Solved

MDF/LDF File

Posted on 2004-08-30
15
6,652 Views
Last Modified: 2012-08-14
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
Comment
Question by:Torus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
  • +3
15 Comments
 
LVL 5

Assisted Solution

by:talphius
talphius earned 20 total points
ID: 11935717
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
 
LVL 43

Accepted Solution

by:
Eugene Z earned 80 total points
ID: 11935734
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 11935752
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 5

Expert Comment

by:talphius
ID: 11935867
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
 
LVL 2

Author Comment

by:Torus
ID: 11938760
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
 
LVL 2

Author Comment

by:Torus
ID: 11938788
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
 
LVL 2

Author Comment

by:Torus
ID: 11938967
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
 
LVL 2

Author Comment

by:Torus
ID: 11939202
I have already detach the database.

Don't know if DBCC works after database is detached.
0
 
LVL 2

Author Comment

by:Torus
ID: 11939488
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
 
LVL 10

Expert Comment

by:imrancs
ID: 11940136
>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
 
LVL 9

Expert Comment

by:auke_t
ID: 11940178
0
 
LVL 2

Author Comment

by:Torus
ID: 11941406
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
 
LVL 2

Author Comment

by:Torus
ID: 11941568
Another question. Can I shrink the master database?
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 11947959
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
 
LVL 34

Expert Comment

by:arbert
ID: 11948165
"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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question