Posted on 2012-09-19
Last Modified: 2012-10-04
We need to delete around 30 millions from each table based on conditions,We also have a stored procedure but it's taking hours or getting hung at sometimes.How can we make sure the data is getting deleted from the table.

I tried checking with below query ,but all remains 0 except memory_usage which is constant at 2
select logical_reads,cpu_time,memory_usage,row_count,reads,writes from sys.dm_exec_sessions
where login_name ='vivek'
and session_id=61
    LVL 21

    Expert Comment

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

    Expert Comment

    by:Aneesh Retnakaran
    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,
   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
    LVL 5

    Author Comment

    there is no statement is getting blocked.
    LVL 21

    Expert Comment

    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.
    LVL 9

    Expert Comment

    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...
    LVL 13

    Expert Comment

    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'

    Open in new window

    This works for me.

    I hope this can help.

    Expert Comment

    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.
    LVL 5

    Accepted Solution

    there were 3 select statement in the SQL which is taking long time pick the parameter for delete operation, and there was 98% fragmentation on index.I rebuilded the index and deleted operation went smoothly.
    LVL 5

    Author Closing Comment

    i found the reason om my own way

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now