Transaction Log Logical File Name

• Theo Jacobs

  Say Hey Kid

  Points: 694

  More actions

  November 15, 2005 at 5:03 am

  #167015

  Hi ppl

  Is there a way to determine the Logical Log File name through sql. At present I use this script to shrink my Transaction logs :

  EXEC sp_dropdevice 'DB_Log_Bkp'

  EXEC sp_addumpdevice 'disk', 'DB_Log_Bkp', 'c:\BACKUPS\DB_Log.TRN'

  BACKUP Log DB TO DB_Log_Bkp

  DBCC SHRINKFILE (DB_Log, 50)

  'DB_Log_Bkp' : The logical name for the backup device for the log

  'c:\BACKUPS\DB_Log.TRN' : The physical filename for the backup of the log

  DB : The database name

  DB_Log : The logical name of the log file to backup.

  Hope there is light at the end of the tunnel.

  Thanx

  Theo

• John Laska

  SSC-Addicted

  Points: 454

  More actions

  November 16, 2005 at 11:05 am

  #604248

  For each database there should be a sysfiles table, that should have the data you are looking for.

  e.g.:

  use northwind

  select

  name,

  filename

  from

  sysfiles

• Rudyx - the Doctor

  SSC-Forever

  Points: 43695

  More actions

  November 16, 2005 at 12:43 pm

  #604275

  There is also the sysaltfiles table in the master database that contains all database files ... here's a quick query:

  select name, filename

  from master..sysaltfiles

  where fileid = 2

  and name = 'your_database_name'

  fileid = 1 --> .mdf files (data)

  fileid = 2 --> .ldf files (log)

   

  Also, here's a link for documentation of the system tables in SQL 2K:

  http://www.microsoft.com/sql/prodinfo/previousversions/systables.mspx

   

  RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

• Theo Jacobs

  Say Hey Kid

  Points: 694

  More actions

  November 17, 2005 at 12:31 am

  #604353

  Thanx to everyone for the help. Much appreciated.

  Thanx especially for the link to the system table map help file. what a wealth of info.

  Great programming to all.

  Theo