Sql-server – Check connection details to SQL Server 2000/2005

connectionsmonitoringsql-server-2005

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.

Best Answer

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,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

    or

    select
P.spid
,   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).

Related Question