Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 3 down vote favorite
1

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

share|improve this question
Did your level of recovery change? – Dave Holland Aug 6 '10 at 21:32
No.. simple recovery. – Max Aug 6 '10 at 21:54

4 Answers

up vote 2 down vote accepted

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.

share|improve this answer
Will do this analysis.. thanks! – Max Aug 9 '10 at 14:52
You could also analyse the log backup with this apexsql.com there is a free trial and not to difficult to use. There is a guide/review here too sql-server-performance.com/software/review/apex_log_p1.aspx There are other tools that do the same but I couldn't find one with a free trial last time I needed to do this (about 4 months ago) Cheers – Ally Reilly Aug 10 '10 at 15:57
up vote -2 down vote

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

share|improve this answer
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
up vote 0 down vote

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.

share|improve this answer
up vote 0 down vote

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

share|improve this answer

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.