Blocking on sqlserver

To check blocking


select * from sys.sysprocesses where blocked <> 0


head blocker

++++++++++

declare @handle binary(20), @spid int

select top 1 @spid = blocked

from master..sysprocesses a

where a.blocked != 0 

and a.blocked not in (select spid from master..sysprocesses b where blocked != 0)

if @spid is not null

begin

  select @handle = sql_handle from master..sysprocesses where spid = @spid

  exec sp_who2 @spid

  dbcc inputbuffer (@spid)

select lastwaittype from sysprocesses where spid=@spid

  select * from ::fn_get_sql(@handle)

end  

Last successful backup in sqlserver script

SET quoted_identifier OFF
DECLARE @dbname AS VARCHAR(80)
DECLARE @msgdb AS VARCHAR(100)
DECLARE @dbbkpname AS VARCHAR(80)
DECLARE @dypart1 AS VARCHAR(2)
DECLARE @dypart2 AS VARCHAR(3)
DECLARE @dypart3 AS VARCHAR(4)
DECLARE @currentdate AS VARCHAR(10)
DECLARE @server_name AS VARCHAR(30)
SELECT @server_name = @@servername
SELECT @dypart1 = DATEPART(dd,GETDATE())
SELECT @dypart2 = DATENAME(mm,GETDATE())
SELECT @dypart3 = DATEPART(yy,GETDATE())
SELECT @currentdate= @dypart1 + @dypart2 + @dypart3

SELECT SUBSTRING(s.name,1,50) AS 'DATABASE Name',
b.backup_start_date AS 'Full DB Backup Status',
c.backup_start_date AS 'Differential DB Backup Status',
d.backup_start_date AS 'Transaction Log Backup Status'
FROM MASTER..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date =
(SELECT MAX(backup_start_date)AS 'Full DB Backup Status'
FROM msdb..backupset
WHERE database_name = b.database_name
AND TYPE = 'D') -- full database backups only, not log backups
LEFT OUTER JOIN msdb..backupset c
ON s.name = c.database_name
AND c.backup_start_date =
(SELECT MAX(backup_start_date)'Differential DB Backup Status'
FROM msdb..backupset

Procedure to attend SQL server memory issues

Check SQL installed on Server

Confirm SQL server is installed on the server through services.msc or SQL services configuration manger.

Start à Run àservices.msc verify SQL Services are visible like SQL server, SQL Server Agent. Etc.,

SQL services configuration

Start àProgramsàMicrosoft SQL server 20xxàConfiguration Tools àSQL Server configuration  Manager

Verify SQL maintenance jobs status

Verify SQL maintenance jobs like Reindex , Update Stats or CheckDB  is running during business hours. These jobs are created and scheduled to run on week end or off business hours.  If any maintenance jobs in running state.

Check blocking

select *from sysprocesseswhere blocked >0

If many blockings are found, head blocker can be determined by using the query below.

declare @handle binary(20), @spid int

select top1 @spid = blocked

from master..sysprocesses a

where a.blocked != 0

and a.blocked not in (select spid from master..sysprocesses b whereblocked != 0)

if @spid isnot null

begin

  select@handle = sql_handlefrom master..sysprocesses where spid = @spid

  execsp_who2 @spid

  dbccinputbuffer (@spid)

--exec ('kill ' + @SPID)

select lastwaittype from sysprocesses where spid=@spid

  select* from ::fn_get_sql(@handle)

end

Verify SQL Server Max. Memory settings

 The minimum and maximum memory values are important, especially since we gave the SQL Server account the permission to lock its pages in memory. If other applications are running on this server, we need to specify how much memory want SQL Server to take.

SQL Server application is Memory consuming application. We need to restrict SQL instance with minimum and maximum memory setting by leaving some amount of memory to Operating system.

Go into SQL Server Management Studio, right-click on the server name and click Properties, go into Memory, and just verify SQL max. memory set for the SQL server. 

Check Memory Pressure

This query will provide information about how many queries are requesting for memory , how many are granted and how many are pending to be granted.

--Query 3: SELECT * FROM sys.dm_exec_query_resource_semaphores --Query 4: SELECT TEXT,* FROM sys.dm_exec_query_memory_grants cross apply sys.dm_exec_sql_text (sql_handle) order by requested_memory_kb desc

Checking resource semaphore wait type during memory pressure.

SELECT * FROM SYSPROCESSES  ORDER BY lastwaittype

SELECT * FROM sys.dm_exec_query_resource_semaphore

File movement in sql server

USE master;
GO

ALTER  database <Database name>
MODIFY FILE (NAME = <database>_Data,
 FILENAME = ' J:\sqldb03\MSSQL12.MSSQLSERVER\data\<database>.mdf');

go

ALTER DATABASE  <Database name>
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

go

ALTER DATABASE  <Database name>
SET OFFLINE
GO

 Move files from
U:\SQL01\MSSQL11.SQLE\MSSQL\Data to U:\SQL07\MSSQL11.SQLE\MSSQL\Data

ALTER DATABASE  <Database name> SET ONLINE
go

ALTER DATABASE  <Database name> SET MULTI_USER

Go