What is Auto shrink in SQL SERVER or Is it safe to have SQL Server auto-shrink turned on?

• SQL Server is one of the easiest databases to maintain because of all of the automatic settings it has, but as I mentioned with Auto-Close, some of them should be left off. The Auto-Shrink setting is another
  That might surprise a few people. You might think from the name of this operation, that you would want your databases to automatically reclaim any space they take whenever they can. And if SQL Server will handle that for you, all the more wonderful. But in fact, shrinking a database can cause your Indexes to be fragmented, especially if it happens a lot. I never shrink my databases unless I have a huge deletion of data, and I know that the data won't come back. That's a pretty rare event, and when it does happen I run the shrink operation manually and rebuild my indexes after.
  In the Database Maintenance workshop we did at SQL Connections last week I promised to blog a script I used to show how data file shrink operations cause massive fragmentation of indexes. The reason is that data file shrink starts at the end of the data file and moves a single page at a time to a free space below the shrink threshold. In the process of doing so, it perfectly reverses the physical order of the pages comprising the leaf level of an index – thus perfectly fragmenting it!!
  
  
  
  Let’s try out my simple script that demonstrates this. First thing I’m going to do is create a new database and create a 10MB ‘filler’ table, which I’m going to then drop later to create a space that shrink can use.
  
  
  USE MASTER;
  GO
  
  IF DATABASEPROPERTYEX (‘shrinktest’, ‘Version’) > 0
        DROP DATABASE shrinktest;
  
  
  
  CREATE DATABASE shrinktest;
  GO
  
  
  USE shrinktest;
  GO
  
  
  
  
  SET NOCOUNT ON;
  GO
  
  
  
  
  
  
  
  
  — Create and fill the filler table
  
  
  
  CREATE TABLE filler (c1 INT IDENTITY, c2 VARCHAR(8000))
  GO
  
  
  DECLARE @a INT;
  
  
  SELECT @a = 1;
  
  
  WHILE (@a < 1280) 
  
  
  BEGIN
  
  
        INSERT INTO filler VALUES (REPLICATE (‘a’, 5000));
  
  
        SELECT @a = @a + 1;
  
  END;
  GO
  
  
  
  Next I’ll create the ‘production’ table that we’d really like to keep in optimal shape for performance.
  
  — Create and fill the production table
  
  
  CREATE TABLE production (c1 INT IDENTITY, c2 VARCHAR (8000));
  
  
  CREATE CLUSTERED INDEX prod_cl ON production (c1);
  
  
  
  GO
  
  
  
  DECLARE @a INT;
  SELECT @a = 1;
  WHILE (@a < 1280) — insert 10MB
  
  
  
  BEGIN
  
  
  
        INSERT INTO production VALUES (REPLICATE (‘a’, 5000));
        SELECT @a = @a + 1;
  END;
  GO 
  
  
  
  Now I’ll use the sys.dm_db_index_physical_stats DMV to check the fragmentation of the production table’s clustered index – it should be almost zero:
  
  
  
  
  
  
  — check the fragmentation of the production table
  
  
  
  SELECT avg_fragmentation_in_percent, fragment_count FROM sys.dm_db_index_physical_stats ( DB_ID (‘shrinktest’), OBJECT_ID (‘production’), 1, NULL, ‘LIMITED’);
  
  
  GO
  
  
  
  avg_fragmentation_in_percent fragment_count
  
  
  
  —————————- ——————–
  
  
  
  0.390930414386239            5
  
  
  
  This is what I expected. Now I’m going to drop the filler table, run a shrink operation and then check the fragmentation again:
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  — drop the filler table and shrink the database
  
  
  DROP TABLE filler;
  GO
  
  
  
  — shrink the database
  
  
  DBCC SHRINKDATABASE (shrinktest);
  GO
  
  — check the index fragmentation again
  
  
  SELECT avg_fragmentation_in_percent, fragment_count FROM sys.dm_db_index_physical_stats ( DB_ID (‘shrinktest’), OBJECT_ID (‘production’), 1, NULL, ‘LIMITED’);
  GO
  
  
  
  avg_fragmentation_in_percent fragment_count
  
  
  
  —————————- ——————–
  
  
  
  99.7654417513683             1277
  
  
  
  Wow! The index went from almost 0% fragmented to almost 100% fragmented – the shrink operation totally reversed the physical ordering of the leaf level of the clustered index – nasty.
  
  One of the common maintenance operations I see at customer sites is to run a database shrink at some interval, and I always advise against it – now you can see why. Running a regular shrink operation can cause horrible fragmentation problems. The worst problems I see are those customers with maintenance plans that rebuild all indexes and then run a shrink to remove the extra space necessary for the index rebuilds – completely undoing the effects of the index rebuild!
  
  
  One other common thing I see is to have auto-shrink set on for one or databases. This is bad for several reasons:
  
  
  Shrink causes index fragmentation, as I’ve just demonstrated above.
  You can’t control when it kicks in. Although it doesn’t have any effect like long-term blocking, it does take up a lot of resources, both IO and CPU. It also moves a lot of data through the buffer pool and so can cause hot pages to be pushed out to disk, slowing things down further. If the server is already pushing the limits of the IO subsystem, running shrink may push it over, causing long disk queue lengths and possibly IO timeouts.
  You’re likely to get into a death-spiral of auto-grow then auto-shrink then auto-grow then auto-shrink… (in my experience, if someone is using auto-shrink, they’re most likely using and relying on auto-grow too). An active database usually requires free space for normal operations – so if you take that free space away then the database just has to grow again. This is bad for several reasons:
  Repeatedly shrinking and growing the data files will cause file-system level fragmentation, which can slow down performance
  It wastes a huge amount of resources, basically running the shrink algorithm for no reason
  Auto-grow itself can be bad, especially if you’re using SQL Server 2000 (or don’t have Instant File Initialization turned on – see this post from Kimberly’s blog) where all allocations to the file being grown are blocked while the new portion of the file is being zero-initialized.
  
  Bottom-line: auto-shrink should *NEVER* be turned on…