0

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.

3
  • Which database are you talking about? It's essential to know, try to fill up more detail in your question
    – lynxman
    Jan 25, 2011 at 16:46
  • It's a user-created database.
    – Aushin
    Jan 25, 2011 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, 2011 at 16:50

2 Answers 2

1

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.

0

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).

3
  • I just saw your update regarding Microsoft SQL Server 2008 R2 - so you can probably disregard my answer. Jan 25, 2011 at 16:55
  • Yeah, it's Windows Server 2003R2
    – Aushin
    Jan 25, 2011 at 17:01
  • Old, I know, but just for completeness: The log in question is most probably the transaction log of the database. You can't handle this with logrotate. Jul 30, 2018 at 12:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .