Drive Space Problem | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Drive Space Problem

Hi I am running a job in sql server 2005 , which updates the statistics of all the tables in the db. The Data Drive containing the data files has only about 9 Gb of space available. But now when this job starts to run the space in the drive reduces to as less as 11 mb as the tempdb increases, and also the job fails. Is there any way to stop the tempdb from growing when running this job.
You cannot stop that doing so, as it is by design.
Why you are updating all the tables, is it required on all the tables?
Why don’t you perform UPDATE STATS on mostly updated tables? http://sqlserver-qa.net/blogs/tools…ks-and-transaction-log-is-filing-up-help.aspx for best practices and what to do for tempdb fillup –http://sqlserver-qa.net/blogs/tools…tempdb-is-full-in-sql-2000-2005-versions.aspx links. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Hi, Adding to what Satya has suggested, refer below article also:
working with tempdb
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx Regards Hemantgiri S. Goswami
MS SQL Server MVP
————————-
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami http://hemantgirisgoswami.blogspot.com
Hi Satya, Hemant, Thanks for the reply. I went through the urls and they were of great help. Regarding the update statistics job I have a table in the db which is 30 gb and is growing by the day. How frequently should I update the stats of this table?
Are you sure you are only updating the statistics? I don’t think update statistics takes up the space at all… How you are running the update stats? Can you post the command…
Reindexing and indexdefrag and other maintenance processes should take the space but not statistics and what is the sample you are using?
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
How frequently – depends upon how frequently the data is getting updated on this table, say the rowcount is not over 10% on the current volume then you can perform every week. Otherwise you need to consider fillfactor on the indexes of that table,http://sqlserver-qa.net/blogs/perftune/archive/2007/05/08/fill-factor-blurb-to-decide.aspx fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Hi, I do it through a job which executes a sp every night. The sp basically has a cursor which selects all the tables from the sysobjects and then update the statistics for each table. SET @SQL_STMT = ‘UPDATE STATISTICS ‘ + @TABLE_NAME +’ WITH FULLSCAN’
EXEC(@SQL_STMT) I have noticed that just after the job starts the drive space (8.91 gb) starts reducing as the tempdb increases. After the job fails the space in the drive reduces to as less as 11 Mb and then I have to restart the service to regain the space. The db size is approximately 70 GB.
It is very interesting…
I was under the impression that update statistics will not use tempdb… What version you are on….
and check the link provided by ghemant…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Yes the UPDATE STATISTICS uses the TEMPDB and you can verify if temp objects (temp tables and variables) are being cached as SQL2005 caches Temp objects that uses TEMPDB by default.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thanks Satya, Is there any way figure it out how much tempdb space required for update stats?
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Hi,<br /><br />We just moved the tempdb data and log file to a separate drive which had 29 GB space and the job succeeded [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />The entire job requires 10 GB of space to succeed and since the old drive had 8.91 GB it was failing.<br /><br />I have the same question can we can make some estimation for the space required.<br /><br />The db is 70 GB and update stats requires 10 GB.<br /><br /><br />
It is difficult to estimate the tempdb space requirement for an application.
One of the best practice is to allow 20% more space on tempdb as compard to the user database. There are various factors you need to consider for the space requirement in TEMPDB data files:
• Query
• Triggers
• Online index creation
• Temporary tables, table variables, and table-valued functions
• DBCC CHECK
• LOB parameters
• Cursors
• Service Broker and event notification (if you are using)
• XML and LOB variable
• Query notifications
• Database mail
• Index creation
• User-defined functions Also this is not a strict guidline and it vary from installation to installation. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>