Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

DELETE IS NOT HAPPENING

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
0
VIVEKANANDHAN_PERIASAMY
Asked:
VIVEKANANDHAN_PERIASAMY
1 Solution
 
mastooCommented:
Use sp_who2 and look at the blocking columns to see if another spid is blocking your delete from running.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
there is no statement is getting blocked.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
mastooCommented:
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.
0
 
keyuCommented:
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/
0
 
jonnidipCommented:
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.
Regards.
0
 
kapilkhalasCommented:
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.
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
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.
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
i found the reason om my own way
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now