October 13, 2009 at 5:46 am
We have a primary data file which is about 277GB.
Our diagnostics program shows that the data file has over 60Gb free.
Our data file is set to autogrow 10%. Last week it extended itself.
My question is, Why would the data file autogrow if we had so much
free space.
We have concerns because the disk the file sits on is only 320GB, and I don't want
our Disk monitoring tool to throw an alert that the data file is getting about 90% of
the disk space, especially since the data file has so much free space.
I have read about shrinking the data file but most experts warn against it.
Is there a way, like in Oracle to find the number and size of the free space/extents.
Thanks,
Keith
October 13, 2009 at 1:10 pm
I had a similar problem albeit with a database much smaller in size.
I have a 10 gig database and it would report having 0 disk space available. I'd run a DBCC updateusage and it would then report 7 gigs free. During the times when it reported 0 gigs free - it would of course autogrow.
I'm betting when you run maintenance on your DB part of the process is to run DBCC updateusage - so right after a maintenance cycle completes you see available space. Check the database right BEFORE that maintenance cycle runs and see if at that point it is reporting any free space at all.
Solution for me was to run a DBCC updateusage every 2 hours. Thats completely feasible on my 10 gig database. For a much larger DB like yours you would have to evaluate just how often you are able to run this since this process can be pretty costly on system resources.
Interestingly... I'm running this same database in a SQL 2008 test environment. No more need to run DBCC updateusage - SQL 2008 keeps much better track of actual available space.
I run the following to check on space used / available. This reports night and day difference when run before and after DBCC updateusage:
DECLARE @cmdstr varchar(100)
CREATE TABLE #TempTable
([Table_Name] varchar(50),
Row_Count int,
Table_Size varchar(50),
Data_Space_Used varchar(50),
Index_Space_Used varchar(50),
Unused_Space varchar(50)
)
SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''
INSERT INTO #TempTable EXEC(@cmdstr)
SELECT * FROM #TempTable ORDER BY CAST(REPLACE(Data_Space_Used,' KB','') AS INT) DESC
DROP TABLE #TempTable
October 14, 2009 at 6:39 am
Thanks for the info, I will give it a try.
Keith
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply