Hi!


A log file for one of my databases is very big (over 100GB). This is
very unusual for this database - most of the time it is under 10GB.
I backup database and tried to shrink the log file - didn't work.
I backuped log file with Truncate_Only (didn't need sequence to be
preserved) and than ran dbcc shrink file again - didn't work. Log
stayed the same size.
What else can I do?

This is for SQL 2000 SP4.

T.

What does DBCC OPENTRAN tell you?


On 6/16/08 12:41 PM, in article
0e42a803-fee3-4017-a591-7b45ea6af049...oglegroups.com, "tolcis"
<nytollydba@gmail.com> wrote:

> Hi!
>
>
> A log file for one of my databases is very big (over 100GB). This is
> very unusual for this database - most of the time it is under 10GB.
> I backup database and tried to shrink the log file - didn't work.
> I backuped log file with Truncate_Only (didn't need sequence to be
> preserved) and than ran dbcc shrink file again - didn't work. Log
> stayed the same size.
> What else can I do?
>
> This is for SQL 2000 SP4.
>
> T.

On Jun 16, 11:41am, tolcis <nytolly...@gmail.com> wrote:
> Hi!
>
> A log file for one of my databases is very big (over 100GB). This is
> very unusual for this database - most of the time it is under 10GB.
> I backup database and tried to shrink the log file - didn't work.
> I backuped log file with Truncate_Only (didn't need sequence to be
> preserved) and than ran dbcc shrink file again - didn't work. Log
> stayed the same size.
> What else can I do?
>
> This is for SQL 2000 SP4.
>
> T.

As Aaron suggested, run DBCC OPENTRAN - is there a transaction stuck
open? Is the database being replicated, but the logreader service is
stopped? Something is preventing transactions from being flushed from
the log.

On Jun 16, 1:22 pm, "Tracy McKibben (http://www.realsqlguy.com)"
<tracy.mckib...@gmail.com> wrote:
> On Jun 16, 11:41 am, tolcis <nytolly...@gmail.com> wrote:
>
> > Hi!
>
> > A log file for one of my databases is very big (over 100GB). This is
> > very unusual for this database - most of the time it is under 10GB.
> > I backup database and tried to shrink the log file - didn't work.
> > I backuped log file with Truncate_Only (didn't need sequence to be
> > preserved) and than ran dbcc shrink file again - didn't work. Log
> > stayed the same size.
> > What else can I do?
>
> > This is for SQL 2000 SP4.
>
> > T.
>
> As Aaron suggested, run DBCC OPENTRAN - is there a transaction stuck
> open? Is the database being replicated, but the logreader service is
> stopped? Something is preventing transactions from being flushed from
> the log.

I have the same issue on a DEV datawarehouse DB. No replication, and
dbcc opentran shows
No active open transactions.

David

On Jun 16, 12:49pm, David Hay <david....@gmail.com> wrote:
> On Jun 16, 1:22 pm, "Tracy McKibben (http://www.realsqlguy.com)"
>
>
>
> <tracy.mckib...@gmail.com> wrote:
> > On Jun 16, 11:41 am, tolcis <nytolly...@gmail.com> wrote:
>
> > > Hi!
>
> > > A log file for one of my databases is very big (over 100GB). This is
> > > very unusual for this database - most of the time it is under 10GB.
> > > I backup database and tried to shrink the log file - didn't work.
> > > I backuped log file with Truncate_Only (didn't need sequence to be
> > > preserved) and than ran dbcc shrink file again - didn't work. Log
> > > stayed the same size.
> > > What else can I do?
>
> > > This is for SQL 2000 SP4.
>
> > > T.
>
> > As Aaron suggested, run DBCC OPENTRAN - is there a transaction stuck
> > open? Is the database being replicated, but the logreader service is
> > stopped? Something is preventing transactions from being flushed from
> > the log.
>
> I have the same issue on a DEV datawarehouse DB. No replication, and
> dbcc opentran shows
> No active open transactions.
>
> David

No replication, no active open transactions - have you performed a
backup of the log file? How much free space is reported in the log
file?

>> I have the same issue on a DEV datawarehouse DB. No replication, and
>> dbcc opentran shows
>> No active open transactions.
>>
>> David
>
> No replication, no active open transactions - have you performed a
> backup of the log file? How much free space is reported in the log
> file?

And are you sure you are in the correct database when running DBCC OPENTRAN.
A common mistake is running it in master or tempdb, or the wrong user
database, by accident.

On Jun 16, 2:02 pm, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:
> >> I have the same issue on a DEV datawarehouse DB. No replication, and
> >> dbcc opentran shows
> >> No active open transactions.
>
> >> David
>
> > No replication, no active open transactions - have you performed a
> > backup of the log file? How much free space is reported in the log
> > file?
>
> And are you sure you are in the correct database when running DBCC OPENTRAN.
> A common mistake is running it in master or tempdb, or the wrong user
> database, by accident.

positive. I slap the other DBA's when they open QA and create stuff
in master.

Next step would be to investigate the virtual log file layout of your log. You'll find some detail
on how to do this in http://www.karaszi.com/SQLServer/info_dont_shrink.asp.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"David Hay" <david.hay@gmail.com> wrote in message
news:3206b17a-ffe8-4863-b8e0-61faec365d0a@l42g2000hsc.googlegroups.com...
> On Jun 16, 2:02 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten....@dnartreb.noraa> wrote:
>> >> I have the same issue on a DEV datawarehouse DB. No replication, and
>> >> dbcc opentran shows
>> >> No active open transactions.
>>
>> >> David
>>
>> > No replication, no active open transactions - have you performed a
>> > backup of the log file? How much free space is reported in the log
>> > file?
>>
>> And are you sure you are in the correct database when running DBCC OPENTRAN.
>> A common mistake is running it in master or tempdb, or the wrong user
>> database, by accident.
>
> positive. I slap the other DBA's when they open QA and create stuff
> in master.
>