February 5, 2013 at 1:05 pm
Hey,
One of our databases on SQL 2008 had a problem with the daily transaction log backup. As a result the job never finished but also neither error'd.
As a result the log file is huge, 185Gb. I don't have enough room on the server to take a transaction log backup.
I know that truncate was removed and isn't brilliant to use anyway( from what I have found through research).
It seems my best option is to change the recovery mode of the database to simple, possible shrink the file (although I believe it will do that itself?) then switch back to full mode... This will then result in my log file reducing massively in size(should be about 2gb).
My problem with this is this particular database is in a merge replication, will this cause a problem when I switch recovery mode? Also should I expect the switch between modes to take a long time while it shrinks the transaction log?
From there on start log transaction backups and shrinks and.....
Put a repeating reminder in my calendar to check the transaction log backup job every week 😉
Reggie
February 5, 2013 at 1:51 pm
Replication has no dependency at all on recovery models.
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
February 5, 2013 at 2:30 pm
Thanks Gila,
This was as I suspected and is great news for my situation...
Would you consider my solution a sound solution to the scenario I am in..? I am currently trying to run a transaction log backup to a network store but it has been running for 5 hours now and the file is still 0kb on the network share!
I will give that over night to see if it goes anywhere then do the change on recovery model tomorrow using the following..
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE KLSTOCK
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (KLSTOCK_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE KLSTOCK
SET RECOVERY FULL;
February 5, 2013 at 2:37 pm
Do Not Shrink your log file to 1MB.
If you insist on shrinking, shrink to a sensible size.
As to whether that's an acceptable option, what's the data loss allowance on that database? If low, then maybe breaking the log chain isn't a great idea. Bear in mind you need a full or diff backup after switching back to full to reinit the log chain.
If your log backup succeeds, then switching to simple isn't necessary, the log backup would have truncated the log, you can just shrink it to a sensible value.
Please read through this - Managing Transaction Logs[/url]
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
February 5, 2013 at 2:49 pm
Yeah that was what was in the example I had found on it... 2gb is about fine for the size....
I am able to make a full backup prior to this and as a result of finding this problem have switched on hourly full backups so I can roll back for most of today... Past that Its not a problem.... The db is only 400Mb
If the backup works I won't have a need to do a recovery model switch, I understand that 🙂
Thanks
February 5, 2013 at 2:58 pm
Ps that document you linked is absolutely immense... Added to my briefcase...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply