April 6, 2010 at 11:35 am
I have a bad feeling the answer to this is going to be 'No' but does anyone know of a way to get 1 or more pieces of code (be they a Stored Procedure or just staright up T-SQL) to automatically execute when SQL Server is about to shutdown or when it is shutting down?
I know you can set an SP to automatically run at Start Up of SQL Server but darned if I can even find the question on Google of how to execute code at or prior to SQL Server Shutdown.
I have a problem that I've been using a creative solution (sounds better then hack) to get around and that is capturing infromation just prior to shutdown because the information I'm capturning, the values retruned by SYS.DM_DB_INDEX_USAGE_STATS, are rest to zero each time SQL Server restarts. In order to get a daily count or total I have to capture this data on a regular basis trhoughout the day and then when the thing restarts, delete all rows captured from SYS.DM_DB_INDEX_USAGE_STATS except the very last one done prior to shutdown.
If there is no way in SQL Server to auto execute code at shutdown I'm curious as to what creative work-a-rounds others have employed.
Thanks
Kindest Regards,
Just say No to Facebook!April 6, 2010 at 2:26 pm
After some mroe digging aorund I believe the official answer to this question is NO. I dont; even see where this has changed in 2008 and I'm betting its not going to in SQL 2010 either. The reason (from what I can gather) is the issue of problems with runnig 'AT SHUTDOWN" code or process interfering with the shutdown itself and that makes sense.
What I've done to get 'At Shutdown' like timing is to run it at Startup. My orginal goal was to purge from a table all but the last row (per rollup or aggregation) of data inserted so that each cycle (the duration between SQL Server Startup and Shutdown) retains but 1 row of data per aggregation instead of many whcih is what I've been dealing with.
If anyone else has come up with come creative work arounds to getting 'AT Shutdown' like event execution I'd love to hear about it.
Kindest Regards,
Just say No to Facebook!April 6, 2010 at 2:41 pm
Um, this may be a stupid question, but is your server suddenly shutting down spontaneously on a regular basis?
If I need something to run before the server shuts down, I generally just run it myself.
If you've got a job or something that reboots your server regularly, can't you just add the proc as a prior step to the service shutdown?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 6, 2010 at 3:02 pm
GSquared (4/6/2010)
Um, this may be a stupid question, but is your server suddenly shutting down spontaneously on a regular basis?If I need something to run before the server shuts down, I generally just run it myself.
If you've got a job or something that reboots your server regularly, can't you just add the proc as a prior step to the service shutdown?
Thats not a stupid question at all. My SQL Boxes are on a scheduled reboot however theres no guarantee that they will not be rebooted at other times by IT as needed. All of the Scheduled jobs on our SQL Boxes are in a calendar so if IT needs to do some unscheduled reboots they know what to check first to amke sure they don;t cut it off when they shouldn't. I do get a heads up when thsi happens but I may be in a situation (after hours) where I can't connect and make changes should they need to reboot other then when scheduled. And so to that end I've found I have less risk if I implement some form of automation with the job that spawned my orginal inquiries.
The tracking of Index Usage data as returned by the sys.DM_DB_INdex_Usage_Stats is great for what I'm tryig to investiagate (so I can jsutofy going against the software vendors recomendations) but because this thing resest each time the service restarts and because I need to see these numbers over a much longer duration (a 30 day cycle) its easiest to populate a table with the results of this thing and have the code purge any current rows in teh table that are for teh current cycle of the server. if I just have a job scheduled it will be fine %90 of the time but it will be in that %10 that I miss getting my data and will get burned for it.
Kindest Regards,
Just say No to Facebook!April 6, 2010 at 3:05 pm
Set up either a script or job that the IT people need to run before they do a shutdown. Maybe a simple VB.NET Windows app with a "pre-shutdown check" button on it that runs a few checks for scheduled/running jobs, runs the data thing you need, and whatever else should be in there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2010 at 1:48 pm
GSquared (4/6/2010)
Set up either a script or job that the IT people need to run before they do a shutdown. Maybe a simple VB.NET Windows app with a "pre-shutdown check" button on it that runs a few checks for scheduled/running jobs, runs the data thing you need, and whatever else should be in there.
Interesting suggestion; hadn't considered that. I'm so used to It doing what they want that I've come to take a "I'll haveto jsutdo it myself if I want it done" approach but if there is something prepackaged that only requires a simple lanuch (i.e. doubl-click of a shortcut) then they would have a hardtime saying its too much to do.
Anyone have anything like this they'd sahre, a pre-shutdown macro/script/whatever that someone (i.e. IT/Admin) can run when they need to reboot the SQL Server while you areaway??
Thanks
Kindest Regards,
Just say No to Facebook!April 12, 2010 at 1:49 pm
The SQLCMD utility could be a solution.
April 12, 2010 at 2:45 pm
This doesn't answer your question, but maybe it will help with the underlying issue.
Since you want to capture the data from the dmv, while not capture the data periodically (say, every minute), and update a table for the last entry in the table. Add a record only at the start of a day, or when the server restarts (through a startup procedure), the rest of the time just update that last record.
It will mean that the queries against this table will need to aggregrate the information, but it might just keep you from losing most of the data. But it seems that you would have to do this anyway.
Does this idea have merit?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 15, 2010 at 9:33 am
WayneS (4/12/2010)
This doesn't answer your question, but maybe it will help with the underlying issue.Since you want to capture the data from the dmv, while not capture the data periodically (say, every minute), and update a table for the last entry in the table. Add a record only at the start of a day, or when the server restarts (through a startup procedure), the rest of the time just update that last record.
It will mean that the queries against this table will need to aggregrate the information, but it might just keep you from losing most of the data. But it seems that you would have to do this anyway.
Does this idea have merit?
Wayne,
Not only does this have merit its what I implemented. Thanks for replying with this and validating it as a soution that at least one other would use. Its worked out well so far. The only difference is instead of updating the current days record (or inserting it if its the first time for that day that the process runs) I insert every hour and at Startup I delete from the table every row except for the last one for each day which leaves me with 1 row per day that has the most accurate results for the DMV on that day. Sine we reboot nightly these daily stats work out well.
Thanks
Kindest Regards,
Just say No to Facebook!Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply