August 5, 2002 at 11:09 am
I have seen several posts on log files that wont shrink - and remember reading an excellent article by Karen Delany on the subject.
However I now have an MDF that will not shrink
- over 95% of the used file space is free
- SQL 7.0
- Not trying to shrink to smaller than its original size
- Not trying to shrink to smaller than the size in model db
- dbcc shrinkfile says it has succeeded - but I see no change.
Any thoughts ?
Tim
August 8, 2002 at 6:27 am
I feel like Billy no mates by replying to my own thread!
I have just found out that this database was upgraded to SQL7 from 6.5 - could this be why it will not shrink? I can find nothing to support this - has anyone had a simelar experience with an upgraded database?
Thanks
Tim
August 8, 2002 at 6:37 am
Shrinkfile often isnt enough, has to do with virtual log segments. We've got a script on the site that will force the shrink:
Andy
August 8, 2002 at 6:57 am
Andy,
Thanks for that -
But in this case it is not the transaction log that is the problem - its the data file
Tim
August 8, 2002 at 7:27 am
Have you tried running DBCC SHRINKFILE with first the NOTRUNCATE option to move all the data to the front of the file followed by running it again with TRUNCATEONLY to cause the unused space to be released?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
August 8, 2002 at 8:24 am
Brian,
I just gave that a go --
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
7 1 298288 2520 1784 1784
(1 row(s) affected)
I give in - I am going to use DTS to create a copy of the database, delete the original and attach the new mdf
Thanks anyway
Tim
August 8, 2002 at 8:44 am
I shouldnt answer posts early in the morning.
Andy
August 8, 2002 at 3:25 pm
I shouldnt answer posts early in the morning.
Andy
August 13, 2002 at 5:10 am
We have the same problem, unfortunately I can't take your approach i.e. new database and copy as used space = 19Gb, free = 15Gb. Does anyone have any other ideas.
I have tried dbcc shrinkdatabase, shrinkfile, with truncate without truncate and nothing seems to work
Thanks,
Paolo
August 15, 2002 at 12:28 am
I have encounter this problem before.
Backup your db first,
then turn on following database option
auto close
auto shrink
truncate log on checkpoint
then wait a monment. maybe this can solve your problem.
Edited by - dannyshu on 08/15/2002 12:44:27 AM
August 15, 2002 at 12:47 am
I use a command in a regular job like this:
DBCC SHRINKFILE (MYDB_data, 1000)
... and it seems to work. The database (.mdf) in question routinely grows to over 2 gigs and can never shrink below about 1.4 gig even though my shrinkfile is set for 1 gig. It seems that it will only go as low as it can and that's it. As the users add mor data, I don't doubt it will grow to say 5 gigs and never shrink below, say, 3 gigs.
quote:
I have seen several posts on log files that wont shrink - and remember reading an excellent article by Karen Delany on the subject.However I now have an MDF that will not shrink
- over 95% of the used file space is free
- SQL 7.0
- Not trying to shrink to smaller than its original size
- Not trying to shrink to smaller than the size in model db
- dbcc shrinkfile says it has succeeded - but I see no change.
Any thoughts ?
Tim
G. Milner
August 15, 2002 at 2:09 am
A quick update...
Earlier this week - I made a copy of the database using DTS - compared the two databases with RedGate SQLCompare and DataCompare. Detached both databases, and reattached the new mdf with the old database name.
The new mdf expands and shrinks with no problem!
Thanks
Tim
August 15, 2002 at 3:35 am
Tim,
You didn't mention if RedGate came back with any differences. My database is 34.2Gb total - 20.8G used and 13.3G Unused. I ran "DBCC UPDATEUSAGE(0)" and it came back with lots of errors. Tried to shrink again using shrinkfile/shrink database with and without truncate - still nothing happens. I am reluctant to use DTS to create a new copy as I guess it will take a very long time to copy ( comparing DTS speed to BCP ). Can you think of anything else other than your solution that might help. Also I am a developer not a DBA so please explain how you detach/attach the mdf to the database name. Do you mean updating the 'filename' column in sysfiles.
Many Thanks,
Paolo
P.S. - Glad to see someone else has had this problem, I have another 20+ databases and none of the others have this problem. I thought I was going Mad!
August 15, 2002 at 6:51 am
Paolo,
Thanks for the reply-
To clarify the Redgate software found no differences between the two databases; '=' signs all over the place.
The amount of used space in the file was less than 20MB - which is why I used DTS - I would still use DTS even if the database had been far bigger.
An alternative could have been to generate scripts for all objects in the database - use this to create the new database and the BCP all data into this new database
More information on attach/detach:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_da-di_83fm.asp
and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp
Tim
August 15, 2002 at 7:42 am
I've had this problem on a 16GB DB here. I actually had to stop and restart the SQL Server Service to get it to shrink to a specific size. I had two data files and wanted to shrink the primary file that was on a seperate disk. I migrated the data over using the emptyfile option which made 9MB of space used in a 4GB file. I then tried to shrink it and it reported successful. However, it did not shrink. Once I restarted the service, it shrank. Weird but it worked.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply