Shrink database not working?

[Gooser]

I've got a database that shows the following properties:

Size: 13865.04 MB
Space available: 13140.41 MB

Note that the used space is very small in relation to the size. I tried shrinking it, but that didn't seem to work. I even updated the statistics, but that didn't change it either. Am I missing something obvious?

--Gooser

 

[katgirl]

what was the exact command you used?

to take the space out, you need the DBCC SHRINKDATABASE with:

TRUNCATEONLY
Causes any unused space in the data files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_percent is ignored when TRUNCATEONLY is used.

also, i don't think it's possible to shrink a database smaller than it's original creation size, someone correct me if i'm wrong

 

[Gooser]

I do hope that you are wrong about shrinkage. I created this database by scripting out another and now its gone 'live' and I've realized that it is WAY too big for the amt of data it holds. Worse yet, I have to back this up daily, and I think I am getting HUGE, empty backups because of it.

Though I may be barking up the wrong tree.



--Gooser

 

[Jamfool]

use dbcc shrinkfile.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_8b51.asp

 

[Catadmin]

If using ShrinkDB with TruncateOnly isn't working, try reallocating your data then shrinking the database with TruncateOnly to slice off the free space.

Shrink / TruncateOnly by itself won't work if there is data on everyone one of the pages that is currently "held" by your database.





Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mutley1]

If a dbcc shrinkfile isn't working then there's the possibility that there's external AND / OR internal fragmentation. If you can run a defrag on the disk using the OS tools then that might do something. Run that then try the shrinkfile again. If not, then you may want to consider a dbcc reindex / dbcc indexdefrag (depending on your DB's usage by the punters or if you can take it offline / do it at a weekend).

Last resort (depending on your disk size) may be to detach the DB, move it off the disk, do an OS defrag, move it back and attach it then try again. (This is because a defrag of the disk in the 1st description may not have sufficient space to jig everything around, depending on free space and other data on the disk).

I've had this problem in the past and 1 of the above has eventually resolved the problem.

Rgds,

M.

 

[mutley1]

Gooser,

Have a read of this - covers the OS side and the SQL side of fragmentation.

http://www.sql-server-performance.com/sql_fragmentation.asp

HTH,

M.

 

[mrdenny]

You may have data towards the end of the physical file. If this is the case you won't be able to shrink the file past that block. You'll want to do a DBCC SHRINKFILE without the truncate only so that all the data is shifted around. This may take a while to complete.

When you backup the database it doesn't backup the free space. Only the used space is backed up. Your backups should be small if the database is empty.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Gooser]

DBCC SHRINKFILE ('[i]FILENAME[/i]_Data')

returns

DbId   FileId CurrentSize MinimumSize UsedPages   EstimatedPages 
------ ------ ----------- ----------- ----------- -------------- 
27     1      930784      128         930768      930768

(1 row(s) affected)

Am I reading this right to believe that I can't shrink this database? My problem here is that I need to fit it onto a DVD and it is right around 5.5GB.

Thanks,
Gooser

 

[Catadmin]

Is it still showing the same size and same amount of free space as per your first post?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Gooser]

Size:             13865.04
Space available:  10693.36

--Gooser

 

[SQLBill]

If you don't use TARGET SIZE, then it only shrinks it to the default file size. Try this:

DBCC SHRINKFILE ('FILENAME_Data', 4000)

Target size is in MB.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

I usually go with 10 Percent target size, which leaves 10% free space available for current/future transactions until it needs to grow again.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

According to the output from your DBCC SHRINKFILE the file is about as small as it's going to get. Your EstimatedPages is 930768 which is about the size of your database file (930784). Based on this and Microsoft's explination of the EstimatedPages column:

Number of 8-KB pages that the Database Engine estimates the file could be shrunk down to.

You won't be able to shrink the file any more.

What method are you using to get the amount of free space within the database file?

The meta data for the database may be out of date. Try running this:

use {DatabaseName}
go
exec sp_spaceused @updateusage='true'
go

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Gooser]

DbId   FileId CurrentSize MinimumSize UsedPages   EstimatedPages 
------ ------ ----------- ----------- ----------- -------------- 
27     1      930784      128         930768      930768

 

[mrdenny]

Your database file is 7.2 Gigs. Your entire database is 13.5 Gigs. Check the size of your log file. I think you'll find that it's rather on the large side.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Gooser]

Denny,

I missed something... I guess. How did you come up with those numbers.

--Gooser

 

[mrdenny]

Number of pages *8k / 1024.

930768*8k = 7446144k
7446144k/1024/1024 = 7.1g

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Gooser]

Duh all over me, got'cha.

--Gooser