asked on

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?

SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Racim BOUDJAKDJI

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

didnthaveaname

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rwheeler23

ASKER

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

didnthaveaname

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

rwheeler23

ASKER

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