Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Changing Locations of DataFile Expand / Collapse
Author
Message
Posted Tuesday, September 18, 2012 1:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, 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 !!!!!
Post #1360586
Posted Tuesday, September 18, 2012 1:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 1,015, Visits: 2,841
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?
Post #1360589
Posted Tuesday, September 18, 2012 1:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:26 PM
Points: 5,961, Visits: 12,847
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"
Post #1360594
Posted Tuesday, September 18, 2012 1:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, 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 !!!!!
Post #1360601
Posted Tuesday, September 18, 2012 2:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:26 PM
Points: 5,961, Visits: 12,847
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"
Post #1360608
Posted Tuesday, September 18, 2012 2:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 1,015, Visits: 2,841
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.

Post #1360628
Posted Tuesday, September 18, 2012 2:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 41,531, Visits: 34,448
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 2008, MVP
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

Post #1360632
Posted Tuesday, September 18, 2012 3:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, 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 !!!!!
Post #1360634
Posted Tuesday, September 18, 2012 3:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 1,015, Visits: 2,841
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


Post #1360639
Posted Tuesday, September 18, 2012 3:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:26 PM
Points: 5,961, Visits: 12,847
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"
Post #1360642
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse