Post reply

Database cleanup?

  • July 7, 2008 at 5:13 pm

    #839224

    sqlservercenter (7/7/2008)

    shrink it at each weekend.

    Absolutely not... it's just going to grow again and all you're doing by shrinking it is cause fragmentation of the underlying files at the OS level.

    --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)
    Intro to Tally Tables and Functions

  • July 7, 2008 at 6:31 pm

    #839236

    sqlservercenter (7/7/2008)

    shrink it at each weekend.

    Did you read the link I posted earlier in the thread before recommending this action? The link doesn't go into the details about why shrinking is disasterous for fragmentation, but you can find that online easily.

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

  • July 8, 2008 at 6:14 am

    #839422

    With an objective have having a reasonable size files that have sufficient free space to run the defragementation and support future needs, here are the detailed steps. The database will not be usable during this process.

    1. Disable any maintenance jobs and any application access.

    2. Backup the database.

    3. Record the database recovery option and, if not simple, change to simple.

    4. If there are any archiving or purge routines, run them now.

    5. Rebuild the indexes.

    6. Check the space used.

    7. Get the space used by the largest table.

    8. If the file sizes are significantly larger than the space used, shrink the file and then, run the rebuild index again. The final file size should be space used plus the size of the largest table plus future growth needs (anywhere from 3 to 6 months is about right).

    9. Check the level of windows file fragementation and defragement if needed.

    10. Change the database recovery option back to the original.

    11 Enable any maintenance jobs

    Commands are:

    To defragment the indexes, run from a Windows command prompt:

    sqlmaint.exe -RebldIdx -SupportComputedColumn -S server_name[\instance_name -D database_name

    To get the total space used, run:

    select sysfilegroups.groupid

    , Files.FileId

    , sysfilegroups.groupname

    , Files.FileName

    , Files.AllocatedMb

    , Files.SpaceUsedMb

    , Files.AllocatedMb - Files.SpaceUsedMb as SpaceFreeMb

    from dbo.sysfilegroups

    JOIN (

    SELECT sysfiles.FileId

    , sysfiles.name AS FileName

    , sysfiles.groupid

    , (sysfiles.size * 8) / 1024 AS AllocatedMb

    , ( (CAST(FILEPROPERTY(sysfiles.name, 'SpaceUsed' ) AS int) * 8 ) / 1024) AS SpaceUsedMb

    FROM dbo.sysfiles

    ) as Files

    on sysfilegroups.groupid = Files.groupid

    order by sysfilegroups.groupid

    , Files.FileId

    SQL = Scarcely Qualifies as a Language

  • July 8, 2008 at 8:00 am

    #839509

    1) prior to doing a windows file defrag it is strongly recommended to shut down sql server.

    2) I sure would want to do a DBCC CHECKDB after all that work under simple recovery mode prior to releasing the database back to production ops.

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

  • July 9, 2008 at 3:11 pm

    #840527

    Thanks once again for the breakdown Carl. Would the windows defrag untility work ok or should we look at another product?

  • July 9, 2008 at 5:44 pm

    #840569

    Here are a few changes to the order of operations:

    1. Disable any maintenance jobs and any application access.

    2. Backup all databases

    New steps:

    3. For tempdb, note the actual size and if different than the specified, change the specified to the actual. An autogrow on tempdb is an expensive operation and can cause calls complaining "the system is slow"

    4. Shutdown SQL Server and run the Window defrag utility - you may need to run more than once.

    5. Run CHECKDB on all databases - if there are any errors, contact Microsoft.

    The perform the remainder of steps starting with:

    3. Record the database recovery option and, if not simple, change to simple.

    SQL = Scarcely Qualifies as a Language

  • July 10, 2008 at 5:42 am

    #840737

    Article "Top Tips for Effective Database Maintenance" was just posted by

    Paul S. Randal, SQL Server MVP, at http://technet.microsoft.com/en-us/magazine/cc671165.aspx

    Provides a good background on physical optimization.

    SQL = Scarcely Qualifies as a Language

Viewing 7 posts - 16 through 21 (of 21 total)

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