Post reply

Best practice needed for shrinking SQL Server Databases

  • March 22, 2012 at 7:40 am

    #258945

    We were running out of space on a SQL Server 2008 instance. I shrank all of the "Log" files.... still needed space. I then shrank the largest Database. While this gave me back space... I read an article that said this caused index fragmentation. I tried to run an Index Rebuild... but that blew off the disk by creating a database twice as large as the original. It seems like a no win situation. Please advise as to what I can do to solve this issue as well as implement an ongoing Best Practice maintenance plan.

    Thank you in advance,

    Charlie

  • March 22, 2012 at 7:52 am

    #1462718

    best practice is to never shrink the databases, unless you know a one time ETL process ro something expanded a database.

    the thing to do now, i think is to look at the space hogs.

    are the backups all on the same drive as the databases? you could archive them to a network location or to disk or tape.

    check for log files or .trc files to see if they are filling up your drive.

    check to see if your msdb is bloated with gigs of history you don't need.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 22, 2012 at 8:02 am

    #1462728

    Hi Lowell,

    Thank you for the information. The backups are on a separate drive... this actually an instance on an Active\Active Cluster and we have a completely separate drive for the backups. The log files as well are on a separate drive on our SAN. I will look into the ".trc" files. I have a job to clean up the history files... but that is also worth checking. Thank you again for the information as well as your time.

    Charlie

  • March 22, 2012 at 8:45 am

    #1462764

    Looks to me like you need to add additional space to your system.

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

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