How to shrink SQL Server Database log file

Shrink SQL Server Log Files.

I have been asked this question lot of time that my log file size is increased and I want to shrink it but don't know how to perform this.

So I decided to make a post for this task so it can help people.

Lets start on the process.

1) Log in to the SQL Server using SSMS  (SQL Server Management Studio) with administrator credentials.

2) Select the database whose log file size is increased.

3) Take the full backup of that database so that in case of any issue during shrink operation we will have the original backup and it can be restored. Make sure the recovery model should be "Full"as displayed in image below.

4)  Right click the database and click properties it and select the "Files" option from left menu of Database Properties dialog box. You can see the log file size here as displayed in image below.

 5) Now you need to change the recovery option to Simple instead of full. So click the "Options" tab in Database Properties dialog box. Now choose the Recovery model to "Simple".

 

 6) Now Click OK and close this dialog box. Right click the database and select Tasks > Shrink > Files menu

7) It will open the Shrink File dialog box. Select the "Log" from File Type drop down and select the "Release unused space" option as displayed in image below.

8) Now click the OK button and it will start the Shrink process and after some time it will shrink the log file size and you will notice the log file size would be minimized.

9) Now you can revert back the recovery model to "Full" as it was earlier. To do this follow the step number 5 and select option "Full" and hit OK button.

Hope this will help you. 

Be happy any stay optimized.

Thanks.

 

Interesting things about Web API in Asp.net MVC

Few things I learn in WEB API which is little interesting. I have spent lots of time to fix small issues and after doing google I found the solutions for those small issues, so I decided to document those solutions somewhere. The Blog is perfect place to document it so that I can refer it any time.

Here is the list of issues and their solutions which I faced last night.

1) Not able to call WEB API from client application.
                 When I started learning WEB API in ASP.NET MVC, I found most of the examples where the API has been called from the same project and it was working fine, but in production environment we have to call API from client applications. When I tried the same it was not working. 
Solution:  
                You need to enable CORS in order to allow remote call to your web API. 

• Add System.Web.Http.Cors reference in your project. If you can't find it, add the nuget package for the same. 
• Go to the  App_Start folder and click WebApiConfig.cs
• Add these two lines in Register function
• var cors = new EnableCorsAttribute("*","*","*");
              config.EnableCors(cors);  // add this line of code to allow cross domain request 

 

2) Custom API name was not working

                 If you have taken the default template for Web API development, it won't allow you to use your favorite name for the API for example if you want to use SubmitOrder for post API it won't identify from client side call. 

 Solution:

            You need to add Route attribute to tell MVC that this is my API here is the example

            [Route("api/Test/SubmitOrder")]

            public HttpResponseMessage SubmitOrder(FormDataCollection data)

           {

                     // your code goes here

           }

           Before you add Route attribute to your function, you need to add following line in your WebApiConfig.cs file 

            config.MapHttpAttributeRoutes();

 

3) How to return data / object in HttpResponseMessage

               It is always a best practice to return HttpResponseMessage from your web api but how to return the data in the response? Most of the examples either returns data / list of data or string or integer or HttpResponseMessage either. 

 

Solution:

 

              You can include an object in your response like this.

               

        [Route("api/Test/SubmitOrderThreePara")]
        [HttpPost]
        public HttpResponseMessage SubmitOrderThreePara(FormDataCollection frm)
        {           
            Customer cm = new Customer();
            cm.City = "Ahmedabad";
            cm.ContactName = "Test User";
            cm.Address = "Test Address";
           
            return Request.CreateResponse(HttpStatusCode.OK, cm);

        } 

 

 

  

 

 

 

           

 

Application_Error not firing in ASP.NET MVC

Application_Error not firing in ASP.NET MVC

If you want to handle errors from single location, Global.asax is the best place to handle it. You can get the error, log it and inform the user about error in a custom way, but what if the Application_Error is not getting fired on error event?

Yes I faced this issue and after doing google I found few causes which prevents the Application_Error event.

1) Set the  customErrors = "On" in web.config

2) comment the filters.Add(new HandleErrorAttribute()); in RegisterGlobalFilters().

 
public class MvcApplication : System.Web.HttpApplication
{
    public static void RegisterGlobalFilters(GlobalFilterCollection filters)
    {
        filters.Add(new HandleErrorAttribute()); // this line is the culprit
    }
...
}

 When you create new MVC project the HandleErrorAttribute is by default added in

GlobalFilters which prevent Application_Error event to be fired. So just comment 

that line in FilterConfig.cs file which is under App_Start directory.

How to enable gzip compression in IIS7

Overview:
          Gzip compression is used to compress your content and then deliver to client. Today's modern browser supports compression. So it is a good idea to deliver the static content after compression, this way you can save lots of bandwidth and your page will be load faster as it has to send compressed data.

How to enable gzip compression in IIS7

The first and most thing is to make sure the compression module is installed on not? check the below mentioned link for installation process.

http://www.iis.net/configreference/system.webserver/httpcompression


Basic steps to enable the HTTP compression on static Content

• Start > Run > Inetmgr > hit enter
• Select your web site
• In Features View, double click Compression
• Check the Enable Static content compression
• Click Apply in the Actions pane.

Above steps must enabled the static compression and your js, css etc static contents should be compressed.

Exceptional Settings

Sometimes you will not get your content compressed after doing above mentioned steps. I recently got the experience and I would like to share the solution with you.

Set read permissions to applicationHost.config file for IIS_ISURS group. 

• You will find this file at C:\Windows\System32\inetsrv\config. 
• Right click to this file and click properties, now click on security tab
• Now click on Edit button
• Now click on Add button under Group or user name
• Now enter IIS_IUSRS 
• Click Check Names button
• Now Select Read & Execute and Read permission
• Click Apply, then Click OK

Check the httpCompression types

    Check the httpCompression section in applicationHost.config file, you should find <add mimeType="application/x-javascript" enabled="true" />. If this is correct then it should be correctly configured in your IIS. 

• Now go to IIS Manager and select your site.
• Click on Features View from bottom
• Now click on MIME Types
• Find the .js entry
• If the .js extension has MIME Type = "application/javascript" then remove that entry.
• Now click Add from Actions pane
• give extension as .js
• give MIME type as application/x-javascript
• click apply.
• Restart the website.

This will start compressing your .js files.

.css files would compressed automatically if other configurations are correct.

How to add Expiry Headers to Static Content from web.config

The easiest way to add the expiry headers or cache control in static content from web.config file is:

Add following line of code in web.config file.
 <system.webServer>
    <staticContent>
       <clientCache cacheControlCustom="public"
    cacheControlMaxAge="720:00:00" cacheControlMode="UseMaxAge" />
    </staticContent>  

  </system.webServer>


Look at the value cacheControlMaxAge="720:00:00"  it suggests the static content will be cached in users browsers for 30 days. This works perfectly and this is the easiest way to handle the cache control in asp.net web site.

Query to find Costly Queries in sql server

Today i found one script which shows top 50 costly queries. It takes those queries who consumes most CPU.

SELECT TOP 50
 [Average CPU used] = total_worker_time / qs.execution_count,
 [Total CPU used] = total_worker_time,
 [Execution count] = qs.execution_count,
 [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
         (CASE WHEN qs.statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
          ELSE qs.statement_end_offset END -
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;

Script to get the number of rows in all tables

Here is the script to get the list of tables with number or rows in each table [SQL Server 2008]. This will help you when you want to know which tables are heavy. When it comes to performance issue this query is very helpful. Recently I find this query from here thanks to Jeremy Jameson. I added this query in this post as i want to make it handy.

SELECT sysobjects.Name , sysindexes.Rows 

FROM 

sysobjects 

INNER JOIN sysindexes 

ON sysobjects.id = sysindexes.id 

WHERE 

type = 'U' 

AND sysindexes.IndId < 2 

ORDER BY 

 2 desc



It will produce the result as shown below.


Table1 50000




Table2 45000




Table3 40000




Table4 10000