SQLServer Log Full / Disk Full / Backup-Shrink Not Working

16 Apr 2007 3:53 PM

gary

Hi All,

I am getting
"The transaction log for database 'MyDatabase' is full. To find out
why space in the log cannot be reused, see the log_reuse_wait_desc
column in sys.databases"
The referenced column is set to REPLICATION.

Looking at the database files, the problem is that the log has grown
to the point of the disk being full.

The majority of the posted solutions for this problem indicate doing
at
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
followed by shrinking the log file. However, this sequence is not
changing the size of the log file despite completing successfully.

One site suggested detaching the database, moving it to someplace with
more storage, and reattaching it. However, after waiting for Detach to
run a while, it eventually stopped, indicating it needed more disk
space to complete.

Most other commands that I attempt indicate there is no space in the
log file.

I understand that I need to look into the issues of why the log is
filling up and also potentially set the log to a maximum size now that
I understand this is a potential problem.

My question is... how can I reduce the size of my log file so that I
can get back to using my database (and fixing the problems)?

Thanks.
-Gary Geniesse

16 Apr 2007 4:54 PM

Greg D. Moore (Strider)

<g***@garygen.com> wrote in message
Show quoteHide quote

news:1176738787.775916.133730@n59g2000hsh.googlegroups.com...
> Hi All,
>
> I am getting
> "The transaction log for database 'MyDatabase' is full. To find out
> why space in the log cannot be reused, see the log_reuse_wait_desc
> column in sys.databases"
> The referenced column is set to REPLICATION.
>
> Looking at the database files, the problem is that the log has grown
> to the point of the disk being full.
>
> The majority of the posted solutions for this problem indicate doing
> at
> BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
> followed by shrinking the log file. However, this sequence is not
> changing the size of the log file despite completing successfully.
>
> One site suggested detaching the database, moving it to someplace with
> more storage, and reattaching it. However, after waiting for Detach to
> run a while, it eventually stopped, indicating it needed more disk
> space to complete.
>
> Most other commands that I attempt indicate there is no space in the
> log file.
>

Sounds like the database is enabled for replication, but the replication job
for some reason is hung/not running.

This is most likely why truncating the log file. (also shrinking the log
file itself is generally a bad idea.)

> I understand that I need to look into the issues of why the log is
> filling up and also potentially set the log to a maximum size now that
> I understand this is a potential problem.
>
> My question is... how can I reduce the size of my log file so that I
> can get back to using my database (and fixing the problems)?
>
> Thanks.
> -Gary Geniesse
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

16 Apr 2007 6:12 PM

gary

Hi Greg,

Thanks for the reply. That looks like it was the problem.

I had tried
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,
@time = 0, @reset = 1
but got the error "Unable to execute procedure. The database is not
published"

sp_dboption did not indicate the database was published and I had no
publishers (or subscriptions) defined.
Based on another posting, I tried adding a publication, running
sp_repldone, then removing the publication with
sp_dboption MyDatabase, published, false

This appears to have solved the problem as my log file immediately
dropped to practically nothing.

Thank you for your speedy assistance!
-Gary Geniesse

On Apr 16, 12:54 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet***@greenms.com> wrote:
Show quoteHide quote

> <g***@garygen.com> wrote in message
>
> news:1176738787.775916.133730@n59g2000hsh.googlegroups.com...
>
>
>
>
>
> > Hi All,
>
> > I am getting
> > "The transaction log for database 'MyDatabase' is full. To find out
> > why space in the log cannot be reused, see the log_reuse_wait_desc
> > column in sys.databases"
> > The referenced column is set to REPLICATION.
>
> > Looking at the database files, the problem is that the log has grown
> > to the point of the disk being full.
>
> > The majority of the posted solutions for this problem indicate doing
> > at
> > BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
> > followed by shrinking the log file. However, this sequence is not
> > changing the size of the log file despite completing successfully.
>
> > One site suggested detaching the database, moving it to someplace with
> > more storage, and reattaching it. However, after waiting for Detach to
> > run a while, it eventually stopped, indicating it needed more disk
> > space to complete.
>
> > Most other commands that I attempt indicate there is no space in the
> > log file.
>
> Sounds like the database is enabled for replication, but the replication job
> for some reason is hung/not running.
>
> This is most likely why truncating the log file. (also shrinking the log
> file itself is generally a bad idea.)
>
> > I understand that I need to look into the issues of why the log is
> > filling up and also potentially set the log to a maximum size now that
> > I understand this is a potential problem.
>
> > My question is... how can I reduce the size of my log file so that I
> > can get back to using my database (and fixing the problems)?
>
> > Thanks.
> > -Gary Geniesse
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html- Hide quoted text -
>
> - Show quoted text -

Thread options