SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Changing Locations of DataFile


Changing Locations of DataFile

Author
Message
OnlyOneRJ
OnlyOneRJ
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

Group: General Forum Members
Points: 348 Visits: 690
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
SQL Galaxy
SQL Galaxy
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2568 Visits: 3465
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?
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20378 Visits: 17244
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" ;-)
OnlyOneRJ
OnlyOneRJ
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

Group: General Forum Members
Points: 348 Visits: 690
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
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20378 Visits: 17244
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" ;-)
SQL Galaxy
SQL Galaxy
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2568 Visits: 3465
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89379 Visits: 45284
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


OnlyOneRJ
OnlyOneRJ
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

Group: General Forum Members
Points: 348 Visits: 690
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
SQL Galaxy
SQL Galaxy
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2568 Visits: 3465
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



Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20378 Visits: 17244
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" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search