Errors on DBCC SHRINKFILE(2)

[Scarab]

Hi,
When I use dbcc shrinkfile to shrink LOG file, following error occurs:

DBCC SHRINKFILE(2)
----------------------------------------------------------------------------
-----------
Cannot shrink log file 2 (myDB_log) because all logical log files are in
use.
(1 row(s) affected)

I have only one transaction log file in my Database, who can tell me what't
the matter?

If my current log file is in use, how can I find who is using it and stop
using then do the shrink operation?

Thanks.

Scarab

[Dan Guzman]

The message means that all of there are open transactions in all of
*logical* log files. Each physical log file is made up of multiple virtual
log files and the physical log cannot be shrunk when there all of these
virtual logs are occupied with open transactions.

You can identify the oldest open transaction with:

USE MyDatabase
DBCC OPENTRAN
GO

You should be able to shrink once the transaction completes. See 'Shrinking
the transaction log' <architec.chm::/8_ar_da2_1uzr.htm> in the Books Online
for details.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Scarab" <[e-mail]> wrote in message
news:ds********@netnews.net.lucent.com...

Hi,
When I use dbcc shrinkfile to shrink LOG file, following error occurs:

DBCC SHRINKFILE(2)
----------------------------------------------------------------------------
-----------
Cannot shrink log file 2 (myDB_log) because all logical log files are in
use.
(1 row(s) affected)

I have only one transaction log file in my Database, who can tell me
what't
the matter?

If my current log file is in use, how can I find who is using it and stop
using then do the shrink operation?

Thanks.

Scarab

[Scarab]

Thanks for your reply, It is really a great help to me.
I think it is strange because after I restart SQL Server, this error still
exists, so I run
DBCC CHECKDB, there is no error messages, but after a while, the file can be
shrinked normally.
My question is: how can I view the virtual log files which make up a
physical log file? Once I find them, I
can kill the session and release the file.
Thank.

[Scarab]

"Scarab" <[e-mail]> wrote in message
news:ds********@netnews.net.lucent.com...

Hi,
When I use dbcc shrinkfile to shrink LOG file, following error occurs:

DBCC SHRINKFILE(2)
-------------------------------------------------------------------------- -- -----------
Cannot shrink log file 2 (myDB_log) because all logical log files are in
use.
(1 row(s) affected)

I have only one transaction log file in my Database, who can tell me what't the matter?

If my current log file is in use, how can I find who is using it and stop
using then do the shrink operation?

Thanks.

Scarab

Thanks for your reply, It is really a great help to me.
I think it is strange because after I restart SQL Server, this error still
exists, so I run
DBCC CHECKDB, there is no error messages, but after a while, the file can be
shrinked normally.
My question is: how can I view the virtual log files which make up a
physical log file? Once I find them, I
can kill the session and release the file.
Thank.

[Erland Sommarskog]

Scarab ([e-mail]) writes:

Thanks for your reply, It is really a great help to me.
I think it is strange because after I restart SQL Server, this error still
exists, so I run
DBCC CHECKDB, there is no error messages, but after a while, the file
can be shrinked normally.
My question is: how can I view the virtual log files which make up a
physical log file? Once I find them, I
can kill the session and release the file.

I don't know the answers to your questions, but maybe you should think
twice before you shrink at all. Have you read
http://www.karaszi.com/SQLServer/info_dont_shrink.asp?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Dan Guzman]

The undocumented DBCC LOGINFO can display virtual log stats.

Also, outstanding replicated transactions can prevent log
truncation/shrinking.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Scarab" <[e-mail]> wrote in message
news:ds********@netnews.net.lucent.com...

Thanks for your reply, It is really a great help to me.
I think it is strange because after I restart SQL Server, this error still
exists, so I run
DBCC CHECKDB, there is no error messages, but after a while, the file can
be
shrinked normally.
My question is: how can I view the virtual log files which make up a
physical log file? Once I find them, I
can kill the session and release the file.
Thank.