Hai,

I am trying to attach the *.mdf file then i am getting the error 1813.
The problem occurs when our development database transcation running out of memory disk, then I dettach database and at the time of dettaching database some of users are connected. I kill the session of all users.
After I deleted the log file. Now when i am trying to attach the database then I am getting the error no 1813.

Anybody urgent reply

-----------------------------------------------------------------------------------------------------------------------------------------------------
1) move the existing .MDF file to a new location (to backup)
2) Start SQL Server Enterprise manager, and create a new (dummy) database where the .MDF file is the exact same name and size of your old/existing .MDF. The ldf file can remain at 1mb in size.

3) Stop the SQL Server service and copy only the .MDF to the location of the new/dummy .MDF file.

4) Start SQL Server. At this point, the database should come up as suspect. You will then want to place the database into emergency bypass mode and rebuild the Transaction log. This can be accomplished by performing the following steps:

a) Change the database context to Master and allow updates to system tables. Note you will be performing this within Query Anayzler:
Use Master
go
sp_configure 'allow updates',1
reconfigure with override
go
b) Set the database in emergency bypass mode:
Select dbid, name, status from sysdatabases where name = '<database name>'
-- Note the value of status and write it down for future reference
begin tran
update sysdatabases set status = 32768 where name = '<database name>'
commit tran
c) Stop and restart your SQL Server. At this point, the database will come up in emergency mode. At this point, you will be able to browse, but not update any data within the database.

d) We will now want to rebuild the log/ldf file. To do this, you will want to run the following:

DBCC rebuild_log('<database name>','')
If you do not receive any errors, we will want to reset the status of the database by running the following commmand and restarting SQL Server:
use master
exec sp_dboption 'database name','single user',true
go
begin tran
update sysdatabases set status = 0 where name = '<database name>'
commit tran
e) set database option to not allow updates to the system tables:

sp_configure 'allow updates', 0
reconfigure with override
go
f) stop and restart SQL Server
When the server restarts, you will then want to launch Query Analyzer and run the following to validate the database and check the overall integrity:

use master
go
sp_dboption '<database name>','Single user',true
You will then want to run the following:
DBCC checkdb('<database name>')

 

 

Thank you so much.  You just saved me alot of headaches.  My transaction log spun out of control, it was up to 28 GB.  I detached the db and tried to reatach and I got this problem.  Normally it would just automatically create a log file I thought. 

Thanks again!

Thank You!!!

Hello,

Please note the process is not applicable to SQL Server 2005 and up (for example statement update sysdatabases set status = 32768 where name = '<database name>' is not valid for SQL Server 2005). This is true even if you are running your database in SQL Server 2000 compatibility mode.

Thanks..

when i execute 

Select dbid, name, status from sysdatabases where name = '<database name>'
-- Note the value of status and write it down for future reference
begin tran
update sysdatabases set status = 32768 where name = '<database name>'
commit tran

i face following error ..

Msg 259, Level 16, State 1, Line 4
Ad hoc updates to system catalogs are not allowed.

so what should i do?

IF SQL 2012, apply  CU

https://support.microsoft.com/en-us/kb/2733673

To Update Sys databases:

sp_configure 'allow updates',1
go
reconfigure