October 13, 2005 at 10:14 pm
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
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
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
<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