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)
go

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 a.name, b.name as ‘Logical filename’, b.filename from sysdatabases a inner join sysaltfiles b on a.dbid = b.dbid where fileid = 2 

 

 Thanks

Prashant Deshpande

Advertisements

Leave a comment

Filed under Database Server, SQL Server

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