SSHC - Database.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": [
                "## Databases ##\r\n",
                "1.\tReview Database Configurations: note new databases, record any changes to existing, etc.\r\n",
                "  * **Database Settings Info.sql**\r\n",
                "  **\tIncluded in this script is the sys.databases.log_reuse_wait value for each database.\r\n",
                "  * 1. If 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",
                "2.\tMake sure autogrowth settings are NOT set to 1 MB on any files using Autogrow Change All 1 MB Growth Files.sql\r\n",
                " * We recommend changing the autogrowth settings to a fixed rate between 128 MB and 512 MB depending on the size of the database.\r\n",
                "3.\tRecord any autogrowth events from the past month using Autogrowth Events.sql.\r\n",
                "4.\tUse Space in files.sql to report any files short on space that are in danger of an autogrowth event.\r\n",
                "  * \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",
                "  * Are any log files full? This would indicate FULL recovery + no TLOG backups.\r\n",
                "5.\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",
                "  * Sharepoint Databases.ps1\r\n",
                "  * https://technet.microsoft.com/en-us/library/cc678868(v=office.16).aspx\r\n",
                "6.\tVLF Counts\r\n",
                "  * Check 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",
                "7.\tOwnership\r\n",
                "  * Individual 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",
                "  ** Database Ownership.sql\r\n",
                "  ** Job Owners.sql\r\n",
                "8.\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",
                "  * Follow the recommendations in Tempdb Data Files.sql\r\n",
                "  *\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",
                "9.\tAge of Statistics –update indexes with outdated statistics (typically by business cycle =  monthly)\r\n",
                "  *\tStats Out of Date whileloop.sql to review all databases.\r\n",
                "10.\tIf we have replication, check the Replication Monitor in SSMS. Look for recent errors.\r\n",
                "  * Verify that “there are no replicated transactions available” for replication scenarios that are nightly snapshot only.\r\n",
                "  *\tVerify no error messages for other replication scenarios.\r\n",
                "11.\tFor SQL 2016+, turn on Query Store on any performance-sensitive database. Use default settings. Set to read/write.\r\n",
                "  *\tselect name, is_query_store_on from sys.databases \r\n",
                "12.\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": "6ebdf329-9912-4556-9125-f32ea95d5a7a"
            }
        },
        {
            "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": "647940f8-7b9d-494f-b04d-4da888dca0c3",
                "tags": [
                    "hide_input"
                ]
            },
            "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": "2bcd1cbf-acc3-4cae-964d-605b27baaa64",
                "tags": [
                    "hide_input"
                ]
            },
            "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",
                "*/"
            ],
            "metadata": {
                "azdata_cell_guid": "67472472-69c9-4ce8-af78-89a42038b90f",
                "tags": [
                    "hide_input"
                ]
            },
            "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": "2c3360c6-2995-4d9f-b5d4-b3e7c00450ec",
                "tags": [
                    "hide_input"
                ]
            },
            "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"
            ],
            "metadata": {
                "azdata_cell_guid": "fa6f6c7d-09d2-438b-8f00-6434eafe2346",
                "tags": [
                    "hide_input"
                ]
            },
            "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",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "402a8404-211e-4b1c-bf49-503240af2789",
                "tags": [
                    "hide_input"
                ]
            },
            "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": "9e98ef74-2843-4932-bd60-97e84f4f819e",
                "tags": [
                    "hide_input"
                ]
            },
            "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": "741c23c3-0bea-410c-92e6-16705f95224f",
                "tags": [
                    "hide_input"
                ]
            },
            "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",
                "*/"
            ],
            "metadata": {
                "azdata_cell_guid": "fb93d554-ac14-46b9-8cf0-35a6850cf449",
                "tags": [
                    "hide_input"
                ]
            },
            "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",
                "*/"
            ],
            "metadata": {
                "azdata_cell_guid": "55230b82-c5bb-4261-9195-c3911ef382a4",
                "tags": [
                    "hide_input"
                ]
            },
            "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'';';"
            ],
            "metadata": {
                "azdata_cell_guid": "b635c1fe-e6b2-483a-89f0-7b2019dfbba4",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}