Diskspace problem running tuning advisor after server profiler?!

Hi,
I am trying to run a session in Database tuning advisor on a ~8G database
and the workload is ~350M. During the session, diskspace on drive C is
running low, it's using more than 3G after a while.
Which database/location is the tuning advisor using for temporary files?
Drive C is too small, and I want to change the setting to use drive E for
example. Anyone knows how to do this?
Thanks!
ValentijnHi
If the DTA is using tempdb space you would be able to see this, by looking
at the file sizes; but if you suddenly regain this space after the program is
stopped then it is more likely to be temporary files in the temp directory.
In general it is best practice if you move the temp directories away from the
system disc onto a drive that is less critical if it fills up.
John
"Valentijn" wrote:
> Hi,
> I am trying to run a session in Database tuning advisor on a ~8G database
> and the workload is ~350M. During the session, diskspace on drive C is
> running low, it's using more than 3G after a while.
> Which database/location is the tuning advisor using for temporary files?
> Drive C is too small, and I want to change the setting to use drive E for
> example. Anyone knows how to do this?
> Thanks!
> Valentijn|||Microsoft recommends using of these tools on another machine if possible,
not on the production one.
--
Ekrem Ã?nsoy
"Valentijn" <Valentijn@.discussions.microsoft.com> wrote in message
news:B3C3E818-D440-4A0E-8D5C-5489D45C62F8@.microsoft.com...
> Hi,
> I am trying to run a session in Database tuning advisor on a ~8G database
> and the workload is ~350M. During the session, diskspace on drive C is
> running low, it's using more than 3G after a while.
> Which database/location is the tuning advisor using for temporary files?
> Drive C is too small, and I want to change the setting to use drive E for
> example. Anyone knows how to do this?
> Thanks!
> Valentijn|||Hi
If you are only talking about SQL Profiler and not DTA then it certainly
does use space in the temp folder.
John
"John Bell" wrote:
> Hi
> If the DTA is using tempdb space you would be able to see this, by looking
> at the file sizes; but if you suddenly regain this space after the program is
> stopped then it is more likely to be temporary files in the temp directory.
> In general it is best practice if you move the temp directories away from the
> system disc onto a drive that is less critical if it fills up.
> John
> "Valentijn" wrote:
> > Hi,
> >
> > I am trying to run a session in Database tuning advisor on a ~8G database
> > and the workload is ~350M. During the session, diskspace on drive C is
> > running low, it's using more than 3G after a while.
> >
> > Which database/location is the tuning advisor using for temporary files?
> > Drive C is too small, and I want to change the setting to use drive E for
> > example. Anyone knows how to do this?
> >
> > Thanks!
> >
> > Valentijn|||Hi John,
Thanks for the answer. I'm using the Tuning Advisor from the GUI (Management
console), so I'm not using the commandline tool DTA directly.
The SQL Profiler runs fine, but the diskspace problem occurs when running
the tuning advisor. And yes, the diskspace suddenly comes back after stopping
the tuning advisor.
One of the things I will try monday at work is changing the temp folder.
Would I do that by just changeing the environment variable TEMP?
Furthermore, how can I see DTA (or the graphical version) does use the
tempdb? I looked at the filesize op tempmdb.mdf (and the other system
databases, but they were not growing during the tuning session)..
Thanks again,
Valentijn
"John Bell" wrote:
> Hi
> If you are only talking about SQL Profiler and not DTA then it certainly
> does use space in the temp folder.
> John
> "John Bell" wrote:
> > Hi
> >
> > If the DTA is using tempdb space you would be able to see this, by looking
> > at the file sizes; but if you suddenly regain this space after the program is
> > stopped then it is more likely to be temporary files in the temp directory.
> > In general it is best practice if you move the temp directories away from the
> > system disc onto a drive that is less critical if it fills up.
> >
> > John
> >
> > "Valentijn" wrote:
> >
> > > Hi,
> > >
> > > I am trying to run a session in Database tuning advisor on a ~8G database
> > > and the workload is ~350M. During the session, diskspace on drive C is
> > > running low, it's using more than 3G after a while.
> > >
> > > Which database/location is the tuning advisor using for temporary files?
> > > Drive C is too small, and I want to change the setting to use drive E for
> > > example. Anyone knows how to do this?
> > >
> > > Thanks!
> > >
> > > Valentijn|||Hi
If the space is reclaimed once you have stopped dta then it is not tempdb
that is being used as the tempdb file would remain enlarged. sp_helpfile or
sp_helpdb from a query window would tell you the size of tempdb files
before, during and again after you ran the wizard if you ran it several
times. You could also use the perfmon counters to monitor database file sizes.
From BOL: Database Engine Tuning Advisor stores tuning session data and
other information in the msdb database. Implement an appropriate backup
strategy for the msdb database to avoid the risk of losing tuning session
data.
Again you can use the above methods to monitor the size of msdb but you say
these have not changed which again points to files being created in the temp
folder.
You need to set the temp environment variable before starting the database
tuning advisor.
John
"Valentijn" wrote:
> Hi John,
> Thanks for the answer. I'm using the Tuning Advisor from the GUI (Management
> console), so I'm not using the commandline tool DTA directly.
> The SQL Profiler runs fine, but the diskspace problem occurs when running
> the tuning advisor. And yes, the diskspace suddenly comes back after stopping
> the tuning advisor.
> One of the things I will try monday at work is changing the temp folder.
> Would I do that by just changeing the environment variable TEMP?
> Furthermore, how can I see DTA (or the graphical version) does use the
> tempdb? I looked at the filesize op tempmdb.mdf (and the other system
> databases, but they were not growing during the tuning session)..
> Thanks again,
> Valentijn
> "John Bell" wrote:
> > Hi
> >
> > If you are only talking about SQL Profiler and not DTA then it certainly
> > does use space in the temp folder.
> >
> > John
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > If the DTA is using tempdb space you would be able to see this, by looking
> > > at the file sizes; but if you suddenly regain this space after the program is
> > > stopped then it is more likely to be temporary files in the temp directory.
> > > In general it is best practice if you move the temp directories away from the
> > > system disc onto a drive that is less critical if it fills up.
> > >
> > > John
> > >
> > > "Valentijn" wrote:
> > >
> > > > Hi,
> > > >
> > > > I am trying to run a session in Database tuning advisor on a ~8G database
> > > > and the workload is ~350M. During the session, diskspace on drive C is
> > > > running low, it's using more than 3G after a while.
> > > >
> > > > Which database/location is the tuning advisor using for temporary files?
> > > > Drive C is too small, and I want to change the setting to use drive E for
> > > > example. Anyone knows how to do this?
> > > >
> > > > Thanks!
> > > >
> > > > Valentijn