Re: TDP for MSSQL 5.2.1 performance issues and questions.

John,

This may not help you right now, but in case you're wanting to throw SQL
Server out the Window because of this "feature," I thought I'd mention that
this will change in SQL Server 2005.  As others have mentioned, SQL Server
2000 (and prior) must initialize all of the database pages before proceeding
with a restore.  This is because the restore only writes out pages that are
in use, so you can't rely on the restore to initialize all the pages for
you.

As I understand it, in SQL Server 2005, SQL Server still must allocate the
file space before doing a database restore, but the time-consuming
initialization of database pages is no longer required.  This should
considerably alleviate, or perhaps even practically eliminate, the problem
you are experiencing.

Like I said, although this might not help you now, I thought it might be
good to know as you look down the road.

Cheers,
Chris
_________________________________

Chris Leonard
MCSE, MCDBA, OCP, CIW
_________________________________

The Database Guy
http://www.databaseguy.com

Brainbench MVP for Oracle Admin
http://www.brainbench.com
_________________________________



| Date:    Wed, 30 Jun 2004 16:07:51 -0400
| From:    Del Hoobler <hoobler AT US.IBM DOT COM>
| Subject: Re: TDP for MSSQL 5.2.1 performance issues and questions.
|
| John,
|
| This is normal.
| SQL Server is pre-formatting the files in preparation for the restore.
| Data Protection for SQL restores a small bit of information that
| the SQL Server requires to properly set up and preformat the files.
| After that, Data Protection for SQL must wait around until the
| SQL Server starts requesting more data to fill the files.
| There is a README entry and a User's Guide note about this:
|
|   We recommend that the COMMTIMEOUT value on the TSM Server be
|   set to at least 600 seconds. The SQL Server must preformat
| the database
|   files prior to restoring the database in those files. This occurs
|   after a small amount of data has already been restored from the
|   TSM Server. When restoring very large databases, it may be necessary
|   to set COMMTIMEOUT to even larger values than 600. Failure to do
|   so may result in restores that fail due to a severed TCP/IP
| connection
|   caused by the TSM Server thinking that DP for SQL is hung.
|
| Thanks,
|
| Del
smime.p7s
Description: S/MIME cryptographic signature