December 14, 2011 at 8:19 am
When I have a log file growing exponentially or if the log file is filled up, I do a log file backup usually. If there is a long running process, I investigate and find out what process is keeping the transaction open, then kill that SPID.
That being said, I was reading on this link how to shrink the log file.
http://www.sql-server-performance.com/2007/gen-tips/
"Change the database recovery mode of the database you want to shrink from “full” to “simple,” then truncate the log file by performing a full backup of the database, then switch back to the “full” recovery mode. By temporally changing from the Full recovery model to the Simple recovery mode, and then back, SQL Server will only keep the “active” portion of the log, which is very small."
Contributed by Tom Kitta.
Does anyone see a problem with this method?
Also, another DBA told me he does the same thing as Tom, but he doesn't do the "full backup of the database", instead he just truncates the log file after changed the DB to Simple. Does anyone see a problem with this as well?
Thank you in advance.
December 14, 2011 at 8:23 am
Abysmal advice (or extremelly last resort).
This is actually what you need to know.
http://www.sqlservercentral.com/articles/Administration/64582/
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
December 14, 2011 at 8:52 am
alanspeckman (12/14/2011)
Does anyone see a problem with this method?
Absolutely yes.
Also, another DBA told me he does the same thing as Tom, but he doesn't do the "full backup of the database", instead he just truncates the log file after changed the DB to Simple. Does anyone see a problem with this as well?
Yes.
They both qualify as mismanagement of the log. Full recovery model (which I assume the DB is in) is used when the database must be recoverable to any point in time. Switching to simple and back to full breaks the log chain and leaves you unable to do point in time restores. If you don't need point in time restores, then there's no reason at all to have the DB in full recovery at all.
Please go and read my two articles on transaction logs (that Ninja linked 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
December 14, 2011 at 1:50 pm
Thank you both, I had never heard of this method. but I assume it was a triage method when out of disk space, accepting the risk of loosing point-in-time restores since the last full. I wouldn't use that as a general practice. But I wanted to pass it on given the source was a respected website in the community.
Holiday Regards, 😉
Al.
December 14, 2011 at 2:17 pm
alanspeckman (12/14/2011)
Thank you both, I had never heard of this method. but I assume it was a triage method when out of disk space, accepting the risk of loosing point-in-time restores since the last full.
That's exactly what it is. It's the last resort for fixing an unmaintained log when you don't have space to take a backup of the log (the log backup will be around the same size as the log file if the log file is full). It's for an emergency situation, usually on an unmaintained database. It's not something that just gets done any time the log gets a little larger than it should.
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
December 14, 2011 at 2:33 pm
Ya and you can always take a backup to another drive or anywhere else on the network for that matter.
Then the shrink will work. Going to simple is realllllllllllly the last option.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply