Postgres Backup queries on AWS after reading Postgres docs I am reading backup section of postgresql docs before heading over to create backup strategy on my EC2 instance.

So there are 2 ways to do it.

Create a dump using pg_dump.
File level backup with WAL archiving on. I will be using Snapshots so my use case falls in this category.

Now the question is do I need to freeze my filesystem before taking the backup? Postgresql docs suggest that WAL recovery does not get affected by an inconsistent snapshot. Does it make sense to do freeze just to be safe?

Should I take backup via both of above ways?

What sort of time period after which I should remove old WAL logs?

Docs also mention that WAL logs and data should be on the same filesystem to make backup consistent. In that case I am thinking to take backup of WAL logs separately from EBS snapshot and save it in S3 and some external place out of Amazon. Thoughts on this strategy?

Kindly mention if I am missing something.

EBS snapshots are atomic, they're a point-in-time. This means that for PostgreSQL, so long as all the datadir including WAL, and all tablespaces are in the same EBS volume, starting from a snapshot is equivalent to starting after an unclean shutdown (crash), which PostgreSQL is designed to do reliably every time.

You do not need to stop I/O to the volume to take an EBS snapshot if everything is in the same EBS volume.

You also don't need to stop I/O if you're using pg_start_backup and pg_stop_backup along with file-level copies, but you must capture all the WAL files up to the one generated after the end of pg_stop_backup. See the manual.

As for S3 storage, etc, check out barman; it can automate most of this for you and supports hook scripts you can use to put backups in S3.


Percentage difference of the last value from the previous values of a column based on certain data group within the same table I have a table shown in below image. Actually i want to calculate the percentage difference in this table. The methodology of calculating the difference will be as following.

1-Records are logically grouped by Column2.

2-We want to compare value of column3 with the other records of the group but the column3 value is taken based on the max-value of column 1 in the group.

If your version of SQL Server is recent enough (2012+), the problem can be solved with the window/analytic function LAST_VALUE():

Column4 = (LAST_VALUE(Column3) OVER (PARTITION BY Column2 ORDER BY Column1)
           - Column3) / Column3 * 100

or FIRST_VAUE() with inverse order:

Column4 = (FIRST_VALUE(Column3) OVER (PARTITION BY Column2 ORDER BY Column1 DESC)
           - Column3) / Column3 * 100


Performance of BINARY vs CAST AS BINARY in MySQL Using MySQL 5.6.23, I recently changed a prepared statement from:

SELECT * FROM table WHERE BINARY column = :boundValue

to:

SELECT * FROM TABLE WHERE CAST(column AS BINARY) = :boundValue

According to the MySQL docs on BINARY:

  BINARY str is shorthand for CAST(str AS BINARY)

So I expected these two prepared statements to have the same run-time performance. However, the CAST version was significantly slower. I mean orders of magnitude difference.

The query plans are identical:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: table
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where

CREATE TABLE `session_detail_binary` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `column` varchar(64) NOT NULL DEFAULT '',
  `foo` varchar(64) DEFAULT NULL,
  `bar` varbinary(4096) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_column` (`column`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

There are about 260k rows in this table.

What's going on here?

UPDATE: I believe this was a red herring. At the same time this SELECT .. WHERE x runs, there were a lot of UPDATE .. WHERE x on the same table and under the same where condition, where "a lot" means 100x more. This suggests to me the row locks were handicapping the selection.

On my tests, I do not see a significant difference (full table scan, 6 rows only, like your example):

$ for i in $(seq 1 10); do for i in $(seq 1 100000); do echo "SELECT * FROM t1 WHERE CAST(c AS BINARY) = 'f';"; done | /usr/bin/time -f "%e" mysql -BN test > /dev/null; done
5.10
4.93
5.00
4.96
4.97
5.21
4.95
5.04
5.00
5.03
$ for i in $(seq 1 10); do for i in $(seq 1 100000); do echo "SELECT * FROM t1 WHERE BINARY c = 'f';"; done | /usr/bin/time -f "%e" mysql -BN test > /dev/null; done
5.10
5.28
4.84
5.00
5.19
5.17
7.14
5.06
4.86
6.24

If the table had more than 6 rows, an index (and therfore, a query plan change) would be helpful, but only if functions are applied to the right side, not to the column name:

MariaDB [test]> EXPLAIN SELECT * FROM t1 USE INDEX(c) WHERE c = BINARY 'f'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: c
          key: c
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

MariaDB [test]> EXPLAIN SELECT * FROM t1 USE INDEX(c) WHERE BINARY c = 'f'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
1 row in set (0.00 sec)

MariaDB [test]> EXPLAIN SELECT * FROM t1 USE INDEX(c) WHERE CAST(c AS BINARY) = 'f'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
1 row in set (0.00 sec)

MariaDB [test]> EXPLAIN SELECT * FROM t1 USE INDEX(c) WHERE c = CAST('f' AS BINARY)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: c
          key: c
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)


Insert data in relation table and into data table with less query cost I have a similar table layout:

create table data ( id int identity primary key
                  , value int not null);

create table relation (id int identity primary key
                      , someid int not null
                      , dataid int foreign key references data(id));

When inserting data into the tables, in most of the cases I need to insert something in data and also in relation.

Currently I do it like this:

-- insert data.value = 1337 and relation.someid = 42 + relation.dataid = 1337
if not exists (select id from data where value = 1337)
    insert into data (value)
    values (1337);
    insert into relation (someid, dataid)
    values (42, (select id from data where value = 1337));

Is this the proper solution?

What can I do if I need to insert many of these lines?

Currently I insert all data into data and then do the queries for relation.

Use the OUTPUT clause of the INSERT statement to capture the identity values created in data.  Then use that to write to relation.

create table #data ( id int  primary key
                  , value int not null);

if not exists (select id from data where value = 1337)
    insert into data (value)
    OUTPUT INSERTED.id, INSERTED.value
        INTO #data
    values (1337);

select * from #data;

insert into relation (someid, dataid)
select
    42, id
from #data;

select * from relation;


truncating transaction log for simple recovery databases We have a couple of databases which are in simple recovery model. When the weekly rebuild job runs, the tlog drive is becoming full every time. How do I control the size tlog from every growing?Because these are in simple recover - should I be resizing the tlog autogrowth in chunks of 512mb instead of 10%. Would that help?

Just because database is in simple recovery does means transaction log will not grow. Actually there is not much difference in terms of logging in full and simple recovery.

In simple recovery model transaction log is truncated when checkpoint is fired and log file grows 70% of its size subject to condition no long running transaction is holding the logs in your case it is the index rebuild which is holding things from being truncated leading to log file increase.

What you can do is use smart index rebuild technique dont rely on SSMS. There are quite few available Ola Hallengren Index rebuild, Minion Index rebuild and My Custom Script.

It may also be possible that the drive on which log file resides needs more space because your indexes are big and needs space and you have to add more space.

Please also read Why Does the Transaction Log Keep Growing or Run Out of Space?


How to update MySQL data within intranet from MySQL data in cloud? I am currently using a MySQL DB located within the intranet. Only the people within the LAN can access the information.

I'm trying to re-create the MySQL DB in a cloud provider. This can be accessed by other people from a different location.

When people update the MySQL DB in the cloud, is there a way to synchronize the information to the MySQL DB located within the intranet and vice versa?

I was thinking of using MySQL Job Event, like exporting data to an insert/update script file then dumping the file onto a local intranet folder. The intranet MySQL will also have a Job Event checking for this file if there's a new dump.

I am not sure if this is the right way to do such a task.

Any helpful tips are really appreciated.

MySQL has a built-in functionality called MySQL replication that allows the synchronisation between 2 servers, by applying on a "slave" the operations that have been done on the "master". If no writes are done on the slave, that works pretty well and, while it has its issues, it is widely used by most MySQL users (Facebook, Twitter, etc.) for high availability, load balancing and/or testing/maintenance operations.

It is very easy to setup and has little performance impact, as it is asynchronous by default. That makes possible to perform the replication in real time or in batches, as desired.

Please note that while all "Infraestructure as a service" allows replication, some "database as a service" providers do not allow to do it outside of their servers (for example, RDS only allowed it in MySQL 5.6 servers).

If you need to write to the slave (your intranet server), that would be more complex, as you have to make a plan on how to merge the results and modify the replication settings or use an alternative technology.


Are InnoDB tables harder to backup and slower? I am trying to optimize our database tables. I have some sql skills, but complete newbie at db administration. Our db operations are pretty slow, and I have been assigned to review the storage engines and create indexes. So, here's the thing... Most of our tables are MyISAM. And most of the tables are used for both read and write, constantly. The tables are also very related, and most selects pull from at least 4 or 5 tables. After hours spent reading recent blog posts and stack exchange answers - looks like InnoDB would be the way to go.

But, our db is hosted, and after discussing briefly with our host about switching to InnoDB, he came up with the following complaints

InnoDB is slower at most things
InnoDB is hard to backup
InnoDB only shines when you have complex joins on tables with udpates and inserts.

So here is my question - are his complaints valid? Should I only select a chosen few tables to convert to InnoDB, the ones with really complex joins?

Thanks

Have a look at this link What are the main differences between InnoDB and MyISAM.?

After looking into this you will somewhat able to decide what to do.

From your question prospective :

1.InnoDB is slower at most things:

It is not true well designed InnoDB tables performs very well i have a 1TB of data with InnoDB table and is performing well

the reason for poor performance may be

  improper use of indexes

  fragmentation in tables

  improper setting of innodb variables like innodb_buffer_pool , log
  files,how the table space is configured

There may be a lot of reasons due which it doesn't perform well. Have a look how to tune InnoDB Performance

2.InnoDB is hard to backup

It is not also very hard to backup the InnoDB tables there might be time that you feel it is tough because of its table space/log files/frm files.As for a large MyISAM table you can directly copy the databases using the copy operation and it is quite fast.

due to that you might feel that backing up InnoDB is hard but it is not so.

There are proper backup techniques for InnoDB tables also, please see the following link for this Backing Up and Recovering an InnoDB Database.

3.InnoDB only shines when you have complex joins on tables with udpates and inserts.

No they can also perform well in all cases in which the MyISAM can.


How to determine if insert or update Whenever INSERT is happened in the CUSTOMER table,I need to call the "StoredProcedure1"and
UPDATE is happend in the  CUSTOMER table,I need to call the "StoredProcedure2" in the Trigger.
How to determine if insert or update in the trigger  from SQL Server 2008.

Some one can please help me how to solve?

Code:

CREATE TRIGGER Notifications ON CUSTOMER
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @recordId varchar(20);
set @recordId= new.Id;
    //if trigger is insert at the time I call to SP1
        EXEC StoredProcedure1 @recordId
    //if trigger is Upadeted at the time I call to SP2
        EXEC StoredProcedure2 @recordId
END

Since it's only UPDATE,INSERT you can say:

IF EXISTS (SELECT 1 FROM deleted)
  -- update
ELSE
  -- insert

You have a bigger problem, though. There is no such thing as new.Id, and an insert or update can affect multiple rows (in some platforms, triggers fire per row; in SQL Server, they fire per operation). So you need to either:

Use a loop to call the stored procedure for all of the RecordId values in inserted.
Stop using a stored procedure for this and perform whatever logic it does inside the trigger, as a set-based operation.


Revoke privileges for a user on tables with like names Attempting to adapt this solution, but for revoking privileges in this way:
DO
$$
DECLARE
    t record;
BEGIN
    FOR t IN
        SELECT table_schema, table_name
        FROM information_schema.tables
        WHERE table_schema = 'my_schema'
        AND table_name LIKE 'test\_%'
        LOOP
            EXECUTE format('REVOKE DELETE, INSERT, UPDATE ON TABLE %I.%I FROM test;', table_schema, table_name);
        END LOOP;
END;
$$ LANGUAGE plpgsql;

However, I'm getting "column table_schema does not exist". Am I approaching this the right way?
Using postgresql 9.6 (french locale).

You need to reference the record of the cursor loop:
EXECUTE format('REVOKE DELETE, INSERT, UPDATE ON TABLE %I.%I FROM test;',
               t.table_schema, t.table_name);
               ^-- here        ^-- here


SQL Connector for Azure Key Vault - multiple key vaults and keys per instance In SQL Server 2016 Enterprise Edition, is it possible to link a single instance to more than one Azure key vault / EKM provider? If so, how would I add an additional provider? Any security considerations?

Also, is it possible to encrypt each individual database with different encryption keys, or is the same encryption key used for all databases?

In SQL Server 2016 Enterprise Edition, is it possible to link a single
  instance to more than one Azure key vault / EKM provider? If so, how
  would I add an additional provider? Any security considerations?

You don't add an additional provider, the provider is simply a reference to a DLL on the SQL Server that provides the interface to your EKM provider.

You add a credential to interact with the key vault (using the FOR CRYPTOGRAPHIC PROVIDER option) and associate that credential with a login. If you have additional key vaults, you add additional credentials and additional logins.

  Also, is it possible to encrypt each individual database with
  different encryption keys, or is the same encryption key used for all
  databases?

(Assuming you mean TDE) Yes. You create a database encryption key, scoped to the DB in question, protected by a certificate or server asymmetric key per database. You already get a different encryption key per DB and you can even protect the DEK using a different cert or asymmetric key per database.

Scenario:
I want to protect 2 databases with TDE using individual Asymmetric Keys provided by different Azure Key Vaults.

Steps:

Create Cryptographic Provider for Azure Key Vault
Create Credential1 for accessing AKV1
Associate Credential1 with sysadmin user
Create Asymmetric Key1 from AKV1
Create Login1 from Asymmetric Key1
Remove Credential1 from sysadmin user
Add Credential1 to Login1
Configure TDE on DB1 using Asymmetric Key1
Create Credential2 for accessing AKV2
Associate Credential2 with sysadmin user
Create Asymmetric Key2 from AKV2
Create Login2 from Asymmetric Key2
Remove Credential2 from sysadmin user
Add Credential2 to Login2
Configure TDE on DB2 using Asymmetric Key2


Trigger xp_LogEvent based on inserted cell value I need to create a trigger on a table (after insert) that simply checks for a string in a cell. If the string exists, I need to log an event in the windows event logger. I am not really sure if I am able to use the inserted tables this way, but I thought I would try. This is what I have so far:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER bcpCatchTrigger
   ON  dbo.TABLE_NAME
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @@Message varchar(255), @@go int;
    SET @@go = 0

    CASE
        WHEN inserted.STAT_MESSAGE LIKE '%bcp.exe%' THEN @@go = 1
    END

    IF @@go == 1 {
        @@Message = inserted.STAT_MESSAGE
        EXEC xp_logevent 60000, @@Message, ERROR;
    }

END
GO

I get incorrect syntax at the CASE statement but I don't quite see why. Perhaps the issue is actually how I'm accessing the inserted table. Where am I going wrong here?

Thank you.

EDITED CODE:

SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER bcpCatchTrigger
   ON  dbo.TABLE_NAME
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @@Message varchar(255), @@go int;
    SET @@go = CASE
        WHEN (SELECT STAT_MESSAGE FROM INSERTED) LIKE '%bcp.exe%' THEN 1
        ELSE 0
    END

    IF (@@go = 1) BEGIN
        SET @@Message = (SELECT STAT_MESSAGE FROM INSERTED)
        EXEC xp_logevent 60000, @@Message, ERROR;
    END

END
GO

Let's start with the main issue of this trigger:

SET @@Message = (SELECT STAT_MESSAGE FROM dbo.TABLE_NAME)

This will produce the following error

  Subquery returned more than 1 value. This is not permitted when the
  subquery follows =, !=, <, <= , >, >= or when the subquery is used as
  an expression.

as soon as your INSERT statement will have more than one row.

Others are more suggestions than corrections.

It's a good practice to check if the insert did insert any row and
if not just exit the trigger (if @@rowcount = 0 return;)
You also should not execute the code that reads from inserted
twice.
Finally, I changed it to read only the first occasion of
STAT_MESSAGE LIKE '%bcp.exe%' (select top 1) but you should think
about the case when multiple rows LIKE '%bcp.exe%' are inserted: if
you want to log all of them you should use a loop.

Here is your trigger modified:

alter TRIGGER bcpCatchTrigger
   ON  dbo.TABLE_NAME
   AFTER INSERT
AS
BEGIN
if @@rowcount = 0 return;

SET NOCOUNT ON;

DECLARE @@Message varchar(255);

SELECT @@Message = (select top 1 STAT_MESSAGE
                    FROM INSERTED
                    where STAT_MESSAGE LIKE '%bcp.exe%')
if @@Message is NULL return;

EXEC xp_logevent 60000, @@Message, ERROR;
END;


SQL Server mirroring not failing over I have two SQL Server 2016 installations configured in a mirror with a separate server as the witness. We've been running some failover tests and every test has succeeded except the following two scenarios. Does anyone know why these scenarios wouldn't result in a failover?

Dismounting the storage that the database files reside on the primary server will not fail the server over. I thought the witness would notice that the files do not exist anymore and fail over?
Throttling the network down to 1kbps on the primary server disconnects the mirror on both the primary and secondary. I would think that the witness would lose connectivity to the primary because the network is so slow and fail over but instead both servers go to disconnected status.

Has anyone ran into any of these issues?

•Dismounting the storage that the database files reside on the primary server will not fail the server over. I thought the witness would notice that the files do not exist anymore and fail over?

Database mirroring does not look at the individual database, even though that's the unity of failover. It's the same as an availability group in 2012/2014 or 2016 without the database failover option.

This is expected.

  •Throttling the network down to 1kbps on the primary server disconnects the mirror on both the primary and secondary. I would think that the witness would lose connectivity to the primary because the network is so slow and fail over but instead both servers go to disconnected status.

If all are disconnected because the network is exceeding sluggish or gone, then it won't be able to talk between the servers for a failover - the databases need to be synchronized, the primary is online and the session timeout for the mirror elapsed so it looks like a mirror disconnect rather than a primary issue. Even if something were to happen, after the disconnect the databases would not be in a state for automatically fail over.

Reference of the required conditions for automatic failover for mirroring.

Also, with Mirroring completely gone at this point, I'd suggest migrating away from using it... though you probably already know this :)


Restore Verify Only option vs Manual Restore My question is related to best practices with SQL Server. I need to put together a document that lists our various databases and that the backups we have of these databases are valid and have been tested and we know that they will restore properly in case of a disaster recovery scenario. I'm using the Ola H. maintenance solution which backs up the databases with the RESTORE VERIFY option and gives a "succeeded" message to indicate that the backupset is valid. Is this enough to be confident or should we still be manually restoring these backups to a test server and running tests?

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

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

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

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

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


Conversion failed error - but not converting anything and no date/time data I am creating a table variable in T-SQL and encountering the "Conversion failed when converting date and/or time from character string", despite the fact that I'm not converting anything and the column in question contains no date data.

Code below:

Declare @table2 (variable1 bigint, variable2 varchar(255), variablen basetypen)

insert into @table2
Select Variable1, variable2
from
etc...

Everything is working, except inserting variable2.  Selecting works fine, and I've checked every single value (there only are 10) and none of them contains a date or time value, or anything which looks like one.  (Can't post text because it would be identifying/proprietary).  In fact, every one has exactly the same value.

What's going on here?  I feel as though I might be doing something silly...

You didn't post your actual code, but SQL Server isn't throwing that message if there isn't an actual conversion error.

You say you aren't converting anything, but if you insert data of one data type into a column of another data type SQL Server will try to convert it implicitly.

Look at Data Type Conversion for more information:

  Implicit conversions are not visible to the user. SQL Server
  automatically converts the data from one data type to another. For
  example, when a smallint is compared to an int, the smallint is
  implicitly converted to int before the comparison proceeds.

That would happen not only with comparison but also with insert statements


Index Seek vs Index Scan Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan.

What is the difference between and index seek and an index scan?

Which performs better?

How does SQL choose one over the other?

I realise this is 3 questions but I think answering the first one will explain the others.

Short version: seek is much better

Less short version: seek is generally much better, but a great many seeks (caused by bad query design with nasty correlated sub-queries for instance, or because you are making many queries in a cursor operation or other loop) can be worse than a scan, especially if your query may end up returning data from most of the rows in the affected table.

It helps to cover the whole family for data finding operations to fully understand the performance implications.

Table Scans: With no indexes at all that are relevant to your query the planner is forced to use a table scan meaning that every row is looked at. This can result in every page relating to the table's data being read from disk which is often the worst case. Note that for some queries it will use a table scan even when a useful index is present - this is usually because the data in the table is so small that it is more hassle to traverse the indexes (if this is the case you would expect the plan to change as the data grows, assuming the selectivity measure of the index is good).

Index Scans with Row Lookups: With no index that can be directly used for a seek is found but an index containing the right columns is present an index scan may be used. For instance, if you have a large table with 20 columns with an index on column1,col2,col3 and you issue SELECT col4 FROM exampletable WHERE col2=616, in this case scanning the index to query col2 is better than scanning the whole table. Once matching rows are found then the data pages need to be read to pickup col4 for output (or further joining) which is what the "bookmark lookup" stage is when you see it in query plans.

Index Scans without Row Lookups: If the above example was SELECT col1, col2, col3 FROM exampletable WHERE col2=616 then the extra effort to read data pages is not needed: once index rows matching col2=616 are found all the requested data is known. This is why you sometimes see columns that will never be searched on, but are likely to be requested for output, added to the end of indexes - it can save row lookups. When adding columns to an index for this reason and this reason only, add them with the INCLUDE clause to tell the engine that it doesn't need to optimise index layout for querying based on these columns (this can speed up updates made to those columns). Index scans can result from queries with no filtering clauses too: SELECT col2 FROM exampletable will scan this example index instead of the table pages.

Index Seeks (with or without row lookups): In a seek not all of the index is considered. For the query SELECT * FROM exampletable WHERE c1 BETWEEN 1234 AND 4567 the query engine can find the first row that will match by doing a tree-based search on the index on c1 then it can navigate the index in order until it gets to the end of the range (this is the same with a query for c1=1234 as there could be many rows matching the condition even for an = operation). This means that only relevant index pages (plus a few needed for the initial search) need to be read instead of every page in the index (or table).

Clustered Indexes: With a clustered index the table data is stored in the leaf nodes of that index instead of being in a separate heap structure. This means that there will never need to be any extra row lookups after finding rows using that index no matter what columns are needed [unless you have off-page data like TEXT columns or VARCHAR(MAX) columns containing long data].

You can only have one clustered index for this reason[1], the clustered index is your table instead of having a separate heap structure, so if you use one[2] chose where you put it carefully in order to get maximum gain.

Also note that the clustered index because the "clustering key" for the table and is included in every non-clustered index on the table, so a wide clustered index is generally not a good idea.

[1] Actually, you can effectively have multiple clustered indexes by defining non-clustered indexes that cover or include every column on the table, but this is likely to be wasteful of space has a write performance impact so if you consider doing it make sure you really need to.

[2] When I say "if you use a clustered index", do note that it is generally recommended that you do have one on each table. There are exceptions as with all rules-of-thumb, tables that see little other than bulk inserts and unordered reads (staging tables for ETL processes perhaps) being the most common counter example.

Additional point: Incomplete Scans:

It is important to remember that depending on the rest of the query a table/index scan may not actually scan the whole table - if the logic allows the query plan may be able to cause it to abort early. The simplest example of this is SELECT TOP(1) * FROM HugeTable - if you look at the query plan for that you'll see that only one row was returned from the scan and if you watch the IO statistics (SET STATISTICS IO ON; SELECT TOP(1) * FROM HugeTable) you'll see that it only read a very small number of pages (perhaps just one).

The same can happen if the predicate of a WHERE or JOIN ... ON clause can be run concurrently with the scan that is the source if its data. The query planner/runner can sometimes be very clever about pushing predicates back towards the data sources to allow early termination of scans in this way (and sometimes you can be clever in rearranging queries to help it do so!). While the data flows right-to-left as per the arrows in the standard query plan display, the logic runs left-to-right and each step (right-to-left) is not necessarily run to completion before the next can start. In the simple example above if you look at each block in the query plan as an agent the SELECT agent asks the TOP agent for a row which in turn asks the TABLE SCAN agent for one, then the SELECT agent asks for another but the TOP agent knows there is no need doesn't bother to even ask the table reader, the SELECT agent gets a "no more is relevant" response and knows all the work is done. Many operations block this sort of optimisation of course so often in more complicated examples a table/index scan really does read every row, but be careful not to jump to the conclusion that any scan must be an expensive operation.


Postgres security for functions with "security definer" I was reading about possible security issues when creating functions in Postgres with "security definer". Official documentation suggests that search_path is set to some trusted schema followed by pg_temp. All examples I could find had pg_temp at the end. Do I have to put pg_temp at the end, what would happen if set search_path only to the trusted schema? Would it open any security holes? And why would you put pg_temp in search_path anyway?

pg_temp is always in the search path.  If you don't add it to search_path parameter explicitly, it will behave as if it were there at the beginning of the setting.  That means the user could create tables which masked the ones the function was supposed to operate on.


XML column performance in TSQL I'm finding very limited reading on how a large xml column affects table performance in sql server 2014/6.  For reference, it is a 36gig table and minus one xml column, it is 36megs.  Some queries call for the column (usually single row requests), most do not.  Is there a significant performance benefit to breaking that xml column out to its own linked table?

The database engine is pretty smart about this and does not read LOB pages unless it has to as far as I can tell, but I created a test-rig to double-check.

Test Rig

USE master
GO

SET NOCOUNT ON
GO

CREATE DATABASE bigXMLTest
GO
ALTER DATABASE bigXMLTest SET RECOVERY SIMPLE
GO
ALTER DATABASE bigXMLTest MODIFY FILE ( NAME = N'bigXMLTest', SIZE = 20GB , FILEGROWTH = 128MB )
GO
ALTER DATABASE bigXMLTest MODIFY FILE ( NAME = N'bigXMLTest_log', SIZE = 512MB )
GO

USE bigXMLTest
GO

IF OBJECT_ID('dbo.largeTable') IS NOT NULL DROP TABLE dbo.largeTable
GO

CREATE TABLE dbo.largeTable (

    rowId INT IDENTITY PRIMARY KEY,

    someDate    DATETIME DEFAULT GETDATE(),
    someData    UNIQUEIDENTIFIER DEFAULT NEWID(),
    someXML     XML NOT NULL
)
GO

-- Add dummy data
;WITH cte AS (
SELECT TOP 100000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.largeTable ( someDate, someData, someXML )
SELECT
    DATEADD( day, rn % 333, '1 Jan 2016' ),
    NEWID(),
    ( SELECT TOP 100 * FROM sys.messages WHERE language_id = 1033 FOR XML PATH, ROOT('root'), TYPE )    -- creates 24k size piece of XML on my machine
FROM cte

CHECKPOINT
GO 10

EXEC sp_spaceused 'dbo.largeTable'
GO

This creates a test table with 1 million rows, the non-XML data around 36MB and the XML data about 23GB.

Test Queries

--DROP INDEX _udx ON dbo.largeTable

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT *
FROM dbo.largeTable

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT rowId, someDate, someData    --, someXML
FROM dbo.largeTable

-- Add covering index for the query
CREATE UNIQUE INDEX _udx ON dbo.largeTable ( rowId ) INCLUDE ( someDate, someData )

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT *
FROM dbo.largeTable

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT rowId, someDate, someData    --, someXML
FROM dbo.largeTable

Test Results

Analysis

Query 1: A select *... causes a full table scan, so the database engine does a high number of LOB reads to read the XML which is stored out of row in LOB pages.  XML can be stored in-row if it's small enough, but the default is out-of-row.  This query takes 5 minutes, ie if you are reading LOB pages on a table like this, you will know about it.

Query 2 A select with column list not including the XML column, has the same number of logical reads in the top window (11,154), but no LOB reads.  The query takes less than a second.

Query 3:  After the non-clustered index has been added, a select * still causes a full table scan as expected.  This query behaves the same as Query 1.

Query 4:  After the non-clustered index has been added, it covers the select with column list query so there is a non-clustered index scan with no LOB reads and lower logical reads overall.

The LOB Reads looks like a broad categorisation as the XML storage pages are classed as TEXT_MIX_PAGE in the sys.dm_db_database_page_allocations DMV:

In summary, it looks like there would be little benefit to splitting the same as the database engine can differentiate when and when not to perform LOB reads in this admittedly simple test rig.  See here for some further details.


Why Oracle SQL Developer does not need Oracle client? I installed Oracle SQL Developer from Oracle's web page. It seems to work without a Oracle client installed on my computer. How is that possible? I also installed Toad for Oracle and it seems to require Oracle client.

SQL Developer is using the so called "thin driver".

For this the JDBC driver (a file named ojdbc6.jar) implements the complete Oracle network protocol which is usually handled by the Oracle client. It could use the another implementation: the "OCI" (or "thick") driver which would require an Oracle client to be installed. That would be necessary if e.g. Transparent Application Failover in a RAC is required.

This is not something specific to SQL Developer but to any Java application using JDBC to connect to the database.


Extended event (sp_statement_completed) firing multiple times I'm using SQL Server 2008 R2 enterprise and this is my first foray into extended events. It's pretty cool stuff. I'm wanting to create a session that will run basically all the time to audit query times on our SQL Server. I will then be able to pipe that data into a product called Splunk and correlate that data with all sorts of other metrics.

Anyway, I'm having some odd things happen (odd to me ... I'm sure there is a reason) with the sp_statement_completed event. The .xel file is growing SUPER fast ... like 1GB in a minute fast. Looking at the data I see the same SQL statements duplicated sometimes 75-100 times. What can I do to deduplicate/filter this data prior to going into the file? Many of them have a cpu of zero and duration of zero. I think I might even be seeing a little duplication using sql_statement_completed. I've also noticed that I do see stored procedure end times using the sql_statement_completed event. So, is there even a need to use sp_statement_completed?

Any thoughts would be most welcomed and thanks in advance.

Looks like the issue is because my stored procedures and ad hoc queries might be running more than one sql statement ... thus creating multiple hits. Really what I need is batch complete. Alas, it does not exist in SQL Server 2008. Probably gonna have to resort to a server trace.


Delete many rows from a large percona mysql db I need a fresh opinion on the case. Any thoughts are appreciated.

Input: we have a huge percona mysql (5.5) database that takes a couple of Tb (terabytes). Tables on innodb engine.
More than a half (2/3) of that size should be deleted as quick as possible.
Also we have master-slave configuration.

As the quickest way to achieve that I am considering the following solution:
Execute for each table on the slave server (to avoid production downtime) :

Stop replication
Select the rows NOT to be deleted into an empty new table that has the same structure as the original table
Rename original table to "table_old", new table - to correct name
Drop the original table "table_old"
Start replication

The problem is that we have a lot of FK constraints. Also I am afraid to break the replication during this process.

Questions:
1) What the potential problems can be with FK constraints in this solution?
2) How do not break replication?
3) Opinions? Alternative solutions?

Thank you in advance.

Your procedure is excellent if you have the disk space.

Disable foreign keys while doing such.  (And hope that no one creates an FK violation that won't be caught.)

Do the two renames in a single statement: RENAME TABLE real TO old, new TO real;.

Replication should not be an issue -- except that a single SELECT to do the copy will be cause a big replication delay.

If you don't have the disk space for that, you are in deep weeds.  No table structure ever shrinks.

More discussion of big deletes.

That discusses "chunking", which could be adapted to the big SELECT, so that it has less impact on replication.

Or the chunking could be done to do the deletes, without all the rest of the stuff.  This also avoids the FK question and the replication delays.  However, it does not shrink the resulting table size; it leaves lots of free space.  You could then use pt-online-schema-change to merely copy the table over, thereby shrinking the table.  Hmmm... I guess this is how to do the deletes and free up disk space without needing extra space.  But it won't help if you have innodb_file_per_table=OFF.


database creation using codeigniter database dbforge class gives "Access denied for user 'username'@'localhost' to database 'database_name' " Using codeigniter database dbforge class I have created a database and loaded that database as the current database and created tables in localhost WAMP server. But when I uploaded it to the server I am not able to create the database using dbforge class.

$this->dbforge->create_database($database_name)

This command line gave me the following error:

Error No 1044: Access denied for user 'username'@'localhost' to database 'database_name'

I am using the 'cpanel' hosting provider .

EDIT1: How we can give permission for a mysql user to create database in cPanel hosting provider...?

EDIT2: The Privileges list of user does not show 'CREATE DATABASE' Privilege in Cpanel. The user is created through cPanel and it have been given all the available privileges, but the CREATE DATABASE privilege is not available in that list in cpanel.

Using C-Panel / WHM, you create databases on a per application basis. Let's say that your c-panel username is john123 and you were installing a blog. You'd do the following:

Go to c-panel -> databases -> and create the database john123_blog
Go to databases -> users and create a user to access it john123_blogu
Add the user to the database, and give them the appropriate permissions

At that point, you can use the dbforge class to create tables within the database. In a shared hosting setup, users are often limited to how many databases they can have, so database creation itself has to go through the automation software (in this case, c-panel).

There's nothing wrong with your code, it's just assuming a non-shared environment.


MongoDB replica sets and data duplication I am new to MongoDB and trying to understand replica sets. Specifically, I am trying to understand how MongoDB would handle the following situation:

A record is written to a table with a unique index on the primary Mongo member (Mongo 1).
Mongo #1 crashes before the new record is propagated to the secondary Mongo member (Mongo 2).
Mongo 2 and the arbiter elect Mongo 2 to be the primary.
The application attempts to create a duplicate record in the table. Because Mongo 2 is not aware of the record written to Mongo 1, the unique constraint is not enforced.
Mongo 1 becomes available again and replication resumes.

What happens next? Does Mongo 1 attempt to replicate the record to Mongo 2? Or Mongo 2 to Mongo 1? Or both simultaneously? How is the conflict resolved?

In this case, the following will happen:

When a member rejoins the replica set, the point in time on which the oplog is identical on the cluster is figured out.
If there are oplog entries beyond said given point in time, those entries are tried to be reverted and saved for human analysis. This is called a rollback in MongoLand.
If the node was down for a longer time than the oplog window, it becomes stale as usual, and a manual resync is required. In this case all the entries of the oplog are rolled back when the node comes up again.
The maximum size for a rollback is 300MB. While this sounds little, it is actually quite some, as those 300MB only need to hold the data in which a primary has to figure out that it is not longer part of a replica set (may be through network partitioning).

Please read more on this at Rollbacks During Replica Set Failover and Resync a Member of a Replica Set in the docs.


Query intermittently fails with "The multi-part identifier could not be bound" I've got Schrodinger's query here; you can run it once successfully, then 5 times in a row, and it works fine. Change anything in the query, even whitespace, run it again, and it might stop working the next 5 times. It seems something breaks when it's cached; it it doesn't run once, the same query won't run properly until it's changed. Sometimes the same exact query that run before won't run or vice versa.

The query is run against multiple remote servers (one at a time) so the problem seems to be related to that. They're linked servers and I can query them properly with a normal query. The servers are matching versions (SS 2005). If it matters I'm running this via SSMS 2008, though my automated script (PHP with SQLSRV driver) appears to be having the same problem.

Here's the simplest statement I could make that still gets the error:

UPDATE LocalServer.DB.dbo.table
SET loc.[ChangeStatus] = rem.[ChangeStatus]
FROM LocalServer.DB.dbo.table as loc,
RemoteServer.DB.dbo.table as rem WHERE loc.LocationID = '1'

This basically updates data from the only row in the remote table with the matching row in my local table; it's a Cartesian join but technically I want that.

The error is:

  The multi-part identifier "loc.ChangeStatus" could not be bound.

But I don't think the problem is in the SQL, something's getting cached wrong or something and I think it's forgetting my alias or some form of metadata necessary. But only sometimes.

Some of our remote servers are on satellite connects which go down or time out, but I can reproduce this on a DSL location that's 5 blocks away where it never times out. What's going wrong here and how can I fix it? What would cause the identifier to fail like that?

If it's just one row, how about:

UPDATE LocalServer.DB.dbo.table
SET [ChangeStatus] =
(
  SELECT [ChangeStatus]
  FROM RemoteServer.DB.dbo.table
)
WHERE LocationID = '1';

Now you've eliminated the join and any aliases that have to be bound. Also why are you referencing both with 4-part names? If the local server is really the same instance, why not reference the local as DB.dbo.table instead of LocalServer.DB.dbo.table? If you take out the four-part name on the outer table, you can also do this for multi-row updates. It's not as pretty to do the aliasing this way, though:

UPDATE DB.dbo.table
SET [ChangeStatus] =
(
  SELECT [ChangeStatus]
  FROM RemoteServer.DB.dbo.table
  WHERE key_col = DB.dbo.table.key_col
)
WHERE LocationID = '1';

And as we discussed offline, if it's multiple rows and multiple columns, you should use something like this:

UPDATE t
SET [ChangeStatus] = r.[ChangeStatus],
    [OtherColumn]  = r.[OtherColumn]
FROM DB.dbo.table AS t
INNER JOIN RemoteServer.DB.dbo.table AS r
  ON t.key_col = r.key_col
WHERE t.LocationID = '1';

I believe that proper aliasing and eliminating the 4-part name should cause less work for the relevant parts of the execution process.


GFS2 as shared storage for MySQL cluster with active active setting I am using MySQL 5.7 on RHEL 7.1. For cluster management I am using Pacemaker and corosync. For shared storage, I am using GFS2 file system with iSCSI server

I am trying to implement MySQL active-active cluster (both nodes running at the same time). I have two nodes with MySQL server running. I am not able to give same data directory (shared storage) for both the nodes.

I tried to change the data directory in my.cnf file. After that the service didn't start. Currently, I am using the default storage engine i.e. InnoDB storage engine.

Is this a problem of the underlying storage engine or something else we need to change in the configuration?

Two instances of mysqld must not use the same data files.  The code is not designed to handle unexpected changes to the file, which will happen by the other instance.

(I assume you are talking about Master-Master replication when you say "cluster"?)


Select Query takes more than a minute to retrieve data I have a table that has 99 columns, all of them are not needed but they were there when we started. There is a Primary Key (Objectuid) which is a GUI and has the Clustered Index on the PK.

The Table is updated often and has close to 2 million rows. there are no other non clustered indexes in this table.

I have a view that pulls necessary field from the table and does a left outer join with another table which has only 7 rows. It then adds 4 filters on the where clause. it retrieves about half a million record but takes about a minute.

Is it normal? How can I make it to run for less than five seconds? The Server has 64 GB RAM and 2 processors with 8 cores each. Its Hard Drive Space is about a TB.

Do you think it is improper Indexing? If so How can I figure out which Column to index to make it run faster? I need to keep in mind that there are inserts , updates and deletes on a regular basis.

The query is like this:

SELECT a,b,c,d,e,f,g,h,i,j
FROM TBLA LEFT OUTER JOIN TBLB
    ON TBLA.Objectuid = TBLB.objectuid
WHERE TBLA.a NOT LIKE '11%'
AND TBLA.b <> '2100'
AND TBLA.c <> 'alloc'
AND TBLA.d NOT LIKE 'lm-%'
AND TBLA.e NOT LIKE 'ap-%'
AND TBLA.f >= '2014'

TBLA has 99 columns out of which the view only uses 15. TBLA has a PK which is clustered is a GUID. even when i remove the index it takes the same amount of time.

Have you checked your actual rows vs estimated rows in the actual execution plan? Also, have you checked the reads from your query compared to the reads from a table made up of just the fields you are selecting on?
Without even seeing any of that I would say you definitely need a non-clustered index. My guess is either one or both of the following is occurring in your query in regards to the questions above:
1) The actual rows are way off from the estimated rows (because the sql optimizer has no good estimates on what is actually in your tables)
2) The IO from your query listed above is at least a magnitude higher than if you just inserted those same 10 fields into another table and ran the same query (if those 10 fields are no larger than your average across all those 99 fields)
Even if you don't use those other 89 fields (since your query looks to only select 10 fields), SQL can only read what is on the page -- all 99 fields. Think of it like this: You have a word doc all your data printed out on paper. All your records are striped in one long string; at the end of the each record (99 fields), you have some delimiter. Then it starts over again with the next record; again all in one long string printed across all these physical piece of paper. Now if you are wanting to read just 10 of those fields, you still have to look at that whole piece of paper and pull out just those 10 fields. This is exactly how SQL Server works. If those 10 fields make up 10% of the actual data length, you are wasting 90% of the paper in his massive book you have printed out. If you create a non-clustered index on just these 10 fields you will save a lot of reading (because now these sheets of paper you have printed out will be completely crammed full of good data and the overall size will be 10% the original book. You will of course want to index on something that limits the most (based on the criteria of your query) and then just throw the other fields into an includes. Also, if those 7 rows you mention in your other query are static values, I would recommend to even go further and do a filtered index on those 7 values OR a filtered index on one of your where criteria. That will shrink your index down even further.


how long can CU and SP be different across a SQL Server Always on Cluster nodes Does Microsoft support running SQL Server Always On clusters for a short period of time on different CU and/or SP levels. For a week or a couple of days?

Does Microsoft support running SQL Always On clusters for a short period of time on different CU and/or SP levels. for a week or a couple of days?

This is not an official statement but I got this from my MS friend.

It would be supported subject to condition both CU and SP are supported. But the risk and burden lies on you. I have worked with such environment for very short period, to be precise for 4 days and after that I made sure all the replicas are on same SP/CU level. If you really need it, I would suggest to try to limit this to couple of days to be on the safer side.

The correct approach would be to first test the SP/CU on UAT and then if you are happy proceed on production. But sometimes due to constraints this may not be possible. If replicas are on different SP/CU failover may cause primary replica to be on node which is not at latest SP/CU and you may see issues which was fixed in recently released SP/CU so this is very risky. The solution here is to keep such environment as short as possible.


Inhibit Sql Server Log Shipping Alerts during certain times There are batch jobs that occur in the middle of the night, which cause log shipping backups to be delayed such that they exceed the alert threshold, and alerts are then emailed.

How can I disable these alerts during this period only?

I don't want to reduce the alerting threshold for the entire day.

Thanks.

One approach is to use sp_help_log_shipping_alert_job to find the log shipping alert job ID and then use sp_update_job to suspend the job during maintenance. Enable it back when maintenance is done. I don't know whether the job would not immediately send an alert upon being enabled back though.


Best way to implement Hierarchical data in SQL Server with multiple parents How can I implement Hierarchical data where a child has two or more parents?

Something like this

Parent ID       Child ID
1               2
1               3
3,2             4
4,1             5

Parent ID       Child ID
1               2
1               3
2               4
3               4
1               5
4               5


In MySQL 8, can we make group replication db as readonly? I have configured group_replication on 4 db. Out of which I am trying to make 2 db as slave(only read). Even though I make read_only, user can write data in it.
Is possible to make 2 as master and 2 as slave?

The simple answer to this would be ... MAYBE.
Single Primary mode has super_read_only on every node that is not Primary.
When it comes to Multi-Primary mode, one possible workaround would be to add nodes to the cluster that have a higher version of MySQL 8 than the Primary. Such a node should go into read-only mode automatically. Please note the paragraph of the MySQL 8 Multi-Primary Mode Docs under the heading Version Compatibility:

For optimal compatibility and performance, all members of a group should run the same version of MySQL Server and therefore of Group Replication. In multi-primary mode, this is more significant because all members would normally join the group in read-write mode. If a group includes members running more than one MySQL Server version, there is a potential for some members to be incompatible with others, because they support functions others do not, or lack functions others have. To guard against this, when a new member joins (including a former member that has been upgraded and restarted), the member carries out compatibility checks against the rest of the group.
One result of these compatibility checks is particularly important in multi-primary mode. If a joining member is running a higher MySQL Server version than the lowest version that the existing group members are running, it joins the group but remains in read-only mode. (In a group that is running in single-primary mode, newly added members default to being read-only in any case.) Members running MySQL 8.0.17 or higher take into account the patch version of the release when checking their compatibility. Members running MySQL 8.0.16 or lower, or MySQL 5.7, only take into account the major version.
In a group running in multi-primary mode with members that use different MySQL Server versions, Group Replication automatically manages the read-write and read-only status of members running MySQL 8.0.17 or higher. If a member leaves the group, the members running the version that is now the lowest are automatically set to read-write mode. When you change a group that was running in single-primary mode to run in multi-primary mode, using the group_replication_switch_to_multi_primary_mode() UDF, Group Replication automatically sets members to the correct mode. Members are automatically placed in read-only mode if they are running a higher MySQL server version than the lowest version present in the group, and members running the lowest version are placed in read-write mode.
For full information on version compatibility in a group and how this influences the behavior of a group during an upgrade process, see Section 18.7.1, “Combining Different Member Versions in a Group” .

You can also try using super_read_only on the desired node you want as read only, just make sure Group Replication doesn't start acting funny.


How to move all non-unique and non-clustered indexes to a different filegroup? I want to move all indexes to a separate hard drive.  To that effect, I created a new filegroup, then added a new file to it (e.g. alter database foo add file(...) to FileGroup).

Now I want to move all the indexes to this new filegroup.  Turns out, I can't use ALTER INDEX  to move an index between filegroups.  And I can't move PK or Unique indexes either, lest I want to move the table itself (which I don't), so those are out.  So, at least, I'd like to move the rest of the indexes.

I am struggling to find a method to move all eligible indexes from one filegroup to another.  Is there a reasonable automated way to do this?

Found the answer.  See comments on how to make it work for SQL Server 2005.


Update one instance of Sql Server 2008 R2 Standard to SP2? We have one server with multiple SQL Server 2008 R2 instances installed.  To support an application we need to upgrade one instance to SP2.  Can we only apply the service pack to the specific instance on the server, or would it have to be applied to all of the instances on the server?

Yes, you can choose which instance(s) you wish to apply the service pack. They'll all be checked by default; as will the client tools / shared features (there is only one "instance" of those):

You can re-run the installer later to update any that you didn't update this time; the ones that are already at SP2 will not be selectable.

On this VM, I have 2008 R2 RTM (no patches whatsoever) and 2008 R2 with Service Pack 2 and CU9, installed and running side by side with no issues:

Now, why you want to have a mix of patched and unpatched instances on a single server might be a different question...


Restrict user to only take COPY_ONLY backups Is there a way to force users to only take a full backup with COPY_ONLY so they don't interfere with differential backups?

Our developers need to take backups every now and then but they don't always remember to use the COPY_ONLY option and then they move the backup away so it causes a few issues.

No there is no option or restriction which we can enforce so that a user can take copy_only backup when ever he tries to take any backup but what you can do is create a procedure which backups database with copy only option and grant execute on the procedure to various users and then remove backup database privilege from the user.

BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles. So move accordingly. Its really not a good practice to allow general users to backup database as per there mood but I guess requirements vary as per different firms.For more details read Security section in This Link

  Is there a way to force users to only take a full backup with COPY_ONLY so they don't interfere with diff backups

Let me tell you there is no concept of copy only differential backup. It only applies to full backup and transaction log backup.

The other option is through social engineering by educating the developers about possible issues which can occur with normal full and log backup may be about if they take log backup it might break LSN chain and Log shipping would fail and they would be responsible. There is also a simple option to ask developers to use inbuilt script kept at some shared location(hope you have one in your firm) and make sure you have copy only clause in that scripts. I guess everybody would find it helpful to use already written script instead of writing by own or taking by GUI.


Efficiently store time based data As a reporting solution, I have a large table with ~50M records called "reports". Currently, I am using PostgreSQL with partitioning table feature day by day, (table name reports_20170101 means that all the records of 2017-01-01 stores in there).

An example query (runs 41 seconds)

SELECT to_char(date_trunc('week', rpt_datetime), 'YYYY-WW') date_week,
SUM(rpt_revenue) revenue FROM reports
WHERE rpt_datetime < ?
GROUP BY date_week

EXPLAIN, ANALYZE result:

https://gist.github.com/onesvat/be234fbcb6c4d375f9d1dd4151d69391

If the interval is small, there is no problem but when I use larger intervals, it becomes too slow (20 or more seconds).

The system has 128GB Ram, 16 threads and 4 SSD disk with raid0.

The system never deletes or updates yesterday and backward tables so there is no need the re-query older datas. Is there any database or extension that can handle these type of queries in reasonable time?

There is indexes on most columns that includes date and something, in fact, there is additional indexes like:

btree (date_trunc('week'::text, timezone('Europe/Istanbul'::text, rpt_datetime)))

also constraint :

"reports_20170101_rpt_datetime_check" CHECK (rpt_datetime >= '2017-01-01 00:00:00+00'::timestamp with time zone AND rpt_datetime < '2017-01-02 00:00:00+00'::timestamp with time zone)

You might look into using materialized views, if the partitions become effectively read-only on a daily basis. You can pre-calculate the results of these queries for the older data in the materialized views.


Grouping Sessions using a DATETIME stamp which can Span across a DAY I am working with data which has a session id value which gets recycled over time (asp session ids from IIS to be exact).

I am trying to give them a sequence so that each instance of an ASP_SESSION_ID don't get grouped together.

For example, the data would look like this. Same session id, used in Aug 2016, then again in March 2017.

DTTM                        SESSION_ID
2016-08-29 14:24:28.450     297692378
2017-04-13 23:54:53.760     297692378
2017-04-13 23:59:53.477     297692378
2017-04-14 00:04:52.897     297692378
2017-04-14 00:04:53.790     297692378

At first I thought to just group on the date (at the DAY level), but for the example above, notice how the 2nd instance of the session id spans across midnight. That would cause a 3rd group, when really it's the same session.

So if I could rank them properly, it would be:

DTTM                        SESSION_ID      RANK
2016-08-29 14:24:28.450     297692378       1
2017-04-13 23:54:53.760     297692378       2
2017-04-13 23:59:53.477     297692378       2
2017-04-14 00:04:52.897     297692378       2
2017-04-14 00:04:53.790     297692378       2

Here, ASP_SESSION_ID should be considered a new instance of a session when > 20 minutes passed since the last request.

So, how can I group by or rank the same ASP_SESSION_ID differently as they get re-used over time? e.g. if the next request by that ASP_SESSION_ID is > 20 minutes from the last, group it/rank it differently?

I am just not sure how to attack the problem.

Here are some statements to generate the data above:

CREATE TABLE #TEST
(
DTTM DATETIME,
SESSION_ID INT
)

INSERT INTO #TEST (DTTM, SESSION_ID)
select '2016-08-29 14:24:28.450', 297692378 union
select '2017-04-13 23:54:53.760', 297692378 union
select '2017-04-13 23:59:53.477', 297692378 union
select '2017-04-14 00:04:52.897', 297692378 union
select '2017-04-14 00:04:53.790', 297692378

CREATE TABLE TEST
(
DTTM DATETIME,
SESSION_ID INT
)

INSERT INTO TEST (DTTM, SESSION_ID)
select '2016-08-29 14:24:28.450', 297692378 union
select '2017-04-13 23:54:53.760', 297692378 union
select '2017-04-13 23:59:53.477', 297692378 union
select '2017-04-14 00:04:52.897', 297692378 union
select '2017-04-14 00:04:53.790', 297692378 union
select '2017-04-14 00:44:53.790', 297692378
GO

First I've added a new record just to check it runs for a more than 20 min.
select '2017-04-14 00:44:53.790', 297692378

Then I've added a new column called RANK to store the final result.

ALTER TABLE TEST ADD [RANK] int;

GO

I've used LAG() window function to calculate DATEDIFF between current and next row.

SELECT DTTM, SESSION_ID,
       DATEDIFF(minute, COALESCE(LAG(DTTM) OVER (ORDER BY DTTM, SESSION_ID), DTTM), DTTM) DIF_MIN
FROM   TEST
GO

DTTM                | SESSION_ID | DIF_MIN
:------------------ | ---------: | ------:
29/08/2016 14:24:28 |  297692378 |       0
13/04/2017 23:54:53 |  297692378 |  327450
13/04/2017 23:59:53 |  297692378 |       5
14/04/2017 00:04:52 |  297692378 |       5
14/04/2017 00:04:53 |  297692378 |       0
14/04/2017 00:44:53 |  297692378 |      40

Then I've used a CURSOR just to calculate RANK field. Basically it acumulates minutes between records, until it hits 20 or more minutes.

DECLARE @dttm datetime,
        @session_id int,
        @diff_min int,
        @acm_diff int,
        @rank int,
        @last_dttm datetime;

SET @diff_min = 0;
SET @acm_diff = 0;
SET @rank = 0;
SET @last_dttm = NULL;

DECLARE curMin CURSOR FAST_FORWARD  FOR
SELECT DTTM, SESSION_ID,
       DATEDIFF(minute, COALESCE(LAG(DTTM) OVER (ORDER BY DTTM, SESSION_ID), DTTM), DTTM) DIF_MIN
FROM   TEST

OPEN curMin;
FETCH NEXT FROM curMin INTO @dttm, @session_id, @diff_min;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @last_dttm IS NULL OR @acm_diff + @diff_min > 20
    BEGIN
        SET @rank = @rank + 1;
        SET @acm_diff = 0;
    END
    ELSE
    BEGIN
        SET @acm_diff = @acm_diff + @diff_min;
    END

    UPDATE TEST
    SET    [RANK] = @rank
    WHERE  DTTM = @dttm
    AND    SESSION_ID = @session_id;

    SET @last_dttm = @dttm;

    FETCH NEXT FROM curMin INTO @dttm, @session_id, @diff_min;
END

CLOSE curMin;

SELECT   DTTM, SESSION_ID,
         DATEDIFF(minute, DTTM, COALESCE(LEAD(DTTM) OVER (ORDER BY DTTM, SESSION_ID), DTTM)) DIF_MIN,
         [RANK]
FROM     TEST
ORDER BY DTTM, SESSION_ID;
GO

DTTM                | SESSION_ID | DIF_MIN | RANK
:------------------ | ---------: | ------: | ---:
29/08/2016 14:24:28 |  297692378 |  327450 |    1
13/04/2017 23:54:53 |  297692378 |       5 |    2
13/04/2017 23:59:53 |  297692378 |       5 |    2
14/04/2017 00:04:52 |  297692378 |       0 |    2
14/04/2017 00:04:53 |  297692378 |      40 |    2
14/04/2017 00:44:53 |  297692378 |       0 |    3

dbfiddle here


Query Postgres JSON array for values in text How would I query a JSON array column such as this for text within each of the array values and only return those rows that have them?

["Mr Smith","Ms Wellington","Mr Anderson"]

I've tried these but neither return results when I know they exist:

select * from people where names::jsonb ? 'Mr';
select * from people where names::jsonb @> 'Mr';

On this case you can cast the jsonb as text and use regular LIKE operator.

create table test(data jsonb);
insert into test values ('["Mr Smith","Ms Wellington","Mr Anderson"]'::jsonb);
insert into test values ('["Md Smith","Ms Wellington","Md Anderson"]'::jsonb);
insert into test values ('["Mg Smith","Ms Wellington","Mr Anderson"]'::jsonb);

select *
from   test
where data::text like'%Mr%';

| data                                         |
| :------------------------------------------- |
| ["Mr Smith", "Ms Wellington", "Mr Anderson"] |
| ["Mg Smith", "Ms Wellington", "Mr Anderson"] |

dbfiddle here


PostgreSQL data directory/files portability I have lost my PostgreSQL 9.1 installation on my Ubuntu machine by update to Ubuntu 14.10. Now I have no (reasonably easy) way to export the data from my PostgreSQL 9.1 database. Is it possible to take the data directory from the Ubuntu machine, put it to a Windows machine and have Windows version of PostgreSQL 9.1 use it so that I can dump it?

The best option is probably to install PostgreSQL 9.1 from http://apt.postgresql.org/ if it's available for your distro/version.

If not, you can install a temporary copy of your old Ubuntu version in a VM for recovery purposes - use virtualbox, kvm, etc. Vagrant can make life easier, just grab a prebuilt Ubuntu box from https://vagrantcloud.com/search?utf8=%E2%9C%93&sort=&provider=&q=ubuntu , bring it up, restore your DB, dump it, and shut the box down again.

A PostgreSQL data directory from Linux is not compatible with PostgreSQL on Windows. So you cannot use a 9.1 install on Windows to restore your data from Linux.

Once you've recovered, set up regular backups.


Version of Witness Server for mirroring SQL 2012 Can I use SQL 2008 R2 as a Witness Server for 2 instances of SQL 2012 Servers being mirrored?

No witness server has to be SQL 2012, you can use SQL Express 2012 though

All server participating in database mirroring should be running same version of SQL server.

Taken from Microsoft SQL Server BOL

  Except during an upgrade from an earlier version of SQL Server, the
  server instances in a mirroring session must all be running the same
  version of SQL Server. For example, a SQL Server 2005 witness is
  supported when you are upgrading from a SQL Server 2005 mirroring
  configuration but cannot be added to an existing or new SQL Server
  2008 or later mirroring configuration.

Database Mirroring will be deprecated in future release of SQL Server if possible use AlwasyOn Availability Group


postgres - SUM Time of this Interval and Group by 30 and 30 minutes I need to SUM Time of this Interval and Group by 30 and 30 minutes.

CREATE TABLE foo AS
SELECT date_start::timestamp,
  date_end::timestamp,
  agent
FROM ( VALUES
  ('2017-01-06 06:52','2017-01-06 06:52',164),
  ('2017-01-06 06:56','2017-01-06 07:25',164),
  ('2017-01-06 06:56','2017-01-06 07:05',169),
  ('2017-01-06 06:56','2017-01-06 10:29',142),
  ('2017-01-06 06:57','2017-01-06 07:23',116)
) AS t(date_start,date_end,agent);

like this example: need to group by 30 and 30 minutes and sum real dates.

    period          |   sum_times
---------------------------------
06:30:00 - 07:00:00 |   00:12:36
07:00:00 - 07:30:00 |   01:24:15
07:30:00 - 08:00:00 |   0:30:00
08:00:00 - 08:30:00 |   0:30:00
08:30:00 - 09:00:00 |   0:30:00
09:00:00 - 09:30:00 |   0:30:00
09:30:00 - 10:00:00 |   0:30:00
10:00:00 - 10:30:00 |   0:29:40

Sql code:

WITH min_max_time AS (
  SELECT MIN(datahora_ini::timestamp(0) ), MAX(datahora_fim::timestamp(0) )
  FROM callcenter.agente_login
  WHERE  datahora_ini >= '2017-01-06 00:00:00' AND datahora_ini <= '2017-01-06 23:59:59'
), periods(time) AS (
  SELECT generate_series(min, max, '30 minutes'::interval)
  FROM min_max_time
), w_agentes_logados AS (
SELECT
  CONCAT(to_char((time),'YYYYMMDD'), to_char(to_timestamp(floor((extract('epoch' from time) / 1800 )) * 1800) AT TIME ZONE 'UTC','HH24MI') ) as id_chamadas_receptiva,
  SUM(CASE WHEN datahora_ini::timestamp(0)  <= time AND datahora_fim::timestamp(0)  >= time THEN EXTRACT(EPOCH FROM (datahora_fim - datahora_ini)) else 0 end) as quantidade

FROM callcenter.agente_login, periods
GROUP BY time
)

SELECT * FROM w_agentes_logados

expected result, but it's wrong it's not returning the real result.

time_id           | qty (seconds)
----------------------------------
201701060630          | 9
201701060700          | 103418
201701060730          | 112410
201701060800          | 137369
201701060830          | 279747
201701060900          | 370753
201701060930          | 372496

I've got a ton of questions here,

Are these all the same day?
What should happen if there is no overlap in time?

Let's also focus on this table from the times of 6:30-7:00

     date_start      |      date_end       | overlap
---------------------+---------------------+----------
 2017-01-06 06:52:00 | 2017-01-06 06:52:00 |
 2017-01-06 06:56:00 | 2017-01-06 07:25:00 | 00:04:00
 2017-01-06 06:56:00 | 2017-01-06 07:05:00 | 00:04:00
 2017-01-06 06:56:00 | 2017-01-06 10:29:00 | 00:04:00
 2017-01-06 06:57:00 | 2017-01-06 07:23:00 | 00:03:00

So how do you get this in your ideal result set?

06:30:00 - 07:00:00 |   00:12:36

Please, value our time... When you create sample data, it's important for you to be sure you're not misleading us. I'm sure it's an innocent mistake, but double check next time. Moreover, this should set you down the right path to get this job done..

SELECT
  tsrange,
  sum(
    UPPER(tsrange*tsrange(date_start,date_end)) -
    LOWER(tsrange*tsrange(date_start,date_end))
  )
FROM foo
JOIN (
  SELECT tsrange(ts::timestamp, ts+'30 minutes'::interval) AS tsrange
  FROM generate_series(
    '2017-01-06'::timestamp,
    '2017-01-17'::timestamp,
    '30 minutes'::interval
  ) AS t(ts)
) AS t(tsrange)
  ON tsrange && tsrange(date_start,date_end)
GROUP BY tsrange
ORDER BY tsrange
;

Let's break it down, here we generate the times in 30 minute intervals as a timestamp range..

JOIN (
  SELECT tsrange(ts::timestamp, ts+'30 minutes'::interval) AS tsrange
  FROM generate_series(
    '2017-01-06'::timestamp,
    '2017-01-17'::timestamp,
    '30 minutes'::interval
  ) AS t(ts)
) AS t(tsrange)
  ON tsrange && tsrange(date_start,date_end)

Then we join this set on an overlap test. The only other clever unintuitive thing is this,

sum(
  UPPER(tsrange*tsrange(date_start,date_end)) -
  LOWER(tsrange*tsrange(date_start,date_end))
)

In my opinion tsrange - tsrange should return an interval (or a cast should be provided). One isn't. So we have to do what's done above. This provides us the duration of the overlap that we previously detected with &&. We can sum(interval) and produce the result set you want..

                    tsrange                    |   sum
-----------------------------------------------+----------
 ["2017-01-06 06:30:00","2017-01-06 07:00:00") | 00:15:00
 ["2017-01-06 07:00:00","2017-01-06 07:30:00") | 01:23:00
 ["2017-01-06 07:30:00","2017-01-06 08:00:00") | 00:30:00
 ["2017-01-06 08:00:00","2017-01-06 08:30:00") | 00:30:00
 ["2017-01-06 08:30:00","2017-01-06 09:00:00") | 00:30:00
 ["2017-01-06 09:00:00","2017-01-06 09:30:00") | 00:30:00
 ["2017-01-06 09:30:00","2017-01-06 10:00:00") | 00:30:00
 ["2017-01-06 10:00:00","2017-01-06 10:30:00") | 00:29:00
(8 rows)


Where to start learning to understand SQL Server architecture and internals? I have a basic knowledge of T-SQL and SQL Server components. My goal is to master my skills and learn everything about SQL Server to eventually become DBA in the future. I would like to understand deep SQL Server internals, how exactly everything works, when and why. Could you please suggest me a good place to start? IMHO it's just not possible by doing the programming work.

Just to add to the suggestions on this thread, you might find these interesting
Books:

I found Thomas Larock's DBA Survior very useful. It doesn't go deep into SQL Server internals, but it has right pointers to guide a newbie towards becoming a successful DBA. If you are past that newbie stage, you can skip this.

Rod Colledge's SQL Server 2008 Administration in action was useful for me as a next step from DBA Survior

Troubleshooting SQL Server by Jonathan Kehayias is a great book. It is available as a free download. Also take a look at other books available on SSC. I am currently reading a book on execution plans by Grant Fritchey.

There are some awesome blogs out there:

Read everything under the category "involuntary DBA" on Paul Randal's blog. While I understand that you obviously are not an involuntary DBA, they tend to be simpler while explaining things to newbies.

Buckwoody has a great blog here and also a useful SQL Server reference guide here. He is blogging about Windows Azure stuff thesedays but you can check the archives

Other Blogs in random order

Aaron Bertrand
Grant Fritchey
Brent Ozar
Thomas LaRock
Pinal Dave
Phil Factor

Podcasts:

There are a few SQL Server related podcasts on DotNetRocks
Also here on SQL Down Under

Other great sites - SSC, MSSQL Tips, SQLCAT, LessThanDot.
This list is in no way complete and if I have missed some important books/sites/blogs, thats because I too am still learning and my knowledge is limited. Lets hope the others on this thread add more resources.
Good luck!
UPDATE: I forgot to include SQL Server Performance Survival Guide technet article which has loads of links and pointers.


Is MySQL smart enough to not do a full table scan for LIKE or NOT LIKE when there are other conditions? I know that full wildcard searches (example: LIKE '%word%') cause a full table scan.

Is MySQL smart enough to only scan the results, not the entire table, when I use LIKE or NOT LIKE?

For example, say I have a query that selects 10 rows out of 1000000. The query is something like select * from table where id <= 10 and description NOT LIKE '%word%'. Is MySQL smart enough to only scan those 10 result rows?

(My query is very different but that's the gist of it: I want to use NOT LIKE on a result set from a large table. Assume everything is indexed. Client version "14.14 Distrib 5.7.29, for Linux (x86_64)", server version "5.7.29-0ubuntu0.16.04.1 for Linux on x86_64".)

Given NOT LIKE '%...' cannot use an index and id can, provided a index (include primary key) begins with id, then it will be used.

EXPLAIN [EXTENDED] {query} will show what indexes the query planner plans to use for the query.


Why doesn't InnoDB store the row count? Everyone knows that, in tables that use InnoDB as engine, queries like SELECT COUNT(*) FROM mytable are very inexact and very slow, especially when the table gets bigger and there are constant row insertions/deletions while that query executes.

As I understood it, InnoDB doesn't store the row count in an internal variable, which is the reason for this problem.

My question is: Why is this so ? Would it be so hard to store such information ? It's an important information to know in so many situations. The only difficulty I see if such an internal count would be implemented is when transactions are involved: if the transaction is uncommitted, do you count the rows inserted by it or not ?

PS: I'm not an expert on DBs, I'm just someone who has MySQL as a simple hobby. So if I just asked something stupid, don't be excessively critical :D .

For starter there is no such thing as the 'current count' to store in a variable. A query like SELECT COUNT(*) FROM ... is subject to the current isolation level and all concurrent pending transactions. Depending on the isolation level, the query can see or not see rows inserted or deleted by pending uncommitted transactions. The only way to answer is to count the rows that are visible to the current transaction.

Note that I did not even touch the even more thorny subject of concurrent transactions that start or end during the count. Not to mention rollbacks...


How do I group the data in the following format? Using the following table

OrderDate     OrderID
[dates]       [id]

I want to display in the following format :

quater name   nooforders
q1            10
q2            10
q3            20

I have tried the following query :

select count(order_id)no_of_orders,
(case when month(order_date)<=4 then 'q1' when month(order_date)<=8 then 'q2' when month(order_date)<=12 then 'q3' end)quarter
from bab_orders
group by quarter;

However this query does not show the value of a particular quarter when it has no orders associated with it. How do I display that?

To display the given format, you can use the following queries instead:

  For MySQL, use the following:

  SELECT QuaterName, COUNT(OrderID) FROM
    (
        SELECT QUARTER(OrderDate) QuaterName, OrderID from bab_orders
    ) T1 group by QuaterName;

  for Oracle use the following:

SELECT QuaterName, COUNT(OrderID) FROM
(
    SELECT TO_CHAR(TO_DATE(OrderDate, 'dd.mm.yyyy'), 'Q')
              QuaterName, OrderID from bab_orders
) T1 group by QuaterName;

  to show always the Quarters even if you don't have a data, execute the
  following query:

SELECT QRTR, COUNT(OrderID) TheCount FROM
(
  SELECT QUARTER('2014-01-01')  QRTR
  union
  SELECT QUARTER('2014-04-01')  QRTR
  union
  SELECT QUARTER('2014-08-01')  QRTR
  union
  SELECT QUARTER('2014-12-01')  QRTR
)  QRTR_T
LEFT JOIN
(
   SELECT QUARTER(OrderDate) Quater, OrderID from bab_orders
) ORDERS
on Quater = QRTR
group by QRTR


Oracle OLEDB Connections hang in SQL Server: Can I restart Instance? There is a killed/rolled back query on this instance that was originally attempting to execute a SELECT on a remote Oracle Server via OLEDB. This query was running for over two days before it finally got cancelled.

SPID 69: transaction rollback in progress.
Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

Using cports I have found all open connections to the 1521 port on the remote server. In cports you can close the connection.

After trying to close the connection, the killed/rollback process is still preventing other processes from taking a schema lock on one of the tables.

How can I determine if the database, upon restarting, won't go into RECOVERY PENDING?

Could I kill the processes using Task Manager, without also killing SQL Server?

EDIT

Here is the query anonymized:

   INSERT  INTO sometable
            ( field1 ,
              field2 ,
              field3 ,
              ...
            )
            SELECT  some_fields
            FROM    someothertable f ( NOLOCK )
                    INNER JOIN OPENQUERY([MYORACLE_LINKED_SERVER],

The roll-back will likely take as long or longer than the amount of time the query was running.

Almost certainly, if you restart the instance, whatever database is affected by the ongoing transaction will need to run recovery, which will take as long or longer than the rollback that is currently happening.

Advice? Don't kill SQL Server.

Also, killing the dllhost.exe process won't stop the rollback, since that process is no longer required by SQL Server to complete the rollback.

You can see if activity is happening against the database in question by checking the following:

SELECT [Database] = d.name
    , FileName = mf.name
    , num_of_reads
    , [io_stall_read_ms]
    , AvgStallPerRead = CASE WHEN num_of_reads > 0 THEN io_stall_read_ms / num_of_reads ELSE 0 END
    , num_of_writes
    , [io_stall_write_ms]
    , AvgStallPerWrite = CASE WHEN num_of_writes > 0 THEN io_stall_write_ms / num_of_writes ELSE 0 END
    , [io_stall]
    , sample_ms
FROM sys.dm_io_virtual_file_stats(NULL,NULL) iovfs
    INNER JOIN master.sys.master_files mf WITH (NOLOCK) ON iovfs.database_id = mf.database_id AND iovfs.file_id = mf.file_id
    INNER JOIN master.sys.databases d WITH (NOLOCK) ON iovfs.database_id = d.database_id
WHERE d.name = 'database_name_in_question';

Running this several times in a row should allow you to see num_of_writes increasing.

This won't help you know the full progress of the rollback, but will allow you to see if there is disk activity against the database.

You could check the following query to see activity against the database in question:

SELECT login_time
    , host_name
    , program_name
    , start_time
    , r.status
    , command
    , wait_type
    , last_wait_type
    , wait_time
    , wait_resource
    , percent_complete
    , estimated_completion_time
FROM sys.dm_exec_sessions S
    INNER JOIN sys.dm_exec_requests R ON S.session_id = r.session_id
WHERE r.database_id = DB_ID('database_name_in_question');

Look for the session where the rollback is occurring by looking for "ROLLBACK" (or something similar) in the command column.  Unfortunately, the percent_complete and estimated_completion_time columns won't be useful for looking at rollbacks.

If you can determine the SPID for the rollback activity, you can use the following command to check progress on the rollback (new to me!)

KILL xx WITH STATUSONLY;

Where xx is the SPID.

See http://msdn.microsoft.com/en-us/library/ms173730.aspx for further details about that!

sys.dm_tran_database_transactions has very detailed information about ongoing transactional activity, including roll-backs.


SQL Server Change Tracking I work for a company of about 80 employees and they all seem to need access to our database on a regular basis. I don't have time to write applications to meet all of their needs so I'm just going to install SQL Server Management Studio on each employee's computer so they can help themselves to the data. Does anyone know if there's a way to write a wrapper for SQL Server that would save all database transactions to a text file so I can monitor what changes everyone is making?

I may get dinged for this, I may not, but if you don't have time I've got a fantastic solution for you. Download Visual Studio LightSwith - you can build a database application in less than five minutes and have it deployed in less than ten - I've done it.

Make time, find the right tools, but do not give them the keys to the database.

In fact, LightSwitch is so dead simple, you could teach some other folks there how to use it at some level.


Is CHARSET important for tables with INT only? My server default CHARSET is UTF-8 (set in my.cnf). I create tables like
CREATE TABLE t1
(
ID int(11) unsigned NOT NULL AUTO_INCREMENT,
Title varchar(255),
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=2

but some tables have only int columns. Is it beneficial to use latin charset for them? Or is it better to have all tables with UTF-8 charset for consistency?
CREATE TABLE t2
(
ID int(11) unsigned NOT NULL,
TagID int(11) unsigned NOT NULL,
PRIMARY KEY(ID,TagID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

vs.
CREATE TABLE t2
(
ID int(11) unsigned NOT NULL,
TagID int(11) unsigned NOT NULL,
PRIMARY KEY(ID,TagID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_swedish_ci KEY_BLOCK_SIZE=1

The character set and collation of a table affects the newly created columns in that table. It also affects char/text columns without a specified character set/collation.
So your int only tables don't have any specific difference because of character set, until you add a column that uses it.


Oracle 19c GI Install on OL7 : ASM disks stamped via AsmLib not getting discovered via GridSetup.sh I am trying to install GI 19c on Oracle Linux 7 (required for 19c), and have used AsmLib to try to achieve device persistence (owner,group) for ASM devices/disks. Prior to that I used udev rules for the same and was able to achieve the same. Both times I get the same issue while doing ASM disk discovery as explained below.

Problem is when I try to invoke the gridSetup.sh from node 1, all checks pass including node readiness, ssh equivalence, scan etc, but the installer fails(not able to find the disks) at discovering ASM disks which are already marked by oracleasm(AsmLib) as asm disks and are owned by grid:oinstall.

The installer logs from oui show the below.

WARNING:  [May 29, 2019 12:32:14 PM] SRVCTL not found as there is no Grid Infrastructure home on the box.
INFO:  [May 29, 2019 12:32:14 PM] Executing [/u01/app/19.0.0/grid/bin/kfod.bin, nohdr=true, verbose=true, op=dfltdstr]
INFO:  [May 29, 2019 12:32:14 PM] Starting Output Reader Threads for process /u01/app/19.0.0/grid/bin/kfod.bin
INFO:  [May 29, 2019 12:32:14 PM] The process /u01/app/19.0.0/grid/bin/kfod.bin exited with code 1
INFO:  [May 29, 2019 12:32:14 PM] Waiting for output processor threads to exit.
INFO:  [May 29, 2019 12:32:14 PM] Parsing Error 49802 initializing ADR
INFO:  [May 29, 2019 12:32:14 PM] Parsing ERROR!!! could not initialize the diag context

Already tried setting ORACLE_BASE env variable and added to path, so adrci is executable. Also verified owner and permissions, (oracle:oinstall)

Also checked on permissions for /u01/app/19.0.0/grid/bin/kfod.bin and they are executable by grid user.(grid:oinstall)

Also tried various different asm diskstrings like ORCL:VOL*, ORCL*, VOL*, /dev/oracleasm/disks/* etc, but none of them worked.

FYI: This is a fresh install of 19c GI onto a 2 node RAC cluster, (2 freshly created virtualbox machines with shared disks) with no prior GI or RDBMS installations on.

Any help/assistance is greatly appreciated.

Thanks

Execute the below command to change owner (from root to oracle) on the given directory:

chown -R oracle:oinstall  /u01/app/19.0.0/grid/log

and run the installer again. It worked for me.


Does it make sense to upgrade to a newer PostgreSQL JDBC driver? I would like to know if it could be possible to gain any performance benefits from upgrading the default PostgreSQL JDBC drivers from applications like Confluence or JIRA.

Assume that you cannot modify the application that is using them, the question if there are any visible benefits from switching from the current driver postgresql-8.4-702.jdbc3.jar to something newer?

the backend is using PostgreSQL 9.1 and soon will be upgraded to 9.3
JVM being used is the latest 1.7 from Oracle

Does it make sense or not? If so, which version should I pick?

You should always ensure that the JDBC driver is at least as new as the back-end server.

PostgreSQL's catalogs change between major versions, and other details like the bytea quoting method, string escaping features, etc have also changed over time. PgJDBC contains compatibility code to cope with this by querying the server version. If you don't use a current driver, you may have issues with some features, particularly JDBC metadata access and bytea fields.


Full backup before and after checkdb Recently someone told me I should take a full backup before dbcc checkdb and one after checkdb, even in full recovery.
Can someone explain me why I should do this?

I thought a full backup before checkdb was unnecessary (in full recovery) because I can always restore last full backup + diff backup + t-log backups.
Is this a wrong assumption?

There is no need to take a FULL backup before and after running DBCC CHECKDB. It is recommended that your FULL backup go before the DBCC CHECKDB so that you know exactly which FULL backups don't contain database corruption if DBCC CHECKDB ever fails due to corruption. The FULL backup before the last successful DBCC CHECKDB is one without corruption.

To recover from corruption, you can use the FULL+DIFF+LOG chain or FULL+LOG chain. Pick the FULL backup from before the last successful DBCC CHECKDB. If there was a DIFF before the last successful DBCC CHECKDB, then you can use that too. But a DIFF after the last successful DBCC CHECKDB could have database corruption, depends when the DIFF occurred and when the corruption occurred.

LOG backups do not contain data pages, so they do not have the corrupted data pages in them. Thus, you can recover with no data loss if you have the entire LOG chain and the tail of the log after the non-corrupted FULL or non-corrupted FULL+non-corrupted DIFF.


Select countries and counts of people per country I have 2 tables, one is a list of countries (id, country) and another is the users (where the country column is the id of the country in the countries table).

Is it possible to make a single query that returns both the country name and the count of people who has that country id in their country column?

like

country   users
--------  -----
uk        4
portugal  2
usa       10
...

You have to aggregate using GROUP BY SELECT statement, which is part of the SQL standard (and quite popular to be honest).

Although You haven't stated which database engine You are using, the query below should work in most, if not all products, because it uses very basic syntax:

SELECT b.country, count(a.id) AS user_count
 FROM countries b
 LEFT JOIN users a ON b.id = a.country_id
 GROUP BY b.country;

The query could use an inner join instead of the left outer join. In that case it would exclude countries for which there are no matches in the users table.

If You are thinking about writing more queries, it might be better to try some online SQL course. Code Academy one is rather good. Other noteworthy example is from SQLCourse, but other ones found on the Internet might be OK too. Checking the SELECT statement's online documentation on Your database's webpage might be a good idea as well (as implementations do vary in some details).


Install Access Database Engine on Linux Centos I want to read mdb file from linux centos.  To read that file, we must already install access data engine. Is there any access data engine for linux?

Now I'm using mdbtools to configure ODBC connect to mdb file inside Centos
I'm edit odbc.ini and odbcinst.ini inside etc directory.

odbc.ini:

[mydb]
Description = Microsoft Access Try DB
Driver = MDB
Database = /tmp/mydb.mdb
Servername = localhost
Username =
Password =
port = 5432

odbcinst.ini:

[MDB]
Description = MDB Tools ODBC
Driver64        = /usr/lib64/libmdbodbc.so.0
Setup64         = /usr/lib64/libmdbodbc.so.0
FileUsage =
CPTimeout =
CPReuse =

Then, in terminal, I type:

isql -v

result:

[IM010][unixODBC][Driver Manager]Data source name too long
[ISQL]ERROR: Could not SQLConnect

My question, how to fix this? I'm sorry for my bad English.

I solved it by adding some extra command in terminal, like this:

isql -v mydb
and the result :
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

nb: "mydb" get it from odbc.ini file


Multiple procedures to schedule jobs I have a package named pkg_cd. In it there are 8 procedures:

pkg_cd.PRC_CD_CASEDATAENTRY;
pkg_cd.PRC_CD_DEDUP;
pkg_cd.PRC_CD_CIBIL;
pkg_cd.PRC_CD_TVR;
pkg_cd.PRC_CD_SANCTION;
pkg_cd.PRC_CD_DOWNPAY;
pkg_cd.PRC_CD_DOC_CPA;
pkg_cd.PRC_CD_DOC_QA;
pkg_cd.PRC_CD_PDN_REQ;
pkg_cd.PRC_CD_PDN_AUTHORIZE;

I want to schedule all the 8 procedures to run every minute. How can I do this?

You will need to schedule this using Oracle DBMS scheduler.

In order to run the 8 procedures, there are couple of ways, you can create a small procedure that calls the 8 procedures respectively.

After that, you will create a DBMS scheduler job that will execute the procedure which calls the main procedure.

The script below creates the main procedure to call the other procedures

CREATE OR REPLACE PROCEDURE RUN_8PROCEDURES AS
BEGIN
  pkg_cd.PRC_CD_CASEDATAENTRY;
  pkg_cd.PRC_CD_DEDUP;
  pkg_cd.PRC_CD_CIBIL;
  pkg_cd.PRC_CD_TVR;
  pkg_cd.PRC_CD_SANCTION;
  pkg_cd.PRC_CD_DOWNPAY;
  pkg_cd.PRC_CD_DOC_CPA;
  pkg_cd.PRC_CD_DOC_QA;
  pkg_cd.PRC_CD_PDN_REQ;
  pkg_cd.PRC_CD_PDN_AUTHORIZE;
END;
/

This script creates a job that runs every minute and runs the main procedure.

BEGIN
   DBMS_SCHEDULER.Create_job (
      job_name          => 'MY_JOB_THAT_RUNS_EVERY_MINUTE',
      repeat_interval   => 'FREQ=minutely;interval=1',
      job_type          => 'STORED_PROCEDURE',
      job_action        => 'RUN_8PROCEDURES',
      enabled           => TRUE
   );
END;
/


Workload Groups in SQL Server 2012 I have been looking for an answer about "Workload Groups" in SQL Server?

What exactly it is Workload Groups?

Could you explicit a real world situation when this term is used?

Thanks in advance your your help!

Workload groups are a concept of the resource governor feature in SQL server.  You would define workload groups (such as a prod group and a test group) on a server and then define the amount of resources that can be used by each.

So for instance if you only had one SQL server for test and prod say, you could define the 2 workload groups and limit the CPU/Memory that can be used by each, to limit the impact of the test workload on the prod workload (SQL 2014 can also limit the IOPs used by each group too).

You should read up more on the resource governor here:

Resource Governor


How to copy backup files to remote share in SQL Server Agent job without AD/domain accounts involvement? MS SQL Server 2012... with nightly databases backups to the same/local as SQL Server machine...

I am trying to add another SQL Server Agent Job to copy the  .bak files to remote (non-windows, i.e. Linux) share with non-Windows (non-AD) user/password credentials.
I do not have any access to configuring or changing that access which is under control of other, quite remote people.

For this (copying) I created local user with the same user name and password, gave it permissions to the (source or local) backup-folders upon which all perfecly works from command line (Win + К or cmd) if to enter the command manually:

RUNAS /user:UserName /savecred "robocopy d:\SQLBACKUP  \\10.195.xx.yyy\backup /S /purge /MAXAGE:7 /MT:1 /Z"

but fails to run as SQL Server Agent job (type of step is "Operating System(CmdExec)". SQL Service Agent (with standard configuration of running under [NT Service/SQLServiceAgent] account, the job is owned by SA SQL Server superuser).

Can anybody explain me why it is failing and how to correctly make it running (taking into account that I do not have access to domain users configuration)?

To answer the specific question of why it is failing is that the service account on your server does not have the correct permissions to access the share.

I have an environment where I have 2 servers not on a domain.  Using SQL Server Agent proxies is the way I solved this.

I have a special account I use for the 2 machines (Your accounts with the same name and password) which I had to add to the instance level credentials (under security in SSMS Object explorer) (see MSDN article "Create a Credential" )  and add an agent proxy under the Operating System (CmdExec) .

Once those are created you can select the account to run as (the name you gave your proxy) and it will work.  However this is on 2008 and I have not played with the 2012 agent that much.
Should still work the same though.

You would then use your robocopy line "robocopy d:\SQLBACKUP  \10.195.xx.yyy\backup /S /purge /MAXAGE:7 /MT:1 /Z" as your command (I use xcopy but it works the same).


PostgreSQL on ephemeral storage Is there any safe way to use ephemeral storage (deleted on halt) for postgresql cluster?

Use it for system swap space.   Swapping doesn't hurt performance, running out of memory hurts performance. swapping is just a symptom.

You could also try putting unlogged temporary tables there but I'm not sure what would happen when they disappear.