Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Unanswered: Large SQL Database Size

    Hi,

    Long story so ...SQL admin resigned and I ended up managing the SQL server (I'm the developer). We were having some issues with disk space and when I run sp_spaceused on our database I got the following results:

    database_name database_size unallocated space
    MyDB 56207.88 MB 45544.69 MB

    reserved data index_size unused
    6995264 KB 6166912 KB 809864 KB 18488 KB

    The DB has a SIMPLE RECOVERY model. What would be the best practice to reclaim space? Do I just do it directly SSMS where I can run the task to SHRINK the FILE?

    Best shrink action to take?
    (1) Release unused space
    (2) Reorganize pages....
    (3) Migrating data to other filegroups.....

    TIA

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    I have no or little experience in reducing the size of a database. What I've done so far is simply run Shrink using Management Studio or re-create the whole db (I'm also a developer). I suppose adding a new harddrive is not an option?

  3. #3
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Adding disks is usually pretty easy - especially when you're only talking about a 56GB database.

    If you really wanted to shrink the file, you could use DBCC ShrinkFile

    However, what you need to consider first is why are your database files at the size they are at:
    1.) Did previous DBA just overestimate file sizes when creating the database
    Or
    2.) Did something put a load of data in the database which extended the file size out that has subsequently been removed, if so is it possible another task will run that does something similar again in the future.

    Remember increasing the size of a file takes time - if you have a transaction running that is going to pump 10GB of data into a database even temporarily, it needs the space to do it. If that space is not there it will need to spend a minute or two extending the file which slows down not only the transaction doing the import but also potentially other transactions.

  4. #4
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Quote Originally Posted by EngadaSQL View Post
    Adding disks is usually pretty easy - especially when you're only talking about a 56GB database.

    If you really wanted to shrink the file, you could use DBCC ShrinkFile

    However, what you need to consider first is why are your database files at the size they are at:
    1.) Did previous DBA just overestimate file sizes when creating the database
    Or
    2.) Did something put a load of data in the database which extended the file size out that has subsequently been removed, if so is it possible another task will run that does something similar again in the future.

    Remember increasing the size of a file takes time - if you have a transaction running that is going to pump 10GB of data into a database even temporarily, it needs the space to do it. If that space is not there it will need to spend a minute or two extending the file which slows down not only the transaction doing the import but also potentially other transactions.

    Thanks. It was #2. We decided to upload images to the SQL server as a binary/image file. Initially, the JPG images were at its full size (several MB big per image). I had several trials on images uploads with different file size. Eventually, I resized them to a manageable level (~20K each). I didn't notice I was getting hit with an expanding DB until I saw I had 2 GB left on the server disk.

    Also, I was not sure about using SHRINKFILE or SHRINKDATABASE since I was reading recommendations not to use it because it causes fragmentation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •