Transact SQL – Drop Temp Table if it it exists

Background

Reviewing some Transact SQL Code and saw a code block that works well in exception handling, but can have a bit of side effect in Transact SQL.

Code

Original Code

SQL


BEGIN TRY

    DROP TABLE #pollingData;
     
END TRY
BEGIN CATCH
END CATCH;

 

Explanation

When the temp table does not exist, an error is raised.

Because the drop table is enclosed in a try/catch block the error is gracefully handled by the system.

Noise

But, yet there is a bit of silent noises.

SQL Server Profiler

Image

Tabulated
  1. Event
    • Exception
      • Error :- 3701
      • Severity :- 11
      • State :- 5
    • User Error Message
      • Error :- 3701
      • Severity :- 11
      • State :- 5

Trace Events

Image

Explanation
  1. Events
    • objectName = error_reported
      • eventData
      • error Number :- 3701
      • severity :- 11
      • message :- Cannot drop the table ‘#pollingData’, because it does not exist or you do not have permission.
      • sqlStatement :- empty

 

Revised Code

Check If Table Exists, before attempt to drop

SQL

BEGIN TRY

	if object_id('tempdb..#pollingData') is not null
	begin
		DROP TABLE #pollingData;
    end 

END TRY

BEGIN CATCH

END CATCH;

Drop Table, If Exists

In MS SQL Server v2016 and later versions, we can use the new “drop object if exists” conditional statement…

SQL


drop table if exists #pollingData;

 

Other Errors

There are other errors that can be avoided with dropping an object only upon validation that it exists.

SET XACT_ABORT ON;

If you use the set xact_abort on directive, your code will abort upon running into the error mentioned above.

Error Message

Msg 3930, Level 16, State 1 ..
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

 

SQL Server – Database Compatibility Level

Background

Upgrading quite a bit of SQL Server Instances.

One of the areas to keep an eye on is the compatibility level of individual databases within each Instance.

Code

Read Compatibility Level

Credit

Crediting Nick Kavadias ( Link ) for his response on Stack Overflow:

How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
Link

SQL



/*
    How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
    https://stackoverflow.com/questions/1501596/how-to-check-sql-server-database-compatibility-after-sp-dbcmptlevel-is-deprecate

*/
select 

              [database] 
                = tblSD.[name]

            , [compatibilityLevel]
                = tblSD.[compatibility_level]

            , [version] = 
                    CASE [compatibility_level]
                        WHEN 65  THEN 'SQL Server 6.5'
                        WHEN 70  THEN 'SQL Server 7.0'
                        WHEN 80  THEN 'SQL Server 2000'
                        WHEN 90  THEN 'SQL Server 2005'
                        WHEN 100 THEN 'SQL Server 2008/R2'
                        WHEN 110 THEN 'SQL Server 2012'
                        WHEN 120 THEN 'SQL Server 2014'
                        WHEN 130 THEN 'SQL Server 2016'
                        WHEN 140 THEN 'SQL Server 2017'
                    END

from sys.databases tblSD

order by 
        tblSD.[name]

Set Compatibility Level

Legacy

sp_dbcmptlevel

Syntax


exec master..sp_dbcmptlevel 
         @dbname = @name 
       , @new_cmptlevel = @version 

Sample


exec master..sp_dbcmptlevel 
         @dbname = 'hrdb' 
       , @new_cmptlevel = 130

Modern

Alter Database / Set Compatibility Level

Syntax


alter database [db-name]
	set COMPATIBILITY_LEVEL = [compatibilityLevel]

Sample


alter database [hrdb]
	set COMPATIBILITY_LEVEL = 130

References

  1. Alter Database
    • ALTER DATABASE (Transact-SQL) Compatibility Level
      • Docs / SQL / T-SQL / Statements
        Link
    • sp_dbcmptlevel (Transact-SQL)
      • Docs / SQL / Relational databases / System stored procedures
        Link

SQL Server – Database Permissions – List

Background

There are different levels of permissions in SQL Server.

There are concentric permissions.

Inclusive are Instance, database, schema, and object.

And, others that are more dependant on the object type.

Database Permissions

Code


SELECT
            [database]
              = db_name()

         ,  [username] 
              = tblGranteeDP.[name]

         ,  [login]     
             = suser_sname(tblGranteeDP.[sid])

         ,  [permissionClass]
             = tblDP.class_desc

         ,  [permissionName] 
            = tblDP.[permission_name]

         ,  [permissionState] 
            = tblDP.state_desc

         ,  [principalType] 
            = tblGranteeDP.[type_desc]

FROM sys.database_permissions AS tblDP

INNER JOIN sys.database_principals AS tblGrantorDP   
    ON tblGrantorDP.principal_id = tblDP.grantor_principal_id

INNER JOIN sys.database_principals AS tblGranteeDP
    ON tblGranteeDP.principal_id = tblDP.grantee_principal_id

LEFT OUTER JOIN sys.server_principals AS tblGranteeSP
    ON tblGranteeDP.principal_id = tblGranteeSP.principal_id

/*
    Identifies class on which permission exists.
*/
where (tblDP.class = 0)

/*
    Exclude those with mere CONNECT permission
*/
and  tblDP.permission_name not in 
        (   
            'CONNECT'
        )

order by
      db_name() asc
    , tblGranteeDP.[name] asc
    , tblDP.[permission_name] asc

Output

SQL Server – Security – Privileging & Tracking the Guest Account

Background

Each month we get databases from our Vendors.  And, we have to review data in those tables.

There are a couple of pathways to quickly granting accessing to our users.

In this post, we will grant access to the guest user.

 

SQL

Grant Access



grant connect to guest;

Review Access

Review Permissions grant/denied to Guest on Contextual Database

Code


declare @principal sysname

set @principal = 'guest'

; with cteObject
(
      objectID
    , [object]
    , schemaID
    , [schema]
)
as
(
    select
              tblSO.object_id
            , tblSO.[name]
            , tblSO.schema_id
            , tblSS.[name]

    from   sys.objects tblSO

    inner join sys.schemas tblSS

            on tblSO.[schema_id] = tblSS.[schema_id]
)
select 

          tblSP.[name]
        
        --, tblSDP.*

        , tblSDP.[state_desc]

        , tblSDP.[permission_name]

        , [objectClass]
            = tblSDP.class_desc

        , [object]
            = coalesce
                (
                     quoteName
                        (
                            cteO.[schema]
                        )
                    + '.'
                    + quoteName
                        (
                            cteO.[object]
                        )

                    , null
                )

            , [grantee]
                = SUSER_NAME(tblSDP.grantor_principal_id)

from   sys.database_principals tblSP

inner join sys.database_permissions tblSDP

    on tblSP.principal_id = tblSDP.[grantee_principal_id]

left outer join cteObject cteO

    on tblSDP.major_id = cteO.objectID

where tblSP.[name] = @principal

order by

    tblSP.[name] asc


Review Access for contextual database

Code

use master
go

set nocount on
go

set XACT_ABORT on
go

declare @tblResult table
(
      [database]    sysname
    , [principal]   sysname
    , [access]      sysname
    , [grantee]     sysname null
)

declare @command nvarchar(4000)

set @command =
                'if databasepropertyex(''?'', ''Collation'') is not null '
                + ' begin '
                + ' use [?]; '
                + ' select [database] = db_name(), tblSP.name,  tblSDP.[state_desc], SUSER_NAME(tblSDP.grantor_principal_id) '
                + ' from sys.database_principals tblSP '
                + ' inner join sys.database_permissions tblSDP '
                + ' on tblSP.principal_id = tblSDP.grantee_principal_id '
                + ' where tblSP.[name] = ''guest'' '
                + ' and tblSDP.[permission_name] = ''CONNECT'' '
                + ' and tblSDP.[state_desc]  = ''GRANT'' '
                + ' end '

insert @tblResult
(
      [database]    
    , [principal]   
    , [access]      
    , [grantee] 
)
exec master.dbo.[sp_MSforeachdb]
        @command1 = @command

select *

from   @tblResult tblR

order by 
    tblR.[database]

SQL Server – Review Database Restore Timeline

Background

Reviewing Database Restore timeline and wasted to share what we have thus far.

 

Metadata

SQL

/*

    a) restorehistory (Transact-SQL)
    https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/restorehistory-transact-sql

*/
; with cteRestoreHistory
(
      [restoreHistoryID]
    , [databaseName]
    , [restorer]
    , [restoreDate]
    , [restoreStopAt]
    , [restoreType]
    , [backupSetID] 
    , [sequenceNbr]
)
as
(

    select 
              [restoreHistoryID]
                = tblRH.[restore_history_id]

            , [databaseName]
                = tblRH.[destination_database_name]

            , [restorer]
                = tblRH.[user_name]

            , [restoreDate]
                = tblRH.[restore_date]

            , [restoreStopAt]
                = tblRH.[stop_at]

            , [restoreType]
                = tblRH.restore_type

            , [backupSetID] 
                = tblRH.[backup_set_id] 

            , [sequenceNbr]
                = DENSE_RANK() 
                    OVER 
                    (

                        PARTITION BY 
                            tblRH.[destination_database_name]

                        ORDER BY 
                            tblRH.[restore_date] DESC
                    )

    from   msdb.[dbo].[restorehistory] tblRH


)

, cteRestoreType
(
      [restoreType]
    , [restoreTypeLiteral]
)
as
(
    select 
              'D'
            , 'Database'
    union
    select 
              'F'
            , 'File'
    union
    select 
              'G'
            , 'FileGroup'
    union
    select 
              'L'
            , 'Log'

)
select 
          [database] 
            = tblSD.[name]

        , [dbid]
             = tblSD.[database_id]

        , [restoreInitatedBy]
             = tblRH.[restorer]

        , [restoreDate]

        , [restoreType]
             = tblRH.[restoreType]

        , [restoreTypeDescription]
            = cteRT.restoreTypeLiteral

        , [backupMachineName]
            = tblBS.[machine_name]

        , [backupInitiatedBy]
            = tblBS.[user_name]

        , [databaseVersion]
            = tblBS.database_version

        , [backupFile]
            = tblBMF.physical_device_name

        , [originalBackupStartDate]
            = tblBS.backup_start_date

        , [originalBackupCompletionDate]
            = tblBS.backup_finish_date

        , [originalBackupDuration (in minutes)]
            = datediff
                (   
                      minute
                    , tblBS.backup_start_date
                    , tblBS.backup_finish_date
                )

from   sys.databases tblSD

inner join cteRestoreHistory tblRH

        on tblSD.[name] = tblRH.[databaseName]

inner join msdb..backupset tblBS

    ON tblRH.[backupSetID] = [tblBS].[backup_set_id]

INNER JOIN msdb..backupmediafamily tblBMF
    ON [tblBS].[media_set_id] = [tblBMF].[media_set_id] 

left outer join cteRestoreType cteRT
    on tblRH.[restoreType] = cteRT.restoreType


where tblRH.sequenceNbr = 1

order by

          tblSD.[name]
        , tblRH.[restoreDate] desc


Output

Sample

SqlServer – Tempdb – Error – “Insufficient space in tempdb to hold row versions”

Background

Here reviewing failed jobs and tried to re-run one of them, but still No Go!

TroubleShooting

Error

Image

Textual

Insufficient space in tempdb to hold row versions.
Need to shrink the version store to free up some space in tempdb.
Transaction (id=17270854 xsn=2470235 spid=79 elapsed_time=3101) has been marked as victim and it will be rolled back if it accesses the version store.
If the problem persists, the likely cause is improperly sized tempdb or long running transactions.
Please refer to BOL on how to configure tempdb for versioning.

 

Query

Top Version Store Occupants

Let us see what Objects are in the Version Store.

Code



set nocount off;
go

declare @tblHOBT TABLE
(
	  [id]			int not null identity(1,1)
	, [dbid]		int not null
	, [database]	sysname not null
	, [schema]		sysname not null
	, [object]		sysname not null
	, [objectType] sysname not null
	, [index]		sysname null
	, [hobtID]		sysname not null

)

insert into @tblHOBT
(
	  [dbid]
	, [database]
	, [schema]
	, [object]
	, [objectType]
	, [index]
	, [hobtID]
)
EXECUTE master.sys.sp_MSforeachdb 
	'
		if databasepropertyex(''?'', ''Collation'') is not null
		begin

			USE [?]; 
			select distinct
				      db_id()
					, ''?''
					, tblSS.[name]
					, tblSO.[name]
					, tblSO.[type_desc]
					, tblSI.[name]
					, tblSP.[hobt_id]

			from   sys.objects tblSO

			inner join sys.schemas tblSS

				on tblSO.schema_id = tblSS.schema_id

			inner join sys.indexes tblSI

				on tblSO.object_id = tblSI.object_id

			inner join sys.partitions tblSP

				on  tblSI.object_id = tblSP.object_id
				and tblSI.index_id = tblSP.index_id

			-- On User Table
			where tblSO.[type] = ''U'' 


		end

	'

select 
		[rowNumber]
			= ROW_NUMBER() 
			OVER
				(
					ORDER BY 
						  [database] ASC
						, [schema]   ASC
						, [object]   ASC
						, [index]    ASC
				) 

		, [database] 
		, [schema]   
		, [object] 
		, [objectType] 		  
		, [index]   
		, [count]
			= count(*)
		, [aggregatedRecordLengthInBytes]
			= sum
			(
				aggregated_record_length_in_bytes
			)
		, [aggregatedRecordLengthInKB]
			= sum
			(
				aggregated_record_length_in_bytes
			)
			/ (1024)

from   @tblHOBT tblHOBT

inner join sys.dm_tran_top_version_generators AS tblSTTVG

		on tblHOBT.[dbid] = tblSTTVG.database_id
		and tblHOBT.[hobtID] = tblSTTVG.rowset_id

group by
		  [database] 
		, [schema]   
		, [object]   
		, [objectType] 
		, [index]   

order by 
		  [database] asc
		, [schema]   asc
		, [object]   asc
		, [index]    asc


Output

 

Monitor TempDB Disk Space Used

Monitor Tempdb Disk Space Used By Type ( sys.dm_db_file_space_usage )

Outline

Let us query sys.dm_db_file_space_usage to divvy up tempdb storage allocation by type.

Code

use [tempdb]
go

select 
		  getdate() AS runtime

		, [userObjectKB]
			= SUM (user_object_reserved_page_count)*8

		, [userObjectMB]
			= (SUM (user_object_reserved_page_count)*8) / (1024)

		, [internalObjectKB]
			= SUM (internal_object_reserved_page_count)*8

		, [internalObjectMB]
			= SUM (internal_object_reserved_page_count)*8 / 1024

		, [internalObjectGB]
			= SUM (internal_object_reserved_page_count)*8 / (1024 * 1024)

		, [versionStoreKB]
			= SUM (version_store_reserved_page_count)*8

		, [versionStoreKB]
			= (SUM (version_store_reserved_page_count)*8) / ( 1024)

		, [mixedextentKB]
			= SUM (mixed_extent_page_count)*8

		, [mixedextentMB]
			= SUM (mixed_extent_page_count)*8 / 1024

		, [freeSpaceKB]
			= SUM (unallocated_extent_page_count)*8

		, [freeSpaceMB]
			= SUM (unallocated_extent_page_count)*8
				/ ( 1024)

		, [freeSpaceGB]
			= SUM (unallocated_extent_page_count)*8
				/ ( 1024 * 1024)

FROM [tempdb].sys.dm_db_file_space_usage



Output

Explanation
  1. User Object is 1 MB
  2. Internal Object 166 GB
  3. mixed extent is 7 MB
  4. free space is 18 GB

 

 

Monitor Disk Space Used By Object ( tempdb.sys.system_internals_allocation_units )

Outline

Let us query tempdb.sys.system_internals_allocation_units and tempdb.sys.system_internals_partitions to gather storage allocation by object.

Code

select 
		  [object]
			= quoteName(object_schema_name(tblSIP.object_id))
				+ '.'
				+ quoteName(object_name(tblSIP.object_id))

		, tblSO.[type_desc]

		, [totalPages] 
			= sum(tblSIAU.total_pages)

		, [totalKB] 
			= sum(tblSIAU.total_pages * 8)

from   tempdb.sys.system_internals_allocation_units tblSIAU

JOIN   tempdb.sys.system_internals_partitions tblSIP

		ON tblSIAU.container_id = tblSIP.partition_id

inner join sys.objects tblSO
	on tblSIP.object_id = tblSO.object_id

where  tblSO.[type] not in 
		(
			'S'
		)

group by
		  tblSIP.[object_id]
		, tblSO.[type_desc]

order by
		sum(tblSIAU.total_pages) desc


Output

Explanation
  1. Space usage for individual objects is very miniscule

Summary

An alarm, through logging in the error log, is raised due to inability to grow the “Version Store“.

We checked sys.dm_tran_top_version_generators to see which objects are currently hogging the Version Store, we identified hangFire.server and msdb SQL Server Agent Job related tables.

We don’t really appear to have high uptake.

We went and looked at [tempdb].sys.dm_db_file_space_usage.  And, the biggest occupant seems to be internal objects.

Both tempdb.sys.system_internals_allocation_units and tempdb.sys.system_internals_partitions have very low numbers.

Conclusion

Our problem is not Version Store nor actual tables (system, user, temp ).

But, internal objects …

sys.dm_db_file_space_usage (Transact-SQL)
Link