HowTo - Prevent WebEdition database log file from expanding continuosly

HowTo - Prevent WebEdition database log file from expanding continuosly

Question
How can I reduce the file size of TBS WebEdition database log, default name 'TBS_BAWSE.ldf'?

NOTE: This logging is an inbuilt feature of MS SQL server itself and not created by TBS software. Its the responsibility of local DB server administrator to keep the database log file size under limits. TBS provides the below article to help in the process based on our knowledge and experience.
Background information
All SQL transactions performed on TBS database level will be logged in  TBS_BAWSE.ldf. The number of SQL transactions could be very big when there is a large number of connected TBS clients and users. This means the size of the DB transaction log file will increase continuously over time.

These SQL transactions have to be clearly distinguished from Time and Attendance transactions: these are general database transactions and thus any operations at database level is logged to ensure rollback possibility.

These SQL Server log files are VERY important and should not be simply deleted !!!

Some details about LDF files (excerpts from internet articles): While data is stored in an MDF file, all transactions and the SQL Server database modifications made by each transaction are stored in an LDF file – a transaction log file which is an essential component of the database. Conceptually, the log file is a string of log records. Physically, the log records are stored in one or the set of physical LDF files that implement the transaction log.

The primary purpose of an LDF file is to provide the ACID concept – Atomicity, Consistency, Isolation, and Durability:
  1. Atomicity: if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged
  2. Consistency: any transaction brings the database from one valid state to another
  3. Isolation: the execution of concurrent transactions brings the database to a state as if the transactions were executed serially, one by one
  4. Durability: once committed, the transaction remains, even in the case of errors, power loss, or crashes
An LDF file stores enough information to replay or undo a change, or recover the database to a specific point in time.

Solution
To keep the size of this file under control, regular maintenance has to be carried out.
TBS recommends to:
  1. Backup the TBS_BAWSE database regularly (daily/weekly/monthly - depending on the size of the installation). The backup process itself could already help to reduce the filesize. This means that a regular backup routine helps to mitigate this problem from the start.
  2. Shrink the transaction log once or twice a year (or more often if really needed) always directly after a backup was taken, using SQL Management Studio.
This recommendation is based on “How a backup interacts with a transaction log” chapter from this article: https://www.mssqltips.com/sqlservertip/3076/how-to-read-the-sql-server-database-transaction-log/ 

Please consider this article on how to create backups:

For guidance on how to shrink the log file please review the below articles:



    • Related Articles

    • HowTo - Handle 'Database operation error' issues

      The Database operation error issue is one of the most common problems detected after a WebEdition installation. It is a general error which is basically saying that connection to the database server (default, SQL Server) could not be established. ...

    • FAQ - Ways to access SQL Server in WebEdition

      Question By default WebEdition accesses SQL server database using 'SQL Server authentication'. How can I change that to 'Windows authentication'? Answer TBS server software is preconfigured to use 'SQL Server authentication' method when connecting to ...

    • FAQ - Basic backup/restore of WE database in the same MS SQL Server

      Question How can I simply backup and restore TBS WebEdition server database (in the same SQL Server installation)? Answer In order to prevent data loss, it is a good practice to regularly backup content of TBS WebEdition server database. This article ...

    • FAQ - Database migration to another MS SQL server

      Question How can I migrate complete TBS WebEdition server database to another MS SQL server? Answer This article describes how to move TBS WebEdition server database to another MS SQL database server. This may be useful e.g. in case you are moving or ...

    • FAQ - WebEdition migration to another server machine

      Question How can I migrate the TBS WebEdition server software to another server machine? Answer This article describes how to migrate the TBS WebEdition (WE) server software to a different physical computer. The below steps should be followed in ...