Log File

Question

Log File

david.alcock

SSCertifiable

Points: 6021
More actions
April 25, 2012 at 2:39 am

#254900

Just wanted to ask, if a log file has got too big and then subsequently a log backup has been taken, the recovery mode switched to simple but the log file has nto been shrank - could the actual size of the log file still cause issues in any way?
'Only he who wanders finds new paths'

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

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

Phil Parkin SSC Guru Points: 247037 More actions · Answer 1

david.alcock (4/25/2012)
Just wanted to ask, if a log file has got too big and then subsequently a log backup has been taken, the recovery mode switched to simple but the log file has nto been shrank - could the actual size of the log file still cause issues in any way?

Presumably other than running out of disk space?

david.alcock SSCertifiable Points: 6021 More actions · Answer 2

Well that was it, aprt from being a needlessly large file...wasnt sure if it would have any negative impact.

'Only he who wanders finds new paths'

Ant-Green SSC Guru Points: 113509 More actions · Answer 3

just one thing that I am not so sure on and might cause an issue

when you change from Simple to Full, before you can take a TX log backup you need to re-take a Full backup, thats a given

but I have never gone from Full to Simple, so unsure if it would require a a full backup to be taken to kick in that change to start marking the log as re-usable, unsure on this.

Phil Parkin SSC Guru Points: 247037 More actions · Answer 4

david.alcock (4/25/2012)
Well that was it, aprt from being a needlessly large file...wasnt sure if it would have any negative impact.

Any reason why you wouldn't shrink it? There might be a performance improvement if you do.

Just issue a

DBCC SHRINKFILE (N'<db>_log' , 1000)

command to shrink to 1GB (for example).

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

anthony.green (4/25/2012)
but I have never gone from Full to Simple, so unsure if it would require a a full backup to be taken to kick in that change to start marking the log as re-usable, unsure on this.

No.

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

david.alcock (4/25/2012)
Just wanted to ask, if a log file has got too big and then subsequently a log backup has been taken, the recovery mode switched to simple but the log file has nto been shrank - could the actual size of the log file still cause issues in any way?

I'd leave it alone unless it's absolutely huge - if you shrink it, then most likely it will grow again when you defragment/reindex your largest table/index

although the log will empty after the reindex/defrag it will still need enough space to be able to maintain the atomicity of the transaction... ditto for large deletes and inserts

MVDBA

david.alcock SSCertifiable Points: 6021 More actions · Answer 7

It was passed to me as an afterthought, I advised to shrink it and that was done last night. Now its simple recovery, wont be going back to full, I just wondered if leaving the file alone would cause problems...

Cheers for the replies all, as always!

'Only he who wanders finds new paths'