0

When the AutoShirnk option on a database (not log files) is set to TRUE, how often does the AutoShirnk operation take place?

Is there a set time at which this operation takes occurs? Or does this depend on the size of the database and if so how?

PS: I realize that having the AutoShirnk option turned is not a best practice and results in fragmented indexes etc.

slayernoah
  • 4,382
  • 11
  • 42
  • 73

1 Answers1

1

There is no set time. As per msdn,

The Database Engine periodically examines the space usage in each database. If a database has the AUTO_SHRINK option set to ON, the Database Engine reduces the size of the files in the database. This activity occurs in the background and does not affect any user activity within the database.

SouravA
  • 5,147
  • 2
  • 24
  • 49
  • Thanks! But even though that post claims that this process doesn't affect user activity within the database, looks like it does. And i am trying to troubleshoot. See this: http://stackoverflow.com/questions/26141795/sql-server-query-time-out – slayernoah Oct 01 '14 at 15:33
  • Also, is there any way to (1) find out the definition of 'periodically' in the text above. And (2) what factors in the 'disk space usage' determines if it's due for a shrink? – slayernoah Oct 01 '14 at 15:36
  • I looked at the other question you posted. By any chance, do you have AUTO GROW set to ON as well? Go through this link http://support.microsoft.com/kb/315512 – SouravA Oct 01 '14 at 15:44
  • In addition, you will get a lot of answers here- http://www.sqlskills.com/blogs/paul/auto-shrink-turn-it-off/ – SouravA Oct 01 '14 at 15:50
  • I do have `AutoGrowth` set turned on for all the data files and the log file. - For the Primary data file, it is: By 1MB, Unlimited. - For 10 of the data files it is: By 100 MB unlimited. - For 4 of the data files it is: By 50MB, Unlimited - For the log it is: By 10%, Limited to 2GB Any issue this might cause? – slayernoah Oct 01 '14 at 15:54
  • Autogrowth with Auto Shrink is a **deadly combo**!! Auto Shrink can trigger Auto growth which might cause IO contention. Please go through the links I posted. – SouravA Oct 01 '14 at 15:58