LEFT OUTER JOIN-ing the same table, grouping, summing and finding 0 or NULL I have a table where I record searches performed on my site, tbl_search. An id is automatically assigned to entries. If the searched phrase is more than one word, my system splits the words and performs another search on each of them, setting a column - called original - to the original search id.

For instance:

A user searches for 'foo bar'
The system searches for 'foo bar', gets the id that was created when recording that search, let's say the id is 5.
So now we have a record like this

 id | phrase   | results | original
----+----------+---------+----------
  5 | foo bar  | 0       | NULL

The system searches for 'foo' and 'bar' by themselves, and records the original id. So now the table looks like this:

 id | phrase   | results | original
----+----------+---------+----------
  5 | foo bar  | 0       | NULL
  6 | foo      | 0       | 5
  7 | bar      | 0       | 5

Any phrase with an original value was not specifically searched for, and the results should be grouped, summed, and counted as results for the original search.

I want to know all the searched phrases that got ZERO results, and order them based on how many times they were searched. Here's my SQL:

 SELECT
      s1.phrase,
      COUNT(s1.phrase) as count,
      SUM(s2.results) as sub_results
 FROM tbl_search s1
 LEFT OUTER JOIN tbl_search s2 ON s1.id = s2.original
 WHERE s1.results = 0
 GROUP BY s1.phrase, s2.phrase
 HAVING SUM(s2.results) = 0
 OR SUM(s2.results) IS NULL
 ORDER BY count desc

I'm very close I think, but I'm getting something like this:

 phrase  | count | results | sub_results
---------+-------+---------+------------
 foo bar | 14    | 0       | NULL
 map     | 5     | 0       | 4

I would expect any multiple-word searches to return 0 for sub_results, and any single word searches to return NULL, however, it seems like the opposite is happening. Like this:

 phrase  | count | sub_results
---------+-------+-------------
 foo bar | 14    | 0  // multiple-word search with no results, even in the aggregate sum of sub results
 map     | 5     | NULL // single-word searches have no sub searches, so they will be NULL

EDIT:
Sorry, StackExchange is being partially blocked by my company's firewall so I can't add any comments. @ypercube I don't want to count the sub searches (because they're not performed by the user) in the count. The count should only be how many times that phrase was searched by a user.

Basically I want all searches that got no results. If a search had sub searches with results, then I'm not worried about that because to the user, they got results.

Also, I don't want distinct id's because that would not be an accurate count. It should group the s1.phrases together and ignore the ids.

I made an SQLFiddle: http://sqlfiddle.com/#!3/a2913/2

I think you want this:

SELECT
      s1.phrase,
      COUNT(DISTINCT s1.id) AS count,
      SUM(s2.results) AS sub_results
 FROM tbl_search s1
   LEFT OUTER JOIN tbl_search s2 ON s1.id = s2.original
 WHERE s1.results = 0
   AND s1.original IS NULL
 GROUP BY s1.phrase
 HAVING SUM(s2.results) = 0
     OR SUM(s2.results) IS NULL
 ORDER BY count DESC ;

The COUNT(DISTINCT s1.id) is because you may have more than one searches with same phrase (but different id).

Tested at SQLfiddle.

If you don't want to see the sub_results column, you can use the simpler:

SELECT
     s1.phrase,
     COUNT(*) AS count
FROM tbl_search s1
WHERE s1.results = 0
  AND s1.original IS NULL
  AND NOT EXISTS
      ( SELECT *
        FROM tbl_search s2
        WHERE s1.id = s2.original
          AND s2.results > 0
      )
GROUP BY s1.phrase
ORDER BY count DESC ;


Strategy for making Postgres support multiple versions of client code I'm about to embark on an overhaul of our code for pushing to Postgres, and want to solicit some feedback before going on.

The setup here is that we've got a lot of software deployed with a non-Postgres database where we periodically push rows into a central Postgres database. (RDS 11.4) The chances of us updating all deployed copies of the code simultaneously are zero. So, we're always going to have multiple active versions in the field...sometimes a lot of versions apart. That's okay in itself, but it does make it unmanageable to bake naive INSERT statements into the client code. Which I have done. It has only bitten us slightly, but it could eventually become enormously painful. Any and all of the following DDL changes on our central Postgres can break pushes from deployed software that's on an old version:

Remove a field
Rename a field
Retype a field
Add a NOT NULL DEFAULT NULL field

Stating the obvious, I've got to move hard-coded/fixed references out of client code and into something else. Most folks likely have some sort of stack with an ORM, etc between their collector/field application and Postgres. We do not. So, with much help from people here on SO, I've built the following solution, using "hsys" as an example table.

For each table version create a custom type, like hsys_v1.

CREATE TYPE api.hsys_v1 AS (
    id uuid,
    name_ citext,
    marked_for_deletion boolean);

For each table and version, write an INSERT handling function that accepts an array of the custom type.

CREATE OR REPLACE FUNCTION ascendco.insert_hsys_v1 (data_in api.hsys_v1[])
  RETURNS int
AS $BODY$

-- The CTE below is a roundabout way of returning an insertion count from a pure SQL function in Postgres.
with inserted_rows as (
        INSERT INTO hsys (
            id,
            name_,
            marked_for_deletion)

        SELECT
            rows_in.id,
            rows_in.name_,
            rows_in.marked_for_deletion

        FROM unnest(data_in) as rows_in

        ON CONFLICT(id) DO UPDATE SET
            name_ = EXCLUDED.name_,
            marked_for_deletion = EXCLUDED.marked_for_deletion

        returning 1 as row_counter)

    select sum(row_counter)::integer from inserted_rows;

$BODY$
LANGUAGE sql;

The idea here is that when I change the table, I'll be able to create the a hsys_v2 type and an insert_hsys_v2 (hsys_v2[]) function to match. Then old clients can continue pushing in the old format, so long as I rewrite insert_hsys_v1 to map/convert/coerce things to the new table format.

I wrote a GUI to grab my table definitions and put a code generator on top a few weeks ago. And then I stopped. I realize I'm wondering if I'm missing something I should consider, and am hoping for someone to point out a hole in this strategy. I'm not being lazy about reaching out to local programmers....there aren't any. (I'm in rural Australia.)

If there's nothing wrong with this strategy, I'll do the overhaul. As a bonus, the work so far made it easy to add in code builders for custom casts and views. Not sure if they'll be useful or not, but I'm generating them at the same time.

I cannot immediately see a problem with your idea, except that you have to change all code to use functions instead of data modifying SQL statements.

I can think of an alternative as follows: When you install your schema changes, also create a number of views that look just like the old tables and “do the right thing”:

SELECTs on the view return the same data you would get in the old version.
The views have triggers for INSERT, UPDATE and DELETE that perform the correct actions on the underlying tables.

Since the names of the views will collide with the table names, place the views in a different schema. You could pick a schema name based on the application version, and you can use search_path to assign different default schemas to the two application versions.

Once all clients are using the new version, you can drop the schema with the views.


How to write this JSON-preparing, non-scalar query as scalar? I use something like the following to prepare a block of json:

SET @json = (SELECT
  @varchar1 as MyVarchar1,
  @varchar2 as MyVarchar2,
  GetUtcDate() as MyTime FOR JSON PATH);

Visual Studio complains that it's a "Non scalar subquery in place of scalar" - is there a way to write it so it doesn't elicit a warning?

In your scenario, because you are using variables, it is sure that your query returns only one row (scalar), so you can ignore this warning.

To suppress the warning, you could add a TOP 1 to your query, but it is not necessary.

SET @json = (SELECT TOP 1
  @varchar1 as MyVarchar1,
  @varchar2 as MyVarchar2,
  GetUtcDate() as MyTime FOR JSON PATH);

You can find more on https://documentation.red-gate.com/codeanalysis/execution-rules/ei003


unable to login oracle as sysdba I just got 11gR2 running and was able to conn as sysdba. I shutdown and started up the database to mount a new pfile. Now, I cannot login as sysdba. My parameter for password file is:

 *.remote_login_passwordfile='EXCLUSIVE'

I am using sqlplus within the server. This is not a remote connection.

[oracle@oel56 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 5 22:50:46 2013

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

SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges

Here's some more information:

[oracle@oel56 ~]$ grep -E "ine SS_DBA|ine SS_OPER" $ORACLE_HOME/rdbms/lib/config.c
#define SS_DBA_GRP "oracle"
#define SS_OPER_GRP "oracle"
[oracle@oel56 ~]$ id oracle
uid=500(oracle) gid=500(oracle) groups=500(oracle),54321(oinstall),54322(dba),54323(oper) context=user_u:system_r:unconfined_t

The problem was I was using the variable TWO_TASK. Once I unset the variable, I nolonger had issues logging in a sysdba.

$ unset TWO_TASK
$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 6 02:51:55 2013

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup;
ORACLE instance started.

Total System Global Area 1707446272 bytes
Fixed Size          1336988 bytes
Variable Size         989858148 bytes
Database Buffers      704643072 bytes
Redo Buffers           11608064 bytes
Database mounted.
Database opened.
SQL> exit

I found the answer here.


SQL Server 2005 - Generate and Bulk Insert 3 million rows across 2 tables I have a task to programmatically generate 3 million different rows of data and insert them into a database table for test purposes.

I have written a VB.NET program that handles the generation of row data which can then be either INSERT-ed a row at a time straight into my database or written as a comma delimited row in a .csv file.

My issue is that some of the rows required a joining row added in another table, and the join is done an IDENTITY column value from my first table, and I won't know what that value is until after the row has been inserted.

So I have been using the VB.NET program to insert rows one by one, adding the joining row in the other table if required. This was working at a reasonable speed until the main table reached around 2 million rows, at which point it started taking much longer to INSERT a new row.

What is the best practice for achieving my goal? I have looked a little at Bulk Insert and OPENROWSET, but cannot figure out how to use them to populate two tables with different amounts of rows and a joining column which I don't know the value of.

Your table structure looks like this?:

Table1
id (identity)
data1
data2
data3

Table 2
id (identity)
t1id (fk to table1)
data1
data2
...

-- Main Table insert
INSERT INTO table1 (data1,data2,data3)
VALUES('dummy1','dummy2','dummy3');

--get last inserted id
var=SELECT SCOPE_IDENTITY()

-- dependent insert into table2
INSERT INTO table2 (t1id,data1,data2,...)
VALUES(var,'dummy1','dummy2','...');

Look at this answer.
If you return the id from the newly inserted row of Table1 you could use that to insert the dependent row into Table2 without querying Table1 for every row.
If the select for the newly generated id is your bottleneck this should help. If you're full scanning your main table for every row, indexing the id column, if not already done, could also decrease the time needed for the lookup.


How to see restore history of a deleted database? For example, I had a database and I want to see which physical backup file was used to restore into it. The problem is that the database was deleted. How can I see the record?

Depending on how the database was dropped, your luck will vary considerably.

If the database was dropped simply by using the DROP DATABASE command, then the restore history will be available to you.

However, if the database was dropped via the SSMS GUI after the restore, then you may be out of luck. In SSMS, the default is to select the option for "Delete backup and restore history information for databases". When this option is selected, the backup & restore history for the dropped database(s) will be removed from msdb:

However, if you either uncheck that box, or do the drop from T-SQL, then your backup & restore history will be available.

CREATE DATABASE Floop;
BACKUP DATABASE Floop TO DISK = 'C:\temp\Floop.bak' WITH INIT;
RESTORE DATABASE Floop FROM DISK = 'C:\temp\Floop.bak' WITH REPLACE;
DROP DATABASE Floop;

To see the restore history, simply query msdb.dbo.restorehistory:

SELECT *
FROM msdb.dbo.restorehistory
WHERE destination_database_name = 'Floop';

The details of the restorehistory table are available in the docs. Essentially, it contains the database that was restored, information about who restored it, the type of restore, and the restore point.

If you want information about the backup that was restored, you can join to other backup tables. When a database from a different instance is restore, SQL Server copies the backup information from the backup header in the file into the backup tables.

SELECT
        RestoreDate = rh.restore_date,
        RestoredDb  = rh.destination_database_name,
        RestoreUser = rh.[user_name],
        RestoreType = rh.restore_type,
        ReplaceDb   = rh.[replace],
        RecoverDb   = rh.[recovery],
        BackupSourceServer  = bh.machine_name,
        BackupSourceDb      = bh.database_name,
        BackupFinishDate    = bh.backup_finish_date,
        BackupFile          = bmf.physical_device_name
FROM msdb.dbo.restorehistory    AS rh
JOIN msdb.dbo.backupset         AS bh  ON bh.backup_set_id = rh.backup_set_id
JOIN msdb.dbo.backupmediafamily AS bmf ON bmf.media_set_id = bh.media_set_id
WHERE rh.destination_database_name = 'Floop';


Offline index rebuild - does it also create a whole new copy of the index? As I know, while online index is rebuilding, SQL Server virtually creates an index and then at the end it swaps the existing old index with the new created one and then finally drops the old index.

I would like to know if these operation are the same for offline index rebuild. Will SQL Server create virtually an index and then swap it with the existing one?

Consider what Paul Randal says in his post on Search Engine Q&A #19: Misconceptions around index rebuilds (allocation, BULK_LOGGED mode, locking):

  Index rebuild (whether online or offline, and at least as far back as 7.0) will create a new copy of the index before dropping the old copy.

He reiterates that point in SQLskills SQL101: REBUILD vs. REORGANIZE:

  Rebuilding an index requires building a new index before dropping the old index...

Finally, the documentation also discusses this on the page Reorganize and rebuild indexes:

  Rebuilding an index drops and re-creates the index.

So the answer to your question is yes, a new index is created and the old one is dropped when rebuilding an index offline (although it will generally require less disk space than the online version of the operation).


SQLAnywhere: How to configure length of message output to console/client when using isql If got an issue with message command (as well as print) on SQLAnywhere.

When running message as a single command it appears that it's completely put everything where it should be.

But when using it inside a procedure for e.g. debugging output of dynamic SQL it appears, that it is truncated at about 300 characters.

I found a solution at least for my basic issue, which is almost too simple:

    -- ... some code
DECLARE @generatedProcedureCall LONG VARCHAR
SET @generatedProcedureCall = ... contents
SELECT @generatedProcedureCall

This is allowing me to print out >4k characters for e.g. debugging.

When writing to file e.g.

SELECT @generatedProcedureCall INTO #logfoo
UNLOAD #logfoo to 'some/file'

could do the trick.

Maybe not the cleanest one, but as mentioned it solved at least my issue.

The reasons seems to be, that print as well as message cannot handle string >4k of text. print was just failing with an error whereas message catted my string to 255 characters.


Should an account be set up and used solely to install SQL Server? I'd like to follow best practices.
However, is it overkill to create a new service account (with minimal access and no special rights) and then use that account to install SQL Server?
Are there downsides if I do the installation under my administrator account?
Side note: I already have separate service accounts that will run SQL Server engine and SQL Server Agent service.

There is no need or benefit to installing under a dedicated account as long as you are following other account-based best practices.


Does SQL Server 2017 CU10 include CVE-2018-8273 hotfix? Microsoft released SQL Server 2017 CU10 KB4342123 (14.0.3037.1) yesterday. I tried looking through the Hotfixes included list, but didn't see any reference to the recently released security update for the remote code execution vulnerability hotfix KB4293805 CVE-2018-8273 (14.0.3035.2).

How can we determine if SQL Server 2017 CU10 includes the security hotfix KB4293805 CVE-2018-8273 or not?

Is the higher version number of CU10 enough to determine that?

NOTE: I've already installed the CVE-2018-8273 fix onto CU9.

Yes, the security fix is in the CU. Direct but private comment from a reliable source within Microsoft:

  Security fixes always roll-up to any subsequent CU. That's been the case for years.

And from another colleague at Microsoft:

  all CU servicing releases for a given baseline are 100% cumulative of all previous Security Updates, CUs, and On Demand hotfixes released to date for that baseline (RTM or SP).  This is mechanically mandated by the fact that we use the same physical CU source repository, we do not have any fix specific release repositories, and we do not remove fixes once they are released.

With very few historical exceptions, cumulative updates always include the fixes in lower builds from the same branch, security or otherwise. Prior to SQL Server 2017, this could be different because of the way service packs were versioned (e.g. service pack 2 RTM has a higher build number than sp1 cu28 even though the latter is 6 months newer). But that is just a cosmetic thing - it still holds true for the branch, but it doesn't always hold true if you are ignoring service pack level and only comparing @@VERSION numbers.

  Has Microsoft explicitly documented anywhere that the security fix is included in CU10? I'd like to be able to prove to management that the fix is there.

I've asked multiple times for more transparency about what fixes are included (or not included) in a specific CU, especially when something like this happens -- a security hotfix with its own set of issues was released between CUs. They have taken the feedback and I do hope to see some official documentation at least on the Release Services Team blog posts announcing each new release.

All I can say is that's not a fast-moving machine over there, and both automated processes and lawyers can sometimes get in the way of what can be disclosed in automatically-generated content like CU KB articles. For now you're going to have to take my (and their) word for it.


What is difference between Lease Timeout and HealthCheck Timeout in AlwaysON? this question was asked twice here but still there is no satisfied answer. Could someone explain the main difference between these two timeouts?

Google took me straight here :-
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-lease-healthcheck-timeout?view=sql-server-ver15


Does an excessive table violate normalization rules? Here is an example of some tables that exist in a database where I work. The data isn't actually around schools, but the structure is identical.

There are four tables:

** School **
School Id, School Name

** ClubType **
ClubType Id, ClubType Name

** Club **
Club Id, School Id, ClubType Id

** Student **
Student Id, Name, Club Id

Knowing that the Club table will never have additional columns (because the real data isn't actually about school clubs),

I believe a clearly better design, eliminating the Club table to avoid joins, would be:

** School **
School Id, School Name

** ClubType **
ClubType Id, ClubType Name

** Student **
Student Id, Name, School Id, ClubType Id

Edit: We also know that each club id may only have one type. The relationship from Club to ClubType is 1 to 1.

My question is, does the first example violate some known rule of database normalization or some other mathematical principle? Or is it just a case of poor design?

By changing to your proposed solution you lose information from the database. The existing solution says what clubs can exist in a particular school irrespective of anyone actually being in that club at any point in time. The proposed solution requires someone to join the club before the club comes into existence (i.e. before a row is written to the database).

As a practical implication think of sign-up sheets. It's the day before term starts. The principal wants a sign-up sheet on the noticeboard for each club so students can join. It would be wasteful to print a sheet for all club types and let students join clubs which will never exists in this school. Today, before term starts, there are no students so your proposed solution will not work. The existing solution, however, allows the principal to offer, say, a soccer club but not a water polo club.

I realise you're using the analogy of school/ club as a proxy for your real situation, and you're going to have to translate all I say to the actual problem and that the comments I'm about to make may not apply. That's the price you pay for analogies. If your actual "school" can choose from all "club type" all the time then your proposed solution is adequate.

  Or is it just a case of poor design?

It is not poor design. Neither is it good design. It is a design which implements some affordances but precludes others. It was written for reasons to which we do not have access. It was written with the knowledge available at the time. Likely it passed a great many tests and active, production usage.

Now, the world may have moved on since then. The business rules may have changed; the implementation team's understanding may have improved. That design may have a performance characteristic which is not acceptable on your hardware with your workload given your data. It may be appropriate to change that design.

Normalization is about how non-key columns depend on key columns within a single table. It shows how you can change the schema so changing a single value in the real world will update a single column in a single row within the database. It has nothing to say about implementing scenarios from the real-world problem at hand.

I understand your current Student table to mean "a person as a member of a club". For that meaning the primary key will be {student id, club id}. In your current implementation the table is not normalized because Name depends only on student id and not on club id. The normalized solution would be to change the semantics of table Student to "A person" (columns student id, name) and create a new table ClubMember with columns {student id, club id}.

  The relationship from Club to ClubType is 1 to 1.

I doubt it. What are possible values of ClubType? Maybe "soccer" or "yoga"? I should think a great many schools would like to have a soccer club. Perhaps

Each Club     is-this-schools exactly one   ClubType
Each ClubType is-offered-in   zero or more  Club

As an ERD:

ClubType --< Club >-- School


IMPORT FOREIGN SCHEMA fails with Password error for 127.0.0.1 but not for remote IP (of same server) I have a Postgres server in Google SQL Cloud with multiple databases. I want to create a FOREIGN DATA WRAPPER between them.  If I use the server's remote IP it works:
CREATE SERVER "some_db_fdw" FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '34.94.1.23', dbname 'some_db');
CREATE USER MAPPING FOR some_user SERVER "some_db_fdw" OPTIONS (user 'some_user', password 'some_password');
IMPORT FOREIGN SCHEMA public LIMIT TO (my_table) FROM SERVER "some_db_fdw" INTO public;

...but if I use the local IP (127.0.0.1) in place of 34.94.1.23 (sample IP only) it gives me the error:
ERROR: password is required
DETAIL: Non-superuser cannot connect if the server does not request a password.
HINT: Target server’s authentication method must be changed.

Why? I'm providing the password in the user mapping.  In fact if I omit the password there then the error changes to:
ERROR:  password is required
DETAIL:  Non-superusers must provide a password in the user mapping.

So it seems to know that it should be getting the password from the user mapping.
The reason I can't just use the remote IP is that we require certs so if I enable "Allow only SSL connections" using the remote IP fails because I don't have certs within psql (I have them on the machine I connect with or I wouldn't be able to connect at all, but since this server is in SQL Cloud, I don't have any filesystem access and can't provide the DB itself with certs to connect to the other DB - thus my attempt to use 127.0.0.1 to avoid this issue).

The problem is that when you connect locally, PostgreSQL does not demand a password. So while the user mapping might define a password, that password can't be used as it is never asked for.  If it doesn't ask for a password but can still connect, that probably means it is relying on the identity of the process doing the connecting to authenticate it.  Since the process doing the connecting is owned by a service account which is running PostgreSQL (usually 'postgres'), you are leveraging an identity you don't have a right to leverage. For security reasons, this must be disallowed.
Since you are using a hosted service, you are at the mercy of the host to fix it.  There are several ways they could fix this.  There are no ways you can fix it without their cooperation, other than to stop using their service.

"Allow only SSL connections" using the remote IP fails because I don't have certs

SSL certs are generally going to have the host name, not the host IP address.  Did you try with the host name?  It might be that Google automatically installs their own certs (or that you can talk them into doing so)


How to uniquely identify SQL instance I need to find a way how to identify SQL Instance no matter what changes were done to SQL Server and\or Windows Server where it is running, because we're collecting remotely information about instances into our operational database and still encountering way we're identifying it is not that unique as it might look.

In example, if I have server WIN1\SQL and due to server refresh will install on different server WIN2\SQL, migrate data and swap server names so that WIN2\SQL will become WIN1\SQL and vice versa, I need to be able identify that current WIN1\SQL is different from previous WIN1\SQL disregards OS version and or SQL version\patch level

Already tried following:

NetworkName\InstanceName - that worked until server hostname\network name has been changed due to server refresh as described above

SQL Server SID - I was searching for some SID of SQL Installation like Windows has, but seems nothing like that really exists

SID of ##MS_SQLAuthenticatorCertificate## - this was last best shot I found, scanned hundreds of SQL Servers and it was really unique - thought I found it finally, until I applied Service Pack to one server and find out SID has changed

Any idea how to uniquely identify SQL Instance (like SQL Instance SID) would be appreciated, because I'm getting lost

EDIT: I'm not administrating all SQL servers. Modifications on out of scope devices cannot be done, thus looking for some "already built-in" way that would work on SQL2005+

Well, it is somewhat possible to have an Instance GUID, but only if you are including [msdb] in your backup / restore process. Assuming that this is indeed the case, then just check the service_broker_guid field in sys.databases for the [msdb] database:

SELECT [service_broker_guid]
FROM   sys.databases
WHERE [name] = N'msdb';

It will be different across each instance, but should be consistent across restores of backups of the same [msdb] database. This even appears to hold true for the Express editions. And while I have not tracked this value across updates and Service Packs, I don't see why it would change unless forced to manually.

If you want to create new SQL Server instances from a "master" set of backup files (including [msdb]) and need the new instances to be seen as different, then just run the following after the initial restore of the system databases:

ALTER DATABASE [msdb] SET NEW_BROKER;

NOTES:

The service_broker_guid field in sys.databases for the [master] and [model] databases is always set to 00000000-0000-0000-0000-000000000000 and cannot be changed via ALTER DATABASE [{database_name}] SET NEW_BROKER;.
If you are using this technique to distinguish instances of SQL Server Express LocalDB, then the the service_broker_guid field in sys.databases for the [msdb] database will initially contain the same value for all newly created instances. In this case, you just need to execute ALTER DATABASE [msdb] SET NEW_BROKER;, just like when creating new instances from a backup restore.


Bypass truncated data type error in `CREATE TABLE AS` I am attempting to recreate the code from this excellent tutorial in MariaDB 10.2 to try and build nested sets from an existing adjacency list. (The goal being to ingest changes to the adjacency list one at a time and then rebuild the nested sets at certain time intervals).

However, I've hit a snag with this bit:

SortPath =  CAST(
                    CAST(anchor.EmployeeID AS BINARY(4))
                AS VARBINARY(4000)) --Up to 1000 levels deep.

I've switched it to this:

CAST(CAST(anchor.EmployeeID AS BINARY(4)) AS BINARY(4000)) AS sort_path ## Also with 8000

I also tried CAST(anchor.EmployeeID AS Binary(4)) AS sort_path

All approaches have thus far yielded a variant of this error message:

Truncated incorrect DOUBLE value: '1\x00\x00\x00'

EDIT

The full sql statement being run is this:

##===== Conditionally drop Temp tables to make reruns easy
DROP TABLE IF EXISTS hierarchy;

##===== Build the new table on-the-fly including some place holders
CREATE TABLE hierarchy AS

WITH RECURSIVE cteBuildPath AS
( ##=== This is the "anchor" part of the recursive CTE.
     ## The only thing it does is load the Root Node.
 SELECT anchor.tree_user_id,
        anchor.upline_id,
        1 AS hlevel,
        CAST(anchor.tree_user_id AS BINARY(4)) AS sort_path ##Up to 1000 levels deep.
  FROM example.tree AS anchor
  WHERE upline_id IS NULL ##Only the Root Node has a NULL ManagerID
  UNION ALL
 ##==== This is the "recursive" part of the CTE that adds 1 for each level
     ## and concatenates each level of EmployeeID's to the SortPath column.
 SELECT recur.tree_user_id,
        recur.upline_id,
        cte.HLevel + 1,
        cte.sort_path + CAST(recur.tree_user_id AS BINARY(4))
   FROM example.tree AS recur
  INNER JOIN cteBuildPath AS cte
          ON cte.tree_user_id = recur.upline_id
) ##=== This final SELECT/INTO creates the Node # in the same order as a
     ## push-stack would. It also creates the final table with some
     ## "reserved" columns on the fly. We'll leave the SortPath column in
     ## place because we're still going to need it later.
     ## The ISNULLs make NOT NULL columns.
 SELECT IFNULL(sorted.tree_user_id,0) AS tree_user_id,
        sorted.upline_id,
        IFNULL(sorted.hlevel,0) AS hlevel,
      #  IFNULL(CAST(0 AS INT),0) AS left_bower, ##Place holder
      #  IFNULL(CAST(0 AS INT),0) AS right_bower, ##Place holder
      (@n := @n + 1) AS node_number,
      #  IFNULL(CAST(0 AS INT),0) AS node_count, ##Place holder
        IFNULL(sorted.sort_path,sorted.sort_path) AS sort_path
   FROM cteBuildPath AS sorted
   JOIN (SELECT @n := 0) n ON 1=1
   #ORDER BY sorted.sort_path
 #OPTION (MAXRECURSION 100) ##Change this IF necessary
;

What is the correct way to duplicate the desired logic in MariaDB 10.2?

After much googling, I came to realize that the error message was exactly correct though somewhat vague.

Truncated incorrect DOUBLE value: '1\x00\x00\x00' meant that somewhere in my query, my CAST BINARY was being converted back to a DOUBLE data type.

Once I figured that out, re-reading the query made me consider how this little gem was working: cte.sort_path + CAST(recur.tree_user_id AS BINARY(4)). Turns out, based on a small sample query that the BINARY value was being converted so addition could be performed.

Switching to CONCAT(cte.sort_path, CAST(recur.tree_user_id AS BINARY(8))) appears to have corrected the problem.


Does creating / updating statistics have an impact on the transaction log? Does creating or updating statistics, assuming on a large table, create significant (or at all) transaction log churn? I would think not as it does not change the underlying data or structure.

If it does, what would be a good method to verify?

It doesn't really in any measurable fashion
Statistics are stored as histograms, with at most 200 entries per index/column. This is trivial, compared to the actual column or index.
See https://www.simple-talk.com/sql/performance/sql-server-statistics-questions-we-were-too-shy-to-ask/

Where are the statistics actually stored? Do they take up much space? Can I save space by only having the essential ones?
The statistics themselves are stored within your database in a series of internal tables that include sysindexes. You can view some of the information about them using the system views sys.stats and sys.indexes, but the most detail is gleaned using a function, DBCC SHOW_STATISTICS. The statistics themselves take very little space. The header is a single row of information. The density is a set of rows with only three columns, equal in the number of rows to the number of columns defining the key columns of the statistic. Then you have the histogram. The histogram is up to 200 rows and never exceeds that amount. This means statistics do not require much room at all. While you can save a little bit of space by removing unneeded statistics, the space savings are too small to ever be worthwhile .

Because the system tables are updated, then there is some minor data change that will be logged, but as I said it is trivial and not worth considering at all


Index rebuild canceled because of .mdf growth I have the Standard Edition of SQL Server 2012 and used a script to rebuild index. I ran the query and then I saw that the .mdf file for the database started the grow a lot.

I chose to cancel/stop my query that run the rebuild of the database.

I checked in database what happened with the following queries:

SP_who2

select cmd,*
from sys.sysprocesses
where blocked > 0

I saw that a process where blocked so I chose to kill that one.

The query/spid became marked with cancel/rollback in SP_who2.

It took a lot of time to rollback the query.

The table that was rebuilding was a big with over 4 million rows and a fragmentation of 99%.

My questions are:

Why did the .mdf grow that fast? About 1 gb/(10/sec)
When I stopped my query, can the database get inconsistent in any way?
Can the data in the database have been changed because of this?

Thanks in advance!

Your question is bit incomplete as you have not posted the query used to rebuild the index. A lot depends on what options you selected during index rebuild. Did you use the GUI with default options? The size of the mdf file would also be affected by the Fill Factor value chosen during index rebuild.

  1.Why did the .mdf grow that fast? About 1 gb/(10/sec)

Gerenal answer would be "YES", MDF file does grow during index rebuild because an index rebuild requires creating a new index first, and that would be created on the same drive where the data file resides, unless you have used SORT_IN_TEMPDB option, which would force intermediate sorts to be done in tempdb instead. Only after the new index is created will the old one be dropped so you can now see why space grew. Also, the space created by dropping the old index would not be released immediately. To release space you would need to run dbcc shrinkfile, although it is not advised.

If space is an issue on the drive where the data file resides you can use sort_in_tempdb option while rebuilding indexes. Please note that the intermediate sort results that are used to build the index are stored in tempdb. If tempdb is on a different set of disks to the user database, this may reduce the time needed to create an index. However, this increases the total amount of disk space that is used during the index build.

  2.When I stopped my query, can the database get inconsistent in any way?

No, the database won't be in an inconsistent state. Rebuild is considered a single transaction by SQL Server so either it completes fully or things are returned to what they were before the index rebuild.

  3.Can the data in the database have been changed because of this?

No, rest assured data cannot change.

I strongly suggest you to read the Blog Why data file increased after index rebuild
Please also read Misconceptions Around Index rebuild by Paul Randal

My other question would be did you try Ola Hallengren solution for index rebuild and stats updating? It's a good one and uses an intelligent script.


What is wrong with the following Python code -- trying to insert into a database? This works in MySQL Workbench:

insert into nametest (name,surname) values('John''Krailling');

I am trying a get similar result with Pycharm with the following code:

import MySQLdb

db = MySQLdb.connect(host="localhost", # your host, usually localhost
                 user="root", # your username
                  passwd="iop", # your password
                  db="new_schema")# name of the data base
cur=db.cursor()

cur.execute("insert into nametest(name,surname) values('John', 'Green')")

The  entry is added in Pycharm, but not in the table in the underlying database.

ok this is my table:

mysql> desc name_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| fname | varchar(20) | NO   | PRI | NULL    |       |
| sname | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

and this is the code to insert the data:

    import mysql.connector

    db = mysql.connector.connect(host="127.0.0.1", # your host, usually localhost
             user="root", # your username
              passwd="", # your password
              db="music")# name of the data base
    cur=db.cursor()

    q = "insert into name_test(fname,sname) values(%s, %s)"
    v = { "bob", "smith" }

    cur.execute(q,v) #runs the query
    db.commit() #ensures that its saved
    cur.close() #closes the cursor
    db.close() #closes the connection

source: https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html

hope this helps :)


Copying Oracle 11g express Database from one server to another Basically, I am a MS-SQL developer,and relatively new to Oracle.

Even after great deal of research, I was unable to find any way of copying entire database from one server instance to another.

In MS-SQL this is usually done through "backup database" and "restore database" sql query. We can even use external tools such as Management studio to create scripts.

But, How this can be done in Oracle 11g express? Any ideas??

You do not state which version you use, so I assume you use version 10.x or 11.x.

Do a full export of your database using Oracle Data Pump, existing since Oracle 10: expdp (export dump) and then re-import it into another database using impdb (import dump).

Use expdp help=y to get the full set of parameters. Parameters are given in the form param=value, not --param=value as one would expect.


oracle XE 18c inmemory settings I try to this in oracle XE 18c.
I cant't alter inmemory_size property but can alter sga_target.
Who relieve me?
SQL> alter system set inmemory_size=200M;
alter system set inmemory_size=200M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set sga_target=1024M;

System altered.

inmemory_size can not be modified online. Modify it in the spfile:
alter system set inmemory_size=200M scope=spfile;
Then restart the database.


Enable or disable a task based on parameter I have an SSIS package called Main_Flow that executes several other packages in order.

What I'd like to do is to somehow parametrise the enabling/disabling of packages in the main flow. This way I can choose to skip certain tasks based on value of parameters upon execution.

How can I ensure that a task can be skipped if DisabeTaskA parameter is set to TRUE?

It is important to emphasize that if I disable a package in the middle of the linear sequence, although this task would be skipped this does not mean any of the next packages should be skipped (unless I opted to disable them). That is, the skip shouldn't break out of the sequence.

Actually this is way simpler than I thought.

Right-click the task and choose Parametrize...
Choose the property to be parametrized. From there you can choose an existing parameter or create a new one. It is easy-peasy.

In this case I created a boolean parameter and gave it the default value of FALSE. Then I bound it to the property Disable of the task. When the task is disabled it skips that task but any connections after the task continues.


SSIS or SQL Server way of sectioning a flat file I have a few hundred thousand flat text files which contain a single column of values. The first 40 values are parameters followed by between 4000 - 8000 rows of sensor data. The files are imported once, will never change and will be accessed by parameter selection for further analysis/calculations.

I think using two tables (one for parameters, one for sensor data) would be a good schema. So I need to split the file after import, transpose the parameter rows to columns and then load all the sensor data into a single table with a key back to the row in the parameters table.

I looked at pivot and unpivot but that won't let me transpose a single row to columns.

Any help on how to do this in SSIS or SQL and thoughts on my schema appreciated I'm not sure if the database will be responsive when I get a few million rows in the sensor data table.

using ssis and some c# / vba

add a foreach loop to iterate over the files
create a variable to use for the key (increment on each iteration)
in the loop add a dataflow consisting of

a script transform (source) to read first 40 lines from the file and pivot
a derived column to add the key (from var)
a destination to the table

in the loop add a dataflow consisting of

a script transform (source) skipping the first 40 lines and reading the rest
a derived column to add the key (from var)
a destination to the table

[optional] delete or move the file with a file task

the code for pivoting would read the first 40 lines into an array then just set the output columns mapping to the array's index.

consideration may need to be given to insure the key remains unique on subsequent runs and may need initialising to the max(key) + 1 from exiting data, use an sql task for this.


The curious case of HADR_SYNC_COMMIT waits We are noticing an interesting pattern for HADR_SYNC_COMMIT waits in our environment. We have a three replica; one primary, one sync secondary and one async secondary in a datacenter and we just added three more ASYNC replicas in another datacenter (~2400 miles apart).

Ever since, we have started to notice an enormous increase in HADR_SYNC_COMMIT waits. When we look at the active sessions, we see a bunch of COMMIT TRANSACTION queries waiting on the SYNC replica

From the screenshot, we can clearly see there is a jump in HADR_SYNC_COMMIT wait on June 29, and we eventually dropped 'two' of the three async replica in the remote datacenter sometime in the noon on July 1st. That dropped the wait times considerably along with it.

What we have checked so far – Log send queue, Redo queue, last hardened time and last commit time on the remote replicas. We have continuous bursts of small transactions during the business hours, and therefore the send queues are pretty small at a given timestamp (anywhere between 60KB and 1MB). The remote replicas are almost in sync, there is very little difference between the last commit time and last hardened time for any individual lsn on the replicas.

The network pipe is 10G and we modified the transmit buffer size from 256 megs to 2 gigs, this was made under the assumption that the network was dropping packets and re-transmitting them; either way that didn’t seem to help much.

So, I’m wondering what does the ASYNC replicas have to do with HADR_SYNC_COMMIT waits? Shouldn’t the SYNC replica depend alone on this wait type, what am I missing here?

First the description of the wait event that your question is regarding is:

  Waiting for transaction commit processing for the synchronized secondary databases to harden the log. This wait is also reflected by the Transaction Delay performance counter. This wait type is expected for synchronized availability groups and indicates the time to send, write, and acknowledge log to the secondary databases.

  https://msdn.microsoft.com/en-us/library/ms179984.aspx

Digging into the mechanics of this wait you have the log blocks being transmitted and hardened but recovery not completed on the remote servers.  With this being the case and given that you added additional replicas it stands to reason that your HADR_SYNC_COMMIT may increase due to the increase in bandwidth requirements.  In this case Aaron Bertrand is exactly correct in his comments on the question.

Source: http://blogs.msdn.com/b/psssql/archive/2013/04/26/alwayson-hadron-learning-series-hadr-sync-commit-vs-writelog-wait.aspx

Digging into the second part of your question about how this wait could be related to application slowdowns.  This I believe is a causality issue.  You are looking at your waits increasing and a recent user complaint and drawing the conclusion potentially incorrectly that the two have a relationship when this may not be the case at all.  The fact that you added tempdb files and your application became more responsive to me indicates that you may have had some underlying contention issues that could have been exacerbated by the additional overhead of the implicit snapshot isolation level overhead when a database is in an availability group.  This may have had little or nothing to do with your HADR_SYNC_COMMIT waits.

If you wanted to test this you could utilize an extended event trace that looks at the hadr_db_commit_mgr_update_harden XEvent on your primary replica and get a baseline.  Once you have your baseline you can then add your replicas back in one at a time and see how the trace changes.  I would strongly encourage you to use a file  that resides on a volume that does not contain any databases and set a rollover and maximum size.  Please adjust the duration filter as needed to gather events that match up with your waits so that you can further troubleshoot and correlate this with any other teams that need to be involved.

CREATE EVENT SESSION [HADR_SYNC_COMMIT-Monitor] ON SERVER  -- Run this on the primary replica
ADD EVENT sqlserver.hadr_db_commit_mgr_update_harden(
    WHERE ([delay]>(10))) -- I strongly encourage you to use the delay filter to avoid getting too many events back, this is measured in milliseconds
ADD TARGET package0.event_file(SET filename=N'<YourFilePathHere>')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO


Selecting from a table contains a XML column I have received an Excel file (verion 2010) and based on the requirement I was asked to save it as an XML file and then import into SQL data ready to be queried in normal SELECT statements.

Below is the list of steps I followed but now that I have a SELECT on the data, no records is returned. I'm not sure whether it's due to the conversion that I did to get an XML file from an Excel file, or there is a problem in my OPENXML query.

To obtain a XML file from the .xlsx file, I opend the file in
EXCEL 2010 and saved it in 'XML spread sheet 2003 (*.XML)'.
I imported the XML file in a table using the code below:

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK 'C:\DEV\TestXML.xml', SINGLE_BLOB) AS x;

SELECT * FROM XMLwithOpenXML

 1. The XML data has been stored now in the table, so I use the code below to read it in a SELECT statement to access the individual columns:

    DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

 SELECT @XML = XMLData FROM XMLwithOpenXML

    EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

    SELECT SNo,Salutation,PatientNRIC,FirstName,LastName,Gender,Race
    FROM OPENXML(@hDoc, 'ROOT/SNo/Salutation/PatientNRIC/FirstName/LastName/Gender/Race')
    WITH
    (
        SNo [nvarchar](50) '@SNo',
        Salutation [nvarchar](100) '@Salutation',
        PatientNRIC [nvarchar](255) '@PatientNRIC',
        FirstName [nvarchar](255) '@FirstName',
        LastName [nvarchar](255) '@LastName',
        Gender [nvarchar](255) '@Gender',
        Race [nvarchar](255) '@Race'
    )

Then

EXEC sp_xml_removedocument @hDoc
    GO

However what I get is only the name of columns, the actual data has not been returned:

I am totally new to OPENXML comment and working with XML files, tried reading different posts but still couldn't figure out why no data is shown in my select query. Any help is appreciated.

As your XML is held in a table you can use the methods of the XML data-type ( eg .nodes, .value, .query etc ).  The thing you might struggle with a bit is namespaces in XML.  Start here to learn more about them: Understanding XML Namespaces.

Here are a few example queries I did against a spreadsheet similar to yours.  Try them and work through the Stairway as suggested by @MikaelEriksson and see if they start to make sense.

IF OBJECT_ID('tempdb.#tmp') IS NOT NULL
    DROP TABLE #tmp
GO

-- 1) Pull the data "as is" unpivoted, tag it with row/cell numbers
-- and optionally pivot it
;WITH XMLNAMESPACES (
    DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet',
    'urn:schemas-microsoft-com:office:spreadsheet' AS ss
    ), cte AS (
SELECT
    t.rawXMLId,
    ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) rowNumber,
    r.c.query('.') rowXML
FROM XMLwithOpenXML t
    CROSS APPLY XMLData.nodes('Workbook/Worksheet[@ss:Name = "Sheet1"]') w(c)
        CROSS APPLY w.c.nodes('Table/Row') r(c)
)
SELECT
    c.rawXMLId,
    c.rowNumber,
    ROW_NUMBER() OVER ( PARTITION BY rowNUmber ORDER BY rowNumber ) cellNumber,
    d.c.value('.', 'VARCHAR(50)') AS cellData
INTO #tmp
FROM cte c
    CROSS APPLY c.rowXML.nodes('Row/Cell/Data') d(c)
GO

SELECT 'unpivoted' s, *
FROM #tmp

SELECT
    'pivoted' s,
    rawXMLId, rowNumber,
    [1] AS Sno,
    [2] AS Salutation,
    [3] AS PatientNRIC,
    [4] AS FirstName,
    [5] AS LastName,
    [6] AS Gender,
    [7] AS Race
FROM #tmp
PIVOT ( MAX(cellData) For cellNumber In ( [1], [2], [3], [4], [5], [6], [7] )  ) upvt
GO

-- 2) Use your knowledge about the data to specify the columns manually
;WITH XMLNAMESPACES (
    DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet',
    'urn:schemas-microsoft-com:office:spreadsheet' AS ss
    )
SELECT
    t.rawXMLId,
    d.c.value('(Cell/Data/text())[1]', 'VARCHAR(50)') AS Sno,
    d.c.value('(Cell/Data/text())[2]', 'VARCHAR(50)') AS Salutation,
    d.c.value('(Cell/Data/text())[3]', 'VARCHAR(50)') AS PatientNRIC,
    d.c.value('(Cell/Data/text())[4]', 'VARCHAR(50)') AS FirstName,
    d.c.value('(Cell/Data/text())[5]', 'VARCHAR(50)') AS LastName,
    d.c.value('(Cell/Data/text())[6]', 'VARCHAR(50)') AS Gender,
    d.c.value('(Cell/Data/text())[7]', 'VARCHAR(50)') AS Race

FROM XMLwithOpenXML t
    CROSS APPLY XMLData.nodes('Workbook/Worksheet[@ss:Name = "Sheet1"]') w(c)
        --CROSS APPLY w.c.nodes('Table/Row') d(c)                   <-- include row header
        CROSS APPLY w.c.nodes('Table/Row[position() > 1]') d(c)     -- exclude row header

GO

My results:

Just a note about OPENXML, it can be good for larger pieces of XML but I tend to try and avoid it if possible due it's well known memory issues.  Your particular example is not working because of the namespaces and you have specified the wrong path (Root etc).  Post back if you really want to work with the OPENXML, but I would advise against it in this example as your data is held in a table - OPENXML can only work with once piece of XML at a time.


SSAS relationship/granularity help Hi all I have 2 fact tables with a measure group each, Production and Production Orders.

Production has production information at a lower granularity (at the component level) productionordershas information at a higher level (order level with header quantities etc.).

I have created a surrogate key link between the two tables on productionorderid. As soon as I add Prod ID (from productiondetailsdim) to the pivot table it blats out the actual qty (from prod order measure group) and I cannot combine the qty's from the two measure groups... How can I design the correct relationship between the two? please see my dim usage diagram.

Production Details is the dim that links the two fact tables... at the moment DimProductionDetails is in a fact relationship with Production.

I'm not sure what the relationship should be with Production Order (it is currently many to many) Thanks for your help!  If I combine the line level qty's with the header level i don't want the header quantities summed for every line (duplicates) i just want it summed at the order(header level) alongside the line qty's...

Please see example data between the two tables

I have to be able to duplicate this behaviour below...

I resolved this issue by creating a named query in the DSV which contains the product key and productorder keys, linked these back to the two fact tables, created a new dim for production orders (so i had one for each fact table),  and created a new measure group off of the named query which acts as a bridge.  In dim usage I then setup regular relationships to the 2 dims for each fact table.  This seems to have fixed my issue!


Configure unconstrained delegation for BULK INSERT I have a pair of Microsoft SQL Server 2016 nodes in an Always On Availability Group. I am trying to perform a BULK INSERT (using a SQL Server 2016 Management Studio query) on a file located on a Windows Server 2016 File Server Failover Cluster, but I get the following error:

  Msg 4861, Level 16, State 1
  Cannot bulk load because the file "\nas2.my.domain\Microsoft SQL Server 2016 Enterprise\test.txt" could not be opened. Operating system error code 5(Access is denied.).

This will occur regardless if I use the active node name (nas2.my.domain) or the failover cluster listener (nas.my.domain).

After looking around I found out this was due to the SQL Server being unable to impersonate the user account I am connected with due to nuances with BULK INSERT.

If you connect to the SQL Server using Windows Authentication, the SQL Server service account attempts to impersonate your user account when connecting to the file server. If you connect using SQL Server authentication, it will connect to the file server as the SQL Server service account.

If delegation and impersonation are not configured properly (the default state), the SQL Server service will not be able to impersonate your user account and will fall back to trying to connect to the file server as an anonymous user.

This can be confirmed by looking through the security event log on the file server. These facts along with a guide on configuring unconstrained and constrained delegation is documented in these links:

How To: SQL Server Bulk Insert with Constrained Delegation (Access is Denied)
Bulk Insert and Kerberos

I've tried following the instructions in thesqldude's guide but it still isnt working.

The database I am trying to BULK INSERT to is not part of the availability group so only the MSSQL1 node should be relevant. The File Server was active on the NAS2 node. Checking the event log on the file server does show that it is still suffering from this problem and the SQL Server is trying to authenticate to the file server as an anonymous user rather than impersonating my user account.

Does anyone know what is going wrong? Or if something changed in SQL Server 2016 to make these guides obsolete?

File Server Security Event Log Entry
Service Account Delegation
Service Account SPNs
SQL Server #1 Computer Account Delegation
File Server #2 Computer Account SPNs
Group Policy Objects
sys.dm_exec_connections - Kerberos

I can confirm that this GPO has been applied to MSSQL1 via gpresult.exe /R, and both the SQL and File Server nodes were rebooted afterwards to ensure any caches have been flushed.

Your configuration looks solid. SPNs and Delegation are configured as I would have done it myself.

The only thing which you haven't explained are the file/share permissions. This is where my solution steps in.

Solution 1  (Personal Account)

Verify that your personal account is allowed to access the file on the file server's share

your personal account needs at least READ permissions at the share permission level or in most cases you might see EVERYONE has READ permission.
your personal account needs at least READ permissions at the file's permission level
Check permission inheritance at file level for that one file

Reboot the SQL Server after changing the file permissions or cycle the SQL Server service.

Alternatively: Log in to the SQL Server with your personal account and connect to the share and open the file.

Open SSMS with your personal account.
Open up a connection to your SQL Server instance with Windows Authentication.

Verify you are connected to your instance with the SQL Server service account with the script you have used before.

Perform the import with BULK INSERT

Solution 2 (SQL Server Service Account)

Verify that the SQL Server service account is allowed to access the file on the file server's share

the SQL Server service account needs at least READ permissions at the share  permission level or in most cases you might see that EVERYONE has READ permissions.
the SQL Server service account needs at least READ permissions at the file's  permission level
Check permission inheritance at file level for that one file

Reboot the SQL Server after changing the file permissions or cycle the SQL Server service.

Alternatively: Log in to the SQL Server with the SQL Server Service Account and connect to the share and open the file.

Open SSMS with the SQL Server service account.

Runas: your_domain\SQL_Server_service_account
Provide password

Open up a connection to your SQL Server with Windows Authentication.

Verify you are connected to your instance with the SQL Server service account with the script you have used before.

Perform the import with BULK INSERT

BULK INSERT permissions

  Security Account Delegation (Impersonation)

  If a user uses a SQL Server login, the security profile of the SQL Server process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service). To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data. In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.

Reference: BULK INSERT (Transact-SQL)


SQL Server backups strategy with limited storage available I've a SQL 2012 Server setup on a VM, with 3 disks:

Disk 1: 150GB --> System
Disk 2: 750GB --> SQL mdf
Disk 3: 750GB --> SQL ldf

DBs are in full recovery mode.
Let's say we'll have 350GB of data in the DBs.
This is a SharePoint 2013 databases server, but I don't think this impacts the backup strategy to adopt here.
The VM itself (and all its disks) is replicated via Hyper-V replicas.

Is the following backup strategy acceptable?

I plan full data backups every week to Disk 3. As far as I understand, this flushes out all transaction logs. I then remove backups from previous week.
I plan logs backups every 1 hour to disk 2. I remove log backups older than 1 week.

With this strategy, I should optimize my disk space, and at worse, I lose 1 hour data? Am I correct?
Will the logs backups (every 1 hour) have a noticeable impact on my server performances when they occur?

Thanks,

Ideally you'd add a 4th volume for backups, but I understand that's likely not an option. (FWIW, Sql Server does support making backups to network shares).

With that out of the picture, it sounds to me like you're trying to keep it so that losing volume 2 on any given day means you can recover the full backup on volume 3 and still have the ldf file valid for that full day to get you back to current, or that losing volume 3 would mean you can still replay the transaction log backups on volume 2 from that day to get back up to the last hour. However, I don't think this will work how you want. I think you'll end up with much larger ldf and transaction log files than you need, and Sql Server will want to keep a lot more of that ldf information in memory, hurting performance.

I think you will be better served by taking nightly full backups, and doing transaction log backups as often as performance and disk space allow (which could be fairly often... but you'll need to test to know). This should result in better overall performance and reliability for your system. I do think it is okay to separate which volumes you're using for each role, as you suggested.

The one other thing I would add is to look at where you're keeping your tempdb. Moving that around can have a big impact on things as well. You might find (just as one example) that keeping your database's mdf and ldf together on one volume with tempdb and backups on the other gives better results.


Changing the SQL Server service account - any additional considerations? I have inherited a few instances that are running under LocalSystem. I need to change this to a domain account as we are changing our backup strategy to go to a network share and then to tape and I need to be able to assign permissions.

I know that the recommended way to change the service account is to use SQL Server Configuration Manager. However, is there anything else I need to be cognizant of? How can I be sure that directory permissions will suffice for the new service account? Will Configuration Manager assign proper permissions to folders that contain the data/log/binaries, regardless of where they are?

Your best bet before you change the service account would be to manually make sure all the permissions are correct.  This will allow you to also document what changes you make so in the future if you need to move the instance to a new server or set up another instance you will all ready have every thing you need.

This is also my preferred method of doing things since I have a slight mistrust of the built in GUI tools for SQL Server and prefer the command line or Windows folder management GUI for security modifications. (too many "Surprises" in the way SSMS does things"

Once you are ready to change the service account, the Sql Server Configuration Manager is the only way to go.  There are some other changes that the manager makes to the system besides just changing the account every thing runs under.


How postgres deals with XIDs and wraparounds? I was reading the docs about the xid and it's wraparounds. But can't understand how it all works.

  Normal XIDs are compared using modulo-2^32 arithmetic. This means that for every normal XID, there are two billion XIDs that are “older” and two billion that are “newer”.

How are they compared
According to documentation xid is 32-bit number and as far as I understand it is incremented with every transaction

Imagine the transaction IDs from 0 to 4294967296 in a circle. AS some point in that circle is your current transaction ID.

Let's say your current transaction ID is 1000000. Then the 2^31 transaction IDs after it are in the future: 1000001 to 2148483648. And the 2^31 transactions before that are the past: 2148483649 to 4294967296 and 0 to 999999 (remember we have a circle, so right after 4294967296 comes 0).

Once your current transaction ID crosses 4294967296, it becomes 0 again (to be precise, it will become 3, because the transaction IDs 0 to 2 have a special meaning).


SQL set allowed values for a column I want to make an ALTER TABLE expression which adds a new column and sets a default value and additionaly defines the allowed values for that column. It's a text column, and allowed should be only 'value1', 'value2' and 'value3'. Default should be 'value1'

According to following syntax diagrams:

I'm getting to this point

ALTER TABLE exampleTable ADD COLUMN new_column VarChar(20) DEFAULT 'value1'

but I'm absolutely not sure how to set the allowed values.

Is it possible to make somethin like

CONSTRAINT CHECK new_column IN ('value1', 'value2', 'value3)

? I must admit the search condition diagram is quite confusing me.

alter table ExampleTable
    add (new_column varchar(20) default 'value1',
         constraint ckExampleTable check (new_column in ('value1', 'value2', 'value3')));


SELECT
  y.Trade_date,x.Scrip,
  (((y.Open_Interest - x.Open_Interest)/x.Open_Interest)*100)
     AS Change_in_OI from stock_future x
JOIN stock_future y
  ON y.Scrip = x.Scrip
  AND y.Trade_date = '2020-03-17'
WHERE x.Trade_date = '2020-03-16';


Is to possible to forbid access to tables in own schema - Oracle? Lets assume that we have the following situation:
We have an Oracle schema/user: USER_TEST (with the "create session" privilege).
SYS as SYSDBA creates couple Tables on schema USER_TEST. Is it possible to forbid access for the user USER_TEST to tables on his schema?

While there is no privilege you can revoke, and I'd wonder why you can't just create another user, the answer to your question is....

Yes

If you have the Enterprise Edition of Oracle you can use the Virtual Private Database feature to prevent tables from being selected.  Here is an overview from the Oracle Database Security Guide 11g Release 2:

  Oracle Virtual Private Database (VPD) enables you to create security
  policies to control database access at the row and column level.
  Essentially, Oracle Virtual Private Database adds a dynamic WHERE
  clause to a SQL statement that is issued against the table, view, or
  synonym to which an Oracle Virtual Private Database security policy
  was applied.

  Oracle Virtual Private Database enforces security, to a fine level of
  granularity, directly on database tables, views, or synonyms. Because
  you attach security policies directly to these database objects, and
  the policies are automatically applied whenever a user accesses data,
  there is no way to bypass security.

  When a user directly or indirectly accesses a table, view, or synonym
  that is protected with an Oracle Virtual Private Database policy,
  Oracle Database dynamically modifies the SQL statement of the user.
  This modification creates a WHERE condition (called a predicate)
  returned by a function implementing the security policy. Oracle
  Database modifies the statement dynamically, transparently to the
  user, using any condition that can be expressed in or returned by a
  function. You can apply Oracle Virtual Private Database policies to
  SELECT, INSERT, UPDATE, INDEX, and DELETE statements.

To prevent SELECT on a table you would need to do the following:

Create a function that accepts a schema/object name and returns a WHERE clause style Varchar2.  In this case it should return something like '1=2' when the user is the user you don't want to access its own objects.
Call the Add_Policy function of the DBMS_RLS package for each table the user owns.  This will create a policy attaching the function to the table it will protect.


List partitioning or filtering a non-partitioned table, which incurs lower cost? Consider a list partitioned table PARTITIONED__SOURCE_TBL on column period, and non-partitioned table SOURCE_TBL also having the same columns. Assume all tables are adequately indexed.

Set 1:

INSERT INTO PARTITIONED__SOURCE_TBL VALUES ...;
SELECT * FROM PARTITIONED__SOURCE_TBL WHERE period = <some value>;

  Total cost = [Cost to insert rows into partitioned table] + [Cost to pull data from a specific partition]

'Cost to insert rows into partitioned table': Would this be higher for a non-partitioned table?

'Cost to pull data from a specific partition': Should be minimal as you are basically pulling data from a logical table rather than using an index to filter out the records

Set 2:

INSERT INTO SOURCE_TBL VALUES ...;
SELECT * FROM SOURCE_TBL WHERE period = <some value>;

  Total cost = [Cost to insert into non-partitioned table] + [Cost to perform a full table scan, filter data row-wise and retrieve a required data ]

'Cost to perform a full table scan, filter data row-wise and retrieve a required data': I feel this would be higher than pulling data from a partition as we are pulling data from a single partition at a time, so Oracle needs to look at a single partition (subset of the table).

Now as both above sets return the same required data.. which approach is considered better..?
As @Phil has mentioned below, a good indexed table should be better than having a partition for better performance. Note that we already have the license for partitioning so that is not a factor here.

The performance benefits of partition pruning depend on the proportion of rows being selected from the table and the efficiency with which the rows can be isolated to their own partition(s).

If you are selecting 5% of a table's rows and those rows are isolated into a subset of partitions then selecting them is generally going to be around twenty times faster than a full table scan. On the other hand, selecting them via an index could be more or less efficient than a full table scan, but is not going to approach twenty times as fast except maybe in very particular circumstances (perfect clustering of values, bitmap index access, SSD storage ...).

There's virtually no overhead to inserting into a partitioned table. One advantage of partitioning when it comes to inserts is that it can allow multiple simultaneous direct path inserts to the table, which is impossible on a non-partitioned table as the insert would require a segment-level exclusive lock.


How can I import a database dump (.sql) using pgAdmin without the PSQL Console plugin? I am trying to import a database dump (.sql) using pgAdmin. I want to do it using only the GUI, not the PSQL Console (where I can import the sql dump using \i /path/to/yourfile.sql on Unix or \i C:\\yourfile.sql on Windows). Is that possible? If so, how?

The new import tool seems to be able to only import data in a table:

The restore function doesn't seem to accept .sql:

Lastly, I cannot load the .sql I'm trying to import in the query builder, as it is too large (25 GB).

I have used this pl/pgsql function to create one .csv file per table:

CREATE OR REPLACE FUNCTION db_to_csv(path TEXT) RETURNS void AS $$
declare
  tables RECORD;
  statement TEXT;
begin
  FOR tables IN
    SELECT (table_schema || '.' || table_name) AS schema_table
    FROM information_schema.tables t INNER JOIN information_schema.schemata s
    ON s.schema_name = t.table_schema
    WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema', 'configuration')
    ORDER BY schema_table
  LOOP
    statement := 'COPY ' || tables.schema_table || ' TO ''' || path || '/' || tables.schema_table || '.csv' ||''' DELIMITER '';'' CSV HEADER';
    EXECUTE statement;
  END LOOP;
  return;
end;
$$ LANGUAGE plpgsql;

And I use it this way:

SELECT db_to_csv('/home/user/dir');
-- this will create one csv file per table, in /home/user/dir/


How do I stop MYSQL trying to load a missing InnoDB table? Everytime I start MySQL, I receive the error:

Cannot open datafile for read-only: '.\deleteddatabase\deletedtable.ibd' OS error: 103

I can't DROP the table, because it doesn't exist. There's no corresponding .frm file. I presume that somewhere in ibdata1 there's a reference to this old InnoDB file.

How do I stop the error?

I fixed this by creating the missing database and table (with just one field), then dropping both the database and table.


Automatic MySQL Replication Fail-over Strategy I want to know that if replication is down due to some reason as if master is down then what strategy should be followed so that the replication is up instantly without manually intervening the process , i.e if replication is down when no DBA is available then how the automatic fail-over will take place.

MySQL 5.6 introduces Global Transaction Identifiers to replication, and complements these with new failover and switchover utilities which can automate fault recovery

You can learn more about replication in MySQL 5.6 here:
http://dev.mysql.com/tech-resources/articles/mysql-5.6-replication.html

You can also watch a tutorial demonstrating how these utilities work to provide self-healing clusters:
http://dev.mysql.com/tech-resources/articles/mysql-replication-utilities.html


How can I speed up this query on table with 65 million records select hash_id, hash_value,
            substring(T.cri, 1, 7),
            substring(T.cri, 9, D2.Pos-9),
            substring(T.cri, D2.Pos + 1, D3.Pos - D2.Pos - 1),
            substring(T.cri, D3.Pos + 1, D4.Pos - D3.Pos - 1),
            substring(T.cri, D4.Pos+1, len(T.cri) - 6 - D4.Pos),
            substring(right(cri,5),1,1),
            substring(right(cri,3),1,1),
            substring(right(cri,1),1,1),
            ref_count,
            compartment_id,
            cg_id,
            cri_version_id
from hash_key as T WITH(index(IX_hashkey_hashid_covering_refcount),nolock)
    cross apply (select charindex('?', T.cri, 31)) as D2(Pos)
    cross apply (select charindex('?', T.cri, D2.Pos+1)) as D3(Pos)
    cross apply (select charindex('?', T.cri, D3.Pos+1)) as D4(Pos)
where hash_id between 1 and 10000

Create table:

create table hash_key_binary
(
  hash_id bigint not null identity,
  hash_value nvarchar(100) not null,
  cri nvarchar(100) not null,
  ref_count_checksum int not null default(0),
  compartment_id int ,
  cg_id int ,
  cri_version_id int,
  primary key (hash_value, cg_id),
  constraint fk_hash_compartment foreign key (compartment_id) references compartment(compartment_id) on delete no action,
  constraint fk_hash_compartmentgroup foreign key (cg_id) references compartment_group(group_id) on delete no action
)

These are the indexes present on this table :

CREATE NONCLUSTERED INDEX IX_hashkey_hashid_covering_refcount
  ON hash_key (hash_id ASC)
  INCLUDE (ref_count)

CREATE NONCLUSTERED INDEX IX_hashkey_cri
  ON hash_key (cri ASC)

I think your biggest issue is the schema.

you should try to see if you can reduce the row footprint. For example, is nvarchar really needed? Using Varchar will cut the size in half. This may cut your processing time in half too. Is BigInt really needed for HashID? you have only 65M rows, a regular INT is more than enough and again half the size. Each byte you save will gain you 65Mb of data.
you should see if you could use separate fields instead of using text manipulations, that would simplify the query, improves integrity and most probably speed.
primary key on a nvarchar(100) is not a great idea, that should be on your identity column instead. And then make it clustered. I'm sure that will remove the need for the index hint.
instead of making (hash_value, cg_id) a primary key, set it as an unique index
make sure you have a good maintenance plan to reduce index fragmentation


AlwaysOn Availability Groups go to Resolving status We have a two-node cluster set up running SQL Server 2012 on Windows Server 2012 R2.  The base cluster consists of only those two nodes.  The quorum is set up as node + disk majority, with a shared disk sitting on an EqualLogic array connecting via iSCSI.  Cluster configuration validates with no errors.

Earlier today, the two Availability Groups running on the cluster (both primary on Node #1) went into a resolving state on both nodes.  Looking at the cluster events, there is nothing until it tries to restart the service.  Everything underneath the Windows Failover cluster shows green (online and no warnings) - disks, network interfaces, nodes, etc.

Looking at the application log in Windows Event Viewer, the event showing that the Availability Groups are entering the Resolving state have been requested to do so by the cluster because a quorum could not be established.  I cannot find anything else in any logs to support this, and the quorum passes during cluster validation.

The AGs do not recover.  We found in the AlwaysOn log in SQL Server logs that the first node had a mirroring endpoint failure.  This occurred immediately following a memory access violation with symptoms similar to a problem Microsoft has resolved with Cumulative Update 6, so we're going to try that next.

I was able to restart the SQL Server service on node 1 (the one with the memory access violations that the second note could not communicate with), and I was able to bring the availability group back online at that point.  A reboot also brings everything back to 100%.

Do you have any insight as to why that wouldn't have failed node 1 and kept the database alive on node 2 when the mirroring endpoint failed?  We're currently at AG level 3; would bringing that up to 4 trigger the failure on node 1 while leaving the service alive on node 2?

I've seen this happen a lot when folks only have one set of network cables connecting their servers - meaning, a pair of 1Gb Ethernet cables in each node, at best, and they're using those for both regular networking as well as iSCSI storage connectivity. (The fact that you're using Equallogic is a clue - I've seen a lot of those with 1Gb implementations.)

If you have any networking problems at all:

The two nodes won't be able to see each other
Neither node will be able to see the storage
Presto, no one sees a majority, and you lose quorum

Things that can cause this include:

Backup software (doing huge reads from disk while simultaneously saturating network)
Running a CHECKDB (again, huge reads from disk plus huge writes to TempDB, which can prevent the cluster heartbeats from getting through if you only have one network interface for both regular networking and storage)

To work around it:

Use separate network interfaces for regular networking and iSCSI (like a pair of 1Gb (at least) ports just dedicated to iSCSI, and nothing else)
Use faster network interfaces (like 10Gb instead of 1Gb)
Do less disk/network-intensive work (stop doing backups and CHECKDB, ha ha ho ho, but also ease up on the index rebuilds)


db2rspgn generates TYPE = ese for workgroup server edition, is that normal? I tried to run db2rspgn on a workgroup server instance and to my surprise <instance>.TYPE = ese in the generated response file

]$ db2licm -l
Product name:                     "DB2 Workgroup Server Edition"

]$ db2rspgn -d /opt/nya/users/db2inst1/generated_responsfile/ -i   db2inst1  -t trc

]$ grep TYPE db2server.rsp
INSTALL_TYPE = CUSTOM
db2inst1.TYPE = ese

I also note that the response file contains:

COMP = ACS

which is also puzzling, since the reason I like to create a response file is because I would like to add support for ACS.

Does anyone have a clue why the file is generated the way it is, and am I missing something obvious when I'm generating the response file?

It appears as if ese is used regardless whether it is workgroup or enterprise. At least I can't find any examples where anything but ese is used.

Running:

db2licm -l

after install reveals an un licenced Enterprice Server, but after adding a workgroup license everything looks normal.


How to insert records using select statement? Hi I am working on one project, here what I am trying is, I want to insert records what I get using select statements into temp table. but it is giving error to me, can any one help me to solve this ?
ERROR:  relation "temp_mytbl" does not exist
SQL state: 42P01
following is my query
truncate table temp_mytbl;
create temporary table temp_mytbl as
select iex_id,dte,agent_name,schd_total
FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum  limit 0;

 insert into temp_mytbl
select iex_id,dte,agent_name,schd_total
FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum
group by iex_id,dte,agent_name,schd_total;

SELECT i.dte,
    idm.empl_id,
    1 AS entity_id,
    sum(i.schd_total) AS s_schdhours
   FROM temp_mytbl i
     JOIN source_odw_usac.t_incontact_agentstatelog a ON a.winid = i.iex_id::text
     JOIN reporting_prod.idm_dte idm ON idm.src_id = a.agent_no AND idm.src_type_id = 618 AND soar_date(a.calendar_date_key) >= idm.start_dte AND soar_date(a.calendar_date_key) <= COALESCE(idm.end_dte, now()::date)
  GROUP BY i.dte, idm.empl_id limit 100;

WITH temp_mytbl AS ( -- without aggregation in output expression / HAVING clause
                     -- GROUP BY must be replaced with DISTINCT
                     -- which is less expensive
                     SELECT DISTINCT iex_id, dte, agent_name, schd_total
                     FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum
                   )
SELECT i.dte, idm.empl_id, 1 AS entity_id, SUM(i.schd_total) AS s_schdhours
FROM temp_mytbl i
JOIN source_odw_usac.t_incontact_agentstatelog a ON a.winid = i.iex_id::text
JOIN reporting_prod.idm_dte idm ON idm.src_id = a.agent_no
                               AND idm.src_type_id = 618
                               AND soar_date(a.calendar_date_key) >= idm.start_dte
                               AND soar_date(a.calendar_date_key) <= COALESCE(idm.end_dte, now()::date)
GROUP BY i.dte, idm.empl_id /* , entity_id */
-- LIMIT without ORDER BY makes no sense
ORDER BY i.dte /* or another sorting expression */
LIMIT 100;

or the same in subquery form
SELECT i.dte, idm.empl_id, 1 AS entity_id, SUM(i.schd_total) AS s_schdhours
FROM ( SELECT DISTINCT iex_id, dte, agent_name, schd_total
       FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum
     ) i
JOIN source_odw_usac.t_incontact_agentstatelog a ON a.winid = i.iex_id::text
JOIN reporting_prod.idm_dte idm ON idm.src_id = a.agent_no
                               AND idm.src_type_id = 618
                               AND soar_date(a.calendar_date_key) >= idm.start_dte
                               AND soar_date(a.calendar_date_key) <= COALESCE(idm.end_dte, now()::date)
GROUP BY i.dte, idm.empl_id
ORDER BY i.dte
LIMIT 100;


Defragmenting SQL Server data and log files live with MoveFile API My employer is looking at deploying NTFS defragmentation software that uses the Windows MoveFile API to defragment open files. This would get deployed to thousands of SQL Server servers running SQL versions from 2005-2012 and Windows versions from 2003 and 2008 R2. If it matters, the product I'm talking about is PerfectDisk, but I believe there are similar programs that work the same way.

Testing so far hasn't turned up many problems aside from occasional I/O performance issues, which is no surprise and can be worked around by rescheduling & tuning the defragmentation. However, I'm more concerned about the risk of data corruption.

Does anyone here have experience with running this type of software in production on database servers? Did you experience any data corruption?

I'm rather uneasy about this despite being unable to find any solid evidence that it poses a problem.

Thanks for any replies.

Edited to add: This horrible idea was thankfully just forgotten about, possibly in part due to some of the warnings I gave.

Personally, I wouldn't want any antivirus or defrag software anywhere near a database file. Ask yourself, is this going to cause more problems than it's going to solve?

From A tale of CHECKDB failures cause by 3rd party file-system drivers:

  At the end of last week, Diskeeper 2009 was upgraded to Diskeeper 2010
  on the servers by our server team.  Diskeeper 2009 had been there for
  at least a year without problems so who would have expected it to be
  the issue.  Turns out the new version has an option enabled called
  IntelliWrite that causes this problem to occur.  Disabling this option
  immediately resolve the problem.  Paul blogged about this problem a
  long time ago, and recalled reading this when I clicked on it but it
  was the last thing that I would have thought about.

From Beware 3rd party file-system drivers with DBCC CHECKDB

  I don't want to cry 'wolf' here, but be careful of attributing massive
  corruption problems to the real IO subsystem if there's any kind of
  file-system filter driver installed.

With proper planning and management, you shouldn't need to defrag your database files.


Is it possible to schedule table update/refresh in Oracle Application Express? I have SQL Server data dumped into a text or csv file available via HTTP. Is is possible to schedule Apex to create a table on that data and keep it updated? How?

You can do the following steps in a procedure:
1) User UTL_HTTP to read the csv file;
2) Save the file on a oracle directory;
3) Create an external table that will map this CSV file;
4) Schedule this procedure accordingly, using dbms_job_scheduler

This assumes your user has permissions to execute UTL_HTTP, UTL_FILE and DBMS_JOB_SCHEDULER, besides read/write permission in the given oracle directory.


Memory Pressure in SQL Server - Enable LPIM or not? I have slow query execution in my SQL Server 2016.

Server configurations:

Total Physical Memory : 128 GB
SQL server Max Memory : 102 GB
Transaction replication is enabled.
Database Size:1.6 TB
Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)   Mar 18 2018 09:11:49   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: )

I ran the ring buffer query to check if Physical memory is low. The reason I checked for ring buffer for memory pressure is because my plan cache is also getting cleared frequently even though no one is running any script to clear cache.I get the below result where I see HIGH and LOW with same values for Avail Phys Mem.KB.

One thing I learned is for all RESOURCE_MEMPHYSICAL_LOW the indicator process is 2, which means memory issue is in the memory allocated to SQL Server not the rest of memory available for OS and other application.
FYI. This is a SQL Server dedicated server.

I also checked in Performance Monitor for Avaialable MBytes which shows the value 16,662.00 (16 GB) as below.

I think 16 GB is enough for OS+other applications, this being a SQL server dedicated server.

Checked the server diagnostics as well. It says that 25 GB is available.

So now I am confused whether:

Is there a memory pressure in this server?
The RESOURCE_MEMPHYSICAL_LOW flag is being set by the OS or external process or SQL Server?
Should I enable LPIM to fix this issue?

Additional details as per @Shanky's answer.

I had Query store enabled, but I disabled it a week ago.
rounds_count for SQL Server Plans with clock hand HAND_EXTERNAL is increasing.

More Details.

I am facing huge memory grant by some queries and this is causing RESOURCE_SEMAPHORE waits for other queries.

Does this huge memory grant(one query i checked ,memory grant is ~7GB)  result in a memory pressure from SQL server and can this be the cause of plan cache clearing and RESOURCE_MEMPHYSICAL_LOW flag.

is there a memory pressure in this server.

With the limited information, I cannot tell with surety. Need more parameters and output from perfmon to tell about memory pressure. But yes, from the query output you posted for the time 2018-10-23 11:22:30:457 there was indeed low memory for some process(s), not the SQL Server system as whole.

  the RESOURCE_MEMPHYSICAL_LOW flag is being set by the OS or external process or SQL server.?

Look at the output it says RESOURCE_MEMPHYSICAL_LOW has values of 2 from IndicatorProcess which means certain processe(s) which were running were indeed facing memory pressure, if IndicatorSystem would have had value of 2 it would have been system wide memory pressure. Also note the memory pressure is coming from low physical(RAM) not low virtual memory(VAS). So you can see just taking a value for one piece of time it is difficult to say there is continuous memory pressure. I suggest you read Using sys.dm_os_ring_buffers To Diagnose Memory Issues in SQL Server to understand what the values Indicatorsystem and IndicatorProcess means

  should i enable LPIM to fix this issue.?

LPIM is not going to fix the issue it is only going to mask the issue and would come as workaround. You need to find what is causing resource monitor to flag low memory notification, it could be possible that some process running is causing it. Now plan cache getting frequently cleared is something lot of people have reported from SQL Server 2016 onward and it may be due to query store, are you using one ?. Query store used to be culprit behind plan cache clearing but since you are on latest SP I doubt this factor.

For seeing memory pressure for cache we can also use DMV sys.dm_os_memory_cache_clock_hands

SELECT  *
FROM sys.dm_os_memory_cache_clock_hands
WHERE rounds_count > 0

If rounds_count is increasing this means there was pressure which forced clocks to sweep the cache more frequently and remove the entries.

EDIT:

  I am facing huge memory grant by some queries and this is causing RESOURCE_SEMAPHORE waits for other queries.Does this huge memory grant(one query i checked ,memory grant is ~7GB) result in a memory pressure from SQL server and can this be the cause of plan cache clearing and RESOURCE_MEMPHYSICAL_LOW flag.

Yes quite likely, I have seen you raised specific thread for this question and I would only add that before you go for anything make sure you have stats updated and indexes defragmented. An outdated stats may lead to bad plan which can end up asking more memory and hence semaphore waits.

Below are list of threads talking about similar issue

Stackexchange thread about plan cache getting cleared frequently.
MSDN thread about plan cache getting cleared.


SQL Uniqueness Across Multiple Tables I have two SQL tables: Users and clients.

Every row in the users table has a set of clients in the clients table, linked by the users table id field and the clients table user_id field.

The users table has a username field which is unique.

The clients table has a user_id field AND clientname field that together are unique (i.e. clientnames can exist more than once, but must have different user_ids)

My question is: How do I add a rule that forces uniqueness between the username field in the users table and the clientname field in the clients table? Is it even possible?

i.e. no username can be the same as a clientname , and no clientname can be the same as a username.

You could have a third table which stores both. Something like this:

unique_ids
----------
  identifier (UNIQUE)
  used_where (name/id of table that the identifier is used in)

users
-----
  username (FK to unique_ids.identifier)

clients
-------
  clientname (FK to unique_ids.identifier)

This way, both tables share one common table that contains the values that need to be unique and uniquness contraints can be easily enforced in that shared table. You will need a simple on-insert trigger (on both tables) as well that will check the used_where field to ensure that the identifier is not already used by the other table (because if I understand your requirement, username must be unique in users but clientname does not need to be unique in clients).

If you don't want to introduce new structures and modify existing ones, you could try using an on-insert trigger on clients and users that checks to make sure a new record does not contain an existing username/clientname.


Does the length of the index name have any performance impact? This may seem silly, but in trying to stick to my standard nomenclature when naming indexes in our DB, the latest one was a little lengthy, and like all things DB I wondered if it in any way could negatively affect performance.  I'm just not sure where or how index names are actually stored or used vs the actual column data.

For example, is it advantageous to name an index epoid vs ecm_products_options_product_id?

The later has been closer to our standard and makes for easier reading and identification in the application layer.

In short: No.

There will be a very very small difference in parsing time for statements that specifically mention the index or generating output that mention the index, but this is so vanishingly small compared to all the other work that teh database engine it doing that it is simply noise - far too small to even reliably measure.

When the index name is not explicitly used then even that difference is gone: the index will be looked up by the columns it contains data for not by its name. If the engine needs to refer to he index directly it will do so by some internal ID - the name you know is essentially just for us humans.

Baring that in mind, especially the second point, long descriptive names are recommended. There is a caveat there though: if you make the name too long then it becomes unwieldy, and many tools will impose arbitrary limits on the length of identifiers. SQL Server for instance limits all names to 128 characters (if you get close to this limit you have probably gone too far!) but I've seen some tools (that were bad in other ways too...) fall over when they hit anything longer than 50. The mySQL documentation should tell you what the internal name length limits are for your circumstance.


Cannot see new drive in SSMS maintenance wizard I'm trying to setup a Backup Maintenance Plan in SSMS for Sql Server 2012, running in a failover cluster on Windows Server 2012.  The current plan backs up to the same cluster volume where the database data and transaction log files reside, and I want the backups going to a different volume, but the only volume that I can select in the maintenance plan configuration is the shared drive I am already using.  The same limitation exists when I try to manually run a backup from within SSMS - I can only see the one drive.

How can I get additional drives to show up?

I have:

Mapped the new cluster volume to a drive letter
Ensured that the sql server service account has full control permissions on that drive
Successfully run a backup to the new drive using T-sql

In the long term, I fully intend to move to t-sql scripted backups for the additional control and flexibility, but I'm new to this and want to get the backups on a separate drive right the heck now.

+1 for sensibly wanting to get the backups on a separate drive. However, if the new cluster volume is on the same SAN, you're not really accomplishing much. It would likely be much better to run backups to a file share on a totally different system.

As a workaround, you can easily add an "Execute T-SQL Statement" task to your Maintenance Plan to manually specify the backup statement:


sp_BlitzFirst - option to save more than only SQL Server PerfMon stats? First, I can't say enough good things about the FirstResponders kit. It's my "Go To" anytime something seems funny on the SQL servers.
I'm also using sp_BlitzFirst to capture data to tables for my own reporting.
Q: For Perfmon stats from sp_BlitzFirst, is there an option or parameter to capture additional perfmon stats? I'm thinking maybe stuff like:

Processor "% Processor time", "Queue Length"
maybe some Physical Disk values
also curious about Network Interface as we have a couple large queries coming from other servers.

sp_BlitzFirst version 7.9999 (20201114), SQL Server 2016.
Much Thanks!

Q: For Perfmon stats from sp_BlitzFirst, is there an option or parameter to capture additional perfmon stats?
Not at the moment! They're all hard-coded into the proc. If there are additional ones you'd like, they'd have to be added to the list. You can make that suggestion over on GitHub.