SQL Server Performance

extending data file onto new drive

Discussion in 'ALL SQL SERVER QUESTIONS' started by Trev256b, May 6, 2013.

  1. Trev256b Member

    1) how do you extend a database data file onto another drive, when the drive is running out of disk space? i heard there is a way of doing this via a script but can't find it documented.
    2) are their any disadvantges of this?
  2. Luis Martin Moderator

    Creating a new filegroup in the new drive and moving some tables?
    This is what your are looking for?
  3. FrankKalis Moderator

    I think you're looking for DBCC SHRINKFILE with the EMPTYFILE option to migrate data to a different file in the same filegroup. If so, I would only do this when you don't have any other option to do it in a planned fashion, e.g. you already ran out of disk space and the database needs to be operational NOW(regardless how bad the performance might be).
  4. Trev256b Member

    Hi Frank, Luis - no I am looking for the script to create a new file on another drive whilst keeping the database online. I saw a DBA create such a file so the database started using '.mdf2' and '.mdf1' was no longer used and the disk space where '.mdf1' resided wasn't filled up.
  5. AJITH123 Member

    You can add one more file in the same filegrup, if you do not want to create a new.
    

    USE [master]
    GO
    ALTER DATABASE [<DBName>]
    ADD FILE ( NAME = N'NEW_FILE'
    , FILENAME = N'<DifferentDrive>\NEW_FILE.ndf'
    , SIZE = 1048576KB
    , FILEGROWTH = 1048576KB )
    TO FILEGROUP [PRIMARY]
    GO
  6. Trev256b Member

    does this mean the database will only write to this new .ndf and no longer write to the old .mdf file?
  7. AJITH123 Member

    Yes, if there is no space in the drive to grow, then the SQL will write it to the new file.

Share This Page