Thanks. I added the order by because the actual purge script is loading records into a temp table ( address_id ) and someone suggested that deleting by that order would...

storing address records to keep would be a large group. Right now only about 4.5 million qualify for deletion.

TableName ...

Created13 February 2016 14:13

COLL Database

Tables

[dbo].[ADDRESS]

[dbo].[CHECK_REQUEST_DEFAULT_ADDRESS]

[dbo].[DIRECT_LENDING_SERVICE]

[dbo].[EARLY_PAYOFF_INFO]

[dbo].[LEGAL_ENTITY]

[dbo].[LIENHOLDER]

[dbo].[PACKET]

[dbo].[SERVICE_REQUEST]

[dbo].[WORK_QUEUE_ITEM]

prodserver

Databases(1)

•...

Good idea on ordering the temp table. I may experiment with slightly larger deletions but Management's main concern is no blocking/locking.

Yes, this is a method I'm using to gradually purge old, un-needed data on various tables ( in this case deleting one at a time )

SET TRANSACTION ISOLATION LEVEL READ...

Hi Steve. Over recent months I've been adding indexes missing on foreign keys as well as searching for missing indexes where I see table scans occurring. Since...

Unless you expect your database to remain small, the lesson seems to be make your data types large enough for the next 10 years, not for the next 10 days.

Scott, the table is not in the primary file group and the database has about 20 data files spread over numerous Netapp Luns if that's what you mean

CREATE TABLE [dbo].[FILE_RECORD](

[FILE_RECORD_ID]...

I'm still waiting for management and QA to decide what they want to do, but all of the suggestions here have been passed along. So, no test of anything...

At this point I think Igor and Sergiy are tired and quoting me sometimes when they think they're quoting eachother. 🙂

RedGate's smart rename function came up with this for...

Thanks for all of the feedback.. I'll check with development to see how large they expect line numbers to get ( I guess the @cap variable was just for...

Sergiy:

"By the end of the week, when the system is back online, your stuff is already packed into a box and ready for you to carry it away."

I fell out...

A test on a smaller database in QA showed that the alter table alter column approach ran for over an hour before I rolled it back. We have thousands of...

Thanks Scott. The direct alter table alter column approach would mean adding 250GB or so to our current 250GB transaction log ( spread over 8 equally sized data files...

I'll check out that approach. Thanks. I'll have to look at referential integrity constraints. If the application could handle keeping historical data in the existing column and "data...