March 28, 2012 at 1:23 am
My database Backup file size 20 GB. i Restore this database another server that time MDF file will take 135 GB. i already tried shrink the database.but no use. give me a solution.
I am using Sqlserver 2005 enterprice edition.
March 28, 2012 at 1:27 am
are you using compressed backups?
how much free space is in the MDF file? run the below SQL it will loop through all DB's giving used and free space
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'SELECT ' + CHAR(13) + CHAR(10) +
'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +
'a.FILEID, ' + CHAR(13) + CHAR(10) +
'[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +
'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)
FROM sys.databases
FOR XML PATH('')
)
AS NVARCHAR(MAX)
),
' ',CHAR(13) + CHAR(10)
)
--SELECT @SQL
EXECUTE sp_executesql @SQL
March 28, 2012 at 2:13 am
Thanks for ur reply
No i am not taken compressed backup.just i took normal backup.
March 28, 2012 at 2:14 am
and what about free space?
March 28, 2012 at 3:12 am
File Size in MB
MDF File : 137337.75
Log File : 22867.38
Space Used in MB
MDF File : 15225.63
Log File : 22303.33
Free Space MB
MDF File : 122112.13
Log File : 564.05
March 28, 2012 at 3:17 am
ok try shrinking in small chunks as this can sometimes be faster than shrinking in one big go
also I take it you are fully aware that you will need to rebuild all your indexes after shrinking which will result in your file growing again, but not to 135GB
March 28, 2012 at 3:29 am
ok thank you i will try
March 28, 2012 at 4:36 am
i done index rebuilding and then shrinking also. but still size not changesd.
March 28, 2012 at 9:11 am
What command exactly are you using to shrink it. Any error message?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 28, 2012 at 9:15 am
Try this:
DBCC SHRINKDATABASE(database_name, NOTRUNCATE);
Then try shrinking it again.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 28, 2012 at 10:56 pm
Its returned this result
DBCC SHRINKDATABASE(iedb, NOTRUNCATE);
19 1 17579232 128 1992792 1992768
19 2 63 63 56 56
March 29, 2012 at 6:32 am
Try this:
DBCC SHRINKDATABASE(iedb, 10);
Go
I usually find specifying the % free size works the best
March 29, 2012 at 8:06 am
vs.satheesh (3/28/2012)
Its returned this resultDBCC SHRINKDATABASE(iedb, NOTRUNCATE);
19 1 17579232 128 1992792 1992768
19 2 63 63 56 56
That looks good, now, as I said, try shrinking the database after running NOTRUNCATE.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply