Mirror, Synchronized / Restoring...
is the typical state for the mirror instance.
You can determine why the log file is growing by issuing the following command in a SQL Server Management Studio query window:
SELECT name, LOG_REUSE_WAIT_DESC
FROM sys.databases
WHERE name='your_database_here';
This will show several possible choices, such as:
NOTHING
CHECKPOINT
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
OTHER_TRANSIENT
You may need to perform at least one transaction log backup, among other actions depending on the LOG_REUSE_WAIT_DESCRIPTION
shown in the query above. For instance, there may be a long-running transaction that is forcing the log files to not be re-used.
Best thing to do is to collect what caused the log file to grow by setting up an alert.
You can use sp_whoisactive to run and log into the table (as DennisT suggested) or dmv's like sys.dm_tran_active_transactions
and sys.dm_tran_database_transactions
-- Find Log space usage
-- http://stackoverflow.com/a/5085179/1387418
select
SessionTrans.session_id as [SPID],
enlist_count as [Active Requests],
ActiveTrans.transaction_id as [ID],
ActiveTrans.name as [Name],
ActiveTrans.transaction_begin_time as [Start Time],
case transaction_type
when 1 then 'Read/Write'
when 2 then 'Read-Only'
when 3 then 'System'
when 4 then 'Distributed'
else 'Unknown - ' + convert(varchar(20), transaction_type)
end as [Transaction Type],
case transaction_state
when 0 then 'Uninitialized'
when 1 then 'Not Yet Started'
when 2 then 'Active'
when 3 then 'Ended (Read-Only)'
when 4 then 'Committing'
when 5 then 'Prepared'
when 6 then 'Committed'
when 7 then 'Rolling Back'
when 8 then 'Rolled Back'
else 'Unknown - ' + convert(varchar(20), transaction_state)
end as 'State',
case dtc_state
when 0 then NULL
when 1 then 'Active'
when 2 then 'Prepared'
when 3 then 'Committed'
when 4 then 'Aborted'
when 5 then 'Recovered'
else 'Unknown - ' + convert(varchar(20), dtc_state)
end as 'Distributed State',
DB.Name as 'Database',
database_transaction_begin_time as [DB Begin Time],
case database_transaction_type
when 1 then 'Read/Write'
when 2 then 'Read-Only'
when 3 then 'System'
else 'Unknown - ' + convert(varchar(20), database_transaction_type)
end as 'DB Type',
case database_transaction_state
when 1 then 'Uninitialized'
when 3 then 'No Log Records'
when 4 then 'Log Records'
when 5 then 'Prepared'
when 10 then 'Committed'
when 11 then 'Rolled Back'
when 12 then 'Committing'
else 'Unknown - ' + convert(varchar(20), database_transaction_state)
end as 'DB State',
database_transaction_log_record_count as [Log Records],
database_transaction_log_bytes_used / 1024 as [Log KB Used],
database_transaction_log_bytes_reserved / 1024 as [Log KB Reserved],
database_transaction_log_bytes_used_system / 1024 as [Log KB Used (System)],
database_transaction_log_bytes_reserved_system / 1024 as [Log KB Reserved (System)],
database_transaction_replicate_record_count as [Replication Records],
command as [Command Type],
total_elapsed_time as [Elapsed Time],
cpu_time as [CPU Time],
wait_type as [Wait Type],
wait_time as [Wait Time],
wait_resource as [Wait Resource],
reads as [Reads],
logical_reads as [Logical Reads],
writes as [Writes],
SessionTrans.open_transaction_count as [Open Transactions],
open_resultset_count as [Open Result Sets],
row_count as [Rows Returned],
nest_level as [Nest Level],
granted_query_memory as [Query Memory],
SUBSTRING(SQLText.text,ExecReqs.statement_start_offset/2,(CASE WHEN ExecReqs.statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), SQLText.text)) * 2 ELSE ExecReqs.statement_end_offset end - ExecReqs.statement_start_offset)/2) AS query_text
from
sys.dm_tran_active_transactions ActiveTrans (nolock)
inner join sys.dm_tran_database_transactions DBTrans (nolock)
on DBTrans.transaction_id = ActiveTrans.transaction_id
inner join sys.databases DB (nolock)
on DB.database_id = DBTrans.database_id
left join sys.dm_tran_session_transactions SessionTrans (nolock)
on SessionTrans.transaction_id = ActiveTrans.transaction_id
left join sys.dm_exec_requests ExecReqs (nolock)
on ExecReqs.session_id = SessionTrans.session_id
and ExecReqs.transaction_id = SessionTrans.transaction_id
outer apply sys.dm_exec_sql_text(ExecReqs.sql_handle) AS SQLText
where SessionTrans.session_id is not null -- comment this out to see SQL Server internal processes
More reference :
Best Answer
No, it does not reduce the size of the log file on disk. The physical log file is made up of virtual log files (VLFs) and each VLF represents a portion of the transaction log. When a VLF is included in a transaction log backup (because there are no active transactions using that VLF) that VLF is marked inactive and can be overwritten. These inactive VLFs are considered free space within the log file because they can be overwritten at any time by an active transaction if required.
It is possible to shrink the physical file and reclaim the disk space, however, it is generally not recommended as the log file most likely requires that space for large operations, such as index maintenance, and regrowing the log file is a performance bottleneck.
Overview of Transaction Log in SQL Server
Index maintenance operations (REBUILD, REORGANIZE etc) are logged in the transaction log, even ONLINE operations that don't lock the underlying table. So the vendor's DBA is correct that rebuilding indexes can cause log growth, but it is not due to table locking, it is because it is a logged operation.
Check out these links for additional information on space utilisation during index DDL:
Transaction Log Disk Space
Disk Space Requirements for Index DDL
It depends on a number of factors, how many rows in the underlying table/index, rate of data modifications, are you using trace flag 2371 and auto-update statistics on your databases etc?
Also, how frequently are you rebuilding your indexes, because the statistics related to your indexes will be getting updated at the same time, and if you're tables have clustered indexes then your column statistics should also be getting updated.
If you're using Auto-Update Stats, then you can read about the threshold for updating stats here. There's also some useful information in the answers to a similar question here.