Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Log Growing Pains Expand / Collapse
Author
Message
Posted Wednesday, March 10, 2010 11:10 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:15 PM
Points: 21,229, Visits: 14,935
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


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
Posted Wednesday, March 10, 2010 11:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 11,192, Visits: 11,096
Very nice Jason.



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #880727
Posted Wednesday, March 10, 2010 11:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:15 PM
Points: 21,229, Visits: 14,935
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.



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


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 #880730
Posted Wednesday, March 10, 2010 11:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 11,192, Visits: 11,096
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.

Inevitably




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #880743
Posted Thursday, March 11, 2010 12:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:48 AM
Points: 488, Visits: 1,328
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
Posted Thursday, March 11, 2010 12:13 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:15 PM
Points: 21,229, Visits: 14,935
arr.nagaraj (3/11/2010)
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.



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.




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


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 #880752
Posted Thursday, March 11, 2010 12:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:48 AM
Points: 488, Visits: 1,328
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
Posted Thursday, March 11, 2010 12:40 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:15 PM
Points: 21,229, Visits: 14,935
arr.nagaraj (3/11/2010)
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.




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.




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


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 #880766
Posted Thursday, March 11, 2010 1:06 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:48 AM
Points: 488, Visits: 1,328
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
Posted Thursday, March 11, 2010 1:08 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:15 PM
Points: 21,229, Visits: 14,935
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 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.




Agreed - and makes perfect sense.




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


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 #880779
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse