Script to Partition in Existing Table in SQL Server

Partition in Existing Table in SQL Server.

Step1 : Create Multiple File Group 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 Simple Table with out partition.


USE [MULTIFILEGROUP]
GO
--Create Simple Table.
CREATE TABLE [DBO].[Tblpartition_existingtable]
(
[Seq]           [INT] IDENTITY(1, 1) NOT NULL,
[Partitionname] [VARCHAR](20),
[Split_value]   [INT] NOT NULL,
CONSTRAINT [PK_TBLPARTITION_EXISTINGTABLE] PRIMARY KEY CLUSTERED ( [Seq]
ASC, [Split_value] ASC )
)

Step3 : Insert sample data into table with out partition.

--Insert Data into Table.
DECLARE @cnt INT
SET @cnt =0
WHILE ( @Cnt < 10000 )
  BEGIN
      IF ( @cnt < 5000 )
        INSERT INTO Tblpartition_existingtable
        VALUES     ('Primary', @cnt)
      ELSE
        INSERT INTO Tblpartition_existingtable
        VALUES     ('Secondary', @cnt)
      SET @cnt = @cnt + 2
  END

Currently we have created simple table without partition and now the step starts to create partition in existing table.

Step4 : Create partition function.

--Create Partition Function
CREATE PARTITION FUNCTION FN_PARTITION_TBLPARTITION_EXISTINGTABLE (INT) AS RANGE RIGHT FOR
VALUES(5000)

Step5 : Create partition scheme.


--Create Parition Scheme
CREATE PARTITION SCHEME SCH_PARTITION_TBLPARTITION_EXISTINGTABLE AS PARTITION
FN_PARTITION_TBLPARTITION_EXISTINGTABLE TO([PRIMARY], [SECONDARY])

Step6 : Drop primary key if you clustered index based on primary key.


--Drop Primary Key (Here i have created primary key so i have to drop primary key)
ALTER TABLE Tblpartition_existingtable
DROP CONSTRAINT PK_TBLPARTITION_EXISTINGTABLE

Step7 : Drop clustered index if your index is independent of primary key.


--Drop and Recreate Clustered Index (It is optional, you can use if you have created clustered index without primary key)
DROP INDEX PK_TBLPARTITION_EXISTINGTABLE ON TBLPARTITION_EXISTINGTABLE

Step8 : Alter table and apply partition scheme.


--Alter Table to apply Partition Schema
ALTER TABLE Tblpartition_existingtable
ADD PRIMARY KEY (Seq, Split_value) ON SCH_PARTITION_TBLPARTITION_EXISTINGTABLE
(SPLIT_VALUE)

Step9 : Check row count by partion using below query.


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

Partition Table in SQL Server

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

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

VB Script to Delete Old Backup Files

VB Script to Delete Old Backup Files.

I have created VB Script which deletes 7 days old file in Folder and Sub Folder.

iDaysOld = 7
strPath = "C:\BACKUP"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)
Set colSubfolders = objFolder.Subfolders
Set colFiles = objFolder.Files

For Each objFile in colFiles
   If objFile.DateLastModified < (Date() - iDaysOld) Then
       objFile.Delete
   End If
Next

For Each objSubfolder in colSubfolders
   Set colFiles = objSubfolder.Files
   For Each objFile in colFiles
       If objFile.DateLastModified < (Date() - iDaysOld) Then
           objFile.Delete
       End If
   Next
Next

Regards,
Nirav Gajjar

VB Scirpt to get Folder Size

VB Scirpt to get Folder Size.

VB Script to get Folder Property.


dim oFS, oFolder
set oFS = WScript.CreateObject("Scripting.FileSystemObject")
set oFolder = oFS.GetFolder("D:\Nirav's Diary")

ShowFolderDetails oFolder

sub ShowFolderDetails(oF)
dim F
    wscript.echo oF.Name & ":Size=" & oF.Size/1024.00/1024.00 &"MB"
    wscript.echo oF.Name & ":Files=" & oF.Files.Count
    wscript.echo oF.Name & ":Folders=" & oF.Subfolders.count
    wscript.echo oF.Name & ":Modified Date=" & oF.DateLastModified
    wscript.echo oF.Name & ":Modified Date=" & oF.DateCreated
    for each F in oF.Subfolders
        ShowFolderDetails(F)
    next
end sub

Regards,

Nirav Gajjar

VB Script to Reboot Server

VB Script to Reboot Server.

Reboot server and send information mail to user.


SendMail "alerts@extreame-advice.com","nirav@extreame-advice.com", "Server Restarted at" & Now(), "Server Restarted"

Dim objShell
Set objShell = WScript.CreateObject ("WScript.shell")
objShell.run "Shutdown /r /c “00:15” /t 30"
Set objShell = Nothing

Sub SendMail(Sender, Recipient, Subject, Message)
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = Subject
objMessage.From = Sender
objMessage.To = Recipient
objMessage.htmlbody = Message
objMessage.Send
End Sub

Regards,

Nirav Gajjar

VB Script to get list of Network Computers

Vb Script to get list of Network Computers.

Create Table ListOfServers
(
	ServerName Varchar(20),
	DomainName Varchar(50),
	CreatedDate DateTime
)

 

'On error resume next
Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data Source=ExtreamAdvice;Initial Catalog=Nirav's Diary;user id ='sa';password='password'"
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
myConn.Open DB_CONNECT_STRING
Set myCommand.ActiveConnection = myConn

Const ADS_SCOPE_SUBTREE = 2
strDomainDn = "DomainName"

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

Set objCOmmand.ActiveConnection = objConnection
objCommand.CommandText = _
    "Select Name, Location from 'LDAP://" & strDomainDn & "' " _
        & "Where objectClass='computer'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst

Do Until objRecordSet.EOF
    strComputer = objRecordSet.Fields("Name").Value
    myCommand.CommandText = "Insert into ListOfServers (ServerName, DomainName,CreatedDate) values('" & strComputer & "','" & strDomainDn & "',getdate())"
    myCommand.Execute
    objRecordSet.MoveNext
Loop
myConn.Close

WScript.Echo "Done!"

Regards,
Nirav Gajjar

Replication Issue – Duplication of Primary Key

Replication Issue – Duplication of Primary Key.

Replication synchronizes data and article on subscriber same as it is on publisher.

Changing and inserting data directly at subscriber cause to break replication.

Please look the following error.

Error Message

Command attempted:

 

if @@trancount > 0 rollback tran
(Transaction sequence number: 0x00000031000001B1000500000000, Command ID: 1)
Error messages:
Violation of PRIMARY KEY constraint ‘PK__Replicat__3214EC27323EC80A’. Cannot insert duplicate key in object ‘dbo.ReplicationTest’. The duplicate key value is (2). (Source: MSSQLServer, Error number: 2627)
Get help: http://help/2627
Violation of PRIMARY KEY constraint ‘PK__Replicat__3214EC27323EC80A’. Cannot insert duplicate key in object ‘dbo.ReplicationTest’. The duplicate key value is (2). (Source: MSSQLServer, Error number: 2627)
Get help: http://help/2627

Replication1

The problem occurs by inserting duplicate key in dbo.ReplicationTest.

Step1 : Track the problem using T-SQL Query.

Copy transaction sequence number and assign to xact_seqno.

The result will show you publisher, publisher db, source article, destination article.

USE [Distribution]
Go

SELECT
  MSA.*
FROM
  dbo.MSarticles AS MSA
JOIN   MSrepl_commands AS MSRC
  ON MSA.Article_id = MSRC.Article_id
WHERE
  MSRC.xact_seqno = 0x00000031000001B1000500000000

Replication2
Step2 : To get the exact row which try to insert data in subscriber.

You have to use SP_BrowseReplCmds procedure of distribution database  and need to pass transaction sequence number to @xact_seqno_start parameter.

EXEC Distribution.DBO.Sp_browsereplcmds
@xact_seqno_start = ’0x00000031000001B1000500000000′

Replication3

See the column “Command”  it will shows you full query.

Error on procedure SP_MSins_DBOReplicationTest with parameter “2″ and “Test” is failing, it means ID “2″ is already exist on subscriber table.

You have a two options to solve, you can modify the record at subscriber or delete the record from subscriber table.

Regards,

Nirav Gajjar

Add article in Replication without initializing

Add article in Replication without initializing

By this way you are only creating a snapshot only for newly added article and no need to reinitialize  all subscription.

Step1 : Create New Table on Publisher.

USE Adventureworks2012

CREATE TABLE ReplicationTest
  (
     ID   INT PRIMARY KEY,
     Test VARCHAR(20)
  )

Step2 : Add this table in Article.

First check Article exist or not in a replication.


--Check Exisitng Articles.
SELECT
  sub.srvname    AS SubscriptionServer,
  pub.name       AS Publication,
  art.name       AS ArticleName,
  art.dest_table AS DestinationArticle,
  art.dest_owner AS OwnerName
FROM
  sysextendedarticlesview art
INNER JOIN syspublications pub
        ON ( art.pubid = pub.pubid )
INNER JOIN syssubscriptions sub
        ON ( sub.artid = art.artid )
WHERE
  art.name = 'ReplicationTest'

GO

Run this to add “ReplicationTest” table to your existing article.

--Script to add table in replication.
EXEC Sp_addarticle
  @publication = N'AdventureWorks2012_Pub',
  @article = N'ReplicationTest',
  @source_owner = N'DBO',
  @source_object = N'ReplicationTest',
  @type = N'logbased',
  @description = N'',
  @creation_script = N'',
  @pre_creation_cmd = N'drop',
  @schema_option = 0x00000000080350DF,
  @identityrangemanagementoption = N'manual',
  @destination_table = N'ReplicationTest',
  @destination_owner = N'DBO',
  @status = 24,
  @vertical_partition = N'false',
  @ins_cmd = N'CALL [sp_MSins_DBOReplicationTest]',
  @del_cmd = N'CALL [sp_MSdel_DBOReplicationTest]',
  @upd_cmd = N'SCALL [sp_MSupd_DBOReplicationTest]',
  @force_invalidate_snapshot = 1

GO

Now check article added successfully.

--Now Check again for newly added article.
SELECT
  sub.srvname    AS SubscriptionServer,
  pub.name       AS Publication,
  art.name       AS ArticleName,
  art.dest_table AS DestinationArticle,
  art.dest_owner AS OwnerName
FROM
  sysextendedarticlesview art
INNER JOIN syspublications pub
        ON ( art.pubid = pub.pubid )
INNER JOIN syssubscriptions sub
        ON ( sub.artid = art.artid )
WHERE
  art.name = 'ReplicationTest'

GO

Step3 : Refresh Subscription.

USE Adventureworks2012
go

Sp_refreshsubscriptions 'Adventureworks2012_pub' -- Supply Publication Name

Step4 : Go to replication monitor screen –> Select Agent Tab
–>Select Snapshot Agent in Agent Type –>Right click on Agent and Start Again.

Replication1

Step5 : Now Go to replication monitor screen –> Select Agent Subscription watch list
–> Double click on your subscription and snapshot applying only for newly added article.

Replication2

Regards,

Nirav Gajjar

Script to Monitor Mirroring

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