Stable State Health Checks.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "Windows Server Information \r\n",
                "\r\n",
                "Gather Windows Log information \r\n",
                "\r\n",
                "Running Gather Log Events.ps1 as an administrator in PowerShell ISE (if applicable; if not, run in PowerShell as administrator) will collect error messages from the last 30 days found in the Application, Security and/or System Event Logs. \r\n",
                "\r\n",
                "The script will export the data to a CSV file (“export.csv”), which can be found in the C:\\temp folder on the server in which the script is run. \r\n",
                "\r\n",
                "Power Options – High Performance should always be selected.   \r\n",
                "\r\n",
                "Check to make sure High Performance is the selected Power Option, and that GPO has not made changes. \r\n",
                "\r\n",
                "Check for possible new anti-virus software on the server. If found, recommend excluding SQL files from the anti-virus scan. \r\n",
                "\r\n",
                "http://support.microsoft.com/kb/309422 "
            ],
            "metadata": {
                "azdata_cell_guid": "134d2231-6ab0-407c-a61b-53f607e70849"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "SQL Server Information\r\n",
                "Server Properties\r\n",
                "1.\tEvaluate current SQL Server version. Report if there is a recent SP (<=2016) or CU (>=2017)\r\n",
                "a.\tVersion reference: https://sqlcollaborative.github.io/builds\r\n",
                "b.\tThere is a specific issue with TDE on early versions of 2016 that is critical. Need to make sure we are patched above CU7 2016 or CU4 2016 SP1 if using TDE.\r\n",
                "i.\thttps://support.microsoft.com/en-us/help/4019893/fix-restore-fails-when-you-do-backup-by-using-compression-and-checksum\r\n",
                "2.\tReview SQL Server Default locations in Server Properties. The default locations must exist and could cause a service pack or CU install to fail if they do not.\r\n",
                "3.\tSQL Server Log \r\n",
                "a.\tReview the SQL Server Error Log using Error Log.sql. Document any new/suspicious activity.\r\n",
                "4.\tSystem Configuration Values \r\n",
                "a.\tConfiguration Changes History.sql checks for system configuration changes.\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "00e99bcf-aa99-4dcb-9087-12ea420d2d54"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--error_log.sql\r\n",
                "--#TODO: review string filters at bottom.\r\n",
                "--Can execute in a multiserver query\r\n",
                "--Execute in Grid mode\r\n",
                "\r\n",
                "PRINT ('Error Log Output Window')\r\n",
                "use tempdb\r\n",
                "go\r\n",
                "select SYSDATETIMEOFFSET()\r\n",
                "declare @oldestdate as date, @now as datetime2(0)\r\n",
                "select @oldestdate = dateadd(month,-3, sysdatetime()), @now = sysdatetime() --Filter the time frame of the logs.\r\n",
                "\r\n",
                "select 'Getting errors since ' + cast(@oldestdate as varchar(30))\r\n",
                "\r\n",
                "--Get list of logs associated with the SQL Server (by default is 7, probably need more!) \r\n",
                "CREATE TABLE #SQLErrorLogList (\r\n",
                "    LogNumber INT NOT NULL,\r\n",
                "    LogEndDate datetime2(0) NOT NULL,\r\n",
                "    LogSize_b BIGINT NOT NULL);\r\n",
                "CREATE NONCLUSTERED INDEX IDX_CL_ell on #SQLErrorLogList (LogNumber) INCLUDE (LogEndDate);\r\n",
                "\r\n",
                "INSERT INTO #SQLErrorLogList\r\n",
                "EXEC sys.sp_enumerrorlogs;\r\n",
                "\r\n",
                "--error messages in current log\r\n",
                "create table #readerrorlog\r\n",
                "( LogDate datetime not null\r\n",
                ", LogProcessInfo varchar(255) not null \r\n",
                ", [LogMessageText] varchar(1500) not null \r\n",
                ")\r\n",
                "\r\n",
                "CREATE CLUSTERED INDEX IDX_CL_rel on #readerrorlog (LogDate);\r\n",
                "\r\n",
                "declare @lognumber int = 0, @endoflogfiles bit = 0, @maxlognumber int = 0;\r\n",
                "\r\n",
                "select @maxlognumber =   MAX(LogNumber) from #SQLErrorLogList\r\n",
                "WHILE (\r\n",
                "\t\t((Select LogEndDate from #SQLErrorLogList where @lognumber = LogNumber) > @oldestdate)\r\n",
                "\t\tand @lognumber <= @maxlognumber\r\n",
                "\t\t) \r\n",
                "BEGIN\r\n",
                "\r\n",
                "\tINSERT INTO #readerrorlog \r\n",
                "\tEXEC master.dbo.xp_readerrorlog  \r\n",
                "\t  @lognumber\t\t--current log file\r\n",
                "\t, 1\t\t\t\t\t--SQL Error Log\r\n",
                "\t, N''\t\t\t\t--search string 1, must be unicode. Leave empty on purpose, as we do filtering later on.\r\n",
                "\t, N''\t\t\t\t--search string 2, must be unicode. Leave empty on purpose, as we do filtering later on.\r\n",
                "\t, @oldestdate, @now --time filter. Should be @oldestdate < @now\r\n",
                "\t, N'desc'\t\t\t--sort\r\n",
                "\t\t\t\r\n",
                "\t--print 'including lognumber ' + str(@lognumber)\r\n",
                "\r\n",
                "\tset @lognumber = @lognumber + 1\t\r\n",
                "END\r\n",
                "GO\r\n",
                "\r\n",
                "CREATE NONCLUSTERED INDEX IDX_NC_rel on #readerrorlog (Logdate desc, [LogMessageText]) INCLUDE( LogProcessInfo)\r\n",
                "\r\n",
                "GO\r\n",
                "--order of servers in a multiserver query is not determinant\r\n",
                "\r\n",
                "--Raw error list\r\n",
                "select * from #readerrorlog \r\n",
                "where  1=1\r\n",
                "and (\t\r\n",
                "\tLogMessageText like '%error%'\r\n",
                "or\tLogMessageText like '%failure%'\r\n",
                "or\tLogMessageText like '%failed%'\r\n",
                "or\tLogMessageText like '%corrupt%'\r\n",
                ")\r\n",
                "and LogMessageText not like '%without errors%'\r\n",
                "and LogMessageText not like '%returned no errors%'\r\n",
                "and LogMessageText not like 'Registry startup parameters:%'\r\n",
                "and LogMessageText not like '%informational%'\r\n",
                "and LogMessageText not like '%found 0 errors%'\r\n",
                "order by LogDate desc;\r\n",
                "\r\n",
                "--Aggregate error counts\r\n",
                "select LogMessageText, LogProcessInfo, ErrorCount = count(LogDate), MostRecentOccurrence = max(LogDate) \r\n",
                "from #readerrorlog \r\n",
                "where  1=1\r\n",
                "and (\t\r\n",
                "\tLogMessageText like '%error%'\r\n",
                "or\tLogMessageText like '%failure%'\r\n",
                "or\tLogMessageText like '%failed%'\r\n",
                "or\tLogMessageText like '%corrupt%'\r\n",
                ")\r\n",
                "and LogMessageText not like '%without errors%'\r\n",
                "and LogMessageText not like '%returned no errors%'\r\n",
                "and LogMessageText not like 'Registry startup parameters:%'\r\n",
                "and LogMessageText not like '%informational%'\r\n",
                "and LogMessageText not like '%found 0 errors%'\r\n",
                "group by LogMessageText, LogProcessInfo\r\n",
                "order by count(LogDate) desc, max(LogDate) desc;\r\n",
                "\r\n",
                "SELECT Reboots = LogDate FROM #readerrorlog WHERE LogMessageText like 'Registry startup parameters:%'\r\n",
                "ORDER BY LogDate desc;\r\n",
                "GO\r\n",
                "\r\n",
                "drop table #readerrorlog\r\n",
                "drop table #SQLErrorLogList"
            ],
            "metadata": {
                "azdata_cell_guid": "1a21953c-b50f-482b-bfc4-724387eee125"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--Configuration Changes History.sql\r\n",
                "--Based on the configuration changes history report in SSMS\r\n",
                "PRINT('Configuration Change History Output Window')\r\n",
                "exec sp_executesql @stmt=N'begin try\r\n",
                "declare @enable int;\r\n",
                "select @enable = convert(int,value_in_use) from sys.configurations where name = ''default trace enabled''\r\n",
                "if @enable = 1 --default trace is enabled\r\n",
                "begin\r\n",
                "        declare @d1 datetime;\r\n",
                "        declare @diff int;  \r\n",
                "        declare @curr_tracefilename varchar(500); \r\n",
                "        declare @base_tracefilename varchar(500); \r\n",
                "        declare @indx int ;\r\n",
                "        declare @temp_trace table (\r\n",
                "                textdata nvarchar(MAX) collate database_default \r\n",
                "        ,       login_name sysname collate database_default\r\n",
                "        ,       start_time datetime\r\n",
                "        ,       event_class int\r\n",
                "        );\r\n",
                "        \r\n",
                "        select @curr_tracefilename = path from sys.traces where is_default = 1 ; \r\n",
                "        \r\n",
                "        set @curr_tracefilename = reverse(@curr_tracefilename)\r\n",
                "        select @indx  = PATINDEX(''%\\%'', @curr_tracefilename) \r\n",
                "        set @curr_tracefilename = reverse(@curr_tracefilename)\r\n",
                "        set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + ''\\log.trc'';\r\n",
                "        \r\n",
                "        insert into @temp_trace\r\n",
                "        select TextData\r\n",
                "        ,       LoginName\r\n",
                "        ,       StartTime\r\n",
                "        ,       EventClass \r\n",
                "        from ::fn_trace_gettable( @base_tracefilename, default ) \r\n",
                "        where ((EventClass = 22 and Error = 15457) or (EventClass = 116 and TextData like ''%TRACEO%(%''))\r\n",
                "        \r\n",
                "        select @d1 = min(start_time) from @temp_trace\r\n",
                "        \r\n",
                "        --set @diff= datediff(hh,@d1,getdate())\r\n",
                "        --set @diff=@diff/24; \r\n",
                "\r\n",
                "        select --(row_number() over (order by start_time desc))%2 as l1\r\n",
                "                @d1 as TraceStartDate\r\n",
                "        ,       start_time as EventDate\r\n",
                "\t\t,       case event_class \r\n",
                "                        when 116 then ''Trace Flag '' + substring(textdata,patindex(''%(%'',textdata),len(textdata) - patindex(''%(%'',textdata) + 1) \r\n",
                "                        when 22 then substring(textdata,58,patindex(''%changed from%'',textdata)-60) \r\n",
                "                end as config_option\r\n",
                "        ,       login_name\r\n",
                "        ,       case event_class \r\n",
                "                        when 116 then ''--''\r\n",
                "                        when 22 then substring(substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))\r\n",
                "                                                                ,patindex(''%changed from%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata)))+13\r\n",
                "                                                                ,patindex(''%to%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))) - patindex(''%from%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))) - 6) \r\n",
                "                end as old_value\r\n",
                "        ,       case event_class \r\n",
                "                        when 116 then substring(textdata,patindex(''%TRACE%'',textdata)+5,patindex(''%(%'',textdata) - patindex(''%TRACE%'',textdata)-5)\r\n",
                "                        when 22 then substring(substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))\r\n",
                "                                                                ,patindex(''%to%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata)))+3\r\n",
                "                                                                , patindex(''%. Run%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))) - patindex(''%to%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))) - 3) \r\n",
                "                end as new_value\r\n",
                "        from @temp_trace \r\n",
                "        order by start_time desc\r\n",
                "end else \r\n",
                "begin \r\n",
                "        select top 0  1  as l1, 1 as difference,1 as date , 1 as config_option,1 as start_time , 1 as login_name, 1 as old_value, 1 as new_value\r\n",
                "end\r\n",
                "end try \r\n",
                "begin catch\r\n",
                "select  ERROR_NUMBER() as Error_Number\r\n",
                ",       ERROR_SEVERITY() as date \r\n",
                ",       ERROR_STATE() as config_option\r\n",
                ",       1 as start_time \r\n",
                ",       ERROR_MESSAGE() as login_name\r\n",
                ",       1 as old_value, 1 as new_value\r\n",
                "end catch',@params=N''"
            ],
            "metadata": {
                "azdata_cell_guid": "86379d98-e2b2-42d1-b4e7-9f14704081db"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Performance\r\n",
                "1.\tRun Page Life Expectancy.sql.\r\n",
                "a.\tDoes SQL have enough memory, based on PLE, Churn, and the Target vs Total?\r\n",
                "2.\tReview CPU Utilization.Sql script and look for any recent +90% periods.\r\n",
                "3.\tAre there any recent Memory Dumps?\r\n",
                "a.\tRunning Find Memory Mini Dumps.sql will let you know if SQL is having issues or has had any  “mini dump crashes” in the recent past.\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "b3022c48-41b8-4e39-8401-1e41975253fb"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--Page Life Expectancy.sql\r\n",
                "PRINT('Page Life Expectancy')\r\n",
                "select \r\n",
                "\tp.InstanceName\r\n",
                ",\tc.Version \r\n",
                ",\t'LogicalCPUCount'\t\t= os.cpu_count\r\n",
                ",\tOS_Physical_Mem_MB = os.[Server Physical Mem (MB)] -- SQL2012+ only\r\n",
                ",\tMin_Server_Mem_MB = c.[Min_Server_Mem_MB]\r\n",
                ",\tMax_Server_Mem_MB = c.[Max_Server_Mem_MB] --2147483647 means unlimited, just like it shows in SSMS\r\n",
                ",\tp.PLE_s --300s is only an arbitrary rule for smaller memory servers (<16gb), for larger, it should be baselined and measured.\r\n",
                ",\t'Churn (MB/s)'\t\t\t=\tcast((os.Total_Server_Memory_GB*1024.)/NULLIF(p.PLE_s,0) as decimal(19,2))\r\n",
                ",\tOS_Available_physical_mem_GB = (SELECT cast(available_physical_memory_kb / 1024. / 1024. as decimal(19,2)) from sys.dm_os_sys_memory) \r\n",
                ",\tSQL_Physical_memory_in_use_GB = (SELECT cast(physical_memory_in_use_kb / 1024. / 1024. as decimal(19,2)) from sys.dm_os_process_memory)\r\n",
                ",\tos.Total_Server_Memory_GB --May be more or less than memory_in_use \r\n",
                ",\tos.Target_Server_Memory_GB\t\r\n",
                ",\tTarget_vs_Total = CASE WHEN os.Total_Server_Memory_GB < os.Target_Server_Memory_GB\t \r\n",
                "\t\t\t\t\t\t\tTHEN 'Target >= Total. SQL wants more memory than it has, or is building up to that point.'\r\n",
                "\t\t\t\t\t\t\tELSE 'Total >= Target. SQL has enough memory to do what it wants.' END\r\n",
                ",\tsi.LPIM -- Works on SQL 2016 SP1, 2012 SP4+\r\n",
                "from(\r\n",
                "\tselect \r\n",
                "\t\tInstanceName = @@SERVERNAME  \r\n",
                "\t,\tPLE_s\t=\tcase when object_name like '%Buffer Manager%' and counter_name = 'Page life expectancy'  then cntr_value end  --This only looks at the overall buffer pool, not individual NUMA nodes. https://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/\r\n",
                "\tfrom sys.dm_os_performance_counters \r\n",
                "\twhere object_name like '%Buffer Manager%' and counter_name = 'Page life expectancy'\r\n",
                ")  as p\r\n",
                "inner join (select 'InstanceName' = @@SERVERNAME, Version = @@VERSION, \r\n",
                "\t\t\tMin_Server_Mem_MB  = max(case when name = 'min server memory (MB)' then convert(bigint, value_in_use) end) ,\r\n",
                "\t\t\tMax_Server_Mem_MB = max(case when name = 'max server memory (MB)' then convert(bigint, value_in_use) end) \r\n",
                "\t\t\tfrom sys.configurations) as c on p.InstanceName = c.InstanceName\r\n",
                "inner join (SELECT 'InstanceName' = @@SERVERNAME \r\n",
                "\t\t\t, cpu_count , hyperthread_ratio AS 'HyperthreadRatio',\r\n",
                "\t\t\tcpu_count/hyperthread_ratio AS 'PhysicalCPUCount'\r\n",
                "\t\t\t, 'Server Physical Mem (MB)' = cast(physical_memory_kb/1024. as decimal(19,2))   -- SQL2012+ only\r\n",
                "\t\t\t, \tTotal_Server_Memory_GB = convert(decimal(19,3), committed_kb / 1024. / 1024.)\r\n",
                "\t\t\t,\tTarget_Server_Memory_GB = convert(decimal(19,3), committed_target_kb / 1024. / 1024.)\r\n",
                "\t\t\tFROM sys.dm_os_sys_info ) as os\r\n",
                "on c.InstanceName=os.InstanceName\r\n",
                "\r\n",
                "-- Works on SQL 2016 SP1, 2012 SP4+\r\n",
                "cross apply (select LPIM = CASE sql_memory_model_Desc \r\n",
                "\t\t\t\t\tWHEN  'Conventional' THEN 'Lock Pages in Memory privilege is not granted'\r\n",
                "\t\t\t\t\tWHEN 'LOCK_PAGES' THEN 'Lock Pages in Memory privilege is granted'\r\n",
                "\t\t\t\t\tWHEN 'LARGE_PAGES' THEN 'Lock Pages in Memory privilege is granted in Enterprise mode with Trace Flag 834 ON'\r\n",
                "\t\t\t\t\tEND from sys.dm_os_sys_info \r\n",
                "\t\t\t\t) as si\r\n",
                " \r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "7dcc495f-9b1a-4843-96e1-dafa06259aa2"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Page Life Expectancy"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Warning: Null value is eliminated by an aggregate or other SET operation."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.030"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 1,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "InstanceName"
                                    },
                                    {
                                        "name": "Version"
                                    },
                                    {
                                        "name": "LogicalCPUCount"
                                    },
                                    {
                                        "name": "OS_Physical_Mem_MB"
                                    },
                                    {
                                        "name": "Min_Server_Mem_MB"
                                    },
                                    {
                                        "name": "Max_Server_Mem_MB"
                                    },
                                    {
                                        "name": "PLE_s"
                                    },
                                    {
                                        "name": "Churn (MB/s)"
                                    },
                                    {
                                        "name": "OS_Available_physical_mem_GB"
                                    },
                                    {
                                        "name": "SQL_Physical_memory_in_use_GB"
                                    },
                                    {
                                        "name": "Total_Server_Memory_GB"
                                    },
                                    {
                                        "name": "Target_Server_Memory_GB"
                                    },
                                    {
                                        "name": "Target_vs_Total"
                                    },
                                    {
                                        "name": "LPIM"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "CORP-2TK94207B1\\SQL2K19",
                                    "1": "Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) \n\tSep 24 2019 13:48:23 \n\tCopyright (C) 2019 Microsoft Corporation\n\tDeveloper Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 19041: )\n",
                                    "2": "8",
                                    "3": "32582.85",
                                    "4": "16",
                                    "5": "8000",
                                    "6": "329",
                                    "7": "1.03",
                                    "8": "20.95",
                                    "9": "0.15",
                                    "10": "0.331",
                                    "11": "7.813",
                                    "12": "Target >= Total. SQL wants more memory than it has, or is building up to that point.",
                                    "13": "Lock Pages in Memory privilege is not granted"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>InstanceName</th><th>Version</th><th>LogicalCPUCount</th><th>OS_Physical_Mem_MB</th><th>Min_Server_Mem_MB</th><th>Max_Server_Mem_MB</th><th>PLE_s</th><th>Churn (MB/s)</th><th>OS_Available_physical_mem_GB</th><th>SQL_Physical_memory_in_use_GB</th><th>Total_Server_Memory_GB</th><th>Target_Server_Memory_GB</th><th>Target_vs_Total</th><th>LPIM</th></tr>",
                            "<tr><td>CORP-2TK94207B1\\SQL2K19</td><td>Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) \n\tSep 24 2019 13:48:23 \n\tCopyright (C) 2019 Microsoft Corporation\n\tDeveloper Edition (64-bit) on Windows 10 Enterprise 10.0 &lt;X64&gt; (Build 19041: )\n</td><td>8</td><td>32582.85</td><td>16</td><td>8000</td><td>329</td><td>1.03</td><td>20.95</td><td>0.15</td><td>0.331</td><td>7.813</td><td>Target &gt;= Total. SQL wants more memory than it has, or is building up to that point.</td><td>Lock Pages in Memory privilege is not granted</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 1
        },
        {
            "cell_type": "code",
            "source": [
                "--CPU Utilization.sql\r\n",
                "--This is simple use of the ring_buffer for historical CPU, goes back a little over 4 hours.\r\n",
                "-- for more CPU and Memory, look at toolbox/sys_dm_os_ring_buffers.sql\r\n",
                "PRINT('CPU Utilization Output Window')\r\n",
                "select\r\n",
                "\tAvg_SystemIdle_Pct\t\t\t\t=\tAVG( record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') )\r\n",
                ",\tAvg_SQLProcessUtilization_Pct\t=\tAVG( record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') )\r\n",
                "      from (\r\n",
                "            select timestamp, convert(xml, record) as record\r\n",
                "            from sys.dm_os_ring_buffers\r\n",
                "            where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'\r\n",
                "            and record like '%<SystemHealth>%') as x\r\n",
                "\r\n",
                "declare @ts_now bigint\r\n",
                "--select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info\r\n",
                "select @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info;\r\n",
                "select\trecord_id\r\n",
                "\t,\tEventTime\t\t\t\t=  dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) \r\n",
                "\t,\tSQLProcessUtilization\r\n",
                "\t,\tSystemIdle\r\n",
                "\t,\tOtherProcessUtilization\t= 100 - SystemIdle - SQLProcessUtilization \r\n",
                "from (\r\n",
                "      select\r\n",
                "            record_id\t\t\t\t=\trecord.value('(./Record/@id)[1]', 'int')\r\n",
                "        ,\tSystemIdle\t\t\t\t=\trecord.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') \r\n",
                "        ,\tSQLProcessUtilization\t=\trecord.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') \r\n",
                "        ,\ttimestamp\r\n",
                "      from (\r\n",
                "            select timestamp, convert(xml, record) as record\r\n",
                "            from sys.dm_os_ring_buffers\r\n",
                "            where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'\r\n",
                "            and record like '%<SystemHealth>%') as x\r\n",
                "      ) as y\r\n",
                "order by record_id desc\r\n",
                "\r\n",
                "\r\n",
                "--http://sqlblog.com/blogs/ben_nevarez/archive/2009/07/26/getting-cpu-utilization-data-from-sql-server.aspx"
            ],
            "metadata": {
                "azdata_cell_guid": "39125f75-f8b2-482a-a050-cc9030ae9fd1"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--Find Memory Mini Dumps.sql \r\n",
                "-- Get information on location, time and size of any memory dumps from SQL Server  \r\n",
                "-- Only SQL 2008R2+\r\n",
                "PRINT('Fine Memory Mini Dump Output Window')\r\n",
                "SELECT [filename], creation_time, size_in_bytes/1048576.0 AS [Size (MB)]\r\n",
                "FROM sys.dm_server_memory_dumps \r\n",
                "ORDER BY creation_time DESC OPTION (RECOMPILE);"
            ],
            "metadata": {
                "azdata_cell_guid": "cd89bba6-64a5-4dfd-b3e7-91d51b4bd536"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "SQL Agent Configuration\r\n",
                "1.\tAre basic alerts for severity 16 and higher setup and enabled to the correct operator, covering all databases?\r\n",
                "a.\tIf not, recommend running Add Error Event Alerts.sql to enable alert notification. \r\n",
                "b.\tConsider disabling the severity 20 error alert because of common nuisance emails.\r\n",
                "2.\tAre the following jobs enabled on the server and completing successfully? Check Job History on the following jobs to confirm all are succeeding: \r\n",
                "a.\tAutomated Index Maintenance\r\n",
                "i.\tLook for any type of similarly-named index maintenance job to ensure index maintenance is being completed routinely.\r\n",
                "ii.\tActually check the log table to verify activity and no errors. This or similar:\r\n",
                "select * from dbaadmin.dbo.indexmaintlog order by id desc \r\n",
                "b.\tVolume Stats Insert\r\n",
                "i.\tDo a select on the table, this should get a few months of data back. Any big jumps or trends?\r\n",
                "SELECT TOP (1000) *  FROM [DBAHOUND].[DBO].[VOLUMESTATS]  ORDER BY DATETIMEPERFORMED DESC\r\n",
                "a.\tJob Failure Notification\r\n",
                "ii.\tCan also be named “Add Job Failure Notification”\r\n",
                "3.\tChange the SQL Server Agent log history retention. Check how far back history goes for important jobs, such as backup jobs. Make sure to change the total row count from default 1000 rows to 10000 or larger, especially on servers that have replication jobs running or multiple large-output jobs, to prevent loss of historical Information. To store hourly transaction log backups rows for 3 months (recommended), you would also need to increase the max rows per job to at least 2160!"
            ],
            "metadata": {
                "azdata_cell_guid": "e9c74984-c2dd-4a28-968f-42e687cab47e"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT TOP (1000) * FROM [DBALOGGING].[DBO].[VOLUMESTATS] ORDER BY DATETIMEPERFORMED DESC"
            ],
            "metadata": {
                "azdata_cell_guid": "220bba60-b57d-4c64-bf34-477436e56d84"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Drives\r\n",
                "1.\tRecord SQL file locations\r\n",
                "a.\tRun “select * from sys.master_files”\r\n",
                "i.\tAre there any SQL server files that have been added to the C: drive? If so, they should be moved."
            ],
            "metadata": {
                "azdata_cell_guid": "dfe0c9d6-c506-462b-8ae5-b120682d8d53"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "select * from sys.master_files"
            ],
            "metadata": {
                "azdata_cell_guid": "4f4cec16-2903-4e17-87d9-6d100458f1c2"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Databases\r\n",
                "4.\tReview Database Configurations: note new databases, record any changes to existing, etc.\r\n",
                "a.\tDatabase Settings Info.sql\r\n",
                "i.\tIncluded in this script is the sys.databases.log_reuse_wait value for each database.\r\n",
                "1.\tIf 0 or 2, this is not an issue and is normal.\r\n",
                "2.\tIf 4, this may indicate an uncommitted transaction is open and needs to be cleaned up. Check uncommitted transactions.sql.\r\n",
                "3.\tIf 6 or 9, this requires some attention to the replication or Availability Groups, respectively.\r\n",
                "4.\tIf other values, investigate accordingly. https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-2017\r\n",
                "5.\tMake sure autogrowth settings are NOT set to 1 MB on any files using Autogrow Change All 1 MB Growth Files.sql\r\n",
                "a.\tWe recommend changing the autogrowth settings to a fixed rate between 128 MB and 512 MB depending on the size of the database.\r\n",
                "6.\tRecord any autogrowth events from the past month using Autogrowth Events.sql.\r\n",
                "7.\tUse Space in files.sql to report any files short on space that are in danger of an autogrowth event.\r\n",
                "a.\tWe typically report any files with less than 5% free space available, but if the server is checked less frequently than once per month, we want to report anything with less than 10% free space available and also want to manually grow that file to prevent autogrowth events until the next cycle of health checks.\r\n",
                "b.\tAre any log files full? This would indicate FULL recovery + no TLOG backups.\r\n",
                "8.\tFor SharePoint environments, evaluate the correct Recovery model for databases. This cannot be reliably done by database name, so execute the following .ps1 script on a web or app server in the sharepoint farm, or the SQL server if it serves as a single-server farm.\r\n",
                "a.\tSharepoint Databases.ps1\r\n",
                "b.\thttps://technet.microsoft.com/en-us/library/cc678868(v=office.16).aspx\r\n",
                "9.\tVLF Counts\r\n",
                "a.\tCheck the number of VLF’s in all database using VLFs Analysis.sql. Too many VLF’s can be bad for the performance of restore operations and slow recovery operations include the amount of time it takes for a database to come online after a SQL restart. Take a transaction log backup to empty the log file as much as possible, and then shrink the transaction to near-zero using a shrink operation.  Re-grow the log file back to an appropriate size in 8000MB increments. The script handles this.\r\n",
                "10.\tOwnership\r\n",
                "a.\tIndividual employees should not own objects on SQL Server.  In the event their account is LOCKED or DISABLED it could cause problems. Look for this especially on newly created databases.\r\n",
                "i.\tDatabase Ownership.sql\r\n",
                "ii.\tJob Owners.sql\r\n",
                "11.\tCheck tempdb data file size – they should all be the same size and have the same autogrowth setting. The initial TempDB data files sizes should be equal to the current TempDB data file sizes.\r\n",
                "i.\tFollow the recommendations in Tempdb Data Files.sql\r\n",
                "ii.\tPre-grow tempdb data files large so that they never autogrow. If this is a constant issue that cannot be fixed with large initial TempDB sizes (this is rare), consider 1117 – Grow All Files in a FileGroup Equally in SQL versions prior to SQL 2016. \r\n",
                "12.\tAge of Statistics –update indexes with outdated statistics (typically by business cycle =  monthly)\r\n",
                "i.\tStats Out of Date whileloop.sql to review all databases.\r\n",
                "13.\tIf we have replication, check the Replication Monitor in SSMS. Look for recent errors.\r\n",
                "a.\tVerify that “there are no replicated transactions available” for replication scenarios that are nightly snapshot only.\r\n",
                "b.\tVerify no error messages for other replication scenarios.\r\n",
                "14.\tFor SQL 2016+, turn on Query Store on any performance-sensitive database. Use default settings. Set to read/write.\r\n",
                "a.\tselect name, is_query_store_on from sys.databases \r\n",
                "15.\tUse last known DBCC CHECKDB.sql to verify that a CHECKDB has been run on all databases in at least the last month. If not, we need to examine maintenance plans."
            ],
            "metadata": {
                "azdata_cell_guid": "9513cc99-a495-4035-b2cd-062dca025e25"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--Database settings infor.sql\r\n",
                "PRINT('Database Settings Output Window')\r\n",
                "IF OBJECT_ID('tempdb..#DBSettings') IS NOT NULL\r\n",
                "    BEGIN\r\n",
                "\t   DROP TABLE #DBSettings;\r\n",
                "    END;\r\n",
                "GO\r\n",
                "select \r\n",
                "\tname\r\n",
                ",\t[compatibility_level]\t\r\n",
                ",\t[dbstate] = case when state_desc = 'online' and is_read_only = 1 then state_desc + ' ' +'(Read-Only)' else state_desc end \t\t\r\n",
                ",\trecovery_model_desc\r\n",
                ",\tpage_verify_option_desc\r\n",
                ",\tuser_access_desc\t\t\t\t--should be MULTI_USER\r\n",
                ",\tis_auto_close_on\t\t\t\t--should be 0\r\n",
                ",\tis_auto_shrink_on\t\t\t\t--should be 0\r\n",
                ",\tis_auto_create_stats_on\t\t\t--should be 1 except for some SharePoint db's\r\n",
                ",\tis_auto_update_stats_on\t\t\t--should be 1 except for some SharePoint db's\r\n",
                ",\tis_auto_update_stats_async_on\t--should be 1 except for some SharePoint db's\r\n",
                ",\tlog_reuse_wait\r\n",
                ",\tlog_reuse_wait_desc\r\n",
                ",\ttarget_recovery_time_in_seconds\r\n",
                ",\tProductMajorVersion\t\t\t\t= SERVERPROPERTY('ProductMajorVersion')\r\n",
                ",\tis_trustworthy_on\r\n",
                "into #DBSettings\r\n",
                "from sys.databases;\r\n",
                "\r\n",
                "--Compatibility Level Check\r\n",
                "WITH cteDB (Database_Name, [compatibility_level], State, Up_To_Date)\r\n",
                "AS (\r\n",
                "SELECT \r\n",
                " \tDatabase_Name\t\t\t= name\r\n",
                ",\t[Compatibility Level]\t= [compatibility_level] --should be latest (130 = SQL2016, 120 = SQL2014, 110 = SQL2012, 100 = SQL2008, 90 = SQL2005)\r\n",
                ",\t[State]\t\t\t\t\t= dbstate\t\t\r\n",
                ",\tUp_To_Date\t\t\t\t= cast(ProductMajorVersion as char(2)) + '0'\r\n",
                "from #DBSettings\r\n",
                ")\r\n",
                "select\r\n",
                "\tcteDB.*\r\n",
                ",\t[SQL Server Version]\t= SERVERPROPERTY('ProductVersion')\r\n",
                ",\t[Alter]\t\t\t\t\t= CASE WHEN Up_To_Date is not null THEN 'ALTER DATABASE [' + Database_Name +'] SET COMPATIBILITY_LEVEL = ' + LEFT(convert(varchar(15), SERVERPROPERTY('ProductVersion')),2) + '0;' ELSE NULL END\r\n",
                ",\t[Revert]\t\t\t\t= CASE WHEN Up_To_Date is not null THEN 'ALTER DATABASE [' + Database_Name +'] SET COMPATIBILITY_LEVEL = ' + convert(char(3), [compatibility_level]) + ';' ELSE NULL END\r\n",
                "from cteDB\r\n",
                "WHERE Up_To_Date <> [compatibility_level]\r\n",
                "and state <> 'OFFLINE'\r\n",
                "order by [Database_Name];\r\n",
                "\r\n",
                "\r\n",
                "--Databases where page verify option is not CHECKSUM\r\n",
                "--Changing this setting does not instantly put a checksum on every page. Need to do an index REBUILD of all objets to get CHECKSUMS in place, or, it'll happen slowly over time as data is written.\r\n",
                "select\r\n",
                " \t[Database Name]\t\t\t= name\r\n",
                ",\t[Page Verify Option]\t= page_verify_option_desc\r\n",
                ",\t[Message]\t\t\t\t= 'Page Verify Option MUST be CHECKSUM!'\r\n",
                ",\t[Alter]\t\t\t\t\t= 'ALTER DATABASE [' + name +'] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT; --Need to rebuild indexes on all objects in DB to take effect '\r\n",
                ",\t[Revert]\t\t\t\t= 'ALTER DATABASE [' + name +'] SET PAGE_VERIFY ' + page_verify_option_desc COLLATE DATABASE_DEFAULT + ' WITH NO_WAIT;'\r\n",
                ",\t[State]\t\t\t\t\t= dbstate\t\t\r\n",
                "from #DBSettings\r\n",
                "where page_verify_option_desc <> 'CHECKSUM'\r\n",
                "ORDER BY name;\r\n",
                "\r\n",
                "--Databases where auto-close and/or auto-shrink is enabled. \r\n",
                "--Strongly recommend NEVER enabling either of these two settings.\r\n",
                "select \r\n",
                " \t[Database Name]\t\t\t= name\r\n",
                ",\t[Is Auto Close On]\t\t= is_auto_close_on\t\t--should be 0\r\n",
                ",\t[Is Auto Shrink On]\t\t= is_auto_shrink_on\t\t--should be 0\r\n",
                ",\t[Alter]\t\t\t\t\t= CASE\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN is_auto_close_on = 1 THEN 'ALTER DATABASE [' + name + '] SET AUTO_CLOSE OFF WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN is_auto_shrink_on = 1 THEN 'ALTER DATABASE [' + name + '] SET AUTO_SHRINK OFF WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN is_auto_close_on = 1 AND is_auto_shrink_on = 1 THEN 'ALTER DATABASE [' + name + '] SET AUTO_CLOSE OFF WITH NO_WAIT; ALTER DATABASE [' + name + '] SET AUTO_SHRINK OFF WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t  ELSE 'N/A'\r\n",
                "\t\t\t\t\t\t\t  END\r\n",
                ",\t[Revert]\t\t\t\t= CASE\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN is_auto_close_on = 1 THEN 'ALTER DATABASE [' + name + '] SET AUTO_CLOSE ON WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN is_auto_shrink_on = 1 THEN 'ALTER DATABASE [' + name + '] SET AUTO_SHRINK ON WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN is_auto_close_on = 1 AND is_auto_shrink_on = 1 THEN 'ALTER DATABASE [' + name + '] SET AUTO_CLOSE ON WITH NO_WAIT; ALTER DATABASE [' + name + '] SET AUTO_SHRINK ON WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t  ELSE 'N/A'\r\n",
                "\t\t\t\t\t\t\t  END\r\n",
                ",\t[State]\t\t\t\t\t= dbstate\t\r\n",
                "from #DBSettings\r\n",
                "where is_auto_close_on = 1\t\t\r\n",
                "   OR is_auto_shrink_on\t= 1\t\r\n",
                "ORDER BY name;\r\n",
                "\r\n",
                "--Databases where auto create and/or auto update stats is disabled\r\n",
                "--Recommend enabling these settings.\r\n",
                "select \r\n",
                "\t[Database Name]\t\t\t\t\t= name\r\n",
                ",\t[Is Auto Create Stats On]\t\t= is_auto_create_stats_on\t\t--should be 1 except for some SharePoint db's\r\n",
                ",\t[Is Auto Update Stats On]\t\t= is_auto_update_stats_on\t\t--should be 1 except for some SharePoint db's\r\n",
                ",\t[Is Auto Update Stats Async On]\t= is_auto_update_stats_async_on\t--should be 1 except for some SharePoint db's\r\n",
                ",\tProductVersion = SERVERPROPERTY('ProductVersion')\r\n",
                ",\t[Alter]\t\t\t\t\t\t\t= CASE\r\n",
                "\t\t\t\t\t\t\t\t\t\t\tWHEN is_auto_create_stats_on = 0 THEN 'ALTER DATABASE [' + name + '] SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t\t\t\t\tWHEN is_auto_update_stats_on = 0 THEN 'ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t\t\t\t\tWHEN is_auto_update_stats_async_on = 0 THEN 'ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t\t\t\t\tWHEN is_auto_create_stats_on = 0 AND is_auto_update_stats_on = 0 THEN 'ALTER DATABASE [' + name + '] SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT; ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t\t\t\t\tWHEN is_auto_create_stats_on = 0 AND is_auto_update_stats_async_on = 0 THEN 'ALTER DATABASE [' + name + '] SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT; ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t\t\t\t\tWHEN is_auto_update_stats_on = 0 AND is_auto_update_stats_async_on = 0 THEN 'ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT; ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t\t\t\t\tWHEN is_auto_create_stats_on = 0 AND is_auto_update_stats_on = 0 AND is_auto_update_stats_async_on = 0 THEN 'ALTER DATABASE [' + name + '] SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT; ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT; ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t\t\t  ELSE 'N/A'\r\n",
                "\t\t\t\t\t\t\t\t\t  END\r\n",
                ",\t[Revert]\t\t\t\t\t\t= CASE\r\n",
                "\t\t\t\t\t\t\t\t\t\t\tWHEN is_auto_create_stats_on = 0 THEN 'ALTER DATABASE [' + name + '] SET AUTO_CREATE_STATISTICS OFF WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t\t\t\t\tWHEN is_auto_update_stats_on = 0 THEN 'ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t\t\t\t\tWHEN is_auto_update_stats_async_on = 0 THEN 'ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS_ASYNC OFF WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t\t\t\t\tWHEN is_auto_create_stats_on = 0 AND is_auto_update_stats_on = 0 THEN 'ALTER DATABASE [' + name + '] SET AUTO_CREATE_STATISTICS OFF WITH NO_WAIT; ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t\t\t\t\tWHEN is_auto_create_stats_on = 0 AND is_auto_update_stats_async_on = 0 THEN 'ALTER DATABASE [' + name + '] SET AUTO_CREATE_STATISTICS OFF WITH NO_WAIT; ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS_ASYNC OFF WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t\t\t\t\tWHEN is_auto_update_stats_on = 0 AND is_auto_update_stats_async_on = 0 THEN 'ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT; ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS_ASYNC OFF WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t\t\t\t\tWHEN is_auto_create_stats_on = 0 AND is_auto_update_stats_on = 0 AND is_auto_update_stats_async_on = 0 THEN 'ALTER DATABASE [' + name + '] SET AUTO_CREATE_STATISTICS OFF WITH NO_WAIT; ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT; ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS_ASYNC OFF WITH NO_WAIT;'\r\n",
                "\t\t\t\t\t\t\t\t\t  ELSE 'N/A'\r\n",
                "\t\t\t\t\t\t\t\t\t  END\r\n",
                ",\t[State]\t\t\t\t\t\t\t= dbstate\t\r\n",
                "from #DBSettings\r\n",
                "where is_auto_create_stats_on = 0\r\n",
                "   OR is_auto_update_stats_on = 0\r\n",
                "   OR (is_auto_update_stats_async_on = 0 and ProductMajorVersion >= 12)\r\n",
                "ORDER BY name;\r\n",
                "\r\n",
                "--Databases log reuse wait and description\r\n",
                "--Expected types: NOTHING, CHECKPOINT, LOG_BACKUP, ACTIVE_BACKUP_OR_RESTORE, DATABASE_SNAPSHOT_CREATION, AVAILABILITY_REPLICA, OLDEST_PAGE, XTP_CHECKPOINT\r\n",
                "--Potentially problematic if long-lasting, research: DATABASE_MIRRORING, REPLICATION, ACTIVE_TRANSACTION, LOG_SCAN, OTHER_TRANSIENT \r\n",
                "select \r\n",
                "\t[Database Name]\t\t= name\r\n",
                ",\t[Log Reuse Wait]\t= log_reuse_wait\r\n",
                ",\t[Description]\t\t= log_reuse_wait_desc\r\n",
                ",\t[State]\t\t\t\t= dbstate\t\t\r\n",
                ",\t[Recovery Model]\t= recovery_model_desc\r\n",
                "from #DBSettings\r\n",
                "where log_reuse_wait_desc not in ('NOTHING', 'CHECKPOINT', 'LOG_BACKUP', 'ACTIVE_BACKUP_OR_RESTORE', 'DATABASE_SNAPSHOT_CREATION', 'AVAILABILITY_REPLICA', 'OLDEST_PAGE', 'XTP_CHECKPOINT')\r\n",
                "ORDER BY name;\r\n",
                "\r\n",
                "--Databases where target recovery time in seconds is < 60 (only applies to 2012+), and recommended in 2016+\r\n",
                "--Make sure latest patches are applied first.\r\n",
                "select \r\n",
                "\t[Database Name]\t\t\t= name\r\n",
                ",\t[Target Recovery Time]\t= target_recovery_time_in_seconds\r\n",
                ",\t[Alter]\t\t\t\t\t= 'ALTER DATABASE [' + name + '] SET TARGET_RECOVERY_TIME = 60 SECONDS WITH NO_WAIT'\r\n",
                ",\t[Revert]\t\t\t\t= 'ALTER DATABASE [' + name + '] SET TARGET_RECOVERY_TIME = ' + CAST(target_recovery_time_in_seconds AS VARCHAR(3)) + ' SECONDS WITH NO_WAIT'\r\n",
                ",\t[State]\t\t\t\t\t= dbstate\t\t\r\n",
                ",\tProductMajorVersion\r\n",
                "from #DBSettings \r\n",
                "where target_recovery_time_in_seconds = 0\r\n",
                "and cast(ProductMajorVersion as int) >= 13\r\n",
                "and [name] <> 'master'\r\n",
                "ORDER BY name;\r\n",
                "\r\n",
                "\r\n",
                "--Databases should only have the Trustworthy setting enabled if necessary. The msdb system database is Trustworthy by default. \r\n",
                "select \r\n",
                "\t[Database Name]\t\t\t= name\r\n",
                ",\tis_trustworthy_on\r\n",
                "from #DBSettings\r\n",
                "where is_trustworthy_on = 1\r\n",
                "and name <> 'msdb'\r\n",
                "ORDER BY name;"
            ],
            "metadata": {
                "azdata_cell_guid": "cab82c86-7e90-469e-bf52-fd0a3dff5423"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--uncommitted transactions.sq\r\n",
                "--Returns information on uncommitted transactions\r\n",
                "PRINT('Uncommitted Transactions Output Window')\r\n",
                "select * from sys.dm_tran_active_transactions tat \r\n",
                "inner join sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id"
            ],
            "metadata": {
                "azdata_cell_guid": "b70c6126-2cb4-4449-89ca-9f0e5e5c818a"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--Autogrow Change All 1 MB Growth Files.sql\r\n",
                "--Run in Results to Text mode\r\n",
                "PRINT('Databses with Default Autogrowth Settings in Use')\r\n",
                "USE [master]\r\n",
                "GO\r\n",
                "select \r\n",
                "Alter_Autogrowth_Rates = case when mf.type_desc = 'ROWS' \r\n",
                "\tthen 'ALTER DATABASE ['+d.name+'] MODIFY FILE ( NAME = N'''+ mf.name+ ''', FILEGROWTH = 256MB );\r\n",
                "GO' \r\n",
                "\telse 'ALTER DATABASE ['+d.name+'] MODIFY FILE ( NAME = N'''+ mf.name+ ''', FILEGROWTH = 256MB );\r\n",
                "GO' \r\n",
                "\tend\r\n",
                ", mf.*\r\n",
                "FROM sys.databases d\r\n",
                "inner join sys.master_files mf\r\n",
                "on d.database_id = mf.database_id\r\n",
                "where (d.state_desc = 'ONLINE')\r\n",
                "and (d.is_read_only = 0)\r\n",
                "and ((mf.is_percent_growth = 0 and growth = 128) or (mf.is_percent_growth = 1))\r\n",
                "/*\r\n",
                "ALTER DATABASE [model] MODIFY FILE ( NAME = N'modeldev', FILEGROWTH = 512000KB )\r\n",
                "*/\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "9b60e8ec-2f87-4dfd-9511-d14222013ee3"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--Autogrowth Events.sql\r\n",
                "\r\n",
                "--The trace automatically finds _n files, strip off the _nnn. For example, will read all data from log_14.trc, log_15.trc, log_16.trc, log_17.trc, etc. \r\n",
                "--Default trace files are limited to 20mb, and there are at most five of them, so we have 100mb of history. Depends on activity to determine how far back that goes.\r\n",
                "PRINT('Autogrowth Events Output Window')\r\n",
                "\r\n",
                "\tSELECT \r\n",
                "\t\tDBName\t\t\t\t=\tg.DatabaseName\r\n",
                "\t,\tDBFileName\t\t\t=\tmf.physical_name\r\n",
                "\t,\tFileType\t\t\t=\tCASE mf.type WHEN 0 THEN 'Row' WHEN 1 THEN 'Log' WHEN 2 THEN 'FILESTREAM' WHEN 4 THEN 'Full-text' END\r\n",
                "\t,\tEventName\t\t\t=\tte.name\r\n",
                "\t,\tEventGrowthMB\t\t=\tconvert(decimal(19,2),g.IntegerData*8/1024.) -- Number of 8-kilobyte (KB) pages by which the file increased.\r\n",
                "\t,\tEventTime\t\t\t=\tg.StartTime\r\n",
                "\t,\tEventDurationSec\t=\tconvert(decimal(19,2),g.Duration/1000./1000.) -- Length of time (in milliseconds) necessary to extend the file.\r\n",
                "\t,\tCurrentAutoGrowthSet=\tCASE\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN mf.is_percent_growth = 1\r\n",
                "\t\t\t\t\t\t\t\t\tTHEN CONVERT(char(2), mf.growth) + '%' \r\n",
                "\t\t\t\t\t\t\t\t\tELSE CONVERT(varchar(30), convert(decimal(19,2), mf.growth*8./1024.)) + 'MB'\r\n",
                "\t\t\t\t\t\t\t\tEND\r\n",
                "\t,\tCurrentFileSizeMB\t=\tconvert(decimal(19,2),mf.size*\t8./1024.)\r\n",
                "\t,\td.Recovery_model_Desc\r\n",
                "\t--,\t@tracepath\t\r\n",
                "\t--,\tMaxFileSizeMB\t\t=\tCASE WHEN mf.max_size = -1 THEN 'Unlimited' ELSE convert(varchar(30), convert(decimal(19,2),mf.max_size*8./1024.)) END\r\n",
                "\t--select count(1)\r\n",
                "\tFROM fn_trace_gettable((select substring((select path from sys.traces where is_default =1), 0, charindex('\\log_', (select path from sys.traces where is_default =1),0)+4)\t+ '.trc'), default) g\r\n",
                "\tcross apply sys.trace_events te \r\n",
                "\tinner join sys.master_files mf\r\n",
                "\ton mf.database_id = g.DatabaseID\r\n",
                "\tand g.FileName = mf.name\r\n",
                "\tinner join sys.databases d\r\n",
                "\ton d.database_id = g.DatabaseID\r\n",
                "\tWHERE g.eventclass = te.trace_event_id\r\n",
                "\tand\t\tte.name in ('Data File Auto Grow','Log File Auto Grow')\r\n",
                "\tand\t\tg.StartTime > dateadd(d, -7, sysdatetime()) \r\n",
                "\t--GROUP BY StartTime,Databaseid, Filename, IntegerData, Duration\r\n",
                "\torder by StartTime desc;\r\n",
                "\r\n",
                "\tSELECT servicename, status_desc, last_startup_time FROM sys.dm_server_services;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "41434706-54cf-46ca-a595-125dae6309a6"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--Space in files.sql\r\n",
                "--Observe space in data and log files\r\n",
                "--Pregenerated scripts to shrink and/or grow files. Do not shrink unless an unusual/emergency situation has created an overgrown log file. \r\n",
                "--See also \"vlfs analysis.sql\"\r\n",
                "PRINT('Space in Files Output Window')\r\n",
                "\r\n",
                "DECLARE @TempTable TABLE\r\n",
                "( DatabaseName varchar(128)\r\n",
                ",recovery_model_desc varchar(50)\r\n",
                ",DatabaseFileName varchar(500)\r\n",
                ",FileLocation varchar(500)\r\n",
                ",FileId int\r\n",
                ",type_desc varchar(50)\r\n",
                ",FileSizeMB decimal(19,2)\r\n",
                ",SpaceUsedMB decimal(19,2)\r\n",
                ",AvailableMB decimal(19,2)\r\n",
                ",FreePercent decimal(19,2)\r\n",
                ",shrinkTSQL nvarchar(4000)\r\n",
                ",growTSQL nvarchar(4000)\r\n",
                ")\r\n",
                "\r\n",
                "--Optional filter for small/unused databases at bottom\r\n",
                "\r\n",
                "INSERT INTO @TempTable\r\n",
                "exec sp_MSforeachdb  'use [?]; \r\n",
                "select *\r\n",
                ", shrinkTSQL\t=\t''USE [?];\r\n",
                "DBCC SHRINKFILE (N''''''+ DatabaseFileName_______ COLLATE SQL_Latin1_General_CP1_CI_AS +'''''' , 0, TRUNCATEONLY)''\r\n",
                ", growTSQL = ''ALTER DATABASE [''+DatabaseName_____________ COLLATE SQL_Latin1_General_CP1_CI_AS+''] \r\n",
                "MODIFY FILE ( NAME = N''''''+DatabaseFileName_______ COLLATE SQL_Latin1_General_CP1_CI_AS +''''''\r\n",
                ", '' + CASE WHEN FileSizeMB < 100 THEN ''SIZE = ''+STR(FileSizeMB+64)\r\n",
                "\t\t\tWHEN FileSizeMB < 1000 THEN ''SIZE = ''+STR(FileSizeMB+256)\r\n",
                "\t\t\tWHEN FileSizeMB < 10000 THEN ''SIZE = ''+STR(FileSizeMB+1024)\r\n",
                "\t\t\tWHEN FileSizeMB < 40000 THEN ''SIZE = ''+STR(FileSizeMB+4092)\r\n",
                "\t\t\tELSE ''SIZE = ''+STR(FileSizeMB+(FileSizeMB*.05)) END +''MB )''\r\n",
                "FROM (\r\n",
                "SELECT \r\n",
                "  ''DatabaseName_____________'' = d.name\r\n",
                ", Recovery\t\t\t= d.recovery_model_desc\r\n",
                ", ''DatabaseFileName_______'' = df.name\r\n",
                ", Location\t\t\t= df.physical_name\r\n",
                ", File_ID\t\t\t= df.File_ID\r\n",
                ", df.type_desc\r\n",
                ", FileSizeMB\t\t= CAST(size/128.0 as Decimal(9,2))\r\n",
                ", SpaceUsedMB\t\t= CAST(CAST(FILEPROPERTY(df.name, ''SpaceUsed'') AS int)/128.0 as Decimal(9,2))\r\n",
                ", AvailableMB\t\t= CAST(size/128.0 - CAST(FILEPROPERTY(df.name, ''SpaceUsed'') AS int)/128.0 as Decimal(9,2))\r\n",
                ", FreePercent\t\t= CAST((((size/128.0) - (CAST(FILEPROPERTY(df.name, ''SpaceUsed'') AS int)/128.0)) / (size/128.0) ) * 100. as Decimal(9,2))\r\n",
                "\r\n",
                " FROM sys.database_files df\r\n",
                " CROSS APPLY sys.databases d\r\n",
                " WHERE d.database_id = DB_ID() \r\n",
                " AND d.is_read_only = 0\r\n",
                " AND df.size > 0) x;\r\n",
                "'\r\n",
                "\r\n",
                "SELECT\r\n",
                "    *\r\n",
                "FROM @TempTable\r\n",
                "--where [FreePercent] < 5  and FileSizeMB > 6 --Optional filter for small/unused databases \r\n",
                "\r\n",
                "ORDER BY FreePercent asc, DatabaseName, FileId\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "4b4c7afe-78fa-4ca7-bfd7-5b4cda37fd8a"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--VLFs Analysis.sql\r\n",
                "\r\n",
                "--TODO: Rewrite this for SQL 2016 SP2+ and SQL 2017 using sys.dm_db_log_stats instead of DBCC LOGINFO\r\n",
                "\r\n",
                "--If no TSQL scripts generated in the Messages tab, then no log files found with need for VLF maint.\r\n",
                "\r\n",
                "--shows the number of VLF's. CreateLSN=0 for the original created files.\r\n",
                "--mf.size /1024, *8 to get MB \r\n",
                "--LogInfo filesize is in bytes, so /1024./1024. to get MB\r\n",
                "--Ideally 1 VLF per 500MB. \r\n",
                "--IF log >8 GB, Recreate log in 8000 MB increments.\r\n",
                "--A single VLF larger than 4GB could be a problem with backup compression - https://techcommunity.microsoft.com/t5/SQL-Server/Backup-Compression-for-TDE-enabled-Databases-Important-fixes-in/ba-p/385593?advanced=false&collapse_discussion=true&q=MAXTRANSFERSIZE&search_type=thread\r\n",
                "\r\n",
                "--Shrink/regrow step only works for databases with one log file. Why do you have more than one log file anyway? Stop. Think. Ask yourself.\r\n",
                "\r\n",
                "PRINT('VLF Analysis Output Window')\r\n",
                "BEGIN TRY\r\n",
                "IF EXISTS (select * from tempdb.sys.objects where name like '#Log%')\r\n",
                "DROP TABLE #Log\r\n",
                "END TRY\r\n",
                "BEGIN CATCH\r\n",
                "END CATCH\r\n",
                "\r\n",
                "SET NOCOUNT ON\r\n",
                "\r\n",
                "Create Table #Log(\r\n",
                "\tRecoveryUnitId bigint  null,--SQL 2012 and above only, comment out for <=SQL 2008\r\n",
                "    FileID      int not null\r\n",
                "  , FileSize_b    bigint not null\r\n",
                "  , StartOff\tbigint not null\r\n",
                "  , FSeqNo      bigint not null\r\n",
                "  , [Status]    int not null\r\n",
                "  , Parity      bigint not null\r\n",
                "  , CreateLSN   decimal(30,0) not null\r\n",
                ");  \r\n",
                "Exec sp_MSforeachdb N'Use [?]; \r\n",
                "Insert Into #Log  \r\n",
                "Exec sp_executesql N''DBCC LogInfo([?]) with no_infomsgs''; \r\n",
                "declare @Co bigint, @Avg_MB decimal(19,2), @LCnt int, @Log_MB decimal(19,2) , @T nvarchar(4000), @Max_MB bigint\r\n",
                "select @Log_MB =sum(convert(bigint, mf.size))*8/1024 FROM sys.master_files mf where type=1 and state=0 and db_id()=mf.database_id\r\n",
                "select @Co=Count_big(StartOff) ,\t@Avg_MB=@Log_MB / Count_big(StartOff), @Max_MB=MAX(FileSize_b/1048576.) from #Log\r\n",
                "if (((@Avg_MB <= 64 OR @Avg_MB > 4000) AND @Log_MB > 1024) AND (@Log_MB<8000)) AND EXISTS \r\n",
                "(select 1 FROM sys.databases WHERE is_read_only=0 and state=0 and db_id()=database_id)\r\n",
                "BEGIN\r\n",
                "\t\tselect DBName= db_name(), Co=@Co, Size_MB=@Log_MB, Avg_MB=@Avg_MB\r\n",
                "SELECT @T= ''USE [''+d.name+''];\r\n",
                "CHECKPOINT\r\n",
                "GO\r\n",
                "DBCC SHRINKFILE (N''''''+mf.name+'''''' , 0, TRUNCATEONLY)\r\n",
                "GO\r\n",
                "USE [master]\r\n",
                "--Original Size ''+convert(varchar(1000), @Log_MB) +'' MB\r\n",
                "ALTER DATABASE [''+d.name+''] MODIFY FILE ( NAME=N''''''+mf.name+'''''', SIZE=''+convert(varchar(30), mf.size*8/1024)+''MB );\r\n",
                "GO\r\n",
                "''\r\n",
                "FROM sys.databases d inner join sys.master_files mf on d.database_id=mf.database_id where type_desc=''log'' and db_name()=d.name\r\n",
                "IF @T IS NOT NULL BEGIN\r\n",
                "\tset @T=@T+''\r\n",
                "''\r\n",
                "\tIF @Co>(@Log_MB/100) and @Co>50\r\n",
                "\tSELECT DB_NAME()+'' log file too many VLFs.''\r\n",
                "\tIF (@Avg_MB<64 AND @Log_MB>1024) and @Co>50\r\n",
                "\tSELECT DB_NAME()+'' log file VLFs too small.''\r\n",
                "\tIF (@Max_MB >= 4096) \r\n",
                "\tSELECT DB_NAME()+'' single log file VLF >4000MB''\r\n",
                "\t\r\n",
                "\tprint  @T\r\n",
                "END\t\r\n",
                "END\r\n",
                "Truncate Table #Log;'\r\n",
                "\r\n",
                "\r\n",
                "--Had to split this out because of sp_MSforeachdb char limits.\r\n",
                "Exec sp_MSforeachdb N'Use [?]\r\n",
                "Insert Into #Log  \r\n",
                "Exec sp_executesql N''DBCC LogInfo([?]) with no_infomsgs''; \r\n",
                "DECLARE @Co bigint, @Avg_MB decimal(19,2), @LCnt int, @Log_MB decimal(19,2) , @Log_curr bigint, @T nvarchar(4000), @LNeed int, @Ac bigint, @Max_MB bigint\r\n",
                "SELECT @Log_MB=sum(convert(bigint, mf.size))*8./1024. FROM sys.master_files mf where type=1 and state=0 and db_id()=mf.database_id\r\n",
                "SELECT @Co=Count_big(StartOff),@Avg_MB=@Log_MB/Count_big(StartOff), @Max_MB=MAX(FileSize_b/1048576.) from #Log\r\n",
                "IF (@Avg_MB>1024 OR (@Avg_MB<64 AND @Log_MB > 1024)) AND (@Log_MB>8000) AND EXISTS (select 1 FROM sys.databases WHERE is_read_only=0 and state=0 and db_id()=database_id)\r\n",
                "BEGIN\r\n",
                "SELECT @LCnt=1, @Ac=0\r\n",
                "SELECT top 1 @T=''USE [''+d.name+'']\r\n",
                "CHECKPOINT\r\n",
                "GO\r\n",
                "DBCC SHRINKFILE (N''''''+mf.name+'''''' , 0, TRUNCATEONLY)\r\n",
                "GO\r\n",
                "USE master\r\n",
                "GO\r\n",
                "--Orig ''+convert(varchar(1000),@Log_MB) +'' MB\r\n",
                "''\r\n",
                "FROM sys.databases d join sys.master_files mf on d.database_id=mf.database_id where type_desc=''log'' and db_name()=d.name\r\n",
                "select @LNeed=@Log_MB/8000\r\n",
                "IF (@Log_MB%8000)>=0 \r\n",
                "SELECT @LNeed=@LNeed+1 \r\n",
                "WHILE (@LCnt<=@LNeed) BEGIN\r\n",
                "SET @Log_curr=CASE WHEN @LCnt=1 and @Log_MB<=8000 THEN @Log_MB\r\n",
                "WHEN @Log_MB-(8000*@LCnt)>0 THEN 8000 \r\n",
                "WHEN @Log_MB-(8000*@LCnt)<0 THEN @Log_MB-(8000*(@LCnt-1))\r\n",
                "END\t\t\t\t\r\n",
                "select @Ac=@Ac+@Log_curr\r\n",
                "if @Log_curr>0\r\n",
                "select top 1 @T=@T+''ALTER DATABASE [''+d.name+''] MODIFY FILE ( NAME=N''''''+mf.name+'''''', SIZE =''+convert(varchar(1000), @Ac)+'' MB );\r\n",
                "GO\r\n",
                "''\r\n",
                "FROM sys.databases d join sys.master_files mf on d.database_id=mf.database_id where type_desc=''log'' and db_name()=d.name\r\n",
                "SELECT @LCnt=@LCnt+1 \r\n",
                "END \r\n",
                "END\r\n",
                "IF @T IS NOT NULL BEGIN\r\n",
                "set @T=@T+''\r\n",
                "''\r\n",
                "IF @Co>(@Log_MB/100) and @Co>50 SELECT DB_NAME()+'' excessive VLF count.'';\r\n",
                "IF @Avg_MB<64 SELECT DB_NAME()+'' VLFs too small'';\r\n",
                "IF @Max_MB>=4096 SELECT DB_NAME()+'' single VLF >4GB'';\r\n",
                "print @T;\r\n",
                "END\r\n",
                "Truncate Table #Log;'\r\n",
                "\r\n",
                "Drop Table #Log;\r\n",
                "\r\n",
                "\r\n",
                "/*\r\n",
                "More reference\r\n",
                "A VLF is comprised of 1 or more log blocks each of which is an integer multiple of 512 bytes, but no more than 60KB total in size.\r\n",
                "----http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/\r\n",
                "----http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/\r\n",
                "----https://blogs.msdn.microsoft.com/alwaysonpro/2013/09/27/performing-transaction-log-backups-using-alwayson-availability-group-read-only-secondary-replicas-part-1/\r\n",
                "----https://www.red-gate.com/simple-talk/sql/database-administration/sql-server-transaction-log-fragmentation-a-primer/\r\n",
                "---\"If you need a 2GB log then just create that as one step. \r\n",
                "---If you need a 20GB log, create that as 8GB, then extend it to 16GB and then to 20GB\"\r\n",
                "--Optimal size for Avg_MB for VLF's is 500MB.\r\n",
                "\r\n",
                "--displays each transaction log size and space used. \r\n",
                "--Dbcc sqlperf (logspace)  --replaced, look for \"space in log files.sql\"\r\n",
                "\r\n",
                "\r\n",
                "*/\r\n",
                "\r\n",
                "/*\r\n",
                "\r\n",
                "--Script to test database, create suboptimal VLF's\r\n",
                "USE [w]\r\n",
                "GO\r\n",
                "DBCC SHRINKFILE (N'w2016_log' , 0, TRUNCATEONLY)\r\n",
                "GO\r\n",
                "\r\n",
                "USE [master]\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1001MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1002MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1003MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1004MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1005MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1006MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1007MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1008MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1009MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1010MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1011MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1012MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1013MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1014MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1015MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1016MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1017MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1018MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1019MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1020MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1021MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1022MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1023MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1024MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1025MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1026MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1027MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1028MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1029MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1030MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1031MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1032MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1033MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1034MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1035MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1036MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1037MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1038MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1039MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1040MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1041MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1042MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1043MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1044MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1045MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1046MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1047MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1048MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1049MB );\r\n",
                "GO\r\n",
                "--Won't show up in query above until here\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=1050MB );\r\n",
                "GO\r\n",
                "--further testing the 8 GB growth pattern\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=8000MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=8001MB );\r\n",
                "GO\r\n",
                "ALTER DATABASE [w] MODIFY FILE ( NAME=N'w2016_log', SIZE=16001MB );\r\n",
                "GO\r\n",
                "*/\r\n",
                "\r\n",
                "/*\r\n",
                "\r\n",
                "--Sample usage\r\n",
                "\r\n",
                "USE [Tfs_Warehouse]\r\n",
                "DBCC SHRINKFILE (N'Tfs_Warehouse_log' , 0, TRUNCATEONLY);\r\n",
                "GO\r\n",
                "USE [master]\r\n",
                "--Original Size 1024.00 MB\r\n",
                "ALTER DATABASE [Tfs_Warehouse] MODIFY FILE ( NAME=N'Tfs_Warehouse_log', SIZE=1024MB );\r\n",
                "GO\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "USE [Tfs_SparkyBox]\r\n",
                "DBCC SHRINKFILE (N'Tfs_SparkyBox_log' , 0, TRUNCATEONLY);\r\n",
                "GO\r\n",
                "USE [master]\r\n",
                "--Original Size 356.00 MB\r\n",
                "ALTER DATABASE [Tfs_SparkyBox] MODIFY FILE ( NAME=N'Tfs_SparkyBox_log', SIZE=356MB );\r\n",
                "GO\r\n",
                "\r\n",
                "\r\n",
                "USE [Tfs_Configuration]\r\n",
                "DBCC SHRINKFILE (N'Tfs_Configuration_log' , 0, TRUNCATEONLY);\r\n",
                "GO\r\n",
                "USE [master]\r\n",
                "--Original Size 1024.00 MB\r\n",
                "ALTER DATABASE [Tfs_Configuration] MODIFY FILE ( NAME=N'Tfs_Configuration_log', SIZE=1024MB );\r\n",
                "GO\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "*/\r\n",
                "\t\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "36d49cab-0c9e-45ac-acf8-14eaa44f9b68"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--Database Ownership.sql\r\n",
                "--Find database owners that are not desired\r\n",
                "PRINT('Database Ownership Output Window')\r\n",
                "\r\n",
                "declare @Desired_DB_owner varchar(255) = 'sa' --'sa' is just an example, change to desired service account, example: domain\\accountname\r\n",
                "\r\n",
                "select \r\n",
                "\tdatabase_name = d.name\r\n",
                ",\tprincipal_name = SUSER_SNAME (d.owner_sid)\r\n",
                ",\tset_to_desired = 'alter authorization on database::[' + d.name + '] to [' + @Desired_DB_owner + ']' \r\n",
                ",\tset_to_current =  case when SUSER_SNAME (d.owner_sid) <> @Desired_DB_owner THEN 'alter authorization on database::[' + d.name + '] to [' + SUSER_SNAME (d.owner_sid) + ']' ELSE NULL END\r\n",
                ",\t* \r\n",
                "from sys.databases d\r\n",
                "where SUSER_SNAME (d.owner_sid) <> @Desired_DB_owner"
            ],
            "metadata": {
                "azdata_cell_guid": "1562be8c-395c-4531-afe9-6bb6cf20f8cd"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--Job Owners.sql\r\n",
                "PRINT('Job Owner Output Window')\r\n",
                "use msdb\r\n",
                "go\r\n",
                "--TODO Change @owner_login_name to desired SQL agent service account to own the job\r\n",
                "\r\n",
                "declare @Desired_job_owner varchar(255) = 'SPARKHOUND\\svcaccount' --'sa' is just an example, change to desired service account, example: domain\\accountname\r\n",
                "\r\n",
                "--sql 2005 and above\r\n",
                "select owner = SUSER_SNAME (j.owner_sid), jobname = j.name, j.job_id\r\n",
                ",\tchange_tsql = N'EXEC msdb.dbo.sp_update_job @job_id=N'''+convert(nvarchar(100), j.job_id)+N''', @owner_login_name=N'''+@Desired_job_owner+''''\r\n",
                ",\trevert_tsql = N'EXEC msdb.dbo.sp_update_job @job_id=N'''+convert(nvarchar(100), j.job_id)+N''', @owner_login_name=N'''+SUSER_SNAME (j.owner_sid)+''''\r\n",
                "from sysjobs j\r\n",
                "left outer join sys.server_principals  sp on j.owner_sid = sp.sid\r\n",
                "where \r\n",
                "\t(sp.name not in ('sa','distributor_admin','NT SERVICE\\ReportServer') \r\n",
                "\t and sp.name <> @Desired_job_owner\r\n",
                "\t and sp.name not like '##%')\r\n",
                "\tor sp.name is null \r\n",
                "\r\n",
                "/*\r\n",
                "--sql 2000\r\n",
                "select sp.name, j.name, j.job_id from msdb.dbo.sysjobs j\r\n",
                "left outer join master.dbo.syslogins sp on j.owner_sid = sp.sid\r\n",
                "where sp.name not in ('sa','distributor_admin') or sp.name is null\r\n",
                "--EXEC msdb.dbo.sp_update_job @job_id=N'8eab379e-958e-4576-92ae-b5999aeec01c', @owner_login_name=N'distributor_admin'\r\n",
                "*/\r\n",
                "\r\n",
                "/*\r\n",
                "--Sample usage\r\n",
                "\r\n",
                "EXEC msdb.dbo.sp_update_job @job_id=N'BDAFAC9B-1705-4E47-9C26-6C4B813CB165', @owner_login_name=N'sa'\r\n",
                "EXEC msdb.dbo.sp_update_job @job_id=N'987AF666-A516-4847-8BA3-73DE337CFF94', @owner_login_name=N'sa'\r\n",
                "EXEC msdb.dbo.sp_update_job @job_id=N'AEBF4C5C-EC6D-4635-96B6-797BF4AEEC62', @owner_login_name=N'sa'\r\n",
                "\r\n",
                "*/"
            ],
            "metadata": {
                "azdata_cell_guid": "f35d0bc9-51ed-4212-9603-df175cc0d4f9"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--Tempdb Data Files.sql\r\n",
                "--TempDB data files should all be the same size, same autogrowth settings\r\n",
                "--Only displays TempDB data files.\r\n",
                "PRINT('TempDB Data Files Output Window')\r\n",
                "USE tempdb\r\n",
                "GO\r\n",
                "DECLARE @cpu_count int;\r\n",
                "SELECT @cpu_count = cpu_count from sys.dm_os_sys_info;\r\n",
                "\r\n",
                "--data files\r\n",
                "select mf.name\r\n",
                ", CurrentSize_MB = (d.size*8.)/1024. --actual current file size\r\n",
                ", InitialSize_MB = (mf.size*8.)/1024. --initial file size\r\n",
                ", GrowthMb = (mf.growth*8.)/1024.\r\n",
                ", mf.is_percent_growth\r\n",
                ", MaxFileSizeMB = CASE WHEN mf.max_size > -1 THEN cast((mf.max_size*8.)/1024. as varchar(100)) ELSE 'Unlimited' END -- \"-1\" is unlimited\r\n",
                ", Recommendation = CASE WHEN d.size > mf.size THEN 'Increase TempDB Data files size to match or exceed current size.' + CHAR(10) ELSE '' END +\r\n",
                "CASE WHEN mf.size <> AVG(mf.size) OVER (PARTITION BY mf.database_id) THEN 'Set all TempDB Data files to the same initial size.' + CHAR(10) ELSE '' END + \r\n",
                "CASE WHEN d.size <> AVG(d.size) OVER (PARTITION BY mf.database_id) THEN 'Grow TempDB Data files to the same current size.' + CHAR(10) ELSE '' END +\r\n",
                "CASE WHEN mf.growth <> AVG(mf.growth) OVER (PARTITION BY mf.database_id) THEN 'Match all TempDB Data files autogrowth rates.' + CHAR(10) ELSE '' END +\r\n",
                "CASE WHEN mf.max_size <> AVG(mf.max_size) OVER (PARTITION BY mf.database_id) THEN 'Match all TempDB Data files max file size.' + CHAR(10) ELSE '' END +\r\n",
                "CASE WHEN count(mf.file_id) OVER (PARTITION BY mf.database_id) > @cpu_count THEN 'Too many TempDB Data files, reduce to '+cast(@cpu_count as varchar(3)) + ' or lower.' + CHAR(10) ELSE '' END\r\n",
                ", mf.physical_name\r\n",
                ", volume_letter = UPPER(vs.volume_mount_point)\r\n",
                ", file_system_type\r\n",
                ", drive_size_GB = (CONVERT(decimal(19,2), vs.total_bytes/1024./1024./1024. ))\r\n",
                ", drive_free_space_GB = (CONVERT(decimal(19,2), vs.available_bytes/1024./1024./1024. ))\r\n",
                ", drive_pct_free = (CONVERT(DECIMAL(5,2), vs.available_bytes * 100.0 / vs.total_bytes))\r\n",
                "from sys.master_files mf\r\n",
                "inner join tempdb.sys.database_files d\r\n",
                "on mf.file_id = d.file_id\r\n",
                "and mf.database_id = db_id()\r\n",
                "cross apply sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs --only return volumes where there is database file (data or log)\r\n",
                "where d.type_desc = 'rows'\r\n",
                "order by mf.file_id asc\r\n",
                "\r\n",
                "--log file\r\n",
                "select mf.name\r\n",
                ", CurrentSize_MB = (d.size*8.)/1024. --actual current file size\r\n",
                ", InitialSize_MB = (mf.size*8.)/1024. --initial file size\r\n",
                ", GrowthMb = (mf.growth*8.)/1024.\r\n",
                ", mf.is_percent_growth\r\n",
                ", MaxFileSizeMB = CASE WHEN mf.max_size > -1 THEN cast((mf.max_size*8.)/1024. as varchar(100)) ELSE 'Unlimited' END -- \"-1\" is unlimited\r\n",
                ", Recommendation = CASE WHEN d.size > mf.size THEN 'Increase TempDB Log file size to match or exceed current size.' + CHAR(10) ELSE '' END\r\n",
                ", mf.physical_name\r\n",
                ", volume_letter = UPPER(vs.volume_mount_point)\r\n",
                ", file_system_type\r\n",
                ", drive_size_GB = (CONVERT(decimal(19,2), vs.total_bytes/1024./1024./1024. ))\r\n",
                ", drive_free_space_GB = (CONVERT(decimal(19,2), vs.available_bytes/1024./1024./1024. ))\r\n",
                ", drive_pct_free = (CONVERT(DECIMAL(5,2), vs.available_bytes * 100.0 / vs.total_bytes))\r\n",
                "from sys.master_files mf\r\n",
                "inner join tempdb.sys.database_files d\r\n",
                "on mf.file_id = d.file_id\r\n",
                "and mf.database_id = db_id() \r\n",
                "cross apply sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs --only return volumes where there is database file (data or log)\r\n",
                "where d.type_desc = 'log'\r\n",
                "order by mf.file_id asc;\r\n",
                "\r\n",
                "SELECT servicename, status_desc, last_startup_time FROM sys.dm_server_services;\r\n",
                "GO\r\n",
                "--To resize tempdb: \r\n",
                "\r\n",
                "/*\r\n",
                "USE [master]\r\n",
                "GO\r\n",
                "ALTER DATABASE [tempdb]   MODIFY FILE ( NAME = N'<tempdfilename>'  , SIZE = <Desired File Size>);\r\n",
                "GO\r\n",
                "\r\n",
                "USE [tempdb]\r\n",
                "GO\r\n",
                "CHECKPOINT\r\n",
                "GO\r\n",
                "DBCC SHRINKFILE (N'<tempdfilename>' , 0, TRUNCATEONLY);\r\n",
                "GO\r\n",
                "*/\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "62fef62f-d679-4b2f-994c-d10d48d93707"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--Stats Out of Date whileloop.sq\r\n",
                "--This script only works in SQL2008R2SP2+ or SQL2012SP1+. \r\n",
                "\r\n",
                "--This generates an UPDATE STATISTICS script for all databases. Can be used in a maintenance plan (see bottom). Safe to execute. \r\n",
                "--This version does not work in Azure SQL DB. Instead, see toolbox\\stats out of date.sql.\r\n",
                "--Use toolbox\\stats out of date.sql to examine a particular database.\r\n",
                "\r\n",
                "--TODO BEFORE EXECUTING: comment out three lines below in <SQL2014 because incremental stastics not supported.\r\n",
                "PRINT('Stats out of Date Output Window')\r\n",
                "declare @tsql nvarchar(max) = \r\n",
                " N'use [?];\r\n",
                "    SELECT distinct\r\n",
                "--\t\t\ts.name AS SchemaName \r\n",
                "--          , o.name AS ObjectName \r\n",
                "--          , STA.name AS StatName \r\n",
                "--\t\t  , Object_Type = ISNULL(i.type_desc + '' Index'', ''Statistics Object'')\r\n",
                "--          , Stats_Last_Updated = ISNULL(sp.last_updated, o.create_date)\r\n",
                "--\t\t  , Rows_Changed = ISNULL(sp.modification_counter,0) --Rows Changed since last update\r\n",
                "\t\t\t--Below block only works in SQL 2014+, comment out this line in prior versions.\r\n",
                "\t\t\t   /*\r\n",
                "\t\t  , PartitionNumber = CASE WHEN MAX(p.partition_number) OVER (PARTITION by STA.name, i.name)  > 1 THEN p.partition_number ELSE null END\r\n",
                "\t\t  , STA.is_incremental --Only works in SQL 2014+, comment out this line in prior versions.\r\n",
                "\t\t  */\r\n",
                "--\t\t  , ''use [?]'' AS [?],\r\n",
                "\t\t   TSQL = CASE WHEN i.type_desc like ''%columnstore%'' THEN NULL ELSE\r\n",
                "\t\t    N''USE [?]; '' + \r\n",
                "\t\t\tN''UPDATE STATISTICS '' \r\n",
                "               + QUOTENAME(s.name) + N''.'' + QUOTENAME(o.name) + N'' '' \r\n",
                "               + QUOTENAME(STA.name) + N'' '' \r\n",
                "\t\t\t   + ''WITH RESAMPLE''\r\n",
                "\t\t\t   --Below block only works in SQL 2014+, comment out this line in prior versions.\r\n",
                "\t\t\t   /*\r\n",
                "\t\t\t   + CASE WHEN \r\n",
                "\t\t\t\t\t\tSTA.Is_Incremental = 1 and  --Only works in SQL 2014+, comment out this line in prior versions.\r\n",
                "\t\t\t\t\t\tMAX(p.partition_number) OVER (PARTITION by STA.name, i.name)  > 1 THEN '' ON PARTITIONS ('' + cast(p.partition_number as varchar(5)) + '') '' ELSE '''' END\r\n",
                "               */\r\n",
                "\t\t\tEND\r\n",
                "   FROM sys.objects  o   \r\n",
                "\t\t INNER JOIN sys.stats STA ON STA.object_id = o.object_id  \r\n",
                "\t\t\tCROSS APPLY sys.dm_db_stats_properties (STA.object_id, STA.stats_id) sp -- Only works in SQL2008R2SP2+ or SQL2012SP1+\r\n",
                "         INNER JOIN sys.schemas AS s \r\n",
                "             ON o.schema_id = s.schema_id \r\n",
                "\t\t LEFT OUTER JOIN sys.indexes as i\r\n",
                "\t\t\ton i.index_id = STA.stats_id\r\n",
                "\t\t\tand (i.type_desc not like ''%columnstore%'')\r\n",
                "\t\t\t--Below block only works in SQL 2014+, comment out this line in prior versions.\r\n",
                "\t\t\t /*\r\n",
                "\t     LEFT OUTER join sys.dm_db_partition_stats p \r\n",
                "\t\t\ton (\r\n",
                "\t\t\tSTA.Is_Incremental = 1 and  --Only works in SQL 2014+, comment out this line in prior versions. \r\n",
                "\t\t\tp.object_id = o.object_id  and \r\n",
                "\t\t\ti.index_id = p.index_id\r\n",
                "\t\t\t)\r\n",
                "\t\t\t*/\r\n",
                "         LEFT JOIN \r\n",
                "         (SELECT IUS.object_id \r\n",
                "                ,MIN(ISNULL(IUS.last_user_update, IUS.last_system_update)) AS LastUpdate \r\n",
                "          FROM sys.dm_db_index_usage_stats AS IUS \r\n",
                "          WHERE database_id = DB_ID() \r\n",
                "                AND NOT ISNULL(IUS.last_user_update, IUS.last_system_update) IS NULL \r\n",
                "          GROUP BY IUS.object_id \r\n",
                "         ) AS IUS \r\n",
                "             ON IUS.object_id = STA.object_id \r\n",
                "    WHERE o.type IN (''U'', ''V'')    -- only user tables and views \r\n",
                "          AND DATEDIFF(d, ISNULL(STATS_DATE(STA.object_id, STA.stats_id), N''1900-01-01'')  , IUS.LastUpdate) > 30 --indexes that haven''t been updated in the last month\r\n",
                "\t\t  AND sp.modification_counter > 10000\r\n",
                "    OPTION (MAXDOP 1);\r\n",
                "\tprint ''[?]'' '\r\n",
                "\r\n",
                "declare @dblist table (id int not null identity(1,1) primary key, dbname sysname not null)\r\n",
                "declare @tsqllist table (id int not null identity(1,1) primary key, tsqltext nvarchar(4000) not null) \r\n",
                "declare @x int = 1, @xmax int = null, @dbname sysname, @runtsql nvarchar(max) = null\r\n",
                "insert into @dblist \r\n",
                "select name from sys.databases\r\n",
                "where state_desc = 'online' and (database_id > 4 or name = 'msdb')\r\n",
                "select @xmax = max(id) from @dblist l\r\n",
                "\r\n",
                "while (@x <= @xmax)\r\n",
                "BEGIN\r\n",
                "\tselect @dbname = dbname from @dblist l where @x = id\r\n",
                "\r\n",
                "\tselect @runtsql = replace(@tsql, N'?', @dbname)\r\n",
                "\r\n",
                "\t--generates scripts, does not actually perform the UPDATE STATISTICS. See below.\r\n",
                "\t--Writes all the TSQL into a table variable which is displayed later\r\n",
                "\tinsert into @tsqllist (tsqltext) \r\n",
                "\texec sp_executesql @runtsql --safe, does not actually update stats\r\n",
                "\t\r\n",
                "\tset @x = @x + 1\r\n",
                "END\r\n",
                "\r\n",
                "--Shows all stats in all databases that need to be updated\r\n",
                "select * from @tsqllist\r\n",
                "\r\n",
                "--OPTIONALLY - execute all UPDATE Stats\r\n",
                "/*\r\n",
                "declare @s int = 1, @scount int = null\r\n",
                "select @scount = max(id), @runtsql = null from @tsqllist l\r\n",
                "while (@s <= @scount)\r\n",
                "BEGIN\r\n",
                "\t\r\n",
                "\t--actually executes the scripts.\r\n",
                "\tselect @runtsql = tsqltext from @tsqllist where id = @s\r\n",
                "\texec sp_executesql @runtsql\r\n",
                "\t\r\n",
                "\tset @s = @s + 1\r\n",
                "\t\r\n",
                "END\r\n",
                "*/\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "/*\r\n",
                "\r\n",
                "USE [WideWorldImporters]; UPDATE STATISTICS [Sales].[InvoiceLines] [_WA_Sys_0000000D_1E6F845E] WITH RESAMPLE\r\n",
                "USE [WideWorldImporters]; UPDATE STATISTICS [Sales].[Invoices] [_WA_Sys_0000000A_7849DB76] WITH RESAMPLE\r\n",
                "\r\n",
                "*/\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "b44ec2d8-2344-44de-b3da-9c7ba2605b5e"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--last known DBCC CHECKDB.sql\r\n",
                "PRINT('Last Known Good DBCC CheckDB')\r\n",
                "EXEC sp_MSforeachdb '\r\n",
                "--Table variable to capture the DBCC DBINFO output, look for the field we want in each database output\r\n",
                "DECLARE @DBCC_DBINFO TABLE (ParentObject VARCHAR(255) NOT NULL, [Object] VARCHAR(255)  NOT NULL, [Field] VARCHAR(255) NOT NULL \r\n",
                "INDEX idx_dbinfo_field CLUSTERED --just this line is SQL 2014+ only\r\n",
                ", [Value] VARCHAR(255));\r\n",
                "INSERT INTO @DBCC_DBINFO EXECUTE (\"DBCC DBINFO ([?]) WITH TABLERESULTS\");\r\n",
                "SELECT DISTINCT ''?'', [Value] FROM @DBCC_DBINFO WHERE Field = ''dbi_dbccLastKnownGood'';';\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "f6fc5aaa-e8c9-4def-9047-43e11e3a6bf2"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--Volume Stats.sql\r\n",
                "PRINT('Volume Stats Output Window')\r\n",
                "select \r\n",
                "  volume_letter = UPPER(vs.volume_mount_point)\r\n",
                ", volume_name = vs.logical_volume_name\r\n",
                ", file_system_type\r\n",
                ", drive_size_GB = MAX(CONVERT(decimal(19,2), vs.total_bytes/1024./1024./1024. ))\r\n",
                ", drive_free_space_GB = MAX(CONVERT(decimal(19,2), vs.available_bytes/1024./1024./1024. ))\r\n",
                ", drive_percent_free = MAX(CONVERT(decimal(5,2), vs.available_bytes * 100.0 / vs.total_bytes))\r\n",
                "FROM\r\n",
                "   sys.master_files AS f CROSS APPLY\r\n",
                "   sys.dm_os_volume_stats(f.database_id, f.file_id) vs --only return volumes where there is database file (data or log)\r\n",
                " GROUP BY vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name\r\n",
                " ORDER BY volume_letter "
            ],
            "metadata": {
                "azdata_cell_guid": "fe53a889-07dc-48af-942e-375af6aa3fd1"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Disaster Recovery\r\n",
                "1.\tReview current backup status, look for missing FULL or missing TLOG backups for DB’s not in SIMPLE.\r\n",
                "a.\tBackup history.sql\r\n",
                "2.\tRetention settings\r\n",
                "a.\tIf local backups are being taken, view the backup folder and verify that retention policy is deleting old backups.\r\n",
                "b.\tVerify that backup chains are intact - .bak’s should not be deleted before dependent .dif and .trn files.\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "a6843627-a4b3-47d4-8766-3a14fe853c5a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--Backup hisotry.sql\r\n",
                "--Looks for a complete backup history, displaying the latest backup of each type.\r\n",
                "\r\n",
                "--See also toolbox/multiserver backup history.sql for health checks.\r\n",
                "PRINT('Backup History Output Window')\r\n",
                "use master\r\n",
                "go\r\n",
                "--sql2012 and above\r\n",
                "select \r\n",
                "\tdatabase_Name\r\n",
                "\t, backuptype \r\n",
                "\t, d.recovery_model_desc\r\n",
                "\t, BackupDate = MAX(BackupDate)\r\n",
                "\t, d.state_desc\r\n",
                "\t, d.is_read_only\r\n",
                "\t, dm.Replica_Role\t\t--SQL 2012+\r\n",
                " from sys.databases d\r\n",
                " inner join \r\n",
                " (\r\n",
                "select distinct \r\n",
                "\tdatabase_name\r\n",
                "\t, database_id = db_id(database_name)\r\n",
                "\t, backuptype = case type\tWHEN 'D' then 'Database'\r\n",
                "\t\t\t\t\t\t\tWHEN 'I' then 'Differential database'\r\n",
                "\t\t\t\t\t\t\tWHEN 'L' then 'Transaction Log'\r\n",
                "\t\t\t\t\t\t\tWHEN 'F' then 'File or filegroup'\r\n",
                "\t\t\t\t\t\t\tWHEN 'G' then 'Differential file'\r\n",
                "\t\t\t\t\t\t\tWHEN 'P' then 'Partial'\r\n",
                "\t\t\t\t\t\t\tWHEN 'Q' then 'Differential partial' END\r\n",
                "\t, BackupDate\t=\tMAX(backup_finish_date)  \t\r\n",
                "\tfrom msdb.dbo.backupset bs\t\t\t\t\t\t\t\r\n",
                " group by Database_name, type\r\n",
                " UNION \r\n",
                " select distinct\r\n",
                "\tdb_name(d.database_id)\r\n",
                "\t, d.database_id\r\n",
                "\t, backuptype = 'Database'\r\n",
                "\t, null\r\n",
                "\tFROM master.sys.databases d\r\n",
                " UNION\r\n",
                " select distinct\r\n",
                "\tdb_name(d.database_id)\r\n",
                "\t, d.database_id\r\n",
                "\t, backuptype = 'Transaction Log'\r\n",
                "\t, null\r\n",
                "  FROM master.sys.databases d\r\n",
                "  where d.recovery_model_desc in ('FULL', 'BULK_LOGGED')\r\n",
                " ) a\r\n",
                " on d.database_id = a.database_id\r\n",
                " \r\n",
                " --SQL 2012+\r\n",
                " LEFT OUTER JOIN ( SELECT  database_id \r\n",
                "\t\t\t\t\t\t ,\tReplica_Role\t\t= CASE WHEN database_state_desc IS NOT NULL and last_received_time is null THEN 'PRIMARY '\r\n",
                "\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN database_state_desc IS NOT NULL and last_received_time is not null THEN 'SECONDARY' \r\n",
                "\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tELSE null END\r\n",
                "\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tfrom sys.dm_hadr_database_replica_states) dm\r\n",
                "\t\t\t\t\t\t on dm.database_id = a.database_id\r\n",
                "WHERE database_name not in ('model','tempdb')\r\n",
                "and not (backuptype = 'transaction log' and recovery_model_desc = 'SIMPLE')\r\n",
                "group by database_name, backuptype, d.recovery_model_desc, d.state_desc, d.is_read_only, dm.replica_role\r\n",
                "order by backuptype, recovery_model_desc, database_name asc\r\n",
                "go\r\n",
                "\r\n",
                " /*\r\n",
                "--for SQL 2000 and above\r\n",
                "select distinct \r\n",
                "\t  database_name\t= d.name \r\n",
                "\t, a.backuptype\t\r\n",
                "\t, RecoveryModel\t=\tdatabasepropertyex(d.name, 'Recovery')  \r\n",
                "\t, BackupDate\t=\tMax(a.backup_finish_date)  \r\n",
                "\tfrom master.dbo.sysdatabases d\r\n",
                "\tleft outer join \r\n",
                "\t(\t\tselect distinct \r\n",
                "\t\t\tdatabase_name\r\n",
                "\t\t\t, backuptype = case type\tWHEN 'D' then 'Database'\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN 'I' then 'Differential database backup'\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN 'L' then 'Transaction Log'\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN 'F' then 'File or filegroup'\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN 'G' then 'Differential file'\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN 'P' then 'Partial'\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN 'Q' then 'Differential partial' END\r\n",
                "\t\t\t, backup_finish_date\t=\tMAX(backup_finish_date)  \t\r\n",
                "\t\t\tfrom msdb.dbo.backupset bs\t\t\t\t\t\t\t\r\n",
                "\t\t group by Database_name, type\r\n",
                "\t\t UNION \r\n",
                "\t\t select distinct\r\n",
                "\t\t\t  d.name\r\n",
                "\t\t\t, backuptype = 'Database'\r\n",
                "\t\t\t, null\r\n",
                "\t\t\tFROM master.dbo.sysdatabases d\r\n",
                "\t\t UNION\r\n",
                "\t\t select distinct\r\n",
                "\t\t\t  d.name\r\n",
                "\t\t\t, backuptype = 'Transaction Log'\r\n",
                "\t\t\t, null\r\n",
                "\t\t  FROM master.dbo.sysdatabases d\r\n",
                "\t\t  where databasepropertyex(d.name, 'Recovery') in ('FULL', 'BULK_LOGGED')\r\n",
                "  ) a\r\n",
                "\ton d.name = a.database_name\r\n",
                " group by d.name , backuptype ,\tdatabasepropertyex(d.name, 'Recovery')\r\n",
                "order by backuptype, RecoveryModel, BackupDate asc\r\n",
                " */\r\n",
                " \r\n",
                "--granular backup history\r\n",
                "SELECT \r\n",
                "\t\tbs.database_name\r\n",
                "\t, backuptype = CASE \r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'D' and bs.is_copy_only = 0 then 'Full Database'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'D' and bs.is_copy_only = 1 then 'Full Copy-Only Database'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'I' then 'Differential database backup'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'L' then 'Transaction Log'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'F' then 'File or filegroup'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'G' then 'Differential file'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'P' then 'Partial'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'Q' then 'Differential partial' END + ' Backup'\r\n",
                "\t, bs.recovery_model\r\n",
                "\t, BackupStartDate = bs.Backup_Start_Date\r\n",
                "\t, BackupFinishDate = bs.Backup_Finish_Date\r\n",
                "\t, LatestBackupLocation = bf.physical_device_name\r\n",
                "\t, backup_size_mb\t\t\t=\tbs.backup_size / 1024./1024.\r\n",
                "\t, compressed_backup_size_mb =\tbs.compressed_backup_size /1024./1024.\r\n",
                "\t, database_backup_lsn -- For tlog and differential backups, this is the checkpoint_lsn of the FULL backup it is based on. \r\n",
                "\t, checkpoint_lsn\r\n",
                "\t, begins_log_chain\r\n",
                "\tFROM msdb.dbo.backupset bs\t\r\n",
                "\tLEFT OUTER JOIN msdb.dbo.[backupmediafamily] bf\r\n",
                "\ton bs.[media_set_id] = bf.[media_set_id]\r\n",
                "\t--where database_name = 'w'\r\n",
                "\tORDER BY  bs.database_name asc, bs.Backup_Start_Date desc;\r\n",
                " \r\n",
                "\r\n",
                " \r\n",
                " /*\r\n",
                "  --Latest Restore\r\n",
                " select d.name, Latest_Restore = max(restore_date)\r\n",
                "\tfrom sys.databases d\r\n",
                "\tLEFT OUTER JOIN msdb.dbo.restorehistory rh on d.name = rh.destination_database_name\r\n",
                "\tgroup by d.name\r\n",
                "\torder by Latest_Restore desc\r\n",
                "\r\n",
                "*/\r\n",
                "\r\n",
                "\r\n",
                "--Look for backups to NUL, a sign that someone doesn't know what they're doing to the tlog. (Probably VEEAM. Bad VEEAM.)\r\n",
                "--This is bad. Backup to NUL is just truncating the log without backing up the log, breaking the tlog chain. Any subsequent tlog backups are broken until a FULL backup restarts a valid chain.\r\n",
                "--Do not allow VEEAM or other VSS-based backup solutions to do backups to NUL. \r\n",
                "--In VEEAM, this is somewhere near the \"application aware backups\" or similar settings menu in various settings. Disable this. \r\n",
                "SELECT \r\n",
                "\t  bs.database_name\r\n",
                "\t, backuptype = CASE \r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'D' and bs.is_copy_only = 0 then 'Full Database'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'D' and bs.is_copy_only = 1 then 'Full Copy-Only Database'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'I' then 'Differential database backup'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'L' then 'Transaction Log'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'F' then 'File or filegroup'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'G' then 'Differential file'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'P' then 'Partial'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'Q' then 'Differential partial' END + ' Backup'\r\n",
                "\t, bs.recovery_model\r\n",
                "\t, BackupStartDate = bs.Backup_Start_Date\r\n",
                "\t, BackupFinishDate = bs.Backup_Finish_Date\r\n",
                "\t, LatestBackupLocation = bf.physical_device_name\r\n",
                "\t, backup_size_mb\t\t\t=\tbs.backup_size / 1024./1024.\r\n",
                "\t, compressed_backup_size_mb =\tbs.compressed_backup_size /1024./1024.\r\n",
                "\t, database_backup_lsn -- For tlog and differential backups, this is the checkpoint_lsn of the FULL backup it is based on. \r\n",
                "\t, checkpoint_lsn\r\n",
                "\t, begins_log_chain\r\n",
                "\tFROM msdb.dbo.backupset bs\t\r\n",
                "\tLEFT OUTER JOIN msdb.dbo.[backupmediafamily] bf\r\n",
                "\ton bs.[media_set_id] = bf.[media_set_id]\r\n",
                "\twhere bf.physical_device_name = 'NUL'\r\n",
                "\tORDER BY  bs.database_name asc, bs.Backup_Start_Date desc;\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "02c9df4c-c2ed-4037-8961-f94b3adec8e1"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Database Mail\r\n",
                "1.\tView Database Mail log for failures\r\n",
                "a.\tLook for unsent mails, unstarted broker, stopped process in Database Mail Diag.sql\r\n",
                "2.\tVerify functionality by sending a test email to managed.sql@sparkhound.com or appropriate distribution group. \r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "94112520-c1cd-46b3-a19d-1b3b62d10293"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--Database Mail Diag.sql\r\n",
                "PRINT('Databse Mail Diagnostic Output Window')\r\n",
                "SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ; -- should be 1\r\n",
                "EXECUTE msdb.dbo.sysmail_help_status_sp ; --should say STARTED\r\n",
                "--EXECUTE msdb.dbo.sysmail_start_sp --start the database mail queues;\r\n",
                "GO\r\n",
                "\r\n",
                "--Find recent unsent emails, hopefully there are none\r\n",
                "SELECT m.send_request_date, m.recipients, m.copy_recipients, m.blind_copy_recipients\r\n",
                ", m.[subject], sent_account = a.name, m.send_request_user, m.sent_status\r\n",
                ", Error_Description = l.description \r\n",
                "FROM msdb.dbo.sysmail_allitems m\r\n",
                "LEFT OUTER JOIN msdb.dbo.sysmail_account a\r\n",
                "\tON m.sent_account_id = a.account_id\r\n",
                "LEFT OUTER JOIN msdb.dbo.sysmail_event_log AS l  \r\n",
                "    ON m.mailitem_id = l.mailitem_id  \r\n",
                "WHERE\t1=1\r\n",
                "AND     m.send_request_date > dateadd(day, -45, sysdatetime()) -- Only show recent day(s)\r\n",
                "AND\t\tm.sent_status <> 'sent' -- Possible values are sent (successful), unsent (in process), retrying (failed but retrying), failed (no longer retrying)\r\n",
                "ORDER BY m.send_request_date DESC;\r\n",
                "GO\r\n",
                "\r\n",
                "--Send mail test\r\n",
                "--exec msdb.dbo.sp_send_dbmail @profile_name ='hotmail', @recipients ='williamdassaf@hotmail.com', @subject ='test', @body = 'test'\r\n",
                "\r\n",
                "--ALTER DATABASE msdb SET ENABLE_BROKER;"
            ],
            "metadata": {
                "azdata_cell_guid": "0511f504-16d3-4d41-92b6-600dcb9d75cf"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Security\r\n",
                "1.\tRun Public Permissions.sql to identify any SELECT and/or EXECUTE permissions granted to the public server role.\r\n",
                "a.\tDo not recommend granting ALTER, CONTROL, REFERENCES, TAKE OWNERSHIP, VIEW DEFINITION, to anyone who is not already an admin. We should be able to revoke this permission from public with no replacement unless the application is doing something with dynamically creating/altering objects. These permissions shouldn’t be needed by anyone not doing deployments of code changes and shouldn’t be necessary for day-to-day operation unless the application is doing something with dynamically creating/altering objects.\r\n",
                "b.\tNeed developer opinions on how DELETEs, INSERTs, UPDATEs are actually made. Then, grant these DELETE, INSERT or UPDATE permissions to something that is clearly not a read-only user, such as an application service user.\r\n",
                "c.\tIt is not uncommon to grant EXECUTE for a whole database to an application or app service user, but it should not be granted to a read-only user. Perhaps there is a subset of sprocs that are for reporting that a read-only user should have access to EXECUTE, would need developer insight on that.\r\n",
                "d.\tSELECT is the easiest topic. \r\n",
                "i.\tThere is a SELECT permission granted to public on the entire [dbo] schema. All the individual GRANT SELECT on dbo objects can be revoked and replaced by a single GRANT SELECT ON SCHEMA::[dbo] to one or more application service users including read-only users.\r\n",
                "ii.\tFor GRANT SELECT on objects on other non-dbo schemas, these could be used by read-write application service users and read-only service users. We should replace the public permission with GRANT SELECT on these objects to any user that could access these tables.\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "b94d9ee9-744c-42f9-b18e-4e6ef6cc6dce"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--Public Permissions.sql\r\n",
                "PRINT('Public Permissions Output Window')\r\n",
                "SELECT @@SERVERNAME\r\n",
                "\r\n",
                "\r\n",
                "--Server Level Security\r\n",
                "SELECT rm.state_desc, rm.permission_name, principal_name = QUOTENAME(u.name),  u.type_desc\r\n",
                ",  TSQL = rm.state_desc + N' ' + rm.permission_name + \r\n",
                "\tCASE WHEN e.name is not null THEN ' ON ENDPOINT::[' + e.name + '] ' ELSE '' END +\r\n",
                "\tN' TO ' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256)) + ';'\r\n",
                ",  TSQL = N'REVOKE ' + rm.permission_name +\r\n",
                "\tCASE WHEN e.name is not null THEN ' ON ENDPOINT::[' + e.name + '] ' ELSE '' END +\r\n",
                "\t N' TO ' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256)) + ';', *\r\n",
                "FROM sys.server_permissions rm\r\n",
                "inner join sys.server_principals u \r\n",
                "on rm.grantee_principal_id = u.principal_id\r\n",
                "left outer join sys.endpoints e\r\n",
                "on e.endpoint_id = major_id and class_desc = 'ENDPOINT'\r\n",
                "where u.name not like '##%' \r\n",
                "and u.name = 'public'\r\n",
                "order by rm.permission_name, u.name\r\n",
                "\r\n",
                "\r\n",
                "--Database role membership\r\n",
                "--Multi Database\r\n",
                "declare @TSQL nvarchar(4000) = 'use [?]; \r\n",
                "SELECT DB_NAME();\r\n",
                "SELECT DISTINCT\tQUOTENAME(r.name) as database_role_name, r.type_desc, QUOTENAME(d.name) as principal_name, d.type_desc\r\n",
                ",\tAdd_TSQL = ''EXEC sp_addrolemember @membername = N'''''' + d.name COLLATE DATABASE_DEFAULT + '''''', @rolename = N'''''' + r.name + ''''''''\r\n",
                ",\tDrop_TSQL = ''EXEC sp_droprolemember @membername = N'''''' + d.name COLLATE DATABASE_DEFAULT + '''''', @rolename = N'''''' + r.name + ''''''''\r\n",
                "FROM\tsys.database_role_members rm\r\n",
                "inner join sys.database_principals r on rm.role_principal_id = r.principal_id\r\n",
                "inner join sys.database_principals d on rm.member_principal_id = d.principal_id\r\n",
                "where d.name = ''public''\r\n",
                "';\r\n",
                "EXEC sp_MSforeachdb @TSQL\r\n",
                "GO\r\n",
                "\r\n",
                "--Multi-database database permissions\r\n",
                "--script is too long for sp_msforeachdb, had to roll our own.\r\n",
                "\r\n",
                "declare @TSQL varchar(8000) = null, @dbcount int = 0, @x int = 0, @dbname varchar(256) = null\r\n",
                "declare @dblist table (id int not null identity(1,1) primary key, dbname varchar(256)  not null )\r\n",
                "insert into @dblist (dbname)\r\n",
                "select name from sys.databases where name <> 'tempdb' and state_desc = 'ONLINE' \r\n",
                "order by database_id\r\n",
                "select @dbcount = count(dbname) from @dblist\r\n",
                "\r\n",
                "while (@x <= @dbcount)\r\n",
                "BEGIN\r\n",
                "\tselect @dbname = dbname from @dblist d where @x = d.id;\r\n",
                "\r\n",
                "\tselect @TSQL = \t'USE [' + @dbname  + '];\r\n",
                "\tSELECT DB_NAME();\r\n",
                "\tSELECT\tPermission_State_Desc\t=\tperm.state_desc\r\n",
                "\t\t,\tPermission_Name\t\t\t=\tperm.permission_name \r\n",
                "\t\t,\tPermission_Object_Name\t= ISNULL(QUOTENAME(s.name ) + ''.'','''') + QUOTENAME(obj.name COLLATE database_default) + CASE WHEN cl.name COLLATE database_default is null THEN '''' ELSE ''.'' + QUOTENAME(cl.name COLLATE database_default) END \t\t\t\r\n",
                "\t\t,\tObject_Type_Desc\t\t=\tobj.type_desc   \r\n",
                "\t\t,\tPrincipal_Name\t\t\t=\tQUOTENAME(u.name COLLATE database_default) \r\n",
                "\t\t,\tUser_Type\t\t\t\t=\tu.type_desc \r\n",
                "\t\t,\tCreate_TSQL = perm.state_desc + N'' '' + perm.permission_name \r\n",
                "\t\t\t+ case when obj.name COLLATE database_default is not null THEN + N'' ON '' + sc.class_desc + ''::'' + ISNULL(QUOTENAME(s.name COLLATE database_default) + ''.'','''') + QUOTENAME(obj.name COLLATE database_default) ELSE '''' END \r\n",
                "\t\t\t+ CASE WHEN cl.column_id IS NULL THEN '' '' ELSE ''('' + QUOTENAME(cl.name COLLATE database_default) + '')'' END \r\n",
                "\t\t\t+ N'' TO '' + QUOTENAME(u.name COLLATE database_default)\r\n",
                "\t\t,\tRevoke_TSQL = N''REVOKE '' + perm.permission_name \r\n",
                "\t\t\t+ case when obj.name COLLATE database_default is not null THEN + N'' ON '' + sc.class_desc + ''::'' + ISNULL(QUOTENAME(s.name COLLATE database_default) + ''.'','''') + QUOTENAME(obj.name COLLATE database_default) ELSE '''' END \r\n",
                "\t\t\t+ CASE WHEN cl.column_id IS NULL THEN '' '' ELSE ''('' + QUOTENAME(cl.name COLLATE database_default) + '')'' END \r\n",
                "\t\t\t+ N'' TO '' + QUOTENAME(u.name COLLATE database_default) \r\n",
                "\t\t\t, *\r\n",
                "\tFROM sys.database_permissions AS perm \r\n",
                "\tINNER JOIN sys.database_principals AS u\tON perm.grantee_principal_id = u.principal_id\r\n",
                "\tLEFT OUTER JOIN (--https://msdn.microsoft.com/en-us/library/ms188367.aspx\t\t\t\r\n",
                "\t\t\t\t\t\tselect name, object_id, schema_id, is_ms_shipped, class_desc=''OBJECT'', type_desc from sys.objects \r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, 0, null, null, ''DATABASE'', ''DATABASE''  from sys.databases \t\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect  name, schema_id, null, null, ''SCHEMA'', ''SCHEMA'' from sys.schemas\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, principal_id, null, null,  ''USER'', type_desc from sys.database_principals where type_desc in (''WINDOWS_USER'',''SQL_USER'',''ASYMMETRIC_KEY_MAPPED_USER'',''CERTIFICATE_MAPPED_USER'', ''WINDOWS_GROUP'',''EXTERNAL_GROUPS'')\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, principal_id, null, null,  ''USER'', type_desc from sys.database_principals where type_desc in (''WINDOWS_USER'',''SQL_USER'',''ASYMMETRIC_KEY_MAPPED_USER'',''CERTIFICATE_MAPPED_USER'', ''WINDOWS_GROUP'',''EXTERNAL_GROUPS'')\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, principal_id, null, null, ''APPLICATION ROLE'', type_desc from sys.database_principals where type_desc in (''APPLICATION_ROLE'')\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, principal_id, null, null, ''ROLE'', type_desc from sys.database_principals where type_desc in (''DATABASE_ROLE'')\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, assembly_id, null, null, ''ASSEMBLY'', ''ASSEMBLY'' from sys.assemblies \r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, user_type_id, null, null, ''TYPE'', ''USER TYPE'' from sys.types \r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, xml_collection_id, null, null, ''XML SCHEMA COLLECTION'', ''XML SCHEMA COLLECTION'' from sys.xml_schema_collections\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name COLLATE database_default, message_type_id, null, null, ''MESSAGE TYPE'', ''MESSAGE TYPE'' from sys.service_message_types\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name COLLATE database_default, service_contract_id, null, null, ''CONTRACT'', ''CONTRACT'' from sys.service_contracts\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name COLLATE database_default, service_id, null, null, ''SERVICE'', ''SERVICE'' from sys.services\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name COLLATE database_default, remote_service_binding_id, null, null, ''REMOTE SERVICE BINDING'', ''REMOTE SERVICE BINDING'' from sys.remote_service_bindings\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name COLLATE database_default, route_id, null, null, ''ROUTE'', ''ROUTE''  from sys.routes\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name COLLATE database_default, fulltext_catalog_id, null, null, ''FULLTEXT CATALOG'', ''FULLTEXT CATALOG''  from sys.fulltext_catalogs\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, symmetric_key_id, null, null, ''SYMMETRIC KEY'', ''SYMMETRIC KEY''  from sys.symmetric_keys\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, certificate_id, null, null, ''CERTIFICATE'', ''CERTIFICATE'' from sys.certificates\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, asymmetric_key_id, null, null, ''ASYMMETRIC KEY'', ''ASYMMETRIC KEY'' from sys.asymmetric_keys\r\n",
                "\t\t\t\t) obj\r\n",
                "\tON perm.major_id = obj.[object_id] \r\n",
                "\tINNER JOIN sys.securable_classes sc on sc.class = perm.class \r\n",
                "\tand sc.class_desc = obj.class_desc\r\n",
                "\tLEFT OUTER JOIN sys.schemas s ON s.schema_id = obj.schema_id\r\n",
                "\tLEFT OUTER JOIN sys.columns cl ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id\r\n",
                "\twhere 1=1\r\n",
                "\tand u.name = ''public''\r\n",
                "\tand perm.major_id > 0\r\n",
                "\t--Ignore internal principals\r\n",
                "\tand u.name COLLATE database_default not in (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'',''MS_DataCollectorInternalUser'',''PolicyAdministratorRole'',''ServerGroupReaderRole''\r\n",
                "\t,''ServerGroupAdministratorRole'',''TargetServersRole'',''SQLAgentUserRole'',''UtilityCMRReader'',''SQLAgentOperatorRole'',''dc_operator'',''dc_proxy'',''dc_admin'',''db_ssisadmin'',''db_ssisltduser'',''db_ssisoperator''\r\n",
                "\t,''UtilityIMRWriter'',''UtilityIMRReader'',''RSExecRole'',''DatabaseMailUserRole'')\r\n",
                "\t--Ignore ## principals\r\n",
                "\tand u.name COLLATE database_default not like ''##%##''\r\n",
                "\t--Ignore built-in svc accounts (not recommended anyway!)\r\n",
                "\tand u.name COLLATE database_default not like ''NT SERVICE%''\r\n",
                "\t--Ignore MS shipped internal objects \r\n",
                "\tand (obj.is_ms_shipped = 0 or obj.is_ms_shipped is null) \r\n",
                "\t--Ignore system sprocs (be aware of your naming conventions!)\r\n",
                "\t--and (obj.name not like ''sp_%'' or obj.name is null)\r\n",
                "\t--Ignore SSMS Diagramming Objects\r\n",
                "\tAND (ISNULL(QUOTENAME(s.name ) + ''.'','''') + QUOTENAME(obj.name COLLATE database_default) + CASE WHEN cl.name COLLATE database_default is null THEN '''' ELSE ''.'' + QUOTENAME(cl.name COLLATE database_default) END)\r\n",
                "\tNOT IN (''[dbo].[fn_diagramobjects]'',\r\n",
                "\t\t\t''[dbo].[sp_helpdiagrams]'',\r\n",
                "\t\t\t''[dbo].[sp_helpdiagramdefinition]'',\r\n",
                "\t\t\t''[dbo].[sp_creatediagram]'',\r\n",
                "\t\t\t''[dbo].[sp_renamediagram]'',\r\n",
                "\t\t\t''[dbo].[sp_alterdiagram]'',\r\n",
                "\t\t\t''[dbo].[sp_dropdiagram]'')\r\n",
                "\t--Ignore Database Tuning Advisor Objects\r\n",
                "\tAND (ISNULL(QUOTENAME(s.name ) + ''.'','''') + QUOTENAME(obj.name COLLATE database_default) + CASE WHEN cl.name COLLATE database_default is null THEN '''' ELSE ''.'' + QUOTENAME(cl.name COLLATE database_default) END)\r\n",
                "\tNOT IN (''[dbo].[dt_generateansiname]'',\r\n",
                "\t\t\t''[dbo].[dt_adduserobject]'',\r\n",
                "\t\t\t''[dbo].[dtproperties]'',\r\n",
                "\t\t\t''[dbo].[dt_setpropertybyid]'',\r\n",
                "\t\t\t''[dbo].[dt_getobjwithprop]'',\r\n",
                "\t\t\t''[dbo].[dt_getpropertiesbyid]'',\r\n",
                "\t\t\t''[dbo].[dt_setpropertybyid_u]'',\r\n",
                "\t\t\t''[dbo].[dt_getobjwithprop_u]'',\r\n",
                "\t\t\t''[dbo].[dt_getpropertiesbyid_u]'',\r\n",
                "\t\t\t''[dbo].[dt_dropuserobjectbyid]'',\r\n",
                "\t\t\t''[dbo].[dt_droppropertiesbyid]'',\r\n",
                "\t\t\t''[dbo].[dt_verstamp006]'',\r\n",
                "\t\t\t''[dbo].[dt_verstamp007]'',\r\n",
                "\t\t\t''[dbo].[dt_getpropertiesbyid_vcs]'',\r\n",
                "\t\t\t''[dbo].[dt_displayoaerror]'',\r\n",
                "\t\t\t''[dbo].[dt_adduserobject_vcs]'',\r\n",
                "\t\t\t''[dbo].[dt_addtosourcecontrol]'',\r\n",
                "\t\t\t''[dbo].[dt_checkinobject]'',\r\n",
                "\t\t\t''[dbo].[dt_checkoutobject]'',\r\n",
                "\t\t\t''[dbo].[dt_isundersourcecontrol]'',\r\n",
                "\t\t\t''[dbo].[dt_removefromsourcecontrol]'',\r\n",
                "\t\t\t''[dbo].[dt_validateloginparams]'',\r\n",
                "\t\t\t''[dbo].[dt_vcsenabled]'',\r\n",
                "\t\t\t''[dbo].[dt_whocheckedout]'',\r\n",
                "\t\t\t''[dbo].[dt_getpropertiesbyid_vcs_u]'',\r\n",
                "\t\t\t''[dbo].[dt_displayoaerror_u]'',\r\n",
                "\t\t\t''[dbo].[dt_addtosourcecontrol_u]'',\r\n",
                "\t\t\t''[dbo].[dt_checkinobject_u]'',\r\n",
                "\t\t\t''[dbo].[dt_checkoutobject_u]'',\r\n",
                "\t\t\t''[dbo].[dt_isundersourcecontrol_u]'',\r\n",
                "\t\t\t''[dbo].[dt_validateloginparams_u]'',\r\n",
                "\t\t\t''[dbo].[dt_whocheckedout_u]'')\r\n",
                "\torder by Object_Type_Desc, Principal_Name';\r\n",
                "\r\n",
                "\r\n",
                "\texec (@TSQL);\r\n",
                "\tselect @x = @x + 1;\r\n",
                "END\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "1a008d14-4303-4fcc-a620-9c0cf338f9ab"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "High Availability\r\n",
                "1.\tIf in a Failover Cluster or Availability Group, check the Failover Cluster Manager application in Windows. Report any recent cluster events.\r\n",
                "2.\tCluster Nodes must be identical SQL Server version.\r\n",
                "3.\tIf in an Availaiblity Group, use AG - Monitor.sql. Check the Availability Group node statuses and verify are all online and that all secondaries are SYNCHRONIZED (if synchronous) or SYNCHRONIZING (if asynchronous.) \r\n",
                "4.\tAfter re-creating an availability group, the owner will be the login that performed the setup. Microsoft recommends changing the availability group owner to sa. The Availability Group may fail if the owner sid is removed from SQL Server.\r\n",
                "a.\tAG – check owner.sql\r\n",
                "b.\tALTER AUTHORIZATION ON AVAILABILITY GROUP::AGname to [sa] ;\r\n",
                "5.\tTrend and Baseline for the following counters\r\n",
                "a.\tPerfmon: AG – Perfmon Counters.sql \r\n",
                "6.\tFor multisubnet AG’s, verify via nslookup that the Listener is broadcasting an IP in each subnet.\r\n",
                "a.\tExample: nslookup listenername\r\n",
                "b.\tYou should see two IP’s in two subnets returned.\r\n",
                "7.\tAll logins used to access SQL Server must be on all the replicas. \r\n",
                "8.\tCompare select * from sys.sql_logins on all AG replicas to confirm that login names and sid's match. The sid's will be the same on each replica, because they are replicated with each database. If they do not match, sql logins may not be mapped to database users on secondary replicas and connectivity will be denied upon failover. Would need to recreate the logins with the correct SID.\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "d8917f97-3eef-4551-9354-0335fb71c488"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--AG - Monitor.sql\r\n",
                "--On a secondary replica, this view returns a row for every secondary database on the server instance. \r\n",
                "--On the primary replica, this view returns a row for each primary database and an additional row for the corresponding secondary database.\r\n",
                "\r\n",
                "--Updated WDA 20180209\r\n",
                "\r\n",
                "\r\n",
                "--Monitor Availability Group performance\r\n",
                "--On a secondary replica, this view returns a row for every secondary database on the server instance. \r\n",
                "--On the primary replica, this view returns a row for each primary database and an additional row for the corresponding secondary database. Recommended.\r\n",
                "PRINT('AG - Monitor Output Window')\r\n",
                "IF NOT EXISTS (\r\n",
                "SELECT @@SERVERNAME\r\n",
                "   FROM sys.dm_hadr_availability_replica_states\r\n",
                "   WHERE is_local = 1\r\n",
                "   and role_desc = 'PRIMARY'\r\n",
                ")\r\n",
                "  SELECT 'Recommend: Run This Script on Primary Replica';\r\n",
                "\r\n",
                "DECLARE @BytesFlushed_Start_ms bigint, @BytesFlushed_Start bigint, @BytesFlushed_End_ms bigint, @BytesFlushed_End bigint\r\n",
                "\r\n",
                "DECLARE @TransactionDelay TABLE\r\n",
                "(\tDB sysname COLLATE SQL_Latin1_General_CP1_CI_AS not null\r\n",
                ",\tTransactionDelay_Start_ms decimal(19,2) null\r\n",
                ",\tTransactionDelay_end_ms decimal(19,2) null\r\n",
                ",\tTransactionDelay_Start decimal(19,2) null\r\n",
                ",\tTransactionDelay_end decimal(19,2) null\r\n",
                ",\tMirroredWriteTranspersec_Start_ms decimal(19,2) null\r\n",
                ",\tMirroredWriteTranspersec_end_ms decimal(19,2) null\r\n",
                ",\tMirroredWriteTranspersec_Start decimal(19,2) null\r\n",
                ",\tMirroredWriteTranspersec_end decimal(19,2) null\r\n",
                ",\tUNIQUE CLUSTERED (DB)\r\n",
                ")\r\n",
                "\r\n",
                "INSERT INTO @TransactionDelay (DB, TransactionDelay_Start_ms, TransactionDelay_Start)\r\n",
                "select DB = pc.instance_name\r\n",
                ",\tTransactionDelay_Start_ms = MAX(ms_ticks)\r\n",
                ",\tTransactionDelay_Start = MAX(convert(decimal(19,2), pc.cntr_value))\r\n",
                "from sys.dm_os_sys_info as si\r\n",
                "CROSS APPLY sys.dm_os_performance_counters as pc\r\n",
                " where object_name like '%database replica%'\r\n",
                " and counter_name = 'transaction delay' --cumulative transaction delay in ms\r\n",
                " group by pc.instance_name\r\n",
                "  \r\n",
                "UPDATE t \r\n",
                "SET MirroredWriteTranspersec_Start_ms = t2.MirroredWriteTranspersec_Start_ms\r\n",
                ",\tMirroredWriteTranspersec_Start = t2.MirroredWriteTranspersec_Start\r\n",
                "from @TransactionDelay t\r\n",
                "inner join \r\n",
                "(select DB = pc.instance_name\r\n",
                ",\tMirroredWriteTranspersec_Start_ms = MAX(ms_ticks)\r\n",
                ",\tMirroredWriteTranspersec_Start = MAX(convert(decimal(19,2), pc.cntr_value))\r\n",
                "from sys.dm_os_sys_info as si\r\n",
                "CROSS APPLY sys.dm_os_performance_counters as pc\r\n",
                " where object_name like '%database replica%'\r\n",
                " and counter_name = 'mirrored write transactions/sec' --actually a cumulative transactions count, not per sec\r\n",
                " group by pc.instance_name\r\n",
                " ) t2 on t.DB = t2.DB\r\n",
                "\r\n",
                "select @BytesFlushed_Start_ms = MAX(ms_ticks), @BytesFlushed_Start = MAX(cntr_value) --the availability database with the highest Tdata_loss becomes the limiting value for RPO compliance.\r\n",
                "from sys.dm_os_sys_info\r\n",
                "CROSS APPLY sys.dm_os_performance_counters where counter_name like 'Log Bytes Flushed/sec%'\r\n",
                "\r\n",
                "WAITFOR DELAY '00:00:05' --Adjust sample duration between measurements\r\n",
                "\r\n",
                "UPDATE t \r\n",
                "SET TransactionDelay_end_ms = t2.TransactionDelay_end_ms\r\n",
                ",\tTransactionDelay_end = t2.TransactionDelay_end\r\n",
                "from @TransactionDelay t\r\n",
                "inner join \r\n",
                "(select DB = pc.instance_name\r\n",
                ",\tTransactionDelay_end_ms = MAX(ms_ticks)\r\n",
                ",\tTransactionDelay_end = MAX(convert(decimal(19,2), pc.cntr_value))\r\n",
                "from sys.dm_os_sys_info as si\r\n",
                "CROSS APPLY sys.dm_os_performance_counters as pc\r\n",
                " where object_name like '%database replica%'\r\n",
                " and counter_name = 'transaction delay' --cumulative transaction delay in ms\r\n",
                " group by pc.instance_name\r\n",
                " ) t2 on t.DB = t2.DB\r\n",
                " \r\n",
                "UPDATE t \r\n",
                "SET MirroredWriteTranspersec_end_ms = t2.MirroredWriteTranspersec_end_ms\r\n",
                ",\tMirroredWriteTranspersec_end = t2.MirroredWriteTranspersec_end\r\n",
                "from @TransactionDelay t\r\n",
                "inner join \r\n",
                "(select DB = pc.instance_name\r\n",
                ",\tMirroredWriteTranspersec_end_ms = MAX(ms_ticks)\r\n",
                ",\tMirroredWriteTranspersec_end = MAX(convert(decimal(19,2), pc.cntr_value))\r\n",
                "from sys.dm_os_sys_info as si\r\n",
                "CROSS APPLY sys.dm_os_performance_counters as pc\r\n",
                " where object_name like '%database replica%'\r\n",
                " and counter_name = 'mirrored write transactions/sec'  --actually a cumulative transactions count, not per sec\r\n",
                " group by pc.instance_name\r\n",
                " ) t2 on t.DB = t2.DB\r\n",
                "\r\n",
                "select @BytesFlushed_End_ms =  MAX(ms_ticks), @BytesFlushed_End = MAX(cntr_value) --the availability database with the highest Tdata_loss becomes the limiting value for RPO compliance.\r\n",
                "from sys.dm_os_sys_info\r\n",
                "CROSS APPLY sys.dm_os_performance_counters where counter_name like 'Log Bytes Flushed/sec%'\r\n",
                "\r\n",
                "declare @LogBytesFlushed decimal(19,2) \r\n",
                "set @LogBytesFlushed = (@BytesFlushed_End - @BytesFlushed_Start) / NULLIF(@BytesFlushed_End_ms - @BytesFlushed_Start_ms,0)\r\n",
                "\r\n",
                "--select * from @TransactionDelay\r\n",
                "\r\n",
                "select \r\n",
                "\tAG\t\t\t\t\t= ag.name\r\n",
                ",\tInstance\t\t\t= ar.replica_server_name + ' ' + case when is_local = 1 then '(local)' else '' end\r\n",
                ",\tDB\t\t\t\t\t= db_name(dm.database_id)\t\r\n",
                ",\tReplica_Role\t\t= CASE WHEN last_received_time IS NULL THEN 'PRIMARY (Connections: '+ar.primary_role_allow_connections_desc+')' ELSE 'SECONDARY (Connections: '+ar.secondary_role_allow_connections_desc+')' END\r\n",
                ",\tLast_received_time\r\n",
                ",\tLast_commit_time\r\n",
                ",\tdm.synchronization_state_desc \r\n",
                ",\tdm.synchronization_health_desc\r\n",
                ",\tar.availability_mode_desc\r\n",
                ",\tar.failover_mode_desc\r\n",
                ",\tSuspended = case is_suspended when 1 then suspend_reason_desc else 'NO' end\r\n",
                ",\tRedo_queue_size_MB\t\t= convert(decimal(19,2),dm.redo_queue_size/1024.)--KB\r\n",
                ",\tRedo_rate_MB_per_s\t\t= convert(decimal(19,2),dm.redo_rate/1024.) --KB/s\r\n",
                ",\tRedo_Time_Left_s_RTO\t= convert(decimal(19,2),dm.redo_queue_size*1./NULLIF(dm.redo_rate*1.,0)) --only part of RTO. NULL value on secondary replica indicates no sampled activity.\r\n",
                ",\tLog_Send_Queue_Size_MB\t\t= convert(decimal(19,2),dm.log_send_queue_size/1024.) \r\n",
                ",\tLog_Send_Queue_Bytes_flushed_per_s = NULLIF(@LogBytesFlushed ,0)\r\n",
                ",\tLog_Send_Queue_Time_Left_s_RPO\t= convert(decimal(19,2),dm.log_send_queue_size*1./NULLIF(@LogBytesFlushed ,0)) --Rate. NULL value on secondary replica indicates no sampled activity.\r\n",
                ",\tSampled_Transactions_count\t\t= (td.MirroredWriteTranspersec_end - td.MirroredWriteTranspersec_start)  \r\n",
                ",\tSampled_Transaction_Delay_ms\t= (td.TransactionDelay_end - td.TransactionDelay_start)  \r\n",
                "--Transaction Delay numbers will be 0 if there is no synchronous replica for the DB\r\n",
                ",\tAvg_Sampled_Transaction_Delay_ms_per_s\t= convert(decimal(19,2), (td.TransactionDelay_end - td.TransactionDelay_Start) / ((td.TransactionDelay_end_ms - td.TransactionDelay_Start_ms)/1000.))\r\n",
                ",\tTransactions_per_s\t= convert(decimal(19,2), ((td.MirroredWriteTranspersec_end - td.MirroredWriteTranspersec_start) / ((td.MirroredWriteTranspersec_End_ms - td.MirroredWriteTranspersec_Start_ms)/1000.)))\r\n",
                ",\tdm.secondary_lag_seconds --sql 2016 and above only\r\n",
                ",\tar.backup_priority\r\n",
                ",\tar.modify_date\r\n",
                ",\tar.endpoint_url \r\n",
                ",\tar.read_only_routing_url\r\n",
                ",\tar.session_timeout\r\n",
                "from sys.dm_hadr_database_replica_states dm\r\n",
                "INNER JOIN sys.availability_replicas ar on dm.replica_id = ar.replica_id and dm.group_id = ar.group_id\r\n",
                "INNER JOIN sys.availability_groups ag on ag.group_id = dm.group_id\r\n",
                "LEFT OUTER JOIN @TransactionDelay td on td.DB = db_name(dm.database_id) --LEFT OUTER in case the sys.dm_os_performance_counters DMV is blank because of a rare issue with SQL startup. Still returns the rest of the data.\r\n",
                "ORDER BY\r\n",
                "\tAG\t\t\t\r\n",
                ",\tInstance\t\t\r\n",
                ",\tDB\t\t\t\r\n",
                ",\tReplica_Role\r\n",
                "\r\n",
                "--Current node only, should be run on primary\r\n",
                "select \r\n",
                "\twait_type\r\n",
                ",\twaiting_tasks_count\r\n",
                ",\twait_time_ms\r\n",
                ",\tper_wait_ms = convert(decimal(19,2), (convert(decimal(19,2), wait_time_ms)/ convert(decimal(19,2),waiting_tasks_count) ))\r\n",
                "from sys.dm_os_wait_stats where waiting_tasks_count >0 \r\n",
                "and wait_type like 'HADR_%_COMMIT'\r\n",
                "\r\n",
                "--SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'operations'\r\n",
                "\r\n",
                "--Check for suspect pages (hopefully 0 rows returned)\r\n",
                "--https://msdn.microsoft.com/en-us/library/ms191301.aspx\r\n",
                "SELECT * FROM msdb.dbo.suspect_pages\r\n",
                "   WHERE (event_type <= 3);\r\n",
                "\r\n",
                "--Check for autorepair events (hopefully 0 rows returned)\r\n",
                "--https://msdn.microsoft.com/en-us/library/bb677167.aspx\r\n",
                "select db = db_name(database_id)\r\n",
                ",\tfile_id\r\n",
                ",\tpage_id\r\n",
                ",\terror_type \r\n",
                ",\tpage_status\r\n",
                ",\tmodification_time\r\n",
                "from sys.dm_hadr_auto_page_repair order by modification_time desc\r\n",
                "\r\n",
                "--Replica status (one row per replica when run on the primary)\r\n",
                "select \r\n",
                "\tag.name,\r\n",
                "\trcs.replica_server_name,\r\n",
                "\trs.last_connect_error_number, rs.last_connect_error_description, rs.last_connect_error_timestamp,\r\n",
                "\trs.operational_state_desc,\r\n",
                "\trs.recovery_health_desc,\r\n",
                "\trs.connected_state_desc,\r\n",
                "\trs.role_desc,\r\n",
                "\trs.synchronization_health_desc\r\n",
                "from sys.dm_hadr_availability_replica_states rs --https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-availability-replica-states-transact-sql?view=sql-server-2017\r\n",
                "inner join sys.dm_hadr_availability_replica_cluster_states rcs\r\n",
                "on rs.replica_id = rcs.replica_id\r\n",
                "inner join sys.availability_groups ag \r\n",
                "on ag.group_id = rcs.group_id\r\n",
                "\r\n",
                "--https://msdn.microsoft.com/en-us/library/ff877972(v=sql.110).aspx\r\n",
                "--https://msdn.microsoft.com/en-us/library/dn135338(v=sql.110).aspx\r\n",
                "--https://blogs.msdn.microsoft.com/psssql/2013/09/23/interpreting-the-counter-values-from-sys-dm_os_performance_counters/\r\n",
                "--https://msdn.microsoft.com/en-us/library/ms175048.aspx\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "35fae00b-68b6-4081-9b2a-5f57a1989875"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--AG - Check Owner.sql\r\n",
                "--should not be owned by named account!\r\n",
                "--Run this on EACH replica SQL instance, it may be different.\r\n",
                "PRINT('AG- Check Owner Output Window')\r\n",
                "\r\n",
                "SELECT ar.replica_server_name\r\n",
                "\t,ag.name AS ag_name\r\n",
                "\t,ar.owner_sid\r\n",
                "\t,sp.name\r\n",
                "FROM sys.availability_replicas ar\r\n",
                "LEFT JOIN sys.server_principals sp\r\n",
                "\tON sp.sid = ar.owner_sid \r\n",
                "INNER JOIN sys.availability_groups ag\r\n",
                "\tON ag.group_id = ar.group_id\r\n",
                "WHERE ar.replica_server_name = SERVERPROPERTY('ServerName') ;\r\n",
                "\r\n",
                "/*\r\n",
                "\r\n",
                "ALTER AUTHORIZATION ON AVAILABILITY GROUP::[drAG1] to [sa] ;\r\n",
                "\r\n",
                "*/"
            ],
            "metadata": {
                "azdata_cell_guid": "1039223c-3caf-4d66-9d4b-ddb96cb72b3d"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--AG- Perform Counters.sql\r\n",
                "PRINT('AG - Performance Counters Output Window')\r\n",
                "declare @tempperfmon table (\r\n",
                "[object_name]\tnchar\t(256) null,\r\n",
                "counter_name\tnchar\t(256) null,\r\n",
                "instance_name\tnchar\t(256) null,\r\n",
                "cntr_value\t\tbigint\tnull\r\n",
                ", second_value bigint null\r\n",
                ")\r\n",
                "\r\n",
                "--For databases both the primary and secondary, with send/receive counters reflecting the local replica\r\n",
                "insert into @tempperfmon ([object_name],counter_name,instance_name,cntr_value)\r\n",
                "select [object_name],counter_name,instance_name,cntr_value\r\n",
                "from sys.dm_os_performance_counters\r\n",
                " where [object_name] like '%Availability Replica%' and instance_name <> '_Total' and\r\n",
                "\t(\tcounter_name = 'Bytes Received from Replica/sec' --From the availability replica. Pings and status updates will generate network traffic even on databases with no user updates.\r\n",
                "\tor\tcounter_name = 'Bytes Sent to Replica/sec' --Sent to the remote replica. On primary, sent to the secondary replica. On secondary, sent to the primary replica.\r\n",
                "\tor\tcounter_name = 'Bytes Sent to Transport/sec' --Sent over the network to the remote replica. On primary, sent to the secondary replica. On secondary, sent to the primary replica.\r\n",
                "\tor\tcounter_name = 'Flow Control Time (ms/sec)' --Time in milliseconds that log stream messages waited for send flow control, in the last second.   \r\n",
                "\tor\tcounter_name = 'Receives from Replica/Sec'\r\n",
                "\tor\tcounter_name = 'Sends to Replica/Sec'\r\n",
                "\r\n",
                "\t)\r\n",
                "\t\r\n",
                "insert into @tempperfmon ([object_name],counter_name,instance_name,cntr_value)\r\n",
                "--Only valid for databases in the secondary replica role\r\n",
                "select [object_name],counter_name,instance_name,cntr_value\r\n",
                "from sys.dm_os_performance_counters\r\n",
                " where [object_name] like '%database replica%' and instance_name <> '_Total' and\r\n",
                "\t(\tcounter_name = 'File Bytes Received/sec' --FILESTREAM data only\r\n",
                "\tor\tcounter_name = 'Log Bytes Received/sec' --Amount of log records received by the secondary replica for the database in the last second.'\r\n",
                "\tor\tcounter_name = 'Log remaining for undo' --The amount of log in kilobytes remaining to complete the undo phase.\r\n",
                "\t)\r\n",
                "\r\n",
                "WAITFOR DELAY '00:00:05';  --5s\r\n",
                "\r\n",
                "\r\n",
                "--For databases both the primary and secondary, with send/receive counters reflecting the local replica\r\n",
                "insert into @tempperfmon ([object_name],counter_name,instance_name,second_value)\r\n",
                "select [object_name],counter_name,instance_name,cntr_value\r\n",
                "from sys.dm_os_performance_counters\r\n",
                " where [object_name] like '%Availability Replica%' and instance_name <> '_Total' and\r\n",
                "\t(\tcounter_name = 'Bytes Received from Replica/sec' --From the availability replica. Pings and status updates will generate network traffic even on databases with no user updates.\r\n",
                "\tor\tcounter_name = 'Bytes Sent to Replica/sec' --Sent to the remote replica. On primary, sent to the secondary replica. On secondary, sent to the primary replica.\r\n",
                "\tor\tcounter_name = 'Bytes Sent to Transport/sec' --Sent over the network to the remote replica. On primary, sent to the secondary replica. On secondary, sent to the primary replica.\r\n",
                "\tor\tcounter_name = 'Flow Control Time (ms/sec)' --Time in milliseconds that log stream messages waited for send flow control, in the last second.   \r\n",
                "\tor\tcounter_name = 'Receives from Replica/Sec'\r\n",
                "\tor\tcounter_name = 'Sends to Replica/Sec'\r\n",
                "\r\n",
                "\t)\r\n",
                "\t\r\n",
                "insert into @tempperfmon ([object_name],counter_name,instance_name,second_value)\r\n",
                "--Only valid for databases in the secondary replica role\r\n",
                "select [object_name],counter_name,instance_name,cntr_value\r\n",
                "from sys.dm_os_performance_counters\r\n",
                " where [object_name] like '%database replica%' and instance_name <> '_Total' and\r\n",
                "\t(\tcounter_name = 'File Bytes Received/sec' --FILESTREAM data only\r\n",
                "\tor\tcounter_name = 'Log Bytes Received/sec' --Amount of log records received by the secondary replica for the database in the last second.'\r\n",
                "\tor\tcounter_name = 'Log remaining for undo' --The amount of log in kilobytes remaining to complete the undo phase.\r\n",
                "\t)\r\n",
                "\r\n",
                "select \r\n",
                "[object_name],counter_name,instance_name\r\n",
                ", Observation = (max(second_value) - max(cntr_value)) /5.\r\n",
                "from @tempperfmon\r\n",
                "group by [object_name],counter_name,instance_name\r\n",
                "\r\n",
                "/*\r\n",
                "--For databases both the primary and secondary, with send/receive counters reflecting the local replica\r\n",
                "select object_name,counter_name,instance_name,cntr_value\r\n",
                "from sys.dm_os_performance_counters\r\n",
                " where object_name like '%Availability Replica%' and instance_name <> '_Total' and\r\n",
                "\t(\tcounter_name = 'Bytes Received from Replica/sec' --From the availability replica. Pings and status updates will generate network traffic even on databases with no user updates.\r\n",
                "\tor\tcounter_name = 'Bytes Sent to Replica/sec' --Sent to the remote replica. On primary, sent to the secondary replica. On secondary, sent to the primary replica.\r\n",
                "\tor\tcounter_name = 'Bytes Sent to Transport/sec' --Sent over the network to the remote replica. On primary, sent to the secondary replica. On secondary, sent to the primary replica.\r\n",
                "\tor\tcounter_name = 'Flow Control Time (ms/sec)' --Time in milliseconds that log stream messages waited for send flow control, in the last second.   \r\n",
                "\tor\tcounter_name = 'Receives from Replica/Sec'\r\n",
                "\tor\tcounter_name = 'Sends to Replica/Sec'\r\n",
                "\r\n",
                "\t)\r\n",
                "\t\r\n",
                "\r\n",
                "--Only valid for databases in the secondary replica role\r\n",
                "select object_name,counter_name,instance_name,cntr_value\r\n",
                "from sys.dm_os_performance_counters\r\n",
                " where object_name like '%database replica%' and instance_name <> '_Total' and\r\n",
                "\t(\tcounter_name = 'File Bytes Received/sec' --FILESTREAM data only\r\n",
                "\tor\tcounter_name = 'Log Bytes Received/sec' --Amount of log records received by the secondary replica for the database in the last second.'\r\n",
                "\tor\tcounter_name = 'Log remaining for undo' --The amount of log in kilobytes remaining to complete the undo phase.\r\n",
                "\t)\r\n",
                "*/"
            ],
            "metadata": {
                "azdata_cell_guid": "1545ee10-3689-40d8-b714-b9106cedd817"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--AG Replica Login Compare\r\n",
                "PRINT('AG Replica Login Compare')\r\n",
                "select * from sys.sql_logins"
            ],
            "metadata": {
                "azdata_cell_guid": "8d898b51-8ae6-4410-a12f-d7cf00d7b1ca"
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}