Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Microsoft SQL Server 2005

I want to reduce the initial size of a database (to a size below what it is currently) and then back up the log file. Is this a safe way of reducing the size of the log? Doing a log backup without reducing the initial size has not yielded any free space for me.

This is not a system database, but a user-created and populated one.

share|improve this question
    
Which database are you talking about? It's essential to know, try to fill up more detail in your question –  lynxman Jan 25 '11 at 16:46
    
It's a user-created database. –  Aushin Jan 25 '11 at 16:47
    
Sure... MySQL, Oracle, Postgres, sqlite, berkeley DB, etc? As said try to provide DB type, version and all this information, it'll be really useful –  lynxman Jan 25 '11 at 16:50
add comment

2 Answers

Not sure if this is the correct direction, but wouldn't log rotate do this for you? I'm assuming you are running Linux. Basically, if would copy over the log file, compress it, and then remove the log file (or truncate it depending on the configuration of logrotate), and continue to write the log file and everything pre-processed while the compression was taking place after the compression or HUP; again this is logrotate on Linux.

If you are running windows, I'm sure you can script something, unless this is something that windows can handle, but overall, you are probably looking to compress the log file, and then start writing a new copy to your log, daily/weekly/monthly (depending on your requirements).

share|improve this answer
    
I just saw your update regarding Microsoft SQL Server 2008 R2 - so you can probably disregard my answer. –  drewrockshard Jan 25 '11 at 16:55
    
Yeah, it's Windows Server 2003R2 –  Aushin Jan 25 '11 at 17:01
add comment

When the database recovery model is set to FULL or BULK-LOGGED, the database and the transaction log are handled completely separately.

Thus, backing up the database does not free up the log; you must still back up the log as well to re-use it.

Vice versa, if the log file is too large for your liking - but see below - resizing the data files does nothing; you must explicitly back up and then shrink the physical log.

HOWEVER, do not use auto-grow just because it looks convenient! There are serious penalties for having a multi-gigabyte log file that was grown in 10MB increments, well-documented at the microsoft website.

In a nutshell, growing or adding to the log will divide the space added into a number of new virtual logs, from a few to a dozen - every single time this happens.

When your transaction log has grown from 10MB to 1GB in 10MB increments, chances are it will be fragmented into a thousand virtual logs; this hurts performance.

My advice is to calculate - or observe - how much transaction log you need between log backups, double that, and set your log to that size.

Then disable autogrowth and implement monitoring on log utilization so it warns you when it gets above 90% or so.

It is far better to manually add a few 100MB every once in a while, thus keeping the fragmentation to a minimum.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.