TempDB Configuration Primer Needed 3

[briangriffin]

I'm no DBA, but since we don't have one here it falls on me - I'm hoping for a quick lesson in database maintenance.

SQL Server 2008, very little data actually is stored on the server. All files reside on the C: drive, which is 32 gigs with 7 gigs currently free. I have 50+ gigs free on another local drive.

The problem surfaced when running a linked server query against an Oracle database. I have to park data in three temp tables, then query against them to return the dataset.

Error messages:

Msg 1105, Level 17, State 2, Line 98
Could not allocate space for object 'dbo.SORT temporary run storage:  140737496023040' 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.
Msg 9002, Level 17, State 4, Line 98
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

I looked on the C: drive and it was full, so I did dbcc shrinkdatabase on tempdb and it freed up 7 gigs. However, when running the query the drive filled up again and I got the same message. The data being stored in the temp tables is minimal, perhaps 40,000 rows total.

Log_reuse_wait_desc value is 'NOTHING'.

Various articles recommend setting tempdb to autogrow, which I did. So my questions are:

1. I'm sure it would be appropriate to move the tempdb files to another drive with more space - what is the best way to accomplish this?

2. Why would relatively small temp tables immediately eat up 7 gigs of storage?

3. If I can't set autoshrink to true on tempdb, do I need to manually shrink it at regular intervals?

As well as any additional information or recommendations you may have.

TIA.

 

[simian336]

No, shrinkfile is not very good option. Since this is temp data would you need to recover a tranacton that was processed?

Guessing probably not. So your best option would be to set the Database mode to SIMPLE and let sql server manage the transaction log and dump those transactions for you.

Simi

 

[simian336]

Oops missed question 2.

Why would relatively small temp tables immediately eat up 7 gigs of storage?

Small temp tables would not eat up the space but the transactions being processed against those table would.

Simi

 

[briangriffin]

You're right, there are no transactions to be recovered. However, recovery on tempdb is set to Simple.

Does that mean that if I allocated more space then I would probably stay under that figure moving forward? If so, I'm back to having to move those files to a different drive. Is there a rule of thumb for calculating the max space to allocate?

 

[SQLBill]

A small amount of rows can take up a lot of space. It all depends on what is stored in them. For example: the first sentence below would take up more space than the second.

The quick brown fox jumps over the lazy dogs back.
Hello world!

Do the rows contain TEXT values or BLOBS? Both of those can greatly increase the amount of space being used.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[simian336]

if it is simple then you just need to turn Auto growth off.

Simi

 

[briangriffin]

More information:

Temp table 1: 3k rows, 5 columns
Temp table 2: 3k rowns, 3 columns
Temp table 3: 67k rows, 15 columns

No blobs, mostly numeric data but a handful of text fields.

I started with 7 gigs free and ran the script to populate the tables, but commented out the final query that references them. No errors, still 7 gigs.

I then dropped the temp tables and ran it again, but this time only ran the final query against the two smaller temp tables (a and b). Again, no errors and still 7 gigs.

So I uncommented everything and ran it again - the errors reappeared and my disk was completely full. Ran dbcc shrinkdatabase(tempdb) and I'm back to 7 free gigs.

So I'm going to retool the final query; I think I'm getting an unexpected explosion of records when I incorporate the third table.

Thanks for your input.

 

[gmmastros]

You can move TempDB easily enough to another drive. Instructions here:

http://blog.sqlauthority.com/2007/0...-move-tempdb-from-one-drive-to-another-drive/

It sounds to me like you are missing a join condition between some of your tables.

If you have 3 tables with 67k, 3k and 3k rows, without any joins, you would end up with 603,000,000,000 rows. Check your query. Make sure you have the proper joins set up.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[briangriffin]

Well, moving tempdb was easier than I could have imagined.

The joins were ok, but I retooled it a bit to get down to 18k rows total and all is well. Thanks again.