Category: SQL Server

PKI-04015: Trusted cert cannot be removed

Trying to remove the trusted certificates from the wallet and getting the errors 

$ orapki wallet remove -wallet . -trusted_cert_all
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
PKI-04015: Trusted cert cannot be removed. CN=A,O=B,C=C

SOLUTION

$ orapki wallet display -wallet .
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Requested Certificates:
User Certificates:
Subject:        CN=A,O=B,C=C
Trusted Certificates:
Subject:        CN=A,O=B,C=C

$orapki wallet remove -wallet . -trusted_cert -dn "CN=A,O=B,C=C"
$ orapki wallet remove -wallet . -cert_req -dn "CN=A,O=B,C=C"
$ orapki wallet display -wallet .
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Requested Certificates:
User Certificates:
Trusted Certificates:

How to Upgrade AWS RDS Microsoft SQL Server DB Engine

This blog demonstrates how to do minor version upgrades and major version upgrades against SQL server RDS on AWS.

This post also addresses some errors like ‘InvalidParameterCombination’, etc.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

How to Write Errors into SQL Server Log

The following T-SQL writes errors into SQL log when there are databases have not been backed up in last 12 hours:

IF EXISTS (
    SELECT bs.database_name,  MAX(bs.backup_finish_date) AS LatestDatabaseBackupDate
    FROM  msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
    INNER JOIN master.sys.databases d ON d.name = bs.database_name
    WHERE bs.type = 'D' and d.name <> 'tempdb' and d.state = 0 and d.source_database_id is null
    GROUP BY bs.database_name HAVING DATEDIFF(HH, MAX(bs.backup_finish_date), GETDATE()) > 12
) RAISERROR(N'On TESTSERVER\TESTINST one or more databases have not been backed up in last 12 hours, please check..', 17, 1) with log

Check the log as below:

ALso we can check spid 55 details:

select * from sys.sysprocesses where spid =55;

Version store bloat detected

In MSSQL database log, there are following errors :

Date 1/05/2021 1:02:03 PM
Log SQL Server (Current -  1/05/2021 1:02:03  PM)

Source spid 123

Message
Version store bloat detected
Date  1/05/2021 1:02:03  PM 
Log SQL Server (Current -  1/05/2021 1:02:03  PM )

Source spid 123

Message
Error: 51000, Severity: 17, State: 1.

Check which session caused this issue:

select * from sys.sysprocesses where spid = 123;

Check tempdb space usage:

use tempdb
exec sp_spaceused @oneresultset = 1

Check all version records in the version store in each database:

select db_name(database_id), COUNT(*)
from sys.dm_tran_version_store
GROUP BY database_id

dbname	        totalVersionRecords
TESTDB  	5535970
msdb	        126687
...
..
.

Check space usage of objects in tempdb:

select  
 db_name(database_id) as dbname, 
 FILEGROUP_ID, 
 FILE_ID,    
 version_store_reserved_page_count/128/1024 AS Version_Store_Size_GB 
from 
  tempdb.sys.dm_db_file_space_usage ;

dbname	FILEGROUP_ID	FILE_ID	Version_Store_Size_GB
tempdb	1	        1	5
tempdb	1	        3	2
tempdb	1	        4	5
tempdb	1	        5	2
tempdb	1	        6	2
tempdb	1	        7	2
tempdb	1	        8	2
tempdb	1	        9	2


SELECT * FROM sysfiles order by groupid;

 Check all active transactions that generate or potentially access row versions:

is_snapshotint0 = Is not a snapshot isolation transaction.

1 = Is a snapshot isolation transaction.
select session_id, is_snapshot, transaction_sequence_num, commit_sequence_num, elapsed_time_seconds
from sys.dm_tran_active_snapshot_database_transactions
order by 5 desc;

How To Resume Suspended Database Mirroring In SQL Server

In SQL Server Management Studio, Right click principle database -> properties -> Mirroring, the click Resume button. The alternative way is to right click principle database -> Tasks -> Mirror, the click Resume button.

We can also run the following Transact-SQL query to resume the database mirroring:

ALTER DATABASE userdb SET PARTNER RESUME;

To check the synchronizing progressing by running following Transact-SQL query:

SELECT [counter_name] as CounterName,[cntr_value] as CounterValue
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ('%Database Mirroring%')
AND [counter_name] IN ('Log Send Queue KB','Redo Queue KB')
AND [instance_name]='TESTDB';

CounterName	      CounterValue
Redo Queue KB     1260219                                                                                              
Log Send Queue KB 8407945

“Redo Queue KB” : Logs to applied onto mirroring database.
“Log Send Queue KB” : Logs to be sent to mirroring database.

Another alternative way to check mirroring status and statistics:

Right click principle database -> Tasks -> Launch Database Mirroring Monitor.

Database Mirror Monitor

Click History, you can find when the mirror broke: