Database Logs are being truncated

Hi,
Can someone help me with a SQL error that I'm getting in my event log. The
error is listed below.
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (6)
Event ID: 17055
Date: 12/15/2003
Time: 12:03:47 AM
User: N/A
Computer: SQL-SERVER
Description:
18278 :
Database log truncated: Database: model.
Data:
0000: 66 47 00 00 10 00 00 00 fG.....
0008: 0b 00 00 00 53 00 51 00 ...S.Q.
0010: 4c 00 2d 00 53 00 45 00 L.-.S.E.
0018: 52 00 56 00 45 00 52 00 R.V.E.R.
0020: 00 00 06 00 00 00 6d 00 .....m.
0028: 6f 00 64 00 65 00 6c 00 o.d.e.l.
0030: 00 00 ..
This is happening on 4 of my databases. 2 created by me, Crystal Reports
database (CE8), and the model database.
I'm backing up all db's and the logs for the 4 db's mentioned above full
every day. This problem has been happening long before I decided to backup
the logs though. I thought backing up the logs would fix this problem. The
4 db's above all have Recovery set to "Full", because log backups were
failing when Recovery was set to "Simple" and the error suggested that
recovery be set to something other than "Simple".
I increased the size of the db's and logs to 100 meg, but that didn't help.
Also the size kept going back to a small number. Now I set the databases to
grow automatically, but I set the maximum size allowed to grow to 100 meg
for both db's and logs. The db's are between 2 to 5 megs though.
We write about 100 - 200 lines of data to the server every day. We don't
use Crystal Reports anymore and I'm not sure what the model db is for. The
db grows very slowly.
Thanks!!!
DaveRegarding the error you see, check this article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;818202&Product=sql2k
replies in line.
Steve
"Dave" <none@.none.com> wrote in message
news:O06b7MMxDHA.1596@.TK2MSFTNGP10.phx.gbl...
> Can someone help me with a SQL error that I'm getting in my event log.
The
> error is listed below.
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (6)
> Event ID: 17055
> Date: 12/15/2003
> Time: 12:03:47 AM
> User: N/A
> Computer: SQL-SERVER
> Description:
> 18278 :
> Database log truncated: Database: model.
> Data:
> 0000: 66 47 00 00 10 00 00 00 fG.....
> 0008: 0b 00 00 00 53 00 51 00 ...S.Q.
> 0010: 4c 00 2d 00 53 00 45 00 L.-.S.E.
> 0018: 52 00 56 00 45 00 52 00 R.V.E.R.
> 0020: 00 00 06 00 00 00 6d 00 .....m.
> 0028: 6f 00 64 00 65 00 6c 00 o.d.e.l.
> 0030: 00 00 ..
> This is happening on 4 of my databases. 2 created by me, Crystal Reports
> database (CE8), and the model database.
> I'm backing up all db's and the logs for the 4 db's mentioned above full
> every day. This problem has been happening long before I decided to
backup
> the logs though. I thought backing up the logs would fix this problem.
The
> 4 db's above all have Recovery set to "Full", because log backups were
> failing when Recovery was set to "Simple" and the error suggested that
> recovery be set to something other than "Simple".
> I increased the size of the db's and logs to 100 meg, but that didn't
help.
> Also the size kept going back to a small number. Now I set the databases
to
> grow automatically, but I set the maximum size allowed to grow to 100 meg
> for both db's and logs. The db's are between 2 to 5 megs though.
> We write about 100 - 200 lines of data to the server every day. We don't
> use Crystal Reports anymore and I'm not sure what the model db is for. Th
e
> db grows very slowly.
There is no reason to set the recovery mode on model to full, simple would
be fine. Model is used as a template when you create new databases.
Steve|||Hi Steve,
Thanks for article. Here's the code for the log backups. It looks like
that I don't have the NO_LOG or the TRUNCATE_ONLY options set. I changed
the recover mode of the databases to bulk logged. I'll see if that helps.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
E79DF926-E729-4FA2-8769-62EE4F86120E -Rpt "C:\Program Files\Microsoft SQL
Server\MSSQL\LOG\DB Maintenance Plan - Log Backups6.txt" -DelTxtRpt
2WEEKS -WriteHistory -VrfyBackup -BkUpMedia
ISK -BkUpLog -UseDefDir -DelBkUps 2WEEKS -CrBkSubDir -BkExt "TRN"'
Thanks!!!|||Hi Dave,
Let us know if that helps... if it doesn't we can give you some alternatives
to maintenance plans.
Steve
"Dave" <none@.none.com> wrote in message
news:#$gd3DNxDHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hi Steve,
> Thanks for article. Here's the code for the log backups. It looks like
> that I don't have the NO_LOG or the TRUNCATE_ONLY options set. I changed
> the recover mode of the databases to bulk logged. I'll see if that helps.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> E79DF926-E729-4FA2-8769-62EE4F86120E -Rpt "C:\Program Files\Microsoft SQL
> Server\MSSQL\LOG\DB Maintenance Plan - Log Backups6.txt" -DelTxtRpt
> 2WEEKS -WriteHistory -VrfyBackup -BkUpMedia
> ISK -BkUpLog -UseDefDir -DelBkUps 2WEEKS -CrBkSubDir -BkExt "TRN"'
> Thanks!!!
>|||Still getting the error after I set the recovery mode to bulk logged.
Is it possible that my space allocation for the logs isn't working right?
Here's what my setup looks like for one of my db's that's generating the
error.
&Transaction Log Files
Space Allocated = 3Mb
File Properties
Automatically Grow File = Checked
In Megabytes = 3
Restrict File Growth (MB) = 100
What else can I look at?
Thanks!
Dave|||Sometimes autogrow doesn't seem to "catch up". I suggest that you pre-allocate storage.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dave" <none@.none.com> wrote in message news:OudZx1YxDHA.2328@.TK2MSFTNGP10.phx.gbl...
> Still getting the error after I set the recovery mode to bulk logged.
> Is it possible that my space allocation for the logs isn't working right?
> Here's what my setup looks like for one of my db's that's generating the
> error.
> &Transaction Log Files
> Space Allocated = 3Mb
> File Properties
> Automatically Grow File = Checked
> In Megabytes = 3
> Restrict File Growth (MB) = 100
> What else can I look at?
> Thanks!
> Dave
>|||I agree with Tibor -- pre-allocate the database and transaction log size to
the estimated total size needed. Also, consider setting the recovery mode to
'simple' for the system databases; master, msdb, model & tempdb. Do place
the first three on a full backup cycle though...
Steve
"Dave" <none@.none.com> wrote in message
news:OudZx1YxDHA.2328@.TK2MSFTNGP10.phx.gbl...
> Still getting the error after I set the recovery mode to bulk logged.
> Is it possible that my space allocation for the logs isn't working right?
> Here's what my setup looks like for one of my db's that's generating the
> error.
> &Transaction Log Files
> Space Allocated = 3Mb
> File Properties
> Automatically Grow File = Checked
> In Megabytes = 3
> Restrict File Growth (MB) = 100
> What else can I look at?
> Thanks!
> Dave
>|||I have tried the following in the past and have not had success.
Under database properties->Transaction Log->&Transaction Log Files I
increased the Space Allocated (MB) field to 100 MB. It seemed to work, but
when I checked this again the next day, the space allocated value was down
to a small number (3 MB). Am I doing this right?
Thanks!!!
Dave|||Hi Steve,
I set model back to Simple recovery mode so I have all databases on Simple
recovery mode except for the 2 that we created and the Crystal Reports db.
I noticed something strange in the event log that I hadn't noticed before.
My backup schedule for the databases(not the logs) was at 5am, but the event
log says they're being backed up at around 12 am and this is when the errors
are occuring. The backups excecute again again at 5am successfully with no
event log errors. The logs get backed up successfully on schedule at 6:00
am. I'm guessing that the 12 o'clock backups are caused by our tape backup
software(Tapeware) which has a sql server connector option that we bought.
The Tapeware backups are scheduled at 12:01am every day. This is the only
thing I can think of that would cause the backups at around 12am.
Anyway, I'm going to disable the sql backups on Tapeware for a day and see
if I still get errors.
Should I not back up the logs for model? Simple recovery models cause log
backup errors.
Thanks!!!
Dave|||> Should I not back up the logs for model?
I'd say its overkill.
> Simple recovery models cause log
> backup errors.
IF you set model to simple, then don't do log backups for model, quite
simply.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dave" <none@.none.com> wrote in message
news:u8j09FlxDHA.2568@.TK2MSFTNGP09.phx.gbl...
> Hi Steve,
> I set model back to Simple recovery mode so I have all databases on Simple
> recovery mode except for the 2 that we created and the Crystal Reports db.
> I noticed something strange in the event log that I hadn't noticed before.
> My backup schedule for the databases(not the logs) was at 5am, but the
event
> log says they're being backed up at around 12 am and this is when the
errors
> are occuring. The backups excecute again again at 5am successfully with
no
> event log errors. The logs get backed up successfully on schedule at 6:00
> am. I'm guessing that the 12 o'clock backups are caused by our tape
backup
> software(Tapeware) which has a sql server connector option that we bought.
> The Tapeware backups are scheduled at 12:01am every day. This is the only
> thing I can think of that would cause the backups at around 12am.
> Anyway, I'm going to disable the sql backups on Tapeware for a day and see
> if I still get errors.
> Should I not back up the logs for model? Simple recovery models cause log
> backup errors.
> Thanks!!!
> Dave
>
>|||Thanks Tibor,
I disabled our tape backup software sql server backup function and the
errors went away.
My maintenance plan backups are working without any errors.
I'm going to assume that the sql server is working fine even though I'm
getting the log truncated errors. I don't think I can do anything to fix
that. Is this a good assumtion?
Thanks!!!