Tracking Progress in a Script with Multiple Commands in SQL Server

monitoringsql serversql server 2014ssmst-sql

I have some processes that require different steps to be taken before they start. For example:

Disable check constraint on foreign key:

select 'alter table '+fk.table_schema+'.'+fk.table_name
+' NOCHECK CONSTRAINT '+fk.CONSTRAINT_NAME 
from SAProduct.information_schema.table_constraints fk 
join SAProduct.information_schema.tables t
on t.table_schema = fk.table_schema 
and t.table_name = fk.table_name 
WHERE constraint_type='FOREIGN KEY'

This will produce a big list of statements, I am copying only 3:

use saproduct
go
alter table dbo.tblProdClassificationCode NOCHECK CONSTRAINT FK_tblProdClassificationCode_tblProdClassification
alter table dbo.tblProdClassificationCodeDescr NOCHECK CONSTRAINT fk_ProdClassificationCodeDescr_ProdClassificationCode
alter table dbo.tblProdClassificationCodeGenerate NOCHECK CONSTRAINT fk_ProdClassificationCodeGenerate_ProdClassification

If I was to run that on one go, how would I find out where I am at?
which table I am dealing with, and which one is the next?

The same for the delete:

select 'DELETE FROM '+table_schema+'.'+table_name
from saproduct.information_schema.tables 
where table_name like 'tbl%'

the script above would produce the set of statements below:

use saproduct
go
DELETE FROM dbo.tblBLanguage
DELETE FROM dbo.tblBLgCategoryXRef
DELETE FROM dbo.tblBLgSegmentXRef
DELETE FROM dbo.tblProdClassification
DELETE FROM dbo.tblProdClassificationCode
DELETE FROM dbo.tblProdClassificationCodeDescr
DELETE FROM dbo.tblProdClassificationCodeGenerate
DELETE FROM dbo.tblProdClassificationDescr
DELETE FROM dbo.tblProdClassificationMarket
DELETE FROM dbo.tblProdClassificationTier
DELETE FROM dbo.tblProdClassificationValue
DELETE FROM dbo.tblProdData
DELETE FROM dbo.tblProdDataDescr
DELETE FROM dbo.tblProdDataMarket
DELETE FROM dbo.tblProdDataTempTier1Descr
DELETE FROM dbo.tblProdDataTempTier1Tier2Descr
DELETE FROM dbo.tblProdDataTier
DELETE FROM dbo.tblProdDataValue
DELETE FROM dbo.tblProdDataValueDate
DELETE FROM dbo.tblProdDataValueNumber
DELETE FROM dbo.tblProdDataValueString
DELETE FROM dbo.tblProdName
DELETE FROM dbo.tblProdNameSizeAlias
DELETE FROM dbo.tblProdNameSizeRuleAlias
DELETE FROM dbo.tblProdNameStructure
DELETE FROM dbo.tblProdNameStructureDescr
DELETE FROM dbo.tblProduct

Which table is the next one for me to delete?

I am trying to access tables in a certain order to minimise deadlocks, specially when having to put any type of exclusive locks. it is not here, but I try to avoid lock escalation too, so it can get complex. I like to "see" what exactly is going on. I am using SQL Server 2014:

Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 
    Feb 20 2014 20:04:26 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Best Answer

I agree with Aaron that RAISERROR...WITH NOWAIT can be very useful and is probably the way to go if you have full control over the script that is being generated.

However, if a long script is currently executing and you don't have the ability to change the script in order to add RAISERROR calls, there are also less direct ways to get this information.

Test script

Here is a test script you can run to help demonstrate the two approaches below:

SELECT 1
WAITFOR DELAY '00:00:15'
SELECT 2
WAITFOR DELAY '00:00:15'
SELECT 3

sp_whosiasctive

While running this script, you can use sp_whoisactive to view the current server activity. You can often view the query plan for the specific statement that is currently executing. In my case, I see the following because the WAITFOR statement is most likely to be running at any given moment in time:

enter image description here

Using sys.dm_exec_requests.statement_start_offset

Alternatively, Conor Cunningham also has a post on extracting the statement from sys.dm_exec_query_stats AND sys.dm_exec_sql_text. I don't believe this has been incorporated into sp_whoisactive yet, but you can use a query like the following to see both the current executing statement and the overall batch.

enter image description here

SELECT er.session_Id AS spid
    --Use the full batch text and the start/end offset of the currect statement to figure 
    --out the SQL that is currently executing. This logic is based on the blog post above
    --but has been updated in light of strange cases in SQL Server that caused the original
    --blog post logic to crash with out of bounds errors on the SUBSTRING operation.
    , SUBSTRING (qt.text 
                , (CASE WHEN er.statement_start_offset > DATALENGTH(qt.text) 
                    THEN 0 ELSE er.statement_start_offset/2 END)+1
                , (CASE WHEN er.statement_end_offset <= 0 THEN DATALENGTH(qt.text)
                    ELSE er.statement_end_offset 
                    END - CASE WHEN er.statement_start_offset > DATALENGTH(qt.text) 
                        THEN 0 ELSE er.statement_start_offset/2 END)
                    + 1
                ) AS query
    , qt.text AS parent_query
FROM sys.dm_exec_requests er
JOIN sys.dm_exec_sessions s
    ON s.session_id = er.session_id
    AND s.session_id <> @@SPID      -- Ignore this current statement.
    AND s.is_user_process = 1       -- Ignore system spids.
    AND s.program_name NOT LIKE '%SQL Server Profiler%' -- Ignore profiler traces
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
ORDER BY spid
Related Question