469,625 Members | 1,079 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

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
Jul 23 '05 #1
5 2787
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
Jul 23 '05 #2
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

Jul 23 '05 #3
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
Jul 23 '05 #4
"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

Jul 23 '05 #5
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

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by vee_kay | last post: by
8 posts views Thread by peterbe | last post: by
4 posts views Thread by Andy Leszczynski | last post: by
5 posts views Thread by Erich Schreiber | last post: by
17 posts views Thread by Eric Lindsay | last post: by
4 posts views Thread by Wanhua Yi | last post: by
1 post views Thread by Drew | last post: by
2 posts views Thread by Al | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.