SQL Server Administrator Cheatsheet

I don’t consider myself a SQL Server expert so here’s a SQL Server Administrator Cheatsheet of queries for managing SQL Server.

Query all Database Files

List all the Data and Log files for your database with the file sizes.

select d.name, m.name, m.physical_name, m.type_desc, m.size
from sys.master_files m
inner join sys.databases d
on (m.database_id = d.database_id)
order by 1, 2

Index Last Rebuild Date

Use this query to check when an Index was last updated.

SELECT OBJECT_NAME(object_id) [TableName],
name [IndexName],
STATS_DATE(object_id, stats_id) [LastUpdated]
FROM sys.stats
WHERE name NOT LIKE '_WA%'
AND STATS_DATE(object_id, stats_id) IS NOT NULL
AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
ORDER BY [LastUpdated] desc

Table and Index Size

If you need to find your largest tables this query will show how big each one is.

SELECT s.Name AS SchemaName, t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0
AND i.OBJECT_ID &gt; 255
GROUP BY t.Name, s.Name, p.Rows
ORDER BY TotalSpaceKB desc

Shrink Transaction Logs

If you need to quickly need to shrink a transaction log. Use the query above to get the logical file name.

ALTER DATABASE [Database_Name] SET RECOVERY SIMPLE
DBCC SHRINKFILE(Database_Logfile_Name)
ALTER DATABASE [Database_Name] SET RECOVERY FULL

Query Database Backup History

Query backup history for specific database. This shows whether it was a full or transaction log backup, start and end times, and backup size.

SELECT upper(name) as 'Backup Type',
CONVERT(VARCHAR(20), backup_start_date, 100) as 'Start',
CONVERT(VARCHAR(20), backup_finish_date, 100) as 'Finish',
backup_size as 'Size',
recovery_model as 'Recovery Model'
FROM msdb..backupset
WHERE database_name = 'Database_Name'
ORDER by backup_finish_date DESC

Grant Execute Permission to All Stored Procedures

Use this command to grant EXECUTE permission to all the stored procedures in a database.

GRANT EXECUTE TO [DOMAIN\USER]

Search the Contents of SQL Objects

To search the contents of Stored Procedures, Functions, Triggers, or Views use this query:

DECLARE @SEARCH VARCHAR(255), @notcontain Varchar(255)

SELECT @SEARCH = 'specOuterPkgMaterialJoin'

SELECT sysobjects.name AS [Object Name] , sysobjects.xtype [Type]
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id 
AND CHARINDEX(@SEARCH,syscomments.text)>0

To search the Command Text of SQL Agent Jobs for a string use the following query:

USE [msdb]
GO
SELECT servers.srvname [Server], jobs.name [Job Name],
steps.step_id [Step ID], steps.command [Command],
jobs.enabled [Enabled]
FROM dbo.sysjobs jobs
JOIN dbo.sysjobsteps steps ON steps.job_id = jobs.job_id
JOIN master.dbo.sysservers servers ON servers.srvid = jobs.originating_server_id
WHERE steps.command LIKE N'%SEARCH_TERM%'

Simple Backup and Restore of Tables

Use this to quickly backup a table, and update records from backup if needed.

-- Table Backup
select * into MyBackup from MyTable;

-- Table Restore
Update MyTable set Enabled = 1 where Id in (select Id from MyBackup where status = 1)

SQL Login Creation

Use this to quickly create a SQL Login and SQL User, then grant datareader and datawriter, and also grant Execute access to certain Stored Procedures.

-- Create db login
CREATE LOGIN [DOMAIN\ServiceIdX] FROM WINDOWS;
GO

-- Create db user and grant role membership
CREATE USER [DOMAIN\ServiceIdX] FOR LOGIN [DOMAIN\ServiceIdX] WITH DEFAULT_SCHEMA = dbo;
GO

-- Add to db roles
EXEC sp_addrolemember N'db_datareader', N'DOMAIN\ServiceIdX'
EXEC sp_addrolemember N'db_datawriter', N'DOMAIN\ServiceIdX'

-- Grant Exec on Stored Procedure
GRANT EXEC on sp_MyStoredProc to [DOMAIN\ServiceIdX]

Other SQL Server Blogs

Other good resources for SQL Server Administration