March 25, 2014 at 10:17 am
Greetings,
I have recently inherited a new production database on a new server (through company acquisition) that is quickly filling up the data drive on the server. I have subsequently purged a lot of data from this database and tried shrinking the file (bad practise I know - but faced with no other choice at the minute) but the space is not getting released back to the OS.
I'm aware of the "sp_clean_db_file_free_space" and "sp_clean_db_free_space" procedures but have never actually utilised these before. Has anyone any experience of these and if I execute them will they/should they clean up space for me and allow me to release memory back to the OS after a shrink.
N.B. If this is successful, I plan to rebuild all the indexes after the shrink! 😎
March 27, 2014 at 7:56 am
Is there actually free space in the data file? If you run the following command on the database in question, what is the result?
Select (select
convert(decimal(6,2),((round(fileproperty(name,'SpaceUsed')/128.000,2)
/round(size/128.000,2)*100)))
from master.sys.master_files
where
database_id = db_id()
and type_desc = 'Rows') as PercentageUsed
March 27, 2014 at 2:46 pm
sp_clean_db_file_free_space removes ghost records - records that have already been deleted. This is unlikely to be your problem. Have you checked which tables are consuming the space and growing the quickest? There is a built-in report to see table size. In SSMS, right-click on the database in question, click Reports > Standard Reports and you have two Disk Usage by Table reports to choose from. Perhaps you just have a table that is receiving a lot of new data. Maybe some of it can be archived off. Just shrinking the database file won't achieve anything other than fragmentation if the database needs the extra space.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply