Insert results from query, update old existing row if query doesn't still return same value

Something like this maybe?
with daily_query as (
  .... this is your current query ...
), upsert as (
  -- covers first and third requirement
  insert into the_table (....)
  select ...
  from daily_query
  on conflict (pk_column) do nothing
)
-- this sets those rows to in active that exist in the target table
-- but are not in the initial query
update the_table t
  set active = false
where not exists (select *
                  from daily_query q
                  where q.pk_column = t.pk_column);


INDEX(result, event_type, event_timestamp) obviates the need for INDEX(result) and INDEX(result, event_type).

Blindly using (255) hurts indexes and queries.  Trim back to realistic limits.

Splitting up the table the way you suggested helps nothing and hurts most queries.  In particular, then you would not be able to effectively use the multiple indexes, nor would you be able to use a 'composite' index (since it would involve more than one table).  On the other hand, if your 'log' gets to be too huge, then such 'normalization' will drastically shrink the disk footprint.  This, itself, has some positive impact on performance.

Do not normalize 'continuous' values such as timestamp.  This severely hurts performance because it makes it impractical to have an index on a "range" value.

Don't use LEFT unless you need the 'right' table to be optional.  LEFT sometimes implies that the 'left' table must be scanned first.  In your example, that would lead to a full scan of events.

If you change LEFT JOIN to JOIN (in the last example), then the Optimizer would pick among the tables to decide which one to start with.  This would be equivalent (but slower) than the original case of single-column indexes on the relevant columns.

"Low cardinality" columns (status and result) are virtually useless to index by themselves.  They can be effective when the first column of a 'composite' index.

Most tables have a limited number of queries that will realistically be applied.  If you are saying that your table begs for lots of different queries, then my advice is as follows:

Monitor what queries people want.  Keep track of the typical combinations of columns.
Implement a few 2- and 3-column indexes.
Be sure to have the first column(s) in the index be column(s) that are tested (in WHERE) with =.  If there is also a "range" (as in your example with timestamp), then put it last.  (Punt on multiple range tests.)
Remember that the order of ANDing things in WHERE does not matter, but the order of columns in an INDEX does.
More on creating optimal indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

MySQL does not implement "bitmap" indexes; they are rarely worth the effort.  MySQL does implement "index merge intersect" (for ANDs) which is a clumsy way to simulate a composite index.  "index merge union" (for ORs) is sometimes handy for OR; but UNION is likely to be as good.

Yours seems like a "Data Warehouse" application.  The best speedup for such is to build and maintain Summary tables.  For your one example, a summary of daily counts broken down by result and event_type would be much smaller and much faster to query.  (10x speedup is quite possible.)  Furthermore, it is practical to have different indexes on the Summary table, thereby somewhat breaking the log jam you currently have.  (You would SUM the subtotals to get the total COUNT.)


You can use the DBMS_Scheduler

DBMS_SCHEDULER.create_program
(
program_name => 'testScript',
program_type => 'EXECUTABLE',
program_action => '/home/someone/scripts/script.sh',
number_of_arguments => 0,
enabled => TRUE,
comments => 'Just a Test'
);
end;
/

and a little hosekeeping:

exec DBMS_SCHEDULER.drop_program(PROGRAM_NAME => 'testScript');


If you actually plan to query these attributes separately (such as "Find all form elements with height = 20 and colour = #FF3377") then it is probably better to store them in some kind of queryable attributes table. If you are only plan to use a set of attributes as a whole, and never query inside it, never update/insert/delete individual attributes for specific form elements, then serializing the array and storing it as a BLOB (or something) might not be so bad, as it would keep your design simpler.


Restore Verify Only option vs Manual Restore

Is this enough to be confident or should we still be manually restoring these backups to a test server and running tests?

To be 100% confident, a restore of database is required. You need a seperate server to do the restores. Since you are using Ols's backup solution, the entire restore and verify process can be automated using dbatools - Restore-DbaDatabase esp specifying parameter MaintenanceSolutionBackup

e.g. Below powershell script Scans all the backup files in \\server2\backups$ stored in an Ola Hallengreen style folder structure,
filters them and restores the database to the c:\restores folder on server1\instance1

  Restore-DbaDatabase -SqlServer server1\instance1 -Path \server2\backups\$ -MaintenanceSolutionBackup -DestinationDataDirectory c:\restores\

Also refer to my answer : Understanding the impact/risk of turning off “verify backup integrity” on SQL backup


You need to explicitly create a statistics object called id

CREATE PROCEDURE dbo.Demo
AS
BEGIN
SET NOCOUNT ON
-- Declare table variable
CREATE TABLE #temp_table (ID INT)
DECLARE @I INT = 0

CREATE STATISTICS id ON #temp_table (ID)

-- Insert 10K rows
    WHILE @I < 100
    BEGIN
        INSERT INTO #temp_table VALUES (@I)

        SET @I=@I+1
    END

-- Display all rows and output execution plan (now the EstimateRow is just fine!)
SELECT * FROM #temp_table

-- Is the object there
SELECT OBJECT_ID('tempdb..#temp_table')

-- How about statistics
DBCC SHOW_STATISTICS ('tempdb..#temp_table', 'id')
END;

EXEC dbo.Demo

When left to its own devices, the statistics objects created by the system have odd names like _WA_Sys_00000002_5F141958

Just note that when you create the statistics matters -- you'll need to move the CREATE STATISTICS command to after you populate the temp table with data if you want it to show anything, or possibly change your query to need to update stats, like SELECT * FROM #temp_table WHERE ID > 0.


why use PostgreSQL clusterdb instruction

The advice to cluster all tables to gain performance is wrong, ignore it.
What benefits from clustering a table (that is, rewriting the table in the order of one of its indexes) are index scans on that index that select many rows. Other than that, clustering is just unnecessary pain.


The SCOPE_IDENTITY function is not available in Sybase ASE as it is specific to Microsoft Sql Server. If you want to obtain the last identity value that was generated you can use the @@IDENTITY global variable like so:

insert into sales_daily (syb_identity, stor_id)
     values (102, "1349")
select @@identity

You can find the documentation for the @@IDENTITY global variable at SyBooks Online.


A database in 3NF is in 2NF also.  A simple mnemonic is:

The key
The whole key
and nothing but the key
So help me Codd

2NF states that a table contains no fields that are logically a function of a part of the primary key.  3NF states that a table contains no fields that are logically a function of any field of the table but the 'whole' key.  3NF can be viewed as a specialisation of 2NF.


Some more info from SSMSBoost developer.
As soon as you mentioned my project, I allow myself to write some words about the project. The main reason I have written the tool was that I have missed productivity features, concerning T-SQL Development and DBA - oriented tasks.
For example:

frequent connection switching between different servers
writing 1000 times a day "select * from "...
export grid results to excel/xml
keeping a list of favorite connections
having a warning from SSMS when connecting to live-environment

all these and some other daily operations are adressed in SSMSBoost and have been constantly improved. Currently I release new version every 30-40 days. Over last 3 versions I have also got a lot of positive/constructive user feedback and lot of features were improved. Your proposal to save every executed query will be also soon implemented. As I wrote - if you want to have something special write me an e-mail (support@ssmsboost.com)


Does restoring a database overwrite transaction logs?

During a Restore of an existing database, the transaction log will be completely overwritten. If data you needed to retrieve was written after the last known good backup (Full or Transaction) and you've already restored the database then the log is overwritten and the data is lost from an SQL Server perspective.

If a Server or SAN backup is taken, it could be possible to recover a copy of your original .mdf and .ldf file from here although this is generally frowned upon
For real recovery you should only use native SQL Server backups but if this is the only option to recover a production database, then you can't be faulted for that but it is not a good solution in its own right. But I would attach as a separate database and run CHECKDBs on it to make sure there is no corruption first.


SSRS - Data missing when running report from server

So for anyone who is interested, I figured out this was a permissions issue. As this is a report, it runs under a limited permissions login. I'd granted that login read permissions to Master and View Server State, which I thought would be enough, and sure enough, the report loads, but it seems like for some reason, if you don't have View Any Definition, your results may not be complete. I suspect (though I don't really have time to prove it) that each visible AG had at least one DB with some sort of relevant permissions, bringing it into view, but anyway, View Any Definition fixed it :) thanks for all the suggestions though


I write a bash script to perform the backups (RMAN, datapump and also xtrabackup and dump for MySQL in a similar way) and then call that from cron.  That way you can handle the email as you want.

So a simple crontab line is like this

00 18 * * 0-6 /dba/bin/rman.sh > /tmp/dba_rman_SID.log 2>&1

Then everything is done in the script.

So define some common variables including RMAN e.g.

export RMAN="${ORACLE_HOME}/bin/rman target /"
...
etc etc

Then import a list of emails you want (one per line) from a local file

declare -a EMAILS
readarray -t EMAILS < .emails.dat

Then define a couple of handy procedures that you can use later on for error handling and email

abort_with_error()
{
        echo
        echo "Error :" $1
        echo
        echo "Error Code :" ${ERR}
        echo

        export SUBJECT="Error - ${SUBJECT}"

        send_email

        exit 1
}

send_email()
{
        echo
        echo "Sending logfile via email to the following :"
        echo

        for EMAIL in ${EMAILS[@]}
        do
                echo ${EMAIL}
                echo
        done

        for EMAIL in ${EMAILS[@]}
        do
                case $EMAIL in
                        \#*) ;;
                        *) ${MAILX} -s "${SUBJECT}" ${EMAIL} < ${LOG_FILE} ;;
                esac
        done
}

now the main process which is wrapped up to output it all to a log file.  This is a simple example but shows the basic method.

{

    //add what you want your script to do here - can be many things for example:

        ${RMAN}<<EOF
        delete noprompt force obsolete;
        exit;
EOF

        ERR=$?
        if [ ${ERR} -ne 0 ];
        then
                abort_with_error "The RMAN delete obsolete backup step failed"
        fi

        echo "${JOB_NAME} ended at : $(date)"

        send_email

} > ${LOG_FILE} 2>&1

Adjust the abort_with_error and send_email procedures to suit your environment and needs.

This separates the scheduler from the process so no need to change cron if you update your backup process or get a new team member who needs the emails.

This way you get an email if it works and if it doesn't but with Error in the subject so an simple email rule can hide away non-interesting successful backups and highlight failures and a simple count up each morning shows if something didn't email and needs a look.  Since the log is held on the server you can find it there (add something to the job to clean the old ones down after a couple of weeks).

Obviously there are a bunch of ways you can implement something like this and you'll need to vary it to suit (and I've cut out * a lot * of stuff from the script for clarity).


C stands for eventual consistent. Correct ?

Consistency in the CAP theorem is referring to strong consistency where every read receives the most recent write or an error. By default MongoDB drivers direct all reads & writes to the primary of a replica set, which is strongly consistent.

The CAP theorem asserts that a distributed system must choose between consistency and availability in the event of a network partition. MongoDB's replica set approach uses a single primary for write consistency (CP), while Cassandra's replication strategy favours write availability (AP). Strong consistency is not possible with a network partition because there could be a conflict if both sides of the partition update the same data. To maintain write availability AP database systems need a solution for conflict resolution, which is a separate consideration from eventual consistency.

However, CAP is a simplification of real-world behaviour: MongoDB and Cassandra both have tunable levels of consistency for reads and writes. For example: MongoDB has write concerns to determine the level of acknowledgement required for write operations, read preferences for routing requests to members of a replica set, and read concerns to control the recency, consistency, and isolation properties of data read from replica set and sharded deployments.

Eric Brewer, author of the CAP Theorem, revisited this in 2012 with a more nuanced take: CAP Twelve Years Later: How the "Rules" Have Changed.

  Till master is re-elected, write requests need to wait and system is not available

There are no writes without a primary, but replica sets still have read availability. MongoDB 3.6 added a Retryable Writes feature which helps applications better handle replica set elections and transient network errors.

  Once the previous node(node crashed in step 2) comes back, writes pending from that node are written back to slaves.

If the primary in a MongoDB replica set becomes unavailable, the remaining members of the replica set will elect a new primary if there is an eligible secondary and a quorum of voting members. In your example, the voting majority would be 2/3 members of your replica set. Any writes accepted by a former primary that were not written to a majority of replica set members will be rolled back (saved to disk) so the former primary resumes syncing from a state consistent with the history of the current primary.


Invalid Column Name Error after ALTER and UPDATE

You cannot add the column and use it in the same batch.

So if you execute your code in SSMS just add GO between your commands like this:

    ALTER TABLE Intraday_Forecast_temp
        ADD IssueTimeUTC SMALLDATETIME

go ----------------------------<<<<<-----------------------------

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
        WHERE DateTimeUTC<=CAST(@tempdate AS SMALLDATETIME)

If it's a part of stored procedure, wrap your update in EXEC:

exec ('UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)')

UPDATE

In your case you can use wrapping in exec this way:

declare @tempdate date = getdate(); -- or whatever it should be
declare @sql nvarchar(4000)= N'UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)';

exec sp_executesql @sql, N'@tempdate date', @tempdate = @tempdate;


Storing shipping details in orders table

I would only introduce a shipping details table if you have a high number of orders going to the same place.  This would save on space as the address wouldn't be repeated multiple times.

But you might want to do something like that if you have users with multiple different shipping destinations that they use.


SELECT and UPDATE in a single query while updating selective fields of duplicate records only

Since there is a unique constraint/index on number, you can use ON DUPLICATE KEY UPDATE syntax - which can be combined with INSERT ... VALUES so all the rows are inserted/updated in one statement. The additional requirement that new null values do not update existing ones can be satisfied using COALESCE() function. Example:

INSERT INTO Contacts
    (number, name, city)
VALUES
    (1234567892, 'Earl Grey', 'Salem'),
    (3333333333, 'Rich Malfoy', 'Sacramento'),
    (7656453248, 'Bill Gates', 'New York'),
    (5555555555, 'James McGill', 'Boston'),
    (6666666666, NULL, 'Baton Rouge')
ON DUPLICATE KEY UPDATE
    name = COALESCE(VALUES(name), name),
    city = COALESCE(VALUES(city), city) ;

Test at SQLfiddle


Sybase internal tables for ORM generation

Those tables are still in ASE 15.5

syscolumns

syscomments

sysobjects

systypes


Your root question is subjective and it's a little hard to fully envision what your data looks like without some examples, but it sounds like a Relational Database Management System (RDBMS) could work for you based on the type of querying you'll be doing.
If you solely want something free than PostgreSQL. If you want something with the most features built into it out of the box and are ok with paying then Microsoft SQL Server. Both can house your data, have Views (and other types of objects) which can save your queries (predetermined formulas) for conversion, and support importing data from CSVs and other files if needed as well (though I recommend you do as much of your data gathering and importing into the database via a non-database language like Python).
Your additional requirements for creating dashboards and other ad-hoc querying will be supported by either of these databases as well.


Can't do any changes to a job in SQL 2000

It sounds like you might be facing this issue ...

http://support.microsoft.com/kb/281642

  After the Windows server name been changed, when trying to update or
  delete the jobs previously created in a SQL Server 2000 instance, you
  may receive the following error message:

  Error 14274: Cannot add, update, or delete a job (or its steps or
  schedules) that originated from an MSX server. The job was not saved.

Here are the suggested steps to workaround the issue ...

  The best way to handle this problem after the rename process is to
  follow these steps:

  Rename the server back to the original name.
  Script out all of the jobs and then delete them.
  Rename the server to the new name.
  Add back the jobs by running the script generated from step 2.

Additionally you can find more discussion/suggestions here ...

http://blog.sqlauthority.com/2006/12/20/sql-server-fix-error-14274-cannot-add-update-or-delete-a-job-or-its-steps-or-schedules-that-originated-from-an-msx-server-the-job-was-not-saved/


In SQL Server 2005 how would I determine the minimal permissions a user requires?

If you want to cut the noise out of a Profiler trace, ClearTrace is invaluable. ClearTrace “normalizes” queries, removing parameters such that:

SELECT x FROM y WHERE z = 1
SELECT x FROM y WHERE z = 2

are both reported as

SELECT x FROM y WHERE z = #

If you capture sufficient activity from the database and order the ClearTrace report by execution count, you’ll have a better idea of the common/typical query patterns and from this the permissions required. You can then investigate the non-typical activity and establish if these require a different set of permissions and/or are generated by a subset of the user base.


Overwriting MySQL database to only store 1 month of data

A short rotation script/event could look like:

drop table if exists table2;
rename table table1 to table2;
create table table1 like table2;


You didn't put a PostgreSQL version tag, but there are functions added around 9.4 that will help with this: you can use jsonb_object_agg(), jsonb_build_object() and jsonb_build_array() (or their equivalent json_ functions if you don't want jsonb).

See documentation here: https://www.postgresql.org/docs/10/functions-json.html

As a side note, you should normalize that table if it really is a table and not a view.  Specifically, have a look at second normal form.


SQL Server: Create default stored procedure as and when a new database is created

Create the procedure in the system database model.

From model Database

  The model database is used as the template for all databases created
  on an instance of SQL Server.


This might happen due to exhausted virtual memory on your server. OOM killer kills processes to free some memory in this case.

You can try to collect some information next time when it happens again with db2fodc -detect functionality using either predefined script or your own.

See the Threshold collection examples at the db2fodc - DB2 first occurrence data collection command link.


Excluding rows from a query on a combination of mulitple conditions

To be precise, to ...

  remove rows where query_id = 10 and flag = 'IGNORED'

... you need something like this:

SELECT *
FROM  ...  -- you are hiding a more complex query here!
WHERE (Flag.Query_id = 10 and Flag.query_flag = 'IGNORED') IS NOT TRUE;

In a WHERE clause, only TRUE qualifies. Rows with FALSE or NULL are excluded.

What you display:

...
WHERE NOT (Flag.Query_id = 10 and Flag.query_flag = 'IGNORED');

... would also exclude rows with NULL values in Flag.Query_id or Flag.query_flag - not exactly what you asked for. Even if the columns are defined NOT NULL, NULL values can be introduced with an OUTER JOIN or similar in your undisclosed query.

(The sample rows you display are not affected, since there are no NULL values.)


What are the best practices for running SQL Server in a Virtual Machine?

As with physical machines IO is king. There will be a noticeable IO hit from virtualisation (how much of a hit depends on the technology you choose), so make sure that you do nothing that can exacerbate this and harm IO performance further.

Always used fixed size, not dynamic virtual disks, and ensure that the underlying drives/arrays are sufficient. Dynamic vdisks are less performant than fixed size ones.
If the data in the VMs is not mission critical, turn on the VM solutions ability to cache writes (effectively lying to the guest OS about when things are physically written to disk) for extra performance. This is useful for a development and testing machine where the data is easily replaced, or read-only replicas local to the master that can be recreated if they experience any trouble, but it is recommended that such options stay off for production and backup systems as they increase the chance of data loss (or worse, corruption) if the machine powers off unexpectedly.
Ensure that each VM has enough RAM dedicated to it such that the normal working set of each database fits into memory with a fair amount of room to spare to avoid unnecessary IO. Also, ensure that the hypervisor will not page out the VMs RAM at any time (for instance most VMWare products can do this, which allows you to run a larger set of VMs than you otherwise could on the same hardware, but the performance degradation can be massive - there is an option to tune or turn off this behaviour).
If the host uses RAID5 arrays, consider moving to RAID10. This will reduce the space available but remove the write performance problems that can be apparent with RAID5 (or 6).
For VMs that are likely to see a lot of IO activity, consider giving them their own drive or array, or only have them share their drive(s)/array(s) with data that sees low activity.

Of course if your entire DB (and everything else the VM is running) fits into the RAM allocated to the VM and it sees very little write activity, IO performance can be much less of an issue.

Edit: a few more points:

If using virtual disks make sure that the best controller type is used for performance. Some virtualisation solutions offer several virtual controller types for better guest compatibility and you might find some less efficient than others due to protocol limitations in the design they are emulating and limitations in the guest OS's drivers.
If your virtualisation solution offers specific drivers for the virtual drive controller, make sure your guest OS is using them - these can speed up bulk IO by reducing the number of memory-to-memory copies and host<->hypervisor<->guest context switches involved in some operations.
The two points above apply if you are using network storage such as simple SMB shares or iSCSI too: make sure that you are using the most efficient virtual network controller on offer and that your guest OS is using the recommended driver for it, otherwise both latency and bandwidth may be more limited than needs be by the virtualisation layer.


SQL Server 2012 running background task that fills memory

SQL Server does not use up memory without a workload running. I.E. an instance of SQL Server will consume the bare minimum it requires to keep the engine alive, listen for connections and handle background tasks (e.g. resource management). That's usually less than 100MB. If you have a min memory setting then it'll acquire that minimum IF there's demand because of workload and then sit there; won't release beyond the minimum even after the workload is done.

If your SQL Server process (sqlservr.exe in task manager or process monitor) is consuming lots of memory, something is going on. Check what's going on in the server using SELECT * FROM sys.dm_exec_sessions and look at the login_name column to see who's connected. That's a start to identify potential unknown workloads. From there, the other columns will provide information on how much resources it's using. It could be a real user, machine or even some old SQL Agent job gone wild.


Strategies for organising SQL Server with large amount of RAM

Even though you have memory to spare at the moment (70GB database vs 128GB server) I would still configure the maximum server memory as per latest guidance.

  ...reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed
  from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB
  RAM.

That equates to 110GB on a 128GB server. Reasoning being that the data warehouse will likely continue to grow and could eventually grab more than you want it too, so put the correct limit in now. It will get forgotten about otherwise.

When your data volume gets beyond the 110GB limit, pay closer attention to the servers free memory. If you consistently have additional GB to spare you may consider raising the limit by a few GB.

Other than that your only other fine tuning options are with trace flags, which should be treated with the caution they deserve. Test, test and test some more.

The three candidates I can think of that would make sense are:

TF834 - Large page allocations
TF2301 - Query Processor Modelling Extensions (typically good for OLAP, potentially problematic for OLTP)
TF4199 - Query Optimiser Changes

As has been mentioned in an answer previously, Thomas Kejser has referred to TF834 as SQL Servers only "go faster" switch. TF2301 enables optimisations which are beneficial to DW type queries. TF4199 requires reading the linked document, so do.


It has no WHERE clause so it must process and aggregate all 40 million rows. SQL Server will not take advantage of the index order and skip scan ahead to the next IdTag once it has found the MAX for the current group but will continue processing the other rows in that group. Each group has an average of about 30,000 rows.

As you have another table that lists the 1,386 distinct IdTag types then you could try the following instead.

SELECT D.IdTag,
       V.PCTimeStamp,
       V.Now,
       datediff(SECOND, V.PCTimeStamp, V.Now) AS DELAY
FROM   DescriptionTagsOPC D
       CROSS APPLY (SELECT TOP 1 *,
                                 getdate() AS Now
                    FROM   ValuesTagsOPC V
                    WHERE  D.IdTag = V.IdTag
                    ORDER  BY PCTimeStamp DESC) V

To replace the scan of 40 million rows with  1,386 seeks.

If that table was not available then a recursive CTE could be used to achieve similar results.

WITH    RecursiveCTE
AS      (
        SELECT TOP 1 IdTag, PCTimeStamp
        FROM ValuesTagsOPC
        ORDER BY IdTag DESC, PCTimeStamp DESC
        UNION   ALL
        SELECT  R.IdTag, R.PCTimeStamp
        FROM    (
                SELECT  V.*,
                        rn = ROW_NUMBER() OVER (ORDER BY V.IdTag DESC, V.PCTimeStamp DESC)
                FROM    ValuesTagsOPC V
                JOIN    RecursiveCTE R
                        ON  V.IdTag < R.IdTag
                ) R
        WHERE   R.rn = 1
        )
SELECT  IdTag,
        PCTimeStamp,
        getdate()                                 AS NOW,
        datediff(SECOND, PCTimeStamp, getdate()) AS DELAY
FROM    RecursiveCTE
OPTION  (MAXRECURSION 0);


You can use the Power of Default Trace to find out the culprit.

provided the default trace files are not rolled over

-- find out the default trace location

SELECT *
FROM fn_trace_getinfo(default);
GO

   --- check the events

SELECT
    TextData,
    HostName,
    ApplicationName,
    LoginName,
    StartTime
FROM
[fn_trace_gettable]('Location of Tracefile.trc', DEFAULT)
WHERE TextData LIKE '%SHRINKFILE%';


SQL Server restore database file access error

Double check that the paths you are trying to restore to exist on the D drive. SQL Server will not create the directory path if it doesn't exist.

If you haven't already, run the restore command with the verify only option MSDN.

If that is successful here are a few additional things you can try to isolate why it is failing

If you have the space on the SQL Server that created the backup, try to restore the backup there under a different file path and different database name (again make sure the temporary restore path(s) exist.)  This will confirm if the backup is valid and can be read by SQL Server.  Be very careful here to change the database name to something different and to give the file names a different name to not cause a problem with the current database.  (Example: change MyDB to Restored_MyDB, MyDB.mdf to Restored_MyDB.mdf and MyDB.ldf to Restored_MyDB.ldf in the restore statement.)
Confirm the account that you are using to do the restore,if it's not the same, has the same permissions that the account that did the backup does.
Copy the backup locally to the machine you wish to restore it to and restore from the local drives.
Change the file extension from dmp to bak.


Is it generally true that any PL can be both set up to be trusted and untrusted?

No. Any PL can be marked as trusted or untrusted, but if you park a PL that doesn't support running trusted as trusted, it'll either notice that and ERROR or it'll silently run without sandboxing as if it were untrusted.

Don't modify catalogs directly.

  If not, what are the specific languages that can be set up to be trusted or are trusted.

Just these,

PL/PgSQL runs as trusted.
PL/Perl can run as trusted or untrusted.
PL/TCL can run as trusted or untrusted.

That's it for the built-in PLs. Notably, PL/Python cannot run as trusted due to fundamental design limitations with the CPython interpreter that make creation of a restricted execution environment impossible. It's possible to create a "trusted-ish" Python runtime where an attacker is protected from simple mistakes but can break out of the sandbox with effort, but PL/Python does not support this, nor is any patch to enable it likely to be accepted.

There are also a bunch of out-of-tree procedural languages, some of which support trusted mode. Notably, PL/V8 (JavaScript), PL/Lua and PL/Java support trusted mode. There are probably others.

(On a side note, I really would love to see adoption of in-core a trusted-enabled PL/Javascript as a default in-core PL, or even better, PL/C# for .NET via Mono or .NET Core).


Microsoft® SQL Server® 2012 Service Pack 2 (SP2) Express

As you said in comments you'd like a SQL Server + a SSMS.
I would suggest not using a SQL2012, but going for a more recent version.

Using this this link you can download the SQL Server itself.
The 2 free versions are express and Developer. More information about the differences between versions can be found here : SQL Editions and SQL Pricing

Developer you should use if you're just trying to develop some things at home, NOT COMMERCIALLY. It has all the features enterprise has.
Express will give you a basic sql server with some limitations.

Afterwards you can get SSMS using this link.
SSMS is free, and its just used to work on your SQL Server


Consistent Database Export of Oracle Database

Data Pump command-line arguments are partially backward-compatible with Export and Import utilities, and CONSISTENT maps to FLASHBACK_TIME.

FLASHBACK_SCN is used to specify system change number until all changes from the source database are exported. FLASHBACK_TIME allows you to specify change time. When you specify CONSISTENT, Data Pump Export determines the current time and uses that value for FLASHBACK_TIME implicitly.

For more info read Oracle Database Utilities.


Unexpected results while using HAVING without GROUP BY

This is not valid SQL - even though MariaDB and MySQL (until version 5.6) allow it:

having budget > avg(budget)

So, if you get unexpected results, don't worry much. Just do not use invalid SQL syntax ;) If you are not sure what syntax is valid and what not, you can add ONLY_FULL_GROUP_BY to the SQL mode setting and you'll get errors instead of unexpected / inconsistent results.

What is actually happening in the query:

select *
from department
having budget > avg(budget);

Whenever there is an aggregate function (SUM(), COUNT(), AVG()) either in the SELECT list or in HAVING condition, a GROUP BY the whole set is implied. So the query is equivalent to:

select dept_name, building, budget
from department
group by ()
having budget > avg(budget);

This means the whole set has to be collapsed in a single row and only aggregate expressions should be allowed, not columns. So the whole SELECT list is invalid (all 3 columns) and the budget in the HAVING condition. However, MySQL and MariaDB have allowed this since very early versions, for some efficiency optimization (in certain cases where it would not create inconsistent results).

In the specific case (or similar ones), that a dept_name, a building and a budget have to de displayed in the SELECT result, an arbitrary row is chosen and displayed. In more complicated cases, you can even see results from different rows, showing a department A with the budget from department B !

The same (arbitrary) budget value is used in the HAVING filter, giving the weird results you noticed.

A few more details can be found in MySQL's documentation:

5.6 (old behaviour): MySQL Handling of GROUP BY
8.0 (current behaviour): MySQL Handling of GROUP BY

You first query is the right way to do it:

select *
from department
where budget > (select avg(budget) from department);

Another way is to use window functions (available in both MariaDB and latest MySQL):

select *
from
  (
    select *,
           avg(budget) over () as avg_budget
    from department
  ) as a
where budget > avg_budget ;


There is no requirement for sysadmin to create a SQL Server Linked Server .

Assigning  the permissions ,ALTER ANY LINKED SERVER and ALTER ANY LOGIN to a login account  , allows the actions to complete.

  Create a Linked Server with master.dbo.sp_addlinkedserver.

  ALTER ANY LINKED SERVER permissions are required

Drop a Linked Server with master.dbo.sp_dropserver.
ALTER ANY LINKED SERVER permissions are required

Create the mapping of a local login with a remote server with master.dbo.sp_addlinkedsrvlogin

Code examples: Assigning permissions

USE master;
GRANT ALTER ANY LINKED SERVER TO MYLOGINACCT;
GO
USE master;
GRANT ALTER ANY LOGIN SERVER TO MYLOGINACCT;

--An example of adding a Linked Server
EXEC sp_addlinkedserver
@server='Server1',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='Server1\instance1'

---An example of dropping a Linked Server
EXEC master.dbo.sp_dropserver ‘Server1’

Link: http://www.sqlserver-dba.com/2011/05/sql-server-linked-servers-and-user-permissions.html


Using max for each sub group does not seem to work.

You have two right parenthesis more than left ones and a missing alias:

Select *
from wh_acct a                                 -- alias added
where a.rundate =
      (select max(a2.rundate)
       from wh_acct a2                         -- parenthesis removed
       WHERE a2.ORGA = a.ORGA)
UNION
Select *
from wh_acct a                                 -- alias added
where a.rundate =
      (select max(a2.rundate)
       from wh_acct a2                         -- parenthesis removed
       WHERE a2.PERS = a.PERS) ;

You could also write the query with OR instead of UNION:

Select *
From wh_acct a
Where a.rundate =
      (Select max(a2.rundate)
       From wh_acct a2
       Where a2.ORGA = a.ORGA)
   Or a.rundate =
      (Select max(a2.rundate)
       From wh_acct a2
       Where a2.PERS = a.PERS) ;

Or using window functions:

WITH cte AS
    ( SELECT ACCT_NBR, MAIL_TY, ORGA, PERS, RUN_DATE,
             MAX(rundate) OVER (PARTITION BY ORGA) AS max_rundate_orga,
             MAX(rundate) OVER (PARTITION BY PERS) AS max_rundate_pers
      FROM wh_acct
    )
SELECT ACCT_NBR, MAIL_TY, ORGA, PERS, RUN_DATE
FROM cte
WHERE rundate = max_rundate_orga
   OR rundate = max_rundate_pers ;

If, as your sample shows, all rows have NULL in orga where there is a value in pers and vice versa, you can change the UNION to UNION ALL to improve efficiency as there would be no need to check the subresults for duplicates (which is what UNION does). The window-cte version could also be rewritten as a union (ALL) of 2 subqueries.

Indexes on (orga, run_date) and (pers, run_date) would help, too.


Another alternative is to use STUFF function.

IF OBJECT_ID('tempdb..#ExampleValues') IS NOT NULL
    DROP TABLE #ExampleValues

CREATE TABLE #ExampleValues (
    ExampleValue VARCHAR(100),
    UpdatedValue VARCHAR(100))

INSERT INTO #ExampleValues (ExampleValue)
VALUES
    ('112344353-0'),
    ('11443-8'),
    ('112344-9'),
    ('11234435348'),
    ('11234448712')

UPDATE T SET
    UpdatedValue = CASE
        WHEN T.ExampleValue NOT LIKE '%-%'  -- If the literal does not contain a hyphen
            THEN STUFF(
                T.ExampleValue,             -- Put in the literal
                LEN(T.ExampleValue),        -- Just before the last character
                0,                          -- While replacing 0 characters ("stuffing" in between)
                '-')                        -- Place a hypen!
        ELSE
            T.ExampleValue END
FROM
    #ExampleValues AS T

SELECT
    T.ExampleValue,
    T.UpdatedValue
FROM
    #ExampleValues AS T

Result:

ExampleValue    UpdatedValue
112344353-0     112344353-0
11443-8         11443-8
112344-9        112344-9
11234435348     1123443534-8
11234448712     1123444871-2


Importing Oracle database from dump where source db filesystem has changed

To respond to your comment, all is the default value of content keyword, so you can ignore this one.

Anyway, I have done some tests.
I don't know how is structured your old database, but from your question I note that you want to import only two tablespaces (TAB_ANC, IDX_ANC) and  with all object/schemas within them. If you want to do this, you can perform this kind of import:

impdp system/passwd@HISTO directory=MIG11G dumpfile=20131125_full_HISTO.expdp  parfile=imp_histo.par

The parameter file is this one:

include=schema:"in ('SCHEMA1', 'SCHEMA2', ... , 'SCHEMAn')"
include=tablespace:"in ('TAB_ANC', 'IDX_ANC')"
remap_datafile=/appli2/oradata05/HISTO/HISTO_tab_anc_01.dbf:/appli2/oracle/HISTO/oradata05/HISTO_tab_anc_01.dbf
remap_datafile=/appli2/oradata06/HISTO/HISTO_idx_anc_01.dbf:/appli2/oracle/HISTO/oradata06/HISTO/HISTO_idx_anc_01.dbf

As you can see, you need to include all schemas contained in those tablespaces (or you can also create them from scratch in the new database). To verify these schemas you can run a query like the following:

SELECT username, default_tablespace FROM dba_users;

Remapping datafiles is useful when you move databases between platforms that have different file naming conventions.

I think you can use remap_tablespace when you have already created (manually) all the required tablespace in the target database, but with different names.


I have designed  a database archiving process - which is a semi automated process.

Below are my thoughts :

Note that you have to develop your custom solution based on your organizational needs

Create a server level trigger to track DROP database activity and log that activity in a table. Now if you are talking about only backup files, then you can do the same without a trigger once the backup is finished to update the table with relevant info.
Below is the possible table structure with columns

ID => this will be identity column
TRIGGER_NAME => the name of trigger that got fired in response to the event
COMMENT => Friendly comment for the command that got issues e.g drop database was issued
EVENT_TYPE => the event type that occured e.g. DROP_DATABASE
OCCURED_DATE => the datetime when the event occured
SERVER_NAME => the host name
SERVER_INSTANCE_NAME => sql server instance name
PATCH_LEVEL => version info from serverproperty or you can just use @@version
LOGIN_NAME => Login that issued drop command
DATABASE_NAME => The database that was dropped
DATABASE_VERSION => If sql 2005, 2008, etc or you can use the compatibility level as well
COMMAND_TEXT => The command that was used for the event e.g. drop database test_drop
TYPE => I use this to differentiate between if the backup stored will be on DVD (if small) or TAPE. This is manual bit to be updated later.
TRACKING_NO => If a tracking number is assigned or a request number can be used e.g. JIRA request or HelpStar request or a TFS request number. This is manual bit to be updated later.
APPLICATION_VERISON => If your database is storing application specific version number e.g. BUILD_NUMBER or database schema version.
NOTIFIED => This will be default to 0 and will be updated to 1 once the DBA team is notified by an email. Once it is set to 1, the next time the job runs, it will just send out new records.
You can add more if needed !!

The key here is EVENTDATA() which has a lot of information.

Remember that triggers (whatsoever) cannot be used for backups. For backups, you have to tailor your own custom solution, but the idea remains the same.


It sounds from your comment on Marlon's answer like these are tables whose structure changes frequently. That could make the trigger solution unwieldy.

In that case, investigate the alternative of doing a Transactional Replication publication and excluding DELETE transactions from the publication. INSERT, UPDATE, and DDL changes would be replicated to your destination table. For SQL 2005, this is the optimal solution.

If you were to upgrade to SQL 2008, you could make use of Change Data Capture. That would be the most robust solution, and easier to maintain than the Replication method. But I know getting the approval/budget/time for such upgrades is not always easy. :)


Best High Availability Solution for SQL Server 2014

Always On Availability group will server your purpose for which you will need Enterprise Edition of SQL Server license. It will provide :

Automatic failover is available when the AG is configured with Synchronous-commit mode with automatic-failover.Failover Modes

Since AlwaysON has data separated for each instance that is part of the AG, the storage corruption for one of the node will leave you safe with the other node/nodes.

Readable secondary will help you in reporting purpose.
Active Secondary

The only disadvantage/Limitation of AG I would say is the need of Enterprise edition.


Its too simple if you have gone through the mysqldump manual

First run a mysql dump ignore the table you would like to skip

mysqldump -p -u root db_name --ignore-table=db_name.tbl_name > sql.sql

Then once more for your table

mysqldump -p -u root db_name tbl_name --where "id!=2" >> sql.sql

As a note, if you are giving something in --where, it applicable for all the tables your are using. It will be useful in case of multi-tenant db.


SQL Server using index scan instead of seek when updating a view that has an INSTEAD OF UPDATE trigger via a cursor

This looks like an oversight.

The optimizer cannot generate an apply-style indexed loops join when the update is performed using WHERE CURRENT OF and the target view (schema-bound or not) has a T-SQL instead-of update trigger, regardless of the number of rows, or any other considerations:

That shows an example with almost 20,000 rows in the table (copied from the AdventureWorks' Person table, as it happens).

The join predicate gets 'stuck' on the nested loops join operator itself, rather than being pushed into the inner side to produce a seek:

Since you cannot change the code, you should report this as a bug through your normal Microsoft Support channel. You may also report the bug on Connect, but the chances are much lower of getting a quick response or fix via that route.

Just for interest's sake, the plan you are after is possible using an API cursor positioned update (the most analogous operation internally):

DECLARE
    @cur integer,
    @scrollopt integer = 2 | 8192 | 32768 | 131072, -- DYNAMIC | AUTO_FETCH | CHECK_ACCEPTED_TYPES | DYNAMIC_ACCEPTABLE
    @ccopt integer = 2 | 32768 | 131072, -- SCROLL_LOCKS | CHECK_ACCEPTED_OPTS | SCROLL_LOCKS_ACCEPTABLE
    @rowcount integer = 1;

-- Open the cursor
EXECUTE sys.sp_cursoropen
    @cur OUTPUT,
    N'
    SELECT * FROM dbo.vPerson WHERE Id = 1;
    ',
    @scrollopt OUTPUT,
    @ccopt OUTPUT,
    @rowcount OUTPUT;

-- Request a positioned update
EXECUTE sys.sp_cursor
    @cur,
    1, -- UPDATE
    1, -- row number in buffer
    'dbo.vPerson', -- table (unambiguous in this case)
    'Name=''Banana'''; -- new value

-- Close
EXECUTE sys.sp_cursorclose -1;

The execution plan is:

Notice the index seek on Person (the predicate is not 'stuck'):

This is not a workaround for you since you cannot change the source query. There's no way to hint or plan guide your way around your issue; the optimizer simply cannot generate the seek plan you expect in your specific case. Attempting e.g. a FORCESEEK hint simply results in an error message saying the optimizer could not produce an execution plan.


How to move the ownership/copy a table from one user to another?

You can't change the owner of a table.

You can create a new table that is owned by NEW_USER, copy the data from the old table to the new table, drop the foreign key constraints that reference the old table, and create new foreign key constraints that reference the new table.  But that's realistically going to require a downtime window since the tables need to be static while this copy is going on.

There are various ways to create a new table that is a copy of the old table.  Personally, I'd use your favorite PL/SQL IDE (i.e. SQL Developer, TOAD, etc.) to generate the DDL for Old_User.Table_A and then manually edit the DDL to create New_User.Table_A.  You could also do an export & import with the FROMUSER and TOUSER parameters.  Or you can use the DBMS_METADATA package to get the DDL

SELECT dbms_metadata.get_ddl( 'TABLE', 'TABLE_A', 'OLD_USER' )
  FROM dual

and edit that.

Once you've created the new table, you can copy the data over

INSERT INTO new_user.table_a( <<list of columns>> )
  SELECT <<list of columns>>
    FROM old_user.table_a

Then, you'll need to go through each child table dropping the old foreign key constraint and creating the new constraint, i.e.

ALTER TABLE child_of_a
 DROP CONSTRAINT fk_constraint_name;

ALTER TABLE child_of_a
  ADD CONSTRAINT fk_constraint_name FOREIGN KEY (name_of_column_in_child)
      REFERENCES new_user.table_a( name_of_column_in_parent )

If you have enough child tables, you could potentially write a script that dynamically generates that DDL.

Once that's done, you can drop the old table and let the applications start modifying the data again.


I am getting error ORA-03113 at the time of startup database.

In your case, it seems that database can not pass from mounted to open state, for any reason.
Please check alert log and any generated file in your trace directories.
EDIT: I removed comment about ORA-00600 (I wrongly read your post 1st time...)


Execute a sql query inside IF() function

Learn about Stored Programs.  Write a Procedure that uses IF statements, not functions, to conditionally perform SQL statements.

Then invoke the Stored Procedure with a CALL statement.


Is LIMIT processed before or after retrieving data?

By suggestion of @RolandoMySQLDBA, I'm combining my comments into an answer.

So, in many cases, LIMIT clause enables the database server to do less work before delivering the result. MySQL docs explain such situations.

It is best to check queries with EXPLAIN to see the execution plan. If the execution plan is different with and without limit clause, then some optimizations were performed (query can be executed more efficiently because of limit by clause, or at least the optimizer thinks so).

For example, using the Sakila test database: a table film has a column named title which is indexed. It has 1000 rows. EXPLAIN for query

select * from film order by title

shows that the databases reads and sorts the entire table (this being cheaper than using the index on title and accessing table rows from index entries, since the whole table must be read). On the other hand, EXPLAIN for the same query with LIMIT clause,

select * from film order by title limit 10

shows that the database now decides to use index on title column; it only needs to read the first 10 index entries and retrieve rows that those entries point to. The rest of the index or table are never accessed. Certainly much cheaper than the first query, if you only need the first 10 rows.


Is it possible to configure Oracle's CONNECT THROUGH based on roles?

A.B.Cade and Phil gave the answer in the comments:

CONNECT THROUGH cannot be granted using a role.

As Phil said, creating a script to do this is trivial.  Be aware the database links do not work the same when used via proxy.

This situation does have risk, but given the requirements and limitations in Oracle, it is sometimes necessary.  You might consider allowing the script to accept two usernames.  The DBA can then run the script on demand for the particular client and support person combination that needs it.  By revoking the privilege when the support session is complete there will be less the potential for abuse.


SQL Server 2008R2 database migration to cloud: case-insensitive collation changed to case-sensitive

There is a lot of information missing from the question, such as:

What does "cloud" mean, exactly?

Microsoft Azure SQL Database
Microsoft Azure SQL Database Managed Instance
Microsoft Azure VM with SQL Server installed
Amazon (AWS) RDS
Amazon (AWS) VM with SQL Server installed?
something else

What is meant by "migration"?

Database was detached / attached, or backed-up / restored
Objects and data were moved via scripting out and applying at the destination, or directly ETLed
Data Migration Assistant
SSDT and BACPAC file
Azure Database Migration Service

What is meant by "database"?

An actual Database that resides in an Instance of SQL Server
An Instance of SQL Server that contains Databases

How does "crappy legacy code base" factor into this Collation issue? Or is it just venting / kvetching?

Generally speaking, there is no obvious / default answer since there is no single mechanism / approach for migrating data, nor is there a singular definition of "cloud" in this context. I would expect that some options (such as restoring from a .bak backup file) would keep the Database's default Collation intact, but that does not imply anything about the Instance-level Collation.

What this comes down to is: even without having more detailed information to go on, since there is a Collation difference at some level (Instance and/or Database), it would seem that the vendor that performed the migration was not careful to ensure that either a) the exact same Collation was being used if given a choice to set a new Collation, or b) that you were notified that there would be a change of Collation at the specific level(s) if there was no choice and it was going to be different than the current Collation(s).

I would definitely contact the vendor regarding this issue. Typically, if there is a default Collation that cannot be changed, it is only the Instance-level, and is case-insensitive:

Azure SQL Database Managed Instances use: SQL_Latin1_General_CP1_CI_AS (unfortunately)
Partially-Contained Databases use: Latin1_General_100_CI_AS_WS_KS_SC
SQL Server Express Local DB uses: SQL_Latin1_General_CP1_CI_AS (unfortunately)

References:

SQL Server database migration to Azure SQL Database
Migrate your SQL Server database to Azure SQL Database using DMA
SQL Server instance migration to Azure SQL Database Managed Instance


SQL*Loader-605: Non-data dependent ORACLE error occurred

You've simply run out of space in the DB you are loading the data into. Try:

SQL> select file_name, bytes/1024/1024/1024 as gb from dba_data_files where tablespace_name='USERS';

Let's say the file was `/data/users01.dbf', 32G in size. Then you might do

SQL> alter tablespace users add datafile '/data/users02.dbf' size 1g autoextend on next 1g maxsize unlimited;

That will give you up to another 32G of space. Repeat as necessary, 03, 04 etc. I don't know how much Oracle data your 95G text file will end up as, but you can see what is available with:

SQL> select tablespace_name, sum(bytes)/1024/1024/1024 as Gb from dba_free_space where tablespace_name='USERS' group by tablespace_name;


Adding a data file to existing sql server database taking too long

I have a feeling something is iffy about your setup here.

If you run the following script:

DBCC TRACEON(3004, 3605, -1);
GO

CREATE DATABASE Crap;
GO

EXEC sys.sp_readerrorlog;
GO

DROP DATABASE Crap;
GO

You should see this in the error log:

In other words, only the transaction log needs to be zeroed out. The data file should skip this.

I can run this commands and have it finish instantly as well:

ALTER DATABASE Crap
    ADD FILE ( NAME = N'Crap_1', FILENAME = N'F:\SQL\Data\Crap_1.ndf', SIZE = 102400000KB, FILEGROWTH = 0 )
    TO FILEGROUP [PRIMARY];
GO

If you're on a new enough version of SQL Server (as your question >implies), you can see if this query helps:

SELECT servicename, instant_file_initialization_enabled
FROM   sys.dm_server_services;

If that doesn't work, you'll have to go back in time and use whoami to determine that.

Keep in mind that your server needs to be restarted to enable IFI, so if you just added the priv, or you added it and never restarted SQL, it won't be in effect.

  Also, this database is 24*7 and has DB mirroring configured

If this is sync mirroring, perhaps IFI isn't enabled on the parter? Have you checked to see what's up over there?


For something like this, you may want to think about a unique architecture known as a Star Topology.

Picture the following:

Server HQDB with 25 databases (1 for each branch)
Server BranchDB001 with database `branch001``
Server BranchDB002 with database branch002
...
Server BranchDB025 with database branch025

Setup Data for HQDB

On HQDB, create the 25 databases branch001 through branch025
Load data for each branch into their respective databases

Setup each Branch

Load HQDB's branch001 into branch001 on Server BranchDB001

mysqldump branch001 database on HQDB into a script
Run that script in branch001 on Server BranchDB001

Repeat this for all Branch Databases

Setup Replication

Here is the tricky part. You want to make the HQDB the Slave of Every Branch. Unfortunately, MySQL Replication is inherently designed to be a Slave reading from one Master. (NOTE: MultiMaster (MultiSource) Replication is being implemented in the soon-to-be-released MariaDB 10.0. Since I am a MySQL DBA, I leave that to more adventurous DBAs. I need to more free time to learn MariaDB.)

Getting on with MySQL Replication, the idea is

RESET MASTER on BranchDB001 (Clears Binary Logs)
make HQDB a Slave of a Branch (say branch01)
Start Replication loading changes from BranchDB001's branch001 into HQDB's branch001
RESET MASTER on BranchDB002
make HQDB a Slave of a Branch (say branch02)
Start Replication loading changes from BranchDB002's branch002 into HQDB's branch002
...
RESET MASTER on BranchDB025
make HQDB a Slave of a Branch (say branch25)
Start Replication loading changes from BranchDB025's branch025 into HQDB's branch025

If you have common tables to ship to every branch, simply mysqldump each branch's copy of that table and load into each branch.

All these things could be done in a maintenance cycle to update HQDB's copy of every branch

I have discussed this wild idea before

Apr 25, 2011 : MySQL in star topology in StackOverflow (My 1st answer)
Apr 25, 2011 : MySQL in star topology in StackOverflow (My 2nd answer)
Apr 28, 2011 : MySQL in star topology in ServerFault

CAVEAT

Make sure your firewalls allow for DB Access in both directions

Branch to HQ
HQ to Branch

SSH Tunneling, please


Anybody know story behind pgFoundry (PostgreSQL project site) downtime?

Update: It's back. No explanation I've seen.

Pgfoundry is maintained outside the PostgreSQL infrastructure team's jurisdiction. It's a 3rd party site, and it hasn't seen a great deal of attention lately. Most projects on pgfoundry have moved to GitHub or git.postgresql.org.

If there's a particular project still on PgFoundry that you use, please contact its maintainers and ask them to relocate to git.postgresql.org or github.

If you're unable to reach them after a few weeks consider adopting the project and re-hosting it; just be prepared to hand hosting back over if the original maintainers reappear.

It was originally run by Marc Fournier. I'm not sure if he still runs it. You could ask on the pgsql-www mailing list for more information.

(Note that while I contribute to PostgreSQL, I am not a member of the infrastructure team, core team, or a committer. I do not speak for the project, or for my employer.)


Log file on SQL Server 2014 grown out of control - LOP_SHRINK_NOOP

As has been mentioned in the comments auto shrink is a generally bad idea for a number of reasons (see the links they've provided) and this is most likely the cause of your transaction log issues

Judging by what you've posted you may have ran something similar to this, but its a useful bit of code which will list out all of the databases in an instance along with the recovery model and what currently is preventing the transaction log from shrinking. the reasons are usually self explanatory, like checkpoint, Log_backup

Using this you should be able to narrow down what is preventing your transaction log from shrinking and hereby having all other transactions making it grow

SELECT instance_name
        , recovery_model_desc [Mode]
        , convert(decimal(12,3),[Data File(s) Size (KB)] * 1.0 / 1024 ) [Data File(s) Size (MB)]
        , convert(decimal(12,3),[Log File(s) Size (KB)] * 1.0 / 1024 ) [Log File(s) Size (MB)]
        , convert(decimal(12,3),[Log File(s) Used Size (KB)] * 1.0 / 1024 ) [Log File(s) Used Size (MB)]
        , [Percent Log Used]
        , log_reuse_wait_desc
    FROM (
        SELECT os.counter_name
            , os.instance_name
            , os.cntr_value
            ,db.recovery_model_desc
            , db.log_reuse_wait_desc
        FROM sys.dm_os_performance_counters os
        JOIN sys.databases db
            ON os.instance_name = db.name
        WHERE os.counter_name IN
        (
            'Data File(s) Size (KB)'
            , 'Log File(s) Size (KB)'
            , 'Log File(s) Used Size (KB)'
            , 'Percent Log Used'
        )
        --AND os.instance_name = 'Titan'
    ) as SourceTable
    PIVOT (
        MAX(cntr_value) FOR counter_name IN
        ([Data File(s) Size (KB)]
        , [Log File(s) Size (KB)]
        , [Log File(s) Used Size (KB)]
        , [Percent Log Used]))
        as PivotTable
    order by case WHEN log_reuse_wait_desc = 'NOTHING' THEN 0 ELSE 1 END desc
        , [Percent Log Used] desc

Personally I have this in a proc in my adminDB that can be ran whenever we see any issues relating to the transaction log to find out the exact state of it and what is the main cause of those issues

Its worth noting additionally for the auto shrink on the tlog, but mainly on the inevitable growth that you will have that the DB will be locked out while it grows the file and re-writes over that area of disk space


MySQL Replication - Issues with Triggers

Basically, there are two methods
METHOD #1
Use row-based replication. It says so in Paragraphs 1-3 of "Replication and Triggers"

With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.
This behavior is by design. If under row-based replication the slave applied the triggers as well as the row changes caused by them, the changes would in effect be applied twice on the slave, leading to different data on the master and the slave.
If you want triggers to execute on both the master and the slave—perhaps because you have different triggers on the master and slave—you must use statement-based replication. However, to enable slave-side triggers, it is not necessary to use statement-based replication exclusively. It is sufficient to switch to statement-based replication only for those statements where you want this effect, and to use row-based replication the rest of the time.

METHOD #2
Drop all the triggers on the Slave. No trigger present, no trigger to execute.
Do not do both method. Just select one method.


Here is a quick example of using min and max along with group by to produce a result that is similar to yours.

--Demo setup
drop table if exists table1
go
CREATE TABLE Table1 (
  Time INTEGER,
  Name VARCHAR(5),
  country VARCHAR(6)
);

INSERT INTO Table1
  (Time, Name, country)
VALUES
  ('12', 'steve', 'brazil'),
  ('12', 'mary', 'brazil'),
  ('12', 'kate', 'usa'),
  ('12', 'john', 'usa'),
  ('13', 'paul', 'canada'),
  ('13', 'tim', 'canada');

--Solution
SELECT [Time]
    ,min(NAME) AS Name1
    ,max(NAME) AS Name2
    ,Country
FROM table1
GROUP BY TIME
    ,country
ORDER BY [Time]

| Time | Name1 | Name2 | Country |
|------|-------|-------|---------|
| 12   | mary  | steve | brazil  |
| 12   | john  | kate  | usa     |
| 13   | paul  | tim   | canada  |


Substring join or additional table, which is faster?

Why Yours Is Better

In general, your pattern is a better idea.

Performance will depend on indexes, predicate selectivity, and table size though.

The reason your pattern is a better idea comes down to the concept of SARGability, which is a fancy word for if your search arguments can be used as seek predicates, or even fully pushed predicates, (i.e. not being processed in Filter operators after accessing an index).

Some examples of where this can hurt in joins and where clauses are:

function(column) = something
column + column = something
column + value = something
column = @something or @something IS NULL
column like ‘%something%’
column = case when …

When you do stuff like this, your queries can end up with all sorts of bad side effects:

Increased CPU (burn baby burn)
Index Scans (when you could have Seeks)
Implicit Conversion (if your predicates produce a different data type)
Poor Cardinality Estimates (poking the optimizer in the eye)
Inappropriate Plan Choices (because the optimizer is blind now, you jerk)
Long Running Queries (yay job security!)

Better Options

SARGable options for what you're looking for would include:

WHERE [ID] LIKE 'G%'

or

WHERE [ID] >= 'G' AND [ID] < 'H'

An alternative solution would be to add a computed column in just the table you're searching:

ALTER TABLE [Table]
    ADD Lefty AS Left([ID], 1);

CREATE INDEX ix_whatever
    ON [Table] (CategoryID , Lefty);

Though like I said before, the performance difference may not be dramatic with smaller tables.

It's also possible that this index won't be used since your example query is selecting all of the table columns, and this index doesn't cover them. But that's a story for a different day.


The main source of the problem is the tags changed in SSIS schema that 2016 and prior handled without issue. I had to view the code of the SSIS package to check for differences and SSIS 2017 seemed to be a bit more strict when it came to the schema within the PropertyGroup node of the Project tag for the script tasks.

Here is what one tag looked like that had errors:

<PropertyGroup>
  <ProjectTypeGuids>{30D016F9-3734-4E33-A861-5E7D899E18F3};{F184B08F-C81C-45F6-A57F-5ABD9991F28F}</ProjectTypeGuids>
  <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
  <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
  <OutputType>Library</OutputType>
  <RootNamespace>ST_f5177258030742e5ba64fefba1f795ea.vbproj</RootNamespace>
  <AssemblyName>ST_f5177258030742e5ba64fefba1f795ea.vbproj</AssemblyName>
  <StartupObject></StartupObject>
  <OptionExplicit>On</OptionExplicit>
  <OptionCompare>Binary</OptionCompare>
  <OptionStrict>Off</OptionStrict>
  <OptionInfer>On</OptionInfer>
  <ProjectGuid>{D93B5E70-A7E9-4AA1-94E7-7859FE6B3672}</ProjectGuid>
</PropertyGroup>

The fix that seemed to solve half of my problems were to add the missing entries so that the above now looked like the below code new entries (which are after </ProjectGuid> and before </PropertyGroup>) are:

ProductVersion
SchemaVersion
AppDesignerFolder
TargetFrameworkVersion
FileAlignment
ResolveAssemblyReferenceIgnoreTargetFrameworkAttributeVersionMismatch
TargetFrameworkProfile

Here is what it looked like after adding the changes:

<PropertyGroup>
  <ProjectTypeGuids>{30D016F9-3734-4E33-A861-5E7D899E18F3};{F184B08F-C81C-45F6-A57F-5ABD9991F28F}</ProjectTypeGuids>
  <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
  <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
  <OutputType>Library</OutputType>
  <RootNamespace>ST_f5177258030742e5ba64fefba1f795ea.vbproj</RootNamespace>
  <AssemblyName>ST_f5177258030742e5ba64fefba1f795ea.vbproj</AssemblyName>
  <StartupObject></StartupObject>
  <OptionExplicit>On</OptionExplicit>
  <OptionCompare>Binary</OptionCompare>
  <OptionStrict>Off</OptionStrict>
  <OptionInfer>On</OptionInfer>
  <ProjectGuid>{D93B5E70-A7E9-4AA1-94E7-7859FE6B3672}</ProjectGuid>
  <ProductVersion>8.0.30703</ProductVersion>
  <SchemaVersion>2.0</SchemaVersion>
  <AppDesignerFolder>My Project</AppDesignerFolder>
  <TargetFrameworkVersion>v4.0</TargetFrameworkVersion>
  <FileAlignment>512</FileAlignment>
  <ResolveAssemblyReferenceIgnoreTargetFrameworkAttributeVersionMismatch>true</ResolveAssemblyReferenceIgnoreTargetFrameworkAttributeVersionMismatch>
  <TargetFrameworkProfile></TargetFrameworkProfile>
</PropertyGroup>

Doing this did not fix the issue with inability to change .Net Framework target so there had to be something else so I looked through the code and found the problem. In the code I found the following after </ProjectExtensions>:

<PropertyGroup>
  <TargetFrameworkVersion>v4.0</TargetFrameworkVersion>
</PropertyGroup>
<PropertyGroup>
  <TargetFrameworkProfile></TargetFrameworkProfile>
</PropertyGroup>

Since these were now located in the main PropertyGroup of the script task, they are considered redundant so they are no longer needed in the old location. Deleting these entries fixed the other half of the problem. After doing this, not only was I able to change the SQL Server target to 2017, I was also able to change the .Net Framework version and it would not revert. Also, the builds were able to build successfully again.

Fixing the problem this way was easier than to try opening every package and manually creating a new script task, copying the logic from the old one to the new one, making sure the variables and references were the same, and deleting the old task, among other things. That process would take too long especially if there are a lot of packages to go through.

Hopefully you find this useful if you run into this issue.


This is not possible with PostgreSQL at this time.

What you're talking about is more like a "quiesce" or "stop" than a "detach". You don't seem to want to be able to move it from one PostgreSQL instance to another.

The issues are:

Transaction IDs are global. Pg would have to stop all writes to the DB then VACUUM FREEZE it, which is quite expensive, to make sure there would be no txid wrap-around issues when it was re-enabled.
Pg needs to be able to tell if a database contains objects that depend on a global object like a user, so when you DROP USER it can reject the attempt if it'd violate dependencies.
... and probably more

I don't think it's impossible, theoretically, but it's not going to be trivial to impliment. It also won't be very useful - I don't see what you gain from this over simply leaving the DB unused, or at most forcing a VACUUM FREEZE. Checkpoint activity is insignificant to nil if there's no activity in the DB, same with autovacuum.


SQL Server Partially Contained Databases and Linked Servers

There does not seem to be an issue for me querying a Linked Server from a partially contained database when logged in as a contained user. I would always recommend using a synonym. I did this on a stand alone SQL 2014 instance, using 4 part naming or the synonym worked fine. When logged in as a contained user you cannot see the Linked Server, but it works fine to call it.

Don't see why it would be a problem with Always On Availability Groups...just create the Linked server on each replica, and call it. Not sure what you mean by secondary replica, do you just mean in a 2-node AG? Give it a try on your instances and see what happens.


How can I edit a replication subscription to include new articles?

It's been a while since I've looked at SQL 2000, but in the newer versions you can just add an article by editing the properties of the publication in the UI then add articles.  You'll have to create and deploy a new snapshot but that's easy enough.  Just start the snapshot agent and it'll build and then push the snapshot to the subscriber.


Server Wide DDL Trigger Permissions Issue

Setting the trigger to EXECUTE WITH 'sa' appeared to do the trick. Not sure if this introduces any security concerns. I tried creating a separate login with permissions only to the Tools database and DDLEvent table but non sysadmin users got errors.

CREATE TRIGGER LogDDLEvent
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR DDL_EVENTS
AS

DECLARE     @eventInfo XML
SET         @eventInfo = EVENTDATA()

INSERT INTO Tools.audit.DDLEvent
VALUES
(
      REPLACE(CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' ') -- EventTime
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/EventType)')) -- EventType
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')) -- LoginName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/UserName)')) -- UserName
      , CAST(HOST_NAME() AS VARCHAR(128)) -- MachineName
      , (SELECT   CAST(client_net_address AS VARCHAR(128))
            FROM  sys.dm_exec_connections
            WHERE Session_id = CONVERT(INT, @eventInfo.value('data(/EVENT_INSTANCE/SPID)[1]', 'int'))) -- IPAddress
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')) -- DatabaseName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')) -- SchemaName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')) -- ObjectName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')) -- ObjectType
      , CONVERT(VARCHAR(MAX), @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) -- DDLCommand
      , @eventInfo -- DDLEventXML
)


Join tables and summarize rows based on condition

I had to edit some parts to get a query, as i didn#t have geommetry

CREATE TABLE countries (
  "id" INTEGER,
  "name" VARCHAR(6),
  "geometry" VARCHAR(3)
);

INSERT INTO countries
  ("id", "name", "geometry")
VALUES
  ('1634', 'UK', 'xxx'),
  ('2357', 'USA', 'yyy'),
  ('3345', 'Mexico', 'zzz'),
  ('4694', 'Italy', 'aaa');

CREATE TABLE customers (
  "name" VARCHAR(9),
  "geometry" VARCHAR(3),
  "statusActive" boolean
);

INSERT INTO customers
  ("name", "geometry", "statusActive")
VALUES
  ('Hernandez', 'xxx', 'TRUE'),
  ('Taylor', 'xxx', 'FALSE'),
  ('Monte', 'aaa', 'TRUE'),
  ('Winter', 'yyy', 'TRUE'),
  ('Best', 'yyy', 'TRUE'),
  ('Twist', 'aaa', 'TRUE');

SELECT  COALESCE(countr,0) NumberActiveUsers ,co.id
FROM
(SELECT countries.id, Count(*) countr
FROM customers
JOIN countries ON countries.geometry =  customers.geometry
WHERE "customers"."statusActive" IS TRUE
GROUP BY countries.id) t1 RIGHT JOIN countries co ON t1.id = co.id

numberactiveusers |   id
----------------: | ---:
                1 | 1634
                2 | 2357
                0 | 3345
                2 | 4694

db<>fiddle here


Improve performance of transactional replication

There are two steps to consider when tuning replication:

Reduce the number of commands to replicate
Reduce the time each command takes

The first one can be achieved by grouping multiple statements in a stored procedure and then replicate each procedure execution as a single command instead of multiple separate commands.
@ThomasStringer recommended http://msdn.microsoft.com/en-us/library/ms152754.aspx as a good starting point to learn about this feature.

The second can often be achieved by tuning the Distributor. The Distributor is doing most of the work in transactional replication. But it is also the most neglected part in replication. Often then Publisher is tasked to do the work of the Distributor too. If the Publisher has a lot to do already to keep up with the application workload, It cannot handle the additional work required to execute the distribution tasks. In high performance environments it is therefore a good idea to make the Distributor its own machine with its own disk. This will reduce CPU load, Disk load and network load on the Publisher.

If that is not possible make sure the distribution database is on a different spindle than the application database. You can also try to setup the subscription(s) as pull subscriptions instead of push subscriptions.

The above are general recommendations. As always, first make sure you understand what the root cause is. You can use sys.do_os_wait_stats and sys.dm_db_virtual_file_stats to see wether your disks are to slow, the processor is overloaded you have memory pressure or the network is to slow. Then look at the recommendations above to see what can help in your case.


The exact similar question was asked on new Microsoft Q and A Site. I created a 2 node WSFC with AG configured for SQL Server databases. To simply put WSFC did not initiated a failover because the parameter Failure-Condition Level is default 3. And as per Microsoft official document Configure a flexible automatic failover policy for an Always On availability group for a SQL Server restart to trigger a failover you need Failure-Condition Level=1
This can be changed via Powershell, TSQL or from cluster GUI. One below shows value in cluster GUI


When no index is faster than having a clustered index?

Looking at the ratio of elapsed time to CPU time for both queries, we can see that the "no index" query benefited from parallelism - CPU time was about 3 times greater than elapsed time.
After adding the clustered index to Table_B, you got a serial version of that execution plan (CPU time and elapsed time were about equal).  Or maybe an entirely different serial execution plan, but the fact that Table_B was scanned twice as often as Table_A in both queries makes me think the general plan shape could be the same.
With the clustered index in place, perhaps the optimizer reasoned that it could take advantage of the sorted nature of the clustered index, and thus didn't need to incur the overhead of parallelism.
In this case, that seems like a good decision by the optimizer overall: the second query used less CPU cores, read less data pages, at the cost of a small increase in elapsed time
If you edit your question to include more details (actual execution plan, table definitions, sample data), then someone might be able to provide an answer more specific to your situation.


Dynamic access to record column in plpgsql function

That's tricky, because identifiers cannot be variables in plain SQL. You need to use dynamic SQL with EXECUTE - which is still tricky, because variables are not visible inside EXECUTE.

Here is a demo how to get around this:

CREATE TYPE mytype AS (id int, txt text);

DO
$body$
DECLARE
    _val    mytype  := (1, NULL)::mytype;
    _name   text    := 'txt';
    _isnull boolean;
BEGIN
    EXECUTE 'SELECT $1.' || quote_ident(_name) || ' IS NULL'
    USING _val
    INTO _isnull;

    IF _isnull THEN
        RAISE NOTICE 'Column "%" cannot be null', _name;
    END IF;
END;
$body$

Improved with with @Jack's idea in the comment.

You cannot use plpgsql built-in FOUND because it is not set by EXECUTE (except for RETURN QUERY EXECUTE - more here). That's why I used GET DIAGNOSTICS ... initially. But finally simplified with @Jack's idea.

quote_ident() makes sure the name is syntactically valid and protects against SQLi.


What code underlies the PG_GET_LATE_BINDING_VIEW_COLS sys information function?

try svv_columns, it should give you what you need


Alternative of trigger to insert updated date in a column

I would use the DELETED table that is available when you do an update.
this is done through the OUTPUT Clause (Transact-SQL)

there is an example on the link above.

USE tempdb;
GO
CREATE TABLE dbo.table1
(
    id INT,
    employee VARCHAR(32)
);
GO

INSERT INTO dbo.table1 VALUES
      (1, 'Fred')
     ,(2, 'Tom')
     ,(3, 'Sally')
     ,(4, 'Alice');
GO

DECLARE @MyTableVar TABLE
(
    id INT,
    employee VARCHAR(32)
);

PRINT 'table1, before delete'
SELECT * FROM dbo.table1;

DELETE FROM dbo.table1
OUTPUT DELETED.* INTO @MyTableVar
WHERE id = 4 OR id = 2;

PRINT 'table1, after delete'
SELECT * FROM dbo.table1;

PRINT '@MyTableVar, after delete'
SELECT * FROM @MyTableVar;

DROP TABLE dbo.table1;

...

-- Results
-- table1, before delete
-- id          employee
-------------- ------------------------------
-- 1           Fred
-- 2           Tom
-- 3           Sally
-- 4           Alice
--
-- table1, after delete
-- id          employee
-------------- ------------------------------
-- 1           Fred
-- 3           Sally
-- @MyTableVar, after delete
-- id          employee
-------------- ------------------------------
-- 2           Tom
-- 4           Alice

and this is an example of how I use it, on a specific situation.

I use dynamic sql in this case.
Look as the DELETED table is used and the records are saved into a newly created table TableBackups.dbo._KB_20140513_tblStockThreshold in case we need to rollback later on.

USE SAStockLevel

DECLARE @SQL VARCHAR(max)

SET @SQL =
'
CREATE TABLE TableBackups.dbo._KB_'+CONVERT(VARCHAR(8),GETDATE(),112)+'_tblStockThreshold
    (
      [strItemNo] [varchar](20) COLLATE Latin1_General_CI_AS
                                NOT NULL ,
      [sintMarketID] [int] NOT NULL ,
      [sintChannelID] [int] NOT NULL ,
      [lngQtyThreshold] [int] NOT NULL ,
      [dtmRecordAdded] [datetime] NOT NULL ,
      [strAddedBy] [varchar](50) COLLATE Latin1_General_CI_AS
                                 NOT NULL ,
      [dtmLastUpdated] [datetime] NOT NULL ,
      [strUpdatedBy] [varchar](50) COLLATE Latin1_General_CI_AS
                                   NOT NULL ,
      [ActiveRecord] [bit] NOT NULL ,
      [ActiveMarket] [bit] NOT NULL ,
      [ActiveChannel] [bit] NOT NULL
)

-- UK

UPDATE  dbo.tblStockThreshold
SET     lngQtyThreshold = 0
OUTPUT  DELETED.strItemNo, DELETED.sintMarketID, DELETED.sintChannelID, DELETED.lngQtyThreshold, DELETED.dtmRecordAdded, DELETED.strAddedBy, DELETED.dtmLastUpdated, DELETED.strUpdatedBy, DELETED.ActiveRecord, DELETED.ActiveMarket, DELETED.ActiveChannel
        INTO TableBackups.dbo._KB_'+CONVERT(VARCHAR(8),GETDATE(),112)+'_tblStockThreshold
WHERE   strItemNo IN (
        SELECT  strItemNo
        FROM    dbo.tblOrgGrpStockLevel
        WHERE   lngTotal - ( lngAllocated + lngReserved ) BETWEEN 1 AND 4
                AND tintOrgGrpId = 1
                AND lngTotal <> 0 )
        AND sintMarketID = 1
        AND sintChannelID IN ( 1, 2 )
        AND lngQtyThreshold < 6666
        AND lngQtyThreshold <> 0
'
print @sql

BEGIN TRANSACTION T1
EXEC (@SQL)

COMMIT TRANSACTION T1

select * from TableBackups.dbo._KB_20140513_tblStockThreshold


MERGE with OUTPUT doesn't seem to be doing the right thing

When you update a row, it will appear in both the inserted (post-update value) and deleted (pre-update value) pseudo-tables.  If this seems odd, consider that an update is logically a deletion followed by an insert (though the update may not be physically performed that way).

When used with MERGE, the OUTPUT clause can include an extra column named $action. Adding this column to your query will show which action was taken ('INSERT', 'UPDATE', or 'DELETE') for each row.

For example:

insert into ModifiedTable_invalid(Id /* And other columns */)
select
    Id
    /* And other columns */
from
(
    merge ModifiedTable t1
    using TargetTable t2 on t1.JoinColumn = t2.JoinColumn
    when matched then update set t1.FkColumn = t2.FkSource
    when not matched by source then delete
    output
        $action as DMLAction,
        deleted.Id as Id /* And other columns... */
) outputs
where
    DMLAction = 'DELETE';

Updated rows will have $action = 'UPDATE'.

Also see Adam Machanic's post on using OUTPUT with the MERGE statement for some other nice examples.


How to delete stored procedures from a database project and have that reflect on the server?

The easiest way is using "Schema compare", where you would manually select objects to be dropped/ignored/added. to do this:

Right click the database in the solution explorer (choose schema compare)
Connect to your database
Click the 'compare' button
select/deselect differences changes that will be made
click the 'update' button
*note: if you want to NOT block on possible data loss, you need to configure that BEFORE comparing schemas. Do this by clicking the little settings icon

If you wish to DELETE (DROP) database objects in production database, that do not exist in your Database Project, you should successfully Build and Publish the project on production database using default settings.

If you have performed the Build successfully, proceed like this:

  Check to see if this checkbox is deselected:

  In Solution Explorer, right-click your database and select "Publish".
  "Publish Database" window should open
  Click the "Advanced..." button.
  "Advanced Publish Settings" window should open
  Switch to "Drop" tab
  Deselect the checkbox "Do not drop stored procedures".
  Click OK and continue with publish.


Putting a Select statement in a transaction

A SELECT query inside a transaction, in itself, is not properly shielded from UPDATEs and DELETEs.

What you need to use the following:

SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE
Disable autocommit by doing one of the following:

start transaction; (Which you have in your first query)
set autocommit = 0;

If you issue Delete From orders Where id=1, it will happen once the rows in the orders table have released their locks at the end of the transaction. You could experiment (on a Dev/Staging Server, please) with using READ UNCOMMITTED transaction isolation level to make the delete logically happen, but only on commit will it become visible and recorded permanently.

In the second transaction, basically all bets are off. If you run

select * From orders Where id=1;
UPDATE orders SET username="John" Where id=1;

running Delete From orders Where id=1 will commit immediately. Depending on the order MySQL executes these statements, will you see (or not see) the delete rows.

CAVEAT

MySQL 5.6 now has the following:

START TRANSACTION READ WRITE;
START TRANSACTION READ ONLY;

  The READ WRITE and READ ONLY modifiers set the transaction access mode. They permit or prohibit changes to tables used in the transaction. The READ ONLY restriction prevents the transaction from modifying or locking both transactional and nontransactional tables that are visible to other transactions; the transaction can still modify or lock temporary tables. These modifiers are available as of MySQL 5.6.5.


DB2 - Archive mode - large number of logs in logarchmeth1 folder

When you enable archive logging in a database, you're telling DB2 that you want to retain a copy of all transaction logs that have been created.  LOGPRIMARY and LOGSECOND only control how many active log files can be in use in the active log path at any given time.

Please read the documentation on database logging for more details.


Multiple databases in same AWS RDS PostgreSQL instance

I believe you can. Based on AWS RDS FAQs:

  Q: How many databases or schemas can I run within a DB instance?

  RDS for Amazon Aurora: No limit imposed by software
  RDS for MySQL: No limit imposed by software
  RDS for MariaDB: No limit imposed by software
  RDS for Oracle: 1 database per instance; no limit on number of schemas per database imposed by software
  RDS for SQL Server: 100 databases per instance
  RDS for PostgreSQL: No limit imposed by software

You can create additional database by connecting to you DB instance and do a CREATE DATABASE. Check this guide

  To create additional databases, connect to the DB instance and use the SQL command CREATE DATABASE.

And the blog post on how to create new database in an existing PostgreSQL DB Instance:

psql --host=SOME-DBMS-HOST --dbname EXISTING_DB \
     --username=YOUR-USERNAME --password \
     --command="CREATE DATABASE new_database WITH OWNER some_owner"


The ordinal position for a column changed without my knowledge

Someone changed the table, simple as that. Maybe not on purpose, e.g. they could have been changing other things in the mickey mouse table designer and accidentally clicked and dragged a column up or down. Figuring out who and when will be guesswork unless you have auditing in place, or the ALTER or DROP/CREATE events are still in the default trace:

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
  LoginName, HostName, ApplicationName, StartTime, Action = CASE EventClass
    WHEN 46 THEN 'Create' WHEN 47 THEN 'Drop' ELSE 'Alter' END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (46,47,164)
AND EventSubClass = 1
AND DatabaseName = N'your database name'
AND ObjectName = N'your table name'
ORDER BY EndTime DESC;


This will not be fast (this took over a minute to produce 0 results in my local copy of AdventureWorks).

DECLARE @SpecificWord NVARCHAR(32);
SET @SpecificWord = N'donut';

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT NULL,NULL WHERE 1 = 0';
SET @SpecificWord = N'%' + @SpecificWord + N'%';

SELECT @sql += N' UNION ALL SELECT '''
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
 + ''',''' + QUOTENAME(c.name) + ''' WHERE EXISTS
 (
   SELECT 1 FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
     + N' WHERE ' + QUOTENAME(c.name) + N' LIKE @s
 )'
FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE c.system_type_id IN (35,99,167,175,231,239);

EXEC sys.sp_executesql @sql, N'@s NVARCHAR(32)', @SpecificWord;

If you need to support sql_variant for some reason (I recommend against it), only a few changes are needed:

DECLARE @SpecificWord NVARCHAR(32);
SET @SpecificWord = N'donut';

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT NULL,NULL WHERE 1 = 0';
SET @SpecificWord = N'%' + @SpecificWord + N'%';

SELECT @sql += N' UNION ALL SELECT '''
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
 + ''',''' + QUOTENAME(c.name) + ''' WHERE EXISTS
 (SELECT 1 FROM ' + QUOTENAME(s.name) + N'.'
 + QUOTENAME(t.name) + N' WHERE CONVERT(
   NVARCHAR(MAX), ' + QUOTENAME(c.name)
 + N') LIKE @s)'
FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE c.system_type_id IN (35,98,99,167,175,231,239);

EXEC sys.sp_executesql @sql, N'@s NVARCHAR(32)', @SpecificWord;


After modifying the throughput of a DynamoDB table, where can I see the new throughput value before it gets active?

I came across similar problem. So thinking to make some sort of series of describe table

DescribeTableResult describeTableResult = client.describeTable('tableName')

And put it in while loop until settings are applied. That could give rough idea how much time does it take to apply settings.


Timestamp Difference - Syntax Error - Condtitional Selects

SELECT name, admin, reason, unban, FROM_UNIXTIME(date, '%Y-%m-%d') AS datetime,
HOUR(x.difference) AS HRemain, MINUTE(x.difference) AS MRemain
FROM(SELECT SEC_TO_TIME(AVG(TIMESTAMPDIFF(MINUTE, unban, NOW()))) AS difference FROM banned) AS x, banned
WHERE name LIKE 'Yashas'

Solved it myself. Just had to get a textbook and study.

EDIT: Server returns datetime as NULL for some reason.


Is it possible to improve the execution performance of a query with 'simple' joins?

Many outer joins are not a best option when dealing with big tables. In a nutshell, your plan might not be as optimal as it could be when using outer joins.

Wrong RENDA_FIXA table estimation is the reason why you are getting Parallelism and Hash joins everywhere even though you are not expecting many rows in the first place.

You can try OPTION (MAXDOP 1) hint to see if that any helps.
Also you should check statistics on RENDA_FIXA and GRUPOS_USER_2804 tables.

The reason why you are getting that plan is because it expects 120,000 records, instead you're getting 1,800 that's why left loop joins helped you.

My advice is to try to deal with RENDA_FIXA table first, get the correct estimations out first, so the query optimizer could try to create a right plan.


This is not native functionality. You can encrypt: Nothing, Sensitive items, Everything. There is no capability to assign one key for development and one for execution.

Instead, the way to restrict execution is through proper security, which is never the last item. If the SSIS package is loading a database, you ensure that only the correct user(s) can do connect/write to the database. If you store the packages in the database, deny them the ability to run the packages. If it's on the file system, set the proper ACL so they can not get to them.


I'm assuming you're not using ASM.

Either set the current datafile to AUTOEXTEND:

alter database datafile
   '/full/path/to/system01.dbf'
autoextend on
next 128m
maxsize 8192m;

Or, add a new datafile:

alter tablespace SYSTEM
  add datafile '/full/path/to/system02.dbf'
size 512m;

Obviously, alter the numbers and path as needed.

Adding a datafile impacts your backups. Take a full backup immediately after adding it - this is the only thing you really have to worry about.

4Gb is HUGE for a SYSTEM tablespace. I'd first investigate whether there are some large objects being stored in the tablespace that shouldn't be there (DBA_EXTENTS is your friend).

Also, note that you cannot remove a datafile once it has been added to the SYSTEM tablespace.


In MongoDB, is having lots of small collections better than having one huge collection?

The smaller the collection the better. Yes if the index does not fit into ram it will be come slower. the way most index searching works is based on binary searching. Its a great method. I build a little test last week in PHP and you can have a list of 1000 numbers and you will be able to find each number in about 12.3 comperasons. So think of it that way. If you a table of 1000 items only having to check 12 times to find the correct result is pretty good in my book. The more documents you add in mongo the more operations that have to take place.But its imporant not to make them to small that your server has to keep switching for simple things.

Also make sure to remember that each document has a max size of about 18mb I think. That is a lot of data. Look up how many words are in the book "war and peace" and that can fit in about 3mb of data. But dont let it control you over stuffing things or under stuffing them.


Primary key: (a) set it on a single column for two distinct data points or (b) set it on a combination of two columns, each for a separate data point?

I suggest doing both. Create an attribute for SKU and two separate attributes for the product and sub-type. SKU would be a key and the product sub-types would be a composite key. Example:

CREATE TABLE product
(sku CHAR(7) NOT NULL,
skusub1 CHAR(4) NOT NULL,
skusub2 CHAR(3) NOT NULL,
...
UNIQUE (sku),
UNIQUE (skusub1,skusub2),
CHECK (skusub||skusub2=sku));

You can give the sub1 and sub2 attributes more meaningful names.

This is not actually a violation of 1NF. SKU is an important value in its own right and as long as you have the separate components in the database as well then there's no reason ever to split SKU into two parts. SKU is nearly always treated as a single value in my experience because although it may have encoded elements within it the encoding scheme is subject to variation. You may at some point have SKUs that don't follow the same scheme.

(note: in the above example I haven't identified a primary key because that's not relevant for this example)


SQL server restore readonly file group without backup from .ndf file

note that when you ran the RESTORE FILELISTONLY look at the column IsPresent and for the readonly file it shows 0
this mean that this file does not exists in the backupset.

anyway this file cannot be restored from that backup set because it does not exists there, so you must take it from a full backup (or just a file backup)

the only option to reset the "Archive_Pending" is by restore that element (either file group, file or page) and make it consistent with the database LSN (log sequence number)

the term attache is only for new attached databases and cannot be used with files.

so you can take it from full backup or from file backup (any backup that this file is "IsPresent" = 1 )

to continue your DEMO SCRIPT

-- take backup of that RO file group
BACKUP DATABASE [TestDB]
FILEGROUP = N'TestDB_Part2'
TO  DISK = N'D:\data\File2Backup.bak' ;
GO

-- restore the RO file / FG
RESTORE DATABASE [TestDB2] FILE = N'TestDB_Part2' FROM  DISK =     N'D:\Data\File2Backup.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

and thats it.... your DB is up and running

I hope this has been informative for you, please let me know if you have any more questions.


version, OS, tool are helping things to help you solve this.

I guess OS is Windows.
version 9i.
export full.

If this is the case:
connect to the server,
set ORACLE_HOME=/where/is/oracle/home/
set PATH=%ORACLE_HOME%/bin;%PATH%
set ORACLE_SID=ORCL -> or your ORACLE_SID name

imp file=x.dmp logfile=imp_x.log full=y

imp help=y gives some clues about the commandline options.

study the errors. If there is a complaint about the dmp file, read the first few blocks. From those you can find who exported it, what type export it is and even some version info. It is a bit binary but just readable enough to get this info.

tip:

If your data has a real value for your company, hire a real dba to make a decent setup and have someone as a backup. It is easy to build a system that work happily for years and due to hardware issues suddenly become less happy. It's a pity if that costs your company the head. This is regardless for what brand of database you setup.