Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

DBCC Shrinkfile & Shrinkdatabase Expand / Collapse
Author
Message
Posted Thursday, March 20, 2008 6:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:08 AM
Points: 1,230, Visits: 1,194
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
Post #472193
Posted Thursday, March 20, 2008 10:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 3, 2013 4:44 PM
Points: 1,336, Visits: 595
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
Post #472408
Posted Friday, March 21, 2008 5:52 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, 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.
Post #472741
Posted Friday, March 21, 2008 7:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:08 AM
Points: 1,230, Visits: 1,194
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 MB:)
Post #472776
Posted Friday, March 21, 2008 7:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:53 AM
Points: 1,145, Visits: 1,932
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.

Post #472787
Posted Friday, March 21, 2008 7:59 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:21 PM
Points: 5,975, Visits: 12,883
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!



---------------------------------------------------------------------

Post #472814
Posted Monday, March 31, 2008 3:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:08 AM
Points: 1,230, Visits: 1,194
Thanks for the pointers. Unfortunately it's producing the same results...

Is there anything else I can/should look at?
Post #476855
Posted Monday, March 31, 2008 11:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 10:47 PM
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,



Post #477417
Posted Tuesday, April 1, 2008 5:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:08 AM
Points: 1,230, Visits: 1,194
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)
Post #477530
Posted Tuesday, April 1, 2008 6:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:45 AM
Points: 236, Visits: 239
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.
Post #477541
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse