October 15, 2009 at 7:28 am
Hi
I'm trying to shrink our current mdf file after doing a truncate on a table. The table takes up 97% of the database size and the database is 270 gigs. Why is it that I'm unable to shrink the mdf file? I've tried shrinking the database but its just hangs forever. I've also tried shrinking the mdf and it also just hangs forever. The "initial file" size within database properties is set to 270 gigs and I'm unable to decrease this either via the database properties window or via a alter database script. I've also tried running a script which tries to decrease the size in increments of 50MB and it still fails. The script is located at http://www.sqlservercentral.com/Forums/Topic342307-5-2.aspx.
Can someone please help me out with this issue. I'm not willing to pump the existing tables into another temp database and thereafter renaming the databases to the existing one. I've come acros this suggestion from another site.
I would be greatful if someone could help me out or give alternative suggestions with this request
Thanks
October 15, 2009 at 8:44 am
Can you give us more information like.
How big is the mdf file
how big is the ldf file
if you run dbcc loginfo on that database what do you get
what is your recovery model for that database
when was the last backup done and what type of backup
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 15, 2009 at 9:23 am
Thanks for the response
the mdf is just under 270 GB
the ldf is 100 MB...its in simple recovery mode and the remaning data size is 7 GB.
The last backup was taken this morning it was a full backup and its SQL 2005 SP2 and is running on OS windows 2003
I've also tried switch to full recovery and took a transaction log backup and then tried to run dbcc shrinkfile however teh problem still persists. I've switched it back to simple recovery now
below is the results of running dbcc loginfo
FileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN
22539528192 272370640
2253952262144 272380640
2253952516096 2723401280
2278528770048 2723601280
213107200104857627235012826390000000047700002
2131072001415577627239012826411000000028600238
2131072002726297627240212826411000000028600238
213107200403701762722606426411000000028600238
213107200534773762722706426411000000028600238
213107200665845762723006426411000000028600238
213107200796917762723106426411000000028600238
213107200927989762723206426411000000028600238
2131072001059061762723306426411000000028600238
225395211901337627228012827182000001950200001
227033611926732827229012827182000001950200001
October 15, 2009 at 9:53 am
If I am catching this correctly,
You have a 270GB Data file that Had 7GB free prior to a truncate
Currently you have a 270GB Data file that still only has 7GB free after the truncate.
Did the truncate complete successfully?
Try a dbcc updateusage
I have also run into a case where a reindex on the table was the only thing that ended up releasing the space.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 15, 2009 at 12:25 pm
No Jason - Currently I have a 270 GB file that has 263 GB free after the truncate; prior to the truncate I had a few megabytes free
The truncate completed successfully. I've tried previously to put in a maintanence plan that rebuilt all the indexes
but this didnt help. I've also put in an extra step to update all teh tables statistics but this didn't help either
I think the problem is I'm unable to shrink the file to a value that is less than the initial file size. The "initial size"
you would find in the database properties...in the files option
Is there any other suggestions/recommendations you can think of?
Please advise
Moenier
October 15, 2009 at 1:46 pm
Grasshopper.
You are right in your assumption that you cannot shrink a file below its initial size but you can reclaim the space by moving objects between file groups. I've not tried this procedure but the link below tells you how. Let me know how you get on as I'm intrigued by this one! 😎
Regards, Mark
October 15, 2009 at 3:34 pm
I am fairly certain that using the target_size parameter to SHRINKFILE will reset the initial size of the database.
Have you tried manually running the following:
DBCC SHRINKFILE(file_name, 260000);
The above should shrink the file to 260,000MB - if that works, repeat and decrease the size until you have reached the target size you are looking for.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 15, 2009 at 4:36 pm
Another thing that has worked for me when shrinking when there truly is free space - expand the file by a little bit and then run the command that Jeff has listed.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 15, 2009 at 5:32 pm
Another thing that has worked for me when shrinking when there truly is free space - expand the file by a little bit and then run the command that Jeff has listed.
Do we need to expand data file or log file for this?
Mj
October 19, 2009 at 1:34 am
I've manged to eventually shrink the file. I've basically created another file in the same file group; then shrunk the main file using the option "Empty file by migrating the data to other files in the same file group" Its basically dbcc shrinkfile with the [emptyfile] parameter. This ran for just over 6 hours. I then did the same with the NDF file that I've created ie ran a dbcc shrinkfile ('NDF_File' ,emptyfile); this then ran for over 3 hours thereafter I've deleted the NDF file
Thanks to everyone for your responses and suggestions
October 19, 2009 at 8:01 am
I'm surprised no one asked, but why do you need to shrink it? Will the database grow again? There is a reason it grew to that size and unless usage has changed it will grow to that size again. I just sat in a seminar by Buck Woody where he was adamant that you should NEVER shrink database and log files. I don't know that I 100% agree, but I would acknowledge that he has a lot more experience than I do.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 19, 2009 at 8:18 am
Hi Jack
We need to shrink the db to free up disk space. This database that I'm shrinking contains one massive table +- 260 GB it is basically a log table. Our application just logs(inserts) into this table and the application never reads the data in this table. Its only our developers that reads the data in this table using SQL Managemets Studio. I basically want to backup this table to our Archive SQL server and therafter truncate the table in production and then shrink the database.
This table contains data since inception (ie 5 years worth of data)...going forward we only going to need 6 months worth data. We will be building in a archive stategy to cater for this.
Hope all this makes sense
Thanks
October 20, 2009 at 4:40 am
The reason you did not get the result you wanted from the shrink is that Truncate just marks the table as empty, it dos not release the space it uses. You need to rebuild the clustered index to release space.
As Jack already mentioned, you should not shrink a database if you expect it to grow again within 3 months, except in an emergency. Lack of disk space may be an emergency, but only because of bad planning.
You need to tell your manager how much disk space will be needed, giving enough time to get the space purchased and installed before the situation becomes critical. Many places plan to never fall below 20% free space on the disks, so that if the new space is dlivered late there is some contingency.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 20, 2009 at 4:56 am
Jack Corbett (10/19/2009)
I just sat in a seminar by Buck Woody where he was adamant that you should NEVER shrink database and log files. I don't know that I 100% agree, but I would acknowledge that he has a lot more experience than I do.
I don't agree either:
I have come across a lot of databases like the one in this thread. Some sort of logging is built into an application with no archive strategy. The database grows over years until there is a disk space problem. Then you find out that only need six months worth of data is ever required. So you have a 50 GB database with 5 years worth of data and you only need 6 months worth. Delete 4.5 years worth of data and shrink the gb down to 5Gb. Then put in a solution which keeps the data at six months.
What would be the purpose of keeping the database at 50Gb?
November 17, 2009 at 11:53 am
Moenier,
After you move data [empty] from .mdf file to .ndf file, could you reset the .mdf to lower size?
Thanks
Siva
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply