Hi!
I created Full Backup of database and transaction logs in Enterprise manager
.. Restore of the database worked fine, but when i'm trying to apply transact
ion logs i get the followin error:
code:Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 7000000063500001, which is too
early to apply to the database. A more recent log backup that includes LSN 7
000000063700001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Why is the possible cause of this problem?
Thank you.
Seems like you try to apply an incorrect transaction log backup. If you can reproduce this using
TSQL code (BACKUP and RESTORE commands) against a test database, we could test and see what the
problem might be.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Francelj Smuk" <Francelj.Smuk@.email.si> wrote in message
news:pan.2004.09.08.08.32.08.781516@.email.si...
> Hi!
> I created Full Backup of database and transaction logs in Enterprise manager
> . Restore of the database worked fine, but when i'm trying to apply transact
> ion logs i get the followin error:
>
> code:Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 7000000063500001, which is too
> early to apply to the database. A more recent log backup that includes LSN 7
> 000000063700001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
>
> Why is the possible cause of this problem?
> Thank you.
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Sunday, March 25, 2012
Can't apply transaction logs after restoring a database in enterprise manager
Hi!
I created Full Backup of database and transaction logs in Enterprise manager
. Restore of the database worked fine, but when i'm trying to apply transact
ion logs i get the followin error:
code:Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 7000000063500001, which is too
early to apply to the database. A more recent log backup that includes LSN 7
000000063700001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Why is the possible cause of this problem?
Thank you.Seems like you try to apply an incorrect transaction log backup. If you can reproduce this using
TSQL code (BACKUP and RESTORE commands) against a test database, we could test and see what the
problem might be.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Francelj Smuk" <Francelj.Smuk@.email.si> wrote in message
news:pan.2004.09.08.08.32.08.781516@.email.si...
> Hi!
> I created Full Backup of database and transaction logs in Enterprise manager
> . Restore of the database worked fine, but when i'm trying to apply transact
> ion logs i get the followin error:
>
> code:Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 7000000063500001, which is too
> early to apply to the database. A more recent log backup that includes LSN 7
> 000000063700001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
>
> Why is the possible cause of this problem?
> Thank you.
I created Full Backup of database and transaction logs in Enterprise manager
. Restore of the database worked fine, but when i'm trying to apply transact
ion logs i get the followin error:
code:Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 7000000063500001, which is too
early to apply to the database. A more recent log backup that includes LSN 7
000000063700001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Why is the possible cause of this problem?
Thank you.Seems like you try to apply an incorrect transaction log backup. If you can reproduce this using
TSQL code (BACKUP and RESTORE commands) against a test database, we could test and see what the
problem might be.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Francelj Smuk" <Francelj.Smuk@.email.si> wrote in message
news:pan.2004.09.08.08.32.08.781516@.email.si...
> Hi!
> I created Full Backup of database and transaction logs in Enterprise manager
> . Restore of the database worked fine, but when i'm trying to apply transact
> ion logs i get the followin error:
>
> code:Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 7000000063500001, which is too
> early to apply to the database. A more recent log backup that includes LSN 7
> 000000063700001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
>
> Why is the possible cause of this problem?
> Thank you.
Thursday, March 8, 2012
Cannot Update Identity Column 'xxxx'
Hi,
I have transaction replication setup and the initial snap shot works fine. However after that updates are giving me the above error. Any help would be appreciated. Thanks.
Jeff
Jeff,
this error is not specific to replication - updates of identity columns are
prohibited in all cases, even when the table isn't published (there's a set
IDENTITY_INSERT on but no set IDENTITY_UPDATE on).
HTH,
Paul Ibison
|||Look at the table on the subscriber and verify that the identity column on
the table has the NOT FOR REPLICATION option set. Based on the error, I
would say it does not. Turn this on and see if that resolves the problem.
Rand
This posting is provided "as is" with no warranties and confers no rights.
I have transaction replication setup and the initial snap shot works fine. However after that updates are giving me the above error. Any help would be appreciated. Thanks.
Jeff
Jeff,
this error is not specific to replication - updates of identity columns are
prohibited in all cases, even when the table isn't published (there's a set
IDENTITY_INSERT on but no set IDENTITY_UPDATE on).
HTH,
Paul Ibison
|||Look at the table on the subscriber and verify that the identity column on
the table has the NOT FOR REPLICATION option set. Based on the error, I
would say it does not. Turn this on and see if that resolves the problem.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Cannot truncate transaction log.
DB Newbie question ahead.
I have a mere 100MB db with a 4GB transaction log. I want to truncate the log as I understand that truncating it will shrink the log by removing the transactions that have already taken place. However, the option to do a transaction backup is greyed out. I suspect this is from the db being in transactional replication with another server; however, I don't know for sure.
Are there any other ways that I can shrink the transaction log? I would like to do shrink it without taking the db offline either.I think the recovery model was set to simple, that's why you can't take log backup. You can use DBCC Shrinkfile to shrink your log file to the size you want.|||Are you using SQL 2000?
If so you can do that by the following
- Go to DB properties and change the backup type to FULL
- Take a backup of the database and keep it safe.
- Go to DB properties again change the backup type to SIMPLE
- Go to all tasks and Shrink the file
- Go to DB properties again and change the backup type to FULL again so that it logs everything.|||Yes, I am using SQL2000. That's important information ;)
SQL2000 on win2003.
smasanam: I tried that and it didn't shrink the log file. Still 4.5GB.
Is the log file important for transactional replication? This db is replicated nightly to another server. If everything is updated and fully bkup'd would the log file be necessary? Or could I just delete it?
Thanks for the help.
I have a mere 100MB db with a 4GB transaction log. I want to truncate the log as I understand that truncating it will shrink the log by removing the transactions that have already taken place. However, the option to do a transaction backup is greyed out. I suspect this is from the db being in transactional replication with another server; however, I don't know for sure.
Are there any other ways that I can shrink the transaction log? I would like to do shrink it without taking the db offline either.I think the recovery model was set to simple, that's why you can't take log backup. You can use DBCC Shrinkfile to shrink your log file to the size you want.|||Are you using SQL 2000?
If so you can do that by the following
- Go to DB properties and change the backup type to FULL
- Take a backup of the database and keep it safe.
- Go to DB properties again change the backup type to SIMPLE
- Go to all tasks and Shrink the file
- Go to DB properties again and change the backup type to FULL again so that it logs everything.|||Yes, I am using SQL2000. That's important information ;)
SQL2000 on win2003.
smasanam: I tried that and it didn't shrink the log file. Still 4.5GB.
Is the log file important for transactional replication? This db is replicated nightly to another server. If everything is updated and fully bkup'd would the log file be necessary? Or could I just delete it?
Thanks for the help.
Cannot Truncate Transaction Log!
My Transaction Log on a production database has grown to 5 GB. And
all but 118 MB is used in that log. DBCC Opentran shows the
following:
Replicated Transaction Information:
Oldest distributed LSN : (10576:2240:4)
Oldest non-distributed LSN : (0:0:0)
Replication is not set up on that server! There are no agents running
on it, there is no Distribution database. But it looks like something
is flagged for replication. I know a very large DTS was executed on
the database and maybe something got mixed up, and now the database
thinks it has to replicate this giant transaction.
I am shipping the logs of this database from MI to VA, and I really
don't want to break log shipping by fixing this problem. I may have
to anyway. How can I get this transaction to get cleaned out' I
can't execute sp_repldone, because no replication agents are running.
Help!!!!!!Try these links :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH
Ryan Waight, MCDBA, MCSE
"Laura Jones" <jonesl@.ofllc.com> wrote in message
news:978604d4.0403040636.446e8010@.posting.google.com...
> My Transaction Log on a production database has grown to 5 GB. And
> all but 118 MB is used in that log. DBCC Opentran shows the
> following:
> Replicated Transaction Information:
> Oldest distributed LSN : (10576:2240:4)
> Oldest non-distributed LSN : (0:0:0)
>
> Replication is not set up on that server! There are no agents running
> on it, there is no Distribution database. But it looks like something
> is flagged for replication. I know a very large DTS was executed on
> the database and maybe something got mixed up, and now the database
> thinks it has to replicate this giant transaction.
> I am shipping the logs of this database from MI to VA, and I really
> don't want to break log shipping by fixing this problem. I may have
> to anyway. How can I get this transaction to get cleaned out' I
> can't execute sp_repldone, because no replication agents are running.
> Help!!!!!!
all but 118 MB is used in that log. DBCC Opentran shows the
following:
Replicated Transaction Information:
Oldest distributed LSN : (10576:2240:4)
Oldest non-distributed LSN : (0:0:0)
Replication is not set up on that server! There are no agents running
on it, there is no Distribution database. But it looks like something
is flagged for replication. I know a very large DTS was executed on
the database and maybe something got mixed up, and now the database
thinks it has to replicate this giant transaction.
I am shipping the logs of this database from MI to VA, and I really
don't want to break log shipping by fixing this problem. I may have
to anyway. How can I get this transaction to get cleaned out' I
can't execute sp_repldone, because no replication agents are running.
Help!!!!!!Try these links :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH
Ryan Waight, MCDBA, MCSE
"Laura Jones" <jonesl@.ofllc.com> wrote in message
news:978604d4.0403040636.446e8010@.posting.google.com...
> My Transaction Log on a production database has grown to 5 GB. And
> all but 118 MB is used in that log. DBCC Opentran shows the
> following:
> Replicated Transaction Information:
> Oldest distributed LSN : (10576:2240:4)
> Oldest non-distributed LSN : (0:0:0)
>
> Replication is not set up on that server! There are no agents running
> on it, there is no Distribution database. But it looks like something
> is flagged for replication. I know a very large DTS was executed on
> the database and maybe something got mixed up, and now the database
> thinks it has to replicate this giant transaction.
> I am shipping the logs of this database from MI to VA, and I really
> don't want to break log shipping by fixing this problem. I may have
> to anyway. How can I get this transaction to get cleaned out' I
> can't execute sp_repldone, because no replication agents are running.
> Help!!!!!!
Labels:
database,
dbcc,
following,
grown,
log,
microsoft,
mysql,
opentran,
oracle,
production,
replicated,
server,
sql,
transaction,
truncate
Cannot Truncate Transaction Log!
My Transaction Log on a production database has grown to 5 GB. And
all but 118 MB is used in that log. DBCC Opentran shows the
following:
Replicated Transaction Information:
Oldest distributed LSN : (10576:2240:4)
Oldest non-distributed LSN : (0:0:0)
Replication is not set up on that server! There are no agents running
on it, there is no Distribution database. But it looks like something
is flagged for replication. I know a very large DTS was executed on
the database and maybe something got mixed up, and now the database
thinks it has to replicate this giant transaction.
I am shipping the logs of this database from MI to VA, and I really
don't want to break log shipping by fixing this problem. I may have
to anyway. How can I get this transaction to get cleaned out' I
can't execute sp_repldone, because no replication agents are running.
Help!!!!!!Try these links :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH
Ryan Waight, MCDBA, MCSE
"Laura Jones" <jonesl@.ofllc.com> wrote in message
news:978604d4.0403040636.446e8010@.posting.google.com...
> My Transaction Log on a production database has grown to 5 GB. And
> all but 118 MB is used in that log. DBCC Opentran shows the
> following:
> Replicated Transaction Information:
> Oldest distributed LSN : (10576:2240:4)
> Oldest non-distributed LSN : (0:0:0)
>
> Replication is not set up on that server! There are no agents running
> on it, there is no Distribution database. But it looks like something
> is flagged for replication. I know a very large DTS was executed on
> the database and maybe something got mixed up, and now the database
> thinks it has to replicate this giant transaction.
> I am shipping the logs of this database from MI to VA, and I really
> don't want to break log shipping by fixing this problem. I may have
> to anyway. How can I get this transaction to get cleaned out' I
> can't execute sp_repldone, because no replication agents are running.
> Help!!!!!!|||Unfortunately, the log cannot be shrunk, because shrinking only clears
up free space on the log, and what I have is something that is showing
up as used space. This makes it impossible to shrink, until I've
unflagged this transaction.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Laura
Run DBCC Loginfo(db_id)
There is a column is called 'status' .If you see the value=2 of this column
at the end ,so you have running(active) transaction and therefore you will
not able to shrink the log.
Run dummy inserts to move the status=2 on the top of the table.
"Laura Jones" <jonesl@.ofllc.com> wrote in message
news:uo$gJGgAEHA.1028@.TK2MSFTNGP11.phx.gbl...
> Unfortunately, the log cannot be shrunk, because shrinking only clears
> up free space on the log, and what I have is something that is showing
> up as used space. This makes it impossible to shrink, until I've
> unflagged this transaction.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
all but 118 MB is used in that log. DBCC Opentran shows the
following:
Replicated Transaction Information:
Oldest distributed LSN : (10576:2240:4)
Oldest non-distributed LSN : (0:0:0)
Replication is not set up on that server! There are no agents running
on it, there is no Distribution database. But it looks like something
is flagged for replication. I know a very large DTS was executed on
the database and maybe something got mixed up, and now the database
thinks it has to replicate this giant transaction.
I am shipping the logs of this database from MI to VA, and I really
don't want to break log shipping by fixing this problem. I may have
to anyway. How can I get this transaction to get cleaned out' I
can't execute sp_repldone, because no replication agents are running.
Help!!!!!!Try these links :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH
Ryan Waight, MCDBA, MCSE
"Laura Jones" <jonesl@.ofllc.com> wrote in message
news:978604d4.0403040636.446e8010@.posting.google.com...
> My Transaction Log on a production database has grown to 5 GB. And
> all but 118 MB is used in that log. DBCC Opentran shows the
> following:
> Replicated Transaction Information:
> Oldest distributed LSN : (10576:2240:4)
> Oldest non-distributed LSN : (0:0:0)
>
> Replication is not set up on that server! There are no agents running
> on it, there is no Distribution database. But it looks like something
> is flagged for replication. I know a very large DTS was executed on
> the database and maybe something got mixed up, and now the database
> thinks it has to replicate this giant transaction.
> I am shipping the logs of this database from MI to VA, and I really
> don't want to break log shipping by fixing this problem. I may have
> to anyway. How can I get this transaction to get cleaned out' I
> can't execute sp_repldone, because no replication agents are running.
> Help!!!!!!|||Unfortunately, the log cannot be shrunk, because shrinking only clears
up free space on the log, and what I have is something that is showing
up as used space. This makes it impossible to shrink, until I've
unflagged this transaction.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Laura
Run DBCC Loginfo(db_id)
There is a column is called 'status' .If you see the value=2 of this column
at the end ,so you have running(active) transaction and therefore you will
not able to shrink the log.
Run dummy inserts to move the status=2 on the top of the table.
"Laura Jones" <jonesl@.ofllc.com> wrote in message
news:uo$gJGgAEHA.1028@.TK2MSFTNGP11.phx.gbl...
> Unfortunately, the log cannot be shrunk, because shrinking only clears
> up free space on the log, and what I have is something that is showing
> up as used space. This makes it impossible to shrink, until I've
> unflagged this transaction.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
Labels:
andall,
database,
dbcc,
grown,
log,
microsoft,
mysql,
opentran,
oracle,
production,
server,
sql,
thefollowingreplicated,
transaction,
truncate
Wednesday, March 7, 2012
'Cannot start transaction while in firehose mode'
I'm getting this error message in one of my procedures... Has anyone ever
seen this, I've searched the group's most messages I found mention a firehos
e
cursor which I know I'm not using... Here's the procedure thats generating
the error, this is the first time I've seen this error the application has
been working fine in production for months. I think this may have to do with
the fact that I'm using a linked server and I get an implied transaction whe
n
I use it, can I turn this off...?
Here's was the whole error message.
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Cannot start transaction while in
firehose mode]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x80004005: ].
CREATE PROCEDURE [dbo].[esp_csla_SalesPerson_Create]
@.SalesPersonID int = NULL OUTPUT,
@.VendorCode CHAR(10),
@.VendorGroup CHAR(10)
AS
BEGIN
DECLARE @.SSN AS CHAR(11),
@.Name AS CHAR(50),
@.FirstName AS VARCHAR(50),
@.LastName AS VARCHAR(50),
@.Index AS INT
SELECT
@.SSN = ven_tax_id,
@.Name = ven_name
FROM
LinkedServer.database.dbo.databasetable
WHERE
ven_cd = @.VendorCode AND
ven_grp_cd = @.VendorGroup
INSERT INTO SalesPersons (VendorCode,VendorGroup,SSN,FirstName,La
stName)
VALUES (@.VendorCode,@.VendorGroup,@.SSN, @.FirstName,@.LastName)
SET @.SalesPersonID = @.@.IDENTITY
END
GOAlien2_51 wrote:
> I'm getting this error message in one of my procedures... Has anyone
> ever seen this, I've searched the group's most messages I found
> mention a firehose cursor which I know I'm not using... Here's the
> procedure thats generating the error, this is the first time I've
> seen this error the application has been working fine in production
> for months. I think this may have to do with the fact that I'm using
> a linked server and I get an implied transaction when I use it, can I
> turn this off...?
> Here's was the whole error message.
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Cannot start transaction while in
> firehose mode]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x80004005: ].
>
> CREATE PROCEDURE [dbo].[esp_csla_SalesPerson_Create]
> @.SalesPersonID int = NULL OUTPUT,
> @.VendorCode CHAR(10),
> @.VendorGroup CHAR(10)
> AS
> BEGIN
> DECLARE @.SSN AS CHAR(11),
> @.Name AS CHAR(50),
> @.FirstName AS VARCHAR(50),
> @.LastName AS VARCHAR(50),
> @.Index AS INT
> SELECT
> @.SSN = ven_tax_id,
> @.Name = ven_name
> FROM
> LinkedServer.database.dbo.databasetable
> WHERE
> ven_cd = @.VendorCode AND
> ven_grp_cd = @.VendorGroup
> INSERT INTO SalesPersons
> (VendorCode,VendorGroup,SSN,FirstName,La
stName) VALUES
> (@.VendorCode,@.VendorGroup,@.SSN, @.FirstName,@.LastName)
> SET @.SalesPersonID = @.@.IDENTITY
> END
> GO
What type of cursor are you using if not a firehose (Server / read-only
/ forward-only)?
Maybe you could try wrapping the linked server call in a transaction? Is
that the whole procedure? Where are the @.SSN, @.FirstName,@.LastName being
set?
David Gugick
Imceda Software
www.imceda.com|||Not using a cursor, anywhere... The @.Name and @.SSN are being read from the
linked server, the first and last name are parsed from @.Name but that code
was irrelevant so I omitted it. The distributed transaction is implied even
though it's only a read on the linked server, MS DTC will still create a
transaction if the remote server supports the "ITransactionJoin" interface a
t
least thats what BOL says. I'd like to try and prevent the creation of the
transaction with the REMOTE_PROC_TRANSACTIONS option but I think I'll have t
o
create a procedure with output parameters on the linked server rather than
just reading from the table directly.
Here's an excerpt from BOL titled (Transact-SQL Distributed Transaction)...
I'm interpreting this as if I can create a procedure on the target of the
linked server execute it, read the output parameters and as long as I have
set the REMOTE_PROC_TRANSACTIONS OFF option for the connection I shouldn't
get a distributed transaction from MS DTC, am I correct in that assumption,
anyone...?
Starting Distributed Transactions
You can start distributed transactions in Transact-SQL in these ways:
Start an explicit distributed transaction using the BEGIN DISTRIBUTED
TRANSACTION statement.
You can also execute a distributed query against a linked server. The SQL
Server you have connected to calls MS DTC to manage the distributed
transaction with the linked server. You can also call remote stored
procedures on a remote SQL Server as part of the distributed transaction.
While in a local transaction, execute a distributed query.
If the OLE DB data source supports the ITransactionJoin interface, the
transaction is promoted to a distributed transaction, even if the query is a
read-only query. If the data source does not support ITransactionJoin, only
read-only statements are allowed.
If SET REMOTE_PROC_TRANSACTIONS ON has been executed and a local transaction
calls a remote stored procedure on another SQL Server, the local transaction
is promoted to a distributed transaction.
SQL Server uses MS DTC to coordinate the transaction with the remote server.
Calls to remote stored procedures execute outside the scope of a local
transaction if REMOTE_PROC_TRANSACTIONS is set to OFF. The work done by the
remote procedure is not rolled back if the local transaction is rolled back.
The work done by the remote stored procedure is committed at the time the
procedure completes, not when the local transaction is committed.
The REMOTE_PROC_TRANSACTIONS option is a compatibility option that affects
only remote stored procedure calls made to remote servers defined using
sp_addserver. For more information about remote stored procedures, see Remot
e
Stored Procedure Architecture. The option does not apply to distributed
queries that execute a stored procedure on a linked server defined using
sp_addlinkedserver. For more information about distributed queries, see
Distributed Queries.
"David Gugick" wrote:
> Alien2_51 wrote:
> What type of cursor are you using if not a firehose (Server / read-only
> / forward-only)?
> Maybe you could try wrapping the linked server call in a transaction? Is
> that the whole procedure? Where are the @.SSN, @.FirstName,@.LastName being
> set?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Alien2_51 wrote:
> Not using a cursor, anywhere... The @.Name and @.SSN are being read
You always use a cursor when selecting data. It's inherent in the
mechanism of data retrieval. From your code ADO, ADO.Net, ODBC, etc. you
are setting parameters which define what type of cursor is used. Don't
confuse the word cursor with the SQL Server DECLARE CURSOR. It's not the
same thing. When you issue a query from QA, for example, QA is probably
using a firehose cursor, same when you open a table from SQL EM.
If this is run from your application, you are either setting these
parameters or using the defaults. A firehose cursor is nothing more than
using what SQL Server generates at the back-end. That is a server-side,
read-only, forward-only result set. If you can, change this result set
to a static, client-side cursor and it may eliminate the problem.
David Gugick
Imceda Software
www.imceda.com|||I remember reading that a firehose cursor is an obsolete term for a default
result set in BOL. How do I tell SQL Server to use something other than the
default cursor when selecting data..? How is that going to prevent the
distributed transaction..?
"David Gugick" wrote:
> Alien2_51 wrote:
> You always use a cursor when selecting data. It's inherent in the
> mechanism of data retrieval. From your code ADO, ADO.Net, ODBC, etc. you
> are setting parameters which define what type of cursor is used. Don't
> confuse the word cursor with the SQL Server DECLARE CURSOR. It's not the
> same thing. When you issue a query from QA, for example, QA is probably
> using a firehose cursor, same when you open a table from SQL EM.
> If this is run from your application, you are either setting these
> parameters or using the defaults. A firehose cursor is nothing more than
> using what SQL Server generates at the back-end. That is a server-side,
> read-only, forward-only result set. If you can, change this result set
> to a static, client-side cursor and it may eliminate the problem.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Alien2_51 wrote:
> I remember reading that a firehose cursor is an obsolete term for a
> default result set in BOL. How do I tell SQL Server to use something
> other than the default cursor when selecting data..? How is that
> going to prevent the distributed transaction..?
>
What are you programming in? This is a client, data-access library
issue, not a SQL Server one. Firehose cursors are still around, even in
.Net, although are are implemented by the datareader object, and are
considered the fastest method to return data to the client. There are
really the "no-cursor cursor". Ignore the SQL Server BOL comment. Here's
more information:
Firehose Cursors
Traditional ADO exposes four different types of cursors that change how
the ADO Recordset object functions. An ADO Recordset object behaves very
differently depending on how its CursorType property is set. For
example, by setting the CursorType to adOpenForwardOnly, the Recordset
remains connected to its data source and must be traversed in a
forward-only direction. However, when you set the CursorType property to
adOpenDynamic, the Recordset can be traversed forward or backward, or
you can even jump the cursor to a particular row. Through its CursorType
and CursorLocation properties, the ADO Recordset object takes the
approach of wrapping many solutions into a single object. ADO.NET takes
a different approach where it has distinct objects and methods that are
designed to handle specific situations.
In classic ADO forward-only, read-only cursors are implemented by
setting the CursorType to adOpenForwardOnly and the CursorLocation to
adUseServer (which are also the default settings). This makes the
Recordset object take the form of a forward-only, server-side cursor.
The MoveNext method repositions the Recordset to the next row and the
MovePrevious method is not allowed at all. You can, however, invoke the
MoveFirst method on a Recordset. It is a bit misleading, however, as it
does not reposition itself to the beginning of the current rowset.
Rather, it invokes the original SQL statement and refills the Recordset
from scratch, thus moving to the first record again.
This can easily be seen by opening the SQL Profiler tool and watching
the SQL execute every time the MoveFirst method is executed on a
traditional ADO Recordset with a CursorType of adOpenForwardOnly. This
type of cursor is commonly used in applications where thousands (or
more) rows need to be traversed one at a time or when a smaller rowset
is required but only needs to be traversed once, perhaps to be loaded
into a pick list:
'-- Forward-only Firehose Cursor in ASP and ADO
Set oRs.ActiveConnection = oCn
oRs.LockType = adLockReadOnly
oRs.CursorType = adOpenForwardOnly
oRs.CursorLocation = adUseServer
oRs.Open sSQL
The closest equivalent to this type of traditional ADO firehose cursor
is the ADO.NET DataReader object. Just like the traditional forward-only
ADO Recordset, the DataReader remains connected to its data source while
it is open and can be traversed only in a forward direction. However,
there are differences. One is that individual DataReader types are
written specifically for a data provider such as SQL Server? (the
SqlDataReader class) or an ODBC data source (the OdbcDataReader class).
The ADO.NET DataReader object is very efficient as it is built
specifically for the purpose of implementing forward-only, read-only
cursors. The classic ADO forward-only cursor is implemented through the
same Recordset object as a disconnected Recordset or even as a data
source-sensitive Recordset. The DataReader is designed solely to be a
lean firehose cursor.
' ASP.NET and ADO.NET in Visual Basic .NET
Dim oDr As SqlDataReader = oCmd.ExecuteReader()
// ASP.NET and ADO.NET in C#
SqlDataReader oDr = oCmd.ExecuteReader();
David Gugick
Imceda Software
www.imceda.com|||David,
Thank you very much for your time in helping me on this, it's very much
appreciated! The client application is written in VB.NET and uses Rocky
Lohtkas CSLA component framework. The stored procedure that's called that
generates this error is in the save method of one of my components, there's
no data reader there, although I do use DataReaders to populate the objects
with data. I'm still scepticle that this is a SQL Server (MS DTC) issue and
not a client cursor problem because the ITransactionJoin interface is
something MS DTC uses, right...? Also I have a powerbuilder application tha
t
has generated the same exact error.
Dan
"David Gugick" wrote:
> Alien2_51 wrote:
> What are you programming in? This is a client, data-access library
> issue, not a SQL Server one. Firehose cursors are still around, even in
> ..Net, although are are implemented by the datareader object, and are
> considered the fastest method to return data to the client. There are
> really the "no-cursor cursor". Ignore the SQL Server BOL comment. Here's
> more information:
> Firehose Cursors
> Traditional ADO exposes four different types of cursors that change how
> the ADO Recordset object functions. An ADO Recordset object behaves very
> differently depending on how its CursorType property is set. For
> example, by setting the CursorType to adOpenForwardOnly, the Recordset
> remains connected to its data source and must be traversed in a
> forward-only direction. However, when you set the CursorType property to
> adOpenDynamic, the Recordset can be traversed forward or backward, or
> you can even jump the cursor to a particular row. Through its CursorType
> and CursorLocation properties, the ADO Recordset object takes the
> approach of wrapping many solutions into a single object. ADO.NET takes
> a different approach where it has distinct objects and methods that are
> designed to handle specific situations.
> In classic ADO forward-only, read-only cursors are implemented by
> setting the CursorType to adOpenForwardOnly and the CursorLocation to
> adUseServer (which are also the default settings). This makes the
> Recordset object take the form of a forward-only, server-side cursor.
> The MoveNext method repositions the Recordset to the next row and the
> MovePrevious method is not allowed at all. You can, however, invoke the
> MoveFirst method on a Recordset. It is a bit misleading, however, as it
> does not reposition itself to the beginning of the current rowset.
> Rather, it invokes the original SQL statement and refills the Recordset
> from scratch, thus moving to the first record again.
> This can easily be seen by opening the SQL Profiler tool and watching
> the SQL execute every time the MoveFirst method is executed on a
> traditional ADO Recordset with a CursorType of adOpenForwardOnly. This
> type of cursor is commonly used in applications where thousands (or
> more) rows need to be traversed one at a time or when a smaller rowset
> is required but only needs to be traversed once, perhaps to be loaded
> into a pick list:
> '-- Forward-only Firehose Cursor in ASP and ADO
> Set oRs.ActiveConnection = oCn
> oRs.LockType = adLockReadOnly
> oRs.CursorType = adOpenForwardOnly
> oRs.CursorLocation = adUseServer
> oRs.Open sSQL
> The closest equivalent to this type of traditional ADO firehose cursor
> is the ADO.NET DataReader object. Just like the traditional forward-only
> ADO Recordset, the DataReader remains connected to its data source while
> it is open and can be traversed only in a forward direction. However,
> there are differences. One is that individual DataReader types are
> written specifically for a data provider such as SQL Server? (the
> SqlDataReader class) or an ODBC data source (the OdbcDataReader class).
> The ADO.NET DataReader object is very efficient as it is built
> specifically for the purpose of implementing forward-only, read-only
> cursors. The classic ADO forward-only cursor is implemented through the
> same Recordset object as a disconnected Recordset or even as a data
> source-sensitive Recordset. The DataReader is designed solely to be a
> lean firehose cursor.
> ' ASP.NET and ADO.NET in Visual Basic .NET
> Dim oDr As SqlDataReader = oCmd.ExecuteReader()
> // ASP.NET and ADO.NET in C#
> SqlDataReader oDr = oCmd.ExecuteReader();
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Alien2_51 wrote:
> David,
> Thank you very much for your time in helping me on this, it's very
> much appreciated! The client application is written in VB.NET and
> uses Rocky Lohtkas CSLA component framework. The stored procedure
> that's called that generates this error is in the save method of one
> of my components, there's no data reader there, although I do use
> DataReaders to populate the objects with data. I'm still scepticle
> that this is a SQL Server (MS DTC) issue and not a client cursor
> problem because the ITransactionJoin interface is something MS DTC
> uses, right...? Also I have a powerbuilder application that has
> generated the same exact error.
> Dan
I'm not going to be able help much more. A firehose cursor as set up by
one data access library is really the same as the next since there's no
client management of the cursor. The error you are receiving has been
reported in SQL EM as well.
When you run the procedure it sounds like you are using .Net call that
may be generating a recordset/resultset or whatever it's called in
.net. If that's the case, try and make an ADO.net call that does not
generate a result set.
You should also have a SET NOCOUNT ON at the top of every procedure. And
you should probbly be using SET @.SalesPersonID = SCOPE_IDENTITY()
instead of @.@.identity.
Bu as I mentioned, I'm out of ideas onthis issue.
David Gugick
Imceda Software
www.imceda.com|||Thanks David... I'm using ExecuteNonQuery which doesn't generate a result
set. Anybody else have any ideas...?
"David Gugick" wrote:
> Alien2_51 wrote:
> I'm not going to be able help much more. A firehose cursor as set up by
> one data access library is really the same as the next since there's no
> client management of the cursor. The error you are receiving has been
> reported in SQL EM as well.
> When you run the procedure it sounds like you are using .Net call that
> may be generating a recordset/resultset or whatever it's called in
> ..net. If that's the case, try and make an ADO.net call that does not
> generate a result set.
> You should also have a SET NOCOUNT ON at the top of every procedure. And
> you should probbly be using SET @.SalesPersonID = SCOPE_IDENTITY()
> instead of @.@.identity.
> Bu as I mentioned, I'm out of ideas onthis issue.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Alien2_51 wrote:
> Thanks David... I'm using ExecuteNonQuery which doesn't generate a
> result set. Anybody else have any ideas...?
>
Get the SET NOCOUNT ON in there and try again. Without it, there is a
phantom result set generated.
David Gugick
Imceda Software
www.imceda.com
seen this, I've searched the group's most messages I found mention a firehos
e
cursor which I know I'm not using... Here's the procedure thats generating
the error, this is the first time I've seen this error the application has
been working fine in production for months. I think this may have to do with
the fact that I'm using a linked server and I get an implied transaction whe
n
I use it, can I turn this off...?
Here's was the whole error message.
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Cannot start transaction while in
firehose mode]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x80004005: ].
CREATE PROCEDURE [dbo].[esp_csla_SalesPerson_Create]
@.SalesPersonID int = NULL OUTPUT,
@.VendorCode CHAR(10),
@.VendorGroup CHAR(10)
AS
BEGIN
DECLARE @.SSN AS CHAR(11),
@.Name AS CHAR(50),
@.FirstName AS VARCHAR(50),
@.LastName AS VARCHAR(50),
@.Index AS INT
SELECT
@.SSN = ven_tax_id,
@.Name = ven_name
FROM
LinkedServer.database.dbo.databasetable
WHERE
ven_cd = @.VendorCode AND
ven_grp_cd = @.VendorGroup
INSERT INTO SalesPersons (VendorCode,VendorGroup,SSN,FirstName,La
stName)
VALUES (@.VendorCode,@.VendorGroup,@.SSN, @.FirstName,@.LastName)
SET @.SalesPersonID = @.@.IDENTITY
END
GOAlien2_51 wrote:
> I'm getting this error message in one of my procedures... Has anyone
> ever seen this, I've searched the group's most messages I found
> mention a firehose cursor which I know I'm not using... Here's the
> procedure thats generating the error, this is the first time I've
> seen this error the application has been working fine in production
> for months. I think this may have to do with the fact that I'm using
> a linked server and I get an implied transaction when I use it, can I
> turn this off...?
> Here's was the whole error message.
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Cannot start transaction while in
> firehose mode]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x80004005: ].
>
> CREATE PROCEDURE [dbo].[esp_csla_SalesPerson_Create]
> @.SalesPersonID int = NULL OUTPUT,
> @.VendorCode CHAR(10),
> @.VendorGroup CHAR(10)
> AS
> BEGIN
> DECLARE @.SSN AS CHAR(11),
> @.Name AS CHAR(50),
> @.FirstName AS VARCHAR(50),
> @.LastName AS VARCHAR(50),
> @.Index AS INT
> SELECT
> @.SSN = ven_tax_id,
> @.Name = ven_name
> FROM
> LinkedServer.database.dbo.databasetable
> WHERE
> ven_cd = @.VendorCode AND
> ven_grp_cd = @.VendorGroup
> INSERT INTO SalesPersons
> (VendorCode,VendorGroup,SSN,FirstName,La
stName) VALUES
> (@.VendorCode,@.VendorGroup,@.SSN, @.FirstName,@.LastName)
> SET @.SalesPersonID = @.@.IDENTITY
> END
> GO
What type of cursor are you using if not a firehose (Server / read-only
/ forward-only)?
Maybe you could try wrapping the linked server call in a transaction? Is
that the whole procedure? Where are the @.SSN, @.FirstName,@.LastName being
set?
David Gugick
Imceda Software
www.imceda.com|||Not using a cursor, anywhere... The @.Name and @.SSN are being read from the
linked server, the first and last name are parsed from @.Name but that code
was irrelevant so I omitted it. The distributed transaction is implied even
though it's only a read on the linked server, MS DTC will still create a
transaction if the remote server supports the "ITransactionJoin" interface a
t
least thats what BOL says. I'd like to try and prevent the creation of the
transaction with the REMOTE_PROC_TRANSACTIONS option but I think I'll have t
o
create a procedure with output parameters on the linked server rather than
just reading from the table directly.
Here's an excerpt from BOL titled (Transact-SQL Distributed Transaction)...
I'm interpreting this as if I can create a procedure on the target of the
linked server execute it, read the output parameters and as long as I have
set the REMOTE_PROC_TRANSACTIONS OFF option for the connection I shouldn't
get a distributed transaction from MS DTC, am I correct in that assumption,
anyone...?
Starting Distributed Transactions
You can start distributed transactions in Transact-SQL in these ways:
Start an explicit distributed transaction using the BEGIN DISTRIBUTED
TRANSACTION statement.
You can also execute a distributed query against a linked server. The SQL
Server you have connected to calls MS DTC to manage the distributed
transaction with the linked server. You can also call remote stored
procedures on a remote SQL Server as part of the distributed transaction.
While in a local transaction, execute a distributed query.
If the OLE DB data source supports the ITransactionJoin interface, the
transaction is promoted to a distributed transaction, even if the query is a
read-only query. If the data source does not support ITransactionJoin, only
read-only statements are allowed.
If SET REMOTE_PROC_TRANSACTIONS ON has been executed and a local transaction
calls a remote stored procedure on another SQL Server, the local transaction
is promoted to a distributed transaction.
SQL Server uses MS DTC to coordinate the transaction with the remote server.
Calls to remote stored procedures execute outside the scope of a local
transaction if REMOTE_PROC_TRANSACTIONS is set to OFF. The work done by the
remote procedure is not rolled back if the local transaction is rolled back.
The work done by the remote stored procedure is committed at the time the
procedure completes, not when the local transaction is committed.
The REMOTE_PROC_TRANSACTIONS option is a compatibility option that affects
only remote stored procedure calls made to remote servers defined using
sp_addserver. For more information about remote stored procedures, see Remot
e
Stored Procedure Architecture. The option does not apply to distributed
queries that execute a stored procedure on a linked server defined using
sp_addlinkedserver. For more information about distributed queries, see
Distributed Queries.
"David Gugick" wrote:
> Alien2_51 wrote:
> What type of cursor are you using if not a firehose (Server / read-only
> / forward-only)?
> Maybe you could try wrapping the linked server call in a transaction? Is
> that the whole procedure? Where are the @.SSN, @.FirstName,@.LastName being
> set?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Alien2_51 wrote:
> Not using a cursor, anywhere... The @.Name and @.SSN are being read
You always use a cursor when selecting data. It's inherent in the
mechanism of data retrieval. From your code ADO, ADO.Net, ODBC, etc. you
are setting parameters which define what type of cursor is used. Don't
confuse the word cursor with the SQL Server DECLARE CURSOR. It's not the
same thing. When you issue a query from QA, for example, QA is probably
using a firehose cursor, same when you open a table from SQL EM.
If this is run from your application, you are either setting these
parameters or using the defaults. A firehose cursor is nothing more than
using what SQL Server generates at the back-end. That is a server-side,
read-only, forward-only result set. If you can, change this result set
to a static, client-side cursor and it may eliminate the problem.
David Gugick
Imceda Software
www.imceda.com|||I remember reading that a firehose cursor is an obsolete term for a default
result set in BOL. How do I tell SQL Server to use something other than the
default cursor when selecting data..? How is that going to prevent the
distributed transaction..?
"David Gugick" wrote:
> Alien2_51 wrote:
> You always use a cursor when selecting data. It's inherent in the
> mechanism of data retrieval. From your code ADO, ADO.Net, ODBC, etc. you
> are setting parameters which define what type of cursor is used. Don't
> confuse the word cursor with the SQL Server DECLARE CURSOR. It's not the
> same thing. When you issue a query from QA, for example, QA is probably
> using a firehose cursor, same when you open a table from SQL EM.
> If this is run from your application, you are either setting these
> parameters or using the defaults. A firehose cursor is nothing more than
> using what SQL Server generates at the back-end. That is a server-side,
> read-only, forward-only result set. If you can, change this result set
> to a static, client-side cursor and it may eliminate the problem.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Alien2_51 wrote:
> I remember reading that a firehose cursor is an obsolete term for a
> default result set in BOL. How do I tell SQL Server to use something
> other than the default cursor when selecting data..? How is that
> going to prevent the distributed transaction..?
>
What are you programming in? This is a client, data-access library
issue, not a SQL Server one. Firehose cursors are still around, even in
.Net, although are are implemented by the datareader object, and are
considered the fastest method to return data to the client. There are
really the "no-cursor cursor". Ignore the SQL Server BOL comment. Here's
more information:
Firehose Cursors
Traditional ADO exposes four different types of cursors that change how
the ADO Recordset object functions. An ADO Recordset object behaves very
differently depending on how its CursorType property is set. For
example, by setting the CursorType to adOpenForwardOnly, the Recordset
remains connected to its data source and must be traversed in a
forward-only direction. However, when you set the CursorType property to
adOpenDynamic, the Recordset can be traversed forward or backward, or
you can even jump the cursor to a particular row. Through its CursorType
and CursorLocation properties, the ADO Recordset object takes the
approach of wrapping many solutions into a single object. ADO.NET takes
a different approach where it has distinct objects and methods that are
designed to handle specific situations.
In classic ADO forward-only, read-only cursors are implemented by
setting the CursorType to adOpenForwardOnly and the CursorLocation to
adUseServer (which are also the default settings). This makes the
Recordset object take the form of a forward-only, server-side cursor.
The MoveNext method repositions the Recordset to the next row and the
MovePrevious method is not allowed at all. You can, however, invoke the
MoveFirst method on a Recordset. It is a bit misleading, however, as it
does not reposition itself to the beginning of the current rowset.
Rather, it invokes the original SQL statement and refills the Recordset
from scratch, thus moving to the first record again.
This can easily be seen by opening the SQL Profiler tool and watching
the SQL execute every time the MoveFirst method is executed on a
traditional ADO Recordset with a CursorType of adOpenForwardOnly. This
type of cursor is commonly used in applications where thousands (or
more) rows need to be traversed one at a time or when a smaller rowset
is required but only needs to be traversed once, perhaps to be loaded
into a pick list:
'-- Forward-only Firehose Cursor in ASP and ADO
Set oRs.ActiveConnection = oCn
oRs.LockType = adLockReadOnly
oRs.CursorType = adOpenForwardOnly
oRs.CursorLocation = adUseServer
oRs.Open sSQL
The closest equivalent to this type of traditional ADO firehose cursor
is the ADO.NET DataReader object. Just like the traditional forward-only
ADO Recordset, the DataReader remains connected to its data source while
it is open and can be traversed only in a forward direction. However,
there are differences. One is that individual DataReader types are
written specifically for a data provider such as SQL Server? (the
SqlDataReader class) or an ODBC data source (the OdbcDataReader class).
The ADO.NET DataReader object is very efficient as it is built
specifically for the purpose of implementing forward-only, read-only
cursors. The classic ADO forward-only cursor is implemented through the
same Recordset object as a disconnected Recordset or even as a data
source-sensitive Recordset. The DataReader is designed solely to be a
lean firehose cursor.
' ASP.NET and ADO.NET in Visual Basic .NET
Dim oDr As SqlDataReader = oCmd.ExecuteReader()
// ASP.NET and ADO.NET in C#
SqlDataReader oDr = oCmd.ExecuteReader();
David Gugick
Imceda Software
www.imceda.com|||David,
Thank you very much for your time in helping me on this, it's very much
appreciated! The client application is written in VB.NET and uses Rocky
Lohtkas CSLA component framework. The stored procedure that's called that
generates this error is in the save method of one of my components, there's
no data reader there, although I do use DataReaders to populate the objects
with data. I'm still scepticle that this is a SQL Server (MS DTC) issue and
not a client cursor problem because the ITransactionJoin interface is
something MS DTC uses, right...? Also I have a powerbuilder application tha
t
has generated the same exact error.
Dan
"David Gugick" wrote:
> Alien2_51 wrote:
> What are you programming in? This is a client, data-access library
> issue, not a SQL Server one. Firehose cursors are still around, even in
> ..Net, although are are implemented by the datareader object, and are
> considered the fastest method to return data to the client. There are
> really the "no-cursor cursor". Ignore the SQL Server BOL comment. Here's
> more information:
> Firehose Cursors
> Traditional ADO exposes four different types of cursors that change how
> the ADO Recordset object functions. An ADO Recordset object behaves very
> differently depending on how its CursorType property is set. For
> example, by setting the CursorType to adOpenForwardOnly, the Recordset
> remains connected to its data source and must be traversed in a
> forward-only direction. However, when you set the CursorType property to
> adOpenDynamic, the Recordset can be traversed forward or backward, or
> you can even jump the cursor to a particular row. Through its CursorType
> and CursorLocation properties, the ADO Recordset object takes the
> approach of wrapping many solutions into a single object. ADO.NET takes
> a different approach where it has distinct objects and methods that are
> designed to handle specific situations.
> In classic ADO forward-only, read-only cursors are implemented by
> setting the CursorType to adOpenForwardOnly and the CursorLocation to
> adUseServer (which are also the default settings). This makes the
> Recordset object take the form of a forward-only, server-side cursor.
> The MoveNext method repositions the Recordset to the next row and the
> MovePrevious method is not allowed at all. You can, however, invoke the
> MoveFirst method on a Recordset. It is a bit misleading, however, as it
> does not reposition itself to the beginning of the current rowset.
> Rather, it invokes the original SQL statement and refills the Recordset
> from scratch, thus moving to the first record again.
> This can easily be seen by opening the SQL Profiler tool and watching
> the SQL execute every time the MoveFirst method is executed on a
> traditional ADO Recordset with a CursorType of adOpenForwardOnly. This
> type of cursor is commonly used in applications where thousands (or
> more) rows need to be traversed one at a time or when a smaller rowset
> is required but only needs to be traversed once, perhaps to be loaded
> into a pick list:
> '-- Forward-only Firehose Cursor in ASP and ADO
> Set oRs.ActiveConnection = oCn
> oRs.LockType = adLockReadOnly
> oRs.CursorType = adOpenForwardOnly
> oRs.CursorLocation = adUseServer
> oRs.Open sSQL
> The closest equivalent to this type of traditional ADO firehose cursor
> is the ADO.NET DataReader object. Just like the traditional forward-only
> ADO Recordset, the DataReader remains connected to its data source while
> it is open and can be traversed only in a forward direction. However,
> there are differences. One is that individual DataReader types are
> written specifically for a data provider such as SQL Server? (the
> SqlDataReader class) or an ODBC data source (the OdbcDataReader class).
> The ADO.NET DataReader object is very efficient as it is built
> specifically for the purpose of implementing forward-only, read-only
> cursors. The classic ADO forward-only cursor is implemented through the
> same Recordset object as a disconnected Recordset or even as a data
> source-sensitive Recordset. The DataReader is designed solely to be a
> lean firehose cursor.
> ' ASP.NET and ADO.NET in Visual Basic .NET
> Dim oDr As SqlDataReader = oCmd.ExecuteReader()
> // ASP.NET and ADO.NET in C#
> SqlDataReader oDr = oCmd.ExecuteReader();
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Alien2_51 wrote:
> David,
> Thank you very much for your time in helping me on this, it's very
> much appreciated! The client application is written in VB.NET and
> uses Rocky Lohtkas CSLA component framework. The stored procedure
> that's called that generates this error is in the save method of one
> of my components, there's no data reader there, although I do use
> DataReaders to populate the objects with data. I'm still scepticle
> that this is a SQL Server (MS DTC) issue and not a client cursor
> problem because the ITransactionJoin interface is something MS DTC
> uses, right...? Also I have a powerbuilder application that has
> generated the same exact error.
> Dan
I'm not going to be able help much more. A firehose cursor as set up by
one data access library is really the same as the next since there's no
client management of the cursor. The error you are receiving has been
reported in SQL EM as well.
When you run the procedure it sounds like you are using .Net call that
may be generating a recordset/resultset or whatever it's called in
.net. If that's the case, try and make an ADO.net call that does not
generate a result set.
You should also have a SET NOCOUNT ON at the top of every procedure. And
you should probbly be using SET @.SalesPersonID = SCOPE_IDENTITY()
instead of @.@.identity.
Bu as I mentioned, I'm out of ideas onthis issue.
David Gugick
Imceda Software
www.imceda.com|||Thanks David... I'm using ExecuteNonQuery which doesn't generate a result
set. Anybody else have any ideas...?
"David Gugick" wrote:
> Alien2_51 wrote:
> I'm not going to be able help much more. A firehose cursor as set up by
> one data access library is really the same as the next since there's no
> client management of the cursor. The error you are receiving has been
> reported in SQL EM as well.
> When you run the procedure it sounds like you are using .Net call that
> may be generating a recordset/resultset or whatever it's called in
> ..net. If that's the case, try and make an ADO.net call that does not
> generate a result set.
> You should also have a SET NOCOUNT ON at the top of every procedure. And
> you should probbly be using SET @.SalesPersonID = SCOPE_IDENTITY()
> instead of @.@.identity.
> Bu as I mentioned, I'm out of ideas onthis issue.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Alien2_51 wrote:
> Thanks David... I'm using ExecuteNonQuery which doesn't generate a
> result set. Anybody else have any ideas...?
>
Get the SET NOCOUNT ON in there and try again. Without it, there is a
phantom result set generated.
David Gugick
Imceda Software
www.imceda.com
Cannot start transaction
Hi,
I have this problem with SQL Server 2000.
Opening an ASP page I read this error:
"cannot create new transaction because capacity was exceeded"
The error code is cannot "8004d01d"
Can anyone help me?
Thanks> Hi,
> I have this problem with SQL Server 2000.
> Opening an ASP page I read this error:
> "cannot create new transaction because capacity was exceeded"
> The error code is cannot "8004d01d"
> Can anyone help me?
I assume you did try to search Usenet archives? There is plenty information
about this issue there. See:
http://groups.google.pl/groups?hl=p...sa=N&tab=wg&lr=
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
I have this problem with SQL Server 2000.
Opening an ASP page I read this error:
"cannot create new transaction because capacity was exceeded"
The error code is cannot "8004d01d"
Can anyone help me?
Thanks> Hi,
> I have this problem with SQL Server 2000.
> Opening an ASP page I read this error:
> "cannot create new transaction because capacity was exceeded"
> The error code is cannot "8004d01d"
> Can anyone help me?
I assume you did try to search Usenet archives? There is plenty information
about this issue there. See:
http://groups.google.pl/groups?hl=p...sa=N&tab=wg&lr=
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
Friday, February 24, 2012
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
>
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
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 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...
>
|||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'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
>
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 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...
>
|||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'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
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/in...
ink.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 aft
er
> 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/in...r />
hrink.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'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 aft
er
> 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 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/in...
ink.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 aft
er
> 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/in...r />
hrink.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'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 aft
er
> 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 - windows 2003/sp1 and SQL 2000/SP4
I cannot shrink a transaction log to a database through the enterprise mmc.
This particular transaction log is about 135 GB and is filling up the hard
drive. Is there a way to force this?http://support.microsoft.com/kb/q272318/
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sabo, Eric" wrote:
> I cannot shrink a transaction log to a database through the enterprise mmc.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?|||Mike,
Thanks for the reply but where do you run this command from. Also, what
if I have no space to backup this transaction log.
"Mike Epprecht (SQL MVP)" wrote:
> http://support.microsoft.com/kb/q272318/
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Sabo, Eric" wrote:
> > I cannot shrink a transaction log to a database through the enterprise mmc.
> > This particular transaction log is about 135 GB and is filling up the hard
> > drive. Is there a way to force this?|||It is saying the following when I tried to shrink the database:
Cannot shrink log file 2 (XXXXXXXX_log) because all logical log files are in
use.
"Sabo, Eric" wrote:
> I cannot shrink a transaction log to a database through the enterprise mmc.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?|||Hi
OK, so I assume that you have not backuped up your transaction log, nor
require it, so put the DB in simple Recovery Mode using Enterprise Manager
(Property of the DB)
This will clear all inactive entries in the log and then you can shrink the
log,
You need to run those commands in the KB from Query Analyser.
Look up "recovery plan" in Books Online to understand how the log is used
for recovery in DR scenarios.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sabo, Eric" wrote:
> Mike,
> Thanks for the reply but where do you run this command from. Also, what
> if I have no space to backup this transaction log.
> "Mike Epprecht (SQL MVP)" wrote:
> > http://support.microsoft.com/kb/q272318/
> > --
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> >
> >
> > "Sabo, Eric" wrote:
> >
> > > I cannot shrink a transaction log to a database through the enterprise mmc.
> > > This particular transaction log is about 135 GB and is filling up the hard
> > > drive. Is there a way to force this?|||Mike,
Thanks alot.
That worked. I restricted the growth this time so it will not fill up my
hard drive again. Thanks again!
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> OK, so I assume that you have not backuped up your transaction log, nor
> require it, so put the DB in simple Recovery Mode using Enterprise Manager
> (Property of the DB)
> This will clear all inactive entries in the log and then you can shrink the
> log,
> You need to run those commands in the KB from Query Analyser.
> Look up "recovery plan" in Books Online to understand how the log is used
> for recovery in DR scenarios.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Sabo, Eric" wrote:
> > Mike,
> >
> > Thanks for the reply but where do you run this command from. Also, what
> > if I have no space to backup this transaction log.
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> > > http://support.microsoft.com/kb/q272318/
> > > --
> > > --
> > > Mike Epprecht, Microsoft SQL Server MVP
> > > Zurich, Switzerland
> > >
> > > MVP Program: http://www.microsoft.com/mvp
> > >
> > > Blog: http://www.msmvps.com/epprecht/
> > >
> > >
> > >
> > > "Sabo, Eric" wrote:
> > >
> > > > I cannot shrink a transaction log to a database through the enterprise mmc.
> > > > This particular transaction log is about 135 GB and is filling up the hard
> > > > drive. Is there a way to force this?
This particular transaction log is about 135 GB and is filling up the hard
drive. Is there a way to force this?http://support.microsoft.com/kb/q272318/
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sabo, Eric" wrote:
> I cannot shrink a transaction log to a database through the enterprise mmc.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?|||Mike,
Thanks for the reply but where do you run this command from. Also, what
if I have no space to backup this transaction log.
"Mike Epprecht (SQL MVP)" wrote:
> http://support.microsoft.com/kb/q272318/
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Sabo, Eric" wrote:
> > I cannot shrink a transaction log to a database through the enterprise mmc.
> > This particular transaction log is about 135 GB and is filling up the hard
> > drive. Is there a way to force this?|||It is saying the following when I tried to shrink the database:
Cannot shrink log file 2 (XXXXXXXX_log) because all logical log files are in
use.
"Sabo, Eric" wrote:
> I cannot shrink a transaction log to a database through the enterprise mmc.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?|||Hi
OK, so I assume that you have not backuped up your transaction log, nor
require it, so put the DB in simple Recovery Mode using Enterprise Manager
(Property of the DB)
This will clear all inactive entries in the log and then you can shrink the
log,
You need to run those commands in the KB from Query Analyser.
Look up "recovery plan" in Books Online to understand how the log is used
for recovery in DR scenarios.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sabo, Eric" wrote:
> Mike,
> Thanks for the reply but where do you run this command from. Also, what
> if I have no space to backup this transaction log.
> "Mike Epprecht (SQL MVP)" wrote:
> > http://support.microsoft.com/kb/q272318/
> > --
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> >
> >
> > "Sabo, Eric" wrote:
> >
> > > I cannot shrink a transaction log to a database through the enterprise mmc.
> > > This particular transaction log is about 135 GB and is filling up the hard
> > > drive. Is there a way to force this?|||Mike,
Thanks alot.
That worked. I restricted the growth this time so it will not fill up my
hard drive again. Thanks again!
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> OK, so I assume that you have not backuped up your transaction log, nor
> require it, so put the DB in simple Recovery Mode using Enterprise Manager
> (Property of the DB)
> This will clear all inactive entries in the log and then you can shrink the
> log,
> You need to run those commands in the KB from Query Analyser.
> Look up "recovery plan" in Books Online to understand how the log is used
> for recovery in DR scenarios.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Sabo, Eric" wrote:
> > Mike,
> >
> > Thanks for the reply but where do you run this command from. Also, what
> > if I have no space to backup this transaction log.
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> > > http://support.microsoft.com/kb/q272318/
> > > --
> > > --
> > > Mike Epprecht, Microsoft SQL Server MVP
> > > Zurich, Switzerland
> > >
> > > MVP Program: http://www.microsoft.com/mvp
> > >
> > > Blog: http://www.msmvps.com/epprecht/
> > >
> > >
> > >
> > > "Sabo, Eric" wrote:
> > >
> > > > I cannot shrink a transaction log to a database through the enterprise mmc.
> > > > This particular transaction log is about 135 GB and is filling up the hard
> > > > drive. Is there a way to force this?
Cannot shrink transaction log - windows 2003/sp1 and SQL 2000/SP4
I cannot shrink a transaction log to a database through the enterprise mmc.
This particular transaction log is about 135 GB and is filling up the hard
drive. Is there a way to force this?
http://support.microsoft.com/kb/q272318/
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sabo, Eric" wrote:
> I cannot shrink a transaction log to a database through the enterprise mmc.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?
|||It is saying the following when I tried to shrink the database:
Cannot shrink log file 2 (XXXXXXXX_log) because all logical log files are in
use.
"Sabo, Eric" wrote:
> I cannot shrink a transaction log to a database through the enterprise mmc.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?
This particular transaction log is about 135 GB and is filling up the hard
drive. Is there a way to force this?
http://support.microsoft.com/kb/q272318/
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sabo, Eric" wrote:
> I cannot shrink a transaction log to a database through the enterprise mmc.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?
|||It is saying the following when I tried to shrink the database:
Cannot shrink log file 2 (XXXXXXXX_log) because all logical log files are in
use.
"Sabo, Eric" wrote:
> I cannot shrink a transaction log to a database through the enterprise mmc.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?
Cannot shrink transaction log - windows 2003/sp1 and SQL 2000/SP4
I cannot shrink a transaction log to a database through the enterprise mmc.
This particular transaction log is about 135 GB and is filling up the hard
drive. Is there a way to force this?http://support.microsoft.com/kb/q272318/
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sabo, Eric" wrote:
> I cannot shrink a transaction log to a database through the enterprise mmc
.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?|||It is saying the following when I tried to shrink the database:
Cannot shrink log file 2 (XXXXXXXX_log) because all logical log files are in
use.
"Sabo, Eric" wrote:
> I cannot shrink a transaction log to a database through the enterprise mmc
.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?
This particular transaction log is about 135 GB and is filling up the hard
drive. Is there a way to force this?http://support.microsoft.com/kb/q272318/
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sabo, Eric" wrote:
> I cannot shrink a transaction log to a database through the enterprise mmc
.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?|||It is saying the following when I tried to shrink the database:
Cannot shrink log file 2 (XXXXXXXX_log) because all logical log files are in
use.
"Sabo, Eric" wrote:
> I cannot shrink a transaction log to a database through the enterprise mmc
.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?
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.
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.
Cannot shrink Transaction Log
Hello. I've got a situation where a live database has stopped working because
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions! I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!
1. Consider the recovery model for the database. If not "simple" then SQL Server will not empty the
log files automatically, and the log will keep growing. When not "simple", only backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log backups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as documented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!
|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions! I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!
1. Consider the recovery model for the database. If not "simple" then SQL Server will not empty the
log files automatically, and the log will keep growing. When not "simple", only backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log backups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as documented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!
|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!
Cannot shrink Transaction Log
Hello. I've got a situation where a live database has stopped working becaus
e
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions!
I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!1. Consider the recovery model for the database. If not "simple" then SQL Se
rver will not empty the
log files automatically, and the log will keep growing. When not "simple", o
nly backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log back
ups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as do
cumented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working beca
use
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions
! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is no
t
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd
be
> fantastic. If anyone can give advice on how to do this, you really would b
e
> saving my life!|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY ar
e
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:
> Hello. I've got a situation where a live database has stopped working beca
use
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions
! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is no
t
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd
be
> fantastic. If anyone can give advice on how to do this, you really would b
e
> saving my life!
e
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions!
I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!1. Consider the recovery model for the database. If not "simple" then SQL Se
rver will not empty the
log files automatically, and the log will keep growing. When not "simple", o
nly backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log back
ups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as do
cumented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working beca
use
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions
! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is no
t
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd
be
> fantastic. If anyone can give advice on how to do this, you really would b
e
> saving my life!|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY ar
e
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:
> Hello. I've got a situation where a live database has stopped working beca
use
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions
! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is no
t
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd
be
> fantastic. If anyone can give advice on how to do this, you really would b
e
> saving my life!
cannot shrink transaction log
I have problems with shrinking the transaction log. I have first backup the
transactionlog and then I try to shrink de transaction log with the shrink
file command but the log wil not shrink. When I use the DBCC loginfo I see
that the status is 2 for the VLF's and they never change to 0.
How can I shrink my transaction log file.A quick way to achieve this is by:
1)Backup your db
2)Detach db
3)rename the relevant log file
4)Attach db - the reattach will automatically create a new log file
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"cas" <cas@.discussions.microsoft.com> wrote in message
news:62EB1A28-DC4C-4A6D-A5DB-D293B8B00E0C@.microsoft.com...
> I have problems with shrinking the transaction log. I have first backup
the
> transactionlog and then I try to shrink de transaction log with the shrink
> file command but the log wil not shrink. When I use the DBCC loginfo I see
> that the status is 2 for the VLF's and they never change to 0.
> How can I shrink my transaction log file.|||After taking the backup make sure there is no open transaction run DBCC
OpenTran to get the open transaction.
"Jack Vamvas" wrote:
> A quick way to achieve this is by:
> 1)Backup your db
> 2)Detach db
> 3)rename the relevant log file
> 4)Attach db - the reattach will automatically create a new log file
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
>
> "cas" <cas@.discussions.microsoft.com> wrote in message
> news:62EB1A28-DC4C-4A6D-A5DB-D293B8B00E0C@.microsoft.com...
>
> the
>
>
transactionlog and then I try to shrink de transaction log with the shrink
file command but the log wil not shrink. When I use the DBCC loginfo I see
that the status is 2 for the VLF's and they never change to 0.
How can I shrink my transaction log file.A quick way to achieve this is by:
1)Backup your db
2)Detach db
3)rename the relevant log file
4)Attach db - the reattach will automatically create a new log file
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"cas" <cas@.discussions.microsoft.com> wrote in message
news:62EB1A28-DC4C-4A6D-A5DB-D293B8B00E0C@.microsoft.com...
> I have problems with shrinking the transaction log. I have first backup
the
> transactionlog and then I try to shrink de transaction log with the shrink
> file command but the log wil not shrink. When I use the DBCC loginfo I see
> that the status is 2 for the VLF's and they never change to 0.
> How can I shrink my transaction log file.|||After taking the backup make sure there is no open transaction run DBCC
OpenTran to get the open transaction.
"Jack Vamvas" wrote:
> A quick way to achieve this is by:
> 1)Backup your db
> 2)Detach db
> 3)rename the relevant log file
> 4)Attach db - the reattach will automatically create a new log file
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
>
> "cas" <cas@.discussions.microsoft.com> wrote in message
> news:62EB1A28-DC4C-4A6D-A5DB-D293B8B00E0C@.microsoft.com...
>
> the
>
>
cannot shrink transaction log
I have not been able to successfully shrink my transaction log. I'm
receiving the infamous "the log file for database is full".
I have tried:
backup log database with truncate_only
go
dbcc shrinkfile (database _log,0)
go
I receive this message when I am importing a large excel spreadsheet.
The error occurs on line 7956. Again, this is large, so what can I do to
make sure this works. This import is daily.
Thanks!!
*** Sent via Developersdex http://www.examnotes.net ***Joey,
Sounds like you have two issues.
1. >I have not been able to successfully shrink my transaction log.
Are there any open transactions (can use DBCC OPENTRAN to determine and KILL
to end).
Also see:
http://groups.google.com/group/micr...9c56843a0f1d1db
2. Import issue - ensure adequate log space is available by increasing the
size of the log file as well as ensuring auto-growth is enabled. You can
use DBCC SQLPERF(LOGSPACE) to track the log usage. If the table has a high
number of indexes you may consider dropping the indexes prior to the import
then recreating them following the import. Minimally logged op with BCP or
BULK COPY is another option - but probably not waranted here.
HTH
Jerry
"Joey Martin" <joey@.infosmiths.net> wrote in message
news:OtHjosX2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I have not been able to successfully shrink my transaction log. I'm
> receiving the infamous "the log file for database is full".
> I have tried:
> backup log database with truncate_only
> go
> dbcc shrinkfile (database _log,0)
> go
> I receive this message when I am importing a large excel spreadsheet.
> The error occurs on line 7956. Again, this is large, so what can I do to
> make sure this works. This import is daily.
> Thanks!!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||What version of SQL Server are you using? What service pack? I've seen
this problem before, but I think it went away when I installed SP3.
Have you tried shrinking the file using Enterprise Manager?
You might try adding a second log file on another disk, truncate and shrink
the first file, and then remove the second log file. I'm not sure if that
will work, but it's worth a try.
Another option would be to replace the log file: detach the database using
sp_detach_db, delete the log file and then reattach the database using
sp_attach_single_file_db. If the log file wasn't created on the correct
physical disk drive, detach the database using sp_detach_db, move the log
file onto the log disk drive, and then reattach the database using
sp_attach_db.
"Joey Martin" <joey@.infosmiths.net> wrote in message
news:OtHjosX2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I have not been able to successfully shrink my transaction log. I'm
> receiving the infamous "the log file for database is full".
> I have tried:
> backup log database with truncate_only
> go
> dbcc shrinkfile (database _log,0)
> go
> I receive this message when I am importing a large excel spreadsheet.
> The error occurs on line 7956. Again, this is large, so what can I do to
> make sure this works. This import is daily.
> Thanks!!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
receiving the infamous "the log file for database is full".
I have tried:
backup log database with truncate_only
go
dbcc shrinkfile (database _log,0)
go
I receive this message when I am importing a large excel spreadsheet.
The error occurs on line 7956. Again, this is large, so what can I do to
make sure this works. This import is daily.
Thanks!!
*** Sent via Developersdex http://www.examnotes.net ***Joey,
Sounds like you have two issues.
1. >I have not been able to successfully shrink my transaction log.
Are there any open transactions (can use DBCC OPENTRAN to determine and KILL
to end).
Also see:
http://groups.google.com/group/micr...9c56843a0f1d1db
2. Import issue - ensure adequate log space is available by increasing the
size of the log file as well as ensuring auto-growth is enabled. You can
use DBCC SQLPERF(LOGSPACE) to track the log usage. If the table has a high
number of indexes you may consider dropping the indexes prior to the import
then recreating them following the import. Minimally logged op with BCP or
BULK COPY is another option - but probably not waranted here.
HTH
Jerry
"Joey Martin" <joey@.infosmiths.net> wrote in message
news:OtHjosX2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I have not been able to successfully shrink my transaction log. I'm
> receiving the infamous "the log file for database is full".
> I have tried:
> backup log database with truncate_only
> go
> dbcc shrinkfile (database _log,0)
> go
> I receive this message when I am importing a large excel spreadsheet.
> The error occurs on line 7956. Again, this is large, so what can I do to
> make sure this works. This import is daily.
> Thanks!!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||What version of SQL Server are you using? What service pack? I've seen
this problem before, but I think it went away when I installed SP3.
Have you tried shrinking the file using Enterprise Manager?
You might try adding a second log file on another disk, truncate and shrink
the first file, and then remove the second log file. I'm not sure if that
will work, but it's worth a try.
Another option would be to replace the log file: detach the database using
sp_detach_db, delete the log file and then reattach the database using
sp_attach_single_file_db. If the log file wasn't created on the correct
physical disk drive, detach the database using sp_detach_db, move the log
file onto the log disk drive, and then reattach the database using
sp_attach_db.
"Joey Martin" <joey@.infosmiths.net> wrote in message
news:OtHjosX2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I have not been able to successfully shrink my transaction log. I'm
> receiving the infamous "the log file for database is full".
> I have tried:
> backup log database with truncate_only
> go
> dbcc shrinkfile (database _log,0)
> go
> I receive this message when I am importing a large excel spreadsheet.
> The error occurs on line 7956. Again, this is large, so what can I do to
> make sure this works. This import is daily.
> Thanks!!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
cannot shrink transaction log
I have problems with shrinking the transaction log. I have first backup the
transactionlog and then I try to shrink de transaction log with the shrink
file command but the log wil not shrink. When I use the DBCC loginfo I see
that the status is 2 for the VLF's and they never change to 0.
How can I shrink my transaction log file.A quick way to achieve this is by:
1)Backup your db
2)Detach db
3)rename the relevant log file
4)Attach db - the reattach will automatically create a new log file
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"cas" <cas@.discussions.microsoft.com> wrote in message
news:62EB1A28-DC4C-4A6D-A5DB-D293B8B00E0C@.microsoft.com...
> I have problems with shrinking the transaction log. I have first backup
the
> transactionlog and then I try to shrink de transaction log with the shrink
> file command but the log wil not shrink. When I use the DBCC loginfo I see
> that the status is 2 for the VLF's and they never change to 0.
> How can I shrink my transaction log file.|||After taking the backup make sure there is no open transaction run DBCC
OpenTran to get the open transaction.
"Jack Vamvas" wrote:
> A quick way to achieve this is by:
> 1)Backup your db
> 2)Detach db
> 3)rename the relevant log file
> 4)Attach db - the reattach will automatically create a new log file
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
>
> "cas" <cas@.discussions.microsoft.com> wrote in message
> news:62EB1A28-DC4C-4A6D-A5DB-D293B8B00E0C@.microsoft.com...
> > I have problems with shrinking the transaction log. I have first backup
> the
> > transactionlog and then I try to shrink de transaction log with the shrink
> > file command but the log wil not shrink. When I use the DBCC loginfo I see
> > that the status is 2 for the VLF's and they never change to 0.
> > How can I shrink my transaction log file.
>
>
transactionlog and then I try to shrink de transaction log with the shrink
file command but the log wil not shrink. When I use the DBCC loginfo I see
that the status is 2 for the VLF's and they never change to 0.
How can I shrink my transaction log file.A quick way to achieve this is by:
1)Backup your db
2)Detach db
3)rename the relevant log file
4)Attach db - the reattach will automatically create a new log file
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"cas" <cas@.discussions.microsoft.com> wrote in message
news:62EB1A28-DC4C-4A6D-A5DB-D293B8B00E0C@.microsoft.com...
> I have problems with shrinking the transaction log. I have first backup
the
> transactionlog and then I try to shrink de transaction log with the shrink
> file command but the log wil not shrink. When I use the DBCC loginfo I see
> that the status is 2 for the VLF's and they never change to 0.
> How can I shrink my transaction log file.|||After taking the backup make sure there is no open transaction run DBCC
OpenTran to get the open transaction.
"Jack Vamvas" wrote:
> A quick way to achieve this is by:
> 1)Backup your db
> 2)Detach db
> 3)rename the relevant log file
> 4)Attach db - the reattach will automatically create a new log file
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
>
> "cas" <cas@.discussions.microsoft.com> wrote in message
> news:62EB1A28-DC4C-4A6D-A5DB-D293B8B00E0C@.microsoft.com...
> > I have problems with shrinking the transaction log. I have first backup
> the
> > transactionlog and then I try to shrink de transaction log with the shrink
> > file command but the log wil not shrink. When I use the DBCC loginfo I see
> > that the status is 2 for the VLF's and they never change to 0.
> > How can I shrink my transaction log file.
>
>
Subscribe to:
Posts (Atom)