SQL Server 2014 Buffer Pool Extension - There is insufficient memory available in the buffer pool I am running Microsoft SQL Server 2014 Enterprise Edition SP1 (build 12.0.4213.0 (X64)), with 288GB of RAM, and max memory set to 280GB (I arrived at that number based on many recommended suggestions from SQL Server MVPs).

Twice in the last month I have received:

  There is insufficient memory available in the buffer pool

...followed by other errors, most notably:

  The log for database 'db' is not available

The database goes into suspect mode.  If I restart the SQL service, it comes back fine. I have an SSD that is 1,863GB in size and buffer pool extension on it with a size of 1,859GB in size (I left almost a 5 GB cushion on the SSD).

The error seems to indicate RAM memory pressure. At the time it errors, the server is under high pressure. But this is not uncommon, however, the errors appearing are new. Should I be targeting my RAM modules for testing? Or is somehow the Buffer Pool Extension causing the error?

We monitor this box with SolarWinds DPA, plus I use DMVs to get realtime info, so I have a real good idea of what was running at the time.  Most waits were memory related.

The buffer pool extension data was around 1TB when it went down, so there was plenty of room there. This is a production server, with a very small maintenance window one day a week. Db is in full recovery model, with 1 full backup a day, and trans logs backed up every 30 minutes.

This is a dedicated SQL Server box.
The first error that shows is the insufficient memory error.
The database that errored is 3.22 TB on an iSCSI mapped drive, set to auto grow by 100GB. There is 800GB free currently. Log file is 646GB in size, set to autogrow by 30GB. There is 154GB free. All dbs together is 4TB. All mdf's and ldf's are on their own iSCSI mapped drive.

No hardware or disk related errors in Event Viewer. The only error in the SQL Server error log before it dumps is:

  04/04/2016 07:42:32,spid77,Unknown,Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 24

sys.dm_os_loaded_modules shows only my ODBC driver.

I think raising or lower the SQL Server max memory wont change the problem.  SQL Server should be able to release memory on its own.  We have been running in this configuration for years without a problem.  However, this new issue has happened twice recently.  The only changes I can think of are that we added an SSD for the buffer pool extension.

I did a diagnostic test on the RAM to see if anything turned up, it did not.  I also updated SQL Server 2014 to SP1 CU5, however the error occurred again.  This time though, it did not take the database into suspect/recovery mode.  So that helped.  The questions still remains as to why it is occurring.

The best answer I can come up with is that Sql really doesn't have any room in the buffer pool to give up.  Meaning, we have too many queries running, or the queries running have plans that are taking up too much space.  This link was somewhat helpful No Free Buffers.  My solution at this point is to evaluate the queries that were running when the error occurs, so see if I can tune them.


Here is your original query

SELECT ua.package_name, c.id as cid, c.title, c.description, c.iconlink, c.marketlink
FROM `user_app` AS ua INNER JOIN `creative` AS c
ON ua.package_name <> c.package_name
where ua.imei_num = '30c899b5c0f29dbc95418c4173f6f745'
and c.package_name in (12,13)
group by `package_name`

I would like to suggest refactoring the query to retrieve needed rows before doing the JOIN

SELECT ua.package_name, c.id as cid, c.title, c.description, c.iconlink, c.marketlink
FROM
(
    SELECT package_name FROM `user_app`
    WHERE imei_num = '30c899b5c0f29dbc95418c4173f6f745'
) AS ua INNER JOIN
(
    SELECT * FROM `creative`
    WHERE c.package_name in (12,13)
) AS c
ON ua.package_name <> c.package_name
group by ua.package_name;

Give it a Try !!!


SELECTing data based on a specific time frame I'm using PostgreSQL. I have a table which stores data related to some orders. It has four to five columns, including:

cust_code char varying (20),
sugg_order integer,
ordered_qty integer,
date date

Now I want to find all the rows which occur within a specific time frame. Let us assume that this time frame starts on 1st of April every year and ends on 31st of March next year (like 2017-04-01 to 2018-03-31 and 2018-04-01 to 2019-03-31 and so on).

Below are the examples of the output which I want from the query:

If I run this query on 2018-08-01 then I need all the data from 2018-04-01 to 2018-08-01.
If I run this query on 2019-03-01 then I need all the data from
2018-04-01 to 2019-03-01.
If I run this query on 2019-05-01 then I need all the data from 2019-04-01 to 2019-05-01

In other words, I need all data between today (the day I run the query) and the latest (most recent) 1st of April before today.

I need a query which will work for any year and I cannot get the start date of the time frame from outside.

In order to answer this, I did the following:

Created the table:

CREATE TABLE cust (cust_code VARCHAR(20), sugg_order integer, ordered_qty integer, ord_date date);

Created some sample data (note how it spans from early 2017 - before Apr 1st) to after today.

INSERT INTO cust VALUES ('cust_1', 45, 450, '2018-01-28');
INSERT INTO cust VALUES ('cust_1', 45, 350, '2018-02-28');
INSERT INTO cust VALUES ('cust_1', 45,  50, '2018-03-28');
INSERT INTO cust VALUES ('cust_1', 45, 451, '2018-04-01');
INSERT INTO cust VALUES ('cust_1', 45, 150, '2018-04-28');
INSERT INTO cust VALUES ('cust_1', 45,  87, '2018-05-28');
INSERT INTO cust VALUES ('cust_1', 45,  93, '2018-06-28');
INSERT INTO cust VALUES ('cust_1', 45,  56, '2018-07-28');
INSERT INTO cust VALUES ('cust_1', 45, 400, '2018-08-28');
INSERT INTO cust VALUES ('cust_1', 45, 234, '2017-01-28');
INSERT INTO cust VALUES ('cust_1', 45, 432, '2017-02-28');
INSERT INTO cust VALUES ('cust_1', 45, 657, '2017-03-28');
INSERT INTO cust VALUES ('cust_1', 45,  43, '2017-04-28');
INSERT INTO cust VALUES ('cust_1', 45,  32, '2017-05-28');
INSERT INTO cust VALUES ('cust_1', 45, 111, '2017-06-28');

WITH cte1 AS
(
  SELECT
    CASE
      WHEN DATE_PART('MONTH', NOW()) >= 4
        THEN TO_DATE(TO_CHAR(DATE_PART('YEAR', NOW())::INT,     '9999')     || '04' || '01', 'YYYYMMDD')
        ELSE TO_DATE(TO_CHAR(DATE_PART('YEAR', NOW())::INT - 1, '9999')  || '04' || '01', 'YYYYMMDD')
    END AS start_tax_year
)
SELECT * FROM cust, cte1
WHERE ord_date <= NOW() AND ord_date >= cte1.start_tax_year
ORDER BY ord_date

(There's a dbfiddle here)

I then ran 2 queries varying this line:

WHEN DATE_PART('MONTH', NOW()) >= 4 -- also used < 4
                                   -- to simulate a previous year.

Results with >= 4

cust_code sugg_order ord_qty    ord_date start_tax_year
   cust_1         45     450  2018-04-01     2018-04-01
   cust_1         45     150  2018-04-28     2018-04-01

Only two records - which is to be expected since we're only at the beginning of the tax year - just into May. A check shows that there are, in fact, only two records between today (May 10th) and Apr 1st.

Results with < 4

cust_code sugg_order ord_qty    ord_date start_tax_year
   cust_1         45      43  2017-04-28     2017-04-01
   cust_1         45      32  2017-05-28     2017-04-01
   cust_1         45     111  2017-06-28     2017-04-01
   cust_1         45     450  2018-01-28     2017-04-01
   cust_1         45     350  2018-02-28     2017-04-01
   cust_1         45      50  2018-03-28     2017-04-01
   cust_1         45     451  2018-04-01     2017-04-01
   cust_1         45     150  2018-04-28     2017-04-01

8 records - again to be expected, as the period between now and Apr 1st 2017 covers most of the data. If the date as sometime late in 2017, it would pick up the records between then and Apr 1st 2017.

The query is rather convoluted:

THEN TO_DATE(TO_CHAR(DATE_PART('YEAR', NOW())::INT, '9999')     || '04' || '01', 'YYYYMMDD')

The whole point of the CTE is to get April 1st of the correct tax year - i.e. the one we're in at the moment, even if we've gone into the next calendar year.

1) get DATE_PART from NOW() cast as an INT when we're beyond April first in the year where the query is being performed. This becomes

DATE_PART('YEAR', NOW())::INT - 1, '9999')

if the month part of the current year is < 4, it's before April, so you have to go back to April 1st of the previous year - which is where the -1 comes in.

2) once you've got the correct year for April 1st, then all the rest is the same. You then use TO_CHAR to transform this into a string and then using the double pipe characters (||) to concatenate the month (04) and day (01) to the correct year. Then use TO_DATE to convert the correct string (20180401) using the format string 'YYYYMMDD'.

3) then use the CTE to feed this value into the main query - obtain the desired records. Et voilà.


Getting averages based on column values Editing because I made a mistake transforming from my real example with lots of irrelevant details to the boxes example:
I have a table BOXES like this:
box_id  | color | dimension | value
1          blue     length      1
1          blue     width       2
2          blue     length      3
2          blue     width       4
3          red      length      5
3          red      width       6
4          red      length      7
4          red      width       8
5          green    length      9
5          green    width       10
6          green    length      11
6          green    width       12

And I want to output average dimension for each color, like this:
color | avg_length | avg_width
blue        2           3
red         6           7
green       10          11

Is there a SQL query to do so? I thought of doing 3 different queries like
WITH avg_len as (
  SELECT AVG(length) as l,color FROM `boxes`
), avg_width as (
  SELECT AVG(width) as w,color FROM `boxes`
), avg_height as (
  SELECT AVG(height) as h,color FROM `boxes`

but not sure how to tie them together. In my real data, there are thousands of "boxes" and a dozen or so "colors".

SELECT color,
       AVG(CASE WHEN dimension = 'length' THEN value END) avg_length,
       AVG(CASE WHEN dimension = 'width' THEN value END) avg_width
FROM boxes
GROUP BY color;

fiddle


Insert Multiple Column based on existing table aggregated I currently have a SQL query that will go through a table, get the distinct dates and then insert the results into another table.
INSERT [fd_movements_dates](Date)
SELECT DISTINCT(CONVERT(date, MsgTimestamp)) as Date
FROM [fd_movements]
ORDER BY Date

I'm wanting to expand it so that it sums the amount of records within each day. The MsgTimestamp column is a datetime2(0).
Expected result
01-01-2020 - 345600
02-01-2020 - 654000
Thank you.

INSERT [fd_movements_dates](Date, Cnt)
SELECT (CONVERT(date, MsgTimestamp)) as Date
    ,COUNT(1) AS Cnt
FROM [fd_movements]
GROUP BY (CONVERT(date, MsgTimestamp))


Oracle - Using index on unique constraint, what the deal? Using Druid to generate SQL script for Oracle, I have seen a difference between the version 3.10 and 3.13 and I would like to understand what going on.

SQL generated by Druid 3.10 :

CREATE TABLE ATTRIBUTE_VALUE_LABEL
  (
    ID         NUMBER(18),
    ATTRVALID  NUMBER(18)       not null,
    LANGUAGE   NVARCHAR2(2)     not null,
    NAME       NVARCHAR2(250)   not null,
    CREATED    DATE             default SYSDATE,
    MODIFIED   DATE,
    CONSTRAINT PK_ATTRIBUTE_VALUE_LABEL primary key(ID) USING INDEX TABLESPACE &&TSINDEX,
    CONSTRAINT XA_ATTRIBUTE_VALUE_LABEL_2 unique(ATTRVALID,LANGUAGE) USING INDEX TABLESPACE &&TSINDEX,
    CONSTRAINT FK_ATTRIBUTE_VALUE_LABEL_1 foreign key(ATTRVALID) references ATTRIBUTE_VALUE(ID) on delete CASCADE
  );

SQL generated by Druid 3.13 :

CREATE TABLE ATTRIBUTE_VALUE_LABEL
  (
    ID         NUMBER(18),
    ATTRVALID  NUMBER(18)       not null,
    LANGUAGE   NVARCHAR2(2)     not null,
    NAME       NVARCHAR2(250)   not null,
    CREATED    DATE             default SYSDATE,
    MODIFIED   DATE,
    CONSTRAINT PK_ATTRIBUTE_VALUE_LABEL primary key(ID) USING INDEX TABLESPACE &&TSINDEX,
    CONSTRAINT XA_ATTRIBUTE_VALUE_LABEL_2 unique(ATTRVALID,LANGUAGE),
    CONSTRAINT FK_ATTRIBUTE_VALUE_LABEL_1 foreign key(ATTRVALID) references ATTRIBUTE_VALUE(ID) on delete CASCADE
  );

As you can see, the only difference is that Druid don't generate anymore a USING INDEX TABLESPACE &&TSINDEX after the UNIQUE constraint.

I suspect that there is a reason for why Druid don't generate anymore this USING INDEX thing on the UNIQUE constraint (could have something to do with the fact that there already a USING INDEX on the PRIMARY constraint), but I would like to understand why.

I have two questions then :

Can this cause some error on Oracle ?
Do this change something for the index used by the unique constraint ? Not in the right table space ?

If the USING INDEX clause is omitted, the index will be still created automatically with the same name as the constraint, in the same tablespace where table is created. This will not cause an error.

http://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52225

  If you specify schema.index, then Oracle attempts to enforce the    constraint using the specified index. If Oracle cannot find the index
  or cannot use the index to enforce the constraint, then Oracle
  returns an error.
  If you specify the create_index_statement, then Oracle attempts to    create the index and use it to enforce the constraint. If Oracle
  cannot create the index or cannot use the index to enforce the
  constraint, then Oracle returns an error.
  If you neither specify an existing index nor create a new index, then    Oracle creates the index. In this case:

  The index receives the same name as the constraint.
  If table is partitioned, then you can specify a locally or globally    partitioned index for the unique or primary key
  constraint.


right syntax to use near 'TOKUDB_DEFAULT' mysql> CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ENGINE=TokuDB, ROW_FORMAT=TOKUDB_DEFAULT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TOKUDB_DEFAULT' at line 1

CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ENGINE=TokuDB ROW_FORMAT=tokudb_zlib;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tokudb_zlib' at line 1

it is on Percona Server 8.0

why??

Most likely the changes in this JIRA link: https://jira.percona.com/browse/PS-4942

To use a specific row_format or tokudb_row_format do the following instead:

SET SESSION tokudb_row_format=TOKUDB_LZMA;
CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ENGINE=TokuDB;

To check what compression method is used by the table you can run this command from the shell:

tokuftdump --header <tablename>_main*.tokudb


Search all columns as a single text per row Need an extension that offers a full-text-search with a kind of contains(*,text) instead to concatenate each field-value.

There are times when a formal structured query language is overkill, and the easiest way to find the right content is to perform a search, like you would with a search engine such as Google or Yahoo (...) JCR-SQL2:
SELECT * FROM [nt:base] WHERE CONTAINS([nt:base],'full-text-query')
Full+text+search example in other SQL engine

You can cast the complete row to a string (text) and then use the built-in full text search on that result:

select *
from foo
where to_tsvector(foo::text) @@ to_tsquery('full-text-query');

foo::text concatenates all columns of that table as a single large string, using the default text representation for non-text data.


Using results from one query as the parameters for another Is there a way to have a SQL query automatically update its' parameters based on the results of a previous query?

Here's the scenario I'm trying to achieve:

A report should automatically be run daily that runs the following queries (in order):

SELECT id of rows that match a certain criteria (we'll call this result set "X")
SELECT certain fields from rows that are titled "Cancelled-[X]"
UPDATE rows from [X] with information from "Cancelled-[X]"

Consider using Common Table Expressions (CTEs).

I don't have your table structures nor your queries, but what I would envisage is something like this:

WITH cte1 AS
(
  Perform your 1.SELECT id of rows that match a certain criteria (we'll call this result set "X")`
),
cte2 AS
(
  Perform you 2.SELECT certain fields from rows that are titled "Cancelled-[X]"
)
UPDATE rows from [X] with information from "Cancelled-[X]";

There are some restrictions on CTEs. You can't perform UPDATEs from them for example. I'm not really a MS SQL Server person, but here are a couple of references which should help (1, 2).

For an example of them in action, see my post here. It's a PostgreSQL solution, but the MS SQL Server syntax is virtually identical.


Is DB Normalization done purely based on the primary key or is it done based on all the candidate keys? I have come across two flavours of the normalization procedure while referring the internet and textbooks. viz.

Type 1. Normal forms based only on the primary key.

In this type,

-> 2NF disallows Partial dependencies on the primary key.

-> 3NF disallows transitive dependencies on the primary key.

Type 2. A much more general Normal form based on ALL of the candidate keys

In this type,

Partial dependencies and transitive dependencies on ALL candidate keys are taken into account.

In most of the sites I referred, I have found tutorials and notes based only on the 1st type.
But the textbook 'Fundamentals of Database Systems' by Navathe and Elmasri describes both the types. Even wikipedia's page on 2NF mentions seperately about '2NF and candidate keys' which is based on the 2nd type.

See 2NF and Candidate Keys - Wiki

But neither the textbook nor wiki mentions anything regarding which type is better or which one is practically preferred.

Which type should be followed when nothing is mentioned about the type?
Hope U have understood my question.

Please help me out on this guys.

Normalization is concerned with all Candidate Keys. A Primary Key is just a candidate key. Primary keys are no different to any other candidate key.

Potential confusion arises because in the early days of relational database theory the term Primary Key used to mean any and all candidate keys whereas modern usage is that Primary Key means only one key that is "preferred" or deemed to have some special significance for database users. Unfortunately many of the "definitions" you will see online or even in print are wrong as a result.


Direct the OUTPUT of an UPDATE statement to a local variable I would like to do this :

DECLARE @Id INT;

UPDATE Logins
SET    SomeField = 'some value'
OUTPUT @Id = Id
WHERE  EmailAddress = @EmailAddress -- this is a parameter of the sproc

Is this even possible? I know I can declare a local table variable and direct the output there but I would prefer to skip it if possible

No, because you are potentially OUTPUTting multiple rows, which wouldn't fit into a scalar variable.

You need to output into a @Table variable or declared table to handle multiple rows of output.


Why does auto increment jumps by more than the number of rows inserted? I am very perturbed by this weird behaviour I am seeing in the auto_increment value recorded in the bidID of a Bids table after performing bulk insertion using a stored procedure:

INSERT INTO Bids (itemID, buyerID, bidPrice)
 SELECT itemID, rand_id(sellerID, user_last_id), FLOOR((1 + RAND())*askPrice)
 FROM Items
 WHERE closing BETWEEN NOW() AND NOW() + INTERVAL 1 WEEK ORDER BY RAND() LIMIT total_rows;

For example, if the auto_increment bidID value is 101 at start, and I inserted 100 rows, the ending value becomes 213 instead of 201. However, the bidIDs of those inserted rows runs sequentially to a maximum of 201.

Having check the following,

SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

I have no idea why it is happening. What could be causing the jump in the auto increment value?

This is not unusual and there are a couple of causes. Sometimes it is due to optimisations the query runner makes to reduce contention issues with the counter resource, improving efficiency when there are concurrent updates to the affected table. Sometimes it is due to transactions that got explicitly rolled back (or implicitly rolled back due to encountering an error).

The only guarantees from an auto_increment column (or IDENTITY in MSSQL, and the other names the concept goes by) is that each value will be unique and never smaller than a previous one: so you can rely on the values for ordering but you can not rely on them not to have gaps.

If you need the column's values to have no gaps at all you will need to manage the values yourself, either in another layer of business logic or in the DB via a trigger (be careful of potential performance issues with triggers though), of course if you do roll your own you will have to contend with all the concurrency/rollback/cleanup-after-delete/other issues that the DB engines work around by allowing gaps).


Logging sequential scans in PostgreSQL? Is there a way to log queries which use sequential scans in PostgreSQL? Or just log everything and then later grep for seqscans?

I don't know a way to log sequential scans only.  Just a small remark: seqscans are not evil, you will get them (especially for smaller tables) all the time.

On the other hand, you can definitely log everything, if you want, and there are multiple ways.

The easiest is probably

[ALTER DATABASE yourdb ] SET log_statement TO 'all';

This will log every query that passes the parser:

  Note: Statements that contain simple syntax errors are not logged even by the log_statement = all setting, because the log message is emitted only after basic parsing has been done to determine the statement type. In the case of extended query protocol, this setting likewise does not log statements that fail before the Execute phase (i.e., during parse analysis or planning). Set log_min_error_statement to ERROR (or lower) to log such statements.

Another insteresting way of achieving it is setting log_min_duration_statement to a (very) low value:

[ALTER DATABASE yourdb ] SET log_min_duration_statement TO 0; -- or somewhat higher

The difference between the two is that the latter will also register the actual parameters passed to functions.

Notes:

you need superuser rights for changing either of these settings
you can use both at the same time.  For details, please read the documentation linked above.


All logins used on an instance is 'SA' I found out that on an instance of a certain SQL server at my company- sa is virtually the only login used by all the sessions. The SPID# extends to 130. What do you think is the impact of this? Is there any downside despite of course security? Appreciate you pitching in on this. Thanks.

The impact is a very severe security risk. It might be that your application is hard-coded to use 'sa'; often very poorly written and implemented 3rd party apps do this kind of awful thing.

The question you need to ask is, "what can I do to fix this?", because it definitely needs fixing urgently.

If it is a 3rd party app, you may need to escalate to your support provider.

Ideally you will create a new database role, assign minimum required permissions to that role [e.g. exec on certain Stored Procedures] , then create logins/groups for your users, add them to this role, and modify the application's connection settings to use their own logins instead of sa.

You should also then create a new sysadmin login NOT called 'sa', for your own use, and then if possible, remove sa itself from the sysadmin fixed server role, and/or disable it.

Having all your users connect using a sysadmin-privileged login (even if it is one other than sa), is, to put it mildly, far from ideal.


postgresql trigger unexpected behaviour by many copies of inserted row On insert in tbl1, the trigger I am working on, copies rows in tbl2 with some updates.

Trigger:

create or replace function myprocedure() returns trigger as
begin
IF (TG_OP = 'INSERT')THEN
 insert into tbl2
 select new.* from tbl1 ;

 update tbl2
 set (field1, field2) =  ...
 where gid = new.gid;

 return new ;
 END IF ;
END;
$$ LANGUAGE plpgsql ;

DROP TRIGGER IF EXISTS mytrig ON public.tbl1 ;
CREATE TRIGGER mytrig AFTER INSERT or UPDATE on public.tbl1
FOR EACH ROW EXECUTE PROCEDURE myprocedure();

My problem is:

The first insert in tbl1 gives 1 insert in tbl2. It is fine.
The second insert in tbl1 gives 2 (identical) lines in tbl2 (values of second insert)
The third insert in tbl1 gives 3 (identical) lines in tbl2 (values of third insert)
etc.

I am feeling a little lost. Any help?

Why do you do an update on tbl2? Anyway you must add a where clause with the primary key of the new row to the update. You are now updating all rows in tbl2.

Also the insert is inserting new.* for every row already in tbl1.

Why don't you rewrite the insert into something:

insert into tbl2 (field1, field2, ...) values (new.field1, new.field2, ...);

Now you can set the correct values and do not need an update afterwards.


SQL SERVER 2016 high CPU I have a brand new server with SQL Server 2016.

It has 24 CPU, about 80go of RAM.

The thing is, sometimes, the CPU gets pretty high (> 70%) with no specific reason at all.

If I look at the 'execute sp_WhoIsActive @get_locks=1' I get about 40 queries running, but without lock, and some of them for more than 30 seconds instead of a few ms.

This append about once a day in production. The only fix I've got so far is changing the option "cost threshold for parallelism" from 90 to 89 and from 89 to 90. Changing this value actually fix the issue in less than 10 seconds and I get a CPU charge getting lower and my user happier.

Do you guys have any idea about the root of this issue? I've thought about plan cache but I don't know what to do with that idea...

Edit : I've disable Intel HyperThreading but the issue is still the same. I've put a task cleaning the cache every hour. Do you have any idea ?

Edit 2 : I have open an issu on Microsoft Support. Apparently there is an issue with page rentention solved with a reboot of SQL Server 2016. I'll let you know ASAP if I have a "real" fixe for this.

Please verify the settings for MAXDOP. Please review suggestions by Microsoft. In most cases the optimal value is the number of dedicated cores, that are available to SQL Server. But maximum value is set to '8'. In some cases that could vary and needs to be tested well to set to higher values.

I assume, that you have two six-core Processors with Hyper-Threating enabled. It is possible, that your queries don't perform very well, with Hyper-Threating enabled.
In some cases the SQL Server Performance is better using 12 'real' cores, than 24 combined with Hyper-Threating.

You should try to disable Hyper-Threating and verify the new settings.

Unfortunately you wrote, that this is your production environment. Are you able to verify settings on similar hardware and similar settings?

Furthermore you should have a look on your I/O subsystem. Are there more than one tempdb?

Additional (I can not comment another comment):

DBCC FREEPROCCACHE will effect all user.
Try using with option recompile at the SQL Query that effects the Performance issue most.


Is there any implications on updating Azure SQL server from V11 to V12? We are planning to update our servers but we can't be sure if there will be any implications or problems if we do that? Did anyone experienced any issue and how big they were? Or should I expect an issue or it would be updated smoothly?

This Azure documentation page lists the limitations during and after the upgrade:

Plan and prepare to upgrade to SQL Database V12


Are Postgres btree index keys compressed? v8.4 and v9.2 (Yes, I know they're archaic and out of support, but there's nothing I can do about it.)

Some of the tables in the database which I manage (but don't design) have btree indexes on CHARACTER VARYING(256) fields.  Even when the field values aren't all blank, the length of the data in them is no more than 12 characters.

Yes, the physical table storage for these columns is highly compressed, but what about the btree indexes?  Would the indexes be more efficient if the columns were altered down to VARCHAR(12)?

Thanks

"Would the indexes be more efficient if the columns were altered down to VARCHAR(12)?"

No, and that is easy to test:

create table foo(val1 varchar(256), val2 varchar(12));
create index i_foo1 on foo(val1);
create index i_foo2 on foo(val2);

insert into foo(val1,val2)
select 'ABCDEFGHIJ','ABCDEFGHIJ'
from generate_series(1,10000);

vacuum full;

select relname, pg_size_pretty(pg_relation_size(c.oid))
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where nspname like 'fiddle%';

relname | pg_size_pretty
:------ | :-------------
foo     | 512 kB
i_foo1  | 392 kB
i_foo2  | 392 kB

dbfiddle here

  "Yes, the physical table storage for these columns is highly compressed…"

It isn't compressed (unless the string is long), it's just that for varchar (unlike char) the length specifier is merely a constraint that determines the maximum length:

  The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which includes the space padding in the case of character. Longer strings have 4 bytes of overhead instead of 1. Long strings are compressed by the system automatically, so the physical requirement on disk might be less.


Can I run a Report Server and a Mirror server from a working Log Shipping secondary instance? Our setup:

City A - Primary server for logshipping
City B - Secondary server for logshipping

Now there is a lot of data being updated from A - B.

And the server in City B is underutilized, and it is a very powerful machine.

Can I install a Reporting server and a mirroring server on the server which hosts the secondary logshipping server, and run the Reporting and mirroring server to get the data from the sSecondary logshipping server?

We cannot have the data come across 3 times (one for Logs, Report, Mirror) - and the secondary server is updated regularly enough to allow for our requirements.

But the secondary logshipping server is obviously always in Standby/Readonly mode - is this all possible?

The question is confusing but I think what you're trying to say is that you want to access the data at City-B, using your existing log-shipping setup.

With regards to the log shipping, at City-B, there is an option [RESTORE ... WITH STANDBY] which will leave the database in restoring mode, however in a READONLY state so it's accessible for SELECT queries. The main restriction with this approach, is that you can only restore a log when all users are out of the database. Depending on your log shipping restore schedule (very frequent/this may not work then), you'd have to kill all the
connections prior to restoring the next log. Test this and re-do your log shipping config.
Depending on your version of SQL, perhaps explore using DB Snapshots at city-B - need to research how this would interact log shipping.
You could install/configure Reporting Services at City-B (which would create the two system report databases). Reports could then query the databases (at city-b) which are involved in log-shipping (but read-only) as per my first point.
If you going to create another instance on City-B, then how about creating a job that performs a backup of databases on City-B (instance1/logshipping) and restoring on City-B (instance2/Reporting Instance) - which you can then access as required.
Mirroring does not let you read from the mirrored instance.

I hope this helps.


Memory-optimized table index size 10X bigger than data On one of our production servers, I found that index size is 10X times bigger than data size.
These servers and schemas are identical with a similar workload.
CREATE TABLE [dbo].[process_aggregation_close]
(
    [organization_id] [bigint] NOT NULL,
    [computer_id] [uniqueidentifier] NOT NULL,
    [process_id] [int] NOT NULL,
    .
    .
INDEX [ix_process_aggregation_memory_computer_id_process_id] NONCLUSTERED
(
    [organization_id] ASC,
    [computer_id] ASC,
    [process_id] ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
GO

Tables are staging tables and they are involved in very frequent insert/delete operations.

Microsoft SQL Server 2017 CU9 Standard Edition.
All databases are in the full recovery mode. I do not think it can be related to checkpoint, since the tables are schema-durable only. (In any case, I tried a manual checkpoint with no effect).
These in-memory tables are staging and always insert and delete activities exists on them. I monitor five SQL Servers with similar load on them. This behavior occurs only on one.

The size difference is due to how row versioning in indexes and the garbage collector of in memory tables work.

Looking at the garbage collection process used for memory optimized tables:

  If there is no transactional activity after (for example) deleting a
  large number of rows and there is no memory pressure, the deleted rows
  will not be garbage collected until the transactional activity resumes
  or there is memory pressure

Okay, that makes sense for the indexes, but why is the data cleared while the indexes are not cleared?

My educated guess is that removing the row versioning from indexes is a different process than removing the overwritten data from the table. More on that here.

An example for this, with a query that inserts 1M rows and executes a full delete on a table that consists of the three columns you have in your question.

-- insert data into the tables
SET NOCOUNT ON;
DECLARE @I int =0;
WHILE @I < 1000000
BEGIN
    INSERT INTO dbo.MemOptimizedTable([organization_id], [computer_id], [process_id])
    VALUES (cast(@I as bigint) + 2147483647, NEWID(),@I) ;
    SET @I +=1;
END
-- Delete everything
 DELETE FROM  dbo.MemOptimizedTable;

SELECT object_id, memory_used_by_table_kb, memory_used_by_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats;

After running the query the first time

object_id   memory_used_by_table_kb memory_used_by_indexes_kb
1045578763          62501           58852

Running the query 3 times gives us this starting point where the index size has grown beyond the table size

object_id   memory_used_by_table_kb memory_used_by_indexes_kb
965578478           62501           78464

While the insert of 1M records is running again, the mem used by table grows

--Insert running
object_id   memory_used_by_table_kb memory_used_by_indexes_kb
965578478           86715            88471

And grows

--Insert running
object_id   memory_used_by_table_kb memory_used_by_indexes_kb
965578478           123628           104662

Until the insert is finished

--Insert done
object_id   memory_used_by_table_kb memory_used_by_indexes_kb
965578478           62500            79543

The index grew, the memory used by the table was removed after the insert and remained the same. After this insert finishes, a process is triggered to remove the excess memory that was used by the table.

This is not by any means the complete explanation but could be a starting point.

What can we do?

According to this post there’s also no way to force garbage collection to occur.

You could op to

Do nothing and let SQL Server handle it
Drop and create the index to reset the memory usage (if you have another index on the table)
...

Commands to drop and create the index example

ALTER TABLE  dbo.table
       DROP INDEX [ix_process_aggregation_memory_computer_id_process_id];

ALTER TABLE  dbo.table
       ADD INDEX [ix_process_aggregation_memory_computer_id_process_id] ([organization_id] ASC,[computer_id] ASC, [process_id] ASC);

Create table statement of the table used in the example

 CREATE TABLE dbo.MemOptimizedTable(
  [organization_id] [bigint] PRIMARY KEY NONCLUSTERED NOT NULL,
    [computer_id] [uniqueidentifier] NOT NULL,
    [process_id] [int] NOT NULL,
INDEX [ix_process_aggregation_memory_computer_id_process_id] NONCLUSTERED
(
    [organization_id] ASC,
    [computer_id] ASC,
    [process_id] ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY)
GO


Where are subscriptions registered in SSMS after creating subscription on Distributor Server? With 2 servers, each with a SQL Server instance, I can:

Create a local publication on server A
Create a local Subscription on server B (managed by the Distributor database)

Then on server A I can see the publication. But there is no publication on server B. If I try re-create the same local subscription on server B, then I get an error that the subscription already exists.

Why can I not see the subscription within the subscription folder on Server B?

Server B is also the Distributor server

I think it was a push subscription - i.e. that it was configured on the distributor to be pushed.

I have expanded the publication tree on the publisher and can see the publication. I was expecting the subscription to be seen in the same way. I tried refreshing.

It is on neither publisher nor subscriber. but if I try to recreate the replication then I get an error that it already exists. The question I'm asking is effectively "why can't I see a subscription in the replication folder?"

Alternatively. Is there a SQL Server instance that I need to connect to?

I should note that there are errors in the replication monitor. So there are still a couple problems. Anyway. I can see the jobs in the SQL Server Agent. There was a warning: "Starting the synchronization agent(s)". Would this stop the subscription being created?

From experience a few days later...

When creating a pull subscription, the subscription was added to the 'local subscriptions' folder in SSMS.

When creating a push subscription, nothing is added to the subscription folder until the subscription is run the first time. Thereafter it is added to the 'local subscriptions' folder.


Cannot Start Oracle Database after Shutdown I have install oracle 11.2.0.4 with oracle asm on oracle linux 6.4 and it work correctly .
After shutdown abort , i can not start database at all .
can any one help me ?

SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 6 14:14:30 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup
ORA-27137: unable to allocate large pages to create a shared memory segment
Linux-x86_64 Error: 1: Operation not permitted
Additional information: 201326592
Additional information: 1
SQL>

--------------------------------------------
[grid@localhost ~]$ asmcmd
ASMCMD> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
-------------------------------------------
[root@localhost ~]#  grep Huge /proc/meminfo
AnonHugePages:    100352 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

$ oerr ora 27137
27137, 00000, "unable to allocate large pages to create a shared memory segment"
// *Cause: Parameter USE_LARGE_PAGES set to only, but large pages were not
//         configured properly
// *Action: Check alert file for more information

Your output shows hugepages were not configured, but the database is configured to use only large pages (use_large_pages=only), hence it can not start.

Configure the required amount of pages by setting the vm.nr_hugepages in /etc/sysctl.conf, the appropriate memlock limit in /etc/security/limits.conf, then reboot and start the database.

Configuring HugePages on Linux

If you do not need hugepages, simply edit the parameter file of the database, and unset the use_large_pages parameter.


Is it possible to run query against only part of the Central Managed Servers? For example, I want to run this query to get the status of some servers having high availability group set up:

SELECT  s.role_desc,  g.name, r.replica_server_name , GETDATE() as checkTime
    FROM sys.dm_hadr_availability_replica_states AS s
    INNER JOIN sys.availability_replicas AS r
        ON s.replica_id = r.replica_id
    INNER JOIN  sys.availability_groups g
        ON r.group_id = g.group_id

...but some of the managed servers are SQL Server 2008, which don't have those views in the query, and hence the query returns error.

I have tried something like:

SELECT  s.role_desc,  g.name, r.replica_server_name , GETDATE() as checkTime
    FROM sys.dm_hadr_availability_replica_states AS s
    INNER JOIN sys.availability_replicas AS r
        ON s.replica_id = r.replica_id
    INNER JOIN  sys.availability_groups g
        ON r.group_id = g.group_id
where  r.replica_server_name in
(select server_name  FROM msdb.dbo.sysmanagement_shared_registered_servers_internal
where server_name like 'theserversIwant%a'
)

...but it doesn't work. It still queries the entire list of all the managed servers.

Try wrapping your query inside some conditional logic which checks for the @@version.
It will still access all servers in your Central Managed Server list, but it won't throw the error.  Here is an example you could try.

IF (SELECT @@VERSION) LIKE '%2008%'
BEGIN
    SELECT 'not avail on 2008' AS role_desc
        ,'not avail on 2008' AS NAME
        ,'not avail on 2008' AS replica_server_name
        ,GETDATE() AS checkTime
END
ELSE
BEGIN
    SELECT s.role_desc
        ,g.NAME
        ,r.replica_server_name
        ,GETDATE() AS checkTime
    FROM sys.dm_hadr_availability_replica_states AS s
    INNER JOIN sys.availability_replicas AS r
        ON s.replica_id = r.replica_id
    INNER JOIN sys.availability_groups g
        ON r.group_id = g.group_id
END

Regarding your question about saving the results of the CMS query - I'm not aware of a built-in method for doing that.  However, one option (that requires a little extra work) is to:

Run the CMS query, select all of the results grid and choose Copy with Headers.
Head over to DBFiddle and click on the Text To DDL button at the bottom of the page.
Paste the contents of the CMS grid results in the Formatted Text window and enter a table name in the Table Name box.
Click the Preview DDL button which will show you a CREATE TABLE command along with INSERT statements.

You'll have to do some minor modifications to the generated DDL such as removing tick-mark quotes around column names and using brackets around column names that have spaces like [Server Name] or putting brackets around reserved identifiers like [Name].

This will allow you to save the results to a table of your choice for further analysis.


MariaDB sample data for test queries? In PostgreSQL we use generate_series a lot to do quick test cases.

For example here I generate a quick test for that question using

CREATE TABLE foo
AS
  SELECT x::int AS a, (x%2*x)::int AS b
  FROM generate_series(1,3e6) AS gs(x);

What's the easy way to generate sample data in MariaDB? Is there anything like generate_series or how do experienced MariaDB users generate test cases?

In MariaDB (which is a better MySQL) there is a sequence storage engine that can be used to generate sequences:

CREATE TABLE foo
AS
SELECT seq AS a, seq%2*seq AS b
FROM seq_1_to_3000000;


Can I change the wal_level from logical to replica? If so are any impact on the replication process? Is there a chance to change the wal_level from logical to replica without any impact. I know this involves a restart of services but is this possible?
For example if there is a already a logical replication running with master and slave setup. The question here is can I change the wal_level now from logical to replica(streaming) with the same master and slave.

If you reduce wal_level to replica, and you have a logical replication slot (as is needed for logical replication), the server will refuse to restart.

This is because wal_level = logical is required by logical decoding and logical replication.

If you first drop the subscription and the publication, thereby removing logical replication, the replication slot will be gone, and you can reduce wal_level.

To switch from logical to physical replication, you have to re-create the standby server with pg_basebackup.


Not able to restore a database with multiple backup files from Azure blob storage (block blobs) I was testing the backup with multiple backup files (6) and I was able to successfully take backup for one of my large database (3TB) to my azure storage account by using MAXTRANSFERSIZE, BLOCKSIZE parameters.
Then I tried to restore the database using the multiple files. Since it's a very large database and it takes few hours to restore I tried to run RESTORE FILELISTONLY from URL to verify if I was able to get the list of files. When I ran the RESTORE FILELISTONLY statement I have received the below error
Cannot open backup device ‘https://mystorageaccount.blob.core.windows.net/sqlbackup/ServerName-Test_DB_FULL_20200912_030001_1.bak’. Operating system error 2(The system cannot find the file specified.)
I used SAS key to create the SQL Credential and hence my blobs are block blobs. I have also created access policy on my container with all types of permission exists on the policy (racwdl). The SAS key was generated on the azure portal with 1 year validity. Do I need to provide the read, write permissions while creating the SAS key (which can be achieved only through Azure Storage Explorer).
If I replace the SAS key with the newly generated key with read write permission, would I be able to restore with the backups which were taken with the old SAS key?
Or Am I missing anything? Please advice

I found the reason why I was not able to get the list from the backup files. Since I used Ola Hallengren's script to run my backup the files were saved in a sub folder with Instance_name>Database_name>FULL>Backupfile_name. I supposed to provide the actual backup path of the backup file by going into the properties of the backup file and then copy the path from there. Instead I was just copying the container path and then adding the backup file at the end.
It looked something like below.
https://storagesqlbackups.blob.core.windows.net/fullsqlbackup/dev-sql-01/Test_DB/FULL/dev-sql-01_Test_DB_FULL_20200821_220001.bak


WildCard to Skip Synchronized DB I am running a security RAG on reporting server that updates our security and put database to read_only mode. I am using wildcard to skip system and some other databases, but what can I use to skip any database that's a part of AG. in other words what can I use to skip synchronized DB?

Thank you.

but what can I use to skip any database that's a part of AG. in other
  words what can I use to skip synchronized DB?

There is a difference between  getting any database that belongs to an AG or to only get the synchronized databases. For example, async nodes will keep having the status: "synchronizing" (or another message, but not synchronized).

To get all the databases participating in an AG:

SELECT name
FROM sys.databases
WHERE database_id
NOT IN (SELECT database_id
        FROM sys.dm_hadr_database_replica_states);

To get only the synchronizing databases:

SELECT name
FROM sys.databases
WHERE database_id
NOT IN (SELECT database_id
        FROM sys.dm_hadr_database_replica_states
        WHERE synchronization_state_desc = 'SYNCHRONIZED');

To also exclude the system db's:

SELECT name
FROM sys.databases
WHERE database_id
NOT IN (SELECT database_id
        FROM sys.dm_hadr_database_replica_states
        WHERE synchronization_state_desc = 'SYNCHRONIZED')
        AND database_id > 4;


Problem setting MSDTC on client computer I created an application that is making use of MSDTC (to open a transaction on multiple databases within the same MS SQL server), our local environment was setup successfully and both DTCPing, DTCTester and the application work. However when it came to deployment I encountered issues when setting up the client computer to make use of the server's coordinator.

On Client site I have tried the following:

DTC Ping passed successfully
Setting Coordinator to the server one results into the following error "The specified remote host machine does not exist"
I am able to ping the server using host name

When I tried running DTC tester, it output the following:

Executed: dtctester.exe
DSN:  accdim
User Name: sa
Password: <hidden>
tablename= #dtc16397
Creating Temp Table for Testing: #dtc16397
Warning: No Columns in Result Set From Executing: 'create table #dtc16397 (ival int)'
Initializing DTC
DtcGetTransactionManager failed: 8004d01b
DtcGetTransactionManager Call Error: Indicates unable to connect to the transaction manager, or the transaction manager is unavailable.

This ended out being a domain policy issue, the computer was moved to an other group and MSDTC was registered and working successfully.


Test-DbaDiskAlignment Not Working PS H:\> Test-DbaDiskAlignment -ComputerName Test_Server
    WARNING: [Test-DbaDiskAlignment][22:09:54] Can't connect to Test_Server
    WARNING: [Test-DbaDiskAlignment][22:09:54] CIM query to Test_Server failed.

I am very new to using dbatools. Would someone be able to help me with this error please.

Check out this post, do you meet the minimum requirements? Is remoting enabled?
https://dbatools.io/install/

Otherwise run it with the verbose flag and let us know what you get:

Test-DbaDiskAlignment -ComputerName Test_Server -Verbose

Also what version of both the dbatools module and powershell are you using?

Powershell version:

$PSVersionTable

dbatools version:

Get-Module dbatools


Postgresql create index concurrently I want to create a concurrently index in postgresql inside a trigger like following code

    CREATE OR REPLACE FUNCTION hour_production_index_partition(partition_name text) RETURNS VOID AS
$BODY$
BEGIN
    -- Ensure we have all the necessary indices in this partition;
    EXECUTE 'CREATE INDEX CONCURRENTLY IF NOT EXISTS ' || partition_name || '_domain_write_date_idx ON ' || partition_name || ' (fk_domain, write_date)';
    END;
$BODY$
LANGUAGE plpgsql;

The problem is that when I execute that statement, postgresql complains sending an error like

  WARN  SqlExceptionHelper - SQL Error: 0, SQLState: 25001 ERROR
  SqlExceptionHelper - ERROR: CREATE INDEX CONCURRENTLY cannot run
  inside a transaction block   Where: SQL statement "CREATE INDEX
  CONCURRENTLY IF NOT EXISTS
  hour_production_1_2018_07_01_domain_write_date_idx ON
  hour_production_1_2018_07_01 (fk_domain, write_date)"

I need to create this index in this way because hour_production table is dinamically break into small parts.

CREATE INDEX CONCURRENTLY cannot run inside a transaction, and all functions are transactions, (but ordinary create index can).

Perhaps something like PG_AGENT can be leveraged to create the index upon command from your trigger.

https://www.pgadmin.org/docs/pgadmin4/3.x/pgagent.html

Alternatively you could do a regular create index when you create the table, an empty table will not be locked for long during a create index.


How to add DATETIME value for session user I am maintaining two tables:

Employee table consists of username and password(for logging in)
log table consists of login and logout time for the session user

With the click of a button, I should be able to capture/insert the time and session user to log table.

Here is my code for logging time and session user:

 <?php

session_start();
     if($_SESSION['username']) {
        $user = $_SESSION['username'];
    $tin= $_POST["timein"];
            $conn = mysql_connect("localhost","root","");

            mysql_select_db("attendance", $conn);

            $query = "INSERT INTO log (user, timein) VALUES ($user , NOW())";

            mysql_query($query, $conn);

            echo "
you have logged your time!
";

            mysql_close($conn);
    }
?>

I see two problems with

$query = "INSERT INTO log (user, timein) VALUES ($user , NOW())";

Problem #1 : You need quotes around $user

Problem #2 : You need use timein instead of NOW()

Now, you need to make a decision.

If you want to supply the time yourself, then the query should be

$query = "INSERT INTO log (user, timein) VALUES ('$user','$tin')";

If you want the DB Server's clock time, then the query should be

$query = "INSERT INTO log (user, timein) VALUES ('$user',NOW())";

thus eliminating the need for $tin= $_POST["timein"];


Problem with Oracle Rollback Segment Session I am facing a problem in Oracle, which after performing a procedure that performs INSERT or UPDATE with a millions amount of record in a table, not the next procedure "sees" the data in the table already inserted. My procedure is scheduled to perform commit 100 thousand lines. I also use cursors with bulk collection.

I suspect the problem may be due to the area Rollback segment of the Session of Oracle, anyone know if there is any DML command to clear the area or give a flush on Rollback segment?

I suspect you're using multiple sessions and the other session cannot "see" the work performed because it is currently uncommitted.

The default Oracle transaction Isolation Level is Read Committed. This means that a  session will not be able to see the work a given session has done until it has explicitly committed the transaction.

The Oracle Server Concepts Guide explains this in detail.


Why Cassandra is not considered a relational database? Read this answer
Relational databases are based on the relational model, an intuitive, straightforward way of representing data in tables. In a relational database, each row in the table is a record with a unique ID called the key. The columns of the table hold attributes of the data, and each record usually has a value for each attribute, making it easy to establish the relationships among data points.
Cassandra has tables.
CREATE TABLE movies (
    movie_id UUID,
    title TEXT,
    release_year INT,
    PRIMARY KEY (( movie_id ))
);

Data being relational has nothing to do with support of ACID properties.
Data being relational has nothing to do with normalised data

Why Cassandra is not considered Relational database?

Why Cassandra is considered NoSQL database? despite it has tables

Cassandra is not an RDBMS because it does not support the relational data model.

The fundamental assumption of the relational model is that all data is
represented as mathematical n-ary relations, an n-ary relation being a
subset of the Cartesian product of n domains

https://en.wikipedia.org/wiki/Relational_model
Instead, it models data as a key-value store, where the values are rows. But there's no requirement that all the "rows" in a "table" have the same "columns", as is the case in the relational model.
From Wikipedia:

Unlike a table in an RDBMS, different rows in the same column family
do not have to share the same set of columns, and a column may be
added to one or multiple rows at any time.[29]
Each key in Cassandra corresponds to a value which is an object. Each
key has values as columns, and columns are grouped together into sets
called column families. Thus, each key identifies a row of a variable
number of elements. These column families could be considered then as
tables. A table in Cassandra is a distributed multi dimensional map
indexed by a key. Furthermore, applications can specify the sort order
of columns within a Super Column or Simple Column family.

https://en.m.wikipedia.org/wiki/Apache_Cassandra


Unable to update SQL Server via Access, Reserved error (-7776) I'm trying to use Access as a front-end for an SQL Server 2017 database. The objects I'm interested in are 6 temporal tables and a view joining them. I created an ODBC user data source and linked the tables and the view. For the view, I created an instead of update trigger that updates the underlying tables, and that works just fine.
Since the primary keys of the underlying tables are identity, I figured it would be easier to insert/delete directly against the underlying tables instead of via the view (I had to SET IDENTITY_INSERT ON when manipulating the view via SQL).
I can add a row to one of the tables without a problem, but when I try to delete or update a row in the table, I get an error saying "Reserverat fel (-7776). Det finns inget meddelande för felet". Loosely translated that would be something like "Reserved error (-7776). There is no message for the error".
I visit the world of Windows on a regular basis every 20 years or so, so I have no real clue on where to start looking for any clues on what might be the error. Any insight?
Funny thing is that if I create a view that joins two of the tables (the primary key of one of the tables is also unique in the view), I can update the view, and the underlying table is also updated (no instead of trigger necessary).
It is also possible to insert a new row (for delete it seems like I will need an instead of trigger). So I guess this is a workaround, but it seems rather odd that I can update the view, but not the table.

Access (I guess) does not seem too fond of SysStartTime and SysEndTime columns. If I mask them in a view and link the view in Access, both update and insert works. Example:
DROP VIEW dbo.PHYTOCLASS_DIVISION;
CREATE VIEW dbo.PHYTOCLASS_DIVISION as
select  d.DivisionName
        , c.ClassID
        , c.ClassName
        , c.DivisionID as classDivisionId
        , c.ModifiedBy
        , c.ModifiedDate
        , c.Avslutad
from dBotnia20200929.dbo.PHYTODIVISION d
join dBotnia20200929.dbo.PHYTOCLASS c
        on d.DivisionID = c.DivisionID;

For a trivial join like this, SQL Server figures out how to handle insert/update so instead of triggers are not necessary.
The answer in is-is-possible-to-update-sql-server-2016-temporal-tables-from-access indicates that the problem has to do with the precision of the timestamps in Sys... columns. I have not verified that lowering the precision would help, but it may be worthwhile for someone to read that answer


How can I stop continuous failed logins on SQL Server I have a couple SQL Server Express databases running on Windows Server 2008 R2 on a VPS.

A couple of websites access them (these are local connections, right?). Website connections use integrated security.

I also connect using Remote Desktop. SQL Server Express is accessible remotely through 1 port (same as Remote Desktop)

Problem: logs are showing failed login attempts every 5-10 seconds. They will be from 1 IP for a few minutes, then from another IP. They usually try user sa (I don't have one) or a variation. I have blocked several hundred of these using Windows Firewall, but they keep coming.

So - what else should I be doing? Is this something I should be worrying about?

It sounds like those failed logins are coming from the Internet.

You really shouldn't have a firewall port open to SQL Server directly from the Internet.  That is just asking for trouble.

If the website and SQL Server are not on the same network, or are connected only via the Internet, you should look at providing a VPN connection between the two so that you don't provide an open port directly from the Internet to SQL Server.


How do I extract a repeating string pattern from multiple rows? I have the following data:

|  ID  |                 BadCol                        |
+------+-----------------------------------------------+
|  1   | All of R489804 R489805 R489806 R489807 R489808|
|  2   | SP fr R308999                                 |
|  3   | R308777, R104621, R708931                     |

I'm only interested in the R###### pattern (which will always be an R followed by 6 numerical characters). I can retrieve the location of first occurrence using the following function:

PATINDEX('R[0-9][0-9][0-9][0-9][0-9][0-9]', BadCol)

Ideally I'd like to pivot the pattern into it's own row like so:

|  ID  | GoodCol |
+------+---------+
|  1   | R489804 |
|  1   | R489805 |
|  1   | R489806 |
|  1   | R489807 |
|  1   | R489808 |
|  2   | R308999 |
|  3   | R308777 |
|  3   | R104621 |
|  3   | R708931 |

I've seen some great resources on how to do this when the data is delimited the same way, such as here. Unfortunately the data in this column is delimited differently from row to row. I've also explored a recursive CTE like what is detailed here, but unfortunately both of these methodologies use CHARINDEX() which allows an optional start_location parameter. PATINDEX() unfortunately does not take a start_location parameter.

Here is a sample of how I would do this:

select
      a.id
    , substring(a.value,T.N,7)
from
    (
    values
        (1,'All of R489804 R489805 R489806 R489807 R489808'),
        (2,'SP fr R308999                                 '),
        (3,'R308777, R104621, R708931                     ')
    ) a(id,value)
cross apply (select N from Tally where N<len(a.value)-5) T(N)
where substring(a.value,T.N,7) like 'R[0-9][0-9][0-9][0-9][0-9][0-9]';

Here, I am using a Tally (or numbers) table to walk the length of each value, extracting substrings of length 7 that match the required format.


mongoDB won't stop with shutdown or kill Not sure If I can make the same question already made here but here it goes.

I have a mongodb fork and running with high CPU usage. I'm trying to shut it down but nothing seems to work.

 2729 mwon      20   0 10.080g 9.047g  22668 S  56.2 28.8   4254:11 mongod --dbpath /disk2/db --port 27018 -logpath /disk2/db/mongod.log --fork

I have tried:

mongod --dbpath /disk2/db --shutdown

but it hangs without any kind of response. I also tried:

 kill 2729
 kill -2 2729

The command gets executed but nothing happens. This is the tail of the log:

2019-07-30T15:55:27.757+0000 I NETWORK  [conn13356] end connection 127.0.0.1:45690 (62 connections now open)
2019-07-30T15:55:27.758+0000 I NETWORK  [conn13368] end connection 127.0.0.1:45764 (61 connections now open)
2019-07-30T15:55:27.759+0000 I NETWORK  [conn13380] end connection 127.0.0.1:45826 (60 connections now open)
2019-07-30T15:55:27.764+0000 I NETWORK  [conn13381] end connection 127.0.0.1:45832 (59 connections now open)
2019-07-30T15:55:27.764+0000 I NETWORK  [conn13369] end connection 127.0.0.1:45772 (58 connections now open)
2019-07-30T15:55:27.765+0000 I NETWORK  [conn13344] end connection 127.0.0.1:45628 (57 connections now open)
2019-07-30T15:59:17.043+0000 I CONTROL  [signalProcessingThread] got signal 15 (Terminated), will terminate after current cmd ends
2019-07-30T15:59:17.044+0000 I NETWORK  [signalProcessingThread] shutdown: going to close listening sockets...
2019-07-30T15:59:17.044+0000 I NETWORK  [signalProcessingThread] removing socket file: /tmp/mongodb-27018.sock
2019-07-30T16:23:37.773+0000 I NETWORK  [conn11035] end connection 127.0.0.1:48928 (56 connections now open)

Try the below commands:

kill <process id>
kill -2 <process id>
kill -SIGHUP <process id>
kill -SIGQUIT <process id>
kill -SIGTERM <process id>
kill -SIGKILL <process id>

or

Log on to your database
Switch to admin database
Execute the command db.shutdownServer() to gracefully shutdown mongodb which is the safest way to shut down the DB

Thank you!

-Anban


GROUP BY using columns not in the SELECT list - when is this practical, elegant or powerful? TL;DR - I would like examples of where using a GROUP BY (example shown below) using columns not in the SELECT list can be used to resolve SQL challenges in a [practical | elegant | powerful] way. I mean in a general way - the example demonstrated below is interesting because it demonstrates the principle (but it doesn't work!). I want working examples where use of this technique can be used to achieve something "significant".

Following up to this question, which required the counting of routes from point_x to point_y and vice-versa, taking routes between the same points to be equivalent - i.e. A -> B is the same for the count purposes as B -> A. A working general solution to that question is given there.

However, one poster (SQLRaptor) showed a solution which solved the problem for the (presumably) subset of data shown in the question, but this poster also said that their solution wasn't general and asked the OP could they see why? SQLRaptor also said this was an example of a GROUP BY using columns not in the SELECT list and that this could be an elegant (powerful) solution to some SQL challenges!

SQLRaptor's solution (using my own notation from answer) was:

SELECT  MIN(origin) AS point_1,
        MAX(destination) AS point_2,
        COUNT(*) AS journey_count
FROM route
GROUP BY ASCII(origin) * ASCII(destination)
ORDER BY point_1, point_2

I thought the non-generality might be something to do with the multiplication (duplicates), but had no concrete proof. This transpired to be the case - see my proof here and here.

Unfortunately, this example of using a GROUP BY using columns not in the SELECT list doesn't work very well - what I would like are examples of where this technique can work well?

I have used this technique (grouping on a column not used in the SELECT list) to combine an ordered list of values (which has gaps) into ranges of contiguous values.

First I do a rownum() on the ordered set of values. Then we diff the rownum() and the value. By grouping on the diff, we get the ranges.

CREATE TABLE #TMP (ID INT)

INSERT INTO #TMP
SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 6 UNION
SELECT 8 UNION
SELECT 9 UNION
SELECT 10 UNION
SELECT 15 UNION
SELECT 16 UNION
SELECT 17 UNION
SELECT 18 UNION
SELECT 19 UNION
SELECT 20

WITH RN AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY ID) AS RN, ID
    FROM
        #TMP
),
SRC AS
(
    SELECT
        RN,
        ID,
        ID-RN DIFF
    FROM
        RN
)
SELECT
    MIN(ID) RANGE_START,
    MAX(ID) RANGE_END,
    COUNT(*) CNT_VALUES_IN_RANGE
FROM
    SRC
GROUP BY
    DIFF


SSRS minimal permission to download reports I received this request from a developer:

  When I try to download *.rdl report files from myssrs_server/Reports
  I’m getting this error:

  Would you be able to extend my permissions so I can download reports
  from all branches?

What is the minimal permission I should grant in order for him to get this done?

Any alternatives? (I was thinking about granting read only access to the reportServer database or something on those lines - but I don't want to make their lives too difficult )

I found this article very good regarding ssrs permissions:

SQL Server Reporting Services 2012 Permissions

Webpage were all the ssrs Predefined Roles are explained.

At the end, just so that I don't need to download the reports for them to edit, I have granted a very selective AD group with the contents manager permission, so that they can do their work.

any better alternative please post an answer or comment, I will review the situation accordingly.


Hard limitation for merge replication article count? A bit of background. We developed an app that uses merge replication. Right now, we are currently publishing about 212 articles, and everything is fine, as it appears to fall into the 256 article count limit. (Using SQL Standard 2005 at the server, and 2005 Express at the subscriber level.)

We are in the process of developing a new version, with a new database built from the ground up, which will have a minimum system requirement of SQL 2008 R2 Standard server side and Express 2008 R2 at the client end, which also appears to have the same 256 count limit on published articles. (yes, we will also support 2012, it's limits are the same though)

The question comes in where we have successfully test published out over 400 articles in the new version, and everything tests functionally (pub creation, snapshot and subscriber creation, and replication itself). Has anyone else exceeded the count limit before, and is this just a soft recommended limit? We have not seen any errors, either during publication creation, or subscriber creation.

According to this article: http://msdn.microsoft.com/en-us/library/ms143432%28v=sql.105%29

I'm waiting on a call back from Microsoft, but I'd like to know if anyone has seen this before, in any ver of SQL.

I have now Merge replication setup with 666 Table Articles in production, and I didn't get any warning or errors regarding articles limit.

Update:
Starting from SQL Server 2016, Articles limit is now: 2048

Source


Update single field in a document (document.update($set)?) Can't seem to figure out how to update a single field.

Whatever I try, to update one field (picture: "url") the rest of the fields are being removed and set to null.

I use the following command now:

Meteor.users.update(
          userId,
          {
            $set: {
              profile: {
                picture: fileURL
              }
            }
          }
        );

And this is what happens with the rest of the object after update.

{
  "data": {
    "user": {
      "_id": "J222CqhR777GcXD",
      "createdAt": "Tue Sep 11 2018 19:23:55 GMT-0400 (EDT)",
      "email": "scott@example.com",
      "profile": {
        "about": null,
        "firstName": null,
        "lastName": null,
        "phone": null,
        "picture": "https://image.path/source.jpg"
      }
    }
  }
}

Is there another operator I am missing or some other way to do this?

In another document I am querying for all the data before, storing it in a React state and then passing all the fields that I have not changed to the update, but that seems rather inefficient and unnecessary step(???)

Is there really not a way to update just a single field without affecting the rest of the document?

cheers guys

Had to rewrite like so (profile.picture in one line with dot notation), this works:

Meteor.users.update(
    userId,
    {
        $set: {
            'profile.picture': fileURL
        }
    }
);

Answered by @coagmano on meteor forums


How do I (safely) kill long running operations in MongoDB? Occasionally operations get out of control in MongoDB and might end up running for hundreds of seconds, and impact performance until they are killed or completed.

When that happens, I know I have killOp() available to me but how do I kill only targeted long running operations without also killing (for example) the long running operations involved in replication (which can be dangerous)?

This can be a little tricky, but the fact that the MongoDB shell is basically a Javascript interpreter gives us decent options in terms of filtering.  Here is the function I use to accomplish this:

// kills long running ops in MongoDB (taking seconds as an arg to define "long")
// attempts to be a bit safer than killing all by excluding replication related operations
// and only targeting queries as opposed to commands etc.
killLongRunningOps = function(maxSecsRunning) {
    currOp = db.currentOp();
    for (oper in currOp.inprog) {
        op = currOp.inprog[oper-0];
        if (op.secs_running > maxSecsRunning && op.op == "query" && !op.ns.startsWith("local")) {
            print("Killing opId: " + op.opid
            + " running over for secs: "
            + op.secs_running);
            db.killOp(op.opid);
        }
    }
};

This will only kill queries above the maxSecsRunning threshold and will not touch anything running against the local database, which is where the oplog lives (and hence is the database that is involved in the long running replication ops.  It is relatively easy to add criteria to the inner if conditional to more precisely target operations as needed based on specific needs.

The code is also available as a gist (where I will remember to update it on an ongoing basis).


mysql - query records to fetch by time stamp I've a MySql 5.6 DB with a simple table (MyISAM) .

| UniqueKey |  TimeStamp |
-------------------------
| 1         | 1024125412 |
| 2         | 1024125413 |
| 3         | 1024125414 |

My table will have maximum 3 million records with indexed timestamps and one record for every secoond.

I need to "Fetch one record (first) per minute".

I've tried select * from table group by MINUTE(timestamp)

But, its taking 28 minutes to fetch locally. I'm looking for solution to fetch with in 10 sec.

Any suggestions for pre computations or using different DB to achieve same would be appreciated.

Edit:

@Vérace: I've chosen MyISAM, because my business requirement enforced me to have 1600 columns in single table and i dont need any relations . It seems only MyISAM allow me to have more than 1000 columns.

@Vérace: timestamp column already indexed.

@Mihai: Yes, sometimes. I may request for entire table. and sometimes in date range.

Edit:

some metrics per answers:

SELECT id,TIMESTAMP FROM t WHERE MOD(TimeStamp,60)=0 order by id desc LIMIT 1000; 59Sec
SELECT id,TIMESTAMP FROM t WHERE MOD(TimeStamp,60)=0 order by null LIMIT 1000; 5SSec

Since the seconds are from 0 (1970-01-01 00:00:00 UTC), you should look for every multiple of 60

SELECT * FROM mytable WHERE MOD(TimeStamp,60)=0;

or if TimeStamp is indexed, you can do

SELECT T.* FROM
(SELECT TimeStamp FROM mytable WHERE MOD(TimeStamp,60)=0) M
INNER JOIN mytable T USING (TimeStamp);

Give it a Try !!!

SUGGESTION #1

You should store the timestamp of the minute and index it

ALTER TABLE mytable ADD COLUMN MinuteTimeStamp AFTER TimeStamp;
UPDATE mytable SET MinuteTimeStamp = TimeStamp - MOD(TimeStamp,60);
ALTER TABLE mytable ADD INDEX MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey);

Then, you can do MIN aggregation on MinuteTimeStamp.

SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp;

and use it get those records

SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);

It was tactfully pointed out that triggers would degrade performance

Perhaps doing INSERTs like this may help

INSERT INTO mytable (UniqueKey,TimeStamp,MinuteTimeStamp) VALUES
(
    uniquevalue,
    UNIX_TIMESTAMP(NOW()),
    UNIX_TIMESTAMP(NOW() - INTERVAL SECOND(NOW()) SECOND)
);

SUGGESTION #2

Since you have over 1000 columns (Ugh), perhaps a table of those minute timestamps would be better.

CREATE TABLE MinuteKeys
(
    MinuteTimeStamp INT UNSIGNED NOT NULL,
    UniqueKey INT UNSIGNED NOT NULL,
    PRIMARY KEY (UniqueKey)
    KEY MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey)
) ENGINE=MyISAM;
ALTER TABLE MinuteKeys DISABLE KEYS;
INSERT INTO MinuteKeys SELECT TimeStamp - MOD(TimeStamp,60),UniqueKey FROM mytable;
ALTER TABLE MinuteKeys ENABLE KEYS;

Then, you could use that table for the aggregation

SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM MinuteKeys GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);

EPILOGUE

Other suggestions are possible but you should really consider normalization of the table

See my post Too many columns in MySQL as to why


Building a custom SQL Server 2017/2019 container on a windows server without internet I am in bit of a predicament,and would appreciate any help to move forward from my current situation
I have been given a VM running on Windows Server 2016 with Docker Enterprise installed. My management wants to have SQL Server 2019 with docker on windows containers running within this VM. This is a POC project. The VM does not have any internet connection, and therefore, I am unable to pull the base image from the docker hub. I know that there are SQL Server 2017 developer editions available. What can be done to make sure that I can go ahead with having a container in this situation? Especially when the VM does not have any internet connection?
What I tried doing was to download a SQL Server 2017 Developer edition on my local machine running Windows 10Pro, and then moved the container image onto the VM. But when I tried to load the container, I got an error message saying that "Host OS does not match". That is when I found the website below
https://docs.microsoft.com/en-us/virtualization/windowscontainers/deploy-containers/version-compatibility?tabs=windows-server-2004%2Cwindows-10-2004
Please suggest as to how I can create an  image in such a situation

SQL Server 2019 Windows containers are still in preview as of this writing. If you don't join the Early Adopter program, you can create a custom image by starting with a Windows Server Core base image and then install SQL Server 2019 (and optionally CUs) to create a custom image.
The build is most easily done on a machine with internet connectivity to pull the base OS image from the public repro. You can then copy the image to the machine without an internet connection (or push it to a local private repro) and run it from there. Alternatively, pull, save, and load the OS images (steps 6-8) and run the build on the server.
Below is an example of the general steps to do this. My appologies for the formatting. I'm mardown-challenged.

create an empty directory on your Windows machine (e.g. C:\dockerfiles)
 MKDIR C:\dockerfiles

create a dockerfile in that directory with the following contents
 #Start with a Windows Server Core base image.
 #Note that the image with tag 'latest' is pulled by default, which could be older than the actual host OS version.
 #In this case, specify the version-specific to pull a later image compatible with the host OS version.

 FROM mcr.microsoft.com/windows/servercore:2004

 #Parameters for start.ps1:
 ENV sa_password="_" \
     attach_dbs="[]" \
     ACCEPT_EULA="_" \
     sa_password_path="C:\ProgramData\Docker\secrets\sa-password"

 #set default shell to PowerShell and set preferences:
 SHELL ["powershell", "-Command", "$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue';"]

 #set current directory to root (C:\)
 WORKDIR /

 #copy SQL Server setup folder into build image for installation
 COPY setup setup

 #run setup to install
 RUN  \setup\setup.exe /q /ACTION=Install /INSTANCENAME=MSSQLSERVER /FEATURES=SQLEngine /UPDATEENABLED=0 /SQLSVCACCOUNT='NT AUTHORITY\System' /SQLSYSADMINACCOUNTS='BUILTIN\ADMINISTRATORS' /TCPENABLED=1 /NPENABLED=0 /IACCEPTSQLSERVERLICENSETERMS

 #remove setup files
 RUN  Remove-Item -Force -Recurse -Path 'setup'

 #create empty directory (C:\SqlFiles) for user database files
 RUN "mkdir SqlFiles | Out-Null"

 #stop SQL Server service, configure TCP/IP, allow SQL authentication, and set default file locations
 RUN stop-service MSSQLSERVER ; \
         set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -name tcpdynamicports -value '' ; \
         set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -name tcpport -value 1433 ; \
         set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\' -name LoginMode -value 2 ; \
         set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\' -name BackupDirectory -value 'C:\SqlFiles' ; \
         set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\' -name DefaultData -value 'C:\SqlFiles' ; \
         set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\' -name DefaultLog -value 'C:\SqlFiles' ;

 #set command used for container health check status
 HEALTHCHECK CMD [ "sqlcmd", "-Q", "SELECT 1;" ]

 &#35;copy startup script to image
 COPY start.ps1 /

 #specify command invoked when container is run from this image
 CMD .\start -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs \"$env:attach_dbs\" -Verbose

create start.ps1 file in build directory (e.g. C:\dockerfiles\start.ps1). Below is an example script similar to the one I use.
 # The script sets the sa password and start the SQL Service
 # Also it attaches additional database from the disk
 # The format for attach_dbs

 param(
 [Parameter(Mandatory=$false)]
 [string]$sa_password,

 [Parameter(Mandatory=$false)]
 [string]$ACCEPT_EULA,

 [Parameter(Mandatory=$false)]
 [string]$attach_dbs
 )

 if($ACCEPT_EULA -ne "Y")
 {
     Write-Verbose "ERROR: You must accept the End User License Agreement before this container can start."
     Write-Verbose "Set the environment variable ACCEPT_EULA to 'Y' if you accept the agreement."

     exit 1
 }

 #start the service
 Write-Verbose "Starting SQL Server"
 Start-Service MSSQLSERVER

 if($sa_password -eq "_") {
     if (Test-Path $env:sa_password_path) {
         $sa_password = Get-Content -Raw $secretPath
     }
     else {
         Write-Verbose "WARN: Using default SA password, secret file not found at: $secretPath"
     }
 }

 if($sa_password -ne "_")
 {
     Write-Verbose "Changing SA login credentials"
     $sqlcmd = "ALTER LOGIN sa with password=" +"'" + $sa_password + "'" + ";ALTER LOGIN sa ENABLE;"
     & sqlcmd -Q $sqlcmd
 }

 $attach_dbs_cleaned = $attach_dbs.TrimStart('\\').TrimEnd('\\')

 $dbs = $attach_dbs_cleaned | ConvertFrom-Json

 if ($null -ne $dbs -And $dbs.Length -gt 0)
 {
     Write-Verbose "Attaching $($dbs.Length) database(s)"

     Foreach($db in $dbs)
     {
         $files = @();
         Foreach($file in $db.dbFiles)
         {
             $files += "(FILENAME = N'$($file)')";
         }

         $files = $files -join ","
         $sqlcmd = "IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = '" + $($db.dbName) + "') BEGIN EXEC sp_detach_db [$($db.dbName)] END;CREATE DATABASE [$($db.dbName)] ON $($files) FOR ATTACH;"

         Write-Verbose "Invoke-Sqlcmd -Query $($sqlcmd)"
         & sqlcmd -Q $sqlcmd
     }
 }

 Write-Verbose "Started SQL Server."

 Write-Verbose "Changing SQL Server server name to $($env:COMPUTERNAME)"
 $sqlcmd = "EXEC sp_dropserver @@SERVERNAME;EXEC sp_addserver N'$env:COMPUTERNAME', 'local';"
 & sqlcmd -Q $sqlcmd

 Write-Verbose "Restarting SQL Server."
 Stop-Service MSSQLSERVER
 Start-Service MSSQLSERVER
 Write-Verbose "Restarted SQL Server."

 Write-Verbose "Tailing log..."
 Get-Content -Path "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG" -Wait

copy files from SQL 2019 installation media to a setup subdirectory (e.g. C:\dockerfiles\setup)

run docker build in the contest of the build directory (e.g. C:\dockerfiles) to build and tag the custom image
 docker build -t mssql-2019-windows-dev-rtm-example .

save the image to a tar file
 docker save --output "C:\temp\mssql-2019-windows-dev-rtm-example.tar" mssql-2019-windows-dev-rtm-example

copy the image to the Windows server
 copy "C:\temp\mssql-2019-windows-dev-rtm-example.tar" "\\YourServer\c$\temp\mssql-2019-windows-dev-rtm-example.tar"

load the image from the tar file on the Windows server machine:
 docker load --input "C:\temp\mssql-2019-windows-dev-rtm-example.tar"

example to run the image
 docker run -d -p 14331:1433 -h sql2019 --name sql2019 -e "sa_password=y0ourS@pAssword" -e "ACCEPT_EULA=Y" -e attach_dbs="" --rm mssql-2019-windows-dev-rtm-example:latest

Be aware Windows containers are not yet supported on production and you are on your own with SQL 2019 images at this time unless you join the EA preview.


Microsoft SQL Server 2008 R2 Subscriptions I have an installation of SQL Server 2008 R2.

In the subscriptions section I do not see an icon or button to create data driven subscriptions; in the previous installation of SQL Server 2008, I do see this option.

Can anyone tell me where I can create data driven subscriptions?

Data Driven Subscriptions is a feature that is only available in SQL Developer or Enterprise Edition.


Is it ok to install a SSAS 2016 instance next to a SQL Server 2014 instance? Should I expect any problems installing a SQL Server Analysis Services 2016 tabular instance next to a SQL Server 2014 database engine? (e.g. due to an update of some shared components)

I'm mostly worried about any issues related to the running database engine.

Thanks.

It is best to keep a server dedicated to a single version of SQL Server and/or its components. You can run into connectivity issues or have components stop working as expected due to the mismatched version of the shared components. I recently had an instance where someone upgraded the SSIS components and prevented us from opening packages properly on that box any more.

Additionally, if you can avoid it, keep SSAS and the engine separated as both do not like to share resources properly. You could end up with performance issues if you do not configure things properly.


Expanding AlwaysOn Availability Groups with Replication Subscribers I have query regarding "AlwaysOn Availability Groups with Replication Subscribers"...

Can I add multiple publishers to a subscriber(which is part of availability group as a primary server)?
In which node can I configure distributer?
How do I configure the distributer?

Can I add multiple publishers to a subscriber(which is part of
  availability group as a primary server)?

Yes, this is the multiple publishers model and is supported.

  In which node can I configure distributer?

The distribution database is currently not supported for use in AlwaysOn Availability Groups. The only supported method for transparent distributor failover is AlwaysOn Failover Cluster Instances.

  How do I configure the distributer?

You can configure it through SSMS or T-SQL.


Mounting a database from database files copied from a previous installation I recently had on Oracle 11g Express database crash.

I have copied the .dbf files from C:\oraclexe\app\oracle\oradata\XE to another folder and reinstalled Oracle 11g Express.

The files are:

Control.dbf
sysaux.dbf
system.dbf
TEMP.DBF
UNDOTBS1.DBF
USERS.DBF

I want to remount the old database files, so that I can recover the database.

Can you please tell me how can I do this?

The information about database data files and online redo log files along with their paths is stored in the control file(s). The information about the control file path is stored in initialization parameter file (pfile) or in server parameter file (spfile).

So if you moved your data files, online redo log files and/or control file(s) you should update the paths in the appropriate files to reflect the changes before you can mount and open the database.

First, you alter you parameter file with new information after you successfully started the instance:

SQL> startup nomount;
SQL> alter system set control_files = '/oracle/data/control.dbf';

Then you mount the database so control file can be read and written:

SQL> alter database mount;

After database is mounted you can use RENAME FILE command to set the new pats to data files and online redo log files:

SQL> alter database rename file '/u01/data/sysaux.dbf'
  to '/oracle/data/sysaux.dbf';

SQL> alter database rename file '/u01/data/redo_1a.dbf'
  to '/oracle/data/redo_1a.dbf';

If your online redo is damaged – you didn't specify redo log files names in the list – and database was not shut down orderly, then data files and control file(s) are in inconsistent state and the only way to open your database is to restore and recover it from a previous backup.


secure way to connect with remote Mysql database I created my application using MySQL and it relies completely on the database I have around 190 tables, and I'm afraid of security breaches and another problem is for remote access I have to add the IP addresses of the devices that would use it, and since they change dynamically I will need to regularly update the remote access IP addresses, what is a better option for the connection with the remote MySQL?  Currently I'm hosting it on my account at hostgator.

For now, the application is under a testing period so, I have time to change my approach with the database connection, It's a desktop application created using c# and visual studio 2013.

My question might seem silly but this is my first real life application so, I don't want to be a mess!
Thanks

What are the clients?  PHP?  Java?  (etc, etc)  There are potentially serious hacks that can do things like "SQL Injection" through them.  That is perhaps more common than IP restrictions.

Do you have root only from some secure place?  Does it have a hardened password?

Are all the application 'users' granted access only to the desired database, and with only the desired permissions?  And are all the passwords hardened?

Where are the client passwords?  Visible in code?  That is, if a hacker can get to the code, are the passwords a useless defense?

SSL is only one issue.  If your provider does not allow for SSL, see if someone else does.  If not, then consider hosting the servers yourself; it's cheaper in the long run, but much more work.  And it would solve the IP problem.

Check skip_name_resolve.


Way to track data deleted from database I'm using Oracle 11g database in archivelog mode and auditing is not enabled.
Two days before, a few lines from a child table in my database got deleted. As the schema password was shared, I'm unable to track how it happened.I checked the trace files as well as queried tables v$log_history, V$SQL, DBA_HIST_SQLTEXT to get the history, but couldn't find any relevant information. Also, I reviewed the data by restoring the backup on this same date. But, since this data was created and got deleted on the same date, I'm unable to track this data even from the backup.
As I'm a newbie, I don't know how to tackle this issue. Are there any other methods/queries by which I can check if any delete operation had been performed in my database?

Provided Minimal Supplemental Logging was turned on before the incident occurred that you want to research (it is off by default since there is a minor performance hit by having it on) which you can check via: select supplemental_log_data_min from v$database;, then Logminer is the tool for this.

Start logminer:

execute dbms_logmnr_d.build(options=> dbms_logmnr_d.store_in_redo_logs);

Load the archive logs that you want to view into Logminer:

begin
   dbms_logmnr.start_logmnr(
     starttime => TO_TIMESTAMP('2017-03-02 07:00:00', 'YYYY-MM-DD HH24:MI:SS'),
     endtime => TO_TIMESTAMP('2017-03-02 15:00:00', 'YYYY-MM-DD HH24:MI:SS'),
     options => dbms_logmnr.dict_from_online_catalog + dbms_logmnr.continuous_mine);
end;

The above assumes that the ArchiveLogs are still on disk.  If you've backed them up, you'll have to unpack them from the backup and register them with the database first.

Query the Redo

select timestamp, operation, username, session#, scn, sql_redo
   from v$logmnr_contents where sql_redo like '<SOME SEARCH CRITERIA>';

End your logminer session

exec dbms_logmnr.end_logmnr;

You can read about Logminer from the docs.

If supplemental Logging is turned off, you may still be able to get the information you need by determining when the incident occurred using flashback queries to narrow down the exact time of the incident (provided you have a large enough Undo and retention policies are adequate):

select * from <SOMETABLE>
  AS OF TIMESTAMP TO_TIMESTAMP('2017-03-02 09:30:00', 'YYYY-MM-DD HH:MI:SS')

Then query the audit trail to find out when the shared schema user logged on around the above timeframe:

select * from dba_audit_trail where username = '<SOME USER>' order by timestamp;

The above assumes that you are logging audit information to the DB (show parameter audit_trail;).  You can read about auditing from the docs.


How output a json file in postgresql functions? I want to define a trigger in postgresql when updated a record in a db table, It output the record as a json file:

CREATE TABLE mytable(
   id serial primary key,
   first_name varchar(40) NOT NULL,
   last_name varchar(40) NOT NULL
);

CREATE OR REPLACE FUNCTION myFunction() RETURNS trigger AS
$BODY$
BEGIN

     // My Code

     RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql
SECURITY DEFINER;

CREATE TRIGGER myTrigger AFTER UPDATE ON mytable
    FOR EACH ROW EXECUTE PROCEDURE myFunction();

How can I do it?

The JSON representation of NEW in a trigger is just row_to_json(NEW), so that part is straightforward.

But writing into files on the server is a different story. It's only allowed to superusers because it gives the ability to corrupt or destroy all the instance's data.

I don't think there's a builtin function to write on the filesystem, but
the contrib module adminpack provides one:

pg_file_write(filename text, data text, append boolean)

data would be row_to_json(NEW) in your case.

The file should be inside the data directory, because:

  Only files within the database cluster directory can be accessed

although I think you may use symbolic links to circumvent that limitation,
if using a file system that supports them.

Here's a working example:

CREATE FUNCTION mytrigger()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
 PERFORM pg_catalog.pg_file_write('outputlog', row_to_json(NEW, true)::text, true);
 return NEW;
END
$function$

The file outputlog will be created in the data directory.


Refresh materalized view incrementally in PostgreSQL Is it possible to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed?

Consider this table & materialized view:

CREATE TABLE graph (
   xaxis integer NOT NULL,
   value integer NOT NULL,
);

CREATE MATERIALIZED VIEW graph_avg AS
SELECT xaxis, AVG(value)
FROM graph
GROUP BY xaxis

Periodically, new values are added to graph or an existing value is updated. I want to refresh the view graph_avg every couple of hours only for the values that have updated. However in PostgreSQL 9.3, the whole table is refreshed. This is quite time consuming. The next version 9.4 allows CONCURRENT update but it still refreshes the entire view. With 100s of millions of rows, this takes a few minutes.

What's a good way to keep track of updated & new values and only refresh the view partially?

You can always implement your own table serving as "materialized view". That's how we did it before MATERIALIZED VIEW was implemented in Postgres 9.3.

You can create a plain VIEW:

CREATE VIEW graph_avg_view AS
SELECT xaxis, AVG(value) AS avg_val
FROM   graph
GROUP  BY xaxis;

And materialize the result once or whenever you need to start over:

CREATE TABLE graph_avg AS
SELECT * FROM graph_avg_view;

(Or use the SELECT statement directly, without creating a VIEW.)
Then, depending on undisclosed details of your use case, you can DELETE / UPDATE / INSERT changes manually.

A basic DML statement with data-modifying CTEs for your table as is:

Assuming nobody else tries to write to graph_avg concurrently (reading is no problem):

WITH del AS (
   DELETE FROM graph_avg t
   WHERE  NOT EXISTS (SELECT FROM graph_avg_view WHERE xaxis = t.xaxis)
   )
, upd AS (
   UPDATE graph_avg t
   SET    avg_val = v.avg_val
   FROM   graph_avg_view v
   WHERE  t.xaxis = v.xaxis
   AND    t.avg_val <> v.avg_val
-- AND    t.avg_val IS DISTINCT FROM v.avg_val  -- alt if avg_val can be NULL
   )
INSERT INTO graph_avg t  -- no target list, whole row
SELECT v.*
FROM   graph_avg_view v
WHERE  NOT EXISTS (SELECT FROM graph_avg WHERE xaxis = v.xaxis);

Basic recipe

Add a timestamp column with default now() to your base table. Let's call it ts.

If you have updates, add a trigger to set the current timestamp with every update that changes either xaxis or value.

Create a tiny table to remember the timestamp of your latest snapshot. Let's call it mv:

CREATE TABLE mv (
   tbl text PRIMARY KEY
 , ts timestamp NOT NULL DEFAULT '-infinity'
); -- possibly more details

Create this partial, multicolumn index:

CREATE INDEX graph_mv_latest ON graph (xaxis, value)
WHERE  ts >= '-infinity';

Use the timestamp of the last snapshot as predicate in your queries to refresh the snapshot with perfect index usage.
At the end of the transaction, drop the index and recreate it with the transaction timestamp replacing the timestamp in the index predicate (initially  '-infinity'), which you also save to your table. Everything in one transaction.
Note that the partial index is great to cover INSERT and UPDATE operations, but not DELETE. To cover that, you need to consider the entire table. It all depends on exact requirements.


Does Couchbase/CouchDB allow me to move / skip the CRUD middle man from the server? So currently this is a common setup:

  Client -> Ajax -> Server CRUD -> Database

Does Couchbase allow me to do this?

  Client -> Ajax -> Database

This might be more technically correct:

  Client -> Ajax REST -> Database

Does this mean that I could just have the client directly interacting with the database, therefore reducing the load on the server? Theoretically this would make scaling a website more cost efficient.

If so, has this method been increasing in popularity usage? If not, why? It seems like this is a huge advantage.

I took a look at the REST API endpoint list on the Couchbase docs site.

I think it might be hypothetically possible. You could use the N1QL REST API to do inserts, selects, updates, and deletes.

I can't say for sure whether this is going to help scaling and load or not, but I do think you're going to have to address a number of other challenges: validation, authentication, authorization, business logic, and more.


How to add sequential numbers to an existing table? I'm seeking your support as I'm new in using Toad.
I need to insert sequence numbers based on columns subno and AR_INVDATE.
So if the same subno has more than one AR_INVDATE then I need a sequential number based on sorting the AR_INVDATE in ascending order.

Generate the numbers:

select subno, ar_invdate, row_number() over (partition by subno order by ar_invdate) as num from first_second_bill_v4;

Update the table with these numbers:

merge into first_second_bill_v4 t1 using (select subno, ar_invdate, row_number() over (partition by subno order by ar_invdate) as num from first_second_bill_v4) t2 on (t1.subno = t2.subno and t1.ar_invdate = t2.ar_invdate)
when matched then update set t1.num = t2.num;

Theoretically, not tested.