Database Option "Auto Shrink"
-
If the database option auto shrink is activated when will it be executed? whats could be its condition in shrinking the data(mdf) and log (ldf)? is there any required percentage? for example at 80% data or log it will truncate the data or log??
"-=Still Learning=-"
Lester Policarpio
-
from the books online:
AUTO_SHRINK
When set to ON, the database files are candidates for periodic shrinking. Both data file and log files can be shrunk automatically by SQL Server. When set to OFF, the database files are not automatically shrunk during periodic checks for unused space. By default, this option is set to ON for all databases when using SQL Server Desktop Edition, and OFF for all other editions, regardless of operating system.
AUTO_SHRINK only reduces the size of the transaction log if the database is set to SIMPLE recovery model or if the log is backed up.
The AUTO_SHRINK option causes files to be shrunk when more than 25 percent of the file contains unused space. The file is shrunk to a size where 25 percent of the file is unused space, or to the size of the file when it was created, whichever is greater.
It is not possible to shrink a read-only database.
The status of this option can be determined by examining the IsAutoShrink property of the DATABASEPROPERTYEX function.
-
Setting a database's Auto_Shrink property to ON is probably not the best idea in the world. Something made it grow, you did something to reduce it's size, it's gonna grow again, you'll do something else to reduce it's size, ad infinitum... the result will be a pretty well fragmented set of files which, of course, isn't real good for performance.
Figure out how much disk space you'll need for the next year or two, buy the space, pre-allocate it to the DB(s), and turn OFF the Auto-Shrink property.
While you're at it, check you database properties. If they set up to auto grow at 10%, chances are that the default setting were never changed and the starting size of the db's was 1 Mega-Byte... if you do the math, that means that a 1 Giga-Byte database can have/will have as many as 73 fragments...
Preplanning the size of a database is one of the more important things you can do... setting Auto-Shrink to ON is one of the more damaging things you can do...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for the reply jo & jeff just want to know the details about auto shrink option (dont wanna use it anyways
). Im just new here at my job being a DBA ( 3 months) after reading informations about SQL I found out about this auto shrink option of a database. based on your explanations it is a bad idea to enable this option so i think I will tell my boss about it the reason why is that we have 2 servers with almost all databases having auto shrink enabled.@@OFF TOPIC
when data(mdf) is shrunk will the data inside the database be lost? (the shrunk data i mean?) or the best way to construct the question is what will be shrunk(or can we say deleted?) if we shrink the data(mdf) is it the data inside the tables?
"-=Still Learning=-"
Lester Policarpio
-
Lester , it is the empty space that is shrunk (removed).
-gol
-
Also beware that auto-shrink can cause locking issues. When we build our datawarehouses if auto shrink is on it can try to shrink the file while it's still writing and has been known to change a build from 2 hours to 18!
-
Tibor has written a very nice article on the subject -
'Why you want to be restrictive with shrink of database files'.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
It's well worth the time reading.
/Kenneth
-
Thanks guys
"-=Still Learning=-"
Lester Policarpio
-
That IS a good article... thanks, Ken.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply