Create/Update Test Database duplicated timely from Production database on same server

Here is a very quick TSQL prototype that you could put into a SQL Agent job

USE MASTER
GO

--IMPORTANT - Use COPY_ONLY when backing up production to prevent messing up recovery for production
--Backup production
BACKUP DATABASE zzz TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\zzz.bak'
WITH NOFORMAT
    ,INIT
    ,SKIP
    ,NOREWIND
    ,NOUNLOAD
    ,STATS = 10
    ,COPY_ONLY

--Restore to new database name using the backup from the previous command
RESTORE DATABASE zzzNew
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\zzz.bak'
WITH FILE = 1
    ,MOVE N'zzz' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\zzzNew.mdf'
    ,MOVE N'zzz_LOG' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\zzzNew_LOG.ldf'
    ,NOUNLOAD
    ,REPLACE
    ,STATS = 10
    ,RECOVERY

--optional command to put test database into simple recovery mode, if
--production had been in FULL and you don't need the test database in FULL
--ALTER DATABASE zzzNew SET RECOVERY SIMPLE WITH NO_WAIT


If you are running SQL Server 2008 R2 SP1 or newer you can turn on the trace flag which changes when auto update stats runs.  The traceflag is 2371 and it changes the stats update trigger from 20% + 500 rows to a sliding scale.

I talk about the finer points of the traceflag here.


How to see query executed for a specific user from V$SQL

You can query v$sqlarea, which holds sql history for a little longer than v$sql (holds info only about current running sql). Both have similar columns, but depending on the report you want to extract, you might wanna join v$sqlarea with other views. But V$sqlarea is also limited to a period of time, and if you need to go more in depth, the view is DBA_HIST_SQLTEXT. Keep in mind that for every dynamic (V$) view, there is a corresponding dba_hist table... But since dba_hist views are often maintained by AWR, you might need to be licensed for diagnostic/ tuning pack for complete reports.


SQL Server Automatic Tuning (for query plans) vs. Availability Groups

In SQL Server 2017 (and up), for a database configured within an Availability Group, with query stored enabled, will automatic tuning work on the read-only replicas?

No, it wont as documented in BOL(for azure but still applies to sql server on prem):

Attempting to enable Automatic tuning via T-SQL on the read-only secondary will result in a failure as having a different tuning configuration on the read-only secondary is unsupported.

Is this correct?

Yes, it is. Erin from SQLSkills has a nice FAQ that says : Because the replica is read-only, and Query Store inherently writes data TO the database, you cannot enable it to capture queries that are executed against that read-only copy.
if you are running into parameter sniffing issues, you can use query hints (with proper testing) as explained by Paul White in his blog post


Why does the column content say “Array”?

It does have the image that you inserted. The database does not have a renderer that it can use to display the images. All files are stored as an array of bytes (binary format). You would need to extract the file using code and convert it to an image to be displayed. As it stands, there is nothing wrong.


PostgreSQL: Check the structure of foreign tables

How can I check if the structure of a foreign table was changed in the
  source database in order to know when to re import it. Foreign table
  was imported by foreign-data wrapper postgres_fdw.

As here and here if you’ve imported your schema, then you can query the foreign tables as if they were normal, local tables.

For further your ref postgres_fdw and IMPORT FOREIGN SCHEMA


One of my Disaster recovery server every day sp_configure values are changing

This will be in your Log File.

For example, if I run:

EXEC sys.sp_configure @configname = 'Show Advanced', @configvalue = 0
RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure @configname = 'Show Advanced', @configvalue = 1
RECONFIGURE WITH OVERRIDE

It shows up in my Error Log like this:

If you want to search through your error log for other instances of this occurring, you can click filter, and then fill out a search condition.

If you want to dig a little bit deeper, you can check the default trace for events as well (adapted from Aaron's code here):

DECLARE @path NVARCHAR(260);

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

SELECT   TOP 1000 TextData,
                  NTUserName,
                  NTDomainName,
                  HostName,
                  ApplicationName,
                  LoginName,
                  ServerName,
                  DatabaseName,
                  SessionLoginName
FROM     sys.fn_trace_gettable(@path, DEFAULT)
WHERE    EventClass = 22
AND TextData LIKE '%Configuration%'
ORDER BY StartTime DESC;


Setting UNIQUE constraint on TIMESTAMP (WITH TIME ZONE)

There is nothing wrong with having a multi-column constraint:
ALTER TABLE mytab ADD PRIMARY KEY (object_id, timestamp);

The column order does not matter for the constraint, but if you more often search by timestamp alone, put that column first.


Is my entity-relationship diagram about customers and accounts correct?

IMHO you just have to pay attention on the order of cardinalities, and you should note that Issue Date is not a weak key, but it's not a key at all: in fact you could have two account statements of different people issued on the same date, so you need a foreign key (Issue Date plus Number of Account)


How do i assign a custom id using a trigger using sequence on a table

You're nearly there! A few small changes needed.

Table definition:

CREATE TABLE Atendant (
   ID VARCHAR(20) PRIMARY KEY,
   NAME  VARCHAR2(25 CHAR) NOT NULL
);

Trigger:

CREATE or replace TRIGGER  Atendant_BI
  BEFORE INSERT ON  Atendant
  FOR EACH ROW
DECLARE
BEGIN
  IF :NEW.ID  IS NULL THEN
    :new.id := 'ATN'||Atendant_SEQ.nextval;
  END IF;
END;
/

Test:

SQL> insert into Atendant (NAME) values ('phil');

1 row created.

SQL> select * from Atendant;

ID     NAME
------ ----------
ATN1   phil

SQL>

So all you were really missing was the data type and doing the concatenation correctly.


How do I ensure consistent MySQL backups?

Are you using innodb? If so, Percona's XtraBackup is a great choice for automating backups while keeping the server operational and responsive. It also supports incremental backups to shorten the backup duration.

Another option is to have another database that is a replication slave and you backup this slave. You can shut down replication, backup, and resume, never impacting the production data.

I personally use a combination of the two solutions. We have a backup sync and we use xtrabackup to backup this server, thus allowing us consistant backups on demand without impacting production load.


Is it possible to store historical configuration settings for each row of data without cramming all the configuration settings into each row of data?

Here are some random thoughts. I guess the configuration could change without the existence of a datapoint at that particular point. First I would have a table for the sites:
CREATE TABLE sites
( site_id ... NOT NULL PRIMARY KEY
, <other attributes>
);

Now, you can have your table relate to that
CREATE TABLE measurements
( site_id ... NOT NULL REFERENCES sites (site_id)
, measurement_ts ... NOT NULL
, <measurements>
, PRIMARY KEY (site_id, measurement_ts)
)

It is not clear to me from your description if you should futher normalize the measurement values or not, so I'll leave them as-is.
Now you can add the configuration as:
CREATE TABLE configurations
( site_id ... NOT NULL REFERENCES sites (site_id)
, begin_time TIMESTAMP NOT NULL
, end_time TIMESTAMP
, ...
, PRIMARY KEY (site_id, begin_time)
)

Neither is it clear for me whether the configuration values should be further normalized, so I'll leave that as well
When you change the configuration, you close the previous one by setting end_time to now, and add a row with begin_time = now, end_time = null.
@J.D. mentions one of the commercial vendors with some support for temporal data as an alternative. You may want to have a look at IBM Db2 community edition as well. The limitations are 100Gb storage, 4 cores and 16Gb ram. In case you are interested, you can find out more at ibm-db2-developer-community-edition
For temporal tables, in particular, you can find more info at Time Travel Query using temporal tables


Unique Non Clustered Column in Partitioned table

You cannot have an unique constraints backed by an aligned index (or plain unique non-clustered indexes) unless you add the partitioning column to the the unique expression. So if you have partitioned your table on column [datetime] then your unique constraint (or the unique index) must be ([datetime], [xyz]). Since more often than not this is not acceptable, the alternative are to:

remove the unique constraint from the data model (ie. accept that duplicates can occur)
keep the non-aligned index, with all switch-in/switch-out issues and performance problems


Distribution database (recovery pending)

Can you try below  (substitute with appropriate names - publisherdb, publisher, distribution database) :

-- Disable the publication database.
USE [AdventureWorks2012]
EXEC sp_removedbreplication @publicationDB;

**-- Remove the registration of the local Publisher at the Distributor.**
USE master
EXEC sp_dropdistpublisher @publisher;

-- Delete the distribution database.
EXEC sp_dropdistributiondb @distributionDB;

-- Remove the local server as a Distributor.
EXEC sp_dropdistributor;
GO


Index rebuild suspended due to IO_COMPLETION wait type

Community wiki answer:

  What is IO_COMPLETION?

This wait is associated with a sort operator that runs out of memory and has to spill to tempdb. It is not immediately obvious why rebuilding an index would involve sorting, perhaps there are some details missing from the question.

  Is there anyway to get/see an estimate?

If you happen to know how large the table is (page size specifically), that would be roughly the number of reads. If you only know the size in MB (or GB), you'd have to divide by 8192 bytes (the size of a data page) to get a rough estimate of total number of reads.

Rebuilding an index might do at least 2x this number depending on which rebuild options you choose.


Is there a way to create a MySQL/Percona Slave without using a dump?

Something close to what you're asking can be done with mysqldump. You can stream its output directly to the slave.

# mysqldump -h master -A --master-data --single-transaction --quick | mysql -h slave

mysqldump will add CHANGE MASTER TO to the output. However, it doesn't include MASTER_HOST, MASTER_USER and MASTER_PASSWORD. It has to be configured separately.

# mysql -h slave -e "CHANGE MASTER TO MASTER_HOST='master',
  MASTER_USER='repl', MASTER_PASSWORD='replpass'; START SLAVE;"

Additional notes:

You can run the abovementioned command on the master, or slave or any other host. Note the -h option in mysqldump and mysql. See mysql Options.
The master is not down when you're taking a dump.
There will not be any binlog ID issues because no matter where you run mysqldump -h master, the dump will come from the master. Respectively, the binlog coordinates will point to the binlog on the master.
The defaults will cause InnoDB tables to be table locked, that is unless --single-transaction is specified which negates the requirement of a table lock for a consistent dump.
Using --quick will ensure large tables are read one row at a time rather than buffering the entire row set in memory, which is more conducive to this type of streaming dump.


Select all data from IBM DB2 member table

The best solution, is to install the DB2 Multisystem product and recreate the table as a local "partitioned table".

Behind the scenes, DB2 for i creates the table as a multi-member PF basically just like you have now.

However, SQL statements referencing the table will auto-magically see all the data.  Old RPG applications that use OVRDBF to override to a specific member will continue to work as before.

Unfortunately,  DB2 multisystem is not cheap.  If you don't already have it, i doubt you'd want to get it.

You can create a DDS logical file with DTAMBRS(*ALL) or use OVRDBF MBR(*ALL) to create a single "view" of the data.  But unfortunately, only RPG programs using record level access (RLA - aka "native RPG I/O op-codes) can use them.  SQL access results in a CPF4268 - Object *ALL in *N type *MEM not found.

The only SQL statement in DB2 for i that knows anything about members is the CREATE ALIAS statement.  So you could issue a CREATE ALIAS as needed to access the member you need.  Or you could pre-create alias' for every member.

You can then use the alias' in a select statement like so

select * from myalias1
UNION ALL
select * from myalias2

It'd be nice if you could create a view from the statement above, unfortunately you can't.  Trying to do so results in an SQL7030 - Alias MYALIAS1  for table MYTABLE in MYLIBRARY not valid for statement.

The only way to encapsulate the statement I'm aware of would be to define it as a stored procedure or user defined table function (UDTF).


Using TOP and getting different result sets

Take a look at the plans. When you use SELECT * it probably uses the clustered index, and when you only want one column, maybe there is a skinnier index to use.

Don't "expect" a certain order. If you don't tell SQL Server how to order, then it will use the most efficient way possible, and this can change due to probably more than 20 factors.

If you want a certain order, SAY SO. Please read #3 here:

T-SQL Tuesday #56 : SQL Server Assumptions

Also, this post by Michael Swart may be an interesting read:

Without ORDER BY, You Can’t Depend On the Order of Results

If you want your second query to be more efficient, you can consider creating an index on DateCreated (you may want to include OrderID - not sure of the present index structure).

For your actual goal of deleting n rows at a time, oldest first, and assuming OrderID is an IDENTITY column (so order create date should roughly align with that), why not use this approach (based on this great blog post, also by Michael Swart):

-- pick a datetime for the newest row you want to delete
-- let's say you want to delete all orders before Jan 1 2014:

SELECT @MaxOrderID = MAX(OrderID)
  FROM dbo.[Order] -- terrible table name, also always use dbo prefix
  WHERE DateCreated < '20140101';

DECLARE @BatchSize INT = 1000,
        @LargestOrderProcessed INT = -1,
        @NextBatchMax INT,
        @RC INT = 1;

WHILE (@RC > 0)
BEGIN
  SELECT TOP (@BatchSize) @NextBatchMax = OrderID
    FROM dbo.[Order]
    WHERE OrderID > @LargestOrderProcessed
    AND OrderID <= @MaxOrderID
    ORDER BY OrderID;

  DELETE dbo.[Order]
    WHERE OrderID > @LargestOrderProcessed
    AND OrderID <= @NextBatchMax;

  SET @RC = @@ROWCOUNT;
  SET @LargestOrderProcessed = @NextBatchMax;
END

In order to minimize the impact on the log, you may want to add some additional logic in there, from my blog post Break large delete operations into chunks. As for the dbo prefix, see Bad habits to kick : Avoiding the schema prefix.


Even i had this issue and came to know its an bug. workaround for this would be dumping by passing the DB

mongodump -d "database_name"

try this may resolve your issue. Make sure you have enough space in the File system.


Postgres default privileges do not seem to work

Having the 'provisioner' role is not the same thing as being 'provisioner'.
For your shown default permission to apply, you would have to create the object as 'provisioner' (for example, SET ROLE provisioner; before you create the objects)


I'm not sure if there is a flag, but there is a reference available at http://msdn.microsoft.com/en-us/library/ms178091.aspx of which built in functions are deterministic.

GETDATE() and GETUTCDATE() are nondeterministic because if you call them multiple times with the same parameters, which in this case are none, you get different values. In other words, the return value is not determined by input.

As far as why the second is slower than the first, check for implicit conversion between SubmitDate = @SubmitDate. Are they both datetime?


Postgres replication not cleaning up WAL files

I think you're looking for the archive_cleanup_command setting in your recovery.conf file:

archive_cleanup_command = 'pg_archivecleanup archivelocation %r'

See the docs or this post for more info.


Flush Host Connection Blocked, but some connections allowed

That host was blocked because there have been too many connection errors. The maximum number of errors before a host is blocked is represented by the max_connection_errors variable. Before reaching that number of errors, the host was not blocked.

The FLUSH HOSTS commands (or mysqladmin flush-hosts) empties the host cache. After that, the host can connect again and the errors counter is reset.


Why does this plan cost even though there are no rows?

The following statement needs 75.6% of the whole Batch-Operation

This is an estimate. The percentage costs are always estimates, even in a post-execution ('actual') execution plan. The 75.6% figure represents the estimated cost of this plan as a proportion of the estimated cost of the entire batch.

Given the disparity between estimated rows and the number actually encountered at runtime, this number means nothing. In fact, these numbers are often no better than a very loose hint, because the optimizer's cost model probably bears very little relation to the actual costs of execution in your particular hardware environment, even where the cardinality estimates happen to be exactly correct.

  As you can see in the execution-plan, the Update-Operation of the clustered index would take 99% of the costs of the plan

Same explanation as above. According to the optimizer's cost model, if the query had actually produced the same number of rows as estimated, the Clustered Index Update would account for 99% of the estimated cost of running this statement.

  LieferantID is not the Clustered index (but it is in another index WW_Pos_LieferantID). Nevertheless, it has to update the clustered index?

An update always has to modify the clustered index, since the clustered index is the table. Changes must be made in the base table as well as any nonclustered indexes (which are just copies of a subset of the underlying table data).

  Why does this Update-Statement take about 40 seconds, even though there are no records to update?

Hard to say from the information provided. This may be a trigger plan (after or instead of update) assuming you do not have a table called [deleted], in which case it is possible you are timing the complete update statement, and not just the execution of this trigger code.

To address an extra question raised in comments to the question, the extra operators in the execution plan are to detect non-updating updates (a change to a nonclustered index that does not actually change the stored value).


You just need to set your $PATH up.

This is covered on the Postgres.app website.

Add this to your ~/.bash_profile:

export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/latest/bin

... then reopen your shell/terminal.


Query: Most recent record for unique combination of two fields in MS Access 2010

Try this:

SELECT  a.* INTO recent_data
FROM primarytrafficcountdata a
 INNER JOIN (SELECT section_id,MAX(date) AS maxdate
              FROM primarytrafficcountdata
              WHERE YEAR(date) <= 2014 AND YEAR(date) >= 2011
             GROUP BY section_id) groupeda
 ON (a.section_id = groupeda.section_id AND a.date = groupeda.maxdate);


Revoke User Access to all System Information Functions on Postgresql

You would have to

REVOKE EXECUTE ON FUNCTION ... FROM PUBLIC;

because that's the default privilege for functions.

Revoking a privilege that was not granted is a no-op in SQL.

After revoking the privilege from PUBLIC, you'd have to explicitly grant it to all users that should have it.

But I think you shouldn't do that.

Why not?

These modifications will not survive a major upgrade.
PostgreSQL does not consider this information security critical, and I think that is correct.

Everybody should be able to see the settings relevant for the session.

Note that certain parameters that reveal details about the operating system where PostgreSQL is running are only visible to superusers or members of the pg_read_all_settings role anyway.
You may break applications that rely on these functions.


With a modern release (>= 10g) and the usage of a Recovery Area (DB_RECOVERY_FILE_DEST is set), the initialization parameter DB_UNIQUE_NAME (derived from DB_NAME if not specified explicitly) tells RMAN in what subdirectory of the Recovery Area the controlfile autobackup must be searched for.
Without usage of Recovery Area, and in status NOMOUNT (no controlfile is opened), you must set DBID for RMAN in order to enable it to retrieve the right controlfile autobackup.


DATETIME is used to store an instant ("point in time") at a particular granularity (approx 300 milliseconds IIRC).

The value you wish to store is an interval (or is it a duration?) in milliseconds. SQL Server lacks an interval data type.

While both are temporal data they have quite different requirements. I suggest you use an numeric column (e.g. INTEGER) to store the interval and use metadata (e.g. an appropriate name) to convey the fact the granularity is one millisecond.


can I use select statement for ifnull function?

Start with this simplification:

CREATE TEMPORARY TABLE t
INSERT INTO t
    SELECT ... -- Keep this; toss the other parts
SELECT FROM t

Then look around for other simplifications.

Read up on INSERT ... ON DUPLICATE KEY UPDATE ... -- it may be the ultimate simplification.

After all that, I may be able to see what you are asking about (IFNULL).


High MySQL CPU usage, queries seem fine, not running ntp

You can have a high load from lots of short queries as well.  Try setting your long_query_time to 0, and force all clients to reconnect.  Let that run for a few minutes and then set it back to your default.  You can peruse the results by hand or use a tool like pt-query-digest.

In either case, you should be able to figure out if there is in fact a bunch going on, just very quickly.


Cassandra nodetool repair open files issue

Since SSTables can contain tokens from multiple token ranges, and repair is performed by token range, it was necessary to be able to separate repaired data from unrepaired data. That process is called anticompaction. Level Compaction Strategy (LCS) a very intensive strategy where SSTables get compacted way more often than with STCS and TWCS. LCS creates fixed-sized SSTables, which can easily lead having thousands of SSTables for a single table. The way streaming occurs in Apache Cassandra during repair makes that overstreaming of LCS tables could create tens of thousands of small SSTables in L0 which can ultimately bring nodes down and affect the whole cluster. This is particularly true when the nodes use a large number of the vnodes. I have seen happening on several customer clusters, and it requires then a lot of operational expertise to bring back the cluster to a sane state. A safety measure has been set in place to prevent SSTables from going through anticompaction to be compacted, for valid reasons. The problem is that it will also prevent that SSTable from going through validation compaction which will lead repair sessions to fail if an SSTable is being anticompacted. Given that anticompaction also occurs with full repairs, this creates the following limitation: you cannot run a repair on more than one node at a time without risking to have failed sessions due to concurrency on SSTables.
The only way to perform repair without anticompaction in “modern” versions of Apache Cassandra is subrange repair, which fully skips anticompaction. To perform a subrange repair correctly, you have three options :

Compute valid token subranges yourself and script repairs accordingly
Use the Cassandra range_repair.py script which performs subrange repair
Use Cassandra Reaper, which also performs subrange repair. Google it to find as repo's might change

To decrease the number of the open files and minimize the restart time use: nodetool cleanup; nodetool compact


MySQL and MariaDB do not have any way for a TRIGGER to invoke anything outside of the MySQL/MariaDB server.  (To do so would be to create a security problem.)

Well, you could write a UDF that could do anything, and compile a special version of the server, etc.

You are probably better off polling, as you mentioned.  If there are thousands of updates per minute, the polling could "optimize" the fetch via GROUP BY and other techniques.


DB and operating system packages updates

You should apply patches. Clone your database, apply the patches to the clone and see if it affects its operation. Test, test, test. But security patches aren't issued on a whim. Are they mandatory? Recommended or optional patches? But at some point you will have patches you do need to apply.


SQL Server 2017 Enterprise, DB has suspect_pages, GAM and SGAM NOT ALLOCATED - am I OK?

To quote Paul Randal on the matter:

  We’re interested in the output beginning Metadata:. Those fields are
  not stored on the page itself...

  If you see the ObjectId is 0, that means there was no metadata found.
  This could be because:

  The table that the page was part of has been deleted since the page
  corruption was logged
  The system catalogs are corrupt in some way
  The
  page is corrupt and so incorrect values were used to look up the
  metadata

  In any case, you’ll need to wait for DBCC CHECKDB to complete to know the extent of the damage.

It doesn't sound like you're in the clear, so I'd probably push on getting a suitable environment to check a full backup on.


You cannot have a WHERE clause on an index in Oracle.

You can create a function-based index (I'm eliminating the isdefault third column from the index since we know that must be Y so it's not adding any selectivity to the index)

CREATE INDEX c_bplocation_isdefault ON c_bpartner_location (
  case when isdefault = 'Y' then ad_client_id else null end,
  case when isdefault = 'Y' then c_bpartner_id else null end
)

This takes advantage of the fact that Oracle does not index entries where all the columns in the index are NULL in order to reduce the size of the index.  In order for your queries to use the index, however, you'd need to use the same expression that you used to create the index.  Something like

SELECT *
  FROM c_bpartner_location
 WHERE (case when isdefault = 'Y'
             then ad_client_id
             else null
         end) = <<some value>>
   AND (case when isdefault = 'Y'
             then c_bpartner_id
             else null
         end) = <<another value>>
   AND isdefault = 'Y'

Realistically, you're often better off creating an actual function that you use in both your index definition and in your query so that you're not coding the CASE statement in multiple locations.


From the description of v$session:

  SECONDS_IN_WAIT
  If the session is currently waiting, then the value is the amount of time waited for the current wait. If the session is not in a wait, then the value is the amount of time since the start of the last wait.
  This column has been deprecated in favor of the columns WAIT_TIME_MICRO and TIME_SINCE_LAST_WAIT_MICRO.

  WAIT_TIME_MICRO
  Amount of time waited (in microseconds). If the session is currently waiting, then the value is the time spent in the current wait. If the session is currently not in a wait, then the value is the amount of time waited in the last wait.

These aren't cumulative measures, they're only related to the current or last wait event.

v$session_wait_class on the other hand sums up the times a session has been in different wait classes.

  V$SESSION_WAIT_CLASS displays the time spent in various wait event operations on a per-session basis.

  TIME_WAITED
  Amount of time spent in the wait class by the session

So it's understandable that the times indicated in [g]v$session_wait_class are larger than the ones you're looking at in [g]v$session.
The opposite could be true too though, depending on when a wait is accounted in the wait class view. If it's accounted once the wait event is over and not dynamically (I think that's what happens), the current wait could be larger than the sum of previous waits.


Oracle - Foreign Key on non Unique - is materialized view the answer?

If the concept identified by TXT_ID can be referenced by some other entity independently of the text language, then it deserves to be a separate entity represented by a separate table, as mentioned by RDFozz in a comment. Text translations will then become its children, as will DATADICT.


SQL Server 2008 posibilities of having views from one database in another database?

Yes it possible, that's what three part naming is for.

Use the form: [DatabaseName].[DatabaseOwner].[TableName] when referencing your vendor tables.
ie:

CREATE View [ViewName]
AS
SELECT * FROM [VendorDB].[dbo].[TableName]

Regarding performance, there's no big performance impacts, if the table has appropriate index and statistics these will be used by your queries in your DB.

Note: If you specify an index hint in your query, these will be ignored unless you bypass the view and refer to the table directly.

Location of your DB
As Stan noted, the above assumes that the two databases are residing on the same SQL Server instance, if you want to have a view residing in a different SQL instance (and I do not recommend this), you can:

Setup a linked server to the Vendor
DB
Use four part naming in your view definition: [LinkedServerName].[DatabaseName].[DatabaseOwner].[TableName]

ie:

CREATE View [ViewName]
AS
SELECT * FROM [VendorDBInstance].[VendorDB].[dbo].[TableName]


SQL Database Server Recovery and connections

Yes, your plan will work.  There are no hidden dependencies that will cause connections to fail.  The client doesn't know anything about the SIDs of the server or server logins.  Whatever the client might learn about the server is not retained from one session to the next.

If you want a bit of a smoother transition, you can run a script on the old server to create scripts that will create the logins on the new server with the same SIDs as the old server.  If you do this, you won't have to remap the database users to the server logins after restoring the databases.  See Sync users and logins.


Grant Admin to an Active Directory account in SQL Server

Some of what you are asking for is actually pretty simple.

Permissions for your AD Group

You create a login for your AD group

CREATE LOGIN [domain\AD Group] FROM WINDOWS

Then grant it the access you want.  You say you want to grant it Admin access but unless this is your DBA team I wouldn't add the AD Group to something like sysadmin.  Instead grant them the permissions you actually want them to have.  It sounds like you want

db_ddladmin - Role that grants permissions to create/modify objects within a DB.
db_datareader - Role that grants permissions to run a SELECT against any table/view within the DB
db_datawriter - Role that grants permissions to run UPDATE, INSERT or DELETE statements against any table/view in the DB.
EXECUTE - Permission that grants the ability to EXECUTE any stored procedure or function within the database.

To add this run the following on each user database.

CREATE USER [domain\AD Group] LOGIN [domain\AD Group]
ALTER ROLE db_ddladmin ADD MEMBER [domain\AD Group]
ALTER ROLE db_datareader ADD MEMBER [domain\AD Group]
ALTER ROLE db_datawriter ADD MEMBER [domain\AD Group]
GRANT EXECUTE TO [domain\AD Group]

If you want these permissions added to new user databases then run the script in the model database as well.

Connecting using your service account

If you have an application running under your service account then when that application connects using trusted authentication it will connect to SQL using that service account.  If however you want to connect to SQL using something like SSMS then you have two choices.  You can either log into a machine using the service account or run SSMS as a different user.

To run SSMS under a different user hold down the SHIFT key and right click on your shortcut to SSMS and select Run as different user

You'll then get a login/password prompt for an AD login.  Type in your service account name & password.  Once SSMS opens any trusted (windows auth) connections will be made using the AD login you connected under (your serviced account in this case).


Giving file system access to the SQLSERVERAGENT virtual account

To specifically answer your question, here's how you give disk access rights to the built in SQL Server Agent account. But read on as I answer what I believe to be the real issue:
1.> Right-click your drive, select properties, select security, click the Add button and enter the SQLSERVERAGENT account(make sure your domain isn't selected in the From this location text box, but rather your computer name):

2.> Click the Check Names button to validate that the account is valid:

3.>  Now, add the file permission you need to the SQLSERVERAGENT account. For trouble-shooting purposes, you may want to give Full control and then scale it back later as needed:

That being said, you probably just need to use SQL Server Configuration Manger to re-add the SQL Agent user--according to the comments I saw about msdb and logins. Configuration Manager makes more changes to SQL Server than using the Windows Services applet--so Configuration Manager should always be used to change ANY SQL Service.
This will fix the issue if someone may have changed the account within Windows Services causing the service to fail on startup. You need to reset it within Configuration Manager. Doing so allows Configuration Manager to add to SQL Server the much needed permissions to manage the MSDB database for the Local Service account (NT SERVICE\SQLSERVERAGENT) whereas changing accounts within the Windows services applet does not.
Caveat: Versions SQL Server Express above 2000 do not include a SQL Agent. Certain aspects of it may appear to be there, but its unusable in the Express version of the product.
To begin, open SQL Server Configuration Manager and double-click the SQL Server Agent service in the SQL Server Services. Select the Built-in account radio button and choose Local Service, and click the Apply button. Important : if you already see that this account is selected chose another account and click the Apply Button. Then, change it back to Local Service and click the Apply button to allow Configuration Manager to add the correct MSDB permissions for the SQL Agent service to start. Now, restart SQL Server Agent to reflect this new setting.


How to SELECT multiple COUNT(*) columns with each GROUP BY having a different WHERE clause?

This will give you the result you wanted but I'm not sure if it's the most flexible piece of code.

SELECT p.PersonId,
    SUM(CASE
          WHEN a.ActionTime >= '2014-01-15 00:00:00'
            AND a.ActionTime < '2014-02-15 00:00:00'
          THEN 1
          ELSE 0
        END) AS GroupA_Actions_Made,
    SUM(CASE
          WHEN a.ActionTime >= '2014-02-15 00:00:00'
            AND a.ActionTime < '2014-03-15 00:00:00'
          THEN 1
          ELSE 0
        END) AS GroupB_Actions_Made
FROM
    Person p
JOIN
    Action a on p.PersonId = a.PersonId
GROUP BY p.PersonId


MySQL lost records in a master slave scheme

My guess would be that there are three(3) aspects to look over:
ASPECT #1
I would be concerned with InnoDB settings because there are new features in InnoDB (starting with the plugin as of MySQL 5.1.38) that allow for accessing multiple CPUs. If you do not set the parameters, InnoDB will behave like it did before the enhancements. In fact, there are cases where InnoDB in older versions of MySQL will operate faster than newer version:

How do I properly perform a MySQL bake-off?
Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Using multiple cores for single MySQL queries on Debian
Is the CPU performance relevant for a database server?

ASPECT #2
I would be very carful when upgrading MySQL 5.1 to another version of MySQL 5.1 because MySQL 5.1 introduced the concept of installing plugins because you now have the table mysql.plugin. If you install a newer version of MySQL, make sure you do not hose mysql.plugin. You have to just reinstall the plugin. This, in itself, is not the cause of your problem. Nevertheless, you need to be aware of this in the event that this is a cause.
ASPECT #3
If you have not already done this, I would raise innodb_log_buffer_size to 32M. The default is 8M. Increasing it will reduce disk I/O. Please do not go beyond this, since large COMMITs can and will form I/O-based bottlenecks.
RECOMMENDATION
I would upgrade completely to MySQL 5.5 because all of the InnoDB Plugin is natively installed in MySQL 5.5. As highlighted in my previous links, you must configure and tune the settings needed for InnoDB to use multiple CPUs. Increase your log buffer
UPDATE 2012-06-03 18:30 EDT
Since you cannot install MySQL 5.5, you still have aspects 2 and 3 you could implement.

Install the InnoDB Plugin
Increase the innodb_log_buffer_size to 32M


"Error creating foreign key (check data types)…" but they do match…

It was a bug with phpmyadmin.

When you create a new constraint you usually are allowed to leave its name field blank, and it fills it for you.

This time, for some still unknown reason, this didn't work and gave this unrelated error.

Filling the name field did the trick, and allowed me to create the constraint.


Dealing with CXPACKET waits - setting cost threshold for parallelism

CXPACKET is never a cause; it gets all the blame, but it's always a symptom of something else. You need to catch these queries in the act and figure out what "something else" is. It might be different from query to query, and turning off parallelism altogether is - as you've suggested - unnecessary overkill in most cases. But it is often the least amount of work, which is why it is such a prevalent "fix."

If you can get an actual plan for a query that seems to be responsible for high CXPACKET waits, load it into SentryOne Plan Explorer. There's usually a reason behind this; we show which parallel operations led to thread skew, and you can easily correlate that to estimates that are off (we highlight operations with estimates that are off by a at least certain threshold). Usually the underlying problem is really bad/out-of-date (or unavailable) statistics.

Unfortunately what you'll find in sys.dm_exec_cached_plans are estimated plans. They won't tell you whether the plan went parallel when it was actually used, because the actual plan is not what's cached. In some cases you expect to see both a serial and parallel plan for the same query; this is not how SQL Server deals with the situation for parallel plans that might be parallel at runtime. (Lots of information about that here.)


What is meant by Orphan Lock and Orphan Sessions?

As the name suggests orphaned session is like a child that nobody knows whom it belongs to and in session terms, nobody know a specific session whom it belongs to, hence the name orphaned session.
Orphan session examples:

database table lock forever because client and server connection is broken
A user session that the IdP (identity providet) cannot determine what client it belongs to, this can happen with SSO when the user log out from SP (service provided) (example any website that uses SSO) and the IdP that provides the SSO (example login with facebook/google). If the user logs out of the SP but not the IdP as well, then the IdP would have all those orphan sessions that they don't know whom they belong to and are just hanging there.


building structure for employee office relation

What "datatype" is From_date?  Assuming it is DATE or DATETIME or TIMESTAMP, then this will find records before May, 2015:

select * from tbl_emp_office where From_date < '2015-05-01';

This will find records for only May, 2015:

select * from tbl_emp_office
    where From_date >= '2015-05-01'
      and From_date  < '2015-05-01' + INTERVAL 1 MONTH;

If you are using VARCHAR, well don't.

Edit to answer "Where was emp#1 working during May, 2015?"

SELECT  office_id
    FROM  tbl_emp_office
    WHERE  empid = 1
      AND  From_date < '2015-05-01' + INTERVAL 1 MONTH -- Started before/during May
      AND  ( To_date IS NULL          -- Still working there
         OR  To_date >= '2015-05-01'  -- Left during/after May
           );

Note:  If he worked in multiple offices during May, this will produce multiple rows.


MySQL quick database transfer between two servers

Option A
Definitely the safest, but long downtime not necessary if Option D is applied correctly. Your downtime would be based on collecting the incremental, not the initial load of the target server. This would be very short if the loading of incrementals are being done frequently.
Option B
This is good, provided the large tables are archive data only.
If the large tables are active logs, go to Option D
Option C
To ensure an rsync, mysqld should be down. To do so, run this
mysql> SET GLOBAL innodb_fast_shutdown = 0;

Next, run this
service mysql stop

Then, issue the rsync of the data directory. This will ensure the mysqld commits everything is has on hand when performing MySQL shutdown. Thus, when you run
service mysql start

the MySQL will not do any crash recovery during mysqld's startup phase.
If you decide to rsync a live server, you are not taking into account the InnoDB Buffer Pool that has dirty pages (changes needed to be flushed to disk). Starting mysqld from a live rsync would impose crash recovery using ibdata1's double write buffer and a scan for any changes (deltas) in the redo logs (ib_logfile0,ib_logfile1). This is risky in a write-intensive live database.
Option D
Doing --single-transaction mysqldump creates a convenient point-in-time backup. Doing the incremental using binary logs will require four(4) things:

Enable binary loggging on DB Server
Ensure that binary logs stay around by increasing expire_logs_days in case you delay doing the incremental load of changes a day or two
Use mysqlbinlog to dump events after the replicaton coordinates of the dump to create incremental data
Use --master-data=2 to record binary log and position of the dump (will appear on line 23)


Is it worth the time to review indexes suggested by index tuning advisers?

Remember it is just an advisor...just as in life they are not always right on the path that you should take. It's free advise to take or leave.

For the most part it is basing it off the DMV data in SQL Server so it can even fluctuate on what it sees as being an issue. I generally will them as a guide.

Example:

I see a common set of tables showing "suggested" indexes. Well that would tell me then maybe the current indexes, for those tables, need to be reviewed against the current load that is running. Index architecut can change over the life of an application, the indexes created when during release version 1.0 may not be the same once you reach version 5.0.

I would then maybe go use sp_BlitzIndex or Jason Strate's Index Analysis to look at the table and indexes. Maybe go through and capture the execution plans for thost tables, or pull them from cache.

Remember though, it is worth testing any index you add to a table if you can. If not and you are going through modifying indexes, then make sure you make a script of the current indexes so you can restore them.


What happens if log files storage is full?

Short answer: The database hangs and will not accept any further changes.  Read activity may or may not be able to continue.
Changes (insert, updates, etc.) are written to the Online Redo logs.  There are always a number of these arranged as a Circular Buffer and, once one of them is full, the database simply "moves on" to write into the next, "available" one.
The Online Redo Logs that are full are picked up the by the Archiver process, which dumps the redo data into an Archived Redo Log (ArchiveLog) file and clears the OnLine Redo Log, making it available for use again.  This usually happens in plenty of time, before the database needs to use that particular Online Redo Log file again.
If the disk containing the Archivelogs fills up, nothing happens - straight away.
The Archiver process will simply wait until it gets enough free space to write the ArchiveLog file and will not do anything else until it can.
Meanwhile, the database continues to fill up the OnLine Redo Logs with changes, periodically moving from one to the next, as they fill up.
The Crunch Point comes when the Archiver is "stuck" and the Online Redo Logs are all full up as well.  Now your database simply hangs.  It cannot accept any further changes because it has nowhere to record them.
By this time, the Alert log will be full of error messages saying that the Archiver is stuck.
You should only remove ArchiveLog files manually using RMAN.
RMAN knows which files it has to keep hold of so that it can recover your database and so won't get rid of anything you need.  Just deleting files by hand, you might.
Better still, you can tell the database to put the ArchiveLog files into the Fast Recovery Area, in which case Oracle database can manage them automatically.  You still need to setup and configure RMAN.


Accepted NTLM SPNs

The Accepted NTLM SPNs variable is needed when a server is known by more than one SPN. When a client attempts to connect to the server by using a valid SPN that the server does not know, service binding will fail. To avoid this problem, users can specify several SPNs that represent the server by using Accepted NTLM SPNs. Accepted NTLM SPNs is a series of SPNs separated my semicolons. For example, to allow the SPNs MSSQLSvc/ HostName1.Contoso.com and MSSQLSvc/ HostName2.Contoso.com, type MSSQLSvc/HostName1.Contoso.com;MSSQLSvc/HostName2.Contoso.com in the Accepted NTLM SPNs box. The variable has a maximum length of 2,048 characters. Accepted NTLM SPNs is on the Protocols for MSSQLSERVER Properties (Advanced Tab) in SQL Server Configuration Manager.

Microsoft Documentation


Is combining independent tables called normalisation?

No, the task you are being asked to fulfill —arranging (a) columns that belong in two or more distinct base tables in (b) a single base table— is not called normalisation (nor normalization for those who write it so).
Regarding the relational model, crated by Dr. E. F. Codd, normalization is a science-based procedure performed by a designer at the logical level of abstraction of a database which involves:

decomposing, by means of first normal form, a relation (table) with one or more attributes (columns) configured with nonatomic1 domains (types) so that, having all the relevant domains (types) fixed as simple or atomic, data manipulation and constriction are much easier to declare via the expressive facilities of the data sublanguage (e.g., SQL) provided by the database management system (DBMS for brevity) of use; and

getting rid of undesirable dependencies among the attributes (columns) of a relation (table), by virtue of split-ups based on the successive normal forms, in order to avoid update anomalies.

Of course, the designer has to take into account the meaning carried by the relations (tables) and attributes (columns) under consideration. The aforementioned meaning is assigned in terms of a modeling exercise that takes place at the conceptual level of abstraction, depending on the particular informational characteristics and rules of the business environment of interest. It is there, at the conceptual level, where the modeler carries out an integration of the structural aspects (entity types and properties) that hold business-related direct associations. Those conceptual elements are subsequently represented in a logical-level layout by way of the relations (tables) and attributes (columns) mentioned above, and normalization assists in testing the soundness of the logical-level representation.
Denormalization, in turn, is as well a science-based procedure applied at the logical level where, basically speaking, a designer takes a relation (table) that meets a certain normal form and produces a relation (table) that is in the normal form that is immediately “under” the previous one; e.g., a relation (table) that was in third normal form underwent a recomposition to its second normal form, so this procedure is sequential. Denormalizing a relation (table) that complies with first normal form generates an unnormalized one.
Although, in certain contexts, the term “denormalization” is informally (and ufortunately) used to denominate the approach in which a designer decides to put up —in an ad hoc manner— a table that includes, e.g., “summary” columns that retain calculated values and/or columns that belong and are also comprised in other tables, but that is decidedly a course of action which is not associated with the —theory-based— normal forms of the relational model. The task you describe (i.e., mixing in the same table some columns standing for aspects that are not directly associated at the conceptual level) seems to be compatible with this informal (and unfortunate) use of the term “denormalization”, but note that, for the record, I highly suggest not to use it when referring to this sort of approach because it only adds ambiguities and confusions.

Endnote
1 Domain (which is somewhat similar to a type) atomicity has much to do with the way in which an attribute (column) of a relation (table) will be utilized in the concerning database. A nonatomic domain is one that comprises one or more sub-structures that have their own specific significance and require separate constraints and/or will be involved in data manipulation operations accessing value sub-parts enclosed in said sub-structures (e.g., SELECT or UPDATE operations with WHERE clauses incorporating DBMS functions —let us say, SUBSTRING()— to transgress domain atomicity by “touching” value sub-parts).
In his seminal 1970 paper entitled A Relational Model of Data for Large Shared Data Banks, Dr. Codd expounds examples of relations (tables) with domains that accept relations (tables) as values; in other words, the concerning relations (tables) contain “nested” relations (tables) in some of their attributes. In practice, this kind of domain entails columns set up with, let us say, a TABLE data type which, if not handled as atomic, involves evident complexity (recursive “nesting” of columns, constraints, rows and the corresponding operations). To free those relations from said complexity, he presents normalization, the procedure by virtue of which a designer breaks down nonsimple (i.e., nonatomic) domains into atomic ones, obtaining relations (tables) in a more convenient (first normal) form. It is worth to note that, in agreement with the prior paragraph, attributes (columns) defined with domains accepting relations (tables) as values are not the only ones that can be considered nonatomic.
In this way, if each of the columns taking part in the combination process that you mention will always be constrained and used in value manipulation as a single indivisible unit, i.e., never with respect to value sub-parts, then their types will be managed atomically; if not, such a combination process would yield unnormalized tables, i.e., tables that do not comply with first normal form and, hence, not with the further normal forms either, so genuine relational normalization would be called for.


Json append results in error of - "No operator matches the given name and argument types"

My column type for "clusterCALogs" was json... It had to be jsonb.


cross self join to get more rows - better solution?

No, their is no better way then cross joining on yourself in MySQL. You can make the SQL slightly more elegant using, say, a CTE in other RDBMS, but what you have is the best way IMO.

By "best", I include "efficient" too: set based/no loops, and no temp tables


SQL Query; Admin Log System; Foreign Keys; Retriving multiple rows form different tables at once

The question is all over the place but I think this is what you are looking for

select a.name, u.name, b.note, b.date
  from table_B as B
  join table_A as u
        on u.ID = B.ID
  join table_A as a
        on a.ID = B.admin_id
 where B.ID = @userID

you can create a line with
select a.name + " took action on " + u.name  ...


Despite STATISTICS is updated, Estimated and Actual Row count is not same

As others have pointed out the most immediate problem is your use of the local variable. SQL Server creates the query plan for that query before it assigns a value to the local variable. You have an unknown input filtered using the >= operator. The legacy cardinality estimator in SQL Server has a rule that treats this as a special case. The cardinality estimate will always be 30% of the number of rows in the table with a non-null value for the filter column.

I don't know many NULL values you have but if you do the math it almost works out exactly:

  27451373 * 0.3 = 8235411.9

There are a few ways around this problem. You can use an OPTION (RECOMPILE) hint. That will force SQL Server to never use a cached query plan and it will create the query plan after the variable has been assigned. Most of the time you will see the actual value for the variable in the plan. The unknown estimate of 30% will no longer be used. One exception that I know of is SQL Server may parameterize trivial queries, even those that use a RECOMPILE hint. In that case you may not see the variable value in the plan. I assume that this table is part of a larger query. If so you should not have that problem.

Other options are to hardcode your filter values into the query, use dynamic SQL, or to use a parameterized query. My preference is the RECOMPILE hint unless your Unless your query is executed so many times per day that you cannot afford to recompile the plan.

I also want to point out that it's relatively uncommon for the estimated number of rows to exactly match the actual number of rows. To get an exact match you typically need to work with a simple query and/or you need to get lucky with statistics.


SELECT xyz INTO #Temp from SSMS is 5x longer in one environment than another

For me it sounds like issue with tempdb.. What you could do- check how many files you have on each server- you can see recommendations here.

Are they big enough and not growing when you doing inserts? What about log file of tempdb?
if they are growing- best would be to expand them before (and keep that way).

One more thing you can check- if they are growing, you can probably make it faster by using instant file initialization.


Pass variable as an input argument to a function inside another function in PostgreSQL

answer from a jjanes comment:
"The error indicates that for some reason left_bipolar contains a space just before the call. I guess convert_to_bipolar8 must put the space there. But we can't see the code for it, not what input you called the outer function with"


This replacement can be done with:
UPDATE tableName SET fieldName =
     regexp_replace(fieldName, '\yreports\y', 'reports_test', 'g');

From the documentation:

\y matches only at the beginning or end of a word

The last argument 'g' is meant to replace all occurrences, otherwise it would replace only the first found.
You may also add a WHERE clause to limit the rows updated to those that match:
UPDATE tableName SET fieldName =
     regexp_replace(fieldName, '\yreports\y', 'reports_test', 'g')
WHERE fieldName ~ '\yreports\y';


Is it possible to exclude specific users in log activity of PostgreSQL?

Using the ALTER ROLE ... SET parameter; command, it was possible to tailor user-specific log parameters. Note that the parameter only takes effect after logout.

Setting log_min_duration_statement=-1 (1st login):

psql console

P:\>psql -U postgres -h 132.156.208.45 -d my_db

my_db=# SHOW log_min_duration_statement;
 log_min_duration_statement
----------------------------
 0
(1 ligne)

my_db=# SELECT COUNT(*) FROM organisms;
 count
-------
   153
(1 ligne)

my_db=# ALTER ROLE postgres SET log_min_duration_statement=-1;
ALTER ROLE

my_db=# SHOW log_min_duration_statement;
 log_min_duration_statement
----------------------------
 0
(1 ligne)

my_db=# SELECT COUNT(*) FROM mv_rings;
 count
--------
 115270
(1 ligne)

my_db=# \q

Result on the log:

Note that within that session, all statements are visible in the log even after setting log_min_duration_statement=-1.

2015-10-15 14:14:01 EDT [unknown] my_db 123.456.789.012 postgres |LOG:  connection authorized: user=postgres database=my_db
2015-10-15 14:14:08 EDT psql my_db 123.456.789.012 postgres |LOG:  duration: 0.000 ms  statement: SHOW log_min_duration_statement;
2015-10-15 14:15:26 EDT psql my_db 123.456.789.012 postgres |LOG:  duration: 32.000 ms  statement: SELECT COUNT(*) FROM organisms;
2015-10-15 14:15:45 EDT psql my_db 123.456.789.012 postgres |LOG:  duration: 31.000 ms  statement: ALTER ROLE postgres SET log_min_duration_statement=-1;
2015-10-15 14:16:08 EDT psql my_db 123.456.789.012 postgres |LOG:  duration: 0.000 ms  statement: SHOW log_min_duration_statement;
2015-10-15 14:17:10 EDT psql my_db 123.456.789.012 postgres |LOG:  duration: 2059.000 ms  statement: SELECT COUNT(*) FROM mv_rings;
2015-10-15 14:17:29 EDT psql my_db 123.456.789.012 postgres |LOG:  disconnection: session time: 0:03:27.450 user=postgres database=my_db host=123.456.789.012 port=65269

Seeing the effect of log_min_duration_statement (2nd login):

psql console

P:\>psql -U postgres -h 132.156.208.45 -d my_db

my_db=# SHOW log_min_duration_statement;
 log_min_duration_statement
----------------------------
 -1
(1 ligne)

my_db=# SELECT COUNT(*) FROM germplasms;
 count
--------
 475290
(1 ligne)

my_db=# \q

Result on the log:

As expected none of the statements are logged.

2015-10-15 14:17:44 EDT [unknown] my_db 123.456.789.012 postgres |LOG:  connection authorized: user=postgres database=my_db
2015-10-15 14:20:27 EDT psql my_db 123.456.789.012 postgres |LOG:  disconnection: session time: 0:02:43.333 user=postgres database=my_db host=123.456.789.012 port=49372


I was able to split the table up as described above.  Since I did not know the data generation time a priori, the approach above did give a 6 to 10 times speed up on queries.


Update value in table based on condition in other table with trigger

CREATE TRIGGER `user_race_count`
AFTER INSERT
ON `tbl_map_race`
FOR EACH ROW
BEGIN
    IF 5 <= ( SELECT COUNT( /* DISTINCT */ user_id)
              FROM tbl_map_race
              WHERE race_id = NEW.race_id)
    THEN
        UPDATE tbl_race
        SET race_status = 'Full'
          , record_status = '2'
        WHERE race_id = NEW.race_id;
    END IF;
END


Oracle DB refresh on 11g using 10g Cold backup

Datapump command like impdp can be used for importing the 10g DB contents into the 11g DB.

Update:

The above datapump command is an example of logical backup which can be done when a database is in running mode.
Below are the common steps followed during a logical backup and restore:

Export(Backup) of full DB is taken in old server:

expdp system/password@db10g full=Y directory=DIRECTORY_ON_SERVER dumpfile=DUMPFILE_NAME.dmp logfile=LOGFILE_NAME.log
Dumpfile moved to new server
Import(Restore) dumpfile in the new server:

impdp system/password@db11g full=Y directory=DIRECTORY_ON_SERVER dumpfile=DUMPFILE_NAME.dmp logfile=LOGFILE_NAME.log

  More details on this can be found in -->
  https://oracle-base.com/articles/10g/oracle-data-pump-10g (Better than
  Oracle docs)

Now coming back to the actual question, what you are asking is a restore of the cold backup in 11g DB server.

The restore of the cold backup can be done by using RMAN command(s) as below:

RMAN> restore database from TAG='COLD_BACKUP_FILE_NAME'

I assume that you have taken backup of all the necessary files during the cold backup process.

Hope this helps you !!
Please share your progress with regard to this :)


the data in a database changes over time

This is the usual reason. While the concept of compiling a stored procedure is implementation specific, it usually involves producing a query plan for the procedure and/or the individual statements it is formed from, and storing it for later reuse (to save that part of the process being repeated every time the procedure is called).
As your data grows, the balance of data in each table/index may change in such a way that the optimal plan when the procedure was last compiled is now much more inefficient than other options so the procedure will no longer be performing as well as it could. If you had just a few rows in a table when the procedure was created (or last compiled) a scan might have been more efficient than one or more seeks for instance, but that may no longer be the case later. Also which index it is most efficient to touch first can change over time especially for queries on wide tables and those that join in several tables.
The database engine will usually have some heuristics built in to cause a recompile after massive data changes, as well as after structural changes, but these are usually quite conservative in their action so manually kicking off a recompile is sometimes necessary. Much the same as the heuristics involved in deciding when to resample index statistics histograms (which in tern feeds into the query plan decisions generally and in your stored procs).
Sometimes procs with parameters will need different plans to be efficient for different inputs - sometimes there are circumstances where it is beneficial to always for a recompile because the difference is so high that you don't want to risk using a slow cached plan (in MS SQL Server the WITH RECOMPILE hint is there to deal with such circumstances, or the per-statement OPTION (RECOMPILE) for a finer grained approach). Procedures complex enough that this is a major issue are often "code smells" which mean your design needs a tweak, though they are not always easily avoidable.
A final reason to recompile stored procedures, functions, views, and other programmatic objects, is when changes elsewhere are made in a way that breaks dependency checking, meaning an automatic recompile does not happen when it is actually needed. For instance:

You have a proc that uses a view on a table.
Someone drops and recreates the view (instead of using ALTER VIEW).
The dependency information proc->view is now lost.
The table is altered.
As the dependency chain is broken, the engine does not know that the statements in the procedure may need to be recompiled meaning that procedure may start failing completely or giving incorrect results.
A recompile (at this stage or after the view was recreated) fixes this.


The actual thing is the variable "innodb_autoinc_lock_mode" which control the behavior for auto_inc lock during generation of auto generated numbers. It can be set to three values which are 0 ,1 or 2.

The catch is the behavior while setting this, if you set it to 0 which ensures that gap is not produced it held the table level lock and parallel inserts are disallowed while if you set it to 1 or 2 it ensure the lock is not held at table level and it is recommended to be set to 2 as it is fastest and allows parallel insert.

Just to add there may be gaps if there are some deleted rows and auto increment ensures the uniqueness not sequence.

Hope it helps


Is this a normal set of MySQL privileges?

In reality, those three user accounts are actually quite dangerous. They pose a very great threat to test databases.

Unfortunately, mysql comes with full access to test databases. How can you find them ?

Run this query:

 SELECT user,host,db from mysql.db;

Upon installation of mysql, you will see two rows that give full access to any database named 'test' or whose first 5 characters are 'test_'.

Why is this a problem ???

Try running this command:

$ mysql -u'' -Dtest

You will have connected the test database without a password.

Now, create a table and load it with a row:

CREATE TABLE mytable (a int);
INSERT INTO mytable VALUES (1);

OK, big deal. Could you double this table in size 30 times ???

INSERT INTO mytable SELECT * FROM mytable;
INSERT INTO mytable SELECT * FROM mytable;
... (30 times)
INSERT INTO mytable SELECT * FROM mytable;

What do you get ?? A table with 1,073,741,824 rows. Easily, 4GB+.

Imagine creating any table of any size. How about creating a bunch of tables in the test database and freely accessing them at will ?

The best thing you can do under these circumstances is to run this query:

DELETE FROM mysql.db;

and restart mysql. Then, those three accounts will be properly rendered inoperative.

Give it a Try !!!

UPDATE 2011-09-12 10:00 EDT

This delete:

DELETE FROM mysql.db;
FLUSH PRIVILEGES;

is just what you need for an initial installation. However, if you have users already established, You can run this instead:

DELETE FROM mysql.db WHERE db IN ('test','test_%');
FLUSH PRIVILEGES;

This will remove the specific two DB permissions.

As I mentioned in my answer, the three permissions are very dangerous for test databases. Running this DELETE will neutralize those accounts for having full rights to test databases.


Recursive query to find shortest path in graph

A recursive solution where the connections alternate between names and company_names.

The output shows all paths between the starting and ending node:

WITH RECURSIVE
conn AS
( SELECT
      name, company_name, job,
      (ROW_NUMBER() OVER (ORDER BY company_name, job))::text
          AS rn,
      CONCAT_WS(', ', name, company_name, job)::text
          AS node,
      1 AS lvl
  FROM
      company
  WHERE
      name = 'Bob Ross'
  UNION ALL
  SELECT
      b.name, b.company_name, b.job,
      CONCAT(a.rn, '-', (ROW_NUMBER()
          OVER (PARTITION BY a.rn
                ORDER BY b.name, b.company_name, b.job))::text),
      CONCAT(a.node, ' - ',
             CONCAT_WS(', ', b.name, b.company_name, b.job)),
      lvl + 1
  FROM
      conn AS a
      JOIN company AS b
      ON (  (  a.company_name = b.company_name
           AND a.name <> b.name AND a.lvl % 2 = 1
            )
         OR (  a.company_name <> b.company_name
           AND a.name = b.name AND a.lvl % 2 = 0
            )
         )
      AND a.name <> 'Celine Dion'
      AND b.name <> 'Bob Ross'
)
SELECT r.*
FROM conn AS f
     JOIN conn AS r
     ON f.rn LIKE CONCAT(r.rn, '%')
WHERE f.name = 'Celine Dion' ;

Test at dbfiddle.uk


Preventing orphaned users in SQL Server 2008 R2

As I understand it, this is because the SID's of the logins don't
  match between our two environments.

You understand correctly!

  How could I make it so that the SID's of the sql server logins on our
  staging environment are the same as on our production environment
  going forward? My thought is to use CREATE LOGIN WITH SID ='SID value
  from PRD' but this has the disadvantages of needing to drop the logins
  first, and I'm not entirely sure that manually defining an SID is
  healthy / wise.

If the logins are already created, you'll have to drop them anyway - so that's a moot point. You can use the SSIS migrate logins task, sp_helprevlogin, CREATE LOGIN WITH SID =, and lastly you can make the database partially contained.

  Maybe there is a way to have the logins migrated with the backup? (if
  sp_help_revlogin has been available to manually copy logins, why don't
  backups have this option)

Logins are a server scoped principal and can have a 1 to many relationship with users. Thus it doesn't make sense to include a server scope item with a database scoped item, that's why users exist. What you're looking for is called partially contained databases, which let a special type of user exist which has a passed and can be used like a login though it lives in the database and not at the server level. It's only good for that database and there are some requirements.

https://msdn.microsoft.com/en-us/library/ff929071.aspx


Grant user access only to specific tables

So this assumes that you have already created the user on the server/database but a simple script should help

USE [Database]
GO

SELECT
 'GRANT SELECT
  ON [' + schema_name(schema_id) + '].[' + name + ']
  TO [InsertUser]'
FROM sys.tables
WHERE name LIKE '%something%';

and run the results in a new query window.  You could probably create a stored proc to do it as well to save for the future and just pass in the user and table name


Should the index on an identity column be nonclustered?

By default the PK  is clustered and in most cases, this is fine.
However, which question should be asked:

should my PK be clustered?
which column(s) will be the best key for my clustered index?

PK and Clustered index are 2 differences things:

PK is a constraint. PK is used to uniquely identify rows, but there is no notion of storage. However by default (in SSMS), it is enforced by a unique clustered index if a clustered index is not yet present.
Clustered indexes is a special type of index which store row data at the leaf level, meaning it is always covering. All columns whether they are part of the key or not, are stored at the leaf level.
It does not have to be unique, in which case a uniquifier (4 bytes) is added to the clustered key.

Now we end up with 2 questions:

How do I want to uniquely identify rows in my table (PK)
How do I want to store it at the leaf level of an index (Clustered Index)

It depends on how:

you design your data model
you query your data and you write your queries
you insert or update your data
...

First, do you need a clustered index? If you bulk insert, it is more efficient to store unordered data to a HEAP (versus ordered data in a cluster). It uses RID (Row Identifier, 8 bytes) to uniquely identify rows and store it on pages.

The clustered index should not be a random value.
The data at the leaf level will be stored and ordered by the index key. Therefore it should grow continuously in order to avoid fragmentation or page split. If this can not be achieved by the PK, you should consider another key as a clustered candidate.
Clustered index on identy columns, sequential GUID or even something like the insertion's date is fine from a sequential point of view since all rows will be added to the last leaf page.
On the other hand, while unique identifier may be useful to your business needs as a PK, they should not be clustered (they are randomly ordered/generated).

If after some data and query analysis, you find out that you mostly use the same index to get your data before doing a key lookup in the clustered PK, you may consider it as clustered index although it may not uniquely identify your data.

The clustered index key is composed of all the columns you want to index. A uniquefier column (4 bytes) is added if there is no unique constraint on it (incremental value for duplicates, null otherwise).
This index key will then be stored once for each row at the leaf level of all your nonclustered indexes. Some of them will also be stored several times at intermediate levels (branch) between the root and the leaf level of the index tree (B-tree).
If the key is too large,  all the non clustered index will get larger, will require more storage and more IO, CPU, memory, ...
If you have a PK on name+birthdate+country, it is very likely that this key is not a good candidate. It is too large for a clustered index. Uniqueidentifier using NEWSEQUENTIALID() is usually not considered as a narrow key (16 bytes) although it is sequential.

Then once you figured out how to uniquely identify rows in your table, you can add a PK. If you think you won't use it in your query, don't create it clustered. You can still create another nonclustered index if you sometime need to query it. Note that the PK will automaticaly create a unique index.

The non clustered indexes will always contain the clustered key. However, if the indexed columns (+key columns) are covering, there won't be any key lookup in the clustered index.
Don't forget you can also add Include and Where to a non clustered index. (use it wisely)

Clustered index should be unique and as narrow as possible
Clustered index should not change over time and should inserted incrementally.

It is now time to write some SQL which will create the table, clustered and nonclustered indexes and constraints.

This is all theoritical because we don't know your data model and datatypes used (A and B).


I did small tests Database compatibility level remains the same.

1.Created dummy database on 2008r2
2.Restored it on 2012

So while restoring,i could see version being changed,but after restoring compatability level remained the same.So you need to alter database to 110 compatibility level to get benefits of SQL 2012

Database 'backupdb' running the upgrade step from version 699 to version 700.
Database 'backupdb' running the upgrade step from version 700 to version 701.
Database 'backupdb' running the upgrade step from version 701 to version 702.
Database 'backupdb' running the upgrade step from version 702 to version 703.
Database 'backupdb' running the upgrade step from version 703 to version 704.
Database 'backupdb' running the upgrade step from version 704 to version 705.
Database 'backupdb' running the upgrade step from version 705 to version 706.

From Paul Randal Q&A sessions,Nakul blogs,below are the details of version.

These file versions are the internal storage format, and have nothing to do with the compatibility level of the database

http://beyondrelational.com/modules/2/blogs/77/Posts/19759/0311-sql-server-database-files-upgraded-from-version-nnnn-to-mmm-why-databases-cannot-be-restored-ba.aspx

For second question,i would recommend running the database upgrade advisor,please see below link for more details

https://technet.microsoft.com/en-us/library/ms144256(v=sql.110).aspx

http://beyondrelational.com/modules/2/blogs/77/Posts/19759/0311-sql-server-database-files-upgraded-from-version-nnnn-to-mmm-why-databases-cannot-be-restored-ba.aspx


Can I on purpose lock the admin out of an Azure SQLDB (as admin)

That is not possible. From books online:

  The database user corresponding to the Server admin login cannot be
  altered or dropped.

Reference: Controlling and granting database access to SQL Database and SQL Data Warehouse


What will happen with cached index or table when it is modified, will it stay in cache?

If by cache you mean shared_buffers, no, it won't be removed from there due to being changed.  shared_buffers is the primary place where it does get changed.  From there it will eventually get pushed out to the kernel, and then from there to disk.


We also found existing tools tricky to work with, so ended up hacking together a solution using the sqlalchemy library in python. Our general approach was to mirror the source database; hackily convert data types where necessary; create the target database; copy across the data in chunks.

For an example, see: https://github.com/MIT-LCP/oracle2postgres/blob/master/migration.ipynb. Using this approach, we migrated a fairly large database (~100 schema, with >1000 tables, some tables with >200 million rows) from Oracle to Postgres.

For more detail, see: https://github.com/MIT-LCP/oracle2postgres. The package can be installed with pip install oracle2postgres. If you hit problems when reusing the code, feel free to raise an issue and we'll do our best to help.


Copying datadir directory enough for backing up a database with InnoDB tables?

Please tell me you are not (and not even thinking of) using file system copying on a database server that's up and running? That is virtually guaranteed to lead to corruption of your copies, leaving your backups useless. You won't receive any errors - except, of course, when you try to restore :-)

It's not very clear from your post (correct me if I'm wrong), but you're now running your database on Linux? If so, then use Percona's XtraBackup for your InnoDB tables. This tool performs non-blocking hot backups of InnoDB tables.Take a look at my answer here for some MySQL backup options. For MyISAM, you could look at MyDumper if mysqldump isn't performant.

If your server is still on Windows, then you're out of luck. XtraBackup doesn't work on Windows, nor does MyDumper (AFAIK). You could check this out (disclaimer - never used).

Again, I'm not totally clear on what, exactly, you mean here.

  Now that I'm copying a database directory with a mix of InnoDB and
  MyISAM tables from Linux to Windows, would this approach work as well?

If you mean that you are copying the mysqldump from Linux to Windows, then yes, that will work as any file copy. If, on the other hand, you mean that you are copying (using the file system) data files on a running server and tranferring those copies from Linux to Windows, then your "backups" will be useless on both systems.

  Ain't InnoDB tables data spread across various places such as in
  ibdata1? I don't have to backup ibdata1 or anything right?

If you are backing up the database using mysqldump, then that is sufficient. If you are simply copying files from one location to another on a running database server, then it really doesn't matter what you do or don't copy, because your backup will be useless.

[EDIT] in response to the original poster's comments.

From here (correct answer)

  mysqldump -uuser -ppass --single-transaction --routines --triggers
  --all-databases > backup_db.sql

Just substitute your schema name for --all-databases. You say that your "table" is InnoDB - is the rest of the database/schema InnoDB? If not, you should make it so. You seem to think that the datadir has to be copied in some shape or form - it DOES NOT. The dump contains the necessary data to reconstruct your database.

In the event of a problem, you restore your database from the last BACKUP (i.e. the dump). There are other solutions which allow PITR (Point In Time Recovery), but they are more complex and replication can also address this issue.

See also these posts (1, 2).

My take on the content of these posts (check the reputations of the posters) is that with InnoDB, the above command will allow for a backup while at the same time permitting reads and writes to the database - note that performance may be affected.


Is it good or bad practice to have a fact table relate to the same dimension table multiple times?

I'd use the same dimension table. Especially for a date dimension
This is a Role Playing Dimension also here

I would not let a client tool (or an Oracle forum post!) determine my SQL Server DW design.


SQL Server - Grid Export, choose delimiter

The "9393 results found" message can be removed by putting SET NOCOUNT ON before your query.

I'm using SSMS 2016 and I see the option to use a custom delimiter for Results to Text by going to Tools > Options, then looking under Query Results > SQL Server > Results to Text

The "Output format" dropdown has a custom delimiter option.

If you are committed to using Results to Grid, then you might have to edit your SELECT columns, perhaps using QUOTENAME() or another method.


Why does a SELECT (JOIN on regex) gets 30x slower with +1 row (query plan stays the same)? Lack of memory?

Thanks for the full example.  With that and a little help from "perf" and "gdb", I traced the problem down to this:

src/backend/utils/adt/regexp.c:#define MAX_CACHED_RES   32

Once you are trying to hold 33 regexps in working memory and accessing them in a predictable cycle, each one pushes out of the cache the one that will be needed next, and so every regexp is recompiled every time it is needed.  This recompilation is slow.

I don't know why 32 was chosen for that value, but clearly the cache can't be made unlimited in size.  Maybe it would be nice of this were user-settable rather than compiled in.

With this knowledge, it is easy to tweak the query to change the order of the join execution so that one regexp is tested to completion before moving on to the next one:

SELECT t2.id, t1.id FROM sample t1
right JOIN (select * from regex_set limit 33) t2 ON t1.data ~* t2.regex

However, if there are regexp in t2 which match no rows in t1, then this query will start returning null-extended results for those rows, which is a change in behavior from your existing query.  And if you try to add a simple WHERE clause to filter out those extra rows, then the planner will see through your trick and go back to using the slow join order.  You could try to use a non-transparent equivalent to filter them out:

SELECT t2.id, t1.id FROM sample t1
right JOIN (select * from regex_set limit 33) t2 ON t1.data ~* t2.regex
where coalesce(t1.id,-5) != -5

(assuming real t1.id are always positive)

But perhaps the best solution to this problem is to build a speciality index which supports regexp queries:

create extension pg_trgm ;
create index on sample using gin (data gin_trgm_ops);

The prospect of using that index will inherently require the faster join order to be used, and the actual use of the index will make the query faster as well.


You wrote: I really look for a solution, that generates the pk from the data, not row number etc.  - Would calculating a checksum and using it as a PK value be suitable for you?

Consider the following: a table with over 9 million rows, loads of duplicates, no primary key (this is just what I imagine your raw data set looks like ... I've used MySQL 5.7).  If we concatenate all values of a row, and have the SHA1 sum calculated, we get a rather good "fingerprint" of each row (yes, I know that SHA1 collisions have been found.  However, maybe this algorithm will be good enough to satisfy your requirements. Article on SHA1 collisions: https://www.theregister.co.uk/2017/02/23/google_first_sha1_collision/).

-- small table
create table t (
  n1 int
, v1 varchar(32)
, n2 int
, v2 varchar(32)
, n3 int
);

-- 25 rows, 23 unique rows
insert into t values
(1,'aaa', 1000, 'ttttt', 1111),
(1,'bbb', 1001, 'ttttt', 2222),
(1,'ccc', 1000, 'ttttt', 3333),
(1,'ddd', 1001, 'ttttt', 1111),
(1,'eee', 1000, 'ttttt', 2222),
(1,'aaa', 1001, 'ttttt', 3333), -- duplicate
(1,'eee', 1000, 'ttttt', 4444),
(1,'aaa', 1001, 'ttttt', 3333), -- duplicate
(1,'eee', 1000, 'ttttt', 5555),
(1,'aaa', 1001, 'ttttt', 3333), -- duplicate
(1,'bbb', 1000, 'x', 1111),
(1,'aaa', 1000, 'xx', 1111),
(1,'bbb', 1001, 'xxx', 2222),
(1,'ccc', 1000, 'xxxx', 3333),
(1,'ddd', 1001, 'xxxxx', 1111),
(1,'eee', 1000, 'y', 2222),
(1,'aaa', 1001, 'yy', 3333),
(1,'eee', 1000, 'yyy', 4444),
(1,'aaa', 1001, 'yyyy', 3333),
(1,'eee', 1000, 'yyyyy', 5555),
(1,'aaa', 1001, 'z', 3333),
(1,'bbb', 1000, 'zz', 1111),
(1,'aaa', 1001, 'zzz', 3333),
(1,'bbb', 1000, 'zzzz', 1111),
(1,'aaa', 1001, 'zzzzz', 3333);

Use the small table to create a bigger table -> we get the actual test data.

create table tbig
as
select t1.n1, t1.v1, t1.n2, t1.v2, t1.n3
from t t1, t t2, t t3, t t4, t t5;

-- output:
Query OK, 9765625 rows affected (24.23 sec)
Records: 9765625  Duplicates: 0  Warnings: 0

Create a table that has an additional column, containing the SHA1 for each row.

create table tbig_sha1
as
select
  sha1(concat(n1, v1, n2, v2, n3)) sha1
, n1, v1, n2, v2, n3
from tbig;

-- output:
Query OK, 9765625 rows affected (38.22 sec)
Records: 9765625  Duplicates: 0  Warnings: 0

Detect and eliminate duplicates (this may need: SET sql_mode = '';).

create table t_sha1
as
select sha1, n1, v1, n2, v2, n3
from tbig_sha1
group by sha1
order by sha1;

-- output:
Query OK, 23 rows affected (18.86 sec)
Records: 23  Duplicates: 0  Warnings: 0

Final result:

mysql> select * from t_sha1;
+------------------------------------------+------+------+------+-------+------+
| sha1                                     | n1   | v1   | n2   | v2    | n3   |
+------------------------------------------+------+------+------+-------+------+
| 0401e846eac37342dd8de769f3385d3090b9c47c |    1 | aaa  | 1000 | xx    | 1111 |
| 09c837f5a4dfa77dc25e37151ba279410ab82cd4 |    1 | aaa  | 1001 | yy    | 3333 |
| 2cde8e392e5f20c7735add5cc073466bbee77830 |    1 | aaa  | 1001 | z     | 3333 |
| 479de10da975efa02a407709d42bf6bc82c08a5e |    1 | bbb  | 1001 | ttttt | 2222 |
| 59e7b2fd58dcd2047289daf41ed165a19f2d5caf |    1 | ccc  | 1000 | xxxx  | 3333 |
| 6222c427cb2c014f982a25a2b684d3c989975164 |    1 | aaa  | 1001 | zzzzz | 3333 |
| 65715bccb92e92b5e95df308bc30aa1e70ec308c |    1 | aaa  | 1001 | zzz   | 3333 |
| 726a867623269a178fa002287448278038e92140 |    1 | eee  | 1000 | yyyyy | 5555 |
| 750054b78741acab58ba474be62ce2bcd9976ed8 |    1 | eee  | 1000 | y     | 2222 |
| 87c720132853afa3ce728caa425482a37038e617 |    1 | aaa  | 1000 | ttttt | 1111 |
| 8bcc29485d571c865de8f62d779dd1b474ddc9de |    1 | bbb  | 1000 | zz    | 1111 |
| 9df67a1d57fa702e4ce2f903ae312153aee09400 |    1 | bbb  | 1001 | xxx   | 2222 |
| a725ff1ae11a9b468392450ef1bffb28991c3b8d |    1 | bbb  | 1000 | zzzz  | 1111 |
| a8b3d416da6c222572ce63dd4ff3212a31a8b339 |    1 | eee  | 1000 | ttttt | 2222 |
| b232dd78cf258899c6df9535013c3fff2b1a835c |    1 | ddd  | 1001 | ttttt | 1111 |
| bb7e24d2df9bc71df1596a081576324ae214273a |    1 | eee  | 1000 | ttttt | 5555 |
| c062cca2e1b3d6d72c4c8420d4810115d5107136 |    1 | aaa  | 1001 | ttttt | 3333 |
| ce210358b4f24e767be6ca223cfe22d51abd563f |    1 | eee  | 1000 | ttttt | 4444 |
| e9cf971e943bcb885bd8ec10a15cb7f1325ad9f7 |    1 | aaa  | 1001 | yyyy  | 3333 |
| f57524ca9bd6a64e155b35f660a0170cdf59e518 |    1 | bbb  | 1000 | x     | 1111 |
| f7e905f522eeb34e57e87ba9e2b5e298fbe00246 |    1 | ccc  | 1000 | ttttt | 3333 |
| f8b900611fdba9592fd28417614ef93fb59dbe6f |    1 | eee  | 1000 | yyy   | 4444 |
| fe64ffc09e699b927f9e01ad790662e2de1026f5 |    1 | ddd  | 1001 | xxxxx | 1111 |
+------------------------------------------+------+------+------+-------+------+
23 rows in set (0.00 sec)

Then, we can use the "sha1" column as "PK".  Notice that we only have 23 rows now (the duplicates have been removed).

alter table t_sha1
add constraint pksha1
primary key (sha1);


Why are there multiple plans for query under forced parameterization?

Forced parameterization <> forced single plan.

However, the Query Store does have a feature to force a single plan if that's what you're after. You can see the button for it right in your screenshot.

But, if your concern is more general as to why you're getting multiple plans in the first place, you should start by making sure the same query is being executed each time. In your case, since you're using GETDATE(), the query is actually different every time it runs, so I wouldn't expect it to yield the same plan necessarily based on that alone.

You also need to be aware of the fact that the statistics of the underlying tables could be changing over time as well, which can heavily impact plan estimations. There are lots of other factors that play into it - this article digs deeper into what goes into an execution plan and what can affect them. The database is a living thing and plan changing shouldn't be inherently viewed as a bad thing, unless it is causing obvious performance issues.


As you are on SQL Server 2014, you can simply harvest historical deadlocks from the system_health Extended Events (XE) session and do some analysis.  It should be running all the time and will contain deadlocks since that last SQL Server service restart, although the ring_buffer could have cycled and ejected some older records.  Try code from here or here.  This
No need for Trace flag 1222 and no need to run Profiler / server side trace with the Deadlock Graph event which can only capture deadlocks from the time you start them / turn the trace flag on.  You can just look back in time.

As the WSUS database is relatively public domain (and not your secret business logic database) you could publish some of the deadlock XML here and we can help with the investigation.  Deadlocks are sometimes due to missing indexes, or if there are excessive exchange events then might be down to parallelism.  Let's have a look!


Delete back ups more than 2 weeks in sql server

Yes, it looks like you're using Ola Hallengren's backup scripts which operate in hours, so 14 days * 24 hours = 336 hours.

Every time this is run, a full backup of the user databases will be taken and any copies older than 2 weeks will be deleted.

Edit:

Based on the OP's edit, you are fine to schedule a job executing your first script as a replacement for the second script you posted. The frequency of this will depend on your backup and restore needs for your business.

As Kris said, this delete only occurs after a successful backup unless you specify the parameter @CleanupMode = 'BEFORE_BACKUP'


Here is what you can do to start

Run the following on the old PRIMARY

var dt = new Date(db.serverStatus().localTime - db.serverStatus().uptime*1000).toString() ; dt

This will print the exact time mongod was started.

PRIMARY failover was triggered 2015-03-03T12:18:41.540-0500.

If dt is close to 2015-03-03T12:18:41.540-0500

mongod crashed or was restarted
Check the mongod Log File on the old PRIMARY for that same timeframe

If dt is not close to 2015-03-03T12:18:41.540-0500

mongod is still running fine
The old PRIMARY simply became unreachable
Check network logs along port 27017


How to escape XML characters in TSQL before converting to XML?

Use for xml path to create XML instead of casting.

select @YourVariable for xml path(''), type

Empty string in the path expression and the absence of an alias on the returned column will give you your string back as xml.


How to remove Alter, Drop, Create SQL from deployment script created in VS 2015 using SSDT 14.06

I've only ever seen that code get scripted in two situations:

When the "Always re-create database" advanced publish option is checked.

That option can be set in multiple ways depending on how your deployment process works.

Here it is in the user interface, when you do a one-off publish or save a re-usable ".publish.xml" file:

Without using the UI, you can see this in the publish file XML:

<CreateNewDatabase>True</CreateNewDatabase>

Note that the default is "False", so the absence of the element means false as well

You can also pass this as a command line parameter to sqlpackage.exe:

/p: CreateNewDatabase=True

When the TargetDatabase doesn't exist

If the target database doesn't exist, then that is added to the script for safety reasons to just make sure it doesn't fail on creating the new database.

You'll need to double check that these properties in the publish XML point to a valid database that exists:

<TargetDatabaseName>YourDatabaseName</TargetDatabaseName>
<TargetConnectionString>Data Source=...etc...</TargetConnectionString>

And here it is in the UI:

If you could share more details about how you generate the publish script (the step-by-step of what you do, e.g. right-click on the SSDT project in Visual Studio, Publish, etc), we can help track down why that's showing up for you.


Can SQL Agent job notifications be sent to one operator for success and another for failure?

Quite frankly, I would just not use the limited operators facility through the UI and instead:

Create a step that only gets reached when the job succeeds, which e-mails person 1
Create another step that only gets reached when any step fails, which e-mails person 2

(You may need to modify multiple steps that could potentially fail, such that when they do fail, processing moves immediately to the second step above.)

You can still use operators if you like, just have the steps manually call sp_notify_operator.


MySQL add few column exception for TRIGGER UPDATE FOR EACH ROW

It's hard to give you an exact answer without knowing what RDBMS you are using or what your table schema looks like. But here is an example for SQL Server.

CREATE TRIGGER dbo.TR_MYTABLE_UPDATESTAMP
ON  dbo.[MYTABLE]
AFTER INSERT,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE I SET I.[MyTableUpdateDate] = GETDATE(), I.[MyTableUpdateBy] = SYSTEM_USER
    FROM INSERTED I
    INNER JOIN [MyTable] T ON T.MyTableId = I.MyTableId
    WHERE IsNull(T.[NumericColumnA], 0) <> IsNull(I.[NumericColumnA], 0)
    OR IsNull(T.[StringColumnB], '') <> IsNull(I.[StringColumnB], '')
    OR IsNull(T.[DateColumnC], '01-01-1950 00:00:00') <> IsNull(I.[DateColumnC], '01-01-1950 00:00:00')
END
GO


How to find values missing from a sequence?

One easy way is to generate a list of all potential documentnos and remove from that all the ones that exist:
select 'YD4BC' || generate_series(2006004, 2006015) as missing_documentno
except
select documentno from documents;


how to reduce transaction log size automatically?

You need to first understand what type of recovery (from a point in time standpoint) you want to get out of your SQL Server environment.

Do you want to be able to restore to a specific point in time? Then you want to remain in full recovery mode and you need to start taking transaction log backups (the log file will continue to grow in full mode until you take a log backup, as described in the linked blog post below)

If you are fine going to just the last full backup and not a specific point in time, then you can switch to simple as @andomar's answer suggests. (in this model, if you backup at 8PM each night and have a failure at 7:30PM, you have to go back to 8PM last night. This is fine in many applications but you need to be sure your business is okay with this. This blog post talks about customer expectations around recovery.

If you want to reduce the size of your transaction log file after it has grown due to mismanagement you can shrink. But I would NOT shrink the entire database as another answer suggests. That will cause other issues. It is generally speaking not a best practice to ever shrink your database. I would do a SHRINKFILE instead.

DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS

This command is described here.

If you want to understand the mechanics deeper, I wrote a blog post a few years ago that discusses transaction log growth. Proper management is best because then you won't have to worry about finding an automatic way to shrink it.


Managing version control of Erwin file changes with multiple environments

Erwin is a great tool for managing logical schemas but rarely being used to manage code (package, procedure, etc…) and content management.

Some editions of Erwin have a built-in version control, but again only for the artifacts being managed by Erwin.

TeamWork by dbMaestro (which I'm part of) does a real Database Version Control and Database Deployment.
As many of our customers do have Erwin, we recommended them to use Erwin on a dedicate environment so the flow will be like Erwin -> Dev -> QA -> …

Changes being made in the Erwin environment are promoted to Dev using TeamWork Deployment Manager and the Dev, QA, etc… environments are managed by TeamWork version control.
This gives you the ability to have logical design by Erwin and a real database version control for the physical layer by TeamWork. I hope I was helpful.


Shrink Database Did Not Reclaim Much Space

When using the DBCC SHRINKDATABASE statement, you cannot shrink a whole database to be smaller than its original size. Therefore, if a database was created with a size of 10 MB and grew to 100 MB, the smallest the database could be reduced to is 10 MB, even if all the data in the database has been deleted.

However, you can shrink the individual database files to a smaller size than their initial size by using the DBCC SHRINKFILE statement. You must shrink each file individually, instead of trying to shrink the whole database.


SQL Server Express Edition Primary - Secondary Model

An answer to Replication from one SQL Server Express to another on Stack Overflow offers some suggestions:

In summary (from the link):

Write an application that transfers "articles" from your "publisher" db to your "subscriber" db(s).

OR

Create a set of views to have a summation of data that you want to be
published. Then create INSTEAD OF triggers on these views (you can't
create an AFTER/FOR trigger on a view) to process that data and
transfer it to your "subscriber"(s).

Triggers On Views? What For? by Michael J Swart has some good information on view triggers.


Are Availability Group Databases on a Secondary replica automatically set to read-only by the AG?

I would give the docs a good read. Particularly the section on availability replicas.

  Every availability replica is assigned an initial role-either the primary role or the secondary role, which is inherited by the availability databases of that replica. The role of a given replica determines whether it hosts read-write databases or read-only databases. One replica, known as the primary replica, is assigned the primary role and hosts read-write databases, which are known as primary databases. At least one other replica, known as a secondary replica, is assigned the secondary role. A secondary replica hosts read-only databases, known as secondary databases.

As can be seen above, each replica can either be read-only or read-write (secondary or primary respectively). In addition there can only be one primary (read-write) replica. When we refer to replica in this respect, we are referring to the database level. Each server can host multiple availability groups, with potentially mixed primary-secondary configuration.

The situation you are referring to with multiple read-write replicas is not supported by AlwaysOn Availability Groups, which are designed to meet High Availability and Read-Scale needs, not multi-master setups. You should look at two-way replication technologies to support this need.

As for the read-only setting itself, this is automatically set by the availability group, although it's worth saying that this is not the same setting as the "Database Read-Only" setting found in the database properties (or is_read_only in sys.databases), nor is it the same setting as the file group level read-only option.


Mysql hand machine due to high load average and CPU

From your screenshot, it seems you are running on a machine just under 4GB of RAM. You also have ~4GB of SWAP space that MySQL could use if necessary. A rough calculation of how much memory your settings will allow is ~11.5GB.

From mysqlcalculator.com:

Due to the complex nature of memory allocation, this is an estimate of a worst-case scenario only.

Out of the box, your join_buffer_size is extremely large. This is a per-session thread that can be allocated multiple times in a query:

  For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.

I recommend setting this back to the default (in 5.6, that is 256kb). If you find many queries that are starting to show 'join_buffer' in the explain plan, then you should examine them for better index usage. If that fails, you might consider increasing join_buffer_size at the SESSION level for specific queries.