August 30, 2012 at 2:53 am
Hi,
I have a 60Gb log file i need to truncate on a DB running in simple mode - what is the easiest way to do this ?
In 2005 I used to use
backup log dbname with truncate_only and then shrink the file in Enterprise manger but i believe this method has changed in 2008
tia,
Mike.
August 30, 2012 at 3:08 am
The easiest way to do that is just to wait and the log will be automatically truncated for you. You shouldn't have to wait more than a minute for it to happen. If it doesn't happen automatically then there's some reason why log space can't be reused - this will not be resolved by truncating manually.
Why do you want to shrink the log file? Are you confident that it will never grow back to 60GB? If you're not, it's likely that you're wasting resources and causing fragmentation on your disk.
John
August 30, 2012 at 3:17 am
It doesn't seem to be reducing in size just growing..
The only reason i noticed it is the DB it logs for is only 2Gb in size so appears rather huge in comparison.
We are not low on disk space so if you believe it has no detrimental affect on performance i'll just leave it...
Maybe wrongly i believed it was good practice to keeps logs at a reasonable size
August 30, 2012 at 3:24 am
You certainly need to investigate why a database of that size has a log of that size. My point was only that if it won't truncate automatically, it won't truncate manually, either.
Use DBCC SQLPERF(LOGSPACE) to find out how much free space you have in your log file. This will give you an idea of whether you have a problem at this very moment. It may be that you have a long-running large transaction.
Please also read this article by Gail Shaw, which explains things better than I can:
http://www.sqlservercentral.com/articles/Transaction+Logs/72488/
John
August 30, 2012 at 3:27 am
These should provide some good reading on the topic of TX log management
Why is my transaction log full - http://www.sqlservercentral.com/articles/Transaction+Logs/72488/
Managing Transaction Logs - http://www.sqlservercentral.com/articles/Administration/64582/
Stairway to Transaction Log Management - http://www.sqlservercentral.com/stairway/73776/
Accidental DBA Guide - Chapter 8 - http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
August 30, 2012 at 3:32 am
Interestingly the %used was pretty high @ 92.6%
virtual64481.8792.59304
August 30, 2012 at 3:35 am
Something is activly using the log then.
The first link on why is my transaction log full should help to find out what is using so much log space.
August 30, 2012 at 3:36 am
USE database_name
GO
DBCC SHRINKFILE(2,1)
BACKUP LOG database_name WITH TRUNCATE_ONLY
DBCC SHRINKFILE(2,1)
The above assume the logfile is file 2.
SP_helpdb databasename
Right click db,TASKS,SHRINK change to log and see how much space is actually being used.
Then click cancel.
August 30, 2012 at 3:40 am
FOR 2008
USE 2008db;
GO
DBCC SHRINKFILE (<logical_logfilename>,5);
GO
For sql 2008
5- size you want to shrink to.
<logical_logfilename> run sp_helpdb.
August 30, 2012 at 3:45 am
DBCC OPENTRAN returns no clues
No active open transactions.
August 30, 2012 at 4:03 am
What's the value for your database from:
select name, log_reuse_wait_desc from sys.databases ?
August 30, 2012 at 4:31 am
select name, log_reuse_wait_desc from sys.databases
masterNOTHING
tempdbACTIVE_TRANSACTION
model LOG_BACKUP
msdb NOTHING
virtualLOG_BACKUP
vmum NOTHING
I've run a T-Log backup through on virtual that truncated the file so it's now mainly empty but i've tried shrinking it in Enterrpise manager to no avail
virtual60085.370.6912691
I guess it's no big deal having a huge empty log file...
August 30, 2012 at 4:43 am
It can sometimes take 2 log backups before the space will free up. Not usually a situation I'd associate with simple recovery mode though.
Looks like the space is free internally now - try another backup & then run the DBBC Shrinkfile command posted earlier - that may give you further messages if it's still unable to shrink.
August 30, 2012 at 5:12 am
Thanks all !!!
Running a 2nd backup and then a shrink in EM worked a treat 🙂
virtual119.367211.94245
August 30, 2012 at 7:21 am
Cool 🙂
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply