Microsoft.Net Developer

How to move or change data(.mdf) file or log(.ldf) file in SQL Server

Sometime we need to change database data (.mdf) and log(.ldf) file for maintenance purpose or server transfer purpose. There are two ways to do this.

Way 1: Using SQL Script

-- First get physical file path of data (.mdf) and log (.ldf)
-- set active database which you want to get file path, here in my case I am using Demo database

USE Demo
GO

-- This will give physical file path of current database
sp_helpfile
GO

-- Now, Set database to single user mode and detach database with disconnecting active sessions

Use MASTER
GO

-- Set database to single user mode
ALTER DATABASE demo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- Detach the database
sp_detach_db 'Demo'
GO

-- This will detach databse
EXEC SP_DETACH_DB @dbname = 'Demo', @skipchecks = 'true';

-- Now move .mdf or .ldf file on your preferred location
-- Attach database with log file at new location (Here I have moved log file only, if you want to mo

USE master
GO

-- Now Attach the database
sp_attach_DB 'Demo',
'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\Demo.mdf',
'C:\FolderName\SQL\Demo_log.ldf'

Way 2: Using SQL Management Studio user interface

1. Right click on database which you want to move data or log file

2. Select Detach option

3. Select checkbox for "Drop Connections" in opened window and then click OK button

4. Now move .mdf or .ldf file on your preferred location

5. Now you will not see your database in Object explorer

--- Attach database

6. Right click on database and select attach option

7. It will open a popup window

8. Click on "Add..." button in "Databases to attach"

9. It will open another popup to select database name which you want to attach, here now you have to select data (.mdf) file and click OK button

10. Now you will see database file location in first window popup, if it is not correct file location then select correct location

11. Finally click on "OK" button, and your database will be attached in couple of seconds.

Hope, this will help to access your database from new location.

Enjoy !!!

:)

How to exit from single-user mode in SQL Server

SQL Server opens multiple connections to serve data to client for their application.

So, you need to kill all opened connections before changing the access mode.

First, make sure the object explorer is pointed to a system database like master.

Second, execute a sp_who2 and find all the connections to database 'my_db'. Kill all the connections by doing KILL { session id } where session id is the SPID listed by sp_who2.

Third, open a new query window.

Then, below script will help to find all current process of sql server and kill and then it will set the database in MULTI_USER mode.

USE master
GO

DECLARE @kill varchar(max) = '';
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(10), spid) + '; '
FROM master..sysprocesses 
WHERE spid > 50 AND dbid = DB_ID('<Your_DB_Name>')
EXEC(@kill);
GO

SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [<Your_DB_Name>] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [<Your_DB_Name>] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

Hope, this will help to access your database.

Enjoy !!!

:)

Find row count of all tables in SQL

Sometimes as a developer or application owner or management team wants to analyse database records, to easily identify database performance first we need to check that how many no of rows stored in database table. The after we can see other things for database performance like index, joins, outer join, conditions etc....

Below is the query which provides no of rows count of all tables of current database.

SELECT T.name AS [TABLE NAME], 
       I.rows AS [ROWCOUNT] 
FROM   sys.tables AS T 
       INNER JOIN sys.sysindexes AS I 
               ON T.object_id = I.id 
                  AND I.indid < 2 
ORDER  BY I.rows DESC

SELECT T.name      AS [TABLE NAME], 
       I.row_count AS [ROWCOUNT] 
FROM   sys.tables AS T 
       INNER JOIN sys.dm_db_partition_stats AS I 
               ON T.object_id = I.object_id 
                  AND I.index_id < 2 
ORDER  BY I.row_count DESC

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    p.[Rows],
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY 
     p.[Rows] desc

Hope, this will help to review database and then you can take action for performance.

Enjoy !!!

:)

Do Fast Productivity - Visual Studio Shortcuts

To improve your development productivity while you are using visual studio, I believe that we all need to use shortcuts which are already provided by Microsoft Visual Studio inbuilt.

Here, I have listed our some most useful code snippets and shortcuts. I hope, it will very useful to improve your development.

Code Snippets

Code Snippest
ctor	create constructor of a class
prop	create a property in a class
propfull	create a full property in a class
cw	Console.WriteLine();
try	try & catch block
tryf	try and finally block
for	create for loop block
forr	To create for loop block with decrement
foreach	create foreach block
while	To create while loop block
do	To create do while loop block

Shortcuts

Shortcut	For what
Ctrl + KK	Book mark code line - to set or remove
Ctrl + KW	To view all book mark list (Book mark window)
Ctrl + KP	To go to book mark (To jump book mark)
Ctrl + KC	To comment code line or selected lines
Ctrl + KU	To uncomment code line or selected lines
Ctrl + A then Ctrl + KF	Format codes
Ctrl + -(minus)	Navigate backward
Ctrl + Shift + -(minus)	Navigate forward
Ctrl + KT	See method call hierarchy
Ctrl + RR	Rename variable name and all places wherever it is used (Select variable and then press key)
Ctrl + RG	Remove unnecessary using namespaces and sort them
Ctrl + MM	collapse/expand active code block
Ctrl + KS	Select any code snippet
Ctrl + Alt + A	Open command window
Ctrl + Alt + I	Oen Immediate window
Ctrl + Alt + P	Attach build process
Ctrl + Alt + X	Open Toolbox
Ctrl + Alt + L	Open Solution Explorer
Ctrl + Alt + S	Open Server Explorer
Ctrl + \ + S	Open SQL Server Object Explorer
Ctrl + \ + E	Open Error window
Ctrl + Tab	To change active tab window - goto next tab
Ctrl + Shift + Tab	To change active tab window - goto previous tab
Ctrl + F6	To change active tab window and goto next tab
Ctrl + Shift + F6	To change active tab window and goto previous tab
Ctrl + , (comma)	To open a navigate window to goto required file
Alt + Shift + C	Open Class view explorer
Alt + Shift + Enter	Set in full screen window or set in restored window
Ctrl + \ + M	Open Team Explorer
Alt + W + L	Close all open tabs
Ctrl + F4	Close current tab
Alt + F11	Run code analysis
Ctrl + N	Add new file
Ctrl + Shift + A	Add new Item
Alt +Shift + A	Add Existing item
Ctrl + Shift + B	Build solution
Ctrl + Shift + F9	Delete all break points
Shift + F7	Goto design view (for webform)
F7	Goto code view (for webform)
F9	To set/remove break point
F10	Step Over (For debug)
F11	Step Into (For debug)
F12	Goto Defination

Enjoy !!!

:)

MVC - JQuery - Async call to load dashboard data simultaneously

To load dashboard data we should use async method call, so it will not wait for previous method's completion. Means, dashabord data will call simultaneously to load all tiles data.

Below is the example,

I have created only one method "GetOrders" for all tiles data, but you have to write different methods as per your requirement.

HTML

<div class="row">
    <div class="col-lg-3 col-xs-6">
        <!-- small box -->
        <div class="small-box bg-aqua" id="orders">            
            <div class="inner">
                <div id="divLoader" style="display:none;position: absolute"> 
                <img src="~/Content/images/loader.gif" alt="Loader" /></div>
                <h3 id="cOrders">&nbsp;</h3>
                <p>New Orders</p>
            </div>
            <div class="icon">
                <i class="ion ion-bag"></i>
            </div>
            <a href="#" class="small-box-footer">More info <i class="fa fa-arrow-circle-right"></i></a>
        </div>
    </div>
    <!-- ./col -->
    <div class="col-lg-3 col-xs-6">        
        <!-- small box -->
        <div class="small-box bg-green">
            <div class="inner">
                <div id="divBounce" style="display:none;position: absolute"> 
                <img src="~/Content/images/loader.gif" alt="Loader" /></div>
                <h3 id="cBounce">&nbsp;</h3>
                <p>Bounce Rate</p>
            </div>
            <div class="icon">
                <i class="ion ion-stats-bars"></i>
            </div>
            <a href="#" class="small-box-footer">More info <i class="fa fa-arrow-circle-right"></i></a>
        </div>
    </div>
    <!-- ./col -->
    <div class="col-lg-3 col-xs-6">
        <!-- small box -->
        <div class="small-box bg-yellow">
            <div class="inner">
                <div id="divUsers" style="display:none;position: absolute"> 
                <img src="~/Content/images/loader.gif" alt="Loader" /></div>
                <h3 id="cUsers">&nbsp;</h3>
                <p>User Registrations</p>
            </div>
            <div class="icon">
                <i class="ion ion-person-add"></i>
            </div>
            <a href="#" class="small-box-footer">More info <i class="fa fa-arrow-circle-right"></i></a>
        </div>
    </div>
    <!-- ./col -->
    <div class="col-lg-3 col-xs-6">
        <!-- small box -->
        <div class="small-box bg-red">
            <div class="inner">
                <div id="divVisitors" style="display:none;position: absolute"> 
                <img src="~/Content/images/loader.gif" alt="Loader" /></div>
                <h3 id="cVisitors">&nbsp;</h3>
                <p>Unique Visitors</p>
            </div>
            <div class="icon">
                <i class="ion ion-pie-graph"></i>
            </div>
            <a href="#" class="small-box-footer">More info <i class="fa fa-arrow-circle-right"></i></a>
        </div>
    </div>
    <!-- ./col -->
</div>

JQuery

<script>
    $(document).ready(function () {

        orders(Math.floor((Math.random() * 100) + 1));
        Bounce(Math.floor((Math.random() * 100) + 1));        
        Users(Math.floor((Math.random() * 100) + 1));
        Visitors(Math.floor((Math.random() * 100) + 1));

        function orders(wtime) {
            $("#divLoader").show();
            $.ajax({
                type: 'GET',
                url: '@Url.Action("GetOrders", "Dashboard")',
                data: { type: $(this).attr("data-type"), val : wtime },
                success: function (response) {
                    console.log(response);
                    $("#cOrders").append(wtime);
                    $("#divLoader").hide();
                },
                error: function () {
                    $("#divLoader").hide();
                    alert("Something wrong");
                }
            });
        }

        function Bounce(wtime)
        {
            $("#divBounce").show();
            $.ajax({
                type: 'GET',
                url: '@Url.Action("GetOrders", "Dashboard")',
                data: { type: $(this).attr("data-type"), val : wtime },
                success: function (response) {
                    console.log(response);
                    $("#cBounce").append(wtime);
                    $("#divBounce").hide();
                },
                error: function () {
                    $("#divBounce").hide();
                    alert("Something wrong");
                }
            });
        }

        function Visitors(wtime) {
            $("#divVisitors").show();
            $.ajax({
                type: 'GET',
                url: '@Url.Action("GetOrders", "Dashboard")',
                data: { type: $(this).attr("data-type"), val : wtime },
                success: function (response) {
                    console.log(response);
                    $("#cVisitors").append(wtime);
                    $("#divVisitors").hide();
                },
                error: function () {
                    $("#divVisitors").hide();
                    alert("Something wrong");
                }
            });
        }

        function Users(wtime) {
            $("#divUsers").show();
            $.ajax({
                type: 'GET',
                url: '@Url.Action("GetOrders", "Dashboard")',
                data: { type: $(this).attr("data-type"), val : wtime },
                success: function (response) {
                    console.log(response);
                    $("#cUsers").append(wtime);
                    $("#divUsers").hide();
                },
                error: function () {
                    $("#divUsers").hide();
                    alert("Something wrong");
                }
            });
        }  
    });
</script>

MVC Async Method

public async Task<ActionResult> GetOrders(int val)
        {
            await Task.Run(() =>
            {
                System.Threading.Thread.Sleep(val * 100);
                return Json(new { data = 10 }, JsonRequestBehavior.AllowGet);
            });

            return Json(new { data = 0 }, JsonRequestBehavior.AllowGet);
        }

Enjoy !!!

:)

Jenkins : How to change home directory of Jenkins

How to change home directory of Jenkins

Q: Why we need to change home directory of jenkins ?

Ans: To manage the disk space, we need to change jenkins home directory.

First copy folder from C:\Program Files (x86)\Jenkins to another location like E:\JenkinsHome

Stop the running service: jenkins.exe stop

Uninstall the service: jenkins.exe uninstall

Now rename C:\Program Files (x86)\Jenkins to Jenkins_Backup to keep it as backup.

Go to E:\JenkinsHome

Reinstall the service: jenkins.exe install

Start Jenkins: jenkins.exe start

Enjoy with your disk space!

Enjoy !!!

:)

MVC - JQuery - Drag and Drop - Sorting

Below is the code that provides features to drag and drop using jquery sorting features.

Controller Code:

namespace Demo.Web.Controllers
{
    public class UserController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }
 
        public ActionResult GetUsersData()
        {
            var datalist = BLUsers.GetUserList();
 
            return Json(new { data = datalist.DataList.ToList() }, JsonRequestBehavior.AllowGet);
 
        }
 
        [HttpPost]
        public virtual void GetUsersPostData(List<VMUser> savedItems)
        {
            foreach (var item in savedItems)
            {
              // TODO: as per your requirement
            }
 
        }
         
    }
}

View page code:

@{
    ViewBag.Title = "index";
}
 
<h2>Drag and Drop</h2>
 
<script src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.10.0.min.js"></script>
 
<script src="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.10.3/jquery-ui.min.js" type="text/javascript"></script>
 
<link href="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.10.3/themes/blitzer/jquery-ui.css" rel="stylesheet" type="text/css" />  
 
<style>
    #sortable1, #sortable2 {
        border: 1px solid #eee;
        width: 142px;
        min-height: 120px;
        list-style-type: none;
        margin: 0;
        padding: 5px 0 0 0;
        float: left;
        margin-right: 10px;
        background-color: gray;
    }
 
        #sortable1 li, #sortable2 li {
            margin: 0 5px 5px 5px;
            padding: 5px;
            font-size: 1.2em;
            width: 120px;
            cursor: move;
        }
</style>
 
<script>
    $(function () {
 
        $("ul.droptrue").sortable({
            connectWith: "ul"
        });
 
        $("ul.dropfalse").sortable({
            connectWith: "ul"
        });
 
        loadUsers();
 
        ///Function to load products using call to WEB API
        function loadUsers() {
            var items = "";
 
            $.ajax({
                url: "/demo.web/User/GetUsersData",
                type: "GET"
            }).done(function (resp) {
                $.each(resp.data, function (idx, val) {
                    items += "<li class='ui-state-default ItemId' id='" + val.UserId + "'>" + val.Username + "</li>";
                });
                $("#sortable1").html(items);
            }).error(function (err) {
                alert("Error! " + err.status);
            });
        }
 
        $("#btnSubmit").click(
            function () {
                 
                var childCheckBoxes = $("#sortable2 li");
                var userViewPreferenceDetails = new Array();
               
                   var values = "";
                   for (i = 0; i < childCheckBoxes.length; i++) {
                       var userPreferenceItem = new Object();
                       userPreferenceItem.UserId = childCheckBoxes[i].id;
                       userViewPreferenceDetails.push(userPreferenceItem);
                    }
 
                    $.ajax({
                        type: 'POST',
                        url: '/demo.web/User/GetUsersPostData',
                        data: JSON.stringify(userViewPreferenceDetails),
                        dataType: 'json',
                        contentType: 'application/json; charset=utf-8',
                        success: function (data) { 
                            alert(data);                            
                        },
                        error: function (jqXHR, status, err) {
                            alert(err);
                        },
                        complete: function (jqXHR, status) {
                            alert("Local completion callback.");
                        }
 
                    });
 
                                
            });
    });
</script>
 
 
    <ul id="sortable1" class="droptrue"></ul>
    <ul id="sortable2" class="dropfalse"></ul>
 
    <div style="clear:both; padding-top:30px">
        <input type="button" id="btnSubmit" value="Submit Data" />
    </div>

How to move or change data(.mdf) file or log(.ldf) file in SQL Server

Way 1: Using SQL Script

Way 2: Using SQL Management Studio user interface

How to exit from single-user mode in SQL Server

Find row count of all tables in SQL

Do Fast Productivity - Visual Studio Shortcuts

Code Snippets

Shortcuts

MVC - JQuery - Async call to load dashboard data simultaneously

To load dashboard data we should use async method call, so it will not wait for previous method's completion. Means, dashabord data will call simultaneously to load all tiles data.

Below is the example,

I have created only one method "GetOrders" for all tiles data, but you have to write different methods as per your requirement.

JQuery

MVC Async Method

Enjoy !!!

:)

How to change home directory of Jenkins

Q: Why we need to change home directory of jenkins ?

Ans: To manage the disk space, we need to change jenkins home directory.

First copy folder from C:\Program Files (x86)\Jenkins to another location like E:\JenkinsHome

Stop the running service: jenkins.exe stopUninstall the service: jenkins.exe uninstallNow rename C:\Program Files (x86)\Jenkins to Jenkins_Backup to keep it as backup.Go to E:\JenkinsHome

Reinstall the service: jenkins.exe installStart Jenkins: jenkins.exe startEnjoy with your disk space!

Enjoy !!!

:)

MVC - JQuery - Drag and Drop - Sorting

Below is the code that provides features to drag and drop using jquery sorting features.

Controller Code:

View page code:

Output:

Enjoy !!!

:)

Stop the running service: jenkins.exe stop

Uninstall the service: jenkins.exe uninstall

Now rename C:\Program Files (x86)\Jenkins to Jenkins_Backup to keep it as backup.

Go to E:\JenkinsHome

Reinstall the service: jenkins.exe install

Start Jenkins: jenkins.exe start

Enjoy with your disk space!