Re: 1105 - log file getting full - SQL Server 6.5

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Hari (hari_prasad_k_at_hotmail.com)
Date: 06/18/04 

Date: Fri, 18 Jun 2004 09:55:59 +0530

Hi,

I feel that you have the database option "Truncate log of check point"
option turned off. If this option
is turned off you have to perform the Transaction log backup to clear the
transaction log. Otherwise all
the committed and non-commited trasnaction data will be in Transaction log.
This can case this error.

How to check the database options enables:-

sp_dboption <dbname>

If the option 'Truncate log on checkpoint' is not shown then you have
perform tranaction log backup in frequent intervals.

How to check the trasnaction log size and Usage

dbcc sqlperf(logspace)

If you have 'Truncate log on checkpoint' disabled, perform the trasnaction
log backup and do a dbcc checktable on syslogs table.

Script

dump tran <dbname> to disk='c:\backup\dbname_tran.dmp'
go
dbcc checktable(syslogs)

Note:

It is not required to backup and restore the database, rather identify the
problem using the above steps mentioned. 

--
Thanks
Hari
MCDBA
"Mike NG" <sweepdog@deadspam.com> wrote in message
news:Y+4LLABXXi0AFwb4@ntlworld.com...
> I am getting an error along the lines of
>
> Can't allocate space for object 'Syslogs' in database ''corp'' because
> the
> 'logsegment' segment is full. If you ran out of space in Syslogs, dump
> the
> transaction log
>
> when trying to delete data from 'corp' (no it's not the tempdb problem!)
>
> I' increased the size of the logsegment and tried doing something along
> the lines of
>
> SET ROWCOUNT 10
> go
> while (select (count(*) from table) > 0
> begin
>     delete from table
> end
>
> to break the task down which did work.  However about a couple of weeks
> on, I need to do some more deletions on the table, and the log is full
> again - even doing the broken down version above.  I could increase the
> log file again a bit, but a) I need to stop this happening, and b) there
> isn't much disk space left.  I have no control over the disk purchases
> and I think pretty much all the data is needed
>
> I can't do truncate because replication is turned on and it'd be a right
> pain disabaling all the tables I need to do and then turning them back
> on again
>
> I've done DUMP transaction corp with no_log but this either fails or
> doesn't recover any space.  DBCC Checktables didn't do a right lot
> either (excuse me for my vagueness, but my notes are at work and I need
> a solution for Saturday morning)
>
> I have tried putting the database into single user modem but this has
> always failed since I haven't been the exclusive user of it.  I doubt
> very much that it will work if I tried it at the weekend
>
>
> So someone has suggested doing a backup of corp and restoring it again.
> Is this going to work.  The database has separate data and log devices
> (rather than shared).  Any thoughts on whether this is going to work
> would be appreciated.  Did I remember seeing an option somewhere that
> would select whether the log file would be copied or not, or was I
> dreaming.
>
> Any help greatly appreciated
>
> Many thanks
> --
> Mike
> Please post replies to newsgroup to benefit others
> Replace dead spam with ntl world to reply by email

Relevant Pages

  • Re: database log truncated, transaction log backup job failed
    ... The integrity check and database backups are successful. ... log backup job fails and I get a message about Database log truncated. ... So how does the command job for the step that runs the transaction log ...
    (microsoft.public.sqlserver.tools)
  • Re: 1105 - log file getting full - SQL Server 6.5
    ... >is turned off you have to perform the Transaction log backup to clear the ... >perform tranaction log backup in frequent intervals. ... >If you have 'Truncate log on checkpoint' disabled, ... >log backup and do a dbcc checktable on syslogs table. ...
    (microsoft.public.sqlserver.server)
  • Re: Problem with point in time recovery
    ... >create database test ... The transaction log backup has been ... >> LSN 869000001648300001 can be restored. ... >> Is there any option to cut off the transaction log with the full ...
    (microsoft.public.sqlserver.programming)
  • Is this some structural problem?
    ... log backup is larger than before and if you are restoring from the ... Because of the changes in the recovery model in SQL Server 2000, ... use the Full recovery mode and you run DBCC DBREINDEX, the transaction log ... If you only rely on full database backups, ...
    (microsoft.public.sqlserver.server)
  • Re: Error message showing the log file for database is full
    ... > 1) Is is necessary to make a transaction log backup on a regular basis? ... space there is no backup of the> production database. ... It also says to back up the transaction log for the database to free up some log space. ...
    (microsoft.public.sqlserver.server)