| Author |
Topic |
|
mikebird
Aged Yak Warrior
529 Posts |
 Posted - 2008-11-03 : 11:05:49
|
| I'm in trouble as this company's single-server IT Support Production Machine has never had so many crashes until I arrived, running KPI/SLA reports on it, possibly overloading it. I'm blamed. All I do is pull rows out in-line with the IT Support app which staff use anyway, to log support calls. They need it up, but don't have high availability or failover at all.I don't have SA access, but I can see system DBs to some extent. Not the system Archive. What's the best way to view crucial crash data and find the root cause? I'm usually a DBA but not on this project. I need to conjure some trust in me back again and get off suspension. I bet there's just something I can say/suggest to impress them!What is a SQL Server crash anyway? I don't knowThanks |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-03 : 13:14:27
|
| well depending on what your queries do you might be causing extensive locking, blocking and reads which would all contribute to a crash.please define server crash._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
 |
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2008-11-04 : 04:47:02
|
| Thanks spiritI am not able to know anything / touch anything. I think I am regarded as a witch, where ppl don't understand, and are scared. Their knowledge of mouse, keybord, monitor is the extent. I have been sent an Archive log in an email, which is an image so can't paste here. The daily activity is IT Support calls raised every minute or so and updated throughout their life until closure, so the row count increases forever. I have been running complex reports which do nothing but pull rows out of one big view I've created over weeks.Is there anything other than locks or reads that can cause a crash? What's blocking? Looking at Activity Monitor, everything's sleeping except me. No locks. In my view I use the same fields the staff support app uses. It just pulls everything in about a minute for the last year or so, while staff insert a new row at once in a picosecond.The version is 8.0.2050 and I feel the machine is slow and a bit incapable by my experience. Worst error for me is solidly failing to pull expected results from a set and crucial view: Msg 1540, Level 16, State 1, Line 1Cannot sort a row of size 8732, which is greater than the allowable maximum of 8094.... which is no good at all if restricted by rows or columns! I have looked into this problem deeply and accepted that it's not happening. This is an 8-processor machine with 3.3Gb RAM and this is just a 4Gb database. I've used a 4-processor SQL Server 2000 on heavy production on a 24Gb database and it was zooming! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-04 : 05:05:11
|
well blocking is a result of locking. for example if you're updateing some rows and someone wants to read them they'll have to wait for you to update them all.the sort error is fixed in sql server 2005. it means that your view has too many columns. try splitting it up.can you run profiler and see what's going on? or just set up a server side trace so you don't put too much load on the server.without more specific info there's not really much we can help you with  you still haven't defined what do you exactly mean by "Server crash". any kind of crash is written into the sql server log as well as in the win event log._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2008-11-04 : 05:35:27
|
| Well, I never do any updating or deleting or creating objects whatsoever on here. Only ever SELECTING. Just one view which I've now refined right down to the final version. The real stuff is in Crystal Reports. It pulls the resultset out every few minutes and the app does its thing with the data. Can this query be that locking and crashmaking?I really want to get onto Profiling and Tracing. Tell me more. Can my login do it? I have a user/password and Windows.Thanks |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2008-11-04 : 05:37:14
|
| I always use 2005 Management Studio with 2000. Do I need EM for any reasons? For Profiler, etc? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-04 : 09:51:14
|
| Did you check execution plan and indexes? |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2008-11-04 : 14:23:43
|
| Excellent question. Yes. I checked all of this 2 months ago. Reports were running no problem ever since. It's a Dashboard report, 12 gauges packed onto on main plasma screen. Every day the row count of the view has climbed from ~ 150k to ~ 172k now so perhaps it's the factor. I put the view in the other post about AWE here... Today I have managed to acquire my very own Blade server right on my desk as a spare until the end of the week. I'll abuse it. I'm still not SA on it and I need to identify crashes if they occur and find the root cause... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2008-11-04 : 16:02:21
|
quote:
Originally posted by mikebird
Excellent question. Yes. I checked all of this 2 months ago. Reports were running no problem ever since. It's a Dashboard report, 12 gauges packed onto on main plasma screen. Every day the row count of the view has climbed from ~ 150k to ~ 172k now so perhaps it's the factor. I put the view in the other post about AWE here...
I saw the view definition. That thing is more than I even want to attempt to delve into. I think you need to step back and reassess what that view is really supposed to be doing and then rewrite it accordingly. Or, post your DDL of all tables involved, some sample data and what the end result should be. Someone would be willing to help but to decipher all the code that you've already posted would be WAY too time consuming for most, if not all, of us.Terry |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2008-11-04 : 16:56:38
|
quote:
Originally posted by mikebird
I always use 2005 Management Studio with 2000. Do I need EM for any reasons? For Profiler, etc?
This question in the middle got skipped, and the answer is NO.Regarding the rest... If you have a beast of a View, you may need to overhaul it. You can try providng the WITH (NOLOCK) hint in your query, but before you do that, be sure that you can live with the consequences of dirty reads.---------------------------EmeraldCityDomains.com |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2008-11-04 : 17:11:44
|
| Fear not. The Beast View HAS been overhauled. I like to publish such sized things to show what I've been working with. The gauges_view is just right, with 6 UNIONs in it. I can't get to it to show it to you yet because my personal server needs ultimate security so I have a crossover ethernet cable to it (from my desktop), then regular net cable at one time to get daily DB backups, etc, so I have to be selective with my time. This is from my Mac at home & I'm going to sleep |
|
|
|
|
Server Crashes