dbcc opentran is not helping me on "waiting for backend connection"

What else could be causing this?
I keep getting waiting on backend connection on a lot of my log readers
connected to this one publisher. The publisher has no blocking and was
just rebooted last night. Everything on the publisher is working fine.
however. I get "waiting for backend connection" and "waiting for log
backup" a lot on the errors of the log reader.
I have also had to had to put the -readbatchsize level to a (1) on all
of these log readers connected to this publisher. Once i do this, they
at least start going 1 transaction at a time. These are not like huge
transactions.
dbcc opentran shows
Transaction information for database 'pet01'.
Replicated Transaction Information:
Oldest distributed LSN : (25212765:36114:75)
Oldest non-distributed LSN : (25212765:36145:1)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Any help would be appreciated.
-comb
Basically this is telling you that the log reader has transactions to write
to the distribution database.
I think your problem is with depleted buffers on SQL Server. You need to
reboot the box. You may be able to get away with merly stopping and starting
SQL Server.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"combfilter" <asdf@.adsf.com> wrote in message
news:MPG.1dd16198660765d99896c2@.news.newsreader.co m...
> What else could be causing this?
> I keep getting waiting on backend connection on a lot of my log readers
> connected to this one publisher. The publisher has no blocking and was
> just rebooted last night. Everything on the publisher is working fine.
> however. I get "waiting for backend connection" and "waiting for log
> backup" a lot on the errors of the log reader.
> I have also had to had to put the -readbatchsize level to a (1) on all
> of these log readers connected to this publisher. Once i do this, they
> at least start going 1 transaction at a time. These are not like huge
> transactions.
> dbcc opentran shows
> Transaction information for database 'pet01'.
> Replicated Transaction Information:
> Oldest distributed LSN : (25212765:36114:75)
> Oldest non-distributed LSN : (25212765:36145:1)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Any help would be appreciated.
> -comb
|||In article <ehBTGvw3FHA.1276@.TK2MSFTNGP09.phx.gbl>,
hilary.cotter@.gmail.com says...
> Basically this is telling you that the log reader has transactions to write
> to the distribution database.
> I think your problem is with depleted buffers on SQL Server. You need to
> reboot the box. You may be able to get away with merly stopping and starting
> SQL Server.
>
We rebooted it last night. It's part of a cluster, and we moved the
instance over and rebooted the box. What else should I check Hilary or
Paul?
tia.
-comb

DBCC INDEXDEFRAG causing blocking

I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
DBCC INDEXDEFRAG "does not hold locks long term and thus will not
block running queries or updates." But several SPIDs are blocked by
the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
5:436352769 [] (436352769 is the table whose index I am defragmenting)
and waittype of LCK_M_IS. These SPIDs are being blocked for a long
time (30+ minutes).
Any help would be appreciated.
This is on SQL Server 2000, sp4
Thanks!Known 'feature', with hotfix available.
http://support.microsoft.com/kb/907250
--
TheSQLGuru
President
Indicium Resources, Inc.
<sfglossolalia@.gmail.com> wrote in message
news:1179179563.268238.322690@.u30g2000hsc.googlegroups.com...
>I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
> DBCC INDEXDEFRAG "does not hold locks long term and thus will not
> block running queries or updates." But several SPIDs are blocked by
> the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
> 5:436352769 [] (436352769 is the table whose index I am defragmenting)
> and waittype of LCK_M_IS. These SPIDs are being blocked for a long
> time (30+ minutes).
> Any help would be appreciated.
> This is on SQL Server 2000, sp4
> Thanks!
>

DBCC INDEXDEFRAG causing blocking

I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
DBCC INDEXDEFRAG "does not hold locks long term and thus will not
block running queries or updates." But several SPIDs are blocked by
the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
5:436352769 [] (436352769 is the table whose index I am defragmenting)
and waittype of LCK_M_IS. These SPIDs are being blocked for a long
time (30+ minutes).
Any help would be appreciated.
This is on SQL Server 2000, sp4
Thanks!Known 'feature', with hotfix available.
http://support.microsoft.com/kb/907250
TheSQLGuru
President
Indicium Resources, Inc.
<sfglossolalia@.gmail.com> wrote in message
news:1179179563.268238.322690@.u30g2000hsc.googlegroups.com...
>I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
> DBCC INDEXDEFRAG "does not hold locks long term and thus will not
> block running queries or updates." But several SPIDs are blocked by
> the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
> 5:436352769 [] (436352769 is the table whose index I am defragmenting)
> and waittype of LCK_M_IS. These SPIDs are being blocked for a long
> time (30+ minutes).
> Any help would be appreciated.
> This is on SQL Server 2000, sp4
> Thanks!
>

DBCC inconsistency

We have a very minor problem with a db. It's not
affecting how the db runs, but it's causing BackupExec to
show red for the server every time it back up. Here is
the error.
Consistency checking magic
Consistency Check including Indexes.
There was a problem running the DBCC.
^ ^ ^ ^ ^
SQL Server returned the following error message:
Table Corrupt: Object ID 2042490355 (object '2042490355')
does not match between 'SYSCOMMENTS' and 'SYSOBJECTS'.
^ ^ ^ ^ ^
I'm told that I just need to reconcile this
object "2042490355" in those two db's using a simple sql
script. Can anyone tell me what that script is or where
to find the article for what I need? I've been searching
support for at least an hour.Shannon
Who told you just needed to reconcile this object in the two dbs? I have no
idea what someone might mean by 'reconcile' without knowing what the problem
is.
First of all, we're most likely talking about just ONE database, but there
are two system tables involved.
Sysobjects has one row for each object in the entire database, and
syscomments contains the textual definition for any objects with a TSQL code
definition such as stored procedures, functions, constraints, views, etc.
So make sure you are in the right database.
Then see what object the message is referring to:
SELECT object_name(2042490355)
Then run sp_helptext 'whatever that object name was'
You might try just dropping the object, and recreating it, but save the
definition before you do that.
Since the object is not a table (or so it seems from this evidence), you
don't have to worry about losing data.
Good luck
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Shannon" <srubin@.pec.com> wrote in message
news:006a01c3602a$482282a0$a401280a@.phx.gbl...
> We have a very minor problem with a db. It's not
> affecting how the db runs, but it's causing BackupExec to
> show red for the server every time it back up. Here is
> the error.
> Consistency checking magic
> Consistency Check including Indexes.
> There was a problem running the DBCC.
> ^ ^ ^ ^ ^
> SQL Server returned the following error message:
> Table Corrupt: Object ID 2042490355 (object '2042490355')
> does not match between 'SYSCOMMENTS' and 'SYSOBJECTS'.
>
> ^ ^ ^ ^ ^
> I'm told that I just need to reconcile this
> object "2042490355" in those two db's using a simple sql
> script. Can anyone tell me what that script is or where
> to find the article for what I need? I've been searching
> support for at least an hour.

DBCC DBREINDEX and diskspace

I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?
It will fail and the index rebuild will be rolled back. You need 1.2X the
size of the data in your table in free space in order to use DBREINDEX. If
you cannot get more space, consider using DBCC INDEXDEFRAG.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.co m...
I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?
|||This helps me out alot:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure admin_DBCCDBReindex
as
declare @.Tablename varchar(128),@.db varchar(128),@.exec varchar(256)
set nocount on
set @.db = (select db_name())
declare @.Table table(TableName varchar(128))
insert into @.Table
select so.name
from sysobjects so
where so.name not like 'sys%' and
so.name not like 'dt%' and
so.name not like 'tmp%' and
so.xtype = 'u'
order by so.name
declare mycur cursor for (select TableName from @.Table)
open mycur
fetch next from mycur into @.TableName
while @.@.fetch_status = 0
begin
set @.exec = 'dbcc dbreindex (''' + @.db + '.' + 'dbo.' + @.TableName + ''')'
exec (@.exec)
BACKUP LOG [tmpReindex] TO [tmp]
WITH INIT , NOUNLOAD , NAME = N'tmp log backup', NOSKIP , STATS = 10,
NOFORMAT
fetch next from mycur into @.TableName
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.co m...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?
|||As Tom suggests, consider DBCC INDEXDEFRAG. There's a whitepaper at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
that explains the difference between the two, how to choose between them,
performance comparison etc.
Let us know if you have any more questions after reading the whitepaper.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.co m...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?

DBCC DBREINDEX and diskspace

I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?It will fail and the index rebuild will be rolled back. You need 1.2X the
size of the data in your table in free space in order to use DBREINDEX. If
you cannot get more space, consider using DBCC INDEXDEFRAG.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?|||This helps me out alot:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure admin_DBCCDBReindex
as
declare @.Tablename varchar(128),@.db varchar(128),@.exec varchar(256)
set nocount on
set @.db = (select db_name())
declare @.Table table(TableName varchar(128))
insert into @.Table
select so.name
from sysobjects so
where so.name not like 'sys%' and
so.name not like 'dt%' and
so.name not like 'tmp%' and
so.xtype = 'u'
order by so.name
declare mycur cursor for (select TableName from @.Table)
open mycur
fetch next from mycur into @.TableName
while @.@.fetch_status = 0
begin
set @.exec = 'dbcc dbreindex (''' + @.db + '.' + 'dbo.' + @.TableName + ''')'
exec (@.exec)
BACKUP LOG [tmpReindex] TO [tmp]
WITH INIT , NOUNLOAD , NAME = N'tmp log backup', NOSKIP , STATS = 10,
NOFORMAT
fetch next from mycur into @.TableName
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?|||As Tom suggests, consider DBCC INDEXDEFRAG. There's a whitepaper at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
that explains the difference between the two, how to choose between them,
performance comparison etc.
Let us know if you have any more questions after reading the whitepaper.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?

DBCC DBREINDEX and diskspace

I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?It will fail and the index rebuild will be rolled back. You need 1.2X the
size of the data in your table in free space in order to use DBREINDEX. If
you cannot get more space, consider using DBCC INDEXDEFRAG.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?|||This helps me out alot:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure admin_DBCCDBReindex
as
declare @.Tablename varchar(128),@.db varchar(128),@.exec varchar(256)
set nocount on
set @.db = (select db_name())
declare @.Table table(TableName varchar(128))
insert into @.Table
select so.name
from sysobjects so
where so.name not like 'sys%' and
so.name not like 'dt%' and
so.name not like 'tmp%' and
so.xtype = 'u'
order by so.name
declare mycur cursor for (select TableName from @.Table)
open mycur
fetch next from mycur into @.TableName
while @.@.fetch_status = 0
begin
set @.exec = 'dbcc dbreindex (''' + @.db + '.' + 'dbo.' + @.TableName + ''')'
exec (@.exec)
BACKUP LOG [tmpReindex] TO [tmp]
WITH INIT , NOUNLOAD , NAME = N'tmp log backup', NOSKIP , STATS = 10,
NOFORMAT
fetch next from mycur into @.TableName
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?|||As Tom suggests, consider DBCC INDEXDEFRAG. There's a whitepaper at
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
that explains the difference between the two, how to choose between them,
performance comparison etc.
Let us know if you have any more questions after reading the whitepaper.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?