Reporting Services TempDB keeps going into restoring mode I am looking into an issue with SQL Server 2008 Reporting Services. It has been working fine until yesterday morning. Now I notice that ReportServerTempDB keeps on going into Restoring... mode. I restored it to online mode with RESTORE DATABASE [ReportServerQ4TempDB] WITH RECOVERY.

The reports work fine after this and then after 10-15 minutes, then ReportServerTempDB goes back into Restoring mode. Report execution gives the following error:

  An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError)
  For more information about this error navigate to the report server on the local server machine, or enable remote errors...

Any suggestions why it is doing that.

I have checked the following

Memory - 50% available
CPU - only 10% utilized
Storage - 30% space remaining

There are multiple instance of SSRS running and SQL instance hosting the database for SSRS on the same server.

I have checked that AutoClose is not enabled for this database, and I have discovered that the database goes into Restoring... mode every hour, when our log backup job runs.

Why are you running a log backup job on [ReportServerQA5TempDB]? It should be in simple mode.

From MSDN:

  ReportServerTempdb is created with the primary report server database and is used to store temporary data, session information, and cached reports.

and

  Use the simple recovery model to backup the reportservertempdb database.

Now the real question becomes, "What are you using for your backups?" Check to make sure that it's not changing the recovery model and that ReportServerTempDB is still in SIMPLE recovery model.

    SELECT name, recovery_model_desc
        FROM sys.databases;

I wonder if your solution is trying to switch back and forth between recovery models and is failing somewhere along the way. I have a vague recollection of this having happened to me in the past.

Try changing the recovery model to Simple and removing it from your hourly log backup job.

ALTER DATABASE ReportServerQA5TempDB SET RECOVERY SIMPLE;
GO


SQL Server 2019 Database stuck In Recovery state after restart SQL Server was restarted by mistake, when it came online, database came back in "In Recovery" mode.
Check from error it says "Recovery of database 'DB1' (5) is 8% complete (approximately 27146 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
It says it will 8 hours to bring this 2tb database online.
Any quick way to fix this, as we didnt had anything open in LOG files, so even if they r ignored, its no impact.
We want to bring this database ONLINE quickly

There's not really anything you can do. Your database is going through crash recovery, and it needs to process the transaction log. I've written about this exact scenario more in depth here.
If SQL Server hadn't completed a CHECKPOINT recently, there would be database changes that were committed, but had only been made in memory & in the transaction log, but not in the data files. These changes need to be redone from the log file so that the changes aren't lost. This is what your server is doing right now. I describe the phases in more detail in the article I linked to earlier.
If your transaction log is unnecessarily very large, this can cause SQL Server to do checkpoints less frequently. A too large log file, or too many VLFs can both result in a longer recovery time after a crash.
For a planned restart you can minimize/eliminate the delay by performing a manual CHECKPOINT; before shutting down and by allowing the SQL Server service to gracefully stop. Rebooting the server without first stopping the service can result in a forced termination of the sqlservr.exe process, leading to recovery like you're seeing on startup.
To get your database back online, just be patient. Don't restart, don't try to get rid of your log file. Just be patient. That estimated recovery time is notoriously incorrect, so hope that it's online sooner, rather than later.


Returning rows in PostgreSQL with a table called "user" I have a table called user

I have some SQL that is select User.* from User

This fails because User is a reserved word however, I was under the impression that Postgresql didn't care about the case of the tables when executing the SQL so I assume there is a conflict because of the reserved word.

In the database the user table is lowercase so if I run this

select * from user

I get a result, although bizarrely I only get one column returned even though I said show all columns.

If I run select * from "user" I get all columns and also SELECT "user".* FROM "user" returns all columns.

Can someone explain what's going on please?

Thanks

(Using PostgreSQL 9.3)

user is a pseudo-function keyword

user is an alias for current_user, a built-in pseudo-function. It doesn't have the () of most zero-argument functions because that's how the SQL standards committee declared that it would be for current_user. I don't know why user follows that too, since I don't think the spec requires it.

Functions can be used in FROM

Anyway, it's legal in PostgreSQL to specify a function in from, e.g.

regress=> SELECT * from clock_timestamp();
        clock_timestamp
-------------------------------
 2014-09-03 17:00:59.191019+08
(1 row)

because a function that returns a single value can be used in a set-returning context too.

So when you run:

select * from user;

you're really doing:

select current_user;

"quoting" escapes keywords

The reason it "works" if you write "user" instead is that quoted identifiers are always user-identifiers, not keywords. user is a keyword; "user" is a user-defined identifier that's a legal table name.

Confusing, no?

Usually "tablename" and tablename are the same (quoted-lower-case and unquoted-lower-case), unless tablename is a keyword.

This might make more sense if we pick a keyword that isn't a pseudo-function, so it's not legal syntax. Say:

regress=> CREATE TABLE "where" (id integer);
CREATE TABLE

regress=> CREATE TABLE where (id integer);
ERROR:  syntax error at or near "where"
LINE 1: CREATE TABLE where (id integer);
                     ^

Quoting makes identifiers case-sensitive

Normally, PostgreSQL case-folds all identifiers to lower case. This is required by the SQL spec, though it actually requires upper case. So when you:

CREATE TABLE MyTable (id integer);

PostgreSQL actually creates a table named mytable. However, this case-folding is disabled if you "double quote" identifiers - again, per the SQL standard.

So the reason that:

select * from "User";

fails is that PostgreSQL, per the SQL standard, treats quoted identifiers as case-sensitive. So "User", "USER" and "user" are all different things.

How to avoid these problems?

Don't name your tables after keywords like type names, function names, etc. It's often legal, but it's rarely a good idea.

If you're going to use keyword names, you should always "double quote" them wherever they appear - and in fact make a habit of simply doing that for all your identifiers, all the time, and always using consistent case.

There's a keyword list here.


Copying a table in SQL Server database to Oracle database I am new to database management. I need to copy a particular table "Attendance" in SQL Server database to Oracle database. There is no connection between the two databases. Also, "Attendance" is updated in real-time in the original SQL database.

I have two options:

To pull the data from SQL Server -> Store it -> Push to oracle.
Connect the two databases so as the updates are synchronised in real-time.

Unfortunately, I don't know how to go about this. I have researched on this since a week but haven't found exactly what needs to be done.

this can be done if you are using SQL Server Management Studio. The method is as follows:-

Goto the source schema on SQL Server

Right click > Export data

Select source as current schema

Select destination as "Oracle OLE provider"

Select properties, then add the service name into the first box, then username and
password, be sure to click "remember password"

Enter query to get desired results to be migrated

Enter table name, then click the "Edit" button

Alter mappings, change nvarchars to varchar2, and INTEGER to NUMBER

Run

Repeat process for remaining tables, save as jobs if you need to do this again in the future


Management Data Warehouse SQL Server data collection reports I configured MDW on an SQL Server instance but when I try to access the reports from the MDW DB, the reports show information until a couple of days ago and I am getting 'collection set is not running. The data displayed here is from a previous run' as per below:

Why does this happen?

You have to enable Data Collection

  Using SQL Server Management Studio

  In Object Explorer, expand the Management node.
  Right-click Data Collection, and then click Enable Data Collection.

or Using Transact-SQL

USE msdb;
GO
EXEC dbo.sp_syscollector_enable_collector;

FYI, MDW works using SQL Agent jobs, so the Agent has to be started and collector's jobs have to be enabled.


Why do VSS Snapshots cause Availability Groups to fail over? Why do VSS snapshots cause Availability Groups to fail over?
Back in 2018, Ken Kellman and Erik Darling wrote that by freezing I/O on databases, VSS snapshots can cause Availability Groups to fail over.  https://www.brentozar.com/archive/2018/01/perils-vss-snaps/
According to Erik, "the redo threads go buck wild on the other end."
OK.  I've got buck wild redo threads.  What does that mean?
I'm seeing one of my Availability Groups fail over on nearly a daily basis.  This usually happens right around the time our Avamar server backups run.  Is there anything that can, or should, be done to tame these buck wild redo threads of mine, and prevent this daily failover?

The failover policies are configurable - so you can adjust the thresholds
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-flexible-automatic-failover-policy?view=sql-server-ver15
of course to do this you need to know which policy triggers the failover in the first place
https://techcommunity.microsoft.com/t5/sql-server-support/diagnose-unexpected-failover-or-availability-group-in-resolving/ba-p/318474


How to check for record which has no entry in another table with some specific value? For example, if we have two tables: Table1 and table 2

Table1 - main table (with unique entry for all records)
table2 - is a history table(where is containg many entries for same record on time stampe basis) (also contains some specific field type)

I need to retreive data like, all data from table1 where there is no entry for the record with type = 'Some vaue'

I typically write this as NOT EXISTS query because this aligns with the wording of the problem ("find everything in table1 where no corresponding row exists in table2")

select t1.*
from table1 t1
where not exists (select *
                  from table2 t2
                  where t1.id = t2.table1_id
                    and type = 'Some Value');

Depending on your DBMS, mcNets' solutions might be faster - that depends a lot on the query optimizer, in Oracle for example it won't make any difference.


Using pg_dump on a view always produces an empty file I want to export a subset of a tables rows using pg_dump (using powershell on windows). The simplest way seems to be to create a view with an appropriate WHERE clause. However, when I run pg_dump on the view the resulting output files never has data in it (it has several rows of SET statements, but no actual data.)
I can successfully run pg_dump on tables without any problems, and creating an appropriate view isn't a problem, I can select from the view using the same account that I'm running pg_dump from so I don't think permissions are an issue. pg_dump isn't producing any errors.
I've been using this command to create the view...
CREATE OR REPLACE VIEW viewA
AS
(
  select * from tableA
  WHERE blah blah blah....
);

And this command to do the dump
$env:PGPASSWORD='own'
.\pg_dump.exe -U myuser -d mydatabase -t viewA -f mydumpfile

This works (i.e. it runs without error) but mydumpfile never contains data.
If I do something very similar, but creating a table instead....
CREATE TABLE tabB
AS
(
  select * from tableA
  WHERE blah blah blah....
);

Followed by an almost identical pg_dump on the table...
$env:PGPASSWORD='own'
.\pg_dump.exe -U myuser -d mydatabase -t tabB-f mydumpfile

Then I get a dump file with the correct data.
Is there any way of using pg_dump against a view. The postgres documentation suggests it should be possible

Is there any way of using pg_dump against a view. The postgres
documentation suggests it should be possible

This is not an answer to your question per-se, but the PostgreSQL documentation is certainly very ambiguous there, so I submitted a patch to clarify what will be dumped (in particular, only the definitions of views), which has been accepted and will be reflected in the current documentation soon.
As mentioned in the comments, the only way to extract the data generated by a view is via psql's \copy command, or possibly the COPY command itself.


Slow MySql server - even on a super fast server I have just upgraded my 12 year old server to a really fast new one.  It is a dedicated server, Dell R620 1 x Intel E5-2620 (2.0GHz, 6C) CPU 32GB RAM - 4 x 250GB SSD RAID 1, in short vastly faster in every way to my old server.  However I still have a rather slow response whenever I query the MySql database.

Loading up any static is lightning fast.  Also when I view my other website (this server hosts two websites) that makes use of Mongodb, it is lighting fast.  Currently I'm only using +- 2.5Gb or ram out of the 32Gb.  Is there some setting for MySql to allow it to use more resources in order to speed up?  Alternatively is there anything else I can do (just dealing with settings or MySql queries) to speed up this slow MySql server?

The MySql version is: 5.1.73.  I'm running CentOs

I think you should change your server parameter to optimize server performance, you can go through this Percona Wizard https://tools.percona.com/wizard

Also you can refer a blog post http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ this shows what parameter to consider to get optimize server performance.

I personally recommend that you should upgrade your MySQL server to latest v5.6 GA and start using it as it has lot of optimization and InnoDB improvements.


Is REPEATABLE READ the right choice for validation before modifying data? In my database I have a Task table. This table has a number of fields that are foreign keys to other tables. There are also a few link tables, that have Task on one end.

When a new task is created (or an old task is updated) I need to make sure that these foreign keys exists, and return a meaningful error if they do not. This is important because the call originates from a web api, so consumers really need to know why their call did not work, so a detailed error message (without giving out too much) is crucial.

Since there are a dozen of foreign keys (and also some other conditions, like current user application account being active) that make task correct, instead of trying to insert and failing I'm trying to validate these before I insert.

Of course when I do this I need to make sure that when I actually insert the record these validations still hold true.

So let's assume a task references a group. From the web api perspective group is a reference data, and it returns a list of valid group keys in a prior call. It receives one of them back when it creates or updates a task as a GroupId field on the Task object.

To make sure that this (and other) foreign keys are present in the database I open a REPEATABLE READ transaction and check them one by one for existence before I attempt to insert the task. I then compile a list of those that are absent (if any) and I roll back the transaction and return these back so that the API can form a meaningful error message.

The question is if REPEATABLE READ isolation level appropriate in this case. Paul White mentioned that often it is not .

To make sure that this (and other) foreign keys are present in the database I open a REPEATABLE READ transaction and check them one by one for existence before I attempt to insert the task. I then compile a list of those that are absent (if any) and I roll back the transaction and return these back so that the API can form a meaningful error message.

REPEATABLE READ is probably the right isolation level to use in this case, assuming the data you are checking does not change very often. This isolation level guarantees that rows actually read earlier in the transaction will continue to exist, unchanged, until the transaction completes by taking and holding shared locks.

A transaction running at this isolation level may miss rows that existed at the start of the transaction, but which moved during the transaction in such a way that they moved from in front to behind the seek or scan trying to locate them. It might also encounter new rows on a second run of the same query that did not exist at the beginning (phantoms).

So, in rare cases, your transaction might fail to approve an insert that would succeed if tried. It would not result in a validated insert subsequently failing, which is probably your primary concern.

If you need a more complete guarantees of isolation, consider SERIALIZABLE - but ensure the proper indexes exist to support minimal key-range locks.

Using SNAPSHOT isolation (as I mentioned on your previous question) would provide a consistent view of the data (as it appeared at the time the transaction started), but you might encounter an update conflict when trying to insert the 'validated' entry, if any concurrent modifications result in the foreign key checks failing. This would not provide the level of detail you need for a meaningful error message, so SNAPSHOT is not the right choice here.

For more information see:

The Repeatable Read Isolation Level
The Serializable Isolation Level
The ACID properties of statements and transactions


Monitor executed SQL statements from external application I have an external application connecting to a SQL Server 2008 Express database.

Is it possible to monitor (view) the SQL statements that are generated by the external application at all? I am trying to work out the tables (and the relevant joins) used in the queries.

Thanks

The best i can think of will be to use SP_whoisactive from Adam Machanic which is very quick to generate a table to collect the data you need, then run the procedure in a loop with a wait statement to regularly collect the data you want over an interval.

You can gather the data as explained here to log the activities on the system with help of SP_whoisactive.

However , you may always go with live monitoring using SQL profiler and gather the required data accordingly.


using COPY command and wal_buffers since COPY command runs inside a single transaction, if its WAL data doesn't fit into wal_buffers? for example if we are bulk importing from CSV file.

As the documentation states:

  There are two commonly used internal WAL functions: LogInsert and
  LogFlush. LogInsert is used to place a new record into the WAL buffers
  in shared memory. If there is no space for the new record, LogInsert
  will have to write (move to kernel cache) a few filled WAL buffers.
  This is undesirable because LogInsert is used on every database low
  level modification (for example, row insertion) at a time when an
  exclusive lock is held on affected data pages, so the operation needs
  to be as fast as possible. What is worse, writing WAL buffers might
  also force the creation of a new log segment, which takes even more
  time. Normally, WAL buffers should be written and flushed by a
  LogFlush request, which is made, for the most part, at transaction
  commit time to ensure that transaction records are flushed to
  permanent storage. On systems with high log output, LogFlush requests
  might not occur often enough to prevent LogInsert from having to do
  writes. On such systems one should increase the number of WAL buffers
  by modifying the configuration parameter wal_buffers.

Which means for me that in such a situation the system will need more time to complete the COPY because of the writes.


PostgreSQL does not use all available IOPS on a single operation Using PostgreSQL 9.3 on Windows 7x64, CoreI7, SSD.

Assume a PostgreSQL table with 8 Int32 columns and following C# code to fill the table.

using (var connection = new NpgsqlConnection("User Id=postgres;Password=**;host=localhost;database=**;"))
using (var command = new NpgsqlCommand())
{
    connection.Open();
    command.Connection = connection;
    command.CommandText = "COPY \"MyTable\" FROM STDIN;";
    var copyInSerializer = new NpgsqlCopySerializer(connection);
    var copyIn = new NpgsqlCopyIn(command, connection, copyInSerializer.ToStream);
    copyIn.Start();

    for (int i = start; i < start + 1000000; i++)
    {
        copyInSerializer.AddInt32(i);
        //add 7 other int32 values
        copyInSerializer.EndRow();
    }
    copyInSerializer.Flush();
    copyIn.End();

    connection.Close();
}

On my test hardware this took ~10 seconds execution time and measured disk usage of ~20MB/s.
CPU and RAM do NOT hit limits.

The given SSD supports ~60MB/s writing random 4k blocks so I wondered why it would not execute faster. I created a second, identical table in the same database and disk and ran the above code sample in parallel (two tasks) on both tables.
Both took ~10 seconds, and Measured Disk usage was ~40MB/s.

Why does PostgreSQL not focus all available resources on the one and only operation happening?
Is there a way to force PostgreSQL to use the full available disk bandwidth?

ADO.NET (used in the code sample) obviously communicates via network - even if a local Postgres instance is used. Using STDIN therefore results in quite a bit overhead. Before Postgres is able to insert the bulk data, it needs to be:

serialized by C# client
transmitted via network connection (in my case to localhost)
deserialized by Postgres

This basically explains/justifies the described behaviour.

If the C# Client and Postgres are run on the same computer, you can speed up bulk inseration by writing a csv file on a local ssd drive (even better a ram drive) and execute the SQL statement 'Copy MyTable From x:\test.csv'.


How can we publish Aurora (Postgres compatible) logs to Cloudwatch? I'm looking for a way to publish Aurora (pg compatible) db logs to cloudwatch.
In Postgres RDS, there is an option when we "modify" the instance to enable publishing db and upgrade logs to cloudwatch, however I see no such option in Aurora.

I found some documentation on how this can be achieved for Aurora MySQL but nothing for Aurora Postgres.

Is this currently feasible? Appreciate any help or pointers on this.

Thanks.

Full Disclosure: I am the product manager for Aurora PostgreSQL.

We are working to add support for the feature you noticed that is already available for RDS for PostgreSQL and for Aurora MySQL, to automatically copy database logs to Cloudwatch. While I don’t have a date to communicate, we plan to launch this feature soon - stay tuned!

Kevin Jernigan
Principal Product Manager, Amazon Aurora with PostgreSQL Compatibility


Postgresql: How to take incremental backup with Barman I am new to Postgresql, I've found a tool named Barman for taking incremental backup of database. But the few tutorials aren't helping that much as they're not for naive users.

Can someone help explaining the steps simply how to backup data with Barman? That would be a great help ! I'm using Postgres 10.4 in Ubuntu 18.04.

With Barman, the only way to do it is to use rsync backup method and to enable links. See example :

backup_method = rsync
reuse_backup = link

If you need advanced configuration about incr and diff backups, maybe you should have a look at pgbackrest tool.


SQL row data mismatch for different users I am using MySQL (version 5.5.43) as my database.

I have a RoR micro-service that does an update column to a model Active Record class:

model.update_column(status: 0)

The next line is an api call to different micro-service that synchronously does a SQL query:

select * from model where status = 0;

The code runs without any errors but the latter query is not fetching the record that is being updated by the former. There is milliseconds of difference between the update and the read.

Both the services are connected to the same database as different users but same access.

I don't understand why this would happen? the update_column is obviously a commit to the db, then why would the select query not fetch the updated record. What am I missing here?

Some things must be put in a "transaction".

One example:

BEGIN;
SELECT ... FOR UPDATE;
do some non-sql processing
UPDATE ...  -- specifying the row that was `SELECTed`
COMMIT;

Without the FOR UPDATE, another connection might sneak in and update (or delete or ...) the row that you thought you had.


Hourly differential backups with append option With simple recovery mode, a nightly full backup, and hourly differential backups, if the hourly differential backup is set to "append" will the differential file continue to grow day after day after day, or does the nightly full backup cause the very next differential backup file to be truncated so that the append starts anew with a "clean slate"?

If you are backing up with NOINIT the backup files will continue to grow if they have the same name and destination.
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017

Media Set Options
These options operate on the media set as a whole.
{ NOINIT | INIT } Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT).

Personally I always use INIT and a distinct file name (and/or backup the file that is overwritten before it is overwritten)
If you are taking hourly differential backups, you might want to consider changing to full recovery and doing hourly t-log backups.  But I understand this is not always an option.
You don't have a good description of how you are protecting the full and differential backups, but you unless you have a good reason not to, you probably want each diff and full to use INIT and have date/time stamp in the file name.
If you must append your diffs for more than one day, you should probably validate you can restore.  I have not tried it, but I suspect that restoring from appended diff is going to be painful at best.
Note that per this answer you can not use EXPIREDATE (backup expiration date) to keep rolling backups in the same file.


Create multiple files for each file group for table partitioning? I am partitioning a table by date (month). The server has four drives. The partition scheme is defined as

CREATE PARTITION SCHEME ByDate AS PARTITION ByDate TO (
    [PRIMARY], [fg2], [fg3], [fg4],
    [PRIMARY], [fg2], [fg3], [fg4],
    [PRIMARY], [fg2], [fg3], [fg4],
    ......

Which of following option is better?

Create one file for each file group [PRIMARY], [fg2], [fg3], [fg4]. And put each file in different drive.
Create four files for each file group. And save each files in different drive.

One file per filegroup, one file per volume.
So a partition read/write goes to one volume: which is one reason to partition.

Otherwise, you are simply using all 4 drives all the time. So why bother with partitioning

I personally don't like multiple files per filegroup (except tempdb of course).

I assume "4 drives" means 4 discrete SAN LUNs or RAID arrays...


Improve performance of a Fact Table I have a Fact table CardTransactionFact

Table Structure

TABLE [dbo].[CardTransactionFact]
    [CardTransactionID] [int] IDENTITY(1,1) NOT NULL,
    [TransactionTerminalID] [int] NOT NULL,
    [SourceAccountTypeID] [int] NULL,
    [DestinationAccountTypeID] [int] NULL,
    [RimNo] [varchar](15) NULL,
    [CaptureCodeID] [int] NOT NULL,
    [RoutingCodeID] [int] NOT NULL,
    [ProcessingCodeID] [int] NOT NULL,
    [ActionCodeID] [int] NOT NULL,
    [NetworkCodeID] [int] NOT NULL,
    [ProductCodeID] [int] NOT NULL,
    [AcquiringCountryCodeID] [int] NOT NULL,
    [IssuingCountryCodeID] [int] NOT NULL,
    [TransactionCurrencyCodeID] [int] NOT NULL,
    [AmountBD] [decimal](18, 3) NOT NULL,
    [LocalCurrencyCodeID] [int] NOT NULL,
    [CardIssuerBank] [int] NOT NULL,
    [CardTypeID] [int] NOT NULL,
    [SuspectTransactionFlag] [char](1) NOT NULL,
    [ReversalTransactionFlag] [char](1) NOT NULL,
    [LocalTransactionDateKey] [int] NOT NULL,
    [LocalTransactionHourKey] [int] NOT NULL,
    [BBKRole] [char](1) NOT NULL,
    [AmountRangeKey] [int] NULL,
    [CustomerKey] [int] NULL

Size: 11GB
No of Rows : 56,959,828

It has become very difficult to access this table now, a simple Select count(*) from CardTransactionFact takes hours to execute.

most of the columns in the table are just integers that's why i didn't do any indexing.

What do you think i should do to improve this table, and increase the speed of query's to this table

If indexing which columns should i index and why
Is it good idea to Partition the table
Any Other suggestions

Lots of stuff wrong here, thankfully lots that can be fixed.

Problems:

You have a heap. Good odds this is heavily fragmented and pages are spread throughout the 82GB data file. See sys.dm_db_index_physical_stats for guidance on checking fragmentation.
You only have 6GB of memory, if you're lucky maybe 4GB is available to the buffer pool.
Reading between the lines, you are using dog slow SATA spinner drives.
A scan of the table will require 11GB of random IO across that dog slow drive and churn the buffer pool completely 3 times over.

Fixes:

Create a clustered index on the table. CardTransactionId looks like the only sensible choice currently.
You desperately need memory. 128GB would be reasonable for an 82GB data warehouse.
You have woefully inadequate IO. SSDs would the cheapest fastest fix.

11GB does not fit into 6GB, it really is that simple. A very rough estimate suggests the table will occupy ~1.5million 8KB pages which given 100 IOPS would take approximately 4 hours to read from disk (assuming worst case, 100% random read, no read-ahead etc).


Why does backup cause two media sets in msdb.dbo.backupmediaset? 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'

There is nothing in SQL Server protects you to reuse 'MEDIANAME'.
Taking your query and reusing for a a different database I will give you an example and that is the same reason you are seeing 2 records with same name.

BACKUP DATABASE [WideWorldImporters]
TO  DISK = 'C:\MaintenanceTest\Maintenance1.bak',
    DISK = 'C:\MaintenanceTest\Maintenance2.bak'
WITH FORMAT
, INIT
, MEDIANAME = 'Maintenance'
, NAME = 'Chapter15-Full Database Backup'
, COMPRESSION ;
GO

Once backup is done :

 SELECT
    media_set_id,
    media_uuid,
    media_family_count,
    name,
    description
 FROM [msdb].[dbo].[backupmediaset]

SELECT bm.media_family_count, bm.[name],bmf.physical_device_name
  FROM [msdb].[dbo].[backupmediafamily] AS bmf
  JOIN [msdb].[dbo].[backupmediaset] AS bm
  ON bmf.media_set_id=bm.media_set_id

Now doing the same again but changing the backup folder name to 'MaintenanceTest1' (different physical location).

BACKUP DATABASE [WideWorldImporters]
TO  DISK = 'C:\MaintenanceTest1\Maintenance1.bak',
    DISK = 'C:\MaintenanceTest1\Maintenance2.bak'
WITH FORMAT
, INIT
, MEDIANAME = 'Maintenance'
, NAME = 'Chapter15-Full Database Backup'
, COMPRESSION ;
GO

For backupmediaset:

To see physical location:


Running job in endless loop I have some C# CLR to parse a directory looking for files on the filesystem.  I want this to run in an endless loop (preferably managed via SQL jobs).  Hoping someone can suggest the best and most efficient way to do this?

Thanks in advance,

Ben.

Assuming that:
- You have the looping and sleep code in the CLR code
- You know how to call said code from SQL agent (a stored procedure wrapper if nothing else)
- Your process is ok with the occasional sub-minute downtime
- Your process will not lose any data if it fails part way through (i.e. the next run will process the data the failed run was trying to process)

then I would propose a SQL agent job that is sceduled for every minute, and the job step itself have say, 10 retries set (advanced page) so that it will usually restart itself immediately if it fails.
Why just 10? Because if it starts consistently failing, you will want to get notification quickly (via agent job failure notification settings). That will be an email a minute.
Of course, if it fails 10 times sporadically, you'll get a false alarm notification, but you can quickly see from the job history whether it's failing consistently or just sporadically.


How to implement on duplicate update for SQL Server? I'm trying insert/update data for table T1(int id,char name, float data), which has unique a index on id and name.

I'm using SQLBulkOperation(SQL_ADD) to add records to the table, and SQLBulkOperation(SQL_UPDATE_BY_BOOKMARK) to update them.

Now my use case is that I have to update records if they already exist and insert if not. For this, MySQL provides INSERT INTO ... ON DUPLICATE KEY UPDATE.

How to implement the same in SQL Server? Is there any other efficient way to do this?

I'm using libmsodbcsql-13.0.so.0.0 to connect to SQL Server.

The MERGE statement, introduced in SQL Server 2008, would be the usual recommendation here. It is a feature that seems quite underused (in my case because until recently I've had to support SQL Server 2005) but pretty powerful.

See:
https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql
https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

There are a number of issues to be careful of though with regard to concurrency (MERGE deadlocking itself(!)), performance, key consistency, and other bugs:
https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/73d2b0de-d6cc-4938-a303-b24d59c1be54/deadlock-on-simple-merge-statement?forum=sqlgetstarted
https://sqlperformance.com/2013/02/t-sql-queries/another-merge-bug

The alternative is to use a multi-step process (delete what is no longer need, update what is already present, insert what is missing) wrapped in a transaction with appropriate settings.


Is there a way to change the compaction throughput via startup options in Cassandra? We are currently running a Cassandra cluster in K8S containers as Stateful sets. We're considering exposing the config file but we would like to know if perhaps we can provide this value as a startup parameter.

There isn't a command-line option/system property to change the compaction throughput.

You could use nodetool setcompactionthroughput after Cassandra has started.


Restore to a point between two full SQL Server backups I have two SQL Server database backups from Server1. Backup1 was made on, say, 2013-01-01 and Backup2 was made on 2013-01-03. What I want to do is restore Server2 to the intermediate state on 2013-01-02.

What I am trying to do is the following:

Restore Backup2 onto Server2 so it has the latest data.
Backup the transaction log on Server2.
Restore Backup1 on Server2.
Restore the transaction log using STOPAT = "2013-01-01 23:59:59"

I'm pretty sure I understand why this doesn't work. In step 2, I want the transaction log to go back to a certain backup, but there's no way to tell SQL Server which one I want. It just picks the latest one.

If that's correct, then I guess my question is, how can I trick SQL Server into giving me the transaction log I need. I've been looking at the backupset table and wondering if I can modify that to get what I need.

Server2 is a development server and not in production. I can modify it as necessary, even into an unusable state if it will help me get this data correct.

If you are in full recovery mode but NOT backing up transaction logs, then any state between two full backups is permanently lost. Sorry. You should either change your mode to basic recovery (since you are not making any use of your transaction logs) or you should do at least one transaction log backup before full backups.


How can I drop relations in a PostgreSQL database without foreign key check? I need to create and delete tables in a test database while in development stage, but foreign keys verification bores me every time I need to erase its tables.

Is there any form to bypass this in Postgresql?

I've got it.

For a single table I can do:

DROP TABLE "tableName" CASCADE

For all tables inside a schema, for example "public", I can do:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

Source:

http://www.postgresql.org/docs/current/static/sql-droptable.html

https://stackoverflow.com/a/13823560/937110

Or, as suggested by a_horse_with_no_name:

DROP OWNED BY name [, ...] [ CASCADE | RESTRICT ]

source:
http://postgresql.org/docs/current/static/sql-drop-owned.html


Schema information within Trigger? Background : (Multi-tenant design;separate schema per customer, all table structures identical;Data-warehouse / non-OLTP system here; Postgres 9.2.x).

Reviewing patterns for the following problem:  I have a matview that needs to be updated if an underlying condition is altered (in this case, an inclusion BOOLEAN).

Was considering implementing a TRIGGER, on update, which would then either execute the matview creation UDF or possibly call 'NOTIFY' to be handled by some listening event, perhaps at a later point in time as the immediate matview update isn't necessarily required.

Workflow :

UPDATE made to some underlying table.
TRIGGER is executed.
(Obtain schema information here).
Either,

EXECUTE matview creation
NOTIFY command for queue / processing at a later time.

First question, am I on the right track here or are there other, more efficient / effective patterns I should review?

Given the nature of my multi-tenant design, is it possible to obtain the current schema from the underlying table from which the trigger event was called as I'll need that information to pass into my matview creation UDF.

If the effect isn't required until a later point in time, I would execute the payload at that later point in time and not via trigger. This way you can collect multiple updates on the underlying table and avoid redundant calls of the DDL script.

Depending on your circumstances, a timestamp column or simple boolean flag in the underlying table might suffice to mark rows that should trigger delayed events.

  is it possible to obtain the current schema from the underlying table ..

Yes. You can query practically any possible detail from the system catalogs or from the standardized (but much slower) information schema.  Furthermore, in a trigger procedure you have the TG_TABLE_SCHEMA variable set.

This related answer has more information and an example:
How do I list all columns for a specified table


Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (MsDtsSrvr) I have a clustered environment which has many instances in it. I have modified the xml file to allow several instances to use ssis. Everything works great when the instances are on the same node as the clustered ssis. However if they are on different nodes there seems to be an aunthentication problem and it loses my credentials. For some reason it tries to use Anonymous Logon instead. Here is the full error:

The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in Server 2005 Books Online.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (MsDtsSrvr)

I would appreciate anyones help on this problem.

Thanks,

This appears to be a authentication scheme problem.  If my memory serves correctly, the appearance of the ANONYMOUS LOGON is indicative of NTLM being used instead of Kerberos.  If your SQL Server service is running under a domain credential, you will need to ensure there is a Service Principle Name (SPN) present for SQL Server.  The syntax of the SetSPN command can be found here:  SetSPN  In short, you must marry a protocol with a TCP Port and the service account.  I believe the command will look something like this:

setspn -s MSSQLSvc/Server.Domain:TCPPort Domain\ServiceUser

To verify which authentication scheme your existing connections are using, run the following code:

SELECT
    dec.session_id,
    dec.auth_scheme
FROM sys.dm_exec_connections AS dec

If configured properly for Kerberos, you will see Kerberos in the auth_scheme column.  If not, you will see NTLM.

Finally, if your setup requires multi-hop authentication, you will need to configure each subsequent hop before the SQL Server to allow delegation.  Inside Active Directory, go to the properties page of the computer or user account that will need to pass through authentication to the SQL Server, go to the Delegation Tab, select "Trust this user for Delegation on Kerberos only", and then select the service that this account will be passing authentication to (search for your SQL Server service account).

Hope this helps,

Matt


What does this TRIGGER do? I have this trigger in DB2 that I need to convert to ORACLE, but I don't know how it works in  DB2 so i can translate it to a ORACLE one, I think it doesn't do anything.

CREATE TRIGGER ERRLOG_TRIGGER
  NO CASCADE BEFORE INSERT
  ON ERROR_LOG
  REFERENCING
    NEW AS NEWROW
  FOR EACH ROW
BEGIN ATOMIC
     SET NEWROW."ERRLOG_CREATEDON" = CURRENT TIMESTAMP;
END;

I see that date update, but what is it for?

The trigger does something, it sets the ERRLOG_CREATEDON column to the current time.

In Oracle this should roughly be:

create or replace trigger errlog_trigger
  before insert on error_log
  for each row
begin
  :new.errlog_createdon := current_timestamp;
end;
/


Customer just bought an Oracle license - am I allowed to install Oracle 11.2.0.4 without support contract? I understand that when I buy an Oracle license, I can choose which version I install, these days either 11gR2 or 12c. But which patch level am I eligible to install? Is it 11.2.0.1 which was released in 2009 and can be downloaded from their website or can I download 11.2.0.4 from My Oracle Support which would be the latest full installation?

Given I'm only eligible for 11.2.0.1 - what happens when I stumble over one of it's many bugs? How would I proceed to have it fixed by Oracle?

You can install the latest version available at the time of purchase, but no newer versions after that.

The "funny" part is, these versions (like 11.2.0.4) can be obtained from https://support.oracle.com/, but you can not download them, because you do not have a support contract. In this case, when you request the installation media, an Oracle representative goes on-site with the requested software (just an example I know of, there may be other ways).

This part took quite long the last time I was in such a situation with a customer.

Without a support contract, you can not download patches, and you can not open service requests. When you hit a bug, you have to live with it or apply an acceptable workaround.


SQL Server 2012 Patching Service Pack and CU Question I am tasked with updating a SQL Server 2012 from a version(11.0.5623.0) before SP3 all the way to SP3 CU2. Can I install both patches without a reboot until afterwards? All the documents I have found on Google indicate that a reboot is only needed if a file is locked.

You should only require a server reboot if something is locked, correct.

However, during the patching, the SQL service will be stopped, patched then restarted so you'll actually be bouncing the service itself twice.  Once for the SP, then again for the CU.


How to find cause of SQL-Server Full-Text Index Auto-Population Error? I have recently added a few full text indices to a database and AUTO populating is not working as I would have expected.

Sample Table Definition

CREATE TABLE [wrk].[WorkOrder] (
    [id]            UNIQUEIDENTIFIER  NOT NULL,
    ...
    [customer]      VARCHAR(250)      NOT NULL,
    ...
    CONSTRAINT [PK_WorkOrder] PRIMARY KEY ([id]),
);

Sample Index Definition

CREATE FULLTEXT INDEX ON [wrk].[WorkOrder] ([customer] LANGUAGE 1033)
KEY INDEX [PK_WorkOrder] ON [DefaultCatalog] WITH STOPLIST = OFF, CHANGE_TRACKING AUTO;

Sample Data

A6A1CEE3-4AA8-41EF-B681-E24B8B5DA03C    AltaGas Extraction and Transmission LP
F0B757FB-0A48-4AB0-9411-72AAF64E677B    Caltex Resources Ltd.
D4339B95-9AEA-4C79-ACBE-9194ED8FA1D5    Rock Energy Inc.

Other Details

SQL Server 2008 R2 Standard Edition
SQL Server & SQL Full-text Daemon Launcher running as  same privileged account.
Windows Server 2008 R2 Standard 64 bit VM
Indices are built using the DefaultCatalog
Indices are using English language.
Catalog is accent sensitive.

If I look at the SQLFT logs, I am seeing that new entries typically go through the following flow:

...
2015-06-05 18:18:05.99 Informational: Full-text Auto population initialized for table or indexed view '[wrk].[WorkOrder]' (table or indexed view ID '844074293', database ID '7'). Population sub-tasks: 1.
2015-06-05 18:18:09.02 Error '0x80070005' occurred during full-text index population for table or indexed view '[wrk].[WorkOrder]' (table or indexed view ID '844074293', database ID '7'), full-text key value 'F0B757FB-0A48-4AB0-9411-72AAF64E677B'. Attempt will be made to reindex it.
2015-06-05 18:18:09.02 Informational: Full-text Auto population completed for table or indexed view '[wrk].[WorkOrder]' (table or indexed view ID '844074293', database ID '7'). Number of documents processed: 1. Number of documents failed: 1. Number of documents that will be retried: 1.
2015-06-05 18:18:10.01 A full-text retry pass of Auto population started for table or indexed view '[wrk].[WorkOrder]'. Table or indexed view ID is '844074293'. Database ID is '7'.
...

If I search for the record associated with F0B757FB-0A48-4AB0-9411-72AAF64E677B no results are returned. If I manually rebuild the index, I will be able to search the data as expected.

How do I find out why the auto-population failed (i.e., reason for retry pass failure)? Do I have something configured incorrectly to have auto-population work as expected?

Using the Microsoft Error Code Lookup Tool shows Error 0x80070005 is "Access Denied".  This is the output from the tool:

# for hex 0x80070005 / decimal -2147024891 :
  COR_E_UNAUTHORIZEDACCESS                                      corerror.h
# MessageText:
# Access is denied.
  DIERR_OTHERAPPHASPRIO                                         dinput.h
  DIERR_READONLY                                                dinput.h
  DIERR_HANDLEEXISTS                                            dinput.h
  DSERR_ACCESSDENIED                                            dsound.h
  ecAccessDenied                                                ec.h
  ecPropSecurityViolation                                       ec.h
  MAPI_E_NO_ACCESS                                              mapicode.h
  STIERR_READONLY                                               stierr.h
  STIERR_NOTINITIALIZED                                         stierr.h
  E_ACCESSDENIED                                                winerror.h
# General access denied error
# 11 matches found for "0x80070005"

Ensure the service accounts used by SQL Server, SQL Server Agent, and SQL Full-text Filter Daemon Launcher Windows Services have the correct access.  Microsoft Docs has some great details about how these services interact.


Biological Sequences of UniProt in PostgreSQL What is the best way to store UniProt biological sequences in PostreSQL?

Data Details

We pull in 12 million sequences from UniProt - this number is likely to double every 3-10 month.
The length of a sequence can vary from 10 to 50 billion characters
Less than 1% of the sequences are longer than 10 thousand characters

Would it improve performance to store the longer sequences separately?

A sequence can be of either Protein or DNA alphabet

The DNA alphabet has 5 characters (A, T, C, G, or -).
The Protein alphabet will have around 30 characters.
We don't mind storing the sequences of the two different alphabets in different columns or even different tables. Would that help?

Data Access Details

To answer Jeremiah Peschka's comment:

Protein and DNA sequences would be accessed at different times
Would not need to search within the sequence (that's done outside of db)
Would ether access single rows at a time or pull out sets of rows by IDs. We would not need to scan rows. All sequences are referenced by other tables - several biologically and chronologically meaningful hierarchies exist in the database.

Backwards Compatibility

It would be nice to be able to continue to be able to apply the following hashing function (SEGUID - SEquence Globally Unique IDentifier) to the sequences.

CREATE OR REPLACE FUNCTION gfam.get_seguid(p_sequence character varying)
  RETURNS character varying AS
$BODY$
declare
  result varchar := null;
  x integer;
begin

  select encode(gfam.digest(p_sequence, 'sha1'), 'base64')
  into   result;

  x := length(result);
  if substring(result from x for 1) = '=' then

     result := substring( result from 1 for x-1 );

  end if;

  return result;

end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

Exploring the functions at PostBio it looks like they have a couple of ways of encoding. However, given that those extensions are optimized for searching, they make multiple references to simply using the text data type.

According to the documentation:

  Long strings are compressed by the system automatically, so the physical requirement on disk might be less. Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values. In any case, the longest possible character string that can be stored is about 1 GB.

Therefore, by putting the table into its own very large tablespace on dedicated hardware should be sufficient for your performance goals. If 1 GB is too small for your data, the int_interval from ProtBio should provide excellent performance:

  A sequence feature corresponds to a triplet (id, orient, ii) where id is a sequence identifier (possibly the primary key for a sequence table), orient is a boolean indicating if the feature is in the same or contrary orientation of the sequence, and ii is the int_interval representing the feature as a subsequence.

Encoding the sequence in sha1 looks to be a very painful way of making a GUID, considering the potential lengths of the sequence.

If the different sequences are unrelated, store them on different tablespaces on different disks for maximum performance.


Is there such thing as an Availability Group failover trigger? Is there such thing as a trigger for Availability Group failovers?

I want a certain action to happen when an AG fails over. Specifically, I want to turn on a database setting (turning on RCSI). I want to do this on a failover in order to minimize disruption to 24/7 workloads and scheduled maintenance windows are hard to come by.

I know that sp_procoption can be used to mark procedures as startup procedures.
This seems like it could work for failover clusters, but not for Availability Groups.

I did consider adding an alert (sp_add_alert) on message_id=26069 in order to respond to failover actions with a sql agent job. But this seems less direct and in practice it seems slow

No. Triggers are not at an instance or AG level. You'd need to base something on the failover event or better yet, as part of a SQL Server Agent job, just check to see if that replica is the primary and then do (or not do) something.


Why is SQL*Plus' desc table not showing not null constraints after using dbms_redefinition I have the following table that I want to redefine:

create table tq84_redefinition (
  id number primary key,
  ts1 timestamp not null,
  ts2 timestamp
);

Please note the not null constraint on the column ts1.

Using dbms_redefinition, I specifically use copy_constraints => true.

create table tq84_redefinition_int (
    id number,                            -- Note: no primary key to prevent «ORA-01408: such column list already indexed»
    ts1 date,
    ts2 date,
    duration_minutes as ((ts2 - ts1) * 24 * 60)
);

begin
  dbms_redefinition.start_redef_table(
    user, 'tq84_redefinition', 'tq84_redefinition_int',
   'id, '               ||
   'to_date(to_char(ts1, ''ddmmyyyyhh24miss''), ''ddmmyyyyhh24miss'') ts1, ' ||
   'to_date(to_char(ts2, ''ddmmyyyyhh24miss''), ''ddmmyyyyhh24miss'') ts2');
end;
/

-- set serveroutput on

declare
  cnt_errors binary_integer;
begin

  dbms_redefinition.copy_table_dependents(
    user, 'tq84_redefinition', 'tq84_redefinition_int',
    -------------------------------------------------------
    copy_indexes     => dbms_redefinition.cons_orig_params,
    copy_triggers    => true,
    copy_constraints => true,
    copy_privileges  => true,
    ignore_errors    => false,
    num_errors       => cnt_errors,
    copy_statistics  => true,
    copy_mvlog       => false);

  if cnt_errors > 0 then
     dbms_output.put_line('There were ' || cnt_errors || ' errors.');
  end if;

end;
/

exec dbms_redefinition.sync_interim_table(user, 'tq84_redefinition', 'tq84_redefinition_int');
exec dbms_redefinition.finish_redef_table(user, 'tq84_redefinition', 'tq84_redefinition_int');

Everything seems to work fine except that desc in SQL*Plus doesn't correctly display the not null constraint:

...> desc tq84_redefinition;
 Name                        Null?    Type
 --------------------------- -------- ---------------
 ID                                   NUMBER
 TS1                                  DATE
 TS2                                  DATE
 DURATION_MINUTES                     NUMBER

However, somewhere, the not null constraints is in place. I can see it by issuing a

select constraint_type, constraint_name, search_condition
  from user_constraints
 where table_name = 'TQ84_REDEFINITION';

Also, if I try to insert a record [insert into tq84_redefinition (id) values (999999)] I get the (correct) error message ORA-01400: cannot insert NULL into ("META"."TQ84_REDEFINITION"."TS1").

Edit: The version (v$version) is:

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Edit 2 @Munchi, the result of your suggested select statement

select
  column_name as "Name",
  nullable as "Null?",
  concat(concat(concat(data_type,'('),data_length),')') as "Type"
from
  user_tab_columns
where
  table_name = 'TQ84_REDEFINITION'

is

Name                           N Type
------------------------------ - --------------
ID                             Y NUMBER(22)
TS1                            Y DATE(7)
TS2                            Y DATE(7)
DURATION_MINUTES               Y NUMBER(22)

This is a known bug (description is not public unfortunately):

Bug:4396234 ET10.2OREDEF: NULLABLE COL OF *_TAB_COLUMNS TABLE NOT UPDATED AFTER ONLINE REDEF

The NOT NULL constraints are copied as NOVALIDATE, and you have to set them to VALIDATE state manually, e.g:

ALTER TABLE t84_redefenition ENABLE VALIDATE CONSTRAINT constraint_name;

The primary key issue is similar, but there is no bug reported for that. However I have found that disabling and reenabling it solves the problem.

ALTER TABLE t84_redefinition DISABLE PRIMARY KEY;
ALTER TABLE t84_redefinition ENABLE PRIMARY KEY;


PostgreSQL, get statements of a running transaction If I execute a transaction like:

begin;
update test set a=2;
select pg_sleep(9999);

I will get a RowExclusiveLock on test.

If I try to see which query is in that pid pg_sleep will be shown (because it is the currently running query).

Is there any option to get the query that actually create the lock?

Or maybe a query to get all the statements of the running transaction?

No, you can't do that.  Unless you have very verbose logging turned on (like log_statement=all) and can mine that data out of the server log file.

I think it would be a nice feature to optionally store some limited amount of data (like 4096 bytes, but configurable) of "prior transaction context" for each backend and then exposing it through pg_stat_actvity, and maybe logging it for log_lock_waits or deadlock errors.  But as far as I know there is no active work going on to add such a feature.


How to retrieve data of two columns from two tables? I have two Table MATCH_MASTER and TEAM_MASTER

MATCH_MASTER

|---------------------------|
|M_ID | TEAM1_ID | TEAM2_ID |
|---------------------------|
|  1  |     1    |     2    |

TEAM_MASTER

|T_ID | T_NAME |
|--------------|
|  1  |    A   |
|  2  |    B   |

I want to select M_ID, team1 name and team2 name.

My query result should look like this:

|------------------------|
|M_ID | t1_Name | t2Name |
|------------------------|
|  1  |    A    |   B    |

How can I achieve this?

Try following.

select T1.M_ID,
    (select T2.T_NAME from TEAM_MASTER as T2
    where T1.TEAM1_ID = T2.T_ID) as t1_Name,
    (select T2.T_NAME from TEAM_MASTER as T2
    where T1.TEAM2_ID = T2.T_ID) as t2Name
    from MATCH_MASTER as T1;


Simple Update Join much slower than it should be (MYSQL) This is a simple Update Join that updates only about 100 rows:
Update A INNER JOIN B using(id) SET A.active = 1 WHERE A.date > '2020'

This takes about 30 seconds to run, despite the fact that:

This query updates the same 100 rows and takes milliseconds to run:
Update A SET active = 1 WHERE date > '2020'

The join condition is fast, this query does the same join and takes less than a second
SELECT * FROM A INNER JOIN B using(id) WHERE A.date > '2020'

The field active not part of any index

Table A has an index on (id, date), and table B has an index on id.

I tried putting the where condition in the join (using on date > '2020') but it didn't help. I'm absolutely stumped why this takes so long. Any help is appreciated.

Maybe:
UPDATE A
    SET A.active = 1
    WHERE A.date >= '2020-01-01'
      AND ( EXISTS ( SELECT 1 FROM B WHERE B.id = A.id ) )

What is the datatype of date?  If it is DATE, you were excluding New Year's Day.
I'm assuming that id is the PRIMARY KEY if each table.  But, given that, I must ask why you have two tables with the same PK -- That is usually poor schema design.  (There are exceptions.)  Furthermore, INDEX(id, date) is probably useless.
Needed:  A:  INDEX(date).  Even so, that might be ignored, depending on the data distribution.  Please provide SHOW CREATE TABLE and EXPLAIN UPDATE ...


Count hours when a specific value is higher than a treshold i have this JSON that is contained in "data" column, the table also have: insert time, pk and some fk as reported below:
PK  INSERT_TIME                     DATA   SENSOR
1   2020-01-04 15:39:44.19364+00    {}       7

The table is populated with an external application that, about every minute, pushes new data, this is the JSON that i have typically in the table:
{
   "dev":11,
   "kw":43.0,
   "lux":200.2,
   "temp":20.3,
}

My goal is to build a query that, having all rows in the table is able to count how many hours in total and hours per day the device was up and running and producing kW, so, when the kW value is greater than zero (those measurements comes from a solar inverter system)
This is the query that i've built, actually i'm able to select only the rows that have the kw value greater than 1 and group them by month, but i'm not able to figure out how to calculate the production hours:
SELECT mt.insert_time,
       date_part('month', mt.insert_time) as mon
FROM mytable mt
WHERE sensor = 7
  AND date_part('month', mt.insert_time) = 5
  AND (mt.data->'kw')::int > 1

What i'm expecting:
I need to get the exact amount of working hour of my solar panel production system, i'm assuming that the solar panel "works" only when the kW value is greater than zero.
I want to see, day by day, and month by month the working hours of my panel.
Here guys i'm reporting an example of my data rows
+-----------------------------------------------------------------------------------------------------+
|                                     pk,insert_time,data,sensor                                      |
+-----------------------------------------------------------------------------------------------------+
| 3003711,2020-10-03 09:55:54.271738+00,"{""dev"": 12, ""kw"": 0, ""temp"": 20.8, ""lux"": 2.0}",12   |
| 3003692,2020-10-03 09:54:54.289131+00,"{""dev"": 12, ""kw"": 0, ""temp"": 20.6, ""lux"": 2.0}",12   |
| 3003681,2020-10-03 09:53:54.287502+00,"{""dev"": 12, ""kw"": 9.0, ""temp"": 20.5, ""lux"": 2.0}",12 |
| 3003670,2020-10-03 09:52:54.284262+00,"{""dev"": 12, ""kw"": 9.0, ""temp"": 20.5, ""lux"": 2.0}",12 |
| 3003659,2020-10-03 09:51:56.382746+00,"{""dev"": 12, ""kw"": 12, ""temp"": 20.5, ""lux"": 2.0}",12  |
| 3003648,2020-10-03 09:50:54.279558+00,"{""dev"": 12, ""kw"": 9.0, ""temp"": 20.5, ""lux"": 2.0}",12 |
| 3003637,2020-10-03 09:49:56.377988+00,"{""dev"": 12, ""kw"": 9.0, ""temp"": 20.5, ""lux"": 2.0}",12 |
+-----------------------------------------------------------------------------------------------------+

The final result may produce a query that (starting from a specific day of month, or specific month, or specific year) will give me the number of minutes and hours that the system was powered up and producing kW.
Like, for example:

Selecting the date of 10/08/2020 i need to get the total production minutes and respective hours count
Selecting the month of july (7) i need to get the total production minutes and respective hours count
Selecting the year (2020) i need to get the total production minutes and respective hours count

Q: This is the query that i've built, actually i'm able to select only the rows that have the kw value greater than 1 and group them by
month, but i'm not able to figure out how to calculate the production
hours:

You need to get each measurement and the next measurement into a single row. This is typically what the lag window function does:
SELECT mt.insert_time,
       lag(mt.insert_time) OVER w AS next_time
       (mt.data->>'kw')::int AS curr_value,
       ((lag(mt.data) OVER w)->>'kw')::int AS next_value
FROM mytable mt
WHERE sensor = 7
WINDOW w AS (order by pk)  -- or order by insert_time if you prefer

Once both the current and next measurements are in the same row, you have the duration between them (=next_time - insert_time) and can sum and filter out rows when the kw value went from above 1 to below 1 or the opposite, or were both below 1.


PLSQL using cursors or functions to query repeated data Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)?
Whats the difference?
What are the best practices?
What are the other considerations?
Or is there another better way?

1) using a package cursor

cursor getLicenseStatus_cur (in_license_no varchar2) is
    SELECT status, status_dt from tbl_license where licence_no=in_license_no;

--use:
OPEN getLicenseStatus_cur('123');
fetch getLicenseStatus_cur into l_status, l_status_dt;
EXIT WHEN sql%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('l_status= ' || l_status || ', l_status_dt= ' || l_status_dt);
close getLicenseStatus_cur('123');

2) or using a function and passing a rowtype

create or replace function getLicenseStatus(in_license_no varchar2(10))
RETURN tbl_license%ROWTYPE
    as
    output_rec tbl_license%ROWTYPE;
begin
    SELECT * into output_rec
    from tbl_license where licence_no=in_license_no;
    return output_rec;
end;

--use
lic_rec users%ROWTYPE;
lic_rec := getLicenseStatus('123');
DBMS_OUTPUT.PUT_LINE('l_status= ' || lic_rec.status || ', l_status_dt= ' || lic_rec .status_dt);

3) or using a function and passing a sys refcursor

create or replace function getLicenseStatus(in_license_no varchar2(10))
return sys_refcursor as
    v_curs sys_refcursor;
begin
    open v_curs for SELECT status, status_dt from tbl_license where licence_no=in_license_no;
    return v_curs;
end;

--use:
v_rc := getLicenseStatus('123');
fetch v_rc into l_status , l_status_dt;
exit when sql%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('l_status= ' || l_status || ', l_status_dt= ' || l_status_dt);
close v_rc ;

This is a 4th option, using out parameters?

create or replace function getLicenseStatus
    (in_license_no varchar2, l_status out varchar2, l_status_dt out varchar2)
    as
begin
    SELECT status, status_dt
       into into l_status, l_status_dt
    from tbl_license
    where licence_no=in_license_no;
end;


SQL Server sp_describe_first_result_set: find user defined types I need to find the column definitions from a stored procedure.The docs for sp_describe_first_result_set show an entry for user_type_name where I'd expect to see my User Defined Types. Sadly, data for UDTs seems to be missing from the procedure result. Given the sample code:
CREATE PROCEDURE [dbo].[test]
AS
BEGIN
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @test TABLE (
        a BIGINT
        ,b udtTest
        ,c NVARCHAR(100)
        ,d BIT
    )

    INSERT INTO @test (
        a, b, c, d
    ) VALUES (
        1, 'udtTest', 'nvarchar', 0
    )

    select *
    from @test
END
GO
EXEC [dbo].[sp_describe_first_result_set] @tsql = N'test';
GO

I'm seeing the following (db<>fiddle):

name
system_type_name
user_type_name

a
bigint
NULL

b
nvarchar(123)
NULL

c
nvarchar(100)
NULL

d
bit
NULL

Why is udtTest missing from my data for column name b?

This appears not to work as advertised. Temporary tables are disallowed, but table variables should work just fine. Alias types are a bit of an edge case, so perhaps a bug crept in here unnoticed. If it is a blocker for you, open a support case with Microsoft.
There aren't any perfect workarounds (that I am aware of). One of the best ones is to create a wrapper procedure that uses WITH RESULT SETS to define the output shape:
CREATE TYPE dbo.udtTest FROM nvarchar(123) NOT NULL;
GO
CREATE OR ALTER PROCEDURE dbo.P AS
SET NOCOUNT ON;
DECLARE @T TABLE (udt dbo.udtTest);
INSERT @T VALUES (N'Banana');
SELECT T.udt FROM @T AS T;
GO
-- No user type info
EXECUTE sys.sp_describe_first_result_set
    @tsql = N'EXECUTE Sandpit.dbo.P;',
    @parameters = NULL,
    @browse_information_mode = 0;

-- Wrapper for dbo.P
CREATE OR ALTER PROCEDURE dbo.P_Wrapper AS
SET NOCOUNT ON;
EXECUTE dbo.P
    WITH RESULT SETS ((udt dbo.udtTest NOT NULL));
GO
-- Works!
EXECUTE sys.sp_describe_first_result_set
    @tsql = N'EXECUTE dbo.P_Wrapper;',
    @parameters = NULL,
    @browse_information_mode = 0;

This may or may not be convenient or practical for you. It's probably the best we can do until the bug (?) is fixed, or Microsoft extend CREATE PROCEDURE syntax to include WITH RESULT SETS, as we have been asking for a long time now.


"Operand type clash" with DATE column I enter the following command into the SQL Server Management Studio

insert into testtable ([product_name], [price], [expire_date], [expire_time])
  values ('Teapot', 10.00, 23/12/2012, '12:35:00')

It yields this error:

  Operand type clash: int is incompatible with date

These are the columns:

  Column name   |   datatype
----------------+---------------
product_name    | varchar(30)
price           | money
expire_date     | date
expire_time     | time(7)

Please use string delimiters around date literals, and do whatever you can to avoid ambiguous, regional formats like DD/MM/YYYY. How about:
insert into dbo.testtable ([product_name], [price], [expire_date], [expire_time])
  values ('Teapot', 10.00, '20121223', '12:35:00');

I use YYYYMMDD because, except with DATE, YYYY-MM-DD can be misinterpreted. With DATETIME, for example:
SET LANGUAGE FRENCH;
DECLARE @d DATETIME = '2012-12-23';

Result:

Msg 242, Level 16, State 3
La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

Your original format could also cause problems even with date:
SET DATEFORMAT MDY;
DECLARE @d DATE = '23/12/2012';

Result:

Msg 241, Level 16, State 1
Conversion failed when converting date and/or time from character string.

Not to sound preachy, but please see this blog post:

Bad habits to kick : mis-handling date / range queries

And this video:

Recommended SQL Server Date Formats

And Tibor's excellent article:

The ultimate guide to the datetime datatypes


How to delete a Database -Error throws the action cant be completed Am trying to delete the WSS_UsageApplication database in  my sql server 2012 SP2 and this is one of the databases associated with SharePoint.
since its consuming huge db space - 62 GB!! of my hard disk and my application is very very slow.
How to delete this database?
 when i tried to delete this from Data folder, am unable to delete the same.
it says the error :

  the action cant be completed because the file is open in sql server.

[![unabel2delete-DB][1]][1]

You can't delete it because it's open in SQL Server.

If you want to delete it, you'll have to stop SQL Server and delete the files.

You could also go on the server and run  DROP DATABASE WSS_UsageApplication;, assuming you have permission, and the database isn't in use. You may need to run ALTER DATABASE WSS_UsageApplication SET SINGLE_USER WITH ROLLBACK IMMEDIATE; first.

But really, deleting a 62 GB database because your 'application is slow' seems like a terrible idea. Since you're here asking why you can't delete a database that's in use by SQL Server, you probably haven't taken steps to tune anything. SharePoint is supported by Microsoft. Have you opened a ticket with them?

62 GB is also absolutely nothing. I'm typing to you from a laptop with two 1 TB SSDs in it. Is this really a concern?

You should at very least take a FULL backup of the database before proceeding.


Database Full Backup fails being deadlock victim On one of my SQL Server 2008 R2 Databases, Full backup job fails about twice a month due to Deadlocks. Transaction Log backup is only other job which is scheduled to execute at the same time.
Can you help me investigate what causes deadlock on backup operation?

SQL Server backup operations do not acquire locks, see - Paul Randal's post.
Therefore BACKUP DATABASE... or BACKUP LOG... cannot become deadlock victims.

Probably some other queries that take part in your backup jobs take part in deadlocks.

If you can describe your backup solution in detail, we might be able to help you.

I personally recommend Ola Hallengren's backup solution to do the job.


Large database design I am new to database design and despite my research I can really only find basic examples on design which does not help for my situation.

Currently using MySQL

The problem I have is as follows:

I am building a site to manage aircraft information, then display that information and how it changes in the form of graphs (things like air speed, plane speed, wing tilt, ect ect).

At the moment I get all this data in CSV form, but different planes give different data (for example one plane might not have wing tilt).

At the moment all I have set up is a table with userID, username, and password.

The concept I do not understand is how I can store something like planeSpeed, especially since it would be a bunch of numbers because it is over the course of the flight (it gets a value every minute).

Also some users would have multiple planes.

So I am stuck visualizing this as a column would be airspeed and then have a ton of numbers in it?

Or would I somehow build a separate table for each plane and instead of userID I would have time?

Very unsure how to design this, any help or tips would be a huge help, sorry for sounding so inexperienced have never built more than a simple login before.

As user1786423 said in your question's comment, you will probably have a User table, an Aircraft table and a table for each type of measurement.

As a minimum, User would have an id, say idUser. I'd imagine there would be more static data about a user in there as well.

As a minimum, Aircraft would have an id, say idAircraft, and idUser. idUser would reference User.idUser. I'd imagine there would be more static data about an aircraft in there as well.

The AirspeedMeasurement table would have, based on your descriptions, columns like idAirspeedMeasurement, idAircraft, time, airSpeed. idAircraft would reference Aircraft.idAircraft.

You may want to have a parent table for all of the measurements. For example, an AircraftMeasurement table would have the columns idAircraftMeasurement, idAircraft, time. Then, the child tables would have a primary key that references idAircraftMeasurement, and wouldn't have to each store idAircraft and time.

An ER diagram would look something like this:

Some sample data:

In this example Aircraft 1 records PlaneSpeedMeasurements and WingTiltMeasurements, whilst Aircraft 2 only records PlaneSpeedMeasurements.

Let us know if something is unclear : ]


How to rewrite CASE expression to short-circuit evaluation I would like to know if there is any way to rewrite the EXPRESSION in the query (just expression, not the whole query) to short-circuit useless evaluation of THEN phase?

Demo data:

CREATE TABLE #Docs (
    ID INT NOT NULL
    ,DocType TINYINT NOT NULL
    );

CREATE TABLE #DocsItems (
    IDDocs INT NOT NULL
    ,Amount NUMERIC(19,6)
    );

INSERT INTO #Docs(ID, DocType) VALUES(1,1),(2,1),(3,2),(4,2),(5,2),(6,2);
INSERT INTO #DocsItems(IDDocs,Amount) VALUES(3,50.),(3,25.),(3,33.),(4,44.),(4,123.),(6,11.);

Subject query:

SELECT
    -- expression
    SumAmount = CASE
                    WHEN D.DocType <> 1 THEN (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID)
                END
FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False

Query plan:

If I rewrite the query (on purpose) to:

SELECT
    -- expression
    SumAmount = CASE
                    WHEN 2 = 1 /* rewrite*/  THEN (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID)
                END
FROM #Docs D
WHERE D.DocType = 1

The plan goes the way it should / could / would  in the original query:

What is currently happening

When running your query, the table scan, stream agg & compute scalar operators are not evaluated at runtime.

Why is it happening

The apply NL join means that for each row in #Docs, return a row from #Docsitems that matches the predicate. This predicate should be WHERE IDDocs = D.ID

But the compute scalar operator (EXPR1007) next to the select (the actual case statement) is calling the scalar function in the inner side of the NL apply (EXPR1005) only when Doctype <> 1. This as you know cannot happen, and they both return NULL.

Compute scalar between NL & SELECT:

Compute scalar on the inner side of the join:

This all seems to be due to how the CASE statement functions & removing literals. (difference between CASE  2 = 1 vs. CASE WHEN D.DocType <> 1)

Resolving

If you change the query to this:

SELECT
    -- expression
    SumAmount =
                     (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID AND D.DocType <> 1 )

FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False

You should get the execution plan you desire:

Removing the SELonLOJ rule that is changing the plan on the rewrite.

Guessing

Reverting the rule that was applied to get closer to the CASE WHEN query can be done by adding the hint: OPTION( QUERYRULEOFF SELonLOJ   )

SELECT
    -- expression
    SumAmount =
                     (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID AND D.DocType <> 1 )

FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False
OPTION( QUERYRULEOFF SELonLOJ   );

Showing the same situation (minus filter & left join) as the case statement, but the runtime elimination is not occuring.

&

Another rule that seems to get closer to the case when when turning it off is JoinPredNorm

SELECT
    -- expression
    SumAmount =
                     (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID AND D.DocType <> 1 )

FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False
OPTION( QUERYRULEOFF JoinPredNorm   )

With the filtering happening on the #Docs table as expected.


Select distinct values between two values in different field I have a table named products like this:

| product_name | start_year | end_year |
|--------------|------------|----------|
| wine         | 2001       | 2012     |
| cheese       | 1999       | 2010     |
| apple        | 2008       | 2015     |

What kind of query do I have to run to have all values between start_year and  end_year where product = 'apple'?

This is the desired results:

| year |
|------|
| 2008 |
| 2009 |
| 2010 |
| 2011 |
| 2012 |
| 2013 |
| 2014 |
| 2015 |

PROPOSED QUERY

SELECT B.num year FROM
(SELECT start_year,end_year FROM products WHERE product_name='apple') A,
(SELECT tth*10000+th*1000+h*100+t*10+u num FROM
(SELECT 0 tth UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) TTHN,
(SELECT 0 th UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) THN,
(SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) HN,
(SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) TN,
(SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) UN) B
WHERE num BETWEEN A.start_year AND A.end_year ORDER BY B.num;

YOUR SAMPLE DATA

mysql> DROP DATABASE IF EXISTS EmaOnTheBlock;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE EmaOnTheBlock;
Query OK, 1 row affected (0.00 sec)

mysql> USE EmaOnTheBlock
Database changed
mysql> CREATE TABLE products
    -> (id int not null auto_increment,
    -> product_name varchar(20),
    -> start_year smallint not null,
    -> end_year smallint not null,
    -> primary key (id));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO products (product_name,start_year,end_year)
    -> VALUES ('wine',2001,2012),('cheese',1999,2010),('apple',2008,2015);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM products;
+----+--------------+------------+----------+
| id | product_name | start_year | end_year |
+----+--------------+------------+----------+
|  1 | wine         |       2001 |     2012 |
|  2 | cheese       |       1999 |     2010 |
|  3 | apple        |       2008 |     2015 |
+----+--------------+------------+----------+
3 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT B.num year FROM
    -> (SELECT start_year,end_year FROM products WHERE product_name='apple') A,
    -> (SELECT tth*10000+th*1000+h*100+t*10+u num FROM
    -> (SELECT 0 tth UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) TTHN,
    -> (SELECT 0 th UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) THN,
    -> (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) HN,
    -> (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) TN,
    -> (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) UN) B
    -> WHERE num BETWEEN A.start_year AND A.end_year ORDER BY B.num;
+------+
| year |
+------+
| 2008 |
| 2009 |
| 2010 |
| 2011 |
| 2012 |
| 2013 |
| 2014 |
| 2015 |
+------+
8 rows in set (0.03 sec)

mysql>

GIVE IT A TRY !!!


How to use pg_restore in database with different postgis installation (postgis schema location)? I have database1 where postgis is installed in the public schema.
And database2 where postgis is located in a schema called postgis.

When I dump database1.schema1, schema1.table1 references its geom column as public.geometry.

Therefore pg_restore throws "schema public does not exists" error
because schema1.table1 has public.geometry column and the schema does not exists nor is postgis installed in that schema.

How can I make a clean pg_dump without having schema qualified geometry columns?

The database2 search_path points to the schema postgis.
I am using PostgresSQL 10.

You cannot do that. Install PostGIS in the same schema in both databases. That shouldn't be a problem in the new database.

The alternative would be to manually edit the dump file, which is tedious and error prone.


generate unique primary key for all clients from the local database and NOT the server I'm using MySQL Innodb database to work as a distributed database among many places and also each place will work offline but at the end all the data will be centralized in one server,
I want to generate a unique primary key for registration table that will be unique for all clients, first I thought about int auto_increment but then I realized that this will cause conflict between each client as all of them will generate a new auto_increment from their local database and not from server so in this case all will start from 1 and increment for each new register
now I'm thinking of using a CHAR or VARCHAR to put a prefix for each registration in client's local database, or is there another way to do so?

If there are no natural way of identifying a registration, you may want to have a look at GUID:

https://en.wikipedia.org/wiki/Globally_unique_identifier

The upside is that there are plenty of libraries dealing with them. The downside is that they occupy space which may lead to higher B-Tree indexes, i.e. additional I/O. More read I/O is also required because GUID's are evenly distributed, when you read a page the likelihood that you get a related row is low which causes additional pages to be read.

Another option is to add another attribute representing the host and use this in combination with your sequence as an identifier.

primary key(host_id, seq_id)

Make sure that no two host uses the same host_id.

Yet a third option is to partition the seq_id for different hosts. I.e. host 1 uses seq_id 1..n, host 2 uses seq_id n+1..2n and so forth. Not an alternative for the heart of fainted ;-)


calculate and return the ratings using sql I have a table like this

What I am trying to achieve is weighted rating. For e.g., in this table we have five star as 3, four star as 1, three star as 1, two star as 1 and one star as 0. So the weighted rating is
((5x3) + (4x1) + (3x1) + (2x1) + (1x0)) / (3+1+1+1) = 4
for service uuid ff0177fe. How can I achieve this ?

You just need an aggregative query using GROUP BY like so:
SELECT
    service_uuid,
    ((5 * SUM(five_rating)) + (4 * SUM(four_rating)) + (3 * SUM(three_rating)) + (2 * SUM(two_rating)) + SUM(one_rating))
    /
    (SUM(five_rating) + SUM(four_rating) +  SUM(three_rating)  + SUM(two_rating) + SUM(one_rating))
FROM RatingsTable
GROUP BY service_uuid

Also just keep in mind that your columns will return integer results and therefore the / symbol will do integer division which means it'll round down to the nearest 0 (e.g. 5 / 2 = 2). If you want a weighted rating that includes fractional decimal points then you just need to cast either the divisor or dividend to float or decimal. You can find more information about this in this StackOverflow post's accepted answer.


MongoDB -- Failed global initialization: Failed to open "var/log/mongodb/mongod.log" I am getting errors starting Mongod v3.0.15 in Debian linux.  I had changed the /etc/mongod.conf file to use smallFiles by adding mmapv1: smallFiles: true to the storage options.
I now get the following error when I execute $mongod --config /etc/mongod.conf:

ib1:/ 514 $ mongod --config /etc/mongod.conf
2017-10-03T12:14:21.183-0500 F CONTROL  Failed global initialization: FileNotOpen Failed to open "/var/log/mongodb/mongod.log"

The mongod.log file exists, and it is also defined in the mongod.conf file as in that directory.
I am able to open the /var/log/mongodb/mongod.log file myself in the shell.  It hasn't been written to in a long time (months) curiously.
Any ideas?
Thanks very much,
Karen

You may need to change the permissions for the parents directory /var/log/ as the user running the mongod instance might not have access to it. The below code should do it

sudo chmod 755 /var/log


Composite Primary Key column order How would you do the ordering of the PK columns in this case (Oracle 11gR2 Standard Edition):

I have a database where I offer the same application to many users. I want to be perfectly sure that all tenants have separated data and that under no circumstances leaving out a WHERE (by user or persistence framework mistake) can show customer data to another customer. Also, I want to have as few maintenance as possible, so I do not want to have the same schema over and over again (because then, in order to make perfectly sure that all the environments are the same, one has neverending schema compare marathons).

My solution:

1 data schema with column "tenant_id" in every table and every view.
For every customer a view VTxxx_tablename or VTxxx_viewname in the form "SELECT * FROM underlyingobject WHERE tenant_id = x WITH CHECK
OPTION" with Select/Insert/Update/Delete-grants.
n customer schemas with no data.
For every of the n tenants synonyms to their granted views in their no-data schemas.
All of the above generated (and re-generated) by script.

Result:

All tables have a PK of (tenant_id, id) (or: (id, tenant_id), this is the question)
All FKs obviously include the tenant_id column, therefore no mixing of customer data possible.

The data:

"normal" OLTP-application (everything normalized, many JOINs in the SELECTs)
tenant_id is currently from [1,2,3], but might eventually get into the range 1..100.
ID is filled by one sequence per table.

The access:

All access is though the views, therefore every SELECT has an implicit WHERE tenant_id = x
Many SELECTs have a WHERE clause with full PK (implicit tenant_id through the used VIEW plus extra id like in WHERE tenant_id = x AND id = y)

Right now I decided for PK-column order (tenant_id, id).

Pro:

when ID column not present, INDEX SKIP SCAN or INDEX RANGE SCAN is possible
As no Oracle EE is available, this is my "poor mans partitioning" (downside below)

Con:

assuming the the application is used the same amount by all tenants, the PK has to be reorganized pretty much / for every insert, as low tenant_id rows are added as well.

What would you do in this case and why:

PK (tenant_id, id)
PK (id, tenant_id)
own schema (with own tablespace) per tenant nevertheless (more admin work (schema compare), better speed, simpler SQLs)

I'm looking for Oracle related physical differences only (like SCANs, Index Update costs or more general read costs/bottlenecks against write costs/bottlenecks)

Thank you,
Blama

Store the tenant_id first. When you do this you can enable index key compression.

See http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#i1106790 for the syntax and http://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm#i14618 for the concepts.

In your case, you can do it like this:

create unique index mytable_idx on mytable(tenant_id,id) compress 1;

alter table mytable add constraint mytable_pk primary key(tenant_id, id);


Running queries directly on DB2 databases Just have a general query, if it is really fine or safe to run long select queries on RDBMS production Databases specifically DB2? If NO, what are the major risks and issues in executing select queries directly on the backend production DB2 database?

Regards
Muddu

There are two major concerns here.  First you are using significant server resource (memory, CPU, disk) on a produciton box which, presumably, is busy.  This will be delaying other work.  In the extreme, your huge query may fully consume some shared resource and cause other work to fail.  Second, your query may be locking data others are trying to use.  There are ways around this which either compromise the integrity of your results or use further server resources.

Mitigations include

cut the job into smaller sub-jobs.  If you're processing data for a year, for example, submit 12 separate monthly jobs sequentially and combine these results afterwards.  Your elapsed time will be longer but your impact will be less.
run during slack times, after-hours or weekends.
copy the data to another server and run the job there.
ensure the SQL is efficient and the DB adequately indexed for this task.

If this work needs to be done, and it needs to be done on this box, then you don't have much choice.  Inform your management of the risks and good luck.


What's the difference between MySQL Fabric and MySQL Cluster I'm new to the MySQL world and was confused by the two. Don't they both provide high availability and sharding?

Also, how does WebScaleSQL compare?

These 3 things have nothing to do one with each other. I will give you an introduction and you can search or ask for more specifics separately:

MySQL Cluster is the marketing name of what many of us call - to avoid confusions like the one you are currently suffering from - "NDB Cluster" or "MySQL NDB Cluster". It is a synchronous mostly memory-only key-value store that allows a shared-nothing architecture for MySQL (providing a backend to the NDB Engine). It provides automatic sharding (so, better read and write throughput) and high availability. It works well for high-throughput key-value small-data systems, but it is relatively complex to setup (leading to problems if it is not configured correctly, or it is used in unintended ways) and it is not the best thing latency-wise (raw speed). This is simplified version (it can be used for more thing than memory-only and key-value, but it was designed around it). In my opinion, a very good piece of engineering, but it has limited use cases, as it is a different product than regular MySQL server with separate downloads (and requires the usage of a specific engine).

MySQL Fabric is a set of tools/middleware written in python that allows the managing of a set of regular MySQL servers in a replicated GTID environment. It is not a cluster per se, but it facilitates the managing of one. It has links to (for now) Java and Python connectors, so it can handle automatically the sharding and high availability of servers, but it is a layer on top of regular MySQL servers and regular replication (regular storage engines like InnoDB can be used). Replication, up to now is asynchronous (or semi-synchronous), so it may not provide by itself the best consistency and security; this may change in future 5.7 versions. However, this approach has less overhead on latency and uses MySQL DBA's more familiar replication technology. MySQL Fabric is a relatively new kid in the neighbourhood, so not yet very extended, but it seem to be Oracle's reaction (together with announced synchronous replication) to competitor's Galera-based clusters.

If you needed sharding and HA, you may use one of the 2 former products, but probably for totally different scenarios.

WebScaleSQL is not an HA solution, like the previous ones, it is just the name that several companies gave to a fork/version of MySQL where they share specific patches that they need and maybe Oracle has not applied. As far as I know it has been created by Facebook, Twitter, LinkedIn and Google, and they rebase their own MySQL versions on top of that common base. They do not provide support or binary releases. If you are an end user, you probably should not care to use it, but you should keep an eye on the project to see what will evolve from it, or the response from other vendors.

If you are trying to learn/start with MySQL HA/scaling, start with basic MySQL replication (it may not be the best, but it is the easiest thing to start with), then you can continue from there to other solutions: DRBD[sic], GTID-replication, NDB, Galera, Tungsten.


Finding dependencies on a SPECIFIC COLUMN (modern way, without using sysdepends) I need to find all views & stored procedures that consume not just a certain table, but a specific column in a table.

The following "seems" to work, but there are numerous warnings to be careful with this method (not reliable for a variety of reasons, soon to be deprecated, etc):

SELECT object_name(so.id) TableName, sc.name ColumnName, OBJECT_NAME(sd.id) DependentObjectName,
(SELECT xtype FROM sysobjects so WHERE so.id = sd.id) Object_Type
FROM sysobjects so INNER JOIN syscolumns sc
ON so.id = sc.id
INNER JOIN sysdepends sd
ON so.id = sd.depid and sc.colid = sd.depnumber
WHERE
    object_name(so.id) = 'MyTableName'
AND sc.name = 'MyColumnName'
order by object_name(so.id), Object_Type

Some alternative approaches that are often referenced are sys.sql_dependencies and sys.sql_expression_dependencies, but neither of these have column level granularity.

Does anyone know a way of doing this?  (Or even if you know definitively that it literally cannot be done, it would be helpful to know.)

Here is an AdventureWorks example on viewing column dependencies.

SELECT ReferencingObject = SCHEMA_NAME(o1.schema_id) + '.' + o1.name,
       ReferencedObject = SCHEMA_NAME(o2.schema_id) + '.'
                          + ed.referenced_entity_name,
       ColumnName = c.name,
       ReferencedObjectType = o2.type,
       ReferencingObjecType = o1.type
FROM   AdventureWorks2012.sys.sql_expression_dependencies ed
       INNER JOIN AdventureWorks2012.sys.objects o1
               ON ed.referencing_id = o1.object_id
       INNER JOIN AdventureWorks2012.sys.objects o2
               ON ed.referenced_id = o2.object_id
       INNER JOIN AdventureWorks2012.sys.sql_dependencies d
               ON ed.referencing_id = d.object_id
                  AND d.referenced_major_id = ed.referenced_id
       INNER JOIN sys.columns c
               ON c.object_id = ed.referenced_id
                  AND d.referenced_minor_id = c.column_id
WHERE  SCHEMA_NAME(o1.schema_id) + '.' + o1.name = 'HumanResources.vEmployee' AND c.name = 'JobTitle'
ORDER  BY ReferencedObject,
          c.column_id;


Would SQL Replication prove to be a FASTER option than downloading compressed DB files every time from servers at different geographic locations? My requirement of synchronising two DB servers in different geographic locations is as follows

synchronise some DBs on 2 servers in regular intervals (manual). One in US is an intermediate release server and we need those updated DBs on our local server every time a new build is deployed.
DBs are huge (not very huge though, more or less 30 GB)
Minimum possible action/disturbance on Source server while synchronising

Currently IT team here downloads the compressed database files from server at US to local server to get the latest updated DBs, which takes huge time for SOME of the DBs.

My question is: Would SQL Replication prove to be a FASTER option in this case?

DBs that take much time have only master look up tables but with huge data, which are updated less frequently. But we need to include these DBs as well, so as to keep the process clean and synchronised.

It seems, I cannot use Transactional Replication as not all tables have primary key here. Initially I thought,once replication is set-up, transactional replication anyways runs snapshot first (which would be a one time activity, so its okay even if it takes time) and replicating only the transactions later would not take much time. Hence, it would save heavy downloads every time.

But, if Snapshot replication is the only option left, I am wondering would it even save time over downloading MDFs,as what I understood till now is that snapshot replication will create a complete snapshot of DB everytime and apply them in batches.

I am not a DBA but a developer, and I was thinking by any chance if I can do something to speed-up current process. First thing I could think of, was replication. I have set-up a snapshot replication for a 30 GB DB which has replicated only 1.5 Gb of data in 2 hours. Please suggest.

There are couple of options --

I would not suggest to go with replication as you are making schema changes and it will require to reinitialize the whole replication with a snapshot.

Logshipping with standby option:

Cheap and effective. Works great and less overhead of maintenance.
Things to look out :

If an adhoc log backup is taken, it will break the log chain.
If you take differential backup then make sure you take full backups with COPY_ONLY option.
Your secondary will be readonly - use the with standby option, so you can read from your secondary. See my answer here for more details.

SSIS with incremental load

Refer to SSIS Design Pattern - Incremental Loads from Andy Leonard

If you are using Enterprise edition then I would suggest you to look into AlwaysON Availability groups.


Ignore a database in an Availability group when using @AvailabilityGroups = instead of @Databases with ola-hallengren backup solution I’m using the @AvailabilityGroups= parameter to backup availability groups, is it possible to NOT backup (exclude) a database in the Availability group?

If using the @Databases parameter I could use - but I am using the @AvailabilityGroups parameter instead.

Thanks

You can't use both the @Databases and @AvailabilityGroups parameter. If you try, you'll get this error:

  Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 633
  [Batch Start Line 0] You can only specify one of the parameters
  @Databases and @AvailabilityGroups.

For your condition, you'd want to only use @Databases with something like

@Databases = 'DB1, DB2'
@Databases = 'AVAILABILITY_GROUP_DATABASES, -DB1'