I have a database maintenance task that runs every night at 10 PM to clean up old records. Users keep getting timeout messages on the website between 10 PM and midnight when this job is running.
Here’s what the job logic looks like:
while @continueJob = 1 and @processedRecords < @maxRecords
exec CleanupData @processedRecords out
if @@error <> 0
set @continueJob = 0
waitfor delay '00:10:00'
The CleanupData procedure finds the oldest 100 records from 30 days back, moves them to an archive database, then removes them from the main tables along with related data. It also builds temporary tables during processing and drops them when done. The @processedRecords parameter gets updated with how many items were handled.
What could be causing the performance problems during this window?
Your archive operation plus temp table creation is probably causing massive transaction log bloat. SQL Server 2000’s memory management is subpar compared to newer versions; when your procedure builds temp tables while processing batches, it will exhaust available memory quickly. This forces everything to disk, which explains the prolonged poor performance. Consider wrapping smaller record sets in explicit transactions and ensure there’s enough transaction log space allocated upfront. Running DBCC SHRINKLOG during maintenance windows may help manage log file growth, which is likely affecting your concurrent users.
have u looked into if the cleanup job’s holding locks for too long? temp tables can block live queries. and what’s up with waiting 10 min between batches? seems like it could stretch your maintenance time more than needed. maybe try shorter delays?
sounds like resource contention. moving data between databases hammers disk i/o hard, especially when both dbs share the same drives. sql 2000 struggles with concurrent access during heavy maintenance too. try breaking it into smaller, frequent batches instead of one massive nightly job.