Post reply

SQL Server 2005 error

  • December 3, 2009 at 1:58 pm

    #206899

    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

    #1087381

    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

    #1087386

    I guess autogrowth could be off as well.

  • December 3, 2009 at 2:49 pm

    #1087391

    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

    #1087427

    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.

  • December 3, 2009 at 8:05 pm

    #1087460

    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

    #1087461

    delete them - they have no value any more.

  • December 4, 2009 at 9:38 am

    #1087845

    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

    #1087849

    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?

    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • December 4, 2009 at 9:51 am

    #1087857

    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

    #1087863

    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.

    Bru Medishetty

    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