sp_dump_info

The size is reported in units of KB, MB, or GB, as appropriate. The size reported may be slightly smaller than the actual size of the archive file (or files, if using multiple stripes), because the archive contains some additional information by way of the labels, header, trailer and runlist pages. sp_dump_info can also only assume that an uncompressed dump is done; if a compressed dump is done, the size of the archive will clearly be smaller than that reported by sp_dump_info.

You cannot use sp_dump_info:

Unless you allow incremental dumps of your database by using the allow incremental dumps parameter of sp_dboption.
If the database has not yet been fully dumped since you enabled incremental dumps for your database.

Syntax

sp_dump_info database_name

Parameters

database_name – is the name of the database.

Examples

Data and log size – Displays the size of data and log that the cumulative dump of the test database contains

sp_dump_info test
go
 Data     Log    Database percentage  Allocation threshold
 -------  -----  -------------------  --------------------
 4368 KB  2 KB                     2                    40
(return status = 0) (return status = 0)

The output indicates that if a cumulative dump were taken at this point in time, it would contain approximately 4,368KB of data and a single log page, which represents 2 percent of the total database size.

Compare this with the size if you performed a cumulative dump at this time:

dump database test cumulative to "c:/tmp/test.dmp" 
go
Backup Server: 4.171.1.1: The current value of 'reserved pages
threshold' is 85%.
Backup Server: 4.171.1.2: The current value of 'allocated pages
threshold' is 40%.
Backup Server session id is: 10. Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any
volume change request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'test122480F0EF ' section
number 1 mounted on disk file 'c:/tmp/test.dmp'
Backup Server: 4.188.1.1: Database test: 4328 kilobytes (3%) DUMPED.
Backup Server: 3.43.1.1: Dump phase number 1 completed.
Backup Server: 3.43.1.1: Dump phase number 2 completed.
Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.188.1.1: Database test: 4370 kilobytes (3%) 
DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database test).

The corresponding size of the archive is 4,487,168 bytes, or 2191 pages. This differs from the estimate given by sp_dump_info by 29 pages (14 KB), which is the result of 8 pages for the dump labels, 1 page for the dump header, 1 page for the dump trailer and 19 pages containing run lists. The size of the dump labels, header and trailer are independent of the numbers of pages dumped, while the number of pages used by run lists is dependent on the numbers of pages dumped.

Error message – Displays an error message when incremental dumps are not enabled on master

 sp_dump_info mydb
go
Msg 17154, Level 16, State 1:
Procedure 'sp_dump_info', Line 32:
Incremental dumps are not enabled in database mydb.
(return status = 1)

Usage

sp_dump_info fails if you do not allow incremental dumps, or you have not enabled incremental dumps for your database.

Permissions

Any user can execute sp_dump_info.