Drive space via powershell

If you want to get info for a remote server, you need to call the following.  This will enter a remote powershell session on the remote host.

 Enter-PSSession <hostname>

The following command will return the used and free space for the C: and Z: drives.

get-psdrive <drive> | ? {$_.Name -eq "Z" -or $_.Name -eq "C"} |  % {"Free(GB) " + $_.Free/1GB + " Used(GB) " + $_.Used/1GB]

As you see this command calls get-psdrive which gets the info for all drives.  You can optionally pass in which drive you want to get info for.  In this case it returns all drives so we can then select the two drives we are interested in.  The last step formats the output for each object in the pipeline.

How to view the XML deadlock graph

An easy way to view deadlock information is to find the deadlock report in the Extended Event system_health session.  Under the system_health session, double-click on package0.event_file.  In the window that opens, create a filter so that you only see the xml_deadlock_report.

Depending on the number of rows that you have recorded this may take awhile.  Once you have your final list, you can double-click on the report that you want to examine.  The report will come up in XML and have tons of good information for you to examine.  

However you sometimes want to view the deadlock graph itself.  To do this save the xml but change the file extension to ".xdl".  Now if you drag that file to SQL Server Management Studio, the graph will be displayed.

Similarly you can query the ring buffer with:

WITH SystemHealth

AS ( 

SELECT CAST(target_data as xml) AS TargetData

FROM sys.dm_xe_session_targets st

       JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address

WHERE name = 'system_health'

       AND st.target_name = 'ring_buffer')

SELECT XEventData.XEvent.value('(data/value)[1]','VARCHAR(MAX)') AS DeadLockGraph

FROM SystemHealth

       CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent)

WHERE XEventData.XEvent.value('@name','varchar(4000)') = 'xml_deadlock_report'

 

Or the extended event file with:

Start by finding the path to the files:

SELECT target_data

FROM sys.dm_xe_session_targets st

INNER JOIN sys.dm_xe_sessions s 

ON s.address = st.event_session_address

WHERE name = 'system_health'

AND target_name = 'event_file'

then query the data

Select Cast(event_data AS xml) AS TargetData

FROM sys.fn_xe_file_target_read_file('C:\events\system_health*.xel',NULL,NULL,NULL)

WHERE object_name like 'xml_deadlock_report';

references: https://social.technet.microsoft.com/wiki/contents/articles/31280.finding-and-extracting-deadlock-information-using-extended-events.aspx

TSQL Timezone conversion

Working through some extended events I needed to correlate events based to observations from end users. The extended events record the events in UTC so I needed to adjust the time to local time.  Initially I tried doing it in my head and always messed it up, so I just modified my query.

I played around with a couple ways to convert the results to the appropriate local time.  The first way involved calculating the timezone offset and applying it to the datetime value.


SELECT CONVERT(DATETIME, '2020-03-10 13:30') AS UTC,
CONVERT(DATETIME, switchoffset(convert(datetimeoffset, '2020-03-10 13:30'), DATENAME(TZOffset, sysdatetimeoffset()))) as LocalDateTime;

This results are:

UTC                     LocalDateTime
----------------------- -----------------------
2020-03-07 13:30:00.000 2020-03-07 08:30:00.000

(1 row affected)

The LocalDateTime field calculates the timezone offset to be -5 and subtracts that from the UTC date returning the correct value.  The problem is when you run into Daylight Savings Time (DST).  For example, if I run the above function today (3/10/2020) which is currently in DST, I get an offset of -5.  But if I apply want to convert a date prior to DST, for example last Tuesday (3/3/2020) I will still get a value of 8:30.  However, the offset on 3/3/2020 should be -6.

There is new functionality available with SQL Serve 2016 plus that takes DST into consideration and is much simpler to use.  To do this you use the AT TIME ZONE clause (https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15).  You can take a time in UTC and specify which timezone you want it converted to.  To see this in action, look at the following two queries.


SELECT CONVERT(datetime, '2020-03-07 13:30') UTC, CONVERT(DATETIMEOFFSET, '2020-03-07 13:30') AT TIME ZONE 'Central Standard Time' AS LocalDateTime;

SELECT CONVERT(DATETIME, '2020-03-10 13:30') UTC, CONVERT(DATETIMEOFFSET, '2020-03-10 13:30') AT TIME ZONE 'Central Standard Time' AS LocalDateTime;

UTC                     LocalDateTime
----------------------- ----------------------------------
2020-03-07 13:30:00.000 2020-03-07 07:30:00.0000000 -06:00

(1 row affected)

UTC                     LocalDateTime
----------------------- ----------------------------------
2020-03-10 13:30:00.000 2020-03-10 08:30:00.0000000 -05:00

(1 row affected)

The first query is not in DST and converts the value 3/7/2020 13:30 to the correct value 3/7/2020 07:30.  Note the value returned is now is off the datetimeoffset datatype withe an offset of -6

The second query takes into consideration the change in DST and converts the value to 3/7/2020 8:30.  This is using an offset of -5.

Similarly, you can easily convert from a local time to UTC.  To do this you just apply the at time zime twice.  The first converts your datetime value to a datetimeoffset datatype with the correct offset, and the second then converts that value to UTC.

SELECT(CONVERT(DATETIME,'2020-03-10 8:30') AT TIME ZONE 'Central Standard Time') AT TIME ZONE 'UTC';

----------------------------------
2020-03-10 15:49:17.393 +00:00

(1 row affected)

To find your specific time zone you can query the sys.time_zone_info view.  This includes the current_utc_offset and if the timezone is in DST.

SELECT * FROM sys.time_zone_info WHERE name LIKE '%Central%'

                                                                
name                               current_utc_offset is_currently_dst
---------------------------------- ------------------ ----------------
Central America Standard Time      -06:00             0
Central Standard Time              -05:00             1
Central Standard Time (Mexico)     -06:00             0
Canada Central Standard Time       -06:00             0
Central Brazilian Standard Time    -04:00             0
Central Europe Standard Time       +01:00             0
Central European Standard Time     +01:00             0
W. Central Africa Standard Time    +01:00             0
Central Asia Standard Time         +06:00             0
N. Central Asia Standard Time      +07:00             0
Aus Central W. Standard Time       +08:45             0
AUS Central Standard Time          +09:30             0
Central Pacific Standard Time      +11:00             0


(13 rows affected)

You can also use the function CURRENT_TIMEZONE_ID() to retrieve the local servers time zone.

Hope this helps!

Verbose truncation message

Do you get frustrated when you insert or update data and you get the message:

truncation occurred.

Microsoft finally heard us and is changing the message to give us more information.  If you are on SQL Server 2019 and have your compatibility level set to 150 then you already get the new error message.  If you aren't so lucky, but are using SQL Server 2017 CU12 or higher then you can enable traceflag 460 to get a detailed truncation message.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15

You can enable this traceflag by running

DBCC TRACEON(460)

This changes the truncation message for your current session to the form:

String or binary data would be truncated in table '<Table>', column '<column>'. Truncated value: '<value>'.

To disable you simply run

DBCC TRACEOFF(460)

You can verify it is off by running

DBCC TRACESTATUS

Viewing Windows Update Files

Windows no longer writes logs entries for Windows Update to "C:\Windows\WindowsUpdate.log".  If you open the file you will see the following:

So if you run get-windowsupdatelog powershell cmdlet, the system will generate log files in your current working directory.

If you run this on a Windows 2016 server you might get the following:

To fix, search the c:\windows\winSxS folder for SymSrv.dll and copy it to: c:\Program Files\Windows Defender\

Next time you run the cmdlet your files will be generated.

However, this generates a file that is basically useless.  To get usable information you need to download the symbol files, etc...

You can view the following technet article for more information:

https://social.technet.microsoft.com/Forums/en-US/4e81956f-9ed0-4dc2-be75-8fb54d065768/windowsupdatelog-no-format-information-found?forum=win10itprogeneral

Running applications as another user

There are many times that you want to run an application as a different user.  You might want to test out security as a different restricted user, or connect to a service on another domain.  This command should be able to help you out.  I wish I knew about it long ago, it would have saved me so much time.

Probably the simplest way to run an application as another user it to use the shift-right-click functionality in windows.  You do this by pressing the shift key and right-clicking on the application icon.  You then should get hte option to "Run as a different User".  You then are prompted for credentials.  Once authenticated the application will start running as that user.

This works great, but there are some issues.  The main issue that I always ran into was that the application is now running as the user you select.  You probably are thinking Duh!  But that means shortcuts to local resources like the Desktop or My Documents may not be accessible.  At the very least they will point to the resources for the account you are running as. 


So what other options are there?  There is a nifty command called runas.exe  There are a number of switches, but the ones I want to mention are /user and /netonly.  The /user switch defines which account you want to use.  You can enter it in the form of user@domain or domain\user. 

The second switch is /netonly. This help says this switch will only use the specified credentials for remote access only.  So when accessing services on the workstation you are logged into, or access local resources like your My Documents, you will be able to access them as you would with out the runas command.  However, if you try to connect to a remote resource on another machine the specified user account is now used.  This is amazing, I wish I would have known of this switch years ago!  This will be such a time saver as I use SQL Server Management Studio and frequently open and save scripts to local drives.

Now how do we use this?  The obvious way is to open a command prompt and type out the command in the following format:

runas.exe /netonly /user:Domain\user application

But that isn't always the best way.  A simpler way might be to create a shortcut.  This way you can simply just click the shortcut to start the application as the other user.  This is ideal if you perform this action a lot, saving you time by not having to type in the command.

To create the shortcut, just right right-click on your desktop and select new shortcut.  In the text box for the location enter the command above.  Next enter the name of the shortcut that you would like.  I like to use something in the form of 'SSMS as domain\user'.  Click finish and you are done.  Now if you want to be fancy then right-click on your new shortcut and select properties.  You can now modify any remaining properties like the Icon.  If you click "Change Icon..." and point to your application, it will probably have an embedded icon that you can use.  You can also right-click and pin to the start menu or taskbar, whichever you prefer.

Now that you have a shortcut you can run the application.  A command window will open prompting you for your password.  Enter it in and press Enter.

 If successful, you will see a message that it is attempting to start the application. 

Once the application starts you will be able to access the remote resources using the specified user.  Hope this helps someone out!

Windows file server quota reporting incorrect usage

Occasionally, I have a user report that a network drive they are working on is reporting that it is full and will no longer let them save documents.  When looking at the drive they calculate that the space used is much lower than what the network drive is reporting.  I'm not sure what causes this, but seems to happen more often when large amounts of data are deleted and Windows has a quota on the drive.

In order to fix, you can initiate a quota scan using the following command on the server:

dirquota quota scan /path:c:\path

Note the path c:\path should be replaced with the actual path to the folder on the server, not the UNC path.  This command will return immediately, but it will start a background task tell windows to rescan the quotas defined on the path provided.  Once complete it will update the quota with the correct usage.  The time it takes will vary depending on the size of the drive and the number of files.

It appears that in Server 2019 this is now deprecated and you need to use the commands in the FileServerResourceManager Powershell module.  To initiate a scan you need to start using the following command:

update-fsrmquota -path "C:\path"

To get quota information you can use get-fsrmquota.  example:

get-fsrmquota -path "C:\path"