August 10, 2010 at 8:21 am
Hi there,
I Have this database with an enormous logfile after a onetime batch load. I wanted to shrink it, but this was not possible. Checking used space there was only 24 Mb free in a log of 27Gb. I have tried Backup log truncate_only, DBCC shrinkfile, checkpoint, even the GUI in SSMS to shrink the log, but nada.
Now, I know I can detach db, rename log and attach db, and that would not be a problem, it's not a production database, but I would like to know what is happening here and why the standard procedures of truncating the log do not work. Database is in simple recoverymode, there is still enough diskspace for growth, there are no processes running on this specific database, so why is this occuring?
Any ideas?
Greetz,
Hans Brouwer
August 10, 2010 at 8:28 am
When you look at the properties of the DB, what is the initial size for the Log file? You could try to create a log back up to another directory and then do a shrink file.
-Roy
August 11, 2010 at 12:30 am
The problem is that one or more of the VLF segments is still in use.
The easiest way to get around this issue is to:
0. backup the database
1. Put the database in Recovery = Simple
2. issue the CheckPoint command
3. Shrink the Log file to the minimum
4. Grow the Log file to the size you expect to need
5. Reset the database to Recovery = Full
6. Backup the database.
Check out SQLSkills.com. Kimberly Tripp has lots of Log file info there.
August 11, 2010 at 12:32 am
Arrgghh!
Initial size is that big, I never looked for it!! Somebody kick me!
Tnx.
Greetz,
Hans Brouwer
August 12, 2010 at 8:45 am
FreeHansje (8/11/2010)
Arrgghh!Initial size is that big, I never looked for it!! Somebody kick me!
Tnx.
Maybe it is that big because someone else KNEW it needed to be that big?? No since shrinking if other activity will make it grow up again. Fragmentation is bad, not to mention the waits you will have during tlog growth for SQL Server to zero out every bit of the new file fragment.
Now, in SIMPLE mode, it should auto-flush committed transactions. So if you are still full on that 27GB then SOMETHING is keeping an open transaction and keeping committed transactions from being flushed out of the log to free up space for new activity.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply