SQL Server - history of failed jobs

SELECT j.[name] AS JobName,

msdb.dbo.agent_datetime(run_date, run_time) AS RunDateTime,

j.[enabled],

h.step_id, h.step_name, h.sql_message_id, h.[message], h.run_duration

FROM msdb.dbo.sysjobs j 

INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id 

WHERE j.[name] = 'Job Name'

AND h.run_status = 0 --Failed

AND msdb.dbo.agent_datetime(run_date, run_time) > '2023-02-07 00:00:00'

ORDER BY RunDateTime DESC

SQL Server - database stuck in restoring status

RESTORE DATABASE <DATABASE_NAME> FROM DISK = '<Fuul path>\MyDatabase.bak' WITH REPLACE,RECOVERY

SQL Server - ALTER DATABASE SET OFFLINE takes a long time

Problem:
ALTER DATABASE SET OFFLINE takes a long time

Solution:
Close active sessions of the database:

USE master
--find active sessions:
SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('MYDATABASE')

kill 52 .... and all other sessions

A connection was successfully established with the server, but then an error occurred during the login process

Error Message:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

Solution:

SQL Server: fix Recovery Pending State of a Database

 ALTER DATABASE [DBName] SET EMERGENCY;
GO
ALTER DATABASE [DBName] set single_user
GO
DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO 
ALTER DATABASE [DBName] set multi_user
GO

SSMS: sql server Script failed for StoredProcedure 'PROCEDURENAME' (Microsoft.SqlServer.Smo) ... Invalid version: ...

What I tried to do:
Alter Stored Procedure in SSMS.

Error:
sql server Script failed for StoredProcedure 'PROCEDURENAME' (Microsoft.SqlServer.Smo) ... Invalid version: ...

Solution: 
Update SSMS to a newer release.

SAP RS - Sybase SSL error: Open Server error: Error: 16029, State: 0, Severity 20 -- 'Failed to start any network listeners'

Error:

F. 2020/08/02 08:25:22. FATAL ERROR #1030 GLOBAL RS(GLOBAL RS) - eneric/err/errhand.c(1262)

    Open Server error: Error: 16029, State: 0, Severity 20 -- 'Failed to start any network listeners'.

T. 2020/08/02 08:25:22. (-1): Exiting due to a fatal error.

Solution:

- see if the RS (or other service) run on the port that set to RS in the sql.ini/interfaces file:

sybase16@asazrlnsap16:/sybvol01/sap16rs/REP-16_0/install$ netstat -a | grep 11753

tcp        0      0 asazrlnsap16.inte:11753 0.0.0.0:*               LISTEN

- find the Process ID of the service:

sybase16@asazrlnsap16:~$ netstat -lntp | grep 11753

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

tcp        0      0 10.1.1.4:11753          0.0.0.0:*               LISTEN      61270/repserver

- kill the service:

sybase16@asazrlnsap16:~$ kill 61270

- check that the services were killed:

sybase16@asazrlnsap16:~$ ps -ef | grep 61270

sybase16  66053  65873  0 08:51 pts/2    00:00:00 grep --color=auto 61270

sybase16@asazrlnsap16:~$ netstat -lntp | grep 11753

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

Start RS

SAP ASE - Sybase SSL error: kernel ninit: bind, Address already in use

Error:
00:0002:00000:00009:2020/07/21 13:10:26.85 kernel  ninit: bind, Address already in use
00:0002:00000:00009:2020/07/21 13:10:26.85 kernel  Cannot allocate resources for listener with protocol ssltcp, host asazrlnsap16, port 5000.

Solution:
Delete duplicate definitions for the server with the same port (edit the ini file).

SAP ASE - Sybase SSL error: Configuration parameter 'enable ssl' can not be enabled without license 'ASE_ASM'.

Error:

Configuration parameter 'enable ssl' can not be enabled without license 'ASE_ASM'.

(from the error log file):

Solution:

Create a license of type "SAP Sybase ASE Enterprise Edition, Security & Direct".

1. Go to Launchpad: https://launchpad.support.sap.com/
2. Click tile "License keys"
3. Click your current server id, i.e. L16
4. Right bottom click "Edit"
5. The info should already there, so "Continue"
6. In this page search "security"
From my side I see below, but you might have different license number, for an example:
Item Description
7011762 SAP Sybase ASE Enterprise Edition Security & Directo
 
-> If you can find such "security" license, then click the "Generate" and step by step fill out the system info, to download.
-> If you can NOT find such license, call SAP hotline(560499) for help, ask them to help involve the SAP sales for license purchase.
 
7. Put the downloaded license in folder:
/sybvol01/sap16/SYSAM-2_0/licenses/
8. Restart ASE server.
9. Check ASE error log see if SSL is enabled, if still license error raised please send me back the complete ASE error log.

SQL Server on GCP - You do not have permission to run the RECONFIGURE statement

Error message:

Msg 15247, Level 16, State 1, Procedure sp_configure, Line 105 [Batch Start Line 0]

User does not have permission to perform this action.

Msg 5812, Level 14, State 1, Line 2

You do not have permission to run the RECONFIGURE statement.

The case:

Trying to set configurations (sp_configure and RECONFIGURE)

Solution:

There are no permissions to run sp_configure and RECONFIGURE.

Configuring database flags is done in the GCP portal.

Can not connect SQL Server instance on GCP from SSMS

Problem:
Can not connect SQL Server instance on GCP from SSMS (SQL Server Management Studio).

Solution:
Create Authorized networks:
Go to Connections page, under Connectivity → Public IP:

MySQL - Can not connect to the database

Error message:

ERROR 2003 (HY000): Can't connect to MySQL server on '....' (111).

Solution:

1. Check connection details:
1. Verify that the IP is correct.
2. Verify that the Port number is correct.
3. Check username and password.
2. Network issues:
1. Check that connection to VPN or any other connection types.
2. Check Firewall definition.
3. For cloud instances: check that your IP is allowed.
3. Check that MySQL is up:
1. Check that the MySQL service is up:
1. Windows: in the services.
2. Linux: sudo systemctl start <SERVICENAME>
4. MySQL configurations:
1. Max connections - see here: http://copypastenet.blogspot.com/2021/02/mysql-see-orand-change-how-many.html 
2. cnf file configurations.

MySQL - MariaDB - error in your SQL syntax near ';

Error Message:
Error Code: 1064. You have an error in your SQL syntax;
check the manual that corresponds to your MariaDB server version for the right syntax to use near '; INSERT CITY (..... at line 1

The case:
INSERT CITY (CITY_CODE, CITY_DESCRIPTION) VALUES (N'TT1', N'test1');;
INSERT CITY (CITY_CODE, CITY_DESCRIPTION) VALUES (N'TT2', N'test2');

The cause:
The ';' without a statement.

Solution:
;; --> ;

MySQL - Operation ALTER USER failed for 'root'

Error Message:

Operation ALTER USER failed for 'root'

ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'

or

ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

The case:

root password was damaged and login with root user is failed.

Solution:

1. Connect to MySQL with another user
2. Update root set authentication_string=null
3. FLUSH PRIVILEGES;
4. Logout (quit)
5. Connect to MySQL using "mysql -u root"
6. Set root password
7. Logout (quit)

C:\Program Files\MySQL\MySQL Server 8.0\bin> mysql -uusername -ppassword

 

mysql> UPDATE mysql.user SET authentication_string=null WHERE User='root';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

 

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.31 sec)

 

mysql> quit

 

Bye

 

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root

 

mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'newrootpassword';

Query OK, 0 rows affected (0.26 sec)

mysql> quit

Bye

Can't connect to MySQL 8.0 because a plugin error

The case:
Can't connect to MySQL 8.0 because a plugin error

Error message:

FATAL: unable to connect to MySQL server on host '10.4.117.147', port 3306, aborting...
FATAL: error 2059: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
FATAL: 'sysbench.cmdline.call_command' function failed: /usr/share/sysbench/oltp_common.lua:83: connection creation failed

Cause:
The main cause is that the user has password plugin as "mysql_native_password" but authentication_string contains different hash in mysql.user table.

Solution:

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE User = 'USERNAME';
FLUSH PRIVILEGES;
UPDATE mysql.user
SET authentication_string=concat('*',upper(sha1(unhex(sha1('USERNAME')))))
WHERE user='USERNAME';
FLUSH PRIVILEGES;

MySQL service is in the status “starting” on windows but the service doesn't work

The case:

MySQL service is in the status “starting” on windows but the service doesn't work.

No error message, but MySQL doesn't work

Cause: 

Encoding issue

Solution: 

Save the configuration file "my.ini" using "UTF8

SAP RS - The transaction log in database RSSD is almost full

What happened?
Error when trying to start SAP Replication Server.

Error Message:
E. 2020/12/14 21:39:52. ERROR #11061 GLOBAL RS(GLOBAL RS) - generic\sts\stscol.c(2631)

Check the log for error messages from RSSD.
I. 2020/12/14 21:39:52. Message from server: Message: 7415, State 1, Severity 10 -- 'The transaction log in database RS1_RSSD is almost full. Your transaction is being suspended until space is made available in the log.

Reason:
Cause of the error: The transaction lof of the RSSD database is (almost) full.

Solution:

use master

go

sp_helpdb RS1_RSSD  --a check

sp_helpdevice rssd_log --a check

disk resize name = "rssd_log", size = "1500M"

go

alter database RS1_RSSD log on rssd_log = "1500M"

go

MySQL troubleshooting - Can’t login remotely

The problem:

Can’t login remotely, can login only from the server itself.

The reason: 

Permissions for remote login are missing.

Solution – Linux:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'second_server’;

Check with:

SELECT host, user, authentication_string  FROM user ;

Solution – Windows:

From MySQL Workbench, on the instance connection, Users and Privilages, choose the user (root) and change “Limit to host matching” to “%”.

MySQL Shell - Dba.deploySandboxInstance: ERROR: Error creating sandbox: Could not find mysqld executable. Make sure it is on the %PATH% environment variable.

Error message:

MySQL  JS > dba.deploySandboxInstance(3310)

A new MySQL sandbox instance will be created on this host in

C:\Users\IGittler\MySQL\mysql-sandboxes\3310

Warning: Sandbox instances are only suitable for deploying and running on your local machine for testing purposes and are not accessible from external networks.

Please enter a MySQL root password for the new instance: ******

Deploying new MySQL instance...

Dba.deploySandboxInstance: ERROR: Error creating sandbox: Could not find mysqld executable. Make sure it is on the %PATH% environment variable. (RuntimeError)

Solutions:

1. Check path definitions.
2. MySQL Server should be installed on the machine.

MySQL: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

 Error message:

You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 

Solution:

add before the update (/delete) statement:

SET SQL_SAFE_UPDATES=0;