February 12, 2010 at 12:32 pm
Hello,
Before I deleted backup history, MSDB Size is around 5G. after I sucessfully deleted backup history, It still is around 5G.
Here is one table info in msdb
The records in table "backupfile" used to around 400000 rows and after cleaning up, table "backupfile" is 50124 rows. The data space in 50124 rows is taking 1850976 KB
I have issued a couple of times "sp_spaceused @updateusage='true'". It is still not changing status.
The backup size for MSDB is also same as before.
Anybody knows why the table space is not released.
Thanks
February 12, 2010 at 12:47 pm
Hi,
You should use: DBCC UPDATEUSAGE
but pay attention:
"Do not run DBCC UPDATEUSAGE routinely for databases created in SQL Server 2005 or higher or on upgraded databases that have been corrected once by using DBCC UPDATEUSAGE. Because DBCC UPDATEUSAGE can take some time to run on large tables or databases, it should not be used only unless you suspect incorrect values are being returned by sp_spaceused"
see: http://msdn.microsoft.com/en-us/library/ms188414.aspx
Regards,
Rafael Melo
Brazil
February 12, 2010 at 12:58 pm
rafael_si (2/12/2010)
Hi,You should use: DBCC UPDATEUSAGE
but pay attention:
Thank you for reply.
After I did "DBCC UPDATEUSAGE(msdb)", the result is same.
February 12, 2010 at 1:05 pm
Did you run log back up after deleting records? Did you shrink data\log file? Check MDF and LDF file size before and after log back up.
EnjoY!
February 12, 2010 at 1:11 pm
GT-897544 (2/12/2010)
Did you run log back up after deleting records? Did you shrink data\log file? Check MDF and LDF file size before and after log back up.EnjoY!
MSDB is in simple recovery mode.
MDF size:4143MB
LDF size: 814MB
February 12, 2010 at 1:15 pm
just to confirm, even after the delete, the backup is the same size?
---------------------------------------------------------------------
February 12, 2010 at 1:19 pm
george sibbald (2/12/2010)
just to confirm, even after the delete, the backup is the same size?
Yes. backup size : 3.9G
February 12, 2010 at 1:20 pm
run a reindex, backup again, see what happens
---------------------------------------------------------------------
February 12, 2010 at 1:30 pm
george sibbald (2/12/2010)
run a reindex, backup again, see what happens
Thank you. It works.
After I reindex one table "backupfile". the size for this table is "18094K".
backup size is 2G.
You are really helpful.
February 12, 2010 at 2:59 pm
no probs.
If you want to reclaim the disk space used by msdb try the truncateonly option of dbcc shrinkfile first.
If you shrink the data file without truncateonly this will fragment your tables again so rerun reindex afterwards.
---------------------------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply