Important Command- Shrinking

Log not getting shrink

DBCC SQLPERF(LOGSPACE)

DBCC LOGINFO

DBCC opentran ('Test')

SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'Test'
---

My work around for the Replication that is blocking shrinking log file is:

1. Set DB Recovery Model to Simple
2. Take DB offline
3. Create backup of log file (just in case)
4. Delete log file
5. Bring DB online

---

BACKUP LOG <Databasename> TO DISK N'<path\database_log.ldf';
GO
DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS

or
DBCC SHRINKFILE (N'My DB_Log' , 10)


============
SELECT file_id ,* FROM sys.database_files
DBCC LOGINFO;
Here you can see if any virtual logs are in use by seeing if the status is 2 (in use), or 0 (free). When shrinking files,
empty virtual logs are physically removed starting at the end of the file until it hits the first used status.
This is why shrinking a transaction log file sometimes shrinks it part way but does not remove all free virtual logs.

If you notice a status 2's that occur after 0's, this is blocking the shrink from fully shrinking the file.
To get around this do another transaction log backup, and immediately run these commands, supplying the file_id found above,
and the size you would like your log file to be reduced to.

-- DBCC SHRINKFILE (file_id, LogSize_MB)
DBCC SHRINKFILE (2, 100);
DBCC LOGINFO;
===============
USE [db_name]

ALTER DATABASE [db_name] SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE([log_file_name]/log_file_number, wanted_size)

ALTER DATABASE [db_name] SET RECOVERY FULL WITH NO_WAIT