Changing Locations of DataFile

  • Hi All,

    As i see some of the servers are having databases online...

    Now this Database have data file & log file on the same drive.. moreover the tempdb database which is heavily used is also on the same drive...

    Now if i Change the Log File location of the database to other drive & also if i change the Tempdb log file location to other drive will it help improving performance???

    I have planned this but is scared if i go ahaed and change the location will it affect the database working???

    How do i do it.. if someone has done this please guide me

    ************************************
    Every Dog has a Tail !!!!! :-D

  • you need to answer as blow question

    what is your server h/w configuration?

    how many disk array controller?

    what is disk configuration for RAID 0, 5, or 10?

  • This is very easy and in fact I have a request for change for exactly this to complete this week in the organisation where I work. Follow these steps;

    • get the logical name and current path\filehame for each databases files that you wish to move. You may use this query

      select db_name(database_id), name, physical_name from sys.master_files

    • Create the statements that you will use to move the database files. Use this code as a template and fill in with the info from the query above (you need an alter statement for each database file)

      ALTER DATABASE mydb MODIFY FILE(name=logicalname,

      FILENAME='new path and drive\the originalfilename.extension')

    • Take a last check at your move statements and when happy execute them. The new paths will not take effect until the database next restarts.

      Note: Be extra careful here as sql server will accept whatever path\filename you type. If the path\filename doesn't exist when you start the database it will not come online!!

    • Take the database offline
    • copy not move the disk files to their new locations
    • bring the database online
    • once the database comes online remove the old disk files

    If for any reason the database does not come back online, don't panic. Check sys.master_files with the query above and verify the path\filename, if it's wrong change it then bring the database online.

    If all this seems too much just detach and then re attach if you find it easier 😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thanks Perry 🙂

    Can you give an example, rough pathfilename etc for this syntax query?

    ALTER DATABASE mydb MODIFY FILE(name=logicalname,

    FILENAME='new path and drive\the originalfilename.extension')

    Also will this approach help in improving perfromance?????????????

    ************************************
    Every Dog has a Tail !!!!! :-D

  • runal_jagtap (9/18/2012)


    Thanks Perry 🙂

    Can you give an example, rough pathfilename etc for this syntax query?

    ALTER DATABASE mydb MODIFY FILE(name=logicalname,

    FILENAME='new path and drive\the originalfilename.extension')

    Yes sure. Let's a ssume i have a database named BOB. The result of my first query above produced

    name physical_name

    Bob_data C:\Program Files\MSSQL.1\MSSQL\data\Bob.mdf

    Bob_Log C:\Program Files\MSSQL.1\MSSQL\data\Bob_log.ldf

    I want to move data files to "F:\MSSQL\Data" and logs to "G:\MSSQL\Logs", my statements would be

    ALTER DATABASE [bob] MODIFY FILE(name=Bob_data,

    FILENAME='F:\MSSQL\Data\Bob.mdf')

    ALTER DATABASE [bob] MODIFY FILE(name=Bob_log,

    FILENAME='G:\MSSQL\Logs\Bob_log.ldf')

    Note: moving system databases such as TEMPDB is a different process to user databases

    runal_jagtap (9/18/2012)


    Also will this approach help in improving perfromance?????????????

    This all depends on how your disk subsystem is made up. Do you have separate physical disks or just logical drives on the same disk set or SAN attached LUNs, etc?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Do you have separate physical disks or just logical drives on the same disk set or SAN attached LUNs, etc?

    if just different logical drive on the same disk set, it would't get much performance also disk IO will be increase.

    if separate physical disks on different array controller, it will get much performance also disk IO will be reduced.. and same thing SAN attached LUN configuration.

  • ananda.murugesan (9/18/2012)


    if separate physical disks on different array controller, it will get much performance also disk IO will be reduced.. and same thing SAN attached LUN configuration.

    Maybe, maybe not. It's not an automatic guarantee of improved performance. Depends what the database bottleneck is currently.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Yes i have benn provided a different physical drive...

    Well i came to know how to move the Datafile & Log files to different location....

    I am just concerned abt TempDB database as there are many stored procedure executing all time..

    WIll moving Tempdb datafile & log file help in improving performance?

    What are the steps for setting the tempdb datafile & log files to other locations???

    Currently the tempdb files are on the same drive, i want to move it to other phyiscal drive....

    ************************************
    Every Dog has a Tail !!!!! :-D

  • Execute the following script in Query Editor to move the tempdb files into a new location:

    -- Moving tempdb to a new location

    -- Checkout current location and logical names

    USE tempdb

    GO

    EXEC sp_helpfile

    GO

    -- Setup the new location

    USE master

    GO

    ALTER DATABASE tempdb

    MODIFY FILE (NAME = tempdev, FILENAME = 'E:\sql\db\tempdb\tempdb.mdf')

    GO

    ALTER DATABASE tempdb

    MODIFY FILE (NAME = templog, FILENAME = 'E:\sql\db\tempdb\tempdb.ldf')

    GO

    /*

    Messages:

    The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.

    The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

    */

    -- Reboot server

    -- After reboot, old tempdb files can be deleted

  • runal_jagtap (9/18/2012)


    What are the steps for setting the tempdb datafile & log files to other locations???

    The same as i detailed above 😉

    You just need to restart the SQL Server service to recreate the TempDb in the new locations, no need to reboot.

    Ensure that any new database file locations have sufficient ACLs applied to allow the database engine service account to access them.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Great thank you all... will implement it soon 🙂

    ************************************
    Every Dog has a Tail !!!!! :-D

  • great, just make sure to pay attention to what you're doing and it will be fine

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle (9/18/2012)


    great, just make sure to pay attention to what you're doing and it will be fine

    Perry Dont scare me, me doing it for first time:-D

    Yes i will note down each step i do:cool:

    ************************************
    Every Dog has a Tail !!!!! :-D

  • when are you doing this?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle (9/18/2012)


    when are you doing this?

    I am Gonna Implemenet this probably by next week, just awaiting manager's go ahead:-)

    ************************************
    Every Dog has a Tail !!!!! :-D

Viewing 15 posts - 1 through 15 (of 69 total)

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