November 8, 2007 at 11:53 pm
Hi, everyone.
One of our clients is experiencing problems with backing up their database. System details: WinXP SP2, MSDE2000 SP3, Database size ~1 GB.
The problem started when they did an automated upgrade of their production database. The application that does the backup (before running the upgrade script) just hangs indefinitely. Here is the backup command used:
BACKUP DATABASE mastermed
TO DISK='C:\Program Files\Mastermed\Upgrades\DBBackups\_UpgradeBackup'
This query is executed through OSQL, using a user-defined login linked to the database in question.
When they do a manual backup (through a GUI interface we supplied), the same thing occurs. Here is 'manual' backup command:
BACKUP DATABASE mastermed
TO DISK = 'C:\Program Files\Mastermed\Upgrades\DBBackups\UpgradeBackup'
WITH FORMAT, NAME = 'mastermed'
This query is executed through an ADO connection using the same user-defined login.
Here is the error log:
2007-11-08 08:03:30.84 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)
2007-11-08 08:03:30.84 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-11-08 08:03:30.84 server All rights reserved.
2007-11-08 08:03:30.84 server Server Process ID is 1308.
2007-11-08 08:03:30.84 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL$MASTERMED\LOG\ERRORLOG'.
2007-11-08 08:03:30.89 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2007-11-08 08:03:31.28 server SQL Server configured for thread mode processing.
2007-11-08 08:03:31.32 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2007-11-08 08:03:32.15 spid3 Starting up database 'master'.
2007-11-08 08:03:33.57 spid3 0 transactions rolled back in database 'master' (1).
2007-11-08 08:03:33.57 spid3 Recovery is checkpointing database 'master' (1)
2007-11-08 08:03:35.23 server Using 'SSNETLIB.DLL' version '8.0.766'.
2007-11-08 08:03:35.25 spid5 Starting up database 'model'.
2007-11-08 08:03:35.35 spid3 Server name is 'SHAMAIN\MASTERMED'.
2007-11-08 08:03:35.35 spid8 Starting up database 'msdb'.
2007-11-08 08:03:35.35 spid9 Starting up database 'Mastermed'.
2007-11-08 08:03:36.04 server SQL server listening on 10.0.0.1: 1213.
2007-11-08 08:03:36.04 server SQL server listening on 127.0.0.1: 1213.
2007-11-08 08:03:36.04 server SQL server listening on TCP, Shared Memory, Named Pipes.
2007-11-08 08:03:36.04 server SQL Server is ready for client connections
2007-11-08 08:03:37.90 spid5 Clearing tempdb database.
2007-11-08 08:03:46.34 spid5 Starting up database 'tempdb'.
2007-11-08 08:04:02.92 spid9 Analysis of database 'Mastermed' (5) is 2% complete (approximately 350 more seconds)
2007-11-08 08:04:08.89 spid9 Analysis of database 'Mastermed' (5) is 4% complete (approximately 368 more seconds)
2007-11-08 08:04:16.48 spid9 Analysis of database 'Mastermed' (5) is 5% complete (approximately 409 more seconds)
2007-11-08 08:04:22.15 spid9 Analysis of database 'Mastermed' (5) is 6% complete (approximately 413 more seconds)
2007-11-08 08:04:30.09 spid9 Analysis of database 'Mastermed' (5) is 7% complete (approximately 444 more seconds)
2007-11-08 08:04:41.54 spid9 Analysis of database 'Mastermed' (5) is 8% complete (approximately 506 more seconds)
2007-11-08 08:04:51.31 spid9 Analysis of database 'Mastermed' (5) is 9% complete (approximately 546 more seconds)
2007-11-08 08:04:56.62 spid9 Analysis of database 'Mastermed' (5) is 10% complete (approximately 543 more seconds)
2007-11-08 08:05:02.57 spid9 Analysis of database 'Mastermed' (5) is 11% complete (approximately 545 more seconds)
2007-11-08 08:05:08.40 spid9 Analysis of database 'Mastermed' (5) is 12% complete (approximately 546 more seconds)
2007-11-08 08:05:13.82 spid9 Analysis of database 'Mastermed' (5) is 13% complete (approximately 537 more seconds)
2007-11-08 08:05:19.07 spid9 Analysis of database 'Mastermed' (5) is 14% complete (approximately 525 more seconds)
2007-11-08 08:05:22.60 spid9 Analysis of database 'Mastermed' (5) is 15% complete (approximately 505 more seconds)
2007-11-08 08:05:28.23 spid9 Analysis of database 'Mastermed' (5) is 16% complete (approximately 497 more seconds)
2007-11-08 08:05:35.03 spid9 Analysis of database 'Mastermed' (5) is 17% complete (approximately 495 more seconds)
2007-11-08 08:05:37.06 spid9 Analysis of database 'Mastermed' (5) is 18% complete (approximately 472 more seconds)
2007-11-08 08:05:46.73 spid9 Analysis of database 'Mastermed' (5) is 19% complete (approximately 482 more seconds)
2007-11-08 08:05:57.06 spid9 Analysis of database 'Mastermed' (5) is 20% complete (approximately 494 more seconds)
2007-11-08 08:06:02.71 spid9 Analysis of database 'Mastermed' (5) is 21% complete (approximately 486 more seconds)
2007-11-08 08:06:06.90 spid9 Analysis of database 'Mastermed' (5) is 22% complete (approximately 473 more seconds)
2007-11-08 08:06:14.85 spid9 Analysis of database 'Mastermed' (5) is 23% complete (approximately 473 more seconds)
2007-11-08 08:06:20.20 spid9 Analysis of database 'Mastermed' (5) is 24% complete (approximately 464 more seconds)
2007-11-08 08:06:25.48 spid9 Analysis of database 'Mastermed' (5) is 25% complete (approximately 456 more seconds)
2007-11-08 08:06:28.68 spid9 Analysis of database 'Mastermed' (5) is 26% complete (approximately 441 more seconds)
2007-11-08 08:06:32.14 spid9 Analysis of database 'Mastermed' (5) is 27% complete (approximately 428 more seconds)
2007-11-08 08:06:35.18 spid9 Analysis of database 'Mastermed' (5) is 28% complete (approximately 415 more seconds)
2007-11-08 08:06:38.21 spid9 Analysis of database 'Mastermed' (5) is 29% complete (approximately 403 more seconds)
2007-11-08 08:06:40.85 spid9 Analysis of database 'Mastermed' (5) is 30% complete (approximately 390 more seconds)
2007-11-08 08:06:41.17 spid9 Analysis of database 'Mastermed' (5) is 31% complete (approximately 373 more seconds)
2007-11-08 08:06:41.32 spid9 Analysis of database 'Mastermed' (5) is 32% complete (approximately 356 more seconds)
2007-11-08 08:06:41.71 spid9 Analysis of database 'Mastermed' (5) is 33% complete (approximately 341 more seconds)
2007-11-08 08:06:41.90 spid9 Analysis of database 'Mastermed' (5) is 34% complete (approximately 326 more seconds)
2007-11-08 08:06:42.28 spid9 Analysis of database 'Mastermed' (5) is 35% complete (approximately 313 more seconds)
2007-11-08 08:06:42.48 spid9 Analysis of database 'Mastermed' (5) is 36% complete (approximately 299 more seconds)
2007-11-08 08:06:42.67 spid9 Analysis of database 'Mastermed' (5) is 37% complete (approximately 286 more seconds)
2007-11-08 08:06:42.82 spid9 Analysis of database 'Mastermed' (5) is 38% complete (approximately 273 more seconds)
2007-11-08 08:06:43.09 spid9 Analysis of database 'Mastermed' (5) is 39% complete (approximately 261 more seconds)
2007-11-08 08:06:43.29 spid9 Analysis of database 'Mastermed' (5) is 40% complete (approximately 249 more seconds)
2007-11-08 08:06:43.51 spid9 Analysis of database 'Mastermed' (5) is 41% complete (approximately 237 more seconds)
2007-11-08 08:06:43.74 spid9 Analysis of database 'Mastermed' (5) is 42% complete (approximately 226 more seconds)
2007-11-08 08:06:44.28 spid9 Analysis of database 'Mastermed' (5) is 44% complete (approximately 215 more seconds)
2007-11-08 08:06:44.65 spid9 Analysis of database 'Mastermed' (5) is 45% complete (approximately 204 more seconds)
2007-11-08 08:06:44.90 spid9 Analysis of database 'Mastermed' (5) is 46% complete (approximately 194 more seconds)
2007-11-08 08:06:45.96 spid9 Analysis of database 'Mastermed' (5) is 48% complete (approximately 185 more seconds)
2007-11-08 08:06:46.21 spid9 Analysis of database 'Mastermed' (5) is 49% complete (approximately 175 more seconds)
2007-11-08 08:06:46.46 spid9 Analysis of database 'Mastermed' (5) is 51% complete (approximately 165 more seconds)
2007-11-08 08:06:46.73 spid9 Analysis of database 'Mastermed' (5) is 52% complete (approximately 156 more seconds)
2007-11-08 08:06:47.18 spid9 Analysis of database 'Mastermed' (5) is 54% complete (approximately 147 more seconds)
2007-11-08 08:06:47.65 spid9 Analysis of database 'Mastermed' (5) is 55% complete (approximately 138 more seconds)
2007-11-08 08:06:48.20 spid9 Analysis of database 'Mastermed' (5) is 57% complete (approximately 130 more seconds)
2007-11-08 08:06:48.79 spid9 Analysis of database 'Mastermed' (5) is 58% complete (approximately 122 more seconds)
2007-11-08 08:06:49.35 spid9 Analysis of database 'Mastermed' (5) is 60% complete (approximately 114 more seconds)
2007-11-08 08:06:49.98 spid9 Analysis of database 'Mastermed' (5) is 62% complete (approximately 106 more seconds)
2007-11-08 08:06:50.53 spid9 Analysis of database 'Mastermed' (5) is 64% complete (approximately 99 more seconds)
2007-11-08 08:06:51.06 spid9 Analysis of database 'Mastermed' (5) is 65% complete (approximately 92 more seconds)
2007-11-08 08:06:52.28 spid9 Analysis of database 'Mastermed' (5) is 67% complete (approximately 85 more seconds)
2007-11-08 08:06:53.42 spid9 Analysis of database 'Mastermed' (5) is 69% complete (approximately 78 more seconds)
2007-11-08 08:06:54.79 spid9 Analysis of database 'Mastermed' (5) is 71% complete (approximately 72 more seconds)
2007-11-08 08:06:55.59 spid9 Analysis of database 'Mastermed' (5) is 73% complete (approximately 65 more seconds)
2007-11-08 08:06:56.00 spid9 Analysis of database 'Mastermed' (5) is 75% complete (approximately 58 more seconds)
2007-11-08 08:06:56.56 spid9 Analysis of database 'Mastermed' (5) is 77% complete (approximately 52 more seconds)
2007-11-08 08:06:56.93 spid9 Analysis of database 'Mastermed' (5) is 79% complete (approximately 46 more seconds)
2007-11-08 08:06:57.29 spid9 Analysis of database 'Mastermed' (5) is 81% complete (approximately 40 more seconds)
2007-11-08 08:06:57.67 spid9 Analysis of database 'Mastermed' (5) is 84% complete (approximately 34 more seconds)
2007-11-08 08:06:58.03 spid9 Analysis of database 'Mastermed' (5) is 86% complete (approximately 28 more seconds)
2007-11-08 08:06:58.56 spid9 Analysis of database 'Mastermed' (5) is 88% complete (approximately 23 more seconds)
2007-11-08 08:06:59.14 spid9 Analysis of database 'Mastermed' (5) is 91% complete (approximately 17 more seconds)
2007-11-08 08:06:59.75 spid9 Analysis of database 'Mastermed' (5) is 93% complete (approximately 12 more seconds)
2007-11-08 08:07:00.46 spid9 Analysis of database 'Mastermed' (5) is 96% complete (approximately 7 more seconds)
2007-11-08 08:07:02.18 spid9 Analysis of database 'Mastermed' (5) is 98% complete (approximately 2 more seconds)
2007-11-08 08:07:02.73 spid9 Analysis of database 'Mastermed' (5) is 100% complete (approximately 0 more seconds)
2007-11-08 08:07:02.74 spid9 Recovery of database 'Mastermed' (5) is 0% complete (approximately 1590 more seconds) (Phase 2 of 3).
2007-11-08 08:07:18.40 spid9 Recovery of database 'Mastermed' (5) is 1% complete (approximately 1548 more seconds) (Phase 2 of 3).
2007-11-08 08:07:33.43 spid9 Recovery of database 'Mastermed' (5) is 2% complete (approximately 1502 more seconds) (Phase 2 of 3).
2007-11-08 08:07:48.48 spid9 Recovery of database 'Mastermed' (5) is 3% complete (approximately 1477 more seconds) (Phase 2 of 3).
2007-11-08 08:08:04.01 spid9 Recovery of database 'Mastermed' (5) is 4% complete (approximately 1469 more seconds) (Phase 2 of 3).
2007-11-08 08:08:19.85 spid9 Recovery of database 'Mastermed' (5) is 5% complete (approximately 1462 more seconds) (Phase 2 of 3).
2007-11-08 08:09:20.89 spid9 Recovery of database 'Mastermed' (5) is 8% complete (approximately 1494 more seconds) (Phase 2 of 3).
2007-11-08 08:10:02.84 spid9 Recovery of database 'Mastermed' (5) is 10% complete (approximately 1512 more seconds) (Phase 2 of 3).
2007-11-08 08:10:33.43 spid9 Recovery of database 'Mastermed' (5) is 12% complete (approximately 1521 more seconds) (Phase 2 of 3).
2007-11-08 08:10:53.20 spid9 Recovery of database 'Mastermed' (5) is 13% complete (approximately 1524 more seconds) (Phase 2 of 3).
2007-11-08 08:11:24.34 spid9 Recovery of database 'Mastermed' (5) is 14% complete (approximately 1532 more seconds) (Phase 2 of 3).
2007-11-08 08:12:58.21 spid9 Recovery of database 'Mastermed' (5) is 18% complete (approximately 1569 more seconds) (Phase 2 of 3).
2007-11-08 08:13:13.79 spid9 Recovery of database 'Mastermed' (5) is 19% complete (approximately 1569 more seconds) (Phase 2 of 3).
2007-11-08 08:13:32.57 spid9 Recovery of database 'Mastermed' (5) is 20% complete (approximately 1557 more seconds) (Phase 2 of 3).
2007-11-08 08:13:53.06 spid9 Recovery of database 'Mastermed' (5) is 20% complete (approximately 1550 more seconds) (Phase 2 of 3).
2007-11-08 08:14:02.42 spid9 Recovery of database 'Mastermed' (5) is 21% complete (approximately 1547 more seconds) (Phase 2 of 3).
2007-11-08 08:14:17.71 spid9 Recovery of database 'Mastermed' (5) is 22% complete (approximately 1541 more seconds) (Phase 2 of 3).
2007-11-08 08:14:37.71 spid9 Recovery of database 'Mastermed' (5) is 22% complete (approximately 1529 more seconds) (Phase 2 of 3).
2007-11-08 08:14:44.96 spid9 Recovery of database 'Mastermed' (5) is 23% complete (approximately 1525 more seconds) (Phase 2 of 3).
2007-11-08 08:14:58.81 spid9 Recovery of database 'Mastermed' (5) is 24% complete (approximately 1506 more seconds) (Phase 2 of 3).
2007-11-08 08:15:18.92 spid9 Recovery of database 'Mastermed' (5) is 24% complete (approximately 1497 more seconds) (Phase 2 of 3).
2007-11-08 08:15:30.78 spid9 Recovery of database 'Mastermed' (5) is 25% complete (approximately 1493 more seconds) (Phase 2 of 3).
2007-11-08 08:15:45.64 spid9 Recovery of database 'Mastermed' (5) is 26% complete (approximately 1487 more seconds) (Phase 2 of 3).
2007-11-08 08:16:05.71 spid9 Recovery of database 'Mastermed' (5) is 26% complete (approximately 1479 more seconds) (Phase 2 of 3).
2007-11-08 08:16:15.54 spid9 Recovery of database 'Mastermed' (5) is 27% complete (approximately 1475 more seconds) (Phase 2 of 3).
2007-11-08 08:16:33.82 spid9 Recovery of database 'Mastermed' (5) is 28% complete (approximately 1467 more seconds) (Phase 2 of 3).
2007-11-08 08:16:53.96 spid9 Recovery of database 'Mastermed' (5) is 28% complete (approximately 1458 more seconds) (Phase 2 of 3).
2007-11-08 08:17:06.31 spid9 Recovery of database 'Mastermed' (5) is 29% complete (approximately 1454 more seconds) (Phase 2 of 3).
2007-11-08 08:17:24.95 spid9 Recovery of database 'Mastermed' (5) is 30% complete (approximately 1450 more seconds) (Phase 2 of 3).
2007-11-08 08:17:48.32 spid9 Recovery of database 'Mastermed' (5) is 30% complete (approximately 1446 more seconds) (Phase 2 of 3).
2007-11-08 08:18:09.00 spid9 Recovery of database 'Mastermed' (5) is 31% complete (approximately 1442 more seconds) (Phase 2 of 3).
2007-11-08 08:18:27.62 spid9 Recovery of database 'Mastermed' (5) is 32% complete (approximately 1438 more seconds) (Phase 2 of 3).
2007-11-08 08:18:47.75 spid9 Recovery of database 'Mastermed' (5) is 32% complete (approximately 1431 more seconds) (Phase 2 of 3).
2007-11-08 08:19:01.50 spid9 Recovery of database 'Mastermed' (5) is 33% complete (approximately 1427 more seconds) (Phase 2 of 3).
2007-11-08 08:19:15.51 spid9 Recovery of database 'Mastermed' (5) is 34% complete (approximately 1421 more seconds) (Phase 2 of 3).
2007-11-08 08:19:35.51 spid9 Recovery of database 'Mastermed' (5) is 34% complete (approximately 1413 more seconds) (Phase 2 of 3).
2007-11-08 08:19:47.84 spid9 Recovery of database 'Mastermed' (5) is 35% complete (approximately 1408 more seconds) (Phase 2 of 3).
2007-11-08 08:20:04.34 spid9 Recovery of database 'Mastermed' (5) is 35% complete (approximately 1402 more seconds) (Phase 2 of 3).
2007-11-08 08:20:04.56 spid9 13812 transactions rolled forward in database 'Mastermed' (5).
2007-11-08 08:20:04.90 spid9 Recovery of database 'Mastermed' (5) is 35% complete (approximately 1402 more seconds) (Phase 3 of 3).
2007-11-08 08:20:15.73 spid9 Recovery of database 'Mastermed' (5) is 20% complete (approximately 3027 more seconds) (Phase 3 of 3).
2007-11-08 08:20:20.09 spid9 Recovery of database 'Mastermed' (5) is 21% complete (approximately 2901 more seconds) (Phase 3 of 3).
2007-11-08 08:20:36.73 spid9 Recovery of database 'Mastermed' (5) is 20% complete (approximately 3067 more seconds) (Phase 3 of 3).
2007-11-08 08:20:56.75 spid9 Recovery of database 'Mastermed' (5) is 20% complete (approximately 3288 more seconds) (Phase 3 of 3).
2007-11-08 08:21:16.87 spid9 Recovery of database 'Mastermed' (5) is 20% complete (approximately 3374 more seconds) (Phase 3 of 3).
2007-11-08 08:21:30.45 spid9 Recovery of database 'Mastermed' (5) is 19% complete (approximately 3474 more seconds) (Phase 3 of 3).
2007-11-08 08:21:35.64 spid9 Recovery of database 'Mastermed' (5) is 20% complete (approximately 3475 more seconds) (Phase 3 of 3).
2007-11-08 08:21:55.68 spid9 Recovery of database 'Mastermed' (5) is 20% complete (approximately 3411 more seconds) (Phase 3 of 3).
2007-11-08 08:22:01.57 spid9 Recovery of database 'Mastermed' (5) is 21% complete (approximately 3384 more seconds) (Phase 3 of 3).
2007-11-08 08:22:21.59 spid9 Recovery of database 'Mastermed' (5) is 21% complete (approximately 3282 more seconds) (Phase 3 of 3).
2007-11-08 08:22:25.53 spid9 Recovery of database 'Mastermed' (5) is 22% complete (approximately 3253 more seconds) (Phase 3 of 3).
2007-11-08 08:22:45.54 spid9 Recovery of database 'Mastermed' (5) is 22% complete (approximately 3174 more seconds) (Phase 3 of 3).
2007-11-08 08:22:50.46 spid9 Recovery of database 'Mastermed' (5) is 23% complete (approximately 3169 more seconds) (Phase 3 of 3).
2007-11-08 08:23:10.56 spid9 Recovery of database 'Mastermed' (5) is 23% complete (approximately 3165 more seconds) (Phase 3 of 3).
2007-11-08 08:23:23.37 spid9 Recovery of database 'Mastermed' (5) is 100% complete (approximately 0 more seconds) (Phase 3 of 3).
2007-11-08 08:23:23.42 spid9 1 transactions rolled back in database 'Mastermed' (5).
2007-11-08 08:23:23.62 spid9 Recovery is checkpointing database 'Mastermed' (5)
2007-11-08 08:24:06.74 spid3 Recovery complete.
2007-11-08 08:24:06.74 spid3 SQL global counter collection task is created.
2007-11-08 08:24:22.95 spid51 Using 'xpsqlbot.dll' version '2000.80.760' to execute extended stored procedure 'xp_qv'.
2007-11-08 09:00:37.26 backup Database backed up: Database: Mastermed, creation date(time): 2006/06/05(09:41:25), pages dumped: 229262, first LSN: 24816:8472:1, last LSN: 24816:8501:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Mastermed\Upgrades\DBBackups\_UpgradeBackup'}).
2007-11-08 14:09:59.26 spid10 This SQL Server has been optimized for 8 concurrent queries. This limit has been exceeded by 1 queries and performance may be adversely affected.
2007-11-08 14:12:22.46 backup BACKUP failed to complete the command BACKUP DATABASE Mastermed TO DISK='C:\Program Files\Mastermed\Upgrades\DBBackups\UpgradeBackup'
2007-11-08 14:25:46.03 backup BACKUP failed to complete the command BACKUP DATABASE Mastermed TO DISK='C:\Program Files\Mastermed\Upgrades\DBBackups\UpgradeBackup'
2007-11-08 14:26:02.54 server SQL Server terminating because of system shutdown.
I'd like to know what are possible causes for the database to be in a state to be recovered to this extent. Is this contributing to the backup failures? The other error logs show that the database is being recovered on nearly every startup of the server.
November 16, 2007 at 12:38 pm
I have no idea, but I'd call PSS. Something is wrong here and you don't want to be running without the ability to backup or recover.
It's worth the $250.
November 18, 2007 at 11:04 pm
Thanks for the reply, Steve.
This remains unsolved for the moment.
November 19, 2007 at 8:29 am
Please let us know if you find anything.
Has the server been rebooted?
The server does get recovered every time and it's possible if there were lots of changes that needed to be rolled forward or back, that you'd get messages like this. However typically recovery happens quickly. There shouldn't be too many transactions that aren't complete when you shut down.
November 20, 2007 at 6:21 am
The most common cause I have seen of a backup not completing but not giving errors is lack of disk space for the backup. This should give you a clear error message, but I have been at the wrong end of a full disk and a failed backup with no error message more than once. Another possible cause is your server is so busy the backup just takes a very long time to complete.
Your SQL Error Log messages are likely to be due to transactions still being running when the server is rebooted. It is best practice to close SQL Server manually before booting the server, to allow time for active transactions to either finish or be rolled back.
If you are sure none of the above apply then I agree with Steve - contact PSS as there could be something more serious wrong. Even if they find an easy answer, think of the $250 as money invested in your training.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply