LOg file growth

Question

LOg file growth

ramyours2003

SSChampion

Points: 12268
More actions
December 17, 2019 at 3:46 pm

#3708512

Today log drive is out of space in secondary server (AG) i need to analyse which database log file is causing the issue , any quwery to find the details ?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LearningDBA SSCertifiable Points: 6609 More actions · Answer 1

Try this

 IF (OBJECT_ID('tempdb..#DatabaseSize')) IS NOT NULL DROP TABLE #DatabaseSize

 CREATE TABLE #DatabaseSize ( 
DatabaseName VARCHAR(255),
FileLogicalName VARCHAR(255),
FilePhysicalName VARCHAR(1000),
AllocatedGB NUMERIC(12,2),
UsedGB NUMERIC(12,2))

   
  DECLARE @ServerName VARCHAR(255)
DECLARE @CurrentDB VARCHAR(255)
DECLARE @SQL VARCHAR(MAX)
DECLARE @DBName VARCHAR(100)


SELECT @ServerName = CONVERT(VARCHAR(255),SERVERPROPERTY('ServerName'))
SELECT @DBName = name FROM sys.databases WHERE database_id > 4

declare database_list CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT name
FROM sys.databases
WHERE STATE = 0 --Online
AND source_database_id IS NULL

OPEN database_list
FETCH NEXT FROM database_list INTO @CurrentDB

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = '
USE ['+@CurrentDB+']
INSERT INTO #DatabaseSize
SELECT '''+@CurrentDB+''' AS DatabaseName
, NAME AS FileLogicalName
, physical_name AS FilePhysicalName
, CONVERT(NUMERIC(10, 2), size / 128.0 / 1024) AS AllocatedGB
, CONVERT(NUMERIC(10, 2), CAST(FILEPROPERTY(NAME, ''SpaceUsed'') AS INT) / 128.0 / 1024) AS UsedGB

FROM ['+@CurrentDB+'].sys.database_files
WHERE CONVERT(NUMERIC(10, 2), CAST(FILEPROPERTY(NAME, ''SpaceUsed'') AS INT) / 128.0 / 1024) > 10
and physical_name like ''%ldf''
'
EXEC(@SQL) 
    

FETCH NEXT FROM database_list INTO @CurrentDB
END
CLOSE database_list
DEALLOCATE database_list

select * from #DatabaseSize

This reply was modified 4 years, 11 months ago by LearningDBA.

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

If the disk filled up because of file autogrowths, you can see the recent history of these in the default trace. Try this script:

DECLARE @path NVARCHAR(260);
SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
  FROM    sys.traces
  WHERE   is_default = 1;

SELECT td.DatabaseName, td.Filename, te.name AS Event, (IntegerData*8)/1024 AS Change_MB, td.StartTime, td.EndTime,
    td.LoginName, td.HostName, td.ApplicationName, td.spid, td.ClientProcessID, td.IsSystem, td.SqlHandle, td.TextData
  FROM sys.fn_trace_gettable(@path, DEFAULT) td
    INNER JOIN sys.trace_events te ON td.EventClass = te.trace_event_id
  WHERE td.EventClass IN (92,93)
  ORDER BY td.StartTime;