MongoDB document delete and fragmentation I've a MongoDB collection that works like a queue: new documents are insert and old documents (after 60days) are removed. I can see a rapid grown of the datafile size, too rapid. I can be reasonable because we remove old data after 60 days, but I was thinking, are my deletes effective without execute the defragmentation? (in few words, what's a good way to manage disk space in MongoDB)

What is a correct defragment / clean collection politics? It's a production database and version is 2.6.9

thanks.

Reasons for unexpected data growth of data files.

"Data fragmentation" and data file preallocation

When a document is deleted, it's space is used right away if the new document fits into that space. Let's say you delete a document which takes 1kb of disk space and a new document requiring 0.9 Kb of disk space is synced to disk, then the first free space (the deleted document's in our example) will be used. Now let's assume the new document would need 1.1k. In a worst case scenario, a new datafile of 2GB had to be provisioned although only 0.1kb of space was missing. The reasons for datafiles being preallocated are rather good ones, btw: it would simply take too long during a disk sync.

Padding

When a document is written, some space is added to allow the document grow in size without triggering a rather expensive document migration each time. Documents are migrated when they do not fit into their position in the data file any more since

  Documents are never fragmented

So if your documents grow, they have to be migrated and a new padding is applied, it might well be that millions of places in the data files would provide enough space for a 1k document, but still a new data file has to be preallocated.

Another "problem" is the way padding is calculated. As of MongoDB 2.6, documents are by default by using power of 2 sizes. So let's assume your document is 513 bytes in size. However, since the next power of 2 would be 1kb, almost half of the space allocated for the document would not be in use until it grows in size. So in a worst case scenario, half of the space allocated for your data files -1 byte might be "wasted".

Increased usage

Your application might well be getting momentum and there simply is more data stored than you expect. Congratulations!

What to do

Usually, one of three ways of dealing with data file growth is suggested.

the compact command
the repair command
Forcing a resync from replica set members

I'll go over them with their Pros and Cons from my point of view and explain why I think all of them are improper ways of dealing with that data file growth.

The compact command

How it works

The compact command defragments the data files of a collection. It does it by creating a new data file of 2GB and moves the documents back and forth until there are no gaps between the documents any more.

Pros

The compact command is relatively fast when compared to the other solutions. The defragmentation helps a bit to prevent unnecessary data file preallocation.

Cons

The database containing the target collection is locked during the execution.
No disk space is reclaimed
You really should have a backup of the target collection before using the compact command. So in order to have said backup, you need to over provision your disks with 2Gb (the additional data file) plus the size of your largest collection (for the backup). But with over provisioned disks, space will not be a problem in the first place.
It doesn't help at all when space really is a problem: if you are in a critical situation, the problems detailed above prevent you from using the compact command.

Why I don't think it is a proper solution

Well, it's kind of obvious - you lock your database, which means downtime. For really large databases, this means a lot of downtime, and all this for the relatively small gain of potentially preventing one or two data files to be created (which means 4Gb disk space at max).

The repairDatabase command

How it works

Simplified, the repairDatabase command creates a second instance of your database, iterates over the documents in the original database, verifies them and writes them into the new database in consecutive order. In the last step, the old database is deleted and the new database is renamed.

Pros

With a proper planning, you can reclaim disk space with very little downtime, since the repairDatabase command can be run against secondaries. So you can do the following

Run the repairDatabase command against all secondaries
Have the primary step down. This might lead to 3-5 seconds of downtime during the election of the new primary.
Run the repairDatabase command against the recently stepped down primary

Sounds nice, right? However, there a huge

Cons

You need to massively over provision your disks, since basically a copy of your database is made. So now let's assume you run this command against a database which is in an optimal state. So to make sure the command is successfully executed you need at least the same amount of free disk space as your database uses when you issue the repair command. Since the repair command is potentially even more critical than the compact command, you should make a backup beforehand or use the backupOriginalFiles option.

Why I don't think it is a proper solution

The cons detailed above show that you have to over provision your disks by at least 200% of your payload data. With that massive amount of disk space, you would not have a problem in the first place.

Forcing a resync from replica set members

How it works

You shut down a secondary, delete its data files and restart it. The node notices that it is basically a new member added to a replica set and forces an initial sync with the replica set. Since the initial resync is document oriented, only necessary datafiles are allocated, potentially freeing formerly used disk space.

Like with the repair command, you do this for all secondaries (of course one after another), have the primary step down and delete its datafiles and let it resync.

Pros

You do not need to over provision the disks of an individual node
There is just very little downtime
It is a relatively straightforward process

Cons

This process takes a while, may well have some impact on performance and reduces your planned level of redundancy. Let me explain this in a bit more detail: When planning a replica set, you choose how many replicas you want to have, ranging from one (two data bearing nodes plus an arbiter) to 50 as the time of this writing. You have a good reason for this redundancy, whatever it may be. When arbitrarily shutting down replica set members in order to reclaim disk space, you effectively reduce or even eliminate failover capabilities. So it is safe to say that in order to keep your desired level of redundancy during the resync, you need one additional node to maintain it.

Why I don't think it is a proper solution

Put plainly: putting half the money you spend for the additional node into additional disk space should solve any space problem in the first place. However, this might not be in your case (although that might well be through under dimensioned hardware) and thus the resync might be a viable solution in some cases

Ok, smarty pants: What to do?

Frankly, from my experience, the need of reclaiming disk space is a sure sign of a badly planned cluster.

Granted, MongoDB is not the most efficient when it comes to disk space consumption, but after a while, it levels out. So when MongoDB constantly adds new datafiles, you can be sure that you simply need more disk space.

This can be either achieved through vertical or horizontal scaling. If you still can scale vertically and get an adequate bang for your buck, your hardware was underprovisioned until now. Go for it, problem solved!

If you already get the most bang for the buck and the size of your data (not only the number of your data files) constantly grows, it is time to scale horizontally, read to shard your cluster.

As a rule of thumb: when more than 80% of your disk space is used and the size of your data didn't show a massive spike but is constantly growing, I'd add a shard or start sharding. It requires some experience and knowledge to determine the exact threshold and how to do it exactly is out of scope even for this long answer.

With this approach, the decision when to shard is based on emprirical information, is started early enough to prevent serious problems, it reduces maintenance effort and risks and it enables you to scale properly.

One last word: Often people say that adding a shard is too expensive or they are not up to paying three config servers in addition to the data bearing nodes and start to shard their data manually. The reason for that is plainly wrong calculation of their own prices and a wrong understanding of how to do things sustainably. In the long run, it's going to bite you in the neck to reinvent the wheel.


SQL Server Patching question If I have an instance at SQL Server 2016 SP1-CU7-GDR (KB4057119) and want to jump over to the latest SP2 path, which patch or patches do I have to run?

SP2 = KB4052908
SP2-CU2 = KB4340355
SP2-CU2-GDR = KB4458621

Do I run all 3 of these in order, do I run just #1 and #3, or can I just run #3 all by itself?

It’s a bit confusing, but would appreciate the help.

Thanks,

Robert

Updates for SQL Server consist of applying the most recent service pack, then the most recent cumulative update, then any updates you require that are newer than the most recent cumulative update.  i.e. do 1, then 2, then 3.

Service packs are cumulative in that they embody all the changes made in the prior service packs, allowing you to go from the RTM release directly to service pack 2, skipping service pack 1 since those changes are included in service pack 2.

Cumulative updates are, as the name indicates, also cumulative, although they only capture the changes since the most recent service pack released prior to the cumulative update package.

Check this post for details of which updates you should consider.  This site provides excellent details for various service packs, cumulative updates, and fixes for SQL Server.

Note that as-of SQL Server 2017, service packs have been discontinued in favor of simple cumulative updates.  This should help eliminate confusion regarding what to apply since you only need to apply the most recent cumulative update, then any fixes you need that have been released since that cumulative update.


Postgres cannot be restarted after disk ran out of space My HD ran out of space while a postgres was running. I had to kill postgres and freed some space. Now I am not able to restart postgres with the following error message:

LOG:  listening on IPv4 address "0.0.0.0", port 5434
LOG:  listening on IPv6 address "::", port 5434
LOG:  listening on Unix socket "/tmp/.s.PGSQL.5434"
LOG:  database system was interrupted; last known up at 2018-04-16 05:20:46 EDT
PANIC:  could not read file "pg_logical/replorigin_checkpoint": Success
LOG:  startup process (PID 97490) was terminated by signal 6: Aborted
LOG:  aborting startup due to startup process failure
LOG:  database system is shut down

In data/pg_logical there exists a file with the name replorigin_checkpoint but it is empty.

I already copied the data directory for backup reasons as suggested here but I am actually not really sure what to do next. amcheck looks like, it is only working on a running postgres.

./postgres -V
postgres (PostgreSQL) 11devel

Ubuntu 16.04.4 LTS

Maybe it is worth noting, that the file system is itself mounted on that machine (nfs)

Questions
1. What is supposed to be inside replorigin_checkpoint?
2. Is there the possibility to restart from an earlier checkpoint?
3. What type of corruption repairings are out ther (not only corruption detection)

Maybe just try to rename the file?
If it's empty anyway, there should not be any loss of data.


Using NUMA Nodes with SQL Server I have a question to understand better how NUMA nodes help in SQL Server:

In our current set up, we have 4 NUMA nodes with 20 cores and hyper threading enabled, making a total of 40 logical processors. With memory of 400 GB, each NUMA gets 100 GB each.

It is a physical server with SQL Server 2012 Enterprise (core based licensing) on Windows 2012 R2 Standard. It has 4 sockets with 10 cores per socket and 20 logical processors per socket total 80 from error log what I see.

My understanding is maybe having 4 NUMA nodes might not perform better if we move (future SQL refresh to 2017/19) with same number of NUMA rather than I prefer 2 NUMA.

Why I am saying this because I think with 2 NUMA 400 GB will be allocated as 200 GB in each.

Therefore my understanding is it might perform better or its best to have this configuration based on how queries are switching between nodes for memory. Yes we are seeing some memory issues, I know there are other related issues which we are working but this question is more specific to NUMA set up for SQL Server.

Could someone please share their experience or may be provide more insight as I might be thinking wrong here with the belief query will have to less swapping here and there on 2 NUMA in comparison with 4 NUMA?

Is there a way or a query I can check if queries are spending time in swapping over two different NUMAs for memory and can be an issue?

Is there a way we can measure if two nodes might work better than four nodes for future design (cross-node memory access)?

Its a physical server on windows 2012 R2 standard. It has 4 sockets with 10 cores per socket and 20 logical procesors per socket total 80 from error log what i see. Its SQL2012 trying to move to 2017

Ok.  The memory on this box is seperated into 4 seperate nodes, each attached to a single socket.  SQL Server will use memory across all the sockets, and since much of SQL Server memory is used for caching database pages and query plans, there will be significant amounts of cross-node memory access.  And that's OK.  Query memory grants will be allocated on the local node for the worker running the task.

SQL Server has several resources it allocates per NUMA node, and for SQL Server 2016+ will automatically subdivide large NUMA nodes with the Automatic Soft-NUMA feature.

So on the one hand there's nothing you can do about the physical configuration of the server memory, and on the other there's nothing you need to do as SQL Server will automatically configure itself to use the NUMA nodes most effectively.

  Is there a way we can measure if 2 nodes might work better than 4 nodes for future design ?

Basically fewer nodes is better, but there's a limit to how many cores and how much RAM you can put on a single NUMA node.

Most of the cross-NUMA node memory access should be in the Buffer Pool, which you can track with a performance counter:

SQL Server: Buffer Node Remote Note page lookups/sec
    Indicates the number of lookup requests from this node which were satisfied from other nodes.


Large transactional log won't shrink after transactional backup I'll start by saying our databases use full recovery mode and we run SQL Server 2014.

Over the last few days our backups appeared to spiral out of control to the point that our full/differential backups were failing and only extremely large transactional backups were getting created. This of course caused a disk space issue and required a fair bit of manual intervention to restore order.

I manually ran a full backup on each database and then tried to follow up by running a transaction backup. During this period, one database's log file was increasing in size to the point that it ran out of disk space. Usually the log file would be around 1 GB, it is now sitting at about 30 GB.

Running a transactional backup did not reduce the size of the log file and I was stuck in a position where the log file was full and there was no room left on the disk. As a last ditch effort, I ran the SHRINKFILE( dbname, 4096) which instantly reduced the size and all appeared to be normal.

After doing so, I noticed my backups were no longer working and I was receiving an error:

Data error (cyclic redundancy check) failed.
I tried running CheckDB and was presented with the following message:

  A severe error occurred on the current command. The results, if any, should be discarded.

Given I was unable to back the database up, I decided to try and restore from earlier in the day off a full backup and 1 single transactional backup I had created. Both these files were large. The reason I only had 1 transactional backup was because I had disabled the job from running to avoid things spiralling further out of control.

New DB is up and running and backups work fine, but the issue of the large log file still prevails. My transactional backups are very small now but I can't seem to shrink the DB.

Anyone have any ideas? Alternatively, if there's a way to resolve the redundancy issue on the original database that would be a preferable solution as I'd have no data loss then.

No open transactions. The log_reuse_wait_desc is "LOG_BACKUP" which my understanding means that it is waiting for a log backup to run. This is madness because I have done several ;) dbcc sqlperf(logspace) is claiming I'm using 92% space.

Log backups run every 15 minutes in normal circumstances, but like I mentioned, these had been failing over the weekend. I've turned them off for the time being to stop the drive filling up and have just been manually running transactional backups. The database is not live at the moment so no additional data is going in there, I've run maybe 4 transactional backups as a test in the last hour or so.

None of replication, mirroring, or AOAG is configured here.

What is the frequency of the log backups ?

Use dbcc sqlperf(logspace) to check how much % is free space in the logs.

There is something that is holding the logs up. Check the log_reuse_wait_desc for that database and take action accordingly.

Follow the below article :

https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/


How to capture error message from one step in another step of an Agent job? Is it possible to capture error message from one step in another step of a SQL Server Agent job?

I have below scenario:

Step 1: Transact-SQL script (TSQL)
-- On Success action: Go to step 2
-- On Failure action: Go to step 3
Step 2: Transact-SQL script (Send success email)
Step 3: Transact-SQL script (Send failure email)

What I need is, if step 1 fails, I need to capture error message from step 1 in step 3 and send the same in an email notification.

I tried to use below token in Job step, but no success.

declare @err_msg as nvarchar(4000) = $(ESCAPE_SQUOTE(A-MSG))

I think your best bet is going to be querying the msdb.dbo.sysjobhistory table in the job step to get the information you're looking for.

Here's an example query that will get you all runs for a given job (mashed together from these two MSSQLTips articles [ref1], [ref2]).  Obviously, you'll want to adjust this to fit your means, but this should provide you with sufficient direction to get what you want.

SELECT j.NAME AS 'JobName'
    ,s.step_id AS 'Step'
    ,s.step_name AS 'StepName'
    ,msdb.dbo.agent_datetime(run_date, run_time) AS 'RunDateTime'
    ,((run_duration / 10000 * 3600 + (run_duration / 100) % 100 * 60 + run_duration % 100 + 31) / 60) AS 'RunDurationMinutes'
    ,CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        WHEN 4 THEN 'In Progress'
     END AS ExecutionStatus
    ,h.message MessageGenerated
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h ON s.job_id = h.job_id
    AND s.step_id = h.step_id
    AND h.step_id <> 0
WHERE j.enabled = 1 --Only Enabled Jobs
    and j.name = 'JobNameHere'
ORDER BY JobName
    ,RunDateTime DESC


select 'id' in every table psql Each table in my database (see schema) has a primary key called id.

I would like to find all records in all tables where id is in the range [1,1000]. How can I write such a query?

I've tried to write a for loop following the PostgreSQL docs

FOR T IN (
  SELECT TABLE_NAME FROM information_schema. TABLES
  WHERE table_schema = 'public'
    )
LOOP
    SELECT id, T FROM T WHERE ID BETWEEN 1 AND 1000;
  RETURN NEXT T;
END LOOP ;

but get "Syntax error at FOR".

You can do this all in one statement pretty easily.

SELECT string_agg(statement, E'\nUNION ALL ') || ';'
FROM (
  SELECT FORMAT(
    $$SELECT '%s' AS table_catalog, '%s' AS table_schema, '%s' AS table_name, id FROM %I.%I.%I WHERE %I BETWEEN 1 AND 1000$$,
    table_catalog,
    table_schema,
    table_name,
    table_catalog,
    table_schema,
    table_name,
    column_name
  )
  FROM information_schema.columns
  WHERE
    table_catalog = CURRENT_CATALOG
    AND table_schema = 'public'
    AND column_name = 'id'
) AS t(statement);

That will generate a single statement. In psql, just run \gexec after that.


ODBC and ADO connection to SQL Server: What protocol is used when connecting to \? We understand when connecting to SQL server that specifying:

< ip address>
< host or ip>,< port number>

That the ADO or ODBC client will typically use tcpip to connect.

However, we observe that when a client asks to connect to:

< server>\< instance name> that it seems the client (ODBC in this case) does NOT use tcpip.

We believe in some cases, connecting to:

< host name>

May also result in a named pipes connection.

Questions:

Since < server>\< instance name> is not connecting over tcpip, that means it is using named pipes, right?
How do I force the connection to use tcpip?

And is the behavior (of the 32 bit Windows ODBC driver for SQL Server) in this regard the same as the behavior of the old dblib client?

If you don't specify the protocol in the connection string, then it will use the protocol specified for the database driver. The protocol order, by default, is Shared Memory, TCP/IP and then Named Pipes. You could also set a default protocol.

To specify which protocol to use in a connection string, add tcp: before the server name. Examples: tcp:server1\instance1, tcp:server2, tcp:server3,1433

You can use SQL Server Configuration Manager to see the protocol order or regedit to see the default and protocol order.

The regkey is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI11.0 for 64-bit applications and HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\SNI11.0 for 32-bit applications.


Testing data integrity porting from old database to new I've created a Web/ASP.NET/MySQL database front end & back end that gets created from the XML logs from a previous Java based XML client-server that is not serving the company very well (the XML database bloats too quickly and the server begins to time out, which halts the production floor).

Any future data will come through the web front end or API calls to the back end, and we'll eventually (after testing) shut down the old client-server database.

We're about to go live for testing with the new database, and I'm worried about data integrity with the imported data.  We ended up with a bunch of duplicate data (about 18%, see SO question about this here) which I've cleared up (or at least come up with a repeatable method to do so).  I've also tested that each of the row ID's that are primary keys actually have data associated with their foreign key children (and their's have the same PK-FK row data association down to the lowest child table, hopefully that made sense).  However, I've only tested that the rows contain data, not that they contain the right data.

My thought is to choose random log files from the old XML database, and make sure that its data is where it should be in the new MySQL version.  Ideally I'd be able to automate this, but it's complex to break down the old logs into their constituent parts to insert into the database, but maybe not so much to simply search for the data contained in them.

I'm relatively stupid when it comes to DBA stuff, and I realize what I'm asking is kind of vague, but what I'm looking for is what would a DBA want to know to make sure they were working with reliable data?  If this can be figured out and tested for (my forte), I'm going to feel a lot better about the data.  The entire company will be relying on this data once it goes live, I need to get this right.

This answer may not be what I was originally looking for, but it's a sequence of events I went through to insure only unique data was inserted into the database vs. trying to purge it after the fact (and never being certain).  As I indicated in my comment above, I investigated using INSERT IGNORE INTO to prevent duplicate data.  To do so, I created a composite unique key that used a primary key from another table, the time stamp, and user name.  If those were not unique, don't insert the data.  That looked like this, the unique key is the last statement shown:

CREATE  TABLE IF NOT EXISTS `uniqueTest`.`events` (
  `Event_ID` INT(11) NOT NULL AUTO_INCREMENT ,
  `PCBID` INT(11) NOT NULL ,
  `EventType_ID` INT(11) NOT NULL ,
  `DateTime` DATETIME NOT NULL ,
  `User` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`Event_ID`) ,
  UNIQUE KEY `PDU_Index` (`PCBID`, `DateTime`, `User`),...

And then used string.Format to build the insert string like this:

INSERT IGNORE INTO EVENTS (PCBID, EVENTTYPE_ID, DATETIME, USER)
VALUES (@eventPCBID, @EVENTTYPE_ID, '{2}', @user);

Along with this I came up with (with lots of SO help), methods to further ignore any child inserts by setting the LAST_INSERT_ID (set to variable @INSERT_ID) of the follow on statements to null.  If it were null, simply don't do the insert.  You can read about that here.  Here's an example of how I used variables to determine the value to use for LAST_INSERT_ID:

SET @EVENT_ID = IF(ROW_COUNT() > 0, LAST_INSERT_ID(), null);

I soon realized that I had to do more work because I frequently had child data where I was using multiple VALUES statements, and needed to be able to insert that data if the ID wasn't null, or ignore it as before.  This was done with UNION ALL, and you can read about it here.

The last thing that became a problem was that I'd frequently have NULL data, and MySQL didn't like having that in the sub-selects, so it became necessary to alias all the values something like this (keep in mind all these statements were being generated in a LinqPAD set of C# statements):

measurementInsertString += String.Format("SELECT @TestSteps_TestSteps_ID AS e, {0} AS MeasurementName, NULL AS MeasuredValue, NULL AS Min, NULL AS Max, {1} AS MeasuredString, {2} AS ExpectedString UNION ALL\n",
                                    stringMeasurements.First().measurementName,
                                    stringMeasurements.First().measuredString,
                                    stringMeasurements.First().expectedString);

Hopefully this will help someone else who's going down a similar path.


Can sst_state_transfer be set to rsync with innodb as storage engine in Galera cluster? Galera cluster wsrep-sst-method docs states:

  You can only use rsync when anode is starting. You cannot use it with
  a running InnoDB storage engine.

I do have InnoDB storage engine configured in MariaDB Galera cluster and the wsrep-sst-method is set to rsync and state transfers do happen on starting nodes.

What's wrong with this configuration? Is xtrabackup the only wsrep-sst-method to be used with innodb storage engine?

Is xtrabackup the only wsrep-sst-method to be used with innodb storage engine?

All Galera clusters are InnoDB, MyISAM (and derivates) not supported (expect mysql database with wrappers for managing users/permissions).

No, nothing wrong with your configuration. You can use Logical State Snapshot mysqldump or the Physical State Snapshot rsync and xtrabackup. See State Snapshot Transfers for a comparison.

A physical restore always needs a restart. The same concept like backup/restore.

In normal operations only IST is needed. SST is only needed when something goes wrong (maybe infrastructure issue) and the node is out-of-sync. Mostly the DBA cares about such cases.

State Snapshot Transfers (SST) Where a snapshot of the entire node
state transfers.
Incremental State Transfers (IST) Where only the missing transactions transfer.


Insert cursor result into a table I have the below query and would like to insert the result into a table I created in Sql Server. Is there a way this can be done? The idea behind I'll create a SQL Agent job where the cursor result is stored on a table within my database.

Declare @DB sysname, @SQL nvarchar(4000)
Declare DBCur Cursor for Select Quotename(Name) from sys.databases where name like '%Prod%'
Open DBCUR
Fetch Next from Dbcur into @DB
While @@Fetch_Status = 0
Begin

      Set @SQL = 'select /*@@Servername as servername,*/ '+ Char(39) + @DB + Char(39)+' as databasename, o.Name as triggerName from '+@DB +'.'+'sys.objects o inner join  '+@DB +'.'+'sys.triggers t
      on o.[object_id] = t.[object_id]
            where o.type = ''TR''
            and t.is_disabled = 0'
      Print @SQL
      Exec SP_ExecuteSQL @SQL
      Fetch Next from Dbcur into @DB
end
CLose DBCur
DEAllocate DBCUr

You will need the table to exist and have all the columns returned by your statement.

Then you can run something like:

INSERT INTO MyTable Exec (@SQL);


In Oracle how do I save a sequence.nextval in a variable to be reused in multiple inserts? I'm writing a script to populate some tables with data for testing.

I would like to write something like the following but I don't know how to do it (I'm Oracle 11g)

SET ENABLED_USER_ID = SEQ.NEXTVAL; // PSEUDOCODE
SET DISABLED_USER_ID = SEQ.NEXTVAL; // PSEUDOCODE

INSERT INTO USERS
        (ID,      USR_NAME)
VALUES  (:ENABLED_USER_ID, 'ANDREW');
INSERT INTO CAR
       (CAR_ID,         CAR_NAME, USR_ID)
VALUES (CARSEQ.NEXTVAL, 'FORD',   :ENABLED_USER_ID);

INSERT INTO USERS
        (ID,      USR_NAME)
VALUES  (:DISABLED_USER_ID, 'ANDREW');
INSERT INTO CAR
       (CAR_ID,         CAR_NAME, USR_ID)
VALUES (CARSEQ.NEXTVAL, 'FORD',   :DISABLED_USER_ID);

I know I could rearrange the queries and use the sequence.currval reference, but I'd prefer to have the id saved in properly named variables.

Maybe I should just wrap the script in a DECLARE ... BEGIN ... END; but I'm hoping there is a more concise way to do it.

Addition 27 May 2011 15:31

It seems that in any case I have to declare the variables in a DECLARE block. So I'm trying with

DECLARE
  USER_ID NUMBER(10,0) := 1;
BEGIN
  insert into TEST_USER
  values (user_id, 'andrew', sysdate);
END;

but I get the following error

Caused by: java.sql.SQLException: ORA-06550: **line 2, column 27:**
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

  * & = - + ; < / > at in is mod remainder not rem
  <an exponent (**)> <> or != or ~= >= <= <> and or like like2
  like4 likec between || multiset member submultiset

That points to the variable declaration.

I'm using java to load the script from a file and running it using the Oracle JDBC driver (ojdbc14-10.2.0.4.0.jar) on a Oracle 11g server.

The table TEST_USER has been created with

create table TEST_USERS (
    id number(10, 0) not null,
    name varchar2(100),
    date_ins date default sysdate,
    primary key (id)
);

I think it goes like this

DECLARE
    ENABLED_USER_ID PLS_INTEGER;
    DISABLED_USER_ID PLS_INTEGER;
BEGIN
    ENABLED_USER_ID := SEQ.NEXTVAL;
    DISABLED_USER_ID := SEQ.NEXTVAL;

    INSERT INTO USERS (ID, USR_NAME)
    VALUES  (ENABLED_USER_ID, 'ANDREW');

    INSERT INTO CAR (CAR_ID, CAR_NAME, USR_ID)
    VALUES (CARSEQ.NEXTVAL, 'FORD', ENABLED_USER_ID);

    INSERT INTO USERS (ID, USR_NAME)
    VALUES  (DISABLED_USER_ID, 'ANDREW');

    INSERT INTO CAR (CAR_ID, CAR_NAME, USR_ID)
    VALUES (CARSEQ.NEXTVAL, 'FORD', DISABLED_USER_ID);
END;
/


Why does MongoDB still support MapReduce, but actively discourage people from using it? Looking at the MongoDB documentation I see that users are discouraged from using MapReduce in favour of the Aggregation pipeline.
For example, on this page in the documentation.
But the same notice is ambiguous in terms of whether the Aggregation Framework completely replaces MapReduce or not:

Various map-reduce operations can be rewritten using aggregation pipeline operators, such as $group, $merge, etc. For map-reduce operations that require custom functionality, MongoDB provides the $accumulator and $function aggregation operators starting in version 4.4.

Does this mean that "every conceivable" MapReduce configuration can be replicated via the Aggregation Framework or not?
I understand that the MapReduce execution context might be slower than the Aggregation pipeline since the former requires a JavaScript engine which is/was/still is? slower than the Aggregation pipeline implementation.
Unlike other features, MapReduce has NOT been labeled as deprecated so far as I know. So, my question is:
Is MapReduce completely replaced (for every single possible use-case) by the Aggregation Framework? Or are there use-cases where MapReduce still provides for greater flexibility?

The answer is no, the aggregation framework does not replace "every conceivable" MapReduce configuration. Without becoming a general purpose MapReduce framework with fully customizable functions, it can't. That's what you get with the existing MapReduce functionality - any functions you want (as long as they can be written in Javascripts of course).
I haven't used it in a long time (years), but it is not just because of the use of the javascript engine that the MapReduce funcionality is slower, it still does things like taking a global write lock (docs) when writing output which will block all other operations on a mongod - ouch. Even several version prior to this, when the Aggregation Framework was nowhere near as featureful as it is now the advice was: if you can use the aggregation framework, do so, only use MapReduce if you have no other choice.


Can the SQL Server 2012 service be running while applying CU 8 to SP 3? I want to apply CU 8 to SQL Server 2012 sp3 and was hoping to not have to ask users to logoff the system while applying since we have 24 x 7 users on this server. Then I assume if the server does require a reboot it will tell me so or is a reboot something I should do as a best practice?

You will definitely need to have users log off during the patch application; in theory, the service will be available for a while during the process, but there's no way to tell when the service will be stopped, and user activity could impact the patch application.

Be safe, plan for a downtime, apply the patch. Make sure you have proper backups and recovery plans in place in case of problems.


Preserve order of array elements after join I have a query that returns a CTE looking like

+-----------+-------------+
|   node_id | ancestors   |
|-----------+-------------|
|         1 | []          |
|         2 | []          |
|         3 | [1]         |
|         4 | [2]         |
|         5 | [4, 2]      |
+-----------+-------------+

What I want to do is join with the nodes table and replace the ids that are in the ancestors column with another column on the nodes table. Here's my query so far:

WITH RECURSIVE tree AS (
  -- snip --
)
SELECT node.entity_id AS id,
       array_remove(array_agg(parent_nodes.entity_id), NULL) AS ancestors
FROM tree
JOIN entity.nodes AS node ON node.id = tree.node_id
LEFT OUTER JOIN entity.nodes AS parent_nodes ON parent_nodes.id = ANY(tree.ancestors)
GROUP BY node.id;

The problem with that query is that is loses the order of the original ancestors array. Is there a way to perform the join while keeping the original order during the array_agg function?

The problem with your query is the join condition id = ANY(ancestors). Not only does it not preserve original order, it also eliminates duplicate elements in the array. (An id could match 10 elements in ancestors, it would still be picked once only.) Not sure if the logic of your query would allow duplicate elements, but if it does I am pretty sure you want to preserve all instances - you want to keep "original order" after all.
Assuming current Postgres 9.4+ for lack of information, I suggest a completely different approach:
SELECT n.entity_id, p.ancestors
FROM   tree t
JOIN   nodes n ON n.id = t.node_id
LEFT   JOIN LATERAL (
   SELECT ARRAY (
      SELECT p.entity_id
      FROM   unnest(t.ancestors) WITH ORDINALITY a(id, ord)
      JOIN   entity.nodes p USING (id)
      ORDER  BY ord
      ) AS ancestors
   ) p ON true;

You query only works as intended if nodes.id is defined as primary key and nodes.entity_id is unique as well. Information is missing in the question.
Normally, this simpler query without explicit ORDER BY works as well, but there are no guarantees (Postgres 9.3+)...
SELECT n.entity_id, p.ancestors
FROM   tree t
JOIN   nodes n ON n.id = t.node_id
LEFT   JOIN LATERAL (
   SELECT ARRAY (
      SELECT p.entity_id
      FROM   unnest(t.ancestors) id
      JOIN   entity.nodes p USING (id)
      ) AS ancestors
   ) p ON true;

You can make this safe as well. Detailed explanation:

PostgreSQL unnest() with element number

SQL Fiddle demo for Postgres 9.3.
Opional optimization
You join to entity.nodes twice - to substitute for node_id and ancestors alike. An alternative would be to fold both into one array or one set and join only once. Might be faster, but you have to test.
For these alternatives we need the ORDER BY in any case:
Add node_id to the ancestors array before we unnest ...
SELECT p.arr[1] AS entity_id, p.arr[2:2147483647] AS ancestors
FROM   tree t
LEFT   JOIN LATERAL (
   SELECT ARRAY (
      SELECT p.entity_id
      FROM   unnest(t.node_id || t.ancestors) WITH ORDINALITY a(id, ord)
      JOIN   entity.nodes p USING (id)
      ORDER  BY ord
      ) AS arr
   ) p ON true;

Or add node_id to the unnested elements of ancestors before we join ...
SELECT p.arr[1] AS entity_id, p.arr[2:2147483647] AS ancestors
FROM   tree t
LEFT   JOIN LATERAL (
   SELECT ARRAY (
      SELECT p.entity_id
      FROM  (
         SELECT t.node_id AS id, 0 AS ord
         UNION ALL
         SELECT * FROM unnest(t.ancestors) WITH ORDINALITY
         ) x
      JOIN   entity.nodes p USING (id)
      ORDER  BY ord
      ) AS arr
   ) p ON true;

You did not show our CTE, this might be optimized further ...


Msg 102, Level 15, State 1, Server DESKTOP-U368A1B, Line 1 Incorrect syntax near '1' I get the above error message when i run the following perl script from the cmd, that is supposed  to do some transaction in my database.

#!/usr/bin/perl.

use warnings;
use strict;

my ( $pid1, $pid2, $pid3, $i );    #Declaration of local variables
$| = 1;

if ( ($pid1 = fork()) && ($pid2 = fork()) && ($pid3 = fork()) ) {       #Starts child processes
   print( "I have to wait for my kids.\n" );        #The main process execute this code
   my $straggler = wait();              #The main process waits for all the children to finish
   print( "Finally $straggler finished, now I can go.\n" );
}
elsif ( $pid1 && $pid2 && defined($pid3)) {         #Is executed by the third child
   sleep( 1 );                      #Wait 1 second
   print( "Start moving money between account 1 - 2" );
   sleep(2);
   for ($i=0; $i < 1000; $i++) {            #Start transaction repeatedly
    system("sqlcmd -S DESKTOP-U368A1B  -Q tranKonto(1,2,100,1)"); #Moves 100 from account 1 to account 2
   }
   print( "End moving money between accounts trans 1 - 2 " );
   exit();
}
else {
   die( "Forking problems: " );
}

The transaction looks like this:

PROCEDURE [dbo].[tranKonto] @konto1 INT,  @konto2 INT, @belopp INT, @isoLevel INT
AS
BEGIN TRANSACTION tranKonto

    IF(@isoLevel=0)
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    ELSE IF (@isoLevel = 1)
        SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    ELSE IF (@isoLevel=2)
        SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    ELSE IF(@isoLevel=3)
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    BEGIN
        UPDATE Konto SET belopp=belopp-@belopp WHERE kontonr=@konto1
        UPDATE Konto SET belopp=belopp+@belopp WHERE kontonr=@konto2
        EXEC kontoLog_SP @konto1,  @konto2 , @belopp , @isoLevel;
    END

COMMIT TRANSACTION tranKonto
RETURN

I get the following error message when running the script:

  Msg 102, Level 15, State 1, Server DESKTOP-U368A1B, Line 1
  Incorrect syntax near '1'.

I would really appreciate if somebody could help me out. I have tried to google a solution but havn't had any luck.

You should remove the parentheses from around the parameters for your stored procedure, and enclose the call in double quotation marks. In a normal command line, this would look like:

sqlcmd -S DESKTOP-U368A1B  -Q "tranKonto 1,2,100,1"

Since this is in perl, you will need to escape the quotation marks:

system("sqlcmd -S DESKTOP-U368A1B  -Q \"tranKonto 1,2,100,1\"");


Best way to migrate a huge SQL Server database with low downtime over network Problem definition

Our database server needs to be transferred to an other datacenter. It runs on Microsoft SQL Server 2012 Enterprise (64-bit) and contains two databases of about 2TB and 1TB.

Having little to no downtime for this would be ideal.

Workload

Those databases are used for a .NET website and are constantly getting updated.

Having it not available over the weekend would be acceptable though. The currently in use DB would remain the only one in use until the switch over to the new one.

That switch would ideally be made just by changing DNS entries to point to the new DB server while making sure the DB is not being updated.

Also, time taken by this operation does not really matter as long as the switch from one server to the other (downtime) is kept low.

Approaches considered

Backup and restore

This has been done in the past but involved a high downtime even though it was done through an internal network, so more efficiently than through Internet
Log shipping

As far as I understand, this approach would minimize downtime by configuring a master/slave and transferring  an exact copy of the master DB to its slave being read only. As mentioned above, no access to the slave would be necessary and we just need a way to have a replica of the master DB without data corruption.

It also seems to be quite efficient in terms of resources utilization and wouldn't impact to much the master performance.

I might be wrong about this approach so feel free to correct me.
Database mirroring

I'm not too aware of that approach but it seems like a valid option. No need to have real time sync and performance of the master is quite important so asynchronous would be the way to go if this approach were to be chosen.
Other options?

That server runs directly on bare metal hardware so lower level solutions are unfortunately not an option. Maybe there is a better way to get this done?

Constraints

As described, those databases are quite big to the point they are hard to maintain but that's an other problem.

The versions of SQL Server will be the same (Microsoft SQL Server 2012 Enterprise 64-bit).

It will have to be transferred over network between two datacenters so most probably over Internet. Having disks sent from one site to the other for an initial sync is unfortunately not an option. Having some sort of security for the transfer would be ideal but we will do the best of this situation.

That should give a quite good overview of our needs for this task and hopefully some of you had to face that situation before.

Straight backup and restore is obviously out. I also wouldn't consider replication of any kind.

Database mirroring is relatively simple to set up, but requires real-time connectivity between the two servers, setting up of partners and endpoints, etc. Availability Groups could be an option, but on top of the networking complications you also have to have both servers as members of the same WSFC - which means they must both be in the same domain. This is not a typical setup (or could even be made to work temporarily) for a data center move.

My vote would be for log shipping. The nice thing about this is that you can use the backups and log backups you're already taking (right?) and don't necessarily have to have real-time connectivity between the two databases - they don't need to know about each other, you don't need to set up endpoints for mirroring, partners, security, etc. You just need a way to get files from the old server to a place where they can be restored on the new server. You can take a full backup well in advance, get it over to the new server, restore it, then apply (possibly diffs and) incremental log backups from that point right up to the moment of cut-over. The process is actually quite simple, and there are many tutorials on log shipping available online if you come across any difficulties.

If the web app is moving with the database, since DNS can take a while to propagate, you might want to make a switch in the connection strings of the old app to make them point at the IP of the new database server once it is writable, since - even after the switch, and even if your TTL settings are tight - clients may continue to hit the old web servers. It all depends on how much respect their providers give your TTLs.


Help to improve a function I have this function working fine:

CREATE or replace FUNCTION pointscanner(
    IN source integer,
    IN target integer,
    IN k integer,
    IN directed boolean,
    IN quant integer
)
RETURNS table(way geometry, name text, distance double precision, tags hstore, "operator" text,
        admin_level text, z_order integer) AS $$
DECLARE
    geomRoute geometry;
    routeBB box2d;
BEGIN
    SELECT INTO geomRoute ST_Union(geom) from route_agg($1, $2, $3, $4);
    routeBB := ST_Extent(geomRoute);

    RETURN QUERY
    SELECT pt.way, pt.name, ST_Distance( rt.geom, ST_Transform(pt.way,4326) ) as distance,
        pt.tags, pt.operator, pt.admin_level, pt.z_order
    FROM planet_osm_point pt, route_agg($1, $2, $3, $4) rt
    where routeBB && ST_Transform(pt.way,4326)
    order by distance asc limit $5;

END; $$
LANGUAGE 'plpgsql' VOLATILE;

But as you can see I'm calculating the route twice (route_agg) and sometimes it may be very costly. How can I improve this to run route_agg just once?

I'd try this (which I don't fully understand, but should be equivalent):

CREATE or REPLACE FUNCTION pointscanner
(
    IN _source integer,
    IN _target integer,
    IN _k integer,
    IN _directed boolean,
    IN _quant integer
)
RETURNS table(way geometry, name text, distance double precision, tags hstore, "operator" text,
        admin_level text, z_order integer) AS $$
BEGIN
    RETURN QUERY
    WITH rt AS
    (
        SELECT
            *
        FROM
            route_agg(_source, _target, _k, _directed)
    ),
    xt AS
    (
         SELECT
             ST_Extent(ST_Union(geom)) AS extent
         FROM
             rt
    )
    SELECT
        pt.way,
        pt.name,
        ST_Distance( rt.geom, ST_Transform(pt.way,4326) ) as distance,
        pt.tags,
        pt.operator,
        pt.admin_level,
        pt.z_order
    FROM
        planet_osm_point pt
        CROSS JOIN rt
        CROSS JOIN xt
    WHERE
        xt.extent && ST_Transform(pt.way, 4326)
    ORDER BY
        distance ASC
    LIMIT
        _quant;
END; $$
LANGUAGE 'plpgsql' VOLATILE;

NOTE: it would be helpful to have the definition of the planet_osm_point table, and of route_agg.


Why does restoring postgresql database from dump produce errors I did the following commands:

pg_dumpall --clean | gzip > db.gz
gunzip db.gz
psql < db

I expected the third command (loading the database back) to just run with zero errors, but I got some errors:

ERROR:  multiple primary keys for table "pga_exception" are not allowed
ERROR:  multiple primary keys for table "pga_job" are not allowed

There are more errors than those listed, but they are a combination of primary key violations, constraints already existing, and triggers already existing.  Why would recreating a database from a backup sql dump produce errors?

pg_dumpall --clean will not issue DROP DATABASE statements for databases like template1 or postgres that have been created by initdb at cluster initialization time.

However it will dump the contents of these databases if you created any objects in it (by default they're empty). So when restoring the result of pg_dumpall --clean into the same instance, it's likely that such errors will occur if these databases happen to have contents.


How to deny drop permission for a table? Is there a way to deny drop permissions for a specific table from a user or role?

If you need to prevent to drop the table by some user, try this:

DENY DELETE ON OBJECT::dbo.table_to_deny TO restricted_user;

http://msdn.microsoft.com/en-us/library/ms173724.aspx


does last_insert_id() returns correct result when writes from multiple sessions? I have a table in which there is a combination of 4 columns that will act as a unique key.

Instead of using a composite primary key, I want to keep an id column as the primary key. This field is on auto_increment. The insert to the database are done through a C interface. When a row is inserted, I want to know the id it was assigned. LAST_INSERT_ID() can be used to retrieve that, but there will be multiple sessions inserting in the table. Will LAST_INSERT_ID() return the correct value in that case?

I have gone through this post: Is MySql's LAST_INSERT_ID() function guaranteed to be correct?
It says that id of first inserted row is returned when multiple rows are inserted.

When multiple sessions write to the same table but a single session writes only one row at a time, is it possible to retrieve the correct value of the id generated? Will the Isolation Level also matter?

Here's what the MySQL 5.5 documentation says:

  The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

Unless you fall into the other caveats noted in the question you link to, the last_insert_id() function will return what you expect, and will not be affected by other sessions.


How to avoid lagging when enabling log-slave-updates? Master:

48GB RAM
16-core
my.cnf: https://clbin.com/hlkUo

Slave:

64GB RAM
24-core
my.cnf: https://clbin.com/viLm0

Both are running 5.5.28.

For the incremental backup purpose, I need to enable the log-slave-updates option. But Seconds_Behind_Master keeps increasing immediately after restarting MySQL. A simple statement get... 2 minutes to updated on the slave at spare time. At the busy time, it seems that the Slave cannot catch up with the Master, so I have to comment it out and restart MySQL.

Is there any way to improve this?

As your main concern is to have an incremental backup solution, you can change the follow variables:
Changes:
innodb_max_dirty_pages_pct - set it to 75, then innoDB will cache some changes and flush it to disk at once.
innodb_doublewrite - Disable the innodb double write
sync_binlog - Disable syn binlog
long_query_time - increase the long query time or disable it (at the moment you are logging every single query)
Why slave delays?
Other think is, it looks to be a busy master, on master you are able to handle 768 connections at the same time, it means that 768 thread can be writing data to MySQL, and the server (if not having looking issues) will execute all these queries at same time. In the other end(slave), you have a single replication thread to execute all queries received from master, and if a query take 10 seconds to execute on slave, the only thread available to execute changes from master will be busy, in this case, some delay are expected.
In your case you also use the slave as a read only copy of the master, then the replication thread may also need to wait some other thread release lock on some rows to apply the changes.


SSISDB Maintenance job cleaning data very slow on SQL Server 2014 On SQL Server 2014 Microsoft (12.0.2430) SQL Server Enterprise: Core-based Licensing (64-bit) after changing the retention period from 365 to 90 since last 2 days job clean up about 2 GB of data only. I have rebuild & update all stats on SSISDB database still its slow.

Current Size of database is 344GB Approx. what is the best way to clean old records Quickly, To do that do i need down time ?

I have followed below article so far :-
https://www.mssqltips.com/sqlservertip/3307/managing-the-size-of-the-sql-server-ssis-catalog-database/

In order to do it quickly, you will need to delete the SSISDB and recreate it.  But I don't recommend doing that.  The cleanup job is interfering with your execution of SSIS Packages (or vice-versa).

My approach when I'm trimming back the retention period is to walk backwards a day at a time.  The script below will do that, just change the @DesiredRetention to what you want and it will walk backwards to that point.  You can stop and start this script multiple times (to allow for it to run when packages are not) and it will pick up where it left off.

DECLARE @DesiredRetention INT = 30;
DECLARE @CurrentRetention INT = (SELECT property_value FROM SSISD.internal.catalog_properties WHERE property_name = 'RETENTION_WINDOW');

WHILE @CurrentRetention > @DesiredRetention
BEGIN

    PRINT 'Removing History older than: ' + CAST(@CurrentRetention AS VARCHAR(10));
    PRINT GETDATE();

    --Change the desired retention period...
    EXEC [SSISDB].[catalog].[configure_catalog] @property_name=N'RETENTION_WINDOW', @property_value=@CurrentRetention;

    --Run the Cleanup Job
    EXEC [SSISDB].[internal].[cleanup_server_retention_window];

    --Decrement the value.
    SET @CurrentRetention = @CurrentRetention - 1;

END

--Make sure to set the desired retention period...
EXEC [SSISDB].[catalog].[configure_catalog] @property_name=N'RETENTION_WINDOW', @property_value=@DesiredRetention;


Literature about In-Memory Database Systems I am currently writing my bachelor thesis on IMDBS and would like to ask if any of you have any recommendations for literature which summarizes the technical conception/architecture of IMDBS as apparently I am not able to find articles, papers, books etc which are covering the physical architecture or to In-Memory Database Systems but just on logical data storage, indexing etc.

Any recommendations would be highly appreciated.

Thanks in advance,
toni

The CMU advanced database course is about building an in-memory DBMS. It has been released on Youtube. Each lecture has some notes and references.


Is my database in the elastic pool? I am trying to set up an Azure SQL Database inside of an elastic pool. I'm not sure if I did it right.  Here's my dashboard:

And here's the actual database:

What makes me wonder if the database is actually inside the elastic pool is what's circled in red in the above screenshot.

  No elastic pool

Here's the server:

And here's the elastic pool:

Another clue that the DB is not in the pool is that the pool shows no usage.

So, I'm pretty sure I did something wrong. But I can't seem to figure out HOW to put the database inside the pool.

Help!?!

Looks like the database is not in the pool.  Go to the Elastic Pool in the portal and click on Configure Pool, and Add Databases.


The Windows Server Failover Clustering (WSFC) resource control API returned error code 19 What is root cause of this error. I couldn't find any related information.
There are two nodes on Availability Group and if you reboot second node, database wont up.

Here are the details of that error code (19):

ERROR_WRITE_PROTECT
19 (0x13)
The media is write protected.

You need to consult the cluster log (use Get-ClusterLog to get that) for additional details about what writes failed within the cluster operation being performed.  Check that out and update your question with any errors you see.
That being said, combined with this symptom:

...if you reboot second node, database wont up.

You might be experiencing disk problems.  Check the Windows system event log and SQL Server error log for messages related to failed writes or corruption.


Oracle autotrace - pstart and pstop always the same I have been executing autotrace against a partitioned table. The table is partitioned by month range. For the data I have so far there are 4 partitions - however, when I execute autotrace against query in which WHERE clause uses partitioning key, the pstart is always 1, and pstop is 1048575. Does that mean that it doesn't utilize the partition pruning properly? Shouldn't it come back as something like pstart=1 and pstop=1 (or 2 and 2 etc) if I'm looking to retrieve data of a single month?
Statement used to create partitioning
PARTITION BY RANGE(event_time)
INTERVAL (numtomyinterval(1, 'month'))
(PARTITION part_01 values less than (to_date('2010-01-01', YYYY-MM-DD)));

SQL query issues that was analyzed by autotrace
SELECT * FROM EXAMPLE
WHERE TO_DATE(SUBSTR(event_time, 1, 10), 'dd-MM-yy' BETWEEN TO_DATE('06-JUL-2020', 'dd-MON-yy')
AND TO_DATE('06-JUL-2020', 'dd-MON-yy');

Autotrace results in

Sure it should, given that you wrote proper SQL. But you used a derived value in the filter conditions instead of the actual column.
Actual condition in SQL should be something like this (I also changed the date format string and the values to make some sense):
where event_time between BETWEEN TO_DATE('06-JUL-2020', 'dd-MON-yyyy')
AND TO_DATE('07-JUL-2020', 'dd-MON-yyyy')


When should indexes be dropped and recreated? We are constructing a data warehouse that initially will be 1 TB and will grow around 20gigs every month.

For certain tables we are doing daily ETL processes and others we are doing weekly/monthly.

When there is a data import going into a table, is it necessary to drop and recreate the indexes?

Is there ever a point to dropping and recreating indexes or are they automatically updated?

The statistics are set to update automatically.

Thank you so much for your help and guidance.

I got this genius script:

SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
       CASE WHEN ps.avg_fragmentation_in_percent > 40 THEN 'REBUILD' ELSE 'REORGANIZE' END +
       CASE WHEN pc.partition_count > 1 THEN ' PARTITION = ' + cast(ps.partition_number as nvarchar(max)) ELSE '' END
FROM   sys.indexes AS ix INNER JOIN sys.tables t
           ON t.object_id = ix.object_id
       INNER JOIN sys.schemas s
           ON t.schema_id = s.schema_id
       INNER JOIN (SELECT object_id, index_id, avg_fragmentation_in_percent, partition_number
                   FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)) ps
           ON t.object_id = ps.object_id AND ix.index_id = ps.index_id
       INNER JOIN (SELECT object_id, index_id, COUNT(DISTINCT partition_number) AS partition_count
                   FROM sys.partitions
                   GROUP BY object_id, index_id) pc
           ON t.object_id = pc.object_id AND ix.index_id = pc.index_id
WHERE  ps.avg_fragmentation_in_percent > 10 AND
       ix.name IS NOT NULL

from here:

http://weblogs.asp.net/okloeten/archive/2009/01/05/6819737.aspx

Do you suggest I run this script daily and based on the findings run the generated code?

If this is cyclical ETL, and you are in a development (i.e. NOT LIVE) data environment, then you definitely should manage your indexes as a part of your load cycle.

I do this for several data sets every month, the largest of which adds around 100 GB monthly to a 5 TB data set.

I have done extensive testing, and from my own experience the most efficient way to load with regard to indexes is:

DISABLE non-clustered indexes, leaving clustered index intact
Perform load of raw into your data table
REBUILD NC indexes

If you only add rows periodically as part of managed ETL, this is the way to go.  This also ensures all your statistics are up to date.

For stats, it's important to note that adding 20GB to a 1TB database won't reach the tipping point for a stats auto-update, so you can add a whole month of data without ever updating statistics.

Rebuilding your NC indexes is a good way around this.  You may want to also do a clustered index rebuild periodically if fragmentation gets high (depending on your table structure and your clustered key).


SQL Developer suggesting I add aggregate to group by clause This isn't actually a bug, just an annoyance that I would like some explanation for. I have tried my Google-Fu, but I am lacking in skill today apparently.

I am using Oracle SQL Developer 4.1.5.21 Build MAIN-21.78
I used to use a really old version from 2010 (can't remember the version number), and it never made this suggestion, which is what has me stumped.

Essentially, I have the following query:

  SELECT
          STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg AS "Semester"
        , MIN(STDT."Application_Date") AS APP_DATE
  FROM INT_COMMENCING_APPS STDT
  WHERE 1=1
  AND STDT."Availability_Year" >= 2017
  GROUP BY STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg

It runs beautifully, no errors, and returns the expected data. HOWEVER, oracle developer tells me I'm stupid and wrong and should go die...okay maybe not that bad. But it does give me a query hint telling me I am wrong:

SELECT list inconsistent with GROUP BY;
amend GROUP BY clause to:
          STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg
        , MIN(STDT."Application_Date")

Notice how it's telling me to add the aggregated MIN function to my GROUP BY clause, what gives? I have been writing SQL for years now and this is the first time I've ever been told to do such a thing...frankly, I'm ignoring Oracle for now, because it's being a jerk and can't tell me politely why. That being said, I would like an explanation from the community if anyone has one. :)

Thanks!

Edit 09/12/16

I just noticed that there is a pattern to this query suggestion. What I didn't mention above was that the above query was inside a CTE! Because...why the hell would that matter, but apparently it does.

Outside a CTE, Oracle SQL Developer doesn't care that the aggregate isn't in the group by clause (which it shouldn't regardless). However, as soon as the query is inside a CTE, BAM, it gets all hormonal and tells me it wants chocolate...I mean it tells me to add the aggregate to the group by clause.

Example (and it doesn't matter if I reference the CTE in the query):

WITH APPDT AS (
  SELECT /*+ materialize */
          STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg AS "Semester"
        , MIN(STDT."Application_Date") AS APP_DATE--<=this aggregate
        --SQL Developer tells me to add the aggregate to the
        --GROUP BY clause below
  FROM INT_COMMENCING_APPS STDT
  WHERE 1=1
  AND STDT."Availability_Year" >= 2017
  GROUP BY STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg
)

  SELECT
          STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg AS "Semester"
        , MIN(STDT."Application_Date") AS APP_DATE--<=this aggregate
        --SQL Developer doesn't tell me to add the aggregate
        --to the GROUP BY clause below
  FROM INT_COMMENCING_APPS STDT
  WHERE 1=1
  AND STDT."Availability_Year" >= 2017
  GROUP BY STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg

Looks like a bug in (the parser of) the specific version of SQL Developer (4.1.5.21) you use.

Update to the most recent version and if it shows the same behaviour, send a bug report to Oracle.

You could try to find a smaller example (something like the below, if it does exhibit the same buggy suggestions):

CREATE TABLE t
  ( a INT NOT NULL,
    b DATE NOT NULL
  ) ;

-- no suggestions
SELECT /*+ materialize */ a, MIN(b) AS min_b
FROM t
GROUP BY a ;

-- buggy suggestions
WITH ct AS
  ( SELECT /*+ materialize */ a, MIN(b) AS min_b
    FROM t
    GROUP BY a
  )
SELECT *
FROM ct ;


Buffer Cache Size when Page Life Expectancy Drops I'm troubleshooting an issue with one of our SharePoint databases, essentially huge drops in PLE which I think are caused by increased I/O activity (I see increased reads, writes and lazy writes at the same time)

Buffer Cache Hit Ratio at this time is typically 96%..

  Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Oct 19 2012 13:38:57 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2  (Build 9200: ) (Hypervisor)

I wanted the check the size of the buffer cache at the exact time of PLE drop, so I'm running the following query which I think gives me the size in MB:

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%'
AND counter_name = 'Database Pages'

SELECT ((@total_buffer * 8) / 1024) AS CacheSizeMB

The size I'm seeing does not make sense (to me, i mean), typically 250-350MB. I was hoping I would be seeing a large size, then I could check which tables have the most pages in the cache, which would hopefully lead me to which LOB objects were being retrieved/uploaded thus causing the problems.

Do I also need to be looking at the plan cache? i.e. could the PLE drop be caused by some heavy table scans?

SQL Server is allocated a MAX memory of 12GB, from an available 16GB. How does that correlate with Buffer Cache Size?

If PLE is dropping to values of 0-5 then surely the Buffer Size should be larger...?

I'm troubleshooting an issue with one of our SharePoint databases, essentially huge drops in PLE which I think are caused by increased I/O activity (I see increased reads, writes and lazy writes at the same time)

I can see from the question that you have SQL Server 2012 SP1. There was a Bug in SQL Server 2012 which forced PLE to plummet but that was fixed in SQL Server 2012 SP1 CU4. Now since ** SQL Server 2012 SP3** has been released I suggest you apply SP3 and see if the issue subsides.

  I wanted the check the size of the buffer cache at the exact time of PLE drop,

You can't use perfmon counter Buffer Manager: Database Pages to check buffer size. As per BOL what it signifies is:

  Indicates the number of pages in the buffer pool with database content.

So you can see it does not gives information about complete Buffer Pool. When you define SQL Server max server memory the value set in max server memory becomes the buffer pool size. As such from SQL Server 2012 onward the buffer pool has less significance. Previously it was both consumer and the provider of memory. From SQL Server 2012 onward its just the consumer. To see the contents of the buffer pool(to some extent) you have DMV sys.dm_os_buffer_descriptors.

Below query returns cached page count of each database.

SELECT COUNT(*)AS cached_pages_count
    ,CASE database_id
        WHEN 32767 THEN 'ResourceDb'
        ELSE db_name(database_id)
        END AS database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) ,database_id
ORDER BY cached_pages_count DESC;

If you want to get pages cached for each objects in particular database

SELECT COUNT(*)AS cached_pages_count
    ,name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
    INNER JOIN
    (
        SELECT object_name(object_id) AS name
            ,index_id ,allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p
                ON au.container_id = p.hobt_id
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name
            ,index_id, allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p
                ON au.container_id = p.partition_id
                    AND au.type = 2
    ) AS obj
        ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC;


How can I find out which statistics are evaluated by the optimizer? My enterprise has a farm of many nearly-identical, isolated production environments.  Each environment has a SQL Server 2005 database server, each supporting a database with nearly identical schemas.  Ideally each database would have the same schema.  Enhancements to the schema would be managed centrally, in a model schema.  The schema changes would be coordinated with rollout of new versions of the client software.

Unfortunately, as time has passed, sysadmins have added ad-hoc statistics entities to some of these databases, and as a result, we see inconsistent performance among our production systems.

We would like to either incorporate the ad-hoc statistics into the model schema, or rid them from the production servers.  However we cannot confidently do either unless we know what effect they are having on query performance.

It seems that there should be a way to see what the Query Optimizer is doing, in terms of accessing statistics entities as it evaluates different execution plans.  Is this information available anywhere?

Is there a SQLTrace option which will report it?

Something like sys.dm_db_index_usage_stats would be helpful.  Is there a DMV like this which counts statistics usage instead of index usage?

I don't know of any exposed or documented way that SQL Server keeps track of this as a separate statistic (no pun intended). But if you monitor your server for expensive queries (long duration, high CPU, high I/O etc) and collect the plans for those queries, you may be able to derive some of that information from the plans you've collected - but keep in mind that not all statistics will be represented (some details on that here). If you know of queries off-hand that are slow and you suspect the problem may be due to specific statistics or missing statistics, you can generate an actual execution plan for that query and inspect the plan - you'll often find evidence such as a missing statistics warning or a huge discrepancy between estimated and actual rows (and the latter is why generating an estimated plan only is kind of worthless).

SentryOne Performance Advisor product is one way to help with the collection of high-impact queries ("Top SQL") and the execution plans associated with them at runtime, since they are collected and saved for you over time. Also Plan Explorer is a free tool that can generate plans or open existing plans you've collected, often highlighting some of these issues much easier than viewing the graphical showplan in Management Studio or parsing the XML yourself.*


Get postgres snapshot from Amazon RDS I can not find a simple way to get a copy of the nightly backup I take of my Amazon RDS postgres instance so I can restore it on my local machine to play with.

Right now I'm doing a pg_dump when I need a copy, but it would be nicer (and faster, presumably) to be able to download those snapshots. Is this not possible?

You can't download a snapshot from RDS you have to use a tool like pg_dump

This has already been answered multiple times on regular Stack Overflow:
https://stackoverflow.com/questions/14916899/download-rds-snapshot


Transaction log records in sql server Based on Silberschatz's Database system concepts book, log file for a transaction has 3 records,

1 record for transaction start: T_i start,
1 for transaction changes on write(x) operation: T_i,X,V_1,V_2, where X is the data item that has been changed from V_1(old value) to V_2(new value).
And 1 for transaction commit(or abort),T_i commit.

How can I see these log records in SQL Server? how can I see recovery phase (analyses, redo and undo) in SQL Server?

Edit:
The given link in comment, will show me the table log that which executed transaction for example has delete command or update command. I want to show for example which changes Transaction T_i has made to a data item.
For example T_1,A,100,150 means transaction T_1 has changed the value of A from 100 to 150.

Based on Silberschatz's Database system concepts book, log file for a transaction has 3 records

That's the minimum you'd need for replay and recovery of individual transactions without any optimizations. There may be many more than 3 for a single CRUD item in SQL Server.

  How can I see these log records in SQL Server?

The one I use to see the active log is sys.fn_dblog (Link to Remus's log post). Note that this function isn't documented and can/will change and doesn't show things such as Hekaton logs which need to be cracked using sys.fn_dblog_xtp which again isn't documented.

  how can I see recovery phase (analyses, redo and undo) in SQL Server?

Through many hours of painstaking work. You can use sys.fn_dump_dblog() to look at transaction log backups that are taken after redo and undo has run.

Since the SQL Server Log records and files aren't documented, except for physical and logical architecture, you'll just have to search the internet and make your own best guesses.

If you're asking because you'd like to make software to deal with the log, then you'll need to get in touch with Microsoft Partner Resources. Otherwise, you're on your own, armed with the collective knowledge of the internet and volumes of your favorite beverage.

  For example T_1,A,100,150 means transaction T_1 has changed the value of A from 100 to 150.

The SQL Server log isn't really human readable, though the functions help. I stated before, it's not always that easy as change 100 to 150.

Let's say you changed 'Hi' to 'Sean'. This might cause a forwarding record, or a page split. It might be the first record in a table, or a heap, or be changed because of multiple indexes.

I'm not trying to put you off, just that academia and the real world and generally two very different things.


54001 stack depth limit exceeded in recursive stable function I have a function which calls itself to resolve a hierarchy. Without any volatility declaration, it gives me the desired result (that means the recursion ends). As a performance optimization, I declared the function as STABLE and executed it again. Now it always aborts with the above error, telling me to increase the max_stack_depth which sounds like endless recursion to me.

What is happening here?

A function may not see the same state of the database depending on whether it's VOLATILE or not, when this state is changing during the execution of the SQL statement it's called from, either because of the statement itself or because of another transaction if the isolation level is
Read  Committed.

This is documented in https://www.postgresql.org/docs/current/xfunc-volatility.html

  For functions written in SQL or in any of the standard procedural
  languages, there is a second important property determined by the
  volatility category, namely the visibility of any data changes that
  have been made by the SQL command that is calling the function. A
  VOLATILE function will see such changes, a STABLE or IMMUTABLE
  function will not. This behavior is implemented using the snapshotting
  behavior of MVCC (see Chapter 13): STABLE and IMMUTABLE functions use
  a snapshot established as of the start of the calling query, whereas
  VOLATILE functions obtain a fresh snapshot at the start of each query
  they execute.

If your function reads the database and follows different paths depending on what it reads, it's quite possible that it goes into infinite recursion only when it's not VOLATILE. Looking at the code might be revealing.


CALCULATE TOTAL NUMBER OF ESTIMATED HOURS I had a question regarding a small task I've been assigned. Below I have pasted the question and a screenshot of the database layout. Now I just don't understand what exactly I'm meant to create, a user defined function maybe? But how?

Question: Calculate and return the total number of estimated hours involved in a given stage at the date of the estimate (i.e. before any further changes to estimated hours were made).

(Screenshot) - Table data from: CHARGE_GRADE, TASK_TYPE, TASK, CHARGE_GRADE, ASSIGNMENT, EMPLOYEE.

When I look at the question, it is not important what you create to achieve the result: calculate and return the total number of estimated hours involved in a given stage at the date of the estimate.

I would advise though to just create a query to return the result.


Unique Constraints on Nullable Columns in SQL Server 2005 In this one project I am working on, I need to set a particular field to be unique (not a problem!) but if the field is null I want the constraint to be ignored. In Sql Server 2008 I use filtered index as shown below but this is not available in earlier versions of SQL!

CREATE UNIQUE NONCLUSTERED INDEX User_UserName_IUC
ON [User] (pinNr)
WHERE UserName IS NOT NULL

But I don't think this is available in SQL Server 2005. In fact this blog post indicates there is a workaround using a trigger to check for uniqueness. Does anyone have an example of this? or maybe an alternative?

Unfortunately upgrading to SQl Server 2008 is not an option for this particular client!!

Actually, I had to do something like this once. It involved creating a computed column that takes the value of the Unique column when is not NULL and the value of the primary key (with some other logic to make it impossible to clash with the values on the unique column), and making the unique index on that column. You can see an example of this and the trigger method here.


How to change SESSION_CACHED_CURSORS for all sessions how Can I do to change SESSION_CACHED_CURSORS for all sessions?

I found on google and oracle website:

Alter Session... but I need to changed it for all the session and not only for one of them.

How can I do it?

UPDATE 1:

SQL> alter session set session_cached_cursors=800 scope=both sid='*';
alter session set session_cached_cursors=800 scope=both sid='*'
                                             *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION

The ALTER SYSTEM SET clause for this parameter is only valid with the SCOPE=SPFILE option, so you can not change the parameter value with this dynamically.

You can change the value for all other sessions, e.g as below:

begin
for c in (select sid, serial# from v$session) loop
   dbms_system.set_int_param_in_session(c.sid,c.serial#,'session_cached_cursors', 100);
end loop;
end;
/

This however will not have any effect on the sessions opened after this point.
For that, you have to use ALTER SYSTEM as above and restart the database.


Restore MSDB data to a new server I'm trying to move everything from a SQL Server 2008 R2 (enterprise) instance to a new instance on a different server.  To do this, I thought (after some research) that I'd be able to move the system databases - master, msdb and model.

To accomplish this, I've got backups of all my system and user database, and I'm trying to restore them to the new instance.  Note that the new instance is the same version of SQL, but the IP address, instance name and port are all different.

I've restored the master database successfully.  But when I try to restore the msdb database it just doesn't seem to work.

The command I'm using is:

restore database msdb from disk='\\webapp\SQLBackups$\msdb.bak' with replace,recovery

and the response I'm receiving is:

  Processed 2912 pages for database 'msdb', file 'MSDBData' on file 1.
  Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1. Msg
  3283, Level 16, State 1, Server SQLServerName\Instance_Name, Line 1
  The file "MSDBLog" failed to initialize correctly. Examine the error
  logs for more details. Msg 3013, Level 16, State 1, Server
  SQLServerName\Instance_Name, Line 1 RESTORE DATABASE is terminating
  abnormally.

If I try to execute:

restore database msdb with recovery

I receive:

  The database cannot be recovered because the log was not restored.

I didn't have a log backup, because the msdb database was in 'simple' recovery mode.  So I went back to the original server, switched recovery mode to 'full', created a new full backup and a log backup, then tried to restore this new backup, but still got the same result.  If I try and access anything within SSMS I just get a message saying the msdb database cannot be opened as it is in the middle of a restore.

So I seem to be stuck.  I cannot use the server as it thinks it's mid restore.  I cannot drop the msdb database as it's a system database.  I cannot complete the restore because whatever options I try, with whatever backup files, I still get the message "The file "MSDBLog" failed to initialize correctly".

Any ideas?

I did that kind of migration and I never want to do it again. But if you really want to do it, it is feasable. Here's my recommandations:

Make sure you have the exact same version on both instance.

select SERVERPROPERTY('ProductVersion')
Migration will be way easier if the root folder is in the same path. If you can't have that, it's much more complicated and I strongly suggest taking another path. BTW I did that with SQL 2000. Now I think you can copy over the ROOT folder in one shot. Some files like FullText will be failing if the catalogs are not migrated and replication snapshot folder and etc... Take note of all the datafiles path with:

select * from master..sysaltfiles
If you can't have the user databases at the same location, no big deal. You'll attach them later.
Start the service and pray.

Now back to restoring MSDB and MODEL.
In your particular problem. Your last restore was done without recovery. The MSDB is inconsistant and can't be recovered. I suggest you restart SQL Server in Single User Mode (using -m startup parameter) then DROP MSDB and restore it again.


What is the actual lowest possible positive REAL number MSDN says  that the range of REAL numbers is - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38. Apparently the true lower limit is much lower. The following script populates a REAL column with 1.401298E-45:

CREATE TABLE a
  (
    r1 REAL NULL ,
    r2 REAL NULL ,
    r3 REAL NULL
  ) ;
GO
INSERT  INTO a
        ( r1, r2 )
VALUES  ( 1.18E-37, 10 ) ;
GO
DECLARE @i INT ;
SET @i = 1 ;

WHILE @i < 20
  BEGIN ;

    UPDATE  a
    SET     r1 = r1 / r2 ;

    SELECT  r1 ,
            r2
    FROM    a ;

    SET @i = @i + 1 ;

  END ;
GO
DROP TABLE a ;

r1            r2
------------- -------------
1.18E-38      10

(snip)

r1            r2
------------- -------------
1.401298E-45  10

Can anyone tell me what is the actual lowest possible positive number?

The minimum positive (subnormal) single-precision floating-point value is 2−149 ≈ 1.4 × 10−45. The minimum positive normal value is 2−126 ≈ 1.18 × 10−38 (reference).

DECLARE
    @r1 real = POWER(2e0, -126),
    @r2 real = POWER(2e0, -23)

SELECT
    @r1,
    @r2,
    @r1 * @r2,
    CONVERT(binary(4), @r1 * @r2);

For double-precision, the minimum positive subnormal is:

DECLARE @r1 float = POWER(2e0, -1075);

SELECT @r1, CONVERT(binary(8), @r1);


Query is faster with additional JOIN SELECT COUNT(DISTINCT User_id) FROM Session
GROUP BY Floor_id;

SELECT COUNT(DISTINCT User_id) FROM Session
JOIN Floor ON Floor.id = Floor_id
GROUP BY Floor_id;

The second query runs 20x faster than the first query.

Floor_id is a FK to Floor.id.

Why is this the case and how should we know when we can make use of JOINs to speed up queries?

CREATE TABLE `Session` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `startTime` datetime NOT NULL,
  `endTime` datetime NOT NULL,
  `ssid` varchar(45) DEFAULT NULL,
  `AccessPoint_id` int(11) DEFAULT NULL,
  `User_id` int(11) NOT NULL,
  `Floor_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique` (`User_id`,`AccessPoint_id`,`startTime`),
  KEY `fk_session_AccessPoint1_idx` (`AccessPoint_id`),
  KEY `fk_session_User1_idx` (`User_id`),
  KEY `time` (`startTime`,`endTime`),
  KEY `fk_Session_Floor1_idx` (`Floor_id`),
  CONSTRAINT `fk_Session_Floor1` FOREIGN KEY (`Floor_id`) REFERENCES `Floor` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_session_AccessPoint1` FOREIGN KEY (`AccessPoint_id`) REFERENCES `AccessPoint` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_session_User1` FOREIGN KEY (`User_id`) REFERENCES `User` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1099568396 DEFAULT CHARSET=utf8

You want faster?  Add INDEX(floor_id, user_id).


How to set constraint sql 2 colume, Data is not available elsewhere and must be used only How to set constraint sql 2 colume, Data is not available elsewhere and must be used only.
Example is OK

NAME
REV
ID
PI

A
0
1
PA001

A
1
1
PA001

Example is Error

NAME
REV
ID
PI

A
0
1
PA001

A
1
1
PA002

When ID = 1 then PI is PA001 Only
We can't insert Any data to table except PA001 when ID = 1

The requirement.is unclear. More examples/better specification is needed..
However, i asume that after an ID is " added " to PI, this becomes " paired" and can't be paired with different ID/PI.

create a pairing table (ID,PI) with PK on Id and Unique on PI. Then add FK from your table to this new table either on (PI,ID) or an identity column

There is no simple constraint that would do what you need. What can be used is a CHECK constraint with an function that would do the check by counting ID/PI pairs with same halves. However this is not very optimal when it comes to performance


Strange Postgres Hstore syntax with curly braces {key=value} I'm using the following program https://sourceforge.net/projects/jailer/ to extract a subset of data from my database with correct associations following my foreign key constraints. The program outputs raw SQL insert and update statements than can be loaded into my development database - all very nice and handy. However - I recently started using Hstore in postgres and now I'm encountering some strange syntax in the SQL files. If I have a table witch has a column of type store then update and insert statements are outputted like so:

Update public.some_table set brand='Ford', model_name='Focus', additional_attrs={engine_size=1.6, weight=1100}, created_at=timestamp '2013-11-05 17:21:38.667085', updated_at=timestamp '2013-11-05 17:21:38.667085' Where id=246747;

Witch gives me the following error, when I try to execute it:

ERROR:  syntax error at or near "{"
LINE 1: ...rand='Ford', model_name='Focus', additional_attrs={engine_si...
                                                             ^

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

ERROR: syntax error at or near "{"
SQL state: 42601
Character: 81

As far as I know from postgresql hstore {key=value} is not a valid syntax so my questions are:

Is it in fact valid syntax?
Why would the program output syntax like this, is it likely a bug in the program or something more related to postgres e.g. the postgres jdbc driver?
Any suggestions on how I might overcome this problem?

It turns out it was a bug in Jailer ;-)

https://sourceforge.net/p/jailer/discussion/700499/thread/4db0ed1e/?limit=25#9eb5


Patroni : How to handle a replica which has been disconnected from primary for long time? Let's say I am using asynchronous streaming replication with the below configuration in a 3 node cluster with Postgres 10.4 and Patroni 1.4.4
    bootstrap:
      dcs:
        ttl: 30
        loop_wait: 10
        retry_timeout: 10
        maximum_lag_on_failover: 1048576
        postgresql:
          use_pg_rewind: true
          use_slots: true
          parameters:
            max_wal_senders: 10
            wal_keep_segments: 100
            max_replication_slots: 10

Let's assume that one of the replica nodes suddenly loses its connection to primary for a long time.

In this case I think the size of WAL on the primary will keep on growing as it is not being consumed by the disconnected replica's replication slot. So is there any setting in patroni configuration which will remove the replica and remove its replication slot if it is disconnected from primary for x time duration?
What is the recommended way to handle this case?

I would assume you are monitoring your DB cluster health, so a missing replica would pop up very soon.  Also, it is a must to monitor disk space (running out of it might bring you into a situation that is not very easy to solve), so that would also catch this (later than sooner, usually).

Once you discover you have a replica that fell back, you have to investigate why it did so, and fix it - or remove the host from Patroni altogether.  If under disk space pressure, remove the replication slot to free up WAL space.  In a cloud setup, often simply terminating the host will solve all this by bringing up a new host.  In any case, once you have a functioning host, you might have to reinit the Patroni node.

On the other hand, I'm afraid currently there is no mechanism for fencing off replicas that doesn't appear to come back (be it any actual implementation from removing the replication slot to anything more complex than that).


Postgres: permission denied for relation for read_only user I am trying to set up a new read only user in postgres. I did the following steps to create the new user:

sudo -u postgres createuser --interactive
    name: read_only_user
    superuser: n
    create database: n
    Create new roles: n

Then I logged into postgres with the postgres user and executed the following commands:

ALTER USER read_only_user WITH PASSWORD '123456';
GRANT CONNECT ON DATABASE myDB TO read_only_user;
GRANT USAGE ON SCHEMA public TO read_only_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO read_only_user;

But if I execute the following command:

SELECT * FROM mytable;

I get the follwing error:

ERROR:  permission denied for relation mytable

When logged in with the postgres account I can view the following settings - but I do not see what is missing. I checked a lot of other Questions/Answer here but could not find the reason for the missing permission.

List of roles:

Role name        |                         Attributes                         | Member of
-----------------+------------------------------------------------------------+-----------
 some_user       | Superuser, Create role, Create DB                          | {}
 read_only_user  |                                                            | {}
 postgres        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

List of databases:

   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 myDB      | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/postgres         +
           |          |          |            |            | postgres=CTc/postgres+
           |          |          |            |            | read_only_user=c/postgres
[...]

List of schemas:

  Name  |  Owner   |  Access privileges         |      Description
--------+----------+----------------------------+------------------------
 public | postgres | postgres=UC/postgres+      | standard public schema
        |          | =UC/postgres+              |
        |          | read_only_user=U/postgres  |

Access privileges:

 Schema |       Name        |   Type   |     Access privileges           | Column privileges | Policies
--------+-------------------+----------+---------------------------------+-------------------+----------
 public | mytable           | table    | postgres=arwdDxt/postgres+      |                   |
        |                   |          | read_only_user=r/postgres       |                   |
 public | mytable_id_seq    | sequence | postgres=rwU/postgres+          |                   |
        |                   |          | read_only_user=rU/postgres      |                   |

Instead of granting the access inside the shell try to do that outside the postgres shell like this

$ sudo -u postgres psql -d myDB -c "GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only_user"

$ sudo -u postgres psql -d myDB -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user"


dump files maxing C drive with corrupt mdf I have some databases with corrupt mdf and my C dive on the SQL server is getting maxed out with error dump files,

I am not asking how to troubleshoot why  these files are being created, I already know what the problem is, why they are being created and what I need to do to correct the underlying issue.

what I would like to know is how to prevent or limit the creation and retention of these dump files so they don't max out my C drive while I am doing repairs

thanks

You should limit your error log size. According to support, you can accomplish this by executing on the target instance the following script:

USE [master];
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'ErrorLogSizeInKb', REG_DWORD, 5120;
GO

Another thing you might want to check is where you have your TempDB stored. If it's on C drive, than perhaps you should move it to another drive.

If you'te getting memory minidumps due to crashes of the database engine, than you should follow Dan Guzman's suggestion and create a script to move or delete them.


How to set up SQL Server 2017 as a service I have a question about what would be the best way to set up SQL Server 2017 as a service.

I have clients that buy a SQL Server database as a service, as if it were an Azure or AWS instance, where they do not have access to the server.  They only have a user and password in the instance, and I need to hide from those users the server configurations that are accessible through SQL Server Management Studio or by T-SQL commands, such as memory allocated to the instance, number of CPUs, etc.

In SQL Server 2016 I accomplished this by removing the permission on the master..xp_msver view.  Without this permission they cannot, for example, see instance properties (right click on the server in SSMS) and some data from the Facets form. Even without this permission they can log on and doing everything they need in their databases.

But in SQL Server 2017 this no longer works, and if I deny permission from this view it is no longer possible to log in SQL Server.

I know it may not be correct to use the standard SQL Server as a service, maybe it has not been prepared for this, but is there a right way? Or what alternatives can be used?

I've spent a lot of time on Google trying to find alternatives, but for SQL Server 2017 I did not find anything that hides the server settings.

Note: My users use SQL Server Management Studio...

Revoking that one view was never a good way to protect your instance.  The most straightforward way to do this is to make the user the owner of their database, but have no other rights on the instance, even to list the other databases.  EG:

use master
create database a
create login a_user with password='P@sssword*****'
alter authorization on database::a to a_user

revoke view any database to public

Now a_user will be able to connect (with SSMS or otherwise) and will see only one database.


Mysql Percona 5.6 Configuration File I've just installed a Percona instance of Mysql on an Amazon server and can not find the my.cnf file anywhere. I found auto.cnf in /var/lib/mysql but there is nothing in that file like what I have seen for my standard mysql installs.

I'm using:

mysql  Ver 14.14 Distrib 5.6.41-84.1, for Linux (x86_64) using  6.2

Previously I've used 5.6.14 and 5.6.35 I guess this version changed a bit?

I tried mysqld --help --verbose | grep my.cnf | tr " " "\n", find . -iname "my.cnf", find / -iname "my.cnf", locate my.cnf, and whereis my.cnf. The whereis gave back a changed the shell, but had the same end result as all other commands which was no my.cnf found.

The whereis appended my: before my shell prompt e.g. [root@ip-ip_address /]#.

Mysql is running but from all SO threads I read there is no global variable I can select to find the conf file. Does anyone know how I can find where my configuration file lives?

No there is no global variable for the config file locations. mysqld --help --verbose is best to show which ones are used.

Take a look at the linux processlist to see how mysqld is executed by  init/service/systemd, that might specify a file.

Otherwise you might be running on a default config with no configuration file.


fighting filter (nested loops) execution plan from Oracle My query is:

SELECT category, count(*)
FROM large_data_table t
WHERE
(t.val IN (SELECT val FROM SMALL_TABLE WHERE id = 1))
OR
(t.val IN (SELECT val FROM SMALL_TABLE WHERE id = 2))
GROUP BY GROUPING SETS ((category), ());

The resulting execution plan is like this:

SELECT STATEMENT
  SORT (GROUP BY ROLLUP)
    FILTER
      large_data_table  TABLE ACCESS (FULL)
      SMALL_TABLE  TABLE ACCESS (BY INDEX ROWID)
      SMALL_TABLE  INDEX (RANGE SCAN)
      SMALL_TABLE  TABLE ACCESS (BY INDEX ROWID)
      SMALL_TABLE  INDEX (RANGE SCAN)

Where FILTER is, essentially, NESTED LOOP JOIN which never finishes as NESTED LOOPS often do on large tables.  (the two subqueries on the small table return, say, 100 rows each).
I am looking for a way to tell Oracle to NOT use the stupid FILTER here.  I've tried sticking USE_HASH hint everywhere including some places I'd rather not talk about now....
GROUPING SETS seem to play a role in this, but getting rid of them is very difficult here.  The one reasonable thing that works is to replace OR with a UNION, but it doesn't cover all cases either.

On 11R2 I usually start with gathering more statistics for the tables:

exec DBMS_STATS.GATHER_TABLE_STATS ('SCHEMA, 'SMALL_TABLE', estimate_percent => '100');
exec DBMS_STATS.GATHER_TABLE_STATS ('SCHEMA, 'LARGE_DATA_TABLE', estimate_percent => '10');

If that helps then table preferences for automatic jobs can be set:

EXEC DBMS_STATS.SET_TABLE_PREFS ('SCHEMA', 'SMALL_TABLE', 'ESTIMATE_PERCENT', '100');
EXEC DBMS_STATS.SET_TABLE_PREFS ('SCHEMA', 'LARGE_DATA_TABLE', 'ESTIMATE_PERCENT', '10');

If large_data_table is really big (tens GB and more) then 1% or something like that may be needed.

And do not believe that in dba_tables sample_size=num_rows. For big tables actual auto sample size is much much lower. I had the SR with Oracle about that. They found actual sample percentage only from session trace file. It was ~0.004% for 170GB table.