Any way to run scripts on shutdown to capture DMV info?

Question

Any way to run scripts on shutdown to capture DMV info?

jcook119

Old Hand

Points: 329
More actions
October 24, 2007 at 1:23 pm

#180608

I would like to collect various metrics from some DMVs prior to a shutdown of a server or the SQL Server service. Specifically, I would like to collect perfomance info for archiving into a trending database. Does SQL 2005 or Windows 2003 have any built-in mechanism to run a script on shutdown? It will be difficult if not impossible for me to convince management to purchase a third-party tool, so any info on doing this natively would be much appreciated.
Thanks,
James

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kenneth.Fisher SSC-Insane Points: 20129 More actions · Answer 1

I don't know if this is exactly what you are looking for but there is an option on a trace called TRACE_PRODUCE_BLACKBOX that records the last 5mb of trace information produced by the server. Look at sp_trace_create in BOL.

Kenneth Fisher

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 2

I don't know of any way to do this because usually upon shutdown the systems wants everything to come down as quickly as possible. You might look at running periodic queries and storing into a table, you wouldn't get the exact time of shutdown, but you should be pretty close.

K. Brian Kelley
@kbriankelley

jcook119 Old Hand Points: 329 More actions · Answer 3

Thanks gentlemen, I appreciate the replies. We have a monthly fixed downtime window on the 3rd Saturday of each month that I can schedule around for normal cases. I was mainly hoping to find something that I could put in place to handle unexpected shutdowns.

Kenneth.Fisher SSC-Insane Points: 20129 More actions · Answer 4

The TRACE_PRODUCE_BLACKBOX option is specifically for unexpected shutdowns. It has a running trace where it keeps the last 5 mg at all times. So after an unexpected shutdown you can restart the server then go back and look at the last 5 mg of the trace.

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]