August 31, 2009 at 6:30 am
I use SQL Server 2005 and i am very short with disk space where only my mdf files are stored. Now i know that shrinking a database is not a good idea but i have to do sometimes since disk space is low but i always take a full backup then try to shrink. Now the problem is let's say the space left on the drive is 2GB. but when i shrink the database the drive space is still 2GB. so what is the actual process of shrinking? and i know if i shrink i should atleast gain a space of 10 to 12 GB. Please help.........
August 31, 2009 at 6:45 am
Do you have free space in the log or the data files? If the log, it might be that there are virtual log files within your log that are near the end of the file and preventing shrinking. You can use this script to help:
http://qa.sqlservercentral.com/scripts/Maintenance+and+Management/30026/
Also, did you try Shrinkfile or shrink database? I'd use Shrink File. Seems to work better.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 31, 2009 at 6:50 am
But this script is for shrinking the transaction log files. and my trans log files are in D:\ drive and my data files are in E:\ drive so just concerned the space in E:\ drive because that drive has only data files. so any other clue might help me please.
August 31, 2009 at 7:03 am
Is there any free space within the data file? If not, shrink's going to do nothing. It's not compression, it's releasing unusued space to OS. If there's no unused space in the data file, there's nothing to release to the OS.
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
August 31, 2009 at 7:49 am
But it worked when the others do it but this task was now given to me to shrink the database, and i could not release space. I don't know why.
August 31, 2009 at 7:56 am
no that's not the case. when other were doing it it was releasing some space and suddenly i was given the responsibility and i could not release any space. so there might be a way of shrinking the database, but could not figure out.
August 31, 2009 at 9:05 am
There's only one way to shrink. No special tricks, no magic words.
Is there free space in the data file? What does sp_spaceused return? If you shrink through management studio (right click DB - tasks - shrink - files) what is listed as the % free space?
Are you aware that 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
August 31, 2009 at 10:12 am
available free space is 2%
August 31, 2009 at 10:18 am
Then there's no point in shrinking. There's virtually no free space in the data file to be released to the OS, all shrinking is going to do is fragment the indexes and ensure that the data file will grow next time data is added.
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
August 31, 2009 at 10:25 am
If you only have 2% free space, then you're running out of space. You need to add more space, not shrink.
And you will need more disk space. There's no magic way of recovering space in a database. If there is data in there, it needs the space. More data has probably been added since the last time someone freed up space.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply