Microsoft SQL Server database index fragmentation report

When the first row of long running queries is returned after a long initial wait, check the fragmentation of the indexes.

On MSSQL execute this query in the database

USE MY_DATABASE

SELECT OBJECT_NAME(Index_Info.OBJECT_ID) AS TableName
  ,Index_Info.name AS IndexName
  ,Index_Stat.index_type_desc AS IndexType
  ,Index_Stat.avg_fragmentation_in_percent IndexFragmPercent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) Index_Stat
INNER JOIN sys.indexes Index_Info ON Index_Info.object_id = Index_Stat.object_id
  AND Index_Info.index_id = Index_Stat.index_id
ORDER BY IndexFragmPercent DESC

Map the host drive in a VMware Fusion Windows virtual machine

Every time I start a PowerShell terminal on my Windows VMware Fusion virtual machine I have to map the host drive of my MacBook Pro. I execute this command in PowerShell:

New-PSDrive -Name z -Root "\\vmware-host\Shared Folders" -PSProvider FileSystem -Scope Global -Persist:$true

Remove byte-order mark from the beginning of the file

When a file is saved with BOM (byte-order mark) on a Mac or Linux machine Windows PowerShell cannot execute the script.

Unfortunately the “cat” command does not show the byte-order mark at the beginning of the file. Some editors show a question mark at the beginning of the file.

I have discovered it when I issued the “git diff” command:

<U+FEFF>

To remove the BOM in Visual Studio Code

  • Open the file in Visual Studio Code
  • Click the UTF-8 with BOM text at the bottom right of the screen
  • On the top select Save with Encoding
  • Select UTF-8

The “git diff” command shows the removal of the BOM

Clear cookies and site data of one site in Chrome

Some sites do not load properly when old cached data is stored in the web browser.

To clear the cached images, cookies and other site related data for one specific site in Chrome

  • Start Chrome

The quick and easy way

  • Navigate to chrome://settings/siteData
  • In the upper right corner search for the site name
  • Click the trash can to delete all site data

For the purists, the very long way to get there

  • In the upper right corner click the three dots
  • Select Settings
  • On the left side select Privacy and security
  • Under Content select Cookies and site data
  • Click See all cookies and site data
  • In the upper right corner search for the site name
  • Click the trash can to delete all site data

MSSQL database migration to another database server

When a database is migrated to another server by copying the database file or restoring it from a backup file, the original database user account references are also carried with it.

Those accounts contain the account IDs specific to the original database server.

To provide access to the restored database on the new database server we need to delete the old user accounts from the restored database and configure the database access in the new database server. This will re-create the user accounts with the correct IDs in the restored database.

For more information see https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server?view=sql-server-ver15

RuntimeError: Volume vol-… attached at xvdf but does not conform to this resource’s specifications

When the Chef aws cookbook’s ebs_volume.rb resource tries to bring a volume online, partition, and format it we get the error message:

RuntimeError: Volume vol-… attached at xvdf but does not conform to this resource’s specifications
C:/chef/cache/cookbooks/aws/resources/ebs_volume.rb:46:in `block in class_from_file’

Make sure the “size” attribute value in the aws_ebs_volume resource call matches the actual size of the volume in GiB.

The installation of this package failed

When we tried to install the Microsoft Access Database Engine and Office 2007 System Driver on a Windows Server 2016 an error message popped up immediately:

The installation of this package failed

When we ran the installation with the logging option, we have found a message at the bottom of the file:

./AccessDatabaseEngine_x64_2010 /passive /log:enginelog.txt

Will create the folder ‘\MSECache\AceRedist\1033’
CActionCreateFolder::execute ends
CActionIf::execute starts
Begin evaluation of the condition
The property ‘SYS.ERROR.INERROR’ is equal to ‘1’

The installer could create the \MSECache\AceRedist\1033 on the D: drive where we executed the program, but for some reason the directory was empty.

We decided to approach the problem in two steps:

  • Extract the installer files to two separate subfolders of the current directory, as the extracted file names are the same for the two packages.
 ./AccessDatabaseEngine_x64_2010 /passive /extract:extract_dir_2010
 ./AccessDatabaseEngine-2007-Office-System-Driver /passive /extract:extract_dir_2007
  • Run the extracted files to install the applications.
cd extract_dir_2010
./AceRedist.msi /passive

cd ../extract_dir_2007
./AceRedist.msi /passive

Invoke-WebRequest : The request was aborted: Could not create SSL/TLS secure channel.

Older PowerShell versions do not use TLS1.2 as the default version during the SSL handshake. When the API requires TLS1.2 the rrror message appears:

 Invoke-WebRequest : The request was aborted: Could not create SSL/TLS secure channel. 

To force PowerShell to use TLS1.2 during the SSL handshake, issue this command before executing the Invoke-Webrequest

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12;
Invoke-WebRequest ....

Extend a Linux partition

When the Linux disk drive is full, first we need to identify the reason for the drive overuse. Many times the drive is filled with one large log file, that can be identified and truncated.

To list the directory sizes under the current directory execute

du -sh *

To empty a file, overwrite it with nothing, so the process that writes into it still can access it.

cat /dev/null > ./MY_LARGE_LOG_FILE

When we free up the disk space, the server needs time to recover and do some housekeeping. The load average numbers show how busy the server was in the recent minutes. Check the load on the computer with

uptime

23:58:50 up 318 days, 16:32, 1 user, load average: 0.03, 5.34, 18.68

The load averages are from the past 1, 5, and 15 minutes

Grow the partition

If extra drive space is needed, enlarge the volume. We also need to grow the partition, so the operating system can access the additional space on the volume.

Grow the partition to use the entire volume. Use the growpart command, and specify the name of the volume and the partition number.

sudo growpart /dev/nvme0n1 1

If the file system is xfs, update the xfs file system metadata to use the entire partition

sudo xfs_growfs /