SQL Profiler Temp Space

[Database Administrator]

I'm attempting to run some traces using profiler under SQL2000.

I've noticed that it appears to use some temp space out on the C drive for
some of its operations which is causing some space issues for the underlying
O/S. The trace is running on one server as client against a a database
located on another SQL2000 server.

Is there a way to re point this temp region to a different drive that
contains more space?

[Database Administrator]

I'm pretty certain that Profiler temp space is hardwired to the OS
temp folder (Google and you should find out for sure of you need). You
can change this (OS setting). Or perhaps run server-side traces
instead? They also have a lower overhead compared to client-side
traces...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Tom Frost" wrote in message
news:F1ED71D5-B925-4D3D-9DBC-913D6F6AAAAB@microsoft.com...
> I'm attempting to run some traces using profiler under SQL2000.
>
> I've noticed that it appears to use some temp space out on the C
> drive for
> some of its operations which is causing some space issues for the
> underlying
> O/S. The trace is running on one server as client against a a
> database
> located on another SQL2000 server.
>
> Is there a way to re point this temp region to a different drive
> that
> contains more space?

[Database Administrator]

Tom Frost (frosty@nospam.nospam) writes:
> I'm attempting to run some traces using profiler under SQL2000.
>
> I've noticed that it appears to use some temp space out on the C drive
> for some of its operations which is causing some space issues for the
> underlying O/S. The trace is running on one server as client against a
> a database located on another SQL2000 server.
>
> Is there a way to re point this temp region to a different drive that
> contains more space?

That would be one of TEMP or TMP environment variables. You can change
these from the System applet in Control Panel. It's under the Advanced tab.

But I would recommend that you invest time in learning about server-side
traces, as they take far less load of the traced server. Getting started
is easier than you think. Set up your trace in Profiler and then select
the Export option from the File menu.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Database Administrator]

Hi Customer,

This is Mark. I'm gald to assist you with the issue.

in order to resolve the issue, I would like to explain the following.
A SQL Server Profiler uses space in the temp directory to improve access to
trace data. SQL Server Profiler requires at least 10 megabytes (MB) of free
space. If free space drops below 10 MB while you are using SQL Server
Profiler, all SQL Server Profiler functions stop. That might be the root
cause of the issue

B When SQL Server Profiler uses space in the temp directory, this space
usage may cause the temp directory to grow rapidly. To avoid file-growth
problems, you can place the temp directory on a drive that is not a system
drive by changing the value for the TEMP environment variable. It might be
the solution to the issue.

C For your convenience, I list the detail steps to change the temp
enviroment variable here.
1 On the Start menu, choose Control Panel, and then click System.

2 In the System Properties dialog box, click the Advanced tab, and then
click Environment Variables.

3 Scroll down the list of System Variables, select the row that corresponds
to the TEMP variable, and click Edit.

4 In the Edit System Variable dialog box, enter the path and name of the
drive and directory where you want the temp directory to be located.

5 Click OK to save the change.

Besides, there is an article to share with
you:http://msdn.microsoft.com/en-us/library/ms174203.aspx

Hope the above helpful.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subs.../aa948874.aspx

================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

[Database Administrator]

Hi Customer,

I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

[andriychenko]

Just though I put some contribution for the misguided souls that hit this same problem.

Turns out the msdn article quoted is somewhat incorrect.

You do need to chnage environmental variable, but not the system one, but the user one. To know for definite try to examin first where the Profiler stores its temporary files. On my server it was %USERPROFILE%\Local Settings\Temp ,which is a user temp folder, not the system temp forlder (C:/windows/temp) as in msdn article.

Now guess what, if you change the value to any other location, profiler acknowledges it by creating a subdirectory there, but stubbornly keeps creating the temp files in the old location UNTIL you physically delete the Temp folders in the %USERPROFILE%\Local Settings\ . Then it finally gets the idea.

Figuring this out took me at least n hour, so here's to all others with the same problem

Have a nice day,

Dmytro