Thursday

Fix Error 9002: Transaction Log Full For SQL Server

Fix Error 9002: Transaction Log Full For SQL Server
04:52 by andrew jackson

Overview of SQL Server Error 9002

Sometimes while working on SQL Server database we come across an error 9002. Which indicates that Transaction log for SQL Server is full. There may be several reasons through which the log file may become very large and run out of space or become full. Transaction log error 9002 generally occur when a log file is full or the disk space in which log file stored is full and cannot expand the log files further. In such circumstances, database remains online, but can only be read and no update operation can be performed. If this error occur while recovery then databases marked as resource pending.

Error 9002 msg:
The log file for database '%*Is' is full. Back up the transaction log for the database to free up some log space.
SQL Server database records all the transactions and the modifications done by each transaction. Transaction log must be regularly truncated to maintain the log space and keep it away from filling up. Some operations can be minimally logged to reduce their impact on transaction log size.
If the error 9002 occurred, when database was in recovery mode then after resolving problem ALTER DATABASE database_name SET ONLINE must be used.

How to Fix SQL Server Error 9002?

Following action can be performed for troubleshooting SQL Server transaction log full Error 9002:
  • Log backup can be done.
  • Log file can be moved to some other disk having sufficient space.
  • Log file size can be increased.
  • Freeing disk space so that the log file may grow automatically.
  • Long running transactions could be killed.
  • Adding a log file to other disk.
However, Transaction log is an essential part of database, which is required to return the database in consistent state if any system failure occurred. That’s why shrinking, deleting or moving transaction log be done after fully understanding the outcome of the action performed.
The above actions performed for troubleshooting t-sql error 9002 described below:

Transaction Log Backup

In case database uses the full or bulk-logged recovery model, and transaction log backup has not been done recently. Then there is need to take recent backup of the transaction log to free some space and supports restoring the database from a specific point. Log backup should be taken frequently to keep the log from filling up again.

Moving Log File to Another Disk

If creating enough space on the disk that containing the log file is not possible, then log file should be moved to some other disk having sufficient space. While moving log file one should never place the log file on a compressed file system. The log file can be moved by using concept of database detach and attach.
sp_detach_db executed to detach database.
sp_attach_db executed to attach database.

Increase Log File Size

As maximum size for log file is two terabytes(TB). So if space available on the disk log file size can be increased. Size can be increased manually to produce a single grow increment in case autogrow disable and a database is online. Also, we can enable autogrow by using ALTER DATABASE statement.

Freeing Disk Space

The disk containing the transaction log file can be freed by deleting or moving some files from that disk to another disk. Freeing the disk space will enlarge the log file automatically on that disc.

Add Log file to Another Disk

A new log file can be added to the database on the different disk by using ALTER DATABASE ADD LOG FILE.

Conclusion

We get to know about SQL Server Transaction Log Error 9002. Error 9002 in SQL Server occurred when the transaction log file is full. While moving or deleting a transaction log file the outcome of the action performed should be kept in mind. Several actions which can be performed to solve this error are discussed briefly.

Suggested Reading

MVC Areas: Organizing an Application using Areas

MVC Areas: Organizing an Application using Areas
08:25 by Anjan Kant

Introduction

MVC architecture is inherently separated into three separate layers like Model (database), Presentation (UI, View), and Controller (Business layer). Releasing of MVC 2 introduces new feature of Area which provide solid capability to further separate files of file structure. Using with Area, we can define same controller in different areas to make our application more modular, manageable and robust. In this demonstration, I have used two areas Main (Public website) and blog area. In my earlier article, how to stop cross-site request forgery. I am explaining all steps how to organizing an application using areas in MVC.
mvc area

Why We Need MVC Areas

When we need multiple departments to maintain with different controllers even having with the same name in different areas. It makes our application more manageable, easy and maintainable to make our development faster.

Create a New MVC Project

First of all, create a new fresh project of MVC

create new project mvc area

New Project Crated

New project created for playing MVC feature of Area.

new project created

Create a New Area

Let’s now start to create a new area. Right click on project then move to Add >>> Area then provide a new relevant name of area.
Create mvc area

Creating Blog Area

Here typing relevant new area name like “Blog”.
type blog area name

Blog Area Created

Now you can see new created area “Blog” under the project.
Area Blog Created

Blog Area File Registration

You can see a new file crated of Blog Area Registration (BlogAreaRegistration.cs) under blog area.

public override string AreaName
{
    get
    {
 return "Blog";
    }
}

public override void RegisterArea(AreaRegistrationContext context)
{
    context.MapRoute("Blog_default",
        "Blog/{controller}/{action}/{id}",
 new { action = "Index", id = UrlParameter.Optional }
    );
}

Area Registration in Global.asax

Area registration is also done in Global.asax file.
public class MvcApplication : System.Web.HttpApplication
{
    protected void Application_Start()
    {
    AreaRegistration.RegisterAllAreas();

        WebApiConfig.Register(GlobalConfiguration.Configuration);
    FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
        RouteConfig.RegisterRoutes(RouteTable.Routes);
    }
} 

Main Area Creation

Next one new main area is crating in this section.
Main blog area created
Area Registration File Created
In the same way, as in blog area a registration file (MainAreaRegistration.cs) is also created.

public class MainAreaRegistration : AreaRegistration
{
 public override string AreaName
        {
        get
        {
            return "Main";
        }
    }

public override void RegisterArea(AreaRegistrationContext context)
    {
        context.MapRoute(
            "Main_default",
            "Main/{controller}/{action}/{id}",
            new { action = "Index", id = UrlParameter.Optional }
        );
    }

Create Home Controller for Main Area

Creating home controller under main area.
Home controller for main area

Create Home Controller For Blog

Alike main area, Home controller is also creating under blog area. Area feature let allow us to create same controller name to make our development more lucrative.
Home controller for blog area

Create View for Both Home controllers of Main & Blog

Here is creating view for Blog and main area.

@{
ViewBag.Title = "Index";
}
<h2>Welcome to Blog Area</h2>
<div> @Html.ActionLink("Back to Main", "Index", "Home", new { area = "Main" }, new { }) </div>
 Main Area View
@{ ViewBag.Title = "Index"; }
<h2>Welcome to Main Area</h2>
<div>
    @Html.ActionLink("Go to Blog", "Index", "Home", new { area = "Blog" }, new { })
</div>

Set Default Area/Controller/Action

Here, you can set your default area, controller and method to launch your application (landing page of your application). Before to set, you need to right click on project and click on properties to navigate on Web tab to set application default page (Area/controller/action).

Area Default Page

or Alternatively can set in RouteConfig.cs

Routes.MapRoute(
                name: "Default",
                url: "{controller}/{action}/{id}",
                defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional },
                namespaces: new[] { "slnMvcArea.Areas.Main.Controllers" }
            ).DataTokens.Add("area","Main");

Now Run your MVC area application

Finally run your MVC app to see how works MVC area feature.

welcome to main area

Navigate to Blog Area

Click on “Go to Blog” to navigate blog area.

welcome to blog area
Now you are on blog area, here is also a link to navigate “Back to Main”.

Conclusion

MVC areas helps us to make more manageable our MVC application to further divides into different files. In each area, we can define same controller like Main area contain “Home” Controller then also other areas can contain same area name “Home”, this code will compile perfectly even we have same controller name in different areas. Thus, it will help us to make large volume of applications with multiple departments so that can build awesome MVC apps with area feature. I tried here to demonstrates all necessary steps to organize an application using areas in MVC app.

Video: This video also demonostrates about Organizing an Application using Areas.