benners70
asked on
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_myT
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.
1. Could not allocate space for object 'dbo.tblExitStats'.'PK_myT
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Never set autoshrink to ON. It will for sure fragment your indexes in your database.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"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?