32 bit MSSQl 2005 SP3 on a 64bit Windows Server 2003 SP2 host. My
database is 1.5gb, and something unknown happens during the day to
make the size of tempdb go to 1.5gb and log to 21gb (not 2.1, 21). I
want to use Performance Monitor logging to capture what is happening.
What counters can I use to trap this event?

Thanks.

You can get them from the default trace:

http://is.gd/1p0u9

The events are 92 and 93:

http://msdn.microsoft.com/en-us/library/ms191263.aspx
http://msdn.microsoft.com/en-us/library/ms187491.aspx

Also see this article, since the trace events might only lead you to a
stored procedure. This might help explain what activity is actually leading
to the growth.

http://is.gd/1p0B2



On 7/6/09 1:04 PM, in article fbb455l9jv2r98v83db3neo4908thi4229 (AT) 4ax (DOT) com,
"SnapDive" <SnapDive (AT) community (DOT) nospam> wrote:

You can run a SQL agent job regularly to check the tempdb log space usage. In
fact, this can cover the log space usage of all the databases.

The job can run the following code:

create table #LogspaceInfo (
DatabaseName varchar(60),
LogSize real,
LogSpaceUsed real,
StatusInfo int
)

insert into #LogspaceInfo
exec('dbcc sqlperf(logspace)')

If exists (select * from #LogspaceInfo where DatabaseName = 'tempdb' and
LogSpaceUsed > 70) -- or whatever the threshold
Begin
-- add the code to send an email or raiserror here
end

Linchi

"SnapDive" wrote:

That is great info. I tweaked the TSQL a little and have a result set.
I can see a lot of rows with "tmpdb" , "templog" , "12880000"
(duration) , "log" , "log_12.trc" but am not sure how I can track this
down to a stored procedure or command that is causing the growth.
Where can I look next?

Thanks.

SnapDive (SnapDive (AT) community (DOT) nospam) writes:
The autogrow event populate the columns "ApplicationName", "HostName"
and "LoginName". Depending on your environment, they might give you a
hot lead, but they might also only tell you things you know already.

You can search for "Data File Auto Grow Event Class" in Books Online, to
see all columns populated by the Autogrow event. I don't know if they are
included in the default trace, but else you can set up your own trace to
look for autogrow events.

If the columns help you to narrow down the culprits, you can expand your
trace to include RPC:Completed and SQL:BatchCompleted filtered for a
duration of 500 ms or so and also filtered on the application, host and/or
login. (12880000 above is microseconds.) The queries around the autogrow
events are the suspects.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hello Snap

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

From your description, I understand that you would like to analysis the SQL
Server Profiler Trace to figure out why the size of the tempDB grew bigger.
If I have misunderstood, please let me know.

After reviewing the post history, I found Erland has give you a detail
answer. Thank you,Erland. besides, if it is convenient to you, please send
me an email, I would like to help you to analysis the profiler trace. my
email address is v-fathan (AT) microsoft (DOT) online.com (please remove "online").

Besides, since the profiler trace you have is generated by SQL Server
automatically, it is possible that some important information is not
captured. Therefore, if the information is not enough for us to figure out
the root cause of the issue, we might need to monitor the issue and while
the issue happens again, try to capture a new Profiler Trace which we could
configure to collect informatin requested.

If there is anything unclear, please do not hesitate to let me know. Have a
nice day!

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subs.../aa948874.aspx

================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

The problem is not tempdb mdf, it is the transaction log (ldf) that
grows to 21gb and never shrinks.



On Tue, 07 Jul 2009 09:19:50 GMT, v-fathan (AT) online (DOT) microsoft.com (Mark
Han[MSFT]) wrote:

The log file itself will never shrink on its own. Nor should you want it
to. If it is just going to grow to 21 GB again, then you don't really gain
anything by shrinking it for a short period of time. You are better off
letting the system reuse the existing space than shrink/grow/shrink/grow all
the time. Whenever you have an autogrowth event, everything has to wait for
it to finish... And you pretty much guarantee that will happen if you shrink
it to the smallest size possible.

http://tr.im/StopShrinking

A




On 7/8/09 10:53 AM, in article 4nc9551mgqpah157qv6a6c6urn2qelur3d (AT) 4ax (DOT) com,
"SnapDive" <SnapDive (AT) community (DOT) nospam> wrote:

I have had some back and forth about this recently.

There is not need [in my unhumble opinion] for your log to ever grow
that large [except under rare conditions] if you are managing the log
*properly.* How does one manage the log? By running a regularly
scheduled job that will either backup the log [for full recovery
model] or truncate the log [for simple recovery].

I can't believe that this is still a question people ask, as this has
been unchanged since database dinosaurs like me started walking the
earth.

Any further Q's, just A.

[The extra 20GB does not buy you anything. OK?]

Hi Steve

Thank you for the update. according to your desription, i understand that
the current issue is that the log file of the temdb growed bigger. So, in
the post, i will help to resolve the issue to let the log file of the
tempdb becomes normal.

in order to resolve the issue, we need to analysis the following
information. my email address is v-fathan (AT) microsoft (DOT) online.com(please
remove online)
1 SQL Server Error log
2 try to use DBCC shrink command to reduce the size of the log file
3 run the following command and tell me the result of the result.
sp_helpdb database name

se database name
go
exec sp_spaceused @updateusage='true'
go

DBCC SQLPERF('logspace')

select @@TRANCOUNT.

4 to figure out which the transaction is so large that the log file might
use up the available disk space and cause the "transaction log full" type
of error message such as Error 9002. So please run the following and send
the result with TXT format to me.
" Open ssms to connect to SQL Server instance.
" "ctrl+T"
" Run
select * from sys.dm_tran_database_transactions,
sys.dm_tran_session_transactions, sys.dm_exec_requests
where
(sys.dm_tran_database_transactions.transaction_id= sys.dm_tran_session_transa
ctions.transaction_id) and
(sys.dm_exec_requests.session_id=sys.dm_tran_sessi on_transactions.session_id
)

5 Also, run select * from sys.databases and send the result with TXT format
to me

I look forward to your reply.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======