February 19, 2010 at 7:59 am
Yesterday I mentioned that I noticed the former dba hadn't setup a maintenance plan to backup the system databases. I added a subplan, to his maintenance plan, and had it run. I scheduled it to run on Friday mornings at 4 AM. This morning I checked it to see how it went.
First I looked at the history in under Maintenance Plans, and everything looked fine. But I thought I would check the job under SQL Server Agent | Jobs, and that told a different story. (Or perhaps, more complete.) But even so, I just don't see what's wrong. I'd appreciate it you'd look it over and could tell me what's wrong, and if there's anything I can/should do about it. Below is the entry from the Log File Viewer:
Date2/19/2010 4:00:00 AM
LogJob History (Maintenance.weekly_full_system_backup)
Step ID1
ServerOURSERVER
Job NameMaintenance.weekly_full_system_backup
Step Nameweekly_full_system_backup
Duration00:00:04
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: OURSERVER\SYSTEM. ...sion 9.00.3042.00 for 32-bit Copyright (C)
Microsoft Corp 1984-2005. All rights reserved.
Started: 4:00:00 AM
Progress: 2010-02-19 04:00:01.36
Source: {39BBD14B-CFCA-4D5E-8C03-03E0DE0FFE42}
Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete
End Progress Progress: 2010-02-19 04:00:01.71 Source: Back Up Database Task
Executing query "BACKUP DATABASE [master] TO DISK = N'C:\Data\SQL ".: 50% complete
End Progress Progress: 2010-02-19 04:00:01.77 Source: Back Up Database Task
Executing query "declare @backupSetId as int select @backupSetId =".: 100% complete
End Progress Progress: 2010-02-19 04:00:01.85 Source: Back Up Database Task
Executing query "BACKUP DATABASE [model] TO DISK = N'C:\Data\SQL S".: 50% complete
End Progress Progress: 2010-02-19 04:00:01.88 Source: Back Up Database Task
Executing query "declare @backupSetId as in... The package execution fa... The step failed.
Kindest Regards, Rod Connect with me on LinkedIn.
February 19, 2010 at 8:19 am
It doesn't have the info regarding the failure. Do you have any error logging for the SQL Job or Maintenance Plan ? That would give more info.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 19, 2010 at 8:19 am
Not enough info. The messages are always truncated in the history log. Go into the job, set up a file to log output to and check what's in that file it it happens again.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2010 at 8:26 am
Instead of using a Maintenance Task, why not simply create an SSIS package that performs the backup. It is very simple to write a package. I have never relied on the "Maintenance Task"
Use a "Backup Database Task" from the Maintenance Plan Tasks" toolbox section. You can use a T-SQL Statement task to perform things like Transaction Log truncation. You can then shrink the log file. You can then use the Check Database Ingtegrity Task to check the database Integrity, and use the Maintenance Cleanup task to delete older backup files. Then use the Execute SQL Task to log the success or failure to a Database Maintenance database table. After that, you can use the send mail task to inform yourself that the backup was successful.
Much easier than these Maintenance Tasks I think. This will give you more control over each step and allow you to manually fire off any step if needed.
Please you can schedule this to fire off when needed.
Did you happen to verify the job performed without error before you began? When was the last time this fired off successfully? Instead of adding to one, just create a new one.
Are you placing the backup files on the same box? If so, why even bother, if that box dies, so do your backup files. Have SQL place the backups on another drive on another box.
You really only need to make a backup of the master when a new user is added, or a user is modified. You need to backup the MSDB only if a schedule is modified, or a SSIS package is added or modified.
Andrew SQLDBA
February 19, 2010 at 8:48 am
AndrewSQLDBA (2/19/2010)
Instead of using a Maintenance Task, why not simply create an SSIS package that performs the backup. It is very simple to write a package. I have never relied on the "Maintenance Task"Use a "Backup Database Task" from the Maintenance Plan Tasks" toolbox section. You can use a T-SQL Statement task to perform things like Transaction Log truncation. You can then shrink the log file. You can then use the Check Database Ingtegrity Task to check the database Integrity, and use the Maintenance Cleanup task to delete older backup files. Then use the Execute SQL Task to log the success or failure to a Database Maintenance database table. After that, you can use the send mail task to inform yourself that the backup was successful.
Much easier than these Maintenance Tasks I think. This will give you more control over each step and allow you to manually fire off any step if needed.
Please you can schedule this to fire off when needed.
Did you happen to verify the job performed without error before you began? When was the last time this fired off successfully? Instead of adding to one, just create a new one.
Are you placing the backup files on the same box? If so, why even bother, if that box dies, so do your backup files. Have SQL place the backups on another drive on another box.
You really only need to make a backup of the master when a new user is added, or a user is modified. You need to backup the MSDB only if a schedule is modified, or a SSIS package is added or modified.
Andrew SQLDBA
Using SSIS is basically the same as using SSMS maintenance plans. SSMS maintenance plans are just a subset of SSIS that can be run in SSMS (which means you don't have to install integration services if you don't want to).
Granted, there are other things you can add in with SSIS that are not available to SSMS maintenance plans but I have not found that to be much more useful. In fact, I don't like hiding maintenance plans inside SSIS where it is harder to find them and manage them.
Either way - this statement is just wrong:
You can use a T-SQL Statement task to perform things like Transaction Log truncation. You can then shrink the log file.
You should not shrink the log file on a scheduled basis - unless you want to cause performance issues for your systems. You should not truncate the log file (ever) - unless you want to prevent the ability to restore your system to a point in time.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 19, 2010 at 8:51 am
If the history on the maintenance plan shows as successful, you have a backup file for all system databases and the job has failed, most likely the reason is because the client tools used to create the plan are older than the instance.
Validate that your client tools are upgraded to at least SP3 and the server is at least post SP2 (9.0.3054 is minimum).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 19, 2010 at 8:54 am
I was at the understanding, and have tested this, that once the data is committed to the database, that is can be removed from the transaction log. I was able to keep the log file small, and restore up to the minute.
I read that in one of the SQL Books, I think that the book had a black cover, and with many pages. I have so many books, I cannot be certain.
Please let me know if I am incorrect about this info. But I was able to show it to a group of Oracle DBAs that is once worked with.
Andrew SQLDBA
February 19, 2010 at 8:54 am
I use separate packages for system backups. These maintenance plans have always worked well for me, and having them in separate packages allows me to run a separate set of backups if I'm making changes to systems (security, msdb stuff, etc)
February 19, 2010 at 9:13 am
AndrewSQLDBA (2/19/2010)
I was at the understanding, and have tested this, that once the data is committed to the database, that is can be removed from the transaction log.
Yes, and that's what happens in simple recovery (well, it's simplified description). In full recovery log records are retained until a log backup runs, the idea being back the log up to allow point in time recovery. Log truncation (backup log with truncate only) discards log records and prevents any more log backups (and hence any possibility of point-in-time recovery) until a full or diff backup is taken.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2010 at 9:15 am
AndrewSQLDBA (2/19/2010)
Are you placing the backup files on the same box? If so, why even bother, if that box dies, so do your backup files. Have SQL place the backups on another drive on another box.
Generally backing up across the network is a poor idea because of the speed of the network and the possibility of a single network glitch causing the entire backup to fail.
Backup local, copy remote.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2010 at 9:20 am
GilaMonster (2/19/2010)
Not enough info. The messages are always truncated in the history log. Go into the job, set up a file to log output to and check what's in that file it it happens again.
Will do, and then I'll run the job again and see what happens.
Kindest Regards, Rod Connect with me on LinkedIn.
February 19, 2010 at 9:29 am
Thanks for the info Gail.
I know that I had everything in Full Recovery mode, maybe I changed it for the demo. Been over a year now, I cannot remember that far back
Andrew SQLDBA
February 19, 2010 at 9:47 am
GilaMonster (2/19/2010)
Not enough info. The messages are always truncated in the history log. Go into the job, set up a file to log output to and check what's in that file it it happens again.
OK, I thought I could do this by myself, using BOL, etc, but I can't. I have absolutely no clue as to how to "set up a file to log output to..." How do I do that, please?
Kindest Regards, Rod Connect with me on LinkedIn.
February 19, 2010 at 9:51 am
Go to a job step, properties, advanced. There are places to log, including a table. you can specify a file or a table.
If this is a maintenance plan, the plan properties allow you to specify a file to log to.
February 19, 2010 at 10:10 am
Steve Jones - Editor (2/19/2010)
Go to a job step, properties, advanced. There are places to log, including a table. you can specify a file or a table.If this is a maintenance plan, the plan properties allow you to specify a file to log to.
Thank you, Steve. OK, I did as you suggested and then re-ran the job. But boy, it doesn't give me a lot. Here's the results:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 10:01:35 AM
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 10:01:35 AM
Finished: 10:01:36 AM
Elapsed: 1.344 seconds
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply