Category Archives: dba

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;

How to identify the location of the SQL Server Error Log file

dba, sql, SQL Server, T-SQL, , , , , , , , , ,

So you want to find out where the SQL Server Error Log file is located for a specific SQL Server instance?

The Error Log is a great place to start tracking down reasons why SQL Server might be experiencing problems.

The queries below (first one works on SQL Server 2008, second one works on 2016) use the extended stored procedure XP_READERRORLOG, which is typically used to read error logs directly, to return the location of the SQL Server Error Log files. The returned value is then cleaned up so only the directory location is provided. You can then copy and paste the result into your file explorer. 

SQL Server 2008

USE Master;
GO

DECLARE @dir AS VARCHAR(MAX)

-- Command will create the temporary table in tempdb
CREATE TABLE [dbo].[#TmpErrorLog] (
	[LogDate] DATETIME NULL
	,[ProcessInfo] VARCHAR(20) NULL
	,[Text] VARCHAR(MAX) NULL
	);

-- Command will insert the errorlog data into temporary table
INSERT INTO #TmpErrorLog (
	[LogDate]
	,[ProcessInfo]
	,[Text]
	)
EXEC [master].[dbo].xp_readerrorlog 0
	,1
	,N'Logging SQL Server messages in file'
	,NULL
	,NULL
	,N'asc';

-- retrieves the data from temporary table and writes it to a variable to remove everything but directory location
SET @dir = (
		SELECT REPLACE(REPLACE(REPLACE([Text], 'Logging SQL Server messages in file ', ''), '''', ''), 'ERRORLOG', '')
		FROM #TmpErrorLog
		)
SET @dir = LEFT(@dir, LEN(@dir) - 1)

SELECT @dir AS ErrorFileLocation

DROP TABLE #TmpErrorLog

SQL Server 2016

USE Master;
GO

DECLARE @dir AS VARCHAR(MAX)

-- Command will create the temporary table in tempdb
CREATE TABLE [dbo].[#TmpErrorLog] (
	[LogDate] DATETIME NULL
	,[ProcessInfo] VARCHAR(20) NULL
	,[Text] VARCHAR(MAX) NULL
	);

-- Command will insert the errorlog data into temporary table
INSERT INTO #TmpErrorLog (
	[LogDate]
	,[ProcessInfo]
	,[Text]
	)
EXEC [master].[dbo].xp_readerrorlog 0
	,1
	,N'Logging SQL Server messages in file';


-- retrieves the data from temporary table and writes it to a variable to remove everything but directory location
SET @dir = (
		SELECT REPLACE(REPLACE(REPLACE([Text], 'Logging SQL Server messages in file ', ''), '''', ''), 'ERRORLOG', '')
		FROM #TmpErrorLog
		)
SET @dir = LEFT(@dir, LEN(@dir) - 1)

SELECT @dir AS ErrorFileLocation

DROP TABLE #TmpErrorLog

 

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

How to determine the index fragmentation level on your SQL Server instance

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

Below is a script that will run against every table in every database on your Sql Server instance and output, among other things, the average index fragmentation percentage.

It can be a bit heavy when running against large databases with thousands of tables so I would recommend running it during downtime or outside office hours.

This script returns fragmentation stats by utilizing a DMV.

When Nulls are presented as parameters this DMV works at a server level and uses preset defaults.

[sys].[dm_db_index_physical_stats](NULL, NULL, NULL, NULL, NULL)

However it also accepts the parameter options below to limited the results returned.

[sys].[dm_db_index_physical_stats](Database_Id, Object_Id, Index_Id, partition_number, MODE)

MODE OPTIONS:

Mode specifies the scan level that is used to obtain statistics. Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. The default (NULL) is LIMITED.

DEFAULT will have 12 columns full of data and then NULLS in the remainder.
SAMPLED will have 21 columns full of data.
LIMITED will have 12 columns of data and the NULLS in the remainder.
DETAILED will have 21 columns full of data.

Note: I’ve capture more fields in the temp table #Index than I use in the final select statement. This is because you might want to include additional index related fields i.e. check if the index is unique etc. If all you want is the index name in the final select statement you can tidy up the query as needed and exclude the unwanted additional fields.

SET NOCOUNT ON

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

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

DECLARE @Sql AS VARCHAR(MAX)
DECLARE @Database TABLE (
	DbId INT
	,DbName SYSNAME
	)
DECLARE @DbName AS SYSNAME
DECLARE @DbId AS INT

CREATE TABLE #Schema (
	DbId INT
	,DbName SYSNAME
	,Object_Id INT NULL
	,ObjectName SYSNAME NULL
	,TableFlag BIT
	,ViewFlag BIT
	,Schema_Id INT NULL
	,SchemaName SYSNAME NULL
	)

CREATE TABLE #Index (
	DbId INT
	,DbName SYSNAME
	,Object_Id INT NOT NULL
	,NAME SYSNAME NULL
	,index_id INT NOT NULL
	,type TINYINT NOT NULL
	,type_desc NVARCHAR(60) NULL
	,is_unique BIT NULL
	,data_space_id INT NULL
	,ignore_dup_key BIT NULL
	,is_primary_key BIT NULL
	,is_unique_constraint BIT NULL
	,fill_factor TINYINT NOT NULL
	,is_padded BIT NULL
	,is_disabled BIT NULL
	,is_hypothetical BIT NULL
	,allow_row_locks BIT NULL
	,allow_page_locks BIT NULL
	,has_filter BIT NULL
	,filter_definition NVARCHAR(max) NULL
	)

INSERT INTO @Database (
	DbId
	,DbName
	)
SELECT dbid
	,NAME
FROM master.dbo.sysdatabases
WHERE NAME <> 'tempdb'
ORDER BY NAME ASC

SET @DbId = 0

WHILE @DbId IS NOT NULL
BEGIN
	SET @DbId = (
			SELECT MIN(DbId)
			FROM @Database
			WHERE DbId > @DbId
			)
	SET @DbName = (
			SELECT DbName
			FROM @Database
			WHERE DbId = @DbId
			)
	SET @Sql = '
	INSERT INTO #Schema (
		DbId
		,DbName
		,Object_Id
		,ObjectName
		,TableFlag
		,ViewFlag
		,Schema_Id
		,SchemaName
		)
	SELECT ' + CONVERT(VARCHAR(128), @DbId) + ' AS DbId
		,' + '''' + @DbName + '''' + ' AS DbName
		,o.Object_Id
		,o.NAME AS ObjectName
		,CASE 
			WHEN o.type = ''IT''
				THEN 1
			WHEN o.type = ''S''
				THEN 1
			WHEN o.type = ''TT''
				THEN 1
			WHEN o.type = ''U''
				THEN 1
			ELSE NULL
			END AS TableFlag
		,CASE 
			WHEN o.type = ''V''
				THEN 1
			ELSE NULL
			END AS ViewFlag
		,s.Schema_Id
		,s.NAME AS SchemaName
	FROM ' + QUOTENAME(@DbName) + '.sys.objects AS o WITH (NOLOCK)
	INNER JOIN ' + QUOTENAME(@DbName) + '.sys.schemas AS s WITH (NOLOCK) ON o.schema_id = s.schema_id
	'

	EXEC (@Sql)

	SET @Sql = '
	INSERT INTO #Index (
	DbId
	,DbName
	,object_id
	,NAME
	,index_id
	,type
	,type_desc
	,is_unique
	,data_space_id
	,ignore_dup_key
	,is_primary_key
	,is_unique_constraint
	,fill_factor
	,is_padded
	,is_disabled
	,is_hypothetical
	,allow_row_locks
	,allow_page_locks
	,has_filter
	,filter_definition
	)
	SELECT ' + CONVERT(VARCHAR(128), @DbId) + ' AS DbId
		,' + '''' + @DbName + '''' + ' AS DbName
		,object_id
		,NAME
		,index_id
		,type
		,type_desc
		,is_unique
		,data_space_id
		,ignore_dup_key
		,is_primary_key
		,is_unique_constraint
		,fill_factor
		,is_padded
		,is_disabled
		,is_hypothetical
		,allow_row_locks
		,allow_page_locks
		,has_filter
		,filter_definition
	FROM ' + QUOTENAME(@DbName) + '.sys.indexes WITH (NOLOCK)
	'

	EXEC (@Sql)
END

SELECT ROW_NUMBER() OVER (
		ORDER BY s.DbName ASC
			,s.SchemaName ASC
			,s.ObjectName
		) AS Row
	,s.DbName
	,s.SchemaName AS SchemaName
	,s.Object_Id AS ObjectId
	,s.ObjectName
	,s.TableFlag
	,s.ViewFlag
	,i.Name AS IndexName
	,ddips.index_type_desc AS IndexType
	,ddips.page_count AS Pages
	,ddips.record_count AS Records
	,ddips.fragment_count AS Frags
	,ddips.avg_fragmentation_in_percent AS AvgFragPercent
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'SAMPLED') AS ddips
INNER JOIN #Schema AS s ON ddips.database_id = s.DbId
INNER JOIN #Index AS i ON s.DbId = i.DbId
	AND s.Object_Id = i.Object_Id
	AND ddips.index_id = i.index_id
	AND ddips.object_id = s.Object_Id
WHERE (
		s.TableFlag = 1
		OR s.ViewFlag = 1
		)
ORDER BY s.DbName ASC
	,s.SchemaName ASC
	,s.ObjectName

How to export a table as an XML file using a SQL script

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

Below is a script that will allow you, as the title suggests, to export a table as an XML file.

By use of the find and replace function in your preferred text editor, or by way of the text editor in SSMS, input your values for the required fields, i.e. replace TableName with the name of the table you will be working off.

SERVER NAME: ServerName

DATABASE NAME: DatabaseName

SCHEMA NAME: SchemaName

TABLE NAME: TableName

SAVE LOCATION ADDRESS: AddressName

FILE NAME: FileName (Excluding .xml extension)

Then run the script below and the table will be exported as an XML file to the location and file name of your choosing.

/*
FIND AND REPLACE:

SERVER NAME: ServerName
DATABASE NAME: DatabaseName 
SCHEMA NAME: SchemaName
TABLE NAME: TableName
SAVE LOCATION ADDRESS: AddressName
FILE NAME: FileName
 */
-- Run on ServerName
USE DatabaseName;
GO

DECLARE @saveLocation AS VARCHAR(510)
DECLARE @nameOfFile AS VARCHAR(255)
DECLARE @instanceName AS VARCHAR(255)
DECLARE @cmd NVARCHAR(1020)

SET @saveLocation = 'AddressName'
SET @nameOfFile = 'FileName'
SET @saveLocation = @saveLocation + '\' + @nameOfFile + '.xml'
SET @instanceName = (
		SELECT @@servername
		)

-- Please note that the fully qualified table name is needed
SELECT @cmd = '
    bcp "SELECT * FROM [DatabaseName].[SchemaName].[TableName] row for xml auto, root(''rows''), elements" ' + 'queryout "' + @saveLocation + '" -S ' + @instanceName + ' -T -w -r -t';

EXEC xp_cmdshell @cmd;
GO

 

This script is enabled by two utilities xp_cmdshell and BCP.

For an explanation of each please view the associated links.

How to enable and disable xp_cmdshell

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

xp_cmdshell is an extended stored procedure provided by Microsoft and stored in the master database. This procedure allows you to issue operating system commands directly to the Windows command shell via T-SQL code.

By default, the xp_cmdshell option is disabled on new installations. Along with other unwanted behavior malicious users can elevate their privileges with the use of xp_cmdshell. For this reason it is generally recommend to leave xp_cmdshell disabled. It can be enabled by using the Policy-Based Management or by running the sp_configure system stored procedure as shown in the following code example:

Use Master
GO

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

 

To disable xp_cmdshell use the following code example. Note: The following example also sets show advanced options server configuration option to 0. It is best practice to leave this option in the default state of 0 to stop the enabling of features.

Use Master
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO