SQL Database size considerably less than actual size

[Andrew_Perry]

Hi,

I am slightly concerned that when backing up SQL databases with Backup Exec 10d (with the SQL Agent), the backup log reports a database size of 333MB, when the actual database size is 13GB.

At the time of the backup AOFO was NOT selected.

We also ran a full backup with SQL services stopped, and without using the SQL Agent and the total backup size was 40GB. When we changed the backup to the SQL Agent and the SQL resource rather than the actual file, the total size has reduced to 20GB, with some individual database sizes considerably less.

The backup job does not report any skipped files or problems and states that the backup run successfully.

What could be the problem?

Andrew Perry

[Keith_Langmead]

Sorry can you clarify those sizes as I'm a little confused, you're throwing a lot of numbers around but without a better descrption of what you're doing or backing up they don't mean much!

So you backup A database which comes out at 333MB in size, yet the "actual" database size is 13GB. According to what? Windows or SQL Server? Have you checking within Enterprise Manager to confirm how much space is ACTUALLY being used by the database, excluding any unused space which is left in case the database need to grow, of if it used to be bigger and hasn't since been shrunk.

When you refer to the 40GB and 20GB sizes are you still referring to the database you were at the beginning, or are you now referring to all the SQL databases as a whole?

[Andrew_Perry]

ok, i will expand further. I am backing up SQL Databases using the SQL Agent. I am selecting the SQL resource in the backup selection. The size is 13GB according to the proprties of the file in Windows. When I look in the selections when restoring, the size is 333MB

The database i mentioned is just 1 example, there are others but obviously they are different sizes.

When i refer to 20GB and 40GB, they are TOTAL backup sizes (as in the final byte count showing in the job monitor).

Basically if i do a "file" backup of C: and D: drives (D: being where the databases are), and turning off the SQL Services, the total backup job size is 40GB. If I add the SQL Resource to this selection, the total backup job size is 20GB. In other words, backing up databases by selecting the resources (and i have made sure that all databases are selected) is half the total size as a file backup without the agent.

I have just never come across this before in all the years of backups, so when it does and displays different backup sizes to what the properties of the file itself says, i worry :)

[Keith_Langmead]

Right, well I've just tried re-creating what you're seeing and I get the same results.

Database file size within Windows is 301MB, restore selections size for the database is 68MB.

If I select the MSSQL folder within Windows and the SQL server itself for backup, the backup size when SQL Server is running is 2.3GB, however when I stop the SQL Server service the backup size increases to 5GB.

The reason for all this is due to the way that SQL databases are physically larger than the data held within them, allowing them to expand if required when more data is added to them. When you do the backup using the SQL agent then it will backup the SQL data itself, not the physical files, so any empty space will not be included. In fact the physical files are automatically excluded from backup since it knows there's no point since they will be locked. When you do the backup without the SQL agent, then it will backup the physical files completely, including any empty space.

You can see this yourself if you go into SQL Enterprise Manager, and drill down to the database you're referring to. Select the database, and if it's not already displayed switch to the task pad view (right click the database, View, Taskpad), now down at the bottom of the task pad you'll see details of the space allocated, and the space actually used.

I went through all mine and totted up the totals, and found that while the total physical space used by the database files was 4.3GB, the amount of space actually containing data was only 1.6GB, and indeed the data size for the database listed above which was 68MB was correct.

So there you are, another reason for using the SQL agent to backup your databases!

Hope that all makes sense and answers your question.

[Andrew_Perry]

:) Thanks for that Keith. At least i can confidently perform the backups now, knowing that it is all there. I have since done a test restore, and it did actually restore the full size in bytes (in the restore log) so atleast i know it is working :)

Thanks again for the explanation.

Andrew