Simple update query balloons tempdb I am trying to do a bulk update of a column from a csv file, however when I do the actual update operation my tempdb balloons to over 20 GB in size (overtaking the entire size of the SSD the tempdb is running on).

I load the csv file using a bulk copy in to a temporary table that has matching schema to the destination table, I then do the query

Update [Med_Rec_Entries]
set [Comments] = [Tmp_Med_Rec_Entries].[Comments]
from [Tmp_Med_Rec_Entries]
where [Tmp_Med_Rec_Entries].[ID] = [Med_Rec_Entries].[ID]

And that is where temp db blows up in size. Both the source and destination tables have 1,770,373 rows in it (there is a 1=1 on ID relationship for the update).

I tried running the database tuning advisor and it had me make a new index, but it did not help. The database I am inserting in to is just the middle step in a data migration and no queries other than other csv imports like one I am doing here and a bulk export at the end will be run against this database so it does not have any indexes or keys itself.

Here is the create table statement for Med_Rec_Entries

CREATE TABLE [dbo].[Med_Rec_Entries](
    [ID] [nvarchar](255) NULL,
    [Patient_ID] [nvarchar](80) NULL,
    [Med_Rec_Code] [nvarchar](20) NULL,
    [Tx_Date_Performed] [nvarchar](254) NULL,
    [Comments] [nvarchar](max) NULL,
    [Staff_Code] [nvarchar](21) NULL,
    [Quantity] [real] NULL,
    [Hide_This_Entry] [bit] NULL,
    [Exclude_From_Printed_History] [bit] NULL,
    [Image_ID] [int] NULL,
    [Remote_Status] [nvarchar](2) NULL,
    [Special_Flag] [nvarchar](20) NULL,
    [Tx_Time_Performed] [nvarchar](254) NULL,
    [Tx_Date_Recorded] [nvarchar](254) NULL,
    [Appended_Comments] [nvarchar](max) NULL,
    [Tx_Time_Recorded] [nvarchar](254) NULL,
    [Entry_Is_Locked] [bit] NULL,
    [Create_User] [nvarchar](21) NULL,
    [Audit_DTS] [nvarchar](20) NULL,
    [Audit_Actor] [nvarchar](21) NULL,
    [vQPixAreax_] [varbinary](max) NULL,
    [Route] [nvarchar](80) NULL,
    [Units] [nvarchar](20) NULL,
    [CFR_Reason_Code] [nvarchar](40) NULL,
    [rpl_Key] [nvarchar](14) NULL,
    [rpl_DateTime] [nvarchar](14) NULL,
    [Medical_Description] [nvarchar](max) NULL,
    [caseID] [int] NULL,
    [Sign_Off_Target] [nvarchar](21) NULL,
    [Sign_Off_Date] [nvarchar](254) NULL,
    [Location_Code] [nvarchar](50) NULL,
    [ACUP_ID] [nvarchar](15) NULL,
    [ExcludeFromWeb] [bit] NULL,
    [SecondarySort] [int] NULL,
    [AutosaveState] [nvarchar](20) NULL,
    [_CommentsAndFindingsText] [nvarchar](max) NULL,
    [NewFormat] [bit] NULL,
    [Temp] [nvarchar](80) NULL,
    [Colour] [nvarchar](20) NULL,
    [PrimaryKey] [nvarchar](200) NULL,
    [Sign_Off_Time] [nvarchar](254) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

--From the database engine tuning advisor
CREATE NONCLUSTERED INDEX [_dta_index_Med_Rec_Entries_13_933578364__K1] ON [dbo].[Med_Rec_Entries]
(
    [ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

Here is the create table statement for Tmp_Med_Rec_Entries

CREATE TABLE [dbo].[Tmp_Med_Rec_Entries](
    [ID] [nvarchar](255) NULL,
    [Comments] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

What can I do to stop tempdb from growing too large?

You could run the import in small batches.

TempDB is growing large enough to accommodate running various queries necessary for the import routine.  Running smaller imports would lessen the amount of tempdb required during each run.


Setting up a simple "permissions architecture" in PostgreSQL I host the databases of several clients. For each database dbname, I currently have a dbnameusers role that I grant to the users of that client.

I would now like to have read only "sub roles".

I created a test database, a role usersro and a user ro (in role usersro).

Then I granted all privileges on the DB to usersro and revoked the CREATE privilege :

# grant all on database test to usersro;
GRANT
# revoke create on database test from usersro;
REVOKE

\l shows something that looks correct :

# \l
                            List of databases
    Name    |    Owner    | Encoding |   Collate   |    Ctype    |   Access privileges
------------+-------------+----------+-------------+-------------+-----------------------
 test       | cat         | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 | =Tc/cat              +
            |             |          |             |             | cat=CTc/cat          +
            |             |          |             |             | usersro=Tc/cat

But connecting as user ro, I can still create a table. I guess this comes from the PUBLIC role but I haven't found a way to see those default privileges.

My guess is that I should revoke all privileges from the PUBLIC role and recreate them in the usersrw role and only part of them in usersro (hence the need to know which are the default ones).

Is this the correct approach ? I have the feeling that PostgreSQL is quite permissive out of the box and that you have to break everything in order to build a multilevel access architecture.

To answer your direct question "if I revoke the CREATE privilege from the PUBLIC role, how do I see that ? "...

Use the following SQL (from this stackoverflow question) to get database-specific privileges for PUBLIC role:

   SELECT nspname,
       coalesce(nullif(role.name,''), 'PUBLIC') AS name,
       substring(
          CASE WHEN position('U' in split_part(split_part((','||array_to_string(nspacl,',')), ','||role.name||'=',2 ) ,'/',1)) > 0 THEN ', USAGE' ELSE '' END
          || CASE WHEN position('C' in split_part(split_part((','||array_to_string(nspacl,',')), ','||role.name||'=',2 ) ,'/',1)) > 0 THEN ', CREATE' ELSE '' END
       , 3,10000) AS privileges
FROM pg_namespace pn, (SELECT pg_roles.rolname AS name
   FROM pg_roles UNION ALL SELECT '' AS name) AS role
 WHERE (','||array_to_string(nspacl,',')) LIKE '%,'||role.name||'=%'
 AND nspowner > 1;

Result:

      nspname       |   name   |  privileges
--------------------+----------+---------------
 pg_catalog         | postgres | USAGE, CREATE
 pg_catalog         | PUBLIC   | USAGE
 information_schema | postgres | USAGE, CREATE
 information_schema | PUBLIC   | USAGE
 test               | postgres | USAGE, CREATE
 public             | postgres | USAGE, CREATE
 **public             | PUBLIC   | USAGE, CREATE**

Now, run the following:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Re-run the first SQL, and we get the following result-set:

   nspname       |   name   |  privileges
--------------------+----------+---------------
 pg_catalog         | postgres | USAGE, CREATE
 pg_catalog         | PUBLIC   | USAGE
 information_schema | postgres | USAGE, CREATE
 information_schema | PUBLIC   | USAGE
 test               | postgres | USAGE, CREATE
 public             | postgres | USAGE, CREATE
 **public             | PUBLIC   | USAGE**

Compare the last lines from the 2 result-sets. You will see the missing CREATE privilege.

Don't forget to add the following back:

GRANT  CREATE ON SCHEMA public TO  PUBLIC;

To answer your other direct question : "Does that mean that the public role has all privileges granted (by default) ? "

'PUBLIC' is not an explicit role. This is what tripped me when I started Postgres.

Run the following command to confirm this - PUBLIC is absent from this result set:

select * from pg_roles;

Erwin Brandstetter has given a great explanation of PUBLIC role and another even more detailed explanation of PUBLIC role here .

My own notes - just paraphrasing the Postgres documentation on GRANT...

Any particular role will have the sum of following privileges:

privileges granted directly to it +
privileges granted to any role it is presently a member of +
privileges granted to PUBLIC.

This official Postgres post does a great job of explaining how to setup rights:
Managing Rights in Postgres


Availability Group Listener and old client software We've got a multisegment AG Listener named SQLAGL01 which points to these two addresses:
10.143.162.32 (the primary server)
10.140.165.32 (DR server)
Because the 3rd party (which means we can't fix it, and no we can't migrate to something else) application software uses old drivers which don't support MultiSubnetFailover=True, we're seeing issues where reports fail half the time because SQLAGL01 resolves to the DR address 10.140.165.32.
Following the Microsoft docs, we set RegisterAllProvidersIP=0 and restarted the cluster.  However, SQLAGL01 still resolves to the DR address half the time.
In Failover Cluster Manager, the AG Listener's owner node is the primary server, and the DR address is offline.  (Is that normal?)
Here's a screenshot of the cluster role:

EDIT: Added RegisterAllProvidersIP value.
PS C:\windows\system32> get-clusterresource "Cluster Name" | Get-ClusterParameter RegisterAllProvidersIP

Object       Name                   Value Type
------       ----                   ----- ----
Cluster Name RegisterAllProvidersIP 0     UInt32

EDIT 2: Added AG cluster resource values.  There doesn't seem to be a RegisterAllProvidersIP resource for the AG Listener.

EDIT 3: Added the actual Listener resource value.
PS C:\windows\system32> get-clusterresource KYHIXSQLAGL01_KYHIXSQLAGL01 | Get-ClusterParameter RegisterAllProvidersIP

Object                      Name                   Value Type
------                      ----                   ----- ----
KYHIXSQLAGL01_KYHIXSQLAGL01 RegisterAllProvidersIP 1     UInt32

In your question, you included the following check to validate that you've set the RegisterAllProvidersIP to false:
PS C:\> get-clusterresource "Cluster Name" | Get-ClusterParameter RegisterAllProvidersIP

Object       Name                   Value Type
------       ----                   ----- ----
Cluster Name RegisterAllProvidersIP 0     UInt32

If you've used the Cluster Name then you have set the RegisterAllProvidersIP parameter on the wrong object.
There are several similarly named objects in your screenshot:

The Windows Cluster: WC01
The Availability Group: AG01
The AG Listener: AGListener01

To validate how the setting is configure, you should use the listener name not the cluster or AG name:
PS C:\> Get-ClusterResource AGListener01 | Get-ClusterParameter RegisterAllProvidersIP

Or, to do this a bit more stepwise, you can do Get-ClusterResource and you'll see something like this:
Name                         State   OwnerGroup     ResourceType
----                         -----   ----------     ------------
10.10.10.199                 Online  Cluster Group  IP Address
Cluster IP Address           Online  Cluster Group  IP Address
Cluster Name                 Online  Cluster Group  Network Name
AG01                         Online  AG01           SQL Server Availability Group
AG01_10.10.20.138            Offline AG01           IP Address
AGListener01                 Online  AG01           Network Name
File Share Witness           Online  Cluster Group  File Share Witness
IP Address 10.10.10.138      Online  AG01           IP Address

Out of all of these things, we only care about the objects that belong to the OwnerGroup for the AG. We can filter a bit further with Get-ClusterResource | Where OwnerGroup -eq AG01:
Name                        State   OwnerGroup   ResourceType
----                        -----   ----------   ------------
AG01                        Online  AG01         SQL Server Availability Group
AG01_10.10.20.138           Offline AG01         IP Address
AGListener01                Online  AG01         Network Name
IP Address 10.10.10.138     Online  AG01         IP Address

These 4 items represent the resources that make up my AG: The AG itself, the listener (listed as "Network Name"), and the two IP addresses, each in different subnets.
The item that I want to check for the RegisterAllProvidersIP parameter is the listener  (Network name) resource: Get-ClusterResource AGListener01 | Get-ClusterParameter will give me the full list of parameters on the Listener object:
Object        Name                   Value                            Type
------        ----                   -----                            ----
AGListener01  Name                   AGListener01                     String
AGListener01  DnsName                AGListener01                     String
AGListener01  Aliases                                                 String
AGListener01  RemapPipeNames         1                                UInt32
AGListener01  HostRecordTTL          1200                             UInt32
AGListener01  RegisterAllProvidersIP 1                                UInt32
AGListener01  PublishPTRRecords      0                                UInt32
AGListener01  ResourceData           {1, 0, 0, 0...}                  ByteArray
AGListener01  StatusNetBIOS          0                                UInt32
AGListener01  StatusDNS              0                                UInt32
AGListener01  StatusKerberos         0                                UInt32
AGListener01  CreatingDC             \\dc01.am2.co                    String
AGListener01  LastDNSUpdateTime      9/29/2020 3:57:12 PM             DateTime
AGListener01  ObjectGUID             abc1234def567890ab12cd3456ef7890 String
AGListener01  DnsSuffix              am2.co                           String
AGListener01  ADAware                1                                UInt32

And I can get the specific parameter by explicitly asking for it Get-ClusterResource AGListener01 | Get-ClusterParameter RegisterAllProvidersIP
Object        Name                   Value  Type
------        ----                   -----  ----
AGListener01  RegisterAllProvidersIP 1      UInt32

If this is not set to 0, then you would need to use the block of code outlined in the docs, and be sure you're using the Listener name, and not the Cluster name. The example also recommends setting the TTL as well (picking an exact number is up to you, though the docs suggest 300 seconds). Note that offlining & onlining the Listener is necessary for the new settings to be picked up by the DNS name:
Get-ClusterResource AGListener01 | Set-ClusterParameter RegisterAllProvidersIP 0
Get-ClusterResource AGListener01 | Set-ClusterParameter HostRecordTTL 300
Stop-ClusterResource AGListener01
Start-ClusterResource AGListener01

After this is done (and the old TTL is expired), you'll be able to see the Lister has only a single IP address configured:
Resolve-DnsName AGListener01


Mysql 5.5 to Mysql 5.6 upgrade Insert query is breaking Assume column with the following definition

product_id      INT   NULL     NO_DEFAULT
product_id_nn   INT   NOT NULL NO_DEFAULT

and query

insert into table (product_id,product_id_nn) values ('','')

the above query works in Mysql 5.5. it will insert NULL value for product_id and0for product_id_nn.

the same query breaks in Mysql 5.6 and informs you that wrong type data is getting passed.

Yes, I am 100% accepting Mysql 5.6 statement, however why it was working in 5.5 and as you expect it's huge old php application breaking the functionality with 5.6

Any setting to make it work like 5.5?


Create row level trigger for Partitioned table I have created a partitioned table (base), and an audit table for it(base_audit) and then created child tables (c1,c2,c3,..cn). To populate entries in base_audit, I have created a function and a row level trigger as below.

--Initial tables and partitions
create table base(id bigint, val text) partition by range (id);
create table base_audit(id bigint, val text, act char);
create table c1 partition of base for values from (1) to (3);
create table c2 partition of base for values from (4) to (6);
create table c3 partition of base for values from (7) to (10);

--Trigger function
CREATE OR REPLACE FUNCTION base_function() RETURNS trigger AS $$
BEGIN
if (tg_op = 'UPDATE') then
    if (old.id = new.id) then
        insert into base_audit values (old.id, oid.val, 'u');
        return new;
    else
        insert into base_audit values (old.id, oid.val, 'i');
        insert into base_audit values (new.id, new.val, 'a');
        return new;
    end if;
    elseif (tg_op = 'DELETE') then
        insert into base_audit values (old.id, oid.val, 'd');
        return old;
    end if;
    return new;
END; $$ LANGUAGE plpgsql;

--Trigger
CREATE TRIGGER base_trigger BEFORE INSERT OR DELETE OR UPDATE ON base FOR EACH row EXECUTE PROCEDURE base_function();

When I try to run the above create trigger sql, I am getting the below exception

ERROR:  "base" is a partitioned table
DETAIL:  Partitioned tables cannot have ROW triggers.
SQL state: 42809

I couldn't get what I am missing here. Is it something to do with Aurora Postgresql?

(Using Aurora Postgresql 10.7)

I don't know about Aurora, but on recent PostgreSQL your trigger will work just fine if you define it to fire AFTER rather than BEFORE the DML operation. That should work just as well.

Triggers on partitioned tables were introduced in v11 - is upgrading an option?


Using mariabackup/xtrabackup vs block level snapshots I am running a mariadb 10.1 instance with all innodb tables and a replication slave in a k8s cluster on Google Cloud's GKE. I have a scheduled mysqldump that runs every night. But for additional security, I am working on a mariabackup implementation that will run on the slave pod. Ideally this would create an incremental backup on an hourly basis. The database is not that large, but growing, around 20GB.

But as I work out the logistics of exporting the backup, whether to external object store or just a separate disk, I am wondering if GCP's own snapshot feature would not be an easier and cheaper solution than using mariabackup at all.

My understanding of mariabackup is that it makes an un-modified copy of the underlying database files. The tables are not necessarily consistent, so upon restore, you must --prepare the files to make them consistent. You can then copy them over to the live folder and restart the instance. - https://mariadb.com/kb/en/library/how-mariabackup-works/

This leaves me with base copies and separate folders for incremental copies that I supposed I could send directly to object storage. Then of course write a restore script that can pull in and prepare the base backup and each incremental backup individually up to the point I need to restore.

The other option is simply to schedule an hourly snapshot in GCP. It takes an incremental snapshot and can be restored to a disk at any given point.

Am I missing anything? GCP seems to indicate that their snapshots are close to consistent, but in a write heavy environment, they won't be perfect. Would this be likely to cause problems? Can innodb recover from small inconsistencies? And am I missing out on anything else by using snapshots instead of something like mariabackup?

To answer my own question, GCP (and AWS) snapshots are mostly consistent, but in a very write heavy database, you would want to lock the tables for the duration of the snapshot backup. I believe there is actually an system level option to lock the drive itself for the snapshot, but that is not necessary for my use-case.

I ended up using both methods to backup. I scheduled an incremental snapshot to happen every 2 hours. Then I also do a mysql dump every night. For the mysqldump, it is done with the --single-transaction flag which will give you a consistent copy of the database. There is a momentary lock on all tables while a binary log point is found. But this is usually very short and not noticeable.

Binary logs are turned on and also included in the mysql dump. So this allows both methods to be used to do a point in time recovery. It is definitely possible that the snapshot image could create inconsistent copies, but it seems to not be an issue and innodb is smart enough to recover from most minor inconsistency issues.

The snapshots take very little storage space because they are only storing the delta with each snap. The mysqldump files do not, but if they become a problem, there are other backup options to store only a delta between each mysql dump.


sql server 2012 developers edition license and 3rd party applications Our company is using an ERP system for our product inventory management and customers orders. The newest version uses MS SQL 2012. if we are not developing software and the only users accessing the MS SQL 2012 database is the 3rd party application (ERP system) can we use the Developers Edition of SQL 2012?

No, the Developer Edition is for developers only. If it contains production data, you are not licensed and it doesn't matter that only the ERP system accesses the database, the users of your ERP system count as users of SQL server.


I want to know about the IOPS (I/O Per Second) and How it influences the DB CRUD operation I want to get a dedicated server for my Database (MySQL) which runs InnoDB Engine.
There is this option in InnoDB capacity that you can define the IOPS in it. Let's say
that I have a SATA 72K-RPM HDD that allows 100 IOPS. Does it mean that my DB can run 100 concurrent SQL query per second only or it means 100 single Read or Write in 1 second ?
How a DB can handle 3000 query per second being launched on a Hardware like that ? I'm a bit confused so please explain it for me. I want to get more than 200 query execution on my dedicated server.

A hard drive's IOPS tells you how many I/O operations that particular drive can perform per second.  Since random reads, random writes, sequential reads, and sequential writes have different performance characteristics, if you're looking at a single number, that implies that this is a weighted average of these four numbers that represents some workload.  That workload may or may not be similar to the workload that a database would actually perform so your system may end up getting more or fewer operations per second.

A SQL query would, assuming it does physical I/O (many queries do not need to do any I/O because they are simply reading from data already in the database's memory or in the file system cache or in the SAN cache), tend to perform many I/O operations.  If your queries are actually doing physical I/O, a single query could easily do 100 I/O operations per second.  You could potentially have 100 queries running simultaneously each only getting to do 1 physical I/O operation per second but that would likely mean that all 100 queries would take an unacceptable amount of time to return.

Realistically, if you need to handle 3000 queries per second, you'll need to ensure that the vast majority of your queries do not need to do physical I/O by ensuring that the data they need is cached (preferrably by the database).  And you'll need to spread the I/O over a relatively large number of drives (with an appropriate RAID configuration) to increase the total IOPS that the system can perform.  This sort of capacity planning is going to require a reasonably good understanding of your application, your data, how much I/O each query is likely to really need, etc.  And you'll likely need to balance cost vs. performance-- adding memory or adding drives or changing the RAID configuration will involve different tradeoffs between cost, performance, and available space that will have to be considered.  You may also want to consider splitting the workload among multiple servers (which may require architectural changes to your application).


SQL Server Cross Domain Transcational Replication Has anyone got any documentation for doing/implementing Cross Domain SQL Server 2012/2008R2/2008 transactional replication document or steps (with screenshot or without screenshot)?

Replication across non-trusted domains or workgroups can be done using Push Subscriptions in conjunction with SQL Authentication for the replication agent process accounts. Alternatively, you can also use Windows Authentication by configuring pass-through authentication.

To use pass-through authentication, create a local Windows account on both the Publisher and Subscriber that has the same username and password. Use this account for the replication agent process account and have the connections to the publisher, distributor, and/or subscriber impersonate this account. Ensure the account has the permissions required in Replication Agent Security Model.

This approach is covered in the section Use Windows Authentication to Set Up Replication Between Two Computers Running SQL Server in Non-Trusted Domains in HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted Domains or Across the Internet.

If you have anymore questions, please let me know.  I hope this helps.


Set "lock wait timeout" in MySQL Where would I set the maximum time a query will wait for a lock in MySQL 5.0.68 before timing out?

By default, it is 50 seconds

Just set innodb_lock_wait_timeout as needed.


Better jsonb schema for querying in postgres I have a JSON with the following structure:

uid | item
1   |[{"id":1, "m":"123", "s":1},{"id":2, "msg":"on","s":2},{"id":3, "msg":"of","s":1}]
2   |[{"id":1, "m":"yes", "s":2},{"id":3, "msg":"gh","s":0}]
3   |[{"id":1, "m":"qa", "s":1},{"id":4, "msg":"ks"},{"id":5, "m":"test"}]

I want to query this table based on id and get rows based on matched object values. For e.g. for id=3 I want:

uid | id | m  | s
 1  | 3  | of | 1
 2  | 3  | gh | 0

This table has over 500M rows so I would need an index. Using GIN(item jsonb_path_ops) with item @> '[{"id": 3}]' works, but I don't know how to get the exact json object from array that matched.

I can turn the JSON structure to:

{
    "1": { "m":"123", "s": 1 },
    "2": { "m":"on", "s":2 },
    "3": { "m":"of", "s": 1 }
}

and use GIN(item) and check for key exists with ? and then try to fetch the key value. But the index size might increase which might be an overkill given that I only want to search based on id.

Maybe I can use a B-Tree index but not sure how. What should be my JSON structure for most efficient querying? and what index should be used so that I get only the matched object from the array?

Some facts:

Cardinality of id <<< uid. (id = ~100,000, uid = ~500,000,000)
Normalizing this turns this table to 500M*10,000 rows which is extremely slow.
Each JSON array will have upto 50 objects only.

There are 2 questions here;

First one is the index structure.

You can use gin(item) here (without jsonb_path_ops). It will be just fine. You are querying  for key (id) only not the full path.
You can create an expression index (or an gin index on a generated column) for extracting id field values to an integer[] value. Most probably this index will be smaller than jsonb index (~%10 smaller). But I am not sure whether it's faster for your case.

Here is an example for second usage.

Create an immutable function.

CREATE FUNCTION to_my_int_array (jsonb) RETURNS integer[] AS
$$
  SELECT
      ARRAY(
        SELECT (jsonb_array_elements($1) ->> 'id')::integer
      );

$$
LANGUAGE sql
IMMUTABLE
RETURNS NULL ON NULL INPUT;

Create an expression index with the function above:

CREATE INDEX ON my_table USING gin (to_my_int_array (item));

Filter the query with function;

SELECT
    mt.uid,
    my_item.*
  FROM my_table mt,
  LATERAL jsonb_array_elements(mt.item) ae,
  LATERAL jsonb_to_record(ae) AS my_item (id int, m text, s int)
  WHERE
    to_my_int_array(mt.item) @> '{1}'::integer[];

Second question is the output. The query above also shows output for version 11. After version 12, you can simply use the query below;

SELECT
    uid,
    my_item.*
  FROM my_table mt,
  LATERAL jsonb_to_record(jsonb_path_query_first(mt.item, '$[*] ? (@.id == 1) '))
    AS my_item(id int, m text, s int)
  WHERE
    mt.item @> '[{"id": 1}]';

| uid | id  | m   | s   |
| --- | --- | --- | --- |
| 1   | 1   | 123 | 1   |
| 2   | 1   | yes | 2   |
| 3   | 1   | qa  | 1   |


How to skip the current 'Relay_Master_Log_File' in AWS MYSQL RDS? Below is the status of my slave. The master log file is at position "mysql-bin-changelog.278337" but the slave log file is at position "mysql-bin-changelog.278008". How to skip the current executing "Relay_Master_Log_File" t point to some other log file?

    mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: kdm-eink-prod.cfewehpmjefz.us-east-1.rds.amazonaws.com
                  Master_User: rdsrepladmin
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.278337
          Read_Master_Log_Pos: 120
               Relay_Log_File: relaylog.377536
                Relay_Log_Pos: 1148
        Relay_Master_Log_File: mysql-bin-changelog.278008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: mysql.plugin,mysql.rds_monitor,mysql.rds_sysinfo,mysql.rds_replication_status,mysql.rds_history,innodb_memcache.config_options,innodb_memcache.cache_policies
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 975
              Relay_Log_Space: 354792434
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 98230
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1113717901
                  Master_UUID: ec0eef96-a6e9-11e2-bdf0-0015174ecc8e
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Sending data
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.24 sec)

ERROR:
No query specified

Replication Lag is usually measured by finding the difference between the following

TIMESTAMP#1 at (Relay_Master_Log_File,Exec_Master_Log_Pos)
TIMESTAMP#2 at (Master_Log_File,Read_Master_Log_Pos)

From what I am seeing in the SHOW SLAVE STATUS\G

you are 98230 seconds (27 hours 17 minutes 10 seconds) behind
you have 329 binlogs (278337 - 278008) to process after mysql-bin-changelog.278337
The sum of all your binlogs is 354792434 (338M)

If the Seconds_Behind_Master is steadily increasing by one every second, you will have to wait it out and let replication catch up. This is a strange indication that you are running some terrible query that is making the number of binlogs waiting to be processed increase. You could see that query by running show processlist; and look for the SQL thread (where the user is system user) with the same number for Time as the Seconds_Behind_Master.

If you cannot wait for the catch up of replication, you just need to spin up another read-only slave and drop this very latent slave.


How do I convert a TIMESTAMP to a BIGINT in Azure Synapse What's the best way to convert a TIMESTAMP to a BIGINT In Azure Synapse. I tried this (which works) but seems clumsy. Is there a better, more concise and efficient way of doing the same thing?
SELECT CURRENT_TIMESTAMP AS CURR_TS,
       CONVERT(BIGINT,
           CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112) +
           REPLACE(CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114), ':', '')) AS CURR_TS_NUM

Slightly more compact and only one call to CURRENT_TIMESTAMP, you can use CONVERT with its format switch (121) and TRANSLATE and REPLACE to tidy up.  This reduces the statement from six individual function calls to four.  You can also use FORMAT, which is only two function calls:
SELECT
    CURRENT_TIMESTAMP ts,
    REPLACE( TRANSLATE( CONVERT( CHAR(23), CURRENT_TIMESTAMP, 121 ), '-:.', '   ' ), ' ', '' ) c1,
    FORMAT( CURRENT_TIMESTAMP , 'yyyyMMddHHmmssfff' ) c2

I would query the need to store these values however.  You probably should be storing a conventional timestamp for event records so why also store this form of timestamp in an encoded fashion which you have to unpack if you want to use?  Logging to a table with an identity column has a similar effect.  Something to think about.


USER_ID field in alert logs (also in V$DIAG_ALERT_EXT view) Does anyone know what triggers the USER_ID field in the log.xml to be populated?  The value also exists in the V$DIAG_ALERT_EXT view.

I've found by observing the logs that if a temp tablespace fills up, it will log the USER_ID of the problematic SQL statement causing the issue.  But other than that, it appears that value is always NULL.

Log.xml is the new log format of oracle database will be  substitute the actual alert.log in future (according to oracle roadmap).
Basically you can find in the log.xml all information available in the alert.log with some more details, like the USER_ID. However the database log file does not aim to alert you about specific user issue but only to problem might be considered database-wide.

This means that for almost all problem there is no USER_ID, like for

ORA-00257: archiver error. Connect internal only, until freed.

But for some issues caused and related to specific user action the USER_ID will be present. For example the alert log provide you only this line:

ORA-1555 snapshot too old: rollback segment number %s with name \"%s\" too small"

(in 11g you have also part of sql statement)

The log.xml will show more data. Furthermore, log.xml is close part of ADRCI, the tool for incident diagnose and management, and to do its own work needs more data than the simpe ORA-xxxx.

So, everytime a USER_ID is applicable to the error you will find in the log.xml, otherwise no.


To view PDBs from HR schema I have installed Oracle 12c R2 on my local machine and have created the HR schema using this link : Installing sample schemas

I also gave GRANTS to HR schema to view data dictionary.

GRANT SELECT ANY DICTIONARY TO HR;
GRANT SELECT_CATALOG_ROLE TO HR;

Now, when I connect  as SYSDBA, I am able to view PDBS.

SQL> select con_id, dbid, name
     from v$pdbs;

    CON_ID|      DBID|NAME
----------|----------|------------------------
         2|3752719039|PDB$SEED
         3|3679838824|ORCLPDB

However, when logged in as HR, it does not show anything.

SQL> select con_id, dbid, name
    from v$pdbs;

no rows selected

I surely have connected to the same instance.The output of this query is same for both connections.

SQL> select SYS_CONTEXT('USERENV','INSTANCE_NAME') instance  FROM DUAL;

INSTANCE
-----------------
orcl2

What am I missing here?

I assume that the user is a common user connected to the root container.

A common user's CONTAINER_DATA attribute determines which PDBs are visible in container data objects

SQL> alter user c##hr set container_data=all container=current;

User altered.

SQL> conn c##hr
Enter password:
Connected.
SQL> select name from v$pdbs;

NAME
------------------------------
PDB$SEED
ORCLPDB

SQL>


Do the action blocks of an ALTER TABLE statement run atomically? In Postgres, ALTER TABLE can accept multiple action forms, separated by a comma.
 For example:

ALTER TABLE mytable
  DROP CONSTRAINT pkey,
  ADD CONSTRAINT pkey PRIMARY KEY USING INDEX myindex;

Will the actions be executed atomically somehow?  Or does the statement need to be wrapped in a transaction to ensure all actions take effect in unison?

Every SQL statement is run inside a transaction. At minimum (and by default) each statement runs in its own transaction.

The documentation says:

  By default (without BEGIN), PostgreSQL executes transactions in “autocommit” mode, that is, each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done).

So either both actions succeed, or both are rolled back.


It took me almost 2 days to resolve, but the following solved my issue

Even with the latest drivers installed the snapshot agent will still use the older version drivers that come with SQL Server 2019.

This has to do with the Path variable in your environment. Have a look at System Properties, Advanced System Settings, Environment Variables, System variables, Path.

Unless you have changed your defaults you will find %SystemRoot%\system32, %SystemRoot% at the top and C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\  further down.

What you will not find is a path reference to %SystemRoot%\SysWOW64\ and %SystemRoot%\SysWOW64\1033 which is where the latest OLE drivers get installed on 64 bit systems. The driver files are msoledbsql.dll and msoledbsqlr.rll

So, when the snapshot agent starts it will use the OLE driver files installed relative to its binary somewhere under the C:\Program Files\Microsoft SQL Server\ branch as it cannot find any others in the Path.

When you insert %SystemRoot%\SysWOW64\ and %SystemRoot%\SysWOW64\1033 into your system Path variable (make sure this is above any references to C:\Program Files\Microsoft SQL Server...) and the restart your SQL Server instance you should find that your problem goes away.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e2c23cda-9e78-4514-bae6-27c6d34767a4/sql-server-2019-snapshot-agent-error?forum=sqldatabaseengine


Need a log based replication / CDC solution to publish postgres to oracle subscribers We have a moderately large postgres 9.1 db that is currently 3TB and will likely grow much larger over the next couple of years. we need a reliable and fast solution for moving this data into either oracle 11G or sql server 2012 databases. we need a log based solution like replication or CDC with a minimal foot print of the postgres server and not looking for a  code based ETL solution like SSIS or SAP Data Services.

It should also be able to effectively handle blob / spatial data that is stored in postgres

There is no such feature at this time and the transaction logs lack some of the key information you would need to implement such a feature.

Work is ongoing for PostgreSQL 9.4 to implement logical and bi-directional replication between PostgreSQL instances. Part of this work involves enhancements to the xlogs to add some key information required for logical row replication, adding a new wal_level = 'logical' option. There's also a pluggable infrastructure for decoding the WAL and streaming it to remote hosts, writing SQL to a file, etc.

That's where it becomes useful to you. Your best bet at this point is probably to use that infrastructure to decode PostgreSQL WAL into a change stream you can send to your remote Oracle or SQL Server systems. This will involve a considerable amount of development, as the logical replication code is somewhat immature and still has some limitations even with Postgresql-to-PostgreSQL limitations. DDL is totally unsupported at this time, for one thing.

If you do this you'll need to understand that the BDR work will probably not be committed into PostgreSQL 9.4 in a fully compatible form so you'd have to do re-work to make it compatible with main-line PostgreSQL later.

You'll want to start by adapting the existing text output plugin for the logical walsender to generate more database-agnostic SQL.

If this all sounds way too hard then your only other option is to implement a trigger-based replication solution or adapt an existing trigger based replication system to meet your needs. This will be much (much) higher overhead on the server, but a fair bit simpler.

I should disclose that I work for 2ndQuadrant, who're developing bidirectional replication for PostgreSQL, and I've recently started working on testing and documentation as part of the project.

Update as of 2014-07: Logical changeset extraction / logical decoding was committed to PostgreSQL 9.4, and is due to be released soon. Beta 2 came out a few days ago.

See:

the 9.4 docs on logical decoding
Robert Haas's post on the topic
depesz's post on the topic
Michael Paquier's post on the topic

Logical decoding makes it possible to implement what you want, but does not provide the feature you want directly. To stream changes into MS SQL, Oracle, etc, you would need an output plugin for logical decoding that can stream data to a streaming logical log receiver. The receiver application would maintain a connection to the target server and apply the changes as it receives them from PostgreSQL.

This is the basis of the PostgreSQL BDR/UDR architecture. BDR is not directly useful for your purposes, but the logical changeset decoding feature that was built to make BDR possible will be.

A good starting point would be to look at the test_decoding plugin


Shrink a database below its initial size I've got a SQL server 2005 dev database that is a 30GB copy of live. We have deleted some data that is not needed in dev, which brings the data file space used down to 20GB. So we have about 33% unused.

I need to reclaim the space, which will allow us to have a second dev DB on the server (based on the cut down version); however, I cannot reclaim the space, I have done the following:

The initial size of the file SMS2_Data is 30GB.

DBCC SHRINKFILE (N'SMS2_Data' , 0, TRUNCATEONLY)

followed by

DBCC SHRINKFILE (N'SMS2_Data' , 19500)

No joy. I have tried making a backup, creating a new DB with a low initial size then restoring, no joy as the initial size gets overwritten. Have also tried:

ALTER DATABASE SMS2HazSub MODIFY FILE (NAME = 'SMS2_Data', SIZE = 20000)

This errored, saying:

  MODIFY FILE failed. Specified size is less than current size.

I tried 20800 and then kept going up til 29000 (29GB) and it still will not let me change it.

Have done the shrink then changed the recovery mode from FULL to SIMPLE and back again. No joy.

I thought it was to do with some TEXT fields. We have about 6 across the system. So as a test I dropped them all and then did a shrink of the file and still no change.

The only option left is to reimport the data to another DB. This is not practical, as it would have to be done on the live DB, which carries too much risk. We semi-regularly grab a copy of the live DB and overwrite dev/test. We have something like 500 tables. I would like a way of doing it that would not have the risk of exporting data to a new DB.

I tried moving the data to another file, and it copied all but 5% of the data. This is what lead me to try and drop all the text columns.

The server is on compatibility mode 90, but is SP2. I've now done the following 3 times: reindex all tables, backup database, shrink file, shrink database. Still no joy.

EXECUTE sp_spaceused returns:

database_name   database_size   unallocated space
SMS2Tests       31453.94 MB     13903.16 MB

reserved     data         index_size   unused
16545568 KB  10602264 KB  4254360 KB   1688944 KB

As some people already mentioned you could create new database and "copy" stuff over from old database. This would be the best option for you. However I've noticed that you want to do it quite regularly. So your best option is Redgate Data Compare and Redgate Compare. Both are part of Redgate SqlToolbelt package.

So what you do:

Create an empty DB with small initial size.
Use Redgate Compare to copy db structure, functions etc from old db
Use Redgate Data Compare to copy data from old database to new one
You work on dev database and then at any time you either do just Data Compare and update the Dev DB regularly, or if you do any changes to db you can deploy those changes using Redgate Compare and then doing Redgate Data Compare.

What is good with Data Compare is that after you copy those 30gb of data (you can do it starting with some tables only) after a while it just needs to 'recompare' only some changes and not whole 30gb of data. Which means it will do a lot less impact on both databases then it would by copying it normally.


SQL Server created a new errorlog file but instance and agent were not restarted One of our jobs failed but the job history had already expired by the time we investigated. We could, however, see that a new log file was created around the time it failed.

There was no instance restart (checked os_sys_info) or agent restart (looked in sys.processes) and nobody using it knows how to cycle the log.

Must it have been a corruption of some kind?

I would assume it's more likely that the procedure sp_cycle_errorlog was called. There's a possibility someone accidentally right clicked on Error Log and then clicked Recycle instead of Configure which also calls this procedure.

What I would do is create an audit event on this procedure for the future so you can see how this occurs. If you do have some SQL Auditing in place, I'd search the history to see if that procedure was caught in the audit.

Jeremiah Peschka from Brent Ozar on the log recycling.


IBM DB2 - store large files I want to store large files in a DB2 database and I need more information about which is the best technique to use as I am new in this IBM product (previous experience with T-SQL only).

The situation:

I am using DB2 C-express 10.1 (I do not know how to use the compression feature but sure that this distribution has not got it - this is not problem I can change the distribution if it may help me.
OS - windows 7
Data files types - ms word/pdf/ppt ( I think they will be store as
binary objects)

So, could some one advice for best technique which I can use (talking about both security and performance)?

I suppose I am looking for something like MS File-Stream Enable Database with BLOB objects.

DB2 shouldn't have issues at all with storage of large files. Some of how you store them will depend on what exactly you are storing. Are you storing XML files? Large text files? Word documents or PDFs? Video? Audio?

You have several options available to you. Graphic strings, binary strings, your large objects (CLOB, BLOB, and DBCLOB), and just plain XML.

If you are using just character data I would side with a CLOB. But check your sizes, if you are consistently lower than 32K it might just be better to use a VARCHAR. (Reason being is that large objects in DB2 are stored outside the table by default (unless you use the INLINE option, but even then it only works to certain sizes of LOBs), and they are also not cached in bufferpools. DB2 always retreives LOB's straight from disk (unless they are INLINE'd as mentioned above). If you are storing XML documents, you might want to look into the XML type as DB2 has a more optimal way to store and retrieve XML. You can set up special XML indexes on XML types too. And you can query the XML using SQL/XML or XQuery using XPath.

If you are storing binary objects, then I'd go with BLOB.

I think you may be right about the compression piece. (Although it wouldn't hurt to test it. There are about four or five different compression levels in DB2 and I know I can do at least backup compression with 9.7 Express-C).

Here is a 10.1 Edition comparison chart. And here is a article on the different editions and what they all offer.

Also take a look at space requirements for tables to help you better pick what you need.


postgres auto_explain : setting log_min_duration doesn't start postgres 8.4.20
With these settings, the daemon starts :

postgresql.conf
shared_preload_libraries = 'auto_explain'

With these settings, the daemon doesn't start :

postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 1000

It is a server with a legacy application, so we cannot upgrade PG.
I cannot find documentation of this behaviour / bug...

I put also this setting and it solved :

  custom_variable_classes = 'auto_explain'


Best way to query huge data First let me describe my case:

I need to store around 200extra detail of each user. I am using wordpress. Now there is a default function to save data associated with user. But i am not sure what should i use. In my case i will need some of data in 1 page. For example i need 20 data in one page and 100 data in another. User may need 20 data at once or 100 data at once.

I didn't designed my database table yet. After understand the best way of query i will design it. To get 200 data (fields) of a user if it is ok to make 200 search query then i will user wordpress default meta table. Structure is given below.

| umeta_id | user_id | meta_key | meta_value |

From structure above you can see wordpress save each data of a single user as a single row. SO in this case for one user's 200 data wordpress will save 200 row. And to retrieve the data i have to make 200 queries.

Now if it is better to get all data of user by a single query and save it in session then i will create my own table. Structure is given below.

| User_ID | User_detail_1 | User_detail_2 | User_detail_3 | ...

In the above structure i can save all those 200 data in  one row and can get all in one query and then save to session and user when needed somewhere.

Now in my site i dont need all data in one page. On one page i might need 100 data and in another page i might need 50 data. So considering my case should i save each data as a row(using wordpress meta table) and then make 100 queries for the page where i need 100 data and make 50 queries where i need 50 data?

Or i should use one query and get all the data, save in SESSION then use data from SESSION where 50 or 100 data is needed?

Thanks for the clarification. The first proposal is the infamous EAV, Entity Attribute Value. It is my daily pain as it is the model used by a 3rd party tool. If you want to have a fine list of why it is usually such a problematic model, please have a look at Bill Karvin's "SQL Antipatterns". You will have problems with data types, multiple values, enforcing integrity...

Therefore, I'm all in favor of the second model. If the volume of your data is really important, you can consider partionning, especially vertical partionning.


Limit CPU usage without Resource Governor We have an SQL Server 2008R2 Standard Edition with several databases belonging to different applications on a 16 core server.

One recently introduced application is regularly executing expensive queries that lead to 100% CPU usage. Of course the other applications are reporting performance issues.

The Resource Governor seems like a suitable tool to put the reins on the rogue application, unfortunately it is only available in the Enterprise Edition.

Since the other applications are rather simple, I tried to get the problem under control by reducing the "max degree of parallelism" of the instance, so that a single query can not bring down everything. While that succeeded in keeping the CPU load at 50%, it did surprisingly nothing to keep other applications from being bogged down.

Now we have decided to move the databases for the new application to a dedicated instance, but what would be the best configuration for this instance? Should I keep the MAXDOP-setting, use a CPU-affinity mask or is there another option to limit CPU usage that I am not aware of?

You can use Windows System Resource Manager (WSRM), which is a feature in Windows Server (not sure of minimum version, but definitely 2008 R2+).

This will allow you to control the amount of CPU used by a process, so if you hadn't already separated out the rogue application to its own SQL Server instance, you would have had do that anyway.

At that point you can set whatever MAXDOP you want, as the process will never exceed the maximum limits you set in WSRM.

Setting the CPU affinity mask is an option. You'd want to completely isolate the application onto its own set of cores to eliminate contention (note: watch your NUMA nodes). If you have those extra cores available, then go for it, but I'd prefer the WSRM solution because when that application is idle, all the CPUs can be used by other applications.


Export data into word documents My boss has a kindle and likes to save a lot of stuff to his clippings.  Currently I'm going through and cutting out each clipping and pasting it into a seperate word document with the title of the article.  I have managed to import the txt file into sql server and have generated an SSIS package to clean up the data and to provide me with what I want.  The part that I'm having difficulty with is being able to take each article and generate a word document.  Is there a way to do this with SQL Server and Intergration Services?

edit-
Definition of the data that I have:

create table dbo.Articles
(
PK_ID int identity(1,1),
Title nvarchar(50),
Article nvarchar(max)
)

If you have SQL Server and SSIS, you should also have Reporting Services.

This can generate Word documents. This can be interactive, or you can use URL access, or SOAP calls to get at data.

May not be ideal, but it takes away the need to try and write Word documents via scripting or code: you just need some way to invoke what MS provide


Open port 1433 to use SSMS securely I have a SQL Server placed in DMZ and I want to give access to internal users (and VPN clients) to use SSMS to remotely connect to it via port 1433 so server will accept the connection only form defined IP sources over this port, is it a safe approach for a production server?

First let's answer your initial question then your follow up question.

I would go further and:
change the SQL Server port to a non standard one, turning off the SQL browser service, renaming SA, turning on the firewall on the local machine not to accept any connections on the SQL Server port outside of your internal IPs, and have all of your traffic hit the web server which serves as a secured abstraction layer for outside connections.

Follow up question:
How about HyperV and setup one server for SQL internally and a second one for the web server in the DMZ?  Why does the web server need to be in the DMZ?  Are you able to just forward all 80/8080/443/etc. ports over to that web server?

BTW, since you have both on 1 machine, have you set 'max memory to use' options in SQL Server and 'lock pages in memory' so IIS doesn't steal precious RAM?


Importing a schema in Oracle with a different name I exported an Oracle XE 18c database with:

expdp USER1/pwd DIRECTORY=backups DUMPFILE=file.dmp
      LOGFILE=config_export.log SCHEMAS=USER1

Now, I created in a different machine another user and schema USER2, and need to import all the tables from USER1 in USER2.

In the second machine, I import with:

impdp USER2/pwd DIRECTORY=backups DUMPFILE=file.DMP
      SCHEMAS=USER1 TABLE_EXISTS_ACTION=TRUNCATE LOGFILE=config_import.log

But I get the error that USER1 does not exist. Is there a way to import the schema without creating USER1?

REMAP_SCHEMA

  The Data Pump Import command-line mode REMAP_SCHEMA parameter loads
  all objects from the source schema into a target schema.

  Default: There is no default

  Purpose

  Loads all objects from the source schema into a target schema.

Example:

remap_schema=USER1:USER2


ORA-03297 on Oracle Tablespace alter I have a database with 4500GB storage on AWS RDS. My "users" tablespace has 3749700 MB allocated. Out of which 1583777.63 MB is used and 2165922.38 MB is free.

When I do:

alter tablespace users resize 3500G;

It gives me:

Error report -
SQL Error: ORA-03297: file contains used data beyond requested RESIZE value
03297. 00000 -  "file contains used data beyond requested RESIZE value"
*Cause:    Some portion of the file in the region to be trimmed is
           currently in use by a database object
*Action:   Drop or move segments containing extents in this region prior to
           resizing the file, or choose a resize value such that only free
           space is in the trimmed.

I have tried this - http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.html#Appendix.Oracle.CommonDBATasks.CreatingTablespacesAndDatafiles

How can I fix my tablespace?

You have segments allocated across the tablespace with "gaps" of free space.
In order to reclaim it you should first reorg the tablespace.
See: reclaiming unused space
https://oracle-base.com/articles/misc/reclaiming-unused-space
It's a rather long process for such a large file....
Btw, if you have EM installed it's just a couple of mouse clicks

Regards
Jony


What is the best practice for storing image varbinary(max) data in a database table? I have a table that stores images that range in size between 16-100 KB each.  Since the images are so small, I've taken Microsoft's advice and not used the FILESTREAM data type.  The table is constructed simply:

CREATE TABLE Screenshot(
         Id bigint NOT NULL,
         Data varbinary(max) NOT NULL,
CONSTRAINT PK_Screenshot PRIMARY KEY CLUSTERED
(
Id ASC
)WITH (PAD_INDEX  = OFF,
   STATISTICS_NORECOMPUTE  = OFF,
   IGNORE_DUP_KEY = OFF,
   ALLOW_ROW_LOCKS  = ON,
   ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The table is heavily inserted into (2 million records in the past week) and rarely selected.  The key is using a hilo algorithm, so for the most part new rows are added at the end.

I've been having problems when a lot of processes try to insert into this table because of locking and contention.  Queries are timing out from waiting for locks.

Should I migrate this table to its own file group and drive?  How can I improve the insert performance and decrease contention in this type of situation?

You might try changing the id generation so inserts are not contending with each other, or consider setting ALLOW_PAGE_LOCKS = OFF, noting the implications for index maintenance (which are probably only relevant if you are also doing updates)


I need query to provide distinct number of choices I have eight numbers in my database.
I need to return 28 distinct combinations.
I thought I had the code correct but it returns an infinite number of combinations.

Here is the code.

SELECT t1.ID AS Number_1, t2.ID AS Number_2, t3.ID AS Number_3
     , t4.ID AS Number_4, t5.ID AS Number_5, t6.ID AS Number_6
FROM cTop8 AS t1, cTop8 as t2, cTop8 as t3, cTop8 as t4, cTop8 as t5, cTop8 as t6
where t2.id<>t1.id and t3.id<>t2.id
AND t3.id<>t1.id and t4.id<>t3.id
AND t4.id<>t2.id AND t4.id<>t1.id
and t5.id<>t4.id AND t5.id<>t3.id
AND t5.id<>t2.id AND t5.id<>t1.id
and t6.id<>t5.id AND t6.id<>t4.id
AND t6.id<>t3.id AND t6.id<>t2.id
AND t6.id<>t1.id ORDER BY rand()

If by distinct combinations you mean distinct sets of numbers, regardless of their order (in fact, sets are unordered, according to the algebra of sets), then the only fix your script requires is to replace each <> with either > or <, by which I mean that it should be either all < or all >.

After you switch the comparison symbol, some comparisons will become redundant, and so your query can be simplified. Here is one way to rewrite it:

SELECT
  t1.ID AS Number_1,
  t2.ID AS Number_2,
  t3.ID AS Number_3,
  t4.ID AS Number_4,
  t5.ID AS Number_5,
  t6.ID AS Number_6
FROM
  cTop8 AS t1,
  cTop8 AS t2,
  cTop8 AS t3,
  cTop8 AS t4,
  cTop8 AS t5,
  cTop8 AS t6
WHERE t1.ID < t2.ID
  AND t2.ID < t3.ID
  AND t3.ID < t4.ID
  AND t4.ID < t5.ID
  AND t5.ID < t6.ID
;

And the output would be exactly 28 rows:

Number_1  Number_2  Number_3  Number_4  Number_5  Number_6
--------  --------  --------  --------  --------  --------
1         2         3         4         5         6
1         2         3         4         5         7
1         2         3         4         5         8
1         2         3         4         6         7
1         2         3         4         6         8
1         2         3         4         7         8
1         2         3         5         6         7
1         2         3         5         6         8
1         2         3         5         7         8
1         2         3         6         7         8
1         2         4         5         6         7
1         2         4         5         6         8
1         2         4         5         7         8
1         2         4         6         7         8
1         2         5         6         7         8
1         3         4         5         6         7
1         3         4         5         6         8
1         3         4         5         7         8
1         3         4         6         7         8
1         3         5         6         7         8
1         4         5         6         7         8
2         3         4         5         6         7
2         3         4         5         6         8
2         3         4         5         7         8
2         3         4         6         7         8
2         3         5         6         7         8
2         4         5         6         7         8
3         4         5         6         7         8

Feel free to keep your ORDER BY RAND() if the row order needs to be randomised.


How to automate basic ddl scripts execution and management Looking for suggestions (A Script, third party tool, software etc.) which can help automate some of the administration tasks around execution of DDL scripts across environments. Database is usually Oracle and MySql

Problem statement: Adding a new column via ALTER TABLE to an existing table. Current process is to send the script to pool of DBAs who get hundreds of such requests from various venues daily. Have to wait for them to pick up the request and then respond which usually takes a day or two. They have to manage that script going forward and execute it in Dev, QA(multiple QA environments) & ultimately PROD. In this process the script is usually lost after PROD and cannot be traced back as to who and why they created a change.

Looking for: Suggestions on how this can be automated. Are there any standard tools or software which can manage scripts across environments. Database is Oracle and MySql.

Development Life-cycle requires at least:

A Code Repository (eg Git) to store your CREATE TABLE scripts
Some tool (eg Liquibase) to auto-generate the ALTER TABLE scripts
Unit Testing tools (SQL Developer has one built in)
DevOps framework (eg Azure DevOps) to automate

the build up of QA/TEST environment
deploy scripts to QA/TEST
run Unit Test on Test
deploy scripts to prod

A future version of Oracle SQL Developer might include Liquibase.


What does Reinitialize All Subscriptions actually do to the databases I tried to find the answer and came across these MSDN pages but they don't actually explain what happens really:

http://msdn.microsoft.com/en-us/library/ms152771.aspx
http://msdn.microsoft.com/en-us/library/ms189701.aspx

Step by step what does the Reinitialize All Subscriptions option actually do to the database in SQL Server Management Studio 2008?

Initializing or re-initializing a subscription consists of applying (or re-applying) a snapshot of the publication to that subscription.

This consists of dropping subscribing tables at the subscriber, and recreating them containing the same data as the snapshot. The schema of the relevant tables at the subscriber will therefore match the schema at the publication following a re-initialization.

The Snapshot Agent, which lives on the Distributor, generates the snapshot, although when re-initializing a subscription you can opt to use the existing snapshot. The Distribution Agent then reads the snapshot from the snapshot folder and applies it to its subscription. The Distribution Agent lives on the Distributor for push subscriptions, and on the Subscriber for pull subscriptions.


How can we improve this PIVOT operation? ( SQL Server 2008 R2 ) We have this query, that runs in 1:10. We need to improve this. This is just a test query, but we will implement this upgrade on our real query.

select top 100 *
from (
    select codCliente
        ,Resposta
        ,NrQuestao --,COUNT(1) as Qtde
        ,(
            select COUNT(1) as qtde
            from tblRespostaINT T2
            where T2.codCliente = T0.codCliente
                and T2.codNrQuestao between 9
                    and 29
            ) as QResp
    from tblRespostaINT T0
    inner join tblQuestao T1 on T0.codNrQuestao = T1.codNrQuestao
    where T0.CodQuestionario = 1
        and T1.codNrQuestao between 9
            and 29
                --and codCliente = 5156
                --Group by codCliente, Resposta,NrQuestao
    ) as tblx
Pivot(Sum(resposta) for NrQuestao in (
            [I_1_P14_1]
            ,[I_2_P14_1]
            ,[I_3_P14_1]
            ,[I_4_P14_1]
            ,[I_5_P14_1]
            ,[I_6_P14_1]
            ,[I_7_P14_1]
            ,[I_9_P14_1]
            ,[I_10_P14_1]
            ,[I_11_P14_1]
            ,[I_12_P14_1]
            ,[I_13_P14_1]
            ,[I_14_P14_1]
            ,[I_15_P14_1]
            ,[I_16_P14_1]
            ,[I_17_P14_1]
            ,[I_18_P14_1]
            ,[I_19_P14_1]
            ,[I_20_P14_1]
            ,[I_21_P14_1]
            ,[I_22_P14_1]
            )) tbl

I've added some indexes but none of them helped. This is the execution plan:

if I add loop in the inner join, we have low CPU usage on HASH MATCH, but a high percentage with the index seek:

It takes 30sec to run the insider query:

select codCliente
    ,Resposta
    ,NrQuestao --,COUNT(1) as Qtde
    ,(
        select COUNT(1) as qtde
        from tblRespostaINT T2
        where T2.codCliente = T0.codCliente
            and T2.codNrQuestao between 9
                and 29
        ) as QResp
from tblRespostaINT T0
inner join tblQuestao T1 on T0.codNrQuestao = T1.codNrQuestao
where T0.CodQuestionario = 1
    and T1.codNrQuestao between 9
        and 29
            --and codCliente = 5156
            --Group by codCliente, Resposta,NrQuestao

What can we do? is there some index tip I should know for problems like these?Should we use only joins?

Query Plan:

XML plan: http://pastebin.com/raw/anLifzBC

I would stay away from LOOP joins, or specifying any specific joins. The SQL Server query optimizer is very good at picking the best join method to use, and it will pick LOOP on its own if that is the right one. Also, if there is a missing non-clustered index that could improve the performance of the query, SQL Profiler will find it and suggest it to you.

One thing that I noticed missing from your query is the use of common table expressions. I haven't had a pivot yet where I haven't needed to use common table expressions to get the data ready to pivot. I would rewrite your query using common table expressions (particularly the correlated subquery) and see if that improves performance. If it doesn't another thing you could consider doing is changing the order of columns in the clustered index on the tblRespostaINT to better support that query.

Finally I believe that what you are doing in the inner select can be accomplished with a windowed function, and if it can that should be faster.


How to duplicate related rows Tables: Payments with auto-increment ID and one-to-many relationship to Projects such that one payment (say £10) has two records in Projects allocating the £10 between the projects.

Now next month, this payment is received again and I would like to duplicate this payment and its project's records except for changing the dates.

With one table you can

 INSERT INTO Payments (date, amount)
 SELECT CURRENT_DATE date, amount
 FROM Payments
 WHERE id IN (1,4,5,6,123) /* e.g. user-selected set of paymeents */
 ...;

Obviously to re-create the child rows you need the new insert ID from Payments to use for the PK of the child rows.

Is there a way to do this in (MySQL) SQL?

I'm doing this for a whole set of matches, not just one. I'm looking for a more efficient way than reading it all out into -say- PHP and doing it in a loop that way.

I've been asked for the CREATE TABLEs. They're a simple example, but here you go:

CREATE TABLE `payments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `amount` int(10) unsigned NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `projects` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `payment_id` int(10) unsigned NOT NULL,
  `project` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `payment_id` (`payment_id`),
  CONSTRAINT `y_ibfk_1` FOREIGN KEY (`payment_id`) REFERENCES `payments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Are you looking for last_insert_id

INSERT INTO payments (date, amount) ;
SET @last_id_in_table1 = LAST_INSERT_ID();
 SELECT CURRENT_DATE date, amount
 FROM payments WHERE id = @last_id_in_table1;


Database design where % of rows have unused attributes/columns Is there a theory/practice that promotes a design by analysing the usage of attributes of a table, such that given a threshold of unused columns over a percentage of rows would suggest that the attributes do not actually belong that that table, but instead in a relationship?

example:

users
| id | name  | completed_sign_up |
| 1  | Bob   | null |
| 2  | Sarah | false |
| 3  | Penny | true |

The above false and nil cases are the same, so instead one could say completed_sign_up is not a property of users, but instead should be a join table on it, eg:

sign_ups
| id | user_id |
| 10 | 3

The question of 'when should nulls be used?' is touchy. See this post as an example. I haven't heard of a principle that is based on the percentage of null values. How would the threshold be decided?

To avoid nulls completely, I also thought of the same 'join table' solution you did: for every nullable column, convert it to a one-to-zero-or-one lookup table. I use that pattern occasionally in practice.


Mongostat display size / res I would like to have your opinion and of course an explanation on the following.

My configuration :

mongodb version 3.4.4
1 replicaset with three identical servers( 4 cpu / 32 Go RAM under linux ubuntu v16)
1 primary and two secondaries.
No delay in replication every thing is up ta date.
The engine is WiredTigger.

During insertion or update time I have the following:

Primary : vsize : 3.73 G/ res : 3.02 G
1st secondary  : vsize : 3.28 G / res : 2.63 G
2nd secondary : vsize : 5.66 G / res : 5.02G

I am wondering why is there this difference between the vsize/res of the 2nd secondary and the 1st one?

I was guessing that I should see some kind of equality between them because they both support the same informations from the primary.

Any help or information will bee appreciated.

Has those servers been on that order during their uptime? It looks like that current 2nd secondary has been primary earlier during time what this replica set has been up. Because mongod memory consumption grow (until it reach it's maximum) when it reads data/indexes from disk. (and of course when you insert documents)

At start, mongod process eats only little memory, but during time when it serve clients, it will consume all available free memory, eventually! Because secodary nodes don't (normally) server/receive queries, their memory consumption grows much slower than primary.


Using scope_identity() to get ID "during" insertion of row? I have a column that has an ident_current constraint on it to populate another column, like so:

alter table dbo.Employee
add constraint dbo_Employee_D1 default ident_current('dbo.Employee') for SourceID`

When using ident_current, I have a risk of not always getting the correct identity value in case multiple threads execute my SQL. I wanted to use scope_identity() instead. But how do I use it? I cannot simply replace ident_current with scope_identity().

alter table dbo.Employee
add constraint dbo_Employee_D1 default scope_identity() for SourceID`

An option is to use an AFTER INSERT trigger. But won't this have the same issue in multi-threaded environment?

Do exactly as Aaron said, or if you are afraid of nulls, or don't want to use COALESCE, perhaps you could do something along these lines:

USE tempdb;
GO
-- Create the table
CREATE TABLE dbo.T
(
    IdentityID INT NOT NULL CONSTRAINT PK_T PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , CopyOfIdentityID INT NULL
);
GO
-- create a trigger to copy the values from column a to column b
CREATE TRIGGER T_Trigger ON T
AFTER INSERT
AS
    UPDATE dbo.T SET CopyOfIdentityID = i.IdentityID FROM dbo.T INNER JOIN inserted i ON t.IdentityID = i.IdentityID;
GO
-- insert some test values (this would typically be some other piece of code, perhaps
-- a stored proc or something.
INSERT INTO dbo.T DEFAULT VALUES;
-- you could return the SCOPE_IDENTITY() here for use in the update below.
SELECT SCOPE_IDENTITY();
-- show the row prior to changing the b column to some other value
SELECT *
FROM dbo.T;
-- update the b column to some other value.
DECLARE @SomeOtherID INT;
SET @SomeOtherID = 2;
UPDATE dbo.T SET CopyOfIdentityID = @SomeOtherID WHERE T.IdentityID = 1;
-- show the row with the updated value.
SELECT *
FROM dbo.T;

Results:

Simple, and doesn't rely on anything fancy.


SSMS 2017: Open SQL Files in existing SSMS app with raised admin permissions? I realise this is an old chestnut, but does anyone have a definitive solution?

When SSMS (2017) is 'run as administrator', double clicking a SQL file in explorer will open another new instance of SSMS (and not open the file). This is not good. Double clicking a SQL file should open it in the existing open SSMS app.

Is there a solution to this?

What reasons are there to 'run as admin' by default or can this be turned off without impacting functionality?

Many thanks,

James

In SSMS use Ctrl+O to open the file instead of double-clicking in explorer.


PostgresSQL date/time field out of range on seconds-since-epoch I am importing a csv file into a PostgreSQL database. Most of the file imports without any trouble until I get this message

ERROR:  date/time field value out of range: "1421088300"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY accidents, line 6356158, column datetime: "1421088300"

But this seems to be a valid timestamp for 01/12/2015 @ 6:45pm (UTC) according to https://www.unixtimestamp.com/index.php

So why is it out of range?

Seconds since epoch timestamps

Your "timestamp" is in seconds-since-epoch. Per dezso, use to_timestamp(). I missed that when I checked \dfS+

Bad complex idea, and dragons.

Check the input formats. They cover casts from strings. That's what COPY is doing under the hood. The only method that even remotely looks like a long number is ISO 8601. If you look at that example though you'll see it's not a seconds-since-epoch

Example    | Description
19990108   | ISO 8601; January 8, 1999 in any mode

This is basically the same as another example on that chart.

Example    | Description
1999-01-08 | ISO 8601; January 8, 1999 in any mode

Converting to timestamp with abstime as an intermediary format

So if you want to convert from seconds-since-epoch, you can cheat by using the internal abstime since there is no available cast directly to timestamp from a string of seconds-since-epoch.

SELECT 1421088300::abstime::timestamp;
      timestamp
---------------------
 2015-01-12 12:45:00
(1 row)

What's happening here is that abstime is binary coercable with integer. You can see that in \dC+. I checked \dfS+ for functions to get from integer to timestamp and found none. There is a cast though from integer to abstime (which is stored as an integer), and from abstime to timestamp.

If this is a new table you could actually type that column as abstime. It should load perfectly fine. And then you can ALTER TABLE. here is an example, except I'm not running COPY (but it should work all the same).

CREATE TABLE foo(bar)
AS VALUES
  (1421088300::abstime);

TABLE foo;
          bar
------------------------
 2015-01-12 12:45:00-06
(1 row)

ALTER TABLE foo
  ALTER bar
  TYPE timestamp;

TABLE foo;
         bar
---------------------
 2015-01-12 12:45:00
(1 row)

\d foo;
                Table "public.foo"
 Column |            Type             | Modifiers
--------+-----------------------------+-----------
 bar    | timestamp without time zone |


How to split a list of number into ranges with a fixed interval with SQL? Let's say I have a table like this

I want to calculate the frequency ( How many times that product exists in that price range ), in intervals of "50"

So eventually it will give me a table like

Interval for range will be lets pretend a fixed 50
We don't know highest and lowest price of these each products.

So I will run the query and it will give a table as shown above.

I got a query like this but it didn't work. It gives this error ( I cannot edit server config )

This is the query

select product, count(*) as frequency,
       floor(price / 50) as range_start, floor(price / 50) + 50 as range_end
from t
group by product, floor(price / 50)
order by product, min(price)

This is the error

 [42000][1055] Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'price' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

After that I tried this ( changed floor(price / 50) to floor(min(price) / 50)

select product, count(*) as frequency,
       floor(min(price) / 50) as range_start, floor(min(price) / 50) + 50 as range_end
from t
group by product, floor(price / 50)
order by product, min(price)

This query worked but it gives ranges like

select product, count(*) as frequency,
       50 * floor(price / 50) as range_start,
       50 * (floor(price / 50) + 1) as range_end
group by product, 50 * floor(price / 50), 50 * (floor(price / 50) + 1)
order by product, min(price)


How to Recover an InnoDB table whose files were moved around So I have a test db server that was setup on a replication stream.  Over the name an optimize came through that quickly filled up the space on the slaves datadir.  Mysql dutifully was just waiting for some more space.

This datadir is a file system used ONLY as mysql's datadir so there wasn't anything else to free up.

I had a 4 gig innodb test table that wasn't part of the replication stream so I figured I'd try something to see if it'd work, and being a test environment I wasn't too worried if things went horribly wrong.

Here's the steps I took

Flushed the table I was about to move
Placed a read lock on it (even though nothing was writing to it and it wasn't in the replication stream)
Copied the .frm and .ibd over to a filesystem w/ some spare room
Unlocked the table
Truncated that table - this freed up enough space for the optimize to finish have replication start chugging along again.
Stop slaving/shutdown mysql
Copy the file out of tmp back to the data dir
Restart mysql

Nothing shows up in the .err log, things look good. I connect and use mydb; and see the table I was messing with in show tables.  But, if I try

select * from testtable limit 10;

I get the error

ERROR 1146 (42S02): Table 'mydb.testtable' doesn't exist

From what I can tell so far I can read from all the other tables just fine and replication started back up w/o any complaints.

Is there anything I can do to recover from this point?  I can rebuild it from scratch if need be but was curious what others thought about this venture in general.  Was there anything about the series of steps I took that would have ended up w/ more flawless results?

What if this wasn't a test server I couldn't just 'do it live' and see what happens?  What would have the best way to free up space temporarily on a production slave if I had to like that?

The biggest thing most people forget about TRUNCATE TABLE is that TRUNCATE TABLE is DDL and not DML. In InnoDB, the metadata within ibdata1 contains a numbered list of InnoDB tables. Using TRUNCATE TABLE causes the internal metadata id of the InnoDB table to shift. This happens because TRUNCATE TABLE effectively does the following:

Example: To truncate an InnoDB Table called mydb.mytb

USE mydb
CREATE TABLE newtb LIKE mytb;
ALTER TABLE mytb RENAME oldtb;
ALTER TABLE newtb RENAME mytb;
DROP TABLE oldtb;

The new mytb would thus have a different internal metadata id.

When you copied the .ibd file to some other place, the .ibd contains within it the original internal metadata id. Simply putting the .ibd file back does not cause a reconciliation of the internal metadata id with that of the one in ibdata1.

What you should have done is this:

Copy the .ibd file of the InnoDB table. Then, run this

ALTER TABLE tablename DISCARD TABLESPACE;

To bring it back later on, copy the .ibd file back into datadir and then run

ALTER TABLE tablename IMPORT TABLESPACE;

This would have preserved the internal metadata id.

Make sure .frm is always present.

I once helped a client restore 30 InnoDB tables he hosed in the same manner. I had to use another DB server and play some games with adding and dropping InnoDB tables to hunt down the correct internal metadata id.

The client found this article : http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file . We used it and it helped a great deal. I hope it helps you.


Query Linked Access database on network share via SQL Job Agent I have an MS Access database on a shared network location which I've added as a linked server to my SQL Server instance.  I'm authenticated on the network location via the server's main Windows login.

I can run the stored procedure normally via SSMS on the server, which works perfectly.  However, when I try and run the job through SQL Job Agent, it fails, I believe due to the fact that Job Agent runs under the Local Service account.

How can I allow only this one job to run under the Windows account without having to change this for all the other jobs?  And should I even be doing it this way from a security standpoint?

Note: SQL Server Agent itself does not access the share

It depends on how you have mapped linked server security between local and remote credentials. SQL Server Agent running as local system will connect to SQL Server, which then accesses the share based on linked server credentials.

SUSER_SNAME will be different of course in this case to when you run it.

Options:

add a linked server login mapping for "local system"
configure all local credentials to use one remote login
look at SQL Server Agent proxy accounts for the job step (changes SUSER_SNAME)


What is "cover-to-cover indexing"? I came across this term reading my lecture notes; I can't find it on the internet.

My notes say the following:

  Given a schema R(A,B) and a query of the form "A=a", or "B=b", or
  "(A=a AND B=b)". Two indices over "A" and "A,B" provide a
  cover-to-cover indexing of the schema.

  If the schema contains N attributes, then the binomial (N, N/2) is the
  number of indices necessary to provide a cover-to-cover indexing.

Can someone shed some light on this topic? What is cover-to-cover indexing?

John M's link in the comments is perfect, but if you want a quick summary:

Covering indices allow the engine to pull all of the relevant data directly from the values in the indices themselves; in this way, the data pages never have to be loaded and parsed to fulfil the query and build the result set.

Example with index on (A,B):

SELECT B FROM table1 WHERE A=9;

For MySQL (let's say InnoDB), for example, on the B-TREE index at values of 9 for A, we can easily gather all of the values of B for the result set right from these same index nodes we're already reading, use of the pointers to the table data pages are not necessary since we have everything we need to fulfil the query already.


SELECT with large WHERE IN clause taking long time I'm pretty new at all this so be gentle :).

I have large medical DB where I need to extract data for patients based on selected doctor(s).

Right now I build a patient list by

SELECT patID
FROM appointments
WHERE docID IN ('docid1', 'docid2', ..... , 'docidn')

and once i get my patient list I use it when extracting data from a whole bunch of patient related tables.

Depending on my initial doctors list I can end up with patient lists that are 20,000+. The total # of patients in the system is close to 1,000,000.

I then take my patient list and start getting data from other tables, more or less like this

Select *
FROM xrays
WHERE patID IN ('patid1', 'patid2', 'paid3',....., 'patidn' )

where I build the list of patids based on my the list I have from the first part.

As you can imagine, with a patient list of 20,000, the above query is taking a long time.

Any suggestions on a better way to do this?

This is what Joins are for. You don't have to get a list of patients ids and then send them back to the SQL engine. You can combine the two queries into one, with JOIN:

SELECT x.*
FROM xrays AS x
  JOIN appointments AS a
    ON a.patID = x.patID
WHERE a.docID IN ('docid1', 'docid2', ..... , 'docidn')
ORDER BY x.patID                   --- optional so your application gets
                                   --- the data of patients, ordered.

Other things to check is the indexes you have on the tables. As a rule of thumb, at least all columns used in joins, like your docID, patID, should have an index.


Run Querys on List of Databases (For Each loop?) I have a selection of about 15 databases on an SQL server that I want to run some query's on. I do not want to affect any of the roughly 10 other databases.

Specifically, I want to run the following commands:

USE MASTER;
GO

ALTER DATABASE DATABASENAME
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE DATABASENAME
SET OFFLINE
WITH ROLLBACK IMMEDIATE;
GO

EXEC MASTER.dbo.sp_detach_db @dbname = N'DATABASENAME'
GO

My goal is to take these databases offline, detach them, move the .mdf and .ldf files to a different disk, then re-attach them using the GUI.

In an ideal world, I would also love to be able to then run the following command on them after all have been re-attached:

ALTER DATABASE databasename SET ENABLE_BROKER

In a perfect world, I would be able to designate a list of databases and then loop through all of the commands.

Something PowerShell-y would look like this:

$Databasesnames = @("Database1","Database2","Database3")
ForEach ($Databasename in $Databasenames)
{
USE MASTER;
GO

-- Take database in single user mode
ALTER DATABASE $Databasename
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

--Take databace offline
ALTER DATABASE $Databasename
SET OFFLINE
WITH ROLLBACK IMMEDIATE;
GO

-- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname = N'$Databasename'
GO
}

And then after something like

$Databasesnames = @("Database1","Database2","Database3")
ForEach ($Databasename in $Databasenames)
{
ALTER DATABASE $Databasename SET ENABLE_BROKER
}

I tried this T-SQL ‘while loop approxmiation’ without any luck:

--Set Base Table for Loop Action, Add Database Names
USE TEMPDB
GO
CREATE TABLE #DBS
(Id INT, Name NVARCHAR(100), Status TINYINT)
GO
INSERT INTO #DBS ( Id, Name, Status)
Values (1, 'Database1', 0),
        (2, 'Database2', 0),
        (3, 'Database3', 0)
GO

--Loop through DB names, taking them offline and detaching them.
DECLARE @LoopCounter INT = 1, @MaxDBId INT = 3 ,
        @DBName NVARCHAR(100)

WHILE(@LoopCounter <= @MaxDBId)
BEGIN
   SELECT @DBName = Name
   FROM #DBS WHERE Id = @LoopCounter
USE MASTER
GO
-- Take database in single user mode
ALTER DATABASE @DBName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

--Take DB Offline
ALTER DATABASE @DBName SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO

-- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname
GO

   SET @LoopCounter  = @LoopCounter  + 1
END

UPDATE 1
Here is my revised script

--Declare base Dynamic SQL Variables

DECLARE @sql nvarchar(max);

DECLARE @sql2 nvarchar(max);

--Set Base Table for Loop Action, Add Database Names
USE TEMPDB
GO
CREATE TABLE #DBS
(Id INT, Name NVARCHAR(100), Status TINYINT)
GO
INSERT INTO #DBS ( Id, Name, Status)
Values (1, 'Database1', 0),
        (2, 'Database2', 0),
        (3, 'Database3', 0)
GO

--Loop through DB names, taking them offline and detaching them.
DECLARE @LoopCounter INT = 1, @MaxDBId INT = 3 ,
        @DBName NVARCHAR(100)

WHILE(@LoopCounter <= @MaxDBId)
BEGIN
   SELECT @DBName = Name
   FROM #DBS WHERE Id = @LoopCounter
USE MASTER
GO

 SET @sql = N'ALTER DATABASE ' + QUOTENAME(@DBName)
    + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;';

 SET @sql2 = N'ALTER DATABASE ' + QUOTENAME(@DBName)
    + N' SET OFFLINE WITH ROLLBACK IMMEDIATE;';

EXEC sys.sp_executesql @sql;

EXEC sys.sp_executesql @sql2;

EXEC MASTER.dbo.sp_detach_db @dbname;
GO

SET @LoopCounter  = @LoopCounter  + 1

END

ALTER DATABASE does not take arguments for database name, so you'll have to construct dynamic SQL, e.g. instead of:

ALTER DATABASE @DBName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

You'll need this:

DECLARE @sql nvarchar(max);

  ... inside loop ...

  SET @sql = N'ALTER DATABASE ' + QUOTENAME(@DBName)
    + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    '
    + N'ALTER DATABASE ' + QUOTENAME(@DBName)
    + N' SET OFFLINE WITH ROLLBACK IMMEDIATE;';

  EXEC sys.sp_executesql @sql;

However assuming you have a backup (and if you don't, you should), why bother detaching? Much cleaner to just drop, then you aren't tempted to use an mdf file as a backup, and you don't have to worry about your data folder being permanently full of abandoned data files...


The size of the tablespace doesn't change when I import a smaller dump or deleting old data i wanna free some disc space used by an oracle database ,

I tried deleting  old data and importing smaller dump but the size of tablespace/datafile

remain unchanged.

Tablespace size is not changed by creating, dropping or importing tables (if we forget autoextend option). If your tablespace has no data after droping tables, it is easy to resize datafiles or recreate tablespace. If not empty tablespace, after tables drop, you can try to resize datafiles ALTER DATABASE DATAFILE 'file1' RESIZE 20000m.  Note that resizing may not always be done if your tablespace still contain some tables/indexes. In this case you will need to do some other actions before resizing... (alter table move or drop+import or...)


String index for a list of values using IN($1,$2): will a btree index work? We have a query that needs to compare a value to a list of values in a where clause. The strings are known and discrete. Does IN use the = operator to compare values? Would a b-tree index work for this use case? Example query:
 create table fruit (
   name character varying NOT null
 )

 select * from fruit where name in ('Banana', 'Apple');

Let's assume at the moment we do not want to normalize the data (even though I agree that makes the most sense).

An IN statement can be considered a chained OR, e.g. WHERE name IN ('Banana','Apple') is equivalent to WHERE (name = 'Banana' OR name = 'Apple').
The data is normalized once you define Name as your primary key as that would uniquely identify a fruit for the purposes of this example.  So if that was your primary key, there would be a B-tree present and the engine would (generally) do two seeks for your query: one for 'Banana' and one for 'Apple'.
If there was a secondary index on Name, the engine would (again, generally) do two seeks on that index and two seeks against the table.


Make old data read-only In a database project that I am currently working on there is a requirement that at a certain point in time 'old' data should be changed to read only. Is this possible in SQL Server?

I can't change the table permission as the read only permission should only apply to data that is older than a certain threshold i.e. users should still be able to add and edit data that has been added after this point in time.

Use partitions across multiple filegroups. Once a section of data (eg, for a month once reconciliation has taken place) must be read only, mark the file group as such.


How to filter a character followed by numbers string pattern in PostgreSQL? Back in SQL Server, I use the command below to get the matching string pattern of #1234 from the column called Number
SELECT [m].[Number]
FROM [Boats] AS [m]
WHERE [m].[Number] LIKE N'[#][0-9][0-9][0-9][0-9]'

Now in Postgres, I have tried using the command below, but no luck:
SELECT m.Number
FROM Boats AS m
WHERE m.Number ILIKE '#[0-9][0-9][0-9][0-9]'

Any idea what pattern expression should I use to get the correct filter?

You can use the operator ~ that uses the full power of regular expressions
select *
from (values ('#1234'), ('#3333'), ('#abcd')) as boats (number)
where number ~* '#\d{4}'

A stricter version of the regex would be '^#\d{4}$', so that it matches only strings that start with # then have 4, and only 4 numbers, and then there is the end of the string.


Oracle USE_DB_RECOVERY_FILE_DEST filesystem path As stated in oracle doc

  The keyword USE_DB_RECOVERY_FILE_DEST to indicate the Fast Recovery Area

I know it's setted during dbca but how can identify the Fast Recovery Area filesystem path on a running Oracle12c instance?

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +O71_FRA
db_recovery_file_dest_size           big integer 8G


Joining two tables based on two Aggregate columns I have a following problem as in the example. I have to join two tables based on the aggregated columns. These are the only way to combine these two tables. Is it possible to do this? I have tried Having by - procedures, without any success.
Select a.customerID,
sum(a.Items) as Amount a
sum(b.Items) as Amount b
From Orders a

Join Table B
   on SUM(a.Items) = SUM(b.Items)
Group By a.CustomerID

Something like this:
with
ta as (select a.customerid, sum(a.items) amount_a
       from orders a
       group by a.customerid
      ),
tb as (select sum(b.items) amount_b
       from tableb b
      )
select ta.customerid, ta.amount_a, tb.amount_b
from ta join tb on ta.amount_a = tb.amount_b


Mongodb ReplicaSet Changing Config Format I have a 3 node mongodb replica set which uses the old syntax (non-Yaml) syntax that I am trying to change to use the new Yaml syntax

old syntax

auth = true
bind_ip = 0.0.0.0
dbpath = /var/lib/mongodb
fork = false
keyFile = /etc/mongod.key
logappend = true
logpath = /var/log/mongodb/mongod.log
nojournal = false
port = 27017
replSet = rs0
rest = false
smallfiles = false

Yaml file

net:
    bindIp: 0.0.0.0
    port: 27017
    http:
        enabled: true
        RESTInterfaceEnabled: true
storage:
    dbPath: /var/lib/mongodb
    mmapv1:
        smallFiles: false
    journal:
        enabled: false
processManagement:
    fork: false
security:
    keyFile: /etc/mongod.key
    authorization: enabled
systemLog:
    logAppend: true
    path: /var/log/mongodb/mongod.log
replication:
    replSetName: "rs0"

I have made the change on the primary but it no longer properly starts up. I get the following error message in the log

  SyncSourceFeedback error sending update to :27017:
  CallbackCanceled: Reporter no longer valid

I did a search on the error but I have not been able to find a solution.
The replicaset is running mongo v3.4.9

I have also tried the following:

Setting up each server with the same configuration
Stopping services on all members and starting one at a time.

These attempts did not work

After a few days of looking at this and testing different things, I found that there was a missing configuration value

  systemLog.destination: file

I discovered this by running the following command

  sudo mongod -f /etc/mongod.conf

instead of doing basic service startup commands

  (i.e. sudo service mongod start...)

It showed the following error:

  BadValue: Can only use systemLog.path if systemLog.destination is to a
  file

A couple of other things as you troubleshoot. Make sure you maintain the mongo file and folder permissions. I was deleting log and config files as I was troubleshooting and did not realize it was not allowing the engine to start a few times. I ran the following commands to correct the permissions

sudo chown -R mongodb:mongodb /var/log/mongodb
sudo chown -R mongodb:mongodb /var/lib/mongodb

I also went through the process of removing one of the secondaries from the replicaSet

rs.remove("<server>:27017")

and

rs.reconfig()

Then logging directly to the old secondary that was a stand alone made sure I can access the database as a standalone.

I then readded the system during the changes of the config

rs.add({host: "<servername>"})

Here is the final config file:

net:
    bindIp: [0.0.0.0,<host_name>,127.0.0.1]
    port: 27017
    http:
        enabled: true
        RESTInterfaceEnabled: true
storage:
    engine: wiredTiger
    dbPath: "/var/lib/mongodb"
    journal:
        enabled: false
processManagement:
    fork: false
security:
    keyFile: "/etc/mongod.key"
    authorization: "enabled"
systemLog:
    logAppend: true
    destination: "file"
    path: "/var/log/mongodb/mongod.log"

replication:
    replSetName: "rs0"

I hope this helps someone