Tracking activity to word document for audit purpose or to prove the compliance

More than once we need to provide a prove of some of the steps, if not all, that we took while implementing a change or when solving a ticket. Sometimes this can be accomplished easily using the screen capture software or just the Microsoft built in print screen while in other cases depending on the length of the activity that we need to track it becomes heavily intrusive.

Because of this I started to search how I can do it easier by the mean of automation and powershell. The problems/questions to solved were:

  • how often I should take a capture
  • what the capture size should be
  • which capture I should write to the document

The answers to those questions were

  • every second I am checking if a mouse movement occurred and if yes a capture will be taken at the position where the mouse was
  • I chose a size that will show me enough area surrounding the mouse. This area can be changed to fit your requirements but it will affect the document size.
  • all captures taken when the mouse moves, movement of the mouse means we did something. I am not using the keyboard too much while performing my day to day tasks.

We need of course to have Word and before saving the document we need to stop the script execution.

Below is how the document look like

The script can be downloaded from here

SQL Server SPID – tracking how spid affects sql server

SQL Server Management Studio offers a lot of tools to track what a spid is doing and how it affects the sql server and other spids. Some of the tools can offer information at a very granular level while others to an wider level. Because sometimes we just want something between we need to use what Microsoft offers and use it appropriately. To have an overview of a specifc spid we can use:

  • sp_who2
  • sp_lock
  • built in dmv
  • Activity Monitor
  • extended events

Or we can use custom script that will aggregate information using Microsoft dmvs and SPs and present them. The biggest advantage of using custom scripts is that we can choose

  • to display results to text
  • to display columns as rows
  • to create a include client statistics look like

Microsoft is already doing this when we choose Query – Include Client Statistics which is why I decided to duplicate that and show status,blocking_session_id,wait_type,wait_duration_ms,cpu_time,memory_usage,total_scheduled_time,reads,writes,logical_reads,arithabort,transaction_isolation_level,lock_timeout,deadlock_priority
,row_count,prev_error,group_id.

The output looks like below and I want to mention before anything else that the script used is providing a broad overview and was created on spot only to test how the output look like. Because of this

  • The output should be read and understood from this perspective.
  • The script and the way was written should be seen from this perspective also.

The values that I choose to show are not the most important and they were choose only to show that by choosing to display columns like status we decrease the amount of info that can fit but we can code some of the columns using numeric values, 1 for running, 2 for sleeping and so on. The biggest advantage of this type of display is that we can see how specific spid behave up and down by having only one look throughout of execution time.

Depending on the things that you want to track you can create your own script using the script below as an example. The only thing that you need to check is Results to Text in SSMS.

You can download this script from here

SQL Server full backups – size, space required

SQL Server backups started to fail now and then due to space issues. There is something that I can do to make it work?

The answer to that question is obvious and we just need to allocate more space to the location where we save the backups. But how much space we need to allocate? The answer is to check the space used by the last full backup for all the databases as a starting point and multiply it with the retention period.

 

In order to have that value we can use the below script to see the sum of all the latest full backups taken on our instance. Because of the reasons below the size that we see might not be the total size that we need but it is still a good estimation of what we might need for a successfull backup.

  • databases were created and for them we haven’t taken yet a backup
  • databases were backed up in the past but the file is not available on the disk to check the size

The script give us a real number when the last full backup of all the databases was successfully and the files are still on disk.

In our environment that full backup file receives _full while in other environments the naming convention might be different which is why the script must be modified a little bit. Also if we want to have an estimation about latest differential we need to modify the script. In red below you can see where you need to modify for the desired results.

SELECT bmf.physical_device_name as path FROM
(select media_set_id = MAX(bs.media_set_id) FROM sys.databases d LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = d.name AND bs.is_copy_only = 0 AND bs.type =’D‘ GROUP BY d.Name, bs.type) r
LEFT OUTER JOIN msdb.dbo.backupmediafamily bmf ON r.media_set_id = bmf.media_set_id where bmf.physical_device_name is not null and bmf.physical_device_name like ‘%_full%’

The script output looks like below

and you can download it from here

Check and Register SPN for our SQL Server Instances

Goal

To check if our SQL Server instance registered its SPN and to register if not.

My way of achieving it

Using powershell.

Sometimes when troubleshooting connection issues we end up realizing that it was caused due to Kerberos and the solution is to check for SPN and to create it in case is not there. Because the creation requires the knowing of the port under which SQL Server instance is listening and also the service account under which the service is running I found useful the usage of a script that will generate and check the existence and the creation of this SPN. The script can be executed in powershell console and I prefer the one that you can open while you are using SSMS and from contextual menu you choose Start Powershell.

The output of the script looks like below

The script can be downloaded from here

SQL Server – map drives to disk numbers

Goal

To obtain the disk number of all the drives together with drive size and free space.

My way of achieving it

Using powershell and CIM_StorageExtent.

For a long period now I tried different methods and approaches in order to map the drives in Windows to their respective disk numbers. Today starting from what Granger Godbold said here, https://rvdnieuwendijk.com/2012/05/29/powershell-function-to-get-disk-scsi-lun-number/ , I finally managed to have that information. The class that I used is CIM_StorageExtent. I started with this Get-WmiObject CIM_StorageExtent  and after I filtered the output to not show me the Memory like objects I noticed that maybe the objects there appears in a specific order and I checked my assumption

name|size|freespace
Disk #1, Partition #0|214748364800|
Disk #6, Partition #0|214748364800|
Disk #7, Partition #0|214748364800|
Disk #8, Partition #0|214748364800|
Disk #9, Partition #0|214748364800|
Disk #10, Partition #0|214748364800|
Disk #11, Partition #0|268435456000|
Disk #12, Partition #0|53684994048|
Disk #13, Partition #0|53684994048|
Disk #14, Partition #0|53684994048|
Disk #15, Partition #0|53684994048|
Disk #2, Partition #0|429498826752|
Disk #16, Partition #0|53684994048|
Disk #17, Partition #0|53684994048|
Disk #18, Partition #0|53684994048|
Disk #19, Partition #0|53684994048|
Disk #20, Partition #0|161059176448|
Disk #21, Partition #0|53684994048|
Disk #22, Partition #0|161059176448|
Disk #23, Partition #0|53684994048|
Disk #24, Partition #0|53684994048|
Disk #25, Partition #0|53684994048|
Disk #3, Partition #0|644249288704|
Disk #26, Partition #0|53684994048|
Disk #27, Partition #0|53684994048|
Disk #28, Partition #0|107372085248|
Disk #29, Partition #0|53684994048|
Disk #30, Partition #0|161059176448|
Disk #31, Partition #0|53684994048|
Disk #32, Partition #0|53684994048|
Disk #33, Partition #0|161059176448|
Disk #34, Partition #0|53684994048|
Disk #35, Partition #0|966376030208|
Disk #4, Partition #0|214748364800|
Disk #36, Partition #0|966376030208|
Disk #37, Partition #0|1234814631936|
Disk #38, Partition #0|966376030208|
Disk #39, Partition #0| 1070596096|
Disk #5, Partition #0|214748364800|
Disk #0, Partition #0|209715200000|
Disk #0, Partition #1| 90248839168|
T:\SQLData\Data01\||214480257024
T:\SQLData\Data04\||36030709760
T:\SQLData\Data05\||336353427456
T:\SQLData\Data06\||183024680960
T:\SQLData\Data07\||141081640960
T:\SQLData\Data08\||214481960960
T:\SQLData\Data09\||214639247360
T:\SQLData\Data10\||214639247360
T:\SQLData\Tempdb\Data01\||212532592640
T:\SQLData\Data02\||23798415360
T:\SQLData\Data03\||70669697024
T:\SQLData\Tempdb\Log01\||53051457536
T:\SQLData\Log01\||32302104576
X:\SQLData\Tempdb\Log01\||53051981824
U:\SQLData\Data01\||53576269824
U:\SQLData\Log01\||53576269824
Y:\SQLData\Tempdb\Data01\|51477938176
…….

 

It looks like all the drives, the ones containing “\” appears in the following order:

  • disk order , partition 0
    • disk 0, partition 0
    • disk 1, partition 0
    • disk 39, partition 0
  • disk order, partition order
    • disk 0, partition 1
    • disk 0, partition 2
    • disk 0, partition n
    • disk 1, partition 1
    • disk 1, partition n
    • …..

This is the order that I noticed when I had ONLY SIMPLE VOLUMES. Maybe the order will be differently in case we have spanned volumes.

But because usually the servers in my environment are not using spanned volumes I decided to make a script to take advantage of what I just saw. The output of the script looks like below

The script can be downloaded from here