Created On:  14 March 2012

Problem:

Occasionally the SQL Server Transaction Logs fill up and cannot grow. This can bring down the StarTeam Server as it cannot commit transactions to the database. Is there any method to monitor the Transaction Logs to pre-empt this issue?

Resolution:

The following SQL script (tested on SQL Server 2008) will report the current allocation of Transaction Log space (in Mb) and the current percentage of the logs used for each database on the SQL Server instance.

It can be used to proactively add additional Transaction log files or shrink / truncate existing files before the StarTeam Server fails.

Recommendation: If the percentage used for your StarTeam database exceeds 90%, it may be advisable to consider shrinking the existing Transaction Logs, or adding additional log file resources in the near future.

IF OBJECT_ID('tempdb..#tTransLogInfo') IS NOT NULL DROP TABLE #tTransLogInfo
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'spLogPerformance') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [spLogPerformance]
GO

CREATE PROCEDURE dbo.spLogPerformance AS
DBCC SQLPERF(logspace)
GO

CREATE TABLE #tTransLogInfo (
dbName sysname,
logSize decimal(18,5),
logUsed decimal(18,5),
status INT)

INSERT INTO #tTransLogInfo EXEC spLogPerformance

SELECT
dbName [Database],
logSize [Total Log Space Allocated (Mb)],
CAST(ROUND(logUsed,2) AS DECIMAL(5,2)) [Log Space Used (%)]
FROM #tTransLogInfo
WHERE dbName IN (
/* avoid reporting on 'system' database */
SELECT [name] FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
)
ORDER BY logSize DESC, logUsed DESC


Sample Results:

Incident # n/a