Shouldn't the trans log backup be truncating my log file?

Question

Post reply

Shouldn't the trans log backup be truncating my log file?

SkyBox

SSCarpal Tunnel

Points: 4288
More actions
September 19, 2010 at 7:44 am

#228018

In the past (SQL 2005) I always dumped trans w/ truncate and dbcc shrinkfile each morning after my db backups. Now I want to create trans log backups and stay in full recovery.

In SQL 2008 the only way I have found to trunc the log is by switching to simple recovery (during production - SCARY) and then running dbcc shrinkfile - then back to full recovery.

Shouldn't the trans log backup be truncating my log file? I even set up a trans log backup maintenance plan through the GUI and that did not truncate my trans log either.

Would like to be creating trans log backup chains at 15 min. intervals at some point, but need to get this straightened out first.

Would appreciate any advice.

Viewing 15 posts - 1 through 15 (of 33 total)

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

Perry Whittle SSC Guru Points: 233792 More actions · Answer 1

hi

what does the following return when executed against the database in question



DBCC LOGINFO

Hint: scroll down the results and check for a status of 2 near the end of the output!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

SkyBox (9/19/2010)

Shouldn't the trans log backup be truncating my log file? I even set up a trans log backup maintenance plan through the GUI and that did not truncate my trans log either.

It'll be truncating the log (marking space in the log as reusable). It will not however be shrinking the log. Shrinking a log is a bad thing to do, you're just forcing it to grow again and causing poor performance when it does so as well as causing internal log fragmentation which slows down database recovery.

Please read through this - Managing Transaction Logs[/url]

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

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 3

Perry Whittle (9/19/2010)

hi

what does the following return when executed against the database in question

DBCC LOGINFO

Hint: scroll down the results and check for a status of 2 near the end of the output!

There are several rows returning the status of 2.

RESULTS:

FileId FileSize StartOffset FSeqNo Status Parity CreateLSN

2 253952 8192 20226 2 128 0

2 327680 262144 20227 2 64 0

2 262144 589824 20228 2 64 28000000010200061

2 262144 851968 20229 2 64 154000000008200011

2 262144 1114112 20230 2 64 20229000000013700001

2 262144 1376256 20231 2 64 20230000000008900010

2 262144 1638400 20232 2 64 20231000000015000004

2 262144 1900544 20233 2 64 20232000000015200001

2 262144 2162688 20234 2 64 20233000000014500021

2 262144 2424832 20235 2 64 20234000000015200001

2 262144 2686976 20236 2 64 20235000000011000045

2 327680 2949120 20237 2 64 20236000000015200001

2 327680 3276800 20238 2 64 20237000000026400001

2 393216 3604480 20239 2 64 20238000000027700012

2 393216 3997696 20240 2 64 20239000000040700002

2 458752 4390912 20241 2 64 20240000000039200001

2 253952 4849664 20242 2 64 20241000000015900036

2 270336 5103616 20243 2 64 20241000000015900036

2 253952 5373952 20244 2 64 20241000000060700037

2 335872 5627904 20245 2 64 20241000000060700037

2 253952 5963776 20246 2 64 20245000000028700002

2 335872 6217728 20247 2 64 20245000000028700002

2 253952 6553600 20248 2 64 20247000000028400007

2 401408 6807552 20249 2 64 20247000000028400007

2 253952 7208960 20250 2 64 20249000000041100012

2 466944 7462912 20251 2 64 20249000000041100012

2 253952 7929856 20252 2 64 20251000000054300002

2 253952 8183808 20253 2 64 20251000000054300002

2 278528 8437760 20254 2 64 20251000000054300002

2 253952 8716288 20255 2 64 20254000000017300007

2 253952 8970240 20256 2 64 20254000000017300007

2 409600 9224192 20257 2 64 20254000000017300007

2 253952 9633792 20258 2 64 20257000000042900007

2 253952 9887744 20259 2 64 20257000000042900007

2 475136 10141696 20260 2 64 20257000000042900007

2 262144 10616832 20261 2 64 20260000000055900002

2 262144 10878976 20262 2 64 20260000000055900002

2 262144 11141120 20263 2 64 20260000000055900002

2 327680 11403264 20264 2 64 20260000000055900002

2 262144 11730944 20265 2 64 20264000000026900007

2 262144 11993088 20266 2 64 20264000000026900007

2 262144 12255232 20267 2 64 20264000000026900007

2 393216 12517376 20268 2 64 20264000000026900007

2 327680 12910592 20269 2 64 20268000000040600004

2 327680 13238272 20270 2 64 20268000000040600004

