SQL Server – Reset or Clear the Wait Stats counter
We can find the wait stats as posted here We can Reset or clear the wait stats using — Reset or clear the wait stats counter DBCC SQLPERF (waitstats, clear) Hope this will help you.
» Read moreSQL Server – Capture the Wait Stats to table for analysis
We can find the SQL Server wait stats by using DBCC SQLPERF (waitstats) Now we need to capture all these wait stats to table for analysis –Create a temp table for waitstats CREATE TABLE #waitstats ( wait_type VARCHAR(500), Request REAL, wait_time REAL, signal_wait_time REAL ) — Insert the waitstats to table INSERT INTO #waitstats EXEC('DBCC SQLPERF(waitstats)') –Now you can analysis waitstats SELECT * FROM #waitstats Hope this will help you to analyze the wait stats.
» Read moreSQL Server – Display Row number in Query Editor
Display Row Numbers in Query Editor by default doesn’t display the Row numbers of T-SQL. To show Row numbers in Query Editor, Go to Tools Click on Option Go to Text Editor Then go to Transact-SQL Check the Line Number Hope this is useful for you.
» Read moreSQL Server – Enable / Configure Replication Alerts
Replication offers the following predefined alerts, which can be configured to respond to replication events: Replication: agent success Replication: agent failure Replication: agent retry Replication: expired subscription dropped Replication: Subscription reinitialized after validation failure Replication: Subscriber has failed data validation Replication: Subscriber has passed data validation Replication: agent custom shutdown Configure these alerts from the Alerts folder in Microsoft SQL Server Management Studio or the Warnings tab in Replication Monitor Let us take a look how can we enable/Configure Replication Alerts;
» Read moreSQL Server – User Defined Group
I had requirement to make the User Defined Group in Reports (i.e. Crystal Reports) Example: CREATE TABLE test_custom_group ( id INT, dept VARCHAR(20), detail VARCHAR(30), salary NUMERIC(5,2) ) Created a Function CREATE FUNCTION test_fn ( ) RETURNS @temp_table TABLE ( sno int, dept varchar(20), grp varchar(10) ) AS BEGIN INSERT @temp_table(sno,dept,grp ) SELECT 1,'C','A' UNION all SELECT 2,'A','A' UNION all SELECT 3,'D','B' UNION all SELECT 4,'E','B' UNION all SELECT 5,'B','B' RETURN END Created a Store Procedure USE test_db IF EXISTS
» Read moreSQL Server – User Defined Sort Order
I had requirement to get the values with Use Defined Sort order. Here is simple way to get User Defined Sort order Example: Created a table as : CREATE TABLE sort_order ( id INT, dept VARCHAR(20), detail VARCHAR(30) ) Inserted few rows in table as Requirement: Now we want to change the sort order and new sorted output should be: REQUIRED OUTPUT: For this created a function: CREATE FUNCTION test_fn ( ) RETURNS @temp_table TABLE ( sno int, dept varchar(20)
» Read moreTask Manager – MyLife Organized (MLO)
Every day many of us spend a lot of time to organize our Task List so that we can work efficiently. Maybe you are using Calendar, Outlook, Excel Sheet or paper note to manage your Task list or day to day activity. But here I want to share with you lightweight yet powerful software to organizing your life. I have been using this software from the last Six months and really speaking it is very effective for managing task, projects
» Read moreHow to Delete Facebook account permanently
Many People are not aware of deleting the Facebook account permanently, let‘s see how to deactivate and Delete Facebook account permanently. Difference between deactivating the account and deleting the account permanently Deactivating the Facebook account Deactivating is just you will disable your account and others can’t see your profile or information but you can reactivate your account by login Facebook again. Delete Facebook account permanently Deleting the Account is permanently you will delete your profile and information from Facebook. How
» Read moreSQL Server – sp_MSforeachdb – Undocumented Stored Procedure
The undocumented stored procedure “sp_MSforeachdb” is found in the “master” database. This is similar to sp_MSforeachtable. This stored procedure Sp_MSforeachdb gives a DBA the ability to cycle through every database in your catalog. This stored procedure will loop every database in your catalog for performing a command. This stored procedure accepts the following input parameters. Example: You can run a DBCC CHECKDB on all databases as below EXEC sp_Msforeachdb "DBCC checkdb ('?')" Similarly you can run other command also.
» Read moreSQL Server – sp_MSforeachtable – Undocumented Stored Procedure
As database administrators or developers, sometimes we need to perform an action on all of the tables within a database. For example, we may want to disable check constraints for all tables prior to loading a bunch of data. No doubt we can use the dynamic statement or cursor for this purpose. But we have better alternative way; this is an undocumented stored procedure called as “sp_MSforeachtable” in the master database. This stored procedure will loop through all the tables
» Read moreSQL Server – Order By Clause Based on a Variable
This is a guest post from one of my good friend and Developer Ranjoyt Singh. He is a dedicated and committed person professionally and personally. We developers come across many times in a situation where we need to change the sort order in which results of a query are displayed. Sorting based on a variable can be achieved in the application as well as in T-SQL. The SQL Server gives performance benefit over application. So, let’s explore what are the
» Read moreSQL Server – How to enable output logging for a replication agent
You can configure the SQL Server replication agents such as, the Snapshot Agent, Log Reader Agent, Queue Reader Agent, Distribution Agent, and Merge Agent to capture more information about error messages and procedure calls to a text file. For example, you can capture the additional information about error messages and procedure calls to troubleshoot agent failures. The following options are provided for each of the replication agents to enable logging to an output file. If the specified file name exists,
» Read more