How to delete .ldf file from SQL Server 2008?

Question

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 Please Suggest in the Query Form.

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!:)

Answer 3

I did it by

• Detach the database (include Drop Connections)
• Remove the *.ldf file
• Attach the database, but remove the expected *.ldf file

Did it for 4 different databases in SQL 2012, i should be the same for SQL 2008

Answer 4

As you can read comments, it is not good solution to remove log. But if you are sure that you do not lose anything, you can just change your DB recovery mode to simple and then use

DBCC shrinkdatabase ('here your database name')

to clear your log.

The worst thing that you can do is to delete log file from disk. If your server had unfinished transactions at moment of server stop, those transactions will not roll back after restart and you will get corrupted data.

Answer 5

You should back up your transaction log, then there will be free space to shrink it. Changing to simple mode then shrinking means you will lose all the transaction data which would be useful in the event of a restore.

Answer 6

The best way to clear ALL ldf files (transaction log files) in all databases in MS SQL server, IF all databases was backed up earlier of course:

USE MASTER
print '*****************************************'
print '************ Czyścik LDF ****************'
print '*****************************************'

declare
   @isql varchar(2000),
   @dbname varchar(64),
   @logfile varchar(128),
   @recovery_model varchar(64)

   declare c1 cursor for 
   SELECT  d.name, mf.name as logfile, d.recovery_model_desc  --, physical_name AS current_file_location, size
   FROM sys.master_files mf
      inner join sys.databases d
      on mf.database_id = d.database_id
   --where recovery_model_desc <> 'SIMPLE'
   and d.name not in ('master','model','msdb','tempdb') 
   and mf.type_desc = 'LOG'
   and d.state_desc = 'online'   
   open c1
   fetch next from c1 into @dbname, @logfile, @recovery_model
   While @@fetch_status <> -1
      begin

      print '----- OPERATIONS FOR: ' + @dbname + ' ------'

      print 'CURRENT MODEL IS: ' + @recovery_model

      select @isql = 'ALTER DATABASE ' + @dbname + ' SET RECOVERY SIMPLE'
      print @isql
      exec(@isql)
      select @isql='USE ' + @dbname + ' checkpoint'
      print @isql
      exec(@isql)
      select @isql='USE ' + @dbname + ' DBCC SHRINKFILE (' + @logfile + ', 1)'
      print @isql
      exec(@isql)
      select @isql = 'ALTER DATABASE ' + @dbname + ' SET RECOVERY ' + @recovery_model
      print @isql
      exec(@isql)

      fetch next from c1 into @dbname, @logfile, @recovery_model
      end
   close c1
   deallocate c1

This is an improved code, based on: https://www.sqlservercentral.com/Forums/Topic1163961-357-1.aspx

I recommend reading this article: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server

Sometimes it is worthwhile to permanently enable RECOVERY MODEL = SIMPLE on some databases and thus once and for all get rid of log problems. Especially when we backup data (or server) daily and daytime changes are not critical from a security point of view.