Tag: SQL Server

SQL Server – File stats using Fn_virtualfilestats

SQL, SQL-Advanced, SQL-Performance, SQL-Tips and Tricks, Varinder Sandhu

Fn_virtualfilestats() is in-build function in the SQL Server and really useful for understanding the amount of I/O within individual files on your SQL Server system. It returns I/O statistics for database files, including log files. Syntax fn_virtualfilestats ( { database_id | NULL } , { file_id | NULL } ) Example 1 File statistical information for a database select * from :: fn_virtualfilestats (1,1)   Example 2 File statistical information for all databases and files select * from :: fn_virtualfilestats

» Read more
,

SQL Server – Capture the Wait Stats to table for analysis

SQL, SQL-Advanced, SQL-Performance, SQL-Tips and Tricks, Varinder Sandhu

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

SQL Server – Enable / Configure Replication Alerts

SQL, SQL-Advanced, SQL-Replication, SQL-Tips and Tricks, Varinder Sandhu

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

SQL Server – User Defined Group

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

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

SQL Server – User Defined Sort Order

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

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

SQL Server – Order By Clause Based on a Variable

Guest Post, SQL, SQL-Advanced, SQL-Query, SQL-Scripts, SQL-Tips and Tricks

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

SQL Server – How to enable output logging for a replication agent

SQL, SQL-Advanced, SQL-Replication, SQL-Tips and Tricks, Varinder Sandhu

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

SQL Server – List of all the Error codes or messages

SQL, SQL-Advanced, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Contains one row for each system error or warning that can be returned by Microsoft SQL Server. SQL Server displays the error description on the user’s screen. You can get the List of all the Error codes or messages as USE master GO SELECT * FROM sysmessages You will get the five columns as Error – Unique error number Severity – Severity level of the error Dlevel – For internal use only Description – Explanation of the error with placeholders

» Read more
, ,

SQL SERVER – The Self Join

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

A self-join is joining a table to itself. This is done by using table name aliases to give each instance of the table a separate name. Self Join is very useful when you want to compare values in a column to other values in the same column. Self join and its example is very common question in the interview. Let us take a look to example

» Read more
, ,

SQL Server – SQL Scripts to find and Kill all the Blocked Process in a database

SQL, SQL-Advanced, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

SQL Server database administrators frequently need in especially development and test environments to find and kill all the blocked process. Following scripts are useful in that scenario. Script – 1 — Find All the Blocked Processes SELECT spid, status, loginame=SUBSTRING(loginame,1,12), hostname=SUBSTRING(hostname,1, 12), blk = CONVERT(char(3), blocked), dbname=SUBSTRING(DB_NAME(dbid),1, 10), cmd, waittype FROM master.dbo.sysprocesses WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)   Script 2 — Kill all the Blocked Processes of a Database DECLARE @DatabaseName nvarchar(50) — Set the Database Name SET

» Read more
, , ,

SQL Server Denali – New Logical Functions

SQL, SQL-Advanced, SQL-General, SQL-Server 2011, SQL-Tips and Tricks, Varinder Sandhu

There are two new Logical functions are introduced in Denali. Let‘s have a look IFF CHOOSE IFF It Returns one of two values, depending on whether the Boolean expression evaluates to true or false. IIF is a shorthand way for writing a CASE statement. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression

» Read more
, , ,
1 … 3 4 5 6 7 … 9