| Author |
Topic |
|
nicolo
Starting Member
20 Posts |
 Posted - 2005-10-20 : 14:38:46
|
| Hello, I've been getting messages in the event logs about failed calls in SQL 2000 and the transaction log file being too full. Other errors included timeout expired messages.I conducted a full backup of the database as well as a dbcc shrinkfile and that did shrink my log file. I'm not sure about taking off auto-shrink but I did set a maximum size for the transaction log file.Now when I run a transaction log backup it fails. The job history reveals sqlmaint.exe fails. Why is this happening?Also would a full or excessively large transaction file cause sql calls to fail or timeout and thus shutting down the SQL server? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-20 : 14:56:06
|
| Auto shrink should never be used. If you don't care about point in time recovery, meaning you can afford to restore to the last full backup, then change your recovery model to SIMPLE. Otherwise, keep it at FULL, and start backing up the trasaction log more frequently. We do ours every 15 minutes. You also should not be shrinking the tlog manually as it is just going to need it again. When the tlog gets full, your application will suffer as it will be getting this error message. SQL Server would not shutdown because of it though, someone must be manually doing this.Tara |
 |
|
|
nicolo
Starting Member
20 Posts |
Posted - 2005-10-20 : 15:10:17
|
| Thanks but I can't run the transaction log backups. It keeps giving me:sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.Why is this occurring?But I will take the auto-shrink off for the database. It's not in SIMPLE mode, it's FULL mode. I do need point in time recovery for it.Also if I cannot shrink the tlog manually, how else could I do it when I have too big of a log? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-20 : 15:18:46
|
| How big is the database and how big is the LDF file?Tara |
|
|
|
nicolo
Starting Member
20 Posts |
Posted - 2005-10-20 : 15:29:00
|
| database is slightly over 4GBs and the LDF is 5MB right nowI did a workaround and created a new Maintenance Plan that only does Translog backups every hour. That ran fine. But the minute I try to incorporate translog backups to my primary maintenance plan it errors out.I checked my own DB as well as the MSDB database under the primary maintenance plan.I also removed Auto-Shrink if that's better for performance although I am not in Simple Mode. If however, it becomes to big how will I reduce the database size? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-20 : 15:42:41
|
| Autoshrink shouldn't be used regardless of the recovery model. You recreate your original maintenance plan or check this out for alternatives to sqlmaint:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspxYou should have enough disk space to allow your tlog file to get as big as 8GB. You should not shrink it unless it is over this size. Shrining it will cause a huge performance problem when it has to grow the very next time because it's too small. The optimizations part of the maintenance plan is what can cause the tlog to grow. Allow it to have the big size. Add disk space if you need to accomodate this. Tara |
|
|
|
wiltech
Yak Posting Veteran
51 Posts |
Posted - 2005-10-20 : 16:02:10
|
quote:
Originally posted by nicolo
I checked my own DB as well as the MSDB database under the primary maintenance plan.
By default, I believe the MSDB database uses Simple recovery mode. If you have both of these databasses checked, the maintenance plan will try to do a transaction log backup on MSDB which will fail.Tony W |
|
|
|
nicolo
Starting Member
20 Posts |
Posted - 2005-10-20 : 16:48:52
|
| Gotcha...Tara, I read the link you sent me. Boy do I feel worthless for using a maintenance wizard.Also I was under the impression, backing up the logs does free up some space, does it not? Seems a bit confusing to me because another DBA yelled at me for letting the transaction log get so big. When I told him the initial size of 4GB, he had a conniption. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-20 : 16:53:24
|
| Backing up the logs frees up space from within the LDF file. So let's say your LDF is 10GB. If you haven't run a recent tlog backup, you might have 9GB of used space in there. Once you backup the tlog, the used space should be less than 100MB. The key though is that the file size is still 10GB. But that's what we want as we don't want to shrink it as it's a huge performance penalty if it has to grow at some point. Feel free to use the sprocs from the link. I wrote them a while back and have been using them in production for quite some time now. Of course, always test them in a non-prod environment to make sure that they work as desired.Tara |
|
|
|
nicolo
Starting Member
20 Posts |
Posted - 2005-10-20 : 17:25:52
|
| I understand now. Essentially what I've then created is a 10GB opening for the log file to move freely. Another quick question, should I then, set a large enough space limit to the transaction logs? Say perhaps a quarter of the database size? Or just let it have unrestricted file growth? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-20 : 17:31:10
|
| I would set it equal to the size of the MDF. It could grow up to twice the size. This is due to DBCC DBREINDEX, which is also called optimizations in the maintenance plan. If you don't do this, then 25% of the MDF is usually fine. I use unrestricted growth on mine. We have alerts fire when our disks are 85% full, so we are made aware of when we need to do something about the disks or shrink the files temporarily.Tara |
|
|
|
nicolo
Starting Member
20 Posts |
Posted - 2005-10-21 : 15:04:00
|
| I see...well I've set it up for about 25% of the MDF. But left the auto grow option on. From what I see, the transaction logs shouldn't fill up in the span of an hour. ok well I've set up daily full backup jobs to run at night, as well as hourly transaction log backups. Haven't had any strange problems with SQL server so far. I don't think it was shutting down physically but rather the service was doing that. In addition it will email me on the status of jobs. |
|
|
|
|
sql problems