Microsoft Sql Server Managment studio backup size goes negative

Question

The problem is that I need to explain the different sizes of backups that are being made of a database in a plant. Sometimes the difference between the sizes is in negative, even though that there is no data being deleted from the system.

 Datum  Backupfile-file         Size KB     Diff    

 6/1/10 backup201006010100.bak  3355914          

 7/1/10 backup201007010100.bak  4333367     977453  
 7/2/10 backup201007020100.bak  4355963     22596   
 7/3/10 backup201007030100.bak  4380896     24933   
 7/4/10 backup201007040100.bak  4380404     -492    

 8/1/10 backup201008010100.bak  4507775     1151861 
 8/2/10 backup201008020100.bak  4507777     2   
 8/3/10 backup201008030100.bak  4532492     24715   
 8/4/10 backup201008040100.bak  4584028     51536   

On 7/3/10 and 8/1/10 there was no production. On other days the production is mostly consistent hence the database is expected to have a pretty much linear increase in size, but how is it that the size goes to negative.

In the maintenance plan the the tasks are: Backup Database Task (Type: Full Append Existing) -> Shrink Database (Leave 10% free space)

Answer 1

The last step of the backup process is to append data from the log that reflects any changes made to the database during the backup process, this could account for the difference you are seeing.

Answer 2

SQL Server have 2 step process of storing your data. First, your data goes into log file, and it not only data that you inserted, but also the whole list of operations SQL performed on your data. So, if something wrong happens, SQL can 'replay' your transactions.

At some point CHECKPOINT happens, ans data gets written into data file. Log files have a tendency to grow and shrink.

During BACKUP, SQL will write data and log files EXACTLY as they look at the point of BACKUP. That's why you can see that difference in size.