2 327680 13565952 20271 2 64 20268000000040600004

2 327680 13893632 20272 2 64 20268000000040600004

2 327680 14221312 20273 2 64 20272000000027600009

2 327680 14548992 20274 2 64 20272000000027600009

2 327680 14876672 20275 2 64 20272000000027600009

2 458752 15204352 20276 2 64 20272000000027600009

2 393216 15663104 20277 2 64 20276000000053300005

2 393216 16056320 20278 2 64 20276000000053300005

2 393216 16449536 20279 2 64 20276000000053300005

2 393216 16842752 20280 2 64 20276000000053300005

2 393216 17235968 20281 2 64 20280000000040500007

2 393216 17629184 20282 2 64 20280000000040500007

2 393216 18022400 20283 2 64 20280000000040500007

2 589824 18415616 20284 2 64 20280000000040500007

2 458752 19005440 20285 2 64 20284000000078600014

2 458752 19464192 20286 2 64 20284000000078600014

2 458752 19922944 20287 2 64 20284000000078600014

2 524288 20381696 20288 2 64 20284000000078600014

2 524288 20905984 20289 2 64 20288000000066000010

2 524288 21430272 20290 2 64 20288000000066000010

2 524288 21954560 20291 2 64 20288000000066000010

2 524288 22478848 20292 2 64 20288000000066000010

2 524288 23003136 20293 2 64 20292000000060300173

2 524288 23527424 20294 2 64 20292000000060300173

2 524288 24051712 20295 2 64 20292000000060300173

2 720896 24576000 20296 2 64 20292000000060300173

2 589824 25296896 20297 2 64 20296000000104500007

2 589824 25886720 20298 2 64 20296000000104500007

2 589824 26476544 20299 2 64 20296000000104500007

2 786432 27066368 20300 2 64 20296000000104500007

2 655360 27852800 20301 2 64 20300000000117500001

2 655360 28508160 20302 2 64 20300000000117500001

2 655360 29163520 20303 2 64 20300000000117500001

2 851968 29818880 20304 2 64 20300000000117500001

2 720896 30670848 20305 2 64 20304000000130200006

2 720896 31391744 20306 2 64 20304000000130200006

2 720896 32112640 20307 2 64 20304000000130200006

2 917504 32833536 20308 2 64 20304000000130200006

2 851968 33751040 20309 2 64 20308000000142800009

2 851968 34603008 20310 2 64 20308000000142800009

2 851968 35454976 20311 2 64 20308000000142800009

2 851968 36306944 20312 2 64 20308000000142800009

2 917504 37158912 20313 2 64 20312000000130300002

2 917504 38076416 20314 2 64 20312000000130300002

2 917504 38993920 20315 2 64 20312000000130300002

2 983040 39911424 20316 2 64 20312000000130300002

2 983040 40894464 20317 2 64 20316000000155800004

2 983040 41877504 20318 2 64 20316000000155800004

2 983040 42860544 20319 2 64 20316000000155800004

2 1179648 43843584 20320 2 64 20316000000155800004

2 1114112 45023232 20321 2 64 20320000000194300002

2 1114112 46137344 20322 2 64 20320000000194300002

2 1114112 47251456 20323 2 64 20320000000194300002

2 1179648 48365568 20324 2 64 20320000000194300002

2 1245184 49545216 20325 2 64 20324000000194200004

2 1245184 50790400 20326 2 64 20324000000194200004

2 1245184 52035584 20327 2 64 20324000000194200004

2 1245184 53280768 20328 2 64 20324000000194200004

2 1376256 54525952 20329 2 64 20328000000207100002

2 1376256 55902208 20330 2 64 20328000000207100002

2 1376256 57278464 20331 2 64 20328000000207100002

2 1376256 58654720 20332 2 64 20328000000207100002

2 1507328 60030976 20333 2 64 20332000000232400009

2 1507328 61538304 20334 2 64 20332000000232400009

2 1507328 63045632 20335 2 64 20332000000232400009

2 1507328 64552960 20336 2 64 20332000000232400009

2 1638400 66060288 20337 2 64 20336000000257900012

2 1638400 67698688 20338 2 64 20336000000257900012

2 1638400 69337088 20339 2 64 20336000000257900012

2 1703936 70975488 20340 2 64 20336000000257900012

2 1769472 72679424 20341 2 64 20340000000296600004

2 1769472 74448896 20342 2 64 20340000000296600004

2 1769472 76218368 20343 2 64 20340000000296600004

2 1966080 77987840 20344 2 64 20340000000296600004

2 1966080 79953920 20345 2 64 20344000000347800004

2 1966080 81920000 20346 2 64 20344000000347800004

2 1966080 83886080 20347 2 64 20344000000347800004

2 2097152 85852160 20348 2 64 20344000000347800004

2 2162688 87949312 20349 2 64 20348000000373000013

2 2162688 90112000 20350 2 64 20348000000373000013

2 2162688 92274688 20351 2 64 20348000000373000013

2 2359296 94437376 20352 2 64 20348000000373000013

2 2424832 96796672 20353 2 64 20352000000423600027

2 2424832 99221504 20354 2 64 20352000000423600027

2 2424832 101646336 20355 2 64 20352000000423600027

2 2424832 104071168 20356 2 64 20352000000423600027

2 2621440 106496000 20357 2 64 20356000000434900062

2 2621440 109117440 20358 2 64 20356000000434900062

2 2621440 111738880 20359 2 64 20356000000434900062

2 2818048 114360320 20360 2 64 20356000000434900062

2 2883584 117178368 20361 2 64 20360000000514000008

2 2883584 120061952 20362 2 64 20360000000514000008

2 2883584 122945536 20363 2 64 20360000000514000008

2 3080192 125829120 20364 2 64 20360000000514000008

2 3211264 128909312 20365 2 64 20364000000564400028

2 3211264 132120576 20366 2 64 20364000000564400028

2 3211264 135331840 20367 2 64 20364000000564400028

2 3276800 138543104 20368 2 64 20364000000564400028

2 3538944 141819904 20369 2 64 20368000000600800075

2 3538944 145358848 20370 2 64 20368000000600800075

2 3538944 148897792 20371 2 64 20368000000600800075

2 3604480 152436736 20372 2 64 20368000000600800075

2 3866624 156041216 20373 2 64 20372000000665800052

2 3866624 159907840 20374 2 64 20372000000665800052

2 3866624 163774464 20375 2 64 20372000000665800052

2 3997696 167641088 20376 2 64 20372000000665800052

2 4259840 171638784 20377 2 64 20376000000743300114

2 4259840 175898624 20378 2 64 20376000000743300114

2 4259840 180158464 20379 2 64 20376000000743300114

2 4390912 184418304 20380 2 64 20376000000743300114

2 4718592 188809216 20381 2 64 20380000000821400002

2 4718592 193527808 20382 2 64 20380000000821400002

2 4718592 198246400 20383 2 64 20380000000821400002

2 4718592 202964992 20384 2 64 20380000000821400002

2 5177344 207683584 20385 2 64 20384000000544200296

2 5177344 212860928 20386 2 64 20384000000544200296

2 5177344 218038272 20387 2 64 20384000000544200296

2 5242880 223215616 20388 2 64 20384000000544200296

2 5701632 228458496 20389 2 64 20388000000968900085

2 5701632 234160128 20390 2 64 20388000000968900085

2 5701632 239861760 20391 2 64 20388000000968900085

2 5767168 245563392 20392 2 64 20388000000968900085

2 6291456 251330560 20393 2 64 20392000001088300012

2 6291456 257622016 20394 2 64 20392000001088300012

2 6291456 263913472 20395 2 64 20392000001088300012

2 6291456 270204928 20396 2 64 20392000001088300012

2 6881280 276496384 20397 2 64 20396000001192700002

2 6881280 283377664 20398 2 64 20396000001192700002

2 6881280 290258944 20399 2 64 20396000001192700002

2 7012352 297140224 20400 2 64 20396000001192700002

2 7602176 304152576 20401 2 64 20400000001314400055

2 7602176 311754752 0 0 0 20400000001314400055

2 7602176 319356928 0 0 0 20400000001314400055

2 7602176 326959104 0 0 0 20400000001314400055

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 4

GilaMonster (9/19/2010)

SkyBox (9/19/2010)

Shouldn't the trans log backup be truncating my log file? I even set up a trans log backup maintenance plan through the GUI and that did not truncate my trans log either.

It'll be truncating the log (marking space in the log as reusable). It will not however be shrinking the log. Shrinking a log is a bad thing to do, you're just forcing it to grow again and causing poor performance when it does so as well as causing internal log fragmentation which slows down database recovery.

Please read through this - Managing Transaction Logs[/url]

If shrinking the log is s bad thing, then how do you recommend keeping the transaction log from hitting 100gb's and higher? I just want to keep the trans log at a manageable size.

Thanks guys

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

Looks like something's keeping the log active.

What's the value of log_reuse_wait_desc in sys.databases for this database?

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

SkyBox (9/19/2010)

If shrinking the log is s bad thing, then how do you recommend keeping the transaction log from hitting 100gb's and higher? I just want to keep the trans log at a manageable size.

Regular log backups.

There's nothing wrong with a 100GB tran log, if that's what the database needs based on activity and log backup frequency. Last big DB I worked on had a 250GB log. It regularly hit 80% full during the overnight jobs.

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

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 7

SkyBox

SSCarpal Tunnel

Points: 4288

September 19, 2010 at 11:45 am

#1223673

GilaMonster (9/19/2010)

Looks like something's keeping the log active.

What's the value of log_reuse_wait_desc in sys.databases for this database?

log_reuse_wait_desc

DATABASE_SNAPSHOT_CREATION

NOTHING

LOG_BACKUP

DATABASE_SNAPSHOT_CREATION

NOTHING

LOG_BACKUP

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

SkyBox (9/19/2010)

GilaMonster (9/19/2010)

Looks like something's keeping the log active.

What's the value of log_reuse_wait_desc in sys.databases for this database?

log_reuse_wait_desc

DATABASE_SNAPSHOT_CREATION

NOTHING

LOG_BACKUP

DATABASE_SNAPSHOT_CREATION

NOTHING

LOG_BACKUP

LOG_BACKUP

What's the value for this database? (The one that you did the LogInfo for)

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

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 9

GilaMonster (9/19/2010)

SkyBox (9/19/2010)

If shrinking the log is s bad thing, then how do you recommend keeping the transaction log from hitting 100gb's and higher? I just want to keep the trans log at a manageable size.

Regular log backups.

There's nothing wrong with a 100GB tran log, if that's what the database needs based on activity and log backup frequency. Last big DB I worked on had a 250GB log. It regularly hit 80% full during the overnight jobs.

So for optimal performance, do you recommend hourly log backups? I plan on setting up trans log backup chains at 15 minute intervals when I get some to test this backup/restore method thoroughly.

With my old server I was molded into shrinking the log because I only had 50 gb's to spare on the log partition and not much more on the db partition. If I didn't shrink then the log drive would run out of space - at 2am or something.

On my new setup, I have a 500gb log partition and 2.6 terabyte data partition. I want to get things right now.

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 10

GilaMonster (9/19/2010)

SkyBox (9/19/2010)

GilaMonster (9/19/2010)

Looks like something's keeping the log active.

What's the value of log_reuse_wait_desc in sys.databases for this database?

log_reuse_wait_desc

DATABASE_SNAPSHOT_CREATION

NOTHING

LOG_BACKUP

DATABASE_SNAPSHOT_CREATION

NOTHING

LOG_BACKUP

LOG_BACKUP

What's the value for this database? (The one that you did the LogInfo for)

Those results were from the same db. Here is the statement:

use databaseName (same as LogInfo)

select log_reuse_wait_desc from sys.databases

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 11

SkyBox (9/19/2010)

So for optimal performance, do you recommend hourly log backups?

Log backup frequency has nothing to do with performance. It has to do with data loss policies and desirable restore abilities.

If you're in full recovery and doing log backups it means that you need the ability to restore a database to the point of failure in case of a disaster. The interval between log backups should be determined by:

1) Activity in DB and desirable log size

2) Maximum allowable data loss in the case of a disaster.

If the system cannot be allowed to lose more than 30 min of data, no matter what, your log backup interval must be at most 30 minutes, it can be less.

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 12

SkyBox (9/19/2010)

Those results were from the same db. Here is the statement:

use databaseName (same as LogInfo)

select log_reuse_wait_desc from sys.databases

sys.databases returns 1 row for each database on the server, so no it is not for the same database, you've given me the log reuse reasons for every single database on the server without any way of telling which database is which.

A database can only have ONE log reuse reason at a time.

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

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 13

SkyBox

SSCarpal Tunnel

Points: 4288

September 19, 2010 at 12:01 pm

#1223681

What's the value for this database? (The one that you did the LogInfo for)[/quote]

This is the statement I am executing:

use dataBase (same db s LogInfo)

select log_reuse_wait_desc from sys.databases

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 14

The sys.databases view is a server-wide view that contains one row for each database on the server. It returns the same no matter what database context you are in.

That list you've given me is useless because it's the log reuse for each individual database on the server and, since you did not include any other columns nor do I know what the name of the database that you're working with is.

What I need to attempt to diagnose the cause of the large active log is the specific log reuse wait for the specific database in question. Since sys.databases is a server-scoped view, USE database is not going to change its output.

May I suggest that you take a minute to look in books online for the sys.databases view to familiarise yourself with it before continuing.

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