April 21, 2010 at 12:45 am
Hi there
I want to parse .trn files (SQL 2005)
I want to know what SPs has been executed etc by parsing .trn file which i get every 15 minutes from Live DB.
This is to decommission the unused SPs etc. I will parse the file for 2 months and make decisions based on that.
is there a easy way to parse .trn file? any code please
or any 3rd party tools which can do that?
Cheers
April 21, 2010 at 12:56 am
I think that you’ll have to use some third party products such as Lumigent’s Log explorer or Red Gate’s SQL Log Rescue. Another alternative is to create a logging table, and in the beginning of every procedure that you suspect that isn’t being used add an insert statement to this table. By the end of the 2 months you can check which procedure’s name is missing.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 21, 2010 at 1:05 am
Hi,
by joining DMVs and Procedure type objects you may find which SPs has never been executed.
sys.sysobjects-- xtype =P
sys.dm_exec_query_stats --execution_count
dm_exec_sql_text --objectid
Regards,
MShenel
April 21, 2010 at 1:30 am
shen-dest (4/21/2010)
Hi,by joining DMVs and Procedure type objects you may find which SPs has never been executed.
sys.sysobjects-- xtype =P
sys.dm_exec_query_stats --execution_count
dm_exec_sql_text --objectid
Take into consideration that the query plans go in and out the cache. Also some times that don’t even go into the cache (for example if the procedure was created or invoked with recompile, or if it has a trivial plan). Since the original poster wants to check if those procedures are being used at all, I would also guess that those procedures are hardly used and this will also add to the difficulties to check the cache at the time that those procedure’s plan are in the cache.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 21, 2010 at 1:47 am
shen-dest (4/21/2010)
sys.dm_exec_query_stats --execution_count
Bear in mind that that DMV is flushed when the DB is closed, queries can be removed from cache and it is possible they'll never appear in there in the first place.
http://sqlinthewild.co.za/index.php/2009/07/10/why-the-dmvs-are-not-a-replacement-for-sql-trace/
I'd honestly recommend SQL trace here, not an expensive log reader.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2010 at 2:09 am
Hi,
If DB goes down so frequently DMVs will not be helpful. for a high-loaded system profiler might be a problem.
Regards,
MShenel
April 21, 2010 at 2:16 am
shen-dest (4/21/2010)
for a high-loaded system profiler might be a problem.
Server side trace to fast local drive is usually ok even on heavily loaded systems. Profiler GUI, not so much.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2010 at 2:22 am
Adi Cohn-120898 (4/21/2010)
I think that you’ll have to use some third party products such as Lumigent’s Log explorer or Red Gate’s SQL Log Rescue.
Log rescue doesn't work on SQL 2005. It's a 2000-only tool. Other than Lumigent, there's one from ApexSQL as well. Most range around $1000 per licence, so they're not cheap.
However...
The transaction log contains changes to data, not stored procedure calls. If you're looking for what data changed, that's in the transaction log. The names of the procedures that made the changes however is not. It's not necessary information for DB integrity/recovery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply