SQL Server restarts, unable to find the reason One of our SQL Server instances restarts itself in a clustered environment, but it is not fail-overed. When I checked the error logs, event viewer and cluster log I'm unable to find any particular reason for what it causing these restarts, but in failover cluster manager --> Roles --> Critical events I found this error:

Cluster resource 'SQL Server' of type 'SQL Server' in clustered role
'SQL Server (MSSQLSERVER)' failed. Based on the failure policies for
the resource and role, the cluster service may try to bring the
resource online on this node or move the group to another node of the
cluster and then restart it. Check the resource and group state using
Failover Cluster Manager or the Get-ClusterResource Windows PowerShell
cmdlet.

Can I know if there is any specific reason for SQL Server restarts with the above error, or is there anything we can configure for the SQL Server error logs to capture the errors of these restarts or failovers or any audits?
Both Windows servers are not rebooted
Server is physical server
It is an Active/Passive clustered environment
Both servers are running on Windows Server 2012 R2
Microsoft SQL Server 2014 Enterprise Edition (64-bit) on Windows
I verified all 6 errorlogs, and events in event viewer, and cluster.log file also.

One clue would be to look in the system_health extended events session, specifically sql_exit_invoked event.  This will give you the time that SQL Server started to shut down, and you can look in the "shutdown_option" value for additional details.
This will tell you a little bit about how the service was stopped (through T-SQL, or through the services snap-in / configuration manager).
Once you know the when and how, you can dig back in to the SQL Server error log, and / or Windows Event Viewer, from that time period and see if there are more details about what initiated the shutdown.


Script to Delete Tables With A Prefix I am running SQL Serve 2008 and we have a few test tables that were obviously used for testing.  The prefix of the tables are Test_, my question is, will this script delete all tables with the prefix of Test_ on the database I execute it on?

declare @cmd varchar(4000)
declare cmds cursor for
select 'drop table [' + Table_Name + ']'
from INFORMATION_SCHEMA.TABLES
where Table_Name like 'Test%'

open cmds
while 1=1
begin
  fetch cmds into @cmd
  if @@fetch_status != 0 break
  exec(@cmd)
end
close cmds;
deallocate cmds

will this script delete all tables with the prefix of Test_ on the database I execute it on?

Yes, it will DROP tables. DELETE is normally used with deletion of data. Your script will physically drop the tables.
Also, please dont use INFORMATION_SCHEMA and there is no need for a cursor. Your script can easily be written using dynamic sql.

EDIT : In comments, @MartinSmith pointed to one of his excellent answer - that I was not aware ... but is an eye opener when concatenating. (To be honest, I have never faced, but now will be aware of the gotcha ! Thanks again Martin !)

The ONLY guaranteed mechanism are the following:

Use cursor to loop through the rows in specific order and concatenate the values

Use for xml query with ORDER BY to generate the concatenated values

Use CLR aggregate (this will not work with ORDER BY clause)

DECLARE @sql nvarchar(max) = N'';

SELECT @sql = (select N'drop table ' + QUOTENAME(t.name) + N';'
FROM sys.tables AS t
WHERE t.name LIKE N'Test_%'
order by object_id
 FOR XML PATH(''), TYPE
).value('.','nvarchar(max)')

print @sql ;
-- caution below will ACTUALLY drop the tables.
-- review the output and then uncomment below line
EXEC sys.sp_executesql @sql;


Unable to connect to the Reporting Services instance through SSMS with windows authentication I am trying to connect to Reporting Services instance SSRS with SSMS so that I can update some of the parameters on display of remote errors and retention of execution log statistics.

I receive the error message:

The username or password is not valid or you do not have permission to access this server. (Microsoft.SqlServer.Management.UI.RSClient)

Server 2016 with SQL Server 2017.  My network account is an admin on the local server and I have no problem connecting to all DB's on the sql side.

Since you have access to the server i'd reccomend you log in and use Reporting Services Configuration Manager to change whatever settings you want. If you cant do it via config manager then you may need to edit the config files.

From memory to access SSRS via SSMS requires permissions on the service. Not the database.


Getting records for yesterday, but per hour I am currently getting all data for yesterday from my database but I want to get data for each hour. This is my query:

select
    camera_id,
    count(*) as snapshot_count
from snapshots
where created_at >= TIMESTAMP 'yesterday'
  and created_at <  TIMESTAMP 'today'
group by camera_id

It's giving me all records for yesterday, but how could it be possible to get records from yesterday but for each hour?

PS: I want to run this in one query, not 24 queries

Use date_part to get the hour part of your timestamp, then add it to your GROUP BY clause.

 select
    camera_id,
    count(*) as snapshot_count,
    date_part('hour', created_at) as hr
 from snapshots
 where created_at >= TIMESTAMP 'yesterday' AND created_at < TIMESTAMP 'today'
 group by camera_id, hr

I generated an example table:

CREATE TABLE ex.snapshots
(
  camera_id integer NOT NULL,
  created_at timestamp with time zone NOT NULL,
)

and populated it with some test data:

INSERT INTO ex.snapshots (camera_id,created_at) (
    SELECT
        (random() * 6)::integer,
        now() - (((random() * 2000)::integer)::text || ' minutes')::interval
        FROM generate_series(1,20000)
    );

After adding an ordering clause, here's the first few rows of output:

  camera_id | snapshot_count | hr
 -----------+----------------+----
          0 |             39 |  0
          0 |             46 |  1
          0 |             59 |  2
          0 |             46 |  3
          0 |             49 |  4
          0 |             48 |  5
          0 |             56 |  6
          0 |             43 |  7


COUNT(condition) counts all rows in the group where condition is not null.

COUNT(*) counts all rows in the group.

COUNT(condition) / COUNT(*) * 100 returns the ratio of the above in percent.


"freeing items" taking forever on inserts, updates and deletes Recently I migrated all my MySQL database tables from MyISAM engine to InnoDB. I'm using the DotCloud MySQL service as my server (http://docs.dotcloud.com/services/mysql/).

After the migration, everything went fine for the first two weeks, but for a couple days I've been experiencing some problems.

At first, all my queries would lock under an update or insert at certain tables. With a SHOW PROCESSLIST; I could see that the locking queries had the state of "freeing items". I started to research and found this:

Why do MySQL threads often show "freeing items" status when the query cache is disabled?

I've changed the innodb_thread_concurrency to 32 as suggested, and that really did help in order to keep the website running well, which means that I'm not experiencing application locks any more.

The problem is that inserts and updates are still slow, some of them taking more than 60 seconds to happen and that is terrible. I'll try to put my database under maintenance and optimize or repair it, but I don't know if that is going to work really well.

Does anyone knows what I could do about it? Thanks!

My knowledge of MySQL is somewhat limited so I may be entirely wrong here, but this is me thinking through your issue given my experience with MySQL w/innodb and other MVCC db's.

If you are seeing processes or threads waiting on eachother it has to be a locking issue.  That's the only explanation.  Given MySQL's structure, it has to be a thread locking issue.  There are no other options.

This leads to a few specific questions:

How is performance writing the log files?  Any issues there?
I there something else going on?  Any deadlocks being reported?
What does top show regarding CPU time?  Sleeping, idle, or wait?
What sort of write concurrency are we talking about?  How many concurrent writers?


Difference between partitioning and fragmentation I'm trying to learn something about distributed databases and I stumbled upon the concepts of fragmentation and partitioning. It seems to me as if both do the same thing and produce similar results (deviding a relation horizontally/vertically/both).

So what's the difference between fragmentation and partitioning?

The terminology depends on the RDBMS. In SQL server: Partitioning is a DB feature that can be used to improve performance and/or manageability of your DB.

Fragmentation happens when your rows in the data pages are non-contiguous due to insert/delete operations. Your DB performance will decrease if you have high fragmentation of your data.


Paged query against timestamp populated by trigger missing items We're having a strange problem with a PostgreSQL 9.2 database that I've been racking my brain trying to figure out.

First some background. We have a "tombstone" table that is populated using a trigger when a row is deleted from a particular table. The trigger does this:

INSERT INTO tombstone (id, delete_timestamp)
VALUES (OLD.id, statement_timestamp() AT TIME ZONE 'UTC');

There is a background service that runs every 30 seconds and checks for items in the tombstone table. The query used by the background service is like this:

SELECT
    id,
    delete_timestamp
FROM
    tombstone
WHERE
    delete_timestamp > @sinceUTC
    OR
    (delete_timestamp = @sinceUTC AND id > @id)
ORDER BY
    delete_timestamp asc, id asc
LIMIT @count;

This query is constructed with two goals: to find new tombstone entries since the last time it was run, and to page through the items if there is a large number. The last timestamp and ID are recorded after each execution of the query to use as the inputs to the next execution of the query. If no results are returned, the last timestamp and ID are not updated.

The problem we are having is this: every once in a while, the background service misses a large number of items in the tombstone table. Recently we had a 10-minute time span where the background service did not pick up any new tombstone items even though there were more than 200 created during that time. The logs for the service show it querying every 30 seconds during that time but finding no items.

I've come up with one scenario that could account for missing items, but it should only miss a few in a very small time window. The scenario I've identified is as follows:

D1 |st1....................|
D2       |st2......|
R                  |.......|

Consider two delete transactions, D1 and D2. D1 starts first. D2 starts and completes while D1 is still in progress. Since the trigger uses statement_timestamp, it will record a the start of D1 as ST1 and the start of D2 as ST2.

If a read (R) starts in the time between the commit of D2 and the commit of D1, then it will see D2 but not D1. It will record the "last timestamp" as ST2, but this is actually after ST1. So the next time the query executes, it will start at a time after ST1 and therefore will miss D1.

As I said, I can see this causing a few occasional misses, but not 10 minutes worth. Also, the missed items were in multiple transactions, not a single large transaction, so the likelihood of this accounting for what we are seeing seems even smaller.

Any ideas on what could cause this issue?

I think you found the main culprit alright. There are more ways to miss rows with this setup. Consider D3 I added to your time diagram:

D1 |st1....................|
D2       |st2......|
D3          |st3......|
R                |.......|
                   ^ -- read query starts here

Transaction D3 starts after D2, but since it's not committed when your read query in transaction R starts, it is invisible as well. It will be included in the next page, but it may still be confusing that it's missing this time. That's how the default transaction isolation level Read Committed is defined. You seem to be aware of it, but for the general public:

  When a transaction uses this isolation level, a SELECT query (without
  a FOR UPDATE/SHARE clause) sees only data committed before the query
  began; it never sees either uncommitted data or changes committed
  during query execution by concurrent transactions. In effect, a SELECT
  query sees a snapshot of the database as of the instant the query
  begins to run.

There is more. Read the manual.

With concurrent access to the DB, various conflicts might stall a DELETE query - which will patiently wait until it can get an exclusive lock on the rows to delete. 10 minutes of missed rows would indicate a (possibly unrelated) problem, I agree with your suspicion. Transactions that remain "idle in transaction" are prime suspects. To diagnose, look for log long running statements or transactions. Some pointers:

Set log_min_duration_statement in postgresql.conf.
Some basics on Logging Difficult Queries in the Postgres Wiki.
The statistics view pg_stat_activity. Related:

Postgres - estimate end time for a long update

check_postgres provides helpful tools.
Related blog.

Database design

You seem to be using data type timestamp. Your INSERT with statement_timestamp() AT TIME ZONE 'UTC' should work correctly for your purpose, but you shouldn't rely on INSERT statements to get this right. Rather move the logic to the table itself. Use the data type timestamptz (timestamp with time zone) for tombstone.delete_timestamp to begin with. This rules out any discrepancies with time zones or DST (daylight saving time). Details:

Ignoring timezones altogether in Rails and PostgreSQL

Also don't rely on the INSERT statement to provide the correct time. Put the logic in the column default (or even in a trigger BEFORE INSERT to overwrite any provided timestamp).

CREATE TABLE tombstone (
  id int NOT NULL PRIMARY KEY  -- assuming PK
, delete_timestamp timestamptz DEFAULT statement_timestamp()
);

Since timestamps are not visible until the transaction is committed anyway, the cheaper current_timestamp (transaction timestamp) may be just as useful as statement_timestamp() - depends on unknown details.

The simpler INSERT statement in the trigger to go with the above:

INSERT INTO tombstone (id) VALUES (OLD.id);

Query

Your current SELECT query can be faster comparing row values, which is also index-backed in Postgres:

SELECT id, delete_timestamp
FROM   tombstone
WHERE  (delete_timestamp, id) > (@sinceUTC, @id)
ORDER  BY delete_timestamp, id
LIMIT  @count;

Details:

Optimize query with OFFSET on large table

Secure alternative

All this aside, timestamps are just not completely reliable for queuing or paging in a multi-user environment. If you need it to be completely reliable, consider a different approach: Lock unread rows before retrieving and mark them as read. The new FOR UPDATE SKIP LOCKED in Postgres 9.5 may prove useful for this:

Postgres UPDATE ... LIMIT 1

Or, if the reading is done by a single user, you don't need locks. Just mark rows you have read. A partial index for unmarked rows makes reading fast.

Upgrade to current Postgres

Postgres 9.2 is getting old. Upgrading to the current version might help for various reasons.


Sybase IQ cache database result I'm using a query that calculates some values on a table with about 11 millions rows. And I need to display the results in real time (on my site), but this calculations need about 1min to execute. The table content changes each 30 mins, so I don't have to recalc the results at each time user reloads the page. How can I cache the results of calculations? Via php (I use odbc) or using some sql statement, some sybase IQ option. Thanks.

solved it by my own. Not optimized, but helpful for me. I insert my calculations into temp table, and add there a column with current date. On a script start I'm checking if table is older then 30mins, and if so, I drop it and crwate again.


Restoring a DB issues I am trying to copy over the live database from one server and restore it on the dev server using SQL Server 2005. I have made a backup of the live db and copied the .bak file over to the dev server. I am trying to run this script and I keep getting errors:

ALTER DATABASE Portal SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE Portal SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE Portal
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Portal.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO

exec dbo.sp_fixusers
GO

ALTER DATABASE Portal SET MULTI_USER
GO

ALTER DATABASE Portal SET ONLINE WITH ROLLBACK IMMEDIATE
GO

Errors:

  Msg 5052, Level 16, State 1, Line 2
  ALTER DATABASE is not permitted while a database is in the Restoring state.
  Msg 5069, Level 16, State 1, Line 2
  ALTER DATABASE statement failed.
  Msg 5052, Level 16, State 1, Line 2
  ALTER DATABASE is not permitted while a database is in the Restoring state.
  Msg 5069, Level 16, State 1, Line 2
  ALTER DATABASE statement failed.
      10 percent processed.
      20 percent processed.
      30 percent processed.
      40 percent processed.
      50 percent processed.
      60 percent processed.
      70 percent processed.
      80 percent processed.
      90 percent processed.
      100 percent processed.
  Processed 307768 pages for database 'Portal', file 'Portal_Data' on file 1.
  Processed 127 pages for database 'Portal', file 'Portal_Log' on file 1.
  RESTORE DATABASE successfully processed 307895 pages in 130.538 seconds (19.322 MB/sec).
  Msg 2812, Level 16, State 62, Line 1
  Could not find stored procedure 'dbo.sp_fixusers'.

You cannot set your database to single-user mode or offline because your database is already in a RESTORING state.

Second, your script tries to use the stored procedure sp_fixusers. This is a custom stored procedure to fix orphaned users. If you need to use this stored procedure, you need to re-create it first, using the script in the link above. But it is possible that your databases uses a different version and it will be better if you use the sp_fixusers from your server/database.


Add multiple rows in a single row with different columns I want to join two tables (they are called Registers and Reads). As a result I would like to obtain 4 columns, corresponding to Registers ID.

According to the next example, by joining the tables, I obtain 2 rows. There could be some cases that I may find/obtain 4 rows because they exist in Reads table.

Table: Registers

    ID      Number    Prod_ID
    331       01       112233
    332       02       112233
    333       03       112233
    334       04       112233

Table: Reads

    Read_Id    Register_Id
      011          331
      012          332

I use this query to link join both tables:

    SELECT rg.ID
    FROM Reads rd LEFT JOIN Registers rg on rd.Register_ID = rg.ID
    WHERE rg.Prod_ID = 112233;

My result is next one:

    ID
    331
    332

What I really want is to obtain just one row, assigning the first result to the first column, second one to the second and so on. In addition, I would like to add an extra column that shows how many columns contain information.

Expected result:

    RegisterID1    RegisterID2    RegisterID3    RegisterID4    Count
        331            332                                        2

Is there any easy way to do this? Thank you very much!

You are on 10g, so PIVOT is unavailable, but still have the good old sum(case ...).

drop table reads purge;
drop table registers purge;

create table registers (id number,  n varchar2(2), prod_id number);
insert into registers values (331, '01', 112233);
insert into registers values (332, '02', 112233);
insert into registers values (333, '03', 112233);
insert into registers values (334, '04', 112233);
commit;

create table reads (read_id varchar2(3), register_id number);
insert into reads values ('011', 331);
insert into reads values ('012', 332);
commit;

SELECT
  sum(case when rg.n = '01' then rd.register_id end) as "RegisterID1",
  sum(case when rg.n = '02' then rd.register_id end) as "RegisterID2",
  sum(case when rg.n = '03' then rd.register_id end) as "RegisterID3",
  sum(case when rg.n = '04' then rd.register_id end) as "RegisterID4",
  count(rd.register_id) as "Count"
FROM Reads rd RIGHT JOIN Registers rg on rd.Register_ID = rg.ID
WHERE rg.Prod_ID = 112233;

RegisterID1 RegisterID2 RegisterID3 RegisterID4      Count
----------- ----------- ----------- ----------- ----------
        331         332                                  2


How to view table data in the newest 6.0 MySQL Workbench I basically fail to view data in the new Workbench version without firing queries. I recall it was clearly possible in the previous version by simply using context menu something like "view table data".

Now in 6.0 I can't see this option anymore. Though I can see "search table data" which gives me next window where I have to input some mysterial query the pattern of which I fail to recognize. (see second screenshot - Start Search button is disabled)

It is not a big problem for me to fire queries, but still this is quite annoying especially if you don't see sql on daily basis using ORM and you just want to take a quick look at the table.

Oracle update - 1, :-)

My apologies to all db evangelists.

If you just want to see the data in a table then do a select for that table in a query editor. That's the most basic operation wrt SQL. Using the table search with some "mysterious query" certainly does not help to accomplish that task. Search is, as the name implies, a search. Put the value you are looking for in the search field and let MySQL Workbench do the search for you. As easy as that.

If you want a mouse only approach then open the schema in the tree and right click on the table you want to see. A context menu appears and the first entry is the select (which takes the limit into account that is set in the preferences, to avoid pulling a potential million rows).

The old "Edit table data" was a very similar shortcut, but that uses the EDIT keyword to tell the editor that the result is editable. That was an intermediate addition as MySQL Workbench had no other way to know if that is the case. In the meantime the application can detect this automatically (a PK must exist, no aggregates, no joins etc.), so the EDIT keyword as well as the shortcut have been removed.


How to return value for key from a jsonb column? I've successfully created the below table on 9.5.1.0:

  CREATE TABLE json_test (
    id serial primary key,
    data jsonb
  );

and successfully inserted this record:

  INSERT INTO json_test (data) VALUES ('{"0":"INVALID","1":"BELOW","2":"EQUAL","3":"ABOVE"}')

and I am successfull in returning the entire record that I inserted via:

  SELECT * FROM json_test WHERE data::jsonb ? '1';

but how do I return the value associated with '1', i.e. "BELOW" ?

This post gives you your answer:

CREATE TABLE json_test
(
  id serial primary key,
  data jsonb
);
INSERT INTO json_test (data) VALUES ('{"0":"INVALID","1":"BELOW","2":"EQUAL","3":"ABOVE"}');

You specify the element that you wish to extract thus:

SELECT id, data->>'1' AS whatever FROM json_test;

And the result is:

id      whatever
1       BELOW

There are a shedload of new json functions in PostgreSQL - there could be more examples (hint PostgreSQL team...).


Default value of serial fields changes after restore Today I found a strange behavior after restoring a PostgreSQL database: the schema of all serial fields default values are trimmed out.

For example:

CREATE TABLE testschema.testtable
(
   id serial,
   name character varying(255),
   CONSTRAINT pk_testtable PRIMARY KEY (id)
)
WITH (
  OIDS = FALSE
)
;

SELECT a.attnum, n.nspname, c.relname, d.adsrc AS default_value
FROM pg_attribute AS a
JOIN pg_class AS c ON a.attrelid = c.oid
JOIN pg_namespace AS n ON c.relnamespace = n.oid
LEFT OUTER JOIN pg_attrdef AS d ON d.adrelid = c.oid AND d.adnum = a.attnum
WHERE a.attnum > 0
  AND n.nspname = 'testschema'
  AND c.relname = 'testtable'

The id's default_value is nextval('testschema.testtable_id_seq'::regclass).

After restore, default_value changes to nextval('testtable_id_seq'::regclass) and INSERT's start to fail as the sequence can´t be found on it's schema.

Backup
$ pg_dump -F c -Z 9 -b -h localhost -U postgres -f backup dbname

Restore
$ pg_restore -U postgres -h localhost -l backup > backup.list
$ pg_restore -U postgres -h localhost --disable-triggers -O -d dbname -S postgres -Fc -L backup.list backup

Is this some backup/restore problem? What am I doing wrong? BTW, PostgreSQL version is 9.1.3 x64 running on Windows (dev machine), but can be reproduced on Linux too.

I ran the same process as you did, but got different results.  Restoring the database using your command didn't work when trying to overwrite the same database because you didn't use the -c --clean option.  Restoring to a new database with the same pg_restore command worked. Inserting new records was still possible however.

Here are the steps I took.

Create the database

  createdb testdatabase

Create the schema

  psql -d testdatabase -c 'create schema testschema;'

Create the table

  psql -d testdatabase

CREATE TABLE testschema.testtable(
  id serial, name character varying(255),
  CONSTRAINT pk_testtable PRIMARY KEY (id)
) with (OIDS=false);

  NOTICE:  CREATE TABLE will create implicit sequence "testtable_id_seq"
  for serial column "testtable.id" NOTICE:  CREATE TABLE / PRIMARY KEY
  will create implicit index "pk_testtable" for table "testtable" CREATE
  TABLE

Insert some records

testdatabase=# insert into testschema.testtable (name) values('person1'),('person2');
INSERT 0 2

Backup the database

pg_dump -F c -Z 9 -b -h localhost -U postgres -f backup testdatabase

Create the backup list file

pg_restore -U postgres -h localhost -l backup > backup.list

Relevant contents of backup.list

  2639; 1262 16468 DATABASE - testdatabase postgres 163; 1259 16472
  TABLE testschema testtable postgres 162; 1259 16470 SEQUENCE
  testschema testtable_id_seq postgres 2643; 0 0 SEQUENCE OWNED BY
  testschema testtable_id_seq postgres 2644; 0 0 SEQUENCE SET testschema
  testtable_id_seq postgres 2633; 2604 16475 DEFAULT testschema id
  postgres 2636; 0 16472 TABLE DATA testschema testtable postgres 2635;
  2606 16477 CONSTRAINT testschema pk_testtable postgres

Restore the backup

pg_restore -U postgres -h localhost --disable-triggers -O -d testdatabase -S postgres -Fc -L backup.list backup

pg_restore sends back errors

  pg_restore: [archiver (db)] could not execute query: ERROR:  schema
  "testschema" already exists pg_restore: [archiver (db)] Error from TOC
  entry 163; 1259 16472 TABLE testtable postgres pg_restore: [archiver
  (db)] could not execute query: ERROR:  relation "testtable_id_seq"
  already exists

Restore to another database

createdb testdatabase2
pg_restore -U postgres -h localhost --disable-triggers -O -d testdatabase2 -S postgres -Fc -L backup.list backup

Did not get any errors

Insert more records

psql -d testdatabase2

testdatabase2=# insert into testschema.testtable (name) values('person2'),('person3');
INSERT 0 2

View records

testdatabase2=# select * from testschema.testtable;

 id |  name
----+---------
  1 | person1
  2 | person2
  3 | person2
  4 | person3

View the table structure for testschema.testtable

pg_dump --schema-only -t testschema.testtable testdatabase2

As you mentioned in your question, postgresql does rewrite the default value

  ALTER TABLE ONLY testtable ALTER COLUMN id SET DEFAULT
  nextval('testtable_id_seq'::regclass);

Conclusion

Using your exact same commands, errors did occur when trying to restore over the same database, but when restoring to a new database, adding new records was possible.

You must add the -c or --clean, to drop existing objects so that they can be restored by pg_restore.

pg_restore -c -U postgres -h localhost --disable-triggers -O -d testdatabase -S postgres -Fc -L backup.list backup

Postgresql did rewrite the default value for your serial column as you mentioned in your question, but insertions were still possible.

Updated Answer

When using the query you provided, it is true I don't see the testschema in the search path after restoring the database.

testdatabase=# SELECT a.attnum, n.nspname, c.relname, d.adsrc AS default_value FROM pg_attribute AS a JOIN pg_class AS c ON a.attrelid = c.oid JOIN pg_namespace AS n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_attrdef AS d ON d.adrelid = c.oid AND d.adnum = a.attnum WHERE a.attnum > 0   AND n.nspname = 'testschema'  AND c.relname = 'testtable';
 attnum |  nspname   |  relname  |             default_value
--------+------------+-----------+---------------------------------------
      1 | testschema | testtable | nextval('testtable_id_seq'::regclass)
      2 | testschema | testtable |

But, when I use \d testschema.testtable I do see the testschema in the definition of NEXTVAL

testdatabase=# \d testschema.testtable;
                                    Table "testschema.testtable"
 Column |          Type          |                             Modifiers
--------+------------------------+-------------------------------------------------------------------
 id     | integer                | not null default nextval('testschema.testtable_id_seq'::regclass)
 name   | character varying(255) |

I found this on Postgresql's forums, which talks about the differences we are both seeing.

http://archives.postgresql.org/pgsql-admin/2009-06/msg00070.php


Patching FileShareWitness for AlwaysOn AG Situation:
We have an AlwaysOn High Availability group with 4 replicas (2 for DR) a listener and an FileShareWitness. I've been tasked with creating a set of instructions for patching all of the servers in the environment.

Problem:
The plan has been based on Pinal Daves article (with some modifications). here

The problem is that when patching the FSW it switches back and forth between replicas.

A colleague has suggested removing the possible failover value from the ip (not from AOAG) to avoid the failover and then putting it back afterwards.

Please advise if this is good idea, or if there is a better way of stopping the AG replica from switching back and forth?

SQL is 2016 Enterprise SP1 running on WS2012R2 Datacenter 6.3 with Hypervisor

Any help will be much appreciated.

Normally, rebooting the File Share Witness should be totally fine in this setup as long as both AG replicas are up and healthy when you patch the FSW server.  So you shouldn't need to remove anything from the WSFC settings to accomplish your plan.

I had a situation a while back where

the file share witness was down for maintenance
the PRIMARY and SECONDARY both switched to the RESOLVING state briefly (about 15 seconds)
then they both returned to their original roles

This was due to the File Share Witness resource running in the same Resource Hosting Subsystem (RHS) process as the AG node resources - when the FSW failed it's "are you up" checks, it caused the whole RHS process to fail, which resulted in the "blip" in the AG.

You can avoid this problem by setting the FSW resource to run in a separate RHS process in WSFC settings:

I blogged in detail about that here: Troubleshooting an AG Failure

If there is something else causing this problem in your case, you can still troubleshoot it using the steps outlined in my blog post:

look at the SQL Server error log
look at the WSFC cluster log
review the AlwaysOn_health extended event session

If you don't want to dig through every single one of these places, you can also use the Failover Detection Utility provided by Microsoft Support.


Better way of re-arranging the indexes for better performance I ran a script to find overlapped and duplicate indexes for a database.

Below is the sample screen for the above said.

Please advise how can I arrange them or what should be the guidelines to make a better indexes before dropping them.

Some of those indexes seem to have a lot of included columns. A table definition will provide context into the width of these columns. What is the clustered index key?

You need to look at the workload being run against this table. What queries are being executed, how often are they being executed, is this going to change in the future?

I would probably start with looking at current index usage statistics to see how indexes are being used (Seeks, Scan, Key Lookups, Updates).

SELECT
OBJECT_NAME([ddius].[object_id]) AS [Table name] ,
[i].[name] AS [index name] ,
i.type_desc,
user_seeks,
user_scans,
user_lookups,
user_updates
FROM sys.indexes i
left outer join [sys].[dm_db_index_usage_stats] ddius on  ddius.index_id = i.index_id
and [ddius].[object_id] = [i].[object_id]
WHERE [ddius].[database_id] = DB_ID()
order by [Table name]

You can also look at the missing index DMV. However, you need to take the information returned with a grain of salt. SQL will allow you to create duplicate indexes, an index creation suggestion is "recommended" for a specific query but there may be combined indexes. Unfortunately, indexing is a bit of trial and error.


How do get correct instance name on Multi instance cluster Just a quick question and somehow unable to figure it out.
We have a Active Active cluster or should i say Multi instance cluster as below. node A and B are part of same window cluster

Node A --> Serv1\SQLINSt1
Node B --> Serv2\SQLINSt2

How can i show the info that node A at a time host instance Serv1\SQLINSt1 and node B other instance Serv2\SQLINSt2 because when i use dmv sys.dm_os_cluster nodes and add SERVERPROPERTY ('instancename') it gives same SQL instance for both the nodes
Also will it be possible to show when both the instances come on says node A while node B host no instance?
Much appreciated thanks

On each instance, run:
SELECT
  ServerAndInstanceName = @@SERVERNAME,
  UnderlyingServerName  = SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS');

If they both show the same UnderlyingServerName then they're both on the same node.


Least recently used replacement strategy - where is pin count stored? I am reading a book on databases and a chapter in it talks about buffer manager and it's replacement strategies.

The two popular replacement startegies mentioned are least recently used - LRU and clock replacement. I am interested in details about LRU. LRU is implemented via queue. When a pin count is set to 0, page in memory is added to queue. When a replecement is needed, memory page from the start of the queue is used.

The book does not describe how pin count (and dirty bit) for a page is stored. Is it stored inside the queue entries? Is it stored as part of the header in buffer pool frames?

Moreover, what happens if a page is accessed while being in the queue? Is it deleted from the queue immediately?

(I have not heard of a pin count; does "pin" stand for something?)

A cache of things needs some subset of the following for each entry:

A timestamp or sequence number of when it was last used.  Or this may be implemented via a linked list.  (This may be the LRU queue you are referring to.)
An expiration date (rare).
A "dirty" flag, indicating that it has been changed.  More specifically, that it disagrees with disk, so it must be written out before being freed.
Reference count -- This indicates how many threads (or whatever) have latched onto this item.  (Not always present.)
A lock -- either on the item or on the entire table.  This is used to avoid modifying data, links, flags, etc, versus freeing up the item.  You must not allow one thread to free up an item while another thread is using the item.  (This sort of addresses your last question.)
Forward and backward links to other items.  (Especially useful if items are variable length.)
The item may be completely self-contained (eg when using RAM), or it may involve two blocks of info when using disk -- Keep the links, dirty bit, etc in RAM.  That way you don't have to do an expensive I/O for following links or checking/setting the dirty bit.  (So, the location of the dirty bit "depends".)

My rambling answer points out that there are many ways to implement a LRU cache.  Your questions indicate that you are beginning to understand the complexity of this basic construct.


Will I be notified that BACKUP encountered a CHECKSUM error if I use CONTINUE_AFTER_ERROR? I am in the process of adding the WITH CHECKSUM flag on our daily SQL backups, as part of an effort to better ensure data integrity.

I definitely want to know if a checksum error is ever encountered, but I also don't want my job to stop dead in the water in the middle of the night; I want it to finish backing up the "bad" database, then continue backing up the other databases on the server.

If I use BACKUP WITH CHECKSUM, CONTINUE_AFTER_ERROR, will it still throw the appropriate error (SEV 22 or Error 825 or whatever) that will trigger my associated alert? Or does CONTINUE_AFTER_ERROR suppress this altogether, and I'd only know about the problem if I parse the job step output?

I would simply test it, but I don't have a database with known CHECKSUM inconsistencies.

Yes it will continue and finish, there is an example in this older blogpost. It also contains a broken database file (for older versions) to play with:

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2007/04/17/example-corrupt-database-to-play-with-and-some-backuprestore-things-to-try/

I think it is actually quite important as a dba to simulate all those cases, I know it from experience with customers it is risky and time consuming if you have to research those methods in a downtime. So kudos for asking. Here is a lunatic/ingenious method to do corruption with SQL:

https://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/03/10/how-to-create-a-corrupt-database-using-bulk-insert-update-and-bcp-sql-server-as-a-hex-editor.aspx


Which databases provide string pooling? Suppose I have many data objects that include very many share very many identical strings (e.g. tags, URLs, "DEBUG"/"TRACE"/"INFO", etc...).

Which databases (if any) pool identical strings internally to prevent them from being copied many times?

It's usually up to you to do that via normalisation. Have a table with unique URLs in it, and link to that by its ID.


How to determine cost/benefit of adding an index? According to Craig Ringer:

  While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations down slightly, so you pay a performance cost on every INSERT, UPDATE or DELETE. If the index is rarely used it may not be worth having.

How do you determine if the benefit of adding an index surpasses its cost?

Do you profile unit tests before/after adding an index and check for an overall performance gain? Or is there a better way?

Index usage depends on the structure of you data and the selectivity of you data. If you make queries that you don't use them they are expensive. If you use them effectively they are great tool. There is a query planner and query planner analytics(EXPLAIN) of just planning or planning and executuion of commands (EXPLAIN ANALYZE).

And there is a great online book: use the index luke about recommended usage of indexes


Can I normalize my experimental data table further? I am attempting to get a MySQL database to 3NF, but I'm unsure as to how to get there.  I'm new to database design, so please correct me if my understanding is incorrect.

I have 6 columns: date(yyyy-mm-dd), compound_type, location, method, value, unit

The database represents values of samples taken on a particular day at a particular location.  There are 21 locations that happen for every date event that samples are taken, and 10 different types of compounds that are tested at each location.  There are three different methods to run on each compound.  So there is a value (and associated unit column) for each method for each compound type at each location on each day.

I got a pseudo-1NF by setting up a table like so:

id(PK), date,       compound_type,  location,   method, value,  unit
1,      2011-07-03, 1,              1,          1,      foo,    g
2,      2011-07-03, 2,              1,          1,      bar,    g
3,      2011-07-03, 1,              2,          1,      789,    g
4,      2011-07-03, 2,              2,          1,      123,    g
5,      2011-07-03, 3,              2,          1,      345,    g

And et cetera for all other compounds and locations and methods.  Obviously, there are a lot of repeats of dates for every compound, location and method combination.  To me, it seems fairly redundant to have that much repetition.

My question is: is it possible to further normalize this database, or is using an id(PK) column like I have above the best way to go with all of the repeating dates?  Am I better off using indexes or unique keys?  Would it be better to have a table for every date which breaks down into the compound/location/method categories?

I'm a newbie, so any resources you can point me to would be excellent.

Eventually, I'm going to be writing a front-end to this database for reporting and graphing, so I'm looking for a database design that will allow me to use brief SQL statements for returning data.

Second normal form

If I understand you correctly, the combination of {date, compound_type, location, method} uniquely identifies {value, units}, and all four are needed in order to identify a unique sample ({date, location, method} isn't enough by itself, for example).

I'm going to write this as if I hadn't received an answer on my question about functional dependencies, since other people might be interested in an explanation of both possibilities.

If there are no partial dependencies

1) Assuming none of the non-prime attributes {value, unit} depend on part of the candidate keys {id} or {date, compound_type, location, method}, your table is in 2NF since, as Wikipedia puts it, "every non-prime attribute of the table is either dependent on the whole of a candidate key, or on another non-prime attribute."

If there are partial dependencies

2) One or both of the non-prime attributes {value, unit} depend on only parts of the candidate key {date, compound_type, location, method}. You've confirmed this is the case with {compund}->{unit}, so your table is not in 2NF.

In order to fix the violation of 2NF, I would suggest moving {unit} to the compound table, which I'm guessing would end up looking something like this: {id, name, unit}. Here, the candidate keys are {id} and {name}. Since there are no composite candidate keys, the table is automatically 2NF. It's also 3NF since there are no transitive dependencies, I.E. there's no attribute that's dependent on unit.

Third normal form

OK, that leaves us with the samples table looking like this: {id, date, compund_type, location, method, value}. The two candidate keys are {id} and {date, compund_type, location, method}, which leaves {value} as the single non-prime attribute. Assuming that there are no more 2NF violations (you can't use a subset of {date, compund_type, location, method} to uniquely determine value), we can check the table for violations against 3NF.

3NF states that every non-prime attribute (attributes that don't belong to a candidate key)  must be directly dependent on every superkey. Since we only have one non-prime attribute, {value}, it's impossible for the table to violate 3NF, since there's no non-prime attribute for {value} to be dependent on, and no non-prime attribute that can depend on {value}.

I'm going to leave discussions about BCNF out of this for simplicity.

Surrogate key vs. natural key

As for your other questions: "is using an id(PK) column like I have above the best way to go with all of the repeating dates?"
I think so. Semantically, the surrogate key id isn't necessary, but it does help keep things simple. I'm not sure how MySQL works underneath the hood, but in other DBMSs composite primary keys with non-integer data types can lead to unnecessary overhead for example when indexing. Another problem with composite keys is that it gets annoying to query them.

Imagine that you need to add information about which labs each sample was sent to. A sample can be sent to several labs and each lab can receive several samples, so you create a table to connect the two tables. Would you rather write this

SELECT *
FROM samples s
JOIN labs_samples ls ON
    s.date = sl.date,
    s.compund_type = sl.compund_type,
    s.location = sl.location,
    s.method = sl.method

or this

SELECT *
FROM samples s
JOIN labs_samples ls ON s.id = ls.id

?


Two Intel E5-2643 (4 cores each) or single E5-2690 (8 cores) for Oracle 11.2.0.3 on Oracle Linux I have quite specific question about Oracle database  (11.2.0.3) and Intel processors. We just upgraded our main database server to big Linux box with 192GB of RAM (150 GB dedicated for Oracle). After the upgrade we found that the system is now CPU constrained (it was IO constrained before).

The machine is DELL R620 with single Intel Xeon E5-2643 processor and 192GB or RAM.

We want to go to 8 cores, so now we have two options:
1. We can buy second E5-2643
2. We can buy a single 8 core processor, e.g. E5-2690.

We don't have experience with multi cpu systems, so decision is not easy for us.

After analysis we found that second E5-2643 could be better because:

it is faster (3.3GHz vs 2.9GHZ of E5-2690)
we get two memory controllers - so we double the theoretical memory bandwidth

E5-2690 could be better because:

we have a single processor, so all memory would be local, so access to memory would be equally fast for all requests, whereas in case of two E5-2643 we would have to split memory between two processors (NUMA nodes), so for each processor half of memory would be remote, so access to this memory would be slower (please keep in mind that our SGA is 140GB, so it would be located on both NUMA nodes).

Do you have any suggestions, which configuration should behave better with Oracle (the price of processor or power consumption does not matter).

Performance will be very similar. GHz difference is not essential because e.g. E5-2690 has 3.8GHz Max Turbo Frequency vs 3.5GHz on E5-2643. You can find full comparision here:

http://ark.intel.com/compare/64596,64587

The big thing may be licensing. If you are using Standard Edition or Standard Edition One per CPU licensing they are licensed per socket. So in case you buy second E5-2643 you will also have to buy one more Oracle license. Of course that it not the case with the Enterprise Edition.

If licensing is also not an issue then you still have two decide between two choices:

buying 2nd E5-2643 will be just cheaper now. Why not to save some money if everything else is the same?
if you buy E5-2690 and after some time you will hit CPU bottleneck again you will have an option to buy 2nd E5-2690 and once again double the performance.

Also with such amount of memory you really really should use Huge Pages. Of course that means no more AMM and back to ASMM. But especially if you have many connections to Oracle the difference can be really big. You can read about configuring Huge Pages e.g. here:

http://www.oracle-base.com/articles/linux/configuring-huge-pages-for-oracle-on-linux-64.php


Are there situations where queries are formed geometrically? The topic came up in this other question. We were discussing sign value on a primary key having any significance.

If there are good times to use geometry in building queries the sign value could be somewhat useful. Are there any such times?

For instance I could query rows based on a Doppler pattern, increasingly skipping rows as I get further away from the starting point.

In response to the latitude/longitude data, relational database systems like SQL Server have spatial data types (http://msdn.microsoft.com/en-us/library/bb933876.aspx) which allows you to handle that kind of stuff. You don't really need to skip rows or anything crazy like that to satisfy a need to make stuff "look" right, if that makes any sense. What is it that you're trying to do? Say if rowA is 200 rows away from rowB, then do X?


Less than operator not working on where clause in mysqldump I am trying to backup some tables with the built in mysqldump function from MariaDb version 10.4. My tables all have a column called insertDateTime with a timestamp in the form of 2019-09-11 12:00:00

Querying one of my tables with the simple statement:

select * from account where insertDateTime < '2019-08-13 18:00:00'

will return a set of rows where that condition matches. However when I run the following code in command prompt no rows are returned in my dumped sql file.

C:
cd c:\Backup_Location

"C:\Program Files\MariaDB 10.4\bin\mysqldump.exe" -u root -p password database account > account.sql --where="insertDateTime < '2019-08-13 18:00:00'"

I have been wracking my brain on why these two statements should return different amounts of rows but am unable to figure out why. Does anyone know why this would be.

Put the options first.  That is, all parameters that start with - or -- before the non-option parameters (database  account) and redirection (> ... or < ...)

If that does not suffice, then remember that the command line processor is handling the first level of quotes, so change to

"--where=insertDateTime < '2019-08-13 18:00:00'"

(but I am not confident that this will help)

The quotes are to deal with spaces that are not separators -- as in the middle of the path or the 'where' expression.


Move/Copy unique rows from TableA to TableB, if they are not in TableB I'm required to move every unique row from TableA to TableB, if the unique row from TableA is not already in TableB.

Both tables look the same, they don't have any keys, but there's a combination of columns that make their rows unique for my task - num which is an int and com, a varbinary(512).

As my SQL is horrible I wanted to create a script that does the job, but I have ridiculous problems, escaping/handling the varbinary value. This is why I'm asking for help.

I can pseudo-code..

moving/copying::

SELECT * INTO TableB
WHERE (unique TableA.num + TableA.com)
AND NOT IN (TableB)
FROM TableA

afterwards, for moving only

DELETE FROM TableA WHERE (unique from above)

(Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2)

Additional details

Sorry. I'm still learning SQL AND asking proper questions..

TableA & TableB have the same design. They have multiple columns, including the two above mentioned ones. I'm required to both move and copy (depending on the chosen option). TableA is the source, TableB is the destination. Both tables may contain numerous occurrences of the same int/com combination. I need to have only one (it doesn't matter which one) of each of the combinations. With copying they're not removed from TableA, when moving, they should be.

If I can understand correctly what you are looking to do, you want to SELECT all records from TableA that only appear once, but don't already appear in TableB.

You can do this with a GROUP BY and HAVING to identify those that only appear once in TableA, then do an EXCEPT to filter out those that already appear in TableB.

SELECT
  int,
  com
FROM TableA
GROUP BY int, com
HAVING COUNT(*) = 1

EXCEPT

SELECT
  int,
  com
FROM TableB

If you actually wanted to SELECT ALL distinct records from TableA (regardless of how many times they occur in TableA) that do not already appear in TableB, then you could use the DISTINCT keyword.

Edit: As @ypercube correctly pointed out, EXCEPT already filters for DISTINCT rows

SELECT
  int,
  com
FROM TableA

EXCEPT

SELECT
  int,
  com
FROM TableB

If the two tables have more columns, it's handier to use NOT EXISTS instead of EXCEPT. You could also use ROW_NUMBER() to select an arbitrary row in 2005+ versions. So, the copying from table A to B would be:

INSERT INTO TableB
  (int, com, --- rest of the columns)
SELECT
  int, com, --- rest of the columns,
FROM
  ( SELECT
      int, com, --- rest of the columns,
      rn = ROW_NUMBER() OVER (PARTITION BY int, com ORDER BY someColumn)
    FROM
      TableA AS a
    WHERE
      NOT EXISTS
      ( SELECT *
        FROM TableB AS b
        WHERE b.int = a.int
          AND b.com = a.com
      )
  ) AS copy
WHERE
    rn = 1 ;


How to handle "or" possibilities in MongoDB I'm new to MongoDB and by no means an expert in DB design in general. This feels like a really basic question that's probably been answered a million times, but I'm having a surprisingly hard time finding an answer for it: is there a good way to easily handle either/or choices in the DB, or structure it in a way that makes it easy to deal with in code?

Let's say I'm building a cocktail database in MongoDB. I'm adding the entry for a Manhattan.

2 oz. Bourbon or Rye Whiskey (this is the issue)
1 oz. Sweet Vermouth
Dash Aromatic Bitters
Garnish Maraschino Cherry

So I might do a cocktails collection with an entry like:

{
    "_id" : "1234",
    "name" : "Manhattan",
    "ingredients" : [
        {
            "measure" : "2 oz.",
            "ingredient" : "Bourbon Whiskey"
        },
        {
            "measure" : "1 oz.",
            "ingredient" : "Sweet Vermouth"
        },
        {
            "measure" : "Dash",
            "ingredient" : "Aromatic Bitters"
        },
        {
            "measure" : "Garnish",
            "ingredient" : "Maraschino Cherry"
        }
    ]
}

Which is fine, but how do I get the rye in there? I don't think I'd want to do "ingredient" : "Bourbon or Rye Whiskey", would I? Is it better for the purpose of searching, later, to have them separated out? What's the best practice, here?

Also, an even more tricky drink would be something like a Martini, where I would actually use a different garnish for vodka than I would for gin, so I'd want to give the user the option to choose the base liquor they're working with and then give the proper garnish accordingly.

Any suggestions? Thanks!

In cases where there is a legitimate option to use a substitute ingredient you can do one of several things, all involving storing the option in the ingredients array.  One way could be to add a field to an array element as "allowedSubstitutions": "different liquor" another way would be to turn "ingredient" into an array which lists possible different acceptable ingredients.

However, in case of a tricky martini drink, I would strongly encourage you to store two separate drinks, one called "Martini" and the other called "Vodka Martini" otherwise known to purists as "not really a martini" :)

The real key question is how will you be querying this collection - with flexible schema the way you use the data will usually drive the way you structure its storage, not the other way around.


Disabling SET Statements / Forcing SET Options in SQL Server 2008R2/2012 I am currently working with a MSSQL 2008R2 Database which uses the following SET-Commands frequently:

SET ANSI_NULLS { ON | OFF }
SET QUOTED_IDENTIFIER { ON | OFF }
SET ANSI_PADDING { ON | OFF }

While I was researching these Settings I stumbled across the following Note for ANSI_NULLS on Microsofts technet (Link):

  In a future version of Microsoft SQL Server ANSI_PADDING will always
  be ON and any applications that explicitly set the option to OFF will
  produce an error. Avoid using this feature in new development work,
  and plan to modify applications that currently use this feature.

Because of this I would like to enforce a "global" Setting. I have been searching for the better part of a day but as I understand it you can always change these settings. To be completely sure I would like to ask here:

Is it possible to either enforce a Setting like ANSI_NULLS ON or to disable the possibility of changing these Settings on "any" level.
I would like to use this to test if the Database would "survive" if the ability to set a specific option to OFF is disabled.

I hope I was able to describe my problem. This is not my first language.
Thanks in advance.

PS: On StackOverflow I was advised to Ask this Question here too.
Original Question: Here

There is no way to enforce a global setting or prevent people running

SET ANSI_NULLS OFF
SET ANSI_PADDING OFF

You can get a quick idea if your application is using any deprecated features by running

SELECT *
FROM   sys.dm_os_performance_counters
WHERE  object_name LIKE '%Deprecated%'
       AND cntr_value > 0
ORDER  BY cntr_value DESC

If you do find areas of concern then you could set up a Server Side trace capturing the deprecation events.

This also returns the session id and SQL Handle of the offending batch to allow you to investigate further if you do see any events.

A slightly more sophisticated approach would be to set up a notification procedure to retrieve the text associated with the SQL Handle more quickly to reduce the probability that the offending SQL is no longer in the cache when you come to analyse the results.

Or you could probably use extended events to retrieve the TSQL along with the event.


Delete from table using FK columns, JOIN and LIMIT This query below will never complete, since it always looks at the first 100,000 (user_type, user_ids). How can I write this to delete 100,000 records from the table with the FK reference (Product) to the UserTable table? In other words, the limit should happen after the join, but preferably in an efficient way? UserTable has an index on (user_type, user_id) and (shard_key) and Product has an index on (user_type, user_id)

It doesn't necessarily have to be exactly 100,000 - for example, each user could own up to 100 products and that would be fine if the records deleted were (avg. # of products * 100,000)

DELETE p.*
FROM (
    SELECT user_type, user_id
    FROM UserTable
    WHERE MOD(shard_key,20) != 7
    LIMIT 100000
) t
JOIN Product p ON (
    t.user_type = p.user_type AND t.user_id = p.user_id
)

EDIT: This is MySQL 5.6

This workaround is OK for my case but isn't great, since it needs to scan the entire index of the User table, so wouldn't be very portable. The user:product ratio can be up to 1:1000, and it's feasible to scan the User table first using a covering index before doing the JOIN.

DELETE
FROM Product f
JOIN (
    SELECT t.* FROM (
        SELECT DISTINCT user_type, user_id
        FROM UserTable
        WHERE MOD(shard_key,20) != 7
        ) p
        JOIN Product t ON (
            t.user_type = p.user_type
            AND
            t.user_id = p.user_id
        ) LIMIT 10
    ) x
ON
x.user_type = f.user_type
AND
x.user = f.user_id;

My takeaway is: If you can reasonably scan the entire primary key table quickly using indexes, and join with the foreign key table afterwards (ideally also using indexes), that is a solution that ensures you will be able to find ALL foreign key references if you want to batch DELETE in situations where many foreign keys reference each primary key.In this case, the User table was ~1GB and the Product table ~350GB


Why can't I select current SCN? I can not select the current SCN, with the command that I previously could.

I tried with this command:

select current_scn from v$database;
ORA-00942: table or view does not exist

select dbms_flashback.get_system_change_number from dual;
ORA-00904: "DBMS_FLASHBACK"."GET_SYSTEM_CHANGE_NUMBER": invalid identifier

I get SCN only with this command:

select timestamp_to_scn(sysdate) from dual;

TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
           835484

Users by default do not have the privilege to use those objects:

[oracle@o7ca1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 4 20:42:48 2019
Version 19.2.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0

SQL> show user
USER is "SYS"
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     564558

SQL> create user u1 identified by u1;

User created.

SQL> grant create session to u1;

Grant succeeded.

SQL> conn u1/u1
Connected.
SQL> select current_scn from v$database;
select current_scn from v$database
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select dbms_flashback.get_system_change_number from dual;
select dbms_flashback.get_system_change_number from dual
       *
ERROR at line 1:
ORA-00904: "DBMS_FLASHBACK"."GET_SYSTEM_CHANGE_NUMBER": invalid identifier

DBMS_FLASHBACK

  DBMS_FLASHBACK Security Model

  To use the DBMS_FLASHBACK package, you must have the EXECUTE privilege
  on it.

About Dynamic Performance Views

  After installation, only user SYS or anyone with SYSDBA privilege has
  access to the dynamic performance tables.

We do not know what privileges you had before, that you do not have now.

Ask your DBA to grant you the required privileges.

grant read on sys.v_$database to petar;

Or:

grant execute on dbms_flashback to petar;


MariaDB database access using roles I run MariaDB 10.0.23, and I use roles to grant privileges to users. I have a user john:

+-------------------------------------------------------------------+
| Grants for john@%                                                 |
+-------------------------------------------------------------------+
| GRANT client TO 'john'@'%'                                        |
| GRANT USAGE ON *.* TO 'john'@'%' IDENTIFIED BY PASSWORD '*C...DF' |
+-------------------------------------------------------------------+

The client role is defined like this:

+--------------------------------------------------------+
| Grants for client                                      |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'client'                         |
| GRANT INSERT ON `database_name`.`table1` TO 'client'   |
| GRANT SELECT ON `database_name`.`table2` TO 'client'   |
| GRANT SELECT ON `database_name`.`table3` TO 'client'   |
| GRANT INSERT ON `database_name`.`table4` TO 'client'   |
+--------------------------------------------------------+

When I connect using mysql -u john -p database_name, I get

ERROR 1044 (42000): Access denied for user 'john'@'%' to database 'database_name'

which kind of makes sense because when users log in, they don't have a role assigned by default.
However, I get the same error when I do mysql -u john -p and then

SET ROLE client;

SHOW GRANTS;
+-------------------------------------------------------------------+
| Grants for john@%                                                 |
+-------------------------------------------------------------------+
| GRANT client TO 'john'@'%'                                        |
| GRANT USAGE ON *.* TO 'john'@'%' IDENTIFIED BY PASSWORD '*C...DF' |
| GRANT USAGE ON *.* TO 'client'                                    |
| GRANT INSERT ON `database_name`.`table1` TO 'client'              |
| GRANT SELECT ON `database_name`.`table2` TO 'client'              |
| GRANT SELECT ON `database_name`.`table3` TO 'client'              |
| GRANT INSERT ON `database_name`.`table4` TO 'client'              |
+-------------------------------------------------------------------+

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

USE database_name;
ERROR 1044 (42000): Access denied for user 'john'@'%' to database 'database_name'

The workaround that I use is to add one of the role's permissions to each user directly. This way users get access to the database, but it doesn't seem quite logical to me that using roles users still have to have permissions granted to them explicitly. MariaDB documentation doesn't say anything about this issue.

Any thoughts on why it happens and how to fix this behavior?

I can't reproduce the problem.

$ mysql -u root -p
Enter password:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 54
Server version: 10.0.23-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> DROP DATABASE IF EXISTS `database_name`;
Query OK, 4 rows affected (0.01 sec)

MariaDB [(none)]> DROP ROLE `client`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> DROP USER 'john'@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CREATE USER 'john'@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CREATE ROLE `client`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT `client` TO 'john'@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CREATE DATABASE `database_name`;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> CREATE TABLE `database_name`.`table1`(`col0` INT);
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CREATE TABLE `database_name`.`table2`(`col0` INT);
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CREATE TABLE `database_name`.`table3`(`col0` INT);
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CREATE TABLE `database_name`.`table4`(`col0` INT);
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT INSERT ON `database_name`.`table1` TO 'client';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT SELECT ON `database_name`.`table2` TO 'client';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT SELECT ON `database_name`.`table3` TO 'client';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT INSERT ON `database_name`.`table4` TO 'client';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'john'@'%';
+----------------------------------+
| Grants for john@%                |
+----------------------------------+
| GRANT client TO 'john'@'%'       |
| GRANT USAGE ON *.* TO 'john'@'%' |
+----------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR `client`;
+------------------------------------------------------+
| Grants for client                                    |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'client'                       |
| GRANT SELECT ON `database_name`.`table3` TO 'client' |
| GRANT SELECT ON `database_name`.`table2` TO 'client' |
| GRANT INSERT ON `database_name`.`table1` TO 'client' |
| GRANT INSERT ON `database_name`.`table4` TO 'client' |
+------------------------------------------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> exit
Bye

$ mysql -u john -p
Enter password:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 56
Server version: 10.0.23-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SET ROLE `client`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| database_name      |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> SELECT `col0` FROM `database_name`.`table1`;
ERROR 1142 (42000): SELECT command denied to user 'john'@'localhost' for table 'table1'

MariaDB [(none)]> SELECT `col0` FROM `database_name`.`table2`;
Empty set (0.00 sec)

UPDATE

With a username longer than 6 characters, I can reproduce the problem, however, you can access the tables.

$ mysql -u root -p
Enter password:

MariaDB [(none)]> CREATE USER 'usertestjohn'@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT `client` TO 'usertestjohn'@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit
Bye

$ mysql -u usertestjohn -p
Enter password:

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SET ROLE `client`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT `col0` FROM `database_name`.`table2`;
Empty set (0.00 sec)

Bug reported to MariaDB: Roles and Users longer than 6 characters.


Restore data from SQL Server backup Monthly I need to sync a SQL Server database. Basically the source database generates a .bak file, then I take that file and restore it to the destination database.

That worked for the first few times, now the destination database changed because there were some users added, and some tables created.

I thought to open the .bak file, query the destination DB, removing the duplicates and then do and INSERT with only the new data.

Is that something reasonable?

How can I read/open a .bak file?

Also, I have been following this instructions

Basically it's my problem, but it use robocopy, as well jobs and I can't use them now.

It use this code to restore the backup:

PRINT '*** Restore full backup of DB $(DB) ***'
:CONNECT $(TGT)
GO
USE [master]
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = '$(DB)')
BEGIN
    ALTER DATABASE $(DB)
        SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    DROP DATABASE $(DB)
END

RESTORE DATABASE $(DB)
FROM disk = '$(RESTORE_PATH)\$(DB).bak'
WITH RECOVERY, NOUNLOAD, STATS = 10, REPLACE,
     MOVE '$(DATAFILENAME)' TO '$(RESTORE_DATA_PATH)\$(DATAFILENAME).mdf',
     MOVE '$(LOGFILENAME)' TO '$(RESTORE_DATA_PATH)\$(LOGFILENAME).ldf'
GO

From what I remember from SQL Server, it will restore the entire database. Is it possible to restore only one table?

Thanks

Is it possible to restore only one table?

No.

the destination database changed because there were some users added, and some tables

If the additions can't be added with a simple script you run after the restore, they should be in a separate database.  You can use Views or Synonyms to reference tables across databases.


NOT IN in function to parse CSV I'm trying to pass an array of items to be used in a NOT IN operation:

ALTER FUNCTION [dbo].[fnTEST] (@CODES VARCHAR)
RETURNS TABLE
AS
RETURN
(
    SELECT
        T1.CODE AS 'CODE',
    (SELECT COUNT(*)
        FROM TABLE1 AS T2 WITH (NOLOCK)
        WHERE T2.CODE NOT IN (@CODES)
    ) AS 'COUNT'
    FROM TABLE1 T1 WITH (NOLOCK)
)

Then I call it like this:

SELECT * FROM fnTEST ('500,250,202,900,204,200,300,400,600,800')

That doesn't work. If I try to call it without the quotes, I get an error of too many parameters (which is expected).

Is there a solution for this?

In SQL Server 2008, the best way to do this is to not pass in a CSV list in the first place. Where does this comma-separated set of values come from?

First, create a table type:

CREATE TYPE dbo.myFoo AS TABLE(id INT PRIMARY KEY);
GO

Now, create a function that takes it as a TVP:

CREATE FUNCTION dbo.fnTest
(
  @codes dbo.myFoo READONLY
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        T1.CODE AS [CODE],
    (SELECT COUNT(*)
        FROM dbo.TABLE1 AS T2 WITH (NOLOCK)
        WHERE NOT EXISTS (SELECT 1 FROM @codes
          WHERE id = T2.CODE)
    ) AS [COUNT]
    FROM dbo.TABLE1 T1 WITH (NOLOCK)
);

To test it locally...

DECLARE @x dbo.myFoo;
INSERT @x(id) VALUES(500),(250),(202);...
SELECT * FROM dbo.fnTest(@x);

...but ultimately you would be passing that set of values from your C# code or wherever as a DataTable or other set-based struct.

For SQL Server 2005

You can't use TVPs. So the next best option is a split function, e.g.

CREATE FUNCTION dbo.SplitInts
(
    @List       VARCHAR(MAX),
    @Delimiter  VARCHAR(32)
)
RETURNS TABLE
AS
    RETURN
    (
      SELECT Item = CONVERT(INT, Item)
        FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number,
          CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
        FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
          FROM sys.all_objects) AS n(Number)
        WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
      ) AS y
    );

So then your function can be:

CREATE FUNCTION dbo.fnTest
(
  @codes VARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        T1.CODE AS [CODE],
    (SELECT COUNT(*)
        FROM dbo.TABLE1 AS T2 WITH (NOLOCK)
        WHERE NOT EXISTS (SELECT 1 FROM dbo.SplintInts(@codes, ',')
          WHERE Item = T2.CODE)
    ) AS [COUNT]
    FROM dbo.TABLE1 T1 WITH (NOLOCK)
);

If you can do this in CLR, it's probably going to perform a bit better. See the following blog series - they deal with splitting a string of strings but for the most part the observations hold true for splitting a string of ints as well.

http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings
http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up
http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql


Is UPDLOCK required? Given table A and table B, such that rows in B reference a row in A...

CREATE TABLE A (Id INT, column1 INT)
CREATE TABLE B (Id INT, AId INT)

Rows in B must be consistent with one another. So, when inserting rows in B, I update A within a transactions, delete existing rows and then insert new ones...

BEGIN TRANSACTION
UPDATE A SET column1 = 1 WHERE Id = 1
DELETE FROM B WHERE AId = 1
INSERT INTO B (AId) VALUES (1)
COMMIT

Question is how to retrieve rows from B without returning rows half way through the above transaction.

SELECT a.Id, b.Id
FROM B AS b
INNER JOIN A AS a
ON a.Id = b.AId

Could the above transaction read elements from two different sets of Bs or do I need to add WITH (UPDLOCK) on A...?

SELECT a.Id, b.Id
FROM B b
INNER JOIN A a WITH (UPLOCK)
ON a.Id = b.AId

This works. The update transaction can't get going until the read query has been completed. But is it necessary?

try REPEATABLE READ

on the select statements:

  Specifies that statements cannot read data that has been modified but
  not yet committed by other transactions and that no other transactions
  can modify data that has been read by the current transaction until
  the current transaction completes.


What are pros and cons of Ola’s scripts vs using maintenance plan? Will you please help me understand pros and cons of using Ola’s solution over maintenance plan? I have prepared a presentation based on SQL Pass (http://www.pass.org/DownloadFile.aspx?File=ebae1b31) which I will present.

I am also preparing few scenarios which Ola’s solution addresses and maintenance plan solution doesn’t. Can you all please help me explain this more technically?

By the way, we are managing almost 150+ servers (mix of 2008/2012/2014/2016) with Ola’s solution on at least 75% of them. I liked this article by Brent Ozar. But in one the comments, Brent has recommended to use script based solution for the number of servers we have. https://www.brentozar.com/archive/2012/04/maintenance-plans-roombas-suck-good-way/

I have written here

  Maintenance plans are not bad, but when your environment grows, the limited flexibility and functionality that maintenance plans provide wont be sufficient.

To add more,

Ola's maintenance solution is widely accepted in the community and large organizations.
Its open sourced and issues can be raised at github/issues with a likelyhood of getting it fixed very fast.
Its flexible and scalable (even if you want to deploy it to 100s of servers just use Install-DbaMaintenanceSolution from dbatools.)
Microsoft took almost a decade to fix Maintenance plan GUI which was buggy itself :-)
has extensive documentation and FAQs and is constantly updated to accomodate newer sql server versions.
in preview version, you can even run backups in parallel.
for index maintenance solution, you can even time the process e.g. if it runs more than X amount of time, abort it.


"When specifying a local instance, always use (local)" -- why? On the subject of SQL Server connection strings, Microsoft says:

  When specifying a local instance, always use (local).

The implication is that using localhost is a bad idea.  But why?  Or am I reading too much into the wording?

This is from memory and without reference so my recall may not be accurate.

IIRC for 2005+ "(local)", "localhost" and "." are equivalent and will use the Shared Memory protocol. Prior to 2005, "(local)" and "." would use Shared Memory whereas localhost would not necessarily do so by default. Use of machine name or FQDN could result in a DNS lookup so is not recommended for a local machine connection.

Lastly, the "(local)" term (including brackets) is a special keyword, rather than a reference to the local machine.


mysql table replication on just some records? I have a big database which contain a table with about 26 million records. Now i need to make some reports on it from a special period of time.

So how to make a replication from that table from a special time, for example, just show replicate the records from 2015-01-01 till now?

I suggest using mysqldump instead of replication.

mysqldump <other-params> --where="date >= '2015-01-01'" db table > dump.sql

That way you have dump file which can be reused. Import will take some time, but setup is much simpler - you can import it anywhere and do what you need.


zero-or-one to zero-or-one How do I model zero-or-one to zero-or-one relationship in Sql Server the most natural way?

There is a 'Hazard' table that lists hazards on a site. There is a 'Task' table for work that needs to be done on a site. Some Tasks are to fix a hazard, no task can deal with multiple hazards. Some Hazards have a task to fix them. No hazard can have two tasks associated with them.

The below is the best I could think of:

CREATE TABLE [dbo].[Hazard](
  [HazardId] [int] IDENTITY(1,1) NOT NULL,
  [TaskId] [int] NULL,
  [Details] [varchar](max) NULL,
 CONSTRAINT [PK_Hazard] PRIMARY KEY CLUSTERED
(
  [HazardId] ASC
))

GO

ALTER TABLE [dbo].[Hazard]  WITH CHECK ADD  CONSTRAINT [FK_Hazard_Task] FOREIGN KEY([TaskId])
REFERENCES [dbo].[Task] ([TaskId])
GO

CREATE TABLE [dbo].[Task](
  [TaskId] [int] IDENTITY(1,1) NOT NULL,
  [HazardId] [int] NULL,
  [Details] [varchar](max) NULL,
 CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED
(
  [TaskId] ASC
))

GO

ALTER TABLE [dbo].[Task]  WITH CHECK ADD  CONSTRAINT [FK_Task_Hazard] FOREIGN KEY([HazardId])
REFERENCES [dbo].[Hazard] ([HazardId])
GO

Would you do that differently? The reason I'm not happy with this set up is that there need to be application logic to make sure that tasks and hazards point to each other and not to other task and hazards and that no task/hazard points to the same hazard/task another task/hazard points to.

Is there a better way?

You could go with your own idea of an asymmetric schema by removing one of the foreign keys from the current set-up, or, to keep things symmetric, you could remove both foreign keys and introduce a junction table with a unique constraint on each reference.

So, it would be like this:

CREATE TABLE dbo.Hazard
(
  HazardId int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Hazard PRIMARY KEY CLUSTERED,
  Details varchar(max) NULL
);

CREATE TABLE dbo.Task
(
  TaskId int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Task PRIMARY KEY CLUSTERED,
  Details varchar(max) NULL,
);

CREATE TABLE dbo.HazardTask
(
  HazardId int NOT NULL
    CONSTRAINT FK_HazardTask_Hazard FOREIGN KEY REFERENCES dbo.Hazard (HazardId)
    CONSTRAINT UQ_HazardTask_Hazard UNIQUE,
  TaskId int NOT NULL
    CONSTRAINT FK_HazardTask_Task FOREIGN KEY REFERENCES dbo.Task (TaskId)
    CONSTRAINT UQ_HazardTask_Task UNIQUE
);

You could additionally declare (HazardId, TaskId) to be the primary key if you need to reference these combinations from another table. For the purpose of keeping the pairs unique, however, the primary key is unnecessary, it is enough that each ID is unique.


Shrink multiple database files using sp_MSForEachDB We have recently cleared out old data from our production database. The database is 3 TB with 1.4 TB empty this however poses a problem on the development and QA instance as we are not utilizing space by having 6 - 8 databases with 1.4 TB empty space especially with the space constraints we have on development. I want to setup a job to shrink the development databases using the below code

EXEC sp_MSForEachDB ' USE [?]; DBCC SHRINKFILE  (''?'' , 10)'

EXEC sp_MSForEachDB ' USE [?]; DBCC SHRINKFILE (''?'' , 0, TRUNCATEONLY)'

I am getting the following error, "Could not locate file 'Test' for database 'Test' in sys.database_files. The file either does not exist, or was dropped."
The database has multiple data files. How could i improve my code to accommodate
the multiple data files.

The question mark will evaluate to the database name, not the file name you're trying to shrink.

For instance:

EXEC master.sys.sp_MSforeachdb ' USE [?]; PRINT N''?''; ';

Will return (on my server)

master
tempdb
model
msdb
SUPERUSER
StackOverflow
StackOverflow_CS
Crap
DBAtools
StackOverflow2010
SUPERUSER_CX
ಠ_ಠ
StackOverflow2010ಠ_ಠ

DBCC SHRINKFILE doesn't take that as an argument:

  DBCC SHRINKFILE    (
      { file_name | file_id }
      { [ , EMPTYFILE ]
      | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
      }   )   [ WITH NO_INFOMSGS ]

If you only have a .mdf and a .ldf, you can (likely, though not definitely) replace your code with:

EXEC sp_MSForEachDB ' USE [?]; DBCC SHRINKFILE  (1 , 10)'

EXEC sp_MSForEachDB ' USE [?]; DBCC SHRINKFILE (2 , 0, TRUNCATEONLY)'

More verbose code to look up file ids, etc. is left as an exercise to the reader.

If you want to just shrink the whole thing, use DBCC SHRINKDATABASE instead. That takes a database name, and will work with your original code.

  DBCC SHRINKDATABASE    ( database_name | database_id | 0
       [ , target_percent ]
       [ , { NOTRUNCATE | TRUNCATEONLY } ]    )   [ WITH NO_INFOMSGS ]

Of course, this can cause all sorts of problems, and I wouldn't wanna do it.


Database connection lost whenever SQL query string is too long I recently switched from running my Rails app on a single VM to running the database — MariaDB 10.3 — on a separate (Debian Buster) VM. Now that the database is on a separate server, Rails immediately throws Mysql2::Error::ConnectionError: MySQL server has gone away whenever it tries to make a query where the SQL itself is very long. (They query itself isn't necessarily one that would put significant load on the system.)
An example query that causes the problem looks like this:
SELECT `articles`.`id` FROM `articles` WHERE `articles`.`namespace` = 0 AND `articles`.`wiki_id` = 1 AND `articles`.`title` IN ('Abortion', 'American_Civil_Liberties_Union', 'Auschwitz_concentration_camp', 'Agent_Orange', 'Ahimsa')

... except the array of titles is about 5000 items long, and the full query string is ~158kB.
On the database side, this corresponds to warnings like this:
2021-03-25 15:47:13 10 [Warning] Aborted connection 10 to db: 'dashboard' user: 'outreachdashboard' host: 'programs-and-events-dashboard.globaleducation.eqiad1.wikimed' (Got an error reading communication packets)
The problem seems to be with the network layer, but I can't get to the bottom of it. I've tried adjusting many MariaDB config variables (max_allowed_packet, innodb_log_buffer_size, innodb_log_file_size, innodb_buffer_pool_size) but none of those made a difference. The problem seems to be that the connection is aborted while it is attempting to transmit the long SQL query string from the app server to the database server. (There's no corresponding problem with receiving large query results from the database.)
I've tried adjusting several timeout-related settings as well, although that seems unlikely to be the problem because I can replicate the connection error without any significant wait, just by issuing one of the long-SQL-string queries from a Rails console.
I've tried using tcpdump to see what's coming in, but didn't pick up any additional clues from that.

Woo! I finally figured it out!
The problem was solved by upgrading libmariadb-dev on the Rails application server and rebuilding the mysql2 gem.
Previously, the system was running on a single server with a mariadb-10.1 database. After the switch to a separate database server, the database was on mariadb-10.3 but it was still connecting from the same Rails environment as before, with a mysql2 gem built against 10.1.


PostgreSQL: Do regclass/oid columns persist across backups? I have a large table (audit) in which I store relation ID of the schema and table.
This is conveniently populated by trigger using TG_RELID.
It is also convenient because I can index it and the index would be smaller than if I were to store "schema"(text) and "table"(text) columns separately and index them.
Also, I can then do queries using WHERE relation_id = 'myschema:mytable'::regclass and it just works.

My question is if this is "safe" to do in terms of backup/restore (will the schema.table have the same regclass/oid in target server and it did in the source server?

Any other issue I should be aware of?

I wouldn't rely on the specific values of OIDs to be portable at all. Even if you try it and you find it to work with the current version of PostgreSQL, it might not do so in newer releases.

If you backup your database in a source machine and try to restore it on a different machine (or, in general, another PostgreSQL Database Cluster) that does already have other databases, schemas, etc. it is very likely that the different OIDs your source database was using are already used to represent other different things in the target database.

The schema_name.table_name correspondence to an OID is handled by PostgreSQL within its system tables. If you need equivalent functionality, with the guarantee it behaves the way you need it to and not the way PostgreSQL uses its internal OIDs, I'd recommend you actually duplicate this behaviour with a your_tables table, where you store an arbitrary id (a serial) and schema_name, table_name. This way, your database is safe to backup and restore, and you're likely to avoid modifications for any newer postgreSQL version.

You may need an extra lookup in your trigger(s), but might save you a lot of trouble in the future.


Poor Query performance in postgresql 9.3 I have a simple query which is taking about 2 minutes to complete.

    select distinct
    a.statename,
    a.districtname,
    count(b.rel_globalid) as samplescollected
    from fulladmin a
    join gridpoint_attach_view b on a.globalid=b.rel_globalid
    where a.statename='Karnataka' group by statename, districtname order by districtname

Here fulladmin is a MATERIALIZED VIEW, which is having about 6.5 million rows.

    CREATE MATERIALIZED VIEW fulladmin AS
    SELECT DISTINCT a.districtname,
    a.talukname,
    a.hobliname,
    a.villagename,
    a.statename,
    b.globalid
    FROM village a
    JOIN gridpoint_1 b ON st_intersects(a.shape, b.shape::st_geometry)
    WHERE a.statename::text = 'Karnataka'::text
    GROUP BY a.statename,
    a.districtname,
    a.talukname,
    a.hobliname,
    a.villagename,
    b.globalid
    WITH DATA;

   ALTER TABLE fulladmin
   OWNER TO sde;

and gridpoint_attach_view is normal view

     CREATE OR REPLACE VIEW sde.gridpoint_attach_view AS
     SELECT gridpoint_1__attach.rel_globalid
     FROM gridpoint_1__attach;

     ALTER TABLE sde.gridpoint_attach_view
     OWNER TO sde;

I have created index on materialized view as

CREATE INDEX sample1 ON fulladmin (statename,districtname,globalid)

Still the query is taking too long. Any suggestion?

EDIT: When I run

explain (analyse,verbose) select * from fulladmin

I am getting output as

seq scan on fulladmin (cost=0.00..141541.52 rows=5765552 width=84) (actual time=0.042..1160.429 rows=5765552 loops=1)
  Output: districtname,talukname,hobliname,villagename,statename,globalid Total
runtime:1356.028 ms

This is an analytics query, analytics queries are never simple in terms of how much data they need to examine.

The where a.statename='Karnataka' is redundant, because it was already in CREATE MATERIALIZED VIEW and repeating it in your query has no effect, since it will match all rows. PostgreSQL sees this from cardinality and knows it needs to scan the full table.

Also, no index will help you here since you are touching a large portion of table. Indexes only work when touching small amounts of data.

If you run this query often, you need to cache it. If a value needs to be exact even as the original records are updated, you need a trigger.


CHECKPOINT or COMMIT writes to disk? Let's say, for SQLServer2008R2 and higher, with full recovery mode databases.

I always thought :

When a transaction is commited (COMMIT), the transaction is written to the transaction log in RAM.
When a CHECKPOINT occurs (after some time and/or some transactions and other criterias), the transactions between the last CHECKPOINT and the current are written to disk.
When a BACKUP LOG happens, the datas are written to the MDF file.

Am I correct? Some of my collegues says I'm wrong, and it's hard to find the correct answer, even with the BOL.

Thanks!

Unfortunately there are a number of errors in the answers so far with regard to how COMMIT works, so I'll add another one. See How It Works: Bob Dorr's SQL Server I/O Presentation for details and SQL Server 2000 I/O Basics. Here is how it works:

All fully logged data writes (changes) occur in the exactly following sequence (see Understanding How SQL Server executes a Query: Writing Data):

The data page is latched exclusively
A log record describing the change is added to to log, in memory. New log record generates a new LSN, see What is an LSN: Log Sequence Number.
The data page is modified (both data record and last_update_lsn on the page). This is now modified ('dirty') page.
The data page latch is released
nothing gets written to disk directly as the result of the update

A COMMIT does the following

adds a new log record describing the COMMIT to the log, in memory
all log records not flushed to disk, up to and including the one generated above, are flushed (written to disk)
thread blocks waits until the OS reports the above write as durable (IO completes)
COMMIT statement (or DML statement with implicit commit) completes

A CHECKPOINT does the following (simplified), see How do checkpoints work and what gets logged:

All dirty pages in memory are written to disk

For each dirty page, before starting to write to disk, the log up to and including the LSN that is the last_update_lsn on that page is flushed (written to disk). Note that flushin any LSN implies all previous LSNs are also flushed, so for the most dirty pages this is a no-op since it's own last_update is likely already flushed.

log record describing the checkpoint is written to the log and flushed
the database boot page is update with the LSN of the record generated above

Writes work differently for minimally logged operations, see Operations That Can Be Minimally Logged. Roughly the minimally logged operations act as following (simplified):

Before inserting rows in a page as part of a minimally logged operation, a log record describing the fact that the page participates in minimally logged operations is being generated and appended to the log (in memory)
The minimally logged page is being updated, as many inserts are being written on it is it fit. Nothing is logged, nothign is written to disk.
When a minimally logged operation commits, before it commit it is required that all pages that participated in minimally logged operations in that transaction are being written to disk. Onyl after this writes completes, the COMMIT log record can be appended to the log (in memory) and and the log, up to and including this newly added commit log record, is flushed (written) to disk.


Multi-site SQL Server Cluster issue We have a 12 node SQL Server FCI (6 nodes in our primary datacenter and the other 6 nodes at a DR site) which has been working well until an access control list (ACL) was applied to a network switch.

When the ACL was applied, the primary cluster was not able to access/communicate with the 6 DR nodes. As a result, disks on the primary DB nodes started to dismount and the cluster stopped functioning.

Any ideas as to why this is happening?

The issue that occurred is quorum was lost - This is a good primer on how the cluster voting works and should give you some insight into how to design a cluster to meet certain disaster scenarios.

Since you have an even number of nodes on each "side" and connectivity was lost between the "sides", the cluster didn't know who should actually own and run the resources - this is something called split brain.

Generally, we have some sort of tie breaker involved. This could be as simple as a fileshare or disk. It could be as convoluted as cluster property settings or even sites with preferences. Generally, though, for most scenarios it can be pretty basic and is discussed in the post I linked above.

If you're looking to not have this happen in the future then I'd suggest, depending on version of Windows Server, think about the scenarios you're wanting to defend against and setting the cluster to have the resources or cluster level settings needed to fend those potential issues off. A cluster does not insulate you from every possible issue but does lend a great deal of high availability to most scenarios.


Can I provide a default for a left outer join? Suppose I have tables a (with column a1) and b (with columns b1 and b2) and I perform a left outer join

SELECT *
FROM a LEFT OUTER JOIN b
ON a.a1 = b.b1

Then b1 and b2 will be NULL where a value of a1 has no matching value of b1.

Can I provide a default value for b2, instead of NULL?  Note that COALESCE won't work here, because I don't want the default value to override potential NULLs in b2 where there is a value of b1 matching a1.

That is, with a and b as

CREATE TABLE a (a1)
  AS VALUES (1),
            (2),
            (3) ;

CREATE TABLE b (b1,b2)
  AS VALUES (1, 10),
            (3, null) ;

a1     b1 | b2
---    --------
 1      1 | 10
 2      3 | NULL
 3

and a default for b2 of, say, 100, I want to get the result

a1 | b1   | b2
---------------
1  |  1   | 10
2  | NULL | 100
3  |  3   | NULL

In this simple case I could do it "by hand" by looking at whether b1 is NULL in the output.  Is that the best option in general, or is there a more standard and neater way?

SELECT a.a1,b.b1,
    CASE WHEN b.b1 is NULL THEN 5 ELSE b.b2 END AS b2
FROM a LEFT OUTER JOIN b
ON a.a1 = b.b1


Setting up replication for MyISAM tables? Before setting up a replication within MySQL, the data first needs to be transferred to the slave.

Since MyISAM tables can be copied as files. Would it be okay to copy MyISAM files, .myd .myi .frm, to the slave? Would this be enough to start the replication?

Since everything is MyISAM, here is what you need to do

STEP 01) Configure Master to be a Replication Master

If you do not have server-id defined in /etc/my.cnf, add one

[mysqld]
server-id=1

and service mysql restart

STEP 02) Check Binary Logging on the Master

If binary logging is active on the Master, do this

RESET MASTER;

to zap all binary logs and start fresh.

If binary logging is not active, do this

Add log-bin=mysql-bin under [mysqld] in /etc/my.cnf
service mysql restart

STEP 03) Copy /var/lib/mysql

You could set rsync to perform a brute force byte-by-byte copy of your MyISAM tables from /var/lib/mysql on one server to /var/lib/mysql on another server. You would have perform multiple rsyncs until the last rsync, which is very quick. Then, you would do a full shutdown of MySQL and perform one more rsync. Start mysql on both Master and Slave.

STEP 04) Create Replication User on the Slave

GRANT REPLICATION CLIENT,REPLICATION SLAVE
ON *.* TO replicator@'PrivateIPofSlave' IDENTIFIED BY 'r3pl1cAt0r';

STEP 05) Set up replication

Run this command on the Slave

CHANGE MASTER TO
MASTER_HOST='PrivateIPofMaster',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='r3pl1cAt0r',
MASTER_LOG_FILE='XXXX',
MASTER_LOG_POS=YYYY;
START SLAVE;

What is XXXX and YYYY ???

XXXX is the last binary log file at the time you perform final rsync. There is a copy of that file in /var/lib/mysql on the Slave. Just run ls -l /var/lib/mysql/mysql-bin.0* on the Slave and use the last binary log.
YYYY is the position of the last binary log file at the time you perform final rsync. Interestingly, the position is also the filesize of the binary log. Just run ls -l /var/lib/mysql/mysql-bin.0* on the Slave and take the last binary log's filesize to be the position YYYY.

STEP 06) Check replication

Run this

SHOW SLAVE STATUS\G

If Slave_IO_Running is Yes and Slave_SQL_Running is Yes, Congratulations You are Done !!!

I have written similar posts on using rsync like this:

MySQL slave replication reset with no Master Downtime (using MyISAM) (Jul 08, 2011 on ServerFault) with Sample Code !!!
Create a MySQL slave from another slave, but point it at the master (Apr 08, 2011 on ServerFault)
MySQL Replication - Introduce new Slave to replication (Feb 23, 2011)


Dynamic ORDER BY clause in PL/SQL cursor I am not sure if this is simply impossible or if I need to approach this from a different angle.

Simplified, here is what I am trying:

create or replace FUNCTION someFunction(SortCol1 varchar2 default 'column1',
                                        SortDir1 varchar2 default 'ASC')
RETURN STUFF pipelined
AS
CURSOR c1 is
    SELECT
        *
    FROM
        table1
    ORDER BY
        SortCol1 SortDir1;

BEGIN
    FOR c1_rec IN c1
    LOOP
        -- Some logic, etc...
    END LOOP;
END someFunction;

Upon compiling this simply tells me that my SQL is improperly terminated. It does not like the SortCol1 SortDir1

You can't dynamically change the column you're sorting by using static SQL.  You'd need to use dynamic SQL.  Something like this should work

create or replace FUNCTION someFunction(SortCol1 varchar2 default 'column1',
                                        SortDir1 varchar2 default 'ASC')
RETURN STUFF pipelined
AS
    c1 SYS_REFCURSOR;
    c1_rec table1%rowtype;
BEGIN
    OPEN c1
     FOR 'select * from table1 order by ' || sortCol1 || ' ' || sortDir1;

    LOOP
        FETCH c1 INTO c1_rec;

        <<do something>>
    END LOOP;

END someFunction;