February 16, 2010 at 8:17 am
After I tried to create an index that filled the available disk space, I am getting a negative number in the "unallocated_space" column in the sp_spaceused. Since the index create failed, it looks like there is enough unused space that I'm not too concerned (yet) about filling the database.
But, is this a problem? And if so, how can I 'fix' it. I'm also getting a low disk space warning for the disk in Server 2003. I've tried to release some space back to the OS to clear that, but without much success.
I tried to run dbcc shrinkdatabase (clarity,10) - this ran OK but didn't seem to release any space back to the OS.
The results from sp_spaceused is below
database_name database_size unallocated space
clarity 617668.63 MB -34751.58 MB
reserved data index_size unused
664597840 KB 454257544 KB 125319472 KB 85020824 KB
Any thoughts on what I can do to clear this? And release a GB or so to the OS?
Thanks!
Norman
February 17, 2010 at 7:08 am
Run exec sp_spaceused @updateusage = ‘true’
to get exact figures and then decide accordingly.
http://www.mssqltips.com/tip.asp?tip=1358
MJ
February 17, 2010 at 7:28 am
Run DBCC UPDATEUSAGE
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2010 at 7:31 am
n.heyen (2/16/2010)
I tried to run dbcc shrinkdatabase (clarity,10) - this ran OK but didn't seem to release any space back to the OS.
Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2010 at 2:23 pm
Thank you Gail and MJ - I wish I would have known that.
Norman
February 18, 2010 at 2:26 pm
Thank you Gail,
I normally wouldn't shrink a database but I get nasty-grams from the server admins about the disk being full. All I want to do is shrink it by a % or 2; enough to clear the warning until I can convince the SAN group that I really need more space... Yes, we are all one big happy team.:-)
Norman
February 18, 2010 at 2:35 pm
n.heyen (2/18/2010)
I normally wouldn't shrink a database but I get nasty-grams from the server admins about the disk being full.
Tell then that the database is full and very soon the apps that use it will start throwing nasty errors to important users unless they get more drive space.
It's almost true.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply