March 10, 2010 at 11:10 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 10, 2010 at 11:28 pm
Very nice Jason.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 10, 2010 at 11:33 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 10, 2010 at 11:59 pm
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 11, 2010 at 12:07 am
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
March 11, 2010 at 12:13 am
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2010 at 12:30 am
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.
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]intnot null
identity (1, 1) primary key clustered ,
[DATABASE_NAME]sysnamenot null ,
[FILEGROUP_TYPE]nvarchar(4)not null ,
[FILEGROUP_ID]smallintnot null ,
[FILEGROUP]sysnamenot null ,
[FILEID]smallintnot null ,
[FILENAME]sysnamenot null ,
[DISK]nvarchar(1)not null ,
[FILEPATH]nvarchar(260)not null ,
[MAX_FILE_SIZE]intnull ,
[FILE_SIZE]intnot null ,
[FILE_SIZE_USED]intnot null ,
[FILE_SIZE_UNUSED]intnot null ,
[DATA_SIZE]intnot null ,
[DATA_SIZE_USED]intnot null ,
[DATA_SIZE_UNUSED]intnot null ,
[LOG_SIZE]intnot null ,
[LOG_SIZE_USED]intnot null ,
[LOG_SIZE_UNUSED]intnot null ,
)
declare @sqlnvarchar(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]
)
selecttop 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
Agreed that its not the greatest piece of code, but yes effective.
Regards,
Raj
March 11, 2010 at 12:40 am
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2010 at 1:06 am
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
March 11, 2010 at 1:08 am
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2010 at 2:29 am
arr.nagaraj (3/11/2010)
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.
It seems correct to me. The idea is to capture information about the occasional rogue process that consumes an unusual amount of log space and thus causes a log growth. (In any well-designed production system, log growths are very unusual.)
Any process that consumes sufficient log space is almost certain to run long enough to be caught by Jason's arrangement.
I really do not see the benefit in including inactive connections - not much information is available after the query has finished executing anyway. Talking about including 'short transactions' also misses the point - we are trying to capture unusually large log usage after all. Short transactions do not use much log space by definition.
Concerning the 12800% growth thing. That is a very old bug now - see KB917887[/url] and KB919611. There were workarounds to both, were not specific to upgraded SQL Server 2000 databases, and hotfixes were available very quickly, for both RTM and SP1 - so it is not true to say there is no fix pre-SP3. I cannot find a reference to say that the fix was not included in Service Pack 2, but even if it only made it into Service Pack 3 as you suggest, all 2005 production systems should be at least at that level now.
"So What I do is to check how much it has grown, using a few automated scripts, and expand it myself well in advance."
This is does not seem like a best practice. Is there some reason you cannot pre-allocate a log of sufficient size, with well-sized VLFs, and just leave it be?
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 11, 2010 at 2:56 am
Well, I have systems running with SP2 + still facing the issue.
And I know few people who have the same issue even after sp3.
Lemme explain a bit more on how the growth part is done. This scripts I use alert me the log which are 75% or more full. We increase the size at a downtime or at non peak hours. The reason is a file growth happening at peak hour can have a serious performance impact and its a good practice to monitor closely have it done yourself instead of relying on auto grow.
Having said that, as Production DBAs with almost zero knowledge about
application and being responsible for few hundred databases across several servers, its hard to allocate perfectly at one shot. We do by getting the info we have. And when autogrow needs to be done occasionally( probably abt once in 2 months) we do.
Regards,
Raj
March 11, 2010 at 3:04 am
It seems correct to me. The idea is to capture information about the occasional rogue process that consumes an unusual amount of log space and thus causes a log growth. (In any well-designed production system, log growths are very unusual.)
The unfortunate thing is my apps are not well designed and I have very little control over it. And My idea is this. To play it safe and instead of regretting on
missed data because of a miss on the time frame, I am better of having all the info at little cost 😉
Regards,
Raj
March 11, 2010 at 3:10 am
arr.nagaraj (3/11/2010)
Well, I have systems running with SP2 + still facing the issue.And I know few people who have the same issue even after sp3.
That really makes no sense at all. Either you have not researched the issue properly and applied the correct hotfix, or the servers in question are not at the Service Pack level you think they are. You also do not mention why the work-arounds in the KB articles will not work for you.
arr.nagaraj (3/11/2010)
Lemme explain a bit more on how the growth part is done. This scripts I use alert me the log which are 75% or more full. We increase the size at a downtime or at non peak hours....<snip>
I am not really all that concerned about how you run your databases.
You said yourself it was a digression - and therefore not related to the article under discussion.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 11, 2010 at 3:16 am
arr.nagaraj (3/11/2010)
The unfortunate thing is my apps are not well designed and I have very little control over it. And My idea is this. To play it safe and instead of regretting on missed data because of a miss on the time frame, I am better of having all the info at little cost 😉
No. I prefer Jason's approach. Logging every small transaction is just pointless in the context of this article, as I have made clear already. You will just make it harder to find the information you need if and when a crisis occurs.
BTW, it might be nice if you acknowledged the efforts made by the author in putting this article together.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 48 total)
You must be logged in to reply to this topic. Login to reply