Mongo Collection `Size` is *larger* than `storageSize`? I recently compacted my collection using the command:

 db.<collectionName>.runCommand( "compact" )

And now my collection size seems to be bigger than size on disk!

SECONDARY> db.<collectionName>.stats()
{
"ns" : "<databaseName>.<collectionName>",
"count" : 2937359,
"size" : 5681676492,                   # 5.6 GB
"avgObjSize" : 1934.2805874256433,
"storageSize" : 4292853728,            # 4.2 GB
"numExtents" : 2,
"nindexes" : 2,
"lastExtentSize" : 2146426864,
"paddingFactor" : 1.669999999836597,
"flags" : 1,
"totalIndexSize" : 220735648,
"indexSizes" : {
    "_id_" : 162326304,
    "e_1_" : 58409344
},
"ok" : 1

}

I don't understand how this is possible. Aren't all mongodb collections backed-by-disk at all times?

Can anyone explain these results?

storageSize is the sum of all extents for that data, excluding indexes.

So that collection takes up 2 extents, they are ~2GB each, hence ~4GB. size includes indexes and I believe a couple of other things which inflate the number. Neither really represents the proper on-disk size. For disk size, db.stats() has a filesize field which is closer to what you want I think you're looking for.

The manual is somewhat better at outlining what the various fields mean, see here for collections:

http://docs.mongodb.org/manual/reference/collection-statistics/

And here for database stats:

http://docs.mongodb.org/manual/reference/database-statistics/

Some other potentially relevant information:

The compact command does not shrink any datafiles; it only defragments deleted space so that larger objects might reuse it. The compact command will never delete or shrink database files, and in general requires extra space to do its work, usually a minimum of one extra extent.

If you repair the database it will essentially rewrite the data files from scratch, which will remove padding and store them on disk as efficiently as you are going to get.  However you will need to have ~2x the size on disk to do so (actually less, but it's a decent guide).

One other thing to bear in mind here - repair and compact remove padding.  The padding factor varies between 1 (no moves of documents caused by documents growing), to 2 (lots of moves caused by documents growing).  Your padding factor of ~1.67 would indicate you are growing (and hence causing moves) quite a bit.

When you compact or repair a database you remove that padding - subsequent document growth is therefore going to trigger even more moves than before.  Because moves are relatiely expensive operations, this can have a serious impact on your performance.  More info here:

http://www.mongodb.org/display/DOCS/Padding+Factor


Cannot start SqlLocalDB instance with my Windows account I'm the administrator and simply run the command:

 sqllocaldb start v11.0

Result:

 Start of LocalDB instance "v11.0" failed because of the following error:
 Error occurred during LocalDB instance startup: SQL Server process failed to sta
 rt.

Event viewer log
Event ID: 528

  Windows API call WaitForMultipleObjects returned error code: 575.
  Windows system error message is: {Application Error} The application
  was unable to start correctly (0x%lx). Click OK to close the
  application. Reported at line: 3621.

I tried another (user and administrator) accounts, there were no problems with them.

I uninstalled & reinstalled 2012 version of SQLLocalDB.msi but I had no luck. Do you have any idea & fix?

Check the error log which usually under the folder %localappdata%\Microsoft\Microsoft SQL Server Local DB\Instances\v11.0 will give you big hints.

Log back in as the administrator account (not an administrator), and start a command prompt with Run As Administrator. Then run this:

sqllocaldb share v11.0 MyInstance

You might want to explicitly add your Windows account as a sysadmin on this instance if that is a goal. So:

sqllocaldb start MyInstance

Then connect with SQLCMD, Management Studio, what have you, and run:

CREATE LOGIN [Domain\Username] FROM WINDOWS;
EXEC sp_addsrvrolemember N'Domain\Username', N'sysadmin';

Now log back in as your Windows account, and you should be able to start this instance using:

sqllocaldb start MyInstance

(It may already be started depending on your O/S and how you switched accounts.)

Then Domain\Username should be able to connect using SQLCMD, SSMS etc. using (localdb)\.\MyInstance.


MariaDB Galera Cluster on Ubuntu 16.04 does not start after rebooting I've installed a MariaDB Galera Cluster on Ubuntu 16.04 in 2 nodes.

Here the info from node1:

root@localhost:~# sudo more /etc/mysql/conf.d/galera.cnf
[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://192.168.143.81,192.168.148.75"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="192.168.143.81"
wsrep_node_name="Node1"

and Here the info from node2:

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://192.168.143.81,192.168.148.75"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="192.168.148.75"
wsrep_node_name="Node2”

Everything was working fine since I restart both servers and I have a problem when starting the service:

Aug 02 09:18:03 localhost mysqld[3220]:          at gcomm/src/pc.cpp:connect():158
Aug 02 09:18:03 localhost mysqld[3220]: 2018-08-02  9:18:03 0 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():208: Failed to open backend connection: -110 (Connection timed out)
Aug 02 09:18:03 localhost mysqld[3220]: 2018-08-02  9:18:03 0 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1458: Failed to open channel 'galera_cluster' at 'gcomm://192.168.153.81,192.168.158.75': -110 (Conn
Aug 02 09:18:03 localhost mysqld[3220]: 2018-08-02  9:18:03 0 [ERROR] WSREP: gcs connect failed: Connection timed out
Aug 02 09:18:03 localhost mysqld[3220]: 2018-08-02  9:18:03 0 [ERROR] WSREP: wsrep::connect(gcomm://192.168.153.81,192.168.158.75) failed: 7
Aug 02 09:18:03 localhost mysqld[3220]: 2018-08-02  9:18:03 0 [ERROR] Aborting
Aug 02 09:18:04 localhost systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Aug 02 09:18:04 localhost systemd[1]: Failed to start MariaDB 10.3.8 database server.
Aug 02 09:18:04 localhost systemd[1]: mariadb.service: Unit entered failed state.
Aug 02 09:18:04 localhost systemd[1]: mariadb.service: Failed with result 'exit-code'.

The problem just happens after the Setting up IP Failover with KeepAlived  with a new floating IP 192.168.143.33

but I also did:

$ sudo ufw allow from 192.168.143.33 to any port 3306,4567,4568,4444 proto tcp
$ sudo ufw allow from 192.168.143.33 to any port 3306,4567,4568,4444 proto udp

doing

ip addr show eth0
7: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether f2:3c:91:49:7f:0a brd ff:ff:ff:ff:ff:ff
    inet 172.104.129.63/24 brd 172.104.129.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 192.168.143.33/17 scope global eth0:1
       valid_lft forever preferred_lft forever
    inet 192.168.143.81/17 scope global secondary eth0:2
       valid_lft forever preferred_lft forever
    inet6 2a01:7e01::f03c:91ff:fe49:7f0a/64 scope global mngtmpaddr dynamic
       valid_lft 2591984sec preferred_lft 604784sec
    inet6 fe80::f03c:91ff:fe49:7f0a/64 scope link
       valid_lft forever preferred_lft forever

where 172.104.129.63 is the public IP and the others one, the private IPs

This is likely to be a network or firewall-related issue.

A few things to check:

Do you get the same error on both nodes?
Have the IP addresses changed?
Are the relevant ports open in the firewall? Relevant ports are 3306, 4567, 4568, 4444. See here for details: Galera Firewall Settings
Could access to any of the ports be blocked by AppArmor?


General Question on sp_recompile For couple of scenarios, not many a times but few we have seen doing sp_recompile on a stored proc improving the performance.
Being a DBA i understand few of caveats involved when doing sp_recompile and why it would have worked in improving with performance.
There has been a constant debate within our management to provide Application/DEV DBA's the access to do sp recompile when they see performance issues like above. Just so that less of DBA involvement is there and APP devs can run on their during perf issues
I am very hesitant as i think it can bring some unwanted behaviors where it can sometime even worsen performance of other procs.
I am looking for general guidance and experience from your experts why this can be a bad idea under possible different circumstances or when it can be ideal. Your inputs might help me design and may be explain the approach in different way.
FYI- Above is being thought not across just one app but across few other as automation when seeing issues like parameter sniffing.

One thing that gets lost about sp_recompile is that it works on objects beyond stored procedures. From the docs:

The qualified or unqualified name of a stored procedure, trigger,
table, view, or user-defined function in the current database

If anyone gets a weird idea in their head, they could end up impacting more than just one procedure:

If object is the name of a table or view, all the stored procedures,
triggers, or user-defined functions that reference the table or view
will be recompiled the next time that they are run.

If developers already have the ability to use other flavors of recompile hints, it may be better to let them use those when appropriate, but also educate them on the difference in scope of what gets recompiled, and how they handle parameters:

For instances running at least SQL Server 2008 build 2746 (Service Pack 1 with Cumulative Update 5), using OPTION (RECOMPILE) has another significant advantage over WITH RECOMPILE: Only OPTION (RECOMPILE) enables the Parameter Embedding Optimization.

In general, WITH RECOMPILE should be avoided -- it's a very heavy handed approach. I'm in favor of targeting recompile hints either to temporarily solve a problem, or when the other potential solutions to a problem are unattractive.


Computed Columns - SQL Server Function I'm looking to improve overall SQL performance of a customers application. On each table of the application, there is a computed column, that uses GETUTCDATE and a combination of three columns on that table, to compute a column called for Active. This is referenced throughout the application to return records from the database in the where clauses.

I noticed this is slowing down each select of the system. I'm assuming GETUTCDATE, just like an article I saw from you saying don't use a Scalar function in a computer column, is just as bad. Do you have a recommendation on what to do here. I've always maintained an actual column of active, and built services to activate as necessary, which would obviously still be hitting sql, but might not effect the front end user interface.

You wouldn't be able to persist this computed column, as its value needs to change over time.  But having such a non-persisted computed column is a recipe for poor performance.

For this scenario you should decide how often you need to recalculate whether a row is "active" and run a scheduled batch process and/or a trigger to maintain the values.


How to Store a Many-to-Many Relationship Between Fact Tables in a Data Warehouse What are some structures and models to store many-to-many relational data between two fact tables in a data warehouse? Currently, I am using a mapping table which includes the primary keys from both tables, but I am wondering if there is a better approach?
My specific use case is relating invoices issued to payments received. I have an invoice fact table (each record designated with a unique InvoiceId) and a payment fact table (each record is designated with a unique PaymentId). My mapping table has columns for the InvoiceId and PaymentId as well as other useful information, but InvoiceId and PaymentId are not necessarily unique in their respective columns (because a payment can apply to multiple invoices and an invoice can be paid with multiple payments).
Thanks in advance for any advice and suggestions!

I just stumbled on your thread accidentally when searching for some stuff. I just read about this in the Kimball DWH ETL toolkit and I might share it with you. Your mapping table is what they refer to as a 'bridge table'. The extra element that they add is a 'group entity', which avoid a many-to-many join. You might want to check that out. See page 196 of that book.
I quote: 'To avoid a many-to-many join between the bridge table and the fact
table, one must create a group entity related to the multivalued dimension.
In the health care example, since the multivalued dimension is diagnosis,
the group entity is diagnosisgroup.'
Herewith the layout. Maybe it is of value to you. I thought it was quite interesting, although I haven't tried it out yet. I have used the similar concept like the bridge/mapping table within the context of object oriented software design though.


Error 1044 Access denied to user This is driving me crazy.

When I try to create a few tables from my Workbench model I get this error 1044.

I've been trying to find a solution but nothing works for me.

Curiously when I run SELECT USER(),CURRENT_USER(); I get two versions of the same user.  One is techboy@(an ip address) and the other is techboy@%.

Access is denied to both at one point or another.

The MySql server is a remote hosted server with the user permissions correctly set.

Please keep in mind that when you run

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in mysqld

CURRENT_USER() reports how you were allowed to authenticate by mysqld

This means you should run this query

SHOW GRANTS FOR CURRENT_USER();

or just

SHOW GRANTS;

to actually see your credentials. If you run,

SHOW GRANTS FOR USER();

you may not see credentials if they do not exist in mysql.user.

Just try to make the appropriate user on the MySQL Instance.

UPDATE 2013-03-05 16:51 EST

In you comment, you said

  Grants shows that 'GRANT USAGE ON . TO \'techboy\'@\'%\' IDENTIFIED BY PASSWORD \'and a character string\'' and 'GRANT ALL PRIVILEGES ON lfc_123awards.* TO \'techboy\'@\'%\' WITH GRANT OPTION'

Since you only have rights to the lfc_123awards database, please make sure you are specifying that database when you connect. If you do not set the default database at authentication, it will not let you connect at all.

Here is what I mean: Let say you trying connecting from the command line

mysql -utechbox -p -hIPofDBServer

This should fail, whereas

mysql -utechbox -p -hIPofDBServer -Dlfc_123awards

should work.


Adding new databases to existing maintenance backup plan I am trying to automate our backup plans to add newly created backups automatically on SQL server 2008. Is there a way to do this?
Since it wont let me comment on this to clarify. I am trying to add new databases that we have created to an existing Management Maintenance Plan on SQL Server 2008. I am trying to make it a script that we can run that will automatically add the database when created. Is this possible?

Yes, you can use the well used ola hallengren.scripts.

Make use of the SQL Server Backup scripts available on the above.

Eg:

  Back up all user databases, using checksums and compression; verify
  the backup; and delete old backup files

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 24

Then run the above script on SQL server agent job as per the backup plan strategy to execute this.

Also, just to mention you need to first create the scripts CommandExecute.sql and DatabaseBackup.sql in order to use the backup plan.


How can I find table name for each row result? How can I find the table name for each row result in my query?

I have tried ideas suggested on the stack network (overflow, dba)

1 How to make a table name an attribute in PostgreSQL?

2 https://stackoverflow.com/questions/24580352/get-the-name-of-a-rows-source-table-when-querying-the-parent-it-inherits-from

The template is the parent that spawns all the child tables I am searching. When I search just name and state, I get expected results, but they all come from different tables

select first_name, last_name, state
from template
where completed_interview_flag = 1

When I add the tablename as suggested, to try to find the source table name - I get an error

select tableoid::regclass AS source, first_name, last_name, state
from template
where completed_interview_flag = 1

********** Error **********

ERROR: column "tableoid" does not exist

SQL state: 42703

Character: 8

There must be some kind of misunderstanding. tableoid is a system column that's available for any regular table in any version of Postgres since at least v7.3. Per documentation:

  Every table has several system columns that are implicitly defined by the system.

tableoid is one of them. The solution I provided under the questions you refer to is also suggested in the manual here. And it works. I have been using it for years in various Postgres versions.

Either you are not using Postgres, or template is not a table. Is it a VIEW maybe? If you define a VIEW like:

CREATE VIEW template AS
SELECT * FROM some_table;

Then system columns are not included by default.

What do you get for

SELECT relnamespace, relname, relkind
FROM   pg_class
WHERE  relname = 'template';

Is relkind 'r', 'v' or something else?
Or do you get multiple rows? Then consider this:

Why does a PostgreSQL SELECT query return different results when a schema name is specified?


Should name suffix (Jr. Sr. III) be a separate field? An existing data model defines a person's name as having the following attributes:

first_name
middle_name
last_name

The last_name also include various suffixes such as Jr. Sr. III, etc.

One issue with this is that a query such as where last_name = 'Brown' will miss Brown Jr.  Is this a good enough reason to have the suffixes in a separate nullable field? What would be the downside of doing that?

One issue with this is that a query such as where last_name = 'Brown' will miss Brown Jr.

That's not really an issue. Just do WHERE last_name LIKE '%Brown%'. Generally, I don't put them in separate fields because my word loads don't require it. You can go by the name Jo2e Bob44 and I don't much care. Sometimes I break things apart into First Name, and Last Name to build Rolodex functionality or custom sorts.

The only time I think the application requires it would be if you're making an ancestry.com or the like. I could see it being more significant there.


Can rebuilding indexes cause worse performance after the rebuild is finished? We have a customer database that is heavily fragmented - practically every table with more than 1000 pages has >95% fragmentation. Fill factors are set to sensible values, but page space usage is nowhere near to fill factor for most tables.

This is the result of no maintenance being performed on the database.

Rebuilding the indexes using Ola Hallengren's IndexOptimize reduces fragmentation as expected. On the existing production hardware, performance of the application improves as expected. All the metrics I normally use - client statistics on heavy queries, profiler durations, read/write stalls, application logs and user perception - indicate performance is improved.

However, a new database server backed with Intel PCIe SSDs is showing the opposite of what we expect. Highly fragmented, the application performs well. After rebuilding indexes, the application performs badly. Some operations that took ~90s now take ~6mins. However, none of the other metrics appear to indicate that the system is going slower.

Is this something anyone else has experienced?

Yes, rebuilding indexes (especially on SSD) can cause worse performance. Most high speed SSD prefer many, smaller block requests instead of fewer, larger requests. This is exactly the opposite pattern preferred by traditional, spinning rust.

Assume you have a highly fragmented B-tree. Because nothing is ordered on the disk, you will typically issue a lot of 8KB I/O requests to scan the tree. If you were to defragment the tree, then you can get up to 512KB in a single request. Those large requests will have higher latency on the SSD, because the SSD internally breaks it down to 8KB chunks (unlike a hard drive, which will issue a sequential I/O). For a great many cases: Higher disk latency = slower queries

All that being said, please do make sure you check that you are actually getting the same query plans that you were getting before the rebuild.

An finally: Unless you are low on space, why are you wasting your precious DBA time with index rebuilds when you run on SSD?


Force a Member to Become Primary when Primary fails in mobgodb I have mongodb 2.6.11 cluster of two members: nms02 and nms01m. I know how to force secondary to become primary when primary is avail. But now my primary nms02 failed..and nms01m is secondary...How I force secondary to become primary now ?

nms:SECONDARY> rs.status()
{
        "set" : "nms",
        "date" : ISODate("2015-12-19T14:52:41Z"),
        "myState" : 2,
        "members" : [
                {
                        "_id" : 0,
                        "name" : "nms02:27017",
                        "health" : 0,
                        "state" : 8,
                        "stateStr" : "(not reachable/healthy)",
                        "uptime" : 0,
                        "optime" : Timestamp(1450445883, 1),
                        "optimeDate" : ISODate("2015-12-18T13:38:03Z"),
                        "lastHeartbeat" : ISODate("2015-12-19T14:52:37Z"),
                        "lastHeartbeatRecv" : ISODate("2015-12-18T13:38:03Z"),
                        "pingMs" : 0,
                        "syncingTo" : "nms01m:27017"
                },
                {
                        "_id" : 1,
                        "name" : "nms01m:27017",
                        "health" : 1,
                        "state" : 2,
                        "stateStr" : "SECONDARY",
                        "uptime" : 180204,
                        "optime" : Timestamp(1450445886, 1),
                        "optimeDate" : ISODate("2015-12-18T13:38:06Z"),
                        "self" : true
                }
        ],
        "ok" : 1
}

I can't do much on secondary now

nms:SECONDARY> cfg = rs.conf()
2015-12-19T10:20:51.821-0500 error: { "$err" : "not authorized for query on local.system.replset", "code" : 13 } at src/mongo/shell/query.js:131

If you have 2 members in a set, and one of them is unhealthy then you cannot elect a primary.  There must be >50% (a strict majority) to elect a primary, with 1/2 nodes up you cannot satisfy that condition, you can only ever get 50% exactly, which is not sufficient.  Your only option is to restore the other node or to add another node (perhaps an arbiter). However, adding a node will require a forced reconfiguration because you do not currently have a primary. Fortunately the procedure is well documented here.


sp_MS_marksystemobject doesn't appear to be working I have been creating a stored procedure to automate the addition of "pseudo-rows" to dimension tables (e.g. -1 = 'Unknown', -2 = 'None', etc.)

Since we have several database that contain datamart data, I opted to make this a system stored procedure that can be shared from multiple databases.  The procedure queries the local database system tables (sys.tables, sys.columns, sys.schema) to construct a dynamic SQL statement that will then be executed.

However, the stored procedure is not working as intended:  despite marking the procedure a system stored procedure, it is working only in the context of the Master database.

Here's a bare-bones implementation that illustrates the issue:

 USE Master
 GO

 CREATE PROCEDURE dbo.sp_WhereAmI
 AS SELECT DB_NAME()
 GO

 EXEC sp_MS_marksystemobject 'dbo.sp_WhereAmI'
 GO

 USE SomeOtherDatabase
 GO

 SELECT DB_NAME()
 EXEC MASTER.dbo.sp_WhereAmI

...and my output looks like

  -----------------
  SomeOtherDatabase
  (1 row affected)

 ------------------
 master
 (1 row affected)

This is precisely NOT as expected.    By marking the stored procedure as a system procedure, I expected the CALLING database context to be used, but that's not happening.

Any idea what's going on here?

Why are you using the master prefix? That is saying "go execute this procedure in the context of master."

Try just EXEC dbo.sp_WhereAmI; or even EXEC tempdb.dbo.sp_WhereAmI;...


Deadlock error in inserting bulk of records to MySQL database using trigger and Qt multi threading I have a multi thread program (In this case in Qt) which every thread at a specific time, inserts 500 records in a MySQL table (I call that as A_tbl) and also there is a trigger for A_tbl that inserts some values to other tables (I call them as B_tbls) and after that by some select queries in the trigger, I get the IDs of those inserted records in B_tbls. (I think that I have to use these select queries and last_insert_id() is not useful in my case since maybe I need the ID of the first inserted record).

But these select queries along with the last insert query lead to this error:

Deadlock found when trying to get lock; try restarting transaction QMYSQL3: Unable to execute statement

This is my trigger:

CREATE DEFINER=`root`@`localhost` TRIGGER `IpAn`.`A_tbl_BEFORE_INSERT` BEFORE INSERT ON `A_tbl` FOR EACH ROW
BEGIN

INSERT IGNORE INTO source_names (source_name) VALUES (NEW.source_name);
INSERT IGNORE INTO locations (loc1,loc2) VALUES (NEW.loc1,NEW.loc2);

SET @source_names_id = (select id from source_names USE INDEX (PRIMARY) where source_name=NEW.source_name);
SET @locations_id = (select id from locations USE INDEX (PRIMARY) where loc1=NEW.loc1 and loc2=NEW.loc2);

INSERT IGNORE INTO sections (source_names_id,locations_id) VALUES (@source_names_id,@locations_id);

...

END

If I change the threads to one, the error will not occur.

How can I solve this?

SET @source_names_id = (select id from source_names USE INDEX (PRIMARY) where source_name=NEW.source_name);

-->

SELECT @source_names_id := id
    FROM source_names
    WHERE source_name = NEW.source_name;

AND have

PRIMARY KEY(id)
INDEX(source_name)

Do not use USE INDEX(PRIMARY) unless it is PRIMARY KEY(source_name).  Even then, there is no need for USE INDEX.

Please provide SHOW CREATE TABLE if you need to discuss this further.


Index by subkey inside subkey inside array in jsonb I have a jsonb field registration that contains an array of objects. I need to create two separates indexes to look for id, by example:

d61802ff-3eec-4a72-97ca-832f51b96bf0 = [ .tour_id.id ]
f492b26d-d260-4bcb-8e96-6532fdf38665 = [ .customer_id.id ]

Example data (I have more keys and arrays, but it's a resume)

[
   {
    "tour_id":
      {
        "id":"d61802ff-3eec-4a72-97ca-832f51b96bf0",
        "name":"Paris  2018"
      },
    "customer_id":
      {
        "id":"f492b26d-d260-4bcb-8e96-6532fdf38665",
        "name":"OBAMA John"
      }
   },
   {
    "tour_id":
      {
        "id":"a52d38d4-9bfb-4ffa-1122-e536f04b0c60",
        "name":"London  2018"
      },
    "customer_id":
      {
        "id":"f492b26d-d260-4bcb-8e96-6532fdf38665",
        "name":"OBAMA John"
      }
   }
]

How to index by [array].customer_id.id?
Or is there a method to index by subkey.subkey? Or deeper nested subkey?

I'm using Postgres 9.6.

First off, I would consider a normalized DB design instead of the convoluted jsonb column, which makes indexing and complex queries harder.

  How to index by [array].customer_id.id?

See my answer to your previous question:

Index by subkey in jsonb

You could have two GIN indexes like instructed there, one on tour_id, another one on customer_id, or a single multicolumn index, and then query:

SELECT * FROM tbl
WHERE  f_extract_tour_ids(registration)     @> '{d61802ff-3eec-4a72-97ca-832f51b96bf0}'::uuid[]
AND    f_extract_customer_ids(registration) @> '{f492b26d-d260-4bcb-8e96-6532fdf38665}'::uuid[];

Note, this returns rows with tour_id and customer_id matching in any top-level object of the array, not necessarily in the same. If you want to find rows where tour_id and customer_id match in the same object, you have to do more ...

jsonb_path_ops index

However, with a growing number of different queries (requiring multiple special indexes), a single generic (much bigger) jsonb_path_ops index on the whole column becomes more competitive. Or even one with the default operator class operator class jsonb_ops - which is bigger, yet.

CREATE INDEX foo ON tbl USING GIN (registration jsonb_path_ops);

Queries to go with it:

For rows with tour_id and customer_id matching in the same top-level object:

SELECT * FROM tbl
WHERE  registration @> '[{"tour_id": {"id": "d61802ff-3eec-4a72-97ca-832f51b96bf0"}
                        , "customer_id": {"id": "f492b26d-d260-4bcb-8e96-6532fdf38665"}}]';

For rows with tour_id and customer_id matching in any top-level object, not necessarily the same:

SELECT * FROM tbl
WHERE  registration @> '[{"tour_id": {"id": "d61802ff-3eec-4a72-97ca-832f51b96bf0"}}
                       , {"customer_id":{"id":"f492b26d-d260-4bcb-8e96-6532fdf38665"}}]';

This solution is slower because of the much bigger index carrying all the other noise in your jsonb column. But it is much more versatile and simpler.
If the first solutions make your head ache, use this one.


What are the differences between the first and subsequent rds_superusers in an AWS RDS Postgres Instance? I have been asked to provide administration of an existing RDS Postgres instance. The AWS admin has created a new user for me and has added the rds_superuser role to it.

But this user with the rds_superuser role does not seem to have the same set of privileges as the default rds_superuser.
I cannot, for example, create new roles.
Does anyone know the difference between the default rds_superuser that comes with a new RDS PG instance and those users that are subsequently granted the rds_superuser role?

rds_superuser, unlike a real superuser, doesn't inherently have permission to create roles.

You will have to have the other rds_superuser (or anyone else who already has createrole) do:

alter user new_user createrole;


Does NTILE work over floating point numbers? I am trying to use NTILE on my data using a float data type in my PARTITION BY clause.  Please excuse the contrived example, but I think the best way to illustrate my question and confusion is through this code:

CREATE TABLE Test
(
    Cash float NOT NULL,
    Number int NOT NULL
)
GO

INSERT INTO TEST VALUES(1.05,1);
INSERT INTO TEST VALUES(1.368,1);
INSERT INTO TEST VALUES(0.775,1);
INSERT INTO TEST VALUES(1.699,1);
INSERT INTO TEST VALUES(1.599,1);
INSERT INTO TEST VALUES(0.80,1);
INSERT INTO TEST VALUES(0.80,1);
INSERT INTO TEST VALUES(0.994,1);
INSERT INTO TEST VALUES(0.848,1);
INSERT INTO TEST VALUES(0.675,1);
INSERT INTO TEST VALUES(0.575,1);
INSERT INTO TEST VALUES(12.998,1);
INSERT INTO TEST VALUES(1.999,1);
INSERT INTO TEST VALUES(0.65,1);
INSERT INTO TEST VALUES(0.80,1);
INSERT INTO TEST VALUES(2.60,1);

SELECT CASH,
    NTILE(3) OVER (PARTITION BY Cash ORDER BY Cash) AS Trio
    INTO #Test
FROM Test

I would have expected the groupings to be something like this:

0.575   1
0.65    1
0.675   1
0.775   1
0.8     1
0.8     2
0.8     2
0.848   2
0.994   2
1.05    2
1.368   3
1.599   3
1.699   3
1.999   3
2.6     3
12.998  3

But instead, The result set looks like this:

CASH    Trio
0.575   1
0.65    1
0.675   1
0.775   1
0.8     1
0.8     2
0.8     3
0.848   1
0.994   1
1.05    1
1.368   1
1.599   1
1.699   1
1.999   1
2.6     1
12.998  1

And the range groupings look really strange when max and min are applied by group

SELECT
    MAX(CASH), MIN(CASH), Trio
FROM #Test
GROUP BY Trio

MAX     MIN     TRIO
12.998  0.575   1
0.8     0.8     2
0.8     0.8     3

Can anyone explain what is going on here?

The NTILE  function breaks an input set down into N equal sized groups.  To determine how many rows belong in each group, SQL Server must first determine the total number of rows in the input set.

If the NTILE function includes a PARTITION BY clause, SQL Server must compute the number of rows in each partition separately.  Once we know the number of rows in each partition, we can write the NTILE function as

NTILE(N) := (N * (ROW_NUMBER() - 1) / COUNT(*)) + 1

where COUNT(*) is the number of rows in each partition.

Taking above into consideration, below will explain :

select *, (3*(RowNumber-1)/Cnt)+1 AS MyNTile
from
(
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY Cash ORDER BY cash) AS RowNumber,
        COUNT(*) OVER (PARTITION BY Cash ) AS Cnt,
        NTILE(3) OVER (PARTITION BY Cash ORDER BY Cash) AS NTile,
        NTILE(3) OVER (ORDER BY Cash) AS WholeTableNtile

    FROM Test
) T

Refer to : Ranking Functions: RANK, DENSE_RANK, and NTILE


Does backing up a database shrink the transaction log size? I am trying to gather some knowledge on SQL databases and I have some questions about the transaction log file (LDF).

First off, when you create a database, you have to define an Initial File Size for both the database and the log file. From what I can see, once the files are created on the disk, they will be of that specified size regardless if there is actual data in the database or if there has been any transactions (logs).

My understanding was that backing up a database:

Truncates the transaction log, and
Shrinks the size

of the LDF file on the disk by "emptying" the logs inside it.

Now it seems that I was not understanding this correctly because the log file seems to be of fixed size. My actual question goes like this:

What does truncating logs actually do to the log file (LDF)? This process is supposed to prevent disks from getting full.

Please do correct me if I am not understanding some concepts correctly.

Thank you!

The transaction log file (LDF) is made up of lots of virtual log files (VLFs) inside. Think of it like a cabinet with several pull-out drawers. You could choose a large cabinet, or a small one - but it's still going to be a fixed size with just different numbers of drawers.

As SQL Server works, it puts your transactions into drawers (VLFs.) It starts at one end of your cabinet, fills up the first drawer, then when space runs out in that drawer, it moves on to the next drawer.

When you back up the transaction log, what you're really doing is:

Finding the first drawer that has transactions in it that haven't been backed up yet
Copying those transactions elsewhere
If that drawer is no longer actively in use (because SQL Server has moved on to the next drawer), then you're marking that drawer as available to reuse (think of it as throwing all the contents out)

Backups don't change the size of your cabinet (log file).


Greyed out menu options, broken activity monitor I recently installed SP1 on my 2012 SQL Server. Ever since then SSMS has behaved a little weirdly when I connect from my pc rather than from the local server itself.

I no longer see the green "running" icons on the server and on the agent, and several menu options are greyed out (Start/Stop/Restart on server menu and agent menu).

Also, when I try to access the Activity Monitor, I get the following error:

Error Detail

I found the following article about the Activity Monitor. I followed the steps there (although I am a local admin on the sql server and should have had all of those permissions already) to no avail. The server is running Windows 2008 R2 Datacenter SP1. When I remote desktop to the server itself and use SSMS directly I have none of these issues, and when a domain admin connects to the server through SSMS remotely everything also works for him.

Any ideas?

The behavior you are seeing happens if you aren't recognized as having permissions to manage services on the remote server.  This MSDN article describes some possible solutions on how to grant users rights to manage services.

Does the service account for the agent have local administrator permissions on the server?  Have you stopped and started database services and the SQL Server agent service on the server since encountering this problem?


Retrieve date ranges from multiple rows I'm struggling to find an optimal way of achieving this without lots of individual queries and PHP, I'm sure there must be an easier way of doing it.
To simplify we have the following table structure:
CREATE TABLE `log` (
  id INT UNSIGNED AUTO_INCREMENT,
  userid INT,
  itemid INT,
  eventname VARCHAR(30),
  timecreated INT
);

timecreated is a unix timestamp, eventname will be one of 2 statuses (assigned or unassigned).
Assuming the following data (I've put actual dates so it's easier to read):

id
userid
itemid
action
timecreated

1
1
1
assigned
2020-01-01

2
1
2
assigned
2020-01-01

3
1
1
unassigned
2020-03-01

4
1
1
assigned
2020-06-01

5
1
1
unassigned
2020-06-30

The output should be:

itemid
start
end

1
2020-01-01
2020-03-01

2
2020-01-01

1
2020-06-01
2020-06-30

Plan A:  Change the schema to have two date columns.  Then have the code INSERT a row when the item is "assigned" and UPDATE the other column when it is "unasigned".  This essentially eliminates the entire problem.  And it cuts in half the number of rows needed.
Plan B:  Split the effort.  Using the schema you currently have, SELECT ... ORDER BY ... to get the pairs of rows adjacent.  Then have the application code finish the task by remembering the contents of the first row of each pair ("assigned") just long enough to blend it with the next row ("unassigned").


In SQLITE, how do you create a new table into its own file? I am trying to do key value pairing, where performance is the top priority. I understand that the smaller a sqlite file is, the better the read and write speeds are, and so I am going to create about 64 files, having every explicit key-value combination.
Hence I would like to create a command that will create a new table inside its own file. How do I do this?
Thanks.

If you're trying to store key-value pairs of data you should use a key-value store database instead of SQLite, since SQLite is meant to store relational data. Where is your database going to live?...mobile device or dedicated server?
If you do stick to SQLite though, file size is not much of a factor in terms of read and write speeds. Rather you're probably thinking of concurrency issues, especially with concurrent writes to the database. In that case, you can do things like enabling the WAL and possibly creating a separate database for any groups of tables you would need to heavily concurrently write to, since concurrency issues mostly occur within the same database. Also ensure you index your tables appropriately, which will likely just be the key of your key-value pairs.
Also switching to an appropriate key-value data store would likely help you with any concurrency issues as well.


Should TempDB SSD Drive be formatted with 64 Kb Allocation Unit Size? We have installed dedicated SSD drives for TempDB

First question - should these drives be formatted with 64 Kb allocation size ? Currently it shows 4 Kb (Bytes per Cluster = 4096)

Does TempDB benefit from 64 Kb unit size, or it can be left with default 4 Kb ?

Second question - we have data and log files on a separate "virtual" volumes that SAN admin sliced before, those volumes are made from SAN RAID array, and show 4 Kb allocation unit size

We can not change allocation unit size for those volumes at this point.
So if we change TempDB SSD drive to 64 Kb, and data/log volumes stay as 4 Kb, will it cause any trouble for the SQL Server ?

ABSOLUTELY format it 64k.. tempdb will be faster and less taxed than the other san datafiles. Factors can be many, but the main one being heavy tempdb usage.  SQL Server allocates space in multiples of 64k, as does Win Server, by design.
Writing into a properly pre allocated datafile on its own 64k formatted drive is very significant to performance.  64k chunk would go from 16x4k data file reads to tempdb in 1x64k packet write as opposed to 16k packet writes.   4k allocation on the read san drive really isn't a big concern unless there's lots of fragmentation, it's the writing that will be impacted by 4k San.
Thats the mathematical truth. However in theory, a properly raided San even at 4k may not be as noticable because consider the drive head multiples they're writing with.

Beyond that are architectural modification you can use such as having tempdb use multiple equal data partitions. But that is probably beyond scope.

That is my short answer, hope I didn't confuse the issue, by being short.


Joining Additional Table Column I need to add another column from another table i.e. CusSorMaster+.SalesOrder.The join is on where dbo.SorMaster.SalesOrder = dbo.CusSorMaster+.SalesOrder. This wasn't written by me and I need to manipulate it to include the other column.When I join the table with the column I don't get the same rows as my original query which I want. Please assist and feel free to ask any questions.
SELECT RIGHT(dbo.SorMaster.SalesOrder,6) AS SalesOrder,
       dbo.SorMaster.CustomerPoNumber,
       dbo.SorDetail.SalesOrderLine,
       dbo.SorDetail.MStockCode,
       dbo.SorDetail.MStockDes,
       dbo.SorDetail.MOrderQty,
       dbo.SorDetail.MCusSupStkCode,
       dbo.SorDetail.LineType,
       dbo.SorMaster.OrderStatus,
       dbo.[CusSorDetailMerch+].DuparJobNumber,
       dbo.SorMaster.Customer,
       dbo.SorDetail.MShipQty,
       dbo.SorDetail.MBackOrderQty,
       CASE
           WHEN
       RIGHT(dbo.SorMaster.SalesOrder,6) = '211125'
           THEN dbo.SorDetail.MOrderQty
           ELSE dbo.SorDetail.MShipQty + dbo.SorDetail.MBackOrderQty
       END AS ShipPlusBackorderQty,
       dbo.InvAltStock.AltStockCode,
       RIGHT(STUFF(DATEPART(year,GETDATE()),1,0,'0'),2) + RIGHT(STUFF(DATEPART(week,GETDATE()),1,0,'0'),2) AS YearWeekNo,
       'B00ABD' + RIGHT(STUFF(DATEPART(year,GETDATE()),1,0,'0'),2) + RIGHT(STUFF(DATEPART(week,GETDATE()),1,0,'0'),2) + '' AS SerialPrefix,
       dbo.[CusSorDetailMerch+].InvoiceNumber
  FROM dbo.SorMaster WITH(NOLOCK)
 INNER JOIN dbo.SorDetail WITH(NOLOCK) ON dbo.SorMaster.SalesOrder = dbo.SorDetail.SalesOrder
 LEFT OUTER JOIN dbo.InvAltStock ON dbo.SorDetail.MStockCode = dbo.InvAltStock.StockCode
 LEFT OUTER JOIN dbo.[CusSorDetailMerch+] ON dbo.SorDetail.SalesOrder = dbo.[CusSorDetailMerch+].SalesOrder
                                                      AND dbo.SorDetail.SalesOrderLine = dbo.[CusSorDetailMerch+].SalesOrderInitLine
 WHERE(
       dbo.SorDetail.LineType = '1'
       OR
       dbo.SorDetail.LineType = '7')
      AND (
       dbo.SorMaster.OrderStatus <> '9')
      AND (
       dbo.SorMaster.OrderStatus <> '\')
      AND (
       dbo.SorMaster.OrderStatus <> '*')
      AND (
       dbo.[CusSorDetailMerch+].InvoiceNumber = ''
       OR
       dbo.[CusSorDetailMerch+].InvoiceNumber = '000000000000000'
       OR dbo.[CusSorDetailMerch+].InvoiceNumber IS NULL);

In order to preserve the "left" part of the join (= your current rows), you need to join with a left outer join. You can learn about outer joins in the documentation.
By the way, if you only need the field you are joining on, then joining is not required...
I took the liberty of:

reformatting the query,
rewriting parts of the WHERE clause to use the IN keyword,
removing the keyword outer (it is optional in T-SQL),
removing the WITH (nolock) options, put them back if you really need them.

Updated query:
SELECT  RIGHT(dbo.SorMaster.SalesOrder, 6) AS 'SalesOrder',
        dbo.SorMaster.CustomerPoNumber,
        dbo.SorDetail.SalesOrderLine,
        dbo.SorDetail.MStockCode,
        dbo.SorDetail.MStockDes,
        dbo.SorDetail.MOrderQty,
        dbo.SorDetail.MCusSupStkCode,
        dbo.SorDetail.LineType,
        dbo.SorMaster.OrderStatus,
        dbo.[CusSorDetailMerch+].DuparJobNumber,
        dbo.SorMaster.Customer,
        dbo.SorDetail.MShipQty,
        dbo.SorDetail.MBackOrderQty,
        CASE
            WHEN RIGHT(dbo.SorMaster.SalesOrder, 6)= '211125'
            THEN dbo.SorDetail.MOrderQty
            ELSE dbo.SorDetail.MShipQty + dbo.SorDetail.MBackOrderQty
        END AS 'ShipPlusBackorderQty',
        dbo.InvAltStock.AltStockCode,
        RIGHT(STUFF(DATEPART(year, GETDATE()), 1, 0, '0'), 2) + RIGHT(STUFF(DATEPART(week, GETDATE()), 1, 0, '0'), 2) AS 'YearWeekNo',
        'B00ABD' + RIGHT(STUFF(DATEPART(year, GETDATE()), 1, 0, '0'), 2) + RIGHT(STUFF(DATEPART(week, GETDATE()), 1, 0, '0'), 2) + '' AS 'SerialPrefix',
        dbo.[CusSorDetailMerch+].InvoiceNumber,
        dbo.SorMaster.SalesOrder,       -- new selected field (no join required)
        dbo.[CusSorMaster+].SalesOrder, -- new selected field
        dbo.[CusSorMaster+].*           -- new selected fields: all fields from [CusSorMaster+]
FROM dbo.SorMaster
JOIN dbo.SorDetail
    ON dbo.SorMaster.SalesOrder = dbo.SorDetail.SalesOrder
LEFT JOIN dbo.InvAltStock
    ON dbo.SorDetail.MStockCode = dbo.InvAltStock.StockCode
LEFT JOIN dbo.[CusSorDetailMerch+]
    ON  dbo.SorDetail.SalesOrder = dbo.[CusSorDetailMerch+].SalesOrder
    AND dbo.SorDetail.SalesOrderLine = dbo.[CusSorDetailMerch+].SalesOrderInitLine
LEFT JOIN dbo.[CusSorMaster+]                                               -- new outer join
    ON dbo.SorMaster.SalesOrder = dbo.[CusSorMaster+].SalesOrder
WHERE dbo.SorDetail.LineType in ('1', '7')                                  -- rewrote OR
  AND dbo.SorMaster.OrderStatus not in ('9', '\', '*')                      -- rewrote OR
  AND (dbo.[CusSorDetailMerch+].InvoiceNumber in ('', '000000000000000')    -- partially rewrote OR
    OR dbo.[CusSorDetailMerch+].InvoiceNumber IS NULL);


Determining why a transaction log file is so large I have a SQL Server 2016 Enterprise database server that I have recently inherited.  One instance on this server has an application that uses two different databases.  One of these is the actual application database and the other is used to generate reports and dump data from the first.  The second database has several stored procedures that run at different times, including one that dumps data from a view into a CSV file for offsite storage of the data that runs every 15 minutes.

When I initially got this database, the recovery model was set to simple and a database backup was being taken nightly.  Since, I have changed the recovery model to full and changed the backup plan so that it is taking full backups twice weekly, differential backups twice daily and log backups every three hours.  However, even with this change, the transaction log for the second database is staying around 300GB.  Each transaction log backup is around 80GB.

I have plans to dig into how the stored procedures work and hopefully simplify some the way these things run, but that is going to be a long term project.  In the short term, I need to find a way to determine if the transaction log is being truncated correctly and if there is anything I can do to reduce the size of the transaction log on this database.  I've looked at the log with the fn_dblog function hoping to see if there were old transactions being stored, but most of the Begin Time and End Time records are null.

Other than a complete rewrite of the system (which is in the works), does anyone have any suggestions for getting a handle on this log file?

the transaction log for the second database is staying around 300GB. Each transaction log backup is around 80GB.

That sounds reasonable.  Your log file should have enough room to accommodate the log records between any two backups without growing.  And it's better if you can afford to skip a log backup, or accommodate an unusually busy period without growing.  Growing the log file requires it to be zeroed, which takes time, and there may not be space available.

So if you normally need 80GB, having 300GB reserved sounds reasonable.

If you shrink the log file after every log backup for a few days, you can probably shrink it, but I wouldn't go below 200GB probably.  Internally the effectiveness of a log shrink operation depends on which VLFs inside the log file have active records.  See Shrinking the Transaction Log and How to Shrink the SQL Server Log for an explanation of why it can take several tries to shrink the log file down to something close to the average log backup size.


Strategy for concurrent group bookings? Consider a seat booking database. There is a list of n seats, and each one has an attribute is_booked. 0 means it isn't, 1 means it is. Any higher number and there is an overbooking.

What is the strategy for having multiple transactions (where each transaction will book a group of y seats concurrently) without allowing over bookings?

I would simply select all unbooked seats, select a randomly selected group of y of them, book them all, and check if that booking is correct (aka the number of is_booked is not over one, which would signify another transaction having booked the seat and committed), then commit. otherwise abort and try again.

This is run at isolation level Read Committed in Postgres.

Because you're not telling us much of what you need, I'll guess for everything, and we'll make it moderately complex to simplify some of the possible questions.

First thing about MVCC is that in a highly concurrent system you want to avoid table locking. As a general rule, you can't tell what does not exist without locking the table for the transaction. That leaves you one option: don't rely on INSERT.

I leave very little as an exercise for a real booking app here. We don't handle,

Overbooking (as a feature)
Or what to do if there are not x-remaining seats.
Buildout to customer and transaction.

The key here is in the UPDATE. We lock only the rows for UPDATE before the transaction starts. We can do this because we've inserted all seat-tickets for sale in the table, event_venue_seats.

Create a basic schema

CREATE SCHEMA booking;
CREATE TABLE booking.venue (
  venueid    serial PRIMARY KEY,
  venue_name text   NOT NULL
  -- stuff
);
CREATE TABLE booking.seats (
  seatid        serial PRIMARY KEY,
  venueid       int    REFERENCES booking.venue,
  seatnum       int,
  special_notes text,
  UNIQUE (venueid, seatnum)
  --stuff
);
CREATE TABLE booking.event (
  eventid         serial     PRIMARY KEY,
  event_name      text,
  event_timestamp timestamp  NOT NULL
  --stuff
);
CREATE TABLE booking.event_venue_seats (
  eventid    int     REFERENCES booking.event,
  seatid     int     REFERENCES booking.seats,
  txnid      int,
  customerid int,
  PRIMARY KEY (eventid, seatid)
);

Test Data

INSERT INTO booking.venue (venue_name)
VALUES ('Madison Square Garden');

INSERT INTO booking.seats (venueid, seatnum)
SELECT venueid, s
FROM booking.venue
  CROSS JOIN generate_series(1,42) AS s;

INSERT INTO booking.event (event_name, event_timestamp)
VALUES ('Evan Birthday Bash', now());

-- INSERT all the possible seat permutations for the first event
INSERT INTO booking.event_venue_seats (eventid,seatid)
SELECT eventid, seatid
FROM booking.seats
INNER JOIN booking.venue
  USING (venueid)
INNER JOIN booking.event
  ON (eventid = 1);

And now for the Booking Transaction

Now we have the eventid hard coded to one, you should set this to whatever event you want, customerid and txnid essentially make the seat reserved and tell you who did it. The FOR UPDATE is key. Those rows are locked during the update.

UPDATE booking.event_venue_seats
SET customerid = 1,
  txnid = 1
FROM (
  SELECT eventid, seatid
  FROM booking.event_venue_seats
  JOIN booking.seats
    USING (seatid)
  INNER JOIN booking.venue
    USING (venueid)
  INNER JOIN booking.event
    USING (eventid)
  WHERE txnid IS NULL
    AND customerid IS NULL
    -- for which event
    AND eventid = 1
  OFFSET 0 ROWS
  -- how many seats do you want? (they're all locked)
  FETCH NEXT 7 ROWS ONLY
  FOR UPDATE
) AS t
WHERE
  event_venue_seats.seatid = t.seatid
  AND event_venue_seats.eventid = t.eventid;

Updates

For timed reservations

  You would use a timed reservation. Like when you buy tickets for a concert, you have M minutes to confirm the booking, or someone else gets the chance – Neil McGuigan 19 mins ago

What you would do here is set the booking.event_venue_seats.txnid as

txnid int REFERENCES transactions ON DELETE SET NULL

The second the user reserves the seet, the UPDATE puts in the txnid. Your transaction table looks something like this.

CREATE TABLE transactions (
  txnid       serial PRIMARY KEY,
  txn_start   timestamp DEFAULT now(),
  txn_expire  timestamp DEFAULT now() + '5 minutes'
);

Then in every minute you run

DELETE FROM transactions
WHERE txn_expire < now()

You can prompt the user to extend the timer when nearing expiration. Or, just let it delete the txnid and cascade down freeing up the seats.


What's the difference between a CTE and a Temp Table? What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other?

CTE

WITH cte (Column1, Column2, Column3)
AS
(
    SELECT Column1, Column2, Column3
    FROM SomeTable
)

SELECT * FROM cte

Temp Table

SELECT Column1, Column2, Column3
INTO #tmpTable
FROM SomeTable

SELECT * FROM #tmpTable

This is pretty broad, but I'll give you as general an answer as I can.

CTEs...

Are unindexable (but can use existing indexes on referenced objects)
Cannot have constraints
Are essentially disposable VIEWs
Persist only until the next query is run
Can be recursive
Do not have dedicated stats (rely on stats on the underlying objects)

#Temp Tables...

Are real materialized tables that exist in tempdb
Can be indexed
Can have constraints
Persist for the life of the current CONNECTION
Can be referenced by other queries or subprocedures
Have dedicated stats generated by the engine

As far as when to use each, they have very different use cases.  If you will have a very large result set, or need to refer to it more than once, put it in a #temp table.  If it needs to be recursive, is disposable, or is just to simplify something logically, a CTE is preferred.

Also, a CTE should never be used for performance.  You will almost never speed things up by using a CTE, because, again, it's just a disposable view.  You can do some neat things with them but speeding up a query isn't really one of them.


Error when upgrading SQL Server from 2012 (sp3) to 2014 I am attempting to upgrade MS SQL Server 2012 (sp3) to SQL Server 2014.

At the end of the upgrade several components fail with an error that says,

  "Error details: Error installing SQL Server Database Engine Services
  Instance Features Failed to create a new folder
  'H:\MSSQL11_MSSQLSERVER_Backup_Dir'. The specified path is invalid
  (for example, it is on an unmapped drive). Error code: 0x84CF000E"

This server does not have an H:\ drive. As far as I know, it never did have an H drive. How can I correct this?

Run this

EXEC  master.dbo.xp_instance_regread
 N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory'

to see what your default backup directory is set to.

This can also be viewed in Management Studio by right clicking on your instance, selecting Properties and going to the Database Settings tab. It will be near the bottom under Database default locations. If your default backup directory is set to 'H:\MSSQL11_MSSQLSERVER_Backup_Dir', changing it will help.


SQL Server : delete row trigger I have these 3 tables:

ConstructorEmployee

EID (employee ID)  as primary key and other key.

Project

PID as primary key and other keys.

ProjectConstructorEmployee

PID and EID as foreign keys to the other tables.

Each ConstructorEmployee can work on several Projects. I need to create a trigger that if project has been deleted, I need to delete all ConstructorEmployee  that worked only on this project. I need to delete them from the ConstructorEmployee table.

I'm working on SQL Server 2017.

I assume your table schema is similar to next one:

CREATE TABLE ConstructorEmployee
(
    EID int PRIMARY KEY
);

CREATE TABLE Project
(
    PID int PRIMARY KEY
);

CREATE TABLE ProjectConstructorEmployee
(
    PID int,
    EID int,
    CONSTRAINT PK_PCE PRIMARY KEY(PID, EID),
    CONSTRAINT FK_P FOREIGN KEY (PID) REFERENCES Project (PID),
    CONSTRAINT FK_CE FOREIGN KEY (EID) REFERENCES ConstructorEmployee (EID)
);
GO

With two FOREIGN KEY on ProjectConstructorEmployee table.

Now let me add some data 3 employes and 3 projects, but Employee=1 has worked only on the first Project.

INSERT INTO ConstructorEmployee VALUES (1), (2),(3);
GO

INSERT INTO Project VALUES (1), (2), (3);
GO

-- EID = 1 worked in PID 1 only
INSERT INTO ProjectConstructorEmployee VALUES
(1, 1), (1, 2), (2, 2), (2, 3), (3, 2), (3, 3);
GO

Next query returns a list of employees that has worked in only one project, in this case PID=1

-- Employees working on PID=1 that didn't work in any other project
SELECT EID
FROM   ProjectConstructorEmployee
WHERE  EID IN (SELECT EID FROM ProjectConstructorEmployee WHERE PID=1)
GROUP BY EID
HAVING COUNT(*) = 1
GO

| EID |
| --: |
|   1 |

  I need to create a trigger that...

No, don't use a trigger, let me suggest to use an STORED PROCEDURE, it's always more clear for your and for any further developer. Or if you want to use a trigger, use a BEFORE DELETE trigger that calls this procedure.

CREATE PROCEDURE DeleteProject(@PID int)
AS
BEGIN
    BEGIN TRY

        DECLARE @employee TABLE (EID int);

        BEGIN TRANSACTION;

        -- save a list of employees that only worked in this proejct
        -- due to FOREIGN KEYS you can't delete ConstructorEmployee table
        -- until you have deleted the other both.
        INSERT INTO @employee
        SELECT EID
        FROM   ProjectConstructorEmployee
        WHERE  EID IN (SELECT EID
                       FROM ProjectConstructorEmployee
                       WHERE PID = @PID)
        GROUP BY EID
        HAVING COUNT(*) = 1;

        DELETE FROM ProjectConstructorEmployee
        WHERE  PID = @PID;

        DELETE FROM Project
        WHERE  PID = @PID;

        DELETE FROM ConstructorEmployee
        WHERE EID IN (SELECT EID FROM @employee);

        IF @@TRANCOUNT > 0
            COMMIT TRANSACTION;

    END TRY
    BEGIN CATCH

        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- Your error handler

        THROW;

    END CATCH
END
GO

Ok, let me try it by deleting projects 1 and 2:

EXEC DeleteProject @PID = 1;
EXEC DeleteProject @PID = 2;

SELECT * FROM ConstructorEmployee;
SELECT * FROM Project;
SELECT * FROM ProjectConstructorEmployee;
GO

Remaining employees:

| EID |
| --: |
|   2 |
|   3 |

Projects:

| PID |
| --: |
|   3 |

and ProjectsEmployees:

PID | EID
--: | --:
  3 |   2
  3 |   3

db<>fiddle here

But...are you sure you want to delete all this information?

You will lost all the Project's history, employees and projects will disappear of your database. Maybe you could set a flag like Active(Yes/No) a let this information available for whichever want to recover later.


Can someone guide me how to compare tables with SSIS I have two tables in two different databases in two different servers.

Both tables have an orderid column, and a given value for orderid references the same entity in both tables.

I want to check whether each value for orderid in either table has a matching entry in the other table, and write a record to table3 based on this information. table3 has the same basic columns as table2, with the additon of a status column.

Server1
Database1
Table 1
orderidproductpricequoted pricediscounted price

Server2
Database 2
Table 2
orderidcreatedonmodifiedon

Table 3
orderidcreatedonmodifiedonstatus

The status can be matching,not matching,only in table 1,only in table 2,error. If in both orderid is same the status will be matching, if it's there only in table 1 it becomes only in table 1, if found only in table
 2 it's only in table 2 not sure whether not matching & error are relevant in this scenario.

I have crafted an sql for it, just let me know how to use it in SSIS

insert into Table3 (orderid,createdon,modifiedon,status)
select
    COALESCE(t1.orderid,t2.orderid) as orderid,
    COALESCE(t1.CREATE_DATE,t2.CREATE_DATE) as createdon,
    COALESCE(t1.UPDATE_DATE,t2.UPDATE_DATE) as modifiedon,
    case when t1.orderid is null then 'only in table 2'
         when t2.orderid is null then 'only in table 1'
         when t1.orderid = t2.orderid then 'matching'
         when t1.orderid != t2.orderid then 'not matching'
         else 'error'
    from Table1 t1
    full outer join Table2 t2
    on t1.orderid = t2.orderid

If you really want to do this in SSIS, add 2 sources with the SQL from each table and link them to a Merge transformation, which will do a FULL JOIN (select it from the drop down) operation on your columns orderid between the 2 data sets. SSIS needs both data sources to be ordered by the column you are joining so either add an ORDER BY orderID on both your sources and mark the column orderID as SORT on the advanced editor (so the merge knows that data is sorted by that column), or add a custom Sort transformation between the source and the merge on each flow to explicitly order by this column.

Then you can add a Derived Column transformation right after the merge with an expression to determine if either:

OrderID1 is NULL (record is from data set 2).
OrderID2 is NULL (record is from data set 1).
OrderID exists on both data sets.

If you also need to know if data changed when both order ID's match, then you will have to write an awful expression comparing each column against each other, making sure to consider NULL values. SSIS isn't good to build expressions (they are hard to read and even harder to maintain), that's why I always recommend to do this directly on SQL.

Finally link the derived column to your destination database.

It's worth mentioning that from your FULL JOIN example, the following CASE scenario:

case
    when t1.orderid is null then 'only in table 2'
    when t2.orderid is null then 'only in table 1'
    when t1.orderid = t2.orderid then 'matching'
    when t1.orderid != t2.orderid then 'not matching'
    else 'error'
end

Can be simplified into:

case
    when t1.orderid is null then 'only in table 2'
    when t2.orderid is null then 'only in table 1'
    else 'matching'
end

Since you are joining by orderid, either they don't join on each side (option 1 & 2) or they match exactly (else), there is no way that they can join but differ in value.


Set the Deadlock Priority to Lowest possible value, but my script is still NOT always the victim in deadlock situations I have below two queries on Production:

Query # 1: Script that deletes older historical data from core table "Table1" based on "Date" column

There are ~20 tables that point to "Table1" by foreign key with cascade rule, so when you delete from "Table1", data is deleted from ~20 other tables, too
Script deletes data in batches 50 rows at a time with 1 second interval  between batches
Script does NOT cause Exclusive locks to escalate to table-level lock, when batch is only 50 rows; it only places row/page level (X) locks

Query # 2: Production Application that pulls data from "Table1", "Table2", "Table3" and some other tables

This is heavy read query, that places Shared locks on tables. It does not do any insert/update/delete
Problem:
Sometimes, deadlock occurs when Query # 2 (Prod app) reads data, and I simultaneously run Query # 1 (script deleting older data) in SSMS. Query # 2 is always a victim, as I understand it is because it is NOT doing any changes to transaction log, while Query # 1 does
Trying to resolve this, I've added set deadlock_priority -10 to Query # 1, hoping that it will make Query # 1 victim in any possible deadlocks
First it seemed it worked, Query # 1 became victim in deadlock situations, throwing error 1205 when deleting some of the batches.
BUT - recently I discovered that sometimes Query # 2 still becomes a victim
Question:
How is this possible that Query # 2 sometimes becomes a victim even after I've set deadlock_priority -10 for Query # 1 ?
Is there a way to fix it and make sure Query # 1 will be the victim in 100 % cases ?
Update: added graph below

You could attempt to solve the issue by changing the transaction isolation level. Specifically enabling Snapshot isolation and setting the Read Committed Snapshot so that reads and writes won't interfere with each other. You could also just update the production query to use snapshot isolation without updating the default database isolation level.
Be careful though and thoroughly test this because it will increase the traffic on tempdb as row snapshots are written. Setting Read Committed Snapshot is also a default behavior change across the whole database which needs regression testing to verify there are no unexpected side effects.
A better explanation on this option is available from Microsoft here: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server


file not found - how to remove its configuration? I run oracle 10.2.0.5.

I have the following:

SQL> select file#, status, error, recover, tablespace_name, name
  2  from v$datafile_header
  3  where recover='YES'
  4  or (RECOVER IS NULL AND ERROR IS NOT NULL);

     FILE# STATUS
---------- -------
ERROR                                                             REC
----------------------------------------------------------------- ---
TABLESPACE_NAME
------------------------------
NAME
--------------------------------------------------------------------------------
        15 OFFLINE
FILE NOT FOUND

It's the remnant of a tablespace which was used for some synchronization using streams.

How can I "delete" this file from oracle's memory?

Thanks

Shut the database down. Start it up again in mount mode:

connect / as sysdba;
startup mount;

Drop the datafile:

ALTER DATABASE DATAFILE '/full/path/of/file.dbf' OFFLINE DROP;

Open the DB:

alter database open;

Drop the tablespace:

DROP TABLESPACE <TS Name> INCLUDING CONTENTS;


Join query with IN condition and Group_concat I have an issue to get concatenated text from below two tables(product_to_category & category_description)

I need mysql query to get output as "Electronics,Electronics Component Connectors,Electronics Accessories" from category_description table's name column.That query will compare category_id in these tables with where condition as product_id = 276.

I tried this query,but it returns empty result -

 SELECT GROUP_CONCAT(description SEPARATOR ',')
  FROM `ocrk_category_description`
 WHERE `category_id` IN (SELECT GROUP_CONCAT(category_id SEPARATOR ',') as `category_id`
                           FROM `ocrk_product_to_category`
                          WHERE `product_id` = 276
                        )

Any ideas? i can provide you more information if need.

I'm not 100% sure if I understand the question correct, but i'll try some solution!:

    SELECT a.product_id, group_concat(distinct b.name)
    FROM product_to_category a
    JOIN category_description b
    ON a.category_id = b.category_id
    GROUP BY a.product_id;

I hope this is what you were looking for...

Cheers,
Olaf


Simplify subqueries with join I'm trying to create a report in SQL Server Reporting Services that shows the availability of multiple sites. The sites are monitored by a tool that uses a SQL Server 2012 database to store the data.
I've created a query that shows me the results and I'm wondering if I can rewrite this query in a single select without the subqueries.

My actual query looks like

    SELECT
         t1.CHECKID
        ,t1.CHECKNAME
        ,t2.Startdate
        ,t2.Enddate
        ,t2.StatusID
        ,t2.Month
        ,t2.Year
    FROM
    (SELECT CHECKID, CHECKNAME FROM CHECKS ) AS t1
    LEFT JOIN
    (SELECT
            STATUSCHANGES.CHECKID
            , STATUSCHANGES.DATETIME AS Startdate
            , MIN(STATUSCHANGES_1.DATETIME) AS Enddate
            , STATUSCHANGES.STATID AS StatusID
            , CHECKS.CHECKNAME
            , STATUS.STATUSNAME
            , DATEPART(MONTH,STATUSCHANGES.DATETIME) as Month
            , DATEPART(YEAR,STATUSCHANGES.DATETIME) as Year
        FROM
            STATUSCHANGES
            INNER JOIN STATUSCHANGES AS STATUSCHANGES_1
                ON STATUSCHANGES.CHECKID = STATUSCHANGES_1.CHECKID
                AND STATUSCHANGES.DATETIME < STATUSCHANGES_1.DATETIME
            INNER JOIN CHECKS ON STATUSCHANGES_1.CHECKID = CHECKS.CHECKID
            INNER JOIN STATUS ON STATUSCHANGES.STATID = STATUS.STATUSID
        GROUP BY
            STATUSCHANGES.CHECKID,
            STATUSCHANGES.DATETIME,
            STATUSCHANGES.STATID,
            CHECKS.CHECKNAME,
            STATUS.STATUSNAME
        HAVING
            (STATUSCHANGES.STATID IN (3))
            AND DATEPART(Q,STATUSCHANGES.DATETIME) = 2
        ) AS t2
        ON t1.CHECKID = t2.CHECKID
        ORDER BY t2.Startdate

Is it possible to integrate the first subquery t1 in the from clause of subquery t2?

It's hard without the tables and data to test on but I believe this will work for you.  I added the subquery T1 as a RIGHT OUTER JOIN to T2, since I've moved the table to the "right" side of the query.  I moved it to the right to avoid any issues with LEFT OUTER JOINing the whole rest of the query.  I aliased it as RCHECKS (RIGHT OUTER JOIN version of CHECKS).

Also why do you have a HAVING clause?  Those particular statements will work just fine in the WHERE clause and save you quite a bit of time.  The HAVING clause is specifically for checks against aggregates.  My guess is if you move the checks into the WHERE clause you query will go much faster.  I've included a modified version of the query at the bottom using a WHERE clause and no subquery at all.

SELECT
     t2.CHECKID
    ,t2.CHECKNAME
    ,t2.Startdate
    ,t2.Enddate
    ,t2.StatusID
    ,t2.Month
    ,t2.Year
FROM
(SELECT
        RCHECKS.CHECKID
        , RCHECKS.CHECKNAME
        , STATUSCHANGES.DATETIME AS Startdate
        , MIN(STATUSCHANGES_1.DATETIME) AS Enddate
        , STATUSCHANGES.STATID AS StatusID
        , CHECKS.CHECKNAME
        , STATUS.STATUSNAME
        , DATEPART(MONTH,STATUSCHANGES.DATETIME) as Month
        , DATEPART(YEAR,STATUSCHANGES.DATETIME) as Year
    FROM STATUSCHANGES
    INNER JOIN STATUSCHANGES AS STATUSCHANGES_1
        ON STATUSCHANGES.CHECKID = STATUSCHANGES_1.CHECKID
        AND STATUSCHANGES.DATETIME < STATUSCHANGES_1.DATETIME
    INNER JOIN CHECKS
        ON STATUSCHANGES_1.CHECKID = CHECKS.CHECKID
    INNER JOIN STATUS
        ON STATUSCHANGES.STATID = STATUS.STATUSID
    RIGHT OUTER JOIN CHECKS RCHECKS
        ON CHECKS.CHECKID = STATUSCHANGES.CHECKID
    GROUP BY
        RCHECKS.CHECKID,
        RCHECKS.CHECKNAME,
        STATUSCHANGES.DATETIME,
        STATUSCHANGES.STATID,
        CHECKS.CHECKNAME,
        STATUS.STATUSNAME
    HAVING
        (STATUSCHANGES.STATID IN (3))
        AND DATEPART(Q,STATUSCHANGES.DATETIME) = 2
    ) AS t2
    ORDER BY t2.Startdate

    SELECT
        RCHECKS.CHECKID
        , RCHECKS.CHECKNAME
        , STATUSCHANGES.DATETIME AS Startdate
        , MIN(STATUSCHANGES_1.DATETIME) AS Enddate
        , STATUSCHANGES.STATID AS StatusID
        , DATEPART(MONTH,STATUSCHANGES.DATETIME) as Month
        , DATEPART(YEAR,STATUSCHANGES.DATETIME) as Year
    FROM STATUSCHANGES
    INNER JOIN STATUSCHANGES AS STATUSCHANGES_1
        ON STATUSCHANGES.CHECKID = STATUSCHANGES_1.CHECKID
        AND STATUSCHANGES.DATETIME < STATUSCHANGES_1.DATETIME
    INNER JOIN CHECKS
        ON STATUSCHANGES_1.CHECKID = CHECKS.CHECKID
    INNER JOIN STATUS
        ON STATUSCHANGES.STATID = STATUS.STATUSID
    RIGHT OUTER JOIN CHECKS RCHECKS
        ON CHECKS.CHECKID = STATUSCHANGES.CHECKID
    WHERE STATUSCHANGES.STATID IN (3)
      AND DATEPART(Q,STATUSCHANGES.DATETIME) = 2
    GROUP BY
        RCHECKS.CHECKID,
        RCHECKS.CHECKNAME,
        STATUSCHANGES.DATETIME,
        STATUSCHANGES.STATID,
        CHECKS.CHECKNAME,
        STATUS.STATUSNAME
    ORDER BY STATUSCHANGES.DATETIME


ORA-01950 error during the execution of insert statement SQL> insert into DEPT values(101, 'RAMESH');
insert into DEPT values(101, 'RAMESH')
        *

  ERROR at line 1:

  ORA-01950: no privileges on tablespace 'USERS'

I cant sort the above problem?

This is already asked on ORA-01950: no privileges on tablespace 'USERS' [closed]. It was answered as:

You cannot insert data because you have a quota of 0 on the tablespace. To fix this, run

ALTER USER <user> quota unlimited on <tablespace name>;

or

ALTER USER <user> quota 100M on <tablespace name>;

as a DBA user (depending on how much space you need / want to grant).


Sybase ASE to Microsoft SQL Server Replication I am currently working on several projects to migrate legacy applications from a Sybase ASE (12.5 and 15.0) to a Microsoft SQL Server 2014 platform.

There will be a need to replicate data in both directions while the old and new applications are being developed.

I was looking for advice regarding how others have approached this problem.

Some ideas we're looking into:

Sybase Replication Server (Expensive)
Third party Apps (Not sure if it will be stable?)
Custom Stored Procedures in Sybase for Update, Insert and Delete and configure those to be called when the data comes in. (Reliability and difficulty of supporting)

We ended up using the SymmetricDS application and have been moving applications over to MSSQL gradually. So far, so good! Thanks for the help everyone.


Why does SSAS keep restarting every few seconds? We have a SQL Server 2005 Analysis Server which began restarting this weekend, seemingly without any external changes.

The current state of the server is that as long as the SSAS service is enabled, it will keep restarting. If the service is manually disabled and then stopped, the service will no longer cycle. But as soon as it is enabled, the service will begin to cycle again.

Internet searching finds a few examples of this behavior, but without a resolution which seems applicable. See, e.g.: Service Level Accounts - SQL 2005 SSAS - Error connecting to OLAP. See also, e.g.: I Can’t Connect to Analysis Services in SSMS...

Brent Ozar reported a similar problem on LessThanDot, but there was no resolution on the thread and the last entry was at Fri Mar 27, 2009 4:08 pm.

This is a snippet of the msmdsrv.log file:

(10/6/2014 3:21:43 PM) Message: The flight recorder was started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)
(10/6/2014 3:21:43 PM) Message: Service started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)
(10/6/2014 3:22:40 PM) Message: Service stopped. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210001)
(10/6/2014 3:22:45 PM) Message: The flight recorder was started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)
(10/6/2014 3:22:45 PM) Message: Service started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)
(10/6/2014 3:23:41 PM) Message: Service stopped. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210001)
(10/6/2014 3:23:47 PM) Message: The flight recorder was started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)
(10/6/2014 3:23:47 PM) Message: Service started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)
(10/6/2014 3:24:41 PM) Message: Service stopped. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210001)
(10/6/2014 3:24:47 PM) Message: The flight recorder was started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)
(10/6/2014 3:24:47 PM) Message: Service started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)

The event viewer for system log, over and over, has:

"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a stop control."
"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a start control."
"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a stop control."
"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a start control."
"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a stop control."
"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a start control."
"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a stop control."
etc....

There are no SQLDmprNNNN.mdmp files which seem to be created. There is no other error logging that we are aware of.

How can we find out what's wrong? Is there a way we can tell if any third party services or processes are cycling the SSAS service?

We have also posted this question on SQL Server Central.

We never found out what was causing the service to stop, but we were able to prevent it from happening. We put a Deny on the Stop permission for SSAS service for the SQL Server (not SSAS) service account user.

So it seems something acting as the SQL Server's service account user was issuing the Stop command for the SSAS account. This was a while ago, and we've since switched servers, and we stopped digging to figure out what was really happening. So it will remain a mystery for the ages.


pgAdmin 4 v2.1 download - could not verify .sig file I downloaded the installer and .sig file for the latest pgAdmin 4 v2.1 from their website, given the recent malware/security issues I thought best to verify the installer using Kleopatra (part of Gpg4win) before doing anything, but got this message:

I have emailed the maintainer as listed on the download page but yet to hear back from him. Does anyone know if this is a cause for concern? I will update this post if/when I hear back from the maintainer.

works now after the maintainer sent me his public key, which I had to certify first, once that was all set up I was able to verify the .sig file.


mongodb memory leak or big collection? mongo 2.4.6 x64
RAM 24 GB

With normal work memory consume around 10GB.
After that we started read data from db (create archive raw data) memory consuming up to 90% of all memory trough 5day. And go forfard by 0.1% by day. Is it normal situation and what happening when oom-killer come ?

mongotop
insert  query update delete getmore command flushes mapped  vsize res faults  locked db idx miss %     qr|qw   ar|aw  netIn netOut conn     set repl       time
    *0     *0     *0     *0       0     2|0       0 21513g 43028g 1.49g      0     .:0.1%          0       0|0     0|0   190b 3k    31 replica  PRI   02:25:08
    *0     *0     *0     *0       0     1|0       0 21513g 43028g 1.49g      0     .:0.1%          0       0|0     0|0    62b 3k    31 replica  PRI   02:25:09
    *0     *0     *0     *0       0     2|0       0 21513g 43028g 1.49g      0     .:0.1%          0       0|0     0|0   190b 3k    31 replica  PRI   02:25:10
    *0     *0     *0     *0       0     1|0       0 21513g 43028g 1.49g      0     .:0.1%          0       0|0     0|0    62b 3k    31 replica  PRI   02:25:11

free -m
             total       used       free     shared    buffers cached
Mem:         24031      23835        195          0         33 1542
-/+ buffers/cache:      22259       1771
Swap:          511          4        507

Collection = 20TB

This has been asked and answered many times, so there is even a couple of FAQ sections on it.  First you will need to understand the difference between resident memory usage and the cache being full (very different things), then you need to look at how memory mapped files behave in general.  Here are a couple of starting points from the MongoDB Docs:

http://docs.mongodb.org/manual/faq/fundamentals/#does-mongodb-require-a-lot-of-ram
http://docs.mongodb.org/manual/faq/diagnostics/#memory-diagnostics

Short version, no it is not a memory leak, and yes it is expected behavior over time when the data set exceeds available physical memory.


Expiration date on a user in a specific database Some of our developers needs access to some of the databases in production for a short period. Is there a way for me to set an expiration date on a user in a specific database?
Just to clarify I do not want the login to expire, just the user in a specific database.

USE [Database1]
CREATE USER [Developer1] FOR LOGIN [Developer1] WITH expirydate = '2018.01.01'
--Add some roles
GO
USE [Database2]
CREATE USER [Developer1] FOR LOGIN [Developer1] WITH expirydate = '2018.02.02'
--Add some roles

MSSQL 2014, 2016 & 2017

To the best of my knowledge, there's no way to set an expiration date on a database user.

I believe the best solution would be what Edgar Allan Bayron suggested:

  you can set a sql server agent job to run daily which compares with today's date. If that date is equal to the "expiry date", then execute a query where it will disable the specific user

Here's a slightly more fleshed out version of that idea:

Create a new database for administrative use (if you don't already have one). We'll assume it's called dba_utilities.
In the dba_utilities database, create a table named db_user_expiration, with columns database (nvarchar(128)), username (nvarchar(128)), and expiration_date (datetime) (plus an id, integer, IDENTITY). You may want to have a removed bit or char(1) column as well.
In the same database, create a procedure to:

identify rows from the table where the user account has expired;
for each row, create a dynamic SQL statement to DROP the user from the indicated database
If the drop is successful, remove the row from the table (or, mark the row as removed).

If there will only be a few of these at a time, I'd simply use a STATIC LOCAL cursor to loop through the records; if the numbers will be very large, you could generate a single SQL statement to do all the accounts at once, but that's more complicated.

NOTE: If you don't create your users with a default schema, then they'll wind up having a schema with the same name as the user created for them. The schema must either be dropped or moved to a different owner before the user can be dropped. Unless your users need a schema of their own, I'd assign them to a default schema owned by the database owner ([dbo] may work well, in most cases), so this is not a problem.
Run this procedure from a SQL Server job as often as you like (from once a day to once an hour, if appropriate). The expiration_date column I suggest is a datetime, so you can specify the time of day when the expiration should take effect, and the next run after that time should see the user dropped.


Anyone want to try a script bash or python? I need to automate master binary log purging to occur so that purging never goes back further than the last slave has the updated info for.

These slaves are mobile and there are 3 of them, and sync whenever the users get around to it.

I can do this manually but how to set this up in a script eludes me.

Any ideas?

DISCLAIMER : Not a Python Programmer

I can give you a generic algorithm

Let's say you have 4 DB Servers

replicator@10.1.2.% is the Replication User
1 Master (10.1.2.10)
3 Slaves (IP Addresses : 10.1.2.20,10.1.2.30,10.1.2.40)

STEP 01) Run this query

SELECT host FROM information_schema.processlist WHERE user = 'replicator';

Collect host columns into a list.

STEP 02) For each host collected into the list, run

SHOW SLAVE STATUS;

and retrieve the Relay_Master_Log_File column. Collect these in an array.

I wrote about why to pick that field and not Read_Master_Log_File

Aug 02, 2012 : Deleting bin logs in replication environment
May 26, 2012 : Trying to understand SHOW SLAVE STATUS\G
Jan 24, 2012 : Mysql master-master replication auto flush old logs
Jan 03, 2012 : what are the conditions under which mysql replication might break?

STEP 03) Sort the array of Relay_Master_Log_File names. Select the first entry.

Suppose you collected

mysql-bin.00124
mysql-bin.00123
mysql-bin.00125

After sorting, you would pick mysql-bin.00123

STEP 04) Purge binary logs on the Master using the select binary log

PURGE BINARY LOGS TO 'mysql-bin.00123';

That's it.

Give it a Try !!!


Counting rows from a subquery Simple: I would like to count the number of rows from the sub-query. Note that status is whether the host is online or not.

Bad code

SELECT COUNT(ip_address) FROM `ports` (
    SELECT DISTINCT ip_address FROM `ports` WHERE status IS TRUE
)

Explained

The first query, when run on its own returns this:

SELECT DISTINCT ip_address FROM `ports` WHERE status IS TRUE

ip_address
192.168.1.1
192.168.1.2
192.168.1.248
192.168.1.251
192.168.1.254

The second query run on its own returns this:

SELECT COUNT(ip_address) FROM `ports`

17

Question

I would like to know how to count that list of 5 IP addresses.

I have been looking online at possible solutions to this simple problem and just getting frustrated, so thought I'd ask the experts.

To answer your immediate question, how to count rows of a subquery, the syntax is as follows:

SELECT COUNT(*) FROM (subquery) AS some_name;

The subquery should immediately follow the FROM keyword. (In MySQL it is also mandatory to assign a name to a subquery of this kind (it is actually called a derived table), which is why you can see the AS some_name following it.) The way you have written it, MySQL interprets your script as two independent queries, that is why you are getting two result sets.

So, since the subquery in your case is

SELECT DISTINCT ip_address FROM `ports` WHERE status IS TRUE

the complete query would look like this:

SELECT COUNT(*) FROM (
    SELECT DISTINCT ip_address FROM `ports` WHERE status IS TRUE
) AS derived;

But, as Julien has suggested, you can rewrite your query just like this:

SELECT COUNT(DISTINCT ip_address) FROM `ports` WHERE status IS TRUE;

This way you do not need a subquery/derived table at all, because with the DISTINCT keyword the COUNT function will count only distinct occurrences of ip_address in the ports table.


How to determine consumption of data for archiving in sql server? I was told that it could be possible to analyze the consumption of a given row. I already know I can use sys.dm_db_index_usage_stats to see the reads / writes on a given index (thus the table) but I need to know for the row level.

For example: The invoice table has 5 million rows, spread over 7 years. If I can determine that no rows are getting read for the first 5 years, this data will be archived. Now, if i have 100 tables that contain 7 years data, maybe archiving the first 5 years is good enough for the invoices but not for the other 99 tables.

Any way I can determine the data consumption / usage of rows for specific archiving?

Analyzing the usage pattern of the server via profiler and/or extended events is going to be the way to go.  Or ask the business unit what data they want archived.  If they say that they don't want any data archived, then the conversation is over.


Hide schemas from users in MSSQL I'm creating a database with a few different schemas.
99% of users connect using Excel, and when they do they see all the system stuff:

This just confuses the users and I would like to configure SQL Server so that they only see UK_data and UK_reports.
So far I tried denying select on the things that I want to hide and only allowing select on the UK_ schemas, but it didn't work.
Is there a way to hide them from users?
Thanks!

I maybe found two solution for you:

HIDE

Hide Table "Person.Address"
EXEC sp_addextendedproperty
@name = N'microsoft_database_tools_support',
@value = 'Hide',
@level0type = N'Schema', @level0name = 'Person',
@level1type = N'Table', @level1name = 'Address';
GO

Show Table "Person.Address"
EXEC sp_dropextendedproperty
@name = N'microsoft_database_tools_support',
@level0type = N'Schema', @level0name = 'Person',
@level1type = N'Table', @level1name = 'Address';
GO

DENY

`DENY VIEW DEFINITION ON Schema.Table TO UserName;`

Now UserName won’t be abe to see Table in Object Explorer. In Fact, they won’t be able to see the table in sys.tables or INFORMATION_SCHEMA.TABLES.
You can Use the DENY keyword to deny certain Users and REVOKE to Remove the existing permission.


Moving Transaction Data To Another Database For Reporting Purpose We got a requirement from our developer group, to do the followings:

Frequently (every 30mins) move the transaction data out of live database to another database
Secondary database will be utilize for Ad-Hoc query and Reporting
They do not want to DELETE data from this secondary reporting database, if data gets deleted from the live database

Our database server is on SQL Server 2012 Enterprise Edition.

This will make sure, live data are not been queried by the end users causing blocking issue. Developer will be working on getting the Analytic data in near future, but they wanted to implement something quickly to make live data as small as possible.

What are the recommendations to achieve this?

Thank you,

HP

Since you have this requirement,

  They do not want to DELETE data from this secondary reporting database, if data gets deleted from the live database

AlwaysON gets eliminated.

I would suggest you to setup Transactional replication.

  Frequently (every 30mins) move the transaction data out of live database to another database

You can schedule the log reader agent frequency to 30 mins (or as per your needs).

As a side note, I will suggest you to read this article by Kendra Little. It explains T-Rep and compares it with other technologies (AlwaysON, CDC, etc).


Multiple instances in Oracle I'm really confuse with the term instance and database. So, my first question is how database and Instance are related in Oracle, I mean, whether an instance is associated with one/multiple database(s) or a database associated with single/multiple instance(s)?

And, my second question is, one of my friend says we can't have multiple instances in oracle. is it really valid a statement ?

The documentation explains it very well:

  An Oracle database server consists of an Oracle database and an Oracle
  instance. Every time a database is started, a system global area (SGA)
  is allocated and Oracle background processes are started. The
  combination of the background processes and memory buffers is called
  an Oracle instance.

An oracle database is the physical files that make up the database itself (control files, data files etc) - Documentation link.

A database is associated with one or more instances, with multiple instances making a RAC setup - Documentation link. As far as your second question is concerned, multiple instances of a single database is a RAC setup.


Retrieving cached plans fails due to cached objects of database Mirror I'm trying to retrieve cached execution plans for a specific database object, but this fails because of cached plans which belong to a mirrored database in MIRROR state.

The below query, without any additional WHERE clauses is failing with the following SQL error:

Msg 954, Level 14, State 1, Line 1
The database "DatabaseName" cannot be opened. It is acting as a mirror database.

SELECT  *
FROM    sys.dm_exec_query_stats AS deqs
CROSS APPLY     sys.dm_exec_query_plan(deqs.plan_handle) AS deqp

I suspect what happens is the sys.dm_exec_query_plan function is first trying to parse all the plan handles from the cache, but fails on cached objects for a mirrored database.

Does anyone know if there are any ways to get around this, T-SQL wise?

Off course I could execute DBCC FREEPROCCACHE to clear the cache, however I'm hoping for other solutions. I'm a bit amazed this function is not discarding any objects from mirrored databases while trying to parse the plans.

The sys.dm_exec_query_plan has a column returned for dbid, you could just filter on that for the database that is being mirrored.

Or, you could get fancy and nest a sub-query that filters for any dbid that is in a mirrored state by going out to the sys.database_mirroring DMV.


PostgreSQL table lock for certain select wheres Let's say I have a postgres database that tracks cars purchased by users. Every user can own as many vehicles as she wants, but only one for each make.

-----------------
|     USER      |
|---------------|
| id |   name   |
|---------------|
| 1  |   Adam   |
| 2  |   Julia  |
-----------------

-----------------
|     MAKE      |
|---------------|
| id |   name   |
|---------------|
| 1  |   Volvo  |
| 2  |   Opel   |
-----------------

--------------------------
|       PURCHASE         |
|----|---------|---------|
| id | user_id | make_id |
|----|---------|---------|
| 1  |    1    |    1    |
| 2  |    1    |    2    |
| 3  |    2    |    2    |
--------------------------

Purchase uniqueness is restricted on a database level by a "unique" constraint (ALTER TABLE purchase ADD UNIQUE (user_id, make_id)).

When user tries to purchase a new vehicle my application first checks (in a db transaction) whether the user/make combination is possible, then inserts a new row

SELECT COUNT(1) FROM purchase WHERE user_id = 2 AND make_id = 1;
// assuming the previous query found no records
INSERT INTO purchase (user_id, make_id) VALUES (2, 1);

The thing is, in a rare race-condition situation, when there are two concurrent requests, this opens a possibility that two separate app instances will get the information that the user/make combination does not exist and will try adding a new row to the database. The second one will obviously fail due to uniqueness restraint on the purchase table, however I was wondering if it was possible for a transaction to create a lock on a postgres table that would prevent any other transactions from making selects for a specific WHERE cause (in my situation - WHERE user_id = 2)? I cannot block the whole table as it would have a horrific impact on the app performance. Just lock selects for some (may be non-existing) rows. How do I do that?

I think this could be achieved with an advisory lock

Before doing the insert every transaction would acquire an advisory lock e.g. for the user_id/make_id combination, so any subsequent transaction that tries to do something for the same user/make, would have to wait:

-- this waits until the lock can be granted
select pg_advisory_lock(2,1);

SELECT COUNT(*)
FROM purchase
WHERE user_id = 2
  AND make_id = 1;

INSERT INTO purchase (user_id, make_id) VALUES (2, 1);

You can also use pg_try_advisory_lock() which returns immediately with a boolean result whether the lock could be granted or not, if you don't want the session to wait for the lock (but show an error message "already locked" or something similar).

The lock is automatically released when you commit (or rollback) your transaction (so you have to make sure to turn off autocommit and wrap all statements in a single transaction)

As deszo pointed out: it's important that all involved parts of the code stick the to the contract that an advisory lock is required. The approach using an advisory lock does not guarantee in any way that conflicts may occur.


How To get value of column when it exist in content value of column by sql server 2016 I want To get value of column when it exist  in content value of column by sql server 2016

For Example:

Table1:

Title1
----
Jack
Benz
Volvoo

Table2:

    Description              Names
    ----                    ---------
  The Jack isnt A122           NULL
  Some  B1234 Benz             NULL
  This Volvoo Are C            NULL
  This Maria Are D             NULL
  Michael Jackson              NULL

example:

Search Row By Row From table 1 in the row by row column Description in Table 2 and get value and insert to cloumn Names

IF Jack or Benz Or Volvoo exist in The Jack isnt A122
get Jack and Update column Names in  Table 2

    Description              Names
    ----                    ---------
  The Jack isnt A122           Jack

For 'Jack owns a Benz' that matches 2 titles (Jack and Benz), update with either one of them.

For Other Rows in Table 2 same:

    Description              Names
    ----                    ---------
  The Jack isnt A122           Jack
  Some  B1234 Benz             Benz
  This Volvoo Are C            Volvoo
  This Maria Are D             Others
  Michael Jackson              Others

The tables do not have primary keys.
Table1 (Title1) is UNIQUE.

Under some simplistic / naive assumptions (that match the given data):

The descriptions have only spaces and no other whitespace or connecting characters (dots, comma, question marks, dashes, etc.)
Titles are single words (no spaces, no extra characters either).

We can use LIKE to check whether a description contains a title:

concat(' ', description, ' ') LIKE concat('% ', title, ' %')

to first check that the matches are according to requirements:

select s.description, coalesce(t.title, 'Others') as title
from table2 as s
  left join table1 as t
  on concat(' ', s.description, ' ')
     LIKE concat('% ', t.title, ' %') ;

and then do the update:

update s
set s.names = coalesce(t.title, 'Others')
from table2 as s
  left join table1 as t
  on concat(' ', s.description, ' ')
     LIKE concat('% ', t.title, ' %') ;

Test in dbfiddle.uk


how do you revoke create table from a user on postgresql 9.4? I'm trying to create a postgresql user that can select but can't create or drop, insert and so on on a database but I cannot for the life of me figure out how to prevent a user from creating its own tables.

What I've done so far:

create database test;
create role readonly;
alter role readonly with login;
alter role readonly with encrypted password 'test';
revoke all on schema public from public;
revoke all on schema public from readonly;
revoke all on database test from public;
revoke all on database test from readonly;
grant connect on database test to readonly;

Yet when I log in to the database as readonly I'm still able to create tables with impunity. What am I missing?

According to the documentation, what one needs for creating tables in a schema is CREATE on that schema.  This you think you revoked, but as you experience it didn't really happen - the only plausible explanation is that from Daniel's comment: you are revoking access on the schemas of another database.

When you clear this up, you'll still need to grant/revoke the following:

GRANT USAGE ON SCHEMA public TO readonly; to be able to access the objects in there
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public, readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
and, if you plan to add new objects (tables) later, you have to set the default privileges, too.