Hello All,
The hourly sheduled appends to the trasnaction log file start genereating
the following in the output log.
There is no current database backup. This log backup cannot be used to roll
forward a preceding database backup. [SQLSTATE 01000].
This is happening on one database on SQL 2000 machine running three
instances of SQL Server. This happening to a database that is on the default
instance. Does anyone know why this is happening? Other than that, there is
one transaction log device and four data devices with the same [Primary]
filegroup defined for each data device in the database. Since I took over as
the admin for this particular database, I have not been able to trucnate the
transaction log. The Dbcc produces the following:
Cannot shrink log file 2 ('') because total number of logical log files
cannot be fewer than 2. [SQLSTATE 01000].
I have tried following the instructions as per MSKB 324432 but have'nt had
any luck. The only thing that I have not tried is recreate the database with
only one log and one data device and transfer all the data. Actually I did
start it once and ran out of diskspace when the indexes were being generated,
so I had to scrap that idea. If you have any clue as to what's going on,
please help.
Thanks.
--
Bilal Abbasi
Chadbourne & Parke LLP
30 Rockefeller Plaza
New York, NY 10112Hi
It looks like you don't have a full database backup? Backing up the log will
allow the log to be re-used and it won't change the size. Shrinking the log
file can make it fragmented on the disc and should only be carried out after
significant abnormal growth.
John
"Bilal Abbasi" wrote:
> Hello All,
> The hourly sheduled appends to the trasnaction log file start genereating
> the following in the output log.
> There is no current database backup. This log backup cannot be used to roll
> forward a preceding database backup. [SQLSTATE 01000].
> This is happening on one database on SQL 2000 machine running three
> instances of SQL Server. This happening to a database that is on the default
> instance. Does anyone know why this is happening? Other than that, there is
> one transaction log device and four data devices with the same [Primary]
> filegroup defined for each data device in the database. Since I took over as
> the admin for this particular database, I have not been able to trucnate the
> transaction log. The Dbcc produces the following:
> Cannot shrink log file 2 ('') because total number of logical log files
> cannot be fewer than 2. [SQLSTATE 01000].
> I have tried following the instructions as per MSKB 324432 but have'nt had
> any luck. The only thing that I have not tried is recreate the database with
> only one log and one data device and transfer all the data. Actually I did
> start it once and ran out of diskspace when the indexes were being generated,
> so I had to scrap that idea. If you have any clue as to what's going on,
> please help.
> Thanks.
>
> --
> Bilal Abbasi
> Chadbourne & Parke LLP
> 30 Rockefeller Plaza
> New York, NY 10112|||see this..from Books Online
Virtual Log Files
Each transaction log file is divided logically into smaller segments called
virtual log files. Virtual log files are the unit of truncation for the
transaction log. When a virtual log file no longer contains log records for
active transactions, it can be truncated and the space becomes available to
log new transactions.
The smallest size for a virtual log file is 256 kilobytes (KB). The minimum
size for a transaction log is 512 KB, which provides two 256-KB virtual log
files. The number and size of the virtual log files in a transaction log
increase as the size of the log file increases. A small log file can have a
small number of small virtual log files (for example, a 5-MB log file that
comprises five 1-MB virtual log files). A large log file can have larger
virtual log files (for example, a 500-MB log file that comprises ten 50-MB
virtual log files).
Microsoft® SQL ServerT 2000 tries to avoid having many small virtual log
files. The number of virtual log files grows much more slowly than the size.
If a log file grows in small increments, it tends to have many small virtual
log files. If the log file grows in larger increments, SQL Server creates a
smaller number of larger virtual log files. For example, if the transaction
log is growing by 1-MB increments, the virtual log files are smaller and
more numerous compared to a transaction log growing at 50-MB increments. A
large number of virtual log files can increase the time taken to perform
database recovery.
As records are written to the log, the end of the log grows from one virtual
log file to the next. If there is more than one physical log file for a
database, the end of the log grows through each virtual log file in each
physical file before circling back to the first virtual log file in the
first physical file. Only when all log files are full will the log begin to
grow automatically.
"Bilal Abbasi" <BilalAbbasi@.discussions.microsoft.com> wrote in message
news:A8FE0793-E2B4-47E6-B176-9F78E78D6BFB@.microsoft.com...
> Hello All,
> The hourly sheduled appends to the trasnaction log file start genereating
> the following in the output log.
> There is no current database backup. This log backup cannot be used to
> roll
> forward a preceding database backup. [SQLSTATE 01000].
> This is happening on one database on SQL 2000 machine running three
> instances of SQL Server. This happening to a database that is on the
> default
> instance. Does anyone know why this is happening? Other than that, there
> is
> one transaction log device and four data devices with the same [Primary]
> filegroup defined for each data device in the database. Since I took over
> as
> the admin for this particular database, I have not been able to trucnate
> the
> transaction log. The Dbcc produces the following:
> Cannot shrink log file 2 ('') because total number of logical log files
> cannot be fewer than 2. [SQLSTATE 01000].
> I have tried following the instructions as per MSKB 324432 but have'nt had
> any luck. The only thing that I have not tried is recreate the database
> with
> only one log and one data device and transfer all the data. Actually I
> did
> start it once and ran out of diskspace when the indexes were being
> generated,
> so I had to scrap that idea. If you have any clue as to what's going on,
> please help.
> Thanks.
>
> --
> Bilal Abbasi
> Chadbourne & Parke LLP
> 30 Rockefeller Plaza
> New York, NY 10112|||That is exactly the point. The full dump is done and transaction log is
initialized as a scheduled process. Subsequent appends work so the log files
increase to around 15 when the output from the append job starts writing
"There is no current backup", just out of the blue. And it's not something
that I can predict either as to at what point this will start to fail.
Another thing that I noticed is that there are 5 rows in the sysfiles table
and 6 in sysfiles1. The sixth row in sysfiles1 is pointing to a physical LDF
file that does not exist. I have not found a way to get rid of this
obviously orphanned record. It's a mess I need to cleanup somehow.
--
Bilal Abbasi
Chadbourne & Parke LLP
30 Rockefeller Plaza
New York, NY 10112
"John Bell" wrote:
> Hi
> It looks like you don't have a full database backup? Backing up the log will
> allow the log to be re-used and it won't change the size. Shrinking the log
> file can make it fragmented on the disc and should only be carried out after
> significant abnormal growth.
> John
> "Bilal Abbasi" wrote:
> > Hello All,
> >
> > The hourly sheduled appends to the trasnaction log file start genereating
> > the following in the output log.
> > There is no current database backup. This log backup cannot be used to roll
> > forward a preceding database backup. [SQLSTATE 01000].
> >
> > This is happening on one database on SQL 2000 machine running three
> > instances of SQL Server. This happening to a database that is on the default
> > instance. Does anyone know why this is happening? Other than that, there is
> > one transaction log device and four data devices with the same [Primary]
> > filegroup defined for each data device in the database. Since I took over as
> > the admin for this particular database, I have not been able to trucnate the
> > transaction log. The Dbcc produces the following:
> >
> > Cannot shrink log file 2 ('') because total number of logical log files
> > cannot be fewer than 2. [SQLSTATE 01000].
> >
> > I have tried following the instructions as per MSKB 324432 but have'nt had
> > any luck. The only thing that I have not tried is recreate the database with
> > only one log and one data device and transfer all the data. Actually I did
> > start it once and ran out of diskspace when the indexes were being generated,
> > so I had to scrap that idea. If you have any clue as to what's going on,
> > please help.
> >
> > Thanks.
> >
> >
> > --
> > Bilal Abbasi
> > Chadbourne & Parke LLP
> > 30 Rockefeller Plaza
> > New York, NY 10112|||Hi
You could code around this by issuing a full backups if you hit this error
to restart the log backup sequence, or it would probably better to use SQL
profiler to get to the root of this and find what is issuing the statement
that is breaking the log backup sequence.
What does sp_helpfiles return?
Can you take a full backup and restore it on a different machine? If so you
could try ALTER DATABASE <db> REMOVE FILE <logical_file>
John
"Bilal Abbasi" wrote:
> That is exactly the point. The full dump is done and transaction log is
> initialized as a scheduled process. Subsequent appends work so the log files
> increase to around 15 when the output from the append job starts writing
> "There is no current backup", just out of the blue. And it's not something
> that I can predict either as to at what point this will start to fail.
> Another thing that I noticed is that there are 5 rows in the sysfiles table
> and 6 in sysfiles1. The sixth row in sysfiles1 is pointing to a physical LDF
> file that does not exist. I have not found a way to get rid of this
> obviously orphanned record. It's a mess I need to cleanup somehow.
> --
> Bilal Abbasi
> Chadbourne & Parke LLP
> 30 Rockefeller Plaza
> New York, NY 10112
>
> "John Bell" wrote:
> > Hi
> >
> > It looks like you don't have a full database backup? Backing up the log will
> > allow the log to be re-used and it won't change the size. Shrinking the log
> > file can make it fragmented on the disc and should only be carried out after
> > significant abnormal growth.
> >
> > John
> >
> > "Bilal Abbasi" wrote:
> >
> > > Hello All,
> > >
> > > The hourly sheduled appends to the trasnaction log file start genereating
> > > the following in the output log.
> > > There is no current database backup. This log backup cannot be used to roll
> > > forward a preceding database backup. [SQLSTATE 01000].
> > >
> > > This is happening on one database on SQL 2000 machine running three
> > > instances of SQL Server. This happening to a database that is on the default
> > > instance. Does anyone know why this is happening? Other than that, there is
> > > one transaction log device and four data devices with the same [Primary]
> > > filegroup defined for each data device in the database. Since I took over as
> > > the admin for this particular database, I have not been able to trucnate the
> > > transaction log. The Dbcc produces the following:
> > >
> > > Cannot shrink log file 2 ('') because total number of logical log files
> > > cannot be fewer than 2. [SQLSTATE 01000].
> > >
> > > I have tried following the instructions as per MSKB 324432 but have'nt had
> > > any luck. The only thing that I have not tried is recreate the database with
> > > only one log and one data device and transfer all the data. Actually I did
> > > start it once and ran out of diskspace when the indexes were being generated,
> > > so I had to scrap that idea. If you have any clue as to what's going on,
> > > please help.
> > >
> > > Thanks.
> > >
> > >
> > > --
> > > Bilal Abbasi
> > > Chadbourne & Parke LLP
> > > 30 Rockefeller Plaza
> > > New York, NY 10112
