Force Shrink a SQL transaction log file

By -
Imagine this. Your database transaction log has grown tremendously. You don't have enough space to do a backup of it and proper truncation. You do, however, have a backup of your database and are sure that you can restore it without any issue. What can you do?

Here is a script that I use to force shrink transaction logs. Note that this can potentially be harmful, so make sure that you have a working backup of your database. Having said that, I've done this on a number of log files, some growing up to 200GB big, and I've not had any problems whatsoever.

Replace the database_name variable in the first line with the actual name of your database (enclose in single quotes)

    DECLARE @DBName nvarchar(100) = 'database_name';

    -- Declare the variable to be used
    DECLARE @sql nvarchar(1000), @exec nvarchar(300), @logfile nvarchar(100);

    -- Initialize Exec StoredProc
    SET @exec = QUOTENAME(@DBName) + '.sys.sp_executesql'

    -- Get Logfile name
    SET @sql = 'Select @    logfile=Name from sys.database_files where type=1;'
    EXEC @exec @sql, @Params = N'@    logfile nvarchar(100) OUTPUT', @logfile=@logfile OUTPUT

    -- Shrink Logfile
    SET @sql = 'ALTER DATABASE '+ @DBName +' SET RECOVERY SIMPLE WITH NO_WAIT;'
    SET @sql += ' DBCC SHRINKFILE (['+ @    logfile +'], 1);'
    SET @sql += ' ALTER DATABASE '+ @DBName +' SET RECOVERY FULL WITH NO_WAIT;'
    EXEC @exec @sql;

    The process takes a bit of time. Around 20mins for every 100GB.

    Good luck!

    Comments

    Post a Comment

    Popular posts from this blog

    Windows RRAS VPN configuration when server is behind NAT

    By -
    The following configuration changes have to be done when the Windows VPN server (Remote Access and Routing Service) is behind NAT (ie, server has a private IP). To make PPTP work: Allow tcp/1723 traffic to flow through the firewall to the RRAS server Add inspect pptp on the firewall To make L2TP/IPSec work on Windows clients: On the client device, open Registry Editor Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\PolicyAgent Add a DWORD (32-bit) key named AssumeUDPEncapsulationContextOnSendRule   Set the value to 2, and base to Hexadecimal Restart the client PC (this is important). Done!
    Read more

    Lenovo X1 Carbon Battery Problem - Plugged, Not Charging

    By -
    Solution taken from  https://stebalien.com/blog/fix-for-lenovo-x1-carbon-not-charging/ Happened on one of our laptops. The battery was draining and if you plug in the charger, it just says "Plugged In, Not Charging" To fix: Unplug the charger Restart the laptop Press Enter to interrupt normal startup Press F1 to enter the BIOS Seup Go to Config, Power Go to the bottom of the list where it says "Disable built-in battery". Press Enter. The laptop will shut down.  Plug in the charger and restart the laptop.
    Read more

    Multiple Remote Desktop sessions on Windows XP

    By -
    Continuing on from my last post about setting up RemoteApp on Windows XP. I needed to make the old application available to multiple users, so I have decided to modify the Terminal Service in my WinXP box to allow multiple remote session. I found the instructions here . It's pretty simple. You just need to replace termsrv.dll file and apply some registry changes. But you do have to consider that Windows XP employs a Windows File Protection system that detects when system files are changed and replaces it back with the original microsoft version of it. Here's what I did: Grab the file termsrv_sp3_patch.rar from the link above. Back the file C:\windows\system32\termsrv.dll into another location.  Open service.msc and change the Start-up property of the Terminal Service to Disabled Restart the computer. After restart, delete the file termsrv.dll from the following locations C:\windows\sytem32\ C:\windows\system32\dllcache C:\windows\ServicePacks\x86 Copy the termsrv.dll file from...
    Read more