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

Grant permission on Mail Profile in SQL Server

Grant permission on Mail Profile in SQL Server.

In SQL Server you can grant send mail permission by granting “DatabaseMailUserRole” roles to particular user under MSDB database. But how to give permission to specific profile, follow the steps below.

Step1 : Right click on “Database Mail” and select “Configure Database Mail”.
Step2 : On a welcome wizard click on “Next”.
Step3 : On the “Select Configuration Task” select “Manage Profile Security” and click on “Next”.
Step4 : On the “Manage Profile Security” select your user that you want to grant permission. Here i have selected “standard” user.
Note : You must have a user created on your MSDB database.
Step5 : Check permission status on “Complete the Wizard” window and click on “Finish”.
Step6 : On the “Configuring” window click on “Close” to complete the process.

Thanks & Regards,

Nirav Gajjar

Increase Size of File Attachment in SQL Server

Increase Size of File Attachment in SQL Server.

Microsoft SQL Server provides attachment when you sent an email from your application.
By default maximum size for attachment is 1000000 bytes. Its less then 1 MB, we can increase this size by following the below steps.

Step1 : Right click on “DatabaseMail” and select “Configure Database Mail”.

Increase Attachmet 1
Step2 : On “Select Configuration Task” window select “View or Change system parameter” and click on “Next”.

Increase Attachment 2
Step3 : You can see “Maximum File Size (Bytes)” Parameter, By default it is 1000000 bytes and i have increased by 10000000 bytes and click on “Next”.

Increase Attachmet 3
Step4 : Check changed parameter value on the “complete the wizard” and click on “Finish”.

Increase Attachmet 4
Step5 : Check configuration status on “Configuring” window and “Close” the window.

Increase Attachmet 5
Now you can able to send attachment up to 10 MB of size.

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

Partition Existing Replicated Table

Partition Existing Replicated Table.

To create partition in existing replicated table you must have to remove article from replication. The reason is, to create partiiton in existing table you must have to recreate primary key and you can’t recreate primary key replicated table.

so, remove table from replication is first step.

Note : Before you remove article from replicatin please check file group on both database at publisher and subscrber.

You must have multiple filegroup on replicated database and both database must have same filegroup.

1) You must remove existing table from replication.
http://nirav.extreme-advice.com/2013/07/11/remove-article-from-replication/

2) Create Parition on existing table.
http://nirav.extreme-advice.com/2013/07/09/partition-in-existing-table-in-sql-server/

3) Add partitioned table to replication.

http://nirav.extreme-advice.com/2013/07/10/add-partitioned-table-in-replication/

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

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