Can I safely delete a log file?

Question

Can I safely delete a log file?

mz1derful

Ten Centuries

Points: 1234
More actions
December 4, 2014 at 11:55 am

#313638

I have an 84 gb file called templog2 that appears not to be used. running select * from sys.database_FILES in tempdb does not produce this file in the results. I do not see this file attached to any other database on the sql server instance. Can I safely assume I can delete this file? It is taking up much needed space. Thank you.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

You should check sys.files for EVERY database on EVERY SQL Server instance that has access to that drive before deleting.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

mz1derful Ten Centuries Points: 1234 More actions · Answer 2

I have checked all the databases and run this query but the file is not in the result set:

SELECT name, physical_name AS current_file_location

FROM sys.master_files order by name

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 3

If you attempt to delete the file and it appears in any way to be locked, then it is in use even if you haven't identified by what yet. Don't attempt to bypass that lock. If you're successful, you're likely to mess up a database pretty severely.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

mz1derful Ten Centuries Points: 1234 More actions · Answer 4

thank you all for your help. I will see what my manager says before I delete anything.

Conor.Lillis SSC Veteran Points: 222 More actions · Answer 5

SQL to check all DBs for the file. Replace the appropriate segment below with your full file path

sp_msforeachdb '

--if (''[?]'' not in (''[master]'',''[model]'',''[tempdb]'',''[msdb]'',''[reportserver]'',''[ReportServerTempDB]''))

BEGIN

use [?] ;

--print ''Working on [?]''

select

convert(varchar(15),@@Servername) as [Server Name],

convert(varchar(40),db_name()) as [Database Name],

convert(varchar(80),filename) as [File Name],

"Size (MB)"= (size/1024)*8

from

sysfiles

where

-- ** CHANGE THIS BIT TO YOUR FILE ** --

filename = ''yourfile.ldf''

END

'

go

There is a chance that the file is from a DB that someone detached and never deleted. Check to see if there is a corresponding MDF file anywhere on the local file system.

Here is how I would approach it :-

1. Create a folder called test on the root of the same drive.

2. Move the file to that folder - if this works it isn't actively in use by a DB. If it does not work use the open file tool from sysinternals to identify what process is actively holding the file open.

Assuming step 2 works OK -

3. Back up the file to an off-server location (tape/other location).

4. Mail the team that the file will be deleted in 1/3/5 days and is backed up to tape X under job Y in case a restore is required.

5. Boom.

Eric M Russell SSC Guru Points: 125562 More actions · Answer 6

Just to be safe, you may want to use a file search tool to locate the database file (assuming it may still exist) related to that log file, even if the database is no longer attached to an instance of SQL Server. For example, if the log file is named "AdventureWorks_log.ldf", then search across all locally attached and network drives for a file named "AdventureWorks*.mdf". Also, search within all "ERRORLOG*" files for references to the database name which may give clues about the history of this log file.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

Conor Lillis (12/5/2014)
SQL to check all DBs for the file. Replace the appropriate segment below with your full file path
sp_msforeachdb '
--if (''[?]'' not in (''[master]'',''[model]'',''[tempdb]'',''[msdb]'',''[reportserver]'',''[ReportServerTempDB]''))
BEGIN
use [?] ;
--print ''Working on [?]''
select
convert(varchar(15),@@Servername) as [Server Name],
convert(varchar(40),db_name()) as [Database Name],
convert(varchar(80),filename) as [File Name],
"Size (MB)"= (size/1024)*8
from
sysfiles
where
-- ** CHANGE THIS BIT TO YOUR FILE ** --
filename = ''yourfile.ldf''
END
'
go
There is a chance that the file is from a DB that someone detached and never deleted. Check to see if there is a corresponding MDF file anywhere on the local file system.
Here is how I would approach it :-
1. Create a folder called test on the root of the same drive.
2. Move the file to that folder - if this works it isn't actively in use by a DB. If it does not work use the open file tool from sysinternals to identify what process is actively holding the file open.
Assuming step 2 works OK -
3. Back up the file to an off-server location (tape/other location).
4. Mail the team that the file will be deleted in 1/3/5 days and is backed up to tape X under job Y in case a restore is required.
5. Boom.

Way more complicated than it needs to be, No need for all that, just query sys.master_files in master as this holds detail of every database file on the instance.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