Database Size in MB

SELECT SUM (size*.008) AS database_size_in_MB, DB_NAME (dbid) AS database_name
FROM sys.sysaltfiles
WHERE DB_NAME (dbid) IS NOT NULL
GROUP BY DB_NAME (dbid)

Query to find the latest backup date and size

SELECT DISTINCT a.*
FROM
(SELECT
d.database_name
,d.backup_finish_date
,(backup_size/1024/1024) AS backup_size_in_MBs
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id) a
INNER JOIN
(SELECT
 d.database_name
,MAX(d.backup_finish_date) AS max_backup_finish_date
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id
WHERE [type] = 'D'
GROUP BY d.database_name) d
ON a.backup_finish_date = d.max_backup_finish_date
AND a.database_name = d.database_name

SQL Server Index Fragmentation Levels Using DMV (Fragmentation greater than 15%)

SELECT DB_NAME(database_id) AS database_name
, so.[name] AS table_name
, dmv.index_id
, si.[name]
, dmv.index_type_desc
, dmv.alloc_unit_type_desc
, dmv.index_depth
, dmv.index_level
, avg_fragmentation_in_percent
, page_count

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') dmv
INNER JOIN sysobjects so
ON dmv.[object_id] = so.id
INNER JOIN sys.indexes si
ON
dmv.object_id = si.object_id
AND dmv.index_id = si.index_id
WHERE dmv.avg_fragmentation_in_percent > 15
ORDER BY table_name, si.name

Script to list all indexes in a database with their seek, scan, update count and their last seek, scan, update times


SELECT DB_NAME(database_id) AS [DB Name]
, OBJECT_NAME (dm.object_id) AS [Table Name]
, si.[name]
, user_seeks AS [User Seeks]
, user_scans AS [User Scans]
, user_lookups AS [User Lookups]
, user_updates AS [User Updates]
, last_user_seek AS [Last User Seek]
, last_user_scan AS [Last User Scan]
, last_user_lookup AS [Last User Lookup]
, last_user_update AS [Last User Update]
--, *
FROM sys.dm_db_index_usage_stats dm
INNER JOIN sys.indexes si
ON si.index_id = dm.index_id
AND dm.object_id = si.object_id
INNER JOIN sysobjects so
ON so.id = si.object_id
WHERE dm.database_id = DB_ID()
AND si.name IS NOT NULL
AND OBJECTPROPERTY (dm.object_id, 'IsUserTable') = 1
ORDER BY COALESCE (last_user_seek, last_user_scan, last_user_lookup, last_user_update)

How to register and de-register an SPN

To view all SPNs registered under a service account, usually the one under which SQL Services are running, type the following command in the CMD prompt

                                           C:> setspn -l [domain\account]

The above should display all SQL services running under the account, that have registered their SPNs in the active directory. The format of the displayed data is like the below example

                                 MSSQLSvc/[FQDN servername]:[port number]

The above result will also serve as means to verify if indeed the SQL Server has registered an SPN correctly. This can be done by cross verifying the server name and port number against the actual server

The port number that a SQL server is running on can be found from the error log. This has to match the port with which the SPN is registered with

De-registering a SPN:

SPN can be deleted using the following command

        c:\> setspn -d MSSQLSvc/[FQDN servername]:[port number] [domain\service account name]

SPN can be registered in a similar way using the following command

        c:\> setspn -a MSSQLSvc/[FQDN servername]:[port number] [domain\service account name]

Notice that the only difference between registering and de-registering a SPN is the switch option. -d for de-registering and -a for registering

List all trace events and trace categories

SELECT s2.[category_id] AS [Category_ID] , s2.name AS [Category_Name] , s1.trace_event_id AS [Trace_Event_ID] , s1.name AS [Event_Class_name] , s4.[trace_column_id] AS [Trace_Colum_ID] , s4.name AS [Column_Name] , s4.[type_name] AS [data_Type] FROM sys.trace_events s1 INNER JOIN sys.trace_categories s2 ON s1.category_id = s2.category_id INNER JOIN sys.trace_event_bindings s3 ON s1.trace_event_id = s3.trace_event_id INNER JOIN sys.trace_columns s4 ON s3.trace_column_id = s4.trace_column_id ORDER BY s2.[category_id] , s1.name , s4.name

Renaming a SQL Server Instance

A SQL Server instance can be renamed the following way

EXEC sp_dropserver 'old server name'
GO
EXEC sp_addserver 'new server name', local
GO

The change can be verified by running the SELECT @@SERVERNAME command