Maintenance plan does not run

Hi - I have a strange situation where the Maintenance plan on a SQL 7, build 961 will not generate .BAK and .TRN files based on the maintenance plan (or at all). I have tried deleteing and recreating the maintenance plan, but, according to the system log, and to maintenance plan logs, the entire maintenance plan is not launching any tasks. However the application log whoes all the jobs as failing, but gives no furhter details.

However I have another server running build 623 that is working fine.

Any ideas from anyone? All help appreciated.Why use a maintenance plan - they give lots of problems.
Just schedule a backup job or jobs.
Have them call a stored procedure and put the backup statement in that.|||Here's the error from Eventlog:

SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'Webmon1'' (0x42E6C45B360CD611935D0002B330ABF9) - Status: Failed - Invoked on: 2/8/02 4:10:46 PM - Message: The job failed. The Job was invoked by User XXXXXX\Administrator. The last step to run was step 1 (Step 1).|||What happens if you try the same backup using a backup command?

Maintenance plan

From my maintenance plan backup, i ve setup MY BACKUP and the transaction lo
g
backup to remove files (BAK and TRN) older than 1 days but it doesn't delete
them anymore. It
usedjto work fine until 2 weeks . Now , ive to remove them manually as it
fills the space quickly
Im running SQL2000 on windows server 2003
any ideas how to fix this '
RickSee if this blurb from Bill and the associated link help:
-- Log files don't delete --
http://support.microsoft.com/defaul...b;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Andrew J. Kelly SQL MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:FF1D6886-671E-4162-8E2C-7894E5345642@.microsoft.com...
> From my maintenance plan backup, i ve setup MY BACKUP and the transaction
> log
> backup to remove files (BAK and TRN) older than 1 days but it doesn't
> delete
> them anymore. It
> usedjto work fine until 2 weeks . Now , ive to remove them manually as
> it
> fills the space quickly
> Im running SQL2000 on windows server 2003
> any ideas how to fix this '
> --
> Rick|||Bill, this was a very good troubleshooting list. Thanks.
"Andrew J. Kelly" wrote:

> See if this blurb from Bill and the associated link help:
>
> -- Log files don't delete --
> http://support.microsoft.com/defaul...b;en-us;Q303292
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). Thi
s
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer t
o
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to conside
r
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it woul
d
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed t
o
> local drive), then you will need to modify the above (with respect to wher
e
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Rick" <Rick@.discussions.microsoft.com> wrote in message
> news:FF1D6886-671E-4162-8E2C-7894E5345642@.microsoft.com...
>
>sql

Maintenance plan

From my maintenance plan backup, i ve setup MY BACKUP and the transaction log
backup to remove files (BAK and TRN) older than 1 days but it doesn't delete
them anymore. It
usedjto work fine until 2 weeks . Now , ive to remove them manually as it
fills the space quickly
Im running SQL2000 on windows server 2003
any ideas how to fix this ?
Rick
See if this blurb from Bill and the associated link help:
-- Log files don't delete --
http://support.microsoft.com/default...;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Andrew J. Kelly SQL MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:FF1D6886-671E-4162-8E2C-7894E5345642@.microsoft.com...
> From my maintenance plan backup, i ve setup MY BACKUP and the transaction
> log
> backup to remove files (BAK and TRN) older than 1 days but it doesn't
> delete
> them anymore. It
> usedjto work fine until 2 weeks . Now , ive to remove them manually as
> it
> fills the space quickly
> Im running SQL2000 on windows server 2003
> any ideas how to fix this ?
> --
> Rick
|||Bill, this was a very good troubleshooting list. Thanks.
"Andrew J. Kelly" wrote:

> See if this blurb from Bill and the associated link help:
>
> -- Log files don't delete --
> http://support.microsoft.com/default...;en-us;Q303292
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed to
> local drive), then you will need to modify the above (with respect to where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Rick" <Rick@.discussions.microsoft.com> wrote in message
> news:FF1D6886-671E-4162-8E2C-7894E5345642@.microsoft.com...
>
>
sql

Maintenance plan

From my maintenance plan backup, i ve setup MY BACKUP and the transaction log
backup to remove files (BAK and TRN) older than 1 days but it doesn't delete
them anymore. It
usedjto work fine until 2 weeks . Now , ive to remove them manually as it
fills the space quickly
Im running SQL2000 on windows server 2003
any ideas how to fix this '
--
RickSee if this blurb from Bill and the associated link help:
-- Log files don't delete --
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Andrew J. Kelly SQL MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:FF1D6886-671E-4162-8E2C-7894E5345642@.microsoft.com...
> From my maintenance plan backup, i ve setup MY BACKUP and the transaction
> log
> backup to remove files (BAK and TRN) older than 1 days but it doesn't
> delete
> them anymore. It
> usedjto work fine until 2 weeks . Now , ive to remove them manually as
> it
> fills the space quickly
> Im running SQL2000 on windows server 2003
> any ideas how to fix this '
> --
> Rick

Maintanance Plan and xp_delete_file

Hi there,

i have a mp that deletes old bak, trn and txt files. It works well for bak and trn files, but it never deletes any of the report files created by other MPs.

I also can't delete any of the report files by executing the following statement (as sa or with windows login as a local admin)

EXECUTE master.dbo.xp_delete_file 1,N'D:\Backup\log',N'txt'

System ist SQL Server 2005 SP1 German

Any ideas?

Thanks in advance

regards
tobias
The path needs to end with a '\'|||As that referred XP is an undocumented XP there might be still few features are not enabled properly, this has been logged under MS Connect http://connect.microsoft.com/SQLServer/Feedback and wait for the solution by referring to the link above.|||

Thanks for reporting the issue.

Please file a defect report for this issue at http://connect.microsoft.com/sqlserver. Customer reported issues are used to prioritize future work.

Thanks,

Sirish

|||

As an FYI for anyone else who is experiencing problems with this - one issue is now documented at http://support.microsoft.com/kb/938085

The solution seems to involve writing your own deletion script. (Many examples can be found online).

|||

This problem has been addressed in the June Cumulative Update Hotfix, available from Microsoft Customer Support.

I have asked the owner of the article you cite to update it to reflect the availability of a fix.

Thanks,

jkh

Maintanance Plan and xp_delete_file

Hi there,

i have a mp that deletes old bak, trn and txt files. It works well for bak and trn files, but it never deletes any of the report files created by other MPs.

I also can't delete any of the report files by executing the following statement (as sa or with windows login as a local admin)

EXECUTE master.dbo.xp_delete_file 1,N'D:\Backup\log',N'txt'

System ist SQL Server 2005 SP1 German

Any ideas?

Thanks in advance

regards
tobiasThe path needs to end with a '\'|||As that referred XP is an undocumented XP there might be still few features are not enabled properly, this has been logged under MS Connect http://connect.microsoft.com/SQLServer/Feedback and wait for the solution by referring to the link above.|||

Thanks for reporting the issue.

Please file a defect report for this issue at http://connect.microsoft.com/sqlserver. Customer reported issues are used to prioritize future work.

Thanks,

Sirish

|||

As an FYI for anyone else who is experiencing problems with this - one issue is now documented at http://support.microsoft.com/kb/938085

The solution seems to involve writing your own deletion script. (Many examples can be found online).

|||

This problem has been addressed in the June Cumulative Update Hotfix, available from Microsoft Customer Support.

I have asked the owner of the article you cite to update it to reflect the availability of a fix.

Thanks,

jkh

sql

Maintanance Plan and xp_delete_file

Hi there,

i have a mp that deletes old bak, trn and txt files. It works well for bak and trn files, but it never deletes any of the report files created by other MPs.

I also can't delete any of the report files by executing the following statement (as sa or with windows login as a local admin)

EXECUTE master.dbo.xp_delete_file 1,N'D:\Backup\log',N'txt'

System ist SQL Server 2005 SP1 German

Any ideas?

Thanks in advance

regards
tobiasThe path needs to end with a '\'|||As that referred XP is an undocumented XP there might be still few features are not enabled properly, this has been logged under MS Connect http://connect.microsoft.com/SQLServer/Feedback and wait for the solution by referring to the link above.|||

Thanks for reporting the issue.

Please file a defect report for this issue at http://connect.microsoft.com/sqlserver. Customer reported issues are used to prioritize future work.

Thanks,

Sirish

|||

As an FYI for anyone else who is experiencing problems with this - one issue is now documented at http://support.microsoft.com/kb/938085

The solution seems to involve writing your own deletion script. (Many examples can be found online).

|||

This problem has been addressed in the June Cumulative Update Hotfix, available from Microsoft Customer Support.

I have asked the owner of the article you cite to update it to reflect the availability of a fix.

Thanks,

jkh

Maintanance Plan and xp_delete_file

Hi there,

i have a mp that deletes old bak, trn and txt files. It works well for bak and trn files, but it never deletes any of the report files created by other MPs.

I also can't delete any of the report files by executing the following statement (as sa or with windows login as a local admin)

EXECUTE master.dbo.xp_delete_file 1,N'D:\Backup\log',N'txt'

System ist SQL Server 2005 SP1 German

Any ideas?

Thanks in advance

regards
tobiasThe path needs to end with a '\'|||As that referred XP is an undocumented XP there might be still few features are not enabled properly, this has been logged under MS Connect http://connect.microsoft.com/SQLServer/Feedback and wait for the solution by referring to the link above.|||

Thanks for reporting the issue.

Please file a defect report for this issue at http://connect.microsoft.com/sqlserver. Customer reported issues are used to prioritize future work.

Thanks,

Sirish

|||

As an FYI for anyone else who is experiencing problems with this - one issue is now documented at http://support.microsoft.com/kb/938085

The solution seems to involve writing your own deletion script. (Many examples can be found online).

|||

This problem has been addressed in the June Cumulative Update Hotfix, available from Microsoft Customer Support.

I have asked the owner of the article you cite to update it to reflect the availability of a fix.

Thanks,

jkh

Maint plan fails to backup DB or TRN, odd error

When I run my maint plan on our new server I get a...
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
This is a bit mysterious for three reasons:
1) There should be nothing to repair: it's a brand new DB
2) I've told it not to bother doing integrity checks before backups in the
plan (it was on, I turned it off today)
3) Using All Tasks -> Backup Database works fine (fast too!)
Any ideas?
For this error, the CHECK BEFORE BACKUP is immaterial. The question is whether or not you have the Integrity Check jobs created. This part of the SQLMAINT utility will execute the DBCC CHECKDB statements. For this to execute, regardless if you've selected the ATTEMPT TO FIX option, the database will need to be put in SINGLE_USER mode. SQLMAINT will not be able to do this if there are persistent connections to the database that can not be dropped.
For a few of our systems, we have had to run a step in the job prior to SQLMAINT step to execute an ALTER DATABASE SET OFFLINE/ONLINE WITH ROLLBACK IMMEDIATE statement.
Sincerely,
Anthony Thomas

