MSDB log file max size reset after SQL Patches

I have encountered this several times already after different Cumulative Updates/Service Packs…

We set fixed maximum size for SQL Server databases and update the max size when required. But after installing Service Pack and Cumulative Updates the maximum size is reset to 2,097,152 MB for the MSDB log file:
max size - msdb log

I am going to submit this issue to the Microsoft Connect (hopefully it still works and active) or check if somebody else had similar issues. As this happens only with one system database and only with one file I would suspect that this is a bug.

This is just something to watch out and remember to review after SQL Server patching if you use fixed maximum files sizes.

Share

Does compression enable checksum for the backups automatically?

I am not sure if this is a bug or an issue with BOL documentation, but BOL says here http://msdn.microsoft.com/en-us/library/ms186865.aspx: “(CHECKSUM) Specifies that the backup operation will verify each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup. This is the default behavior for a compressed backup”.

So, let’s run some tests.

 

Create three backups: backup a database without compression, then backup a database with compression and finally backup it with CHECKSUM:

BACKUP DATABASE msdb TO DISK = ‘K:\BACKUPS\msdb_compressed.bak’

WITH COMPRESSION, COPY_ONLY

BACKUP DATABASE msdb TO DISK = ‘K:\BACKUPS\msdb_NOT_compressed.bak’

WITH COPY_ONLY

BACKUP DATABASE msdb TO DISK = ‘K:\BACKUPS\msdb_CHECKSUM.bak’

WITH CHECKSUM, COPY_ONLY

Run RESTORE HEADERONLY for the backups created without CHECKSUM:

RESTORE HEADERONLY FROM DISK = ‘K:\BACKUPS\msdb_compressed.bak’

RESTORE HEADERONLY FROM DISK = ‘K:\BACKUPS\msdb_NOT_compressed.bak’

Actual results: Both results have “HasBackupChecksums” column equal to 0.

Expected Results: Expecting “HasBackupChecksums” column to have value equal to 1 for the compressed backup (based on documentation).

 

Run RESTORE HEADERONLY for the backup created with CHECKSUM:

 

RESTORE HEADERONLY FROM DISK = ‘K:\BACKUPS\msdb_CHECKSUM.bak’

“HasBackupChecksums” column is equal to 1 as expected.

It does not look that CHECKSUM enabled automatically when COMPRESSION is enabled for the backup.

 

So, the questions are:

  • Is this the documentation issue?
  • Does compression really automatically enables checksum for the backups?

I have sent these questions to Microsoft, so if you want to know the answers as well – come back later. I will publish thier response as soon as I get it.

Share