InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 12:09:36 GMT20http://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspxhi Perry,No its the Development Server, i have done with moving of dsatafiles to other location only for One DB..In sometime i am going to do it for allFri, 30 Nov 2012 05:48:06 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]runal_jagtap (11/29/2012)[/b][hr]Good Idea if i have one or two DB's..But there are 170 + Db's :w00t:[/quote]is that your Production server ?Fri, 30 Nov 2012 00:44:57 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]Paul Clark-418949 (11/29/2012)[/b][hr]Taking the Database Offline only makes the Database inaccessible. SQL Server will still have a lock on the files[/quote]No it doesn't! This is an action I have performed more times than I care to remember, offlining the database will release any hold the engine had on the files, you may then copy move or even delete them!Thu, 29 Nov 2012 07:06:41 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspxthanks Perry, great paul :-) It workedThu, 29 Nov 2012 06:48:18 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspxRunal,See my post, above sanket's.The document I provided you does say you need to stop the SQL Server service before moving the physical files.Taking the Database Offline only makes the Database inaccessible. SQL Server will still have a lock on the physical file.So... 1.) Stop the SQL Server Service.2.) Move the physical files.3.) Start the SQL Server Service.RegardsPaulThu, 29 Nov 2012 06:43:51 GMTPaul Clark-418949http://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]runal_jagtap (11/29/2012)[/b][hr]But error - Access Denied, File may be currently in use :w00t:i have admin rights on the drives & folders.. & nothing is open still its not allowing me to copy the files :w00t:[/quote]Verify the ACLs on the folder, being an admin on the server does not gurantee that you can access the files if the ACLs have been changed. Right click the folder and select properties, then click the security tab.[quote][b]Paul Clark-418949 (11/29/2012)[/b][hr]Taking the Database Offline isn't good enough, [/quote]Taking the database offline will allow you to copy, move or even delete the database files, it's my guess there are ACLs stopping the user copying the file.Thu, 29 Nov 2012 06:41:10 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspxGood Idea if i have one or two DB's..But there are 170 + Db's :w00t:Thu, 29 Nov 2012 05:54:46 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]runal_jagtap (11/29/2012)[/b][hr]I did, Execute the Alter Datafile queryi got outut - the files are moved . next time when DB starts..Fine enough..then i took the DB to OFfline state..Now i am trying to copy the Old datafiles to the new location which i have set...But error - Access Denied, File may be currently in use :w00t:i have admin rights on the drives & folders.. & nothing is open still its not allowing me to copy the files :w00t:[/quote]if your database is still accessible, then you have one more option to move your database files,1)take the backup of the DB2)Restore on the same server with different name and log file on different drive than data file with norecovery .3)make sure that no user is connected to DB , and then take log backup and append it on newly created DB with recovery4)Change old DB name , change the new DB name to original DB name .I hope thats not much confusing,it will also minimize your downtime Thu, 29 Nov 2012 04:57:48 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]runal_jagtap (11/29/2012)[/b][hr]I did, Execute the Alter Datafile queryi got outut - the files are moved . next time when DB starts..Fine enough..then i took the DB to OFfline state..Now i am trying to copy the Old datafiles to the new location which i have set...But error - Access Denied, File may be currently in use :w00t:i have admin rights on the drives & folders.. & nothing is open still its not allowing me to copy the files :w00t:[/quote]Hiya,Taking the Database Offline isn't good enough, this stops the database from being accessed but SQL Server is still accessing the file. You need to stop the SQL Server Service then move the files then start the SQL Server service again.Thu, 29 Nov 2012 04:09:24 GMTPaul Clark-418949http://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspxI did, Execute the Alter Datafile queryi got outut - the files are moved . next time when DB starts..Fine enough..then i took the DB to OFfline state..Now i am trying to copy the Old datafiles to the new location which i have set...But error - Access Denied, File may be currently in use :w00t:i have admin rights on the drives & folders.. & nothing is open still its not allowing me to copy the files :w00t:Thu, 29 Nov 2012 03:40:56 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]Perry Whittle (10/12/2012)[/b][hr][quote][b]Paul Clark-418949 (10/12/2012)[/b][hr]:-PFor most of our VM's it is just 1 aggregate as you are correct NetApp best practice....But I wanted the separate RAID Groups for the SQL Volumes (I know WAFL shouldn't need you to do this but we ran some tests in our test environment and found slightly better performance....)When we 1st set up our virtual environment we were using iSCSI LUNS , but as the technology got better we migrated to FC vmfs volumes.This was also when I started virtualising all our SQL Servers, which originally were physical clusters using iSCSI LUNS using SnapDrive with SnapManager for SQL, very cool technology for physical SQL Servers, but very big storage cost, size wise as obviously the volume a LUN sits in is almost double the size of a vmfs volume, bigger if you have a high snapshot reserve.(Didn't explain that very well but you no doubt get the idea)We almost doubled our storage capacity over night going from iSCSI to FC, and using vmfs rather than LUNS. Didn't last long though. It's always a struggle trying to get more storage. People seem to think it's unlimited. Yes of course you can have that big beefy server with 20 Hex Core CPU's and God knows how much Memory, I'll just run it on fresh air shall I. lol :w00t: :-D [/quote]I know what you mean, users think that not only is the storage unlimited but so are the host resources.You're not using Raw device mappings for any of your SQL Servers then?[/quote]No we're not, all our VMs use Virtual Disks from NFS Stores on the ESXi5 hosts.All we've done is that the SQL NFS Stores are on separate Aggregates. We are in a fairy good position where we can create big enough Aggregates to have enough spindles for the performance gain. If we didn't I would have left WAFL to deal with it and just had the 1 Aggregate for all so that there were plenty of spindles for the performance. With a schenario where I criss-crossed the Virtual Disks so in our environment where we have 2 clustered filer heads. I would have 2 Aggregates.Data Files - Aggr1 Filer01Log Files - Aggr2 Filer02Temp Data Files - Aggr2 Filer02Temp Log Files - Aggr1 Filer01The above is actually how we have our test environment SQL Servers set up in simple terms, as we don't have enough spindles to gain any performance.I shouldn't have started this should I....:w00t:I hate simplifications as if you over simplify it's wrong lol.... :hehe: You're totally right about the resources on the hosts as well. We tried to set up a VM Cost sheet here with a basic cost for a standard server:1CPU, 4GB RAM, 50GB DiskWith VRanger Weekly BackupWindows Server2008r2Base Cost £1500.00Then extra:+1CPU - £200.00+1GB RAM - £100.00+10GB Disk -£100.00So every time a new server is authorised a cost for the VM goes against the Project. When the time then comes around where we need an extra host or more disk shelves we should in theory have the money that was signed off as part of all the projects......In theory this seemed like a good idea, in practice the money appears to be virtual as well. :w00t:Fri, 12 Oct 2012 04:38:41 GMTPaul Clark-418949http://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]Paul Clark-418949 (10/12/2012)[/b][hr]:-PFor most of our VM's it is just 1 aggregate as you are correct NetApp best practice....But I wanted the separate RAID Groups for the SQL Volumes (I know WAFL shouldn't need you to do this but we ran some tests in our test environment and found slightly better performance....)When we 1st set up our virtual environment we were using iSCSI LUNS , but as the technology got better we migrated to FC vmfs volumes.This was also when I started virtualising all our SQL Servers, which originally were physical clusters using iSCSI LUNS using SnapDrive with SnapManager for SQL, very cool technology for physical SQL Servers, but very big storage cost, size wise as obviously the volume a LUN sits in is almost double the size of a vmfs volume, bigger if you have a high snapshot reserve.(Didn't explain that very well but you no doubt get the idea)We almost doubled our storage capacity over night going from iSCSI to FC, and using vmfs rather than LUNS. Didn't last long though. It's always a struggle trying to get more storage. People seem to think it's unlimited. Yes of course you can have that big beefy server with 20 Hex Core CPU's and God knows how much Memory, I'll just run it on fresh air shall I. lol :w00t: :-D [/quote]I know what you mean, users think that not only is the storage unlimited but so are the host resources.You're not using Raw device mappings for any of your SQL Servers then?Fri, 12 Oct 2012 03:37:30 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]Perry Whittle (10/12/2012)[/b][hr][quote][b]Paul Clark-418949 (10/12/2012)[/b][hr][quote][b]Perry Whittle (10/11/2012)[/b][hr][quote][b]Paul Clark-418949 (10/11/2012)[/b][hr]C: Drive - Operating System - (Filer01 - Volume 1)D: Drive - SQL Installation - (Filer02 - Volume 2)F: Drive - Page File - (Filer01 - Volume 3)G: Drive - System Database Data Files - (Filer01 - SQLVolume 1)H: Drive - System Database Log Files - (Filer02 - SQLVolume 2)I: Drive - User Database Data Files - (Filer02 - SQLVolume 3)J: Drive - User Database Log Files - (Filer01 - SQL Volume 4)K: Drive - Temp Database Data Files - (Filer01 - SQL Volume 5)L: Drive - Temp Database Log Files - (Filer02 - SQL Volume 6)[/quote]Problem is all these volumes are carved out of 1 huge aggregate on each filer are they not?[/quote]I didn't want to confuse the issue too much, but no there are multiple Aggregates that all the vmfs Volumes are spread over. When building a Virtual SQL Server, I try to make sure the Virtual disks are spread across volumes on separate Aggregates across separate Filer headsOur setup is as you can imagine quite alot more complicated than the above as our total server farm is almost 99% Virtualised on ESXi5 with Clustered NetApp Backend Storage. I was trying to simplify things a bit, showing a single server with regards to better performance with physical disks(Or separate RAID Groups) which will give you better performance. Obviously the absolute best performance disk IO wise would be to have a separate physical disk(or RAID Group) for each physical SQL File, but you are going to have to make a trade off somewhere.[/quote]Ah you've deviated away from the Netapp recommendation then ;-)The last time i worked with a Netapp solution I seem to remember they recommended you create one aggregate and allow Netapps WAFL to handle the storage access.How do you present your LUNs to the VMs are they via iSCSI or FC?[/quote]:-PFor most of our VM's it is just 1 aggregate as you are correct NetApp best practice....But I wanted the separate RAID Groups for the SQL Volumes (I know WAFL shouldn't need you to do this but we ran some tests in our test environment and found slightly better performance....)When we 1st set up our virtual environment we were using iSCSI LUNS , but as the technology got better we migrated to FC vmfs volumes.This was also when I started virtualising all our SQL Servers, which originally were physical clusters using iSCSI LUNS using SnapDrive with SnapManager for SQL, very cool technology for physical SQL Servers, but very big storage cost, size wise as obviously the volume a LUN sits in is almost double the size of a vmfs volume, bigger if you have a high snapshot reserve.(Didn't explain that very well but you no doubt get the idea)We almost doubled our storage capacity over night going from iSCSI to FC, and using vmfs rather than LUNS. Didn't last long though. It's always a struggle trying to get more storage. People seem to think it's unlimited. Yes of course you can have that big beefy server with 20 Hex Core CPU's and God knows how much Memory, I'll just run it on fresh air shall I. lol :w00t: :-D Fri, 12 Oct 2012 02:51:56 GMTPaul Clark-418949http://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]Paul Clark-418949 (10/12/2012)[/b][hr][quote][b]Perry Whittle (10/11/2012)[/b][hr][quote][b]Paul Clark-418949 (10/11/2012)[/b][hr]C: Drive - Operating System - (Filer01 - Volume 1)D: Drive - SQL Installation - (Filer02 - Volume 2)F: Drive - Page File - (Filer01 - Volume 3)G: Drive - System Database Data Files - (Filer01 - SQLVolume 1)H: Drive - System Database Log Files - (Filer02 - SQLVolume 2)I: Drive - User Database Data Files - (Filer02 - SQLVolume 3)J: Drive - User Database Log Files - (Filer01 - SQL Volume 4)K: Drive - Temp Database Data Files - (Filer01 - SQL Volume 5)L: Drive - Temp Database Log Files - (Filer02 - SQL Volume 6)[/quote]Problem is all these volumes are carved out of 1 huge aggregate on each filer are they not?[/quote]I didn't want to confuse the issue too much, but no there are multiple Aggregates that all the vmfs Volumes are spread over. When building a Virtual SQL Server, I try to make sure the Virtual disks are spread across volumes on seperate Aggregates across seperate Filer headsOur setup is as you can imagine quite alot more complicated than the above as our total server farm is almost 99% Virtualised on ESXi5 with Clustered NetApp Backend Storage. I was trying to simplify things a bit, showing a single server with regards to better performance with physical disks(Or seperate RAID Groups) which will give you better performance. Obviously the absolute best performance disk IO wise would be to have a seperate physical disk(or RAID Group) for each physical SQL File, but you are going to have to make a trade off somewhere.[/quote]Ah you've deviated away from the Netapp recommendation then ;-)The last time i worked with a Netapp solution I seem to remember they recommended you create one aggregate and allow Netapps WAFL to handle the storage access.How do you present your LUNs to the VMs are they via iSCSI or FC?Fri, 12 Oct 2012 02:22:06 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]Perry Whittle (10/11/2012)[/b][hr][quote][b]runal_jagtap (10/11/2012)[/b][hr]Hi Paul,thanks for providing the answer :)Currently we have 5 servers...For example..1 server have 20 databases almost 20GB each.. now the tempdb files & user datafiles & log files is on same server.....same is for other servers...Now if i ask the team, they will provide me additional servers....:-Dshould i ask for it & implement what you have written :-)[/quote]What works for one won't necessarily work for another. Are you using NetApp Filer storage technology or some other SAN device??You need to perform your own tests on your own systems and provide the spec that works best[/quote]Hi Runal,I agree with Perry, what I have given you above is a very simplified version of best practice on our SQL Servers. But it doesn't give an overall view of our total infrastructure.In simple terms it's easier to talk about physical servers. Where good practice would be to have a server with multiple RAID groups. If you substitute the volumes in the description of one of my servers for seperate RAID Groups you will have a server with good Disk IO performance, and this would mean that Disk IO is less likely to be a bottle neck on the Server.Perry's question about what Storage Appliance you are running is also very pertinent. From what you have asked I'm assuming maybe wrongly that your Servers are Virtual?1.) If so what Virtualisation Technology are you using? VMWare, Microsoft Hyper-V2.) What Storage Appliance are you using? NetApp or some other Vendor3.) How is your Storage Appliance connected to your Virtual Environment? iSCSI, NFS....4.) How is your storage carved up? IE how many Aggregates, how many volumnes on each Aggregate, how many virtual machines on each volume5.) Is your Storage Appliance committed to the Virtual Environment or is it also used for Shares etc?These are just some of the questions you need to ask. I think it may be wise to get a good handle on exactly what you are trying to acheive. I think you need to ask yourself why do you want to move the files?Is it because you have seen a bottleneck in performance caused by high Disk IO?If this is the case then just moving the files to somewhere else on the same RAID group won't really help...Here is a link to a Best Practice Article on TechNet that may give you a better understanding of disk configurations for SQL Server.[url=http://msdn.microsoft.com/en-us/library/cc966412.aspx][/url]You also need to answer the 5 questions above, and do a bit of research on the various aspects of your infrastructure.Fri, 12 Oct 2012 01:39:26 GMTPaul Clark-418949http://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]Perry Whittle (10/11/2012)[/b][hr][quote][b]Paul Clark-418949 (10/11/2012)[/b][hr]C: Drive - Operating System - (Filer01 - Volume 1)D: Drive - SQL Installation - (Filer02 - Volume 2)F: Drive - Page File - (Filer01 - Volume 3)G: Drive - System Database Data Files - (Filer01 - SQLVolume 1)H: Drive - System Database Log Files - (Filer02 - SQLVolume 2)I: Drive - User Database Data Files - (Filer02 - SQLVolume 3)J: Drive - User Database Log Files - (Filer01 - SQL Volume 4)K: Drive - Temp Database Data Files - (Filer01 - SQL Volume 5)L: Drive - Temp Database Log Files - (Filer02 - SQL Volume 6)[/quote]Problem is all these volumes are carved out of 1 huge aggregate on each filer are they not?[/quote]I didn't want to confuse the issue too much, but no there are multiple Aggregates that all the vmfs Volumes are spread over. When building a Virtual SQL Server, I try to make sure the Virtual disks are spread across volumes on seperate Aggregates across seperate Filer headsOur setup is as you can imagine quite alot more complicated than the above as our total server farm is almost 99% Virtualised on ESXi5 with Clustered NetApp Backend Storage. I was trying to simplify things a bit, showing a single server with regards to better performance with physical disks(Or seperate RAID Groups) which will give you better performance. Obviously the absolute best performance disk IO wise would be to have a seperate physical disk(or RAID Group) for each physical SQL File, but you are going to have to make a trade off somewhere.Fri, 12 Oct 2012 01:21:51 GMTPaul Clark-418949http://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]runal_jagtap (10/11/2012)[/b][hr]Hi Paul,thanks for providing the answer :)Currently we have 5 servers...For example..1 server have 20 databases almost 20GB each.. now the tempdb files & user datafiles & log files is on same server.....same is for other servers...Now if i ask the team, they will provide me additional servers....:-Dshould i ask for it & implement what you have written :-)[/quote]What works for one won't necessarily work for another. Are you using NetApp Filer storage technology or some other SAN device??You need to perform your own tests on your own systems and provide the spec that works bestThu, 11 Oct 2012 23:43:40 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]Paul Clark-418949 (10/11/2012)[/b][hr]C: Drive - Operating System - (Filer01 - Volume 1)D: Drive - SQL Installation - (Filer02 - Volume 2)F: Drive - Page File - (Filer01 - Volume 3)G: Drive - System Database Data Files - (Filer01 - SQLVolume 1)H: Drive - System Database Log Files - (Filer02 - SQLVolume 2)I: Drive - User Database Data Files - (Filer02 - SQLVolume 3)J: Drive - User Database Log Files - (Filer01 - SQL Volume 4)K: Drive - Temp Database Data Files - (Filer01 - SQL Volume 5)L: Drive - Temp Database Log Files - (Filer02 - SQL Volume 6)[/quote]Problem is all these volumes are carved out of 1 huge aggregate on each filer are they not?Thu, 11 Oct 2012 23:39:06 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspxHi Paul,thanks for providing the answer :)Currently we have 5 servers...For example..1 server have 20 databases almost 20GB each.. now the tempdb files & user datafiles & log files is on same server.....same is for other servers...Now if i ask the team, they will provide me additional servers....:-Dshould i ask for it & implement what you have written :-)Thu, 11 Oct 2012 22:29:01 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]runal_jagtap (10/11/2012)[/b][hr]Perry where r u???? :(Say na what will happen if we do implement this???What is the use of doing this???????i wanna send email to my seniors so that they can give me a go-ahead for the same :w00t:[/quote]Hi Runal,Do you currently have all your files on 1 physical disk on the server?The best setup for SQL is to have multiple disk drives. If this is a virtual server then each virtual disk needs to be on a different volume on your back end storage, if possible on different filer heads as well.We have our Servers set up in the following way(They are Virtual so I know you may not have enough physical disks on your server to accomplish this):C: Drive - Operating System - (Filer01 - Volume 1)D: Drive - SQL Installation - (Filer02 - Volume 2)F: Drive - Page File - (Filer01 - Volume 3)G: Drive - System Database Data Files - (Filer01 - SQLVolume 1)H: Drive - System Database Log Files - (Filer02 - SQLVolume 2)I: Drive - User Database Data Files - (Filer02 - SQLVolume 3)J: Drive - User Database Log Files - (Filer01 - SQL Volume 4)K: Drive - Temp Database Data Files - (Filer01 - SQL Volume 5)L: Drive - Temp Database Log Files - (Filer02 - SQL Volume 6)This gives us the best possible disk I/O Performance for the Infrastructure we have....I am in the enviable position of also being a Storage and Virtual Infrastructure Administrator at our company, although my primary role is DBA.:-DThe above drives must be seperate and not partitions of a single physical disk to get the best possible performance.The reasons for splitting out the files like this is to give you the best possible Disk I/O for performance.You can simplfy this if you don't have enough disks, but try to keep the Temp DB files away from the User Database files to give better I/O Performance, if possible.If you only have 1 Physical Disk on your server then moving the files will not really give you any performance gain, you need to be moving them onto seperate disks.I hope the above is good enough to answer questions your manager may ask as to why you want to move these files.;-)PaulThu, 11 Oct 2012 07:52:37 GMTPaul Clark-418949http://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspxHithe links above will do you just fine, post back if you're still stuckThu, 11 Oct 2012 07:04:22 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspxPerry where r u???? :(Say na what will happen if we do implement this???What is the use of doing this???????i wanna send email to my seniors so that they can give me a go-ahead for the same :w00t:Thu, 11 Oct 2012 06:56:13 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspxThanks Paul ;)Perrry if you have documented it.. please attach na;-)Mon, 08 Oct 2012 23:50:15 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]Perry Whittle (10/8/2012)[/b][hr]No work as I have it documented myself already ;-)[/quote]Fairy Snuff;-)Mon, 08 Oct 2012 09:04:27 GMTPaul Clark-418949http://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspxNo work as I have it documented myself already ;-)Mon, 08 Oct 2012 08:33:06 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspxHi Perry,This might save some work:I have already documented the steps for this in my Blog.Let Runal know if the below links are OKMoving TempDB Files - Just look at the Tempdb section.[url=http://paulmjclark.blogspot.co.uk/2011/10/change-system-database-location-on-sql.html]http://paulmjclark.blogspot.co.uk/2011/10/change-system-database-location-on-sql.html[/url]Moving User Database Files:[url=http://paulmjclark.blogspot.co.uk/2012/10/moving-user-database-files.html]http://paulmjclark.blogspot.co.uk/2012/10/moving-user-database-files.html[/url]Hope this helps...Mon, 08 Oct 2012 06:54:45 GMTPaul Clark-418949http://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]Perry Whittle (10/8/2012)[/b][hr][quote][b]runal_jagtap (10/8/2012)[/b][hr]Hey Perry can you just attach step by step processs which u did... so that while doing it at my end i wont miss any step..[/quote]Yes I can, but are you planning to do just user databases or system databases too?If system, which ones?[/quote]Was planning to move Log files of User databases & only TempDB ... what say??? i am a fresher so not sure whether i am correct:-)Mon, 08 Oct 2012 06:22:17 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]runal_jagtap (10/8/2012)[/b][hr]Hey Perry can you just attach step by step processs which u did... so that while doing it at my end i wont miss any step..[/quote]Yes I can, but are you planning to do just user databases or system databases too?If system, which ones?Mon, 08 Oct 2012 06:18:33 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]Perry Whittle (10/8/2012)[/b][hr][quote][b]runal_jagtap (10/7/2012)[/b][hr]Hi Perry Whittle,Did you goyt any chance to perform this at your end???:-)I am still waiting for the approval :w00t:[/quote]Hi, yes my tasks around this have been completed. What files are you moving, just user DBs or system DBs too?If system DBs which ones?[/quote]As of now i am asked to do other tasks.. so this task is on hold...Hey Perry can you just attach step by step processs which u did... so that while doing it at my end i wont miss any step..Also do let me know the effect after u have done this.. Was it helpfull for you????because when i will do it .. i will be asked to justify why i am doing this :w00t:Mon, 08 Oct 2012 06:05:39 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]runal_jagtap (10/7/2012)[/b][hr]Hi Perry Whittle,Did you goyt any chance to perform this at your end???:-)I am still waiting for the approval :w00t:[/quote]Hi, yes my tasks around this have been completed. What files are you moving, just user DBs or system DBs too?If system DBs which ones?Mon, 08 Oct 2012 05:28:15 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]saidapurs (10/8/2012)[/b][hr]Hi Runal, What pretty has shared documents that i tested in Test environment and also work on production its working fine....Thanks & regardsSatish[/quote]Hi Satish can you share the document here????exactly how did you perform it???that would help me, if could share it....Mon, 08 Oct 2012 00:34:11 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspxHi Runal, What pretty has shared documents that i tested in Test environment and also work on production its working fine....Thanks & regardsSatishMon, 08 Oct 2012 00:29:00 GMTsaidapurshttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspxHi Perry Whittle,Did you goyt any chance to perform this at your end???:-)I am still waiting for the approval :w00t:Sun, 07 Oct 2012 23:49:36 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]Paul Clark-418949 (10/3/2012)[/b][hr]Can't a Detach/Attach also cause orphaned SQL users? .[/quote]Not if detaching and attaching to the same instance.Wed, 03 Oct 2012 07:04:10 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspxCan't a Detach/Attach also cause orphaned SQL users? Whereas using the alter table statements then offlining the DB and copying the files to the new location doesn't?And Wouldn't the Detach/Attach method also change your DB id in sys.databases, so you might lose backup history etc?..Wed, 03 Oct 2012 06:52:32 GMTPaul Clark-418949http://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]runal_jagtap (9/24/2012)[/b][hr]Hello Everyone :)Finally i have been assigned this task to do next month..but they are still not sure that 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??? :w00t:I have no answer as of now.. :([/quote]Using Performance Monitor, check your I/O latency on the drive you have the data, log and tempdb currently running on. If it is really bad, you should expect to see some improvements moving the files to seperate drives. Of course that depends on the drives you move them onto as well. Say the new drive is a 2 physical disk 7.2K raid 1 but the old drive was a 12 physical disk 15K raid 10, you might actually see worse performance after the move. :hehe:Mon, 24 Sep 2012 11:00:41 GMTscogebhttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]saidapurs (9/24/2012)[/b][hr]Hi Gila/Perry, Thanks for your response even i observed required less down time for this...Thanks & RegardsSatish[/quote]No problem, the main thing is to not panic. If you get the situation where the database does not start, immediately check sys.master_files and then marry this up to the files in the physical OS.Remember, the database engine will accept whatever you type into the alter database command, if that path\filename doesn't exist when the database attempts to restart, you'll get an error ;-)Mon, 24 Sep 2012 06:59:50 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspxHi Gila/Perry, Thanks for your response even i observed required less down time for this...Thanks & RegardsSatishMon, 24 Sep 2012 06:52:59 GMTsaidapurshttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]Perry Whittle (9/24/2012)[/b][hr][quote][b]runal_jagtap (9/24/2012)[/b][hr]then Move the files manually from C Drive to the new location & then start the DB..[/quote]I always copy the files then remove the originals when the database starts successfully[/quote]Ya Perry, me too agreee to this...:-)Mon, 24 Sep 2012 06:40:58 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1360586-1549-1.aspx[quote][b]runal_jagtap (9/24/2012)[/b][hr]then Move the files manually from C Drive to the new location & then start the DB..[/quote]I always copy the files then remove the originals when the database starts successfullyMon, 24 Sep 2012 06:31:33 GMTPerry Whittle