With MS SQL Server are the generated constraint names predictable?

No, the constraint name is completely unpredictable. If you want your names to be consistent, you can name them correctly by applying a predictable / repeatable name manually. I have no idea how you would do this in the code you have, but in T-SQL instead of:

CREATE TABLE dbo.foo(bar INT PRIMARY KEY);

CREATE TABLE dbo.blat(bar INT FOREIGN KEY REFERENCES dbo.foo(bar));

(The above end up with constraints having names like PK__foo__DE90ECFF6CF25EF6 and FK__blat__bar__1B1EE1BE.)

You would say:

CREATE TABLE dbo.foo(bar INT, CONSTRAINT PK_foo PRIMARY KEY (bar));

CREATE TABLE dbo.blat(bar INT, CONSTRAINT fk_foobar FOREIGN KEY(bar)
  REFERENCES dbo.foo(bar));


ASM isn't just a file system.  It's also a volume manager.

An ASM instance is a cut-down Oracle instance that database instances talk to in order to find out how to access the blocks they need.  The ASM instance doesn't write data to or read data from the disks itself, it only provides the information that the database requires in order to do this.

This communication is over the network, so involves IP.  Hence the note about IPv4/IPv6.


How to check if I am hitting the Express Edition size limit?

Here is a good script I shamelessly ripped from here:

use [Insert DB Name]

select
a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name, 'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name, 'SpaceUsed'))/128.000,2)) ,
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a

This is good because it will give you the free space in each DB file (you might have multiple files and perhaps someone set it up to put some objects in each) as well as the free size.

For example, you have have a 4GB data file which has 3GB of free space.  Maybe you have 1 MDF without a lot of data but a NDF with lots of data.  This query will tell you the free size in each file and what DB that file is allocated to.  Remember to add up all the 'SPACE_USED_MB' for each DB to get the total size.

Good luck!

Edit:
Removed a unsupported and buggy command that I thought I could get away with posting in here for this query. :(


You included the following in your question:

Executed as user: ...tabase backup. [SQLSTATE 42000] (Error 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).

The error you're seeing (error 4214) indicates that there has been no current database backup performed.
This is the actual text of that error message:

BACKUP LOG cannot be performed because there is no current database backup.

I was able to look that up using this query:
SELECT m.message_id
    , m.severity
    , m.text
FROM sys.messages m
WHERE m.message_id = 4214
    AND m.language_id = 1033; --Show the English version of the message

This means you need to ensure you take a full database backup in order for the BACKUP LOG to complete without error.
If, by chance, you changed the recovery model of the database from simple recovery to full recovery since the last full backup occurred, you won't be able to do a BACKUP LOG until you take another full database backup.  Changing the recovery model doesn't actually take effect until a full database backup completes.  The following excerpt was taken from the Microsoft Docs:

Immediately after switching to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.
NOTE: The switch to the full or bulk-logged recovery model takes effect only after the first data backup.


mysql restore specific date data from a dump file

You have two alternatives:

1. Load the file into a new database table and do a query for the particular date that you want.

or

2. Hand edit the dump file and load that (also in a new database).

The second alternative has several disadvantages:

hand editing is unreliable and error prone.
you may break integrity constraints - not easy to see in a load of text!
it's a pain in the ass :-) and will probably take you more time than alternative 1.

Conclusion:

Load the whole file - query the data you want,

CREATE my_table AS SELECT * FROM main_table
WHERE the_date = date_of_interest;

and then put that into a separate table, and then:

mysqldump db_name my_table > my_table.sql

Et voilà!


How to select from two tables where the rows may be or may not be equal and group it if they are equal

FULL JOIN is not implemented in MySQL.

Possible realization (model):

SELECT filename,
       CASE SUM(table_from) WHEN 1 THEN 'In first table'
                            WHEN 2 THEN 'In second table'
                            ELSE        'In both tables'
                            END AS where_it_is
FROM ( SELECT filename, 1 AS table_from
       FROM table1
       UNION DISTINCT
       SELECT filename, 2
       FROM table2 ) AS combined_data_array
GROUP BY filename

If the field in question is unique in each separate table then use UNION ALL instead of UNION DISTINCT.


You can use SUSER_ID which will give you the SID from sys.server_principals regardless of metadata visibility

Also note that syslogins has been deprecated since SQL Server 2005


Disallowing MariaDB query cache except from webserver (JDBC)

If there is a lot of write activity, the Query Cache is virtually useless -- because all QC entries for a table are purged when any modification happens to the table.

If the QC is useful, using query_cache_type=DEMAND and manually saying SQL_CACHE or SQL_NO_CACHE on every SELECT may be optimal.  This keeps the rare, bulky, queries from cluttering the cache, while allowing the 'good' ones to make use of the QC.  Note:  This has nothing to do with the connector (JDBC, etc), only with the SELECT statements.

Yes, DEMAND without SQL_CACHE leads to not using the QC.  I agree that this is "not ideal" since it depends on un-obvious rules about the QC.  Still, it may be your best solution, other than...

If a query is slow slow that it matters, we should look critically at the query.  The speed-up may be as simple as adding a 'composite' index, or some seemingly minor tweak to the query such as not hiding an indexed column in a function call (eg, DATE(dt) = ...).


DB client tool -- To get the error message when open toad oracle client

Depending on YouTube video from @Philᵀᴹ, I have done this problem and I will try to list all I know and every step as following content, Hopefully I can help someone who has the same issues.

Download and Install Toad for Oracle v12.10

Open it you should can not use like as below error message

Download Instant Client 12.2

Unzip it and move it to anywhere you like (for example C:\app\instantclient_12_2)

Go to Control Panel\All Control Panel Items\System-->Environment Variable to "Add" TNS_ADMIN as like below picture

The same path to "Edit" Path like as following picture

OK done with toad client

To move your old tnsnames.ora to C:\app\instantclient_12_2 (for example)

Done all


Giving permission for each table within a database

In Oracle (and, realistically, most any reasonable database), everything is denied other than what is granted.  So simply grant the user the privileges you want him to have

GRANT select, update, delete
   ON table_owner.table_name
   TO username;

GRANT select
   ON table_owner.another_table_name
   TO username;


Permissions for mysql 'SHOW CREATE PROCEDURE'?

the second line in man says:

To use either statement, you must be the user named in the routine DEFINER clause or have SELECT access to the mysql.proc table. If you do not have privileges for the routine itself, the value displayed for the Create Procedure or Create Function field will be NULL.

The other option (just a suggestion) you may design a procedure or function (preferable)  to show the info in question.

Regards,


Backup job doesn't write backup when run on schedule

Does anything show up in the backupset tables for the missing backups?

SELECT  TOP 5
        bs.database_name,
        bmf.physical_device_name,
        bs.backup_finish_date,
        BackupSize = CONVERT( VARCHAR( 16 ),
            CONVERT( DECIMAL( 18, 4 ), bs.backup_size * 1.0 / 1024 ) ) + 'KB',
        AfterCompression = CONVERT( VARCHAR( 16 ),
            CONVERT( DECIMAL( 18, 4 ), bs.compressed_backup_size * 1.0 / 1024 ) ) + 'KB',
        bs.type
FROM    msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf
    ON  bmf.media_set_id = bs.media_set_id
WHERE   bs.database_name = '<Your Catalog Here>'
    AND bs.type = 'D'
ORDER BY bs.backup_finish_date DESC;

I would wager you are right that it's not a permissions issue, as if it was, the job history would indicate it.  Similarly, if the network path was simply unavailable at 7:00AM, there'd be something in the job messages about it, even if the jobstep itself was set to always report success.

SELECT  ji.name, ji.step_name, ji.step_id, ji.run_date,
        ji.run_time, ji.run_status, ji.on_success_action, ji.on_fail_action, ji.message
FROM (  SELECT  sj.name, sjs.step_name, sjs.step_id, sjh.run_date, sjh.run_time,
                sjh.run_status, sjh.message, sjs.on_success_action, sjs.on_fail_action,
                Ordinal = ROW_NUMBER() OVER (
                    PARTITION BY sj.name
                    ORDER BY sjh.run_date DESC, sjh.run_time DESC )
        FROM    msdb.dbo.sysjobs sj
        INNER JOIN msdb.dbo.sysjobsteps sjs
            ON  sjs.job_id = sj.job_id
        INNER JOIN msdb.dbo.sysjobhistory sjh
            ON  sjh.job_id = sj.job_id
            AND sjh.step_id = sjs.step_id ) ji
WHERE   ji.Ordinal <= 5
    AND ji.name = '<Your Job Here>'
ORDER BY ji.run_date DESC, ji.run_time DESC,
    ji.name, ji.step_id;

I'm guessing it's some subtle difference between the successful jobs and the one that's behaving oddly, like a conditional that's getting evaluated differently by the agent or similar code issue.


Please make sure that oracleserviceSID is running in your services.msc.

Then try restart your listener from services.msc itself or from command prompt(connect to sqlplus sys as sysdba).

(Hope you have configured listener.ora from netca)


Add columns to all tables in a database if the columns don't exist

If you only have around 50 tables, I personally wouldn't bother with a solution that uses a cursor. You can write a SQL query that looks at SQL Server object catalog views to generate the SQL code that you need to run to add the columns. One nice benefit is that you can easily review your code before running it. I think that the below query will be close to what you need for the DateTime_Table column:

SELECT 'ALTER TABLE ' + QUOTENAME(ss.name) + '.' + QUOTENAME(st.name) + ' ADD [DateTime_Table] DATETIME NULL;'
FROM sys.tables st
INNER JOIN sys.schemas ss on st.[schema_id] = ss.[schema_id]
WHERE st.is_ms_shipped = 0
AND NOT EXISTS (
    SELECT 1
    FROM sys.columns sc
    WHERE sc.[object_id] = st.[object_id]
    AND sc.name = 'DateTime_Table'
);

I used QUOTENAME to avoid issues with special characters in table or schema names. Once you've tested the generated code and are confident that it is correct, you can simply copy and paste the SSMS query results into another query window and run them.

If you needed a more automated approach or needed to deal with many more objects, you could use a cursor to find and loop through each table missing the column and EXEC to execute dynamic SQL to add the column.


Exporting/Importing a large MySQL database without mysqldump

For MySQL, you have these methods (beside mysqldump):

Select ... into outfile: support all engines, in can be use for a warm backup, it's slow for backup and recovery.
mk-parallel-backup: support all engines, used it for warm, backup and recovery speed is medium.
ibbackup: all engines, warm (if you use this method for only INNODB it can be hot), and it's fast for backup and recovery

here is a good image about backups.


In a banking organization, why would account balance be declared as varchar and not decimal?

We do this in our data warehouse. It's because decimal has a fixed precision and float isn't accurate past a certain number of decimal places. We need to retain the most accurate value extracted from the source as we are summing over 100 million rows and the rounding would put our figures out. However if you've got a fixed number of decimal places, then go for a decimal datatype or similar.


How do I loop through every table and alter the PK column like so? (Postgres)

What I suggest you do is to start up psql using the -E option.
This will give you a "blow-by-blow account" of the SQL that's used to perform operations in psql.
From issuing the command man psql:

-E
--echo-hidden

Echo the actual queries generated by \d and other backslash commands. You can use this to study psql's internal operations. This
is equivalent to setting the variable ECHO_HIDDEN to on.

Then, perform your desired operations on a sample table in psql and copy and paste the resultant "background" SQL into your editor of choice. Then write a PL/pgSQL routine that cycles through the tables you wish to change which does the same thing - monkey-see, monkey-do!
As @a_horse_with_no_name pointed out, this request has been cross-posted (to StackOverflow here). As an advanced PL/pgSQL question, it probably belongs here. So, could you please put a line at the top of your question on StackOverflow pointing here and telling people that any answers are to be put here and not on StackOverflow. This eliminates duplication of effort on the part of those trying to help you - thanks! Please see this post on the topic.
p.s. welcome to the forum!


Why "Data File(s) Used Size (KB)" PerfMon counter doesn't exist?

While it probably doesn't really answer your question I'll post it anyway, it may be more of a comment though.

I don't think anybody outside of Microsoft can give us a definitive reason why this counter was never added.

There is only minor feedback on this connect item asking for a similar thing, but it hasn't been closed as wontfix either:

"Percent Data Used" Performance Counter - by AussieJohn in which Microsoft's last suggestion is:

  Bill: thanks for the suggestion. sp_spaceused provides this
  information and much more. Do you want to expose all or a subset of
  the following info in perfmon?

  space reserved
  space allocated
  unused space that is reseved but not used
  space allocation between index and data

If you really want to expose this information through perfmon you could look at user settable counters as suggested by Marian in this answer


How to get statement which causes error 3609?

If you use TRY/CATCH in the calling code and trigger, then the error handling process becomes a lot more predictable and reliable.

Without this, an error in the trigger (here caused by RAISERROR) aborts the batch. So you'll never see the error from RAISERROR.

See Why TRY CATCH does not suppress exception in trigger for more

Note: a trigger is always in a transaction because it is part of the DML. Any trigger code/actions must be atomic with the DML of course.


Update trigger seemingly won't fire if multiple rows are updated

Your INSERT is nested under IF UPDATE(Stock_out) but then filters on i.Stock_out <> d.Stock_out which is redundant.

If Stock_out is ever allowed to be NULL then your WHERE clause will never match (because NULL IS NULL evaluates as true but NULL <> NULL always evaluates as NULL which will not match). Please check that.

I am unsure how the UPDATE() function responds to multi-row invocations as the Microsoft Docs don't seem to mention it! Somebody please update me on this. I would avoid using something with unclear behavior.


AVG using specific condition for each row

You could do something like this.

Quantity2015 = SUM(CASE WHEN Year = 2015 THEN QuantityTotal ELSE 0 END)
Quantity2016 = SUM(CASE WHEN Year = 2016 THEN QUantityTotal ELSE 0 END)

Not very flexible, but it does work.  You can use other aggregators than SUM as well.

Ok, so a full query example would look like this.  You might need to correct table names (especially for Client/Product).

SELECT Q.QuotationId
    , C.ClientName
    , P.ProductName
    , Quantity2015 = SUM(CASE WHEN S.[Year] = 2015 THEN S.Quantity ELSE 0 END)
    , Quantity2016 = SUM(CASE WHEN S.[Year] = 2016 THEN S.Quantity ELSE 0 END)
FROM Quotation AS Q
    INNER JOIN Client AS C ON C.ClientID = Q.ClientID
    INNER JOIN QuotationDetail AS QD ON QD.QuotationID = Q.QuotationID
    INNER JOIN Product AS P ON P.ProductID = QD.ProductID
    INNER JOIN Sales AS S ON S.ProductID = QD.ProductID AND S.ClientID = Q.ClientID
GROUP BY Q.QuotationId
    , C.ClientName
    , P.ProductName


Can a rollback/commit occur, independent, for each iteration inside a cursor?

After testing a lot I found the answer. Yes, it can since you run it AD HOC, without transaction control.

create table dbo.TOBEINSERTEDINTO
(
  ID int not null
)
GO

/*********************************************************************/
/***   Test scenario                                               ***/
/*********************************************************************/
create procedure TestCursor
as
begin
    declare @id int

    declare cursor_test cursor read_only fast_forward
    for
    select * from (values(1),(2),(3),(null)) as src(id) order by id desc

    open cursor_test

    declare @status int = 0

        while @status = 0
        begin
            fetch next from cursor_test into @id

            set @status = @@fetch_status

            if (@status = 0)
            begin

                if (@id is null)
                begin
                    throw 50000, n'!!!!!!!!! id cannot be NULL !!!!!!!!!!' ,1
                end

                insert into dbo.TOBEINSERTEDINTO(ID) values(@id)
            end
        end

    close cursor_test
    deallocate cursor_test
end;
GO

/*********************************************************************/
/***   Calling inside a transaction, with a try..catch can be ok   ***/
/*********************************************************************/

begin tran
begin try
    Exec TestCursor
end try

begin catch
    if @@trancount > 0
    rollback
end catch

if @@trancount > 0
    commit transaction;

select * from dbo.TOBEINSERTEDINTO

GO

/*********************************************************************/
/***   Lack of transaction control leads to inconsistence          ***/
/*********************************************************************/
exec TestCursor
GO

select * from dbo.TOBEINSERTEDINTO
GO

The application got a solid transaction control.

The problem originated because someone was tampering with the base. I guess someone put a profile to run and tried to mimic some app calls.


Is there an easy way to delete all records in multiple tables (but not every table)?

Borrowing from this answer, which drops tables with a common prefix, you could use something like this

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += '
DELETE FROM '
    + QUOTENAME(s.name)
    + '.' + QUOTENAME(t.name) + ';'
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id]
    WHERE t.name LIKE 'to[_]%';

PRINT @sql;
-- EXEC sp_executesql @sql;

The above example prints the delete commands.  When you are ready to actually run it, uncomment the EXEC


DBCC CHECKDB WITH DATA_PURITY gives out of range error

Use DBCC PAGE:

DBCC TRACE(3604);
DBCC PAGE('dbname', 1, 19558, 3);

and check the slot 13.


PostgreSQL - PostGIS ST_SetValues - int array cast to double and back

You're dong it wrong. I assume you're wanting a 32bit integer so you can bit shift around for RGBA (8bits per channel.) What you really want is a raster with 4 channels, 8 bits each. Because life will be easier.

ST_MakeEmptyRaster
ST_AddBand

Adapted from official docs...

SELECT
    *
FROM ST_BandMetadata(
    ST_AddBand(
        ST_MakeEmptyRaster(10, 10, 0, 0, 1, -1, 0, 0, 0),
        ARRAY[
            ROW(NULL, '8BUI', 255, 0),
            ROW(NULL, '8BUI', 255, 0),
            ROW(NULL, '8BUI', 255, 0),
            ROW(NULL, '8BUI', 255, 0)
        ]::addbandarg[]
    ),
    ARRAY[]::integer[]
);


can't backup 11.2.0.2 database after catalog upgrade to 11.2.0.4

You should be able to backup an 11.2.0.2 database using RMAN with an RMAN repository that is set to 11.2.0.4. There seems to be some invalid objects in your RMAN repository schema. You may want to try compiling all of the invalid objects in your RMAN repository to see if they become valid. This probably has nothing to do with you backing up an 11.2.0.2 database with an 11.2.0.4 RMAN repository.


For a point in time recovery, you need the series of transaction log backup files that are usually generated by regular, scheduled transation log backups in addition to the full database backup that you possess. Transaction log backups are often denoted by .bak or by .trn extensions. If you do not have those transaction log backup files, you can't do what you want. Roughly speaking, you would restore to a point in time by:
1. Restoring the full backup file using the NORECOVERY option.
2. Restore all of the relevant transaction log backups using the STOPAT option.
3. Lastly, bring the database out of recovery.


User who runs the package must have access to the folder to which you are writing the file. User also needs access to SQL Server with sysadmin rights.

Also, verify you have properly added all libraries.

For more, please read my blog article at http://www.dbadrive.in/2013/03/ssis-package-to-backup-replication_25.html.

Please share some more details of your code if the error persists.


Best way to store currency values on MySql database

Do not use FLOAT or DOUBLE.  If it is a single currency, then use DECIMAL(m,n) with m and n suitably chosen.  If different currencies are stored in the same column, values for m and n become messy.  And you can run into rounding issues -- because of different laws.

DECIMAL goes through a package that does INT-like stuff transparently.  DECIMAL(19,4) will be stored in

6 digits in (effectively) a `MEDIUMINT` (high-order digits)
9 digits in (effectively) a `INT`
4 digits in (effectively) a `SHORTINT`  (after the decimal point)


select runs in batch mode, but CTAS runs in row mode

Running explain on the query showed that something underneath was setting MAXDOP to 1.

Forcing the query to use 0 overcame this, as columnstores require DOP >= 2

Therefore adding the below hint enables batch mode

    OPTION (MAXDOP 0)

I don't understand why this is happening -  my understanding is that CTAS and building a new columnstore should always be parallel, but this resolves the issue.

Edit: This appears to be the default setting as it's aiming for the best optimum columnstore quality. So you have to make your choice between read performance and index quality or use an intermediate store, with each query having a different maxdop hint.


How to find the sql jobs that have not run for more than two months?

There is no way to assure you can get data for that historical range because-

This depends upon the settings of SQL agent where you decided to keep history based on certain conditions , unless left default which again may or may not go that time back.

If you are lucky enough and msdb has the information still stored for jobs 60 days old, you can simply query from sysjobs to get required data and filter based on your need


Replicating Results of Queries on Oracle

Depends how much data you need on the second database. Solutions are not to save query results on a second DB but duplicate data for querying on another DB. Basically the following technologies:

Oracle Streams: it is a replication from one database to another one. Can be done by table or by schema.
Oracle Active Dataguard: it is basically a standby DB open in ReadOnly for query. It is the standard oracle solution for the real time query. This requires a supplemental license
ETL: as suggested in the comment you can setup some ETL jobs using either an ETL tool or manually creating some stored procedure access data on remote DB. ETL is not, usually, a realtime process.
MATERIALIZED VIEV: if data and tables are a very small set you can create some MVs on your second DB that update periodically. Again this is not likely a realtime process and also may add load on primary DB.

In any case you need a sort of access to the primary DB. If you do not have any access apart the standard used you use for normal access through you application probably you can implement only ETL with a stored procedure or materialized view.


In strategy what do you try to ask?

RTO: Recovery time objective, how long do you have to restore data,
RPO: Recovery point objective, how much data are you allowed to lose when reverting to a restore,
SLA: How fast does it need to be and how are maintenance windows
managed between the data owners,
Features: What version of SQL server do you need,
Data sizing: How much data will the system have in N years from now, do you have a storage strategy, RAM needs?,
Data retention: how long do you need to retain the data for legal compliance,
Users: Data sensitivity and authentication schemes that need to be supported,
Lineage: where does the data come from and where does it go.
Governance: who in the business (not IT) owns what data and has authority to grant and restrict access
PII: what is held and what uses have been agreed to


self-joins in recursive CTE: `recursive reference must not appear more than once`?

Indeed error like recursive reference to query "x" must not appear more than once is some strange restriction applied in postgres. And I made assumption it is because their parser just simple distinguish recursive and non-recursive part of query by present of that table.
Meantime for that present nice workaround - you may use nested CTE (WITH statement), and give another name for such table. For your initial example it will look like:

WITH RECURSIVE
x (id) AS (
  SELECT 1 id
  UNION ALL
  SELECT * FROM (
    WITH x_inner AS ( -- Workaround of error: recursive reference to query "x" must not appear more than once
      SELECT * FROM x
    )
    SELECT x1.id+x2.id
    FROM x_inner x1, x_inner x2
    WHERE x1.id < 5 AND x2.id < 5 AND x1.id = x2.id
  )t
)
SELECT * FROM x;

You could try it in SQL fiddle.


How to properly stop MySQL server on Mac OS X?

DISCLAIMER : Not a MacOS user

That is an alternative way. This shutdown method can be done in MacOS, Linux, Windows, any platform MySQL is supported in.

I actually prefer your mysqladmin method for a reason...

In Linux, I have seen the mysql.sock (the socket file) file just up and disappear without warning. The standard way to shutdown mysql in Linux is service mysql stop. Whenever the socket file disappears, the mysqld_safe program cannot shutdown without first seeing in the socket file. Such a standard shutdown would just hang.

I usually connect using TCP/IP

/usr/local/mysql/bin/mysqladmin -u root -h127.0.0.1 -p shutdown

Doing it this way bypasses checking for a socket file. Of course, you must make sure root@127.0.0.1 exists. Run these lines to create that user:

SET SQL_LOG_BIN=0;
GRANT ALL PRIVILEGES ON *.* TO root@'127.0.0.1' IDENTIFIED BY 'whateverpassword';

CAVEAT If you do DESC mysql.user;, you will see that one of the privilege columns is Shutdown_priv. This evidently would permit a user who connects to mysqladmin to issue a shutdown. Users with this privilege cannot issue a shutdown from the mysql client. Users with shutdown_priv='Y' can only be done from mysqladmin.


How do I get article properties from query in a merge replication

This is covered in View and Modify Article Properties, in the section To view the properties of an article belonging to a merge publication:

Execute sp_helpmergearticle, specifying the name of the
publication for the @publication parameter and the name of the
article for the @article parameter. If you do not specify these
parameters, information will be returned for all articles in a
publication or at the publisher.

If you want to generate scripts to create replication components, this is covered in Scripting Replication.


Failover is easy to do. That just requires shared storage, and a couple of servers.

SQL Server doesn't support scale out to multiple nodes.  However a properly designed database with only a couple of thousand users should be able to run on a dual chip server pretty easily.  My companies systems support about 35k transactions per second on a quad chip server (with 6 cores per chip).

Like any database server you'll want to use multiple LUNs for each part of the system.  1 for data, 1 for indexes, 1 for transaction logs, one for tempdb.

If you can't afford a SAN (you'll be looking at a six figure purchase for the SAN alone) then you should look at database mirroring instead.  This when used with a witness server can give you automatic failover in the event of a failure, and doesn't require shared storage.  The different volumes for storage should still be used.

Before you can design your high availability solution (clustering, mirroring, log shipping, etc) you need to figure out what you are trying to protect against.  How much data are you willing to loose?  How long can you afford to be down during failover?


What folders should I check to see if these MongoDB applications are installed on my server?

You can find these programs using the Linux find command as such:

find / -name 'program_name' -print 2> /dev/null

Where 'program_name' is for example, bsondump.

find / -name 'bsondump' -print 2> /dev/null

The 2> /dev/null prevents find from printing all the paths it looks at.


It sounds like you may benefit from a Failover Cluster if all you're looking for is availability. You can run them on standard edition and if you take an N+1 route, you can have two nodes for HA and have one in a DR site ready to take over. You'll need to work on your storage availability for the DR side, but that's a whole other can of worms.

Another option may be Log Shipping since you're already taking log backups every minute. You can ship the logs off to your HA/DR server.

You can get more information on how to do it and the implicatioons for AWS here:

Implementing Microsoft Windows Server Failover Clustering and SQL Server AlwaysOn Availability Groups in the AWS Cloud


Enforce known functional dependency in a group by clause

create table users (user_id int, user_name text);

insert into users values
(1, 'user1'),(2, 'user2'),(3, 'user3'),(4, 'user4'),(5, 'user5');

create table products (user_id int, id int, name text, att1 text);

insert into products values
(2, 1, 'prod1', 'att1'),
(2, 2, 'prod2', 'att1'),
(2, 3, 'prod3', 'att1'),
(2, 4, 'prod4', 'att1'),
(3, 5, 'prod5', 'att1'),
(3, 6, 'prod6', 'att1');

IMHO your first query should throw the same error message.

select
    users.*,
    json_agg(products.*) as products
from
    users
left join products on
    users.user_id = products.user_id;

  ERROR:  column "users.user_id" must appear in the GROUP BY clause or be used in an aggregate function
  LINE 2:     users.*,
              ^

But you can move json_agg to a subquery instead of joining both tables, then you can easily limit the number of returned rows.

select
    users.*,
    (select
        json_agg(products.*)
     from
        products
     where
        user_id = users.user_id)  as products
from
    users
limit 3;

+---------+-----------+------------------------------------------------------+
| user_id | user_name | products                                             |
+---------+-----------+------------------------------------------------------+
| 1       | user1     |                                                      |
+---------+-----------+------------------------------------------------------+
| 2       | user2     | [{"user_id":2,"id":1,"name":"prod1","att1":"att1"},  |
|         |           |  {"user_id":2,"id":2,"name":"prod2","att1":"att1"},  |
|         |           |  {"user_id":2,"id":3,"name":"prod3","att1":"att1"},  |
|         |           |  {"user_id":2,"id":4,"name":"prod4","att1":"att1"}]  |
+---------+-----------+------------------------------------------------------+
| 3       | user3     | [{"user_id":3,"id":5,"name":"prod5","att1":"att1"},  |
|         |           |  {"user_id":3,"id":6,"name":"prod6","att1":"att1"}]  |
+---------+-----------+------------------------------------------------------+

db<>fiddle here


Clustered Index and Primary Key on table with unique identifier and date

The main characteristic of the clustered index is that the data is all in that order.  That allows SQL to "read ahead".  So for example if you created your CI on the Date column and ran queries pulling summary info for a week or a month then SQL could pull the data more quickly.

If on the other hand you are having to do seeks (a single Id for example) then the CI is no different than a covering NCI.  Note the covering part of that.

Here is something you could try.  Place your CI on the Date, OrderId combination.  Do this because you are more likely to pull range data on a date than an Id.  Also because you said that 80% of your queries use the date.  Some portion of these will presumably also use the OrderId.

Then see what columns the queries that just use OrderId use and add those columns to an OrderId NCI using the INCLUDE clause.  And just to save time if the answer is "they are using all of the columns on these queries" then a) you need to look at those queries and see if they really need all that data and b) yes you could INCLUDE all of the columns in the table.

I realize that would be a HUGE INCLUDE.  In fact it would double the size of your table and you'll want to carefully test that you aren't going to have a serious negative impact on write operations.  However for reads it should work just fine.


Eliminate duplicates in ListAgg (Oracle)

You can use regular expressions and regexp_replace to remove the duplicates after concatenation with listagg:

SELECT Num1,
       RTRIM(
         REGEXP_REPLACE(
           (listagg(Num2,'-') WITHIN GROUP (ORDER BY Num2) OVER ()),
           '([^-]*)(-\1)+($|-)',
           '\1\3'),
         '-') Num2s
FROM ListAggTest;

This could be tidier if Oracle's regex flavour supported lookahead or non-capturing groups, but it doesn't.

However this solution does avoid scanning the source more than once.

DBFiddle here


Galera: any drawback of enabling wsrep_log_conflicts and cert.log_conflicts?

This information may not be of interest to the DBA. If the application is handling conflicts correctly, there is no need to log them. It would be expected behaviour. You would only enable this level of logging if you were debugging a specific issue. Otherwise it would generate a lot of spam in the logs.


How can i automate DIfferential restore using dbatools Powershell

Can you share what you've come up with so far?
In the Restore-DbaDatabase you can use -IgnoreDiffBackup to ignore the differential backups, so I presume there should be no problem with automating your diffs.
Can you add more details what you are trying to achieve and how your backup process looks like?


Why is ADD CONSTRAINT ... NOT VALID taking a long time?

The ALTER TABLE needs an "access exclusive" lock on the table.  While it will only hold that lock for microseconds once it does gets it, it still might block indefinitely while waiting to acquire it if any other session is already holding any lock on the table.  And once it does block then other sessions, even ones wanting a weaker lock, will block behind it.  (PostgreSQL does not allow queue jumping, so a strong lock which is waiting to get granted will block incoming weak locks, even if those incoming locks would be compatible with the lock already being held.)

If you want the ALTER TABLE to either succeed or fail nearly instantly, set lock_timeout to a low value.  Trying to run them as separate statements would make the situation worse, not better.


query_cache_type = 0 vs query_cache_type = 1

For a Wordpress blog it should be fine to set query_cache_type = 1. See, the major problems with the query cache are:

It invalidates very easily (any update on some table invalidates all queries related to said table)
It has a single mutex on which any incoming or outgoing query must go through.

The query cache was fine in the days where machines had one core, maybe two; it does not fare well with multiple cores and high concurrency, and it does not fare well with write intensive applications.

Your Wordpress blog is not likely to be write intensive: your writes are new posts, updates, comments... How many of these? You may not even counter them on a per-minute basis...

It would actually not be read intensive either. Hundreds of reads per day Thousands? That's nothing.

With regard plugins, that greatly depends on the plugin and on your deployment. Wordpress is written in PHP, and PHP might use such cache mechanisms as file system, memcached or APC. It should be typically "better" caching for the specific purpose it is oriented. That is, it will invalidate cached data based on real changes to relevant data -- not for irrelevant operations on some table. The programmers of the plugin would need to decide which pieces of data are important to cache, which are not, how invalidation works.

Again, in the scale of a blog, this is no big deal, and should not make a significant difference (of course there could always be some crazy plugin).

Bottom line: for your needs, anything goes and it does not matter much.


How to Exclude Foreign Keys during Publish action in sqlpackage

You need to disable deployment option "Script validation for new constraints".
Foreign keys will be still created, but in not trusted state and existing data will not be validated.
Publish script will contains something like:

ALTER TABLE [Tab] WITH NOCHECK
    ADD CONSTRAINT [FK_Tab] FOREIGN KEY ([Id]) REFERENCES [Tab2] ([Id]);

This will allow you to deploy all constraints without errors, but you still need to disable them in post deployment script.


Check 1 Table If No Data Check Second Table

You could try something like this query:

Declare @d1 date, @d2 date, @numreturned int
Set @begindate = '01/01/2015'
Set @enddate = '01/12/2015'
Set @numreturned = 0

SELECT @numreturned = COUNT(DISTINCT CAST(csdate as date))
FROM firsttabletocheck
WHERE csdate >= @begindate and csdate < @enddate;

IF DATEDIFF(day, @begindate, @enddate) > @numreturned
BEGIN
    SELECT DISTINCT CAST(csdate as date))
    FROM secondtabletocheck
    WHERE csdate > @begindate <= @enddate
        AND CAST(csdate as date) NOT IN (
            SELECT DISTINCT csdate = CAST(csdate as date)
            FROM firsttabletocheck
            WHERE csdate >= @begindate and csdate < @enddate
        )
END

I am using CAST(csdate as date) everywhere because you datatype seems to be datetime. If you are not using the time part, you should consider changing the type to date. This will very likely leads to table scan with the second query.

I added a distinct because I don't know whether you have duplicates or several rows on the same day. If there is only 1 row per day, this is not needed.

I replaced the between by >= and <. (see @ypercubeᵀᴹ comments)

I am not sure what sort of output is needed precisely. You will have to adapt this query to your needs or add sample data and sample output. Right now it only show csdate missing from firsttabletocheck but present in secondtabletocheck.


How can I generate a script to choose only top 10 items from each table in SQL database?

If you go to the following stack exchange link and scroll down to the second answer where you see Neeraj Prasad Sharma create a stored procedure named INS, if you go into that stored procedure and modify the line

Select @CONDITIONS= 'Select  '+@CONDITIONS +'FRom  ' +@Schema_name+'.'+@Table_name+' With(NOLOCK) ' + ' Where '+@Condition

to be

Select @CONDITIONS= 'Select top 10 '+@CONDITIONS +'FRom  ' +@Schema_name+'.'+@Table_name+' With(NOLOCK) ' + ' Where '+@Condition

Here is a quick table I put together so you can do that and run it to see how it works

CREATE TABLE tbltest
(
    id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    ,col1 VARCHAR(20)
);
GO
INSERT INTO dbo.tbltest( col1 )
VALUES  ( 'test1' ), ( 'test2' ),( 'test3' ),( 'test4' );
GO
EXEC ins 'dbo.tblTest where 1=1 order by id'

From here you might have to generate your Exec INS executions by using sys.objects or information_schema.tables, but this should do what you are looking to do as long as I understand correctly what you are trying to do.  As Mr. Bertrand said in his comment, this does not take any relationships into account.  But if you are just looking to generate top 10 inserts for each table ordered by something, this should do that for you.


How to delete data from both tables or least one of them, when using "delete from ... using ... where" in Postgresql?

A single DELETE statement only deletes rows from a single table (ignoring table inheritance for now). So if you want to delete from two tables, you have to run two delete statements.

You can do it in "one" statement, but using a common table expression that first deletes from table2, then from table1:

with t2_deleted as (
  delete from table2
  where frg_table1_id = $1
)
delete from table1
where id = $1;

But in the end, this is exactly the same as simply running two DELETE statements in a single transaction:

delete from table2
where frg_table1_id = $1;

delete from table1
where id = $1;


SELECT  state, num_cities, @n := @n+1 AS rank
    FROM
      ( SELECT  state, count(*) AS num_cities
            FROM  us
            GROUP BY  state
            ORDER BY  num_cities DESC
      ) AS x
    JOIN
      ( SELECT  @n:=0 ) AS init

+-------+------------+------+
| state | num_cities | rank |
+-------+------------+------+
| CA    |        447 |    1 |
| FL    |        264 |    2 |
| TX    |        238 |    3 |
| IL    |        221 |    4 |
| NY    |        185 |    5 |
| OH    |        185 |    6 |
| MA    |        180 |    7 |


Executing a SQL Stored procedure from a SSRS text box item - Is it possible

I think this used to be easier when SQL Server included SOAP/web services with SQL 2005/2008. With SOAP you could create a wrapper to a SQL web service that would call a stored procedure with a URL. Microsoft gutted that functionality in future releases of SQL Server 2012 and above. I miss the functionality.

I can think of three ways to handle this but there's probably more:

First create an ASP.NET web service

Here's a tutorial to get you to connecting to a database and accessing the web service with HTML. So basically, you could call your stored procedure from anywhere--not just SSRS.

https://docs.microsoft.com/en-us/aspnet/web-api/overview/older-versions/build-restful-apis-with-aspnet-web-api#Exercise3

And, second, embed custom code with SSRS (here's a few examples I thought were useful):

http://www.davidgiard.com/2012/09/07/EmbeddedCodeInAnSSRSReport.aspx

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cdf290fb-153d-4bc2-8964-8b550df7a087/calling-stored-procedure-from-custom-code?forum=sqlreportingservices

Last, create an assembly to call a custom function (that calls your procedure):

http://davidgiard.com/2012/09/11/CallingAnExternalAssemblyFromSSRS.aspx


Copying MySQL tables, InnoDB tables not recognized

While .frm, .MYD, .MYI are enough for MyISAM, ibdata1 (InnoDB Global Table space) is needed for InnoDB even if you are using innodb_file_per_table = TRUE.

ibdata1 holds meta data for InnoDB Tables. InnoDB Table can't be found without those meta data. So you are getting "Table 'table_name' doesn't exist". error.

MySQL Manual states that

  You cannot freely move .ibd files between database directories as you can with MyISAM table files. The table definition stored in the InnoDB shared tablespace includes the database name. The transaction IDs and log sequence numbers stored in the tablespace files also differ between databases.

The main reason is Table id is different both source and dest. DB's ibdata1. So ibdata1 should be moved from src to dest.


Storing personal data on an invoice to preserve data integrity

The solution I have seen is copying address data to the InvoiceHeader table.  This is redundancy, but it is not harmful*.  Update anomalies do not occur, because the address on the invoice should not be changed later.

An analogous point could be made about copying product price data into the InvoiceDetail table.

* Update anomales only occur for harmful redundancy.  Still, if the customer address changes in the customer table at a later point in time, but remains unchanged in the InvoiceHeader table, then they will no longer be redundant.


HADR solutions for SQL Server 2016 Standard Edition

Log Shipping for DR.  Not so much HA as no automatic failover, but still better than nothing.   Use Windows cluster for HA, and Log ship them to somewhere else.

transactional replication IS allowed in standard:
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-supported-features-for-sql-server-2016#a-namereplicationa-replication

But is not really an HADR solution


Challenging MySQL query, compare two records to find if they match or not

You can do this in SQL and make it very efficient at the same time. Essentially what we'll do is join the table to itself on your two Ids. Once you have a full set of all matching IDs you can compare the additional field values. To do this efficiently you can build them out into a single string, and hash it, then compare the hashes. It is exceptionally efficient and effective. Abbreviated example below.

SELECT
     id
    ,asset_id
FROM
(
 SELECT
     id
    ,asset_id
    ,SHA1(left + '|' + top) As DataCompare
    FROM ad_transitions
) As FirstSet
JOIN
(
 SELECT
     id
    ,asset_id
    ,SHA1(left + '|' + top) As DataCompare
    FROM ad_transitions
) As SecondSet
    ON FirstSet.id = SecondSet.id
    AND FirstSet.asset_id = SecondSet.asset_id
WHERE FirstSet.DataCompare = SecondSet.DataCompare

The pipe is added to the end of each field string (convert where necessary) to prevent issues that could result from odd but always possible data patterns such as the following:

Name     RandomText
Joh      Normand
John     Ormand

If just these two items are hashed, as is, they will be identical, despite they clear fact they are very different. The addition of a pipe after the field fixes johnnormand to joh|normand and john|ormand ensuring a unique hash and thus no accidental duplicate detection.


What are the default fixed server role members for SQL Server 2012

On a clean install, just do:

SELECT role_name = pr.name, member_name = m.name
FROM sys.server_role_members AS r
INNER JOIN sys.server_principals AS m
ON r.member_principal_id = m.principal_id
INNER JOIN sys.server_principals AS pr
ON r.role_principal_id = pr.principal_id;

Here's what I get:

sysadmin    sa
sysadmin    CANUCK\aaronbertrand -- Administrator account I added during setup
sysadmin    NT SERVICE\SQLWriter
sysadmin    NT SERVICE\Winmgmt
sysadmin    NT Service\MSSQL$InstanceName
sysadmin    NT SERVICE\SQLAgent$InstanceName


No, there is no means of partitioning either an IDENTITY or SEQUENCE. In order to do this you would have to create a table to manage TenantID and RecordID for this table. Then you would need a function that accepts TenantID and will return RecordID. This means that you need to insert via a stored procedure that can grab that value and then do the INSERT, or maybe you can get away with an INSTEAD OF trigger, but I am not sure about that given that both TenantID and RecordID need to be part of the PK.

And if you want this pattern to apply to all tables then you would need to duplicate that custom sequence per each table. This is ok for maybe a few tables, but not more than that.

There are rare occasions when this type of behavior is desirable, but the current reason for requesting this seems a bit suspect (noted in a comment on the question):

  for capacity...1 mil record with id partitioned by 50 tenants will have highest id around 20k, while not partitioned will have highest id of 1 mil.

This is not (or at least should not be) a valid concern. The INT datatype has a capacity of just under 4.3 billion values. If you start the IDENTITY (i.e. the "seed") at 1, then you get half that range since this is a signed INT and not an unsigned INT. If you want all 4,294,967,296 values, then use IDENTITY(-2147483648, 1).


You cannot run

SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) from mysql.user WHERE user='root';

because you do not have remote rights to the mysql database.

You only have remote rights to mydb

GRANT ALL PRIVILEGES ON `mydb`.* TO 'root'@'%' WITH GRANT OPTION;

When you ran

mysql -h x.x.x.x -uroot --database=abc

you evidently do not have remote rights to database abc.

When you ran

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

you should now have rights to every database.


Are database connections from an application expensive from a performance point of view?

Well, there are four main aspects to this:

Why are you asking the general Interwebs for an opinion when there are three other sources that should be checked first, one of which will be definitive and not an opinion:

When someone tells you a technical / scientific piece of information, whenever possible, ask them how they know that to be the case. Did they read it somewhere? If so, was it a reputable source or was it mentioned in passing in a conversation they had several years ago that they were only partially paying attention to and also never asked that person how they happened to know this information. If it was an article / blog post, ask to see it so you can read it for yourself. If they did some testing, ask to see the test scripts so you can run them yourself.

The point is, a lot of people say a lot of things, sometimes being honestly convinced of the validity of information that is absolutely not true. So it is good of you to not just take their word for it, but whoever conveyed the information should be asked why they believe it to be true. Their answer could prove to be very insightful on a few levels :-).
Whether or not you are able to get an answer from the person making technical claims, do your own research. Do some searches to see if technical articles on this topic have already been written. Simply searching on "database connection performance" returns quite a few articles. Just be wary of random blog posts as people sometimes post information that they believe to be true but in fact isn't. And that brings us to....
The absolute best source of information for technical matters, especially with regards to "is approach A faster or slower than approach B" types of questions, is the system itself. Who cares what your colleague says, or even anyone else, no matter how well respected they may be, when your system will behave the way it is going to behave and it cannot be argued with. If someone says calling 8 procs in one call to the database is faster than 8 connections, well, try both and see which is faster. Of course, you may or may not be doing things correctly, but once you have a test set up and some results, then you can present those to see if somehow you missed something. And you can check against articles that have been written to see if their is a trick or option that you missed.

Generally speaking, yes, establishing new connections is expensive, which is why Connection Pooling exists. It keeps the connection open so that it can be re-used by the same client. Making a whole new connection requires allocating memory, checking security, which might involve making a request to yet another server if using Windows Authentication in an environment that uses Active Directory (since the auth info, if not yet cached, resides on the Domain Controller). All of this overhead is skipped when using Connection Pooling. If you are using .NET and connecting to Microsoft SQL Server, then Connection Pooling should be enabled by default. But you should check to make sure that it is used being.

Regardless of whether or not Connection Pooling is being used, are multiple calls in a single query batch faster than individual calls made via separate query batches? Well, if the issue is one of closing and re-opening the connection being expensive, then why close the connection? Why not just make 8 separate stored procedure calls on the same connection? At that point the only performance difference is the time it takes to make the 7 additional stored procedure requests. And that would be so minimal as to not even be worth thinking about unless this group of 8 stored procedures is being called many times per second. And that brings us too...
Most often, performance is a function of scale. There are many inefficient operations that do not show their inefficiency when only being called once or twice per day, or once or twice per month. But perform those operations multiple times per minute, or even second, and that same poor choice can have a drastic effect on performance. There are many bad choices people make when choosing datatypes for new fields (whether new table or just a new column on an existing table). They say, "disk is cheap so it doesn't matter than an INT is 3 bytes larger than a TINYINT and this field will only ever hold values of 1 - 10". An extra 3 bytes for 100 or even 1000 rows will never (or maybe I should just say "almost never" ;-) degrade performance. But that same field, if a PK, copied into 100+ tables as a Foreign Key, and those 100+ tables having a combined row count in the hundreds of millions range, will have an impact, and not just on query performance, but also backup and restore times, etc.

So, how often will these 8 stored procedures be called? If only a few times per hour or less, then you probably won't see much of a performance difference whether or not you call all 8 in one shot, or make 8 calls on the same connection, or make 8 calls on separate connections using Connection Pooling, or even making 8 separate calls on separate connections not using Connection Pooling. In this case you can also ask your colleagues why they are even concerned about connection overhead and why over-complicate a process for almost no gain.
Finally, since you mentioned using DataSets, does this mean that if you do make these 8 stored procedure calls in one shot, that you are loading all 8 result sets fully into memory at that time? If so, how large are these result sets? And not just in terms of rows, but also the size of each row? Are all 8 result sets needed at the same time? And are all 8 result sets always needed or are some only needed sometimes? This could be a situation where someone is trying to gain some efficiency on the connection overhead, but actually sacrificing a good amount of memory in order to do so. And if the connection overhead is really that much (due to the 8 procs being called many times per second AND no ability to use Connection Pooling), then maybe the additional memory overhead is worth it. But if not, then you could be wasting memory in order to solve a connection performance issue that might not even exist in the first place.


Consider using the WITH syntax that has the subquery above the outer SELECT and has the better maintainability you were looking for:

WITH subquery (a, b) AS
(
    SELECT x, y FROM whatever
    UNION
    SELECT z, w FROM something
)
SELECT a, b FROM subquery;

... runs on Oracle 11.2 according to: http://sqlfiddle.com/#!4/d0b42/2/0


MariaDB subquery cache scope and settings

The cache needs to return the same results as running the subquery would, so the scope has to be limited by active transaction too.

But seeing that the cache can only be enabled or disabled as an optimizer feature and  not configured otherwise, my guess is that it actually is instantiated once for each specific cacheable subquery for a query where the optimizer decides it is worth it.

That seems to be confirmed by: "It has a unique index over all parameters." - this is not doable if you store results for more than one subquery in the same table.

So it seems to me that the cache is instantiated once for each cacheable subquery and its lifetime would be limited by the outer query execution.

Edit: I checked some source code (disclaimer: I definitely do not fully understand the code as MariaDB is quite big and complex piece of software and my knowledge of C++ is far from expert level). The cache is implemented by the Expression_cache_tmptable. The table used to store the cached results is created in the init() method which is being called from some methods in Item_cache_wrapper and AFAIK lifespan of various Items is single (outer) query execution as different types of Items keep and manage different parts of the query (sort of expression tree if I understand it correctly).

From that I deduce that one instance of the subquery cache is created for each cacheable subquery at the time query is being "prepared" for execution. It is then initialized (and the temp table created) when it is first accessed during the query execution (so only if the subquery is actually executed at least once). The condition of "<0.2" is then checked (for each subquery independently) after 200 misses (that will probably be after processing some 200+ rows of the outer query) to see if the cache has any positive effect, and it is disabled early if it does not help. Then, if the cache table grows too much, it is checked again according to the conditions listed in the manual.

So the subquery cache is not tuneable, but seems to be made as unintrusive as possible. The optimizer will ever instantiate it for subqueries which can at least theoretically use it and it will be disabled early in the query execution if it does not prove useful.

If you are still worried about it slowing your queries down, you can disable it globally by configuring the optimizer_switch and then enable it only for selected queries by calling

SET SESSION optimizer_switch='subquery_cache=on';

before the query execution and optionally turn it off afterwards.


How can I store user generated content in postgresql or rdbms?

I can't comment so I'll post here.
If I understood you correctly, you wish to know which posts each user created?
If so.. create another table "user_posts" which will have a foreign key with a user_id and then all your post data, Then if you want to find which posts are associated with each user you can use a JOIN.


Granting minimum select permission on system "sys" tables in every database

I can't seem to find a way around this other than creating a user for
  the login for every single database

Starting with SQL Server 2014 the new CONNECT ANY DATABASE permission can be used to accomplish this

  ...and then granting permissions to the system tables explicitly.

You have no need to grant permissions on system tables, they are visible to any user. But the visibility of objects they contain depends on the permissions the user has: he can see only those objects on which it has permissins: Metadata Visibility Configuration

And this can be solved by giving to your login VIEW ANY DEFINITION permission

Also, there was no need to give VIEW ANY DATABASE TO permission because it's already granted to public, and  VIEW SERVER STATE TO is not required for the metadata you described retrieving.

So, all you need are these permissions:

CONNECT ANY DATABASE to access all the databases
VIEW DATABASE STATE for querying sys.dm_db_partition_stats
VIEW ANY DEFINITION for have the rights on any metadata


Insert much slower when target table has clustered index

Let's take a step back and forget all of the troubleshooting around the clustered index. You have an INSERT query which used to finish in a reasonable amount of time but now won't finish after hours. Why might that query now be slow? Let's take a look at the estimated plan:

Reading from right to left, the plan is to first scan the single row from Extract_DW_Control_Finance, do a loop join with a scan of Extract_JCS_Trans on the inner side, sort the data according to the clustered key of the target table, and do another loop join with a scan of Extract_GL_Jnl_Trans on the inner side. The first join probably isn't the issue. The plan can't actually benefit from parallelism, but with a single row in the outer result set the scan on Extract_JCS_Trans should only happen once. However, the optimizer estimates that a single row will come out of that join. If that row estimate is wrong then you could end up doing hundreds of thousands of clustered index scans on Extract_GL_Jnl_Trans.

The query plan for the query which performs well uses a different strategy. The row estimates are significantly different and it performs a hash join:

I suspect that the optimizer will choose a different plan for the poorly performing query if you fix the row estimates. If the Extract_DW_Control_Finance table will always have one row you could consider moving that into a local variable and possibly using a RECOMPILE hint. That could result in a much better estimate.

In terms of why removing the clustered index causes the issue, I suspect that the optimizer does a hash join to Extract_GL_Jnl_Trans without the clustered index. A hash join does not preserve the order of the outer input but a loop join does preserve order. The optimizer may have costed doing the sort on a single row and performing a loop join lower than doing a hash join and performing the sort later on 356566 rows. However, if the sort is not needed then doing the hash join may have had a lower cost than the loop join. It probably all comes down to fixing your cardinality estimates.

If you need to do more troubleshooting while the slow query is running, you could consider trace flag 7412 if you're on SQL Server 2016 SP1. That should given you clues as to where SQL Server is "stuck" in the query plan. If you're able to ask for an actual plan or run the query directly in SSMS you could use sys.dm_exec_query_profiles or the live query statistics feature.


You often need pg_rewind because the standby server that got promoted might have been a few transactions behind the broken primary server. This can even happen with synchronous replication.

pg_rewind undoes these few transactions by replacing all affected blocks with blocks from the promoted (source) server. That is also why recovery will start from the last common checkpoint of both servers.


Efficient join with data spread into different tables

You've got a couple of options - but none of them are likely to produce lightning-swift results.

Note I'll be making a few assumptions below. The main one is that all the usersXXX tables have the same structure (same column names, same datatypes).

Massive LEFT JOIN - if there are fewer than 256 usersXXX tables (or more, maybe - see comment below code), and you're only joining to the central table (we'll call that User, for convenience), then you can (in theory) create a single statement like the following:

SELECT U.ID, U.firstname, U.lastname, COALESCE(U001.field1, U002.field1, ... , U255.field1) as field1, ...
  FROM User U
         LEFT JOIN users001 U001 ON (U.ID = U001.ID)
         LEFT JOIN users002 U002 ON (U.ID = U002.ID)
        ...
         LEFT JOIN users255 U255 ON (U.ID = U255.ID)

Note that all references to a given field from the usersXXX tables, will need to COALESCE the field from the possible 255 tables.

SQL Server claims to have an upper limit of 256 tables in a query, which is why that's our limit here. However, the documentation is not consistent. On another page the maximum number of tables per SELECT statement is "Limited only by available resources". In addition, experiments have shown that queries can contain more than 256 tables. Another test had involved 599 tables.

The awkward nature of referring to the COALESCEd fields makes this hard to maintain and hard to read.
Your current view. I'm assuming (if you're using the view to do calculations over all users) that you may not necessarily be joining to the User table.

So, your view probably looks something like this (you can add an INNER JOIN to User if you want):

SELECT field1, field2, ... FROM users001
UNION ALL
SELECT field1, field2, ... FROM users002
UNION ALL
...
UNION ALL
SELECT field1, field2, ... FROM usersZZZ

You've already stated that this leaves something to be desired performance wise - but we'll get back to that.
Next, there's a direct use of UNION ALL. Instead of going through a view (simplifying the construction of the query), use the actual UNION ALL of the tables directly. This may perform better that option 2, if you're using a WHERE clause. It depends on how the query optimizer handles things - if there's relatively few records to bring back, but the query optimizer insists on materializing the view before applying the WHERE clause, then this will help. If (as I suspect) the query optimizer would apply the WHERE clause at the individual table level rather than trying to materialize the view, then this would probably have little performance benefit.
Last, you can query each table individually, and put the results in a temp table. The possible performance advantage here would be that each individual query would be much simpler, would take far less time to generate a plan for, and would run relatively quickly. The disadvantages are that a separate plan would be generated for each table (meaning that even the relatively short time to generate these query plans must be multiplied by the number of tables for the first run), and that the "simple" query must be run NNN times (where NNN is the total number of tables).

So, the real question - what's making the current query so costly? If it's simple the amount of data that must be gone through, then playing with alternate queries is unlikely to result in something dramatically faster. If, on the other hand, the sheer volume of different tables involved is causing a really convoluted plan to be generated, than an alternative that might create simpler plans (maybe option 3, definitely option 4) could be worth a try.

You'll probably want to look at the actual query plans being generated, in addition to looking at the time each alternative takes.

Also, keep in mind that perfection is not always desirable. If the current query is being used for reporting, for example, and the reports are taking longer than you'd like, but an acceptable amount of time, then spending a lot of time to shave off 25 of the current run time may simply not be worth it. If, on the other hand, a daily report is taking 25 hours to run, that's a different scenario.


The whole idea around Redshift is not to run in a single node. It is actually designed to run in a sharded cluster and it is expected to have very bad numbers within only one node. If you look at the internals you'll see that is actually designed to run on top of a set of nodes, adding an extra layer for the query processing.

If you are already familiar with the basics, you can check the Best practices.


How to apply filter at runtime in SSRS 2008 R2

Probably the easiest way would be to use a drill through report. Add an action to the row group that links to a new report. Duplicate the layout of the new report, but implement a filter on the dataset of interest.

Pass the row group value ([SQLFieldName]) to a parameter of the report. If you use a multivalue variable with an IN statement in the SQL, several filters can be used at once.


This value is encrypted by QlikView to prevent you from tampering with the value.

Some fields in the generated XML file i.e. "LastUsed", are encrypted
for security reasons, thus cannot be converted into readable dates
using date() conversion. The generated XML files should be used for
troubleshooting only.

https://community.qlik.com/t5/Support-Knowledge-Base/Reading-PGO-contents-and-CAL-assignments-PGO-to-XML/ta-p/1710604?_ga=2.160240829.1496857553.1606312645-1955769053.1606312645
Presumably this is part of how they track and enforce their sofware licensing agreements.


Changing hostname IP address in mongodb configuration

Address 0.0.0.0 (itself) don't exists.

It is used at config files as value what means "listen all network devices".

If you want to have mongos at different server, you need to configure your cluster to use address what is connected to network device what point outside world (maybe eth0). All components of that cluster (config server, all shards) must be using that "external" IP-address what is reachable from that machine where you are going to put that mongos.

How to change all components to use external IP address? It is possible to do "under the hood", but to you it would be easier to export all data to HD, rebuild your cluster with right configuration and then import data back to cluster.


How can I make the audit system create new audit files if they already exist?

There doesn't seem to be any reference to being able to directly manipulate the audit file names in 10g.  The closest thing I found is Bug 6023472 which lists the following changes introduced in 10.2.0.5/11.1.07/11.2.0.1:

OS/XML Audit filenames will have a filename format of the form:
  <InstanceName>_<ProcessName>_<ProcessID>_<SerialNo>.<Extension>
The default value for Max Audit FileSize is 1000KB and Max Audit File Age is 1 Day. This means that once one of these max limits are
reached, the audit records will be written in a new audit file with same
"<InstanceName>_<ProcessName>_<ProcessID>_" part, but with a new "<SerialNo>".

I'm not sure how Oracle assigns PIDs, but you could try increasing your PROCESSES parameter to see if that keeps it from re-using the values as quickly.


I am not a database designer.

You don't make dbs but tables.

Both of your table structures can be implemented as innodb tables and as MyIsam tables.

Your first idea is not a good design. It violates first normal form,
so i would propose the second idea with innodb.
the table db1 can be decomposed further in two tables

id | name      | details      | add_date   | brand
1  | some name | some details | 1333128354 | 1
2  | some name | some details | 1333128355 | 1
3  | some name | some details | 1333128357 | 2

id | brand
1  | nike
2  | puma


How can I place "WHERE CLAUSE " to this query in order to filter a results

Between FROM and GROUP BY.

SELECT
stid,
MAX( CASE WHEN courseid = 'MATH' THEN marks END) MATH ,
MAX( CASE WHEN courseid = 'HIST' THEN marks END) HIST,
MAX( CASE WHEN courseid = 'SCI' THEN marks END) SCI,
MAX( CASE WHEN courseid = 'GEO' THEN marks END) GEO,
MAX( CASE WHEN courseid = 'ENG' THEN marks END) ENG,
MAX( CASE WHEN courseid = 'CIV' THEN marks END) CIV,
MAX( CASE WHEN courseid = 'VS' THEN marks END) VS,
MAX( CASE WHEN courseid = 'KISW' THEN marks END) KISW
FROM finalresults
WHERE field=value
GROUP BY stid
ORDER BY 1


As the old saying goes: Technically correct is the best kind of correct.  So following the technical tutorials is something you should do.  However, I remember when normalization was first taught to me it didn't click in right away.  Let's consider the basic steps in plain, but less accurate, English:

First Normal Form (1NF) says to remove columns containing repeating groups to its own table.  Your relation doesn't have any repeating groups in any of the columns, so your relation is already in 1NF.

Second Normal Form (2NF) says that there shouldn't be any non-key attributes which depend on a subset of a multi-part candidate key.  You have one multi-part key.  That appears to be the details of the appointment, which seem to depend on the patient, the doctor and the date.  That means that you need to move the appointment details out to a new relation (along with foreign keys that make a candidate key for the Appointment Details relation.  When talking about 2NF, sometimes you'll hear the term "partial functional dependency".  You want to avoid those.

Third Normal Form (3NF) says that every attribute in a relation depends only on every candidate key (and nothing else).  This is also know as a "transitive functional dependency" - also to be avoided.

If you look at your two relations that you have after achieving 2NF, you have these candidate keys and the following fields which rely on those keys:

Patient ID determines: Patient Name, Sex and DOB.
Doctor determines itself (there are no non-key attributes of doctor)
Patient ID + Appointment Date + Doctor + Details determine the details of an appointment.

NOTE: One other obvious thing that stands out from your sample data is that appointment details is probably actually a compound attribute.  It looks like the details are a number and a textual description, jammed together with " - " between them.  You could make a case, therefore, that the textual description is determined by the leading number.  If that is true of the actual data, then you'd actually want to split out a two-column relation: (e.g.: Treatment ID, Treatment Description) as a separate relation.  This would be part of getting to 3NF as well.

If you did split out a treatment relation, then the appointment relation would only have the Treatment ID, not the description in it.

I hope this helps to understand the steps and how to apply them to your example.  Stick with the technical tutorials.  You'll figure them out soon enough and then you'll start to get an instinctual feel for defining relations in 3NF right from the start.  Once you get comfortable with it, you won't usually need to go through a formal normalization exercise.  It will just come naturally.


COALESCE returning NULL when there are no records

Scalar subqueries that return no rows have a return "value" of NULL ... so all you need to do is coerce that NULL to a 0 directly outside the subquery with IFNULL():

SELECT
    (
        (
            SELECT
                SUM(s.quota)
            FROM
                `inv_zfs_share` s
            JOIN `inv_zfs_project` p2 ON s.project_id = p2.id
            WHERE
                p2.device_id = p.device_id
            GROUP BY
                p2.pool_id
        ) +
         IFNULL( -- add this, here
           (
            SELECT
                SUM(quota)
            FROM
                inv_zfs_replication
            WHERE
                device_id = p.device_id
            GROUP BY
                pool_id
        )
      ,0) -- and add this, here
    ) AS 'Quota Total'
FROM
    inv_zfs_pool p
WHERE
    p.device_id = 626;


Can syslog be configured for Oracle Database Cloud Service?

You can see Syslog using root user. In Oracle cloud machine you cannot login as root directly. But you can login as OPC user and after that switch yourself as root using the sudo command. SYSLOG is located in /var/log/messages. If you installed Oracle management cloud then you can get GUI called "Oracle Log Analytics interface". You can check from this interface.


The data is automatically distributed in the cluster based on the value of the partition key of your tables. So you need to take care about creating correct data model - don't have the partitions that have hundred thousands of rows, etc.

If the data model is correct, then you can scale Cassandra by just adding the new nodes, and then data will be redistributed between nodes.


MySQL Index creation failing on table is full

Please don't fooled by the error message The table 'my_table' is full. This rare scenario has absolutely nothing to do with diskspace. This table full condition has to do with the internal plumbing of InnoDB.

First, take a look at this diagram of the InnoDB Architecture

Please note that the system tablespace (the file ibdata) only has 128 Rollback Segments and 1023 Rollback Slots Per Rollback Segment. This places limits on the size of a transaction's rollback capacity. In other words, if a single rollback segment needs more than 1023 slots to support a transaction, the transaction will hit that table is full condition.

Think of the restaurant Red Lobster in New Jersey. It may have a capacity of 200 people. If the restaurant is full, a line of people may go outside to wait. If the people on the line get impatient, they may leave because the restaurant is full. Obviously, the solution would not be to make New Jersey bigger (or getting more diskspace). The solution would be to make the Red Lobster restaurant bigger. That way you can increase the seating capacity to, let's say, 240. Even with that, a line may form outside if more than 240 people decide to come to Red Lobster.

Just to give you an example, I had a client with 2TB of system tablespace and innodb_file_per_table was disabled. (346G for ibdata1, the reset for ibdata2). I ran this query

SELECT SUM(data_length+index+length) InnoDBDataIndexSpace
FROM information_schema.tables WHERE engine='InnoDB';

Next, I substracted InnoDBDataIndexSpace from the sum of the filesizes for ibdata1 and ibdata2. I got two things that shocked me

There was 106GB remaining inside the system tablespace.
I got this same Table is Full condition

This means that the 106GB was using for InnoDB's internal plumbing. The client was using ext3 at the time.

The solution for me was to add ibdata3. I discussed this in my old post How to solve "The table ... is full" with "innodb_file_per_table"?

I have discussed this in other posts as well

Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full
Nov 25, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

Keep in mind that this condition can happen even if innodb_file_per_table was enabled. How? Rollbacks and Undo Logs are the source of uncontrolled spikes is growth for ibdata1.

YOUR ACTUAL QUESTION

Since your are adding an index to a table and getting Table is Full, the table must be huge and cannot fit inside a single rollback segment. You must do the following:

STEP 01

Get the data into a dump file

mysqldump --no-create-info mydb mytable > table_data.sql

STEP 02

Login to MySQL and run this

USE mydb
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new ADD INDEX ... ;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;

STEP 03

Load the table with the additional index with the data

mysql -Dmydb < table_data.sql

That's all.

See, the problem is that the ALTER TABLE will try to inject all the rows in your huge table as a single transaction. Using the mysqldump will insert the data into table (now with a new index) thousands of rows at a time, not all the rows in a single transaction.

Don't worry about what if this doesn't work? The original table will be named mytable_old in case of anything. it can serve as a backup. You can drop the backup when you know the new table works for you.

Give it a Try !!!

UPDATE 2014-06-16 11:13 EDT

If you are worried about the dump of the data bigger huge, just gzip it.

You can do the same steps, but as follows

STEP 01

Get the data into a dump file

mysqldump --no-create-info mydb mytable | gzip > table_data.sql.gz

STEP 02

Login to MySQL and run this

USE mydb
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new ADD INDEX ... ;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;

STEP 03

Load the table with the additional index with the data

gzip -d < table_data.sql.gz | mysql -Dmydb

or

gunzip < table_data.sql.gz | mysql -Dmydb

UPDATE 2014-06-16 12:55 EDT

I just thought of another aspect with regard to this issue.

Since you are doing DDL and not DML, it is possible that this is not InnoDB internal plumbing. Since DDL cannot rollback for InnoDB, the issue has to be external plumbing. Where is this external plumbing getting clogged? I suspect the temp folder for the OS. Why?

140616 13:04:33  InnoDB: Error: Write to file (merge) failed at offset 3 1940914176.
InnoDB: 1048576 bytes should have been written, only 970752 were written.
InnoDB: Operating system error number 0.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 0 means 'Success'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
140616 13:04:33 [ERROR] /usr/libexec/mysqld: The table 'my_table' is full

See the disk quota exceeded ? Where is this disk quota being imposed ?

Run this query

SHOW GLOBAL VARIABLES LIKE 'tmpdir';

You said it's /var/lib/mysqltmp

Now, run this in the OS

df -h /var/lib/mysqltmp

Something tells me that the space for /var/lib/mysqltmp was running out After all, you only have 14G free. In the eyes of the DDL (to create the index), a rollback happened, not in the ibdata1 file, but in the tmpdir location. If /var/lib/mysqltmp is not mounted anywhere, then the temp data is being written in the root partition. If /var/lib/mysqltmp is mounted somewhere, then that mount is being filled with row data. In either case, there is not enough room to complete the DDL.

You have two options here

OPTION #1

You could also create a large disk (perhaps with 100+GB) and mount /var/lib/mysqltmp on that large disk.

OPTION #2

My 3-step suggestions should still work, even with the limited diskspace you have

COMMENTARY

It's a shame the error message you posted says

InnoDB: Operating system error number 0.
InnoDB: Error number 0 means 'Success'.

This means the OS is just fine. It also there doesn't exist any associated error number for this situation.

There is another thing you should know. The MySQL Documentation says that Fast Index Creation for InnoDB still goes to disk:

  During index creation, files are written to the temporary directory
  ($TMPDIR on Unix, %TEMP% on Windows, or the value of --tmpdir
  configuration variable). Each temporary file is large enough to hold
  one column that makes up the new index, and each one is removed as
  soon as it is merged into the final index.

  Due to a limitation of MySQL, the table is copied, rather than using
  “Fast Index Creation” when you create an index on a TEMPORARY TABLE.
  This has been reported as MySQL Bug #39833.

UPDATE 2014-06-16 13:58 EDT

[mysqld]
datadir                         = /mnt/cbsvolume1/var/lib/mysql
tmpdir                          = /mnt/cbsvolume1/var/lib/mysql

Please make sure /mnt/cbsvolume1/var/lib/mysql has 100G or more free


sql server management studio 2008 sucks up with memory yielding .net runtime failure

Looks like up until SQL Server 2000 there was an undocumented command DBCC CHECKDBTS that did exactly what you want but it is no more. It looks like it is stored on the database boot page with no (safe, see below) way of manually setting it.

I'm not sure why you want to do this and I suggest that you don't bother but if you must then you will likely find set based inserts considerably quicker.

(NB: The below might need tweaking to avoid overshooting the target as I found that @@DBTS incremented by 1003921 for each million row insert but should be an improvement...)

CREATE TABLE __DUMMY (I TINYINT, TS TIMESTAMP)

DECLARE @Target INT, @Batch INT
SET @Target = 666666666

WHILE @Target >  CONVERT(INT, @@DBTS)
BEGIN
RAISERROR ('@@DBTS = %d',0,1,@@DBTS) WITH NOWAIT;

SET @Batch = @Target - CONVERT(INT, @@DBTS);

IF @Batch > 1000000
    SET @Batch = 1000000;

   WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
 INSERT INTO __DUMMY (I)
  SELECT 1
   FROM cteTally
  WHERE N <= @Batch;

TRUNCATE TABLE __DUMMY
END

DROP TABLE __DUMMY

Or alternatively if this is only a development database, you have backups and you aren't going to come complaining to me if it all goes wrong you could do some experimenting with the undocumented and highly warned against DBCC WRITEPAGE command.

USE tempdb;

IF DB_ID('TestDBTS') IS NOT NULL
    DROP DATABASE TestDBTS;

CREATE DATABASE TestDBTS;

USE TestDBTS;

DBCC WRITEPAGE(TestDBTS, 1, 9, 412, 8, 0xAA86BC2700000000);

ALTER DATABASE TestDBTS SET OFFLINE WITH ROLLBACK IMMEDIATE;

ALTER DATABASE TestDBTS SET ONLINE WITH ROLLBACK IMMEDIATE;

USE TestDBTS;
SELECT @@DBTS;

DBCC TRACEON (3604);
DBCC DBINFO ('TestDBTS');
DBCC TRACEOFF (3604);


Restricting Insert and Update to functions

Yes this is possible.

You (obviously) need two database users for this:

one who is allowed to call the function
one who ones the table and is allowed to directly modify it.

Then revoke the update, insert and delete privilege from the restricted user.

Then create the function with the privileged user as the owner and use the attribute SECURITY DEFINER when creating it.

Thus the restricted user cannot change the table, but when he calls the function, the function runs with the privileges of the other user and thus can modify the table.

Something like this:

Logged in as the privileged user:

create user restricted_user password 'hidden';
create table foo (id serial not null primary key, some_info text);
grant select on foo to restricted_user;
create or replace function public.insert_foo(p_info text)
  RETURNS void
  security definer  -- << this is important
  LANGUAGE sql
AS
$body$
   insert into public.foo (some_info) values (p_info);
$body$;

grant execute on function public.insert_foo(text) to restricted_user;

Now you log in as the restricted_user:

insert into foo (some_info) values ('foo');
==> ERROR: permission denied for relation foo

select insert_foo('foo');
==> one row inserted


Distributing and managing Postgres SCHEMAs with an EXTENSION?

It may make sense to distribute to third party PG users as EXTENSIONS, but it doesn't make sense to bundle their data as EXTENSIONS.

it's more work,
it won't be backed up by pg_dump

Because it won't be backed up by pg_dump, you'll have to add to your development repository their data rather than just the initial load scripts. For the added complexity of backing things up, it doesn't seem advisable to do this.


DBCC CHECKDB - SQL Log Says Completed, SQL Server Agent Says Failed

There is no mystery, the 2 messagges for CellTestData_QC reference 2 different databases CellTestData_QC.

The first one is your database CellTestData_QC on which DBCC CHECKDB did not complete because you began a restore.

The second message refers to the restored database CellTestData. It's not DBCC CHECKDB that was running on 25/01, it's just an info that is stored within CellTestData, and it reports the last date when DBCC CHECKDB completed without errors on this database.

Every time the database is opened (goes online), the last known good dbcc checkdb date is reported in eroor log. So the second message tells you that DBCC CHECKDB completed successfully on your database CellTestData on 2019-01-24 23:01:46.507. This message appears in error log as soon as restore is completed.

You can see this on every server restart. SQL Server opens every database that should go online and within every database it reads the date called last known good time and reports it in errorlog. This does not mean that checkdb runs on every restart, in fact, the date reported is always earlier than the current date.

For example, if you run checkdb every Sunday, and the restart occurs on Saturday, you'll see these messages reporting previous Sunday date in errorlog as it was the last date when dbcc checkdb completed successfully.

Here CHECKDB From Every Angle: When did DBCC CHECKDB last run successfully? you can read more on last-known good time.


Get availability group and listener of the current DB (dynamic SQL)

You need to run queries agains the DMV to get the AG name.

Here's some code you could use as a base:

select DB_NAME(AG_DB.database_id) "DB", AG.name  "AG Name", li.dns_name "Listener"
from sys.dm_hadr_database_replica_states AG_DB
join sys.availability_groups AG on AG.group_id = AG_DB.group_id
join sys.availability_group_listeners li on li.group_id = AG.group_id

This will only returns databases that are in AG (vs a query in sys.databases which will return the system DB as well).

P.s. If you do not need the listener (which seems to be the case) then you do not need the 3rd table and it will keep the query pretty simple.


Creating a Stored Procedure that will input data for each day between the date entered as input to or from the 1st of that month

I reduced your statement down to the simplest possible piece of code, and found the exact error.  I removed everything except the loop, and got to this:

CREATE PROCEDURE dbo.test
(
    @ProductionDate As DateTime
    , @Debug BIT = 1
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @BegMonth As DateTime

    SET @BegMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

    WHILE @productionDate >= @BegMonth
        SELECT @endDate = DATEADD(DAY, 1, @BegMonth)
END

The WHILE statement does not have BEGIN directly afterwards.  This means the WHILE loop is essentially this:

WHILE @productionDate >= @BegMonth SELECT @endDate = DATEADD(DAY, 1, @BegMonth)

and nothing else.  Just, loop forever.

To fix it, make your code like this:

USE [OperationsView]
GO
/****** Object:  StoredProcedure [Interface].[proc_GetChemicalDaily] Script    Date: 1/30/2017 4:03:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Daily Chemical>
-- =============================================
ALTER PROCEDURE [Interface].[proc_GetChemicalDaily]
-- Add the parameters for the stored procedure here

@ProductionDate As DateTime,
@Debug BIT = 1

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- pi sproc variables Declaration
DECLARE @result As Float
DECLARE @tag_in As varchar(80)
DECLARE @pi_server As varchar(50)
DECLARE @enddate As DateTime
DECLARE @FCChemGALPERMIN float
DECLARE @CAUSTICDAILYUSAGE As Decimal(16, 8)
DECLARE @constant As Decimal(16, 8)
DECLARE @BegMonth As DateTime

--Actual variables for insertion
DECLARE @Group As VARCHAR(200)
DECLARE @Item As VARCHAR(200)
DECLARE @Total As DECIMAL(20, 4)
DECLARE @Days As Int
DECLARE @Type As VARCHAR(1)

--Pi Sproc variable setting
   SELECT @tag_in = 'I-FC-835'
   SELECT @pi_server = 'valpi'
   SET @constant = (18318.0/2000.0)*.5
   SET @BegMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

   --Setting Actual insertion variables
   SET @Group = 'CHEMICAL'
   SET @Item = 'CAUSTIC CHEM'
   SET @Days = 1
   SET @Type ='a'
   --create temp table to grab decimal value
   create table #temp
   (
    float_col   float,
    percent_col float
   )
--Grabbing gal/min value from PI
While @productionDate >= @BegMonth
BEGIN
     SELECT @endDate = DATEADD(DAY, 1, @BegMonth)
     INSERT into #temp(float_col, percent_col)
     EXEC Interface.proc_GetPIValueAverageTime @result, @tag_in, @BegMonth, @endDate, @pi_server, @Debug
     SELECT @FCChemGALPERMIN = float_col FROM #temp

  -- daily dry ton usage calculation
  IF @FCChemGALPERMIN > .5
 SET @CAUSTICDAILYUSAGE = cast(@FCChemGALPERMIN*@constant as numeric(38,10))
  ELSE
 SET @CAUSTICDAILYUSAGE = 0.0000

--Inserting in Actuals table
IF @Debug = 1

begin
    INSERT INTO [OperationsView].[dbo].[Actuals]
            ([Group]
            ,[Item]
            ,[ProductionDate]
            ,[Days]
            ,[Total]
            ,[Type])
        VALUES
            (@Group
            ,@Item
            ,@ProductionDate
            ,@Days
            ,@CAUSTICDAILYUSAGE
            ,@Type);
end

DROP TABLE #temp
Set @BegMonth = DATEADD(DAY, 1, @BegMonth)
 END
END


Reset every Oracle sequence to the MAX value of its target table PK?

I made this script as I did not find a script online that dynamically sets all my sequences to the current highest ID. Tested on Oracle 11.2.0.4.

DECLARE
  difference         INTEGER;
  sqlstmt            VARCHAR2(255) ;
  sqlstmt2           VARCHAR2(255) ;
  sqlstmt3           VARCHAR2(255) ;
  sequenceValue      NUMBER;
  sequencename       VARCHAR2(30) ;
  sequencelastnumber INTEGER;
  CURSOR allseq
  IS
     SELECT sequence_name, last_number FROM user_sequences ORDER BY sequence_name;
BEGIN
  DBMS_OUTPUT.enable(32000) ;
  OPEN allseq;
  LOOP
    FETCH allseq INTO sequencename, sequencelastnumber;
    EXIT
  WHEN allseq%NOTFOUND;
    sqlstmt  := 'ALTER SEQUENCE ' || sequencename || ' INCREMENT BY ';
    --Assuming: <tablename>_id is <sequencename>
    sqlstmt2 := 'select (nvl(Max(ID),0) - :1)+1 from ' || SUBSTR(sequencename, 1, LENGTH(sequencename) - 3) ;
    --DBMS_OUTPUT.PUT_LINE(sqlstmt2);
    --Attention: makes use of user_sequences.last_number --> possible cache problems!
    EXECUTE IMMEDIATE sqlstmt2 INTO difference USING sequencelastnumber;
    IF difference > 0 THEN
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt || difference) ;
      EXECUTE IMMEDIATE sqlstmt || difference;
      sqlstmt3 := 'SELECT ' || sequencename ||'.NEXTVAL from dual';
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt3 || ' INTO sequenceValue') ;
      EXECUTE IMMEDIATE sqlstmt3 INTO sequenceValue;
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt || 1) ;
      EXECUTE IMMEDIATE sqlstmt || 1;
      DBMS_OUTPUT.PUT_LINE('') ;
    END IF;
  END LOOP;
  CLOSE allseq;
END;


SQL server Installation - SQL server agent is enabled by default?

This is an installation option. In the installer GUI or command line you can leave it as enabled or disabled and automatic or manual. By default in most versions of SQL it is not enabled and started. I always change it when installing - or forget and do it shortly thereafter.


View the role assigned to each user (Report Server)

Found My Answer from a Blog Almost similar :) !!!

DECLARE @UserName VARCHAR(200) = 'UserName'

SELECT Rol.RoleName,Us.UserName ,Cat.PATH,Cat.[Name] ReportName,
CASE Cat.Type WHEN 1 THEN 'Folder' WHEN 2 THEN 'Report'
WHEN 3 THEN 'Resource' WHEN 4 THEN 'Linked Report'
WHEN 3 THEN 'Data Source' ELSE '' END AS CatalogType
,Cat.Description
FROM Catalog Cat     --Parent
INNER JOIN Policies Pol
ON Cat.PolicyID = Pol.PolicyID
INNER JOIN PolicyUserRole PUR
ON Pol.PolicyID = PUR.PolicyID
INNER JOIN Users Us
ON PUR.UserID = Us.UserID
INNER JOIN Roles Rol
ON PUR.RoleID = Rol.RoleID
WHERE   Cat.Type in (1,2)
AND  ( Us.UserName LIKE @UserName  )
ORDER BY Cat.PATH


As in all things SQL Server, the answer is "it depends".  A good database developer can design an application to mitigate SQL Server hardware requirements with attention to detail on database design, index/query tuning, and application design (e.g. appropriate data caching).  So it seems you may have covered that part if you see index seeks rather than scans in the execution plans with the actual number of rows being the minimum needed.

Whether or not your hardware is up for the task will depends much on response time service level agreements (SLAs).  Doing the math at 30 users and twice a minute, the server will need to be able to execute 120 selects, 120 updates, and 60 updates a minute.  This comes to 5 queries per second, which my gut indicates can achieve sub-second response time on the PC class hardware you mentioned, especially if the entire database will fit in RAM (which is probably can based on your description) and you are accessing a small number of rows per query.


PostgreSQL: Correct way for subset calculations

You can easily do this with arrays in Postgres:

select user_id, array_agg(activity_id) as activities
from users
group by user_id
having array_agg(activity_id) @> array[1,2]
   and not 3 = any(array_agg(activity_id));

The condition array_agg(activity_id) @> array[1,2] only returns those that have activity_ids 1 and 2  and the condition not 3 = any(array_agg(activity_id)) removes all those that contain activity_id = 3

If the table contains more than just those two columns, an index on (user_id, activitiy_id) will help as it enables Postgres to use an "Index Only Scan" instead of a full table scan. If there are only very users that have activity_ids 1 and two, an additional condition that only returns rows with either one of them (e.g. using a where exists condition) might help as it reduces the number of rows that need to be aggregated. In that case the index should be on (activity_id, user_id) to enable Postgres to remove unwanted rows efficiently.

On a table with 100.000 rows this ran in about 100ms on my laptop with Postgres 11 and a SSD.

Online example: https://rextester.com/YLN7221


How to find out why this query is in waiting state?

What makes you think your session is waiting?

When a statement uses the CPU, it is not waiting. There is no wait event that accounts for using the CPU for processing. People often forget this. Your session is most likely using the CPU. There is no indication in your output that your session is waiting.

V$SESSION

  SECONDS_IN_WAIT

  If the session is currently waiting, then the value is the amount of
  time waited for the current wait. If the session is not in a wait,
  then the value is the amount of time since the start of the last wait.

You should query WAIT_TIME as well:

  WAIT_TIME

  If the session is currently waiting, then the value is 0. ...

Another method to confirm that your session is ON CPU would be querying ASH.

V$ACTIVE_SESSION_HISTORY

In ASH, the SESSION_STATE column has 2 different possible values:

  SESSION_STATE

  Session state:

  WAITING

  ON CPU

Another method would be to query V$SESSTAT (joined with V$STATNAME) an check whether the statistic CPU time increases.

You can run any CPU-bound query to test this, for example the below query uses nothing else but CPU. Start it, and you will we see SECONDS_IN_WAIT increasing, while your session is not waiting, but using CPU.

with g as (select * from dual connect by level <= 1000)
select count(*) from g,g,g,g;


How to capture execution plan from sql profiler for filtered queries only

The easiest way would be to stop using Profiler and go to Extended Events. The query_post_execution_showplan event has duration built right in and you can use that to filter capture quite neatly. Here's a simple example:
CREATE EVENT SESSION ExecPlansDuration
ON SERVER
    ADD EVENT sqlserver.query_post_execution_showplan
    (WHERE ([duration] > (10000)))
    ADD TARGET package0.event_file
    (SET filename = N'ExecPlansDuration');

Just know that capturing plans can be very expensive.


Database not naturally shrinking log file

You must check log_reuse_wait during the import process. Most likely you have a some long running transaction started at the beginning of the import that is pinning the log for the entire import duration. Simple Recovery model log is automatically truncated, not shrunk. See How to Shrink the SQL Server log.