sp_attachdb time varies

[Ray]

Every night we use sp_attachdb to attach a 1TB database to our data
warehouse server. Each night we kill off all nonsystem related spids, set
all the other databases to DBO only and then attach. The attach seems to
take between 4 minutes to an hour. With an idle server and filegroup sizes
that have not grown what could be causing the variance? The number of
transactions to rollback or forward doesn't seem to have an impact. The log
indicates that the attach is spending almost all its time in phase 2.
Anyone know exactly what happens in phase 2?

Books online and reading the sp_attachdb proc weren't much help.
sp_attachdb is simply a derived call to create database ... for attach.

Any insight is appreciated.

Thanks,
Ray

[Erland Sommarskog]

Ray (so*****@nowhere.com) writes:

Every night we use sp_attachdb to attach a 1TB database to our data
warehouse server. Each night we kill off all nonsystem related spids,
set all the other databases to DBO only and then attach. The attach
seems to take between 4 minutes to an hour. With an idle server and
filegroup sizes that have not grown what could be causing the variance?
The number of transactions to rollback or forward doesn't seem to have
an impact. The log indicates that the attach is spending almost all its
time in phase 2. Anyone know exactly what happens in phase 2?

Books online and reading the sp_attachdb proc weren't much help.
sp_attachdb is simply a derived call to create database ... for attach.

I gladly admit that I don't have very many ideas, so I forwarded the
question to our internal MVP forum where people from Microsoft also
attend.

What does your @@version say?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ray]

We are currently at build 997 but the issue existed under 789 as well.

Thanks,
Ray

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Ray (so*****@nowhere.com) writes:

Every night we use sp_attachdb to attach a 1TB database to our data
warehouse server. Each night we kill off all nonsystem related spids,
set all the other databases to DBO only and then attach. The attach
seems to take between 4 minutes to an hour. With an idle server and
filegroup sizes that have not grown what could be causing the variance?
The number of transactions to rollback or forward doesn't seem to have
an impact. The log indicates that the attach is spending almost all its
time in phase 2. Anyone know exactly what happens in phase 2?

Books online and reading the sp_attachdb proc weren't much help.
sp_attachdb is simply a derived call to create database ... for attach.

I gladly admit that I don't have very many ideas, so I forwarded the
question to our internal MVP forum where people from Microsoft also
attend.

What does your @@version say?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Ray (so*****@nowhere.com) writes:

We are currently at build 997 but the issue existed under 789 as well.

Thanks! Then I know, if someone asks me!

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

"Ray" <so*****@nowhere.com> writes:

Every night we use sp_attachdb to attach a 1TB database to our data
warehouse server. Each night we kill off all nonsystem related spids, set
all the other databases to DBO only and then attach. The attach seems to
take between 4 minutes to an hour. With an idle server and filegroup sizes
that have not grown what could be causing the variance? The number of
transactions to rollback or forward doesn't seem to have an impact. The log
indicates that the attach is spending almost all its time in phase 2.
Anyone know exactly what happens in phase 2?

Books online and reading the sp_attachdb proc weren't much help.
sp_attachdb is simply a derived call to create database ... for attach.

Any insight is appreciated.

I have now gotten an answer from of the SQL Server devs. He said:

Phase 2 is REDO recovery and its length is going to be largely driven by
how> far back in the log the last checkpoint was. If they did a proper
sp_detach_db or ALTER DATABASE SET OFFLINE there should be nothing to REDO.
If they can't do this for some reason, they should at least attempt an
explicit CHECKPOINT on the database before the files are copied/moved/split
for their attach.
Does that give you an idea of how you should approach the problem?
--
Erland Sommarskog, Stockholm, es****@sommarskog.se

[Ray]

Yes. That really narrows it down. The source system never really stops.
They just snap it via the SAN so there are usually a few transactions (about
5 on average) to rollback and 3 to 5 thousand to roll forward. I'll see
what their checkpoint interval is and if they can implement an explict
checkpoint before the hot split.

Thanks,
Ray
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:d1**********@green.tninet.se...

"Ray" <so*****@nowhere.com> writes:

Every night we use sp_attachdb to attach a 1TB database to our data
warehouse server. Each night we kill off all nonsystem related spids, set
all the other databases to DBO only and then attach. The attach seems to
take between 4 minutes to an hour. With an idle server and filegroup
sizes
that have not grown what could be causing the variance? The number of
transactions to rollback or forward doesn't seem to have an impact. The
log
indicates that the attach is spending almost all its time in phase 2.
Anyone know exactly what happens in phase 2?

Books online and reading the sp_attachdb proc weren't much help.
sp_attachdb is simply a derived call to create database ... for attach.

Any insight is appreciated.

I have now gotten an answer from of the SQL Server devs. He said:

Phase 2 is REDO recovery and its length is going to be largely driven by
how> far back in the log the last checkpoint was. If they did a proper
sp_detach_db or ALTER DATABASE SET OFFLINE there should be nothing to
REDO.
If they can't do this for some reason, they should at least attempt an
explicit CHECKPOINT on the database before the files are
copied/moved/split
for their attach.
Does that give you an idea of how you should approach the problem?
--
Erland Sommarskog, Stockholm, es****@sommarskog.se