C# / .NETDevOpsMisc
DevOps
MSSQL Shrink / Truncate transaction log
Alexandru Puiu
Alexandru Puiu
March 10, 2019
1 min

SQL Transaction logs allow you to restore a database to a specific point in time, and is a great option for a production database, but these logs must be backed up frequently enough to prevent them from filing up. However, you need to back up the transaction log itself, not just the database, if you want to use the Full or Bulk Logged recovery models. This article describes the right way to set this up: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-a-transaction-log-sql-server?view=sql-server-2017 

If you’ve already taken a full backup of your database, and find yourself in a crunch out of space, the following is a quick way of clearing the transaction log to recover space:

Log into Microsoft SQL Server Management Studio

Right click on your database.

Select Properties.

sql shirnk db 1

Switch to the Options tab.

Change the recovery model to Simple.

sql shirnk db 2

Click OK.

Right click on the database.

Select Tasks > Shrink > Files.

sql shirnk db 3

Select Log for the file type

sql shirnk db 4

Click OK.

Your transaction log should now be empty and the space reclaimed. Now you should change your recovery model back to Full or Bulk Logged.

Right click on the database.

Select Properties.

Click on Options.

Change the recovery model to Full.

Click OK.


Tags

sqldebugging
Alexandru Puiu

Alexandru Puiu

Engineer / Security Architect

Systems Engineering advocate, Software Engineer, Security Architect / Researcher, SQL/NoSQL DBA, and Certified Scrum Master with a passion for Distributed Systems, AI and IoT..

Expertise

.NET
RavenDB
Kubernetes

Social Media

githubtwitterwebsite

Related Posts

Hide SQL DB
C# / .NET
SQL Server hide database list from a particular user
November 05, 2015
1 min

Subscribe To My Newsletter

I'll only send worthwhile content I think you'll want, less than once a month, and promise to never spam or sell your information!
© 2023, All Rights Reserved.

Quick Links

Get In TouchAbout Me

Social Media