July 20, 2009 at 5:44 am
We had done a large data purge recently and I finally had an opportunity to shrink a data file to reclaim some much needed space, by running DBCC SHRINKFILE . However, I now have no disk space free on that drive, and the data file has 0% free !?!? I don't think I'm hallucinating.
July 20, 2009 at 6:25 am
Can you delete other files from that drive? If not can you add another file for this file group and place it on a different drive?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 20, 2009 at 6:25 am
Did the data file grow or did the log file grow? Shrink is a logged operation and it's not unexpected if the log file grows while the data file shrinks.
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
July 20, 2009 at 6:51 am
homebrew01 (7/20/2009)
We had done a large data purge recently and I finally had an opportunity to shrink a data file to reclaim some much needed space, by running DBCC SHRINKFILE . However, I now have no disk space free on that drive, and the data file has 0% free !?!? I don't think I'm hallucinating.
Hi homebrew01,
I think you should set your recovery model of the database into "simple" recovery instead of "full" mode.
Hope it can help.
July 20, 2009 at 9:22 am
golden (7/20/2009)I think you should set your recovery model of the database into "simple" recovery instead of "full" mode.
:ermm:
Oh dear! There's that horrible piece of advice again. It's almost like saying, "Just stop making regular backups".
Errm... the main difference between recovery models full and simple is that with recovery model full, you can use the transaction log to recreate any and all transactions since your latest full or differential backup, whilst with recovery model simple, you can't use the transaction log to recreate anything at all because everything's just deleted as soon as the transactions are completed.
So, if your DB gets screwed up in the afternoon, your latest full backup is from the night before and you don't have any differential backups, the alternatives look something like this:
With recovery model full, you can restore the full backup from last night and then use transaction log backups to recreate all the lost transactions, but with recovery model simple, your customers will have just lost a full day's work. 🙁
Back to the real topic: You say the disk is full... is the transaction log file and the data file on the same disk? This is generally not a very good idea either. Try to keep them on separate disks whenever possible, it's both safer and better for performance. 😉
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
July 21, 2009 at 5:54 am
".. I think you should set your recovery model of the database into "simple" recovery instead of "full" mode..." No thanks, it's a production database.
"... You say the disk is full... is the transaction log file and the data file on the same disk?..."
No, I was shrinking Data_3.ndf which is on it's own drive. I had 10G free, and now I have 0 free !?
The job completed normally. The transaction log also grew, which I expected
"..Can you delete other files from that drive? If not can you add another file for this file group and place it on a different drive?.."
The data file I was shrinking is the only file on the drive.
Autogrowth was on, so perhaps it used the space for some reason ? I probably should have set it off. The other 2 data files (.mdf & .ndf) on another drive have 124G free and 48 gig free so overall I have space to work with
Rather puzzling.
July 22, 2009 at 1:51 pm
Could you check how many free space in the Data_3.ndf
Did you do any reindex before shrinkdatabase which will increase the file size?
Maybe you can try this statement
DBCC SHRINKDATABASE (databasename,5, TRUNCATEONLY);
5 leave 5% free space remaining in the database after shrunk
TRUNCATEONLY will release all free space at the end of the file to the operating system.
Hope that will help.
Kevin
July 28, 2009 at 11:46 am
Kevin Mao (7/22/2009)
Could you check how many free space in the Data_3.ndf
Did you do any reindex before shrinkdatabase which will increase the file size?
Maybe you can try this statement
DBCC SHRINKDATABASE (databasename,5, TRUNCATEONLY);
5 leave 5% free space remaining in the database after shrunk
TRUNCATEONLY will release all free space at the end of the file to the operating system.
Hope that will help.
Kevin
I had the same problem again with a different data file. I didn't try your method, my code was:
DBCC SHRINKFILE (N'Data2' , 190840)
After the "shrink completed a day later, the file is 223 Gig with 0 free space, worse than when I started !
Am I causing index fragmentation that's filling up the space just as it gets freed up ?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply