Sql-server – SQL Server database has very small backup

sql-server-2008

I have a database of 216 GB. 75% is used by data, the rest is used by index and log. When I check free space in the database files I get values around 300 MB. The database is split over 3 data files. When I take a full backup without compression I get a backup of 38 GB.

Can anyone explain this? Can I recover any disk space from the database?

Update: If I try to restore the backup it requires over 200 GB of diskspace.

Update2: I got these values by running:

select Name,
(convert(float,size)) * (8192.0/1048576) File_Size,
(convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576) MB_Used,
((convert(float,size)) * (8192.0/1048576) - (convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576)) MB_Free
from sysfiles
order by fileproperty(name,'IsLogFile')

The result is:

QASDATA1    74124   73383,75    740,25
QASDATA2    69548   69114   434
QASDATA3    72288   71972,6875  315,3125
QASLOG1 374,8203125 112,1015625 262,71875

Update 3: Here is some more information about the backup itself, the query is below in the responses:

QAS x:\sql\SQL42\QAS\QAS_log_20121030.BAK   51 MB   160 MB  7 Seconds   2012-10-30 01:00:00.000 16269000022730200001    16271000002874600001    Transaction Log SQL42   FULL
QAS x:\sql\SQL42\QAS\QAS_data_20121025.BAK  38820 MB    224903 MB   3458 Seconds    2012-10-25 09:55:16.000 16265000012013400138    16265000012581700001    Full    SQL42   FULL

Best Answer

As Paul Randall stated in his MCM Internals on Backup/Restore for SQL Server, the backup file does not require the empty space in the database to be in the backup, but it DOES require it when restoring the backup. The backup file basically puts pointers to where the empty space needs to repopulate when it rebuilds it during the restore process.

Database compression works differently than removing empty space from backups. It uses something closer to a winzip algorithm. If you were to winzip/winrar/7zip with maximum compression your backup, you would get very similar space usage as SQL Server native compression. One of the benefits of using it native in SQL Server is it requires a lot less space to write to disk thus it's faster assuming your CPU isn't pegged.

Mr. Denny is also correct, so that could be part of it hence why I voted it back up.

Paul Randal's MCM video list including backup/recovery as mentioned: http://www.sqlskills.com/T_MCMVideos.asp

EDIT: After re-reading your answer there's some discrepancies I want to clarify, but the information stated above is still relevant.

You have a database that when you right click on the DB, properties, and space used, you see: Free Space: 300MB SP_HELPDB shows: 216GB

run: dbcc sqlperf('logspace'). What do you get back for the log life related to this DB? Please post your findings.

Please validate and get back to us. 

/*
10/29/2012 Edit after posting findings & using block comment code to drive Buck Woody crazy:


Next, please run this, it will tell us everything about your backup right when SQL Server took it.  This modified script originally from Pinal will help us validate if something after the backup happened to the file, or if SQL Server really just spit out a small backup:
*/

USE [DatabaseName]

GO

SELECT TOP 100

s.database_name,

m.physical_device_name,

CAST(CAST(s.compressed_backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS CompBkSize,

CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,

CAST(DATEDIFF(second, s.backup_start_date,

s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,

s.backup_start_date,

CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,

CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,

CASE s.[type]

WHEN 'D' THEN 'Full'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Transaction Log'

END AS BackupType,

s.server_name,

s.recovery_model

FROM msdb.dbo.backupset s

INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id

WHERE s.database_name = DB_NAME() -- Remove this line for all the database

ORDER BY s.backup_start_date DESC, s.backup_finish_date

GO

Edit after final results were posted: As suspected, your backups have compression enabled. As you can see in the query, Compressed backup size shows 38GB where as just 'backup size' shows 228GB. Review your backup scripts, you will find 'WITH COMPRESSION' in it.

Remember the factors that affect backup size: -Transaction Log files are backed up along the SQL Server backup file. This is for redo/undo operations during the recovery portion.

-Data pages, GAM, SGAM, etc. are to the backup file. Empty space in the database is removed and only pointers are placed, thus if you have lots of free space your backup will remain small. However it will be required when you restore.

-Compression plays a huge factory typically in SQL Server databases. One of the biggest contributing factors.

-Breaking up a backup file into smaller multiple files obviously will also affect size.

Let us know if you have any questions but your original question regarding the small backup size is resolved.

Related Question