Solved

MDF/LDF File

Posted on 2004-08-30
15
6,635 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
  • 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 42

Accepted Solution

by:
EugeneZ 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 42

Expert Comment

by:EugeneZ
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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 42

Expert Comment

by:EugeneZ
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now