IBM Support

Clearing the RealSecureDB transaction logs

Question & Answer


Question

How do you clear Transaction logs for RealSecureDB in MS SQL Server?

Cause

The Transaction Logs for the RealSecure DB under certain circumstances can reach 100% of it's maximum size. This will prevent new transactions from occurring, ultimately causing new security events to fail to be loaded to the database. Other unwanted unexpected behavior may also occur.

Answer


YouTube Video
Clear the SiteProtector RealSecureDB Transaction Logs (3:41)
This video describes how to clear the RealSecureDB transaction log.


Note: if you are using the Full recovery model: The transaction log is supposed to be large. This is how you are able to roll back to individual backups. By executing any of the queries below, you will lose your ability to recover backups from a certain time as the entire transaction log will be committed to the main database file. Subsequent automatic backups will again use the Full model if that is how your current database maintenance settings are configured.

Depending on your version of SQL Server, the method for committing your transaction log may be different.

To clear transaction logs of RealSecureDB database installed on SQL 2005:

The query to truncate the transaction logs is:

backup log realsecuredb with truncate_only


DBCC SHRINKFILE (N'SiteProtector site database log file' , 1)


To clear transaction logs of RealSecureDB database installed on SQL 2008/2012:

To clear transaction logs of RealSecureDB database installed on SQL 2008, you would need to run the following queries. You can either run them from Microsoft Query Analyzer using the RealSecureDB, or by using osql. To use osql, type osql -E -d RealSecureDB at a command prompt, then type the query and the enter key, then type go and enter.


select name,size from sys.database_files
select name,recovery_model_desc from sys.databases
alter database RealSecureDB SET Recovery simple
select name,recovery_model_desc from sys.databases
select name,size from sys.database_files
DBCC SHRINKFILE (N'SiteProtector site database log file' , 1)
select name,size from sys.database_files


Notes:
  • If you are using a FULL recovery model, you may attempt omitting the 3rd line that sets the recovery model to simple, however this may cause your transaction log only to shrink, it will not clear. You may need to set the recovery model depending on the results you are trying to achieve.
  • When executing these queries, the backup may fail with the following error:

Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.

This is a common error message when manually preforming backup operations. This simply means that another database transaction was in progress when you attempted to commit the transaction log. This can typically be resolved by running the query again. If you are faced with this error repeatedly, you may need to stop all SiteProtector services (SiteProtector Application Server, SiteProtector Web Server and SiteProtector Sensor Controller) and all instances of issDaemon before trying again.




[{"Product":{"code":"SSETBF","label":"IBM Security SiteProtector System"},"Business Unit":{"code":"BU008","label":"Security"},"Component":"Database","Platform":[{"code":"PF033","label":"Windows"}],"Version":"3.0;3.1.1","Edition":"","Line of Business":{"code":"LOB24","label":"Security Software"}}]

Document Information

Modified date:
16 June 2018

UID

swg21516175