|
Home > Archive > MS SQL Server > June 2005 > transaction log growth -- too many active vlogs!
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 |
transaction log growth -- too many active vlogs!
|
|
| stephen.lee.moore@gmail.com 2005-06-15, 1:23 pm |
| Hello. I have a database that has an out-of-control transaction log.
If I run dbcc loginfo(db_name), I see over a hundred virtual logs with
a status of 2, and only a few that have a status 0 (and these aren't at
the end). If I run dbcc opentran(db_name), it tells me there are no
active open transactions in that database.
This particular database has data loaded in via DTS every night, which
should account for the majority of data in the log. There are also
relatively small number of inserts that happen on tables not loaded via
DTS. The transaction logs are backed up on a nightly basis.
How can I get the virtual logs to become inactive so I can shrink the
log file down?
| |
| Richard Ding 2005-06-15, 1:23 pm |
| You don't need to change the status from 2 to 0 in order to shrink the logs.
You do need to bring the virtual logs with status = 2 to the end of the
sequence.
<stephen.lee.moore@gmail.com> wrote in message
news:1118858110.370893.223920@g14g2000cwa.googlegroups.com...
> Hello. I have a database that has an out-of-control transaction log.
> If I run dbcc loginfo(db_name), I see over a hundred virtual logs with
> a status of 2, and only a few that have a status 0 (and these aren't at
> the end). If I run dbcc opentran(db_name), it tells me there are no
> active open transactions in that database.
>
> This particular database has data loaded in via DTS every night, which
> should account for the majority of data in the log. There are also
> relatively small number of inserts that happen on tables not loaded via
> DTS. The transaction logs are backed up on a nightly basis.
>
> How can I get the virtual logs to become inactive so I can shrink the
> log file down?
>
| |
| Tibor Karaszi 2005-06-15, 8:23 pm |
| Log is emptied (2 to 0) then you backup log. Backup log, check if 2 at the end. 2 at the end sets
limit of shrink. What you do is alternate backup log and shrink a few times. Then you alter database
and pre-allocate a much as you probably need.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
<stephen.lee.moore@gmail.com> wrote in message
news:1118858110.370893.223920@g14g2000cwa.googlegroups.com...
> Hello. I have a database that has an out-of-control transaction log.
> If I run dbcc loginfo(db_name), I see over a hundred virtual logs with
> a status of 2, and only a few that have a status 0 (and these aren't at
> the end). If I run dbcc opentran(db_name), it tells me there are no
> active open transactions in that database.
>
> This particular database has data loaded in via DTS every night, which
> should account for the majority of data in the log. There are also
> relatively small number of inserts that happen on tables not loaded via
> DTS. The transaction logs are backed up on a nightly basis.
>
> How can I get the virtual logs to become inactive so I can shrink the
> log file down?
>
| |
| Vishal Gandhi 2005-06-15, 8:23 pm |
| Hi Stephen ,
If you could provide the details like recovery model it will make more sense
, also you can take the backup log to free up VLF.
Cheers
Vishal
"stephen.lee.moore@gmail.com" wrote:
> Hello. I have a database that has an out-of-control transaction log.
> If I run dbcc loginfo(db_name), I see over a hundred virtual logs with
> a status of 2, and only a few that have a status 0 (and these aren't at
> the end). If I run dbcc opentran(db_name), it tells me there are no
> active open transactions in that database.
>
> This particular database has data loaded in via DTS every night, which
> should account for the majority of data in the log. There are also
> relatively small number of inserts that happen on tables not loaded via
> DTS. The transaction logs are backed up on a nightly basis.
>
> How can I get the virtual logs to become inactive so I can shrink the
> log file down?
>
>
| |
| Vishal Gandhi 2005-06-16, 3:23 am |
| Forgot to add after backup log you have to use dbcc shrink file
"Vishal Gandhi" wrote:
[color=darkred]
> Hi Stephen ,
>
> If you could provide the details like recovery model it will make more sense
> , also you can take the backup log to free up VLF.
>
> Cheers
> Vishal
>
> "stephen.lee.moore@gmail.com" wrote:
>
| |
| stephen.lee.moore@gmail.com 2005-06-17, 1:23 pm |
| The database is in full recovery mode. I've added a mid-day
transaction log backup and that seems to be setting most of the vlogs
to a status of 0, so hopefully they will be able to auto-shrink now. I
used the tlog backup / dbcc shrinkfile repeatedly and it got the log
size back down. Hopefully w/ the extra log backup it will stay that
way. Thanks for the help, everyone.
| |
| Tibor Karaszi 2005-06-17, 8:23 pm |
| Just make sure you do one big grow to the normal max size of the file. Continuous grow and shrink
hurt perf, fragments disk etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
<stephen.lee.moore@gmail.com> wrote in message
news:1119033043.487301.35130@o13g2000cwo.googlegroups.com...
> The database is in full recovery mode. I've added a mid-day
> transaction log backup and that seems to be setting most of the vlogs
> to a status of 0, so hopefully they will be able to auto-shrink now. I
> used the tlog backup / dbcc shrinkfile repeatedly and it got the log
> size back down. Hopefully w/ the extra log backup it will stay that
> way. Thanks for the help, everyone.
>
|
|
|
|
|