December 4, 2014 at 11:55 am
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.
December 4, 2014 at 12:07 pm
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
December 4, 2014 at 12:39 pm
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
December 4, 2014 at 1:02 pm
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 4, 2014 at 2:27 pm
thank you all for your help. I will see what my manager says before I delete anything.
December 5, 2014 at 9:36 am
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.
December 5, 2014 at 9:52 am
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
December 6, 2014 at 12:30 pm
Conor Lillis (12/5/2014)
SQL to check all DBs for the file. Replace the appropriate segment below with your full file pathsp_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" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply