If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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:

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?

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.

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.