I want to migrate some databases hosted in SQL Server 2000/2005 to SQL Server 2014.

I don't have direct access to the databases and I want to know all applications connecting to it and connection details. Is there any best way to get this information? I tried using DB profiling but its not allowed as its taking up lot of space on hosted DB server which causing disruptions.

Any better way like a SQL statement or remote connection monitoring will be helpful.

Thanks in advance for all your help.

1 Answer 1


I am not sure regarding SQL Server 2000 since I did not work on it before, but if you want to check active transactions on SQL 2005, you can execute the following command SELECT * FROM sys.dm_tran_session_transactions. to check active sessions execute sp_who. Useful commands:

  • check active queries

    SELECT sqltext.TEXT,
    FROM sys.dm_exec_requests req
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext


    ,   right(convert(varchar, 
            dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'), 
            121), 12) as 'batch_duration'
    ,   P.program_name
    ,   P.hostname
    ,   P.loginame
    from master.dbo.sysprocesses P
    where P.spid > 50
    and      P.status not in ('background', 'sleeping')
    and      P.cmd not in ('AWAITING COMMAND'
                    ,'MIRROR HANDLER'
                    ,'LAZY WRITER'
                    ,'CHECKPOINT SLEEP'
                    ,'RA MANAGER')
    order by batch_duration desc
  • to check free disk space: https://www.mssqltips.com/sqlservertip/1706/determine-free-disk-space-in-sql-server-with-tsql-code/

  • to check Server status I recommend to use activity monitor (you can use resource monitor as well).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.