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.
Read more of this content when you subscribe today.
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;
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_snapshot | int | 0 = 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;
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.
Click History, you can find when the mirror broke:
While running SSMA migrates Oracle database to Microsoft SQL Server, the constraint violation errors occurred for some tables as below:
| Violation of PRIMARY KEY constraint ‘TABLENAME_PK’. Cannot insert duplicate key in object ‘USER.TABLE’. The duplicate key value is (John). |
Read more of this content when you subscribe today.
Make DBA Life Easy 