Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    Unanswered: I never post my problems but I have no time right now

    I just released a beta and I am too busy with the testers right now.

    New company. Brought into automate the back office and to advise on the design on their software. Lots of sloppiness here. Denormalized tables, inline SQL all of the usual crap I get paid to cleanup.

    I have'nt gotten to this part of there process yet but they do some over night processing that can take up to 12 hours. (I can't wait to look at this code).

    Data entry people get here at 6 am and their applications can't seem to connect to the SQL Server but the processing has stopped. They just hang and hang. The network guy comes in about 8 am and restarts the SQL Service Fonzi style and everything is fine. The DBA thinks it's a network issue which seems a tid bit ridiculous to me since restarting the service "fixes" it.

    I suspecting it's a memory issue.

    Any ideas on where to start.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    since you're one of the good guys i will ask you if you have a preference for what to rename the subject line of your question to, since the current subject is too nebulous
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    on the other hand, it is fairly distinctive so i may just leave it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    rename constipated SQL Server

    rename constipated SQL Server
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Log on to the SQL server, and see what is going on in Task Manager, first. You have probably already done that, but it does sound like a resource problem somewhere (memory, CPU, etc.). Can we hope this is SQL 2000? Or are you stuck with 6.5?

    If there are no resource problems, and the operating system is running smoothly, you probably have some sort of open transaction hanging everyone up. The data entry application may have commandtimeout set to "0", which sounds about in line with what you describe of their code.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My bet is on SQL object locking. I'd check sp_who (definitely not sp_who2 until I knew it was safe) before rebooting to see if a spid is blocking the rest of the known universe.

    The lovely little nightly process might be camping on some resource that everybody needs to "wake up the daemons" in the morning. When you kick the ground out from under it (by restarting SQL), the locks are gone and life is lovely once again.

    -PatP

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Yes it is SQL 2K.

    Thanks guys. I will let you know the resolution. Of course I will have to come in at 6 am. No VPN here. Small shop.

    Have a good weekend.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How long does the "fix" take after it's restarted?

    Are the files set to autogrow?

    Is the log being blown out?

    How do you know if the 12 hour batch windows actually completes.

    Is there enough space left on the drives?

    Are there any other services on the box that are in competition for the memory?

    If there are, my first reccomendation would be to isolate
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Thanks. I will know more on Monday. I just have'nt had much time to think about this due to the revamp project from hell. I plan on being here early to gauge what is going on.

    ===========================================
    How long does the "fix" take after it's restarted?

    Immediate

    Are the files set to autogrow?

    Yes

    Is the log being blown out?

    Not sure I would have to check the log size before and after.

    How do you know if the 12 hour batch windows actually completes.

    They tell me so when I ask.

    Is there enough space left on the drives?

    Yes

    Are there any other services on the box that are in competition for the memory?

    This box is dedicated the SQL Server.

    If there are, my first reccomendation would be to isolate
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    My bet: uncommitted transactions in the nightly process.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •