DBCC Shrinkfile & Shrinkdatabase

  • 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

  • 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.

    [font="Verdana"]--www.sqlvillage.com[/size][/font]

  • 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.

  • 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:)

  • 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

  • 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!

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

  • Thanks for the pointers. Unfortunately it's producing the same results...

    Is there anything else I can/should look at?

  • 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,

  • 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)

  • 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.

  • sp_spaceused & OS correlate.

    The log file is under control & is currently sitting on 100MB.

    The problem is that the MDF file is sitting on 105GB, but I'd like it to be more or less the size of the data + index pages (About 20GB)

  • Preet (4/1/2008)


    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.

    i am still runnign sql 2000/sp4 in my case, and yes actually teh spaces lost correlate to the unsused space over the tables, when the problem happened we opened a case with Microsoft, and after investigation they repprted to me that this is a bug in sql 2000/sp4/cluster in my case, and unfortunatly they will not be able to fix it because of cost blah blah issue, and my only workaround is the clustered indexex to shrink the DB size and recover the unsused space, and i should go for sql 2005 sp2.

  • Lian Pretorius (4/1/2008)


    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)

    This is interesting!, what the reasons for this unused space then? it should be related to something indexes , tables..

  • 1. back up the T-Log first using the command

    BACKUP LOG MyDb TO MyDb_log

    GO

    2.Truncate the Transaction Log using the command

    BACKUP LOG MyDb WITH TRUNCATE_ONLY

    GO

    Truncation does not reduce the size of a physical log file,it reduces the size of the logical log file.To reduce the Physical log file, we need to shrink the file.

    3. Shrink the Log file using the command

    USE MyDb

    DBCC SHRINKFILE (MyDb_Log)

    GO

    I think this might help you.

  • Truncation does not reduce the size of a physical log file, it reduces the size of the logical log file and marks as inactive the virtual logs that do not hold any part of the logical log.

    A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size.

    To know more about this please visit the following link

    http://www.akadia.com/services/sqlsrv_programming.html#Example%20Shrinking%20the%20Transaction%20Log

Viewing 15 posts - 1 through 15 (of 36 total)

You must be logged in to reply to this topic. Login to reply