Compaq ProLiant 1000 Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 25

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 25 require that a DUMP TRANSACTION WITH NO_LOG be performed followed by a full database dump32 the Event Manager can be asked to log a SQL Server event in response to a Performance Monitor Alert. As described above, the Performance Monitor can be used to monitor log space usage and generate an alert. This performance monitor alert can either run a batch file to dump the log itself, or use the xp_logevent extended stored procedure to log a SQL Server event, which in turn can invoke a SQL Server alert that fires off its own task to dump the log. Either way, using Performance Monitor provides a more 'proactive' strategy to transaction log management while not requiring that you know beforehand how often the log dumps need to occur. The transaction log dump size can be estimated using the SQL Enterprise Manager utility, using the Manage, Databases, and Edit Database sequence of options. The output will include total log space and log space available in MB (subtract to get log dump size). You can also use the DBCC checktable(syslogs) command. The output of this command includes the total number of data pages in the syslogs table and the space used on the log segment in megabytes. Use the number of megabytes used on the log segment, i.e. the number of data pages in the syslogs table multiplied by the page size of 2048 bytes, to approximate the size of the transaction log dump. Always allow for an extra 5% of the result to compensate for inaccuracy of the estimate. Alternatively, you can use the DBCC sqlperf(logspace) command. Also, the DBCC updateusage command can be run beforehand in order to correct possible inaccuracies in space usage reports. The database dump size can be estimated with the SQL Enterprise Manager utility, using the Manage, Databases, and Edit Database sequence of options. Enterprise Manager will give the total data space and estimate the database space available in MB (subtract to give dump size). The sp_spaceused stored procedure can also be used to report reserved space, data space used, index space used, and unused reserve space, all in KB. Add data space used and index space used to the log space used (see above). The dump database command causes SQL Server to include a dump of the transaction log, to recover transactions that were in progress at the time of the dump. Always include the size of the transaction log in your estimate. Allow for an extra 5% of the result to compensate for inaccuracy of the estimate. Also, the DBCC updateusage command can be run beforehand in order to correct possible inaccuracies in space usage reports. Although the methods described above can provide a good estimate of the dump size, they tend to report 'rounded' numbers. The most accurate method to get the size of the dump image is to actually perform a dump to disk or tape, then to get the size of the file from a command prompt (not from File Manager) or from the SQL Enterprise Manager Backup/Restore window. This is the method used to measure the size of the test database used in later sections of this paper, in order to calculate backup throughput. Another, also accurate method of determining the dump image size is to read the number 32 Depending on how full the transaction log has become, SQL Server may not be able to dump the log or even to do a normal truncate only. In this case a 'non-logged truncation' must be done which requires a subsequent database dump to preserve the usability of future transaction log dumps. For more information on this subject, refer to the SQL Server Administrator's Companion. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797