Post reply

transaction log size

  • January 22, 2007 at 8:25 pm

    #115906

    hi,

    I am not a database guy, but I've been kind of forced into this role now.

    I find that one of the database's transaction log is taking up 20 GB of disk space! It is not a constantly changing database. So database backups (full) are only performed once every week using Windows native backup utility. And transaction logs are never backed up.

    I googled and found two methods to reduce the size of the transaction log and free up disk space.

    1. By using the DBCC SHRINKFILE utility. If I use this, what should I realistically set as the target size?

    2. By using the sp_detach_db and sp_attach_db system stored procedures as mentioned in the following article:

    http://www.databasejournal.com/features/mssql/article.php/1460151

    this sounds much easier. What are your thoughts on this method?

    I anyway went ahead and did a back up of the transaction log of this particular database. But it did nothing to change the size of the transaction log. The back up file (of the transaction log) was 2GB lesser than the original log, ie it was 18 GB. Is this how it is supposed to work?

    I thank you in advance for all your suggestions.

    Thanks.

  • January 22, 2007 at 10:41 pm

    #684847

    SQL Server has 3 recovery models - simple, bulk-logged and full.  Depending on your data recovery needs, you need to choose the recovery model that is most suitable for you.  You can read more about this in the SQL Server documentation - search on the topic 'recovery model'.

    The choice of recovery model determines what sort of data are stored in your database transaction logs.  In a simple recovery model, the transaction log stores as much data as it needs to rollback any active transactions.  Once a transaction has been committed, SQL Server can reuse the space that was occupied by the previous transactions.

    In a full or bulk-logged recovery model, SQL Server stores as much data as it needs to restore all transactions since the last full backup.  Thus, the transaction logs will keep growing, until you perform a transaction log backup.  Only after that has been done will SQL Server reuse the existing space.  That appears to be your situation here.

    There are advantages and disadvantages to the different recovery models, but you need to know your own needs first before deciding on one.

    Peter Yeoh
    Developer
    Red Gate Software

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply