Tuesday, January 01, 2008

Truncating your transaction log.

First, HAPPY NEW YEAR GUYS!!

Now, some actual blogging...

Here I was, trying to restore our metadata only database into a production server, getting constantly an error messaging saying that it was waaay to big for the available space. Well, my concern was that the entire back up of the database was only 6MB, and the database itself only contains tables with very basic data.

What is worst, the space available on that production drive was 14GB, so gosh, it simply didn't make any sense. Well, after reviewing the original metadata-only database I realized that our constant adding, updating and deleting of structures made our transaction log simply huge. (40gb)

So, after some reading I found this nice recipe to cut down the fat of your log database:

First, backup your existing log file by running
BACKUP LOG  TO DISK = ''

Now, shrink the transaction log, executing this
DBCC SHRINKFILE (, ) WITH NO_INFOMSGS

File name is the transaction log filename and target size you wanted to be, don't be too demanding on the shrinking, but as a hint, I ended up making mine only 1MB cause we don't need a log on our metadata db. :)


Update:
My apologies, the database in question is MSSQL 2005. Thanks for the feedback.

3 comments:

Anonymous said...

looks like your blog chewed up your commands..

BACKUP LOG [databasename] TO DISK = '[backupfile]'

DBCC SHRINKFILE ([filename], [targetsize]) WITH NO_INFOMSGS

Anonymous said...

For those amongst us who have been wondering: "what SQL DBMS is he talking about?": It is MS SQL Server.

See MSDN for more info:
Transact-SQL Reference

Anonymous said...
This comment has been removed by a blog administrator.

A painless self-hosted Git service

Remember how a part of my NAS setup was to host my own Git server? Well that forced me to review options and I stumble into Gitea .  A extr...