October 7, 2011 at 9:10 am
i was running a query in a DB which was running to more then 2 hrs(i put a transaction before executing that) then i cancel the query now i am not able to access any procedures of that DB. its showing error
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476
what to do now??
need urgent help on this
October 7, 2011 at 9:16 am
There's still a transaction opened (probably rolling back).
Check out sp_WhoIsActive from Adam Mechanic or this stolen from the activity monitor :
--Borrowed from the activity monitor
SELECT
[Session ID] = s.session_id,
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[Login] = s.login_name,
[Database] = ISNULL(db_name(p.dbid), N''),
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024,
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0)
-- [Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
--LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)--TAKE THIS dmv OUT TO WORK IN 2005
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
ORDER BY s.session_id;
October 7, 2011 at 9:22 am
thanks ninja for ur quick reply
Session IDUser ProcessLoginDatabaseTask StateCommandApplicationWait Time (ms)Wait TypeWait ResourceBlocked ByHead BlockerTotal CPU (ms)Total Physical I/O (MB)Memory Use (KB)Open TransactionsLogin TimeLast Request Start TimeHost NameNet AddressExecution Context IDRequest ID
10saAvalon_IBMWarrenty_AutoRUNNINGRESOURCE MONITOR000002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
20saAvalon_IBMWarrenty_AutoSUSPENDEDLAZY WRITER187LAZYWRITER_SLEEP00002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
30saAvalon_IBMWarrenty_Auto000002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
40saAvalon_IBMWarrenty_AutoSUSPENDEDLOCK MONITOR2203REQUEST_FOR_DEADLOCK_SEARCH00002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
50samasterSUSPENDEDSIGNAL HANDLER1824041719KSOURCE_WAKEUP00002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
60samasterTASK MANAGER000002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
70samasterSUSPENDEDTRACE QUEUE TASK1640SQLTRACE_BUFFER_FLUSH00002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
80saAvalon_IBMWarrenty_Auto000002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
90samasterSUSPENDEDBRKR TASK1824052937BROKER_TRANSMITTER00002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
100samasterSUSPENDEDTASK MANAGER1824055187ONDEMAND_TASK_QUEUE00002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
110samaster000002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
120samasterTASK MANAGER000002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
130samasterTASK MANAGER000002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
140samasterTASK MANAGER000002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
150samasterTASK MANAGER000002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
160samasterSUSPENDEDBRKR EVENT HNDLR2053703BROKER_EVENTHANDLER00002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
170samasterSUSPENDEDBRKR TASK1824052937BROKER_TRANSMITTER00002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
180samasterTASK MANAGER000002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
190samasterTASK MANAGER000002011-09-16 05:38:13.5372011-09-16 05:38:13.53700
511SERVICESOURCE\crmadminprdMSCRM_MIGRATION.Net SqlClient Data Provider0001602011-10-07 08:19:10.3602011-10-07 08:19:10.360COLOCRMPRDCDI<local machine>00
521SERVICESOURCE\crmadminprdmasterRUNNINGEXECUTEMicrosoft SQL Server Management Studio - Query0001602011-09-26 03:36:37.7732011-09-26 05:32:46.197COLOCRMPRDCDI<local machine>00
531DMAvalon_IBMWarrenty_AutoRUNNINGSELECTMicrosoft SQL Server Management Studio - Query0OLEDB001602011-10-07 08:19:04.5172011-10-07 08:19:10.407GHANSHYAMK10.100.100.5700
541DMAvalon_IBMWarrentyMicrosoft SQL Server Management Studio - Query0001602011-10-07 08:05:47.2702011-10-07 08:16:26.847COLOCRMPRDCDI<local machine>00
551SERVICESOURCE\bosqlservicemsdbSQLAgent - Generic Refresher017211602011-09-16 05:38:16.1472011-10-03 22:16:20.057COLOCRMPRDCDI<local machine>00
561SERVICESOURCE\bosqlservicemsdbSQLAgent - Job invocation engine0481802011-09-19 00:25:01.0202011-10-07 00:25:00.760COLOCRMPRDCDI<local machine>00
571DMmasterMicrosoft SQL Server Management Studio020301602011-10-07 08:09:57.7832011-10-07 08:11:04.753GHANSHYAMK10.100.100.5700
581DMAvalon_IBMWarrentyMicrosoft SQL Server Management Studio028101602011-10-07 08:11:11.2372011-10-07 08:11:17.880GHANSHYAMK10.100.100.5700
591SERVICESOURCE\bosqlserviceReportServerReport Server000802011-10-07 08:19:02.7332011-10-07 08:19:02.733COLOCRMPRDCDI<local machine>00
601SERVICESOURCE\bosqlservicemasterMicrosoft SQL Server0001602011-10-07 08:15:00.9072011-10-07 08:15:00.907COLOCRMSTGCDI10.100.6.12400
601SERVICESOURCE\bosqlservicemasterMicrosoft SQL Server0001602011-10-07 08:15:00.9072011-10-07 08:15:00.907COLOCRMSTGCDI10.100.6.12400
611SERVICESOURCE\bosqlservicemasterMicrosoft SQL Server0001602011-10-07 08:15:00.7202011-10-07 08:15:00.720COLOCRMSTGCDI10.100.6.12400
611SERVICESOURCE\bosqlservicemasterMicrosoft SQL Server0001602011-10-07 08:15:00.7202011-10-07 08:15:00.720COLOCRMSTGCDI10.100.6.12400
671SERVICESOURCE\bosqlserviceAvalon_IBMWarrenty_AutoSUSPENDEDSET OPTION ONMicrosoft SQL Server12136469LCK_M_SCH_Sobjectlock lockPartition=0 objid=1186103266 subresource=FULL dbid=38 id=lock8050880 mode=Sch-M associatedObjectId=11861032667200802011-10-07 04:56:53.5172011-10-07 04:56:53.827COLOCRMPRDCDI<local machine>00
711DMmasterMicrosoft SQL Server Management Studio018901602011-10-07 04:42:50.9732011-10-07 08:15:15.267COLOCRMPRDCDI<local machine>00
721DMmasterRUNNINGEXECUTEMicrosoft SQL Server Management Studio - Query0110901612011-10-07 04:33:07.8202011-10-07 04:51:58.517GHANSHYAMK10.100.100.5700
this is the result it is showing
mine DB was Avalon_IBMWarrenty_Auto
what to do next??
October 7, 2011 at 9:30 am
thanks ninja for your quick reply
it was blocked some of the objects, and the lock was not released, i have restarted sql server services and now i am able to access everything.
i though i was in soup for a moment :hehe:
October 7, 2011 at 9:43 am
ghanshyam.kundu (10/7/2011)
thanks ninja for your quick replyit was blocked some of the objects, and the lock was not released, i have restarted sql server services and now i am able to access everything.
i though i was in soup for a moment :hehe:
That should have been your absolute last resort.
The best thing was to let the application finish the rollback. It was probably blocked by something else, but now it's too late to investigate.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply