Database mirroring and log growth

Hi all:
I have implemented database mirroring and it is working great. However, the
database log files are growing and growing and growing... is there a way to
manage the size of the log files?
ThanksHi Jim
Have you seen http://msdn2.microsoft.com/en-us/library/ms345414.aspx. If the
mirror is significantly behind the log file will grow.
John
"Jim Norton" wrote:

> Hi all:
> I have implemented database mirroring and it is working great. However, t
he
> database log files are growing and growing and growing... is there a way t
o
> manage the size of the log files?
> Thanks
>
>sql

Database mirroring and log growth

Hi all:
I have implemented database mirroring and it is working great. However, the
database log files are growing and growing and growing... is there a way to
manage the size of the log files?
Thanks
Hi Jim
Have you seen http://msdn2.microsoft.com/en-us/library/ms345414.aspx. If the
mirror is significantly behind the log file will grow.
John
"Jim Norton" wrote:

> Hi all:
> I have implemented database mirroring and it is working great. However, the
> database log files are growing and growing and growing... is there a way to
> manage the size of the log files?
> Thanks
>
>

Database Maintenance Plans for Backup

Hello

We created maintenance plans for Backup, we configured as:

1. Backup set expires after 2 days. (but we still see backup files are at the location from day one)

2. There is Overwrite and Append in backup file settings. what eaxactly overwrites means, in case we set up expire the backup set after 2 days.

please advice. Thanks, Jay

If you are using a single backup device (whether it's a tape, file location...it's the same thing), overwrite means it will replace the backup on that device with the current backup. So that device will just hold the one backup. Append means it will add the current backup to the device. That device will hold multiple backups.

If you use a single device, set it to overwrite and the backup has not expired and you have selected the option to check the expiration date of the backup, you will get an error. It is designed to prevent you from accidentally overwritting a backup that you want to keep.

-Sue

Database Maintenance Plans - Not removing Files Older than x days

I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
database on the server and have created a database maintenance plan that
backups up all the database nightly. I have also configured the maintenance
plan to delete back files older than 3 days old but this isn't currently
happening.
I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
seen this problem first. Or if anyone can confirm SP4 fixes this issue.
You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
SP4 on another system to make sure your apps work correctly first, of course.
Have you checked the various logs SQL Server uses? There's a SQL Server Log,
a SQL Server Agent Log, and the Windows Application Event Log. They might
point out what is happening.
That being said, there is an extended stored procedure that Microsoft uses
to delete the files. If the Agent service doesn't have the proper rights to
the subdirectory you might find that to be the issue.
"Gary S" wrote:

> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenance
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
>
|||What is the extended store procedure that's used so that I can check it
hasn't been removed.
"Buck Woody - Microsoft SQL Server Team" wrote:
[vbcol=seagreen]
> You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> SP4 on another system to make sure your apps work correctly first, of course.
> Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> a SQL Server Agent Log, and the Windows Application Event Log. They might
> point out what is happening.
> That being said, there is an extended stored procedure that Microsoft uses
> to delete the files. If the Agent service doesn't have the proper rights to
> the subdirectory you might find that to be the issue.
> "Gary S" wrote:
|||It's sp_delete_file - and it's undocumented, meaning unsupported for direct
use by users. Check this thread:
https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
"Gary S" wrote:
[vbcol=seagreen]
> What is the extended store procedure that's used so that I can check it
> hasn't been removed.
> "Buck Woody - Microsoft SQL Server Team" wrote:
|||Slight correction - that's XP_delete_file. Had an S in there!
"Buck Woody - Microsoft SQL Server Team" wrote:
[vbcol=seagreen]
> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
|||On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenance
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
There are a couple of common causes for this:
1. The maintenance plan is configured to do transaction log backups,
but one of the databases included is in Simple mode. The plan fails
when it hits this database, and never reaches the "cleanup" step.
2. One of the files to be deleted is "in use" by another process,
such as a tape backup, causing the cleanup process to fail.
Right-click on the maintenance plan itself (not the Agent job), look
at the history, you should be able to get an actual error message from
there.
|||You have to be a sysadmin to run this stored proc, even in SP2 CTP. I have
been told a fix will be raised for a later service pack.
Chris
"Buck Woody - Microsoft SQL Server Team"
<BuckWoodyMicrosoftSQLServerTeam@.discussions.micro soft.com> wrote in message
news:0751636B-0F16-40EA-BAE8-8ADE57217570@.microsoft.com...[vbcol=seagreen]
> It's sp_delete_file - and it's undocumented, meaning unsupported for
> direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
|||Thanks Tracey a number of the database were set to Simple Recovery. I'm not a
big fan of database mainatenance plans (perfer setting up the jobs
individual) so as I'm not 100% certain why the databases are set to simple
I'll create seperate backup jobs.
"Tracy McKibben" wrote:

> On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> There are a couple of common causes for this:
> 1. The maintenance plan is configured to do transaction log backups,
> but one of the databases included is in Simple mode. The plan fails
> when it hits this database, and never reaches the "cleanup" step.
> 2. One of the files to be deleted is "in use" by another process,
> such as a tape backup, causing the cleanup process to fail.
> Right-click on the maintenance plan itself (not the Agent job), look
> at the history, you should be able to get an actual error message from
> there.
>
|||On Feb 15, 5:13 am, Gary S <G...@.discussions.microsoft.com> wrote:
> Thanks Tracey a number of the database were set to Simple Recovery. I'm not a
> big fan of database mainatenance plans (perfer setting up the jobs
> individual) so as I'm not 100% certain why the databases are set to simple
> I'll create seperate backup jobs.
>
Here's a script of mine that might help you out:
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html

Database Maintenance Plans - Not removing Files Older than x days

I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
database on the server and have created a database maintenance plan that
backups up all the database nightly. I have also configured the maintenance
plan to delete back files older than 3 days old but this isn't currently
happening.
I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
seen this problem first. Or if anyone can confirm SP4 fixes this issue.You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
SP4 on another system to make sure your apps work correctly first, of course.
Have you checked the various logs SQL Server uses? There's a SQL Server Log,
a SQL Server Agent Log, and the Windows Application Event Log. They might
point out what is happening.
That being said, there is an extended stored procedure that Microsoft uses
to delete the files. If the Agent service doesn't have the proper rights to
the subdirectory you might find that to be the issue.
"Gary S" wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenance
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
>|||What is the extended store procedure that's used so that I can check it
hasn't been removed.
"Buck Woody - Microsoft SQL Server Team" wrote:
> You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> SP4 on another system to make sure your apps work correctly first, of course.
> Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> a SQL Server Agent Log, and the Windows Application Event Log. They might
> point out what is happening.
> That being said, there is an extended stored procedure that Microsoft uses
> to delete the files. If the Agent service doesn't have the proper rights to
> the subdirectory you might find that to be the issue.
> "Gary S" wrote:
> > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > database on the server and have created a database maintenance plan that
> > backups up all the database nightly. I have also configured the maintenance
> > plan to delete back files older than 3 days old but this isn't currently
> > happening.
> >
> > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> >
> >|||It's sp_delete_file - and it's undocumented, meaning unsupported for direct
use by users. Check this thread:
https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
"Gary S" wrote:
> What is the extended store procedure that's used so that I can check it
> hasn't been removed.
> "Buck Woody - Microsoft SQL Server Team" wrote:
> > You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> > SP4 on another system to make sure your apps work correctly first, of course.
> >
> > Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> > a SQL Server Agent Log, and the Windows Application Event Log. They might
> > point out what is happening.
> >
> > That being said, there is an extended stored procedure that Microsoft uses
> > to delete the files. If the Agent service doesn't have the proper rights to
> > the subdirectory you might find that to be the issue.
> >
> > "Gary S" wrote:
> >
> > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > > database on the server and have created a database maintenance plan that
> > > backups up all the database nightly. I have also configured the maintenance
> > > plan to delete back files older than 3 days old but this isn't currently
> > > happening.
> > >
> > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> > >
> > >|||Slight correction - that's XP_delete_file. Had an S in there!
"Buck Woody - Microsoft SQL Server Team" wrote:
> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
> > What is the extended store procedure that's used so that I can check it
> > hasn't been removed.
> >
> > "Buck Woody - Microsoft SQL Server Team" wrote:
> >
> > > You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> > > SP4 on another system to make sure your apps work correctly first, of course.
> > >
> > > Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> > > a SQL Server Agent Log, and the Windows Application Event Log. They might
> > > point out what is happening.
> > >
> > > That being said, there is an extended stored procedure that Microsoft uses
> > > to delete the files. If the Agent service doesn't have the proper rights to
> > > the subdirectory you might find that to be the issue.
> > >
> > > "Gary S" wrote:
> > >
> > > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > > > database on the server and have created a database maintenance plan that
> > > > backups up all the database nightly. I have also configured the maintenance
> > > > plan to delete back files older than 3 days old but this isn't currently
> > > > happening.
> > > >
> > > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > > > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> > > >
> > > >|||On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenance
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
There are a couple of common causes for this:
1. The maintenance plan is configured to do transaction log backups,
but one of the databases included is in Simple mode. The plan fails
when it hits this database, and never reaches the "cleanup" step.
2. One of the files to be deleted is "in use" by another process,
such as a tape backup, causing the cleanup process to fail.
Right-click on the maintenance plan itself (not the Agent job), look
at the history, you should be able to get an actual error message from
there.|||You have to be a sysadmin to run this stored proc, even in SP2 CTP. I have
been told a fix will be raised for a later service pack.
Chris
"Buck Woody - Microsoft SQL Server Team"
<BuckWoodyMicrosoftSQLServerTeam@.discussions.microsoft.com> wrote in message
news:0751636B-0F16-40EA-BAE8-8ADE57217570@.microsoft.com...
> It's sp_delete_file - and it's undocumented, meaning unsupported for
> direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
>> What is the extended store procedure that's used so that I can check it
>> hasn't been removed.
>> "Buck Woody - Microsoft SQL Server Team" wrote:
>> > You'll want to apply SP4 anyway, for lots of other reasons. Test the
>> > setup of
>> > SP4 on another system to make sure your apps work correctly first, of
>> > course.
>> >
>> > Have you checked the various logs SQL Server uses? There's a SQL Server
>> > Log,
>> > a SQL Server Agent Log, and the Windows Application Event Log. They
>> > might
>> > point out what is happening.
>> >
>> > That being said, there is an extended stored procedure that Microsoft
>> > uses
>> > to delete the files. If the Agent service doesn't have the proper
>> > rights to
>> > the subdirectory you might find that to be the issue.
>> >
>> > "Gary S" wrote:
>> >
>> > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a
>> > > number of
>> > > database on the server and have created a database maintenance plan
>> > > that
>> > > backups up all the database nightly. I have also configured the
>> > > maintenance
>> > > plan to delete back files older than 3 days old but this isn't
>> > > currently
>> > > happening.
>> > >
>> > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone
>> > > else has
>> > > seen this problem first. Or if anyone can confirm SP4 fixes this
>> > > issue.
>> > >
>> > >|||> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users.
Buck,
Above is for 2005. For 2000, the files are removed by sqlmaint.exe (which is called by xp_sqlmaint
if the plan is created by the wizard).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Buck Woody - Microsoft SQL Server Team" <BuckWoodyMicrosoftSQLServerTeam@.discussions.microsoft.com>
wrote in message news:0751636B-0F16-40EA-BAE8-8ADE57217570@.microsoft.com...
> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
>> What is the extended store procedure that's used so that I can check it
>> hasn't been removed.
>> "Buck Woody - Microsoft SQL Server Team" wrote:
>> > You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
>> > SP4 on another system to make sure your apps work correctly first, of course.
>> >
>> > Have you checked the various logs SQL Server uses? There's a SQL Server Log,
>> > a SQL Server Agent Log, and the Windows Application Event Log. They might
>> > point out what is happening.
>> >
>> > That being said, there is an extended stored procedure that Microsoft uses
>> > to delete the files. If the Agent service doesn't have the proper rights to
>> > the subdirectory you might find that to be the issue.
>> >
>> > "Gary S" wrote:
>> >
>> > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
>> > > database on the server and have created a database maintenance plan that
>> > > backups up all the database nightly. I have also configured the maintenance
>> > > plan to delete back files older than 3 days old but this isn't currently
>> > > happening.
>> > >
>> > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
>> > > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
>> > >
>> > >|||Thanks Tracey a number of the database were set to Simple Recovery. I'm not a
big fan of database mainatenance plans (perfer setting up the jobs
individual) so as I'm not 100% certain why the databases are set to simple
I'll create seperate backup jobs.
"Tracy McKibben" wrote:
> On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > database on the server and have created a database maintenance plan that
> > backups up all the database nightly. I have also configured the maintenance
> > plan to delete back files older than 3 days old but this isn't currently
> > happening.
> >
> > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> There are a couple of common causes for this:
> 1. The maintenance plan is configured to do transaction log backups,
> but one of the databases included is in Simple mode. The plan fails
> when it hits this database, and never reaches the "cleanup" step.
> 2. One of the files to be deleted is "in use" by another process,
> such as a tape backup, causing the cleanup process to fail.
> Right-click on the maintenance plan itself (not the Agent job), look
> at the history, you should be able to get an actual error message from
> there.
>|||On Feb 15, 5:13 am, Gary S <G...@.discussions.microsoft.com> wrote:
> Thanks Tracey a number of the database were set to Simple Recovery. I'm not a
> big fan of database mainatenance plans (perfer setting up the jobs
> individual) so as I'm not 100% certain why the databases are set to simple
> I'll create seperate backup jobs.
>
Here's a script of mine that might help you out:
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html

Database Maintenance Plans - Not removing Files Older than x days

I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number o
f
database on the server and have created a database maintenance plan that
backups up all the database nightly. I have also configured the maintenance
plan to delete back files older than 3 days old but this isn't currently
happening.
I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
seen this problem first. Or if anyone can confirm SP4 fixes this issue.You'll want to apply SP4 anyway, for lots of other reasons. Test the setup o
f
SP4 on another system to make sure your apps work correctly first, of course
.
Have you checked the various logs SQL Server uses? There's a SQL Server Log,
a SQL Server Agent Log, and the Windows Application Event Log. They might
point out what is happening.
That being said, there is an extended stored procedure that Microsoft uses
to delete the files. If the Agent service doesn't have the proper rights to
the subdirectory you might find that to be the issue.
"Gary S" wrote:

> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number
of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenanc
e
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else h
as
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
>|||On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number
of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenanc
e
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else h
as
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
There are a couple of common causes for this:
1. The maintenance plan is configured to do transaction log backups,
but one of the databases included is in Simple mode. The plan fails
when it hits this database, and never reaches the "cleanup" step.
2. One of the files to be deleted is "in use" by another process,
such as a tape backup, causing the cleanup process to fail.
Right-click on the maintenance plan itself (not the Agent job), look
at the history, you should be able to get an actual error message from
there.

Database Maintenance Plan not removing older files

I've recently upgraded the SQL server from 7 to 2000 SP3. The database
maintenance plans were set to remove backup and log files older than 2 days,
but does not seem to be working any more. I removed the plan and entered a
new one last week and still the files are building and are not being removed
.
Any suggestions.
Thanks
Jim Tyou can write an ActiveX script and have it execute before your job
runs to clean up the server prior to maintenance.
Jim T wrote:
> I've recently upgraded the SQL server from 7 to 2000 SP3. The database
> maintenance plans were set to remove backup and log files older than 2 day
s,
> but does not seem to be working any more. I removed the plan and entered a
> new one last week and still the files are building and are not being remov
ed.
> Any suggestions.
> Thanks
> Jim T|||I was going to write a vb program and schedule it to clean up, but I'm
surprised that it worked fine in 7 but now fails to work under SQL 2000. May
be I should not be surprised at all.
"GlennThomas5" wrote:

> you can write an ActiveX script and have it execute before your job
> runs to clean up the server prior to maintenance.
> Jim T wrote:
>|||Jim T wrote:
> I've recently upgraded the SQL server from 7 to 2000 SP3. The database
> maintenance plans were set to remove backup and log files older than 2 day
s,
> but does not seem to be working any more. I removed the plan and entered a
> new one last week and still the files are building and are not being remov
ed.
> Any suggestions.
> Thanks
> Jim T
Check the maintenance plan history (right-click on the maintenance plan
in Enterprise Manager). Any error messages reported there?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Under the Plan History - No errors were reported - Task says 'Succeeded'
"Tracy McKibben" wrote:

> Jim T wrote:
> Check the maintenance plan history (right-click on the maintenance plan
> in Enterprise Manager). Any error messages reported there?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||I also checked the security on the directory which currently has "EVERYONE"
with all rights (Yes, I know it could be a security breach - but had to try
something)
"Tracy McKibben" wrote:

> Jim T wrote:
> Check the maintenance plan history (right-click on the maintenance plan
> in Enterprise Manager). Any error messages reported there?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Jim T wrote:
> Under the Plan History - No errors were reported - Task says 'Succeeded'
>
Is that the JOB history, or the PLAN history?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||One of the entries from the Plan history.
I looked at your web site and it reminded me to look into the SQL Server
Agnts jobs and checked the steps on the Database maintenance, even though th
e
DB Maint util says 2days, the script steps is saying
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
12E9433F-9CC4-4ACC-BF1D-EC7E4566B3EA -Rpt "D:\SQLDATA\LOG\DB Maintenance
Plan14.txt" -DelTxtRpt 4DAYS -WriteHistory -VrfyBackup -BkUpMedia DISK
-BkUpDB "D:\SQLDATA\BACKUP" -DelBkUps 4DAYS -CrBkSubDir -BkExt "BAK"'
I changed the script to 2DAYS and will check processing tonight. Since I
only found the issue 4 days ago and the plan is set for 2 days of files, it
was surprising to see the script with 4 instead of 2. I'll let you know
tommorrow if that worked.
"Tracy McKibben" wrote:

> Jim T wrote:
> Is that the JOB history, or the PLAN history?
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Resolved, once the script step was changed to 2DAYS it is now cleaning up
after itself. Strange that the PLAN wizard shows 2 days while the script
shows 4DAYS - but I'll keep that in mind.
Thank you Tracy for a good web site
"Tracy McKibben" wrote:

> Jim T wrote:
> Is that the JOB history, or the PLAN history?
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Database Maintenance Plan not removing older files

I've recently upgraded the SQL server from 7 to 2000 SP3. The database
maintenance plans were set to remove backup and log files older than 2 days,
but does not seem to be working any more. I removed the plan and entered a
new one last week and still the files are building and are not being removed.
Any suggestions.
Thanks
Jim Tyou can write an ActiveX script and have it execute before your job
runs to clean up the server prior to maintenance.
Jim T wrote:
> I've recently upgraded the SQL server from 7 to 2000 SP3. The database
> maintenance plans were set to remove backup and log files older than 2 days,
> but does not seem to be working any more. I removed the plan and entered a
> new one last week and still the files are building and are not being removed.
> Any suggestions.
> Thanks
> Jim T|||I was going to write a vb program and schedule it to clean up, but I'm
surprised that it worked fine in 7 but now fails to work under SQL 2000. May
be I should not be surprised at all.
"GlennThomas5" wrote:
> you can write an ActiveX script and have it execute before your job
> runs to clean up the server prior to maintenance.
> Jim T wrote:
> > I've recently upgraded the SQL server from 7 to 2000 SP3. The database
> > maintenance plans were set to remove backup and log files older than 2 days,
> > but does not seem to be working any more. I removed the plan and entered a
> > new one last week and still the files are building and are not being removed.
> >
> > Any suggestions.
> >
> > Thanks
> > Jim T
>|||Jim T wrote:
> I've recently upgraded the SQL server from 7 to 2000 SP3. The database
> maintenance plans were set to remove backup and log files older than 2 days,
> but does not seem to be working any more. I removed the plan and entered a
> new one last week and still the files are building and are not being removed.
> Any suggestions.
> Thanks
> Jim T
Check the maintenance plan history (right-click on the maintenance plan
in Enterprise Manager). Any error messages reported there?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Under the Plan History - No errors were reported - Task says 'Succeeded'
"Tracy McKibben" wrote:
> Jim T wrote:
> > I've recently upgraded the SQL server from 7 to 2000 SP3. The database
> > maintenance plans were set to remove backup and log files older than 2 days,
> > but does not seem to be working any more. I removed the plan and entered a
> > new one last week and still the files are building and are not being removed.
> >
> > Any suggestions.
> >
> > Thanks
> > Jim T
> Check the maintenance plan history (right-click on the maintenance plan
> in Enterprise Manager). Any error messages reported there?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||I also checked the security on the directory which currently has "EVERYONE"
with all rights (Yes, I know it could be a security breach - but had to try
something)
"Tracy McKibben" wrote:
> Jim T wrote:
> > I've recently upgraded the SQL server from 7 to 2000 SP3. The database
> > maintenance plans were set to remove backup and log files older than 2 days,
> > but does not seem to be working any more. I removed the plan and entered a
> > new one last week and still the files are building and are not being removed.
> >
> > Any suggestions.
> >
> > Thanks
> > Jim T
> Check the maintenance plan history (right-click on the maintenance plan
> in Enterprise Manager). Any error messages reported there?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Jim T wrote:
> Under the Plan History - No errors were reported - Task says 'Succeeded'
>
Is that the JOB history, or the PLAN history?
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||One of the entries from the Plan history.
I looked at your web site and it reminded me to look into the SQL Server
Agnts jobs and checked the steps on the Database maintenance, even though the
DB Maint util says 2days, the script steps is saying
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
12E9433F-9CC4-4ACC-BF1D-EC7E4566B3EA -Rpt "D:\SQLDATA\LOG\DB Maintenance
Plan14.txt" -DelTxtRpt 4DAYS -WriteHistory -VrfyBackup -BkUpMedia DISK
-BkUpDB "D:\SQLDATA\BACKUP" -DelBkUps 4DAYS -CrBkSubDir -BkExt "BAK"'
I changed the script to 2DAYS and will check processing tonight. Since I
only found the issue 4 days ago and the plan is set for 2 days of files, it
was surprising to see the script with 4 instead of 2. I'll let you know
tommorrow if that worked.
"Tracy McKibben" wrote:
> Jim T wrote:
> > Under the Plan History - No errors were reported - Task says 'Succeeded'
> >
> Is that the JOB history, or the PLAN history?
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Resolved, once the script step was changed to 2DAYS it is now cleaning up
after itself. Strange that the PLAN wizard shows 2 days while the script
shows 4DAYS - but I'll keep that in mind.
Thank you Tracy for a good web site
"Tracy McKibben" wrote:
> Jim T wrote:
> > Under the Plan History - No errors were reported - Task says 'Succeeded'
> >
> Is that the JOB history, or the PLAN history?
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Database Maintenance Plan no longer deleting old files

My maintenance plan is set up to delete the old database dump files and transaction logs. After months of this working, the dump files are no longer being deleted. This has happened before. I recreated the plan and it worked for a while, then stopped again. The old transaction logs are being deleted as scheduled. Please help.Below KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
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
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Smith" <anonymous@.discussions.microsoft.com> wrote in message
news:B0355203-D93B-44F1-AD39-9EEE5194DEE3@.microsoft.com...
> My maintenance plan is set up to delete the old database dump files and transaction logs. After
months of this working, the dump files are no longer being deleted. This has happened before. I
recreated the plan and it worked for a while, then stopped again. The old transaction logs are
being deleted as scheduled. Please help.|||"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in message news:<O7uyC2XxDHA.3224@.tk2msftngp13.phx.gbl>...
> Below KB might help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> 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
>
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Smith" <anonymous@.discussions.microsoft.com> wrote in message
> news:B0355203-D93B-44F1-AD39-9EEE5194DEE3@.microsoft.com...
> > My maintenance plan is set up to delete the old database dump files and transaction logs. After
> months of this working, the dump files are no longer being deleted. This has happened before. I
> recreated the plan and it worked for a while, then stopped again. The old transaction logs are
> being deleted as scheduled. Please help.
You may also want to check what the 'effective' date is on your job -
see if your date range is out of whack, or else, if the process that
is supposed to be deleting the files is running into a 'file lock'. I
have had problems before like this, where a process that copies my old
backup files to a new directory was not actually terminating all the
time, and so then whenever the other process tried to delete them, it
couldn't. If you can look at that - it may help - or else, try
rebooting the server (if you can) and waiting to see if it behaves
normally again.
When in doubt, reboot *grin*
Sonya

Database Maintenance Plan - Can't select Remove Files Older Than .

Hello,
Having a strange problem and hoping that someone else has seen this before.
Have an up and running SQL Server 2000 (no problems that I can detect).
Trying to create a maintenance plan to backup the databases each night.
Start the maintenance plan wizard and get to the "Specify Backup Disk
Directory" screen and the "Remove Files Older Than" drop down box is blank,
can't select "Weeks", "Days", etc.
Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, with
no change.
Thanks in advance for any help or insight.
Hi
Have you tried using the tools on a different machine or against a different
server?
John
"DHood" wrote:

> Hello,
> Having a strange problem and hoping that someone else has seen this before.
> Have an up and running SQL Server 2000 (no problems that I can detect).
> Trying to create a maintenance plan to backup the databases each night.
> Start the maintenance plan wizard and get to the "Specify Backup Disk
> Directory" screen and the "Remove Files Older Than" drop down box is blank,
> can't select "Weeks", "Days", etc.
> Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, with
> no change.
> Thanks in advance for any help or insight.
|||hi dhood,
i have the same problem!
is it possible that this problem exists since you updated to sp4?
because in my case all sql2000sp4 have this problem, sp3 not...
with mangerstudio from sql 2005 this problem is gone, but others appear...
greets
jeff
"DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> Hello,
> Having a strange problem and hoping that someone else has seen this
> before.
> Have an up and running SQL Server 2000 (no problems that I can detect).
> Trying to create a maintenance plan to backup the databases each night.
> Start the maintenance plan wizard and get to the "Specify Backup Disk
> Directory" screen and the "Remove Files Older Than" drop down box is
> blank,
> can't select "Weeks", "Days", etc.
> Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> with
> no change.
> Thanks in advance for any help or insight.

Database Maintenance Plan - Can't select Remove Files Older Than .

Hello,
Having a strange problem and hoping that someone else has seen this before.
Have an up and running SQL Server 2000 (no problems that I can detect).
Trying to create a maintenance plan to backup the databases each night.
Start the maintenance plan wizard and get to the "Specify Backup Disk
Directory" screen and the "Remove Files Older Than" drop down box is blank,
can't select "Weeks", "Days", etc.
Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, with
no change.
Thanks in advance for any help or insight.Hi
Have you tried using the tools on a different machine or against a different
server?
John
"DHood" wrote:
> Hello,
> Having a strange problem and hoping that someone else has seen this before.
> Have an up and running SQL Server 2000 (no problems that I can detect).
> Trying to create a maintenance plan to backup the databases each night.
> Start the maintenance plan wizard and get to the "Specify Backup Disk
> Directory" screen and the "Remove Files Older Than" drop down box is blank,
> can't select "Weeks", "Days", etc.
> Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, with
> no change.
> Thanks in advance for any help or insight.|||hi dhood,
i have the same problem!
is it possible that this problem exists since you updated to sp4?
because in my case all sql2000sp4 have this problem, sp3 not...
with mangerstudio from sql 2005 this problem is gone, but others appear...
greets
jeff
"DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> Hello,
> Having a strange problem and hoping that someone else has seen this
> before.
> Have an up and running SQL Server 2000 (no problems that I can detect).
> Trying to create a maintenance plan to backup the databases each night.
> Start the maintenance plan wizard and get to the "Specify Backup Disk
> Directory" screen and the "Remove Files Older Than" drop down box is
> blank,
> can't select "Weeks", "Days", etc.
> Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> with
> no change.
> Thanks in advance for any help or insight.|||No, I definiately had the problem before I upgraded to SP4. I just tried the
SP4 upgrade in hopes that it would fix the problem.
"j.goines" wrote:
> hi dhood,
> i have the same problem!
> is it possible that this problem exists since you updated to sp4?
> because in my case all sql2000sp4 have this problem, sp3 not...
> with mangerstudio from sql 2005 this problem is gone, but others appear...
> greets
> jeff
> "DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> > Hello,
> >
> > Having a strange problem and hoping that someone else has seen this
> > before.
> >
> > Have an up and running SQL Server 2000 (no problems that I can detect).
> > Trying to create a maintenance plan to backup the databases each night.
> > Start the maintenance plan wizard and get to the "Specify Backup Disk
> > Directory" screen and the "Remove Files Older Than" drop down box is
> > blank,
> > can't select "Weeks", "Days", etc.
> >
> > Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> > with
> > no change.
> >
> > Thanks in advance for any help or insight.
>
>|||I have tried it against another server, same problem. I have tried accessing
the problem server from the tools on another server and everything looks
normal. So it appears to be something specific on that machine.
"John Bell" wrote:
> Hi
> Have you tried using the tools on a different machine or against a different
> server?
> John
> "DHood" wrote:
> > Hello,
> >
> > Having a strange problem and hoping that someone else has seen this before.
> >
> > Have an up and running SQL Server 2000 (no problems that I can detect).
> > Trying to create a maintenance plan to backup the databases each night.
> > Start the maintenance plan wizard and get to the "Specify Backup Disk
> > Directory" screen and the "Remove Files Older Than" drop down box is blank,
> > can't select "Weeks", "Days", etc.
> >
> > Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, with
> > no change.
> >
> > Thanks in advance for any help or insight.|||Hi
It certainly seems that a dll or somthing is corrupted on that machine. Did
you remove the shared dlls when you un-installed the client tools?
John
"DHood" wrote:
> No, I definiately had the problem before I upgraded to SP4. I just tried the
> SP4 upgrade in hopes that it would fix the problem.
> "j.goines" wrote:
> > hi dhood,
> > i have the same problem!
> > is it possible that this problem exists since you updated to sp4?
> > because in my case all sql2000sp4 have this problem, sp3 not...
> > with mangerstudio from sql 2005 this problem is gone, but others appear...
> >
> > greets
> > jeff
> >
> > "DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
> > news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> > > Hello,
> > >
> > > Having a strange problem and hoping that someone else has seen this
> > > before.
> > >
> > > Have an up and running SQL Server 2000 (no problems that I can detect).
> > > Trying to create a maintenance plan to backup the databases each night.
> > > Start the maintenance plan wizard and get to the "Specify Backup Disk
> > > Directory" screen and the "Remove Files Older Than" drop down box is
> > > blank,
> > > can't select "Weeks", "Days", etc.
> > >
> > > Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> > > with
> > > no change.
> > >
> > > Thanks in advance for any help or insight.
> >
> >
> >|||No, I didn't remove any files manually. Do you know what and where they are?
"John Bell" wrote:
> Hi
> It certainly seems that a dll or somthing is corrupted on that machine. Did
> you remove the shared dlls when you un-installed the client tools?
> John
> "DHood" wrote:
> > No, I definiately had the problem before I upgraded to SP4. I just tried the
> > SP4 upgrade in hopes that it would fix the problem.
> >
> > "j.goines" wrote:
> >
> > > hi dhood,
> > > i have the same problem!
> > > is it possible that this problem exists since you updated to sp4?
> > > because in my case all sql2000sp4 have this problem, sp3 not...
> > > with mangerstudio from sql 2005 this problem is gone, but others appear...
> > >
> > > greets
> > > jeff
> > >
> > > "DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> > > > Hello,
> > > >
> > > > Having a strange problem and hoping that someone else has seen this
> > > > before.
> > > >
> > > > Have an up and running SQL Server 2000 (no problems that I can detect).
> > > > Trying to create a maintenance plan to backup the databases each night.
> > > > Start the maintenance plan wizard and get to the "Specify Backup Disk
> > > > Directory" screen and the "Remove Files Older Than" drop down box is
> > > > blank,
> > > > can't select "Weeks", "Days", etc.
> > > >
> > > > Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> > > > with
> > > > no change.
> > > >
> > > > Thanks in advance for any help or insight.
> > >
> > >
> > >|||Hi
When you un-install it usually prompts to remove shared files. You may want
to see what dlls are loaded when you start the application.
John
"DHood" wrote:
> No, I didn't remove any files manually. Do you know what and where they are?
> "John Bell" wrote:
> > Hi
> >
> > It certainly seems that a dll or somthing is corrupted on that machine. Did
> > you remove the shared dlls when you un-installed the client tools?
> >
> > John
> >
> > "DHood" wrote:
> >
> > > No, I definiately had the problem before I upgraded to SP4. I just tried the
> > > SP4 upgrade in hopes that it would fix the problem.
> > >
> > > "j.goines" wrote:
> > >
> > > > hi dhood,
> > > > i have the same problem!
> > > > is it possible that this problem exists since you updated to sp4?
> > > > because in my case all sql2000sp4 have this problem, sp3 not...
> > > > with mangerstudio from sql 2005 this problem is gone, but others appear...
> > > >
> > > > greets
> > > > jeff
> > > >
> > > > "DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > > news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> > > > > Hello,
> > > > >
> > > > > Having a strange problem and hoping that someone else has seen this
> > > > > before.
> > > > >
> > > > > Have an up and running SQL Server 2000 (no problems that I can detect).
> > > > > Trying to create a maintenance plan to backup the databases each night.
> > > > > Start the maintenance plan wizard and get to the "Specify Backup Disk
> > > > > Directory" screen and the "Remove Files Older Than" drop down box is
> > > > > blank,
> > > > > can't select "Weeks", "Days", etc.
> > > > >
> > > > > Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> > > > > with
> > > > > no change.
> > > > >
> > > > > Thanks in advance for any help or insight.
> > > >
> > > >
> > > >|||See if this helps--
If you profile when on that form you should see the following:
xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'
I was getting the same problem that you were, and discovered that this proc
was returning an error b/c the registry key did not exist. I entered key and
a valid value and the problem went away.
I know several people have posted on this same issue, so I hope someone gets
some value out of this.
JIM BOUTIT
MCSE MCDBA
"John Bell" wrote:
> Hi
> When you un-install it usually prompts to remove shared files. You may want
> to see what dlls are loaded when you start the application.
> John
>
> "DHood" wrote:
> > No, I didn't remove any files manually. Do you know what and where they are?
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > It certainly seems that a dll or somthing is corrupted on that machine. Did
> > > you remove the shared dlls when you un-installed the client tools?
> > >
> > > John
> > >
> > > "DHood" wrote:
> > >
> > > > No, I definiately had the problem before I upgraded to SP4. I just tried the
> > > > SP4 upgrade in hopes that it would fix the problem.
> > > >
> > > > "j.goines" wrote:
> > > >
> > > > > hi dhood,
> > > > > i have the same problem!
> > > > > is it possible that this problem exists since you updated to sp4?
> > > > > because in my case all sql2000sp4 have this problem, sp3 not...
> > > > > with mangerstudio from sql 2005 this problem is gone, but others appear...
> > > > >
> > > > > greets
> > > > > jeff
> > > > >
> > > > > "DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > > > news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> > > > > > Hello,
> > > > > >
> > > > > > Having a strange problem and hoping that someone else has seen this
> > > > > > before.
> > > > > >
> > > > > > Have an up and running SQL Server 2000 (no problems that I can detect).
> > > > > > Trying to create a maintenance plan to backup the databases each night.
> > > > > > Start the maintenance plan wizard and get to the "Specify Backup Disk
> > > > > > Directory" screen and the "Remove Files Older Than" drop down box is
> > > > > > blank,
> > > > > > can't select "Weeks", "Days", etc.
> > > > > >
> > > > > > Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> > > > > > with
> > > > > > no change.
> > > > > >
> > > > > > Thanks in advance for any help or insight.
> > > > >
> > > > >
> > > > >

Database Maintenance Plan - Can't select Remove Files Older Than .

Hello,
Having a strange problem and hoping that someone else has seen this before.
Have an up and running SQL Server 2000 (no problems that I can detect).
Trying to create a maintenance plan to backup the databases each night.
Start the maintenance plan wizard and get to the "Specify Backup Disk
Directory" screen and the "Remove Files Older Than" drop down box is blank,
can't select "Weeks", "Days", etc.
Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, with
no change.
Thanks in advance for any help or insight.Hi
Have you tried using the tools on a different machine or against a different
server?
John
"DHood" wrote:

> Hello,
> Having a strange problem and hoping that someone else has seen this before
.
> Have an up and running SQL Server 2000 (no problems that I can detect).
> Trying to create a maintenance plan to backup the databases each night.
> Start the maintenance plan wizard and get to the "Specify Backup Disk
> Directory" screen and the "Remove Files Older Than" drop down box is blank
,
> can't select "Weeks", "Days", etc.
> Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, wi
th
> no change.
> Thanks in advance for any help or insight.|||hi dhood,
i have the same problem!
is it possible that this problem exists since you updated to sp4?
because in my case all sql2000sp4 have this problem, sp3 not...
with mangerstudio from sql 2005 this problem is gone, but others appear...
greets
jeff
"DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> Hello,
> Having a strange problem and hoping that someone else has seen this
> before.
> Have an up and running SQL Server 2000 (no problems that I can detect).
> Trying to create a maintenance plan to backup the databases each night.
> Start the maintenance plan wizard and get to the "Specify Backup Disk
> Directory" screen and the "Remove Files Older Than" drop down box is
> blank,
> can't select "Weeks", "Days", etc.
> Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> with
> no change.
> Thanks in advance for any help or insight.

Database Maintenance Plan

Hello, everyone:
I have a Database Maintenance Plan that back up some databases and delete old backup files that more than two day. Can any one have idea which action is first, backup or delete old files?
Thanks
ZYTIt backs up first then deletes.

Database Maintenance Plan

A previous employee created the plan t0 back up TRN and BAK files.
When the previous employees account is removed I get an event log that
the TRN backup failed because it could not verify that the previous
users ID had access to backup point. In the Enterprise Manager I see
the plan, but am not sure how to associate it with a different login.
Do I have to recreate the plan under my id, or is there a way I can
have it run under my ID?
I don't think you can change the plan owner, but you can try changing the owner of each job that the
plan created... I haven't tried this myself, so worst case, it will mess up your plan...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<natas234@.yahoo.com> wrote in message news:1127407000.465696.229780@.g49g2000cwa.googlegr oups.com...
>A previous employee created the plan t0 back up TRN and BAK files.
> When the previous employees account is removed I get an event log that
> the TRN backup failed because it could not verify that the previous
> users ID had access to backup point. In the Enterprise Manager I see
> the plan, but am not sure how to associate it with a different login.
> Do I have to recreate the plan under my id, or is there a way I can
> have it run under my ID?
>
|||That was it. Changed job owner and force fired them off and confirmed
backup location contained new files. Thanks.

Database Maintenance Plan

A previous employee created the plan t0 back up TRN and BAK files.
When the previous employees account is removed I get an event log that
the TRN backup failed because it could not verify that the previous
users ID had access to backup point. In the Enterprise Manager I see
the plan, but am not sure how to associate it with a different login.
Do I have to recreate the plan under my id, or is there a way I can
have it run under my ID?I don't think you can change the plan owner, but you can try changing the owner of each job that the
plan created... I haven't tried this myself, so worst case, it will mess up your plan...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<natas234@.yahoo.com> wrote in message news:1127407000.465696.229780@.g49g2000cwa.googlegroups.com...
>A previous employee created the plan t0 back up TRN and BAK files.
> When the previous employees account is removed I get an event log that
> the TRN backup failed because it could not verify that the previous
> users ID had access to backup point. In the Enterprise Manager I see
> the plan, but am not sure how to associate it with a different login.
> Do I have to recreate the plan under my id, or is there a way I can
> have it run under my ID?
>|||That was it. Changed job owner and force fired them off and confirmed
backup location contained new files. Thanks.

Database Maintenance Plan

A previous employee created the plan t0 back up TRN and BAK files.
When the previous employees account is removed I get an event log that
the TRN backup failed because it could not verify that the previous
users ID had access to backup point. In the Enterprise Manager I see
the plan, but am not sure how to associate it with a different login.
Do I have to recreate the plan under my id, or is there a way I can
have it run under my ID?I don't think you can change the plan owner, but you can try changing the ow
ner of each job that the
plan created... I haven't tried this myself, so worst case, it will mess up
your plan...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<natas234@.yahoo.com> wrote in message news:1127407000.465696.229780@.g49g2000cwa.googlegroups
.com...
>A previous employee created the plan t0 back up TRN and BAK files.
> When the previous employees account is removed I get an event log that
> the TRN backup failed because it could not verify that the previous
> users ID had access to backup point. In the Enterprise Manager I see
> the plan, but am not sure how to associate it with a different login.
> Do I have to recreate the plan under my id, or is there a way I can
> have it run under my ID?
>|||That was it. Changed job owner and force fired them off and confirmed
backup location contained new files. Thanks.

Database Maintenance - Order of Actions

Is there anyway to make the maintenance plan delete the backup files
BEFORE it backs up. My backups are failing due to lack of disk space,
because it essentially creates two copies of backups before deleting.
I know i can just write a script to delete them before hand, but it
would be alot neater to use the maintenace plan because the maintenance
plan is more dynamic that the script. Suggestions?
KellyYes. If you create a backup plan using the Database Maintenance Wizard, you
can set the amount of time you want to keep the backup files. In SQL 2005 it
translates in a Maintenance Cleanup Task.
"KellyLeia@.gmail.com" wrote:
> Is there anyway to make the maintenance plan delete the backup files
> BEFORE it backs up. My backups are failing due to lack of disk space,
> because it essentially creates two copies of backups before deleting.
> I know i can just write a script to delete them before hand, but it
> would be alot neater to use the maintenace plan because the maintenance
> plan is more dynamic that the script. Suggestions?
> Kelly
>|||"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
<EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Yes. If you create a backup plan using the Database Maintenance Wizard,
> you
> can set the amount of time you want to keep the backup files. In SQL 2005
> it
> translates in a Maintenance Cleanup Task.
>
Right, 2000 is the same way. But as KellyLeia points out, that deletion
occurs AFTER the backup completes... if successul.
The problem is, if the backup fails, the deletion never occurs.
In addtion, I'd caution against doing what Kelly wants to do. Because if
the backup fails for another reason, you've already deleted one of your
backups.
Personally, I'd try to invest in more disk space if possible.
> "KellyLeia@.gmail.com" wrote:
>> Is there anyway to make the maintenance plan delete the backup files
>> BEFORE it backs up. My backups are failing due to lack of disk space,
>> because it essentially creates two copies of backups before deleting.
>> I know i can just write a script to delete them before hand, but it
>> would be alot neater to use the maintenace plan because the maintenance
>> plan is more dynamic that the script. Suggestions?
>> Kelly
>>|||Conviently, we have tape backups that run at 4am (the db backups run
between 1 and 2 am). So if the job fails, we've still got yesterday's
backup on tape. If it fails as it did, i don't get in till 8am, so the
damage is done. Thanks for your help guys.
Greg D. Moore (Strider) wrote:
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
> <EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.microsoft.com> wrote in message
> news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> > Yes. If you create a backup plan using the Database Maintenance Wizard,
> > you
> > can set the amount of time you want to keep the backup files. In SQL 2005
> > it
> > translates in a Maintenance Cleanup Task.
> >
> Right, 2000 is the same way. But as KellyLeia points out, that deletion
> occurs AFTER the backup completes... if successul.
> The problem is, if the backup fails, the deletion never occurs.
> In addtion, I'd caution against doing what Kelly wants to do. Because if
> the backup fails for another reason, you've already deleted one of your
> backups.
> Personally, I'd try to invest in more disk space if possible.
>
> > "KellyLeia@.gmail.com" wrote:
> >
> >> Is there anyway to make the maintenance plan delete the backup files
> >> BEFORE it backs up. My backups are failing due to lack of disk space,
> >> because it essentially creates two copies of backups before deleting.
> >> I know i can just write a script to delete them before hand, but it
> >> would be alot neater to use the maintenace plan because the maintenance
> >> plan is more dynamic that the script. Suggestions?
> >>
> >> Kelly
> >>
> >>

Database Maintenance - no backups being removed

Database maintenance plan doesn't allow me to choose to
delete files older than x number of days/weeks. I can
choose the option, input a number, but the drop down for
days/weeks contains no items.
I Jon,
a friend tell me to add in the registry the string value BackupDirectory
with default C: at the address
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\"Instance_Na me"\MSSQLServer...
and this work!!!
Mirco Scott
mailto: mscotta@.giada.it
Giada s.r.l.
<jon.epstein@.infov{REMOVE}.com> ha scritto nel messaggio
news:2cde01c42886$1f8f4bf0$a401280a@.phx.gbl...
> Database maintenance plan doesn't allow me to choose to
> delete files older than x number of days/weeks. I can
> choose the option, input a number, but the drop down for
> days/weeks contains no items.

Database Maintenance

I have a maintenance plan set up to:
Remove unused space from database files
Shrink database when it grows beyond: 50 MB
Amount of free space to remain after shrink = 10%
Does this effect log files or just data files, or both?
--
Message posted via http://www.sqlmonster.comboth.
Many DBA's would not recommend shrinking the Database unless you
specifically are running into storage space issues.
My personal recommendation is that you disable this feature.
Greg Jackson
PDX, Oregon|||The maintenance plan will do a dbcc shrinkdatabase, not a
dbcc shrinkfile. As Greg posted, you really don't need this
in a regular maintenance plan. It's better to just size your
files appropriately and let them have the space they may
need for your normal operations. If they grow and you shrink
them and then they grow again right after you shrink them,
you just waste resources. Also, I think the maintenance plan
defaults to where you do a dbreindex and then a
shrinkdatabase. In that scenario, you likely just
reintroduce the fragmentation you cleaned up with the
dbreindex.
The following article on Tibor Karaszi's site had some
additional information on shrinking:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Tue, 08 Feb 2005 22:21:42 GMT, "Robert Richards via
SQLMonster.com" <forum@.SQLMonster.com> wrote:
>I have a maintenance plan set up to:
>Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
>Does this effect log files or just data files, or both?|||I have applied "autoshrink" to many of my databases specifically= to try and keep the size of the logs down, and it seems to help.= I had much trouble with logs growinbg larger than the database,= but this has improved with autoshrink and nightly log backups.
> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink =3D 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.sqlmonster.com
User submitted from AEWNET (http://www.aewnet.com/)|||http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"engel1" <engel1@.aew_nospam.com> wrote in message news:OE6hmHxEFHA.4004@.tk2msftngp13.phx.gbl...
I have applied "autoshrink" to many of my databases specifically to try and keep the size of the
logs down, and it seems to help. I had much trouble with logs growinbg larger than the database, but
this has improved with autoshrink and nightly log backups.
> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.sqlmonster.com
User submitted from AEWNET (http://www.aewnet.com/)

Database log files

Does anyone know of anyway to read the transaction logs? The reason why I am
curios is, the transaction logs are way to large for the amount of
transactions we are running(yes, the transaction logs are set to
automatically grow). Our application would not send that amount of
transactions to the database. The other thing I am thinking is that there is
another db within this sqlserver that I think access this DB and I think the
transaction logs would prove this.
Thanks for your assistance in advance....
I've listed three log reader tools on my links page:
http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJ" <TJ@.discussions.microsoft.com> wrote in message
news:92CF3A8E-4DA0-4E1B-BCA8-EC4491DCAFED@.microsoft.com...
> Does anyone know of anyway to read the transaction logs? The reason why I am
> curios is, the transaction logs are way to large for the amount of
> transactions we are running(yes, the transaction logs are set to
> automatically grow). Our application would not send that amount of
> transactions to the database. The other thing I am thinking is that there is
> another db within this sqlserver that I think access this DB and I think the
> transaction logs would prove this.
> Thanks for your assistance in advance....
|||What recovery mode are you in? If it is FULL and you don't issue regular
Log backups it will continue to grow and grow. Change it to simple mode and
you should not see it grow any more. If you really want to see what is
happening on the server you should use Profiler or Trace.
Andrew J. Kelly SQL MVP
"TJ" <TJ@.discussions.microsoft.com> wrote in message
news:92CF3A8E-4DA0-4E1B-BCA8-EC4491DCAFED@.microsoft.com...
> Does anyone know of anyway to read the transaction logs? The reason why I
> am
> curios is, the transaction logs are way to large for the amount of
> transactions we are running(yes, the transaction logs are set to
> automatically grow). Our application would not send that amount of
> transactions to the database. The other thing I am thinking is that there
> is
> another db within this sqlserver that I think access this DB and I think
> the
> transaction logs would prove this.
> Thanks for your assistance in advance....
|||TJ
Another option to consider is the Bulk-Logged recovery mode. Bulk-logged
has better performance than Full with large bulk inserts and bcp operations
because the transaction log files should not grow as quickly as a result of
only recording complete transactions. In other words, you sacrifice the
ability to recover row by row that might occur with large batches.
Regards,
Keith
This posting is provided "as is" without inferring rights or warranties.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eOihJpswFHA.2076@.TK2MSFTNGP14.phx.gbl...
> What recovery mode are you in? If it is FULL and you don't issue regular
> Log backups it will continue to grow and grow. Change it to simple mode
> and you should not see it grow any more. If you really want to see what
> is happening on the server you should use Profiler or Trace.
> --
> Andrew J. Kelly SQL MVP
>
> "TJ" <TJ@.discussions.microsoft.com> wrote in message
> news:92CF3A8E-4DA0-4E1B-BCA8-EC4491DCAFED@.microsoft.com...
>