April 25, 2012 at 2:39 am
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'
April 25, 2012 at 2:41 am
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?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 25, 2012 at 2:45 am
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'
April 25, 2012 at 2:55 am
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.
April 25, 2012 at 2:57 am
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).
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 25, 2012 at 2:59 am
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
April 25, 2012 at 3:04 am
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
April 25, 2012 at 3:07 am
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'
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply