Query To Find Out Full Details of Databases

SELECT database_id,CONVERT(VARCHAR(25), DB.name) AS dbName,CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],state_desc,
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],user_access_desc AS [User access],recovery_model_desc AS [Recovery model],CASE compatibility_levelWHEN 60 THEN '60 (SQL Server 6.0)'WHEN 65 THEN '65 (SQL Server 6.5)'WHEN 70 THEN '70 (SQL Server 7.0)'WHEN 80 THEN '80 (SQL Server 2000)'WHEN 90 THEN '90 (SQL Server 2005)'WHEN 100 THEN '100 (SQL Server 2008)'END AS [compatibility level],CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],-- last backupISNULL((SELECT TOP 1CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +' (' + CAST(DATEDIFF(second, BK.backup_start_date,BK.backup_finish_date) AS VARCHAR(4)) + ' '+ 'seconds)'FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup],CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext],CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],page_verify_option_desc AS [page verify option],CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only],CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [cleanly shutdown]FROM sys.databases DBORDER BY dbName, [Last backup] DESC, NAME

Script To Identify Blocking Query

SELECTdb.name DBName,tl.request_session_id,wt.blocking_session_id,OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,tl.resource_type,h1.TEXT AS RequestingText,h2.TEXT AS BlockingTest,tl.request_modeFROM sys.dm_tran_locks AS tlINNER JOIN sys.databases db ON db.database_id = tl.resource_database_idINNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_addressINNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_idINNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_idINNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_idCROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

Query to Keep CPU Busy for 60 Seconds

DECLARE @T DATETIME, @F BIGINT;SET @T = GETDATE();WHILE DATEADD(SECOND,60,@T)>GETDATE()SET @F=POWER(2,30);

Finding Memory Pressure – External and Internal

The following query will provide details of external and internal memory pressure. It will return the data how much portion is assigned to what kind of memory type.


SELECT TYPE, SUM(single_pages_kb) InternalPressure, SUM(multi_pages_kb) ExtermalPressureFROM sys.dm_os_memory_clerksGROUP BY TYPE
ORDER BY SUM(single_pages_kb) DESC, SUM(multi_pages_kb) DESCGO

Find Most Expensive Queries Using DMV

SELECTTOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs
.execution_count,
qs
.total_logical_reads, qs.last_logical_reads,
qs
.total_logical_writes, qs.last_logical_writes,
qs
.total_worker_time,
qs
.last_worker_time,
qs
.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs
.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs
.last_execution_time,
qp
.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

Find Number of Tables in a Perticular Database

USE Master
SELECT COUNT(*) from information_schema.tables
 
WHERE table_type = 'base table'
 

Find and Fix Fragmentation of a Table

Find Fragmentation
=====================
select TableName=object_name(dm.object_id)
      ,IndexName=i.name
      ,IndexType=dm.index_type_desc
      ,[%Fragmented]=avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id(),null,null,null,'sampled') dm
join sys.indexes i on dm.object_id=i.object_id and dm.index_id=i.index_id
order by avg_fragmentation_in_percent desc

Remove Fragmentation
=====================
dbcc indexdefrag('master','spt_values','ix2_spt_values_nu_nc')

Quorum configuration choices

Node Majority (recommended for clusters with an odd number of nodes)

Can sustain failures of half the nodes (rounding up) minus one. For example, a seven node cluster can sustain three node failures.

Node and Disk Majority (recommended for clusters with an even number of nodes)

Can sustain failures of half the nodes (rounding up) if the disk witness remains online. For example, a six node cluster in which the disk witness is online could sustain three node failures.

Can sustain failures of half the nodes (rounding up) minus one if the disk witness goes offline or fails. For example, a six node cluster with a failed disk witness could sustain two (3-1=2) node failures.

Node and File Share Majority (for clusters with special configurations)

Works in a similar way to Node and Disk Majority, but instead of a disk witness, this cluster uses a file share witness.

Note that if you use Node and File Share Majority, at least one of the available cluster nodes must contain a current copy of the cluster configuration before you can start the cluster. Otherwise, you must force the starting of the cluster through a particular node.

No Majority: Disk Only (not recommended)

Can sustain failures of all nodes except one (if the disk is online). However, this configuration is not recommended because the disk might be a single point of failure.

 

Quorum Mode	Components that has vote	Votes for Quorum (v denotes vote)
Node Majority	Nodes (1 per node)	v/2 + 1
Node and Disk Majority	Nodes (1 per node) and Disk Witness Resource (1)	v/2 + 1
Node and File Share Majority	Nodes (1 per node) and File Share Witness Resource (1)	v/2 + 1
No Majority: Disk Only (Legacy)	Disk Witness Resource (1)	v

Hardware RAID Levels

RAID	Minimum	Description	Strengths	Weaknesses
Level	Number
	of Drives
RAID 0	2	Data striping without redundancy	Highest performance	No data protection; One drive fails, all data is lost
RAID 1	2	Disk mirroring	Very high performance; Very high data protection; Very minimal penalty on write performance	High redundancy cost overhead; Because all data is duplicated, twice the storage capacity is required
RAID 2	Not used in LAN	No practical use	Previously used for RAM error environments correction (known as Hamming Code ) and in disk drives before the use of embedded error correction	No practical use; Same performance can be achieved by RAID 3 at lower cost
RAID 3	3	Byte-level data striping with dedicated parity drive	Excellent performance for large, sequential data requests	Not well-suited for transaction-oriented network applications; Single parity drive does not support multiple, simultaneous read and write requests
RAID 4	3 (Not widely used)	Block-level data striping with dedicated parity drive	Data striping supports multiple simultaneous read requests	Write requests suffer from same single parity-drive bottleneck as RAID 3; RAID 5 offers equal data protection and better performance at same cost
RAID 5	3	Block-level data striping with distributed parity	Best cost/performance for transaction-oriented networks; Very high performance, very high data protection; Supports multiple simultaneous reads and writes; Can also be optimized for large, sequential requests	Write performance is slower than RAID 0 or RAID 1
RAID 0/1	4	Combination of RAID 0 (data striping) and RAID 1 (mirroring)	Highest performance, highest data protection (can tolerate multiple drive failures)	High redundancy cost overhead; Because all data is duplicated, twice the storage capacity is required; Requires minimum of four drives
RAID 1/0	4	Combination of RAID 1 (mirroring) and RAID 0 (data striping)	Shares the same fault tolerance as RAID 1 (the basic mirror), but compliments said fault tolerance with a striping mechanism that can yield very high read rates	High redundancy cost overhead; Because all data is duplicated, twice the storage capacity is required; Requires minimum of four drives

 

SQL Server Partial Backups

OverviewA new option is "Partial" backups which was introduced with SQL Server 2005. This allows you to backup the PRIMARY filegroup, all Read-Write filegroups and any optionally specified files. This is a good option if you have Read-Only filegroups in the database and do not want to backup the entire database all of the time.
ExplanationA Partial backup can be issued for either a Full or Differential backup. This can not be used for Transaction Log backups. If a filegroup is changed from Read-Only to Read-Write it will be included in the next Partial backup, but if you change a filegroup from Read-Write to Read-Only you should create a filegroup backup, since this filegroup will not be included in the next Partial backup.
A partial backup can be completed only by using T-SQL. The following examples show you how to create a partial backup.

---

Create a partial backup of the TestBackup database
For this example I created a new database called TestBackup that has three data files and one log file. Two data files are the PRIMARY filegroup and one file is in the ReadOnly filegroup. The code below shows how to do a partial backup.

T-SQL
Create a Differential Partial Backup

BACKUP DATABASE Test READ_WRITE_FILEGROUPS
TO DISK = 'C:\Test_Partial.BAK'
GO
Create a Differential Partial Backup

BACKUP DATABASE Test READ_WRITE_FILEGROUPS
TO DISK = 'C:\Test_Partial.DIF'
WITH DIFFERENTIAL
GO

QUERY TO FIND ALL THE PROCESSES RUNNING ON PARTICULAR DATABASE

SELECT [Database]=DB_NAME(dbid), spid, last_batch,status, hostname, loginame

FROM sys.sysprocesses

WHERE dbid=DB_ID('SYSTEMTEST'); ----change the databse name here

How many SQL Server Instances are installed on a server?

-- Create Temporary table to store the data

Create Table #SQLInstances
( Value nvarchar(100),
InstanceName nvarchar(100),
Data nvarchar(100))

-- Read Data from Registery

Insert into #SQLInstances
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances'

Select InstanceName from #SQLInstances

-- Clear the temp table
drop table #SQLInstances

Script to Find SQL Server Cluster Shared Drives

-- SQL Script to Find SQL Server Cluster Shared Drives, Using Function

SELECT * FROM fn_servershareddrives()

-- SQL Script to Find SQL Server Cluster Shared Drives, Using DMV

SELECT * FROM sys.dm_io_cluster_shared_drives

Script to Find SQL Server Error Log location

 

SELECT SERVERPROPERTY('ErrorLogFileName')

SQL Script to check blocking and blocked processes

SELECT  x.session_id,
        x.host_name,
        x.login_name,
        x.start_time,
        x.totalReads,
        x.totalWrites,
        x.totalCPU,
        x.writes_in_tempdb,
    (
            -- Query gets XML text for the sql query for the session_id
            SELECT      text AS [text()]
            FROM  sys.dm_exec_sql_text(x.sql_handle)
            FOR XML PATH(''), TYPE
     
    )AS sql_text,
     COALESCE(x.blocking_session_id, 0) AS blocking_session_id,
    (
        SELECT p.text
        FROM
        (
            -- Query gets the corresponding sql_handle info to find the XML text in the next query
            SELECT MIN(sql_handle) AS sql_handle
            FROM sys.dm_exec_requests r2
            WHERE r2.session_id = x.blocking_session_id
        ) AS r_blocking
        CROSS APPLY
        (
            -- Query will pull back the XML text for a blocking session if there is any from the sql_haldle
            SELECT text AS [text()]
            FROM sys.dm_exec_sql_text(r_blocking.sql_handle)
            FOR XML PATH(''), TYPE
        ) p (text)
    ) AS blocking_text
FROM
(
-- Query returns active session_id and metadata about the session for resource, blocking, and sql_handle
    SELECT  r.session_id,
            s.host_name,
            s.login_name,
            r.start_time,
            r.sql_handle,
            r.blocking_session_id,
            SUM(r.reads) AS totalReads,
            SUM(r.writes) AS totalWrites,
            SUM(r.cpu_time) AS totalCPU,
            SUM(tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb
    FROM    sys.dm_exec_requests r
    JOIN    sys.dm_exec_sessions s ON s.session_id = r.session_id
    JOIN    sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id
    WHERE   r.status IN ('running', 'runnable', 'suspended')
      and r.blocking_session_id <> 0
    GROUP BY    r.session_id,
                s.host_name,
                s.login_name,
                r.start_time,
                r.sql_handle,
                r.blocking_session_id
) x

Internals of TempDB

Tempdb is a critical resource in SQL Server.

It is used internally by the database engine for many operations, and it might consume a lot of disk space. In the past two weeks I encountered 3 different scenarios in which tempdb has grown very large, so I decided to write about troubleshooting such scenarios.
Before I describe the methods for troubleshooting tempdb space usage, let’s begin with an overview of the types of objects that consume space in tempdb. There are 3 types of objects stored in tempdb:

• User Objects
• Internal Objects
• Version Stores

A user object can be a temporary table, a table variable or a table returned by a table-valued function. It can also be a regular table created in the tempdb database. A common misconception is that table variables (@) do not consume space in tempdb, as opposed to temporary tables (#), because they are only stored in memory. This is not true. But there are two important differences between temporary tables and table variables, when it comes to space usage:

1. Indexes and statistics on temporary tables also consume space in tempdb, while indexes and statistics on table variables don’t. This is simply because you cannot create indexes or statistics on table variables.
2. The scope of a temporary table is the session in which it has been created, while the scope of a table variable is the batch in which it has been created. This means that a temporary table consumes space in tempdb as long as the session is still open (or until the table is explicitly dropped), while a table variable’s space in tempdb is deallocated as soon as the batch is ended.

Internal objects are created and managed by SQL Server internally. Their data or metadata cannot be accessed. Here are some examples of internal objects in tempdb:

• Query Intermediate Results for Hash Operations
• Sort Intermediate Results
• Contents of LOB Data Types
• Query Result of a Static Cursor

Unlike user objects, operations on internal objects in tempdb are not logged, since they do not need to be rolled back. But internal objects do consume space in tempdb. Each internal object occupies at least 9
pages (one IAM page and 8 data pages). tempdb can grow substantially due to internal objects when queries that process large amounts of data are executed on the instance, depending on the nature of the queries.

Version stores are used for storing row versions generated by transactions in any database on the instance. The row versions are required by features such as snapshot isolation, after triggers and online index build. Only when row versioning is required, the row versions will be stored in tempdb.
As long as there are row versions to be stored, a new version store is created in tempdb approximately every minute. These version stores are similar to internal objects in many ways. Their data and metadata cannot be accessed, and operations on them are not logged. The difference is, of-course, the data that is stored in them.

When a transaction that needs to store row versions begins, it stores its row versions in the current version store (the one that has been created in the last minute). This transaction will continue to store row versions in the same version store as long as it’s running, even if it will run for 10 minutes. So the size of each version store is determined by the number and duration of transactions that began in the relevant minute, and also by the amount of data modified by those transactions.

Version stores that are not needed anymore are deallocated periodically by a background process. This process deallocates complete version stores, not individual row versions. So, in some cases, it might take a while till some version store is deallocated.
There are two types of version stores. One type is used to store row versions for tables that undergo online index build operations. The second type is used for all other scenarios.

Since the release of SQL Server 2005 there are 3 dynamic management views, which make the task of troubleshooting tempdb space usage quite easy. The views are:

• sys.dm_db_file_space_usage
• sys.dm_db_session_space_usage
• sys.dm_db_task_space_usage

All 3 views return a column named “database_id”, so you might think that they return information for all the databases in the instance, right? Wrong! These views return information for the tempdb database only, so the value returned in this column is always “2” (the database ID of tempdb).

The first view (sys.dm_db_file_space_usage) returns space usage information for each data file in tempdb. It gives a high level distribution of the space occupied by tempdb.