The limit is not on the collection size, but on the document size. There are ways that you can store the document of a higher size.

Having very large Documents will surely affect the performance of the MongoDB. You can surely split these documents into multiple documents in the same Collection.

For example,
You can divide the Documents to be stored Date wise, I mean, if you were writing all the data related to some X constraint into a document, then split it into X Constraint, Date 1 as one document, X Constraint, Date 2 into a different document. You can split in any logical way you feel is right for your Database design.

Or if you Document is very granular in nature and can't be split, then you have 3 options
1. Use Mongo's GRID FS
2. a relational approach to store the part of the data in a different Collection
3. Depending on the number of reads, number of writes and the size of the Document, choose the best possible NoSql Solution.

If you need a more specific answer, please update the question with relevant details.


Verifying password policy on existing users

This may not be popular among your users, but I believe the only way you can know for sure is to force a password change for every SQL login with CHECK_POLICY = ON. This will generate a set of ALTER LOGIN commands with blank passwords, you can update the query giving them all a common password or manually update each one with individual passwords - just make sure they meet your policy. Of course you need to be sure that the password policy is as complex as you expect, and that it is enabled (Control Panel > Administrative Tools > Local Security Policy > Account Policies > Password Policy > Password must meet complexity requirements).

SELECT N'ALTER LOGIN ' + QUOTENAME(name)
  + N' WITH PASSWORD = N'''' MUST_CHANGE, CHECK_POLICY = ON;'
  FROM sys.sql_logins
  --WHERE is_policy_checked = 0;

Steve Jones wrote about this a while back. Note that - due to what I uncovered below - you can't rely on is_policy_checked = 1 to mean that the password actually meets your current policy, since the login could have been created with a hashed password (in which case the plain text password can't be checked) or while the local complexity policy was disabled (which still leads to is_policy_checked = 1).

Another approach I thought would work would be to try to create a copy of every login with their current password_hash and with CHECK_POLICY = ON, and make a note of every one that fails. However, this can not work - even with CHECK_POLICY = ON, it doesn't perform any validation of an already-hashed password. I'll include the code for posterity - but, by design, the policy simply can't be checked.

SELECT N'BEGIN TRY
  CREATE LOGIN ' + QUOTENAME(N'copy_of_' + name)
    + N' WITH PASSWORD = '
    + CONVERT(NVARCHAR(255), password_hash, 1)
    + ' HASHED, CHECK_POLICY = ON;
  DROP LOGIN ' + QUOTENAME(N'copy_of_' + name) + ';
END TRY
BEGIN CATCH
  IF ERROR_NUMBER() = 15118
    PRINT N''' + REPLACE(name, '''', '''''')
      + N' was not complex enough.'';
END CATCH'
FROM sys.sql_logins;

Personally, I think this is a bug. If the syntax allows me to create a login using a hashed password, and I can stipulate that that password must meet my complexity policy, it should generate an error or warning that the policy was not, in fact, checked.

UPDATE: I filed a bug against this behavior.

CHECK_POLICY is a no-op with CREATE LOGIN ... PASSWORD ... HASHED


Given the fact that you mentioned you have Master-Master replication mode, I would not recommend any automatic failover unless you properly account for Replication Lag. After all, MySQL Replication is asynchronous.

It is theoretically possible to have the following:

DBServer1 as Master to DBServer2
DBServer2 as Master to DBServer1
DBVIP pointing at DBServer1
DBServer2 is 180 seconds behind
DBServer1 goes down
Automatic Failover moves DBVIP to DBServer2

With this scenario, DBServer2 could have auto increment keys that do not exist yet. Upon failover, the DBVIP will allow WebServers to connect to DBServer2 and ask for data that does not exist yet.

This would therefore require background processes running on each DBServer.

For the above scenario:

DBVIP is on DBServer1
DBServer1 runs HeartBeat
DBServer2 runs HeartBeat
Background Process on DBServer1 to monitor

a) Data Mount Availability
b) Data Mount Writeability
c) MySQL Connectivity
Once a,b, or c fail, kill HeartBeat

Background Process on DBServer2 to make sure DBVIP is pingable

What should killing HeartBeat do? Trigger the startup script defined for it.

What should the startup script on DBServer2 look for?

Loop until DBVIP is unreachable via ping
Connect to MySQL and

Run SHOW SLAVE STATUS\G in a Loop until Seconds_Behind_Master is NULL
RUn SHOW SLAVE STATUS\G in a Loop until `Exec_Master_Log_Pos stops changing

Assign DBVIP to DBServer2 via ip addr add

This is essentailly the algorithm for failing over safely to a Passive Master in a Master/Master Replication Cluster.

ALTERNATIVE

If ALL your data is InnoDB, I recommend something with less rigor. Perhaps you should look into using DRBD and HeartBeat. Here is why:

DRBD provides network RAID-1 for a Block Device on two servers.

You would essentially do this:

Have DBServer1's DRBD Block Device as Primary
Have DBServer2's DRBD Block Device as Secondary
Mount DBServer1's DRBD Device on /var/lib/mysql
Startup MySQL on DBServer1
Have HeartBeat Monitor Ping Activity Between Servers

What would startup script look like in a DRBD scenario?

Loop until DBVIP is unreachable via ping
Kill HeartBeart
Disconnect DRBD
Promote DRBD to Primary
Mount DRBD on /var/lib/mysql
Start MySQL (InnoDB Crash Recovery Fills in Missing Data)
Assign DBVIP via ip addr add

This is a lot more straightforward because only one side is Active. The Passive side (DRBD Secondary) is a Synchronous Disk Copy of the Active Side (DRBD Primary).

CAVEAT

If all or most of the working set data is MyISAM, do not touch DRBD. Crash scnearios quickly result in MyISAM tables being marked crashed and need auto-repair (which can be paintfully slow to wait for).

UPDATE 2012-12-29 08:00 EDT

Here are my past posts on using DRBD with MySQL

Mar 29, 2011 : MySQL high availability, failover and replication with Latency
Aug 29, 2011 : MySQL Replication : 1 Slave / Multiple Masters
Dec 19, 2011 : Best way to setup master to multi master replication
Jul 25, 2012 : Mysql database replication on different vlan/subnet/another site (I stirred a pretty big pot on this one in someone else's blog)


Wrong data type while using linked tables in MS Access

Bigint is not a datatype supported by access. If you wanted the value to stay as a numeric value in access you would have to change the source column in the SQL server table to numeric(19,0).

https://msdn.microsoft.com/en-us/library/ms714540%28v=vs.85%29.aspx


The roles db_owner and sysadmin are special cases and can't be denied anything.  My guess is that db_ddladmin, db_datareader, db_datawriter and execute access will give you everything you want instead of using db_owner.


Table view created with triggers, someone please explain why?

The reason is because the skills for evaluating competence in any discipline are the same skills for competence itself. They thought this design was a good idea because they weren't very skilled in database design, and thus were not skilled enough to recognize that it was a terrible idea.


Determine last time a mysql table was accessed (i.e. SELECT'ed)

It is not possible to view accessed information unless and untill General Query Log is enabled in Mysql
you can enable general query log in mysql 5.5 as follows, which would help you in future
Here's how in Dhanesh Mane's words:

OS : Ubuntu 12.04.1 LTS
MySQL version : 5.5.28
Locate and edit my.cnf file. We need to change two variables in this file to enable general query log.

sudo gedit /etc/mysql/my.cnf
Locate this variable in the file : general_log_file
It must have some default value, this variable is nothing but the path of log file.
The default value is "/var/log/mysql/mysql.log", you can change it as per your wish.
Now locate variable "general_log", and set it to 1. The means turning on the log system.
Now restart the MySQL server by running : sudo service mysql restart
now if you open the mysql.log file, you can see all SQL queries in it

Hope this helps!


You must install the Microsoft Access Database Engine 2016, as Justin Onstot already wrote (https://www.microsoft.com/en-us/download/details.aspx?id=54920), and ALSO you MUST run the correct version of Import/Export application (32 or 64 bit) to be successful. You have a 64-bit SQL Server, so you must install the 64-bit Access Database Engine and perform the following steps:

Go to Start Menu -> Microsoft SQL Server 2017 ->  SQL Server 2017 Import and Export Data (64-bit)
Open the application and try to import data using the "Excel 2016" option, it should work fine.

Hope this helps.


How to update a single record in a table based of foriegn key ID in another table?

OK, so I'm not going to comment on the table design as I assume that this is just part of the tables.

To do the update that you are looking for there's a couple of options.

You can get the Permission ID and put it into a variable, then use that against the permission table.

DECLARE @PermissionId INT

SELECT @PermissionId = PermissionId
from Employee
where EmployeeId = 2

UPDATE Permission
SET Read='T', Write='T', Delete='T'
where Permission = @PermissionId

Or you can do it all with a join in your update statement.

UPDATE p
SET Read='T', Write='T', Delete='T'
FROM Permission p
JOIN Employee e ON p.PermissionId = e.PermissionId
WHERE e.EmployeeId = 2

In either case you'll want to properly parameterize the query in your application layer to prevent against SQL Injection.


This can be a client error, not a server error. That is, the server responds (as you note) to other clients but not a certain one.
Summary

remove all client aliases
ensure tcp is default and top protocol
ensure servername\instancename only is used

Background
It is possible that there is a "client alias". The client alias will basically "hijack" servername\instancename and overrides port, protocol, instance, server etc. Personally I don't like them.
This can be done explicitly with the "SQL Server Configuration Manager" or by the Control panel ODBC thing. Check and remove it. Alternatively, you can poke around in the registry and remove it under:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

A second option is that port is specified. If I use servername\instancename,1234 then this is effectively servername,1234. Instancename and resolution of instancename via SQL browser are not performed.
A third option is that the default protocol is named pipes not tcp. This can happen if someone has messed around with "SQL Server Configuration Manager" and is liable to show on server migrations when you drop np or such from the new server. However, you also fix this in the registry too.
There are more exotic option such as hard coded HOSTS file or wrong DNS. But generally is the a fault of the SQL Server client configuration.


Ok, PostgreSQL does not directly support foreign keys and partitions. You can create a foreign key on each partition, but there is no way to have a foreign key against a set of partitions without either coding the constraint triggers yourself or using a key management table.

There are several ways to resolve this, but they require some understanding of underlying db implementation concepts to make perform well.

The first is to do your referential integrity enforcement manually through triggers.  You can write constraint triggers which can be deferred (if needed) that check the existence of foreign key information.  Note this is subject to ALTER TABLE DISABLE TRIGGER ALL issues but it should generally work.

The second is to have a separate table which stores keys and is maintained through a combination of triggers and bulk data operations.  This can help a great deal.

So it is possible to enforce things but it requires a bit more work.

I don't know enough about MySQL's table partitioning capabilities to comment on it though.


Moving DBs (including System DBs) to new LUNs on a single node cluster

As long as the path names remain the same for your database files, this approach should work.  This assumes that when you say "copying all files", your are ONLY moving the database files (data/log) and not the SQL Server binaries.  If your SQL binaries and/or resourcedb need to be moved, you have a much stickier task ahead of you.

Your biggest gotcha should be maintaining the directory and database file permissions.  I would handle this using ROBOCOPY with the /copyall switch to move the files while keeping all perms and attributes intact.

While outside of scope, if your end goal is to move the database to your full cluster and you're hurdle is client connectivity, have you considered using a DNS Alias to redirect traffic?  I understand that the connectivity change could be larger in scope, but it could give you a path to consolidating your databases to where you want them.


When is an application using too many sessions?

As with all things, it depends.

If you're using OCI, I assume you're developing a client/ server application.  I would generally want the database in that situation to support shared server connections and I would want to request a shared server connection when the application connected to the database.  That improves the performance of the application by limiting the amount of work the database has to do in order to create a new session.  That has the side effect of leaving more resources available on the server for others.  Using shared server connections does involve a longer code path when the database executing subsequent queries since the query has to be sent to a shared server process but that generally isn't a big deal if the number of shared server processes is reasonable.

Opening a number of sessions could also be a problem if the DBA is not using automatic PGA management.  If you're using manual PGA management, PGA is configured on a per-session basis so each session can allocate a separate SORT_AREA_SIZE for sorts among other PGA components.  If you created a large number of sessions in a database using manual PGA management and each session tried to maximize its PGA usage, you could easily starve the server of RAM and cause performance problems for everyone.  Assuming you are using Oracle 10.1 or later, however, automatic PGA management is available.  In that case, the DBA configures a PGA_AGGREGATE_TARGET (or includes the PGA in the MEMORY_TARGET in 11g) and the database takes care of ensuring that the aggregate PGA across all sessions is limited so that the database runs out of resources.

If the database supports shared server connections, your application gets a shared server connection, and the database uses automatic PGA management, most DBAs won't care too much how many sessions you create.

Now, if you are creating many sessions so that you can do more work in parallel, that would create performance problems over and above the number of sessions.  It's easy enough to configure the database to support 1000 sessions, for example, it's much harder to configure the database to not die in a pile if all 1000 session simultaneously issue a meaty query against a data warehouse.  If your application is using up all the resources available to the database for your queries, the DBA would probably want to consider using Oracle Resource Manager to prioritize different applications and/or different users.  For example, the DBA could configure Resource Manager so that if the CPU utilization goes to 100%, your application in the aggregate gets 50% of the CPU, some other application in the aggregate gets 25%, and all others get the remaining 25%.  If no other requests were pending, your application would be free to use all 100% of the CPU.

If you are running things in parallel, it might also be useful to investigate Oracle's ability to run statements in parallel because that might involve less overhead than writing your own parallelization code.  I would expect, for example, that it would be far easier and quite possibly faster to write a client application that serially submitted statements that used Oracle parallel query to execute rather than opening multiple sessions and executing each statement from a separate thread of your application while Oracle was not using parallel query to execute any of the statements.  If you use parallel query, the database can also adjust the number of parallel slaves that are spawned so that when the database is particularly busy fewer parallel slaves are started and when the database is relatively idle more parallel slaves are started.  That would potentially satisfy both the desire to make the application as efficient as possible as well as to share the resources of the server more equitably with other applications.


How can I pass the table name as an argument

As the error states:

  Unclosed quotation mark

Your string is:

'SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM "'+@TableName+' -o d:\processing\'+@TableName+'.csv'

Once the @TableName variable is replaced with its value, the string gets rendered as:

'SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM "AIR_LINE -o d:\processing\AIR_LINE.csv'

If you look at the -Q parameter, it is malformed as:

"SELECT * FROM "AIR_LINE

You just need to move the double-quote (") to the other side of the variable (it should be to the left of the -o, not to the right of the FROM):

'SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM '+@TableName+'" -o d:\processing\'+@TableName+'.csv'


What is the cardinality of entity relationship between Employee and Department?

What is the cardinality of (this) entity relationship between Employee and Department?

Yes, this relationship ("managed-by") is an example of a one-to-one relationship. This doesn't forbid the two entities form having other relationships, (ie. "works-in") that are of different cardinality, one-to-many or many-to-many.

  Suppose after a year another employee is assigned to manage a department previously being managed by another employee. At this point are we going to update the mgr_id column and loose the history of the department and employee relationship?

Yes, the department . mgr_id will be updated and the historical info will be lost. If you want to preserve historical data, the tables should be designed with this in mind. Some DBMS provide built-in support for such temporal data. For some more details, see Temporal databases.


This may or may not help you, but with PIVOT XML instead of the simple PIVOT you can do what you want.

  Adding the XML keyword to the PIVOT operator allows us to convert the
  generated pivot results to XML format. It also makes the PIVOT a
  little more flexible, allowing us to replace the hard coded IN clause
  with a subquery, or the ANY wildcard.

(source)

This will return obviously an XML, this is why it may not help you...


Best design of mongo replica set for DB read/write speed

It is not a great practice to mix the app and database on the same server. You're competing for resources. You also mentioned remote execution versus local execution on the same disk. I'd contend that you could suffer from disk contention if it was all on the same disk and the overhead of the remote replication is questionable by comparison. A lot of factors play into that including the type of disk(s) and if you are separating the OS, logs, and data.

Regarding your topology, yes a best practice in any DB technology is to place replica set members in a different datacenter. Pending your disaster recovery requirements, having at least one of those close to the primary node would be beneficial. There are other considerations such as an emergency on one coast of the US for example that makes all 3 unavailable. That is a case by case scenario to consider though. You also should consider your read and write concerns when addressing performance and availability. The default write concern will be 1. If your greater concern is availability; you may want to use a WC of 2 or majority in the example you gave. Testing your workload will only prove this for your use case and the WC can be set per operation so if you have some operations that are less important you could just use a less reliable WC. You should explore the bulk insert method as well. Personally for time-series data I have found the default and maximum batch of 1000 to be very good. I've also had good results increasing it to 5000 and allowing the DB engine break the batches out into 1k batches. Anything after that has demonstrated poorer inserts per second.

It is quite a lengthy topic but these are some ideas.


MySQL innodb_log_file_size change is not being recognized

We tried the same procedure on an Ubuntu workstation with MySQL 5.7.x on Ubuntu 16.04 and it worked without issue.

Based on this, we decided to upgrade our server's OS to Ubuntu 16.04 which upgraded MySQL to v5.7.12. After changing my.cnf to reflect changes in some option names and restarting the service, the log files now have the correct sizes and the problem is resolved.


Best Practices to reconfigure SQL Server for VMware

What I want to know is why would someone configure a server this way.

Is there a technical reason? I highly doubt it. Is there a different reason? Likely.

They didn't care what they were doing.
They didn't know what they were doing.
They weren't allowed to do what they were meant to do.
The system is so low-use that it doesn't matter what they do.

Either way it's a little irrelevant. There are a few schools of thought on what to do next when it gets reported to you as broken.

Always standardise to minimal best practices before troubleshooting further (max mem, max dop, cost threshold, trace flags, affinity, backups, index maintenance).
As above but only after taking before/after baselines in a way that will let you compare whether it's working better or worse.
Only change what's necessary to get it working again.

It's up to you and they're all justifiable (except maybe in banking and health care).


Setting shared_buffers over 320 MB prevents Postgres from restarting

Just a misunderstanding I guess. The unit for the given number is blocks (or buffers), not 1 kb like you assume.
Quoting the manual for shared_buffers:

  Each buffer is 8192 bytes, unless a different value of BLCKSZ was
  chosen when building the server.

Bold emphasis mine.

SELECT pg_size_pretty(327680 *  8192::numeric);

pg_size_pretty
--------------
2560 MB

Not 320 MB. In modern Postgres the syntax with appended unit is preferred: 320MB, but I don't think that's implemented in the ancient Postgres 8.1, yet. For 320 MB set the GUC to:

shared_buffers = 40960

Have you considered installing a modern version of Postgres? (Like has been hinted in the comments ever so gently). Postgres 8.1 was released 10 years ago.


How can I install a provider for linked server?

The 'provider' is the driver required to allow SQL Server to talk to the remote server. These are normally installed pieces of software, so can't simply be copied between machines.

While not being overly familiar with PervasiveDB, I would assume that like most other manufacturers drivers, it will be included in their client release, which is available for download here - http://www.pervasivedb.com/psqlv11/pages/default.aspx

Once you've installed this, then you can build your linked server connection. Within SSMS you can right click on your existing Linked Server and make a create script which you can use on your SQL2012 instance. Though be warned that this won't copy all the security details (passwords in particular), so you may need to look this up in the documentation, or have a new user created in the target database.

Sometimes you can have issues with different versions of client software, differing client setup or not getting the permissions identical on the rebuild. You can avoid this in future (or make someone's life a lot easier in the future) by documenting everything as you set it up.


Can we disable binary logging for one specific session or user in AWS RDS

One solution proposed in https://forums.aws.amazon.com/thread.jspa?threadID=45258 requires setting backup retention period to zero. Which should mean no automatic backup! But you can still take manual snapshots.


MySQL master slave replication-troubleshooting

Because in MySQL all fuynctions and procedures are stored in the mysql system database rather than in the database where they were defined. So unless you exclude the mysql database from replication, the functions and procedures you define will replicate downstream.


You actually want to keep up to date on service packs in SQL < 2014 as you won't get support after awhile if you don't.

The support policy can be found here.

Generally installing Service Packs is safe but for any production system please make sure you have a strong backup model in place and hopefully a test environment to simulate all changes for production ahead of time to validate that they work.


Database design for stores with and without branches

What you want to think about is what attributes you would track at each of the levels ("store" and "branch")?

You don't want to repeat information in multiple places or have ambiguity around which table a certain type of data is stored.

I'd suggest that you probably need to think a little bit differently.  Instead of "store" and "branch" how about "organization" and "location"?  It's a subtle, but maybe an important distinction.

The location (aka branch) table would have information (columns) about where a business is conducted and perhaps how to contact them (branch phone number, for example).  You might also hand location-specific information off of this table.  Not necessarily just by having columns on the "location" table, but also by having relationships that refer to the location table.  Things that occur to me in this vein might include store hours, inventory and sales, for example.

At the organization level you might have more broadly based information and relationships. Things like vendors, employees, and the like.

The basic idea is to split data attributes (and relationships) in a sensible way so that the things in one table belong with other things in that table.  You want to avoid thinking about trying to model the same thing in two different ways because they operate at two different scales.  You asked about simplicity. The simplest data model is the one that you apply consistently.


Try this:

GRANT select, update on all tables in schema SCHEMANAME to ROLE;
GRANT select, update on all sequences in schema SCHEMANAME to ROLE;
GRANT create on schema SCHEMANAME to ROLE;

You cannot provide grants to drop an object to any other user besides its owner. Only the superuser or owner has the permission to drop the object.


What happens when a MySQL Cluster node is disconnected

In short, MySQL Cluster isn't a good fit for your use case.

MySQL Cluster's shared-nothing architecture provides excellent protection against component-level failure.  When nodes fail or become unresponsive, Cluster takes quick action to disable affected nodes.  It does not focus on conflict resolution, instead works to avoid the "split-brain" problem where a single cluster begins processing work in parallel without coordination.  This is exactly the opposite the use case you describe:  Database activity continuing as normal when partitioned by the network, to be reconciled later when everything reconnects.

MySQL Cluster also typically does not fare well when nodes lack low-latency connections.  Relying on WAN connections between nodes is a bad idea.  MySQL Cluster does support asynchronous replication between data centers.

You might consider using multi-source replication in MySQL Server 5.7 as an alternative.


sp_help with different object types with the same name

If you have two objects with the same name, it is almost certain that one is in a different schema. So to differentiate, you need to include the schema name in the call to sp_help as well.

EXEC sys.sp_help N'dbo.tablename';
EXEC sys.sp_help N'otherschema.udtname';

You should always be using schema-qualified object names anyway:

Bad habits to kick : avoiding the schema prefix

And in the case where you managed to create a table and a UDDT with the same name under the same schema:

I believe this is a bug in the engine. It should not allow me to create a data type and an object with the same name under the same schema. And I don't know why you would want to do that, even though it is allowed.
sp_help is certainly not prepared for that (and I don't see any chance for their investment in fixing it, especially if 1. is correct). It will return the information for the table, but not the type. The documentation mentions nothing of this, of course - so at the very least there is information missing in the docs, and perhaps you could also consider this a feature gap (sp_help could take type as an argument, but again, don't hold your breath).

So your options in the meantime are:

Rename the type.
Don't expect to use sp_help to get information about the type.
Write your own version of sp_help.

sp_help has the following basic logic, which you could change to include both:

-- @objname must be either sysobjects or systypes: first look in sysobjects
....
-- IF NOT IN SYSOBJECTS, TRY SYSTYPES --
....
    -- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME
....
    -- IF NOT IN SYSTYPES, GIVE UP

Or you could add a parameter that lets you decide which object class is more important.

Of course you need to fix any line that uses TypePropertyEx(), an undocumented system function, to do this instead:

--'Prec'  = Convert(int,TypePropertyEx(user_type_id, 'precision')),
[Prec] = [precision],
--'Scale' = Convert(int,TypePropertyEx(user_type_id, 'scale')),
[Scale] = [scale],
....
FROM sys.types
...

Why they use TypePropertyEx() here, I don't have a clue.

But, in all honesty, the best solution is to rename your type. Who knows what other conflicts you'll come across.


Microsoft documentation indicates that you can only use a 3-part naming convention for the SET IDENTITY_INSERT command.

  SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

Somebody else had this same issue, and provided a solution using a temp table, and remote stored procedure to do the work.


What is the difference between Logical connection and User connections?

I looked into the Books Online Doc, it says

  Number of logical connections to the system.

  The main purpose of logical connections is to service multiple active
  result sets (MARS) requests. For MARS requests, every time that an
  application makes a connection to SQL Server, there may be more than
  one logical connection that corresponds to a physical connection.

  When MARS is not used, the ratio between physical and logical
  connections is 1:1. Therefore, every time that an application makes a
  connection to SQL Server, logical connections will increase by 1.


Query between tables to delete similar records from another table

To find all rows from table products01 where the 10 leading characters of volumeId match any products02.volumeId:

SELECT t1.*
FROM   products01 t1
JOIN   products02 t2 ON left(t1.volumeId, 10) = t2.volumeId;

To delete:

DELETE FROM products01 t1
USING  products02 t2
WHERE  left(t1.volumeId, 10) = t2.volumeId;

You can use this equivalent syntax to get both commands more in line:

SELECT DISTINCT t1.* FROM products01 t1, -- see below
-- DELETE FROM products01 t1 USING
products02 t2
WHERE  left(t1.volumeId, 10) = t2.volumeId;

Only add DISTINCT if t2.volumeId is not fully deterministic (t1.volumeId can match multiple t2.volumeId).

Now you can either select the whole statement to select or starting with DELETE to delete. See:

What does [FROM x, y] mean in Postgres?
The manual on DELETE


MySQL Master not sending events to another Master

This could be one of two things

Slave I/O Thread is encountering Network Issues
Slave I/O is Playing Possum (pretending to be dead)

Derek Downey and I have written posts about the flaky behavior of the Slave's I/O Thread

Oct 17, 2011 : Master port is disabled from Public IP, MySQL log is not getting refreshed
Sep 30, 2011 : Can we capture only Slave_IO_Running in SHOW SLAVE STATUS in MySQL)

According to the Bug Reports on this phenomenon, it was supposedly fixed.

See (Bug #30703 and Bug #51089)

If this bug is happening to you, someone missed a patch making MySQL 5.6.23.

I have three suggestions

Drop back to MySQL 5.6.22 or 5.6.21 or last version of MySQL 5.6 where replication was working
Use MySQL 5.6's heartbeat option when running CHANGE MASTER TO.
Implement semisynchronous replication

As for the Network

Check Network For Dropped Packets
Check the NIC Card
Use separate NIC card for Replication Traffic

Other that what I have just said, sorry I don't have a silver bullet for this one :-(


How can I replace multiple correlated subqueries in the SELECT clause by LEFT JOINs?

You can aggregate over distinct values as:

array_agg(distinct person.identifier)

However, I'm not sure why you are joining against these sub-selects. You can join directly against the tables like:

select class.identifier, array_agg(distinct person.identifier) as persons
                       , array_agg(distinct room.identifier) as rooms
from class
left join  person
    on class.identifier = person.class_identifier
left join room
    on class.identifier = room.class_identifier
group by   class.identifier
order by   class.identifier;

I would also suggest that you use an alias for your tables:

select c.identifier, array_agg(distinct p.identifier) as persons
                   , array_agg(distinct r.identifier) as rooms
from class c
left join  person p
    on c.identifier = p.class_identifier
left join room r
    on c.identifier = r.class_identifier
group by   c.identifier
order by   c.identifier;

Oh, and welcome to the site. A nice first question with code and sample data, well done.


See following results:

'true' != 'true' → false
'false' != 'true' → true
NULL != 'true' → null (and equivalent to false in WHERE because it is not true)

What you can do is use COALESCE to change null into 'false'

SELECT * FROM Table WHERE COALESCE(Bool, 'false') != 'true';


MongoDB - Store date and optional time as a field

You can always insert object with type "date" (type 9), no special codes needed. As you can always insert it as "string" or ISODate.


What can be best database schema for multiple project and one database?

You are talking about Multitenancy, or multiple separate entities sharing code, but having its own dataset. While there are many ways to approach this, the most simple way to approach this is by creating multiple databases (schemas), where each project only has access to its own, separate database.

If the amount of data that each user creates is very small, it can make sense to use a single table or set of tables, where each one is prefixed by a user id, or user field, so the applications controls you can only write and read your own rows. That assumes that you ha full control the application.

If the application is on the user's side, you shouldn't solve this on MySQL only- you need a backend application communicating to the user's frontend application, and being between the database and the frontend application for access control (implementing authorization and authentication).


Do large longtext necessarily slow down a database

On MyISAM tables, fragmentation can easily occur where rows have variable length and deletions are frequent. This can affect index reads and full table scan performance by increasing the number of pages that need to be read to find the data, and preventing the use of sequential read-ahead technology.

See this Serverfault question for good info on fragmentation, how to detect it and react to it: https://serverfault.com/questions/202000/how-find-and-fix-fragmented-mysql-tables

This is less applicable on InnoDB, where large objects are stored separately to the rest of the rows, and tables are index-oriented.


SQL Data in form of Date+Time bulk insert into one column

You can process the data using regular T-SQL as part of the bulk loading process, using the OPENROWSET(BULK... function and the XML format file.

INSERT <target> WITH (TABLOCKX)
    <target_column_list>
SELECT
    <columns_and_expressions>
FROM OPENROWSET
(
    BULK <data_file>,
    FORMATFILE = <xml_format_file>,
    ... <other_options>
) AS alias;

The key part is the <columns_and_expressions> in the select clause. This provides a lot of flexibility.

A more concrete example:

INSERT dbo.zz_u_test_TEMP WITH (TABLOCKX)
(
    col1_dirty, col2_dirty, col3_dirty, col4_dirty, col5_dirty,
    col6_dirty, col7_dirty, col8_dirty, col9_dirty, col10_dirty
)
SELECT
    REPLACE(RowSource.col1_dirty, CHAR(34), SPACE(0)),
    REPLACE(RowSource.col2_dirty, CHAR(34), SPACE(0)),
    REPLACE(RowSource.col3_dirty, CHAR(34), SPACE(0)),
    REPLACE(RowSource.col4_dirty, CHAR(34), SPACE(0)),
    REPLACE(RowSource.col5_dirty, CHAR(34), SPACE(0)),
    REPLACE(RowSource.col6_dirty, CHAR(34), SPACE(0)),
    RowSource.col7_dirty,
    RowSource.col8_dirty,
    RowSource.col9_dirty,
    RowSource.col10_dirty
FROM OPENROWSET
(
    BULK 'C:\Documents and Settings\Paul\Test.data',
    FORMATFILE = 'C:\Documents and Settings\Paul\format.xml',
    CODEPAGE = 'RAW',
    FIRSTROW = 0,
    LASTROW = 0,
    MAXERRORS = 0,
    ROWS_PER_BATCH = 0
) AS RowSource;

This gives you the potential benefits of minimally-logged fast bulk load, while applying transformations in the SELECT clause.

That is a code example I wrote years ago for a different problem, but it should work for you. If I get a moment to create a table and file (not supplied in the question), I might add a more specific version.


How to audit users connections via ODBC to all dbs in SQL Server 2008 R2

Now I would like to audit those connections - when they are happening and to which database.

If you want to audit on an ongoing basis then a server side trace will do the job.

Below is how you will script out from Profiler

Entire SCRIPT :

/****************************************************/
/* Created by: SQL Server 2014 Profiler          */
/* Date: 06/18/2014  04:52:30 PM         */
/****************************************************/

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 7, @on
exec sp_trace_setevent @TraceID, 14, 8, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 26, @on
exec sp_trace_setevent @TraceID, 14, 35, @on
exec sp_trace_setevent @TraceID, 14, 64, @on
exec sp_trace_setevent @TraceID, 15, 3, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 7, @on
exec sp_trace_setevent @TraceID, 15, 8, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 26, @on
exec sp_trace_setevent @TraceID, 15, 35, @on
exec sp_trace_setevent @TraceID, 15, 64, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 3, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 7, @on
exec sp_trace_setevent @TraceID, 17, 8, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 26, @on
exec sp_trace_setevent @TraceID, 17, 35, @on
exec sp_trace_setevent @TraceID, 17, 64, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 7, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 26, @on
exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 64, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 7, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 26, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 64, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 7, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 26, @on
exec sp_trace_setevent @TraceID, 13, 35, @on
exec sp_trace_setevent @TraceID, 13, 64, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 7fd54d82-f62b-4c99-8e02-ff0aa8d1d418'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

If you want to just see who is connected now then you can use DMVs to look into

The NULL in dbid (below query) indicates that those queries are Ad Hoc queries.

SELECT cr.DatabaseName
    ,s.session_id
    ,s.host_name
    ,s.program_name
    ,s.client_interface_name
    ,s.login_name
    ,s.login_time
    ,s.nt_domain
    ,s.nt_user_name
    ,c.client_net_address
    ,c.local_net_address
    ,cr.ObjName
    ,cr.Query
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
CROSS APPLY (
    SELECT db_name(dbid) AS DatabaseName
        ,object_id(objectid) AS ObjName
        ,ISNULL((
                SELECT TEXT AS [processing-instruction(definition)]
                FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)
                FOR XML PATH('')
                    ,TYPE
                ), '') AS Query

    FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)
    ) cr
--where s.nt_user_name = '' -- filter here your user name
where s.session_id <> @@SPID
ORDER BY c.session_id


This example code shows how to parse a ref_cursor. There are additional columns available, see the definition of SYS.DBMS_SQL.desc_tab2 for a full list of available columns.

In this example, my ref_cursor is defined as select * from user_tables. You would of course substitute in your own ref_cursor.

DECLARE
   l_csrid       INTEGER;
   l_coldesc     SYS.DBMS_SQL.desc_tab2;
   l_colcnt      INTEGER;
   l_refcursor   SYS_REFCURSOR;

   FUNCTION tostring (p_label   IN VARCHAR2
                     , p_value   IN VARCHAR2)
      RETURN VARCHAR2
   AS
   BEGIN
      RETURN RPAD (p_label, 30)
             || ': '
             || p_value
             || UTL_TCP.crlf;
   END tostring;

   PROCEDURE myput_line (p_rec      IN DBMS_SQL.desc_rec2
                       , p_colnum   IN INTEGER)
   AS
   BEGIN
      DBMS_OUTPUT.put_line (   RPAD ('_', 30, '_')
                        || UTL_TCP.crlf
                        || tostring (p_label => 'Column'
                                   , p_value => p_colnum)
                        || tostring (p_label => 'Name'
                                   , p_value => p_rec.col_name)
                        || tostring (p_label => 'Type'
                                   , p_value => p_rec.col_type)
                        || tostring (p_label => 'Null OK'
                                   , p_value => CASE WHEN p_rec.col_null_ok
                                                     THEN 'Yes'
                                                     ELSE 'No'
                                                END));
   END myput_line;
 BEGIN
   OPEN l_refcursor FOR
      SELECT * FROM user_tables;

   l_csrid   := DBMS_SQL.to_cursor_number (rc => l_refcursor);
   DBMS_SQL.describe_columns2 (c       => l_csrid
                             , col_cnt => l_colcnt
                             , desc_t  => l_coldesc);

   FOR i IN 1 .. l_colcnt LOOP
      myput_line (l_coldesc (i), i);
   END LOOP;
END;


Error: The user specified as a definer ('Admin'@'%') does not exist

Thanks, I executed this query and it worked. We need change the word 'someuser' for 'Admin' in my case.

GRANT ALL ON *.* TO 'someuser'@'%' IDENTIFIED BY 'complex-password';
FLUSH PRIVILEGES


SQL Server Cluster: There is no SQL Server failover cluster available to join

Sql Server account has to be the same on both machines, it should be Domain account ( not local user). It should have particular rights in domain, at least during installation, and it should be local administrator.
SQL server browser service should work.
You should not mess with reporting services nor "install all" option, because it may lead to slightly harder configurations scenario. It is better to focus on bare database engine services installation, and after add what you want.


Data Compression Through Base36 Encoding

More often than not, Base36 encoding will lead to a loss of precision as the base conversion process requires floating point number operations. Not only will this corrupt your data when converting to and from Base36, you will also be consuming more space (INT takes up 4 bytes, many Base36-encoded integers near the 32-bit unsigned integral limit will take up more than this).

See PHP's documentation for a thorough explanation on the loss of precision.

Answer: You should be using BIGINT for your operations.


What can cause TRUNCATE TABLE to take a really long time?

Using TRUNCATE TABLE on an InnoDB table requires a full table lock because TRUNCATE TABLE is DDL (Data Definition Language) not DML (Data Manipulation).

Doing DELETE FROM user_engagements; will not help because MVCC info is written to the undo logs in ibdata1, and that can hold up the table from being emptied. If any uncommitted transactions are holding onto user_engagements, that could potentially hold up a TRUNCATE TABLE as well.

You could rename the table so that it is immediately available

SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE user_engagements_new LIKE user_engagements;
ALTER TABLE user_engagements RENAME user_engagements_zap;
ALTER TABLE user_engagements_new RENAME user_engagements;
DROP TABLE user_engagements_zap;
SET FOREIGN_KEY_CHECKS = 1;

This should replicate quickly except for the last statement.

Give it a Try !!!

If you have MySQL 5.1.16+, TRUNCATE TABLE requires DROP privilege. My answer performs what TRUNCATE TABLE now does.

If you have MySQL 5.1.15 and back, you need DELETE privilege, which my answer covers.


How to identify which query is filling up the tempdb transaction log?

From http://www.sqlservercentral.com/scripts/tempdb/72007/
;WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       ISNULL(
           NULLIF(
               SUBSTRING(
                 EST.text,
                 ERQ.statement_start_offset / 2,
                 CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset
                  THEN 0
                 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC;

EDIT
As Martin pointed out in a comment, this would not find active transactions that are occupying space in tempdb, it will only find active queries that are currently utilizing space there (and likely culprits for current log usage). So there could be an open transaction but the actual query that causes the problem is no longer running.
You could change the inner join on sys.dm_exec_requests to a left outer join, then you will return rows for sessions that aren't currently actively running queries.
The query Martin posted...
SELECT database_transaction_log_bytes_reserved,session_id
  FROM sys.dm_tran_database_transactions AS tdt
  INNER JOIN sys.dm_tran_session_transactions AS tst
  ON tdt.transaction_id = tst.transaction_id
  WHERE database_id = 2;

...would identify session_ids with active transactions that are occupying log space, but you wouldn't necessarily be able to determine the actual query that caused the problem, since if it's not running now it won't be captured in the above query for active requests. You may be able to reactively check the most recent query using DBCC INPUTBUFFER but it may not tell you what you want to hear. You can outer join in a similar way to capture those actively running, e.g.:
SELECT tdt.database_transaction_log_bytes_reserved,tst.session_id,
       t.[text], [statement] = COALESCE(NULLIF(
         SUBSTRING(
           t.[text],
           r.statement_start_offset / 2,
           CASE WHEN r.statement_end_offset < r.statement_start_offset
             THEN 0
             ELSE( r.statement_end_offset - r.statement_start_offset ) / 2 END
         ), ''
       ), t.[text])
     FROM sys.dm_tran_database_transactions AS tdt
     INNER JOIN sys.dm_tran_session_transactions AS tst
     ON tdt.transaction_id = tst.transaction_id
         LEFT OUTER JOIN sys.dm_exec_requests AS r
         ON tst.session_id = r.session_id
         OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
     WHERE tdt.database_id = 2;

You can also use the DMV sys.dm_db_session_space_usage to see overall space utilization by session (but again you may not get back valid results for the query; if the query is not active, what you get back may not be the actual culprit).
;WITH s AS
(
    SELECT
        s.session_id,
        [pages] = SUM(s.user_objects_alloc_page_count
          + s.internal_objects_alloc_page_count)
    FROM sys.dm_db_session_space_usage AS s
    GROUP BY s.session_id
    HAVING SUM(s.user_objects_alloc_page_count
      + s.internal_objects_alloc_page_count) > 0
)
SELECT s.session_id, s.[pages], t.[text],
  [statement] = COALESCE(NULLIF(
    SUBSTRING(
        t.[text],
        r.statement_start_offset / 2,
        CASE WHEN r.statement_end_offset < r.statement_start_offset
        THEN 0
        ELSE( r.statement_end_offset - r.statement_start_offset ) / 2 END
      ), ''
    ), t.[text])
FROM s
LEFT OUTER JOIN
sys.dm_exec_requests AS r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
ORDER BY s.[pages] DESC;

With all of these queries at your disposal, you should be able to narrow down who is using up tempdb and how, especially if you catch them in the act.
some tips for minimizing tempdb utilization

use fewer #temp tables and @table variables
minimize concurrent index maintenance, and avoid the SORT_IN_TEMPDB option if it isn't needed
avoid unnecessary cursors; avoid static cursors if you think this may be a bottleneck, since static cursors use work tables in tempdb
try to avoid spools (e.g. large CTEs that are referenced multiple times in
the query)
don't use MARS
thoroughly test the use of snapshot / RCSI isolation levels - don't just turn it on for all databases since you've been told it's better than NOLOCK (it is, but it isn't free)
in some cases, it may sound unintuitive, but use more temp tables. e.g. breaking up a humongous query into parts may be slightly less efficient, but if it can avoid a huge memory spill to tempdb because the single, larger query requires a memory grant too large...
avoid enabling triggers for bulk operations
avoid overuse of LOB types (max types, XML, etc) as local variables
keep transactions short and sweet
don't set tempdb to be everyone's default database -

You may also consider that your tempdb log usage may be caused by internal processes that you have little or no control over - for example database mail, event notifications, query notifications and service broker all use tempdb in some way. You can stop using these features, but if you're using them you can't dictate how and when they use tempdb.


Identifying root cause of slow query outliers from pt-query-digest?

Queries can become slow in such cases:

server is shorted out with disk i/o
server is shorted out with memory and swapped
server have too small key_buffer_size and buffer should be refreshed prior to perform the query
server have too small (join|sort)_buffer_size and on-disk temporary tables are created

Any of the mentioned above can cause sporadical slowdown. List is not exhaustive, sure.


What is the name of the process for creating a new DB from an exported snapshot?

If you are just looking for nomenclature to name the process, I would call it

DB Sandbox Provisioning
DB Self-Service Provisioning
DB Self-Service

If you need an actual process supplied by Amazon, you could look into

AWS Control Tower
AWS Service Catalog

If you are planning to script this yourself, there is always the AWS CLI.

create-db-snapshot
restore-db-instance-from-db-snapshot

Check out the Amazon Docs for further info.


SELECTing an optional column, and doing SUM on it

If you need only the initial and the last (current) value of TimeWorked, you only need to add GROUP BY (ticket) to your query:

-- query 1
SELECT
  ti.id                                          AS Ticket,
  ti.Subject                                     AS Subject,
  SUM(tr.NewValue - tr.OldValue)                 AS TransactionTimeWorkedTotal,
  ti.TimeWorked - SUM(tr.NewValue - tr.OldValue) AS InitialTimeWorked,
  ti.Created                                     AS TicketCreated
FROM
    Tickets AS ti
         LEFT JOIN Transactions AS tr      ON ti.id = tr.ObjectId
            AND tr.Field = 'TimeWorked'
WHERE
    ti.Queue = 50
    AND ti.Created > '2015-06-01'
    AND ti.Created < '2015-09-01'
GROUP BY
    ti.id,
    ti.Subject,
    ti.TimeWorked,
    ti.Created ;

This could also be accomplished with a different approach:

-- query 2
SELECT
  ti.id                                          AS Ticket,
  ti.Subject                                     AS Subject,
  COALESCE(ti.TimeWorked - tri.OldValue, 0)      AS TransactionTimeWorkedTotal,
  COALESCE(tr.OldValue, ti.TimeWorked)           AS InitialTimeWorked,
  ti.Created                                     AS TicketCreated
FROM
    Tickets AS ti
         LEFT JOIN Transactions AS tr
            ON  ti.id = tr.ObjectId
            AND tr.Field = 'TimeWorked'
            AND tr.Created =
                ( SELECT tr1.Created               -- finds the first
                  FROM Transactions AS tr1         -- transaction (if any)
                  WHERE ti.id = tr1.ObjectId       -- for the ticket
                    AND tr1.Field = 'TimeWorked'
                  ORDER BY tr1.Created
                  LIMIT 1
                )
WHERE
    ti.Queue = 50
    AND ti.Created > '2015-06-01'
    AND ti.Created < '2015-09-01' ;

Both the above queries will give the initial TimeWorked and the total of the transactions TimeWorked values.

If you want all the individual changes as well - and since the Transactions table has already the old and new values - it needs something different, but not more complicated than the previous queries. using query 2 for example:

-- query 2b
SELECT
  ti.id                                      AS Ticket,
  ti.Subject                                 AS Subject,
  NULL                                       AS TransactionTimeWorked,
  COALESCE(tr.OldValue, ti.TimeWorked)       AS InitialTimeWorked,
  ti.Created                                 AS TicketCreated,
  ti.Created                                 AS TransactionCreated
FROM
    Tickets AS ti
         LEFT JOIN Transactions AS tr
            ON  ti.id = tr.ObjectId
            AND tr.Field = 'TimeWorked'
            AND tr.Created =
                ( SELECT tr1.Created               -- finds the first
                  FROM Transactions AS tr1         -- transaction (if any)
                  WHERE ti.id = tr1.ObjectId       -- for the ticket
                    AND tr1.Field = 'TimeWorked'
                  ORDER BY tr1.Created
                  LIMIT 1
                )
WHERE
    ti.Queue = 50
    AND ti.Created > '2015-06-01'
    AND ti.Created < '2015-09-01'

UNION ALL

SELECT
  ti.id                                      AS Ticket,
  ti.Subject                                 AS Subject,
  tr.NewValue - tr.OldValue                  AS TransactionTimeWorked,
  NULL                                       AS InitialTimeWorked,
  ti.Created                                 AS TicketCreated,
  tr.Created                                 AS TransactionCreated
FROM
    Tickets AS ti
         INNER JOIN Transactions AS tr           -- all the transactions
            ON  ti.id = tr.ObjectId             -- of a ticket
            AND tr.Field = 'TimeWorked'
WHERE
    ti.Queue = 50
    AND ti.Created > '2015-06-01'
    AND ti.Created < '2015-09-01'

ORDER BY
    Ticket,
    TransactionCreated ;


How does this derived table with cross apply work?

What you`re seeing is the XQuery implementation in SQL Server . Although XQuery uses its own parser and performs its own algebrarization during the query compilation stage, the results are combined and
optimized together with the DML portion of the query, then combined into a single execution plan.

SQL Server supports five different methods.  value , exist , query , and
nodes are used to access and transform the data. Last one, modify , uses XML DML to modify the data.

The value() method returns a scalar value from the XML instance
Lets say you have the xml : `

declare @X xml =
'<Order OrderId="42" OrderTotal="49.96">
<Customer Id="123"/>
<OrderLineItems>
<OrderLineItem>
<ArticleId>250</ArticleId>
<Quantity>3</Quantity>
<Price>9.99</Price>
</OrderLineItem>
</OrderLineItems>
</Order>’

select @X.value('/Order[1]/Customer[1]/@Id','int')`or
select @X.value('(/Order/Customer/@Id)[1]','int')

Would give you ID of the first customer from the first order

In your particular case value('.') means give me all values from the shredded element( that i will talk about in a moment)
Remember in using value function you`re moving through XML. Now to make it more easier ,not to 'move' too much you can use function node which
shreds XML into relational data. It returns a row set with rows representing the nodes identified by the path expression.

Example:

declare @X xml =
'<Order OrderId="42" OrderTotal="49.96">
<Customer Id="123"/>
<OrderLineItems>
<OrderLineItem>
<ArticleId>250</ArticleId>
<Quantity>3</Quantity>
<Price>9.99</Price>
</OrderLineItem>
</OrderLineItems>
</Order>'

select C.t.value('(@Id)[1]','int')
from  @X.nodes('/Order/Customer') as C(t)

When you use the nodes() method with the XML column from the table, you must use the APPLY
operator.

Example:

declare @X as table (xmlCode varchar(1000))
insert into @X values (
'<Order OrderId="42" OrderTotal="49.96">
<Customer Id="123"/>
<OrderLineItems>
<OrderLineItem>
<ArticleId>250</ArticleId>
<Quantity>3</Quantity>
<Price>9.99</Price>
</OrderLineItem>
</OrderLineItems>
</Order>');

WITH CTE AS(
select CAST(xmlCode as XML) as X from @X
)
select C.t.value('(@Id)[1]','int')
from  CTE
CROSS APPLY X.nodes('/Order/Customer') as C(t)

I used examples on value and node, because you provided the code with those two functions only, if you want to read more about it please visit this

Hope this simple examples give you an idea of how to query xml types


Looking for a better way with Reporting Db server

I would suggest 2 options ...

If you are using

Enterprise edition then

SQL server 2014 Enterprise edition will give you AlwaysON Availablity groups .. which you can use to offload reporting on secondary.

Standard edition then

You can use logshipping with delaying on transaction logs on secondary in STANDBY mode. The only caveat will be that when the transaction logs are being restored, all the users will be kicked out of the secondary server.

(I am not mentioning database mirroring, but you can use that (even though it is marked as deprecated. You can create database snapshot on secondary and every 2-3 hrs, you can generate a new one since your requirement is - data can be stale 2-4hours)

When using backup/restore method, make sure that you have enabled Instant file initialization and you are using backup compression. That will cut down the backup/restore time.


MS SQL Server 2008 Transaction log file back up failure

Have you tried running a full backup followed by another transaction log backup?  What error messages are you getting?

  We know the logs backup fails, but if it's in simple recovery mode and
  the database is backed up but the log back up fails would I be able to
  recover the database? By recover lets get this straight, I'm talking
  about getting the thing working again by simple restoring from my last
  back up?

If the database fails, you can recover the database with the last full backup regardless of whether it's set to full or simple recovery.  Simple recovery doesn't support log backups at all.  So basically, you'd lose everything from your last full backup forward.

  So wouldn't creating new log file and removing the old log file (but
  leaving the old file on the disk) be the thing to do?

NO.  DO NOT DELETE YOUR LOG FILE.  You'll probably kill your database and the symptom you're trying to fix is that it won't back up properly.

I'm going to recommend this article, "A beginner’s guide to SQL Server transaction logs."  Especially this bit:

  Can SQL Server database work without a transaction log?

  No, that is not possible due to the SQL Server design and ACID
  compliance. ACID stands for atomicity, consistency, isolation, and
  durability.

Also this article on Full vs. Simple recovery.

To crib from this article:

Run dbcc checkdb on the database
Stop all user activity in the database
Switch to the SIMPLE recovery model (breaking the log backup chain and removing the requirement that the damaged portion of log must be backed up) (which you've done, yes)
Switch to the FULL recovery model
Take a full database backup (thus starting a new log backup chain)
Start taking log backups


Large long-running reporting queries are causing very slow new mysql connections

It's look like your reporting query lock for a while the tables needed by general queries.

There is no general recipe for such cases and you have to refactor your reporting to avoid locking. Sometimes you can achieve this with splitting one big query into the series of smaller ones. Sometimes the good approach is to copy some tables for exclusive use by report generator. Sometimes it is something else. All depends on the your DB scheme.


How does this replication behaviour work in a RDBMS?

It depends on exactly how the "replication" is set up.

Real Application Cluster

N1,N2,N3 are the three nodes of a RAC cluster.

In a RAC system, all nodes are "masters".  You just choose to do READ-ONLY session on the other two.

DML operation on Node 1 tries to set R to R1
Node 1 crashes
Transparent Application Failover fails the connection from Node 1 to
Node 2 (or Node 3).
The DML is "rolled back and tried again" on Node 2.
DML on Node 2 attempts to change R to R2 (but value is now R1).

Logical Standby

DML operation on Node 1 tries to set R to R1
Node 1 crashes
DML on Node 2 attempts to change R to R2.

DML on Node 2 fails since Logical Standbys can only be opened as READ ONLY
Starting with 18c, You can only do DML on Temp Table.

At some point in time, DBAs set the Logical Standby as the primary.

If the change log was successful in writing, this change might be applied
to the standby database.

Golden Gate

DML operation on Node 1 tries to set R to R1
Node 1 crashes before it completes the COMMIT process.
DML on Node 2 attempts to change R to R2.

Since Node 2 is a separate Database, this DML succeeds.


How to convert MySQL master-slave to master-master

Master-Master has a lot of problems if you are writing to both. It is better to write to only one of them until you need to failover.

Setting up Dual-Master is simply setting up both servers as being both Master and Slave, much like you already did to make your current Master-Slave topology.


How to use default value of data type as column default?

The default default value for any new table column is the default value of the data type.

And the default default value for data types is NULL - which is the case for all basic data types in Postgres. But any valid value is allowed for custom types or domains.

The manual on CREATE TYPE:

  A default value can be specified, in case a user wants columns of the
  data type to default to something other than the null value. Specify
  the default with the DEFAULT key word. (Such a default can be
  overridden by an explicit DEFAULT clause attached to a particular column.)

The manual on CREATE DOMAIN:

  The default expression will be used in any insert operation that does
  not specify a value for the column. If a default value is defined for
  a particular column, it overrides any default associated with the
  domain. In turn, the domain default overrides any default value
  associated with the underlying data type.

You only use the DEFAULT clause for columns in CREATE TABLE to specify a different default. Else, you do nothing. WITH DEFAULT like you display in the question is not valid in Postgres

To reset a column default to the default default of the data type (typically NULL), drop the specified default value of the column.

The manual on ALTER TABLE:

ALTER [ COLUMN ] column_name DROP DEFAULT

NOT NULL constraints are related but completely independent. A column defined NOT NULL and with no custom DEFAULT (and no not-null default for the type) requires you to provide a not-null value for every INSERT.

Aside: you probably do not want to use the data type char(12) in Postgres.

Any downsides of using data type “text” for storing strings?
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars


Upgrading mysql myisam 5.1 to mysql 5.6: force innodb on restore?

I am going to answer based on option #3.
You can dump all databases except the following:

information_schema
performance_schema
mysql

You should dump it like this:
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="select group_concat(schema_name) from information_schema.schemata"
SQL="${SQL} where schema_name not in ('information_schema','mysql','performance_schema')"
DBLIST=`mysql -ANe"${SQL}" | sed 's/,/ /g'`
mysqldump --databases ${DBLIST} | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g'  > AllMyData.sql

You can just load that and you are all set.
If you want to move the MySQL Users and Passwords, do this:
MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ',"
SQLSTMT="${SQLSTMT} QUOTE(user),'@',QUOTE(host),';') "
SQLSTMT="${SQLSTMT} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > GetGrants.sql
echo "SET sql_log_bin = 0;" > MySQLUserGrants.sql
mysql ${MYSQL_CONN} -AN < GetGrants.sql | sed 's/$/;/g' >> MySQLUserGrants.sql
rm -f GetGrants.sql

Then, just import MySQLUserGrants.sql into the MySQL 5.6 instance.
Give it a Try !!!


One and only one relationship - ER diagram to relational schema

You have several possibilities, each of them with advantages and disadvantages.
Solution 1
Define a single table:
AB(a b c d e f)

with two candidate keys, b and e. You chose one of them as primary key, and declare the other as unique.
Advantages: All the constraints of the association among the two entities are satisfied.
Disadvantages: If there are other entities related to A or B, then the foreign key for the relation corresponding to this entity can be not “natural” if the primary key chosen for AB is the “other” key.
Solution 2
Define one of the two relations with a not nullable foreign key for the other, for instance add fke (“foreign key for attribute e in B”) to relation A. Then declare that this attribute is unique.
Advantages: it is more “natural” to keep the two entities in separate relations. You keep the constraints that each element of A is related to one and only one element of B (since the attribute fke is not nullable and it is unique in all the relation).
Disadvantages: you lose one constraints: that each element of B is related to some element of A. This constraint must be enforced in the applications developed on the database.
Solution 3
As before, exchanging the role of A and B.
Solution 4
As you have mentioned, with a bridge table R(b,e). Then declare both b and e as unique.
Advantages: you keep the constraint that each element of A can be related only to one element of B, and vice versa.
Disadvantages: you lose the constraints that each element of A is related to some element of B and vice versa. Those constraints must be enforced by the applications. Moreover, you have one more table, and this make the queries more complex.
Solution 5
You put a foreign key in each of the two tables to refer the other table. These foreign keys are also declared unique.
Advantages: you keep the constraint that each element in A is related to one and only one element in B, and vice versa.
Disadvantages: you lose the constraint that if a certain element b1 of B is associated to an element a1 of A, then a1 is associated to b1. In fact this solution allows inconsistences: an element a1 could be associated to b2, a2 could be associated to b1, while at the same time the foreign key for b2 could refer to a2, and the foreign key for b1 could refer to a1. So this constraint should be enforced by the application. Moreover, there is a certain redundancy in the data.

The solutions are listed in order from the more “secure”, to the more “dangerous”, at least in my opinion.
Finally, is worth to note an important point from this case: when translating from the ER model to the Relational Data Model, there are situations in which the translation is not completely satisfactory.
For your last question: what happen if at most one arrow. In this case the second or third solution can be used. And normally, if one arrow is total (i.e. you have exactly one element in the other entity set) while the other is partial, you put the foreign key in the relation for which the relation is total. If both are partial, then you can choose freely the relation in which to put the foreign key.


How can I eliminate false "connection string format is not valid" messages from SSIS log?

If you use the Excel connection manager for example, you should set the ExcelFilePath property as an Expression instead of the ConnectionString !


Merge difference MySQL DBs into a single new server

This consider all database names are different on all server.

As you said all servers are mysql 5.1, You can take mysqldump on each server and restore it on new server

Step 1 :  Take dump from all old server like below

Everything written in one file: table structures, indexes, triggers, stored procedures, users, encrypted passwords.

mysqldump -u... -p... --routines --triggers --all-databases | gzip > MySQLDataServer1.sql.gz

mysqldump -u... -p... --routines --triggers --all-databases | gzip > MySQLDataServer2.sql.gz

mysqldump -u... -p... --routines --triggers --all-databases | gzip > MySQLDataServer3.sql.gz

mysqldump -u... -p... --routines --triggers --all-databases | gzip > MySQLDataServer4.sql.gz

Step 2 : Copy dump from all old servers to new server

Step 3 : Extract all zips on new server

Step 4 : Import extracted dump files on new server like below

mysql -u... -p... < MySQLDataServer1.sql
mysql -u... -p... < MySQLDataServer2.sql
mysql -u... -p... < MySQLDataServer3.sql
mysql -u... -p... < MySQLDataServer4.sql


Is there a timeout option for remote access to PostgreSQL database?

A PostgreSQL server connection dropping after 10-15 minutes is almost certainly being caused by a state-tracking firewall (possibly using Network Address Translation (NAT)) between the client and the server. Many such firewalls have default timeouts of 15 minutes (900 seconds).

The three server-side parameters, tcp_keepalives_idle, tcp_keepalives_interval, tcp_keepalives_count are designed to help in these situations. See the documentation located here: http://www.postgresql.org/docs/9.2/static/runtime-config-connection.html#GUC-TCP-KEEPALIVES-IDLE

There are also client-side parameters for this: keepalives, keepalives_idle, keepalives_interval, keepalives_count, which you can set on connection. See the documentation located here: http://www.postgresql.org/docs/9.2/static/libpq-connect.html#LIBPQ-KEEPALIVES


I'm wondering whether the memcached plugin has been added to MariaDB?

Not yet.  But slide 24 of mariadb-10-and-whats-new-with-the-project mentions:

 Plan is to have all important features of MySQL 5.6 by the time
 MariaDB 10.1.x is released as stable - For all practical purposes
 MariaDB 10.1.x will be a drop in replacement to MySQL 5.6"

https://kb.askmonty.org/en/what-is-mariadb-100/

New Features Backported from MySQL 5.6

New InnoDB — from MySQL 5.6.5

The thought being that the InnoDB Memcached Plugin would come along with InnoDB from 5.6.  Also see https://mariadb.com/blog/mariadb-10-vs-mysql-56-feature-comparison-update

https://jira.mariadb.org/browse/MDEV-4674 is a request for it
  https://github.com/MariaDB/server/pull/126 is a patch

The source is https://github.com/MariaDB/server , this is where it would be added.

Update, add link to related issue: https://jira.mariadb.org/browse/MDEV-12050 - "Remove unused InnoDB Memcached hooks from MariaDB Server"


Database T-Log is full due to 'Active_transaction'. SQL server is using new T-log file first rather than old

Why process was failing with T-log full even though T-log file size is set to 500 GB

Is Replication or CDC enabled for the database?
select name, is_published, is_cdc_enabled
from sys.databases

Why would SQL server start using new T-log file rather than old T-Log file first?

Because the old one was full.


Calculating new date with date/time operators fails with variables

To construct the interval, multiply the number with the 1 second interval:
 duration * interval '1 second'

select calldate + duration * interval '1 second' as "completion_date";

or:

select calldate + duration * '1 second'::interval as "completion_date";

Postgres docs have a page about Date/Time Functions and Operators, where there are similar examples:

Operator    Example                     Result
  *         900 * interval '1 second'   interval '00:15:00'
  *         21 * interval '1 day'       interval '21 days'


Sybase Central: How to set language for UI of Sybase Central

You can use the dblang command, but you need to disable fast launcher first.

See this for reference: Changing administration tool language settings


How do I pivot to summarize my entries by a specific area?

Do you know that you only have woodpecker, robin, blackbird, chaffinch? To pivot at all you must either

Constraint the value-set in the table that you're pivoting on. Because values become schema, and schema must be determined at the time you issue the query you would have to know all possible values.
Make two trips to the server, one to generate the original resulset of values. And a second to read the metadata from that resultset so you can issue a second query against it.


Oracle12c R2 on Windows 10 starts only one of two instances. Manual start results in LRM-00109

I ran into the same problem. The database did not start with the service. First I checked the setting for the service using the Oracle Administration Assistant for Windows and configured the DB to start with the service start (by the way, I prefer the database to shut down immediately when the service is shut down).

But enabling the database to open at service start didn't solve the problem. Next I found that the folder %ORACLE_HOME%\database was read-only, and I did not succeed to clear this attribute using the properties dialog in the Windows Explorer. The attrib program showed no R flag, though...

It was not possible to start up the database as SYSDBA from sqlplus either - I got the same LRM-00109 error. So I copied the SPFILE to an INIT<INSTANCE>.ora file and edited it. Startup wasn't possible either, except when I used

startup open pfile='(ORACLE_HOME)\database\init(Instance).ora'

I finally changed the service from running as a virtual account to running as Local System - that works...

The Oracle version I use is 12.2.0.1 - I guess there is some problem with it.


If you look at the code for your spGetSites procedure, somewhere in that procedure is another INSERT...EXEC. It may be directly in that procedure, or nested in the bowels of some other procedure it calls.

Ultimately, if a stored procedure uses INSERT...EXEC then if you try to call that stored procedure in the context of another INSERT...EXEC you will get the error you are seeing.

How do you fix it?

You could simply take the inner INSERT...EXEC and inline the code to this single stored procedure. Though, I suspect that other procedure may be there for a reason: ie to keep your code DRY.
Since this is a Get procedure, hopefully there's no data manipulation happening anywhere in the call stack. You could convert the child procedure into a table-valued function. This would allow you to convert that inner INSERT...EXEC into an INSERT...SELECT and resolve this issue.
Use temp tables scoped to the outer procedure to pass data between procedures. This solution gets complicated, so it's not my favorite, and I generally discourage this pattern when there's a better option--but for the sake of completeness, I'll include it here.  Basically, if you create your #temp table outside spGetSites you can use it inside spGetSites (without creating it inside there), and the table with data inserted will survive the procedure execution and continue to work.

I don't like option 3 because it's a coding pattern complex enough to ensure someone messes it up in the future, unless everyone is on board and familiar with the coding pattern:
* spGetSites will fail unless you create the table first. All callers need to remember to create the table exactly the same first.
* spGetSites can't assume the table is empty. It might have existing data from the outer call (or a prior execution from the same caller)
* Troubleshooting and debugging (and even getting a query plan) for spGetSites is more complex because of the table creation confusion.

What would I do?

Without knowing how complex the code is behind spGetSites, I'd look into creating an inline TVF that replaces the inner INSERT...EXEC with INSERT...SELECT or possibly all of spGetSites could be simplified/rewritten to make it self contained without the INSERT...EXEC


Optimization on table selected where primary key

Generally speaking, you'd want to rewrite that query like this:

SELECT *
FROM wp_dev_posts
    INNER JOIN wp_dev_postmeta
        ON wp_dev_posts.ID = wp_dev_postmeta.post_id
    INNER JOIN wp_dev_term_relationships
        ON wp_dev_posts.ID = wp_dev_term_relationships.object_id
WHERE ((post_type = 'projects') OR (post_type = 'post_cost_codes'));

Notice, I've moved parts of the WHERE clause to the FROM clause, using the ON construct.  That makes the query easier to read.

To optimize access to the table, you'd generally like to have indexes on wp_dev_posts.ID, wp_dev_postmeta.post_id, wp_dev_term_relationships.object_id, and post_type.

It might make sense to create a compound index on wp_dev_posts for (post_type, ID).

You'd probably want a foreign key on wp_dev_postmeta pointing to the primary key defined on wp_dev_posts.ID.

Providing more details about your specific environment, including the definition of the tables, any existing queries, row counts, etc, would help us provide a more definitive answer.


If you're happy with what you have already, try starting with a set of the distinct sections, cross joining on that, and then filtering your subquery to only that section. That way, it's like considering each section as if it's its own event.

But... why not consider looking at all the seats and finding blocks where the (N-1)th next one is the actual right number away:

SELECT * FROM
(SELECT seats.*, eventtickets.eticketprice,
    LEAD(seats.SeatID,@N-1) OVER (PARTITION BY seats.seatSection ORDER BY seats.SeatID) AS SeatNAway
from seats
INNER JOIN eventtickets ON eventtickets.etickettype = seats.seattype
WHERE ((Seats.seatType ='BOXSEAT')
AND seatID not in (select seatID from ticketsales WHERE eventID=6))
) s
WHERE s.SeatId = s.SeatNAway - (@N-1)
ORDER BY convert (int, s.seatSection), s.seatrow ASC

See how you go with this maybe... check that you understand the inner subquery, of course.


My SQL server showing different result with same query execution at different time

I'm guessing you're getting varying row count in the table_row column? This is expected behaviour for InnoDB-tables.

"For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned."

http://dev.mysql.com/doc/refman/5.1/en/tables-table.html

There is more information here: http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html

Notably: "ANALYZE TABLE determines index cardinality (as displayed in the Cardinality column of SHOW INDEX output) by doing eight random dives to each of the index trees and updating index cardinality estimates accordingly. Because these are only estimates, repeated runs of ANALYZE TABLE may produce different numbers. This makes ANALYZE TABLE fast on InnoDB tables but not 100% accurate because it does not take all rows into account"


Where can I found a example database for apply data quality improvement?

here we have some big public datasets I want to work with the million song Dataset some day. Also google offers some public datasets

Enjoy them!!


How to add a Default constraint while creating a table? SQL Server

You can name the constraint inline:
CREATE TABLE tblTest(
  --
  --
  Gender int CONSTRAINT DF_tblTest_Gender DEFAULT 3,
  --
) ;

As the CREATE TABLE msdn page shows:

DEFAULT
... To maintain compatibility with earlier versions of SQL Server, a constraint name can be assigned to a DEFAULT.

In the same page, we can find that the only options for <table_constraint> are PRIMARY KEY, FOREIGN KEY and CHECK constraints:

< table_constraint > ::=
  [ CONSTRAINT constraint_name ]
{
   { PRIMARY KEY | UNIQUE }
       {
         NONCLUSTERED (column [ ASC | DESC ] [ ,... n ])
         | NONCLUSTERED HASH (column [ ,... n ] )
               WITH ( BUCKET_COUNT = bucket_count )
       }
    | FOREIGN KEY
        ( column [ ,...n ] )
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
    | CHECK ( logical_expression )
}

so if you want to add a default constraint (naming or not) the only ways are by doing it inline or with an ALTER TABLE statement.


How do I create a Mongo config file to allow access to http on port 28017

You're on RHEL6 or a derivative from your kernel version 2.6.32-431.3.1.el6.x86_64. The default firewall only allows ssh incoming. You'll need to poke a hole in your firewall. Look here for firewall tips:

http://wiki.centos.org/HowTos/Network/IPTables


Generated DDL doesn't match with CREATE TABLE statement

As the manual says (emphasis mine):

  MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.

In other words, this is an expected, documented behaviour. If DBeaver cannot see a difference between table constraints and its indexes, may be choose some other tool.


Yes the Distributor is a "fully fledged instance of SQL Server". A Distributor is a SQL Server instance that contains a distribution database. The Distribution server could have other databases on it.

The transaction logs of the publisher are NOT stored in the distribution database. What gets stored in there are the DML commands (INSERT/UPDATE/DELETE), though represented a bit different.

The other types of replication are Merge, Snapshot and Peer to Peer.


Performance of a linked server between two instances of SQL Server on the same virtual machine

should I expect a significant performance drop during queries between
  them (large joins)? Is there a way to overcome this?

This answer addresses directly your question about using a linked server to run queries through.  And the answer is YES, you can expect a performance drop, especially with large joins.

Usually when SQL processes a query, it can use statistics, indexes and other things to define an optimal execution plan, using later joins and where statements to eliminate required rows before they are even read (or at least making a good estimate for how much memory is needed).  But the linked server is a black box for those things.  With queries of even minimal complexity, you will quickly see a drop in performance.  This is due to SQL bringing the entire table contents over the wire into a worktable and then performing the join operation(s).

So, a few thoughts for you:

You can do log shipping of Database A on Server A to Database B on Server A (the same server, but the database is a different name).  You will have to roll your own, but it's just RESTORE LOG WITH STANDBY.  You won't be able to separate cpu/memory for each instance, but you would keep reporting transactions from interfering with your OLTP transactions.  If these are going to live on the same host (just as different instances) then this is no great loss.

What joins are you doing through the linked server?  If all the data you need is available in the reporting database, then you could use OPENQUERY() to make sure that the linked server handles all of the query.  Otherwise, you could use OPENQUERY to pull minimal data from the remote server to the local (temp table) for later joining/work.


The eager index spool is accessed multiple times, each time fetching about 4 rows.

It's called by the Row Count Spool (note that the number of rebinds matches the number of executions):

Which is called iteratively by the Nested Loops Join after the Sort operator:

I'd be a bad consultant if I didn't also mention that using multi-statement table valued functions, and converting dates (some with date math) on the fly will often have a negative performance impact on queries. I realize that's not what your question is about, but hey.

If you'd like to learn more about spools (and many other operators), Fabiano Amorim has an excellent series of articles:

Operator of the Week – Spools, Eager Spool

Hope this helps.


Assuming the answer is 25 and 30, then this should work

SELECT Year, MAX(Quarter) Quarter, MAX(Revenue)
FROM Sale
GROUP BY Year
ORDER BY Year, Quarter


This is typically achieved by writing a SELECT statement that outputs the fields in the same order as the INSERT statement and using that instead of a VALUES clause. For example, one might:

INSERT INTO my_table (field1, field2)
SELECT t1.field1, max(t2.field2)
FROM t1 INNER JOIN t2 ON (...)
GROUP BY t1.field1
WHERE something_else = 'whatever';

The point is that you can just do an

INSERT INTO ... SELECT ...

to insert rows. You do not have to create a VALUES clause and mess with subqueries.


Concatenating rows into a single string query running for 5 hrs and counting

As you don't care about the order of the concatenated items it would be quite easy to knock up a custom CLR aggregate to do this and it will likely out perform the XML method, there is an example of one in this article.

There is a quick and easy change you can make to your existing code though.

Instead of

SELECT DISTINCT email,
                STUFF((SELECT ',' + project_name
                       FROM   dbo.[UMG sent 2016] AS T1
                       WHERE  T1.email = T2.email
                       FOR XML PATH('')), 1, 1, '')
FROM   dbo.[UMG sent 2016] AS T2;

You could use

SELECT email,
       STUFF((SELECT ',' + project_name
              FROM   dbo.[UMG sent 2016] AS T1
              WHERE  T1.email = T2.email
              FOR XML PATH('')), 1, 1, '')
FROM   dbo.[UMG sent 2016] AS T2
GROUP  BY email;

The difference being that the first one calculates concatenated strings for all rows in [UMG sent 2016] and then removes duplicates for email,string. The second one finds distinct email first and then just performs the string concatenation work on those distinct values. So in your example data instead of performing the work 5 times (twice for test and 3 times for Nadal) then throwing away three of them it will just perform the work 2 times, once for each.


Query returning all posts by friends and friends of friends

It is possible to solve this with a query that does not have a single UNION in it:

SELECT
  p.id
FROM
  relations AS r1
  INNER JOIN relations AS r2 ON r1 ON r1.userB = r2.userA
  INNER JOIN posts AS p ON p.ownerId IN (r1.userB, r2.userB)
WHERE
  r1.userA = @userID
  AND r2.userB <> @userID
;

The optimiser, however, might still internally rewrite your query to something very similar to a UNION query, so in the end the performance might not be much better, if at all, than that of query with an explicit UNION.


How to get info if any Monitoring tool (DB performance Analyzer) stops working?

You are encountering THREADPOOL waits, which occurs when the instance has run out of available worker threads. New logins will be denied, and lots of queries will be just waiting.

To troubelshoot THREADPOOL waits, connect with the Remote DAC and check current activity. You'll likely see huge blocking chains. You'll need to resolve or reduce the blocking to resolve the THREADPOOL waits.

Some Microsoft engineers will say to increase worker threads on the instance, but that doesn't help in my experience. It just means it'll take longer before THREADPOOL is encountered.

Things that might resolve THREADPOOL waits:

Adjusting Max Degree of Parallelism and Cost Threshold for Parallelism so that less queries go parallel and thus fewer worker threads are needed
Adding indexes
Refactoring queries
RCSI
etc

For more information on THREADPOOL waits, check out these links (disclaimer: I work for Brent Ozar Unlimited):

https://www.brentozar.com/archive/2014/05/connections-slow-sql-server-threadpool/
https://www.sqlskills.com/help/waits/threadpool/
http://www.sqlpassion.at/archive/2011/10/25/troubleshooting-threadpool-waits/


prepare statement not getting deallocated

User-defined variables such as @whereclause and @query will retain their values between calls. I.e. the variables get a value from call #1 to the procedure and will then keep those values when you get to call #2. That is why you have to initialise the variables to make your procedure work.

However, instead of using user-defined variables, it is good practice to DECLARE variables inside the procedure:

DECLARE whereclause TEXT unsigned DEFAULT NULL;
DECLARE query TEXT unsigned DEFAULT NULL;

(For these kinds of variables you don't prefix them with the '@' character.)


Which is better: one database per application, or just one database?

I agree with mrdenny to have 1 database per Application. There are many good reasons to do that :

Most importantly Log :
SQL Server uses transaction log to be able to allow a point-in-time recovery in case of any disaster, provided that log backups are regularly taken when the database is online.
Full Backups : Easy to manage per application and can be prioratize based on critical to less critical application databases.
DR situation : Easy to manage in scenarios like application crash, server down or failover scenarios. You have the flexiblity of just performing failover of 1 application or all - depending on if you want to test DR for just 1 application or all.
Performance consideration and Problem Isolation : When the application gets busy, it can easily be migrated to new server with less activity. Also, think about locking, blocking and deadlock situations where the same database is being accessed by multiple applications causing all sorts of issues for troubleshooting. It becomes easy to just concentrate on a single application performing bad when you have 1 database per application.
Capacity Planning : You can easily establish baselines to see what applications will require more disk space in the future and possibly migrate them to a new server having enough disk space.
Database Maintenance : The maintenance will be easy as you can concentrate on specific application's databases and give them priority as opposed to just one single database for all applications. The overall maintenance time can be reduced when performing checkdb, rebuild/reorg indexes, update stats, etc.

Above are just some advantages of using 1 database per application. There are more, like having different requirement of database recovery models, backup schedules, collation, etc as well.

Also, you can think of logically separating within the database using different schemas.


How to mark an item as "Expired" when it's ExpiryDate value is in the past?

Assuming you want the expired flag to be real time...

You can use a view to access the data that has a CASE with GETDATE(). A computed column also works.

Note: you can't index the view because GETDATE is non-deterministic

CREATE TABLE myTestTable (foo int IDENTITY (1,1), bar varchar(100), ExpiryDate datetime)
GO
DECLARE @now datetime= GETDATE()
INSERT myTestTable (bar, ExpiryDate)
SELECT 'row1', @now - 2.1
UNION ALL SELECT 'row2', @now - 1.6
UNION ALL SELECT 'row3', @now - 1
UNION ALL SELECT 'row4', @now - 0.1
UNION ALL SELECT 'row5', @now + 2.1
UNION ALL SELECT 'row6', @now + 1.6
UNION ALL SELECT 'row7', @now + 1
UNION ALL SELECT 'row8', @now + 0.1
GO

CREATE VIEW MytestTableWithEXPIRED
WITH SCHEMABINDING
AS
SELECT
   foo, bar, ExpiryDate,
   CASE WHEN ExpiryDate < GETDATE()THEN 1 ELSE 0 END AS HasExpiredView
FROM
   dbo.myTestTable
GO
SELECT * FROM MytestTableWithEXPIRED
GO

ALTER TABLE MytestTable ADD
    HasExpiredCol AS CASE WHEN ExpiryDate < GETDATE()THEN 1 ELSE 0 END
GO
SELECT * FROM MytestTable
GO


The first problem I see is that you are performing a massive update (19+hours in duration) in a single run, and that is why your server is running low in resources. You need to perform the updates in batch. They will be faster, and your server setup should be able to cope with the resource load.

Try the following:

DECLARE @Rows INT,
        @BatchSize INT;

SET @BatchSize = 2000;

SET @Rows = @BatchSize;

WHILE (@Rows = @BatchSize)
  BEGIN
      UPDATE TOP (@BatchSize) tab
      SET    tab.expiration = dateadd(day, 5, getdate())
      FROM  I3dialer.dbo.MasterDNC tab
      WHERE tab.expiration < dateadd(day, 5, getdate());
      SET @Rows = @@ROWCOUNT;
  END;

Before testing this, be sure that there's an index in the expiration field to accelerate the query.

For the second part of your problem, other than increasing the batch insert to say, 10000 should help a bit. You can actually experiment with this to find your optimal batch size, for example, testing against 1 million records to see if there's any performance difference. There are also other things to consider. You should first load the data into an empty table, perform whatever operations you need to clean up your data, and finally insert them into your main table.

In summary, you should now have a 3 part process:

Perform the batch update on your main table
Insert data into a temporal or stage empty table
Insert data from your stage table to your main table

You should definitively get a better performance after implementing this. Do share your results.