Friday, March 9, 2012

Ever increasing transaction log size

Hi guys
I have a database thata have a roughly 1 Gb datafile.
The issue I seem to be facing is the fact that the transaction log keeps
growing.
Currently the transaction log is just short of 5 Gb however the backup of
the transaction log is always below 1 Gb. Every morning when I come in I see
the same pattern the transaction log size is increased by a few Mb however
the backup file of the log is only around 900 Mb.
Can anyone explain to me how and why the log file keeps growing?
Regards
JonasHave you done a FULL backup of the database lately? What about open
transactions? Run DBCC OPENTRAN() to see if any long running trans are
hanging around.
Andrew J. Kelly SQL MVP
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:Ox3QD06uEHA.1372@.TK2MSFTNGP14.phx.gbl...
> Hi guys
> I have a database thata have a roughly 1 Gb datafile.
> The issue I seem to be facing is the fact that the transaction log keeps
> growing.
> Currently the transaction log is just short of 5 Gb however the backup of
> the transaction log is always below 1 Gb. Every morning when I come in I
> see
> the same pattern the transaction log size is increased by a few Mb however
> the backup file of the log is only around 900 Mb.
> Can anyone explain to me how and why the log file keeps growing?
> Regards
> Jonas
>|||You might try running a dbcc opentran against the problem database to see if
there's an old
transaction that's 'hung' open.
See the BOL index on dbcc opentran for full syntax...
You are doing full db dumps periodically in conjunction with your log dumps
too?
-- bbott
-- SQL Server MVP
"Jonas Larsen" wrote:

> Hi guys
> I have a database thata have a roughly 1 Gb datafile.
> The issue I seem to be facing is the fact that the transaction log keeps
> growing.
> Currently the transaction log is just short of 5 Gb however the backup of
> the transaction log is always below 1 Gb. Every morning when I come in I s
ee
> the same pattern the transaction log size is increased by a few Mb however
> the backup file of the log is only around 900 Mb.
> Can anyone explain to me how and why the log file keeps growing?
> Regards
> Jonas
>
>|||In regards to backup:
From 6 am to 8 pm backup of the transaction log every 2nd hours.
Full backup @. 10 pm.
(The database is only inuse between 6 am and 8 pm)
I have now tried dbcc opentran and nothing came up.
Regards
Jonas
"Bruce Bott" <BruceBott@.discussions.microsoft.com> wrote in message
news:53017A8E-1EA1-42B3-AF84-C56D22EF2109@.microsoft.com...
> You might try running a dbcc opentran against the problem database to see
if
> there's an old
> transaction that's 'hung' open.
> See the BOL index on dbcc opentran for full syntax...
> You are doing full db dumps periodically in conjunction with your log
dumps[vbcol=seagreen]
> too?
> -- bbott
> -- SQL Server MVP
> "Jonas Larsen" wrote:
>
of[vbcol=seagreen]
see[vbcol=seagreen]
however[vbcol=seagreen]|||Do you have any scheduled jobs that may be updating/deleting records in the
database after the full backup occurs? Our Great Plains databases have very
small transaction logs during the day, then, after hours, we do a massive
update to the customer information which swells the transaction log to over
500MB.
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:et0wzk7uEHA.908@.TK2MSFTNGP11.phx.gbl...
> In regards to backup:
> From 6 am to 8 pm backup of the transaction log every 2nd hours.
> Full backup @. 10 pm.
> (The database is only inuse between 6 am and 8 pm)
> I have now tried dbcc opentran and nothing came up.
> Regards
> Jonas
> "Bruce Bott" <BruceBott@.discussions.microsoft.com> wrote in message
> news:53017A8E-1EA1-42B3-AF84-C56D22EF2109@.microsoft.com...
> if
> dumps
> of
> see
> however
>|||Yes we do. These account for the tansaction log backup size to be app 900 Mb
however this does not justify a 5 Gb transaction log file to grow (I would
think).
The thing I dont understand is the fact that the backup of the log file in
the morning is around 900Mb however the log file still grows despite the
size being 5 times that amount.
Jonas
"Morgan" <abcd@.senditon.com> wrote in message
news:ePQByz7uEHA.1988@.TK2MSFTNGP12.phx.gbl...
> Do you have any scheduled jobs that may be updating/deleting records in
the
> database after the full backup occurs? Our Great Plains databases have
very
> small transaction logs during the day, then, after hours, we do a massive
> update to the customer information which swells the transaction log to
over
> 500MB.
>
> "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> news:et0wzk7uEHA.908@.TK2MSFTNGP11.phx.gbl...
see[vbcol=seagreen]
backup[vbcol=seagreen]
in[vbcol=seagreen]
>|||Where you specifying the correct DB with OPENTRAN()? What does the output
of DBCC LOGINFO show? A status of 2 in any of the VLF's basically indicate
these are part of an active transaction. Normally there would be only one or
two with a status of 2.
Andrew J. Kelly SQL MVP
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:et0wzk7uEHA.908@.TK2MSFTNGP11.phx.gbl...
> In regards to backup:
> From 6 am to 8 pm backup of the transaction log every 2nd hours.
> Full backup @. 10 pm.
> (The database is only inuse between 6 am and 8 pm)
> I have now tried dbcc opentran and nothing came up.
> Regards
> Jonas
> "Bruce Bott" <BruceBott@.discussions.microsoft.com> wrote in message
> news:53017A8E-1EA1-42B3-AF84-C56D22EF2109@.microsoft.com...
> if
> dumps
> of
> see
> however
>|||Below is the output from the DBCC OPENTRAN ('DATABASENAME').
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Jonas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ODjSW08uEHA.1524@.TK2MSFTNGP09.phx.gbl...
> Where you specifying the correct DB with OPENTRAN()? What does the output
> of DBCC LOGINFO show? A status of 2 in any of the VLF's basically
indicate
> these are part of an active transaction. Normally there would be only one
or
> two with a status of 2.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> news:et0wzk7uEHA.908@.TK2MSFTNGP11.phx.gbl...
see[vbcol=seagreen]
backup[vbcol=seagreen]
in[vbcol=seagreen]
>|||And DBCC LOGINFO?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:ujzOoQ9uEHA.3840@.TK2MSFTNGP12.phx.gbl...
> Below is the output from the DBCC OPENTRAN ('DATABASENAME').
> No active open transactions.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Jonas
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ODjSW08uEHA.1524@.TK2MSFTNGP09.phx.gbl...
> indicate
> or
> see
> backup
> in
>|||Gives me about 300 record. I tried to look up DBCC LOGINFO but could not
find info about it?
How do I intrepid the information the statement returns?
Regards
Jonas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OsNJ5Z%23uEHA.1296@.TK2MSFTNGP10.phx.gbl...
> And DBCC LOGINFO?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> news:ujzOoQ9uEHA.3840@.TK2MSFTNGP12.phx.gbl...
output[vbcol=seagreen]
one[vbcol=seagreen]
to[vbcol=seagreen]
log[vbcol=seagreen]
come[vbcol=seagreen]
>

No comments:

Post a Comment