My second favorite SQL tip

Create an audit log table (or even better yet, an audit database). It can have a pretty simple structure – I often only have an artificial key, description, and date in mine. Then, when you’re executing a long running script or automated process, you can liberally add “INSERT” statements. Besides providing a log of what you did for future troubleshooting, I find this comes in real handy for identifying long-running operations (with a lot less effort and filtering than SQL Profiler), as well as letting me keep track of what’s going on without having to sit around at a console. In particular, it’s good insurance to let you know what happened with the long running query you kicked off in a remote desktop that you get disconnected from. I tend to through my logging statements before and after any relatively large operation, like a bulk delete, backup, restore, reindex, etc.

Technorati Tags: Tips and Tricks,SQL Server,T-SQL,Scripting