Checking For Unused Database Files

Category : Scripts User Rating : 3 Stars      Views : 3 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




It's useful to periodically check if there are any old database files that can be deleted from a SQL Server. Usually database files will be deleted along with the database, however this is not always the case. For instance if a database is set to ‘offline’ before deleting then its data and log files will not be deleted. Some databases will have multiple data and log files so it’s not always obvious which ones are in use (though checking the file time stamp can help).

To get a complete list of all data and log files used by all the databases on a server then run the following query :
SELECT name, physical_name, type_desc
FROM   sys.master_files
If you find a file that’s not listed then it’s a good candidate for deletion.

You can also find out what files are used by the *current* database by running the following query :
SELECT name, physical_name, type_desc
FROM   sys.database_files
The same information can be obtained from SQL Server Management Studio by right clicking the appropriate database and selecting 'Properties' and then 'Files'.
Link back to this article : http://www.sqlmatters.com/Articles/Checking For Unused Database Files.aspx

Keywords

SQL,scripts,files


Comments
Post by ann on Thu 12 May 2016 15:19. Report Inappropriate Post

does not make a correlation with the files on disk.
And that is what you need .

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered