DBCC CHECKDB and DBCC Shrinkfile in Log shipping

Chaitanya Kiran 616 Reputation points
2022-09-06T13:38:05.707+00:00

I have log shipping set up. If I run DBCC CHECKDB on primary server, will it also run on secondary server? How about running DBCC Shrinkfile on primary, will it also run on secondary?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,069 questions
{count} votes

Accepted answer
  1. PandaPan-MSFT 1,861 Reputation points
    2022-09-08T07:56:59.95+00:00

    Hi @Chaitanya Kiran
    I did the test on my own,and If you shrink the priminary databse, it is passed to the secondary , and it does not break log shipping. But only if the secondary database is read-only. If the secondary database has been configured for Norecovery mode, the changes will not be delivered to the secondary repository because nothing can be done in NORECOVERY mode
    And @Erland Sommarskog thanks so much for your advice, and I will do better in the future.
    Best regards
    Jong
    238956-image.png238938-image.png


3 additional answers

Sort by: Most helpful
  1. Olaf Helper 27,136 Reputation points
    2022-09-06T14:10:34.84+00:00

    will it also run on secondary server?

    No, why should it? That are no DML statements logged to Log file, that are "only" administrative operations.

    1 person found this answer helpful.
    No comments

  2. Erland Sommarskog 72,236 Reputation points MVP
    2022-09-06T21:05:30.923+00:00

    DBCC CHECKDB without a REPAIR option is a read operation, so it wil obviously not run on the secondary. But if you use the REPAIR option, that may be reflected. I say "may be", because I don't know if the REPAIR work is logged. I guess it has to be logged, so it can be rolled back. But if there is corruption on the primary, that corruption may not be on the secondary, so the repair may not be applicable.

    When it comes to DBCC SHRINKFILE, I would expected that to be reflect on the secondary.

    1 person found this answer helpful.
    No comments

  3. PandaPan-MSFT 1,861 Reputation points
    2022-09-07T02:30:01.193+00:00

    Hi @Chaitanya Kiran ,
    I'm agree with @Olaf Helper ,here are the functions of DBCC CHECKDB:
    Check some key system tables
    Run DBCC CHECKALLOC on the database
    Run DBCC CHECKCATALOG on the database
    Validate the contents of each indexed view in the database
    Validate service broker data in the database
    The function of DBCC Shrinkfile can be seen in the link:https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver16
    best regards
    Jong