Maintenance Plan for SQL Server 2005 64-bit doesn't work!

Hi all,
We have a SQL Server 2005 64-bit, and recently I upgrade from build 3042 to 3054 and I try to do a maintenance plan for transaction logs(TL) backup, including cleanup for two days (have full backup every night).

Problem I have is that I want the TL files to dump in a different location(due to disk space), so I put in the UNC path in the "Create a backup file for every database - >Folder:\\FileServer\TL\DBLogs"

NB: if using the local drives, it work

Check List
Security:
- The account that I used to create the plan is an sa account
- The location that I dump the TL files, I have full access to the folder

SQL Statement:
exec xp_cmdshell 'dir \FileServer\TL\DBLogs' (it list all files)

Is this a bug for 64-bit? because I can do this on SQL Server 2005 32-bit and it's work perfectly

Resolved: permission problems, u need to give permission to the folder for SQL Server Agent account

Maintenance Plan Failure

I have a maintenance plan setup that among other tasks is supposed to backup
Transaction Logs every 3 hours and then delete Log files that are older than
2 days. However, this job keeps failing when it tries to delete the older
files. It makes the 3 hour backups ok, but then shows a failure for the
delete action inside Enterprise Manager > Server > Maintenance > Jobs. Any
idea what could be happening here? It used to work fine. The only error I
get is posted below (though probably not much help).
*************
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 12/17/2004
Time: 2:00:06 PM
User: N/A
Computer: SERVER
Description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'Backup Maintenance Plan'' (0x205B61B1C1A7D54BA426DF0C810C8F05) - Status:
Failed - Invoked on: 2004-12-17 14:00:00 - Message: The job failed. The Job
was invoked by Schedule 43 (Schedule 1). The last step to run was step 1
(Step 1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
--
Brandon
IT Director
Presentations Direct - Office Equipment & Supplies
http://www.presentationsdirect.com"Brandon S." wrote:
I don't know if this is what is causing your problem, but we had our backup
directory shared and that was enough to stop the deletes.|||Thanks, but that's not the case here.
--
Brandon
IT Director
Presentations Direct - Office Equipment & Supplies
http://www.presentationsdirect.com
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:4C8DBE2F-99D8-4FCD-85DB-13609E06EC84@.microsoft.com...
> "Brandon S." wrote:
> I don't know if this is what is causing your problem, but we had our
> backup
> directory shared and that was enough to stop the deletes.|||It is usually due to some other process locking the file or dir such as a
tape device. But this blurb from Bill at MS does a pretty good job of
outlining the issues:
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
"Brandon S." <bsmith@.presentationsdirect.nospam.com> wrote in message
news:uE0Ol3H5EHA.2804@.TK2MSFTNGP15.phx.gbl...
>I have a maintenance plan setup that among other tasks is supposed to
>backup Transaction Logs every 3 hours and then delete Log files that are
>older than 2 days. However, this job keeps failing when it tries to delete
>the older files. It makes the 3 hour backups ok, but then shows a failure
>for the delete action inside Enterprise Manager > Server > Maintenance >
>Jobs. Any idea what could be happening here? It used to work fine. The
>only error I get is posted below (though probably not much help).
> *************
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 12/17/2004
> Time: 2:00:06 PM
> User: N/A
> Computer: SERVER
> Description:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
> Plan 'Backup Maintenance Plan'' (0x205B61B1C1A7D54BA426DF0C810C8F05) -
> Status: Failed - Invoked on: 2004-12-17 14:00:00 - Message: The job
> failed. The Job was invoked by Schedule 43 (Schedule 1). The last step
> to run was step 1 (Step 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> --
> Brandon
> IT Director
> Presentations Direct - Office Equipment & Supplies
> http://www.presentationsdirect.com
>

Maintenance plan "Backup transaction Logs" running out of control

I have a miantenance plan that backs up transaction logs to another Server for the use in Log shipping. this has been running fine for months now, but the other day it has been spitting transaction logs that are 2.5 G over and over. about 20 G so far.

I tried to delete this plan and create a new one and reset Log shipping but it is still spitting out these 2.5 G files. I am really not sure what to look at next.

thanks for any help in advance.Why use a maintenance plan for this?
It is really easy to implement tsql and scheduled tasks and then you will at least know what it is doing.sql

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

Maintainance Plan - Transaction Log Backups Not Deleted

Hi
Ive got a Maintainance Plan set up for all my databases.
The Database backup part works fine but I have a problem
with the Transaction log backup. I have set it to delete
old TRN files after 4 days. However it does not to this.
It creates a new TRN file, but does not delete the old
ones. I have looked in the job history - it just says the
job failed. I have checked the NTFS permissions - they
seem ok. Any ideas
TIA
RobProblem is that one of your databases recovery model is
set to simple. (microsoft knowledge base article 303292)
go to enterprise manager| click down to databases| right
click on each database in your maintenance plan | select
properties| select options| each one should NOT be simple
select either full or bulk
>--Original Message--
>Hi
>Ive got a Maintainance Plan set up for all my databases.
>The Database backup part works fine but I have a problem
>with the Transaction log backup. I have set it to delete
>old TRN files after 4 days. However it does not to this.
>It creates a new TRN file, but does not delete the old
>ones. I have looked in the job history - it just says
the
>job failed. I have checked the NTFS permissions - they
>seem ok. Any ideas
>TIA
>Rob
>.
>|||You pretty much just get that generic message when you use
the maintenance plans. To find the specific errors, you need
to go to the maintenance plans, right click on the plan that
is failing and select Database Maintenance Plan History.
The problem with deleting the backups xx days old is most
likely related to one of the issues that Allan Mitchell
listed for you in his post - permissions or a sharing
violation.
-Sue
On 22 Sep 2003 04:36:22 -0700, rss@.abasoft.co.uk (Rob Smith)
wrote:
>Thanks for that tip but, each database was set to Full
>In the step detail in job history I get this
>sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
>Rob
>
>"bob schwartz" <robtschwartz@.sbcglobal.net> wrote in message news:<01c801c37c7f$411100b0$a401280a@.phx.gbl>...
>> Problem is that one of your databases recovery model is
>> set to simple. (microsoft knowledge base article 303292)
>> go to enterprise manager| click down to databases| right
>> click on each database in your maintenance plan | select
>> properties| select options| each one should NOT be simple
>> select either full or bulk
>>|||I had tried Allans advice, but it didnt seem to make a difference who
was running it. In the end I have got it working by going into SQL
Agent/Jobs/Transaction Log Backup/Properties/Steps and changing the
database from master to one of my user DBs. This ran ok with no errors
and removed the old files.
Thanks to everyone who helped with this
Rob
Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<8n2vmvk74vhupjjfj9h96lrjpn0kcsvvd0@.4ax.com>...
> You pretty much just get that generic message when you use
> the maintenance plans. To find the specific errors, you need
> to go to the maintenance plans, right click on the plan that
> is failing and select Database Maintenance Plan History.
> The problem with deleting the backups xx days old is most
> likely related to one of the issues that Allan Mitchell
> listed for you in his post - permissions or a sharing
> violation.
> -Sue
> On 22 Sep 2003 04:36:22 -0700, rss@.abasoft.co.uk (Rob Smith)
> wrote:
> >Thanks for that tip but, each database was set to Full
> >
> >In the step detail in job history I get this
> >
> >sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
> >
> >Rob
> >
> >
> >"bob schwartz" <robtschwartz@.sbcglobal.net> wrote in message news:<01c801c37c7f$411100b0$a401280a@.phx.gbl>...
> >> Problem is that one of your databases recovery model is
> >> set to simple. (microsoft knowledge base article 303292)
> >> go to enterprise manager| click down to databases| right
> >> click on each database in your maintenance plan | select
> >> properties| select options| each one should NOT be simple
> >> select either full or bulk
> >>
> >>

Maintain Transaction even if need to loop through multiple data

I have a ADP app that executes several SPs with a click of a button
Now I want to combine these SP into 1 transaction so that it can
rollback even if one SP fails.
Problem is several SP need to process recordsets in vba so I have made
while loops in vba for them. I do this to avoid cursors as they no good
performers. My choice of avoiding cursors has broken the 'transaction
boundary' therefore I cannot do a roll back for these. How can I still
use store procedures but still maintain performance and be able to roll
back all the SPs if any of them fails?Have you considered rewriting the procs in set based fashion such that
you don't need to call them in a loop? A for each loop is really just a
cursor by another name. Without seeing what you are doing it's
difficult to advise otherwise.
David Portas
SQL Server MVP
--|||Thanks for the hint -> set based technique.
I've found what I've been looking for:
http://groups.google.com/group/micr...8924a2b73888ca4|||I got another problem.
I got this dynamic sp:
create procedure spFindData()
as
declare @.statement varchar(255),@.intYear int,intCount int
set @.intyear = 2010
while @.intyear >=2004
begin
select @.statement = "select count(*) from sales' + @.intYear
-- I want to assign the result of @.statement to @.intCount, how?
if (@.intCount>0)
begin
return @.intYear
end
@.intYear = @.intYear -1
end

Maintain Transaction even if need to loop through multiple data

I have a ADP app that executes several SPs with a click of a button
Now I want to combine these SP into 1 transaction so that it can
rollback even if one SP fails.
Problem is several SP need to process recordsets in vba so I have made
while loops in vba for them. I do this to avoid cursors as they no good
performers. My choice of avoiding cursors has broken the 'transaction
boundary' therefore I cannot do a roll back for these. How can I still
use store procedures but still maintain performance and be able to roll
back all the SPs if any of them fails?Have you considered rewriting the procs in set based fashion such that
you don't need to call them in a loop? A for each loop is really just a
cursor by another name. Without seeing what you are doing it's
difficult to advise otherwise.
--
David Portas
SQL Server MVP
--|||Thanks for the hint -> set based technique.
I've found what I've been looking for:
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/eb09130dcb79c392/d8924a2b73888ca4?lnk=st&q=stored+procedure+set+based&rnum=3&hl=en#d8924a2b73888ca4|||I got another problem.
I got this dynamic sp:
create procedure spFindData()
as
declare @.statement varchar(255),@.intYear int,intCount int
set @.intyear = 2010
while @.intyear >=2004
begin
select @.statement = "select count(*) from sales' + @.intYear
-- I want to assign the result of @.statement to @.intCount, how?
if (@.intCount>0)
begin
return @.intYear
end
@.intYear = @.intYear -1
end

Maintain Transaction even if need to loop through multiple data

I have a ADP app that executes several SPs with a click of a button
Now I want to combine these SP into 1 transaction so that it can
rollback even if one SP fails.
Problem is several SP need to process recordsets in vba so I have made
while loops in vba for them. I do this to avoid cursors as they no good
performers. My choice of avoiding cursors has broken the 'transaction
boundary' therefore I cannot do a roll back for these. How can I still
use store procedures but still maintain performance and be able to roll
back all the SPs if any of them fails?
Have you considered rewriting the procs in set based fashion such that
you don't need to call them in a loop? A for each loop is really just a
cursor by another name. Without seeing what you are doing it's
difficult to advise otherwise.
David Portas
SQL Server MVP
|||Thanks for the hint -> set based technique.
I've found what I've been looking for:
http://groups.google.com/group/micro...924a2b73888ca4
|||I got another problem.
I got this dynamic sp:
create procedure spFindData()
as
declare @.statement varchar(255),@.intYear int,intCount int
set @.intyear = 2010
while @.intyear >=2004
begin
select @.statement = "select count(*) from sales' + @.intYear
-- I want to assign the result of @.statement to @.intCount, how?
if (@.intCount>0)
begin
return @.intYear
end
@.intYear = @.intYear -1
end

Maint plan: integerity check & backup

In the maintenance plans Integrity tab there is a check box for: "Preform
these tests before backing up the database, or transaction logs".
I want to be sure I understand this. If I check this box, does that mean an
integrity check (and potentially a repair) will run ANYTIME a database, OR
transaction log backup is run, as part of the backup procedure and that one
would not need to set a schedule for the integrity checks?
Given the posibility of the function locking the database (to do a repair),
I don't know if this is a good idea, or not.
JayKon wrote:
> In the maintenance plans Integrity tab there is a check box for: "Preform
> these tests before backing up the database, or transaction logs".
> I want to be sure I understand this. If I check this box, does that mean an
> integrity check (and potentially a repair) will run ANYTIME a database, OR
> transaction log backup is run, as part of the backup procedure and that one
> would not need to set a schedule for the integrity checks?
> Given the posibility of the function locking the database (to do a repair),
> I don't know if this is a good idea, or not.
Checking that box means that the maintenance plan will perform an
integrity check before running a backup. Running a backup manually,
from QA for instance, will NOT invoke an integrity check. I would
suggest NOT using this option.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||What I thought, thanks guys.
"Hari Prasad" wrote:
[vbcol=seagreen]
> Checking that option will verify the allocation and structural integrity of
> user and system tables, and indexes in the database.
> This will incur lots of resource usage and will lock resouces in SQL 2000.
> So I recommend you to schedule a DBCC CHECKDB during offpeak time.
> Thanks
> Hari
> "Tracy McKibben" wrote:

Maint plan: integerity check & backup

In the maintenance plans Integrity tab there is a check box for: "Preform
these tests before backing up the database, or transaction logs".
I want to be sure I understand this. If I check this box, does that mean an
integrity check (and potentially a repair) will run ANYTIME a database, OR
transaction log backup is run, as part of the backup procedure and that one
would not need to set a schedule for the integrity checks?
Given the posibility of the function locking the database (to do a repair),
I don't know if this is a good idea, or not.JayKon wrote:
> In the maintenance plans Integrity tab there is a check box for: "Preform
> these tests before backing up the database, or transaction logs".
> I want to be sure I understand this. If I check this box, does that mean a
n
> integrity check (and potentially a repair) will run ANYTIME a database, OR
> transaction log backup is run, as part of the backup procedure and that on
e
> would not need to set a schedule for the integrity checks?
> Given the posibility of the function locking the database (to do a repair)
,
> I don't know if this is a good idea, or not.
Checking that box means that the maintenance plan will perform an
integrity check before running a backup. Running a backup manually,
from QA for instance, will NOT invoke an integrity check. I would
suggest NOT using this option.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Checking that option will verify the allocation and structural integrity of
user and system tables, and indexes in the database.
This will incur lots of resource usage and will lock resouces in SQL 2000.
So I recommend you to schedule a DBCC CHECKDB during offpeak time.
Thanks
Hari
"Tracy McKibben" wrote:

> JayKon wrote:
> Checking that box means that the maintenance plan will perform an
> integrity check before running a backup. Running a backup manually,
> from QA for instance, will NOT invoke an integrity check. I would
> suggest NOT using this option.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||What I thought, thanks guys.
"Hari Prasad" wrote:
[vbcol=seagreen]
> Checking that option will verify the allocation and structural integrity o
f
> user and system tables, and indexes in the database.
> This will incur lots of resource usage and will lock resouces in SQL 2000.
> So I recommend you to schedule a DBCC CHECKDB during offpeak time.
> Thanks
> Hari
> "Tracy McKibben" wrote:
>

LSN Sequence Issue

HI
I have full backup of 17th & lost the file of full backup
of 13th. I have transaction log backups from their
onwards. I need to restore data from 15th & 16th from
transaction logs. Its very critical. When I apply the
transaction log on 17th restore in loading mode it says
apply current transaction log having so & so lsn number.
What is the alternative to get that critical data.
Thanks,
ManojAfter you restore the backup from the 17th, you can only restore log backups
taken after that point.
SQLServer doesn't provide any tool to extract the data from the log only.
There may be tools, from companies such as "Lumigent" which would allow you
to extract the inserts/deletes/updates from the log.
"Manoj" <mgmanoj@.hotmail.com> wrote in message
news:0e7501c3846c$6b60b460$a101280a@.phx.gbl...
> HI
> I have full backup of 17th & lost the file of full backup
> of 13th. I have transaction log backups from their
> onwards. I need to restore data from 15th & 16th from
> transaction logs. Its very critical. When I apply the
> transaction log on 17th restore in loading mode it says
> apply current transaction log having so & so lsn number.
> What is the alternative to get that critical data.
> Thanks,
> Manoj|||Do you have the "next latest" db backup? And all subsequent log backups? If so, you can restore from
that thanks to SQL Server not truncating the log when you do db backup.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Manoj" <mgmanoj@.hotmail.com> wrote in message news:0e7501c3846c$6b60b460$a101280a@.phx.gbl...
> HI
> I have full backup of 17th & lost the file of full backup
> of 13th. I have transaction log backups from their
> onwards. I need to restore data from 15th & 16th from
> transaction logs. Its very critical. When I apply the
> transaction log on 17th restore in loading mode it says
> apply current transaction log having so & so lsn number.
> What is the alternative to get that critical data.
> Thanks,
> Manoj