?
Solved

Getting the name of the log file

Posted on 2003-03-12
12
Medium Priority
?
213 Views
Last Modified: 2012-05-04
Hi
How can I get the name of the log file of my DB ???

Thanks in advance
0
Comment
Question by:kukiya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 23

Expert Comment

by:adathelad
ID: 8118219
Hi

USE YourDatabase

SELECT name, filename
FROM sysfiles
WHERE LTRIM(RTRIM(filename)) LIKE '%.LDF'

Cheers
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 8118243
Hi kukiya,

kukiya, please cleanup your open questions. Failure to do so is in contravention of the user agreement and may lead to suspension or termination of your account.

Open questions:
03/12/03 http:Q_20547530.html "Getting the name of the log file"
11/04/02 http:Q_20389588.html "SQL Server Connection failure"
08/05/02 http:Q_20333032.html "Timeout expired"
07/17/02 http:Q_20324394.html "Database status =suspect"
07/17/02 http:Q_20324335.html "SQL 2000 "tempdb" database"
07/17/02 http:Q_20324293.html "Sql Connection Failure"
07/17/02 http:Q_20324290.html "Sql Connection Failure"
06/17/02 http:Q_20312707.html "Check Database Connection"
06/03/02 http:Q_20307140.html "select and insert part of the fields"
04/25/02 http:Q_20293488.html "Linked Server"
04/24/02 http:Q_20293398.html "Copy records from one DB to another"
02/13/02 http:Q_20266660.html "Notification before executing a trigger"
01/28/02 http:Q_20260227.html "Trigger"
01/01/02 http:Q_20250416.html "ODBC driver doesnt support dynaset"
12/16/01 http:Q_20246608.html "Delete cascade relations"
11/05/01 http:Q_20226830.html "Modal/Non Modal Forms"
10/24/01 http:Q_20206709.html "Getting the  time from datetime datatype"
09/05/01 http:Q_20178709.html "Urecognized database format"
08/22/01 http:Q_20172246.html "Addnew inserts different data"
08/22/01 http:Q_20172079.html "Delete some records from accsess DB"
08/21/01 http:Q_20171571.html "compact and repair"
08/16/01 http:Q_20169315.html "Creating DAO application"
06/27/01 http:Q_20142254.html "Automatic action"
06/24/01 http:Q_20140982.html "Create Database"
04/17/01 http:Q_20106553.html "Delete last records from SQL Server"
03/15/01 http:Q_20091963.html "Scripting Views"
11/26/00 http:Q_11977218.html "ODBC connection failed"

As for your current question:

Select * from DatabaseName.dbo.sysfiles

Will return a list of the actual filename(s) used for your database, including the main database filename (*.mdf) and the transaction log filename (*.ldf).

Tim Cottee MCSD, MCDBA, CPIM
http://www.timcottee.tk 

Brainbench MVP for Visual Basic
http://www.brainbench.com

Experts-Exchange Advisory Board Member
0
 

Author Comment

by:kukiya
ID: 8118276
And Suppose My database is also a parameter, and I know it only in run time ???
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:kukiya
ID: 8118292
Ok, I should use "USE YourDatabase"

Thanks alot !!!
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8118294
EXECUTE ('SELECT name, filename FROM ' + @DatabaseName + '.dbo.sysfiles WHERE LTRIM(RTRIM(filename)) LIKE '''' + '%.LDF' + '''')

Cheers
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8118301
no problem :)
0
 

Author Comment

by:kukiya
ID: 8118513
How do you definfe  "@DatabaseName" parameter ???
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8118530
If you're running the query in Query Analyser:

DECLARE @DatabaseName VARCHAR(50)
SET @DatabaseName = 'YourDatabase'

Or, if you're running a stored procedure you could pass it in as a parameter:

CREATE PROCEDURE stp_GetLogFileName
     @DatabaseName VARCHAR(50)
AS

EXECUTE ('SELECT name, filename FROM ' + @DatabaseName + '.dbo.sysfiles WHERE LTRIM(RTRIM(filename)) LIKE '''' + '%.LDF' + '''')
GO
0
 

Author Comment

by:kukiya
ID: 8118614
I run my query and got the error:

Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near '%'.
Server: Msg 105, Level 15, State 1, Line 11
Unclosed quotation mark before the character string ' + '')

My Query is:

DECLARE @DatabaseName VARCHAR(50)
SET @DatabaseName = 'MyDB'

EXECUTE ('SELECT name, filename FROM ' + @DatabaseName + '.dbo.sysfiles WHERE LTRIM(RTRIM(filename)) LIKE '''' + '%.LDF' + '''')
0
 
LVL 23

Accepted Solution

by:
adathelad earned 400 total points
ID: 8118632
Sorry, try this:

DECLARE @DatabaseName VARCHAR(50)
SET @DatabaseName = 'MyDB'

EXECUTE ('SELECT name, filename FROM ' + @DatabaseName + '.dbo.sysfiles WHERE LTRIM(RTRIM(filename)) LIKE ' + '''' + '%.LDF' + '''')
0
 

Author Comment

by:kukiya
ID: 8118645
Thank, It worked !!!!
0
 

Author Comment

by:kukiya
ID: 8119113
I wrote the following query

DBCC SHRINKFILE (SELECT name FROM  eden.dbo.sysfiles WHERE LTRIM(RTRIM(filename)) LIKE '%.LDF'  ,100 )

and got the error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.

How can I set the Log File name as a query in SHRINKFILE command ???
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question