ansaurus

Question

Huge transaction log with SQL Server database in simple recovery mode

Answer 1

+1 A:

You're missing an argument in dbcc shrinkfile:

dbcc shrinkfile (MyDatabase_log, 20000, TRUNCATEONLY)

NOTRUNCATE is the default, which moves allocated blocks to the beginning of unallocated space. TRUNCATEONLY removes the unallocated space. So if you do a NOTRUNCATE followed by a TRUNCATEONLY, you get one slimmed down log.

Eric 2009-07-28 18:34:02

I did try that. It didn't change anything. I did add a second log file to get the batabase back online when the space ran out. Maybe that's part of it?

Eric 2009-07-28 18:40:55

@Eric (Good name, btw): Make sure then, that you're shrinking the right log. If you have two log files, you'll need to shrink both independently. Check in the Files section of the database properties for what they're called. I think the default is `MyDatabase_log_1`, but I'm going off the top of my head.

Eric 2009-07-28 18:45:18

Answer 2

+2 A:

It means you once had a single transaction that lasted for so long that it forced the log to grow 410GB. The log cannot be reused if there is an active transaction since the rollback information cannot be erased. Such an example would be if someone open an SSMS query, starts a transaction, updates a record and then goes in vacation. The transaction will be active and force the log to grow until is eventually committed or rolled back. When the transaction eventually ends the used space can finally be reclaimed, leaving a huge empty log file.

Another scenario is if you had about 200GB of data updated in a single transaction. The log will store the before and after images of the changes thus consuming twice the space, and it cannot be reused, again because is all one single transaction.

Update

I neglected to mention Replication that is also a factor that can prevent log truncation. And so is Mirroring, an distributed transaction (technically that is the same as an 'active transaction', but the DTC implication makes it a distinct case). The complete list and explanations is at Factors That Can Delay Log Truncation.

Remus Rusanu 2009-07-28 18:44:00

It wouldn't show as unallocated space if it were mid-rollback/transaction, would it?

Eric 2009-07-28 18:46:15

Thanks! This sounds very possible. Do you know what can be done to remove the log if it's in this locked state?

Eric 2009-07-28 18:47:27

If it shows as unallocated the space must be already claimed back by the SIMPLE recovery mode. Does the `log_reuse_wait_desc` column of sys.databases for the database in question says `NOTHING` or not?

Remus Rusanu 2009-07-28 18:51:26

CHECKPOINTNOTHINGACTIVE_TRANSACTIONNOTHINGREPLICATIONNOTHINGNOTHINGNOTHING

Eric 2009-07-28 18:55:38

@Remus: That's what I thought. Since 99% of his space is unallocated, I figured there wasn't a hanging transaction. He's obviously confirmed that, though :)

Eric 2009-07-28 18:58:34

@Eric can you restrict that only to the database of interest, not your entire server? There is at least one `REPLICATION` entry, do you have replication off the database in question?

Remus Rusanu 2009-07-28 19:03:47

damn, there are two Eric lol.

Remus Rusanu 2009-07-28 19:04:48

@Eric (OP) from the reply to no_one is clear you have replication from the database. Replication is another one of them log shrink/reuse restriction factors. You need all your subscribers to catch up, the server keeps track of who has received what in replication. If a subscriber is behind, it will cause the log to grow, same as an active transaction.

Remus Rusanu 2009-07-28 19:07:40

I see. I bet this is the problem since I'm seeing lots of errors in my replication subscriber database. I'll try to turn off replication and see if that allows me to shrink the file.

Eric 2009-07-28 19:30:52

@Remus: I may have misinterpreted your explanation but I thought Replication would only delay truncation of the Publishers transaction log if the transaction had not been committed to the Distributor database (as opposed to the Subscriber)?

John Sansom 2009-07-28 19:39:52

Eric 2009-07-28 19:44:13

@John: I think you are right. I'm not an expert in Replication, but it makes sense that only the distributor needs to read the log for transaction based replication, not each subscriber.

Remus Rusanu 2009-07-28 19:49:00

@Remus: Hmmm I wonder then if the posters distribution database was the root cause, perhaps a space issue there...

John Sansom 2009-07-28 20:24:55

@John: yes, replication can grow the log as such if the distributor is not reading the log: either the agents don't run (schedule is busted usually due to operator intervention) or they run but can't connect to the publisher.

Remus Rusanu 2009-07-28 20:47:13

Answer 3

A:

If you have only one mdf file and one log file, perhaps the simplest way will be to detach the database, rename the log and reattach the database. SQL Server will create a new log file. After that your huge log file can be safely deleted.

This though will not work if you have multiple data files.

no_one 2009-07-28 18:47:11

Thanks. I did try this but it wouldn't let me detach because the database acts as a replication publisher.

Eric 2009-07-28 18:49:58

Answer 4

A:

Replication Publisher? Could this be the reason for the huge transaction log?

2009-09-14 07:06:14

ansaurus

tags:

views:

answers:

Huge transaction log with SQL Server database in simple recovery mode

related questions