Product:

Microsoft SQL server 2016

Issue:

SQL agent can not send mail. There is no error in windows event log, and no errors in SQL server log.

Solution:
Install NET Framework 3.5 on your SQL server.  Download from here:

https://www.microsoft.com/en-us/download/details.aspx?id=25150

SQL server 2016 demands Net Framework 3.5 for the mail function to work.

Check if NET Framework is installed on your server in folder:

C:\Windows\Microsoft.NET\Framework64

Folder is missing from the SQL server where SQL Mail is not working.

On a SQL server, where mail function is okay, the v3.5 folder exist.

You can also test this by go to the SQL server, start a command prompt, and go to folder

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn

Run DataBaseMail.exe

If you get a error that Net Framework can not be installed, then this is the problem.

Please note that the windows account that run the SQL Server Agent (MSSQLSERVER) must have access rights to the Net Framework 3.5 folder and its files.

You can check if SMTP mail host is working by run below PowerShell command:

Send-MailMessage -To "your.mail@company.com" -From "noreply@company.com"  -Subject "Test mail powershell" -Body "Some important plain text!" -Credential (Get-Credential) -SmtpServer "smtp-servername" -Port 25

If that works, then the SMTP and firewall is correct. Then you need to check SQL servers settings;

SELECT [sysmail_server].[account_id],

       [sysmail_account].[name] AS [Account Name],

       [servertype],

       [servername] AS [SMTP Server Address],

       [Port]

FROM [msdb].[dbo].[sysmail_server]

     INNER JOIN [msdb].[dbo].[sysmail_account] ON [sysmail_server].[account_id] = [sysmail_account].[account_id];

Above gives you the SMTP setup

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'Mail';

SELECT TOP (1000) [profile_id]
,[name]
,[description]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_profile]

Above will show the SQL Mail profiles.

More information:

Troubleshooting Database Mail Failures

https://www.sqlshack.com/configure-database-mail-sql-server/

Check version of SQL server : https://sqlserverbuilds.blogspot.com/

Product:

Microsoft SQL server

Issue:
SQL server is slow, is it low on memory?

Solution:

Get Brent Ozar Health check.  https://www.brentozar.com/blitz/ 

Other solution:

Check if SQL server need to move pages out of memory with this code:

SELECT *

FROM sys.dm_os_performance_counters

WHERE [counter_name] = 'Page life expectancy'

“Page Life Expectancy drops can be triggered by confusing operations. By default, any one running query can get a memory grant the size of 25% of your buffer pool. Run a few of those queries at the same time, and your buffer pool gets drained – but PLE doesn’t necessarily drop. However, the instant an unrelated query runs and needs to get data that isn’t cached in RAM, your PLE will drop catastrophically. Which queries are at fault? ”

Check how memory is used by each database:

SELECT

    (CASE WHEN ([database_id] = 32767)

        THEN N'Resource Database'

        ELSE DB_NAME ([database_id]) END) AS [DatabaseName],

    COUNT (*) * 8 / 1024 AS [MBUsed],

    SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]

FROM sys.dm_os_buffer_descriptors

GROUP BY [database_id];

GO

More Information:

https://www.brentozar.com/archive/2020/06/page-life-expectancy-doesnt-mean-jack-and-you-should-stop-looking-at-it/

https://houseofbrick.com/page-life-expectancy-and-what-its-telling-you/

https://www.spotlightcloud.io/blog/monitoring-page-life-expectancy-in-sql-server

https://training.brentozar.com/p/how-i-use-the-first-responder-kit

SQL SERVER – What is Page Life Expectancy (PLE) Counter

https://www.sqlskills.com/blogs/paul/performance-issues-from-wasted-buffer-pool-memory/

https://github.com/SQLadmin/AwesomeSQLServer/blob/master/T-SQL%20Scripts/Memory%20Monitoring.sql

Product:

Microsoft SQL server

Issue:
How slow is the disk for my databases?

Suggested solution:

This query will show Average Total Latency for each database, value above 10ms is bad.

SELECT DB_NAME(vfs.database_id) AS database_name ,physical_name AS [Physical Name],

size_on_disk_bytes / 1024 / 1024. AS [Size of Disk] ,

CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [Average Read latency] ,

CAST(io_stall_write_ms/(1.0 + num_of_writes) AS NUMERIC(10,1)) AS [Average Write latency] ,

CAST((io_stall_read_ms + io_stall_write_ms)

/(1.0 + num_of_reads + num_of_writes)

AS NUMERIC(10,1)) AS [Average Total Latency],

num_of_bytes_read / NULLIF(num_of_reads, 0) AS [Average Bytes Per Read],

num_of_bytes_written / NULLIF(num_of_writes, 0) AS [Average Bytes Per Write]

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs

JOIN sys.master_files AS mf

ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id

ORDER BY [Average Total Latency] DESC

More information:

https://www.sqlshack.com/sql-server-troubleshooting-disk-i-o-problems/

https://www.mssqltips.com/sqlservertip/6125/disk-latency-for-sql-server-database-and-transaction-log-files/

https://www.brentozar.com/blitz/slow-storage-reads-writes/

Product:

Microsoft SQL server

Issue:

Can i list when all databases where backed up?

Solution:

Enter a Query;

SELECT

    bs.database_name,

bs.backup_size,

    bs.backup_start_date,

    bs.backup_finish_date,

    bs.server_name, 

    bs.user_name,

    bs.type,

    bm.physical_device_name

FROM msdb.dbo.backupset AS bs

INNER JOIN msdb.dbo.backupmediafamily AS bm on bs.media_set_id = bm.media_set_id

More information:

https://www.mssqltips.com/sqlservertip/3171/identify-sql-server-databases-that-are-no-longer-in-use/

https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/

Getting database backup history in SQL Server

How to automate SQL Server database backups

https://ola.hallengren.com/sql-server-backup.html

Product:
Microsoft SQL Server

Issue:

How list users in a database?

Suggestion:

To get all users in one database;

use [database_name]
select name as username,
       create_date,
       modify_date,
       type_desc as type,
       authentication_type_desc as authentication_type
from sys.database_principals
where type not in ('A', 'G', 'R', 'X')
      and sid is not null
      and name != 'guest'
order by username;

To get all users on the SQL server;

declare @db varchar(100)
declare @user varchar(100)
declare c cursor for select name from sys.sysdatabases        

open c

fetch next from c into @db

while @@fetch_status = 0
begin
    print @db   
    exec ('use ' + @db)

    declare u cursor for select name from sys.sysusers
        where issqlrole <> 1 and hasdbaccess <> 0 and isntname <> 1

    open u   

    fetch next from u into @user

    while @@fetch_status = 0
    begin
        print @user
        fetch next from u into @user
    end

    print '--------------------------------------------------'
    close u     
    deallocate u    
    fetch next from c into @db
end

close c
deallocate c

 

To list the DB_OWNERS in database, use this code:

SELECT user_name(member_principal_id)

FROM   sys.database_role_members

WHERE  user_name(role_principal_id) = 'db_owner'

To get more information, you can query like this:

SELECT  members.name MemberName, roles.name,roles.type_desc,members.type_desc

FROM sys.database_role_members rolemem

INNER JOIN sys.database_principals roles

    ON rolemem.role_principal_id = roles.principal_id

INNER JOIN sys.database_principals members

    ON rolemem.member_principal_id = members.principal_id

where 

 roles.name like  'db_owner'

To find out who is sysadmin on the SQL database:

SELECT createdate,accdate, name FROM sys.syslogins WHERE sysadmin = 1

More Information:

https://stackoverflow.com/questions/2445444/how-to-get-a-list-of-users-for-all-instances-databases

https://dev.to/jsgurugit/list-users-in-sql-server-database-1k20

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-role-members-transact-sql?view=sql-server-ver15

https://www.sqlserver-dba.com/2015/05/find-database-users-mapped-to-db_owner-role.html

List of Users with db_owner Role in All Databases

https://www.mssqltips.com/sqlservertip/6828/sql-server-login-user-permissions-fn-my-permissions/

Product:
Microsoft SQL server

Issue:
How to simple move database file to other hard disk?

Suggested solution:

There are many ways to do this task, this is one of them:

Detach the database with a SQL query:

Use MASTER
GO

-- Set database to single user mode
ALTER DATABASE adventure
SET SINGLE_USER
GO 

-- Detach the database
sp_detach_db 'Adventure'
GO

Copy the files with robocopy from a command prompt:

Syntax are : ROBOCOPY source destination [file [file]…] [options]

The /MOV parameter will move the file, if you leave it out – there will be a copy instead.

This will move file adventure.mdf from C to D drive.

robocopy /MOV "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA"  D:\Data   adventure.mdf

This will move file adventure.ldf from C to L drive.

robocopy /MOV "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA"  L:\Log   adventure.ldf

You need to put file path in ” if they contain spaces.

Attach the database with a SQL query:

USE master
GO

-- Attach the database
sp_attach_DB 'Adventure', 
'D:\data\Adventure.mdf',
'L:\log\Adventure.ldf'
GO

More Information:

Robocopy does NOT like trailing \

The Ultimate Guide to Robocopy

https://dba.stackexchange.com/questions/52007/how-do-i-move-sql-server-database-files

https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-user-databases?view=sql-server-2017

https://www.mssqltips.com/sqlservertip/1774/move-sql-server-transaction-log-files-to-a-different-location-via-tsql-and-ssms/

How to Move Log File or MDF File in SQL Server? – Interview Question of the Week #208

Product:

VirtualBox https://www.virtualbox.org/wiki/Downloads

Microsoft Windows 10

Issue:
How stop virtual box vms from command line before computer shutdown?

Suggestion: (this may not work)

Create text files in a c:\scripts folder with the command you need to start and stop vms.
Important: that case of the vms name matched the name you have given them inside Virtualbox.

Create a CMD file with following command:

rem stop one virtualbox image
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" list runningvms

"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" controlvm "win2012big" acpipowerbutton
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" controlvm "Win2016 CAPA" acpipowerbutton

Add the file to group policy on the computer:

1. In a command window, open the Group Policy Editor (GPE) by running gpedit.msc.
2. Expand Computer Configuration and select Windows Settings.
3. Double-click Scripts (Startup/Shutdown).
4. Click Add and add c:/scripts/vboxshutdown.cmd.

Add the start script (c:/scripts/vboxstart.cmd) to the Start properties.

Create a CMD file with following command to start:

rem start one virtualbox image
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" startvm "Win2016 CAPA"
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" startvm "Win2019"

You may need to use the Task Scheduler instead, to make it work.

Or try placing a shortcut of the script as a startup program in %APPDATA%\Microsoft\Windows\Start Menu\Programs\Startup.

Or place a shortcut to your start script in folder C:\ProgramData\Microsoft\Windows\Start Menu\Programs\StartUp – that make the script run when user login.

More Information:

https://askubuntu.com/questions/457329/shutting-down-all-virtualbox-vagrant-vms-in-one-easy-to-use-bash-command-that  

https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-r2-and-2012/dn789196(v=ws.11)

https://simplecodesoftware.com/articles/how-to-set-up-group-policy-scripts-programmatically

Vboxmanage command not found in Windows CMD or PowerShell

https://www.windowscentral.com/how-create-automated-task-using-task-scheduler-windows-10

https://4sysops.com/archives/managing-virtualbox-vms-with-powershell/

http://desertpenguin.org/blog/nnvirtualboxpowershellmodul.html

https://github.com/SmithersTheOracle/VirtualBoxPS

Managing VirtualBox with PowerShell

Product:
Cognos Controller Client 10.4.2
Microsoft Windows 10

Issue:
During manual install of ccrlocalclient64.msi on a users laptop they get a error message.

Installing a program on a workstation demand that you are local administrator on that workstation.
In many cases, this is not allowed at companies, but you can get a temporary different user account that is local administrator.

But even when you use this temporary admin account, during installation, you get above error.

Solution:

First ensure that you account is local admin on the computer.

It should belong to a global Active Directory group that you can find in the list of administrator groups on the laptop.

The command net user adminDonald /DOMAIN will show the groups the account is part of.
Then check that one of these groups is part of the Administrator group on your computer.

Press the Win + R keys on your keyboard to open Run, enter the command compmgmt.msc, and then press Enter or OK.

Go to groups and click on Administrators to see what groups are listed.
If the group is in the list, that account is local admin on the workstation.
If it still does not work, ensure the workstation name is listed as allowed (see below).

If you run the below command for user Donald, you get a result similar to this.

C:\Windows\system32>net user adminDonald /DOMAIN
The request will be processed at a domain controller for domain europe.com.

User name Admindonald
Full Name Donald Duck admin
Comment 
User's comment
Country/region code (null)
Account active Yes
Account expires 2022-08-11 00:00:00

Password last set 2021-08-11 14:12:59
Password expires 2022-08-11 14:12:59
Password changeable 2021-08-12 14:12:59
Password required Yes
User may change password Yes

Workstations allowed myWorkstationName
Logon script
User profile
Home directory
Last logon Never

Logon hours allowed All

Local Group Memberships
Global Group memberships *Computers_local_admins *Domain Users
*other group x

The command completed successfully.

If Workstations allowed is missing, then you get above error.

More Information:

http://woshub.com/restrict-workstation-logon-ad-users/

https://www.digitalcitizen.life/ways-open-computer-management-windows/

Product:
Microsoft Windows Server
Issue:
How configure the agent-config.yaml file to work?

Solution:
The new windows agent for Grafana Cloud should make it easy to collect some metrics from your  computers to the dashboard in the cloud.

Sign up for a account at https://grafana.com/auth/sign-up/create-user?pg=dl&plcmt=box-right

Download the agent and install the agent from here https://grafana.com/blog/2021/04/22/weve-added-first-class-windows-support-to-grafana-agent/

If you click “Get Started with Grafana”, from your grafana.net page, you will see a list of local agent you can install to collect metrics (data) from your system. Click on Windows exporter and Next.

After you have configure the windows agent, as below, you can go back to this page and click “Test integration and finish installation”.

There is a default agent-config.yaml file created, where you need to add your url and username to make the agent ship data to the grafana cloud.
You get your Password (API key) on your host page – go to your name under grafana cloud, and then click on prometheus details to get the key and urls.  Click on grafana details to go to your cloud dashboard.

You need to copy the following values, and place in your yaml file.
Remote Write Endpoint (Use this URL to send Prometheus metrics to Grafana Cloud.)
Username / Instance ID (Your Grafana Cloud Prometheus username.)
Password / API Key (Your Grafana Cloud API Key. Be sure to grant the key a role with metrics push privileges.)

Open C:\Program Files\Grafana Agent\agent-config.yaml in notepad++ on your computer.

You need to add the lines remote_write (very important that every line is indent with two spaces)

remote_write:
  - url: https://prometheus-blocks-prod-us-central1.grafana.net/api/prom/push
    basic_auth:
      username: (the user number from your account)
      password: (the long password from your account)

In YAML file is important to indent the text correct, and the order of the lines, have a meaning. Above setup worked for me, to send data to the grafana cloud. You may find other configurations, also work well.

Then go to your grafana.net page, and select “integration – Windows Exporter” dashboard.

Monitoring Windows Services with Grafana, InfluxDB ‎and Telegraf

You will soon fill up your free account, so you must limit the services that is reported up to the cloud.

Add last to your agent-config.yaml file this lines, to limit the information collected:

 # List of collectors to enable
 enabled_collectors: "cpu,system,os,cs,time"

You can copy your text to a YAML checker: https://codebeautify.org/yaml-validator

Here is some information of possible values in the file:

https://github.com/prometheus-community/windows_exporter/blob/master/README.md

https://grafana.com/grafana/dashboards/6593

More Information:

https://github.com/prometheus-community/windows_exporter

https://github.com/grafana/agent/blob/main/docs/configuration/integrations-config.md#windows_exporter_config

sc stop "Grafana Agent" 
sc start "Grafana Agent"

https://grafana.com/blog/2020/07/02/getting-started-with-the-grafana-agent-a-remote_write-focused-prometheus-agent/ 

Windows Server Monitoring using Prometheus and WMI Exporter

https://devconnected.com/how-to-setup-grafana-and-prometheus-on-linux/

https://prometheus.io/download/

https://githubmemory.com/repo/prometheus-community/windows_exporter/issues/757

https://grafana.com/docs/grafana-cloud/how-do-i/control-prometheus-metrics-usage/usage-reduction/

https://grafana.com/docs/grafana-cloud/how-do-i/control-prometheus-metrics-usage/usage-analysis-explore/ 

https://grafana.com/tutorials/grafana-fundamentals/

Product:
Microsoft Windows 2019 server

Issue:
Problem to connect to Remote Desktop from old laptop.

Solution:

Newer Microsoft Windows server has the possible to harden the communication, to not use old security protocols, then it will not allow connections from Windows 2012 or Windows 7 computers.

Try to connect from a Windows 10 computer with the latest service packs.

More information:

https://support.microsoft.com/en-us/topic/microsoft-security-advisory-update-for-disabling-rc4-479fd6f0-c7b5-0671-975b-c45c3f2c0540

https://support.microsoft.com/en-us/topic/rc4-cipher-is-no-longer-supported-in-internet-explorer-11-or-microsoft-edge-f8687bc1-1f88-9abe-5c81-b00c26290f36#bkmk_more

https://death-to-ie11.com/

https://www.syvik.com/multidesk/documentation.en.html