Category Archives: SQL Server DBA Scripts

SQL Server – Basic SQL Commands for day to day SQL DBA


===== Number of SQL Connections & login name ====

SELECT
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame

===== Check for database compatibility =====

ALTER DATABASE DB_Caching_SearchFacets SET COMPATIBILITY_LEVEL = 110
SELECT compatibility_level FROM sys.databases WHERE name = ‘DBName’

— Enabling SQL Agent notification – SQL Server 2005, 2008, 2008 R2

USE [msdb]
EXEC msdb.dbo.sp_set_sqlagent_properties
@email_save_in_sent_folder=1

EXEC master.dbo.xp_instance_regwrite
N’HKEY_LOCAL_MACHINE’
, N’SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent’
, N’UseDatabaseMail’
, N’REG_WORD’
, 1

EXEC master.dbo.xp_instance_regwrite
N’HKEY_LOCAL_MACHINE’
, N’SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent’
, N’DatabaseMailProfile’
, N’REG_SZ’
, N’01_mail’

=========================================================

/***Run this query and save the full results – who is doing what at this moment*****/

SELECT r.session_id
,r.scheduler_id
,r.status
,s.text
,r.start_time
,r.blocking_session_id
,r.wait_type
,r.wait_time
,r.last_wait_type
,r.wait_resource
,r.open_transaction_count
,r.cpu_time
,r.total_elapsed_time
,r.reads
,r.writes
,r.logical_reads
,r.percent_complete
,r.estimated_completion_time
,wt.*
FROM sys.dm_exec_requests r
LEFT OUTER JOIN sys.dm_os_waiting_tasks wt ON r.session_id = wt.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) s
ORDER BY r.session_id ;

=========================================================

=== Check orphaned users for all DBs ====

CREATE TABLE ##ORPHANUSER
(
DBNAME VARCHAR(100),
USERNAME VARCHAR(100),
CREATEDATE VARCHAR(100),
USERTYPE VARCHAR(100)
)

EXEC SP_MSFOREACHDB’ USE [?]
INSERT INTO ##ORPHANUSER
SELECT DB_NAME() DBNAME, NAME,CREATEDATE,
(CASE
WHEN ISNTGROUP = 0 AND ISNTUSER = 0 THEN ”SQL LOGIN”
WHEN ISNTGROUP = 1 THEN ”NT GROUP”
WHEN ISNTGROUP = 0 AND ISNTUSER = 1 THEN ”NT LOGIN”
END) [LOGIN TYPE] FROM sys.sysusers
WHERE SID IS NOT NULL AND SID <> 0X0 AND ISLOGIN =1 AND
SID NOT IN (SELECT SID FROM sys.syslogins)’

SELECT * FROM ##ORPHANUSER

DROP TABLE ##ORPHANUSER

=========================================================

—–When will the Backup Finish / Backup status & FInish—-

SELECT command, percent_complete,
‘elapsed’ = total_elapsed_time / 60000.0,
‘remaining’ = estimated_completion_time / 60000.0
FROM sys.dm_exec_requests
WHERE command like ‘BACKUP%’

=========================================================
—- Backup details for single database —-

SELECT TOP 50 s.database_name, m.physical_device_name, CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(100)) + ‘ ‘ + ‘Seconds’ TimeTaken, s.backup_start_date, 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 ORDER BY backup_start_date DESC

=========================================================

–== Determine Locks held in database ==–

SELECT request_session_id AS [spid], DB_NAME(resource_database_id) AS [dbname],
CASE WHEN resource_type = ‘OBJECT’ THEN OBJECT_NAME(resource_associated_entity_id)
WHEN resource_associated_entity_id = 0 THEN ‘n/a’
ELSE OBJECT_NAME(p.object_id) END AS [entity_name], index_id, resource_type AS [resource],
resource_description AS [description], request_mode AS mode, request_status AS [status]
FROM sys.dm_tran_locks t
LEFT OUTER JOIN sys.partitions p ON p.partition_id = t.resource_associated_entity_id
WHERE t.resource_type <> ‘DATABASE’;

=========================================================

/******* Find the SESSIONS that are currently blocking ******/

CREATE TABLE #who2
(SPID int NOT NULL,
[Status] varchar(100),
Login varchar(50) ,
HostName varchar(100),
[BlkBy] varchar(20) ,
DBName varchar(55) ,
Command varchar(256),
CPUTime varchar(20),
DiskIO varchar(20),
LastBatch varchar(100),
ProgramName varchar(100),
SPID2 varchar(20),
RequestID varchar(20)
)
INSERT INTO #who2
EXEC master.sys.sp_who2 active
SELECT r.session_id AS SPID
,(SELECT MAX(HostName) FROM #who2 WHERE SPID = r.session_ID) AS Server
,(select max(sysprocesses.hostprocess) from master.dbo.sysprocesses (nolock) where sysprocesses.spid = r.session_id) as TaskManagerPID
,(SELECT MAX(LOGIN) FROM #who2 WHERE SPID = r.session_ID) AS Login
–,p.cpu as CPUTime, p.physical_io as DiskIO
,(SELECT SUM(cast(CPUTime as bigint)) FROM #who2 WHERE SPID = r.session_ID) AS CPUTime
,(SELECT SUM(cast(DiskIO as int)) FROM #who2 WHERE SPID = r.session_ID) AS DiskIO
,CONVERT(VARCHAR(16),start_time,120) AS start_time
,(SELECT MAX(DBName) FROM #who2 WHERE SPID = r.session_ID) AS DBName
— ,(SELECT MAX(Command) FROM #who2 WHERE SPID = r.session_ID) AS Command
— ,qt.text,r.statement_start_offset, r.statement_end_offset
,substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end – r.statement_start_offset)/2 + 1) as query_text — this is the statement executing right now
— ,qt.dbid
— ,qt.objectid
,blocking_session_id AS BlockingSPID
— ,substring(convert(char(23),DATEADD(ms ,r.cpu_time, 0),121 ),12,8) AS cpu_time
,RIGHT(‘0’+CONVERT(VARCHAR,(DATEDIFF(ss,start_time,GETDATE()))/(3600)),2) + ‘:’+RIGHT(‘0’+CONVERT(VARCHAR,(DATEDIFF(ss,start_time,GETDATE()))%(3600)/60),2)+’:’+RIGHT(‘0’+CONVERT(VARCHAR,(DATEDIFF(ss,start_time,GETDATE()))%60),2) AS elapsed_time
,SUBSTRING(CONVERT(VARCHAR,CAST(r.reads AS MONEY),1),1,LEN(CONVERT(VARCHAR,CAST(r.reads AS MONEY),1))-3) AS reads
,SUBSTRING(CONVERT(VARCHAR,CAST(r.writes AS MONEY),1),1,LEN(CONVERT(VARCHAR,CAST(r.writes AS MONEY),1))-3) AS writes
,SUBSTRING(CONVERT(VARCHAR,CAST(r.logical_reads AS MONEY),1),1,LEN(CONVERT(VARCHAR,CAST(r.logical_reads AS MONEY),1))-3) AS logical_reads
,(SELECT MAX(ProgramName) FROM #who2 WHERE SPID = r.session_ID) AS ProgramName
,wait_type ,wait_time –,wait_resource
,r.status
–,*
–,r.scheduler_id
FROM sys.dm_exec_requests r (NOLOCK)
–inner join master.dbo.sysprocesses p with (nolock)
–on p.spid = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as qt
WHERE r.session_id > 50
AND qt.text NOT LIKE ‘%SELECT r.session_id%’
–AND r.session_id IN (SELECT SPID FROM #who2 WHERE LOGIN LIKE ‘%siitusr%’)
ORDER BY 3,1
go
drop table #who2
go

=========================================================

——- KILL all the connections for a database ——

Use Master
Go

Declare @dbname sysname

Set @dbname = ‘databaseName’

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
Execute (‘Kill ‘ + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid > @spid
End

—— Simple script to close all connections to a MSSQL database. —-

USE master;
GO
ALTER DATABASE dbName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE dbName
SET MULTI_USER;
GO

=========================================================

—– get all DB sizes in MB———————-

SELECT d.name,
ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 — Skip system databases
GROUP BY d.name
ORDER BY d.name

=========================================================

———————————————————————–
———————————————————————–

–1.To find out which recovery model your database is using

SELECT databasepropertyex(‘database’, ‘recovery’)

——————————————————————————

–2.To change the recovery option for a database

ALTER DATABASE database name SET RECOVERY {FULL | SIMPLE | BULK_LOGGED}

——————————————————————————
–3.To know the compatability level of all databases

select * from sys.databases

——————————————————————————

–4.To know the compatability of a particular database

select * from sys.databases where name=’database name’

——————————————————————————

–5.To know the space availability in a log file

dbcc sqlperf(logspace)

——————————————————————————

–6.To know the space availability in a data file

sp_spaceused
——————————————————————————

–7.To know the details of particular database (db name,filename,filegroup,size)

sp_helpfile
——————————————————————————

–8.To know the details of all databases (db name,size,owner,date created,status,compatability level)

sp_helpdb

——————————————————————————

–9.To know the details of database (name,filename)

select * from sys.sysaltfiles

——————————————————————————

–10.To know the details of database (db name,login name,status,command)

sp_who2

——————————————————————————

–11.command for FULL BACKUP

backup database ‘database name’ to disk=’path.bak’

——————————————————————————

–12.command for differential backup

backup database ‘aaa’ to disk=’path.bak’ with differential

——————————————————————————

–13.command for transaction log backup

backup log ‘aaa’ to disk=’d:\aaalog.trn’

——————————————————————————

–14.command for file & filegroup backup

backup database ‘aaa’ file=’aaa’, filegroup=’primary’ to disk=’d:\aaafile.bak’ with noformat

——————————————————————————

–15.command for tail log backup

backup log ‘aaa’ to disk=’d:\aaatail.trn’ with no_truncate

——————————————————————————

–16.command for mirrored backup

backup database aaa to disk=’d:\aaamirror1.bak’ mirror to disk=’d:\aaamirror2.bak’ with format

——————————————————————————

–17.command for striped backup

backup database aaa to disk=’d:\aaastriped_part1.bak’,disk=’d:\aaastriped_part2.bak’

——————————————————————————
–18.command for partial backup

backup database aaa read_write_filegroups to disk=’d:\aaapartial.bak’

——————————————————————————

–19.command for partial differential backup

backup database aaa read_write_filegroups to disk=’d:\aaapartialdiff.bak’ with differential

——————————————————————————

–20. command for copyonly backup for (data files)

backup database aaa to disk=’d:\aaa_copyonly.bak’ with copy_only

——————————————————————————

–21.command for copyonly backup for (log files)

backup log aaa to disk=’d:\aaa_copyonly_log.bak’ with copy_only

——————————————————————————

–22.To know the server role permissions

exec sp_srvrolepermission
——————————————————————————

–23.To know the particular server role permission

exec sp_srvrolepermission sysadmin

——————————————————————————

–24.To create built in administrator account

create login[builtin administrators] from windows

——————————————————————————

–25.To know the details of protocols

select * from sys.endpoints

——————————————————————————

–26.To know rows in a table

select count (*) from tablename

——————————————————————————

–27.command for restore

restore database dbname from disk=’path’ with norecovery,replace

——————————————————————————

–28.command to know the validity of a backup

restore verifyonly from disk=’path’

——————————————————————————

–29.command to know the header of backup

restore headeronly from disk=’path’

——————————————————————————

–30.command to verify the contents of backup

restore filelistonly from disk=’path’

——————————————————————————

–31.command to restore a full backup into a different location

restore DATABASE [NEW_DATABASE_NAME] FROM DISK = ‘F:\pathFULL.bak’ WITH FILE=1,
MOVE
‘OLD_FILE_NAME_mdf’ TO ‘C:\NEW_FILE_NAME….mdf’,
MOVE
‘OLD_FILE_NAME_ldf’ TO ‘C:\NEW_FILE_NAME…..ldf’;
GO

——————————————————————————

–32.commands to keep database in offline mode

alter database dbname set offline with rollback immediate

–the above option kills all the current transactions &put the database immediately in offline state

alter database dbname set offline with rollback after 5 seconds

–the above option kills the transactions after 5 seconds & then puts the db in offline state

alter database dbname set offline with no_wait

–the above option can be used when we dont want the command to be blocked

——————————————————————————

–33.command to keep the database in online state

alter database dbname set online

——————————————————————————

–34.command for backup of database with PASSWORD protected?

BACKUP DATABASE [DATABASE NAME] TO DISK=’C:\path.BAK’ WITH MEDIAPASSWORD=’password’

——————————————————————————

–35.Restoring database with specifying PASSWORD

RESTORE DATABASE [DATABASE NAME] FROM DISK=’C:\path.BAK’ WITH MEDIAPASSWORD=’password’

——————————————————————————

–36.command to create database

create database dbname

——————————————————————————

–37.command to change the collation settings

alter database dbname collate sql_latin1_general_cp1_cs_as

——————————————————————————

–38.command to set the database in a single user mode

alter database dbname set single_user

——————————————————————————

–39.command to create a file group

alter database dbname add filegroup filegroup name

——————————————————————————

–40.command to change the logical name of a file

alter database dbname modify file(name=’old filename’,newname=’new filename’)

——————————————————————————

–41.command to disable autogrowth of a file

alter dbname modify file(name=’filename’,filegrowth=0)

——————————————————————————

–42.command to resize a file

alter dbname modify file(name=’filename’,size=’kb’)

——————————————————————————

–43.command to make database read only

alter database dbname set read_only

——————————————————————————

–44.command to remove file from database

alter database dbname remove file filename

——————————————————————————

–45.command to change the path of a file

alter database dbname modify file(name=’logical filename’,filename=’path.mdf’)
alter database dbname modify file(name=’logical filename’,filename=’path.ldf’)

——————————————————————————

–46.command to put database in a restricted mode

alter database dbname set restricted_user

——————————————————————————

–48.command to know the edition

select @@version
select serverproperty (‘edition’)

——————————————————————————

–49.command to know how much space is available in logfile

dbcc sqlperf(logspace)

——————————————————————————

–50.command to know how much space is available in datafile

sp_spaceused

——————————————————————————

–51.command to know database name and size

sp_databases

——————————————————————————

–52.command to know the database id

select db_id(databasename)

——————————————————————————

–53.command for point in time recovery

restore log dbname from disk=’path’ stopat ‘dd:mm:yy hh:mm:ss’

——————————————————————————

–54.command to delete older backups

use msdb
go
exec sp_delete_database_backuphistory ‘dbname’

——————————————————————————

–55.command to delete older backups beyond a date

use msdb
go
exec sp_delete_backuphistory’date’

——————————————————————————

–56.command to know job information

use msdb
select * from sysjobs

——————————————————————————

–57.command to know database is really in suspect mode or not

select databasepropertyex(‘dbname”status’)

——————————————————————————

— 58. SHrink TempDB, log files

USE [tempdb]
–Check Database File Size before shrink
SELECT name, size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files

–Shrink tempdb Log File (.ldf)
DBCC SHRINKFILE(templog,1) — run this line > SQL 2005
BACKUP LOG tempdb WITH TRUNCATE_ONLY — Truncate_Only, not available > SQL 2005
DBCC SHRINKFILE(templog,1)

–Shrink tempdb Data File (.mdf)
DBCC SHRINKFILE(tempdb7,1) — run this line > SQL 2005
BACKUP LOG tempdb WITH TRUNCATE_ONLY — Truncate_Only, not available > SQL 2005
DBCC SHRINKFILE(tempdev,1)

–Check Database File Size after shrink
SELECT name, size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files

 

script to find out all the Orphan Users on the SQL Server


–We can use below script to find out the Orphan Users on the Server

IF

EXISTS(SELECT*FROMsysobjectsWHERE id =OBJECT_ID(N'[dbo].[usp_ShowOrphanUsers]’)ANDOBJECTPROPERTY(id,N’IsProcedure’)= 1

)

 

DROP

PROCEDURE [dbo].[usp_ShowOrphanUsers]

GO

CREATE

PROC dbo.usp_ShowOrphanUsers

AS

BEGIN

CREATE

TABLE #Results

(

[Database Name] sysname

,

 

[Orphaned User]

sysname,

[Type User]

sysname)

SET

NOCOUNTON

DECLARE

@DBName sysname, @Qry NVARCHAR(4000

)

 

SET

@Qry =

 

SET

@DBName =

 

WHILE

@DBName ISNOT

NULL

 

BEGIN

SET

@DBName

=

(

 

 

SELECT

MIN(name

)

 

FROM

master..

sysdatabases

 

WHERE

name NOT

IN

(

 

 

‘master’,‘model’,‘tempdb’,‘msdb’,‘distribution’,‘pubs’,‘northwind’,‘dba_database’

)

AND

 

 

DATABASEPROPERTY(name,‘IsOffline’)= 0

AND

DATABASEPROPERTY(name,‘IsSuspect’)= 0

AND

name > @DBName

)

IF

@DBName ISNULL

BREAK

SET

 

 

@Qry =‘ SELECT ”’+ @DBName +

”’ AS [Database Name],

CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User],

 

[Type User] =

 

CASE isntuser

 

WHEN ”0” THEN ”SQL User”

 

WHEN ”1” THEN ”NT User”

 

ELSE ”Not Available”

 

END

 

FROM ‘

 

 

+QUOTENAME(@DBName)+

‘..sysusers su

WHERE su.islogin = 1

 

AND su.name NOT IN (”INFORMATION_SCHEMA”, ”sys”, ”guest”, ”dbo”, ”system_function_schema”)

 

AND NOT EXISTS (SELECT 1 FROM master..syslogins sl WHERE su.sid = sl.sid)’

 

 

INSERT

INTO #Results

EXEC

master..sp_executesql@Qry

END

SELECT

 

*

 

FROM

#Results

ORDER

BY [Database Name], [Orphaned User]

IF

@@ROWCOUNT= 0

PRINT

 

‘No orphaned users exist in this server.’

 

END

GO

–Execute the proc

EXEC

[dbo].[usp_ShowOrphanUsers]

Script to get Long Running queries, current running queries in SQL Server


— Run following query to find longest running query using T-SQL.

SELECT

DISTINCTTOP 10

t

.TEXT QueryName,

s

.execution_count AS ExecutionCount,

s

.max_elapsed_time AS MaxElapsedTime,

ISNULL

(s.total_elapsed_time / s.execution_count, 0)AS AvgElapsedTime

,

 

s

.creation_time AS LogCreatedOn,

ISNULL

(s.execution_count /DATEDIFF(s, s.creation_time,GETDATE()), 0)AS FrequencyPerSec

FROM

sys.dm_exec_query_stats s

CROSS

APPLYsys.dm_exec_sql_text( s.sql_handle) t

ORDER

 

BY

 

s

.max_elapsed_time DESC

GO

 

—————————————————————–

— Identifying the current running queries :

SELECT

r.session_id, r.status, r.start_time, r.command, s.

text

FROM

 

 

sys.dm_exec_requests r

CROSS

APPLYsys.dm_exec_sql_text(r.sql_handle) s

WHERE

r.status=

‘running’

 

 

 

 

 

 

 

———————————————————————————

— the below query will return the top 10 I/O intensive queries, that are causing my tempdb to bloat like crazy.

 

SELECT

top 10(total_logical_reads/execution_count

),

(

 

 

total_logical_writes/execution_count

),

(

 

 

total_physical_reads/execution_count

),

 

Execution_count

,sql_handle, plan_handle

FROM

sys.dm_exec_query_stats

ORDER

BY (total_logical_reads + total_logical_writes)

Desc

 

 

 

 

 

————————————————————————————

/***SQL Server 2005 keeps alot of good information in the dynamic management views about this kind of thing. Below are the 2 main queries I use to find slow running application queries in our systems.

Queries taking longest elapsed time:***/

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,

(

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

DB_NAME

(qt.dbid)AS database_name

FROM

sys.dm_exec_query_stats qs

CROSS

APPLYsys.dm_exec_sql_text(qs.sql_handle)as qt

LEFT

OUTERJOINsys.objects o ON qt.objectid = o.

object_id

 

where

qt.dbid=DB_ID

()

 

ORDER

BY average_seconds DESC;

Get Index suggestions from SQL Server Database


— Potentially Useful Indexes

select

d

.*

 

, s.avg_total_user_cost, s.avg_user_impact, s.last_user_seek,s.unique_compiles

from

sys.dm_db_missing_index_group_stats s,sys.dm_db_missing_index_groups g

,sys.dm_db_missing_index_details d

where

s.group_handle = g.index_group_handle

and

d.index_handle = g.index_handle

order

by s.avg_user_impact

desc

go

 

 

— suggested index columns and usage

declare

@handle

int

select

 

 

@handle = d.index_handle

from

sys.dm_db_missing_index_group_stats s,sys.dm_db_missing_index_groups g

,sys.dm_db_missing_index_details d

where

s.group_handle = g.index_group_handle

and

d.index_handle = g.index_handle

select

*

from

sys.dm_db_missing_index_columns(@handle

)

 

order

by column_id

Get Index recommendations from SQL Server Database


SELECT

sys.objects.name,

(

avg_total_user_cost * avg_user_impact)*(user_seeks + user_scans)as Impact,

cast

(‘CREATE NONCLUSTERED INDEX ~NewNameHere~ ON ‘+sys.objects.name +‘ ( ‘+ mid.equality_columns +

CASE

WHEN mid.inequality_columns ISNULLTHENELSECASEWHEN mid.equality_columns ISNULLTHENELSE‘,’END+

mid

.inequality_columns END+‘ ) ‘+CASEWHEN mid.included_columns ISNULLTHENELSE‘INCLUDE (‘+ mid.included_columns +‘)’END+

‘;’

asxml)AS CreateIndexStatement, mid.equality_columns, mid.inequality_columns, mid.included_columns FROM

sys

.dm_db_missing_index_group_statsAS migs INNERJOINsys.dm_db_missing_index_groupsAS mig ON migs.group_handle = mig.index_group_handle

INNER

JOINsys.dm_db_missing_index_detailsAS mid ON mig.index_handle = mid.index_handle INNERJOINsys.objectsWITH (nolock)

ON

mid.object_id=sys.objects.object_idWHERE (migs.group_handle IN(SELECTTOP (500) group_handle FROM

sys

.dm_db_missing_index_group_statsWITH (nolock)ORDERBY (avg_total_user_cost * avg_user_impact)*(user_seeks + user_scans)DESC

))

 

ORDER

BY 2 DESCSELECTsys.objects.name,(avg_total_user_cost * avg_user_impact)*(user_seeks + user_scans)as Impact,

cast

(‘CREATE NONCLUSTERED INDEX ~NewNameHere~ ON ‘+sys.objects.name +‘ ( ‘+ mid.equality_columns +CASE

WHEN

mid.inequality_columns ISNULLTHENELSECASEWHEN mid.equality_columns ISNULLTHENELSE‘,’END+ mid.inequality_columns END+‘ ) ‘

+

CASEWHEN mid.included_columns ISNULLTHENELSE‘INCLUDE (‘+ mid.included_columns +‘)’END+‘;’asxml)AS CreateIndexStatement,

mid

.equality_columns, mid.inequality_columns, mid.included_columns FROMsys.dm_db_missing_index_group_statsAS migs INNER

JOIN

sys.dm_db_missing_index_groupsAS mig ON migs.group_handle = mig.index_group_handle INNERJOINsys.dm_db_missing_index_detailsAS mid ON

mig

.index_handle = mid.index_handle INNERJOINsys.objectsWITH (nolock)ON mid.object_id=sys.objects.object_idWHERE

(migs.group_handle IN(SELECTTOP (500) group_handle FROMsys.dm_db_missing_index_group_statsWITH (nolock)ORDERBY

(avg_total_user_cost * avg_user_impact)*(user_seeks + user_scans)DESC))ORDERBY 2

DESC

 

Get performance CACHE from SQL Server


——————————————————————————-

/* SQL Server stores the procedure cache in 8KB data pages.

You can use the dynamic management view sys.dm_os_memory_cache_counters to provide a summary of how

the cache is allocated using this query:*/

/* CACHESTORE_OBJCP. These are compiled plans for stored procedures, functions and triggers.

CACHESTORE_SQLCP. These are cached SQL statements or batches that aren’t in stored procedures, functions and triggers. This includes any dynamic SQL or raw SELECT statements sent to the server.

CACHESTORE_PHDR. These are algebrizer trees for views, constraints and defaults. An algebrizer tree is the parsed SQL text that resolves the table and column names.

*/

SELECT

TOP 6LEFT([name], 20)as [name],

LEFT([type], 20)as [type],

[single_pages_kb]

+ [multi_pages_kb] AS cache_kb,

[entries_count]

FROM

sys.dm_os_memory_cache_counters

order

by single_pages_kb + multi_pages_kb

DESC

 

 

 

———————————————————————————-

/*We can see individual cache entries by using the sys.dm_exec_cached_plans dynamic management view.*/

 

SELECT

usecounts, cacheobjtype, objtype, plan_handle

FROM

sys.

dm_exec_cached_plans

 

 

————————————————————————————-

 

/* This query lists the most used query plans.

 

In includes plans for stored procedures, adhoc or dynamic SQL, triggers, prepared SQL and views.

 

*/

 

 

select

TOP 100

objtype

,

p

.size_in_bytes,LEFT([sql].[text], 100)as [text]

from

sys.dm_exec_cached_plans p

outer

applysys.dm_exec_sql_text(p.plan_handle)

sql

ORDER

 

 

BY usecounts

DESC

 

—————————————————————————————-

Get High CPU Sessions & Queries from SQL Server


–CREATE

–procedure HighCpuSessions_in_Production

–as

 

INSERT

INTO dbo.HighCpuSessions

SELECT

GETDATE(),r.session_id,status,SUBSTRING(qt.text,

r

.statement_start_offset/2,

(

CASE

WHEN

r.statement_end_offset =1

THEN

LEN(CONVERT(NVARCHAR(MAX), qt.text))* 2

ELSE

r.statement_end_offset

END

r.statement_start_offset)/2)

AS

query_text

— this is the statement executing right now

 

,

qt.dbid

,

qt.objectid

,

r.cpu_time

,

r.total_elapsed_time

,

r.reads

,

r.writes

,

r.logical_reads

,

r.scheduler_id

FROM

sys.dm_exec_requests r

CROSS

APPLYsys.dm_exec_sql_text(sql_handle)AS qt

WHERE

r.session_id >50

order

by CPU_time desc

————————————————————————————————————————————————-

–create

–procedure TOP50ExecutedQueries

–as

 

select

top 50

(

total_logical_reads + total_logical_writes)as total_logical_io,

(

total_logical_reads/execution_count)as avg_logical_reads,

(

total_logical_writes/execution_count)as avg_logical_writes,

(

total_physical_reads/execution_count)as avg_phys_reads,

substring

(st.text,(qs.statement_start_offset/2)+1,

((

case qs.statement_end_offset

when

1 thendatalength(st.text)

else

qs.statement_end_offset

end

qs.statement_start_offset)/2)+ 1)as statement_text,

*

 

from

 

sys

.dm_exec_query_statsas qs

cross

applysys.dm_exec_sql_text(qs.sql_handle)as st

order

by

total_logical_io

desc

Find all Databases size in SQL Server


CREATE

PROCEDURE PROD_DB_SIZE

AS

 

BEGIN

set

 

 

nocount

on

declare

 

 

@name

sysname

declare

 

 

@SQL nvarchar(600

)

 

/* Use temporary table to sum up database size w/o using group by */

create

table #databases

(

 

DATABASE_NAME

sysnameNOTNULL,

size

intNOTNULL)

declare

c1 cursorfor

select

name frommaster.dbo.

sysdatabases

 

where

has_dbaccess(name)= 1 and name notin(‘master’,‘model’,‘msdb’,‘Northwind’,‘pubs’,‘tempdb’)

— Only look at databases to which we have access

 

open

c1

fetch

c1 into @name

while

@@fetch_status>= 0

begin

select

@SQL =

‘insert into #databases

select N”’

 

 

+ @name +

”’, sum(size) from ‘

 

+

QuoteName(@name)+

‘.dbo.sysfiles’

 

/* Insert row for each database */

execute

(@SQL

)

 

fetch

c1 into @name

end

deallocate

c1

select

 

[DATABASE_NAME]

,

[DATABASE_SIZE in GB]

= size*8/1024 /* Convert from 8192 byte pages to K */

from

#databases

— order by DATABASE_SIZE

order

by 2

desc

END

 

 

— EXEC PROD_DB_SIZE

DB Log file location, file size growth and percent


–This script pulls all data and log files

–associated with any user db’s

BEGIN

CREATE TABLE #FILEINFO

(DatabaseName VARCHAR(100),

PhysicalFileName NVARCHAR(520),

FileSizeMB INT,

Growth VARCHAR(100))

DECLARE @command VARCHAR(5000)

SELECT @command = ‘Use [‘ + ‘?’ + ‘] select ‘ + ”” + ‘?’ + ”” + ‘ AS DatabaseName, 

sysfiles.filename AS PhysicalFileName,

CAST(sysfiles.size/128.0 AS int) AS FileSize,

CASE

 WHEN status & 0x100000 = 0

     THEN convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + ” MB”

  ELSE STR(growth) + ” %”

END growth

FROM dbo.sysfiles’

INSERT #FILEINFO EXEC sp_MSForEachDB @command

SELECT * FROM #FILEINFO

order by DatabaseName, PhysicalFileName

DROP TABLE #FILEINFO

END

GO

Detect highest CPU consuming queries


Create

Procedure SQL_HighCPU

as

Set

NOCOUNT

ON

————————————————

— Description: This procedure helps you detect the highest consuming CPU queries

— Compatibility: SQL Server 2005 & 2008

—————————————————

SELECT

TOP 50(a.total_worker_time/a.execution_count)as [Avg_CPU_Time]

,

Convert

(Varchar,Last_Execution_Time)as‘Last_execution_Time’

,

Total_Physical_Reads

,

SUBSTRING

(b.text,a.statement_start_offset/2

,

(casewhen a.statement_end_offset =1 thenlen(convert(nvarchar(max), b.text))* 2 else

a

.statement_end_offset end a.statement_start_offset)/2)as Query_Text,

dbname

=Upper(db_name(b.dbid)),

b

.objectid as‘Object_ID’

FROM

sys.dm_exec_query_stats a

cross

apply

sys

.dm_exec_sql_text(a.sql_handle)as b

ORDER

BY

[Avg_CPU_Time]

DESC

 

— EXEC SQL_HighCPU

Get database name and file location in SQL Server 2005


 

SELECT

sd

.[name] AS [Database Name] 

, smf.[physical_name] AS [MDF Filename]

FROM

 

sys.master_files smf 

JOIN sys.databases sd ON sd.[database_Id] = smf.[database_Id]

WHERE

smf

.[physical_name] LIKE

‘%send%’

 

———————————————————————————————————————————

/* Get database name and file location in SQL Server 2005 with size */

DECLARE

@Files TABLE

 

(  

Name SYSNAME,  

FileName VARCHAR(256),  

Size BIGINT  

)  

INSERT

@Files

EXEC

 sp_msforeachdb ‘SELECT name, filename, size FROM ?.sys.sysfiles’

SELECT

 *

FROM

@Files

TempDB Issues – Shrinking TempDB in SQL Server 2005


From time to time you find yourself needing to shrink some space out of TempDB. Shrinking database files is never my first choice but sometimes it is the best I have. Many people think that you cannot shrink TempDB in SQL 2005

Why would I need to shrink TempDB? 

Yesterday afternoon my pager started going crazy because an Ad-Hoc query that needed some tuning filled TempDB on a server. Luckily, the user only impacted their own query so it was easy to quickly identify them and work with the right people to get the query rewritten. 

Once the immediate problem was resolved there had to be some cleanup. On this server, TempDB has 32 files (1 per processor) all on the same disk. The full database condition caused all kinds of alerts in our monitoring tools, from drive space alerts to too few growths remaining. There were 3 possible solutions to quiet the alerts: 

1.       Reboot – There is never a good time to reboot a production server 

2.       Turn off the Alerts – Not really an option. My preference would be for increasing the sensitivity 

3.       Shrink TempDB – Not a great option, but the best of the 3 

Shrinking TempDB 

Once we had decided that we would go ahead and shrink the files in TempDB it seemed like the hard part was done, but after running the following command: 

USE [tempdb] 

GO 

DBCC SHRINKFILE (N’tempdev’ , 5000) 

GO 

Result :

DBCC SHRINKFILE: Page 1:878039 could not be moved because it is a work file page. 

DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages 

—— ———– ———– ———– ———– ————– 

2      1           878040      640000      4672        4672 

(1 row(s) affected) 

DBCC execution completed. If DBCC printed error messages, contact your system administrator. 

“Page could not be moved because it is a work file page.”. This is a new thing in SQL 2005 caused by the caching that is done in TempDB. I am not going to try to explain here how objects are cached in TempDB, but Kalen Delaney’s Inside Sql Server Series is a great place to learn about it if you are interested (http://www.insidesqlserver.com/books.html).  What is important is that the cached objects are tied to a query plan and that by freeing the procedure cache you can make those objects go away, allowing you to shrink your files. 

Trying again: 

DBCC FREEPROCCACHE 

GO 

USE [tempdb] 

GO 

DBCC SHRINKFILE (N’tempdev’ , 5000) 

GO 

This time it worked: 

DBCC execution completed. If DBCC printed error messages, contact your system administrator. 

DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages 

—— ———– ———– ———– ———– ————– 

2      1           640000      640000      264         264 

(1 row(s) affected) 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Monitoring Transaction LOG Space on SQL Server


 This SP will monitor Log space for SQL Server.

IF OBJECT_ID(‘dbo.logspace_monitor’, ‘P’)
IS NOT NULL
DROP PROCEDURE dbo.logspace_monitor

GO

CREATE PROCEDURE logspace_monitor

      @threshold
int = 70

AS

SET NOCOUNT ON

CREATE TABLE #logSpaceStats

(

databaseName sysname,

logSize decimal(18,5),

logUsed decimal(18,5),

staus int

)

declare @cmd nvarchar(max)

set @cmd = ‘dbcc sqlperf(logspace) with no_infomsgs’

insert into #logSpaceStats exec sp_executesql @cmd

PRINT ‘*********************************************’

PRINT ‘DATABASES with logspace used greated than 70%’

PRINT ‘*********************************************’

PRINT ”

select

cast(DatabaseName as varchar(25))as ‘DATABASE NAME’,

cast(logused as varchar(20))as ‘LOG SPACE USED’,

cast(a.log_reuse_wait_desc as varchar(15)) as ‘LOG REUSE WAIT’

from #logSpaceStats,sys.databases a where databasename=a.name and logUsed >@threshold

go

Recover Database from Suspect


 /*

Copy this script to sql query analyser and change db name and its location in the script and run it as mention in the steps pasted with the script.

*/

–1. Create Database with exact name and mdf-ldf files

–2. Stop MSSQLSERVER service, replace created mdf file with original one

–3. Start MSSQLSERVER service, the database will be in Suspend mode

–4. From Query Analyzer (QA) execute script

Use master

Go

sp_configure ‘allow updates’, 1

Reconfigure with override

Go

–5. From QA execute script

Update sysdatabases set status= 32768 where name = ‘eSM_DF’

–6. Restart MSSQLSERVER service, the database will be in Emergency mode

–7. Rebuild Log. From QA execute script

DBCC REBUILD_LOG (‘eSM_DF’, ‘E:\SQL2000\MSSQL$SQL2000\Data\eSM_DF_Log.LDF’)

–You got a

–Message – Warning: The log for database ‘eSM_DF’ has been rebuilt.

–8. From QA execute script

Use master

Go

sp_configure ‘allow updates’, 0

Go

–9. Clear from Enterprise Manager on database properties options tab Restrict

–Access checkbox

Finding the SQL Server Installation date


—–SQL 2000/2005 Version

 set nocount on

go

 DECLARE @SQLVersion varchar(2)

select @SQLVersion = left(CAST(SERVERPROPERTY(‘ProductVersion’)AS sysname),1)

if @SQLVersion = ‘9’

BEGIN

 —–SQL 2005

 DECLARE @InstallDate nvarchar(4000)

DECLARE @svr_name varchar(100)

DECLARE @reg_key varchar(500)

DECLARE @ServiceName VARCHAR(8000)

Declare @ServicesList VARCHAR(8000) — List of delimited items

declare @counter int

declare @sql nvarchar(4000)

declare @displayname varchar(200)

select @svr_name = CAST(SERVERPROPERTY(‘ServerName’)AS sysname)

set @reg_key = ‘SOFTWARE\Microsoft\Microsoft SQL Server\90\Bootstrap\MSIRefCount’

EXEC master..xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,

@key=@reg_key, @value_name=’Uninstall’,

@value=@InstallDate output

set @ServicesList = @installDate

create table #list(id int identity(1,1),item VARCHAR(8000))

WHILE CHARINDEX(‘,’,@ServicesList,0) <> 0

BEGIN

SELECT

@ServiceName=RTRIM(LTRIM(SUBSTRING(@ServicesList,1,CHARINDEX(‘,’,@ServicesList,0)-1))),

@ServicesList=RTRIM(LTRIM(SUBSTRING(@ServicesList,CHARINDEX(‘,’,@ServicesList,0)+LEN(‘,’),LEN(@ServicesList))))

IF LEN(@ServiceName) > 0

INSERT INTO #List SELECT @ServiceName

END

IF LEN(@ServicesList) > 0

INSERT INTO #List SELECT @ServicesList — Put the last item in

create table #servicelist (ServiceName varchar(200),InstallDate varchar(50))

select @counter = max(id) from #list

while @counter > 0

BEGIN

select @sql = item from #list where id = @counter

set @reg_key = ‘SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\’ + @sql

EXEC master..xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,

@key=@reg_key, @value_name=’DisplayName’,

@value=@displayname output

EXEC master..xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,

@key=@reg_key, @value_name=’InstallDate’,

@value=@InstallDate output

insert into #servicelist values (@displayname,@installdate)

SET @COUNTER = @COUNTER – 1

END

select @svr_name AS ‘ServerName’,s.servicename as ‘ServericeName’,cast(s.installdate as DateTime) as ‘InstallDate’ from #servicelist s

drop table #list

drop table #servicelist

END

go

DECLARE @SQLVersion varchar(2)

select @SQLVersion = left(CAST(SERVERPROPERTY(‘ProductVersion’)AS sysname),1)

IF @SQLVersion = ‘8’

BEGIN

–SQL 2000

DECLARE @Installdate varchar(50)

DECLARE @svr_name varchar(100)

DECLARE @reg_key varchar(500)

DECLARE @instance_name varchar(20)

select @svr_name = CAST(SERVERPROPERTY(‘ServerName’)AS sysname)

select @instance_name = CAST(SERVERPROPERTY(‘instancename’)AS sysname)

if @instance_name is NULL

BEGIN

set @reg_key = ‘SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\Microsoft SQL Server 2000’

END

ELSE BEGIN

set @reg_key = ‘SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\Microsoft SQL Server 2000 (‘ + @instance_name + ‘)’

END

EXEC master..xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,

@key=@reg_key, @value_name=’InstallDate’,

@value=@installdate output

select @svr_name as ‘ServerName’,’Microsoft SQL Server 2000′ as Servicename, cast(@installdate as DateTime) as ‘InstallDate’

END

go

Index DeFragmentation


/*

Index Defragmentation is one of the most important DBA tasks. This will significantly improve query performance. When you perform any DML operation (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. If you use predefined maintenance plan it will take much server resource and time. Here is a custom stored procedure.

If you want to determine the level of fragmentation, you can use the SYS.DM_DB_INDEX_PHYSICAL_STATS statement. The SYS.DM_DB_INDEX_PHYSICAL_STATS DMV displays fragmentation information for the data and indexes of the specified object.

Here I use 3 conditions :

1. Fragmentation >30 AND PAGES>1000 then rebuild
2. Fragmentation between 15 to 30 AND PAGES>1000 then reorganize&updatestatistics
3. If the above two conditions are false then update the statistics

Before you run the procedure create the tables provided for history propose

Click the following URL Index Architecture By Gail Shaw–>http://www.sqlservercentral.com/articles/Indexing/68439/

*/

————————-For SQL-2005/2008—————————-

USE MSDB;

CREATE TABLE [DBO].[DBA_DEFRAG_MAINTENANCE_HISTORY]

(

[DB_NAME] [SYSNAME] NOT NULL,

[TABLE_NAME] [SYSNAME] NOT NULL,

[INDEX_NAME] [SYSNAME] NOT NULL,

[FRAG] [FLOAT] NULL,

[PAGE] [INT] NULL,

[ACTION_TAKEN] [VARCHAR](35) NULL,

[DATE] [DATETIME] NULL DEFAULT (GETDATE())

) ON [PRIMARY]

–Archive the data’s in master DB

USE MASTER;

CREATE TABLE [DBO].[DBA_DEFRAG_MAINTENANCE_HISTORY]

(

[DB_NAME] [SYSNAME] NOT NULL,

[TABLE_NAME] [SYSNAME] NOT NULL,

[INDEX_NAME] [SYSNAME] NOT NULL,

[FRAG] [FLOAT] NULL,

[PAGE] [INT] NULL,

[ACTION_TAKEN] [VARCHAR](35) NULL,

[DATE] [DATETIME] NULL DEFAULT (GETDATE())

) ON [PRIMARY]

— Defragmentation Procedure:

— Note: Below script I use try/catch statement if PAGE LEVEL LOCK is disabled then the index has rebuild.

–EXEC [MSDB].[DBO].[USP_DBA_INDEX_DEFRAGMENTATION] ‘dba_db1’

USE [MSDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [DBO].[USP_DBA_INDEX_DEFRAGMENTATION]

@P_DBNAME SYSNAME

AS

BEGIN

SET NOCOUNT ON

DECLARE

@DB_NAME SYSNAME,

@TAB_NAME SYSNAME,

@IND_NAME VARCHAR(5000),

@SCHEMA_NAME SYSNAME,

@FRAG FLOAT,

@PAGES INT

SET @DB_NAME=@P_DBNAME

TRUNCATE TABLE MSDB.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY

CREATE TABLE #TEMPFRAG

(

TABLE_NAME SYSNAME,

INDEX_NAME VARCHAR(5000),

FRAG FLOAT,

PAGES INT,

SCHEM_NAME SYSNAME

)

EXEC (‘USE [‘+@DB_NAME+’];

INSERT INTO #TEMPFRAG

SELECT OBJECT_NAME(F.OBJECT_ID) OBJ,I.NAME IND,

F.AVG_FRAGMENTATION_IN_PERCENT,

F.PAGE_COUNT,TABLE_SCHEMA

FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(),NULL,NULL,NULL,NULL) F

JOIN SYS.INDEXES I

ON(F.OBJECT_ID=I.OBJECT_ID)AND I.INDEX_ID=F.INDEX_ID

JOIN INFORMATION_SCHEMA.TABLES S

ON (S.TABLE_NAME=OBJECT_NAME(F.OBJECT_ID))

–WHERE INDEX_ID<> 0

AND F.DATABASE_ID=DB_ID()

AND OBJECTPROPERTY(I.OBJECT_ID,”ISSYSTEMTABLE”)=0′

)

DECLARE CUR_FRAG CURSOR FOR

SELECT * FROM #TEMPFRAG

OPEN CUR_FRAG

FETCH NEXT FROM CUR_FRAG INTO

@TAB_NAME ,@IND_NAME , @FRAG , @PAGES ,@SCHEMA_NAME

WHILE @@FETCH_STATUS=0

BEGIN

IF (@IND_NAME IS NOT NULL)

BEGIN

IF (@FRAG>30 AND @PAGES>1000)

BEGIN

EXEC (‘USE [‘+@DB_NAME+’];ALTER INDEX [‘+@IND_NAME+’] ON [‘+@SCHEMA_NAME+’].[‘+@TAB_NAME +’] REBUILD ‘)

INSERT INTO MSDB.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY

VALUES (@DB_NAME,@TAB_NAME,@IND_NAME,@FRAG,@PAGES,’REBUILD’,GETDATE())

END

ELSE IF((@FRAG BETWEEN 15 AND 30 ) AND @PAGES>1000 )

BEGIN

–IF PAGE LEVEL LOCKING IS DISABLED (PLLD) THEN REBUILD

BEGIN TRY

EXEC (‘USE [‘+@DB_NAME+’];ALTER INDEX [‘+@IND_NAME+’] ON [‘+@SCHEMA_NAME+’].[‘+@TAB_NAME +’] REORGANIZE ‘)

EXEC (‘USE [‘+@DB_NAME+’];UPDATE STATISTICS [‘+@SCHEMA_NAME+’].[‘+@TAB_NAME+’] ([‘+@IND_NAME+’]) ‘ )

INSERT INTO MSDB.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY

VALUES (@DB_NAME,@TAB_NAME,@IND_NAME,@FRAG,@PAGES,’REORGANIZE & UPDATESTATS’,GETDATE())

END TRY

BEGIN CATCH

IF ERROR_NUMBER()=2552

EXEC (‘USE [‘+@DB_NAME+’];ALTER INDEX [‘+@IND_NAME+’] ON [‘+@SCHEMA_NAME+’].[‘+@TAB_NAME +’] REBUILD ‘)

INSERT INTO MSDB.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY

VALUES (@DB_NAME,@TAB_NAME,@IND_NAME,@FRAG,@PAGES,’PLLD_REBUILD’,GETDATE())

END CATCH

END

ELSE 

BEGIN 

EXEC (‘USE [‘+@DB_NAME+’];UPDATE STATISTICS [‘+@SCHEMA_NAME+’].[‘+@TAB_NAME+’] ([‘+@IND_NAME+’]) ‘  ) 

INSERT INTO MSDB.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY

VALUES (@DB_NAME,@TAB_NAME,@IND_NAME,@FRAG,@PAGES,’UPDATESTATS’,GETDATE()) 

END  

END 

ELSE

BEGIN

EXEC (‘USE [‘+@DB_NAME+’];UPDATE STATISTICS [‘+@SCHEMA_NAME+’].[‘+@TAB_NAME+’]’) 

INSERT INTO MSDB.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY 

VALUES (@DB_NAME,@TAB_NAME,’HEAP’,@FRAG,@PAGES,’UPDATESTATS’,GETDATE()) 

END

FETCH NEXT FROM CUR_FRAG INTO

@TAB_NAME ,@IND_NAME , @FRAG , @PAGES ,@SCHEMA_NAME

END

DROP TABLE #TEMPFRAG

CLOSE CUR_FRAG

DEALLOCATE CUR_FRAG

INSERT INTO MASTER.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY

SELECT * FROM MSDB.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY

END

— SELECT * FROM MASTER.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY

Sending multiple file attachments using sp_send_dbmail


— I found something interesting if you ever want to send multiple reports using sp_send_dbmail

— You have to first set the rowcount to 0 (zero).

— There can’t be a space after the semi-colon that separates the full pathnames for the reports.

———- Check it out——–

SET ROWCOUNT 0

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘<profile name>’,

@recipients = ‘recipient.1@xyz.com; recipient.2@xyz.com’,

@body = ‘Reports 1 and 2 are attached’,

@file_attachments = ‘C:\Report Folder\Report1.TXT;C:\Report Folder\Report2.TXT’,

@subject = ‘Reports 1 and 2 are attached’ ;

How to Sync all the stored procedures in between two servers


–Description: synchronize all stored procedures between two servers

—                     @ACTION = 0: Synch procedures exist in server1 but not in server2

—                     @ACTION = 1: Synch all procedures from server1 to server2

–WARNING: use this stored procedure VERY CAREFULLY!

CREATE PROCEDURE [dbo].[spSynchAllProcedures]

            @ACTION TINYINT = 0,

            @SourceServer VARCHAR(50) = NULL,

            @SourceDatabase VARCHAR(50) = NULL,

            @TargetServer VARCHAR(50) = NULL,

            @TargetDatabase VARCHAR(50) = NULL

AS

BEGIN

            SET NOCOUNT ON;

            DECLARE @STRSQL NVARCHAR(MAX);

            DECLARE @Params NVARCHAR(MAX);

            DECLARE @ProcedureName VARCHAR(50);

            –Insert to temp table since some stored procedures contains over 4000 characters

            CREATE TABLE #tblTmp(item VARCHAR(50));

            IF @ACTION = 0

                        SET @STRSQL = N’INSERT INTO #tblTmp

                                                            SELECT [name]

                                                            FROM ‘ + @SourceServer + ‘.’ + @SourceDatabase + ‘.dbo.sysobjects

                                                            WHERE xtype = ”P”

                                                            AND [name] NOT IN (SELECT [name]

                                                                                                            FROM ‘ + @TargetServer + ‘.’ + @TargetDatabase + ‘.dbo.sysobjects

                                                                                                            WHERE xtype = ”P”)’;

            ELSE IF @ACTION = 1

                        SET @STRSQL = N’INSERT INTO #tblTmp

                                                            SELECT [name]

                                                            FROM ‘ + @SourceServer + ‘.’ + @SourceDatabase + ‘.dbo.sysobjects

                                                            WHERE xtype = ”P”’;

            EXECUTE(@STRSQL);

            DECLARE Cur CURSOR FOR

                        SELECT item FROM #tblTmp

            OPEN Cur

            FETCH FROM Cur

            INTO @ProcedureName

            WHILE @@FETCH_STATUS = 0

            BEGIN

                        –PRINT @ProcedureName;              

                        EXEC dbo.spSynchStoredProcedure @ProcedureName, @SourceServer, @SourceDatabase, @TargetServer, @TargetDatabase;

                        FETCH NEXT FROM Cur

                        INTO @ProcedureName

            END

            CLOSE Cur

            DEALLOCATE Cur

            DROP TABLE #tblTmp;

END

Get the list of Orphaned users in all of the databases in SQL Server


/*************************************************
** Purpose: To return database users (for each db) orphaned from any login.
** Created By: James Howard
** Created On: 03 DEC 09
*************************************************/

–create a temp table to store the results
CREATE TABLE #temp (
DatabaseName NVARCHAR(50),
UserName NVARCHAR(50)
)
–create statement to run on each database
declare @sql nvarchar(500)
SET @sql=’select ”?” as DBName
, name AS UserName
from [?]..sysusers
where (sid is not null and sid <> 0x0)
and suser_sname(sid) is null and
(issqlrole <> 1) AND
(isapprole <> 1) AND
(name <> ”INFORMATION_SCHEMA”) AND
(name <> ”guest”) AND
(name <> ”sys”) AND
(name <> ”dbo”) AND
(name <> ”system_function_schema”)
order by name

–insert the results from each database to temp table
INSERT INTO #temp
exec SP_MSforeachDB @sql
–return results
SELECT * FROM #temp
DROP TABLE #temp

Finding SQL Server Tables that are not used since last SQL Server restart


  WITH LastActivity (ObjectID, LastAction) AS
  (
  SELECT object_id AS TableName,
last_user_seek as LastAction
  FROM sys.dm_db_index_usage_stats u
   WHERE database_id = db_id(db_name())
  
UNION

  SELECT object_id AS TableName,
last_user_scan as LastAction
  FROM sys.dm_db_index_usage_stats u
   WHERE database_id = db_id(db_name())
  
UNION

  SELECT object_id AS TableName,
last_user_lookup as LastAction
  FROM sys.dm_db_index_usage_stats u
   WHERE database_id = db_id(db_name())
  )

  SELECT OBJECT_NAME(so.object_id) AS TableName,
MAX(la.LastAction) as LastSelect
    FROM sys.objects so
    LEFT
    JOIN LastActivity la
      ON so.object_id = la.ObjectID
   WHERE so.type = ‘U’
     AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)

Database Users And Associated Database Roles


/*

SQL QUERY NAME:Retreive Database Users and Associated Database Roles

USE DESCRIPTION:Execute this query against each database to retrieve its users and thier roles

COMPATIBILITY:Works on only SQL Server 2005 Databases

AUTHOR:Larry Sumuri

DATE:20.01.2010 6:16PM

*/

1. Copy the script over to you SQL Query Window

2.Replace the name of the database, that is, ‘<databasename>’ with the name of the database to run against

3. Execute the script to view the resultset displaying that database’s users and thier associated database roles

Note: The script works on SQl Server 2005, and has not been tested against SQL Server 2008 yet.

USE <databasename>

GO

CREATE TABLE ##DBUsersAndRoles(DBUser VARCHAR(255), DBUserType VARCHAR(255), DBUserRole VARCHAR(255), DBUserRoleType VARCHAR(255))

DECLARE @DBUser VARCHAR(255)

DECLARE @DBUserType VARCHAR(255)

DECLARE @DBUserRole VARCHAR(255)

DECLARE @DBUserRoleType VARCHAR(255)

DECLARE @MemberID INT

DECLARE @RoleID INT

DECLARE memberCursor CURSOR

FAST_FORWARD

FOR

SELECT role_principal_id, member_principal_id FROM sys.database_role_members

OPEN memberCursor

FETCH NEXT FROM memberCursor INTO @RoleID, @MemberID

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @DBUser = [name], @DBUserType = type_desc

FROM sys.database_principals

WHERE principal_id = @MemberID

SELECT @DBUserRole = [name], @DBUserRoleType = type_desc

FROM sys.database_principals

WHERE principal_id = @RoleID

INSERT INTO ##DBUsersAndRoles (DBUser,DBUserType,DBUserRole,DBUserRoleType)

VALUES(@DBUser, @DBUserType, @DBUserRole, @DBUserRoleType)

FETCH NEXT FROM memberCursor INTO @RoleID, @MemberID

END

CLOSE memberCursor

DEALLOCATE memberCursor

SELECT * FROM ##DBUsersAndRoles ORDER BY DBUSER ASC

DROP TABLE ##DBUsersAndRoles

Finding Unused Objects SQL Server


\*

This Script is used to List out the objects lying in the box which were un used from the day of the sql server recycled. This will be useful to reclaim the unnecessary usage of space in the server and by this we can say the backup will consume less space.

Please copy the script into the sql box. Reveiw for a while and then execute and test it on any of the development box. Once you are confident enough please use on production box.

*/

IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#UnwantedTables]’))
drop table #UnwantedTables

IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#tablelist]’))
drop table #tablelist

create table #tablelist
(
dbname varchar(64)
,tablename varchar(256)
, test varchar(256))

Create table #UnwantedTables
(

tblname varchar(256),
Row int,
reserved varchar(32),
data varchar(16),
index_size varchar(16),
Unused varchar(16))
insert into #tablelist
exec sp_msforeachdb @command1=’USE ?;SELECT DISTINCT
dbname = db_name() ,
OBJECTNAME = OBJECT_NAME(I.OBJECT_ID)
,db_name() + ”.dbo.sp_spaceused [”+ ss.name + ”.” + OBJECT_NAME(I.OBJECT_ID) + ”]” as test
FROM
SYS.INDEXES AS I
INNER JOIN SYS.OBJECTS AS O
ON I.OBJECT_ID = O.OBJECT_ID AND O.TYPE = ”U”
inner join sys.schemas as ss on ss.schema_id = o.schema_id
LEFT JOIN (select distinct object_id from SYS.DM_DB_INDEX_USAGE_STATS) AS S
ON S.OBJECT_ID = I.OBJECT_ID
WHERE
S.OBJECT_ID IS NULL’

DECLARE Cur_UnWantedTbls CURSOR
READ_ONLY
FOR select test from #tablelist where (dbname not like ‘MASTER’
and dbname NOT LIKE ‘MSDB’ and dbname NOT LIKE ‘TEMPDB’ and dbname NOT LIKE ‘MODEL’)

DECLARE @objectName varchar(512)

OPEN Cur_UnWantedTbls

FETCH NEXT FROM Cur_UnWantedTbls INTO @objectName

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN
insert into #UnwantedTables
exec ( @objectName)
END
FETCH NEXT FROM Cur_UnWantedTbls INTO @objectName
END
CLOSE Cur_UnWantedTbls
DEALLOCATE Cur_UnWantedTbls

select distinct @@servername, DBNAME, TABLENAME, LEFT(RESERVED, LEN(RESERVED)-2) ReservedInKB,
(select create_Date from sys.databases where name = ‘tempdb’) RecycledTime
from #tablelist inner join #UnwantedTables on tblname = tablename

where reserved <> ‘0 KB’

Performance – Get I/O information of SQL Server


use master

go

if exists

(select * from master.dbo.sysobjects where id = object_id(‘dbo.sp_whoio’) )

 Drop Procedure dbo.sp_whoio go

/*====================================================================

 https://www.mcpvirtualbusinesscard.com/VBCServer/Mircea/card ======================================================================*/

Create Procedure dbo.sp_whoio

@dbname sysname = null,

@loginame sysname = null as set nocount on declare @retcode int ,

@sidlow varbinary(85) ,@sidhigh varbinary(85) ,

@sid1 varbinary(85) ,@spidlow int ,

@spidhigh int ,@seldbid varchar(10) ,

@charMaxLenLoginName varchar(24) ,

@charMaxLenDBName varchar(24) ,

@charMaxLenCPUTime varchar(10) ,

@charMaxLenDiskIODelta varchar(10) ,

@charMaxLenDiskIO varchar(10) ,

@charMaxLenHostName varchar(24) ,

@charMaxLenProgramName varchar(10) ,

@charMaxLenLastBatch varchar(10) ,

@charMaxLenCommand varchar(10) ,

@charsidlow varchar(85) ,

@charsidhigh varchar(85) ,

@charspidlow varchar(11) ,

@charspidhigh varchar(11) ,

@command varchar(8000)

— set defaults set @retcode = 0

set @sidlow = convert(varbinary(85),

(replicate(char(0), 85))) set

@sidhigh = convert(varbinary(85), (replicate(char(1), 85)))

set @spidlow = 0 set @spidhigh = 32767

if (@dbname is not null) set

@seldbid = cast((select top 1 dbid from master.dbo.sysdatabases where name like ‘%’+@dbname+’%’) as varchar(10)) else

 set @seldbid = ‘0’ if (@loginame is null)

— Simple default to all LoginNames.

GOTO LABEL_PARAM select @sid1 = null

if exists

(select * from sys.syslogins where loginname = @loginame) select @sid1 = sid from sys.syslogins where loginname = @loginame if (@sid1 IS NOT NULL)

— The parameter is a recognized login name.

begin select @sidlow = suser_sid(@loginame)

@sidhigh = suser_sid(@loginame)

GOTO LABEL_PARAM

end if (lower(@loginame collate Latin1_General_CI_AS) IN (‘active’))

–Special action, not sleeping.

begin select @loginame = lower(@loginame collate Latin1_General_CI_AS) GOTO LABEL_PARAM

end

if

 (patindex (‘%[^0-9]%’ , isnull(@loginame,’z’)) = 0)

— Is a number.

begin

select @spidlow = convert(int, @loginame) ,@spidhigh = convert(int, @loginame)

GOTO LABEL_PARAM

end raiserror(15007,-1,-1,@loginame)

 select @retcode = 1 GOTO LABEL_RETURN LABEL_PARAM:

— Getting data over a time window to allow the io_delta metric calculation

if object_id(‘tempdb.dbo.#io1’)

is not null

drop table #io1

if object_id(‘tempdb.dbo.#io2′) is not null

drop table #io2

select spid, physical_io into #io1

from master.dbo.sysprocesses with (nolock)

order by

physical_io desc

waitfor delay ’00:00:03’

select spid, physical_io into #io2

from master.dbo.sysprocesses with (nolock)

order by physical_io desc

——————– Capture consistent sysprocesses. ——————-

select sp.spid ,status ,sid ,hostname ,program_name ,cmd ,cpu ,sp.physical_io ,i2.physical_io-i1.physical_io

as ‘io_delta’ ,

blocked ,

dbid ,convert(sysname, rtrim(loginame)) as loginname ,

sp.spid as ‘spid_sort’ , substring( convert(varchar,last_batch,111) ,6 ,5 ) + ‘ ‘ + substring( convert(varchar,last_batch,113) ,13 ,8 )

as

 ‘last_batch_char’ into #tb1_sysprocesses

from #io2

 i2 join #io1 i1 on i2.spid = i1.spid join master.dbo.sysprocesses

sp with (nolock) on sp.spid = i2.spid where i2.physical_io-i1.physical_io > 0 if @@error <> 0

begin

select @retcode = @@error GOTO LABEL_RETURN

end

if (@loginame in (‘active’)) delete #tb1_sysprocesses where lower(status) = ‘sleeping’

and

upper(cmd) in ( ‘AWAITING COMMAND’ ,’LAZY WRITER’ ,’CHECKPOINT SLEEP’ ) and blocked = 0 and dbid <> @seldbid

–Prepare to dynamically optimize column widths.

select @charsidlow = convert(varchar(85),@sidlow) ,

@charsidhigh = convert(varchar(85),@sidhigh) ,

@charspidlow = convert(varchar,@spidlow) ,

@charspidhigh = convert(varchar,@spidhigh)

select @charMaxLenLoginName = convert( varchar ,isnull( max( datalength(loginname)) ,16) ) ,

@charMaxLenDBName = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,20) ) ,

@charMaxLenCPUTime = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,10) ) ,

@charMaxLenDiskIO = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6) ) ,

@charMaxLenDiskIODelta = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),io_delta)))) ,6) ) ,

@charMaxLenCommand = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7) ) ,

@charMaxLenHostName = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,16) ) ,

@charMaxLenProgramName = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11) ) ,

@charMaxLenLastBatch = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9) )

from #tb1_sysprocesses where spid >= @spidlow

and

spid <= @spidhigh

— Output the report.

set @command = ‘ SET nocount off

select SPID = convert(char(5),spid) ,

Status = CASE lower(status) When ”sleeping” Then lower(status) Else upper(status) END ,

Login = substring(loginname,1,’ + @charMaxLenLoginName + ‘) ,

HostName = CASE hostname When Null Then ” .” When ” ” Then ” .” Else substring(hostname,1,’ + @charMaxLenHostName + ‘) END ,BlkBy = CASE isnull(convert(char(5),blocked),”0”) When ”0” Then ” .” Else isnull(convert(char(5),blocked),”0”)

END , DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,’ + @charMaxLenDBName + ‘) ,Command = substring(cmd,1,’ + @charMaxLenCommand + ‘) ,CPUTime = substring(convert(varchar,cpu),1,’ + @charMaxLenCPUTime + ‘) ,

DiskIO_Total = substring(convert(varchar,physical_io),1,’ + @charMaxLenDiskIO + ‘) ,DiskIO_Delta = substring(convert(varchar,io_delta),1,’ + @charMaxLenDiskIODelta + ‘) ,LastBatch = substring(last_batch_char,1,’ + @charMaxLenLastBatch + ‘) ,

ProgramName = substring(program_name,1,’ + @charMaxLenProgramName + ‘) ,SPID = convert(char(5),spid) — Handy extra for right-scrolling users. from #tb1_sysprocesses where spid > 50

— filter out system spids

and spid <> @@spid

— and current process spid

and spid >= ‘ + @charspidlow + ‘ and spid <= ‘ + @charspidhigh + ‘ ‘ if @seldbid > 0

set @command = @command + ‘ and dbid = ‘ + @seldbid + ‘ ‘ set @command = @command + ‘ order by cast(io_delta as int) desc,

cast(physical_io as int) desc SET nocount on ‘ exec (@command) LABEL_RETURN: if object_id(‘tempdb..#tb1_sysprocesses’) is not null drop table #tb1_sysprocesses if object_id(‘tempdb.dbo.#io1’)

is not null

drop table #io1

if object_id(‘tempdb.dbo.#io2’)

is not null

drop table #io2 return @retcode

— sp_whoio

go

if exists (select * from sysobjects where id = object_id(‘dbo.sp_whoio’) and sysstat & 0xf = 4)

grant

exec on dbo.sp_whoio

to

public

go

Enable and Disable all the Triggers on all the tables in SQL Server 2005


Create PROCEDURE TRIGGEREnable (@action varchar(50))
AS
/**
* @Author: Tsepo D. Mohlapo
* @Date: 2010 April 14
* @Description: Disable / Enable All Triggers on all tables
*
**/
IF (@action = ‘Enable’ OR @action = ‘Disable’)
 BEGIN
DECLARE @Tables TABLE(
  primary_key INT IDENTITY(1,1) NOT NULL,
  schema_name NVARCHAR(100),
  table_name NVARCHAR(100)
  
 )

 INSERT INTO @Tables
 SELECT DISTINCT
   S.name as [Schema name],
   object_name(T.object_id) AS [Table name]
  FROM
   sys.tables T
   LEFT JOIN sys.schemas S ON (S.schema_id = T.schema_id)
   
       
  WHERE
   T.object_id is not null

 DECLARE @l_count INT
 DECLARE @row_count INT

 SET @l_count = ISNULL((SELECT COUNT(*) FROM @Tables),0)
 SET @row_count = 1

 DECLARE @schema_name VARCHAR(100)
 DECLARE @table_name VARCHAR(100)
 DECLARE @sql NVARCHAR(1000)

 WHILE @l_count > 0 AND @row_count <= @l_count
 BEGIN
   
  
   SELECT
    @schema_name = schema_name,
    @table_name = table_name
   FROM
    @Tables
   WHERE
    primary_key = @row_count

  
 
 
 
 SET @sql = ‘
ALTER TABLE  [‘+ @schema_name+ ‘].[‘ + @table_name + ‘]  ‘ +  @action + ‘ TRIGGER ALL


 
 Print @sql –Can comment out

 EXEC sp_executesql @sql

SET @row_count =  @row_count + 1

 END
END

Backup information and database status


 

SELECT t.name as [DB Name],
  t.user_access_desc as [Access State], 
  t.state_desc as [Online/Offline], 
  ((SELECT (CASE t.is_in_standby WHEN 0 THEN ‘No’ WHEN 1 THEN ‘Yes’ ELSE ‘Other’ END))) as [In Standby],
  (COALESCE(Convert(datetime, MAX(u.backup_finish_date), 101),’Not Yet Taken’)) as [Last BackUp Taken],
  (((COALESCE(Convert(real(256), MAX(u.backup_size), 101),’NA’))/1024)/1024) as [Backup Size in MB],
  (COALESCE(Convert(varchar(10),MAX(datediff(d, getdate(), u.backup_finish_date))),101)) as [Days since Backup],
  (COALESCE(Convert(varchar(12), MAX(u.user_name), 101),’NA’)) as [User Name]
  
    FROM SYS.DATABASES t
    INNER JOIN msdb.dbo.BACKUPSET u
     ON t.name = u.database_name
   GROUP BY t.Name,t.is_in_standby, t.user_access_desc, t.state_desc
   ORDER BY t.Name

Tracking FRAGMENTATION in tables in SQL Server Database


Here’s a diagnostic table and stored procedure to track the fragmentation of large tables within databases. The user passes the database name and the table name as parameters to the SP. The SP can be set up to run as a scheduled job every few hours to produce a historical trend of fragmentation.

While DBCC SHOWCONTIG produced the data I needed, it certainly wasn’t in a friendly form for storage. I use the executable file – OSQL.exe – to process the DBCC command and save its results to a temporary table. The Identity column (Record_Id) allows me to differentiate between the rows so I can apply specific parsing on the target row/content. I’ve augmented the data from SHOWCONTIG to include the Rowcount from the specified table.

_____________________________________________________________________________________________

use Master
go

if exists (select * from sysobjects where id = object_id(N'[dbo].[dg_TableFragmentation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dg_TableFragmentation]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[tb_FragTrack_log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tb_FragTrack_log]
GO

CREATE TABLE [dbo].[tb_FragTrack_log] (
	[Record_id] [int] IDENTITY (1, 1) NOT NULL ,
	[Date_time] [datetime] NOT NULL ,
	[DBName] [varchar] (50) NOT NULL ,
	[Tablename] [varchar] (50) NOT NULL ,
	[TableRows] [int] NOT NULL ,
	[PagesScnd] [int] NOT NULL ,
	[ExtentsScnd] [int] NOT NULL ,
	[ExtentSws] [int] NOT NULL ,
	[AvgPagesExt] [real] NOT NULL ,
	[ScanDensity] [real] NOT NULL ,
	[ExtRatio] [varchar] (20) NOT NULL ,
	[LogicalScnFrag] [real] NOT NULL ,
	[ExtScanFrag] [real] NOT NULL ,
	[AvgByteFree] [real] NOT NULL ,
	[AvgPageDens] [real] NOT NULL
) ON [PRIMARY]
GO

CREATE Procedure dg_TableFragmentation @db varchar(50), @table varchar(50)

as

/** 	This Procedure will Run the DBCC SHOWCONTIG command against the table specified in the database specified.
	The data from the DBCC Command is augmented with the rowcount for the target table and saved with a Date_time.
	The goal is to produce a history of fragmenation change over time on a per table basis.

	Ralph Clark - Plantware,Inc 5/2/02
**/

Set NoCount ON

Declare @rows int,
	@start int,
	@end int,
	@ps int,
	@es int,
	@esw int,
	@ape real,
	@sd real,
	@rat varchar(10),
	@lsf real,
	@esf real,
	@bfp real,
	@apd real,
	@str nvarchar(500)

--get rows in target table
Set @str = 'SELECT @rows = rows FROM ' + @db + '.dbo.sysindexes WHERE id = OBJECT_ID(' + CHAR(39) + @db +'.dbo.' + @table + Char(39) + ') AND indid < 2'

Exec sp_ExecuteSql @str, N'@rows Int Out', @rows Out

--get fragmenation details
CREATE TABLE #cnt (Record_id Int IDENTITY (1, 1),  info VARCHAR(100))

DECLARE @tbl VARCHAR(15), @parm VARCHAR(255)

SELECT @tbl = CONVERT(VARCHAR(50), OBJECT_ID((@db + '..'+ @table)))

SELECT @parm = 'OSQL /E /Q"DBCC SHOWCONTIG(' + @tbl + ')" /d ' + @db

INSERT #cnt EXEC master..xp_cmdshell @parm

select @start = CHARINDEX(': ', info), @end =  Len(info) from #cnt where record_id = 4

select @ps = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+2), (@end - (@start+1)))))) from #cnt where record_id = 4

select @start = CHARINDEX(': ', info), @end =  Len(info) from #cnt where record_id = 5

select @es = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+2), (@end - (@start+1)))))) from #cnt where record_id = 5

select @start = CHARINDEX(': ', info), @end =  Len(info) from #cnt where record_id = 6

select @esw = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+2), (@end - (@start+1)))))) from #cnt where record_id = 6

select @start = CHARINDEX(': ', info), @end =  Len(info) from #cnt where record_id = 7

select @ape = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+2), (@end - (@start+1)))))) from #cnt where record_id = 7

select @start = CHARINDEX(': ', info), @end =  CHARINDEX('%', info) from #cnt where record_id = 8

select @sd = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+1), (@end - (@start+1)))))) from #cnt where record_id = 8

select @start = CHARINDEX('% [', info), @end =  CHARINDEX(']', info, @start) from #cnt where record_id = 8

select @rat = Ltrim(Rtrim(Substring(Info, (@start+1), (@end - (@start))))) from #cnt where record_id = 8

select @start = CHARINDEX(': ', info), @end =  CHARINDEX('%', info) from #cnt where record_id = 9

select @lsf = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+1), (@end - (@start+1)))))) from #cnt where record_id = 9

select @start = CHARINDEX(': ', info), @end =  CHARINDEX('%', info) from #cnt where record_id = 10

select @esf = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+1), (@end - (@start+1)))))) from #cnt where record_id = 10

select @start = CHARINDEX(': ', info), @end =  Len(info) from #cnt where record_id = 11

select @bfp = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+2), (@end - (@start+1)))))) from #cnt where record_id = 11

select @start = CHARINDEX(': ', info), @end =  CHARINDEX('%', info) from #cnt where record_id = 12

select @apd = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+1), (@end - (@start+1)))))) from #cnt where record_id = 12

Drop table #cnt

--save results

Insert PlantQueDG.dbo.tb_FragTrack_log
	(Date_time,
	DBName,
	Tablename,
	TableRows,
	PagesScnd,
	ExtentSws,
	ExtentsScnd,
	AvgPagesExt,
	ScanDensity,
	ExtRatio,
	LogicalScnFrag,
	ExtScanFrag,
	AvgByteFree,
	AvgPageDens)
values
	(getdate(),
	@db,
	@table,
	@rows,
	@ps,
	@es,
	@esw,
	@ape,
	@sd,
	@rat,
	@lsf,
	@esf,
	@bfp,
	@apd)

return
GO
__________________________________________________________________________________________________________

How to ReBoot all the machines at a time.


Pre-requisite:

a. Windows 2003 machine
b. Login or the scheduled job that runs this reboot.bat should have admin access to all the machines

Steps:

1. Step1: Create D:\scripts\reboot\serverlist.txt and the list all the machines that you want to reboot.

Example:
MyServer
SQL1Prod
SQL1QA
DOTNET1a

2. Step2: Create D:\scripts\reboot\reboot.bat 

REM Objective: TO reboot all the 
  machines listed in D:\script\reboot\serverlist.txt
REM Created by : MAK
REM Created Date: Jan 5, 2005
REM Save this file as 
  D:\scripts\reboot\reboot.bat
for /f "tokens=1,2,3" %%i in 
 (D:\scripts\reboot\serverlist.txt) 
 do c:\windows\system32\shutdown.exe /m \\%%i /r /f /c 
 "You machine is going to be restarted in 10 minutes" /t 600

3. Step3: Create D:\scripts\reboot\Abortreboot.bat

REM Objective: TO abort reboot on all the machine listed in 
  D:\script\reboot\serverlist.txt
REM Created by : MAK
REM Created Date: Jan 5, 2005
REM Save this file as D:\scripts\reboot\abortreboot.bat
for /f "tokens=1,2,3" %%i in 
  (D:\scripts\reboot\serverlist.txt) 
  do c:\windows\system32\shutdown.exe /a /m \\%%i 
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Code for Reboot.bat
----------------------
REM Objective: TO reboot all the machine listed in D:\script\reboot\serverlist.txt
REM Created by : MAK
REM Created Date: Jan 5, 2005
REM Save this file as D:\scripts\reboot\reboot.bat
for /f "tokens=1,2,3" %%i in (D:\scripts\reboot\serverlist.txt) do c:\windows\system32\shutdown.exe /m \\%%i /r /f /c "You machine is going to be restarted in 10 minutes" /t 600

Code for AbortReboot.bat
-------------------------
REM Objective: TO abort reboot on all the machine listed in D:\script\reboot\serverlist.txt
REM Created by : MAK
REM Created Date: Jan 5, 2005
REM Save this file as D:\scripts\reboot\abortreboot.bat
for /f "tokens=1,2,3" %%i in (D:\scripts\reboot\serverlist.txt) do c:\windows\system32\shutdown.exe /a /m \\%%i

How to Import SQL Server Errorlogs into TABLE


CREATE PROC sp_import_errorlog
(
 @log_name sysname,
 @log_number int = 0,
 @overwrite bit = 0
)
AS
/*************************************************************************************************
Purpose: import SQL Server error log into a table and query 
Tested on:  SQL Server 2000

Limitation:  With error messages spanning more than one line only the first line is included in the table

Example 1:  To import the current error log to table myerrorlog
  EXEC sp_import_errorlog ‘myerrorlog’

Example 2:  To import the current error log to table myerrorlog, and overwrite the table
  ‘myerrorlog’ if it already exists
  EXEC sp_import_errorlog ‘myerrorlog’, @overwrite = 1

Example 3:  To import the previous error log to table myerrorlog
  EXEC sp_import_errorlog ‘myerrorlog’, 1

Example 4:  To import the second previous error log to table myerrorlog
  EXEC sp_import_errorlog ‘myerrorlog’, 2

*************************************************************************************************/

BEGIN
 SET NOCOUNT ON
 
 DECLARE @sql varchar(500) –Holds to SQL needed to create columns from error log

 IF (SELECT OBJECT_ID(@log_name,’U’)) IS NOT NULL
  BEGIN
   IF @overwrite = 0
    BEGIN
     RAISERROR(‘Table already exists. Specify another name or pass 1 to @overwrite parameter’,18,1)
     RETURN -1
    END
   ELSE
    BEGIN
     EXEC(‘DROP TABLE ‘ + @log_name)
    END
  END

 
 –Temp table to hold the output of sp_readerrorlog
 CREATE TABLE #errlog
 (
  err varchar(1000),
  controw tinyint
 )

 –Populating the temp table using sp_readerrorlog
 INSERT #errlog
 EXEC sp_readerrorlog @log_number

 –This will remove the header from the errolog
 SET ROWCOUNT 4
 DELETE #errlog
 SET ROWCOUNT 0

 
 SET @sql =  ‘SELECT
    CONVERT(DATETIME,LEFT(err,23)) [Date],
    SUBSTRING(err,24,10) [spid],
    RIGHT(err,LEN(err) – 33) [Message],
    controw
   INTO ‘ + QUOTENAME(@log_name) +
   ‘ FROM #errlog ‘ +
   ‘WHERE controw = 0’
 
 –Creates the table with the columns Date, spid, message and controw
 EXEC (@sql) 
 
 –Dropping the temporary table
 DROP TABLE #errlog
 
 SET NOCOUNT OFF
PRINT ‘Error log successfully imported to table: ‘ + @log_name
END

How to view all sql server instances from the network.


Call Start->Run type cmd and click enter (start command line prompt). Now depending on version of installed sql server client tools run either from the following commands

sqlcmd -L

osql -L

isql -L

Try them in the given order. sqlcmd is the latest version of command line sql server utility. All of this utilities support -L param which lists all sql server instances from the network. However, isql shows only sql servers of 7.0 version and below, osql – only sql server 2000 and below and sqlcmd shows all sql server instances from the network. So to view all sql server in the network you better use sqlcmd -L command.

Script to kill all processes using database


The below script will “kill all the proccesses that use database ” which name in set to @dbname variable

declare @dbname sysname

set @dbname = ‘dbname’ — type here the name of database you want to kill processes of

declare

     @spid INT

    ,@Query VARCHAR(250)

    ,@processes CURSOR

SET @processes = CURSOR FOR

select spid

from master..sysprocesses

where db_name(dbid) = @dbname

open @processes

fetch next from @processes into @spid

while @@FETCH_STATUS = 0

begin

    set @query = ‘kill ‘ + cast(@spid as varchar(10))

    exec(@query)

    fetch next from @processes into @spid

end

close @processes

deallocate @processes

GO

Dropping Views, Procedures and Fucntions from a Database at a time.


Take into account that this script just generates the script to delete objects and that it considers only default schema of objects. You can easily modify script to use different schemas.

SET NOCOUNT ON

declare sps cursor for

select name

from sysobjects

where type = ‘P’

  and objectproperty(id, ‘IsProcedure’) = 1

  and objectproperty(id, ‘IsReplProc’) = 0

  and objectproperty(id, ‘IsMSShipped’) = 0

open sps

declare @spname varchar(100)

fetch next from sps into @spname

while @@fetch_status = 0

begin

print (‘drop procedure ‘ + @spname + ‘;’)

fetch next from sps into @spname

end

close sps

deallocate sps

declare funcs cursor for

select name

from sysobjects

where type in (‘TF’, ‘IF’, ‘FN’)

  and objectproperty(id, ‘IsMSShipped’) = 0

open funcs

declare @funcname varchar(100)

fetch next from funcs into @funcname

while @@fetch_status = 0

begin

print(‘drop function ‘ + @funcname + ‘;’)

fetch next from funcs into @funcname

end

close funcs

deallocate funcs

declare vw cursor for

select name

from sysobjects

where type in (‘V’)

  and category = 0

  and [name] not like ‘sys%’

  and objectproperty(id, ‘IsMSShipped’) = 0

open vw

declare @viewname varchar(100)

fetch next from vw into @viewname

while @@fetch_status = 0

begin

set @viewname = QUOTENAME(@viewname)

print(‘drop view ‘ + @viewname + ‘;’)

fetch next from vw into @viewname

end

close vw

deallocate vw

Delete huge data from a table without Transaction Log Full


Suggested approach resolves problem of transaction log growth but it does not comply with consistancy rule. Delete is not executed as a single transaction (otherwise we will have problem with transaction log). Database recovery model should be set to single before deletion start. And here is the script

SET NOCOUNT ON

SET ROWCOUNT 100000

declare @r int

set @r = 10

WHILE @r > 0

begin

delete ord

from  orders w (nolock)

right outer join orderdetails ord on ord.ID = w.DetailID

where w.xpitordernumber is null

  and conditionalfield = 1

set @r = @@ROWCOUNT

end

  So the table orders will be purged by 100k rows batches wihout holding huje amount of locks,transaction log growth etc. Microsoft says that set rowcount won’t be supported for data modification operations (such as delete, update, insert) however it still works on my SQL Server 2008 RTM.

SQL Server Database consuming huge disk Space. How to know space distribution by table?


After MS SQL Server 2005 you may use “sys.dm_db_partition_stats” DMV to find out this information

select  OBJECT_NAME(object_id) as objname

        , SUM (reserved_page_count) * 8192/ 1024 as reserved_kb

        , SUM(used_page_count) * 8192 / 1024 as used_kb

from sys.dm_db_partition_stats

group by OBJECT_NAME(object_id)

order by reserved_kb desc

 

Prior to sql server 2005 (for sql server 2000)  you may use following

if object_id (‘tempdb..#table’) is not null

  drop table #table

create table #table (

  name varchar(8000)

, rows int, reserved varchar(50)

, data varchar(50)

, index_size varchar(50)

, unused varchar(50))

insert into #table

exec sp_msforeachtable ‘sp_spaceused ”?”’

select name, rows, reserved = REPLACE (reserved, ‘KB’,”)

, data = REPLACE (data, ‘KB’,”),index_size = REPLACE (index_size, ‘KB’,”)

,unused = REPLACE (unused, ‘KB’,”)

from #table

order by convert ( int , REPLACE (reserved, ‘KB’,”)) desc

drop table #table

Get count of tables along with count of rows in all tables.


Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to list accurate and approximate (fast) rowcounts for all the tables in the database.

— SQL Server count all rows in all tables – sql server rowcount all tables

DECLARE  @CountStats  TABLE(

SchemaName SYSNAME,

TableName  SYSNAME,

RowsCount  INT

)

INSERT @CountStats

EXEC sp_msForEachTable

‘SELECT PARSENAME(”?”, 2), PARSENAME(”?”, 1), COUNT(*) FROM ?’

SELECT   *

FROM     @CountStats

ORDER BY RowsCount DESC

/* Partial results

SchemaName  TableName                     RowsCount

Sales       SalesOrderDetail              121317

Production  TransactionHistory            113443

Production  TransactionHistoryArchive     89253

Production  WorkOrder                     72591

*/
———-

— APPROXIMATE – very quick

— SQL count rows in all tables

USE AdventureWorks2008;

DECLARE @SQL NVARCHAR(255)

SET @SQL = ‘DBCC UPDATEUSAGE (‘ + DB_NAME() + ‘)’

EXEC sp_executeSQL @SQL

SELECT Schema_name(t.schema_id) AS SchemaName,

t.name                   AS TableName,

i.rows                   as [Rows]

FROM   sys.tables AS t

INNER JOIN sys.sysindexes AS i

ON t.object_id = i.id

AND i.indid < 2

ORDER BY [Rows] DESC

GO

/* Partial results

SchemaName  TableName                     Rows

Sales       SalesOrderDetail              121317

Production  TransactionHistory            113443

Production  TransactionHistoryArchive     89253

Production  WorkOrder                     72591

Production  WorkOrderRouting              67131

*/
———-

— SQL Server 2000 – approximate quick count

USE Northwind;

SELECT      TableName = o.name,

[Rows] = replace(convert(VARCHAR,convert(MONEY,max(i.rows)),1), ‘.00’,”)

FROM sysobjects o

INNER JOIN sysindexes i

ON o.id = i.id

WHERE xtype = ‘u’

AND OBJECTPROPERTY(o.id,N’IsUserTable’) = 1

GROUP BY o.name

ORDER BY max(i.rows) DESC

GO

/* Partial resutls

TableName               Rows

Order Details           2,155

Orders                  830

Customers               91

Products                77

Territories             53

EmployeeTerritories     49

*/
———-

— ACCURATE COUNT with cursor

— SQL Server find rows count in all tables and views – select count(*)

USE AdventureWorks2008;

DECLARE  @SchemaName SYSNAME,

@TableName  SYSNAME,

@TableType  varchar(12)

DECLARE  @SQL NVARCHAR(MAX)

CREATE TABLE #Population (

TableName  VARCHAR(256),

TableType varchar(12),

[Population] INT);

DECLARE curTablesAndViews CURSOR FAST_FORWARD FOR

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE

FROM   INFORMATION_SCHEMA.TABLES

OPEN curTablesAndViews

FETCH NEXT FROM curTablesAndViews

INTO @SchemaName,

@TableName,

@TableType

WHILE (@@FETCH_STATUS = 0)

BEGIN

SELECT @SQL = ‘INSERT #Population SELECT ”’ +

@SchemaName + ‘.’ + @TableName +

”’,”’+ @TableType+”’, COUNT(*) as Population ‘ +

‘FROM [‘ + @SchemaName + ‘].[‘ + @TableName + ‘]’

PRINT @SQL — debugging

EXEC SP_EXECUTESQL   @SQL

FETCH NEXT FROM curTablesAndViews

INTO @SchemaName,

@TableName,

@TableType

END

CLOSE curTablesAndViews

DEALLOCATE curTablesAndViews

— Return the list of rows counts

SELECT   *

FROM     #Population

ORDER BY [Population] DESC

GO

DROP TABLE #Population

/*

TableName                     TableType   Population

…….

Person.BusinessEntityAddress  BASE TABLE  19614

Person.Address                BASE TABLE  19614

Sales.CreditCard              BASE TABLE  19118

Sales.PersonCreditCard        BASE TABLE  19118

Sales.vIndividualCustomer     VIEW        18508

…….

*/
———-

— SQL Server 2005/2008 – approximate, very quick

DECLARE @SQL NVARCHAR(255)

SET @SQL = ‘DBCC UPDATEUSAGE (‘ + DB_NAME() + ‘)’

EXEC sp_executeSQL @SQL

SELECT schema_name(o.schema_id)    AS SchemaName,

object_name(o.object_id)    AS TableName,

SUM(row_count)              AS [Count]

FROM     sys.dm_db_partition_stats ps

INNER JOIN sys.objects o

ON o.object_id = ps.object_id

WHERE    index_id < 2

AND TYPE = ‘U’

GROUP BY o.schema_id,  o.object_id

ORDER BY [Count] desc

/*

SchemaName  TableName                     Count

Production  TransactionHistory            14520704

Sales       SalesOrderDetail              121317

Production  TransactionHistoryArchive     89253

Production  WorkOrder                     72591

*/

———-

Script to Backup all databases in SQL Server


Backup all the databases in SQL Server:

 

DECLARE @name VARCHAR(50-- database name 
DECLARE @path VARCHAR(256-- path for backup files 
DECLARE @fileName VARCHAR(256-- filename for backup 
DECLARE @fileDate VARCHAR(20-- used for file name

SET @path 'C:\Backup\' 

SELECT @fileDate CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb'

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS 0  
BEGIN  
       SET @fileName @path @name '_' @fileDate '.BAK' 
       BACKUP DATABASE @name TO DISK = @fileName 

       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

Identify mostly used Indexes in SQL Server


To identify the most used indexes in your DB run the following query. This will help you to identify whether or not your indexes are useful and used.

declare @dbid int
–To get Datbase ID
set @dbid = db_id()

select
db_name(d.database_id) database_name
,object_name(d.object_id) object_name
,s.name index_name,
c.index_columns
,d.*
from sys.dm_db_index_usage_stats d
inner join sys.indexes s
on d.object_id = s.object_id
and d.index_id = s.index_id
left outer join
(select distinct object_id, index_id,
stuff((SELECT ‘,’+col_name(object_id,column_id ) as ‘data()’ FROM sys.index_columns t2 where t1.object_id =t2.object_id and t1.index_id = t2.index_id FOR XML PATH (”)),1,1,”)
as ‘index_columns’ FROM sys.index_columns t1 ) c on
c.index_id = s.index_id and c.object_id = s.object_id
where database_id = @dbid
and s.type_desc = ‘NONCLUSTERED’
and objectproperty(d.object_id, ‘IsIndexable’) = 1
order by
(user_seeks+user_scans+user_lookups+system_seeks+system_scans+system_lookups) desc

Query to get size of all the databases in SQL Server


Script to get size of all the databases in SQL Server Instance

/*– SQL Server 2000 only.
— Display the Database ID, Database Name, Logical File Name,
— MB Size on Disk, GB Size on Disk and Physical File Name
— for all databases in this instance.

*/
use master;
go

select
db.[dbid] as ‘DB ID’
,db.[name] as ‘Database Name’
,af.[name] as ‘Logical Name’
–,af.[size] as ‘File Size (in 8-kilobyte (KB) pages)’
,(((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) as ‘File Size (MB)’
,((((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) /1024) as ‘File Size (GB)’
,af.[filename] as ‘Physical Name’
from sysdatabases db
inner join sysaltfiles af
on db.dbid = af.dbid
where [fileid] in (1,2);

/*– SQL Server 2005 only.
— Display the Database ID, Database Name, Logical File Name,
— MB Size on Disk, GB Size on Disk and Physical File Name
— for all databases in this instance.

*/
use master;
go

select
db.[dbid] as ‘DB ID’
,db.[name] as ‘Database Name’
,af.[name] as ‘Logical Name’
–,af.[size] as ‘File Size (in 8-kilobyte (KB) pages)’
,(((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) as ‘File Size (MB)’
,((((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) /1024) as ‘File Size (GB)’
,af.[filename] as ‘Physical Name’
from sys.sysdatabases db
inner join sys.sysaltfiles af
on db.dbid = af.dbid
where [fileid] in (1,2);

%d bloggers like this: