May 10, 2010 at 2:59 am
Hello
Since moving to 64 bit SQL 2005, the maintenance tasks have been "failing". They don't actually think they've failed so no errors are shown, but they just don't do anything. Having spent ages looking into this (permissions etc), it seems it's down to using a UNC path (e.g. \\myserver\dbbackups\). Under the old server which is still up and running, the maintenance task will remove files with no problems, but the same task on the 64 bit server, does nothing.
I'm loathe to delete these files outside of SQL as it's related to the whole backup procedures but obviously it does cause problems because old backups and transaction logs are not being deleted. Whilst my old server is available, I've renacted the maintenance task from that, but it'll be going any day soon...
Is there a fix? We are on 9.0.4053 which I believe is SP3 which is the same version as my old server.
Thanks in advance
May 10, 2010 at 2:37 pm
Worked ok when I just tried it: 9.0.4053
Maint plan wrote a backup to a UNC path, then deleted from the same UNC path.
So if everything else is exactly the same, and you change only the path, it doesn't work ?
Sure you don't accidentaly have a period in the extension box ? Should be "trn", not ".trn"
May 11, 2010 at 1:27 am
Hi
I checked all the obvious things such as a . before the BAK and TRN, permissions to the remote server, space on remote server, changing BAK to bak, adding a \ at the end of the path, etc but it only really became a possible issue related to the move to 64 BIT SQL 2005, because when I ran the job from the old server, it deleted the files OK. The TSQL for both jobs is shown below but not sure if that shows any differences. I'm probably missing something really obvious so apologies in advance!!
I created a job on the old server with 2 tasks, 1 for deleting backups more than 5 days old and 1 for deleting logs more than 3 days old:
EXECUTE master.dbo.xp_delete_file 0,N'\\cerium\dbdumps',N'BAK',N'2010-05-06T08:11:01',1
EXECUTE master.dbo.xp_delete_file 0,N'\\cerium\dbdumps',N'TRN',N'2010-05-08T08:14:12',1
These delete the files from the location OK.
The new server has a daily backup and maintenance plan and a separate transaction log backup (hourly) and trans log delete plan. The plans are split rather than being incorporated into 1 plan and there are other tasks such as the backup database within them. However the maintenance tasks for both deleting the backups and trans logs are the same, supposedly...
EXECUTE master.dbo.xp_delete_file 0,N'\\cerium\dbdumps',N'bak',N'2010-05-06T08:16:08',1
EXECUTE master.dbo.xp_delete_file 0,N'\\cerium\dbdumps\',N'TRN',N'2010-05-08T08:22:30',1
You can see the variances in the case and the extra \ in the new ones, but even when I tried it without, the ones above do not delete the files. Interestingly, when I tried the TSQL in query analyser, it just executed the command but didn't remove the files.
I guess my only other test would be to create just the one test plan as I did on the old server, with the 2 tasks and nothing else, and see how that fairs.
I'll let you know.
Thanks a lot
May 11, 2010 at 2:38 am
Foiled again I'm afraid. The TSQL for the new plan is:-
EXECUTE master.dbo.xp_delete_file 0,N'\\cerium\dbdumps',N'BAK',N'2010-05-06T09:10:58',1
EXECUTE master.dbo.xp_delete_file 0,N'\\cerium\dbdumps',N'TRN',N'2010-05-10T09:11:24',1
It didn't work
The old server plan is as below:-
EXECUTE master.dbo.xp_delete_file 0,N'\\cerium\dbdumps',N'BAK',N'2010-05-06T09:12:06',1
EXECUTE master.dbo.xp_delete_file 0,N'\\cerium\dbdumps',N'TRN',N'2010-05-08T09:12:21',1
Just ran this and it worked...
The only differences I can see between these 2 default SQL server instances is:-
The new one is Microsoft SQL Server Standard Edition (64-bit)
The old one is Microsoft SQL Server Enterprise Edition
The platform is NT AMD64
The old one is NT INTEL X86
Memory is 12287 (MB)
Memory on old one is 4095 (MB)
Root Directory is on E: drive
Root Directory is on C: drive
Server collation, Operating system, version, language and IsClustered are all the same.
Just can't think what I'm missing on this one but I bet it's obvious!
May 12, 2010 at 5:14 am
Found it - finally.
Whilst the SQL agent on the old and new servers, run under local system account, it clearly uses the security from the SQL server service? On the old server, the SQL instance was running under a domain admin account (naughty I know), so the new server had a new non-domain admin account. I did wonder before if this could cause a problem, so I added the new account into the local administrator group on the backup server, just in case. It appears that was not enough though, because it wasn't until I gave it Full Control rights today that it worked.
I would have thought the security would have come from the Agent service anyway bearing in mind these jobs are kicked off from this...
Never mind, just pleased I fixed it before saying Sayounara to the old server!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply