Category Archives: TSQL

Partition Table in SQL Server

Posted on by

Partition Table in SQL Server

To create table partition your database must need Multple file group database to apply Table partition.

Step1 : Create Multiple Filegroup Database.

CREATE DATABASE MULTIFILEGROUP
ON PRIMARY
(NAME = N'MFGTest_1', FILENAME = N'C:\SQL Server\MulipleFilegroup\MFGTest_1.mdf', SIZE = 5124KB, FILEGROWTH = 10% ),
FILEGROUP [SECONDARY]
(NAME = N'MFGTest_2', FILENAME = N'C:\SQL Server\MulipleFilegroup\MFGTest_2.ndf', SIZE = 1024KB, FILEGROWTH = 10% )
LOG ON
( NAME = N'MFGTest_Log', FILENAME = N'C:\SQL Server\MulipleFilegroup\MFGTest_Log.ldf', SIZE = 1024KB, FILEGROWTH = 10% )

Step2 : Create Partition Function to define partition block .

here, i have created two partition block

See Values(10) means first partition contains 1-10 and second partition contains > 10.

you can create number of partition block same as number of file group in your database.

you can create multiple block as below.
VALUES(10,20,30,40)– it will create 5 blocks 1-10, 11-20, 21-30, 31-40, >40

--Create Partition Function
CREATE PARTITION FUNCTION FN_PARTITION_TBLPARTITION (INT) AS RANGE RIGHT FOR
VALUES(10)

Step3 : Create partition Schema.

--Create Parition Schema
CREATE PARTITION SCHEME SCH_PARTITION_TBLPARTITION AS PARTITION
FN_PARTITION_TBLPARTITION TO([PRIMARY], [SECONDARY])

Step4 : Create Parition Table.

CREATE TABLE Tblpartition
  (
     Seq         INT IDENTITY(1, 1) NOT NULL,
     Fisrt_name  VARCHAR(20),
     Last_name   VARCHAR(20),
     Split_value INT NOT NULL
  )
ON SCH_PARTITION_TBLPARTITION(SPLIT_VALUE)

ALTER TABLE Tblpartition
  ADD CONSTRAINT PK_TBLPARTITION PRIMARY KEY (Seq, Split_value)

Step5 : Insert sample data in partition table.

INSERT INTO Tblpartition
SELECT 'Nirav', 'Gajjar', 1
UNION ALL
SELECT 'Smith', 'Mack', 2
UNION ALL
SELECT 'Jack', 'Samson', 3
UNION ALL
SELECT 'Smith', 'Raka', 10
UNION ALL
SELECT 'Hen', 'Vaith', 11

Step6 : Script to get Paritition Table Information.

SELECT
T.Name             AS Table_name,
Ps.Name            AS Partition_scheme,
Pf.Name            AS Partition_function,
CASE
WHEN Pf.Boundary_value_on_right = 1 THEN 'RIGHT'
ELSE 'LEFT'
END                AS Range_type,
P.Partition_number AS Partition_number,
P.Rows             AS Numberofrows
FROM
SYS.Tables T
JOIN   SYS.Indexes I
ON T.Object_id = I.Object_id
JOIN   SYS.Partition_schemes Ps
ON I.Data_space_id = Ps.Data_space_id
JOIN   SYS.Partition_functions Pf
ON Ps.Function_id = Pf.Function_id
JOIN   SYS.Partitions P
ON I.Object_id = P.Object_id
AND I.Index_id = P.Index_id
WHERE
I.Index_id < 2 --Filter for Clustered Index
ORDER  BY
P.Partition_number

Regards,
Nirav Gajjar

Script to get Table Partition information

Posted on by

Script to get Table Partition information.

This script gives you Table Partition information with Partition Number, Scheme Name and Function Name.

SELECT T.Name  AS Table_Name,
       Ps.Name AS Partition_Scheme,
       Pf.Name AS Partition_Function,
       CASE
         WHEN Pf.Boundary_value_on_right = 1 THEN 'RIGHT'
         ELSE 'LEFT'
       END  AS Range_type,
       P.Partition_number AS Partition_Number,
       P.Rows AS NumberOfRows
FROM   SYS.Tables T
       JOIN SYS.Indexes I
         ON T.Object_id = I.Object_id
       JOIN SYS.Partition_schemes Ps
         ON I.Data_space_id = Ps.Data_space_id
       JOIN SYS.Partition_functions Pf
         ON Ps.Function_id = Pf.Function_id
       JOIN SYS.Partitions P
         ON I.Object_id = P.Object_id
            AND I.Index_id = P.Index_id
WHERE  I.Index_id < 2 --Filter for Clustered Index
ORDER  BY P.Partition_number

TablePartition

Regards,

Nirav Gajjar

Script to Monitor Mirroring

Posted on by

Script to Monitor Mirroring

This script will provide all database which is mirrored with mirroring mode, mirroring status and partner information.


SELECT Sd.Name              AS Databasename,
       Mirroring_state_desc AS Status,
       CASE
         WHEN Mirroring_safety_level = 1 THEN 'High Performance'
         WHEN Mirroring_safety_level = 2 THEN 'High Safety'
         ELSE NULL
       END                  AS Mirroringmod,
       Mirroring_role_desc,
       Mirroring_partner_instance
FROM   MSDB.SYS.Database_mirroring Sdm
       JOIN MASTER.SYS.Sysdatabases Sd
         ON Sdm.Database_id = Sd.Dbid
WHERE  Mirroring_guid IS NOT NULL

Regards,
Nirav Gajjar

Script to get Replication Latency

Posted on by

Script to get Replication Latency.

I have created monitoring script to get latency information for replication and status of replication health.

I have used “SP_REPLMONITORHELPSUBSCRIPTION ” under Distribution database.

It gives you same information which you can get from Replication Monitor –> Subscription Wath List.

In SQL Server 2012 you just need to pass value only for @Publication_type.

If you are using older version of SQL Server then you need to pass @Publication_type and @Publisher parameter, so I have created script for both different versions.

DECLARE @Srvname VARCHAR(100)
DECLARE @Pub_db VARCHAR(100)
DECLARE @Pubname VARCHAR(100)

CREATE TABLE #REPLMONITOR
 (
 Status INT NULL,
 Warning INT NULL,
 Subscriber SYSNAME NULL,
 Subscriber_db SYSNAME NULL,
 Publisher_db SYSNAME NULL,
 Publication SYSNAME NULL,
 Publication_type INT NULL,
 Subtype INT NULL,
 Latency INT NULL,
 Latencythreshold INT NULL,
 Agentnotrunning INT NULL,
 Agentnotrunningthreshold INT NULL,
 Timetoexpiration INT NULL,
 Expirationthreshold INT NULL,
 Last_distsync DATETIME,
 Distribution_agentname SYSNAME NULL,
 Mergeagentname SYSNAME NULL,
 Mergesubscriptionfriendlyname SYSNAME NULL,
 Mergeagentlocation SYSNAME NULL,
 Mergeconnectiontype INT NULL,
 Mergeperformance INT NULL,
 Mergerunspeed FLOAT,
 Mergerunduration INT NULL,
 Monitorranking INT NULL,
 Distributionagentjobid BINARY(16),
 Mergeagentjobid BINARY(16),
 Distributionagentid INT NULL,
 Distributionagentprofileid INT NULL,
 Mergeagentid INT NULL,
 Mergeagentprofileid INT NULL,
 Logreaderagentname VARCHAR(100),
 Publisher VARCHAR(20)
 )

INSERT INTO #REPLMONITOR
SELECT A.*
FROM OPENROWSET('MSDASQL',
 'DRIVER={SQL Server}; SERVER=InstanceName; UID=sa; PWD=Password',
 'SET FMTONLY OFF EXEC DISTRIBUTION.DBO.SP_REPLMONITORHELPSUBSCRIPTION @Publication_type = 0') AS A;

SELECT CASE Status
 WHEN 1 THEN 'Started'
 WHEN 2 THEN 'Succeeded'
 WHEN 3 THEN 'In Profress'
 WHEN 4 THEN 'Idle'
 WHEN 5 THEN 'Retrying'
 WHEN 6 THEN 'Failed'
 END AS Status,
 Publication,
 Publisher_db Subscriber_db,
 Subscriber_db,
 CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, Latency
 ), 6),
 5, 0, ':'), 3, 0,
 ':'), 108) AS Latency,
 CASE Monitorranking
 WHEN 60 THEN 'Error'
 WHEN 56 THEN 'Warning: performance critical'
 WHEN 52 THEN 'Warning: expiring soon or expired'
 WHEN 50 THEN 'Warning: subscription uninitialized'
 WHEN 40 THEN 'Retrying failed command'
 WHEN 30 THEN 'Not running (success)'
 WHEN 20 THEN 'Running (starting, running, or idle)'
 END AS Healthcheck
FROM #REPLMONITOR

DROP TABLE #REPLMONITOR

Query For SQL Server 2005 and 2008.

DECLARE @Srvname VARCHAR(100)
DECLARE @Pub_db VARCHAR(100)
DECLARE @Pubname VARCHAR(100)

CREATE TABLE #REPLMONITOR
  (
     Status                        INT NULL,
     Warning                       INT NULL,
     Subscriber                    SYSNAME NULL,
     Subscriber_db                 SYSNAME NULL,
     Publisher_db                  SYSNAME NULL,
     Publication                   SYSNAME NULL,
     Publication_type              INT NULL,
     Subtype                       INT NULL,
     Latency                       INT NULL,
     Latencythreshold              INT NULL,
     Agentnotrunning               INT NULL,
     Agentnotrunningthreshold      INT NULL,
     Timetoexpiration              INT NULL,
     Expirationthreshold           INT NULL,
     Last_distsync                 DATETIME,
     Distribution_agentname        SYSNAME NULL,
     Mergeagentname                SYSNAME NULL,
     Mergesubscriptionfriendlyname SYSNAME NULL,
     Mergeagentlocation            SYSNAME NULL,
     Mergeconnectiontype           INT NULL,
     Mergeperformance              INT NULL,
     Mergerunspeed                 FLOAT,
     Mergerunduration              INT NULL,
     Monitorranking                INT NULL,
     Distributionagentjobid        BINARY(16),
     Mergeagentjobid               BINARY(16),
     Distributionagentid           INT NULL,
     Distributionagentprofileid    INT NULL,
     Mergeagentid                  INT NULL,
     Mergeagentprofileid           INT NULL,
     Logreaderagentname            VARCHAR(100)
  )

INSERT INTO #REPLMONITOR
SELECT A.*
FROM   OPENROWSET('MSDASQL',
       'DRIVER={SQL Server}; SERVER=InstanceName; UID=sa; PWD=Password',
' set fmtonly off EXEC distribution.dbo.sp_replmonitorhelpsubscription @publication_type = 0, @Publisher = ''ServerName'''
) AS A;

SELECT CASE Status
         WHEN 1 THEN 'Started'
         WHEN 2 THEN 'Succeeded'
         WHEN 3 THEN 'In Profress'
         WHEN 4 THEN 'Idle'
         WHEN 5 THEN 'Retrying'
         WHEN 6 THEN 'Failed'
       END                                                AS Status,
       Publication,
       Publisher_db                                       Subscriber_db,
       Subscriber_db,
       CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, Latency
                                             ), 6),
                                 5, 0, ':'), 3, 0,
                                               ':'), 108) AS Latency,
       CASE Monitorranking
         WHEN 60 THEN 'Error'
         WHEN 56 THEN 'Warning: performance critical'
         WHEN 52 THEN 'Warning: expiring soon or expired'
         WHEN 50 THEN 'Warning: subscription uninitialized'
         WHEN 40 THEN 'Retrying failed command'
         WHEN 30 THEN 'Not running (success)'
         WHEN 20 THEN 'Running (starting, running, or idle)'
       END                                                AS Healthcheck
FROM   #REPLMONITOR

DROP TABLE #REPLMONITOR

Regards,
Nirav Gajjar

Script to get replication Undistributed Commands

Posted on by

Script to get replication Undistributed Commands

Script to Monitor Replication by checking last refresh time and pending commands

SELECT ( CASE
           WHEN Mdh.Runstatus = '1' THEN 'Start'
           WHEN Mdh.Runstatus = '2' THEN 'Succeed'
           WHEN Mdh.Runstatus = '3' THEN 'InProgress'
           WHEN Mdh.Runstatus = '4' THEN 'Idle'
           WHEN Mdh.Runstatus = '5' THEN 'Retry'
           WHEN Mdh.Runstatus = '6' THEN 'Fail'
         END )                                 [Run status],
       Mda.Subscriber_db                       [Subscriber db],
       Mda.Publication                         [Pub name],
       Mdh.[Time]                              [Lastsynchronized],
       Und.Undelivcmdsindistdb                 [Undistcom],
       Mdh.Comments                            [Comments],
       DATEDIFF(SECOND, Mdh.[Time], GETDATE()) Lastrefresh
FROM   DISTRIBUTION.DBO.Msdistribution_agents Mda
       LEFT JOIN DISTRIBUTION.DBO.Msdistribution_history Mdh
              ON Mdh.Agent_id = Mda.Id
       JOIN (SELECT S.AGENT_ID,
                    Maxagentvalue.[Time],
                    SUM(CASE
                          WHEN Xact_seqno > Maxagentvalue.Maxseq THEN 1
                          ELSE 0
                        END) AS Undelivcmdsindistdb
             FROM   DISTRIBUTION.DBO.Msrepl_commands T (NOLOCK)
                    JOIN DISTRIBUTION.DBO.Mssubscriptions AS S (NOLOCK)
                      ON ( T.ARTICLE_ID = S.ARTICLE_ID
                           AND T.PUBLISHER_DATABASE_ID = S.PUBLISHER_DATABASE_ID
                         )
                    JOIN (SELECT Hist.AGENT_ID,
                                 MAX(Hist.[TIME]) AS [Time],
                                 H.Maxseq
                          FROM   DISTRIBUTION.DBO.Msdistribution_history Hist (
                                 NOLOCK)
                                 JOIN (SELECT Agent_id,
                                              ISNULL(MAX(Xact_seqno), 0x0) AS
                                              Maxseq
                                       FROM
                                 DISTRIBUTION.DBO.Msdistribution_history (
                                 NOLOCK)
                                       GROUP  BY Agent_id) AS H
                                   ON ( Hist.AGENT_ID = H.Agent_id
                                        AND H.Maxseq = Hist.XACT_SEQNO )
                          GROUP  BY Hist.AGENT_ID,
                                    H.Maxseq) AS Maxagentvalue
                      ON Maxagentvalue.Agent_id = S.AGENT_ID
             GROUP  BY S.AGENT_ID,
                       Maxagentvalue.[Time]) Und
         ON Mda.Id = Und.Agent_id
            AND Und.[Time] = Mdh.[Time]
ORDER  BY Mdh.[Time] DESC


Regards,
Nirav Gajjar

Query to get Database size and table size

Posted on by

Query to get Database size and table size.

Query to get Database size in GB.


SELECT
 D.NAME,
 Cast((SUM(MF.SIZE) * 8.00 /1024.00/1024.00)as Numeric(18,2)) AS [SIZE (In GB)]
FROM SYS.MASTER_FILES MF
INNER JOIN SYS.DATABASES D
 ON D.DATABASE_ID = MF.DATABASE_ID
 WHERE D.DATABASE_ID > 4
 GROUP BY D.NAME
 ORDER BY [SIZE (In GB)] DESC

Query to get Table size in GB with no of rows.


SELECT SCHEMA_NAME(T.Schema_id) + '.' + T.Name
 AS
 Tablename,
 SUM(
		CASE
			WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
			ELSE 0
		END
	   )
 AS Rowcounts,
 CAST(( SUM(A.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(
 18, 3)) AS
 Totalspacegb
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
 INNER JOIN SYS.Filegroups Fg
 ON I.Data_space_id = Fg.Data_space_id
 INNER JOIN SYS.Database_files Df
 ON Df.Data_space_id = Fg.Data_space_id
WHERE T.Is_ms_shipped = 0
 AND I.Object_id > 255
GROUP BY SCHEMA_NAME(T.Schema_id) + '.' + T.Name
ORDER BY Totalspacegb DESC

Query to get Service Broker Queue size in GB with no of rows.

Select OBJECT_NAME(IT.parent_object_id),SUM(
 CASE
 WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
 ELSE 0
 END
 )
 AS Rowcounts,CAST(( SUM(A.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC( 18, 3)) AS Totalspacegb
From sys.partitions P
Inner Join sys.internal_tables IT On IT.OBJECT_ID = P.OBJECT_ID
INNER JOIN SYS.Allocation_units A
ON P.PARTITION_ID = A.Container_id
Where P.index_id In (1, 0)
Group By OBJECT_NAME(IT.parent_object_id)
Order by 3 DESC

Regards,
Nirav Gajjar

Query to get Open Transaction

Posted on by

Query to get Open Transaction.

Query to monitor open transactions in your sql server instance.

If you are using SQL Server 2012 use open_transaction_count column instead of enlist_count.

Query to get open transactions by host. remove the comment if you want open transactions just from ssms window.

SELECT
SUM(Tst.Enlist_count) Noofopentran,
Host_name
FROM   SYS.Dm_tran_session_transactions Tst
INNER JOIN SYS.Dm_exec_sessions Es
ON Tst.Session_id = Es.Session_id
--WHERE program_name = 'Microsoft SQL Server Management Studio - Query'
GROUP  BY Host_name

List Open transactions in details.


SELECT
Tst.Session_id,
Tst.Enlist_count,
Host_name,
Program_name,
Text
FROM   SYS.Dm_tran_session_transactions Tst
INNER JOIN SYS.Dm_exec_connections Ec
ON Tst.Session_id = Ec.Session_id
INNER JOIN SYS.Dm_exec_sessions Es
ON Tst.Session_id = Es.Session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(Ec.Most_recent_sql_handle)
--where program_name = 'Microsoft SQL Server Management Studio - Query'

Regards,
Nirav Gajjar

SQL Server long running and blocking queries

Posted on by

SQL Server long running and blocking queries

This query helps you to get all running queries including user queries and SQL Server engine queries.

with duration, so you can examine which query running long.

You can see BlockingWith field which shows you query is blocking with another process.

I have ordered query by blocking first so you can get all blocking query at first and you can take necessary actions.

SELECT
DATEDIFF(MI, Start_time, GETDATE()) AS Mins,
'USE ' + DB_NAME(Database_id) + '; SELECT object_name(' + CAST(Objectid AS VARCHAR) + ')' AS Objectname,
SUBSTRING(St.Text, ( Statement_start_offset / 2 ) + 1, ( ( CASE Statement_end_offset WHEN -1 THEN DATALENGTH(St.Text) ELSE Statement_end_offset END -   Statement_start_offset ) / 2 ) + 1) AS Statement_text,
DB_NAME(Database_id) AS Dbname,
Session_id,
Blocking_session_id AS Blockingwith,
Status,
Command
FROM SYS.Dm_exec_requests R
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(Sql_handle) AS St
ORDER BY Blockingwith DESC, Mins DESC

This query show you only user queries. any queries running by SQL Server machine will be filtered.

Here are few more things to observe.

CPU field which shows the CPU usage, Reads, Writes, Host_Name which shows query running from which machine, Program_Name Which shows from which program the query executed.

SELECT
R.Cpu_time AS Cpu,
DATEDIFF(MINUTE, Last_request_start_time, GETDATE()) AS Mins,
S.Host_name AS Host,
LEFT(Client_interface_name, 15) AS Client,
S.Session_id AS Sessionid,
Blocking_session_id AS Blocking,
'USE ' + DB_NAME(Database_id) + '; SELECT object_name(' + CAST(Objectid AS VARCHAR) + ')' AS Objectname,
SUBSTRING(St.Text, ( Statement_start_offset / 2 ) + 1, ABS(( ( CASE Statement_end_offset WHEN -1 THEN DATALENGTH(St.Text) ELSE Statement_end_offset END - Statement_start_offset ) / 2 ) + 1)) AS Statement_text,
DB_NAME(Database_id) AS Dbname,
Blocking_session_id AS Blockingwith,
R.Reads,
R.Writes,
S.Session_id,
S.[Program_name],
S.Login_name,
S.Status,
S.Last_request_start_time,
R.Logical_reads
FROM SYS.Dm_exec_requests R
INNER JOIN SYS.Dm_exec_sessions S ON S.Session_id = R.Session_id AND S.Is_user_process = 1
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(Sql_handle) AS St
ORDER BY Blocking DESC, Mins DESC

Regards,
Nirav Gajjar

XML Data in where clause

Posted on by

XML Data in where clause
XML Data in Document Types.
Step1 : Create Table XMLData.

	Use Adventureworks2012
	Go
	Create Table XMLData
	(
		ID Int Identity(1,1),
		XMLResult XML,
		CreateDate DateTime
	)

Step2 : Insert XML Document Types Data.

	Insert Into XMLData Values('<Databases> <Name>master</Name> <recovery_model>3</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2003-04-08T09:13:36.390</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>tempdb</Name> <recovery_model>3</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-27T16:13:43.170</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>model</Name> <recovery_model>1</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2003-04-08T09:13:36.390</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>msdb</Name> <recovery_model>3</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2012-02-10T21:02:17.770</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>ReportServer$SQL</Name> <recovery_model>1</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-16T20:33:13.300</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>ReportServer$SQLTempDB</Name> <recovery_model>3</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-16T20:33:13.583</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>temp</Name> <recovery_model>1</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-22T21:16:22.997</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>Adventureworks2012</Name> <recovery_model>1</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-27T22:12:48.527</create_date> </Databases>',getdate())

Step3 : Where clause for XML Data Type.

	Select * from XMLData
	 Where XMLResult.value('(/Databases/Name)[1]','varchar(20)') = 'master'

XML Data in Element Types.
Step1 : Truncate XML Data table and insert some element type XML data in the same table.

	Truncate Table XMLData
	Insert Into XMLData Values('<root><Databases name="master" recovery_model="3" state_desc="ONLINE" create_date="2003-04-08T09:13:36.390" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="tempdb" recovery_model="3" state_desc="ONLINE" create_date="2013-05-29T17:09:36.863" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="model" recovery_model="1" state_desc="ONLINE" create_date="2003-04-08T09:13:36.390" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="msdb" recovery_model="3" state_desc="ONLINE" create_date="2012-02-10T21:02:17.770" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="ReportServer$SQL" recovery_model="1" state_desc="ONLINE" create_date="2013-05-16T20:33:13.300" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="ReportServer$SQLTempDB" recovery_model="3" state_desc="ONLINE" create_date="2013-05-16T20:33:13.583" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="temp" recovery_model="1" state_desc="ONLINE" create_date="2013-05-22T21:16:22.997" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="Adventureworks2012" recovery_model="1" state_desc="ONLINE" create_date="2013-05-27T22:12:48.527" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="SSISDB" recovery_model="1" state_desc="ONLINE" create_date="2013-05-29T17:07:05.090" /></root>',getdate())

Step2 : Where clause for XML Data Type.

	Select * from XMLData Where XMLResult.exist('(/root/Databases[@name="master"])') = 1
	Select * from XMLData Where XMLResult.value('(/root/Databases/@name)[1]','varchar(20)') = 'master'

Regards,
Nirav Gajjar

Change file location of Master Database

Posted on by

Change file location of Master Database.

To change file location of other database is very easy and it can be done in three steps.
Step1 Detach that database.
Step2 Cut from Old location and paste mdf and ldf files to new location.
Step3 Attach database with new location.

To change file location of master database is more tricky task, because you must need to restart the sql server service and need parameter changes in SQL Server Service property.

Please follow the steps to change database file location of master database.
Step1 : Check for the current path of master database files to copy from that path.

Select * FROM SYS.sysaltfiles where dbid = DB_ID('master')

Step2 : Script to change file location.

USE master;
GO
ALTER DATABASE master
MODIFY FILE (NAME = master, FILENAME = 'D:\Data\master.mdf');
GO
ALTER DATABASE master
MODIFY FILE (NAME = mastlog, FILENAME = 'D:\Log\mastlog.ldf');
GO

Step3 : Change SQL Server service parameter.
–> Goto SQL Server Configuration Manager –> Select you SQL instance(By Default MSSQLSERVER).
–> Right click on service and goto property.
–> Goto startup parameter tab and select “-dC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf”
–> change it by “-dD:\DATA\master.mdf” and click on update.
–> Now Select “-lC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf”
–> change it by “-lD:\Log\mastlog.ldf”
ChangeMasterFile1

Step4 : Stop your sql server instance copy mdf and ldf file from base location to new location and restart the service.

Step5 : If you get error like “The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.”.

it means you don’t have rights on new locations.
ChangeMasterFile2

To solve this give a full permission to that folder.

–> Go to D:\Data –> Right click on Data Folder and go to Security tab.

–>Click on ADD button and add everyone user rights.

ChangeMasterFile3

 

 

Regards,

Nirav Gajjar