Comments
3 comments
-
What is the original size of my Database?
SET NOCOUNT ON CREATE DATABASE mytest GO USE mytest GO CREATE TABLE table001 (col1 char(8000)) INSERT INTO table001 VALUES (REPLICATE('A', 8000)) DECLARE @count INT SET @count = 1 WHILE @count < 10 BEGIN INSERT INTO table001 SELECT * FROM table001 SET @count = @count + 1 END EXEC sp_helpdb mytest PRINT '' PRINT 'backup result for "full" database' PRINT '---------------------------------' BACKUP DATABASE mytest TO DISK = 'e:\temp\mytest_01.bak' WITH INIT, FORMAT PRINT '' PRINT '' /* my results: Processed 592 pages for database 'mytest', file 'mytest' on file 1. Processed 1 pages for database 'mytest', file 'mytest_log' on file 1. BACKUP DATABASE successfully processed 593 pages in 0.422 seconds (11.496 MB/sec). */ DROP TABLE table001 EXEC sp_helpdb mytest PRINT 'backup result for "empty" database' PRINT '---------------------------------' BACKUP DATABASE mytest TO DISK = 'e:\temp\mytest_02.bak' WITH INIT, FORMAT PRINT '' PRINT '' GO /* my results: Processed 80 pages for database 'mytest', file 'mytest' on file 1. Processed 1 pages for database 'mytest', file 'mytest_log' on file 1. BACKUP DATABASE successfully processed 81 pages in 0.404 seconds (1.624 MB/sec). */ USE master GO DROP DATABASE mytest GO SET NOCOUNT OFF
Notice the difference in the number of pages that are backed up, although the database size has not changed (look at the results of sp_helpdb). Thus, when SQL Backup reports the 'backup data size', it is reporting the amount of data that was actually backed up i.e. the number of pages x 8 KB. That would also have been the size of a native backup i.e. if you performed a normal backup using SQL Server, the backup file would be ~2 GB, and not anywhere near 10 GB.
The compression rate is also calculated using this number. Some tools might calculate the rate using 10 GB as the denominator, thus giving a compression rate of 95%. It's open to interpretation, I suppose. -
Hi;
Thanks for explaining me that in details.
1- Now, my question is that now is the data size in the database is 2 GB then where the 8 GB is used in my case. I can see 3 GB would be in Log but where the 5 GB is used.
2- How can I reduce that size of the database which seems useless since I have only 2 GB data in my database.
Thanks -
1 - The 5 GB isn't 'used'. It is allocated, but not used by any objects, probably because the object no longer exists.
2 - See DBCC SHRINKFILE, DBCC SHRINKDATABASE, but also see http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Add comment
Please sign in to leave a comment.
I have a database on production total size is 10 GB approx, MDF = 7 GB and LDF = 3 GB, when red-gate takes backup it sends email as
10/18/2006 1:36:49 AM: Backup data size : 2.076 GB
10/18/2006 1:36:49 AM: Compressed data size: 472.404 MB
10/18/2006 1:36:49 AM: Compression rate : 77.77%
Now, I am confused since I was thinking my database is 2.076 reading these emails, but originally it is taking 10 GB on production and I was misinterpreted by the email.
What is the original size of my Database?
One event I want to mention, there was some un-used table approximately 4 GB before two weekd and I deleted them on production before that the email from red-gate was like that:
10/4/2006 1:40:50 AM: Backup data size : 6.481 GB
10/4/2006 1:40:50 AM: Compressed data size: 1.271 GB
10/4/2006 1:40:50 AM: Compression rate : 80.38%
After doing that I was happy I reduced the size of the database by 4 GB, but the original size of the database is still 10 GB.
Please, can anyone explain to me? It is very important for me?
Thanks