"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message news:BCDA29C3-CEE3-4FA8-83CB-96151AF34A15@.microsoft.com...
When I run my maint plan on our new server I get a...
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
This is a bit mysterious for three reasons:
1) There should be nothing to repair: it's a brand new DB
2) I've told it not to bother doing integrity checks before backups in the
plan (it was on, I turned it off today)
3) Using All Tasks -> Backup Database works fine (fast too!)
Any ideas?|||"AnthonyThomas" wrote:

> For this error, the CHECK BEFORE BACKUP is immaterial.
Ok, that answers that one.

> The question is whether or not you have the Integrity Check jobs created.
I do. I assume that this is not the same thing as CHECK BEFORE BACKUP then?
That's what I assumed it was -- and I don't think it was too dumb an
assumption.

> This part of the SQLMAINT utility will execute the DBCC CHECKDB
> statements. For this to execute, regardless if you've selected the
> ATTEMPT TO FIX option, the database will need to be put in
> SINGLE_USER mode.
This seems odd though. In this case the only connection is me on SQL
Manager. It would seem very stupid if the very tool used to start these jobs
makes them fail. It also doesn't explain why it failed last night, when no
one should have been on (although perhaps inactive Access connections).
What do I do? Simply delete this job?

> For a few of our systems, we have had to run a step in the job
> prior to SQLMAINT step to execute an ALTER DATABASE SET
> OFFLINE/ONLINE WITH ROLLBACK IMMEDIATE statement.
Ok, let me try that.
Seems silly I have to do this at all -- you'd think it would come out of the
box set to "work right".
|||"AnthonyThomas" wrote:
Reading between the lines of your message I was able to get the transaction
log to backup by turning off the step in my maint plan. So now it works OK at
least, but I stand by my opinion that this should either not be a problem in
the first place, or should be MUCH more obvious as a potential issue.
But now that task doesn't run. It seems to me that it would be a good idea
to have it run every so often! Is there some "natural" time when I the
database will be in the right mode? Perhaps after a backup?

