Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

Getting the name of the log file

Hi
How can I get the name of the log file of my DB ???

Thanks in advance
0
kukiya
Asked:
kukiya
  • 6
  • 5
1 Solution
 
adatheladCommented:
Hi

USE YourDatabase

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

Cheers
0
 
TimCotteeCommented:
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
 
kukiyaAuthor Commented:
And Suppose My database is also a parameter, and I know it only in run time ???
0
Technology Partners: 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!

 
kukiyaAuthor Commented:
Ok, I should use "USE YourDatabase"

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

Cheers
0
 
adatheladCommented:
no problem :)
0
 
kukiyaAuthor Commented:
How do you definfe  "@DatabaseName" parameter ???
0
 
adatheladCommented:
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
 
kukiyaAuthor Commented:
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
 
adatheladCommented:
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
 
kukiyaAuthor Commented:
Thank, It worked !!!!
0
 
kukiyaAuthor Commented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now