Restoring a SQL Server 2012 database in high availability I have a database which is in always-on high availability mode synchronized with another database on a different instance. How can I restore from a .bak file into the primary database using T-SQL?

I'm new to high availability and I've been advised that I need to take the database out of high availability before I can do a restore and then put it back in high availability again but I'm not sure.

I'm hoping I can just restore straight into the primary while the AlwaysOn is still enabled and it will auto sync with the secondary.

Listen to your adviser.  By restoring a backup, you are essentially replacing the database schema and data.  You will need to turn synchronization off, remove the DB from HA and perform the restore on the primary and replica, leaving the replica version in a restoring state by using WITH NORECOVERY.  Once your backup is in place, put the DB back into HA and start synchronization again.

HA is very similar to mirroring and uses similar technology, just not nearly as finicky.  You will want to treat your HA DBs similarly as well.

Code would be similar to the following:

--on primary

ALTER AVAILABILITY GROUP MyAG REMOVE DATABASE AdventureWorks2012;

--on primary

RESTORE DATABASE AdventureWorks2012
   FROM AdventureWorksBackups
   WITH NORECOVERY,
      MOVE 'AdventureWorks2012_Data' TO
'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf',
      MOVE 'AdventureWorks2012_Log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.

RESTORE LOG AdventureWorks2012
   FROM AdventureWorksBackups
   WITH RECOVERY;

--on secondary

RESTORE DATABASE AdventureWorks2012
   FROM AdventureWorksBackups
   WITH NORECOVERY,
      MOVE 'AdventureWorks2012_Data' TO
'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf',
      MOVE 'AdventureWorks2012_Log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf';

RESTORE LOG AdventureWorks2012
   FROM AdventureWorksBackups
   WITH NORECOVERY;

--on primary

ALTER AVAILABILITY GROUP MyAG ADD DATABASE AdventureWorks2012;

--on secondary

ALTER DATABASE AdventureWorks2012 SET HADR AVAILABILITY GROUP = MyAG;


enq: TX - row lock contention creating session waits The database I'm working on has session waits due to DMLs on numerous tables in a procedure.

The DBA identifies this as enq: TX - row lock contention.

How can I resolve this issue?

You are holding locks due to an UPDATE statemnt. Those locks are released when you commit your transaction.

To solve this issue, make sure you commit your transactions as soon as possible.


SQL to select all siblings with specific child I have a simple parent-child hierarchical arrangement using two tables: Assemblies (parents) and Parts (children). An assembly consists of one or more parts.

| AID |     A_NAME |
|-----|------------|
|   1 | Assembly X |
|   2 | Assembly Y |
|   3 | Assembly Z |

| PID | P_NAME | AID |
|-----|--------|-----|
|   1 | Part A |   1 |
|   2 | Part B |   1 |
|   3 | Part C |   1 |
|   4 | Part D |   1 |
|   5 | Part A |   2 |
|   6 | Part B |   2 |
|   7 | Part C |   3 |
|   8 | Part D |   3 |

To show the complete list of Assemblies with their Parts, I can run the following SQL query:

SELECT a.*, p.*
FROM assemblies a
LEFT JOIN parts p USING (AID);

and it returns:

| AID |     A_NAME | PID | P_NAME |
|-----|------------|-----|--------|
|   1 | Assembly X |   1 | Part A |
|   1 | Assembly X |   2 | Part B |
|   1 | Assembly X |   3 | Part C |
|   1 | Assembly X |   4 | Part D |
|   2 | Assembly Y |   5 | Part A |
|   2 | Assembly Y |   6 | Part B |
|   3 | Assembly Z |   7 | Part C |
|   3 | Assembly Z |   8 | Part D |

Now, if I only want to return Assemblies that include a specific Part (say 'Part B'), then I could run this query:

SELECT a.*, p.*
FROM assemblies a
LEFT JOIN parts p USING (AID)
WHERE p.P_NAME='Part B';

which returns:

| AID |     A_NAME | PID | P_NAME |
|-----|------------|-----|--------|
|   1 | Assembly X |   2 | Part B |
|   2 | Assembly Y |   6 | Part B |

However, what I'd really like to see is all the sibling parts* as well:

| AID |     A_NAME | PID | P_NAME |
|-----|------------|-----|--------|
|   1 | Assembly X |   1 | Part A |*
|   1 | Assembly X |   2 | Part B |
|   1 | Assembly X |   3 | Part C |*
|   1 | Assembly X |   4 | Part D |*
|   2 | Assembly Y |   5 | Part A |*
|   2 | Assembly Y |   6 | Part B |

Any ideas on how I can achieve this? That is, for a given part, return the assemblies it belongs to, along with all the other parts in the assembly.

I'm using MySQL, and here's an sqlfiddle if it helps.

In the WHERE you can simply filter for the parts which have an assemblie id equal to the assemblie id of 'Part B'

SELECT a.*, p.*
FROM assemblies a
LEFT JOIN parts p USING (AID)
WHERE p.AID IN
  (
    SELECT AID FROM parts WHERE P_NAME='Part B'
  );


Better way to select products that are related to ALL of the categories? I am trying to select products that are related to ALL of the categories, that has been "handed" to me.

The products are in the Product table and the relation to the categories are in the ProductCategoryRelation table.

The below code works, but I am not sure it is the fastest way to do it.

The 1,2,3 numbers are the category IDs.

SELECT *
FROM Product
WHERE (
    SELECT Count(*)
    FROM ProductCategoryRelation
    WHERE ProductCategoryRelation.CategoryId IN (1,2,3)
        AND ProductCategoryRelation.ProductId = Product.ProductId
    ) = 3

This might work for you, should perform better.

WITH CTE_ProductCount AS
(
    SELECT PC.ProductID
        , COUNT(PC.CategoryID) AS ProductCount
    FROM ProductCategoryRelation PC
    WHERE PC.CategoryID IN (1,2,3)
    GROUP BY PC.ProductID
    HAVING COUNT(PC.CategoryID) = 3
)
SELECT *
FROM Product
    INNER JOIN CTE_ProductCount PC ON PC.ProductID = Product.ProductID;


Creating Indexes to optimize query I have this query and I want to increase its performance by adding appropriate Indexes.

DELETE
  FROM MYTAB1
  WHERE MYID1 IN
        ( SELECT MYID2 FROM MYTAB2 );

I am not familiar with the syntax for indices and the type of settings that they require. Please provide the same.

The main issue here is that MYTAB1 has millions of records and thus the query takes a lot of time.

What indices do I need?

Perhaps this will help.  If your data looks something like this:

create table mytab1 as (select level myid1 from dual connect by level <=2000000);
create table mytab2 as (select level+20000 myid2 from dual connect by level <=100);

No indexes:

  Cost: 881, Consistent Gets: 3140, CPU Used: 174

Index on MyTab1 Table:

create index myid1_1 on mytab1 (myid1);

  Cost: 104, Consistent Gets: 103, CPU Used: 14

Index on both MyTab1 and MyTab2:

create index myid2_1 on mytab2 (myid2);

  Cost: 53, Consistent Gets: 102, CPU Used: 12

Your mileage will vary depending on quite a few factors, but in general an index on myid1 would probably be beneficial.

If you haven't already you should read through the Oracle Concepts Guide (pdf) particularly the section on indexing.


Permissions not allowing Update or Select on a trigger executed function With two databases A and B on the same SQL Server 2014 instance, I've written a series of scripts trying to do the following (written in very high level pseudo code):

User Updates table A.dbo.Main
A.dbo.Main has a trigger on UPDATE, DELETE, INSERT that calls a stored procedure A.CallProcInB
A.CallProcInB then calls a stored Procedure in B called B.RunComponentsUpdate
B.RunComponentsUpdate then merges a view and a table called B.dbo.A_View and B.dbo.B_Table.

The problem is I have gotten these series of scripts to work perfectly on a personal computer running SQL Server 2014 Express, with the exact same schema setup for the databases A and B, but when I set everything up on my company's main server running SQL Server 2014 I get the following error message:

  System.Data.Odbc.OdbcException (0x80131937):
  ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
  The SELECT permission was denied on the object 'B_Table', database 'B', schema 'dbo'.
  ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
  The UPDATE permission was denied on the object 'B_Table', database 'B', schema 'dbo'.

I've tried giving the user activating the initial trigger all privileges on both A and B, as this was enough to fix the issue on my personal computer server. However, giving basically "god" privileges hasn't been enough to resolve this error on the server.

The permissions error is to be expected when you don't have things like Cross-Database Ownership Chaining and TRUSTWORTHY enabled, and aren't logging in as sa ;-).

This very similar to the problem described (and resolved) in my answer to another question here on DBA.SE:

Permissions in triggers when using cross database certificates

However, in that case there was a Trigger in Database B and that is not the situation here. Still, this should be fairly easy to solve and will use a setup similar to what is described in that other answer.

Here are the steps you need to do for your particular issue (all of which are shown in that other answer):

Create a Certificate in Database A.
Sign the A.dbo.CallProcInB Stored Procedure using that Certificate.
Backup the Certificate, either to a File or into a temporary table (as shown in that linked answer).

Create that same Certificate in Database B (which is done by getting it from the backup file or out of the temp table, as shown in that linked answer).
Create a User from that Certificate.
Grant EXECUTE on the B.dbo.RunComponentsUpdate Stored Procedure to the new Certificate-based User.

That should be all you need.

Please be aware that any changes to the A.dbo.CallProcInB Stored Procedure will cause it to lose the signature. In those cases, just execute ADD SIGNATURE again.


Copy a mongo db collection without locking whole mongod instance I am trying to run mongodb copy collection on same server.
Requirement is we delete database as per our archival policy but we need to keep one collection from this data as a backup in separate db on same server.
I run copyCollection but it takes lock on whole mongo instance and block all other operations.
Collection size is 10 GB so it takes considerable time while copying and system is unavailable for duration.
Please suggest a way to achieve this without locking whole instance.

I am trying to run mongodb copy collection on same server. Requirement
  is we delete database as per our archival policy but we need to keep
  one collection from this data as a backup in separate db on same
  server.

Yes, it's possible through mongoexport from your source db and mongoimport the collection to the target database on the same mongod server.

Let's start from beginning.

Suppose that i have two database StackExchange and StackOverflow in same mongod.

> show dbs
StackExchange     0.000GB
StackOverflow     0.000GB

Suppose that in StackExchange there is collection orders having 4 documents.

> show collections
orders
>

Let's check there is documents records in Orders collection exist in StackExchange or not through mongo shell.

 > db.orders.find().pretty()
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea526"),
        "cust_id" : "A123",
        "amount" : 250,
        "status" : "A"
}
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea525"),
        "cust_id" : "A123",
        "amount" : 500,
        "status" : "A"
}
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea528"),
        "cust_id" : "A123",
        "amount" : 300,
        "status" : "D"
}
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea527"),
        "cust_id" : "B212",
        "amount" : 200,
        "status" : "A"
}
>

Here, as we can see that there is 4 document records in orders collection.

Suppose that  i want to copy the orders collection of  StackExchange database to the target database StackOverflow with targetorders documents name.

>mongoexport -d StackExchange -c orders | mongoimport -d StackOverflow -c targetorders
2018-02-14T10:52:16.630+0300    connected to: localhost
2018-02-14T10:52:16.631+0300    exported 4 records
2018-02-14T10:52:16.693+0300    connected to: localhost
2018-02-14T10:52:17.047+0300    imported 4 documents

To verify the targetorders collection documents in StackOverflow database.

> use StackOverflow
switched to db StackOverflow
> show collections
targetorders
test
> db.targetorders.find().pretty()
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea528"),
        "cust_id" : "A123",
        "amount" : 300,
        "status" : "D"
}
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea525"),
        "cust_id" : "A123",
        "amount" : 500,
        "status" : "A"
}
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea526"),
        "cust_id" : "A123",
        "amount" : 250,
        "status" : "A"
}
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea527"),
        "cust_id" : "B212",
        "amount" : 200,
        "status" : "A"
}
>

So, here we can see all the 4 documents of orders collection from the StackExchange has successfully copied in StackOverflow.

  Very Import Note: Run mongoexport & mongoimport from the system command line, not the mongo shell.

Hope this will help out to you.

For further your ref mongoexport and mongoimport


Performance hit using CAST in T-SQL We have a SQL generator that emits SQL conditional statements generically for specified fields (which for the sake of discussion: we will label as myField).

If myField is of type NVARCHAR, we can do a comparison of said field against a string like so: myField = 'foo'.

However, this does not work for fields of type NTEXT.  Thus, we have to do the comparison with a cast:  CAST(myField as NVARCHAR(MAX)) = 'foo'.  This will in fact work if myField is of type NVARCHAR or NTEXT.

What is the performance hit of doing the aforementioned cast on a field that is already of type NVARCHAR?  My hope is that SQL Server is smart enough to dynamically recognize that myField is already of type NVARCHAR (effectively turning the CAST into a no-op).

If the cast of the column is to exactly the same datatype and length and the seek predicate is a literal it does indeed seem to disregard it or treat it as a no-op and does an index seek on equality.

Seek Keys[1]: Prefix: [tempdb].[dbo].[#test].name = Scalar Operator(N'rpc')

If the cast of the column is to the same datatype but greater length and the seek predicate is a string literal it causes an index scan. This is obviously to be avoided.

If the cast of the column is to the same datatype and the same or greater length and the seek predicate is a local variable it adds a compute scalar operator to the execution plan. This  calls GetRangeThroughConvert and outputs a range.

This range is used to do an index seek and seems pretty efficient

Seek Keys[1]:
Start: [tempdb].[dbo].[#test].name > Scalar Operator([Expr1006]),
End: [tempdb].[dbo].[#test].name < Scalar Operator([Expr1007])

Testing Code

SELECT *
 INTO #test
  FROM [master].[dbo].[spt_values]

CREATE NONCLUSTERED INDEX [ixname] ON #test
(
    [name] ASC
)

DECLARE @name NVARCHAR(MAX)

SET @name = 'rpc'

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(35))= @name --Cast the same and local variable

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(MAX))=@name --Cast to longer and local variable

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(35))='rpc' --Cast the same and literal

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(MAX))='rpc' --Cast to longer and literal


Backing up a SQL 2008 DB, then restoring, gives a SQL 2005 (compatibility level 90) DB We have four different databases on a SQL Server 2008 (not SP2 - don't ask me why) database server that have an odd problem.

When listed from sys.databases, these databases report that their compatibility is SQL Server 2008/R2 (compatibility level 100) as shown for these three of the DBs here:

MyDB_Name               100 SQL Server 2008/R2
MyDB_Name_MirrorTables  100 SQL Server 2008/R2
MyDB_Name_Reporting     100 SQL Server 2008/R2

As output from this query:

select
    name, compatibility_level , version_name =
    case compatibility_level
        when 90  then 'SQL Server 2005'
        when 100 then 'SQL Server 2008/R2'
        when 110 then 'SQL Server 2012'
        else 'unknown - ' + convert(varchar(10), compatibility_level)
    end
from sys.databases

Restores from backups of other user DBs on the server restore correctly as compatibility level 100. However, restores of these four DBs from backups restore as compatibility level 90 (SQL Server 2005). Only these four DBs appear to have this issue.

To test this, I took a manual backup of one of these DBs with only the options INIT, SKIP specified. I then restored this backup to a SQL Server 2012 server. When restored, the DB went through the upgrade process from Version 655 to 706. Nothing unusual happened.

However, when looking at the compatibility level on SQL Server 2012, using the same code as above, the information showed up as:

bwh_MyDB_Name_MirrorTables  90  SQL Server 2005

Additionally, when restored to the original SQL Server 2008 DB server under a different name, the database still returns SQL Server 2005 (compatibility level 90) for the DB version, even though it was backed up on the same server as compatibility level 100.

Finally, although the DB reports as SQL Server 2008, when returning a date column from a DB2 linked server, the query returns a datetime (with 00:00:00.000 for the time). Running the query in SQL Server 2012, the value is returned as a Date data type, which is what it should be returned as in a SQL Server 2008/R2 database.

In my last tests, I added a test table to the DB containing a date and a time field, and inserted a row of data. Then I backed up and restored the DB again.  The table worked correctly, though the compatibility level was 90. I also created a table with date, time, and datetime2 fields with no problems.

I'm at a loss where to look next. The date/datetime problem was the original trigger for looking at this, but it has become a much larger puzzle. Obviously, I could simply CAST the incoming data as a date value (which does work, oddly enough), but this doesn't explain the RESTORE returning a DB to compatibility level 90, or the DB2 query returning a datetime instead of a date from the linked server. Any suggestions would be gladly accepted.

DISCLAIMER
  This information is being supplied as is. It might assist you in finding the root issue.

While I don't have a solution why your database is converting back to a certain level during a restore, I might be able to shed some light on why the restore might think it has to reset the compatibility level.

DBCC DBINFO

You might want to have a look at the DBCC DBINFO command.

DBCC DBINFO('<Your_DB_Name>') WITH TABLERESULTS

Have a look at the following lines:

ParentObject      | Object                     | Field             | VALUE
------------------+----------------------------+-------------------+--------
DBINFO STRUCTURE: | DBINFO @0x000000003609D5E0 | dbi_version       |  782
DBINFO STRUCTURE: | DBINFO @0x000000003609D5E0 | dbi_createVersion |  705
DBINFO STRUCTURE: | DBINFO @0x000000003609D5E0 | dbi_cmptlevel     |  110

While your database might look like it is at a certain level, internally it might be still fostering a lower level, which might be the cause of the restore setting a lower compatibility level.

DBCC PAGE

Instead of using DBCC DBINFO you can use the equally undocumented DBCC PAGE command:

DBCC TRACEON(3604)
DBCC PAGE('<Your_DB_Name>',1,9,3)

Where the syntax is

DBCC PAGE('DATABASE_NAME' | DB_ID, <FILE_ID>, <PAGE>, <LEVEL_OF_DETAILS>)

Scroll down a bit and you will find:

...
dbi_version = 782                   dbi_createVersion = 705             dbi_SEVersion = 0
dbi_dvSplitPoint = 0:0:0 (0x00000000:00000000:0000)
dbi_dbbackupLSN = 96:512:1 (0x00000060:00000200:0001)
dbi_LastLogBackupTime = 2018-11-22 09:15:01.473
dbi_nextseqnum = 1900-01-01 00:00:00.000                                 dbi_status = 0x00010000
dbi_crdate = 2012-03-14 13:14:18.510dbi_dbname = AdventureWorks2012     dbi_dbid = 9
dbi_cmptlevel = 110                 dbi_masterfixups = 0                dbi_maxDbTimestamp = 10000

Verifying The Database Backup Version

Now for some reason your system might be creating a lower level backup. You can verify your backup with the following command:

RESTORE HEADERONLY FROM DISK='B:\BACKUP\DATABASE_BACKUP.bak'

Which will return the Database Version of your backup file:

DatabaseVersion | CompatibilityLevel | SoftwareVersionMajor | SoftwareVersionMinor | SoftwareVersionBuild
----------------+--------------------+----------------------+----------------------+----------------------
782             | 110                | 12                   | 0                    | 5590

Information Output has been shortened

Is the database backup the same level as the SQL Server version running?

Questions to Ask Yourself

Are we using a third-party backup tool?
Are we using a different server to create the backups?
Are we using some other tool which might interfere with the backups?

Conclusion

You might have just hit a bug with the version of SQL Server you are using and an database internal issue with the dbi_version and dbi_createVersion information, which causes the compatibility level to set lower than you expect.

-OR-

You might be having issues with the database backup files being created with a down-level compatibility level, because...

of some 3-rd party issue
of the dbi_version and/or dbi_createVersion being different in the backup, because of, ....

a bug
a 3-rd party issue


Migrating SQL Server 2008 databases to new 2-node WSFC SQL2014 Alwayson AG I am currently working on migrating our single FCI Prod SQL Server 2008 Enterprise instance (on Win2008) multiple databases (>2TB total) to a new 2-node WSFC cluster (Win2012 R2 & SQL2014 enterprise) AlwaysOn AG.

The current single FCI prod server will be decommissioned after migration.

I could use LogShipping to migrate from our current single FCI Prod server to the new WSFC cluster Node 1 (Primary).
Then, backup the Node 1 (Primary) db's and Restore on Node 2(Secondary) to prepare Node 2 (Secondary) and then Create/JOIN the AG group.

I would like to reduce the preparation time for the Node 2 (Secondary) i.e. downtime during migration i.e. to avoid separately backing up the Node 1 db's and Restoring on Node 2(Secondary).

If we Logship our current Prod db's to both Node 1 and Node 2 simultaneously, then is it possible to create an AG and JOIN db's on both these Nodes 1 and 2 successfully at the same time?

To perform a server migration to an AG using log shipping, you should log ship to both servers. When you are ready to actually cut over to the new AG, follow the following steps:

Take a final tail log backup on the old instance (BACKUP DATABASE...WITH NORECOVERY)
Restore the backup to both AG nodes (or allow your log shipping to apply it) WITH NO RECOVERY.
On Node 1, bring the the database online (RESTORE DATABASE MyDb WITH RECOVERY)
Add the DB to the AG on Node 1.
Join the secondary DB to the AG.

I'd recommend practicing this routine on your QA system before the real production upgrade. There are a lot of scripts/statements to be run, and lots of switching between servers to do it. It's best to have those steps carefully documented and scripts staged in advance.


Troubles with an attribute that depends on the value of another attribute I'm redesigning a health insurance costs database (actually they're not really insurance plans in my country
but I think that for the sake of the example it doensn't matter). We're the health plan consulting firm so we don't provide health plans, we manage health plans on our client's companies.
My problem lies specifically  on the relationship between the insured and his or her dependants.

I've chosen to model the relevant situation as follows:

The Insured attribute named type is important here. It identifies  if  the insured person in question is the 'primary plan member' or a 'dependant'. But, for dependants, and dependants only, we need a relationship_type attribute ('spouse' or 'child').

My considerations

If I simply add an relationship_type attribute to the insured entity type (and a column the corresponding logical table), there'll be multiple null marks when the insured person is a 'primary plan member'.
If I create a relationship_type entity type and the respective logical table, the foreign key column will retain null marks on 'primary plan member' rows in the Insured table.

Both options include a lot of nulls and I've read that that's not a good sign.

The question

relationship_type is an attribute that depends on the value of another attribute and I don't know how to implement that. Could you share some insights about how to model this situation?

My first thought off the top of my head is that you could put in the relationship_type attribute and have it be "self" rather than null for the primary. Another possibility is that you could make the "type" include "primary", "dependant spouse", or "dependant child", depending on how you need to access it.

I know that's not anything clever, design-wise. I'm interested to hear what others suggest, also.


Allowing non-sysadmin users to see SQL Server execution plans Is this possible ? On SQL Server 2012+, grant access to a non sysadmin user to see the execution plans ? And can this be done for selected databases only ?

Community Wiki answer generated from question comments

Yes this is possible. See:

Syntax for Granting, Denying, and Revoking the SHOWPLAN Permission1 2
Showplan Security2
SHOWPLAN Permission and Transact-SQL Batches2

1 Contributed by Mark Sinkinson
2 Contributed by Aaron Bertrand♦


Extended Events to find Blocking and Deadlocking Came across another great article from Jeremiah Peschka called Finding Blocked Processes and Deadlocks using SQL Server Extended Events and set this up on my local MS SQL 2014 Developer edition and created blocking and deadlocking events and was able to run his provided query which generate XML for both events.

I took the same process up to a production server MS SQL 2012 Enterprise edition and although I can query some captured deadlock events, the report column is blank, contains no XML for any captured event.  Thoughts?

Also, I am currently running the production server with deadlock trace flags
-T1204 & -T1222 and getting deadlocks logged to the SQL log but although this extended event query generated many rows of REPORT TYPE=Deadlock, no corresponding deadlock was written to the SQL Log, at least around the same time, I have many many more at other times in the SQL log, hence my work in this environment.  What am I missing?

If I open the .xel in SSMS I get a column with the XML string and can double click to have it parse/format and display it in a new window. I'm just not sure why the hyperlink doesn't display in the original query.

Glad you liked the post - it was by Jeremiah Peschka though, not me. (I know because I use it all the time myself!)

Make sure to read the entire article, though, which includes the answer you seek:

  Extended Events deadlock graphs use a slightly different XML schema
  than what SSMS expects. You should see an error along the lines of
  “There is an error in XML document”. For folks using SQL Server 2012
  and earlier, you can either parse the XML by hand or use SQL Sentry
  Plan Explorer.


Get names of games that have the highest number of guilds I've a database which contains tables:

Quest:

PK  Name
PK  qName
    goal

Guild:

PK  Name
PK  gName
    level

Game:

PK  Name
    MaxPlayer
    levels
    uName

GuildQuest:

PK  Name
PK  gName
PK  qName

I need to retrieve the names of the games that contain the highest number of guilds.

Guilds are assigned by the game.

My attempted query:

SELECT Name, Count(*) guildName
FROM Game game, Guild guild
WHERE game.Name = Guild.gName

and somehow use the MAX function.

I'm kind of confused how to perform this, any help is much appreciated.

One obvious solution is to get all the counts, sort them in the descending order and retrieve just the topmost row:

SELECT
  gName,
  COUNT(Name) AS GuildCount
FROM
  Guild
GROUP BY
  gName
ORDER BY
  GuildCount DESC
LIMIT
  0, 1
;

That would work only in cases where only one game can have the maximum number, but you probably cannot guarantee that. So the above solution is not very good, because in cases where two or more games have the same highest number you say you want to get all those names.

So, you could try this approach instead:

SELECT
  gName,
  COUNT(Name) AS GuildCount
FROM
  Guild
GROUP BY
  gName
HAVING
  COUNT(Name) =
  (
    SELECT
      COUNT(Name)
    FROM
      Guild
    GROUP BY
      gName
    ORDER BY
      GuildCount DESC
    LIMIT
      0, 1
  )
;

The subquery will get just the number, the highest guild count, and the outer query will get the names of those games that have the matching number of guilds.

The problem with the above is obviously that you have to scan the table twice, each time performing the same grouping, and the grouping itself, moreover, has to be repeated in the code as well. Question is, how to avoid that?

Now, where other SQL platforms offer powerful instruments like ranking functions, MySQL is trying to make up by allowing you to use variables to achieve better performance. In this case, too, it is possible to use variables to get the results in one go and avoid repetition of logic. Here is one way:

SELECT
  gName,
  GuildCount
FROM
  (
    SELECT
      IF(@LastCount <> 0 AND @LastCount <> grp.GuildCount, @flag := 0, @flag) AS flag,
      grp.gName,
      @LastCount := grp.GuildCount AS GuildCount
    FROM
      (
        SELECT
          @LastCount := 0,
          @flag := 1
      ) AS init,
      (
        SELECT
          gName,
          COUNT(Name) AS GuildCount
        FROM
          Guild
        GROUP BY
          gName
        ORDER BY
          GuildCount DESC
      ) AS grp
  ) AS derived
WHERE
  flag = 1
;

The init derived table is just for variable initialisation, and grp provides the initial set of aggregated values sorted in the descending order to use our variables on.

The main part of the logic is located at the second nesting level where the variables are actually used. The @flag variable is used to generated the flag column for further filtering, marking rows that need to be included in the output with 1 and the rest with 0. It is initially set to 1 because the first row obviously need to be included. It is reset to 0 as soon as the number less than the maximum is encountered. To help with that, the other variable, @LastCount, is used, which stores the previous row's count value.

The outermost query merely filters the nested set on the flag column.

This solution can be tested at Rextester.


Properly determine SQL Server version and update level I have some existing code which tries to read the current version of SQL Server that's installed for a particular instance name by using a WMI query.  It checks the first dotted part of the VERSION (to trigger an Upgrade install) and the SPLEVEL (to trigger a Patch install).  This has worked well up to now.  (I need to be able to get the version programmatically and without actually opening an SQL connection to the instance.)

However 2017 and 2019 have done away with service packs, rendering SPLEVEL useless, so I'm looking for an alternative to determine when only an older CU is present.

According to the table of version numbers, on my fresh 2019 install with CU4 applied, I should be seeing a VERSION of 15.0.4033.1 and a FILEVERSION of 2019.150.4033.1 (the actual page has a typo in the latter).

However while I am seeing the matching FILEVERSION, what I see for the VERSION is still the RTM value of 15.0.2000.5.  This is also confirmed if I look at the Advanced Properties in SQL Server Configuration Manager.  (And FWIW I also confirmed that SPLEVEL was still 0.)

Oddly, if I connect using Management Studio then it does show the version as 15.0.4033.1.

I don't mind switching to comparing the FILEVERSION if that's what's needed, but what has me concerned are the "GDR Builds".  Looking at the equivalent table from 2017, there doesn't appear to be any particular rhyme or reason and a more recent GDR build can have a lower version number than an older CU build.

How can I make sense of this?  What even is a GDR build and where does it come from anyway?  Is it safe to assume they'll never "upgrade" an install that already has some CUs applied?

as a partial solution for this interesting problem, I would consider powershell:

first you need to Install the SQL Server PowerShell module on every server you need.

Get-SqlAgent -ServerInstance "MY_SERVER"

On the picture above you can see the ServerVersion that you are looking for.


Getting an error ORA-00933 while using EXECUTE IMMEDIATE Hi i am using 1 dynamic query which has a very big length size as below.

v_DymnamicQuery :=
    'WITH TempResult as
    (
        SELECT
        f.rbase_fmla_name as FilingName, f.user_comp_eff_dt, f.user_rnwl_dt, f.user_n_bus_dt, f.pf_wkstn_oid_lng, f.pf_wkstn_oid_sh, f.fmla_flng_u_exp_dt as expDate,
        rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn) as FileTag,
        ''Formula'' as DisplayType, null as Origin, f.wkstn_oid_lng, f.wkstn_oid_sh, CAST(f.iteration_number AS varchar(10) )||''.''||CAST(f.minor_version AS varchar(10)) as version, status_indc, f.status_indc as WIPStatus,
        upper(RTrim(f.rbase_fmla_name) ||''-''|| CAST(f.user_n_bus_dt AS varchar(10)) ||''-''|| CAST(f.user_comp_eff_dt as varchar(10)) ||''-''|| CAST(f.user_rnwl_dt AS varchar(10)) ||''-''|| CAST(f.iteration_number AS varchar(10)) ||''-''|| rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)) as NameWithDates'
        || v_FmlaQuery ||
        ' from fmla_flng_adptn f '
        || v_FmlaCondition ||
        ' where '|| p_isFormulas ||'= 1 AND (rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)) = COALESCE('||v_FileTag||', (rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)))
        and f.rbase_fmla_name like ''%''|| COALESCE('||v_name||', f.rbase_fmla_name)||''%''
    union all
        SELECT
        f.rbase_rttbl_name as FilingName, f.user_comp_eff_dt, f.user_rnwl_dt, f.user_n_bus_dt, f.pf_wkstn_oid_lng, f.pf_wkstn_oid_sh, f.rt_flng_u_exp_dt as ExpDate,
        rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn) as FileTag,
        (case f.rt_flng_type_indc when ''C'' then ''Constant Table'' else ''Keyed Table'' end) as DisplayType, null as Origin,f.wkstn_oid_lng, f.wkstn_oid_sh, CAST(f.iteration_number AS varchar(10))||''.''||CAST(f.minor_version AS varchar(10)) as version, status_indc, f.status_indc as WIPStatus,
        upper(RTrim(f.rbase_rttbl_name) ||''-''|| CAST(f.user_n_bus_dt AS varchar(10)) ||''-''|| CAST(f.user_comp_eff_dt AS varchar(10)) ||''-''|| CAST(f.user_rnwl_dt as varchar(10) ) ||''-''|| CAST(f.iteration_number as varchar(10)) ||''-''|| rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)) as NameWithDates'
        || v_TableQuery ||
        ' from rt_flng_adptn f
        where '|| p_isTables ||'= 1 AND rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn) = COALESCE('||v_FileTag||', rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)) and f.rbase_rttbl_name like ''%''|| COALESCE('||v_name||', f.rbase_rttbl_name)||''%''
    ),
    UnionTable as
    (
        SELECT ROW_NUMBER() OVER(ORDER BY ' || p_SortColumn || ') as RowNumber,
        FilingName, user_comp_eff_dt, user_rnwl_dt, user_n_bus_dt, pf_wkstn_oid_lng, pf_wkstn_oid_sh, expDate,
        FileTag, DisplayType, Origin, wkstn_oid_lng, wkstn_oid_sh, version, status_indc, WIPStatus,NameWithDates '
        || v_UnionQuery ||
        ' from TempResult
    )
    SELECT *
    FROM UnionTable
    WHERE RowNum > '||v_FirstRec||'
    AND RowNum < '||v_LastRec||';
    --AND RowNum = '||v_LastRecForRow;

    dbms_output.put_line(v_DymnamicQuery);

    EXECUTE IMMEDIATE v_DymnamicQuery;

while running the EXECUTE IMMEDIATE it is throwing an error as:
ORA-00933: SQL command not properly ended
ORA-06512: at "RATABASE.GETFILINGSWITHPAGING", line 120
ORA-06512: at line 5

even when i am running the same query(string) which comes from
dbms_output.put_line(v_DymnamicQuery);
it is successfully running without any error.

can someone help me how to use this or handle this situation.

At the end of the string, you're concatenating a final closing semi-colon; however, you don't have a closing quote after it.

Instead of:

AND RowNum < '||v_LastRec||';

Try this:

AND RowNum < '||v_LastRec||';'


SQL Server Offline database I do not really understand when it is necessary to set database offline .

I have googled and I did find any information or examples where and why should I set database offline.

I will be perfect if you will give me some example and cases when I need to make my database offline.

When you want to remove access but not DROP the database

Example 1: migrate one database to a new installation

Prepare new server
Backup/restore to new server
Update application connection srings
Take DB off-line on old server
Test thoroughly
Drop DB on old server

Without taking the DB off line, you limit rollback options

Example 2: Cleaning up a messy SQL Server (e.g. developers created DBs)

Take offline, see who calls...


Storing Routes and Locations in Graph using SQL Server 2017 I'm very new to Graph database, so please ignore my mistakes. My scenario described in following picture:

I have different locations connected to each other with a route between them. Then I have many outlets linked to routes.

I created a RDBMS schema like this:

My goal is to give possible routes and outlets on them between two queried locations.

Recently I heard of Graph database support in SQL Server 2017 and read this article, but can't connect with my current problem.

Please help me to create Graph and node tables in SQL Server 2017.

Thanks!

Update

Now I create a data model:

and some Node and Edge tables as follows:

CREATE TABLE [dbo].[mRoute](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](500) NOT NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)ON [PRIMARY]
)
AS NODE ON [PRIMARY]
GO

CREATE TABLE [dbo].[mCity](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](500) NOT NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)ON [PRIMARY]
)
AS NODE ON [PRIMARY]
GO

CREATE TABLE [dbo].[mOutlet](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](500) NOT NULL,
    [Latitude] [decimal](11, 9) NULL,
    [Longitude] [decimal](11, 9) NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)ON [PRIMARY]
)
AS NODE ON [PRIMARY]
GO

CREATE TABLE [dbo].[LinkedTo]
AS EDGE ON [PRIMARY]
GO

CREATE TABLE [dbo].[On]
AS EDGE ON [PRIMARY]
GO

Now I am stuck on Between Edge Table, which contains route between two cities.

SQL Server's graph capabilities are -- how can I put this nicely -- limited. At the moment it is really just a little syntactic sugar in the new MATCH clause. There is no support for any serious graph-oriented algorithm.  To achieve your goals you will end up using recursive CTEs, which do not play nicely with MATCH. While a solution is possible it will not be elegant. Sorry to be the bearer of bad news.

To learn about graph design & algorithms I would suggest starting with a specific graph DBMS. Neo4j springs to mind. Otherwise you will be spending all your time in work-arounds and never see the big picture.


Is it possible to upsert into a self-referencing table with a single statement in PostgreSQL? If I have a table A, like so:

A {
 id SERIAL
 title TEXT
 ...
 parentId INT references A.id via a foreign key constraint
}

I am pulling data from a source table - A_SOURCE - where there isn't a parentId column. Instead there is a parentTitle column. So the source table looks something like this:

A_SOURCE {
  title TEXT
  parentTitle TEXT
}

I started writing an upsert statement to insert into table A via a selection from table A_SOURCE before I realized that I can't easily resolve the parentTitle column in the source to a parentId in the target.

Since I can't be sure that the parent will have been inserted at the time the child being processed, a join or a subquery could return no results.

My upsert statement is looking something like this:

with source as (
  select
   title
   parentTitle
  from A_SOURCE
)

insert into A
select
title
... I don't think I can resolve to parentId here?
from source
on concflict ...;

I know that I can run two separate statements:

insert with null as parentId
Then update the parentId fields in the second statement

But is it possible to do this in a single query?

Prepare the values to insert with a recursive CTE that pre-calculates the ids and defines an order. Then insert it in that order:

WITH RECURSIVE a_sort AS (
      /* get all entries without a parent (rank 1) */
      SELECT nextval('a_id_seq')::integer AS id,
             title,
             NULL::integer AS parentid,
             parenttitle,
             1 AS rank
      FROM a_source
      WHERE parenttitle IS NULL
   UNION ALL
      /* recursively get all the immediate children and increase rank */
      SELECT nextval('a_id_seq')::integer,
             src.title,
             a_sort.id,
             a_sort.title,
             a_sort.rank + 1
      FROM a_source AS src
         JOIN a_sort ON a_sort.title = src.parenttitle
)
INSERT INTO a
SELECT id,
       title,
       parentid
FROM a_sort
ORDER BY rank;

The beauty of the solution is that this uses the sequence behind a.id (a_id_seq in the example), so the sequence automatically has the correct value after we are done.

This solution assumes that the data in a_source are correct, i.e., do not contain cycles.


An easy way to select one db dump from multiple db MySQL dump In an urgent situation another person made a backup of all the databases from my webserver to one *.sql file (MySQL dump). Now I have a ~700 Mb file with about 20 different databases. As I only need one of them to restore at the moment, is there an easy way to select the only part I need (one database)? Of course I can try to simply select the needed text from the *.sql file to perform a query in PHPMyAdmin, but it is not so easy as the file is big.

Take the difference between two 'CREATE DATABASE' using sed operation

eg

sed -n '/^-- Current Database: `dbname`/,/^-- Current Database: `/p' alldatabases.sql > output.sql

https://stackoverflow.com/questions/2342356/import-single-database-from-all-databases-dump

https://stackoverflow.com/questions/3596444/how-do-i-restore-one-database-from-a-mysqldump-containing-multiple-databases

Restoring a single database from mysql enterprise full backup


Select the newest data (inner join + group by maybe) I think I would need some help.
To simplify my problem, lets assume that I have two tables. First called "topics" with this structure:

idTopic  author  deleted
1        123     false
2        132     false

and second callledd  "texts" which looks like this:

idTopic idText datePosted
1       11     1
1       12     2
2       15     1
2       18     3

and what I need is to select the data for every topic with the data of newest text of this topic. So my output should be like this:

idTopic author deleted idText datePosted
1       123    false   12     2
2       132    false   18     3

so how should my sql query looks? I was trying to do some inner join with second table grouped by idTopic, but it didn't work.

Thanks

select idTopic, author, deleted, idText, datePosted
from
(
    select t.idTopic, t.author, t.deleted, x.idText, x.datePosted,
           row_number() over (partition by t.idTopic order by x.datePosted desc) rn
    from topics t
    left join texts x on t.idTopic = x.idTopic -- or INNER JOIN
) z
where rn=1

Check out the ROW_NUMBER() function which is used to give a unique sequential number to each row in each "partition".  In this case partition by t.idTopic tells the function to independently number each set of records of the same t.idTopic.
Within each partition, the records are numbered 1,2,3... based on a specific order, which in this case is order by x.datePosted desc - this puts the newest posts first.
LEFT JOIN also known in full as "LEFT OUTER JOIN" is used to keep all records of the table on the left side even if there are no records from the right. If you don't need to see a topic if there are no texts for it, simply drop the "LEFT" keyword.
The query is moved into a derived table and aliased (unimportantly as z) so that we can filter on it for where rn = 1.  Recall that each partition can only have one row of value rn=1, which is by definition of our ORDER BY clause in ROW_NUMBER(), the row with the latest text.


What's the anatomy of a columnstore index? One of the new features in SQL Server 2012 codenamed Denali is the Columnstore index.

I know a good bit about regular old row-store indexes, like the b-tree structure, differences in storage between the leaf level and b-tree pages, affects of included fields, optimizing to use them, order of keys, etc.

I'm having difficulty getting any good info on the internals of a columnstore index.

How is it structured?
Is there a b-tree?  Some other structure in place?
How is data organized?
What sorts of specific operators are best suited to use it?
Any other anti-patterns to be avoided when using them?

A lot of what I can find out about them are basically the exact opposite of a "normal" index, i.e. no ordering of keys, no included fields, nonclustered ONLY.

Any insights are appreciated.

Columnstore Structure

Columnstore data is physically stored in one or more segments (regular LOB allocation units) per column, and may also be partitioned in the usual way.  Each segment contains roughly one million rows of highly-compressed values or value references (several compression techniques are available).  A value reference links to an entry in one of up to two hash dictionaries.

Dictionaries are pinned in memory during query execution, with data value IDs from the segment being looked in the dictionary whenever execution requires the actual data value (this lookup is deferred for as long as possible for performance reasons).

Segments also have a header record containing metadata such as the minimum and maximum values stored in the segment.  Information from the header can often be used to eliminate complete partitions from processing at execution time.  Header record information is stored in the usual LOB data root structure, so eliminating a segment means the Storage Engine can skip reading the LOB data pages from physical storage entirely.  Maximizing the potential for elimination can require careful design, including a dependency on the clustered index order at the time the Columnstore index is built.

Specific Plan Operators

SQL Server 2012 introduces a new execution mode called Batch Mode.  In this mode, packets of roughly 1000 rows are passed between operators, significantly improving processor utilization efficiency.  Within each packet, columnar data is represented as a vector.  Not all plan operators support batch mode operation, but examples of those that do include Columnstore Index Scan, Hash Inner Join, Batch Hash Table Build, Bitmap Filter, Hash Aggregate (not scalar aggregates), Filter, and Compute Scalar (for projection and expression evaluation).  Query execution plans have been enhanced to show estimated and actual execution mode.

Anti-Patterns

There are a large number of restrictions in the first release, including constraints on allowable data types.  Most common types are supported; unsupported data types include DECIMAL with a precision greater than 18 digits, (N)VARCHAR(MAX), UNIQUEIDENTIFIER, CLR types, and (VAR)BINARY.

Use of string types, OUTER JOIN, IN, EXISTS, NOT IN, OR, UNION ALL may result in significantly reduced performance (Row Mode execution), unless workarounds are employed that typically involve unusual syntax rewrites as shown in the linked articles in this section.

More Information

Remus Rusanu has blogged a great overview here.


Using EXCEPTION to ignore duplicates during bulk inserts In pq 9.3.5, I'm importing records from an external source where duplicates are VERY rare, but they do happen. Given a readings table with a unique compound key on (real_time_device_id, recorded_at), the following will fail once in a blue moon:

INSERT INTO readings (real_time_device_id, recorded_at, duration) VALUES
  ('150', TIMESTAMP WITH TIME ZONE '2014-11-01 23:06:33 -0700', 10.0),
  ... many more records ...
  ('150', TIMESTAMP WITH TIME ZONE '2014-11-01 23:06:43 -0700', 10.0);

(FWIW, the above fails 'properly' with a duplicate key violation.)

I know that handling exceptions is expensive, but as I said, duplicate entries are very rare.  So to keep the code simple, I followed an example given in Optimal way to ignore duplicate inserts?:

BEGIN
    INSERT INTO readings (real_time_device_id, recorded_at, duration) VALUES
      ('150', TIMESTAMP WITH TIME ZONE '2014-11-01 23:06:33 -0700', 10.0),
      ... many more records ...
      ('150', TIMESTAMP WITH TIME ZONE '2014-11-01 23:06:43 -0700', 10.0);
EXCEPTION WHEN unique_violation THEN
  -- silently ignore inserts
END;

The above gets two errors:

psql:sketches/t15.sql:11: ERROR:  syntax error at or near "INSERT"
LINE 2:         INSERT INTO readings (real_time_device_id, recorded_...
                ^
psql:sketches/t15.sql:14: ERROR:  syntax error at or near "EXCEPTION"
LINE 1: EXCEPTION WHEN unique_violation THEN
        ^

Can anyone set me straight on the correct syntax?  Or is my error deeper than mere syntax?  (For example, will all of the INSERTs be ignored if there is a single duplicate?)

updated question

As @dezso points out, there are a few problems with the code above. So (at the risk of having the question flagged as too general), what's a good way to do bulk inserts where very few (< .1%) are duplicates?

There are 3 possible kinds of duplicates:

Duplicates within the rows of the bulk insert. That's your immediate cause for the exception.
Duplicates between inserted rows and existing rows.
Duplicates between inserted rows and concurrently inserted / updated rows from other transactions.

1. and 2. can be fixed easily. But you really need to define exactly how to solve conflicts. Which row is to be picked?

INSERT INTO readings (real_time_device_id, recorded_at, duration)
SELECT DISTINCT ON (real_time_device_id, recorded_at)  -- solves 1.
       i.real_time_device_id, i.recorded_at, i.dur
FROM (
    VALUES
  ('150', TIMESTAMP WITH TIME ZONE '2014-11-01 23:06:33 -0700', 10.0),
  ('150', TIMESTAMP WITH TIME ZONE '2014-11-01 23:06:43 -0700', 10.0)
  ) i (real_time_device_id, recorded_at, dur)
LEFT   JOIN readings r1 USING (real_time_device_id, recorded_at)
WHERE  r1.real_time_device_id IS NULL                    -- solves 2.

I am picking an arbitrary row from each set of dupes with DISTINCT ON. You may want to define deterministically instead:

Select first row in each GROUP BY group?

3. is more tricky - but hopefully not applicable to your case ...

To insert ~250K rows, it would be much more efficient to COPY the lot to a temp table (or as much as you can easily store in RAM and process without spilling to disk) and proceed from there.

If that's a large part of the table, it might pay to drop indexes you don't need for the duration of the update and create them afterwards ...

Getting error while importing large data through csv
Call UPSERT function multiple times to different inputs
How to update selected rows with values from a CSV file in Postgres?


How to address ORA-03113? If I leave sqlplus open and unused for too long (probably 15 minutes or more), when I EXIT the following message will appear.

ORA-03113: end-of-file on communication channel

Does this indicate a real problem? If so, how might one go about correcting it?

There are a number of other posts about this error at database startup time. This is not at startup time. This is on the client. I can immediately start sqlplus again and have a working connection. Environment is Oracle 11.2.0.3.0 on Windows 64-bit, client is Windows 7 64-bit.

This is a generic error.

If you receive this in an active session while running some query, PL/SQL code or other statements, you should check the alert log of the database for further information. Usually it is caused by critical errors such as ORA-07445 that cause the server process that belongs to your session to terminate unexpectedly.

But if you receive this error in and idle session after a specific amount of time, as in your case, then do not worry, because it is absolutely normal when the database enforces an inactivity timeout, for example in the case of DRCP (Database Resident Connection Pooling).

Here is an example:

Database Resident Connection Pool (DRCP)


Best database to store and retrieve Word and Excel documents My current project is to create a barcode system (for lack of a better word) that is linked to a database (was not given more information or directions).  My task is to have a barcode scanner interpret a barcode, query a database that stores MS Word and Excel documents (.docx/.xlsx), then automatically retrieve and display the document.  I have not received much direction other than when a barcode is scanned, to have the corresponding document to be retreived and displayed automatically.  There is currently no database at our location and we store all of our information in Excel spreadsheets.

The process that I am attempting to automate is to remove the "human element" from opening the correct document(checklists and reports) as well as saving the document with the proper naming format and location (excel VBA script).

There is no reason why this should be impossible, but I am not sure which (R)DBMS to use.  We are not looking to spend extreme money (no oracle 11g or 12c), but we are essentially starting from scratch and our spreadsheets are spread over the entire networked drive. I know there are options like IBM DB2 or MySQL or MS SQL Server or MongoDB...but I am not sure which one would be the best "bang for my buck" and give the least amount of headaches.

Personally since you don't have a relation beyond barcode --> document I don't think a relational database is the best fit.

If your requirements are really as simple as:

Find a document
Serve document to user

Then any Key/Value store should work.  This wikipedia article talks about some of the theory for Document Databases as well as some pro/cons vs Relational Databases.

You will need to write some sort of interface/application to translate the barcode reader output into a query for the document in the database you choose.  As such you might not even need to use a database at all.  You could just have the application layer find the documents in the network drive...

Since I was pressed for a specific recommendation.  I would look at MongoDB. It should be cheap to run, will fit you needs, and appears to have broad support.  Here is a link to a blog article discussing storing files/binaries in MongoDB.

Fair warning: I don't have any direct experience with MongoDB, and only basic familiarity with document databases in general.  As such my recommendation of MongoDB is based on hearsay, and general impressions.


Restoring MSSQL back-ups to Oracle 12c I currently have an Oracle 12c DW that has several data sources of varying types (MSSQL, Oracle, MS Access, PostgreSQL, etc.) being moved into it as part of our existing ETL process.  We have FME (safe.com) currently configured to do the bulk of it. For the most case this is functioning well.

However I have one data source that poses an interesting issue.  It is in production with no reporting/slave/replication strategy and it's resources/security prevents us from directly connecting to it. The only manner in which this data is available is through backups.  It is a MSSQL 2008 server with a weekly full backup and incremental backups daily between those weekly full backups.  I need to get this information into the Oracle 12c DW.  As far as I'm aware (currently speaking with FME support) FME isn't capable of directly reading backups and I'm likely unable to use FME short of restoring the backups to a different server and using FME to move from that intermediate server.

So I wanted to ask a 2 part question:

1) Can I restore a MSSQL full backup to an Oracle 12c database?

2) Can I restore incremental MSSQL backups to an Oracle 12c database?

And if this can't be done directly, is anyone aware of some workarounds?  I'd like to do this in a manner that can be scheduled (no manual work required) if at all possible.

My research shows that you can take some manual steps in Oracle developer to do this (might work for a one time migration, I'm unsure if I can schedule this process):
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/hol08/sqldev_migration/mssqlserver/migrate_microsoft_sqlserver_otn.htm

As a last resort, I'm pretty sure I can get an intermediate server with MSSQL on it that I can restore the backups to and then connect to it with FME.  However that has some costs associated to it (MSSQL license and hardware) that I'm trying to avoid for my client.

No. There is no way to do this without first restoring to MSSQL.


How can I get an object's "script as..." script through code? In SSMS I can right click on an object in Object Explorer (e.g. a table, key, constraint, or index) and go Script As -> Create To -> New Query Editor Window. This outputs the creation script in t-sql for that object. I would like to know if there is a function or procedure available that can do that which I can call, as I'm writing a script and can't rely on UI elements.

Exactly which combination of tables to refer to depends on the object type, but it is all in the tables in the sys schema of each database. For instance sys.tables contains user table information, sys.columns lists columns in tables and input parameters to rpocedures, sys.all_sql_modiles includes the definitions of stored procedures and views, and so on.

Most are keyed on the object_id from which you can lookup the name in sys.objects.

For instance to list the definition of SomeProcedure you might run SELECT sm.[definition] FROM sys.objects so JOIN sys.all_sql_modules sm ON sm.object_id = so.object_id WHERE so.name = 'SomeProcedure'.

See https://msdn.microsoft.com/en-us/library/ms189783.aspx for reference information.

The system procedures sp_help and sp_helptext can be used to do the legwork in many cases where you need the information for manual inspection, but if you are trying to take programmed action based on the information it is usually better to refer to the data in sys.* directly than try to parse the output of these procedures.


Duplicating a Database with RMAN I would like to duplicate to the same server Oracle Database with RMAN. I've configured flash recovery area for Oracle Database. My database is using SPFILE as shown below.

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile

SPFILE

My questions are:

if my source database uses SPFILE then do I have create a PFILE from the SPFILE? If so, how can I do that?
What do they mean by this?

  You only need to create directories that are referenced in the PFILE or SPFILE.

Which directories need to be created?

Production Database : /u01/app/oracle/oradata/DB11G/. My SPFILE (spfileDB11g.ora):

DB11G.__java_pool_size=4194304
DB11G.__large_pool_size=4194304
DB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DB11G.__pga_aggregate_target=155189248
DB11G.__sga_target=264241152
DB11G.__shared_io_pool_size=0
DB11G.__shared_pool_size=171966464
DB11G.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/DB11G/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/DB11G/control01.ctl','/u01/app/oracle/oradata/DB11G/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain='localdomain'
*.db_flashback_retention_target=720
*.db_name='DB11G'
*.db_recovery_file_dest='/u01/app/oracle/fra'
*.db_recovery_file_dest_size=3221225472
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)'
*.log_archive_dest_1='location=/u01/app/oracle/archive_logs'
*.log_archive_format='db11g%r_%t_%s.arc'
*.memory_target=417333248
*.open_cursors=3^AC^@^@C"^@^@^D^@^@^@^@^@^@^@^@^@^A^DU^F^@^@00
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

if backup files (autobackup and backup set) are in a different path on the source host (not default FRA Path) then how can I use the duplicate command? BTW, only the flashback logs are in the default location (/u01/app/oracle/fra/flashback). Path: /u01/app/oracle/oradata/DB11G/autobackup and /u01/app/oracle/oradata/DB11G/backupset.

If my source database uses SPFILE then do I have create a PFILE from the SPFILE?

Yes, you need to create a temporary PFILE to use while duplicating the database. You will later switch the new instance to use the SPFILE.

Use

CREATE PFILE = 'path/to/pfile' FROM SPFILE;

  You only need to create directories that are referenced in the PFILE or SPFILE.
  Which directories need to be created?

You will start the database as NOMOUNT to begin the duplication. This means the controlfiles will not be read, and so you don't need to have the directories in which the datafiles reside present in your new host. However, the directories references in the parameter files must be present. From your transcript,

DB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.audit_file_dest='/u01/app/oracle/admin/DB11G/adump'
*.control_files='/u01/app/oracle/oradata/DB11G/control01.ctl','/u01/app/oracle/oradata/DB11G/control02.ctl'#Restore Controlfile
*.db_recovery_file_dest='/u01/app/oracle/fra'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_dest_1='location=/u01/app/oracle/archive_logs'

You may find this chapter of the documentation useful: http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmdupdb.htm#i1006474


postgres dyanmic and unlimited where conditions How would one properly write a FUNCTION in postgres to return a TABLE based on a query where the query WHERE clause can have both an unknown number of filter conditions, and the conditions themselves are dynamic?

I feel like this should be a solved problem, and maybe it is, but I am unable to find any examples in postgres.

The type of conditions in involved in building one filter condition would be dynamically setting AND, OR, NOT conditions, =, >, <, >=, <=, etc. for a given column's values.

Then multiply this by an unknown n number of possible filter conditions.

In my scenario at the moment, there are nearly 70 different numerical measures which could be used to compose the where clause. Again, each of these measures could have AND, OR, NOT conditions, =, >, <, >=, <=, etc. applied. Moreover, each one of these 70 different numerical measures could be used more than once if different equality operators and low/high limit ranges are applied. At a 30,000 ft view, think of the problem as passing all the conditions you could generate in a tool like this to a postgres query. Essentially you would be translating into a postgres query the results of a web app end-user who is building a cohort from visual tools with 70+ measures at his/her disposal.

What is the best way to go about this in postgres? Pass a json array of objects as a function parameter, convert them to a table, and base the dynamic where in some manner off of that table? Run a LOOP based off the conditions? Other? Appreciate any help getting un-stuck on this challenge.

Have the function take a structure that defines the constraint to apply and parse that structure into a SQL where expression, then execute it.

The structure could be represented as JSON, XML, or any other type that can be handled by postgresql built-ins or your code.

For safety aghainst injection attacks be sure to not copy any raw data from the struct to the query, all values and identifiers should be quoted (quote_identifier(), quote_nullable(), etc...) and all operators should come from string literals in the function code.

the function itself would be structured like something like.

CREATE OR REPLACE FUNCTION filter( conditions text )
   RETURNS SETOF sometable
   LANGUAGE plpgsql
   AS $$
        DECLARE
          whereclause text := '';
        BEGIN
          whereclause = whereclause || conditions
          RETURN QUERY EXECUTE
             'SELECT * FROM sometable WHERE ' || whereclause;
        END;
      $$;

but probably with some more involved processing of conditions before the RETURN QUERY EXECUTE


SymmetricDS Setup - SQL Server 2008 to MySQL I need to replicate just a couple of tables from a Microsoft SQL Server 2008 database to a MySQL 5.7 database (one-way only). I've been struggling to find a simple solution.

I've ultimately decided to try SymmetricDS, but the setup is confusing: Assuming I've got SQL Server running on Computer1, and MySQL on Computer2, and I just need Table1 and Table2, how am I supposed to set this up?

As I understand already, I need to add four additional tables to the Master Node (SQL Server in my case), do I add these tables directly to the database I need to copy? This may prove problematic as this database is managed by a very temperamental third-party application and we really don't want to touch it, should we replicate the specific data we need into a local copy on the SQL Server and then "sym" that entire db over to MySQL?

I got SSIS talking my MySQL instance, if it's possible to set that up with a trigger of some kind to automatically run the procedure that would probably work as an alternative.

However, to add, I'm pretty sure I understand the process of creating the .properties files, what I don't fully follow is the Configuration settings, or more specifically, how to set these up properly so the connection is only one-way (the SQL Server should be essentially read-only).

It seem that the official documents are not clear enough to help you.
The steps are below:

You need 1( or 2) intermediate node(s)(Linux or Windows) which have connection between Source and Destination databases and install symmetricDs on it.

You need to build the schema(database structure) on the destination(Mysql) node by symmetricds script or by insert yourself.

You need to create 2 users on 2 nodes who have access at read and write to database

You need to create the engine file for source and destination in the intermediate(s) node similar like this:

For mssql.properties
  engine.name=MSSQL
  db.driver=net.sourceforge.jtds.jdbc.Driver
db.url=jdbc:jtds:sqlserver://[sqlserver:port]/[DatabaseName];useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880

 db.user=[username]
 db.password=[password]

registration.url=
sync.url=http://localhost:8080/sync/MSSQL

group.id=source
external.id=0000000

job.purge.period.time.ms=7200000

job.routing.period.time.ms=5000

 job.push.period.time.ms=10000

 job.pull.period.time.ms=10000

 initial.load.create.first=true

For mysql.properties
engine.name=mysql

db.driver=com.mysql.jdbc.Driver

db.url=jdbc:mysql://<IP>/databasename?tinyInt1isBit=false

registration.url=http://localhost:8080/sync/MSSQL

group.id=destination
external.id=0000002

job.routing.period.time.ms=5000

job.push.period.time.ms=10000

job.pull.period.time.ms=10000

jobs.synchronized.enable=true

On  the intermediate  Node, You will need to CREATE SYMMETRICDS CONFIGURATION TABLES with following bin\symadmin --engine source create-sym-tables
On  the intermediate  Node, You need  sql file containing the initial table configurations to insert to source engine node with at least 4 parameter below to make trigger and synchronization:

insert into sym_node_group (node_group_id, description) values ('MSSQL', 'SQL Servers Group');

insert into sym_node_group (node_group_id, description) values ('MYSQL', 'MYSQL server');

insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('MSSQL', 'MYSQL', 'P');

insert into sym_router
(router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('MSSQL_2_MYSQL', 'MSSQL', 'MYSQL', 'default',current_timestamp, current_timestamp);

insert into sym_channel (channel_id, processing_order, max_batch_size, enabled, description) values ('main_channel', 1, 100000, 1, 'channel for replication');

insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values ('table1','table1','main_channel',current_timestamp,current_timestamp);

insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values ('table1','MSSQL_2_MYSQL', 100, current_timestamp, current_timestamp);

insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values ('table2','table2','main_channel',current_timestamp,current_timestamp);

insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values ('table2','MSSQL_2_MYSQL', 100, current_timestamp, current_timestamp);

And the last step is run the script for source engine node and destination engine node.
If you want the connection is only one-way just


Sql Server delete syntax I recently faced a delete syntax I was unaware of.

delete #fooTbl from #fooTbl where attr ='some'

The Official Microsoft documentation states:

DELETE FROM [database_name . [ schema ] . | schema. ] table_name
[ WHERE <search_condition> ]
[ OPTION ( <query_options> [ ,...n ]  ) ]
[; ]

i have some perplexity about the command I wrote:

What is and what is needed that parameter before the delete keyword (In my example coincides with table name)?
What is the best practice?

What is and what is needed that parameter before the delete keyword
  (In my example coincides with table name)?

Referencing the table is a possibility due to the fact that you could DELETE FROM with an INNER JOIN.

A simple example of deleting from #footbl with an inner join to a different table

DELETE FT
FROM
#DifferentTable DT
INNER JOIN #fooTbl FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';

Whereas this generates an error due to not specifying what table to delete from

DELETE
FROM #DifferentTable AS  DT
INNER JOIN #fooTbl AS FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';

  Msg 156, Level 15, State 1, Line 45 Incorrect syntax near the keyword
  'AS'.

And this also works

DELETE  #fooTbl
FROM #DifferentTable AS  DT
INNER JOIN #fooTbl AS FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';

In short, you need to specify the alias/table to delete from if you are referencing multiple tables, but you don't have to when you only reference the one.

  What is the best practice?

In terms of best practice I don't think that it matters much for the single table. Aliasses can be useful as to make adapting / reusing scripts easier.

Some more examples in the Microsoft documentation

D. Using joins and subqueries to data in one table to delete rows in another table

Using the table name twice

DELETE FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;

Using an alias

DELETE spqh
  FROM
        Sales.SalesPersonQuotaHistory AS spqh
    INNER JOIN Sales.SalesPerson AS sp
        ON spqh.BusinessEntityID = sp.BusinessEntityID
  WHERE  sp.SalesYTD > 2500000.00;

Both serving the exact same purpose.


How much memory does the MySQL server variable table_open_cache cause the DBMS to use? Simple question, but I have been unable to find simple answers.
So the default value for the table_open_cache server variable is 2000. Which means 2000 slots for 2000 files. We can assume each slot takes a few bytes. As we are not discussing Table row data, but Table information. Perhaps 1K or memory each slot.
So, if we increase the cache size by a factor of 10, how much memory is then required by MySQL 5.7? And is the usage linear?

Let's look at it from another point of view -- Is table_open_cache too big or too small?
If Table_open_cache_overflows / Uptime > 1 per second, then increase the setting.
If Table_open_cache_misses / Uptime > 1/sec, then increase table_open_cache
If Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) > 3%, then increase table_open_cacheand checktable_open_cache_instances`.


Database Design Review I´m currently developing a vacationplaner and I´m facing some problems during the database design stage.

The Requirements

The User has a specified amount of Vacation Days. Therefore I have to keep track of how much days he has left (tbl vacationUser & userAccount).

The User can create a request, which needs to be confirmed by another user that is specified as Admin (tbl group). While the Request is unconfirmed, the unconfirmed days are stored (to prevent the user to make too much requests) in the tbl userAccount. After a user from group Admin has confirmed that, the unconfirmed day´s are subtracted from daysLeft.

In addition to the specified group of the User (Admin/ User) EACH User is part of a Department. Each Department has a MinStock, if that is reached, no user of this department can take vacation on these days.

For EACH department exists a User that is responsible for that department, and ONE user who is the "deputy" of that responsible user. If the responsible User HAS vacation, the deputy CANNOT take vacation on these days.

So that is the short version of what I´m trying to do.

The Problem

Database Design

This database design was made with use of the junction tableconcept. The data are mostly normalized and summarized in one table users.

My thoughts on this Design

I thought it would enable me a good way to get all important data, by accessing the Userstable. If a User is a Responsible or Deputy of a department, there would be an ID otherwise it would be null.

Here is my Question - a bit hidden under the "giving you information part"

As I stared to think about this design, i don´t liked that I have two tables that just have an ID responsible & deputy table.
The responsible & deputy of a department are on the one hand Departmentrelated information, but on the other hand userrelated information. By using the junction table users this information are connected, but is it the right way I did that? Or should responsible & deputy just be related to the Department table?

Is it better to connect the userAccount & vacationGroupdirectly with the vacationUser table by setting the userID in those tables and add an FK?
The same for the department and the  `responsible & deputy (my thought´s on this are mentioned above).

I would wish to discuss this design and would like to know if I will run into any problems here.

Any help is welcomed!

If you need some further Information, leave a comment!

Edit:
Here is the entire SQL code, to play with the database SQL

EDIT 2:

I´ve recognized that the current design does not work, because I will never be able to associate a User with a userAccount, because there must an existing  UserAccount, before I can fill the table Users.
So probably my initial thoughts were correct, that I have to connect the userAccount directly with the vacationUser table via the userID.

But when I do this, from the logical part I should also connect the group directly with the user. I would have to add the Column groupID to the user´s table to associated both. Is that correct?

Does it even make sense to use the table users?

Here is a visualization of some of my Edit 2 thoughts, combined with some Ideas from my Initial Concept.
With this design I´ll probably not have the troubles about the Useraccount (EDIT 2), but will keep the easy accessibility to verify if a User is responsible or deputyof a Department. But I´m still unsure if I shouldn´t do it completely the way as I mentioned in EDIT 2. What do you guys think?

SQL 2

I hope someone will still help/ answer me, even if this Question is going to be bigger and bigger. Therefore I will stop here and wait for your ideas :)

First identify the entities in your design (in hierarchical order, i.e. in dependency):

Department
User
VacationRequest

Then identify the relationships:

Each Department have one or more User
Each User can have 0 or more VacationRequest

Then the business rules:

Each Department must have one responsible and one deputy.
If the responsible of the department have a confirmed vacation then in the same period then the deputy can't have a confirmed vacation (and viceversa)

How the rules could be implemented:

Create the tables Department, User, VacationRequest.
In the user table assign the user as responsible of his own department or deputy.
Each time you enter a new VacationRequest asign it with the Confirmed field on false.
Each time you confirm a Vacation via a stored procedure you must check if the vacations not colide with the deputy or viceversa (also you must check if there is a deputy effectively)

I hope it would help you.


Handle multiple simultaneous requests Mysql sorry for this noob question.
I do have an application which expects about 5000 users accessing it simultaneously, my current database is running on RDS and for each request, a query is called, it takes about 30 milliseconds to be executed.
The main caveat is when we open multiple connections the CPU database spikes to 100% and the app starts to getting timeout error.
What solution would be possible to handle so many requests?
Architecture:

3 EC2 (db.t3.large) running behind a load balancer
1 RDS (db.r5.16xlarge) MySql 5.7
1 Index Created for the AAA table, this table has about 17K rows, after index created the number of rows read was reduced to 6754 rows.
When the connections go to spike it achieves up to 2K connections
The Max connections for this instance type is 10K
The time registered on profiling to execute is 0.03705 seconds (before the index it was 0.7 seconds)

SELECT Count(*)
FROM   (SELECT `AAA`.id,
               Degrees(Acos(-0.55754389032929 * Sin(Radians(ZZZ.latitude)) +
                                    0.83014746301876 * Cos(
                                    Radians(ZZZ.latitude)) *
                                    Cos(Radians(151.211111 -
                            ZZZ.longitude)))) * 60 * 1.1515 * 1.609344 AS
               distance
        FROM   `AAA`
               LEFT JOIN 'ZZZ'
                      ON `AAA`.`userid` = 'ZZZ'.`id`
               LEFT JOIN 'BBB'
                      ON 'ZZZ'.`id` = 'BBB'.`userid`
               LEFT JOIN 'CCC'
                      ON 'ZZZ'.`id` = 'CCC'.`userid`
               LEFT JOIN 'EEE'
                      ON 'CCC'.`badgeid` = 'EEE'.`id`
               LEFT JOIN 'FFF'
                      ON 'ZZZ'.`id` = 'FFF'.`userid`
        WHERE           ( ( `AAA`.`active` = 1 )
                 AND ( `AAA`.`approved` = 1 )
                 AND ( 'ZZZ'.`active` = 1 )
                 AND ( 'ZZZ'.`verified` = 1 ) )
               AND ( St_contains(St_geomfromtext( 'POLYGON((151.30120109009 -33.97620109009, 151.30120109009 -33.79602090991, 151.12102090991 -33.79602090991, 151.12102090991 -33.97620109009,151.30120109009 -33.97620109009 ))'), AAA.`position`) )
 GROUP  BY `AAA`.`id`
 HAVING distance < 10) `c`;

According to your screenshot, it looks like the CPU is spiking before the connections grow. Not sure if you can conclude they are exactly correlated unless this is the only time you see the spiking occur and it's always reving up exactly just a couple minutes before the connections grow (then maybe something just before the connection is registered in your counter). But even your graph shows the lowest point of CPU utilization was at your highest point of connections, so I don't think that's your issue here.
As Michael Lutz mentioned, you should look into Connection Pooling on AWS to see if helps with the Timeouts you're experiencing. But it's also possible your query is not performant when ran concurrently, at high rates of calls, or for whatever varying predicates that could potentially be used.
Without seeing your query, the EXPLAIN AND ANALYZE, and information about your indexes, there's not much more information we can provide. If you can update your post with this information, I'll update my answer accordingly, should any other potential sources of issues become evident.


CU Update to Sql Server 2014 hangs for 7 hours How to improve time for patching installs?  Applying patches on test Hyper-V VM's prior to production patching.  It is a two node Always-On AG.

From Detail.txt when I let it run:

(01) 2017-04-26 15:55:36 Slp: Running discovery on remote machine: SQLHADEV1

(01) 2017-04-26 21:48:32 Slp: Discovery on SQLHADEV1 is complete

Things I've checked/done:

Resources are lightly used shown in task manager
Validated the Cluster
Overkill on security, the account running the SQL Services on both nodes is sysadmin on both nodes
Attempted to get cluster log but it error-ed

PS SQLSERVER:\> Get-ClusterLog -Destination C:\tempsql
Get-ClusterLog : The network name cannot be found.

Tested install media on non-AG local box and it installed as expected
Applied windows updates on both servers, failed over, failed back, and restarted servers.  Successful automatic failover

HKLM\System\CurrentControlSet\Services\LanmanServer\Parameters\AutoShareServer to 1. restart the server service.  Validate my navigating to \servername\C$.  If you can't navigate there, the Admin$ share is not available.
Ran the patch install on a non-AG Hyper-V VM successfully
No errors or messages in the Windows Event Logs or SQL Server event logs on either node that are telling.

Changing the reg key HKLM\System\CurrentControlSet\Services\LanmanServer\Parameters\AutoShareServer to 1. restart the server service. Validate by navigating to \servername\C$. If you can't navigate there, the Admin$ share is not available.  In my case something is causing the key to get reverted very quickly.  I'll get with the network admin to suspend this action so the Admin$ share stays available.


Duplicating rows but incrementing the ID each time I have the strenuous task of merging multiple Magento databases together and I am importing hundreds of blocks into the main database but subsequently have to go through every one and change the store ID to associate each imported block with the relevant store in the multi-store environment.

Hold on before you move me over to Magento SE

I'm just after a single query I can run in the database to duplicate a row (x) amount of times and auto-increment the ID column following on from the last in sequence.

For example, I have an existing table like so:-

...
╔══════════╦═══════════╗
║      id  ║  store_id ║
╠══════════╬═══════════╣
║       49 ║        1  ║
║       50 ║        1  ║
║       51 ║        1  ║
║       52 ║        1  ║
║       53 ║        1  ║
║       54 ║        1  ║
║       55 ║        1  ║
║       56 ║        1  ║
║       57 ║        1  ║
║       58 ║        1  ║
║       59 ║        1  ║
╚══════════╩═══════════╝
...

And I need to add new rows like so:-

...
╔══════════╦═══════════╗
║      id  ║  store_id ║
╠══════════╬═══════════╣
║       60 ║        2  ║
║       61 ║        2  ║
║       62 ║        2  ║
╚══════════╩═══════════╝
...

So that the end result is like so:-

...
╔══════════╦═══════════╗
║      id  ║  store_id ║
╠══════════╬═══════════╣
║       58 ║        1  ║
║       59 ║        1  ║
║       60 ║        2  ║
║       61 ║        2  ║
║       62 ║        2  ║
║       63 ║        2  ║
║       64 ║        2  ║
║       65 ║        2  ║
║       66 ║        2  ║
║       67 ║        2  ║
║       68 ║        2  ║
╚══════════╩═══════════╝
...

I am hoping there is a single query that I can use which can add each row with a specified store_id of 2 and allow the id to auto-increment?

I have unfortunately not managed to find an example of this online but I suspect it should be possible/relatively straight forward?

If the id column has the AUTO_INCREMENT property, then it's pretty straight-forward. Don't include it in the insert list:

INSERT INTO tableX (store_id, column3, column4, ...)
SELECT 2, column3, column4, ...
FROM tableX
WHERE store_id = 1 ;

This will insert as many rows as there are with id = 1.

If you want to do this for multiple store_id values (not only for 2), i.e. for example for 10 store ids, inserting 10N rows where N is the number of rows with id = 1, you can extend to:

INSERT INTO tableX (store_id, column3, column4, ...)
SELECT s.store_id, t.column3, t.column4, ...
FROM tableX AS t
  CROSS JOIN
    ( SELECT 2 AS store_id UNION ALL
      SELECT 3 UNION ALL
      ---
      SELECT 11
    ) AS s
 WHERE t.store_id = 1 ;


How to repair corrupted PostgreSQL pg_toast error The hard drive on my PostgreSQL 9.5 server died, but I managed to recover most of the data. I've restored the database to a solid state hard drive, and I can access most of the data. However, when I try to access certain rows on my single biggest table, I get errors like:

ERROR:  could not read block 28126 in file "base/106800/107273": read only 0 of 8192 bytes

I've mostly fixed this by going through the rows and deleting the ones that were not retrievable. However, I still get errors like:

ERROR: missing chunk number 0 for toast value 151065 in pg_toast_106852

Is there any way to fix this? Googling doesn't find any suggestions. I found this similar question, but the sole answer is essentially "I think your database is corrupt", which is diagnosis but not a solution.

Other proposed solutions were to do a vacuuming or reindexing, both on the table and the entire database, but those had no effect.

How do I repair this damage? I know truncating the table would fix it, but that would destroy all remaining data, and most of the records appear to be intact.

I'm sorry you ran into that kind of problem, due to device failure. I suppose you don't have any backup (either physical or logical) from before the corruption.

You may find interesting things in Christophe Petus's conf talk "Corruption war stories". You'll find the slides here and the youtube video there.

What you could do is trying to copy all your uncorrupted data in another table (created with create table like for example to preserve constraints, comments and indexes), drop the other table and rename the new one...


SQL Server 2014 instance count What is best practice for SQL Server 2014 regarding number of instances per node? I know SQL Server 2014 added CSVs that will do away with the drive letter limitation, but what is best practice on the number of instances per node? To my knowledge, SQL Server 2008 R2 best practice was 5 per node.

You've asked a subjective question, but you'll find that many of us will say that the best practice is to have only one instance per node if it's a production cluster. If you have two instances, then many of us will say you need 3 nodes: one node for instance1, one node for instance2, one node is passive and is ready to be used if either of the other two nodes go down.

Brent Ozar recently blogged about this instance stacking topic. There are benefits and drawbacks to instance stacking. One such benefit is licensing and one such drawback is performance tuning.

Disclaimer: I work for Brent Ozar Unlimited. The referenced article is from our web site and is written by my boss.

SQL Server has supported mount points since SQL Server 2005, so drive letters didn't limit the number of instances even ~12 years ago unless you got really crazy with tons of instances in a cluster.

I have never heard that there's a 5 instance per node best practice for 2008 R2. I have had 4 instances on one node in a test environment, but the databases were tiny and very little utilization. I did not have to worry about performance tuning on that server at all.

Now if you were to change your question to ask what is the maximum number of instances you should have on a node, then you might get a different answer. But that answer is going to be dependent on the hardware of the server and workload of the instances. I still say 1 instance for each node though if it's a production cluster.


MySQL replication: slave is not getting data from master My problem is that all setup is done for MySQL replication but the slave is unable to sync the master's data. To understand I am sharing link below; please visit.

mysql>  SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.110
                  Master_User: slaveuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000033
          Read_Master_Log_Pos: 402
               Relay_Log_File: VoltyLinux-relay-bin.000046
                Relay_Log_Pos: 317
        Relay_Master_Log_File: mysql-bin.000033
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: replica
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 402
              Relay_Log_Space: 692
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: f1739fcc-0d2d-11e6-a8cc-c03fd56585b5
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 60
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

https://stackoverflow.com/q/36929641/2644613

From your Show slave status \G output, the following things can be understood:

Slave_IO_Running: Yes It means, Input Output thread is running - this thread is responsible for downloading data (binlog) from master server to slave server.
Slave_SQL_Running: Yes It means, SQL thread is running - this thread is responsible for executing SQL from relay log (from binlog).
Read_Master_Log_Pos: 402, Exec_Master_Log_Pos = 402, It means, the IO thread reading from the binary log position 402 and the SQL thread executing the same query.
Seconds_Behind_Master: 0 It means, both the master and slave is in sync and slave is in up-to the point, no delay.
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates. It means slave is up to the point.

It seems, slave is getting data from master and there is no issues in your settings.

FYI:

Run the Show Slave Status \G command continuously, you will see the following variable values will change, every time.

Master_Log_File: mysql-bin.000033
Read_Master_Log_Pos: 402
Relay_Log_File: VoltyLinux-relay-bin.000046
Relay_Log_Pos: 317
Exec_Master_Log_Pos: 402
Relay_Log_Space: 692

In case, if your slave stopped getting data from master server, you will see the No value for Slave_IO Running variable in your Show Slave Status \G command output as follows.

Slave_IO_Running: NO

I hope this answer will help you. Thanks.


MySQL query: select max(A) and use MAX(pkey) if multiple max(A) records exist I've been trying to find a solution for this problem using just 1 join. Is it possible?

table:

pkey | uid | A | B | C
100  | 1   | 5 | 2 |
101  | 1   | 5 | 3 | 'should find this'
102  | 1   | 4 | 6 |
103  | 2   | 5 | 2 | 'should find this'
104  | 3   | 2 | 7 |
105  | 3   | 1 | 1 |
106  | 3   | 2 | 1 | 'should find this'

Now i need to select records with pkey 101 and 103. I use this query to select max(B).

SELECT table.*
FROM (
     SELECT uid, max(A) as maxA
     FROM table
     GROUP BY uid
) as maxlog
JOIN table
ON table.uid = maxlog.uid
AND table.A = maxlog.maxA

It returns two records for uid 1 (pkey 100 and 101). How can i filter on max(B) in the same query with adding another JOIN?

It looks like what you want is: for each uid, find the MAX(pkey) associated with the MAX(A) value of A.

If that's what you need, try this:

SELECT table.*
FROM (
     SELECT uid, A, MAX(pkey) as pkey
     FROM table t
     WHERE A = (SELECT MAX(A) FROM table WHERE uid = t.uid)
     GROUP BY uid, A
) as maxlog
JOIN table
ON maxlog.pkey = table.pkey

Note that, while this technically only has one JOIN, that's really just semantics - the SELECT MAX(A) subquery is, in effect, a JOIN (and might perform better if written as such).


Cannot run a job to update table on a linked server I am running SQL Server 2012 on a server (server 1) and have set up a job to run a stored procedure which will summarize data from a series of tables and update a table on a linked server (server 2) which is also SQL Server 2012. I can successfully run the stored procedure on it's own, but when the scheduled job runs, I get the following error:

  Executed as user: NT AUTHORITY\NETWORK SERVICE. The OLE DB provider "SQLNCLI11" for linked server "222.222.222.222" reported an error. Authentication failed.
  [SQLSTATE 42000] (Error 7399)  Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "222.222.222.222".
  [SQLSTATE 42000] (Error 7303)  OLE DB provider "SQLNCLI11" for linked server "222.222.222.222" returned message "Invalid authorization specification".
  [SQLSTATE 01000] (Error 7412).  The step failed.,00:00:00,16,7412,,,,0

I suspect this is a security issue, but cannot find the settings that will let me run this.

The SQL Server 2012 builds that were released after SQL Server 2012 was released. You must apply a SQL Server 2012 hotfix to an installation of SQL Server 2012. The fix for this issue was released in Cumulative Update 1 for SQL Server 2012.  For more detail please read:
https://support.microsoft.com/en-in/help/2652582/fix-invalid-authorization-specification-error-message-when-you-run-the


List all users on SQL Server for auditing I'm using SQL Server Management Studio 17 and I'm trying to list all the users for auditing in an organized way with mentioning their database name.

I tried the query below but it's showing the users in different schedules and some users are duplicated.

exec sp_MSforeachdb 'select * from ?.sys.sysusers'

What could I use/try instead?

Users are shown 'duplicated' simply because each database has its own users.

It may help to extend your query a little, so you can see which database each set of users is coming from:

exec sp_MSforeachdb 'select ''?'' as DBNAME, * from ?.sys.sysusers'

Note the important differences between a Login and a User :

  A Login is for Authentication.
  A Login verifies that you can access the server.
  There are two types: SQL Logins and Windows Logins.
  Logins are created at the SQL Server instance level (and stored in master).

  A User is for Authorization.
  Authorization decides what you can do in a database.
  A User needs to be linked to a Login.
  A User doesn't have a password - the Login decides where that is stored.
  It is quite usual to have Users from different databases linked to a single Login on the server.
  Users (and their Roles) are stored in each database, not in master (see Security > Users in each database when using SSMS).


SELECT sql_function(...) vs SELECT * FROM sql_function(...) I'm reading the PostgreSQL documentation for the first time and when facing SQL functions I think I have understood all the basics, but still can't see the difference between SELECT sql_function(...) and SELECT * FROM sql_function(...) and when to use one or the other.

You typically use a function in FROM if it is a set-returning function (SRF). Such functions return a result set rather than a single value or tuple.
But, confusingly, this is not written in stone in PostgreSQL:

You can also call a SRF in the SELECT list, as in SELECT f().
As the documentation says:

Functions returning sets can also be called in the select list of a query. For each row that the query generates by itself, the set-returning function is invoked, and an output row is generated for each element of the function's result set.

You can use any function in the FROM clause. It is then treated as a SRF that returns only a single row.