Category Archives: Database Administrator

How to clear SQL Server backup and job history

Database Administrator, dba, How To, SQL Server, T-SQL, tsql, , , , , , ,

SQL Server makes a record of each backup and restore it performs. This information is recorded across the following tables:

msdb.dbo.backupfile
msdb.dbo.backupfilegroup
msdb.dbo.backupmediafamily
msdb.dbo.backupmediaset
msdb.dbo.backupset
msdb.dbo.restorefile
msdb.dbo.restorefilegroup
msdb.dbo.restorehistory

There is no cleanup process that automatically clears these records down so as you can imagine over time it could grow to be substantial.

Before you upgrade a SQL Server instance if any of these tables exceeds 10,000 rows this can cause an upgrade to hang so it is recommended the older records be deleted.

To reduce rows in all of these tables, you can run the sp_delete_backuphistory stored procedure. This procedure deletes the entries in all of the backup and restore history tables for backup sets older than a specified date. An example is shown below:

EXEC msdb..sp_delete_backuphistory ‘20161201 12:00:00.000’

Similarly to remove records from msdb.dbo.sysjobhistory you can run the following example as shown below:

EXEC msdb..sp_purge_jobhistory @oldest_date = ‘20161201 12:00:00.000’

It is recommended you set up a job to clear these tables however how often you clear them requires some thought. For instance you may want to keep, or archive, the job history for performance comparisons over time or as a record of job failures.

Another example is I use a stored procedure that automatically generates TSQL to perform restores. This stored procedure uses the records in the backup history tables to generate the TSQL. Therefore I need to be careful I do not delete records that are within our backup file retention time period otherwise I would not be able to generate a restore script for those backup files using the stored procedure.

You might be reliant on these tables as well in some way so keep this in mind.

How to set up SQL Server System Alerts

Database Administrator, dba, sql, SQL Server, T-SQL, , , , , , , ,

In this article I will be sharing the TSQL Script I use to set up SQL Server System Alerts. Prerequisites for this script to work are you have database mail set up and working and have defined an operator to use.

This script sets up alerts of severity 16 and up. These are errors you want to alert on because they either represent a resource issue, an integrity issue, or a hardware issue. Severity 15 and down tends to refer to the database and deals with issues that are tied to syntax errors, violations of foreign keys, etc. These issues do not typically require an alert and can be viewed in the error log.

Alert meaning:

016 Miscellaneous User Error
017 Insufficient Resources
018 Nonfatal Internal Error Detected
019 SQL Server Error in Resource
020 SQL Server Fatal Error in Current Process
021 SQL Server Fatal Error in Database (dbid) Process
022 SQL Server Fatal Error Table Integrity Suspect
023 SQL Server Fatal Error: Database Integrity Suspect
024 Hardware Error
025 (no description)
823 I/O Failure
824 I/O Failure
825 I/O Subsystem Failing (Not completely failed)

Just do a globale find and replace of PLACE_HOLDER with the operator you have defined for the alert and run the script.

USE msdb;
GO

/*
CHANGE OPERATOR NAME: PLACE_HOLDER
(TIP: LOOK AT OPERATORS IN SSMS FOR LIST OF AVAILABLE OPERATORS)
*/
/*
[1] DELETE ALERTS IF THEY ALREADY EXIST
*/
IF EXISTS (
		SELECT *
		FROM dbo.sysalerts
		WHERE NAME = 'Severity 016'
		)
BEGIN
	EXEC dbo.sp_delete_alert @name = N'Severity 016';
END
GO

IF EXISTS (
		SELECT *
		FROM dbo.sysalerts
		WHERE NAME = 'Severity 017'
		)
BEGIN
	EXEC dbo.sp_delete_alert @name = N'Severity 017';
END
GO

IF EXISTS (
		SELECT *
		FROM dbo.sysalerts
		WHERE NAME = 'Severity 018'
		)
BEGIN
	EXEC dbo.sp_delete_alert @name = N'Severity 018';
END
GO

IF EXISTS (
		SELECT *
		FROM dbo.sysalerts
		WHERE NAME = 'Severity 019'
		)
BEGIN
	EXEC dbo.sp_delete_alert @name = N'Severity 019';
END
GO

IF EXISTS (
		SELECT *
		FROM dbo.sysalerts
		WHERE NAME = 'Severity 020'
		)
BEGIN
	EXEC dbo.sp_delete_alert @name = N'Severity 020';
END
GO

IF EXISTS (
		SELECT *
		FROM dbo.sysalerts
		WHERE NAME = 'Severity 021'
		)
BEGIN
	EXEC dbo.sp_delete_alert @name = N'Severity 021';
END
GO

IF EXISTS (
		SELECT *
		FROM dbo.sysalerts
		WHERE NAME = 'Severity 022'
		)
BEGIN
	EXEC dbo.sp_delete_alert @name = N'Severity 022';
END
GO

IF EXISTS (
		SELECT *
		FROM dbo.sysalerts
		WHERE NAME = 'Severity 023'
		)
BEGIN
	EXEC dbo.sp_delete_alert @name = N'Severity 023';
END
GO

IF EXISTS (
		SELECT *
		FROM dbo.sysalerts
		WHERE NAME = 'Severity 024'
		)
BEGIN
	EXEC dbo.sp_delete_alert @name = N'Severity 024';
END
GO

IF EXISTS (
		SELECT *
		FROM dbo.sysalerts
		WHERE NAME = 'Severity 025'
		)
BEGIN
	EXEC dbo.sp_delete_alert @name = N'Severity 025';
END
GO

IF EXISTS (
		SELECT *
		FROM dbo.sysalerts
		WHERE NAME = 'Error Number 823'
		)
BEGIN
	EXEC dbo.sp_delete_alert @name = N'Error Number 823';
END
GO

IF EXISTS (
		SELECT *
		FROM dbo.sysalerts
		WHERE NAME = 'Error Number 824'
		)
BEGIN
	EXEC dbo.sp_delete_alert @name = N'Error Number 824';
END
GO

IF EXISTS (
		SELECT *
		FROM dbo.sysalerts
		WHERE NAME = 'Error Number 825'
		)
BEGIN
	EXEC dbo.sp_delete_alert @name = N'Error Number 825';
END
GO

/*
[2] CREATE ALERTS AND SET UP NOTIFICATIONS
*/
EXEC msdb.dbo.sp_add_alert @name = N'Severity 016'
	,@message_id = 0
	,@severity = 16
	,@enabled = 1
	,@delay_between_responses = 60
	,@include_event_description_in = 1
	,@job_id = N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 016'
	,@operator_name = N'PLACE_HOLDER'
	,@notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name = N'Severity 017'
	,@message_id = 0
	,@severity = 17
	,@enabled = 1
	,@delay_between_responses = 60
	,@include_event_description_in = 1
	,@job_id = N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 017'
	,@operator_name = N'PLACE_HOLDER'
	,@notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name = N'Severity 018'
	,@message_id = 0
	,@severity = 18
	,@enabled = 1
	,@delay_between_responses = 60
	,@include_event_description_in = 1
	,@job_id = N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 018'
	,@operator_name = N'PLACE_HOLDER'
	,@notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name = N'Severity 019'
	,@message_id = 0
	,@severity = 19
	,@enabled = 1
	,@delay_between_responses = 60
	,@include_event_description_in = 1
	,@job_id = N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 019'
	,@operator_name = N'PLACE_HOLDER'
	,@notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name = N'Severity 020'
	,@message_id = 0
	,@severity = 20
	,@enabled = 1
	,@delay_between_responses = 60
	,@include_event_description_in = 1
	,@job_id = N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 020'
	,@operator_name = N'PLACE_HOLDER'
	,@notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name = N'Severity 021'
	,@message_id = 0
	,@severity = 21
	,@enabled = 1
	,@delay_between_responses = 60
	,@include_event_description_in = 1
	,@job_id = N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 021'
	,@operator_name = N'PLACE_HOLDER'
	,@notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name = N'Severity 022'
	,@message_id = 0
	,@severity = 22
	,@enabled = 1
	,@delay_between_responses = 60
	,@include_event_description_in = 1
	,@job_id = N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 022'
	,@operator_name = N'PLACE_HOLDER'
	,@notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name = N'Severity 023'
	,@message_id = 0
	,@severity = 23
	,@enabled = 1
	,@delay_between_responses = 60
	,@include_event_description_in = 1
	,@job_id = N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 023'
	,@operator_name = N'PLACE_HOLDER'
	,@notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name = N'Severity 024'
	,@message_id = 0
	,@severity = 24
	,@enabled = 1
	,@delay_between_responses = 60
	,@include_event_description_in = 1
	,@job_id = N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 024'
	,@operator_name = N'PLACE_HOLDER'
	,@notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name = N'Severity 025'
	,@message_id = 0
	,@severity = 25
	,@enabled = 1
	,@delay_between_responses = 60
	,@include_event_description_in = 1
	,@job_id = N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 025'
	,@operator_name = N'PLACE_HOLDER'
	,@notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name = N'Error Number 823'
	,@message_id = 823
	,@severity = 0
	,@enabled = 1
	,@delay_between_responses = 60
	,@include_event_description_in = 1
	,@job_id = N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name = N'Error Number 823'
	,@operator_name = N'PLACE_HOLDER'
	,@notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name = N'Error Number 824'
	,@message_id = 824
	,@severity = 0
	,@enabled = 1
	,@delay_between_responses = 60
	,@include_event_description_in = 1
	,@job_id = N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name = N'Error Number 824'
	,@operator_name = N'PLACE_HOLDER'
	,@notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name = N'Error Number 825'
	,@message_id = 825
	,@severity = 0
	,@enabled = 1
	,@delay_between_responses = 60
	,@include_event_description_in = 1
	,@job_id = N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name = N'Error Number 825'
	,@operator_name = N'PLACE_HOLDER'
	,@notification_method = 7;
GO

How to size transaction log files appropriately and reduce the number of virtual log files

Data, Database Administrator, dba, SQL Server, T-SQL, , , , , , , , , , , , , ,

Sizing transaction log files and resolving the issue of having too many virtual log files may or may not be caused by the same problem but both have similar solutions revolving around the same steps. As such the script provide below can resolve both but the application of the script differs slightly.

Appropriately sizing the Transaction Log (TLog) File

Ideally, TLog files should be created at their desired size at installation rather than being allowed to grow slowly over time.

Typically though the DBA will not know how large or frequent the transactions will be prior to the database becoming active in the production environment.

Or the DBA might be new to the organisation and may not be able to tell if the TLog file size is appropriate for the typical transactions the database handles or is the result of some abnormally large transactions or failed TLog backups.

All this can of course be investigated somewhat but if the database transactions are in the kilobytes and transaction frequency is low then you could also take the pragmatic approach outlined below:

  1. Backup the log until it gets truncated
  2. Shrink the log as much as possible
  3. Allow the file to grow
  4. Record the maximum size of the log
  5. Backup the log until it gets truncated
  6. Shrink the log as much as possible
  7. Manually expand the log to the maximum size recorded in step 4

Carrying out the steps above will also resolve any issues with excessive Virtual Log Files. This is dealt with in more detail below.

The script provided at the end of this article can be utilized to achieve the steps above, see the instructions “How to use the script” below.

Before proceeding with this approach however ensure the following points have been considered as these will minimize the impact of growing the files on the databases:

  • That the autogrowth increment for the transaction log is suitably large, @DefaultFileGrowth is set at 128 Mb. This value or higher is recommended.
  • Appropriate restriction settings for growth, preferably unrestricted
  • Instant file initialization is on.

Review the following article to set up instant file initialization.

https://www.brentozar.com/blitz/instant-file-initialization/

You may already know that the TLog file has been appropriately sized but there are too many VLFs in which case the following section outlines how to resolve VLFs as an issue.

Dealing with Virtual Log Files (VLFs)

Each Transaction Log (TLog) file is divided logically into smaller segments called VLFs. VLFs are the unit of truncation for the TLog. When a VLF no longer contains log records for active transactions, it can be truncated and the space becomes available to log new transactions.

SQL Server should not have an excessive number of VLFs inside the TLog. Having a large number of small VLFs can cause the following problems:

  • Slow down the recovery process on startup or after restoring a backup
  • Slow down TLog backups
  • Affect insert/update/delete performance

What constitutes too many VLFs as a problem is subjective however Microsoft flags 1000+ VLFs as an issue.

The number of VLFs can grow based on the autogrowth settings for the TLog file and how often active transactions are written to disk. The problem of too many VLFs is often the result of accepting the default settings for initial log file size and autogrowth increment when the database is created.

To check how many VLFs a database has run the below command against the database:

DBCC LOGINFO;

Each row returned represents a VLF.

To resolving too many VLFs unfortunately requires some downtime. The fix is to shrink and regrow the log file. This is a blocking operation and I would suggest once complete you run a full backup on the database\s as the shrink will break the continuity of the Full and TLog backup schedule which will cause the TLog backups to fail.

Taking the following steps will reduce the number of VLFs in the TLog of a database:

  1. Record the maximum size of the log
  2. Backup the log until it gets truncated
  3. Shrink the log as much as possible
  4. Manually expand the log to the maximum size recorded in the first step

The script provided at the end of this article can be utilized to achieve the steps above, see the instructions “How to use the script” below.

How to use the script:

The script below will output the TSQL to run against all the databases in your instance. If you want to address every database on the instance run the entire script during scheduled downtime. To run against a specific database simply run the snippet that relates to that database.

The role of Backups

The TLog needs to be backed up and truncated prior to the running the shrink to get the file as small as possible.

Backup Option 1:

The script will backup the TLog\s if you provide a value for the @BackupLocation variable. This is defaulted to ‘C:\SQL_Server\Backup\’.

Backup Option 2: 

Alternatively you can set  @BackupLocation = NULL and run your own TLog backup maintenance plan or maintenance script prior to running the script.

Future Proofing by adding a margin for growth:

The script variable @Margin will allow you to set a percentage of how much larger you want the new TLog file size base on the current size. So for a database with a current TLog file size of 100 Mb, running the script with @Margin = 10 will output TSQL to change the TLog file size to 110 Mb. This has increased the TLog File size by 10%, providing 10% additional disk space before the TLog would need to grow again.

Appropriate TLog File Size Instructions:

Once you have backups arranged using one the options above set the variable @LogSizeMb = ‘128’. And run the script. This will start the TLog file size at 128 Mb.

Take the TSQL Generated and run the snippets needed for particular databases or run the entire script.

Let the file grow over a period that is representative of repeated database transcation activity.

When the TLog file is no longer growing you have found the natural size of the TLog for that database.

Run the script again this time setting @LogSizeMb = 0 and if appropriate provide a value for @Margin. The TSQL generated will then use the current TLog file size as is, plus the optional margin.

Run the snippets for the required database\s.

Dealing with Virtual Log Files (VLFs) Instructions:

Once you have backups arranged using one the options above, ensure that the variable @LogSizeMb is set to zero and if appropriate provide a value for @Margin. Then the script can be run to generate the TSQL needed to shrink and resize the TLog file size reducing the number of VLFs.

Run the snippets for the required database\s.

USE master;
GO

SET QUOTED_IDENTIFIER ON
GO

SET ARITHABORT ON
GO

DECLARE @DbName NVARCHAR(255)
	,@LogicalFileName NVARCHAR(255)
	,@DBRecoveryDesc VARCHAR(200)
	,@LogSizeMb INT
	,@DefaultFileGrowth VARCHAR(10)
	,@BackupLocation VARCHAR(255)
	,@GetDate VARCHAR(23)
	,@Margin AS DECIMAL(5, 2)

SELECT @Margin = 0
	,@LogSizeMb = 0
	,@BackupLocation = 'C:\SQL_Server\Backup\'
	,@DefaultFileGrowth = '128'
	,@GetDate = CONVERT(VARCHAR(23), GETDATE(), 126)
	,@GetDate = REPLACE(REPLACE(REPLACE(REPLACE(@GetDate, '-', ''), ':', ''), 'T', '_'), '.', '')

DECLARE DatabaseList CURSOR
FOR
SELECT d.NAME
	,d.recovery_model_desc AS DBRecoveryDesc
	,CASE 
		WHEN @LogSizeMb > 0
			THEN @LogSizeMb
		ELSE CEILING((mf.size * 8.0) / 1024) + (CEILING(((mf.size * 8.0) / 1024) * (@Margin / 100)))
		END AS LogSizeMb
	,@BackupLocation AS BackupLocation
	,@DefaultFileGrowth AS DefaultFileGrowth
FROM sys.databases AS d
LEFT JOIN sys.master_files AS mf ON d.database_Id = mf.database_Id
WHERE d.state_desc = 'ONLINE'
	AND d.is_read_only = 0
	AND d.database_id > 4
	AND type_desc = 'LOG'
ORDER BY NAME

OPEN DatabaseList

