Friday, February 17, 2012

Empty trans log in Simple model

I have a database SQL Server 2005 Express, that's
using the Simple recovery model.
There's quite a big transaction log for this database,
how do I empty it when it's the Simple model?
Besides, the log file hasn't been updated (acc. to
the Windows file date) since yesterday while the MDF
file has. Is this normal?Run DBCC SQLPERF (logspace) on the instance to see if any of the space is actually being used. If it is, run DBCC OPENTRAN in the database to see who has the open transaction.

If the space is genuinely empty, and you do not suspect there are any giant transactions that run on this thing, you can safely shrink back the log. I find the Windows last updated dates generally doesn't mean a lot when it comes to database files.|||I couldn't manage to shrink the file. I tried setting the
initial file size down to 2MB but it remained 15MB as
it was (without any error message).
Finally, I re-created the database instead, it had
very little content anyway so it was quite easy.

No comments:

Post a Comment