Shrink file

  • I have a vendor product that has some tables like track each change or history of activity, which make our tables really big, Currently the database data file reserved 13 gb.

    we don't want to keep those, so vendor send us some maintence script that deletes data to a cut off date for some tables.

    It is a stored procedure that do above task. Basically is to delete some records in some history tables to a cutoff date.

    But then he wrote some code like below after the exc stored procedure command:

    dbcc shrinkfile(SurveyorDB_Data, 1)

    go

    dbcc shrinkfile(SurveyorDB_Log, 1)

    go

    I am a little hesitated to run this, because it shrinks files to 1 MB?

  • This is a duplicate of this post:

    http://www.sqlservercentral.com/Forums/Topic1246826-1550-1.aspx

    Shrink file does not cause any data loss. If you specify 1MB, it will shrink only to the smallest possible size. As your DB has very little unallocated space, this won't free up the disk space. Try rebuilding your indexes first on the tables you've removed data from.

    Again, SHRINKFILE will introduce fragmentation in your indexes if you don't use TRUNCATEONLY. I'd advise rebuilding all your indexes after the shrink. Do the shrink only as a one off operation only after freeing large volumes of space in your data files. 13-14GB is pretty small for a database - If disk space is that tight on your server it might be time to invest in some new disks.

    Hope this helps,

    David

    DBA Dash - Free, open source monitoring for SQL Server

  • sqlfriends (2/3/2012)


    I have a vendor product that has some tables like track each change or history of activity, which make our tables really big, Currently the database data file reserved 13 gb.

    we don't want to keep those, so vendor send us some maintence script that deletes data to a cut off date for some tables.

    It is a stored procedure that do above task. Basically is to delete some records in some history tables to a cutoff date.

    But then he wrote some code like below after the exc stored procedure command:

    dbcc shrinkfile(SurveyorDB_Data, 1)

    go

    dbcc shrinkfile(SurveyorDB_Log, 1)

    go

    I am a little hesitated to run this, because it shrinks files to 1 MB?

    As a sidebar, you should never run 3rd party provided code for such things unless you know precisely what the code is going to do. It's fine to ask for some help but what you really need to do is lookup the command in Books Online (the free "Help" system that comes with SQL Server) and understand the command so that you actually know what people are talking about when you do ask for help. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

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