The sum of all SQL Server matter that exist,
and the space in which all SQL Server events
occur or could occur.
Welcome to SQL Server Universe.com Sign in | Join | Help
in
Home SS SLUG Forums Articles Photos Downloads
SQL Server Universe.com » SQL Server Components » SQL Server Administration » Re: TempDB is full

TempDB is full

Last post 10-30-2007, 18:30 by Chandana N. Athauda. 2 replies.
Sort Posts: Previous

  • TempDB is full

    		 10-29-2007, 18:02

    zahran
    • Joined on 04-03-2007
    • Posts 13
    • Points 0
    • Top 10 Contributor
    Reply Quote

    When I try to execute a stored procedure I get following message.

    Server: Msg 1105, Level 17, State 2, Procedure usp_Test, Line 74
    Could not allocate space for object '(SYSTEM table id: -395925358)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.

    I have not used any temporory table in my stored procedure. TempDB is configured to unrestricted file growth and my hard disk has enough spaces.

     What could be the reason to the above error.

  • Re: TempDB is full

    		 10-30-2007, 6:18

    Dinesh Priyankara
    • Joined on 11-22-2006
    • Colombo, Sri Lanka
    • Posts 102
    • Points 0
    • Top 10 Contributor
      Male
    Reply Quote
    Hi Zaharan,

    SQL Server uses tempdb for cursor operations and sorting operations too, not only for temporary objects. What is the implementation of your stored procedure?

    This error is totally related to space allocation for tempdb. Even though you have set the “unrestricted file growth” and has enough space in the drive, it may use the entire free space while running the SP. Whats the size of the tempdb after the error and whats the free space size in the drive?

  • Re: TempDB is full

    		 10-30-2007, 18:30

    Chandana N. Athauda
    • Joined on 03-27-2007
    • Colombo
    • Posts 48
    • Points 0
    • Top 10 Contributor
      Male
    Reply Quote
    As dinesh said, it is related to space allocation of tempdb.  So, if you can restart the SQL Server, then it will reset the tempdb size to last configured size or you can shrink the tempdb.

    How to shrink the tempdb database in SQL Server
    My Logo


    If we were supposed to write perfect code, debugger wouldn’t have been created.

View as RSS news feed in XML

(Best viewed with a resolution of more than 1024 * 768)
Powered by Community Server, by Telligent Systems