Device holding MASTER, MODEL, MSDB, and TempDB FULL

I have other drives that are quite large that I could move any one of these
to. Not sure how to do the move...this is a production server and down
time needs to be almost non-existent.
TempDB is taking up 1 gig, if there was a way to move it without bringing
down the server, that would be great (like adding a file to new drive, then
shrinking with EMPTY_FILE on old drive.....can this be done during
production or would it cause slowness or locking, etc.?)What version of SQL Server are you using ...?
Have you tried Shrinking it. DBCC SHRINKFILE
HTH
Ryan Waight, MCDBA, MCSE
"John Hamilton" <jhamil@.nowhere.com> wrote in message
news:ODt9rtJnDHA.2528@.TK2MSFTNGP12.phx.gbl...
> I have other drives that are quite large that I could move any one of
these
> to. Not sure how to do the move...this is a production server and down
> time needs to be almost non-existent.
> TempDB is taking up 1 gig, if there was a way to move it without bringing
> down the server, that would be great (like adding a file to new drive,
then
> shrinking with EMPTY_FILE on old drive.....can this be done during
> production or would it cause slowness or locking, etc.?)
>|||I'm using 2000, and I tried DBCC SHRINKFILE, it didn't change the size.
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eWnaIzJnDHA.2012@.TK2MSFTNGP12.phx.gbl...
> What version of SQL Server are you using ...?
> Have you tried Shrinking it. DBCC SHRINKFILE
>
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "John Hamilton" <jhamil@.nowhere.com> wrote in message
> news:ODt9rtJnDHA.2528@.TK2MSFTNGP12.phx.gbl...
> > I have other drives that are quite large that I could move any one of
> these
> > to. Not sure how to do the move...this is a production server and down
> > time needs to be almost non-existent.
> >
> > TempDB is taking up 1 gig, if there was a way to move it without
bringing
> > down the server, that would be great (like adding a file to new drive,
> then
> > shrinking with EMPTY_FILE on old drive.....can this be done during
> > production or would it cause slowness or locking, etc.?)
> >
> >
>|||Tried SHRINKFILE again and it worked this time....'?
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eWnaIzJnDHA.2012@.TK2MSFTNGP12.phx.gbl...
> What version of SQL Server are you using ...?
> Have you tried Shrinking it. DBCC SHRINKFILE
>
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "John Hamilton" <jhamil@.nowhere.com> wrote in message
> news:ODt9rtJnDHA.2528@.TK2MSFTNGP12.phx.gbl...
> > I have other drives that are quite large that I could move any one of
> these
> > to. Not sure how to do the move...this is a production server and down
> > time needs to be almost non-existent.
> >
> > TempDB is taking up 1 gig, if there was a way to move it without
bringing
> > down the server, that would be great (like adding a file to new drive,
> then
> > shrinking with EMPTY_FILE on old drive.....can this be done during
> > production or would it cause slowness or locking, etc.?)
> >
> >
>|||Have a look at this :- http://www.aspfaq.com/show.asp?id=2446
good for future reference..
--
HTH
Ryan Waight, MCDBA, MCSE
"John Hamilton" <jhamil@.nowhere.com> wrote in message
news:%234Iku4JnDHA.2080@.TK2MSFTNGP10.phx.gbl...
> Tried SHRINKFILE again and it worked this time....'?
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:eWnaIzJnDHA.2012@.TK2MSFTNGP12.phx.gbl...
> > What version of SQL Server are you using ...?
> >
> > Have you tried Shrinking it. DBCC SHRINKFILE
> >
> >
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "John Hamilton" <jhamil@.nowhere.com> wrote in message
> > news:ODt9rtJnDHA.2528@.TK2MSFTNGP12.phx.gbl...
> > > I have other drives that are quite large that I could move any one of
> > these
> > > to. Not sure how to do the move...this is a production server and
down
> > > time needs to be almost non-existent.
> > >
> > > TempDB is taking up 1 gig, if there was a way to move it without
> bringing
> > > down the server, that would be great (like adding a file to new drive,
> > then
> > > shrinking with EMPTY_FILE on old drive.....can this be done during
> > > production or would it cause slowness or locking, etc.?)
> > >
> > >
> >
> >
>|||This would indicate an open transaction was preventing the shrink. DBCC
OPENTRAN shows any open transcations
--
HTH
Ryan Waight, MCDBA, MCSE
"John Hamilton" <jhamil@.nowhere.com> wrote in message
news:%234Iku4JnDHA.2080@.TK2MSFTNGP10.phx.gbl...
> Tried SHRINKFILE again and it worked this time....'?
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:eWnaIzJnDHA.2012@.TK2MSFTNGP12.phx.gbl...
> > What version of SQL Server are you using ...?
> >
> > Have you tried Shrinking it. DBCC SHRINKFILE
> >
> >
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "John Hamilton" <jhamil@.nowhere.com> wrote in message
> > news:ODt9rtJnDHA.2528@.TK2MSFTNGP12.phx.gbl...
> > > I have other drives that are quite large that I could move any one of
> > these
> > > to. Not sure how to do the move...this is a production server and
down
> > > time needs to be almost non-existent.
> > >
> > > TempDB is taking up 1 gig, if there was a way to move it without
> bringing
> > > down the server, that would be great (like adding a file to new drive,
> > then
> > > shrinking with EMPTY_FILE on old drive.....can this be done during
> > > production or would it cause slowness or locking, etc.?)
> > >
> > >
> >
> >
>

Determining what causes tempdb to autogrow

I have a SQL server with over 15 user databases on it. Tempdb autogrows to
the point that it monopolizes all available disk space (I just recently
shrank it from 36 GB to free up space). Does anyone have any tips on how to
determine what process are causing so much space usage for my tempdb? It is
perplexing...I used sysmon to track when log usage increases and I try to
correlate that with profile traces in which I capture 'text like '%#%'.
This is not the best way to do this so I was wondering if anyone had any
suggestions.
Rod,
Use perfmonitor.
Performance object - "SQLServer: Access methods"
Counters - Workfiles Created/sec,Worktables created/sec,Worktables From
cache Ratio and also anything related.
Performance object - "SQLServer:databases"
Counters - Active transactions, datafile size and also anything related.
Here is some related info:
'TechNet Support WebCast: Administering and maintaining the tempdb system
database in Microsoft SQL Server'
http://support.microsoft.com/default...&Product=sql2k
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl#GOEHA.2716@.tk2msftngp13.phx.gbl...
> I have a SQL server with over 15 user databases on it. Tempdb autogrows
to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
to
> determine what process are causing so much space usage for my tempdb? It
is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>
|||Some information here as well
http://www.aspfaq.com/2446
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl%23GOEHA.2716@.tk2msftngp13.phx.gbl...
>I have a SQL server with over 15 user databases on it. Tempdb autogrows to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
> to
> determine what process are causing so much space usage for my tempdb? It
> is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>

Determining what causes tempdb to autogrow

I have a SQL server with over 15 user databases on it. Tempdb autogrows to
the point that it monopolizes all available disk space (I just recently
shrank it from 36 GB to free up space). Does anyone have any tips on how to
determine what process are causing so much space usage for my tempdb? It is
perplexing...I used sysmon to track when log usage increases and I try to
correlate that with profile traces in which I capture 'text like '%#%'.
This is not the best way to do this so I was wondering if anyone had any
suggestions.Rod,
Use perfmonitor.
Performance object - "SQLServer: Access methods"
Counters - Workfiles Created/sec,Worktables created/sec,Worktables From
cache Ratio and also anything related.
Performance object - "SQLServer:databases"
Counters - Active transactions, datafile size and also anything related.
Here is some related info:
'TechNet Support WebCast: Administering and maintaining the tempdb system
database in Microsoft SQL Server'
http://support.microsoft.com/default.aspx?scid=kb;en-us;834846&Product=sql2k
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl#GOEHA.2716@.tk2msftngp13.phx.gbl...
> I have a SQL server with over 15 user databases on it. Tempdb autogrows
to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
to
> determine what process are causing so much space usage for my tempdb? It
is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>|||Some information here as well
http://www.aspfaq.com/2446
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl%23GOEHA.2716@.tk2msftngp13.phx.gbl...
>I have a SQL server with over 15 user databases on it. Tempdb autogrows to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
> to
> determine what process are causing so much space usage for my tempdb? It
> is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>

Determining what causes tempdb to autogrow

I have a SQL server with over 15 user databases on it. Tempdb autogrows to
the point that it monopolizes all available disk space (I just recently
shrank it from 36 GB to free up space). Does anyone have any tips on how to
determine what process are causing so much space usage for my tempdb? It is
perplexing...I used sysmon to track when log usage increases and I try to
correlate that with profile traces in which I capture 'text like '%#%'.
This is not the best way to do this so I was wondering if anyone had any
suggestions.Rod,
Use perfmonitor.
Performance object - "SQLServer: Access methods"
Counters - Workfiles Created/sec,Worktables created/sec,Worktables From
cache Ratio and also anything related.
Performance object - "SQLServer:databases"
Counters - Active transactions, datafile size and also anything related.
Here is some related info:
'TechNet Support WebCast: Administering and maintaining the tempdb system
database in Microsoft SQL Server'
http://support.microsoft.com/defaul...6&Product=sql2k
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl#GOEHA.2716@.tk2msftngp13.phx.gbl...
> I have a SQL server with over 15 user databases on it. Tempdb autogrows
to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
to
> determine what process are causing so much space usage for my tempdb? It
is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>|||Some information here as well
http://www.aspfaq.com/2446
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl%23GOEHA.2716@.tk2msftngp13.phx.gbl...
>I have a SQL server with over 15 user databases on it. Tempdb autogrows to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
> to
> determine what process are causing so much space usage for my tempdb? It
> is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>

Determining the cause of tempdb growth?

I'm trying to determine the cause of tempdb to grow from ~ 30MB to well over
40GB. I have a SQL Profiler trace that was run at the time of this growth
but I don't see any particular SQL statements that consumed a lot of CPU or
performed a lot of reads (maybe the maximum was 50000 reads).
Any ideas as to what I could do to identify this problem?
Thanks in advance.Creation of temp tables and ORDER BY statements can cause tempdb growth. = Tempdb may also be used by other (SQL Server internal) query =optimization processes.
With that said, 40GB does sound rather large. How large are your databases?
What type of operations were being performed during the growth?
-- Keith
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message =news:OPLVdaYmDHA.1800@.TK2MSFTNGP10.phx.gbl...
> I'm trying to determine the cause of tempdb to grow from ~ 30MB to =well over
> 40GB. I have a SQL Profiler trace that was run at the time of this =growth
> but I don't see any particular SQL statements that consumed a lot of =CPU or
> performed a lot of reads (maybe the maximum was 50000 reads).
> > Any ideas as to what I could do to identify this problem?
> > Thanks in advance.
> >