Log Growing Pains

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jason Brimhall » Log Growing Pains

49 posts, Page 1 of 5

1 2 3 4 5 »»»

Log Growing Pains

Rate Topic

Display Mode

Topic Options

Author

Message

SQLRNNR

Posted Wednesday, March 10, 2010 11:10 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

Comments posted to this topic are about the item Log Growing Pains

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #880714

Paul White

Posted Wednesday, March 10, 2010 11:28 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:03 AM
Points: 10,990, Visits: 10,543

Very nice Jason.

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #880727

SQLRNNR

Posted Wednesday, March 10, 2010 11:33 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

Thanks Paul. After submitting the article and taking another look at it, I found a few things that could have been changed to improve the scripts. I bet they will get pointed out. Wink

Post #880730

Paul White

Posted Wednesday, March 10, 2010 11:59 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:03 AM
Points: 10,990, Visits: 10,543

CirquedeSQLeil (3/10/2010)

Thanks Paul. After submitting the article and taking another look at it, I found a few things that could have been changed to improve the scripts. I bet they will get pointed out. Wink

Inevitably Wink

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #880743

arr.nagaraj

Posted Thursday, March 11, 2010 12:07 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:27 AM
Points: 483, Visits: 1,191

Hi Jason,

The query you used to capture tsqls doesnt capture sleeping connections. Was that intentional? I would prefer to grab the sleeping ones as well so that I grab all connections that have completed and not closed.

Regards,
Raj

Strictlysql.blogspot.com

Post #880747

SQLRNNR

Posted Thursday, March 11, 2010 12:13 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

arr.nagaraj (3/11/2010)

The query was designed to capture executing / active queries in the attempt to find what is actively causing log growths. Thus sleeping connections was not considered in the design of the query.

You are welcome to make that adjustment to suit your needs. If you get it to work, I encourage you to post it back to the thread for all to gain.

Post #880752

arr.nagaraj

Posted Thursday, March 11, 2010 12:30 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:27 AM
Points: 483, Visits: 1,191

Hi Jason,

The reason i felt getting sleeping ones are also better is because we would increase the chances of finding the actual queries. And at times if we have a system which has large number of short transactions , it can be fairly effective.

Second point from me is use fileproperty function instead of just the perfmon counter. Reason is it can monitor the Data file used and left and log file used and left.
[code = "plain"]
USE [dbadb]
GO
/****** Object: StoredProcedure [dbo].[get_db_sizes] Script Date: 03/11/2010 15:19:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[get_db_sizes]
AS

if exists ( select * from tempdb.dbo.sysobjects o
where o.xtype in ('U') and o.id = object_id( N'tempdb..#DB_FILE_INFO' ))
drop table #DB_FILE_INFO

set nocount on

create table #DB_FILE_INFO (
[ID] int not null
identity (1, 1) primary key clustered ,
[DATABASE_NAME] sysname not null ,
[FILEGROUP_TYPE] nvarchar(4) not null ,
[FILEGROUP_ID] smallint not null ,
[FILEGROUP] sysname not null ,
[FILEID] smallint not null ,
[FILENAME] sysname not null ,
[DISK] nvarchar(1) not null ,
[FILEPATH] nvarchar(260) not null ,
[MAX_FILE_SIZE] int null ,
[FILE_SIZE] int not null ,
[FILE_SIZE_USED] int not null ,
[FILE_SIZE_UNUSED] int not null ,
[DATA_SIZE] int not null ,
[DATA_SIZE_USED] int not null ,
[DATA_SIZE_UNUSED] int not null ,
[LOG_SIZE] int not null ,
[LOG_SIZE_USED] int not null ,
[LOG_SIZE_UNUSED] int not null ,
)

declare @sql nvarchar(4000)
set @sql =
'use ['+'?'+'] ;
if db_name() <> N''?'' goto Error_Exit

insert into #DB_FILE_INFO
(
[DATABASE_NAME],
[FILEGROUP_TYPE],
[FILEGROUP_ID],
[FILEGROUP],
[FILEID],
[FILENAME],
[DISK],
[FILEPATH],
[MAX_FILE_SIZE],
[FILE_SIZE],
[FILE_SIZE_USED],
[FILE_SIZE_UNUSED],
[DATA_SIZE],
[DATA_SIZE_USED],
[DATA_SIZE_UNUSED],
[LOG_SIZE],
[LOG_SIZE_USED],
[LOG_SIZE_UNUSED]
)
select top 100 percent
[DATABASE_NAME] = db_name(),
[FILEGROUP_TYPE] = case when a.groupid = 0 then ''Log'' else ''Data'' end,
[FILEGROUP_ID] = a.groupid,
a.[FILEGROUP],
[FILEID] = a.fileid,
[FILENAME] = a.name,
[DISK] = upper(substring(a.filename,1,1)),
[FILEPATH] = a.filename,
[MAX_FILE_SIZE] =
convert(int,round(
(case a.maxsize when -1 then null else a.maxsize end*1.000)/128.000
,0)),
[FILE_SIZE] = a.[fl_size],
[FILE_SIZE_USED] = a.[fl_used],
[FILE_SIZE_UNUSED] = a.[fl_unused],
[DATA_SIZE] = case when a.groupid <> 0 then a.[fl_size] else 0 end,
[DATA_SIZE_USED] = case when a.groupid <> 0 then a.[fl_used] else 0 end,
[DATA_SIZE_UNUSED] = case when a.groupid <> 0 then a.[fl_unused] else 0 end,
[LOG_SIZE] = case when a.groupid = 0 then a.[fl_size] else 0 end,
[LOG_SIZE_USED] = case when a.groupid = 0 then a.[fl_used] else 0 end,
[LOG_SIZE_UNUSED] = case when a.groupid = 0 then a.[fl_unused] else 0 end
from
(
Select
aa.*,
[FILEGROUP] = isnull(bb.groupname,''''),
-- All sizes are calculated in MB
[fl_size] =
convert(int,round((aa.size*1.000)/128.000,0)),
[fl_used] =
convert(int,round(fileproperty(aa.name,''SpaceUsed'')/128.000,0)),
[fl_unused] =
convert(int,round((aa.size-fileproperty(aa.name,''SpaceUsed''))/128.000,0))
from
dbo.sysfiles aa
left join
dbo.sysfilegroups bb
on ( aa.groupid = bb.groupid )
) a
order by
case when a.groupid = 0 then 0 else 1 end,
a.[FILEGROUP],
a.name

Error_Exit:

'

exec sp_msforeachdb @sql

select database_name,filegroup_type,filename,filepath,file_size,file_size_used
from #DB_FILE_INFO

[/code]

Agreed that its not the greatest piece of code, but yes effective.

Regards,
Raj

Strictlysql.blogspot.com

Post #880761

SQLRNNR

Posted Thursday, March 11, 2010 12:40 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

arr.nagaraj (3/11/2010)

Good point on the short transactions. Also a valid point on the fileproperty. I use the fileproperty type scripts for monitoring my file consumption for different purposes. I didn't feel that knowing that information was essential for this particular article. However, that could be modified as well to suit different needs.

It is possible that a short burst transaction could have caused a log or tempdb growth. My focus was to look for sustained growths. My experience has shown that sustained growths typically come from long running transactions. Many times those transactions finish before somebody can get into the system to find what caused it.

However, it would be more beneficial to trap sleeping transactions as well. I am exploring the use of Event Notifications to do the same sort of thing as presented in this article. That will also provide greater flexibility (as far as frequency in job run). Right now, the script is limited in execution frequency (10 second intervals on SQL 2008, I believe SQL 2005 is limited to once per minute - both are probably adequate to trap those long running transactions that cause the log to grow by gigs at a time).

I appreciate your input.

Post #880766

arr.nagaraj

Posted Thursday, March 11, 2010 1:06 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:27 AM
Points: 483, Visits: 1,191

Hi Jason,

Thanks Jason.

My experience has shown that sustained growths typically come from long running transactions. Many times those transactions finish before somebody can get into the system to find what caused it.

Agreed 100%. The reason I started worrying about small transactions is a different reason altogether. Bit of digression here.When a SQL 2k database is in SQL 2005 , at times suddenly its growth % changes into 12800% causing a disk space full error. this one is a MS bug. Till sp3 there is no fix. So What I do is to check how much it has grown, using a few automated scripts, and expand it myself well in advance.

Regards,
Raj

Strictlysql.blogspot.com

Post #880776

SQLRNNR

Posted Thursday, March 11, 2010 1:08 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

arr.nagaraj (3/11/2010)

Hi Jason,

Thanks Jason.

My experience has shown that sustained growths typically come from long running transactions. Many times those transactions finish before somebody can get into the system to find what caused it.

Agreed - and makes perfect sense.

Post #880779

« Prev Topic | Next Topic »

49 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions