I'm scared to delete log file

Question

Post reply

I'm scared to delete log file

spectra

SSCarpal Tunnel

Points: 4120
More actions
December 11, 2016 at 6:41 am

#316629

Hi,

I have two files i.e .ldf and .mdf in my disk. As we know these are normally SQL Server database log and data files. ......I'm not sure if these files are currently being used by any databases or not or they are simply lying in the system and redundant

How do I check if these files are used by databases or not ?

Basically I'm planning to delete these files to save some space in disk.

Is there any way I can be certain that these files are redundant and in that case I can safely delete these files ?

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

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

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 1

spectra (12/11/2016)

Hi,

I have two files i.e .ldf and .mdf in my disk. As we know these are normally SQL Server database log and data files. ......I'm not sure if these files are currently being used by any databases or not or they are simply lying in the system and redundant

How do I check if these files are used by databases or not ?

Basically I'm planning to delete these files to save some space in disk.

Is there any way I can be certain that these files are redundant and in that case I can safely delete these files ?

Check if the files are in sys.master_files

😎

SELECT



   SMF.*

FROM sys.master_files

Ed Wagner SSC Guru Points: 287015 More actions · Answer 2

Eirikur Eiriksson (12/11/2016)

spectra (12/11/2016)

Hi,

I have two files i.e .ldf and .mdf in my disk. As we know these are normally SQL Server database log and data files. ......I'm not sure if these files are currently being used by any databases or not or they are simply lying in the system and redundant

How do I check if these files are used by databases or not ?

Basically I'm planning to delete these files to save some space in disk.

Is there any way I can be certain that these files are redundant and in that case I can safely delete these files ?

Check if the files are in sys.master_files

😎

SELECT
SMF.* FROM sys.master_files

Adding the alias to the table:

SELECT SMF.*



  FROM sys.master_files SMF;

If the instance is running, you aren't going to be able to delete the LDF because there's a lock on the file. If you have multiple instances on the server, there will be files for the databases on each instance. Hopefully, they're in different directories in the file system.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 3

Ed Wagner (12/11/2016)

Eirikur Eiriksson (12/11/2016)

spectra (12/11/2016)

Hi,

I have two files i.e .ldf and .mdf in my disk. As we know these are normally SQL Server database log and data files. ......I'm not sure if these files are currently being used by any databases or not or they are simply lying in the system and redundant

How do I check if these files are used by databases or not ?

Basically I'm planning to delete these files to save some space in disk.

Is there any way I can be certain that these files are redundant and in that case I can safely delete these files ?

Check if the files are in sys.master_files

😎

SELECT
SMF.* FROM sys.master_files

Adding the alias to the table:

SELECT SMF.*
FROM sys.master_files SMF;

If the instance is running, you aren't going to be able to delete the LDF because there's a lock on the file. If you have multiple instances on the server, there will be files for the databases on each instance. Hopefully, they're in different directories in the file system.

Thanks for the correction Ed, this I what I get for responding on the mobile. 😉

😎

Ed Wagner SSC Guru Points: 287015 More actions · Answer 4

Eirikur Eiriksson (12/11/2016)

Ed Wagner (12/11/2016)

Eirikur Eiriksson (12/11/2016)

spectra (12/11/2016)

Hi,

I have two files i.e .ldf and .mdf in my disk. As we know these are normally SQL Server database log and data files. ......I'm not sure if these files are currently being used by any databases or not or they are simply lying in the system and redundant

How do I check if these files are used by databases or not ?

Basically I'm planning to delete these files to save some space in disk.

Is there any way I can be certain that these files are redundant and in that case I can safely delete these files ?

Check if the files are in sys.master_files

😎

SELECT
SMF.* FROM sys.master_files

Adding the alias to the table:

SELECT SMF.*
FROM sys.master_files SMF;

If the instance is running, you aren't going to be able to delete the LDF because there's a lock on the file. If you have multiple instances on the server, there will be files for the databases on each instance. Hopefully, they're in different directories in the file system.

Thanks for the correction Ed, this I what I get for responding on the mobile. 😉

😎

No problem. You post from a phone??? I'm not so brave. My stupid auto-correct drives me nuts already. If I started trying to post table names, it might just blow up. 😉

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

spectra SSCarpal Tunnel Points: 4120 More actions · Answer 5

spectra

SSCarpal Tunnel

Points: 4120

December 11, 2016 at 8:33 pm

#1917298

I have run that query ...and I checked those files are not present in the returned physical path result.

seems ...I can safely delete those files.

MMartin1 One Orange Chip Points: 27655 More actions · Answer 6

spectra (12/11/2016)

I have run that query ...and I checked those files are not present in the returned physical path result.

seems ...I can safely delete those files.

You may want to archive instead of delete. There may come the day when peopl/someone may ask something like what happened to their data.

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

MMartin1 (12/12/2016)

spectra (12/11/2016)

I have run that query ...and I checked those files are not present in the returned physical path result.

seems ...I can safely delete those files.

You may want to archive instead of delete. There may come the day when peopl/someone may ask something like what happened to their data.

Agreed. Keep them in an archive for a month or two before you delete them.

As a bit of a sidebar, you don't need to check if the files exist in sys.master_files before you try to delete or move them. If SQL Server is using them, the files will be "open" and won't be able to be deleted or moved. Trust the DOS...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions