Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

scheduling job Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 3:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
I would recommend against running a job every 4 seconds. Instead it would be better for you to make a stored procedure and use the WAITFOR DELAY command inside a loop.


create proc dbo.get_lock_report
as
begin

while 1=1
begin

-- sleep for 4 seconds
waitfor delay '00:00:04'

SELECT
spid = Lock.request_session_id,
HostName = Session.host_name,
ProgramName = Session.program_name,
Cmd = Request.Command,
LoginName = Session.login_name,
dbid = Lock.resource_database_id,
DatabaseName = DB_NAME(Lock.resource_database_id),
Type = Lock.resource_type,
LockType = Lock.request_type,
Mode = Lock.request_mode,
Status = request_status,
UserTable.Name

FROM
sys.dm_tran_locks AS Lock

LEFT JOIN
sys.dm_exec_sessions AS Session ON
Session.session_id = Lock.request_session_id

LEFT JOIN
sys.dm_exec_requests AS Request ON
Request.session_id = Session.session_id

LEFT JOIN
sys.tables AS UserTable ON
UserTable.object_id = Lock.resource_associated_entity_id
AND
type = 'U'

WHERE
Lock.resource_type = 'OBJECT'

end
end
go



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1434046
Posted Thursday, March 21, 2013 3:12 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:06 PM
Points: 319, Visits: 757
Thanks. Will this run every 4 seconds from the time the stored procedure is called in the program? Also the results from the select should go into xls file, may be appended every 4 seconds... Is this possible?

Thanks in advance
Post #1434048
Posted Thursday, March 21, 2013 4:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
Will this run every 4 seconds from the time the stored procedure is called in the program?

The stored procedure will execute indefinitely, but every 4 seconds it will deliver a new resultset. Compile it in your environment and try it out.

Also the results from the select should go into xls file, may be appended every 4 seconds... Is this possible?

If you want to write the resultset to an Excel file then you probably would want to write the resultset to a table every 4 seconds, and then have a separate process pull the data in that table periodically, but far less than every 4 seconds.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1434069
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse