3

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

  • Did your level of recovery change? – Dave Holland Aug 6 '10 at 21:32
  • No.. simple recovery. – Max Aug 6 '10 at 21:54
2

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.

0

A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server

This is for SQL 2005, but may also be relevant to 2008.

0

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

-2

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

  • In case you wonder why you get downvotes: sqlskills.com/blogs/paul/post/… – Remus Rusanu Aug 7 '10 at 18:30
  • So you run the task that either rebuild index, or defrag it. I stand by my reply, but thanks for letting me know why you down vote it. – EKS Aug 7 '10 at 19:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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