THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help
in Search

Kevin Kline

Tracking Log File Growth on SQL Server 2000

Gert Drapers, whom I consider to be a super-genius of the SQL Server world and owner of the very useful website http://www.sqldev.net, wrote a useful little script a few years ago that I thought I'd share.  This is a SQL query that will show who and what is causing the log of a database, in this case tempdb, to grow:

USE tempdb
GO

SELECT Operation,
   Context,
   [Log Size] = SUM([Log Record Length])
FROM ::fn_dblog(null, null)
GROUP BY Operation, Context
GO

This dumps the active part of the log in tempdb and provides an aggregate per operation and the context it occured in. The [Object Name] and [Index Name] columns provide info on which object, [Server UID], [UID] and [SPID] can provide user context when applicable.

Cheers,

-Kevin


Published Wednesday, July 11, 2007 4:37 PM by KKline
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jeremy said:

Is there any way i can find out who uploaded a specific set of files onto a server? We have a bit of a problem in my work place with files being loaded onto the server and we can't delete them.

September 23, 2008 1:15 AM

Leave a Comment

(required) 
(optional)
(required) 
Submit

About KKline

Kevin Kline is Technical Strategy Manager for the SQL Server business unit at Quest Software. Kevin was the original architect and dev manager for many of Quest's SQL Server tools. Prior to Quest, Kevin worked as an enterprise DBA for a variety of large corporations and government agencies.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement