Getting the physical directory that the log file is located

I am trying to programatically determine the directory of the log files in
SQL Server.
I am using
SELECT Filename FROM master.dbo.sysdatabases
to get the data directory, now I need to find the log files.
Thanks,
RSHmaster..sysaltfiles or dbname..sysfiles
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RSH" <way_beyond_oops@.yahoo.com> wrote in message news:eFWU7uc%23FHA.3064@.TK2MSFTNGP10.phx
.gbl...
>I am trying to programatically determine the directory of the log files in
> SQL Server.
> I am using
> SELECT Filename FROM master.dbo.sysdatabases
> to get the data directory, now I need to find the log files.
>
> Thanks,
> RSH
>|||There are some issues with this - like suppose you multiple data files
and/or multiple log files.
However, assuming one data file and one log file, then the file that is
not the data file is the log file...
SELECT filename
FROM sysfiles
WHERE filename NOT IN
(SELECT filename FROM master..sysdatabases WHERE name = db_name())
Payson
RSH wrote:
> I am trying to programatically determine the directory of the log files in
> SQL Server.
> I am using
> SELECT Filename FROM master.dbo.sysdatabases
> to get the data directory, now I need to find the log files.
>
> Thanks,
> RSH|||In SQL Server 2000, you can use master.dbo.sysaltfiles to return both the
data and log file directories.
In SQL Server 2005, you should use the sys.master_files catalog view
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:eFWU7uc%23FHA.3064@.TK2MSFTNGP10.phx.gbl...
>I am trying to programatically determine the directory of the log files in
>SQL Server.
> I am using
> SELECT Filename FROM master.dbo.sysdatabases
> to get the data directory, now I need to find the log files.
>
> Thanks,
> RSH
>|||Thanks...I just realized however that when our SA installed SQL Server he
set it up so the Log files were stored in the same directory as the
Datafiles. he then later changed his mind and set it up to store the
datafiles seperate from the logfiles. The problem is that in doing so the
Master database (which I was using to find the directories of the files to
use in several stored procs) still has both files in the same directory. Is
there a way to find out where the Log files and where the Datafiles are set
to be created in programatically? In other words can I query a sys table to
find where SQL Server will by default place the Log and the Data files when
a new database is being created?
Thanks,
RSH
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23gcUrzc%23FHA.3064@.TK2MSFTNGP10.phx.gbl...
> master..sysaltfiles or dbname..sysfiles
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:eFWU7uc%23FHA.3064@.TK2MSFTNGP10.phx.gbl...|||I realize that but our SA has set it up so all of the log files are written
to directory A and the data files are written to direcory B.
I have the query below which indeed returns the location of the Log and Data
file for the Master DB which is not the problem. The problem is that the
Master database was created when we installed SQL Server. Since then we
went back and set it up so that the MDFs are written in one directory and
the LDFs are written to another. ALL of the databases that exist on the
server have both their LDF and MDF in the same directory. The problem is
that I have a conversion application that is about to convert thousands of
databases and I need to get the data files in the data directory and the log
files in the log directory but since all of the databases on that server
have both the logs and datafiles in the same directory i need a way to find
out the preference setting for SQL server to find out which directory to
correctly write the appropriate filetype.
Thanks,
Ron
"Payson" <payson_b@.hotmail.com> wrote in message
news:1133808837.723905.12280@.g44g2000cwa.googlegroups.com...
> There are some issues with this - like suppose you multiple data files
> and/or multiple log files.
> However, assuming one data file and one log file, then the file that is
> not the data file is the log file...
> SELECT filename
> FROM sysfiles
> WHERE filename NOT IN
> (SELECT filename FROM master..sysdatabases WHERE name = db_name())
> Payson
>
> RSH wrote:
>|||Not sure I understand your problem completely, but maybe this will
help.
The undocumented procedure sp_msforeachdb will loop through the
databases
sp_msforeachdb 'use ? SELECT ''?'' as dbname, filename FROM
sysfiles'
The status column in sysfiles will, when decoded, tell you whether the
file is a data file or a log file - see sysfiles in BOL.
Good luck.
Payson
RSH wrote:
> I realize that but our SA has set it up so all of the log files are writte
n
> to directory A and the data files are written to direcory B.
> I have the query below which indeed returns the location of the Log and Da
ta
> file for the Master DB which is not the problem. The problem is that the
> Master database was created when we installed SQL Server. Since then we
> went back and set it up so that the MDFs are written in one directory and
> the LDFs are written to another. ALL of the databases that exist on the
> server have both their LDF and MDF in the same directory. The problem is
> that I have a conversion application that is about to convert thousands of
> databases and I need to get the data files in the data directory and the l
og
> files in the log directory but since all of the databases on that server
> have both the logs and datafiles in the same directory i need a way to fin
d
> out the preference setting for SQL server to find out which directory to
> correctly write the appropriate filetype.
> Thanks,
> Ron
>
> "Payson" <payson_b@.hotmail.com> wrote in message
> news:1133808837.723905.12280@.g44g2000cwa.googlegroups.com...|||Let me word this a bit differently...Is there anyway to query the
preferences to find out where SQL Server is setup to write the Log files,
and datafiles.
I can't simply query the tables because the system databases were setup when
SQL Server was setup to write both filetypes to the same directory. Now he
has changed the directories so that each is in its own directory but since
all of the databases on the server already exist, querying the table brings
back the same data directory.
EXAMPLE:
Preferences:
Physical Directories When SQL Server was installed:
DATA: C:\Datafiles
LOGS: C:\Datafiles
The systable query returns this value when pointed at the master database:
datafile location: C:\Datafiles\master.mdf
log file location: C:\Datafiles\masterlog.ldf
NOW... The Sys Admin changed the SQL Server preferences to write datafiles
to a seperate directory than the log files:
Preferences:
DATA: L:\Datafiles
LOGS: M:\Logfiles
BUT... because SQL was installed initially with the datafiles and log files
written to the same directory, all of the databases still have their files
in the original locations which is a problem...my data conversion
application queries the systables to find the directories where the log and
data files are so I can pass that information in the create database query
when setting up the database.
The systable query returns this value when pointed at the master
database: --
datafile location: C:\Datafiles\master.mdf
log file location: C:\Datafiles\masterlog.ldf
Since none of the databases reflect the preferences setting in SQL Server
they are being written to the C:\Datafiles directory instead of being split
to the L:\Datafiles, and M:\Logfiles respectively.
"Payson" <payson_b@.hotmail.com> wrote in message
news:1133812823.499247.50450@.g43g2000cwa.googlegroups.com...
> Not sure I understand your problem completely, but maybe this will
> help.
> The undocumented procedure sp_msforeachdb will loop through the
> databases
> sp_msforeachdb 'use ? SELECT ''?'' as dbname, filename FROM
> sysfiles'
> The status column in sysfiles will, when decoded, tell you whether the
> file is a data file or a log file - see sysfiles in BOL.
> Good luck.
> Payson
> RSH wrote:
>|||Gonna try once more. I am using sysfiles in my query, which is local
to each database - hence the USE. I think sysfiles (the one in a given
database, not the one in master) will tell you the actual files for
that database. And I think the status column, when anded with 64, will
tell you whether or not this particular file is a log file.
I think.
If not, I don't have a clue.
Best of luck.
Payson
RSH wrote:
> Let me word this a bit differently...Is there anyway to query the
> preferences to find out where SQL Server is setup to write the Log files,
> and datafiles.
> I can't simply query the tables because the system databases were setup wh
en
> SQL Server was setup to write both filetypes to the same directory. Now h
e
> has changed the directories so that each is in its own directory but since
> all of the databases on the server already exist, querying the table bring
s
> back the same data directory.
> EXAMPLE:
> Preferences:
> Physical Directories When SQL Server was installed:
> DATA: C:\Datafiles
> LOGS: C:\Datafiles
> The systable query returns this value when pointed at the master database:
> datafile location: C:\Datafiles\master.mdf
> log file location: C:\Datafiles\masterlog.ldf
>
> NOW... The Sys Admin changed the SQL Server preferences to write datafiles
> to a seperate directory than the log files:
> Preferences:
> DATA: L:\Datafiles
> LOGS: M:\Logfiles
>
> BUT... because SQL was installed initially with the datafiles and log file
s
> written to the same directory, all of the databases still have their files
> in the original locations which is a problem...my data conversion
> application queries the systables to find the directories where the log an
d
> data files are so I can pass that information in the create database query
> when setting up the database.
> The systable query returns this value when pointed at the master
> database: --
> datafile location: C:\Datafiles\master.mdf
> log file location: C:\Datafiles\masterlog.ldf
> Since none of the databases reflect the preferences setting in SQL Server
> they are being written to the C:\Datafiles directory instead of being spli
t
> to the L:\Datafiles, and M:\Logfiles respectively.
>
>
> "Payson" <payson_b@.hotmail.com> wrote in message
> news:1133812823.499247.50450@.g43g2000cwa.googlegroups.com...|||> In other words can I query a sys table to find where SQL Server will by default place the
Log and
> the Data files when a new database is being created?
It is a bit more involved than that. You can define in EM default path for d
ata and log files. This
is stored in the registry at (for a default instance):
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer
In the entries "DefaultData" and "DefaultLog".
However, if you didn't set these, I believe that the path where the master d
atabase is will act as
the default path (you need to do some testing on this).
You can read the registry with TSQL using xp_instance_regread. I don't think
it is documented so use
at own risk, Google for usage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RSH" <way_beyond_oops@.yahoo.com> wrote in message news:%23LzIp3c%23FHA.600@.tk2msftngp13.ph
x.gbl...
> Thanks...I just realized however that when our SA installed SQL Server he
set it up so the Log
> files were stored in the same directory as the Datafiles. he then later c
hanged his mind and set
> it up to store the datafiles seperate from the logfiles. The problem is t
hat in doing so the
> Master database (which I was using to find the directories of the files to
use in several stored
> procs) still has both files in the same directory. Is there a way to find
out where the Log files
> and where the Datafiles are set to be created in programatically? In othe
r words can I query a
> sys table to find where SQL Server will by default place the Log and the D
ata files when a new
> database is being created?
> Thanks,
> RSH
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23gcUrzc%23FHA.3064@.TK2MSFTNGP10.phx.gbl...
>