SQL Server 2008 AutoShrink, Yes or No?

I am on a shared SQL 2008 Server and have a limited amount of space (200MB) and I have run into problems which caused my site to go down e.g.

1. Could not allocate space for object 'dbo.tblExitStats'.'PK_myTable' in database 'mssql0_mydb' because the 'PRIMARY' filegroup is full"
2. Log file full so unable to perform any data manipulation SQL.

I currently have AutoShrink set to ON, but have since been told this is not a good idea. I'd appreciate other expert recommendations considering my space limitation and that I am on SQL 2008.

I understand that fragmentation is the problem of AutoShrink, but if I need to use it in my situation what can I do to fix the fragmentation, re-indexing weekly perhaps? Or is there a better solution?

My Server support have said:
- That blog article is from 2007 and related to SQL Server 2005, not 2008.http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx
- It is aimed at DBAs who do not have a hard cap on database size that requires manual intervention from their shared hosting provider to manually shrink
- Our DBAs made the decision to enable it based on recurring issues with this database and their extensive experience in administering MS SQL Server.
- If we switch this back on, the original issue with your database is likely to happen again, resulting in downtime for your site.
benners70Asked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
>> I currently have AutoShrink set to ON, but have since been told this is not a good idea.

Yes, Autoshrink ON is a bad practice and would impact Server performance..
Reason being, SQL server grows automatically with some reserverd space for maintaining fill factor and future growth.
Setting AUTOSHRINK to ON would shrink the database file sizes thereby removing the allocated or reserved spaces thereby causing your Database to grow for each and every modifications in the database. In simple words ( to explain), INSERT causes growth, DELETE causes Shrink, Rebuild index causes growth, AUTOSHRINK causes shrinking behaviour..

>> have a limited amount of space (200MB)

Its better to allocate some more memory say 500 MB for your database for better performance and avoiding these issues.
0
 
ProjectChampionConnect With a Mentor Commented:
Shrinking database is an expensive operation, which causes a lot of i/o contention and generally has an adverse impact on the performance so normally you wouldn't want to put it on auto-cruise mode as it's not that clever and more than often will become counterproductive. Don't mistake it though, unlike what many out there might suggest otherwise, auto-shrink is not an evil feature and there are some (rare) occasions that it might actually be useful, however more than often it's a sign of a lazy DBA!
You should also note that  the Data file in you DB only grows when needed, so unless you have a great number of deleted records as part of a one-off operation (e.g. a recent archiving) you shouldn't need to and wouldn't want to shrink the data file. As for the transaction log the best way to keep things under control is to have a proper backup plan in place, for instance assuming you're using the full recovery model, create a scheduled job to backup the transaction log on a daily or hourly basis according to the number of transactions in your db.  

The error message you mentioned is strongly suggesting that your database has overgrown its space, so the real solution for your problem is more disk space (not “memory”).
0
 
benners70Author Commented:
I currently have the DB set as SIMPLE which I believe uses less LOG file space compared with FULL RECOVERY.

"create a scheduled job to backup the transaction log on a daily or hourly basis according to the number of transactions in your db."

Does running a LOG file backup automatically reduce the LOG file size?

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
ProjectChampionConnect With a Mentor Commented:
If you're using Simple recovery model, you should schedule a regular full backup of your database. Under the simple recovery model, log truncation occurs automatically after you back up the database, and under the full recovery model, after you back up the transaction log.
0
 
chapmandewCommented:
Never set autoshrink to ON.  It will for sure fragment your indexes in your database.
0
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
>> I currently have the DB set as SIMPLE which I believe uses less LOG file space compared with FULL RECOVERY.
>> Does running a LOG file backup automatically reduce the LOG file size

If your database is in SIMPLE recovery model, then Transactional log backup are not applicable..
Taking regular FULL backups would make your Log file to reuse the inactive portions of the log but that doesn't guarantee non-increase in Log file size..
And Full backups will not Shrink Log File by default and you need to shrink it when required..

PS: Don't shrink either Log File or Database unnecessarily as it might impact performance.
0
All Courses

From novice to tech pro — start learning today.