I was investigating a number of maintenance plans that were taking serveral hours to complete and I noticed some optimizations that could be made to the plans without actually affecting the behaviour very much. The maintenance plan after the fixes is: Check Database Integrity Maintenance Cleanup Task Rebuild Index Clean Up History Back Up Database […]
I was asked about how to test a query without all of the clever caching that SQL performs, this is how you do it… but read on for important information. DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE GO SELECT * FROM MyTable Very Important Notes There are some very important things to consider before you use these commands. […]
So you have a SQL Cluster and you want to run a database upgrade using Octopus Deploy… where do you start? There are actually two strategies you can employ to do this, and you can choose the most appropriate one based on how you have things set up. Octopus Deploy is cluster-agnoistic, but you can […]
If you need to find gaps in a sequence (for example, gaps in an ID column, or in a sort order column, etc) you can use the below query. It uses a series of Common Table Expressions to slice up the gaps between islands in the range and presents a list of each gap. I […]
If you ever need to get a list of all your tables and their current identity value (i.e. you want to know CHECKIDENT NORESEED for all your things) you can run this query. The query should be executed against the database you are interested in: SELECT IDENT_CURRENT(TABLE_NAME) AS Id, IDENT_SEED(TABLE_NAME) AS Seed, IDENT_INCR(TABLE_NAME) AS Increment, […]
If you need to find stored procedures that reference a table in a SQL database, you can use this script. Just update the table name in the script. The script returns all objects that reference the table, including stored procedures. SELECT DISTINCT so.name, so.xtype FROM syscomments sc INNER JOIN sysobjects so ON sc.id = so.id […]
A lot of SQL troubleshooting starts like this: sp_who2 The “spootoo” procedure brings back a list of SPIDs and includes information on which SPIDs are blocking. You can quickly track back to a problem SPID by following the information in the “BlkBy” column, which shows the SPID blocking a row. Once you have the SPID, […]
This is not our preferred method of deployment, but we have some batch-file based deployments still in active use (most of our deployments are executed via Octopus Deploy, but some of the concepts below are re-usable there too). One of the tasks we perform during a deployment is a database backup prior to schema changes. […]
As your database grows, you’ll start becoming highly interested in finding out where your data is. This SQL script grabs a list of tables, with their size (total and used) along with a quick calculation of an average size per row. Just point it at your database and run it to get some numbers out. […]
I am working on a database with a really big table. In total it contains something in the region of 50GB of data. This was a rare occassion where we wanted to re-generate the data in this table and wanted to completely clear down the table in a master database that has seven subscribers. There […]