- 14 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 Agent jobs for reliable, repeatable maintenance

Info
On this page, you will learn how to remove historical Nodinite Log Databases by creating an automated job inside SQL Server. You do not need to be a database expert to follow this guide.

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

DaysToSplitDatabaseOn — Create a new database after this many days.
SizeToSplitDatabaseOn — Create a new database when exceeding this size.

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.

Tip
Nodinite can perform shrinking of Log Databases to manage disk space, but this does not remove the database itself. See the System Parameter 'DatabaseMaintenance' for more information on this capability.

Understanding Data Retention

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

Data retention in the Log Databases is governed by 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:

A Log Database is safe to remove once all its data has been purged according to your retention policy and the EndDate for that database has passed. The scripts below automatically detect such databases — you only need to set the variables.

What You Need Before Starting

Make sure you have the following before continuing:

SQL Server Management Studio (SSMS) installed on your computer — Download SSMS free from Microsoft
A SQL Server login with sysadmin privileges on the server that hosts the Nodinite Configuration Database
The SQL Server Agent service is running on that SQL Server instance (check with your DBA if unsure)
The name of your Nodinite Configuration Database — for example, NodiniteConfig_Prod
The prefix of your Nodinite Log Databases — for example, NodiniteLog_Prod_

Warning
SQL Server Express does not include SQL Server Agent. If you are running SQL Server Express (including SQL Server 2025 Express), use a Windows Scheduled Task with sqlcmd as an alternative. Move to Standard/Enterprise only if your operations policy requires SQL Server Agent.

The Scripts

The job uses two T-SQL scripts that must run in sequence. Never swap the order — Step 1 removes the physical databases, Step 2 removes the records from Nodinite. If Step 1 fails, Step 2 must never run.

Script variables

Update the following three variables at the top of both scripts to match your environment before using them in the job:

Variable	Description	Example
`@prefix`	The prefix portion of your Nodinite Log Database names	`NodiniteLog_Prod_`
`@configDB`	The name of the Nodinite Configuration Database	`NodiniteConfig_Prod`
`@days`	How old (in days) a database must be before it is eligible for removal — must be a positive integer	`180`

Tip
Not sure what your prefix and config database are called? Open SSMS, connect, and expand Databases in Object Explorer. Look for databases whose names start with NodiniteLog_ or NodiniteConfig_.

Local vs Remote Log Databases

Before using the scripts, check where your Log Databases physically reside. Run the following query on your Config DB:

SELECT [Database], [Server], [IsRemoteServer] FROM [dbo].[LogTables] ORDER BY [StartDate]

`IsRemoteServer`	Meaning	Which script to use
`0`	The Log Database is on the same SQL Server instance as the Config DB	Run the job on the Config DB server — this is the standard case
`1`	The Log Database is on a different SQL Server instance (connected via a linked server)	Run the job on the remote SQL Server that hosts the Log Databases

Warning
Script A filters AND [IsRemoteServer] = 0 as a safety guard. If all your rows show IsRemoteServer = 1, the script will produce no DROP statements — only USE [master]. Before assuming your databases are truly remote, verify in SSMS whether the Log Databases actually appear on the same server as the Config DB. If they do, the flag is incorrect in Nodinite and must be fixed — see Fix incorrect IsRemoteServer flag below.

Fix Incorrect IsRemoteServer Flag

If your Log Databases are visible on the same SQL Server instance as your Config DB in SSMS, but LogTables shows IsRemoteServer = 1, the Remote Server checkbox was incorrectly ticked when those databases were registered in Nodinite.

Fix this in the Nodinite Web Client:

Navigate to Administration > Settings > Log Databases
Click Edit on each affected Log Database entry
Uncheck the Remote Server checkbox
Click Save
Repeat for all affected entries

Once all entries show IsRemoteServer = 0, re-run the diagnostic query above to confirm, then proceed with the scripts.

Tip
See Add or Manage a Log Database for full details on each field, including the Remote Server checkbox.

Job step execution plan

Job Step	On success	On failure	What it does
Script A	Go to next step	Quit — report failure	Removes the physical Log Databases from SQL Server
Script B	Quit — report success	Quit — report failure	Removes the database records from the Nodinite Configuration Database

Script A: Remove Databases from SQL Server

-- NOTE: @prefix, @configDB, and @days MUST be identical in Script A and Script B
DECLARE @prefix    NVARCHAR(255) = 'NodiniteLog_Prod_',
        @configDB  NVARCHAR(255) = 'NodiniteConfig_Prod',
        @days      INT           = 180 -- Must be a positive integer

-- @dropSql contains the DROP statements that run in master context
DECLARE @dropSql  NVARCHAR(MAX) = 'USE [master]' + CHAR(13) + CHAR(10)

-- @buildSql queries LogTables in the Config DB using a 3-part name so
-- this script can safely run from any database context (e.g. master)
DECLARE @buildSql NVARCHAR(MAX) = N'
SELECT @dropSql +=
    ''EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = ['' + [Database] + '']'' + CHAR(13) + CHAR(10) +
    ''ALTER DATABASE ['' + [Database] + ''] SET SINGLE_USER WITH ROLLBACK IMMEDIATE''          + CHAR(13) + CHAR(10) +
    ''DROP DATABASE [''  + [Database] + '']''                                                   + CHAR(13) + CHAR(10)
FROM [' + @configDB + '].[dbo].[LogTables]
WHERE  [Database]       LIKE N''' + @prefix + '%''
  AND  [EndDate]        IS NOT NULL
  AND  [EndDate]        < DATEADD(DD, ' + CAST(@days AS VARCHAR(18)) + ' * -1, GetDate())
  AND  [IsRemoteServer] = 0
ORDER BY [Database]'

EXEC sp_executesql @buildSql, N'@dropSql NVARCHAR(MAX) OUTPUT', @dropSql OUTPUT

PRINT @dropSql
-- TODO: Remove the two dashes at the start of the next line when adding to the SQL Agent job
-- EXEC sp_executesql @dropSql

Important
The last EXEC line is commented out. When testing manually, leave it commented so the script only prints what it would do. When adding this script to the SQL Agent job, remove the -- comment prefix so the job actually performs the removal. See Step 5 below.

Script B: Remove Entries from LogTables

-- NOTE: @prefix, @configDB, and @days MUST be identical in Script A and Script B
DECLARE @prefix    NVARCHAR(255) = 'NodiniteLog_Prod_',
        @configDB  NVARCHAR(255) = 'NodiniteConfig_Prod',
        @days      INT           = 180 -- 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
-- TODO: Remove the two dashes at the start of the next line when adding to the SQL Agent job
-- EXEC sp_executesql @sql

Important
Same rule applies: leave the EXEC line commented for manual testing. Uncomment it only when adding this script to the SQL Agent job in Step 6.

Step 1: Test the Scripts Manually First

Before you build the job, verify the scripts return the correct list of databases. Skipping this step risks deleting the wrong databases.

Open SQL Server Management Studio (SSMS)
Connect to the SQL Server where the job will run:
- If your Log Databases have IsRemoteServer = 0: connect to the Config DB server
- If your Log Databases have IsRemoteServer = 1: connect to the remote SQL Server that hosts the Log Databases
Click New Query in the toolbar
Paste in Script A exactly as shown above (with the EXEC line commented out)
Update the three variables at the top (@prefix, @configDB, @days) to match your environment
Press F5 to run the query
Click the Messages tab at the bottom — you will see a list of database names that would be deleted and the generated DROP DATABASE statements

Warning
If the Messages tab only shows USE [master] with no DROP DATABASE lines, the most likely causes are:

The IsRemoteServer flag is incorrectly set to 1 in Nodinite — even though the databases are on the same server. Fix this in the Web Client first. See Fix incorrect IsRemoteServer flag.

Your Log Databases are genuinely on a different server — connect SSMS to that server and run the job there instead.

[!IMPORTANT] Review this list carefully. Dropping a database is permanent and irreversible — there is no undo. If a database appears in the list that you want to keep, increase the @days value. Confirm with your team that the listed databases truly contain expired data before continuing.

Once you are satisfied the list is correct, repeat the test for Script B the same way.

Step 2: Open SQL Server Management Studio

Open SQL Server Management Studio (SSMS). If you do not have it, download it here for free
In the Connect to Server dialog:
- Server name: the hostname or IP of the SQL Server that hosts your Nodinite Configuration Database
- Authentication: use Windows Authentication when the server is domain-joined. Use SQL Server Authentication if your DBA gave you a SQL login and password
Click Connect

Tip
If you are unsure of the server name, look it up in the Nodinite Web Client under Administration > Settings > Configuration Database, or contact your DBA.

Step 3: Navigate to SQL Server Agent

SQL Server Agent is the built-in job scheduler in SQL Server. Think of it as Windows Task Scheduler, but built directly into SQL Server and far more reliable for database operations. Learn more about SQL Server Agent

In the Object Explorer panel on the left side of SSMS:

Expand the tree node for your SQL Server instance
Scroll down until you see SQL Server Agent
Expand SQL Server Agent
Right-click Jobs
Click New Job...

Warning
If SQL Server Agent is missing from the tree, either the Agent service is not running or you are connected to a SQL Server Express instance (which does not include Agent). For non-Express editions, ask your DBA to start the SQL Server Agent Windows Service. Open Services (services.msc), find SQL Server Agent (MSSQLSERVER) and set it to Automatic startup.

Step 4: Name the Job

The New Job dialog opens. On the General tab:

Name: Enter a clear and descriptive name. Good examples:
- Nodinite - Remove Old Log Databases - Prod
- Nodinite Log DB Cleanup (Production)
Owner: Set this to a SQL login with sysadmin rights — typically sa or a dedicated DBA service account. Do not use a personal login. If your personal account is ever removed or disabled, the job will stop running silently.
Category: Click the ... button and select Database Maintenance. This is cosmetic but makes it easy to find the job among dozens of others.
Description: Write a short note about what the job does and why. For example:

Removes Nodinite Log Databases older than 180 days. Configured per retention policy. Runs every Sunday at 02:00. Created by [your name] on [date].
Make sure the Enabled checkbox is ticked.

Tip
A good naming convention for jobs is: Product - Action - Environment. For example: Nodinite - Remove Old Log Databases - Prod. When you have many jobs, a consistent naming convention makes maintenance much easier.

Step 5: Add Job Step A — Remove Databases

Click the Steps page on the left side of the New Job dialog, then click New...:

Step name: Step 1 - Remove Old Log Databases from SQL Server
Type: Transact-SQL script (T-SQL)
Run as: Leave as default (SQL Server Agent Service Account)
Database: Select master from the dropdown — the script uses a 3-part database name internally to read from your Config DB, so it can safely run from master context
Command: Paste Script A from above. Before clicking OK, remove the -- comment prefix from the last EXEC line so the script reads:
```
EXEC sp_executesql @dropSql
```
Also update the three variables at the top to match your environment.
Click the Advanced tab within the step dialog and set:
- On success action: Go to the next step
- On failure action: Quit the job reporting failure
Click OK to save the step.

Important
The On failure action setting is critical. Setting it to Quit the job reporting failure ensures that if the physical database removal in Step 1 fails, Step 2 will never run — preventing the situation where Nodinite loses track of databases that still exist on SQL Server.

Step 6: Add Job Step B — Clean Up LogTables

Click New... again to add the second step:

Step name: Step 2 - Remove Log Database entries from LogTables
Type: Transact-SQL script (T-SQL)
Run as: Leave as default
Database: Select your Nodinite Configuration Database from the dropdown (e.g., NodiniteConfig_Prod)
Command: Paste Script B from above. Remove the -- comment prefix from the last EXEC line so the script reads:
```
EXEC sp_executesql @sql
```
Update the three variables to match your environment — they must be identical to the values used in Step 1.
Click the Advanced tab and set:
- On success action: Quit the job reporting success
- On failure action: Quit the job reporting failure
Click OK to save the step.

Tip
You can reorder job steps using the Move step up / Move step down arrows on the Steps page. Step 1 must always come before Step 2.

Step 7: Set Up a Schedule

Without a schedule, the job must be started manually every time. A recurring schedule automates the cleanup.

Click the Schedules page, then click New...:

Name: Give the schedule a descriptive name, for example: Weekly Sunday 02:00
Schedule type: Recurring
Frequency section:
- Occurs: Weekly
- Recurs every: 1 week(s)
- Tick the Sunday checkbox (or whichever day your organization uses as a maintenance window)
Daily frequency section:
- Occurs once at: 02:00:00
Duration section:
- Start date: today's date
- End date: No end date
Click OK to save the schedule.

Tip
Always pick a time when the SQL Server is under low load. Early Sunday morning (01:00–04:00) is the most common choice. Avoid business hours and integration-heavy periods. Ask your team if there is a defined maintenance window.

Note
For more scheduling options — such as running on multiple days or on the last day of the month — see Create a Schedule in SQL Server Agent.

Step 8: Configure Failure Notifications (Recommended)

Getting an alert when the job fails lets you act before a retention problem grows.

Click the Notifications page:

Tick E-mail
Select an operator (an operator is a named email recipient configured in SQL Server Agent)
Set the condition to When the job fails
Click OK

Info
If you do not see any operators in the dropdown, or if email notifications are not yet configured, ask your DBA to set up Database Mail and create an Operator. See Configure Database Mail on Microsoft Learn. You can skip this step for now and check job history manually instead.

Step 9: Save the Job

Click OK at the bottom of the New Job dialog to save.

The job now appears under SQL Server Agent > Jobs in the Object Explorer. Right-click it and select Properties at any time to review or change its configuration.

Step 10: Run the Job Manually to Verify

Before relying on the scheduled run, trigger the job manually once to confirm everything works end-to-end:

In the Object Explorer, expand SQL Server Agent > Jobs
Right-click your job (e.g., Nodinite - Remove Old Log Databases - Prod)
Select Start Job at Step... and choose Step 1 - Remove Old Log Databases from SQL Server
Click Start in the dialog that appears
Wait for the progress indicator to complete — this is usually fast (a few seconds to a minute depending on how many databases are removed)
Click Close

Step 11: Verify the Results

Check that the job completed successfully and that the correct databases were removed:

In the Object Explorer, right-click your job
Select View History
The Job Activity Monitor opens — find your job in the list:
- A green checkmark means the job succeeded
- A red X means the job failed
Click on any run row to expand it and read the output messages from each step
Refresh the Databases node in the Object Explorer — the removed Log Databases should no longer appear
Open the Nodinite Web Client and navigate to Administration > Settings > Log Databases — deleted entries should be gone from the list

Tip
Bookmark the Job Activity Monitor (right-click SQL Server Agent > Monitor Job Activity). It gives you a real-time overview of all job statuses and is the fastest way to spot problems after a maintenance window.

Next Step

Microsoft SQL Server Options

Log Databases System Parameters DatabaseMaintenance

Explore Nodinite for 30 days – completely free

See how Nodinite resolves your technical issues, in a

LIVE Demo!