October 29, 2009 at 10:33 am
Hello everyone,
Having a bit of an issue here. We have a test server, which is low on space. One specific database takes up 40GB of that space. I have tried everything to get it to release the information back to drive, but I am not having any luck. Was hoping that someone here might be able to lend a hand.
Here is what I have done so far and the results from them.
sp_spaceused
database_namedatabase_sizeunallocated space
LastNight 39131.88 MB 38340.21 MB
reserved dataindex_sizeunused
809384 KB645448 KB154096 KB9840 KB
dbcc shrinkdatabase (usaloe_lastnight, 10, notrucate)
DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages
2415008720 128101184 101168
242160 128160 128
dbcc shrinkdatabase (usaloe_lastnight, 10, truncateonly)
DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages
2415008720 128101184 101168
242160 128160 128
Thinking that the current file size is preventing me from actually shinking the database, I ran the following.
alter database lastnight
modify file ( name = jeff_data
, size = 5000MB
)
which gives me
Msg 5039, Level 16, State 1, Line 1
MODIFY FILE failed. Specified size is less than current size.
So, after all of this, I come to you.
Thanks for the assistance.
Fraggle.
October 30, 2009 at 6:33 am
What service pack are you running? I remember SQL Server 2k5 had a bug. If you have SP1 or earlier and use blob data type (e.g. text), the database cannot shrink to the size smaller than it was created, or smaller than the size it shrinked before.
SP2 or later is fine.
October 30, 2009 at 7:10 am
Check out DBCC SHRINKFILE instead of DBCC SHRINKDATABASE
October 30, 2009 at 7:26 am
peterhe (10/30/2009)
What service pack are you running? I remember SQL Server 2k5 had a bug. If you have SP1 or earlier and use blob data type (e.g. text), the database cannot shrink to the size smaller than it was created, or smaller than the size it shrinked before.SP2 or later is fine.
I am running SP2.
October 30, 2009 at 7:30 am
Ian Scarlett (10/30/2009)
Check out DBCC SHRINKFILE instead of DBCC SHRINKDATABASE
I have tried it, but I did it again just to make sure.
dbcc shrinkfile (jeff_data, 5000)[\code]
DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages
2415008720128101184101184
October 30, 2009 at 7:46 am
Then it could be the transaction log (or another file in the database) that is taking up all the space.
Run sp_helpdb [dbname] ...that should tell you which file is taking up the space.
If it's the transaction log taking up the space, then check what recovery mode the database is in. If it's anything other than SIMPLE, make sure you are taking regular transaction log backups... if you aren't, it's just going to keep growing. As it's only a test server, you can probably bin the transaction log backups as soon as you run them (or switch the recovery mode to SIMPLE). Once you've done that, try running DBCC SHRINKFILE on the log file.
October 30, 2009 at 1:35 pm
Double chekced, The bug I mentioned is fixed in SQL SERVER 2005 SP3. You may want to apply SP3 and try again
October 30, 2009 at 2:41 pm
Ian Scarlett (10/30/2009)
Then it could be the transaction log (or another file in the database) that is taking up all the space.Run sp_helpdb [dbname] ...that should tell you which file is taking up the space.
If it's the transaction log taking up the space, then check what recovery mode the database is in. If it's anything other than SIMPLE, make sure you are taking regular transaction log backups... if you aren't, it's just going to keep growing. As it's only a test server, you can probably bin the transaction log backups as soon as you run them (or switch the recovery mode to SIMPLE). Once you've done that, try running DBCC SHRINKFILE on the log file.
Database is in simple recover mode prior to all of the above statements.
Fraggle
October 30, 2009 at 2:42 pm
peterhe (10/30/2009)
Double chekced, The bug I mentioned is fixed in SQL SERVER 2005 SP3. You may want to apply SP3 and try again
I will give that a shot this weekend when no one is on the server.
Fraggle.
October 30, 2009 at 3:07 pm
Run this query to see the size of each database file, used space, and unused space.
select
[FileSizeMB]=
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB]=
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB]=
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName]= a.name
from
sysfiles a
Results:
FileSizeMB UsedSpaceMB UnusedSpaceMB DBFileName
------------ ------------ ------------- ----------------------
24994.94 22300.50 2694.44 MyDatabase_Data
17762.50 119.12 17643.38 MyDatabase_Log
(2 row(s) affected)
If a data file (not a log file) has a lot of unused space, you can use this script to shrink it:
Shrink DB File by Increment to Target Free Space
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply