|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Profiler temp fileIs there any way to move the temp file that is create by Profiler (used for
the datagrid scrolling screen) from the C: drive? Our C drive is not large enough to allow a long Profiler session, since the Profiler temp file ends up filling up the C drive. Nancy Well you really shouldn't be using profiler for such things anyway. The more
efficient way to do this is to create a trace and send the trace events directly to a file on a drive attached to the server. Then you can copy that file to your local machine and view it in Profiler or dump it into a table for querying. If you set up a trace definition in Profiler, run it for a second and then stop it you can go to File - Script trace to generate an almost ready made script for your trace. -- Show quoteAndrew J. Kelly SQL MVP "Nancy Lytle" <nancy_ly***@payformance.com> wrote in message news:O2Gku9YjHHA.1240@TK2MSFTNGP04.phx.gbl... > Is there any way to move the temp file that is create by Profiler (used > for the datagrid scrolling screen) from the C: drive? Our C drive is not > large enough to allow a long Profiler session, since the Profiler temp > file ends up filling up the C drive. > > Nancy > I'm not sure I am getting what you mean.
We need to run a trace for a period of time to cover while some jobs are processing etc, and this could take several hours. We do create a trace and save it to a file or table, but don't you have to leave the Profiler window open? When I tried to close it, it will stop the trace. And it is what is displayed in that window that creates the large temp file on the C drive. And this could be for either SQL 2005 or 2000, we use one in one environment (one division) and the other in another environment (the other division), both are using Windows 2003. Am I missing something here? Nancy Show quote "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:OvYsXLZjHHA.492@TK2MSFTNGP04.phx.gbl... > Well you really shouldn't be using profiler for such things anyway. The > more efficient way to do this is to create a trace and send the trace > events directly to a file on a drive attached to the server. Then you can > copy that file to your local machine and view it in Profiler or dump it > into a table for querying. If you set up a trace definition in Profiler, > run it for a second and then stop it you can go to File - Script trace to > generate an almost ready made script for your trace. > > -- > Andrew J. Kelly SQL MVP > > "Nancy Lytle" <nancy_ly***@payformance.com> wrote in message > news:O2Gku9YjHHA.1240@TK2MSFTNGP04.phx.gbl... >> Is there any way to move the temp file that is create by Profiler (used >> for the datagrid scrolling screen) from the C: drive? Our C drive is not >> large enough to allow a long Profiler session, since the Profiler temp >> file ends up filling up the C drive. >> >> Nancy >> > > Yes you are missing the whole point. Profiler is a gui tool that uses the
output of the internal utility called TRACE. You can output directly from Trace to a file without running profiler at all. Profiler has an enormous overhead (as you found out) and can significantly impact performance while running. You do this via a series of tsql commands not with profiler itself. But you can use Profiler as I mentioned to create the script that has the series of tsql commands to do the actual work. This first link shows how to create a script from scratch but if you follow my instructions fromt he previous post it will make it much easier for you. All you need to do is to fill in the file name it will create. Have a look at these: http://support.microsoft.com/kb/283790 How to create a trace http://msdn2.microsoft.com/en-us/library/ms191006.aspx http://msdn2.microsoft.com/en-us/library/aa173875(SQL.80).aspx -- Show quoteAndrew J. Kelly SQL MVP "Nancy Lytle" <nancy_ly***@payformance.com> wrote in message news:%23FngeUZjHHA.5012@TK2MSFTNGP04.phx.gbl... > I'm not sure I am getting what you mean. > We need to run a trace for a period of time to cover while some jobs are > processing etc, and this could take several hours. We do create a trace > and save it to a file or table, but don't you have to leave the Profiler > window open? When I tried to close it, it will stop the trace. And it is > what is displayed in that window that creates the large temp file on the C > drive. > And this could be for either SQL 2005 or 2000, we use one in one > environment (one division) and the other in another environment (the other > division), both are using Windows 2003. > Am I missing something here? > > Nancy > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:OvYsXLZjHHA.492@TK2MSFTNGP04.phx.gbl... >> Well you really shouldn't be using profiler for such things anyway. The >> more efficient way to do this is to create a trace and send the trace >> events directly to a file on a drive attached to the server. Then you can >> copy that file to your local machine and view it in Profiler or dump it >> into a table for querying. If you set up a trace definition in Profiler, >> run it for a second and then stop it you can go to File - Script trace to >> generate an almost ready made script for your trace. >> >> -- >> Andrew J. Kelly SQL MVP >> >> "Nancy Lytle" <nancy_ly***@payformance.com> wrote in message >> news:O2Gku9YjHHA.1240@TK2MSFTNGP04.phx.gbl... >>> Is there any way to move the temp file that is create by Profiler (used >>> for the datagrid scrolling screen) from the C: drive? Our C drive is >>> not large enough to allow a long Profiler session, since the Profiler >>> temp file ends up filling up the C drive. >>> >>> Nancy >>> >> >> > > Thanks, with your prodding, I did find what you were talking about and after
some trial and error I got it to work, now I'll do some more testing and hopefully this will be just what the higher ups needed. Thanks again, Nancy Show quote "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:euSdJnajHHA.5044@TK2MSFTNGP05.phx.gbl... > Yes you are missing the whole point. Profiler is a gui tool that uses the > output of the internal utility called TRACE. You can output directly from > Trace to a file without running profiler at all. Profiler has an enormous > overhead (as you found out) and can significantly impact performance while > running. You do this via a series of tsql commands not with profiler > itself. But you can use Profiler as I mentioned to create the script that > has the series of tsql commands to do the actual work. This first link > shows how to create a script from scratch but if you follow my > instructions fromt he previous post it will make it much easier for you. > All you need to do is to fill in the file name it will create. Have a look > at these: > > > http://support.microsoft.com/kb/283790 How to create a trace > > http://msdn2.microsoft.com/en-us/library/ms191006.aspx > > http://msdn2.microsoft.com/en-us/library/aa173875(SQL.80).aspx > > > -- > Andrew J. Kelly SQL MVP > > "Nancy Lytle" <nancy_ly***@payformance.com> wrote in message > news:%23FngeUZjHHA.5012@TK2MSFTNGP04.phx.gbl... >> I'm not sure I am getting what you mean. >> We need to run a trace for a period of time to cover while some jobs are >> processing etc, and this could take several hours. We do create a trace >> and save it to a file or table, but don't you have to leave the Profiler >> window open? When I tried to close it, it will stop the trace. And it >> is what is displayed in that window that creates the large temp file on >> the C drive. >> And this could be for either SQL 2005 or 2000, we use one in one >> environment (one division) and the other in another environment (the >> other division), both are using Windows 2003. >> Am I missing something here? >> >> Nancy >> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message >> news:OvYsXLZjHHA.492@TK2MSFTNGP04.phx.gbl... >>> Well you really shouldn't be using profiler for such things anyway. The >>> more efficient way to do this is to create a trace and send the trace >>> events directly to a file on a drive attached to the server. Then you >>> can copy that file to your local machine and view it in Profiler or dump >>> it into a table for querying. If you set up a trace definition in >>> Profiler, run it for a second and then stop it you can go to File - >>> Script trace to generate an almost ready made script for your trace. >>> >>> -- >>> Andrew J. Kelly SQL MVP >>> >>> "Nancy Lytle" <nancy_ly***@payformance.com> wrote in message >>> news:O2Gku9YjHHA.1240@TK2MSFTNGP04.phx.gbl... >>>> Is there any way to move the temp file that is create by Profiler (used >>>> for the datagrid scrolling screen) from the C: drive? Our C drive is >>>> not large enough to allow a long Profiler session, since the Profiler >>>> temp file ends up filling up the C drive. >>>> >>>> Nancy >>>> >>> >>> >> >> > > Nancy Lytle (nancy_ly***@payformance.com) writes:
> Is there any way to move the temp file that is create by Profiler (used To answer this question: change the TMP environment variable.> for the datagrid scrolling screen) from the C: drive? Our C drive is > not large enough to allow a long Profiler session, since the Profiler > temp file ends up filling up the C drive. But a server-side trace as Andrew suggested is a much better solution. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Where can I find "TMP environment variable"
Nancy Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9926F3B199D74Yazorman@127.0.0.1... > Nancy Lytle (nancy_ly***@payformance.com) writes: >> Is there any way to move the temp file that is create by Profiler (used >> for the datagrid scrolling screen) from the C: drive? Our C drive is >> not large enough to allow a long Profiler session, since the Profiler >> temp file ends up filling up the C drive. > > To answer this question: change the TMP environment variable. > > But a server-side trace as Andrew suggested is a much better solution. > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Nancy Lytle wrote:
> Where can I find "TMP environment variable" Control Panel - SystemAdvanced tab Environment Variables Got it! Thanks,
Nancy Show quote "Ed Murphy" <emurph***@socal.rr.com> wrote in message news:463f61e9$0$15113$4c368faf@roadrunner.com... > Nancy Lytle wrote: > >> Where can I find "TMP environment variable" > > Control Panel - System > Advanced tab > Environment Variables My guess:
HKEY_CURRENT_USER\Environment or possibly: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Nancy Lytle" <nancy_ly***@payformance.com> wrote in message news:%23hUs2LMkHHA.1340@TK2MSFTNGP04.phx.gbl... > Where can I find "TMP environment variable" > Nancy > > "Erland Sommarskog" <esq***@sommarskog.se> wrote in message > news:Xns9926F3B199D74Yazorman@127.0.0.1... >> Nancy Lytle (nancy_ly***@payformance.com) writes: >>> Is there any way to move the temp file that is create by Profiler (used >>> for the datagrid scrolling screen) from the C: drive? Our C drive is >>> not large enough to allow a long Profiler session, since the Profiler >>> temp file ends up filling up the C drive. >> >> To answer this question: change the TMP environment variable. >> >> But a server-side trace as Andrew suggested is a much better solution. >> >> -- >> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >> >> Books Online for SQL Server 2005 at >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >> Books Online for SQL Server 2000 at >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > >  |
|||||||||||||||||||||||
Other interesting topics