Get the Logical File name of SQL server Databases

Dear All

As there was a requirement of shrinking of LOG files  in SQL server and many times we just copy the old Database with new name and we never look for logical file of log file which is required while shrinking teh log file.

Syntax of Shrink database log file is

use Databasename
backup log Databasename with truncate_only
dbcc shrinkfile(“Logical File name “,1)

As we don’t know the Logocal File name the following command will help us to get the logical file names of all databases present in SQL server

Open Query Analyser and type

Use Master

select, as ‘Logical filename’, b.filename from sysdatabases a inner join sysaltfiles b on a.dbid = b.dbid where fileid = 2 



Prashant Deshpande


Leave a Reply

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

You are commenting using your 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