Something on SLQ I was pondering about

Question

Post reply

Something on SLQ I was pondering about

veer

SSC Enthusiast

Points: 177
More actions
May 13, 2005 at 10:58 am

#110438

I have been given a 300 GB Database by client for RCA after it had problem in Insert /Updates etc any DML would never commit.

The Database stopped working and no update/inserts to any table was the problem.

even any new Table created behaves in the same manner

for example

I created a simple table with one column and tried to inserta value in it But it never ever completes...and Locks get esclated to DB Exclusive lock and nothing commits actually...

create table veer

(

testcol varchar(50)

)

begin tran

insert into veer values ('sdfs')

commit

Process ID Lock Type Mode Status Owner Index Resource

53 FIL U GRANT Sess Veer 1:0:d

53 DB S GRANT Sess Veer

53 DB NULL GRANT Xact Veer [BULK-OP-LOG]

53 IDX X GRANT Xact Veer IDX: 33:19041914

53 DB NULL GRANT Xact Veer [BULK-OP-DB]

I ahve used DBCC CheckDB and it completed with tou Errors.

SP_Configure has advance levels set to normal ones...

DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES -- All good results

SP_HelpDb --- no issues

Service Restarted OPtions -- All done and nothing cam eout

I am not able to figure out the problem...Please help me making RCA

Please send me individual help at

veer_w@hotmail.com

v-veerw@revenuescience.com

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

David Webb-CDS

SSCoach

Points: 17398

May 13, 2005 at 1:04 pm

#558831

What version of the server are you running?

Does anything show up in the error log when this happens?

What database options are set?

Can you issue a checkpoint, or does that hang also?

And then again, I might be wrong ...
David Webb

veer SSC Enthusiast Points: 177 More actions · Answer 2

I can easily issue CHECKPOINT and no errors occur

SP_CONFIGURE

name minimum maximum config_value run_value

----------------------------------- ----------- ----------- ------------ -----------

affinity mask -2147483648 2147483647 0 0

allow updates 0 1 1 1

awe enabled 0 1 1 1

c2 audit mode 0 1 0 0

cost threshold for parallelism 0 32767 5 5

Cross DB Ownership Chaining 0 1 0 0

cursor threshold -1 2147483647 -1 -1

default full-text language 0 2147483647 1033 1033

default language 0 9999 0 0

fill factor (%) 0 100 0 0

index create memory (KB) 704 2147483647 0 0

lightweight pooling 0 1 0 0

locks 5000 2147483647 0 0

max degree of parallelism 0 32 1 1

max server memory (MB) 4 2147483647 6000 6000

max text repl size (B) 0 2147483647 65536 65536

max worker threads 32 32767 255 255

media retention 0 365 0 0

min memory per query (KB) 512 2147483647 1024 1024

min server memory (MB) 0 2147483647 0 0

nested triggers 0 1 1 1

network packet size (B) 512 65536 4096 4096

open objects 0 2147483647 0 0

priority boost 0 1 0 0

query governor cost limit 0 2147483647 0 0

query wait (s) -1 2147483647 -1 -1

recovery interval (min) 0 32767 5 5

remote access 0 1 1 1

remote login timeout (s) 0 2147483647 20 20

remote proc trans 0 1 0 0

remote query timeout (s) 0 2147483647 0 0

scan for startup procs 0 1 1 1

set working set size 0 1 0 0

show advanced options 0 1 1 1

two digit year cutoff 1753 9999 2049 2049

user connections 0 32767 0 0

user options 0 32767 16448 16448

veer SSC Enthusiast Points: 177 More actions · Answer 3

It is a nice case study and thread is Exciting ... Current Issues

1. Current SP_DBOPTION is showing DB to be Offline and Readonly and changing it still doesnt change it.

2. Sp_HelpDb shows DB to be online and read//Write both.

3 Still we cant execute any DML commands...

Something a subject for PhD.

We are researching on this issue and comming to know more important things..