Why are these two queries having such different executions? I'm trying to get all users that haven't been sent a message in a given day. I have two tables, one with the messages that have a reporterid indexed column, and a reporter table.

Let me walk you through it:

This inner query returns reporters that have received messages in a given day. It takes tenths of a second on my local dev machine:

select  m.reporterid
    from  nubamessage m
    where  m.messagetypeenum =7
      and  m.createdOn>='2016-06-18 00:00:00'
      and  m.createdOn<='2016-06-18 09:30:00';

There is no difference in the execution time of the above query if I do select(distinct) or not, and it returns the same rows.

But if I used that in an outer join query, it basically never completes:

select  *
    from  reporter r
    left outer join
    (
        SELECT  m.reporterid
            from  nubamessage m
            where  m.messagetypeenum =7
              and  m.createdOn>='2016-06-18 00:00:00'
              and  m.createdOn<='2016-06-18 09:30:00'
    ) as mm  ON mm.reporterid=r.id
    where  r.enabled=1 and m.reporterid is null;

However, if I change the inner query to distinct as the below, it takes a fraction of a second:

select  *
    from  reporter r
    left outer join
    (
        SELECT  distinct(m.reporterid)   -- the only difference
            from  nubamessage m
            where  m.messagetypeenum =7
              and  m.createdOn>='2016-06-18 00:00:00'
              and  m.createdOn<='2016-06-18 09:30:00'
    ) as mm  ON mm.reporterid=r.id
    where  r.enabled=1 and m.reporterid is null;

The execution plan for the non-completing query:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  r   NULL    ALL NULL    NULL    NULL    NULL    11538   50.00   "Using where"
1   SIMPLE  m   NULL    ALL NULL    NULL    NULL    NULL    968388  100.00  "Using where; Using join buffer (Block Nested Loop)"

The execution plan for the query where I just add "distinct", it adds some auto-key:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY r   NULL    ALL NULL    NULL    NULL    NULL    11538   50.00   "Using where"
1   PRIMARY <derived2>  NULL    ref <auto_key0> <auto_key0> 5   r.id    10  100.00  "Using index"
2   DERIVED m   NULL    ALL NULL    NULL    NULL    NULL    968388  1.11    "Using where; Using temporary"

If someone could find it in their heart to explain to me why 1. the first query never completes/is so slow and 2. why the distinct makes it run fast, even though the inner query returns the exact same rows in both cases, I'd be most grateful.

Thanks

Edit: Notice that I write "all users that haven't been sent a message. This is why I have the left join - later in the query I have "where m.reporterid is null", in order to only get out the reporters that don't already have a message in the messagetable. I have edited the questions above to reflect that.

I could have used a "not in" instead but from what I've read and in my tests it was a bit slower than left join.

LEFT is killing performance; remove it unless you have a good reason for keeping it.  To elaborate...

((but first... The purpose for the LEFT was missing from the original query; this answer assumes the LEFT was not necessary.))

LEFT JOIN says that you want data from the right table, whether or not there was a match with the left table.

JOIN says to display only the rows that match (via ON) both tables.

(OUTER is optional and adds no semantics.)

If you are running a version before 5.6, the derived table (subquery in LEFT JOIN) will have no index, so it must be scanned repeatedly.  This is a big reason to get rid of LEFT.

Without the LEFT, the Optimizer is likely to evaluate the subquery once, then efficiently JOIN to reporter to finish the query.

For JOIN, the inner query needs this composite (and covering) index INDEX(messagetypeenum, createdOn, reporterid).

There is another technique (I think)...  Get rid of the inner SELECT, simply JOIN (or LEFT JOIN) to the table:

select  r.*
    from  reporter r
    left join  nubamessage m  ON m.reporterid = r.id
      AND  m.messagetypeenum = 7
      and  m.createdOn >= '2016-06-18 00:00:00'
      and  m.createdOn <= '2016-06-18 09:30:00'
    WHERE  r.enabled = 1
      AND  m.reporterid is null;

In this case, it may needs INDEX(reporterid, messagetypeenum, createdOn).

Yet another variant would use EXISTS and, I think, provides the equivalent of the LEFT

select  r.*
    from  reporter r
    WHERE  r.enabled=1
      AND NOT EXISTS
    (
        SELECT  *
            from  nubamessage m
            where  m.messagetypeenum =7
              and  m.createdOn>='2016-06-18 00:00:00'
              and  m.createdOn<='2016-06-18 09:30:00'
              AND  m.reporterid=r.id
    )

I can't predict which variant will be fastest.  It partially depends on how "many" in the many:1 mapping of m.reporterid : r.id.


Store CURSOR results into Stored Procedure or View I have this SQL code that creates a set of results from a dynamic table range. I want to know if I can create a view from this or a stored procedure?

DECLARE @CommonTablePrefix SYSNAME = 'IncidentResolvedByUserFact_'
DECLARE @SQL VARCHAR(MAX);

SET @SQL = '';
DECLARE TBL_CURS CURSOR FOR
   SELECT [Name] FROM SYs.tables Where name like @CommonTablePrefix + '%';

OPEN TBL_CURS;
DECLARE @TBL_INSTANCE SYSNAME;
DECLARE @FIRST BIT = 1;
FETCH TBL_CURS INTO @TBL_INSTANCE;
WHILE @@FETCH_STATUS = 0
BEGIN
   IF @FIRST = 1
   BEGIN
          SET @FIRST = 0;
   END
   ELSE
   BEGIN
          SET @SQL = @SQL + ' UNION ALL ';
   END

   SET @SQL = @SQL + 'SELECT * FROM ' + @TBL_INSTANCE;
   FETCH TBL_CURS INTO @TBL_INSTANCE;
END
CLOSE TBL_CURS;
DEALLOCATE TBL_CURS;
EXEC(@SQL);

You should be able to wrap your code into a stored procedure that accepts your 'CommonTablePrefix' as an input parameter fairly easily.

CREATE PROCEDURE ReturnCommonTablePrefixResults (@CommonTablePrefix SYSNAME)
AS
BEGIN
    DECLARE @SQL VARCHAR(MAX);

    SET @SQL = '';

    DECLARE TBL_CURS CURSOR
    FOR
    SELECT [Name]
    FROM SYs.tables
    WHERE NAME LIKE @CommonTablePrefix + '%';

    OPEN TBL_CURS;

    DECLARE @TBL_INSTANCE SYSNAME;
    DECLARE @FIRST BIT = 1;

    FETCH TBL_CURS
    INTO @TBL_INSTANCE;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @FIRST = 1
        BEGIN
            SET @FIRST = 0;
        END
        ELSE
        BEGIN
            SET @SQL = @SQL + ' UNION ALL ';
        END

        SET @SQL = @SQL + 'SELECT * FROM ' + @TBL_INSTANCE;

        FETCH TBL_CURS
        INTO @TBL_INSTANCE;
    END

    CLOSE TBL_CURS;

    DEALLOCATE TBL_CURS;

    EXEC (@SQL);
END

Then, you'd simply execute the stored procedure and supply the 'variable' @CommonTablePrefix = 'IncidentResolvedByUserFact_'

EXEC ReturnCommonTablePrefixResults 'IncidentResolvedByUserFact_'


Need database design advise I attached the DB schema, so it will be simple.

Problem: Need to get the item records from the company e.g. 'A'.

Should I add to the item table company_id field?
Will it be a redundancy info as we can always take it using user_company ?

Thank you in advance.

In my personal preference, I would advice you not to add company_id in item table, as this will reduce the purpose of user_company table and both the Id will be repeated in Item table, of no use.

For retrieving  item records from the company e.g. 'A'. Simple join can be used.


I would like to understand better the difference between "sql server stand alone" and "sql server failover instance" AS these question asks:

Are "Always On Failover Cluster Instances" and "SQL Server Failover Clustering" the same thing?

Shouldn't Windows Server Failover Clustering already take care of SQLServer Failover Clustering?

I have some doubts about clusters.

In my first job i remember we had a failover clustering with 4 nodes. There were 3 SQL servers instances on each node and a empty node for failover. If we had a problem with a node, it would failover to that empty node and not cause performance problems with a node that has already a SQL Instance.

Question:  If I'm not mistaken we had a SHARED DISK failover cluster. So, every database was inside a single disk that was shared with all nodes. But what happened for an instance to change to the empty node? I mean, there were no SQL instalation there, no instance, but after a failover, the instance was there with all its jobs. Does this mean that the virtual machine went to the empty node?

Now I have a new job and I still can't quite understand te concept of clusters. We have a failover cluster with 2 nodes. Here I created an Always on availability groups environment. It works great ( The only problem is we have no quorum yet but we will get there ). The environment here is different. I had to install 2 SQL Server instances on each node. And this is where I would like to reach.

Does this mean that on that first job we had a sql server failover cluster installation? In this new job I just installed the normal stand-alone installation instance on both nodes.

( I had 0 sql knowledge when I started at my first job so I really cant remember what was the configuration ).

