May 5, 2009 at 7:11 am
Bit of an odd problem here.
History:
- We have a database which was in FULL recovery mode and its Transaction Log had grown to over 90GB - so someone set it to SIMPLE recovery mode, thinking the Log would get truncated.
- The Log did not truncate and all further attempts to truncate the Log would not reduce it below 90GB
- Tried turning FULL recovery mode back on and truncating, same result. Various approaches were tried and nothing would make the log shrink.
- During this process a secondary log file was added.
As nothing worked then we resorted to detaching the database, removing the offending log file and then reattaching - SQL Server should recreate an empty log...but this didt work. I beleive this approach would work provided there was only 1 log file but now that we have 2 this fails.
Does anyone know how we can get rid of the second log file and/or the main log file?
TIA
Adam
May 5, 2009 at 7:25 am
TRUNCATE and SHRINK are two different things.
TRUNCATE would truncate unused log segments but wouldn't affect TLog size.
SHRINK would reduce the size of TLog to a size not smaller than it was originally created.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 5, 2009 at 7:32 am
Yes sorry, I should have used the word Shrink instead of truncate in my post - the log file wont shrink below 90GB.
May 5, 2009 at 7:49 am
Use the following script to create the stored procedure "sp_forcelogtoshrink".
You can speicify the new log size by setting the @NewSize value in the script before createing the procedure
[Code]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[sp_ForceLogToShrink] AS
SET NOCOUNT ON
DECLARE @LogicalFileID INT,
@MaxMinutes INT,
@NewSize INT
SELECT @LogicalFileID = (SELECT fileid FROM sysfiles WHERE status & 0x40 = 0x40),
@MaxMinutes = 20, -- Limit on time allowed to wrap log.
@NewSize = 256 -- in MB
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE fileid = @LogicalFileID
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE fileid = @LogicalFileID
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileID, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE fileid = @LogicalFileID) -- the log has not shrunk
AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE fileid = @LogicalFileID
DROP TABLE DummyTrans
SET NOCOUNT OFF
[/Code]
Now backup the log and then execute the stored procedure fromt the database in question. This should force the log to shrink to the desired size. If I could remember where I got this script from or who wrote it I would definitely have given credit but I have had it for so long that I cannot remember.
Regards,
Jason P. Burnett
Senior DBA
May 5, 2009 at 8:09 am
Thanks for that but this wont work in this case - we have already tried shrinking and truncating the Log - and it will not go below 90GB.
I think the solution we need involves somehow dropping the existing log files and recreating a new, reasonably sized Tlog file.
Adam
May 5, 2009 at 8:14 am
Hi Adam,
Have you actually tried this procedure? I have yet to run into a situation where this would not force shrink the log. I have encountered many scenarios where I could not shrink a log normally but this has always worked for me. Not to say that it will definitely work for you but it always has for me. 🙂
Regards,
Jason P. Burnett
Senior DBA
May 5, 2009 at 9:03 am
No I didnt try it - but I did read carefully through what it was going to do - the log file will not shrink below 90gb and we have tried all variations of shrinking and truncating.
I will try it though at a quiet time though - just incase theres some magic in there that Im not seeing 🙂
May 5, 2009 at 9:09 am
Thanks Adam, I'm not trying to be pushy I have just had good luck with that procedure. Hope it works! Let me know how it goes.
Regards,
Jason P. Burnett
Senior DBA
May 5, 2009 at 9:28 am
what does the command dbcc loginfo(yourdbname) return?
If the status field of the last row (and therefore that last virtual log) has a value of 2 it has active transactions in it and you won't be able to shrink past that point till it wraps round, so use the procedure you were given but take a full backup afterwards
Is the database replicated or was it ever, that may be preventing it shrinking. Is the database the principal in a mirrored pair, you won't be able to shrink it.
what does dbcc opentran return, perhaps you ahve a rogue transaction.
are you using shrink database or shrinkfile, always use shrinkfile.
the above are the only things I have come across that prevent shrinking a log file.
get rid of the second tran log by using the emptyfile option of shrinkfile followed by alter database modify file
edited to replace dbcc logspace(yourdbname) with dbcc loginfo(yourdbname)
---------------------------------------------------------------------
May 5, 2009 at 9:31 am
How full is the transaction log? If there's 90GB of log data in there, it won't shrink below that.
Use DBCC SQLPERF(LogSpace) to see what percentage of the log is full. Also check the log_reuse_wait_descr column in sys.databases to see if anything's preventing the log from truncating when log backups are run.
You are running regular log backup, aren't you?
Just be aware that if you run Jason's script, you are breaking the log chain (if you're taking regular log backups) and hence it's necessary to take a full backup as soon as possible to restart the log chain. Until you do so, you will not be able to take log backups and you will no be able to restore to any point after that, should you need to.
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 5, 2009 at 9:36 am
atoth (5/5/2009)
Yes sorry, I should have used the word Shrink instead of truncate in my post - the log file wont shrink below 90GB.
What was the original size of the TLog file?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 5, 2009 at 10:00 am
GilaMonster (5/5/2009)
How full is the transaction log? If there's 90GB of log data in there, it won't shrink below that.Use DBCC SQLPERF(LogSpace) to see what percentage of the log is full. Also check the log_reuse_wait_descr column in sys.databases to see if anything's preventing the log from truncating when log backups are run.
You are running regular log backup, aren't you?
Just be aware that if you run Jason's script, you are breaking the log chain (if you're taking regular log backups) and hence it's necessary to take a full backup as soon as possible to restart the log chain. Until you do so, you will not be able to take log backups and you will no be able to restore to any point after that, should you need to.
Good point Gail! I definitely should have mentioned that. I re-read the origianal post and realized that it looks like they only temporarily switched to simple recovery. I should have payed more attention. Thanks for covering me. 🙂
Regards,
Jason P. Burnett
Senior DBA
May 5, 2009 at 10:08 am
..er, previous post of mine should have read dbcc loginfo(yourdbname), edited it
---------------------------------------------------------------------
May 5, 2009 at 12:22 pm
Big thanks to Jason, george, Gail and Paul - I think Im getting somewhere.. 🙂
log_reuse_wait_desc shows : REPLICATION
dbcc opentran shows:
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (249012:7272:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
- So Im guessing that someone has tried to set up replication on this database at some point. It is not replicated now and there is no mirroring and no log shipping.
Im thinking that I will need to run something like the following to clear this up, can anyone confirm?:
EXEC sp_dboption 'DatabaseName', 'Publish', 'true'
GO
EXEC sp_repldone..................
GO
EXEC sp_dboption 'DBName', 'publish', 'false'
Adam
May 5, 2009 at 1:02 pm
Create a transactional replication publication with one table in it (totally irrelevance what table)
Drop the publication
Right click Replication (in management studio's object explorer)
Select Disable Publishing and distribution (providing no other DB on this server is replicated)
The log should now truncate on the next checkpoint/log backup (depending what recovery model you're in)
Once it has, shrink the log to a reasonable size. Not down to nothing, just to a reasonable size for the activity that you have.
If you're in full recovery, take a full backup (I'm guessing you've truncated the log several times) and set up regular log backups.
My guess - that DB was restored from a backup of a published DB. That half-configured replication is sometimes the result of that.
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply