November 7, 2015 at 2:20 am
can anyone pls help me
A log file size of a production database has been increase from 4gb to 150 gb initial size.Now i want to find when it will grow & how much it grow & which transaction is responsible for this.
November 7, 2015 at 4:30 am
It grew over time or all of a sudden?
Did you check open transactions?
November 7, 2015 at 5:18 am
giri10488 (11/7/2015)
can anyone pls help meA log file size of a production database has been increase from 4gb to 150 gb initial size.Now i want to find when it will grow & how much it grow & which transaction is responsible for this.
More info please, recovery model, backup details, dbcc opentran output etc.
😎
November 7, 2015 at 6:36 am
Eirikur Eiriksson (11/7/2015)
giri10488 (11/7/2015)
can anyone pls help meA log file size of a production database has been increase from 4gb to 150 gb initial size.Now i want to find when it will grow & how much it grow & which transaction is responsible for this.
More info please, recovery model, backup details, dbcc opentran output etc.
😎
Especially the recovery model and backup info. Or you can start here: http://qa.sqlservercentral.com/stairway/73776/
November 8, 2015 at 9:23 pm
The most common reason for uncontrolled log growth is being in Full Recovery mode without having any log backups.
November 9, 2015 at 6:01 am
johnwalker10 (11/8/2015)
The most common reason for uncontrolled log growth is being in Full Recovery mode without having any log backups.
That's my guess as well.
November 9, 2015 at 10:32 am
Help up help you by executing the code below on your server and replying to this conversation with the result. Replace the "YourDB" values with your database name.
SET NOCOUNT ON
USE 'YourDB'
GO
EXEC sp_helpdb 'YourDB'
IF(SELECT CONVERT(int,value_in_use) FROM sys.configurations WHERE [name] = 'default trace enabled' ) = 1
BEGIN
DECLARE @curr_tracefilename varchar(500)
, @base_tracefilename varchar(500)
, @indx int;
SELECT @curr_tracefilename = [path]
FROM sys.traces
WHERE is_default = 1;
SET @curr_tracefilename = REVERSE(@curr_tracefilename);
SELECT @indx = PATINDEX('%\%', @curr_tracefilename);
SET @curr_tracefilename = REVERSE(@curr_tracefilename);
SET @base_tracefilename = LEFT( @curr_tracefilename,LEN(@curr_tracefilename) - @indx) + '\log.trc' ;
SELECT (DENSE_RANK() OVER(ORDER BY StartTime DESC))%2 AS [l1]
, CONVERT(int, EventClass) [EventClass]
, DatabaseName
, [Filename]
, msdb.dbo.fn_CreateTimeString((CAST(Duration AS numeric)/1000000.0)) [Duration]
, StartTime
, EndTime
, IntegerData
, (IntegerData*8.0/1024) [ChangeInSize]
FROM ::fn_trace_gettable( @base_tracefilename, default )
WHERE EventClass >= 92
AND EventClass <= 95
ORDER BY StartTime DESC;
END
GO
This script was originally published at DB_RecentFileGrowth[/url]
[font="Verdana"]Sal Young[/font]
[font="Verdana"]MCITP Database Administrator[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply