texts:
I have 3 tables with this structure:

+-----------+   +-----------+   +-----------+
|   Files   |   | Programs  |   | Channels  |
|-----------|   |-----------|   |-----------|
|    ID     |   |    ID     |   |    ID     |
|   State   |   |   State   |   |   State   |
| ProgramID |   | ChannelID |   |    ...    |
|    ...    |   |    ...    |   |    ...    |
+-----------+   +-----------+   +-----------+

The tables Files and Programs has millions of records. When I run this query with one JOIN it's very fast (< 1 sec):

SELECT f.[ID] AS [ID], p.[Id] AS [ProgramID]
FROM dbo.[Files] f
INNER JOIN dbo.[Programs] p ON f.[ProgramID] = p.[ID] AND p.[State] = 1
WHERE f.[State] = 1
ORDER BY f.[ID] ASC OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

But if I add the second JOIN it will be very slow (about 12 sec):

SELECT f.[ID] AS [ID], p.[Id] AS [ProgramID]
FROM dbo.[Files] f
INNER JOIN dbo.[Programs] p ON f.[ProgramID] = p.[ID] AND p.[State] = 1
INNER JOIN dbo.[Channels] c ON p.[ChannelID] = c.[ID] AND c.[State] = 1
WHERE f.[State] = 1
ORDER BY f.[ID] ASC OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

I found out that it's because a very large data flowing from the first join, looking at the execution plan:

Now, how can I increase the performance?

These are create table queries:

/****** Object:  Table [dbo].[Channels] ******/
CREATE TABLE [dbo].[Channels](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [State] [bigint] NULL,
    PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MyDatabase]
) ON [MyDatabase]

GO

CREATE INDEX IX_State ON [dbo].[Channels] ([State])
CREATE INDEX IX_TEST_Channels ON [dbo].[Channels] ([ID], [State])

GO

/****** Object:  Table [dbo].[Programs] ******/
CREATE TABLE [dbo].[Programs](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [State] [bigint] NULL,
    [ChannelID] [bigint] NULL,
 CONSTRAINT [PK_Programs] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MyDatabase]
) ON [MyDatabase] TEXTIMAGE_ON [MyDatabase]

GO

ALTER TABLE [dbo].[Programs]  WITH CHECK ADD  CONSTRAINT [FK_Dbo.Programs_Dbo.Channels_ChannelID] FOREIGN KEY([ChannelID])
REFERENCES [dbo].[Channels] ([ID])
GO

ALTER TABLE [dbo].[Programs] CHECK CONSTRAINT [FK_Dbo.Programs_Dbo.Channels_ChannelID]
GO

CREATE INDEX IX_ChannelIDState ON [dbo].[Programs] ([ChannelID], [State])
CREATE INDEX IX_TEST_Programs ON [dbo].[Programs] ([ID], [State])

GO

/****** Object:  Table [dbo].[Files] ******/
CREATE TABLE [dbo].[Files](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [State] [bigint] NULL,
    [ProgramID] [bigint] NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MyDatabase]
) ON [MyDatabase] TEXTIMAGE_ON [MyDatabase]

GO

ALTER TABLE [dbo].[Files]  WITH CHECK ADD  CONSTRAINT [FK_Dbo.Files_Dbo.Programs_ProgramID] FOREIGN KEY([ProgramID])
REFERENCES [dbo].[Programs] ([ID])
GO

ALTER TABLE [dbo].[Files] CHECK CONSTRAINT [FK_Dbo.Files_Dbo.Programs_ProgramID]
GO

CREATE INDEX IX_ProgramIDState ON [dbo].[Files] ([ProgramID], [State])
CREATE INDEX IX_TEST_Files ON [dbo].[Files] ([ID], [State])

GO

Stats are up to date. I updated them again to be sure.

This is a shared database and I can't change the structure. The State column can have up to 10 possible values.


texts: Reclaiming data file free space after deleting a large amount of data

texts:
I am an accidental DBA. We have a sql cluster that hosts 10 databases. Suddenly the size of one of the database increased drastically from 100GB to 250GB. When we checked the datafile, the size had grown more than twice over the last few days. We identified the tables and truncated the data and deleted 130GB worth of data. The datafile is still showing 250GB. How do we reclaim the space?

Thanks a lot for all your help.


texts: Identity/Primary Key columns pattern for performance in SQL?

texts:
I was discussing about database layout/design with a friend and I got some questions about performance and best practices. I'm mainly a software programmer, so I may lack "common sense" regarding databases, so if you can please explain as you would for a newbie.

I have a table that records the transactions of users and their credit card usages. It has a few millions of records per day. People in my country have a 11 digit number that is supposedly unique.

I've always designed tables with an auto-increment int column as identity.
In this case, the identity column is a composite of the user's social number + the date, which is a huge number column, and the fact that it's non-incremental seems like it's worse when doing a query with WHERE.
What's the best option in this case?

Another question is about primary keys, stores do need to look up the table and they usually send the social number or, most commonly, the user's name.
Would making the user name column as primary key enhance performance? And why is that?


texts: Allow access for localhost and named server

texts:
I'm trying to set up a user to allow them access from localhost and from a specific named server. Is this possible in MySQL and if it is how do I do it?

I know I can use % under the host to allow access from any server, but I'm looking to limit the access to let's say localhost and testserver1.


texts: Exporting from Oracle Cloud, importing on local database

texts:
I am currently experiemting with oracle databases.
I have a free tier at Oracle Cloud, with a free instance of autonomous database.
As I like to have my backups locally I did an export, basically following this tutorial, but on a local VM.

Export works fine and I moved it to the Oracle object storage, with the script described in the blog post.

Next, I wanted to import it into a local Oracle database I have setup on a VM on my local PC. (Guest operating System: Oracle Linux, DB: Oracle 18 Enterprise)

How I do the export from Oracle cloud:

[root@oracle-linux-vm ~]# expdp admin@dbname_high TABLES=TABLE1 data_options=group_partition_table_data parallel=1 dumpfile=table.dmp

Export: Release 18.0.0.0.0 - Production on Thu Apr 2 08:05:19 2020
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "ADMIN"."SYS_EXPORT_TABLE_01":  admin/********@dbname_high TABLES=TABLE1 data_options=group_partition_table_data parallel=1 dumpfile=table.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ADMIN"."TABLE1"                            5.078 KB       2 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "ADMIN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ADMIN.SYS_EXPORT_TABLE_01 is:
  /u03/dbfs/A0AF0C3CCE4D3568E0535A18000AD882/data/dpdump/table.dmp
Job "ADMIN"."SYS_EXPORT_TABLE_01" successfully completed at Thu Apr 2 06:07:37 2020 elapsed 0 00:02:06

[root@oracle-linux-vm ~]#

I download the file from Oracle object storage and move it to the DATA_PUMP_DIR Folder

[root@oracle-linux-db-ee ~]# ls -al /opt/oracle/admin/ORCLCDB/dpdump
total 172
drwxrwxrwx. 2 oracle oinstall     23 Apr  2 06:12 .
drwxr-x---. 6 oracle oinstall     64 Mar 31 06:41 ..
-rw-r--r--. 1 oracle oinstall 176128 Apr  2 06:10 table.dmp
[root@oracle-linux-db-ee ~]#

When trying to import the data, it always fails.

In my local DB/VM:

[root@oracle-linux-db-ee ~]# impdp admin/pwd4admin@ORCLCDB  dumpfile=table.dmp logfile=1.log TABLES=TABLE1
DIRECTORY=DATA_PUMP_DIR

Import: Release 18.0.0.0.0 - Production on Thu Apr 2 05:31:46 2020
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
ORA-39002: invalid operation

Looking at the logfile, I can't get a hint what is going wrong:

[root@oracle-linux-db-ee ~]# cat /opt/oracle/admin/ORCLCDB/dpdump/1.log
;;;
Import: Release 18.0.0.0.0 - Production on Thu Apr 2 06:16:52 2020
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "ADMIN"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Job "ADMIN"."SYS_IMPORT_TABLE_01" successfully completed at Thu Apr 2 06:17:02 2020 elapsed 0 00:00:06
Job "ADMIN"."SYS_IMPORT_TABLE_01" successfully completed at Thu Apr 2 06:17:02 2020 elapsed 0 00:00:07

The schema "admin" is already created manually on my local db.

Any ideas what I am doing wrong?


texts: Do we have any File Table / File Stream like support in any other database than MS SQL Server?

texts:
I am looking for an alternative to Microsoft SQL Server's File Tables / File Stream feature. The reason is because I really wanted to use SQL Server File Tables but I am on Linux and using docker.
Unfortunately, Microsoft does not provide any support for Linux when it comes to File Tables. See this Issue which is still open. MySQL or PostgreSQL is preferable though.
SQL Server File Tables uses Operating System's File System to store files and synchronize these file values in SQL Tables. It is much faster than saving a BLOB. Also, it is backed up if I run Database Backup. So, its using File System for speed but also keeping it synchronized with Database. I can also access the files via File System and / or via transactions.


texts: Do I need to leave some extra space available on the disk which my database log files reside, for log backup operations to successfully occur?

texts:
If I size my log files to evenly split the entire drive they reside on, leaving no extra space available, will log backups still be able to occur successfully?  I have several databases, with one log file per database.

Is it good practice to not leave any space on the drive available, i.e. allocate it all to the log files? (The drive is dedicated to the log files in this case, data and the OS live on their own partitions.)


texts:
I have this query:

Select To_Char(Order.Date, 'IW') As "Week No",
       Order.User As "User",
       Count(Order.OrderNo) As "Total Orders",
       Sum(Order_Totals.Qty) As "Total Items",
From Order Order,
     Order_Totals Order_Totals
Where Order.OrderNo = Order_Totals.OrderNo
And (Order.Date Between '01-JAN-15' And '31-DEC-15')
Group By Order.User

But it throws the error message:

Not a valid GROUP BY expression

I want to show how many orders and items each user has created each week this year, looking at http://www.techonthenet.com/oracle/group_by.php I thought that I am using GROUP BY correctly, but clearly not and I am not sure how I can sort it.


texts: How to use a table as a reference for another table in MySQL

texts:
I have code from generic SQL that I need to find out how to run in MySQL:

CREATE TABLE firstTable
(
freq int NULL,
w1 int NULL,
w2 int NULL,
w3 int NULL
)

CREATE TABLE secondTable
(
[wID] [int] NULL,
[w1] [varchar](50) NULL,
[w1cs] [varchar](50) NULL,
[L1] [varchar](50) NULL,
[c1] [varchar](25) NULL
)

The creation of the first table is simple enough, but MySQL runs into syntax errors with trying to create the second table. I know this code works in another version of SQL, but I only have access to MySQL. The data I need is in [firstTable], and I need to use [secondTable] to look up the identity of the items (coded as integers) in [firstTable].

Also, the data in [firstTable] is really large (though I do have the requisite amount of RAM)... Is there a better way of importing the data than the standard MySQL command that's designed for larger files?

Cheers, and thanks so much!


texts:
This instance hosts the SharePoint 2007 databases (SP). We have been experiencing numerous SELECT/INSERT deadlocks against one heavily utilized table within SP content database. I have narrowed down the resources involved, both processes are requiring locks on the non-clustered index.  The INSERT needs an IX lock on the SELECT resource and the SELECT needs a S lock on the INSERT resource. The deadlock graph depicts and three resources, 1.) two from the SELECT (producer/consumer parallel threads), and 2.) the INSERT.  I have attached the deadlock graph for your review. Because this is Microsoft code and table structures we cannot make any changes.  However, I have read, on the MSFT SP site, that they recommend setting MAXDOP Instance level configuration option to 1. Since this instance is shared amongst many other databases/applications this setting cannot be disabled.

Therefore, I decided to try and prevent these SELECT statements from going parallel. I know this is not a solution but more a temporary modification to help with troubleshooting. Therfore, I increased the “Cost Threshold for Parallelism” from our standard 25 to 40 upon doing so, even though the workload has not changed (SELECT/INSERT occurring frequently) the deadlocks have disappeared. My question is why?

SPID 356 INSERT has an IX lock on a page belonging to the non-clustered index
SPID 690 SELECT Execution ID 0 has S lock on a page belonging to the same non clustered index

Now

SPID 356 wants an IX lock on SPID 690 resource but cannot ontain it because SPID 356 is being blocked by SPID 690 Execution ID 0 S lock
SPID 690 Execution ID 1 wants a S lock on SPID 356 resource but cannot obtain it because SPID 690 Execution ID 1 is being blocked by SPID 356 and now we have our deadlock.

Execution Plan can be found on my SkyDrive

Full Deadlock Details can be found here

If someone can help me understand why I would really appreciate it.

EventReceivers Table.
Id  uniqueidentifier    no  16
Name    nvarchar    no  512
SiteId  uniqueidentifier    no  16
WebId   uniqueidentifier    no  16
HostId  uniqueidentifier    no  16
HostType    int no  4
ItemId  int no  4
DirName nvarchar    no  512
LeafName    nvarchar    no  256
Type    int no  4
SequenceNumber  int no  4
Assembly    nvarchar    no  512
Class   nvarchar    no  512
Data    nvarchar    no  512
Filter  nvarchar    no  512
SourceId    tContentTypeId  no  512
SourceType  int no  4
Credential  int no  4
ContextType varbinary   no  16
ContextEventType    varbinary   no  16
ContextId   varbinary   no  16
ContextObjectId varbinary   no  16
ContextCollectionId varbinary   no  16

index_name  index_description   index_keys
EventReceivers_ByContextCollectionId    nonclustered located on PRIMARY SiteId, ContextCollectionId
EventReceivers_ByContextObjectId    nonclustered located on PRIMARY SiteId, ContextObjectId
EventReceivers_ById nonclustered, unique located on PRIMARY SiteId, Id
EventReceivers_ByTarget clustered, unique located on PRIMARY    SiteId, WebId, HostId, HostType, Type, ContextCollectionId, ContextObjectId, ContextId, ContextType, ContextEventType, SequenceNumber, Assembly, Class
EventReceivers_IdUnique nonclustered, unique, unique key located on PRIMARY Id


texts:
I am looking for something that will help us to create a lot of mysql slaves connected to the specified master, let's say kind of mass-replication.

If there are any existing solution for this purpose. In simple case it can be just bash script, that locks the master DB, copies table files to the new slave, restore position and start the replication.

From you knowledge if something described above exists?


texts: Meaning of SSMS database icon preceded by scroll with red x

texts:
SSMS 2008 R2, connecting to a 2008 instance shows the above glyph in SSMS Object Explorer. None of our other instances show that icon. I've moused over it in the vain hopes that I'd get a useful tooltip but to no avail.

I assume it's related to a failure we had this past weekend. Could someone provide a more authoritative response on what that icon means and any action I should direct my DBAs to take to clear it up?


texts: SQL Server Service Broker messages in one database fails unless SA owns the database? What's going on here?

texts:
I've been attempting to learn how to set up Service Broker through Microsoft's "Completing a Conversation in a Single Database" tutorial. However, when I actually try to send a message, it fails until I change the owner of the database from my account (which appears to be the default) to SA. What's going on here?

I'm using the AdventureWorks2008R2 datafile from CodePlex attached like this:

CREATE DATABASE AdventureWorks2008R2 ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCE\MSSQL\DATA\AdventureWorks2008R2_Data.mdf')
FOR ATTACH_REBUILD_LOG;

I can enable Service Broker and create the message types, contract, and target/initiator services and queues, as instructed in the tutorial:

-- Creating the Conversation Objects
-- http://technet.microsoft.com/en-us/library/bb839488(v=sql.105).aspx

USE master;
GO
ALTER DATABASE AdventureWorks2008R2
      SET ENABLE_BROKER;
GO
USE AdventureWorks2008R2;
GO

CREATE MESSAGE TYPE
       [//AWDB/1DBSample/RequestMessage]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE
       [//AWDB/1DBSample/ReplyMessage]
       VALIDATION = WELL_FORMED_XML;
GO

CREATE CONTRACT [//AWDB/1DBSample/SampleContract]
      ([//AWDB/1DBSample/RequestMessage]
       SENT BY INITIATOR,
       [//AWDB/1DBSample/ReplyMessage]
       SENT BY TARGET
      );
GO

CREATE QUEUE TargetQueue1DB;

CREATE SERVICE
       [//AWDB/1DBSample/TargetService]
       ON QUEUE TargetQueue1DB
       ([//AWDB/1DBSample/SampleContract]);
GO

CREATE QUEUE InitiatorQueue1DB;

CREATE SERVICE
       [//AWDB/1DBSample/InitiatorService]
       ON QUEUE InitiatorQueue1DB;
GO

...and I can begin the conversation:

-- Beginning a Conversation and Transmitting Messages
-- http://technet.microsoft.com/en-us/library/bb839499(v=sql.105).aspx

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle
     FROM SERVICE
      [//AWDB/1DBSample/InitiatorService]
     TO SERVICE
      N'//AWDB/1DBSample/TargetService'
     ON CONTRACT
      [//AWDB/1DBSample/SampleContract]
     WITH
         ENCRYPTION = OFF;

SELECT @RequestMsg =
       N'<RequestMsg>Message for Target service.</RequestMsg>';

SEND ON CONVERSATION @InitDlgHandle
     MESSAGE TYPE
 [//AWDB/1DBSample/RequestMessage]
 (@RequestMsg);

SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;
GO

However, at this point, if I try to receive the conversation, I don't see anything. This query (still following the tutorial) returns NULL:

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_name
  FROM TargetQueue1DB
), TIMEOUT 1000;

SELECT @RecvReqMsg AS ReceivedRequestMsg;

The transmission queue ('select * from sys.transmission_queue') shows one record that is not being transmitted. In the transmission_status field I see:

An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

How is this possible? Certainly I must have permission over objects in my own server, on which I am sysadmin. And I can't imagine "dbo" doesn't exist - this is the default AdventureWorks database straight from Microsoft, and the dbo schema is right there in the schemas list.

Following the unexplained advice on a blog I found, I changed the database owner to SA:

ALTER AUTHORIZATION ON DATABASE::[dbname] TO [SA]

...and the message transmitted perfectly.

So, my question is, what did I really change here? Why does SA, who should have the same rights as me as sysadmin, suddenly make a difference? I presume I'm misunderstanding something about SQL Server's security.


texts: Most efficient encoding for mysql varchar column (english only, a-z, 0-9 and -_)?

texts:
I have table with 1M+ records. Is utf_general_ci is the most efficient encoding for storing only english characters, numbers and symbols -_  in few columns?

I have index on some of that columns and I'd like to make that columns as optimized as possible.


texts: Database Technology for Large Scale Logging

texts:
I am stuck looking for a suitable database technology to suit our application. I am stuck between going cloud-based or keeping it local.
Scenario:
We have a service that receives a log stream for thousands of devices. We have to store every log in order to do queries and analytics on that data. We do about 4-6 GB a day.
We currently have a local machine running SQL Server Enterprise with daily partitions. But the database has reached 1TB now and queries have become very slow and when running big queries it starts affecting the service that is continuously inserting in to the DB.
Local machine specs:

2 * 2TB 7200RPM Drive RAID 1
16GB RAM
i7

So we are stuck between spending a fair amount of money on upgrading the local machine vs moving it to the cloud. However, when looking at cloud solutions, they seem to get very expensive when running at such levels of performance.
I am also open to moving away from SQL Server. I quite like the idea of Azure table storage and how it does easy partitioning. Is there possibly a similar technology that we can run on a local machine?
So my question is: what are my options here?


texts: Best way to create a daily snapshotted read-only db in RDS?

texts:
We have a PostgreSQL db instance in RDS. In our application we need to run a daily export in the early hours of every day, the export and transformation may take some time and one issue we've found is it can export incorrect data if the data is being modified while the export is running (we use limit/offset as our table has ~100 million rows). This is probably a common issue.

To get over this we want to create a snapshot of our instance at midnight and use this for the export.

What's the best way to do this in RDS? Ideally the snapshot shouldn't take too long to create.

I've researched some things to try:

I tried to create a stored procedure to duplicate tables, but then it takes time to reindex etc, and the table name has to be different (which I want to keep the same so the query, which is used in other areas of the app, doesn't have to change).
RDS has read only replicas, but from brief reading they have a lag time so it seems to me they are constantly updated from the master instance. Is there any way to stop updates, or update once a day at midnight?
We have daily snapshots ran at midnight already, and I think we could create an AWS lambda to restore this snapshot (which I believe creates a new instance). It seems clunkier than the read-only replica but doable.
Use pg_dump, but this seems more work than using the AWS snapshot.


texts: Connect to LocalDB via SQLCMD in PowerShell

texts:
I'm trying to access the "localdb\MSSQLLocalDB" server on my computer through PowerShell with the SQLCMD utility. I'm using PowerShell v5, .NET v5.0, and the server name is (localdb)\MSSQLLocalDB when I connect to it in Microsoft SQL Server Management Studio 2014.

PS C:\> sqlcmd -S localdb\MSSQLLocalDB and PS C:\> sqlcmd -S .\localdb\MSSQLLocalDB result in this error:

  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : SQL Server
  Network Interfaces: Error Locating Server/Instance Specified
  [xFFFFFFFF].

I queried the server name in Management Studio with SELECT @@ServerName and used that after the -S in the above command and got the same error.

PS C:\> sqlcmd -S localdb gives this error:

  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Named Pipes
  Provider: Could not open a connection to SQL Server [53]

Other notes: I'm able to connect to the server and work with a database named testdb01 in a C# console app using System.Data.SqlClient with this connection string:

"Data Source=(localdb)\\mssqllocaldb;Initial Catalog=testdb01;Integrated Security=SSPI;"


texts: How to deny writes in all SQL Server databases (including new ones & restored ones)

texts:
I want to create a login that is denied the ability to write to any databases - not just databases that exist today, but any newly created databases or databases that are restored from other servers.
I can't use database-only roles (not even in model), because those won't take effect on newly restored databases.
(Business purpose: I'm writing a blog post on how folks can set up a new login for themselves that has low privilege, and they can make sure that they don't accidentally have an "oops" moment when they copy/paste code or run it without a where clause.)


texts: Framework to effectively identify blocking queries

texts:
I need a routine to effectively identify which queries caused blocking.  This is related to my previous question How to find the query that is still holding a lock?.

I know there is a bunch of material online regarding this, but all of them are based on the premise that the last SQL statement on an active session is most likely the one who acquired the lock (hence generating the blocking), which is not always true (in my case, never).

I've set the blocking-process-threshold to 30 seconds and started analysing the Blocking Process Reports (BPR).
These reports are fired every time a blocking occurs, when the threshold is reached.
It contains information about the blocked spid and the blocking spid.

Often the blocking spid runs a couple of statements after the one that acquired and is holding the lock on a resource (table, page or row): so despite of the report content, I remain clueless about which query exactly caused that block.

Usually the SQL Server DMVs show only the last SQL text for each session_id, and the DMVs related to active locks (such as sys.dm_tran_locks) also don't address this issue.

Tuning the blocked queries is not the best approach here: our application is all based on dynamic SQL embedded on client code, we don't use stored procedures and based on the blockings that I saw until now, all of the blocked queries were correctly indexed and written.

I think an option to solve this would be to collect candidate queries, which could have generated a blocking and then lookup on this info using timestamp and spid gathered on BPR. Do you agree? If so, can you point a way to do this with the least overhead possible using xEvents?


texts: MySQL Table with 568 columns: 1118, 'Row size too large...'

texts:
I'm currently migrating a database from Oracle to MySQL5.6. One table consists
of 568 columns. When atempting to create the table I received this

Error 1118, 'Row size too large..'

I already followed instructions from this link StackOverflow with no success.

Update

I can only add 352 columns maximum. Further additions result in the error above. Any suggestion or tips that you can give are really helpful.

Thank you.


texts: Removing the Oracle Instance details from windows machine

texts:
Actually, I have installed the Oracle 11g Version 1 in my machine. After that instead of
using universal installer for uninstallation manually I had removed the files from installed location.

Now, the impact is if I install the same version in my system, I get the error like the SID
is already available in your system.

What are the ways to remove that installed instance details?

Please help me to get know about that.


texts: Creating a database snapshot for every previous month

texts:
I am new to SQL Server,
I got a task to create a snapshot of existing database for every previous month until current month and do a snapshot once a month from now on.

I created a job to make snapshots once a month, but I am having problem how can i do this for past months.


texts:
Not sure if this is a false-positive issue or not, but I need quell my curiosity about it.  I get the following error message:

  Error: 3041, Severity: 16, State: 1.
  BACKUP failed to complete the command BACKUP DATABASE DBApp. Check the backup application log for detailed messages.

I get this error fairly often, but here is the kicker, when I check the backup folder for the DB in question, the .trn & .bak file are there.  So, it appears it is backing it up, but stating it failed.  The backup is performed by a maintenance plan and it finishes fine while backing up all the subsequent databases.

Any thoughts or should I just completely ignore this from now on as long as the DB is listed in the backup folder?


texts: Why does Oracle still not support Multi-Row insert?

texts:
According to Wikipedia's article about INSERT in SQL, multi-row inserts are standard since SQL-92. Oracle (up to 11g at least) does not support this.
Why?

-- Valid
insert into some_table (magic) values (42);

-- Invalid
insert into some_table (magic) values (42), (666);
-- --> SQL Error: ORA-00933: SQL command not properly ended


texts: Speed up AWS PostgreSQL insertion via Python script

texts:
I have 120,000 csv inside my AWS EC2 instance, each containing 120,000 rows. I can't insert each of them as is into my AWS RDS postgresql DB, that will be 120,000^2 = 14,400,000,000 records. Each csv is about 2 MB.

My approach is:

Python script that converts 120,000 records into just 1 record (list of dictionary) for each csv (now 5 MB after condensing the data) with the help of pandas library
The python script then insert each csv's 1 record into AWS postgresql database via pandas.to_sql (which uses sqlalchemy
I use python multiprocessing module to fully utilize my AWS EC2 to speed up data insertion
I did not create additional indexes in order to speed up my data insertion
I use AWS EC2 instance with up to 25GB of network connection

My observation is:

At the beginning, my code will insert 50 csv per min, which is decent speed
However, right now, with 50k csv being inserted, it only insert 1k csv in 5 hours, about 3.33 csv per min
I tried using psql \copy and realized that it takes between 30-50 sec to insert 1 csv, that's slower than my script that converts the data and insert into the DB directly

I am not sure how I can speed up things up.


texts: How to remove partial duplicates from a table

texts:
I have a database that contains some erroneous results that are essentially but not technically duplicates. The structure is like this:

id_page (PK), id_site, label, create_date

There should only be one entry for each site for each label but it turns out that I have some duplicates for site and label, where id_page and create_date are different. I would like to remove the rows with the lowest create_date.

I think I have come up with a solution for this but I'd appreciate feedback.

WITH duplicates as (
   SELECT id_page, id_site, count(id_site) over (partition by id_site) as ct,
    min("create_date") over (partition by id_site) as dt
   from pages
   where label = '2018-12-15'
   )
DELETE from pages
where id_page in (
   select p.id_page
   from duplicates as d
   join pages as p on (p.id_page = d.id_page
                   and p.create_date = d.dt)
WHERE ct = 2
);


texts: Linked Server - login trying to access database that is not default

texts:
Trying to create a linked server from server A (2005) to B (2008 R2). Everything worked when created. The login on server B was mapped to two databases: DataBase1 and Database2. Database1 was the default.

Database2 was taken off-line. When opening the User Mapping, there is a message, "One or more databases are inaccessible and will not be displayed in list."  I assume Database2 is the culprit. I brought Database2 online and unchecked it.

When testing the connection on the Linked Server, it fails because it can't access Database2. Again, Database1 is the default.

I rebuilt the login using the exact same script with no mention of Database2, still get the error.

Because I need a different Linked Server name that does not match the actual SQL Server name, let's say I want to link to server "A" but call the link "X"

EXEC master.dbo.sp_addlinkedserver @server = N'X'
, @srvproduct=N'Microsoft OLE DB Provider for ODBC'
, @provider=N'MSDASQL', @datasrc=N'X'
, @provstr=N'DRIVER={SQL Server};SERVER=B;UID=test;PWD=*******;'

I can go into SSMS and create a linked server through the GUI. Picking SQL Server, which won't allow me to use a name for the linked server except the same name as the server and it works. This does not use the OLE DB Provider for ODBC.

EXEC master.dbo.sp_addlinkedserver @server = N'B', @srvproduct=N'SQL Server'

Can I script this so I can indicate the link name as X and the server or datasrc?


texts: Troubleshooting error message Invalid Object Name accessing MS SQL with python

texts:
Using python, I can connect to a MS SQL database, but cannot run any queries at all. I always get the error - invalid object name.
I understand from other stack exchanges that I may be accessing the wrong database (although that is specified in my connection); there may be something wrong in my schema (whatever that is); or I simply don't have permission to run a query (I should have been given read only permissions).
My connection looks like:
import pyodbc

connSurvNet = pyodbc.connect(r'DRIVER={ODBC Driver 17 for SQL Server};' +
   ('SERVER={server},{port};' +
   # 'DATABASE={database};' +
   'UID={username};' +
   'PWD={password}').format(
   server= 'xxx',
   port= 1433,
   database= 'db',
   username= 'xxxx',
   password= 'xxxx')
   )

In another database, I can download a table into a pandas dataframe with the following code:
df = pd.read_sql_query('''SELECT * FROM MyTable''', conn)

But from the database in question, MyTable is never a valid object name.
We have a gui interface for querying the data, which allows me to see the SQL-code used for the queries. It includes code like:
FROM [Data].[Version]

I've a pdf of the tables, with all the table names and columns, but nothing I enter as MyTable is ever valid.
I've run a few ideas from Stack Exchanges, like DB_SELECT(), which shows I'm connected to a database 'master', but I do not know of other things I can do to isolate my issue.
I've tried [Data].[MyTable] as well as just Data.MyTable, and even [Data].[Version], but same error. Looking at the Object Explorer in Management Studio, I can see everything - the server is listed, then in a folder Tables, all the tables are listed, like Data.Version.
I can select the top rows, and see the data. What should I look for that will allow me to put the table into my dataframe? I guess it's obvious, but not to me.


texts: MySQL select all records unless user must be present in favorites

texts:
I am having trouble to find the best solution for the following problem;

Description: I want to select all records from an items table, unless it is for favorites only, in that case the user must be present in the owners favorites.

The tables look something like these;

Items table (Table that holds all the items)

+----+----------+---------------+
| id | owner_id | favorite_only |
+----+----------+---------------+
|  1 |        1 |             0 |
|  2 |        2 |             1 |
|  3 |        2 |             1 |
+----+----------+---------------+

Favorites table (Table which holds the owners favorite users)

+----+----------+---------+
| id | owner_id | user_id |
+----+----------+---------+
|  1 |        2 |       1 |
+----+----------+---------+

I hope the above sufficient, if not, please let me know.


texts: SSRS Encryption key error after failover

texts:
I recently encountered an error after a planned failover of an AG to the secondary node after patching it.  I am on SQL 2016 enterprise.  we have the reportserver in the AG, but after failover I got the below error.

This is a 2 node AG, both participating in scale-out deployment.  on the node that was rebooted the scale-out tab in config manager does not show any of the nodes present, but the other node shows both nodes as joined.

I have automation to handle the nessesary restart of the SQL agent and SSRS service that needs to happen after a failover to recognize the new primary node, and it works correctly.
As soon as I restore the encryption key to the primary node the issue goes away...  Does anyone know you would need to restore the encryption key after a failover?  I can understand after a migration or something, not i would not think it should be needed after a failover.


texts: PostgreSQL: Run EXPLAIN on all queries in pg_stat_activity

texts:
I am investigating high load on a PostgreSQL database on RDS. As a part of the investigation, I'm running the following query:

SELECT now()-query_start, state, query FROM pg_stat_activity ORDER BY 1 DESC;

This query returns the longest-running queries on the database. Then, I manually copy the query text to another window, and run EXPLAIN on each query to identify longer queries that need indexing.

Is there a way to automatically run EXPLAIN on each query in pg_stat_activity?


texts: How can I implement data encryption in SQL Express for a VB6 application?

texts:
I am a developer tasked with maintaining a legacy application that is written in VB6. With the upcoming introduction of GDPR we are working on an encryption solution for our users. Our users use a variety of different versions of SQL Server and a variety of OS's. They host their own databases on their own servers.

I have come up with an idea of using column level encryption on the tables using certificates installed on the database server, then using a view to retrieve unencrypted data. Then using an INSTEAD OF trigger to update the encrypted data.

This means no changes to the application for data access. Unfortunately, because we connect via an ADODB.Recordset in many places the system will not allow the data to be updated (it throws an OLE DB generated error).

My next idea was to implement object model classes within the VB6 code and save via a stored procedure. This will involve allot of laborious  code changes so we would ideally like to avoid this.

Does anybody have any experience solving this issue or any advice regarding implementing the correct form of encryption to enable our clients to be compliant?

Thanks in advance


texts: What is the best way to have full text search where data in database is frequently updated

texts:
I am working on an application where the database is continuously and periodically updated, and the frequency of updating of data is daily, weekly, monthly, and yearly based on their type. I want to implement full text search, I am using MySQL and applied full text indexes, and the search is working fine, but the job working for addition/updating of data was adversely impacted.

Does anyone have a solution that can be implemented for full text search for a continuously updated dataset?


texts:
I was looking into my server and I found this:

[root@host ~]# cd /var/lib/mongo/journal/
[root@host journal]# ls -lh
total 3.1G
-rw-------. 1 mongod mongod 1.0G Apr  7 01:18 j._0
-rw-------. 1 mongod mongod   88 Apr  7 01:18 lsn
-rw-------. 1 mongod mongod 1.0G Dec 19 23:03 prealloc.1
-rw-------. 1 mongod mongod 1.0G Dec 19 23:06 prealloc.2

but in my db, db.stats() shows:

> db.stats()
{
    "db" : "gpstracker",
    "collections" : 5,
    "objects" : 59127,
    "avgObjSize" : 139.84318500854093,
    "dataSize" : 8268508,
    "storageSize" : 11198464,
    "numExtents" : 10,
    "indexes" : 3,
    "indexSize" : 1937712,
    "fileSize" : 201326592,
    "nsSizeMB" : 16,
    "dataFileVersion" : {
        "major" : 4,
        "minor" : 5
    },
    "ok" : 1
}

And I have only one database, with 3 collections in it.

Maybe it is something that I don't know, yet.

I searched a bit and found things about compacting a database, can this be of help for this? Or these files are completely normal and they should be this way?

What if my database grows, say from ~200MB (which is now) to ~1G. How much does these files grow? Can these files (journals) be optimized somehow?

Sorry if my questions seem obvious. I'm a beginner in database department.

Thanks in advance


texts:
I'm testing experimental storage (so I expect the numbers to be bad / far off).

I ran the test on the experimental storage and got this:

                         Response Time (s)
 Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
    Delivery   3.01     42.087 :    55.219           11                0   0.00
   New Order  37.70     11.001 :     7.547          138                2   1.45
Order Status   2.19      0.157 :     0.619            8                0   0.00
     Payment  35.79      4.602 :     5.585          131                0   0.00
 Stock Level   1.37      0.777 :     1.675            5                0   0.00

-0.64 new-order transactions per minute (NOTPM)
-212.7 minute duration
73 total unknown errors
2.00 rollback transactions
-1 second(s) ramping up

And then I ran the same test on Azure 30 GB HDD (not a powerhouse either...) and got this:

                         Response Time (s)
 Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
    Delivery   2.31      0.068 :     0.096           10                0   0.00
   New Order  47.45      0.026 :     0.033          205                1   0.49
Order Status   3.94      0.002 :     0.003           17                0   0.00
     Payment  43.98      0.011 :     0.018          190                0   0.00
 Stock Level   2.31      0.005 :     0.009           10                0   0.00

41.07 new-order transactions per minute (NOTPM)
5.0 minute duration
0 total unknown errors
1.00 rollback transactions
0 second(s) ramping up

But I don't understand what am I seeing in any of the columns (well, sans the name of a transaction).  Can anyone kindly explain what do they show?

I'll give you my guesses, just to show I tried to solve the mystery :)

% relative portion of time the transactions of this type spent cumulatively.
Average must be the average latency (i.e. number of seconds it took for an average transaction to go through).
90 % The same as above, but for the 10% of the worst transactions in this group.
Total is the number of transactions performed.
Rollbacks is the number of rollbacks per all (?) transactions in this group.

My Perl skills are nonexistent, so I wouldn't be able to edit the mix_analyzer.pl to make it work with these data.  It's kind of funny how it went into negative space, though it would've been also useful if it worked even with such slow devices...

PS. This is tagged "MySQL" because the test is endorsed by MySQL, and the actual database under test was MariaDB.


texts: User-Defined Table Type script with constraints (SSMS 2016)

texts:
How can one script a User-Defined Table Type with default constraints in SQL Server Management Studio (SSMS) 2016?

I can generate a script, but it does not include these constraints. One cannot script these constraints individually either (whereas this is possible for table constraints). In working with another developer I noted that they were able to generate the script with the constraints using SSMS 2014. Is this a bug in SSMS 2016?

Below is an example of a user-defined table type with constraints. I can execute this, but scripting it using the GUI results in the constraints being removed.

-- Example of UDT with default constraints.

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'UDTTest' AND ss.name = N'dbo')
    DROP TYPE [dbo].[UDTTest]
GO

IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'UDTTest' AND ss.name = N'dbo')
CREATE TYPE [dbo].[UDTTest] AS TABLE(
  [ID] [int] NULL,
  [BitTest] [bit] NULL,
  [VarcharTest] [varchar](50) NULL,
  [VarcharDefaultTest] [varchar](50) NULL DEFAULT (''),
  [IntDefaultTest] [int] NULL DEFAULT ((0)),
  [MoneyDefaultTest] [money] NULL DEFAULT ((0.0))
)
GO

Responses to comments

  Not sure it's the issue, but something to check is that 'Script defaults' is true under Options -> SQL Server Object Explorer -> Scripting. Possibly 'Script bound defaults and rules' too, although I think they're deprecated features. -- Gareth Lyons

I have tried the 'Script defaults setting' as well as all the scripting options (I set everything to true).

Can anyone confirm that SSMS 2016 allows generating scripts of existing UDT with constraints?


texts: Get output in comma separated format in sql server

texts:
When I retrieve the data I get result in congested format.

TableName       TableRows       DataSpaceMB     IndexSizeMB
Sec             2               0.02            0.02
Key             2               0.02            0.01
name            3               0.01            0.02
agenda          1               0.01            0.05
Configuration   19              0.01            0.02
Policies        2               0.01            0.01

How to get result in separated values for different columns in a single row?

E.g
    TableName,TableRows,DataSpaceMB,IndexSizeMB
    Sec,2,0.02,0.02
    Key,2,0.02,0.01
    name,3,0.01,0.02
    agenda,1,0.01,0.05
    Configuration,19,0.01,0.02
    Policies,2,0.01,0.01


texts:
How to get the row with the latest value in a TIMESTAMPZ column? Is an index needed? Does an index change the strategy? Would behavior vary by database (I'm using Postgres 9.4)?

My app records data from a data feed. Another process endlessly queries to get the latest freshest entry. Older data may appear on occasion from secondary sources. So the most recently inserted rows are usually, but not necessarily, the freshest data.

I am using this kind of SQL where when_ is a TIMESTAMP WITH TIME ZONE column:

SELECT *
FROM my_table_
ORDER BY when_ DESC
LIMIT 1
;

This code works (if no NULL values in data!). But with a possible couple million rows, and a query every 10 seconds, I'm concerned about performance.

Without any index on when_ column, does this statement require a full scan of all rows?

Does adding an index change the performance? Will Postgres automatically scan the index to locate the most recent row, or must I do something to make an index scan happen?

With an index on when_ column, should I change this SQL to use some other approach/strategy of query?

Is there some other way to gather freshly inserted rows? I'm using UUID rather than SERIAL types for my primary key, and may federate data amongst multiple database instances, so that rules out checking for ever-increasing integer numbers.


texts: Grouping similar rows together based on time

texts:
I have a table that records all the changes done to a model by a user. The table looks something like this:  (SQLFiddle)

In the table an action_hash is generated based on the model, model_id and timestamp, which effectively generates a unique identifier of an action.

I want to to display the data from this table to my users as an "activity feed". At first I was querying the database like so:

-- Get the last 5 actions (for pagination)
SELECT `action_hash` from `changes` GROUP BY `action_hash` ORDER BY `timestamp` DESC LIMIT 5;

-- Get all the rows for this last 5 action
SELECT * FROM `changes` WHERE `action_hash` IN ('a1a2913ac13c34afd957c1646243d8fa', 'af024d2e0b56c8af6506e8f755bb1e39', '33ccb3d33e4add95486a58dd5b33495a', 'b2fcb43f711ea6e047b4610334e546c8', '85f21b48085cce9ff7bab9c0b26dfb8f')

(SQLFiddle)

I would get a result like above. Then my backend code would go through the results in the last query and group the rows by hash, and parse them to be human readable while still maintaining the pagination.

The problem with this method is that some rows are so similar showing them as separate changes creates too much noise in the activity feed. The similar rows should somehow be grouped together.

The rows highlighted above should be grouped together uniquely identified in my final result (maybe with another hash?) because the changes occurring are happening to the same model (and model_id) within 15 minutes of each other. How can I write a query that shows this?

This is the result I'm looking for:

Note: action_group doesn't have to be integers, any string that indicate they associate with one another will do. I'd also would like to paginate my results based on the new action_group column.


texts:
I've been writing some queries that look like this:

SELECT
  ...
FROM table_a a
LEFT JOIN table_b b
  ON b.id = a.id
LEFT JOIN table_c c
  ON c.id = a.id

In these queries only one of the joins can be successful. In other words, a row from table_a is matched by either a row from table_b or a row from table_c.

In the select list, there is some logic based on which join succeeds. I can test this by doing the following:

SELECT
  CASE
    WHEN b IS NULL THEN
      -- do something
    ELSE NULL
  END
FROM table_a a
LEFT JOIN table_b b
  ON b.id = a.id
LEFT JOIN table_c c
  ON c.id = a.id

When testing if a row is NULL you can use the table alias in an IS NULL expression, but it does not work as expected in an IS NOT NULL expression. If there are columns in table_b that are not NULL, the expression table_b IS NOT NULL will return FALSE.

However, the expression NOT (table_b IS NULL) will return TRUE as expected.

Why this is the case?


texts: Does the SQL Server generate the contents for a derived table contents for every matching record in a join

texts:
I am on SQL Server 2008 R2. I am executing the following SQL a

use tempdb
go
create table t1 ( c1 int, c2 int, c3 uniqueidentifier null)

go
insert t1(c1,c2) values (1,2),(1,3)
go
update t1
set c2 = 5 + c2,
    c3 = t2.c3
from t1 join ( select c1=1, c3=newid())   as t2 on t1.c1 = t2.c1
go
select * from t1
go
drop table t1
go

The result of the query is

c1          c2          c3
----------- ----------- ------------------------------------
1           7           D6BE2119-CECB-4413-94E4-6099E5CC9028
1           8           0BF8A3E2-4E66-4091-A1B0-4FB8E130B347

I was expecting the GUID values in c3 to be the same. It appears that derived table t2 is being "reevaluated" for each row in the table t1 even though it is not a co-related sub-query. Is this expected behavior ?


texts: Subquery on timestampdiff only allows for 1 result, how do I scale this for all results?

texts:
I'm currently writing up a query which is meant to output data to show curves of power profiles, and I'm facing a slight problem. So far, I've managed to do this:

select datetime, PLANT_STATUS, INCOMER_PWR, PLANT_DEMAND, PLANT_OUTPUT
from d909
where
    (PLANT_STATUS >=400 AND PLANT_STATUS < 500)
    OR
    (timestampdiff(minute,
                   (select min(datetime)
                    from d909
                    where PLANT_STATUS >= 400 AND PLANT_STATUS < 500
                   ),
                   datetime)
         <= -1
     )
     OR
     (timestampdiff(minute,
                    (select max(datetime)
                     from d909
                     where PLANT_STATUS >= 400 AND PLANT_STATUS < 500
                    ),
                    datetime)
         >= 1
     );

How do I get the timestampdiff functions to resolve for ANY data, not just the min and max? I realise subqueries normally have 1 row, but I would like to have multiple rows output from it.


texts: Group by specific value and return results in generated column

texts:
I am attempting to figure out how I can pull this off, but I have no clue how to do it.

I have a table (below) that has object_id's, types and values. The values are always unique and the type is always type1, type2 or type3.

id   object_id   type    value
---------------------------------
1    1           type1   123
2    1           type2   456
3    2           type3   789

The end result I'm trying to get is to consolidate all object_id's and return the types associated in "virtual columns" which are type1, type2, and type3.

object_id   type1  type2  type3
-----------------------------
   1        123    456    null
   2        null   null   789

Not sure if this is possible in mySql or if i should just try and build this in PHP instead. Any help would be greatly appreciated


texts: Is there a way to do data export from Amazon Web Services RDS for SQL Server to On-Premises SQL Server?

texts:
We have a large Amazon Web Services RDS for SQL Server instance and we would like to do incremental data transfers from RDS to On-Premesis SQL Server on a regular basis.

The On-prem server will be used to feed information into other systems on an acceptable 1-day delay.

However reading through the docs and searching on google, forums etc, we have not found a seamless way to do off-AWS data transfers using RDS for SQL Server.

Built-in SQL Server functions such as Change Data Capture (CDC) are turned off as well as Replication and off-site Backup/Restore services.

Is there a way to do this or is it a limitation of using RDS?


texts: MariaDB - Replication - Unable to login remotely

texts:
So I've been trying to configure MariaDB replication for our server. Unfortunately I cannot get it replicating correctly.
So I've followed the MariaDB guide to the letter to get replication set up. Master and slave are configured and the slave is set connect to the master. The slave shows the error as:

Lost connection to server at 'waiting for initial communication packet', system error:110 "connection timed out".

I've noticed that I am unable to login to the master from the slave with -u root -p -h xxx.xxx.xxx.xxx. Though the servers are able to ping one another and all ports are allowed between the two, I can ssh between them and transfer files. Both replication and root users have the required permissions to login remotely and locally.
So I trawled google and implemented some changes to try and further diagnose the issue, such as bind address changes and max packet size. But no luck. I also noticed I am unable to login to the master from the master if I specify its IP address rather than just localhost. I get the error:

ERROR 1045 (28000):access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: YES).

Any ideas will be much appreciated!
Master is running the following:
Ubuntu server 16:04, MariaDB 10.0.38
Slave is running:
Ubuntu server 20.04, MariaDB 10.5.4


texts: How to restore a filegroup from its backup in SQL Server

texts:
I need guidance on how to restore a file group in Database from the File group backup taken from another same DB server. Is it possible to restore the filegroup by running restore DB for File Group.

I was trying to restoring filegroup from its back up like below

RESTORE DATABASE <DBName > FILEGROUP = 'MFG12' FROM DISK = 'E:\MFG12.bak'
  WITH RECOVERY

The backup set holds a backup of a database other than the existing DBNAME database.

When I was trying to restore in to same DB , restoring works perfectly,

Processed 840976 pages for database 'DB', file 'MFG12_File' on file 1.
RESTORE DATABASE ... FILE=<name> successfully processed 840976 pages in 59.375 seconds
(110.654 MB/sec).

When I run the Select query against partion of filegroup. it throws error

 One of the partitions of index '' for table 'xyz'
 (partition ID 72057594656129024) resides on a filegroup ("MFG12")
 that cannot be accessed because it is offline, restoring,
 or defunct. This may limit the query result

When I see status of all file group.

I see one of the File Group MFG 12 has status = "Restoring" , How to set back to Online Mode.

When I try to restore log file also.i get below error

The roll forward start point is now at log sequence number (LSN) 66787000000001800001.
Additional roll forward past LSN 66787000000008900001 is required to complete the
restore sequence”.

Please help how do I bring back the missing file group data even though filegroup backup has been taken from another same Database in another server or in the same server DB


texts: Tempdb log file was growing continuously on SQL Server 2008 R2-SP2. Could a pending insert statement which uses temporary table be a cause?

texts:
I just started at my new job as a DBA. All of a sudden, we started getting alerts about disk space. When looked into it, I found that tempdb log file was growing continuously. There was an active transaction running against it. I checked the query and it was using temp tables. From 45 minutes, it hadn't progressed at all. I tried many things including these as a last resort.

use tempdb
GO
DBCC FREEPROCCACHE -- clean cache
DBCC DROPCLEANBUFFERS -- clean buffers
DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
DBCC FREESESSIONCACHE -- clean session cache

But it didn't help at all. I also killed the insert which was stuck. Seems like it was not out of the norm process. Finally I had to take an emergency outage of few minutes and restart SQL Service. Any tips about what I may have missed?


texts: Performance Recommendations for Large Volume of Data

texts:
Oracle Table:

Fields: 55
Roughly 10 are Used for Querying
Single Record Length: 512 bytes
Records: 3-5 million records per day
Period of Time: 13 months to be available, > 13 months to be purged daily or monthly.

Questions:

What is the best mechanism for us to maintain this large volume of data and be able to query with good performance ( partitions ? Monthly tables ? )
Tests on indexing the data have not yielded good results, any suggestions on creating one or more indexes ?
Date format for these records is YYYYMMDDHHMMSSCSS
example:    20190710093054579
Current coding s forcing a SUBSTR or Like to be used in the query
example: SELECT * FROM TABLE WHERE SUBSTR(SYSDATE,1,8)='20190710'

How can we optimize the performance of querying perhaps for a whole day with other fields in the criteria ?

Is it possible to index this properly ? Or should the date and time be separated into 2 fields ?

We have observed positive results by using between 20190710000000000 and 2019071999999999  but performance is still problematic.


texts: SSAS Multidimentional Drillthrough with Many-to-Many Relationships

texts:
I was browsing through the AdventureWorks DW 2012 sample, along with the accompanying SSAS cube sample, and noticed some interesting behavior related to drillthrough when a many-to-many relationship existed.

When looking at the Internet Sales Amount measure for a particular date (ie. July 1, 2005), and then performing a drillthrough to see the related lines, I'd notice that sums of the sales amount on the drillthrough lines didn't match the reported total sales amount. Looking at the base fact table in the warehouse, I found either lines were missing or sometimes were duplicated.

If I did a query combining the FactInternetSales table with the FactInternetSalesReason table, then I'd receive the same results that I got when looking at the drillthrough. Conversely, if I removed the Sales Reason dimension from the set of returned columns for the drillthrough, then I'd see "all" the rows and the totals would match up.

So I'm left with 2 questions:

Is there a way to have drillthrough show "unresolved" rows when a
many-to-many relationship exists, but the source row doesn't have a
matching attribute?
How do I remove duplicate rows in the drillthrough when a source row
has multiple matches in the many-to-many relationship?


texts:
A few months ago I added an extended event to track blocked processes and deadlocks, with a file output to track occurrences.

Every now and again, I check the log file, but find that the extended event has stopped, when I "Start Session" again through SSMS, it picks up again and logs as expected.

How can I ensure that this runs 24/7?
Is it possibly failing somewhere and there's a log of errors that I'm unaware of?


texts: How do you view PostgreSQL messages (such as RAISE NOTICE) in DBeaver?

texts:
I think this is a DBeaver UI/Configuration question, but I cannot see where the messages are when executing a script (Alt-X).

In PGAdminIII, I would execute a script and see the NOTICE output:

In DBeaver, the same script does not output in "Statistics" tab.  But I am not certain if this is where I should be looking for the messages:


texts: Efficient way to join two tables with one to many relationship

texts:
This is interview question asked to me in interview and i was not able to answer. Please help..

What is the most efficient way to join two table with one to many relationship between them


texts:
I getting an error report of:

Error report -
SQL Error: ORA-02270: no matching unique or primary key for this column-list
02270. 00000 -  "no matching unique or primary key for this column-list"
*Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement
           gives a column-list for which there is no matching unique or primary
           key constraint in the referenced table.
*Action:   Find the correct column names using the ALL_CONS_COLUMNS
           catalog view

Can I know why?

parent table

    CREATE TABLE STUDENTINFO
    (
      Student_ID VARCHAR2 (10) PRIMARY KEY,
      Full_Name VARCHAR2 (50) NOT NULL,
      Contact_Number NUMBER (15)NOT NULL,
      Address VARCHAR2 (50) NOT NULL,
      Nationality VARCHAR2 (15) NOT NULL,
      IC_PassportNo VARCHAR2 (15) NOT NULL,
      Programme VARCHAR (75) NOT NULL,
      Email_Address VARCHAR2 (50) NOT NULL REFERENCES                    USERNAMEPASSWORD(Username),
      Parents_Number NUMBER (15)NOT NULL,
      Fingerprint_Template clob
    );

child table

    create table bit_2015_sep_cit4114_fyp_G_
    (
      Student_ID VARCHAR2 (10) PRIMARY KEY REFERENCES STUDENTINFO(Student_ID),
      Full_Name VARCHAR2 (50) NOT NULL REFERENCES STUDENTINFO(Full_Name),
      Nationality VARCHAR2 (15) NOT NULL REFERENCES STUDENTINFO(Nationality),
      Fingerprint_Template CLOB NOT NULL REFERENCES        STUDENTINFO(Fingerprint_Template),
     "23/10/2015" VARCHAR2 (15) not null,
   );

I keep nationality and fingerprint as duplicate because it will be time consuming in verifying based on all information stored in studentinfo, therefore by breaking into individual class will be easier and faster. like for the table STUDENTINFO consist 1 million record, in table bit_2015_sep_cit4114_fyp_G_ will only have 40 records. I keep the nationality column because I have 1 more column in my table structure which is visa renewal which calculated based on the value of nationality in the table.


texts: How to not replicate index creation on ignored tables in MySQL/MariaDB

texts:
I have a setup with two MariaDB masters, each being the slave of the other.
Some tables are ignored, with the configuration option :

replicate-ignore-table = moodle.mdl_temp_%

My problem is when a statistic batch is run against one of the server :
a CREATE TABLE followed by a CREATE INDEX is issued, and the last statement is replicated (looking at the binlogs the CREATE TABLE is effectively not replicated), which broke the replication on the other server.

Who to avoid the CREATE INDEX being replicated ?

[EDIT]
Here is the error on the second server when the statistic batch is ran on the first server.

Error 'Table 'moodle.mdl_temp_stats_daily' doesn't exist' on query. Default database: 'moodle'. Query: 'CREATE INDEX mdl_tempstatdail_cou_ix ON mdl_temp_stats_daily (courseid)

And when I take a look at the binlogs, there's no "CREATE TABLE" but there is still the CREATE INDEX :

# mysqlbinlog /var/lib/mysql/binlog/mysql-bin.000101 | grep -i create
CREATE INDEX mdl_tempstatdail_cou_ix ON mdl_temp_stats_daily (courseid)
CREATE INDEX mdl_tempstatdail_tim_ix ON mdl_temp_stats_daily (timeend)
CREATE INDEX mdl_tempstatdail_rol_ix ON mdl_temp_stats_daily (roleid)
[...]


texts: Non numeric attributes in fact table (to track data source)?

texts:
I am creating a data warehouse using star schemas, following the Kimball methodology. One of the rules is only put numeric values in fact tables.

I am considering putting an "id" in a fact table, in order to track the source of the data that the fact was inserted from. Users may upload data in a variety of ways, I am required to support a use case where a user has made a mistake in their data upload and wishes to redact it. I am considering therefore putting job and row ids on all uploaded data and keeping the raw data uploaded (prior to ETL from a staging area). This way the users will be able to see a log of uploads, and can redact them and replace with correct data if need be.

Is it really bad to put these ids in the fact table? An alternative would be to put them in a special tracking dimension. However, this dimension will have one row per fact table row, so does it not make sense to put them in the fact table?


texts: Copy tables selected from sys.tables to new DB

texts:
I have a DB with thousands of tables.

I want to copy all tables with name like 'TableType1%' to a new DB.

I can easily get a list of the tables:

  select * from sys.tables t where schema_name(t.schema_id) = 'S1' AND
  [name] LIKE 'TableType1%'

But how do I copy them to a new DB?

I CANNOT do this manually, as there are too many tables.

I would like to use

  INSERT INTO

type statement, but don't know how to put it into select statement above.

Something like:

select * from sys.tables t
insert int [NewDB].[S1].[ t.name ] <<< What should go here?
where schema_name(t.schema_id) = 'S1' AND [name] LIKE 'TableType1%'


texts: Does a view need its own foreign key constraints?

texts:
Disclaimer: I'm a programmer, not a DBA, so bear with me...

I have a view that I use to just map 2 entities together.  I have to do a join between a few different tables to get that:

CREATE OR REPLACE VIEW V_SCREENING_GROUP_SITES AS (
SELECT SG.SCREENING_GROUP_ID, V.SITE_ID
FROM SCREENING_GROUP SG, VISIT V, VISIT_DATE VD
WHERE VD.VISIT_ID = V.VISIT_ID
AND V.SCREENING_GROUP_ID = SG.SCREENING_GROUP_ID);

Above is just for context, don't worry too much about that.  What I need to know is how to make the fields in my new V_SCREENING_GROUP_SITES view (SCREENING_GROUP_ID and SITE_ID) behave as foreign keys to the SCREENING_GROUP and SITE tables.  Or does it even matter?

If it was a table I would do:

ALTER TABLE V_SCREENING_GROUP_SITES
ADD CONSTRAINT FK_SCREENING_GROUP_ID
FOREIGN KEY (SCREENING_GROUP_ID)
REFERENCES SCREENING_GROUP.SCREENING_GROUP_ID;
...

But since it's a view that obviously doesn't work.  I couldn't find an ALTER VIEW syntax that works for setting FKs.  What should I do?

(This is a MySQL Database)


texts:
I want to install and configure a high availability cluster for Mysql 8. To automate that, I want to use use apt repository (via ansible).
My servers are debian jessy (8) distribution.

I succesfully installed mysql server with commands :

> sudo apt-key adv --keyserver pgp.mit.edu --recv-keys 5072E1F5
> echo "deb http://repo.mysql.com/apt/debian jessie mysql-8.0" | \ sudo tee /etc/apt/sources.list.d/mysql80.list
> sudo apt update
> sudo apt install mysql-server

It works fine. Now I want to install Mysql shell to manage a InnoDB cluster. It seems that it is available via apt repository as mentioned in this page :
https://dev.mysql.com/downloads/repo/apt/
And installation is explained here :
https://dev.mysql.com/doc/refman/8.0/en/installing-mysql-shell-linux-quick.html

But when I execute a install via reposiory, I get an error :

> sudo apt-get install mysql-shell

with the message :

  Reading package lists... Done Building dependency tree Reading state
  information... Done E: Unable to locate package mysql-shell

I have same error with server running on debian stretch (9).


texts: Mysql - when increase 'max_connections', what other paramters to adjust accordingly?

texts:
I know some memory / cache / open file related params  should be adjusted accordingly.
But after searching for quite a while, didn't found a comprehensive guide.
Say, mysql 8 is in use, running on Linux, and trying to change max_connections to 400, the database is using innodb engine, could you give a list of other parameters, and their new values to adjust to, in common.


texts: SQL12/14 Maintenance Plans - Local Server Connection Failure

texts:
So interesting problem that I ran into with regards to maintenance plans and SQL Server 2012/2014.

The test case for this is on a clustered Windows 2012 R2 environment install SQL of your choice without SSIS installed.   Create a maintenance plan that just does "select 1" and it will run fine via SQL Agent.  Now, install SSIS, and the job now fails due to:

  Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection.

Even when uninstalling SSIS, the package still fails when being run through SQL Agent.  In all cases, the package can be run via the command line or through Integration Services directly.

The connection string is:

  Data Source=instance\name;Integrated Security=True;Pooling=False;MultipleActiveResultSets=False;Packet Size=4096;Application Name="Microsoft SQL Server Management Studio";

It seems like SSIS changes some type of reference to the package but I can't narrow down what is causing this to fail.  I've compared registry keys as well with other instances and there isn't anything would would reference '.\name' or anything to that effect that would black hole it.

Named pipes, shared memory, and TCP/IP are all enabled and firewall rules both outbound and inbound are open as well.

Thoughts on what to check for next?


texts: how to override a table's cardinality or statistics histogram

texts:
I remember reading once that it is possible to force SQL Server to assume a table is a certain size, or perhaps even override a statistics histogram, when compiling a query.  Can someone explain how to do that, if it's possible?

To be clear: the purpose of this is to do what-if scenario analysis, outside of production, when writing queries.  I am not talking about modifying the query itself or using query hints.  (Examples of modifying the query and using query hints can be found in the answers to this question.)


texts: 6 datacenters running the same mysql database, replication layout design question

texts:
So each of the datacenters is around the conus. The Master slave replication route seems like my best option here. I have never done this with more then 1 slave and 1 master. Advice? Links?

The commits to each DB are around 2/second with some spikes of 20/second. ID consistency is my main concern with this. They each have different rates of commits that vary a lot.


texts: SSAS cube process time increased after making fact and dimension small

texts:
I have a cube of 55 GB size which takes around 2 Hour to process full. As I have data for last 4 years but our business wants only 2 years of data.
For that, I have changed dimension and Fact views to have only last two years of data. Now, my cube size is reduced to 32 GB but processing time increased by 30 min (i.e. 2 hours 30 min). I was expecting it to be less as I have restricted good amount of data going into the cube.
Why the processing time has increased when it should actually decrease?
How can I reduce processing time now?

P.S.: I have already tried cube partition and due to large dimension size it is also increasing processing time.

I am using views where I have restricted the data via WHERE clause.

My view is basically like this:

SELECT V.Col1, V.Col2.... V.Col13
FROM DimeTable V
WHERE <my filter clause>

It is selecting almost all columns from the dimension table so I can't create non clustered index on all these columns as it may slow down insert operation and won't help me much


texts: How to truncate a table without locking it?

texts:
I have a SYSTEM's table AUD$ in ORACLE's database that is constantly being populated (whenever a DB user logs in to DB - a records is written to AUD$).

The table AUD$ has like 100 million records  (100GB in size) and I want to delete data to regain the usable storage (100GB). I don't want to drop the table because it is still needed.

DELETE = does not reallocate the storage.
TRUNCATE = does reallocate the storage but it LOCKS the table, which I cannot afford.

I thought about DBMS_AUDIT_MGMT already but my intention is not only to delete old records and reallocate used storage but also to create another tablespace and partitioned table (seperated from system's SYSAUX) and compress it (columns to be exact, because SQLBIND and SQLTEXT are CLOB's that are very space consuming), which is something I cannot achieve with DBMS_AUDT_MGMT.

So how do I delete records to regain storage without locking the table ?


texts:
I have followed many articles on how to get SQL 2016 always on setup and I am hopelessly stuck on the listener.

One of such articles is http://blog.fedenko.info/2016/06/sql-server-2016-always-on-availability.html

Not exactly what I am looking for but very close, I found it very hard to find a clear step by step and many different articles have different ways of doing it.

My listen problem:

On my DC open AD user and computers added SQL-HA-LISTENER as a computer

In DNS added SQL-HA-LISTENER 192.168.10.82

When i ping 192.168.10.82 i get Destination host unreachable

(192.168.10.82 and SQL-HA-LISTENER are not actual computers on the network)

Telnet 192.168.10.82 1433 closed, as to be expected.

I am confused as to what a listener "Is"

Is it a VM with SQL expressed installed on it?

That would be the only thing that makes sense.

If so how do i need to configure it to be a listener?


texts: Mysql: how to get unique values in column that can have a value or 0

texts:
Mysql

on a simple table (all integers)
A B C

I want to query all rows where C is a distinct number or 0
for example:

0 1 4
4 5 6
6 5 0
5 6 0
7 5 4
8 5 6
9 5 0

Should give:

0 1 4
4 5 6
6 5 0
5 6 0
9 5 0

rows:

7 5 4
8 5 6

didnt match because in col C  4 and 6 already appear.
All the C=0 columns should appear.

Whats the best way to do a query that does distinct C or C=0 ?
the results order doesn't matter.


texts: Question on Enabling Kerberos Authentication using SetSPN for Availability Listener alternative DNS name

texts:
We have Availability Group, and original DNS name that was given to AG Listener, is too long (something like longername-Listener-01.mydomain.com); Its hard for us to change original DNS name of listener at this point

So we simply added new shorter DNS name into DNS server (shortername.mydomain.com), to point to listener's IP address

But the issue is that original long Listener's DNS name, was setup to use Kerberos authentication using Kerberos Configuration Manager tool;
Apparenly this tool simply run command

SetSPN -s "MSSQLSvc/longername-Listener-01.mydomain.com:1433" "MYDOMAIN\SQLSvcAccount"

So currently everyone who connect to original longer DNS name, are connecting to SQL Server using KERBEROS auth_scheme;
And everyone who connect to new shorter DNS name, connecting using NTLM auth_scheme - which we want to avoid

Question:

If we execute following command on Availability Group servers:

SetSPN -s "MSSQLSvc/shortername.mydomain.com:1433" "MYDOMAIN\SQLSvcAccount"

Would it make connections to "shortername.mydomain.com" use KERBEROS auth_scheme instead of NTLM ?
And connections to "longername-Listener-01.mydomain.com" would still be using KERBEROS auth_scheme, too ?


texts: When does Log shipping restore job mark itself to completion in standby mode

texts:
I am editing my previous question to be more precise as i am still unable to get the logic behind the log shipping restore job.

May be there is something i am missing to explain but will try my best.

We have night Log Shipping restore job running on secondary at 10:00 PM daily once. When it starts to restore it picks the log backups from where it completed on its last run. I understand how log restores are done per Kins link, but i see LS restore will try to get as close to current run time and completes. But that time varies with a gap of 30 min, 20 or even an hour sometime.

Its still not clear how ls restore is checking this is the best time it will complete. This is to let users know till what time of the day data is getting restored via LS job, so that they can query and fetch data accordingly from Standby database:

Previous question marked as duplicate @ How log shipping restore works

Eg:  What i see is LS restore completes at 3:30 AM. For log backups restored , i see the files which arrived at 2:45 AM only restored but not the one at 3 and 3:15 AM. So just thinking if these 2 files are copied, why LS restored ignored them and got completed. LS backup and LS copy happens every 15 mins while restore once at10PM.So i need to understand how is LS restore deciding on what basis will it complete?


texts: SQL Server data extract with limited performance impact

texts:
Background:

I have a SQL Server 2008 Enterprise Edition database containing three tables. The tables contain ~3 million, ~14 million, ~14.5 million rows respectively.

Problem:

I need to do a data extract from the database for reporting purposes. This will involved joining the three tables and writing each row to a text file. This database is currently taking production traffic and I want to limit performance impact. This data extract should not prevent reads, writes, or updates from occuring during the course of the process.

From the research I have done, I believe adding the 'NOLOCK' hint to the data extract query will allow the production traffic to behave normally while still accomplishing my primary task of the data extract. I'm assuming the dirty reads associated with the 'NOLOCK' hint will be isolated to the data extract query.

Am I correct to assume that the 'NOLOCK' hint on the data extract query will only affect that one particular query? Does it affect all queries being executed on the database while the data extract query is executing?

Is there a better approach to accomplishing my goal than what I've presented above?


texts: how do i update mulitple rows with dynamic data?

texts:
I am using SQLite to update mulipte rows, but it does not work.
item table to keep the item data
item_ID Qty
-       -
1      10
2      10
3      10

user_basket table to keep users' basket data
user_ID item_ID  Bask_Qty
-        -       -
1        1       5
1        2       1
2        1       1

I used command like:
UPDATE item
SET Qty =
(SELECT Qty-Bask_Qty FROM user_basket
INNER JOIN item ON item.item_ID = user_basket.item_ID)
WHERE item_ID IN (SELECT item_ID FROM user_basket WHERE user_ID = 1);

After the command, I should expect item table be like that:
item_ID Qty
-       -
1      5
2      9
3      10

but instead, I got:
item_ID Qty
-       -
1      5
2      5
3      10

apparently, it used the same value to update all the rows.


texts: What Current Continuous SQL Server Backup Solutions Are there?

texts:
With Mirroring being deprecated in SQL Server 2012 and up along with AlwaysON Availability Groups costing $7k per core, I am wonder what options are there for constant reliable backups with a somewhat reasonable ($2k per server or less) price tag?

I understand that not every solution is going to do everything that Availability Groups.  I am just looking for what solutions there are available that will duplicate changes to another live server as each change happens.  Also, the less intensive the process the better.

Some other sources site Double-Take as a good solution.  That question refers to file system backups rather than SQL but Double-Take does boast that it will work with SQL.  I'm not sure of how reliable that is because Double-Take just sounds like it is file replication and I'm not sure if that is the best choice.

Also, I have looked at solutions like ApexSQL and Toad for SQL and I am not sure which solutions of these would do what I am looking for.

Any solutions with a clear answer as to whether the solution will post SQL changes to a backup server as they occur would be nice.  Any other expertise on the surrounding matter would also be much appreciated.


texts: extension "pg_buffercache" already exists and relation "pg_buffercache" does not exist

texts:
When I try to create the extension pg_buffercahe using the following command:

➤ psql://postgres@postgresql-95-2:5432/postgres

CREATE EXTENSION pg_buffercache;

...it returns the error:

ERROR:  extension "pg_buffercache" already exists

If I try to retrieve information from the pg_buffercahe using the following command:

➤ psql://postgres@postgresql-95-2:5432/postgres

select count(*) from pg_buffercache where not isdirty;

...I receive the error:

ERROR:  relation "pg_buffercache" does not exist

The following packages are installed on my system:

yum list postgresql95*
Installed Packages
postgresql95.x86_64         9.5.10-1PGDG.rhel7
postgresql95-contrib.x86_64 9.5.10-1PGDG.rhel7
postgresql95-libs.x86_64    9.5.10-1PGDG.rhel7
postgresql95-server.x86_64  9.5.10-1PGDG.rhel7

How can I fix this issue?

➤ psql://postgres@postgresql-95-2:5432/postgres

# \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description
--------------------+---------+------------+-----------------------------------------------------------
 pg_buffercache     | 1.1     | public     | examine the shared buffer cache
 pg_stat_statements | 1.3     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

➤ psql://postgres@postgresql-95-2:5432/postgres

# select count(*) from pg_buffercache where not isdirty;
ERROR:  relation "pg_buffercache" does not exist
LINE 1: select count(*) from pg_buffercache where not isdirty;


texts: Is there a disk space impact when choosing NULL vs empty array {} for the default of an ARRAY[] column?

texts:
My intuition is NULL will take up less space than {} when used as the default value of an ARRAY[] column in PostgreSQL.

Am I correct?


texts: PostgreSQL not working, pgAdmin always says server not listening

texts:
I am brand new at this so maybe it's a simple mistake. I was given a very large .sql file with the instructions to make a copy of a database locally on a computer. I was told "to be able to install the database you need to install postgres. Use pgAdmin to create a table called reports with UTF8 encoding".

pgAdmin III always tells me the server is not listening. Postgres is not showing up anywhere in the services in administrative tools. Whenever I install or uninstall Postgres 9.1 or 9.2 I get errors. When I uninstall I get "Error stopping service postgresql-x64-9.2", "Error deleting service postgresql-x64-9.2", "The data directory (C:\Program Files\PostgreSQL\9.2\data) and service user account (NT AUTHORITY\NetworkService) has not been removed".

I was advised to login to the postgres account, however none appears at the login screen. If I try to create an account named postgres I get a bogus error message (see picture)

I was advised to type this into the command line:

When installing I get this error:


texts: How to rotate the CMK of Always Encrypted using SSMS?

texts:
I have implemented the encryption in SQL Server 2016 using Always Encrypted.

I want to know how to rotate the column master key after the initial one year validity using SQL Server Management Studio 17.0.

I'm looking for the steps that can executed using only SSMS and not PowerShell or any other program.

Is it technically possible to rotate the key only using SSMS?

Experts advice please.


texts: Data Migration and altering tables in one SSDT deployment script

texts:
I'm using SSDT and MS SQL Server 2012.
And I need to handle next cases in one deployment script:
--1
CREATE TABLE Table1 (Col1 INT)
--2
INSERT INTO Table1(Col1)
SELECT SomeCol FROM SomeTable
--3
ALTER TABLE dbo.SomeTable DROP COLUMN SomeCol
--4
INSERT INTO AnotherTable(AnotherCol)
VALUES(1)

What can I see now in my deployment script:

--Pre-deployment part
--some insert. Will fail because it select/insert from/to not created objects

--Deployment part
CREATE TABLE Table1 (Col1 INT)
GO
ALTER TABLE dbo.SomeTable DROP COLUMN SomeCol
GO

--Post-deployment part
--some insert. Will fail because it select/insert from/to not created objects

How do I need to configure post/pre-deployment scripts?
Is it possible to do it in SSDT automatically during deployment?

Appreciate for any proposals.


texts: How do I shrink all files quickly for all databases?

texts:
In SQL Server (2008 in this case) how can I quickly shrink all the files, both log and data, for all databases on an instance? I could go through SSMS and right click each and choose Tasks -> Shrink, but I'm looking for something faster.

I scripted some "Create database" scripts and forgot they had ballooned sizes for defaults, and don't need quite that much space reserved for these files on this project.


texts: Is it possible to specify Active Directory username and password in connection string?

texts:
My organization uses Active Directory to control access to our databases. Building web applications and as part of our CI process I would like to specify the credentials in the connection string.

Is it possible to pass AD credentials in a connection string or do I need a standard SQL account for these items?


texts: How to filter specific entries from a MySql table

texts:
I have a MySql table and it has following values.

Table name results.

    Reg.No   SubjectCode   Attempt   Pass/Fail
    112108   CMIS 1113           1   fail
    112110   CMIS 1114           1   pass
    112119   CMIS 1114           1   fail
    112108   CMIS 1113           2   fail
    112107   CMIS 1113           1   fail
    112108   CMIS 1113           3   pass

Following are the business rules.

Students can have several attempts to pass the subject.
Student should pass each subjects to get the degree.
Some student pass in first attempt.
Some take more than 3 attempt.
However student can try until he/she pass.
But some still remain fail.

So I want to get the Reg.No of students who are still unable to pass the subject.
(eg .112119 and 112107 still unable to pass their subject)

I was unable to write a query for this problem.

Can someone help me?

Thank you


texts:
As part of a SQL Server deployment, it was proposed to use Database mirroring as a redundancy option as well as allowing for Reporting and MI to be detached from the Operational DB.

The proposal is that we setup the database mirror using asynchronous mirroring and then at regular intervals create snapshots.

However one line in MSDN states:

  To create a database snapshot on a mirror database, the database must be in the synchronized mirroring state.

How does this work in practice? And what does this mean exactly?

How often will the database be in a non-synced state? (In a relatively moderate volume workload Database (~50 users).)

Will a create snapshot script wait until the DB is synchronized before executing, or do we need to prompt the DB to fully synchronize before the snapshot is taken (as part of the create snapshot script)?


texts: Taking the difference in timestamps between successive entries

texts:
This is my first foray into DBA.stackexchange, so I'll say up front: this is a query optimization question.  If this is not an appropriate question for DBA, let me know and I'll skulk away quietly...

I'm trying to get the difference between timestamps using a self join.  Despite indexing, it seems to be taking a very long time.  I'd welcome suggestions for optimizations.

The setup (with sample data)

CREATE TABLE reading(reading_id INT, device_id INT, time_recorded TIMESTAMP);

CREATE INDEX my_idx on reading(device_id, time_recorded);

INSERT INTO reading(reading_id,device_id,time_recorded) VALUES
(42271930,1154,'2014-11-18 13:12:38'),
(42271931,1154,'2014-11-18 13:12:40'),
(42271932,1154,'2014-11-18 13:12:52'),
(42271933,1154,'2014-11-18 13:13:21'),
(42271934,1154,'2014-11-18 13:13:22'),
(42271935,1154,'2014-11-18 13:13:23'),
... snip ...

The query

    select t1.time_recorded as tr1,
           min(t2.time_recorded) as tr2,
           timestampdiff(second, t1.time_recorded, min(t2.time_recorded)) as td
      from reading as t1
inner join reading as t2
        on t2.time_recorded > t1.time_recorded
     where t1.device_id = 1154
       and t2.device_id = 1154
  group by t1.time_recorded;

The explain

|ID |SELECT_TYPE|TABLE|TYPE|POSSIBLE_KEYS|KEY   |KEY_LEN |REF  |ROWS |FILTERED |EXTRA              |
|1  |SIMPLE     |t1   |ref |my_idx       |my_idx|5       |const|14   |100 |Using where; Using index|
|1  |SIMPLE     |t2   |ref |my_idx       |my_idx|5       |const|14   |100 |Using where; Using index|

An excerpt of the results

|                             TR1 |                             TR2 | TD |
|---------------------------------|---------------------------------|----|
| November, 18 2014 13:12:38+0000 | November, 18 2014 13:12:40+0000 |  2 |
| November, 18 2014 13:12:40+0000 | November, 18 2014 13:12:52+0000 | 12 |
| November, 18 2014 13:12:52+0000 | November, 18 2014 13:13:21+0000 | 29 |
| November, 18 2014 13:13:21+0000 | November, 18 2014 13:13:22+0000 |  1 |
| November, 18 2014 13:13:22+0000 | November, 18 2014 13:13:23+0000 |  1 |
| snip...

The question

The results are correct.  And on the small dataset given here, it runs in 170 ms (on the sqlfiddle site).  But on a table with ~20K rows, the query takes over seven minutes to run (on an Amazon RDS / AWS instance).

My feeble understanding of EXPLAIN suggests that the query is using the index properly.  Is this just an inherently slow query, or is there a faster way?

Update 1: A faster way

The following query is faster: about 2.5 minutes vs 7.  It assumes that timestamps are in increasing order -- while this is generally true it's not guaranteed, so I'm not particularly keen on this as a solution:

select t1.time_recorded as tr1,
       timestampdiff(second,
                     t1.time_recorded,
                     (select t2.time_recorded
                        from reading as t2
                       where t2.time_recorded > t1.time_recorded
                         and t2.device_id = 1154
                       limit 1)) as t2
  from reading as t1
 where t1.device_id = 1154;


texts:
I received a task from one of my colleagues to check why he always receives high CPU load alerts.
I don't have too much experience with databases and I need some clarifications.
I created a script to capture the first 5 threads that are using most of the CPU when the alerts are triggered.
The order is almost always the same, first three are db2agents and the other 2 are db2pfchr.
From my knowledge, I found nothing wrong with the SQL statements so I checked for table scans and IREF.

What I noticed immediately was the difference between rows read and rows selected which looks like this for example:

Rows deleted                               = 0
Rows inserted                              = 0
Rows updated                               = 1329
Rows selected                              = 714
Rows read                                  = 3903430587518
Rows written                               = 3471

First 3 biggest table scan values are 26586532, 15538513 and 942177.
The first table has one index that was never used, the second table has 1 index that was used around 70k times and the third table has no index.

So, my obvious guess is that indexes need to be added in order to avoid table scans

Since I never added an index in my life and must read more about it, I needed to be sure about my assumptions and maybe there something else to check?
Also, all values in the queries are "?"... hidden?
Like UPDATE X  SET VALUE_INT = ?,  VALUE_STRING = ?,  Y = CURRENT TIMESTAMP  WHERE AB = ?

This is an OLTP DB, and it's about DB2 V10.5 on RHEL
IREF - Index Read Efficiency = ROWS READ/ROWS RETURNED


texts: Bypassing version store for changing column datatype

texts:
Database is Always On High Availability synchronised database on SQL Server 2019 with READ_COMMITTED_SNAPSHOT on and we are updating datatype from varchar to nvarchar (with "alter table" command) on a column on a large (1 TB) table.
It blew up our tempdb due to version store.
I understand why this happened, but really just looking at (maybe creative) ways to stop this happening (ideally to bypass the version store for this command).
I'm considering recommending ADR to at least mean that at least issue is isolated to this particular database, but would love to hear any other ideas.


texts:
I have installed postgres 11 earlier on my MacBook Pro. Suddenly it stopped working. Usually I start my postgres server by following command

pg_ctl -D /usr/local/var/postgres start

and it returning the following error

dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicui18n.63.dylib
  Referenced from: /usr/local/Cellar/postgresql/11.1_1/bin/postgres
  Reason: image not found
no data was returned by command ""/usr/local/Cellar/postgresql/11.1_1/bin/postgres" -V"
The program "postgres" is needed by pg_ctl but was not found in the
same directory as "/usr/local/Cellar/postgresql/11.1_1/bin/pg_ctl".
Check your installation.

How can I solve this?


texts: Why does backup cause two media sets in msdb.dbo.backupmediaset?

texts:
After I take a SQL Server database backup using the script below, to a single media set, why do I see two Media SET IDs in table msdb.dbo.backupmediaset?

--take the full back up
BACKUP DATABASE Maintenance
TO  DISK = 'F:\MaintenanceTest\Maintenance1.bak',
    DISK = 'F:\MaintenanceTest\Maintenance2.bak'
WITH FORMAT
, INIT
, MEDIANAME = 'Maintenance'
, NAME = 'Chapter15-Full Database Backup'
, COMPRESSION ;
GO

--query media set but get two media set IDs with the same name
select * from msdb.dbo.backupmediaset where name='Maintenance'


texts:
I have a very small table with 12 rows in it that can be created with the following statement:

CREATE TABLE dbo.SmallTable(ScoreMonth tinyint NOT NULL PRIMARY KEY,
                            ScoreGoal float NOT NULL
                           );

I have another table with ≈100M rows in it that can be created with the following statments:

CREATE TABLE dbo.SlowCrossApply(RecordKey nvarchar(12) NOT NULL,
                                Score1 decimal(3, 2) NOT NULL,
                                Score2 decimal(3, 2) NOT NULL,
                                Score3 decimal(3, 2) NOT NULL,
                                Score4 decimal(3, 2) NOT NULL,
                                Score5 decimal(3, 2) NOT NULL,
                                Score6 decimal(3, 2) NOT NULL,
                                FromToday bit NOT NULL
                               );

ALTER TABLE dbo.SlowCrossApply ADD CONSTRAINT i01PK PRIMARY KEY CLUSTERED(RecordKey ASC)
    WITH(FILLFACTOR = 90, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
         DATA_COMPRESSION = PAGE
        );

CREATE NONCLUSTERED INDEX i02TodayRecords ON dbo.SlowCrossApply(FromToday)
    INCLUDE (Score1, Score2, Score3, Score4, Score5, Score6)
    WHERE FromToday = 1
    WITH(FILLFACTOR = 100, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
         DATA_COMPRESSION = PAGE
        );

i02TodayRecords has ≈1M rows in it. When I run the following query—I struggled formatting it to both look clean and prevent a horizontal scrollbar—it takes over 5 minutes to finish:

SELECT b.RecordKey,
       COALESCE(NULLIF(ROUND(((0.95 * (ROW_NUMBER() OVER(PARTITION BY a.Prefix
                                                         ORDER BY b.Score6 ASC
                                                        ) - 1
                                      )
                              )
                              / COALESCE(NULLIF(COUNT(*) OVER(PARTITION BY a.Prefix) - 1, 0
                                               ), 1
                                        )
                             ) + 0.005, 2
                            ), 0.96
                      ), 0.95
               ) AS NewScore
FROM (SELECT LEFT(s.RecordKey, 2) AS Prefix,
             CAST(ROUND(sm.ScoreGoal * COUNT(*), 0) AS int) AS Quant
      FROM dbo.SlowCrossApply AS s
      CROSS JOIN dbo.SmallTable AS sm
      WHERE s.FromToday = 1 AND sm.ScoreMonth = MONTH(GETDATE())
      GROUP BY LEFT(s.RecordKey, 2), sm.ScoreGoal
     ) AS a
CROSS APPLY (SELECT TOP(a.Quant) s2.RecordKey, s2.Score6
             FROM dbo.SlowCrossApply AS s2
             WHERE s2.FromToday = 1 AND s2.Score6 > 0 AND LEFT(s2.RecordKey, 2) = a.Prefix
             ORDER BY s2.Score6 DESC
            ) AS b;

The outer subquery returns only 10 rows; and if I supply a hint to use i02TodayRecords or put the results of the outer subquery in a table variable, it takes less than 1 second. The final result returns just over 8000 rows.

The execution plan shows that 64% of the cost is due to an eager index spool on the clustered index in the Cross Apply portion.

I know the index hint works (at least for now), but I'm hoping to avoid using one. Ideally, I wouldn't go the table variable route either. Is there something I can do to get the query optimizer to "know" to utilize i02TodayRecords? I realize there is a lot more information that is probably important, and I'll do my best to supply said information if requested.

Some potentially useful information: the indexes have less than 1% fragmentation. The statistics for both indexes have been updated via a FULLSCAN, and the database is set to have simple parameterization and parameter sniffing—unfortunately, I can't changes those settings. In regards to the latter, the query optimizer did not replace any values with parameters unlike other simple queries I have run where I was forced to use a hint to utilize a particular filtered index.