|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Re-attaching database
A database was generating some problems because of disk place, so I detached
it and move the files to another location. However when trying to re-attached the database, I receive an error message: Error 3624 : 1450 Transactions rolled forward in Database <xxx> Location : filemgr:cpp:2005 the "Attaching database has failed" messag. The lost transactions are of less importance, I just need to be able to re-attach the database. How can I go about it. Thanks You might consider using sp_attach_single_file_db which will allow you
attach the MDF file without the LDF. Because of the disk space issues, you probably have a lot of uncommitted transactions in the log file. You will probably lose these transactions if you use this method, but it might allow you to get that database connected again. -Eric Isaacs Eric,
But there is something strange going on here. Topokin stated the database was detached. Detach performs a checkpoint before the file is .... detached. This means that there should be no need to roll forward any transactions - and roll forward is what the error refers to. And since it apparently is necessary to roll forward transaction, then sp_attach_single_file_db would not help because we have no log file. We have a catch 22 situation. I don't know what happened, since we have so little information, but there seems to be some piece of the puzzle missing. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Eric Isaacs" <eisa***@gmail.com> wrote in message news:6c42a715-b527-4498-a669-41fd0c37f196@s9g2000prm.googlegroups.com... > You might consider using sp_attach_single_file_db which will allow > you > attach the MDF file without the LDF. Because of the disk space > issues, you probably have a lot of uncommitted transactions in the > log > file. You will probably lose these transactions if you use this > method, but it might allow you to get that database connected again. > > -Eric Isaacs Unfortunately I am still getting error messages relating to the log file
while using the "sp_attach_single_file_". I get this error message if the log file is still available on the system: Server: Msg 3624, Level 20, State 1, Line 1 1450 transactions rolled forward in database 'database' (10). Location: page.cpp:2005 Expression: IsValidSlot (sid) SPID: 57 Process ID: 1760 Connection Broken I get this error message when the log file is not accessible: Server: Msg 1813, Level 16, State 2, Line 1 Could not open new database 'OnePoint'. CREATE DATABASE is aborted. Device activation error. The physical file name 'E:\microsoft sql server\MSSQL$SQLSVCAPPL\Data\Database_logfile' may be incorrect. Any possibility to bypass the logfile errormessage? Thanks Show quoteHide quote "Eric Isaacs" wrote: > You might consider using sp_attach_single_file_db which will allow you > attach the MDF file without the LDF. Because of the disk space > issues, you probably have a lot of uncommitted transactions in the log > file. You will probably lose these transactions if you use this > method, but it might allow you to get that database connected again. > > -Eric Isaacs > If SQL Server believe that it need to do recovery, then it need the
ldf file. Something didn't go right with the detach, so I suggest you go back to the source machine and re-detach the database so you can try again. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "topokin" <topo***@discussions.microsoft.com> wrote in message news:296F4ACA-ADB2-45CD-8F62-09F129EE87E6@microsoft.com... > Unfortunately I am still getting error messages relating to the log > file > while using the "sp_attach_single_file_". > > I get this error message if the log file is still available on the > system: > Server: Msg 3624, Level 20, State 1, Line 1 > > 1450 transactions rolled forward in database 'database' (10). > Location: page.cpp:2005 > Expression: IsValidSlot (sid) > SPID: 57 > Process ID: 1760 > > Connection Broken > > I get this error message when the log file is not accessible: > Server: Msg 1813, Level 16, State 2, Line 1 > Could not open new database 'OnePoint'. CREATE DATABASE is aborted. > Device activation error. The physical file name 'E:\microsoft sql > server\MSSQL$SQLSVCAPPL\Data\Database_logfile' may be incorrect. > > Any possibility to bypass the logfile errormessage? > > Thanks > > "Eric Isaacs" wrote: > >> You might consider using sp_attach_single_file_db which will allow >> you >> attach the MDF file without the LDF. Because of the disk space >> issues, you probably have a lot of uncommitted transactions in the >> log >> file. You will probably lose these transactions if you use this >> method, but it might allow you to get that database connected >> again. >> >> -Eric Isaacs >> I have a proved solution for the corrupted LOG file issue. The solution is
based on the new LOG creation. So, all the data in the old LOG will be lost. The solution is as follows. 1. Create a new database with the same name and same MDF and LDF files 2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files. 3. Start SQL Server 4. Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files update sysdatabases set status=32768 where name ='dbname' 6. Restart sql server. now the database will be in emergency mode 7. Now execute the undocumented DBCC to create a log file DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') 8. Execute sp_resetstatus <dbname> 9. Restart SQL server and see the database is online. What you forgot to mention is that we now have a database which most
probably is corrupt at both the logical (user information) level as well as the physical (allocation structures etc) level. I.e., one should do a manual verification of the data after such an operation - something that can be extremely time-consuming. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Dmitrij Siemieniako" <DmitrijSiemieni***@discussions.microsoft.com> wrote in message Show quoteHide quote news:FA09DC26-BE6E-4B14-934F-4F9BA46CCCEA@microsoft.com... >I have a proved solution for the corrupted LOG file issue. The >solution is > based on the new LOG creation. So, all the data in the old LOG will > be lost. > The solution is as follows. > 1. Create a new database with the same name and same MDF and LDF > files 2. > Stop sql server and rename the existing MDF to a new one and copy > the > original MDF to this location and delete the LDF files. > 3. Start SQL Server > 4. Now your database will be marked suspect 5. Update the > sysdatabases to > update to Emergency mode. This will not use LOG files > update sysdatabases set status=32768 where name ='dbname' > 6. Restart sql server. now the database will be in emergency mode > 7. Now execute the undocumented DBCC to create a log file > DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') > 8. Execute sp_resetstatus <dbname> > 9. Restart SQL server and see the database is online. > Ken Henderson wrote about this in Chapter 2 of SQL Server 2005 Practical
Troubleshooting. It should only be used as a last ditch effort to recover where you have no other options. An open transaction may have been flushed to disk because of memory pressure and it is written to the data file but the log would show it open and roll it back since it wasn't commited. This has a impact on the transactional consistency of the data in the database which is not a good thing. Show quoteHide quote "Dmitrij Siemieniako" <DmitrijSiemieni***@discussions.microsoft.com> wrote in message news:FA09DC26-BE6E-4B14-934F-4F9BA46CCCEA@microsoft.com... >I have a proved solution for the corrupted LOG file issue. The solution is > based on the new LOG creation. So, all the data in the old LOG will be > lost. > The solution is as follows. > 1. Create a new database with the same name and same MDF and LDF files 2. > Stop sql server and rename the existing MDF to a new one and copy the > original MDF to this location and delete the LDF files. > 3. Start SQL Server > 4. Now your database will be marked suspect 5. Update the sysdatabases to > update to Emergency mode. This will not use LOG files > update sysdatabases set status=32768 where name ='dbname' > 6. Restart sql server. now the database will be in emergency mode > 7. Now execute the undocumented DBCC to create a log file > DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') > 8. Execute sp_resetstatus <dbname> > 9. Restart SQL server and see the database is online. > Dmitrij
Thanks for the tips. Following your steps I was able to get the database back online. Only trying to change the configuration of the TransactionLog file (increase size and remove AutoGrow) was throwing error message. So what I did was to detach and re-attach the database again, then I was able to change the size. Things seems to be working fine, but a scheduled job that does a bulk update (deleting outdated entries in the DB) is throwing this error message, which I guess might have been the cause of the whole problem: "Could not find the index entry for RID '16c532b379010000' in index page (1:118), index ID 3, database 'Database Name'.. Error: 644, Severity: 21, State: 5" The resolution I found on MS website is to apply the latest SP, but the SQL_Instance already have the latest SP4 installed. What should I look for to address the this? Thanks topokin Show quoteHide quote "Dmitrij Siemieniako" wrote: > I have a proved solution for the corrupted LOG file issue. The solution is > based on the new LOG creation. So, all the data in the old LOG will be lost. > The solution is as follows. > 1. Create a new database with the same name and same MDF and LDF files 2. > Stop sql server and rename the existing MDF to a new one and copy the > original MDF to this location and delete the LDF files. > 3. Start SQL Server > 4. Now your database will be marked suspect 5. Update the sysdatabases to > update to Emergency mode. This will not use LOG files > update sysdatabases set status=32768 where name ='dbname' > 6. Restart sql server. now the database will be in emergency mode > 7. Now execute the undocumented DBCC to create a log file > DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') > 8. Execute sp_resetstatus <dbname> > 9. Restart SQL server and see the database is online. > Did you use DBCC REBUILD_LOG? If so, a corrupted database *is
expected*. See my other post. It's like having a car that doesn't start so you whack it with a huge sledgehammer and then complain that the car is dented. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "topokin" <topo***@discussions.microsoft.com> wrote in message news:2EE49EE9-B786-4707-9DF8-3C55CA5FAB5A@microsoft.com... > Dmitrij > > Thanks for the tips. Following your steps I was able to get the > database > back online. Only trying to change the configuration of the > TransactionLog > file (increase size and remove AutoGrow) was throwing error message. > So what > I did was to detach and re-attach the database again, then I was > able to > change the size. > > Things seems to be working fine, but a scheduled job that does a > bulk update > (deleting outdated entries in the DB) is throwing this error > message, which I > guess might have been the cause of the whole problem: > > "Could not find the index entry for RID '16c532b379010000' in index > page > (1:118), index ID 3, database 'Database Name'.. > Error: 644, Severity: 21, State: 5" > > The resolution I found on MS website is to apply the latest SP, but > the > SQL_Instance already have the latest SP4 installed. > > What should I look for to address the this? > > Thanks > topokin > > > > "Dmitrij Siemieniako" wrote: > >> I have a proved solution for the corrupted LOG file issue. The >> solution is >> based on the new LOG creation. So, all the data in the old LOG will >> be lost. >> The solution is as follows. >> 1. Create a new database with the same name and same MDF and LDF >> files 2. >> Stop sql server and rename the existing MDF to a new one and copy >> the >> original MDF to this location and delete the LDF files. >> 3. Start SQL Server >> 4. Now your database will be marked suspect 5. Update the >> sysdatabases to >> update to Emergency mode. This will not use LOG files >> update sysdatabases set status=32768 where name ='dbname' >> 6. Restart sql server. now the database will be in emergency mode >> 7. Now execute the undocumented DBCC to create a log file >> DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') >> 8. Execute sp_resetstatus <dbname> >> 9. Restart SQL server and see the database is online. >> Tibor,
Yes I did use DBCC REBUILD_LOG and I'm actually not complaining. I am just seeking the possibilty to get things back, it there is any. Thanks. Show quoteHide quote "Tibor Karaszi" wrote: > Did you use DBCC REBUILD_LOG? If so, a corrupted database *is > expected*. See my other post. > > It's like having a car that doesn't start so you whack it with a huge > sledgehammer and then complain that the car is dented. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "topokin" <topo***@discussions.microsoft.com> wrote in message > news:2EE49EE9-B786-4707-9DF8-3C55CA5FAB5A@microsoft.com... > > Dmitrij > > > > Thanks for the tips. Following your steps I was able to get the > > database > > back online. Only trying to change the configuration of the > > TransactionLog > > file (increase size and remove AutoGrow) was throwing error message. > > So what > > I did was to detach and re-attach the database again, then I was > > able to > > change the size. > > > > Things seems to be working fine, but a scheduled job that does a > > bulk update > > (deleting outdated entries in the DB) is throwing this error > > message, which I > > guess might have been the cause of the whole problem: > > > > "Could not find the index entry for RID '16c532b379010000' in index > > page > > (1:118), index ID 3, database 'Database Name'.. > > Error: 644, Severity: 21, State: 5" > > > > The resolution I found on MS website is to apply the latest SP, but > > the > > SQL_Instance already have the latest SP4 installed. > > > > What should I look for to address the this? > > > > Thanks > > topokin > > > > > > > > "Dmitrij Siemieniako" wrote: > > > >> I have a proved solution for the corrupted LOG file issue. The > >> solution is > >> based on the new LOG creation. So, all the data in the old LOG will > >> be lost. > >> The solution is as follows. > >> 1. Create a new database with the same name and same MDF and LDF > >> files 2. > >> Stop sql server and rename the existing MDF to a new one and copy > >> the > >> original MDF to this location and delete the LDF files. > >> 3. Start SQL Server > >> 4. Now your database will be marked suspect 5. Update the > >> sysdatabases to > >> update to Emergency mode. This will not use LOG files > >> update sysdatabases set status=32768 where name ='dbname' > >> 6. Restart sql server. now the database will be in emergency mode > >> 7. Now execute the undocumented DBCC to create a log file > >> DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') > >> 8. Execute sp_resetstatus <dbname> > >> 9. Restart SQL server and see the database is online. > >> > > The thing is that you can have all types of corruptions because of the
rebuild of the log, where some might be repairable without data loss, some might be repairable with data loss and some might not even be repairable in the first place. If you really want to work off this database, then spending some time with reading the documentation and Google for DBCC CHECKDB can be a good start. I would definitely go for restoring your most recent clean backup instead. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "topokin" <topo***@discussions.microsoft.com> wrote in message news:B12B32C0-30F5-46CC-9661-78A447743D21@microsoft.com... > Tibor, > > Yes I did use DBCC REBUILD_LOG and I'm actually not complaining. I > am just > seeking the possibilty to get things back, it there is any. > > Thanks. > > "Tibor Karaszi" wrote: > >> Did you use DBCC REBUILD_LOG? If so, a corrupted database *is >> expected*. See my other post. >> >> It's like having a car that doesn't start so you whack it with a >> huge >> sledgehammer and then complain that the car is dented. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> "topokin" <topo***@discussions.microsoft.com> wrote in message >> news:2EE49EE9-B786-4707-9DF8-3C55CA5FAB5A@microsoft.com... >> > Dmitrij >> > >> > Thanks for the tips. Following your steps I was able to get the >> > database >> > back online. Only trying to change the configuration of the >> > TransactionLog >> > file (increase size and remove AutoGrow) was throwing error >> > message. >> > So what >> > I did was to detach and re-attach the database again, then I was >> > able to >> > change the size. >> > >> > Things seems to be working fine, but a scheduled job that does a >> > bulk update >> > (deleting outdated entries in the DB) is throwing this error >> > message, which I >> > guess might have been the cause of the whole problem: >> > >> > "Could not find the index entry for RID '16c532b379010000' in >> > index >> > page >> > (1:118), index ID 3, database 'Database Name'.. >> > Error: 644, Severity: 21, State: 5" >> > >> > The resolution I found on MS website is to apply the latest SP, >> > but >> > the >> > SQL_Instance already have the latest SP4 installed. >> > >> > What should I look for to address the this? >> > >> > Thanks >> > topokin >> > >> > >> > >> > "Dmitrij Siemieniako" wrote: >> > >> >> I have a proved solution for the corrupted LOG file issue. The >> >> solution is >> >> based on the new LOG creation. So, all the data in the old LOG >> >> will >> >> be lost. >> >> The solution is as follows. >> >> 1. Create a new database with the same name and same MDF and LDF >> >> files 2. >> >> Stop sql server and rename the existing MDF to a new one and >> >> copy >> >> the >> >> original MDF to this location and delete the LDF files. >> >> 3. Start SQL Server >> >> 4. Now your database will be marked suspect 5. Update the >> >> sysdatabases to >> >> update to Emergency mode. This will not use LOG files >> >> update sysdatabases set status=32768 where name ='dbname' >> >> 6. Restart sql server. now the database will be in emergency >> >> mode >> >> 7. Now execute the undocumented DBCC to create a log file >> >> DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') >> >> 8. Execute sp_resetstatus <dbname> >> >> 9. Restart SQL server and see the database is online. >> >> >> >> Sorry, topokin, I should not omit such an obvious thing as DBCC CHECKDB after
such "manipuilations". Jonathan, Tibor - thank you for comments. Regards. Dmitrrij Show quoteHide quote "Tibor Karaszi" wrote: > The thing is that you can have all types of corruptions because of the > rebuild of the log, where some might be repairable without data loss, > some might be repairable with data loss and some might not even be > repairable in the first place. If you really want to work off this > database, then spending some time with reading the documentation and > Google for DBCC CHECKDB can be a good start. I would definitely go for > restoring your most recent clean backup instead. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "topokin" <topo***@discussions.microsoft.com> wrote in message > news:B12B32C0-30F5-46CC-9661-78A447743D21@microsoft.com... > > Tibor, > > > > Yes I did use DBCC REBUILD_LOG and I'm actually not complaining. I > > am just > > seeking the possibilty to get things back, it there is any. > > > > Thanks. > > > > "Tibor Karaszi" wrote: > > > >> Did you use DBCC REBUILD_LOG? If so, a corrupted database *is > >> expected*. See my other post. > >> > >> It's like having a car that doesn't start so you whack it with a > >> huge > >> sledgehammer and then complain that the car is dented. > >> > >> -- > >> Tibor Karaszi, SQL Server MVP > >> http://www.karaszi.com/sqlserver/default.asp > >> http://sqlblog.com/blogs/tibor_karaszi > >> > >> > >> "topokin" <topo***@discussions.microsoft.com> wrote in message > >> news:2EE49EE9-B786-4707-9DF8-3C55CA5FAB5A@microsoft.com... > >> > Dmitrij > >> > > >> > Thanks for the tips. Following your steps I was able to get the > >> > database > >> > back online. Only trying to change the configuration of the > >> > TransactionLog > >> > file (increase size and remove AutoGrow) was throwing error > >> > message. > >> > So what > >> > I did was to detach and re-attach the database again, then I was > >> > able to > >> > change the size. > >> > > >> > Things seems to be working fine, but a scheduled job that does a > >> > bulk update > >> > (deleting outdated entries in the DB) is throwing this error > >> > message, which I > >> > guess might have been the cause of the whole problem: > >> > > >> > "Could not find the index entry for RID '16c532b379010000' in > >> > index > >> > page > >> > (1:118), index ID 3, database 'Database Name'.. > >> > Error: 644, Severity: 21, State: 5" > >> > > >> > The resolution I found on MS website is to apply the latest SP, > >> > but > >> > the > >> > SQL_Instance already have the latest SP4 installed. > >> > > >> > What should I look for to address the this? > >> > > >> > Thanks > >> > topokin > >> > > >> > > >> > > >> > "Dmitrij Siemieniako" wrote: > >> > > >> >> I have a proved solution for the corrupted LOG file issue. The > >> >> solution is > >> >> based on the new LOG creation. So, all the data in the old LOG > >> >> will > >> >> be lost. > >> >> The solution is as follows. > >> >> 1. Create a new database with the same name and same MDF and LDF > >> >> files 2. > >> >> Stop sql server and rename the existing MDF to a new one and > >> >> copy > >> >> the > >> >> original MDF to this location and delete the LDF files. > >> >> 3. Start SQL Server > >> >> 4. Now your database will be marked suspect 5. Update the > >> >> sysdatabases to > >> >> update to Emergency mode. This will not use LOG files > >> >> update sysdatabases set status=32768 where name ='dbname' > >> >> 6. Restart sql server. now the database will be in emergency > >> >> mode > >> >> 7. Now execute the undocumented DBCC to create a log file > >> >> DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') > >> >> 8. Execute sp_resetstatus <dbname> > >> >> 9. Restart SQL server and see the database is online. > >> >> > >> > >> > >  
Other interesting topics
Migrating SQL 2000 to SQL 2005, any risk I change db collation too?
Trigger and Misc. Adding memory has degraded performance Alter Index On Database Rebuild (SQL Server 2005) SQL GROUP BY CLAUSE SQL Server 2005 Cannot refer column Schema Across Database Update? [SAN] More spindles via multiple RAID/LUNs, or fewer & more focused LUNs? SQL Server 2005 Alter Index All On DB Rebuild Documentation / Checklists |
|||||||||||||||||||||||
