December 3, 2009 at 1:58 pm
Hello friends,
i am running a job and that given an error. the error log is shown below. Please can anyone tell me what's wrong, because i cannot exactly understand what this error says
Message
Executed as user: IBM\anton234. Could not allocate space for object 'dbo.SORT temporary run storage: 17643546809231' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. [SQLSTATE 42000] (Error 1105). The step failed.
December 3, 2009 at 2:20 pm
it means the tmpdb doesn't have enough disk space to grow as big as it needs to.
you can run this to see where your tempdb is and make more room on that drive to accommodate tempDB
use tempdb
select * from sysfiles
December 3, 2009 at 2:26 pm
I guess autogrowth could be off as well.
December 3, 2009 at 2:49 pm
i was looking forward to move the Tempdb to a new drive that has more space . so now after using the Alter statements and then moving the files, SQL Server needs to restart.
Now here comes the confusion.
when i go to services i see many SQL Services. SO do i have to restart all of them. The below list shows what services are running and what are disabled. so is there any priority which one should be stopped and restarted or start form the first one?
SQL Server(MSSQLSERVER) started Automatic
SQL Server Agent(MSSQLSERVER) started Automatic
SQL Server Full Text Search(MSSQLSERVER) started Automatic
SQL Server INtegration Services started Automatic
SQL Server Reporting Services(MSSQLSERVER) started Autoimatic
SQL Server VSS Writer started Automatic.
December 3, 2009 at 4:03 pm
When you go to restart the database engine, it will ask you to stop the Agent, which is dependent on it. SSIS and SSRS shouldn't necessarily be an issue.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 3, 2009 at 8:05 pm
so after moving the tempdb files i still see my files in the old drive where it was before. and also see the files in the new drive so what do i do with the old tempdb files?
December 3, 2009 at 8:14 pm
delete them - they have no value any more.
December 4, 2009 at 9:38 am
so now i have moved the Tempdb to a new drive but its 200 GB now, so any idea to shrink it.
December 4, 2009 at 9:41 am
espanolanthony (12/4/2009)
so now i have moved the Tempdb to a new drive but its 200 GB now, so any idea to shrink it.
If it has grown to that size, it would most likely grow to that size again in the future, so no point in shrinking it.
Look at what's causing that?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 4, 2009 at 9:51 am
as i mention earlier create one bcp job and because of that Tempdb grows. but now that job won't run so the Tempdb won't grow much. so any idea how to shrink it. or do you think i can restart the SQL Server services.
December 4, 2009 at 10:00 am
Follow this article http://support.microsoft.com/kb/307487
Remember, restarting your SQL Server means its a down time for your application using that Instance, so be aware of that.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply