Cannot shrink transaction log of a SQL Server 2005 db

I have a SQL Server 2005 db for a Navision installation.
I have tried to execute DBCC Shrinkfile on the related transaction log after
the running of backup log. I have had this error:
"Cannot shrink log file 2 (...) because all logical log files are in use".
The transaction log is of 15 GB!
I know that it is possible to reduce the space of the log while the db is in
use.
How can I solve this issue?
Many thanksOpen transaction? Check using DBCC OPENTRAN.
Also, details on shrinking found at 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
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:BF7E5E86-D439-47DA-B469-68526A22A19F@.microsoft.com...
>I have a SQL Server 2005 db for a Navision installation.
> I have tried to execute DBCC Shrinkfile on the related transaction log after
> the running of backup log. I have had this error:
> "Cannot shrink log file 2 (...) because all logical log files are in use".
> The transaction log is of 15 GB!
> I know that it is possible to reduce the space of the log while the db is in
> use.
> How can I solve this issue?
> Many thanks
>|||I have executed DBCC OPENTRAN(MyDB) and
there aren't any active transactions.
The DBCC SQLPERF(LOGSPACE) return 4% used for the log.
"Tibor Karaszi" wrote:
> Open transaction? Check using DBCC OPENTRAN.
> Also, details on shrinking found at 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
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:BF7E5E86-D439-47DA-B469-68526A22A19F@.microsoft.com...
> >I have a SQL Server 2005 db for a Navision installation.
> > I have tried to execute DBCC Shrinkfile on the related transaction log after
> > the running of backup log. I have had this error:
> > "Cannot shrink log file 2 (...) because all logical log files are in use".
> > The transaction log is of 15 GB!
> > I know that it is possible to reduce the space of the log while the db is in
> > use.
> >
> > How can I solve this issue?
> >
> > Many thanks
> >
>|||I have executed a complete backup of the db after logging out of the users
from Navision. So I have executed first the backup log (copy and truncate)
and then the dbcc shrinkfile for three times.
I have obtained a log of 320 MB from 15 GB, but I have always the same error:
"Cannot shrink log file 2 (..._Log) because all logical log files are in use".
Do you have any suggests to understand this error?
Thanks
"Tibor Karaszi" wrote:
> Open transaction? Check using DBCC OPENTRAN.
> Also, details on shrinking found at 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
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:BF7E5E86-D439-47DA-B469-68526A22A19F@.microsoft.com...
> >I have a SQL Server 2005 db for a Navision installation.
> > I have tried to execute DBCC Shrinkfile on the related transaction log after
> > the running of backup log. I have had this error:
> > "Cannot shrink log file 2 (...) because all logical log files are in use".
> > The transaction log is of 15 GB!
> > I know that it is possible to reduce the space of the log while the db is in
> > use.
> >
> > How can I solve this issue?
> >
> > Many thanks
> >
>|||Did you investigate the virtual log file layout as suggested i my article?
Also, are you saying that you got it down to 320 MB, and want to shrink it further? If so, why? See
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:A3FEF114-AB29-41E5-A260-6B451C39706D@.microsoft.com...
>I have executed a complete backup of the db after logging out of the users
> from Navision. So I have executed first the backup log (copy and truncate)
> and then the dbcc shrinkfile for three times.
> I have obtained a log of 320 MB from 15 GB, but I have always the same error:
> "Cannot shrink log file 2 (..._Log) because all logical log files are in use".
> Do you have any suggests to understand this error?
> Thanks
>
> "Tibor Karaszi" wrote:
>> Open transaction? Check using DBCC OPENTRAN.
>> Also, details on shrinking found at 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
>>
>> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
>> news:BF7E5E86-D439-47DA-B469-68526A22A19F@.microsoft.com...
>> >I have a SQL Server 2005 db for a Navision installation.
>> > I have tried to execute DBCC Shrinkfile on the related transaction log after
>> > the running of backup log. I have had this error:
>> > "Cannot shrink log file 2 (...) because all logical log files are in use".
>> > The transaction log is of 15 GB!
>> > I know that it is possible to reduce the space of the log while the db is in
>> > use.
>> >
>> > How can I solve this issue?
>> >
>> > Many thanks
>> >
>>|||320 MB for the log is enough for me, absolutely!
I want to underline that I have still the error every time that I shrink the
log.
"Tibor Karaszi" wrote:
> Did you investigate the virtual log file layout as suggested i my article?
> Also, are you saying that you got it down to 320 MB, and want to shrink it further? If so, why? See
> http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:A3FEF114-AB29-41E5-A260-6B451C39706D@.microsoft.com...
> >I have executed a complete backup of the db after logging out of the users
> > from Navision. So I have executed first the backup log (copy and truncate)
> > and then the dbcc shrinkfile for three times.
> > I have obtained a log of 320 MB from 15 GB, but I have always the same error:
> > "Cannot shrink log file 2 (..._Log) because all logical log files are in use".
> >
> > Do you have any suggests to understand this error?
> >
> > Thanks
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Open transaction? Check using DBCC OPENTRAN.
> >>
> >> Also, details on shrinking found at 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
> >>
> >>
> >> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> >> news:BF7E5E86-D439-47DA-B469-68526A22A19F@.microsoft.com...
> >> >I have a SQL Server 2005 db for a Navision installation.
> >> > I have tried to execute DBCC Shrinkfile on the related transaction log after
> >> > the running of backup log. I have had this error:
> >> > "Cannot shrink log file 2 (...) because all logical log files are in use".
> >> > The transaction log is of 15 GB!
> >> > I know that it is possible to reduce the space of the log while the db is in
> >> > use.
> >> >
> >> > How can I solve this issue?
> >> >
> >> > Many thanks
> >> >
> >>
> >>
>|||As I stated twice before, the answer is in the virtual log file layout. Also, minimum number of
virtual log files is 2 or 4 (I don't remember).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:37F476C6-F5C1-4C15-8AD1-39ED6C5275D9@.microsoft.com...
> 320 MB for the log is enough for me, absolutely!
> I want to underline that I have still the error every time that I shrink the
> log.
>
> "Tibor Karaszi" wrote:
>> Did you investigate the virtual log file layout as suggested i my article?
>> Also, are you saying that you got it down to 320 MB, and want to shrink it further? If so, why?
>> See
>> http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
>> news:A3FEF114-AB29-41E5-A260-6B451C39706D@.microsoft.com...
>> >I have executed a complete backup of the db after logging out of the users
>> > from Navision. So I have executed first the backup log (copy and truncate)
>> > and then the dbcc shrinkfile for three times.
>> > I have obtained a log of 320 MB from 15 GB, but I have always the same error:
>> > "Cannot shrink log file 2 (..._Log) because all logical log files are in use".
>> >
>> > Do you have any suggests to understand this error?
>> >
>> > Thanks
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Open transaction? Check using DBCC OPENTRAN.
>> >>
>> >> Also, details on shrinking found at 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
>> >>
>> >>
>> >> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
>> >> news:BF7E5E86-D439-47DA-B469-68526A22A19F@.microsoft.com...
>> >> >I have a SQL Server 2005 db for a Navision installation.
>> >> > I have tried to execute DBCC Shrinkfile on the related transaction log after
>> >> > the running of backup log. I have had this error:
>> >> > "Cannot shrink log file 2 (...) because all logical log files are in use".
>> >> > The transaction log is of 15 GB!
>> >> > I know that it is possible to reduce the space of the log while the db is in
>> >> > use.
>> >> >
>> >> > How can I solve this issue?
>> >> >
>> >> > Many thanks
>> >> >
>> >>
>> >>
>>|||I'm not sure if this helps but, below is what I do to shrink and clean the
tlogs.
USE [mydb];
GO
BACKUP LOG [mydb] WITH TRUNCATE_ONLY;
GO
DBCC SHRINKFILE (mydb_Log, EMPTYFILE);
GO
DBCC SHRINKDATABASE('mydb')
GO
"Pasquale" wrote:
> I have a SQL Server 2005 db for a Navision installation.
> I have tried to execute DBCC Shrinkfile on the related transaction log after
> the running of backup log. I have had this error:
> "Cannot shrink log file 2 (...) because all logical log files are in use".
> The transaction log is of 15 GB!
> I know that it is possible to reduce the space of the log while the db is in
> use.
> How can I solve this issue?
> Many thanks
>