[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6684
  • Last Modified:

MDF/LDF File

I accidentally deleted the LDF file. Now I can't access the database. What can I do? Can I generate the LDF from the MDF? Also, the MDF and LDF continous to grow bigger and bigger. How can I control them? If I limit the LDF size, the old transactions log will be deleted automatically and new transactions log will replace the old one when the file size reach the limited?  How about the MDF, can I compact it like Access?

Thanks
0
Torus
Asked:
Torus
  • 7
  • 3
  • 2
  • +3
2 Solutions
 
talphiusCommented:
You should be able to attach the database in EM and have it recreate the LDF for you.  

Open Enterprise Manager
Right click on the Database folder, Choose All Tasks : Attach database
Browse to the MDF file
Enter attach as name, click OK.
It should recreate the file for you.

As for the size limits, these too can be specified in EM.  
Open Enterprise Manager
Right click the database, choose properties
On the Data Files tab, you can set the maximum growth restriction.  

This can also be done for the Transaction Log as well, through the Transaction Log Tab.  

You can shrink the databases - check in Books Online (DBCC ShrinkDatabase).
0
 
Eugene ZCommented:
try script  
DBCC REBUILD_LOG('MyDatabase','C:\MyDatabase.ldf')
example from:
from http://www.winnetmag.com/SQLServer/Article/ArticleID/26044/SQLServer_26044.html

LISTING 1: Undocumented DBCC Command REBUILD_LOG
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

BEGIN TRAN

UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = 'MyDatabase'

IF @@ROWCOUNT = 1
BEGIN
   COMMIT TRAN
   RAISERROR('emergency mode set', 0, 1)
END
ELSE
BEGIN
   ROLLBACK
   RAISERROR('unable to set emergency mode', 16, 1)
END

GO

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

-- Restart SQL Server at this point.

DBCC REBUILD_LOG('MyDatabase','C:\MyDatabase.ldf')


/*Perform physical and logical integrity checks at this point.
Bcp data out if your integrity checks demonstrate that problems exist.
*/

ALTER DATABASE MyDatabase SET MULTI_USER
GO

-- Set database options and recovery model as desired.
GO


0
 
Eugene ZCommented:
if you have sql server 2000 and do not need transaction log backup - make Simple recovery mode
if need backup - have full mode - and run regular transactions log backups..
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
talphiusCommented:
Just tried this to ensure it worked (SQL 2000).  I detached a test DB and moved the log file from where it expected it and performed the following:

Open Enterprise Manager
Right click on the Database folder, Choose All Tasks : Attach database
Browse to the MDF file, click OK
The log file will show with an 'X' in it.  
Click OK, you will receive a warning "The filename specified for the log file is incorrect.  A new log file may be created.  Do you want to continue?".
Clicking Yes will display "Attaching database has completed successfully" and a new log file will be created
0
 
TorusAuthor Commented:
talphius, it didn't work.

I used sp_Attach_db and sp_attach_single_file_db also. But go the following error

Server: Msg 1813,
Level 16, State 2, Line 1
Could not open new database 'db'.
 CREATE DATABASE is aborted.
Device activation error.
The physical file name
'C:\Program Files\Microsoft SQL ServerMSSQL\datadb_log.LDF' may be incorrect.
0
 
TorusAuthor Commented:
EugeneZ ,

Can you explain your feedback in detail? I am using sql 2000 server. I try to run your script as you posted but got error in DBCC REBUILD_LOG

EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

BEGIN TRAN

UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = 'MyDatabase'

IF @@ROWCOUNT = 1
BEGIN
   COMMIT TRAN
   RAISERROR('emergency mode set', 0, 1)
END
ELSE
BEGIN
   ROLLBACK
   RAISERROR('unable to set emergency mode', 16, 1)
END

GO

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

-- Restart SQL Server at this point.

DBCC REBUILD__LOG('CallingCard','e:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabase.ldf')

Server :  Msg 50000,Level 16,state 1,Line 16
unable to set emergency mode
DBCC is completed。if DBCC has error,please contact your administrator。
The setting 'allow updates' changed from 1 to 0。please excute RECONFIGURE for installation。
server: msg 2526,level 16,state 3,line 4
incorrect syntax near DBCC。Please check the documentation.

What's wrong?
Thanks




0
 
TorusAuthor Commented:
What will be happened if the database or log reach the limit especially the database? If more data is added to the database, warning message or error will appear if exceeds the limit ? or some data will be lost?

0
 
TorusAuthor Commented:
I have already detach the database.

Don't know if DBCC works after database is detached.
0
 
TorusAuthor Commented:
EugeneZ ,

I typed incorrectly so that I go the incorrect syntax error. But now I have the other error because my database record cannot be found in master..database. What can I do? Add it automatically? how about the sid, etc. how to assign those value?

Thanks
0
 
imrancsCommented:
>I used sp_Attach_db and sp_attach_single_file_db also. But go the following error

>Server: Msg 1813,
>Level 16, State 2, Line 1
>Could not open new database 'db'.
> CREATE DATABASE is aborted.
>Device activation error.
>The physical file name
>'C:\Program Files\Microsoft SQL ServerMSSQL\datadb_log.LDF' may be incorrect.


I think its the invalid path problem because you have missed the '\' between Microsoft SQL ServerMSSQL it should be
'C:\Program Files\Microsoft SQL Server\MSSQL\datadb_log.LDF'


Imran
0
 
TorusAuthor Commented:
imrancs, not the case. I think I have typing mistake only. I tried many times and make sure the path is ok. If the path is not ok, it is another error. I think sp_attach_db only works without LDF only if there is no transactions before.  I created a new database and then delete the LDF, sp_attach_db works in this situation
0
 
TorusAuthor Commented:
Another question. Can I shrink the master database?
0
 
Eugene ZCommented:
Torus:

Plan:
1. Copy or rename your MDF file
2. Create same named database with same MDF and ldf files
3. Stop sql service
4. Copy old (main) MDF  file over "new" (or just delete new and copy\rename back old one)
5.start sql server
6. run the script  from above
...
0
 
arbertCommented:
"Just tried this to ensure it worked (SQL 2000).  I detached a test DB and moved the log file from where it expected it and performed the following:"

SQL Server will recreate the log file IF IT CAN.  You detached the database and then reattached it, so the database was clean and SQL Server COULD recreate the log file....Most instances it won't/can't recreate it on its own....
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 7
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now