- 0 minutes to read

Remove historical Log Databases

Easily automate the safe removal of historical Nodinite Log Databases that are no longer required by your business. This guide empowers you to:

✅ Maintain compliance and optimize storage with automated cleanup
✅ Reduce manual DBA workload and risk of human error
✅ Follow best practices for data retention and system stability
✅ Use SQL Server jobs for reliable, repeatable maintenance

Info

On this page, you will learn how to remove historical Nodinite Log Databases using a job in SQL Server.

Nodinite automatically creates new databases when the following System Parameters are met:

Important

Nodinite does NOT automatically remove Log Databases. You must establish a routine to remove these according to your policy. Dropping Log Databases carries a high risk, including potential data loss and system instability. Always verify you are removing the correct set and use your own discretion.

A SQL DBA with the proper authority can use the sample scripts on this page to manually or automatically clean out historical Log Databases as part of planned maintenance. Using a SQL job ensures reliability and consistency for your retention policy.

Find the oldest number of days to keep messages as defined by the system

It is safe to remove empty old Log Databases. However, even empty historical Log Databases consume disk space and create overhead, including CPU, disk I/O, and backup pressure. This process is designed to minimize risk and optimize performance.

Data retention in the Log Databases is set using System parameters. Log Events are individually removed based on the Message Type. The following System parameters specify how long to keep events and data by default:

Delete Log Databases from SQL Server

Use the following scripts to create or execute the script to remove Log Databases older than the specified time (for example, 180 days) from the Nodinite instance.

SQL Variable Description Example
@prefix The prefix part of Nodinite Log Databases NodiniteLog_Prod_
@configDB The name of the Nodinite Configuration Database NodiniteConfig_Prod
days The number of days to go back in time, a positive integer 180

Follow the sequence of steps as outlined to ensure a successful outcome.

# On success action On failure Action Comment
Step 1 Go to the next step Quit the job reporting failure Remove backup history and delete Old Nodinite Log Databases from SQL Server
Step 2 Quit the job reporting success Quit the job reporting failure Remove Log Database entries from the LogTables table

Step 1: Remove backup history and delete Old Nodinite Log Databases from SQL Server

This step removes the Log Databases from SQL Server.

-- NOTE: You MUST ensure the DECLARE part is the same in Step 1 and Step 2
DECLARE	@prefix NVARCHAR(255) =  'NodiniteLog_Prod_',
	@configDB NVARCHAR(255) = 'NodiniteConfig_Prod',
	@days INT = 180 -- NOTE: Must be a positive integer

DECLARE @sql NVARCHAR(MAX) = 'USE [master]' + CHAR(13)+CHAR(10)  

;WITH nodinitelogdatabases ([Database]) as
(SELECT [Database] From LogTables LT Where LT.[Database] LIKE (@prefix + '%') AND LT.[EndDate] IS NOT NULL AND LT.[EndDate] < DATEADD(DD, @days * -1, GetDate()) AND LT.[IsRemoteServer] = 0 )
	SELECT @sql += 
		'EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = [' + l.[Database] + ']' + CHAR(13)+CHAR(10) +
		'ALTER DATABASE [' + l.[Database] + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE' + CHAR(13)+CHAR(10) +
		'DROP DATABASE [' + l.[Database] + ']' +  CHAR(13)+CHAR(10)
FROM nodinitelogdatabases l
ORDER BY [Database]
PRINT '-- Number of Log Databases to remove: ' + CAST(@@Rowcount AS VARCHAR(18))

PRINT @sql
-- EXEC sp_executesql @sql

Important

The last line is commented out. Test the output first, then uncomment the last line to perform the operation.

Step 2: Remove Log Database entries from the LogTables table

This step removes the information about Log Databases from Nodinite.

-- NOTE: You MUST ensure the DECLARE part is the same in Step 1 and Step 2
DECLARE	@prefix NVARCHAR(255) =  'NodiniteLog_Prod_',
	@configDB NVARCHAR(255) = 'NodiniteConfig_Prod',
	@days INT = 180 -- NOTE: Must be a positive integer

DECLARE @sql NVARCHAR(MAX)

SELECT @sql = 
	'USE [' + @configDB + ']' + CHAR(13)+CHAR(10) +
	'DELETE FROM LogTables Where [Database] LIKE (''' + @prefix + '%'') AND [EndDate] IS NOT NULL AND [EndDate] < DATEADD(DD, ' + CAST(@days AS VARCHAR(18)) + ' * -1, GetDate()) AND [IsRemoteServer] = 0' + CHAR(13)+CHAR(10)  

PRINT @sql
-- EXEC sp_executesql @sql

Important

The last line is commented out. Test the output first, then uncomment the last line to perform the operation.

Next Step

Microsoft SQL Server Options

Log Databases