Email Alert when SQL Server is restarted

SSUGE.orgLeave a comment

As a DBA we need to be notified when a SQL Server is restarted so that we can take a proactive reaction to identify the cause of the restart which could be an indication of underlining issues with the Server OS or the SQL Server Engine.

I have used this several years now and it has helped me avoid Severity 1 outages a few cases.

Just put this code in a sql job and make sure you change the db mail portion that pertains to your environment.

Declare @Title varchar(max)
Declare @Ebody varchar(max)
Declare @TableHead varchar(max)
Declare @TableTail varchar(max)

Select @Title = 'SQL Server Restarted - ' + @@SERVERNAME
Set NoCount On;
Set @TableTail= '</body></html>';

---HTML Layout--

Set @TableHead = '<html><head>' +
'<H4 style="color: #000000">**********This is an informational message only**********</H4>' +
 '</head>' +
 '<body>' + 'SQL Server Services have been RESTARTED : '+ '<strong>' + @@SERVERNAME + '</strong>' +
 '<p>' + ' If this was not planned, check the Server' + '</p>'

select @TableHead = @TableHead + @TableTail

WAITFOR DELAY '00:00:30'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DB_Admins',
@recipients = 'db_admins@yourdomain.com',
@body = @TableHead,
@body_format = HTML,
@subject = @Title;

Hope this helps you avoid any outages as it has helped me.

Get hard drive brand, size and serial number on a Windows Machine

SSUGE.org

A few occasions I came across a request to find the hard drive brand, size and serial number installed on a Windows OS machine.

To get the basic hard drive info and to determine some basic info about the hard drive installed on your machine, follow these steps:

Click on the Search icon on your Task Bar:

Search for Command Prompt

Item 1: Your Search Text

Item 2: Will be the search results, right click on it and click on Run as administrator

The Command Shell will open, at the prompt type in:

wmic diskdrive get model,serialNumber,size,mediaType

You will see the following info:

If you have multiple hard drives on your machine then it might take a few seconds before the results are shown.

Find Missing Index

SSUGE.org

As part of SQL Server’s processing algorithm for a query, SQL will recommend creating an index which it believes will help the query run faster – hence called Missing Index. You have to take this recommendation with a grain of salt. although SQL Server is very good at determining when an index is needed but sometimes it is not so good on the index recommendation.

I have been using this script for a few years now, donot remember where I got it from but I think it may be based off Glenn Berry’s Diagnostic Script.
The query below will show suggested missing index for a specified database. You can run it for a specified table by uncommenting the AND clause and specifying the table name.
Keep in mind that these missing index gets reset every time SQL Server is restarted, therefore if you have rebooted the server or restarted sql server recently you may not have the index stats updated yet.

USE DatabaseName
GO
 
SELECT db.[name] AS [DatabaseName]
    ,id.[object_id] AS [ObjectID]
	,OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName]
    ,id.[statement] AS [FullyQualifiedObjectName]
    ,id.[equality_columns] AS [EqualityColumns]
    ,id.[inequality_columns] AS [InEqualityColumns]
    ,id.[included_columns] AS [IncludedColumns]
    ,gs.[unique_compiles] AS [UniqueCompiles]
    ,gs.[user_seeks] AS [UserSeeks]
    ,gs.[user_scans] AS [UserScans]
    ,gs.[last_user_seek] AS [LastUserSeekTime]
    ,gs.[last_user_scan] AS [LastUserScanTime]
    ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]  -- Average cost of the user queries that could be reduced by the index in the group.
    ,gs.[avg_user_impact] AS [AvgUserImpact]  -- The value means that the query cost would on average drop by this percentage if this missing index group was implemented.
    ,gs.[system_seeks] AS [SystemSeeks]
    ,gs.[system_scans] AS [SystemScans]
    ,gs.[last_system_seek] AS [LastSystemSeekTime]
    ,gs.[last_system_scan] AS [LastSystemScanTime]
    ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
    ,gs.[avg_system_impact] AS [AvgSystemImpact]  -- Average percentage benefit that system queries could experience if this missing index group was implemented.
    ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
    ,'CREATE INDEX [IX_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN '_'
        ELSE ''
        END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN ','
        ELSE ''
        END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
    ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK) ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK) ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK) ON db.[database_id] = id.[database_id]
WHERE  db.[database_id] = DB_ID()
--AND OBJECT_NAME(id.[object_id], db.[database_id]) = 'YourTableName'
ORDER BY ObjectName, [IndexAdvantage] DESC
OPTION (RECOMPILE);

Hope this helps in resolving index issues in your database environment.

Get list of table record count

SSUGE.org

This SQL query will provide a list of tables in a database with the number of rows

select schema_name(tab.schema_id) + '.' + tab.name as [tablename],
       sum(part.rows) as [rowcount]
   from sys.tables as tab
        inner join sys.partitions as part
            on tab.object_id = part.object_id
where part.index_id IN (1, 0) -- 0 - table without PK, 1 table with PK
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(part.rows) desc

Each row represents each table. This will also include all the tables in the a database even the empty ones.

Get List of Table by size in Azure SQL Database

SSUGE.org

Here is a handy sql script to get a list of tables by their size in Azure SQL Database

select schema_name(tab.schema_id) + '.' + tab.name as [tablename],

    cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_spacemb,

    cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_spacemb

from sys.tables tab

    inner join sys.indexes ind

        on tab.object_id = ind.object_id

    inner join sys.partitions part

        on ind.object_id = part.object_id and ind.index_id = part.index_id

    inner join sys.allocation_units spc

        on part.partition_id = spc.container_id

group by schema_name(tab.schema_id) + '.' + tab.name

order by sum(spc.used_pages) desc

The columns you will get from this script:

tablename

size_mb

allocated_spacemb