backup history:
select top 5 a.server_name, a.database_name, backup_finish_date, a.backup_size,
CASE a.[type] -- Let's decode the three main types of backup here
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
ELSE a.[type]
END as BackupType
,b.physical_device_name
from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b
on a.media_set_id = b.media_set_id
where a.database_name Like 'master%'
order by a.backup_finish_date desc
==========================================
SELECT [backup_start_date], [backup_finish_date] FROM msdb.dbo.backupset WHERE [type] = 'D' AND [database_name] = 'pam' ORDER BY [backup_start_date] DESC;
=======================================
Rename database:
USE master;
GO
ALTER DATABASE AdventureWorks2012
Modify Name = Northwind ;
GO
==========================================
alter database DBOldName set single_user with rollback immediate
alter database DBOldName modify name = DBNewName
alter database DBNewName set multi_user
=========================================
finding SQl server memory allocation:
select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory
================================
SELECT command,
s.text,
start_time,
percent_complete,
estimated_completion_time/1000 as "seconds to go",
dateadd(second,estimated_completion_time/1000, getdate()) as "estimated completion time"
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
==================================================================================================
SELECT command,
start_time,
percent_complete,
estimated_completion_time/1000 as "seconds to go",
dateadd(second,estimated_completion_time/1000, getdate()) as "estimated completion time"
FROM sys.dm_exec_requests r
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
==================================================================================================
q sch
=========================================================
select * from sys.dm_os_memory_objects
select * from sys.dm_os_memory_pools
select * from sys.dm_os_memory_nodes
select * from sys.dm_os_memory_cache_entries
select * from sys.dm_os_memory_cache_hash_tables
============================================================
select type, name, sum(multi_pages_kb)/1024 as multi_pages_mb
from sys.dm_os_memory_clerks
where multi_pages_kb > 0
group by type, name
order by multi_pages_mb desc
============================================================
SELECT percent_complete,*
FROM sys.dm_exec_requests
WHERE command In ( 'RESTORE DATABASE', 'BACKUP DATABASE' )
=======================================================
sp_who2
DBCC INPUTBUFFER (59);
print @@version
USE Master
GO
SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id <> 0
GO
dbcc sqlperf(logspace)
select * from sys.databases
select * from sys.sysprocesses
select * from sys.dm_exec_requests where blocking_session_id<>0
=============================
To finding the blocking:
SELECT session_id, blocking_session_id, text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle);
====================
/**** Query to check currently running sessions ****/
SELECT DISTINCT
name AS database_name,
session_id,
host_name,
login_time,
login_name,
reads,
writes
FROM sys.dm_exec_sessions
LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE resource_type <> 'DATABASE'
--AND name ='specific db name'
ORDER BY name
====================
to find currently running on the system:
SELECT *
FROM sys.dm_exec_requests AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp;
GO
============================
To find the blocking:
SELECT SUBSTRING(dest.text, ( der.statement_start_offset / 2 ) + 1,
( CASE der.statement_end_offset
WHEN -1 THEN DATALENGTH(dest.text)
ELSE der.statement_end_offset
- der.statement_start_offset
END ) / 2 + 1) AS querystatement ,
deqp.query_plan ,
der.session_id ,
der.start_time ,
der.status ,
DB_NAME(der.database_id) AS DBName ,
USER_NAME(der.user_id) AS UserName ,
der.blocking_session_id ,
der.wait_type ,
der.wait_time ,
der.wait_resource ,
der.last_wait_type ,
der.cpu_time ,
der.total_elapsed_time ,
der.reads ,
der.writes
FROM sys.dm_exec_requests AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp;
GO
============================================================
*********
To find the latest restore:
select restore_date, destination_database_name, user_name, restore_type,
stop_at, stop_at_mark_name, stop_before
from msdb.dbo.restorehistory
where restore_date > '20180125 08:00'
--you may use a filter on the database name as well
--AND destination_database_name like 'MyDatabase%'
order by restore_date desc;
*********
==============================================================
SQL Server Long Query Script:
SELECT DISTINCT TOP 20
est.TEXT AS sqlturkiye_queryText
,DB_NAME(dbid)
,eqs.execution_count AS sqltr_execCount
,eqs.max_elapsed_time AS sqltr_maxelapsedTime
,ISNULL(eqs.total_elapsed_time / NULLIF(eqs.execution_count, 0), 0) AS sqltr_avgElapsedTime
,eqs.creation_time AS sqltr_CreatedTime
,ISNULL(eqs.execution_count / NULLIF(DATEDIFF(s, eqs.creation_time, GETDATE()), 0), 0) AS sqltr_execPerSecond
,total_physical_reads AS sqltr_agPhyRead
FROM sys.dm_exec_query_stats eqs
CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) est
ORDER BY eqs.max_elapsed_time DESC
=======================================================
==============================================================================
DBCC SHOWCONTIG
================================
To find the fragmentaion level:
SELECT OBJECT_NAME(ips.OBJECT_ID)
,i.NAME
,ips.index_id
,index_type_desc
,avg_fragmentation_in_percent
,avg_page_space_used_in_percent
,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
AND (ips.index_id = i.index_id)
ORDER BY avg_fragmentation_in_percent DESC
================================================================================
Script : Index Fragmentation Report Script
--To Find out fragmentation level of a given database
--This query will give DETAILED information
--CAUTION : It may take very long time, depending on the number of tables in the DB
USE AdventureWorks
GO
SELECT object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(N'AdventureWorks'), NULL, NULL, NULL , 'DETAILED') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY 1,5
GO
========================================================
==========================
SELECT *
FROM master.sys.syslogins;
===========================
EXEC sp_helplogins
You can also pass an "@LoginNamePattern" parameter to get information about a specific login:
EXEC sp_helplogins @LoginNamePattern='fred'
=====================================================
SELECT name FROM master..sysxlogins WHERE sid IS NOT NULL
=======================================================
select name, sid, password_hash from sys.sql_logins
========================================================
Get the list of all Login Accounts in a SQL Server:
SELECT name AS Login_Name, type_desc AS Account_Type
FROM sys.server_principals
WHERE TYPE IN ('U', 'S', 'G')
and name not like '%##%'
ORDER BY name, type_desc
==================================================
Get the list of all SQL Login Accounts only:
SELECT name
FROM sys.server_principals
WHERE TYPE = 'S'
==========================
How to find out List of all logins in SQL Server those are enabled/disabled. :
SELECT name, type_desc, is_disabled
FROM sys.server_principals
==========================================
---------To find users permission on SP------------
SELECT
dp.Class,
dps1.Name As Grantee,
dps2.Name As Grantor,
so.Name,
so.Type,
dp.Permission_Name,
dp.State_Desc
FROM sys.database_permissions AS dp
JOIN Sys.Database_Principals dps1
ON dp.grantee_Principal_ID = dps1.Principal_ID
JOIN Sys.Database_Principals dps2
ON dp.grantor_Principal_ID = dps2.Principal_ID
JOIN sys.objects AS so
ON dp.major_id = so.object_id
WHERE so.Name = 'UpdateStock'
============================================================
Get the list of all Login Accounts in a SQL Server
SELECT name AS Login_Name, type_desc AS Account_Type
FROM sys.server_principals
WHERE TYPE IN ('U', 'S', 'G')
and name not like '%##%'
ORDER BY name, type_desc
=====================
Get the list of all SQL Login Accounts only
SELECT name
FROM sys.server_principals
WHERE TYPE = 'S'
and name not like '%##%'
======================================
Get the list of all Windows Login Accounts only
SELECT name
FROM sys.server_principals
WHERE TYPE = 'U'
======================================
Get the list of all Windows Group Login Accounts only
SELECT name
FROM sys.server_principals
WHERE TYPE = 'G'
=================================================
select highest_cpu_queries.plan_handle,highest_cpu_queries.
plan_generation_num,highest_cpu_queries.max_worker_time,
highest_cpu_queries.total_physical_reads,
highest_cpu_queries.total_logical_reads,
highest_cpu_queries.total_elapsed_time,q.[text],q.dbid,q.objectid,q.number,q.encrypted,query_plan
from (select top 50 qs.plan_handle,
qs.plan_generation_num,qs.creation_time, qs.execution_count, qs.total_worker_time,
qs.max_worker_time, qs.total_elapsed_time,
qs.max_elapsed_time, qs.total_logical_reads, qs.max_logical_reads,
qs.total_physical_reads, qs.max_physical_reads from sys.dm_exec_query_stats
qs order by qs.total_worker_time DESC)
as highest_cpu_queries
cross apply sys.dm_exec_sql_text (plan_handle) as q
cross apply sys.dm_exec_query_plan (plan_handle) as qp
order by highest_cpu_queries.total_worker_time DESC
================================================================================
When was the last time a login was used?
--list of logins and last time each logged in
SELECT [Login] = login_name
,[Last Login Time] = MAX(login_time)
FROM sys.dm_exec_sessions
GROUP BY [login_name];
=======================================================
Top 3 CPU-sapping queries for which plans exist in the cache
SELECT TOP 3
total_worker_time ,
execution_count ,
total_worker_time / execution_count AS [Avg CPU Time] ,
CASE WHEN deqs.statement_start_offset = 0
AND deqs.statement_end_offset = -1
THEN '-- see objectText column--'
ELSE '-- query --' + CHAR(13) + CHAR(10)
+ SUBSTRING(execText.text, deqs.statement_start_offset / 2,
( ( CASE WHEN deqs.statement_end_offset = -1
THEN DATALENGTH(execText.text)
ELSE deqs.statement_end_offset
END ) - deqs.statement_start_offset ) / 2)
END AS queryText
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
ORDER BY deqs.total_worker_time DESC ;
==========================
Top ten most costly queries in cache by total worker time.
SELECT TOP ( 10 )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 , execution_count ,total_worker_time / 1000 AS total_worker_time_ms ,
( total_worker_time / 1000 ) / execution_count AS avg_worker_time_ms ,total_logical_reads ,
total_logical_reads / execution_count AS avg_logical_reads , total_elapsed_time / 1000 AS total_elapsed_time_ms ,
( total_elapsed_time / 1000 ) / execution_count AS avg_elapsed_time_ms , qp.query_plan FROM sys.dm_exec_query_stats
qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY total_worker_time DESC
========================================================
high CPU usage
tempdb usage
stack dump
You should analyse and determine the cause of the Stack dumps, If you have support from microsoft, you can consult them and of course you can always delete them,
they are nothing but memory dumps, may be generated because of memory issue,
access violations, DB courruption, etc. You can also check SQL Server error log for the information or errors logged at the same time when dump was generated.
Sometimes dumps are also generated because of Database corruption, So i will also suggest to run DBCC CHECKDB.
Scenario 1
I asked you about Version of SQL Server and you did not responded, the reason I asked it because if you are running RTM version of SQL Server or your SQL Server is not patched to latest Service Pack and cumulative update there is no point in opening case with Microsoft. If you do so the Microsoft Engineer or the support personal would first ask you to apply latest SP.
Other scenario is if you have not updated your SQL Server to latest SP, for instance for SQL Server 2012 you have SP3 released and you are still on SP1 and you log a case with Microsoft for this issue you would be charged and its quite possible the support guy would say this is known issues and is fixed in Sp3. So you would end up wasting money. So I strongly suggest you to check whether the SQL Server is on latest SP.
I also wanted to check whether you are actually running supported version of SQL Server or not. The way it is creating dump I have hunch that you are running SQL Server which is not patched to latest SP
Scenario 2
If the SQL Server is patched to latest SP and still it is crashing producing stack dumps I would suggest you to open case with Microsoft they are the best in terms of analyzing the stack dump file and would surely tell you the reason. Unless you are really good with analyzing dumps I would not suggest you to waste time in doing so.
I can share with you few blogs which would give you some hint on how to analyze the dumps
Looking into SQL Server Minidump File
How to analyze deadlock scheduler dumps
Analyzing non yielding resource monitor
Scenario 3
Not all crash dumps are because of bug in SQL Server many occur due to poorly configured SQL Server or some rouge queries running. But since you have not shared detailed errorlog it is difficult to say at this point. Make sure your SQL Server is configured correctly. Again if such is the case MS support will point this out.
Moral:
If SQL Server is not updated with latest SP first update it, look for the cumulative updates released after the SP(you can get that from first link I have shared) and make sure bug you are facing is not fixed in CU releases ONLY then open case with Microsoft.
can I delete them and if it is good thing to do ?
If you are planning to open case with Microsoft I suggest you move them to some other location just in case. If you have those dumps you can give more information to support personal who would be looking at your case. Also note that its quite likely that the dump produced would not capture all information related to the issue and support personal would ask you to enable trace flag and wait for the next dump to occur which will capture all the related information.
If you really like to delete it, delete the old ones and leave the new ones.
==============================================================================================
How to get a listing of all available cluster resources?
C:\Windows\System32>cluster resource
--or
C:\Windows\System32>cluster resource /status
31. How to failover a service from one node to another?
C:\Windows\System32>cluster group "groupname" /move:nodeName
==================================================================================================
open tran:
down vote
You can get all the information of active transaction by the help of below query
SELECT
trans.session_id AS [SESSION ID],
ESes.host_name AS [HOST NAME],login_name AS [Login NAME],
trans.transaction_id AS [TRANSACTION ID],
tas.name AS [TRANSACTION NAME],tas.transaction_begin_time AS [TRANSACTION
BEGIN TIME],
tds.database_id AS [DATABASE ID],DBs.name AS [DATABASE NAME]
FROM sys.dm_tran_active_transactions tas
JOIN sys.dm_tran_session_transactions trans
ON (trans.transaction_id=tas.transaction_id)
LEFT OUTER JOIN sys.dm_tran_database_transactions tds
ON (tas.transaction_id = tds.transaction_id )
LEFT OUTER JOIN sys.databases AS DBs
ON tds.database_id = DBs.database_id
LEFT OUTER JOIN sys.dm_exec_sessions AS ESes
ON trans.session_id = ESes.session_id
WHERE ESes.session_id IS NOT NULL
===========================================
dbcc opentran()
====================================
SELECT *
FROM sys.dm_tran_session_transactions tst INNER JOIN sys.dm_exec_connections ec ON tst.session_id = ec.session_id
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)
================================
SELECT DB_NAME(dbid) AS DBNAME, (SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS SQLSTATEMENT FROM master..sysprocesses WHERE open_tran > 0
==============================================
1-SELECT * FROM SYS.SYSPROCESSES WHERE OPEN_TRAN = 1
2-SELECT * FROM SYS.DM_TRAN_SESSION_TRANSACTIONS
=====================
uncommited transactinos
SELECT
er.session_id
,er.open_transaction_count
FROM sys.dm_exec_requests er
select top 5 a.server_name, a.database_name, backup_finish_date, a.backup_size,
CASE a.[type] -- Let's decode the three main types of backup here
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
ELSE a.[type]
END as BackupType
,b.physical_device_name
from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b
on a.media_set_id = b.media_set_id
where a.database_name Like 'master%'
order by a.backup_finish_date desc
==========================================
SELECT [backup_start_date], [backup_finish_date] FROM msdb.dbo.backupset WHERE [type] = 'D' AND [database_name] = 'pam' ORDER BY [backup_start_date] DESC;
=======================================
Rename database:
USE master;
GO
ALTER DATABASE AdventureWorks2012
Modify Name = Northwind ;
GO
==========================================
alter database DBOldName set single_user with rollback immediate
alter database DBOldName modify name = DBNewName
alter database DBNewName set multi_user
=========================================
finding SQl server memory allocation:
select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory
================================
SELECT command,
s.text,
start_time,
percent_complete,
estimated_completion_time/1000 as "seconds to go",
dateadd(second,estimated_completion_time/1000, getdate()) as "estimated completion time"
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
==================================================================================================
SELECT command,
start_time,
percent_complete,
estimated_completion_time/1000 as "seconds to go",
dateadd(second,estimated_completion_time/1000, getdate()) as "estimated completion time"
FROM sys.dm_exec_requests r
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
==================================================================================================
q sch
=========================================================
select * from sys.dm_os_memory_objects
select * from sys.dm_os_memory_pools
select * from sys.dm_os_memory_nodes
select * from sys.dm_os_memory_cache_entries
select * from sys.dm_os_memory_cache_hash_tables
============================================================
select type, name, sum(multi_pages_kb)/1024 as multi_pages_mb
from sys.dm_os_memory_clerks
where multi_pages_kb > 0
group by type, name
order by multi_pages_mb desc
============================================================
SELECT percent_complete,*
FROM sys.dm_exec_requests
WHERE command In ( 'RESTORE DATABASE', 'BACKUP DATABASE' )
=======================================================
sp_who2
DBCC INPUTBUFFER (59);
print @@version
USE Master
GO
SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id <> 0
GO
dbcc sqlperf(logspace)
select * from sys.databases
select * from sys.sysprocesses
select * from sys.dm_exec_requests where blocking_session_id<>0
=============================
To finding the blocking:
SELECT session_id, blocking_session_id, text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle);
====================
/**** Query to check currently running sessions ****/
SELECT DISTINCT
name AS database_name,
session_id,
host_name,
login_time,
login_name,
reads,
writes
FROM sys.dm_exec_sessions
LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE resource_type <> 'DATABASE'
--AND name ='specific db name'
ORDER BY name
====================
to find currently running on the system:
SELECT *
FROM sys.dm_exec_requests AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp;
GO
============================
To find the blocking:
SELECT SUBSTRING(dest.text, ( der.statement_start_offset / 2 ) + 1,
( CASE der.statement_end_offset
WHEN -1 THEN DATALENGTH(dest.text)
ELSE der.statement_end_offset
- der.statement_start_offset
END ) / 2 + 1) AS querystatement ,
deqp.query_plan ,
der.session_id ,
der.start_time ,
der.status ,
DB_NAME(der.database_id) AS DBName ,
USER_NAME(der.user_id) AS UserName ,
der.blocking_session_id ,
der.wait_type ,
der.wait_time ,
der.wait_resource ,
der.last_wait_type ,
der.cpu_time ,
der.total_elapsed_time ,
der.reads ,
der.writes
FROM sys.dm_exec_requests AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp;
GO
============================================================
*********
To find the latest restore:
select restore_date, destination_database_name, user_name, restore_type,
stop_at, stop_at_mark_name, stop_before
from msdb.dbo.restorehistory
where restore_date > '20180125 08:00'
--you may use a filter on the database name as well
--AND destination_database_name like 'MyDatabase%'
order by restore_date desc;
*********
==============================================================
SQL Server Long Query Script:
SELECT DISTINCT TOP 20
est.TEXT AS sqlturkiye_queryText
,DB_NAME(dbid)
,eqs.execution_count AS sqltr_execCount
,eqs.max_elapsed_time AS sqltr_maxelapsedTime
,ISNULL(eqs.total_elapsed_time / NULLIF(eqs.execution_count, 0), 0) AS sqltr_avgElapsedTime
,eqs.creation_time AS sqltr_CreatedTime
,ISNULL(eqs.execution_count / NULLIF(DATEDIFF(s, eqs.creation_time, GETDATE()), 0), 0) AS sqltr_execPerSecond
,total_physical_reads AS sqltr_agPhyRead
FROM sys.dm_exec_query_stats eqs
CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) est
ORDER BY eqs.max_elapsed_time DESC
=======================================================
==============================================================================
DBCC SHOWCONTIG
================================
To find the fragmentaion level:
SELECT OBJECT_NAME(ips.OBJECT_ID)
,i.NAME
,ips.index_id
,index_type_desc
,avg_fragmentation_in_percent
,avg_page_space_used_in_percent
,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
AND (ips.index_id = i.index_id)
ORDER BY avg_fragmentation_in_percent DESC
================================================================================
Script : Index Fragmentation Report Script
--To Find out fragmentation level of a given database
--This query will give DETAILED information
--CAUTION : It may take very long time, depending on the number of tables in the DB
USE AdventureWorks
GO
SELECT object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(N'AdventureWorks'), NULL, NULL, NULL , 'DETAILED') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY 1,5
GO
========================================================
==========================
SELECT *
FROM master.sys.syslogins;
===========================
EXEC sp_helplogins
You can also pass an "@LoginNamePattern" parameter to get information about a specific login:
EXEC sp_helplogins @LoginNamePattern='fred'
=====================================================
SELECT name FROM master..sysxlogins WHERE sid IS NOT NULL
=======================================================
select name, sid, password_hash from sys.sql_logins
========================================================
Get the list of all Login Accounts in a SQL Server:
SELECT name AS Login_Name, type_desc AS Account_Type
FROM sys.server_principals
WHERE TYPE IN ('U', 'S', 'G')
and name not like '%##%'
ORDER BY name, type_desc
==================================================
Get the list of all SQL Login Accounts only:
SELECT name
FROM sys.server_principals
WHERE TYPE = 'S'
==========================
How to find out List of all logins in SQL Server those are enabled/disabled. :
SELECT name, type_desc, is_disabled
FROM sys.server_principals
==========================================
---------To find users permission on SP------------
SELECT
dp.Class,
dps1.Name As Grantee,
dps2.Name As Grantor,
so.Name,
so.Type,
dp.Permission_Name,
dp.State_Desc
FROM sys.database_permissions AS dp
JOIN Sys.Database_Principals dps1
ON dp.grantee_Principal_ID = dps1.Principal_ID
JOIN Sys.Database_Principals dps2
ON dp.grantor_Principal_ID = dps2.Principal_ID
JOIN sys.objects AS so
ON dp.major_id = so.object_id
WHERE so.Name = 'UpdateStock'
============================================================
Get the list of all Login Accounts in a SQL Server
SELECT name AS Login_Name, type_desc AS Account_Type
FROM sys.server_principals
WHERE TYPE IN ('U', 'S', 'G')
and name not like '%##%'
ORDER BY name, type_desc
=====================
Get the list of all SQL Login Accounts only
SELECT name
FROM sys.server_principals
WHERE TYPE = 'S'
and name not like '%##%'
======================================
Get the list of all Windows Login Accounts only
SELECT name
FROM sys.server_principals
WHERE TYPE = 'U'
======================================
Get the list of all Windows Group Login Accounts only
SELECT name
FROM sys.server_principals
WHERE TYPE = 'G'
=================================================
select highest_cpu_queries.plan_handle,highest_cpu_queries.
plan_generation_num,highest_cpu_queries.max_worker_time,
highest_cpu_queries.total_physical_reads,
highest_cpu_queries.total_logical_reads,
highest_cpu_queries.total_elapsed_time,q.[text],q.dbid,q.objectid,q.number,q.encrypted,query_plan
from (select top 50 qs.plan_handle,
qs.plan_generation_num,qs.creation_time, qs.execution_count, qs.total_worker_time,
qs.max_worker_time, qs.total_elapsed_time,
qs.max_elapsed_time, qs.total_logical_reads, qs.max_logical_reads,
qs.total_physical_reads, qs.max_physical_reads from sys.dm_exec_query_stats
qs order by qs.total_worker_time DESC)
as highest_cpu_queries
cross apply sys.dm_exec_sql_text (plan_handle) as q
cross apply sys.dm_exec_query_plan (plan_handle) as qp
order by highest_cpu_queries.total_worker_time DESC
================================================================================
When was the last time a login was used?
--list of logins and last time each logged in
SELECT [Login] = login_name
,[Last Login Time] = MAX(login_time)
FROM sys.dm_exec_sessions
GROUP BY [login_name];
=======================================================
Top 3 CPU-sapping queries for which plans exist in the cache
SELECT TOP 3
total_worker_time ,
execution_count ,
total_worker_time / execution_count AS [Avg CPU Time] ,
CASE WHEN deqs.statement_start_offset = 0
AND deqs.statement_end_offset = -1
THEN '-- see objectText column--'
ELSE '-- query --' + CHAR(13) + CHAR(10)
+ SUBSTRING(execText.text, deqs.statement_start_offset / 2,
( ( CASE WHEN deqs.statement_end_offset = -1
THEN DATALENGTH(execText.text)
ELSE deqs.statement_end_offset
END ) - deqs.statement_start_offset ) / 2)
END AS queryText
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
ORDER BY deqs.total_worker_time DESC ;
==========================
Top ten most costly queries in cache by total worker time.
SELECT TOP ( 10 )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 , execution_count ,total_worker_time / 1000 AS total_worker_time_ms ,
( total_worker_time / 1000 ) / execution_count AS avg_worker_time_ms ,total_logical_reads ,
total_logical_reads / execution_count AS avg_logical_reads , total_elapsed_time / 1000 AS total_elapsed_time_ms ,
( total_elapsed_time / 1000 ) / execution_count AS avg_elapsed_time_ms , qp.query_plan FROM sys.dm_exec_query_stats
qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY total_worker_time DESC
========================================================
high CPU usage
tempdb usage
stack dump
You should analyse and determine the cause of the Stack dumps, If you have support from microsoft, you can consult them and of course you can always delete them,
they are nothing but memory dumps, may be generated because of memory issue,
access violations, DB courruption, etc. You can also check SQL Server error log for the information or errors logged at the same time when dump was generated.
Sometimes dumps are also generated because of Database corruption, So i will also suggest to run DBCC CHECKDB.
Scenario 1
I asked you about Version of SQL Server and you did not responded, the reason I asked it because if you are running RTM version of SQL Server or your SQL Server is not patched to latest Service Pack and cumulative update there is no point in opening case with Microsoft. If you do so the Microsoft Engineer or the support personal would first ask you to apply latest SP.
Other scenario is if you have not updated your SQL Server to latest SP, for instance for SQL Server 2012 you have SP3 released and you are still on SP1 and you log a case with Microsoft for this issue you would be charged and its quite possible the support guy would say this is known issues and is fixed in Sp3. So you would end up wasting money. So I strongly suggest you to check whether the SQL Server is on latest SP.
I also wanted to check whether you are actually running supported version of SQL Server or not. The way it is creating dump I have hunch that you are running SQL Server which is not patched to latest SP
Scenario 2
If the SQL Server is patched to latest SP and still it is crashing producing stack dumps I would suggest you to open case with Microsoft they are the best in terms of analyzing the stack dump file and would surely tell you the reason. Unless you are really good with analyzing dumps I would not suggest you to waste time in doing so.
I can share with you few blogs which would give you some hint on how to analyze the dumps
Looking into SQL Server Minidump File
How to analyze deadlock scheduler dumps
Analyzing non yielding resource monitor
Scenario 3
Not all crash dumps are because of bug in SQL Server many occur due to poorly configured SQL Server or some rouge queries running. But since you have not shared detailed errorlog it is difficult to say at this point. Make sure your SQL Server is configured correctly. Again if such is the case MS support will point this out.
Moral:
If SQL Server is not updated with latest SP first update it, look for the cumulative updates released after the SP(you can get that from first link I have shared) and make sure bug you are facing is not fixed in CU releases ONLY then open case with Microsoft.
can I delete them and if it is good thing to do ?
If you are planning to open case with Microsoft I suggest you move them to some other location just in case. If you have those dumps you can give more information to support personal who would be looking at your case. Also note that its quite likely that the dump produced would not capture all information related to the issue and support personal would ask you to enable trace flag and wait for the next dump to occur which will capture all the related information.
If you really like to delete it, delete the old ones and leave the new ones.
==============================================================================================
How to get a listing of all available cluster resources?
C:\Windows\System32>cluster resource
--or
C:\Windows\System32>cluster resource /status
31. How to failover a service from one node to another?
C:\Windows\System32>cluster group "groupname" /move:nodeName
==================================================================================================
open tran:
down vote
You can get all the information of active transaction by the help of below query
SELECT
trans.session_id AS [SESSION ID],
ESes.host_name AS [HOST NAME],login_name AS [Login NAME],
trans.transaction_id AS [TRANSACTION ID],
tas.name AS [TRANSACTION NAME],tas.transaction_begin_time AS [TRANSACTION
BEGIN TIME],
tds.database_id AS [DATABASE ID],DBs.name AS [DATABASE NAME]
FROM sys.dm_tran_active_transactions tas
JOIN sys.dm_tran_session_transactions trans
ON (trans.transaction_id=tas.transaction_id)
LEFT OUTER JOIN sys.dm_tran_database_transactions tds
ON (tas.transaction_id = tds.transaction_id )
LEFT OUTER JOIN sys.databases AS DBs
ON tds.database_id = DBs.database_id
LEFT OUTER JOIN sys.dm_exec_sessions AS ESes
ON trans.session_id = ESes.session_id
WHERE ESes.session_id IS NOT NULL
===========================================
dbcc opentran()
====================================
SELECT *
FROM sys.dm_tran_session_transactions tst INNER JOIN sys.dm_exec_connections ec ON tst.session_id = ec.session_id
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)
================================
SELECT DB_NAME(dbid) AS DBNAME, (SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS SQLSTATEMENT FROM master..sysprocesses WHERE open_tran > 0
==============================================
1-SELECT * FROM SYS.SYSPROCESSES WHERE OPEN_TRAN = 1
2-SELECT * FROM SYS.DM_TRAN_SESSION_TRANSACTIONS
=====================
uncommited transactinos
SELECT
er.session_id
,er.open_transaction_count
FROM sys.dm_exec_requests er
No comments:
Post a Comment