Maintenance Job not working properly

Hi,

I an new to the forum.

I am having some problems where my maintenance job is not backing up the user databases in SQL2000. Unfortunately, I found out the hard way as my payroll in GP did not run properly and I had to do a restore from the BAK file.

I am receiving the following error msg in the event log

Status: Failed - Invoked on: 2006-04-11 22:00:02 - Message: The job failed. The owner () of job DB Backup Job for DB Maintenance Plan 'Daily Backup of User Databases' does not have server access.

I cannot find where and how the user permission was chmaged on this job. I would like some assistance with this problem.

thanks

Raymond

Hi,

Go to Jobs list under SQL Server Agent in the Enterprise Manager.

Find the job for the maintenance plan you have created. Name of the job will include the name of the maintenance plan.

If you open the job properties screen and activate the Steps tab. You will see the "run as user" in the advanced tab of the edit job steps screen.

Check this user for necessary credentials.

Also control the owner of the job in the main job properties screen.

Eralper

Maintenance Cleanup task not functioning properly?

Using SQL Server 2005 Management Studio and backing up databases. When I add the task Maintenance Cleanup to remove database backups older then 1 day the task fails?
What part of this TSQL code does SQL Server 2005 Management Studio not understand?

EXECUTE master.dbo.xp_delete_file 0,N'\\pdxsql03\SQL Backup\POSSQL_Server',N'.bak',N'05/15/2006 08:09:37'

The database file backup extension is .bak and the file is older than one day which was specified.
It is just not deleting the file.
We have deleted the maintenance task and recreated it with no effect and this has been happening for over a month.
Any help here would be greatly appreciated.

Carl
carlt@.gicw.org

I've been having the same problem for over three months now and keep checking back here to see if there's been a fix....

|||

http://blogs.msdn.com/sanchan/archive/2006/04/23/581640.aspx

This is addressed in SP1. Please look at the above blog for more information.

Gops Dwarak

|||

The trick is to replace .bak with *.*

It definitely works. :-)

|||

I tried to replace the extension "bak" with "*.*", "*" and ".*" I know this looks like the meeting of the disabled Smilie group, but you know what I mean. It definitely _DOESN′T_ delete any backups. However, when I execute the t-sql created by the maintenance Cleanup Task, it deletes the files.

Any suggestions?

|||Not working for me either. SP1 applied and all. Subdirectories for backups. .. trying to delete archived backups, in the form Filename.bak.7z. No worky worky!
Please advise? (Read: How hard is it to implement a functioning XSP?!)|||

My solution was to take the T-SQL created by the cleanup task and modifying it slightly:

declare @.dt datetime

select @.dt=getdate()-5
EXECUTE master.dbo.xp_delete_file 0,N'E:\Backups',N'BAK',@.dt,1

The variable is used to check the age of the backup-files. In this case, all backups older than 5 days are deleted. The last Number (=1) gives the number of subdirectories to be searched in for .bak-files. AFAIK a maximum of 5 subdirs is supported.

Then you can take it and put it into an "execute SQL"-Task, take the package and schedule it or directly schedule it in the Job Scheduler of Management Studio as SQL-Statement

|||

The workaround above may or may not work, depending on what the problem is.

To get to the bottom of the problem - what is the exact error that you are getting? We can then search if this is a known problem. There were number of issues with Cleanup task. A few were fixed in SP1, like Gops has mentioned, more were fixed in SP2 which is in the CTP release right now. If you can, please install SQL Server 2005 SP2 CTP December from here: http://www.microsoft.com/sql/ctp.mspx and let us know if the problem sitll exists.

Regards,

Maciek Sarnowicz

|||

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

Thanks,

-lance

|||

There is an "off by one" bug using the time unit of measure in "Maintenance" and "Cleanup" tasks.

If you select "Weeks" you get "Days", with "Days" you get "Hours", and "Hours" blows the index.

The patching procedure is a little complicated. Read this:

http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

|||

-lance wrote:

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

You are running SQL Server 2005 Service Pack 2 (9.00.3054.00).

You are running Windows Server 2003 Service Pack 1.

Hope this clears up your confusion.

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

|||

I was also having a similar issue. First the cleanup task would just not delete any files no matter where I pointed it to. I originally had put “.trn” in the file extension box. I then removed the . and everything deleted correctly. The problem we then ran into was that it would only delete from the top level folder that we pointed to. In another blog I read that I must check the Include first-level subfolders box to delete from these sub folders. The problem is that this checkbox is nowhere to be found on the maintenance cleanup task dialog box. We do have service pack 2 loaded and most of the updates but….?

I finally just got the T-SQL statement and created my own job.

This is the original statement:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18'

Is simply added a 1 to the end of the statement and made it:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18',1

This job runs great and deletes all the files on level below the top level folder. I did try adding a 2 to see if it would let me delete further levels but it errors out. Hope this helps somebody!

Maintenance Cleanup task not functioning properly?

Using SQL Server 2005 Management Studio and backing up databases. When I add the task Maintenance Cleanup to remove database backups older then 1 day the task fails?
What part of this TSQL code does SQL Server 2005 Management Studio not understand?

EXECUTE master.dbo.xp_delete_file 0,N'\\pdxsql03\SQL Backup\POSSQL_Server',N'.bak',N'05/15/2006 08:09:37'

The database file backup extension is .bak and the file is older than one day which was specified.
It is just not deleting the file.
We have deleted the maintenance task and recreated it with no effect and this has been happening for over a month.
Any help here would be greatly appreciated.

Carl
carlt@.gicw.org

I've been having the same problem for over three months now and keep checking back here to see if there's been a fix....

|||

http://blogs.msdn.com/sanchan/archive/2006/04/23/581640.aspx

This is addressed in SP1. Please look at the above blog for more information.

Gops Dwarak

|||

The trick is to replace .bak with *.*

It definitely works. :-)

|||

I tried to replace the extension "bak" with "*.*", "*" and ".*" I know this looks like the meeting of the disabled Smilie group, but you know what I mean. It definitely _DOESN′T_ delete any backups. However, when I execute the t-sql created by the maintenance Cleanup Task, it deletes the files.

Any suggestions?

|||Not working for me either. SP1 applied and all. Subdirectories for backups. .. trying to delete archived backups, in the form Filename.bak.7z. No worky worky!
Please advise? (Read: How hard is it to implement a functioning XSP?!)
|||

My solution was to take the T-SQL created by the cleanup task and modifying it slightly:

declare @.dt datetime

select @.dt=getdate()-5
EXECUTE master.dbo.xp_delete_file 0,N'E:\Backups',N'BAK',@.dt,1

The variable is used to check the age of the backup-files. In this case, all backups older than 5 days are deleted. The last Number (=1) gives the number of subdirectories to be searched in for .bak-files. AFAIK a maximum of 5 subdirs is supported.

Then you can take it and put it into an "execute SQL"-Task, take the package and schedule it or directly schedule it in the Job Scheduler of Management Studio as SQL-Statement

|||

The workaround above may or may not work, depending on what the problem is.

To get to the bottom of the problem - what is the exact error that you are getting? We can then search if this is a known problem. There were number of issues with Cleanup task. A few were fixed in SP1, like Gops has mentioned, more were fixed in SP2 which is in the CTP release right now. If you can, please install SQL Server 2005 SP2 CTP December from here: http://www.microsoft.com/sql/ctp.mspx and let us know if the problem sitll exists.

Regards,

Maciek Sarnowicz

|||

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

Thanks,

-lance

|||

There is an "off by one" bug using the time unit of measure in "Maintenance" and "Cleanup" tasks.

If you select "Weeks" you get "Days", with "Days" you get "Hours", and "Hours" blows the index.

The patching procedure is a little complicated. Read this:

http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

|||

-lance wrote:

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

You are running SQL Server 2005 Service Pack 2 (9.00.3054.00).

You are running Windows Server 2003 Service Pack 1.

Hope this clears up your confusion.

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

|||

I was also having a similar issue. First the cleanup task would just not delete any files no matter where I pointed it to. I originally had put “.trn” in the file extension box. I then removed the . and everything deleted correctly. The problem we then ran into was that it would only delete from the top level folder that we pointed to. In another blog I read that I must check the Include first-level subfolders box to delete from these sub folders. The problem is that this checkbox is nowhere to be found on the maintenance cleanup task dialog box. We do have service pack 2 loaded and most of the updates but….?

I finally just got the T-SQL statement and created my own job.

This is the original statement:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18'

Is simply added a 1 to the end of the statement and made it:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18',1

This job runs great and deletes all the files on level below the top level folder. I did try adding a 2 to see if it would let me delete further levels but it errors out. Hope this helps somebody!

Maintenance Cleanup task not functioning properly?

Using SQL Server 2005 Management Studio and backing up databases. When I add the task Maintenance Cleanup to remove database backups older then 1 day the task fails?
What part of this TSQL code does SQL Server 2005 Management Studio not understand?

EXECUTE master.dbo.xp_delete_file 0,N'\\pdxsql03\SQL Backup\POSSQL_Server',N'.bak',N'05/15/2006 08:09:37'

The database file backup extension is .bak and the file is older than one day which was specified.
It is just not deleting the file.
We have deleted the maintenance task and recreated it with no effect and this has been happening for over a month.
Any help here would be greatly appreciated.

Carl
carlt@.gicw.org

I've been having the same problem for over three months now and keep checking back here to see if there's been a fix....

|||

http://blogs.msdn.com/sanchan/archive/2006/04/23/581640.aspx

This is addressed in SP1. Please look at the above blog for more information.

Gops Dwarak

|||

The trick is to replace .bak with *.*

It definitely works. :-)

|||

I tried to replace the extension "bak" with "*.*", "*" and ".*" I know this looks like the meeting of the disabled Smilie group, but you know what I mean. It definitely _DOESN′T_ delete any backups. However, when I execute the t-sql created by the maintenance Cleanup Task, it deletes the files.

Any suggestions?

|||Not working for me either. SP1 applied and all. Subdirectories for backups. .. trying to delete archived backups, in the form Filename.bak.7z. No worky worky!
Please advise? (Read: How hard is it to implement a functioning XSP?!)|||

My solution was to take the T-SQL created by the cleanup task and modifying it slightly:

declare @.dt datetime

select @.dt=getdate()-5
EXECUTE master.dbo.xp_delete_file 0,N'E:\Backups',N'BAK',@.dt,1

The variable is used to check the age of the backup-files. In this case, all backups older than 5 days are deleted. The last Number (=1) gives the number of subdirectories to be searched in for .bak-files. AFAIK a maximum of 5 subdirs is supported.

Then you can take it and put it into an "execute SQL"-Task, take the package and schedule it or directly schedule it in the Job Scheduler of Management Studio as SQL-Statement

|||

The workaround above may or may not work, depending on what the problem is.

To get to the bottom of the problem - what is the exact error that you are getting? We can then search if this is a known problem. There were number of issues with Cleanup task. A few were fixed in SP1, like Gops has mentioned, more were fixed in SP2 which is in the CTP release right now. If you can, please install SQL Server 2005 SP2 CTP December from here: http://www.microsoft.com/sql/ctp.mspx and let us know if the problem sitll exists.

Regards,

Maciek Sarnowicz

|||

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

Thanks,

-lance

|||

There is an "off by one" bug using the time unit of measure in "Maintenance" and "Cleanup" tasks.

If you select "Weeks" you get "Days", with "Days" you get "Hours", and "Hours" blows the index.

The patching procedure is a little complicated. Read this:

http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

|||

-lance wrote:

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

You are running SQL Server 2005 Service Pack 2 (9.00.3054.00).

You are running Windows Server 2003 Service Pack 1.

Hope this clears up your confusion.

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

|||

I was also having a similar issue. First the cleanup task would just not delete any files no matter where I pointed it to. I originally had put “.trn” in the file extension box. I then removed the . and everything deleted correctly. The problem we then ran into was that it would only delete from the top level folder that we pointed to. In another blog I read that I must check the Include first-level subfolders box to delete from these sub folders. The problem is that this checkbox is nowhere to be found on the maintenance cleanup task dialog box. We do have service pack 2 loaded and most of the updates but….?

I finally just got the T-SQL statement and created my own job.

This is the original statement:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18'

Is simply added a 1 to the end of the statement and made it:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18',1

This job runs great and deletes all the files on level below the top level folder. I did try adding a 2 to see if it would let me delete further levels but it errors out. Hope this helps somebody!

Maintenance Cleanup task not functioning properly?

Using SQL Server 2005 Management Studio and backing up databases. When I add the task Maintenance Cleanup to remove database backups older then 1 day the task fails?
What part of this TSQL code does SQL Server 2005 Management Studio not understand?

EXECUTE master.dbo.xp_delete_file 0,N'\\pdxsql03\SQL Backup\POSSQL_Server',N'.bak',N'05/15/2006 08:09:37'

The database file backup extension is .bak and the file is older than one day which was specified.
It is just not deleting the file.
We have deleted the maintenance task and recreated it with no effect and this has been happening for over a month.
Any help here would be greatly appreciated.

Carl
carlt@.gicw.org

I've been having the same problem for over three months now and keep checking back here to see if there's been a fix....

|||

http://blogs.msdn.com/sanchan/archive/2006/04/23/581640.aspx

This is addressed in SP1. Please look at the above blog for more information.

Gops Dwarak

|||

The trick is to replace .bak with *.*

It definitely works. :-)

|||

I tried to replace the extension "bak" with "*.*", "*" and ".*" I know this looks like the meeting of the disabled Smilie group, but you know what I mean. It definitely _DOESN′T_ delete any backups. However, when I execute the t-sql created by the maintenance Cleanup Task, it deletes the files.

Any suggestions?

|||Not working for me either. SP1 applied and all. Subdirectories for backups. .. trying to delete archived backups, in the form Filename.bak.7z. No worky worky!
Please advise? (Read: How hard is it to implement a functioning XSP?!)
|||

My solution was to take the T-SQL created by the cleanup task and modifying it slightly:

declare @.dt datetime

select @.dt=getdate()-5
EXECUTE master.dbo.xp_delete_file 0,N'E:\Backups',N'BAK',@.dt,1

The variable is used to check the age of the backup-files. In this case, all backups older than 5 days are deleted. The last Number (=1) gives the number of subdirectories to be searched in for .bak-files. AFAIK a maximum of 5 subdirs is supported.

Then you can take it and put it into an "execute SQL"-Task, take the package and schedule it or directly schedule it in the Job Scheduler of Management Studio as SQL-Statement

|||

The workaround above may or may not work, depending on what the problem is.

To get to the bottom of the problem - what is the exact error that you are getting? We can then search if this is a known problem. There were number of issues with Cleanup task. A few were fixed in SP1, like Gops has mentioned, more were fixed in SP2 which is in the CTP release right now. If you can, please install SQL Server 2005 SP2 CTP December from here: http://www.microsoft.com/sql/ctp.mspx and let us know if the problem sitll exists.

Regards,

Maciek Sarnowicz

|||

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

Thanks,

-lance

|||

There is an "off by one" bug using the time unit of measure in "Maintenance" and "Cleanup" tasks.

If you select "Weeks" you get "Days", with "Days" you get "Hours", and "Hours" blows the index.

The patching procedure is a little complicated. Read this:

http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

|||

-lance wrote:

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

You are running SQL Server 2005 Service Pack 2 (9.00.3054.00).

You are running Windows Server 2003 Service Pack 1.

Hope this clears up your confusion.

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

|||

I was also having a similar issue. First the cleanup task would just not delete any files no matter where I pointed it to. I originally had put “.trn” in the file extension box. I then removed the . and everything deleted correctly. The problem we then ran into was that it would only delete from the top level folder that we pointed to. In another blog I read that I must check the Include first-level subfolders box to delete from these sub folders. The problem is that this checkbox is nowhere to be found on the maintenance cleanup task dialog box. We do have service pack 2 loaded and most of the updates but….?

I finally just got the T-SQL statement and created my own job.

This is the original statement:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18'

Is simply added a 1 to the end of the statement and made it:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18',1

This job runs great and deletes all the files on level below the top level folder. I did try adding a 2 to see if it would let me delete further levels but it errors out. Hope this helps somebody!

sql

Maintenance Cleanup task not functioning properly?

Using SQL Server 2005 Management Studio and backing up databases. When I add the task Maintenance Cleanup to remove database backups older then 1 day the task fails?
What part of this TSQL code does SQL Server 2005 Management Studio not understand?

EXECUTE master.dbo.xp_delete_file 0,N'\\pdxsql03\SQL Backup\POSSQL_Server',N'.bak',N'05/15/2006 08:09:37'

The database file backup extension is .bak and the file is older than one day which was specified.
It is just not deleting the file.
We have deleted the maintenance task and recreated it with no effect and this has been happening for over a month.
Any help here would be greatly appreciated.

Carl
carlt@.gicw.org

I've been having the same problem for over three months now and keep checking back here to see if there's been a fix....

|||

http://blogs.msdn.com/sanchan/archive/2006/04/23/581640.aspx

This is addressed in SP1. Please look at the above blog for more information.

Gops Dwarak

|||

The trick is to replace .bak with *.*

It definitely works. :-)

|||

I tried to replace the extension "bak" with "*.*", "*" and ".*" I know this looks like the meeting of the disabled Smilie group, but you know what I mean. It definitely _DOESN′T_ delete any backups. However, when I execute the t-sql created by the maintenance Cleanup Task, it deletes the files.

Any suggestions?

|||Not working for me either. SP1 applied and all. Subdirectories for backups. .. trying to delete archived backups, in the form Filename.bak.7z. No worky worky!
Please advise? (Read: How hard is it to implement a functioning XSP?!)|||

My solution was to take the T-SQL created by the cleanup task and modifying it slightly:

declare @.dt datetime

select @.dt=getdate()-5
EXECUTE master.dbo.xp_delete_file 0,N'E:\Backups',N'BAK',@.dt,1

The variable is used to check the age of the backup-files. In this case, all backups older than 5 days are deleted. The last Number (=1) gives the number of subdirectories to be searched in for .bak-files. AFAIK a maximum of 5 subdirs is supported.

Then you can take it and put it into an "execute SQL"-Task, take the package and schedule it or directly schedule it in the Job Scheduler of Management Studio as SQL-Statement

|||

The workaround above may or may not work, depending on what the problem is.

To get to the bottom of the problem - what is the exact error that you are getting? We can then search if this is a known problem. There were number of issues with Cleanup task. A few were fixed in SP1, like Gops has mentioned, more were fixed in SP2 which is in the CTP release right now. If you can, please install SQL Server 2005 SP2 CTP December from here: http://www.microsoft.com/sql/ctp.mspx and let us know if the problem sitll exists.

Regards,

Maciek Sarnowicz

|||

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

Thanks,

-lance

|||

There is an "off by one" bug using the time unit of measure in "Maintenance" and "Cleanup" tasks.

If you select "Weeks" you get "Days", with "Days" you get "Hours", and "Hours" blows the index.

The patching procedure is a little complicated. Read this:

http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

|||

-lance wrote:

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

You are running SQL Server 2005 Service Pack 2 (9.00.3054.00).

You are running Windows Server 2003 Service Pack 1.

Hope this clears up your confusion.

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

|||

I was also having a similar issue. First the cleanup task would just not delete any files no matter where I pointed it to. I originally had put “.trn” in the file extension box. I then removed the . and everything deleted correctly. The problem we then ran into was that it would only delete from the top level folder that we pointed to. In another blog I read that I must check the Include first-level subfolders box to delete from these sub folders. The problem is that this checkbox is nowhere to be found on the maintenance cleanup task dialog box. We do have service pack 2 loaded and most of the updates but….?

I finally just got the T-SQL statement and created my own job.

This is the original statement:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18'

Is simply added a 1 to the end of the statement and made it:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18',1

This job runs great and deletes all the files on level below the top level folder. I did try adding a 2 to see if it would let me delete further levels but it errors out. Hope this helps somebody!

Maintenance Cleanup task not functioning properly?

Using SQL Server 2005 Management Studio and backing up databases. When I add the task Maintenance Cleanup to remove database backups older then 1 day the task fails?
What part of this TSQL code does SQL Server 2005 Management Studio not understand?

EXECUTE master.dbo.xp_delete_file 0,N'\\pdxsql03\SQL Backup\POSSQL_Server',N'.bak',N'05/15/2006 08:09:37'

The database file backup extension is .bak and the file is older than one day which was specified.
It is just not deleting the file.
We have deleted the maintenance task and recreated it with no effect and this has been happening for over a month.
Any help here would be greatly appreciated.

Carl
carlt@.gicw.org

I've been having the same problem for over three months now and keep checking back here to see if there's been a fix....

|||

http://blogs.msdn.com/sanchan/archive/2006/04/23/581640.aspx

This is addressed in SP1. Please look at the above blog for more information.

Gops Dwarak

|||

The trick is to replace .bak with *.*

It definitely works. :-)

|||

I tried to replace the extension "bak" with "*.*", "*" and ".*" I know this looks like the meeting of the disabled Smilie group, but you know what I mean. It definitely _DOESN′T_ delete any backups. However, when I execute the t-sql created by the maintenance Cleanup Task, it deletes the files.

Any suggestions?

|||Not working for me either. SP1 applied and all. Subdirectories for backups. .. trying to delete archived backups, in the form Filename.bak.7z. No worky worky!
Please advise? (Read: How hard is it to implement a functioning XSP?!)
|||

My solution was to take the T-SQL created by the cleanup task and modifying it slightly:

declare @.dt datetime

select @.dt=getdate()-5
EXECUTE master.dbo.xp_delete_file 0,N'E:\Backups',N'BAK',@.dt,1

The variable is used to check the age of the backup-files. In this case, all backups older than 5 days are deleted. The last Number (=1) gives the number of subdirectories to be searched in for .bak-files. AFAIK a maximum of 5 subdirs is supported.

Then you can take it and put it into an "execute SQL"-Task, take the package and schedule it or directly schedule it in the Job Scheduler of Management Studio as SQL-Statement

|||

The workaround above may or may not work, depending on what the problem is.

To get to the bottom of the problem - what is the exact error that you are getting? We can then search if this is a known problem. There were number of issues with Cleanup task. A few were fixed in SP1, like Gops has mentioned, more were fixed in SP2 which is in the CTP release right now. If you can, please install SQL Server 2005 SP2 CTP December from here: http://www.microsoft.com/sql/ctp.mspx and let us know if the problem sitll exists.

Regards,

Maciek Sarnowicz

|||

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

Thanks,

-lance

|||

There is an "off by one" bug using the time unit of measure in "Maintenance" and "Cleanup" tasks.

If you select "Weeks" you get "Days", with "Days" you get "Hours", and "Hours" blows the index.

The patching procedure is a little complicated. Read this:

http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

|||

-lance wrote:

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

You are running SQL Server 2005 Service Pack 2 (9.00.3054.00).

You are running Windows Server 2003 Service Pack 1.

Hope this clears up your confusion.

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

|||

I was also having a similar issue. First the cleanup task would just not delete any files no matter where I pointed it to. I originally had put “.trn” in the file extension box. I then removed the . and everything deleted correctly. The problem we then ran into was that it would only delete from the top level folder that we pointed to. In another blog I read that I must check the Include first-level subfolders box to delete from these sub folders. The problem is that this checkbox is nowhere to be found on the maintenance cleanup task dialog box. We do have service pack 2 loaded and most of the updates but….?

I finally just got the T-SQL statement and created my own job.

This is the original statement:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18'

Is simply added a 1 to the end of the statement and made it:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18',1

This job runs great and deletes all the files on level below the top level folder. I did try adding a 2 to see if it would let me delete further levels but it errors out. Hope this helps somebody!

Mailing labels don't display properly when run from report server