> For this error, the CHECK BEFORE BACKUP is immaterial. The question is whether or not you have the Integrity Check jobs created. This part of the SQLMAINT utility will execute the DBCC CHECKDB statements. For this to execute, regardless if you've sel
ected the ATTEMPT TO FIX option, the database will need to be put in SINGLE_USER mode. SQLMAINT will not be able to do this if there are persistent connections to the database that can not be dropped.
> For a few of our systems, we have had to run a step in the job prior to SQLMAINT step to execute an ALTER DATABASE SET OFFLINE/ONLINE WITH ROLLBACK IMMEDIATE statement.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message news:BCDA29C3-CEE3-4FA8-83CB-96151AF34A15@.microsoft.com...
> When I run my maint plan on our new server I get a...
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> This is a bit mysterious for three reasons:
> 1) There should be nothing to repair: it's a brand new DB
> 2) I've told it not to bother doing integrity checks before backups in the
> plan (it was on, I turned it off today)
> 3) Using All Tasks -> Backup Database works fine (fast too!)
> Any ideas?
|||I recommend removing the "repair minor problems" option from your integrity checks. If you do have
corruption problems, you want to know about it and not try to sweep it under the carpet. This is so
you can do root cause analysis of why you have those problems. The option is said to be removed from
next version of SQL server, btw.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:FC849EE8-986F-4F16-A044-BBFDC01509B9@.microsoft.com...[vbcol=seagreen]
> "AnthonyThomas" wrote:
> Reading between the lines of your message I was able to get the transaction
> log to backup by turning off the step in my maint plan. So now it works OK at
> least, but I stand by my opinion that this should either not be a problem in
> the first place, or should be MUCH more obvious as a potential issue.
> But now that task doesn't run. It seems to me that it would be a good idea
> to have it run every so often! Is there some "natural" time when I the
> database will be in the right mode? Perhaps after a backup?
>
the Integrity Check jobs created. This part of the SQLMAINT utility will execute the DBCC CHECKDB
statements. For this to execute, regardless if you've selected the ATTEMPT TO FIX option, the
database will need to be put in SINGLE_USER mode. SQLMAINT will not be able to do this if there are
persistent connections to the database that can not be dropped.[vbcol=seagreen]
an ALTER DATABASE SET OFFLINE/ONLINE WITH ROLLBACK IMMEDIATE statement.[vbcol=seagreen]
news:BCDA29C3-CEE3-4FA8-83CB-96151AF34A15@.microsoft.com...[vbcol=seagreen]

Maint plan fails to backup DB or TRN, odd error

When I run my maint plan on our new server I get a...
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not
processed. Database needs to be in single user mode.
This is a bit mysterious for three reasons:
1) There should be nothing to repair: it's a brand new DB
2) I've told it not to bother doing integrity checks before backups in the
plan (it was on, I turned it off today)
3) Using All Tasks -> Backup Database works fine (fast too!)
Any ideas?For this error, the CHECK BEFORE BACKUP is immaterial. The question is whet
her or not you have the Integrity Check jobs created. This part of the SQLM
AINT utility will execute the DBCC CHECKDB statements. For this to execute,
regardless if you've selected the ATTEMPT TO FIX option, the database will
need to be put in SINGLE_USER mode. SQLMAINT will not be able to do this if
there are persistent connections to the database that can not be dropped.
For a few of our systems, we have had to run a step in the job prior to SQLM
AINT step to execute an ALTER DATABASE SET OFFLINE/ONLINE WITH ROLLBACK IMME
DIATE statement.
Sincerely,
Anthony Thomas
--
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in mess
age news:BCDA29C3-CEE3-4FA8-83CB-96151AF34A15@.microsoft.com...
When I run my maint plan on our new server I get a...
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not
processed. Database needs to be in single user mode.
This is a bit mysterious for three reasons:
1) There should be nothing to repair: it's a brand new DB
2) I've told it not to bother doing integrity checks before backups in the
plan (it was on, I turned it off today)
3) Using All Tasks -> Backup Database works fine (fast too!)
Any ideas?|||"AnthonyThomas" wrote:

> For this error, the CHECK BEFORE BACKUP is immaterial.
Ok, that answers that one.

> The question is whether or not you have the Integrity Check jobs created.
I do. I assume that this is not the same thing as CHECK BEFORE BACKUP then?
That's what I assumed it was -- and I don't think it was too dumb an
assumption.

> This part of the SQLMAINT utility will execute the DBCC CHECKDB
> statements. For this to execute, regardless if you've selected the
> ATTEMPT TO FIX option, the database will need to be put in
> SINGLE_USER mode.
This seems odd though. In this case the only connection is me on SQL
Manager. It would seem very stupid if the very tool used to start these jobs
makes them fail. It also doesn't explain why it failed last night, when no
one should have been on (although perhaps inactive Access connections).
What do I do? Simply delete this job?

> For a few of our systems, we have had to run a step in the job
> prior to SQLMAINT step to execute an ALTER DATABASE SET
> OFFLINE/ONLINE WITH ROLLBACK IMMEDIATE statement.
Ok, let me try that.
Seems silly I have to do this at all -- you'd think it would come out of the
box set to "work right".|||"AnthonyThomas" wrote:
Reading between the lines of your message I was able to get the transaction
log to backup by turning off the step in my maint plan. So now it works OK a
t
least, but I stand by my opinion that this should either not be a problem in
the first place, or should be MUCH more obvious as a potential issue.
But now that task doesn't run. It seems to me that it would be a good idea
to have it run every so often! Is there some "natural" time when I the
database will be in the right mode? Perhaps after a backup?

> For this error, the CHECK BEFORE BACKUP is immaterial. The question is whether or
not you have the Integrity Check jobs created. This part of the SQLMAINT utility w
ill execute the DBCC CHECKDB statements. For this to execute, regardless if you've
sel
ected the ATTEMPT TO FIX option, the database will need to be put in SINGLE_USER mode. SQLM
AINT will not be able to do this if there are persistent connections to the database that ca
n not be dropped.
> For a few of our systems, we have had to run a step in the job prior to SQ
LMAINT step to execute an ALTER DATABASE SET OFFLINE/ONLINE WITH ROLLBACK IM
MEDIATE statement.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in me
ssage news:BCDA29C3-CEE3-4FA8-83CB-96151AF34A15@.microsoft.com...
> When I run my maint plan on our new server I get a...
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statem
ent not
> processed. Database needs to be in single user mode.
> This is a bit mysterious for three reasons:
> 1) There should be nothing to repair: it's a brand new DB
> 2) I've told it not to bother doing integrity checks before backups in t
he
> plan (it was on, I turned it off today)
> 3) Using All Tasks -> Backup Database works fine (fast too!)
> Any ideas?|||I recommend removing the "repair minor problems" option from your integrity
checks. If you do have
corruption problems, you want to know about it and not try to sweep it under
the carpet. This is so
you can do root cause analysis of why you have those problems. The option is
said to be removed from
next version of SQL server, btw.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in messag
e
news:FC849EE8-986F-4F16-A044-BBFDC01509B9@.microsoft.com...[vbcol=seagreen]
> "AnthonyThomas" wrote:
> Reading between the lines of your message I was able to get the transactio
n
> log to backup by turning off the step in my maint plan. So now it works OK
at
> least, but I stand by my opinion that this should either not be a problem
in
> the first place, or should be MUCH more obvious as a potential issue.
> But now that task doesn't run. It seems to me that it would be a good idea
> to have it run every so often! Is there some "natural" time when I the
> database will be in the right mode? Perhaps after a backup?
>
the Integrity Check jobs created. This part of the SQLMAINT utility will ex
ecute the DBCC CHECKDB
statements. For this to execute, regardless if you've selected the ATTEMPT
TO FIX option, the
database will need to be put in SINGLE_USER mode. SQLMAINT will not be able
to do this if there are
persistent connections to the database that can not be dropped.[vbcol=seagreen]
an ALTER DATABASE SET OFFLINE/ONLINE WITH ROLLBACK IMMEDIATE statement.[vbcol=seagreen]
news:BCDA29C3-CEE3-4FA8-83CB-96151AF34A15@.microsoft.com...[vbcol=seagreen]

Maint plan fails to backup DB or TRN, odd error

When I run my maint plan on our new server I get a...
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
This is a bit mysterious for three reasons:
1) There should be nothing to repair: it's a brand new DB
2) I've told it not to bother doing integrity checks before backups in the
plan (it was on, I turned it off today)
3) Using All Tasks -> Backup Database works fine (fast too!)
Any ideas?"AnthonyThomas" wrote:
> For this error, the CHECK BEFORE BACKUP is immaterial.
Ok, that answers that one.
> The question is whether or not you have the Integrity Check jobs created.
I do. I assume that this is not the same thing as CHECK BEFORE BACKUP then?
That's what I assumed it was -- and I don't think it was too dumb an
assumption.
> This part of the SQLMAINT utility will execute the DBCC CHECKDB
> statements. For this to execute, regardless if you've selected the
> ATTEMPT TO FIX option, the database will need to be put in
> SINGLE_USER mode.
This seems odd though. In this case the only connection is me on SQL
Manager. It would seem very stupid if the very tool used to start these jobs
makes them fail. It also doesn't explain why it failed last night, when no
one should have been on (although perhaps inactive Access connections).
What do I do? Simply delete this job?
> For a few of our systems, we have had to run a step in the job
> prior to SQLMAINT step to execute an ALTER DATABASE SET
> OFFLINE/ONLINE WITH ROLLBACK IMMEDIATE statement.
Ok, let me try that.
Seems silly I have to do this at all -- you'd think it would come out of the
box set to "work right".|||"AnthonyThomas" wrote:
Reading between the lines of your message I was able to get the transaction
log to backup by turning off the step in my maint plan. So now it works OK at
least, but I stand by my opinion that this should either not be a problem in
the first place, or should be MUCH more obvious as a potential issue.
But now that task doesn't run. It seems to me that it would be a good idea
to have it run every so often! Is there some "natural" time when I the
database will be in the right mode? Perhaps after a backup?
> For this error, the CHECK BEFORE BACKUP is immaterial. The question is whether or not you have the Integrity Check jobs created. This part of the SQLMAINT utility will execute the DBCC CHECKDB statements. For this to execute, regardless if you've selected the ATTEMPT TO FIX option, the database will need to be put in SINGLE_USER mode. SQLMAINT will not be able to do this if there are persistent connections to the database that can not be dropped.
> For a few of our systems, we have had to run a step in the job prior to SQLMAINT step to execute an ALTER DATABASE SET OFFLINE/ONLINE WITH ROLLBACK IMMEDIATE statement.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message news:BCDA29C3-CEE3-4FA8-83CB-96151AF34A15@.microsoft.com...
> When I run my maint plan on our new server I get a...
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> This is a bit mysterious for three reasons:
> 1) There should be nothing to repair: it's a brand new DB
> 2) I've told it not to bother doing integrity checks before backups in the
> plan (it was on, I turned it off today)
> 3) Using All Tasks -> Backup Database works fine (fast too!)
> Any ideas?|||I recommend removing the "repair minor problems" option from your integrity checks. If you do have
corruption problems, you want to know about it and not try to sweep it under the carpet. This is so
you can do root cause analysis of why you have those problems. The option is said to be removed from
next version of SQL server, btw.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:FC849EE8-986F-4F16-A044-BBFDC01509B9@.microsoft.com...
> "AnthonyThomas" wrote:
> Reading between the lines of your message I was able to get the transaction
> log to backup by turning off the step in my maint plan. So now it works OK at
> least, but I stand by my opinion that this should either not be a problem in
> the first place, or should be MUCH more obvious as a potential issue.
> But now that task doesn't run. It seems to me that it would be a good idea
> to have it run every so often! Is there some "natural" time when I the
> database will be in the right mode? Perhaps after a backup?
>
> > For this error, the CHECK BEFORE BACKUP is immaterial. The question is whether or not you have
the Integrity Check jobs created. This part of the SQLMAINT utility will execute the DBCC CHECKDB
statements. For this to execute, regardless if you've selected the ATTEMPT TO FIX option, the
database will need to be put in SINGLE_USER mode. SQLMAINT will not be able to do this if there are
persistent connections to the database that can not be dropped.
> >
> > For a few of our systems, we have had to run a step in the job prior to SQLMAINT step to execute
an ALTER DATABASE SET OFFLINE/ONLINE WITH ROLLBACK IMMEDIATE statement.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:BCDA29C3-CEE3-4FA8-83CB-96151AF34A15@.microsoft.com...
> > When I run my maint plan on our new server I get a...
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> > processed. Database needs to be in single user mode.
> >
> > This is a bit mysterious for three reasons:
> >
> > 1) There should be nothing to repair: it's a brand new DB
> > 2) I've told it not to bother doing integrity checks before backups in the
> > plan (it was on, I turned it off today)
> > 3) Using All Tasks -> Backup Database works fine (fast too!)
> >
> > Any ideas?