What are the challenges you face when working across database platforms? Take the survey
Options

Old tran log files not deleted - using SQL Backup 6.4

soxfanjimsoxfanjim Posts: 16
edited June 9, 2010 9:06AM in SQL Backup Previous Versions
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.

Comments

  • Options
    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
    English DBA living in CANADA
  • Options
    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.
  • Options
    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
    English DBA living in CANADA
  • Options
    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.
  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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.
  • Options
    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
  • Options
    peteypetey Posts: 2,358 New member
    Thanks for the update. This bug has been logged and will be fixed in the next release of SQL Backup.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.