Optimizing SQL server Performance, by moving databases to different Drive...

It somewhat depends on what version of SQL you're using, but basically the trick in terms of user login is as stated - the user has to have the sysadmin role for the database.  In earlier versions of SQL, for example, the sa user was always created with this level of access. In SQL 2008, this user is created but disabled, so you have to create a special user during the install.  I hope if this was done then you have the logon information for this user.

The only other trick is to make sure that the database is not being used by anyone when you do this. Sounds self-evident, but it is important as you'll get an error message if anyone is connected in any way to that database when you try to detach it.

Hmmm, it is SQL 2008 and i am having a hard time tracking down those credentials (sysadmin)...way to reset this?

From the tone and phrasing of your question, I'm going to assume you're an accidental DBA.

There are two ways to login to SQL Server, SQL or Windows Authentication. You can set up a server to do only Windows Authentication, but that is rare. So if you have the sa password, login using sa and the password. In the management studio there will be a Security --> Logins folder. (Hit <F7> to see the Object Explorer Details window.) If you see your windows login in there right-click --> Properties. Select the Roles option and you will see System Administrator. Give yourself those permissions.

As far as moving the database I suggest that you do it via a query window. Just click on the New Query button.

Several steps to do it:
To find out who is using the DB:

exec sp_who2

Select allOpen in new window

then hit <F5> or click Execute

If you see anyone in your DB use the Kill comm

kill ## --<-- this is the SPID number in the firt column

Select allOpen in new window

then hit <F5> or click Execute. You can issue multiple kill commands at the same time, just do one per line.

After no one is using your DB issue the following command

exec sp_detach_db '<MyDBName>', True

Select allOpen in new window

then hit <F5> or click Execute. Move the files around as needed.

The next thing to

exec sp_attach_db @dbname = 'MyDBName', 
@filename1 = 'Y:\MyPath\MyDB.mdf', 
@filename2 = X:\MyPath\MyDB.ldf',
@filename3 = Z:\MyPath\MyDB.ldf', 

Select allOpen in new window

then hit <F5> or click Execute. Typically there will be only a single mdf and ldf file.

You should be ready to go.

Considerations for file locations and raiding, etc.

The way SQL Server (and most DB Server apps work) is that they write the data into the transaction log (ldf file), then it writes to the data file (mdf) and then goes back and tells the ldf that the data is committed.

So if you throw a new single LUN at the server split into an D: (data) and L: (logs) drives and move the files there, you don't really gain anything. The disk heads now have to move to the L: portion to write the log, move to the D: portion to write the data, then back to the L: portion to checkpoint.

Now if you add two LUNS and your SAN allows you to control what disks are in each LUN you may see improvement in performance.

My life as it is now: The 2 drive shelf units present the 15 disk, each,  to the SAN unit as a giant multi Terabyte Aggregate. So you carve out 100 GB to give to ServerA as the D: drive. You carve out 40GB to give as a L: drive. The Temp DB is on the T: drive, the master DB is on the M: drive, etc. Because we have no control of the Aggregate's disk allocation, it makes not one whit of difference to split the data and log files and the various databases on individual drives. Because we don't control the individual drives and say these three disks are going to be nothing but ldf files, these 7 are mdf files, etc.

(Sub-note: I am so glad the former DBA developers who set this crap up are no longer longer with the company. I won't have the opportunity to tell them what I think and use a blunt instrument to instruct them on the error of their ways.)

Hi, JamesAnctil - sorry for the delay in answering your followup question.  I know there's a way to get around this, because I experienced it some time back. The problem is, I can't find the solution in my records.  I'm going to take another look and see if I can locate it, but I just wanted to let you know that I'm not ignoring your question.

I found it!  Here are the steps to set (change) the sa password and enable the account:

1. Download the pstools from the Microsoft website and extract it to a folder on the server.
3. Open a command prompt and navigate to the extracted folder.
4. Run the following command:
                psexec -i -s cmd
    This opens a new command prompt window.
5. Run the whoami command to confirm that the command prompt is running under the ntauthority\system account.
6. Run the following commands in sequence:
    sqlcmd -S[SERVER] -E  
    (NOTE: Substitute your server name for SERVER, without the brackets.)
    alter sa login with password=’password’
    go

This sets the sa account password to password.

7. Run the following commands in sequence:
    sqlcmd -S[SERVER] -E
    alter login sa enable
    go

Then exit the command prompt and you should be able to log in to the SQL management studio using the sa credentials, which has full sysadmin rights.  Of course you want to immediately set the sa password to something else and make sure you record that somewhere safe so that you can utilitize this login again if necessary.

You guys rock, i was able to get my SA credentials...you would ROFL if knew how simple and unsecure they are...


anyway i will be moving some DB's tonight with the help of your guys tips...i will follow up when i have completed the procedure.

The root of my questions is because our client said that when he runs certain queries it ends up making another application (mas90) that uses a database (might be same one) freeze for all users.

Oh! CRAP!!! :-(

<personal commentary  = on>

This part of the comment is only attributable to me and does not reflect on EE, any other entity and is strictly my personal opinion.

Any of the application suites that M$ bought, bribed, or stole from other companies and is in the Dynamics suites are, and need to be, treated as special entities.

Sage, CRM, Great Plains, etc. are screwed up beyond all recognition (FUBAR) by the M$ attempt to integrate thirty separate animals, that developed individually into a single, genetically stable animal. M$ Dynamics makes the platypus seem like it a well thought out, sensibly derived, genetically developed animal.

Ok, I'll step off my soap box now.

<personal commentary  = off>

Ok, if he is running queries against the Sage DB, he may want to look at using the (NoLock) option on the queries.

Hmm interesting, what is this NoLock option?

Also i am getting an error when trying to set my database online after moving the log file...

here is what i do

Login with SA Credentials

queries:

1. ALTER DATABASE Mydatabase
MODIFY FILE (NAME = 'MydatabaseLog1', FILENAME = 'D:\MydatabaseLog1.ldf')

2.USE master
Go
ALTER DATABASE Mydatabase
SET offline

3. Move location of ldf file to new location (D:\)

4.USE master
Go
ALTER DATABASE Mydatabase
SET online

when i do 4, i get this error

File activation failure. The physical file name "E:\test_log" may be incorrect.?


What gives? the permissions on the data folder and new log file location has everyone set with full control...

Hi,

From http://msdn.microsoft.com/en-us/library/ms345483.aspx

Run this query to check that the alter database did actually set the new filename correctly

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');

It appears that your change to d:\MyDatabaeLog1.ldf didn't stick if the error message is that e:\test_log may be incorrect.

Just a question: Are you renaming the datafile as well as moving it?

Regards
  David

I would trust the the sp_detach_db and the sp_attach_db much more. Those are "ancient" but generally work well.

I am moving just the log files...

Jimpen, what would the process look like if i were to use sp_detach_db and sp_attach_db?

drodd, when i run the query you posted, (replacing Database_name with test)(test is the name of the database im moving for now) it tells me the query was run successfully but nothing shows up in the window...

is there another query that will output the location of a specified dbase log file?

ok the command you gave me worked drodd to show the location of the database and log file...however

It appears as though despite my "file activation failure" the log file location path appears to have been changed to the new location i specified...

should i just ignore the file activation error if this is the case?

can i test the log file somehow?

Anyone?

Hi,

Given that your server is not responding predictably, when is the next time you could schedule a reboot, and try again.

This is starting to feel like need hands-on to see what is happening. Can you hire a consultant?

Regards
  David

I did hire a consultant...experts exchange ;-)

it seems when the admin moved the thread into a different area people have stopped responding...

I Feel my last few questions would be easy enough for someone with intermediate knowledge of SQLserver..

Ok...guess ill just have to repost

Fair enough.

Thank you all for your responses.

I will share my results soon ;-)