November 13, 2007 at 10:10 am
The test database I use during development has a 130GB (yes, that's a "G") log file. The MDF file is a couple MB. This happened the other day and the computer was complaining about low disk space.
The only solution I found was to delete the database and restore from backup. Now I had 130GB free space, so I figured I'd be ok for a while while I investigated this out-of-control log.
Then this morning, my computer popped up complaining, "Out of disk space" and what do you know, it had 130GB in the log again, so figuring this out has become more urgent. I am again deleting and restoring from backup.
Let's see, facts about this database: SQL Server 2000 developer version. Patched up to date, I think. @@version says:
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
I have only a couple triggers, but I use functions and procedures heavily to query, but not insert data.
I have to know how to replicate, so I do merge replication to another server. I don't pay much attention to it, but the other server's data seems to be up-to-date.
This database gets lots of ALTER TABLE type statements, being my development guinnea pig.
Help? :hehe:
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
November 13, 2007 at 10:15 am
Is the database following the Full Recovery model and is the transaction log backed up regularly (e.g. every 15 mins)?
November 13, 2007 at 10:41 am
you need to backup tran log
November 13, 2007 at 11:06 am
Do you do a lot of large data imports as well? You might want to change the database to simple recover to limit this issue whil in developement especially since you alter the structure and keep a regular backup so you don't have to recover a lot of changes after restoring if you need to. But if it is just the log you can run
BACKUP LOG dbName WITH TRUNCATE_ONLY
to clear it then use
DBCC SHRINKFILE
to shrink the logfile back down (see BOL for more info).
November 13, 2007 at 12:49 pm
I also found under database properties, the initial size for the log file was 130,000 MB, so after I did
BACKUP LOG V60 WITH TRUNCATE_ONLY
DBCC SHRINKFILE ('V60_log')
I set it to 1MB. We'll see what happens.:P
I thought it would empty and start a fresh log whenever I did a "Full Backup" using the backup option in SQL Manglement Studio.
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
November 13, 2007 at 2:53 pm
If you make it too small you may suffer from autogrows so. You should size it properly so that you have the necessary space at all times and you need to create a maintenace schedule to prevent this from happening again
Cheers,
* Noel
November 13, 2007 at 3:08 pm
Full and Differential backups do not truncate the transaction log. This is only done by a transaction log backupup (BACKUP LOG ...).
November 14, 2007 at 12:30 am
If this is a dev server and the data's not important you can set the DB to simple recovery mode. That way you won't need to do log backups.
Be aware though, you won't be able to do point-in-time restores though.
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
November 14, 2007 at 8:06 am
I'll read up on backing these up. Along with all the other things I need to get done before lunchtime:hehe:
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
November 15, 2007 at 4:18 am
Don't forget, if you're doing a lot of deleting (data from tables), these transactions do get logged. That could also be adding to the size of your Transaction Log. Switch deletes to truncates if you really don't need to keep the delete statements in your log for restore purposes.
Also, as others have said, change your Dev DB to SIMPLE recovery mode (after backing up & truncating the log). And make sure your Prod DB is getting regular TransLog backups or the same thing will happen there eventually.
November 16, 2007 at 4:10 am
ALTER DATABASE MYDB SET RECOVERY SIMPLE
go
use mydb
go
checkpoint
go
dbcc shrinkfile (logfilename,1)
go
then your log file should never grow beyond a reasonable value (proboably the same size as your largest clustered index) again
problem pretty much fixed
MVDBA
November 16, 2007 at 7:51 am
That still depends on the size of the largest transaction.
November 16, 2007 at 9:14 am
That won't be very large even in production.
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
November 19, 2007 at 1:49 am
in that case your largest transaction will when you reindex a table (most likely)
MVDBA
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply