Identifying SQL Server Version

DECLARE @SQLRelease VARCHAR(15);

SET @SQLRelease = 'UNKNOWN'

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),1)='8'

SET @SQLRelease = 'SQLServer2000'

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),1)='9'

SET @SQLRelease = 'SQLServer2005'

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),2)='10'

SET @SQLRelease = 'SQLServer2008'

SELECT @SQLRelease as ReleaseLevel

, SERVERPROPERTY('ProductLevel') AS ProductLevel

, SERVERPROPERTY('Edition') AS Edition;

We are working upon a central Server Inventory solution and needed to capture for SQLServer details. Surprisingly, there are many script-lets out there to capture Product Version, but not much details around what it maps to (unless you want to keep a table running with you to interpret numbers again SQL Server Product version). Hence the quick script above which can tell you Product Version in more humanly manner, along with SP and Edition details.

Finding Transactions which are using Rollback Segment

SELECT r.name "RBS", s.sid, s.serial#, s.username "USER", t.status,
t.cr_get, t.phy_io, t.used_ublk,t.used_urec, t.noundo,
substr(s.program, 1, 78) "COMMAND"
FROM sys.v_$session s, sys.v_$transaction t, sys.v_$rollname r
WHERE t.addr = s.taddr
and t.xidusn = r.usn
ORDER BY t.cr_get, t.phy_io
/

 

Vikas Rajput (http://vikasrajput.wordpress.com)

Finding number of Sessions / Users accessing an Object

SELECT S.USERNAME,S.SID,S.SERIAL#,S.SQL_HASH_VALUE,A.OWNER,A.OBJECT,A.TYPE
FROM V$SESSION S, V$ACCESS A
WHERE S.SID = A.SID
AND A.OWNER = ‘&OWNER_NAME’
AND A.OBJECT = ‘&OBJECT_NAME’
/

 

Vikas Rajput (http://vikasrajput.wordpress.com)

Finding List of Active Sessions

select sid,status,to_char(logon_time,’dd/mm/yyyy:hh:mi’),osuser,terminal,username,
serial# from v$session
where  status=’ACTIVE’
/

 

Vikas Rajput (http://vikasrajput.wordpress.com)