In full recovery mode, SQL Server logs every thing. If regular backups are not taken, at one point you might get an error as below when max log size is reached.. Regular backups truncate the log files.
Error: 9002, Severity: 17, State: 2
The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
In this case you are interested in following facts-
1. What exactly is the reason why the log space is not being re-used?
2. What is the size of active log before and after the log file is backed up, and/or log file size is increased? Remember active log size is different (<=) from physical file size.
1. What exactly is the reason why the log space is not being re-used?
The SQL error above itself hints which catalog view to use to find the reason.
select log_reuse_wait_desc from sys.databases
where name = <databasename>
You might get one of the below values. The descriptions are self explanatory. For example, if the reason is ACTIVE_TRANSACTION, that means, a non-committed transaction is holding the log file from being re-used.
2. How big is active transaction space in the log after log backup and/or increase?
One might right away do following query to find the log file size after the log backup.
USE <DB_NAME>
GO
select size,max_size from sys.database_files
where type_desc = 'LOG'
(Screen shot - 2)
OR
DBCC SQLPERF ('LOGSPACE');
GO
(Screen shot- 3)
The "log size" column is allocated space for log file
Log Space Used % is percent of log space used out of allocated space.
so Total space, = 5 MB
% Used = 5.90% OR .295 MB
Total Unused fee space = 5 - .295 = 4.795 MB
This number almost matches with the screen schot taken from Screen Shot - 2 for available free space.
Error: 9002, Severity: 17, State: 2
The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
In this case you are interested in following facts-
1. What exactly is the reason why the log space is not being re-used?
2. What is the size of active log before and after the log file is backed up, and/or log file size is increased? Remember active log size is different (<=) from physical file size.
1. What exactly is the reason why the log space is not being re-used?
The SQL error above itself hints which catalog view to use to find the reason.
select log_reuse_wait_desc from sys.databases
where name = <databasename>
You might get one of the below values. The descriptions are self explanatory. For example, if the reason is ACTIVE_TRANSACTION, that means, a non-committed transaction is holding the log file from being re-used.
- CHECKPOINT
- LOG_BACKUP
- ACTIVE_TRANSACTION
- DATABASE_MIRRORING
- REPLICATION
- DATABASE_SNAPSHOT_CREATION
- LOG_SCAN
- OTHER_TRANSIENT
2. How big is active transaction space in the log after log backup and/or increase?
One might right away do following query to find the log file size after the log backup.
USE <DB_NAME>
GO
select size,max_size from sys.database_files
where type_desc = 'LOG'
WRONG... the "size" column name could be misleading. It actually stores the number of pages allocated to Log file. It does not store the active space used by the log file.
PROOF?
(Screen shot - 1)
The log file is specified to be 5 MB, but its 640 in the size column. 640 (Pages) * 8 (KB page each) = 5120 KB OR 5 MB.
OR
Rt. Click the db in SSMS and follow the below.
OR
DBCC SQLPERF ('LOGSPACE');
GO
The "log size" column is allocated space for log file
Log Space Used % is percent of log space used out of allocated space.
so Total space, = 5 MB
% Used = 5.90% OR .295 MB
Total Unused fee space = 5 - .295 = 4.795 MB
This number almost matches with the screen schot taken from Screen Shot - 2 for available free space.
