Transaction logs

Question

Transaction logs

ericwenger1

Hall of Fame

Points: 3436
More actions
February 28, 2018 at 9:46 am

#403147

We had an issue where our transaction log backup job became disabled over the weekend and didn't find out until Monday morning. Now the logs are taking up a lot of space that we would like to reclaim now that we've got the
Transaction log backup job running. In test environments i've: 1) changed the recovery model to simple, 2) dbcc shrinkfile(database_log,1000), 3) changed the recovery model back to full, 4) take a full backup of the database,
5) then start the transaction log backup job.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Do step 2 alone. That'll fix the excessive space. Once-off shrinks of the log aren't harmful providing you don't shrink to 0 and you have a sensible growth increment

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ericwenger1 Hall of Fame Points: 3436 More actions · Answer 2

ericwenger1

Hall of Fame

Points: 3436

February 28, 2018 at 12:15 pm

#1981531

Thanks

ericwenger1 Hall of Fame Points: 3436 More actions · Answer 3

I was always thought that I had to change the recovery model to simple before I could do step 2.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Not unless you want to break the log chain, compromise the DB's recoverability in the case of a disaster.

Restarting the log backups would have cleared the log, allowing you to shrink. The only thing that a switch to Simple gets you is a broken log chain.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101123 More actions · Answer 5

Also, you need to either:
1) check the number of logical log files
2) shrink the log as much as possible, then immediately reallocate it in large chunks.
2) is easier to do, since it requires less tech know-how:

DBCC SHRINKFILE(2);
ALTER DATABASE <db_name> MODIFY FILE ( NAME = database_log, SIZE = 512MB );
ALTER DATABASE <db_name> MODIFY FILE ( NAME = database_log, SIZE = 1024MB );

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Ivan R. SSChasing Mays Points: 628 More actions · Answer 6

hello -- adding a few more bits of info

1. backup the tlog then shrink is enough in your case
2. reason why it makes sense to grow tlog manually in increments: VLFs
3. in case you cannot backup to disk for whatever reason because of unusually large TLOG, its better to backup the tlog to NUL: device (aka truncate_only) than switch to simple recovery (in case you're using AAG or mirroring, you don't need to break it)