Deleted rows left a gap in an identity column for my developer. Here is how to reseed identity of a table so new rows keep the sequence without gaps.
SQL SERVER – 2008 – Optimize for Ad hoc Workloads – Advance Performance Optimization
One time ad hoc plans waste plan cache memory. See how the SQL Server 2008 option optimize for ad hoc workloads keeps them from filling up the cache.
SQL SERVER – Find Current Location of Data and Log File of All the Database
A one line query on sys.master_files that shows the current data and log file location for all databases, handy when cleaning up old .mdf and .ldf files.
SQL SERVER – List All Server Wide Configurations Values
I forgot the syntax to check the two digit year cutoff, so I listed all Server Wide Configurations with sys.configurations. A small script worth saving.
Using sp_help and Friends to Explore a Database
Using sp_help, sp_helpindex, sp_helptext, and sp_spaceused gives a quick SQL Server tour before you reach for catalog views.
SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL – Part 2
An updated script to find the longest running query in SQL Server 2005 and 2008, with the divide by zero error fixed and time now shown in milliseconds.
SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL
A quick script using sys.dm_db_partition_stats that lists the row count of every table and shows the largest table in database right at the top.