Unable to shrink Database

  • Ashish-437288

    Mr or Mrs. 500

    Points: 572

    I have a production databases running on SQL Server 2008 R2 Ent editionand.The size of this DB is 170 GB. Unallocated space available in this database of around 130 GB. Please see below stats of this database:

    database_name database_size unallocated space

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

    Manager 174929.31 MB 131038.92 MB

    reserved data index_size unused

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

    44329360 KB 38557904 KB 5342496 KB 428960 KB

    I have tried to shrink this database from GUI & through command (DBCC SHRINKFILE), but unable to do so. Unallocated space is remain the same.

    I have tried following ways to get this DB shrink:

    1) Defrag all the indexes then ran DBCC SHRINKFILE

    2) Trunate T-Log file then ran DBCC SHRINKFILE

    3) Manually ran Ghost cleanup stored procedure then ran DBCC SHRINKFILE

    but finally the Output of DBCC CHECKALLOC is still same:

    The total number of extents = 740590, used pages = 5808614, and reserved pages = 5923760 in this database.

    I have tried all possible way but DB is not getting shrink. Can any one help me on this issue?

  • PiMané

    SSChampion

    Points: 13961

    What's the recovery mode for the database?

    1) Defrag all the indexes then ran DBCC SHRINKFILE

    Don't do this... defrag indexes and the shrink database will ruin the indexes again.

    Pedro



    If you need to work better, try working less...

  • TheSQLGuru

    SSC Guru

    Points: 134017

    It could well be the tlog that is taking up all the space you see in that particular output. try dbcc sqlperf(logspace) and see if you have a huge tlog. If so, try a tlog shrink. Note that you may not be able to reduce the size depending on where the currently active Virtual Log File endpoint is. I believe you can add junk transactions to get it to wrap around to the beginning again, at which point a truncate/shrink of the log file should work fine.

    Another possible solution for the tlog issue (use with care!!) is to detach the database and reattach while having sql server build a fresh (tiny - be sure to grow it appropriately afterwards) tlog for you.

    If it truly is the data file that is at issue here, will need a deeper check to find out why it won't shrink.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Ashish-437288

    Mr or Mrs. 500

    Points: 572

    Hi,

    Thanks for your reply. Please find stats for dbcc sqlperf(logspace)

    Database Name Log Size (MB) Log Space Used (%) Status

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

    Manager 599.9922 0.668954 0

    Log file size is very less.

    Please find DB files stats:

    type_desc data_space_id name size

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

    ROWS 1 M_DATA 22314152

    LOG 0 M_LOG 76800

  • Ashish-437288

    Mr or Mrs. 500

    Points: 572

    Moreover, is there any way to recover Reserved space from SQL Server (except DBCC SHRINKFILE). Please see below tables which are top contributers of un-used/reserved space.

    Table Name # Records Reserved (KB) Data (KB) Indexes (KB) Unused (KB)

    dbo.Table1 267,576 21,851,536 21,838,592 3,408 9,536

    dbo.Table2 817,218 3,791,312 3,752,296 38,512 504

    dbo.Table3 209,958 3,687,544 3,347,664 8,232 331,648

  • TheSQLGuru

    SSC Guru

    Points: 134017

    You either shrink or you export all data, build a new database, and import all data.

    I am afraid that getting deeper into why you cannot shrink the database file (it isn't the log file) will be beyond the ability of a forum to assist. You may wish to try Microsoft support or some other form of direct help.

    Good luck! Maybe someone else has some ideas here.

    P.S. I suppose someone having an open transaction could prevent the full shrink? dbcc opentran to find that.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • PiMané

    SSChampion

    Points: 13961

    Hi,

    You could try to close all transaction by setting the database in single user mode... if possible...

    USE master

    GO

    ALTER DATABASE [DATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO

    USE [DATABASE]

    GO

    DBCC SHRINKFILE (N'[DATABASE_LOG]' , 0, TRUNCATEONLY)

    DBCC SHRINKFILE (N'[DATABASE_DATA]' , 0, TRUNCATEONLY)

    GO

    USE master

    GO

    ALTER DATABASE [DATABASE] SET MULTI_USER WITH ROLLBACK IMMEDIATE

    GO

    Pedro



    If you need to work better, try working less...

  • andreas.kreuzberg

    SSCertifiable

    Points: 6043

    did you a full backup of the transactionlog, or of the database?

    i can only shrink my transactionlog if i made backup before.

  • Joshua M Perry

    SSCrazy

    Points: 2655

    You mentioned that you truncated the trans log, but that's no longer possible with SQL 2008 R2. Are you certain the log was truncated? Regardless, the right way to do this is to first run a backup of the trans log and then immediately perform the shrink before another lock is granted at the tail of the log. Anything else will affect your backup chain and prevent you from being able to restore to a point in time, which is why it's no longer possible to truncate a log in SQL 2008 R2. Try something like this and see if it resolves your issue.

    BACKUP LOG [databasename]

    TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' WITH COMPRESSION;

    USE [databasename];

    GO

    DBCC SHRINKFILE (logfilename_log, 0);

    GO

  • saqlainkhan

    Ten Centuries

    Points: 1126

    if you have tried all and still unable to release the space and you absultuly need to ... go this route

    1*- On the shrink file chose your File Tyep - Log and select the file name (if multiple)

    2*- on Shrink Action "select reorganize pages before releasing unused space" and shrink it to desire size.

    Drawback on this is it will cause massive fragmentation.

    refer:

    http://technet.microsoft.com/en-us/library/ms190757.aspx

    http://www.sqlservercentral.com/Forums/Topic863273-146-1.aspx#bm863293

    That will take you where you need to be.

    Do you know the reason why this was bumped up so high? And is there a reason why you would need to be released back?

    Noli Timere

Viewing 10 posts - 1 through 10 (of 10 total)

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