Professional Web Applications Themes

TempDB annoyance - Microsoft SQL / MS SQL Server

Whenever I try to do a large update to my SQL database 10 minutes through the update Query I receive this error message Could not allocate space for object '(SYSTEM table id: - 333902149)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.. What causes this? How can I set the tempDB to stop bothering me?...

  1. #1

    Default TempDB annoyance

    Whenever I try to do a large update to my SQL database 10
    minutes through the update Query I receive this error
    message

    Could not allocate space for object '(SYSTEM table id: -
    333902149)' in database 'TEMPDB' because the 'DEFAULT'
    filegroup is full..

    What causes this? How can I set the tempDB to stop
    bothering me?
    Dustin Guest

  2. #2

    Default Re: TempDB annoyance

    Ok, that brings me to another question... how do I move
    TempDB? I've tried "Fetch boy... Fetch.."
    and "getonouttahea" but nothin seems to work. LOL. Really
    though, how to I move this big guy...

     
    autogrow. (through 
    DATABASE statement) 
    tempdb files to a 
    it told me it had [/ref]
    10 
    >
    >
    >.
    >[/ref]
    Dustin Guest

  3. #3

    Default Re: TempDB annoyance

    Pls refer to "expanding database-SQL Server" in SQL SERVER
    BOL.
    Here is a excerpt.

    Moving tempdb
    To change the physical location of the tempdb database:

    1)Alter the tempdb database, using the ALTER DATABASE
    statement and MODIFY FILE clause, to change the physical
    file names of each file in tempdb to reference the new
    physical location, such as the new disk.


    2)Stop and restart SQL Server.


    3)Delete the old tempdb database files from the original
    location.

    HTH,
    Srinivas Sampangi

     
    Really 
    >autogrow. (through 
    >DATABASE statement) 
    >tempdb files to a 
    >it told me it had [/ref]
    >10 [/ref][/ref]
    id: - 
    >>
    >>
    >>.
    >>[/ref]
    >.
    >[/ref]
    sampangi Guest

  4. #4

    Default Re: TempDB annoyance

    Dustin,

    Heres a snippet from :

    INF: Moving SQL Server Databases to a New Location with Detach/Attach
    http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071


    Moving Tempdb
    You can move tempdb files by using the ALTER DATABASE statement.
    1.. Determine the logical file names for the tempdb database by using
    sp_helpfile as follows:
    use tempdb
    go
    sp_helpfile
    go
    The logical name for each file is contained in the name column. This
    example uses the default file names of tempdev and templog.


    2.. Use the ALTER DATABASE statement, specifying the logical file name as
    follows:
    use master
    go
    Alter database tempdb modify file (name = tempdev, filename =
    'E:\Sqldata\tempdb.mdf')
    go
    Alter database tempdb modify file (name = templog, filename =
    'E:\Sqldata\templog.ldf')
    go
    You should receive the following messages confirming the change:
    File 'tempdev' modified in sysaltfiles. Delete old file after restarting
    SQL Server.

    File 'templog' modified in sysaltfiles. Delete old file after restarting
    SQL Server.

    3.. Using sp_helpfile in tempdb will not confirm these changes until you
    restart SQL Server.
    4.. Stop and restart SQL Server.


    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "Dustin" <net> wrote in message
    news:04c001c36053$c0b6bfa0$gbl... 
    > autogrow. (through 
    > DATABASE statement) 
    > tempdb files to a 
    > it told me it had [/ref]
    > 10 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Dinesh.T.K Guest

  5. #5

    Default Re: TempDB annoyance

    Increase the size of tempdb's data file or allow it to autogrow. (through
    the DB properties in EM/Data Files or using the ALTER DATABASE statement)
    Make sure there is enough free space on the partition.

    You will get much better performance if you move your tempdb files to a
    seperate physical disk to reduce I/O contention.

    Please forgive poor tempdb, I spoke with it just now and it told me it had
    no intention of 'bothering' you ;-)

    HTH

    "Dustin" <net> wrote in message
    news:05c901c3604c$71a055e0$gbl... 


    Amy Guest

  6. Moderated Post

    Default Re: TempDB annoyance

    Removed by Administrator
    Kevin Guest
    Moderated Post

  7. #7

    Default Re: TempDB annoyance

    Use ALTER DATABASE:

    --move data file
    ALTER DATABASE tempdb
    MODIFY FILE
    (NAME = 'tempdev',
    FILENAME = 'full_path_to_your_new_data_file',
    SIZE = <new_size>,
    MAXSIZE = <max_size>,
    FILEGROWTH = <growth_increment>)

    --move log file
    ALTER DATABASE tempdb
    MODIFY FILE
    (NAME = 'templog',
    FILENAME = 'full_path_to_your_new_log_file',
    SIZE = <new_size>,
    MAXSIZE = <max_size>,
    FILEGROWTH = <growth_increment>)

    Look in BOL for more details.

    You will need to restart SQL Server and delete the old files manually.

    HTH

    "Dustin" <net> wrote in message
    news:04c001c36053$c0b6bfa0$gbl... 
    > autogrow. (through 
    > DATABASE statement) 
    > tempdb files to a 
    > it told me it had [/ref]
    > 10 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Amy Guest

  8. Moderated Post

    Default Re: TempDB annoyance

    Removed by Administrator
    Dinesh.T.K Guest
    Moderated Post

Similar Threads

  1. another annoyance
    By Getho in forum Macromedia Freehand
    Replies: 0
    Last Post: April 2nd, 03:56 AM
  2. SQL annoyance
    By Dan in forum PERL Beginners
    Replies: 3
    Last Post: January 4th, 05:35 PM
  3. tempdb tran log
    By Mark Denham in forum Informix
    Replies: 0
    Last Post: August 21st, 01:59 PM
  4. Tempdb is full?
    By Dustin in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 2nd, 02:13 PM
  5. cleanup the tempdb
    By Abraham in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 1st, 11:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139