Tempdb information
The tempdb database is a temporary workspace and SQL Server uses tempdb to perform many tasks which are given below:
- Storage of explicitly created temporary tables
- Worktables that hold intermediate results that are created during query processing and sorting
- Materialized static cursors
- Storage of version records when snapshot isolation levels or read-committed snapshot isolation levels are used
In the SQL, we need to pull
the data into temporary tables from the database to do more actions to meet the
business requirements. These activities are the part of our day to day jobs which
are the main root cause for the growing temporary database. Tempdb can grow very large on some systems if a lot of ‘dirty’ queries are run that sluff a lot of data into tempdb. We should be
habitual to drop these temp tables after accomplish the tasks.
If our temporary database
is full then we should need to look and carefully examine our functions, views,
stored procedures and other data components. Test run each of them with
analyzer to know how much does it take to query the data needed. If it returns
on a longer time than expected, revise the codes to simplify.
How to Find out the correct size of tempdb?
To find the correct size
of tempdb files after a shrink operation, execute the following statement in
SQL Server Management Studio:
USE TEMPDB
GO
SELECT NAME,
PHYSICAL_NAME, TYPE_DESC,
---- convert into KB
ACTUALSIZEKB=(SIZE*8)
FROM
SYS.DATABASE_FILES
|
Many programmers don’t
consider the load of the database server when executing queries which is not a
right approach. If you think that your tempDB is growing and taking a lots of the
space of your disk then you should move your database to another location
because-
- TempDB grows big and the existing drive does not have enough space.
- Moving TempDB to another file group which is on different physical drive helps to improve database disk read, as they can be read simultaneously.
Shrink tempdb
We can use to shrink the tempdb database in Microsoft SQL Server where tempdb resets to its configured size after each restart of the instance of SQL Server. A limitation of below
method is that it operates only on the default tempdb logical files tempdev and
templog. If more files are added to tempdb, you can shrink them after you
restart SQL Server as a service. All tempdb files are re-created during start-up.
ALTER DATABASE
tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = target_size_in_MB)
--Desired target
size for the data file
ALTER DATABASE tempdb MODIFY
FILE
(NAME = 'templog', SIZE = target_size_in_MB)
--Desired target
size for the log file
/*
Note: The file size, max size cannot be
greater than 2147483647 in units of a page size.
The file growth cannot be greater than
2147483647 in units of both page size and percentage.
*/
|
Move TempDB from one drive to another drive
Before move the TempDB to another location, we should make sure that TempDB is set to autogrow and do not set a maximum size for TempDB. We need to check the file size of the tempdb becasue SQL Server locks tempdb while it is expanding and If the current drive is too full to allow autogrow events, then arrange a bigger drive, or add files to TempDB on another device.
USE master
GO
---- change the
location of tempdev file
ALTER DATABASE
TempDB MODIFY FILE
(NAME =
tempdev, FILENAME
= 'd:datatempdb.mdf')
GO
---- change the
location of templog file
ALTER DATABASE
TempDB MODIFY FILE
(NAME =
templog, FILENAME
= 'd:datatemplog.ldf')
GO
|
The above commands are capable to change the physical location of the tempdb but no changes are made to TempDB till SQL Server restarts. So, we need to stop and restart SQL Server and after that it will create TempDB files in new locations.
No comments:
Post a Comment