TempDB log growth

Question

Post reply

TempDB log growth

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Mani-584606 SSChampion Points: 11788 More actions · Answer 1

Thanks

Query1:

use tempdb

go

SELECT OBJECT_NAME(id), rowcnt

FROM tempdb..sysindexes

WHERE OBJECT_NAME(id) LIKE '#%'

ORDER BY rowcnt DESC

Results:

328 row(s) affected)

#5629CD9C0

#58DCE86C0

#59511E610

#5A45429A0

#5BB955170

#Temp_______________________________________________________________________________________________________________0000000046480

#5CECA51B0

#5DE0C9540

#5E15D37E0

#5E95C1C20

Query2:

DBCC OPENTRAN('tempdb')

Results:

No active open transactions.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

We have currenly have 3 content databases as below:

ContentDB1 of Size 10 GB

ContentDB2 of size 4GB

ContentDB3 of size 3GB--> This is the MOST active content database and the other 2 Content databases are less used.

For this SQL Instance, I have set the TempDb initial data file size as 15 GB. But it never used more than 20 MB and I set the initial log file size as 20 GB and in lessthan 1 month, the log file size reached to 10 GB. So I'm wondering based on what factors I can increase the size of the tempdb log file? and how much big it can grow?

FOr 3gb COntent database, the log file of tempdb is going to 10 GB and more how much it can grow for a 100 GB content database? Any body experienced this growth for Tempdb for Share point databases?

any body have a 100 GB content database, please let me know the log file of TempDB?

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Mani-584606 (5/4/2010)
Thanks
Query1:
use tempdb
go
SELECT OBJECT_NAME(id), rowcnt
FROM tempdb..sysindexes
WHERE OBJECT_NAME(id) LIKE '#%'
ORDER BY rowcnt DESC
Results:
328 row(s) affected)
#5629CD9C0
#58DCE86C0
#59511E610
#5A45429A0
#5BB955170
#5BB955170
#Temp_______________________________________________________________________________________________________________0000000046480
#Temp_______________________________________________________________________________________________________________0000000046480
#5CECA51B0
#5DE0C9540
#5E15D37E0
#5E95C1C20
Query2:
DBCC OPENTRAN('tempdb')
Results:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
We have currenly have 3 content databases as below:
ContentDB1 of Size 10 GB
ContentDB2 of size 4GB
ContentDB3 of size 3GB--> This is the MOST active content database and the other 2 Content databases are less used.
For this SQL Instance, I have set the TempDb initial data file size as 15 GB. But it never used more than 20 MB and I set the initial log file size as 20 GB and in lessthan 1 month, the log file size reached to 10 GB. So I'm wondering based on what factors I can increase the size of the tempdb log file? and how much big it can grow?
FOr 3gb COntent database, the log file of tempdb is going to 10 GB and more how much it can grow for a 100 GB content database? Any body experienced this growth for Tempdb for Share point databases?
any body have a 100 GB content database, please let me know the log file of TempDB?
Thanks

Those objects do not appear to be doing much. Have you been able to trap the query yet that is causing so much activity?

I would set up the sql agent job to fire as often as possible.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Mani-584606 SSChampion Points: 11788 More actions · Answer 3

Those objects do not appear to be doing much. Have you been able to trap the query yet that is causing so much activity?
I would set up the sql agent job to fire as often as possible.

You are talking about the below query to run from the SQLAgent job to trap the query that causing the tempdb log file size to grow?

SELECT [Spid] = session_Id

, [Database] = DB_NAME(sp.dbid)

, [User] = nt_username

, [Individual Query] = SUBSTRING (qt.text,

er.statement_start_offset/2,

(CASE WHEN er.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE er.statement_end_offset END -

er.statement_start_offset)/2)

,[Parent Query] = qt.text

FROM sys.dm_exec_requests er

INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt

Could you please advice me on how to set up that SQLAgent job and fire alert when the query causing log file size to grow occurs.

thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Mani-584606 (5/4/2010)
Those objects do not appear to be doing much. Have you been able to trap the query yet that is causing so much activity?
I would set up the sql agent job to fire as often as possible.
You are talking about the below query to run from the SQLAgent job to trap the query that causing the tempdb log file size to grow?
SELECT [Spid] = session_Id
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Individual Query] = SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END -
er.statement_start_offset)/2)
,[Parent Query] = qt.text
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
Could you please advice me on how to set up that SQLAgent job and fire alert when the query causing log file size to grow occurs.
thanks

I was referring to the queries and instructions available in the article that I suggested. Look at method 2 in the article.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events