RESTORE LOG long run - SQL Server Forums

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 2008 Forums

High Availability (2008)

RESTORE LOG long run

Author

Topic

Altair83
Starting Member

1 Post

Posted - 2011-12-08 : 09:16:06

Hello ALL,

We work with a rather large (1,7 TB) MS SQL 2008 / SAP BI database.

The database consists of 16 database data files (1x .mdf and 15x .ndf)
and also one transaction log file (.ldf). Each database data file is
approximately 100 GB large.

I executed the BACKUP of the database running it in 32 steps as
described next.

1. BACKUP DATABASE [XYZ] FILE = 'XYZ_01' TO DISK = 'R:\bak_XYZ_01_.bak'
WITH STATS = 1

2. BACKUP LOG [XYZ] TO DISK = 'R:\bak_trlog_01_.bak' WITH STATS = 1

3. BACKUP DATABASE [XYZ] FILE = 'XYZ_02' TO DISK = 'R:\bak_XYZ_02_.bak'
WITH STATS = 1

4. BACKUP LOG [XYZ] TO DISK = 'R:\bak_trlog_02_.bak' WITH STATS = 1

. . .

31. BACKUP DATABASE [XYZ] FILE = 'XYZ_16' TO DISK = 'R:\bak_XYZ_16_.bak'
WITH STATS = 1

32. BACKUP LOG [XYZ] TO DISK = 'R:\bak_trlog_16_.bak' WITH STATS = 1

OK, now I am creating the new database XYZ_C on the other MS SQL 2008 server
executing the following procedure:

1. RESTORE DATABASE [XYZ_C] FILE = 'XYZ_01' FROM DISK = 'R:\bak_XYZ_01_.bak'
WITH STATS = 1, NORECOVERY

2. RESTORE DATABASE [XYZ_C] FILE = 'XYZ_02' FROM DISK = 'R:\bak_XYZ_02_.bak'
WITH STATS = 1, NORECOVERY

. . .

16. RESTORE DATABASE [XYZ_C] FILE = 'XYZ_16' FROM DISK = 'R:\bak_XYZ_16_.bak'
WITH STATS = 1, NORECOVERY

17. RESTORE LOG [XYZ_C] FROM DISK = 'R:\bak_trlog_01_.bak' WITH STATS = 1, NORECOVERY

18. RESTORE LOG [XYZ_C] FROM DISK = 'R:\bak_trlog_02_.bak' WITH STATS = 1, NORECOVERY

. . .

32. RESTORE LOG [XYZ_C] FROM DISK = 'R:\bak_trlog_16_.bak' WITH STATS = 1, NORECOVERY

It is working excellent, but I still have one issue and a question.

As we use the STATS hint in the RESTORE LOG commands, we receive
the messages, showing us the progress of the restore command.

The messages look like in the following list:

Changed language setting to us_english.
Changed database context to 'master'.
20 percent processed.
40 percent processed.
60 percent processed.
81 percent processed.
100 percent processed.
Processed 0 pages for database 'XYZ_C', file 'XYZ_01' on file 1.
Processed 0 pages for database 'XYZ_C', file 'XYZ_02' on file 1.
Processed 0 pages for database 'XYZ_C', file 'XYZ_03' on file 1.
Processed 0 pages for database 'XYZ_C', file 'XYZ_04' on file 1.
Processed 0 pages for database 'XYZ_C', file 'XYZ_05' on file 1.
Processed 0 pages for database 'XYZ_C', file 'XYZ_06' on file 1.
Processed 0 pages for database 'XYZ_C', file 'XYZ_07' on file 1.
Processed 0 pages for database 'XYZ_C', file 'XYZ_08' on file 1.
Processed 0 pages for database 'XYZ_C', file 'XYZ_09' on file 1.
Processed 0 pages for database 'XYZ_C', file 'XYZ_10' on file 1.
Processed 0 pages for database 'XYZ_C', file 'XYZ_11' on file 1.
Processed 0 pages for database 'XYZ_C', file 'XYZ_12' on file 1.
Processed 0 pages for database 'XYZ_C', file 'XYZ_13' on file 1.
Processed 0 pages for database 'XYZ_C', file 'XYZ_14' on file 1.
Processed 0 pages for database 'XYZ_C', file 'XYZ_15' on file 1.
Processed 0 pages for database 'XYZ_C', file 'XYZ_16' on file 1.
Processed 632 pages for database 'XYZ_C', file 'XYZLOG1' on file 1.
RESTORE LOG successfully processed 632 pages in 0.154 seconds (32.023 MB/sec).

I would like to understand, why we sometime have to wait for a long
time for the very last message - in the shown list the message:

RESTORE LOG successfully processed 632 pages in 0.154 seconds (32.023 MB/sec).

What is happening with the database / what is the job, executed
by the server instance in the time between the second last message

Processed 632 pages for database 'XYZ_C', file 'XYZLOG1' on file 1.

and the very last message

RESTORE LOG successfully processed 632 pages in 0.154 seconds (32.023 MB/sec).

Regards

Altair83

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-08 : 12:31:50

Could be due to high number of VLFs. How many rows are returned by DBCC LOGINFO()?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-09 : 08:02:28

How long is a long time and how often is sometimes?

Thanks for the post though - it's a test I've been meaning to run for a long time to see what happened.

Can't think of much. Checking that things are ok, recording state, flushing memory.
I don't think it does a roll back or roll forward but maybe it has to do something to get it into a state for the next log?
Maybe waiting for the updates to be written to disk - a checkpoint maybe, might have to wait until a checkpoint can be taken for this database?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources