deAttach database: lots

Showing posts with label lots. Show all posts

Tuesday, March 27, 2012

deadlocks after dbcc checkdb

sql2k sp3
Over the weekend I had severe corruption and needed to do DBCC with
repair_allow_data_loss. Since then Ive had tons of deadlocks on lots of
different tables. Any ideas as to why? I know I can trace them and figure
them out, but I was curious as to why this would start to happen after my
problems?
TIA, ChrisR.The only thing I can think of is that the repair dropped some pages. The
data now resides on fewer pages, fewer pages with the same number of locks =deadlock..
The problem with this thinking is that I do not know if DBCC repair moves
rows or simply drops bad page links..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"ChrisR" <noemail@.bla.com> wrote in message
news:uA12OEBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
> sql2k sp3
> Over the weekend I had severe corruption and needed to do DBCC with
> repair_allow_data_loss. Since then Ive had tons of deadlocks on lots of
> different tables. Any ideas as to why? I know I can trace them and figure
> them out, but I was curious as to why this would start to happen after my
> problems?
> TIA, ChrisR.
>|||How about if repair removed a whole index in order to repair? That could increase deadlock due to
longer execution time = overlap. Or? I don't know whether repair does drop a whole index, though...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eRZ6oeBUFHA.2172@.tk2msftngp13.phx.gbl...
> The only thing I can think of is that the repair dropped some pages. The
> data now resides on fewer pages, fewer pages with the same number of locks => deadlock..
> The problem with this thinking is that I do not know if DBCC repair moves
> rows or simply drops bad page links..
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uA12OEBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
>> sql2k sp3
>> Over the weekend I had severe corruption and needed to do DBCC with
>> repair_allow_data_loss. Since then Ive had tons of deadlocks on lots of
>> different tables. Any ideas as to why? I know I can trace them and figure
>> them out, but I was curious as to why this would start to happen after my
>> problems?
>> TIA, ChrisR.
>>
>|||Assuming you're on SQL 2000, repair won't drop whole indexes (only rebuild
them) or do anything that would cause deadlocks. However, it probably had to
delete some pages so whatever application logic was inherent in the database
is now broken (i.e. if your app relied on certain values being present in
multiple tables, that may not be the case any more).
Did you study the output of repair to work out roughly what it dropped? I
take it your backup was broken too which is why you had to run repair rather
than restoring your backup? Did you work out why the corruption happened in
the first place so you can ensure it doesn't happen again?
Regards
--
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:ecDwqnBUFHA.2928@.TK2MSFTNGP09.phx.gbl...
> How about if repair removed a whole index in order to repair? That could
increase deadlock due to
> longer execution time = overlap. Or? I don't know whether repair does drop
a whole index, though...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:eRZ6oeBUFHA.2172@.tk2msftngp13.phx.gbl...
> > The only thing I can think of is that the repair dropped some pages. The
> > data now resides on fewer pages, fewer pages with the same number of
locks => > deadlock..
> >
> > The problem with this thinking is that I do not know if DBCC repair
moves
> > rows or simply drops bad page links..
> >
> >
> > --
> > Wayne Snyder MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > (Please respond only to the newsgroup.)
> >
> > I support the Professional Association for SQL Server ( PASS) and it's
> > community of SQL Professionals.
> > "ChrisR" <noemail@.bla.com> wrote in message
> > news:uA12OEBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
> >> sql2k sp3
> >>
> >> Over the weekend I had severe corruption and needed to do DBCC with
> >> repair_allow_data_loss. Since then Ive had tons of deadlocks on lots of
> >> different tables. Any ideas as to why? I know I can trace them and
figure
> >> them out, but I was curious as to why this would start to happen after
my
> >> problems?
> >>
> >> TIA, ChrisR.
> >>
> >>
> >
> >
>|||Hardware issues were the cause. Yes the backups were bad. MS is now
evaluating the output. Thanks to all who helped.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OXOmsFPUFHA.1148@.tk2msftngp13.phx.gbl...
> Assuming you're on SQL 2000, repair won't drop whole indexes (only rebuild
> them) or do anything that would cause deadlocks. However, it probably had
> to
> delete some pages so whatever application logic was inherent in the
> database
> is now broken (i.e. if your app relied on certain values being present in
> multiple tables, that may not be the case any more).
> Did you study the output of repair to work out roughly what it dropped? I
> take it your backup was broken too which is why you had to run repair
> rather
> than restoring your backup? Did you work out why the corruption happened
> in
> the first place so you can ensure it doesn't happen again?
> Regards
> --
> 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:ecDwqnBUFHA.2928@.TK2MSFTNGP09.phx.gbl...
>> How about if repair removed a whole index in order to repair? That could
> increase deadlock due to
>> longer execution time = overlap. Or? I don't know whether repair does
>> drop
> a whole index, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> news:eRZ6oeBUFHA.2172@.tk2msftngp13.phx.gbl...
>> > The only thing I can think of is that the repair dropped some pages.
>> > The
>> > data now resides on fewer pages, fewer pages with the same number of
> locks =>> > deadlock..
>> >
>> > The problem with this thinking is that I do not know if DBCC repair
> moves
>> > rows or simply drops bad page links..
>> >
>> >
>> > --
>> > Wayne Snyder MCDBA, SQL Server MVP
>> > Mariner, Charlotte, NC
>> > (Please respond only to the newsgroup.)
>> >
>> > I support the Professional Association for SQL Server ( PASS) and it's
>> > community of SQL Professionals.
>> > "ChrisR" <noemail@.bla.com> wrote in message
>> > news:uA12OEBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
>> >> sql2k sp3
>> >>
>> >> Over the weekend I had severe corruption and needed to do DBCC with
>> >> repair_allow_data_loss. Since then Ive had tons of deadlocks on lots
>> >> of
>> >> different tables. Any ideas as to why? I know I can trace them and
> figure
>> >> them out, but I was curious as to why this would start to happen after
> my
>> >> problems?
>> >>
>> >> TIA, ChrisR.
>> >>
>> >>
>> >
>> >
>>
>

deadlocks after dbcc checkdb

Deadlocks

Hello everyone,

For a while I have been experiencing deadlocks on my tables.
I am not sure how to get this fixed.

I have done lots of research but havent come to a result as yet.

Here is a bit of background on the apps that are cause these deadlocks,

I work at an ISP, we run about 8 radius servers, which open up a connection to our database to authenticate users and to insert accounting records. We also use radius (radiator) to give us information about our users online, meaning that our modem box servers where users dial in to send update packets every 5 minutes for each user which radiator handles and updates our database.

We then have another application which goes through all these account records and "processes" them, Meaning that it will find the stop records for a particular user, then grab the start record , then bill the customer, insert a record to our history table and then delete those records. These 2 (well in actual fact 9 programs because of our 8 radius servers) programs are causing deadlocks, I dont know how to fix this,

Are there any steps I can take to stop this, minimise this. Any help will be very helpful :)

thanks guysRefer to this link (http://www.sql-server-performance.com/lock_contention_tamed_article.asp) about lock contentions and how to tame them.

Tuesday, February 14, 2012

DBException (Communication link failure)

Hi,
with SQL 2000 and Windows 2000, we're getting lots of this error message
"Communication link failure" from our C++ application.
Any idea why?

Thanks,neo (second714@.hotmail.com) writes:
> with SQL 2000 and Windows 2000, we're getting lots of this error message
> "Communication link failure" from our C++ application.
> Any idea why?

There are many possibilities.

One is of course that you have a network problem.

Another is that you have some SQL code that cause the SQL Server process
for the connection to crash - access violation, stack overflow - in which
case SQL Server closes the connection quite brutally. In this case, the
SQL Server error log will include a stack dump, which in time is correlated
with the application being disconnected. Such a crash, by the way, is due
to bugs in SQL Server, not in your SQL code.

There are probably several other reasons, but with the miniscule of
information you have posted, I stop here.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your reply.
We have two SQL server machines both with problems.
one with Windows 2000 SP3 and SQL 2000 SP3
another one with XP and SQL 2000 SP3
We'll get the latest OS SP for both machines
and have to wait a couple of date to get IT.

I didn't want to put this information before
because I wanted to get all the possible answers.

we'll see after OS update and if it's not fixed,
then I'll come back to this news group.

Thanks a lot,

Tuesday, March 27, 2012

deadlocks after dbcc checkdb

deadlocks after dbcc checkdb

deadlocks after dbcc checkdb

Deadlocks

Tuesday, February 14, 2012

DBException (Communication link failure)

deAttach database

Blog Archive

About Me