1. Find out when was the server rebooted :
========================================
go to -> run -> cmd -> type NET STATS SRV
or
NET STATISTICS SERVERAME
2. Backup script :
===============
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO
(or)
SELECT database_name, backup_start_date, type, description,*
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'D'
ORDER BY backup_set_id DESC
3. finding errorlog path script :
==============================
SELECT SERVERPROPERTY('ErrorLogFileName')
or
sp_readerrorlog,xp_readerrorlog
4. Blocking script:
================
1.sp_who2
(or)
2.select * from sysprocesses where blocked<>0 and blocked<>spid
(or)
3.
sp_who2
dbcc inputbuffer(59)
select * from sys.dm_exec_requests where session_id =59 -- to find out how much % backup completed.
select * from sys.dm_exec_sql_text(0x01000100B59D281B9052BC090A0000000000000000000000) -- to find which query is runnning.
Now execute the query below using sys.dm_exec_query_stats DMV & sys.dm_exec_sql_text(sql_handle) DMF to get the status of what SQL engine is doing behind:
4.Find Resource Currently Involved in Blocking
-------------------------------------------
SELECT DISTINCT
objname = object_name(p.object_id)
FROM sys.partitions p
JOIN sys.dm_tran_locks t1
ON p.hobt_id = t1.resource_associated_entity_id
5.The following script displays the lead blocker in a chain of blocks:
-------------------------------------------------------------------------
SELECT
spid
,sp.status
,loginame = SUBSTRING(loginame, 1, 12)
,hostname = SUBSTRING(hostname, 1, 12)
,blk = CONVERT(char(3), blocked)
,open_tran
,dbname = SUBSTRING(DB_NAME(sp.dbid),1,10)
,cmd
,waittype
,waittime
,last_batch
,SQLStatement =
SUBSTRING
(
qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
)
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
5. Adding datafile :
=================
USE [master]
GO
ALTER DATABASE [CapitalOneCustom] ADD FILE ( NAME = N'CapitaloneCustom_data1', FILENAME = N'F:\Data\Data0\CapitaloneCustom_data1.ndf' , SIZE = 1048576KB , MAXSIZE = 10485760KB , FILEGROWTH = 102400KB ) TO FILEGROUP [PRIMARY]
GO
6. Finding path of database and logfile location and size :
======================================================
SELECT filegroup_name(groupid),Name, Filename,
CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)],
CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)],
CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)]
,a.maxsize
FROM dbo.sysfiles a (NOLOCK)
7. database recovery model and status :
==================================
Select DBID As [DBID], Name As [DBName],
databasepropertyex(name, 'Recovery') as RecoveryModel,
databasepropertyex(name, 'Status') as Status,
databasepropertyex(name, 'UserAccess') as UserAccess
from master.dbo.sysdatabases Order By DBID
8. find out backups when & who taken with eid info status from msdb :
================================================================
select user_name,BACKUP_sTART_DATE,backup_finish_date,database_name ,* from msdb.dbo.backupset
where type='D' and BACKUP_sTART_DATE > getdate()-1
9. To check the Mirroring Status on the server :
===========================================
Select * from sys.database_mirroring where mirroring_state Is not NULL
10. Drop and create user :
========================
exec sp_msforeachdb 'use [?] DROP USER [bp1\-adm-sachs4-xEU]'
exec sp_msforeachdb 'use [?] CREATE USER [bp1\-adm-sachs4-xEU] FOR LOGIN [bp1\-adm-sachs4-xEU]'
go
exec sp_msforeachdb 'use [?] EXEC sp_addrolemember ''db_datareader'', ''bp1\-adm-sachs4-xEU'''
11. Find out which service account is running in the sql server :
===============================================================
DECLARE @ServiceaccountName varchar(250)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@ServiceAccountName OUTPUT,
N'no_output'
SELECT @ServiceaccountName
12. To get fragmentation and Rebuild index:
========================================
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
1. SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('PLBX'), OBJECT_ID('dbo.<table_name>'), NULL, NULL, NULL);
2.ALTER INDEX ALL ON <table_name>
REBUILD WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = OFF)
go
3.select 'ALTER INDEX ALL ON '+ name +
'REBUILD WITH ( SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON)'+CHAR(10)+CHAR(13)+'go'+CHAR(10)+CHAR(13)
+'print ' +name +CHAR(10)+CHAR(13)+
'go'
from sys.objects with(nolock) where type='U'
4.ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO
5.ALTER INDEX [IX_Test] ON [dbo].[Test] REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON [dbo].[Test] REBUILD WITH (ONLINE = ON);
select ss.name [schema], object_name(ddips.object_id) table_name, si.name index_name,
ddips.page_count * 8 [Size KB], ddips.page_count * 8/1024.0 [Size MB]
from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'SAMPLED') ddips
join sys.indexes si on ddips.index_id = si.index_id and ddips.object_id = si.object_id
join sys.tables st on ddips.object_id = st.object_id
join sys.schemas ss on st.schema_id = ss.schema_id
group by ss.name,ddips.object_id,si.name,ddips.page_count
order by table_name asc
-----Generate rebuild script for all the tables in a DB.(Replace DB name)
Select 'ALTER INDEX ALL ON [CSRPT_TEST1].'+'['+S.name++']'+'.'+'['+T.name+']'+ ' REBUILD;' + CHAR(13) +CHAR(10) + 'GO'
from sys.tables T join sys.schemas S on T.schema_id=S.schema_id Where T.type='U' and T.is_ms_shipped=0 --order by name
GO
-----Generate rebuild script for all the tables in a database, followed by backup T-log to avoid Log file space crunch------
Select 'ALTER INDEX ALL ON '+'['+S.name++']'+'.'+'['+T.name+']'+ ' REBUILD WITH (ONLINE = ON,SORT_IN_TEMPDB = ON);' + CHAR(13) +CHAR(10) + 'GO'
+ CHAR(13) +CHAR(10) + 'BACKUP LOG CorruptTest1
TO DISK =N'''+'D:\CorruptTest1_'+T.Name+'.Trn''' + CHAR(13) +CHAR(10) + 'GO'
from sys.tables T join sys.schemas S on T.schema_id=S.schema_id Where T.type='U' and T.is_ms_shipped=0 --order by name
GO
13. SQL Script to find Expected Finish TIME for current running BACKUP / RESTORE :
============================================================================
SELECT
session_id,
start_time,
status,
command,
percent_complete,
estimated_completion_time,
estimated_completion_time /60/1000 as estimate_completion_minutes,
DATEADD(n,(estimated_completion_time /60/1000),GETDATE()) as estimated_completion_time, getdate() as'Current Time'
FROM sys.dm_exec_requests where command = 'BACKUP DATABASE' OR command = 'RESTORE DATABASE'
14. Attach database without log file and rename database:
=====================================================
1.USE [master]
2.GO
3.CREATE DATABASE [myTestDatabase] ON
4.( FILENAME = N'E:\Program Files\Microsoft SQL Server\Data\MSSQL.1\MSSQL\Data\myTestDatabase.mdf' )
5. FOR ATTACH
6.GO
7.if exists (select name from master.sys.databases sd where name = N'myTestDatabase ' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [myTestDatabase].dbo.sp_changedbowner @loginame=N'sa', @map=false
8.GO
15. Find out particular login in sql server :
==============================================
use msdb
sp_helplogins 'loginname'
or
select * from syslogins where name like 'tempuser%'
16. Findout whether update stastics updated or not any particular table :
=====================================================================
select a.id as 'ObjectID', isnull(a.name,'Heap') as 'IndexName', b.name as 'TableName',
stats_date (id,indid) as stats_last_updated_time
from sys.sysindexes as a
inner join sys.objects as b
on a.id = b.object_id
where b.type = 'U'
and b.name='CDM_APP_FEE'
17.How much percentage backup completed:
========================================
select percent_complete,db_name(database_id),* from sys.dm_exec_requests where command like '%backup%'
or
select * from sys.dm_exec_requests (percentage tab)
18.To find out is there anything running on Trace :
===============================================
1.To stop the profiler trace before its end time has occurred you exec two commands.
One stops the trace and the other closes the trace file.
2.Here are the commands:
a.Execute "select * FROM ::fn_trace_getinfo(default)"
b.Identify the traceid you using the folder name specified when starting the trace.
c.Execute these two commands replacing # with the trace id.
EXEC sp_trace_setstatus @traceid = #, @status = 0; -- Stop/pause Trace
EXEC sp_trace_setstatus @traceid = #, @status = 2; -- Close trace and delete it from the server
3.Verify it has stopped by executing "select * FROM ::fn_trace_getinfo(default)"
4.To view the captured trace files, open them using SQL Server Profiler.
5.Old profiler trace files are not automatically deleted. You will need to delete them your-self when you are finished.
19.How to shutdown, restart, logoff using command prompt?
==========================================================
shutdown -OPTION
OR
shutdown /OPTION
-l – Logs off the current user
-s – Shutdown the computer
-r – Restart the computer
EX : shutdown /r
20. Determine users connected to sqlserver database :
=================================================
select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses where db_name(dbid) = 'databasename'
21. To find out logshipping status :
=======================================
You can use the Log Shipping Status report by right clicking on the Server Name in Management Studio > Reports > Standard Reports > Transaction Log Shipping Status.
sql server standard reports options
You can execute the below Log Shipping System Stored Procedure to monitor log shipping and get detailed information about log shipping.
* sp_help_log_shipping_monitor
o This is the how SQL Server generates the Log Shipping Status report by executing sys.sp_help_log_shipping_monitor procedure. This procedure returns the log shipping status (whether it is healthy or not) as well as metadata such as primary and secondary database names, time since last backup, last backup file, last restore file, etc...
* sp_help_log_shipping_monitor_primary
o returns all columns from the log_shipping_monitor_primary table for the specified primary log shipping database. It returns server name, database name, time of last backup, backup threshold, threshold alert and history retention period.
* sp_help_log_shipping_monitor_secondary
o returns all columns from log_shipping_monitor_secondary table for the specified secondary log shipping database. It will return database name, server name, restore threshold, last copied file, time of last copy / restore and history retention period.
** select * from msdb..restorehistory where destination_database_Name like '%capitalone%'
order by restore_history_id desc
** sp_tables
** select * from mediafamily where restore_history_id=''
22. Filegroup wise space info :
==================================
declare @dbname varchar(256)
IF (OBJECT_ID('tempdb..#space') IS NOT NULL)
drop table #space
IF (OBJECT_ID('tempdb..#filestats') IS NOT NULL)
drop table #filestats
IF (OBJECT_ID('tempdb..#filegroup') IS NOT NULL)
drop table #filegroup
create table #filestats
(fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(255),
filename varchar(1000))
create table #filegroup
(groupid int,
groupname varchar(256))
insert into #filestats
exec ('DBCC showfilestats with no_infomsgs')
insert into #filegroup
select groupid, groupname
from sysfilegroups
select g.groupname,
sum(TotalExtents)*64.0/1024 as TotalSpaceMB,
sum((TotalExtents - UsedExtents) * 64.0 / 1024.0) AvailSpaceMB
from #filestats f
join #filegroup g on f.filegroup = g.groupid
group by g.groupname
drop table #filestats
drop table #filegroup
set nocount off
23. To find out which Queries using high cpu usage
==================================================
1. SELECT TOP 10 SUBSTRING(b.text, (a.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(b.text) ELSE a.statement_end_offset END - a.statement_start_offset)/2) + 1) AS statement_text, c.query_plan, total_worker_time as CPU_time FROM sys.dm_exec_query_stats a CROSS APPLY sys.dm_exec_sql_text (a.sql_handle) AS b CROSS APPLY sys.dm_exec_query_plan (a.plan_handle) AS c ORDER BY total_worker_time DESC
2.SELECT d.text AS SQL_statement, login_time, [host_name], [program_name], a.session_id, client_net_address, a.status, command, DB_NAME(database_id) AS DatabaseName FROM sys.dm_exec_requests a INNER JOIN sys.dm_exec_connections b ON a.session_id = b.session_id INNER JOIN sys.dm_exec_sessions c ON c.session_id = a.session_id CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS d WHERE a.session_id > 50 /* exclude the current statement */ AND a.session_id <> @@SPID ORDER BY a.status ---running processes
3.-- Get CPU Utilization History (SQL 2005 Only)
-----------------------------------------------
DECLARE @ts_now bigint;
SET @ts_now = (SELECT cpu_ticks / CONVERT(float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info);
SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int')
AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%') AS x
) AS y
ORDER BY record_id DESC OPTION (RECOMPILE);
-- Look at the trend over the entire period.
-- Also look at high sustained Other Process CPU Utilization values
24. To find out How much % backup completed : (compltion tab)
=============================================================
1. select * from sys.dm_exec_requests
25. Obtaining information about the top five queries by average CPU time :
========================================================================
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
26. DBCC Pages info syntax :
============================
DBCC PAGE
(
['database name'|database id], -- can be the actual name or id of the database
file number, -- the file number where the page is found
page number, -- the page number within the file
print option = [0|1|2|3] -- display option; each option provides differing levels of information
)
ex:
=====
-- List data and index pages allocated to the EMPLOYEE table
DBCC IND('MSSQLTIPS',EMPLOYEE,-1)
GO
27.How to Update Statistics
===========================
There are two ways to update statistics. The first way is the easy way. It is one line of code that will update all the statistics in the database using the default sample size of 20,000 rows per table.
EXEC sp_updatestats
The other way, is to use the UPDATE STATISTICS command. This command gives much better granularity of control:
-- Update all statistics on a table
UPDATE STATISTICS Sales.SalesOrderDetail
-- Update a specific index on a table
UPDATE STATISTICS Sales.SalesOrderDetail IX_SalesOrderDetail
-- Update one column on a table specifying sample size
UPDATE STATISTICS Production.Product(Products) WITH SAMPLE 50 PERCENT
Using update statistics can give you the granularity of control to only update the out of date statistics, thus having less impact on your production system.
28.To find long running queries in sql server :
===========================================
SELECT TOP 100 qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds, qs.execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
o.name AS object_name, DB_NAME(qt.dbid) AS database_name FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
ORDER BY average_seconds DESC;
How to find top long running queries?
=====================================
** You can use query stats in SQL 2005.
SELECT creation_time
,last_execution_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
, total_elapsed_time
, total_elapsed_time / execution_count avg_elapsed_time
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
--1200000000 microseconds are in 20 minutes
where total_elapsed_time >= 1200000000
ORDER BY total_elapsed_time / execution_count DESC;
** Find the TOP n Longest Running Procedures (or Queries), Ordered by Total Impact on Server
SQL Server 2005 / SQL Server 2008 Solution
==========================================
SELECT TOP 10
ProcedureName = t.text,
ExecutionCount = s.execution_count,
AvgExecutionTime = isnull( s.total_elapsed_time / s.execution_count, 0 ),
AvgWorkerTime = s.total_worker_time / s.execution_count,
TotalWorkerTime = s.total_worker_time,
MaxLogicalReads = s.max_logical_reads,
MaxLogicalWrites = s.max_logical_writes,
CreationDateTime = s.creation_time,
CallsPerSecond = isnull( s.execution_count / datediff( second, s.creation_time, getdate()), 0 )
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
-- WHERE ...
ORDER BY
s.total_elapsed_time DESC
29. Script to check SQL Server Cluster Nodes :
===========================================
Following Script will help your to check SQL Server Cluster Nodes, which will query clustered server instance and determine how many nodes exist in the SQL Server cluster
For SQL Server 2000
– Returns the list of nodes on which the virtual server can run. Such information is useful in failover clustering environments.
– If the current server instance is not a clustered server, fn_virtualservernodes returns an empty rowset.
SELECT * FROM ::fn_virtualservernodes()
For SQL Server 2005 /2008 / 2008 R2
– determine the nodes on a SQL Server clustered server instance:
SELECT * FROM sys.dm_os_cluster_nodes
For SQL Server 2005/2008/2008 R2 and SQL Server 2011 (Denali)
– VIEW SERVER STATE permission are required to execute this function
– current server instance is not a clustered server, fn_virtualservernodes returns an empty rowset.
SELECT * FROM fn_virtualservernodes()
Following Script will help your to check SQL Server Cluster Nodes, which will query clustered server instance and determine how many nodes exist in the SQL Server cluster
For SQL Server 2000
– Returns the list of nodes on which the virtual server can run. Such information is useful in failover clustering environments.
– If the current server instance is not a clustered server, fn_virtualservernodes returns an empty rowset.
SELECT * FROM ::fn_virtualservernodes()
For SQL Server 2005 /2008 / 2008 R2
– determine the nodes on a SQL Server clustered server instance:
SELECT * FROM sys.dm_os_cluster_nodes
For SQL Server 2005/2008/2008 R2 and SQL Server 2011 (Denali)
– VIEW SERVER STATE permission are required to execute this function
– current server instance is not a clustered server, fn_virtualservernodes returns an empty rowset.
SELECT * FROM fn_virtualservernodes()
30. To find out number of conections on a particular database on server :
=========================================================================
SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections, loginame as LoginName FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame
31. To find out CPU utilization for last 4 hours :
==================================================
DECLARE @ts_now BIGINT
SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms) FROM sys.dm_os_sys_info
SELECT record_id,
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,
SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization
FROM (
SELECT
record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
TIMESTAMP
FROM (
SELECT TIMESTAMP, CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '% %') AS x
) AS y
ORDER BY record_id DESC
32. To find space used by each table
=====================================
sp_msforeachtable 'sp_spaceused "?"'
33.Enabling lockpages in memory
====================================
http://msdn.microsoft.com/en-us/library/ms190730.aspx
34.Windows system shutdown event id
=======================================
1074
35.SQL Event id
===================
7034
36.Changing Tempdb location
=============================
USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'd:datatemplog.ldf')
GO
37.Find out datafiles and logfiles informaton
=================================================
SELECT name, physical_name AS current_file_location
FROM sys.master_files
38.TO comment the job
========================
/* ---------- */
39.Blocking information
============================
Blocking in SQL Server is a scenario where one connection to SQL Server locks one or more records, and a second connection to SQL Server requires a conflicting lock type on the record or records locked by the first connection. This causes the second connection to wait until the first connection releases its locks. By default, a connection will wait an unlimited amount of time for the blocking lock to go away.
In real time scenario blocking is something that can't be avoided needs to be looked upon when it occurs.
40.To find the Snapshot isolation is off or on
================================================
select sys.databases.snapshot_isolation_state ,sys.databases.snapshot_isolation_state_desc
from sys.databases where (sys.databases.[name] = 'dbname')
41)This is what I'm using to get the running jobs (principally so I can kill the ones which have probably hung):
========================================================================================================================
select job.Name, job.job_ID, job.Originating_Server, activity.run_requested_Date, datediff(minute, activity.run_requested_Date, getdate()) as Elapsed from msdb.dbo.sysjobs_view job inner join msdb.dbo.sysjobactivity activity on (job.job_id = activity.job_id) where run_Requested_date is not null and stop_execution_date is null and job.name like 'Your Job Prefix%'
-------------------------
IF EXISTS(SELECT 1 FROM msdb.dbo.sysjobs J JOIN msdb.dbo.sysjobactivity A ON A.job_id=J.job_id WHERE J.name=N'Your Job Name' AND A.run_requested_date IS NOT NULL AND A.stop_execution_date IS NULL) PRINT 'The job is running!' ELSE PRINT 'The job is not running.'
T-SQL script that creates the statements to first disable the jobs:
---------------------------------------
select 'exec msdb.dbo.sp_update_job @job_name = "' + name + "', @enabled = 0'
from msdb.dbo.sysjobs
order by name
Here is the T-SQL script that creates the statements to enable the jobs:
---------------------------------
select 'exec msdb.dbo.sp_update_job @job_name = "' + name + "', @enabled = 1'
from msdb.dbo.sysjobs
order by name
------------------------------------------------
Disable All SQL Server Agent Jobs
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO
---------------------------------------------------------
Enable All SQL Server Agent Jobs
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
WHERE Enabled = 0;
GO
---------------------------------------------------------
Disable Jobs By Job Name
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] LIKE 'Admin%';
GO
----------------------------------------------------------
Disable Jobs By Job Category
USE MSDB;
GO
UPDATE J
SET J.Enabled = 0
FROM MSDB.dbo.sysjobs J
INNER JOIN MSDB.dbo.syscategories C
ON J.category_id = C.category_id
WHERE C.[Name] = 'Database Maintenance';
GO
----------------------------------------------------------
Analyse the DB update states. Below query displays all the stats and last udpated date of each stat
SELECT name AS stats_name,
STATS_DATE(object_id, stats_id) AS statistics_update_date, *
FROM sys.stats
-----------------------------------------------------------
When was the last time your SQL Server database was restored
DECLARE @dbname sysname, @days int
SET @dbname = NULL --substitute for whatever database name you want
SET @days = -30 --previous number of days, script will default to 30
SELECT
rsh.destination_database_name AS [Database],
rsh.user_name AS [Restored By],
CASE WHEN rsh.restore_type = 'D' THEN 'Database'
WHEN rsh.restore_type = 'F' THEN 'File'
WHEN rsh.restore_type = 'G' THEN 'Filegroup'
WHEN rsh.restore_type = 'I' THEN 'Differential'
WHEN rsh.restore_type = 'L' THEN 'Log'
WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
WHEN rsh.restore_type = 'R' THEN 'Revert'
ELSE rsh.restore_type
END AS [Restore Type],
rsh.restore_date AS [Restore Started],
bmf.physical_device_name AS [Restored From],
rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rsh
INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all
ORDER BY rsh.restore_history_id DESC
GO
---------------------------------------------------
Steps to Grant execute permissions for stored procedure
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO <insert_username>' FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
---------------------------------------------------------
How much memory is occupied by AWE--output of memory in MB
select SUM(awe_allocated_kb)/1024 AS [AWE allocated,Mb] from sys.dm_os_memory_clerks
---------------------------------------------------------
How much memory is each SQL server database is using
SELECT(CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State', (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name', COUNT (*) AS 'Page Count'FROM sys.dm_os_buffer_descriptors GROUP BY [database_id], [is_modified] ORDER BY [database_id], [is_modified]; GO
=============================================================================
Script to fetch all the tablename,/size of a table
SELECT
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
--t.NAME NOT LIKE 'dt%'
t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, p.Rows
ORDER BY
TotalSpaceKB asc
================================================================================
Script to fetch all the table containing LOB_DATA as datatype
SELECT OBJECT_NAME(object_id) as OBJECT_NAME, FILEGROUP_NAME(data_space_id) as
FILE_GROUP_NAME, type_desc
FROM sys.partitions p
JOIN sys.allocation_units a
on p.partition_id = a.container_id
WHERE
type_desc = 'LOB_DATA'
===================================================================================
Script to fetch all the index and talbes and column on which index is built
SELECT '['+Sch.name+'].['+ Tab.[name]+']' AS TableName,
Ind.[name] AS IndexName,
SUBSTRING(( SELECT ', ' + AC.name
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I
ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC
ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC
ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 0
ORDER BY IC.key_ordinal
FOR
XML PATH('')
), 2, 8000) AS KeyCols,
SUBSTRING(( SELECT ', ' + AC.name
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I
ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC
ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC
ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 1
ORDER BY IC.key_ordinal
FOR
XML PATH('')
), 2, 8000) AS IncludeCols
FROM sys.[indexes] Ind
INNER JOIN sys.[tables] AS Tab
ON Tab.[object_id] = Ind.[object_id]
INNER JOIN sys.[schemas] AS Sch
ON Sch.[schema_id] = Tab.[schema_id]
==========================================================================================
Script to Fetch all the tables,index,Rowcount and size of table
SELECT DB_NAME() AS DatabaseName
, object_name(i.object_id) AS TableName
, ISNULL(i.name, 'HEAP') AS IndexName
, i.index_id AS IndexID
, i.type_desc AS IndexType
, p.partition_number AS PartitionNo
, p.[rows] AS NumRows
, au.type_desc AS InType
, au.total_pages AS NumPages
, au.total_pages * 8 AS TotKBs
, au.used_pages * 8 AS UsedKBs
, au.data_pages * 8 AS DataKBs
FROM sys.indexes i INNER JOIN sys.partitions p
ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units au ON
CASE
WHEN au.[type] in (1,3) THEN p.hobt_id
WHEN au.type = 2 THEN p.partition_id
end = au.container_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE o.is_ms_shipped <> 1
ORDER BY TableName, i.index_id
===========================================================================================
Incase of performanance issue, Investigating blocking using the sys.dm_os_waiting_tasks DMV
SELECT blocking.session_id AS blocking_session_id ,
blocked.session_id AS blocked_session_id ,
waitstats.wait_type AS blocking_resource ,
waitstats.wait_duration_ms ,
waitstats.resource_description ,
blocked_cache.text AS blocked_text ,
blocking_cache.text AS blocking_text
FROM sys.dm_exec_connections AS blocking
INNER JOIN sys.dm_exec_requests blocked
ON blocking.session_id = blocked.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle)
blocked_cache
CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle)
blocking_cache
INNER JOIN sys.dm_os_waiting_tasks waitstats
ON waitstats.session_id = blocked.session_id
==============================================================================
Clear the stats in sys.dm_wait_stats DMV manaually. by default stats in dmv are refershed when server is restarted.
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
==============================================================================
Identify CPU pressure using Signal waits
SELECT SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,
( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))
/ SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 )
AS PercentageSignalWaitsOfTotalTime
FROM sys.dm_os_wait_stats
============================================================================
--Last Restore date of a database
SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
--where restore_date>='2016-02-29'
ORDER BY [rs].[restore_date] DESC
============= Backup & Restore Percentage================================
SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('restore DATABASE ')
======================DB Mail==============================================
Send mail to Single Recipient
EXEC master.dbo.xp_sendmail
@recipients=N'danw@Adventure-Works.com',
@query = N'EXEC sp_configure' ;
GO
--Send mail to Multiple Recipients--
EXEC master.dbo.xp_sendmail
@recipients=N'danw@Adventure-Works.com;ashvinis@Adventure-Works.com',
@message=N'The master database is full.',
@copy_recipients=N'peterc@Adventure-Works.com',
@subject=N'Master database status' ;
GO
--Send result of SP_configure to recipient--
EXEC master.dbo.xp_sendmail
@recipients=N'danw@Adventure-Works.com',
@query = N'EXEC sp_configure' ;
GO
----Send Result as Attachment--
EXEC master.dbo.xp_sendmail
@recipients = N'danw@Adventure-Works.com',
@query = N'SELECT * FROM INFORMATION_SCHEMA.TABLES',
@subject = N'SQL Server Report',
@message = N'The contents of INFORMATION_SCHEMA.TABLES:',
@attach_results = 'TRUE',
@width = 250 ;
--------------List of users in a Group------------------------
EXEC xp_logininfo 'NTDOMAIN\MRA_SQLDEV_DukeDeveloper', 'members'
-------------Generate Backup Script for all User Database in server-------------------
Select 'BACKUP DATABASE ['+name+'] TO DISK = N'+''''+'Z:\MSSQL\Bak\'+name+'.bak'+''''+' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 5' from sys.databases where database_id>4
-----Generate Restore Script for all the database in server-----------
--Replace the Source Backup path to be restored
Select
'RESTORE DATABASE '+ '['+D.Name+']'+ ' FROM DISK = N'+''''+'(Please Replace Backup Path)'+'\'+D.Name+'.BAK'+''''+'
WITH MOVE N'+''''+F.Name+''''+ ' TO N'+''''+F.Physical_Name+''''+','+
' MOVE N'+''''+L.Name+''''+' TO N'+''''+L.physical_name+''''+','+' REPLACE, STATS = 5 --, NORECOVERY'
from sys.master_files F Join sysdatabases D On F.database_id=D.dbid and F.file_id=1 Join
sys.master_files L on L.database_id=D.dbid and L.file_id=2 and D.Name not in('Master','Model','MSDB','Tempdb')
------SQL 2000----script to generate restore script for all dbs
Select
'RESTORE DATABASE '+ '['+D.Name+']'+ ' FROM DISK = N'+''''+'(Please Replace Backup Path)'+'\'+D.Name+'.BAK'+''''+'
WITH MOVE N'+''''+F.Name+''''+ ' TO N'+''''+F.fileName+''''+','+
' MOVE N'+''''+L.Name+''''+' TO N'+''''+L.filename+''''+','+' REPLACE, STATS = 5 --, NORECOVERY'
from sysaltfiles F Join sysdatabases D On F.dbid=D.dbid and F.fileid=1 Join
sysaltfiles L on L.dbid=D.dbid and L.fileid=2 and D.Name not in('Master','Model','MSDB','Tempdb')
----------------Disc Space Report--------------------
http://www.codeproject.com/Articles/459566/SQL-Server-Script-to-Generate-HTML-Report-mail-for
--------------------Change DB owner for all databases---
EXEC sp_MSforeachdb 'EXEC [?]..sp_changedbowner ''sa'''
--------------Job List and Schedule----
--Lists all the jobs and their schedule in one instance---
SELECT
[sJOB].[job_id] AS [JobID]
, [sJOB].[name] AS [JobName]
, [sDBP].[name] AS [JobOwner]
, [sCAT].[name] AS [JobCategory]
, [sJOB].[description] AS [JobDescription]
, CASE [sJOB].[enabled]
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS [IsEnabled]
, [sJOB].[date_created] AS [JobCreatedOn]
, [sJOB].[date_modified] AS [JobLastModifiedOn]
, [sSVR].[name] AS [OriginatingServerName]
, [sJSTP].[step_id] AS [JobStartStepNo]
, [sJSTP].[step_name] AS [JobStartStepName]
, CASE
WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
ELSE 'Yes'
END AS [IsScheduled]
, [sSCH].[schedule_uid] AS [JobScheduleID]
, [sSCH].[name] AS [JobScheduleName]
, CASE [sJOB].[delete_level]
WHEN 0 THEN 'Never'
WHEN 1 THEN 'On Success'
WHEN 2 THEN 'On Failure'
WHEN 3 THEN 'On Completion'
END AS [JobDeletionCriterion]
FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
ON [sJOB].[originating_server_id] = [sSVR].[server_id]
LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
ON [sJOB].[category_id] = [sCAT].[category_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
ON [sJOB].[job_id] = [sJSTP].[job_id]
AND [sJOB].[start_step_id] = [sJSTP].[step_id]
LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
ON [sJOB].[owner_sid] = [sDBP].[sid]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
ORDER BY [JobName]
--List Articles that are not part of any publication----
select * from distribution.dbo.MSarticles where publication_id not in (select publication_id from distribution.dbo.MSpublications)
-----------------------------------------
-----------Uninstalling SQL 2005 Manually----------
First, here are the recommended steps to manually uninstall SQL 2005:
http://support.microsoft.com/kb/909967
If that doesn't work, this is how you can manually uninstall all versions of SQL 2005 so that SQL can be reinstalled:
Note – This method will delete all databases and configuration, you must have a backup of your existing SQL databases (which this uninstall will delete) before proceeding. (Again, this is not supported, please call Microsoft PSS for supported methods)
Uninstall all SQL components possible in Add/Remove Programs (including the SQL client and VSS writer)
Stop all SQL Services and configure them to disabled using Start > SQL Configuration Manager
Delete the following registry to get rid of the services. If you have multiple instance installed, it will be Keyname$Instance (i.e. MSSQLServer$OFFICESERVERS)
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesMSSQLServer
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesSQLServerAgent
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesMSSQLServerADHelper
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesReportServer
Verify that the services do not exist in the SQL Configuration Manager.
Delete the SQL Configuration in the registry
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServer
HKEY_LOCAL_MACHINESoftwareMicrosoftMicrosoft SQL Server
Note – if you installed a 32 bit version of SQL on 64 bit version of Windows, you will need to also delete them from the 32 bit registry node that is redirected. More Info MS 896459.
* HKEY_LOCAL_MACHINESOFTWAREWOW6432nodeMicrosoftMSSQLServer
* HKEY_LOCAL_MACHINESOFTWAREWOW6432nodeMicrosoftMicrosoft SQL Server
Delete the SQL Program files – Note this will also delete the existing databases on the system that must be restored from backup.
Delete the directory: C:Program FilesMicrosoft SQL Server
Note – if you installed a 32 bit version of SQL on 64 bit version of Windows, you will need to also delete them from the 32 bit program files:
C:Program Files (x86)Microsoft SQL Server
You might need to reboot to close all files before you can delete the directory.
====Generate restore Log command for a Database, if in case of AG sync-=================================
SELECT 'Restore Log '+s.database_name+' From disk=N'+''''+'\\cl1-prosql-02n2\'+m.physical_device_name+''''+ ' With NoRecovery, STATS=5',backup_finish_date,s.type
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME(8) -- Remove this line for all the database
and backup_finish_date>='02/28/2017'
ORDER BY backup_start_date asc, backup_finish_date
GO
--------------------------------------
List the users and permission on server
-- List out all users and user roles of all databases in a SQL Server instance
set nocount on
set quoted_identifier off
Declare @name varchar(100)
Declare @sqlstatement nvarchar(4000)
--move declare cursor into sql to be executed
Declare users_cursor CURSOR FOR Select name from sys.databases where database_id > 4
OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Database ' + @name
set @sqlstatement = N'use [' + @name +']'+char(13)+N'select
convert(char(30),dp2.name) UserName,convert(char(20),dp2.type_desc) UserType, convert(char(20),dp.name) Role
FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id
INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
WHERE dp2.principal_id > 4 AND dp2.type <>'+'''R'''
exec sp_executesql @sqlstatement
FETCH NEXT FROM users_cursor --have to fetch again within loop
INTO @name
END
CLOSE users_cursor
DEALLOCATE users_cursor
----------------------------------
Add DB to AG Group on Primary Server
ALTER AVAILABILITY GROUP AGGROUPNAME ADD DATABASE DBNAME
GO
------------------------------
ADD DB to AG group on Secondary Server
ALTER DATABASE DBNAME SET HADR AVAILABILITY GROUP = AGGROUPNAME
