Task 7: Shrink statement

• On the production server the ETL job failed with the following error: Could not adjust the space allocation for file ... and then the ETL process was running again. The last step to run was step Shrink Truncate Data.
• What I found are some advises regarding shrink using: 

          1. Constant shrinking and growth is bad for performance.
          2. Automatic space allocation always causes a delay.
          3. The physical files get fragmented on the hard drive. 

• Another possibility is that another job is running against the database at the same time, eg. a backup job. You can't do a database or log backup at the same time as a shrink operation. Shrink file is not a best practice,  it cause data fragment, but sometimes after you clean the database with deleting old data, you had to shrink file and it should be fine as long as you run rebuild index after shrink file.

          DBCC SHRINKFILE(1, 1000)
          GO 

• As a suggested sequence we can try the following:

       1. Backup databases
       2. Detach the db 
       3. Attach it
       4. Put the db into single user mode
       5. Attempt the dbcc shrink file operation again
       6. If successful,  rebuild the indexes 
       7. Perform dbcc checkdb
       8. Database backup