You are here: Using Promodag Reports > Database management > Using Promodag Reports and Microsoft SQL Server

Using Promodag Reports and Microsoft SQL Server

Introduction

This article does not claim to replace the official Microsoft SQL Server documentation, but to provide basic information for those PROMODAG Reports users who are not familiar with SQL Server. See Also

A. Permissions required and database collation settings

a) Permissions

The PROMODAG account must have been granted the db_owner role on the Promodag Reports database.
It must also have the public and db_datareader roles on the TEMPDB database.

b) Database collation

The database uses the SQL_Latin1_General_CP1_CI_AS collation. The tempdb system database must also use case-insensitive collation settings.

B. SQL Server database files

An SQL Server database includes at least two separate files :

A data file containing the data (mdf extension)
A transaction log file containing the log information that is used to recover the database (ldf extension).

Note: SQL server creates these files in the server’s default locations when the database is created through PROMODAG Reports. To find out how to create them in other locations, read the following article: Manually create a new SQL Server database.

a) Purpose of transaction log files

During the course of normal operations, SQL Server utilizes a transaction log to track all of the modifications performed within a database. This log ensures both that the database is able to recover when abruptly interrupted (such as a loss of power) and that users are able to undo the results of a database transaction.

One could think that transaction logs are doomed to grow indefinitely. Fortunately it is not the case, for SQL Server controls transaction logs maintenance through a database property called recovery model.

b) Log truncation and recovery model

If log records were never deleted from the transaction log, it would eventually fill all the disk space that is available to the physical log files. The process of automatically freeing disk space for reuse by the transaction log is called log truncation. This process is differently triggered depending on the recovery model of the database which can be simple or full.

Typically, an SQL Server database uses the following recovery models:

Simple:

Under the Simple recovery model, log truncation occurs automatically after a checkpoint. So this recovery model automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.

Full:

Under the Full recovery or Bulk-logged recovery models, log truncation occurs automatically after a log backup, if a checkpoint has occurred since the previous backup. Requires log backups. No work is lost due to a lost or damaged data file. Can recover to an arbitrary point in time (for example, prior to application or user error).

If your database recovery model is set to Full, please make sure that :

there is a daily backup;
the maximum size for the transaction log is sufficient to handle your daily work.

Notes: A new database inherits its recovery model from the model database. When a log is truncated, unused disk space is marked as reusable but the file size is changed. See ‘Database shrinking’ later in this article on how to reclaim disk space.

C. The tempdb database

The tempdb system database is a global resource that is available to all users who are connected to an SQL Server.

PROMODAG Reports uses the tempdb database to store temporary data during report generation. The actual amount of disk space used by tempdb may vary depending on the database size and the report used. For example, the Message Breakdown by Delivery Time report makes considerable use of the tempdb database.

Some reports might fail with an error message like 'Could not allocate new page for database 'TEMPDB' if your database administrator (DBA) decided to set some size restriction on the tempdb database.

Recommendations:

Microsoft recommendation is to allow for tempdb files to automatically grow as required. This allows for the file to grow until the disk is full.

D. How does Promodag Reports use the SQL database

PROMODAG Reports uses the database to permanently store the data it needs to generate reports.

Data are collected and added to the database during message tracking file imports. Data and transaction log files grow during this import process. In accordance with what has been explained above, the transaction log will be purged – either automatically, or during a backup. However, the database will keep on growing unless you decide to clear data using the Tools > Purge Message Tracking Data option in PROMODAG Reports.

Note: Heavy operations (such as, for example, changing the time zone, importing or clearing a large number of message tracking files in one go), do modify a large part of the database. It may entail that the transaction log would swell to a point close to the database size.

Recommendations:

If you have many Exchange servers to analyze, PROMODAG Reports may become a very demanding database application. In this case, it is recommended not to create the database on an SQL server used by critical applications.

Database size limitation and potential problems

Your DBA may want to limit the size of databases under certain circumstances. This should be done carefully in the case of a PROMODAG Reports database, after having estimated its growth (see our Knowledge Base: How to estimate the Database size). If the database size was underestimated you will eventually face disk space problems and error messages such as ‘Could not allocate space for object ''x'' in database ''y'' because the ''PRIMARY'' filegroup is full’.

Recommendations:

Make the data files as large as possible when creating a database based on the maximum amount of data that you estimate would fill the database.
Allow the data files to grow automatically, but set a limit on their growth by specifying a maximum data file size that leaves some available space on the hard disk. This lets the database grow if more data is added than expected, but does not fill up the disk drive. If the initial data file size is exceeded and the file starts to grow automatically, re-evaluate the expected maximum database size. Then, plan accordingly by adding more disk space, if required, and creating and adding more files or filegroups to the database.

Database shrinking

Although SQL Server will reuse space effectively, there are times when a file no longer needs to be as large as it once was. The Shrink task reduces the size of SQL Server database data and log files by reclaiming unused space within those files.

Note: Selecting the Compact database after import check box in the File > Import Message Tracking Data option sends a DBCC SHRINKDATABASE request to SQL Server. As a result, both data and transaction log files are shrunk.