Script to Shrink Database

Script to Shrink Database.

You need to use Database Console Commands of SQL Server to get back the free space.

DBCC ShrinkDatabase : Shrink all files from database.


Use Master
Go

DBCC ShrinkDatabase(AdventureWorks2012, 10)
Go

It will shrink 90% free space and remains 10% free for database files.


Use Master
Go

DBCC ShrinkDatabase(AdventureWorks2012, TruncateOnly)
Go

It will shrink data and log files to the last allocated extent, while you use TruncateOnly the percentage parameter will be ignored.

DBCC ShrinkFile : Shrink only single file.

You have a choice to which data file or log file you want to shrink.

–> “AdventureWorks_MDF” and “AdventureWorks_LDF” is a logical name of data files and log files.


Use AdventureWorks2012
Go

DBCC ShrinkFile(AdventureWorks_MDF,10)
Go


Use AdventureWorks2012
Go

DBCC ShrinkFile(AdventureWorks_LDF,10)
Go

Regards,
Nirav Gajjar

Identity reseed in SQL Server 2012

Identity reseed in SQL Server 2012

In SQL Server 2012, there is one issue regarding Identity column.

I have faced identity value is jumping by 1000 when i have restarted my SQL Server Service.

You can find gaps in identity while you restart your sql server service, restart of server or failover.

Please review below code to try this.

USE ADVENTUREWORKS2012
GO
CREATE TABLE TestIdentJump
    (
         Id   INT IDENTITY (1, 1),
         Name VARCHAR(20)
    )
INSERT INTO TestIdentJump VALUES     ('testing1')
GO 5

SELECT *
FROM   TestIdentJump

IdentJump

Now restart your sql server service and run below code again and see the result.

INSERT INTO TestIdentJump VALUES     ('AfterRestart')
SELECT *
FROM   TestIdentJump

IdentJump1

Have you seen SQL server has started Identity column from 1002 instead of 6.

To resolve the problem there is two options

1) Run the checkpoint before you restart the sql server and check again.

This technique will solve the problem of jump in identity.

This technique has limitations, this will helps you out only in manual failover.

2) We need to open one trace flag to avoid this issue when server crisis and we don’t have a chance to run checkpoint.

Need to Open trace flag -t272

Right click on sql server service and click on property.

Select “Startup Parameters” tab.

Specify -t272 in textbox an click on add button and restart the service.

IdentJump3

Note : You need to this activity on principle server as well as mirror server.

Thanks & Regards,

Nirav Gajjar

Process Element XML and Attribute XML in single node

Process Element XML and Attribute XML in single node.


DECLARE @T VARCHAR(400)
SET @T = '<Log DeviceId="MainDevice001">
  <Info UId="100000" Date="05/05/2014">
    <Err>Error1</Err>
  </Info>
  <Info UId="100001" Date="05/05/2014">
    <Err>Error2</Err>
  </Info>
</Log>'
DECLARE @Errorlogsxml AS XML
SET @Errorlogsxml = Cast(@T AS XML)
SELECT TAB1.COL1.value('@UId', 'VARCHAR(100)') AS Uid,
       X.Deviceid AS Deviceid,
       TAB1.COL1.value('@Date', 'DATETIME') AS Date,
       TAB1.COL1.value('(Err)[1]', 'VARCHAR(100)') AS Error
FROM   (SELECT TAB.COL.value('@DeviceId', 'VARCHAR(100)') AS Deviceid
        FROM   @Errorlogsxml.nodes('/Log') AS Tab(COL)) X
       CROSS APPLY @Errorlogsxml.nodes('/Log/Info') AS Tab1(COL1)

Purge records of Sys.Transmission_Queue of Service Broker in SQL Server

Purge records of Sys.Transmission_Queue of Service Broker in SQL Server.

Today i got one issue on service broker for sys.transmission_queue.
Service broker configured well and working fine, suddenly service broker tranmission_queue goes high and high.
I have checked Transfer_Status to check the error if there is any, but i can’t see any error in Transfer_Status.

So, the question is what to do if SYS.Transmission_Queue stucks.
I had searched on web and read many bolg’s and forums but can’t find proper way to subsidize the transmission queue.

Finally, i have started to send the message manually from sys.transmission_queue and clean the conversation.

The steps that i followed is as below.

Step1 : Insert all Sys.Transmission_Queue data into temp table.

SELECT *
INTO MY_TRANSMISSION_QUEUE
FROM SYS.TRANSMISSION_QUEUE

Step2 : Now purge records of Sys.Transmission_Queue.

BEGIN
BEGIN TRY
DECLARE @Convhandle UNIQUEIDENTIFIER

DECLARE CONV CURSOR FOR

SELECT S2TRANSQUEUE.CONVERSATION_HANDLE
FROM SYS.CONVERSATION_ENDPOINTS AS S2CONVEND WITH(NOLOCK)
 INNER JOIN SYS.TRANSMISSION_QUEUE S2transqueue WITH(NOLOCK)
 ON S2TRANSQUEUE.CONVERSATION_HANDLE = S2CONVEND.CONVERSATION_HANDLE

OPEN CONV
FETCH NEXT FROM CONV INTO @Convhandle

WHILE @@FETCH_STATUS = 0
 BEGIN
 END CONVERSATION @Convhandle WITH CLEANUP
 FETCH NEXT FROM CONV INTO @Convhandle
 PRINT @Convhandle
 END

CLOSE CONV
DEALLOCATE CONV
END TRY

BEGIN CATCH
PRINT Error_message()
END CATCH

Step3 : Alter table and add one new column TransferStatus to check manually transfer status.


ALTER TABLE MY_TRANSMISSION_QUEUE
ADD TRANSFERSTATUS BIT

UPDATE MY_TRANSMISSION_QUEUE
SET TRANSFERSTATUS = 0

Step4 : Now send data manually from temp table and change the TransferStatus column.

DECLARE @Cnt INT
SELECT @Cnt = Count(*)
FROM My_transmission_queue(NOLOCK)
WHERE TRANSFERSTATUS = 0

WHILE( @Cnt > 0 )
 BEGIN
 DECLARE @Con_hand UNIQUEIDENTIFIER
 DECLARE @Xml XML
 DECLARE @Seq INT
 SELECT TOP 1 @Con_hand = CONVERSATION_HANDLE,
 @Xml = Cast(MESSAGE_BODY AS XML),
 @Seq = MESSAGE_SEQUENCE_NUMBER
 FROM My_transmission_queue(NOLOCK)
 WHERE TRANSFERSTATUS = 0
 ---Process Queue
 DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
 DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION

BEGIN DIALOG @InitDlgHandle
 FROM SERVICE [SB://Service/Audit/Initiator]
 TO SERVICE N'SB://Service/Audit/Target'
 ON CONTRACT [SB://Contract/Audit]
 WITH ENCRYPTION = ON;

 SEND ON CONVERSATION @InitDlgHandle
 MESSAGE TYPE [SB://Message/Audit/Request]
 (@Xml);

COMMIT TRANSACTION
 --End of Process Queue.

UPDATE MY_TRANSMISSION_QUEUE
 SET TRANSFERSTATUS = 1
 WHERE TRANSFERSTATUS = 0
 AND CONVERSATION_HANDLE = @Con_hand
 AND MESSAGE_SEQUENCE_NUMBER = @Seq
 SET @Cnt = @Cnt - 1
 END

Note: This script is useful if you have only one type of service. If you have more than one service in sys.transmission_queue you have to change the code as your requirement.

 
Thanks & Regards,
Nirav Gajjar

Auto Fix Orphaned Users

Auto Fix Orphaned Users.

When you move your database to any higher version, Logins and Users are not sync.

This type of problem comes while the migraion, SQL calls that all users as Orphaned Users and we have to link it manually.

we have the script bellow to link it manually.

USE ADVENTUREWORKS2012
GO
DECLARE @Users TABLE
    (
         Id    INT IDENTITY(1, 1),
         Users VARCHAR(250)
    )
DECLARE @Rows     INT,
        @Username VARCHAR(250)

INSERT INTO @Users

SELECT DISTINCT [Name]
FROM   [Sysusers]
WHERE  Islogin = 1
       AND [Name] NOT IN ( 'guest', 'sa', 'dbo', 'public',
                           'sys', 'INFORMATION_SCHEMA' )
SET @Rows = @@ROWCOUNT
WHILE ( @Rows > 0 )
  BEGIN
      SELECT @Username = Users
      FROM   @Users
      WHERE  Id = @Rows

      EXEC Sp_change_users_login 'Auto_Fix', @Username, NULL, @Username
	  SELECT @Username
	  SET @Rows = @Rows - 1
  END

Thanks & Regards,
Nirav Gajjar

Script to Remove Sysmail_mailitems History

Script to Remove Sysmail_mailitems History.

I am getting a space issue with my system data file drive, meanwhile I have checked the size of database and I found MSDB database contains 32 GB of data. I am shocked by this information and I have eagerly checked the size of particular table and I found  “dbo.sysmail_mailitems” table contains 31 GB of data and “dbo.sysmail_attachments” contains 1 GB of data.

I had ran the query on MSDB Database to get table size.


USE MSDB;
GO

SELECT
  Schema_name(T.Schema_id) + '.' + T.Name                                  AS Tablename,
  Sum(P.Rows)                                                              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
GROUP      BY
  Schema_name(T.Schema_id) + '.' + T.Name
ORDER      BY
  Totalspacegb DESC

See the Result
Remove Mail Log
I have created a script to remove last 30 days mail log.
First i have tried to remove all logs older than 30 days but the situation is i have a mail log of last 6 months and deleting all records at a time also creates a space issue while deleting.
So, i have tried to delete last 30 days data and than shrink the file and than remove the last 30 days data again and this way i have completed disk space issue and purge mail history.

Use MSDB
Go
DECLARE @Deletelastmonth DATETIME

-- Remove History of last 30 Days
SELECT
@Deletelastmonth = Min(Sent_date)
FROM
DBO.Sysmail_mailitems
SET @Deletelastmonth = @Deletelastmonth + 30

--Sysmail_attachments and Sysmail_send_retries table has a foreign key from Sysmail_mailitems so need to remove data from both the tables first.

--Need to remove attachements otherwise you can't purge mailitem history.
DELETE FROM DBO.Sysmail_attachments
WHERE  Last_mod_date <= @Deletelastmonth

--Need to remove entries from retries table too.
DELETE FROM DBO.Sysmail_send_retries
WHERE  Last_send_attempt_date <= @Deletelastmonth

EXEC Sysmail_delete_mailitems_sp
@Sent_before = @Deletelastmonth

EXEC Sysmail_delete_log_sp
@Logged_before = @Deletelastmonth

Regards,
Nirav Gajjar

Remove Article From Replication

Remove Article From Replication

In transnational, replication you have to remove article from Subscription list and drop an Article.

Step1 : Remove article from subscription.

USE [DatabaseName]
EXEC Sp_dropsubscription
  @Publication = N'PublicationName',
  @Subscriber = N'SuscriberServerName',
  @Article = N'ArticleName'
GO

Step2 : Drop article.

EXEC Sp_droparticle
  @Publication = N'PublicationName',
  @Article = N'ArticleName',
  @Force_Invalidate_Snapshot = 1;
GO
--Message "Invalidated the existing snapshot of the publication. Run the Snapshot Agent again to generate a new snapshot."

Step3 : Refresh Subscription.

Sp_refreshsubscriptions 'MultipleFileGroup_Pub'

Step4 : Start snapshot sgent in replication to apply changes in snapshot.
–> Replication Monitor.
–> Expand Publisher and select Subscription.
–> Goto Agent Tab.
–> Right click on Snaphot and click on Start.

Regards,

Nirav Gajjar

Add Partitioned Table in Replication

Add Partitioned Table in Replication.

Step1 : Create Partition Table.

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

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

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)

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

Step2 : Script to add Partition Table in existing Replication.

--Script to add table in replication.
EXEC Sp_addarticle
  @publication = N'PublicationName',
  @article = N'TBLPartition',
  @source_owner = N'DBO',
  @source_object = N'TBLPartition',
  @type = N'logbased',
  @description = N'',
  @creation_script = N'',
  @pre_creation_cmd = N'drop',
  @schema_option = 0x00000000081B50DB, --Partitioning Options, This will enables Partition Function and Partition Scheme to replicate.
  @identityrangemanagementoption = N'manual',
  @destination_table = N'TBLPartition',
  @destination_owner = N'DBO',
  @status = 24,
  @vertical_partition = N'false',
  @ins_cmd = N'CALL [sp_MSins_DBOTBLPartition]',
  @del_cmd = N'CALL [sp_MSdel_DBOTBLPartition]',
  @upd_cmd = N'SCALL [sp_MSupd_DBOTBLPartition]',
  @force_invalidate_snapshot = 1
GO

Step3 : Refresh Subscription.

exec sp_refreshsubscriptions @publication = 'PublicationName'

Step4 : Start snapshot sgent in replication to apply changes in snapshot.
–> Replication Monitor.
–> Expand Publisher and select Subscription.
–> Goto Agent Tab.
–> Right click on Snaphot and click on Start.

Regards,

Nirav Gajjar

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