/*
**	File:		ManualIndirectCheckpoint.sql
**
**	Summary:	Run in a job to run indirect checkpoints prior to SQL 2012
**				set the percentage limit before running.
**
**	SQL Server Versions:	2005 onwards
**	----------------------------------------------------------------------------
**	Written by Rainer Unwin (c) 2014
**
**	For more scripts check out http://www.rmuniwn.com
**
**	You may alter this code for your own non-commercial purposes (e.g. in a
**	for-sale commercial tool).
**	You may republish altered code as long as you include this copyright and
**	give due credit, however you must obtain my prior permission before blogging
**	this code.
**
**	this code and information are provided "as is" without warranty of
**	any kind, either expressed or implied, including but not limited
**	to the implied warranties of merchantability and/or fitness for a
**	particular purpose. You use the script at your own risk and should
**	always test in a pre-production or test environment before running
**	in production.
*/
SET NOCOUNT ON;
GO

IF OBJECT_ID(N'TempDB..#loginfo', N'U') IS NOT NULL DROP TABLE #loginfo;
GO

CREATE TABLE #loginfo (
	[Database Name]			SYSNAME
	, [Log Size (MB)]		DECIMAL(19, 6)
	, [Log Space Used (%)]	DECIMAL(7, 5)
	, [Status]				TINYINT
);
GO

DECLARE @DBName	SYSNAME
	, @sqlcmd		NVARCHAR(2000)
	, @Recovery		VARCHAR(128)
	, @UsedPercent	DECIMAL(7, 5)
	, @ChkpntLimit	DECIMAL(7, 5);

SELECT @DBName		= N'test'
	, @sqlcmd		= N'dbcc sqlperf(logspace);'
	, @ChkpntLimit	= 50; -- set log space used limit to trigger checkpoint as a percentage

IF @DBName = N'()' SELECT @DBName = DB_NAME(DB_ID());

SELECT @Recovery = CAST(DATABASEPROPERTYEX(@DBName, 'Recovery') AS VARCHAR);

IF @Recovery = 'SIMPLE'
BEGIN
	INSERT INTO #loginfo
	EXEC sp_executesql @sqlcmd;

	SELECT @UsedPercent = [Log Space Used (%)]
	FROM #loginfo
	WHERE [Database Name] = @DBName;

	IF (@UsedPercent > @ChkpntLimit )
	BEGIN
		RAISERROR ('Checkpoint ran', 10, 1) WITH NOWAIT;
		CHECKPOINT;
	END
	ELSE
	BEGIN
		RAISERROR ('Log usage fine', 10, 1) WITH NOWAIT;
	END;
END
ELSE
BEGIN
	RAISERROR ('Database needs to be in simple recovery for checkpoint to permit log clearing', 10, 1) WITH NOWAIT;
END;
GO