November 24, 2008 at 3:11 pm
Is there anyway be it a job or an event that one can use to cause a piece of T-SQL to execute before SQL Server is shutdown when SQL Server receives a shutdown command regardless of where that command orginates.
Currently our Network Admins run a hardware/software soltuion that handles automatic shitdown & restart of every server each day at around 4AM-5AM. Is there any way in SQL Server 2005 that I can have a T-SQL command execute when SQL Server recieves a shutdown command? I know I could schedule a job to run X minutes prior to the scheduled shutdown time and that would work but if there is a way in SQL 2005 to perform some action upon the start othe shutdwn event I would prefer to go that route so that I know the code is always run before the system is shutdown.
The T-SQL command is an INSERT that will execute in a few seconds at most.
Ideas?
Thanks
Kindest Regards,
Just say No to Facebook!November 24, 2008 at 4:18 pm
What exactly you want to capture?
MJ
November 24, 2008 at 4:35 pm
I'm not looking to capture any specific data or value that occurs at shutdown but to capture the data in specifc tables just prior to shutdown.
Thanks
Kindest Regards,
Just say No to Facebook!November 24, 2008 at 4:56 pm
Could you explain in greater detail what it is you want to accomplish?
November 25, 2008 at 8:33 am
I know there are articles on execute SP on SQL startup, haven't seen shutdown
You could add a script/file to run in Group Policy, then maybe call SQLCMD?
Start > run > gpedit.msc
Computer configuration > windows settings > scripts (startup/shutdown) > shutdown > add.
November 25, 2008 at 9:07 am
Lynn Pettis (11/24/2008)
Could you explain in greater detail what it is you want to accomplish?
To perform the following command which is using pseduo names because an NDA disallows my listing actual objects from the DB that the thrid party app we primarily use, stores its data:
INSERT INTO AUDIT_TABLE01 (Col1, Col2, ColN)
SELECT Col1, Col2, ColN
FROM TABLE_TO_MONITOR
Kindest Regards,
Just say No to Facebook!November 25, 2008 at 9:16 am
YSLGuru (11/25/2008)
Lynn Pettis (11/24/2008)
Could you explain in greater detail what it is you want to accomplish?To perform the following command which is using pseduo names because an NDA disallows my listing actual objects from the DB that the thrid party app we primarily use, stores its data:
INSERT INTO AUDIT_TABLE01 (Col1, Col2, ColN)
SELECT Col1, Col2, ColN
FROM TABLE_TO_MONITOR
Have you heard the joke about the helicopter lost in the fog? Well, you're in a helicopter. While your answer is technically correct, it does not help me understand what you are trying to accomplish.
November 25, 2008 at 11:07 am
Lynn Pettis (11/25/2008)
YSLGuru (11/25/2008)
Lynn Pettis (11/24/2008)
Could you explain in greater detail what it is you want to accomplish?To perform the following command which is using pseduo names because an NDA disallows my listing actual objects from the DB that the third party app we primarily use, stores its data:
INSERT INTO AUDIT_TABLE01 (Col1, Col2, ColN)
SELECT Col1, Col2, ColN
FROM TABLE_TO_MONITOR
Have you heard the joke about the helicopter lost in the fog? Well, you're in a helicopter. While your answer is technically correct, it does not help me understand what you are trying to accomplish.
The exact table names and columns shouldn't be necessary to answer this so I'm nut sure what more I could say to explain what I'm trying to do. So let me combine the details from the original post with the last post.
At some point in time that is within 5 minutes prior to SQL Server SHutting Down I need to perform the following INSERT. Pretend that the name of the actual database is MyDatabase and the 2 tables involved are TABLE_TO_MONITOR and AUDIT_TABLE01. I have includes the 'USE' line to indicate that this code does need to execute within a specific database on the SQL Server Instance.
USE MyDatabase
GO
INSERT INTO AUDIT_TABLE01 (Col1, Col2, ColN)
SELECT Col1, Col2, ColN
FROM dbo.TABLE_TO_MONITOR
The goal is to capture data from a table close to the shutdown of SQL Server and populate another table with that data. The reason why shouldn't help make this clearer but just to be complete, the reason for doing this is the data in the table we are capturing data from is (for lack of a better explanation) reset each time the database starts up and so we are trying to capture this data in order to generate a history of the values over a period of time measured in day, months and even years since our SQL Server is restart at least once each day.
If that is not enough to get my helicopter out of the fog your seeing then I apologize but I really don't know what more I could provide short of a copy of our data which of course is not possible for several reasons.
Thanks you for replying
Kindest Regards,
Just say No to Facebook!November 25, 2008 at 11:13 am
Actually, you are getting out of the fog. Now that you are getting more into the What is need to be done, rather than how it is starting to make more sense. No, for what you are try to explain, we don't really need to know the details of the database. Another good example that would work is retaining information from the dmv's in SQL Server 2005 between reboots, as those get cleared as well.
I'll have to reread what you have already posted, but if you have any additional information about the what, that may further explain what you are trying to accomplish, please post it.
November 25, 2008 at 11:17 am
Actually, I do have a question about the table(s) that you need to do this for, and it is very generic, how many rows of data in each of the table(s)?
November 25, 2008 at 11:18 am
whilst SQL is shutdown the data in the table is not going to change, so rather than try to capture it on shutdown, which is difficult, capture it on startup which is easy. So in the process you must already have to clear this table down, run your insert first to save it away, then clear your table down.
---------------------------------------------------------------------
November 25, 2008 at 11:19 am
Lynn Pettis (11/25/2008)
Actually, you are getting out of the fog. Now that you are getting more into the What is need to be done, rather than how it is starting to make more sense. No, for what you are try to explain, we don't really need to know the details of the database. Another good example that would work is retaining information from the dmv's in SQL Server 2005 between reboots, as those get cleared as well.I'll have to reread what you have already posted, but if you have any additional information about the what, that may further explain what you are trying to accomplish, please post it.
If there is a known way to do this for the DMV's in 2005 then I would think I could apply the same method to our database/tables. Is there something known for doing this for SQL 2005's DMV's?
Thanks for replying
Kindest Regards,
Just say No to Facebook!November 25, 2008 at 11:21 am
Lynn Pettis (11/25/2008)
Actually, I do have a question about the table(s) that you need to do this for, and it is very generic, how many rows of data in each of the table(s)?
It varies based on how many users are actively working in the system but on average I'd say it would range between 500 to 1000 rows. Currently when I run the query during the day while evryone is on line it completes in a few seconds if that long so it's pretty quick.
Thanks for replying
Kindest Regards,
Just say No to Facebook!November 25, 2008 at 11:27 am
Based on your earlier posts, I also assume that the records are fairly narrow, would you say under 128 bytes each?
November 25, 2008 at 11:33 am
YSLGuru (11/25/2008)
Lynn Pettis (11/25/2008)
Actually, you are getting out of the fog. Now that you are getting more into the What is need to be done, rather than how it is starting to make more sense. No, for what you are try to explain, we don't really need to know the details of the database. Another good example that would work is retaining information from the dmv's in SQL Server 2005 between reboots, as those get cleared as well.I'll have to reread what you have already posted, but if you have any additional information about the what, that may further explain what you are trying to accomplish, please post it.
If there is a known way to do this for the DMV's in 2005 then I would think I could apply the same method to our database/tables. Is there something known for doing this for SQL 2005's DMV's?
Thanks for replying
Didn't say there was a way to capture that info on shut down, it was an analogy to what you are trying to accomplish. Also, I just reread a post just before mine. Can you capture the data in the tables prior to them being reset? How is the reset accomplished, by the application when restarted, is it a truncate or delete, or whatever?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply