July 10, 2009 at 9:14 am
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.
July 10, 2009 at 9:30 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 10, 2009 at 9:31 am
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.
July 10, 2009 at 10:17 am
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.
July 10, 2009 at 10:29 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 10, 2009 at 10:30 am
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.
July 10, 2009 at 10:42 am
Thankx. Let me digest this then get back to you.
July 14, 2009 at 11:44 am
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?
July 14, 2009 at 1:56 pm
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
July 15, 2009 at 12:38 pm
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
July 15, 2009 at 1:10 pm
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
July 15, 2009 at 6:48 pm
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
August 6, 2009 at 8:45 am
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?
August 7, 2009 at 1:30 pm
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
August 12, 2009 at 9:34 am
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