grajee on Tue, 12 Jan 2016 17:42:26
We have a database that is part of the SQLServer 2012 (11.0.5613.0) AAG.
We restored this database from a backup on "2015-12-03 20:21:28.123" and reinitiated the AAG process and it is working fine. The issue seems to be with the active/inactive transactions of the log and since this database is part of the AAG I was expecting the log size to be very small since it gets backed up regularly But it is not. The problem seems to be that sqlserver is not removing the inactive transactions. The datafile is 86GB but the logfile is 553GB.
Output of “DBCC SQLPERF(LOGSPACE)” is Database: INSDB, LogSize: 540577.6, Log Space Used: 96.72788, Status: 0
Output of “DBCC LOGINFO” returns 1099 rows with the FileSize SUM being 553GB with only rows having status = 0 and the rest having status = 2. Check the uploaded Image.
Output of “DBCC OPENTRAN” returns “No active open transactions”
I tried “Select * FROM sys.fn_dblog(NULL,NULL)” and it returns lot of details and all of them are from the time the database was restored from the backup. Check the uploaded Image. The entries in the rows are after the restore was done (2015-12-03 20:21:28.123)
Essentially it looks like SQLServer is not removing the Inactive/Dead Transactions.
I’m aware that the easiest way of addressing this would be to change the recovery mode to simple and truncating the transaction log but we would still want to know why this is happening and how to address it.
TiborK on Tue, 12 Jan 2016 18:23:09
Check out sys.databases, the log_reuse_wait_desc column for the database in question.
Tom Phillips on Tue, 12 Jan 2016 18:45:39
Do not confuse the physical size of the log file with log reusability.
The recovery mode controls the log space reusability.
The physical size of the log file does not shrink unless you manually shrink it. It is that big because at some point it needed to be that big.
grajee on Tue, 12 Jan 2016 20:02:11
Thanks for the update. The transaction Log backup on this server (Stage environment) was not happening from the tape system. The log_reuse_wait_desc column helped me in the right direction.