SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBCC Shrinkfile & Shrinkdatabase


DBCC Shrinkfile & Shrinkdatabase

Author
Message
Skandalis
Skandalis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1691 Visits: 1301
Hi there,

When running sp_spaceused on a database I get the following:

Database Size: 108 493.94 MB
Unallocated Space: 98 738.68 MB

Reserved: 9 988 616 KB
Data: 5 957 424 KB
index_size: 3 200 200 KB
unused: 830 992 KB

This is a copy of our production database that we need to restore in our Dev/Training environments. We’ve deleted/truncated 'old' transactional & only kept recent data.

The Primary file is still over 110GB, but data & indexes only add up to about 10GB. Can anyone tell me what I’m doing wrong with my shrinking operations? Or am I missing anything?
I’ve followed both these KB’s:

http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/aa258287.aspx

To release the unallocated space mentioned above, I have tried the following:


USE 'dbname'
GO
DBCC SHRINKDATABASE 'dbname', TRUNCATEONLY
GO



I’ve also tried DBCC SHRINKFILE, using Management Studio, but to no avail.

I’ll appreciate any recommendations.

Thanks,
Lian
Mohan Kumar
Mohan  Kumar
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2213 Visits: 596
use

DBCC shrinkfile (filename, expected size in MB , notruncate)

once this is completed then

DBCC shrinkfile(filename, expected size in MB , truncateonly)

This will do trick.

--www.sqlvillage.com
SuperDBA-207096
SuperDBA-207096
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3005 Visits: 711
If the database was using replication in production you may have to also mark the transactions as complete in order to be able to shrink the logfile.
Skandalis
Skandalis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1691 Visits: 1301
Mohan Kumar (3/20/2008)
use

DBCC shrinkfile (filename, expected size in MB , notruncate)

once this is completed then

DBCC shrinkfile(filename, expected size in MB , truncateonly)

This will do trick.


Thanks Mohan, will give it a try.

Mark, thanks, the log is under control - Currently at 100 MBSmile
Adam Bean
Adam Bean
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6706 Visits: 2222
The above is correct, but something that makes life pretty easy when constantly restoring databases from a production environment into DEV/QA is to write a little post restore procedure that...

- Changes recovery models to simple
- Set autoshrink ok
- Shrink log files

---
SQLSlayer
Making SQL do what we want it to do.
george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25058 Visits: 13698
the truncateonly option only deallocates space up to the last used extent, so you were probably not getting much space back as you had an allocated extent near the end of the database.

the notruncate followed by truncateonly option will work but you can just go for not specifying these options and acheive the same result in one hit. As data will be moved to the front of the file it can be time consuming and intrusive, it will also cause fragmentation in the database, so don't run it after rebuilding indexes!

---------------------------------------------------------------------
Skandalis
Skandalis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1691 Visits: 1301
Thanks for the pointers. Unfortunately it's producing the same results...

Is there anything else I can/should look at?
bassemzayed
bassemzayed
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 15
i had issues like this with sql2000/sp4/cluster, and i think some of this problem may happen with sql 2005 before sp2, so i am not sure u have 2005 with or without sp2?

but anyway let me give u an idea about what may be the problem, actually your problem that sql for certain reason (bug) sometimes miscalculate the free spaces over the pages and due to this it consider that tha page is full before actually reaching its full cabacity (it is not the fill factore issue), that's why there will be lots of free spaces unused by your databases, use teh following script to get a detail list of tables in your database and the unused space over each of them


CREATE TABLE #temp (TableName NVARCHAR (128), RowsCnt VARCHAR (11), ReservedSpace VARCHAR(18), DataSpace VARCHAR(18), CombinedIndexSpace VARCHAR(18), UnusedSpace VARCHAR(18))
EXEC sp_MSforeachtable 'INSERT INTO #temp (TableName, RowsCnt, ReservedSpace, DataSpace, CombinedIndexSpace, UnusedSpace) EXEC sp_spaceused ''?'', FALSE'
SELECT TableName, RowsCnt, ReservedSpace, DataSpace, CombinedIndexSpace, UnusedSpace
FROM #temp
ORDER BY TableName
DROP TABLE #temp



u will discover the tables that is eating up your spaces, and what i do in my cases is creating dump clustered indexes over these table , this recover the lost space and then u can shrink and u will get your lost space,,, sorry for my english

regards,



Skandalis
Skandalis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1691 Visits: 1301
Thanks Bessem,

There's also a report you can run from Management Studio for this info.

Unused space in my tables only add up to about 750MB (Againt a total of nearly 95GB total unused space)
Preet_S
Preet_S
Mr or Mrs. 500
Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)

Group: General Forum Members
Points: 596 Visits: 240
What sizes are the actual op sys files (mdf/ldf/ndf) ?
Do these correlate with what sp_spaceused is tellling you?
If the log file is huge run DBCC OPENTRAN to determine the oldest transaction, as indicated in previous posts an old open transaction could be preventing the log file from shrinking significantly.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search