November 23, 2004 at 7:06 am
Hi Guys
I've just inherited a SQL 2000 box that has some maint tasks running for DB backup, integrity checks and optimizations just the usual maint tasks set up. What I noticed first glance is that the jobs failed with the SQLSTATE 42000 Error 22029 bug. I've checked disk space and everything looks fine, but i noticed that DB backup job is selected for some user and system dbs which i would prefer to seperate anyway, one of the user databases is set to single user mode and also one of the dbs selected in the maint task seems to have vanished from the db list in databases drop down list. I don't know if any of the issues i mentioned earlier might be the cause of the job failing or if there's something else i've overlooked but any suggestions much appreciated. Thanks...
November 23, 2004 at 8:57 am
November 23, 2004 at 9:15 am
Ah. The joys of inheriting a database.
It sounds as if you have multiple issues.
If I understand the first issue, the job is set to run as a user against a system database.
The second appears to be a job scheduled to run against a database that is set to single user mode, which fails.
The third issue is a job scheduled to run against a database that no longer exists on the server.
Here's my two cents.
1. Make sure all jobs are run as and owned by "sa". It may be easier to drop and re-create the jobs.
2. Make sure that all objects (databases, tables, procedures, etc) are owned by 'dbo'.
3. Set the single user mode database to dbo user only. At least multiple admin level users will be able to connect.
Good luck
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 23, 2004 at 9:33 am
From your first point Michael, the maint task DB backup job is setup to to backup some databases both system and user dbs for instance master and msdb was selected along with user databases. I've made a few adjustments and i'll leave the jobs to run as scheduled and check for errors if any...
Thanks for your responses
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply