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
Showing posts with label sp3. Show all posts
Showing posts with label sp3. Show all posts
Sunday, March 11, 2012
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 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.
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.
Thursday, March 8, 2012
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
>
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
>
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 Issue with non-default instances
I have a SQL Server (2000 sp3) with one default instance and many named
instances. I have configured, tested, and monitored a database maintenance
plan for the default instance - works fine. I can't seem to get any of the
default instances to perform the maintenance plan. I have examined the logs,
but I don't see any any relevant entries (I just tried to kick off the job
manaully by executing the associated SQL job). I do see one message in the
log across all of the named instances I tried to run the maintenance plan
against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
stored procedure 'xp_qv'". I have googled this message, but I came up empty.
I should also note that the maintenance plan isn't even writing the history
report.
The only difference I'm aware of is that I performed a 'minimum install' on
the named instances. My transaction logs are growing and growing. The only
way I know to keep the size of these in check is to back them up. Please help
if you can.
Thank you,
- Johnny
Hi
It is not clear how you are running this maintenance plan, and if you
created a different plan in the other instances (which if you did will
probably have a different Ids).
You may want to use create a job that calls xp_slqmaint and specifies the
database and options separately rather than in a plan.
John
"JohnnyMagz" wrote:
> I have a SQL Server (2000 sp3) with one default instance and many named
> instances. I have configured, tested, and monitored a database maintenance
> plan for the default instance - works fine. I can't seem to get any of the
> default instances to perform the maintenance plan. I have examined the logs,
> but I don't see any any relevant entries (I just tried to kick off the job
> manaully by executing the associated SQL job). I do see one message in the
> log across all of the named instances I tried to run the maintenance plan
> against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
> stored procedure 'xp_qv'". I have googled this message, but I came up empty.
> I should also note that the maintenance plan isn't even writing the history
> report.
> The only difference I'm aware of is that I performed a 'minimum install' on
> the named instances. My transaction logs are growing and growing. The only
> way I know to keep the size of these in check is to back them up. Please help
> if you can.
> Thank you,
> - Johnny
instances. I have configured, tested, and monitored a database maintenance
plan for the default instance - works fine. I can't seem to get any of the
default instances to perform the maintenance plan. I have examined the logs,
but I don't see any any relevant entries (I just tried to kick off the job
manaully by executing the associated SQL job). I do see one message in the
log across all of the named instances I tried to run the maintenance plan
against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
stored procedure 'xp_qv'". I have googled this message, but I came up empty.
I should also note that the maintenance plan isn't even writing the history
report.
The only difference I'm aware of is that I performed a 'minimum install' on
the named instances. My transaction logs are growing and growing. The only
way I know to keep the size of these in check is to back them up. Please help
if you can.
Thank you,
- Johnny
Hi
It is not clear how you are running this maintenance plan, and if you
created a different plan in the other instances (which if you did will
probably have a different Ids).
You may want to use create a job that calls xp_slqmaint and specifies the
database and options separately rather than in a plan.
John
"JohnnyMagz" wrote:
> I have a SQL Server (2000 sp3) with one default instance and many named
> instances. I have configured, tested, and monitored a database maintenance
> plan for the default instance - works fine. I can't seem to get any of the
> default instances to perform the maintenance plan. I have examined the logs,
> but I don't see any any relevant entries (I just tried to kick off the job
> manaully by executing the associated SQL job). I do see one message in the
> log across all of the named instances I tried to run the maintenance plan
> against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
> stored procedure 'xp_qv'". I have googled this message, but I came up empty.
> I should also note that the maintenance plan isn't even writing the history
> report.
> The only difference I'm aware of is that I performed a 'minimum install' on
> the named instances. My transaction logs are growing and growing. The only
> way I know to keep the size of these in check is to back them up. Please help
> if you can.
> Thank you,
> - Johnny
Database Maintenance Plan Issue with non-default instances
I have a SQL Server (2000 sp3) with one default instance and many named
instances. I have configured, tested, and monitored a database maintenance
plan for the default instance - works fine. I can't seem to get any of the
default instances to perform the maintenance plan. I have examined the logs,
but I don't see any any relevant entries (I just tried to kick off the job
manaully by executing the associated SQL job). I do see one message in the
log across all of the named instances I tried to run the maintenance plan
against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
stored procedure 'xp_qv'". I have googled this message, but I came up empty.
I should also note that the maintenance plan isn't even writing the history
report.
The only difference I'm aware of is that I performed a 'minimum install' on
the named instances. My transaction logs are growing and growing. The only
way I know to keep the size of these in check is to back them up. Please help
if you can.
Thank you,
- JohnnyHi
It is not clear how you are running this maintenance plan, and if you
created a different plan in the other instances (which if you did will
probably have a different Ids).
You may want to use create a job that calls xp_slqmaint and specifies the
database and options separately rather than in a plan.
John
"JohnnyMagz" wrote:
> I have a SQL Server (2000 sp3) with one default instance and many named
> instances. I have configured, tested, and monitored a database maintenance
> plan for the default instance - works fine. I can't seem to get any of the
> default instances to perform the maintenance plan. I have examined the logs,
> but I don't see any any relevant entries (I just tried to kick off the job
> manaully by executing the associated SQL job). I do see one message in the
> log across all of the named instances I tried to run the maintenance plan
> against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
> stored procedure 'xp_qv'". I have googled this message, but I came up empty.
> I should also note that the maintenance plan isn't even writing the history
> report.
> The only difference I'm aware of is that I performed a 'minimum install' on
> the named instances. My transaction logs are growing and growing. The only
> way I know to keep the size of these in check is to back them up. Please help
> if you can.
> Thank you,
> - Johnny
instances. I have configured, tested, and monitored a database maintenance
plan for the default instance - works fine. I can't seem to get any of the
default instances to perform the maintenance plan. I have examined the logs,
but I don't see any any relevant entries (I just tried to kick off the job
manaully by executing the associated SQL job). I do see one message in the
log across all of the named instances I tried to run the maintenance plan
against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
stored procedure 'xp_qv'". I have googled this message, but I came up empty.
I should also note that the maintenance plan isn't even writing the history
report.
The only difference I'm aware of is that I performed a 'minimum install' on
the named instances. My transaction logs are growing and growing. The only
way I know to keep the size of these in check is to back them up. Please help
if you can.
Thank you,
- JohnnyHi
It is not clear how you are running this maintenance plan, and if you
created a different plan in the other instances (which if you did will
probably have a different Ids).
You may want to use create a job that calls xp_slqmaint and specifies the
database and options separately rather than in a plan.
John
"JohnnyMagz" wrote:
> I have a SQL Server (2000 sp3) with one default instance and many named
> instances. I have configured, tested, and monitored a database maintenance
> plan for the default instance - works fine. I can't seem to get any of the
> default instances to perform the maintenance plan. I have examined the logs,
> but I don't see any any relevant entries (I just tried to kick off the job
> manaully by executing the associated SQL job). I do see one message in the
> log across all of the named instances I tried to run the maintenance plan
> against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
> stored procedure 'xp_qv'". I have googled this message, but I came up empty.
> I should also note that the maintenance plan isn't even writing the history
> report.
> The only difference I'm aware of is that I performed a 'minimum install' on
> the named instances. My transaction logs are growing and growing. The only
> way I know to keep the size of these in check is to back them up. Please help
> if you can.
> Thank you,
> - Johnny
Database Maintenance Plan Issue with non-default instances
I have a SQL Server (2000 sp3) with one default instance and many named
instances. I have configured, tested, and monitored a database maintenance
plan for the default instance - works fine. I can't seem to get any of the
default instances to perform the maintenance plan. I have examined the logs,
but I don't see any any relevant entries (I just tried to kick off the job
manaully by executing the associated SQL job). I do see one message in the
log across all of the named instances I tried to run the maintenance plan
against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
stored procedure 'xp_qv'". I have googled this message, but I came up empty.
I should also note that the maintenance plan isn't even writing the history
report.
The only difference I'm aware of is that I performed a 'minimum install' on
the named instances. My transaction logs are growing and growing. The only
way I know to keep the size of these in check is to back them up. Please hel
p
if you can.
Thank you,
- JohnnyHi
It is not clear how you are running this maintenance plan, and if you
created a different plan in the other instances (which if you did will
probably have a different Ids).
You may want to use create a job that calls xp_slqmaint and specifies the
database and options separately rather than in a plan.
John
"JohnnyMagz" wrote:
> I have a SQL Server (2000 sp3) with one default instance and many named
> instances. I have configured, tested, and monitored a database maintenance
> plan for the default instance - works fine. I can't seem to get any of the
> default instances to perform the maintenance plan. I have examined the log
s,
> but I don't see any any relevant entries (I just tried to kick off the jo
b
> manaully by executing the associated SQL job). I do see one message in the
> log across all of the named instances I tried to run the maintenance plan
> against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
> stored procedure 'xp_qv'". I have googled this message, but I came up empt
y.
> I should also note that the maintenance plan isn't even writing the histor
y
> report.
> The only difference I'm aware of is that I performed a 'minimum install' o
n
> the named instances. My transaction logs are growing and growing. The only
> way I know to keep the size of these in check is to back them up. Please h
elp
> if you can.
> Thank you,
> - Johnny
instances. I have configured, tested, and monitored a database maintenance
plan for the default instance - works fine. I can't seem to get any of the
default instances to perform the maintenance plan. I have examined the logs,
but I don't see any any relevant entries (I just tried to kick off the job
manaully by executing the associated SQL job). I do see one message in the
log across all of the named instances I tried to run the maintenance plan
against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
stored procedure 'xp_qv'". I have googled this message, but I came up empty.
I should also note that the maintenance plan isn't even writing the history
report.
The only difference I'm aware of is that I performed a 'minimum install' on
the named instances. My transaction logs are growing and growing. The only
way I know to keep the size of these in check is to back them up. Please hel
p
if you can.
Thank you,
- JohnnyHi
It is not clear how you are running this maintenance plan, and if you
created a different plan in the other instances (which if you did will
probably have a different Ids).
You may want to use create a job that calls xp_slqmaint and specifies the
database and options separately rather than in a plan.
John
"JohnnyMagz" wrote:
> I have a SQL Server (2000 sp3) with one default instance and many named
> instances. I have configured, tested, and monitored a database maintenance
> plan for the default instance - works fine. I can't seem to get any of the
> default instances to perform the maintenance plan. I have examined the log
s,
> but I don't see any any relevant entries (I just tried to kick off the jo
b
> manaully by executing the associated SQL job). I do see one message in the
> log across all of the named instances I tried to run the maintenance plan
> against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
> stored procedure 'xp_qv'". I have googled this message, but I came up empt
y.
> I should also note that the maintenance plan isn't even writing the histor
y
> report.
> The only difference I'm aware of is that I performed a 'minimum install' o
n
> the named instances. My transaction logs are growing and growing. The only
> way I know to keep the size of these in check is to back them up. Please h
elp
> if you can.
> Thank you,
> - Johnny
Database maintenance plan error - database backed up twice
Are there any known issues with SQL Server 2000 SP3 backing up a database
twice when utilizing the "Database Maintenance Plan?
Thank you.
I have not heard of any. Are you sure you don't have multiple jobs?
Andrew J. Kelly SQL MVP
"Mike Fieldman" <Mike Fieldman@.discussions.microsoft.com> wrote in message
news:E9B29DD2-A915-46B4-A65E-4D5E5345F79B@.microsoft.com...
> Are there any known issues with SQL Server 2000 SP3 backing up a database
> twice when utilizing the "Database Maintenance Plan?
> Thank you.
twice when utilizing the "Database Maintenance Plan?
Thank you.
I have not heard of any. Are you sure you don't have multiple jobs?
Andrew J. Kelly SQL MVP
"Mike Fieldman" <Mike Fieldman@.discussions.microsoft.com> wrote in message
news:E9B29DD2-A915-46B4-A65E-4D5E5345F79B@.microsoft.com...
> Are there any known issues with SQL Server 2000 SP3 backing up a database
> twice when utilizing the "Database Maintenance Plan?
> Thank you.
Database maintenance plan error - database backed up twice
Are there any known issues with SQL Server 2000 SP3 backing up a database
twice when utilizing the "Database Maintenance Plan?
Thank you.I have not heard of any. Are you sure you don't have multiple jobs?
Andrew J. Kelly SQL MVP
"Mike Fieldman" <Mike Fieldman@.discussions.microsoft.com> wrote in message
news:E9B29DD2-A915-46B4-A65E-4D5E5345F79B@.microsoft.com...
> Are there any known issues with SQL Server 2000 SP3 backing up a database
> twice when utilizing the "Database Maintenance Plan?
> Thank you.
twice when utilizing the "Database Maintenance Plan?
Thank you.I have not heard of any. Are you sure you don't have multiple jobs?
Andrew J. Kelly SQL MVP
"Mike Fieldman" <Mike Fieldman@.discussions.microsoft.com> wrote in message
news:E9B29DD2-A915-46B4-A65E-4D5E5345F79B@.microsoft.com...
> Are there any known issues with SQL Server 2000 SP3 backing up a database
> twice when utilizing the "Database Maintenance Plan?
> Thank you.
Database maintenance plan error - database backed up twice
Are there any known issues with SQL Server 2000 SP3 backing up a database
twice when utilizing the "Database Maintenance Plan?
Thank you.I have not heard of any. Are you sure you don't have multiple jobs?
--
Andrew J. Kelly SQL MVP
"Mike Fieldman" <Mike Fieldman@.discussions.microsoft.com> wrote in message
news:E9B29DD2-A915-46B4-A65E-4D5E5345F79B@.microsoft.com...
> Are there any known issues with SQL Server 2000 SP3 backing up a database
> twice when utilizing the "Database Maintenance Plan?
> Thank you.
twice when utilizing the "Database Maintenance Plan?
Thank you.I have not heard of any. Are you sure you don't have multiple jobs?
--
Andrew J. Kelly SQL MVP
"Mike Fieldman" <Mike Fieldman@.discussions.microsoft.com> wrote in message
news:E9B29DD2-A915-46B4-A65E-4D5E5345F79B@.microsoft.com...
> Are there any known issues with SQL Server 2000 SP3 backing up a database
> twice when utilizing the "Database Maintenance Plan?
> Thank you.
Friday, February 17, 2012
Database Log file doesn't shrink ?
Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon
i'm trying to shrink some of the log files for my databases
One has free space of about 200mb for the log file
I've tried shrinking it in enterprise manager but this doesn't help
It still has 200mb free space
What do i need to do
I was hoping to shrink them all b4 going to SP3
thanksRefer to the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
<jpond@.railcu.org.au> wrote:
>Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
>i'm trying to shrink some of the log files for my databases.
>One has free space of about 200mb for the log file.
>I've tried shrinking it in enterprise manager but this doesn't help.
>It still has 200mb free space.
>What do i need to do ?
>I was hoping to shrink them all b4 going to SP3.
>thanks|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager)
or (if you do need the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Thanks
Hari
MCDBA
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0pfqb05p4dq7pcgd913pc40o6hc638hb3u@.4ax.com...
> Refer to the following article:
> INF: Shrinking the Transaction Log in SQL Server 2000 with
> DBCC SHRINKFILE
> http://support.microsoft.com/?id=272318
> -Sue
> On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
> <jpond@.railcu.org.au> wrote:
> >Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
> >i'm trying to shrink some of the log files for my databases.
> >One has free space of about 200mb for the log file.
> >I've tried shrinking it in enterprise manager but this doesn't help.
> >It still has 200mb free space.
> >
> >What do i need to do ?
> >I was hoping to shrink them all b4 going to SP3.
> >
> >thanks
>
i'm trying to shrink some of the log files for my databases
One has free space of about 200mb for the log file
I've tried shrinking it in enterprise manager but this doesn't help
It still has 200mb free space
What do i need to do
I was hoping to shrink them all b4 going to SP3
thanksRefer to the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
<jpond@.railcu.org.au> wrote:
>Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
>i'm trying to shrink some of the log files for my databases.
>One has free space of about 200mb for the log file.
>I've tried shrinking it in enterprise manager but this doesn't help.
>It still has 200mb free space.
>What do i need to do ?
>I was hoping to shrink them all b4 going to SP3.
>thanks|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager)
or (if you do need the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Thanks
Hari
MCDBA
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0pfqb05p4dq7pcgd913pc40o6hc638hb3u@.4ax.com...
> Refer to the following article:
> INF: Shrinking the Transaction Log in SQL Server 2000 with
> DBCC SHRINKFILE
> http://support.microsoft.com/?id=272318
> -Sue
> On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
> <jpond@.railcu.org.au> wrote:
> >Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
> >i'm trying to shrink some of the log files for my databases.
> >One has free space of about 200mb for the log file.
> >I've tried shrinking it in enterprise manager but this doesn't help.
> >It still has 200mb free space.
> >
> >What do i need to do ?
> >I was hoping to shrink them all b4 going to SP3.
> >
> >thanks
>
Database Log file doesn't shrink ?
Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
i'm trying to shrink some of the log files for my databases.
One has free space of about 200mb for the log file.
I've tried shrinking it in enterprise manager but this doesn't help.
It still has 200mb free space.
What do i need to do ?
I was hoping to shrink them all b4 going to SP3.
thanksRefer to the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
<jpond@.railcu.org.au> wrote:
>Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
>i'm trying to shrink some of the log files for my databases.
>One has free space of about 200mb for the log file.
>I've tried shrinking it in enterprise manager but this doesn't help.
>It still has 200mb free space.
>What do i need to do ?
>I was hoping to shrink them all b4 going to SP3.
>thanks|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager)
or (if you do need the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Thanks
Hari
MCDBA
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0pfqb05p4dq7pcgd913pc40o6hc638hb3u@.
4ax.com...
> Refer to the following article:
> INF: Shrinking the Transaction Log in SQL Server 2000 with
> DBCC SHRINKFILE
> http://support.microsoft.com/?id=272318
> -Sue
> On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
> <jpond@.railcu.org.au> wrote:
>
>
i'm trying to shrink some of the log files for my databases.
One has free space of about 200mb for the log file.
I've tried shrinking it in enterprise manager but this doesn't help.
It still has 200mb free space.
What do i need to do ?
I was hoping to shrink them all b4 going to SP3.
thanksRefer to the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
<jpond@.railcu.org.au> wrote:
>Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
>i'm trying to shrink some of the log files for my databases.
>One has free space of about 200mb for the log file.
>I've tried shrinking it in enterprise manager but this doesn't help.
>It still has 200mb free space.
>What do i need to do ?
>I was hoping to shrink them all b4 going to SP3.
>thanks|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager)
or (if you do need the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Thanks
Hari
MCDBA
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0pfqb05p4dq7pcgd913pc40o6hc638hb3u@.
4ax.com...
> Refer to the following article:
> INF: Shrinking the Transaction Log in SQL Server 2000 with
> DBCC SHRINKFILE
> http://support.microsoft.com/?id=272318
> -Sue
> On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
> <jpond@.railcu.org.au> wrote:
>
>
Database Log file doesn't shrink ?
Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
i'm trying to shrink some of the log files for my databases.
One has free space of about 200mb for the log file.
I've tried shrinking it in enterprise manager but this doesn't help.
It still has 200mb free space.
What do i need to do ?
I was hoping to shrink them all b4 going to SP3.
thanks
Refer to the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
<jpond@.railcu.org.au> wrote:
>Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
>i'm trying to shrink some of the log files for my databases.
>One has free space of about 200mb for the log file.
>I've tried shrinking it in enterprise manager but this doesn't help.
>It still has 200mb free space.
>What do i need to do ?
>I was hoping to shrink them all b4 going to SP3.
>thanks
|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager)
or (if you do need the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Thanks
Hari
MCDBA
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0pfqb05p4dq7pcgd913pc40o6hc638hb3u@.4ax.com...
> Refer to the following article:
> INF: Shrinking the Transaction Log in SQL Server 2000 with
> DBCC SHRINKFILE
> http://support.microsoft.com/?id=272318
> -Sue
> On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
> <jpond@.railcu.org.au> wrote:
>
i'm trying to shrink some of the log files for my databases.
One has free space of about 200mb for the log file.
I've tried shrinking it in enterprise manager but this doesn't help.
It still has 200mb free space.
What do i need to do ?
I was hoping to shrink them all b4 going to SP3.
thanks
Refer to the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
<jpond@.railcu.org.au> wrote:
>Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
>i'm trying to shrink some of the log files for my databases.
>One has free space of about 200mb for the log file.
>I've tried shrinking it in enterprise manager but this doesn't help.
>It still has 200mb free space.
>What do i need to do ?
>I was hoping to shrink them all b4 going to SP3.
>thanks
|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager)
or (if you do need the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Thanks
Hari
MCDBA
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0pfqb05p4dq7pcgd913pc40o6hc638hb3u@.4ax.com...
> Refer to the following article:
> INF: Shrinking the Transaction Log in SQL Server 2000 with
> DBCC SHRINKFILE
> http://support.microsoft.com/?id=272318
> -Sue
> On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
> <jpond@.railcu.org.au> wrote:
>
Database lock makes all Clients hold
We are using SQL2000 with SP3, Windows 2000 Svr Std with SP4
Sometimes, we found all clients had no response. Later, we found that the
all clients are waiting response from sql server.
Then in querry analyzer, we use this script:
select * FROM master..sysprocesses where status='sleeping' AND
waittype=0x000 AND open_tran > 0
and then we found out one computer is locking and after request that
computer logout the program, then, all clients can run again.
However, I have no idea why this case happen and I am still searching what's
the meaning the about select statement.
Can anyone help me?
IvanTalk to the person who wrote that program, as you have a bug in the application because it doesn't
and the transaction properly.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Ivan Kan" <ivankan@.no-ip.com> wrote in message news:uJg1Qb3lDHA.1740@.TK2MSFTNGP12.phx.gbl...
> We are using SQL2000 with SP3, Windows 2000 Svr Std with SP4
> Sometimes, we found all clients had no response. Later, we found that the
> all clients are waiting response from sql server.
> Then in querry analyzer, we use this script:
> select * FROM master..sysprocesses where status='sleeping' AND
> waittype=0x000 AND open_tran > 0
> and then we found out one computer is locking and after request that
> computer logout the program, then, all clients can run again.
> However, I have no idea why this case happen and I am still searching what's
> the meaning the about select statement.
> Can anyone help me?
> Ivan
>
>
Sometimes, we found all clients had no response. Later, we found that the
all clients are waiting response from sql server.
Then in querry analyzer, we use this script:
select * FROM master..sysprocesses where status='sleeping' AND
waittype=0x000 AND open_tran > 0
and then we found out one computer is locking and after request that
computer logout the program, then, all clients can run again.
However, I have no idea why this case happen and I am still searching what's
the meaning the about select statement.
Can anyone help me?
IvanTalk to the person who wrote that program, as you have a bug in the application because it doesn't
and the transaction properly.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Ivan Kan" <ivankan@.no-ip.com> wrote in message news:uJg1Qb3lDHA.1740@.TK2MSFTNGP12.phx.gbl...
> We are using SQL2000 with SP3, Windows 2000 Svr Std with SP4
> Sometimes, we found all clients had no response. Later, we found that the
> all clients are waiting response from sql server.
> Then in querry analyzer, we use this script:
> select * FROM master..sysprocesses where status='sleeping' AND
> waittype=0x000 AND open_tran > 0
> and then we found out one computer is locking and after request that
> computer logout the program, then, all clients can run again.
> However, I have no idea why this case happen and I am still searching what's
> the meaning the about select statement.
> Can anyone help me?
> Ivan
>
>
Tuesday, February 14, 2012
'Database Loading' state after Restore
I have a SQL 2000 Ent. SP3 on W2k3 cluster.
Every time when I restore a db from EM ( Use : leave database operational ...) it starts restoring, I se NO progression on the progressbar, and then it says: Successfully restored ...
And the db is gray and says Loading. I cant acess it in any way. No errors in any logs.
The db I now have problem with restores ok on another system.
I havent had this problem with small db's. The one I'm trying now is 9GB.try RESTORE DATABASE ... WITH RECOVERY option
"Bjørn" <anonymous@.discussions.microsoft.com> wrote in message
news:6DDC2092-7FCC-4C57-9759-812AAA5CEB84@.microsoft.com...
> I have a SQL 2000 Ent. SP3 on W2k3 cluster.
> Every time when I restore a db from EM ( Use : leave database operational
...) it starts restoring, I se NO progression on the progressbar, and then
it says: Successfully restored ...
> And the db is gray and says Loading. I cant acess it in any way. No errors
in any logs.
> The db I now have problem with restores ok on another system.
> I havent had this problem with small db's. The one I'm trying now is 9GB.
>
>|||Says :
File 'db_data' was only partially restored by a database or file restore. The entire file must be successfully restored before applying the log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
And trying to get the properties of the db says:
Error 927. Database '...' cannot be opened. It is in the middle of a restore.
Every time when I restore a db from EM ( Use : leave database operational ...) it starts restoring, I se NO progression on the progressbar, and then it says: Successfully restored ...
And the db is gray and says Loading. I cant acess it in any way. No errors in any logs.
The db I now have problem with restores ok on another system.
I havent had this problem with small db's. The one I'm trying now is 9GB.try RESTORE DATABASE ... WITH RECOVERY option
"Bjørn" <anonymous@.discussions.microsoft.com> wrote in message
news:6DDC2092-7FCC-4C57-9759-812AAA5CEB84@.microsoft.com...
> I have a SQL 2000 Ent. SP3 on W2k3 cluster.
> Every time when I restore a db from EM ( Use : leave database operational
...) it starts restoring, I se NO progression on the progressbar, and then
it says: Successfully restored ...
> And the db is gray and says Loading. I cant acess it in any way. No errors
in any logs.
> The db I now have problem with restores ok on another system.
> I havent had this problem with small db's. The one I'm trying now is 9GB.
>
>|||Says :
File 'db_data' was only partially restored by a database or file restore. The entire file must be successfully restored before applying the log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
And trying to get the properties of the db says:
Error 927. Database '...' cannot be opened. It is in the middle of a restore.
'Database Loading' state after Restore
I have a SQL 2000 Ent. SP3 on W2k3 cluster.
Every time when I restore a db from EM ( Use : leave database operational ..
.) it starts restoring, I se NO progression on the progressbar, and then it
says: Successfully restored ...
And the db is gray and says Loading. I cant acess it in any way. No errors i
n any logs.
The db I now have problem with restores ok on another system.
I havent had this problem with small db's. The one I'm trying now is 9GB.try RESTORE DATABASE ... WITH RECOVERY option
"Bjrn" <anonymous@.discussions.microsoft.com> wrote in message
news:6DDC2092-7FCC-4C57-9759-812AAA5CEB84@.microsoft.com...
...) it starts restoring, I se NO progression on the progressbar, and then
it says: Successfully restored ...
in any logs.
File 'db_data' was only partially restored by a database or file restore. Th
e entire file must be successfully restored before applying the log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
And trying to get the properties of the db says:
Error 927. Database '...' cannot be opened. It is in the middle of a restor
e.
Every time when I restore a db from EM ( Use : leave database operational ..
.) it starts restoring, I se NO progression on the progressbar, and then it
says: Successfully restored ...
And the db is gray and says Loading. I cant acess it in any way. No errors i
n any logs.
The db I now have problem with restores ok on another system.
I havent had this problem with small db's. The one I'm trying now is 9GB.try RESTORE DATABASE ... WITH RECOVERY option
"Bjrn" <anonymous@.discussions.microsoft.com> wrote in message
news:6DDC2092-7FCC-4C57-9759-812AAA5CEB84@.microsoft.com...
quote:
> I have a SQL 2000 Ent. SP3 on W2k3 cluster.
> Every time when I restore a db from EM ( Use : leave database operational
...) it starts restoring, I se NO progression on the progressbar, and then
it says: Successfully restored ...
quote:
> And the db is gray and says Loading. I cant acess it in any way. No errors
in any logs.
quote:|||Says :
> The db I now have problem with restores ok on another system.
> I havent had this problem with small db's. The one I'm trying now is 9GB.
>
>
File 'db_data' was only partially restored by a database or file restore. Th
e entire file must be successfully restored before applying the log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
And trying to get the properties of the db says:
Error 927. Database '...' cannot be opened. It is in the middle of a restor
e.
Subscribe to:
Posts (Atom)