Tuesday, November 30, 2010

Finding execution plans of currently active session sqls

Here is the script to find execution plans of currently running sqls on server. It is in xml format.

SELECT sp.spid,sp.cpu,sp.cmd, qp.query_plan from sys.sysprocesses as sp inner join sys.dm_exec_requests as er ON sp.spid=er.session_id
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle)as qp
where sp.status not in ('sleeping','background') and sp.cmd not in ('DB MIRROR') order by cpu

Thursday, November 11, 2010

How to Move Sql Server 2005/2008 Mirrored Database datafile

In order to move datafiles of a mirrored database from one location to an other (requires server offline):

1-) Check datafile physical locations:

select * from master..sysaltfiles where filename like '%MYDB_Data%'

2-) Run similar alter database command to rename physical datafile names.

alter database MYDB MODIFY FILE (NAME=MYDB_Data01,FILENAME='D:\mypath\MYDB_Data01.ndf');

Following similar message will come up means change will apply when server is restarted

The file "MYDB_Data01" has been modified in the system catalog. The new path will be used the next time the database is started.

* Shutdown sql service
* copy physical files to new location
* rename old ones to some dummy name
* Start sql services

4-) If everything is fine, you can delete old files.

Note: You can not take mirrored database offline, that is why we shutdown sql services and copy datafiles while system is offline.