Log File Can only be Shrunk when :-
1) There are no Open Transactions in the Database
USE DB
DBCC OPENTRAN
2) When the Log File is not in use
DBCC LOGINFO
Verify that the File status <> 2 for all the Virtual log files.
Backup the Log file to mark the file status to inactive.
3) Check if the Database is involved in Transactional Replication
If your database is involved in Transactional replication and you see there are no undelivered transactions, then execute the below
USE [database]
go
exec sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1
go
DBCC Shrinkfile ('logfilename')
Increase in the Virtual Log files and Size will also lead to fail replication from delivering the transactions to the Subscriber.
...Happy SQLing
Thanks for Visiting and Sharing your Views
1) There are no Open Transactions in the Database
USE DB
DBCC OPENTRAN
2) When the Log File is not in use
DBCC LOGINFO
Verify that the File status <> 2 for all the Virtual log files.
Backup the Log file to mark the file status to inactive.
3) Check if the Database is involved in Transactional Replication
If your database is involved in Transactional replication and you see there are no undelivered transactions, then execute the below
USE [database]
go
exec sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1
go
DBCC Shrinkfile ('logfilename')
Increase in the Virtual Log files and Size will also lead to fail replication from delivering the transactions to the Subscriber.
...Happy SQLing
Thanks for Visiting and Sharing your Views
No comments:
Post a Comment