September 12, 2011 at 1:22 pm
Hello all,
I am looking to setup a sql server alert based on the error number for transaction log is full.
i have finished that part but I also want to creat in a sql server job that kicks in and take a log back of the database that has issued the alert.
how would I go about it.
Thanks in advance.
Regards,
Ahmed
September 12, 2011 at 2:33 pm
Seriously, not a great idea.
You should have regular log backups already. Properly planned, that will take care of the log backup reasons for logs full (plus, if you're restoring in a crisis having unexpected log backups is a good way to ruin your day)
If you already have log backups running regularly a full log will probably be due to something other than needing a log backup. If you don't have regular log backups, schedule them or switch to simple recovery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 13, 2011 at 9:43 am
Thanks, sorry i dodnt see the response, my bad.
i understand where you coming form but I have been asked to do so in a test enviroment.
i can make the case that its not a good ides but if I really have to do it ( they wont listen ) how would i go about it.
September 13, 2011 at 9:45 am
qur7 (9/13/2011)
Thanks, sorry i dodnt see the response, my bad.i understand where you coming form but I have been asked to do so in a test enviroment.
i can make the case that its not a good ides but if I really have to do it ( they wont listen ) how would i go about it.
Actually you have nothing to do. Just let it happen.
It'll error out when you can't use the db anymore and you'll be alerted (not just by sql server, but by a end user).
September 13, 2011 at 9:56 am
qur7 (9/13/2011)
i understand where you coming form but I have been asked to do so in a test enviroment.
As in a non-production server?
If so, set the DB to simple recovery and don't worry about log backups.
i can make the case that its not a good ides but if I really have to do it ( they wont listen ) how would i go about it.
The point is that it probably won't help. If you have regular log backups and the log fills, it's probably filling for some reason other than needing a log backup. Like active transaction, active backup, replication or one of the other reasons. If that's the case, running a log backup will do absolutely nothing to fix the problem.
It's like having a car that won't start and going to buy petrol without checking why it won't start. Might help, but probably won't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 13, 2011 at 1:17 pm
Thanks for the ifo guys,
can i set up an alert on the server that if transaction log file is about to be full.
Lets say 90% or 85% so I can get a heads up before LF gets full.
how would like go about that ?
Thanks in advance
September 13, 2011 at 2:55 pm
You would TAKE LOG BACKUPS.
In the event those are insufficient there won't be more than a few seconds between the time the app crashes and the alert was sent to your box.
September 13, 2011 at 3:04 pm
Ninja's_RGR'us (9/13/2011)
You would TAKE LOG BACKUPS.
In all fairness, there are lots of things that cause a log to fill unexpectedly with regular log backups and it's nice to know that the log is filling up before the app starts throwing errors.
qur7: I personally don't use alerts, I have monitoring tools that handle all that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 13, 2011 at 4:27 pm
when setting up the alert via sqlagent (right click on alerts), log filling (by percentage) is one of the default options I believe. You can then tell it to respond to the alert by executing a sqlagent job, this would be the transaction log backup job you will have undoubtedly already set up to do regular log backups.
---------------------------------------------------------------------
September 13, 2011 at 4:29 pm
in case we have misunderstood your question the transaction log backup job could be created by setting up a maintenance plan via SSMS
---------------------------------------------------------------------
September 19, 2011 at 11:04 am
Thanx George,
i can set up the SQL agent job to take transaction log backup, but what if i have 10 databases on the server and the alerts send me for database "a"
how can i configure the job to just run transaction log for database "a"
thanks for the help
September 19, 2011 at 4:20 pm
you would need a log filling alert for each database and a corresponding transaction log backup job per database.
---------------------------------------------------------------------
September 20, 2011 at 9:46 am
Thanks,
so there is no way i can have a script that will pick up the name of the database from error log or from sys table to determine that if log file for that database is full and run the transaction log for that database?
the reason i am asking that is because we have some times 20 or so databases on the server and have about 40 servers, and creating alert for each database just seems too long to accomplish.
hope you are any one else has the answer for this.
September 20, 2011 at 9:50 am
Sql monitor from red-gate
Or make a job and run tlog backups every 15 minutes or 5.
The only reasons I've seen log blow out if because of index maintenance or awfully bad sql (dev box).
Either way, no amount of alerting would have allowed me to stop it from occurring.
September 20, 2011 at 10:03 am
Hello,
unfortunately we dont have Red gate, i do have spot light from Quest, can i setup a alert in there with the automatic transaction log for only the database where log is full.
even though we do have spot light i am not well verse in it that why i am asking from the people who have deep knowledge of spot light.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply