1

My customer needed to migrate his production database to a new server. The majority of the database is audit messages. The file was initially about 200GB. What I've always done in this situation, to minimize downtime, is to migrate a backup to the new server, then truncate the audit table. This guarantees that anything after the switch is all new data, and once the old database is no longer in use, I export the contents of the Audit table to the new database to make sure all of the audit messages are retained.

The problem here is that I somehow ran the export job 5 times, and now the database is well over 1TB. I've managed to delete all duplicate data from the table, but the file itself is still well over 1TB.

Does anyone have a suggestion on how to clean up this mess? Is the Shrink option the best route, or is there something better?

jscs
  • 63,694
  • 13
  • 151
  • 195
Christopher Cass
  • 817
  • 4
  • 19
  • 31
  • 1
    Without any info about what kind of database it is nobody would be able to help you. – Tometzky Feb 07 '19 at 19:04
  • Well, I guess that answers that question :) I don't know enough about this to say what kind of database it is... – Christopher Cass Feb 07 '19 at 19:05
  • @ChristopherCass - I think what he means is, is this SQL Server? MySQL? MSAccess? What application are you using? – Johnny Bones Feb 07 '19 at 19:12
  • 1
    Ah, it is SQL Server. 2012 – Christopher Cass Feb 07 '19 at 19:15
  • I find it odd that 1) you don't want to keep previous audits local. What's the point of them being local in the first place if you are going to offload them later? Why not set up an audit server with CDC or whatever? 2) This is a migration for a *customer* With that, [I would stop and read this including the links contained within](https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/) – S3S Feb 07 '19 at 19:47

1 Answers1

1

Here is what I'd do:

  • Use DBCC SHRINKFILE to shrink the individual files rather than the whole DB

  • Use ALTER INDEX REORGANIZE to reduce the index fragmentation (i.e. ALTER INDEX MyIndex ON dbo.MyTable REORGANIZE;). otherwise, you run the risk of blowing up your DB while you try to shrink your indexes

  • Last, I would shrink the Log file

Doing this in this order should give you the best results with the smallest impact

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117