Shrink SQL transaction logs

Last Modified

Thu Feb 08 19:37 GMT 2018

Description

Sometimes when reinstalling N-able Report Manager on a server, there will not be sufficient space due to large transaction Logs. They can be shrunk by following the steps below.

PackageStart - Fact Transform - Device Service Parameters on: 2/8/2018 10:21
OnError,,, ETL STATUS ExecuteSSIS: Package failed
"The transaction log for database 'warehouse' is full due to 'ACTIVE_TRANSACTION'.".
Description: Executing the query "-- Required parameters
PackageEnd - Fact Transform - Device Service Parameters on: 2/8/2018 10:22 Duration: 0 hrs, 1 mins, 33 secs

Environment

  • N-able N-central
  • SQL Server on which you're re-installing N-able Report Manager, with limited disk space.

Solution

  1. Check that a Limit was not set on the Database throwing the error.
    1. Open SQL Management Studio > expand Databases > right-click the Database name mentioned by the error > select Properties > Files > scroll to the Autogrowth/Maxsize column > click the "..." button.
    2. On the Change Autogrowth dialog window, choose Unlimited for the Maximum File Size.
  2. Allow the ETL to run again and check if the issue is resolved by above. Otherwise, there could be an issue with actual disk space so we will want to try clearing the transaction logs.
  3. Stop & Disable the N-able Report Manager Maintenance and N-able Report Manager Maintenance Watchdog service via services.msc or the Command Prompt as Administrator:
    1. sc config "Report Manager Maintenance Watchdog" start= disabledsc config "Report Manager Maintenance" start= disabledsc stop "Report Manager Maintenance Watchdog"sc stop "Report Manager Maintenance"net stop "Report Manager Maintenance"net stop "Report Manager Maintenance Watchdog"
  4. Check that Export/ETL is not in progress.
    1. ETL in progress - DTEXEC.exe is running in Task Manager.
    2. Check that Export is not running (Completion Time is AFTER Start Time). Open SQL Management Studio > expand Databases > config > right-click Datasource > click New Query > paste the query below & Execute: 
      --Example: export is completed because Completion > Start.SELECT[DataSourceID]      ,[External N-able N-central Server FQDN]      ,[Last Export Start Time]      ,[Last Export Completion Time]  FROM [config].[dbo].[DataSource]DataSourceIDExternal N-able N-central Server FQDNLast Export Start TimeLast Export Completion Time1185.22.33.442018-02-08 17:55:19.3272018-02-08 18:56:36.733
  5. Launch SQL Management Studio.
  6. Right-click on the Database, and choose Tasks > Shrink > Files.
  7. For File Type, select Log.
  8. Select the Radio button for Reorganize pages before releasing unused space.
  9. Set Shrink file to 200MB.
  10. Click OK.
    1. ?
  11. Restart the N-able Report Manager services. 
    sc config "Report Manager Maintenance Watchdog" start= autosc config "Report Manager Maintenance" start= autosc start "Report Manager Maintenance Watchdog"sc start "Report Manager Maintenance"net start "Report Manager Maintenance"net start "Report Manager Maintenance Watchdog"