SQL Maintenance Plans
-
I am getting following error in the logshipping job for copying the log file from source server to standby server.
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
I am using SQL2K with SP2 , EP Edition. I know there is no access rights problem for shared folder on network. I have all sa rights as well.
I have searched all aritcles on microsoft kb and all SQL Server sites but no help.
Any help is highly appriciated.
Thanks.
Minesh.
-
So it's creating the backup, then fails on the copy? I've never had a problem with log shipping, to start with I'd recommend verifying the permissions one more time, making sure that the agent account has disk permissions.
Andy
-
I use both customized backups and maintenance plans.
1) With maintenance plans how do you delegate the manual running of a backup to a non-system administrator. A developer would like to have a full or increment backup before he starts a test plan. The developer cannot see the SQL Agent jobs created by maintenance plans.
2) How does one integrate maintenance plans into customized jobs? I would like to do a full or increment backup just before a posting program runs?
Thanks
-
On the first one, a shortcut method is have then add an entry to a table indicating they want the job to run, have a job that polls the table periodically and if it finds the entry, runs the backup job. You could also use xp_readmail but I find the table method simpler to use.
On the second point, if you're wanting to leverage the full backup job created by the plan you can just figure out which job it is, then use sp_start_job to kick it off. Knowledge of the plan is critical, if the plan changed, say to NOT do a full backup anymore or to not include the db you're working on, then you'd have problems!
Andy
-
I took over several SQL servers with copies of a key database. They all had maintenance plans with Integrity Checks enabled. The integrity checks ran in about a second, and returned success.
Then I found that every copy of our database was corrupt with an allocation error, and evidently had been for months, so every backup copy we had was corrupt as well. DBCC CHECKALLOC would immediately detect it, and DBCC CHECKDB would also report it. The fix required creating a new database, and copying every object out to it, then using it to replace the old one.
Since then I "roll my own" maintenance plans.
Barry
-
Wow! Was this 2000 or 7.0? Does anybody know what "Integrity Checks" on the maint plans really runs?
quote:
I took over several SQL servers with copies of a key database. They all had maintenance plans with Integrity Checks enabled. The integrity checks ran in about a second, and returned success.Then I found that every copy of our database was corrupt with an allocation error, and evidently had been for months, so every backup copy we had was corrupt as well. DBCC CHECKALLOC would immediately detect it, and DBCC CHECKDB would also report it. The fix required creating a new database, and copying every object out to it, then using it to replace the old one.
Since then I "roll my own" maintenance plans.
Barry
-
I'll have a follow up article in the next week or two that looks at what actually gets done by the plans.
Andy
-
Just going to add guys that the Maintenance Plans do offer a reasonable amount of error reporting if you know where to look.
Don't look at the job, flick back to the Maint Plan that drives the job, and check 'Maintenance Plan History' there.
Cheers,
I use maintenance plans. But my pet peeve is any software that, when something breaks, provides little in the way of meaningful feedback about the cause.
-
I agree that would be a very nice feature. I guess the plans cant be all things to all people, but a few more features would close the gap.
Andy
-
i think its really good tool provided in sql server the only thing is it shows a lot of screens .
harshal
harshal -
Nice article. I have a OT question. Please forgive my ignorance. How do you create a maintenance plan with sqlmaint (without the wizard)?
Thanks in advance,
Yuan
-
Thats an interesting question. Dont know the answer, will have to look some. SQLMaint executes the plans, dont think (but not sure) it plays a part in creating the plan. If it doesnt...then possibly could be done using SQL-Namespace. I'll try to look into, but it may be a bit, got a few other things in progress at the moment!
Andy
-
Hi, I new and just read the article. We use DB maintenance plans to do our index rebuilds and a third-party product called TSM to handle backups (it has a native sql module). Like everyone else, I would like to see the code. So the option to generate it would be great. Also, I would like to see SQL server coupled with something like Visual Source Safe for version control. We have lots of SP's, DTS packages, scheduled jobs etc. with no automated way of handeling changes.
-
Check out Bill Wunder's Source Safe utility on this site. It's great!
-
We currently use a stored procedure to do this, it was written by a DBA here. Not a whole lot of code, runs nightly and has never been a problem. except on the rare occasion when we get a "General MAPI Failure" So my question is, what's the advantage to the maintenance plan approach, because I felt the same as you until the stored procedure was introduced and has worked fine.
Viewing 15 posts - 16 through 30 (of 60 total)
You must be logged in to reply to this topic. Login to reply