March 24, 2011 at 9:10 am
Hi
I've got an transaction log that is 5110mb with 99% free space and would like to shrink this to 256mb but I'm unable to to this.
I've run the command
DBCC SHRINKFILE (database_log, 256);
GO
And nothing happens. Also tried to change recovery mode to simple, and back to full. Nothing.
Any idea what I'm doing wrong?
/Andy
March 24, 2011 at 9:29 am
The active portion of the log may be towards the end of the file.
Do a couple of log backups and it should kick the active portion around to the front of the file and you can then shrink.
You can use DBCC LOGINFO to see where the active portion is (status column = 2 means active, and cannot be shrunk past this point).
More info at http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-More-on-the-circular-nature-of-the-log.aspx
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
March 24, 2011 at 9:48 am
And if you have no real activity on that database as regards data modifications, you can create a table with a char(8000) field (one page worth), then make a loop 64 times to insert 64 rows of something. And do the dbcc log info. Rinse and repeat as necessary until you see the active VLF (status=2) to switch to a new VLF.
Once that happens, drop the bogus table, backup the log and shrink and it'll shrink down much better probably.
I'll post the code for this later this afternoon when I get into the office.
Please read Kimberly tripp's post on sizing the log file to grow it properly, and her and Paul's related blogs about avoiding bugs in growth sizes here: http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 24, 2011 at 9:51 am
You don't need to do that any more post SQL Server 2000 - using log backups will do it automatically for you. You might have to do 2 or 3 though, that's all.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
March 24, 2011 at 9:59 am
Oh cool. I did about 4 or 5 log backups on 2008r2 and got nowhere, so I wrote the loop. Even with a loop, I had to fill up about 10 extents or so before it switched; must have been a larger vlog. Perhaps if I were persistent and on a larger vlog just the backups would work?
Does this work because the backup log knows what you are trying to do, or because it adds to the log itself, thereby performing the same sort of task?
Thanks,
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 24, 2011 at 10:03 am
Oh wait, as I remember, the time I had to perform this ritual, I was on SQL 7. So disregard the r2 statement. I was working with r2 that night to test before correcting my vlog woes on the production sql7 box.
I know. I still support one client with sql7. Their vendor app is being replaced in fall! Hooray!
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply