First check what is causing your database to not shrink by running:
If you are blocked by a transaction, find which one with:
Kill the transaction and shrink your db.
If the cause of the blocking is 'REPLICATION' and you are sure that your replicas are in sync, you might need to reset the status of replicated transactions. To see the status of what the database still think needs to be replicated use:
You can reset this by first turning the Reader agent off (I usually just turn the whole SQL Server Agent off), and then run that query on the database for which you want to fix the replication issue:
Exec sp_replflush
Close the connection where you executed that query and restart SQL Server Agent (or just the Reader Agent). You should be all set to shrink your db now.
SELECT name, log_reuse_wait_desc FROM sys.DATABASESIf you are blocked by a transaction, find which one with:
DBCC OPENTRANKill the transaction and shrink your db.
If the cause of the blocking is 'REPLICATION' and you are sure that your replicas are in sync, you might need to reset the status of replicated transactions. To see the status of what the database still think needs to be replicated use:
DBCC loginfoYou can reset this by first turning the Reader agent off (I usually just turn the whole SQL Server Agent off), and then run that query on the database for which you want to fix the replication issue:
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time= 0, @reset = 1Exec sp_replflush
Close the connection where you executed that query and restart SQL Server Agent (or just the Reader Agent). You should be all set to shrink your db now.
No comments:
Post a Comment