question

mickyd avatar image
mickyd asked

Attach Large DB

Hi , need some advice , attaching a large database about 900 Gb in size. Had to move log from drive 1 to drive 2. Process is still running , is this expected , been about 40 mins so far ? Any way to detect progress ? Or should I just assume as it is running , and not displayed as failed it will work eventually.... Many thanks
attach
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image Blackhawk-17 commented ·
How large was the log? And what process did you use to prep? Which version of SQL Server?
0 Likes 0 ·
mickyd avatar image mickyd commented ·
Thanks everyone. Worked in the end. took about 1 hour 50 mins ???? I guess this is a issue that needs to be looked at. Could see progress , or activity in activity monitor and could see it was still running Cheers Everyone.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
You can try running a query against sys.dm_exec_requests. This will show you if the query is blocked or just processing. It may, may, show you the percentage complete too.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ozamora avatar image ozamora commented ·
Agree with Grant. There needs to be a blocking process.
0 Likes 0 ·
homebrew01 avatar image
homebrew01 answered
For me, attaching databases has always been a matter of a few seconds. What does Activity Monitor or sp_who2 show ?
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
I agree. Something was up. Attaching is largely just doing some writes to the system databases to point out where the new databases are located... unless... is there a rollback & recovery process when attaching? That could be problematic.
2 Likes 2 ·
Oleg avatar image Oleg commented ·
Even when, like in the question
attaching a large database about 900 Gb in size
?
0 Likes 0 ·
homebrew01 avatar image homebrew01 commented ·
I just now detached a 1.1 Terabyte database. Attached it again in about 5 seconds.
0 Likes 0 ·
ozamora avatar image ozamora commented ·
Agreed. That process is instantaneous.
0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
Detaching from one server and reattaching to the same can't really take that long. But if there's another patch level on the target server than the source server, that would mean some work attaching it.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.