Displaying the most recent of 72 posts written by

nirav

Script to get Replication Latency

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

Script to get replication Undistributed Commands

Script to get replication Undistributed Commands Script to Monitor Replication by checking last refresh time and pending commands Regards, Nirav Gajjar

Query to get Database size and table size

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 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

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

Deploy SSIS Package in SQL Server 2012

Deploy SSIS Package in SQL Server 2012. SQL Server 2012 added new feature of Integration Service Catalog, which makes SSIS package deployment task very easy. To get advantage of this feature you must have sql server integration service installed on your server. Follow the steps to deploy package. Step1 : Create Integration Service catalog. Step2 [...]

XML Data in where clause

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

SQL Server connection in WMI Script

SQL Server connection in WMI Script. VB Script provides composition of WMI and SQL Server. You can connect SQL Server and store WMI informations in SQL Server. Before reading this blog you already know that WMI is also works like central communication machine for all network servers. To know more read http://nirav.extreme-advice.com/2013/05/15/get-disk-space-usage-from-central-server-with-wmi/. I have created one [...]

Monitor performance counter with WMI and SQL Server

Monitor performance counter with WMI and SQL Server. WMI is very important to use as central monitoring task. I have created performance monitoring which checks Disk Queue Legth, Disk Read/Sec, Disk Write/Sec, CPU Usage, Memory Usage, Disk Read Bytes/Sec, Disk Write Bytes/Sec, ASP Request/Sec. Create a table “PerformanceCounter” to store all information. Open Notepad and save [...]

Change file location of Master Database

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