SiteProtector transaction log cleaning up

At times, your database transaction log for the SiteProtector database may reach its allocated disk space. To clear your transaction log you can use the following command through the CLI on your database server.
osql -E -d RealSecureDB -Q "backup log RealSecureDB with truncate_only"
If your SQL Server is running in a named instance, you will need to run this command instead:
osql -S <server name or IP>/<instance name> -U <username> -P <password> -d RealSecureDB -Q "backup log RealSecureDB with truncate_only"

For further information on truncating SQL Server transaction logs, please review Microsoft's official MSDN article at http://msdn.microsoft.com/en-us/library/aa173551(SQL.80).aspx#

BACKUP LOG RealsecureDB WITH TRUNCATE_ONLY

Examples

Shrink a datafile to 64 Mb:

DBCC SHRINKFILE (MyDataFile01, 64);

DBCC SHRINKFILE(MyDatabase_Log, 64) WITH TRUNCATE_ONLY

DBCC SHRINKFILE

Shrink the size of the current database data / log file or empty a file by moving the data.

Syntax

      DBCC SHRINKFILE ( file , EMPTYFILE ) [WITH NO_INFOMSGS]

      DBCC SHRINKFILE ( file , target_size

              [, {NOTRUNCATE | TRUNCATEONLY }] )  [WITH NO_INFOMSGS ]

Key:

   file          -  Logical file name or file_id

   EMPTYFILE      - Migrate data to other files in the same filegroup.

                    The file can be removed with ALTER DATABASE.

   target_size   - The size for the file in megabytes.

                   default = that specified when the file was created, or 

                   the last size used with  ALTER DATABASE.(int)

   NOTRUNCATE    - Free space at the end of the data file is not returned to the OS

                    (pages are still moved)

   TRUNCATEONLY  - Release free space at the end of the data file to the OS

                   (do not move pages)

      NO_INFOMSGS   - Suppress all information messages (severity 0-10)

You can shrink a transaction log file while the system is in use (DML commands are also being executed), however this will only affect the inactive portion of the transaction log file.
Discover the file_ID for each file with the SQL: SELECT file_id, name FROM sys.database_files;
After using TRUNCATE_ONLY you must perform a full backup
Examples
Shrink a datafile to 64 Mb:

DBCC SHRINKFILE (MyDataFile01, 64);

Shrink a Log file to 64 Mb:

USE MyDatabase;

GO

DBCC SHRINKFILE(MyDatabase_Log, 64)

BACKUP LOG MyDatabase WITH TRUNCATE_ONLY

DBCC SHRINKFILE(MyDatabase_Log, 64)

Afterwords, perform a full backup of the database.
To make the file as small as possible you can specify 1 for 1 Mb, or just leave out the target_size completely, be aware that doing this will slow down the system a little as the system will just have to grow the log file again as soon as another transaction is started.
Set database recovery model to SIMPLE or FULL

ALTER DATABASE MyDatabase

SET RECOVERY FULL;

GO

"Men shrink less from offending one who inspires love than one who inspires fear" - Niccolo Machiavelli
Related:
ALTER DATABASE
DBCC SHRINKDATABASE
FILE_ID
sys.database_files
Equivalent Oracle command: ALTER DATABASE Datafile '/oradata/ss64.dbf' resize 64M;

Intrusion Prevention best practices

Ten Best Practices for Enterprise Intrusion Prevention

by Lou Ryan

IT and network security managers face many challenges in securing their organization's critical servers from attack. Lack of dedicated security resources and the increased sophistication of attack methods are among their top headaches. Although intrusion detection systems (IDS) have been a popular solution for enterprises in the past, it is not enough to block the evolving attacks in cyber-space today. One of the main issues with IDS is that they do nothing to proactively stop intrusions before attacks occur. Also, many IDS are signature-based, so they don't detect new attacks or variations on old attacks, nor do they detect attacks in encrypted traffic such as HTTP over SSL (Secure Sockets Layer).

What's the alternative? Intrusion prevention is the next logical step in enterprise security. Intrusion prevention systems take IDS to the next level by going beyond just detecting, to actually stopping attacks before they cause damage. The difference between the two technologies is one enterprise executives are all too familiar with: Intrusion prevention blocked Code Red, Nimda, and SQL Slammer, while IDS users spent millions cleaning up after each of these.

What Is An Intrusion Prevention Solution?
There are many products and tools on the market today that use the "prevention" moniker. The right intrusion prevention solution includes enabling you to circumvent the need for analysis to be done before action can be taken to protect the system. In addition, it prevents attacks from doing damage to your operating system, applications and data. By using a system to proactively prevent attacks, there is no gap between the attack being detected, identifying it as an attack, and finally doing something to prevent it. In addition, intrusion prevention helps enterprises get better control of the costly and time-consuming process of installing software patches to plug vulnerabilities in operating systems and applications and to fend off attacks like worms and buffer overflows.

How do you choose the right type of solution for your organization? This checklist should serve as the building blocks to choosing the right enterprise intrusion prevention solution for your organization.

Table 1. Intrusion Prevention Checklist

Proactive, real-time prevention of attacks	The right solution should provide real-time prevention and analysis of attacks. It should identify the attack and prevent access to critical server resources before any unauthorized activity occurs.
Patch latency relief	Patch management is a complex process. Between the time a patch is developed and deployed, a smart hacker could compromise servers and critical data. A good intrusion prevention solution gives system administrators the protection needed during patch latency and ample time to test and deploy patches.
Protection for each critical server	Servers, where the most sensitive enterprise data resides, are on the hit list for most hackers. It is vital to have an intrusion prevention solution that is tailored for server protection. Too many solutions on the market today try to be the "ultimate" protection, by using the same mousetrap for servers and desktops. The result is thin technology that does not adequately protect sensitive systems and data.
Signatures and behavioral rules	The most effective method for identifying intrusions is a hybrid approach that combines the strengths of attack-specific signatures and behavioral rules. This hybrid approach avoids the fundamental trade-off by providing coverage to both known and unknown attacks and at the same time keep the false-positive rate to a minimum. One technology can't take the place of the other: Behavioral rules allow the servers to be protected from new and previously unknown attacks. However, the coverage of behavioral systems is limited, many attacks aren't covered, and behavioral systems generate more false positives. For full forensics capability, the signature is critical in identifying attacks, so security managers can know what sort of attack is being directed at their systems.
Layers	Strong security is founded on the concept of defense in depth: having several layers of protection. Redundant mechanisms should co-exist so that even if one hurdle is bypassed, there are always other barriers to cross.
Heterogeneous environment protection	Organizations using mixed computing environments need to be sure that the intrusion prevention they choose will be consistent across all their critical servers. It should also enable consistent, reliable cross-platform protection.
Manageable	The ideal intrusion prevention solution will allow security configurations and policies too be easily be leveraged across applications, user groups and agents to decrease the cost of installing and maintaining large security deployments.
Scalable	An enterprise-class intrusion prevention solution must scale to meet the needs of the extended enterprise while maintaining the highest levels of security. Scalability comes in the form of supporting large numbers of protected servers, supporting large amounts of event traffic, and supporting distributed security management to meet the needs of large, distributed organizations.
Low total cost of ownership	Ideally, the system you invest in should decrease costs associated with monitoring and managing total server security. Make sure that the system you are evaluating can show metrics around reducing man-hours spent on clean-up, patching, monitoring, etc.
Proven prevention technology	Beware of solutions that use the word, prevention,but are really detection-based products or desktop solutions in new packaging. It is important to investigate that the solution has been thoroughly tested, deployed, and continuously maintained, in an environment similar to your own. Read case studies, ask questions, and compare.
Strong corporate security policy	All businesses need a detailed and enforced corporate security policy.

You'll notice that there are actually eleven best practices on this list. Intrusion prevention is not a one-time implementation of point products, but a continuous evolving process. All businesses need a detailed and enforced corporate security policy. A security policy defines which "users" have access rights to which enterprise resources. Make sure the policy takes into consideration users within the enterprise as well as outside users including partners, customers, and remote employees accessing corporate resources.

---

About the Author
Lou Ryan is President and CEO of Entercept Security Technologies.

How do you manually configure the speed and duplex settings on a Proventia M?

This article explains how to manually configure the speed and duplex settings on a Proventia M.

This information applies to:
Proventia M Firmware 3.1

Fix Version:
N/A

Related Articles: See Below

Answer

To hard code the speed and duplex settings on the Proventia M perform the following steps:

1. Connect via SSH and login as root.
2. Type vi /sbin/ifup-local to create a config file.
3. Open the file and add a line using the following syntax:
   ethtool -s [Adapter] speed [Speed in Mbps] duplex [full/half] autoneg [on/off]
   
   Example: To set the external interface (eth1) to 10Mbps half duplex, use the following line:
   ethtool -s eth1 speed 10 duplex half autoneg off
4. Save the file.
5. Change the permissions of the file by using the following command:chmod x /sbin/ifup-local
6. Restart the interfaces by running the following command:service network restart

Once the network interfaces have restarted, you can verify the settings have been updated by running the following command:mii-tool

This will display the current status of all links on the appliance.

How to manually purge siteprotector database

WARNING: This solution requires that you edit your SQL or MSDE database. Incorrectly editing your database may cause severe and irreparable damage and may require you to reinstall your database. It is recommended that you backup your database before performing any edit to it. Editing of your database should only be performed by a SQL database Administrator, or similarly qualified individual. If you are not familiar with SQL or MSDE, do not attempt this operation! IBM Internet Security Systems cannot guarantee that problems resulting from incorrectly editing your database can be solved. Proceed at your own risk. Some commands only valid in SQL 2000 but not SQL 2005 and vice versa.
NOTE: You must be logged in with local administrator privileges on the MSDE or SQL machine or the following procedures will not work. Before performing any maintenance on your database, you must first stop the Event Collector to prevent damage to the database. Restart the Event Collector only after you have completed the maintenance.
These queries can be run 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.
Increase the size of the database
If your database is 100% full, the size of the database may have to be increased before you can purge or clear your database. Keep in mind, you can not increase your database larger than the size of the hard drive the database is on, or past 2GB if you are using MSDE. If you have your database set to grow, and you still have plenty of space on your hard drive, but your database keeps getting filled at 100%, you may also want to increase the growth rate of your database.
Increase the size of your data file
Alter database RealSecureDB modify file (name='SiteProtector site database primary data file', maxsize= 2GB)
Note that the maxsize is the size to which it will be increased. In this example, the database is being increased to 2GB. Make this number whatever you feel is best for your environment.
To force increase the size of the data file, execute the statement below:
alter database RealSecureDB modify file (name='SiteProtector site database primary data file', size= 2GB)
Increase the size of your transaction logs
Alter database RealSecureDB modify file (name='SiteProtector site database log file', maxsize= 2GB)
Again, in this example the size has been increased to 2GB.
To force increase the size of the log file, execute the statement below:
alter database RealSecureDB modify file (name='SiteProtector site database log file', size= 2GB)
Purging the database
Purges are useful because data can be removed based on a date. Data that is older than the date supplied will be purged. However, purges may take a very long time to run (possibly hours), especially with a large database, In the process, all data is compared to the date you supplied.
Information about all the different purge stored procedures is located in Knowledgebase Article #1146.
The most common procedures that you will use are iss_PurgeObs and iss_PurgeSD:
iss_PurgeObs - Removes records from the Observances tables. These records correspond to the first level event information seen in the console.
iss_PurgeSD - Removes records from the SensorData tables. These records correspond to the detailed information seen when drilling into events on the console.
All of the purge stored procedures will be called with a query like these for examples:

exec iss_PurgeSD @BeginDate='March 26, 2006', @ObsType='0,1,2,3,4,5,6,7,8,9,10'
This would purge all type of data before 3/26/2006.
exec iss_PurgeSD @BeginDate='March 26, 2006', @ObsType='0,1'
This would purge incomplete and intrusion detection data before 3/26/2006.
Note: For SiteProtector 2.0 SP6.1 and Above use the command below:
exec iss_PurgeSD @BeginDate='DATE', @ObsType='0,1,2,3,4,5,6,7,8,9', @PurgeFlag=1
Users can purge only cleared events by adding @clearedonly=1. This will only purge the data that has been cleared in the SiteProtector Console's Analysis View. An example is shown below:
exec iss_PurgeObs @BeginDate='March 26, 2006', @ObsType='0,1,2,3,4,5,6,7,8,9,10', @PurgeFlag=1, @clearedonly=1

ObsType data should be purged from the database. The descriptions are provided below:
Type Description
-------------- ------------------------------
0 Incomplete data
1 Intrusion Detection
2 Vulnerability
3 Informational Only
4 AntiVirus
5 Firewall
6 WebFilter
7 AntiSpam
8 Application Compliance
9 Network Anomaly Detection
10 File Integrity
The status of a purge can be monitored while it is running by executing the following query:
exec iss_GetPurgeStatus

Clearing/Truncating the database
Truncating the database is much faster than purging the database because it simply deletes all of the event data from the database.
WARNING: Only do this if you want to remove ALL of your event data.
NOTE: This will not remove any license or component data.
The query to truncate the event data is:
exec iss_TruncateTables
The query to truncate the transaction logs is:
backup log realsecuredb with truncate_only


Backing up the database
To do a full backup the database to the location selected in the SiteProtector automatic maintenance, or "C:\" if the location hasn't been changed, run the following two queries:
exec iss_AddBackupDevice
exec iss_BackupDatabase 'RealSecureDB'
This may take a while depending on your database size.
Shrinking the database
To shrink the size of the database to allow for more disk space, run one of the following methods:
DBCC SHRINKDATABASE ('RealSecureDB')
This will shrink both the data and log files.
DBCC SHRINKFILE (name= 'siteprotector site database primary data file')
This will shrink the data file to the initial size if possible.
DBCC SHRINKFILE (name= 'siteprotector site database log file')
This will shrink the log file to the initial size if possible.

SiteProtector transaction log 100% full

How to stop the transaction log of a SQL Server database from growing unexpectedly

View products that this article applies to.

On This Page

• INTRODUCTION
• MORE INFORMATION
• Reduce the size of the transaction logs
• Truncate the inactive transactions in your transaction log
• Shrink the transaction log file
• Prevent the transaction log files from growing unexpectedly
• REFERENCES

Expand all | Collapse all

INTRODUCTION

This article describes the steps that you must follow when the transaction logs...

This article describes the steps that you must follow when the transaction logs expand to an unacceptable limit. The expansion of the transaction logs can make your Microsoft SQL Server database unusable. This article also provides the options that you can use to stop the transaction logs from growing unexpectedly.

Back to the top

MORE INFORMATION

In SQL Server 2000 and in SQL Server 2005, each database contains at least one d...

In SQL Server 2000 and in SQL Server 2005, each database contains at least one data file and one transaction log file. SQL Server stores the data physically in the data file. The transaction log file stores the details of all the modifications that you perform on your SQL Server database and the details of the transactions that performed each modification. Because the transactional integrity is considered a fundamental and intrinsic characteristic of SQL Server, logging the details of the transactions cannot be turned off in SQL Server.

The transaction log file is logically divided into smaller segments that are referred to as virtual log files. In SQL Server 2000, you can configure the transaction log file to expand as needed. The transaction log expansion can be governed by the user or can be configured to use all the available disk space. Any modifications that SQL Server makes to the size of the transaction log file, such as truncating the transaction log files or growing the transaction log files, are performed in units of virtual log files.

If the transaction log file that corresponds to a SQL Server database is filled and if you have set the option for the transaction log files to grow automatically, the transaction log file grows in units of virtual log files. Sometimes, the transaction log file may become very large and you may run out of disk space. When a transaction log file grows until the log file uses all the available disk space and cannot expand any more, you can no longer perform any data modification operations on your database. Additionally, SQL Server may mark your database as suspect because of the lack of space for the transaction log expansion.

For additional information about the scenarios that may cause the transaction log file to grow unexpectedly, click the following article number to view the article in the Microsoft Knowledge Base:

317375 (http://support.microsoft.com/kb/317375/ ) Transaction log grows unexpectedly or becomes full on SQL Server

Back to the top

Reduce the size of the transaction logs

To recover from a situation where the transaction logs grow to an unacceptable limit, you must reduce the size of the transaction logs. To do this, you must truncate the inactive transactions in your transaction log and shrink the transaction log file.

Note The transaction logs are very important to maintain the transactional integrity of the database. Therefore, you must not delete the transaction log files even after you make a backup of your database and the transaction logs.

Truncate the inactive transactions in your transaction log

When the transaction logs grow to an unacceptable limit, you must immediately back up your transaction log file. While the backup of your transaction log files is created, SQL Server automatically truncates the inactive part of the transaction log. The inactive part of the transaction log file contains the completed transactions, and therefore, the transaction log file is no longer used by SQL Server during the recovery process. SQL Server reuses this truncated, inactive space in the transaction log instead of permitting the transaction log to continue to grow and to use more space.

For additional information about the issues that you must consider when you make a backup of the transaction logs and the issues that you must consider when you restore the transaction log backups, visit the following topics in SQL Server Books Online:

• Transaction log backups
• Transaction log backup and restore

You can also delete the inactive transactions from a transaction log file by using the Truncate method. For additional information about truncating the transaction logs, see the "Truncating the transaction log" topic in SQL Server Books Online.

Important After you manually truncate the transaction log files, you must create a full database backup before you create a transaction log backup.

For additional information about the issues that may occur when you truncate the transaction log files, click the following article number to view the article in the Microsoft Knowledge Base:

62866 (http://support.microsoft.com/kb/62866/ ) Reasons why SQL transaction log is not being truncated

Shrink the transaction log file

The backup operation or the Truncate method does not reduce the log file size. To reduce the size of the transaction log file, you must shrink the transaction log file. To shrink a transaction log file to the requested size and to remove the unused pages, you must use the DBCC SHRINKFILE operation. The DBCC SHRINKFILE Transact-SQL statement can only shrink the inactive part inside the log file.

Note The DBCC SHRINKFILE Transact-SQL statement cannot truncate the log and shrink the used space inside the log file on its own.

For more information about shrinking the transaction log files, see the following topics in SQL Server Books Online:

• Shrinking the transaction log
• DBCC SHRINKFILE

For additional information about how to shrink the transaction log files in SQL Server 2000, click the following article number to view the article in the Microsoft Knowledge Base:

272318 (http://support.microsoft.com/kb/272318/ ) Shrinking the transaction log in SQL Server 2000 with DBCC SHRINKFILE

For additional information about the problems that may occur when you shrink the transaction log files, click the following article numbers to view the articles in the Microsoft Knowledge Base:

814574 (http://support.microsoft.com/kb/814574/ ) PRB: Error message: "Cannot shrink log file ..." occurs when you shrink the transaction log file

324432 (http://support.microsoft.com/kb/324432/ ) PRB: DBCC SHRINKFILE and SHRINKDATABASE commands may not work because of sparsely populated text, ntext, or image columns

Back to the top

Prevent the transaction log files from growing unexpectedly

To prevent the transaction log files from growing unexpectedly, consider using one of the following methods:

• Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.
• Configure the automatic expansion of transaction log files by using memory units instead of a percentage after you thoroughly evaluate the optimum memory size.
  
  For additional information about the issues to consider when you configure the autogrow option, click the following article number to view the article in the Microsoft Knowledge Base:

315512 (http://support.microsoft.com/kb/315512/ ) Considerations for autogrow and autoshrink configuration

• Change the recovery model. If a disaster or data corruption occurs, you must recover your database so that the data consistency and the transactional integrity of the database are maintained. Based on how critical the data in your database is, you can use one of the following recovery models to determine how your data is backed up and what your exposure to the data loss is:
• Simple recovery model
• Full recovery model
• Bulk-logged recovery model

By using the simple recovery model, you can recover your database to the most recent backup of your database. By using the full recovery model or the bulk-logged recovery model, you can recover your database to the point when the failure occurred by restoring your database with the transaction log file backups.

By default, in SQL Server 2000 and in SQL Server 2005, the recovery model for a SQL Server database is set to the Full recovery model. With the full recovery model, regular backups of the transaction log are used to prevent the transaction log file size from growing out of proportion to the database size. However, if the regular backups of the transaction log are not performed, the transaction log file grows to fill the disk, and you may not be able to perform any data modification operations on the SQL Server database.

You can change the recovery model from full to simple if you do not want to use the transaction log files during a disaster recovery operation.

• Back up the transaction log files regularly to delete the inactive transactions in your transaction log.
• Design the transactions to be small.
• Make sure that no uncommitted transactions continue to run for an indefinite time.
• Schedule the Update Statistics option to occur daily.
• To defragment the indexes to benefit the workload performance in your production environment, use the DBCC INDEXDEFRAG Transact-SQL statement instead of the DBCC DBREINDEX Transact-SQL statement. If you run the DBCC DBREINDEX statement, the transaction log may expand significantly when your SQL Server database is in Full recovery mode. Additionally, the DBCC INDEXDEGRAG statement does not hold the locks for a long time, unlike the DBCC DBREINDEX statement.
  
  For additional information about defragmenting the indexes in SQL Server 2000, see the following Microsoft Web site:

http://technet.microsoft.com/en-us/library/cc966523.aspx (http://technet.microsoft.com/en-us/library/cc966523.aspx)

If you must run the DBCC DBREINDEX statement as a job that is a part of the database maintenance plan, you must break up the job into multiple jobs. Additionally, you must take the frequent backups for the transaction logs between the execution of the jobs.

Back to the top

REFERENCES

For additional information about troubleshooting the additional disk space requi...

For additional information about troubleshooting the additional disk space requirement during the recovery process, see the "Insufficient disk space" topic in SQL Server Books Online. For additional information about the transaction log architecture, see the following topics in SQL Server Books Online:

• Transaction log architecture
• Transaction log logical architecture
• Transaction log physical architecture

For more information about the recovery models in SQL Server 2000, see the following topics in SQL Server Books Online:

• Selecting a recovery model
• Simple recovery
• Full recovery
• Bulk-logged recovery
• Switching recovery models

Back to the top

---

APPLIES TO

• Microsoft SQL Server 2000 Standard Edition
• Microsoft SQL Server 2005 Standard Edition
• Microsoft SQL Server 2005 Developer Edition
• Microsoft SQL Server 2005 Enterprise Edition
• Microsoft SQL Server 2005 Express Edition
• Microsoft SQL Server 2005 Workgroup Edition