🚀 go-pugleaf

Try dbcc checktable(syslogs).  It may be that sysindexes are not reflecting
the true space available.

Glenn R. Kurban <gkurban@earthlink.net> wrote in article
<MPG.f9ef4a1f267a17989680@news.earthlink.net>...
> This is probably a fairly common problem since it has happened to me 
> before resulting in my having to restore a whole database (testing 
> database, luckily); however, I have never received a solution for it.
> 
> The transaction log in one of my development databases ran out of log 
> space completely (zero, zilch, nada...)  Upon truncating the log with 
> 'no_log,' I was left with only 20MB of a 148MB log for me to work with.  
> Nothing I try will free-up the rest of my transaction log. DBCC opentran 
> shows NO open transactions...I dont know what to do.  Please, any help 
> would be greatly appreciated.
> 
> Thanks,
> Glenn K. 
> 

Glenn,
After you tried everything - what did the DBCC checktable (syslogs)
statement return ?
Peder Holst
http://www.elektrosoft.com

Glenn R. Kurban wrote in message ...
>This is probably a fairly common problem since it has happened to me
>before resulting in my having to restore a whole database (testing
>database, luckily); however, I have never received a solution for it.
>
>The transaction log in one of my development databases ran out of log
>space completely (zero, zilch, nada...)  Upon truncating the log with
>'no_log,' I was left with only 20MB of a 148MB log for me to work with.
>Nothing I try will free-up the rest of my transaction log. DBCC opentran
>shows NO open transactions...I dont know what to do.  Please, any help
>would be greatly appreciated.
>
>Thanks,
>Glenn K.

I get these on warehouse DB's which see large (+800) Mbyte transactions for
loads.  These usually develop when a load crashes while the "bulk copy"
option is set.  My solution is to transfer the objects of the database to a
new patch, kill the database, rebuild it and refill it.  A simple dump and
restore recreates the jelly fish.  The DBCC does not work, or at least it
claims not to work, on databases larger than 400 Mbytes.

I think the bulk copy option disables the setting of the open transaction
flag in some misterious way.

Glenn R. Kurban <gkurban@earthlink.net> wrote in article
<MPG.f9ef4a1f267a17989680@news.earthlink.net>...
> This is probably a fairly common problem since it has happened to me 
> before resulting in my having to restore a whole database (testing 
> database, luckily); however, I have never received a solution for it.
> 
> The transaction log in one of my development databases ran out of log 
> space completely (zero, zilch, nada...)  Upon truncating the log with 
> 'no_log,' I was left with only 20MB of a 148MB log for me to work with.  
> Nothing I try will free-up the rest of my transaction log. DBCC opentran 
> shows NO open transactions...I dont know what to do.  Please, any help 
> would be greatly appreciated.
> 
> Thanks,
> Glenn K. 
> 

You don't mention which version of SQL you are using.  Try:

use <database>
go
dbcc checktable (syslogs)
go


Glenn R. Kurban wrote in message ...
>This is probably a fairly common problem since it has happened to me
>before resulting in my having to restore a whole database (testing
>database, luckily); however, I have never received a solution for it.
>
>The transaction log in one of my development databases ran out of log
>space completely (zero, zilch, nada...)  Upon truncating the log with
>'no_log,' I was left with only 20MB of a 148MB log for me to work with.
>Nothing I try will free-up the rest of my transaction log. DBCC opentran
>shows NO open transactions...I dont know what to do.  Please, any help
>would be greatly appreciated.
>
>Thanks,
>Glenn K.




Opinions expressed herein are my own and may not represent those of my employer.