Database Overwritten: maint

Showing posts with label maint. Show all posts

Sunday, March 11, 2012

Database Maintenance Plans

Hello everyone,
I'm new to DB Maint Plans, so let me apologize upfront. I've taken over a system from a DBA who is no longer working here, and he set up Maint Plans for all of the existing DBs. The plans show up in the Enterprise Manager under "Management->Database Maintenance Plans" like they should, but there are also entries in the "Management->SQL Server Agent->Jobs" area. When I set up a new DB Maint Plan for a new DB, it seems to be working fine, but I don't have any corresponding entries in Jobs. Did the other DBA set these up manually? Does anyone know why he might have done this? Is it needed? The jobs and job steps look like the following:

[DBName]Full:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "W:\sqldata\MSSQL$P001\Backup" -DelBkUps 1WEEKS -CrBkSubDir -BkExt "BAK"'

[DBName]Maint Integrity Checks:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -CkDB '

[DBName]Maint Optimizations:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '

Any help or insight would be greatly appreciated!
Thanks in advance,
CatWhen you set up a plan, it should set up a job. In Enterprise Manager folders window, right-click on Jobs and select "Refresh". I'm betting the GUI just isn't aware of the change you made...|||I would suggest that you start with BOL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/entrmgr/em_6x7w.asp) for Database Maintenance Plans, and go from there.

-PatP|||When you set up a plan, it should set up a job. In Enterprise Manager folders window, right-click on Jobs and select "Refresh". I'm betting the GUI just isn't aware of the change you made...

Hi Blindman,
Thanks for your reply. I refreshed the GUI for Jobs, but I still don't see my maint plan tasks. Strange! I figured that it must have set up the jobs automatically, but since it didn't set up anything for mine, I didn't know what to think. Do you know if I might have done something incorrectly in setting up my plan? They appear to be working fine.

Well, any further insight would be appreciated.
Thanks for your time.
Cat|||Sorry. I'm not sure why they aren't showing up in your job list. I don't use the Maintenance Plans anyway.|||I would suggest that you start with BOL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/entrmgr/em_6x7w.asp) for Database Maintenance Plans, and go from there.

-PatP

I have looked there (if I hadn't I would not have been able to set up a plan in the first place). If I had found the answers to my specific questions, I would not have posted them here. I realize that there are a lot of people who post questions on these boards without taking any time to research the answers themselves, but... 1) I am not one of them, and 2) Sometimes it is easier and less frustrating to ask a human for a quick answer to a question than to try and weed through the enormous amount of information contained in the BOL. If you are going to take the time to respond to a post, it would be more helpful if you would offer more specific areas or keywords to search on to reduce the amount of time spent pouring over information that does not apply.

Thank you for your time.
Cat|||Sorry. I'm not sure why they aren't showing up in your job list. I don't use the Maintenance Plans anyway.

Well, thank you! At least I know that the previous DBA did not manually set up the jobs. That's one mystery solved.

Thanks again, you're a sweetheart!
Cat|||Not necessarily. I set up jobs manually. The Maintenance Plan wizard sucks.|||I have If you are going to take the time to respond to a post, it would be more helpful if you would offer more specific areas or keywords to search on to reduce the amount of time spent pouring over information that does not apply.

Thank you for your time.
Cat
Hi Cat,
I think you have meet with some of the best and wonderful people of this forum.They are gurus in this field and they spend their valuable and busy time to answer questions from the most difficult one to the most silliest one.I bet that their suggestions are most helpful and have a deep insight.They have a huge patience and delicate word style to deal with everybody with extreme politeness.I suggest you to follow them without any hesitation,bcoz they have the best intention that you could think of. I am saying this not only they are my favourite but they are really a nice human being.|||Well, I'm not always delicate... ;)|||As Blindman noted, the Maintenance Plan Wizard is better than nothing, but to a DBA that's been around for a while it isn't a lot better than nothing. I'm one of the "crusty old pharts" that still prefers the level of control that comes from writing the maintenance scripts myself at the Transact-SQL level.

Usually when I see what you've decribed (Maintenance plans set up, then jobs set up to further customize the plan), it usually means that whoever set them up originally figured out they needed more, so they manually scheduled the job to allow them to customize the existing setup using parameters.

Sorry if I was a bit short up front. I've been running into a lot of posters/users complaining something to the effect of: "My toy is broke, you need to fix it for me, NOW" and that apparently carried forward into my response to your original question. I should have read it more closely before I responded.

-PatP|||Delicately handled, Pat.|||Sorry if I was a bit short up front. I've been running into a lot of posters/users complaining something to the effect of: "My toy is broke, you need to fix it for me, NOW" and that apparently carried forward into my response to your original question. I should have read it more closely before I responded.

-PatP

Thank you for that! And I'm sorry too if my tone was a bit harsh, I had been having a frustrating day. I have seen many of the posts on this board which you describe and I know that it can be frustrating.

I suspect that the previous DBA either did not set up the jobs himself and they were set up by SQL Server (like blindman suggested), OR he did set them up but only because he thought he had to rather than because he wanted to tweek them. I have come to find many of his DBA practises quite suspect to say the least.

Thanks again to everyone for their responses!
Cat

Database Maintenance Plan Wizard

I cannot run the DB Maint. Plan Wizard, when I trying I get the following
error (right from the start):
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Unable to execute requested command.
Method not found: 'Void
Microsoft.SqlServer.Management.DatabaseMaintenance .TaskUIUtils..ctor()'.
(Microsoft.SqlServer.Management.MaintenancePlanWiz ard)
BUTTONS:
OK
I do have SSIS installed, and I can see that the service is running in the
SS Configuration Manager.
The version of SQL:
Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on
Windows NT 5.2 (Build 3790: Service Pack 2)
Any help would be very much appreciated!
Wanted to include for details on install:
Microsoft SQL Server Management Studio9.00.1399.00
Microsoft Analysis Services Client Tools2005.090.1399.00
Microsoft Data Access Components (MDAC)2000.086.3959.00
(srv03_sp2_rtm.070216-1710)
Microsoft MSXML2.6 3.0 6.0
Microsoft Internet Explorer6.0.3790.1830
Microsoft .NET Framework2.0.50727.1433
Operating System5.2.3790
"Scott" wrote:

> I cannot run the DB Maint. Plan Wizard, when I trying I get the following
> error (right from the start):
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Unable to execute requested command.
> --
> Method not found: 'Void
> Microsoft.SqlServer.Management.DatabaseMaintenance .TaskUIUtils..ctor()'.
> (Microsoft.SqlServer.Management.MaintenancePlanWiz ard)
> --
> BUTTONS:
> OK
> --
>
> I do have SSIS installed, and I can see that the service is running in the
> SS Configuration Manager.
> The version of SQL:
> Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02
> Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on
> Windows NT 5.2 (Build 3790: Service Pack 2)
> Any help would be very much appreciated!
|||RELOADED SP2, which seems to have fixed it.
"Scott" wrote:

> I cannot run the DB Maint. Plan Wizard, when I trying I get the following
> error (right from the start):
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Unable to execute requested command.
> --
> Method not found: 'Void
> Microsoft.SqlServer.Management.DatabaseMaintenance .TaskUIUtils..ctor()'.
> (Microsoft.SqlServer.Management.MaintenancePlanWiz ard)
> --
> BUTTONS:
> OK
> --
>
> I do have SSIS installed, and I can see that the service is running in the
> SS Configuration Manager.
> The version of SQL:
> Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02
> Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on
> Windows NT 5.2 (Build 3790: Service Pack 2)
> Any help would be very much appreciated!

Database Maintenance Plan Wizard

I cannot run the DB Maint. Plan Wizard, when I trying I get the following
error (right from the start):
TITLE: Microsoft SQL Server Management Studio
--
Cannot show requested dialog.
--
ADDITIONAL INFORMATION:
Unable to execute requested command.
--
Method not found: 'Void
Microsoft.SqlServer.Management.DatabaseMaintenance.TaskUIUtils..ctor()'.
(Microsoft.SqlServer.Management.MaintenancePlanWizard)
--
BUTTONS:
OK
--
I do have SSIS installed, and I can see that the service is running in the
SS Configuration Manager.
The version of SQL:
Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on
Windows NT 5.2 (Build 3790: Service Pack 2)
Any help would be very much appreciated!Wanted to include for details on install:
Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00
(srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.1433
Operating System 5.2.3790
"Scott" wrote:
> I cannot run the DB Maint. Plan Wizard, when I trying I get the following
> error (right from the start):
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Unable to execute requested command.
> --
> Method not found: 'Void
> Microsoft.SqlServer.Management.DatabaseMaintenance.TaskUIUtils..ctor()'.
> (Microsoft.SqlServer.Management.MaintenancePlanWizard)
> --
> BUTTONS:
> OK
> --
>
> I do have SSIS installed, and I can see that the service is running in the
> SS Configuration Manager.
> The version of SQL:
> Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02
> Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on
> Windows NT 5.2 (Build 3790: Service Pack 2)
> Any help would be very much appreciated!|||RELOADED SP2, which seems to have fixed it.
"Scott" wrote:
> I cannot run the DB Maint. Plan Wizard, when I trying I get the following
> error (right from the start):
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Unable to execute requested command.
> --
> Method not found: 'Void
> Microsoft.SqlServer.Management.DatabaseMaintenance.TaskUIUtils..ctor()'.
> (Microsoft.SqlServer.Management.MaintenancePlanWizard)
> --
> BUTTONS:
> OK
> --
>
> I do have SSIS installed, and I can see that the service is running in the
> SS Configuration Manager.
> The version of SQL:
> Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02
> Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on
> Windows NT 5.2 (Build 3790: Service Pack 2)
> Any help would be very much appreciated!

Thursday, March 8, 2012

database maintenance plan - reorganize data pages and indexes

I've set up a monthly maint. plan that will re-org the db. I'm hoping that since this is a very high tran vol that I'm being proactive.

But, when I do this against a matching test db (exact same one but the name) I get a SET QUOTED IDENTIFIER incorrect on the optimization step. I've checked the properties of the DB and the 'Use quoted identifiers' box is checked and needs to be. I created the maint plan using the wizard and the option I've tried it with the option of reorg to orig amt of free space and change free space selected.

Help!!!! Oh, this is a PeopleSoft DB, so I can't change the prop of the DB..

Try putting the following statements at the top of the step with the SQL Agent Job that the DB Maintenance Plan creates to schedule execution of the plan:

set nocount on
set arithabort on
set ansi_nulls on
set ansi_null_dflt_on on
set ansi_padding on
set ansi_warnings on
set cursor_close_on_commit off
set implicit_transactions off
set quoted_identifier on
set concat_null_yields_null on
set numeric_roundabort off

That should be directly followed by the statement that already existed within the step to execute the master.dbo.xp_sqlmaint procedure.

HTH

|||Sorry to take so long, I had to find out how to add the commands. Which I did, but I'm still getting the same error. I c&p'd them into the 'edit' of the job step and applied. Then I went back and checked that they were still there. I started the job (which I've set up on a copy of the prod db) and it died w/the same error.
As you can tell, this is new stuff to me.
What else can I try?|||Well, try changing the 'on' in the 'set quoted identifier on' statement to 'off' instead...all comes down to differences between these settings in the msdb, master, and your user database...|||I couldn't chg the flag. But did get the suggestion you sent to work. I removed the line returns at the end of each line and replaced them with just a space, so that the entries where all strung together when I edited the step. So that worked.

So thank you. Now I just have to learn how to keep them when the job is refreshed.

as you've probably guessed, I'm learning as I go.|||

I'm back! Thought I had this resolved, but am still having the same error. The bad thing is that it only occurs on some of the DBs in the same instance of SQL Server. I've checked the options on the DBs and they match between the ones that can be integrity checked and the ones that can not. The ones that can be i ckd will run w/o any of the overrides. For the ones that the job gets an error, I've tried quoted_identifier on and off, with the other options and w/o. All DBs have ANSI NULL default, Auto update statistics, torn page, auto create statistics and use quoted identifiers checked, others not. (sql 2000). Db compatibility level 80 is selected on the compatibility. Some Dbs are model simple, some are full. That doesn't seem to matter.

I'd appreciate any more ideas.........

database maintenance plan - integrity check

I've set up a monthly maint. plan that will re-org the db. I'm hoping that since this is a very high tran vol that I'm being proactive.

Help!!!! Oh, this is a PeopleSoft DB, so I can't change the prop of the DB..

Try putting the following statements at the top of the step with the SQL Agent Job that the DB Maintenance Plan creates to schedule execution of the plan:

That should be directly followed by the statement that already existed within the step to execute the master.dbo.xp_sqlmaint procedure.

HTH

|||Sorry to take so long, I had to find out how to add the commands.

Which I did, but I'm still getting the same error. I c&p'd

them into the 'edit' of the job step and applied. Then I went

back and checked that they were still there. I started the job

(which I've set up on a copy of the prod db) and it died w/the

same error.

As you can tell, this is new stuff to me.

What else can I try?|||Well, try changing the 'on' in the 'set quoted identifier on' statement to 'off' instead...all comes down to differences between these settings in the msdb, master, and your user database...|||I couldn't chg the flag. But did get the suggestion you sent to work. I removed the line returns at the end of each line and replaced them with just a space, so that the entries where all strung together when I edited the step. So that worked.

So thank you. Now I just have to learn how to keep them when the job is refreshed.

as you've probably guessed, I'm learning as I go.|||

I'd appreciate any more ideas.........

Wednesday, March 7, 2012

database maint plan failed

Hi ,
i am running sql server 2000 (evaluation copy) on Win 2003 server and i
just set up a Database Maint Plan to baackup my system database
of all the 4 jobs created only the optimization job suceed and rest such
as txn log bkup ,db bkup failed with the following errors :
sqlmaint.exe failed (SQLSTATE 42000) (Error 22029) the step failed
however, i did go to the location where the files were supposed to be saved
to and i found there are some files (but i did not check if the data is ok)
what could be the reason ? appreciate any advice
tk s& rdgsSpecify a report file for the maint plan and check that report file for specific error messages
after an execution of the job(s).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:083F3AEB-13DB-474C-B1BC-EC78501D139D@.microsoft.com...
> Hi ,
> i am running sql server 2000 (evaluation copy) on Win 2003 server and i
> just set up a Database Maint Plan to baackup my system database
> of all the 4 jobs created only the optimization job suceed and rest such
> as txn log bkup ,db bkup failed with the following errors :
> sqlmaint.exe failed (SQLSTATE 42000) (Error 22029) the step failed
> however, i did go to the location where the files were supposed to be saved
> to and i found there are some files (but i did not check if the data is ok)
> what could be the reason ? appreciate any advice
> tk s& rdgs|||MS says it's related to SP but mine is with the latest sp3a but the problem
still there...
http://support.microsoft.com/kb/295022/EN-US/
MS guys, can you guys please help
"Tibor Karaszi" wrote:
> Specify a report file for the maint plan and check that report file for specific error messages
> after an execution of the job(s).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:083F3AEB-13DB-474C-B1BC-EC78501D139D@.microsoft.com...
> > Hi ,
> >
> > i am running sql server 2000 (evaluation copy) on Win 2003 server and i
> > just set up a Database Maint Plan to baackup my system database
> >
> > of all the 4 jobs created only the optimization job suceed and rest such
> > as txn log bkup ,db bkup failed with the following errors :
> >
> > sqlmaint.exe failed (SQLSTATE 42000) (Error 22029) the step failed
> >
> > however, i did go to the location where the files were supposed to be saved
> > to and i found there are some files (but i did not check if the data is ok)
> >
> > what could be the reason ? appreciate any advice
> >
> > tk s& rdgs
>
>|||That article is for MSDE. You are running eval edition. So the article doesn't apply to you.
Do don't you do as suggested? Specify a report file so we have an error message to go on. "My car
doesn't work. What is the problem." Pretty hard to answer. Inside the report file, you find detailed
error messages, which will tell us why the job failed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:C9C496F5-5DEA-433A-9C1E-6DC76E21659D@.microsoft.com...
> MS says it's related to SP but mine is with the latest sp3a but the problem
> still there...
> http://support.microsoft.com/kb/295022/EN-US/
> MS guys, can you guys please help
> "Tibor Karaszi" wrote:
>> Specify a report file for the maint plan and check that report file for specific error messages
>> after an execution of the job(s).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:083F3AEB-13DB-474C-B1BC-EC78501D139D@.microsoft.com...
>> > Hi ,
>> >
>> > i am running sql server 2000 (evaluation copy) on Win 2003 server and i
>> > just set up a Database Maint Plan to baackup my system database
>> >
>> > of all the 4 jobs created only the optimization job suceed and rest such
>> > as txn log bkup ,db bkup failed with the following errors :
>> >
>> > sqlmaint.exe failed (SQLSTATE 42000) (Error 22029) the step failed
>> >
>> > however, i did go to the location where the files were supposed to be saved
>> > to and i found there are some files (but i did not check if the data is ok)
>> >
>> > what could be the reason ? appreciate any advice
>> >
>> > tk s& rdgs
>>

database maint plan failed

Database Maint issue

I've got an issue when creating a database maintenance plan. When I get to
the
Specify Backup Disk Directory (in the DB Maintenance Plan Wizard), the
drop-down box for specifying the remove files older than is blank, and the
default timeframe is 0, and the Backup file extension is blank. On other SQL
servers, by default these locations show the drop-down as Week(s), the
timeframe as 4, and the extention as BAK.
If I continue setting up the maintenance plan, when I get to the Specify
Transaction Log Backup Disk Directory, I'll run into the same problem as the
Database (blank and 0 for the same fields).
In any event it will allow me to setup the plans, however I'd like to be
able to set the remove feature and I can't...
Any ideas?
hi,
Darth Blubber wrote:
> I've got an issue when creating a database maintenance plan. When I
> get to the
> Specify Backup Disk Directory (in the DB Maintenance Plan Wizard), the
> drop-down box for specifying the remove files older than is blank,
> and the default timeframe is 0, and the Backup file extension is
> blank. On other SQL servers, by default these locations show the
> drop-down as Week(s), the timeframe as 4, and the extention as BAK.
> If I continue setting up the maintenance plan, when I get to the
> Specify Transaction Log Backup Disk Directory, I'll run into the same
> problem as the Database (blank and 0 for the same fields).
> In any event it will allow me to setup the plans, however I'd like to
> be able to set the remove feature and I can't...
> Any ideas?
this is a long nightmare with Maintenance Plan withard (which actually is
very buggy)... please have a look at
http://groups.google.it/groups?as_q=...0&as_maxy=2005
for a short idea...
I strongly recommend you to write your own SQL Server jobs in
Transact-SQL... this can be a little bit longer than in the wizard,
requiring you to improve your skills, but it will pay in the long terms...
Andrea Montanari
http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Sunday, March 11, 2012

Database Maintenance Plans

Database Maintenance Plan Wizard

Database Maintenance Plan Wizard

Thursday, March 8, 2012

database maintenance plan - reorganize data pages and indexes

database maintenance plan - integrity check

Wednesday, March 7, 2012

database maint plan failed

database maint plan failed

database maint plan failed

Database Maint issue

Database Overwritten

Blog Archive

About Me