Database Usage

Hello everyone,

I got a requirement to track all the information like the users connecting, queires executing by them and time, etc. I think we can use profiler for this but I want to track these details for few months, hence profiler will not be a good option I think.
Also I looked at SQL Server auditing and I felt it will not fit my requirement as I want to capture the details at server level.

Anyone please suggest me (any script/dmv/etc) which can be used to capture these details.

Thanks a lot in advance.

-Shyam

Any script available to store the information in a user table from sysprocesses view?

Code:

SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
WHERE STATUS !='SLEEPING'
ORDER BY CPU DESC

Create the same table def with Id column Primary key because month of data will be huge and then use

Code:

INSERT INTO YOURTABLE
SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
WHERE STATUS !='SLEEPING'
ORDER BY CPU DESC

you can schedule this query on SQL Server Agent

you can use another stuff for this purpose is

1-EventData()

http://msdn.microsoft.com/en-us/library/ms173781.aspx

2-Database Audit Specification

http://technet.microsoft.com/en-us/library/cc280404.aspx