At some stage you’ll likely want to check your SQL Server errorlog files using xp_readerrorlog or sp_readerrorlog.
If you haven’t worked on the server before and/or don’t have access to view the file sizes before running the command the below query will help obtain this information.
Why would you be interested in knowing the file sizes before running the command? Well, if the instance hasn’t been restarted in a while and sp_cycle_errorlog hasn’t been run, the log files could be really large and cause potential memory and IO performance issues when you try read them.
DECLARE @enumerrorlogs TABLE ([Log Type] varchar(5), [Archive #] int, [Date] datetime, [Log File Size (Byte)] bigint);
--get sql log file sizes
INSERT INTO @enumerrorlogs ([Archive #], [Date], [Log File Size (Byte)])
EXEC master.sys.sp_enumerrorlogs 1;
UPDATE @enumerrorlogs
SET [Log Type] = 'SQL'
WHERE [Log Type] IS NULL;
--get sql agent log file sizes
INSERT INTO @enumerrorlogs ([Archive #], [Date], [Log File Size (Byte)])
EXEC master.sys.sp_enumerrorlogs 2;
UPDATE @enumerrorlogs
SET [Log Type] = 'Agent'
WHERE [Log Type] IS NULL;
SELECT
a.[Log Type] AS log_type
,a.[Archive #] AS archive
,a.[Date] AS date_last_updated
,CAST(a.[Log File Size (Byte)]/1024/1024.0 AS decimal(20,3)) AS log_size_mb
FROM @enumerrorlogs a
ORDER BY a.[Log Type] DESC, a.[Archive #];