Sql Server 2005 locking up daily running stored procedure.

  • Hello,

    We have an issue with SQL Server running report batch job that runs store procedures that connect to the DB's on a NAS. Seems that every morning after running a store procedure all night the SQL Server 2005 locks up and we have to reboot.

    The only error that was received in a log that we have the stored procedure log was:

    rsReportServerDatabaseUnavailable

    The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing.

    rsReportServerDatabaseUnavailable

    The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing.

    Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    This is now requiring a daily reboot to unlock the server. It almost seems as though there something is chewing up memory and locking up everything.

    We move the reportssever db to a local directory off of the NAS but the db had the same situation occurs. Ran all night, locked up again in the morning.

    ?'s

    What can I do to trouble shoot this?

    What information will it capture and be available to me after a reboot?

    Any advice would be greatly appreciated on fixing this issue.

  • A couple of questions:

    Do you have other reports on that reportserver that are working fine?

    What else is running on the server at that time?

    I'd run a server-side trace overnight to see the activity on the server. I'd include ExistingConnection, lock:Deadlock and Lock:Timeout, RPC:Starting and RPC:Completed, and SQL:BatchStarting and SQL:BatchCompleted events to start with. I'd filter on DatabaseID and include the ReportServer db's and the DB being queried by the report. You might want to try this for a short time on the server during thte day first to see how much data is collected so I could correctly configure the trace.

  • Very strange. You could look through the default trace for some clues, and then see if you can execute the various items that are captured. If you can narrow things down to a specific statement or query, that will help.

    Are your SSRS and other databases on the same server?

    Are you saying your database files are stored on a a NAS device? If so, that's completely unsupported, and this is why. The bandwidth and connection aren't stable or reliable enough.

  • Hello,

    Thank you for the suggestion. How do you run a server side trace? What and should I do to set this up?

    Where can we look for the default trace information? How do we know if we have it turned on?

    Anyone ever run any monitoring tools to mine for information? What would you suggest.

    Thank you.

  • How do you run a server side trace? What and should I do to set this up?

    Start by reading this.

    Where can we look for the default trace information? How do we know if we have it turned on?

    The default trace is installed and started when SQL Server is installed. If you have not explicitly stopped it, then it is running. You can check for sure by running this code:

    SELECT

    *

    FROM

    sys.traces

    WHERE

    is_default = 1

    You can then query the default trace using fn_trace_gettable like this:

    SELECT

    TE.[name],

    I.*

    FROM

    sys.traces T CROSS Apply

    :: fn_trace_gettable(CASE WHEN CHARINDEX('_', T.[path]) 0

    THEN SUBSTRING(T.PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'

    ELSE T.[path]

    End, T.max_files) I

    Anyone ever run any monitoring tools to mine for information? What would you suggest.

    I have not been running any monitoring tools, although there are some good ones out there that I'm sure others could recommend.

  • The default trace is always on. That's why it's named that.

    http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/

    Server side tracing, it's in books online. It's not too hard to set up, but a little tedious. I believe you can use Profiler to choose the items and then script out the code.

  • Thankx. Let me digest this then get back to you.

  • Hello,

    We loaded a monitoring tool. It shows that before we have to reboot we see a large frequency of Async_Network_IO. Seems like SQL is done loading over the information but the Nas as not completed writing to the table. Then we get the server locking up.

    How can we meter the data being processed so it NAS can write just as fast as SQL is processing?

    Is there better storage options?

    Any advice?

  • patrickn (7/14/2009)


    Hello,

    We loaded a monitoring tool. It shows that before we have to reboot we see a large frequency of Async_Network_IO. Seems like SQL is done loading over the information but the Nas as not completed writing to the table. Then we get the server locking up.

    How can we meter the data being processed so it NAS can write just as fast as SQL is processing?

    Is there better storage options?

    Any advice?

    SQL Server databases are not support on a NAS device. Verify whether or not you are really on a NAS (e.g. UNC path to a database file), or running from a SAN (local drive).

    If you are actually running on a SAN - then you have an I/O problem and you need to get with your SAN support to identify the problem. It could be the controllers are getting saturated, or it could be a write-cache problem, or something else.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Although SQL Server isn't supported on an NAS I managed several database servers that used NetAPP for a storage device. Stability was always an issue. I saw time-outs, hung processes and an assortment of other issues/problems.

    How this issue got resolved was upgrading the NetAPP to provide a much greater bandwidth. Once this was done the SQL Server environment became a lot more stable. Performance was back to "normal".

    The NAS solution was not my doing so I had to make the best of what was handed to me. Prior to moving to the NAS solution I was using conventional hardware.

    The final results as referred to as "normal" is based upon what was seen prior to migrating to the NetAPP solution.

    I do know the site still has issues but no one seems to care..

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • Kurt W. Zimmerman (7/15/2009)


    Although SQL Server isn't supported on an NAS I managed several database servers that used NetAPP for a storage device. Stability was always an issue. I saw time-outs, hung processes and an assortment of other issues/problems.

    How this issue got resolved was upgrading the NetAPP to provide a much greater bandwidth. Once this was done the SQL Server environment became a lot more stable. Performance was back to "normal".

    The NAS solution was not my doing so I had to make the best of what was handed to me. Prior to moving to the NAS solution I was using conventional hardware.

    The final results as referred to as "normal" is based upon what was seen prior to migrating to the NetAPP solution.

    I do know the site still has issues but no one seems to care..

    Kurt

    Netapp provides both a NAS and SAN solution. SQL Server is not supported on a NAS and will not attach a database unless you specify a particular trace flag. I would bet that you were not using the NAS but instead was using their SAN product.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • You are probably correct. When the company that I was working for was acquired all hardware were handled in another office 2000 miles away...

    Thanks for your comments.

    Kurt Zimmerman

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • Hello,

    We turned on server tracing and I see that I am getting buffer latch type 2 errors on Spid's 70,67, 64.

    I cross refereded in the trace that:

    64 is a sp_execute 4

    70 is a sp_execute

    67 is:

    -- network protocol: LPC

    set quoted_identifier on

    set arithabort off

    set numeric_roundabort off

    set ansi_warnings on

    set ansi_padding on

    set ansi_nulls on

    set concat_null_yields_null on

    set cursor_close_on_commit off

    set implicit_transactions off

    set language us_english

    set dateformat mdy

    set datefirst 7

    set transaction isolation level read committed

    I turned on TSQL_SP's and Standard. I added read, writes, CPU duration.

    So, when it goes to do these executes, the network or disk is not available.

    Any advice?

  • Hey, Can I ask you? Does the error look to be part of this:

    When you run SQL Server with the awe enabled option, SQL Server may hold a latch while attempting to remove an object from the cache. If that cached object contains information that is stored in memory from a shared memory allocation block, the cache removal may block or deadlock with another system server process ID (SPID) that is accessing the same memory block. As a result, the latch is held longer than anticipated, which may lead to latch warnings and timeouts for other users.

    We get a bunch of Latch type 4 and 2 type errors. We are running Server 2003 and SQL Server 2005. Current AWE is 1 and Run AWE is 0.

    What are your thoughts?

    Reference:

    http://msdn.microsoft.com/en-us/library/ms175581%28SQL.90%29.aspx

    http://support.microsoft.com/kb/309093

  • Changing the AWE seemed to work. (knock on the wood in my noggin). We have gone through monthly reporting with out any issues.

    Thank you to everyone for the help. Doing the server trace helped find the underlying cause.

Viewing 15 posts - 1 through 14 (of 14 total)

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