October 14, 2015 at 12:59 am
Hello experts,
can you please help out this error to resolve:
Maintenance - User DBs.Trans Log Backups - User DBs failed, please verify
[ Step #1 (Maintenance - User DBs.Trans Log Backups - User DBs) failed at (230001) with message
(Executed as user: ---------(server)\SYSTEM. ...0.5324.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:00:01 PM Progress: 2015-10-13 23:02:02.38 Source: {E178E7CA-3938-4583-BCD9-6168D37F0FFD}
Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2015-10-13 23:02:02.92
Source: Maintenance Cleanup Task Executing query "EXECUTE master.dbo.xp_delete_file 0,N'D:\SQLBackup".: 100% complete End Progress
Progress: 2015-10-13 23:02:03.88 Source: Back Up Database (Transaction Log)
Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\SQLBackup".: 10% complete End Progress Progress: 2015-10-13 23:02:03.88
Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\SQLBackup".: 20% complete End Progress
Progress: 2015 -10-13 23:02:03.88 Source: Back Up Database (Transaction Log) ... The package execution fa... The step failed.) ]
October 14, 2015 at 1:14 am
The error message does not say what went wrong, so I don't know whether anyone can help much without more information.
Did you check whether you have sufficient disk space for the backup?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 14, 2015 at 1:25 am
Get the job to log to a file to get the full error message dumped out so that we can see the actual error.
October 14, 2015 at 1:42 am
Hello team,
this is my entire error info from my scheduled job
Date10/13/2015 11:00:01 PM
LogJob History (Maintenance - User DBs.Trans Log Backups - User DBs)
Step ID1
ServerProdserver
Job NameMaintenance - User DBs.Trans Log Backups - User DBs
Step NameTrans Log Backups - User DBs
Duration00:03:23
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: Prodserver\SYSTEM. ...0.5324.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:00:01 PM Progress: 2015-10-13 23:02:02.38 Source: {E178E7CA-3938-4583-BCD9-6168D37F0FFD} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2015-10-13 23:02:02.92 Source: Maintenance Cleanup Task Executing query "EXECUTE master.dbo.xp_delete_file 0,N'D:\SQLBackup".: 100% complete End Progress Progress: 2015-10-13 23:02:03.88 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\SQLBackup".: 10% complete End Progress Progress: 2015-10-13 23:02:03.88 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\SQLBackup".: 20% complete End Progress Progress: 2015-10-13 23:02:03.88 Source: Back Up Database (Transaction Log) ... The package execution fa... The step failed.
October 14, 2015 at 1:48 am
Get the job / step to log to file to get the full error message written to file, this is only a snippet of the error due to the size of the column in msdb
October 14, 2015 at 9:23 am
Try to use this code to get the error messages
CREATE PROC [sys].[sp_readerrorlog](
@p1 INT = 0,
@p2 INT = NULL,
@p3 VARCHAR(255) = NULL,
@p4 VARCHAR(255) = NULL)
AS
BEGIN
IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1)
BEGIN
RAISERROR(15003,-1,-1, N'securityadmin')
RETURN (1)
END
IF (@p2 IS NULL)
EXEC sys.xp_readerrorlog @p1
ELSE
EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4
END
This procedure takes four parameters:
Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
Log file type: 1 or NULL = error log, 2 = SQL Agent log
Search string 1: String one you want to search for
Search string 2: String two you want to search for to further refine the result
@JayMunnangi
October 14, 2015 at 12:31 pm
With the native maintenance plan, if someone creates a new database without taking a FULL backup, then your TLog backup job will fail. It may not be the case here but doesnt hurt to double check.
Ola Halengren's maintenance script is way better.
October 14, 2015 at 12:48 pm
vedau (10/14/2015)
Ola Halengren's maintenance script is way better.
I couldn't agree more with this statement. The stock maintenance plans are better than nothing, but not much better. My advice would be to get rid of them completely and replace them with something better.
If you're so inclined, writing your own is an excellent way to learn about how things work under the hood. It takes some work, but if you have the time, it's an excellent way to learn.
October 15, 2015 at 2:31 pm
Try viewing the history of the maintenance plan itself....generally the info is there
------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply