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

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.

3-)
* 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.