Not really an SQL guy here and I wanted to run this by someone that knows more about it than I do. I'm pretty sure I figured out what's wrong but fixing it is another matter.
I have a database called "PracticeManager". As part of the nightly maintenance it performs a self-backup to the local drive on the server which is pretty straight forward.
1. This database is also backed up to a SonicWALL CDP 2440i (basically a fancy backup server). The backup server backs up the logs for the database every 2 hours.
2. The database itself up backs up every night at 2am.
3. After the database backs itself up to the local drive the backup server will not back anything else up from that database until another full backup is performed. Aat this point I'm running full backups every day. Not exactly ideal since I'm eating up an enormous amount of space to have backup history going back more than a few days.
So...I suspect that because the job is executing the "DUMP TRANSACTION PracticeManager WITH TRUNCATE_ONLY", the backup server doesn't know what to do with it because the log file has changed since the last time it got backed up. It needs to wait until it does a another full backup again so it knows where to start again.
I've considered getting rid of the self backup altogether but I'm not sure what effect that will have on the database, if any. Also if it doesn't perform a self backup then the end users get errors every time they log into the application that uses this database which generates more phone calls in addition to making me look like I'm not doing my job. :)
Does my theory hold water? Anyone have any ideas how I can modify this job or even do anything else out of the blue to keep this job from interfering with the functionality of the backup server?
I've included the code the job runs every night.
Any help is appreciated, thanks in advance.
EXECUTE master..xp_sqlmaint '-D PracticeManager -WriteHistory -BkUpOnlyIfClean -CkDB -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1DAYS -BkExt "BAK" -VrfyBackup -UpdOptiStats 15'
DUMP TRANSACTION PracticeManager WITH TRUNCATE_ONLY
exec gJobHistory 'Backup - PracticeManager'