Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a stored-procedure which is executed at 7 o'clock in the morning that updates the database because new records were imported at night.

Today it started but never endet.

So i've tried to run the main-update in the SP manually on only one record with the TOP(1) statement. But it still runs endless(i stopped it after 20 minutes).

Here's the complete statement, although i assume that it's not related to the cause:

SET NOCOUNT ON;
GO

UPDATE TOP(1) [dbo].[tabData] SET
    [Claim_Age] = [dbo].[__TAT]([Repair_Completion_Date], [Claim_Submitted_Date]),

    [TAT_B2B] = [dbo].[__TAT]([Received_Date], [Repair_Completion_Date]),
    [TAT_Total] = [dbo].[__TAT]([Received_Date], [Returned_Date]),

    [fiCountrySold] = (SELECT D.idCountrySold FROM dimCountrySold AS D WHERE D.CountrySold = tabData.CountrySold),

    [SL_LaborPrice] = (SELECT SUM([SL_Labor_Price]) FROM [dbo].[tabDataDetail] DD WHERE [DD].[fiData] = [idData]),
    [SL_LaborPrice_notAC22_Parts] = (SELECT SUM([SL_Labor_Price]) FROM [dbo].[tabDataDetail] DD WHERE [DD].[fiData] = [idData] AND [DD].[fiActionCode] = 1),
    [SL_LaborPrice_AC22_Parts] = (SELECT SUM([SL_Labor_Price]) FROM [dbo].[tabDataDetail] DD WHERE [DD].[fiData] = [idData] AND [DD].[fiActionCode] = 22),
    [SL_LaborPrice_AC1_Parts] = (SELECT SUM([SL_Labor_Price]) FROM [dbo].[tabDataDetail] DD WHERE [DD].[fiData] = [idData] AND [DD].[fiActionCode] = 1),
    [SL_PartPrice] = (SELECT SUM([SL_Part_Price]) FROM [dbo].[tabDataDetail] DD WHERE [DD].[fiData] = [idData]),
    [SL_PartPrice_notAC22_Parts] = (SELECT SUM([SL_Part_Price]) FROM [dbo].[tabDataDetail] DD WHERE [DD].[fiData] = [idData] AND [DD].[fiActionCode] = 1),
    [SL_PartPrice_AC22_Parts] = (SELECT SUM([SL_Part_Price]) FROM [dbo].[tabDataDetail] DD WHERE [DD].[fiData] = [idData] AND [DD].[fiActionCode] = 22),
    [SL_PartPrice_AC1_Parts] = (SELECT SUM([SL_Part_Price]) FROM [dbo].[tabDataDetail] DD WHERE [DD].[fiData] = [idData] AND [DD].[fiActionCode] = 1),

    [GSP_LaborPrice] = (SELECT SUM([GSP_Labor_Price]) FROM [dbo].[tabDataDetail] DD WHERE [DD].[fiData] = [idData]),
    [GSP_LaborPrice_notAC22_Parts] = (SELECT SUM([GSP_Labor_Price]) FROM [dbo].[tabDataDetail] DD WHERE [DD].[fiData] = [idData] AND [DD].[fiActionCode] = 1),
    [GSP_LaborPrice_AC22_Parts] = (SELECT SUM([GSP_Labor_Price]) FROM [dbo].[tabDataDetail] DD WHERE [DD].[fiData] = [idData] AND [DD].[fiActionCode] = 22),
    [GSP_LaborPrice_AC1_Parts] = (SELECT SUM([GSP_Labor_Price]) FROM [dbo].[tabDataDetail] DD WHERE [DD].[fiData] = [idData] AND [DD].[fiActionCode] = 1),
    [GSP_PartPrice] = (SELECT SUM([GSP_Part_Price]) FROM [dbo].[tabDataDetail] DD WHERE [DD].[fiData] = [idData]),
    [GSP_PartPrice_notAC22_Parts] = (SELECT SUM([GSP_Part_Price]) FROM [dbo].[tabDataDetail] DD WHERE [DD].[fiData] = [idData] AND [DD].[fiActionCode] = 1),
    [GSP_PartPrice_AC22_Parts] = (SELECT SUM([GSP_Part_Price]) FROM [dbo].[tabDataDetail] DD WHERE [DD].[fiData] = [idData] AND [DD].[fiActionCode] = 22),
    [GSP_PartPrice_AC1_Parts] = (SELECT SUM([GSP_Part_Price]) FROM [dbo].[tabDataDetail] DD WHERE [DD].[fiData] = [idData] AND [DD].[fiActionCode] = 1),
    [Reused_Price] = (SELECT SUM([Reused_Price]) FROM [dbo].[tabDataDetail] DD WHERE [DD].[fiData] = [idData]),

    [fimaxActionCode] = (SELECT TOP 1 [AC_C].[fiActionCode] FROM [tabDataDetail] [DD] INNER JOIN [tdefActionCode_Compensation] [AC_C] ON [DD].[fiActionCode] = [AC_C].[fiActionCode] AND [DD].[fiServiceLevel] = [AC_C].[fiServiceLevel] WHERE [DD].[fiData] = [idData] ORDER BY [AC_C].[Compensation] DESC),
    [fionlyActionCode] = (SELECT CASE WHEN MIN([DD].[fiActionCode]) = MAX([DD].[fiActionCode]) THEN MIN([DD].[fiActionCode]) ELSE 0 END FROM [dbo].[tabDataDetail] [DD] WHERE [DD].[fiData] = [idData]),
    [fimaxServiceLevel] = [dbo].[_idmaxServiceLevel](idData),
    [fiReceivedDate] = (SELECT idDate FROM tdefDate WHERE CONVERT(VARCHAR, tabData.Received_Date, 112) = tdefDate.Text),
    [fiRepairCompletionDate] = (SELECT idDate FROM tdefDate WHERE CONVERT(VARCHAR, tabData.Repair_Completion_Date, 112) = tdefDate.Text),
    [fiReturnedDate] = (SELECT idDate FROM tdefDate WHERE CONVERT(VARCHAR, tabData.Returned_Date, 112) = tdefDate.Text),
    [fiClaimCreationDate] = (SELECT idDate FROM tdefDate WHERE CONVERT(VARCHAR, tabData.Claim_Creation_Date, 112) = tdefDate.Text),
    [fiClaimSubmittedDate] = (SELECT idDate FROM tdefDate WHERE CONVERT(VARCHAR, tabData.Claim_Submitted_Date, 112) = tdefDate.Text),
    [fiInvoiceNumberAddedDate] = (SELECT idDate FROM tdefDate WHERE CONVERT(VARCHAR, tabData.Invoice_Number_Added_Date, 112) = tdefDate.Text),
    [fiManufactureDate] = (SELECT idDate FROM tdefDate WHERE CONVERT(VARCHAR, tabData.Manufacture_Date, 112) = tdefDate.Text),
    [fiLastUpdatedDate] = (SELECT idDate FROM tdefDate WHERE CONVERT(VARCHAR, tabData.Last_Updated_Date, 112) = tdefDate.Text),
    [IsOpened] = [dbo].[_isOpened](tabData.idData) 
WHERE [Claim_Age] 
    IS NULL;

So my question is: What reasons can cause an update from executing(which normally runs without a problem)?

Edit: sys.dm_os_waiting_tasks returns following:

SELECT *
FROM sys.dm_os_waiting_tasks;

waiting_task_address    session_id  exec_context_id wait_duration_ms    wait_type
0x0000000007C08478  6   0   3557                         REQUEST_FOR_DEADLOCK_SEARCH
0x0000000007C125C8  13  0   131087                       CHECKPOINT_QUEUE
0x0000000007C1C5C8  15  0   957788980                    BROKER_TRANSMITTER
0x0000000007C4E5C8  4   0   1019248                      LAZYWRITER_SLEEP
0x0000000008686328  NULL    NULL    6469704              CLR_AUTO_EVENT
0x000000000868E328  NULL    NULL    6469704              CLR_AUTO_EVENT
0x0000000004608478  5   0   131087                       LOGMGR_QUEUE
0x00000000046125C8  7   0   957785860                    KSOURCE_WAKEUP
0x000000000461C478  12  0   957789947                    ONDEMAND_TASK_QUEUE
0x0000000004630478  64  0   0                            OLEDB
0x000000000463A478  11  0   957788980                    BROKER_TRANSMITTER
0x0000000004644478  14  0   101693214                    BROKER_EVENTHANDLER
0x000000000464E5C8  3   0   639                          LAZYWRITER_SLEEP
0x000000000477A328  9   0   998                          SQLTRACE_BUFFER_FLUSH

I don't know what to do with this information.

share|improve this question
3  
Might be blocked by another transaction. What does sys.dm_os_waiting_tasks say? – Martin Smith Apr 24 '12 at 8:39
@MartinSmith: Edited my answer, what does the result mean? – Tim Schmelter Apr 24 '12 at 9:14
@MartinSmith: Just now i wanted to restart the server and noticed that there was an open transaction on closing one of the open SSMS windows. Somehow strange though, there was no explicit transaction there, just a select which executed successfully(afaik). Anyway, i hope this problem is solved now. – Tim Schmelter Apr 24 '12 at 10:03
If I were you I would read about the performance implications of correlated subqueries. – HLGEM Apr 24 '12 at 19:08
@HLGEM: This SP runs > 1,5h what actually is fast enough. But business rules might change, hence it's more important to ensure readability and changeability. – Tim Schmelter Apr 24 '12 at 19:57

1 Answer

up vote 1 down vote

Run this code for checking any blocking in your process.

SELECT 
    OBJECT_NAME(ObjectID) as ObjectName,
    st.Text, 
    DB_NAME(database_ID) as dbname,
    Blocking_session_ID as BlockingSessionID,  
    *
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
share|improve this answer
Thanks. But this query returns just one record which apparently is the query itself. Objectname is null, Text is this query, dbname is the current db, BlockingSessionID = 0, session_id = 64, request_id = 0 etc. – Tim Schmelter Apr 24 '12 at 9:48
@TimSchmelter use this query in separate window while running this update code. So it will tell you if there is some blocking or wait type associated with that update query. – Ashish Apr 24 '12 at 13:12
Keep in mind that if the session that is blocking is not an active session (sleeping state), the offending spid won't show up in this DMV. It seems that was exactly the case for the OP. – Anon246 Apr 24 '12 at 14:56
@Ashish: +1 since it might be usefule sometime. But would you please explain it a bit? What does it mean when it lists something(apart from itself)? – Tim Schmelter Apr 24 '12 at 15:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.