Month: May 2013

Schedule DBCC CHECKDB in SQL Server

1. In Microsoft SQL Server Management Studio, right-click SQL Server Agent, then click New, then click Job

2. Input Name, “JOB_DBCC_CHECKDB”

3. Click Steps, then click New

4. Input Step Name, “DBCC CHECKDB”

5. Paste the following Command

DECLARE @pDbName varchar(1000)

DECLARE c CURSOR FOR SELECT name FROM [sys].[databases] WHERE name <> 'tempdb'

OPEN c

FETCH NEXT FROM c INTO @pDbName

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('DBCC CHECKDB(' + @pDbName + ');')

    FETCH NEXT FROM c INTO @pDbName
END

CLOSE c

DEALLOCATE c

6. Click OK

7. Click Schedules, then click New

8. Input Name, “Weekly Sunday 00:00”

9. Setup the appropriate schedule

10. Click OK

11. Click OK again

Reorganize / Rebuild Indexes in SQL Server

1. Check the fragmentation of an index

SELECT O.name, I.name, S.avg_fragmentation_in_percent
FROM [sys].[dm_db_index_physical_stats](DB_ID('DB_NAME'), NULL, NULL, NULL, NULL) AS S INNER JOIN 
    [sys].[indexes] AS I ON S.object_id = I.object_id AND S.index_id = I.index_id INNER JOIN
    [sys].[objects] AS O ON S.object_id = O.object_id AND I.object_id = O.object_id
WHERE S.avg_fragmentation_in_percent > 5 
ORDER BY S.avg_fragmentation_in_percent DESC;
avg_fragmentation_in_percent value Corrective statement
> 5% and <= 30% ALTER INDEX REORGANIZE
> 30% ALTER INDEX REBUILD

2. Reorganize Index & Update Statistics

Reorganize index will not automatically update the statistics.

ALTER INDEX {INDEX_NAME} ON {OBJECT_NAME} REORGANIZE;
UPDATE STATISTICS {OBJECT_NAME} {INDEX_NAME};

3. Rebuild Index

Rebuild index will automatically update the statistics.

ALTER INDEX {INDEX_NAME} ON {OBJECT_NAME};

4. Everything in one shot

DECLARE @pSqlText varchar(1000)

DECLARE c CURSOR FOR SELECT SqlGenerator.SqlText FROM 
    (SELECT CASE WHEN S.avg_fragmentation_in_percent > 5 AND S.avg_fragmentation_in_percent <= 30 THEN 'ALTER INDEX ' + I.name + ' ON ' + O.name + ' REORGANIZE; UPDATE STATISTICS ' + O.name + ' ' + I.name + ';'
        WHEN S.avg_fragmentation_in_percent > 30 THEN 'ALTER INDEX ' + I.name + ' ON ' + O.name + ' REBUILD;' ELSE '' END AS SqlText
    FROM [sys].[dm_db_index_physical_stats](DB_ID('DB_NAME'), NULL, NULL, NULL, NULL) AS S INNER JOIN 
        [sys].[indexes] AS I ON S.object_id = I.object_id AND S.index_id = I.index_id INNER JOIN
        [sys].[objects] AS O ON S.object_id = O.object_id AND I.object_id = O.object_id
    WHERE I.name IS NOT NULL) SqlGenerator
    WHERE SqlGenerator.SqlText <> ''

OPEN c

FETCH NEXT FROM c INTO @pSqlText

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC (@pSqlText)

    FETCH NEXT FROM c INTO @pSqlText
END

CLOSE c

DEALLOCATE c

References

CascadingDropDown in ASP.NET MVC

1. Download CascadingDropDown jQuery Plugin

2. Put file jquery.cascadingDropDown.js in ~\Scripts

3. Add the following line of code in ~\App_Start\BundleConfig.cs

    bundles.Add(new ScriptBundle("~/bundles/jqueryplugin").Include(
        "~/Scripts/jquery.cascadingDropDown.js"));

4. Create a View Model for the Cascading DropDown Select Lists and the selected values

public class CascadingDropDownViewModel
{
    public SelectList AreaSelectList { get; set; }

    public SelectList DistrictSelectList { get; set; }

    [Display(Name = "Area")]
    public Area Area { get; set; }

    [Display(Name = "District")]
    public District District { get; set; }
}

5. Create a Controller for populating the View Model

public ActionResult CascadingDropDown()
{
    CascadingDropDownViewModel model = new CascadingDropDownViewModel();
    model.AreaSelectList = new SelectList(_entities.Areas, "AreaId", "Name");
    model.DistrictSelectList = new SelectList(Enumerable.Empty(), "DistrictId", "Name");

    return View(model);
}

6. Create a View to show the Cascading DropDown

@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()
    @Html.LabelFor(m => m.Area) :
    @Html.DropDownListFor(m => m.Area, Model.AreaSelectList, "-- Please Select --")
    @Html.ValidationMessageFor(m => m.Area)


    @Html.LabelFor(m => m.District) :
    @Html.DropDownListFor(m => m.District, Model.DistrictSelectList)
    @Html.ValidationMessageFor(m => m.District)


    <button type="submit">Submit</button>
}

@section scripts{
<script type="text/javascript">// <![CDATA[
        $(document).ready(function () {
            $("#District").CascadingDropDown("#Area", "/Ajax/DistrictCascadingDropDown",
            {
                promptText: "-- Please Select --",
                postData: function () {
                    return { areaId: $("#Area").val() };
                }
            });
        });

// ]]></script>
}

7. Create a dedicated Controller AjaxController and an Action method DistrictCascadingDropDown

public ActionResult DistrictCascadingDropDown(int areaId)
{
	var query = from d in _entities.Districts
				where d.AreaId == areaId
				select d;

	SelectList selectList = new SelectList(query, "DistrictId", "Name");

	return Json(selectList);
}

References

DropDownList in ASP.NET MVC

View Model

public class DropDownListViewModel
{
    public SelectList DistrictSelectList { get; set; }

    [Display(Name = "District")]
    public District District { get; set; }
}

Controller

public ActionResult DropDownList()
{
    DropDownListViewModel model = new DropDownListViewModel();
    model.DistrictSelectList = new SelectList(_entities.Districts, "DistrictId", "Name");

    return View(model);
}

View

@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()
    @Html.LabelFor(m => m.District) <text>:</text>
    @Html.DropDownListFor(m => m.District, Model.DistrictSelectList, "-- Please Select --")
    @Html.ValidationMessageFor(m => m.District)
    <button type="submit">Submit</button>
}

References