search

olahallengren / sql-server-maintenance-solution

SQL Server Maintenance Solution
https://ola.hallengren.com
MIT License
2.88k stars 738 forks source link

Recommend a @Verbose flag to minimize RAISERROR messages. #429

Open barryseymour opened 3 years ago

barryseymour commented 3 years ago

We have a nightly job that uses the DatabaseBackup procedure to back up numerous databases. If the job fails, we look to the Job History. But these backup jobs are full of status messages from RAISERROR - so many that the text is truncated and we get no indication of what the actual error was! (See the attached jpg)

Recommend a @Verbose bit flag to let the user turn these off once the procedure is productionized, so that only actual errors are included in the Job History log. If @Verbose = 0, only actual errors are displayed.

In the procedure, the code would be IF @Verbose = 1 RAISERROR...('%s',10,1,@StartMessage) WITH NOWAIT

Thanks!

Ola Hallengren Job History Error

olahallengren commented 3 years ago

I am aware of the issue with the Job History. The reason for that is that there is a limitation for how many characters that can be stored in the message column in msdb.dbo.sysjobhistory. https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobhistory-transact-sql?view=sql-server-ver15

I recommend that you use SQL Server Agent output - files to get the complete output.

The downside with a less verbose mode, is that it will be very difficult to troubleshoot it, when there is an error.

barryseymour commented 3 years ago

I'll research how to use SQL Server agent to output this to a file, thanks. But if @Verbose was an option you could turn it on when you did have to troubleshoot.

Thanks for all you do.

olahallengren commented 3 years ago

Try executing MaintenanceSolution.sql on a server and then have a look at the jobs (Job Properties / Steps / Edit / Advanced).

barryseymour commented 3 years ago

Very kind, thanks.

On Sat, Oct 24, 2020 at 11:53 AM Ola Hallengren notifications@github.com wrote:

Try executing MaintenanceSolution.sql on a server and then have a look at the jobs (Job Properties / Steps / Edit / Advanced).

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/olahallengren/sql-server-maintenance-solution/issues/429#issuecomment-716039034, or unsubscribe https://github.com/notifications/unsubscribe-auth/AFJSAHMUW3HVWN4AUSKBVD3SMMPEHANCNFSM4S3VEZIQ .