Solved

SQL Shrink Question

Posted on 2013-06-29
6
365 Views
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?
0
Comment
Question by:rwheeler23
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 70

Assisted Solution

by:Qlemo
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.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 100 total points
ID: 39286810
And do not forget to update your statistics.
0
 
LVL 8

Accepted Solution

by:
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( 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rwheeler23
ID: 39287002
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
 
LVL 8

Expert Comment

by:didnthaveaname
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
0
 

Author Comment

by:rwheeler23
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 @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

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

623 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