How can i come to know SQL Server is going Down.
-
Hi all,
Want to know all events which are logged in Event logger of Windows which will let me know that SQL server(2000 & 2005) is going down.
i.e those event ID's which will tell that SQL Server is having some problem(going down) so just copy all the DB to other location(Backup DB).
Thanks in Advance.
Thanks
-Abhishek.
-
I think your logic is backwards.... if the sql server is going down or, is already down/offline, you wouldn't be able to backup the databases...well...because the server must be up to do a backup operation.
you could use file system operations to copy mdf or bak files, but why would you do that? isn't it more important to alert someone that the server is down, and try and get it back up, rather than worry about copying files?
can you explain in a bit more detail what you want to accomplish? that way we could give you a better answer, rather than guessing at your requirements.
do you just want to restart the SQL service if it is stopped?
are you afraid of a harddrive failure if the server goes down,a dn you want to save the fiels just in case?
do you want to bring up a differetn server if the other server goes down?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
ok its like this
I want those event ID which will let me know that server is going down so that i can backup that SQL server before going down using some S/W .i want this mostly for Microsoft operations manager. so that before going down i can say that i have backup ed that SQL Server.
so thats why i need those SQL events ID's which will let me know that SQL server is going down...
Thanks
-Abhishek.
-
again, your timing is off: if you stop the SQl server service, you will see something like this in the events log:
17148 :SQL Server is terminating due to 'stop' request from Service Control Manager.
there's no "is about to terminate " and then "is now terminiated" events...it's all the same.
since it occurs almost instantly, you cannot backup the databases prior to the event occuring. If the operating system goes down, there is no way to back them up prior to that happening, period.
what you really want to do is create a backup strategy, so that if the server or service goes down, you can recover: backups every 15 minutes or half hour, whether full backups or a full backup in the morning and transactions on every interval you need, that is what you need to do...
forget trying to capture the event and do something about it...just create a solid backup strategy and practice restoring .
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
ok thats right. i agree with you.
but u r talking about Service stop and if service is Stopped we can restart it i think no need to take backup of SQL Server. and if it stopped and not able to restart again than using some backup S/W we can backup those DB files am i right?
so i dont want those events which are generated by Services Stop state i want events other than Service stop like Low DB space or transaction log is full etc. so thats why i m in need of such events which tells me that SQL server is down/inconsistent(Low Free Space etc).
Thanks
-Abhishek.
-
You may want to consider creating custom alerts under SQL Server agent. You can create custom alerts to monitor any of the SQL Server perfmon counters such as datafile size or transaction log size etc... You can set it once it goes beyond a maximum value to email an operator of the condition. I hope this helps.
-
ok thanx a lot..
I think this will help.and almost done with this.
by the way are these two things only tell us that we have to take backup of SQl Server or any more things which will tell us that we have to backup SQL Server.?
Thanks
-Abhishek.
-
On the response tab of a new alert being created you could also have it execute a job such as a predefined backup job or shrinkfile etc... There are many other counters that can be evaluated such as buffer cache hit ratio but I would be cautious about putting alerts on everything or else you will find yourself inundated with emails.
-
ya thats right...
ok then thanx a lot.....
if i have any probs will get back to u...
bye..
-Abhishek.
-
You're still thinking backwards.
You don't want to 'capture' the server state in order to decide when to make your backup, you want to decide and plan for your backups at certain times anyway, regardless if the server is 'feeling well' or not.
*IF* the server then suddenly decides to blow up, you'll already have a 'recent' backup done, so that you can recover with as little damage as possible, should need be.
/Kenneth
-
I agree with Kenneth.
Additionally, if you are alerted that the server is failing, depending on the size of the databases, the server could likely fail during the backup.
As Kenneth suggested, get your backup plan in place so that if it does fail, your covered. Saves getting a lot of emails from alerts too!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply