TlbNlog table occupying huge space - Suggestion

[venkatabuma]

Hi Team,

I am technical back ground of MSSQL DBA. In one of our environment, lansweeper table TlbNlog is occupied of 320GB out of 380 GB total DB size. I am suspecting the purging is not happening. Lansweeper DB hosted on MSSQL Server enterprise edition.

I do not have knowledge on Lansweeper tables information and purging option.

Please suggest what information Tlblog store and which purging option to purge this table.

[ITdude]

Hi

This is an old post but I've referenced it 2ce recently and had to work around an error both times

Problem - tblNtlogMessage table grows and maxes out SQL express 10GB limit

I've reduced the log retention in settings but table size didn't change 

Below used to clear tblNtlog table to -7 days, then delete records from tblNtlogMessage where the EventID is not in tblNtlog or tblHyperVLog
(had to include tblHyperVLog to avoid error [The DELETE statement conflicted with the REFERENCE constraint "FK_tblHyperVLog_tblNtlogMessage"])
**This has not been approved by support - Use with caution**
I haven't seen any issues using it

Delete
FROM [lansweeperdb].[dbo].[tblNtlog]
WHERE [TimeGenerated] < GETDATE()-7

Delete
FROM [lansweeperdb].[dbo].[tblNtlogMessage]
WHERE [MessageID] NOT IN (SELECT DISTINCT [lansweeperdb].[dbo].[tblNtlog].[MessageID] FROM [lansweeperdb].[dbo].[tblNtlog])
AND
[MessageID] NOT IN (SELECT DISTINCT [lansweeperdb].[dbo].[tblHyperVLog].[EventMessageId] FROM [lansweeperdb].[dbo].[tblHyperVLog])

[ghelpdesk]

We did the following to remove excess rows (~100m) from tblntlog which seemed to no longer get removed by the Lansweeper clean-up routines.

There might be better ways? We had to run this multiple times to avoid out of memory errors if we tried to run without a row restriction.

DELETE TOP (3000000) FROM [lansweeperdb].[dbo].[tblNtlog]
WHERE [TimeGenerated] < GETDATE()-180

DELETE FROM [lansweeperdb].[dbo].[tblNtlogMessage]
WHERE [MessageID] NOT IN (SELECT DISTINCT [lansweeperdb].[dbo].[tblNtlog].[MessageID] FROM [lansweeperdb].[dbo].[tblNtlog])

[ghelpdesk]

Is there a script that would delete from tblntlog and tblntlogmessage from a given date/time? ie: manually do the purge after N days process?

Edit: That is, to avoid purging those two tables completely.

[Michael_V]

This might help: http://www.lansweeper.com/kb/147/clearing-tables-to-free-up-space-and-improve-performance.html

3,4:"Truncate logs button"

Afterwards: Step 6 to optimize your database

[Mister_Nobody]

I think you have to delete log then
change "Delete eventlog entries after 21 days" to another (for example, 30).

Deleting of tblntlog doesn't affect the work of LS. It's just store of eventlog of Windows on the assets.

[Mister_Nobody]

The tblNtlog is assigned wih next tables:
tblNtlogFile;
tblNtlogMessage;
tblNtlogSource;
tblNtlogUser.

Documentation is located at http(s)://ls_server/DataDictionary/Default.aspx#1954106002

You can delete only tblNtlog with timegenerated older than 21 days. Other tables are dictionaries.
Hash-function of tblNtlogMessage.hash is unknown for me. IMHO it's only table with possible loosing consistence.

[venkatabuma]

Q1. What about Eventlog scanning?
Scan Warning events.
Scan Information events.
Scan Success audit events.
Scan Failure events. Do you scan all events?

My Query: I am not technical expert or no access to LS. I guess all events are enabled.

My question the table tblNlog had nearly 15 months data i.e., approx 450 days. From my attached LS configuration screen, I see max purge configuration is 90 days.

Let me know your suggestions.

Q2.
I think
1. LS ignores deleting in your environment. You have to write to LS Support.
2. For future logs you can run query
Code: 1 delete from tblntlog where tblNtlog.TimeGenerated > Current_TimeStamp
also it is needed to clear log on assets side.
3. You can clear table by own query.

My Query: I agree with you to apply DELETE cmd on table. Is there any LS procedural document/blog step to purge without loosing consistence on database (back end).

[Mister_Nobody]

I think
1. LS ignores deleting in your environment. You have to write to LS Support.
2. For future logs you can run query

delete from tblntlog where tblNtlog.TimeGenerated > Current_TimeStamp

also it is needed to clear log on assets side.
3. You can clear table by own query.

[Mister_Nobody]

What about Eventlog scanning?
Scan Warning events.
Scan Information events.
Scan Success audit events.
Scan Failure events.

Do you scan all events?