SQL Anywhere database - Stopping the database service takes >2 minutes.

Author

Stopping the database service takes >2 minutes.

Mark Hampton

2005-07-20, 1:23 pm

ASA 8.0.3 5002, Windows XP, 128MB Ram, 400Mhz AMD processor, 10+GB free disk
space.

Database server is running as a service, serving three databases (one
without a transaction log, not used in this example). Here is the startup
information :

I. 11/09 07:49:42. 1: -c
I. 11/09 07:49:42. 2: 128M
I. 11/09 07:49:42. 3: -hs
I. 11/09 07:49:42. 4: -z
I. 11/09 07:49:42. 5: -o
I. 11/09 07:49:42. 6: c:\Log\DBSERVER.txt
I. 11/09 07:49:42. 7: -c
I. 11/09 07:49:42. 8: 63M
I. 11/09 07:49:42. 9: -n
I. 11/09 07:49:42. 10: DBSERVER
I. 11/09 07:49:42. 11: C:\DB\MAIN.DB
I. 11/09 07:49:42. 12: C:\DB\LOAD.DB
I. 11/09 07:49:42. 13: C:\DB\TRAINING.DB
I. 11/09 07:49:42. 14: -i0

I. 11/09 07:49:42. Sybase Adaptive Server Anywhere Network Server Version
8.0.3.5002

We have a process that loads in data into the MAIN database, by first
loading it into the LOAD database. Once done, the program exits, we backup
the databases, then shut down the database service.

When we have large load files (36 MB in this case), several internal
warnings pop up in the log (perhaps unrealted to this, they occur 50 minutes
and 15 minutes before the shutdown request).

To make things short, I've snipped most of the stuff:

<snip>
I. 11/09 08:27:27. Connection ID 6: "DBA" disconnected from database "LOAD"
I. 11/09 08:27:27. Connection ID 6: Client disconnected
I. 11/09 08:30:36. Internal warning: task dispatch took 21 seconds
I. 11/09 08:30:36. Internal warning: WinSock completion dispatch took 21
seconds
I. 11/09 08:50:36. Starting checkpoint of "MAIN" (MAIN.DB) at Wed Nov 09
2005 08:50
I. 11/09 08:50:37. Finished checkpoint of "MAIN" (MAIN.DB) at Wed Nov 09
2005 08:50
I. 11/09 09:04:55. Internal warning: worker dispatch took 54 seconds
I. 11/09 09:04:56. Internal warning: worker dispatch took 13 seconds
I. 11/09 09:05:28. Connection ID 1: "DBA" disconnected from database "MAIN"
I. 11/09 09:05:28. Connection ID 1: Client disconnected
I. 11/09 09:05:28. Connection ID 2: "DBA" disconnected from database "MAIN"
I. 11/09 09:05:29. Connection ID 2: Client disconnected
I. 11/09 09:05:29. Connection ID 5: "DBA" disconnected from database "LOAD"
I. 11/09 09:05:29. Connection ID 5: Client disconnected
<snip>
I. 11/09 09:15:31. Connection ID 23: "DBA" disconnected from database "LOAD"
I. 11/09 09:15:31. Connection ID 23: Client disconnected
I. 11/09 09:18:39. Disallowing new connections
I. 11/09 09:18:39. TCP Listener shutting down (995)
I. 11/09 09:18:39. Shutting down databases
I. 11/09 09:18:40. Starting checkpoint of "MAIN" (MAIN.DB) at Wed Nov 09
2005 09:18
I. 11/09 09:18:45. Finished checkpoint of "MAIN" (MAIN.DB) at Wed Nov 09
2005 09:18
I. 11/09 09:20:52. Starting checkpoint of "LOAD" (LOAD.DB) at Wed Nov 09
2005 09:20
I. 11/09 09:20:53. Finished checkpoint of "LOAD" (LOAD.DB) at Wed Nov 09
2005 09:20
I. 11/09 09:21:03. Trying to stop NamedPipes link ...
I. 11/09 09:21:03. NamedPipes link stopped
I. 11/09 09:21:03. Trying to stop TCPIP link ...
I. 11/09 09:21:03. TCPIP link stopped
I. 11/09 09:21:03. Completing engine shutdown
I. 11/09 09:21:05. Database server stopped at Wed Nov 09 2005 09:21

We're shutting down the server with the "NET STOP" command, which times out.

Any idea why it takes so long to shut down? This is causing some problems
for us.

Thanks.

Paul Horan[TeamSybase]

2005-07-20, 1:23 pm

"Mark Hampton" <mhampton. removeme@datavantage
corp.com> wrote in message news:42de8aa3$1@foru
ms-2-dub...
> ASA 8.0.3 5002, Windows XP, 128MB Ram, 400Mhz AMD processor, 10+GB free disk space.
>
& #91;deletia]
>
> We're shutting down the server with the "NET STOP" command, which times out.
>
> Any idea why it takes so long to shut down? This is causing some problems for us.
>
> Thanks.
>

Unless you absolutely need the Named Pipes protocol, you might try adding the -x TCPIP switch into the startup
parameters. The fewer things it needs to start up, the fewer there are to shut down...

--
Paul Horan[TeamSybase]

Breck Carter [TeamSybase]

2005-07-20, 8:23 pm

On 20 Jul 2005 11:46:32 -0700, "Paul Horan[TeamSybase]"
< paulhATvcisolutionsD
OTcom> wrote:

>"Mark Hampton" <mhampton. removeme@datavantage
corp.com> wrote in message news:42de8aa3$1@foru
ms-2-dub...
>[deletia]
>
>
>Unless you absolutely need the Named Pipes protocol, you might try adding the -x TCPIP switch into the startup
>parameters. The fewer things it needs to start up, the fewer there are to shut down...

The log seems to show that shutting down the protocols took less than
1 second:

I. 11/09 09:21:03. Trying to stop NamedPipes link ...
I. 11/09 09:21:03. NamedPipes link stopped
I. 11/09 09:21:03. Trying to stop TCPIP link ...
I. 11/09 09:21:03. TCPIP link stopped

A big delay seems to occur earlier, *between* the final checkpoints:

I. 11/09 09:18:45. Finished checkpoint of "MAIN" (MAIN.DB) at ...
I. 11/09 09:20:52. Starting checkpoint of "LOAD" (LOAD.DB) at ...

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

krisztian pinter

2005-07-21, 3:23 am

On 20 Jul 2005 10:32:23 -0700, Mark Hampton =

<mhampton. removeme@datavantage
corp.com> wrote:

> We have a process that loads in data into the MAIN database, by first
> loading it into the LOAD database. Once done, the program exits, we =

> backup
> the databases, then shut down the database service.
> We're shutting down the server with the "NET STOP" command, which time=
s =

> out.

Try invoking a CHECKPOINT statement. It will still take two minutes,
but shutdown afterwards will be fast.

Jason Hinsperger $iAnywhere$

2005-07-22, 9:23 am

Yes, that is weird.
Are there a lot of active connections to either database when the server is
shut down?
How large ar ethe db/log files?
When is training.db stopped? There don't seem to be any messages related to
stopping it in the log.
How big is the server temp file?
How big are the database files just before shutdown? How big are they after
shutdown completes?

If you run the server as a regular process instead of a service and do your
test, does it still take a long time for the server to shutdown?

--
Jason Hinsperger
Product Manager
iAnywhere Solutions
********************
********************
********************

For the latest downloads technotes, whitepapers, webcasts and other
developer
resources, go to: http://www.ianywhere.com/developer/
********************
********************
********************

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:3b9td1tjlvm56jb
miagmounqs5ics53kae@
4ax.com...
> On 20 Jul 2005 11:46:32 -0700, "Paul Horan[TeamSybase]"
> < paulhATvcisolutionsD
OTcom> wrote:
>
>
> The log seems to show that shutting down the protocols took less than
> 1 second:
>
> I. 11/09 09:21:03. Trying to stop NamedPipes link ...
> I. 11/09 09:21:03. NamedPipes link stopped
> I. 11/09 09:21:03. Trying to stop TCPIP link ...
> I. 11/09 09:21:03. TCPIP link stopped
>
> A big delay seems to occur earlier, *between* the final checkpoints:
>
> I. 11/09 09:18:45. Finished checkpoint of "MAIN" (MAIN.DB) at ...
> I. 11/09 09:20:52. Starting checkpoint of "LOAD" (LOAD.DB) at ...
>
> Breck
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Sponsored Links