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