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

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

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

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 !!!

:)