SQL Server Performance

What to do when disk space is getting too short

Discussion in 'SQL Server 2005 General DBA Questions' started by EMoscosoCam, Jul 3, 2008.

  1. EMoscosoCam Member

    Hello
    I have a database that has a single mdf file. I would like to know that if the database is too big for the disk that it is stored, then I should buy a new disk and move the entire file there or there is a better tecnique.
    Thanks a lot.
  2. preethi Member

    You have a couple of Options
    ( I Assume that the table does not contain temporary tables, created for testing but never deleted)
    1. Add a new disk and add a file to the same filegroup. SQL Server will take care of the data
    2. Add a new disk and a file in a new filegroup. You need to manually more the table into the new file group. Try this link: http://www.sqljunkies.com/HowTo/B9F7F302-964A-4825-9246-6143A8681900.scuk
    I will rcommend the second option if you really want to wet your hand with SQL Server Administration. It gives better control to you not only on disk size, but also on backup and restore. If you are not ready for it yet, you can start with the first option and gradually try the second option
    All the Best!
  3. Mahmoud_H New Member

    ... You need to manually more MOVE the table into the new file group ....
    (just simple typing error preethi) [;)]
  4. preethi Member

    thanks for correcting it.
  5. EMoscosoCam Member

    when the database is split in more that one MDF file, the backup becomes more that one bak files or it stays to be a single back file? Can I tell the server to generate the backup in a disk of another computer?
  6. Mahmoud_H New Member

    you can backup on a shared folder using the UNC path of the file name.

    creat share with r/w permissions in the other computer , and use it as:
    \server1ackupmydb.bak
    or
    \server1d$ackupmydb.bak
    other method to move database to the new disk
    1) after adding the new disk , you can move the database to the new disk by move wizard in sql2005
    or
    2) deattch and attach from management studio.


  7. preethi Member

    There is not direct relationship between the number of data files and the number of files in a backup. You can take backup in a single file or multiple files irrespective of the number of data files. The advantage of having multiple data files is you can back them seperately (if you want, but not a must) and it is very much useful if your backup takes time.
    Addtionally, moving the data files into different drives is also easy. In caase of any disaster, you dont have to search for a for a big drive to load the single MDF file.
    The last question is already answered.
    All the best
  8. satya Moderator

    First things first... does the server has capability to increase the capacity on disks?
  9. EMoscosoCam Member

    yes it does.
  10. satya Moderator

Share This Page