February 2, 2011 at 7:35 am
Hello All,
I'm unable to shrink the transaction log files for the prod db and it is causing the db to run out of log space on the disks I have the transaction log files 2 days in a row. I've tried to put the db in simple recovery mode and run "backup log (dbname) with truncate_only" and try to shrink the log files thereafter but it doesn't shrink them. I can't even run a full, diff, or trans log backup to any of the disk because it keeps stating that I don't have enough disk space to complete the process. This all started when my db that was being mirrored went into "suspend" mode. Any help would be greatly appreciated!!!
Thanks,
Dave 😀
February 2, 2011 at 8:03 am
Can you run backups to another server or drive on your network ?
February 2, 2011 at 8:05 am
Try adding a file on a different drive to the database as a log file. That should give you enough space so that you can then truncate it.
Also, if the logs are waiting on the transaction marker to be cleared by the failed process, you need to take the database out of mirrroring until you recover everything.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2011 at 8:11 am
I'm running a backup to another drive (30% complete). Every backup I've tried (e.g. full, diff, trans) keeps failing.
February 2, 2011 at 8:13 am
I added another transaction log file to another drive volume, but I'm still unable to shrink the transaction log from it's large sizes for 2 of my files. I had previously removed the mirror a few days ago when this problem initially bubbled up.
February 2, 2011 at 8:19 am
If you can't shrink those files it's because SQL Server is retaining the transaction information. That usually means replication, log shipping, or mirroring is active. That's where I'd focus at the moment. Make sure that mirroring really is disabled on that database. Once it's removed you should be able to checkpoint and then shrink the file.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2011 at 8:51 am
What's the value of the 'log_reuse_wait_desc' column in the sys.databases table for that database?
Thanks.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
February 2, 2011 at 10:20 am
I found that the issue was related to Replication and was holding all the logical log files for the prod db. I have removed Replication and was able to shrink the log files to a normal size. I'm currently running a full backup and it seems to be working thus far. I'm still not sure what the root cause for the Replication to hold up the db's transaction log files. Thanks to everybody involved for your help!
February 2, 2011 at 10:56 am
Perhaps the job that updates the subscriber was not running.
February 4, 2011 at 2:44 am
First stop all the SQL Services (Instance) then restart them and then try to shrink the log file but make sure that your recovery model should be Simple.
Thanks.
February 4, 2011 at 3:05 am
vinodsy123 (2/4/2011)
First stop all the SQL Services (Instance) then restart them and then try to shrink the log file but make sure that your recovery model should be Simple.Thanks.
I am not sure that is the best advice for a production database
February 4, 2011 at 3:14 am
steveb. (2/4/2011)
vinodsy123 (2/4/2011)
First stop all the SQL Services (Instance) then restart them and then try to shrink the log file but make sure that your recovery model should be Simple.Thanks.
I am not sure that is the best advice for a production database
You are right buddy but after posting I have seen that its a Production DB. Thanks.
February 4, 2011 at 9:33 am
Check for an open transaction.
If there is and is deemed safe to do so, kill the SPID with the transaction.
Take a transaction log backup and then shrink the transaction log.
There is no other reason I can think of that would cause the log to continue to grow.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply