Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can't shrink transaction log on sql server 2005 Standard edition, 64 bit Expand / Collapse
Author
Message
Posted Thursday, July 3, 2014 1:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 17, 2014 11:05 AM
Points: 24, Visits: 94
Hi. We have a DB on a sql server 2005 server that is 600MB data and 242 GB log, but I can't shrink the log, even after doing a backup tran of the log.
The dbcc shrinkfile(logfilename) doesn't give an error, just doesn't work.
This is a production server, so I don't want to set the recovery to simple, unless there is no other way.
The DB options are:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics
Please advise on any suggestions to free up space. Thanks.
Post #1589114
Posted Thursday, July 3, 2014 1:34 PM This worked for the OP Answer marked as solution
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:25 AM
Points: 292, Visits: 1,206
What does log_reuse_wait_desc say?

Can you run the below stmnt and see what it says?
SELECT log_reuse_wait_Desc FROM sys.databases
WHERE name = 'Your_DB_name'
Post #1589118
Posted Monday, July 7, 2014 6:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 17, 2014 11:05 AM
Points: 24, Visits: 94
Hi, Sreekanth.
It's set to LOG_BACKUP.
Post #1589822
Posted Monday, July 7, 2014 6:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 17, 2014 11:05 AM
Points: 24, Visits: 94
Thanks very much, Sreekanth.
I was told by the SAs that they were doing Arcserve backups on the DB, but after you replied, I checked further, and found they were only doing full backups, so the log has been growing for a very long time.
I set the DB to simple, cleared the log, set it to full, and did a full backup, and now everything is ok.
Post #1589826
Posted Thursday, July 10, 2014 8:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:25 AM
Points: 292, Visits: 1,206
np...So after changing it back to FULL, have you shceduled T-Log backups?
Make sure you have got regular T-Log Backups scheduled for any database which is in "FULL" recovery model. If not, the same issue(gigantic log file) will re-occur.
Post #1591194
Posted Thursday, July 10, 2014 12:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 17, 2014 11:05 AM
Points: 24, Visits: 94
Hi, Sreekanth.
Yes, the backup I said worked was the tran log backup job.
Thanks for your help, and for verifying I didn't forget to turn on the tran log backups after clearing the log.
Post #1591287
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse