blocking prevent maintenance to run

I am having intermittent issues with a team that has an Access front end
that hits SQL2000sp3a+. They are wanting me to do some type of kill of all
users in the db prior to running the DBCC DBREINDEX that is done during the
night. Granted I could do a DBCC INDEXDEFRAG for online.
I see the application causing the problem. They have confirmed that some
users are leaving reports open that are bound to data, which I assume is
causing the issue.
1. Is it possible to selectively kill the process that is causing the block
as to kill all users in the db?
I am open to ideas how to address the issue. How to battle being forced to
implement a server side bandaid to a application issue.Look up Alter Database in BOL. See the restricted_user and single_user
options with Rollback Immediate.
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:OJJRZhzdFHA.612@.TK2MSFTNGP12.phx.gbl...
>I am having intermittent issues with a team that has an Access front end
>that hits SQL2000sp3a+. They are wanting me to do some type of kill of all
>users in the db prior to running the DBCC DBREINDEX that is done during the
>night. Granted I could do a DBCC INDEXDEFRAG for online.
> I see the application causing the problem. They have confirmed that some
> users are leaving reports open that are bound to data, which I assume is
> causing the issue.
> 1. Is it possible to selectively kill the process that is causing the
> block as to kill all users in the db?
> I am open to ideas how to address the issue. How to battle being forced
> to implement a server side bandaid to a application issue.
>

blocking prevent maintenance to run

I am having intermittent issues with a team that has an Access front end
that hits SQL2000sp3a+. They are wanting me to do some type of kill of all
users in the db prior to running the DBCC DBREINDEX that is done during the
night. Granted I could do a DBCC INDEXDEFRAG for online.
I see the application causing the problem. They have confirmed that some
users are leaving reports open that are bound to data, which I assume is
causing the issue.
1. Is it possible to selectively kill the process that is causing the block
as to kill all users in the db?
I am open to ideas how to address the issue. How to battle being forced to
implement a server side bandaid to a application issue.
Look up Alter Database in BOL. See the restricted_user and single_user
options with Rollback Immediate.
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:OJJRZhzdFHA.612@.TK2MSFTNGP12.phx.gbl...
>I am having intermittent issues with a team that has an Access front end
>that hits SQL2000sp3a+. They are wanting me to do some type of kill of all
>users in the db prior to running the DBCC DBREINDEX that is done during the
>night. Granted I could do a DBCC INDEXDEFRAG for online.
> I see the application causing the problem. They have confirmed that some
> users are leaving reports open that are bound to data, which I assume is
> causing the issue.
> 1. Is it possible to selectively kill the process that is causing the
> block as to kill all users in the db?
> I am open to ideas how to address the issue. How to battle being forced
> to implement a server side bandaid to a application issue.
>

blocking prevent maintenance to run

I am having intermittent issues with a team that has an Access front end
that hits SQL2000sp3a+. They are wanting me to do some type of kill of all
users in the db prior to running the DBCC DBREINDEX that is done during the
night. Granted I could do a DBCC INDEXDEFRAG for online.
I see the application causing the problem. They have confirmed that some
users are leaving reports open that are bound to data, which I assume is
causing the issue.
1. Is it possible to selectively kill the process that is causing the block
as to kill all users in the db?
I am open to ideas how to address the issue. How to battle being forced to
implement a server side bandaid to a application issue.Look up Alter Database in BOL. See the restricted_user and single_user
options with Rollback Immediate.
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:OJJRZhzdFHA.612@.TK2MSFTNGP12.phx.gbl...
>I am having intermittent issues with a team that has an Access front end
>that hits SQL2000sp3a+. They are wanting me to do some type of kill of all
>users in the db prior to running the DBCC DBREINDEX that is done during the
>night. Granted I could do a DBCC INDEXDEFRAG for online.
> I see the application causing the problem. They have confirmed that some
> users are leaving reports open that are bound to data, which I assume is
> causing the issue.
> 1. Is it possible to selectively kill the process that is causing the
> block as to kill all users in the db?
> I am open to ideas how to address the issue. How to battle being forced
> to implement a server side bandaid to a application issue.
>

Bizarre maintenance plan failure (db backup)

I am getting a failure on the db backup job of one of my maintenance
plans. It is coming back with the generic error message of,

"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step
failed."

I then checked the Database Maintenance Plan History page, but this
shows all the steps having run successfully. If I check the drives for
the actual backup files, they exist and look healthy too!

There is plenty of space on the drives, so it is not that.

I've checked the NT logs and all they say is,

"SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan for All User Databases''
(0xC06E15E2A9E1414087BE19541D167861) - Status: Failed - Invoked on:
2005-06-29 21:00:04 - Message: The job failed. The Job was invoked by
Schedule 35 (Schedule 1). The last step to run was step 1 (Step 1). "

Which doesn't give me any clues.

Since the backups have actually run to success, I am going to take off
the option on the maintenance plan to "Verfiy the integrity of the
backup upon completion". Maybe it is this that is causing problems,
rather than the backup?

Anyone had anything similar?(nielsonj1976@.yahoo.co.uk) writes:
> I am getting a failure on the db backup job of one of my maintenance
> plans. It is coming back with the generic error message of,
> "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step
> failed."
> I then checked the Database Maintenance Plan History page, but this
> shows all the steps having run successfully. If I check the drives for
> the actual backup files, they exist and look healthy too!
> There is plenty of space on the drives, so it is not that.
> I've checked the NT logs and all they say is,
> "SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
> Maintenance Plan for All User Databases''
> (0xC06E15E2A9E1414087BE19541D167861) - Status: Failed - Invoked on:
> 2005-06-29 21:00:04 - Message: The job failed. The Job was invoked by
> Schedule 35 (Schedule 1). The last step to run was step 1 (Step 1). "
> Which doesn't give me any clues.

So did you look at the Job History? Find the backup job, right-click
and select Show History. In this dialog check "Show step details". The
step details usually have some more meat for information.

> Since the backups have actually run to success, I am going to take off
> the option on the maintenance plan to "Verfiy the integrity of the
> backup upon completion". Maybe it is this that is causing problems,
> rather than the backup?

Ehum, if the problem is that the backups don't verify, that I would
not like to know that and not sweep it under the carpet.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> So did you look at the Job History? Find the backup job, right-click
> and select Show History. In this dialog check "Show step details". The
> step details usually have some more meat for information.

This doesn't really give me any clues either. If I click show step
details, I get it displaying 2 steps,

Step 1: Executed as user: OMNIA\SVC_SQL_Exec. sqlmaint.exe failed.
[SQLSTATE 42000] (Error 22029). The step failed.

Step 0: (Job Outcome) The job failed. The Job was invoked by Schedule
35 (Schedule 1). The last step to run was step 1 (Step 1).

> > Since the backups have actually run to success, I am going to take off
> > the option on the maintenance plan to "Verfiy the integrity of the
> > backup upon completion". Maybe it is this that is causing problems,
> > rather than the backup?

> Ehum, if the problem is that the backups don't verify, that I would
> not like to know that and not sweep it under the carpet.

Having checked this morning the backup status is still showing Failed
even with the verify option off. The only reason I thought it *could*
be this is that the backups are fairly large (25gb+) so SQL server
could be having problems verifying with the disk space available?|||nielsonj1976@.yahoo.co.uk (nielsonj1976@.yahoo.co.uk) writes:
> This doesn't really give me any clues either. If I click show step
> details, I get it displaying 2 steps,
> Step 1: Executed as user: OMNIA\SVC_SQL_Exec. sqlmaint.exe failed.
> [SQLSTATE 42000] (Error 22029). The step failed.
> Step 0: (Job Outcome) The job failed. The Job was invoked by Schedule
> 35 (Schedule 1). The last step to run was step 1 (Step 1).

I rarely set into maintenance job, so I don't really know where sqlmaint
might write its errors. But looking at the properties of a plan that I've
actually set up, I see that there is a Reporting tab. Have you checked
what's yours, and if that leads to a useful file?

>> > Since the backups have actually run to success, I am going to take off
>> > the option on the maintenance plan to "Verfiy the integrity of the
>> > backup upon completion". Maybe it is this that is causing problems,
>> > rather than the backup?
>> Ehum, if the problem is that the backups don't verify, that I would
>> not like to know that and not sweep it under the carpet.
> Having checked this morning the backup status is still showing Failed
> even with the verify option off. The only reason I thought it *could*
> be this is that the backups are fairly large (25gb+) so SQL server
> could be having problems verifying with the disk space available?

I will have to retract what I said above. I recalled a discussion among
my MVP colleagues who are into server administration. The consensus was
that verification of backups as offered by RESTORE VERIFYOLNLY is useless.
The only way to really verify a backup is to restore it. And run DBCC checks
on the restored copy.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Do your maint plans cover more than 1 database? If so check the recovery modes of each - this error seems to pop up if there are mixed modes within 1 plan.

Bizarre data loss running maintenance plan

I'm running MSDE 2000 SP4 on Win2KAS SP4 (fully patched). I have a
maintenance plan that backs up the database at 18:30 and backs up the
transaction log at midnight. Apparently the transaction log backups have
been failing because the database is set to the Simple recovery model. More
importantly, I'm losing updates to records that were updated prior to the
database backup! The inserted records are still there, but the updates
appear to have been rolled back (at least in one table where I've verifiied
the problem.) I'm running some more checks now and I've set the recovery
model to Full, but I'm interested in knowing if anyone else has seen anything
like this before.
hi,
Donald Welker wrote:
> I'm running MSDE 2000 SP4 on Win2KAS SP4 (fully patched). I have a
> maintenance plan that backs up the database at 18:30 and backs up the
> transaction log at midnight. Apparently the transaction log backups
> have been failing because the database is set to the Simple recovery
> model.
and this is a standard behavior :D

>More importantly, I'm losing updates to records that were
> updated prior to the database backup! The inserted records are still
> there, but the updates appear to have been rolled back (at least in
> one table where I've verifiied the problem.) I'm running some more
> checks now and I've set the recovery model to Full, but I'm
> interested in knowing if anyone else has seen anything like this
> before.
I do not think this has something to do with backup operation, even if
performed via Maintenance Plan (unfortunately a little buggy tool)... it's
actually not possible (and the simple recovery model is no guilty at all)
for a backup task to rollback update statements while committing insert...
the problem must be else where...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
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
|||Overnight testing has isolated the occurence of the change to running the
transaction log backup at midnight. The data is fine at 23:55:01 and bad at
00:00:01. However it seems that I cannot reproduce this result even by
rescheduling the maintenance plan job steps to run in sequence during the
day. I've just dropped and replaced the maintenance plan (with a better
schedule) to see if that has any effect. Also, the new plan only backs up
this database rather than "All User Databases" (might also be related.)
"Andrea Montanari" wrote:

> hi,
> Donald Welker wrote:
> and this is a standard behavior :D
I've noticed.

> I do not think this has something to do with backup operation, even if
> performed via Maintenance Plan (unfortunately a little buggy tool)... it's
> actually not possible (and the simple recovery model is no guilty at all)
> for a backup task to rollback update statements while committing insert...
> the problem must be else where...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> 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
It's happened three nights in a row so far, so it's certainly possible. It
still happened last night with Full recovery model, it just didn't record an
error anymore.