1
DBA Queries
1.1
DB Details (SQLServerName,
Machine, IP, node, DBName)
SELECT
@@SERVERNAME server
,SERVERPROPERTY(N'MachineName') MachineName
, CONNECTIONPROPERTY('local_net_address') AS
local_net_address
,SERVERPROPERTY('ComputerNamePhysicalNetBios') as 'Is_Current_Owner' --node details
, name
FROM
master.dbo.sysdatabases
1.2
DB_Users
Select
SERVERPROPERTY(N'MachineName') MachineName
,CONNECTIONPROPERTY('local_net_address') AS
local_net_address
,name as DBuser
FROM master.sys.server_principals
1.3
DB Jobs
Select
@@SERVERNAME server
, CONNECTIONPROPERTY('local_net_address') AS
local_net_address
,name [JobName]
,enabled
from [msdb].[dbo].[sysjobs] AS [sJOB]
1.4
DB Objects with Object Type
SELECT
o.[name] objName, i.[name] as ItemName,
i.[index_id], f.[name] FileGroup,
ObjType =
CASE o.type
WHEN 'AF' THEN 'Aggregate function (CLR)'
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT
constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'FS' THEN 'Assembly (CLR)
scalar-function'
WHEN 'FT' THEN 'Assembly (CLR) table-valued
function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'IT' THEN 'Internal table'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'PC' THEN 'Assembly (CLR)
stored-procedure'
WHEN 'PK' THEN 'PRIMARY KEY constraint (type
is K)'
WHEN 'RF' THEN 'Replication filter stored
procedure'
WHEN 'S' THEN 'System table'
WHEN 'SN' THEN 'Synonym'
WHEN 'SQ' THEN 'Service queue'
WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'SQL DML Trigger'
WHEN 'TT' THEN 'Table type'
WHEN 'U' THEN 'User table'
WHEN 'UQ' THEN 'UNIQUE constraint (type is
K)'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE 'NotKnown'
END
FROM sys.indexes
i
INNER JOIN sys.filegroups
f
ON
i.data_space_id = f.data_space_id
INNER JOIN
sys.all_objects o
ON i.[object_id]
= o.[object_id] WHERE i.data_space_id = f.data_space_id
--AND o.type =
'U' -- User Created Tables
1.5
DB Name and Size
EXEC
master.dbo.sp_databases;
GO
1.5.1
DB each file Name and Size in
MB
select
@@SERVERNAME as Server, smf.database_id, sd.name, smf.physical_name, type_desc,
CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) ) AS
[Size in MB]
from sys.master_files smf, sys.sysdatabases sd
where smf.database_id = sd.dbid and
database_id > 4
group by smf.database_id, sd.name, smf.physical_name, type_desc
1.6
Backup
1.6.1
List all backups happened previous
week (we can change the date)
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM
msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
1.6.2
Most Recent Database Backup for
Each Database
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM
msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
ORDER BY
msdb.dbo.backupset.database_name
1.6.3
Databases Missing a Data (aka
Full) Back-Up Within Past 24 Hours
-------------------------------------------------------------------------------------------
--Databases
Missing a Data (aka Full) Back-Up Within Past 24 Hours
-------------------------------------------------------------------------------------------
--Databases
with data backup over 24 hours old
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]
FROM
msdb.dbo.backupset
WHERE
msdb.dbo.backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
HAVING
(MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))
UNION
--Databases
without any backup history
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
master.sys.sysdatabases.NAME AS database_name,
NULL AS [Last Data Backup Date],
9999 AS [Backup Age (Hours)]
FROM
master.sys.sysdatabases
LEFT JOIN msdb.dbo.backupset ON master.sys.sysdatabases.name = msdb.dbo.backupset.database_name
WHERE
msdb.dbo.backupset.database_name IS NULL
AND master.sys.sysdatabases.name <> 'tempdb'
ORDER BY
msdb.dbo.backupset.database_name
2
Developer Queries
