Monitoring Transaction Log Size
-
Just curious as to what methods if any are used out there to monitor the size of log files.
For example after the log file gets to a certain size > 1gig send email,run a log backup,shrink file etc..
Thanks
-
We use dbcc sqlperf (logspace) to return the size of all the log files on the server.
You could schedule this to run each morning or at intervals throughout the day. Store the output in a table and then send emails or run shrink jobs depending on the values you receive.
-
I record the size of the both the data and log files and put the information in a table. That way I can spot trends, work out growth etc.
If the log file grows 'large', it must have used the space at some point be it a 'dbcc reindex' or the application itself. Really you would need to know what is using the space, before you can plan what to do with it.
For instance if it is the application, running a transaction log backup more often would help it filling up and hence extending itself.
If something is growing the log file often (and assumeing you are running transaction log backups) shrinking the log will cause that process to slow down as it will need to extend the file.
Steven
-
We also use sqlperf (logspace) to monitor logfiles.
-
I am new to SQL/Server, could explain how to use sqlperf (logspace) to monitor logfiles?
Thank you,
-
dbcc sqlperf (logspace)
-
I use the filesystemobject via VBScript to check file sizes.
Andy
-
If all you want is to be notified when the log reaches a certain size, you can create an alert based on a SQL Server performance condition. (SQLServer:Databases - Percent log used and/or Log file(s) size(s))
You can then have that alert notify you or start a job.
JM
-
There's lots of ways to do this. I have a couple of different things in place to handle this. I am emailed by a process whenevery any of my transaction logs are above 80% Full (And I catch things happening that probably shouldn't be). You could use DBCC SHOWFILESTATS or DBCC SQLPERF(logspace) to help.
Here is a little script for one DB of mine which may get you started too..
SET NOCOUNT ON
DECLARE @sql_command varchar(255)
DECLARE @SpaceUsed int
CREATE TABLE #TempForLogSpace (DBName varchar(40),
LogSize_MB int,
LogSpaceUsed_pct int,
Status int)
SELECT @sql_command = 'dbcc sqlperf (logspace)'
INSERT #TempForLogSpace
EXEC (@sql_command)
IF ((SELECT tfls.LogSpaceUsed_pct FROM #TempForLogSpace tfls WHERE DBName = 'PRD') > 60)
BEGIN
--
-- do some stuff
--
END
DROP TABLE #TempForLogSpace
SET NOCOUNT OFF
"Keep Your Stick On the Ice" ..Red Green -
With automatic growth of log files, I find that DBCC SQLPERF doesn't quite fit my needs.
The following script identifies any log files that have grown bigger than their corresponding data files. I have this in a stored procedure that sends me an email if there are any results. One of these days when I have some spare time
, I plan to add a parameter to check if the log is more than xx% of the data.
SELECT
sAfl.name as DBName
, sAfl.size as DBSize
, LogSize.Size as LogSize
FROM master..sysaltfiles sAfl
INNER JOIN (
SELECT dbid, size
FROM master..sysaltfiles
WHERE fileid = 2
) as LogSize
ON sAfl.dbid = LogSize.dbid
WHERE sAfl.FileID = 1
AND sAfl.size < LogSize.SizeHope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
, I plan to add a parameter to check if the log is more than xx% of the data.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply