error 17883

Hi, ALL
We are getting error 17883
Backup for DB "GBK" was not creating.
19:22 first error.
~ 07:00 users begin to work. Server does not response.
~ 07:13 administrator log in to console.
07:31 server restart. there are no error in error log.
Errorlog:
2006-04-06 19:20:03.24 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6750:1, last LSN:
1131478:6786:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:21:08.93 server Error: 17883, Severity: 1, State: 0
2006-04-06 19:21:08.93 server The Scheduler 2 appears to be hung. SPID
58, ECID 0, UMS Context 0x07A92978.
2006-04-06 19:22:03.13 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6786:1, last LSN:
1131478:6814:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:22:08.93 server Error: 17883, Severity: 1, State: 0
2006-04-06 19:22:08.93 server The Scheduler 2 appears to be hung. SPID
58, ECID 0, UMS Context 0x07A92978.
2006-04-06 19:24:03.21 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6814:1, last LSN:
1131478:6850:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:26:03.12 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6850:1, last LSN:
1131478:6867:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:28:03.20 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6867:1, last LSN:
1131478:6919:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:30:02.70 backup Log backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), first LSN: 46544:183:1, last LSN:
46544:183:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'GSM_log'}).
2006-04-06 19:30:04.65 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6919:1, last LSN:
1131478:6962:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:31:08.93 server Error: 17883, Severity: 1, State: 0
2006-04-06 19:31:08.93 server The Scheduler 2 appears to be hung. SPID
58, ECID 0, UMS Context 0x07A92978.
2006-04-06 19:32:03.18 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6962:1, last LSN:
1131478:7027:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:34:03.23 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7027:1, last LSN:
1131478:7070:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:36:03.31 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7070:1, last LSN:
1131478:7096:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:37:08.94 server Error: 17883, Severity: 1, State: 0
2006-04-06 19:37:08.94 server The Scheduler 2 appears to be hung. SPID
58, ECID 0, UMS Context 0x07A92978.
2006-04-06 19:38:03.28 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7096:1, last LSN:
1131478:7127:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:40:02.13 backup Log backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), first LSN: 46544:183:1, last LSN:
46544:183:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'GSM_log'}).
2006-04-06 19:40:03.22 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7127:1, last LSN:
1131478:7175:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:42:03.19 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7175:1, last LSN:
1131478:7220:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:44:03.22 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7220:1, last LSN:
1131478:7247:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:44:08.94 server Error: 17883, Severity: 1, State: 0
2006-04-06 19:44:08.94 server The Scheduler 2 appears to be hung. SPID
58, ECID 0, UMS Context 0x07A92978.
2006-04-06 19:46:03.24 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7247:1, last LSN:
1131478:7264:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:48:03.24 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7264:1, last LSN:
1131478:7286:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:50:02.13 backup Log backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), first LSN: 46544:183:1, last LSN:
46544:183:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'GSM_log'}).
2006-04-06 19:50:03.28 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7286:1, last LSN:
1131478:7293:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:52:03.21 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7293:1, last LSN:
1131478:7305:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:54:03.18 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7305:1, last LSN:
1131478:7307:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:56:03.11 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7307:1, last LSN:
1131478:7309:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:58:03.10 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7309:1, last LSN:
1131478:7319:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 22:30:02.74 spid55 Attempting to cycle errorlog.
2006-04-06 22:30:02.83 spid55 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: )
2006-04-06 22:30:02.83 spid55 Copyright (C) 1988-2002 Microsoft
Corporation.
2006-04-06 22:30:02.83 spid55 All rights reserved.
2006-04-06 22:30:02.83 spid55 Server Process ID is 636.
2006-04-06 22:30:02.83 spid55 Logging SQL Server messages in file
'd:\mssql2000\MSSQL\log\ERRORLOG'.
2006-04-06 22:30:02.83 spid55 Errorlog has been reinitialized. See
previous log for older entries.
2006-04-06 22:30:05.14 spid55 DBCC CHECKDB (master) executed by
domain\user found 0 errors and repaired 0 errors.
2006-04-06 22:30:06.81 backup Database backed up: Database: master,
creation date(time): 2006/03/24(20:39:08), pages dumped: 1963, first LSN:
312:455:1, last LSN: 312:457:1, number of dump devices: 1, device
information: (FILE=1, TYPE=DISK: {'master_full'}).
2006-04-06 22:30:08.88 spid54 DBCC CHECKDB (msdb) executed by domain\user
found 0 errors and repaired 0 errors.
2006-04-06 22:30:12.94 backup Database backed up: Database: msdb,
creation date(time): 2000/08/06(01:40:56), pages dumped: 4811, first LSN:
18066:979:1, last LSN: 18066:982:1, number of dump devices: 1, device
information: (FILE=1, TYPE=DISK: {'msdb_full'}).
2006-04-06 22:30:13.89 spid54 DBCC CHECKDB (sqladmin) executed by
domain\user found 0 errors and repaired 0 errors.
2006-04-06 22:30:14.99 backup Database backed up: Database: sqladmin,
creation date(time): 2003/04/21(14:44:13), pages dumped: 258, first LSN:
27404:239:1, last LSN: 27404:241:1, number of dump devices: 1, device
information: (FILE=1, TYPE=DISK: {'sqladmin_full'}).
2006-04-06 22:30:17.50 spid55 DBCC CHECKDB (GSM) executed by domain\user
found 0 errors and repaired 0 errors.
2006-04-06 22:30:31.42 backup Database log truncated: Database: GSM.
2006-04-06 22:30:33.74 backup Database backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), pages dumped: 3750, first LSN: 46638:361:1,
last LSN: 46638:363:1, number of dump devices: 1, device information:
(FILE=1, TYPE=DISK: {'GSM_full'}).
2006-04-06 22:44:07.73 spid56 DBCC CHECKDB (gbk) executed by domain\user
found 0 errors and repaired 0 errors.
2006-04-07 00:18:47.12 backup Database log truncated: Database: gbk.
2006-04-07 07:14:53.14 server Error: 17883, Severity: 1, State: 0
2006-04-07 07:14:53.14 server The Scheduler 2 appears to be hung. SPID 0,
ECID 0, UMS Context 0x07A86300.
2006-04-07 07:14:56.42 backup Log backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), first LSN: 46638:361:1, last LSN:
46638:368:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'GSM_log'}).
2006-04-07 07:20:03.00 backup Log backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), first LSN: 46638:368:1, last LSN:
46638:368:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'GSM_log'}).
2006-04-07 07:30:03.30 backup Log backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), first LSN: 46638:368:1, last LSN:
46638:368:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'GSM_log'}).
2006-04-07 07:31:17.07 backup BACKUP failed to complete the command PRINT
'creating full backup GBK'
backup database [gbk] to [gbk_full] with stats=100, init, skip
2006-04-07 07:31:27.73 spid3 SQL Server is terminating due to 'stop'
request from Service Control Manager.
...
Server restart
Hello
In essence all this 17883 message tells us is that no context switches were
made on the scheduler. The two things that I can think of are a) a hung
scheduler, or b) a thread that is not properly yielding to the scheduler.
I would suggest opening up a case with PSS since this need memory dump to
find clues to troubleshoot the issue.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Also, please make sure you have latest service pack installed on the
server.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: error 17883
>thread-index: AcZaGCgcfWSWeH5/QoCXiTp9pSJ7YQ==
>X-WBNR-Posting-Host: 212.12.3.11
>From: =?Utf-8?B?dmFkaW1t?= <white@.community.nospam>
>Subject: error 17883
>Date: Fri, 7 Apr 2006 00:52:02 -0700
>Lines: 186
>Message-ID: <1961654E-C620-4AFA-BD4A-A731BCA877CE@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>Newsgroups: microsoft.public.sqlserver.server
>Path: TK2MSFTNGXA01.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:427204
>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Hi, ALL
>We are getting error 17883
>Backup for DB "GBK" was not creating.
> 19:22 first error.
>~ 07:00 users begin to work. Server does not response.
>~ 07:13 administrator log in to console.
> 07:31 server restart. there are no error in error log.
>Errorlog:
>2006-04-06 19:20:03.24 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6750:1, last LSN:
>1131478:6786:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:21:08.93 server Error: 17883, Severity: 1, State: 0
>2006-04-06 19:21:08.93 server The Scheduler 2 appears to be hung. SPID
>58, ECID 0, UMS Context 0x07A92978.
>2006-04-06 19:22:03.13 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6786:1, last LSN:
>1131478:6814:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:22:08.93 server Error: 17883, Severity: 1, State: 0
>2006-04-06 19:22:08.93 server The Scheduler 2 appears to be hung. SPID
>58, ECID 0, UMS Context 0x07A92978.
>2006-04-06 19:24:03.21 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6814:1, last LSN:
>1131478:6850:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:26:03.12 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6850:1, last LSN:
>1131478:6867:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:28:03.20 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6867:1, last LSN:
>1131478:6919:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:30:02.70 backup Log backed up: Database: GSM, creation
>date(time): 2004/06/05(00:34:40), first LSN: 46544:183:1, last LSN:
>46544:183:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'GSM_log'}).
>2006-04-06 19:30:04.65 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6919:1, last LSN:
>1131478:6962:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:31:08.93 server Error: 17883, Severity: 1, State: 0
>2006-04-06 19:31:08.93 server The Scheduler 2 appears to be hung. SPID
>58, ECID 0, UMS Context 0x07A92978.
>2006-04-06 19:32:03.18 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6962:1, last LSN:
>1131478:7027:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:34:03.23 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7027:1, last LSN:
>1131478:7070:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:36:03.31 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7070:1, last LSN:
>1131478:7096:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:37:08.94 server Error: 17883, Severity: 1, State: 0
>2006-04-06 19:37:08.94 server The Scheduler 2 appears to be hung. SPID
>58, ECID 0, UMS Context 0x07A92978.
>2006-04-06 19:38:03.28 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7096:1, last LSN:
>1131478:7127:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:40:02.13 backup Log backed up: Database: GSM, creation
>date(time): 2004/06/05(00:34:40), first LSN: 46544:183:1, last LSN:
>46544:183:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'GSM_log'}).
>2006-04-06 19:40:03.22 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7127:1, last LSN:
>1131478:7175:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:42:03.19 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7175:1, last LSN:
>1131478:7220:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:44:03.22 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7220:1, last LSN:
>1131478:7247:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:44:08.94 server Error: 17883, Severity: 1, State: 0
>2006-04-06 19:44:08.94 server The Scheduler 2 appears to be hung. SPID
>58, ECID 0, UMS Context 0x07A92978.
>2006-04-06 19:46:03.24 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7247:1, last LSN:
>1131478:7264:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:48:03.24 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7264:1, last LSN:
>1131478:7286:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:50:02.13 backup Log backed up: Database: GSM, creation
>date(time): 2004/06/05(00:34:40), first LSN: 46544:183:1, last LSN:
>46544:183:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'GSM_log'}).
>2006-04-06 19:50:03.28 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7286:1, last LSN:
>1131478:7293:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:52:03.21 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7293:1, last LSN:
>1131478:7305:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:54:03.18 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7305:1, last LSN:
>1131478:7307:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:56:03.11 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7307:1, last LSN:
>1131478:7309:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:58:03.10 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7309:1, last LSN:
>1131478:7319:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 22:30:02.74 spid55 Attempting to cycle errorlog.
>2006-04-06 22:30:02.83 spid55 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: )
>2006-04-06 22:30:02.83 spid55 Copyright (C) 1988-2002 Microsoft
>Corporation.
>2006-04-06 22:30:02.83 spid55 All rights reserved.
>2006-04-06 22:30:02.83 spid55 Server Process ID is 636.
>2006-04-06 22:30:02.83 spid55 Logging SQL Server messages in file
>'d:\mssql2000\MSSQL\log\ERRORLOG'.
>2006-04-06 22:30:02.83 spid55 Errorlog has been reinitialized. See
>previous log for older entries.
>2006-04-06 22:30:05.14 spid55 DBCC CHECKDB (master) executed by
>domain\user found 0 errors and repaired 0 errors.
>2006-04-06 22:30:06.81 backup Database backed up: Database: master,
>creation date(time): 2006/03/24(20:39:08), pages dumped: 1963, first LSN:
>312:455:1, last LSN: 312:457:1, number of dump devices: 1, device
>information: (FILE=1, TYPE=DISK: {'master_full'}).
>2006-04-06 22:30:08.88 spid54 DBCC CHECKDB (msdb) executed by
domain\user
>found 0 errors and repaired 0 errors.
>2006-04-06 22:30:12.94 backup Database backed up: Database: msdb,
>creation date(time): 2000/08/06(01:40:56), pages dumped: 4811, first LSN:
>18066:979:1, last LSN: 18066:982:1, number of dump devices: 1, device
>information: (FILE=1, TYPE=DISK: {'msdb_full'}).
>2006-04-06 22:30:13.89 spid54 DBCC CHECKDB (sqladmin) executed by
>domain\user found 0 errors and repaired 0 errors.
>2006-04-06 22:30:14.99 backup Database backed up: Database: sqladmin,
>creation date(time): 2003/04/21(14:44:13), pages dumped: 258, first LSN:
>27404:239:1, last LSN: 27404:241:1, number of dump devices: 1, device
>information: (FILE=1, TYPE=DISK: {'sqladmin_full'}).
>2006-04-06 22:30:17.50 spid55 DBCC CHECKDB (GSM) executed by
domain\user
>found 0 errors and repaired 0 errors.
>2006-04-06 22:30:31.42 backup Database log truncated: Database: GSM.
>2006-04-06 22:30:33.74 backup Database backed up: Database: GSM,
creation
>date(time): 2004/06/05(00:34:40), pages dumped: 3750, first LSN:
46638:361:1,
>last LSN: 46638:363:1, number of dump devices: 1, device information:
>(FILE=1, TYPE=DISK: {'GSM_full'}).
>2006-04-06 22:44:07.73 spid56 DBCC CHECKDB (gbk) executed by
domain\user
>found 0 errors and repaired 0 errors.
>2006-04-07 00:18:47.12 backup Database log truncated: Database: gbk.
>2006-04-07 07:14:53.14 server Error: 17883, Severity: 1, State: 0
>2006-04-07 07:14:53.14 server The Scheduler 2 appears to be hung. SPID
0,
>ECID 0, UMS Context 0x07A86300.
>2006-04-07 07:14:56.42 backup Log backed up: Database: GSM, creation
>date(time): 2004/06/05(00:34:40), first LSN: 46638:361:1, last LSN:
>46638:368:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'GSM_log'}).
>2006-04-07 07:20:03.00 backup Log backed up: Database: GSM, creation
>date(time): 2004/06/05(00:34:40), first LSN: 46638:368:1, last LSN:
>46638:368:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'GSM_log'}).
>2006-04-07 07:30:03.30 backup Log backed up: Database: GSM, creation
>date(time): 2004/06/05(00:34:40), first LSN: 46638:368:1, last LSN:
>46638:368:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'GSM_log'}).
>2006-04-07 07:31:17.07 backup BACKUP failed to complete the command
PRINT
>'creating full backup GBK'
>backup database [gbk] to [gbk_full] with stats=100, init, skip
>2006-04-07 07:31:27.73 spid3 SQL Server is terminating due to 'stop'
>request from Service Control Manager.
>...
>Server restart
>
sql

error 17883

Hi, ALL
We are getting error 17883
Backup for DB "GBK" was not creating.
19:22 first error.
~ 07:00 users begin to work. Server does not response.
~ 07:13 administrator log in to console.
07:31 server restart. there are no error in error log.
Errorlog:
2006-04-06 19:20:03.24 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6750:1, last LSN:
1131478:6786:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:21:08.93 server Error: 17883, Severity: 1, State: 0
2006-04-06 19:21:08.93 server The Scheduler 2 appears to be hung. SPID
58, ECID 0, UMS Context 0x07A92978.
2006-04-06 19:22:03.13 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6786:1, last LSN:
1131478:6814:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:22:08.93 server Error: 17883, Severity: 1, State: 0
2006-04-06 19:22:08.93 server The Scheduler 2 appears to be hung. SPID
58, ECID 0, UMS Context 0x07A92978.
2006-04-06 19:24:03.21 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6814:1, last LSN:
1131478:6850:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:26:03.12 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6850:1, last LSN:
1131478:6867:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:28:03.20 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6867:1, last LSN:
1131478:6919:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:30:02.70 backup Log backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), first LSN: 46544:183:1, last LSN:
46544:183:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'GSM_log'}).
2006-04-06 19:30:04.65 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6919:1, last LSN:
1131478:6962:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:31:08.93 server Error: 17883, Severity: 1, State: 0
2006-04-06 19:31:08.93 server The Scheduler 2 appears to be hung. SPID
58, ECID 0, UMS Context 0x07A92978.
2006-04-06 19:32:03.18 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6962:1, last LSN:
1131478:7027:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:34:03.23 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7027:1, last LSN:
1131478:7070:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:36:03.31 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7070:1, last LSN:
1131478:7096:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:37:08.94 server Error: 17883, Severity: 1, State: 0
2006-04-06 19:37:08.94 server The Scheduler 2 appears to be hung. SPID
58, ECID 0, UMS Context 0x07A92978.
2006-04-06 19:38:03.28 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7096:1, last LSN:
1131478:7127:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:40:02.13 backup Log backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), first LSN: 46544:183:1, last LSN:
46544:183:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'GSM_log'}).
2006-04-06 19:40:03.22 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7127:1, last LSN:
1131478:7175:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:42:03.19 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7175:1, last LSN:
1131478:7220:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:44:03.22 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7220:1, last LSN:
1131478:7247:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:44:08.94 server Error: 17883, Severity: 1, State: 0
2006-04-06 19:44:08.94 server The Scheduler 2 appears to be hung. SPID
58, ECID 0, UMS Context 0x07A92978.
2006-04-06 19:46:03.24 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7247:1, last LSN:
1131478:7264:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:48:03.24 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7264:1, last LSN:
1131478:7286:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:50:02.13 backup Log backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), first LSN: 46544:183:1, last LSN:
46544:183:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'GSM_log'}).
2006-04-06 19:50:03.28 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7286:1, last LSN:
1131478:7293:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:52:03.21 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7293:1, last LSN:
1131478:7305:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:54:03.18 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7305:1, last LSN:
1131478:7307:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:56:03.11 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7307:1, last LSN:
1131478:7309:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:58:03.10 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7309:1, last LSN:
1131478:7319:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 22:30:02.74 spid55 Attempting to cycle errorlog.
2006-04-06 22:30:02.83 spid55 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: )
2006-04-06 22:30:02.83 spid55 Copyright (C) 1988-2002 Microsoft
Corporation.
2006-04-06 22:30:02.83 spid55 All rights reserved.
2006-04-06 22:30:02.83 spid55 Server Process ID is 636.
2006-04-06 22:30:02.83 spid55 Logging SQL Server messages in file
'd:\mssql2000\MSSQL\log\ERRORLOG'.
2006-04-06 22:30:02.83 spid55 Errorlog has been reinitialized. See
previous log for older entries.
2006-04-06 22:30:05.14 spid55 DBCC CHECKDB (master) executed by
domain\user found 0 errors and repaired 0 errors.
2006-04-06 22:30:06.81 backup Database backed up: Database: master,
creation date(time): 2006/03/24(20:39:08), pages dumped: 1963, first LSN:
312:455:1, last LSN: 312:457:1, number of dump devices: 1, device
information: (FILE=1, TYPE=DISK: {'master_full'}).
2006-04-06 22:30:08.88 spid54 DBCC CHECKDB (msdb) executed by domain\user
found 0 errors and repaired 0 errors.
2006-04-06 22:30:12.94 backup Database backed up: Database: msdb,
creation date(time): 2000/08/06(01:40:56), pages dumped: 4811, first LSN:
18066:979:1, last LSN: 18066:982:1, number of dump devices: 1, device
information: (FILE=1, TYPE=DISK: {'msdb_full'}).
2006-04-06 22:30:13.89 spid54 DBCC CHECKDB (sqladmin) executed by
domain\user found 0 errors and repaired 0 errors.
2006-04-06 22:30:14.99 backup Database backed up: Database: sqladmin,
creation date(time): 2003/04/21(14:44:13), pages dumped: 258, first LSN:
27404:239:1, last LSN: 27404:241:1, number of dump devices: 1, device
information: (FILE=1, TYPE=DISK: {'sqladmin_full'}).
2006-04-06 22:30:17.50 spid55 DBCC CHECKDB (GSM) executed by domain\user
found 0 errors and repaired 0 errors.
2006-04-06 22:30:31.42 backup Database log truncated: Database: GSM.
2006-04-06 22:30:33.74 backup Database backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), pages dumped: 3750, first LSN: 46638:361:1
,
last LSN: 46638:363:1, number of dump devices: 1, device information:
(FILE=1, TYPE=DISK: {'GSM_full'}).
2006-04-06 22:44:07.73 spid56 DBCC CHECKDB (gbk) executed by domain\user
found 0 errors and repaired 0 errors.
2006-04-07 00:18:47.12 backup Database log truncated: Database: gbk.
2006-04-07 07:14:53.14 server Error: 17883, Severity: 1, State: 0
2006-04-07 07:14:53.14 server The Scheduler 2 appears to be hung. SPID 0,
ECID 0, UMS Context 0x07A86300.
2006-04-07 07:14:56.42 backup Log backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), first LSN: 46638:361:1, last LSN:
46638:368:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'GSM_log'}).
2006-04-07 07:20:03.00 backup Log backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), first LSN: 46638:368:1, last LSN:
46638:368:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'GSM_log'}).
2006-04-07 07:30:03.30 backup Log backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), first LSN: 46638:368:1, last LSN:
46638:368:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'GSM_log'}).
2006-04-07 07:31:17.07 backup BACKUP failed to complete the command PRINT
'creating full backup GBK'
backup database [gbk] to [gbk_full] with stats=100, init, skip
2006-04-07 07:31:27.73 spid3 SQL Server is terminating due to 'stop'
request from Service Control Manager.
...
Server restartHello
In essence all this 17883 message tells us is that no context switches were
made on the scheduler. The two things that I can think of are a) a hung
scheduler, or b) a thread that is not properly yielding to the scheduler.
I would suggest opening up a case with PSS since this need memory dump to
find clues to troubleshoot the issue.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Also, please make sure you have latest service pack installed on the
server.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: error 17883
>thread-index: AcZaGCgcfWSWeH5/QoCXiTp9pSJ7YQ==
>X-WBNR-Posting-Host: 212.12.3.11
>From: examnotes <white@.community.nospam>
>Subject: error 17883
>Date: Fri, 7 Apr 2006 00:52:02 -0700
>Lines: 186
>Message-ID: <1961654E-C620-4AFA-BD4A-A731BCA877CE@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>Newsgroups: microsoft.public.sqlserver.server
>Path: TK2MSFTNGXA01.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:427204
>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Hi, ALL
>We are getting error 17883
>Backup for DB "GBK" was not creating.
> 19:22 first error.
>~ 07:00 users begin to work. Server does not response.
>~ 07:13 administrator log in to console.
> 07:31 server restart. there are no error in error log.
>Errorlog:
>2006-04-06 19:20:03.24 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6750:1, last LSN:
>1131478:6786:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:21:08.93 server Error: 17883, Severity: 1, State: 0
>2006-04-06 19:21:08.93 server The Scheduler 2 appears to be hung. SPID
>58, ECID 0, UMS Context 0x07A92978.
>2006-04-06 19:22:03.13 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6786:1, last LSN:
>1131478:6814:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:22:08.93 server Error: 17883, Severity: 1, State: 0
>2006-04-06 19:22:08.93 server The Scheduler 2 appears to be hung. SPID
>58, ECID 0, UMS Context 0x07A92978.
>2006-04-06 19:24:03.21 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6814:1, last LSN:
>1131478:6850:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:26:03.12 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6850:1, last LSN:
>1131478:6867:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:28:03.20 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6867:1, last LSN:
>1131478:6919:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:30:02.70 backup Log backed up: Database: GSM, creation
>date(time): 2004/06/05(00:34:40), first LSN: 46544:183:1, last LSN:
>46544:183:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'GSM_log'}).
>2006-04-06 19:30:04.65 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6919:1, last LSN:
>1131478:6962:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:31:08.93 server Error: 17883, Severity: 1, State: 0
>2006-04-06 19:31:08.93 server The Scheduler 2 appears to be hung. SPID
>58, ECID 0, UMS Context 0x07A92978.
>2006-04-06 19:32:03.18 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6962:1, last LSN:
>1131478:7027:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:34:03.23 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7027:1, last LSN:
>1131478:7070:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:36:03.31 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7070:1, last LSN:
>1131478:7096:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:37:08.94 server Error: 17883, Severity: 1, State: 0
>2006-04-06 19:37:08.94 server The Scheduler 2 appears to be hung. SPID
>58, ECID 0, UMS Context 0x07A92978.
>2006-04-06 19:38:03.28 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7096:1, last LSN:
>1131478:7127:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:40:02.13 backup Log backed up: Database: GSM, creation
>date(time): 2004/06/05(00:34:40), first LSN: 46544:183:1, last LSN:
>46544:183:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'GSM_log'}).
>2006-04-06 19:40:03.22 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7127:1, last LSN:
>1131478:7175:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:42:03.19 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7175:1, last LSN:
>1131478:7220:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:44:03.22 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7220:1, last LSN:
>1131478:7247:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:44:08.94 server Error: 17883, Severity: 1, State: 0
>2006-04-06 19:44:08.94 server The Scheduler 2 appears to be hung. SPID
>58, ECID 0, UMS Context 0x07A92978.
>2006-04-06 19:46:03.24 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7247:1, last LSN:
>1131478:7264:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:48:03.24 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7264:1, last LSN:
>1131478:7286:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:50:02.13 backup Log backed up: Database: GSM, creation
>date(time): 2004/06/05(00:34:40), first LSN: 46544:183:1, last LSN:
>46544:183:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'GSM_log'}).
>2006-04-06 19:50:03.28 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7286:1, last LSN:
>1131478:7293:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:52:03.21 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7293:1, last LSN:
>1131478:7305:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:54:03.18 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7305:1, last LSN:
>1131478:7307:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:56:03.11 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7307:1, last LSN:
>1131478:7309:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:58:03.10 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7309:1, last LSN:
>1131478:7319:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 22:30:02.74 spid55 Attempting to cycle errorlog.
>2006-04-06 22:30:02.83 spid55 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: )
>2006-04-06 22:30:02.83 spid55 Copyright (C) 1988-2002 Microsoft
>Corporation.
>2006-04-06 22:30:02.83 spid55 All rights reserved.
>2006-04-06 22:30:02.83 spid55 Server Process ID is 636.
>2006-04-06 22:30:02.83 spid55 Logging SQL Server messages in file
>'d:\mssql2000\MSSQL\log\ERRORLOG'.
>2006-04-06 22:30:02.83 spid55 Errorlog has been reinitialized. See
>previous log for older entries.
>2006-04-06 22:30:05.14 spid55 DBCC CHECKDB (master) executed by
>domain\user found 0 errors and repaired 0 errors.
>2006-04-06 22:30:06.81 backup Database backed up: Database: master,
>creation date(time): 2006/03/24(20:39:08), pages dumped: 1963, first LSN:
>312:455:1, last LSN: 312:457:1, number of dump devices: 1, device
>information: (FILE=1, TYPE=DISK: {'master_full'}).
>2006-04-06 22:30:08.88 spid54 DBCC CHECKDB (msdb) executed by
domain\user
>found 0 errors and repaired 0 errors.
>2006-04-06 22:30:12.94 backup Database backed up: Database: msdb,
>creation date(time): 2000/08/06(01:40:56), pages dumped: 4811, first LSN:
>18066:979:1, last LSN: 18066:982:1, number of dump devices: 1, device
>information: (FILE=1, TYPE=DISK: {'msdb_full'}).
>2006-04-06 22:30:13.89 spid54 DBCC CHECKDB (sqladmin) executed by
>domain\user found 0 errors and repaired 0 errors.
>2006-04-06 22:30:14.99 backup Database backed up: Database: sqladmin,
>creation date(time): 2003/04/21(14:44:13), pages dumped: 258, first LSN:
>27404:239:1, last LSN: 27404:241:1, number of dump devices: 1, device
>information: (FILE=1, TYPE=DISK: {'sqladmin_full'}).
>2006-04-06 22:30:17.50 spid55 DBCC CHECKDB (GSM) executed by
domain\user
>found 0 errors and repaired 0 errors.
>2006-04-06 22:30:31.42 backup Database log truncated: Database: GSM.
>2006-04-06 22:30:33.74 backup Database backed up: Database: GSM,
creation
>date(time): 2004/06/05(00:34:40), pages dumped: 3750, first LSN:
46638:361:1,
>last LSN: 46638:363:1, number of dump devices: 1, device information:
>(FILE=1, TYPE=DISK: {'GSM_full'}).
>2006-04-06 22:44:07.73 spid56 DBCC CHECKDB (gbk) executed by
domain\user
>found 0 errors and repaired 0 errors.
>2006-04-07 00:18:47.12 backup Database log truncated: Database: gbk.
>2006-04-07 07:14:53.14 server Error: 17883, Severity: 1, State: 0
>2006-04-07 07:14:53.14 server The Scheduler 2 appears to be hung. SPID
0,
>ECID 0, UMS Context 0x07A86300.
>2006-04-07 07:14:56.42 backup Log backed up: Database: GSM, creation
>date(time): 2004/06/05(00:34:40), first LSN: 46638:361:1, last LSN:
>46638:368:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'GSM_log'}).
>2006-04-07 07:20:03.00 backup Log backed up: Database: GSM, creation
>date(time): 2004/06/05(00:34:40), first LSN: 46638:368:1, last LSN:
>46638:368:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'GSM_log'}).
>2006-04-07 07:30:03.30 backup Log backed up: Database: GSM, creation
>date(time): 2004/06/05(00:34:40), first LSN: 46638:368:1, last LSN:
>46638:368:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'GSM_log'}).
>2006-04-07 07:31:17.07 backup BACKUP failed to complete the command
PRINT
>'creating full backup GBK'
>backup database [gbk] to [gbk_full] with stats=100, init, skip
>2006-04-07 07:31:27.73 spid3 SQL Server is terminating due to 'stop'
>request from Service Control Manager.
>...
>Server restart
>

error 17883

Hi, ALL
We are getting error 17883
Backup for DB "GBK" was not creating.
19:22 first error.
~ 07:00 users begin to work. Server does not response.
~ 07:13 administrator log in to console.
07:31 server restart. there are no error in error log.
Errorlog:
2006-04-06 19:20:03.24 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6750:1, last LSN:
1131478:6786:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:21:08.93 server Error: 17883, Severity: 1, State: 0
2006-04-06 19:21:08.93 server The Scheduler 2 appears to be hung. SPID
58, ECID 0, UMS Context 0x07A92978.
2006-04-06 19:22:03.13 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6786:1, last LSN:
1131478:6814:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:22:08.93 server Error: 17883, Severity: 1, State: 0
2006-04-06 19:22:08.93 server The Scheduler 2 appears to be hung. SPID
58, ECID 0, UMS Context 0x07A92978.
2006-04-06 19:24:03.21 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6814:1, last LSN:
1131478:6850:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:26:03.12 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6850:1, last LSN:
1131478:6867:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:28:03.20 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6867:1, last LSN:
1131478:6919:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:30:02.70 backup Log backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), first LSN: 46544:183:1, last LSN:
46544:183:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'GSM_log'}).
2006-04-06 19:30:04.65 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6919:1, last LSN:
1131478:6962:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:31:08.93 server Error: 17883, Severity: 1, State: 0
2006-04-06 19:31:08.93 server The Scheduler 2 appears to be hung. SPID
58, ECID 0, UMS Context 0x07A92978.
2006-04-06 19:32:03.18 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:6962:1, last LSN:
1131478:7027:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:34:03.23 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7027:1, last LSN:
1131478:7070:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:36:03.31 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7070:1, last LSN:
1131478:7096:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:37:08.94 server Error: 17883, Severity: 1, State: 0
2006-04-06 19:37:08.94 server The Scheduler 2 appears to be hung. SPID
58, ECID 0, UMS Context 0x07A92978.
2006-04-06 19:38:03.28 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7096:1, last LSN:
1131478:7127:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:40:02.13 backup Log backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), first LSN: 46544:183:1, last LSN:
46544:183:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'GSM_log'}).
2006-04-06 19:40:03.22 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7127:1, last LSN:
1131478:7175:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:42:03.19 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7175:1, last LSN:
1131478:7220:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:44:03.22 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7220:1, last LSN:
1131478:7247:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:44:08.94 server Error: 17883, Severity: 1, State: 0
2006-04-06 19:44:08.94 server The Scheduler 2 appears to be hung. SPID
58, ECID 0, UMS Context 0x07A92978.
2006-04-06 19:46:03.24 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7247:1, last LSN:
1131478:7264:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:48:03.24 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7264:1, last LSN:
1131478:7286:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:50:02.13 backup Log backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), first LSN: 46544:183:1, last LSN:
46544:183:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'GSM_log'}).
2006-04-06 19:50:03.28 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7286:1, last LSN:
1131478:7293:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:52:03.21 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7293:1, last LSN:
1131478:7305:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:54:03.18 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7305:1, last LSN:
1131478:7307:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:56:03.11 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7307:1, last LSN:
1131478:7309:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 19:58:03.10 backup Log backed up: Database: gbk, creation
date(time): 2004/07/24(12:45:40), first LSN: 1131478:7309:1, last LSN:
1131478:7319:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'gbk_log'}).
2006-04-06 22:30:02.74 spid55 Attempting to cycle errorlog.
2006-04-06 22:30:02.83 spid55 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: )
2006-04-06 22:30:02.83 spid55 Copyright (C) 1988-2002 Microsoft
Corporation.
2006-04-06 22:30:02.83 spid55 All rights reserved.
2006-04-06 22:30:02.83 spid55 Server Process ID is 636.
2006-04-06 22:30:02.83 spid55 Logging SQL Server messages in file
'd:\mssql2000\MSSQL\log\ERRORLOG'.
2006-04-06 22:30:02.83 spid55 Errorlog has been reinitialized. See
previous log for older entries.
2006-04-06 22:30:05.14 spid55 DBCC CHECKDB (master) executed by
domain\user found 0 errors and repaired 0 errors.
2006-04-06 22:30:06.81 backup Database backed up: Database: master,
creation date(time): 2006/03/24(20:39:08), pages dumped: 1963, first LSN:
312:455:1, last LSN: 312:457:1, number of dump devices: 1, device
information: (FILE=1, TYPE=DISK: {'master_full'}).
2006-04-06 22:30:08.88 spid54 DBCC CHECKDB (msdb) executed by domain\user
found 0 errors and repaired 0 errors.
2006-04-06 22:30:12.94 backup Database backed up: Database: msdb,
creation date(time): 2000/08/06(01:40:56), pages dumped: 4811, first LSN:
18066:979:1, last LSN: 18066:982:1, number of dump devices: 1, device
information: (FILE=1, TYPE=DISK: {'msdb_full'}).
2006-04-06 22:30:13.89 spid54 DBCC CHECKDB (sqladmin) executed by
domain\user found 0 errors and repaired 0 errors.
2006-04-06 22:30:14.99 backup Database backed up: Database: sqladmin,
creation date(time): 2003/04/21(14:44:13), pages dumped: 258, first LSN:
27404:239:1, last LSN: 27404:241:1, number of dump devices: 1, device
information: (FILE=1, TYPE=DISK: {'sqladmin_full'}).
2006-04-06 22:30:17.50 spid55 DBCC CHECKDB (GSM) executed by domain\user
found 0 errors and repaired 0 errors.
2006-04-06 22:30:31.42 backup Database log truncated: Database: GSM.
2006-04-06 22:30:33.74 backup Database backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), pages dumped: 3750, first LSN: 46638:361:1,
last LSN: 46638:363:1, number of dump devices: 1, device information:
(FILE=1, TYPE=DISK: {'GSM_full'}).
2006-04-06 22:44:07.73 spid56 DBCC CHECKDB (gbk) executed by domain\user
found 0 errors and repaired 0 errors.
2006-04-07 00:18:47.12 backup Database log truncated: Database: gbk.
2006-04-07 07:14:53.14 server Error: 17883, Severity: 1, State: 0
2006-04-07 07:14:53.14 server The Scheduler 2 appears to be hung. SPID 0,
ECID 0, UMS Context 0x07A86300.
2006-04-07 07:14:56.42 backup Log backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), first LSN: 46638:361:1, last LSN:
46638:368:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'GSM_log'}).
2006-04-07 07:20:03.00 backup Log backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), first LSN: 46638:368:1, last LSN:
46638:368:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'GSM_log'}).
2006-04-07 07:30:03.30 backup Log backed up: Database: GSM, creation
date(time): 2004/06/05(00:34:40), first LSN: 46638:368:1, last LSN:
46638:368:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'GSM_log'}).
2006-04-07 07:31:17.07 backup BACKUP failed to complete the command PRINT
'creating full backup GBK'
backup database [gbk] to [gbk_full] with stats=100, init, skip
2006-04-07 07:31:27.73 spid3 SQL Server is terminating due to 'stop'
request from Service Control Manager.
...
Server restartHello
In essence all this 17883 message tells us is that no context switches were
made on the scheduler. The two things that I can think of are a) a hung
scheduler, or b) a thread that is not properly yielding to the scheduler.
I would suggest opening up a case with PSS since this need memory dump to
find clues to troubleshoot the issue.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Also, please make sure you have latest service pack installed on the
server.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: error 17883
>thread-index: AcZaGCgcfWSWeH5/QoCXiTp9pSJ7YQ==>X-WBNR-Posting-Host: 212.12.3.11
>From: =?Utf-8?B?dmFkaW1t?= <white@.community.nospam>
>Subject: error 17883
>Date: Fri, 7 Apr 2006 00:52:02 -0700
>Lines: 186
>Message-ID: <1961654E-C620-4AFA-BD4A-A731BCA877CE@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>Newsgroups: microsoft.public.sqlserver.server
>Path: TK2MSFTNGXA01.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:427204
>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Hi, ALL
>We are getting error 17883
>Backup for DB "GBK" was not creating.
> 19:22 first error.
>~ 07:00 users begin to work. Server does not response.
>~ 07:13 administrator log in to console.
> 07:31 server restart. there are no error in error log.
>Errorlog:
>2006-04-06 19:20:03.24 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6750:1, last LSN:
>1131478:6786:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:21:08.93 server Error: 17883, Severity: 1, State: 0
>2006-04-06 19:21:08.93 server The Scheduler 2 appears to be hung. SPID
>58, ECID 0, UMS Context 0x07A92978.
>2006-04-06 19:22:03.13 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6786:1, last LSN:
>1131478:6814:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:22:08.93 server Error: 17883, Severity: 1, State: 0
>2006-04-06 19:22:08.93 server The Scheduler 2 appears to be hung. SPID
>58, ECID 0, UMS Context 0x07A92978.
>2006-04-06 19:24:03.21 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6814:1, last LSN:
>1131478:6850:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:26:03.12 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6850:1, last LSN:
>1131478:6867:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:28:03.20 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6867:1, last LSN:
>1131478:6919:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:30:02.70 backup Log backed up: Database: GSM, creation
>date(time): 2004/06/05(00:34:40), first LSN: 46544:183:1, last LSN:
>46544:183:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'GSM_log'}).
>2006-04-06 19:30:04.65 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6919:1, last LSN:
>1131478:6962:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:31:08.93 server Error: 17883, Severity: 1, State: 0
>2006-04-06 19:31:08.93 server The Scheduler 2 appears to be hung. SPID
>58, ECID 0, UMS Context 0x07A92978.
>2006-04-06 19:32:03.18 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:6962:1, last LSN:
>1131478:7027:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:34:03.23 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7027:1, last LSN:
>1131478:7070:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:36:03.31 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7070:1, last LSN:
>1131478:7096:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:37:08.94 server Error: 17883, Severity: 1, State: 0
>2006-04-06 19:37:08.94 server The Scheduler 2 appears to be hung. SPID
>58, ECID 0, UMS Context 0x07A92978.
>2006-04-06 19:38:03.28 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7096:1, last LSN:
>1131478:7127:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:40:02.13 backup Log backed up: Database: GSM, creation
>date(time): 2004/06/05(00:34:40), first LSN: 46544:183:1, last LSN:
>46544:183:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'GSM_log'}).
>2006-04-06 19:40:03.22 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7127:1, last LSN:
>1131478:7175:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:42:03.19 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7175:1, last LSN:
>1131478:7220:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:44:03.22 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7220:1, last LSN:
>1131478:7247:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:44:08.94 server Error: 17883, Severity: 1, State: 0
>2006-04-06 19:44:08.94 server The Scheduler 2 appears to be hung. SPID
>58, ECID 0, UMS Context 0x07A92978.
>2006-04-06 19:46:03.24 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7247:1, last LSN:
>1131478:7264:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:48:03.24 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7264:1, last LSN:
>1131478:7286:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:50:02.13 backup Log backed up: Database: GSM, creation
>date(time): 2004/06/05(00:34:40), first LSN: 46544:183:1, last LSN:
>46544:183:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'GSM_log'}).
>2006-04-06 19:50:03.28 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7286:1, last LSN:
>1131478:7293:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:52:03.21 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7293:1, last LSN:
>1131478:7305:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:54:03.18 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7305:1, last LSN:
>1131478:7307:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:56:03.11 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7307:1, last LSN:
>1131478:7309:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 19:58:03.10 backup Log backed up: Database: gbk, creation
>date(time): 2004/07/24(12:45:40), first LSN: 1131478:7309:1, last LSN:
>1131478:7319:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'gbk_log'}).
>2006-04-06 22:30:02.74 spid55 Attempting to cycle errorlog.
>2006-04-06 22:30:02.83 spid55 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: )
>2006-04-06 22:30:02.83 spid55 Copyright (C) 1988-2002 Microsoft
>Corporation.
>2006-04-06 22:30:02.83 spid55 All rights reserved.
>2006-04-06 22:30:02.83 spid55 Server Process ID is 636.
>2006-04-06 22:30:02.83 spid55 Logging SQL Server messages in file
>'d:\mssql2000\MSSQL\log\ERRORLOG'.
>2006-04-06 22:30:02.83 spid55 Errorlog has been reinitialized. See
>previous log for older entries.
>2006-04-06 22:30:05.14 spid55 DBCC CHECKDB (master) executed by
>domain\user found 0 errors and repaired 0 errors.
>2006-04-06 22:30:06.81 backup Database backed up: Database: master,
>creation date(time): 2006/03/24(20:39:08), pages dumped: 1963, first LSN:
>312:455:1, last LSN: 312:457:1, number of dump devices: 1, device
>information: (FILE=1, TYPE=DISK: {'master_full'}).
>2006-04-06 22:30:08.88 spid54 DBCC CHECKDB (msdb) executed by
domain\user
>found 0 errors and repaired 0 errors.
>2006-04-06 22:30:12.94 backup Database backed up: Database: msdb,
>creation date(time): 2000/08/06(01:40:56), pages dumped: 4811, first LSN:
>18066:979:1, last LSN: 18066:982:1, number of dump devices: 1, device
>information: (FILE=1, TYPE=DISK: {'msdb_full'}).
>2006-04-06 22:30:13.89 spid54 DBCC CHECKDB (sqladmin) executed by
>domain\user found 0 errors and repaired 0 errors.
>2006-04-06 22:30:14.99 backup Database backed up: Database: sqladmin,
>creation date(time): 2003/04/21(14:44:13), pages dumped: 258, first LSN:
>27404:239:1, last LSN: 27404:241:1, number of dump devices: 1, device
>information: (FILE=1, TYPE=DISK: {'sqladmin_full'}).
>2006-04-06 22:30:17.50 spid55 DBCC CHECKDB (GSM) executed by
domain\user
>found 0 errors and repaired 0 errors.
>2006-04-06 22:30:31.42 backup Database log truncated: Database: GSM.
>2006-04-06 22:30:33.74 backup Database backed up: Database: GSM,
creation
>date(time): 2004/06/05(00:34:40), pages dumped: 3750, first LSN:
46638:361:1,
>last LSN: 46638:363:1, number of dump devices: 1, device information:
>(FILE=1, TYPE=DISK: {'GSM_full'}).
>2006-04-06 22:44:07.73 spid56 DBCC CHECKDB (gbk) executed by
domain\user
>found 0 errors and repaired 0 errors.
>2006-04-07 00:18:47.12 backup Database log truncated: Database: gbk.
>2006-04-07 07:14:53.14 server Error: 17883, Severity: 1, State: 0
>2006-04-07 07:14:53.14 server The Scheduler 2 appears to be hung. SPID
0,
>ECID 0, UMS Context 0x07A86300.
>2006-04-07 07:14:56.42 backup Log backed up: Database: GSM, creation
>date(time): 2004/06/05(00:34:40), first LSN: 46638:361:1, last LSN:
>46638:368:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'GSM_log'}).
>2006-04-07 07:20:03.00 backup Log backed up: Database: GSM, creation
>date(time): 2004/06/05(00:34:40), first LSN: 46638:368:1, last LSN:
>46638:368:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'GSM_log'}).
>2006-04-07 07:30:03.30 backup Log backed up: Database: GSM, creation
>date(time): 2004/06/05(00:34:40), first LSN: 46638:368:1, last LSN:
>46638:368:1, number of dump devices: 1, device information: (FILE=1,
>TYPE=DISK: {'GSM_log'}).
>2006-04-07 07:31:17.07 backup BACKUP failed to complete the command
PRINT
>'creating full backup GBK'
>backup database [gbk] to [gbk_full] with stats=100, init, skip
>2006-04-07 07:31:27.73 spid3 SQL Server is terminating due to 'stop'
>request from Service Control Manager.
>...
>Server restart
>

Error 16916: A cursor with the name "tentative_cursor" does not exist

Hi,

I am creating a stored procedure which contains the declaration of a cursor, fetch the cursor, close the cursor, deallocate cursor. I tried executing this code in SQL analyzer and found working. But when I use the same code inside a create stored procedure, it throws the following error message.

Error 16916: A cursor with the name "tentative_cursor" does not exist

As a result, I am not able to proceed in creation of stored procedure with the above SQL statements.

Can any one help me where the problem is..

My SQL statements..
---
DECLARE tentative_cursor CURSOR
FOR select * from TbTentativeBookingTable where ExpTime < GETDATE()
GO
OPEN tentative_cursor
GO
DECLARE @.transactionno BIGINT
...
...
FETCH ...
CLOSE tentative_cursor
---

Thanks
-SudhakarThe GO keyword serves to terminate a SQL batch, so it implicitly ends a stored procedure declaration. There may be other problems too, but at the very least you need to remove the GO keyword(s).

-PatP

Error 156 Creating View

I tried writing the following statement in a stored procedure, and I get an
Error message: "Error 156: Incorrect syntax near the keyword 'VIEW'."
CREATE VIEW VW_TotalAccepted
AS
SELECT [CustID], [ReqNo], Sum([AcceptedQty]) AS [Total]
FROM [DelDetail]
GROUP BY [DelDetail].[CustID], [DelDetail].[ReqNo]
What's wrong with the syntax? Isn't it the way to create a View?You cannot create a VIEW inside a SP
"wrytat" wrote:

> I tried writing the following statement in a stored procedure, and I get a
n
> Error message: "Error 156: Incorrect syntax near the keyword 'VIEW'."
> CREATE VIEW VW_TotalAccepted
> AS
> SELECT [CustID], [ReqNo], Sum([AcceptedQty]) AS [Total]
> FROM [DelDetail]
> GROUP BY [DelDetail].[CustID], [DelDetail].[ReqNo]
> What's wrong with the syntax? Isn't it the way to create a View?|||Then... Just ask a stupid question regarding View. If I add a new row or
change some values of some columns in a table that is included in a View
after creating the View, will the change be reflected in the View?
"Chandra" wrote:
> You cannot create a VIEW inside a SP
> "wrytat" wrote:
>|||Yes. A view doesn't store any data, it is only a "view" into the data stored
in your tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:B14BFAD9-CF63-4645-A61C-86AA8854F4D1@.microsoft.com...
> Then... Just ask a stupid question regarding View. If I add a new row or
> change some values of some columns in a table that is included in a View
> after creating the View, will the change be reflected in the View?
> "Chandra" wrote:
>|||Yes. View doesnt store any data. Selecting rows from a view is as good as
executing the query that created it.
We go for views because, it improves the performance of a query.
thanks and regards
Chandra
"wrytat" wrote:
> Then... Just ask a stupid question regarding View. If I add a new row or
> change some values of some columns in a table that is included in a View
> after creating the View, will the change be reflected in the View?
> "Chandra" wrote:
>

error 15247 while creating subscription

Hi,
I have a local and a remote sql server that connect to remote via dial-up
connection.
In local server, I'm member of sysadmin server role but in remote server
just member of dbowner database role.
I create my publication on the local but when I want to create pull
subscription
at the subscriber(remote server) I get this error:User does not have
permission to perform this action(error 15247).
When I try to create push subscription from local server, the error is
something like: Cannot initialize the
subscription.
Any help would be greatly appreciated.
Thanks in advance.
Amin
Amin,
please check that your remote server's agent in the PAL on the publisher.
Also, that the subscriber been added to the list of potential subscribers
(sp_addsubscriber).
HTH,
Paul Ibison
|||Paul,
My local server is both publisher and distributor. All agents are running
locally. sa login is in PAL of my publication. Would you please write me
more detail.
Thanks,
Amin
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uTbm#x7VEHA.584@.TK2MSFTNGP09.phx.gbl...
> Amin,
> please check that your remote server's agent in the PAL on the publisher.
> Also, that the subscriber been added to the list of potential subscribers
> (sp_addsubscriber).
> HTH,
> Paul Ibison
>
|||Amin,
when replicating between non-trusted domains, several configuration changes
are necessary compared to the normal setup. Please have a look at this
article, which explains them: http://support.microsoft.com/?id=321822.
You'll need to set up an alias, consider pass-through security or SQL
security and the account used on subscriber must be in the PAL on the
publisher. Also you might need to edit the HOSTS file to resolve the IP
address. To test at various stages, try to use Query Analyser on the
subscriber to connect to the publisher using the subscriber agent's login.
HTH,
Paul Ibison

Error 14258 while creating a publication

Hi all,
I am trying to create a merge-publication (SQL 2000 SP4).
Unfortunately, it is not working because the wizard pops up with error
14258: Cannot perform this operation while SQL Server Agent is
starting. Try again later.
Checked both services, they seem correct. Both are using (the same)
domain-user with sufficient privs to access the SQLServer box. They
both run fine (all in the green) and there are no errors in the logs.
SQL itself runs fine and I can access all data from any computer in the
network.
Just to be sure, I've stopped and restarted both services but the same
error comes back again. I've checked all settings but as far as I can
see, the box is configured properly.
Any ideas?
Thx,
FS
Check out this MS article:
http://support.microsoft.com/?id=303287
John Steen
"Ferry" wrote:

> Hi all,
> I am trying to create a merge-publication (SQL 2000 SP4).
> Unfortunately, it is not working because the wizard pops up with error
> 14258: Cannot perform this operation while SQL Server Agent is
> starting. Try again later.
> Checked both services, they seem correct. Both are using (the same)
> domain-user with sufficient privs to access the SQLServer box. They
> both run fine (all in the green) and there are no errors in the logs.
> SQL itself runs fine and I can access all data from any computer in the
> network.
> Just to be sure, I've stopped and restarted both services but the same
> error comes back again. I've checked all settings but as far as I can
> see, the box is configured properly.
> Any ideas?
> Thx,
> FS
>
>
|||Hmm, I guess I was too fast... Solved the problem already by googling a
bit further. As a thank you for reading I will share the solution :-)
When having MSDTC switched on _and_ using NT-fibres in the SQL
configuration, you can encounter this error. Switching the NT-fibres
off (and stopping and starting the services again) resolved the
problem. MSKB article, aaaaargh, how could I missed it in the first
place...
From Ferry :
> Hi all,
> I am trying to create a merge-publication (SQL 2000 SP4). Unfortunately, it
> is not working because the wizard pops up with error 14258: Cannot perform
> this operation while SQL Server Agent is starting. Try again later.
>

Error 14053 and Push Replication

SQL Server Enterprise Manager encountered errors creating push subscriptions
for the following Subscribers:
[Subscriber Server Name]: Error 14053: Cannot load the DLL replincrementlsn
extended procedure, or one of the DLLs it references. Reason: 126(error not
found).
The subscription could not be updated at this time.
The subscription could not be created.
The publisher and distributor are running SQL Server 2000 SP3a, the
subscriber is RTM (no SP applied expect for the criticial release update).
The subscriber has a new server just released into production, hence why no
SP yet.
The publisher and subscriber are on non trusting domains, but on the
internal network.
I have checked the SP log file (C:\Windows\) and have been advised that both
sp_replincrementlsn and sp_replincrementlsn_internal exist in the master
table on both servers. I also reattempted to install SP3a on the publisher
server to no avail.
All work has been carried out in Ent Mgr.
I thank anyone in advance for their assistance, as I am totally lost for
ideas.
Betty,
can you run this on the distributor, just to confirm that the stored
procedure was created correctly:
sp_helptext sp_replincrementlsn
Please check that xprepl.dll is in your sql bin directory (eg C:\Program
Files\Microsoft SQL Server\MSSQL\Binn).
Please check the sql server error log for any issues recorded there.
Have a look at sqlsp.log file from the c:\windows directory this might shed
some light on any failed actions during the sp installation
If any of this returns something interesting then please post it up,
otherwise I'd reinstall sp3a.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I ran the steps you requested on our distribution server (which is also the
publication server)
- sp_helptext sp_replincrementlsn returned no issue, the sp does exist, but
only in the master table.
- xprepl.dll DOES exist in the BINN directory of the SQL Server
- The SQL Server reports no error, and even reports that it is using
xprepl.dll for replication
- SQLsp.log appears to contain no critical errors (the only error of sort is
the one that indicates that some data files could not be modified - the
reason being that those databases are in the offline state.
- I am still yet to reinstall SP3a, awaiting for authorisation from the
server controller, but previous attempts have not worked.
Anyother ideas?
Thanks,
Betty.
"Paul Ibison" wrote:

> Betty,
> can you run this on the distributor, just to confirm that the stored
> procedure was created correctly:
> sp_helptext sp_replincrementlsn
> Please check that xprepl.dll is in your sql bin directory (eg C:\Program
> Files\Microsoft SQL Server\MSSQL\Binn).
> Please check the sql server error log for any issues recorded there.
> Have a look at sqlsp.log file from the c:\windows directory this might shed
> some light on any failed actions during the sp installation
> If any of this returns something interesting then please post it up,
> otherwise I'd reinstall sp3a.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||With this type of installation error, I'd apply the
service pack, reinstall completely and ring PSS in this
order assuming the error is repeated at each step.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
one interesting thing is that the SP3Readme.htm states that this error can
be safely ignored.
Regards,
Betty.
"Paul Ibison" wrote:

> With this type of installation error, I'd apply the
> service pack, reinstall completely and ring PSS in this
> order assuming the error is repeated at each step.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Paul,
after applying SP3 it appears that the replication works, it states, that it
said the replication was created successfully.
I think what happens, is if you attempt to create your first publication,
distributor, or subscriber, after SP3 has been applied, you need to reapply
SP3 to update the replication system files.
Thank you for your assitance,
Betty.

ERROR 1141 on mySQL creating a User on NT 4.0

Hi,

I just installed mySQL on MS NT 4.0 workstation and applied Service Pack
6a. When trying to add a new user using the Control Center I get an
info message that say that user was added but this is followed by an
error message that says:

There is no such grant defined for user 'username' on host 'hostname'

Does anyone know what is going on here?

Please respond in email.

Thanks,
Paul"Paul K. McKneely" <pkmckneely@.ev1.net> wrote in message
news:41499067.C056EB65@.ev1.net...
> Hi,
> I just installed mySQL on MS NT 4.0 workstation and applied Service Pack
> 6a. When trying to add a new user using the Control Center I get an
> info message that say that user was added but this is followed by an
> error message that says:
> There is no such grant defined for user 'username' on host 'hostname'
> Does anyone know what is going on here?
> Please respond in email.
> Thanks,
> Paul

This is an MSSQL (Microsoft SQL Server) newsgroup, so you will probably get
a better response in a MySQL forum.

Simon

error 0xE0040200

Hello,

I′ve got this problem when I′m creating a new cube with the Microsoft Visual Studio 2005 8.0.550727.42 (RTM.050727-4200) and my operative system is Microsoft(R) Windows(R) Server 2003, Standard Edition. It isn′t the first cube I′ve created in this project, but it′s the the first time I get the problem.

Could someone help me?

Can you provide bit more informatio about the problem.

At what point you are getting the error?
Is that at deployment time or you get the error while running Cube Wizard?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I′m getting the error when I′m browsing the cube using the third level of a hierarchy. It happens whit all the hierarchies of the project so I think It isn′t a problem of the hierarchy.|||

Try installing SP1 of SQL server and see if the same problem happens.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Erratic SMO Performance

I have written a console app which uses SMO to automate the scripting and the creating of our database.

For some strange reason the performance varies from blindingly fast to excruciatingly slow and I cannot seem to account for the cause. Can anyone tell me why?

For example, when scripting 100 tables, sometimes it will take 1.5 second to do all of them; sometimes it takes about 1.5 seconds to do each one!

Some debugging uncovered the fact that it is the scripting of DROP statements that is the culprit. Scripting a CREATE statment seems to take between 1 and 70ms, whereas scripting a DROP statement *sometimes* takes the same amount of time, but *sometimes* it takes about 1400ms!

I've tried varying all sorts of things but I can't reliably reproduce the problem. Indeed, when I was debugging it to get these metrics, performance suddenly bounced back and there was no longer a problem to debug. Nevertheless, the slow performance occurs frequently, if not more often than the fast performance. I thought it might be memory problems, but memory usage doesn't seem to be any different between high and low performance scenarios.

Obviously, there could be a better way of using SMO that would side step the problem completely, in which case I'm all ears. :-) Here is the code for scripting all tables to a file.

OnProgress(new ProgressEventArgs(""));

OnProgress(new ProgressEventArgs("Scripting tables..."));

ScriptingOptions scriptOptions = new ScriptingOptions();

scriptOptions.AppendToFile = true; // but file will be deleted first

scriptOptions.FileName = PathHelper.IncludeTrailingBackslash(options.Directory) + Constants.CreateTablesFileName;

scriptOptions.DriAll = false;

scriptOptions.ExtendedProperties = true;

scriptOptions.IncludeHeaders = false;

scriptOptions.Indexes = true;

scriptOptions.ClusteredIndexes = true;

scriptOptions.NonClusteredIndexes = true;

scriptOptions.ScriptDrops = true;

scriptOptions.Triggers = true;

scriptOptions.NoFileGroup = false;

DataTable dt = db.EnumObjects(DatabaseObjectTypes.Table, SortOrder.Name);

DataView view = dt.DefaultView;

view.RowFilter = "(Schema <> 'sys') AND (Schema <> 'INFORMATION_SCHEMA')";

numTables = view.Count;

numSuccessfulTables = 0;

File.Delete(scriptOptions.FileName);

using (StreamWriter writer = new StreamWriter(scriptOptions.FileName, false, new UnicodeEncoding()))

{

writer.WriteLine("USE " + db.Name);

writer.WriteLine("GO");

writer.WriteLine();

}

foreach (DataRowView row in view)

{

Table tbl = db.Tables[row["Name"].ToString(), row["Schema"].ToString()];

// script drop

scriptOptions.ScriptDrops = true;

scriptOptions.IncludeIfNotExists = true;

//ignore diagram stuff

if (tbl.Name == "sysdiagrams")

{

numTables--;

continue;

}

tbl.Script(scriptOptions);

// script create

scriptOptions.ScriptDrops = false;

scriptOptions.IncludeIfNotExists = false;

tbl.Script(scriptOptions);

numSuccessfulTables++;

OnProgress(new ProgressEventArgs(null));

}

Is anyone from MS able to comment on this?|||

I spent a lot of time researching the SMO issue and decided to share what I know and what I have done. Here is some of what I found:

1. It seems like there are very few options to speed up access to SQL Server via SMO objects, at the moment. Several people said they have reported this as a major issue to Microsoft.

2. SMO is used natively by SQL Server 2005, so using DMO or other methods are likely to be deprecated in the future. Use them with caution.

3. SMO Scripting queries SQL Server very excessively. This appears to be the major problem and I haven't found away around it. A likely culprit is that, by default, not all SMO object attributes are retrieved from SQL Server at once. Any properties that are used but not yet retrieved require a round trip to the server.

4. There does not appear to be a class or method which will allow you to script an entire server or database at one time. You need to script each object seperately.

Module sqlas

Private stringCollection As New StringCollection
Private scripter As New Scripter

Sub Main()

Try

Dim selectedServer As New Server("NameOfServer")

' The Server object's DefaultInitFields does not include the IsSystemObject property by default
' This caused a major perfomance hit. These methods cause the Server object to include
' the all properties for the selected DB objects.

selectedServer.SetDefaultInitFields(True)

'***** These method calls were being used to set InitFields seperately rather than all at once
'selectedServer.SetDefaultInitFields(GetType(User), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Table), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(View), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(StoredProcedure), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(UserDefinedFunction), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Trigger), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Column), True)
'*******************************

' The Scripter requires a reference to the server selected for scripting.
scripter.Server = selectedServer

' Alters the Scripter.Options because they differ from the default.
scripter.Options.Permissions = True
scripter.Options.DriPrimaryKey = True
scripter.Options.IncludeIfNotExists = True
scripter.Options.NoCollation = True

Dim database As Database = selectedServer.Databases("SelectedDatabase")

'***** These calls decreased time by 50%, but it is still too slow
database.PrefetchObjects(GetType(Table))
database.PrefetchObjects(GetType(User))
database.PrefetchObjects(GetType(View))
database.PrefetchObjects(GetType(StoredProcedure))
database.PrefetchObjects(GetType(UserDefinedFunction))
'*************************

' Location to store the scripts
Using streamWriter As New StreamWriter("PathToWriteTo", False)
For Each user As User In database.Users
If (user.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {user.Urn}, streamWriter)
End If
Next

' ROLES - Scripts Role objects
Console.ForegroundColor = ConsoleColor.Yellow
For Each databaseRole As DatabaseRole In database.Roles
If (databaseRole.Name.StartsWith("db_") = False) Then
ScriptSmoObject(New Urn() {databaseRole.Urn}, streamWriter)
End If
Next

' SCHEMAS - Scripts Schema objects
For Each schema As Schema In database.Schemas
ScriptSmoObject(New Urn() {schema.Urn}, streamWriter)
Next

' TABLES - Scripts Table objects
' The Scripter.Options.DriForeignKeys property is set to false so that FKs are not
' scripted with the table objects. They are scripted at the end of the file.
scripter.Options.DriForeignKeys = False
For Each table As Table In database.Tables
If (table.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {table.Urn}, streamWriter)

' Table specific triggers will be scripted with their respective tables
For Each trigger As Trigger In table.Triggers
ScriptSmoObject(New Urn() {trigger.Urn}, streamWriter)
Next
End If
Next table

' VIEWS - Scripts View objects
For Each view As View In database.Views
If view.IsSystemObject = False Then
ScriptSmoObject(New Urn() {view.Urn}, streamWriter)
End If
Next view

' STORED PROCEDURES - Scripts StoredProcedure objects
For Each storedProcedure As StoredProcedure In database.StoredProcedures
If (storedProcedure.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {storedProcedure.Urn}, streamWriter)
End If
Next storedProcedure

' USE DEFINED FUNCTIONS - Scripts UserDefinedFunction objects
For Each userDefinedFunction As UserDefinedFunction In database.UserDefinedFunctions
If (userDefinedFunction.IsSystemObject) = False Then
ScriptSmoObject(New Urn() {userDefinedFunction.Urn}, streamWriter)
End If
Next

' TRIGGERS - Scripts database level Trigger objects
For Each trigger As Trigger In database.Triggers
If trigger.IsSystemObject = False Then
ScriptSmoObject(New Urn() {trigger.Urn}, streamWriter)
End If
Next

' FOREIGN KEYS - Scripts the ForeignKey objects for all tables in the DB
' The Scripter.Options.DriForeignKeys property is set to true so that FKs will
' be scripted at the end of the file
scripter.Options.DriForeignKeys = True
For Each table As Table In database.Tables
For Each foreignKey As ForeignKey In table.ForeignKeys
ScriptSmoObject(New Urn() {foreignKey.Urn}, streamWriter)
Next

streamWriter.Flush()
End Using

Catch ex As Exception
Console.WriteLine(ex.Message)
End Try

End Sub

''' <summary>
''' Scripts an SMO object
''' </summary>
''' <param name="urn">The Urn of the SqlSmoObject to script</param>
''' <param name="streamWriter">The StreamWriter used to write the script to file.</param>
''' <remarks>None</remarks>
Private Sub ScriptSmoObject(ByRef urn() As Urn, ByRef streamWriter As TextWriter)
stringCollection = scripter.Script(urn)
For Each script As String In stringCollection
streamWriter.WriteLine(script)
Next

End Sub
End Module

Erratic SMO Performance

I have written a console app which uses SMO to automate the scripting and the creating of our database.

For some strange reason the performance varies from blindingly fast to excruciatingly slow and I cannot seem to account for the cause. Can anyone tell me why?

For example, when scripting 100 tables, sometimes it will take 1.5 second to do all of them; sometimes it takes about 1.5 seconds to do each one!

Some debugging uncovered the fact that it is the scripting of DROP statements that is the culprit. Scripting a CREATE statment seems to take between 1 and 70ms, whereas scripting a DROP statement *sometimes* takes the same amount of time, but *sometimes* it takes about 1400ms!

I've tried varying all sorts of things but I can't reliably reproduce the problem. Indeed, when I was debugging it to get these metrics, performance suddenly bounced back and there was no longer a problem to debug. Nevertheless, the slow performance occurs frequently, if not more often than the fast performance. I thought it might be memory problems, but memory usage doesn't seem to be any different between high and low performance scenarios.

Obviously, there could be a better way of using SMO that would side step the problem completely, in which case I'm all ears. :-) Here is the code for scripting all tables to a file.

OnProgress(new ProgressEventArgs(""));

OnProgress(new ProgressEventArgs("Scripting tables..."));

ScriptingOptions scriptOptions = new ScriptingOptions();

scriptOptions.AppendToFile = true; // but file will be deleted first

scriptOptions.FileName = PathHelper.IncludeTrailingBackslash(options.Directory) + Constants.CreateTablesFileName;

scriptOptions.DriAll = false;

scriptOptions.ExtendedProperties = true;

scriptOptions.IncludeHeaders = false;

scriptOptions.Indexes = true;

scriptOptions.ClusteredIndexes = true;

scriptOptions.NonClusteredIndexes = true;

scriptOptions.ScriptDrops = true;

scriptOptions.Triggers = true;

scriptOptions.NoFileGroup = false;

DataTable dt = db.EnumObjects(DatabaseObjectTypes.Table, SortOrder.Name);

DataView view = dt.DefaultView;

view.RowFilter = "(Schema <> 'sys') AND (Schema <> 'INFORMATION_SCHEMA')";

numTables = view.Count;

numSuccessfulTables = 0;

File.Delete(scriptOptions.FileName);

using (StreamWriter writer = new StreamWriter(scriptOptions.FileName, false, new UnicodeEncoding()))

{

writer.WriteLine("USE " + db.Name);

writer.WriteLine("GO");

writer.WriteLine();

}

foreach (DataRowView row in view)

{

Table tbl = db.Tables[row["Name"].ToString(), row["Schema"].ToString()];

// script drop

scriptOptions.ScriptDrops = true;

scriptOptions.IncludeIfNotExists = true;

//ignore diagram stuff

if (tbl.Name == "sysdiagrams")

{

numTables--;

continue;

}

tbl.Script(scriptOptions);

// script create

scriptOptions.ScriptDrops = false;

scriptOptions.IncludeIfNotExists = false;

tbl.Script(scriptOptions);

numSuccessfulTables++;

OnProgress(new ProgressEventArgs(null));

}

Is anyone from MS able to comment on this?|||

I spent a lot of time researching the SMO issue and decided to share what I know and what I have done. Here is some of what I found:

1. It seems like there are very few options to speed up access to SQL Server via SMO objects, at the moment. Several people said they have reported this as a major issue to Microsoft.

2. SMO is used natively by SQL Server 2005, so using DMO or other methods are likely to be deprecated in the future. Use them with caution.

3. SMO Scripting queries SQL Server very excessively. This appears to be the major problem and I haven't found away around it. A likely culprit is that, by default, not all SMO object attributes are retrieved from SQL Server at once. Any properties that are used but not yet retrieved require a round trip to the server.

4. There does not appear to be a class or method which will allow you to script an entire server or database at one time. You need to script each object seperately.

Module sqlas

Private stringCollection As New StringCollection
Private scripter As New Scripter

Sub Main()

Try

Dim selectedServer As New Server("NameOfServer")

' The Server object's DefaultInitFields does not include the IsSystemObject property by default
' This caused a major perfomance hit. These methods cause the Server object to include
' the all properties for the selected DB objects.

selectedServer.SetDefaultInitFields(True)

'***** These method calls were being used to set InitFields seperately rather than all at once
'selectedServer.SetDefaultInitFields(GetType(User), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Table), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(View), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(StoredProcedure), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(UserDefinedFunction), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Trigger), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Column), True)
'*******************************

' The Scripter requires a reference to the server selected for scripting.
scripter.Server = selectedServer

' Alters the Scripter.Options because they differ from the default.
scripter.Options.Permissions = True
scripter.Options.DriPrimaryKey = True
scripter.Options.IncludeIfNotExists = True
scripter.Options.NoCollation = True

Dim database As Database = selectedServer.Databases("SelectedDatabase")

'***** These calls decreased time by 50%, but it is still too slow
database.PrefetchObjects(GetType(Table))
database.PrefetchObjects(GetType(User))
database.PrefetchObjects(GetType(View))
database.PrefetchObjects(GetType(StoredProcedure))
database.PrefetchObjects(GetType(UserDefinedFunction))
'*************************

' Location to store the scripts
Using streamWriter As New StreamWriter("PathToWriteTo", False)
For Each user As User In database.Users
If (user.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {user.Urn}, streamWriter)
End If
Next

' ROLES - Scripts Role objects
Console.ForegroundColor = ConsoleColor.Yellow
For Each databaseRole As DatabaseRole In database.Roles
If (databaseRole.Name.StartsWith("db_") = False) Then
ScriptSmoObject(New Urn() {databaseRole.Urn}, streamWriter)
End If
Next

' SCHEMAS - Scripts Schema objects
For Each schema As Schema In database.Schemas
ScriptSmoObject(New Urn() {schema.Urn}, streamWriter)
Next

' TABLES - Scripts Table objects
' The Scripter.Options.DriForeignKeys property is set to false so that FKs are not
' scripted with the table objects. They are scripted at the end of the file.
scripter.Options.DriForeignKeys = False
For Each table As Table In database.Tables
If (table.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {table.Urn}, streamWriter)

' Table specific triggers will be scripted with their respective tables
For Each trigger As Trigger In table.Triggers
ScriptSmoObject(New Urn() {trigger.Urn}, streamWriter)
Next
End If
Next table

' VIEWS - Scripts View objects
For Each view As View In database.Views
If view.IsSystemObject = False Then
ScriptSmoObject(New Urn() {view.Urn}, streamWriter)
End If
Next view

' STORED PROCEDURES - Scripts StoredProcedure objects
For Each storedProcedure As StoredProcedure In database.StoredProcedures
If (storedProcedure.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {storedProcedure.Urn}, streamWriter)
End If
Next storedProcedure

' USE DEFINED FUNCTIONS - Scripts UserDefinedFunction objects
For Each userDefinedFunction As UserDefinedFunction In database.UserDefinedFunctions
If (userDefinedFunction.IsSystemObject) = False Then
ScriptSmoObject(New Urn() {userDefinedFunction.Urn}, streamWriter)
End If
Next

' TRIGGERS - Scripts database level Trigger objects
For Each trigger As Trigger In database.Triggers
If trigger.IsSystemObject = False Then
ScriptSmoObject(New Urn() {trigger.Urn}, streamWriter)
End If
Next

' FOREIGN KEYS - Scripts the ForeignKey objects for all tables in the DB
' The Scripter.Options.DriForeignKeys property is set to true so that FKs will
' be scripted at the end of the file
scripter.Options.DriForeignKeys = True
For Each table As Table In database.Tables
For Each foreignKey As ForeignKey In table.ForeignKeys
ScriptSmoObject(New Urn() {foreignKey.Urn}, streamWriter)
Next

streamWriter.Flush()
End Using

Catch ex As Exception
Console.WriteLine(ex.Message)
End Try

End Sub

''' <summary>
''' Scripts an SMO object
''' </summary>
''' <param name="urn">The Urn of the SqlSmoObject to script</param>
''' <param name="streamWriter">The StreamWriter used to write the script to file.</param>
''' <remarks>None</remarks>
Private Sub ScriptSmoObject(ByRef urn() As Urn, ByRef streamWriter As TextWriter)
stringCollection = scripter.Script(urn)
For Each script As String In stringCollection
streamWriter.WriteLine(script)
Next

End Sub
End Module