Lock Files SQL - Lock Logger

Yahoo Archive
1
There is the "SP_2Lock" program that was written by Raj in Epicor
Development, it was set to allow it to be run through the Vantage Admin
tools to detect locks from an OpenEdge AppServers to a SQL Database.



-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Rupert
Sent: Thursday, September 23, 2010 6:58 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Lock Files SQL - Lock Logger

Hi Guys

Does anyone know where I can get or if it does exist a program or routine
called Lock logger that shows all "locked tables" in SQL? We're experiencing
some problems with ODBC locking some files (When Accessed incorrectly) and
we're trying to find who and what is locked.

Thanks



------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
2
Hi Guys

Does anyone know where I can get or if it does exist a program or routine called Lock logger that shows all "locked tables" in SQL? We're experiencing some problems with ODBC locking some files (When Accessed incorrectly) and we're trying to find who and what is locked.

Thanks
3
Here's a little query I found:

SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id


Joe Luster
Network Administrator
Cold Jet, LLC
455 Wards Corner Road
Loveland, Ohio 45140
USA
+1 513-831-3211 x308 (office)
+1 513-831-1209 (fax)
www.coldjet.com<http://www.coldjet.com>
[cid:image001.jpg@01CB5AFC.D6229480]
Setting industry standards in dry ice technology and solutions for over 20 years.



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Rupert
Sent: Thursday, September 23, 2010 6:58 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Lock Files SQL - Lock Logger



Hi Guys

Does anyone know where I can get or if it does exist a program or routine called Lock logger that shows all "locked tables" in SQL? We're experiencing some problems with ODBC locking some files (When Accessed incorrectly) and we're trying to find who and what is locked.

Thanks



[Non-text portions of this message have been removed]