Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBCC Shrinkfile & Shrinkdatabase


DBCC Shrinkfile & Shrinkdatabase

Author
Message
Lian Pretorius
Lian Pretorius
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1317 Visits: 1250
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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1341 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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1479 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.
Lian Pretorius
Lian Pretorius
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1317 Visits: 1250
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1215 Visits: 2160
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
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6362 Visits: 13687
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!

---------------------------------------------------------------------
Lian Pretorius
Lian Pretorius
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

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

Is there anything else I can/should look at?
bassemzayed
bassemzayed
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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,



Lian Pretorius
Lian Pretorius
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1317 Visits: 1250
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
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 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