November 1, 2011 at 5:08 pm
Hi.
I'm working in a small company which provides an ERP software system along with an SQL Server Express installation.
They want to keep the databases small because of the 10GB database size limit in SQL Express (used to be 4GB in previous versions) and so they enabled the autoshrink option in all our client's databases. All of them use the simple recovery model because log backups are not needed given the size of the database and the amount of people working on it.
My question is: does the auto shrink option have any undesirable effects that could possible harm our business?
Thank you.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
November 1, 2011 at 5:47 pm
Autoshrink will cause severe fragmentation in the indexes. Which would require you to defrag after each shrink. And that defrag is likely to cause the database to grow again.
Rather than autoshrink - control the amount of data being retained or go with an edition that can handle more data.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 1, 2011 at 6:05 pm
Thank you, Jason.
That's great information and advice.
I'm noticing that the indexes are getting 70%-90% fragmented even with small data being inserted and modified so the reports (based on views that use a lot of joins) started to run somewhat slowly in some cases. Do you think that that option may have something to do with it?
I'm truly concerned that some bad decisions were made in many of our routines that could impact us somehow. Like when we move from SQL Express to some higher editions and start working with bigger clients.
Thanks again.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
November 1, 2011 at 6:11 pm
Absolutely yes. Autoshrink and shrinking a database in general wreak havoc on a database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 1, 2011 at 6:22 pm
Thank you.
I'm convincing our CIO to make changes and I'm being successful in optimizing many things there. My company aims bigger clients but they don't understand that it will generate higher demands.
I guess with great power comes great responsibility.
Most of the things I'm trying to improve are thanks to SQLServerCentral which is an invaluable source of information and many of the great experts like you and many others. I can't be thankful enough.
Also the Execution Plans book by Grant Fritchey and Kalen Delaney's SQL Server 2008 Internal which is very dense and is giving me a hard time to understand in depth. 😀
Have a great week.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
November 1, 2011 at 6:42 pm
That is good to hear. Keep hanging out and you should be able to keep learning. 🙂
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 2, 2011 at 1:59 am
I wouldnt worry about fragementation for indexes having less than 1000 pages.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
November 2, 2011 at 8:19 am
Sachin Nandanwar (11/2/2011)
I wouldnt worry about fragementation for indexes having less than 1000 pages.
That doesn't hold true. It is a myth. The person that said it said it himself.
I have seen indexes with 50 pages cause the system to come to a halt when severely fragmented. You need to evaluate on a case by case basis.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply