Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Simple Recovery - Autogrowth On or Off?

Author  Topic 

gfahrlander
Starting Member

23 Posts

Posted - 2011-03-14 : 13:04:54
I've been reading about tran logs until I'm bleary-eyed and I can't find a definitive answer...

Database in Simple recovery -- should Autogrowth be on or off?

Just had a Db in Simple w/ Autogrow on grow to 192GB then threw a 9002 error in the middle of the night (I hate it when that happens).
If it had Autogrow off would it have checkpointed and reused the log or just throw the 9002 error sooner?

No trees were killed in posting this message. However, a large number of electrons were seriously inconvenienced!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-14 : 13:30:58
There are differing opinions on the autogrowth setting, and it's not recovery model related. I'm in the camp that says autogrowth should be enabled, and you should have monitoring in place to watch disk space.

Autogrowth actually helped you in your scenario. The 9002 error would have been throw sooner with it being off.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

gfahrlander
Starting Member

23 Posts

Posted - 2011-03-14 : 15:11:28
Tara,
Thank you. You confirmed what I suspected. I'll make sure my autogrowth isn't set to 65,536%....

Gary

No trees were killed in posting this message. However, a large number of electrons were seriously inconvenienced!
Go to Top of Page

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2011-03-14 : 23:13:42
Perhaps the more important thing is to check what's causing the t-log to grow until 192 GB, for a database running the simple recovery model?

Ray Mond

RESTORE VERIFYONLY - don't bet your job on it! Learn why here.
TLogInfo - the only FREE tool to analyse your transaction logs. Download here..
SQLBakReader - the only FREE tool to inspect your SQL Server backup files without using SQL Server. Download here..
Go to Top of Page
   

- Advertisement -