Cannot restore database: shrinkfile

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
>

Cannot shrink transaction log of a SQL Server 2005 db

Cannot shrink transaction log

Hi,

I have a t-log on one database which is 400mb when the database size is only 30mb. dbcc shrinkfile does not work and dbcc opentran shows no open transactions. When I tried to do a Backup log with truncate_only the following message displays:

The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.

The database in question is not a publisher and only receives 5 subscribed articles, none of which are changed very often. The database operates 24 x 7.

Does anyone have a solution to this dilemma?

Thank YouHave you enabled the transaction checkpoint ? so that it will reduce automatically ?

dave|||Run this script (courtesy of SQL Server Professional magazine)

Sometimes a piece of data blocks the rest of the transaction log from being truncated. This script will solve your problem by created and removing garbage data until the active portion of the log loops to the beginning of the t'log.

Friday, February 24, 2012

Cannot shrink transaction log of a SQL Server 2005 db

Cannot shrink transaction log of a SQL Server 2005 db

Cannot shrink transaction log of a SQL Server 2005 db

Cannot shrink transaction log

Cannot restore database

Blog Archive

About Me