NAS_TRANSACTION_LOG table is missing from the UIM database

book

Article ID: 198676

calendar_today

Updated On:

Products

NIMSOFT PROBES DX Infrastructure Management

Issue/Introduction

Any ideas on how the NAS_TRANSACTION_LOG table would disappear from the database? Any ill effects if I have the customer run the sqlserver-nis-create.sql script to restore it?

Cause

- Unknown

Environment

Release : 20.1

Component : UIM NAS

Resolution

Yes, the nas script sqlserver-nis-create.sql located in ...<install_directory>\probes\service\nas), can be run to restore the table. 

It should be run in SQL Server Studio or whatever tool/studio applies to the backend DB vendor so you can view any messages/errors, but when you run it you should expect to see some errors due to duplicate columns being detected. For example:

Msg 2705, Level 16, State 4, Line 8
Column names in each table must be unique. Column name 'dev_id' in table 'NAS_ALARMS' is specified more than once.
Msg 2705, Level 16, State 4, Line 17
Column names in each table must be unique. Column name 'dev_id' in table 'NAS_TRANSACTION_SUMMARY' is specified more than once.

(1 row(s) affected)

No ill effects will occur and the NAS_TRANSACTION_LOG table will successfully be recreated.

Clear some alarms and then recheck the 3 main nas tables.

SELECT * FROM NAS_ALARMS
SELECT * FROM NAS_TRANSACTION_SUMMARY
SELECT * FROM NAS_TRANSACTION_LOG

Additional Information

If the table was actually dropped you'd see something like this in the nas.log at debug level 5,

Sep  2 19:44:37:256 [11132] 1 nas: COM Error [0x80040e37] IDispatch error #3127 - [Microsoft SQL Server Native Client 11.0] Invalid object name 'NAS_TRANSACTION_LOG'.
Sep  2 19:44:37:256 [11132] 3 nas: ndbTableInsert error: error, '[Microsoft SQL Server Native Client 11.0] Invalid object name 'NAS_TRANSACTION_LOG'.'
Sep  2 19:44:37:256 [11132] 3 nas: Failed Query
 
 INSERT  INTO NAS_TRANSACTION_LOG (type,time,nimid,nimts,level,severity,message,sid,subsys,source,hostname,prid,robot,hub,nas,domain,origin,suppcount,tz_offset,visible,i18n_token,i18n_dsize,i18n_data) VALUES (16,'2020-09-02 19:44:37','HL91395256-96321','2020-09-02 12:29:34',1,'information','crond: Process crond is running with process id = 5863.','1.1.2','Process','10.74.56.89','lvntest000529','processes','lvntest000529','lvntest000523_hub','lvntest000523_hub','lvntest000523_domain','lvntest000523_hub',15,14400,1,'as#system.processes.proc_running_with_procid',396,'d2F0Y2hlcgA3ADYAY3JvbmQAaGFuZGxlcwA3ADIAMABwcm9jZXNzADcANgBjcm9uZABjcHVfYXZlcmFnZQA3ADQAMC4wAHdpbmRvd19uYW1lADcAMQAAdGhyZWFkcwA3ADIAMQBwaWQANwA1ADU4NjMAcmVhbF9zaXplADcANQAxNjk2AGNvbW1hbmQANwAxNgAvdXNyL3NiaW4vY3JvbmQAZXhlY3V0YWJsZQA3ADE2AC91c3Ivc2Jpbi9jcm9uZABzaXplADcANwAxMjYyODQAYmluYXJ5X3BhdGgANwAxOQAvdXNyL3NiaW4vY3JvbmQgLW4AZXhwZWN0ZWRfdXNlcgA3ADEAAHdpbmRvd19jbGFzcwA3ADEAAHVzZXIANwA1AHJvb3QA');
Sep  2 19:44:37:256 [11132] 0 nas: Failed to insert data into transaction-log

Also the nisQueue.db and the nisQueue-db.journal may keep getting larger and larger filling up the disk space. Particularly the journal file.