Sibeesh Passion

Top Menu

  • Home
  • Communities
  • Search
  • About

Main Menu

  • Articles
    • Azure
    • JavaScript
    • Angular
    • Node JS
    • Web API
    • Career Advice
    • Interview
    • JQuery
    • Knockout JS
    • Jasmine Framework
    • Database
    • SQL
    • MongoDB
    • MySQL
    • Tools
    • IIS
    • Wamp Server
    • WordPress
    • Visual Studio
    • ASP.NET
    • MVC
    • PHP
    • SEO
    • HTML5
    • Office Development
  • Controls
    • HighChart
    • High Map
    • Ignite UI
      • igGrid
      • igDataChart
    • JQWidgets
      • JQX Grid
      • JQX List Box
    • Spire.Doc
    • Spire.XLS
  • My Contributions
    • Medium
    • ASP.NET Forum
    • C# Corner
    • Code Project
    • DZone
    • GitHub
    • JSFiddle
    • MSDN
    • Stack Overflow
  • Connect with Me
    • About Me
    • Facebook
    • Google Plus
    • Linkedin
    • Pinterest
    • Twitter
    • Quora
    • YouTube
  • Videos
  • Awards
  • SibeeshVenu.com
  • Home
  • Communities
  • Search
  • About

logo

Sibeesh Passion

  • Articles
    • Azure
    • JavaScript
    • Angular
    • Node JS
    • Web API
    • Career Advice
    • Interview
    • JQuery
    • Knockout JS
    • Jasmine Framework
    • Database
    • SQL
    • MongoDB
    • MySQL
    • Tools
    • IIS
    • Wamp Server
    • WordPress
    • Visual Studio
    • ASP.NET
    • MVC
    • PHP
    • SEO
    • HTML5
    • Office Development
  • Controls
    • Ignite_UI_Chart_Control

      Working With IgniteUI Chart igDataChart Control

      July 14, 2016
      0
    • Apply License Key

      Working With IgniteUI Grid Control

      July 10, 2016
      0
    • Grid with check box selection

      Implement radio button selection in JQWidgets JQXGrid using checkbox

      June 29, 2016
      0
    • High Chart

      Client Side Exporting In HighChart

      May 27, 2016
      0
    • Dynamically apply colour coding in Grid

      Dynamically Apply Colour Coding in Grid With Dynamic Headers And Data

      February 16, 2016
      0
    • Dynamically apply colour coding in Grid

      Remove Time Stamp Form Legend In HighChart

      January 25, 2016
      0
    • Select NuGet Package

      Using MVC Grid In MVC

      December 11, 2015
      0
    • Column values to a href

      a href Columns In Grid

      November 11, 2015
      0
    • Load Data On Scroll

      Load Data To Grid On Sroll

      November 2, 2015
      0
    • HighChart
    • High Map
    • Ignite UI
      • igGrid
      • igDataChart
    • JQWidgets
      • JQX Grid
      • JQX List Box
    • Spire.Doc
    • Spire.XLS
  • My Contributions
    • Medium
    • ASP.NET Forum
    • C# Corner
    • Code Project
    • DZone
    • GitHub
    • JSFiddle
    • MSDN
    • Stack Overflow
  • Connect with Me
    • About Me
    • Facebook
    • Google Plus
    • Linkedin
    • Pinterest
    • Twitter
    • Quora
    • YouTube
  • Videos
    • Bing Speech And Translate Text

      Webinar: Let us Build a Speech, Translation API using AZURE Bing Speech ...

      June 30, 2018
      0
    • Text Translator Api Thumbnail

      Video: Azure Cognitive Services Text Translator API

      June 30, 2018
      0
    • Bing Speech Api Thumbnail

      Video: Azure Cognitive Services - Bing Speech API

      June 30, 2018
      0
    • Video: 21 Easy Tips for Healthy Life

      February 22, 2018
      0
    • Azure Face API

      Webinar: Azure Cognitive Service Face API

      July 23, 2017
      0
    • Custom Stylish Handlebar Mirror in Royal Enfield Thunderbird

      June 25, 2017
      0
    • Wrapping Rope in Royal Enfield Crash Guard Or Bumper

      April 22, 2017
      0
    • How to Change Handlebar in Royal Enfield Thunderbird

      April 22, 2017
      0
    • Video: How to be a successful software engineer

      April 22, 2017
      0
  • Awards
  • SibeeshVenu.com
  • Create Your Own Cryptocurrency in Private Consortium Network Ethereum Azure Blockchain

  • Detect Noise Level Audio Decibels in MXChip Azure IoT DevKit

  • Deploy Angular App Using Azure DevOps Build and Release Pipelines

  • MXChip Device with Pressure, Humidity, Temperature Info using Azure IoT Workbench

  • Realtime IoT Data using Azure SignalR and Functions in Angular

ArticleSQL
Home›Article›Foolproof Tips for Reading and Shrinking Transaction logs in SQL Server

Foolproof Tips for Reading and Shrinking Transaction logs in SQL Server

By Andrew Jackson
June 7, 2018
1148
0
Share:

Table of contents

  • Query-1: SQL Server log file growing unexpectedly?
    • SOLVED: SQL Server Log File Getting Too Big
      • What to do for stopping growing log file too big
  • Query-2 How To Shrink Transaction logs File in SQL Server?
    • SOLVED: Reduce SQL Server Database Transaction Log File
      • Shrink By SQL Server Management Studio:
      • Shrink By T-SQL:
      • Tips when you plan to shrink database or file:
      • Is Shrinking database Bad?
  • Query-3 How to check Deleted Records in SQL Server?
    • SOLVED: Ways to Check Deleted Records in SQL Server
      • How to Read Transaction Log Quickly
  • Conclusion

Query-1: SQL Server log file growing unexpectedly?

“We are wondering why transaction logs keep on increasing?? It become so gigantic, the transaction log size get increase from 135GB to 350GB and that’s so annoying. My drive containing data store is losing space. I am using Full Recovery Model and the logs are backed up hourly. Please help me out why this is happening and what should be done to stop growing it too big?”

SOLVED: SQL Server Log File Getting Too Big

SQL Server Transaction logs records all activities of transaction logs. Each database in SQL Server instance consists of logs that records all the modification done in the databases. It is crucial component of the databases. Any modification, deletion can be tracked down in transaction logs.

There are various reasons of transaction log file getting expanded. The possible reasons are listed below:

  • Large transaction log file
  • Transaction taking too long to complete
  • Transaction operation fail & may start to rollback
  • Issue in performance
  • Blocking issue
  • Database participating in AlwaysOn availability group

What to do for stopping growing log file too big

  1. Change the type of Recovery Model to Simple Recovery Model only if you are fine with not able to restore logs hence, failed point in time recovery. It is because if you are truncating your logs, you are breaking Transaction log LSN & if any disaster occur, you will not able to restore T-logs. Hence if you are fine with this situation, then change your Recovery model to Simple. This will not allow any extra growth of log file.
  2. Take T-log backup every hour in Full recovery Model, your transaction log will keep on growing until you take backup and truncate of it. While taking hourly T-log backup, the logs will grow until one hour, but after this it would truncate all commited transaction.

Sometimes it might happen that you are in Simple Recovery Model, but your logs are still growing!!! Reason being is long running transaction. If your transaction taking long time when deleting thousand records in one delete statement, then logs will not be able to truncate until delete operation is done.

You can save yourself by maintaining proper size of log file or monitoring the usage of transaction.

By doing this, you can cut down the size of Transaction log.

Query-2 How To Shrink Transaction logs File in SQL Server?

“My log file size is growing continuously. Why is this happening?? I am lacking my disk space. I want to shrink my Transaction log file. Can anyone tell me how to shrink transaction logs in SQL Server. What will be the after effects if I shrink the log file.

SOLVED: Reduce SQL Server Database Transaction Log File

Relax!! There are several reasons for transaction log file growth. Long Running transaction, lack of taking log backup are some of the reasons which result in transaction log growth. Databases running for business purposes or production requires appropriate recovery model to be chosen for log management.

Log truncation frees up the log file space so that logs can reuse it. Truncation occur automatically if the database is backed up in Simple Recovery Model or after log backup when database is in Full Recovery Model.

Here I will resolve your shrink issue by two methods: By using SQL Server Management Studio and by using T-SQL.

Shrink By SQL Server Management Studio:

  • Right Click on your Database–>Tasks–>Shrink—>Files.
  • You will get the Shrink File Window, Change the File Type to Log.
  • There are three different actions under Shrink Action option. Either release the unused space, or shrink file to reorganize pages before releasing unused space, or empty file by migrating the data to other files in the same filegroup. Choose it according to your need.

Shrink By T-SQL:

You can simply shrink transaction log file by running DBCC SHRINKFILE after taking log back. If you are using Simple Recovery Model, run below code:

DBCC SHRINKFILE (TestDB_log, 1)

If your database is in Full Recovery Model, first of all, set it to Simple Recovery Model, Then run DBCC SHRINKFILE and then set it to full.

Caution: Setting database again in Full Recovery Model leads to loss of data in the Log. So, if you dont care about losing your log data, then definitely go for it!

Tips when you plan to shrink database or file:

  • Shrink operation found to be most effective after delete or drop table operation as it create lot of unused space.
  • If you are repeatedly shrinking your database and you notice that the size of database grows again. Alert!! Shrinking database is a wasted operation, as most database require free space for regular day-to-day operation. This indicates that the space that was shrunk was a part of regular operation.
  • You should not shrink your database or data file after rebuilding indexes. As shrinking increases fragmentation to extreme level.
  • Do not ON Auto_Shrink option. (Unless you have very urgent requirement)

Is Shrinking database Bad?

Well, Yes.

After shrinking operation,you will definitely be able to reduce the size of the database. But Wait!! Check your Fragmentation level first. It is found to be way too much!!!

Shrink operation increases the value of the fragmentation way too much. And higher fragmentation costs you poor performance of the database.

While if you are thinking that you can reduce fragmentation by using rebuild index, let me alert you, this will definitely reduce your fragmentation level. But Let me check the size of the database The database size increases way higher than the original.

Query-3 How to check Deleted Records in SQL Server?

“I am working in an organization and my manager assigned me work to check deleted database records of my database. How can I check my deleted records in SQL Server? Please Help me out from this situation.”

SOLVED: Ways to Check Deleted Records in SQL Server

You can track down your deleted database records by reading Transaction logs. And you can read it by using fn_dblog function. You can track down any DML as well as DDL activity like insert, delete , update, create operation by checking your transaction logs.

Fn_dblog function enables users to read all the database activity happening in SQL database. The function require begin and end LSN number for a transaction. You can easily trackdown the information of a person who did changes to your database. As LSN number are not in human readable form, SQL Server provide fn_dblog function for easy reading of transaction logs.

You can simply check your database activity by implementing below code:

USE ReadingDBLog;
GO
select [Current LSN],
[Operation],
[Transaction Name],
[Transaction ID],
[Transaction SID],
[SPID],
[Begin Time]
FROM fn_dblog(null,null)

How to Read Transaction Log Quickly

One quick way to read SQL Server database activity is to use SysTools SQL Log Viewer. It read & analyse operation like insert, update, delete. The software previews all .ldf activities like transaction name, table name, query etc. You have option to choose the database from different mode ie Online & Offline mode. You will able to fetch it from live databases. If your database is in Simple Recovery Mode, the tool is capable to recover deleted database records. You will get three different option for exporting. You can export it as in SQL Server Database , as CSV or as Compatible Script.

Conclusion

The blog covers various users queries about transaction logs along with its possible solution. I have discussed why transaction logs keep on increasing, how to track down your transaction details of your database and how to shrink transaction logs. I have also discussed some quick tips when you shrink your database.

TagsSQLSQL ServerSQL TransactionTransaction logs in SQL
Previous Article

Most common interview questions which may be ...

Next Article

Video: Azure Cognitive Services – Bing Speech ...

0
Shares
  • 0
  • +
  • 0
  • 0
  • 0
  • 0

Andrew Jackson

I am SQL DBA and SQL Server blogger too. I like to share about SQL Server and the problems related to it as well as their solution and also I do handle database related user queries, server or database maintenance, database management etc.

Related articles More from author

  • Code SnippetsSQLSQL Server

    Find Out the Missing Indexes in SQL

    May 31, 2015
    By SibeeshVenu
  • Excel Export In MDX
    ArticleASP.NETMDX QuerySQL

    Export MDX Result As Excel

    October 28, 2015
    By SibeeshVenu
  • ArticleMicrosoft TechnologiesSQLSQL Server

    SQL Server Tips and Tricks

    December 29, 2014
    By SibeeshVenu
  • Code SnippetsSQLSQL Server

    Find the Relationship Difference Between Two DB

    May 31, 2015
    By SibeeshVenu
  • ArticleASP.NETSQL

    Passing a DataTable to a Stored Procedure

    May 31, 2015
    By SibeeshVenu
  • ArticleQ&ASQLSQL Server

    Solution for saving changes not permitted error in SQL

    June 29, 2015
    By SibeeshVenu

Leave a reply Cancel reply

0

MICROSOFT MVP (3)

profile for Sibeesh Venu - Microsoft MVP

Recent Posts

  • Create Your Own Cryptocurrency in Private Consortium Network Ethereum Azure Blockchain
  • Detect Noise Level Audio Decibels in MXChip Azure IoT DevKit
  • Deploy Angular App Using Azure DevOps Build and Release Pipelines
  • MXChip Device with Pressure, Humidity, Temperature Info using Azure IoT Workbench
  • Realtime IoT Data using Azure SignalR and Functions in Angular
  • IoTHubTrigger Azure Function and Azure IoT Hub
  • Azure Function as Output Job Topology of an Azure Stream Analytics Job
  • Azure Stream Analytics Job and Tools for Visual Studio
  • An Introduction to Azure Stream Analytics Job
  • Raspberry PI SD Card Provisioning with Windows 10 IoT Core – Adding Packages to the Existing FFU Image

Archives

  • January 2019 (4)
  • December 2018 (6)
  • November 2018 (8)
  • October 2018 (5)
  • July 2018 (4)
  • June 2018 (4)
  • May 2018 (2)
  • April 2018 (3)
  • March 2018 (4)
  • February 2018 (4)
  • January 2018 (1)
  • December 2017 (3)
  • November 2017 (3)
  • October 2017 (1)
  • September 2017 (2)
  • July 2017 (3)
  • June 2017 (3)
  • May 2017 (7)
  • April 2017 (9)
  • March 2017 (9)
  • February 2017 (2)
  • January 2017 (4)
  • December 2016 (2)
  • November 2016 (2)
  • October 2016 (1)
  • August 2016 (1)
  • July 2016 (7)
  • June 2016 (9)
  • May 2016 (11)
  • April 2016 (6)
  • March 2016 (14)
  • February 2016 (15)
  • January 2016 (11)
  • December 2015 (8)
  • November 2015 (16)
  • October 2015 (22)
  • September 2015 (11)
  • August 2015 (20)
  • July 2015 (27)
  • June 2015 (35)
  • May 2015 (51)
  • April 2015 (10)
  • March 2015 (9)
  • January 2015 (17)
  • December 2014 (5)
  • November 2014 (2)
  • October 2014 (3)
  • August 2014 (1)
  • July 2014 (1)
  • December 2013 (1)

Categories

  • Achievements (34)
    • Microsoft MVP (1)
  • ADO.NET (1)
  • Android (1)
  • Angular (26)
  • Arduino (2)
  • Article (255)
  • Article Of The Day (3)
  • ASP.NET (35)
  • Asp.Net Core (2)
  • Automobile (5)
  • Awards (8)
  • Azure (34)
    • Azure CDN (1)
    • Cognitive Services (3)
      • Translator Text (1)
    • Virtual Machine (4)
  • Blockchain (1)
  • Blog (14)
  • Browser (1)
  • C-Sharp Corner (17)
    • Monthly Winners (1)
      • October 2015 (1)
  • C# (19)
  • Career Advice (10)
  • Code Snippets (53)
  • CodeProject (31)
  • CSS (6)
  • CSS3 (5)
  • Database (36)
    • MongoDB (5)
    • MySQL (3)
    • SQL (28)
      • SSAS (2)
    • SQL Server (11)
  • Docker (2)
  • Drawings (1)
  • Excel Programming (3)
  • Fun (1)
  • How to (35)
  • HTML (5)
  • HTML5 (18)
    • Storage In HTML5 (2)
  • IIS (3)
  • Interview (6)
  • IoT (11)
  • JavaScript (15)
  • JQuery (77)
    • Exporting (1)
    • jQuery UI (1)
  • Json (7)
  • Knockout JS (2)
  • Linux (1)
  • Machine Learning (1)
  • Microsoft (8)
    • Microsoft Windows Server (1)
    • Outlook (2)
    • Skype (1)
    • Web Platform Installer (1)
    • WebMatrix (1)
  • Microsoft ADOMD (7)
    • MDX Query (3)
  • Microsoft Office (7)
  • Microsoft Technologies (7)
    • Office Development (5)
  • Microsoft Windows (9)
    • Windows 10 (5)
    • Windows 7 (1)
    • Windows 8.1 (2)
  • Mobile (2)
  • Mobile Brands (1)
    • One Plus (1)
  • MVC (6)
  • News (12)
  • Node JS (5)
  • npm (1)
  • Number Conversions (1)
  • Page (1)
  • PHP (3)
  • Poems (1)
    • Malayalam Poems (1)
  • Products (36)
    • High Map (2)
    • HighChart (8)
      • Drill Down Chart (1)
    • Ignite UI (2)
    • JQWidgets (19)
      • JQX Grid (18)
    • MVC Grid (1)
    • Spire.Doc (1)
    • Spire.PDF (1)
    • Spire.XLS (2)
  • Q&A (7)
  • Raspberry PI (5)
  • React (2)
  • SEO (2)
  • SharePoint (2)
  • Social Media (1)
    • Facebook (1)
  • Software (1)
    • Third Party Software Apps (1)
  • Stories (6)
    • English (2)
    • Malayalam (3)
  • Tips (2)
  • Tools (19)
    • GitHub (1)
    • SSMS (1)
    • Visual Studio (12)
      • Visual Studio 2017 (8)
  • Uncategorized (1)
  • Unit Testing (2)
  • VB.Net (1)
  • Videos (17)
  • Wamp Server (3)
  • Web API (12)
  • Webinars (1)
  • Wordpress (9)

ABOUT ME

I am Sibeesh Venu, an engineer by profession and writer by passion. I’m neither an expert nor a guru. I have been awarded Microsoft MVP 3 times, C# Corner MVP 5 times, DZone MVB. I always love to learn new technologies, and I strongly believe that the one who stops learning is old.  If you would like to know more about me, you can read my story here.

Follow me

CONTACT ME

  • X 384 / A, Katrikkal House, Vembilly Post, Kunnathunadu Panchayat, Ernakulam - 683565, Kerala, India
  • 08893 08893 2
  • info@sibeeshpassion.com

Optin Form

Tags

Achievements ADOMD Angular Angular 5 Angular JS article Article Of The Day Asp.Net Azure Azure IoT C# c-sharp corner Career Advice chart CSharp CSS CSS3 HighChart How To HTML5 HTML5 Chart Interview IoT IoT Hub Javascript JQuery jquery functions JQWidgets JQX Grid Json Microsoft MVC MVP MXChip News Products SQL SQL Server Visual Studio Visual Studio 2017 VS2017 Web API Windows Windows 10 Wordpress
  • Home
  • About
  • Communities
  • Search
© Copyright Sibeesh Passion 2018-2019. All Rights Reserved.