January 6, 2014 at 12:37 pm
Hi I am no DBA, but we are facing an issue with an application that eventually loops a piece of code which keeps writing records to the db until the whole db is choked.
A brief background to the environment.
We have an sql server 2008 instance hosting two dbs
1. PosDB1
2. PosDB2
Two separate instances of an almost identical application called
1. App1 writes to PosDB1
2. App2 writes to PosDB2
Now there is a bug at the application which loops and hence keeps inserting records to the db.
Eventually the PosDB2 becomes unavailable to app2 for insertions while App1 keeps working fine.
The only resolution we have currently at production before a new app release is to restart the sql db service
Please help in suggesting what can be the best practices to deal with such an issue/case?
I just ran a sp_who and the tab output is below:
SPIDStatusLoginHostNameBlkByDBNameCommandCPUTimeDiskIOLastBatchProgramNameSPIDREQUESTID
1BACKGROUND sa . .NULLRESOURCE MONITOR1501/6/2014 0:35 10
2BACKGROUND sa . .NULLXE TIMER 001/6/2014 0:35 20
3BACKGROUND sa . .NULLXE DISPATCHER 001/6/2014 0:35 30
4BACKGROUND sa . .NULLLAZY WRITER 76501/6/2014 0:35 40
5BACKGROUND sa . .NULLLOG WRITER 3573401/6/2014 0:35 50
6BACKGROUND sa . .NULLLOCK MONITOR 1501/6/2014 0:35 60
7BACKGROUND sa . .masterSIGNAL HANDLER 001/6/2014 0:35 70
8sleeping sa . .masterTASK MANAGER 001/6/2014 0:35 80
9BACKGROUND sa . .masterTRACE QUEUE TASK001/6/2014 0:35 90
10BACKGROUND sa . .masterBRKR TASK 17101/6/2014 0:35 100
11BACKGROUND sa . .accutechCHECKPOINT 85911471/6/2014 0:35 110
12BACKGROUND sa . .masterTASK MANAGER 001/6/2014 0:35 120
13BACKGROUND sa . .masterBRKR EVENT HNDLR0401/6/2014 0:35 130
14sleeping sa . .masterTASK MANAGER 001/6/2014 0:35 140
15sleeping sa . .masterTASK MANAGER 001/6/2014 0:35 150
16BACKGROUND sa . .masterBRKR TASK 001/6/2014 0:35 160
17BACKGROUND sa . .masterBRKR TASK 001/6/2014 0:35 170
18sleeping sa . .masterTASK MANAGER 001/6/2014 0:35 180
19sleeping sa . .masterTASK MANAGER 001/6/2014 0:35 190
20sleeping sa . .masterTASK MANAGER 001/6/2014 0:35 200
21sleeping sa . .masterTASK MANAGER 001/6/2014 0:35 210
22sleeping sa . .masterTASK MANAGER 001/6/2014 0:35 220
23sleeping sa . .masterTASK MANAGER 001/6/2014 0:35 230
24sleeping sa . .masterTASK MANAGER 001/6/2014 0:35 240
25sleeping sa . .masterTASK MANAGER 001/6/2014 0:35 250
26sleeping sa . .masterTASK MANAGER 001/6/2014 0:35 260
51RUNNABLE saYQPOSDBSVR01 .accutechANSELECT INTO 791071/6/2014 11:06Microsoft SQL Server Management Studio - Query510
52sleeping saYQPOSDBSVR01 .accutechAWAITING COMMAND31301/6/2014 11:06Microsoft SQL Server Management Studio - Query520
53SUSPENDED saYQMONITOR01 93accutechANINSERT 021031/6/2014 11:06.Net SqlClient Data Provider 530
54sleeping saYQMONITOR01 .accutechANAWAITING COMMAND1601/6/2014 11:04.Net SqlClient Data Provider 540
55SUSPENDED saYQMONITOR01 93accutechANINSERT 091411/6/2014 11:06.Net SqlClient Data Provider 550
56sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:04.Net SqlClient Data Provider 560
57sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:04.Net SqlClient Data Provider 570
58sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:04.Net SqlClient Data Provider 580
59sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:05.Net SqlClient Data Provider 590
60sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:06.Net SqlClient Data Provider 600
61sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:05.Net SqlClient Data Provider 610
62SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 620
63sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:02.Net SqlClient Data Provider 630
64SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 640
65SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 650
66SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 660
67SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 670
68sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:06.Net SqlClient Data Provider 680
69sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:04.Net SqlClient Data Provider 690
70SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 700
71SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 710
72SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 720
73SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 730
74SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 740
75sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:05.Net SqlClient Data Provider 750
76sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:04.Net SqlClient Data Provider 760
77sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:04.Net SqlClient Data Provider 770
78sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:04.Net SqlClient Data Provider 780
79sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:05.Net SqlClient Data Provider 790
80SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 800
81sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:04.Net SqlClient Data Provider 810
82SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 820
83sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:05.Net SqlClient Data Provider 830
84SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 840
85SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 850
86sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:02.Net SqlClient Data Provider 860
87sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:06.Net SqlClient Data Provider 870
88SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 880
89sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:06.Net SqlClient Data Provider 890
90sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:04.Net SqlClient Data Provider 900
92SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 920
93RUNNABLE saYQMONITOR01 .accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 930
94sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:02.Net SqlClient Data Provider 940
95sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:05.Net SqlClient Data Provider 950
96sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:05.Net SqlClient Data Provider 960
97sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:05.Net SqlClient Data Provider 970
98sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:05.Net SqlClient Data Provider 980
99sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:04.Net SqlClient Data Provider 990
100sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:06.Net SqlClient Data Provider 1000
101SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 1010
102sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:03.Net SqlClient Data Provider 1020
103SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 1030
104sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:05.Net SqlClient Data Provider 1040
105SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 1050
106SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 1060
107sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:03.Net SqlClient Data Provider 1070
108SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 1080
109sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:06.Net SqlClient Data Provider 1090
110SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 1100
111sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:05.Net SqlClient Data Provider 1110
112SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 1120
113sleeping saYQPOSAPPSRV01 .accutechAWAITING COMMAND011/6/2014 11:05.Net SqlClient Data Provider 1130
114SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 1140
115sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:04.Net SqlClient Data Provider 1150
116sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:04.Net SqlClient Data Provider 1160
117sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:05.Net SqlClient Data Provider 1170
118sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:02.Net SqlClient Data Provider 1180
119sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:04.Net SqlClient Data Provider 1190
120sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:03.Net SqlClient Data Provider 1200
121sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:05.Net SqlClient Data Provider 1210
122SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 1220
123sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:05.Net SqlClient Data Provider 1230
124SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 1240
125sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:05.Net SqlClient Data Provider 1250
126sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:03.Net SqlClient Data Provider 1260
127SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 1270
128SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 1280
129SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 1290
130SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 1300
131SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 1310
132sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:03.Net SqlClient Data Provider 1320
133sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:05.Net SqlClient Data Provider 1330
134SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 1340
135sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:04.Net SqlClient Data Provider 1350
136sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:06.Net SqlClient Data Provider 1360
137SUSPENDED saYQMONITOR01 93accutechANINSERT 001/6/2014 11:06.Net SqlClient Data Provider 1370
138sleeping saYQMONITOR01 .accutechANAWAITING COMMAND001/6/2014 11:04.Net SqlClient Data Provider 1380
Regards,
Arsalan
January 6, 2014 at 1:01 pm
arsalanayub (1/6/2014)
Hi I am no DBA, but we are facing an issue with an application that eventually loops a piece of code which keeps writing records to the db until the whole db is choked.A brief background to the environment.
We have an sql server 2008 instance hosting two dbs
1. PosDB1
2. PosDB2
Two separate instances of an almost identical application called
1. App1 writes to PosDB1
2. App2 writes to PosDB2
Now there is a bug at the application which loops and hence keeps inserting records to the db.
Eventually the PosDB2 becomes unavailable to app2 for insertions while App1 keeps working fine.
The only resolution we have currently at production before a new app release is to restart the sql db service
Please help in suggesting what can be the best practices to deal with such an issue/case?
I just ran a sp_who and the tab output is below:
Regards,
Arsalan
This is an issue with the application not the database. From what you posted the database is doing exactly what the application is telling it to. The best solution for this type of thing is to perform better testing of the application prior to release. I know that isn't what you want to hear but since the database is performing exactly as expected there is nothing on the database to "fix".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 6, 2014 at 3:04 pm
Assuming that PosDB2 is the database that grows out of control, I would set the PosDB2 database with a maximum size that you don't expect it to hit except when the bug occurs. When the bug happens and it hits this size, App2 will crash, but PosDB1 and App1 should nto be affected.
Another approach would be to set up an alert monitoring database growth and have it email you or page you when a growth occurs and you can stop the offending process before herm is done.
But as the other poster pointed out, these solutions are kludgy. The best practice is to pressure the application vendor to fix their code so this doesn't occur in the first place.
January 6, 2014 at 11:05 pm
Not sure based on what you've posted, but it's possible that you're filling the log file (and the drive). Is your database in Full Recovery mode? If so, are you running log backups? If not, you either need to run log backups or set the database to Simple recovery.
But, I agree with the others, this sounds like it's primarily an application issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2014 at 2:24 am
Even more worrying, why are all the connections using system administrator??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply