March 17, 2015 at 2:44 pm
I'm seeing this error pop up occasionally, and am not finding much on the Google about what might be causing it:
Msg 9020, Sev 16, State 2, Line 1 : The log for database <dbname> failed to grow while shrink in progress. Please retry.
Anyone have any idea what exactly this error is trying to tell me? I've verified that nothing is running but the shrink at the time, and re-running it immediately after it fails always succeeds.
The Redneck DBA
March 17, 2015 at 2:56 pm
The Redneck DBA (3/17/2015)
I'm seeing this error pop up occasionally, and am not finding much on the Google about what might be causing it:Msg 9020, Sev 16, State 2, Line 1 : The log for database <dbname> failed to grow while shrink in progress. Please retry.
Anyone have any idea what exactly this error is trying to tell me? I've verified that nothing is running but the shrink at the time, and re-running it immediately after it fails always succeeds.
Please don't tell me that you have auto-shrink enabled on the database!
😎
Run this in a database to check the settings
select DATABASEPROPERTYEX(db_name(),'IsAutoShrink')
March 17, 2015 at 3:03 pm
Eirikur Eiriksson (3/17/2015)
The Redneck DBA (3/17/2015)
I'm seeing this error pop up occasionally, and am not finding much on the Google about what might be causing it:Msg 9020, Sev 16, State 2, Line 1 : The log for database <dbname> failed to grow while shrink in progress. Please retry.
Anyone have any idea what exactly this error is trying to tell me? I've verified that nothing is running but the shrink at the time, and re-running it immediately after it fails always succeeds.
Please don't tell me that you have auto-shrink enabled on the database!
😎
Run this in a database to check the settings
select DATABASEPROPERTYEX(db_name(),'IsAutoShrink')
No auto shrink involved. This is an error we're seeing when running a DBCC SHRINKFILE command after some maintenance to the database.
The Redneck DBA
March 18, 2015 at 2:59 am
Firstly why are you regularly shrinking your database?
AFAIK the error's from the log's trying to auto-grow while you have a shrink running. Solution: stop shrinking the log, if it's reaching a size it needs to be that size, leave it alone.
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
March 18, 2015 at 3:33 am
Do you do regular Transaction Log backups as these will help keep the Log file in a reasonably trim state, as the Log back up will mark free up the space used by closed checkpoints and allow the space to be reused.
Ideally the Log file should be on its own drive and not competing for space with other Datafiles
A bog standard simple SQL server really needs 4 drives,
1) OS/Program Files
2) Tempdb & Other System DB's
3) User DB Data files
4) Logs
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply