What are the challenges you face when working across database platforms? Take the survey
Options

Database Data Size confusion?

essamughalessamughal Posts: 35
edited October 19, 2006 8:59PM in SQL Backup Previous Versions
Hi;

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

Comments

  • Options
    peteypetey Posts: 2,358 New member
    What is the original size of my Database?
    The database size (data + log) is 10 GB, and the data in it occupies ~2 GB. Perhaps an example would illustrate this better (you may need to change the location of the backup files):
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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
  • Options
    peteypetey Posts: 2,358 New member
    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
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.