June 11, 2012 at 5:31 am
I have been tasked with figuring out haw to shrink a 2.5 Terrabyte log file. When I took it over it had very little free space. I have since done a full backup and spend 3 days reorganizing the file and releasing unused space and the only result I received was I now have 1.9 Terrabytes of free space. The file did not shrink at all in fact it grew by .5 Terrabytes. They do have replication set up on this server, however it has been suspended. They have not been making regulary full sql backups because they are using DPM to backup up their servers. Can anyone give me any advice on how to properly make this file shrink? I am very limited on disk space at this point and have tried the process to rename the log file and do the 'dbcc rebuild_log' but the system kept saying my syntax was not correct and it wouldn't rebuild. Was this depricated in SQL 2008 as well.
CRC
June 11, 2012 at 5:47 am
What recovery model is the database in? Are you taking t-log backups? A full backup will not truncate the log.
What does column log_reuse_wait_desc in sys.databases say?
How many VLF files, what size and what is the most common status when running DBCC LOGINFO?
What's the output from DBCC SQLPERF(LOGINFO) for the database?
June 11, 2012 at 6:04 am
Do you need the database to be in full recovery mode?, happen change the recovery mode to simple, and shrink the log file, then if needed return to full recovery mode and enable so sort of transaction log backup plan
June 11, 2012 at 6:29 am
Remove the database from the replication publication if it still exists. Only stopping the distribution agent will cause the tran log to grow continuously, no matter of a full backup is taken, as transactions have not been marked as replicated and so cannot be marked inactive and truncated.
June 11, 2012 at 6:41 am
As above you should not use full recovery unless you are backing up the transaction log, to shrink the log file as stolen from bol
USE AdventureWorks2008R2;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
GO
-- Reset the database recovery model, only if you have transaction log backups
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO
June 11, 2012 at 6:42 am
Please read through this - Managing Transaction Logs[/url] and this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
It's not the replication distribution agent that, if stopped, causes the logs to grow. It's the log reader agent.
Also, do not ever shrink a transaction log to 1 (DBCC ShrinkFile (<file name>, 1)) as the regrowth of the log will take time and cause severe log fragmentation. It's terrible advice. Once you've resolved whatever's keeping the log active (and it may not be log backups so don't just switch to simple recovery without checking), shrink the log to a reasonable size given the activity of the database.
p.s. Don't try to rebuild the log unless you understand the potential consequences (loss of transactional integrity, possible loss of structural integrity)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2018 at 10:17 am
I had the same problem. I ran an index defrag process but the transaction log became full and the defrag process errored out. The transaction log remained large.
I backed up the transaction log then proceeded to shrink the transaction log .ldf file. However the transaction log did not shrink at all.
I then issued a "CHECKPOINT" followed by "DBCC DROPCLEANBUFFER" and was able to shrink the transaction log .ldf file thereafter
Tung Dang
Azure and SQL Server DBA Contractor / Consultant
DataZip
May 21, 2018 at 9:27 am
One thing to keep in mind in full mode that if you have the active part of the log at the end of the log, the file won't shrink. There are scripts (like this http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30460/) that can add some transactions and then try to shrink
Make sure you read the above advice. There are good items and this might not be a single command to run that fixes this.
May 25, 2018 at 5:43 am
This was removed by the editor as SPAM
May 29, 2018 at 10:25 am
Steve Jones - SSC Editor - Monday, May 21, 2018 9:27 AMOne thing to keep in mind in full mode that if you have the active part of the log at the end of the log, the file won't shrink. There are scripts (like this http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30460/) that can add some transactions and then try to shrinkMake sure you read the above advice. There are good items and this might not be a single command to run that fixes this.
+1
...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply