September 13, 2010 at 2:44 am
As stated earlier there is no scope of adding a new drive. Currently 15 GB space is free on disk. I dont think it isn't enough for overhead to perform shrink.
September 13, 2010 at 4:05 am
Hi
You could try shrink database and check if it works for you.
check in SSMS , you can run shrinkfile for a the data file only as long as there is space left to be freed. it sgould show you how much space is available to be freed.
September 13, 2010 at 4:08 am
if this is a dev box , for the purpose of meeting your immediate space requirement try dropping the indexes on the tables and then work from there.
September 13, 2010 at 7:04 am
This is beginning to sound as if something might be stuck, preventing a shrink.
What is the SQL version?
Assuming SQL2005 or higher:
Are the log file and the data file on the same drive?
What is the "log_reuse_wait_desc" value for this database in sys.databases?
Are you using shrink with a value or truncateonly?
September 13, 2010 at 5:28 pm
Dropping indexes does sounds like idea...
drop, shrink, rebuild indexes...
September 13, 2010 at 5:51 pm
Have you checked what the inital size of the database file was when it was created? SQL doesn't shrink a database smaller than the initial create size. Sof the db was created at 122 GB and then loaded, you will need to butcher the system tables to be able to shrink it.
Cheers
leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
September 13, 2010 at 8:23 pm
That's something logical. Actually this db was created long time back by someone else. Is there any way to know the initial size of a datafile when the db was created?
September 13, 2010 at 9:52 pm
Vivek29 (9/13/2010)
Is there any way to know the initial size of a datafile when the db was created?
Goto database properties( right click it) >> select "files" option >> see initial size(mb) on right pane
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 13, 2010 at 10:34 pm
Is it? Then why initial size can be modified? I meant the file size when db was created and I am sure it couldn't be 122 gb initially for a test db.
September 13, 2010 at 10:43 pm
edit: ignore me, I need to read the whole thread - not just the 1st page!
September 14, 2010 at 10:26 pm
Are you using updated SQLServer SP3? I think it has some bug fixes for deallocate of ntext,image columns when shrink data files.
Three months back, I had similar issues, I tried alot of combinations/options with DBCC SHRINKFILE but nothing works. I installed SP3 in our development server and tried DBCC SHRINKFILE and file size is reduced.
September 14, 2010 at 10:54 pm
bhaskar.mythili (9/14/2010)
Three months back, I had similar issues, I tried alot of combinations/options with DBCC SHRINKFILE but nothing works. I installed SP3 in our development server and tried DBCC SHRINKFILE and file size is reduced.
Then how did it get resolved ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 14, 2010 at 11:12 pm
I install sql server 2005 sp3 and run command DBCC SHRINKFILE <datafile>
please refer the following links for more information
http://www.sqlserverdba.co.cc/2009/05/move-contents-between-files-within.html
September 16, 2010 at 8:13 am
This works for SQL 2005/2008.
The reason you cannot shrink your datafile is very simple. You have tables that contains Blobs (Large datatypes). SQL has a very difficult and complex process of moving blob objects around and this essentially blocks datafile shrinking. After understanding what might be blocking the shrinking I found the following MSDN blog by Paul Randall of MSFT
What I did, successfully, was to SSIS (export) all tables that contained these data types. Just make sure when you export that you also export all contraints, indexes, triggers, etc.
After that step is completed then reindex your entire remaining database for any indexes that are fragmented by 30%. Then shrink your DB and it will shrink.
Finally, SSIS your exported tables back and enjoy your reclaimed space. One important note, everyone here is correct, shrinking will create tons of fragmentation so you will need to reindex after all is said and done. My magic number is anything over 30% fragmentation gets reindexed, but your mileage may vary.
I have shared in your frustration for a long time, using google, forums, even Microsoft support and no one was able to explain this to me so I hope this helps as much as it has helped our organization.
Short history, we ran an upgrade to your system that bloated our database by about 500 gigabytes, in other words, 500 gigabytes of free unused space. I spent many weeks trying to shrink this db back to a more realistic size. After doing the steps above it was a piece of cake.
I have more details, scripts etc. if you are interested.
By the way GilaMonster also mentions Paul Randall. Every single reply besides GilaMonster's is useless to you at this time and I know this because I have been in your shoes.
I'll even try to help Google ranking for this issue.
dbcc shrinkfile, shrink database, shrink datafile, paul randall blob, pointers.
September 20, 2010 at 5:46 am
Its SP2 with SE. Is this the reason?
Viewing 15 posts - 46 through 60 (of 60 total)
You must be logged in to reply to this topic. Login to reply