I currently have an ASA 12 server that is unresponsive to any calls. I am unable to connect, any program currently connected is frozen, and the server is not responding to shutdown requests. Even the SQL Monitor is unable to get any information off of the server.

The server is however still wiring to the DB file as well as a file names sqla0001.tmp in my WindowsTemp directory.

Anyone have any ideas?

Thanks Steve

asked 28 Jan '14, 14:04

steve_ell's gravatar image

steve_ell
46234
accept rate: 0%


The server could be doing a checkpoint? When a checkpoint of a database occurs all of the database connections get (temporarily) locked out so that changes to the database do not occur. Normally the lock out time is very short but if you have a very large cache and there are a lot of dirty (changed) database pages then the time it takes to write all of the dirty pages to disk could take a long time.

You also mention that I/O to the tmp file is happening... so this would imply that perhaps there is a query (or multiple queries) that is (are) materializing a large (intermediate?) result set and this is causing the cache to thrash. If all database workers are busy then no new requests would be processed.

Just some ideas... hard to tell what is really happening without more information.

permanent link

answered 28 Jan '14, 14:14

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297
accept rate: 41%

1

We are running a very large cache (around 17G). I was also able to locate a query that I think was causing the tmp file to get out of control. I've removed that for now to see if there is any effect.

I have also limited the tmp space for each connection to 100M to see if any other processes error out with tmp space issue.

(29 Jan '14, 13:14) steve_ell

Umm, Task Manager / Process Explorer? This happens occasionally to us. It may be frozen when connecting to one database on the engine but respond normally on other databases as well (or it may not).

Do a standard shutdown request (via Services if you have it installed as one) and wait for a bit for it to get as far as it can shuting down normally (check server logs to see if anything's being written).

Once it seems completely dead / idle, kill the process in task manager / process explorer and then restart the engine. It may take some time launching if there were a large number of databases or the checkpoint urgency was high on one of them; again, check server logs to gauge progress.

permanent link

answered 28 Jan '14, 14:14

Erik%20Anderson's gravatar image

Erik Anderson
43681323
accept rate: 15%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×14

question asked: 28 Jan '14, 14:04

question was seen: 2,731 times

last updated: 29 Jan '14, 13:14