SQL Server: Error: 3041, Severity: 16, State: 1

There are probably a good number of reasons this error shows up in your SQL Server log, along with the following message:

Source: Backup
Message: BACKUP failed to complete the command BACKUP LOG DATABASE_NAME. Check the backup application log for detailed messages.

In my case, the recovery model was set to SIMPLE and the DB maintenance plan was set to backup all user databases, which included all databases set to SIMPLE. I have my suspicions as to why those production databases were set to SIMPLE but FULL is best in most cases anyway, so I set all recovery models to FULL, and manually backed up all databases. To see a list of all of your databases and their recovery models, run the following query:

select [name],databasepropertyex([name],'Recovery') as [Recovery]
from master.dbo.sysdatabases
order by [name]

Note that the following system databases are set to SIMPLE by default: master, tempdb and msdb.

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in SQL Server
One comment on “SQL Server: Error: 3041, Severity: 16, State: 1
  1. BlueCollarCritic says:

    I know this may sound counter-logical at first but there are certain scenarios which are not less common then you might think where it is better to have your DB’s Recovery Model set to SIMPLE.

    I use to work for a software company that dealt with the Property Management industry. Amongst our many clients we had a sizeable number who used our software and had no IT staff to speak of. Either the person with the most computer skills was the IT admin by default or they had an on call contracted IT person who came in only when needed.

    For smaller operations we dealt with that had no in house IT statff or who did have an IT person but they had no SQL Server knowledge it was better and safer as far as data backups to set them up with a recovery model of SIMPLE and get their IT person to setup a backup job (daily) in SQL Server to create a bak file which there backup sofwtare could include in whatever else it was already backing up.

    The resaon that SIMPLE is better then FULL or even BULK_LOGGED for these folks was because of the risk that comes with handling backups and more importantly restores of DB’s set to FULL or BULK_LOGGED, namely the handling of the log files seperate from the core DB.

    Clients could grasp the concept of a DB file and getting a backup (a bak file) of it and making sure that backup got onto a tape or some other backup media each day. What they would not be able to do is stay on top of getting log file backups let along trying to restore them shoudl something go worng.

    In order to ensure these clients woudl have at a bare minimum a daily backup of ther data should something go worng we would set them up with the SIMPLE recoveryu model and a daily SQL Job to create the bak file and they woudl have tehir IT person handle getting that bak file included in the backup sofwtare they used for tehir file server and email system and so on.

    For these smaller operations it was much safer to go with a SIMPLE DB with a daily backup and the client would understand that if soimething went worng they would have to start over from where ever the last days backup left off at. In these scenarios you traded the ability to restore a DB from just an hour ago or less for ease of maintenace and better assurance that you were getting at least 1 good backup each day that you could actuall restore.

    For the larger clients we did go with th traditional mindset of using FULL or BULK_LOGGED.

Leave a Reply

Your email address will not be published. Required fields are marked *