SQL Transaction Log Size

[Greg]

Hi All,

i am running a small dot net nuke portal, the ms sql database is 11Mb is size however the sql transaction logs are 43Mb. its my understanding the transaction logs are usually backed up and truncated to prevent them from growing too large. i can truncate the logs manually but only wish to do so as long as it has been backed up?

[Rock]

I've seen people inquiring to what exactly are the transaction logs in MSSQL Server & why do they keep on growing & whether they can be disabled permanently to avoid disk space issue, etc etc... Here are the answers to your questions..

Each MSSQL database contains at least one data file which has an extension of .MDF & one transaction log file with an extension of .LDF. MSSQL Server stores the data physically in the .MDF data file, while the transaction log file stores the details of all the modifications that you perform on your SQL Server database when it's functioning & operating in the respective .LDF file. In short the database engine keeps track of almost every change that takes place in the database by making entries into the respective transaction log so that it can be used later if needed.

This log ensures both that the database is able to recover when unexpectedly interrupted due to loss of power or server reboot) & that users are able to undo or rollback the results of a database transaction. Incomplete transactions are maintained in the log before they are permanently stored or committed in the database.

Because the transactional integrity is considered as an essential & intrinsic characteristic of SQL Server, hence logging the details of the transactions cannot be turned off in SQL Server..

The location of the transaction log is setup or configured at the same time the database is created. When creating a database, the location of the SQL Server transaction log can specified as well as other options associated with the transaction log can be modified. The transaction log backup functionality enables users to store copies of the database's transaction log on a backup device. These backups generally consume far fewer resources than a full or differential database backup & therefore are appropriate for even high-use periods..

To create a backup of the currently active transaction log using MSSQL Management Studio follow these steps:

• After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
• Expand Databases & select the user database you wish to backup.
• Right-click the database, point to Tasks, & then click Back Up. The Back Up Database dialog box appears.
• In the Database list box, verify the database name. You can optionally select a different database from the list.
• Verify that the recovery model is either FULL or BULK_LOGGED.
• In the Backup type list box, select Transaction Log.
• Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.
• Optionally, in the Description text box, enter a description of the backup set.
• Choose the type of backup destination by clicking Disk or Tape. This usually should be Disk if you want the backup on the HDD, click Add. The selected paths are displayed in the Backup to list box. To remove an existing backup destination, select it & click Remove. Add a new destination & click ok. To view the contents of a backup destination, select it & click Contents.
• To view or select the advanced options, click Options in the Select a page pane.
• Select Overwrite Media option, if you wish to overwrite any earlier existing database backups.
• Once done, click ok. The transaction log would be backed up as per your requirement

To create a backup of the currently active transaction log using theTransact-SQL statements follow these simple steps:

Execute the BACKUP LOG statement to back up the currently active transaction log, specifying the following:
* The name of the database to which the transaction log to back up belongs.
* The backup device where the transaction log backup will be written.

Databases use SIMPLE, FULL or BULK_LOGGED recovery models. To permit log backups, before taking a full database backup, the database need to be set to use the full recovery model by using: ALTER DATABASE myDB SET RECOVERY FULL statement, this is an important step.

ALTER DATABASE myDB SET RECOVERY FULL
BACKUP LOG myDB
TO myDB_log1
GO

If database is damaged & inaccessible, you'd need to use the NO_TRUNCATE clause, provided that the transaction log file is accessible & undamaged .

ALTER DATABASE myDB SET RECOVERY FULL
BACKUP LOG myDB
TO myDB_log1
WITH NO_TRUNCATE
GO

This WITH NO_TRUNCATE clause allows the active part of the transaction log to be backed up even if the database is inaccessible.

You can also shrink the log (LDF) file without shrinking database (MDF) file by using the DBCC SHRINKFILE syntax:
Shrink the log file to 1MB:

DBCC SHRINKFILE (myDB_log, 1)[WITH NO_INFOMSGS]

To empty the log file:

DBCC SHRINKFILE (myDB_log, EMPTYFILE) [WITH NO_INFOMSGS]

Doubts & questions are most welcome..

[Greg]

Thanks Rock, a great detailed explanation. I will give it a go

[eUKhost.com]

Thanks Rock, a great detailed explanation. I will give it a go

Greg !

Thank you for joining our forum.