Displaying posts categorized under

TSQL

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. It will shrink 90% free space and remains 10% free for database files. It will shrink data and log files to the last allocated extent, while you use [...]

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 [...]

Process Element XML and Attribute XML in single node

Process Element XML and Attribute XML in single node.

Modify database name with single user mode

Modify database name with single user mode

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. [...]

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. Thanks & Regards, Nirav [...]

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 [...]

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. Step2 : Drop article. Step3 : Refresh Subscription. Step4 : Start snapshot sgent in replication to apply changes in snapshot. –> Replication Monitor. –> Expand Publisher and select Subscription. –> Goto [...]

Add Partitioned Table in Replication

Add Partitioned Table in Replication. Step1 : Create Partition Table. Step2 : Script to add Partition Table in existing Replication. Step3 : Refresh Subscription. 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 [...]

Script to Partition in Existing Table in SQL Server

Partition in Existing Table in SQL Server. Step1 : Create Multiple File Group Database. Step2 : Create Simple Table with out partition. Step3 : Insert sample data into table with out partition. Currently we have created simple table without partition and now the step starts to create partition in existing table. Step4 : Create partition function. [...]