Mark Minasi's Tech Forum
Register Calendar Latest Topics
 
 
 


Reply
  Author   Comment  
cspanburgh

Avatar / Picture

Senior Member
Registered:
Posts: 219
Reply with quote  #1 
Useful tool.

/*Begin_Clear out TempDB Database */

use tempdb

go

ifexists(select*from dbo.sysobjectswhere id =Object_id('.DEX_LOCK')andtype='U')

begin

droptable DEX_LOCK

end

go

ifexists(select*from dbo.sysobjectswhere id =Object_id('.DEX_SESSION')andtype='U')

begin

droptable DEX_SESSION

end

go

/*End_Clear out TempDB Database */


__________________
Curt Spanburgh
0
wobble_wobble

Avatar / Picture

Associate Troublemaker Apprentice
Registered:
Posts: 832
Reply with quote  #2 
When would you need to do that?
__________________
Have you tried turning it off and walking away? The next person can fix it!

New to the forum? Read this
0
cspanburgh

Avatar / Picture

Senior Member
Registered:
Posts: 219
Reply with quote  #3 
New folks often notice that when they reboot the SQL server or restart the service the Tempdb goes back to it's original size.

Often the tempdb is ignored because Server guys don't know what it does.

It should be put on it's own drive.  If you had a physical box a physical drive would do fine.

If the files in the Tempdb database are 80 percent or more of the drive it's time to get them back to there original preset sizes.   One hope it's been set beyond 8 MB and that there are more files than one in the tempdb.  That being said, I do not think anyone should be clearing out the tempdb every night but rather should do so in a state of low activity.  

That means they have to check activity before doing so.

I was just speaking to a Director of a data center and he know next to nothing about the functions of the Tempdb database.

Almost as little as I know perhaps.  
Here are some guidelines.

It is a best practice to proactively monitor the normal usage of Tempdb and set the size accordingly. If this is one off case where the Tempdb has grown to such a size and its a PROD env, I would restart SQL Server Services during weekly maintenance. There after Tempdb would go back to its configured size.

Shrinking the file is fine as long as Tempdb is not being used, else existing transactions may be impacted from performance point of view due to blockings and deadlocks.

Cleaning procedure cache, buffer caches etc will have negative impact on the database performance itself until those are not re-created. I would not do this on PROD.

Perhaps James will jump in here and provide more insight.




__________________
Curt Spanburgh
0
wobble_wobble

Avatar / Picture

Associate Troublemaker Apprentice
Registered:
Posts: 832
Reply with quote  #4 
My last job we had 4 tempDB files for the 4 biggest Databases with the most queries.
2 per disk (they were vmdk, but on well provisioned disk)
we had been told the tempDB was where the SQL Server did it's queries against a database.
We had some big queries against these databases and putting in multiple tempDB'S was supposed to improve performance.
Some of the queries was up to 6000 lines of code at the time.

So was this right/ better practice.
Or could we have done the above for similar results/ maybe some results?

__________________
Have you tried turning it off and walking away? The next person can fix it!

New to the forum? Read this
0
Infradeploy

Avatar / Picture

Senior Member
Registered:
Posts: 166
Reply with quote  #5 
Deleted because I need to read first before I state the obvious
__________________
Have SpaceSuit, Will Travel

0
wobble_wobble

Avatar / Picture

Associate Troublemaker Apprentice
Registered:
Posts: 832
Reply with quote  #6 
Now we're suckling diesel
__________________
Have you tried turning it off and walking away? The next person can fix it!

New to the forum? Read this
0
cspanburgh

Avatar / Picture

Senior Member
Registered:
Posts: 219
Reply with quote  #7 
Within the files of the Tempdb there is allocated extents with some of the pages containing metadata.

Like I said before, clearing it is not something to be done all the time.  

Many people reboot the server to clear it if it gets too big.
But you can clear it or you can reduce it.  You can even reduce it from SSMS.  But you have to be careful when you do this.  The best thing is to make sure you get the size right.  Even for a two proc server I would create 4 files within the tempdb.

Allocation of resources and wait states are involved.   I like the script above because it's a quick tool.

Just like when you have to kill a SPID.


__________________
Curt Spanburgh
0
Infradeploy

Avatar / Picture

Senior Member
Registered:
Posts: 166
Reply with quote  #8 
Thanks Curr,, oh and missed the previous posts entirly last night.
__________________
Have SpaceSuit, Will Travel

0
JamesNT

Senior Member
Registered:
Posts: 142
Reply with quote  #9 
Curt is correct.  Most admins ignore tempdb and doing so can cost you.  Directly from MSDN:

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

  • Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.

  • Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.

  • Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.

  • Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.

So for the vast majority of implementations, leaving tempdb where it is with all the default settings is fine.  But with databases that have stored procs or other code creating lots of temporary variables, cursors, and so forth, need to put tempdb on its own disk and treat it more like the transaction log.

JamesNT


__________________
I miss Windows NT 4.0 Service Pack 4.
0
cspanburgh

Avatar / Picture

Senior Member
Registered:
Posts: 219
Reply with quote  #10 
Thanks James.

A few other little bits of advice are on a site called DBA diaries.

If you have checked for activity and found almost none, you can shrink the Tempdb if you have a need and that need would often be a past misconfiguration of the server.

DBCC SHRINKFILE(tempdev, 5);
This would shrink the first file within the tempdb database.

If you have more than one file, use this example:

 DBCC SHRINKDATABASE(tempdb, ‘target_percentage_of_free_space’);

You can then shrink tempdb with the command to leave 10% of free space   DBCC SHRINKDATABASE(tempdb, 10); 



__________________
Curt Spanburgh
0
Previous Topic | Next Topic
Print
Reply

Quick Navigation:

Easily create a Forum Website with Website Toolbox.