How can I create a view that spans multiple databases? I have three databases named exayear2011, exayear2012, and exayear2013. They all have the same schema, but the data is different for each year.

I want to create a view that gets data from table1 in all three databases with UNION ALL. How would I do this?

That isn't that hard to do.  A simple view using the three part names of the tables will do it.

CREATE VIEW YourView AS
SELECT Col1, Col2, Col3
FROM exayear2011.dbo.table1
union all
SELECT Col1, Col2, Col3
FROM exayear2012.dbo.table1
union all
SELECT Col1, Col2, Col3
FROM exayear2013.dbo.table1


How I can find out which views use other views in their sql code that generates them? I am mapping the view usage on an uknown database scheme. I was provided with a list with some views:
view_a
view_b
view_c

And I want to search my database which views depend on another views for example if view_c is generated via the following sql query:
CREATE VIEW view_c AS SELECT * from view_a JOIN view_b WHERE view_a.column=344

I want somehow to list that view_c uses view_a and view_b upon excell sheet. For now is done manually via looking the views dll sql on Dbeaver and figuring out from it. But it is very laborious job and may miss some view dependencies as well, so do you have any idea how If I can generate the excell sheet via a query from information_schema.* tables?

You can get which view uses another view with the following query:
select
    pg_views.viewname ,
    pg_views2.viewname as view_used from pg_views, pg_views as pg_views2
where
    pg_views.definition ilike '%'||pg_views2.viewname||'%'
    and pg_views.viewname <> pg_views2.viewname
    and pg_views.schemaname = 'public' and pg_views2.schemaname='public'
;

And In case you want to search a specific list of views:
select
    pg_views.viewname ,
    pg_views2.viewname as view_used from pg_views, pg_views as pg_views2
where
    pg_views.definition ilike '%'||pg_views2.viewname||'%'
    and pg_views.viewname <> pg_views2.viewname
    and pg_views.schemaname = 'public'
    and pg_views2.schemaname='public'
    and pg_views2.viewname in ('view_a','view_b','view_c')
;

In case that you need which materialized view uses another materialized view then you can use this query:
select
    pg_matviews.matviewname ,
    pg_matviews2.matviewname as view_used from pg_matviews, pg_matviews as pg_matviews2
where
    pg_matviews.definition ilike '%'||pg_matviews2.matviewname||'%'
    and pg_matviews.matviewname <> pg_matviews2.matviewname
    and pg_matviews.schemaname = 'public'
    and pg_matviews2.schemaname='public'
;

The whole idea is to use either the pg_views for normal views or pg_matviews join them together and search which view used another view into their sql code.
For example in order to see which view uses another materialized view:
select
    pg_views.viewname ,
    pg_matviews.matviewname as view_used
from pg_views, pg_matviews
where
    pg_views.definition ilike '%'||pg_matviews.matviewname||'%'
    and pg_views.schemaname = 'public'
    and pg_matviews.schemaname='public'
;

It was tested upon postgresql 11.


Postgres 9.3 changes the standard_conforming_strings value I'm migrating a legacy application's database from Postgres 8.1 to Postgres 9.3

In order to avoid compatibility issues, I'd like to keep the standard_conforming_strings parameter to it's default value in 8.1: off.

So, I create the databases using following in the schema:

SET standard_conforming_strings = off;

But a pg_dump of the database reveals that Postgres has changed the value to on.

SET standard_conforming_strings = on;

How can I prevent this?

How exactly did you set standard_conforming_strings?

If you did that in your session, it only applies to that session. For a permanent setting change your postgresql.conf and reload.

Also, a pg_dump  cannot reveal anything. The setting in the dump only applies to the dump. To see the current setting of your database, run

SHOW standard_conforming_strings;

Or:

SELECT * FROM pg_settings WHERE name = 'standard_conforming_strings';

Here you also see where the current value is from and whether it's the default etc.

Generally, It's a very bad idea to try and keep standard_conforming_strings off. This was changed for a good reason. It's going to bite you sooner or later. Update to the current (superior) standard.


MySQL Enterprise Connector/PHP Plugin for php 5.4 I'm trying to setup mysqlenterprise.so. I have installed the native driver and loaded before mysqlent, but when I try to start it I got:

Starting php-fpm: [14-Jul-2015 14:27:40] NOTICE: PHP message: PHP Warning:  PHP Startup: Unable to load dynamic library '/usr/lib64/php/modules/mysqlenterprise.so' - /usr/lib64/php/modules/mysqlenterprise.so: undefined symbol: php_body_write in Unknown on line 0

I have googled and found the error undefined symbol: php_body_write in Unknown on line 0  for other php modules that got solved upgrading the module.

Is it possible to load this mysqlenterprise.so into php 5.4? Documentation says that i need a minimal version of 5.3.

So far I got:
Config files:

ls -la /etc/php.d/
total 96
drwxr-xr-x.  2 root root 4096 Jul 15 20:35 .
drwxr-xr-x. 87 root root 4096 Jul 15 18:21 ..
-rw-r--r--.  1 root root   49 Jun 15 09:46 curl.ini
(omitted output)
-rw-r--r--.  1 root root   55 Jun 15 09:46 mysqlnd.ini
-rw-r--r--.  1 root root   69 Jun 15 09:46 mysqlnd_mysqli.ini
-rw-r--r--.  1 root root   67 Jun 15 09:46 mysqlnd_mysql.ini
(omitted output)
-rw-r--r--.  1 root root   29 Jul 15 20:35 zzmysqlenterprise.ini

Loaded modules

ls /usr/lib64/php/modules/mysqlenterprise.so
/usr/lib64/php/modules/mysqlenterprise.so

Config file contents:

cat /etc/php.d/zzmysqlenterprise.ini
extension=mysqlenterprise.so

With @mim help now I know that php 5.3 vs 5.4 changed on output.c symbol php_body_write in favour to php_output_write (why? why broke like this the libs compat?)

https://github.com/php/php-src/blob/PHP-5.4.42/main/output.c
vs
https://github.com/php/php-src/blob/PHP-5.3.29/main/output.c

nm /usr/lib64/php/modules/mysqlenterprise.so | grep php
                 U _php_stream_free
                 U _php_stream_get_line
                 U _php_stream_read
                 U _php_stream_write
                 U _php_stream_xport_create
                 U php_base64_encode
                 U php_body_write
                 U php_error_docref0
                 U php_info_print_table_end
                 U php_info_print_table_header
                 U php_info_print_table_row
                 U php_info_print_table_start
0000000000006d42 t php_mysqlenterprise_conn_change_user_pub
0000000000006e8c t php_mysqlenterprise_conn_close_pub
0000000000006e4a t php_mysqlenterprise_conn_end_psession_pub
0000000000006e6b t php_mysqlenterprise_conn_free_contents_pub
0000000000006cbd t php_mysqlenterprise_conn_kill_connection_pub
0000000000006c90 t php_mysqlenterprise_conn_ping_pub
0000000000006b76 t php_mysqlenterprise_conn_query_pub
0000000000006cf9 t php_mysqlenterprise_conn_select_db_pub
0000000000002659 t php_mysqlenterprise_init_globals
                 U php_sprintf
                 U php_stream_xport_get_name

I'm having trouble opening Oracle support case, but I'm suspecting is incompatible or there are newer versions from them.

Now a little update:

I tried mysqlenterprise.so for php 5.4, 5.5 and 5.6 and i'm sure it won't work cause it's a compatibility issue.

In PHP Plugin Pack 3.0.22 there's only mysql-enterprise-plugin-1.0.1-php5.3 and unfortunately it works only on php 5.3. It caused by the change of php_body_write to php_output_write on PHPAPI main/output.c after 5.4 release. I checked if there's a new extension but can not find anything.

Only solution seems to open a support ticket.

As shown in documentation you need to load your mysqlnd before mysqlenterprise.so. Mostly this error is about wrong configuration or choosing wrong version for extensions.

Did you compile your php enabled with mysqlnd or installed mysqlnd extension?

First check your php configuration on command line with php -i for controlling your extensions loaded successfully.
You can read more clean you grep result with mysql and check your extension list with grep -Hrv ";" /etc/php.d/ | grep -i "extension="

/etc/php.d directory can be different in different installations.

If you can't see your extensions details, check your php configuration ini files under extension folderand make sure you have mysqlnd.ini. For mysql enterprise ini file, you can create one mysqlzz_enterprise.ini or just add it in mysqlnd.ini file.

If this not solve your problem, try to uninstall php and make sure you installed mysqlnd, then add your extension to your ini file, check your extension ini files as in documentation tells.

Example configure options for php source build:

./configure --with-pdo-mysql=mysqlnd --with-mysql=mysqlnd


SQL 14 AG go into read only when cluster is down, how? Looking at SQL 14 AG and trying to get this to work

https://blogs.technet.microsoft.com/dataplatforminsider/2013/06/27/alwayson-in-sql-server-2014-ctp1/

Increased availability of readable secondaries in multi-site environments

Readable secondary replicas now allow read workloads to run without any disruption even in the presence of lasting network failures or loss of quorum on the underlying Windows Server Failover Cluster.

This is especially desirable in large-scale geo-distributed environments where network disconnections are not that unusual. This is depicted in the picture below, reports running on readable secondaries far from the main data center will continue running despite a network disconnection. Similarly, new reports started during the network disconnection will run.

--
We spent a good bit of time trying to get this to work with a two node clustered instance as primary and then a third node with a stand alone instance as the readable secondary. We can't find too much more info on it other than this link. Since it's from a CPT we're wondering if it's even in the software? Or what we're missing to get it to work. Any thoughts or help would be awesome.

Thanks

You have to set it up for all communication. If the WSFC is down, so is the AG and Listener. If you can directly access the secondary and it's set up, you can access it for readable.


Calculating a percentage discount in SQL Basically just started using sql and having a minor problem.
Working out the percentage of a number.

Example: What would I need to type in to work out:

2.75 - 5%

Drink_price= 2.75
After 5%discount = 2.61

So I need to deduct 5% of the price as a discount.

Sql doesn't seem to like the '%' key.

This is not really database administration. The way you do it cannot be done in most programming languages too. Calculate the discount as 95% of the drink price:

2.75*0.95


Need suggestion for placing foreign keys in the databases I have the database for customers. The customers table is something like this

+------------------+
|     Customers    |
+------------------+
| id  (PK)         |
| business_email   |
| business_name    |
| customer_name    |
| payment_terms    |
| currency         |
| business_address |
| city             |
| state            |
| postal_code      |
| country          |
| phone            |
| created_at       |
| updated_at       |
+------------------+

Now I want to put the shipping address in my application where the customer can optionally mention his shipping address.S o I have made an extra table for shipping address like this

+------------------+
| Shipping Address |
+------------------+
| id  (PK)         |
| contact_name     |
| contact_address  |
| delivery_address |
| created_at       |
| updated_at       |
+------------------+

Now my problem is I am little bit confused in whose key should be foreign key in which table.I am strucked in this.So any help and suggestion will be highly appriciable.

Assuming a company can have more than one shipping address, the key would be a column added on Shipping Address table (aptly named 'customer_id' and the foreign key definition on the Shipping Address table. If you wanna avoid same address more than once, also add a unique key on shipping address table that can include (customer_id, contact_name)

On an unrelated note, make sure your created_at and updated_at use timestamps ;)


Inner Join Performance This is my first question on this forum, so be patient...
My question is about the most efficient way to build a query on the following table:

create table OpenTrades(
    AccountNumber   number,
    SnapshotTime    date,
    Ticket      number,
    OpenTime    date,
    TradeType   varchar2(4),
    TradeSize   number,
    TradeItem   char(6),
    OpenPrice   number,
    TradeSL     number,
    TradeTP     number,
    TradeSwap   number,
    TradeProfit number
);
alter table OpenTrades add constraint OpenTrades_PK Primary Key (AccountNumber, SnapshotTime, Ticket) using index tablespace MyNNIdx;

This table is populated every 15 minutes, and the timestamp of the insert is saved in SnapshotTime column.
The sought query should group records by Week number (based on SnapshotTime), and filter the records so that it would return those with the latest (SnapshotTime) within the same week.

So far, I've tried the following:

select max(ot2.SnapshotTime) from OpenTrades ot1, OpenTrades ot2 where to_date(ot1.SnapshotTime,'IW')=to_date(ot2.SnapshotTime,'IW');.

However, this takes way longer than expected (15 minutes for less than 1500 rows); I'm sure there's a much better way to write this, but so far, it has eluded me. Any idea?
Thanks!

Try something like:

select AccountNumber, SnapshotTime, Ticket, ...
from (
    select AccountNumber, SnapshotTime, Ticket, ...
         , row_number() over (partition by AccountNumber, Ticket
                              order by SnapshotTime desc) as rn
    from OpenTrades
) T
where rn = 1;

I just reread the post and realized that I gave a solution for something different than the OP asked for, I guess something like:

select AccountNumber, SnapshotTime, Ticket, ...
from (
    select AccountNumber, SnapshotTime, Ticket, ...
         , row_number() over (to_date(SnapshotTime,'IW')
                              order by SnapshotTime desc) as rn
    from OpenTrades
) T
where rn = 1;

is closer to what the OP wants.


Why do I have to change Media Sets to create a Full Backup to a new file and what does that mean? I'm trying to create an ad-hoc Full Backup as a snapshot of the database in time and when I added a new file to backup to I get the following error:

I see I can surpass this error by going to Media Options and changing the Overwrite Media to "Back up to a new media set, and erase all existing backup sets". What does this mean?...will I lose my existing backups if I do that?

It's quite a long topic and for the most part it relates to technology that is irrelevant in this day and age (more specifically: tape storage). However here is a quick summary:

media set
An ordered collection of backup media, tapes or disk files, to which one or more backup operations have written using a fixed type and number of backup devices.
media family
Backups created on a single nonmirrored device or a set of mirrored devices in a media set
backup set
The backup content that is added to a media set by a successful backup operation.

In short, if you create a new media set on a device, then yes, you will lose all of your previous backups:

Creating a new media set
To create a new media set, you must format the backup media (one or more tapes or disk files). The formatting process changes the backup media as follows:

Deletes the old header (if any), effectively deleting the previous contents of the backup media.

Formatting a tape device deletes all previous contents of the currently mounted tape. Formatting a disk affects only the file that you specify for the backup operation

Writes a new media header on the backup media (tape or disk file) on each of the backup devices.

For more information, checkout the Media sets, media families & backup sets documentation over at Microsoft Docs.


Group by other table with sum function I have a query that pulls in a delivered count of a campaign. Each run is a new row in the runs table. To retrieve a campaign I need to join the related table in.

SELECT c.Company, r.RunId, r.DeliveredCount FROM Run r
INNER JOIN Campaign c ON r.CampaignId = c.Id
WHERE r.RunId = 3001626 AND c.Company LIKE '%74108%'

Why does it return more than one row, even when I specify the Run Id I would like to pull?

Try this

select *
from Campaign
where CampaignId = 3001626
AND Company LIKE '%74108%'

I suspect you will get more than one row


How do I set and get custom database variables? Using PGAdmin III I can right-click on a database, navigate to the Variables tab, and put a variable name-value property on the database itself.  Is there a way to customize these?  I saw an application_name variable, but I'd like to have an application_version variable.

You have to add the variable to the end of your postgresql.conf like

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

custom_variable_classes = 'general'     # list of custom variable class names

general.application_version = 'v1.0'

When restarted, you can add general.application_version manually in PgAdmin (it will not show up in the drop-down - at least not for me).  Otherwise, you can use it just like any other PostgreSQL variable.

Update for version 9.2 and newer

From PostgreSQL 9.2 onwards, we don't have to set custom_variable_classes anymore.  One can set whatever variable they want - the only limitation seems to be it still has to have two parts:

SET something TO 'bla';
ERROR:  unrecognized configuration parameter "something"

SET thing.something TO 'bla';
SET

I guess this is so to avoid collision with the builtin parameters.


Postgres Partition Table Trigger inserting duplicate records I have a trigger applied on my database table. But when i insert data into it via hibernate jpa it creates duplicate rows in master table. Here is the trigger

CREATE OR REPLACE FUNCTION SMS_RECEIPT_func_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.timedate >=  '2015-01-01' AND NEW.timedate <  '2015-01-31' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m01 VALUES (NEW.*);

    ELSIF ( NEW.timedate >=  '2015-02-01' AND NEW.timedate <  '2015-02-28' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m02 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-03-01' AND NEW.timedate <  '2015-03-31' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m03 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-04-01' AND NEW.timedate <  '2015-04-30' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m04 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-05-01' AND NEW.timedate <  '2015-05-31' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m05 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-06-01' AND NEW.timedate <  '2015-06-30' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m06 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-07-01' AND NEW.timedate <  '2015-07-31' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m07 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-08-01' AND NEW.timedate <  '2015-08-31' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m08 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-09-01' AND NEW.timedate <  '2015-09-30' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m09 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-10-01' AND NEW.timedate <  '2015-10-31' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m010 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER trigger_SMS_RECEIPT_insert
    AFTER INSERT ON "SMS_RECEIPT"
    FOR EACH ROW EXECUTE PROCEDURE SMS_RECEIPT_func_insert_trigger();

If use Before in place of AFTER it gives the following error in java

  CREATE TRIGGER trigger_SMS_RECEIPT_insert
  BEFORE INSERT ON "SMS_RECEIPT"
  FOR EACH ROW EXECUTE PROCEDURE SMS_RECEIPT_func_insert_trigger();

Caused by: org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1

I also tried using INSTEAD OF inplace of AFTER and before but its giving

Tables cannot have INSTEAD OF triggers.
I am new to table partitioning and using triggers. I have searched for all this in google and also gone through the post here: Insert trigger ends up inserting duplicate rows in partitioned table

But it didn't work for me.

Can anyone tell me how to resolve this.

You can (pick one)

Create a view and build on it the trigger  with INSTEAD OF. Then you mae insert on the view, not on the table (you can have a trigger on the table raising an error if you want to be sure).
Do not make direct insert. Call a stored procedure and move logic there

More on trigger and where they can be applied here: http://www.postgresql.org/docs/9.3/static/sql-createtrigger.html


Where to look for clues as to why SQL Server become unresponsive/crashed? Recently an SQLExpress instance suddenly become unresponsive/crashed whilst I was mid-dialog (SSMS transaction log backup), which froze with an hourglass and the dialog was unusable. No other applications or users on the network were able to access instance. I tried to restart using Services, but that didn't respond. Eventually I rebooted the server entirely and it came back up. All the databases in use at the time appear to still be working post-reboot and not corrupt.

Where are the best places to look to find out the source of this crash?

I looked at Event Viewer > Applications, but the only messages around the time of the crash are (in order):

The last dialog box I received before the crash
(approx 1 min pause)
Windows Error Reporting and Application Hang for SSMS (I was using locally on the server)
Subsequent warnings from ASP.NET saying it couldn't connect
Several "Access to server validation failed while revalidating the login on the connection." errors
Several "The client was unable to reuse a session with SPID 68, which had been reset for connection pooling. The failure ID is 23. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message."
Login failed for user 'xxxxxxxx'. Only administrators may connect at this time. [CLIENT: 127.0.0.1]

Messages 5/6/7 appear to be resulting from the server becoming unresponsive, not the cause.

I have looked at the SQL Error Logs using SSMS > Management > SQL Server Logs but that just jumps from 1 to 5 above.

Any other things I can analyse?

Only administrators may connect at this time.

Sounds like the UI has issued ALTER DATABASE ... SET RESTRICTED_USER and then hung. This hasn't actually hung SQL Server, it has only prevented non-admin users from connecting. You could get around this by making your application use sa - I'm kidding.

Honestly, stop using the UI for log backups - it's not very good at things like this, and it's hard to pick all the right options for success. BACKUP LOG ... TO DISK is much more reliable, transparent, error-free, and even source controllable...


SQLITE: Select row ID based on only when the column all the values are same I have a table where more than one rows with same ttid. I would like to get ttid rows based on status column is done.

tb  tbid    ttid    status
-----------------------------
216 6   5   done
216 6   5   running
216 7   5   preparing
217 11  10  done
217 11  10  done
218 12  11  done
218 12  11  running
212 15  14  done
212 15  14  running
220 17  15  done
220 17  15  done

I wanted to have the following rows only:

tb      tbid    ttid    status
--------------------------------
217     11      10      done
220     17      15      done

I have tried the following query but it gives wrong info for tb: 216, because two different tbid.

select tb,tbid,tbid,status FROM tt
status in ('done')

Could you please help how to get the expected results.

Edit

  I should not get the result of ttid 5 because it has status as
  'preparing'. I should get ttids results only when all the statuses are
  'done'.

To get only the ones where done is found twice and no other statuses are found:

SELECT tb,  tbid, ttid,status
FROM  test t1
WHERE status = 'done'
AND NOT EXISTS(SELECT * FROM test t2 where t2.status != 'done' and t1.ttid = t2.ttid)
GROUP BY  tb,tbid,ttid,status
HAVING COUNT(*) > 1;

DB-Fiddle

Or

SELECT ttid,status
FROM test t1
WHERE status = 'done'
AND NOT EXISTS(SELECT * FROM test t2 where t2.status != 'done' and t1.ttid = t2.ttid)
GROUP BY  ttid,status
HAVING COUNT(*) > 1;

DB-Fiddle

The reasoning for adding it to the group by or applying a function:

if tb or tbid can be different for the same 'done' status and the same ttid, which one do you choose? Do you choose the smallest, the biggest, ...?

An example of what happens if this is true and it is not added to a group by or function:

DB-Fiddle that fails


Replication fails exactly after a week We are application developers trying to decode why replication is failing.

It fails every week on the same day pretty much same time.

This has been happening for about a month now and our vendors are not helping with any answers.

Where should we start with diagnostics?

you may want to check subscription expiration option or check replication monitor for the error in more details, this can help, resolving your problem.


Ola Hallengren Maintenance script - Full database backup delays The DatabaseBackup job created by Maintenance Script will introduce delay for the last database being backed up.

What I mean is, there are 6 databases around 200GB each.

The FULL backup takes around 90minutes to complete.
The backup jobs is scheduled to run at 2AM.
So, the last 2 databases to be backed up will potentially run on business hours (after 8AM)

  1st DB will complete on 3:30 AM 2nd DB will complete on 5:00
  AM 3rd DB will complete on 6:30 AM 4th DB will complete on
  8:00 AM 5th DB will complete on 9:30 AM 6th DB will complete
  on 11:00 AM

All the data files are on E: drive
Does it take same amount of time even when the backups are made to run at the same time at 2AM(by different jobs) due to IO overhead?

Is this the normal way the FULL backups are made (with delays)?

It will depend on the resources available on the server.

  Does it take same amount of time even when the backups are made to run
  at the same time at 2AM(by different jobs) due to IO overhead?

You can set up parallel jobs and test.  You will need to watch, how many of full backups can run in parallel before other processes (if any) are effected (cpu, io, network bandwidth etc).

Recently there was another question where Brent Ozar explained the phases of backup and how you can test.

I will not repeat his words but you can check details here.

There are built in switches in SQL Server that you can use to speed up backup.
See this blog post.

I did not understand this question?  Did you mean if full backup run in serialized way?  Depends how you job is set up. You can make them parallel as @Ghauan explained in comment, you can make multiple jobs to run in parallel.

  Is this the normal way the FULL backups are made (with delays)?


Alternative to Triggers I am working on an oracle database and want to record the exact time of any transaction happening on a specific table in the schema. Using triggers on that table is out of question as the client do not want to use it for unknown reasons, also it is not possible to alter the existing tables

Can anyone tell me if there is any other way to achieve this. I just want to record the time of the transaction and the ROWID of the inserted or updated row in a separate table.

Background Info

Transactions are recorded in the log/archive log files.

From there, you can find such things as

what values were changed
time of change within +/- 3 seconds
who changed it under what context (values in SYS_CONTEXT) (*)

TIME

The "time of transaction" comes from the SCN.  As such, the time resolution is "+/- 3 seconds".

If you need a higher resolution, then the Business needs to accept some schema modifications.

example:

alter table T add (
    time_of_insert timestamp default systimestamp -- possibly INVISIBLE
);

VERSIONS BETWEEN Queries

A VERSIONS BETWEEN query adds the following pseudo columns

VERSIONS_STARTSCN
VERSIONS_STARTTIME
VERSIONS_ENDSCN
VERSIONS_ENDTIME
VERSIONS_XID
VERSIONS_OPERATION - (I)nsert, (U)pdate, (D)elete

"how far back you can go" is dependent on the UNDO tablespace.  Which is, usually, 1 hour. YMMV.

To extended the range, use Flashback Data Archive.

LogMiner

Prior to 11g, the only way to access "when a transaction occurs" is to use LogMiner.

This can be done through the DBMS_LOGMNR Package.

Flashback Data Archive

11g introduced a feature called Flashback Data Archive.

This feature

Extends the range of AS OF and VERSIONS BETWEEN queries.
runs in the background.
Reads the log/archive log files (I believe it uses LogMiner also)
records the values in a separate table
"Old values" are automatically drop

"Old Values" is determined by your Flashback Archive destination.

This feature is included with all editions of Oracle starting with 11.2.0.4. Prior to 11.2.0.4, you have to have a specific EE add-on ($$$$).

(*) With 12c, Flashback Data Archive can optionally record the values of SYS_CONTEXT also.

Other Links

Here are some additional URLs regarding Flashback Data Archive

http://www.oracle.com/technetwork/issue-archive/2016/16-mar/o26performance-2925662.html
https://youtu.be/2RXUUrxcKrw?list=PLlo3zrPbMP9_HDjsq9lcdi8RyiIvqfmBy


when selecting from multiple tables I get a multiplication of the records When executing this query below it takes too long. And the result is too much records,
more than possibly in the database.
The query should return around 117.000 records. That is the amount of records enterred into the database during a period of half a year.

Why is this query multiplying, resulting in millions of records?
If someone could give me the clu where I'm stuck, I'll be very happy.

/* Formatted on 2011/08/05 17:09 (Formatter Plus v4.8.7) */
SELECT   t2.flag, t3.reg_number, t1.reportdate, t1.latitude, t1.longitude,
     t1.value2 course, ((t1.measspeed) * 3.6) / 1.852, t1.status alarms
FROM traffic t1, details t2, registernumbers t3
WHERE     t1.mobileno = t2.mobile
     AND t2.mobile = t3.mobileno
     AND UPPER (t2.flag) = UPPER ('BEL')
     AND t1.reportdate BETWEEN '1/jan/2011' AND '1/jul/2011'
     AND (latitude > 52 AND longitude > 2)
     AND latitude > 48
  OR (latitude < 52 AND longitude > 2)
  OR (latitude > 52 AND longitude < 2)
ORDER BY reportdate DESC

Thanks in advance,

Maarten

The part of the filter that performs the join is t1.mobileno = t2.mobile AND t2.mobile = t3.mobileno. For that to be sensible, it is likely that you need two of the following three statements to be true:

mobileno is unique in traffic
mobile is unique in details
mobileno is unique in registernumbers

Because the join is returning more rows than you expect, I guess this is not the case. For further help we'll need to know the DDL for the tables (in particular the primary keys)

---EDIT

on second thoughts, I think

WHERE     t1.mobileno = t2.mobile
     AND t2.mobile = t3.mobileno
     AND UPPER (t2.flag) = UPPER ('BEL')
     AND t1.reportdate BETWEEN '1/jan/2011' AND '1/jul/2011'
     AND (latitude > 52 AND longitude > 2)
     AND latitude > 48
  OR (latitude < 52 AND longitude > 2)
  OR (latitude > 52 AND longitude < 2)

needs to have some more brackets:

WHERE     t1.mobileno = t2.mobile
     AND t2.mobile = t3.mobileno
     AND UPPER (t2.flag) = UPPER ('BEL')
     AND t1.reportdate BETWEEN '1/jan/2011' AND '1/jul/2011'
     AND (latitude > 52 AND longitude > 2)
     AND ( latitude > 48
           OR (latitude < 52 AND longitude > 2)
           OR (latitude > 52 AND longitude < 2) )

although some of the conditions seems to be redundant


Can select read index page while modify this page in innodb? When mysql innodb insert or update secondary index page , will at the same time this index page can searched by select ?

Yes, that's possible, that's what the change buffer is for. It caches changes to secondary indexes. When data is read via an index and (parts of) the index is in the change buffer, it's read from there. The changes are written to disk periodically.

  The change buffer is a special data structure that caches changes to secondary index pages when affected pages are not in the buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.
  ...
  Periodically, the purge operation that runs when the system is mostly idle, or during a slow shutdown, writes the updated index pages to disk.

Read about it in more detail here.

Also creating/dropping/changing an index is possible while selects/insert/updates/deletes can be performed at the same time with online DDL (at least with MySQL >= 5.6).

Read more about it here.


Does a DBA need to know how to program in a system language besides SQL? To what extent does a Database Administrator need to know system or application level programming languages (for example .NET or PHP) besides "just SQL"?

For the purposes of this question, no specific version of the SQL standard is considered for this answer (SQL ANSI 86, SQL ISO 87, SQL:2008) as the question is in regards to desktop or server languages outside the realm of SQL.

It depends.

In a big shop, maybe not because you have 1000s of servers to look after and your tools are provided. In a small shop, you'll probably need more knowledge because you have a wider remit.

Scripting languages (PowerShell, cmd.exe for example) are always useful though for monitoring, deploying etc. I've often had some Perl scripts I had to maintain (just about). Then there are the assorted ETL packages that you'd be expected to know about.

Saying that, most DBAs (that I know) would be able to write basic CLR stuff or know PL/SQL well. For me, the dividing line is knowing about the broader patterns or architecture of .net or Java. I don't need it as a DB developer or DBA. In the same way that .net or PHP or Java Monkeys don't understand database design or architecture or code as well as I do.

Personally, I decided to stop chasing the latest or best client language years ago and focus on database work. That doesn't make me a "non-programmer": I'd need to learn it again if I had to.


What is causing my Transaction log to grow The TLOG in my SQL Server 2008 is growing faster than usual.
The DB has a Full recovery model and we have a TLOG backup every hour.

Can you please write me or refer me to link that describes a way to identify what is the session/process/query that causes the TLOG growth.

I thought that if I'll know WHEN the growth is occurring, I'll be able to detect the job/process that causes the growth.
I've created a job that saves the output of sp_helpdb 'dbname' to a table, but even after knowing the right time, I wasn't able to find the sordid process.

I would appreciate your help in this matter.

Thanks in advance,

Roni.

You can review events from the default trace to identify the growth events you care about:

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 93 -- log autogrow event
ORDER BY StartTime DESC;

Combined with a separate trace or extended event session you set up yourself (e.g. to capture all updates with > x duration or > y reads or writes) you can hopefully find some likely culprits around the same time from the same SPID. You won't be able to do that going back in time, of course, but you could set something up to help identify them in the future.

With that all said, your next instinct might be to fight with this process and constantly run up behind it and shrink the log file. This is not a good idea because, really, what's the point? You're going to shrink it to free up space temporarily, only so the same file can grow and fill up the space again? Please read this Q & A in full:

Why Does the Transaction Log Keep Growing or Run Out of Space?


How to create and start a PDB for RCU in Oracle 12c So I've made a fresh install on a virtual machine based on OEL 6.5 x64.
All my installs are in /opt/apps

I installed a Oracle Database 12c, with the Desktop Version selected (not server), UNICODE encoding, Enterprise Edition. If I get it right, from this point I only have a CDB named orcl that is running.

Now I want to run the Repository Creation Utility to initialize the database for OIM and OAM but when I do so it tells me it won't run on a CDB.
So I checked the Oracle documentation and created a PDB from the default seed with :

CREATE PLUGGABLE DATABASE PDBFMW ADMIN USER fmw IDENTIFIED BY Passw0rd
FILE_NAME_CONVERT=(
'/opt/apps/oradata/orcl/pdbseed/system01.dbf',
'/opt/apps/oradata/orcl/pdbfmw/system01.dbf',
'/opt/apps/oradata/orcl/pdbseed/sysaux01.dbf',
'/opt/apps/oradata/orcl/pdbfmw/sysaux01.dbf',
'/opt/apps/oradata/orcl/pdbseed/pdbseed_temp01.dbf',
'/opt/apps/oradata/orcl/pdbfmw/pdbfmw_temp01.dbf')
STORAGE UNLIMITED;

ALTER PLUGGABLE DATABASE PDBFMW OPEN READ WRITE;
GRANT ALL PRIVILEGES TO fmw WITH ADMIN OPTION;
GRANT SYS TO fmw;

So far my PDB is not started and won't start with the orcl instance so I made this trigger :

CREATE OR REPLACE TRIGGER open_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE';
END open_pdbs;
\

Which, I was told, was supposed to automatically start my PDBFMW along with ORCL.
 So I checked it out with :

SELECT name, open_mode from v$pdbs;

Which tells me that it's correctly opened and in READ WRITE mode.

But when I try to connect with this :

export ORACLE_SID=pdbfmw
sqlplus / as sysdba

It says connected to an idle instance.

Look at:
http://docs.oracle.com/database/121/DBSEG/authentication.htm#DBSEG99827

Operating system authentication is not supported in a multitenant environment.

You can't connect with "sqlplus / as sysdba" to a PDB. It only works with the CDB.

If you change environment variable ORACLE_SID to that of a PDB, you'll connect to the binaries to what he expects to be a CDB that doesn't exist, and you'll get the message "connected to an idle instance"

Fun fact: If you are logged as the oracle binaries owner (or someone in the dba group) and try to connect with:

sqlplus pdbuser/pdbpass@pdb as sysdba

Even if the pdbuser doesn't exist in the CDB, you'll log in it, as pdbuser, with SYSDBA permissions, on the CDB, because of OS Authentication.

Or at least that's what happens in my test/experiment environment...


How do I grant PostgreSQL superuser access to vacuum tables? My logs are constantly filling up with these messages for hundreds of tables:

WARNING:  skipping "some_table_name" --- only table or database owner can analyze it

How do I perform any the following and/or resolve this?

Determine in which database(s) these tables reside? Would be nice if the warning included that information.
Grant the PostgreSQL superuser access to vacuum these tables
Disable these warnings in the log file

Side note: none of the log file rotation, truncation, size/date limits settings are working either but I'll save that for a separate post.

It sounds like you revoked superuser privileges from the cluster's founding user.  Don't do that.  Log in as your remaining superuser, and grant superuser privileges back to the founding user.  If you have no remaining superusers, then you need to shut down the server, start it up in single-user mode, and regrant superuser privs.

The founding user is the username specified to initdb's -U argument when the database was created, or if that argument was not provided, then it is the name of the OS user who ran the initdb.  This is most often postgres.


How to configure AutoMySQLBackup? How I can configure AutoMySQLBackup to backup some selected tables ones per week while the other tables will be backed up every day?

Thanks.

P.S. My google search ninja skills is not working. :)

According to the AutoMySQLBackup documention you can supply an alternate configuration file:

From https://github.com/rcruz/AutoMySQLBackup/blob/master/README

  Thee backup can be run from the command line simply by running the
  following command.

  automysqlbackup /etc/automysqlbackup/myserver.conf

  If you don't supply an argument for automysqlbackup, the default
  configuration in the program automysqlbackup will be used unless a
  global file

  CONFIG_configfile="/etc/automysqlbackup/automysqlbackup.conf"

  exists

So that means you can simply use a different config file, and schedule two different runs in crontab.

That... or you can always roll your own, which I find more flexible.

#!/bin/bash

OUTPUT="/mnt/backups/"

rm -f "$OUTPUTDIR/*.bz2" > /dev/null 2>&1

databases=`mysql -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases
do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump --quick --set-gtid-purged=OFF --databases $db | bzip2 > $OUTPUT/$db.sql.bz2
    fi
done


Placing an ASSERT in a SQL Server query As part of a MERGE query that I want to run I'd like to assert at runtime that a certain condition holds. When a MERGE-match is found I'd like to update a certain column and have the following logic executed:

If the target column is NULL, write the source value
If the target is NOT NULL, assert that target and source are identical

I expect that the two values are always identical in case 2 but I might have made a mistake (have a bug). When that happens I'd like to crash the statement and let my app report the error. This is a very rare error condition, not something that can happen as part of usual processing.

So I was thinking that I could abuse a divide-by-zero exception to trigger a crash:

MERGE
...
WHEN MATCHED BY TARGET THEN UPDATE SET
  TargetCol = CASE
    WHEN TargetCol IS NULL THEN SourceCol
    WHEN TargetCol = SourceCol THEN SourceCol
    ELSE 0/0 END --crash!

Will this work reliably? Is there a reason this should not be done?

Will this work reliably?

The answer to this depends on the strength of the guarantee you need. There have been bugs in the past around CASE evaluation-order, for example constant-folding an error-generating expression. There are no current bugs in SQL Server 2014 that I am aware of that would affect your query, but that does not mean they might not occur in future.

This is a risk with any code of course, but my assessment is that the risk is higher when combining this technique with MERGE. Merge has a complex implementation, and there have been many related bugs in the past, as you know.

  Is there a reason this should not be done?

Personally, I would look to avoid a divide-by-zero as part of the solution. A better option might be to attempt to write a value that violates an explicit CHECK (or some other) constraint on the target table. This would result in an explicit Assert operator in the execution plan, positioned after the Merge operator. It might not be desirable to add such a constraint (or even possible if the column is allowed to hold all values in its type domain) but it is one thing to consider.

One other general suggestion: avoid updating the target column when the values match. This is logically a no-op, but that doesn't always translate physically.

DECLARE @target AS TABLE (pk integer PRIMARY KEY, col2 integer NULL CHECK (col2 <> -1));
DECLARE @source AS TABLE (pk integer PRIMARY KEY, col2 integer NULL);

INSERT @target VALUES (1, 100), (2, NULL), (3, 300);
INSERT @source VALUES (1, 100), (2, 200), (3, 301);

MERGE @target AS T
USING @source AS S
    ON S.pk = T.pk
WHEN MATCHED
    AND
    (
        -- No update if values match
        T.col2 IS NULL
        OR T.col2 <> S.col2
    )
    THEN UPDATE SET col2 =
        CASE
            WHEN T.col2 IS NULL THEN S.col2
            ELSE -1 -- violates CHECK constraint
        END;


Fetch ids(Primary Key) from table where ids not available in table within range I have table named Student. It have primary key student_id which is auto increment and not null.

Now Student contains around 5000 records.

After passed out from university, we need to delete specific student record from Student and related child table as well.

It is working good.

I need to get those numbers who are not in student_id range.

For ex.

I have 5000 records. So assume range is 1-5000. In Student table, there may be data with primary key :

student_id :

1
2
4
5
6
7
9
12
15
etc...

So I want deleted ids, like : 3, 8, 10, 11, 13, 14, etc... And need this result in some text file.

Finally I have 2 questions :

Need query to get those deleted ids
Can I export result in some file after fired query ?

SELECT n
FROM (
        SELECT 1000*n1+100*n2+10*n3+n4+1 n
        FROM
                (
                SELECT 0 n1 UNION
                SELECT 1 UNION
                SELECT 2 UNION
                SELECT 3 UNION
                SELECT 4 UNION
                SELECT 5 UNION
                SELECT 6 UNION
                SELECT 7 UNION
                SELECT 8 UNION
                SELECT 9
                ) n1,
                (
                SELECT 0 n2 UNION
                SELECT 1 UNION
                SELECT 2 UNION
                SELECT 3 UNION
                SELECT 4 UNION
                SELECT 5 UNION
                SELECT 6 UNION
                SELECT 7 UNION
                SELECT 8 UNION
                SELECT 9
                ) n2,
                (
                SELECT 0 n3 UNION
                SELECT 1 UNION
                SELECT 2 UNION
                SELECT 3 UNION
                SELECT 4 UNION
                SELECT 5 UNION
                SELECT 6 UNION
                SELECT 7 UNION
                SELECT 8 UNION
                SELECT 9
                ) n3,
                (
                SELECT 0 n4 UNION
                SELECT 1 UNION
                SELECT 2 UNION
                SELECT 3 UNION
                SELECT 4 UNION
                SELECT 5 UNION
                SELECT 6 UNION
                SELECT 7 UNION
                SELECT 8 UNION
                SELECT 9
                ) n4
        HAVING n < (
                    SELECT MAX(student_id)
                    FROM Student
                   )
     ) numbers
LEFT JOIN Student ON student_id = n
WHERE student_id IS NULL

fiddle


Recover two weeks old stored proc which has changed since I accidentally changed a stored procedure on Microsoft 2016 SQL Database. Then I noticed the change after complaints from the reporting side. Made few changes and run but didnt work. Not it seems I need to recover the stored proc 2 weeks ago. Can someone explain the process.

There's no way to recover the code unless you restore a backup of the server taken prior to when you updated the database with your change. But I would just check your source control repository and re-run the prior version.
This is why one generally keeps stored procedures, triggers, table definitions and the like in a source control system, like e.g. Git. That would prevent this sort of situation from happening again and provide many other benefits, such as versioning.


SQL Server Internal Memory Pressure I am trying to identify the cause of internal memory pressure. From what I have learned, Resource Monitor Ring Buffer returns an indicator for internal pressure. For example by using this query,

SELECT Top (1) *
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

The XML results (within the resource monitor tag):

<ResourceMonitor>
  <Notification>RESOURCE_MEMPHYSICAL_LOW</Notification>
  <IndicatorsProcess>2</IndicatorsProcess>
  <IndicatorsSystem>0</IndicatorsSystem>
  <NodeId>0</NodeId>
  <Effect type="APPLY_LOWPM" state="EFFECT_OFF" reversed="0">0</Effect>
  <Effect type="APPLY_HIGHPM" state="EFFECT_IGNORE" reversed="0">100789</Effect>
  <Effect type="REVERT_HIGHPM" state="EFFECT_OFF" reversed="0">0</Effect>
</ResourceMonitor>

In this article by Bob Dorr, shrinking signal from Memory Broker could create internal memory pressure and reflect in resource monitor notification with IndicatorsProcess = 2 (IDX_MEMPHYSICAL_LOW) as shown above.

This article by Slava Oks shows another way of checking internal memory pressure with RING_BUFFER_SINGLE_PAGE_ALLOCATOR. I don't know much about this particular ring buffer because I never seen it in my environment.

SELECT Top (1) *
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SINGLE_PAGE_ALLOCATOR'

The XML result this time:

<Pressure status="0"><AllocatedPages>477</AllocatedPages>
  <AllAllocatedPages>477</AllAllocatedPages>
  <TargetPages>31553</TargetPages>
  <AjustedTargetPages>31553</AjustedTargetPages>
  ...
</Pressure>

My question is, which internal processes may be contributing to internal physical memory pressure that will turn on this indicator = 2 (IDX_MEMPHYSICAL_LOW)?  What I see from resource monitor indication of internal physical memory pressure, these are among them:

Memory broker shrink operation (e.g. cache cleanup) which can be identified through memory broker ring buffer,
Max Server Memory setting change and,
Buffer pool stolen page > 75% (as I am not seeing it reflected from single page allocator ring buffer)

But are there others, too? And is there any record/log to identify them through DMV or other mean?

The closest internal documentation I can find on the RM is from the PSS team here: How It Works: What are the RING_BUFFER_RESOURCE_MONITOR telling me?

Which specific to your question on which internal processes may contribute to changing the indicator there is no specific documentation that answers this...that I can find. The article references actually shows that there is a startup trace flag 8020 that can be used (with extreme caution) to narrow down what is triggering RM.

I think the main bit of text from the article above would be this:

  There are memory broker decisions that come into play at this time.   What the information is is saying is that a cache or the cache’s predicted usage will exceed internal targets and we need to start doing cache cleanup.    By looking at the memory broker, ring buffer entries, you can see the behavior that broker it asking of RM.   Using the clock hands you can see the internal and external hand movement.   This will help you determine which caches are involved.    The dbcc memorystatus() show good details and last notification states as well.

Not sure if that answers the question in full, but is about as close to what I can find online.


Allow remote access only from certain IP addresses If I've understood what I've read, all you need to do in order to allow remote access to SQL Server is to open TCP port 1433 in Windows firewall. But that will leave your server wide open to the entire internet and SQL Server itself provides no further tools to fine-tune the source of connections.

Is there a way to grant access to such port only to connections from a know static IP address?

I use Windows Firewall and Powershell for this.  Unless of course you want to get something like ScaleArc to act as a reverse proxy.  Your options are really going to be rather limited to something that utilizes one of those technologies.  Firewall/IPSec or a reverse proxy.

This is the outline of the script I use to verify ports the rule exists in this environment and add it.  Specify -RemoteAddress to limit access to only certain IPs:

New-NetFirewallRule Docs

-RemoteAddress<String[]>
Specifies that network packets with matching IP addresses match this rule.
This parameter value is the second end point of an IPsec rule and specifies the computers that are subject to the requirements of this rule.
This parameter value is an IPv4 or IPv6 address, hostname, subnet, range, or the following keyword: Any.
The acceptable formats for this parameter are:
-- Single IPv4 Address: 1.2.3.4
-- Single IPv6 Address: fe80::1
-- IPv4 Subnet (by network bit count): 1.2.3.4/24
-- IPv6 Subnet (by network bit count): fe80::1/48
-- IPv4 Subnet (by network mask): 1.2.3.4/255.255.255.0
-- IPv4 Range: 1.2.3.4 through 1.2.3.7
-- IPv6 Range: fe80::1 through fe80::9
Note: Querying for rules with this parameter can only be performed using filter objects. See the Get-NetFirewallAddressFilter cmdlet for more information

.

Powershell Script:

##Internal SQL Endpoint for AGs to communicate together. Needed on all nodes.
$LocalPortNumber = '5122'
$PortDisplayName = 'SQL-AG-Endpoint'
$Protocol = 'TCP'
$PortCheck = Get-NetFirewallPortFilter | where {$_.LocalPort -eq $LocalPortNumber}
if ($PortCheck){  ##Check if exists))
write-host "Port $($LocalPortNumber) for $($PortDisplayName) already found. Not adding Firewall Entry" #-ForegroundColor DarkGreen
}
ELSE
{
write-host "Port $($LocalPortNumber) is not found. Adding now"
New-NetFirewallRule -DisplayName "$($PortDisplayName)" -Direction Inbound –LocalPort $($LocalPortNumber) -Protocol $($Protocol) -Action Allow
}


Compare the database compatibility_level to the server version? I have a mixed environment of servers from 2000 to 2012, and I'd like to find any databases that have a compatibility level set less than the version of the server it is running on.

Ideally, I'd like to do something like:

SELECT name, compatibility_level
FROM master.sys.databases
WHERE compatibility_level <> SERVERPROPERTY('productcompatibilitylevel')

but that unfortunately doesn't exist. Instead I'm having to do a big CASE statement based on SERVERPROPERTY('productversion'). Any better ways to do this?

I found a couple of options that I think will work:

First, you could multiply the first two digits of SERVERPROPERTY('productversion') by 10:

SELECT name, compatibility_level
FROM master.sys.databases
WHERE compatibility_level <>
    10 * convert(int,CONVERT(char(2),SERVERPROPERTY('productversion')))

Or, probably easier, you could just compare to the highest compatibility_level on the server:

SELECT name, compatibility_level
FROM master.sys.databases
WHERE compatibility_level < (SELECT max(compatibility_level) FROM sys.databases)

FYI, you can't just compare to the compatibility_level of MASTER or MODEL, as those can be left lower after an upgrade, or deliberately changed. TempDB, however, I think is always the current version.


No connections to mongodb replset member post outage I have a weird issue in MongoDB. Let me explain.

Suppose, I have following components of mongodb:

A shard with a replset containing 3 members, with 1 primary(say A) and 2
secondary nodes (say B and C)
1 mongo-config server
1 mongos server

I am reading the data from "mongos" server with "secondary" read preference.

Lets assume, I somehow created "4 read connections" on mongos server. Now, the connections are divided between secondary replset members.

No. of Connections:

A (primary): 0 connections
B (Ist secondary): 2 connections
C (IInd secondary): 2 connections

Now the real issue starts when I restart mongod service of B (secondary) to replicate the outage type situation, all the read query connections are lost after that even after mongod got re-started.

A (primary): 0 connections
B (Ist secondary): 0 connections
C (IInd secondary): 4 connections

But to solve this, I always have to restart "mongos" service to get back B again working which is painful.

I don't find any feasible solution for this. Can any one help me in sorting this situation.

By "B" again working do you mean the connections to be distributed evenly when B comes back? Do you see new connections also going to C when B has come back?

I believe MongoS does not do dynamic load balancing w.r.t. to the existing state of connections. However if you create new connections, MongoS may appropriately create connections on B. Do you not see this behaviour?


MySQL SSL disabled on Windows I want to enable an SSL connection to my database.  I've created SSL certificates and so on, however SSL remains disabled:

mysql> show variables like '%ssl%';
+---------------+------------------------------------+
| Variable_name | Value                              |
+---------------+------------------------------------+
| have_openssl  | DISABLED                           |
| have_ssl      | DISABLED                           |
| ssl_ca        | C:/Users/Krzysztof/ca-cert.pem     |
| ssl_capath    |                                    |
| ssl_cert      | C:/Users/Krzysztof/server-cert.pem |
| ssl_cipher    |                                    |
| ssl_key       | C:/Users/Krzysztof/server-key.pem  |
+---------------+------------------------------------+

What am I missing?

I'm using Windows 7.

You need to check to make sure the user indeed has the ssl field loaded.

Run this query;

SELECT A.* FROM
(SELECT user,host FROM mysql.user WHERE ssl_type IS NOT NULL) A
INNER JOIN
(SELECT user,host FROM mysql.user WHERE ssl_cipher IS NOT NULL) B
USING (user,host);

If nothing comes back, then look the user using

SELECT * FROM
(SELECT user,host FROM mysql.user WHERE ssl_type IS NOT NULL) A
UNION
(SELECT user,host FROM mysql.user WHERE ssl_cipher IS NOT NULL);

If nothing comes back from this second query, that means no user in mysql.user has ssl_type and ssl_cipher filled in properly.

Another thing you can investigate is this:

Did you add this to my.ini ?

[mysqld]
ssl

If you didn't, please do so and run this

C:\> net stop mysql
C:\> net start mysql


How do I get a list of values for column x GROUPed BY column y? I have the following table structure and data (using Oracle DB 12c):

CREATE TABLE authors (
    aid NUMBER(38) GENERATED ALWAYS AS IDENTITY CONSTRAINT authors_pk PRIMARY KEY NOT NULL,
    fname VARCHAR2(100 CHAR) NOT NULL,
    sname VARCHAR2(150 CHAR) NOT NULL,
    dob NUMBER(4),
    gender CHAR(1 CHAR)
);

CREATE UNIQUE INDEX authors_uind ON authors (fname, sname, dob, gender); --- Combination of columns must be unique

INSERT INTO authors(sname, fname, dob, gender) VALUES('Codd', 'Edgar F', 1923, 'M');
INSERT INTO authors(sname, fname, dob, gender) VALUES('Date', 'Chris J', 1941, 'M');
INSERT INTO authors(sname, fname, dob, gender) VALUES('Darwin', 'Hugh', 1943, 'M');
INSERT INTO authors(sname, fname, dob, gender) VALUES('Lions', 'John', 1937, 'M');

CREATE TABLE publications (
    pid NUMBER(38) GENERATED ALWAYS AS IDENTITY CONSTRAINT publications_pk PRIMARY KEY NOT NULL,
    title VARCHAR2(150 CHAR) NOT NULL,
    written NUMBER(4)
);
CREATE INDEX publications_ind ON publications (title, written);

INSERT INTO publications(title, written) VALUES('A Relational Model of Data for Large Shared Data Banks', 1970);
INSERT INTO publications(title, written) VALUES('The Relational Model for Database Management', 1990);
INSERT INTO publications(title, written) VALUES('An Introduction to Database Systems', 2003);
INSERT INTO publications(title, written) VALUES('The Third Manifesto', 2000);
INSERT INTO publications(title, written) VALUES('Temporal Data and the Relational Model', 2002);
INSERT INTO publications(title, written) VALUES('Database in Depth: Relational Theory for Practitioners', 2005);
INSERT INTO publications(title, written) VALUES('Commentary on UNIX', 1976);

CREATE TABLE author_publications (
    aid NUMBER(38) REFERENCES authors (aid),
    pid NUMBER(38) REFERENCES publications (pid),
    CONSTRAINT author_publications_pk PRIMARY KEY (aid, pid)
);

INSERT INTO author_publications(aid, pid) VALUES(1, 1);
INSERT INTO author_publications(aid, pid) VALUES(1, 2);
INSERT INTO author_publications(aid, pid) VALUES(2, 3);
INSERT INTO author_publications(aid, pid) VALUES(2, 4);
INSERT INTO author_publications(aid, pid) VALUES(2, 5);
INSERT INTO author_publications(aid, pid) VALUES(2, 6);
INSERT INTO author_publications(aid, pid) VALUES(3, 4);
INSERT INTO author_publications(aid, pid) VALUES(3, 5);
INSERT INTO author_publications(aid, pid) VALUES(4, 7);

It's possible for one author to have written multiple books and one book to have been written by multiple authors.

What should the query look like so that I get a single row per book (for all books), with the names of the authors concatenated together. EG:

| Written | Title | PID | Authors |
|---------|-------|-----|---------|
| 1990 | THE THIRD MANIFESTO | 4 | DATE, DARWIN |
| 2002 | TEMPORAL DATA AND THE RELATIONAL MODEL | 8 | DARWIN, DATE |

Note 1: The order of the authors' names in the fourth column is not important, nor is a comma after the name of a single author in a row.

Note 2: The use of a GROUP BY clause is not required if the desired result can be achieved by a different means.

The query I have is as follows:

--- Get a list of author names per title
SELECT p.written, upper(p.title), p.pid, concat(upper(a.sname), ', ')
FROM publications p INNER JOIN author_publications apub on p.pid = apub.pid
INNER JOIN authors a on apub.aid = a.aid
GROUP BY p.pid
order by p.written, upper(p.title)
;

The error message I get is  "not a GROUP BY expression". (I don't understand what particular part of that statement fails or why.) The line in question is the start of the SELECT statement.

Instead of CONCAT, use LISTAGG:

select
  p.written, upper(p.title) as title, p.pid,
  upper(listagg(a.sname, ', ') within group (order by a.sname)) as authors
from publications p
join author_publications ap on p.pid = ap.pid
join authors a on ap.aid = a.aid
group by p.written, p.title, p.pid
order by p.written;

Result:

WRITTEN TITLE                                                         PID AUTHORS
------- ------------------------------------------------------------ ---- ---------------
   1970 A RELATIONAL MODEL OF DATA FOR LARGE SHARED DATA BANKS          1 CODD
   1976 COMMENTARY ON UNIX                                              7 LIONS
   1990 THE RELATIONAL MODEL FOR DATABASE MANAGEMENT                    2 CODD
   2000 THE THIRD MANIFESTO                                             4 DARWIN, DATE
   2002 TEMPORAL DATA AND THE RELATIONAL MODEL                          5 DARWIN, DATE
   2003 AN INTRODUCTION TO DATABASE SYSTEMS                             3 DATE
   2005 DATABASE IN DEPTH: RELATIONAL THEORY FOR PRACTITIONERS          6 DATE


Copying SQL Server database I am working on a SQL Server on which I have three databases:

User: This is the one where real data resides
Test
Development: This is used for testing and developement.

From time to time I want to copy the content of the user database into the test and / or development database to make sure that the developement and test environments are as close as possible to the user environment.

I thought I could use the "Copy Database Wizard" for this, but it fails with an error

  "Object reference not set to an instance of an object

after I have chosen which hosts to copy from and to (localhost in both cases). I have not been able to find any solutions to this error.

Question

Does anybody have any ideas about what causes this and how to correct the problem (maybe I need to install something or do something with the setup of the server?)
What would be the selected way of doing the transfer if I cannot use the Copy Database Wizard? - This is to be done, I would guess, about once a month.

If it matters, I have full admin access on the database server but not on the underlying windows server.

Are you just looking for a complete copy of the data to completely replace what already lives on the destination instance?  In that case, a full backup of the source database and a restore into the destination instance would be the cleanest and easiest route.

If you are looking at select data copying (i.e. you don't want all data, or you don't want to replace the destination data) then you'll need to have a logic layer between the two data sources.  Some ideas behind this would be SSIS, PowerShell, or any other tool to do some brain work between both SQL Server databases.  The tool to use would depend on the specifics of your need as well as your comfort level with all the options.


MongoDB temporary node / mirror When I have a running MongoDB instance with some data and requests against it, can I start a second instance on another machine, register it as another node in the first instance and all the data is copied to the second node while changes on the first node in the meantime are also mirrored to the second instance? Is that possible?

The context is: I have a single machine deployment with an application and mongodb running on the same instance (Amazon EC2). Its a low budget deployment but I try to have as few downtime as possible. I'd like to change the ec2 instance every week and for that I'd need some sort of mirroring. Is that possible?

You can replicate data from one server to others using Replication feature in MongoDB.

The replica set ensures high availability and redundancy of you mongodb data in case if node failure. In your case, you can create a replica set of 3 nodes (PRIMARY, SECONDARY, ARBITER). When one of the server goes down, Secondary would be promoted as PRIMARY which ensures high availability of MongoDB data.

It is always recommended to have at least 3 members in replica set.

You can find detailed document here


Need some recommendations on organizing data I'm new here.  This question, to my mind, is allowed by the rules I saw in the "2 minute tour".  If I got it wrong, no need for nasty grams.  I'll delete if enough people say so.  But anyway, I have a bunch of data that I need to organize in a database.  This isn't the first I've ever constructed a database, but this might be the most complicated I've done and I want to "do it right".  So ....

I have a bunch of simulation runs.  To completely specify a simulation run, you need: code, code rev., date of run, begin time, time span, population model, time step, time step method, left boundary, and right boundary.

That specifies a run, but associated with each run is a set of data that comes out of analysis of the results: global max, final value, fitting coeff 1, fitting coeff2, and number of local maxima.

If number of local maxima is greater than zero, then I need to capture that info.  A local maximum will have a: value, location, and simulation run.

Well, I have figured out that I need to split off the local maxima into their own table, and link the simulation run of each maximum to the simulation run in the central table.

But what about that central table?  An aggregate key would have to have all of the information I listed above for specifying a particular run.  Is it a good thing to repeat all of that data in my local maxima table?  I thought about establishing a simulation run table that has all of the values needed to specify a run, and then an added unique numerical identifier for each run.  Then I could link to this identifier in my local maxima table.  This would cut down on the amount of data storage, but would it cause other problems?

Also, as I indicated, some of the data pertaining to a run defines the run while other data comes out of the analysis of the run.  Should I split these two types of data into two tables and link with that identifier I mentioned, or should I leave everything all together in one big table?

Thanks.

Using a surrogate key as a primary key for tables is a very common approach and it seems to fit your situation quite well. The foreign key in local maxima can then be a single column referencing the surrogate key.

If the relationship between the definition of the run and the outcome of the run is a one-to-one relationship, then you should at first consider having them in the same table, and only split them up if you feel it's more natural, convenient, or better for performance.


identity value way off in a restored database I have a table with a identity primary key column. Its last value is 5228491220 using IDENT_CURRENT.

I make a full backup of the database, restore it on a second server. (We backup and restore daily from one server to the other one.) Immediately I pull IDENT_CURRENT from this table and the value is 5228501279.

That is, the restore jumps the last value by more than 10000.

I can't make sense of it. Why would the last value of this identity column not be carried properly through the backup?

More practically, how do I make sure the value carries over properly?

If you're on SQL Server 2012 (or newer), you may be running into a planned change, where identity values are retrieved in batches, and the next ID value may be incremented after a shutdown, or a restore.

Quick summary: SQL grabs its IDENTITY values in batches, and if it's not sure what the last ID used was, it will increment the next IDENTITY value by 10 (for tinyint) to 10000 (for bigint) when starting back up.

(Compiled from comments)


Child, Parent and Grand-Parent relationship where a child may not necessary have a parent or a grand parent Here, I have some problem representing a hostel-room-inmate entities.
First an inmate is given a hostel seat,
Then he is allotted a particular hostel,
Then he is allotted a room.
My approach is that the inmate table contains a parent-link to the room table, the room table contains a parent-link to the hostel table.
table hostel(id, name)
table room(id, hostel_id, room_no)
table inmate(id, room_id, name, etc)

But while at the stage of allotment, the inmate is first allotted a hostel, in which the room_id is to be left blank, thereby meaning that the inmate is not allotted a hostel room and so on...
How do I solve this problem please...
I wanted to use it in Laravel development environment
(NB: I have some idea of creating many-to-many relationship type by creating relationship table, but just asking if there's better approach than this.)

I would add hostel_id to inmate, and leave Room-id empty till he arrives and the room-number is chosen.
So you could also, allow inmate to book a specific room with sea view for example
And you could check if any rooms for a date are available, for example hostel 1 has 10 room and 10 users have chosen the hostel already, you ca tell him  that is not possible and give additional information when a room is free again or a hostel nearby who has vacancy.
table hostel(id, name)
table inmate(id,hostel_id, room_id, name, etc)
table room(id, hostel_id, room_no)


How Do I Grant Permissions to Replace a Function? I am getting the following error message (when trying to enable NOSQL in DB2 10.5, although I don't think that's really the issue):

  Error code: -551 DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501,
  SQLERRMC=DB2ADMIN;REPLACE FUNCTION; SYSTOOLS.JSON_VAL2, DRIVER=4.17.29

  An error occurred during database object creation, rolling back

The DB2ADMIN user already has the following permissions:

grant DBADM, SECADM, ACCESSCTRL, DATAACCESS ON DATABASE TO USER db2admin

I cannot find the GRANT command that I need to use to enable DB2ADMIN to replace the function. Sorry, I'm not a DBA, so I just need the exact command I'd need to enter.

Apparently, whatever you are doing tries to CREATE OR REPLACE the function SYSTOOLS.JSON_VAL2. To replace any object the ID performing the operation must be the owner of that object, that is, the same ID that issued the very first CREATE statement.

You have several options:

Use the TRANSFER OWNERSHIP statement to change the function owner. SECADM can do that.
Determine the current owner (SELECT OWNER FROM SYSCAT.ROUTINES WHERE ROUTINENAME = 'JSON_VAL2' AND ROUTINESCHEMA = 'SYSTOOLS') and perform your task while logged in as that user.
Drop the object explicitly, for which DBADM is sufficient, before performing your task.


RAID0 vs multiple files in file group I have a server that gets updated and backed up once a day and processes queries the rest of the day (selects). I'm trying to improve performance of the update process and the selects. I'm not worried at all about redundancy since the database is backed up after it's updated.

I'm currently using RAID0 with 2 SSDs but I think that the controller is limiting the read and write performance. I read that when using multiple files in a single file group then data is written in a stripped way to all files. Should I move from RAID0 to 2 single disks where the data is stored in one file in each disk?

RAID0 vs multiple files in file group
  RAID0, definitely, with SSD's.  There MAY be an edge case exception if you know you're getting hung up on two particular indexes (clustered or not) in a JOIN, where you could gain performance by having two FILEGROUPs, one on each disk, and splitting those indexes between the filegroups, but I really, really doubt it, and you'd only find that out by benchmarking it.

As far as not caring about redundancy, I think you're crazy.  Note that a modern Dell PERC (LSI Logic) controller can get >1GB/s sequential data transfer off of a set of 6 SATA SSD's in RAID5, and the random rates are also in the hundreds of MB/s if you keep your IO depth deep enough.  Note also there's a huge different between high-end SSD's and low-end SSD's, particularly for random writes (i.e. probably your update process).

  I'm trying to improve performance of the update process and the selects.

This is a general performance tuning question, so all the normal performance tuning advice applies.  Run Profiler on your selects during the day, look at reads, writes, duration, CPU.  Use the SSMS-based Standard Report for Top Queries by Total CPU and Total IO during the day.  Tune your queries and data structure, starting with the highest impact and working your way down.

Do the same thing with the update, but separately.

If you really think IO is really the issue, look at IOStall in sys.dm_io_virtual_file_stats or use Perfmon (Avg sec/Read and Avg sec/Write).

As a general shotgun approach, if during the day it's 100% read only (selects), you can ALTER DATABASE x SET READ_ONLY to reduce possible locking.  Set it to READ_WRITE before the update, and back to READ_ONLY afterwards.

As part of your update, are you rebuilding indexes (which also updates their statistics with FULLSCAN as part of the rebuild)?  Or making sure stats are up to date?

If you're really having IO issues on pure reads (selects), and you're unwilling or unable to tune your queries and schema, then there's always buying more RAM or increasing the maximum amount of RAM allocated to this instance of SQL Server.


Sync many client database to one central database We have client application which can be installed on local machine where users can work offline and create projects in it. This application is already developed.

Now we need to create Admin application where Admin can view all the Projects created by the client application. So basically we need to sync all the client database with one central database.

I considered using Microsoft Sync Framework but the challenge is.

Suppose I have table MasterProject in which ProjectId is Primary key and Identity.
user1 creates two project so the Project ids will be 1 & 2.
user2 creates 2 project its Project ids will be 1 & 2.

Now I need to sync both these data to Central database it won't allow since project id is Primary key at central database. If I remove primary key at central database the Sync Framework does not support.
I have also tried to create composite primary key on ProjectId and UserId and tried to trick the sync framework by matching the schema of tables but it does not work.

I don't want to change the database of client application.
Is there any way to achieve this?

It seems to me that you're falling into an XY Problem. You want to see the data from lots of different databases, and have the mindset that you should only connect to a single database. That line of thinking logically lead you to the conclusion that you need to bring all the information into one central database.

IMO your real problem is needing to see the data in multiple databases in one application.

One way to accomplish this is to merge the separate databases back into a central database like you suggested. Since you aren't looking to modify the existing clients to use the master database this seems like a complicated and error prone plan that will increase your maintenance costs.

You have to figure out how to merge separate databases without conflicts.
You have to worry about performance tuning the master database separately from the clients since it is going to naturally have much more data than any single source database.
You are artificially increasing the storage/hardware/licensing costs by creating a master database that is only going to be used by a small number of users.
While the space/size constraints of SQL Express might work for the client databases, once all the databases are pulled into the master database the combined total might overwhelm SQL Express's constraints.

I would recommend you consider taking a different approach. Instead of concentrating the databases why not concentrate the connection strings? Following this route you would only have to write a screen for the admins to select the project and then perform a lookup for the correct connection string for the project's database. Then you would be able to essentially use the client but dynamically swap out the connection string depending on what database is being targeted.


MySQL compression on Google Cloud SQL As the Google Cloud SQL is a managed instance of MySQL under the hood, limited to an absolute max on 2nd generation machines of 100GB of RAM, should I be compressing some of my table and index data ? I am designing a global system  so eventually the working set will probably exceed the 100GB.

Previously i've achieved performance improvements by compressing tables and indexes in SQL Server and Oracle systems that were memory bound.

I'm new to GCP, and consequently MySQL. Would the usage of compression still apply in this case? are there any such functions or procedures (like compression_estimate in SQL Server) I can run to assess if a table or index will benefit from the compression?

Do you really need compressed? A multi-terabyte datasets may happily live on 100G-RAM machine. What is your data like? Text? Blobs? Lots of rows? Care to share the CREATE TABLEs?

I don't have any details on Cloud SQL.  But...

Compression is usually not the first place to look when thinking of Scaling.  It has overhead (usually CPU, sometimes complexity).

InnoDB's compression gives you about 2x.
Outside compression (zip, etc) give about 3x for text. That is not very significant for "scaling".
TokuDB and InfiniDB claim to get 10x, and I have heard supporting evidence for such.

"Scaling" usually involves Sharding or some other "distributed" technique.

RAM is not usually the limiting factor in Scaling; it is disk space. Or it could be any of several metrics - IOPs, network bandwidth, etc.

Scaling...

Read scaling is 'easily' handled via Replication.
Write scaling is not handled by any normal technique.
Galera Cluster gives some write scaling.
Sharding give a lot of write scaling, but at noticeable complexity in the application.
MySQL's NDB Cluster gives some write scaling, but it is usually limited to niche applications, and not for general use.  (And the setup is quite complex.)
I am skeptical of any off-the-shelf database "scaling" -- beware of marketing hype.  (Web servers are easy to scale; databases are not.)


Delete record in Aerospike? I'm tuning Aerospike, after I use Recipe for an SSD Storage Engine with this config:

namespace test {
        replication-factor 2
        memory-size 4G
        default-ttl 30d # 30 days, use 0 to never expire/evict.

        #storage-engine memory
        storage-engine device {     # Configure the storage-engine to use persistence
        device /dev/vdb    # raw device. Maximum size is 2 TiB
        # device /dev/<device>  # (optional) another raw device.
        write-block-size 128K   # adjust block size to make it efficient for SSDs.
    }
}

After run the benchmarks first time,my SSD is completely full

quanlm@quanlm2:/mnt/device$ df -h
Filesystem      Size  Used Avail Use% Mounted on
udev            2.0G     0  2.0G   0% /dev
tmpfs           395M  3.1M  392M   1% /run
/dev/vda1        60G  2.8G   54G   5% /
tmpfs           2.0G     0  2.0G   0% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           2.0G     0  2.0G   0% /sys/fs/cgroup
/dev/vdb         64Z   64Z   59G 100% /mnt/device
tmpfs           395M     0  395M   0% /run/user/1000

So is there anyway to delete the data in my SSD (/dev/vdb) ?

First of all you should not be mounting any file system on the device that Aerospike is configured to use. Aerospike manages its storage directly. The file system metadata will corrupt Aerospike storage.

In storage engine device configuration Aerospike will use the entire device irrespective of how much data you actually inserted. If you do not want to give entire device you can create raw partition on the device (without any file system) and configure the raw partition as device in Aerospike.

To see how much of the device is being used, you should be looking Aerospike stats. To wipe the device you can either use the dd command or the more time efficient blkdiscard.


Get distinct category columns with multiple subcategory columns mapped in table I have 2 tables, category and subcategory, this query

select C.Description,s.Description
from category C
inner join SubCategory S on C.CategoryID =S.CategoryID
where  C.IsActive=1 and S.IsActive=1

gives the following output:

But my output has to be as follows:

You can accomplish that using ROW_NUMBER().

Take a look at the example below:

DROP TABLE IF EXISTS #temp
CREATE TABLE #temp ( Description VARCHAR(100), SubDescription VARCHAR(100));

INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment', 'Peds' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment', 'Peds2' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment', 'Peds3' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment', 'Peds4' );

INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment1', 'Peds' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment1', 'Peds2' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment1', 'Peds3' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment1', 'Peds4' );

WITH Temp
AS (
    SELECT
        Description,
        SubDescription,
        ROW_NUMBER() OVER ( PARTITION BY Description
ORDER BY Description ) rownumber
    FROM #Temp
)
SELECT
    CASE WHEN Temp.rownumber = 1 THEN Description ELSE '' END Description,
    SubDescription
FROM Temp;

But I'd prefer doing that in the application layer.


When a table is locked but SHOW PROCESSLIST shows nothing We had some slowness in our application. When I checked I figure out that one particular table was locked so all transactions were rejected by Lock wait timeout exceeded. When I did SHOW PROCESSLIST; I was expecting to see  some processes with Waiting for table level lock state but it shows nothing !? only some queries INSERT/UPDATE on the same table taking exactly 120 seconds before time-out and others processes on sleeping state (see picture):

The reason in my case was that one transaction is making a read-lock on a particular table leading to a "Lock wait timeout exceeded" because its not committed. So all other transactions are waiting to that particular tranx to do commit.
Now, why SHOW PROCESSLIST; show nothing ? In fact, it does.
you'll find it (the process) with a "Sleep" state and with no query shown in the info column like if it's just a regular connection to the database.
Let's check my example in the image :
In Blue, those are some very simple queries blocked (taking 120 second before a time-out) and the red one is the "ghost" or non-committed transaction.
To see those kind of transactions execute :
SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY
FROM INNODB_TRX

And you may kill the process kill <id>; in which TRX_QUERY is null to free other transactions. In my case, KILL 33267342;
Make sure to check the non-committed transaction to see if a commit; or a Rollback clause is missing in order to ovoid such issues.


SQL Server clustered index, index balancing and insert performance using NewID I have a large (6db) trace table. It has a clustered key (DateTime) which is created through GETDATE().

The connection pool for connections to this database/table rises as high as 50 on average across a cluster of 10 computers, so on average we have at ~500 concurrent connections attempting to insert.

The database fits in memory and hardly any IO is seen at all.

I am trying to figure out whether under sustained INSERT load the clustered index gets to a point where it rebalances the tree, and whether this will cause a slowdown in the number of inserts that the system can sustain.

There is some question in my mind as to whether the rebalancing an index is something SQL Server does on a clustered index (and even on a non-clustered index).

Questions-

Are there any reasons for periodic/cyclic slow-down of insert performance?
Do rebalance operations automatically trigger on clustered indexes?
Do rebalance operations automatically trigger on non-clustered indexes?

Other info

SQL Server 2008
Really BIG server - 256Gb, 40 cores, 40mbit LAN...

SQL Server does not "rebalance the tree" as a periodic event. I have last heard this term in the context of Oracle. All that SQL Server does it increase the tree height when necessary. This is an event that happens only a few times in the entire existence of a B-tree.

In a DML heavy workload there can be many small tree adjustments called page splits. These are indeed detrimental to CPU and IO usage and they can cause fragmentation. If you are inserting in ascending date order this problem does not occur because tree "appends" are a special case that SQL Server optimizes for. In any case a page split only affect a handful of pages.

No periodically occurring tree operations.

Clustered indexes have (almost) the same structure as non-clustered indexes.

All the usual SQL Server B-tree index advice applies: Chose the key wisely (seems like you have a good one based on ascending datetime values) and have a strategy for fragmentation and for reclaiming space in case of deletes.


DatabaseIntegrityCheck Fails on Large Database Every Day On one of my databases, the integrity check keeps failing with the error:

  Connection may have been terminated by the server. [SQLSTATE HY000]
  Msg 596, Sev 21, State 1: Cannot continue the execution because the
  session is in the kill state. [SQLSTATE HY000].

There is nothing else going on that I can tell and so this is a particularly hard thing to solve.  It's as if the database is timing out or something on the CheckDB command.

I would recommend check space on data file of your database and extended in case there is only 5% free of space.


What is the purpose of sqlbulkcopy's useinternaltransaction property? I'm just testing my PowerShellScript to do bulkcopy of multiple tables between sql-server and oracle Databases.
When the destination is Oracle, than I can monitor the progress by by executing

select count(*) from Mytable

the single batchsize chunkss individual committed.

Using a sql-server destination, there seems to be a single transaction. Which was rolledback due to network error at first trial. The second is still in progress. Will the use of internal transaction commit the individual chunks?

The use of bulkcopy seems a bit of an all or nothing, if it fails you can restart from the beginning.

$source = 'ora'
$dest = 'sql'

$SourceConnectionString_sql      = "Data Source=localhost;Initial Catalog=MySQLDatabase;Integrated Security=True"
$SourceConnectionString_ora      = "Data Source=MyOracleDatabase;User ID=MyUser;Password=MyPassword"

$DestinationConnectionString_sql = "Data Source=localhost;Initial Catalog=MySqleDatabase2;Integrated Security=True"
$DestinationConnectionString_ora = "Data Source=MyOracleDatabase2;User ID=MyUser;Password=MyPassword"

# -----------------------------------------------------------------------------

$SQL_Select_TABLES = "SELECT name FROM sys.Tables ORDER BY 1"
$SQL_Select_TABLES = "SELECT Top 1 name FROM sys.Tables"

$ORA_Select_TABLES = "SELECT TABLE_NAME Name FROM user_tables ORDER BY 1"
$ORA_Select_TABLES = "SELECT TABLE_NAME Name FROM user_tables where ROWNUM = 1"

$notifyAfter = 1000
# -----------------------------------------------------------------------------
if ($ora_dll -eq $null)
{
    $ora_dll = [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess")
}

$global:start = get-date
$notifyFunction={
    $dauer = [int]((get-date) - $global:start).TotalMilliseconds / 1000
    Write-Host "$tableName $($args[1].RowsCopied) $dauer"
    }

if ($dest -eq 'sql')
{
    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $DestinationConnectionString_sql
}
else
{
    $bulkCopy = new-object ("Oracle.DataAccess.Client.OracleBulkCopy") $DestinationConnectionString_ora
}

$bulkCopy.BatchSize = 50000
$bulkCopy.BulkCopyTimeout=10000000
if ($notifyAfter -gt 0){
    $bulkCopy.NotifyAfter=$notifyafter
    if ($dest -eq 'sql')
    {
       $bulkCopy.Add_SQlRowscopied($notifyFunction)
    }
    else
    {
       $bulkCopy.Add_OracleRowscopied($notifyFunction)
    }

}

if ($source -eq 'sql')
{
    $sourceConnection  = New-Object System.Data.SqlClient.SQLConnection($SourceConnectionString_sql)
    $sourceConnection.open()

    $commandSourceData  = New-Object system.Data.SqlClient.SqlCommand($SQL_Select_TABLES, $sourceConnection)
    $da = New-Object system.Data.SqlClient.SqlDataAdapter($commandSourceData)
    $dt = New-Object system.Data.datatable
    [void]$da.fill($dt)
}
else
{
    $sourceConnection  = New-Object Oracle.DataAccess.Client.OracleConnection($SourceConnectionString_ora)
    $sourceConnection.open()

    $commandSourceData  = New-Object Oracle.DataAccess.Client.OracleCommand($Ora_Select_TABLES, $sourceConnection)
    $da = New-Object Oracle.DataAccess.Client.OracleDataAdapter($commandSourceData)
    $dt = New-Object system.Data.datatable
    [void]$da.fill($dt)
}

foreach ($table in $dt)
{
    $tablename  = $table.name
    $Tablename
    try
    {
        $sql = "SELECT * FROM $tableName"
        if ($source -eq 'sql')
        {
            $commandSourceData  = New-Object system.Data.SqlClient.SqlCommand($sql,$sourceConnection)
        }
        else
        {
            $commandSourceData  = New-Object Oracle.DataAccess.Client.oracleCommand($sql,$sourceConnection)
        }
        $reader = $commandSourceData.ExecuteReader()
        $bulkCopy.DestinationTableName = $tableName
        $bulkCopy.WriteToServer($reader)
    }
    catch
    {
        $ex = $_.Exception
        Write-Host "Write-DataTable$($connectionName):$ex.Message"
    }
    finally
    {
        $reader.close()
    }
}
$sourceConnection.close()
$bulkCopy.close()

((get-date) - $start).TotalSeconds

If I understand you correctly:

You'd use BatchSize to break the load into chunks eg 10k rows
UseInternalTransaction determines if each batch is a transaction

So:

BatchSize = 0 -> UseInternalTransaction is irrelevant
BatchSize > 0 -> Apply UseInternalTransaction is specified

FYI, to monitor a SQL Server load in a non-blocking fashion

select count(*) from Mytable WITH (nolock)


How to get history of mapping for following table data I have a history mapping table for UserId changes, where every time when UserId changes, a row for new UserId with old UserId inserted in the history table.
Below is the sample table and data:

UserIdNew | UserIdOld
---------------------
    5     |     1
    10    |     5
    15    |     10

The above data explains that UserId 1 has gone with following transition from UserId 1 -> 5-> 10 -> 15.

I want to query all the Old Ids for a give UserIdNew, how can I do it in a single query?

For this case if UserIdNew = 15, then it should return 1,5,10

System Version Tables in MariaDB-10.3 offers a range of features associated with versioning. This provides easy history viewing however its hard to trace changes.

Any table structure that preserves a unique unmodified key as the starting ID will make all required queries easier rather than resorting to CTE, procedures or other required recursive implementations.


I just deleted 19k hi-res images from a table column and have a few questions about shrinking We just moved to a new image-storage method for our application, which means we don't need to store image byte arrays in the database any longer.

To that end, I have deleted (via an UPDATE statement) about 19k byte arrays containing hi-res images (about 8 GB) from a column in a single table. Without the images, I do not expect the database to ever grow back to the size it is now. Again, please note that I did not delete rows here, but have only set field values to NULL.

Other Info:

The column type was VARBINARY
There was no index on the column.

Questions

Should I just go ahead and shrink the database, then rebuild indexes for that table?
Most scenarios I read involve shrinking after a DELETE statement. I didn't delete any rows, I only set the field value to null for all rows. Does this change anything?
The column is now deprecated and we may want to just delete it from the table; I believe this will require the table to be recreated. Should I shrink the database before or after I delete the column?

Edit:
Another possibility just occurred to me. With the image data gone, it might be better to just write a script to completely rebuild the entire database, without the column image column. Thoughts?

If the goal is to make backups smaller, shrinking the data file(s) will not help accomplish that goal in this case, because all of those pages that were holding that max data were LOB pages that should be deallocated (potentially requiring a rebuild).

So unless your drive is literally out of space, I don't see any reason to shrink the file from 8GB, unless you are 100% confident it will never grow that size again.

Note that when you shrink a data file you might actually increase fragmentation which could decrease read performance, impact memory usage, and reduce concurrency. The knee-jerk reaction there is to rebuild indexes, which fixes the fragmentation but increases the file size to make room for the new copies of the index. When the rebuild is done, those pages are freed up, but the data file doesn't magically shrink again, so you're back where you started, and have gained nothing.

If you delete 95% of the data, the backup size will go down, whether you shrink the MDF file(s) or not. An MDF file is just a container; its size does not affect the backup. The actual data affects the backup. More precisely, the number of pages allocated with any data affects the backup (which is why a table rebuild after a big delete might yield a difference in backup size).

This is easy to test for yourself, but keep in mind the impact you observe will depend on a lot of things, including data types, fragmentation, free space, and a host of other variables. Just look at the size of a full backup after each of these steps:

before you set the values to NULL.
after you set the values to NULL (and after dropping the column, maybe).
after rebuilding the table.
after shrinking the database.

My theory is that, in almost all cases I can think of, there will be no tangible difference between the backup size after 3. and after 4.

A different variable you might care about is the time it takes to restore that backup on another system, which will be dependent on the size of the MDF file(s) only in the following cases:

Instant file initialization is not enabled
The destination drive can not accommodate the current size

The first one is easy to fix, this is just a right you can grant to the service account. The second one should be easy to fix too: add disk space. There's no reason to sacrifice your primary system because your secondary system is inadequate.


What is an advantage or disadvantage for not upgrading database server? I have a legacy database server. The person who wrote it is long gone. There is no documentation at all just some comments in the code. Luckily, it is not that complicate but if I want to fix it (requests from users), it takes time to investigate all related database objects (tables, views, or stored procedures).

Recently, I received suggestions from our IT departments person that I should upgrade our database to a newer database version (2005/2008). Right now, it is SQL Server 2000. He said it will have better performance and security. I think if I upgrade to newer version, our old box (hardware) may not powerful enough for new database engine. Currently, according to users, the response time is acceptable.

I try to think all pro(s) and con(s). I prefer for not upgrade but I want to make sure that I don't miss anything.

Not upgrade

Better ROI (not pay for everything)

Upgrade

Pay for new server/license
Redo some works to get the same functionality
Better performance/security

Question is: What should I do? Upgrade or Not upgrade our database server.

If you don't:

it will be unmaintainable at some point because of OS, version, patch, whatever
hardware failure may force an upgrade at the wrong time

What you gain:

older code can be simplified with new constructs (eg ROW_NUMBER)
far better error handling (TRY/CATCH)
engine improvements: most queries will run quicker on a later version

MS don't support it anymore either


ORA-01157: cannot identify/lock data file 201 SQL>  select FILE_NAME,TABLESPACE_NAME from dba_temp_files;
                                   *

  ERROR at line 1: ORA-01157: cannot identify/lock data file 201 - see
  DBWR trace file ORA-01110: data file 201: '/oradata_nocg/temp01.dbf'

In the database I don't have '/oradata_nocg/temp01.dbf', I am not doing any operation related temp tablespace  Why it is not allowing run above select query. ?

Most likely this is in a database that was cloned from an other database and the tempfiles are not added. Oracle needs a functional temporary tablespace to be able to process joins and sorts. dba_temp_files is a view that does this.
Solution is to simply add a few tempfiles to the temporary tablespace.