I have some mailing labels that when I run it from my report works fine as
two columns. When I deploy it to the report server and run it from there it
only displays one column. Anyone have any information that could help solve
this?When viewing the report in HTML format e.g. in report manager, you will only
see one column because multiple columns are currently not supported in HTML
format. You will however get them when using PDF export.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"GORAMS" <GORAMS@.discussions.microsoft.com> wrote in message
news:EE7C98FE-8D7A-4BBD-9057-48B68A71140D@.microsoft.com...
>I have some mailing labels that when I run it from my report works fine as
> two columns. When I deploy it to the report server and run it from there
> it
> only displays one column. Anyone have any information that could help
> solve
> this?|||Thanks so much for your reply. I was able to run the report and when I
exported them out to a PDF format, it properly displayed my report.
"Robert Bruckner [MSFT]" wrote:
> When viewing the report in HTML format e.g. in report manager, you will only
> see one column because multiple columns are currently not supported in HTML
> format. You will however get them when using PDF export.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "GORAMS" <GORAMS@.discussions.microsoft.com> wrote in message
> news:EE7C98FE-8D7A-4BBD-9057-48B68A71140D@.microsoft.com...
> >I have some mailing labels that when I run it from my report works fine as
> > two columns. When I deploy it to the report server and run it from there
> > it
> > only displays one column. Anyone have any information that could help
> > solve
> > this?
>
>|||Hi,
Just want to know how did you use label priting in MC Repots. because as far
as my knowle'ge i can have only one row at a time.
I am unable to print label with three column.
Any help?
Labhesh Shrimali
Bangalore
"GORAMS" wrote:
> Thanks so much for your reply. I was able to run the report and when I
> exported them out to a PDF format, it properly displayed my report.
> "Robert Bruckner [MSFT]" wrote:
> > When viewing the report in HTML format e.g. in report manager, you will only
> > see one column because multiple columns are currently not supported in HTML
> > format. You will however get them when using PDF export.
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "GORAMS" <GORAMS@.discussions.microsoft.com> wrote in message
> > news:EE7C98FE-8D7A-4BBD-9057-48B68A71140D@.microsoft.com...
> > >I have some mailing labels that when I run it from my report works fine as
> > > two columns. When I deploy it to the report server and run it from there
> > > it
> > > only displays one column. Anyone have any information that could help
> > > solve
> > > this?
> >
> >
> >|||Make the height of the body of the report small enough so that several
rows will print on one page - also, set up the body of the report to
have three columns.
You need to carefully control the size of the page, the body of the
report, and any page header and footer.|||This is a great feature but can reporting services print records left to
right rather than top to bottom. For Example, I want to print the records in
order like this
1 2
3 4
rather than
1 3
2 4
Thank you for your time.
Dan
"Parker" wrote:
> Make the height of the body of the report small enough so that several
> rows will print on one page - also, set up the body of the report to
> have three columns.
> You need to carefully control the size of the page, the body of the
> report, and any page header and footer.
>|||Dan -
If you read this, the only way I can think of is to do the work in a
stored procedure to combine two (or more) records into one - and then
print the resulting record on a single row.
I had to do something like this when I needed to put 4 records on a
page (one in each corner) and duplex print them.
Dan Storch wrote:
> This is a great feature but can reporting services print records left to
> right rather than top to bottom. For Example, I want to print the records in
> order like this
> 1 2
> 3 4
> rather than
> 1 3
> 2 4
> Thank you for your time.
> Dan|||HI Dan,
Were you ever able to figure out how to get the labels to print from left to
right rather than down the page?
Any help would be greatly appreciated!
Thanks!
"Dan Storch" wrote:
> This is a great feature but can reporting services print records left to
> right rather than top to bottom. For Example, I want to print the records in
> order like this
> 1 2
> 3 4
> rather than
> 1 3
> 2 4
> Thank you for your time.
> Dan
> "Parker" wrote:
> > Make the height of the body of the report small enough so that several
> > rows will print on one page - also, set up the body of the report to
> > have three columns.
> >
> > You need to carefully control the size of the page, the body of the
> > report, and any page header and footer.
> >
> >