Mark Mintoff My superpower is common sense

9Jul/130

How long will my database be IN RECOVERY? (SQL 2008)

I write this as I stare at a screen waiting for a critical database to return out of being In Recovery. Have you ever been there? Have you ever had an unexpected shutdown or error on the production server, while you were running a large transaction? If your answer is "Yes", then inevitably you are wondering how long it is going to take.

To this end, I have devised a simple query which will loop through all databases on the current server and give a report on the database recovery process for any that are in recovery. This will give you an estimate of how long you have left twiddling your thumbs and hoping you did not lose any data.

Enjoy and I hope you did not lose any data!

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
13Mar/120

T-SQL Batch Delete

There are times when you need to delete an arbitrarily large subset of data from a database table. For the sake of example, let us suppose we will be deleting 100,000,000 rows. A simple delete statement:

will take a very long time (hours) to process as 100,000,000 rows will be deleted within a single transaction.

For this purpose, when I need to delete a large subset of data, I prefer to work with a while loop delete and multiple transactions:

This results in a much faster deletion of data as each transaction will only need to delete 1000 rows at a time. An additional benefit is presented if you need to stop the deletion at any point; for example you have locked the table and somebody needs to select some data. There are no long rollbacks and you do not lose progress on what you have deleted.

Hence, to make things easier:

Alternatively:

Enjoy!

VN:F [1.9.22_1171]
Rating: 4.7/5 (3 votes cast)