Post reply

Maintenance plan RMunusedspace on databasefile

  • July 1, 2009 at 2:39 am

    #72241

    Hi everyone,

    I encounter a problem with a 22Go database (.mdf file) which don't want to return unused space in the datafile to the operating system !

    There is a maintenance plan :

    EXECUTE master.dbo.xp_sqlmaint N'-PlanID xxxx-Rpt "D:\log\DB Maintenance db_truc.txt" -DelTxtRpt 1WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '

    The database file is 22826 Mo (automatically grow, 1 Mo, with no restriction).

    My question : is the "RMunusedspace" option do a DBCC SHRINKDATABASE or DBCC SHRINKFILE ?

    I have read many things about the difference between the SHRINKDATABASE and the SHRINKFILE but I would like someone to confirm/explain what is the (main) difference(s).

    What I think I have understood :

    - the shrinkdatabase cannot return unused space if the autogrow option is activated --> the autogrow option modify the database structure (with alter database), so the shrinkdatabase cannot return space if it's smaller than the allocated size (which can change "because of" the autogrow option).

    - the shrinkfile can return the unused space (by specifying argument) to the system, no matter of the allocated space.

    Thanks in advance for at least reading my post 😉

    Vincent

  • July 1, 2009 at 10:03 am

    #1018541

    I don't know the answer, but I know how you can find out - Run the profiler and watch what command it sends while the maintenance plan is running. You should be able to filter by user or just let it capture everything and search for DBCC. I love the profiler - nothing can hide from me there (insert evil laugh).

    Good luck - if this works, let us know what you find.

    Chad

  • July 2, 2009 at 8:41 am

    #1019197

    Hi Chad,

    thanks for your answer, it has helped me 🙂

    I have taken a backup of the database on a test server, restored it, created the same maintenance plan ; same environment.

    for the command line :

    EXECUTE master.dbo.xp_sqlmaint N'-PlanID xxxx -Rpt "D:\log\DB Maintenance db_sido0.txt" -DelTxtRpt 1WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '

    By activate the trace, I have this :

    eventclass : SQL:BatchCompleted

    TextData : DBCC shrinkdatabase(N'databasename', 10, TRUNCATE_ONLY).

    So, I have used the DBCC Shrinkfile command (by using the GUI) and it works, "I" can return the free space to the operting system. By scheduling the operation, a job is created (so, the command appears!)

    I don't how to close this post but i've found the solution/answer thanks to Chad.

    Vincent

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

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