FETCH NEXT
FROM DatabaseList
INTO @DbName
	,@DBRecoveryDesc
	,@LogSizeMb
	,@BackupLocation
	,@DefaultFileGrowth

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @LogicalFileName = (
			SELECT TOP 1 NAME
			FROM sys.master_files AS mf
			WHERE DB_NAME(database_id) = @DbName
				AND type_desc = 'LOG'
			)

	IF @DBRecoveryDesc = 'Full'
		AND @BackupLocation IS NOT NULL
	BEGIN
		PRINT (
				'Use [' + @DbName + '] 
            GO  
			
			BACKUP LOG [' + @DbName + '] TO DISK=''' + @BackupLocation + @DbName + '_' + @GetDate + '_Log.trn' + ''';
			GO
			
            ALTER DATABASE [' + @DbName + '] SET RECOVERY SIMPLE WITH NO_WAIT
            GO   

            DBCC SHRINKFILE (''' + @LogicalFileName + ''',10)  
            GO  
            
            ALTER DATABASE [' + @DbName + '] MODIFY FILE (NAME=''' + @LogicalFileName + ''', SIZE=' + CONVERT(VARCHAR(10), @LogSizeMb) + 'MB, MAXSIZE=UNLIMITED, FILEGROWTH=' + @DefaultFileGrowth + 'MB);
            GO

            ALTER DATABASE [' + @DbName + '] SET RECOVERY FULL WITH NO_WAIT
            GO '
				)
		PRINT '----------------------------------------------------------- '
	END
	ELSE IF @DBRecoveryDesc = 'Full'
	BEGIN
		PRINT (
				'Use [' + @DbName + '] 
            GO  
					
            ALTER DATABASE [' + @DbName + '] SET RECOVERY SIMPLE WITH NO_WAIT
            GO   

            DBCC SHRINKFILE (''' + @LogicalFileName + ''',10)  
            GO  
            
            ALTER DATABASE [' + @DbName + '] MODIFY FILE (NAME=''' + @LogicalFileName + ''', SIZE=' + CONVERT(VARCHAR(10), @LogSizeMb) + 'MB, MAXSIZE=UNLIMITED, FILEGROWTH=' + @DefaultFileGrowth + 'MB);
            GO

            ALTER DATABASE [' + @DbName + '] SET RECOVERY FULL WITH NO_WAIT
            GO '
				)
		PRINT '----------------------------------------------------------- '
	END
	ELSE IF @DBRecoveryDesc = 'Simple'
	BEGIN
		PRINT (
				'Use [' + @DbName + ']  
            GO  

            DBCC SHRINKFILE (''' + @LogicalFileName + ''',10)    
            GO
            
            ALTER DATABASE [' + @DbName + '] MODIFY FILE (NAME=''' + @LogicalFileName + ''', SIZE=' + CONVERT(VARCHAR(10), @LogSizeMb) + 'MB, MAXSIZE=UNLIMITED, FILEGROWTH=' + @DefaultFileGrowth + 'MB);
            GO
            '
				)
		PRINT '----------------------------------------------------------- '
	END

	FETCH NEXT
	FROM DatabaseList
	INTO @DbName
		,@DBRecoveryDesc
		,@LogSizeMb
		,@BackupLocation
		,@DefaultFileGrowth
END

CLOSE DatabaseList

DEALLOCATE DatabaseList
Picture showing SQL Server Agent Properties Window

How to setup how much Agent Job History is retained

Database Administrator, dba, SQL Server, , , , , , , , , , , , , ,

I was asked to do a historic analysis of SQL Server jobs to determine their statistics, such as the number of runs, average run duration, successes/failures etc. As part of the daily checks I do on a server I’d only check the job runs for the past 24 hours. So I was surprised when I started the analysis and saw that averages for the same job across servers varied significantly.

When I looked at the msdb.dbo.sysjobhistory table of the server with the lowest averages I noted that there were only 999 rows and the history only went back a day or two.

So I had effectively no history on that server. When I looked at another the sysjobhistory table had almost 10 million entries, roughly 4 Gb of data! (The joys of taking over someone else’s environment)

This got me looking at the history settings for the SQL Agent. To open the SQL Server Agent Properties while in SQL Server Object Explorer right click on SQL Server Agent and then click on properties.

Picture showing SQL Server Agent Properties Window

For the first server the history settings were set to the default of 1000 lines for the log as a whole and 100 lines for the individual jobs. If you have a job that runs 4 steps that will use 5 lines, one line for the job and one line for each of the steps that are run. When I looked at the history settings on the second, server as you would expect, no settings had been applied.

Some sensible thresholds needed to be applied across the environment. I’m not going to dictate to you what your retention policy should be regarding job run history but I will say it can be as much as business decision as a server maintenance one so ask around if people need a record that something has run.

You may be in the position that you can be specific enough to enter row values but most organisations will have jobs that run weekly or monthly. In that case you may be better off using the “Older than” option and setting a retention period rather than using rows.

Always with logging, if you have the space, you’re better off having too much than having too little and missing data you need.  The size of each row in the sysjobhistory table is at most 4.5KB so keeping 10k rows is only around 45 Mb. If you don’t have 45 Mb to spare on your server then job history retention is the least of your worries. The worst thing that is likely to happen in storing more rows than you need is that it takes a bit longer to bring up the job history viewer. But here’s a link to some queries to help with that.

The moral of the story is just because it’s in the MSDB doesn’t mean Microsoft are taking care of it. Check your settings and make sure you’re retaining an appropriate amount of history now.

Icon of a folder or file with a clock on it

How to get the date of creation and size of current temporary objects from tempdb

Database Administrator, dba, SQL Server, T-SQL, Tableau, , , , , , , , , , ,

The query below will return the temporary objects that currently exist in the tempdb of an instance.

It’s a good query if you’re experiencing issues with the tempdb and want to see what objects are taking up space or if temp tables are surviving longer than you might expect them to.

Recently I found that because queries were running directly from Tableau and the connection was being held open temp tables were not being removed. All the temp tables existing at the same time was taking up several GBs of space needlessly.

-- Get current temporary objects date of creation and size 
SELECT DISTINCT obj.name AS ObjectName
	,obj.type_desc AS ObjectType
	,obj.object_id AS ObjectId
	,obj.principal_id AS PrincipalId
	,obj.schema_id AS SchemaId
	,obj.parent_object_id AS ParentId
	,stat.row_count AS RowCountStat
	,stat.used_page_count * 8 AS UsedSizeKB
	,stat.reserved_page_count * 8 AS RevervedSizeKB
	,obj.create_date AS CreatedDate
	,obj.modify_date AS ModifiedDate
FROM tempdb.sys.partitions AS part WITH (NOLOCK)
INNER JOIN tempdb.sys.dm_db_partition_stats AS stat WITH (NOLOCK) ON part.partition_id = stat.partition_id
	AND part.partition_number = stat.partition_number
INNER JOIN tempdb.sys.tables AS tbl WITH (NOLOCK) ON stat.object_id = tbl.object_id
LEFT JOIN tempdb.sys.objects AS obj WITH (NOLOCK) ON tbl.name = obj.name
ORDER BY CreatedDate ASC;

How to identify databases with Guest user enabled

Database Administrator, dba, sql, SQL Server, T-SQL, , , , , , , , , , , , ,

As best practice it is recommended to disable guest user in every user database, i.e. not master, msdb and tempdb, to improve the security of SQL Server. Guest user permits access to a database for any logins that are not mapped to a specific database user. The guest user cannot be dropped but it can be disabled by revoking the CONNECT permission.

Use the script below to identify which databases have guest user enabled. 

USE master;
GO

DECLARE @database_name SYSNAME
	,@sqlcmd NVARCHAR(4000)

DECLARE databases_cursor CURSOR
FOR
SELECT NAME
FROM sys.databases
WHERE STATE IN (0)
	AND database_id > 4
ORDER BY NAME

CREATE TABLE #guest_users_enabled (
	database_name SYSNAME
	,user_name SYSNAME
	,permission_name NVARCHAR(128)
	,state_desc NVARCHAR(6)
	)

OPEN databases_cursor;

FETCH NEXT
FROM databases_cursor
INTO @database_name;

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sqlcmd = N'use ' + @database_name + ';

        insert into #guest_users_enabled

        SELECT ''' + @database_name + ''' as database_name, name,

        permission_name, state_desc

        FROM sys.database_principals dpr

        INNER JOIN sys.database_permissions dpe

        ON dpr.principal_id = dpe.grantee_principal_id

        WHERE name = ''guest'' AND permission_name = ''CONNECT'''

	EXEC sp_executesql @sqlcmd

	FETCH NEXT
	FROM databases_cursor
	INTO @database_name;
END

SELECT database_name
	,user_name
	,permission_name
	,state_desc
FROM #guest_users_enabled
ORDER BY database_name ASC

DROP TABLE #guest_users_enabled

CLOSE databases_cursor;

DEALLOCATE databases_cursor;
GO
Use the script below, changing the placeholder database with the database identified in the script above, to revoke permission for the guest user to connect to that database.
USE [database name];

GO

REVOKE CONNECT FROM GUEST;

GO

How to resize TempDB in SQL Server

Database Administrator, dba, sql, SQL Server, T-SQL, , , , , , , , , ,

SQL Server Tempdb is used to store temporary objects. By default the initial size of the tempdb is too small for a production database engaged in any significant enterprise activities, therefore it should be sized accordingly on setup.

Failure to do so will have a negative performance impact when the database is first put into operation as it will need to grow to a more fitting size. Worse still, every time SQL Server is restarted the  tempdb will be recreated with its initial size. So SQL Server has to initiate autogrowth steps to grow the database file again and performance will be impacted negatively while it grows.

To compare the initial tempdb size to the current size run the script below.

USE master;
GO

SELECT mf.database_id
	,mf.NAME
	,mf.size * 8 / 1024 AS Initial_Size
	,df.size * 8 / 1024 AS Current_Size
FROM sys.master_files mf
/*adding info about current file size*/
INNER JOIN tempdb.sys.database_files df ON mf.NAME = df.NAME
/*filtering for tempdb only*/
WHERE mf.database_id = 2;
GO
You can set the tempdb initial size to the displayed current size if you think it will need to grow to this size again or take it as a simple guide and set the initial size less than its current size and allow it to grow as it needs.
To change the tempdb size run the script below replacing all the values in the placeholders with your specific values, use the example script further down as a guide. The files tempdev and templog are typically what the tempdb files are called in a default installation. (You can run the first script again to confirm success)

USE master;
GO

ALTER DATABASE TempDB MODIFY FILE (
	NAME = [logical file name of the tempdb data file]
	,SIZE = [value] MB
	);
GO


USE master;
GO

ALTER DATABASE TempDB MODIFY FILE (
	NAME = [tempdev]
	,SIZE = 4 MB
	);
GO


ALTER DATABASE TempDB MODIFY FILE (
	NAME = [templog]
	,SIZE = 3 MB
	);
GO

How to drop a user from a SQL Server database when you encounter the error message “The database principal owns a schema in the database, and cannot be dropped”

Database Administrator, dba, sql, SQL Server, T-SQL, , , , , , , , , , ,

principalowner

So if you have encountered the error above “The database principal owns a schema in the database, and cannot be dropped” you will not be able to drop the user until ownership of the effected schema has been transferred to another user/role. In order to drop the user, you have to find the schema that is assigned first. You can do this by running the script below replacing myUser with the user name in question. 

SELECT name 
FROM  sys.schemas 
WHERE principal_id = USER_ID(‘myUser’)
Then, use the schema found from the above query in place of the SchemaName below. This transfers ownership to dbo. You may need to alter authorization for multiple schema. Just run the statement for each returned schema replacing SchemaName. You can then drop your user.
ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo;
 
GO

DROP USER myUser;
An example of the HTML table sent as part of the email

How to send an email notification when disk space is low from SQL Server

Database Administrator, dba, Development, SQL Server, System, T-SQL, tsql, , , , , , , , , , , , , , , ,

Firstly let me acknowledge a few points worth nothing:

  • Disk space monitoring should be performed by enterprise quality network monitoring software. (And yes these are often very expensive)
  • The availability of adequate amounts of disk space is the responsibility of the people in charge of the IT infrastructure. (Not the DBAs)
  • The process doing the monitoring should not be running on the machine it is monitoring. (If the monitored machine experiences a problem the monitor may not be able to notify anyone)

With all that being said you might be in the situation where you still need to know when SQL Server is running out of local disk space and the solution below provides you with a free means to achieve this. This is a dynamic solution which will be able to determine the disk space available of all the local drives visible to SQL Server.

Prerequisites:

  • You will need to have an exchange server set up with SQL Server already capable of sending emails.
  • I have set up a database called Admin to hold the below tables and SP. It is good practice to store this sort of thing in a purposely created database instead of using something like Master. You can use another database if you would prefer just change the references to Admin in the first and second script with a find and replace.

Setup:

There are 4 components to this process.

Tables:

DiskSpaceAlertThreshold, this table holds a red and a yellow value. These values relate to the percentage space available on the local drives. A good rule of thumb would be to ensure there is over 20% of space available. So disk space availability for the yellow alert is set to 20% and the red alert is set to 10%. (Yes I’ve borrowed the alert names from Star Trek)

StaffEmailList, this table contains the email address of the staff you want to be notified when disk space is running low. For this example Clark Kent of The Daily Planet is very interested in SQL server disk space. You can of course add additional staff email addresses to the table. To exclude a staff member from receiving an email, after they have been added to the table, set the active flag to zero.

Store Procedure:

LowDiskSpaceAlert, this store procedure contains all the logic to determine what percentage of disk space is available. It also creates the HTML table used in the email to display the alert results and calls for the email to be sent.

N.B. You will need to change the profile named in the stored procedure to match an email account profile available within your SQL Server. Simply change the value of @profileName before executing the script.

SET @profileName = 'SQL Report'

An example of the HTML table sent as part of the email:

An example of the HTML table sent as part of the email

Job:

LowDiskSpaceAlert, this job simply runs the LowDiskSpaceAlert stored procedure every 10 minutes. You can change the schedule as you wish.

Create Tables and SP:

USE [Admin]
GO

/****** Object:  Table [dbo].[DiskSpaceAlertThreshold]    Script Date: 09/23/2016 18:46:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DiskSpaceAlertThreshold] (
	[DiskSpaceAlertThresholdId] [int] IDENTITY(1, 1) NOT NULL
	,[AlertClass] [varchar](25) NULL
	,[ThresholdValue] [int] NULL
	,PRIMARY KEY CLUSTERED ([DiskSpaceAlertThresholdId] ASC) WITH (
		PAD_INDEX = OFF
		,STATISTICS_NORECOMPUTE = OFF
		,IGNORE_DUP_KEY = OFF
		,ALLOW_ROW_LOCKS = ON
		,ALLOW_PAGE_LOCKS = ON
		) ON [PRIMARY]
	) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

SET IDENTITY_INSERT [dbo].[DiskSpaceAlertThreshold] ON

INSERT [dbo].[DiskSpaceAlertThreshold] (
	[DiskSpaceAlertThresholdId]
	,[AlertClass]
	,[ThresholdValue]
	)
VALUES (
	1
	,N'Red'
	,10
	)

INSERT [dbo].[DiskSpaceAlertThreshold] (
	[DiskSpaceAlertThresholdId]
	,[AlertClass]
	,[ThresholdValue]
	)
VALUES (
	2
	,N'Yellow'
	,20
	)

SET IDENTITY_INSERT [dbo].[DiskSpaceAlertThreshold] OFF
/****** Object:  Table [dbo].[StaffEmailList]    Script Date: 09/23/2016 18:46:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[StaffEmailList] (
	[StaffEmailListId] [int] IDENTITY(1, 1) NOT NULL
	,[FirstName] [varchar](255) NULL
	,[LastName] [varchar](255) NULL
	,[EmailAddress] [varchar](255) NULL
	,[Active] [bit] NULL
	,PRIMARY KEY CLUSTERED ([StaffEmailListId] ASC) WITH (
		PAD_INDEX = OFF
		,STATISTICS_NORECOMPUTE = OFF
		,IGNORE_DUP_KEY = OFF
		,ALLOW_ROW_LOCKS = ON
		,ALLOW_PAGE_LOCKS = ON
		) ON [PRIMARY]
	) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

SET IDENTITY_INSERT [dbo].[StaffEmailList] ON

INSERT [dbo].[StaffEmailList] (
	[StaffEmailListId]
	,[FirstName]
	,[LastName]
	,[EmailAddress]
	,[Active]
	)
VALUES (
	1
	,N'Clark'
	,N'Kent'
	,N'cKent@thedailyplanet.com'
	,1
	)

SET IDENTITY_INSERT [dbo].[StaffEmailList] OFF
/****** Object:  StoredProcedure [dbo].[LowDiskSpaceAlert]    Script Date: 09/23/2016 18:46:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[LowDiskSpaceAlert]
AS
SET NOCOUNT ON

IF OBJECT_ID('tempdb..#DiskSpaceStats') IS NOT NULL
	DROP TABLE #DiskSpaceStats

IF OBJECT_ID('tempdb..#DriveAlert') IS NOT NULL
	DROP TABLE #DriveAlert

DECLARE @profileName AS VARCHAR(128)
DECLARE @thresholdRed AS INT
DECLARE @thresholdYellow AS INT
DECLARE @drive AS VARCHAR(5)
DECLARE @sql AS VARCHAR(MAX)
DECLARE @i AS INT
DECLARE @j AS INT

/*Change the name for your SQL Server email profile*/
SET @profileName = 'SQL Report'

CREATE TABLE #DriveAlert (
	AlertEntry VARCHAR(25)
	,AlertValue VARCHAR(25)
	)

SET @thresholdRed = (
		SELECT ThresholdValue
		FROM [Admin].[dbo].[DiskSpaceAlertThreshold]
		WHERE AlertClass = 'Red'
		)
SET @thresholdYellow = (
		SELECT ThresholdValue
		FROM [Admin].[dbo].[DiskSpaceAlertThreshold]
		WHERE AlertClass = 'Yellow'
		)

SELECT ROW_NUMBER() OVER (
		ORDER BY AvailableDriveSpacePercentage ASC
		) AS RowNo
	,ObservationDT
	,Drive
	,AvailableDriveSpacePercentage
	,AvailableDriveSpaceGB
	,UsedDriveSpaceGB
	,TotalDriveSpaceGB
INTO #DiskSpaceStats
FROM (
	SELECT DISTINCT GETDATE() AS ObservationDT
		,dovs.volume_mount_point AS Drive
		,CAST(((dovs.available_bytes / 1073741824.0) / (dovs.total_bytes / 1073741824.0)) * 100 AS NUMERIC(8, 2)) AS AvailableDriveSpacePercentage
		,CAST((dovs.available_bytes / 1073741824.0) AS NUMERIC(8, 3)) AvailableDriveSpaceGB
		,CAST(((dovs.total_bytes - dovs.available_bytes) / 1073741824.0) AS NUMERIC(8, 3)) AS UsedDriveSpaceGB
		,CAST((dovs.total_bytes / 1073741824.0) AS NUMERIC(8, 3)) TotalDriveSpaceGB
	FROM sys.master_files AS mf
	CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) AS dovs
	) AS r

SET @i = 1
SET @j = (
		SELECT MAX(RowNo)
		FROM #DiskSpaceStats
		)

WHILE @i <= @j
BEGIN
	SET @drive = (
			SELECT Drive
			FROM #DiskSpaceStats
			WHERE RowNo = @i
			)
	SET @sql = 'DECLARE @alert AS VARCHAR(6)
IF ' + CONVERT(VARCHAR(3), @thresholdYellow) + ' > (
		SELECT AvailableDriveSpacePercentage
		FROM #DiskSpaceStats
		WHERE Drive = ''' + @drive + '''
		)
BEGIN
SET @alert = ''YELLOW''
END
IF ' + CONVERT(VARCHAR(3), @thresholdRed) + ' > (
		SELECT AvailableDriveSpacePercentage
		FROM #DiskSpaceStats
		WHERE Drive = ''' + @drive + '''
		)
BEGIN
SET @alert = ''RED''
END
IF @alert IS NOT NULL
BEGIN
	SELECT AlertEntry
	,AlertValue
	FROM (
	SELECT ''Alert Class: '' AS AlertEntry
	,@alert AS AlertValue 
	UNION ALL
	SELECT ''DateTime: '' AS AlertEntry
	,CONVERT(VARCHAR(30), ObservationDT)  AS AlertValue
	FROM #DiskSpaceStats
	WHERE Drive = ''' + @drive + '''
	UNION ALL
	SELECT ''Drive: '' AS AlertEntry
	,Drive AS AlertValue
	FROM #DiskSpaceStats
	WHERE Drive = ''' + @drive + 
		'''
	UNION ALL
	SELECT ''Percentage Available: '' AS AlertEntry
	,CONVERT(VARCHAR(10), AvailableDriveSpacePercentage) + ''%'' AS AlertValue
	FROM #DiskSpaceStats
	WHERE Drive = ''' + @drive + '''
	UNION ALL
	SELECT ''Available Space: '' AS AlertEntry
	,CONVERT(VARCHAR(10), AvailableDriveSpaceGB) + '' GB'' AS AlertValue
	FROM #DiskSpaceStats
	WHERE Drive = ''' + @drive + '''
	UNION ALL
	SELECT ''Used Space: '' AS AlertEntry
	,CONVERT(VARCHAR(10), UsedDriveSpaceGB) + '' GB'' AS AlertValue
	FROM #DiskSpaceStats
	WHERE Drive = ''' + @drive + '''
	UNION ALL
	SELECT ''Total Space: '' AS AlertEntry
	,CONVERT(VARCHAR(10), TotalDriveSpaceGB) + '' GB'' AS AlertValue
	FROM #DiskSpaceStats
	WHERE Drive = ''' + @drive + '''
	UNION ALL 
	SELECT '' '' AS AlertEntry
	,'' '' AS AlertValue
	) AS alert
END
'

	INSERT INTO #DriveAlert
	EXEC (@sql)

	SET @i = @i + 1
END

IF EXISTS (
		SELECT *
		FROM #DriveAlert
		)
BEGIN
	DECLARE @emailList AS VARCHAR(MAX)
	DECLARE @subjectMsg AS VARCHAR(255)
	DECLARE @tableHTML NVARCHAR(MAX)

	SET @emailList = (
			SELECT STUFF((
						SELECT '; ' + EmailAddress
						FROM [Admin].[dbo].[StaffEmailList]
						WHERE Active = 1
						FOR XML PATH('')
						), 1, 1, '') AS EmailAddress
			)
	SET @subjectMsg = (
			SELECT @@SERVERNAME
			) + ' Low Disk Space Alert'
	SET @tableHTML = N'<style>
	.tableFormat {
		width:80%;
		border:1px solid #C0C0C0;
		border-collapse:collapse;
		padding:5px;
	}
	.tableFormat th {
		border:1px solid #C0C0C0;
		padding:5px;
		background:#F0F0F0;
	}
	.tableFormat td {
		border:1px solid #C0C0C0;
		text-align:right;
		padding:5px;
	}
</style>' + N'<H1></H1>' + N'<table class="tableFormat" align="center">' + N'<tr><th>Description</th><th>Value</th></tr>' + CAST((
				SELECT td = AlertEntry
					,''
					,td = AlertValue
					,''
				FROM #DriveAlert
				FOR XML PATH('tr')
					,TYPE
				) AS NVARCHAR(MAX)) + N'</table>';

	DROP TABLE #DiskSpaceStats

	SET @tableHTML = REPLACE(@tableHTML, '<td> </td>', '<td bgcolor="#F0F0F0"> </td>')
	SET @tableHTML = REPLACE(@tableHTML, '<td>RED</td>', '<td bgcolor="red"><b>RED</b></td>')
	SET @tableHTML = REPLACE(@tableHTML, '<td>YELLOW</td>', '<td bgcolor="yellow"><b>YELLOW</b></td>')

	EXEC msdb.dbo.sp_send_dbmail @profile_name = @profileName
		,@recipients = @emailList
		,@body = @tableHTML
		,@subject = @subjectMsg
		,@importance = 'High'
		,@body_format = 'HTML';
END
GO
Create Job:
USE [msdb]
GO

/****** Object:  Job [LowDiskSpaceAlert]    Script Date: 09/23/2016 19:03:39 ******/
BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 09/23/2016 19:03:39 ******/
IF NOT EXISTS (
		SELECT NAME
		FROM msdb.dbo.syscategories
		WHERE NAME = N'[Uncategorized (Local)]'
			AND category_class = 1
		)
BEGIN
	EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB'
		,@type = N'LOCAL'
		,@name = N'[Uncategorized (Local)]'

	IF (
			@@ERROR <> 0
			OR @ReturnCode <> 0
			)
		GOTO QuitWithRollback
END

DECLARE @jobId BINARY (16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N'LowDiskSpaceAlert'
	,@enabled = 1
	,@notify_level_eventlog = 0
	,@notify_level_email = 0
	,@notify_level_netsend = 0
	,@notify_level_page = 0
	,@delete_level = 0
	,@description = N'No description available.'
	,@category_name = N'[Uncategorized (Local)]'
	,@owner_login_name = N'PL\admin.ph'
	,@job_id = @jobId OUTPUT

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

/****** Object:  Step [RunLowDiskSpaceAlertSP]    Script Date: 09/23/2016 19:03:40 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId
	,@step_name = N'RunLowDiskSpaceAlertSP'
	,@step_id = 1
	,@cmdexec_success_code = 0
	,@on_success_action = 1
	,@on_success_step_id = 0
	,@on_fail_action = 2
	,@on_fail_step_id = 0
	,@retry_attempts = 0
	,@retry_interval = 0
	,@os_run_priority = 0
	,@subsystem = N'TSQL'
	,@command = N'[dbo].[LowDiskSpaceAlert]'
	,@database_name = N'Admin'
	,@flags = 0

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId
	,@start_step_id = 1

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId
	,@name = N'LowDiskSpaceAlertSchedule'
	,@enabled = 1
	,@freq_type = 4
	,@freq_interval = 1
	,@freq_subday_type = 4
	,@freq_subday_interval = 10
	,@freq_relative_interval = 0
	,@freq_recurrence_factor = 0
	,@active_start_date = 20160824
	,@active_end_date = 99991231
	,@active_start_time = 0
	,@active_end_time = 235959
	,@schedule_uid = N'a8831f35-e7a6-4880-bc2d-4d48aff82ff6'

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId
	,@server_name = N'(local)'

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0)
	ROLLBACK TRANSACTION

EndSave:
GO

How to set SQL Server file autogrowth settings to recommended sizes across all databases on an instance

Database Administrator, dba, IT, sql, SQL Server, System, T-SQL, tsql, , , , , , , , , , , , , ,

As part of a Microsoft RAP analysis you might encounter some of the following:

  • Databases identified with auto-growth set to percentage growth
  • Databases identified with auto-growth set to 1MB growth
  • Database files have been identified that have the next Auto Growth increment of 1GB or above
  • Databases have been identified with one or more Transaction Log files where expected next Auto Growth increment is 1GB or above

Brent Ozar suggests that the data file and log file autogrowth setting should be 256MB and 128MB respectively, and he makes a good argument here.

Broadly I agree with his position but I also acknowledge, as he does, that every database is different and has its own considerations. (Keep that in mind while reviewing the following script)

I’ve created the script below, which programmatically creates the SQL commands, to apply these recommended values to the autogrowth settings of each database on an instance.

The script gets every database name, and associated data file and log file names and using dynamic SQL writes a script to update autogrowth settings for each database.

You can then adjust the setting values manually for each database before executing.

Or if you’re really brave/crazy execute the dynamic script automatically by changing PRINT @sql to EXEC(@sql).

USE Master;
GO

SET NOCOUNT ON

DECLARE @database AS NVARCHAR(256)
	,@dataFileName AS NVARCHAR(256)
	,@logFileName AS NVARCHAR(256)
	,@sql AS VARCHAR(MAX)
	,@loop AS INT
	,@end AS INT

IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL
	DROP TABLE #DatabaseList

CREATE TABLE #DatabaseList (
	[#DatabaseListId] INT IDENTITY(1, 1) PRIMARY KEY
	,[DatabaseName] [sysname] NOT NULL
	,[DataFileName] [sysname] NOT NULL
	,[LogFileName] [sysname] NOT NULL
	)

INSERT INTO #DatabaseList
SELECT d.DatabaseName
	,d.FileName AS DataFileName
	,l.FileName AS LogFileName
FROM (
	SELECT d.NAME AS DatabaseName
		,mf.NAME AS FileName
	FROM sys.master_files mf(NOLOCK)
	JOIN sys.databases d(NOLOCK) ON mf.database_id = d.database_id
	WHERE type_desc = 'ROWS'
	) AS d
LEFT JOIN (
	SELECT d.NAME AS DatabaseName
		,mf.NAME AS FileName
	FROM sys.master_files mf(NOLOCK)
	JOIN sys.databases d(NOLOCK) ON mf.database_id = d.database_id
	WHERE mf.type_desc = 'LOG'
	) AS l ON l.DatabaseName = d.DatabaseName

SET @end = (
		SELECT MAX(#DatabaseListId)
		FROM #DatabaseList
		)
SET @loop = 1

WHILE @loop <= @end
BEGIN
	SELECT @database = DatabaseName
		,@dataFileName = DataFileName
		,@logFileName = LogFileName
	FROM #DatabaseList
	WHERE #DatabaseListId = @loop

	--PRINT @database
	SET @sql = 'USE [' + @database + '];
		GO
		ALTER DATABASE [' + @database + '] MODIFY FILE (NAME=''' + @dataFileName + ''',MAXSIZE = UNLIMITED, FILEGROWTH = 256MB);
		ALTER DATABASE [' + @database + '] MODIFY FILE (NAME=''' + @logFileName + ''',MAXSIZE = UNLIMITED, FILEGROWTH = 128MB);
		GO'

	PRINT @sql

	SET @loop = @loop + 1
END
		/*
Change PRINT @sql to:
EXEC (@sql)
To Update the file sizes automatically
*/

 

As part of this process I’d also run the script below before and after you’ve made the changes to have a record for change management purposes.

SELECT GETDATE() AS DateOfObservation
	,d.NAME AS database_name
	,mf.NAME AS file_name
	,mf.type_desc AS file_type
	,mf.growth AS current_percent_growth
FROM sys.master_files mf(NOLOCK)
JOIN sys.databases d(NOLOCK) ON mf.database_id = d.database_id