Go Premium for a chance to win a PS4. Enter to Win


SQL Shrink Question

Posted on 2013-06-29
Medium Priority
Last Modified: 2013-07-09
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?
Question by:rwheeler23
LVL 71

Assisted Solution

Qlemo earned 400 total points
ID: 39286802
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.
LVL 23

Assisted Solution

Racim BOUDJAKDJI earned 400 total points
ID: 39286810
And do not forget to update your statistics.

Accepted Solution

didnthaveaname earned 1200 total points
ID: 39286932
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.


Author Comment

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

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

Expert Comment

ID: 39287010
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

Author Comment

ID: 39287032
OK, I am rebuilding the indexes right now using this. I am sure this will run for while.

DECLARE @Database VARCHAR(255)  
DECLARE @fillfactor INT

SET @fillfactor = 90

SELECT name FROM master.dbo.sysdatabases  
WHERE name  IN ('MERCH')  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  

   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  

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

       FETCH NEXT FROM TableCursor INTO @Table  

   CLOSE TableCursor  
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question