November 18, 2013 at 2:13 pm
I am trying to upgrade to SQL 2012 from SQL 2005. I have a job that deletes expired records from a single table one a month. There are millions of records that it deletes. The job works just fine in SQL 2005. In SQL 2012 however, the job just hangs indefinitely. There are no on-delete triggers on the table. In the script, records are successfully deleted from the parent table that it is associated with first and that works fine.
Any ideas on the matter would be much appreciated.
Thanks
November 18, 2013 at 2:38 pm
Hi and welcome to the forums! It is nearly impossible to provide any kind of assistance based on the limited amount of information you provided. It sounds like maybe you have cascading deletes? Seems weird that it just stops, surely something is going on.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 18, 2013 at 2:45 pm
1) IIRC, it is mandatory to update all statistics with FULLSCAN when upgrading from SQL 2005. You could be getting a horribly bad plan on the delete.
2) use sp_whoisactive to check for blocking, etc while the delete is running.
3) It is often appropriate to batch up large-scale DELETEs like this, doing an index seek on something (such as identity or date column) and doing 50-100K records at a time with explicit transaction control and error handling. This can also allow you to keep an eye on the tlog size and act accordingly if it gets bloated
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 18, 2013 at 2:45 pm
raysteve43 (11/18/2013)
I am trying to upgrade to SQL 2012 from SQL 2005. I have a job that deletes expired records from a single table one a month. There are millions of records that it deletes. The job works just fine in SQL 2005. In SQL 2012 however, the job just hangs indefinitely. There are no on-delete triggers on the table. In the script, records are successfully deleted from the parent table that it is associated with first and that works fine.Any ideas on the matter would be much appreciated.
Thanks
also important: after you upgrade from a lower version of SQL, it's absolutely manditory to rebuild indexes and update your statistics for all tables.
plans that used to work well, based on exisitng statistics can be incredibly slow after an upgrade specifically because of statistics; there are lots of posts here on SSC about how someones new super server is slower than the old one it replaced, and it usually boils down to not rebuilding indexes and updating statistics with fullscan after the restore ont he new server.
Lowell
November 18, 2013 at 3:36 pm
Success!! Many thanks
After running the statement below, I was able to successfully run my delete statement.
UPDATE STATISTICS MyTable WITH FULLSCAN
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply