Deadlock when to restore database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Deadlock when to restore database

Author

Topic

cwang
Starting Member

5 Posts

Posted - 2009-04-23 : 14:11:15

I got deadlock issue when I tried to restore the database:

Here is the command:

RESTORE DATABASE [trackit65] FROM DISK = N'C:\test\trackit65_it_cw.bak' WITH FILE = 1, MOVE N'TRACKIT65_IT' TO
N'C:\SQLData\TRACKIT65_IT.mdf', MOVE N'TRACKIT65_IT_log' TO N'C:\SQLData\TRACKIT65_IT_log.LDF', NOUNLOAD, REPLACE, STATS = 10

And here is the message I got:
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 45992 pages for database 'trackit65', file 'TRACKIT65_IT' on file 1.
Processed 118 pages for database 'trackit65', file 'TRACKIT65_IT_log' on file 1.
Msg 1205, Level 13, State 56, Line 1
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Converting database 'trackit65' from version 539 to the current version 611.
Database 'trackit65' running the upgrade step from version 539 to version 551.
Database 'trackit65' running the upgrade step from version 551 to version 552.
Database 'trackit65' running the upgrade step from version 552 to version 553.
Database 'trackit65' running the upgrade step from version 553 to version 554.
Database 'trackit65' running the upgrade step from version 554 to version 589.
Database 'trackit65' running the upgrade step from version 589 to version 590.
Database 'trackit65' running the upgrade step from version 590 to version 593.
Database 'trackit65' running the upgrade step from version 593 to version 597.
Database 'trackit65' running the upgrade step from version 597 to version 604.
Database 'trackit65' running the upgrade step from version 604 to version 605.
Database 'trackit65' running the upgrade step from version 605 to version 606.
Database 'trackit65' running the upgrade step from version 606 to version 607.
Database 'trackit65' running the upgrade step from version 607 to version 608.
Database 'trackit65' running the upgrade step from version 608 to version 609.
Database 'trackit65' running the upgrade step from version 609 to version 610.
Database 'trackit65' running the upgrade step from version 610 to version 611.
RESTORE DATABASE successfully processed 46110 pages in 30.672 seconds (12.315 MB/sec).

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-23 : 14:43:01

Can you access the database? If so, I don't think you have to worry, it looks like part of the upgrade process got blocked. If you want you can run DBCC CHECKDB and see if it reports any errors.

cwang
Starting Member

5 Posts

Posted - 2009-04-23 : 14:49:50

DBCC CheckDB result is good. But I want to know why that error happened. I tried 10 times and every time that error happened. There are no other users connected to the server at all.

I ran the sql profile to trace but it didn't tell me anything...

I tried to restore other databases, they are all good.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-23 : 14:57:18

Can you make another backup of the original database and restore that?

cwang
Starting Member

5 Posts

Posted - 2009-04-23 : 14:59:39

I tried 4 times already but still the same result. I even did the dbcc checkdb on the original one before I did the backup.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 15:54:41

Is it also SPID51 that does the restore?
Which database is SPID attached to?

Change to MASTER database and run the RESTORE command again.

E 12°55'05.63"
N 56°04'39.26"

cwang
Starting Member

5 Posts

Posted - 2009-04-23 : 16:16:43

still the same...I turn on the trace and here is more detail about the restore process:

RestoreDatabase: Database trackit65

Opening backup set

Restore: Configuration section loaded

Restore: Backup set is open

Restore: Planning begins

Dismounting FullText catalogs

X-locking database: trackit65

Restore: Planning complete

Restore: BeginRestore (offline) on trackit65

Restore: PreparingContainers

Restore: Containers are ready

Restore: Restoring backup set

Restore: Transferring data to trackit65

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
FileHandleCache: 0 files opened. CacheSize: 10

Restore: Data transfer complete on trackit65

Processed 45992 pages for database 'trackit65', file 'TRACKIT65_IT' on file 1.
Processed 118 pages for database 'trackit65', file 'TRACKIT65_IT_log' on file 1.
Restore: Backup set restored

Restore-Redo begins on database trackit65

Rollforward complete on database trackit65

Restore: Done with fixups

Restore: Transitioning database to ONLINE

Restore: Restarting database for ONLINE

Msg 1205, Level 13, State 56, Line 2
Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Converting database 'trackit65' from version 539 to the current version 611.
Database 'trackit65' running the upgrade step from version 539 to version 551.
Database 'trackit65' running the upgrade step from version 551 to version 552.
Database 'trackit65' running the upgrade step from version 552 to version 553.
Database 'trackit65' running the upgrade step from version 553 to version 554.
Database 'trackit65' running the upgrade step from version 554 to version 589.
Database 'trackit65' running the upgrade step from version 589 to version 590.
Database 'trackit65' running the upgrade step from version 590 to version 593.
Database 'trackit65' running the upgrade step from version 593 to version 597.
Database 'trackit65' running the upgrade step from version 597 to version 604.
Database 'trackit65' running the upgrade step from version 604 to version 605.
Database 'trackit65' running the upgrade step from version 605 to version 606.
Database 'trackit65' running the upgrade step from version 606 to version 607.
Database 'trackit65' running the upgrade step from version 607 to version 608.
Database 'trackit65' running the upgrade step from version 608 to version 609.
Database 'trackit65' running the upgrade step from version 609 to version 610.
Database 'trackit65' running the upgrade step from version 610 to version 611.
Restore: Database is restarted

Resuming any halted fulltext crawls

Restore: Writing history records

Writing backup history records

Restore: Done with MSDB maintenance

RESTORE DATABASE successfully processed 46110 pages in 29.545 seconds (12.784 MB/sec).
RestoreDatabase: Finished

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 16:22:09

Try this

USE [Master]
GO

RESTORE DATABASE [trackit65] FROM DISK = N'C:\test\trackit65_it_cw.bak' WITH FILE = 1, MOVE N'TRACKIT65_IT' TO 
N'C:\SQLData\TRACKIT65_IT.mdf', MOVE N'TRACKIT65_IT_log' TO N'C:\SQLData\TRACKIT65_IT_log.LDF', NOUNLOAD, REPLACE, STATS = 10
GO

E 12°55'05.63"
N 56°04'39.26"

cwang
Starting Member

5 Posts

Posted - 2009-04-24 : 11:15:05

thanks Peso but same thing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources