|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Full Transaction LogSQL 6.5
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Full Transaction LogSQL 6.5
|
|
| John Morgan 2002-11-20, 10:24 am |
| Hi there,
I have a 450 MB transaction log which shows zero free space. What is
the best way to clear this out without causing any irreperable harm?
As you may be able to tell from this, I am pretty inexperienced with SQL
and it's maintenance procedures.
Thanks in advance.
John
| |
| Catelin Wang 2002-11-20, 11:23 am |
| You can make your log has more space by doing one of the followings:
1.Set truncate log on checkpoint on.
2.Expend the device size for database transaction log .
3.Schedule a full DB backup then transaction log back up evry acouple hours
or so, depands on your need.
Catelin
"John Morgan" <"JMorg"@nospam@OAH.wa.gov> wrote in message
news:O4stl7KkCHA.2548@tkmsftngp11...
> Hi there,
>
> I have a 450 MB transaction log which shows zero free space. What is
> the best way to clear this out without causing any irreperable harm?
>
> As you may be able to tell from this, I am pretty inexperienced with SQL
> and it's maintenance procedures.
>
> Thanks in advance.
>
> John
>
| |
| Catelin Wang 2002-11-20, 1:23 pm |
| It might be your two log backup don't work along very well, try put the two
into one job,schedule it see if make difference.
CREATE PROCEDURE MYDBLOG AS
BEGIG
DECLARE @var1 INT
SELECT @var1 = datepart(hh,getdate())
IF @var1=8
BEGIN
DUMP TRANSACTION my db TO mydblog VOLUME = 'mydblog' WITH NOUNLOAD ,
STATS = 10, INIT , NOSKIP
END
ELSE
BEGIN
DUMP TRANSACTION myd TO mydbLog VOLUME = 'mydblog' WITH NOUNLOAD , STATS =
10, NOINIT , NOSKIP
END
Catelin
"John Morgan" <"JMorg"@nospam@OAH.wa.gov> wrote in message
news:uPpiUiLkCHA.3576@tkmsftngp08...
> Thanks for the reply.
>
> The backup part is the part that is confusing me. I have three scheduled
> backups running against the database. One is a full backup which goes
every
> night at 11:00 PM. The other is a dump of the transaction log with init
which
> occurs every morning at 8:00 AM. The third is a non init transaction log
dump
> which occurs hourly from 9:00 AM to 10:00 PM. I was under the impression
that
> one of these log dumps would clear out the log and let it start building
> again. I am a little confused.
>
> Here are the SQL statements for the 2 different Log Dumps:
>
> 8:00 AM:
>
> DUMP TRANSACTION HATS TO HATS_Log_Backup VOLUME = 'SS0002' WITH NOUNLOAD
,
> STATS = 10, INIT , NOSKIP
>
> 9:00 AM to 10:00 PM (Hourly)
>
> DUMP TRANSACTION HATS TO HATS_Log_Backup_noinit VOLUME = 'ss0002' WITH
> NOUNLOAD , STATS = 10, NOINIT , NOSKIP
>
> They both show as sucessful in the history.
>
> Am I missing something here?
>
> Thanks again.
> Catelin Wang wrote:
>
> > You can make your log has more space by doing one of the followings:
> > 1.Set truncate log on checkpoint on.
> > 2.Expend the device size for database transaction log .
> > 3.Schedule a full DB backup then transaction log back up evry acouple
hours
> > or so, depands on your need.
> >
> > Catelin
> >
> > "John Morgan" <"JMorg"@nospam@OAH.wa.gov> wrote in message
> > news:O4stl7KkCHA.2548@tkmsftngp11...
> > > Hi there,
> > >
> > > I have a 450 MB transaction log which shows zero free space. What is
> > > the best way to clear this out without causing any irreperable harm?
> > >
> > > As you may be able to tell from this, I am pretty inexperienced with
SQL[c
olor=darkred]
> > > and it's maintenance procedures.
> > >
> > > Thanks in advance.
> > >
> > > John
> > >
>[/color]
| |
| Michael Abraham 2002-11-20, 5:23 pm |
| As I recall, SQL 6.5 had issues with incorrect reporting of log free space.
Check out KB article Q183100. Try DBCC CHECKTABLE(syslogs) - it will fix
up the reporting of log free space. I used it many times in the olden
days - it never failed to fix up the stats.
Mike Abraham
"John Morgan" <"JMorg"@nospam@OAH.wa.gov> wrote in message
news:O4stl7KkCHA.2548@tkmsftngp11...
> Hi there,
>
> I have a 450 MB transaction log which shows zero free space. What is
> the best way to clear this out without causing any irreperable harm?
>
> As you may be able to tell from this, I am pretty inexperienced with SQL
> and it's maintenance procedures.
>
> Thanks in advance.
>
> John
>
| |
| John Morgan 2002-11-20, 5:23 pm |
| Thanks for that. I tried it and I am not sure how it will work yet. It does
the second portion, but of course I can't tell how it does the first until 8:00
in the morning.
I will say that I don't think it will solve my problem and here is why. If I
just do a manual log dump (either an init or a non init) I still have 0 free
space in the log file. On the other databases on that server when I do the same
thing the free space changes. I am wondering if the problem is that I have
450MB allocated and none free. Maybe there is a problem due to the fact the the
transaction log file iscompletely full? I did a dump with No_Log but that did
not hel either.
John
Catelin Wang wrote:
> It might be your two log backup don't work along very well, try put the two
> into one job,schedule it see if make difference.
>
> CREATE PROCEDURE MYDBLOG AS
> BEGIG
> DECLARE @var1 INT
> SELECT @var1 = datepart(hh,getdate())
> IF @var1=8
> BEGIN
> DUMP TRANSACTION my db TO mydblog VOLUME = 'mydblog' WITH NOUNLOAD ,
> STATS = 10, INIT , NOSKIP
> END
> ELSE
> BEGIN
> DUMP TRANSACTION myd TO mydbLog VOLUME = 'mydblog' WITH NOUNLOAD , STATS =
> 10, NOINIT , NOSKIP
> END
>
> Catelin
>
> "John Morgan" <"JMorg"@nospam@OAH.wa.gov> wrote in message
> news:uPpiUiLkCHA.3576@tkmsftngp08...
> > Thanks for the reply.
> >
> > The backup part is the part that is confusing me. I have three scheduled
> > backups running against the database. One is a full backup which goes
> every
> > night at 11:00 PM. The other is a dump of the transaction log with init
> which
> > occurs every morning at 8:00 AM. The third is a non init transaction log
> dump
> > which occurs hourly from 9:00 AM to 10:00 PM. I was under the impression
> that
> > one of these log dumps would clear out the log and let it start building
> > again. I am a little confused.
> >
> > Here are the SQL statements for the 2 different Log Dumps:
> >
> > 8:00 AM:
> >
> > DUMP TRANSACTION HATS TO HATS_Log_Backup VOLUME = 'SS0002' WITH NOUNLOAD
> ,
> > STATS = 10, INIT , NOSKIP
> >
> > 9:00 AM to 10:00 PM (Hourly)
> >
> > DUMP TRANSACTION HATS TO HATS_Log_Backup_noinit VOLUME = 'ss0002' WITH
> > NOUNLOAD , STATS = 10, NOINIT , NOSKIP
> >
> > They both show as sucessful in the history.
> >
> > Am I missing something here?
> >
> > Thanks again.
> > Catelin Wang wrote:
> >
> > > You can make your log has more space by doing one of the followings:
> > > 1.Set truncate log on checkpoint on.
> > > 2.Expend the device size for database transaction log .
> > > 3.Schedule a full DB backup then transaction log back up evry acouple
> hours
> > > or so, depands on your need.
> > >
> > > Catelin
> > >
> > > "John Morgan" <"JMorg"@nospam@OAH.wa.gov> wrote in message
> > > news:O4stl7KkCHA.2548@tkmsftngp11...
> > > > Hi there,
> > > >
> > > > I have a 450 MB transaction log which shows zero free space. What is
> > > > the best way to clear this out without causing any irreperable harm?
> > > >
> > > > As you may be able to tell from this, I am pretty inexperienced with
> SQL
> > > > and it's maintenance procedures.
> > > >
> > > > Thanks in advance.
> > > >
> > > > John
> > > >
> >
| |
| John Morgan 2002-11-20, 6:23 pm |
| Thanks Michael,
I tried that and got this result:
Checking syslogs
The total number of data pages in this table is 1409.
*** NOTICE: Space used on the log segment is 2.82 Mbytes, 0.61.
*** NOTICE: Space free on the log segment is 457.98 Mbytes, 99.39.
Table has 36560 data rows.
But I still show 0 free. Do I need to do anything else after issuing that
command?
Michael Abraham wrote:
> As I recall, SQL 6.5 had issues with incorrect reporting of log free space.
> Check out KB article Q183100. Try DBCC CHECKTABLE(syslogs) - it will fix
> up the reporting of log free space. I used it many times in the olden
> days - it never failed to fix up the stats.
>
> Mike Abraham
> "John Morgan" <"JMorg"@nospam@OAH.wa.gov> wrote in message
> news:O4stl7KkCHA.2548@tkmsftngp11...
> > Hi there,
> >
> > I have a 450 MB transaction log which shows zero free space. What is
> > the best way to clear this out without causing any irreperable harm?
> >
> > As you may be able to tell from this, I am pretty inexperienced with SQL
> > and it's maintenance procedures.
> >
> > Thanks in advance.
> >
> > John
> >
| |
| John Morgan 2002-11-20, 6:23 pm |
| Ooops. Wrote too soon. I did a log dump and now I am showing the free space
as being 449MB. Cool. Also my database free space has gone from 338MB to
1.5GB.
So it was just a reporting problem?
Which explains why the database was running OK, and not freaking out.
This one's going in the books.
Michael Abraham wrote:
> As I recall, SQL 6.5 had issues with incorrect reporting of log free space.
> Check out KB article Q183100. Try DBCC CHECKTABLE(syslogs) - it will fix
> up the reporting of log free space. I used it many times in the olden
> days - it never failed to fix up the stats.
>
> Mike Abraham
> "John Morgan" <"JMorg"@nospam@OAH.wa.gov> wrote in message
> news:O4stl7KkCHA.2548@tkmsftngp11...
> > Hi there,
> >
> > I have a 450 MB transaction log which shows zero free space. What is
> > the best way to clear this out without causing any irreperable harm?
> >
> > As you may be able to tell from this, I am pretty inexperienced with SQL
> > and it's maintenance procedures.
> >
> > Thanks in advance.
> >
> > John
> >
| |
| Tibor Karaszi 2002-11-21, 1:23 am |
| > So it was just a reporting problem?
Yep. And a quite common one... :-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"John Morgan" <"JMorg"@nospam@OAH.wa.gov> wrote in message news:evY3syOkCHA.2736@tkmsftngp10...
> Ooops. Wrote too soon. I did a log dump and now I am showing the free space
> as being 449MB. Cool. Also my database free space has gone from 338MB to
> 1.5GB.
>
> So it was just a reporting problem?
>
> Which explains why the database was running OK, and not freaking out.
>
> This one's going in the books.
>
> Michael Abraham wrote:
>
> > As I recall, SQL 6.5 had issues with incorrect reporting of log free space.
> > Check out KB article Q183100. Try DBCC CHECKTABLE(syslogs) - it will fix
> > up the reporting of log free space. I used it many times in the olden
> > days - it never failed to fix up the stats.
> >
> > Mike Abraham
> > "John Morgan" <"JMorg"@nospam@OAH.wa.gov> wrote in message
> > news:O4stl7KkCHA.2548@tkmsftngp11...
> > > Hi there,
> > >
> > > I have a 450 MB transaction log which shows zero free space. What is
> > > the best way to clear this out without causing any irreperable harm?
> > >
> > > As you may be able to tell from this, I am pretty inexperienced with SQL
> > > and it's maintenance procedures.
> > >
> > > Thanks in advance.
> > >
> > > John
> > >
>
| |
| John Morgan 2002-11-21, 10:24 am |
| See, told you I knew nothing about this stuff.
At least I know where to ask :-)
Tibor Karaszi wrote:
> > So it was just a reporting problem?
>
> Yep. And a quite common one... :-)
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "John Morgan" <"JMorg"@nospam@OAH.wa.gov> wrote in message news:evY3syOkCHA.2736@tkmsftngp10...
> > Ooops. Wrote too soon. I did a log dump and now I am showing the free space
> > as being 449MB. Cool. Also my database free space has gone from 338MB to
> > 1.5GB.
> >
> > So it was just a reporting problem?
> >
> > Which explains why the database was running OK, and not freaking out.
> >
> > This one's going in the books.
> >
> > Michael Abraham wrote:
> >
> > > As I recall, SQL 6.5 had issues with incorrect reporting of log free space.
> > > Check out KB article Q183100. Try DBCC CHECKTABLE(syslogs) - it will fix
> > > up the reporting of log free space. I used it many times in the olden
> > > days - it never failed to fix up the stats.
> > >
> > > Mike Abraham
> > > "John Morgan" <"JMorg"@nospam@OAH.wa.gov> wrote in message
> > > news:O4stl7KkCHA.2548@tkmsftngp11...
> > > > Hi there,
> > > >
> > > > I have a 450 MB transaction log which shows zero free space. What is
> > > > the best way to clear this out without causing any irreperable harm?
> > > >
> > > > As you may be able to tell from this, I am pretty inexperienced with SQL
> > > > and it's maintenance procedures.
> > > >
> > > > Thanks in advance.
> > > >
> > > > John
> > > >
> >
|
|
|
|
|