SQL Server Performance

How do I Know ho is using my database?

Discussion in 'General DBA Questions' started by sunda, Oct 11, 2006.

  1. sunda New Member

    Hello!!!

    Does anyone knows what is the command to list the user´s currently logged on my db?

    For example:
    I have an network application with works with SQL Server (microsoft)
    In some moment I´d like to open a window and have a list of ho is logged on.

    I am not DBA (far from it) and i really need your help!!!!

    Thanks a lot.

  2. Roji. P. Thomas New Member

  3. sunda New Member

    unbeliveble!

    u are amazing! this site is alread in my favorits pages!!!

    thanks!
  4. babusamuel New Member

    Also you can use :


    Use master
    go
    select * from sysprocesses
    go









    Thanks & Regards,








    N.Babu

    Don't try to do different things; Do things differently.
  5. Madhivanan Moderator

  6. MichaelB Member

    I know a better one!<br /><br />I got this from SQL Server Mag by contributor Bill McEnvoy in the October 2006 (Reader to Reader column). I hope he doesnt mind me sharing. Look at the article for more detail. It even gives you the sql the user is running. very cool. Needs atleast SP3 for SQL2000<br /><br /><br />USE [master]<br />GO<br />/****** Object: StoredProcedure [dbo].[sp_Now] Script Date: 10/13/2006 13:01:36 ******/<br />SET ANSI_NULLS ON<br />GO<br />SET QUOTED_IDENTIFIER ON<br />GO<br />CREATE PROCEDURE [dbo].[sp_Now]<br />as<br />set nocount on<br />declare @handle binary(20), <br /> @spid smallint,<br /> @rowcnt smallint,<br /> @output varchar(500)<br /><br />declare ActiveSpids CURSOR FOR<br />select sql_handle, spid<br /> from sysprocesses <br /> where sql_handle &lt;&gt; 0x0000000000000000000000000000000000000000<br /> --and spid &lt;&gt; @@SPID<br />order by cpu desc<br /><br />OPEN ActiveSpids<br />FETCH NEXT FROM ActiveSpids<br />INTO @handle,<br /> @spid<br /><br /><br />set @rowcnt = @@CURSOR_ROWS<br /><br />print '===================='<br />print '= CURRENT ACTIVITY ='<br />print '===================='<br />print ' '<br />set @output = 'ACTIVE SPIDS: ' + convert(varchar(4),@rowcnt)<br />print @output<br /><br /><br />WHILE (@@FETCH_STATUS = 0)<br />BEGIN<br /> print ' '<br /> print ' '<br /> print 'O' + replicate('x',120) + 'O'<br /> print 'O' + replicate('x',120) + 'O'<br /> print ' '<br /> print ' '<br /> print ' '<br /><br />select 'loginame' = left(loginame, 30), <br /> 'hostname' = left(hostname,30),<br /> 'datagbase' = left(db_name(dbid),30),<br /> 'spid' = str(spid,4,0), <br /> 'block' = str(blocked,5,0), <br /> 'phys_io' = str(physical_io,8,0), <br /> 'cpu(mm<img src='/community/emoticons/emotion-7.gif' alt=':s' />s)' = str((cpu/1000/60),6) + ':' + case when left((str(((cpu/1000) % 60),2)),1) = ' ' then stuff(str(((cpu/1000) % 60),2),1,1,'0') else str(((cpu/1000) % 60),2) END ,<br /> 'mem(MB)' = str((convert(float,memusage) * 8192.0 / 1024.0 / 1024.0),8,2),<br /> 'program_name' = left(program_name,50), <br /> 'command' = cmd,<br /> 'lastwaittype' = left(lastwaittype,15),<br /> 'login_time' = convert(char(19),login_time,120), <br /> 'last_batch' = convert(char(19),last_batch,120), <br /> 'status' = left(status, 10),<br /> 'nt_username' = left(nt_username,20)<br /> from master..sysprocesses<br /> where spid = @spid<br /> print ' '<br /> print ' '<br /> <br /> -- Dump the inputbuffer to get an idea of what the spid is doing<br /> dbcc inputbuffer(@spid)<br /> print ' '<br /> print ' '<br /><br /> -- Use the built-in function to show the exact SQL that the spid is running<br /> select * from ::fn_get_sql(@handle)<br /> <br /> FETCH NEXT FROM ActiveSpids<br /> INTO @handle,<br /> @spid<br />END<br />close ActiveSpids<br />deallocate ActiveSpids<br /><br /><br />Michael B<br />Sr. DBA<br /><br />"The fear of the Lord is the beginning of knowledge,<br />but fools despise wisdom and instruction." Proverbs 1:7

Share This Page