SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can't shrink transaction log on sql server 2005 Standard edition, 64 bit


Can't shrink transaction log on sql server 2005 Standard edition, 64 bit

Author
Message
richard.vanveen
richard.vanveen
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 176
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.
Sreekanth B
Sreekanth B
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1168 Visits: 2155
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'
richard.vanveen
richard.vanveen
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 176
Hi, Sreekanth.
It's set to LOG_BACKUP.
richard.vanveen
richard.vanveen
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 176
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.
Sreekanth B
Sreekanth B
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1168 Visits: 2155
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.
richard.vanveen
richard.vanveen
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 176
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search