SQL Server Performance Forum – Threads Archive

Alerts and Truncate Log

I was wondering how to make this work. Every once in a while, I have a backup that might fail. The log file then gets too big and throws the full log error, 9002. I have the alert set so it emails me. But, if this happens at 4am, I won’t know until someone calls and wakes me up <img src=’/community/emoticons/emotion-3.gif’ alt=’:O’ />( Or worse, if I’m out the system could be down until I get my messages. I was wondering though, if I can have the alert do a: BACKUP LOG dbname WITH TRUNCATE_ONLY, and if this would be a good fix. I see I can run another job but how would I pass the database in. There are 5 databases on the server and I would need to know which one has the full log, although its normally just one. Probably, would also like to run a SHRINKFILE to take it back down in size since it shouldn’t be this big and NTFS runs better when it has 20% free space. I know I would lose integrity with the log. But when I do get to my email I will be able to do a full backup, so I’m only out a few hours. Thanx Adam

What recovery model has each database? Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

You’ve resolve the problem by running a schedule of transaction log backup in order to maintin the size of transaction log. Otherwise if its in SIMPLE recovery model then ensure to maintain complete backup every now and then. If you execution BACKUP LOG.. TRUNCATE_ONLY then you must run BACKUP DATABASE in order to use when any system failure occurs, otherwise you will be at risk losing all the transactions from last good known backup. Shrinking regularly also does not fetch much joy, find out where and when log is used mostly such as during optimization jobs then ensure to size the Tr.log in order to continue the process and ensure to maintain regular transaction log backups. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

The databases use full and simple logging depending on the database. Some I need up to the minute data. I do have a scheduled job to do the backup, but if it fails I am out of luck. Therefore, I was looking to catch the failure and perform a task to truncate the log, so the database is not down. Thanx
Adam

I think you need a monitoring process on TEMPDB also and make sure to keep an alerting process such as alert via pagers whenever any job fails. Can you confirm the schedule of transaction log backup for important databases.
Have you had any job failures in the past? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi Satya, The TempDB is not the issue, it is mainly our production DB. Here’s the scenario. All databases are backed up to a backup drive. That drive is then backed up to tape. After 2 days the backups are deleted off the drive. This job has failed in the past for a couple reasons. 1) The database back does not delete an old backup and the drive becomes full 2) Somebody makes a copy of an old backup or does a non-scheduled backup for some reason and the drive becomes full on the next backup This full drive causes the log not to be backed up. After about a day without backup, the log fills up. This doesn’t happen too often maybe once every 3 months or so. True, email and pagers is the way to go but if I am out, then my whole system is down until I can respond. So, what I would like is to somehow empty the log using the alert system that pages and emails me. I understand the risks, but my thought is I can come in and do a full backup when I am able to get on. I don’t want to have the system down longer than it needs to be. We are just a small shop but run 20 hours a day, as you gather from the posts. I might be looking at this the wrong way too, is there a way to set an alert on log file growth. My log file is set fairly solid to where it doesn’t grow unless there is an issue. So, if I got an alert it was growing, I would know something was wrong and maybe can respond before hand. Thanx
Adam

I believe you’re taking care of backups using maintenance plans, if so you can take help from thsihttp://www.sqlteam.com/item.asp?ItemID=11672 link to resolve the delete issues. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>