Tips: Deleting a Lot of Rows from a Database

Recently, I had to remove a large number of rows, almost 10 million, from a very active database table in a LIVE system. Forget why for now as that is the subject of another post. The basic problem was how to remove 99.8% of the rows without impacting users or removing the few rows that we actually wanted to keep. To make matters worse, the field that was determined to have all the answers didn’t have an index.

Adding an index to the field in question would only solve part of the problem. It would take resources just to apply the key to a table that large, especially one that already had several indices. There are also locking issues and let us not forget the Transaction Log usage. We are talking Gigabytes of space for any new index and for temporary Transaction Log space.

Continue reading

Tips: Moving to SQL Server 2005 for D6

As many of you may know, or recall, D6 does not support SQL Server 2000 anymore. This is fine and good. Most larger installations moved to Oracle years ago. However, there are a large number of Microsoft shops out there, a SQL Server is a quick to standup database for development, POC, and demo environments. So I thought I would share some tidbits on two different upgrades of SQL Server 2005. Neither were in a clustered environment, but that will happen soon and I’ll share then.

Continue reading