CA UIM (Nimsoft) Database Best Practices for MS SQL Server

Document ID : KB000033680
Last Modified Date : 11/09/2018
Show Technical Document Details
Introduction:
This Article includes content, links and a best practices guide that covers Microsoft SQL Server database best practices. Note that all of the guidelines/recommendations MUST be discussed with a DBA first before taking any action.

Note: that all of the proposed guidelines/recommendations included in this Article and document MUST be discussed with a DBA first before taking any action/making any changes to the database.
Background:

Why the SQL Server Transaction Log Grows Even in Simple Recovery Model (Disk Filling Up)

SQL Server always uses the log for data modifications, regardless of recovery model used. The recovery model just affects what happens after the transaction completes.

From the following MSDN article:
http://msdn.microsoft.com/en-us/library/ms179355.aspx

"Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN). The MinLSN is the log sequence number of the oldest log record that is required for a successful database-wide rollback. The log may grow very large because the log cannot be truncated past the MinLSN. This happens even if the database is using the simple recovery model, in which the transaction log is normally truncated on each automatic checkpoint."

One of the things you may want to consider in this scenario is either increasing the disk space or shrinking the file using either "DBCC shrinkfile" or via the SQL Enterprise Studio Admin GUI.

Recovery Modes (Simple versus Full)

SQL Server offers Full versus Simple recovery modes. The quick difference is that in Simple recovery mode, the transaction log reuses the space after each checkpoint (upon write-to-disk completion and/or the database's internal system checkpoint) and there is no need for backing up the transaction log file for recovery purposes.

With Full recovery mode, the transaction log cannot be reused unless backed up and it is used in recovery. So, Simple recovery mode still makes use of the transaction log, it bloats it for large or long running transactions and space is then reclaimed after the checkpoint.

For customers with sizable Nimsoft SLM databases, the transaction log can become bloated during database maintenance (via deletes and re-indexing) but it normally returns to normal at the end of the maintenance completion.

Example errors from the data_engine log:

Jan 4 02:49:44:408 [8072] de: ExecuteNoRecords - Query: TRUNCATE TABLE D_QOS_PROBESJan 4 02:49:44:408 [8072] de: [QoSData] ExecuteNoRecords - 1 errorsJan 4 02:49:44:408 [8072] de: (1) ExecuteNoRecords [Microsoft OLE DB Provider for SQL Server] The transaction log for database 'NimsoftSLM' is full due to 'LOG_BACKUP'.Jan 4 02:49:44:409 [8072] de: COM Error [0x80040e14] IDispatch error #3092 - [Microsoft OLE DB Provider for SQL Server] The transaction log for database 'NimsoftSLM' is full due to 'LOG_BACKUP'.Jan 4 02:49:44:415 [8072] de: qos_check - InitializeOnce failed ...Jan 4 02:49:44:424 [13028] de: [LSV] has disconnected from databaseJan 4 02:49:44:424 [13028] de: Database_global_lock LOCK


This can be due to the transaction logging set to archive mode instead of a circular method. If you need to stay with an archive logging method, then we suggest separating the logs onto their own dedicated disk and archive them to another format. Obviously you would need to talk to your DBA about what would be needed to restructure the database server.

- Check your Recovery model and Transaction Log...If you are using FULL recovery model, are you regularly backing up and truncating the transaction log so that it does not grow too large?

Some things you can do to relieve strain on a bad situation is as follows...

1 - Instead of waiting for DB to auto-grow, increase the data file size so that there is a lot of extra DB space available.

2 - Assuming you are running with a Full recovery Model which means all data is being written to the transaction log file and is not emptied until committed to the DB. This will create very large log files and affect performance.

3 - Follow all of the recommendations found in the NISDatabseBestPracticesGuidev11.pdf document found in the TEC000003224.zip file in the File Attached section below.
Note that this pdf is somewhat dated, but there is solid info on best practices in Chapters 4-5, and information regarding DB performance analysis in Chapter 6.

4 - Backup/truncate the transaction log

5 - Or switch to Simple Recovery mode

Instructions:
Please review the NISDatabaseBestPracticesGuidev11.pdf found in the TEC000003224.zip file found in the File Attachments section
Additional Information:

Other Considerations

If your data_engine setting 'delete raw samples' is set too high, e.g., 365 days you may consider gradually cutting back on the size of your database as there may be too much data for the data_engine maintenance to complete in a timely fashion and not cause DB problems. Normally customers save 90 days or so of raw data unless there is some business reason to save more data. Due to this setting data_engine maintenance can take taking a long time.

Partitioning of Raw Sample Data (SQL Server)

Important! When using the Partitioning feature, schedule maintenance to run daily.The time required to execute the partitioning depends on amount of data as well as performance of disk subsystem but can for large installations take several hours or even up to several days.

The sample data tables can be partitioned in order to achieve improved performance.

The sample data will be partitioned by day so if you for instance have configured the system to delete raw sample data older than 365 days, then the sample data tables (RN_QOS_DATA_xxxx) will each be configured with 365 partitions (plus a few extra partitions in order allow for faster maintenance).

SQL Server: If using partitioning then the property Delete raw data older than must be between 1 and 900. SQL Server, up to and including 2008 SP1, limits a table to 1000 partitions.

Partitioning will contribute to improved performance and faster more efficient maintenance when accessing the raw sample data tables:

  • higher insert rates
  • faster read access to data
  • faster data maintenance (delete/compress of sample data)
  • faster index maintenance

For complete information see:
http://docs.nimsoft.com/prodhelp/en_US/Probes/Catalog/data_engine/7.9/index.htm?toc.htm?1971023.html?zoom_highlight=partitioning

The data_engine runs a query to determine the MS SQL Server edition and if applicable enables the table/index partition option in the data_engine.

Note that data_engine 7.91 which is part of NMS 6.50 does not recognize MS SQL Server 2012 Enterprise edition properly and does not enable the table/index partitioning option. data_engine version v7.92 fixes this issue.
This fix applies only to NMS 6.50 version of the data_engine
.

References:
http://dba.stackexchange.com/questions/29829/why-does-the-transaction-log-keep-growing-or-run-out-of-space

DBAs can also examine the transaction log and/or use SQL profiler to see which transactions are filling up the log.

keywords: transaction log disk fill filling up error database maintenance administration space data_engine SQL Server recovery model mode simlpe full table truncate truncation data raw settings configuration ExecuteNoRecords.

File Attachments:
TEC000003224.zip