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


How To Enable Remote Access To MySQL Server on Windows Server

1. Go to the Start Menu and select Run to open a Command Prompt
2. Browse to the MySQL bin directory. I had to do

   C:>CD "Program FilesMySQLMySQL Server 4.1bin"

   Keep in mind that your directory structure might differ from mine depending on how you installed MySQL. Also you may have to do a few “Clear Directories” (i.e. “CD” or CD..) to get to the base of C:>.
3. Type in:

   mysql -u root -p

   and enter in your root password when prompted.
4. If you logged in successfully, you should see:

   mysql>

   and if not, then you will get something like:
   

   ERROR 1045 (28000): Access denied for user 'blah'@'localhost' (using password: YES)

5. Enter in

   GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD';

   What this will do is create a new user with ROOT privileges, so be very careful what account you are creating. If you are just using the root account, then replace USERNAME with root. And just so we are clear, USERNAME is the account you wish to create or use. IP is the physical IP address of the computer you wish to grant remote access to. If you enter ‘%’ instead of an IP number, that user will be able to remote access the MySQL server from any computer. PASSWORD is the password you wish to create if it’s a new user or the existing password of an existing account. And yes, you need to use the single quotation.
6. And finally, you want to run this last command:

   mysql> FLUSH PRIVILEGES;

7. To exit, just type:

   mysql> quit;

How to redirect from HTTP to HTTPS in ASP.NET

If you are a web developer and want to make an online shopping site, you will definitely asked to use SSL and use HTTPS request. Now the linked question in your mind is How do i redirect request from HTTP to HTTPS?

Here is the simple solution.

Go to Web.Config file and add below code stuff.

<rewrite>

<rules>

<rule name="Redirect to HTTPS" stopProcessing="true">

<match url="(.*)" />

<conditions>

<add input="{HTTPS}" pattern="^OFF$" />

</conditions>

<action type="Redirect" url="https://{HTTP_HOST}/{R:1}" />

</rule>

</rules>

</rewrite>

That's great your all request are now redirect to HTTPS. Wait this may not be happened with all the guys. Please make sure you have installed the URL Rewrite Module 2.0 available here.  If it is not installed and you try above code it will make your web.config file invalid and raise error.

After installing Rewrite Module 2.0 restart the IIS and your url redirect will start working.

How to prevent browser caching for css / javascript

Now a days i have an online shopping site for maintenance.
https://www.izabellondon.com
Since i have done lots of changes in the site, there are situations when we upload new css or javascript but client can not see the new changes because by default browser loads the css or javascript file from its cache memory if the URL is repeated. 


Then we have to ask our client to please clear your browser cache to get new changes. I am sure many of web developer has to face this situation then i decided not to ask our client for same thing.


I start wondering how to overcome this problem? after some google search i found the solution.


You need to add any random querystring after your call to javascript or css includes. for example.


CSS
<link href="/App_Templates/FrontEnd/Login.css?date=2012-07-02" rel="stylesheet" type="text/css" />


Javascript
<script src="/Content/swfobject.js?version=2" type="text/javascript"></script>


It will resolve the issue and your client will not need to clear the cache to get new changes. Querystring in css/javascript call will always load file from the server instead of browser cache.


:)


wow


Happy Coding :)

SEO friendly URL (extensionless URL) not working in IIS 7

Currently i am working on a project in which i need to implement SEO friendly URLs. I done it and works fine in development server but when i tried to upload all stuffs on server and send request for SEO friendly URLs i found it gives me error 404 resource not found.

System configuration is
Windows Server 2008
IIS7
ASP.NET 4.0.30319


Solution is here,

Follow the steps:

1) Go to IIS and site application pool set to classic instead of integrated (Managed pipeline mode:).
2) Go to your site in IIS and open "Handler Mapping" section and add a "Wildcard Script Map..." Request path = * and Executables = 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_isapi.dll"
3) Restart your IIS and run your site.

Happy Coding :)

Saving changes is not permitted in SQL 2008 Management Studio

I am working on one of my project and i need to change the table design for change requirement, i done the changes and tried to save the table but SQL server throws an error message as described below:

"Saving changes is not permitted. The changes you have made require the following table to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."

How to solve this issue?

1. Go to Tools > Options it will open the Options dialog.
2. Select the Designers from left pane
3. Un check the Prevent saving changes that require table re-creation
4. Press OK button.

This will allow you to save the table changes.

Possible causes for this error:

1. You tried to change the data type of any column and the table already have some data exists.
2. You tried to add constraint to any column like Primary key which already have data exists.

thanks and regards.

Visual Studio crashing when opening XAML (WPF) files

Before some months i develop one project in WPF, now i got new requirements from client i start working on that change but what i see when i try to open XAML visual studio crashed. I was shocked what happened because few months ago everything was fine.

I did lots of goggling to solve the issue, believe me i search for 2 days. finally i found a solution here

what you need to do is run the visual studio command prompt and enter "ngen/ delete *" (exclude the quotes). it will remove all assemblies native images. It will remove corrupted assemblies too.

It worked for me.
Thanks to Pale Musings.

How to use Session variable in Asp.net web service

In order to use Session variable in asp.net web service you need to add
EnableSession=true attribute in web method definition.

[WebMethod(EnableSession=true)]
public void HelloUser()
{
return "Hello " + Convert.ToString(Session["LoggedInUser"]);
}

Align a div center of page vertically and horizontally

Today i need to align a div center in page horizontal as well as verticle.


css :
body
{
font-size: 14px;
font-family: verdana, helvetica, sans-serif;
color: #333;
}
p
{
margin: 2em;
}

#centerdiv
{
position: absolute;
top: 50%;
left: 50%;
width: 50em;
height: 26em;
margin-top: -13em; /*set to a negative number 1/2 of your height*/
margin-left: -25em; /*set to a negative number 1/2 of your width*/
border: 1px solid #ccc;
background-color: #f3f3f3;
}


html is :

List of Simple Quality Checks while developing a software or web site

15 Basic QC points to check while developing the software / web application.

In today’s world software development is very rapid process. There are very tight deadlines which need to meet by developers. Usually developers finish the development but after it delivered to QC team developer get a big list of bugs find by qc team. And then developer needs to solve bug list overnight which is very tedious job. Herewith I tried to list out common qc points which developer can check at the time of development which make his/her life easier. Hope it will helpful to developers.

1. Proper tab index of every field in all the pages.
2. There should be * near mandatory field. And a note in bottom of page which mention that * fields are mandatory / compulsory.
3. Set the max length property of every input field in a page so that user can’t enter longer text in field.
4. There should be at least one default button (i.e. Save / Submit etc.)
5. In windows application every entry form should be close when user press escape key.
6. There should be product icon on top left corner or every page in case of windows application.
7. Numeric fields should not accept alpha characters.
8. Use proper validation for email addresses, phone numbers etc.
9. During development please check the junk character entry like ', @, #, $ etc. it should be pass on insert, update, record fetching etc.
10. In case of listing – details page scenario listing page should have features like sorting, paging, search by all required columns, go to page etc.
11. Every master entry should be entered during transaction form (i.e. if your transaction form has combo box which contain master data then there should be a + button near combo box, user should be able to press this button and that button should open particular master page after inserting new master entry it should be selected in given combo box.)
12. Use of try .. catch block. Developer should use try catch block on every event handler where user can interact. Also user should not get unhandled exception, there should be proper message to user in case of any exception and that error should be logged in some text file on user’s machine / server in case of web based application.
13. Use proper validation for avoiding duplicate master entries.
14. Referential integrity should be maintain, means when user tries to delete any master entry system should check for the existence of referential data in transactions and should give proper message to user. Then take action like deactivate the master or remove all referential transactions whatever best suits as per the requirements of software.
15. Every field should have a tooltip which can help user to understand the role of that field in the page.
    

validation of viewstate mac failed in asp.net

Before some days the error "Validation of viewstate MAC failed" became my great enemy.
I done lot's of googling to resolve the problem.
I found some good links in which they show some ways to prevent this error one of them is
http://blogs.msdn.com/b/tom/archive/2008/03/14/validation-of-viewstate-mac-failed-error.aspx
Hard luck with me, i tried tips shown in above link which did not work for me, finally after lots of efforts i found the root cause for that error. My all websites were hosted on shared server and they did not alocated dedicated application pool, so most of web sites were on default application pool and hosting people has done setting which recycles that pool every 5 minutes. so when the page got post back after interval of more than 5 minutes application pool get recycled and server raise above error
Validation of viewstate MAC failed.

So finally we decided to buy a dedicated server for our websites and i set dedicated application pool for each website with proper settings so that it does not recycled every 5 minutes.

Finally i got the solution for above error and now me and our clients are happy.


Resources : Asp.net, c#, mysql, iis6.0