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 ?

• LearningDBA

  SSCertifiable

  Points: 6609

  More actions

  December 17, 2019 at 5:41 pm

  #3708558

  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 5 years 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

  December 17, 2019 at 8:03 pm

  #3708601

  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;