May 5, 2008 at 2:17 pm
SQL 2000 sp4
I'm writing a procedure that will load trace files into a table without shutting down the trace. Is there a way to load all files except the file that's still be written to? The ::fn_trace_gettable function simply fails and doesn't return a RC for interrogation. Is it possible to somehow get the file status?
I can make a copy of a file, attempt to delete the original and capture/scan the xp_cmdshell delete output, but I'd hope for a better method.
Any help would be greatly appreciated.
May 5, 2008 at 2:27 pm
If you can figure out wich filenumber it is writing to, you can load it up to the lower filenumber.
Don't use the "default" keyword, because that will try to load all related trc files !
Off course you'll have to modify the recepient table to match the columns of your trace.
e.g.
USE [TraceDD20080416]
GO
if object_id('TRC_DBASOX_20080416') is null
begin
CREATE TABLE [dbo].[TRC_DBASOX_20080416](
RecordNumber bigint not null identity(1,1) primary key,
[TextData] nvarchar(max) NULL,
[BinaryData] [image] NULL,
[DatabaseID] [int] NULL,
[TransactionID] [bigint] NULL,
[LineNumber] [int] NULL,
[NTUserName] [nvarchar](256) NULL,
[NTDomainName] [nvarchar](256) NULL,
[HostName] [nvarchar](256) NULL,
[ClientProcessID] [int] NULL,
[ApplicationName] [nvarchar](256) NULL,
[LoginName] [nvarchar](256) NULL,
[SPID] [int] NULL,
[Duration] [bigint] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[Reads] [bigint] NULL,
[Writes] [bigint] NULL,
[CPU] [int] NULL,
[Permissions] [bigint] NULL,
[Severity] [int] NULL,
[EventSubClass] [int] NULL,
[ObjectID] [int] NULL,
[Success] [int] NULL,
[IndexID] [int] NULL,
[IntegerData] [int] NULL,
[ServerName] [nvarchar](256) NULL,
[EventClass] [int] NULL,
[ObjectType] [int] NULL,
[NestLevel] [int] NULL,
[State] [int] NULL,
[Error] [int] NULL,
[Mode] [int] NULL,
[Handle] [int] NULL,
[ObjectName] [nvarchar](256) NULL,
[DatabaseName] [nvarchar](256) NULL,
[FileName] [nvarchar](256) NULL,
[OwnerName] [nvarchar](256) NULL,
[RoleName] [nvarchar](256) NULL,
[TargetUserName] [nvarchar](256) NULL,
[DBUserName] [nvarchar](256) NULL,
[LoginSid] [image] NULL,
[TargetLoginName] [nvarchar](256) NULL,
[TargetLoginSid] [image] NULL,
[ColumnPermissions] [int] NULL,
[LinkedServerName] [nvarchar](256) NULL,
[ProviderName] [nvarchar](256) NULL,
[MethodName] [nvarchar](256) NULL,
[RowCounts] [bigint] NULL,
[RequestID] [int] NULL,
[XactSequence] [bigint] NULL,
[EventSequence] [bigint] NULL,
[BigintData1] [bigint] NULL,
[BigintData2] [bigint] NULL,
[GUID] [uniqueidentifier] NULL,
[IntegerData2] [int] NULL,
[ObjectID2] [bigint] NULL,
[Type] [int] NULL,
[OwnerID] [int] NULL,
[ParentName] [nvarchar](256) NULL,
[IsSystem] [int] NULL,
[Offset] [int] NULL,
[SourceDatabaseID] [int] NULL,
[SqlHandle] [image] NULL,
[SessionLoginName] [nvarchar](256) NULL,
[PlanHandle] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
end
go
INSERT INTO [dbo].[TRC_DBASOX_20080416] ([TextData], [BinaryData], [DatabaseID], [TransactionID], [LineNumber], [NTUserName], [NTDomainName], [HostName], [ClientProcessID], [ApplicationName], [LoginName], [SPID], [Duration], [StartTime], [EndTime], [Reads], [Writes], [CPU], [Permissions], [Severity], [EventSubClass], [ObjectID], [Success], [IndexID], [IntegerData], [ServerName], [EventClass], [ObjectType], [NestLevel], [State], [Error], [Mode], [Handle], [ObjectName], [DatabaseName], [FileName], [OwnerName], [RoleName], [TargetUserName], [DBUserName], [LoginSid], [TargetLoginName], [TargetLoginSid], [ColumnPermissions], [LinkedServerName], [ProviderName], [MethodName], [RowCounts], [RequestID], [XactSequence], [EventSequence], [BigintData1], [BigintData2], [GUID], [IntegerData2], [ObjectID2], [Type], [OwnerID], [ParentName], [IsSystem], [Offset], [SourceDatabaseID], [SqlHandle], [SessionLoginName], [PlanHandle])
SELECT *
FROM ::fn_trace_gettable('D:\tracedb01\DBASOX_20080206_1325__3388.trc', @Current_FileNo - 1)
GO
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 6, 2008 at 11:08 am
USE [databasename]
GO
SELECT * INTO trace_table FROM ::fn_trace_gettable ('c:\my_trace.trc', default)
This statement will just load trace file data into trace_table you want.
SQL DBA.
May 6, 2008 at 11:09 am
$sanjayattray (5/6/2008)
USE [databasename]GO
SELECT * INTO trace_table FROM ::fn_trace_gettable ('c:\my_trace.trc', default)
This statement will just load trace file data into trace_table you want.
Ooops........I didn't see ALZDBA reply. Its doing the same thing.
SQL DBA.
May 7, 2008 at 9:21 am
thanks for the replies.
I still have the issue of not knowing which trace file is in use. It's possible that for a given trace that there isn't any number suffix.
I found some info on CIM_DataFile and InUseCount, but most have found( and I have as well) that this doesn't return the use count of the file.
I think my best choice is to catch an error when deleting/renaming a file in use and then I'll know to bypass the load of that file.
May 8, 2008 at 12:25 am
Maybe this can help out:
- Beware this uses xp_cmdshell (should only be used by sysadmins)
- yep it uses a cursor, but in this case that's perfect.
- TEST IT - TEST IT
Declare @TraceFilePrefix Nvarchar(245)
Set @TraceFilePrefix = N'ALZDBA'
Declare @TraceFileFolder Nvarchar(245)
Declare @SafeFileFolder Nvarchar(245)
Declare @TraceFileName Nvarchar(245)
-- get active trace(s)
SELECT *
into #tmptraceinfo
FROM :: fn_trace_getinfo(default)
if exists( Select * from #tmptraceinfo )
begin
Select @TraceFileFolder = substring(cast([value] as nvarchar(500)),0, charindex(@TraceFilePrefix, cast([value] as nvarchar(500))))
from #tmptraceinfo
where property = 2 -- 2 = filename
print @TraceFileFolder
Select @SafeFileFolder = 'x:\'
Declare @DOSCmd varchar(5000)
create table #tmpTracefilelist (trcfilename nvarchar(1000) )
set @DOSCmd = 'Dir ' + @TraceFileFolder + @TraceFilePrefix +'*.trc'
Insert into #tmpTracefilelist
exec master..xp_cmdshell @DOSCmd
if exists (select count(*)
from #tmpTracefilelist
where trcfilename like '%.trc'
having count(*) > 1 )
BEGIN
Declare @trcCounter int
Set @trcCounter = 0
select @trcCounter = count(*)
from #tmpTracefilelist
where trcfilename like '%.trc'
Declare @Counter int
Set @Counter = 0
DECLARE csrTrc INSENSITIVE CURSOR FOR
select substring(trcfilename,charindex('@TraceFilePrefix', trcfilename),charindex('.trc', trcfilename) + 4) as TraceFileName
from #tmpTracefilelist
where trcfilename like '%.trc'
order by TraceFileName
OPEN csrTrc
FETCH NEXT FROM csrTrc INTO @TraceFileName
WHILE @@FETCH_STATUS = 0 and @Counter < (@trcCounter - 1)
BEGIN
Set @Counter = @Counter + 1
select @DOSCmd = 'Move "' + @TraceFileFolder +'\'+ @TraceFileName+ '" "' + @SafeFileFolder + '"'
exec master..xp_cmdshell @DOSCmd , no_output
-- read next row
FETCH NEXT FROM csrTrc INTO @TraceFileName
END
-- Cursor afsluiten
CLOSE csrTrc
DEALLOCATE csrTrc
print 'Trace Servicemessage: [' + convert(varchar(15), @Counter) + '] trace file(s) moved to Safe folder.'
END
else
begin
print 'Trace Servicemessage: No trace file(s) to be moved to Safe folder.'
end
print 'No active trace found';
drop table #tmpTracefilelist;
end
drop table #tmptraceinfo;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 7, 2014 at 2:53 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply