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_myT
able' 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.