INF: Log Space Used Appears to Grow After Restoring from Backup

• Microsoft SQL Server version 7.0

SUMMARY

When applying transaction log backups from a primary server to an identically configured backup server, the amount of space reported as used in the transaction log appears to grow. Over time, the space used by the log will appear to remain at approximately 100 percent, no matter how small the log space used on the primary. This is expected behavior with SQL Server 7.0 and differs from behavior on previous versions of SQL Server.

MORE INFORMATION

A standby (warm backup) server is a second server that can be brought online in the event of failure of the primary production server. The standby server contains a copy of the databases on the primary server. This copy is maintained by initially backing up the databases on the primary and restoring them on the standby. Periodically, transaction log backups from the databases on the primary server are applied on the standby to ensure that the standby remains synchronized with the primary server.

Restoring a transaction log WITH STANDBY allows a database to be brought up for read-only access between transaction log restores and can be used with either warm backup server situations or special recovery situations in which it is useful to check the database between log restores.

If you monitor (with DBCC SQLPERF (LOGSPACE)) the transaction log space used on a standby server after each RESTORE WITH STANDBY of a transaction log, you will see the percentage of the log used apparently increasing. The percentage may appear to approach 100 percent usage and remain there. However, no matter how many logs are restored, the transaction log will not fill up, even if the transaction log is set at a fixed size with no growth allowed, assuming that drives have some unused space and are not at full capacity.

SQL Server 7.0 does not reclaim unused log space as it becomes available, but reclaims it as needed when a transaction log is applied to the standby server. This is why you cannot fill up a transaction log on a standby server with backups from the primary server (assuming the size of the transaction logs on both servers are the same). In the event of a failover to the standby server, all unused log space will be reclaimed when the database is recovered.

To guarantee failover, you should set a maximum size on database and log files on the primary server so that disk capacity will not be exhausted. If the secondary server is configured identically to the primary, there should be a cushion of a few percent between the maximum size of the database and log files and the maximum size of a full disk. During the UNDO phase of recovery, SQL Server could require more space (either in the data or the log) than was needed during a normal operation. In normal operation where transactions are committing concurrently, SQL Server will reuse space as it is freed, optimistically assuming that the transaction will commit. If the transaction did not complete, during recovery SQL Server will need to roll back the operation that freed up space and more space would be temporarily used until the recovery operation completes.

Although SQL Server respects the set maximum size during normal operations, this is ignored during recovery. SQL Server will use as much space as needed to recover the database, limited by available disk space on the drive. The additional space cushion required for recovery situations should not need to be any more than a few percent of the total database size, but is best determined by the scope of concurrent operations. In addition, if you are using WITH STANDBY when restoring logs on the secondary server, you must provide space for the undo file. Since the undo file holds pre-rollback images of the pages modified by a rollback, the required size is determined by the scope of the transactions being rolled back. If the secondary server is not recovered between log restores, space for the undo file need not be provided.

Additional query words: dump load 1105 secondary log shipping

Keywords : SSrvTran_SQL kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbinfo