SQL Shrink Question

I was just ask to look at a performance issue with SQL and one of the things I wanted to do was look at the databases. So I made a backup of one of the databases. The production database mdf file is almost 90GB in size but when I created a backup of the database that file is not even 9GB in size. I normally do not shrink the data file due to fragmentation issues but given this wide discrepancy should I shrink the data file?
rwheeler23Asked:
Who is Participating?
 
didnthaveanameConnect With a Mentor Commented:
Excellent points.  I would just add my 2cents too:

Check on backup compression, as you may not be comparing apples to apples if the instance default is to compress backups and you aren't explicitly specifying no compression.  I would say a much better way to see if your mdf is oversized is by checking the space used inside the data file with something like:
select a.FILEID, FILE_SIZE_MB = convert( decimal( 12, 2 ), round( a.size / 128.000, 2 ) ), SPACE_USED_MB = convert( decimal( 12, 2 ), round( fileproperty( a.name, 'SpaceUsed' ) / 128.000, 2 ) ), FREE_SPACE_MB = convert( decimal( 12, 2 ), round( ( a.size - fileproperty( a.name, 'SpaceUsed' ) ) / 128.000, 2 ) ), NAME, FILENAME from dbo.sysfiles as a;
If you'd like to take a peak at the index fragmentation before rebuild them, you can do so with, and paying particular attention to avg_page_space_used_in_percent and avg_fragmentation_in_percent :
select * from sys.dm_db_index_physical_stats( db_id( 'databaseNameHere' ), null, null, null, null );
If you do want to shrink the database, I'm personally a fan of moving it into a database with smaller data files so that you minimize fragmentation on the physical disk (this is dependent on the fragmentation of the physical disk(s), so I would check the fragmentation of the file itself using the contig tool from sysinternals: http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx
Last but not least, you may want to ponder the growth trajectory of the database.  It's far better to have a database whose underlying datafiles are far in excess of what current capacity needs are, but will accommodate growth for a determined amount of time.  Increases to the database file sizes should be just as carefully planned as decreases, in my humbled opinion, so make sure you're giving yourself room to grow
0
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
You can shrink the DB to e.g. 20 GB. Much more important is to do a full defrag or rebuild of the indexes (and hopefully of the associated tables, if they are clustered). Provided there were more data in those DB which got deleted. If the DB just grow from something to 9GB, you won't have much of an effect by doing anything different from the usual periodical index reorganisation/rebuild. The basic question is whether your table and index data is fragmented and scattered heavily. If the occupied area is continuous, the reserved size of the DB does not matter.
0
 
Racim BOUDJAKDJIConnect With a Mentor Database Architect - Dba - Data ScientistCommented:
And do not forget to update your statistics.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
rwheeler23Author Commented:
Here is the result of checking whether the mdf is oversized by checking the space used inside the data file

FILEID      FILE_SIZE_MB      SPACE_USED_MB      FREE_SPACE_MB      NAME      FILENAME
1      82823.13      73107.50      9715.63      GPSMERCHDat.mdf      D:\MSSQL\Data\MERCH.mdf
2      456.06      7.30      448.77      GPSMERCHLog.ldf      D:\MSSQL\Logs\MERCH.ldf
0
 
didnthaveanameCommented:
The larger one looks to be just the right size.  I would focus in on the index fragmentation like qlemo suggested. And it certainly doesn't hurt to check the mdf's file fragmentation on the physical disk
0
 
rwheeler23Author Commented:
OK, I am rebuilding the indexes right now using this. I am sure this will run for while.

DECLARE @Database VARCHAR(255)  
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM master.dbo.sysdatabases  
WHERE name  IN ('MERCH')  
ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
  table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
  WHERE table_type = ''BASE TABLE'''  

   -- create table cursor  
   EXEC (@cmd)  
   OPEN TableCursor  

   FETCH NEXT FROM TableCursor INTO @Table  
   WHILE @@FETCH_STATUS = 0  
   BEGIN  

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
       BEGIN
           -- SQL 2005 or higher command
           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
           EXEC (@cmd)
       END
       ELSE
       BEGIN
          -- SQL 2000 command
          DBCC DBREINDEX(@Table,' ',@fillfactor)  
       END

       FETCH NEXT FROM TableCursor INTO @Table  
   END  

   CLOSE TableCursor  
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.