asked
Need help deleting 96 million records
I am pulling database into my sql 2008 server i need to delete every eariler than march of last year. Nothing is working in my 2000 server. i've spent all weekend and no matter what i try it times out. What can i do in 2008 to get rid of them then i can just bring the rows into the 2000 server and i'm running out of time because everything takes 3-8 hours. I think it is working then it times out again.
for those tables without foreign keys reference to it, it might be faster to 1. rename existing table to old 2. create new table identical to old 3. insert required rows from old to new table 4. truncate and drop the old table
Recovery suggests that it was recovering from either a different restart of the server or it was finishing up a restore. Recovery is a process that may take a long time because it's rolling forward or rolling back transactions. If you have really long transactions and you're choosing to reboot in the middle of them, you'll have a really long recovery. Rebooting over and over will not speed this process, let the database finish recovering.
I created the new table and when trying to insert only the records needed from old table. It hosed the database. Filled log file and nothing i did would clear it the database is having to be rebooted. How can i insert records a little at a time with the criteria i need. FYI i brought the db into sql 2008 and created the table i need with only the 24 million records but sql 2000 can't even handle that as it timed out. I'm running out of time and I seriously need some help.
It is simple. There is 121.5 million records in old table and I need 24.8 million brought into new table.
Oh and database right now shows in recovery. I have IT guy rebooting the server but what do i do about that status.
SQL Server doesn't have a timeout by default. That means that you've either adjusted SQL Server itself or you're hitting a timeout in an application. If moving millions and millions of records is going to take a while, I'd suggest adjusting that application or SQL Server to turn off the timeout, first. Second, like any other performance problem, have you looked at the wait statistics to understand why things are running slow on the server? Have you looked at sys.dm_exec_requests to see if the process you're running is blocked? Have you looked at the execution plan to ensure that the DELETE statement is using an index to help speed the process?
If you are looking at deleting 96 million records and you don't really bother about them going away all in one go, I'd recommend deleting in smaller portions. If it's about migrating to a new database server, you might not even have production going on in the database while you are deleting data, so you could use simple recovery mode. Deleting in chunks will then have the advantage of not filling up the transaction log too much. And like Grant say: Look at the execution plan of a DELETE-statement and make sure you have an OK plan. Perhaps you have an identity-column as clustered index and know that it's the lower numbers of that identity-column you are looking to delete? In that case, you will probably gain some performance using the identity-column in the where-clause rather than a non clustered index on a date column. But that's just guessing from my part. Without knowing anything about the table and its indexes, it's hard to give firm advice.
If you have the same table in SQL Server 2008, you can create partition in such a way to keep old data and new data in different partitions and use partition switch to switch new data to new table and then drop the original table. It is very fast.
http://www.mssqltips.com/sqlservertip/1406/switching-data-in-and-out-of-a-sql-server-2005-data-partition/
18 People are following this question.
