SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Freeing tempdb resources
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

heze
Posting Yak Master

USA
192 Posts

Posted - 04/18/2006 :  17:41:35  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 04/18/2006 :  18:28:31  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

heze
Posting Yak Master

USA
192 Posts

Posted - 04/18/2006 :  18:35:02  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36848 Posts

Posted - 04/18/2006 :  18:38:13  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36848 Posts

Posted - 04/18/2006 :  18:48:36  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 04/18/2006 18:53:27
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36848 Posts

Posted - 04/18/2006 :  18:59:01  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 04/18/2006 19:01:49
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/18/2006 :  19:11:25  Show Profile  Visit nr's Homepage  Reply with Quote
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.

Edited by - nr on 04/18/2006 19:14:18
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/18/2006 :  21:22:12  Show Profile  Reply with Quote
Why not just shrink it with DBCC SHRINKFILE commands?


use tempdb

dbcc shrinkfile ( 'tempdev', 1000 )

dbcc shrinkfile ( 'templog', 500 )





CODO ERGO SUM
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 04/19/2006 :  00:42:29  Show Profile  Reply with Quote
"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
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/19/2006 :  05:27:09  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 04/19/2006 :  11:20:23  Show Profile  Reply with Quote
"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!
Go to Top of Page

heze
Posting Yak Master

USA
192 Posts

Posted - 04/19/2006 :  23:59:21  Show Profile  Reply with Quote
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
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/20/2006 :  09:15:50  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

heze
Posting Yak Master

USA
192 Posts

Posted - 04/20/2006 :  14:47:30  Show Profile  Reply with Quote
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.
Go to Top of Page

montu
Yak Posting Veteran

60 Posts

Posted - 01/09/2008 :  20:40:47  Show Profile  Reply with Quote
>>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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/10/2008 :  10:22:03  Show Profile  Reply with Quote
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
Go to Top of Page

amitsharma
Starting Member

India
2 Posts

Posted - 06/18/2008 :  06:12:50  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 06/18/2008 :  09:07:41  Show Profile  Reply with Quote
Dups:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64914
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000