September 18, 2012 at 1:07 am
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
September 18, 2012 at 1:15 am
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?
September 18, 2012 at 1:28 am
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;
select db_name(database_id), name, physical_name from sys.master_files
ALTER DATABASE mydb MODIFY FILE(name=logicalname,
FILENAME='new path and drive\the originalfilename.extension')
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!!
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" 😉
September 18, 2012 at 1:48 am
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
September 18, 2012 at 2:06 am
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" 😉
September 18, 2012 at 2:48 am
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.
September 18, 2012 at 2:59 am
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
September 18, 2012 at 3:10 am
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
September 18, 2012 at 3:19 am
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
September 18, 2012 at 3:28 am
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" 😉
September 18, 2012 at 3:30 am
Great thank you all... will implement it soon 🙂
************************************
Every Dog has a Tail !!!!! :-D
September 18, 2012 at 4:20 am
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" 😉
September 18, 2012 at 4:22 am
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
September 18, 2012 at 5:18 am
when are you doing this?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 18, 2012 at 5:35 am
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