How to delete .ldf file from SQL Server 2008?

Question

Thanks in Advance

If I stop SQL-server and then delete the .LDF file (transactionlog file) to the database, what will happen ? Will the database be marked suspect or will SQL-server just create a new automatically ? SQL Server 2008 R2 And My .LDF file Size is Too Big, So how to manage it, whether I can Shrink it or delete Plz Suggest in the Query Form

Thanks

Answer 1

You should not delete any of the database files since it can severely damage your database!

If you run out of disk space you might want to split your database in multiple parts. This can be done in the database's properties. So you are able to put each part of the database to a different storage volume.

You also can shrink the transaction log file if you change the recovery mode from full to simple, using following commands:

ALTER DATABASE myDatabase SET RECOVERY SIMPLE DBCC SHRINKDATABASE (myDatabase , 5) 

Switching back to full recovery is possible as well:

ALTER DATABASE myDatabase SET RECOVERY FULL 

Update about SHRINKDATABASE - or what I did not know when answering this question:

Although the method above gets rid off some unused space it has some severe disadvantages on database files (MDF) - it will harm your indexes by fragmenting them worsening the performance of your database. So you need to rebuild the indexes afterwards to get rid off the fragmentation the shrink command caused.

If you want to shrink just the log file only might want to use SHRINKFILE instead. I copied this example from MSDN:

USE AdventureWorks2012; GO -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE AdventureWorks2012 SET RECOVERY SIMPLE; GO -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (AdventureWorks2012_Log, 1); GO -- Reset the database recovery model. ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL; GO 

Answer 2

Do not risk deleting your LDF files manually! If you do not need transaction files or wish to reduce them to any size you choose, follow these steps: (Note this will affect your backups so be sure before doing so)

1. Right click database
2. Choose Properties
3. Click on the 'Options' tab.
4. Set recovery model to SIMPLE
5. Next, choose the FILES tab
6. Now make sure you select the LOG file and scroll right. Under the "Autogrowth" heading click the dots ....
7. Then disable Autogrowth (This is optional and will limit additional growth)
8. Then click OK and set the "Initial Size" to the size you wish to have (I set mine to 20MB)
9. Click OK to save changes
10. Then right-click the DB again, and choose "Tasks > Shrink > Database", press OK.
11. Now compare your file sizes!:)