Old tran log files not deleted - using SQL Backup 6.4

soxfanjim · June 7, 2010 2:44PM

I am using SQL Backup 6.4.0.56 and discovered the other day that our old transaction log files were not being deleted. ("Discovered" is a nice way of saying my Network Manager wanted to string me up for chewing up 200Gb of extra disk space.) We're using a script that calls SQL Backup and ultimately executes this statement to delete files that are more than 336 hours old:
DECLARE @ReturnCode int EXECUTE @ReturnCode = master.dbo.sqbutility 1032, N'SomeDatabase', N'F:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SOMESERVER$INST1\SomeDatabase\LOG', 'L', '336h' IF @ReturnCode <> 0 RAISERROR('Error deleting SQLBackup backup files.', 16, 1)

I've checked permissions on the backup directory and both the SQL Server Agent account (which the SQL job runs as) and the SQL Server account (which the SQL Backup service runs as) have Full Control permissions on the directory. But when I run statement above I get "(0 row(s) affected)" as the output and no files are deleted. The directory I'm running this against has files that are 20+ days old, so there is definitely something to delete.

I have not found any useful log information. But I did try some variations on the command above. Interestingly I changed the directory to an invalid path and instead of getting an error, as I expected, I got the same "(0 row(s) affected)" output.

Any ideas to help resolve this are welcome.

ChrisAVWood · June 7, 2010 3:06PM

As you are running 6.4 you should be able to opem up the GUI on the server and right click on the log and Show Log. You should then see the parms used followed by the summary of the backup followed by any attempted deletes.

HTH

Chris

soxfanjim · June 7, 2010 4:20PM

Thanks, Chris. I might've checked that before, but couldn't remember so I went back and looked. On the server where this is happening (I should've mentioned that it's only occurring on one of our servers) the log output is basically identical to the log output on a server where the log file deletes are working. In neither case do I see anything in the parms that indicates a file deletion will occur, but it's definitely happening on the other servers. The one where it's not working is our only SQL Server cluster, in case that's interesting.

The log on the offending server shows this:
-SQL "BACKUP LOG [SomeDatabase] TO DISK = N'F:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SomeServer$INST1\SomeDatabase\LOG\SomeServer$INST1_SomeDatabase_LOG_20100607_154533.trn' WITH INIT, CHECKSUM, COMPRESSION = 2 "

PROCESSES COMPLETED SUCCESSFULLY

6/7/2010 3:45:33 PM: Backing up SomeDatabase (transaction log) on INST1 instance to:
6/7/2010 3:45:33 PM: F:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SomeServer$INST1\SomeDatabase\LOG\SomeServer$INST1_SomeDatabase_LOG_20100607_154533.trn

6/7/2010 3:45:33 PM: BACKUP LOG [SomeDatabase] TO VIRTUAL_DEVICE = 'SQLBACKUP_A96CEFEB-BDE0-45EF-9745-CB8E6C5C988B' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (SomeDatabase), 6/7/2010 3:45:33 PM', DESCRIPTION =
N'Backup on 6/7/2010 3:45:33 PM Server: SomeServer\INST1 Database: SomeDatabase', FORMAT, CHECKSUM

6/7/2010 3:45:33 PM: Backup data size : 512.000 KB
6/7/2010 3:45:33 PM: Compressed data size: 6.000 KB
6/7/2010 3:45:33 PM: Compression rate : 98.83%

6/7/2010 3:45:33 PM: Processed 0 pages for database 'SomeDatabase', file 'SomeDatabase_log' on file 1.
6/7/2010 3:45:33 PM: BACKUP LOG successfully processed 0 pages in 0.004 seconds (0.000 MB/sec).
6/7/2010 3:45:33 PM: SQL Backup process ended.

ChrisAVWood · June 7, 2010 4:49PM

We don't run our jobs that why. We run EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE and a whole lot of parameters. I have never used sqbutility but from your output you are definitely not looking to delete any old backups either from the disk or from msdb. You might have a very swollen msdb if you aren't getting the jobhistory entries cleaned up too.

Myabe someone from Red Gate will tell you how to code sqbutilty to make it delete old files.

Chris

soxfanjim · June 8, 2010 12:27PM

The script from which we call SQL Backup handles various steps - creating a subdirectory (if necessary) for the backup files, backing up the transaction log, doing a RESTORE VERIFYONLY on the backup file, then finally deleting aged files via the sqbutility XP - see syntax in original post. It is only the last step that is failing.

I'm hoping that a Red Gate technician can give me some ideas on what to look at next, as it appears to be a valid call to the XP but I'm not getting the desired result.

petey · June 9, 2010 3:08AM

There is indeed a bug in the sqbutility 1032 option, where it fails to process folders containing spaces in them. Is this the case on the servers where the function is working i.e. the folders where the backup files are located do not contain spaces in their names?

Thanks.

soxfanjim · June 9, 2010 8:46AM

Yes, the other servers use paths that do not contain spaces. That must be it. So if I change it to use a share name instead of the file path I should be all set? I'll try that.

I've always hated the default installation path and unfortunately inherited this server and didn't get the chance to change it at install.

soxfanjim · June 9, 2010 8:54AM

Peter,

That was it. I created a share, changed the path in my job and everything ran as expected, including the deletion of old backup files.

Thanks a bunch.

Jim

petey · June 9, 2010 9:06AM

Thanks for the update. This bug has been logged and will be fixed in the next release of SQL Backup.

Old tran log files not deleted - using SQL Backup 6.4

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk