DELETE IS NOT HAPPENING

Use sp_who2 and look at the blocking columns to see if another spid is blocking your delete from running.

you can check the locking info, using sp_Lock or using the following query

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

there is no statement is getting blocked.

Would you expect other activity on the server?  I'm thinking go to the OS and use Resource Monitor to check for cpu or disk activity.  I've done that a few times in the past when I wondered if something was actually happening.

you can do one thing set trigger or make an entry in your own transaction log table with id of deleted record...

so you can keep track of how many records get deleted

as you said its millions  of records you can delete entries from that table after each 1000 records..

although you delete 1000 records you can easily track record using id of deleted record.

you can also refer below link for more details...


http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/

I suggest you not to delete all records in a single operation, but to split your query into multiple operations.
This is how I delete records from a table, based on a date field:

while exists (select 1 from MyTable where MyDate < '20101200')
delete top(10000) from MyTable
where MyDate < '20101200'

Select allOpen in new window

This works for me.

I hope this can help.
Regards.

I suggest you can use trigger and log table having  row_created_date  column to find exactly how many records are deleted & at what time , can also split you delete query  as suggested by jonnidip.

I hope this works.

i found the reason om my own way