SQL transaction log very large

[mbell]

I am working on an issue with a customer's Unity server where the SQL transaction log (UnityDb_log.LDF) has grown to over 4.5GB and caused their system to run out of disk space on that partition. It turns out that the Nightly and Weekly backup jobs were failing because the account that owned the backup jobs didn't have permissions to run them (I'm still investigating why because it was the account used to install Unity in the first place).

I re-configured the owner to be the SQL "sa" account and re-ran both backup jobs manually. They both completed successfully but the UnityDb_log.LDF file is still over 4.5GB. I thought the backup jobs were supposed to truncate that file. I have never seen a system where the log has gotten that large. This system only has about 200 users.

What can I do to shrink the transaction log down to a more reasonable size and prevent this kind of problem from happening again?

[Hin Lee]

Open Enterprise Manager, right-click on the database, select properties

Go to the Options tab and make sure that Simple Recovery is selected for Recovery Models. (If Full Recovery is selected, then it would not be possible to truncate the file which is the next step)

Then right-click on the database again and select Shrink Database. On the "Shrink Database" window, Click on FILES button

Select the transactional log from DATABASE FILE, and select the option TRUNCATE FREE SPACE FROM THE END OF THE FILE on the SHRINK ACTION choices. After shrinking, the Current Size and Space Used should decrease significantly.

H. M.