Performance degradation while updating tables having 10s of millions of records I want to update tables ( my be 20-30 ) having 10s of millions of records each.

The problem is that it is taking too much time for the update process and also at that time CPU usage also goes very high. I want to do in such a way that it can not use much CPU while processing the data. If the processing time is increased then it will not be a problem for me but it should use limited CPU resources for processing ( updating ) the table. I am using PostgreSQL as database and server operating system is Linux.

Sample query of mine can be like this

UPDATE TEMP
SET CUSTOMERNAME =
  ( select customername from user where user.customerid = temp.customerid );

The first question is: Why is it important that you not use a lot of CPU time? The query will be bottlenecked on some resource; if you could introduce enough additional disk access, the CPU time used per second would go down, but would that really be an improvement?  What resource would you prefer to saturate?  Understanding why you have emphasized this might help guide people to providing an answer that you will find useful.

As suggested in a comment, your query may run faster with a join rather than a correlated subquery.  Something like this:

UPDATE temp
  SET customername = user.customername
  FROM user
  WHERE user.customerid = temp.customerid;

Another important thing to know is whether you want to update all rows in the table.  Are some of the values already correct?  If so, you will get a big performance boost by not updating the rows that don't need it.  Add AND temp.customername is distinct from user.customername to the WHERE clause.

If you limit the number of rows updated in each statement, and VACUUM ANALYZE after each UPDATE, you will avoid table bloat.  If the point of the desire to minimize CPU time is to avoid a performance impact on concurrent transactions, this would give you the opportunity to introduce a short delay (in the form of a sleep or something similar) before you start the next UPDATE of a set of rows.

Even better, why are you redundantly storing the information in the temp table rather than joining to it when needed?  (Sometimes there is a good reason; quite often there isn't.)


How to update integer primary key adding 1000? I have the following primary keys in my table: 1,2,3,4,5...

Is it possible to update these keys to: 1001,1002,1003,1004,1005... ?

UPDATE mytable SET `id`= (select `id` from mytable where id=??)+1000

Yes, you can do that, however if you need to do this regularly, then something is probably bad in your DB or software design.

The purpose of the primary keys is to identify the records. They should not have any additional information. This rule may be stepped over on the some optimization reasons, or in special cases (like DB merge, refactor) but that is all.

Furthermore, typically the primary keys are referred by external parts of your software (either by foreign keys from other tables, or by non-db parts of your software). Be careful, changing a primary key requires to change it everywhere where they are referred. The CASCADE parameter of the foreign keys may help a lot, if you are using MySQL.

Put this additional information in alternate columns, you will spare a lot of pain on the longterm with that.


Slow response from SELECT query I have 50 lac rows (i.e. 5 million rows) in student table.

I fire select * from student where s_name like('%something word%') limit 0,5.

I define indexing on s_name column.

That query takes 2 minutes or more sometimes.

I want quick response within 5 seconds or faster for that type of query.

How...?

I tried the below query also:

SELECT * FROM (select * from student  ORDER BY s_name LIMIT 0,10) as s
WHERE `status` = 'active' AND ( `s_name ` LIKE ('%a%') or `f_name ` LIKE ('%a%'))
ORDER BY s_name LIMIT 0,5

Code taken and adjusted from: search for something in the first 1000 records in the table

First of All indexes wont be used if used %keyword% , it should be keyword%.

after you do that add index on

index (status, s_name, f_name)


Declarative and Procedural Query Languages In the book Database System Concepts 6th Edition, Chapter 2 (Relational Algebra), it states that there are three formal query languages, the relational algebra, the
tuple relational calculus and the domain relational calculus, which are declarative query languages based on mathematical logic.
But then, on another page it states that relational algebra is procedural, whereas the tuple relational calculus and domain relational calculus are non-procedural.
Which of these is the right statement? Could you provide simple examples illustrating the difference?

The writing is poor. "Declarative" is informal & they use it it in two ways. The general meaning is, "describe a result (not a process)". But many descriptions of results have an obvious interpretation as a process. So those "describe a result & a process" whether or not they were intended to. The first says "the notations can be used to describe results without intending any particular process". The second says "the algebra has a procedural interpretation & the calculi don't".

But the latter statement is a myth. It is parroted received [pseudo-]wisdom. The algebra has an obvious procedural interpretation--we call operators on arguments. But the calculi do too. The calculi don't have an obvious procedural interpretation in terms of standard relational operators.

But the idea is that the calculi aren't used to request such procedures.

The calculi have a straightforward procedural interpretation in terms of standard relational operators where you mechanically convert them to "prenex normal form" then mechanically convert them to standard relational algebra. (Codd's reduction algorithm.) Also, they have an obvious procedural interpretation (not in terms of relational algebra) in terms of trying every possible tuple for membership in the result. (Like standard Tarskian semantics for predicate calculus.) Also, they have an obvious procedural interpretation in terms of an algebra with two simple but impractical operators added--a generalization of UNION (corresponding to OR) & an operator returning the tuples not in a relation (corresponding to NOT). (Already NATURAL JOIN corresponds to AND & PROJECTing out corresponds to EXISTS.) (Like Tarskian cylindric-algebra semantics for predicate calculus.)

Difference between Relational Algebra and Relational calculus
Procedural and non-procedural query language difference


Backup Restore, CPU usage and Extended Events I'm working on measuring the performance impact of Backups on CPU. I would like to capture all the backup operations and CPU usage. I'm running a weekly full backup, daily diff, and 15 min log backup. Are there any options in Extended Events we can use to capture CPU usage?
CREATE EVENT SESSION [Backup_Events_Trace] ON SERVER ADD EVENT sqlserver.backup_restore_progress_trace (ACTION(sqlserver.client_hostname, sqlserver.database_name))
    ,ADD EVENT sqlserver.databases_backup_restore_throughput (ACTION(sqlserver.client_hostname, sqlserver.database_name)) ADD TARGET package0.event_file (SET filename = N'L:\XE\Backup_Events_Trace.xel')
    WITH (STARTUP_STATE = OFF)
GO

You can fetch CPU utilisation by adding this event:
sqlos.scheduler_monitor_system_health_ring_buffer_recorded
Adding this to your Extended Events session will return CPU utilisation statistics (among others).
When you query the data, there are two fields - process_utilization and system_idle which represents the SQL process CPU utilisation and the idle CPU % respectively. By subtracting process_utilization from system_idle you get the non-SQL used CPU from the server.


Oracle data pump export / import errors I'm attempting to export from a physical Oracle 11.2.0.3.0 database to a amazon rds Oracle database.
I used the expdp and exported a full database and incurred no errors upon export.

I've moved the copy over to my server running SQL Developer in my AWS environment, and using the impdp command I incur a slew of errors.

I've seen snippets in my searches for a few things, but having tried suggestions, it seems that isn't the error.

c:\Windows\system32\> impdp c2sadmin@x.x.x.x:1521/DATABASE file=e:\export\full_export.sql log=e:\export\import.log

Running the above command I get the below.

Import: Release 11.2.0.1.0 - Production
Connected to Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
ORA-31626: job does not exist
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY"
ORA-06512: at "SYS.KUPV$FT", line 1009
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_LOGREP_UTIL
ORA-20900: RDS restricted DDL Found: ALTER VIEW SYS.ALL_CAPTURE
ORA-06512: at "RDSADMIN.RDSADMIN", line 367
ORA-06512: at line 2
ORA-04063: view "SYS.DBA_CAPTURE" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGPREP_UTIL"

I've tried different schemas and all end with the same error.
Looking AWS disabled the ... AS SYSDBA option with logging in, so I can't do that either.

I've seen some random web posts regarding datapump and RDS not playing nicely, but I can't get anything concrete.

Our DBA's have assured me that the data pump export worked flawlessly, as they could import it to another physical oracle database. So could this just be an RDS issue and not Oracle?

After hours of trying numerous things I've found this as to answer my question. On a tip of an Oracle Community discussion, I was told to run this below.

select comp_id, version, status, comp_name
from dba_registry
where comp_id='SDO';

This would tell me if I am or not running Oracle Spatial on my existing database that I ran the export from. Now, why I bring this up is I've asked numerous people in my organization if we're using spatial and numerous people have swore up and down we don't. Come to find out, the database is installed with spatial and that above sql script showed me that yes, it is running spatial.

RDS won't accept spatial data, even if there is no spatial data in it. So I'm in the process of installing an EC2 instance with 11gR2 and actually importing the data that way. This is why the stored procedures failed as above. I just don't know why our AWS team didn't catch this when I submitted my trouble ticket. Oh well.

Thanks for all of the help.


Some Ideas In Database Design I am creating a site where users can sell their products(let say a little version of eBay, just assumption). Concerning the database design am a little bit stuck and looking for some ideas how it is done in real world.
My initial thoughts

I wanted to create one database for each user (seller). But I realize that if let say 70,000 people (70,000 is just assumption) want to sell, then 70,000 databases must be created. I think this may not the right idea. Or?

I am also thinking of creating one database for all sellers. I don't know if it is the best thing to do.

Questions

My question is what is the best way of designing a database for the above purpose?

Sites like eBay, how do they design their database for such large people to sell? Do they use one database, or each user has his own database?
I have googled "eBay database design" but the results i got were not what i was looking for. The results were too high level(not how they designed their database).

Is it advisable to create for each user a sub-domain?

A pointer to where I can find how such systems are designed will be appreciated.
I hope someone can help me with some specific answers.
Thank you

As a general rule, and speaking very broadly, each "thing" in a database is represented by a row in a table. Each seller is a row in a table. Each purchaser is a row in a table.

The simple, obvious, and arguably wrong approach is to have one table of sellers and another table of purchasers. The problem is that one real-world human can be both a seller and a purchaser. In other applications, one real-world person might be a doctor, a patient, and a hospital board member, all at the same time.

The problem with those two tables is that an update to, say, the email address for me in the sellers table might not be matched with a corresponding update to my email address in the purchasers table. And, in fact, that's almost certain to happen, because the row that corresponds to me in the sellers table probably won't have the same primary key as my row in the purchasers table.

For a working solution to the general case, see this SO answer. In your case, you're only talking about people. If you want to model people, sellers, and bidders, you're probably looking for something along these lines. This kind of structure allows one person to be both a seller and a bidder. (I think bidder is a better word than purchaser, if we're going along eBay lines.)

create table people (
  person_id integer primary key,
  full_name varchar(35) not null
);

create table sellers (
  person_id integer primary key references people (person_id),
  other_attributes_of_this_person_as_a_seller char(1) not null
);

create table bidders (
  person_id integer primary key references people (person_id),
  other_attributes_of_this_person_as_a_bidder char(1) not null
);


MySQL MVCC implementation The question is about the behavior of simultaneous SELECT and UPDATE in MySQL InnoDB table:

We have a relatively large table which we periodically scan reading several fields including a field named, say, LastUpdate. During the scan we update previously scanned rows. Updates are batched and performed in a background thread - using a different connection. It is important to note that we update rows that have already been read.

Three questions:

Will InnoDB will save previous versions of the updated rows since the SELECT is still in progress?
Would using READ-UNCOMMITTED for the SELECT help?
How can I confirm that InnoDB saves or does not save previous versions of the modified rows in its redo-log.

InnoDB always saves the previous version of the row. Everything is copy on write therefore every update is a select -> copy -> write. For how long it is being kept is dependent on

the open transactions (obviously as long as you have an open transaction that needs to be able to see an old version it cannot be removed)
how busy your server is (purge lag)

Purge behaviour had been improved a lot in the recent version. Look at dev.mysql.com for more info if you're interested.
I'm not sure what you mean by help. You read first and update next so I don't see too much benefit. You most likely don't want to go below READ-COMMITTED isolation level. That will already eliminate a lot of locking issue (gap locks and next-key locking).
InnoDB redo log contains every change that happens in the innodb tablespace to be able to recover in case of a system failure. The format is: page pointer offset and the changed bytes. So to answer the explicit use case if the previous version is written to the undo space (which it is) then InnoDB will write that change to the log files too.

I hope this helps!

If you want to look into the details more I always recommend people to play with innodb_ruby that is a very handy tool to look into innodb files and understand the basic principles of operations.


What are the main differences between InnoDB and MyISAM? What are the main differences between InnoDB and MyISAM?

First major difference I see is that InnoDB implements row-level lock while MyISAM can do only a table-level lock. You will find better crash recovery in InnoDB. However, it doesn't have FULLTEXT search indexes until v5.6, as does MyISAM. InnoDB also implements transactions, foreign keys and relationship constraints while MyISAM does not.

The list can go a bit further. Yet, they both have their unique advantages in their favor and disadvantages against each other. Each of them is more suitable in some scenarios than the other.

So to summarize (TL;DR):

InnoDB has row-level locking, MyISAM can only do full table-level locking.
InnoDB has better crash recovery.
MyISAM has FULLTEXT search indexes, InnoDB did not until MySQL 5.6 (Feb 2013).
InnoDB implements transactions, foreign keys and relationship constraints, MyISAM does not.


A confusing thing said in the PostgreSQL manual: For some releases, manual changes may be required to complete the upgrade, so always read the release notes before upgrading.

Source: https://www.postgresql.org/support/versioning/

It's talking about some minor releases in the quoted sentence.

Doesn't this mean that it's impossible to automate updating of PostgreSQL, even for minor versions?
What kind of "manual steps" are required, and how often is that the case?

This is exactly the kind of thing which makes me scared of and unwilling to update software, even crucial core software like PostgreSQL. I'm 100% sure that I'm not the only one who just mentally blocks out the need to update software when we read remarks like this. The thought of manually having to keep track of every damn changelog is literally Hell to most of us.

I think the technical term for this sort of thing is "Covering your Behind".

All it's saying is that upgrades will work in the vast majority of cases but acknowledges that there's an outside chance that somebody, somewhere has got some convoluted set-up that means an extra step or two might be needed.  If the PostgreSQL team think that's a possibility for any given upgrade, then they'll tell us so and we can work out whether or not the problem case(s) apply to us and we can do the extra work, if necessary.

I would never contemplate completely automating database upgrades.
Databases are complex Beasties and can be "upset" by any number of things.  Having them "upset" in the middle of an upgrade is a really Bad Place to be.

Of course, you want to script up as much as you can, but you have to accept that you cannot cater for every possible eventuality.  You should always have somebody around during the upgrade, just in case.


Missing view privileges in USER_TAB_PRIVS I can't figure out how to view the grant privileges I've given users on views --

FIRSTUSER@SQL> create view remotetabletest
  2  as select * from A_TABLE where PKEY = '123456'
  3  /

FIRSTUSER@SQL> grant select on remotetabletest to USERB;
FIRSTUSER@SQL> exit

[... login as USERB ...]

USERB@SQL> select count(*) from FIRSTUSER.remotetabletest;

  COUNT(*)
----------
        1

USERB@SQL> select * from user_tab_privs where TABLE_NAME = 'remotetabletest';

no rows selected

Normally, if this were a table, querying user_tab_privs would return a line about 'GRANT SELECT', but for views I get nothing.

I also tried querying dba_tab_privs from SYS: nothing.

(Also, in reality, I'm creating the view over a DB Link, but that doesn't seem related to my problem here.)

Unquoted identifiers in SQL are implicitly converted to upper case, so the statement create view remotetabletest creates the view named 'REMOTETABLETEST', not 'remotetabletest'.

In other words, the following should return the expected result:

select * from user_tab_privs where TABLE_NAME = 'REMOTETABLETEST'


How come spatial objects can't be used in a VALUES statement? How come you can do,

SELECT *
FROM (VALUES
  ('foo')  -- or (1), etc.
) AS t(x);

But, not

SELECT *
FROM ( VALUES
  ( geography::Point(0,0,4326) )
) AS t(geog);

I was trying to answer this question, but had to create a temp table instead.

What result are you expecting? It seems to work OK for me:

SELECT *
FROM (VALUES
  ('foo')  -- or (1), etc.
) AS t(x);
GO

| x   |
| :-- |
| foo |

SELECT *
FROM ( VALUES
  ( geography::Point(0,0,4326) )
) AS t(geog);
GO

| geog        |
| :---------- |
| POINT (0 0) |

dbfiddle here


Oracle: What should be better, alter table CACHE or INDEXES? I have a database (700 tables / 400GB) running on a VM with 64GB of RAM on a physical server with 384GB of RAM, the database has some indexes on some table fields, but it hasn't a good performance.

That physical server was reserved to run databases only, so I could allocate as memory as necessary on it to get a good performance.

I've been reading about tuning and realized that I could put all non-securefile (blob) tables on memory using the CACHE or KEEP POOL parameter, or create a lot of indexes.

What should I do to make queries run faster on it? CACHE or INDEXES?

indexes and caching serve different purposes:

indexes allow you to read only a (hopefully small) part of the table.
Caching reduces the time it takes to read a block as you only need logical (in buffer) reads instead of disk reads.

The two mix very well together as Oracle would happily use a cached index if it fits the query and statistics.

For example reading 10 rows via index will be faster then scanning 40gb table in memory, and if the index is cached then even more so.


fill table A with random ID's from another table B I have two tables and I want update the column GebruikerID from table Topic with the ID of the table Gebruiker with random values.

You must also know the follow things:

Topic.GebruikerID references to Gebruiker.ID.
Not every ID is used in both tables.
The random number that must be generated exist in the table Gebruiker (column ID) and must be updated into the table Topic (column GebruikerID).
I use SQL server in an express edition.

Here are some images.

The relation between the tables

The ID's of the Gebruiker-table

You can assign two ROW_NUMBERs based on a random sort and join on them:

select
   t.id,
   g.id as gebruikerid
from
 (
   select id,
      (row_number() over (order by newid())
    % (select count(*) from gebruiker))+1 as rn -- number from 1 to n
   from topic
 ) as t
join
 ( select id,
     row_number() over (order by newid()) as rn -- sequential number from 1 to n
   from gebruiker
 ) as g
  on t.rn = g.rn
order by t.id

See fiddle

EDIT:

You can use the result of this SELECT to update the Topic table:

update t
set gebruikerid = dt.gebruikerid
from topic as t
join
 (
   select
      t.id,
      g.id as gebruikerid
   from
    (
      select id,
         (row_number() over (order by newid()) % (select count(*) from gebruiker))+1 as rn
      from topic
    ) as t
   join
    ( select id,
         row_number() over (order by newid()) as rn
      from gebruiker
    ) as g
   on t.rn = g.rn
 ) as dt
on t.id = dt.id;

fiddle


How can I restructure a table of points with repeating lengthy text ids? I have a dataset that looks like this:

4cd1c79bf6a87692@@@b6aa5eeb-8a83-433f-a0ea-6c72708abc1d,82,49.226215,9.23175,244,1531840519
4cd1c79bf6a87692@@@b6aa5eeb-8a83-433f-a0ea-6c72708abc1d,86,49.22584,9.229898,260,1531840525
4cd1c79bf6a87692@@@b6aa5eeb-8a83-433f-a0ea-6c72708abc1d,82,49.225726,9.22834,263,1531840530
ff9d6b955f214ff0@@@599e1d04-7fc4-4ce4-8c92-22e9d7ac6596,84,49.456019,11.026769,123,1531840479
ff9d6b955f214ff0@@@599e1d04-7fc4-4ce4-8c92-22e9d7ac6596,82,49.455454,11.02813,122,1531840484
ff9d6b955f214ff0@@@599e1d04-7fc4-4ce4-8c92-22e9d7ac6596,75,49.454935,11.029419,121,1531840489

Multiple tracks of cars of a car-sharing service. The first entry is a random ID that marks one rental. (I guess something like hash(user, car, RNG) ). Every row consists of

ID, speed, lat, lng, orientation (deg), epoch

...but the ID column takes up at least half of the storage. (Don't ask me why this format was chosen).

I want to keep the IDs, but I'm not sure how to do it. (In PostgreSQL with Django)

My idea is to use one table that only contains the ID strings and the second table that contains all the other information and a foreign key on the first table.

I just wonder if there is already a feature for this problem directly within PostgreSQL as I can imagine that multiple repetitions of a string in a column is a common problem.

Update:

I worked my way through the full data set, and found only 439252 unique ID Strings.
BigInt + @@@ + uuid: Thanks for pointing that out, however, I will not be able to exploit it as the string-format is not fixed throughout the whole data dump. (I had a manual look at the first thousand or so entries and they use it, but when I tried to implement the approach, I found other formats also.)

First, you shouldn't use those IDs in the database. To start off they're clearly a compound ID, with

bigint (hex encoded)
uuid

The @@@ is a text-token. That means right off the bat you can make your table substantially smaller very easy.

ALTER TABLE foo
  ADD COLUMN id_uuid uuid,
  ADD COLUMN id_num bigint;

Now you need to populate it. (essentially we make sure you have 16 characters, prepend an x and then cast it to a bigint by proxy of ::bit(64))

UPDATE foo
SET id_num =
    (
      'x'
      || lpad(
        (regexp_match(id, '(.*?)@'))[0]
        , 16
        , '0'
      )
    )::bit(64)::bigint,
  id_uuid = (regexp_match(id, '.*@(.*)$'))[1]::uuid;

After this you could drop the original text id column. That's what you should do when you commit to a direction.

Now you have two id columns, but one of them is 16 bytes, the other is 8 bytes. compared to one text column that is 55 bytes (plus varlena overhead which is like 6 byts for you).

What you really want to do though is rewrite the table with these two columns at the start because that'll impact physical layout and make a big difference. Google for PostgreSQL Tetris for the reasons there..

At the point that you have two ids, and you can start to understand the data, the questions emerge.

Are both ids necessary, can I drop one and will it make it make difference
If they're both neccessary, can I store both of them as external vendor ids in a table and assign my own simplier int4 ids?

Depending on the answers to those questions you may want to structure some triggers so on insertion something (like a function you create) takes a text argument, decodes it into a composite type of (uuid,int) and assigns them to the new columns in one pass.

Moreover, you have another massive question here. This is a spatial problem. You have GPS coordinates. If you're talking about billion of rows, you may consider using a table like this until the routes are complete, and then migrating to a GIS solution with LINESTRINGS and PostGIS. Then you can map the routes in the database, and find what roads were traveled, how many miles the routes were, and place them on a map (or do other fancy goodness). The GIS solution makes the points columnar and gives you the ability to index them too.

You may want a consultant.


Calculate reverse running total I'm trying to get rid of using cursors in my project (SQL Server 2012). And I need to prepare query which calculates reverse (with subtraction) running total.

Let me explain on example.

I have the following data set:

Id  AppId   CaseId  Amount  Balance
1   2       123     16.48   43.29
2   2       123     5.01    43.29
3   2       123     2.25    43.29
4   4       123     16.48   43.29
5   4       123     5.01    43.29
6   4       123     2.25    43.29
7   10      789     2       10
8   11      789     4       10

I need to get following data set:

Id  AppId   CaseId  Amount  Balance Total
1   2       123     16.48   43.29   25.81
2   2       123     5.01    43.29   20.8
3   2       123     2.25    43.29   18.55
4   4       123     16.48   43.29   2.07
5   4       123     5.01    43.29   -2.94
6   4       123     2.25    43.29   -5.19
7   10      789     2       10      8
8   11      789     4       10      4

I've tried to use LAG and SUM() OVER() but I can't understand how to use it correctly.

Could you please help me with it?

You can use a combination of MIN() OVER() and SUM() OVER():

SELECT *,
       MIN(Balance) OVER(PARTITION BY CaseId) -
       SUM(Amount) OVER(PARTITION BY CaseId ORDER BY Id) Total
FROM dbo.YourTable
ORDER BY Id;

Here is a demo of it.


Default Value Of Table In my example syntax below I want the field delayed to be the difference in days between gotitdate and dunitdate  I know that this syntax will get me the result, and I was going to set this up as an OnInsert() and a AfterUpdate() trigger however my two fields are not required!  I can not alter table structure to make them be required.

Question at hand, how can I use this update syntax to account for a null date, to update to 0 if either field is null or update to the DateDiff() if both fields are not null?  And as always, if there is a better approach for this methodology I am open to suggestions:

Create Table #Moofasa
(
  gotitdate date
  ,dunitdate date
  ,delayed int
)

Insert Into #Moofasa (gotitdate, dunitdate) Values
('07/01/2016','07/05/2016')
,('07/01/2016','07/04/2016')
,('07/04/2016','07/08/2016')

Update #Moofasa
Set delayed = DateDiff(d, gotitdate, dunitdate)
WHERE delayed is null

Select * from #Moofasa

--Drop Table #Moofasa

A computed column would solve the issue and probably be better than having triggers:

CREATE TABLE #Moofasa
(
  gotitdate DATE,
  dunitdate DATE,
  delayed AS COALESCE( DATEDIFF(day, gotitdate, dunitdate), 0)
) ;

Tested at: rextester.com:

INSERT INTO #Moofasa
  (gotitdate, dunitdate)
VALUES
  ('2016-07-01', '2016-07-05'),
  ('2016-07-01', '2016-07-04'),
  ('2016-07-04', '2016-07-08'),
  (NULL,         '2016-07-09'),
  ('2016-07-09', NULL),
  (NULL,         NULL)  ;

SELECT *
FROM #Moofasa ;

gives:

gotitdate   dunitdate   delayed
01.07.2016  05.07.2016  4
01.07.2016  04.07.2016  3
04.07.2016  08.07.2016  4
NULL        09.07.2016  0
01.07.2016  NULL        0
NULL        NULL        0


Clone / Copy Oracle 10g & 11g Hi I am absolute begginer in Oracle domain. I would like to learn way how to clone copy oracle database to som backup, use this backup on another server.

Oracle is installed on Windows server 2003.

I have some question:

When I need copy oracle DB I must shutdown service?
I found many examples but I don’t know which is suitable for beginner.
It exist som graphics Tool for cloning Oracle DB?

Welcome to the world of Database Administration... and good luck. You're going to want to read up on as much Oracle documentation as you can, as well as other good technical sources (O'Reilly has always been good), and subscribe to lots of Oracle blogs.

I'll answer your questions here, but you're really going to want to get a solid foundation in RMAN ( For 10gR2: http://www.oracle.com/pls/db102/portal.portal_db?selected=4 ).

1.Do you have to shutdown your Oracle DB when you want to make a copy/clone of it?

Yes and no. It depends on if your database is in archivelog mode. If the database is archiving its logs, the backup can be done while your database is online, though you may notice some performance degradation during the backup, so it is still a good idea to schedule the backup during a non-critical time of day. If the database is not archiving the logs, then you must shutdown the database cleanly in order to make a copy of it (any other way will result in a corrupt/incomplete restore).

2.A good beginner example

This is hard to do without knowing your environment. There are various RMAN commands that will happily clone a database on the same server, but when you get into moving a clone from one server to another, you have to go a different route. So without knowing your environment, I can't really tell you a good example.

That said, essentially your goal is to do the following:
 - Get a good copy of all your datafiles
 - Get a good copy of all your archive logs
 - Get a good copy of your database parameters and control file
 - Create a new database with the settings from your old database (or reuse an existing database)
 - Copy over your datafiles and archive logs
 - Restore from your controlfile, and recover until there are no archives to process.

RMAN does a lot of the work for you with some fairly simple commands, so I suggest learning how to use it effectively, but you can always do the hard work without it. (For a long time at  a previous workplace, we did this with some shell scripts and such. Fun it was not, and was easy to screw up.)

3.Does a GUI exist for cloning an Oracle Database?

I think the thing that comes closest would be Oracle Enterprise Manager (GRID). It offers several automated backup/restore options as well as cloning features. Keep in mind, however, that it uses RMAN under-the-hood, and you should never use a tool blindly without understanding what's going on underneath. Sooner or later, you'll need to delve down into the command line because the GUI won't do what you want it to do, and without a good understanding, you'll be stuck.


Query Takes more time in SQL Server 2008 I have a table in SQL Server 2008 with 553,386 rows and every day it is increasing by about 400 rows. Now when I full scan of the table (Select * from) it is taking 23 sec. Is there any solution to speed up the query?

My PC configuration is, CPU: Intel Core 2 Duo 2.0 GHz, RAM: 4GB, OS: Windows 7 32 bit

Thanks in advance!

Table structure is given below:

USE [dbCPFL_Test]
GO

/****** Object:  Table [dbo].[PriceData]    Script Date: 10/04/2013 19:56:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PriceData](
    [TradeDate] [date] NOT NULL,
    [Ticker] [varchar](50) NOT NULL,
    [ClsPRC] [float] NOT NULL,
    [No_of_Share] [float] NOT NULL,
    [TradeStatus] [char](1) NOT NULL,
    [Adj_MKTCap] [float] NOT NULL,
    [deList] [char](1) NOT NULL,
 CONSTRAINT [PK_PriceData] PRIMARY KEY CLUSTERED
(
    [TradeDate] ASC,
    [Ticker] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Sure; upgrade your hardware.  Note that rendering results in Management Studio is notoriously slow, so keep that in mind if that is your performance benchmark tool.  However ...

The big question I have is: why do you care?  I'm hard pressed to come up with a legitimate reason to return the entire 550k record set.  You're not going to display that much on any screen in an application.  No one would ever view it.  If you're returning it for post-processing an aggregation or something of the sort, you should be aggregating it in SQL, then returning it.  If you are dumping it in an ETL process, long queries basically come with the territory (unless you're clever and come up with an incremental Extraction).


Restoring differential backup creates DEFUNCT log file? Here is my problem.  I'm trying to move a database to a new server via a full restore, then cutover with a quick differential backup/restore.  I can do a full restore without a problem, but when restoring the differential backup, I get the following warning:

  Msg 3127, Level 16, State 1, Line 1 The file 'Database_Log2' of
  restored database 'DatabaseName' is being left in the
  defunct state because the database is using the simple recovery model
  and the file is marked for read-write access. Therefore, only
  read-only files can be recovered by piecemeal restore.

The database restores and is considered online, but any backup operation fails due to this DEFUNCT file with the following error:

  Msg 3636, Level 16, State 2, Line 1 An error occurred while processing
  'BackupMetadata' metadata for database id 10 file id 6. Msg 3046,
  Level 16, State 2, Line 1 Inconsistent metadata has been encountered.
  The only possible backup operation is a tail-log backup using the WITH
  CONTINUE_AFTER_ERROR or NO_TRUNCATE option. Msg 3013, Level 16, State
  1, Line 1 BACKUP DATABASE is terminating abnormally.

If I do a RESTORE FILELISTONLY on the full and differential both give me the same output, which matches what I see from sys.database_files on the source database.  Server is SQL2012 SP1, on Developer edition.

I can do a full backup, and immediately afterward do a differential, and restore these files to a different database on the same server and see the exact same problem, so there is something with how the differential is created that is causing this.  If I restore the full backup WITH RECOVERY there is no problem.  I do not know if this file used to exist on this database, but it is entirely possible this file used to exist and was deleted a long time ago.  If I query sys.database_files on the restored database, the DEFUNCT file has a value for drop_lsn, which seems to confirm this.  Currently in the source database there is only one filegroup (PRIMARY), 4 data files and one log file.

Any ideas?

Here are the steps to reproduce this, tested on SQL 2012 SP1 Developer Edition.  This does not occur on SQL 2008.  To summarize, a database created in SQL 2012 while the model database is in SIMPLE recovery, that has a full backup taken while an extra log file exists, cannot create usable differential backups if that extra log file is ever deleted.

ALTER DATABASE [model] SET RECOVERY SIMPLE
GO
CREATE DATABASE [DefunctTest]
GO
ALTER DATABASE [DefunctTest] ADD LOG FILE ( NAME = N'DefunctTest_log2', FILENAME = N'D:\DefunctTest_log2.ldf' , SIZE = 25600KB , FILEGROWTH = 10%)
GO
BACKUP DATABASE [DefunctTest] TO DISK = 'D:\DefunctTestPostLogFile.bak' WITH INIT
GO
ALTER DATABASE [DefunctTest]  REMOVE FILE [DefunctTest_log2]
GO

BACKUP DATABASE [DefunctTest] TO DISK = 'D:\DefunctTestFull.bak' WITH INIT
GO
BACKUP DATABASE [DefunctTest] TO DISK = 'D:\DefunctTestDiff.bak' WITH DIFFERENTIAL, INIT
GO
--Show that the backups only have the one log file.
RESTORE FILELISTONLY FROM DISK = 'D:\DefunctTestFull.bak'
RESTORE FILELISTONLY FROM DISK = 'D:\DefunctTestDiff.bak'
GO
RESTORE DATABASE [DefunctTest2] FROM DISK = 'D:\DefunctTestFull.bak' WITH
MOVE 'DefunctTest' TO 'D:\DefunctTest2.mdf',
MOVE 'DefunctTest_log' TO 'D:\DefunctTest2_log.ldf', REPLACE, NORECOVERY
GO
--This restore will have the error.
RESTORE DATABASE [DefunctTest2] FROM DISK = 'D:\DefunctTestDiff.bak' WITH RECOVERY
GO

USE [DefunctTest2]
SELECT * FROM sys.database_files
GO

I submitted a Connect item for this bug here.  The only way I've been able to remove this defunct file is to detach the database, and re-attach with ATTACH_REBUILD_LOG.

UPDATE: The bug that creates this scenario in my repro script seems to have been fixed by this KB: https://support.microsoft.com/en-us/kb/2830400.  From the comments it appears an additional fix is available for SQL2012/2014, the scenarios seem very similar:  https://support.microsoft.com/en-us/kb/3009576


Database design - load multiple excels daily My first post ever :) Apologies if I am not so clear with my question - here I go...

I have a design problem from database schema perspective.

My use case is following:

On a daily basis, system will receive around 30 Load files on a daily basis. These files will be basically excel files - with rows and columns. Each file will at most be related to one other file in terms of data contained. For other files, they might at all not be dependant on other file data. We're thinking of using SQL Server to contain this data. This will be loaded into database using a programming language.

A plain relational Database approach would be:

  Create around 20 Tables (As some files are related to other file(s)).
  Every table will have all the fields corresponding to the file(s) it has been created for (fields in the excel file)
  On a daily basis, load the data of these files into the corresponding tables.

Each file will load 10 KB to 15 MB of data per day. Also, there can be multiple iterations of loading data from these files in a day (Max 24 - hourly once). I am dreading pushing so much data into database with above proposed structure, as it will become too loaded in a few days.

If I think of non-relational, I need to also process/clean/filter this data once it is loaded (I might want to create a stored procedure) - so that I can render useful data on UI - I think it will be more complicated on no-sqls due to non-availability of joins.

Moreover, my files are fixed and I have a structure. To my understanding - NoSqls are more apt when your data doesn't have any fixed format.

Can somebody please suggest what kind of database & the design would be good for this use case? I don't want to create 20 tables for 20 files :(

If the data needs to be clean and relational before being presented in your UI, I'd use SSIS to handle reading, cleaning, inserting your data into SQL Server, handling rejects, and archiving the input files.

If your data does not need to be clean/relational before presenting in the UI, then a NoSQL solution might work for you.

By the way, Excel is horrible to work with as an input file format.  You might want to see if you can receive the data as CSV or some other flat file format.


SQL Left Join on First Match Only I've got two tables of data, one at a person level and one at a district level.  I'd like to get a query that adds the district information to only the first person it finds in the district and leaves the rest null. The person table looks like:

IDNo    FirstName   LastName    DistrictID    SignUpDate
----------------------------------------------------------
123     bob         smith       1             1/19/2017
234     john        willis      1             1/18/2017
456     john        doe         2             1/17/2017
678     jane        doe         2             1/16/2017
890     jeff        bridges     3             1/19/2017
135     bill        doe         3             1/19/2017
246     sally       abby        4             1/14/2017
357     michael     jordan      4             1/12/2017

And the district table is:

DistrictID    DistrictName   BigDistrict
-------------------------------------------
1             district1      big1
2             district2      big1
3             district3      big2
4             district4      big2

What I'd like to end up with:

IDNo    FirstName   LastName    DistrictID     DistrictName     BigDistrict
-----------------------------------------------------------------------------
123     bob         smith       1              district1        big1
234     john        willis      1              (NULL)           (NULL)
456     john        doe         2              district2        big1
678     jane        doe         2              (NULL)           (NULL)
890     jeff        bridges     3              district3        big2
135     bill        doe         3              (NULL)           (NULL)
246     sally       abby        4              district4        big2
357     michael     jordan      4              (NULL)           (NULL)

I think this should solve the question.

I've numbered Person table, partitioning by DistrictID and ordered by SingUpDate and IDNo.

IDNo    FirstName   LastName    DistrictID    SignUpDate   Row Number
---------------------------------------------------------------------
123     bob         smith       1             1/19/2017         2
234     john        willis      1             1/18/2017         1
456     john        doe         2             1/17/2017         2
678     jane        doe         2             1/16/2017         1
890     jeff        bridges     3             1/19/2017         1
135     bill        doe         3             1/19/2017         2
246     sally       abby        4             1/14/2017         2
357     michael     jordan      4             1/12/2017         1

Then adds District information to all rows where row number is equal 1.

This should works for MySql:

select pn.IDNo, pn.FirstName, pn.LastName, pn.DistrictID, pn.SignUpDate,
       case when pn.rn=1 then d.DistrictName else '' end as DistrictName,
       case when pn.rn=1 then d.BigDistrict else '' end as BigDistrict,
from
    (select
           @rn := case when @district = DistrictID then @rn + 1 else @rn := 1 end as rn,
           @district := DistrictID as DistrictID,
           p.IDNo, p.FirstName, p.LastName, p.SignUpDate
    from
        (select @rn := 1) i,
        (select @district := DistrictID as DistrictID, IDNo, FirstName, LastName, SignUpDate
         from Person order by SignUpDate , IDNo) p
    ) pn
    inner join District d on d.DistrictID = pn.DistrictID

And this for other DBMS that allows CTE:

WITH p AS
(
    SELECT IDNo, FirstName, LastName, DistrictID, SignUpDate,
           ROW_NUMBER() OVER (PARTITION BY DisctictID ORDER BY SignUpDate, IDNo) RN
    FROM Person
)
SELECT IDNo, FirstName, LastName, DistrictID, SignUpDate,
       CASE WHEN RN = 1 THEN d.DistrictName ELSE '' END AS DistrictName,
       CASE WHEN RN = 1 THEN d.BigDistrict ELSE '' END AS BigDistrict,
FROM p
     JOIN District d ON p.DistrictID = d.DistrictID
ORDER BY p.IDNo;


SQL Server 2008 R2 DB grows overnight and slow I am running a SQL Server 2008 R2 SP2 DB. I have a DB that was running around 19 GB in size and three days ago grew to 34 for no good reason. We typically have about 100 MB growth in the DB every month or so, so this jump up by 15 GB is unexpected. At the same time I started seeing SQL Server eating up more and more RAM from the server. It's typical to see it run at around the 6-8 GB range but all of sudden its taking up to 24 GB of the 32 GB the server has. Again unexplained. I have rebuilt all the indexes, did a FILE shrink (not a DB and never a DB), did a full backup and nothing helped. I was forced to set MaxMemory limit at 16 GB to stabilize things but I have no clue what else I can do. The only change that was ever made over the last week was that a login name that was part of a Domain Controller (Admin account) was removed from SQL Server and replaced with a local Admin account.

I don't have performance states to share at the moment but wondering what could cause the sudden bloat in the DB and what can I look into or do about it? This DB has been running for 4 years without one single issue except for now. I am not a DBA so any help would be appreciated.

I don't have answer but would like to clear few things that would help

  DB is 34GB, TL is 1.2 GB, 10% for both

There was surely something happened in database it cannot grow on its own. The first thing you should do is change autogrowth setting you have set autogrowth in percentage(10 %)  for both data and log files this IMO would have forced database to grow unnecessarily. You can refer to Autogrowth settings and how to configure it and configure optimum value for this preferably in MB.

  It's typical to see it run at around the 6-8 GB range but all of sudden its taking up to 24 GB of the 32 GB the server has. Again unexplained.

What is current max server memory setting and please dont change it to lower value it will cause issues for you. Its normal for SQL server to utilize all memory if allowed to use via max server memory setting.


How can I export Query Store data? I have Query Store (QS) running on a SQL 2017 instance.  Currently at RTM, with RTM CU13 currently in test, to be applied on prod in next month's patch window.

While most queries and reports return results quickly with little to no impact, anything thing I try to look at around waits is problematic. CPU usage climbs from 20 to 80 percent and stays there for minutes until I kill it.  This is 24/7 production system so if I really want to look at QS waits I am going to need to do it someplace else.

The database is 150GB with 1000MB of space for QS.  I have a sandbox with 10GB of space so if I could get the QS data out, I could play with it there.

I looked around and I am not finding how to do this.  The best I found was is this sql.sasquatch 2016 post with a 2016 answer by Erin Stellato

  At present, there is no option to export and/or import Query Store data, but there is a Connect item to vote for: https://connect.microsoft.com/SQLServer/feedback/details/2620017/export-query-store-tables-separately-from-the-database-tables

Note: The link goes to a redirect "Microsoft Connect Has Been Retired" Looks like the actual link should be to https://feedback.azure.com/forums/908035-sql-server/suggestions/32901670-export-query-store-tables-separately-from-the-data

Looking at Microsoft, I find that most everything you might use to access the data is a View, Stored Procedure or Report. I am not seeing a way to just extract all the QS stuff out of database.

Examples of direct queries, use the views Example by Kendra Little I have toyed with the idea of just doing a Select * from the views and exporting the results to my sandbox.  But as I have not found anyone talking about it, I am not sure it is good idea.

Related

Best Practice with the Query Store
How Query Store Collects Data
Query Store Catalog Views
Query Store Stored Procedures
Monitoring performance by using the Query Store

Additionally  I would like to be able to keep the Query Store results from pre-CU13 to use as a baseline to compare post CU13.

Edit after first answer and edits to same
Recent edits to the answer by jadarnel27 adds good info, but I don't care about the user interface, I want to be able to query the data without altering the database or impacting performance. As a secondary goal I would like to be able to archive the QS data, so I can review previous performance (i.e. before and upgrade, but after the old QS data would have purged off)

First of all, you might be able to get acceptable performance with queries directly against the query store catalog views by updating stats, adding query hints with plan guides, or changing the database compatibility level / CE.  See the answers from Forrest and Marian here:

Never ending Query Store search

If you're on SP1 or greater, the simplest approach would be to use DBCC CLONEDATABASE - which includes statistics, query store data, and schema objects - but none of the actual data from the tables.

Otherwise, for exporting, one approach would be a simple SELECT...INTO from the query store views to the "sandbox" database.  These are the relevant views.

The basic approach would be like this:

SELECT * INTO Sandbox.dbo.query_store_runtime_stats FROM sys.query_store_runtime_stats;
SELECT * INTO Sandbox.dbo.query_store_runtime_stats_interval FROM sys.query_store_runtime_stats_interval;
SELECT * INTO Sandbox.dbo.query_store_plan FROM sys.query_store_plan;
SELECT * INTO Sandbox.dbo.query_store_query FROM sys.query_store_query;
SELECT * INTO Sandbox.dbo.query_store_query_text FROM sys.query_store_query_text;
SELECT * INTO Sandbox.dbo.query_store_wait_stats FROM sys.query_store_wait_stats;

The nice thing about this approach is that:

you'll only get the data you need (1000 MB)
you can add indexes to support your reporting queries, because these are actual tables
they won't have the unusual memory scanning behavior that leads to poor performance against the actual views (again because they are actual tables)

Note: the SELECT...INTO queries shouldn't drive up CPU like the built-in query store reporting queries, because they won't have the problematic joins that cause repeated access to the in-memory TVFs

you can keep different versions of the data (for different CU levels, etc) by changing the table names, or adding a column to the tables that indicates the data and / or version of SQL Server that was used for that import

The "con" of this approach is that you can't use the query store user interface.  A workaround for that would be to use profiler or extended events to capture the queries being executed by the user interface for the specific reports you need.  You could even do this capture in a non-prod environment, as the queries should be the same.

Warning: this is potentially a really bad idea.  There's a reason you can't normally write to these tables.  Special thanks to Forrest for mentioning the possibility to me.

If you really want to be able to use the user interface, you can actually load the base Query Store tables with data while connecting via the DAC.  Here's what worked for me.

Reminder: you have to be using a DAC connection to do this, otherwise you'll get errors related to the sys.plan_persist_* tables not existing

USE [master];
GO
CREATE DATABASE [Sandbox];
GO

USE [YourSourceDatabaseWithTheQueryStoreInfo];
GO

BEGIN TRANSACTION;

INSERT INTO Sandbox.sys.plan_persist_runtime_stats SELECT * FROM sys.plan_persist_runtime_stats;
INSERT INTO Sandbox.sys.plan_persist_runtime_stats_interval SELECT * FROM sys.plan_persist_runtime_stats_interval;
INSERT INTO Sandbox.sys.plan_persist_plan SELECT * FROM sys.plan_persist_plan;
INSERT INTO Sandbox.sys.plan_persist_query SELECT * FROM sys.plan_persist_query;
INSERT INTO Sandbox.sys.plan_persist_query_text SELECT * FROM sys.plan_persist_query_text;
INSERT INTO Sandbox.sys.plan_persist_wait_stats SELECT * FROM sys.plan_persist_wait_stats;
INSERT INTO Sandbox.sys.plan_persist_context_settings SELECT * FROM sys.plan_persist_context_settings

COMMIT TRANSACTION;
GO

USE [master];
GO
ALTER DATABASE [Sandbox] SET QUERY_STORE = ON (OPERATION_MODE = READ_ONLY);

Note: if you're on SQL Server 2016, you'll need to remove the line about wait stats - that catalog view wasn't added until SQL Server 2017

After doing that, I was able to use the Query Store UI in SSMS to view info on the queries from the source database.  Neat!

It's important to load the data into the Sandbox database with Query Store off, and then turn Query Store on in read only mode.  Otherwise QS ended up in an error state, and this was written to the SQL Server error log:

  Error: 12434, Severity: 20, State: 56.
  The Query Store in database Sandbox is invalid, possibly due to schema or catalog inconsistency.

I also noticed that this doesn't work if there are in-memory OLTP (Hekaton) tables in the source database.  No matter what I do, Query Store ends up in the "Error" state with this message in the error log:

  Error: 5571, Severity: 16, State: 2.
  Internal FILESTREAM error: failed to access the garbage collection table.

You may be able to work around that by adding a memory-optimized filegroup to the Sandbox database, I haven't tried that yet.


Looping through JSON and insert data to Postgres DB Below is how i have my data at the moment. How would i achieve in postgres with Postgresql to get all those "class_name" and "id" values and store them on their own rows in the database. I can store the data as JSON or JSONB, that doesnt matter but im having trouble looping through all the cases and getting all the values.

I have tried json_each and combination of jsonb_query_path and json_array_elements but only have managed to get only the first case in each suite.

Would appreciate any help here and i would prefer to do this with postgresql instead of python

 {
  "duration" : 6109.1104,
  "failCount" : 0,
  "passCount" : 4389,
  "skipCount" : 0,
  "suites" : [
    {
    "cases" : [
      {
        "class_name": "testname1",
        "id": 231
      },
      {
        "class_name": "testname2",
         "id": 233
      }
    ],
    "duration" : 0.012,
    "id" : null,
    "name" : "EventTest",
    "stderr" : null,
    "stdout" : null,
    "timestamp" : null
     }
   ]
 }

You need to combine two jsonb_array_elements() calls:

select c.cases ->> 'id' as id,
       c.cases ->> 'class_name' as class_name
from the_table
  cross join jsonb_array_elements(the_column -> 'suites') as s(suite)
  cross join jsonb_array_elements(s.suite -> 'cases') as c(cases);

Online example


How to only pick the first matched row in a one-to-many relationship? I have two tables A and B with one-to-many relationship.

For rows A1 and A2 in table A, the relationship is

A1 -> [B11, B12, B13]
A2 -> [B21, B22]

When I join A and B I get

A1,B11
A1,B12
A1,B13
A2,B21
A2,B22

Instead I want

A1,B11
A2,B21

What should I do? I don't know what this mapping is called.

Use analytics - see documentation or examples here eg

Tables

create table A ( c1 ) as
select 'A1' from dual union
select 'A2' from dual ;

create table B ( c1, c2 ) as
select 'A1', 'B11' from dual union
select 'A1', 'B12' from dual union
select 'A1', 'B13' from dual union
select 'A2', 'B21' from dual union
select 'A2', 'B22' from dual ;

When joining the tables, we get ...

SQL> select *
  2  from A join B on A.c1 = B.c1 ;

C1 C1 C2
-- -- ---
A1 A1 B11
A1 A1 B12
A1 A1 B13
A2 A2 B21
A2 A2 B22

Use FIRST_VALUE (without joining the tables)

select unique
  c1
, first_value( c2 ) over ( partition by c1 order by c2 ) fv
from B ;

C1 FV
-- ---
A1 B11
A2 B21


Can SQL Server's crash resilience be improved? We have PCs running SQL Server (2008 SP4 and 2016 SP1) which regularly lose power. Obviously, this sometimes leads to (index) corruption of the SQL Server database, which we need to restore afterwards.

I am aware that SQL Server is not designed for such scenarios and the correct solution is to fix the cause of the power loss (more on that below, if you are curious). Nevertheless, are there any tuning options in SQL Server that I can set to reduce the risk of database corruption on power loss?

Background: The "PC" is a Windows tablet mounted on a forklift. When the user turns off the forklift, the tablet loses power. We have tried to teach the users to properly shut down Windows before turning off the forklift, but failed (probably because just turning it off "works" most of the time). We are also currently investigating other options, such as adding a UPS which signals the tablet to shut down on power loss.

I am aware that SQL Server is not designed for such scenarios and the correct solution is to fix the cause of the power loss […]

Actually it's designed to deal with power loss, that's why there are things like write ahead logging (WAL) and crash recovery upon startup (or whatever you want to call it). One of the ways this is done is by choosing to not cache writes which it seems is what the tablet is doing, hence the corruption.

  Nevertheless, are there any tuning options in SQL Server that I can set to reduce the risk of database corruption on power loss?

No, SQL Server is doing what it should. You should look either outside SQL Server (windows settings for drive caching [which SQL wants to be off but we can't force you], hardware/firmware updates, etc.) or as Eric has said, buy an external power supply for relatively cheap which could solve the symptoms (the actual issue is probably some type of caching or battery backed write that isn't actually backed).


insert into all possible combination of arrays of strings I have a users table it has 3 columns

First Name |  Last Name  | Email

I have

first names = ["a1", "a2", "a3", "a4"]
last names = ["b1", "b2", "b3", "b4"]
email = ["e1", "e2", "e3", "e4"]

now i want to insert users table by

all combinations of the string arrays.

like below.

First Name | Last Name | Email
 a1        | b1        | e1
 a2        | b1        | e1
 a3        | b1        | e1
....
 a1        | b2        | e1
 a1        | b3        | e1
.....
 a1        | b1        | e1
 a1        | b1        | e2

in this above way of all combinations.

You just need to do a cartesian product:

INSERT INTO mytable("First Name", "Last Name", "Email")
    SELECT * FROM (
        SELECT 'a1' AS firstname
        UNION SELECT 'a2'
        UNION SELECT 'a3'
        UNION SELECT 'a4'
    ) AS t1, (
        SELECT 'b1' AS lastname
        UNION SELECT 'b2'
        UNION SELECT 'b3'
        UNION SELECT 'b4'
    ) AS t2, (
        SELECT 'e1' AS email
        UNION SELECT 'e2'
        UNION SELECT 'e3'
        UNION SELECT 'e4'
    ) AS t3
;

(for mysql, replace " by ` or set sql_mode to include ANSI_QUOTES)

Alternate shorter syntax for PostgreSQL:

INSERT INTO table("First Name", "Last Name", "Email")
    SELECT * FROM (
        VALUES('a1'), ('a2'), ('a3'), ('a4')
    ) AS t1(firstname), (
        VALUES('b1'), ('b2'), ('b3'), ('b4')
    ) AS t2(lastname), (
        VALUES('e1'), ('e2'), ('e3'), ('e4')
    ) AS t3(email)
;

For PostgreSQL and from arrays:

INSERT INTO table("First Name", "Last Name", "Email")
    SELECT * FROM (
        SELECT unnest(ARRAY['a1', 'a2', 'a3', 'a4'])
    ) AS t1(firstname), (
        SELECT unnest(ARRAY['b1', 'b2', 'b3', 'b4'])
    ) AS t2(lastname), (
        SELECT unnest(ARRAY['e1', 'e2', 'e3', 'e4'])
    ) AS t3(email)
;


Grant Select on some columns for specific user I have a windows group with 20 people and all 20 people in the group were revoked access for some columns in the table. Now I need to add the select permissions for 2 of the users in this windows group. How can I do this?
Can I simply grant select on these columns directly to these 2 users instead of windows group?

Thank you

Yes, you give permissions only required users in SQL Server using GRANT like this

GRANT SELECT ON <schema>.<tablename>(<Col1>,<Col2>) TO [Domain\User]

For more info, refer Grant


How do you determine the direction of a relationship? Suppose I have the following relationship diagram:

How would you describe the relationship determined by the red line?

Reading from left to right you would say each order has only one customer so it could be described as a "many to one" whereas from right to left each customer can have many orders so you could say it is "one to many".

Without direction it seems to me to be futile to try and discuss a relationship yet I rarely see people say, it is a one to many (from customer to order) or many to one (from order to customer).

So my question is why is this the case (is it just implied from context?) or does the primary key have some relation, as in we wouldn't really store an order_ID in the customer table (I am not a DBA but I don't think this make sense from a design point of view) whereas we would have to store a customer as a foreign key in the order table otherwise we wouldn't know who bought the stuff, so would this give precedence to reading from right to left and we say the red line is a one to many relationship?

Any clarification would be great!

A Foreign Key always refers to a Unique Index or Primary Key.  So the "one" side is always the one where the related columns are a key.


What is the best SSIS Tasks for String Parsing database fields What is/are the best SSIS Tasks to use in order to search for string patterns.  This relates to standardizing address information.  My developer does not have a confident grasp on using Regular Expressions in his code.

There is no built-in address clean-up task in SSIS. However, there are plenty of Examples of regular expressions that could be implemented with a Script Task. They can be implemented in VB or C# and knowledge of such a transform is something that any ETL developer should be able to do, or learn to do. They don't need to memorize Regular Expressions or become an expert, but they should be able to create a basic implementation and test/verify the patterns created and shared by others.

This is a great professional development opportunity for your developer! I'd suggest giving them the time, support, and encouragement to expand their development skills with this... along with encouraging their own use of this site and search engines to navigate their way through a solution. Whatever you do and search for though, start by breaking the address down into component parts. You will find numerous US Postal Code validation regular expressions, that are relatively easy to comprehend and apply.

If you do not have the time or capability there are third party packages, like Task Factory, that have components that can address this need.

On the higher end of development skills (used to ensure a true address, not just standardizing text) is using a web service like Bing Maps to geocode/resolve addresses. Such a service should be able to not only re-fromat addresses to correct values, but correct or identify bad data issues that may exist in your data. This would be a much more advanced solution.


How to search double quotes in fulltext search in sql I have double quotes in my Keyword. How can I search this in my fulltext search query. I have this query

SELECT  top 10 K.[KEY], 10, K.[RANK]
FROM    CONTAINSTABLE(ProductKeywords, Keywords, '("19*") AND ( "<Cat>5" OR "<Cat>30" OR "<Cat>398" ) AND NOT "<Blocked>"' ) AS k

It works fine but when i have double quotes in my keyword like

SELECT  top 10 K.[KEY], 10, K.[RANK]
FROM    CONTAINSTABLE(ProductKeywords, Keywords, '("19"*") AND ( "<Cat>5" OR "<Cat>30" OR "<Cat>398" ) AND NOT "<Blocked>"' ) AS k

it gives this error

Msg 7630, Level 15, State 3, Line 1
Syntax error near '*' in the full-text search condition '("19"*") AND ( "<Cat>5" OR "<Cat>30" OR "<Cat>398" ) AND NOT "<Blocked>"'.

Since the content of 'Keywords' is interpreted by the RDBMS instead of being read literally, your code is vulnerable to SQL Injection . This is also why you get the syntax errors.

You must sanitize the content of 'Keywords'. There's an answer on Stack Overflow : here.


A good use case for Postgres Schemas? I'm developing a database for time series analysis of price bars for stocks.  The associated metrics ("technical indicators") will derive on as many as 10 successive layers of dependent set operations. Using Postgres 10.3.

There are two ways to derive a layer-1 metric "metric1" that becomes an input to most of the other metrics. The only difference between the datasets after the 1st layer of derivations is the metric1 value. The two datasets use the same set of table specs.

I want to to see how the two different derivations of metric1 impact the higher-level metrics, with the implementation goal of doing so with minimal coding and operational overhead.

My impression is that putting the datasets into separate Postgres schemas could meet the implementation goal.  However, all the example Postgres schema use cases I've googled relate to segregating data that remains separate, for privacy, or to manage different groups or data releases. I have not found examples where the intent is to compare derivations from one schema to those from another schema.

So two related questions: is there anything about a two-schema approach that would be problematic for this use case, and are there are other approaches that would be preferable ?

The development process has falsified the premiss of the question, i.e. each of the 2 derivations of metric1 have become inputs to derivations at several metric levels.

If this had not been the case, I'm now inclined to implement the original scenario via alternate views that duplicate query logic while being based on one or the other derivation of metric1.  This seems from my pov a simpler approach, in that it avoids an additional schema and instead adopts a view-naming convention where view names denote the particular metric1 derivation.


DB2 - Archive mode - large number of logs in logarchmeth1 folder maybe for someone this is begginner's questions. I read lot of documentation and set:

`LOGPRIMARY 6
 LOGSECOND 4
LOGFILSIZ 4096
logarchmeth1 Disk - C:\archive
 logpath C:\active`

Number of logs in C:\active is 6 which is ok. But current number of logs in C:\archive is 40. How is that possible? I thought that maximum number of primary logs in C:\archive will be 6 and then 4 LOGSECOND (if needed, they are not preallocated).

Allowed log space is breached but new logs are created constantly in C:\archive. How is that possible? I will be thankful if someone can explain me this. On this way I do not see how can I get Transaction log full (except if I run out of total disk space) because new logs in C:\archive are created.

I am beginner so please sorry for simple question I did not find answer on Internetenter code here.

When those files will be deleted after the new online backup or when?
logarchmeth1 is the destination for archive logs I set them on 6 for PRIMARY how then I have 40 logs? Thank you

When you enable archive logging in a database, you're telling DB2 that you want to retain a copy of all transaction logs that have been created.  LOGPRIMARY and LOGSECOND only control how many active log files can be in use in the active log path at any given time.

Please read the documentation on database logging for more details.


Cannot remotely access PostgreSQL on EC2 instance I was unable to access a remote AWS postgres database via things
like psql -h *EC2ExternalIP* -U *me*.  I would get:

psql -h *EC2ExternalIP* -U *me*
psql: could not connect to server: Operation timed out
Is the server running on host *EC2ExternalIP* and accepting
TCP/IP connections on port 5432?

same thing trying a simple telnet connection to this port:

curl telnet://*EC2ExternalIP*:5434
curl: (7) Failed to connect to *EC2ExternalIP* port 5434:
Operation timed out

and even though i could access other services (eg, apache) on this
same EC2ExternalIP.

So I poked around at threads like these:

How to connect to an remote PostgreSQL database on Ubuntu using pgAdmin3?
Connect to remote EC2 Postgresql database
https://askubuntu.com/questions/423165/remotely-access-postgresql-database/827358#827358
https://stackoverflow.com/questions/17770329/cannot-connect-to-postgresql-remotely-on-amazon-ec2-instance-using-pgadmin

and they helped me to ensure I'd made appropriate modifications to
postgresql.conf and pg_hba.conf; the right settings for you will
depend on your instance's security concerns.  But it is still not
working.

What should I do to make this DB accessible?

(self-answering in case someone else has these same symptoms!)

Then I stumbled upond Shubham Dipt's post on
"PostgreSQL on EC2 (Ubuntu) in AWS",
making me aware of the "Security group" layer AWS maintains.  It's
easy to specify a new one  via the AWS console, specifically adding  Custom TCP: 5432, Source: Anywhere and SSH TCP:22, Source: Anywhere rules for Inbound:

Then, added this security group to the instance, and it worked for me.


order by random meaning (postgresql) One possible way to select random rows in PostgreSQL is this:

select * from table order by random() limit 1000;

(see also here.)

My question is, what does order by random() mean exactly? Is it that somehow a random number is generated and it is taken as some kind of "seed"? Or is this special built in syntax, and in this place random() has a different meaning than in other contexts?

From some experimentation, the last explanation seems more plausible. Consider the following:

# select random();
      random
═══════════════════
 0.336829286068678
(1 row)

# select * from article order by 0.336829286068678 limit 5;
ERROR:  non-integer constant in ORDER BY
LINE 1: select * from article order by 0.336829286068678 limit 5;

ORDERY BY random() is not a special case.  It generates random numbers, one for each row, and then sorts by them.  So it results in rows being presented in a random order.

Rather it is ORDER BY <literal constant> which is the special case, but that special case only works with integers.  It throws the error you show for non-integers.  The special case is that it uses the integer to index into the select-list, and orders by that column from the select list.  This lets you both select and order by an expression, without having to repeat the expression in both places.


Why can a differential backup not be copy-only? I have noticed that copy only backups does not allow for differential backups.
What is the reason the copy-only backup option is disabled for differential backups?

It can be specified in TSQL but the documentation states

  COPY_ONLY has no effect when specified with the DIFFERENTIAL option.

There's no need for copy only differential backups because you can just take a normal differential backup.

Taking a differential backup doesn't break anything as each such backup is cumulative and contains all changes since the last full backup.

If you were to take a full backup without copy_only that would potentially be problematic as it resets the differential base (clears the bitmaps containing information about which extents have changed).


can my SQL Server application code using temporary tables be used for Postgres? I have a large application that connects to SQL Server and makes extensive use of temporary tables using the #table syntax.  I would like to convert this to connect to postgres, which does not accept that notation.  Is there a work-around, other than modifying all my code?

Postgres supports temporary tables but it does not support the #tablename syntax.  You can keep the structure but you will have to modify the code to the new syntax.  Unless you want to download the code for Postgres and modify that but that seems like unrecommended overkill.

http://www.sqlines.com/postgresql/statements/create_temporary_table

  CREATE TEMPORARY TABLE statement creates a temporary table that is
  automatically dropped at the end of a session, or the current
  transaction (ON COMMIT DROP option).

  Quick Example:

   -- Create a temporary table
   CREATE TEMPORARY TABLE temp_location
   (
      city VARCHAR(80),
      street VARCHAR(80)
   )
   ON COMMIT DELETE ROWS;


(Ola Hallengren) Deleting transaction log backups We're using Ola's script to run weekly full, nightly diff and hourly transaction logs. All the jobs are correctly creating their backups, and the full and diff jobs are cleaning up after a week, transaction logs are set to clean up after 48 hours (we backup the NAS that the files are stored on nightly so these files are still accessible if we need them, they're just out of the way).

I'm finding transaction logs on certain databases aren't cleaning up, ie when I look at the file-system I can find transaction logs from a month ago dating right forward to today. The only reason I could think for this  is if a more recent diff backup doesn't exists, but they do, and they've not been moved to a different directory. None of the jobs are recording any any kind of failure triggered manually or running from the schedule. The other jobs are running and cleaning up just fine, ruling out permission issues (I've also checked on the NAS, the SQL server agent has Full Control).

Current step being run by the log backup job:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'\\server\folder\', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 48, @CheckSum = 'Y', @LogToTable = 'Y'" -b

Here's the Diff job that cleans up just fine for comparison

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'\\servername\folder', @BackupType = 'DIFF', @Verify = 'Y', @CleanupTime = 192, @CheckSum = 'Y', @LogToTable = 'Y'" -b

Does anyone know  more about how the job checks for the presence of the a full or diff backup? Or any other reason this could be happening?

Thanks

I just noticed that you are only providing information on the differential and transaction log backup steps of the backup procedure. What is with the FULL backup?

Ola has implemented a fail-safe mechanism which will delete transaction log backups only if a FULL and/or DIFF backup is available.

  DatabaseBackup has a check to verify that transaction log backups that are newer than the most recent full or differential backup are not deleted.

Reference: SQL Server Backup (from ola.hallengren.com)

This means that if you don't have a recent FULL or DIFF backup, then the @CleanupTime parameter will have no effect. All the Transaction Log backups will be kept on disk, even though the CleanupTime has passed.

You might want to check if your FULL, DIFF and TLOG backups are consistent and available on disk. It is important to have all files available. sequence available.

Example of Files on Disk

Assuming it is Friday 3.30pm (what a coincidence) you should find the following files in your backup folder if everything is set up correctly:
(I have put a separator between the FULL and DIFF backups)

SA | 1900 | FULL
----------------
SU | 1800 | DIFF
----------------
MO | 1800 | DIFF
----------------
TU | 1800 | DIFF
----------------
WE | 1800 | DIFF
----------------
WE | 1800 | TLOG
WE | 1900 | TLOG
WE | 2000 | TLOG
WE | 2100 | TLOG
WE | 2200 | TLOG
WE | 2300 | TLOG
WE | 2400 | TLOG
TH | 0100 | TLOG
TH | 0200 | TLOG
TH | 0300 | TLOG
TH | 0400 | TLOG
TH | 0500 | TLOG
TH | 0600 | TLOG
TH | 0700 | TLOG
TH | 0800 | TLOG
TH | 0900 | TLOG
TH | 1000 | TLOG
TH | 1100 | TLOG
TH | 1200 | TLOG
TH | 1300 | TLOG
TH | 1400 | TLOG
TH | 1500 | TLOG
TH | 1600 | TLOG
TH | 1700 | TLOG
----------------
TH | 1800 | DIFF
----------------
TH | 1800 | TLOG
TH | 1900 | TLOG
TH | 2000 | TLOG
TH | 2100 | TLOG
TH | 2200 | TLOG
TH | 2300 | TLOG
TH | 2400 | TLOG
FR | 0100 | TLOG
FR | 0200 | TLOG
FR | 0300 | TLOG
FR | 0400 | TLOG
FR | 0500 | TLOG
FR | 0600 | TLOG
FR | 0700 | TLOG
FR | 0800 | TLOG
FR | 0900 | TLOG
FR | 1000 | TLOG
FR | 1100 | TLOG
FR | 1200 | TLOG
FR | 1300 | TLOG
FR | 1400 | TLOG
FR | 1500 | TLOG

Possible Reasons TLOG Files Aren't Deleted

Ola's Failsafe Mechanism

If a DIFF backup is missing (for example the DIFF from WE and TH) then no TLOG backups will be deleted from disk up until the last DIFF or FULL backup. In this example you would have TLOG backup files up until the DIFF backup on TUesday at 6pm (1800).

Solution

Ensure you have FULL and DIFF backups on disk.

"SQL Server Agent" Service Account Insufficient Privileges

If the SQL Server Agent (INSTANCE) Service Account doesn't have enough privileges on the directory it is deleting files from, then it will be unable to delete the TLOG backup files.

Solution

Ensure the Windows Account of the SQL Server Agent Service has access to the network drive.
Check that the Run As setting in the job step is set to the SQL Server Agent Service Account.

SQL Server Agent Jobs Not Being Logged in Msdb

There are cases then the job steps of jobs executing are unable to enter the details into the msdb job tables due to contention (locks, blocks, long running transactions). The tables required by the backup job are being locked by another process (another maintenance plan / other 3-rd party tools / cleanup jobs / a long running transaction) and might be determined as the victim of a deadlock and rolled back. The data is missing in the msdb database and because no FULL or DIFF backups exist, nothing gets deleted.

Ola checks this with the following part in the DatabaseBackup stored procedures:

    IF @CurrentBackupType = 'LOG' AND (@CleanupTime IS NOT NULL OR @MirrorCleanupTime IS NOT NULL)
    BEGIN
      SELECT @CurrentLatestBackup = MAX(backup_finish_date)
      FROM msdb.dbo.backupset
      WHERE ([type] IN('D','I')
      OR database_backup_lsn < @CurrentDifferentialBaseLSN)
      AND is_damaged = 0
      AND database_name = @CurrentDatabaseName
    END

This means that if the record for the previous DIFF or FULL backup was unable to be recorded in the msdb database, then the TLOG files will never be deleted.

Special Solution

Check that the FULL and/or DIFF backups of the database in question are recorded in the msdb database and verify that you don't have any long running transactions on the msdb database.
Reschedule (the/other) jobs to run at a slightly different times if you have multiple jobs running at the same time.

Good luck.


How can I keep the user permission on a table that is dropped and recreated? I drop, create and bulk insert a table weekly for a user, but the user's permission is removed from the table each time I do that. I right click on the table name and add them each time. Is there a way to keep their name in the permissions?

When you drop and re-create the table, it becomes a new object. If the principal is granted access to that specific object, all security related to it will be dropped as well.

If you truncate the table or delete from it, the security will be preserved. You'll need to slightly adjust your bulk import process.

Alternatively, you could run the import with elevated permissions, and have that process execute a GRANT SELECT for that principal. But I would recommend against that.


Can i get a SQL Server catch Updates to reflect the responsible objectid with a database trigger? I have a table that runs several stored procedures and stores the resulting rows in the target table [TestTable]

[TestTable] is first populated by the stored procedure [spInsertRowToTestTable] and then Updated by [spUpdatesRowInTestTableFirst] and [spUpdatesRowInTestTableLast]

I can capture the responsible procedure of initial insert in the table by using a Default Constatraint to set the @@PROCID. I would like to capture Updates in a similar way, without modifying existing procedures that result in updates to the row.

I have tried, but I have not been able to create a trigger or a similar solution that captures any subsequent Updates. The scope of the calling procedure aludes my atempts to catch it in a trigger and update the row.

Is there a straight forward way to do this?

See example code below:

--DROP SCHEMA test;
--GO
--DROP TABLE test.TestTable;
CREATE SCHEMA test;
GO

CREATE TABLE test.TestTable
    (
      Column1 NVARCHAR(20) NULL ,
      RowInsertedBySP NVARCHAR(30)
        NULL
        CONSTRAINT DF_TestTable_RowInsertedBySP DEFAULT OBJECT_NAME(@@PROCID) ,
      RowUpdatedBY NVARCHAR(20)
    );
GO

CREATE PROCEDURE test.spInsertRowToTestTable
AS
    INSERT  INTO test.TestTable
            ( Column1 )
    VALUES  ( 'TestRow' );
GO

CREATE PROCEDURE test.spUpdatesRowInTestTableFirst
AS
    UPDATE test.TestTable
    SET Column1 = 'Updated ' +[Column1];
GO

EXEC test.spInsertRowToTestTable;

SELECT  *
FROM    test.TestTable AS tt;

Resulting in:

Column1              RowInsertedBySP                RowUpdatedBY
-------------------- ------------------------------ --------------------
TestRow              spInsertRowToTestTable         NULL

(1 row(s) affected)

I would simply like to catch the ids of the procedures that do Updates on the rows in column [RowUpdatedBy].

i.e if i have [spUpdatesRowInTestTableFirst], [spUpdatesRowInTestTableLast]. The column [RowUpdatedBy] should then reflect the updates like this:

Column1              RowInsertedBySP                RowUpdatedBY
-------------------- ------------------------------ --------------------
TestRow              spInsertRowToTestTable        spUpdatesRowInTestTableFirst, spUpdatesRowInTestTableLast

I have seen a example of using DBCC Outputbuffer, but i would rather not go down that road. We use Enterprise edition, so another idea would be to leverage CDC.

I would appreciate any input.

if you want to know who wrote what and when,
I would create 2 users, one for each of the procedures involved, and then
create procedures with execute as user

add an extra column on your testtable to keep the session_user or current_user so that you know who inserted/updated each record since insert_procedure will be executed as insert_user and update_procedure will be executed as update_user.

this script can help you to find out the original login, and compares different ways of addressing the users.

DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(),
               CHARINDEX('\',
               SUSER_SNAME()) + 1,
               LEN(SUSER_SNAME()))

SELECT   @USER
        , SUSER_SNAME()
        ,SYSTEM_USER
        , USER_NAME()
        , CURRENT_USER
        , ORIGINAL_LOGIN()
        ,USER
        ,SESSION_USER

EXECUTE AS LOGIN='my_user'

--DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(),
               CHARINDEX('\',
               SUSER_SNAME()) + 1,
               LEN(SUSER_SNAME()))

SELECT   @USER
        , SUSER_SNAME()
        ,SYSTEM_USER
        , USER_NAME()
        , CURRENT_USER
        , ORIGINAL_LOGIN()
        , USER
        ,SESSION_USER

REVERT


SQL Server Clustering for HA combined with Log Shipping for DR Has anyone implemented SQL Server log shipping in a clustered environment? Are there any issues or things to consider when implementing this scenario?

In general, log shipping works same way whether SQL server is clustered or not. Only thing you need to use windowsclustername\Share when you copy your logs instead of specifying node names e.g. nodel1\share  or node22\share so it works regardless what node your SQL serve is running.


avoid lock and allow two scripts to insert data in one table I have a database called db1 and I'm filling it with other two different databases, let's call db2 and db3. I connect db2 and db3 to db1 using postgres_fdw and now I'm running scripst like this:

insert into db1.table from select a,b,c from db2.table

and

insert into db1.table from select a,b,c from db3.table

turns out that only the first script is running, the second is waiting with idle in transaction. This is what htop shows me from the second script, while the first is ok.

looking for reasons, I found out that this may be happening due to a lock, so investigating on postgres, I also found this

I'm inserting data into interactions and each interactions has user, so I'm selecting the correct user before insert into interaction.
Is there a way to bypass the lock? If so, is it safe?

idle in transaction means it is waiting for data from the client, not that is waiting on a lock.

And your excerpt from pg_lock supports this, assuming the last column is the "granted" column.  All locks have been granted, no one is blocking.

If your scripts are blocking each other, it seems to be happening outside of the database.  Or at least, outside of that particular database.


How to insert very big XML data file to PostgreSQL I'm using PostgreSQL 9.5 and trying to import XML data file to database.
To load the XML I use this function from here

CREATE OR REPLACE FUNCTION bytea_import(p_path text, p_result out bytea) as
$$
DECLARE
  l_oid oid;
BEGIN
  SELECT lo_import(p_path) INTO l_oid;
  SELECT lo_get(l_oid) INTO p_result;
  PERFORM lo_unlink(l_oid);
END;
$$
LANGUAGE plpgsql;

I successfully load data from a small XML file, but when I try a 4GB XML file,
it returns error:

SQL Error [54000]: ERROR: large object read request is too large
Where: SQL statement "SELECT lo_get(l_oid)" PL/pgSQL function
bytea_import(text) line 6 at SQL statement.

It seems lo_get cannot read/extract very large data.
It's said that since version 9.4, PostgreSQL can handle up to 4TB, so how can I import very large XML data file to PostgreSQL 9.5?

Thanks in advance

The lo_import is successful, creating a 4GB large object.

But the hard limit for the bytea datatype is 1GB, so these contents are not transferable into a bytea.


Trigger not setting DATETIME correctly I have the following table:

CREATE TABLE assignment (
  id INT NOT NULL AUTO_INCREMENT,
  course_id INT NOT NULL,
  title VARCHAR(128),
  assigned DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  due DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
)

with the following trigger:

DELIMITER ;;

DROP TRIGGER IF EXISTS assignment_due_date;

CREATE DEFINER=`root`@`localhost` TRIGGER assignment_due_date
    BEFORE INSERT ON `assignment`
    FOR EACH ROW BEGIN
        IF (NEW.due IS NULL) THEN
            SET NEW.due=DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 7 DAY);
        END IF;
    END;;

DELIMITER ;

The intent is to set a default due date of 1 week (7 days), unless the INSERT already specifies a due date. However, whenever I insert a row, the due date ends up the same as the assigned date, without any error messages. Why would the trigger not update the due column?

Note that this behaves as expected when the INSERT statement does specify a value for the due column, and only misbehaves when a value is not provided. INSERT INTO assignment (course_id, title, due) VALUES (12, 'Hemingway Essay', '2019-06-12') populates the assigned and due columns with the current time and 2019-06-12, as expected, but INSERT INTO assignment (course_id, title) VALUES (12, 'Hemingway Essay') populates both columns with the current time.

UPDATE

I've removed the DEFAULT value of the due column, so the table now looks like this:

CREATE TABLE assignment (
  id INT NOT NULL AUTO_INCREMENT,
  course_id INT NOT NULL,
  title VARCHAR(128),
  assigned DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  due DATETIME NOT NULL,
  PRIMARY KEY (id)
)

With no trigger in place, the following INSERT fails (due to the lack of default value):

 INSERT INTO assignment (course_id, title) VALUES (12, 'Hemingway Essay')

which is expected. With the following three variations of the trigger in place, I would expect the due date to be set to one week out, but it always ends up with the current time:

IF (NEW.due IS NULL) THEN
    SET NEW.due=DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 7 DAY);
END IF;

SET NEW.due=COALESCE(NEW.due, CURRENT_TIMESTAMP + INTERVAL 7 DAY);

SET NEW.due=(CURRENT_TIMESTAMP + INTERVAL 7 DAY); -- forget conditions, just always set

You are testing for NULL, yet you are not letting it be NULL -- either by having a DEFAULT or NOT NULL.

So change to

due DATETIME NULL,

And, for less confusion between "timestamps" and "datetimes", use

SET NEW.due = COALESCE(NEW.due, NOW() + INTERVAL 7 DAY);


Mysql update table from another table with condition In mysql i have 2 table.
First

wp_postmeta_import

Second

wp_postmeta_temp

These tables have these columns:

meta_id
post_id
meta_key
meta_value

I need to update only my column meta_value of wp_postmeta_temp when post_id is egual to post id of wp_postmeta_import and the value of meta_key is _variation_description because in table are other post_id with other value
i try some code an this is the only that work a little bit but it is change the row that i need and in the other meta_key it gives me NULL.
UPDATE wp_postmeta_temp
SET meta_value = (
                    SELECT
                      meta_value
                    FROM
                      wp_postmeta_import
                    WHERE
                      wp_postmeta_import.post_id = wp_postmeta_temp.post_id
                    AND
                      wp_postmeta_temp.meta_key = '_variation_description'
                 );

The table have rows, for any post_id for exemple:

meta_id: variable (82649,82650..)
post_id: 5065
meta_key: _variation_description or _regular_price or total_sales ecc..
meta_value: variable (15, 0, 200 ...)

i hope that i'm clear
anyone can help me, please?

you should join the tables, so  that all rows are joint together.
Before you start this use a select with the inner join to see if it fits.
UPDATE wp_postmeta_temp wpmt
INNER JOIN wp_postmeta_import wpi ON wpi.post_id=wpmt.post_id AND wpmt.meta_key = '_variation_description'
   SET wpmt.meta_value= wpi.meta_value


How to merge Oracle data There are two schemas in Oracle11g. For example db1 and db2.

They have same structure of tables and different data to each other.

Now I want to merge data on db1 with data on db2 with no duplication.

But I can’t, duplication is happened.

I tried two different ways like bellow:

Import from dumpFile.db1 into db1 then import from dumpFile.db2 into db1.
After import data into both schemas and executo direct path insert.

insert/*+ APPEND */ into db1.myTable (SELECT * FROM db2,myTable);.

Are there good way to merge without duplication?

Just add a WHERE clause to your select that eliminates the duplicates:

INSERT /*+ APPEND */ INTO db1.myTable (
   SELECT * FROM db2.myTable b WHERE NOT EXISTS
      (SELECT 1 FROM db1.myTable a WHERE a.ID = b.ID)
   );

If you don't have a field like ID that is unique across the two tables, then you will need to compare more fields, perhaps all of them.


Tracking down program from Event Viewer The event log has hundreds of login failed errors for SQL Server. How do you find out the application generating them?

There are lots of potential candidates on the server and since it's logging in as SA just like half the applications do it's not enough that I know the username.

This is three log in attempts per second all day up to a point where all the sites halt for about 30 seconds. Some asp.net errors appear and then it's back to 3 failed SQL logins per second.

But I can't see a way to trace the actual program name.

A failed login error will usually look something like this:

Login failed for user 'bob'.
Reason: Could not find a login matching the name provided. [CLIENT: 10.59.68.215]

If you're trying to isolate the issue to a particular server, the client's IP address should be clearly displayed in the error message, so that gives you at least something to work from.

If you're trying to pin it down further, fire up SQL Server Profiler. This was previously not available for some editions of SQL Server, but the SQL Server 2012 Express SP1 Management Studio finally includes it, meaning it's available to everyone!

Once you've logged in to your server with Profiler, use "Events Selection" tab and choose to  show "All events" and "All columns". You'll want to check the box just to the left of Security Audit --> Audit Login Failed, then use the Run button.

Then, simply leave profiler running until you see a hit or three. This will show you additional information including the Application Name, and the Client Process ID.

EventClass:      Audit Login Failed
ApplicationName: Microsoft SQL Server Management Studio
ClientProcessID: 4680

In a busy production environment, the more appropriate way to capture this information is to script out a server-side trace that captures the events to a .trc file on the server, or to use Extended Events.


Error converting NVARCHAR to BIGINT when in SUBQUERY with WHERE clause after invalid records filtered I have a table that has an NVARCHAR column that contains data that cannot be converted to BIGINT.  I'm well aware of that and have filtered it out using ISNUMERIC(BB.NVARCHARCOL) = 1.  Despite this, I still get an error when trying to query the data stating that Error converting data type nvarchar to bigint.

The following works fine (no errors reported by SQL):

SELECT *
FROM myNormalTable AA INNER JOIN myBadTable BB ON BB.NVARCHARCOL = AA.MYBIGINTCOL
WHERE ISNUMERIC(BB.NVARCHARCOL) = 1

The following throws the error:

SELECT *
FROM (
    SELECT *
    FROM myNormalTable AA INNER JOIN myBadTable BB ON BB.NVARCHARCOL = AA.MYBIGINTCOL
    WHERE ISNUMERIC(BB.NVARCHARCOL) = 1
    ) ZZ
WHERE ZZ.MYBIGINTCOL = 1234

This variation also throws the error:

SELECT *
FROM (
    SELECT *
    FROM myNormalTable AA INNER JOIN
        (SELECT CAST(NVARCHARCOL AS BIGINT) NVARCHARCOL FROM myBadTable WHERE ISNUMERIC(NVARCHARCOL) = 1) BB
      ON BB.NVARCHARCOL = AA.MYBIGINTCOL
    ) ZZ
WHERE ZZ.MYBIGINTCOL = 1234

Keep in mind that this does not error and returns all the records successfully...

SELECT CAST(NVARCHARCOL AS BIGINT) NVARCHARCOL FROM myBadTable WHERE ISNUMERIC(NVARCHARCOL) = 1

I was able to find a solution, which was to convert my BIGINT to NVARCHAR in the subquery:

SELECT *
FROM (
    SELECT *
    FROM myNormalTable AA INNER JOIN myBadTable BB ON BB.NVARCHARCOL = CAST(AA.MYBIGINTCOL AS NVARCHAR)
    WHERE ISNUMERIC(BB.NVARCHARCOL) = 1
    ) ZZ
WHERE ZZ.MYBIGINTCOL = 1234

If I insert the records into a temporary table:

SELECT CAST(NVARCHARCOL AS BIGINT) NVARCHARCOL INTO #TEMP FROM myBadTable WHERE ISNUMERIC(NVARCHARCOL) = 1

I can use that temporary table in a subquery successfully:

SELECT *
FROM (
    SELECT *
    FROM myNormalTable AA INNER JOIN #TEMP BB ON BB.NVARCHARCOL = AA.MYBIGINTCOL
    WHERE ISNUMERIC(BB.NVARCHARCOL) = 1
    ) ZZ
WHERE ZZ.MYBIGINTCOL = 1234

What in the world is going on?  It seems like SQL refuses to use the subquery to get a smaller result set before running the outer query, it wants to use the whole table regardless of what I do.  SQL Server 2012 Developer Edition

Use try_cast() instead.

select *
from (
  select *
  from myNormalTable AA
  inner join #TEMP BB on try_cast(BB.NVARCHARCOL as bigint) = AA.MYBIGINTCOL
  --where try_cast(BB.NVARCHARCOL as bigint) is not null /* not neccessary for inner join */
 ) ZZ
where ZZ.MYBIGINTCOL = 1234

In Sql Server 2012 and up: each of these will return null when the conversion fails instead of an error.

try_convert(datatype,val)
try_cast(val as datatype)
try_parse(val as datatype [using culture])


SQL Server Try Catch Transastion I need to know if the T-SQL below is correct to roll back if something bad happens.

I first create a temp table

IF OBJECT_ID(N'tempdb..#LocalSummary') IS NOT NULL
    DROP TABLE #LocalSummary

CREATE TABLE  #LocalSummary (

then I populate the table

INSERT INTO #LocalSummary
   SELECT *....

Then I start my try catch

BEGIN TRY
 BEGIN TRANSACTION

    DELETE FROM LocalSummaryRealTable

    INSERT INTO LocalSummaryRealTable
        SELECT S.*
        FROM #LocalSummary AS S

    COMMIT
END TRY
BEGIN CATCH
       IF @@TRANCOUNT > 0
              ROLLBACK TRANSACTION
    ...

I noticed when I did the insert into the LocalSummaryRealTable the columns didn't line up and I got an error. I expected things to roll back but in SSMS query window it gave me a message that the trans wasn't committed and asked me if I wanted to commit it. Why would this ask me when it should of been rolled back?

You want to do the BEGIN TRANSACTION prior to the BEGIN TRY:

BEGIN TRANSACTION
BEGIN TRY
    DELETE FROM dbo.LocalSummeryRealTable;
    INSERT INTO dbo.LocalSummeryRealTable
    SELECT S.*
    FROM  #LocalSummery' AS S;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
       IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
END CATCH

Also, please schema-qualify references to database objects such as tables, views, stored procedures etc.  Also, you should get in the habit of adding semi-colons to the end of each statement.

SET XACT_ABORT ON; is not necessary if you are explicitly rolling back the transaction inside the CATCH block.


Deleting Data From Multiple Tables Suppose,I've a table called UNIVERSITY containing universities name:

universityID    universityNAME  isACTIVE
     7            GNDU             1
     6            PU               1
     5            PTU              1
     8            LPU              1

Now these universities ID's has been(obviously) used in many tables within the database(name e.g.Education),Suppose 10 tables.

Q.Now what happen if i delete one university?

A.The universityID field in other tables becomes NULL.

But I don't want these,rather when I delete 1 university from UNIVERSITY TABLE,all its occurrences with Rows in all 10 table should get deleted.

What will be the shortest and easiest MySQL Query for this operation.

NOTE:I'm using PHP language.

If you have foreign keys to this table, add the option ON DELETE CASCADE.  Then when you delete a row in a parent table all the rows in the child tables that refference that row will be deleted as well.