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 2012 Forums
 SQL Server Administration (2012)
 DATABASE TEMPDB

Author  Topic 

wided
Posting Yak Master

218 Posts

Posted - 2015-04-22 : 12:08:56

i have a problem with the tempdb database
the log file grows at every transaction

help please

i've post this query on SQL2005

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-22 : 12:21:26
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.

Most operations in tempdb are not logged. Operations on temporary tables, table variables, table-valued functions, and user-defined tables are logged. These are called user objects. Sort operations in tempdb also require logging for activities related to page allocation. The log size requirement depends on two factors: how long it is necessary to keep the log and how many log records are generated during this time.

Since log truncation can become a contention point in a heavily loaded system with a small log file, make the tempdb log file large enough to avoid truncating logs frequently. For example, if 50 MB of the log file can be generated per second in the system, the log size should be at least 500 MB so that a log truncation is performed about every 10 seconds.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-04-22 : 13:42:31
Monitor the tempdb to see what activity is causing the growth - read here for details : http://www.sqlserver-dba.com/2013/03/monitor-sql-server-tempdb-usage.html
Also, check the growth rates are set at the relevant rate based on intended usage

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-23 : 03:18:08
quote:
Originally posted by gbritton

Operations on ... table variables ... are logged


I may be misunderstanding, but I thought table variables were not logged?

e.g.

CREATE TABLE #TempTable
(
Foo varchar(10)
)

DECLARE @TableVar TABLE
(
Bar varchar(10)
)

BEGIN TRANSACTION

INSERT INTO #TempTable
SELECT 'aaa'
UNION ALL SELECT 'bbb'

INSERT INTO @TableVar
SELECT *
FROM #TempTable

ROLLBACK

PRINT '#TempTable:'
SELECT *
FROM #TempTable

PRINT '@TableVar:'
SELECT *
FROM @TableVar

DROP TABLE #TempTable

which displays

#TempTable:
Foo
----------

(0 row(s) affected)

@TableVar:
Bar
----------
aaa
bbb

(2 row(s) affected)
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-23 : 10:04:25
You're quite right, K
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-23 : 12:23:20
Sorry, just Dotting-T's and Crossing-I's
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-29 : 18:27:40
quote:
Originally posted by gbrittonTemporary tables and stored procedures are dropped automatically on disconnect


I don't believe SQL goes to the trouble of actually dropping all the objects. As you pointed out, tempdb starts empty every time, so it doesn't need to.

quote:
Operations on ... [and] table-valued functions are logged.


I don't believe these are logged either; indeed, I'm not sure exactly what could be "logged" for TVFs.

quote:
Since log contention can become a contention point in a heavily loaded system with a small log file, make the tempdb log file large enough to avoid truncating logs frequently. For example, if 50 MB of the log file can be generated per second in the system, the log size should be at least 500 MB so that a log truncation is performed about every 10 seconds.


I believe the tempdb log is truncated at ~70% full; SQL doesn't wait until it gets to 100% full, as that would cause a noticeable delay to activity.
Go to Top of Page
   

- Advertisement -