Freeing tempdb resources - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Freeing tempdb resources

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2006-04-18 : 17:41:35

Hi My tempdb database data file is very large, does somebody know how can I free up resources? I have already stopped and restarted sql server,

thank you

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-18 : 18:28:31

stop sql server, delete the file, restart the server.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

heze
Posting Yak Master

192 Posts

Posted - 2006-04-18 : 18:35:02

ok, it is this file,

S:\MSSQL\Data\tempdb.mdf
size=3,240,768 KB
if I delete this file will I cause any trouble in some other place?

thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-18 : 18:38:13

quote:
Originally posted by suhde76

Go to the Query Analyzer and type in the following commands:-

Use master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
Update master..sysaltfiles set size = <WhateverSizeYouWantInMB>*128 where name = 'tempdev'
Go

Stop SQL Server services. Delete or move the tempdb files. Start SQL Server services (This will create a new MDF and new LDF file for the tempdb database)

Again Open Query Analyzer and run the following commands:-

Use master
Go
sp_configure 'allow updates', 0
reconfigure with override
Go

This should shrink your tempdb to the size of your requirement.

Thanks, Suhas, MS SQL Server Engineer, Microsoft.

Don't do that! Do what nr said to do. It'll create the tempdb database using the size of the model database. There's no reason to modify system tables for this.

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-18 : 18:48:36

Wow, I can't believe MS is actually recommending this! This seems very strange to me to recommend modifying data in system tables.

So I guess I'm wrong about tempdb being created the same size as Model. I've never had to do it, so I was making an assumption.

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-18 : 18:59:01

I believe you. It's good information to know about tempdb. I'll keep it in mind. However, I haven't had to modify system tables since SQL Server 7.0 and highly recommend people not to touch them as there are usually ways to avoid it. I don't believe this original poster will need to modify the system table as from another post of his the increase of the database was due to a query. At least that's what I recall. So he should be able to follow nr's advice.

Tara Kizer
aka tduggan

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-18 : 19:11:25

Tempdb will be created as the size it was set rather than model (although it needs model to be created).

I've seen a manual delete help in the past although it shouldn't as it should be deleted and recreated automatically.

Had to try this as it's been a long time but (haven't tried on v2005).
You can't do a alter database as it won't let you shrink the file but you can start the server in minimal mode then do an alter database to set the file size.
On my laptop.
Open a command window
go to the sql server folder for the instance
C:\Program Files\Microsoft SQL Server\MSSQL\Binn
run sqlserver -c -f

That will start the server - leave the command window open
change the size in query analyser (easier to have the command set up to rund before stopping and starting the server)
Alter database tempdb modify file (name = tempdev, filename = 'c:\Sqldata\MSSQL\data\tempdb.mdf', size=8MB)
Back to the command window = ctrl c to stop the server.
Now restart sql server and you should have a 8MB file.

Think I'd prefer that to patching sysaltfiles - I tend to only do that if someone has messed up the server by perhaps trying to move system databases.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-18 : 21:22:12

Why not just shrink it with DBCC SHRINKFILE commands?


use tempdb

dbcc shrinkfile ( 'tempdev', 1000 )

dbcc shrinkfile ( 'templog', 500 )

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-04-19 : 00:42:29

"Why not just shrink it [TEMPDB] with DBCC SHRINKFILE commands?"

'Coz it is likely to corrupt it if its in use? And Paul said not to

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61440

But apparently if you restart SQL Server in single user mode its OK:

http://support.microsoft.com/default.aspx/kb/307487

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-19 : 05:27:09

Not keen on doing a shrink of tempdb even in single user mode.
And as it's recreated every server start the alter database just feels a bit neater.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-04-19 : 11:20:23

"Not keen on doing a shrink of tempdb even in single user mode.
And as it's recreated every server start the alter database just feels a bit neater"

Me too!

heze
Posting Yak Master

192 Posts

Posted - 2006-04-19 : 23:59:21

Hi,

The problem if I shrink the database I would be restricting its size and my sql scripts make heavy use of temporary tables as t said. I just read today that using table variables is cheaper thatn temp tables since they are stored in memory rather than in some database. Now, I am running a script of aproximately 2000 lines of code so changing everything to variables of type table will not be easy task bu may ba a way to go.
By the way I stpped the server, deleted the file and started again. Even then , tempdb was growing so fast that it filled the entire disk(we have 36GB in the sql srvr partition and 14GB in the file syst part), the application reached a point in which it could o nowhere. I made some changes and its running again now.
Adittionally, I also found that one can move the tempdb to another hard disk and given
that our server disk is small(i think? RFC..) I was thinking about doing so using

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\tempdb.mdf')
go

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\templog.ldf')

Again, i dont want to restrict, I want to expand.
Please help regarding the changing temptables to table variables or an alternative technique to make use of the temp table functionality without being so inefficient?
Are the temp tables really used by the professional sql coders? is it a bad practice?
I found that by using them I could develop queries that with the click of 1 button, they can build an entire database and execute all kinds of profcesses in the way, of course, in the script, after using them, almost allways, I drop them if they will not be needed further in the process. This of course, reduces a lot of administration and is error safe in the sense that if by mistake an administrator deletes an object(has happened), I can either recreate it alone or the entire database again. Unless any of you tell me the opposite, up to now I believe that there is no replacement for temp tables (functionality at least).

thank you

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-20 : 09:15:50

If tempdb is growing due to your queries then you should not shrink it. It will only grow again and that is very expensive in resources.
It sounds like you are using large temp tables - if so table variables will not help and may be a lot slower as they have less optimisation options.

Probably your only solution is to look at your queries and recode them so that they have less impact on tempdb.

>> Are the temp tables really used by the professional sql coders?
Yes
>> is it a bad practice?
No. But that doesn't mean that it isn't possible to use innappropriate temp tables.

>> I found that by using them I could develop queries that with the click of 1 button, they can build an entire database and execute all kinds of profcesses in the way
What are you doing - it sounds a bit like you are loading the whole database into temp tables which is not really the point.
Are you using temp tables or global temp tables?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

heze
Posting Yak Master

192 Posts

Posted - 2006-04-20 : 14:47:30

Ok n,

yes I am recoding. What I am doing is building a large flat table that contains data from tables in other databases and sometimes in other servers. I am then querying this flat table and producing smaller manageable tables which are then queried by the users. The flat table became so slow that I striped it into primary key+individual fields so that queries to it in the next part of the process took less time. Indeed after some tests, the time of exectution ws reduced to 1/4 of the original time. However, it was at the expense of space, I filled the hard disc of the server and has everybody complaining. Now, instead of temp tables I am using views the most I can, the proces is faster but defenitely not as fast. I am also working in indexing some of the tables to improve performance.

montu
Yak Posting Veteran

60 Posts

Posted - 2008-01-09 : 20:40:47

>>Update master..sysaltfiles set size = <WhateverSizeYouWantInMB>*128 where name = 'tempdev'

why here *128 if specify the size in MB sorry for silly question but i have no idea why here size in MB multiply by 128

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-10 : 10:22:03

quote:
Originally posted by montu

>>Update master..sysaltfiles set size = <WhateverSizeYouWantInMB>*128 where name = 'tempdev'

why here *128 if specify the size in MB sorry for silly question but i have no idea why here size in MB multiply by 128

The SQL Server page size is 8192. What number would you have to multiply 8192 by to get 1 MB?

CODO ERGO SUM

amitsharma
Starting Member

2 Posts

Posted - 2008-06-18 : 06:12:50

hi i have created sfdata databse having craores of records in the table. now when i trying to insert some data in some tabel in sfdata databse, the sql server 2005 increses the size of mdf file on tempdb databse and becuse of this my c drive gets full. why this is incresing the size of tempdb databse mdf file however tehre no link b/w the databse i am working and the tempdb database. how can i remove the .mdf file of tempdb file

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-18 : 09:07:41

Dups:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64914

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources