Monday, October 25, 2010

Simple Production Script Auditing Best Practices: Including Query in the Result Set

To make it easier for others to audit the results of a script you have run on current production servers, I suggest two ways to best accomplish the debugging of the potentially problematic code. One would hope that such issues are dealt with already in a testing or development environment, but playing the devil's advocate with production data is worth the effort once one enables a few options, described with print-screens below.  

First, in SQL Server Management Studio, one can make these simple modifications by clicking on Tools, Query Results in the left pane, and click the checkbox ‘Include the query in the result set’. Now one will always have proof of what script was run with your result set report - assuming that one is saving it for work performed on production servers (i.e. by right-clicking in the Results pan and saving script results).


The second modification, is to allow the Display of Line Numbers under Text Editor, Transact-SQL, General, in the left pane, and click on Line Numbers under Display. This is for when you need to run through the save query results and are going back to see the offending line(s) of code.

Insert Line Numbers - helps to find where errors where in script after reading a report.

Finally, I would also suggest a select getdate() and DB(id) at the beginning of all scripts being run in production, and will provide a script eventually (when it looks cool enough to share). This way, there can be no doubt as to where the script was run and when!


Of course, if you really want to investigate specific activity, one should use the best tool for this type of work thanks to the creation of Traces with SQL Server Profiler.  


No comments:

Post a Comment