detached db cant recover

A genius here at work decided to detach a database because the disk the log
file was on was full. He copied the log to a different drive and now we
cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
looks like it does the analysis to reattach in about 5 minutes then starts
what appears to be a 60 hour recovery cycle.
Is there anything I can do to speed this up? How can I truncate a detached
log?
Any ideas?
PLEASE HELP!
Richard
Hi Richard
Was the detach successful, as far as you know, i.e. no error messages
reported?
If so, you can try attaching without the log and have SQL Server build a new
one. Just change the name of the old log file so SQL Server can't find it,
and point to the primary file for the attach.
HTH
Kalen Delaney, SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log?
> Any ideas?
> PLEASE HELP!
> Richard
>
|||Ok That seems to be working but it is PAINFULLY slow.
Now I am getting these messages in the log. What the heck is going on?
SQL Server has encountered 13087 occurrence(s) of IO requests taking longer
than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
database [envision] (7). The OS file handle is 0x0000055C. The offset of
the latest long IO is: 0x00000c83a76000
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in database
[envision] (7). The OS file handle is 0x0000055C. The offset of the latest
long IO is: 0x00000c83a76000
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log?
> Any ideas?
> PLEASE HELP!
> Richard
>
|||Take a look into the below KB article.
http://support.microsoft.com/default...b;en-us;897284
Thanks
Hari
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:%23lv8SaQ3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> Ok That seems to be working but it is PAINFULLY slow.
> Now I am getting these messages in the log. What the heck is going on?
> SQL Server has encountered 13087 occurrence(s) of IO requests taking
> longer
> than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database [envision] (7). The OS file handle is 0x0000055C. The offset of
> the latest long IO is: 0x00000c83a76000
>
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
> than
> 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database
> [envision] (7). The OS file handle is 0x0000055C. The offset of the
> latest
> long IO is: 0x00000c83a76000
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>
|||It just keeps going dowbn hill. Now the problem is that during recovery it
crashes with a primary key violation at abuot 9% complete. I am starting to
panic a little. Any ideas on this new error?
Thanks as ALWAYS!
Richard
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log?
> Any ideas?
> PLEASE HELP!
> Richard
>
|||richard
I had same kind of problem with small db when i was copying & attaching
files on different drive. transaction rollback happened.
( i dont know that time,the tlog was big)
Then I attached the original files as different dbname and took a
backup. Run checkpoint & backup before detaching db
Richard Douglass wrote:
> It just keeps going dowbn hill. Now the problem is that during recovery it
> crashes with a primary key violation at abuot 9% complete. I am starting to
> panic a little. Any ideas on this new error?
> Thanks as ALWAYS!
> Richard
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>

detached db cant recover

A genius here at work decided to detach a database because the disk the log
file was on was full. He copied the log to a different drive and now we
cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
looks like it does the analysis to reattach in about 5 minutes then starts
what appears to be a 60 hour recovery cycle.
Is there anything I can do to speed this up? How can I truncate a detached
log'
Any ideas'
PLEASE HELP!
RichardHi Richard
Was the detach successful, as far as you know, i.e. no error messages
reported?
If so, you can try attaching without the log and have SQL Server build a new
one. Just change the name of the old log file so SQL Server can't find it,
and point to the primary file for the attach.
HTH
Kalen Delaney, SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||Ok That seems to be working but it is PAINFULLY slow.
Now I am getting these messages in the log. What the heck is going on'
SQL Server has encountered 13087 occurrence(s) of IO requests taking longer
than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
database [envision] (7). The OS file handle is 0x0000055C. The offset
of
the latest long IO is: 0x00000c83a76000
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in data
base
[envision] (7). The OS file handle is 0x0000055C. The offset of the la
test
long IO is: 0x00000c83a76000
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||Take a look into the below KB article.
http://support.microsoft.com/defaul...kb;en-us;897284
Thanks
Hari
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:%23lv8SaQ3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> Ok That seems to be working but it is PAINFULLY slow.
> Now I am getting these messages in the log. What the heck is going on'
> SQL Server has encountered 13087 occurrence(s) of IO requests taking
> longer
> than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF]
in
> database [envision] (7). The OS file handle is 0x0000055C. The offse
t of
> the latest long IO is: 0x00000c83a76000
>
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
> than
> 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database
> [envision] (7). The OS file handle is 0x0000055C. The offset of the
> latest
> long IO is: 0x00000c83a76000
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>|||It just keeps going dowbn hill. Now the problem is that during recovery it
crashes with a primary key violation at abuot 9% complete. I am starting to
panic a little. Any ideas on this new error'
Thanks as ALWAYS!
Richard
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||richard
I had same kind of problem with small db when i was copying & attaching
files on different drive. transaction rollback happened.
( i dont know that time,the tlog was big)
Then I attached the original files as different dbname and took a
backup. Run checkpoint & backup before detaching db
Richard Douglass wrote:
> It just keeps going dowbn hill. Now the problem is that during recovery i
t
> crashes with a primary key violation at abuot 9% complete. I am starting
to
> panic a little. Any ideas on this new error'
> Thanks as ALWAYS!
> Richard
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>

detached db cant recover

A genius here at work decided to detach a database because the disk the log
file was on was full. He copied the log to a different drive and now we
cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
looks like it does the analysis to reattach in about 5 minutes then starts
what appears to be a 60 hour recovery cycle.
Is there anything I can do to speed this up? How can I truncate a detached
log'
Any ideas'
PLEASE HELP!
RichardHi Richard
Was the detach successful, as far as you know, i.e. no error messages
reported?
If so, you can try attaching without the log and have SQL Server build a new
one. Just change the name of the old log file so SQL Server can't find it,
and point to the primary file for the attach.
--
HTH
Kalen Delaney, SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||Ok That seems to be working but it is PAINFULLY slow.
Now I am getting these messages in the log. What the heck is going on'
SQL Server has encountered 13087 occurrence(s) of IO requests taking longer
than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
database [envision] (7). The OS file handle is 0x0000055C. The offset of
the latest long IO is: 0x00000c83a76000
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in database
[envision] (7). The OS file handle is 0x0000055C. The offset of the latest
long IO is: 0x00000c83a76000
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||Take a look into the below KB article.
http://support.microsoft.com/default.aspx?scid=kb;en-us;897284
Thanks
Hari
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:%23lv8SaQ3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> Ok That seems to be working but it is PAINFULLY slow.
> Now I am getting these messages in the log. What the heck is going on'
> SQL Server has encountered 13087 occurrence(s) of IO requests taking
> longer
> than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database [envision] (7). The OS file handle is 0x0000055C. The offset of
> the latest long IO is: 0x00000c83a76000
>
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
> than
> 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database
> [envision] (7). The OS file handle is 0x0000055C. The offset of the
> latest
> long IO is: 0x00000c83a76000
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>>A genius here at work decided to detach a database because the disk the
>>log file was on was full. He copied the log to a different drive and now
>>we cant reattach the db. The db is 260 gig, the log us 70 gig. SQL
>>Server looks like it does the analysis to reattach in about 5 minutes then
>>starts what appears to be a 60 hour recovery cycle.
>> Is there anything I can do to speed this up? How can I truncate a
>> detached log'
>> Any ideas'
>> PLEASE HELP!
>> Richard
>|||It just keeps going dowbn hill. Now the problem is that during recovery it
crashes with a primary key violation at abuot 9% complete. I am starting to
panic a little. Any ideas on this new error'
Thanks as ALWAYS!
Richard
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||richard
I had same kind of problem with small db when i was copying & attaching
files on different drive. transaction rollback happened.
( i dont know that time,the tlog was big)
Then I attached the original files as different dbname and took a
backup. Run checkpoint & backup before detaching db
Richard Douglass wrote:
> It just keeps going dowbn hill. Now the problem is that during recovery it
> crashes with a primary key violation at abuot 9% complete. I am starting to
> panic a little. Any ideas on this new error'
> Thanks as ALWAYS!
> Richard
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>> A genius here at work decided to detach a database because the disk the log
>> file was on was full. He copied the log to a different drive and now we
>> cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>> looks like it does the analysis to reattach in about 5 minutes then starts
>> what appears to be a 60 hour recovery cycle.
>> Is there anything I can do to speed this up? How can I truncate a
>> detached log'
>> Any ideas'
>> PLEASE HELP!
>> Richard
>

detach-copy-attach vs Copy Database Wizard

Instead of running the Copy Database Wizard, if I
1 detached the source database
2 copied the files from the source to the destination server
3 attached the files to the destination server
does it accomplish the same thing? The source will be SQL 7 and the
destination will be SQL 2000, so a database upgrade is involved.
I want to be able to get a copy of the detached .mdf and .ldf files copied
to the destination server that I can use to test the upgrade from SQL 7 to
2000
numerous times if needed. I don't want to use the wizard repeatedly, since
it
requires that the source db be in single user mode or have no users
connected to it.
Thanks,
johnYour method works. You might want to look into BACKUP and RESTORE as =another method to "move" databases. One benefit with this method: you =can use the Transact-SQL command 'BACKUP' to backup your database =without having to take it offline (as you do with detach_db). Another =method is that you can simply come along with your favorite backup =utility and simply backup a file (instead of trying to backup an open =database).
-- Keith
"john" <jgorman@.humanitees.com> wrote in message =news:%23e3gJhZmDHA.2444@.TK2MSFTNGP09.phx.gbl...
> Instead of running the Copy Database Wizard, if I
> > 1 detached the source database
> 2 copied the files from the source to the destination server
> 3 attached the files to the destination server
> > does it accomplish the same thing? The source will be SQL 7 and the
> destination will be SQL 2000, so a database upgrade is involved.
> > I want to be able to get a copy of the detached .mdf and .ldf files =copied
> to the destination server that I can use to test the upgrade from SQL =7 to
> 2000
> numerous times if needed. I don't want to use the wizard repeatedly, =since
> it
> requires that the source db be in single user mode or have no users
> connected to it.
> > Thanks,
> john
> >|||Will a restore of a SQL 7 database to a SQL 2000 database result in an
upgrade of that database to SQL 2000?
john
Keith Kratochvil <sqlguy.back2u@.comcast.net> wrote in message
news:ufNP2uZmDHA.3700@.TK2MSFTNGP11.phx.gbl...
Your method works. You might want to look into BACKUP and RESTORE as
another method to "move" databases. One benefit with this method: you can
use the Transact-SQL command 'BACKUP' to backup your database without having
to take it offline (as you do with detach_db). Another method is that you
can simply come along with your favorite backup utility and simply backup a
file (instead of trying to backup an open database).
--
Keith
"john" <jgorman@.humanitees.com> wrote in message
news:%23e3gJhZmDHA.2444@.TK2MSFTNGP09.phx.gbl...
> Instead of running the Copy Database Wizard, if I
> 1 detached the source database
> 2 copied the files from the source to the destination server
> 3 attached the files to the destination server
> does it accomplish the same thing? The source will be SQL 7 and the
> destination will be SQL 2000, so a database upgrade is involved.
> I want to be able to get a copy of the detached .mdf and .ldf files copied
> to the destination server that I can use to test the upgrade from SQL 7 to
> 2000
> numerous times if needed. I don't want to use the wizard repeatedly,
since
> it
> requires that the source db be in single user mode or have no users
> connected to it.
> Thanks,
> john
>|||Yes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"john" <jgorman@.humanitees.com> wrote in message
news:%23tOae2ZmDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Will a restore of a SQL 7 database to a SQL 2000 database result in an
> upgrade of that database to SQL 2000?
> john
>
> Keith Kratochvil <sqlguy.back2u@.comcast.net> wrote in message
> news:ufNP2uZmDHA.3700@.TK2MSFTNGP11.phx.gbl...
> Your method works. You might want to look into BACKUP and RESTORE as
> another method to "move" databases. One benefit with this method: you
can
> use the Transact-SQL command 'BACKUP' to backup your database without
having
> to take it offline (as you do with detach_db). Another method is that
you
> can simply come along with your favorite backup utility and simply
backup a
> file (instead of trying to backup an open database).
> --
> Keith
>
> "john" <jgorman@.humanitees.com> wrote in message
> news:%23e3gJhZmDHA.2444@.TK2MSFTNGP09.phx.gbl...
> > Instead of running the Copy Database Wizard, if I
> >
> > 1 detached the source database
> > 2 copied the files from the source to the destination server
> > 3 attached the files to the destination server
> >
> > does it accomplish the same thing? The source will be SQL 7 and the
> > destination will be SQL 2000, so a database upgrade is involved.
> >
> > I want to be able to get a copy of the detached .mdf and .ldf files
copied
> > to the destination server that I can use to test the upgrade from
SQL 7 to
> > 2000
> > numerous times if needed. I don't want to use the wizard
repeatedly,
> since
> > it
> > requires that the source db be in single user mode or have no users
> > connected to it.
> >
> > Thanks,
> > john
> >
> >
>