Database Data Size confusion?

Comments

• petey Redgate › Posts: 2,358 New member

  October 18, 2006 9:51PM

  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

  0 · Share on Twitter
• essamughal Posts: 35

  October 19, 2006 1:39PM

  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

  0 · Share on Twitter
• petey Redgate › Posts: 2,358 New member

  October 19, 2006 8:59PM

  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

  0 · Share on Twitter