You administer a SQL Server 2000 computer that stores inventory data. Each weeknight you run a series of maintenance jobs in this order: (1) BULK INSERT data at 10:00 P.M., (2) rebuild indexes at 10:15 P.M., (3) run data integrity checks at 10:30 P.M., (4) perform a differential backup at 10:45 P.M., and (5) run DBCC SHRINKDATABASE at 11:00 P.M. The final DBCC SHRINKDATABASE job often fails with error message 3140 stating that SQL Server could not adjust space allocation for a file. How can you ensure that the shrink operation completes successfully?

Introduction / Context:
Database maintenance plans often string together multiple tasks such as data loads, index rebuilds, integrity checks, backups and file shrinking. When these tasks run too close together, they can interfere with each other and cause failures. In this case, DBCC SHRINKDATABASE frequently fails with an error about adjusting space allocation after a differential backup has just run. This question asks how to adjust the schedule so the shrink operation succeeds.

Given Data / Assumptions:

• Nightly jobs run in the following order: BULK INSERT, index rebuild, DBCC CHECKDB or similar integrity checks, differential backup, then DBCC SHRINKDATABASE.
• Error 3140 occurs during DBCC SHRINKDATABASE, indicating space allocation issues while shrinking the file inventory_data.
• The differential backup runs just 15 minutes before the shrink operation.
• You want the shrink operation to complete reliably without errors.

Concept / Approach:
Differential backups read changed extents and can hold locks and use resources that overlap with operations that try to move or deallocate pages, such as DBCC SHRINKDATABASE. If the backup is still finishing or if there is heavy I/O contention, the shrink operation may fail to move or deallocate certain pages, resulting in error 3140. By increasing the time gap between the differential backup and the shrink operation, you allow the backup to complete cleanly and free resources, making it more likely that the shrink will succeed. Structural changes like switching to DBCC SHRINKFILE do not address the core timing conflict.

Step-by-Step Solution:
1. Review the durations of the differential backup and the DBCC SHRINKDATABASE job to understand how long each typically takes. 2. Recognize that the shrink operation is scheduled very soon after the backup, which can cause contention. 3. Modify the SQL Server Agent job schedule so that DBCC SHRINKDATABASE starts later, leaving enough time for the differential backup to complete. 4. Optionally monitor the backup job completion status and use job chaining so that the shrink job starts only after the backup job succeeds. 5. After adjusting the schedule, observe whether the shrink job completes successfully without error 3140.

Verification / Alternative check:
Once you increase the delay between the backup and the shrink operation, check SQL Server Agent history for the shrink job over several nights. Errors should no longer occur. You can also monitor disk I/O counters and backup job durations during the maintenance window to confirm that the backup is fully complete before the shrink job begins. If the shrink job still occasionally fails, you may need to give it even more time or re-evaluate whether shrinking is necessary at all.

Why Other Options Are Wrong:
Option A, switching to DBCC SHRINKFILE, changes the granularity of the shrink operation but does not eliminate the underlying contention with the recent backup; the same error can still occur if timing is too tight. Option B, running a new SHRINKFILE job at the same time as SHRINKDATABASE, would likely increase contention rather than reduce it. Option D moves the integrity checks relative to the backup but does not directly address the conflict between the backup and the shrink operation, which is where the problem lies.

Common Pitfalls:
Administrators sometimes try to pack many maintenance tasks into a very short window, leaving them scheduled back-to-back without considering that earlier tasks may overrun. Another pitfall is assuming that changing the type of shrink operation alone will fix errors caused by timing and resource conflicts. It is also easy to overuse shrinking; frequent shrinking can cause fragmentation and may not always be necessary. Carefully reviewing job order and timing is essential for stable maintenance plans.

Final Answer:
You should increase the time between the differential backup and the DBCC SHRINKDATABASE job so that the backup completes fully before the shrink starts, allowing the shrink operation to succeed without error 3140.