Hi Folks Error: 1105, Severity: 17, State: 2 Could not allocate space for object '(SYSTEM table id: -669903346)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full. I have received this error message when running a Store procedure.. what should I do and how? Thanks in Advance sonny
Ensure you let tempdb grows automatically and there is enough disk space to host it. Change tempdb's initial size to make it bigger. And you can expand tempdb manually before run your process. 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.
What is your tempdb size? Follow the Saty's recomendations... I think your procedure is using temp table and/or procedure doing some kind of sort operations and joins causing the optimizer to create temp table... So check your procedure too...what is cuasing to tempdb grow in your procedure code... MohammedU. Moderator SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
TempDB sizes follow:- Data File: 407 MB Log file: 27 MB Autogrow on with 10 percent unrestricted growth recovery mode: simple ON GEneral Tab (properties) Size: 432.81 MB Space available: 421.52 MB of course Mohammed SP using temp table...and that's why this happenig..but I was faced this before as I never involved this kind of situation where I need to expand or transfer tempdb to the another disk (or partition). Please refer any article or thread that give us how to expand (or transfer tempdb another disk) tempdb. Thanks guys...
KBAhttp://support.microsoft.com/kb/187824 and additionallyhttp://www.sql-server-performance.com/tempdb.asp fine tune tempdb for your performance. 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.
quote:Originally posted by sonnysingh TempDB sizes follow:- Data File: 407 MB Log file: 27 MB Autogrow on with 10 percent unrestricted growth recovery mode: simple ON GEneral Tab (properties) Size: 432.81 MB Space available: 421.52 MB of course Mohammed SP using temp table...and that's why this happenig..but I was faced this before as I never involved this kind of situation where I need to expand or transfer tempdb to the another disk (or partition). Please refer any article or thread that give us how to expand (or transfer tempdb another disk) tempdb. Thanks guys... Check articles provided by Satya... Don't expect sql behaves all the times same way.... It is sql optimizer who decides how to handle the query...and also depend on the time and size of the data.... When you are executing your procedure there might be another activity on the server which might consumed tempdb a lot... MohammedU. Microsoft SQL Server MVP Moderator SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
I will try to expand first and see how it is going to react in future. If any trouble again then move to another disk... Thanks a lot guys...
