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...
>

Getting rid of too many log files

Hi All
I have 3 datafile & 5 log files for a database, not sure if these LOG files really have any data.
I remember somebody saying there is an way to move the data from these 5 LOG files to a single LOG file & deleting the 4.
Can somebody help me on how to accomplish this.
ThanksSee dbcc shrinkfile with emptyfile.
After this the file can be deleted using alter database
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Getting read only database into useable state.

We have a log shipping pair that has the secondary server in a read only
state. My question is how to I get that server into a useable state if there
are no available transaction logs to restore with recovery?
For example the primary server becomes unavailable and all of the logs that
were shipped to the secondary server have already been restored.
Have a look at sp_change_secondary_role. This is the correct way to do it,
although restoring with recovery would also do the trick.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

getting per case info from 1:n related case and log table fast

I neeed to search through the CASE table and get all records with case
created between two dates. A lot of info is in related tables and the case
table only has the id. So there are quiet some Joins, but that's not the big
problem.
What made my SQL unusable is the fact, that I needed also to look up some
info in the 1:n related LOG table. For example I need to know what support
unit was asked something about a certain case. This unitid is not saved in
the CASE table but I can find it out by looking up a varchar field! (and
doing some string-manipulation) in the LOG table where the log_title (anothe
r
varchar field!) has a certain value. I now do this for every record found in
the CASE table (where caseID is the foreign fey in the LOG table.
But since I have to query some 100-tousend records that gets too slow and I
run into a timeout.
Therefor I need a more performant way of doing this kind of query.
Does anyone have any hints for me.
I have no idea how to rearrange it into one query that could be saved as a
stored procedure where at least two dates and a unitid needed to be variable
s.
Thanks for any hints.To give you a hint on what I need to accomplish here the actual queries.
The base query is like this where the view vAppKometFlowLinesXT could
basically be replaced with the CASES table plus the lookups of the keys in
foreign tables.
SELECT *, category_prefix + CAST(case_id AS Varchar(20)) AS cid
FROM vAppKometFlowLinesXT
WHERE ((case_generated > CONVERT(DATETIME, '2005-12-01', 102)) AND
(case_generated < CONVERT(DATETIME, '2005-12-30 23:59:59', 102)) AND
(owner_unit = 98190)) OR
((case_generated > CONVERT(DATETIME, '2005-12-01',
102)) AND (case_generated < CONVERT(DATETIME, '2005-12-30 23:59:59', 102))
AND
(EXISTS
(SELECT id
FROM vAppKometUnitDownAll
WHERE (start = 98190 AND owner_unit = id)) OR
EXISTS
(SELECT TOP 100 PERCENT log_caseID
FROM tAppKometLog
WHERE (log_userunit = 98190 OR
EXISTS
(SELECT id
FROM
vAppKometUnitDownAll
WHERE
(log_userunit = id))) AND case_id = log_caseID
GROUP BY log_caseID
ORDER BY log_caseID)))
ORDER BY cid
Then I do this 3 queries on all records with the caseID (or cid) as the
log_caseID.
SELECT SUBSTRING(log_text, PATINDEX('%:%', log_text) + 2, LEN(log_text) -
PATINDEX('%:%', log_text)) AS logtextOE, log_userunit
FROM tAppKometLog
WHERE (345414 = log_caseID)
AND (log_title = 'log_questionsent')
ORDER BY tAppKometLog.log_id DESC
SELECT log_ownerunit
FROM tAppKometLog
WHERE (345414 = log_caseID)
AND ((log_userunit = 98190) AND (log_title = 'log_forward'))
ORDER BY tAppKometLog.log_id DESC
SELECT log_userunit
FROM tAppKometLog
WHERE (log_caseID = 345414)
AND ((log_ownerunit = 98190) AND (log_title = 'log_forward'))
ORDER BY tAppKometLog.log_id DESC
I somehow need to get this into a single query.|||"zu" <zu@.discussions.microsoft.com> wrote in message
news:07244D9D-49F6-44B2-B077-79F2F6EE92C1@.microsoft.com...
> To give you a hint on what I need to accomplish here the actual queries.
> The base query is like this where the view vAppKometFlowLinesXT could
> basically be replaced with the CASES table plus the lookups of the keys in
> foreign tables.
> SELECT *, category_prefix + CAST(case_id AS Varchar(20)) AS cid
> FROM vAppKometFlowLinesXT
> WHERE ((case_generated > CONVERT(DATETIME, '2005-12-01', 102)) AND
> (case_generated < CONVERT(DATETIME, '2005-12-30 23:59:59', 102)) AND
> (owner_unit = 98190)) OR
> ((case_generated > CONVERT(DATETIME, '2005-12-01',
> 102)) AND (case_generated < CONVERT(DATETIME, '2005-12-30 23:59:59', 102))
> AND
> (EXISTS
> (SELECT id
> FROM vAppKometUnitDownAll
> WHERE (start = 98190 AND owner_unit = id))
> OR
> EXISTS
> (SELECT TOP 100 PERCENT log_caseID
> FROM tAppKometLog
> WHERE (log_userunit = 98190 OR
> EXISTS
> (SELECT id
> FROM
> vAppKometUnitDownAll
> WHERE
> (log_userunit = id))) AND case_id = log_caseID
> GROUP BY log_caseID
> ORDER BY log_caseID)))
> ORDER BY cid
>
> Then I do this 3 queries on all records with the caseID (or cid) as the
> log_caseID.
>
One thing at a time. First rewriting your query to place matchint parens
either on the same line, or vertically aligned, we get
SELECT *, category_prefix + CAST(case_id AS Varchar(20)) AS cid
FROM vAppKometFlowLinesXT
WHERE
(
(case_generated > CONVERT(DATETIME, '2005-12-01', 102))
AND (case_generated < CONVERT(DATETIME, '2005-12-30 23:59:59', 102))
AND (owner_unit = 98190)
)
OR
(
(case_generated > CONVERT(DATETIME, '2005-12-01', 102))
AND (case_generated < CONVERT(DATETIME, '2005-12-30 23:59:59', 102))
AND(
EXISTS
(
SELECT id
FROM vAppKometUnitDownAll
WHERE (start = 98190 AND owner_unit = id)
)
OR
EXISTS
(
SELECT TOP 100 PERCENT log_caseID
FROM tAppKometLog
WHERE (
log_userunit = 98190
OR
EXISTS
(
SELECT id
FROM vAppKometUnitDownAll
WHERE (log_userunit = id)
)
)
AND case_id = log_caseID
GROUP BY log_caseID
ORDER BY log_caseID
)
)
)
ORDER BY cid
Then factor out the date criteria to a top-level AND, and get rid of the
absurd EXISTS (SELECT TOP 100 PERCENT...
SELECT *, category_prefix + CAST(case_id AS Varchar(20)) AS cid
FROM vAppKometFlowLinesXT
WHERE case_generated > CONVERT(DATETIME, '2005-12-01', 102)
AND case_generated < CONVERT(DATETIME, '2005-12-30 23:59:59', 102)
AND
(
owner_unit = 98190
OR
(
EXISTS
(
SELECT id
FROM vAppKometUnitDownAll
WHERE (start = 98190 AND owner_unit = id)
)
OR
EXISTS
(
SELECT *
FROM tAppKometLog
WHERE (
log_userunit = 98190
OR
EXISTS
(
SELECT id
FROM vAppKometUnitDownAll
WHERE (log_userunit = id)
)
)
AND case_id = log_caseID
)
)
)
ORDER BY cid
Try that. If you need more help, decorate all the columns with table
aliases, and post the table DDL.
David|||thanks
this sounds incredible helpful.
I thought that this EXISTS (SELECT TOP 100... might not be best practice.
It's just the way it was.
I didn't expect such a good answer that quick an will take me time too look
at it next year;-)
My family is waiting since it's already quiet late now.
But thank you. Iappreciate it.|||thanks again
hope you started the new year well and happy!
the query doesn't return all I need which obviously is the 'fault' of the
vAppKometFlowLinesXT view, which in itself doesn't return all fields I need.
I rewrote it so that I get the cols I want (as far as the're easy to get) an
have some
significant aliases. The main customer need, to get 4 new fields which I
need to get out of the log is not yet implemented since it's beyond my
current capacity.
The things are: The query now needs 26 seconds and returns 29357 rows
while it previously only took 16 second and returned 29347 rows.
Of course you need to have to be able to have a look at the table structure
to see the points.
I really don't know how to send the DDL.
I know how to script the database but I think this is not what you'd need.
Just tell me, if you think I get you too busy or am asking too much.
the vAppKometFlowLinesXT old and new look like this below
/* dbo.vAppKometFlowLinesXT */
SELECT dbo.tAppKometCase.case_generated,
dbo.tAppKometCategory.category_prefix, dbo.tAppKometCase.case_id,
dbo.tAppKometCategory.category_langtextKEY,
dbo.tAppKometType.type_langtextKEY, dbo.tAppKometUnit.unit_name,
dbo.tAppKometUnit.unit_description,
dbo.tAppKometUnit.unit_KST, dbo.tAppKometCase.case_provisorisch,
dbo.tAppKometCase.case_solution,
dbo.tAppKometCase.case_count,
tAppKometUnit_1.unit_description AS owner_description,
dbo.tAppKometCase.case_ownerLOGIN,
dbo.tAppKometState.state_langtextKEY,
dbo.vAppKometInlistProblem.casecoords_value AS list3_problem,
dbo.tAppKometUnit.unit_colonel,
tAppKometUnit_1.unit_id AS owner_unit,
dbo.tAppKometCase.case_weighting, tAppKometUnit_1.unit_description AS
supportoe,
dbo.tAppKometCase.case_bemerkungsupport,
dbo.tAppKometCase.case_eskalation, dbo.vAppKometInlist.casecoords_value AS
list1_desc,
dbo.vAppKometInlist.field_name AS list1_name,
dbo.vAppKometInlist2.field_name AS list2_name,
dbo.vAppKometInlist2.casecoords_value AS list2_desc,
dbo.tAppKometCase.case_done, dbo.tAppKometCase.case_timeset,
dbo.tAppKometCase.case_others,
dbo.tAppKometCase.case_resent, dbo.tAppKometCause.cause_langtextKEY AS
ursache_key,
vtAppKometCauseCat.category_langtextKEY AS katkorr,
vAppKometCauseType.type_langtextKEY AS typekorr,
dbo.tAppKometCase.case_sorti,
dbo.tAppKometSorti.sorti_txt AS sortiment,
dbo.vAppKometInlist2.flabel2, dbo.vAppKometInlist.flabel1,
dbo.vAppKometBoss.casecoords_value AS bossnr,
dbo.tAppKometCase.case_stateID, tAppKometUnit_1.unit_name AS owner_unitname,
dbo.tAppKometCase.case_sapnr
FROM dbo.tAppKometCase INNER JOIN
dbo.tAppKometUnit ON dbo.tAppKometCase.case_userUnitID
= dbo.tAppKometUnit.unit_id INNER JOIN
dbo.tAppKometCategory ON
dbo.tAppKometCase.case_categoryID = dbo.tAppKometCategory.category_id INNER
JOIN
dbo.tAppKometType ON dbo.tAppKometCase.case_typeID =
dbo.tAppKometType.type_id INNER JOIN
dbo.tAppKometUnit tAppKometUnit_1 ON
dbo.tAppKometCase.case_owner_unitID = tAppKometUnit_1.unit_id INNER JOIN
dbo.tAppKometState ON dbo.tAppKometCase.case_stateID =
dbo.tAppKometState.state_id INNER JOIN
dbo.tAppKometCategory vtAppKometCauseCat ON
dbo.tAppKometCase.case_causeCategory = vtAppKometCauseCat.category_id INNER
JOIN
dbo.tAppKometType vAppKometCauseType ON
dbo.tAppKometCase.case_causeType = vAppKometCauseType.type_id LEFT OUTER JOI
N
dbo.vAppKometBoss ON dbo.tAppKometCase.case_id =
dbo.vAppKometBoss.casecoords_caseID LEFT OUTER JOIN
dbo.tAppKometSorti ON dbo.tAppKometCase.case_sorti =
dbo.tAppKometSorti.sorti_id LEFT OUTER JOIN
dbo.tAppKometCause ON dbo.tAppKometCase.case_cause =
dbo.tAppKometCause.cause_id LEFT OUTER JOIN
dbo.vAppKometInlist2 ON dbo.tAppKometCase.case_id =
dbo.vAppKometInlist2.casecoords_caseID LEFT OUTER JOIN
dbo.vAppKometInlist ON dbo.tAppKometCase.case_id =
dbo.vAppKometInlist.casecoords_caseID LEFT OUTER JOIN
dbo.vAppKometInlistProblem ON
dbo.tAppKometCase.case_id = dbo.vAppKometInlistProblem.casecoords_caseID
WHERE (dbo.tAppKometCase.case_caseID IS NULL)
/* dbo.vAppKometFlowLinesXT_NEW20051230 */
SELECT tAppKometCase.case_generated AS ErfasstAm,
tAppKometCase.case_done AS ErledigtAm, tAppKometCategory.category_prefix AS
KategoriePrefix,
tAppKometCase.case_id AS Fallnummer,
OwnerUnit.unit_description AS Supportstelle, tAppKometState.state_langtextKE
Y
AS Status,
UserUnit.unit_description AS ErfasstDurch,
tAppKometCase.case_userLOGIN AS ErfasstDurchName, tAppKometCase.case_sapnr A
S
ErfasserSAPNr,
UserUnit.unit_KST AS KST,
tAppKometCategory.category_langtextKEY AS Kategorie,
tAppKometType.type_langtextKEY AS Problemart,
vtAppKometCauseCat.category_langtextKEY AS katkorr,
vAppKometCauseType.type_langtextKEY AS typekorr,
vAppKometBoss.casecoords_value AS bossnr,
vAppKometInlist.field_name AS Flex1Name, vAppKometInlist.casecoords_value AS
Flex1,
vAppKometInlist2.field_name AS Flex2Name,
vAppKometInlist2.casecoords_value AS Flex2,
vAppKometInlistProblem.casecoords_value AS
Problembeschreibung, tAppKometCause.cause_langtextKEY AS Ursache,
tAppKometCase.case_provisorisch AS ProvLoesung,
tAppKometCase.case_solution AS Loesung, tAppKometCase.case_ownerLOGIN AS
Supporter,
tAppKometCase.case_count AS Anz,
vAppKometInlistMangel.casecoords_value AS Mangel,
vAppKometInlistLieferant.casecoords_value AS Lieferant
FROM tAppKometCase INNER JOIN
tAppKometCategory ON tAppKometCase.case_categoryID =
tAppKometCategory.category_id INNER JOIN
tAppKometType ON tAppKometCase.case_typeID =
tAppKometType.type_id INNER JOIN
tAppKometState ON tAppKometCase.case_stateID =
tAppKometState.state_id INNER JOIN
tAppKometCategory vtAppKometCauseCat ON
tAppKometCase.case_causeCategory = vtAppKometCauseCat.category_id INNER JOIN
tAppKometType vAppKometCauseType ON
tAppKometCase.case_causeType = vAppKometCauseType.type_id LEFT OUTER JOIN
tAppKometUnit UserUnit ON
tAppKometCase.case_userUnitID = UserUnit.unit_id LEFT OUTER JOIN
vAppKometInlistLieferant ON tAppKometCase.case_id =
vAppKometInlistLieferant.casecoords_caseID LEFT OUTER JOIN
vAppKometInlistMangel ON tAppKometCase.case_id =
vAppKometInlistMangel.casecoords_caseID LEFT OUTER JOIN
vAppKometBoss ON tAppKometCase.case_id =
vAppKometBoss.casecoords_caseID LEFT OUTER JOIN
tAppKometCause ON tAppKometCase.case_cause =
tAppKometCause.cause_id LEFT OUTER JOIN
vAppKometInlist2 ON tAppKometCase.case_id =
vAppKometInlist2.casecoords_caseID LEFT OUTER JOIN
vAppKometInlist ON tAppKometCase.case_id =
vAppKometInlist.casecoords_caseID LEFT OUTER JOIN
vAppKometInlistProblem ON tAppKometCase.case_id =
vAppKometInlistProblem.casecoords_caseID LEFT OUTER JOIN
tAppKometUnit OwnerUnit ON
tAppKometCase.case_owner_unitID = OwnerUnit.unit_id
WHERE (tAppKometCase.case_caseID IS NULL)
the tables case and log have the following structure:
CREATE TABLE [dbo].[tAppKometCase] (
[case_id] [numeric](18, 0) IDENTITY (100000, 1) NOT NULL ,
[case_caseID] [numeric](18, 0) NULL ,
[case_typeID] [int] NOT NULL ,
[case_categoryID] [int] NOT NULL ,
[case_userFullname] [varchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[case_userLOGIN] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[case_userUnitID] [int] NOT NULL ,
[case_ownerLOGIN] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[case_owner_unitID] [int] NOT NULL ,
[case_worker_unitID] [int] NULL ,
[case_workerLogin] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[case_workerconditionID] [int] NULL ,
[case_workerattachement] [varchar] (300) COLLATE Latin1_General_CI_AS NULL ,
[case_count] [int] NOT NULL ,
[case_generated] [datetime] NOT NULL ,
[case_eskalation] [datetime] NULL ,
[case_eskalation_set] [tinyint] NOT NULL ,
[case_stateID] [int] NOT NULL ,
[case_provisorisch] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
[case_attachprovisorisch] [varchar] (300) COLLATE Latin1_General_CI_AS NULL ,
[case_solution] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
[case_attachsolution] [varchar] (300) COLLATE Latin1_General_CI_AS NULL ,
[case_bemerkungsupport] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
[case_reason] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
[case_notconfirmed] [bit] NOT NULL ,
[case_cause] [int] NOT NULL ,
[case_causeType] [int] NOT NULL ,
[case_causeCategory] [int] NOT NULL ,
[case_enterLOGIN] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[case_enterUnitID] [int] NULL ,
[case_rule_set] [tinyint] NOT NULL ,
[case_sapnr] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[case_weighting] [smallint] NULL ,
[case_resent] [bit] NULL ,
[case_others] [bit] NULL ,
[case_timeset] [int] NULL ,
[case_done] [datetime] NULL ,
[case_sorti] [smallint] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tAppKometLog] (
[log_id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[log_title] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[log_desc] [varchar] (300) COLLATE Latin1_General_CI_AS NULL ,
[log_text] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
[log_caseID] [numeric](18, 0) NULL ,
[log_timestamp] [datetime] NULL ,
[log_userLOGIN] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[log_userunit] [int] NULL ,
[log_userFullName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[log_owner] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[log_ownerunit] [int] NULL ,
[log_state] [int] NULL
) ON [PRIMARY]
the rest of the tables involve are basically just foreign key look ups.
If this helps you to help me, that would be great. Otherwise I'd love to
hear what would help even just to get more hints on how to go on myself.

Getting larger drive array

We are planning to install more disk space and need to temporarily move the data and log files and move them back after the disk space has been added.

Is Detach/Attach the best way to handle this?

You have to detach the database and then copy the .bak files to the directory and then attach it.

Steve Russell, MCDBA

|||

Steve,

Thanks for the response. What will I need the .Bak file for? I thought I would just be working with the .mdf & .ldf files. In other words, I was planning to do a Detach, then move the .mdf and .ldf to another server, uninstall the current 30 GB drives then restall 200 GB drives and finally Attach the Db files...

Will this work or am I missing something?

David

|||Your absolutely correct, my bad; the .ldf and .mdf files.

Getting information out of a transaction that rollsback

I have a client who is logging diagnostic information in the midst of lengthy, nested transactions, and finds that the log informaiton disappears when the outer transaction is rolled back. What he wants to do is essentially defeat the transaction, and get records added to a table from within a transaction that don't participate in the transaction.

I've thought about using an Extended Stored Proc, or an RPC with SET_REMOTE_PROC_TRANSACTIONS set to "OFF", or trying to create a linked server and defeat the distributed transaction system, but before I set out building and testing I'd like to ask the experts how to purposefully get data out of a transaction.

Yes, there are work-arounds possible within the T_SQL Procs, we've talked about loading the debug information into a table var which is passed around until all the transactions are cleared and then is logged, but he doesn't hant to go back and revise hundreds of procs. We're hoping to adjust the single logging function to make a "transaction-proof" INSERT.

Any ideas?

GA Dean

You should be able to write a .NET CLR procedure to do this.

When you create the SqlConnection object in the procedure, do not specify Context Connection=true. Instead, create a "real" connection to the database. Such a connection will not be a part of the ambient transaction.

I do not believe that there is any straight T-SQL way to do this, but since you interested in extended SP's, this technique should make sense to you as well, given MS's recommendation to move away frop extended procs in favor of SQLCLR.

Look up "Regular vs. Context Connections" in Books Online for more information.

|||Thanks, I'll give it a try. Reading the docs it was unclear if we would be able to create a regular connection from a CLR called from within an existing transaction.|||I'm not sure that will work. A context connection runs within the same transaction space as the regular connection. It sounds like you're doing two-phase commits if you have what you called an inner and an outer transaction. An alternative would be to start a different transaction, using a different regular connection, for the logging phase although data from the "production" transaction would have to be explicitly passed to the "logging" transaction. Of course, I'm not sure if that will work either.
|||

GA Dean,

I believe that the easy way to capture logging information into a table variable (you mentioned alreay) while in the transaction.

Thanks.

Naras.

|||A transaction by definition is all or nothing. If it gets rolled back, it never happened. There is no evidence of it, except in the transaction log itself. You cannot do "just part of a transaction". EVERYTHING within the transaction gets rolled back on failure. If you log to another table, that other table will be rolled back also, because it is within the transaction scope.

There is no way to get what you want, except to write a text file with the diagnostic info. You could write out to a file, and then bcp the log file into a table after the transaction is done.

Getting information out of a transaction that rollsback

I have a client who is logging diagnostic information in the midst of lengthy, nested transactions, and finds that the log informaiton disappears when the outer transaction is rolled back. What he wants to do is essentially defeat the transaction, and get records added to a table from within a transaction that don't participate in the transaction.

I've thought about using an Extended Stored Proc, or an RPC with SET_REMOTE_PROC_TRANSACTIONS set to "OFF", or trying to create a linked server and defeat the distributed transaction system, but before I set out building and testing I'd like to ask the experts how to purposefully get data out of a transaction.

Yes, there are work-arounds possible within the T_SQL Procs, we've talked about loading the debug information into a table var which is passed around until all the transactions are cleared and then is logged, but he doesn't hant to go back and revise hundreds of procs. We're hoping to adjust the single logging function to make a "transaction-proof" INSERT.

Any ideas?

GA Dean

You should be able to write a .NET CLR procedure to do this.

When you create the SqlConnection object in the procedure, do not specify Context Connection=true. Instead, create a "real" connection to the database. Such a connection will not be a part of the ambient transaction.

I do not believe that there is any straight T-SQL way to do this, but since you interested in extended SP's, this technique should make sense to you as well, given MS's recommendation to move away frop extended procs in favor of SQLCLR.

Look up "Regular vs. Context Connections" in Books Online for more information.

|||Thanks, I'll give it a try. Reading the docs it was unclear if we would be able to create a regular connection from a CLR called from within an existing transaction.|||I'm not sure that will work. A context connection runs within the same transaction space as the regular connection. It sounds like you're doing two-phase commits if you have what you called an inner and an outer transaction. An alternative would be to start a different transaction, using a different regular connection, for the logging phase although data from the "production" transaction would have to be explicitly passed to the "logging" transaction. Of course, I'm not sure if that will work either.
|||

GA Dean,

I believe that the easy way to capture logging information into a table variable (you mentioned alreay) while in the transaction.

Thanks.

Naras.

|||A transaction by definition is all or nothing. If it gets rolled back, it never happened. There is no evidence of it, except in the transaction log itself. You cannot do "just part of a transaction". EVERYTHING within the transaction gets rolled back on failure. If you log to another table, that other table will be rolled back also, because it is within the transaction scope.

There is no way to get what you want, except to write a text file with the diagnostic info. You could write out to a file, and then bcp the log file into a table after the transaction is done.

Getting full security log

I'm administering serveral sqlserversin our company.
Most off the servers are in different domains. For the Enterprise Manager
that's no problem, you can register any server with it's ipnumber or name.
But on all the servers which are not in the same domain, it's security log
is getting full with the audit failure entry's.
How can I avoid this ? Is the enterprise manager doing something 'under
water' ?
Thanks in advance,
T. LeerIt could just be the polling of the servers from Enterprise
Manage so you can try turning that off. In Enterprise
Manager, go to the menu to Tools, select Options. On the
General Tab, remove the check for Server State Polling.
-Sue
On Tue, 19 Oct 2004 13:21:43 +0200, "Ton"
<t.leer@.wanadoo.nl> wrote:

>I'm administering serveral sqlserversin our company.
>Most off the servers are in different domains. For the Enterprise Manager
>that's no problem, you can register any server with it's ipnumber or name.
>But on all the servers which are not in the same domain, it's security log
>is getting full with the audit failure entry's.
>How can I avoid this ? Is the enterprise manager doing something 'under
>water' ?
>Thanks in advance,
>T. Leer
>

Getting full security log

I'm administering serveral sqlserversin our company.
Most off the servers are in different domains. For the Enterprise Manager
that's no problem, you can register any server with it's ipnumber or name.
But on all the servers which are not in the same domain, it's security log
is getting full with the audit failure entry's.
How can I avoid this ? Is the enterprise manager doing something 'under
water' ?
Thanks in advance,
T. Leer
It could just be the polling of the servers from Enterprise
Manage so you can try turning that off. In Enterprise
Manager, go to the menu to Tools, select Options. On the
General Tab, remove the check for Server State Polling.
-Sue
On Tue, 19 Oct 2004 13:21:43 +0200, "Ton"
<t.leer@.wanadoo.nl> wrote:

>I'm administering serveral sqlserversin our company.
>Most off the servers are in different domains. For the Enterprise Manager
>that's no problem, you can register any server with it's ipnumber or name.
>But on all the servers which are not in the same domain, it's security log
>is getting full with the audit failure entry's.
>How can I avoid this ? Is the enterprise manager doing something 'under
>water' ?
>Thanks in advance,
>T. Leer
>

Getting executed query details from MSSQL Log

Hi all,
plz consider this in urgent manner. Let me tell the situation. Someone
executed a query on a table around 5 hrs before. Is it possibel to get to
know that what query and from where and when it happened. Will the .ldf file
reader helps me in this ' ... plz iam waiting for the reply...
thanks in advance
--
Deepson ThomasGoogle for "dbcc log".
Understanding the SQL Server Transaction Log
http://www.windowsitlibrary.com/Content/77/21/1.html
AMB
"Deepson Thomas" wrote:
> Hi all,
> plz consider this in urgent manner. Let me tell the situation. Someone
> executed a query on a table around 5 hrs before. Is it possibel to get to
> know that what query and from where and when it happened. Will the .ldf file
> reader helps me in this ' ... plz iam waiting for the reply...
> thanks in advance
> --
> Deepson Thomas|||> plz consider this in urgent manner. Let me tell the situation. Someone
> executed a query on a table around 5 hrs before. Is it possibel to get to
> know that what query and from where and when it happened. Will the .ldf
> file
> reader helps me in this ' ... plz iam waiting for the reply...
If the log is not cleared, Lumigent Log Explorer or some other log reader
could help you (http://www.lumigent.com/products/le_sql.html).
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Getting executed query details from MSSQL Log

Hi all,
plz consider this in urgent manner. Let me tell the situation. Someone
executed a query on a table around 5 hrs before. Is it possibel to get to
know that what query and from where and when it happened. Will the .ldf file
reader helps me in this ? ... plz iam waiting for the reply...
thanks in advance
Deepson Thomas
Google for "dbcc log".
Understanding the SQL Server Transaction Log
http://www.windowsitlibrary.com/Content/77/21/1.html
AMB
"Deepson Thomas" wrote:

> Hi all,
> plz consider this in urgent manner. Let me tell the situation. Someone
> executed a query on a table around 5 hrs before. Is it possibel to get to
> know that what query and from where and when it happened. Will the .ldf file
> reader helps me in this ? ... plz iam waiting for the reply...
> thanks in advance
> --
> Deepson Thomas
|||> plz consider this in urgent manner. Let me tell the situation. Someone
> executed a query on a table around 5 hrs before. Is it possibel to get to
> know that what query and from where and when it happened. Will the .ldf
> file
> reader helps me in this ? ... plz iam waiting for the reply...
If the log is not cleared, Lumigent Log Explorer or some other log reader
could help you (http://www.lumigent.com/products/le_sql.html).
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Getting executed query details from MSSQL Log

Hi all,
plz consider this in urgent manner. Let me tell the situation. Someone
executed a query on a table around 5 hrs before. Is it possibel to get to
know that what query and from where and when it happened. Will the .ldf file
reader helps me in this ' ... plz iam waiting for the reply...
thanks in advance
Deepson ThomasGoogle for "dbcc log".
Understanding the SQL Server Transaction Log
http://www.windowsitlibrary.com/Content/77/21/1.html
AMB
"Deepson Thomas" wrote:

> Hi all,
> plz consider this in urgent manner. Let me tell the situation. Someone
> executed a query on a table around 5 hrs before. Is it possibel to get to
> know that what query and from where and when it happened. Will the .ldf fi
le
> reader helps me in this ' ... plz iam waiting for the reply...
> thanks in advance
> --
> Deepson Thomas|||> plz consider this in urgent manner. Let me tell the situation. Someone
> executed a query on a table around 5 hrs before. Is it possibel to get to
> know that what query and from where and when it happened. Will the .ldf
> file
> reader helps me in this ' ... plz iam waiting for the reply...
If the log is not cleared, Lumigent Log Explorer or some other log reader
could help you (http://www.lumigent.com/products/le_sql.html).
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com