Jack Corbett (12/22/2009)

---

If you are using the current version of the script you should be getting data from ALL the trace files. The T.path column will only return the path of the current file which is why there is a CASE statement setting up the first parameter in the call to fn_trace_gettable. T.path comes form the sys.traces table so it only has the current file name.

charlesd (4/2/2013)

---

The script is very useful. I know it was designed for SQL 2005, however it also seems to work on SQL 2008. I have tried it on several SQL 2008 servers successfully, but I am getting an error on one of them and I'm not sure exactly why. SQL Server would be running on a Windows 2008 R2 Virtual Server. Here is the error:
Server: Msg 567, Level 16, State 7, Line 1
File 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MSSQL10.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.
This is definitely a valid file on the server. I am able to open it directly with SQL Profiler, but not using the script from a query window in SSMS.

Thanks!

sys.fn_trace_gettable(CASE WHEN PATINDEX('%Log[_]%', path)<> 0
                                                   THEN LEFT(path, PATINDEX('%Log[_]%', path) + 2) +
                                                        '.trc'
                                                   ELSE T.[path]
                                              END, T.max_files)

marie contencin (4/4/2013)

---

Hi,
I do not like "NULL" value in results.
May I suggest this :

...
CASE
when S.type_desc is NULL then 'allowed via a Windows Group'
else S.type_desc
end as type_desc,
...

Jack Corbett (4/9/2013)

---

Charles,

Sorry for the delayed reply. This version of the script actually doesn't work on 2008 R2 because MS changed the path to the trace file a bit so the CASE that creates the path doesn't work correctly. Here's what you need to change for R2:

sys.fn_trace_gettable(CASE WHEN PATINDEX('%Log[_]%', path)<> 0
                                                   THEN LEFT(path, PATINDEX('%Log[_]%', path) + 2) +
                                                        '.trc'
                                                   ELSE T.[path]
                                              END, T.max_files)