| |
 |
Run
the following script:
CREATE DATABASE sizetest
GO
USE sizetest
GO
CREATE TABLE data (col1 char(8000))
INSERT INTO data VALUES ('A')
INSERT INTO data SELECT * FROM data
INSERT INTO data SELECT * FROM data
INSERT INTO data SELECT * FROM data
INSERT INTO data SELECT * FROM data
INSERT INTO data SELECT * FROM data
INSERT INTO data SELECT * FROM data
INSERT INTO data SELECT * FROM data
INSERT INTO data SELECT * FROM data
INSERT INTO data SELECT * FROM data
GO
BACKUP DATABASE sizetest TO DISK = 'e:\temp\sizetest_1.bak'
DROP TABLE data
BACKUP DATABASE sizetest TO DISK =
'e:\temp\sizetest_2.bak' |
The first backup generates the following output:
Processed 664 pages for database 'sizetest', file
'sizetest' on file 2.
Processed 2 pages for database 'sizetest', file 'sizetest_log' on
file 2.
BACKUP DATABASE successfully processed 666 pages in 0.549 seconds
(9.924 MB/sec). |
while the second backup generates the following output:
Processed 152 pages for database 'sizetest', file
'sizetest' on file 2.
Processed 4 pages for database 'sizetest', file 'sizetest_log' on
file 2.
BACKUP DATABASE successfully processed 156 pages in 0.221 seconds
(5.782 MB/sec). |
Note that the second backup actually backed up less pages (156),
since we deleted all the data. The first backup file size is 7.7
MB, while the second is 2.6 MB. If you restore the database using
the second backup file, your database files would still require the
same amount of free space as the first backup (~ 9 MB), because
that was the size of the database files during the time of
backup.
If you want to reduce the size of the database files in this case,
you would need to run DBCC
SHRINKDATABASE,
DBCC
SHRINKFILE or set
the database to auto-shrink. Note that there are caveats to doing
this if the deletion and growth is normal for the database.
Constantly shrinking and growing the database file will cause it to
fragment on the disk, imposes a disk and processor overhead during
the shrinking process, and a disk overhead again during the growth
process.
Discuss or comment on this article on our Facebook group.
Discuss or comment on this article on our Facebook group.
Document history
| 6/27/2008 | Initial release. |
|
