T-SQL – Checking Database and Log file Location

T-SQL – Checking Database and Log file Location

Using the below script you can easily find location details of SQL Server Databases and Logs.

SELECT name, physical_name AS current_file_location

FROM sys.master_files

 

Also, you can use the below script to get more details like filesize, freespace, space used and etc. I am trying to pull information for temdb database. If you want to pull information of any other database, then change the database name in the script.

USE tempdb
SELECT
a.FILEID,
CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) as [FILESIZEINMB] ,
CONVERT(decimal(12,2),ROUND(fileproperty(a.name,’SpaceUsed’)/128.000,2)) as [SPACEUSEDINMB],
CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)) as [FREESPACEINMB],
a.name as [DATABASENAME],
a.FILENAME as [FILENAME]

FROM
dbo.sysfiles a

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s