Shrink that MSSQL transaction log ...

Zoom to a few weeks back from now. The IT manager of the company for whom we had done an MCMS (Microsoft Content Management Server) implementation called up.... " Yo ma....an! the admin site is going crazy. My editors are yelling blue murder. They are unable to approve changes to existing postings or add new postings."

They were getting the exception "SQLException: Timeout expired when approving CMS pages" when approving the postings.

Now i was a wee bit worried.... it was 18 months since we had implemented the MCMS solution. The sites had been working like clockwork till date. The original implementation had 5 sites using a set of standard templates. The IT team had successfully added another site using the same templates. They were planning to add the seventh for an upcoming launch. But now a problem had surfaced.. anything could have happened, somebody may have decided to have a go at .NET and decided to tweak the code a bit, who knows....

The manager did say that the public facing sites were not affected (phew...! ). Further investigation (cutting out all the "censored" stuff...) revealed that the problem originated in the code block used for a custom search implementation. This involved another MSSQL database called "SupportDB" (not the MCMS database). Ha! the culprit has been isolated...

So there i was peeking & poking at the various properties of this "SupportDB", then it hit me that the Transaction log was unusually big (4 gig... that is BIG...!) Then i began to notice few more things... the transaction log was set to
-Automatically grow file
-File growth by 10%
-Unrestricted file growth
hmmm.... excellent recipe for disaster.... a potential "TIMEBOMB"

To summerise, my dear watson, the transaction log(4 gig) must have been full, so when it was time to insert/update another record, MSSQL tries to increase the log file by 10% i.e., 400 MB. It requires some time to do that.. add to this the fact that the hard disk is severly fragmented and hence the timeout... (Applause in the background)

Now it is time to bring a swift end to this unholy saga..

-First check up on the database by running
sp_helpdb [database name] this reveals the
names/paths of the data files and log files, the fileID, file size etc..
"D:\Work\Data\MYDB_Data.MDF " & "D:\Work\Data\MYDB_Log.LDF " .
Remember the FileID of the Log file we will use it.

-Second set the truncate log on check point option
sp_dboption [database name], 'trunc. log on chkpt.', 'true'

-Finally trim the excess FAT
dbcc shrinkfile([LogFiles_FileID],TRUNCATEONLY)
e.g: dbcc shrinkfile([MyDB_Log],TRUNCATEONLY)

And voila, it is done.... now the log file should have shrunk to something smaller... most likely to 1 MB.

To diffuse the bomb, modify the database properties to...
-Automatically grow file
-File growth in Megabytes (say 1MB)
-Restrict file growth to (say 20 MB).
You may want to increase this if you are performing a large data import or some other maintenance activity, else even 20MB seems like overkill to me.

---

What is Content Management System - CMS ?

Compare Content Management Systems

---

Disclaimer : You use this web site, software, advice 100% at your own risk.