Hi fiaola
SQL Server has to log every data modification operation, every insert,
update and delete. If the log is full, nothing can be written to it and no
data modifications can be done. This has nothing to do with the tool you are
using.
Just do what the messages says. Look at the sys.databases view and tell us
what the log_reuse_wait_desc says. Also look at the recovery model
SELECT log_reuse_wait_desc, recovery_model_desc
FROM sys.databases
WHERE name = 'EventSentry'
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"fiaola" <fiaola@.mail.com> wrote in message
news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> I'm trying to clear some data from a SQL DB table, but i'm getting this
> error:
> I'm just running a Delete Query using Date Diff function. I read in some
> web site that this maybe a problem with SQL Server Management Tools. I
> appreciate any help to get around this and any suggestions would be great.
> Msg 9002, Level 17, State 4, Line 2
> The transaction log for database 'EventSentry' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Thanks for answering.
>
It might just be that your log is not big enough for the operation you were
doing.
How big is your log file, and is it set to autogrow?
You can get the information in the sys.files view in the database that is
having the problems.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"fiaola" <fiaola@.mail.com> wrote in message
news:uH1ss$YUIHA.5816@.TK2MSFTNGP06.phx.gbl...
> Thanks for the quick reply, here are the results.
> Log_Reuse_Wait_Desc = NOTHING
> Recovery_Model_Desc = SIMPLE
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eMohs$WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>
sql
Showing posts with label operation. Show all posts
Showing posts with label operation. Show all posts
Wednesday, March 21, 2012
Monday, March 19, 2012
Msg 7391, Level 16 The operation could not be performed because OL
I am getting the following error message
Msg 7391, Level 16, State 2, ProcedureName, Line 103
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "LINKEDSERVERNAME" was unable to begin a distributed
transaction.
This error is thrown from a stored procedures from a SQL Server 2005 (32)
default instance on Windows 2003 Server. This sp creates a #temp table and
inserts values based on the results of an executed stored procedure against a
linked server as follows: The linked server points to a SQL Server 200(32)
on Windows 2000 Server os.
create table #Data (
...columns...
)
insertinto #Data
(...columns)
exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
If I run the following command by itself, it runs fine. It only errors
within the sp and while it is inserting into #temp.
exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
I have enabled network DTC access on the executing Windows 2003 server as
suggested by the following article.
http://support.microsoft.com/default...b;en-us;817064
Any Help?
Hi
Have you checked out http://support.microsoft.com/kb/839279
John
"timw86" wrote:
> I am getting the following error message
> Msg 7391, Level 16, State 2, ProcedureName, Line 103
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "LINKEDSERVERNAME" was unable to begin a distributed
> transaction.
> This error is thrown from a stored procedures from a SQL Server 2005 (32)
> default instance on Windows 2003 Server. This sp creates a #temp table and
> inserts values based on the results of an executed stored procedure against a
> linked server as follows: The linked server points to a SQL Server 200(32)
> on Windows 2000 Server os.
> create table #Data (
> ...columns...
> )
> insertinto #Data
> (...columns)
> exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> If I run the following command by itself, it runs fine. It only errors
> within the sp and while it is inserting into #temp.
> exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> I have enabled network DTC access on the executing Windows 2003 server as
> suggested by the following article.
> http://support.microsoft.com/default...b;en-us;817064
> Any Help?
Msg 7391, Level 16, State 2, ProcedureName, Line 103
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "LINKEDSERVERNAME" was unable to begin a distributed
transaction.
This error is thrown from a stored procedures from a SQL Server 2005 (32)
default instance on Windows 2003 Server. This sp creates a #temp table and
inserts values based on the results of an executed stored procedure against a
linked server as follows: The linked server points to a SQL Server 200(32)
on Windows 2000 Server os.
create table #Data (
...columns...
)
insertinto #Data
(...columns)
exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
If I run the following command by itself, it runs fine. It only errors
within the sp and while it is inserting into #temp.
exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
I have enabled network DTC access on the executing Windows 2003 server as
suggested by the following article.
http://support.microsoft.com/default...b;en-us;817064
Any Help?
Hi
Have you checked out http://support.microsoft.com/kb/839279
John
"timw86" wrote:
> I am getting the following error message
> Msg 7391, Level 16, State 2, ProcedureName, Line 103
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "LINKEDSERVERNAME" was unable to begin a distributed
> transaction.
> This error is thrown from a stored procedures from a SQL Server 2005 (32)
> default instance on Windows 2003 Server. This sp creates a #temp table and
> inserts values based on the results of an executed stored procedure against a
> linked server as follows: The linked server points to a SQL Server 200(32)
> on Windows 2000 Server os.
> create table #Data (
> ...columns...
> )
> insertinto #Data
> (...columns)
> exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> If I run the following command by itself, it runs fine. It only errors
> within the sp and while it is inserting into #temp.
> exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> I have enabled network DTC access on the executing Windows 2003 server as
> suggested by the following article.
> http://support.microsoft.com/default...b;en-us;817064
> Any Help?
Msg 7391, Level 16 The operation could not be performed because OL
I am getting the following error message
Msg 7391, Level 16, State 2, ProcedureName, Line 103
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "LINKEDSERVERNAME" was unable to begin a distributed
transaction.
This error is thrown from a stored procedures from a SQL Server 2005 (32)
default instance on Windows 2003 Server. This sp creates a #temp table and
inserts values based on the results of an executed stored procedure against a
linked server as follows: The linked server points to a SQL Server 200(32)
on Windows 2000 Server os.
create table #Data (
...columns...
)
insert into #Data
(...columns)
exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
If I run the following command by itself, it runs fine. It only errors
within the sp and while it is inserting into #temp.
exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
I have enabled network DTC access on the executing Windows 2003 server as
suggested by the following article.
http://support.microsoft.com/default.aspx?scid=kb;en-us;817064
Any Help?Hi
Have you checked out http://support.microsoft.com/kb/839279
John
"timw86" wrote:
> I am getting the following error message
> Msg 7391, Level 16, State 2, ProcedureName, Line 103
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "LINKEDSERVERNAME" was unable to begin a distributed
> transaction.
> This error is thrown from a stored procedures from a SQL Server 2005 (32)
> default instance on Windows 2003 Server. This sp creates a #temp table and
> inserts values based on the results of an executed stored procedure against a
> linked server as follows: The linked server points to a SQL Server 200(32)
> on Windows 2000 Server os.
> create table #Data (
> ...columns...
> )
> insert into #Data
> (...columns)
> exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> If I run the following command by itself, it runs fine. It only errors
> within the sp and while it is inserting into #temp.
> exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> I have enabled network DTC access on the executing Windows 2003 server as
> suggested by the following article.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;817064
> Any Help?|||Thanks John.
The issue appears to be MSDTC security configurations for the initiating
server (windows 2003 server). By default, after I enabled Distributed
Transactions, the transaction manager communication had its setting to Mutual
Authentication. I concluded that this will not work for this our
environment. We have a windows 2003 server initiating dtc to a windows 2000
server. I changed the setting to no authentication is required. This has
allowed the sql commands to be executed without error. I conclude that the
mutual authentication is a new feature to windows 2003 and requires 2 windows
2003 servers communicating via DTC to one another. The kb article's
symptoms and cause are lacking in detail. However, the kb workaround
provided usefull steps.
Tim
"John Bell" wrote:
> Hi
> Have you checked out http://support.microsoft.com/kb/839279
> John
> "timw86" wrote:
> > I am getting the following error message
> >
> > Msg 7391, Level 16, State 2, ProcedureName, Line 103
> > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > linked server "LINKEDSERVERNAME" was unable to begin a distributed
> > transaction.
> >
> > This error is thrown from a stored procedures from a SQL Server 2005 (32)
> > default instance on Windows 2003 Server. This sp creates a #temp table and
> > inserts values based on the results of an executed stored procedure against a
> > linked server as follows: The linked server points to a SQL Server 200(32)
> > on Windows 2000 Server os.
> >
> > create table #Data (
> > ...columns...
> > )
> >
> > insert into #Data
> > (...columns)
> > exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> >
> > If I run the following command by itself, it runs fine. It only errors
> > within the sp and while it is inserting into #temp.
> >
> > exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> >
> > I have enabled network DTC access on the executing Windows 2003 server as
> > suggested by the following article.
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;817064
> >
> > Any Help?
Msg 7391, Level 16, State 2, ProcedureName, Line 103
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "LINKEDSERVERNAME" was unable to begin a distributed
transaction.
This error is thrown from a stored procedures from a SQL Server 2005 (32)
default instance on Windows 2003 Server. This sp creates a #temp table and
inserts values based on the results of an executed stored procedure against a
linked server as follows: The linked server points to a SQL Server 200(32)
on Windows 2000 Server os.
create table #Data (
...columns...
)
insert into #Data
(...columns)
exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
If I run the following command by itself, it runs fine. It only errors
within the sp and while it is inserting into #temp.
exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
I have enabled network DTC access on the executing Windows 2003 server as
suggested by the following article.
http://support.microsoft.com/default.aspx?scid=kb;en-us;817064
Any Help?Hi
Have you checked out http://support.microsoft.com/kb/839279
John
"timw86" wrote:
> I am getting the following error message
> Msg 7391, Level 16, State 2, ProcedureName, Line 103
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "LINKEDSERVERNAME" was unable to begin a distributed
> transaction.
> This error is thrown from a stored procedures from a SQL Server 2005 (32)
> default instance on Windows 2003 Server. This sp creates a #temp table and
> inserts values based on the results of an executed stored procedure against a
> linked server as follows: The linked server points to a SQL Server 200(32)
> on Windows 2000 Server os.
> create table #Data (
> ...columns...
> )
> insert into #Data
> (...columns)
> exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> If I run the following command by itself, it runs fine. It only errors
> within the sp and while it is inserting into #temp.
> exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> I have enabled network DTC access on the executing Windows 2003 server as
> suggested by the following article.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;817064
> Any Help?|||Thanks John.
The issue appears to be MSDTC security configurations for the initiating
server (windows 2003 server). By default, after I enabled Distributed
Transactions, the transaction manager communication had its setting to Mutual
Authentication. I concluded that this will not work for this our
environment. We have a windows 2003 server initiating dtc to a windows 2000
server. I changed the setting to no authentication is required. This has
allowed the sql commands to be executed without error. I conclude that the
mutual authentication is a new feature to windows 2003 and requires 2 windows
2003 servers communicating via DTC to one another. The kb article's
symptoms and cause are lacking in detail. However, the kb workaround
provided usefull steps.
Tim
"John Bell" wrote:
> Hi
> Have you checked out http://support.microsoft.com/kb/839279
> John
> "timw86" wrote:
> > I am getting the following error message
> >
> > Msg 7391, Level 16, State 2, ProcedureName, Line 103
> > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > linked server "LINKEDSERVERNAME" was unable to begin a distributed
> > transaction.
> >
> > This error is thrown from a stored procedures from a SQL Server 2005 (32)
> > default instance on Windows 2003 Server. This sp creates a #temp table and
> > inserts values based on the results of an executed stored procedure against a
> > linked server as follows: The linked server points to a SQL Server 200(32)
> > on Windows 2000 Server os.
> >
> > create table #Data (
> > ...columns...
> > )
> >
> > insert into #Data
> > (...columns)
> > exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> >
> > If I run the following command by itself, it runs fine. It only errors
> > within the sp and while it is inserting into #temp.
> >
> > exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> >
> > I have enabled network DTC access on the executing Windows 2003 server as
> > suggested by the following article.
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;817064
> >
> > Any Help?
Msg 7391, Level 16 The operation could not be performed because OL
I am getting the following error message
Msg 7391, Level 16, State 2, ProcedureName, Line 103
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "LINKEDSERVERNAME" was unable to begin a distributed
transaction.
This error is thrown from a stored procedures from a SQL Server 2005 (32)
default instance on Windows 2003 Server. This sp creates a #temp table and
inserts values based on the results of an executed stored procedure against
a
linked server as follows: The linked server points to a SQL Server 200(32)
on Windows 2000 Server os.
create table #Data (
...columns...
)
insert into #Data
(...columns)
exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
If I run the following command by itself, it runs fine. It only errors
within the sp and while it is inserting into #temp.
exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
I have enabled network DTC access on the executing Windows 2003 server as
suggested by the following article.
http://support.microsoft.com/defaul...kb;en-us;817064
Any Help?Hi
Have you checked out http://support.microsoft.com/kb/839279
John
"timw86" wrote:
> I am getting the following error message
> Msg 7391, Level 16, State 2, ProcedureName, Line 103
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "LINKEDSERVERNAME" was unable to begin a distributed
> transaction.
> This error is thrown from a stored procedures from a SQL Server 2005 (32)
> default instance on Windows 2003 Server. This sp creates a #temp table a
nd
> inserts values based on the results of an executed stored procedure agains
t a
> linked server as follows: The linked server points to a SQL Server 200(32
)
> on Windows 2000 Server os.
> create table #Data (
> ...columns...
> )
> insert into #Data
> (...columns)
> exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> If I run the following command by itself, it runs fine. It only errors
> within the sp and while it is inserting into #temp.
> exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> I have enabled network DTC access on the executing Windows 2003 server as
> suggested by the following article.
> http://support.microsoft.com/defaul...kb;en-us;817064
> Any Help?
Msg 7391, Level 16, State 2, ProcedureName, Line 103
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "LINKEDSERVERNAME" was unable to begin a distributed
transaction.
This error is thrown from a stored procedures from a SQL Server 2005 (32)
default instance on Windows 2003 Server. This sp creates a #temp table and
inserts values based on the results of an executed stored procedure against
a
linked server as follows: The linked server points to a SQL Server 200(32)
on Windows 2000 Server os.
create table #Data (
...columns...
)
insert into #Data
(...columns)
exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
If I run the following command by itself, it runs fine. It only errors
within the sp and while it is inserting into #temp.
exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
I have enabled network DTC access on the executing Windows 2003 server as
suggested by the following article.
http://support.microsoft.com/defaul...kb;en-us;817064
Any Help?Hi
Have you checked out http://support.microsoft.com/kb/839279
John
"timw86" wrote:
> I am getting the following error message
> Msg 7391, Level 16, State 2, ProcedureName, Line 103
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "LINKEDSERVERNAME" was unable to begin a distributed
> transaction.
> This error is thrown from a stored procedures from a SQL Server 2005 (32)
> default instance on Windows 2003 Server. This sp creates a #temp table a
nd
> inserts values based on the results of an executed stored procedure agains
t a
> linked server as follows: The linked server points to a SQL Server 200(32
)
> on Windows 2000 Server os.
> create table #Data (
> ...columns...
> )
> insert into #Data
> (...columns)
> exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> If I run the following command by itself, it runs fine. It only errors
> within the sp and while it is inserting into #temp.
> exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> I have enabled network DTC access on the executing Windows 2003 server as
> suggested by the following article.
> http://support.microsoft.com/defaul...kb;en-us;817064
> Any Help?
MSG 605 - Server having problems with fetching the logical pages
Hi,
I'm running SQL 2000 and server quite often (3 times within the last week)
fails on insert operation with the messages like:
"Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
object '-948063835' not to object #tmp
Connection Broken"
Similar messages were for the tables in the completely different databases.
Does not look like this is directly related to the table's size.
ThanksHi
Looks like corruption. Either your RAM or disk could ber causing this.
Run DBCC CHECKDB againt all your DB's on the server and see what the output
is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"alex" wrote:
> Hi,
> I'm running SQL 2000 and server quite often (3 times within the last week)
> fails on insert operation with the messages like:
> "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
> object '-948063835' not to object #tmp
> Connection Broken"
> Similar messages were for the tables in the completely different databases
.
> Does not look like this is directly related to the table's size.
> Thanks
>
I'm running SQL 2000 and server quite often (3 times within the last week)
fails on insert operation with the messages like:
"Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
object '-948063835' not to object #tmp
Connection Broken"
Similar messages were for the tables in the completely different databases.
Does not look like this is directly related to the table's size.
ThanksHi
Looks like corruption. Either your RAM or disk could ber causing this.
Run DBCC CHECKDB againt all your DB's on the server and see what the output
is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"alex" wrote:
> Hi,
> I'm running SQL 2000 and server quite often (3 times within the last week)
> fails on insert operation with the messages like:
> "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
> object '-948063835' not to object #tmp
> Connection Broken"
> Similar messages were for the tables in the completely different databases
.
> Does not look like this is directly related to the table's size.
> Thanks
>
MSG 605 - Server having problems with fetching the logical pages
Hi,
I'm running SQL 2000 and server quite often (3 times within the last week)
fails on insert operation with the messages like:
"Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
object '-948063835' not to object #tmp
Connection Broken"
Similar messages were for the tables in the completely different databases.
Does not look like this is directly related to the table's size.
Thanks
Hi
Looks like corruption. Either your RAM or disk could ber causing this.
Run DBCC CHECKDB againt all your DB's on the server and see what the output
is.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"alex" wrote:
> Hi,
> I'm running SQL 2000 and server quite often (3 times within the last week)
> fails on insert operation with the messages like:
> "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
> object '-948063835' not to object #tmp
> Connection Broken"
> Similar messages were for the tables in the completely different databases.
> Does not look like this is directly related to the table's size.
> Thanks
>
I'm running SQL 2000 and server quite often (3 times within the last week)
fails on insert operation with the messages like:
"Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
object '-948063835' not to object #tmp
Connection Broken"
Similar messages were for the tables in the completely different databases.
Does not look like this is directly related to the table's size.
Thanks
Hi
Looks like corruption. Either your RAM or disk could ber causing this.
Run DBCC CHECKDB againt all your DB's on the server and see what the output
is.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"alex" wrote:
> Hi,
> I'm running SQL 2000 and server quite often (3 times within the last week)
> fails on insert operation with the messages like:
> "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
> object '-948063835' not to object #tmp
> Connection Broken"
> Similar messages were for the tables in the completely different databases.
> Does not look like this is directly related to the table's size.
> Thanks
>
MSG 605 - Server having problems with fetching the logical pages
Hi,
I'm running SQL 2000 and server quite often (3 times within the last week)
fails on insert operation with the messages like:
"Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
object '-948063835' not to object #tmp
Connection Broken"
Similar messages were for the tables in the completely different databases.
Does not look like this is directly related to the table's size.
ThanksHi
Looks like corruption. Either your RAM or disk could ber causing this.
Run DBCC CHECKDB againt all your DB's on the server and see what the output
is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"alex" wrote:
> Hi,
> I'm running SQL 2000 and server quite often (3 times within the last week)
> fails on insert operation with the messages like:
> "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
> object '-948063835' not to object #tmp
> Connection Broken"
> Similar messages were for the tables in the completely different databases.
> Does not look like this is directly related to the table's size.
> Thanks
>|||Also check your backups (restore to a different sql server, run dbcc checkdb
on those). Find a backup that does not contain the corruption.
You may lose data.
If DBCC reports the minimal repair level is "repair_allow_data_loss" it's
not kidding.
If you run that, be prepared to lose some data. It might be a row, a page,
whole tables. There's really no telling.
Going back to a known good state is often a better alternative. At least
you know what data you lost.
:D
Donna Lambert
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Looks like corruption. Either your RAM or disk could ber causing this.
> Run DBCC CHECKDB againt all your DB's on the server and see what the output
> is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "alex" wrote:
> > Hi,
> > I'm running SQL 2000 and server quite often (3 times within the last week)
> > fails on insert operation with the messages like:
> >
> > "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
> > object '-948063835' not to object #tmp
> >
> > Connection Broken"
> >
> > Similar messages were for the tables in the completely different databases.
> >
> > Does not look like this is directly related to the table's size.
> >
> > Thanks
> >
> >|||Thanks. Did that and got:
....
CHECKDB found 0 allocation errors and 0 consistency errors in database
'tempdb'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Looks like database itself is OK. Can the problem be a result of some
application's
activities like the following scenario:
main stored procedure calls, withing a cycle, underlying stored procedure
and this
procedure contains the following:
create table #tmp(....)
....
drop table #tmp
It looks like after few repetitions of this scenario (number of the
iterations varies)
breakdown occurs. After replacement of the temp table with a regular one,
situation seems to be remedied.
Can it be that tempdb may have problems with a repeated re-creation of a
dropped temp. table within a single session?
Thanks
"Donna Lambert" wrote:
> Also check your backups (restore to a different sql server, run dbcc checkdb
> on those). Find a backup that does not contain the corruption.
> You may lose data.
> If DBCC reports the minimal repair level is "repair_allow_data_loss" it's
> not kidding.
> If you run that, be prepared to lose some data. It might be a row, a page,
> whole tables. There's really no telling.
> Going back to a known good state is often a better alternative. At least
> you know what data you lost.
> :D
> Donna Lambert
>
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > Looks like corruption. Either your RAM or disk could ber causing this.
> >
> > Run DBCC CHECKDB againt all your DB's on the server and see what the output
> > is.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> >
> >
> > "alex" wrote:
> >
> > > Hi,
> > > I'm running SQL 2000 and server quite often (3 times within the last week)
> > > fails on insert operation with the messages like:
> > >
> > > "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
> > > object '-948063835' not to object #tmp
> > >
> > > Connection Broken"
> > >
> > > Similar messages were for the tables in the completely different databases.
> > >
> > > Does not look like this is directly related to the table's size.
> > >
> > > Thanks
> > >
> > >|||> Can the problem be a result of some
> application's
> activities like the following scenario:
Basically, no. No TSQL code should be able to corrupt a database. If you can find such code that
reproduces, you have found a bug in SQL Server. You should investigate for hardware errors. See
general recommendations at http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp. Also,
remember that tempdb is re-created at each startup...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"alex" <alex@.discussions.microsoft.com> wrote in message
news:8D8B81BF-1C2A-4088-ACAE-1609A0FC5223@.microsoft.com...
> Thanks. Did that and got:
> ....
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'tempdb'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Looks like database itself is OK. Can the problem be a result of some
> application's
> activities like the following scenario:
> main stored procedure calls, withing a cycle, underlying stored procedure
> and this
> procedure contains the following:
> create table #tmp(....)
> ....
> drop table #tmp
> It looks like after few repetitions of this scenario (number of the
> iterations varies)
> breakdown occurs. After replacement of the temp table with a regular one,
> situation seems to be remedied.
> Can it be that tempdb may have problems with a repeated re-creation of a
> dropped temp. table within a single session?
> Thanks
> "Donna Lambert" wrote:
>> Also check your backups (restore to a different sql server, run dbcc checkdb
>> on those). Find a backup that does not contain the corruption.
>> You may lose data.
>> If DBCC reports the minimal repair level is "repair_allow_data_loss" it's
>> not kidding.
>> If you run that, be prepared to lose some data. It might be a row, a page,
>> whole tables. There's really no telling.
>> Going back to a known good state is often a better alternative. At least
>> you know what data you lost.
>> :D
>> Donna Lambert
>>
>> "Mike Epprecht (SQL MVP)" wrote:
>> > Hi
>> >
>> > Looks like corruption. Either your RAM or disk could ber causing this.
>> >
>> > Run DBCC CHECKDB againt all your DB's on the server and see what the output
>> > is.
>> >
>> > Regards
>> > --
>> > Mike Epprecht, Microsoft SQL Server MVP
>> > Zurich, Switzerland
>> >
>> > MVP Program: http://www.microsoft.com/mvp
>> >
>> > Blog: http://www.msmvps.com/epprecht/
>> >
>> >
>> >
>> > "alex" wrote:
>> >
>> > > Hi,
>> > > I'm running SQL 2000 and server quite often (3 times within the last week)
>> > > fails on insert operation with the messages like:
>> > >
>> > > "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
>> > > object '-948063835' not to object #tmp
>> > >
>> > > Connection Broken"
>> > >
>> > > Similar messages were for the tables in the completely different databases.
>> > >
>> > > Does not look like this is directly related to the table's size.
>> > >
>> > > Thanks
>> > >
>> > >|||Thanks but I did not say that DB was corrupted: DBCC CHECKDATABASE indicated
that there are no problems.
Is corruption the only reason for getting Msg 605?
"Tibor Karaszi" wrote:
> > Can the problem be a result of some
> > application's
> > activities like the following scenario:
> Basically, no. No TSQL code should be able to corrupt a database. If you can find such code that
> reproduces, you have found a bug in SQL Server. You should investigate for hardware errors. See
> general recommendations at http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp. Also,
> remember that tempdb is re-created at each startup...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "alex" <alex@.discussions.microsoft.com> wrote in message
> news:8D8B81BF-1C2A-4088-ACAE-1609A0FC5223@.microsoft.com...
> > Thanks. Did that and got:
> > ....
> > CHECKDB found 0 allocation errors and 0 consistency errors in database
> > 'tempdb'.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> > Looks like database itself is OK. Can the problem be a result of some
> > application's
> > activities like the following scenario:
> >
> > main stored procedure calls, withing a cycle, underlying stored procedure
> > and this
> > procedure contains the following:
> >
> > create table #tmp(....)
> > ....
> > drop table #tmp
> >
> > It looks like after few repetitions of this scenario (number of the
> > iterations varies)
> > breakdown occurs. After replacement of the temp table with a regular one,
> > situation seems to be remedied.
> >
> > Can it be that tempdb may have problems with a repeated re-creation of a
> > dropped temp. table within a single session?
> >
> > Thanks
> >
> > "Donna Lambert" wrote:
> >
> >> Also check your backups (restore to a different sql server, run dbcc checkdb
> >> on those). Find a backup that does not contain the corruption.
> >> You may lose data.
> >> If DBCC reports the minimal repair level is "repair_allow_data_loss" it's
> >> not kidding.
> >> If you run that, be prepared to lose some data. It might be a row, a page,
> >> whole tables. There's really no telling.
> >> Going back to a known good state is often a better alternative. At least
> >> you know what data you lost.
> >> :D
> >> Donna Lambert
> >>
> >>
> >> "Mike Epprecht (SQL MVP)" wrote:
> >>
> >> > Hi
> >> >
> >> > Looks like corruption. Either your RAM or disk could ber causing this.
> >> >
> >> > Run DBCC CHECKDB againt all your DB's on the server and see what the output
> >> > is.
> >> >
> >> > Regards
> >> > --
> >> > Mike Epprecht, Microsoft SQL Server MVP
> >> > Zurich, Switzerland
> >> >
> >> > MVP Program: http://www.microsoft.com/mvp
> >> >
> >> > Blog: http://www.msmvps.com/epprecht/
> >> >
> >> >
> >> >
> >> > "alex" wrote:
> >> >
> >> > > Hi,
> >> > > I'm running SQL 2000 and server quite often (3 times within the last week)
> >> > > fails on insert operation with the messages like:
> >> > >
> >> > > "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
> >> > > object '-948063835' not to object #tmp
> >> > >
> >> > > Connection Broken"
> >> > >
> >> > > Similar messages were for the tables in the completely different databases.
> >> > >
> >> > > Does not look like this is directly related to the table's size.
> >> > >
> >> > > Thanks
> >> > >
> >> > >
>|||Probably the corruption disappeared since objects are created and dropped all the time in tempdb.
Also, you possibly re-started SQL server in between so tempdb was rebuilt. However, you have had a
corruption, and that it something you should investigate further.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"alex" <alex@.discussions.microsoft.com> wrote in message
news:E28034E7-9F14-46EB-B491-CB690F52E0D0@.microsoft.com...
> Thanks but I did not say that DB was corrupted: DBCC CHECKDATABASE indicated
> that there are no problems.
> Is corruption the only reason for getting Msg 605?
> "Tibor Karaszi" wrote:
>> > Can the problem be a result of some
>> > application's
>> > activities like the following scenario:
>> Basically, no. No TSQL code should be able to corrupt a database. If you can find such code that
>> reproduces, you have found a bug in SQL Server. You should investigate for hardware errors. See
>> general recommendations at http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp. Also,
>> remember that tempdb is re-created at each startup...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "alex" <alex@.discussions.microsoft.com> wrote in message
>> news:8D8B81BF-1C2A-4088-ACAE-1609A0FC5223@.microsoft.com...
>> > Thanks. Did that and got:
>> > ....
>> > CHECKDB found 0 allocation errors and 0 consistency errors in database
>> > 'tempdb'.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> > Looks like database itself is OK. Can the problem be a result of some
>> > application's
>> > activities like the following scenario:
>> >
>> > main stored procedure calls, withing a cycle, underlying stored procedure
>> > and this
>> > procedure contains the following:
>> >
>> > create table #tmp(....)
>> > ....
>> > drop table #tmp
>> >
>> > It looks like after few repetitions of this scenario (number of the
>> > iterations varies)
>> > breakdown occurs. After replacement of the temp table with a regular one,
>> > situation seems to be remedied.
>> >
>> > Can it be that tempdb may have problems with a repeated re-creation of a
>> > dropped temp. table within a single session?
>> >
>> > Thanks
>> >
>> > "Donna Lambert" wrote:
>> >
>> >> Also check your backups (restore to a different sql server, run dbcc checkdb
>> >> on those). Find a backup that does not contain the corruption.
>> >> You may lose data.
>> >> If DBCC reports the minimal repair level is "repair_allow_data_loss" it's
>> >> not kidding.
>> >> If you run that, be prepared to lose some data. It might be a row, a page,
>> >> whole tables. There's really no telling.
>> >> Going back to a known good state is often a better alternative. At least
>> >> you know what data you lost.
>> >> :D
>> >> Donna Lambert
>> >>
>> >>
>> >> "Mike Epprecht (SQL MVP)" wrote:
>> >>
>> >> > Hi
>> >> >
>> >> > Looks like corruption. Either your RAM or disk could ber causing this.
>> >> >
>> >> > Run DBCC CHECKDB againt all your DB's on the server and see what the output
>> >> > is.
>> >> >
>> >> > Regards
>> >> > --
>> >> > Mike Epprecht, Microsoft SQL Server MVP
>> >> > Zurich, Switzerland
>> >> >
>> >> > MVP Program: http://www.microsoft.com/mvp
>> >> >
>> >> > Blog: http://www.msmvps.com/epprecht/
>> >> >
>> >> >
>> >> >
>> >> > "alex" wrote:
>> >> >
>> >> > > Hi,
>> >> > > I'm running SQL 2000 and server quite often (3 times within the last week)
>> >> > > fails on insert operation with the messages like:
>> >> > >
>> >> > > "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
>> >> > > object '-948063835' not to object #tmp
>> >> > >
>> >> > > Connection Broken"
>> >> > >
>> >> > > Similar messages were for the tables in the completely different databases.
>> >> > >
>> >> > > Does not look like this is directly related to the table's size.
>> >> > >
>> >> > > Thanks
>> >> > >
>> >> > >
>>|||Also, CHECKDB doesn't check everything in TEMPDB so may not pick up on the
problem you have. You also can't run repair on TEMPDB as its impossible to
put TEMPDB into single_user mode (prerequisite for running repair).
If you can repro the problem, you should call product support
(http://support.microsoft.com)
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eBmRC1EbFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Probably the corruption disappeared since objects are created and dropped
> all the time in tempdb. Also, you possibly re-started SQL server in
> between so tempdb was rebuilt. However, you have had a corruption, and
> that it something you should investigate further.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "alex" <alex@.discussions.microsoft.com> wrote in message
> news:E28034E7-9F14-46EB-B491-CB690F52E0D0@.microsoft.com...
>> Thanks but I did not say that DB was corrupted: DBCC CHECKDATABASE
>> indicated
>> that there are no problems.
>> Is corruption the only reason for getting Msg 605?
>> "Tibor Karaszi" wrote:
>> > Can the problem be a result of some
>> > application's
>> > activities like the following scenario:
>> Basically, no. No TSQL code should be able to corrupt a database. If you
>> can find such code that
>> reproduces, you have found a bug in SQL Server. You should investigate
>> for hardware errors. See
>> general recommendations at
>> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp. Also,
>> remember that tempdb is re-created at each startup...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "alex" <alex@.discussions.microsoft.com> wrote in message
>> news:8D8B81BF-1C2A-4088-ACAE-1609A0FC5223@.microsoft.com...
>> > Thanks. Did that and got:
>> > ....
>> > CHECKDB found 0 allocation errors and 0 consistency errors in database
>> > 'tempdb'.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> > Looks like database itself is OK. Can the problem be a result of some
>> > application's
>> > activities like the following scenario:
>> >
>> > main stored procedure calls, withing a cycle, underlying stored
>> > procedure
>> > and this
>> > procedure contains the following:
>> >
>> > create table #tmp(....)
>> > ....
>> > drop table #tmp
>> >
>> > It looks like after few repetitions of this scenario (number of the
>> > iterations varies)
>> > breakdown occurs. After replacement of the temp table with a regular
>> > one,
>> > situation seems to be remedied.
>> >
>> > Can it be that tempdb may have problems with a repeated re-creation of
>> > a
>> > dropped temp. table within a single session?
>> >
>> > Thanks
>> >
>> > "Donna Lambert" wrote:
>> >
>> >> Also check your backups (restore to a different sql server, run dbcc
>> >> checkdb
>> >> on those). Find a backup that does not contain the corruption.
>> >> You may lose data.
>> >> If DBCC reports the minimal repair level is "repair_allow_data_loss"
>> >> it's
>> >> not kidding.
>> >> If you run that, be prepared to lose some data. It might be a row, a
>> >> page,
>> >> whole tables. There's really no telling.
>> >> Going back to a known good state is often a better alternative. At
>> >> least
>> >> you know what data you lost.
>> >> :D
>> >> Donna Lambert
>> >>
>> >>
>> >> "Mike Epprecht (SQL MVP)" wrote:
>> >>
>> >> > Hi
>> >> >
>> >> > Looks like corruption. Either your RAM or disk could ber causing
>> >> > this.
>> >> >
>> >> > Run DBCC CHECKDB againt all your DB's on the server and see what
>> >> > the output
>> >> > is.
>> >> >
>> >> > Regards
>> >> > --
>> >> > Mike Epprecht, Microsoft SQL Server MVP
>> >> > Zurich, Switzerland
>> >> >
>> >> > MVP Program: http://www.microsoft.com/mvp
>> >> >
>> >> > Blog: http://www.msmvps.com/epprecht/
>> >> >
>> >> >
>> >> >
>> >> > "alex" wrote:
>> >> >
>> >> > > Hi,
>> >> > > I'm running SQL 2000 and server quite often (3 times within the
>> >> > > last week)
>> >> > > fails on insert operation with the messages like:
>> >> > >
>> >> > > "Attempt to fetch logical page (1:202685) in database 'tempdb'
>> >> > > belongs to
>> >> > > object '-948063835' not to object #tmp
>> >> > >
>> >> > > Connection Broken"
>> >> > >
>> >> > > Similar messages were for the tables in the completely different
>> >> > > databases.
>> >> > >
>> >> > > Does not look like this is directly related to the table's size.
>> >> > >
>> >> > > Thanks
>> >> > >
>> >> > >
>>
>
I'm running SQL 2000 and server quite often (3 times within the last week)
fails on insert operation with the messages like:
"Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
object '-948063835' not to object #tmp
Connection Broken"
Similar messages were for the tables in the completely different databases.
Does not look like this is directly related to the table's size.
ThanksHi
Looks like corruption. Either your RAM or disk could ber causing this.
Run DBCC CHECKDB againt all your DB's on the server and see what the output
is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"alex" wrote:
> Hi,
> I'm running SQL 2000 and server quite often (3 times within the last week)
> fails on insert operation with the messages like:
> "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
> object '-948063835' not to object #tmp
> Connection Broken"
> Similar messages were for the tables in the completely different databases.
> Does not look like this is directly related to the table's size.
> Thanks
>|||Also check your backups (restore to a different sql server, run dbcc checkdb
on those). Find a backup that does not contain the corruption.
You may lose data.
If DBCC reports the minimal repair level is "repair_allow_data_loss" it's
not kidding.
If you run that, be prepared to lose some data. It might be a row, a page,
whole tables. There's really no telling.
Going back to a known good state is often a better alternative. At least
you know what data you lost.
:D
Donna Lambert
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Looks like corruption. Either your RAM or disk could ber causing this.
> Run DBCC CHECKDB againt all your DB's on the server and see what the output
> is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "alex" wrote:
> > Hi,
> > I'm running SQL 2000 and server quite often (3 times within the last week)
> > fails on insert operation with the messages like:
> >
> > "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
> > object '-948063835' not to object #tmp
> >
> > Connection Broken"
> >
> > Similar messages were for the tables in the completely different databases.
> >
> > Does not look like this is directly related to the table's size.
> >
> > Thanks
> >
> >|||Thanks. Did that and got:
....
CHECKDB found 0 allocation errors and 0 consistency errors in database
'tempdb'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Looks like database itself is OK. Can the problem be a result of some
application's
activities like the following scenario:
main stored procedure calls, withing a cycle, underlying stored procedure
and this
procedure contains the following:
create table #tmp(....)
....
drop table #tmp
It looks like after few repetitions of this scenario (number of the
iterations varies)
breakdown occurs. After replacement of the temp table with a regular one,
situation seems to be remedied.
Can it be that tempdb may have problems with a repeated re-creation of a
dropped temp. table within a single session?
Thanks
"Donna Lambert" wrote:
> Also check your backups (restore to a different sql server, run dbcc checkdb
> on those). Find a backup that does not contain the corruption.
> You may lose data.
> If DBCC reports the minimal repair level is "repair_allow_data_loss" it's
> not kidding.
> If you run that, be prepared to lose some data. It might be a row, a page,
> whole tables. There's really no telling.
> Going back to a known good state is often a better alternative. At least
> you know what data you lost.
> :D
> Donna Lambert
>
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > Looks like corruption. Either your RAM or disk could ber causing this.
> >
> > Run DBCC CHECKDB againt all your DB's on the server and see what the output
> > is.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> >
> >
> > "alex" wrote:
> >
> > > Hi,
> > > I'm running SQL 2000 and server quite often (3 times within the last week)
> > > fails on insert operation with the messages like:
> > >
> > > "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
> > > object '-948063835' not to object #tmp
> > >
> > > Connection Broken"
> > >
> > > Similar messages were for the tables in the completely different databases.
> > >
> > > Does not look like this is directly related to the table's size.
> > >
> > > Thanks
> > >
> > >|||> Can the problem be a result of some
> application's
> activities like the following scenario:
Basically, no. No TSQL code should be able to corrupt a database. If you can find such code that
reproduces, you have found a bug in SQL Server. You should investigate for hardware errors. See
general recommendations at http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp. Also,
remember that tempdb is re-created at each startup...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"alex" <alex@.discussions.microsoft.com> wrote in message
news:8D8B81BF-1C2A-4088-ACAE-1609A0FC5223@.microsoft.com...
> Thanks. Did that and got:
> ....
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'tempdb'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Looks like database itself is OK. Can the problem be a result of some
> application's
> activities like the following scenario:
> main stored procedure calls, withing a cycle, underlying stored procedure
> and this
> procedure contains the following:
> create table #tmp(....)
> ....
> drop table #tmp
> It looks like after few repetitions of this scenario (number of the
> iterations varies)
> breakdown occurs. After replacement of the temp table with a regular one,
> situation seems to be remedied.
> Can it be that tempdb may have problems with a repeated re-creation of a
> dropped temp. table within a single session?
> Thanks
> "Donna Lambert" wrote:
>> Also check your backups (restore to a different sql server, run dbcc checkdb
>> on those). Find a backup that does not contain the corruption.
>> You may lose data.
>> If DBCC reports the minimal repair level is "repair_allow_data_loss" it's
>> not kidding.
>> If you run that, be prepared to lose some data. It might be a row, a page,
>> whole tables. There's really no telling.
>> Going back to a known good state is often a better alternative. At least
>> you know what data you lost.
>> :D
>> Donna Lambert
>>
>> "Mike Epprecht (SQL MVP)" wrote:
>> > Hi
>> >
>> > Looks like corruption. Either your RAM or disk could ber causing this.
>> >
>> > Run DBCC CHECKDB againt all your DB's on the server and see what the output
>> > is.
>> >
>> > Regards
>> > --
>> > Mike Epprecht, Microsoft SQL Server MVP
>> > Zurich, Switzerland
>> >
>> > MVP Program: http://www.microsoft.com/mvp
>> >
>> > Blog: http://www.msmvps.com/epprecht/
>> >
>> >
>> >
>> > "alex" wrote:
>> >
>> > > Hi,
>> > > I'm running SQL 2000 and server quite often (3 times within the last week)
>> > > fails on insert operation with the messages like:
>> > >
>> > > "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
>> > > object '-948063835' not to object #tmp
>> > >
>> > > Connection Broken"
>> > >
>> > > Similar messages were for the tables in the completely different databases.
>> > >
>> > > Does not look like this is directly related to the table's size.
>> > >
>> > > Thanks
>> > >
>> > >|||Thanks but I did not say that DB was corrupted: DBCC CHECKDATABASE indicated
that there are no problems.
Is corruption the only reason for getting Msg 605?
"Tibor Karaszi" wrote:
> > Can the problem be a result of some
> > application's
> > activities like the following scenario:
> Basically, no. No TSQL code should be able to corrupt a database. If you can find such code that
> reproduces, you have found a bug in SQL Server. You should investigate for hardware errors. See
> general recommendations at http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp. Also,
> remember that tempdb is re-created at each startup...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "alex" <alex@.discussions.microsoft.com> wrote in message
> news:8D8B81BF-1C2A-4088-ACAE-1609A0FC5223@.microsoft.com...
> > Thanks. Did that and got:
> > ....
> > CHECKDB found 0 allocation errors and 0 consistency errors in database
> > 'tempdb'.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> > Looks like database itself is OK. Can the problem be a result of some
> > application's
> > activities like the following scenario:
> >
> > main stored procedure calls, withing a cycle, underlying stored procedure
> > and this
> > procedure contains the following:
> >
> > create table #tmp(....)
> > ....
> > drop table #tmp
> >
> > It looks like after few repetitions of this scenario (number of the
> > iterations varies)
> > breakdown occurs. After replacement of the temp table with a regular one,
> > situation seems to be remedied.
> >
> > Can it be that tempdb may have problems with a repeated re-creation of a
> > dropped temp. table within a single session?
> >
> > Thanks
> >
> > "Donna Lambert" wrote:
> >
> >> Also check your backups (restore to a different sql server, run dbcc checkdb
> >> on those). Find a backup that does not contain the corruption.
> >> You may lose data.
> >> If DBCC reports the minimal repair level is "repair_allow_data_loss" it's
> >> not kidding.
> >> If you run that, be prepared to lose some data. It might be a row, a page,
> >> whole tables. There's really no telling.
> >> Going back to a known good state is often a better alternative. At least
> >> you know what data you lost.
> >> :D
> >> Donna Lambert
> >>
> >>
> >> "Mike Epprecht (SQL MVP)" wrote:
> >>
> >> > Hi
> >> >
> >> > Looks like corruption. Either your RAM or disk could ber causing this.
> >> >
> >> > Run DBCC CHECKDB againt all your DB's on the server and see what the output
> >> > is.
> >> >
> >> > Regards
> >> > --
> >> > Mike Epprecht, Microsoft SQL Server MVP
> >> > Zurich, Switzerland
> >> >
> >> > MVP Program: http://www.microsoft.com/mvp
> >> >
> >> > Blog: http://www.msmvps.com/epprecht/
> >> >
> >> >
> >> >
> >> > "alex" wrote:
> >> >
> >> > > Hi,
> >> > > I'm running SQL 2000 and server quite often (3 times within the last week)
> >> > > fails on insert operation with the messages like:
> >> > >
> >> > > "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
> >> > > object '-948063835' not to object #tmp
> >> > >
> >> > > Connection Broken"
> >> > >
> >> > > Similar messages were for the tables in the completely different databases.
> >> > >
> >> > > Does not look like this is directly related to the table's size.
> >> > >
> >> > > Thanks
> >> > >
> >> > >
>|||Probably the corruption disappeared since objects are created and dropped all the time in tempdb.
Also, you possibly re-started SQL server in between so tempdb was rebuilt. However, you have had a
corruption, and that it something you should investigate further.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"alex" <alex@.discussions.microsoft.com> wrote in message
news:E28034E7-9F14-46EB-B491-CB690F52E0D0@.microsoft.com...
> Thanks but I did not say that DB was corrupted: DBCC CHECKDATABASE indicated
> that there are no problems.
> Is corruption the only reason for getting Msg 605?
> "Tibor Karaszi" wrote:
>> > Can the problem be a result of some
>> > application's
>> > activities like the following scenario:
>> Basically, no. No TSQL code should be able to corrupt a database. If you can find such code that
>> reproduces, you have found a bug in SQL Server. You should investigate for hardware errors. See
>> general recommendations at http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp. Also,
>> remember that tempdb is re-created at each startup...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "alex" <alex@.discussions.microsoft.com> wrote in message
>> news:8D8B81BF-1C2A-4088-ACAE-1609A0FC5223@.microsoft.com...
>> > Thanks. Did that and got:
>> > ....
>> > CHECKDB found 0 allocation errors and 0 consistency errors in database
>> > 'tempdb'.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> > Looks like database itself is OK. Can the problem be a result of some
>> > application's
>> > activities like the following scenario:
>> >
>> > main stored procedure calls, withing a cycle, underlying stored procedure
>> > and this
>> > procedure contains the following:
>> >
>> > create table #tmp(....)
>> > ....
>> > drop table #tmp
>> >
>> > It looks like after few repetitions of this scenario (number of the
>> > iterations varies)
>> > breakdown occurs. After replacement of the temp table with a regular one,
>> > situation seems to be remedied.
>> >
>> > Can it be that tempdb may have problems with a repeated re-creation of a
>> > dropped temp. table within a single session?
>> >
>> > Thanks
>> >
>> > "Donna Lambert" wrote:
>> >
>> >> Also check your backups (restore to a different sql server, run dbcc checkdb
>> >> on those). Find a backup that does not contain the corruption.
>> >> You may lose data.
>> >> If DBCC reports the minimal repair level is "repair_allow_data_loss" it's
>> >> not kidding.
>> >> If you run that, be prepared to lose some data. It might be a row, a page,
>> >> whole tables. There's really no telling.
>> >> Going back to a known good state is often a better alternative. At least
>> >> you know what data you lost.
>> >> :D
>> >> Donna Lambert
>> >>
>> >>
>> >> "Mike Epprecht (SQL MVP)" wrote:
>> >>
>> >> > Hi
>> >> >
>> >> > Looks like corruption. Either your RAM or disk could ber causing this.
>> >> >
>> >> > Run DBCC CHECKDB againt all your DB's on the server and see what the output
>> >> > is.
>> >> >
>> >> > Regards
>> >> > --
>> >> > Mike Epprecht, Microsoft SQL Server MVP
>> >> > Zurich, Switzerland
>> >> >
>> >> > MVP Program: http://www.microsoft.com/mvp
>> >> >
>> >> > Blog: http://www.msmvps.com/epprecht/
>> >> >
>> >> >
>> >> >
>> >> > "alex" wrote:
>> >> >
>> >> > > Hi,
>> >> > > I'm running SQL 2000 and server quite often (3 times within the last week)
>> >> > > fails on insert operation with the messages like:
>> >> > >
>> >> > > "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
>> >> > > object '-948063835' not to object #tmp
>> >> > >
>> >> > > Connection Broken"
>> >> > >
>> >> > > Similar messages were for the tables in the completely different databases.
>> >> > >
>> >> > > Does not look like this is directly related to the table's size.
>> >> > >
>> >> > > Thanks
>> >> > >
>> >> > >
>>|||Also, CHECKDB doesn't check everything in TEMPDB so may not pick up on the
problem you have. You also can't run repair on TEMPDB as its impossible to
put TEMPDB into single_user mode (prerequisite for running repair).
If you can repro the problem, you should call product support
(http://support.microsoft.com)
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eBmRC1EbFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Probably the corruption disappeared since objects are created and dropped
> all the time in tempdb. Also, you possibly re-started SQL server in
> between so tempdb was rebuilt. However, you have had a corruption, and
> that it something you should investigate further.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "alex" <alex@.discussions.microsoft.com> wrote in message
> news:E28034E7-9F14-46EB-B491-CB690F52E0D0@.microsoft.com...
>> Thanks but I did not say that DB was corrupted: DBCC CHECKDATABASE
>> indicated
>> that there are no problems.
>> Is corruption the only reason for getting Msg 605?
>> "Tibor Karaszi" wrote:
>> > Can the problem be a result of some
>> > application's
>> > activities like the following scenario:
>> Basically, no. No TSQL code should be able to corrupt a database. If you
>> can find such code that
>> reproduces, you have found a bug in SQL Server. You should investigate
>> for hardware errors. See
>> general recommendations at
>> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp. Also,
>> remember that tempdb is re-created at each startup...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "alex" <alex@.discussions.microsoft.com> wrote in message
>> news:8D8B81BF-1C2A-4088-ACAE-1609A0FC5223@.microsoft.com...
>> > Thanks. Did that and got:
>> > ....
>> > CHECKDB found 0 allocation errors and 0 consistency errors in database
>> > 'tempdb'.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> > Looks like database itself is OK. Can the problem be a result of some
>> > application's
>> > activities like the following scenario:
>> >
>> > main stored procedure calls, withing a cycle, underlying stored
>> > procedure
>> > and this
>> > procedure contains the following:
>> >
>> > create table #tmp(....)
>> > ....
>> > drop table #tmp
>> >
>> > It looks like after few repetitions of this scenario (number of the
>> > iterations varies)
>> > breakdown occurs. After replacement of the temp table with a regular
>> > one,
>> > situation seems to be remedied.
>> >
>> > Can it be that tempdb may have problems with a repeated re-creation of
>> > a
>> > dropped temp. table within a single session?
>> >
>> > Thanks
>> >
>> > "Donna Lambert" wrote:
>> >
>> >> Also check your backups (restore to a different sql server, run dbcc
>> >> checkdb
>> >> on those). Find a backup that does not contain the corruption.
>> >> You may lose data.
>> >> If DBCC reports the minimal repair level is "repair_allow_data_loss"
>> >> it's
>> >> not kidding.
>> >> If you run that, be prepared to lose some data. It might be a row, a
>> >> page,
>> >> whole tables. There's really no telling.
>> >> Going back to a known good state is often a better alternative. At
>> >> least
>> >> you know what data you lost.
>> >> :D
>> >> Donna Lambert
>> >>
>> >>
>> >> "Mike Epprecht (SQL MVP)" wrote:
>> >>
>> >> > Hi
>> >> >
>> >> > Looks like corruption. Either your RAM or disk could ber causing
>> >> > this.
>> >> >
>> >> > Run DBCC CHECKDB againt all your DB's on the server and see what
>> >> > the output
>> >> > is.
>> >> >
>> >> > Regards
>> >> > --
>> >> > Mike Epprecht, Microsoft SQL Server MVP
>> >> > Zurich, Switzerland
>> >> >
>> >> > MVP Program: http://www.microsoft.com/mvp
>> >> >
>> >> > Blog: http://www.msmvps.com/epprecht/
>> >> >
>> >> >
>> >> >
>> >> > "alex" wrote:
>> >> >
>> >> > > Hi,
>> >> > > I'm running SQL 2000 and server quite often (3 times within the
>> >> > > last week)
>> >> > > fails on insert operation with the messages like:
>> >> > >
>> >> > > "Attempt to fetch logical page (1:202685) in database 'tempdb'
>> >> > > belongs to
>> >> > > object '-948063835' not to object #tmp
>> >> > >
>> >> > > Connection Broken"
>> >> > >
>> >> > > Similar messages were for the tables in the completely different
>> >> > > databases.
>> >> > >
>> >> > > Does not look like this is directly related to the table's size.
>> >> > >
>> >> > > Thanks
>> >> > >
>> >> > >
>>
>
Monday, March 12, 2012
Msg 16958: Could not complete cursor operation
Got this error:
Msg 16958:
"Could not complete cursor operation because the set
options have changed since the cursor was declared."
The only thing I did was change the Recovery model to
SIMPLE from FULL. Apparently a cursor was open prior to
the change?
Knowledge Base turned up empty.
Thanks,
DanWell -- I didn't find much about this error even when I "googled" it.
Some people have reported that re-starting SQL Server fixes this.
In my case, it did NOT.
However, I was able to get this to work again by applying the following
setting:
SET ARITHABORT ON
"Dan Carollo" <d.carollo@.verizon.net> wrote in message
news:088401c3db1f$b0c0a470$a101280a@.phx.gbl...
Msg 16958:
"Could not complete cursor operation because the set
options have changed since the cursor was declared."
The only thing I did was change the Recovery model to
SIMPLE from FULL. Apparently a cursor was open prior to
the change?
Knowledge Base turned up empty.
Thanks,
DanWell -- I didn't find much about this error even when I "googled" it.
Some people have reported that re-starting SQL Server fixes this.
In my case, it did NOT.
However, I was able to get this to work again by applying the following
setting:
SET ARITHABORT ON
"Dan Carollo" <d.carollo@.verizon.net> wrote in message
news:088401c3db1f$b0c0a470$a101280a@.phx.gbl...
quote:
> Got this error:
> Msg 16958:
> "Could not complete cursor operation because the set
> options have changed since the cursor was declared."
>
> The only thing I did was change the Recovery model to
> SIMPLE from FULL. Apparently a cursor was open prior to
> the change?
> Knowledge Base turned up empty.
> Thanks,
> Dan
Msg 16958: Could not complete cursor operation
Got this error:
Msg 16958:
"Could not complete cursor operation because the set
options have changed since the cursor was declared."
The only thing I did was change the Recovery model to
SIMPLE from FULL. Apparently a cursor was open prior to
the change?
Knowledge Base turned up empty.
Thanks,
DanWell -- I didn't find much about this error even when I "googled" it.
Some people have reported that re-starting SQL Server fixes this.
In my case, it did NOT.
However, I was able to get this to work again by applying the following
setting:
SET ARITHABORT ON
"Dan Carollo" <d.carollo@.verizon.net> wrote in message
news:088401c3db1f$b0c0a470$a101280a@.phx.gbl...
> Got this error:
> Msg 16958:
> "Could not complete cursor operation because the set
> options have changed since the cursor was declared."
>
> The only thing I did was change the Recovery model to
> SIMPLE from FULL. Apparently a cursor was open prior to
> the change?
> Knowledge Base turned up empty.
> Thanks,
> Dan
Msg 16958:
"Could not complete cursor operation because the set
options have changed since the cursor was declared."
The only thing I did was change the Recovery model to
SIMPLE from FULL. Apparently a cursor was open prior to
the change?
Knowledge Base turned up empty.
Thanks,
DanWell -- I didn't find much about this error even when I "googled" it.
Some people have reported that re-starting SQL Server fixes this.
In my case, it did NOT.
However, I was able to get this to work again by applying the following
setting:
SET ARITHABORT ON
"Dan Carollo" <d.carollo@.verizon.net> wrote in message
news:088401c3db1f$b0c0a470$a101280a@.phx.gbl...
> Got this error:
> Msg 16958:
> "Could not complete cursor operation because the set
> options have changed since the cursor was declared."
>
> The only thing I did was change the Recovery model to
> SIMPLE from FULL. Apparently a cursor was open prior to
> the change?
> Knowledge Base turned up empty.
> Thanks,
> Dan
Subscribe to:
Posts (Atom)