So basically, whats the difference between sql server stand-alone installation and a `sql server failover cluster installation.

and One more question. almost weekly we are suffering from failover. I'm sure node1 is losing connection with node2 and then the quorum is lost. The concept with the quorum is, if node1 lose connection with node2, the quorum can say" oh, they are not communitacing with each other, but node1 is still alive so I will not failover them. Is this right?

First, lets define a few terms:

SQL Server Failover Cluster / Always On Failover Cluster Instance

These terms are interchangeable. An Always On Failover Cluster is just the modern term for a SQL Server Failover Cluster or SQL FCI. An FCI consists of multiple nodes in a Windows Server Failover Cluster with Shared Disks.

SQL Server software is installed on each node, however, the instance that is installed is a special instance that is clustered - a Failover Cluster Instance. The WSFC service manages the failover of the SQL Instance and Shared Disks between nodes to provide high availability and disaster recovery. The SQL Server service for that instance exists on all nodes but is only started and running on the active node of the cluster.

For all intents and purposes, there is only one instance and it moves between nodes along with its underlying Shared Disks. Data synchronisation between the nodes is achieved by using the same underlying disks, so data states between nodes are identical.

SQL Server Always On Availability Group

A SQL Server Always On Availability Group (SQL AG) is a modern solution for high availability and disaster recovery that does not require Shared Disk infrastructure (in fact, even a WSFC cluster is not required in some cases, but that's a different topic).

It still utilises a WSFC cluster to manage failover and availability, however, the synchronisation of data between nodes does not leverage shared disks to ensure data is up to date, instead, a mechanism is used entirely within SQL Server to transport blocks of data between nodes, or Replicas as they're called in an AG.

Each Replica in an AG architecture is an independent SQL Server instance. The underlying machine is joined to the WSFC cluster as a node and the SQL Server instances on each node have the Always On feature enabled.

When an AG is created between these replicas, the data movement mechanisms begin moving data between the replicas for the databases that are joined to the AG. This is very similar to, and built on the same technology as, database mirroring from earlier versions of SQL Server. Data synchronisation is achieved using this 'mirroring' technology.

Standalone SQL Server Instance

A Standalone SQL Server Instance is quite simply a SQL Server instance that is not linked or joined with another through HADR technology. As the name suggests, this instance stands alone.

While it may seem like an Always On architecture features multiple standalone instances, because the servers are joined via the underlying WSFC cluster and the instances are joined via the Availability Group, they are not standalone instances.

Cluster vs Cluster

Often the terms cluster, SQL cluster and Always On cluster are thrown around fairly interchangeably between Always On and FCIs but there is an important distinction - a SQL Cluster is a SQL Server Failover Cluster Instance, an Always On cluster doesn't really exist, its an Always On Availability Group and shoud be referred to as such and a 'cluster' typically refers to the underlying Windows Server Failover Cluster that the FCI or Always On Ag is built upon.

Now to your questions:

  Question: If I'm not mistaken we had a SHARED DISK failover cluster.
  So, every database was inside a single disk that was shared with all
  nodes. But what happened for an instance to change to the empty node?

A Shared Disk FCI has the software installed on each node, but the instance is moved between nodes as required. Think of the instance as whereever the master database resides. When an FCI fails over, the shared disk hosting master fails over too so when SQL Server is started on the new node, it starts the master database that has been moved and thereby starts up the instance.

  I mean, there were no SQL instalation there, no instance, but after a
  failover, the instance was there with all its jobs. Does this mean
  that the virtual machine went to the empty node?

Actually, SQL Server was installed on that node. The VM did not move, the instance and all of its DBs, jobs etc moved when the disk hosting the files moved. The SQL Server service on node 2 started up after failover and reconnected to the instance files.

  Does this mean that on that first job we had a sql server failover
  cluster installation? In this new job I just installed the normal
  stand-alone installation instance on both nodes.

In the new environment, it sounds like they have installed an Always On Availability Group architecture.

  One more question. almost weekly we are suffering from failover. I'm
  sure node1 is losing connection with node2 and then the quorum is
  lost. The concept with the quorum is, if node1 lose connection with
  node2, the quorum can say" oh, they are not communitacing with each
  other, but node1 is still alive so I will not failover them. Is this
  right?

Quorum is supposed to be the tie-breaker - if both nodes vote that they cannot see the other one, then each would want to take over the primary role (FCI or AG) and this would lead to split-brain. The WSFC cluster won't let this happen and will shut down the cluster instead.

Quorum gives you a deciding vote when you have an even number of nodes. If the cluster service on node 1 cannot communicate with node 2 but it can communicate with the quorum witness (file share or shared disk), then it has two votes (one from the node and one from the quorum witness) and node 2 only has one vote. It is overruled and node 1 stays live. If Node 2 has the two votes then failover is initiated.

It is important to always have an odd number of votes. In later versions of Windows Server, node weight and node votes are adjusted dynamically to try and enforce an odd number of votes, but with only two votes, it cannot enforce this.

UPDATE -

Below is a diagram from Microsoft that helps illustrate the differences:

As you can see from the diagram, an Availability Group is built across independent instances of SQL Server installed on servers joined to a WSFC cluster. A Failover Cluster Instance is installed as a single instance of SQL Server across multiple nodes in the WSFC cluster, using Shared Storage.

Even though the binaries and executables for SQL Server are installed on all nodes in an FCI, the instance (system databases, user databases etc) is installed on shared storage and is only active on a single node at a time.

In an AG, both independent instances are up and running at the same time, and the AG Listener (a virtual network name) only listens on the primary replica.

This link provides detailed information about business continuity in SQL Server, including AGs and FCIs.


COALESCE returning NULL when there are no records When running the following query on a device_id that has no entries in the inv_zfs_replication table the result is a NULL value. How can I make the 2nd sub-query return a zero if there aren't any records?

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

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

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


A few huge instances or many small ones I recently inherited an environment with around 4000+ databases distributed between 21 instances on 3 huge physical servers. Databases are distributed between different instances based on the name, so some instances are a few times bigger than others. Schema is identical for all databases, but data is not. Some databases are just few MB and other are 400+GB.

Management is considering whether we should try to keep as many databses as possible on each instance and have fewer huge instances. Another idea is to keep adding small instances to existing environment or even reduce the number of databases in each existing instance and increase the number of instances.

Administration wise its probably easier to have fewer big instances than many small instances. On the other hand if something happens to big instance then all databases on it will be affected, so it may be safer to have many small instances.

What would be better from the performance point of view? What other factors should I take into account? How many databases should I aim to keep on each instance? I know that it depends, but do you have some rough estimates? For example is 500 databases on one instance too many? If you need any more information please let me know.

I forgot to mention that all these databses are production databses of different clients and so all of them are equally important. Development, testing etc are on different servers.

A single SQL Server instance knows how to manage its memory, CPU and IO in the most optimal way. Two (or more) SQL Server instances cannot cooperate to coordinate usage of shared resources. On this grounds, a single instance is more performant than more instances. If you split into 2 (or more) instances, then it would be required to partition the resources between them (ideally via virtualization) so the instances do not start stepping on each other toes.

To give a concrete example, consider a query memory grant. This is a reservation against the buffer pool. Multiple queries can launch into execution, as long as their total grant does not exceed the allowed memory. A memory grant is not allocated immediately, so the BP is not evicted, but instead used incrementally, and most often not all the reservation is used (reservation is computed for worst case). On many instances, a single instance can hit its cap of the total memory grant and queue queries although the total system has the capability to run more. On a single instance, the total capability has to be hit, so a tenant can survive a spike better.

The biggest drawback and danger of instance consolidation is the noisy neighbor syndrome. If one Tennant takes on a constant basis more resources from the common pool, it will create a poor experience for other tenants as their workload can be starved of required resources and the system appear slow for everybody. On single instances you would use Resource Governor to control fairness between tenants, but the control is not complete. Using virtualization for isolation gives much better control. Using multiple instances on same physical OS is not ideal, imho it gives worse control than single instance Resource Governance and does not provide anywhere near the isolation offered by virtualization. My opinion.

My advice is therefore: either split at VM level, or consolidate to single instance (per physical server, obviously). Also, consider Azure SQL DBs instead, depending on some factors it may be better suited for your scenario.


Mysqld crashes after hold f5 in browser i have cloud vps server, cpu 512 mb.

i have website. if i hold f5 in browser after 10-20 sec mysql server no responding and it gives me error

SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

i also installed drupal, open home page and hold f5, it also crashes mysql.

after that mysql server need to restart service mysqld restart

i think problem is in mysql server configuration.

any advice?

server centos 6

log file after f5 hold

    160502 10:42:45 mysqld_safe Number of processes running now: 0
160502 10:42:45 mysqld_safe mysqld restarted
160502 10:42:45 [Note] /usr/libexec/mysqld (mysqld 5.5.48) starting as process 12843 ...
160502 10:42:45 [Note] Plugin 'FEDERATED' is disabled.
160502 10:42:45 InnoDB: The InnoDB memory heap is disabled
160502 10:42:45 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160502 10:42:45 InnoDB: Compressed tables use zlib 1.2.3
160502 10:42:45 InnoDB: Using Linux native AIO
160502 10:42:45 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
160502 10:42:45 InnoDB: Completed initialization of buffer pool
160502 10:42:45 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160502 10:42:45 [ERROR] Plugin 'InnoDB' init function returned error.
160502 10:42:45 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160502 10:42:45 [ERROR] Unknown/unsupported storage engine: InnoDB
160502 10:42:45 [ERROR] Aborting

160502 10:42:45 [Note] /usr/libexec/mysqld: Shutdown complete

160502 10:42:45 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

The important part of that log is

InnoDB: mmap(137363456 bytes) failed; errno 12 - ENOMEM 12 /* Out of memory */

If you check system log you will see OOM killer doing its work too.

It means your browser sends multiple requests when you hold F5, they probably are executing slowly so they stack, the web server eats more and more memory for each new request and after some time there is just not enough so some process has to be killed - that victim is the mysqld server process which holds some big buffers (key buffer and/or innodb buffer pool).

Then it cannot start anymore until some memory is freed (for example by restarting the system or the web server service).

Check this guide to set your MySQL server to use less memory or separate web and db servers.


How do I record SQL transactions with SQL Server Express With a test migration from 2000 to 2008R2 Express, one function from the client application fails silently. I'd like to find out what SQL is running and why it fails.

I think I would have attacked this with the profiler in 2000 (not sure)? However this tool is absent from the Express Edition.

Is it possible to do what I want without any tool, eg just with T-SQL, and how would I go about it?

You'll need a profiler of some variety if the call is wrapped up.

One of:

Download a trial version, install client tools only
3rd party http://sites.google.com/site/sqlprofiler/


SQL Server 2012: Full Text Catalog taking up all the disk space We have created full text catalog and index for a pretty large table. The column on which the index is being created has a datatype of varchar(max). The problem started as soon as we started populating the catalog. It almost grew to 835GB and Population status stuck to "Processing Notification". I think is status code 9 which corresponds with the "Change tracking" status in the SQL. It gets stuck in this status almost for ever.

Is there a way to deal with this huge catalog size, may be shrink it or move it to a different location.

Is switching off Change Tracking going to improve the situation.

Any help regarding this will be highly appreciated.

UPDATE:

Some more info about the volume of data we are dealing with:

Row Count: 129,953,562 Total: 1327 GB Data 867 GB Index 452 GB Column size 361GB

Although we went to FT instead of LIKE search, but is there a strategy to optimize LIKE search to perform better (may not be as efficient as FT, but somewhat near to that)

Please provide Full-text index definition, rowcount and total size of that column. Having VARCHAR(max) gives 32 megabytes per row of storage. If someone inserts huge text in that column many times, your db and index will grow very fast.

You can create index on a different FILEGROUP, which in turn can map to db file on another drive. ALTER DATABASE to add file(s) to new file group. FG is created and file(s) added to it in single statement:

USE master
GO
ALTER DATABASE AdventureWorks2008R2
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2008R2
ADD FILE
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1;
GO

For further reading (and source of this code): ALTER DATABASE File and Filegroup Options

Sorry, cannot add a comment for some reason. wil update /delete this answer later.

UPDATE

Although possible via a mapped drive, storing an index on the network has big performance penalty. If you are using all records in the table frequently and FT index is new functionality to explore, then this is ok: table on local storage and index on the network.

Alternatively I would suggest considering table usage and partition table into current-frequently used - and archive - almost unused - data. You can have several archive tables on different shares. Table and indexes will be stored on the same partition.

RE: Compression. It is a trade off between storage and CPU. If you have plenty of CPU resource, do consider this. If index stored on the network and compression rate is good, then it would also help.

UPDATE on Compression: Compression would requires row size to be less than 8060 bytes. So if you are storing text, then compression would not be possible for the table (= clustered index or heap). But if FTI row size is less than that, then you can compress FTI index.


How to replicate particular table in MySQL master-slave configuration? How do I replicate a particular table in a MySQL master-slave configuration?
I have tried replicate-do-table and replicate-wild-do-table.
I tried in my.ini file; is there any alternative to replicate without using replicate-do-table and replicate-wild-do-table?
I need a binary log statement to replicate a single table.

The only other way would be at the application level but it will be a little messy.

TECHNIQUE #1

In your application, you can prevent replication of an INSERT of any table as follows:

SET sql_log_bin = 0;
INSERT INTO ...
SET sql_log_bin = 1;

You would surround SQL statement or transaction with SET sql_log_bin = 0; and SET sql_log_bin = 1;.

TECHNIQUE #2

Another way (less code editing) would be to run SET sql_log_bin = 0; as the first statement after establishing a persistent DB Connection.

You would then need to enable binary logging for the table you want replicated like this:

SET sql_log_bin = 1;
INSERT INTO ...
SET sql_log_bin = 0;

EPILOGUE

Obviously, TECHNIQUE #2 would be best. However, you would still need to deploy the code changes for this. You must make sure you have persistent DB Connections. You would also need to test this if you are using Connection Pooling.

UPDATE 2019-06-01 21:57 EDT

replicate-do-table must be setup on a slave, not a master.

In your particular situation, there is just one major problem: This option was first created in MySQL 5.6. It is also available in MySQL 5.7 and MySQL 8.0.

You had posted the same question in another post (which I closed). In that post, you specified MySQL 5.1. replicate-do-table does not exist in MySQL 5.1 or MySQL 5.5. This make my somewhat convoluted answer more applicable because MySQL 5.1 does not have this option.

You could just update Master and Slave to MySQL 5.6 and then setup the replication-do-table on the slave to your heart's content.

CAUTION

You may want to not use such filtering if you only have one slave. The use of only one slave makes it virtually impossible to perform any kind of point-in-time recovery using a slave. See Percona Blog "Why MySQL’s binlog-do-db option is dangerous" on this. Even though the Blog in 10 years old, it still applies.

If you do upgrade Master and Slave to MySQL 5.6, get a second slave. Setup one slave with no filters and the second slave with replication-do-table.

If you cannot upgrade MySQL, then you must go with the application-level techniques I first suggested. At least it will allow Master and Slave to be identical in data content.

Upgrading to MySQL 5.6 is actually the best option.


If you schedule a TSQL Job in SQL Server 2008 R2 and additionally need to copy files with it, is it better just to use a script? I noticed that alot of backup jobs are scheduled using SQL Server 2008 R2, but we have a system here at work that also requires the backup of a directory in addition to the database contents.  I've written a batch file that calls sqlcmd and calls a TSQL script to backup the database; the script also includes copying the directory and logging the result of the operations.

(And yes, I probably should have used powershell instead...)

I would use the SQL Server Job functionality as it has email notification of failures, event loggins and other perks that my batch file does not; but I don't see a way to work copying the directory into that in a all or nothing approach; so is it possible to work copying a directory into an SQL Server Agent Job?

Even though you can use xp_cmdshell to copy the directory, I would not recommend to do it using SQL Server.

You were right in thinking of using PowerShell and/or robocopy to copy files or directory to wherever you want. Powershell will even allow you to send email once the task is completed.

Currently, in my company, we use native sql server backups with compression and then have powershell scripts kick off the move to backup server on a different schedule.


MySQL 5.5.9 Wont Start I installed mysql version 5.5.9 on my mac and I tried to start it using this command :

sudo /usr/local/mysql/support-files/mysql.server start

mysql didn't start with this command. I checked the localhost.err file in data directory and it was like this :

110227 22:51:14 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
110227 22:51:14 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
110227 22:51:14 [Note] Plugin 'FEDERATED' is disabled.
/usr/local/mysql/bin/mysqld: Table 'mysql.plugin' doesn't exist
110227 22:51:14 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
110227 22:51:14 InnoDB: The InnoDB memory heap is disabled
110227 22:51:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins
110227 22:51:14 InnoDB: Compressed tables use zlib 1.2.3
110227 22:51:14 InnoDB: Initializing buffer pool, size = 128.0M
110227 22:51:14 InnoDB: Completed initialization of buffer pool
110227 22:51:14  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
110227 22:51:14  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
110227 22:51:15 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
110227 22:51:15  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
110227 22:51:15  InnoDB: Waiting for the background threads to start
110227 22:51:16 InnoDB: 1.1.5 started; log sequence number 1595916
110227 22:51:16 [ERROR] /usr/local/mysql/bin/mysqld: unknown option '--skip-locking'
110227 22:51:16 [ERROR] Aborting

110227 22:51:16  InnoDB: Starting shutdown...
110227 22:51:16  InnoDB: Shutdown completed; log sequence number 1595916
110227 22:51:16 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

110227 22:51:16 mysqld_safe mysqld from pid file /usr/local/mysql/data/localhost.pid ended

I deleted the two ib_log files, I changed my.cnf file and I got nothing.

It isn't refusing to start because of the missing ib_log files: in fact that trace shows it created blank versions in response to not finding them [presumably this is similar to attaching a detached DB in MSSQL without it s log file(s)].

The reason it is reporting for failing to start appears to be unknown option '--skip-locking' which implies that this option is specified on the command line or in the config file but is not being understood.

Why there would be a wrong option in a fresh install I'm not sure, and I'm no expert at all on MySQL so can't be much more help. To help other people (who may have much greater MySQL knowledge) help you it would be a good idea to document the steps you took install and configure MySQL (including where you obtained any install files or source code) - that will help identify if you are hitting a known problem with MacOS that is a seen in certain releases or setups.

Edit:

A quick search for info about the option indicates that it is now deprecated as it has been renamed to --skip-external-locking (see this SO question+answer) and is apparently the default behaviour anyway. The changelog linked to suggests is changed somewhere in the 4.x line though, so why v5.1.x accepted it and v5.5.9 doesn't is not obvious (unless it was a bug that v5.1.x didn't give the error/warning).


Need help with SQL Server PIVOT I know about the PIVOT feature in SQL Server and I've read a few other questions and answers on here but I just can't seem to grasp it in order to do what I need. I'm trying to run some calculations and I have the following:

TABLE office_settings
column office_id (bigint not null)
column setting_value (NVARCHAR(500) NOT NULL)

office_id    setting_value
--------------------------------------------
4             0.2
4             0.2
4             2
4             3
5             0.2
5             0.2
5             2
5             3

I'd like to convert this using PIVOT (assuming that's the right tool for the job) to this, basically "grouping by" the office_id column, so:

office_id    value_1    value_2    value_3   value_4
------------------------------------------------------
    4         0.2         0.2         2         3
    5         0.2         0.2         2         3

I should also mention that I need to CAST the setting_value column values to DECIMAL(4,2) in the process of this - in case that has any bearing on the T-SQL required to accomplish it.

Any ideas?

Try the following:

select office_id,
[1] as value_1, [2] as value_2,
[3] as value_3, [4] as value_4
from
(
  select office_id, setting_value,
    row_number() over(partition by office_id order by office_id) rnk
  from office_settings
) d
pivot
(
  max(setting_value)
  for rnk in ([1], [2], [3], [4])
) piv;

Or without using PIVOT (generates the same execution plan):

WITH Numbered AS
(
    SELECT
        OS.office_id,
        OS.setting_value,
        row_num = ROW_NUMBER() OVER (
            -- Note: non-deterministic
            PARTITION BY OS.office_id
            ORDER BY OS.office_id)
    FROM dbo.office_settings AS OS
)
SELECT
    N.office_id,
    value_1 = MAX(CASE WHEN N.row_num = 1 THEN N.setting_value ELSE NULL END),
    value_2 = MAX(CASE WHEN N.row_num = 2 THEN N.setting_value ELSE NULL END),
    value_3 = MAX(CASE WHEN N.row_num = 3 THEN N.setting_value ELSE NULL END),
    value_4 = MAX(CASE WHEN N.row_num = 4 THEN N.setting_value ELSE NULL END)
FROM Numbered AS N
GROUP BY
    N.office_id;

With dynamic sql (useful when number of pivot columns varies within a search window):

declare @pivcols as varchar(200);

select  @pivcols = stuff((select distinct '],[' + cast(row_number()
over(partition by office_id order by office_id) as varchar(10))
from office_settings
for xml path('')), 1, 2, '') + ']';

declare @pivquery varchar(500);

set @pivquery = 'Select office_id, '+
@pivcols +'
from
(select office_id, setting_value,
 row_number() over(partition by office_id order by office_id) rnk
 from office_settings
) as d
PIVOT
(
max(setting_value)
for rnk in ( '+ @pivcols +' )
) AS pvt ';

EXECUTE(@pivquery);


Disable SQL Server 2012 Management Studio remote access I am trying to disable SQL Server 2012 Management Studio remote access from other servers. I have unchecked SQL Server Properties -> Connections -> Allow remote connections to this server. but still users can access SQL Server database through SQL Server Management Studio remotely.

I have a client app on all network computers which needs to access the database. I just want to block SQL Server 2012 Management Studio remote access.

Thanks.

Management studio communicates with SQL Server using the same interfaces as normal clients. You can modify user permissions so they can only read from certain tables, and can't write or alter.


How to find out what caused a schema change? I am investigating some performance issues on my SQL Server 2008 R2 server. Some queries are being recompiled due to a "schema change". I am pretty sure no one is changing the schema. All databases on this server are full text enabled.

The question is: how can I find what is causing the schemas to change? Is there any query oy profiler column that can tell me what is going on?

You can use a DDL trigger to log schema changes.

CREATE DATABASE AuditDB;
GO

USE AuditDB;
GO

CREATE TABLE dbo.DDLEvents
(
    EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    EventType    NVARCHAR(64),
    EventDDL     NVARCHAR(MAX),
    EventXML     XML,
    DatabaseName NVARCHAR(255),
    SchemaName   NVARCHAR(255),
    ObjectName   NVARCHAR(255),
    HostName     VARCHAR(64),
    IPAddress    VARCHAR(32),
    ProgramName  NVARCHAR(255),
    LoginName    NVARCHAR(255)
);

Then in your database, you can add events to a DDL trigger:

CREATE TRIGGER DDLTrigger_Sample
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
        CREATE_TABLE,     ALTER_TABLE,     DROP_TABLE,
        CREATE_VIEW,      ALTER_VIEW,      DROP_VIEW
        --, etc etc
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EventData XML = EVENTDATA();
    DECLARE @ip VARCHAR(32) = (SELECT client_net_address
        FROM sys.dm_exec_connections WHERE session_id = @@SPID);

    INSERT AuditDB.dbo.DDLEvents
    (
        EventType,
        EventDDL,
        EventXML,
        DatabaseName,
        SchemaName,
        ObjectName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName
    )
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @EventData,
        DB_NAME(),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        HOST_NAME(),
        @ip,
        PROGRAM_NAME(),
        SUSER_SNAME();
END
GO

The advantage over audit is that this does not require Enterprise Edition. The advantages over the default trace are that (a) you can control which events you capture and (b) you can keep your history as long as you want (the default trace rolls over).

This is culled from a tip I wrote here.


MySQL/innodb trying to lock uncommitted row from parallel transaction, deadlock in result MySQL 8.0.16, 2 simple RC transactions, each trying to insert and delete rows:

    CREATE TABLE test1 (
      id int(11) NOT NULL AUTO_INCREMENT,
      name varchar(100),
      PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

Transaction1:

    mysql> SET autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)

    mysql>
    mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into test1(name) values ('shit1') ;
    Query OK, 1 row affected (0.00 sec)

Transaction2:

    mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    Query OK, 0 rows affected (0.00 sec)

    mysql> SET autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into test1(name) values ('shit2') ;
    Query OK, 1 row affected (0.00 sec)

Transaction1:

    mysql> delete from test1 where name = 'shit1' ;

The transaction1 now trying to apply exclusive lock on non-committed row from transaction2. How can it be possible ? "shit2" row is not committed, mysql use next-key lock in the delete ? why ?

Transaction2:

    mysql> delete from test1 where name='shit2' ;
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Deadlock details: where name = 'shit1' tried to lock "shit2"

    ------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2019-05-21 19:24:16 0x7f6d16db5700
    *** (1) TRANSACTION:
    TRANSACTION 24153, ACTIVE 61 sec fetching rows
    mysql tables in use 1, locked 1
    LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
    MySQL thread id 5009, OS thread handle 140106507769600, query id 111381 localhost root updating
    delete from test1 where name = 'shit1'
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 15 page no 4 n bits 72 index PRIMARY of table `db1`.`test1` trx id 24153 lock_mode X locks rec but not gap waiting
    Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
     0: len 4; hex 80000002; asc     ;;
     1: len 6; hex 000000005e5a; asc     ^Z;;
     2: len 7; hex 81000000b60110; asc        ;;
     3: len 5; hex 7368697432; asc shit2;;

    *** (2) TRANSACTION:
    TRANSACTION 24154, ACTIVE 28 sec starting index read
    mysql tables in use 1, locked 1
    3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
    MySQL thread id 5008, OS thread handle 140106511636224, query id 111382 localhost root updating
    delete from test1 where name='shit2'
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 15 page no 4 n bits 72 index PRIMARY of table `db1`.`test1` trx id 24154 lock_mode X locks rec but not gap
    Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
     0: len 4; hex 80000002; asc     ;;
     1: len 6; hex 000000005e5a; asc     ^Z;;
     2: len 7; hex 81000000b60110; asc        ;;
     3: len 5; hex 7368697432; asc shit2;;

    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 15 page no 4 n bits 72 index PRIMARY of table `db1`.`test1` trx id 24154 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
     0: len 4; hex 80000001; asc     ;;
     1: len 6; hex 000000005e59; asc     ^Y;;
     2: len 7; hex 02000000f10301; asc        ;;
     3: len 5; hex 7368697431; asc shit1;;

    *** WE ROLL BACK TRANSACTION (2)

Here, the MySQL is not trying to lock uncommitted rows, but it locks all rows from table upto auto-incremented value if index on specific column is not defined ( using which we are deleting row).

Reproduce :

Transaction 1 :

mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1(name) values ('shit1') ;
Query OK, 1 row affected (0.01 sec)

mysql> select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  1 | shit1 |
+----+-------+
1 row in set (0.00 sec)

mysql> delete from test1 where name = 'shit1' ;
Query OK, 1 row affected (10.97 sec)

Now Let's try to delete record from Transaction 2 using column Name as well as Primary key.

Transaction 2 :

mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1(name) values ('shit2') ;
Query OK, 1 row affected (0.01 sec)

mysql>  select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  2 | shit2 |
+----+-------+
1 row in set (0.00 sec)

mysql> delete from test1 where name = 'shit2' ;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

mysql> delete from test1 where name = 'shit2' ;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> delete from test1 where id=2 ;
Query OK, 0 rows affected (0.01 sec)

Now We have created Index on name column :

Transaction 1 :

mysql> create index IDX_NAME on test1(name);
Query OK, 0 rows affected (10.82 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  3 | shit2 |
+----+-------+
1 row in set (0.00 sec)

mysql> insert into test1(name) values ('shit1') ;
Query OK, 1 row affected (0.01 sec)

mysql> delete from test1 where name = 'shit1' ;
Query OK, 1 row affected (0.00 sec)

Transaction 2 :

mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql>  select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  3 | shit2 |
+----+-------+
1 row in set (0.00 sec)

mysql> insert into test1(name) values ('shit2') ;
Query OK, 1 row affected (0.00 sec)

mysql>  select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  3 | shit2 |
|  5 | shit2 |
+----+-------+
2 rows in set (0.00 sec)

mysql> delete from test1 where name = 'shit2' ;
Query OK, 2 rows affected (0.00 sec)


Table/database design question Suppose you have these fields to keep track of in records: (first_name, last_name, state_id, stateid_status, day, month, year, id_status, id, criminal_status, charge, plea, sentence, create_time, update_time, hashID). If you were designing the database/table(s), would you create a single table, or would you divide the fields over multiple tables?

Why?

P.S. stateid_status, criminal_status, and id_status refer to the existence of those elements (e.g., criminal history either does or doesn't exist, the entity either does or doesn't have a state ID, etc.)

It's better to extract some rarely used fields to another table. Especially if those fields are big (blob, long varchar etc). This will case smaller row size and better optimized buffer usage. Because small data (i.e 'id', 'name', 'status') will be stored in buffer and will have fast access to it. And queries with big fields (i.e. 'description TEXT') will be slower, because of need to read second table which can be unbuffered yet.

So, I think in your case there is no reason to split fields into different tables.


To be honest, for such need, I would not use pivot, I would simply:

select XPK, 'Total_count', First_count + Second_count
from my_table
union
select XPK, 'Total_amount', First_amount + Second_amount
from my_table

If you really want to use pivot you can try:

select XPK, kpi, total
from (
select XPK,  First_count + Second_count as Total_count, First_amount + Second_amount as Total_amount
from my_table
) t
unpivot (total for kpi in(Total_count, Total_amount)) tt

But maybe your problem is more complex than what you described....


Can I remove binary log files not listed in mysql-bin.index? We've had some problems with one of our more elderly MySQL servers (version 5.045).  The database is back up and running OK but there were over twenty thousand binary log files in the mysql folder.

I logged into MySQL and issued the following command after checking that data replication was all working OK...

purge binary logs to 'mysql-bin.024717';

The command completed without error and the resulting mysql-bin.index looks like this...

./mysql-bin.024717
./mysql-bin.024718
./mysql-bin.024719
./mysql-bin.024720
./mysql-bin.024721
./mysql-bin.024722
./mysql-bin.024723
./mysql-bin.024724

When I left mysql a lot of files had gone, but now there are still over a thousand binary log files left, all from before mysql-bin.024717 and most of them only containing 117 bytes.

Given that they are no longer referenced in the mysql-bin.index file is it OK for me to just go ahead and remove them with rm at the OS level or do I need to do something else to clean them up?

Thanks in Advance

You can also try purge binary logs before '2016-04-29 08:00:00'. I sometimes find one works when the other doesn't for some reason.

Failing that just delete them with rm like you said. It's not technically correct, but it shouldn't be a problem (I did this frequently in the past before I learned about the PURGE commands, and never had any problems).

Of course you also want to make sure you have expire_logs_days set as well to keep them down in future.

I also just saw this note:

  Prior to MySQL 5.1.24, PURGE BINARY LOGS TO and PURGE BINARY LOGS
  BEFORE did not behave in the same way (and neither one behaved
  correctly) when binary log files listed in the .index file had been
  removed

for some reason I don't have access to the 5.0 page to check what that says.


Reclaim space from dropped column in SQLServer2008R2 I am trying to reduce the size of a database that is reaching the limit of the express edition of SQLServer2008R2.
We have identified that due to bad design, a large varchar(2000) field that contains on average 1100 bytes occupies most of the space.

Since this field is not needed to be retained, I want to drop the column, move it to a different table, use it there for the duration it is needed and then clean that table frequently.

I am trying to find the best solution to reclaim the space "wasted" in this column after it is dropped.

The table contains about 1.500.000 rows and has a Clustered PK on an ID (int) column.

I have succesfully reclaimed the space by moving all the contents of the table to a separate database, compacting the original database and then moving the table contents back to the original database having dropped the varchar column. This reclaims about 5Gb of space, which is what I need.

However, I'm looking for a faster and more streamlined option.

I have also succesfully reclaimed the space by dropping the varchar column and then using
DBCC DBREINDEX ({TABLENAME}, {PK_Name}, 100)

However, this process enlarges the size of the database while being executed, exceeding the 10Gb limit that would be enforced in the production environment, so it's not feasible.

I have also tried using DBCC CLEANTABLE that by the documentation appears to be the function best suited for the task, especially since the "Batch_Size" parameter may avoid the inflation of the database size during execution.

However, it looks like CLEANDATABASE is not doing it's intended job at all. It's a pretty simple command and I can't find how I can get it wrong, but here is what I did:

Restore the database (7.2Gb size, 198Mb free) - an old backup, the current database is about 9.5Gb large
Drop the varchar column (does not alter size and free size as expected)
Run DBCC CLEANTABLE ({DatabaseName}, {TableName}, 1000)
Database still shows the same size and free space

From that point on, the only way I've found to be able to reclaim the space is to reindex the table by using:
DBCC DBREINDEX ({TableName}, {PK_Name}, 100)
with the PK_Name being the Primary key, clustered, i.e. the actual data pages.
By doing so, the database shows to have become 8.1Gb with 4.8Gb free space.
From there on, I can reduce the size of the database file - for faster backups - by issuing a shrink command with NOTRUNCATE and then a second one to truncate the file at the end.

Although I have found a way that decently does what I need to do, I am baffled because CLEANTABLE doesn't seem to perform it's "advertized" purpose, even though it takes some time to complete.

Any suggestions or similar experiences?

Thanks in advance

I did more work on the subject and came up with an interesting insight.
Working with the Express edition database engine, I just dropped the column, re-indexed the primary key (cluster index) to re-arrange the data, which inflated the database file size to more than 10Gb and then simply performed a shrink step (NOTRUNCATE) to compact the database file and reduce it's size within nominal limits.
The database engine didn't complain for the excess size during this process and the end result was a database file that had the space needed reclaimed.

So I guess the answer was simpler to solving the problem at hand.

However I still remain puzzled about CLEANTABLE not doing what the documentation describes it does.


Get Aggregate Totals This query was already developed and produces line by line totals, then is copy/pasted into Excel to get Aggregate Totals.  I am sure this syntax can be altered to produce Aggregate Totals - I just do not have the skillset to achieve such.

Right now the syntax produces

Big Boy Records 200  400  0.00
Big Boy Records 400  200 10.00
Big Boy Records 300  100  4.00

And copied into Excel to get the below

Big Boy Records 900  700 14.00

What change needs to be made in this query to get the aggregate totals like above?

SELECT vendor.venID,vendor.venName,((
select  coalesce(sum(rl.amountToInvoice),0)
from    AR r join ARItems rl on r.arID=rl.AR
       join scottCalvin sc on rl.scottCalvin=sc.asID
where   rl.AR=AR.arID and sc.arRPT=true
   )) AS TotalAmountToInvoice,((
select  coalesce(sum(ja.actualcost),0)
from    workDetails ja
where   ja.workID=AR.workID and ja.
       vendorID=AR.vendorTran
   )) AS costamt,((
select  coalesce(sum(ja.markup),0)
from    workDetails ja
where   ja.workID=AR.workID and ja.
       vendorID=AR.vendorTran
   )) AS fullon
FROM "public".AR AR LEFT OUTER JOIN "public".vendor
       vendor ON AR.venID=vendor.venID
WHERE ((NOT ( (AR.vendorTran = 'Pending') ))) AND ((NOT (
       (AR.vendorTran = 'Basis') ))) AND ((AR.payDay
       BETWEEN '01/01/2017' AND '02/01/2017'
       AND vendor.venName = 'Big Boy Records'))
ORDER BY vendor.venID DESC

Taking it as a given that the query as shown in your question generates the three rows shown, turning the existing query into a subquery would give you the grand total for "Big Boy Records":

SELECT venID, venName
      ,COALESCE(SUM(TotalAmountToInvoice),0) as TotalAmountToInvoice
      ,COALESCE(SUM(costAmt),0) as costAmt
      ,COALESCE(SUM(fullon),0) as fullon
  FROM (
        SELECT vendor.venID,vendor.venName,((
        select  coalesce(sum(rl.amountToInvoice),0)
        from    AR r join ARItems rl on r.arID=rl.AR
               join scottCalvin sc on rl.scottCalvin=sc.asID
        where   rl.AR=AR.arID and sc.arRPT=true
           )) AS TotalAmountToInvoice,((
        select  coalesce(sum(ja.actualcost),0)
        from    workDetails ja
        where   ja.workID=AR.workID and ja.
               vendorID=AR.vendorTran
           )) AS costamt,((
        select  coalesce(sum(ja.markup),0)
        from    workDetails ja
        where   ja.workID=AR.workID and ja.
               vendorID=AR.vendorTran
           )) AS fullon
        FROM "public".AR AR LEFT OUTER JOIN "public".vendor
               vendor ON AR.venID=vendor.venID
        WHERE ((NOT ( (AR.vendorTran = 'Pending') ))) AND ((NOT (
               (AR.vendorTran = 'Basis') ))) AND ((AR.payDay
               BETWEEN '01/01/2017' AND '02/01/2017'
               AND vendor.venName = 'Big Boy Records'))
        ORDER BY vendor.venID DESC
       ) grandTotal
 GROUP BY venID, venName

If this grand total is all you want, you can go back to the original query, GROUP BY venID, venName, and add the SUM() function around each of the other three fields.

As PostgreSQL is not my usual language, I have tried to modify the original query as little as possible. It appears that you want one row per vendor, per AR entry. You might be able to eliminate many of the subqueries (turning them into LEFT JOINs instead), by adding the unique ID from the AR table. You could then use `GROUP BY GROUPING SETS ( (venID, venName, arID), () ) to get the individual lines, with the grand total at the end (or possibly the beginning, not sure how it'd sort). You'd have to include the arID in the SELECT list, I think, but once you've got the results you want, you could find a way to filter that out.


Copy SQL Azure Hyperscale I wanted to try SQL Azure Hyperscale, after I selected it seems I no longer have the option to move out of it. If I try, I get this message "databases cannot be moved out of hyperscale tier".

The Copy option is not available and restore does not give you the capability of choosing a different configuration.

Is there a way to move to another Tier ?

Once a database is migrated to Hyperscale, it cannot be migrated
directly to a non-Hyperscale service tier. At present, the only way to
migrate a database from Hyperscale to non-Hyperscale is to
export/import using a BACPAC file or other data movement technologies
(Bulk Copy, Azure Data Factory, Azure Databricks, SSIS, etc.)

Source is here.
Database copy for Hyperscale is now in Public preview as announced here on Sep 22, 2020.


Which is more efficient b+ tree or search (binary or linear)? SELECT A
FROM T
WHERE M > 1000 AND M < 5000;

I am unable to judge which of the below is best suited for the above query:

Linear search
Binary search on M
B+-tree with index on M
B+-tree with index on A

Which is the best answer and why?

I faced this question recently in an exam and I couldn't find the solution. I chose option 3 (B+ tree with index on M) because the query is not sorted on M and B+ tree indexing on A will make it difficult compared to M (I guess).

I want some clarification of the concepts, because I am a little confused about when each of the above can be effectively used.

To be more clear: when a query optimizer chooses optimization plans which of the given options will it pick? That is what the intention of the question was.

Let us consider a dataset with N items, of which P items match the predicate and will be returned by the query.

1a.A linear search, unsorted data. There is no way for the algorithm to know whether a value will match the predicate until that value is read. Therefore the only solution is to read all values and ignore those which do not match the predicate. The effort will be O(N).

1b.A linear search, data sorted by M. In this case we are assured that all values which match the predicate are contiguous in the data. However, there is no way to know the offset of the first value so, again, reading from the beginning is the only approach. Once a value is read that is larger than the upper limit the algorithm can stop. So, in general, this approach is faster than the unsorted approach. It is still O(N), however.

2.Binary search on M. A binary search requires the data to be sorted. Finding the lower/ upper boundary will require fewer operations - it is an O(log N) algorithm. Reading all the values between the lower and upper bound is O(P), however, so overall that is the query's complexity.

3.BTree index on M. In this context a BTree is a more general form of the binary search. Both operate by eliminating a fraction of the possible results in each itteration of the algorithm. This fraction is known as the fan-out. For a binary search the fan-out is 2 i.e. 1/2 the values remain after each itteration. For a BTree the fan-out is determined by the number of index rows that can be held on a data page, which is typically much larger than 2, and so specific values can be found in fewer itterations. The complexity is the same, however, at O(log N) to find the lower limit and O(P) to read the values.

BTree on A. This will be useless as the query puts no restriction on A's value.

  when a query optimizer chooses optimization plans which of the given options will it pick

It will pick the cheapest one. It is the optimiser's job to examine various physical implementations of the query (use and index, scan all rows, sort the data etc), apply heuristics to assign a cost to each permutation and deliver the cheapest (in terms of estimated elapsed time) to the execution engine as a query plan. Note that the cheapest need not be the least complex in Big-O terms. Big-O notation shows how the effort required changes as the number of items approaches a very large limit. Most databases, however, do not have an infininte number of rows. The optimizer has information about the actual number and distribution of values within the dataset. These are stored in internal statistic objects.

Let's take an example where we have a BTree on M which is 3 levels deep. The full table occupies 1,000 pages on disk. Remember that all data movement happens one page at a time. If the given query were likely to return only a single row it would be efficient to use an index on M to read four pages in total (3 from the index on M and one from the table to get the corresponding value of A). On the other hand, if the statistics suggest that most of the data pages will be read it will be more efficient to ingore the index and scan all data pages, ignoring the ones which do not match the predicate.

I have never heard of binary search implemented in a DBMS. BTrees offer the same functionality with greater performance.

Linear search (also known as a table scan) will be used either when no index exists, or when the overhead of using the index is more than the benefit of identifying individual rows.

BTrees are not the only type of index. Single-value lookup and table scan are not the only possible algorithms.


Database Mail has incorrect encoding of CSV attachment I have a procedure that generates a resultset, which I attach as a CSV file attachment in sp_sendmail.  However, the resulting file is encoded UCS-2 LE BOM (according to notepad++).  This causes the resulting file to not "parse" into columns but opens with all values in column A. If I change the encoding to UTF-8 it opens fine.
Problem:
This is an easy fix for me... but the recipients would have no idea how to do this.  Can I change the file to be produced as UTF-8?
Code used to produce send the mail:
exec msdb.dbo.sp_send_dbmail
            @subject                     = @subject
        ,   @body                        = @message
        ,   @body_format                 = 'html'
        ,   @recipients                  = @recipients_to
        ,   @copy_recipients             = @recipients_cc
        ,   @importance                  = 'High'
        ,   @sensitivity                 = 'Confidential'
        ,   @query                       = N'select * from ##Report'
        ,   @execute_query_database      = @dbName
        ,   @attach_query_result_as_file = 1
        ,   @query_attachment_filename   = @file_name
        ,   @query_result_header         = 1
        ,   @query_result_no_padding     = 1
        ,   @query_result_separator      = ','
        ,   @query_result_width          = 32767;

I would suggest you to generate the attachment with powershell and then send the attachment:
$reportQuery = "select * from ##Report"
$filePath = "\\aaaa\bbbb\attach.csv"
$mailQuery = "
  EXEC msdb..sp_send_dbmail
  @profile_name = '..',
  @recipients = '..',
  @subject = '..',
  @file_attachments = '" + $filePath + "'",
    ..... (others parameters)

Invoke-Sqlcmd -query $reportQuery | export-csv $filePath -encoding "unicode"
$result = Invoke-Sqlcmd -query $mailQuery


Delete from table with(snapshot) I was trying to learn querystore. When going through few queries involved in query store one of the query is below

DELETE TOP (1000000)
FROM [sys].[memory_optimized_history_table_1179151246]
 WITH (SNAPSHOT)

Full query:

(@rowcount INT OUTPUT) DELETE TOP (1000000) FROM [sys].[memory_optimized_history_table_1179151246] WITH (SNAPSHOT)
OUTPUT
DELETED.[ColdRoomTemperatureID], DELETED.[ColdRoomSensorNumber], DELETED.[RecordedWhen], DELETED.[Temperature], DELETED.[ValidFrom], DELETED.[ValidTo]
 INTO [Warehouse].[ColdRoomTemperatures_Archive]
 WHERE [ValidTo] < GetHkOldestTxTemporalTs()

I searched a lot to see what the WITH (SNAPSHOT) option means, but I was not able to find anything.

Can you please help me in understanding what does WITH (SNAPSHOT) mean?

Let me know ,if you need any further info.

This is a table hint that can be used in various statements (SELECT, INSERT, DELETE, UPDATE, MERGE). See the related page at msdn: Table hints and the linked Introduction to Memory-Optimized tables.
For further information about isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Syntax
WITH  ( <table_hint> [ [, ]...n ] )

<table_hint> ::=
[ NOEXPAND ] {
    INDEX  ( index_value [ ,...n ] )
  | INDEX =  ( index_value )
  | FORCESEEK [( index_value ( index_column_name  [ ,... ] ) ) ]
  | FORCESCAN
  ...
  | SNAPSHOT
  ...

...
SNAPSHOT
Applies to: SQL Server 2014 through SQL Server 2016.
The memory-optimized table is accessed under SNAPSHOT isolation.
SNAPSHOT can only be used with memory-optimized tables (not with disk-based tables).
For more information, see Introduction to Memory-Optimized Tables.


SQL Server 2019 System.Data.SqlClient namespace query I am using the .NET classes in the System.Data.SqlClient namespace on a SQL 2019 Enterprise Edition and have successfully run TSQL commands via the creation of a function in PowerShell.
function SqlQuery($server, $database, $query)
{
 $connection = New-Object System.Data.SqlClient.SqlConnection
 $connection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True;"
 $connection.Open()
 $command = $connection.CreateCommand()
 $command.CommandText = $query
 $result = $command.ExecuteReader()
 $table = new-object “System.Data.DataTable”
 $table.Load($result)
 $connection.Close()
 return $table
}

SqlQuery $Server $Database "SELECT * FROM Staff"

I have some quite large pieces of T-SQL code stored in discrete files that I would like to execute against this instance.  Is it possible to utilise the function to call a T-SQL script stored in a filesystem file?

possible to call a T-SQL script rather than include it in the main body of the PowerShell script

Of course.  Just read the file contents with get-content and pass that to the function:
function SqlQuery($server, $database, $query)
{
 $connection = New-Object System.Data.SqlClient.SqlConnection
 $connection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True;"
 $connection.Open()
 $command = $connection.CreateCommand()
 $command.CommandText = $query
 $result = $command.ExecuteReader()
 $table = new-object “System.Data.DataTable”
 $table.Load($result)
 $connection.Close()
 return $table
}

$server = "localhost"
$database = "tempdb"
$sql = get-content "c:\test\foo.sql"

SqlQuery $Server $Database $sql

If the sql file contains multiple batches, you'll need to break it up yourself, or use use the Invoke-Sqlcmd cmdlet from the SqlServer module, which you can install with
install-module SqlServer


Of course, my previous answer was just to answer your immediate question.  As I mentioned in my comments, your data model itself is flawed.  Not only should you not keep both 'status_from' (prevous_status) and 'status_to' in the history table, you should also NOT keep status at all in the THINGS table.  This violates standard data normalization rules.

What you should do is drop status from the THINGS table, establish a FK relationship between the two tables, and use a proper query to derive the current status when needed.

First, create and populate the THINGS table.  I've added a couple of columns just to indicate the kinds of things that should be in this table:

S

QL> create table things (thing_id number,
  2                       attrib_a varchar2(10),
  3                       attrib_b varchar2(10),
  4                         constraint thing_pk primary key (thing_id)
  5                      )
  6  ;

Table created.

SQL> insert into things
  2     values (7770,
  3             'red',
  4             'white'
  5            )
  6  ;

1 row created.

SQL> insert into things
  2     values (8880,
  3             'blue',
  4             'green'
  5            )
  6  ;

1 row created.

Then create and populate the history tracking table:

SQL> create table thing_status
  2       (thing_id number,
  3        new_status  varchar2(10),
  4        status_date date,
  5          constraint fk_things
  6            foreign key (thing_id)
  7            references things(thing_id)
  8        )
  9  ;

Table created.

SQL> -- -----------------------------------------------------
SQL> insert into thing_status
  2     values (7770,
  3             'incomplete',
  4             to_date('2016-08-09 00:00:00','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (7770,
  3             'inactive',
  4             to_date('2016-08-10 16:26:22','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (7770,
  3             'active',
  4             to_date('2016-08-11 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> -- -----------------------------------------------------
SQL> insert into thing_status
  2     values (8880,
  3             'incomplete',
  4             to_date('2016-08-12 00:00:00','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (8880,
  3             'inactive',
  4             to_date('2016-08-13 16:26:22','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (8880,
  3             'active',
  4             to_date('2016-08-14 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (8880,
  3             'expired',
  4             to_date('2016-08-15 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

And your history report (as before)

SQL> -- -----------------------------------------------------
SQL> --  Select status history
SQL> --
SQL> select thing_id,
  2         new_status,
  3         to_char(status_date,'dd-Mon-yyyy hh24:mi:ss') status_start_date,
  4         lead(to_char(status_date,'yyyy-mm-dd hh24:mi:ss'),1)
  5           over (partition by thing_id order by status_date) status_end_date
  6  from thing_status
  7  order by thing_id,
  8           status_date
  9  ;

  THING_ID NEW_STATUS STATUS_START_DATE    STATUS_END_DATE
---------- ---------- -------------------- -------------------
      7770 incomplete 09-Aug-2016 00:00:00 2016-08-10 16:26:22
      7770 inactive   10-Aug-2016 16:26:22 2016-08-11 12:32:04
      7770 active     11-Aug-2016 12:32:04
      8880 incomplete 12-Aug-2016 00:00:00 2016-08-13 16:26:22
      8880 inactive   13-Aug-2016 16:26:22 2016-08-14 12:32:04
      8880 active     14-Aug-2016 12:32:04 2016-08-15 12:32:04
      8880 expired    15-Aug-2016 12:32:04

7 rows selected.

And your current status report

SQL> -- -----------------------------------------------------
SQL> --  Select current status
SQL> --
SQL> select t.thing_id,
  2         t.attrib_a,
  3         t.attrib_b,
  4         s.new_status curr_status,
  5         to_char(s.status_date,'yyyy-mm-dd hh24:mi:ss') status_date
  6  from things t
  7   join thing_status s
  8     on t.thing_id = s.thing_id
  9  where s.status_date = (select max(status_date)
 10                         from thing_status x
 11                         where s.thing_id = x.thing_id
 12                         group by thing_id
 13                        )
 14  order by t.thing_id
 15  ;

  THING_ID ATTRIB_A   ATTRIB_B   CURR_STATU STATUS_DATE
---------- ---------- ---------- ---------- -------------------
      7770 red        white      active     2016-08-11 12:32:04
      8880 blue       green      expired    2016-08-15 12:32:04


MySQL can't delete table I have a corrupt table and I can't delete it to restore it. How do I delete this table from the database?

When I try to query on the table this is what I get...

mysql> SELECT * FROM tbl_company LIMIT 10;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: us_businessdb

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.soc            k' (111)
ERROR:
Can't connect to the server

Here is the error log;

2017-08-10T16:11:28.492290Z 4 [ERROR] InnoDB: Trying to access page number 3 in space 35, space name us_businessdb/tbl_company, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
2017-08-10T16:11:28.492335Z 4 [ERROR] InnoDB: Server exits.
2017-08-10T16:11:28.724161Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-08-10T16:11:28.724259Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)

So with force recovery set to innobd_force_recovery=6 this is what I get...

mysql> SHOW TABLES;
+-------------------------+
| Tables_in_us_businessdb |
+-------------------------+
| jnct_tbl_user           |
| tbl_company             |
| tbl_contact             |
| tbl_data                |
| tbl_details             |
| tbl_sic                 |
| tbl_user                |
| tbl_zipcode             |
+-------------------------+
8 rows in set (0.00 sec)

mysql> DROP TABLE tbl_company;
ERROR 1051 (42S02): Unknown table 'us_businessdb.tbl_company'
mysql> DROP VIEW  tbl_company;
ERROR 1347 (HY000): 'us_businessdb.tbl_company' is not VIEW
mysql> CREATE TABLE tbl_company ( id int(10));
ERROR 1050 (42S01): Table 'tbl_company' already exists

For the record every time I have tried to delete this table I have:

SET_FOREIGN_KEY_CHECK=0 before I attempted to delete the table.
still received this same error every time I have tried to drop table (so no partial deletes that I am aware of.
In force recovery I can still query other tables

I just want to drop this table and reload it with the original data that I dumped originally.

What do I do to fix this? The only thing I know to do is delete the file manually but then I get errors when MySQL loads and I'm sure there has to be a better way to do this.

And also for the record, I have recreated this database several times and this same table which is the main table keeps corrupting (which is another issue in itself that I will reserve for another question). Since it's a clean MySQL Server install on Linux, previously I just reinstalled MySQL server to get rid of the error on MySQL startup for missing files where I deleted the db file manually, but this is a pain and I'm sure there is a better way.

EDIT:
This corrupt table originated from a table is full error when I was updating a single column. I had just updated about 12 million records so I'm sure it was a setting issue somewhere since my HD has plenty of space.

That table, us_businessdb.tbl_company may have a broken data dictionary entry. Back in June/July of 2015, I answered 3 questions about a similar situation (where mysqld crashes accessing the table data). My condolences on this table's detachment.

If you have a previous mysqldump of that table's data, you could load it into another database. Then, transfer all the good tables into that other database.

Your current database with the corrupt table is called us_businessdb. Create a database with a similar name (us_business_db) and move the good stuff into it.

CREATE DATABASE us_business_db;
ALTER TABLE us_businessdb.jnct_tbl_user RENAME us_business_db.jnct_tbl_user;
ALTER TABLE us_businessdb.tbl_contact   RENAME us_business_db.tbl_contact;
ALTER TABLE us_businessdb.tbl_data      RENAME us_business_db.tbl_data;
ALTER TABLE us_businessdb.tbl_details   RENAME us_business_db.tbl_details;
ALTER TABLE us_businessdb.tbl_sic       RENAME us_business_db.tbl_sic;
ALTER TABLE us_businessdb.tbl_user      RENAME us_business_db.tbl_user;
ALTER TABLE us_businessdb.tbl_zipcode   RENAME us_business_db.tbl_zipcode;

OK, NOW FOR THE GORY STUFF ...

You mentioned in your question

  EDIT: This corrupt table originated from a table is full error when I was updating a single column. I had just updated about 12 million records so I'm sure it was a setting issue somewhere since my HD has plenty of space.

When you attempted to update too much data in a single UPDATE query and you ran into a table is full error, you did not run out of space on your HD. What ran out of space is the undo log inside ibdata1. I have discussed this back on Jun 16, 2014 (MySQL Index creation failing on table is full) where I personally worked with a 2TB ibdata1/ibdata2 and someone's query failed with that error even with 106GB of unused space in the ibdata files. That unused space is used for undo logs (128 of them).

I discussed this situation even further back:

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

I cannot definitively say that the "Table is Full" broke your table or not. I just wanted to clarify with that error meant.

OK, NOW FOR THE GORIER STUFF (is gorier a word ?)...

Here is something you will find interesting. Run the following:

USE us_businessdb
SHOW TABLES;

You might see this:

mysql> SHOW TABLES;
+-------------------------+
| Tables_in_us_businessdb |
+-------------------------+
| tbl_company             |
+-------------------------+

Did you ever wonder why mysqld did not crash when doing SHOW TABLES; ?

Doing SHOW TABLES; simply passes through the data folder looking for .frm files.

Running this query

SELECT table_name Tables_in_us_businessdb.
FROM information_schema.tables
WHERE table_schema='us_businessdb';

does the exact same thing. In both cases, it looks for tbl_company.frm.

If you attempt to access the metadata of tbl_company.ibd, by doing

SELECT data_length,index_length,create_time,update_time
FROM information_schema.tables
WHERE table_schema='us_businessdb';

or trying to access the actual data with

SELECT * FROM tbl_company LIMIT 10;

then, mysqld blows up and crashes.

OK, ENOUGH OF THE GORY STUFF. WHAT NOW ???

If you have a mysqldump of that tbl_company table, you could load it into the new database we created (us_business_db).

If you have the original CREATE TABLE tbl_company command, you can execute it in the new database. I will leave the details of reloading it s data to you.

If you do not have the original CREATE TABLE tbl_company command, you are going to need to do is get the table structure from the tbl_company.frm file. How ???

Run this query: SELECT @@global.datadir;

Let's say, for this example, that datadir is /var/lib/mysql

Go to the OS and go to the database folder with the tbl_company.frm

cd /var/lib/mysql/us_businessdb
ls -l

You should see, the tbl_company.frm and tbl_company.ibd files. Copy the .frm to another folder or another machine.

Back on Jun 11, 2013 I answered the question How can extract the table schema from just the .frm file?. In that post, I wrote about something I saw in Chuck's Blog regarding a mysqlfrm utility. I tried it out and it worked. You will have to use it to recreate the empty table in the new database.

Once you have done, you can drop the old database, create the old database back, move the tables back into the old database.

DROP DATABASE us_businessdb;
CREATE DATABASE us_businessdb;
ALTER TABLE us_business_db.jnct_tbl_user RENAME us_businessdb.jnct_tbl_user;
ALTER TABLE us_business_db.tbl_contact   RENAME us_businessdb.tbl_contact;
ALTER TABLE us_business_db.tbl_data      RENAME us_businessdb.tbl_data;
ALTER TABLE us_business_db.tbl_details   RENAME us_businessdb.tbl_details;
ALTER TABLE us_business_db.tbl_sic       RENAME us_businessdb.tbl_sic;
ALTER TABLE us_business_db.tbl_user      RENAME us_businessdb.tbl_user;
ALTER TABLE us_business_db.tbl_zipcode   RENAME us_businessdb.tbl_zipcode;
ALTER TABLE us_business_db.tbl_comnpany  RENAME us_businessdb.tbl_comnpany;

EPILOGUE

Sorry about the corrupt .ibd file. You must see to that. Yet, these steps will help clean up the data dictionary and restore the us_businessdb to some sense of normalcy.


Automating DB Restore from network share We have a network share that uses different credentials from the currently logged in user. Inside this share we have full, differential and log backups that come through from a third party database that are updated daily.

I've been tasked with automating the restoration of these backups to a local database.

What's the best way to go about designing and implementing a solution for this?

Folder Structure

Log File structure

As can be seen above, there are multiple files in each folder with different file names depending on the date, etc.

Update:

Following on from the solution posted below, I had to modify the suggested PowerShell code slightly in order to make it work:

$pass ="xxx"|ConvertTo-SecureString -AsPlainText -Force
$cred = New-Object System.Management.Automation.PsCredential('Domain\Account',$pass)

New-PSDrive -Name K -PSProvider "FileSystem" -Credential $cred -Root '\\UNC PATH TO FOLDER WHERE BACKUP IS STORED';
Get-PsDrive;

Copy-Item K:\ -Recurse M:\DESTINATION;

Restore-DbaDatabase -server MRVAPPSQL01 -path M:\DESTINATION -MaintenanceSolutionBackup -WithReplace;

Remove-PSDrive -Name K;

Remove-Item M:\DESTINATION\*.*;

It appears that these backups are being made using Ola Hallengren's Maintenance Solution so I'm going to write this based upon that assumption.

The dbatools PowerShell module has a function which does exactly what you need - Restore-DbaDatabase. For a basic restore:

Restore-DbaDatabase -server DESTINATIONSERVER -path \\BackupServer\Path\To\Backups -MaintenanceSolutionBackup

There are additional parameters you can specify to put the files in a location other than the instance's default paths, change the name of the database or the files, or even a point in time earlier than the last backup (but this point in time must be reachable from the files given).

The account under which your destination SQL Server instance needs read access to the UNC path where your backups are stored. If that's not possible, you'll need to copy that directory structure to somewhere that is accessible or map a drive using alternate credentials.

To do that, you can use New-PSDrive to map a drive using alternate credentials (you'll want to save those credentials securely if you're doing this from a script that runs via Task Scheduler).

New-PSDrive -Name R -Root \\BackupServer\Path\To\Backups;
Copy-Item -path r:\*.* -destination \\Path\That\Destination\Instance\Can\Read
Restore-DbaDatabase -server DESTINATIONSERVER -path \\Path\That\Destination\Instance\Can\Read;
Remove-PSDrive -Name R;


SQL Server 2014/2016 (Win2K12R2) and Winsock limits - how to cope? We're experiencing a problem with connections to SQL Server and after a week of digging through I was able to pinpoint it to Winsock limits. Here's the description:

We have SQL Server 2014 (and 2016) Ent installations running on Windows Server 2012 R2. Virtualized and not virtualized. We observe the problem in both environments.
The clients do not use connection pooling (please don't recommend turning it on, it's root of the problem, I know, but it's something I can't do anything about)
Clients create and close new connections at extremely high rate: normally it's 1500 closed/opened connections per second from a single client. There are 20-25 clients per SQL Server that are doing it at the same time.

Now, at certain points during the business day, we experience traffic bursts, which essentially makes the new connection rate spike up to 2500-3500 connection attempts per second and it continues for around 2-3 seconds.

That's when the fun starts: clients get connection errors during the same 2-3 second bursts.

That is what we see in the network trace for all these connection attempts:

What we have confirmed and done:

It is not port exhaustion, neither on the server nor on the client.
It is not SQL Server worker thread exhaustion. Here're graphs that demonstrate it:

It is not a TIME_WAIT problem, previous connections by the clients are gracefully closed with FIN, ACK -ACK - FIN, ACK - ACK sequence.
A connection does not reach SQL Server and gets rejected by Winsock. Here is a PerfMon graph that demonstrates it:

We tried adjusting the NIC settings and Windows TCP/IP feature settings (RSS, TSO, Scaling Window - enabling/disabling) and it did not fix the issue.
It is not SYN attack protection. We have collected a netsh trace and there were no indications of entering SYN flood attack protection mode.
Hardware resources should not be an issue. We see this problem even on 72 core / 144 thread 4-socket box with 2TB RAM.

Prior to SQL Server 2005 we were able to adjust the backlog window using registry (I'm talking about these instructions). Right now we don't have this kind of ability.

The same document I've linked above says:

  Starting in SQL Server 2005, the network library passes a value of SOMAXCONN as the backlog setting to the listen API. SOMAXCONN allows the Winsock provider to set a maximum reasonable value for this setting.

My understanding is that it happens based on the max connections setting. We have it configured to 0 (unlimited) in SQL Server.

Worth mentioning, we do observe a drop in network traffic (bytes sent/received over the network interface) when we experience the problem. Though there is no increased traffic before/after:

So, here's the question: what can we do to fix the issue? Or are we virtually hitting the limits of Winsock?

The information presented here makes it look likely connections are rejected due to the TCP backlog queue being full of partially opened connections. The other item I ask folks to double-check in cases like this is that MPP (Memory Pressure Protection) is either globally disabled, or disabled for the SQL Server listener port.
As you indicated, SOMAXCONN is the value passed in when the SQL Server listening socket is opened to give the queue depth for the backlog queue.  Unfortunately, as you're probably aware, up through Windows Server 2019 SOMAXCONN is not exposed or configurable to administrators, but is instead set to a "reasonable value" by Windows.  Although I've not seen documentation for how Windows determines this value, I imagine it increases as RAM on the system increases.  CPU count may also  play a role.
One way to explore whether it is a TCP backlog queue full of partially-opened connections is by counting connections in the SYN Received state.
For example the following powershell will give counts for connections in each state for each (LocalPort, OwningProcess).
Get-NetTCPConnection | Group-Object -Property LocalPort, OwningProcess, State | Select -Property Name, Count | Sort Name

Unfortunately, while trend of connection counts will be helpful, without knowing SOMAXCONN it may not be definitive.
Unfortunately Windows does not provide the TCPExt stats in 'netstat -s' output; 'resets received for embryonic SYN_RECV sockets' could be especially useful here.
Without knowing the formula or resulting values for SOMAXCONN on a system, it's hard to know how much RAM would result in a higher SOMAXCONN value and more breathing room for partially opened connections. You mentioned a system with 2TB of RAM saw similar connection failures, it's likely you won't get much larger of a SOMAXCONN value than that.
And if the connection burst can't be throttled or capped at a lower maximum, then the only intervention with reasonable confidence is trying to speed up getting connections through the process.  Eliminating any use of paging space if present can help.  In a VM, oversubscription of CPU or memory can hurt (by slowing down connection completions during a connection burst).  In some cases RSS can help, but you noted that has already been tried.  WFP filters (Windows filtering platform) such as anti-virus and anti-malware can add overhead to connection completion; so any WFP filters in use may warrant review.
Use High Performance Power Plan rather than Balanced Power Plan.  If a connection burst immediately follows a period of low activity on the target server, the CPU will be starting to complete partially opened connections at a lower clock speed and this can contribute to a full backlog queue followed by connections denied.


Finding parameter names for binding stored procedure parameters For unit testing purposes I am attempting to call the DBMS_OUTPUT.PUT_LINE procedure from c# using parameters that are bound by name. Since this DBMS_OUTPUT package seems to be a standard on Oracle databases, I am using it to test the code that I have written.

I have been able to successfully call DBMS_OUTPUT.ENABLE with the OraleCommand.BindByName flag set to true. However, that is being called without passing any parameters. I have also been able to call a stored procedure that I made myself by passing a reference cursor bound by name.

When I attempt to call DBMS_OUTPUT.PUT_LINE in a similar fashion I get the following message:

Error Details

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I am specifying the bind name for the parameter as item according to how the procedure is defined in the 10g documentation for DBMS_OUTPUT. The parameter type for item is defined as VARCHAR2 and that is the type that is being passed to the procedure. When I set the OracleCommand.BindByName flag to false, I am able to call DBMS_OUTPUT.PUT_LINE with no problems.

Here is the code that I am using to run the query:

public bool ExecuteQuery(string procedureName, string[,] queryParameters, bool useReferenceCursor = false)
{
    bool returnStatus = false;

    if (ConnectionStatus() == false)
    {
        //message database connection was interrupted
        return false;
    }

    OracleCommand cmd = new OracleCommand();
    cmd.CommandText = procedureName;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.BindByName = true;

    cmd.Connection = databaseConnection;

    if (useReferenceCursor)
    {
        cmd.Parameters.Add("output_reference_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
    }

    for (int parameterSet = 0; parameterSet < queryParameters.GetLength(0); parameterSet++)
    {
        cmd.Parameters.Add(
            queryParameters[parameterSet, 0],
            OracleDbType.Varchar2,
            256,
            queryParameters[parameterSet, 1],
            ParameterDirection.Input
        );
    }

    if (ConnectionStatus() == false)
    {
        //message database connection was interrupted
        return false;
    }

    try
    {
        queryDataReader = cmd.ExecuteReader();
        queryHasRows = queryDataReader.HasRows;
        returnStatus = true;
    }
    catch (Oracle.DataAccess.Client.OracleException error)
    {
        switch (error.Number)
        {
            case 6550:
                Console.WriteLine("The stored procedure specified doesn't exist.");
                DatabaseErrorMessage("The stored procedure doesn't exist. Make sure that the procedure has been properly compiled and that the names match.");
                break;
            default:
                throw error;
        }
        Console.WriteLine(error.ToString());
        queryHasRows = false;
        returnStatus = false;
    }

    return returnStatus;
}

Just for reference, we have decided to pass all parameters to the procedures that we write as OracleDbType.Varchar2 and perform the type casting in the stored procedures that we define. The only exception to this would be reference cursors. I am also using Oracle's ODP.net for access since the Microsoft equivalent is obsolete.

Any feedback or suggestions would be greatly appreciated.

I did a little bit more research and found the answer to the problem.

Even though the first parameter for DBMS_OUTPUT.PUT_LINE is listed as item in the 10g documentation for DBMS_OUTPUT. The actual name for the parameter is A. You need to bind the name using DBMS_OUTPUT.PUT_LINE(A => 'random text').

The actual parameter names for stored procedures can be discovered using the following query:

SELECT
    argument_name,
    in_out,
    data_type
FROM
    all_arguments
WHERE
    owner = 'SYS' --owner for said procedure
    AND package_name = 'DBMS_OUTPUT' --if procedure is in a package that name, null otherwise
    AND object_name = 'PUT_LINE' --procedure name

The values SYS, DBMS_OUTPUT, and PUT_LINE can be replaced with the values of the the stored procedure that you would like to look up.


How to configure Ola Hallengren backup script for encryption How can I add encryption to ola.hallengren backup script.

In short i want to achieve this

BACKUP DATABASE [MyTestDB]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\MyTestDB.bak'
WITH
  COMPRESSION,
  ENCRYPTION
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = MyTestDBBackupEncryptCert
   ),
  STATS = 10
Go

In the documentation for Ola Hallengren's backup scripts, he explains these parameters:

@Encrypt Y/N - Y means encrypt the backup, N is the default
@EncryptionAlgorithm - sounds like you're looking for AES_256, which is a valid option that he supports
@ServerCertificate - so you can specify the one you want to use

At the bottom of the page, in the examples, there's an example to back up all user databases with compression, encryption, and a server certificate:

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'MyCertificate'

So based on my extensive research and a good thirty seconds of reading, it would be:

EXECUTE dbo.DatabaseBackup @Databases = 'MyTestDB',
@Directory = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\',
@BackupType = 'FULL',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'MyTestDBBackupEncryptCert'


SQL - Get the values of another column for values in a given column I am working with a large data table. I want to check the individual items with their destination and the count of destinations if there are more than 1 destination for a given item.
So in the below image, in my result, I should not get Items Grapes and Rice since there is only one destination for them. I want to filter by the destination as well. I need to remove the items which are going to the same destination. So here, only Apple and their respective destinations are the ones I need. Orange should be removed because its going to the same destination.
The query I have written is below. However it does not serve the purpose.This does not filter out same destinations. Any help please ?
select Item,
listagg(Destination, ',') within group (order by Destination) as Destination
from ProductsTable
group by Item

with data as
(
  select 'Apple' as item, 'Rome' as destination from dual union all
  select 'Apple' as item, 'Barcelona' as destination from dual union all
  select 'Apple' as item, 'Moscow' as destination from dual union all
  select 'Apple' as item, 'Paris' as destination from dual union all
  select 'Apple' as item, 'London' as destination from dual union all
  select 'Orange' as item, 'Cairo' as destination from dual union all
  select 'Orange' as item, 'Cairo' as destination from dual union all
  select 'Orange' as item, 'Cairo' as destination from dual union all
  select 'Grapes' as item, 'Berlin' as destination from dual union all
  select 'Rice' as item, 'Manila' as destination from dual
)
select Item,
listagg(Destination, ',') within group (order by Destination) as Destination
from data
group by Item having count(distinct destination) > 1;

ITEM   DESTINATION
------ --------------------------------------------------
Apple  Barcelona,London,Moscow,Paris,Rome