Post reply

Backup mystery cleared!

  • August 18, 2004 at 2:24 am

    #83698

    Hi,

    This is related to a previous thread (can view it at http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=126882)

    I think I owe it to everybody who posted and contributed to this thread to inform you about the reason for this wiered backup sizes

    The backup job was supposed to delete the data and log backup files created by the job one week ago (e.g. the Saturday job was supposed to first delete the files SAT_DB and SAT_LOG, then create the backup files for this day, with same name of course)

    Thus circulating files weekly, and ensuring that i keep backup files for last 6 days

    Now for some reason, (new puzzle)! the job sometimes was not deleting the data backup file, or the log backup, or both, then continue with backup and appending to the existing files

    Thus the wiered combinations of data and log backup sizes over the days, also this big backup size mentioned (14 GB) was the result of continuing appending for this particular week day(e.g. if the Sunday job was always not able to delete the sunday files, it will continue appending to them, thus the size growth)

    even so this looks very simple (even silly) i couldn't catch it at first cause the bahaviour was inconsistent (size ok for some days, different sizes for other days. ...etc)

    this is taken care of now

    Thank you all, especially for hoo-t (Steve) who first hinted at the possibility of appending, but i was blindly digging at another path at this time!!

     

    EM

  • August 23, 2004 at 8:00 am

    This was removed by the editor as SPAM

  • August 23, 2004 at 9:57 am

    #520198

    Glad you figured it out and it's working.

    Thanks for the followup.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • August 23, 2004 at 10:16 am

    #520203

    Thank you for the update and for the explicit thank you! 

    Steve

  • August 23, 2004 at 10:30 am

    #520205

    Try going into your query analyzer and run sp_helpdevice against both you master and user db's.

    I'll make a guess that SQL has/had attached your file(s) as a device. Therefore the file can't be deleted because the SQL Server/Agent has the file locked open (i.e Cannot delete file because someone else is using the file error).

    If you run into it again you may need to make a sp_dropdevice procedure.

    Just my $0.02.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply