Post reply

backup log, shrink database and log, backup database

  • October 13, 2005 at 10:14 pm

    #111007

    hi,

    i have 2 scheduled jobs that run every night.  the first job has the ff t-sql statements on it:

    BACKUP LOG SERVICECENTERMAPPED WITH TRUNCATE_ONLY

    DBCC SHRINKDATABASE (N'ServiceCentermapped', 10)

    the next job that follows has the ff statements:

    BACKUP DATABASE [ServiceCenterMapped] TO  DISK = N'F:\SQLData\ServiceCenterMapped.BAK'

    WITH  INIT ,  NOUNLOAD , 

    NAME = N'ServiceCenterMapped Database Backup', 

    NOSKIP ,  STATS = 10,  NOFORMAT

    in the event viewer, every time the first job runs it logs the following error message:

    18278 : Database log truncated: Database: SERVICECENTERMAPPED.

    i did some net surfing about this and the microsoft site came up with this:

    You might see the message only when you have the database in full recovery mode, and you try to

    back up the transaction log by using either the NO_LOG or the TRUNCATE_ONLY options. You may not

    receive the error message when you do not have the database in full recovery mode, or when you do

    not use the NO_LOG or TRUNCATE_ONLY options when you back up the transaction log.

    (url is: http://support.microsoft.com/kb/818202/EN-US/)

    should i be concered about the state of my backup file?  i'm not an sql admin but i have been assigned to look

    after this one.  i tried restoring my backup into another server and it looks fine, although i never tested it

    but i assume that since i was able to restore it then everything is okay.

    any thought/ideas would be appreciated.

    thanks.

    ann

     

     

  • October 14, 2005 at 12:47 am

    #597264

    Check out this link for a good synopsis of how to easily regularly shrink your tranaction log file:

    Dynamically Shrink the Transaction Log with a Stored Procedure

    http://codetempest.com/article.php?story=20050703191510469

    G. Milner

  • October 14, 2005 at 1:34 am

    #597278

    I would be concerned!

    Your transaction log contains all the changes done to your database and should be backed up regularly (e.g. if you can only afford to lose 1 hours worth of work, then you should backup the transaction log every hour). If you have any sort of problem, you may need to restore from your last full database backup, and then roll forward all the changes done after the full backup from your transaction log backup(s).

    The first of your jobs just truncates the transaction log i.e. throws away all of the changes without actually backing them up. The second job does a full database backup.

    If you have a problem some time between the first and second job, you will be able to restore from the last full backup (the previous night), but you will no longer have any changes in the transaction log to roll forward, so you will be stuck with last night's data.

     

  • October 14, 2005 at 7:36 am

    #597346

    <quote>every time the first job runs it logs the following error message:

    18278 : Database log truncated: Database: SERVICECENTERMAPPED.<end quote>

    Okay, what's the problem?  Your first job is to TRUNCATE THE DATABASE LOG.

    <Quote>BACKUP LOG SERVICECENTERMAPPED WITH TRUNCATE_ONLY

    <end quote>

    You truncate the database log, so you will get a message stating the database log is truncated. Were you expecting something different?

    By the way, the DBCC SHRINKDATABASE command should be a separate job step.

    -SQLBill

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

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