April 14, 2009 at 2:41 pm
One of the application uses global temporary table. This global temporary table gets deleted every now and then and application stucks, we have to reboot the application server to make it work. How can i start my investigation about how and what makes this global temporary delete?
Just for the information,this server is the backend of other application also. And one another application exec sp_reset_connections on temp database.
April 14, 2009 at 5:26 pm
If you are not using many temp tables in the code you could use Table Variables instead of Temp tables.
Kind regards,
Gift Peddie
April 15, 2009 at 6:48 am
If you are really on SQL Server 2005 (based on the forum) you could query the Default Trace for the Object:Deleted event limiting the results to tempdb. One thing that would make it even easier would be to get the Object Id of the global temporary table when it is first created so that you will have that as well because you can't get it once it is deleted. The query would look something like this:
USE tempdb
GO
DECLARE @trace_path NVARCHAR(250)
SELECT
@trace_path = T.PATH
FROM
sys.traces AS T
WHERE
T.is_default = 1
-- have to remove the file number from the trace path in order to query all files
SET @trace_path = LEFT(@trace_path, CHARINDEX( '_', @trace_path)-1) + '.trc'
SELECT
TE.[name],
TGT.TextData,
TGT.ApplicationName,
TGT.DatabaseName,
TGT.ObjectID,
TGT.ObjectName,
TGT.ObjectID2,
TGT.ObjectType,
TGT.*
FROM
fn_trace_gettable(@trace_path, default) AS TGT JOIN
sys.trace_events AS TE ON
TGT.EventClass = TE.trace_event_id
WHERE
TGT.DatabaseName = 'tempdb' AND
TE.[name] = 'Object:Deleted'
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2009 at 8:33 am
This is the third party application. I am just DBA administoring the application
April 15, 2009 at 8:39 am
Right you are the DBA and if you are running any version of SQL Server 2005 there is a Default Trace that is installed and started, unless someone manually turned it off. Since someone turning it off is unlikely, you should be able to run the code I provided and get the results.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2009 at 8:44 am
Jack,
I think the person is talking about my solution but I still thinks the vendor of the software should be contacted if their developer wrote crappy code.
Kind regards,
Gift Peddie
April 15, 2009 at 8:46 am
i was able to get the object ID. I will run your script next time when application hangs to find out what deleted the temp table. I have even run the profiler to trace the deleted object. I will let you knwo if i find out anything.
Thanks
April 15, 2009 at 9:01 am
Gift Peddie (4/15/2009)
Jack,I think the person is talking about my solution but I still thinks the vendor of the software should be contacted if their developer wrote crappy code.
Oh yeah, you may be right about who the OP was responding too.
You are also right that the vendor should be contacted. I'd be concerned about using a Global Temp Table regardless. They should also be checking to see if it exists whenever they use it if it is being dropped somewhere.
One last thought, if you start a server-side trace and collect the you can get the Drop Table statement and see the code that did it. I'd trace the SP:Starting, SP:Completed, RPC:Starting, RPC:Completed, and SQL:StmtCompleted events. You may want to add the SP:StmtCompleted event as well to insure you get the Drop Table statement.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2009 at 9:12 am
I am a bit worried about the sp_reset_connection executed by another app. (mentioned in the original post)
According to this: http://www.sqldev.net/misc/sp_reset_connection.htm
all temporary objects will be dropped.
So if there are multiple applications accessing the server and one of them is using a global temp table and another calls sp_reset_connection could that effectively mess up the first application's temp objetcs?
PS. Can not find any Microsoft documentation on sp_reset_connection
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 15, 2009 at 12:47 pm
Old hand,
"So if there are multiple applications accessing the server and one of them is using a global temp table and another calls sp_reset_connection could that effectively mess up the first application's temp objetcs?"
Is this the information or asking question?Little confused.
April 15, 2009 at 1:05 pm
I am just adding my 2 cents to the discussion. I am afraid that if the sp_reset_connection is dropping all temp objects the profiler may not give you an answer because there might not be a DROP issued explicitly.
I just got this strange feeling the problem might be not because one application or another has a bug. But both of them on the same server might cause the problem and if this is the case you kind of out of luck and may have to get another SQL server box. (or have the vendor change the code not to use ##table).
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 15, 2009 at 1:06 pm
from bol:
Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned.
Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).
....
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
Hence.... a crappy solution of your SV.
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
April 15, 2009 at 1:12 pm
The definition of the two types of temp table is not the issue but rather what the code is doing in the application because definition is not relevant SSRS parser will execute most code using global temp table but not local temp table. So it is the context of the temp table use that is relevant.
Kind regards,
Gift Peddie
April 15, 2009 at 1:18 pm
If sys.sp_reset_connection being executed by one application is causing your global temporary table to be dropped for another application, your other choice is to install a separate instance of SQL Server 2005 (a named instance) and configure one of the applications to use the default instance, and the other application to use the named instance.
This will isolate your applications from each other and solve this issue.
April 15, 2009 at 1:30 pm
Lynn Pettis (4/15/2009)
If sys.sp_reset_connection being executed by one application is causing your global temporary table to be dropped for another application
This the big IF. This sp is a strange beast. I tried to exeute it directly in query window to test it's impact on the temp tables but the server claims it does not exist. So who the heck knows what it does....
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply