Home / server / Questions / 167952
Accepted
Max
Asked: 2010-08-07 12:39:08 +0800 CST

After upgrade to SQL Server 2008, database log file grows disproportionaly

  • 772

After upgrading from SQL2005 to 2008 SP1, we're suddenly observing disproportional expansions of the database log file during large transactions.

Example: inserting 75 million rows into table B, deleting those same 75M rows from table A, both in the same transaction. Total amount of data about 2 times 20GB, but database log file grows to 150GB and beyond.

Never seen this in SQL2005, before upgrading.

I couldn't find this documented as a known regression in SQL2008 anywhere.. anyone seen this, too?

Thanks

Max

sql-server sql-server-2005 sql-server-2008

4 Answers

  1. Best Answer

    You can always use fn_dblog() to analyze the log content and understand where does the increase come from. With simple recovery, the log might roll over and not be able to analyze, so for analysis purposes I'd recommend changing recovery to full, taking a dummy full backup to /dev/nul (backup database ... to disk='nul:') and re-running your transaction. If possible, mark down the transaction ID (look in sys.dm_tran_active_transactions) then look into the select ... from fn_dblog(null, null) where [TransactionID] = 'xactid' output and see what kind of records are generated so your log grows to 150Gb. The Reserved space is the relevant column for you. Eventually, if possible, compare with same on SQL 2005.

    After that, you can revert the recovery model back to simple. Note though that the log reservation behavior might be different under simple than under full, but we'll cross that bridge when we get to it if it happens.

    • 2
    • 0

  3. Your transaction log may be set to auto-grow. Turn that setting off and set your log file at a fixed size.

    • 0

  4. This is because your log file is logging everything, what i would recommend is trimming the database log files via the Shrink database task. Remember to do that after you have done a backup.

    Tsql to do it:

    USE [N2CMS]
GO
DBCC SHRINKDATABASE(N'MyDataBase', 10, TRUNCATEONLY)

    Depending on how you take backups, your backup software should be able to do this

    • -2