Shrinking SQL database transaction logs

This support note applies to:

AO Document Manager (AU)
AO Document Manager (NZ)
AE Document Manager (NZ)
AE Document Manager (AU)
AE Practice Manager (NZ)
AE Practice Manager (AU)
AE Tax Series 6 & 8 (AU)
AE Tax (AU)

Article ID: 35772

If you have been using Full Recovery mode the transactions log can become quite large and take up an exponentially large amount of space.

If the log file is too large the first step should be to put the server into Simple recovery mode, do a backup and shrink as per Shrinking my SQL database using SQL Management Studio.

If this does not resolve the issue please have your IT person perform the following. Before you begin, backup your SQL database. Refer to Backing up an SQL database using Management Studio

This process should only be done by a person who is comfortable with troubleshooting and maintaining SQL servers and databases.

It could indicate that the free space inside the log file is not being made available after a full backup e.g. it is still 98% in use, instead of reducing to 2% in use so a shrink is unable to reduce it.

To detach the specific database

Refer to Attaching and detaching SQL databases

To re-attach the specific database using a query

EXEC sp_attach_single_file_db @dbname = 'VPMSER',

@physname = 'c:\MSSQL\Data\VPMSER.mdf'

Where: c:\MSSQL\data\VPMSER.mdf is the path to your database .mdf file; and VPMSER is the name of your database (i.e VPMSER, AEDB1).

As long as the database is left in Simple recovery mode the log file should stay relatively small.