May 31, 2008 at 11:00 pm
Hi,
Can sql server 2000 allow to update db records(we are using vb .net framework 2.0 sqlconnection) during processing auto-recovery during startup? We notice that the period of autorecovery process is quite long if the db server have long time not to restart(we are using simple recovery mode and merge replication). Is it possible to have program client connection and make records changes at this recovery time? If not, any alternative method that can shorten and make the autorecovery time become consistence shorten every time? (say 10 - 20s)
Thanks and regards,
Wallace Chan
June 1, 2008 at 11:39 am
Usually the recovery time is less than 1 minute, undo operations excluded.
Are there any long running transactions on the database?
June 11, 2008 at 4:45 am
Hi,
Sorry for late reply. Please see the below sql server log. Please note that we fail to attempt to update db using visual basic application between sql server startup and autorecovery time(02:19:44.90 to 02:20:13.83). We are now using simple recovery mode and merge replication. Is it possible to shorten this auto-recovery time?
Thanks and regards,
Wallace Chan
2008-05-29 02:19:44.90 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
2008-05-29 02:19:44.90 server Copyright (C) 1988-2002 Microsoft Corporation.
2008-05-29 02:19:44.90 server All rights reserved.
2008-05-29 02:19:44.90 server ??????? 1108?
2008-05-29 02:19:44.90 server ??? SQL Server ???????? 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'?
2008-05-29 02:19:44.95 server SQL Server ??????? 'normal'(???? 4 CPUs) ???
2008-05-29 02:19:45.84 server ??? SQL Server ? thread ????????
2008-05-29 02:19:45.84 server ?? dynamic ?????[2500] ?????,[5000] ?????????
2008-05-29 02:19:46.00 server ??????????????????
2008-05-29 02:19:54.68 spid3 ??????? 'master'?
2008-05-29 02:19:59.87 server ???? 'SSNETLIB.DLL' (? '8.0.766' ?)?
2008-05-29 02:19:59.87 spid5 ??????? 'model'?
2008-05-29 02:20:01.92 spid3 ?????? 'IMDNACCFT13'?
2008-05-29 02:20:01.92 spid9 ??????? 'pubs'?
2008-05-29 02:20:01.92 spid8 ??????? 'msdb'?
2008-05-29 02:20:01.92 spid10 ??????? 'Northwind'?
2008-05-29 02:20:01.92 spid11 ??????? 'CommServ'?
2008-05-29 02:20:02.03 spid12 ??????? 'LDAP'?
2008-05-29 02:20:02.06 spid13 ??????? 'distribution'?
2008-05-29 02:20:08.58 spid5 ???? tempdb ????
2008-05-29 02:20:13.74 spid5 ??????? 'tempdb'?
2008-05-29 02:20:13.80 server SQL server ???? 10.26.92.20: 1433?
2008-05-29 02:20:13.80 server SQL server ???? 172.32.11.71: 1433?
2008-05-29 02:20:13.80 server SQL server ???? 127.0.0.1: 1433?
2008-05-29 02:20:13.83 spid3 ?? (Recovery) ???
2008-05-29 02:20:13.83 spid3 SQL global counter collection task is created.
2008-05-29 02:20:13.85 spid3 Launched startup procedure 'sp_MSrepl_startup'
2008-05-29 02:20:13.86 spid3 Launched startup procedure 'sp_MScleanupmergepublisher'
2008-05-29 02:20:17.22 server SQL server ???? TCP, Shared Memory, Named Pipes?
2008-05-29 02:20:17.23 server SQL Server ????????????
2008-05-29 02:20:18.86 spid51 ???? 'xpsqlbot.dll' ?? '2000.80.194' ?????????? 'xp_qv'?
2008-05-29 02:30:02.39 spid63 ???? 'xpstar.dll' ?? '2000.80.760' ?????????? 'xp_instance_regread'?
2008-05-29 04:02:49.60 spid63 ???? 'xprepl.dll' ?? '2000.80.760' ?????????? 'xp_mapdown_bitmap'?
June 11, 2008 at 10:14 am
Seems not many peoples know Chinese here:)
Maybe you need faster harddrive? or CPU?
June 11, 2008 at 11:10 am
Is the update being done with .Update (can be slow) or with an update-sql statement?
June 11, 2008 at 11:51 am
Jo Pattyn (6/11/2008)
Is the update being done with .Update (can be slow) or with an update-sql statement?
Does it matter?
During recovery (or reboot), I don't think SQL will let you update or do any other things.....
The interesting part from the error log is:
02:20:18.86 spid51
02:30:02.39 spid61
04:02:49......
------ Why do these steps take so much time??
June 11, 2008 at 8:00 pm
Jo Pattyn (6/1/2008)
Usually the recovery time is less than 1 minute, undo operations excluded.Are there any long running transactions on the database?
For the undo operations during auto-recovery, what operations do this process perform? Referring to the log I shown before, what part of log would indicate this undo operations? Now we don't have any long running search query at that moment but we will have reports running which may require a certain search processing on that machine at future. Is it possible to neglect this auto-recovery process at startup? Any harmful effect if we neglect this auto-recovery process? Thanks for any suggestions.
Thanks and regards,
Wallace Chan
June 11, 2008 at 8:08 pm
Wildcat (6/11/2008)
Seems not many peoples know Chinese here:)Maybe you need faster harddrive? or CPU?
Hi,
Now we are using windows server 2003 with 2GB Ram and 3.06GHz machine. Due to resources we don't want to upgrade the hardware now therefore we try to search any other solution that can fasten this startup process if possible. Thanks.
Thanks and regards,
Wallace
June 11, 2008 at 8:12 pm
Wildcat (6/11/2008)
Jo Pattyn (6/11/2008)
Is the update being done with .Update (can be slow) or with an update-sql statement?Does it matter?
During recovery (or reboot), I don't think SQL will let you update or do any other things.....
-----> Thanks for your opinion
The interesting part from the error log is:
02:20:18.86 spid51
02:30:02.39 spid61
04:02:49......
------ Why do these steps take so much time??
-----> during 02:30:02 to 04:02:49 we can issue sql to db successfully.
Thanks and regards,
Wallace
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply