SQL Server TSQL-Scripts

Here are som great scripts to manage SQL-server log

See why you cannot shrink log files;

select log_reuse_wait_desc,* from sys.databases as a1
--where name like 'lois%' order by a1.log_reuse_wait_desc

See always on database status;

Select last_commit_time, synchronization_state_desc,* from sys.dm_hadr_database_replica_states where database_id = 5

See how much data log files are using;

DBCC SQLPERF(LOGSPACE);

Shrink log file on Always On Cluster when they get tooo large;

BACKUP LOG Database1 TO DISK='NUL:'

DBCC SHRINKFILE (Database1_log, EMPTYFILE)

DBCC SHRINKFILE (Database1_log, 2048);