Find out when/who dropped an object without auditing

If your environment doesn’t have any kind of auditing and you need to find since when an object was dropped.

If you have the default trace enabled (good practice) you can track when the object was dropped and determine if that was the root cause for some performance issue for example.

The point here is to find the root cause of an issue, not point the finger to who did it 😉

First, let’s check what kind of events the default trace has

SELECT DISTINCT Trace.EventID
        , TraceEvents.NAME AS Event_Desc
 FROM ::fn_trace_geteventinfo(1) Trace
 JOIN sys.trace_events TraceEvents ON Trace.eventID = TraceEvents.trace_event_id 

You are going to see the events

The next step is get the default trace path

SELECT path
 FROM sys.traces
 WHERE is_default = 1;

Knowing the path you can get all the files name using xp_cmdshell for example

EXEC xp_cmdshell 'dir e:\"Program Files"\"Microsoft SQL Server"\"MSSQL10.MSSQLSERVER"\MSSQL\*.trc'

Grab the file name you want and change the script below

SELECT 
    CASE EventClass
        WHEN 46 THEN 'Object:Created'
        WHEN 47 THEN 'Object:Deleted'
        WHEN 164 THEN 'Object:Altered'
    END
    , DatabaseName
    , ObjectName
    , HostName
    , ApplicationName
    , LoginName
    , StartTime
 FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\your_file_name.trc', DEFAULT)
 WHERE 
    EventClass IN (46,47,164) 
    AND EventSubclass = 0 
 ORDER BY StartTime DESC

In my scenario what is showing is the creating object in the bottom with some index creation, drop index, object alter (it’s not telling what kind of change), create a new object and alter (the primary key creates the index and alter the table adding the constraint).

Plus you can see the login name and when the action was made. 😉

Install dbatools module without admin rights

If you have been face a issue that you don’t have rights to install any powershell module like showing the image below for dbatools, one workaround is to install it only for your user.

• 

In this case you can use the scope parameter to install it in your user only as you can see in the message.

Install-Module dbatools -Scope currentuser

How to add Active Directory powershell module

I’m going to show the steps how to add the AD module in your machine and in the server if you need to get information like run Get-ADPrincipalGroupMembership to know what groups a specific user belongs.

To add this module in your local machine is quite easy. Install the Remote Server Administration Tools for Windows 10 package

But, to install it in a Windows Server for example you need to add this feature following the steps .

1. Open Server Manager Dashboard
2. Click Manage -> Add Roles and Features Wizard
3. Click next until to show the picture below in the item features

After install it (no restart needed) you can run import-module ActiveDirectory and run something like this:

Get-ADPrincipalGroupMembership -Identity 'user_name' | Select-Object name

$groups = Get-ADGroup -Filter {name -like 'user_group'} -Properties * | Select -property name

foreach($group in $groups) {
    if($group.name -ceq 'Group trying to find') {
        $group.name
        Get-ADGroupMember -Identity $group.name | Where-Object objectClass -Like 'group' | Select-Object name
    }
}

Powershell function to get extended service account info

I created this function to get more information for any service account, for example if you need to know the login use in SQL Server services.

Check the script below and also can download here

<#
.SYNOPSIS
Get-ServiceAccountInfo retrieves account info from windows services

.DESCRIPTION
Get-ServiceAccountInfo gets the data from services accounts

.PARAMETER ComputerName
Server name that you want to get information

.PARAMETER Filter
Specify a value name to filter by service name

.PARAMETER Credential
Provide the user name and password to retrive the services information if not logged with admin account.

.EXAMPLE
Get-ServiceAccountInfo -ComputerName 'server_name' -Filter 'SQL'
#> 
function Get-ServiceAccountInfo {
    [cmdletbinding()] 
    param (
        [Parameter(Mandatory = $true
            , ValueFromPipelineByPropertyName = $true
            , Position = 0)]
        [string[]]$ComputerName,

        [Parameter(Mandatory = $false, 
            Position = 1)]
        [string]$Filter,

        [Parameter(Mandatory = $false, 
            Position = 2)]        
        [PSCredential]$Credential
    )            
    begin {
        $params = @{
            Class           = 'Win32_Service';
            ComputerName    = '';
        }

        if($Credential){
            $params.Credential = $Credential
        }

        if($Filter){
            $Filter = "*" + $Filter.TrimStart('*').TrimEnd('*') + "*"
        }

        $outArray = @()
    }
    process{
        foreach($Computer in $ComputerName) {
            $params.ComputerName = $Computer

            try{
                if($Filter) {
                    $data = Get-WmiObject @params | Where-Object { $_.StartName -like $Filter } | Select-Object DisplayName, StartName, State, Name, StartMode, Status, Description
                } else {
                    $data = Get-WmiObject @params | Select-Object DisplayName, StartName, State, Name, StartMode, Status, Description
                }
            
            $outObj = New-Object PSObject 
            Add-Member -InputObject $outObj -MemberType NoteProperty -Name ComputerName -Value $Computer
            Add-Member -InputObject $outObj -MemberType NoteProperty -Name ServiceName -Value $data.Name
            Add-Member -InputObject $outObj -MemberType NoteProperty -Name DisplayName -Value $data.DisplayName
            Add-Member -InputObject $outObj -MemberType NoteProperty -Name StartName -Value $data.StartName
            Add-Member -InputObject $outObj -MemberType NoteProperty -Name State -Value $data.State
            Add-Member -InputObject $outObj -MemberType NoteProperty -Name StartMode -Value $data.StartMode
            Add-Member -InputObject $outObj -MemberType NoteProperty -Name Status -Value $data.Status
            Add-Member -InputObject $outObj -MemberType NoteProperty -Name Description -Value $data.Description

            $outArray += $outObj
            } catch {
                Write-Message -Level Error -Message $_.Exception.Message 
            }
        }
    } end {
        Write-Output $outArray
    }

}

Common Git situations and how to solve it

Work with Git can be easy, but sometimes we need to know more than just pull, add, commit and push.

So, below you are going to find examples how to solve the most common scenarios you could face working with Git.

Combine with the previous commit

git commit --amend

Committed all those changes to the master branch by mistake

git branch New-branch
git reset HEAD~ --hard
git checkout New-branch

Made a spelling mistake in my branch name

git branch -m New-brunch New-branch

Clean up local commits before pushing

git rebase -i

Reverting pushed commits

git revert HEAD

Edit a commit message

git commit --amend -m “new message”

Remove a file from git without removing it from your file system

git reset filename

Undo local commits

git reset HEAD

Discard local file modifications

git checkout -- something

Rollback to a specific point

git reflog 
git reset HEAD@{XX} 
-- git add or checkout

Checking the differences between remote and local repository

git diff origin/master master

Useful transactional replication stored procedure

The scenario is when your publication settings has the option
immediate_sync configured as false (means if you run the Snapshot Agent, the snapshot files will be created for the new articles only and also a new subscriber to the existing publication, the snapshot files only get created for the new subscription)

This option immediate_sync will prevent to generate all snapshot files again when add a new article in the replication, but it still need schema lock to check all articles modification.

If you have a large number of articles and a bunch of subscribers, you might face the situation you have to add a new article in your transactional replication and after run the snapshot the article is not propagating to the subscribers.

First, check your publication settings.

exec sp_helppublication 'PublicationName'
GO

If you are using the settings immediate_sync and allow_anonymous false you can run the procedure below to solve the issue that your new article didn’t propagate to your subscribers.

exec sp_refreshsubscriptions 'PublicationName'
GO

T-SQL script to get detailed login permissions

This script is very helpful when you need to know all the permissions a user have in detail.

 

SELECT distinct 
@@SERVERNAME as ServerName
, UserName as LoginName
, UserType as LoginType
, DatabaseUserName
, Role
, PermissionType
, PermissionState
, DatabaseName = db_name()
, ObjectName  -- can be an object or the entire database
, ObjectType
, ColumnName
--	, item -- used to check consistencies in each subquery
FROM (
SELECT  
    UserName = princ.name,
    UserType = CASE princ.type
                    WHEN 'S' THEN 'SQL'
                    WHEN 'U' THEN 'Windows'
					WHEN 'G' THEN 'Windows Group'
					WHEN 'R' THEN 'Database Role'
					ELSE princ.type 
                END,  
    DatabaseUserName = princ.name,       
    Role = princ.name,      
    PermissionType = perm.permission_name,       
    PermissionState = perm.state_desc,       
    ObjectType = obj.type_desc,
    ObjectName = coalesce(OBJECT_NAME(perm.major_id), db_name()),
    ColumnName = col.name,
	1 as item
FROM    
    sys.database_principals princ  
LEFT JOIN sys.login_token ulogin on princ.sid = ulogin.sid
LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = princ.principal_id
LEFT JOIN sys.columns col ON col.object_id = perm.major_id AND col.column_id = perm.minor_id
LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id
WHERE perm.permission_name <> 'CONNECT' 
UNION
SELECT  
    UserName =  memberprinc.name ,
    UserType = CASE memberprinc.type
                    WHEN 'S' THEN 'SQL'
                    WHEN 'U' THEN 'Windows'
					WHEN 'G' THEN 'Windows Group'
					WHEN 'R' THEN 'Database Role'
					else memberprinc.type 
                END, 
    DatabaseUserName = memberprinc.name,   
    Role = roleprinc.name,      
    PermissionType = perm.permission_name,       
    PermissionState = perm.state_desc,       
    ObjectType = obj.type_desc,
    ObjectName = db_name(),
    ColumnName = col.name,
	2 as item
FROM    
    sys.database_role_members members
INNER JOIN sys.database_principals roleprinc ON roleprinc.principal_id = members.role_principal_id
INNER JOIN sys.database_principals memberprinc ON memberprinc.principal_id = members.member_principal_id
LEFT JOIN sys.login_token ulogin on memberprinc.sid = ulogin.sid
LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = roleprinc.principal_id
LEFT JOIN sys.columns col on col.object_id = perm.major_id AND col.column_id = perm.minor_id
LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id
UNION
SELECT  
    UserName = roleprinc.name,
    UserType = 
	CASE roleprinc.type
                    WHEN 'S' THEN 'SQL'
                    WHEN 'U' THEN 'Windows'
					WHEN 'G' THEN 'Windows Group'
					WHEN 'R' THEN 'Database Role'
					ELSE roleprinc.type 
                END,  
    DatabaseUserName = roleprinc.name,
    Role = roleprinc.name,      
    PermissionType = perm.permission_name,       
    PermissionState = perm.state_desc,       
    ObjectType = obj.type_desc,
    ObjectName = coalesce(OBJECT_NAME(perm.major_id), db_name()),
    ColumnName = col.name,
	3 as item
FROM    
    sys.database_principals roleprinc 
LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = roleprinc.principal_id
LEFT JOIN sys.columns col on col.object_id = perm.major_id AND col.column_id = perm.minor_id                   
LEFT JOIN sys.objects obj ON obj.object_id = perm.major_id
UNION
SELECT 
    UserName = princ.name collate Latin1_General_CI_AS,
    UserType = CASE princ.type
                    WHEN 'S' THEN 'SQL'
                    WHEN 'U' THEN 'Windows'
					WHEN 'G' THEN 'Windows Group'
					WHEN 'R' THEN 'Database Role'
					ELSE princ.type 
                END ,  
    DatabaseUserName = princ.name collate Latin1_General_CI_AS,
    Role =  CASE 
                WHEN logins.sysadmin = 1 THEN 'sysadmin'
                WHEN logins.securityadmin = 1 THEN 'securityadmin'
                WHEN logins.serveradmin = 1 THEN 'serveradmin'
                WHEN logins.setupadmin = 1 THEN 'setupadmin'
                WHEN logins.processadmin = 1 THEN 'processadmin'
                WHEN logins.diskadmin = 1 THEN 'diskadmin'
                WHEN logins.dbcreator = 1 THEN 'dbcreator'
                WHEN logins.bulkadmin = 1 THEN 'bulkadmin'
                ELSE 'Public' 
            END,
    PermissionType	= perm.permission_name,
    PermissionState = 'GRANT',
    ObjectType		= NULL,
    ObjectName		= princ.default_database_name,
    ColumnName		= NULL,
	4 as item
FROM sys.server_principals princ 
INNER JOIN sys.syslogins logins ON princ.sid = logins.sid 
LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = princ.principal_id
WHERE princ.type  <> 'R' AND princ.name NOT LIKE '##%'
) P  
where (Role <> 'Public' or ObjectName = db_name())
ORDER BY
P.DatabaseUserName,
P.ObjectName,
P.ColumnName,
P.PermissionType,
P.PermissionState,
P.ObjectType

Method to update LOB data with minimal log

Use the .WRITE (expression, @Offset,@Length) clause to perform a partial or full update of varchar(max), nvarchar(max), and varbinary(max) data types.

For example, a partial update of a varchar(max) column might delete or modify only the first 200 characters of the column, whereas a full update would delete or modify all the data in the column. .WRITE updates that insert or append new data are minimally logged if the database recovery model is set to bulk-logged or simple.

UPDATE <table_or_view_name>
SET column_name.WRITE (expression, @offset, @length)
FROM <table_source>
WHERE <search_condition>

expression is the value that is copied to column_name. expression must evaluate to or be able to be implicitly cast to the column_name type. If expression is set to NULL, @Length is ignored, and the value in column_nameis truncated at the specified @Offset.

@Offset is the starting point in the value of column_name at which expression is written. @Offset is a zero-based ordinal position, is bigint, and cannot be a negative number. If @Offset is NULL, the update operation appends expression at the end of the existing column_name value and @Length is ignored. If @Offset is greater than the length of the column_name value, the Database Engine returns an error. If @Offset plus @Length exceeds the end of the underlying value in the column, the deletion occurs up to the last character of the value. If @Offset plus LEN(expression) is greater than the underlying declared size, an error is raised.

@Length is the length of the section in the column, starting from @Offset, that is replaced by expression. @Length is bigint and cannot be a negative number. If @Length is NULL, the update operation removes all data from @Offset to the end of the column_name value.

The regular update statement results in overwriting the entire string using full logging and it’s very inefficient when dealing with large value updates.

Note: The update using WRITE method will fail if the value is null.

More information about performance in my tip on MSSQLTips

 

 

How to enable stretch database

After the tip https://www.mssqltips.com/sqlservertip/5526/how-to-setup-and-use-a-sql-server-stretch-database/ I made a video to show step by step how to enable stretch database.

Using wait stats on SQL Server

I wrote two posts about wait stats:

1.  What wait means
2. About SQL Server saving wait stats on DMVs

Now, going deeper on wait stats and see “why SQL Server is running slow?”. To answer that question I like to start with the DMV sys.dm_os_wait_stats, because this DMV provides a running total of all waits encontered by executing threads in SQL Server instance.

SQL Server categorizes waits across several different type and some of these types only indicate quit period on the instance where threads stay in waiting.

The script below shows the top wait types that have accumulated since SQL Server started or was cleared.

WITH Waits AS (
SELECT  wait_type
    , CAST(wait_time_ms / 1000. AS DECIMAL(12, 2)) AS [wait_time_s]
    , CAST(100. * wait_time_ms / SUM(wait_time_ms) OVER () AS DECIMAL(12, 2)) AS [pct]
    , ROW_NUMBER() OVER (ORDER BY wait_time_ms DESC) AS rn 
FROM sys.dm_os_wait_stats WITH (NOLOCK) 
WHERE  
     wait_type NOT IN (N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP', N'RESOURCE_QUEUE', N'SLEEP_TASK', N'SLEEP_SYSTEMTASK', N'SQLTRACE_BUFFER_FLUSH', N'WAITFOR', N'LOGMGR_QUEUE', N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH', N'XE_TIMER_EVENT', N'BROKER_TO_FLUSH', N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT', N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'XE_DISPATCHER_WAIT', N'XE_DISPATCHER_JOIN', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'ONDEMAND_TASK_QUEUE', N'BROKER_EVENTHANDLER', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'DIRTY_PAGE_POLL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'PWAIT_ALL_COMPONENTS_INITIALIZED')), Running_Waits AS (
SELECT  W1.wait_type
    , wait_time_s
    , pct
    , SUM(pct) OVER (ORDER BY pct DESC ROWS UNBOUNDED PRECEDING) AS [running_pct] 
FROM   Waits AS W1)
SELECT     
    wait_type
    , wait_time_s
    , pct
    , running_pct
FROM      Running_Waits
WHERE     running_pct - pct = 99
ORDER BY  running_pct
OPTION (RECOMPILE);

This query I got from Paul Randal blog, his blog has a lot of information about waits. Since not all wait types are indicators of real issue, the where clause is removing unnecessary type.

The common wait types for me are in the table below, there are much more, but you can start with that list.

Wait_Type	Area	Description	Action
ASYNC_IO_COMPLETION	I/O	Used to indicate a worker is waiting on a asynchronous I/O operation to complete not associated with database pages	Since this is used for various reason you need to find out what query or task is associated with the wait. Two examples of where this wait type is used is to create files associated with a CREATE DATABASE and for “zeroing” out a transaction log file during log creation or growth.
CHECKPOINT_QUEUE	Buffer	Used by background worker that waits on events on queue to process checkpoint requests. This is an “optional” wait type see Important Notes section in blog	You should be able to safely ignore this one as it is just indicates the checkpoint background worker is waiting for work to do. I suppose if you thought you had issues with checkpoints not working or log truncation you might see if this worker ever “wakes up”. Expect higher wait times as this will only wake up when work to do
CHKPT	Buffer	Used to coordinate the checkpoint background worker thread with recovery of master so checkpoint won’t start accepting queue requests until master online	You should be able to safely ignore. You should see 1 wait of this type for the server unless the checkpoint worker crashed and had to be restarted.. If though this is technically a “sync” type of event I left its usage as Background
CXPACKET	Query	Used to synchronize threads involved in a parallel query. This wait type only means a  parallel query is executing.	You may not need to take any action. If you see high wait times then it means you have a long running parallel query. I would first identify the query and determine if you need to tune it. Note sys.dm_exec_requests only shows the wait type of the request even if multiple tasks have different wait types. When you see CXPACKET here look at all tasks associated with the request. Find the task that doesn’t have this wait_type and see its status. It may be waiting on something else slowing down the query. wait_resource also has interesting details about the tasks and its parallel query operator
IO_COMPLETION	I/O	Used to indicate a wait for I/O for operation (typically synchronous)  like sorts and various situations where the engine needs to do a synchronous I/O	If wait times are high then you have a disk I/O bottleneck. The problem will be determining what type of operation and where the bottleneck exists. For sorts, it is on the storage system associated with tempdb. Note that database page I/O does not use this wait type. Instead look at PAGEIOLATCH waits.
LAZYWRITER_SLEEP	Buffer	Used by the Lazywriter background worker to indicate it is sleeping waiting to wake up and check for work to do	You should be able to safely ignore this one. The wait times will appear to “cycle” as LazyWriter is designed to sleep and wake-up every 1 second. Appears as LZW_SLEEP in Xevent
LOGBUFFER	Transaction Log	Used to indicate a worker thread is waiting for a log buffer to write log blocks for a transaction	This is typically a symptom of I/O bottlenecks because other workers waiting on WRITELOG will hold on to log blocks. Look for WRITERLOG waiters and if found the overall problem is I/O bottleneck on the storage system associated with the transaction log
RESOURCE_SEMAPHORE	Query	Used to indicate a worker is waiting to be allowed to perform an operation requiring “query memory” such as hashes and sorts	High wait times indicate too many queries are running concurrently that require query memory. Operations requiring query memory are hashes and sorts. Use DMVs such as dm_exec_query_resource_semaphores and dm_exec_query_memory_grants
SOS_SCHEDULER_YIELD	SQLOS	Used to indicate a worker has yielded to let other workers run on a scheduler	This wait is simply an indication that a worker yielded for someone else to run. High wait counts with low wait times usually mean CPU bound queries. High wait times here could be non-yielding problems
THREADPOOL	SQLOS	Indicates a wait for a  task to be assigned to a worker thread	Look for symptoms of high blocking or contention problems with many of the workers especially if the wait count and times are high. Don’t jump to increase max worker threads especially if you use default setting of 0. This wait type will not show up in sys.dm_exec_requests because it only occurs when the task is waiting on a worker thread. You must have a worker to become a request. Furthermore, you may not see this “live” since there may be no workers to process tasks for logins or for queries to look at DMVs.
WRITELOG	I/O	Indicates a worker thread is waiting for LogWriter to flush log blocks.	High waits and wait times indicate an I/O bottleneck on the storage system associated with the transaction log

The next posts I will show an example for each wait in the list.

Prost!