Moving System databases and User databases

Question

Moving System databases and User databases

devanandiiway

SSCommitted

Points: 1652
More actions
March 1, 2012 at 8:12 am

#100459

Hi,
Am trying to move system database and the user database to another drive in my environment. My question is will it affect the views/stored procedure/function in the database. Kindly clarify my doubt.
Thanks in advance.
Thanks,
Devanand.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Devanand

No it won't - provided that the operations performed by your stored procedures are only on the database and not on any drives in the operating system.

John

devanandiiway SSCommitted Points: 1652 More actions · Answer 2

Thanks John 🙂

My another question is will it affect the system in anyway. Because am planning to move my production database.

Thanks,

Dev.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Yes, of course it will - it will mean the databases will be on a different drive from where they started.

Excuse the flippant reply... can you be more specific about what you mean by "affect the system"?

John

devanandiiway SSCommitted Points: 1652 More actions · Answer 4

John

I just want to know the Pros and cons in moving the database to other drive. Pros i can list down myself. Am really worried of Cons :w00t:

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

I can't really think of any cons, provided you're not moving to an inferior disk (smaller, slower or shared with another app). You'll want to use this opportunity to review your disk configuration: RAID levels? log files on separate disks? tempdb on its own disk?

John

devanandiiway SSCommitted Points: 1652 More actions · Answer 6

I have to move the temp db to separate drive. currently it is in C: drive. Also i have to move the log files to separate drive. In my environment data and log files are placed in the same drive.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 7

Assuming these are direct attached (local disk or SAN), then to SQL Server, a drive is a drive. There is no effect on SQL Server of two equivalent drives. If one is slower, then your system will be slower.

John is correct, there is no issue outside of the performance, but if you are moving drives, why? It is a good time to address the layout of your physical devices, and perhaps find a way to increase performance.

If you are trying to move to network drives, I would suggest you not do that.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

Steve Jones - SSC Editor (3/1/2012)
If you are trying to move to network drives, I would suggest you not do that.

Steve, I don't think that's even permitted, certainly not in SQL Server 7/2000.

John

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 9

I hesitate to post the kb, because I don't believe in it, but there is a trace flag that bypasses the network drive check.

I don't have a 2000 instance to check it on at the moment, but the kb says it works on 2000 and 7, although with mapped drives, not UNC paths.

I would not do this, especially for system databases (for stability) or user databases (for performance).

devanandiiway SSCommitted Points: 1652 More actions · Answer 10

John/Steve,

I used "alter database modify file" command to move the user database. When i tried to bring the db online it slaps me with the following error.

"During upgrade, database raised exception 945, severity 14, state 2, address 00F995C8. Use the exception number to determine the cause."

Also the database is in Recovery_Pending state.

Following are the errors encountered in the log file :

i. Login failed for user 'ITLINFOSYS\sudharshini_s'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

ii. Error: 18456, Severity: 14, State: 38

iii. Error: 928, Severity: 20, State: 1.

Kindly assist me.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 11

I would worry about corruption, but do any of these help?

http://blog.sqlauthority.com/2007/08/02/sql-server-fix-error-945-database-cannot-be-opened-due-to-inaccessible-files-or-insufficient-memory-or-disk-space-see-the-sql-server-error-log-for-details/

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/01/22/error-945-severity-14-state-2-database-mssqlsystemresource-cannot-be-opened.aspx

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 12

Also, for the failure to open the specific database, is the database you're moving the default database for the user you're connecting as? You need to change this to master, either by first logging on as a different user (do you have the sa password locked away anywhere?) or possibly by starting SQL Server in single user mode.

John

devanandiiway SSCommitted Points: 1652 More actions · Answer 13

Steve/John

Thanks so much for your assistance. The blunder i did is i gave the logical name of the ldf file wrongly and that's why it errored out. I again changed it to proper logical name and tried and then i was able to move the user database.

devanandiiway SSCommitted Points: 1652 More actions · Answer 14

Hi,

I would like to discuss about service broker under this topic. I moved all the system databases to another drive. When i checked the service broker it was not enabled. I tried to enable it through the command "ALTER DATABASE MSDB ENABLE_BROKER". It errored out with some msdb numbers are not in sync.

My question is should i have to disable the service broker before moving the msdb? I shall send the exact error in my next post.

Could you please assist me on this.

Thanks,

Dev.