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. 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 [...]
Script to get Table Partition information. This script gives you Table Partition information with Partition Number, Scheme Name and Function Name. Regards, Nirav Gajjar
Script to Monitor Mirroring This script will provide all database which is mirrored with mirroring mode, mirroring status and partner information. Regards, Nirav Gajjar
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 [...]
Query to get Database size and table size. Query to get Database size in GB. Query to get Table size in GB with no of rows. Query to get Service Broker Queue size in GB with no of rows. Regards, Nirav Gajjar
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. List Open transactions in details. Regards, Nirav Gajjar
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 [...]
XML Data in where clause XML Data in Document Types. Step1 : Create Table XMLData. Step2 : Insert XML Document Types Data. Step3 : Where clause for XML Data Type. XML Data in Element Types. Step1 : Truncate XML Data table and insert some element type XML data in the same table. Step2 : Where [...]
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 [...]