September 7, 2006 at 2:02 am
Hi,
I deleted LOG file from test server, thinking that I'll be able to rebuilt it, the way I did it number of times on SQL 2000, but DBCC REBUILD_LOG is not working in 2005 (giving syntax error).
After looking around found ATTACH_REBUILD_LOG option with CREATE DATABASE , but its not working for me, gives operating system error 2, Could someone help me in recreating log in 2005.
Cheers
Deepak Khattar
September 7, 2006 at 8:05 am
See BOL for sp_attach_single_file_db
Also See if you can detach the DB and then try and attach but do not specify trans log when you attach it just specify the mdf and the path, a new trans log will be created with a default size in the default location.
September 7, 2006 at 8:16 am
Vik,
sp_attach_single_file_db will work if database was cleanly shutdown i.e. it was detached.
When sp_attach_single_file_db attaches the database to the server, it builds a new log file and performs additional cleanup work to remove replication from the newly attached database.
Used sp_attach_single_file_db only on databases that were previously detached from the server using an explicit sp_detach_db operation.
===========
OS error 2 = The system cannot find the file specified.
Few questions:
what is the syntax you are using?
As per BOL (SQL 2005)
FOR ATTACH_REBUILD_LOG requires the following:
HTH
September 7, 2006 at 8:50 am
HTH,
You are correct - if the DB was not shutdown cleanly - it will not work. I guess the only way is:
1. Rename database.mdf to old_database.mdf
2. Create New Database with same name as old database
3. Change DB properties to "Allow modifications to be made directly to the system catalogs"
4. Run this Query
update sysdatabases
set status = 32768
where name='database_name'
5. Stop/Start SQL Server
Database will be in Emergency Mode
Now DTS all the data out of this DB
September 10, 2006 at 7:43 am
A system admin in a company I know of did exactly the same, "to reclaim disk space". His backup routine didn't include transaction log backups. To make matters worse, a restore was impossible, because the tape drive had been out of order for a couple of weeks.
I said, 'Yes - I'll see what I can do', and dashed off to my keyboard to search the net. Eventually, I found some possible solutions. All relied on being able to modify the system tables, as some have mentioned above. I didn't want to try the procedure without checking with Microsoft, so for a few houndred dollars they provided me with a procedure practically similar to the ones on the net:
Microsofts disclaimer on DBCC REBUILD_LOG:
The Command is an undocumented and unsupported command that can be used in emergency situations where the database is offline because of some catastrophic problem with the transaction log. It is designed to destroy the entire contents of the current transaction log, rebuild a new one, and allow the user to bring the database back online. However, this may result in more data lost than if the last known, good backup was restored. As such, if you choose to use the Command with or without the assistance of Microsoft PSS, Microsoft strongly recommends you backup all of the database and transaction log files associated with the database for the Command as a method to recover from problems where the Command is being considered.
If this Command is successful and the database is brought back online, the database may be in a physically and logically inconsistent state. The ACID properties of Atomicity and Consistency are no longer guaranteed. The DBCC CHECKDB command can be used to determine what physical problems may exist. However, there is no method to determine what logical inconsistencies exist within the database aside from the user’s own manual inspection of the database tables. This means that there is no method to determine if the database pages that exist within the database reflect the correct committed state of the database when it was last taken offline.
Take a backup first!
1. Create a new DB with the same name as the original DB.
2. Stop SQL Server.
3. Replace the old MDF atop of the newly created one.
4. Start SQL Server, DB should be marked as suspect. Note that the status under master..sysdatabases of the DB is 1073741840.
5.Change the database context to Master and allow updates to system tables:
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
6.
begin tran
-- Putting" a database in emergency mode
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran
-- If you run DBCC REBUILD_LOG without setting the database in Emergency mode, the command does not work.
-- You do not receive an error, but the log is not rebuilt either.
7.
-- The syntax for DBCC REBUILD_LOG is as follows:
DBCC REBUILD_LOG('<db_name>','<log_filename>')
-- Where <db_name> is the name of the database and <log_filename> is the physical path of the
-- new log file, not a logical file name. If you do not specify the full path, the new log is created in
-- the same directory as the primary database file (.mdf). Therefore, if the log file already exists in that
-- path, use a different name for the new log. You cannot move the log or delete the log because SQL
-- Server is using the log).
8.
begin tran
update sysdatabases set status = 0 where name = '<db_name>'
-- verify one row is updated before committing
commit tran
9.
RESTORE DATABASE <dbname> WITH RECOVERY
Good luck!
This procedure actually worked, the sysadmin didn't loose his job, and the company is thriving.
September 11, 2006 at 8:23 pm
Nice post with all steps. but....
DBCC REBUILD_LOG doesnot work with SQL 2005.
September 12, 2006 at 1:42 am
Eh - blush... How could I ignore that? They deliberately made it useless and replaced it with other, unsupported command(s).
Bastards.
Anyway, hope it can be of use to desperate SQL 2000-users without proper backup routines...
July 16, 2010 at 1:43 pm
Its replaced with Alter database for attach_rebuild_log and for attach_force_rebuild_log. Second one is not documented.
use second one at own risk..
July 16, 2010 at 2:54 pm
Please note: 4 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2010 at 4:56 pm
np :). I felt that thread was incomplete.
December 24, 2010 at 11:10 am
December 24, 2010 at 1:19 pm
You would probably be better served by starting a new thread rather than add to a 4 year old thread.
November 21, 2011 at 11:03 am
Gourav Das (7/16/2010)
np :). I felt that thread was incomplete.
I don't care if its 40 years old. nwb's post about 'attach_force_rebuild_log' lead to a final solution that saved my !@#.
I had a 1tb database that had just imported millions of records over 36 hours before crashing with a created log file of 160gigs in that day and a half. I didn't have 2-3 days to allow this to 'recover' (data loss not a problem in this case [importing of data can be replicated at failed records] I just could not afford the lost time)
DBCC TraceOn(1807)
CREATE DATABASE myCoolDB
ON (Filename = N'\\my\cooletwork\path\myCoolDB.mdf')
FOR ATTACH_FORCE_REBUILD_LOG
DBCC TraceOff(1807)
November 21, 2011 at 11:19 am
Do note that even that can and will fail. It can also result in a DB that's structurally inconsistent (not just transactionally), one that's got metadata corruption, allocation corruption or other sorts of really unpleasant and very hard to fix problems.
The log is not something that can be deleted without concern.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply