Month: October 2020

How to Change the Owner of Database in SQL SERVER

Check database owner by the below sql query:

select name, SUSER_SNAME(owner_sid) OWNER from sys.databases;

To change the owner of a database, the following either way should do the job.

  1. On SQL Server Management Studio -> Right Click on Database -> Properties-> Files ->click Owner..

2. Change owner of a database by sql.

ALTER AUTHORIZATION ON DATABASE::TESTDB TO "VIRTUALLAB\Administrator";

If you meet the following error messages.

Msg 15110, Level 16, State 1, Line 48
The proposed new database owner is already a user or aliased in the database.

Then drop the user first, and re-run the above one.

USE "TESTDADB";
GO
drop user "VIRTUALLAB\Administrator";

ALTER AUTHORIZATION ON DATABASE::TESTDB TO "VIRTUALLAB\Administrator";

Advertisement

SQL Server Database in “Recovery Pending“ Status

Due to temporary disk availability issue, SQL Server database is in “Recovery Pending“ Status.

Recovery Pending

Let’s step by step repair the database by the following guidelines:

  • Make sure disks are available and with enough space for databases.
  • Make a backup of datafiles and logfiles. In case repairing failure, so the alternative repairing methods can be used later against database restoration from this backup.
  • Manually set database online.
  • Run CheckDB.
DBCC CHECKDB

You can run CHECKDB with option ‘NO_INFOMSGS’ to eliminate the informational messages. 

DBCC CHECKDB ('TestDatabase') WITH NO_INFOMSGS;

In this case, CheckDB has completed without warning, so the database is healthy. Otherwise continue to repair the database.

• Set to single user mode for databases to be repaired.

ALTER DATABASE TestDatabase SET SINGLE_USER;

• Start to repair the database.

First try “REPAIR_REBUILD” option.

DBCC CHECKDB('TestDatabase',REPAIR_REBUILD)

The repaired database can be se to multiple user mode now, if it is successful.
Otherwise continue to repair with level ‘REPAIR_ALLOW_DATA_LOSS’. 

DBCC CHECKDB(‘TestDatabase’,REPAIR_ALLOW_DATA_LOSS);

Please note the option name explains the potential data loss could happen.

If the repairing is successful, the database can be set back to multiple user status to open to users.

If the repairing is still a failure, then continue to next step for repairing with ‘EMERGENCY’ mode.

ALTER DATABASE TestDatabase SET EMERGENCY;
ALTER DATABASE TestDatabase SET SINGLE_USER;
DBCC CHECKDB ('TestDatabase',REPAIR_ALLOW_DATA_LOSS) WITH  NO_INFOMSGS,ALL_ERRORMSGS;

Finally set database to ‘online’ and multiple user mode.

ALTER DATABASE TestDatabase SET ONLINE;
ALTER DATABASE TestDatabase SET MULTI_USER;

Let’s check database online and normal now.

DataPump: UDE-00008 ORA-31626 ORA-39086

The following errors occur when exporting data by using DataPump:

UDE-00008: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-39086: cannot retrieve job information
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2772
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3886
ORA-06512: at line 1

Check the log which shows exporting has been successful.

...
..
.
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
C:\TEMP\TEST.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:08:38

So it is a bug, and this error can be ignored.

How to Check and Fix Dead Tuples Not Removed By Vacuum From PostgreSQL Tables

Why Vacuum ?

In PostgreSQL, when rows are updated or deleted from a table, dead rows are created and left behind, vacuum daemon processes will clean those dead tuples ( rows) periodically based on parameter setting for vacuum process. Generally the following four reasons explain why vacuum is needed:

  1. To recover or reuse disk space occupied by updated or deleted rows.
  2. To update data statistics used by the PostgreSQL query planner.
  3. To update the visibility map, which speeds up index-only scans.
  4. To protect against loss of very old data due to transaction ID wraparound or multixact ID wraparound.

Tables’ Dead Tuples Not Removed

The following query result shows the dead tuples are not removed by auto vacuuming for some reasons. But they should have been removed from tables.

 schemaname  |          relname          | n_live_tup | n_dead_tup |    overthread    |              now              |        last_autovacuum        | autovacuum_count
------------+---------------------------+------------+------------+------------------+-------------------------------+-------------------------------+------------------
 pg_catalog | pg_statistic              |       3298 |      48500 |  127.69878883623 | 2020-10-10 03:35:43.573615+00 | 2020-10-10 03:34:11.607386+00 |          1797116
 pg_catalog | pg_attribute              |       1116 |       6643 | 41.1076732673267 | 2020-10-10 03:35:43.573615+00 | 2020-10-10 03:34:28.653988+00 |          1589637
 cdrdb      | users                     |      27316 |      87554 | 31.4761288467069 | 2020-10-10 03:35:43.573615+00 | 2020-10-10 03:35:35.891844+00 |          1717941
 cdrdb      | products                  |        645 |       1689 | 14.7510917030568 | 2020-10-10 03:35:43.573615+00 | 2020-10-10 03:35:32.192822+00 |          1596895
 cdrdb      | price                     |     154492 |     168499 |  10.871464333643 | 2020-10-10 03:35:43.573615+00 | 2020-10-10 03:35:05.049206+00 |          1655072
 cdrdb      | contract_pdf              |     157701 |     165106 | 10.4364700602398 | 2020-10-10 03:35:43.573615+00 | 2020-10-10 03:35:27.945506+00 |          1656837
...
..
.
(27 rows)

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

How to Restrict Parallel Server Processes Running on Specific Oracle RAC Nodes

For this SQL, I want all the parallel processes running on this node or those nodes only.

All Oracle DBA knows, by default, the parallel server processes spawned to execute a SQL statement can operate on any or all Oracle RAC nodes in the cluster.

For big indexes rebuilding , a client wants all the parallel server processes running on specific node(s).

There are two easy ways to achieve this without bouncing database required.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe