July 22, 2009 at 11:32 am
Since this is a maintenance plan try querying the msdb maintenance history table:
select * from msdb..sysdbmaintplan_history
where plan_name = 'your_plan_name'
I have found that this table sometimes has better info than the logged text file !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
July 22, 2009 at 2:21 pm
Where you are looking at the history is on the job - you need to look at the history for the maintenance plan. Right click on the maintenance plan and view history.
If you want to look at the history using a query, you need to query the tables:
msdb.dbo.sysmaintplan_plans
msdb.dbo.sysmaintplan_subplans
msdb.dbo.sysmaintplan_log
msdb.dbo.sysmaintplan_logdetail
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 22, 2009 at 3:52 pm
I tried the view history, it was truncated.. that was my problem. I'll check those tables out tomorrow morning.
Thanks
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 22, 2009 at 4:31 pm
torpkev (7/22/2009)
I tried the view history, it was truncated.. that was my problem. I'll check those tables out tomorrow morning.Thanks
The history that you checked was from the job history - not the maintenance plan history. They are different and the maintenance plan history will show you which task failed - and give you the error for that task.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 23, 2009 at 7:00 am
ah I see what you're getting at.. sorry im a bit dim some days..
It does show that it failed, and why.. unfortunately
Task Detail: Backup Database on Local server connection
Databases: myDB
Type: Full
Append existing
Error Number: 100
Error: "An exception occurred while executing a Transact-SQL statement or batch." doesn't really help
Hopefully the logging will catch the error with a bit more detail next time it happens.
Thanks!
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 23, 2009 at 8:21 am
Are you appending backups to an existing file?
Don't do that. It increases your risk dramatically. If there is a file issue, and there could be as it grows, then you might lose multiple days worth of backups. What's worse than losing yesterday's backup? Losing the last 3 days and explaining that to your boss.
Use a new file every day.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 23, 2009 at 8:28 am
I dont think I am appending.. the option is greyed out (see attached image)
I agree though.. geez.. sure hope I'm not overlaying backup files.. i didn't think I was anyway.. there is no way that could end well!
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 23, 2009 at 8:33 am
I'd agree with Steve's concern of backup up your database in a single file by appending files to it. If something happens to the file, none of the backups will be useful.
Instead, why not create a plain backup job that backs up your database with a different filename each time.
Something like this.
declare @BackupDest varchar(100)
set @BackupDest = 'C:\MyDB\MyDB_' + convert(varchar,getdate(),112)+'.BAK'
BACKUP DATABASE MyDB TO DISK = @BackupDest
July 23, 2009 at 8:38 am
ps (7/23/2009)
I'd agree with Steve's concern of backup up your database in a single file by appending files to it. If something happens to the file, none of the backups will be useful.Instead, why not create a plain backup job that backs up your database with a different filename each time.
Something like this.
declare @BackupDest varchar(100)
set @BackupDest = 'C:\MyDB\MyDB_' + convert(varchar,getdate(),112)+'.BAK'
BACKUP DATABASE MyDB TO DISK = @BackupDest
I have a stored procedure already to create my backups with unique filenames that I use when I dont want to bother with the maintenance plan.. but when I'm creating a full maintenance plan then I tend to use the built in Full Backup (see screenshot attached to previous post) - If the maintenance plan appends by default (which I've never noticed before to be honest) - then I'll need to go through every database and change that el-pronto!
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 23, 2009 at 8:50 am
torpkev (7/23/2009)
If the maintenance plan appends by default (which I've never noticed before to be honest) - then I'll need to go through every database and change that el-pronto!
I did create a maintainance plan a while ago and it doesn't append to the single file by default.
This was the T-SQL code generated by the maintainance plan.
BACKUP DATABASE [Peer_Replication_Test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\Peer_Replication_Test_backup_200907232019.bak' WITH NOFORMAT, NOINIT, NAME = N'Peer_Replication_Test_backup_20090723201912', SKIP, REWIND, NOUNLOAD, STATS = 10
which means it's generating new names for each backupset and not appending to the same file.
July 23, 2009 at 9:09 am
the error you posted seemed to indicate appends. Definitely check that.
There are some good scripts here on the site for backups that can help if you want to look at how others are doing it.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 23, 2009 at 9:31 am
Thanks guys..
I think I've been relying on the maintenance plans a bit too much lately (time constraints more than anything).. i think i'll just bite the bullet, mark off a couple of days on my calendar and create jobs rather than using the plans.
Thanks again for all your help... I didn't get to where I was hoping, but I am getting to the realization that trying to do this the easy way is gonna burn me, and my time will probably be better spent taking the time to do it properly.
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 23, 2009 at 9:44 am
you are welcome, and one thing I'd do is document what you do as you go along, and then save copies for yourself (and put it on a blog).
I've carried this exact system to a few jobs, enabling me to standardize and set things up quickly and easily.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 23, 2009 at 9:45 am
torpkev (7/23/2009)
Thanks guys..I think I've been relying on the maintenance plans a bit too much lately (time constraints more than anything).. i think i'll just bite the bullet, mark off a couple of days on my calendar and create jobs rather than using the plans.
Thanks again for all your help... I didn't get to where I was hoping, but I am getting to the realization that trying to do this the easy way is gonna burn me, and my time will probably be better spent taking the time to do it properly.
I don't view using the maintenance plans as a problem. We have more than 160 servers, with more than 250 databases that are being backed up using standard SSMS maintenance plans with no problems.
I prefer using the maintenance plans just because I get logging for each step without having to write it myself. I also get management of the history built in - again, without having to write anything myself.
For this error - there has to be more information. When you look at the history - the box that contains the error scrolls to the right. If there is nothing else there, then there should be more logged in the event viewer or SQL Server logs.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply