Translating backup size to database size

You could have asked mysql for all individual table sizes

SELECT table_schema,table_name,data_length,index_length
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql');

Summary by Storage Engine

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 3 pw) A
ORDER BY TSize;

Summary by Database

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size"
FROM (SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,
SUM(XSize) SXSize,SUM(TSize) STSize FROM (SELECT table_schema DB,
data_length DSize,index_length XSize,data_length+index_length TSize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

Summary by Database/Storage Engine

SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score, IF(ISNULL(table_schema)=1,
'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,
"Storage for All Databases",IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,
CONCAT("Storage for ",B.table_schema),CONCAT(B.engine," Tables for ",
B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') DataSize,
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') TableSize FROM (SELECT table_schema,engine,
SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND engine IS NOT NULL
GROUP BY table_schema,engine WITH ROLLUP) B,(SELECT 3 pw) A) AA
ORDER BY schemaname,schema_score,engine_score;

Please send these queries to your IT people and have them email the results back

Please keep in mind that mysqldumps do not contain indexes. A mysqldump is simply a logical representation of the data plus the commands and directive to crate the table, load the table, and make indexes. What get generated physically upon restore can be known by these queries before launching the mysqldump.


SQL Server Database Mail - Why can't I run an execute against xp_sysmail_activate?

You aren't the one getting the error, the activated stored procedure is.  That procedure is running under a different account than yours.

By default that procedure will run as what ever account is setup as the dbo of the msdb database, unless someone changed it of course.  Best guess is that the account which is dbo of the msdb database isn't a member of the sysadmin fixed server role, or the activation principal of that queue has been changed (querying sys.service_queues will tell you this).


-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in *ALL* tables (not just refTables)
EXEC sp_MSForEachTable "DELETE FROM ?"
-- reseed the indexes so primary keys start over at 1
declare @cmd varchar(4000)
declare cmds cursor for
select 'DBCC CHECKIDENT(''' + t.name + ''', RESEED, 0);'
FROM sys.tables AS t
INNER JOIN sys.identity_columns AS i
ON t.object_id = i.object_id
WHERE last_value IS NOT NULL

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

adding WHERE last_value IS NOT NULL as suggested by the second answer in the question Aaron Bertrand linked too solved this issue.


PostgreSQL exit status when running a file

Adding this to the first line of the SQL file does the trick:

\set ON_ERROR_STOP 1

New comments (Thanks!) suggest the following solutions:

-v ON_ERROR_STOP=1
-v ON_ERROR_STOP=on


Conjecture #1 : Single Transaction

The dump for the sys_data database has to create far more MVCC information to load and dump from ibdata1. Please Click Here to See the InnoDB Infrastructure Map and notice the section in ibdata1 that has 1023 undo logs.

When you were dumping an entire database, mysqldump will export the tables in alphabetical order. There can times that undo logs are populated with row data in the event of a crash and recovery as needed. Perhaps those logs are being loaded and unloaded from previous dumps. The undo space populated by the dump of previous tables (tables before tb_trade_376) may need some housecleaning performed while the dump of tb_trade_376 is in progress. This would be particularly true for a very busy server where INSERTs, UPDATEs, and DELETEs are being done to the previous tables.

Conjecture #2 : InnoDB Buffer Pool

If you are not writing anything to the database at all, another place to look would be the InnoDB Buffer Pool. Think about it: the data pages of every table being dumped would have to be loaded in the Buffer Pool simply because you are doing a SELECT. The SQL_NO_CACHE prevents the query results from entering the query cache, but does not prevent data movement in and out of the Buffer Pool. The data pages accessed from the previous tables have to be invalidated and overwritten in the Buffer Pool for each and every table prior to tb_trade_376.

Conjecture #3 : innodb_file_per_table

If innodb_file_per_table is disabled, then everything and its grandmother is in ibdata1. What would it contain? ibdata1 would have the following:

Data Pages for Every InnoDB Table
Index Pages for Every InnoDB Table
Data Dictionary
Double Write Buffer (support data consistency; used for Crash Recovery)
Insert Buffer (Buffers Changes to Secondary Non-Unique Indexes)
Rollback Segments
Undo Space (where the most uncontrolled growth can happen)

This means that the Undo Logs are competing for space with the data and index pages of every table. This would cause ibdata1 to grow rapidly. If you have innodb_file_per_table disabled, you need to cleanup the InnoDB Infrastructure to keep InnoDB tables out of ibdata1.

Conjecture #4 : MyISAM Tables

How can MyISAM tables cause a problem? If any of the tables in the mysqldump are MyISAM and still receiving INSERTs, UPDATEs, and DELETEs during the database dump, it could possibly disable the checkpoint mechanism of the --single-transaction option midstream. This is plausible because MyISAM is a non-transactional storage engine. If any of the previous tables are MyISAM and are still receiving writes, all bets are off for all the tables in the dump to have the same point-in-time. Each table being dumped after a MyISAM table in encountered is basically on it own in a transactional sense.

SUMMARY

You may have one or more of these issues going on. Please compensate with one of the following:

Arrange to dump of each table into separate files
Setup MySQL Replication

You have freedom to tune MySQL on the Slave
You can stop the Slave and dump from the Slave. This will make point-in-time simple since a stopped slave means no more writes. YOu can start the slave once all dumps are done.

Deal with MyISAM

Convert all MyISAM tables to InnoDB. Prior to MySQL 5.6, a MyISAM table cannot be converted to InnoDB if there are FULLTEXT indexes.
If you cannot convert a MyISAM table, please stop writing to it during the dump

Do the ibdata1 cleanup
Use a larger buffer pool

Give it a Try !!!


Disable all associations to these unwanted tables.
(Alternatively, but not recommended: remove the unwanted tables from the data model)


Function Based Index with XPATH extractValue

Use the /*+ NO_XML_QUERY_REWRITE */ hint. My environment for the below is 11.2.0.3.0 on Linux_x86-64 platform, Enterprise Edition.

create table xml_table (c1 xmltype) pctfree 99;

(PCTFREE 99, just a simple trick to spread the table over many blocks, thus making full table scan even more costly and inefficient. 1 row/block in this case.)

insert into xml_table select
xmltype('
  <stuff>
    <something>
      <x1>' || rownum || '</x1>
      <x2>' || rownum || '</x2>
    </something>
  </stuff>
')
from dual connect by level <= 10000;
commit;

So I have a table with 10000 rows, with XMLType values like:

<stuff>
   <something>
      <x1>1</x1>
      <x2>1</x2>
   </something>
</stuff>

<stuff>
   <something>
      <x1>2</x1>
      <x2>2</x2>
   </something>
</stuff>

...

And I want to run queries like, let's say:

select * from xml_table
where extractvalue(c1, '/stuff/something[x1=5]/x2/text()') = '5';

Create the function-based index and gather statistics:

create index xml_table_fbi1 on
  xml_table(extractvalue(c1, '/stuff/something[x1=5]/x2/text()'));

exec dbms_stats.gather_table_stats(user, 'XML_TABLE', no_invalidate=>false);

Run the query and see what happened:

alter session set statistics_level=all;

select * from xml_table where extractvalue(c1, '/stuff/something[x1=5]/x2/text()') = '5';

C1
--------------------
<stuff>
  <something>
    <x1>5</x1>
    <x2>5</x2>
  </something>
</stuff>

Check the plan and runtime statistics:

select * from table(dbms_xplan.display_cursor(format=>'allstats last cost'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  a78z6npnp94va, child number 0
-------------------------------------
select * from xml_table where extractvalue(c1,
'/stuff/something[x1=5]/x2/text()') = '5'

Plan hash value: 3307077916

---------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |      1 |        |   226K(100)|      1 |00:00:00.43 |   10239 |
|*  1 |  FILTER              |           |      1 |        |            |      1 |00:00:00.43 |   10239 |
|   2 |   TABLE ACCESS FULL  | XML_TABLE |      1 |  10000 |  2764   (2)|  10000 |00:00:00.04 |   10239 |
|   3 |   SORT AGGREGATE     |           |  10000 |      1 |            |  10000 |00:00:00.37 |       0 |
|   4 |    NESTED LOOPS SEMI |           |  10000 |    667K|   223K  (1)|      1 |00:00:00.28 |       0 |
|   5 |     XPATH EVALUATION |           |  10000 |        |            |  10000 |00:00:00.12 |       0 |
|   6 |     XPATH EVALUATION |           |  10000 |        |            |      1 |00:00:00.14 |       0 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SYS_XMLTYPE2SQL(SYS_XQSEQ2CON(SYS_XQEXTRACT(,'/something/x2/text()')))
              ='5')

The database transformed the XPath expression, so the function-based index is not a valid candidate anymore, so it is  a full table scan with over 10000 buffer gets.

While running the same with the above hint:

select /*+ NO_XML_QUERY_REWRITE */ * from xml_table where
extractvalue(c1, '/stuff/something[x1=5]/x2/text()') = '5';

C1
--------------------
<stuff>
  <something>
    <x1>5</x1>
    <x2>5</x2>
  </something>
</stuff>

Plan and runtime statistics:

select * from table(dbms_xplan.display_cursor(format=>'allstats last cost'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID  gby3tfs2z7mqm, child number 0
-------------------------------------
select /*+ NO_XML_QUERY_REWRITE */ * from xml_table where
extractvalue(c1, '/stuff/something[x1=5]/x2/text()') = '5'

Plan hash value: 2259369741

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |      1 |        |     2 (100)|      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| XML_TABLE      |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN          | XML_TABLE_FBI1 |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("XML_TABLE"."SYS_NC00003$"='5')

Much better.

Also notice the Cost column in the plans. The cost of the first plan was 226K, but with disabling XML rewrite, the cost of the second plan was 2. I didn't investigate this topic deeper, but it seems this is another kind of query transformation, that is not based on the cost.


How to prevent sysadmin server role from executing a stored procedure

Members of the sysadmin role cannot be denied permission to anything in SQL Server.  The documentation for DENY (Transact-SQL) states:

  ...except that DENY does not apply to object owners or members of the sysadmin fixed server role.

So in order to prevent a sysadmin from running a stored procedure, you would need to put code in the stored procedure to check if the user is a sysadmin and return an error if they are.  However, a sysadmin could easily get around this by:

modifying the stored procedure
creating a new stored procedure with the same code and ripping out the part that checks for sysadmin
running the same code, minus the part that checks for sysadmin, from a SQL client
using EXECUTE AS to run the procedure as a different user

If your goal is to prevent sysadmin users from seeing sensitive data, the way to do that is to use Always Encrypted.  This encrypts and decrypts the data with a key that only the application has access to.  If a sysadmin reads the table, they will just see encrypted data.


SQL Servers highest priority is to comply with the ACID properties. That means, no matter what you do, your database is always going to be transactionally consistent.

What you are trying to do however could get you into an inconsistent state. Think about the standard example to explain the ACID properties: The bank account. Say you have two accounts that reside on different filegroups (e.g. two account tables or one with partitioning). Now you transfer money from filegroup one to filegroup two within a transaction. Then you go and restore filegroup one to before the transfer. That results in the money being in both accounts now, clearly not a desired state.

So, what is happening in your example?

6) the filegroup is successfully restored to a point in time before the disaster. However it is in the "restoring" state and not accessible.

8) the log restore is bringing the filegroup up to speed with the rest of the database (on a transactional level). To do that, all committed transaction that are captured in the log backup are reapplied to that filegroup. However, the delete was a committed transaction, so it is re-executed too. This leaves the database consistent, but the table empty.

The Technet example you are pointing to makes the assumption that the file goes offline for some external reason, e.g. a drive failure. In that case the restore as described makes sense as you would want all the transactions to be re-applied.

In your case you need to restore the database somewhere else and then copy the data back into the original table. For that you can just restore the filegroup containing the data in question and the primary filegroup. You cannot just restore a secondary filegroup, the primary has to always be restored too. This is one of the reasons why it is recommended to not have any data in the primary filegroup. That way the impact of having to restore that one too is minimal.


PSQL: create random data sets with the same statistic distrbution as another table

If you only need the distribution of cardinalities to be the same, and not the ordering, or the length of the text strings, then you can use a hashing function.

create new_table as select
    md5('Aiw7Pa6y'||col1) as col1,
    md5('coh9ahMo'||col2) as col2,
    md5('ii5eFohj'||col3::text) as col3
from old_table;

For non-text columns, you would have to decide if they need to be randomized at all, and if so whether conversion to text (keeping the cardinality of each distinct value, but not the order or relative gaps) would be suitable.


Should I use VACUUM on a SQLite db with a log-structured filesystem?

When the order of pages in the file has no relation to the order of pages on the disk, reordering the file does not make sense.


Connection refused when trying to set up replication

After you import the dump file from the master, your new root password on the slave will stop being valid on the slave not later than, but sometimes as late as, the first time you restart it, because the mysql.user table is going to be overwritten... the new root password after restart on the slave is going to be the same as the root password from the master (assuming the master has a SUPER user called "root").

The workaround is either using the master's root password (assuming it has a root@localhost account) or is to keep your connection open to the slave while restoring the dump file, and then after the restore is done, modify the mysql.user table to have the values you want, before disconnecting and restarting.  FLUSH PRIVILEGES; after making the change, then try logging in from an additional connection to verify that it's going to work, then restart the slave.

If you RESET SLAVE; before the restart, you shouldn't need to --skip-slave-start.


Replication error after server name change

sp_dropserver 'real-server-name' was giving an error about remote logins:

  There are still remote logins or linked logins for the server ...

Therefore, I executed the following query:

execute sp_dropremotelogin old_physical_server_name;

...but this gave the following error because of replication:

  There is no remote user '(null)' mapped to local user '(null)' from the remote server ...

Then I executed:

execute sp_removedbreplication 'database-name-on-which-publication-exist';
execute sp_dropdistributiondb 'distribution-database-name';

...still the same error. Finally I tried:

execute sp_dropdistributor @no_checks = 1, @ignore_distributor = 1;
execute sp_dropserver 'real-server-name';
execute sp_addserver 'nwe-server', 'local';

This resolved the issue.


Each node in the plan only projects the columns minimally needed to satisfy the query correctly. You can see this by looking at the Output List of each operator. For example, the final join only lists one column. SQL Server is very good at removing unneeded projections.

I think perhaps you are expecting one or more of the joins to be eliminated. This is more tricky, because the optimizer has to be careful to not change the meaning of the query. There are four reasons to keep a join as Rob Farley describes in JOIN simplification in SQL Server:

Extra columns. The join is needed to provide columns needed by the query, either because the column appears in the final result, or it is needed for some intermediate step (like filtering, or a different join).
Row duplication. A join can increase the number of rows matched. For example a single row in table A might join with two rows in table B, so the result would contain two copies of the information in the table A row.
Row elimination. An inner join can eliminate rows from table A that do not join with any row in table B.
Added NULLs. A right or full join can introduce new NULLs where a row in table A does not match a row in table B.

SQL Server is quite good (though not perfect) at removing unnecessary joins where it is safe to do so. A join can only be removed if the optimizer has a guarantee that none of the four join effects above will affect the result.

In your case, it is likely that item #2 and #3 above applies. You may be able to make the view more simplifiable by using left joins instead of inner joins, and perhaps adding a DISTINCT to your outer query. See Rob's article for examples.


Why is the System tablespace in manual segment space management mode?

Because Oracle says so :). The official documentation explicitly states that:

  Restrictions on Automatic Segment-Space Management This clause is
  subject to the following restrictions:

  You can specify this clause only for a permanent, locally managed tablespace.
  You cannot specify this clause for the SYSTEM tablespace.

I don't know why, there are some hypothesis here.


@Kin is right about getting this info from the default trace, if it is in fact still there (depends on how busy your server is, and how long ago this happened).

To get an e-mail immediately, though, instead of having to remember to check the default trace all the time just in case something happened, you can do this from a DDL trigger. Database Mail config is out of scope for the question, I think, so I'll leave that part to you (there is a Q & A here).

ALTER TRIGGER DroppinDatabasesAwYeah
ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
  DECLARE
    @name    NVARCHAR(255),
    @login   NVARCHAR(255),
    @msg     NVARCHAR(MAX);

  SELECT
    @name  = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(255)'),
    @login = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','NVARCHAR(255)');

  SELECT @msg = N'It appears that '
    + QUOTENAME(COALESCE(@login, N'<unknown>'))
    + N' has dropped the database '
    + QUOTENAME(COALESCE(@name, N'<unknown>')) + N'.';

  EXEC msdb.dbo.sp_send_dbmail
    @profile_name = N'default',
    @recipients = N'you@yourdomain.com',
    @subject = N'Somebody done dropped a database',
    @body = @msg;
END
GO


Automating the service restart from another server in Windows server 2016

Although you could restart the remote application service from T-SQL using a command invoked via xp_cmdshell, I suggest this be done with a PowerShell command instead.
The PowerShell script below calls a proc and restarts a service when the return code is 1, indicating no transactions were processed. This script can be included in a SQL Server Agent PowerShell job step running under an account with permissions needed to execute the proc and restart the remote service.
try {
    $connection = New-Object System.Data.SqlClient.SqlConnection("Integrated Security=SSPI")
    $command = New-Object System.Data.SqlClient.SqlCommand("YourDatabase.dbo.usp_CheckNoTransactions", $connection)
    $command.CommandType = [System.Data.CommandType]::StoredProcedure
    $returnValueParamter = $command.Parameters.Add("@ReturnValue", [System.Data.SqlDbType]::Int)
    $returnValueParamter.Direction = [System.Data.ParameterDirection]::ReturnValue
    $connection.Open()
    [void]$command.ExecuteNonQuery()
    $connection.Close()
    if($returnValueParamter.Value -eq 1) {
        Write-Output "No transactions processed - restarting application service..."
        Get-Service -ComputerName "YourApplicationServer" -Name "YourAppServiceName" | Restart-Service
    }
}
catch {
    throw
}

Sample proc:
CREATE PROC dbo.usp_CheckNoTransactions
AS
IF NOT EXISTS(SELECT 1 FROM dbo.Transactions WHERE CreateTimestamp > DATEADD(minute, -3, GETDATE()))
BEGIN
    --application has stalled - send email and return 1 to indicate a service restart is needed
    EXEC msdb.dbo.sp_send_dbmail ...
    RETURN 1;
END;
GO


IO Error: Got minus one from a read call

"emctl start dbconsole" is a command to start Enterprise Manager (EM), which is tool comes bundled with Oracle Database, and used for monitoring and managing the Oracle Database.

I believe localhost:1158/em  is the URL for Enterprise Manager(EM), so is the port.

The EM and DB port won't be same, in your case the DB port should be 1521 and SID should be ORCL.

So, your connection URL should look like "jdbc:oracle:thin:@localhost:1521:orcl".


Duplicate login details per user in error log

I realize this doesn't answer the question "why does the log file have multiple entries for 'Login succeeded...'", however since you also seem to be concerned about the other log entries being "drowned-out" by these low-importance login messages, I thought I'd add the following as an answer.

Instead of using SQL Server Management Studio's built-in interface for looking at log files, you can use the following code that will allow you to ignore log messages you are not interested in.  This allows you to concentrate on messages that may be more immediately relevant.

IF NOT EXISTS(
    SELECT s.name + '.' + t.name
    FROM tempdb.sys.tables t
        INNER JOIN tempdb.sys.schemas s ON t.schema_id = s.schema_id
    WHERE s.name + '.' + t.name = 'dbo.ErrorLogAnalysis'
    )
BEGIN
    CREATE TABLE tempdb.dbo.ErrorLogAnalysis
    (
        LogDate DATETIME,
        ProcessInfo NVARCHAR(255),
        [Text] NVARCHAR(MAX)
    );
END

TRUNCATE TABLE tempdb.dbo.ErrorLogAnalysis;
EXEC sp_ReadErrorLog 0,1;
INSERT INTO tempdb.dbo.ErrorLogAnalysis
SELECT ela.*
FROM tempdb.dbo.ErrorLogAnalysis ela
WHERE ela.Text NOT LIKE 'Login succeeded%'
ORDER BY LogDate DESC;

It may be helpful to point out that client-side code is quite likely to use more than a single connection to SQL Server.  This may in fact be the precise reason why more than a single entry is recorded in the SQL Server Error Log.

To prove this, I've written a very simple VB.Net command-line app that creates 5 connections to my local SQL Server instance.  Once all five connections have been opened, the app executes a simple query against the server showing 5 session_id values that the app has open.  It then closes all 5 connections, and exits.

This is the code:

Module Module1

    Sub Main()

        Dim sConnString As String =
            "SERVER=localhost;" & _
            "DATABASE=master;" & _
            "TRUSTED_CONNECTION=True;" & _
            "Application Name=MultiConnTest"
        Dim sqlC1 As New SqlClient.SqlConnection(sConnString)
        Dim sqlC2 As New SqlClient.SqlConnection(sConnString)
        Dim sqlC3 As New SqlClient.SqlConnection(sConnString)
        Dim sqlC4 As New SqlClient.SqlConnection(sConnString)
        Dim sqlC5 As New SqlClient.SqlConnection(sConnString)
        sqlC1.Open()
        sqlC2.Open()
        sqlC3.Open()
        sqlC4.Open()
        sqlC5.Open()
        Dim sqlCMD As New SqlClient.SqlCommand( _
            "SELECT session_id, login_name " & _
            "FROM sys.dm_exec_sessions des " & _
            "WHERE des.host_name = HOST_NAME " & _
            "   AND des.program_name = 'MultiConnTest';", sqlC1)
        Console.WriteLine("Testing multiple connections to SQL Server.")
        Console.WriteLine()
        Console.WriteLine("Sessions currently open on the server, with username")
        Dim sqlReader As SqlClient.SqlDataReader = sqlCMD.ExecuteReader
        Dim iSessionID As Int32
        Dim sUserName As String
        While sqlReader.Read
            iSessionID = sqlReader.GetValue(0)
            sUserName = sqlReader.GetValue(1)
            Console.WriteLine(iSessionID & vbTab & sUserName)
        End While
        sqlReader.Close()
        sqlCMD = Nothing
        sqlC1.Close()
        sqlC2.Close()
        sqlC3.Close()
        sqlC4.Close()
        sqlC5.Close()
        Console.WriteLine("Press any key to exit")
        Console.ReadKey()

    End Sub

End Module

The console output from running this against my machine is:

Testing multiple connections to SQL Server.

Sessions currently open on the server, with username
52      Max
53      Max
54      Max
55      Max
56      Max
Press any key to exit

The SQL Server Error Log, after enabling auditing of both successful and unsuccessful logins, shows:

LogDate                 ProcessInfo Text
2014-09-16 10:31:54.290 Logon       Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]
2014-09-16 10:31:54.290 Logon       Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]
2014-09-16 10:31:54.290 Logon       Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]
2014-09-16 10:31:54.290 Logon       Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]
2014-09-16 10:31:54.290 Logon       Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]

I think you can see from this a possible source of your error log entries is not some errant SQL Server behavior that can be "disabled", but is far more likely to be the desired result arising from the combination of auditing successful logins, and client applications that use multiple connections.

I would check the source code of whatever applications are running against your SQL Server to determine if they are in-fact using multiple connections.

If they are, I would use sp_readerrorlog to look at the error log.  I might even go so far as to create a stored procedure to encapsulate the functionality of sp_readerrorlog to produce output in whatever format you desire.


This question has been answered by Chris Saxon of Oracle's "Ask TOM" team, who explains why ANSI joins are preferable to the old Oracle syntax:

  This is because there are number of restrictions that apply to the (+)
  operator, but not the "outer join" clause. Follow the link for the
  full list.

The article does acknowledge that there can be cases where the old Oracle syntax is necessary (Materialized Views don't support ANSI - should be "fixed" in his view - implying it is a bug).

The documentation link referred to in the article says:

  Oracle recommends that you use the FROM clause OUTER JOIN syntax
  rather than the Oracle join operator. Outer join queries that use the
  Oracle join operator (+) are subject to the following rules and
  restrictions, which do not apply to the FROM clause OUTER JOIN syntax:

(rules excluded - follow link)

In your case, you might want to take a careful look at Plan Management (previously called Plan Stability - nice Oracle white paper here). The site (oracle-base) is a superb site for all things Oracle - I'd put it in my top 5 no matter what I was doing (dev, test, prod...).

The big plus is you won't get major swings in performance as a plan tilts from one optimization to the other - the price you pay for this is that you won't see performance improvements either! If you do implement plan management, then you should revisit those managed plans (on dev/test) from time to time to ensure that they are still optimal (or at least satisfactory).


Getting sum result for last year divided quarterly

I'm not sure if I understood the problem correctly, but maybe you're looking for something like this?

Select
to_char(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1), 'MM') ||'-'|| to_char(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1), 'YYYY') AS Month,
sum(case when (AP.ASSET_TYPE like 'CFTD%') then ABS(AP.Eval_Market_Value) else 0 end) as 'Depostis',
sum(case when (AP.ASSET_TYPE like 'CASH%') then ABS(AP.Eval_Market_Value) else 0 end) as 'Cash',
sum(case when (AP.ASSET_TYPE = 'FTL') then ABS(AP.Eval_Market_Value) else 0 end) as 'Loan',
...


It is happening because of the row being filtered before the LAG() function executes, making it unable to get the previous results.

To work around this, we could use a CTE or a subquery to execute the LAG() function before applying the filter.

Using a CTE

;WITH CTE  AS
(
SELECT
    s.StatusName, LAG(s.StatusName) OVER (ORDER BY cs.CustomerId, cs.CustomerStatusId) as PreviousStatus, cs.StatusId,cs.CustomerStatusId
FROM
    #CustomerStatus as cs
    left join #Status as s on s.StatusId = cs.StatusId
)
SELECT StatusName ,PreviousStatus
FROM CTE
WHERE
StatusId = 3
ORDER BY
    CustomerStatusId;

Result

StatusName  PreviousStatus
Finished    Started

Rextester

With a subquery

SELECT outerquery.StatusName ,outerquery.PreviousStatus
FROM (
    SELECT
    s.StatusName, LAG(s.StatusName) OVER (ORDER BY cs.CustomerId, cs.CustomerStatusId) as PreviousStatus, cs.StatusId,cs.CustomerStatusId
    FROM
    #CustomerStatus as cs
    left join #Status as s on s.StatusId = cs.StatusId
     ) as outerquery
WHERE
outerquery.StatusId = 3
ORDER BY
    outerquery.CustomerStatusId;

Result

StatusName  PreviousStatus
Finished    Started

Rextester

The ORDER BY CustomerStatusId; might not be needed if you only need one record


Create dynamic set conditionally excluding member if exists

IsError to the rescue!  Combined with an iif was able to solve it.  Thanks to this post for leading me right to it.

iif (
        isError(
            ([Claim].[Claim Status].[All Claims].Children - [Claim].[Claim Status].[Pending])
            )
      , ([Claim].[Claim Status].Children)
      , ([Claim].[Claim Status].[All Claims].Children - [Claim].[Claim Status].[Pending])
)


Exporting results of a query in SQL developer without having to execute the query twice

There are some SQL Developer specific comments/"hints". For example if you run the below as a script (F5) and not a statement (Ctrl-Enter):

select /*csv*/ * from table;

You will get the results in CSV format the first time already. You can even spool the output just as in SQL*Plus. So you could just run the below block of code as a script (select lines and F5) and get a CSV directly in one pass:

spool C:\Users\XYZ\Desktop\my.csv
select /*csv*/ * from table;
spool off

Starting with version 4.1, you do not even need to use the above comment/"hint". You can just:

SET SQLFORMAT csv

Then run your query as a script.

Further options here:
http://www.thatjeffsmith.com/archive/2012/05/formatting-query-results-to-csv-in-oracle-sql-developer/


Indexed view referencing objects on two different schemas

I think I found the answer here. Basically, grant authorization on my second schema to dbo:

ALTER AUTHORIZATION ON SCHEMA::sfdc TO dbo

So, it really has to do with ownership/authorization rather than the schema itself.
See: https://www.sqlteam.com/articles/understanding-the-difference-between-owners-and-schemas-in-sql-server


This isn't saying the default file locations need to be the same, this is saying that you don't have identical paths for all files in the database(s) on the secondary server.

Example:

Primary Replica: Database files are in E:\Mount1\, E:\Mount2\, F:\Mount3

Secondary Replica: Existing directories E:\Mount1\, F:\Mount3

You'll notice that the secondary replica is missing E:\Mount2\

The fix is to make the secondary have identical paths for the databases involved.


Why filtered index on IS NULL value is not used?

Why this index is not "covering" for this query:

No good reason.  That is a covering index for that query.

Please vote for the feeback item here: https://feedback.azure.com/forums/908035-sql-server/suggestions/32896348-filtered-index-not-used-when-is-null-and-key-looku

And as a workaround include the WhereColumn in the filtered index:

CREATE NONCLUSTERED INDEX IX_MyTab_GroupByColumn
ON MyTab (GroupByColumn) include (WhereColumn)
WHERE (WhereColumn IS NULL)


This takes one from a table that has a PK on date (or datetime, change accordingly), to a table with an auto_increment column. Then it takes a child table if you will (that relied on the previous date PK FK setup) in a new direction using the new parent table's auto_increment PK.

It could be done inline in the original tables. But I am risk adverse, and not wanting to mess up anyone's originals. At the end, do drops and renames. By the way, one interesting functionality with mysql rename is doing hot-swap renaming of tables that are safe even in high concurrency environments.

So in the below, think of origThought and nextThought as the before and after of a main table. And think of sotv1 and sotv2 as the before and after of some-other-table before and after conversion.

Create the Schema

create table origThought
(   myDate date not null, -- this can be a date or datetime, either one (but used throughout all tables consistently)
    thing varchar(10) not null,
    primary key(myDate)
);

create table nextThought
(   id int auto_increment primary key,
    myDate date not null,   -- remember that this was the driving force in old table as a PK
    thing varchar(10) not null
);

create table sotv1
(   -- some other table version 1
    id int auto_increment primary key, -- let's just say it started with this one
    ref_ToOldPK_myDate date not null,
    colC varchar(10) not null,
    constraint `asdf` foreign key (ref_ToOldPK_myDate) references origThought(myDate)
);

create table sotv2
(   -- some other table version 2
    id int auto_increment primary key,
    ref_ToOldPK_myDate date not null, -- comes along til we clean things up
    colC varchar(10) not null,
    newPkValue int not null, -- this is our new FK value pointing back to nextThought(id)
    -- when I say newPkValue, I mean the new PK value in nextThought table (not the PK value id in this table)
    constraint `asdf2` foreign key (newPkValue) references nextThought(id) -- our new PK in 'parent' table
    -- point with asdf is that asdf will fail cuz that name is already used, so named it something else if you need to
    -- many people don't care and leave out the `asdf` part
);
-- show create table sotv2; -- can see key and constraint names with this

Now load some data

insert origThought (myDate,thing) values('2015-01-02','frog'),('2015-01-07','fish');
-- convert our main table to nextThought
insert nextThought (myDate,thing) select myDate,thing from origThought order by myDate;
-- ok that is done, now onward
insert sotv1(ref_ToOldPK_myDate,colC) values ('2014-02-02',1); -- error 1452 showing FK was in force
insert sotv1(ref_ToOldPK_myDate,colC) values ('2015-01-02',77); -- success
insert sotv1(ref_ToOldPK_myDate,colC) values ('2015-01-07',88); -- success
insert sotv1(ref_ToOldPK_myDate,colC) values ('2015-01-02',99); -- success

Complete the conversion of sotv2 table

Now we merely want to patch our new sotv2 table using appropriate newPkValue.

Well not patch it because our FK is in place, so let's try to do it all in one insert statement.

Else it will fail due to FK (which is your friend). Your foreign key constraints are your friends.

insert sotv2(ref_ToOldPK_myDate, colC, newPkValue)
select t1.ref_ToOldPK_myDate, t1.colC, t2.id
from sotv1 t1
join nextThought t2
on t2.myDate=t1.ref_ToOldPK_myDate;

Look at results

select * from sotv2;
+----+--------------------+------+------------+
| id | ref_ToOldPK_myDate | colC | newPkValue |
+----+--------------------+------+------------+
|  1 | 2015-01-02         | 77   |          1 |
|  2 | 2015-01-07         | 88   |          2 |
|  3 | 2015-01-02         | 99   |          1 |
+----+--------------------+------+------------+

bingo, what we want to see.

Drop the prev date residue we don't need anymore in this 'child' table

alter table sotv2 drop column ref_ToOldPK_myDate;

Now we have

select * from sotv2;
+----+------+------------+
| id | colC | newPkValue |
+----+------+------------+
|  1 | 77   |          1 |
|  2 | 88   |          2 |
|  3 | 99   |          1 |
+----+------+------------+

That's even better !


IDX_2 is the best index for that query when the test for work_id is included.  The ORDER BY is too complex for any index to help.
If you need to exclude work_id, then make an index without it.
The table is probably under 2GB; how big is innodb_buffer_pool_size?
Most of the BIGINTs are likely to be overkill.  Shrinking them from that 8-byte size would shrink the table.  But that probably would not help the 15 seconds much.


Getting the data from a foreign key table

A simple JOIN query would be in order

SELECT B.* FROM UsersClients A
INNER JOIN Clients B ON A.clientid = B.id
WHERE A.userid = 2;

Give it a Try !!!


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

First, create a table type:

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

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

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

To test it locally...

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

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

For SQL Server 2005

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

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

So then your function can be:

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

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

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


If I understand your question, you should think about it this way:

Select aat.action_id, aat.emp_id
    from ActionAssignedTo aat
    where aat.action_id = 10

gives you the keys you want.  You just expand that to include look-ups to the other tables to get the rest:

Select {whatever columns you want from any of the tables}
    from ActionAssignedTo aat
    inner join action a on a.id = aat.action_id
    inner join employee emp on emp.id = aat.emp_id
    where aat.action_id = 10

In your query, you are limiting the results to only action 10 in the second INNER JOIN and then further limiting the results to employee 1000 in the WHERE clause.  It's not employee 1000 and all the other employees who were also assigned action 10; it's just all the employees who were assigned action 10 (employee 1000 will be among them).  The solution (unless I'm totally misreading what you're trying to do) is fairly simple and your original approach was over-complicating things.

I hope this helps.

AMENDED:

I looked at your newly posted query.  Still looks like it;s getting in it's own way but it made me wonder if you're trying to test for Employee 1000 as either the assigner or the assignee (since you're joining the employee table to itself again).  So I added adding logic to do that additional test in the where clause on the off-chance that is the sticking point:

Select {whatever columns you want from any of the tables}
    from ActionAssignedTo aat
    inner join action a on a.id = aat.action_id
    inner join employee emp on emp.id = aat.emp_id
    where aat.action_id = 10
        and (aat.emp_Id = 1000 or a.emp_Id = 1000)

I'm inferring that ActionAssignedTo.emp_Id and action.emp_Id represent the assignee and assigner respectively - hope I got that right,

I hope this helps.


I strongly believe that only the "local" record is set to trust.
try something like that:

local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust

this should work for all kinds of local connections.


By default the SQL Server does not create a node for NULL values so if you change your query to use two CASE statements and return NULL when there is no match you will get the XML you are looking for.

WITH XMLNAMESPACES
(
  'http://www.w3.org/2001/XMLSchema-instance' AS abc,
  'uri' AS def
)
SELECT CASE WHEN AGE <= 30 THEN 'BUYER' END AS [def:GIRL],
       CASE WHEN AGE >= 40 THEN 'SELLER' END AS [def:BOY]
FROM ACCOUNTS
FOR XML PATH(''), ROOT('abc:OilCompany'), TYPE;

dbfiddle


Do you know from which binlog files you need to restore and how many binlog files there are?

Using mysqlbinlog utility we can view the binlog file content.

If it's a single file you can recover using:

mysqlbinlog /var/lib/mysql-bin.000016 | mysql –uroot –pReset123

If it's multiple files then just extract all content to one .sql file and directly restore it:

mysqlbinlog /var/lib/mysql-bin.000016 > /logs/allbinlog.sql

To append second binlog content to allbinlof.sql file use below command:

mysqlbinlog /var/lib/mysql-bin.000016 >> /logs/allbinlog.sql

You can also exclude certain statements using option of mysqlbinlog. Try this below for options:

mysqlbinlog --help


RAC, data guard, physical stand by and impdp

When you drop user on primary then this will be also reflected on secondary site. Also impdp will work as expected. But when you are about to import large amount of data MAA might slow down the whole procedure significantly.

If you have enough experience with RMAN you might try:

shutdown and disconnect the secondary
import data into master
backup master using RMAN
cross-site-restore RMAN backup of primary on secondary node, thus re-initialize the secondary.

Although this approach might seem to be complicated it might be faster in large configurations.


Backup / Restore of DB on MSSQL 2019 to MSSQL 2014

The harsh and direct answer would be no, you cannot downgrade.

You said scripting out and exporting data doesn't work, did you try to set the database compatibility mode to 2014. Then script out the database and create it on 2014 server. Then export the data?


MongoDB restoring to existing replica set (avoid creating a new one)

No. Copying to Secondary will not help
Yes. Primary will replicate the data to Secondary. Follow the steps below
Re-creating the replica set is not an option

Steps:

You can restore it on the Secondary node first
Next, Switch your Secondary with restored data to be the new Primary. Now the Primary has latest data
Bring down the Secondary, erase its data and then bring it up (Do a Resync) so it can copy the data from the Primary with latest data
Repeat the same on other Secondary
Once done, you can switch your Primary if required


SQL Server DB Restore Unable to browse directory

First thing you need to check is that:

Has the SQL Server service account been added as a member of any local
groups on EACH server? Does the group in which it resides have read-write
permissions?

So please check if you're NW service account is admin on the box, along with R/W permissions for it to access the folder for backups.

Also, in case you are on cluster,

then you need to make sure and check for the dependency of that drive when in cluster for the service account its running under!


How do I identify the program that called a stored procedure?

Multiple ways to get this information:

SELECT APP_NAME();

SELECT PROGRAM_NAME();

SELECT [program_name]
  FROM sys.dm_exec_sessions
  WHERE session_id = @@SPID;

Just keep in mind that it can be spoofed in the connection string or in Management Studio's connection properties. If I connect using the following parameter, all three of the above will return foobar:


Feature SQL Server 2012 - Filetable - The location of the file can not be opened

You need to have

  "Allow remote clients access to FILESTREAM data"  selected  as shown below :

Also, you need to leave the windows share as a Globalroot path and manage permissions through table properties, not Windows share properties.

Refer to : Prerequisites for FileTable


How to use a value from insert in conditional insert in postgres

You can use a CTE to supply the values:

with data (street, city, user_id) as (
  values ('street1','LA', 2)
)
insert into addresses(street, city, user_id)
select *
from data
where exists (select *
              from users
              where users.id = data.user_id
                and users.storeaddress = true);

Or alternatively a derived table:

insert into addresses(street, city, user_id)
select *
from (
   values ('street1','LA', 2)
) as data (street, city, user_id)
where exists (select *
              from users
              where users.id = data.user_id
                and users.storeaddress = true);


Make sure that you have uniform servers across the cluster because a slow/overloaded master node can affect performance of the Galera Replication cluster.
Check the node status by monitoring value of these variables wsrep_ready and wsrep_connected
Make sure that value of this variable is close to 0 wsrep_flow_control_paused
A high value for this variable wsrep_local_send_queue_avg for a node indicates a bottleneck in network line, If this is the case, the cause can be at any layer, from physical to OS configuration and must be investigated.
You can yourself profile SELECTs on diffrent nodes and see performance.

  mysql> SET profiling = 1;

  mysql> SELECT * FROM

  mysql> SHOW PROFILES;

I hope this information helps.


You need to also return multiple rows, not just a single value. You also don't need PL/pgSQL for this. A simple SQL function will do:

create or replace function myfunc()
  returns table (document json)
as
$body$
  select row_to_json(myview)
  from myview;
$body$
language sql;

returns table is the same as returns setof but you can define the column names.

You also need to put the function call into the from clause:

select *
from myfunc();

You don't really need function for that in the first  place. You could do that in the view directly:

create view myview
as
select row_to_json(t) as document
from (
  select retailerid, retailername
  from mytable
)t ;


postgres: could not find the database system

While I don't know what specific config file is pointing to that location, it can be specified when you run the initdb command, which it's sounding like you may not have run yet, given the error you're seeing.
If you just installed a new/upgrade postgres instance, you'll probably want to use a new data directory from whatever your old instance was using (i.e. you can just run the initdb command with no additional params, and it will use the default /usr/local/pgsql/data location which, apparently, is not yet in use for your machine), then you can export/import the old data if you still need it.


Total of orders open on a given date for each date in a date range

If your priority is speed of selects, the following approach allows for extremely fast selects. Instead of storing a period, you want to store two events (period start and period end). Change column is 1 when the period begins, and is -1 when the period ends. If more than one event occurs on the same day, they must have different EventNumberPerDay. RunningTotal is the number of open peroids after the event has happened:

CREATE TABLE dbo.Events(
 PeriodId INT NOT NULL,
 Change SMALLINT NOT NULL,
 ChangeDate DATE NOT NULL,
 EventNumberPerDay INT NOT NULL,
 RunningTotal INT NOT NULL);
GO

INSERT dbo.Events
        ( PeriodId ,
          Change ,
          ChangeDate ,
          EventNumberPerDay,
          RunningTotal
        )
-- first period begins
VALUES  ( 1 , 1, '20120801', 1, 1),
-- second period begins on the same day
(2, 1, '20120801', 2, 2),
-- third period begins
(3,1,'20120803',1, 3),
-- second period ends on the same day
(2,-1,'20120803',2, 2),
-- fourth period begins
(4,1,'20120804',1,3),
-- fourth period ends
(4,-1,'20120805',1,2),
-- first period ends
(1, -1, '20120808',1,  1),
-- third period ends
(3, -1, '20120809',1,  0);
GO

Also you need a calendar table:

CREATE TABLE dbo.Calendar([Date] DATE NOT NULL);
GO

INSERT INTO dbo.Calendar([Date])
VALUES('20120801'),
('20120802'),
('20120803'),
('20120804'),
('20120805'),
('20120806'),
('20120807'),
('20120808'),
('20120809'),
('20120810'),
('20120811');

Once that is accomplished, then your select is very simple and very fast:

SELECT  [Date] ,
        coalesce(RunningTotal, 0) AS NumOpenIntervals
FROM    dbo.Calendar
        OUTER APPLY ( SELECT TOP ( 1 )
                                RunningTotal
                      FROM      dbo.Events
                      WHERE     ChangeDate <= [Date]
                      ORDER BY  ChangeDate DESC, EventNumberPerDay DESC
                    ) AS t
ORDER BY [Date]

Of course, this query is only correct if the data in Events table is valid. We can use constraints to ensure 100% data integrity. I can explain how if you are interested.

Another alternative is to just load your raw data, your periods, into a client application - your problem is absolutely trivial in C++/C#/Java.

Yet another approach is to use an RDBMS with fast cursors such as Oracle - that will allow you to just write a simple cursor and enjoy good performance, but still not always as good as my first solution.


in order to prevent impacting original DB performance.

All three options you are considering will impact performance on the Oracle DB.

If you don't want to impact performance at all, apply archivelog files from Oracle to a standby DB and query that (you could try Linked Servers first to find out if it's fast enough for you).


INSERT gives Error Code: 1366. Incorrect string value: '\xF0\x9F\x98\x80' for column

The problem appears to be in the Client.

When the client connects to the MySQL server, it needs to announce that the bytes in the client are utf8mb4.  This can be done in several ways:

In the connection parameters (client-dependent).
SET NAMES utf8mb4.

\U+1F600 is the Unicode representation for that Emoji.
\xF0\x9F\x98\x80 is the equivalent Hex.
F09F9880 is the UTF-8 (utf8mb4) in Hex.

Avoid the Unicode representation.


ST_GeomFromGeoJSON() Causes postgres to crash

From the osgeo ticket

  The root our our crash problem was library version mismatch. The build system was using 0.9, but I was deploying on a system with 0.11. The RPM packages only declared their requirement as "json-c" without any version specificity. Apparently json is not forward binary compatible, so packagers beware: you have to peg your json-c version in your package dependency declarations.

followed by

Resolution set to invalid
Status changed from new to closed
Closing because this is a build problem.


Those are page reads, mind you.  That is relevant.

Possibilities:

Improper or insufficient indexing.  Are any of the filtered fields indexed?  How wide are the indexes (see below)?
Poor page density.  What's your fill factor on any indexes you may have?  If it's too low, you are pulling a lot of pages for this.
Very wide indexes.  If you have indexes but they have a lot of fields, or very wide fields, then you get less rows per page.

I'm guessing you have a table or clustered index scan happening for at least one of these criteria, and the table is wide-ish which causes a lot of data to be read, regardless of how much data you actually need.


Help to understand 'log space used' metric

You can check your backup history by using a standard report:

... by using some scripts:
Script to retrieve SQL Server database backup history and no backups

---------------------------------------------------------------------------------
--Database Backups for all databases For Previous Week
---------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date

... or having a look to your server's ERRORLOG file and searching for Source = Backup


How to compare dates on minute level in plsql?

You can use TRUNC (date) function which returns date with the time portion of the day truncated to the unit specified by the format model.
Syntax:

TRUNC(date, fmt) or TRUNC(date).

Where fmt can be 'DAY', 'YEAR','HH','MI'.
For more format model please refer: ROUND and TRUNC Date Functions

Example:

SELECT 1 FROM sample_table WHERE TRUNC(sysdate,'MI')<=TRUNC(sample_table_date_field,'MI');

Otherwise you can also use EXTRACT function to extract the elements from datetime.
Example:

SELECT 1 FROM dual WHERE extract(minute from systimestamp)=extract(minute from systimestamp);


Your case statement is wrong. Right now if the values are the same then it shows compressed whereas you would want it to show not compressed.

select top(10) database_name,
case when  backup_size = compressed_backup_size then 'Not compressed'
else 'Compressed'
end as Compression,
backup_finish_date
from msdb.dbo.backupset
where database_name ='MyDAtabase'
order by backup_start_date desc


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

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

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

A COMMIT does the following

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

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

All dirty pages in memory are written to disk

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

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

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

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


Can't remove active node from SQL Server 2008 R2 Clustered Instance

I needed to remove an instance that was only installed on one node on a two node cluster. This unwanted instance had never been installed on the second node.

The error in the summary and detail log files stored in Setup Bootstrap sent me in the wrong direction.

I finally resolved the issue by following the following steps.

Adding a node to the unwanted instance
Move the unwanted instance to the second node
Removed the node from the first node
Removed the node from the second node

Unwanted instance removed from the cluster.


Use CROSS APPLY and the .nodes method of the XML data-type, eg something like this:

IF OBJECT_ID('dbo.yourTable') IS NOT NULL DROP TABLE dbo.yourTable
GO
CREATE TABLE dbo.yourTable (
    rowId INT IDENTITY PRIMARY KEY,
    ST_Registro_Del XML
)
GO

INSERT INTO dbo.yourTable ( ST_Registro_Del )
SELECT '<row>
  <ID_Cota>162986</ID_Cota>
  <ID_Taxa_Plano>1000</ID_Taxa_Plano>
  <ID_Plano_Venda>1020</ID_Plano_Venda>
  <ID_Pessoa>18522</ID_Pessoa>
</row>'
GO

SELECT
    r.c.value('(ID_Cota/text())[1]', 'INT' ) AS ID_Cota,
    r.c.value('(ID_Taxa_Plano/text())[1]', 'INT' ) AS ID_Cota,
    r.c.value('(ID_Plano_Venda/text())[1]', 'INT' ) AS ID_Cota,
    r.c.value('(ID_Pessoa/text())[1]', 'INT' ) AS ID_Cota
FROM dbo.yourTable yt
    CROSS APPLY yt.ST_Registro_Del.nodes('row') r(c)
WHERE rowId = 1

Here are my results:

The .nodes method returns a resultset from XML.  Use the text() accessor for a performance boost when querying untyped XML ( ie no XSD is associated with the XML column and the [1] ordinal, which is a shorthand way of saying "get me the first element you find".

HTH


VSS is being used to backup your database.

Edit: [Thanks Colin]

This causes the logged 'I/O is frozen' message.
It is not serious.
No need to resolve it.

Be warned, your database restore chain may be impacted, in which case it is serious.
If you backup strategy is weekly full, daily diff, hourly tran per day - 8am -5pm; a daily snapshot VSS backup will cause your diff's to fail.


I want to set lower_case_variables to 1 but I'm unable to locate my.cnf

To locate all "my.cnf" in the system:

$ find / -name my.cnf


How to repair all indexes in a MySQL database?

Perhaps you should look for all tables that have fulltext indexes

SELECT DISTINCT table_schema,table_name
FROM information_schema.statistics
WHERE index_type='FULLTEXT';

To individually remake all tables that have FULLTEXT indexes

REPAIR_SQL_SCRIPT=RemakeTables.sql
SQL="SELECT"
SQL="${SQL} CONCAT('ALTER TABLE ',db,'.',tb,' ENGINE=',eng,';')"
SQL="${SQL} FROM (SELECT A.table_schema db,A.table_name tb,B.engine eng"
SQL="${SQL} FROM (SELECT DISTINCT table_schema,table_name"
SQL="${SQL} FROM information_schema.statistics WHERE index_type='FULLTEXT') A"
SQL="${SQL} INNER JOIN information_schema.tables B"
SQL="${SQL} USING (table_schema,table_name)) AA"
mysql -uroot -ANe"${SQL}" > ${REPAIR_SQL_SCRIPT}
cat ${REPAIR_SQL_SCRIPT}

If you are OK with the contents of RemakeTables.sql, then execute it:

mysql -uroot < ${REPAIR_SQL_SCRIPT}

CAVEAT : All indexes are remade as well


MySQL IN() statements: Avoiding duplicate lists

You can create a temp table, insert your parameters there and then join it:

create temporary table tmp (x int);
insert into tmp(x) values (10027),(11535);
select x.*
from activities as x
join tmp as y
    on y.x = x.actor_id
join tmp as z
    on z.x = x.original_actor_id;


This should work for you:

CREATE VIEW MyView AS
SELECT <colList>
FROM <TableOne>
INNER JOIN <TableTwo> ON ...
AND .....
AND .....
LEFT JOIN <TableThree> ON ...
AND .....
AND .....
WHERE .....
AND .....
AND .....

Then replace in Procs with:

...
FROM MyView
WHERE
MedicalPlanCode IN ('abc', 'def', 'ghi')


What will be the best data model for membership based websites?

Check out the free data models at databaseanswers.org for inspiration.

Once you've attempted something yourself, we can help fine tune it.


SQL Server database backup to store only the last 7 backups in a file

I would recommend you to study and implement  Ola Hallengren's SQL Server Backup solution. Its free and is widely tested and recognized in the community.

e.g :

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

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

Automate and Improve Your Database Maintenance Using Ola Hallengren's Free Script will give you a good start.


Does SQL Server ignore STATISTICS for varchar columns as this article suggests?

Does SQL Server only use the varchar column length limit for row-size estimates? If not, then why does the SQLPerformance article describe the same?

Correct. SQL Server uses only the varchar (max specified) length when estimating row size. The SQLPerformance article accurately describes the estimated row size measurement.
The longer answer
In his example in the linked article, Aaron rebuilds all indexes to ensure all versions of the query have an equal playing field as far as both index size and statistics so that the execution plans for all cases are "ideal" and (as the experiment proved) almost equal, but not quite.
Statistics are used to estimate how many rows will be returned, not how much memory is granted for the execution of a query.
In the article, Aaron says (emphasis mine):

SQL Server has to take into account how much data might be in a column. It doesn't do this based on actually profiling the data, and it can't make any assumptions based on the <= 201 histogram step values. Instead, it has to estimate that every row holds a value half of the declared column size. So for a varchar(4000), it assumes every e-mail address is 2,000 characters long.

Aaron's reference to "the histogram step values" is a reference to the statistics histogram. The statistics histogram contains knowledge of at most 201 data values from the table. It knows the actual length of those (up to 201) explicit values, but it has no idea about the values in between those.
Additionally, Statistics are based on a sample of data, so there may be rows that were not analyzed as part of the sample, and relying on min/max/avg length of data from stats would be another opportunity for outdated or unrepresentative samples to adversely affect query execution.


Get nested procedure and sql execution oracle

v$sql gives you the information at a SQL child cursor level and sql_trace gives you the information at a SQL execution level. v$sql also tells you the program_id and the program_line# of the compiled pl/sql code that last hard parsed it.
That said, you are probably looking for the Hierarchical Profiler


If you really need to do a stack in MySQL I think you should clarify your answer as to why.  One key aspect of MySQL is that it makes a bunch of fundamental assumptions that it is serving to a single application's codebase.  If you are on MySQL, the best thing to do is implement this application-side.  If you really need to do this in the database, you need to rethink how you are using MySQL, or whether you want to use a different database.  Treating SQL as a public API on MySQL is problematic because applications can set sql_mode which can do things like determine how invalid data is treated or even what counts as valid data, and the dba has no control over what sql mode an application is using.

This being said, a stack on a db might look like (assuming you are not doing composite keys):

CREATE TABLE stack_demo (
   id bigint autoincrement primary key,
   table_name text not null,
   key_name text not null,
   key_value text not null,
   nav_down bigint unique references stack_demo(id)
);

Or better yet, the PostgreSQL version:

CREATE TABLE stack_demo (
   id bigserial primary key,
   table_name text not null,
   key_name text not null,
   key_value text not null,
   nav_down bigint unique references stack_demo(id)
);


Postgresql warning: type reference X converted to Y (SQL State: 00000 - Error Code: 0)

Answering my own question...

It turns out this is harmless INFO-level logging: https://stackoverflow.com/a/3531274/14731


How can I write a trigger that can subtract values between two tables

Look for

CREATE TRIGGER trigger_name
AFTER INSERT
ON transaction
FOR EACH ROW
UPDATE defaulters
SET debt_amount = debt_amount - NEW.amount_paid
WHERE myPIN = NEW.myPIN
  AND NEW.status = 1;

fiddle


How To Grant A User In AD Groups Which Is Denied To DML Commads, To Update On A Spesific Tables

I need to give just update and delete permission on specific tables to
  several developers who are in the SQLQRY AD account. How can i do that
  without extracting them out of the group?

If you are using the same AD user for all your developers I would advise you to start using AD groups and adding your developers to these groups. See here for a Q/A on using one AD login for multiple users. Afterwards you could follow the steps below.

Permissions are granted to all members of the AD group or none.

If you want to grant permissions to some of these users, you would have to add a different ad group or add the users separately.

Afterwards you can grant the update and delete permissions to that group or create a new role with the permissions and add the group / users to it .

Example commands:

USE [master]
CREATE LOGIN [SQLQRY_2] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [DBName]

CREATE USER [SQLQRY_2] FOR LOGIN [SQLQRY_2];
CREATE ROLE [NewRole];
GRANT INSERT,UPDATE,DELETE ON dbo.test to [NewRole]; -- table1
-- Table 2,3, ...

ALTER ROLE [NewRole] ADD MEMBER [SQLQRY_2];

  They are defined in SQLQRY AD account and they don't have update and
  delete permissions out there. So, will my role overwrite to my deny
  rule?

If you have deny permissions specified on writing data to the tables then granting corresponding write permissions will not work. DENY has a higher precedence than grant.


How to group a view by the tables from which it gathers its data?

Maybe you need

CREATE VIEW my_view
AS
SELECT *, 'LAMPS' AS TABLE_NAME FROM LAMPS WHERE STATUS = 'OFF'
UNION ALL
SELECT *, 'CARS'                FROM CARS WHERE STATUS = 'OFF'
UNION ALL
SELECT *, 'DRONES'              FROM DRONES WHERE STATUS = 'OFF'
UNION ALL
SELECT *, 'OVENS'               FROM OVENS WHERE STATUS = 'OFF';

?


WITH
-- enumerate all rows according to specified ordering
cte1 AS (SELECT *, ROW_NUMBER() OVER (ORDER BY FIELD(Impact,'HIGH','MODERATE','LOW'), Frequency) rn
         FROM test),
-- find the most upper occurence for each `Gene`
cte2 AS (SELECT Gene, MIN(rn) rn
         FROM cte1
         GROUP BY Gene)
-- select desired data
SELECT t1.*
FROM test t1
JOIN cte2 USING(Gene)
-- use found "most upper occurence" for groups pre-sorting,
-- then apply specified sorting
ORDER BY cte2.rn, FIELD(Impact,'HIGH','MODERATE','LOW'), Frequency;

fiddle

the order of the gene does not matter when both have the same Impact and Frequency value, although the ideal would be to order them alphabetically, so ADA wuould be first in your example.

If so simply add , Gene to the end of ordering expression.


While I would say you are definitely correct, and this was the correct design, your tutor may be technically correct (it's difficult to tell, especially, if you're translating form another language - can't tell if you are or not (congrats!)).

You are correct, that a given sectionNo determines the branchNo, since that value exists in BranchSection. Including branchNo in Staff in addition to sectionNo is redundant, and would indeed lead to having to make sure that all linked branchNo values in Staff are updated if the branchNo in BranchSection were ever changed.

However, you said:

  One of the requirements of the given problem was that the staff entity hold information about the section and branch of each staff member

(Emphasis added)

Technically, Staff does not (directly) hold information about the branch; only about the section.

If you haven't already argued too much with the tutor, I would note the maintenance (and normalization) issues, and ask him why he wants you to start off with a denormalized design.

And I mean ask him, seriously. There may be a reason. At the conceptual level, you do need to tie a staff person to the branch they're located at; perhaps he believes that, for a high-level design, you need to leave that sort of thing in place, even if it's not literally going to be implemented that way in the actual database. Maybe they wanted everyone to include it so they can explain the importance of normalization (a bad reason to mark you down still, but if you've got tutors with an actual professor over them, they may be following instructions overly literally). Or, maybe they're training you for the real world; customers sometimes have specific requests that defy best DB practices, and that you can't talk them out of.

It's even possible that this implies a deeper issue with the design. Perhaps BranchSection should be just Section, and should apply to all instances of a given section, regardless of the branch. If so, then branchNo wouldn't belong, and each Staff row would need both branchNo and sectionNo. That's not how I read the question as presented, and it seems rather unlikely to me, but it's not impossible.

So, especially if you'll have further assignments based off this one, I'd say you have a valid reason to ask for further explanation, to valid your reading of the assignment.

If all else fails, and there is a class professor over the tutor, talk to them. They may have the latitude to accept a "more correct" design than expected, if that's what happened, and may have a better view on the class overall, and might have the explanation you need.


That may depend on what version of OpenEdge you are querying. Reasonably modern releases do support UNION:
https://www.progress.com/docs/default-source/openedge/progress-openedge-sql-compliance-to-core-sql-2011.pdf?sfvrsn=0
(this applies to OpenEdge 11.5 and above)


Any way to verify user password from PL/PGSQL?

I did it as:
CREATE FUNCTION login(auid TEXT, password TEXT, dbhost TEXT DEFAULT NULL) RETURNS ...
LANGUAGE PLPGSQL AS
$$
DECLARE
  rec RECORD;
  conn_str TEXT;
  dbname TEXT;
  conn_name TEXT;
  failed BOOLEAN := FALSE;
BEGIN
  SELECT u.uid AS role,u.my_role,u.display_name INTO rec FROM ldap_users u
    WHERE u.uid=auid AND u.my_role IS NOT NULL;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'User not permitted to access TV'
      USING HINT='No such user or it is not allowed to access TV';
  END IF;

  IF dbhost IS NULL THEN
    dbhost := FORMAT('%s', INET_SERVER_ADDR());
  END IF;

  dbname := CURRENT_DATABASE();
  conn_str = 'host=' || dbhost || ' dbname=' || dbname || ' user=' || auid || ' password=' || password;
  conn_name := FORMAT('conn_%s', RANDOM());

  BEGIN
    PERFORM dblink_connect(conn_name, conn_str);
  EXCEPTION WHEN OTHERS THEN failed := TRUE;
  END;

  BEGIN
    PERFORM dblink_disconnect(conn_name);
  EXCEPTION WHEN OTHERS THEN NULL;
  END;

  IF failed THEN
    RAISE EXCEPTION 'User failed to login to the DB'
      USING HINT='It seems the password is wrong';
  ELSE
    ...
    RETURN ...
  END IF;

END;
$$;

Specific things are replaced with ... - they are not related to the question.
To use it you need CREATE EXTENSION IF NOT EXISTS dblink; to enable the dblink extension.
This login() function verifies the password of the user by connecting to itself (to the current host (if dbhost is NULL) and to the current database.
It was tested and I use it but it works fine only if the host has just one IP. Otherwise, it's not clean which IP will be returned from the INET_SERVER_ADDR().


Symantec Backup Exec runing DBCC CheckDB and Backup on my SQL server

I'm not sure if Symantec still owns it, but Backup Exec is Netbackup's little brother. I don't remember the feature differences but Netbackup is more for Enterprise environments and Backup exec for smaller ones.

Are you sure it's Backup Exec that's running DBCC? I've used Netbackup for SQL for many years and it's always done checksum checking during backup. I've never seen it run a DBCC check directly from the Netbackup server or via the client.

Any third party SQL backup software rides on top of the MS written SQL backup. What you're paying for is much better compression, AlwaysOn support, centralized job scheduling and backup image storage. SQL backup has a checksum option and most of the third party products support it as well. But the only time i've seen something not-SQL run dbcc or alter index on a database is Sharepoint doing it.

I've used both and prefer the third party products to native SQL backup any day.


Requests to local CouchDB have 40ms latency

This is caused by Nagle's algorithm. CouchDB sends the HTTP headers and the response body in separate calls, causing the kernel to not deliver the response body for 40ms.

As a workaround, you can use this LD_PRELOAD shim to completely turn off Nagle's algorithm for the database process.


MS SQL Management Studio - Script of INSERT statements leaves empty chars for shorter than limit NVARCHAR?

I believe that the underlying data in your table has additional spaces at the end.  You can verify the actual data length with a query like:

SELECT LEN(StudentName) FROM dbo.Students WHERE StudentID = 1

If it's not 4, then it's storing more than just 'Mike' and SSMS is simply preserving your data.

If you don't want the extra spaces, you can remove it from your data thusly:

UPDATE dbo.Students
SET
    StudentName = LTRIM(RTRIM(StudentName)), -- older db compatibility levels
    StudentSurname = TRIM([StudentSurname]) -- new db compatibility levels


to change collation for system database SQL Server 2017

The only way to change the collation for the instance itself (instance-level and system DBs plus user databases) without reinstalling is an undocumented command/option. The "rebuilding system databases" option referred to in the other answers is effectively reinstalling, even if it isn't in a literal sense, nor does that approach update any user databases to match the new collation.
I posted a detailed analysis of this undocumented approach, and the implications of making any collation changes to an instance and/or database, here:
Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?
There are quite a few areas that could be affected, and it depends greatly on if you are talking about just one particular database, or the instance and one or more of the databases on that instance, and if you have any VARCHAR data containing characters with values of 128 - 255, and if you are switching sensitivities (e.g. case-sensitive to case-insensitive, or vice-verse).
With regards to the ALTER DATABASE ... COLLATE ... option:

That does not work on system databases.
That is not permitted if you have one of many dependencies (e.g. TVFs returning string columns, computed column returning string types, etc).
For user databases where it is permitted, it only changes the system tables (e.g. sys.objects, sys.schemas, etc); it does not change any user tables (columns or indexes)


This is resolved now. What I did is to start the mongos at a specific port number,like:

mongos --configdb "myrepl/localhost:27019,localhost:27020 --port 47019

Then I connected a mongo instance to it:

mongo --host localhost --port 47019

Further I executed all the commands to add shards in this shell and it worked! Please correct me if I'm wrong


Need help figuring out a daisy chain issue

A simple way to approach this would be to use a loop.

Start by identifying your records where alt_key does not show up in any alt_key2 values. Load those into a staging table with pmi_order 1, and mark the records as processed in the source table (add a bit field to track this). These are your parent records.

Now start your loop with an index (n) of 2, and keep going up by one for each loop. For each staging record with pmi_order = (n-1), find the unprocessed main table record whose alt_key value matches the staging alt_key2 value. This becomes your pmi_order 2 (i.e. current value of n) record for that primary_member_nbr. (Note, you will alternate matching staging.alt_key2 to main.alt_key and staging.alt_key to main.alt_key2 for each execution of the loop.)

After each loop completes, check to see if any unprocessed records remain in the main table. Once you have exhausted the main table, you are done.


How to generate Entity Relationship (ER) Diagram of a on-premises database using Azure Data Studio (macOS)?

Azure Data Studio doesn't have database diagramming built in.

This means you're asking a shopping question, and here's how and where to ask a shopping question.


2GB of RAM is barely acceptable to run Windows, let alone SQL Server on top.

Obviously your 15GB of data doesn't fit into 2GB of RAM. So, any and every query you run that requires rows that aren't in RAM will require reads from disk.

From there, you can do the math on how much data you're requesting versus how fast your disks can deliver it. Given that the server is so woefully underspecified for memory, one can assume you've got just a single spinner delivering IOPS at miserably slow rate, perhaps 50MB/sec for a sequential read but as low as 1MB/sec with several queries fighting for their share.

Have 5 users each run a query that needs 1GB of data that isn't currently in RAM and you'll be waiting 15+ minutes for the data to crunch its way through the system.

RAM is comically cheap, especially compared to the cost of you wasting your time trying to optimise 15GB into 2GB. Fill the server, or buy a new one.

Also: the Pentium E6300 seems to be a desktop-class processor. You want a computer designed to be a server. Just adding a couple of disks and more RAM may not be satisfactory.


Unfortunately, MySQL is behaving exactly as I expected.

The problem lies in the fact that MySQL's Query Optimizer gets very confused when used in conjunction with other tables in an EXPLAIN plan's compliation. I wrote about this before:

https://stackoverflow.com/a/6092216/491757 (May 23, 2011)
FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional (Oct 25, 2011)
Mysql fulltext search my.cnf optimization (Jan 26, 2012)
MySQL EXPLAIN doesn't show 'use index' for FULLTEXT (May 07, 2012)

My suggestion: Try Refactoring the Query By Getting the FULLTEXT Search to occur before JOINs

SELECT * FROM table1
INNER JOIN table2 ON table1.pid = table2.pid
LEFT JOIN
(
    select * from table3
    WHERE MATCH (title, content)
    AGAINST ('+words' IN BOOLEAN MODE )
    ORDER BY pid
) table3
ON table1.pid = table3.pid
;


Postgres : Relation does not exist error

You created your tables with double quotes, and now the names are case sensitive.

As documented in the manual "Approvals" and Approvals are two different names.

Now that you created the table names in a case sensitive manner, you have to use those dreaded double quotes always.

select *
from "Approvals";

As a general advice: never use double quotes in SQL.


Whats is the right way to check for new messages?

SELECT * FROM mails
    WHERE user_id = 1
      AND sequence > $left_off
    ORDER BY sequence
    LIMIT 100;

You need a column sequence that is monotonically increasing.  And AJAX needs to keep track of "where it left off".


Huge slowdown to SQL Server query on adding wildcard (or top)

This is a decision of the cost based optimiser.

The estimated costs used in this choice are incorrect as it assumes statistical independence between values in different columns.

It is similar to the issue described in Row Goals Gone Rogue where the even and odd numbers are negatively correlated.

It is easy to reproduce.

CREATE TABLE dbo.animal(
    id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    colour varchar(50) NOT NULL,
    species varchar(50) NOT NULL,
    Filler char(10) NULL
);

/*Insert 20 million rows with 1% black and 1% swan but no black swans*/
WITH T
     AS (SELECT TOP 20000000 ROW_NUMBER() OVER (ORDER BY @@SPID) AS RN
         FROM   master..spt_values v1,
                master..spt_values v2,
                master..spt_values v3)
INSERT INTO dbo.animal
            (colour,
             species)
SELECT CASE
         WHEN RN % 100 = 1 THEN 'black'
         ELSE CAST(RN % 100 AS VARCHAR(3))
       END,
       CASE
         WHEN RN % 100 = 2 THEN 'swan'
         ELSE CAST(RN % 100 AS VARCHAR(3))
       END
FROM   T

/*Create some indexes*/
CREATE NONCLUSTERED INDEX ix_species ON  dbo.animal(species);
CREATE NONCLUSTERED INDEX ix_colour ON  dbo.animal(colour);

Now try

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black'
       AND species LIKE 'swan'

This gives the plan below which is costed at 0.0563167.

The plan is able to perform a merge join between the results of the two indexes on the id column. (More details of merge join algorithm here).

Merge join requires both inputs to be ordered by the joining key.

The nonclustered indexes are ordered by (species, id) and (colour, id) respectively (nonunique non clustered indexes always have the row locator added in to the end of the key implicitly if not added explicitly). The query without any wildcards is performing an equality seek into species = 'swan' and colour  ='black'. As each seek is only retrieving one exact value from the leading column the matching rows will be ordered by id therefore this plan is possible.

Query plan operators execute from left to right. With the left operator requesting rows from its children, which in turn request rows from their children (and so on until the leaf nodes are reached). The TOP iterator will stop requesting any more rows from its child once 10 have been received.

SQL Server has statistics on the indexes that tell it that 1% of the rows match each predicate. It assumes that these statistics are independent (i.e. not correlated either positively or negatively) so that on average once it has processed 1,000 rows matching the first predicate it will find 10 matching the second and can exit. (the plan above actually shows 987 rather than 1,000 but close enough).

In fact as the predicates are negatively correlated the actual plan shows that all 200,000 matching rows needed to be processed from each index but this is mitigated to some extent because the zero joined rows also means zero lookups were actually needed.

Compare with

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'

Which gives the plan below which is costed at 0.567943

The addition of the trailing wildcard has now caused an index scan. The cost of the plan is still quite low though for a scan on a 20 million row table.

Adding querytraceon 9130 shows some more information

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'
OPTION (QUERYTRACEON 9130)

It can be seen that SQL Server reckons it will only need to scan around 100,000 rows before it finds 10 matching the predicate and the TOP can stop requesting rows.

Again this makes sense with the independence assumption as 10 * 100 * 100 = 100,000

Finally lets try and force an index intersection plan

SELECT TOP 10 *
FROM   animal WITH (INDEX(ix_species), INDEX(ix_colour))
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'

This gives a parallel plan for me with estimated cost of 3.4625

The main difference here is that the colour like 'black%' predicate can now match multiple different colours. This means the matching index rows for that predicate are no longer guaranteed to be sorted in order of id.

For example the index seek on like 'black%' might return the following rows

+------------+----+
|   Colour   | id |
+------------+----+
| black      | 12 |
| black      | 20 |
| black      | 23 |
| black      | 25 |
| blackberry |  1 |
| blackberry | 50 |
+------------+----+

Within each colour the ids are ordered but the ids across different colours may well not be.

As a result SQL Server can no longer perform a merge join index intersection (without adding a blocking sort operator) and it opts to perform a hash join instead. Hash Join is blocking on the build input so now the cost reflects the fact that all matching rows will need to be processed from the build input rather than assuming it will only have to scan 1,000 as in the first plan.

The probe input is non blocking however and it still incorrectly estimates that it will be able to stop probing after processing 987 rows from that.

(Further info on Non-blocking vs. blocking iterators here)

Given the increased costs of the extra estimated rows and the hash join the partial clustered index scan looks cheaper.

In practice of course the "partial" clustered index scan is not partial at all and it needs to chug through the whole 20 million rows rather than the 100 thousand assumed when comparing the plans.

Increasing the value of the TOP (or removing it entirely) eventually encounters a tipping point where the number of rows it estimates the CI scan will need to cover makes that plan look more expensive and it reverts to the index intersection plan. For me the cut off point between the two plans is TOP (89) vs TOP (90).

For you it may well differ as it depends how wide the clustered index is.

Removing the TOP and forcing the CI scan

SELECT *
FROM   animal WITH (INDEX = 1)
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'

Is costed at 88.0586 on my machine for my example table.

If SQL Server was aware that the zoo had no black swans and that it would need to do a full scan rather than just reading 100,000 rows this plan would not be chosen.

I've tried multi column stats on animal(species,colour) and animal(colour,species) and filtered stats on animal (colour) where species = 'swan' but none of these help convince it that black swans don't exist and the TOP 10 scan will need to process more than 100,000 rows.

This is due to the "inclusion assumption" where SQL Server essentially assumes that if you are searching for something it probably exists.

On 2008+ there is a documented trace flag 4138 that turns off row goals. The effect of this is that the plan is costed without the assumption that the TOP will allow the child operators to terminate early without reading all matching rows. With this trace flag in place I naturally get the more optimal index intersection plan.

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'
OPTION (QUERYTRACEON 4138)

This plan now correctly costs for reading the full 200 thousand rows in both index seeks but over costs the key lookups (estimated 2 thousand vs actual 0. The TOP 10 would constrain this to a maximum of 10 but the trace flag prevents this being taken into account). Still the plan is costed significantly cheaper than the full CI scan so is selected.

Of course this plan might not be optimal for combinations which are common. Such as white swans.

A composite index on animal (colour, species) or ideally animal (species, colour) would allow the query to be much more efficient for both scenarios.

To make most efficient use of the composite index the LIKE 'swan' would also need to be changed to = 'swan'.

The table below shows the seek predicates and residual predicates shown in the execution plans for all four permutations.

+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+
|                 WHERE clause                 |       Index       |                         Seek Predicate                         |              Residual Predicate              |
+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+
| colour LIKE 'black%' AND species LIKE 'swan' | ix_colour_species | colour >= 'black' AND colour < 'blacL'                         | colour like 'black%' AND species like 'swan' |
| colour LIKE 'black%' AND species LIKE 'swan' | ix_species_colour | species >= 'swan' AND species <= 'swan'                        | colour like 'black%' AND species like 'swan' |
| colour LIKE 'black%' AND species = 'swan'    | ix_colour_species | (colour,species) >= ('black', 'swan')) AND colour < 'blacL'    | colour LIKE 'black%' AND species = 'swan'    |
| colour LIKE 'black%' AND species = 'swan'    | ix_species_colour | species = 'swan' AND (colour >= 'black' and colour <  'blacL') | colour like 'black%'                         |
+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+


Promoting MySQL slave with unknown replication delay

Master + Slave?  Not Dual Master?

With Dual Master, each server is the Master and Slave to the other.  In this case either can receive writes, but you should write to only one.  More pertinent, failover and failback is readily available.

With Master + Slave, you have not prepared for the failback because things like log_bin and log_slave_updates are not turned, nor is the Master listening to the Slave.

I'm afraid your only option (now) is to stop the Slave (which you promoted to Master), copy the data, build a Slave to it (presumably on the original Master's server).  That is, you lost the Master, and whatever data is on it is useless.  It's like initially adding a Slave to a running system.


MySQL Session sql_mode vs Global sql_mode

"Session" variables apply to the connection.  (Usually, there is exactly one connection per client.)  Two different clients (or different connections in one client) may have different values in the variables.

"Global" variables are defaults that are copied into "session" when you connect.  Then you, or your client code, or the 3rd party application, or whatever, could change the variables.

Note:  Some global variables are really global -- the "session" value always tracks the global copy.

If you display the session value from the same connection, then that is what the connection is using.  If you make a separate connection to read the value, then what you see may not be what is in effect.

There are hundreds of 3rd party packages out there; who knows what each of them does.  Furthermore, things change from version to version of that package.

sql_mode can be changed in a session.

In your example, something in the connection added STRICT_TRANS_TABLES to SQL_MODE.  And it should be in effect for the connection until it is changed again.


If your Servers get out of sync they play back the events from the binlog, so as long you have all statements writen into your binlog, the master/slave is able to catch up. I had to catch up 12days without any problems, but in a cluster this is not what you want to happen.
And be careful when using "unsafe" statements, this are statements which the server can't reproduce on your master/slave setup.

Mysql Article on safe/unsafe statements - http://dev.mysql.com/doc/refman/5.1/en/replication-rbr-safe-unsafe.html


SSMS cannot connect remote server by IP through Windows authentication

So it turns out the timeout is actually a timeout...
After I expend the connection timeout to 90 sec it connected after about a minute.

Infrastructure guys say this might since it's trying to resolve the machine name or something? (I'm not really familiar with network and setting stuff).
I will update this answer if they figure out any way to speed up the process.


MySQL Computed Column counting other rows with attributes relative to this one

The problem
https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html says:

Generated column expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.
...
Subqueries, parameters, variables, stored functions, and user-defined functions are not permitted.
...

Your expression includes a subquery.  Note that the error message pointed directly to the subquery:  "... near 'select ..."
Generated columns are rather limited (for good reasons).
Suggestion 1
May I suggest that you do the computation when you are fetching data from the table.  Consider writing a VIEW or Stored routine to hide it from the caller.
Suggestion 2
Perhaps this comes closer to what you are looking for?
SELECT  b.*,
    (
        SELECT  count(*)
            from  flight a
            where  a.FlightCarrier = b.FlightCarrier
              and  a.DestinationAirport = b.OriginAirport
              and  a.ScheduledArrivalTimeUTC < b.ScheduledDepartureTimeUTC
              and  a.ScheduledArrivalTimeUTC >= (b.ScheduledDepartureTimeUTC - (60 * 60 * 24))
              and  a.DelayedArrival = 1
    )
    FROM  flight AS b;

It won't be efficient due to the correlated subquery, but we can work on that.  First, verify that it does what you need.
Suggestion 3
Better would be to have a column for that info, then do the computation separately as you are storing the b information.
INSERT INTO flight ( ..., DelayedCarrierArrivals24H)
    SELECT ...,    -- the values from your source
        ( SELECT ... ) AS DelayedCarrierArrivals24H;


The system provided sp_helpconstraint should do the trick.

If you want to write your own code then I'd suggest you take a look at the source code for sp_helpconstraint, see where/how it derives the constraint data, then write your own query(s) to generate the desired result set.

To find the source code for sp_helpconstraint you can either look in the installmaster script, or run exec sybsystemprocs..sp_helptext sp_helpconstraint,null,null,showsql.