Friday
Dec082017

Best way to delete millions of rows SQL Server Stored Procedure

Need to delete millions of rows from a table in a SQL Server database?

I've often read the best way is to do 10,000 rows at a time by joining a key value on the target table to a key value in a TEMP table. Where 10,000 rows is just small enough that it doesn't get escalated to a table scan by the optimizer and incremental enough that if you have to kill your delete when business hours roll around you won't have to revert the whole shebang.

Anyway. I've ran into this enough that I've got a SPROC written up for it. You'll just need to provide:

  1. The target table name (duh).
  2. The key value (hopefully indexed)
  3. The # of rows (10K by default)
  4. Any additional WHERE clause criteria.

Hell. I even index the temp table for you. Note: if you don't have enterprise edition you won't be able to take advantage of the "WITH (DATA_COMPRESSION=PAGE)" so delete that.

Here's the SPROC:

You run it like this:

Keep in mind that if your key value is the clustered index column you may want to consider putting a nonclustered index on it. That way you're only pulling back one column in your JOIN/WHERE criteria. Rather than a clustered index's feature of having all columns in the leaf level.

Happy deleting!

Wednesday
Oct182017

Stored Procedure to Restore SQL Database

Note: requires sp_rcb_killtargetdbconns (below)

Whenever I'm restoring a SQL database I hate using the GUI and meticulously having to browse out to each file needed in the recovery effort. So I wrote a SPROC that takes a few parameters (i.e. the path to my backup files). Works great for seeding secondary Availability Group replicas or just recovering a database.

It's called sp_rcb_PitrRestore and you run it like this:

The full SPROC follows. As the comments allude to please note that the latest full backup in you @BackupsFolderFull value is the one you want to use in your restore. Also be sure to check that your filename suffixes (i.e. .BAK, .DIF, et cetera) match what's in the SPROC.

Tuesday
Jul162013

Kill connections for specified database

In non-production environments I'm frequently tearing down old databases and replacing them with new ones with the same name. Rather than kill all current connections manually I prefer to use this handy system stored procedure: Here's an example of usage where myDbName is the name of the target database that I intend to name to myDbName_OLD and need to terminate all active connections first.