Prev: How connect to a remote sql server behind vpn
Next: Script to stop running traces
From: NancyL on 26 Jan 2009 11:36
I have just found a server (out of 1800 I monitor) were the transaction log
has grown to over 90 gigs (db is only 8 gigs) and I am unable to shrink it
and the disk drive has only 5 gigs of free space left (I had to delete files
to free up that much space). The database is set to Simple recovery.
I have tried every method I could think of to shrink it, but it actually
grew from 82 g to 91 gigs.
There are no open transactions on the db.
After much research I did find that the log_reuse_wait_desc comes back as
=REPLICATION and log_reuse_wait is set to 6. There is no replication on this
box, and I don't think there ever was.
Is there any way I can set the log_reuse_wait(_desc) back to 0 (NOTHING)?
Any suggestions appreciate. I need to get this log file shrunk back up.

From: Kalen Delaney on 26 Jan 2009 11:55
Hi Nancy

PLEASE always tell us what version you are running on.

There were some bugs with replication not getting totally removed on SQL
2005, before SP1. Please make sure you are on the latest SP.

You should look for any other evidence of replication, like subscriptions or
publications and remove them. Also look up "Removing Replication" in the
Books Online, as well as the procedure sp_removedbreplication.

If that doesn't help, you can also do a web search for "SQL Server remove
replication sp_log_reuse_wait_desc". There is a lot of discussion out there.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com


"NancyL" <NancyL(a)discussions.microsoft.com> wrote in message
news:B6B0E186-C028-47F3-BB98-0913B4C2F228(a)microsoft.com...
>I have just found a server (out of 1800 I monitor) were the transaction log
> has grown to over 90 gigs (db is only 8 gigs) and I am unable to shrink it
> and the disk drive has only 5 gigs of free space left (I had to delete
> files
> to free up that much space). The database is set to Simple recovery.
> I have tried every method I could think of to shrink it, but it actually
> grew from 82 g to 91 gigs.
> There are no open transactions on the db.
> After much research I did find that the log_reuse_wait_desc comes back as
> =REPLICATION and log_reuse_wait is set to 6. There is no replication on
> this
> box, and I don't think there ever was.
> Is there any way I can set the log_reuse_wait(_desc) back to 0 (NOTHING)?
> Any suggestions appreciate. I need to get this log file shrunk back up.
>


 | 
Pages: 1
Prev: How connect to a remote sql server behind vpn
Next: Script to stop running traces