maintenance plan wizard: plans

Showing posts with label plans. Show all posts

Friday, March 9, 2012

Maitnenance Plans start, but don't do anything

Hi,
This is an SQL2005 issue.
We are having a strange problem where maintenance plans will start,
but not do anything and just stay in the "Executing" stage forever. I
have created a simple plan that just does a tran log backup for a test
database which should be done in half a second, but starts and thats
it. Also, no tran log is created so it isn;t doing anything. I have
created a job that runs sql script and it runs with no problem, it
just seems to be with the maintence plans that has the issue.
there are no errors being generated and the logs all look fine.
Anyone have any ideas what is going wrong or what I can try to do to
figure this out'
Thanks!Ed,
You don't mention the version and build of SQL2005.
Chris
"Ed" <ede@.nait.ab.ca> wrote in message
news:1193673452.504643.227330@.z24g2000prh.googlegroups.com...
> Hi,
> This is an SQL2005 issue.
> We are having a strange problem where maintenance plans will start,
> but not do anything and just stay in the "Executing" stage forever. I
> have created a simple plan that just does a tran log backup for a test
> database which should be done in half a second, but starts and thats
> it. Also, no tran log is created so it isn;t doing anything. I have
> created a job that runs sql script and it runs with no problem, it
> just seems to be with the maintence plans that has the issue.
> there are no errors being generated and the logs all look fine.
> Anyone have any ideas what is going wrong or what I can try to do to
> figure this out'
> Thanks!
>|||Try right-clicking on the job and select 'refresh job', or 'refresh' on
SQLagent.
It's not the job that's going on forever, it's the fact that MS (like EM
before it) has to be manually refreshed.
PITA, but what is, is.
Good luck,
Jay
"Ed" <ede@.nait.ab.ca> wrote in message
news:1193673452.504643.227330@.z24g2000prh.googlegroups.com...
> Hi,
> This is an SQL2005 issue.
> We are having a strange problem where maintenance plans will start,
> but not do anything and just stay in the "Executing" stage forever. I
> have created a simple plan that just does a tran log backup for a test
> database which should be done in half a second, but starts and thats
> it. Also, no tran log is created so it isn;t doing anything. I have
> created a job that runs sql script and it runs with no problem, it
> just seems to be with the maintence plans that has the issue.
> there are no errors being generated and the logs all look fine.
> Anyone have any ideas what is going wrong or what I can try to do to
> figure this out'
> Thanks!
>

Maitenance plans and noskip

I am using SQL Server 2005. I have a Backup Database task which I use
to back up to a single backup device. I want to set a retention time
of 3 days and have old backups overwritten. This seems to be the WITH
INIT, NOSKIP problem. However using the wizard there doesn't seem to
be the possibility of doing this. I find it hard to believe though
that anyone would write a wizard which didn't offer this feature.
Is there any way to backup with NOSKIP or failing that alter the SQL
genereated by the wizard.
For info, here's the first few SQL statements from the wizard!
BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS = 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS = 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS = 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
RETAINDAYS = 3, NOFORMAT, NOINIT, NAME = N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
= 10
GO
etcThere's no way in SQL Server to backup to a single backup device and overwrite "only the oldest"
backups. Either you overwrite everything, or you append. Period.
Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP can be confusing, and
that it probably why there's a section in Books Online titled "Interaction of SKIP, NOSKIP, INIT,
and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server either overwrites everything
or not at all. This is how the engine work, so it doesn't relate to the maintenance wizard.
Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options adding anything to
INIT/NOINIT.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<usenet@.tynemarch.co.uk> wrote in message
news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
>I am using SQL Server 2005. I have a Backup Database task which I use
> to back up to a single backup device. I want to set a retention time
> of 3 days and have old backups overwritten. This seems to be the WITH
> INIT, NOSKIP problem. However using the wizard there doesn't seem to
> be the possibility of doing this. I find it hard to believe though
> that anyone would write a wizard which didn't offer this feature.
> Is there any way to backup with NOSKIP or failing that alter the SQL
> genereated by the wizard.
> For info, here's the first few SQL statements from the wizard!
> BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS => 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS => 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS => 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
> RETAINDAYS = 3, NOFORMAT, NOINIT, NAME => N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
> = 10
> GO
> etc
>|||I would happily overwrite everything but there doesn't seem to be that
option either in the Wizard. You only have the option that it either
expires or it doesn't as whatever you do it always puts in SKIP into
the SQL. The only way you seem to be able to backup without having old
backups is to backup to individual files rather than using a backup
device as I am.
Rob
On 2 Aug, 12:58, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> There's no way in SQL Server to backup to a single backup device and overwrite "only the oldest"
> backups. Either you overwrite everything, or you append. Period.
> Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP can be confusing, and
> that it probably why there's a section in Books Online titled "Interaction of SKIP, NOSKIP, INIT,
> and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server either overwrites everything
> or not at all. This is how the engine work, so it doesn't relate to the maintenance wizard.
> Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options adding anything to
> INIT/NOINIT.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <use...@.tynemarch.co.uk> wrote in message
> news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
>
> >I am using SQL Server 2005. I have a Backup Database task which I use
> > to back up to a single backup device. I want to set a retention time
> > of 3 days and have old backups overwritten. This seems to be the WITH
> > INIT, NOSKIP problem. However using the wizard there doesn't seem to
> > be the possibility of doing this. I find it hard to believe though
> > that anyone would write a wizard which didn't offer this feature.
> > Is there any way to backup with NOSKIP or failing that alter the SQL
> > genereated by the wizard.
> > For info, here's the first few SQL statements from the wizard!
> > BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS => > 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
> > REWIND, NOUNLOAD, STATS = 10
> > GO
> > BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS => > 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
> > REWIND, NOUNLOAD, STATS = 10
> > GO
> > BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS => > 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
> > REWIND, NOUNLOAD, STATS = 10
> > GO
> > BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
> > RETAINDAYS = 3, NOFORMAT, NOINIT, NAME => > N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
> > = 10
> > GO
> > etc- Hide quoted text -
> - Show quoted text -|||So what you want to do is to backup to the same backup device name and always overwrite?
In my wizard (I'm on sp2, there has been improvements here...), I see an option "If backup files
exists:" and I select "Overwrite".
Here are the backup commands executed, as catched by Profiler:
BACKUP DATABASE [master] TO [MyDevice] WITH NOFORMAT, INIT, NAME =N'master_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
BACKUP DATABASE [model] TO [MyDevice] WITH NOFORMAT, NOINIT, NAME =N'model_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
As you see, it does INIT for the first backup and NOINIT for the following backups. Is above what
you want, ir are you saying that there's some problem with the TSQL above?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<usenet@.tynemarch.co.uk> wrote in message
news:1186057933.490529.68270@.l70g2000hse.googlegroups.com...
>I would happily overwrite everything but there doesn't seem to be that
> option either in the Wizard. You only have the option that it either
> expires or it doesn't as whatever you do it always puts in SKIP into
> the SQL. The only way you seem to be able to backup without having old
> backups is to backup to individual files rather than using a backup
> device as I am.
> Rob
> On 2 Aug, 12:58, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> There's no way in SQL Server to backup to a single backup device and overwrite "only the oldest"
>> backups. Either you overwrite everything, or you append. Period.
>> Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP can be confusing, and
>> that it probably why there's a section in Books Online titled "Interaction of SKIP, NOSKIP, INIT,
>> and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server either overwrites
>> everything
>> or not at all. This is how the engine work, so it doesn't relate to the maintenance wizard.
>> Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options adding anything to
>> INIT/NOINIT.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> <use...@.tynemarch.co.uk> wrote in message
>> news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
>>
>> >I am using SQL Server 2005. I have a Backup Database task which I use
>> > to back up to a single backup device. I want to set a retention time
>> > of 3 days and have old backups overwritten. This seems to be the WITH
>> > INIT, NOSKIP problem. However using the wizard there doesn't seem to
>> > be the possibility of doing this. I find it hard to believe though
>> > that anyone would write a wizard which didn't offer this feature.
>> > Is there any way to backup with NOSKIP or failing that alter the SQL
>> > genereated by the wizard.
>> > For info, here's the first few SQL statements from the wizard!
>> > BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS =>> > 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
>> > REWIND, NOUNLOAD, STATS = 10
>> > GO
>> > BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS =>> > 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
>> > REWIND, NOUNLOAD, STATS = 10
>> > GO
>> > BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS =>> > 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
>> > REWIND, NOUNLOAD, STATS = 10
>> > GO
>> > BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
>> > RETAINDAYS = 3, NOFORMAT, NOINIT, NAME =>> > N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
>> > = 10
>> > GO
>> > etc- Hide quoted text -
>> - Show quoted text -
>|||I could not get the wizard to generate any SQL with SKIP in it.
However, the answer to my problems was to use a one-off backup file
rather than have a pre-allocated backup device. I could then move to
the file to somewhere else and start afresh with a new file the next
day. Thanks for the help anyway.
On 2 Aug, 14:19, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> So what you want to do is to backup to the same backup device name and always overwrite?
> In my wizard (I'm on sp2, there has been improvements here...), I see an option "If backup files
> exists:" and I select "Overwrite".
> Here are the backup commands executed, as catched by Profiler:
> BACKUP DATABASE [master] TO [MyDevice] WITH NOFORMAT, INIT, NAME => N'master_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
> BACKUP DATABASE [model] TO [MyDevice] WITH NOFORMAT, NOINIT, NAME => N'model_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
> As you see, it does INIT for the first backup and NOINIT for the following backups. Is above what
> you want, ir are you saying that there's some problem with the TSQL above?
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <use...@.tynemarch.co.uk> wrote in message
> news:1186057933.490529.68270@.l70g2000hse.googlegroups.com...
>
> >I would happily overwrite everything but there doesn't seem to be that
> > option either in the Wizard. You only have the option that it either
> > expires or it doesn't as whatever you do it always puts in SKIP into
> > the SQL. The only way you seem to be able to backup without having old
> > backups is to backup to individual files rather than using a backup
> > device as I am.
> > Rob
> > On 2 Aug, 12:58, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> There's no way in SQL Server to backup to a single backup device and overwrite "only the oldest"
> >> backups. Either you overwrite everything, or you append. Period.
> >> Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP can be confusing, and
> >> that it probably why there's a section in Books Online titled "Interaction of SKIP, NOSKIP, INIT,
> >> and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server either overwrites
> >> everything
> >> or not at all. This is how the engine work, so it doesn't relate to the maintenance wizard.
> >> Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options adding anything to
> >> INIT/NOINIT.
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> <use...@.tynemarch.co.uk> wrote in message
> >>news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
> >> >I am using SQL Server 2005. I have a Backup Database task which I use
> >> > to back up to a single backup device. I want to set a retention time
> >> > of 3 days and have old backups overwritten. This seems to be the WITH
> >> > INIT, NOSKIP problem. However using the wizard there doesn't seem to
> >> > be the possibility of doing this. I find it hard to believe though
> >> > that anyone would write a wizard which didn't offer this feature.
> >> > Is there any way to backup with NOSKIP or failing that alter the SQL
> >> > genereated by the wizard.
> >> > For info, here's the first few SQL statements from the wizard!
> >> > BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS => >> > 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
> >> > REWIND, NOUNLOAD, STATS = 10
> >> > GO
> >> > BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS => >> > 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
> >> > REWIND, NOUNLOAD, STATS = 10
> >> > GO
> >> > BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS => >> > 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
> >> > REWIND, NOUNLOAD, STATS = 10
> >> > GO
> >> > BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
> >> > RETAINDAYS = 3, NOFORMAT, NOINIT, NAME => >> > N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
> >> > = 10
> >> > GO
> >> > etc- Hide quoted text -
> >> - Show quoted text -- Hide quoted text -
> - Show quoted text -

Maitenance plans and noskip

Wednesday, March 7, 2012

Maintenence Plan

We are running SQL server 2000 on Windows 2000 and have a
number of servers. We have set up scheduled backup
maintenence plans which are set to back our databases,
transaction logs to disk then delete the transaction logs
and backup files after so many days. The backups are
always ok but For some reason the Transaction logs and
backup files are not being deleted which causes the disk
to fill up and databases to crash. We have checked the
permissions on the backup folders to see if the SQL
service account might not have had permissions set
correctly but this is not the case. Can anyone help with
this.BigA,
Can you verify that a tape backup job does not have an open file lock on
the files? I have had this problem and the culprit was a tape backup job
copying the files to tape, and leaving the files in a "open" state.
sqlmaint is then unable to delete the files and "fails".
There are issues with MSDE, so check that you are not using MSDE.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
BigA wrote:
> We are running SQL server 2000 on Windows 2000 and have a
> number of servers. We have set up scheduled backup
> maintenence plans which are set to back our databases,
> transaction logs to disk then delete the transaction logs
> and backup files after so many days. The backups are
> always ok but For some reason the Transaction logs and
> backup files are not being deleted which causes the disk
> to fill up and databases to crash. We have checked the
> permissions on the backup folders to see if the SQL
> service account might not have had permissions set
> correctly but this is not the case. Can anyone help with
> this.|||We use Veritas Backup Exec and it's does backup open
files with a lock. So could this be the culprit? Would
this only happen if the tape backup was at the same time
as the Maintenence plan?
>--Original Message--
>BigA,
>Can you verify that a tape backup job does not have an
open file lock on
>the files? I have had this problem and the culprit was a
tape backup job
>copying the files to tape, and leaving the files in
a "open" state.
>sqlmaint is then unable to delete the files and "fails".
>There are issues with MSDE, so check that you are not
using MSDE.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>BigA wrote:
have a[vbcol=seagreen]
logs[vbcol=seagreen]
disk[vbcol=seagreen]
the[vbcol=seagreen]
with[vbcol=seagreen]
>.
>|||BigA,
Yes, this is likely the culprit. Check your tape back windows do not
overlap your SQL Server backup jobs. The problem should only occur if
the maintenance plan is trying to delete a file whilst backup exec is
copying it to tape.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
BigA wrote:
> We use Veritas Backup Exec and it's does backup open
> files with a lock. So could this be the culprit? Would
> this only happen if the tape backup was at the same time
> as the Maintenence plan?
>|||Thanks for your fast response. I will give this a try.
sounds like it could be the answer.
>--Original Message--
>BigA,
>Yes, this is likely the culprit. Check your tape back
windows do not
>overlap your SQL Server backup jobs. The problem should
only occur if
>the maintenance plan is trying to delete a file whilst
backup exec is
>copying it to tape.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>BigA wrote:
Would[vbcol=seagreen]
time[vbcol=seagreen]
>.
>

Maintenence Plan

We are running SQL server 2000 on Windows 2000 and have a
number of servers. We have set up scheduled backup
maintenence plans which are set to back our databases,
transaction logs to disk then delete the transaction logs
and backup files after so many days. The backups are
always ok but For some reason the Transaction logs and
backup files are not being deleted which causes the disk
to fill up and databases to crash. We have checked the
permissions on the backup folders to see if the SQL
service account might not have had permissions set
correctly but this is not the case. Can anyone help with
this.BigA,
Can you verify that a tape backup job does not have an open file lock on
the files? I have had this problem and the culprit was a tape backup job
copying the files to tape, and leaving the files in a "open" state.
sqlmaint is then unable to delete the files and "fails".
There are issues with MSDE, so check that you are not using MSDE.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
BigA wrote:
> We are running SQL server 2000 on Windows 2000 and have a
> number of servers. We have set up scheduled backup
> maintenence plans which are set to back our databases,
> transaction logs to disk then delete the transaction logs
> and backup files after so many days. The backups are
> always ok but For some reason the Transaction logs and
> backup files are not being deleted which causes the disk
> to fill up and databases to crash. We have checked the
> permissions on the backup folders to see if the SQL
> service account might not have had permissions set
> correctly but this is not the case. Can anyone help with
> this.|||We use Veritas Backup Exec and it's does backup open
files with a lock. So could this be the culprit? Would
this only happen if the tape backup was at the same time
as the Maintenence plan?
>--Original Message--
>BigA,
>Can you verify that a tape backup job does not have an
open file lock on
>the files? I have had this problem and the culprit was a
tape backup job
>copying the files to tape, and leaving the files in
a "open" state.
>sqlmaint is then unable to delete the files and "fails".
>There are issues with MSDE, so check that you are not
using MSDE.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>BigA wrote:
>> We are running SQL server 2000 on Windows 2000 and
have a
>> number of servers. We have set up scheduled backup
>> maintenence plans which are set to back our databases,
>> transaction logs to disk then delete the transaction
logs
>> and backup files after so many days. The backups are
>> always ok but For some reason the Transaction logs and
>> backup files are not being deleted which causes the
disk
>> to fill up and databases to crash. We have checked
the
>> permissions on the backup folders to see if the SQL
>> service account might not have had permissions set
>> correctly but this is not the case. Can anyone help
with
>> this.
>.
>|||BigA,
Yes, this is likely the culprit. Check your tape back windows do not
overlap your SQL Server backup jobs. The problem should only occur if
the maintenance plan is trying to delete a file whilst backup exec is
copying it to tape.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
BigA wrote:
> We use Veritas Backup Exec and it's does backup open
> files with a lock. So could this be the culprit? Would
> this only happen if the tape backup was at the same time
> as the Maintenence plan?
>|||Thanks for your fast response. I will give this a try.
sounds like it could be the answer.
>--Original Message--
>BigA,
>Yes, this is likely the culprit. Check your tape back
windows do not
>overlap your SQL Server backup jobs. The problem should
only occur if
>the maintenance plan is trying to delete a file whilst
backup exec is
>copying it to tape.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>BigA wrote:
>> We use Veritas Backup Exec and it's does backup open
>> files with a lock. So could this be the culprit?
Would
>> this only happen if the tape backup was at the same
time
>> as the Maintenence plan?
>.
>

Maintenence Plan

We are running SQL server 2000 on Windows 2000 and have a
number of servers. We have set up scheduled backup
maintenence plans which are set to back our databases,
transaction logs to disk then delete the transaction logs
and backup files after so many days. The backups are
always ok but For some reason the Transaction logs and
backup files are not being deleted which causes the disk
to fill up and databases to crash. We have checked the
permissions on the backup folders to see if the SQL
service account might not have had permissions set
correctly but this is not the case. Can anyone help with
this.
BigA,
Can you verify that a tape backup job does not have an open file lock on
the files? I have had this problem and the culprit was a tape backup job
copying the files to tape, and leaving the files in a "open" state.
sqlmaint is then unable to delete the files and "fails".
There are issues with MSDE, so check that you are not using MSDE.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
BigA wrote:
> We are running SQL server 2000 on Windows 2000 and have a
> number of servers. We have set up scheduled backup
> maintenence plans which are set to back our databases,
> transaction logs to disk then delete the transaction logs
> and backup files after so many days. The backups are
> always ok but For some reason the Transaction logs and
> backup files are not being deleted which causes the disk
> to fill up and databases to crash. We have checked the
> permissions on the backup folders to see if the SQL
> service account might not have had permissions set
> correctly but this is not the case. Can anyone help with
> this.
|||We use Veritas Backup Exec and it's does backup open
files with a lock. So could this be the culprit? Would
this only happen if the tape backup was at the same time
as the Maintenence plan?
>--Original Message--
>BigA,
>Can you verify that a tape backup job does not have an
open file lock on
>the files? I have had this problem and the culprit was a
tape backup job
>copying the files to tape, and leaving the files in
a "open" state.
>sqlmaint is then unable to delete the files and "fails".
>There are issues with MSDE, so check that you are not
using MSDE.[vbcol=seagreen]
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>BigA wrote:
have a[vbcol=seagreen]
logs[vbcol=seagreen]
disk[vbcol=seagreen]
the[vbcol=seagreen]
with
>.
>
|||BigA,
Yes, this is likely the culprit. Check your tape back windows do not
overlap your SQL Server backup jobs. The problem should only occur if
the maintenance plan is trying to delete a file whilst backup exec is
copying it to tape.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
BigA wrote:
> We use Veritas Backup Exec and it's does backup open
> files with a lock. So could this be the culprit? Would
> this only happen if the tape backup was at the same time
> as the Maintenence plan?
>
|||Thanks for your fast response. I will give this a try.
sounds like it could be the answer.
>--Original Message--
>BigA,
>Yes, this is likely the culprit. Check your tape back
windows do not
>overlap your SQL Server backup jobs. The problem should
only occur if
>the maintenance plan is trying to delete a file whilst
backup exec is[vbcol=seagreen]
>copying it to tape.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>BigA wrote:
Would[vbcol=seagreen]
time
>.
>

Mainteneance plans BAK TRN

Hi All,
Ive created a maintenance plan which does the following (all in one plan)
* All Databases
* Reorganise data,
Change free space per page percentage to 10%
Remove unused space from database files
Shrink database when it grows beyond 50 Mb
Amound of free space to remain after shrink 10%
*Check database integrity
include indexes
*Back up the database as part of the maintenance plan
Verify the integrity of the database upon completion
Backup to Disk
Use this Directory
Remove files older than 5 days
Backup file extension BAK
*Back up the Transaction log as part of the maintenance plan
Verify the integrity of the database upon completion
Backup to Disk
Use this Directory
Remove files older than 5 days
Backup file extension TRN
*Write a report file in the directory
delete report files after 14 days
We also have one other plan which simply checks the integrity on all DBs
once a week. Does this solution / plan seem ok? Its the first one I have
created so Im not 100% on it.
Also what rights/user does the plan run under?
I find that sometimes it deletes the files over 5 days and then sometimes it
just doesnt, or it will delete the TRN files but not the BAK files or vice
versa. I cant really make sense of it. Any ideas?
Also would I be better off having seperate plans for removing the BAK and
the TRN files?
Any help would be appreciated.
Thank youAdrin,
If you are using Sql Server 2000, then check your script and please
specify any pattern that you see in not removing old files.
You should be able to remove old files without any issues.
In Sql 2005 removing old files from sub-directory was not possible.
Even that is fixed after applying SP1.
Hope this helps,
Sameer Raval
[DBA-Developer]
Augusta,GA,USA
"Adrian" wrote:
> Hi All,
> Ive created a maintenance plan which does the following (all in one plan)
> * All Databases
> * Reorganise data,
> Change free space per page percentage to 10%
> Remove unused space from database files
> Shrink database when it grows beyond 50 Mb
> Amound of free space to remain after shrink 10%
> *Check database integrity
> include indexes
> *Back up the database as part of the maintenance plan
> Verify the integrity of the database upon completion
> Backup to Disk
> Use this Directory
> Remove files older than 5 days
> Backup file extension BAK
> *Back up the Transaction log as part of the maintenance plan
> Verify the integrity of the database upon completion
> Backup to Disk
> Use this Directory
> Remove files older than 5 days
> Backup file extension TRN
> *Write a report file in the directory
> delete report files after 14 days
> We also have one other plan which simply checks the integrity on all DBs
> once a week. Does this solution / plan seem ok? Its the first one I have
> created so Im not 100% on it.
> Also what rights/user does the plan run under?
> I find that sometimes it deletes the files over 5 days and then sometimes it
> just doesnt, or it will delete the TRN files but not the BAK files or vice
> versa. I cant really make sense of it. Any ideas?
> Also would I be better off having seperate plans for removing the BAK and
> the TRN files?
> Any help would be appreciated.
> Thank you|||In addition, I've seen a plan stopping execution if something goes wrong. This can lead to old
backup files not being removed. Read the report file carefully. Also, don't have databases in simple
recovery mode in a plan where you do log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sameer Raval" <SameerRaval@.discussions.microsoft.com> wrote in message
news:ED2CC0C2-6E36-4AA7-A6B4-8BD0AFD05934@.microsoft.com...
> Adrin,
> If you are using Sql Server 2000, then check your script and please
> specify any pattern that you see in not removing old files.
> You should be able to remove old files without any issues.
> In Sql 2005 removing old files from sub-directory was not possible.
> Even that is fixed after applying SP1.
> Hope this helps,
>
> Sameer Raval
> [DBA-Developer]
> Augusta,GA,USA
>
> "Adrian" wrote:
>> Hi All,
>> Ive created a maintenance plan which does the following (all in one plan)
>> * All Databases
>> * Reorganise data,
>> Change free space per page percentage to 10%
>> Remove unused space from database files
>> Shrink database when it grows beyond 50 Mb
>> Amound of free space to remain after shrink 10%
>> *Check database integrity
>> include indexes
>> *Back up the database as part of the maintenance plan
>> Verify the integrity of the database upon completion
>> Backup to Disk
>> Use this Directory
>> Remove files older than 5 days
>> Backup file extension BAK
>> *Back up the Transaction log as part of the maintenance plan
>> Verify the integrity of the database upon completion
>> Backup to Disk
>> Use this Directory
>> Remove files older than 5 days
>> Backup file extension TRN
>> *Write a report file in the directory
>> delete report files after 14 days
>> We also have one other plan which simply checks the integrity on all DBs
>> once a week. Does this solution / plan seem ok? Its the first one I have
>> created so Im not 100% on it.
>> Also what rights/user does the plan run under?
>> I find that sometimes it deletes the files over 5 days and then sometimes it
>> just doesnt, or it will delete the TRN files but not the BAK files or vice
>> versa. I cant really make sense of it. Any ideas?
>> Also would I be better off having seperate plans for removing the BAK and
>> the TRN files?
>> Any help would be appreciated.
>> Thank you|||Hey guys,
sorry for the late response I made some changes and wanted to wait a gew
days to see the results.
I changed all dbs to full recovery mode this seemed to fix the problem with
the BAK files and now they are getting backed up and removed successfully
after 5 days.
however the TRN files are still not being removed, I get the following
errors in my log file.
Backup can not be performed on database 'master'. This sub task is ignored.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'Backup All DBs' on 01/08/2006 00:00:44
SQLMAINT.EXE Process Exit Code: 1 (Failed)
On different days I seem to get different dbs showing the same type of error
Backup can not be performed on database 'msdb'. This sub task is ignored.
Backup can not be performed on database 'pubs'. This sub task is ignored.
Any suggestions?|||You really need two separate plans. One for the databases for which you intend to do log backups,
and one for the databases that you don't intent to do log backups.
You cannot do log backups on master regardless of recovery model.
Agent will set msdb to simple recovery on startup.
Why do you backup pubs?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:97F75DCF-B984-4644-BD93-DE4798C81B4B@.microsoft.com...
> Hey guys,
> sorry for the late response I made some changes and wanted to wait a gew
> days to see the results.
> I changed all dbs to full recovery mode this seemed to fix the problem with
> the BAK files and now they are getting backed up and removed successfully
> after 5 days.
> however the TRN files are still not being removed, I get the following
> errors in my log file.
> Backup can not be performed on database 'master'. This sub task is ignored.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'Backup All DBs' on 01/08/2006 00:00:44
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> On different days I seem to get different dbs showing the same type of error
> Backup can not be performed on database 'msdb'. This sub task is ignored.
> Backup can not be performed on database 'pubs'. This sub task is ignored.
> Any suggestions?
>|||Hi Tibor,
Im not quite sure I understand, I thought it was good practice to backup and
log all database's?
Am I right now in thinking that I should not backup or log the system
databases
Master, model, msdb, pubs, tempdb ?
Does my initial plan look ok, if I remove the system databases and just have
it for our business database's?
Thanks|||You should always do a FULL backup on Master, Model & MSDB but there is no
need for Log backups since they are either in Simple mode to begin with or
in the case of Model there are no changes made. Tempdb is rebuilt from
scratch each time you start up SQL Server so there is no need to back it up.
--
Andrew J. Kelly SQL MVP
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:12366730-7681-43DB-80BE-A838D1188209@.microsoft.com...
> Hi Tibor,
> Im not quite sure I understand, I thought it was good practice to backup
> and
> log all database's?
> Am I right now in thinking that I should not backup or log the system
> databases
> Master, model, msdb, pubs, tempdb ?
> Does my initial plan look ok, if I remove the system databases and just
> have
> it for our business database's?
> Thanks
>|||Thanks Andrew,
I have created a third Maintenance plan to back up the system dbs, Master,
Msdb and model.
I have also changed the Initial Plan to backup all user databases, I will
check this tomorrow and see the results.
"Andrew J. Kelly" wrote:
> You should always do a FULL backup on Master, Model & MSDB but there is no
> need for Log backups since they are either in Simple mode to begin with or
> in the case of Model there are no changes made. Tempdb is rebuilt from
> scratch each time you start up SQL Server so there is no need to back it up.
> --
> Andrew J. Kelly SQL MVP
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:12366730-7681-43DB-80BE-A838D1188209@.microsoft.com...
> > Hi Tibor,
> >
> > Im not quite sure I understand, I thought it was good practice to backup
> > and
> > log all database's?
> >
> > Am I right now in thinking that I should not backup or log the system
> > databases
> > Master, model, msdb, pubs, tempdb ?
> >
> > Does my initial plan look ok, if I remove the system databases and just
> > have
> > it for our business database's?
> >
> > Thanks
> >
>
>|||Adrian wrote:
> Thanks Andrew,
> I have created a third Maintenance plan to back up the system dbs, Master,
> Msdb and model.
> I have also changed the Initial Plan to backup all user databases, I will
> check this tomorrow and see the results.
>
Sounds like you have a solution, but if you decide that managing three
seperate maintenance plans is too much work, have a look at this script
of mine:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/AutomaticBackupOfAllDatabases
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Mainteneance plans BAK TRN

Hi All,
Ive created a maintenance plan which does the following (all in one plan)
* All Databases
* Reorganise data,
Change free space per page percentage to 10%
Remove unused space from database files
Shrink database when it grows beyond 50 Mb
Amound of free space to remain after shrink 10%
*Check database integrity
include indexes
*Back up the database as part of the maintenance plan
Verify the integrity of the database upon completion
Backup to Disk
Use this Directory
Remove files older than 5 days
Backup file extension BAK
*Back up the Transaction log as part of the maintenance plan
Verify the integrity of the database upon completion
Backup to Disk
Use this Directory
Remove files older than 5 days
Backup file extension TRN
*Write a report file in the directory
delete report files after 14 days
We also have one other plan which simply checks the integrity on all DBs
once a week. Does this solution / plan seem ok? Its the first one I have
created so Im not 100% on it.
Also what rights/user does the plan run under?
I find that sometimes it deletes the files over 5 days and then sometimes it
just doesnt, or it will delete the TRN files but not the BAK files or vice
versa. I cant really make sense of it. Any ideas?
Also would I be better off having seperate plans for removing the BAK and
the TRN files?
Any help would be appreciated.
Thank youAdrin,
If you are using Sql Server 2000, then check your script and please
specify any pattern that you see in not removing old files.
You should be able to remove old files without any issues.
In Sql 2005 removing old files from sub-directory was not possible.
Even that is fixed after applying SP1.
Hope this helps,
Sameer Raval
[DBA-Developer]
Augusta,GA,USA
"Adrian" wrote:

> Hi All,
> Ive created a maintenance plan which does the following (all in one plan)
> * All Databases
> * Reorganise data,
> Change free space per page percentage to 10%
> Remove unused space from database files
> Shrink database when it grows beyond 50 Mb
> Amound of free space to remain after shrink 10%
> *Check database integrity
> include indexes
> *Back up the database as part of the maintenance plan
> Verify the integrity of the database upon completion
> Backup to Disk
> Use this Directory
> Remove files older than 5 days
> Backup file extension BAK
> *Back up the Transaction log as part of the maintenance plan
> Verify the integrity of the database upon completion
> Backup to Disk
> Use this Directory
> Remove files older than 5 days
> Backup file extension TRN
> *Write a report file in the directory
> delete report files after 14 days
> We also have one other plan which simply checks the integrity on all DBs
> once a week. Does this solution / plan seem ok? Its the first one I have
> created so Im not 100% on it.
> Also what rights/user does the plan run under?
> I find that sometimes it deletes the files over 5 days and then sometimes
it
> just doesnt, or it will delete the TRN files but not the BAK files or vice
> versa. I cant really make sense of it. Any ideas?
> Also would I be better off having seperate plans for removing the BAK and
> the TRN files?
> Any help would be appreciated.
> Thank you|||In addition, I've seen a plan stopping execution if something goes wrong. Th
is can lead to old
backup files not being removed. Read the report file carefully. Also, don't
have databases in simple
recovery mode in a plan where you do log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sameer Raval" <SameerRaval@.discussions.microsoft.com> wrote in message
news:ED2CC0C2-6E36-4AA7-A6B4-8BD0AFD05934@.microsoft.com...[vbcol=seagreen]
> Adrin,
> If you are using Sql Server 2000, then check your script and please
> specify any pattern that you see in not removing old files.
> You should be able to remove old files without any issues.
> In Sql 2005 removing old files from sub-directory was not possible.
> Even that is fixed after applying SP1.
> Hope this helps,
>
> Sameer Raval
> [DBA-Developer]
> Augusta,GA,USA
>
> "Adrian" wrote:
>|||Hey guys,
sorry for the late response I made some changes and wanted to wait a gew
days to see the results.
I changed all dbs to full recovery mode this seemed to fix the problem with
the BAK files and now they are getting backed up and removed successfully
after 5 days.
however the TRN files are still not being removed, I get the following
errors in my log file.
Backup can not be performed on database 'master'. This sub task is ignored.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'Backup All DBs' on 01/08/2006 00:00:44
SQLMAINT.EXE Process Exit Code: 1 (Failed)
On different days I seem to get different dbs showing the same type of error
Backup can not be performed on database 'msdb'. This sub task is ignored.
Backup can not be performed on database 'pubs'. This sub task is ignored.
Any suggestions?|||You really need two separate plans. One for the databases for which you inte
nd to do log backups,
and one for the databases that you don't intent to do log backups.
You cannot do log backups on master regardless of recovery model.
Agent will set msdb to simple recovery on startup.
Why do you backup pubs?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:97F75DCF-B984-4644-BD93-DE4798C81B4B@.microsoft.com...
> Hey guys,
> sorry for the late response I made some changes and wanted to wait a gew
> days to see the results.
> I changed all dbs to full recovery mode this seemed to fix the problem wit
h
> the BAK files and now they are getting backed up and removed successfully
> after 5 days.
> however the TRN files are still not being removed, I get the following
> errors in my log file.
> Backup can not be performed on database 'master'. This sub task is ignored
.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'Backup All DBs' on 01/08/2006 00:00:44
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> On different days I seem to get different dbs showing the same type of err
or
> Backup can not be performed on database 'msdb'. This sub task is ignored.
> Backup can not be performed on database 'pubs'. This sub task is ignored.
> Any suggestions?
>|||Hi Tibor,
Im not quite sure I understand, I thought it was good practice to backup and
log all database's?
Am I right now in thinking that I should not backup or log the system
databases
Master, model, msdb, pubs, tempdb ?
Does my initial plan look ok, if I remove the system databases and just have
it for our business database's?
Thanks|||You should always do a FULL backup on Master, Model & MSDB but there is no
need for Log backups since they are either in Simple mode to begin with or
in the case of Model there are no changes made. Tempdb is rebuilt from
scratch each time you start up SQL Server so there is no need to back it up.
Andrew J. Kelly SQL MVP
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:12366730-7681-43DB-80BE-A838D1188209@.microsoft.com...
> Hi Tibor,
> Im not quite sure I understand, I thought it was good practice to backup
> and
> log all database's?
> Am I right now in thinking that I should not backup or log the system
> databases
> Master, model, msdb, pubs, tempdb ?
> Does my initial plan look ok, if I remove the system databases and just
> have
> it for our business database's?
> Thanks
>|||Thanks Andrew,
I have created a third Maintenance plan to back up the system dbs, Master,
Msdb and model.
I have also changed the Initial Plan to backup all user databases, I will
check this tomorrow and see the results.
"Andrew J. Kelly" wrote:

> You should always do a FULL backup on Master, Model & MSDB but there is no
> need for Log backups since they are either in Simple mode to begin with or
> in the case of Model there are no changes made. Tempdb is rebuilt from
> scratch each time you start up SQL Server so there is no need to back it u
p.
> --
> Andrew J. Kelly SQL MVP
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:12366730-7681-43DB-80BE-A838D1188209@.microsoft.com...
>
>|||Adrian wrote:
> Thanks Andrew,
> I have created a third Maintenance plan to back up the system dbs, Master,
> Msdb and model.
> I have also changed the Initial Plan to backup all user databases, I will
> check this tomorrow and see the results.
>
Sounds like you have a solution, but if you decide that managing three
seperate maintenance plans is too much work, have a look at this script
of mine:
http://realsqlguy.com/twiki/bin/vie...realsqlguy.com

Maintenance schedule in 2005

In SQL 2005, is there a way to specify two (or more) different schedules
within one maintenance plan? Or, do I have to create different plans to be
ran on different schedules?
Thank you in advance for your help!> In SQL 2005, is there a way to specify two (or more) different schedules
> within one maintenance plan? Or, do I have to create different plans to be
> ran on different schedules?
You cannot add more than one schedule using the Maintentance Plan Wizard, or
by using the Properties of a created plan. However, with the wizard, you
create a scheduled job. You can add schedules to the job.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/

Maintenance schedule in 2005

In SQL 2005, is there a way to specify two (or more) different schedules
within one maintenance plan? Or, do I have to create different plans to be
ran on different schedules?
Thank you in advance for your help!> In SQL 2005, is there a way to specify two (or more) different schedules
> within one maintenance plan? Or, do I have to create different plans to be
> ran on different schedules?
You cannot add more than one schedule using the Maintentance Plan Wizard, or
by using the Properties of a created plan. However, with the wizard, you
create a scheduled job. You can add schedules to the job.
--
Dejan Sarka
http://www.solidqualitylearning.com/blogs/

Maintenance plans: online rebuilding of indexes...

I'm using SQL Server 2005 SP1 Standard.

On the Rebuild Index Task there is a checkbox at the bottom that says 'Keep index online while reindexing'.

Great I thought, I'll check that.

Later, when I tested the job, I got this error:

'Online index operations can only be performed in Enterprise edition of SQL Server.'

Why have that checkbox available to check, if I'm running a version that doesn't allow it? Where's the bug?

Thanks

I've entered a bug in our tracking database.

Thanks,

Mark

Maintenance Plans, SQL Agent daily restart needed

We have a SQL 2000 server running on Server 2003, both updated, that has a
persistent problem running Maintenance plans, and thus a database backup.
Trying to troubleshoot, I’ve updated, deleted and recreated the maint plans,
restarted the server altogether, SQL itself and the SQL Agent; the long and
short of it is that if I restart the SQL Agent daily, the maintenance plan
will run.
Previously, this server has been running the maintenance plans without fail
for literally years, and now this problem has cropped up over the last couple
of months. No significant changes have been made to the server (other apps,
etc).
Having to restart SQL agent daily doesn't make any sense - any ideas?
Gary
Waht version (sp and hotfix)?
Select @.@.version
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:8AB0A498-B268-4797-BD3E-8EDBD58B131A@.microsoft.com...
> We have a SQL 2000 server running on Server 2003, both updated, that has a
> persistent problem running Maintenance plans, and thus a database backup.
> Trying to troubleshoot, I've updated, deleted and recreated the maint
> plans,
> restarted the server altogether, SQL itself and the SQL Agent; the long
> and
> short of it is that if I restart the SQL Agent daily, the maintenance plan
> will run.
> Previously, this server has been running the maintenance plans without
> fail
> for literally years, and now this problem has cropped up over the last
> couple
> of months. No significant changes have been made to the server (other
> apps,
> etc).
> Having to restart SQL agent daily doesn't make any sense - any ideas?
> --
> Gary

Maintenance Plans, SQL Agent daily restart needed

We have a SQL 2000 server running on Server 2003, both updated, that has a
persistent problem running Maintenance plans, and thus a database backup.
Trying to troubleshoot, Iâ've updated, deleted and recreated the maint plans,
restarted the server altogether, SQL itself and the SQL Agent; the long and
short of it is that if I restart the SQL Agent daily, the maintenance plan
will run.
Previously, this server has been running the maintenance plans without fail
for literally years, and now this problem has cropped up over the last couple
of months. No significant changes have been made to the server (other apps,
etc).
Having to restart SQL agent daily doesn't make any sense - any ideas?
--
GaryWaht version (sp and hotfix)?
Select @.@.version
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:8AB0A498-B268-4797-BD3E-8EDBD58B131A@.microsoft.com...
> We have a SQL 2000 server running on Server 2003, both updated, that has a
> persistent problem running Maintenance plans, and thus a database backup.
> Trying to troubleshoot, I've updated, deleted and recreated the maint
> plans,
> restarted the server altogether, SQL itself and the SQL Agent; the long
> and
> short of it is that if I restart the SQL Agent daily, the maintenance plan
> will run.
> Previously, this server has been running the maintenance plans without
> fail
> for literally years, and now this problem has cropped up over the last
> couple
> of months. No significant changes have been made to the server (other
> apps,
> etc).
> Having to restart SQL agent daily doesn't make any sense - any ideas?
> --
> Gary

Maintenance Plans won't allow me to select to delete jobs after 3 days

SQL Server 2000 (SP3A) won't allow me to select to delete any jobs older tha
n 3 days. I don't get the option in the drop down box. When I change to 3
it goes back to 0 and again no option in the drop down box.
I manually went in to job (steps tab) and inserted DAYS next to the 3 and ra
n a sql backup job after this to see if it would delete the jobs older than
3 days. It didn't delete any of them. This is occurring on both the .bak a
nd .trn files, however on t
he reports it is allowing me to select both number of days and days themselv
e. (currently set to 5 days)
This is frustrating as we are now manually deleting jobs so they don't run o
ut of hard drive space when performining nightly backup and maintenance on t
he sql server.
Any help is greatly appreciated. Permissions have been checked etc and ever
ything appears to be accurate.did you try to purge backup files or jobs older than 3 days?
1. maintenance plan will purge backup or trans log backup files after
certain days if you spcify it.
2. it does not purge jobs!
3. why would you create a job then purge it 3 days later?
Steve
"Sheri" <sheri@.isdesign.com> wrote in message
news:F868C089-CDF3-4F25-B750-6227AEBFEE12@.microsoft.com...
> SQL Server 2000 (SP3A) won't allow me to select to delete any jobs older
than 3 days. I don't get the option in the drop down box. When I change to
3 it goes back to 0 and again no option in the drop down box.
> I manually went in to job (steps tab) and inserted DAYS next to the 3 and
ran a sql backup job after this to see if it would delete the jobs older
than 3 days. It didn't delete any of them. This is occurring on both the
.bak and .trn files, however on the reports it is allowing me to select bot
h
number of days and days themselve. (currently set to 5 days)
> This is frustrating as we are now manually deleting jobs so they don't run
out of hard drive space when performining nightly backup and maintenance on
the sql server.
> Any help is greatly appreciated. Permissions have been checked etc and
everything appears to be accurate.
>

Maintenance Plans on MSDE

Hi NG,
I've defined a job and a maintenance plan on my SQL-Server. The target
system is using MSDE. I could see that the job was created, but with the
Enterprise Manager I cannot see that there is a folder 'Database Maintenance
Plans' under folder 'Management'.
Does anybody know if Maintenance plans are generally possible under MSDE, or
is there something missing on my target system where MSDE is installed.
Thanks for support, rene
hi Rene,
Re Fo wrote:
> Hi NG,
> I've defined a job and a maintenance plan on my SQL-Server. The target
> system is using MSDE. I could see that the job was created, but with
> the Enterprise Manager I cannot see that there is a folder 'Database
> Maintenance Plans' under folder 'Management'.
> Does anybody know if Maintenance plans are generally possible under
> MSDE, or is there something missing on my target system where MSDE is
> installed.
> Thanks for support, rene
Maintenance Plans are supported, but you do not have a graphical tool to
manage them, and have to rely on xp_sqlmaint system extended stored
procedure
(http://msdn.microsoft.com/library/de...-us/tsqlref/ts
_xp_aa-sz_0mb8.asp)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Maintenance plans not deleting old transaction log backups

I'm the dba for a large company and have setup many maintenance plans for our sql servers. One of the options I have set is to delete old backups of databases and transaction logs after 2 days. For some reason, the old database backups are being deleted, but the old transaction logs backups are not. I have them being put in the same directory. Anybody have any ideas why this is happening and what if any fix is available?

Those are separate items that you check off in the options for each step - are you sure you asked the wizard to do that? If possible, drop the old plans and recreate them, checking that section to make sure you select that option.

Other than missing the check box, I don't know what else might cause this. Both delete operations use the same delete stored procedure, so if one works, the other one should.

If you do change your plans and this happens again, check the event logs and the SQL Server error logs.

Maintenance Plans is SSE and Studio Management Express

Hi all,

I am using the Express versions of SQL Server and the Management console and can't seem to find anyway to set up basic maintenance plans.

Is this feature not in the Express management studio? I hope I am just missing it and it is not totally missing. I am trying to get my skills back up to speed so I can tackle some DBA jobs again and am working from home at the moment.

Can someone fill in the blanks for me to tell me if this infact is not available in the express management studio or where it is if it infact is available.

Many Thanks.

Steven

Maint Plans are not part of Express and hence not exposed in SSMS-E|||

Where can I find some scripts that allow scheduling of a backup operation?

I intend to schedule it through the Windows scheduler, as SSE has no scheduling agent.

|||Hi edmund1,

Two example calls are below. They each do the same thing so you only need one of them:

osql -U sa -S .\SQLEXPRESS -Q"BACKUP DATABASE NateTest TO DISK = 'C:\NateTest.bak'" -o c:\osql_log.txt -P

osql -U sa -S .\SQLEXPRESS -Q"BACKUP DATABASE NateTest TO DISK = 'C:\NateTest.bak'" -P > c:\osql_log.txt

You can then schedule the batch script to run every so often. Basically the above called are connecting to a local SQL Server Express instance (.\SQLEXPRESS) using the "sa" account and will execute the BACKUP DATABASE command (which you can alter as appropriate). A log file (osql_log.txt) is produced and this will contain the outcome of the call plus any errors that happened etc. I have used the -P switch without a value to indicate that a NULL password is to be used for the account - if you use a SQL Server login that has a password you need to put it in after the -P switch, e.g. -P ******.

Other options for osql that you might use are:

* Windows authentication. In this case, remove the -U and -P switches and just put in a -E switch

* Passing a file that contains a list of commands to execute. In this case, remove the -Q switch and add in the -i switch with the name of the file to execute, e.g.

-i c:\query.qry

Hope that helps a bit but sorry if it doesn't
|||Good example but I strongly recomend using sqlcmd not osql as osql is going away and sqlcmd is richer.|||

Thanks both.

Yes, this helps alot. I will try to script via sqlcmd.

I am also trying to script a signle batch of all databases that have a common naming (for example, they all start with the letters "DB_"). I'll post my solution for others to see.

Maintenance Plans in SQL 2000 vs 2005

We're in the process of setting up some new SQL 2005 servers (previously all
our DB servers have been SQL 2000).
SQL 2000 had maintenance plans which consisted of a Database Backup Job,
Transaction Log Backup Job, Integrity Check Job, and an Optimization Job.
Each of these four jobs was managed by the parent maintenance plan and each
job could be run on different schedules. This made it simple for us to
manage DB maintenance.
However it seems the idea of a DB Maintenance Job is a bit different in SQL
2005:
- All the jobs inside a maintenance plan seem to have to run at the same
time? In our case we actually want these jobs to run on different schedules.
- Rather than adding each database to one maintenance plan, each database
has to be added to each job creating 4 times as much work to setup a
database.
The new functionality offers greater granularity which is great to have
available, but at the expense of ease of use. In SQL 2000 we used to have 5
maintenance plans per SQL instance to manage - in SQL 2005 that would equate
to 20 jobs per server to manage.
I'm praying we're missing something. Can anyone clue me in :-)
Thanks,
Brad BakerBrad Baker wrote:
> We're in the process of setting up some new SQL 2005 servers (previously a
ll
> our DB servers have been SQL 2000).
> SQL 2000 had maintenance plans which consisted of a Database Backup Job,
> Transaction Log Backup Job, Integrity Check Job, and an Optimization Job.
> Each of these four jobs was managed by the parent maintenance plan and eac
h
> job could be run on different schedules. This made it simple for us to
> manage DB maintenance.
> However it seems the idea of a DB Maintenance Job is a bit different in SQ
L
> 2005:
> - All the jobs inside a maintenance plan seem to have to run at the same
> time? In our case we actually want these jobs to run on different schedule
s.
> - Rather than adding each database to one maintenance plan, each database
> has to be added to each job creating 4 times as much work to setup a
> database.
> The new functionality offers greater granularity which is great to have
> available, but at the expense of ease of use. In SQL 2000 we used to have
5
> maintenance plans per SQL instance to manage - in SQL 2005 that would equa
te
> to 20 jobs per server to manage.
> I'm praying we're missing something. Can anyone clue me in :-)
> Thanks,
> Brad Baker
>
I would encourage you to move away from the maintenance plans and write
your own maintenance scripts. It's not difficult, and you have far
greater control over how things work.
See
http://realsqlguy.com/serendipity/a...-Send-In-Backup!.html
for a couple of examples.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy -
Thanks for the links. I'll have a look.
We have on average 200 databases per SQL instance. So I'm afraid manual
scripting may not be totally practical. I'm assuming we would have to type
out the names of all 200 DBs in multiple locations?
To make things more fun our DBs are named like TZ0001-Y-clientname, so
imagine typing 200 of those without typos. :-) Not to mention managing
semi-weeekly additions and deletions.
To make this practical I think we'll need a GUI. Maybe wer can write our own
app that can generate these scripts auto-magically for us. I hope we don't
have to go that route though.
Brad Baker
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44FF106C.7020409@.realsqlguy.com...
> Brad Baker wrote:
> I would encourage you to move away from the maintenance plans and write
> your own maintenance scripts. It's not difficult, and you have far
> greater control over how things work.
> See
> http://realsqlguy.com/serendipity/a.....htm
l
> and http://realsqlguy.com/serendipity/a...-Send-In-Backup!.html for
> a couple of examples.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||One more thing I should probably have mentioned: because we have so many DBs
per SQL instance we we've previously done with SQL 2000 is something similar
to this;
Maintenance Plan 1
--
Integrity Check, Optimization Job, Full DB Backup
Runs Sunday around Midnight (each job is spaced out by 10-20 mins so that
they aren't colliding)
Transaction Log Backup
Runs every day - starts at 11PM
Contains DBs 1-50
Maintenance Plan 2
--
Integrity Check, Optimization Job, Full DB Backup
Runs Monday around Midnight (each job is spaced out by 10-20 mins so that
they aren't colliding)
Transaction Log Backup
Runs every day - starts at 11:30 PM
Contains DBs 51-100
Maintenance Plan 3
--
Integrity Check, Optimization Job, Full DB Backup
Runs Tuesday around Midnight (they're spaced out by 10-20 mins so that they
aren't colliding)
Transaction Log Backup
Runs every day - starts at 12:00 AM
Contains DBs 101-150
The details above aren't exactly right but you get the idea.
- Each database gets a full backup once a week but they are distributed
across different days to limit impact on server performance.
- Transaction log backups are made every day for every database but I
believe they start at different times - again to limit the impact on
performance.
Brad
"Brad Baker" <brad@.nospam.nospam> wrote in message
news:ekgkAme0GHA.4920@.TK2MSFTNGP06.phx.gbl...
> Tracy -
> Thanks for the links. I'll have a look.
> We have on average 200 databases per SQL instance. So I'm afraid manual
> scripting may not be totally practical. I'm assuming we would have to type
> out the names of all 200 DBs in multiple locations?
> To make things more fun our DBs are named like TZ0001-Y-clientname, so
> imagine typing 200 of those without typos. :-) Not to mention managing
> semi-weeekly additions and deletions.
> To make this practical I think we'll need a GUI. Maybe wer can write our
> own app that can generate these scripts auto-magically for us. I hope we
> don't have to go that route though.
> Brad Baker
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:44FF106C.7020409@.realsqlguy.com...
>|||Brad Baker wrote:
> Tracy -
> Thanks for the links. I'll have a look.
> We have on average 200 databases per SQL instance. So I'm afraid manual
> scripting may not be totally practical. I'm assuming we would have to type
> out the names of all 200 DBs in multiple locations?
> To make things more fun our DBs are named like TZ0001-Y-clientname, so
> imagine typing 200 of those without typos. :-) Not to mention managing
> semi-weeekly additions and deletions.
> To make this practical I think we'll need a GUI. Maybe wer can write our o
wn
> app that can generate these scripts auto-magically for us. I hope we don't
> have to go that route though.
>
One of the links I gave you is for a backup script that will
automatically backup every database on the server, no need to type out
database names.
The second link is for an "intelligent" reindex routine that checks the
severity of fragmentation before acting on an index. This one accepts a
database name as a parameter, you should be able to easily take the
"loop through all databases" logic from the backup script and apply it
to the reindex script.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> One of the links I gave you is for a backup script that will automatically
> backup every database on the server, no need to type out database names.
We don't want to back all the databases up at the same time as it would take
too long.
Thus we do full backup on about 30 databases per day:
Sunday: Databases 1-30
Monday: Databases 31- 61
Tuesday: Databases 62 - 102
etc.|||Dear Brad,
From your description, I understand that:
You had 5 maintenance plans for backing up your 200 databases per your SQL
Server 2000 instance. Now you want to move the maintenance plans to your
SQL Server 2005.
If I have misunderstood, please let me know.
If your solution in SQL Server 2000 can run well, it should be also
available in SQL Server 2005. As you said:
SQL 2000 had maintenance plans which consisted of a Database Backup Job,
Transaction Log Backup Job, Integrity Check Job, and an Optimization Job.
Each of these four jobs was managed by the parent maintenance plan and each
job could be run on different schedules. This made it simple for us to
manage DB maintenance.
In SQL Server 2005, you can also create 4 maintenance plans which response
to Database Backup Job, Transaction Log Backup job, Integreity Check job,
and Optimization Job. If you are considering management convenience, you
can named the plans with a name specification such as
"instance1_db1to50_trans_backup".
I'm not sure whether or not your concerns are for this point. Unfortunately
SQL Server 2005's desgin doesn't include this function of SQL 2000.
I do know that the task in SQL Server 2005 is at least quadruple your work
in SQL Server 2000. I appreciate that you can give Microsoft feedback on
this issue, and hope the next release will include this helpful function.
You can submit a feedback via the link:
http://connect.microsoft.com/feedba...aspx?SiteID=68
Note: Login first before a submition of your feedback.
Also, I understand that your situation seems complex and if I have some
misunderstandings of your issue, welcome your pointing out and coming back
to newsgroup for further research. I'm very glad for your assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Brad Baker wrote:
> We don't want to back all the databases up at the same time as it would ta
ke
> too long.
> Thus we do full backup on about 30 databases per day:
> Sunday: Databases 1-30
> Monday: Databases 31- 61
> Tuesday: Databases 62 - 102
> etc.
>
Ok, so you didn't look at the links I sent you. The backup script backs
up each database, one at a time, checking to see if
a. it's been more than 7 days since a full backup was taken
or
b. today is Friday after 9:00pm, or Saturday, or Sunday, and it's been
more than 3 days since a full backup was taken
If either of those is true, then it runs a full backup, otherwise it
runs a transaction log backup, or truncates the log.
The point is, it's a SCRIPT, and you can alter it to fit your needs,
unlike the maintenance plans.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||My problem is:
1) That jobs in the 2005 maintenance plans can't be run on separate
schedules. They have to be run at the same time which is not what we need.
In searching online I was able to finally find someone else with the same
problem:
http://connect.microsoft.com/SQLSer...=12489
6
Apparently this scheduling problem is being addressed in SQl 2005 SP2 but
SP2 isn't due to be released until after Vista (Yuck!). Is there any way we
can call PSS and get a patch sooner? We're setting up 6 new SQL instances -
I really don't want to create 150 maintenance plans its bad enough to have
to create 30.
2) Instead of being able to associate a db with one maintenance plan and be
done with it. We now have to associate a db with each component of a plan -
Integrity Check, Optimization, Full Backup, Transaction Log backup. So in
our case we have to add the DB four separate times instead of one.
I'm quickly realizing that this isn't something we've just missed but rather
Microsoft seems to have dropped a lot of functionality that we used on a day
to day basis. It's frustrating and disappointing - We are not at all
impressed with SQL 2005.
Brad
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:8eoqAun0GHA.4280@.TK2MSFTNGXA01.phx.gbl...
> Dear Brad,
> From your description, I understand that:
> You had 5 maintenance plans for backing up your 200 databases per your SQL
> Server 2000 instance. Now you want to move the maintenance plans to your
> SQL Server 2005.
> If I have misunderstood, please let me know.
> If your solution in SQL Server 2000 can run well, it should be also
> available in SQL Server 2005. As you said:
> SQL 2000 had maintenance plans which consisted of a Database Backup Job,
> Transaction Log Backup Job, Integrity Check Job, and an Optimization Job.
> Each of these four jobs was managed by the parent maintenance plan and
> each
> job could be run on different schedules. This made it simple for us to
> manage DB maintenance.
> In SQL Server 2005, you can also create 4 maintenance plans which response
> to Database Backup Job, Transaction Log Backup job, Integreity Check job,
> and Optimization Job. If you are considering management convenience, you
> can named the plans with a name specification such as
> "instance1_db1to50_trans_backup".
> I'm not sure whether or not your concerns are for this point.
> Unfortunately
> SQL Server 2005's desgin doesn't include this function of SQL 2000.
> I do know that the task in SQL Server 2005 is at least quadruple your work
> in SQL Server 2000. I appreciate that you can give Microsoft feedback on
> this issue, and hope the next release will include this helpful function.
> You can submit a feedback via the link:
> http://connect.microsoft.com/feedba...aspx?SiteID=68
> Note: Login first before a submition of your feedback.
> Also, I understand that your situation seems complex and if I have some
> misunderstandings of your issue, welcome your pointing out and coming back
> to newsgroup for further research. I'm very glad for your assistance.
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||Tracy -
I appreciate the links you sent - I review them both but admittedly I'm not
a DBA - I'm a sysadmin. I'm not familiar with TSQL (or whatever language
those scripts are written in) thus utilizing these scripts would probably
require a lot of time especially because the scripts don't do exactly what
we need already.
It doesn't seem like they handle Optimizations and Integrity Checks -
although maybe that's what the fragmentation script does? We also need email
notifications based on failures and logging.
Additionally it seems that the scripts take one of two approaches - 1)
Backup all databases or 2) specify the names of each database manually.
Neither approach is ideal. We don't want to backup all our DBs at once and
we don't want to type out the names of all the DBs on a server - which is
why we are looking for some way to do this through a GUI where we don't have
to worry about typos.
In any case it doesn't sound to me like this would be a good solution for
us. We might be better off setting up 150 maintenance plans through the GUI.
It would be incredibly tedious and time consuming but it would allow other
less technical people at our organization add new dbs without having to edit
scripts.
Brad
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45001512.4030505@.realsqlguy.com...
> Brad Baker wrote:
> Ok, so you didn't look at the links I sent you. The backup script backs
> up each database, one at a time, checking to see if
> a. it's been more than 7 days since a full backup was taken
> or
> b. today is Friday after 9:00pm, or Saturday, or Sunday, and it's been
> more than 3 days since a full backup was taken
> If either of those is true, then it runs a full backup, otherwise it runs
> a transaction log backup, or truncates the log.
> The point is, it's a SCRIPT, and you can alter it to fit your needs,
> unlike the maintenance plans.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Friday, March 9, 2012

Maitnenance Plans start, but don't do anything

Maitenance plans and noskip

Maitenance plans and noskip

Maitenance plans and noskip

Wednesday, March 7, 2012

Maintenence Plan

Maintenence Plan

Maintenence Plan

Mainteneance plans BAK TRN

Mainteneance plans BAK TRN

Maintenance schedule in 2005

Maintenance schedule in 2005

Maintenance plans: online rebuilding of indexes...

Maintenance Plans, SQL Agent daily restart needed

Maintenance Plans, SQL Agent daily restart needed

Maintenance Plans won't allow me to select to delete jobs after 3 days

Maintenance Plans on MSDE

Maintenance plans not deleting old transaction log backups

Maintenance Plans is SSE and Studio Management Express

Maintenance Plans in SQL 2000 vs 2005

maintenance plan wizard

Blog Archive

About Me