Home Forums SQL Server 2005 Administering DBCC CHECKDB - how use it in a maintenance plan ? RE: DBCC CHECKDB - how use it in a maintenance plan ?

  • I certainly reccommend running CHECKDB, but I think it should be run at the most convenient time of the day which may not be when you do a backup.

    At my old place the optimum time for backup was before the overnight batch work started, which put the backups under time pressure to complete. After the bulk of the batch work had finished at about 4AM, we had another window where we ran CHECKDB.

    I understand the issues of checking if your DB is good before doing a backup, but if we did find corruption there was always a good backup we could go back to and transaction log backups to bring us up to the point of failure if not the curent point in time. On good disk systems it is very unlikely you will get a corrupted database, so you need to balance the risks of doing or not doing a CHECKDB before a backup.

    Back in the 1990s I worked on mainframe DB2. There were good vendor backup utilities that included the equivalent of a CHECKDB in the backup processing. Because the I-O needed to check if the database was OK was combined with the I-O needed to run the backup, doing the DB check in-line with the bacukp only added about 5% to backup completion time. It would be great if one of the backup vendors for SQL Server could do this type of thing, it would be a major selling point!

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara