September 16, 2008 at 5:49 am
Hi,
We're using an automatic deployment system for DTS and SSIS packages. Our development machine gets refreshed every day. At the moment, our msdb is 2.8 GB in size, and this is all due to the sysdtspackages and sysdtspackages90 tables.
For starters, I deleted all legacy DTS packages. This resulted in an empty sysdtspackages table. However, the table is still taking about 1.4 GB of space. Checking sys.allocation_units revealed this:
allocation_unit_id;type;type_desc;container_id;data_space_id;total_pages;used_pages;data_pages
72057594048413696;2;LOB_DATA;72057594052018176;1;181937;181905;0
So it's probably due to the packagedata column in sysdtspackages, which is an image datatype column.
How do I reclaim this space, as when adding new DTS packages, the table just keeps on growing instead of reusing the space from the deleted rows.
Thanks!
September 16, 2008 at 6:08 am
Unless you manually shrink a database or autoshrink is on SQL Server will not return space to the OS. It will re-use the space.
Does sys.allocation_units show many unused pages?
I would think that you need all that space if it continues to be re-used, to limit autogrowth events. If you don't have disk space issues I'd leave it alone.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 16, 2008 at 6:20 am
Hi,
Jack Corbett (9/16/2008)
Unless you manually shrink a database or autoshrink is on SQL Server will not return space to the OS. It will re-use the space.
Shrinking is not possible because the space is somehow still being used, but I don't know why/where.
Jack Corbett (9/16/2008)
Does sys.allocation_units show many unused pages?
No.
I ran the following command:
select * from sys.dm_db_index_physical_stats(db_id('msdb'), object_id(N'dbo.sysdtspackages'), null, null, 'DETAILED')
...which gave me some more mysterious results:
database_idobject_idindex_idpartition_numberindex_type_descalloc_unit_type_descindex_depthindex_levelavg_fragmentation_in_percentfragment_countavg_fragment_size_in_pagespage_countavg_page_space_used_in_percentrecord_countghost_record_countversion_ghost_record_countmin_record_size_in_bytesmax_record_size_in_bytesavg_record_size_in_bytesforwarded_record_count
417462366511CLUSTERED INDEXLOB_DATA100NULLNULL1819040,19724487274524501921950000NULL
It's the ghost_record_count value of 192195 that seems rather strange.
Thanks for your input.
September 16, 2008 at 6:27 am
Sounds like the old problem where space release by deleted/modified image or text columns isn't released.
Have a look at this KB article http://support.microsoft.com/kb/324432
September 16, 2008 at 6:33 am
In SSMS you can run a Disk Usage by Table report by right-clicking on the database, selecting Reports, and then selecting the Disk Usage by Table Report. This may help you determine what is using the space. You can also run the base disk usage report to see if you have free space in the database itself.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply