SQL Shrink Question

Posted on 2013-06-29
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 68

Assisted Solution

Qlemo earned 100 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 100 total points
ID: 39286810
And do not forget to update your statistics.

Accepted Solution

didnthaveaname earned 300 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(, 'SpaceUsed' ) / 128.000, 2 ) ), FREE_SPACE_MB = convert( decimal( 12, 2 ), round( ( a.size - fileproperty(, '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:
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.


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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Query Syntax Error 9 34
MS SQL 2014 get SPIDs of users 6 27
Numeric sequence in SQL 14 38
search for a string in all tables 4 15
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now