Broken mysql replication on slave server (error 1236) My question is: how to fix broken mysql replication on slave server (error 1236)?

server 1 (192.168.1.105)
server 2 (192.168.1.106)

I ran these steps to re-sync:

At the master - server 1 (192.168.1.105):

mysql -u root -p

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
Note > File: mysql-bin.000001 and Position: 107
mysqldump -uroot -p --events --ignore-table=mysql.events --all-databases > /var/backups/mysqldump.sql
UNLOCK TABLES;
Copy mysql dump file to server 2
(scp -p /var/backups/mysqldump.sql root@192.168.1.106:/tmp)

At the slave server 1 (192.168.1.106):

STOP SLAVE;
mysql -uroot -p < /tmp/mysqldump.sql
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.1.105', MASTER_USER='slaveuser', MASTER_PASSWORD='mypassword';
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
START SLAVE;

I still got this error on server 1

  Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

I believe the log file and position need to be specified with the host.

Try:

CHANGE MASTER TO MASTER_HOST='192.168.1.105', MASTER_USER='slaveuser', MASTER_PASSWORD='mypassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;


How to sync a database with an external full text engine? How do you keep your external full-text engine (Lucene, Solr, etc) in sync with your database data? For example, keeping track of inserts, deletes, updates.

My current solution involves a big union all query on modified records, that is polled by Lucene every few minutes.

Any better solutions?

Thanks!

Depends on your database engine.  For example, in Microsoft SQL Server, there are several database engine features that can track which rows have been changed, and then you can grab just those rows in your periodic query.

Another technique I've seen is to add an UpdatedDateTime field on the tables you want to search.  Use a default value of the current date/time, and add an update trigger so that whenever the record is updated, the UpdatedDateTime is reset to the current date/time.  Keep in mind that you'll probably want to index that field since you'll be querying it frequently.  Then, in your app, just poll for all records where UpdatedDateTime > the last time you updated.

If you take the latter approach, you'll probably want to do full repopulations periodically to catch any goofups where the polling app failed for a while.


mysqlcheck: unknown variable 'max_allowed_packet=100M' I get the following error when I try to use mysqlcheck, mysqlrepair or mysqlanalyze:

mysqlcheck: unknown variable 'max_allowed_packet=100M'

I am trying to find whether there are any corrupt tables or indexes.

Background:

MySQL  Ver 14.14 Distrib 5.5.24, for Linux (x86_64) using readline 5.1
I used the command mysqlcheck  -u xxxx -h xxx.xxx.xx.xx -d xxxx -p xxxxx

my.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
default-character-set = utf8
max_allowed_packet = 100M

Under [client]

According to this bug report (resolved as "not a bug"), you shouldn't put settings for mysqld in the [client] section of your .ini file.

Put the max_allowed_packet option in the [mysqld] section instead.

See Using Option Files for more details.


Master/Master Master/Slave Ok So here is the scenario.

Web based application running on mysql database A.  All users able to add/edit/delete.

The aspplication runs well.  But now the Company face a new problem which is accessing the web app where no internet is readily available.

As a result they need an offline version which runs on their tablet.

Im thinking that I could simply set up WAMP on their tablet and have the app run there, however clearly I have to sycronise the data at some point.

So my questions are this.

Is this a master/master scenario?

Can someone explain some of the major pitfalls/uplifts I may get from doing this?

Is there a more practical approach that Im missing?

Many Thanks in advance

Here is my recommended approach:

On the tablet, split into two databases. One is the master copy, the other is the local changes.
The tablet considers the master copy to be read-only
At sync time, e.g. when the network is available, you simply ditch the master and reload it from the "real" master that is running on your website
But before doing this, you load all the rows from the local changes, into your real master. After you have done this, you delete all the local changes from the tablet.
You will need to ensure no clashes of primary keys for new data, and you will need to build an app so that users can reconcile conflicting changes to existing data
If you consider this a master-master or master-slave situation and try to make it work with the traditional MySQL replication tools, you are opening the door to a world of pain
However if you are feeling ambitious enough to re-architect everything, you could so something very elegant with messaging/queuing middleware - this is probably how you would have done it if you are starting from scratch, my suggestions are around retro-fitting this onto what you already have.


"Entity Attribute code uniqueness" error in PowerDesigner I am using PowerDesigner to do Conceptual Data modeling. If I use the same attribute name in more than one entities, I will get above error when I run "check model" or try to generate Logical Data model.

Is that constraint PowerDesigner only or it belongs to the database design standard. I cannot remember I have this issue before without the PowerDesigner. Why we cannot use the same name in different entities and tables in the next steps?

The experts don't always agree on the exact meaning of the terms used in conceptual data modeling, or even necessarily on what conceptual data modeling really is.

But Powerdesigner's restriction in this case is not unique to this product.  Many experts assert that an attribute can only be an attribute of one entity.  So if an attribute, say "color" could be an attribute of hair, or of a car, it's necessary to qualify them as "hair color" and "car color" In order to identify each attribute uniquely.

Notice that there is no such restriction in the relational data model.  An attribute could be a component of more than one relation with no problem at all.

In this sense, conceptual data modeling isn't just "relational lite" at least as far as this school of thought is concerned.  It's a whole different way of looking at the world.

There are other schools of thought, with their own experts, where conceptual data modeling is, in effect, "relational lite".  Powerdesigner must seem jarring to those who follow that school of thought.


Is using timestamp as primary key good idea - mysql/mariadb? We just argued that storing timestamp in sqlserver in microsoft does guarantee uniqueness, but im a little bit doubful about mysql/mariadb, because we are using parallel execution when inserting to the mysql/mariadb database.

So, Is using timestamp as primary key good idea - mysql/mariadb?

PS: There is always an PK and status but its important if we are using timestamp as the where clause in getting the actual remaining records to be archived/removed (so that it is efficient in querying the records).So its important its unique

IMO it's bad because of several reasons. First, blindly assigning a generated p.k. for every relation is bad practice. At least try to identify what candidate keys there are in each relation. A surregate p.k. may be the smartest choice in some situations, but it is definitely not so in all possible situations. Good properties for candidate keys are:

Unique
Irreducible
Non-complex
Stable
Familiar

Often there is a conflict between these criteria and you have to sacrifice one for another.

Second, if the best choice is a surrogate identifier I'll say that timestamp is a poor choice. Especially in MySQL/MariaDB before (MySQL) 5.6.4 where the resolution is seconds. Using a timestamp as a p.k in these DBMS means that you cannot insert more than one row / second (as long as the timestamp should reflect the time of insert).


Can an active session in oracle be dead? I have a time consuming query which populates a materialized view.  It has been running for 18 hours - which was not expected.

SELECT STATUS
FROM V$SESSION
WHERE SID=194;

The above query shows the session as 'ACTIVE'.  I was wondering if that is sufficient proof that the query is still running and whether there is any other way to double check that the session is not dead.

UPDATE

The session was not dead; but there was a constant wait:

 SQL*Net more data from dblink

I used sql trace as suggested by Phil to reach this conclusion.

You should query
V$SESSION_WAIT
to see what your statement is doing. A short description of the wait events can be found in "Part IV Appendixes", "C Oracle Wait Events" of the "Database Reference"


Result occurs often with fast random select query I have this query to display 4 random photos on a website (I have it from here (when I asked for something faster than ORDER BY RAND())):

(SELECT   `p1`.`id`, `p1`.`url`, `p1`.`hits`, `p1`.`created_at`,
    `users`.`username`,
    `users`.`displayname`
FROM     pics p1
JOIN
        (SELECT ceil(rand() *
                (SELECT max(id)
                FROM   pics)) AS id
        ) AS p2
JOIN     users
ON       users.id = p1.user_id
WHERE    p1.id >= p2.id
ORDER BY p1.id ASC limit 1
)
UNION ALL
(
SELECT   `p1`.`id`, `p1`.`url`, `p1`.`hits`, `p1`.`created_at`,
        `users`.`username`,
        `users`.`displayname`
FROM     pics p1
JOIN
        (SELECT ceil(rand() *
                (SELECT max(id)
                FROM   pics)) AS id
        ) AS p2
JOIN     users
ON       users.id = p1.user_id
WHERE    p1.id >= p2.id
ORDER BY p1.id ASC limit 1
)
UNION ALL
(
SELECT   `p1`.`id`, `p1`.`url`, `p1`.`hits`, `p1`.`created_at`,
        `users`.`username`,
        `users`.`displayname`
FROM     pics p1
JOIN
        (SELECT ceil(rand() *
                (SELECT max(id)
                FROM   pics)) AS id
        ) AS p2
JOIN     users
ON       users.id = p1.user_id
WHERE    p1.id >= p2.id
ORDER BY p1.id ASC limit 1
)
UNION ALL
(
SELECT   `p1`.`id`, `p1`.`url`, `p1`.`hits`, `p1`.`created_at`,
        `users`.`username`,
        `users`.`displayname`
FROM     pics p1
JOIN
        (SELECT ceil(rand() *
                (SELECT max(id)
                FROM   pics)) AS id
        ) AS p2
JOIN     users
ON       users.id = p1.user_id
WHERE    p1.id >= p2.id
ORDER BY p1.id ASC limit 1
)

It is fast, but it seems, that one particular photo is displayed too often, about every 10th refresh of the page, or so. It has not id 1 (it has 1131), but is the 1st photo of a certain user.

Could it be, that somehow gaps in the pics table could be the reason? I have no idea what else could be the reason.

edit:

SELECT id FROM pics WHERE id < 1131 ORDER BY id DESC LIMIT 1; outputs this:

+-----+
| id  |
+-----+
| 923 |
+-----+

This has 8 'fast' ways to pick a random row from a table.

AI with gaps may be the best choice.

Or the 50/10 trick.

Add a FLOAT column may work well for you.

etc.


Why aren't primary key / foreign key matches used for joins? As far as I could find out many DBMSs (e.g. mysql, postgres, mssql) use fk and pk combinations only to constrain changes to data, but they are rarely natively used to automatically select columns to join (like natural join does with names). Why is that? If you've already defined a relationship between 2 tables with a pk/fk, why can't the database figure out that if I join those tables I want to join them on the pk/fk columns?

EDIT: to clarify this a bit:

suppose I have a table1 and a table2. table1 one has a foreign key on column a, which references to the primary key on table2, the column b. Now if I join these tables, I'll have to do something like this:

SELECT * FROM table1
JOIN table2 ON table1.a = table2.b

However, I already defined using my keys that table1.a references to table2.b, so it seems to me that it shouldn't be to hard to make a DBMS system automatically use table1.a and table2.b as the join columns, such that one can simply use:

SELECT * FROM table1
AUTO JOIN table2

However, many DBMS don't seem to implement something like this.

In many cases, there are more than one way to join two tables; See the other answers for lots of examples. Of course, one could say that it would be an error to use the 'automatic join' in those cases. Then only a handfull of simple cases where it can be used would be left.

However, there is a severe drawback! Queries that are correct today, might become an error tomorrow just by adding a second FK to the same table!

Let me say that again: by adding columns, queries that do not use those columns could turn from 'correct' into 'error'!

That is such a maintenance nightmare, that any sane style guide would prohibit to use this feature. Most already prohibit select * for the same reason!

All this would be acceptable, if performance would be enhanced. However, that's not the case.

Summarizing, this feature could be used in only a limited set of simple cases, does not increase performance, and most style guides would prohibit its usage anyway.

Therefor it is not supprising that most database vendors choose to spend their time on more important things.


Improve update performance in PostgreSQL data warehouse (RDS) I am using a Postgres data warehouse hosted on Amazon RDS. When trying to update one column of a fact table (25 million rows) from another table in the same database, the query takes several days to run. Why is this happening and how can I improve this performance? I know that PG is designed more for OLTP than OLAP, but select query performance is usually pretty decent on this table.

The query in question looks like this:

UPDATE a
SET a.value = b.value
FROM b
WHERE a.id = b.id

b is a temp table in a different schema but same database that has the same number of rows as a. Both tables have primary keys on id. There is no index or constraints on the value column. There are views that depend on table a but no foreign keys

I am using PG 9.5 on RDS. General purpose (SSD) with 256 GB of storage, so after exhausting our initial burst IOPS, I should get a little under 800 IOPS.

Is the IOPS throttling really the issue here? While watching the query run I see ~ 400 IOPS of write performance, and similar read performance. 25,000,000 rows / 400 IOPS = 17 hours, but this query took much longer than 24 hours to run ( cancelled after ~ 30 hours to try and make tweaks). There was some other periodic update traffic on the same table, but I halted this at around the 20 hour mark when I saw how long this query was taking.

I wondering if my general update approach is wrong, or if there is general advice for operating a data warehouse (OLAP workload) using postgres. Could I get better performance by ditching RDS and running an PG on EC2?

UPDATE:
Inspired by responses and comment, I ran a test on 45k rows (by limiting the pk below a certain range)

You can see the results of explain analyze here. The vast majority of the time is spent writing the actual updates to the table. Right now I am still leaning towards write IOPS being a limiting factor, but I will dig into possible replication issues as mentioned by joanolo.

This image shows the RDS instance monitoring page. The most recent spike is the query in question.

Have you considered the possibility that WAL (Write-Ahead Logging) is slowing down your update? Also see this more detail explanation.

This is a common problem for UPDATEs, INSERTs and ALTER TABLE operations on very large tables. According to my understanding, for any row affected by an update--even if that update is only on a single column--Postgres archives and replaces the entire row and updates all indexes on all indexes columns. While Postgres's implementation of WAL is extremely effective for maintaining data integrity in a transactional setting, it can seriously degrade performance for operations on large tables--particularly for data warehouses, where bulk updates involving many records are common.

I would need to know more about the table being updated to know if WAL is the culprit. Is a large proportion of records (say 30% or more) affected by the update? You stated that the value column is not indexed, but are there indexes on other columns? If the answer to both questions is yes, then I would strongly suspect WAL.

An effective solution to this problem is to create a new, unindexed table using the CREATE TABLE AS method described here. You will need to rebuild indexes, keys and constraints on the new table, but this is still much faster than updating-in-place. See also the related answer here.

The downside to the CREATE TABLE AS approach is that a simple UPDATE query becomes a monster multi-statement transaction. The latter code is not only cumbersome but also fragile: the table schema must be repeated for every update that uses this approach. Imagine a data warehouse pipeline with dozens of such updates. Any change to the table schema must be hard-coded into every single update operation.

As an alternative approach, I suggest you first try stripping all indexes not involved in the update (joins or where clause), then use a regular UPDATE statement and rebuild the indexes. Depending on the dimensions of you table, its indexes, the complexity of the update and the number of rows involved, an update-in-place may nearly as fast as the "CREATE TABLE AS" method, and your code will be simpler and more stable.


Create a trigger that compares a field, with a field in other table I have 3 databases, (One principal, one Mirror and a witness) and I'm trying to make this system the most fault tolerant as possible.

So I was thinking in doing some extra codding to protect me against the loss of the witness in some cases.

I want to keep track of mirroring_state_desc in SYS.database_mirroring database, so that in case it changes from SYNCHRONISING to DISCONNECTED I'll know its not safe to promote it to principal, but if it changes from SYNCHRONIZED to DISCONNECTED is ok to promote it (I'm working in high safety mode)

My first approach was to have a custom windows service monitoring this table...but doing that I'm doing a LOT of unnecessary selects to the database.

So I though, maybe a trigger would be what I'm looking for... but i stuck with just

CREATE TRIGGER modify_state ON SYS.database_mirroring FOR INSERT, UPDATE

I need help for the rest of the syntax.

I have a state_db database with 2 columns (last_state and current_state), and I need to be able to check the current state, with the one in my state_db. if they are different I want to modify the last_state with current_state and current_state with the fresh state read from SYS.database_mirroring

My question is, can I use a trigger for insert and modify to keep an auxiliary database with the last 2 states of SYS.database_mirroring?? and if yes, how can I do it?

You can't create triggers on system objects. So going down the path you're thinking isn't possible.

Your best bet to increase fault tolerance is to move your Witness database to it's own server hardware, (not just another instance on the same box even though SQL Server allows you to set it up that way), separate from both the principal and the mirror.

That way you're highly unlikely to have a failure that takes out both your principal AND your mirror database at the same time.


SQL query not selecting records I'm trying to select * records. The problem is some only have values ('MNT', 'NPT', 'OM') for attrib_type 1-3 in this case. Attribute types 4 & 5 do not exist in the older records therefore there are 4500 records that are not being selected. I would like to be able to select ALL records even where there is no attrib_type 4 & 5 value.

The SQL I have tried is below

SELECT
   central_site.site_code,
   central_site.site_name,
   ward.ward_name,
   area.area_name,
   feature_type.feature_type_code,
   feature_type.feature_type_name,
   feature.feat_cent_east,
   feature.feat_cent_north,
   feat_measurement.feature_quantity,
   feat_attrib_type.attrib_value_code,
   feat_attrib_type_2.attrib_value_code,
   feat_attrib_type_3.attrib_value_code,
   feat_attrib_type_4.attrib_value_code,
   feat_attrib_type_5.attrib_value_code
FROM
   area,
   central_site,
   feat_attrib_type,
   feat_attrib_type feat_attrib_type_2,
   feat_attrib_type feat_attrib_type_3,
   feat_attrib_type feat_attrib_type_4,
   feat_attrib_type feat_attrib_type_5,
   feat_measurement,
   feature,
   feature_type,
   site,
   ward
WHERE
   central_site.site_code = site.site_code AND
   site.site_code = feature.site_code AND
   ward.ward_code = feature.ward_code AND
   area.area_code = feature.area_code AND
   feature_type.feature_type_code = feature.feature_type_code AND
   feature.site_code = feat_attrib_type.site_code AND
   feature.plot_number = feat_attrib_type.plot_number AND
   (feat_attrib_type.attrib_type_code = 'MNT' OR feat_attrib_type.attrib_type_code IS NULL) AND
   feature.site_code = feat_attrib_type_2.site_code AND
   feature.plot_number = feat_attrib_type_2.plot_number AND
   (feat_attrib_type_2.attrib_type_code = 'NPT' OR feat_attrib_type_2.attrib_type_code IS NULL) AND
   feature.site_code = feat_measurement.site_code AND
   feature.plot_number = feat_measurement.plot_number AND
   (feat_measurement.measurement_code = 'NSNP' OR feat_measurement.measurement_code IS NULL) AND
   feature.site_code = feat_attrib_type_3.site_code AND
   feature.plot_number = feat_attrib_type_3.plot_number AND
   (feat_attrib_type_3.attrib_type_code = 'OM' OR feat_attrib_type_3.attrib_type_code IS NULL) AND
   feature.site_code = feat_attrib_type_4.site_code AND
   feature.plot_number = feat_attrib_type_4.plot_number AND
   (feat_attrib_type_4.attrib_type_code = 'PMNT' OR feat_attrib_type_4.attrib_type_code IS NULL) AND
   feature.site_code = feat_attrib_type_5.site_code AND
   feature.plot_number = feat_attrib_type_5.plot_number AND
   (feat_attrib_type_5.attrib_type_code = 'WMNT' OR feat_attrib_type_5.attrib_type_code IS NULL)
ORDER BY
   central_site.site_code ASC

As Mihai said, use a LEFT JOIN on feat_attrib_type_4 and feat_attrib_type_5.  Either old style (as you have) or ANSI joins style would work.  For the former add (+) after every reference to a column in the where clause from those two aliases.  For the latter, convert the statement to ANSI join syntax and add LEFT before the word JOIN for those two tables.


SQL Server 2008 R2 - How does the SQL Trigger actually work in this case? I have a table named _Test which has the following values stored in it.
Primary Key : Id

I also have designed a trigger on that table

ALTER Trigger [dbo].[_Test_Data_Save]
On [dbo].[_Test]
For Insert
As

--Some custom algorithm which takes about 2-3 seconds to complete

Now, 3 records were saved at the same time from 3 different sessions at the same microsecond like in the image below.

Question

Now my question is how will the trigger work for the last 3 records?
Will the last 3 records fire 3 different triggers:-

simultaneously
one by one (according to the order they are saved in)
in any random order SQL seems fit

If the trigger is not executed one by one then what can i do so that they execute the trigger for the record of Id=5 first then after completion of that Id=6 and then trigger for Id=7.

Trigger code executes in the scope of the transaction that invokes it. Since, as you say, each of these three records is inserted by a separate session, this means three different transactions were completed, each inserting one record and executing trigger code for that one record.

If you initiate three (or more) transactions "simultaneously", you cannot control the order in which parts of execute transaction will execute beyond setting the transaction isolation level. If the isolation level is set to serializable, then the trigger code will execute "one by one (according to the order they are saved in)"; otherwise it will seem to run "in any random order SQL seems fit", subject to whatever locks each transaction obtains.


database design- diagrams I'm trying to derive an Entity Relation diagram from a class diagram.
In the class diagram, I have a class Player with one to many relationship to another class Payment. In Payment there is no attribute 'paymentID' and 'playerID' is foreign key. Since the same player ('playerID') can make the same payment many times, I thought of adding an attribute 'paymentID' to the Payment table. Is this right? Will I still follow the requirements? I'm new to all these, thank you for any help.

Every table should have a primary key (I really can't think of a reason not to have one). So having a paymentID column to your payment table is definitely a standard design.


How to parse XHTML node value with TSQL? I have the following XHTML and would like to be able to parse out My Node Value. Is there a way to easily do this with TSQL?
My Node Value

You can extract the node text with XML methods as long as the value is well-formed XML.
If the XHTML value is a parameter or variable:
DECLARE @XHTML xml = N'My Node Value'
SELECT @XHTML.value('.','varchar(100)');

If the XHTML value is in a table column:
SELECT YourColumn.value('.','varchar(100)')
FROM dbo.YourTable;

The node paths can be adjusted if the SPAN element is not the root node.


Composite field in SQL Server, does it exist? I'm designing a table to be used by a Web App, and my framework requires that the primary key be a surrogate key. But I also need an extra field which will be a composite of 2 other fields. In other words:

Id -> Primary key
OrderNumber
OrderDate
UniqueOrderNumber -> a composite of OrderNumber and OrderDate

UniqueOrderNumber should be a string generated by merging OrderNumber and OrderDate. This new field will then by used in certain queries that require a unique OrderNumber.

I wanted to avoid handling this logic in the application as I feel this should be a job for the databate. Is there a way to have the database generate this UniqueOrderNumber field automatically by concatenating the values of OrderNumber and OrderDate?

You could leverage computed columns for this.  So your create table would look like:

CREATE TABLE foo
(ID int not null,
 OrderNumber int not null,
 OrderDate datetime not null,
 UniqueOrderNumber AS
   (CONVERT(varchar(20),OrderNumber) + ":" + CONVERT(varchar(20),OrderDate,112))
)

SQL Fiddle Example


SQL Server database size didn't decrease after deleting large number of rows. I'm no good in SQL, but I've got a database to maintain.

There's almost no place left for it, so I've decided to delete all the data for, let's say, year 2008. After executing delete query (had about 10 000 000 rows cleaned) and cleaning transaction log I've found out that my actions had no effect on database size. Is there anything else I have to do?

While shrinking is dangerous indeed for the reasons mentioned here. There is a happy medium between Jimbo's answer and John's answer... You should always seriously consider whether or not you want to shrink your database.

In an ideal world - you'd create your DB with plenty of free space to grow into. I call this "Right Sizing" your database. You would allow this free space to be there and not strive to give it back and keep your total size right at your used size.. Why? Because your database will eventually grow again.. Then you'll shrink again.. And you'll be stuck in this horrible pattern of useless shrinks followed by growths - and the entire time, as a few have pointed out, you'll be increasing your index fragmentation.

I've blogged about this where I admonished folks to "Don't touch that shrink button!" but sometimes... Sometimes you need to. If you have a large database, just freed significant space and don't expect to grow back into it ever - well then it is okay to consider shrinking as a one time operation as long as you can take care of your index fragmentation afterwards through rebuilding them. The shrink operation can be time consuming so you'd want to plan it for a time where you can pay that price of a shrink running. The approach of creating an empty DB and copying data into it works - but that can become very difficult with larger databases and a lot of data.

If you plan on adding that space back to the DB through normal usage and growth patterns into the future, then you may just want to leave the space there.

Also
You said you "cleared" your transaction log. I'd be curious to know how you did this but as you read the post I shared and the others in the series you'll see some tips on transaction log management. But in short - if you are in Full Recovery mode you should be taking regular log backups to keep the log reusing itself. Otherwise - with no log backups while in Full Mode - the log file keeps growing and growing and growing and always saves what you've done because you told SQL you don't just want to maintain that log for crash recovery but want to keep a manual backup of it to replay transactions/undo transactions to recover to a specific point in time for recovery purposes... If you are in simple and seeing the log grow excessively, this can be a sign (typically) that you are doing a LOT of work in one transaction (whether you said BEGIN TRAN ... do work.... COMMIT TRAN or whether you just issued one big DELETE statement and deleted a whole mess of data in one implicit transaction.)

I am also assuming that you are looking for this free space on your file system. If you are looking for it within SQL and within that large file you have - it could be that you are waiting on ghost cleanup to complete if looking immediately after your operation. Paul Randal blogs about Ghost Cleanup.


Possible to use a Trigger as failsafe for insert in two databases? (This is for a project on MSSQL 2008R2 Server)

I have been tasked with splitting a table into an internal (same db as before) and an external one (different server).
Currently the idea is to read only from the external table and to insert into the external table IF possible. If the external is unreachable the inserts are to be redirected into the internal table until the external is back online. The records within the internal table will be moved into the external table manually afterwards.

At the moment we have created a view that points to the external table and a instead of insert Trigger that does the following:

ALTER TRIGGER dbo.Log_IoI ON dbo.Log
INSTEAD OF INSERT
AS
BEGIN
    BEGIN TRY
        INSERT INTO LINKEDSERVER.dbo.ExternalLog (...)
        FROM INSERTED
    END TRY
    BEGIN CATCH
        INSERT INTO dbo.InternalLog (...)
        FROM INSERTED
    END CATCH
END

(Let me state at this point, that we are using a view to replace the table because there are a ton of triggers and stored procedures targeting this logtable and we can't change them without an incredible amount of extra work.)

I thought this was a good idea at first but two problems reared their ugly head.

After a lot of try and error I can't seem to be able to contain critical error in the try. For example if the insert into the external db fails because the external db went offline I get the following Message:

OLE DB provider "SQLNCLI10" for linked server "LINKEDSERVER" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "LINKEDSERVER" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 65535, Level 16, State 1, Line 0
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

After some research I have found some discussions over at SQL-Central that it is not possible to do this kind of error-handling within a trigger.

This has me stumped and brings me to my question:

IF this is true, is their any other way to build a failsafe "fork in the road"-Trigger?

I have tried to use
    SAVE Transaction
before the Try and then to Rollback at the beginning of the catch. It had no effect.

I am open to a new direction to solve this problem as I am completly stumped and I might be kicking a dead horse by trying to make this solution work.

PS: Second problem is performance. If the trigger would work in case of a failure of the external db the trigger would wait for a timeout on evrey insert until the insert would be redirected to the internal database. Really bad for performance seeing as it is a logging table.

After a lot of try and error I can't seem to be able to contain critical error in the try.

The big problem here is that performing the remote insert inside the trigger involves a distributed transaction. Triggers execute inside the transaction of the calling statement; in this case, the transaction is the auto-commit transaction for the original INSERT statement. This local transaction is promoted to a distributed transaction when the operation on the linked server is attempted. Crucially, errors raised by DTC cannot be caught by TRY...CATCH.

An alternative approach is to write the view trigger so it only writes to the local table. A separate process (e.g. a SQL Agent job) then attempts to perform the remote inserts, removing successfully-processed entries from the local table.

To make this work, you will need to process the remote inserts one row at a time, via a stored procedure on the remote server that contains a TRY...CATCH block to catch any errors locally. This is restricted to a row at a time because the insert data must be passed via parameters to the stored procedure.

The job process needs to use a transaction to ensure local deletes and remote inserts are atomic, which you'd think would raise the same sorts of issues as with the trigger, but no - this does work in all scenarios I have tested so far.

You could also use Service Broker for this, which is a bit more work initially, but would avoid the row-at-a-time restriction, and is probably neater all round.

  Second problem is performance.

You can reduce the impact of this by reducing the connection timeout value using sys.sp_serveroption, for example:

EXECUTE sys.sp_serveroption
    @server = N'LINKEDSRV',
    @optname = 'connect timeout',
    @optvalue = N'1'; -- seconds

This is a moot point though if you decide to implement using Service Broker.


Taking the difference in timestamps between successive entries This is my first foray into DBA.stackexchange, so I'll say up front: this is a query optimization question.  If this is not an appropriate question for DBA, let me know and I'll skulk away quietly...

I'm trying to get the difference between timestamps using a self join.  Despite indexing, it seems to be taking a very long time.  I'd welcome suggestions for optimizations.

The setup (with sample data)

CREATE TABLE reading(reading_id INT, device_id INT, time_recorded TIMESTAMP);

CREATE INDEX my_idx on reading(device_id, time_recorded);

INSERT INTO reading(reading_id,device_id,time_recorded) VALUES
(42271930,1154,'2014-11-18 13:12:38'),
(42271931,1154,'2014-11-18 13:12:40'),
(42271932,1154,'2014-11-18 13:12:52'),
(42271933,1154,'2014-11-18 13:13:21'),
(42271934,1154,'2014-11-18 13:13:22'),
(42271935,1154,'2014-11-18 13:13:23'),
... snip ...

The query

    select t1.time_recorded as tr1,
           min(t2.time_recorded) as tr2,
           timestampdiff(second, t1.time_recorded, min(t2.time_recorded)) as td
      from reading as t1
inner join reading as t2
        on t2.time_recorded > t1.time_recorded
     where t1.device_id = 1154
       and t2.device_id = 1154
  group by t1.time_recorded;

The explain

|ID |SELECT_TYPE|TABLE|TYPE|POSSIBLE_KEYS|KEY   |KEY_LEN |REF  |ROWS |FILTERED |EXTRA              |
|1  |SIMPLE     |t1   |ref |my_idx       |my_idx|5       |const|14   |100 |Using where; Using index|
|1  |SIMPLE     |t2   |ref |my_idx       |my_idx|5       |const|14   |100 |Using where; Using index|

An excerpt of the results

|                             TR1 |                             TR2 | TD |
|---------------------------------|---------------------------------|----|
| November, 18 2014 13:12:38+0000 | November, 18 2014 13:12:40+0000 |  2 |
| November, 18 2014 13:12:40+0000 | November, 18 2014 13:12:52+0000 | 12 |
| November, 18 2014 13:12:52+0000 | November, 18 2014 13:13:21+0000 | 29 |
| November, 18 2014 13:13:21+0000 | November, 18 2014 13:13:22+0000 |  1 |
| November, 18 2014 13:13:22+0000 | November, 18 2014 13:13:23+0000 |  1 |
| snip...

The question

The results are correct.  And on the small dataset given here, it runs in 170 ms (on the sqlfiddle site).  But on a table with ~20K rows, the query takes over seven minutes to run (on an Amazon RDS / AWS instance).

My feeble understanding of EXPLAIN suggests that the query is using the index properly.  Is this just an inherently slow query, or is there a faster way?

Update 1: A faster way

The following query is faster: about 2.5 minutes vs 7.  It assumes that timestamps are in increasing order -- while this is generally true it's not guaranteed, so I'm not particularly keen on this as a solution:

select t1.time_recorded as tr1,
       timestampdiff(second,
                     t1.time_recorded,
                     (select t2.time_recorded
                        from reading as t2
                       where t2.time_recorded > t1.time_recorded
                         and t2.device_id = 1154
                       limit 1)) as t2
  from reading as t1
 where t1.device_id = 1154;

You don't need a self join of any kind. Just use variables.

SELECT
time_recorded AS current,
@prev AS previous,
timestampdiff(second, time_recorded, @prev) AS diff_between_current_and_previous,
@prev := time_recorded
FROM
reading
, (SELECT @prev := null) var_init_subquery
WHERE device_id = 1154
ORDER BY time_recorded

What's important in this query is first the ORDER BY and then the ordering in the SELECT clause.

@prev AS previous, /*here it outputs the value of the previous row*/
@prev := time_recorded /*here the value of the current row is assigned*/

The subquery in the FROM clause is just a fancy way to not have to write "two queries" like this:

SET @prev := NULL;
SELECT
time_recorded AS current,
@prev AS previous,
timestampdiff(second, time_recorded, @prev) AS diff_between_current_and_previous,
@prev := time_recorded
FROM
reading
WHERE device_id = 1154
ORDER BY time_recorded


PostgreSQL dump/restore from 8.4 to 8.1 in a shell script I have to restore a DB table every day from a postgresql 8.4 to 8.1.
For the backup, I use this code (windows server):

D:\PostgreSQL\8.4\bin\pg_dump -U postgres -p 5433 -t my_table myDB > D:\backup\myFile.sql

I send "myFile.sql" through SCP to the second server, I restore it with the below command via a console (centos) :

su - postgres

psql myDB

drop my_table;

\q

psql -U postgres -p 5432 -d myDB -t my_table myDB -f /share/temp/myFile.sql

exit

Because of the version problem, I can't use pg_dump and pg_restore. I want to put this code in a shell script and execute as cron task, but I don't know how to do. I have tried with psql:

su - postgres -c "psql -U postgres -p 5432 -d myDB -t my_table myDB -f /share/temp/myFile.sql"

But many errors occurred like:

 duplicate key violates unique constraint
 ERROR:  relation "site_idx" already exists
...

It would be better to drop the table, create a new and restore with psql but I don't know how to do it. Any ideas?

You can drop the table (and dependent DB objects) with the pg_dump parameter --clean, from the pg_dump manual:

  -c, --clean
     Output commands to clean (drop) database objects prior to
     outputting the commands for creating them. (Restore might generate
     some harmless errors.)

So in your case this should be

D:\PostgreSQL\8.4\bin\pg_dump -U postgres -p 5433 --clean -t my_table myDB > D:\backup\myFile.sql

you should then be able to restore the dump file with your current restore command in the cron job.

Hope that helps.


inner joins with where clause for no values? I have 2 tables :

Table1

Parent(varchar) Child (varchar)

Table2

C1(varchar)    PC (varchar)

Sample data:

Requirement - I need Table2.C1 values for which column Table2.PC = Table1.Child , but Child values must be different from Table1.Parent's values..

I'm using below query in mysql:

select distinct(C1),Child,PC
  from Table2 inner join Table1
    on Table2.PC=Table1.Child
 where Table1.Child not in (select Parent from Table1);

It is giving empty set, but there are values in Child which is same as in PC, but not in Parent....

Where I'm getting wrong?

The "where" clause excludes everything in Table1

Provide sample data in the question if you would like an SQL query example


Learning to Optimize SQL Queries and Understand Execution Plans - Resources? I find myself writing more and more SQL queries at work (mostly Oracle 11g, but some SQL Server 2005-2008) and have started creating some pretty complex views for the rest of the analyst team.

They mostly all run quite well, but some of them not so well. So...

How do I learn to tune my queries?
Do I need to learn to read/act on Execution Plans?

And...

What books/websites can you recommend to learn about SQL query tuning 1) in general 2) specifically for Oracle 11g?

We have some good DBAs here, but they're just too swamped to help us tune every query we write.

Most of the books that I've found on Amazon for Oracle all seem to be geared toward overall database optimization and/or were written 8-10 years ago.

Thanks kindly for your advice :)

I would say that learning how to understand explain plans is a vital skill in helping you to optimise SQL statements. I've found Christian Antognini's book, Troubleshooting Oracle Performance, very useful in detailing how these work, as well as explaining how to approach database optimization. While a few years old, you'll still learn a lot that's still relevant from it.

If you get more advanced you could look at Jonathan Lewis' books, but these are more in-depth so probably not a good starting point. Cost-based Oracle Fundamentals is quite old now, but much of it is still relevant. I've not read Oracle Core: Essential Internals for Troubleshooting yet, but it's received good reviews from the Oracle community.

As you're on 11g, if you have queries that take more than a few seconds I'd definitely recommend looking at the real-time SQL monitor (assuming you're appropriately licensed). As the name suggests, it shows the progress of an SQL statement in real-time, breaking down how long each operation has taken with details of rows fetched so far. It also keeps details of recently executed queries for a short while so can see how your changes affect a statement.

Oracle SQL Monitoring documentation: http://docs.oracle.com/cd/E11882_01/server.112/e16638/instance_tune.htm#PFGRF94543

Learning how to tune queries is something that will take time and practice. A few things I've learned:

Write queries to fetch as few rows as possible as soon as possible (e.g. you don't want to full scan a 10 million row table if you only need 100 rows from it)
Verify that number of rows expected in each step of an explain (expected) plan match those returned in the actual execution plan. When these are orders of magnitude different it's likely the optimizer isn't choosing the "best" plan.
Understand the principles of good indexing: how they work and when they should/shouldn't be used when executing a query (Richard Foote has a very in-depth blog discussing indexes in Oracle)

Mostly you'll learn by writing queries, looking at the (expected) explain plans and comparing these to the actual execution plans (either via tracing the query or using the SQL monitor). Then re-write the query, add/remove indexes, etc. and see how it affects the plans and execution times


Interpretation of a column in SQL Server profile trace In a trace table generated by Profiler I tried to determine the origin of a lock using the column ObjectID2 joined to sys.allocation_units.

The results are not good. It identifies many tables that are NOT on the query that generated the trace.

Anyone knows what is that column means? My Google-fu failed me today.

The base query I used to find the object (the final one does not have the au.*, p.*
part):

select object_name(p.object_id) AS name, tt.RowNumber, au.*, p.*
from
  Trc_Tables_20131210 tt
  INNER JOIN sys.allocation_units au
    ON tt.ObjectID2 = au.allocation_unit_id  AND tt.ObjectID = 0
  INNER JOIN sys.partitions p
    ON au.container_id = p.hobt_id

WHERE au.type IN (1,3) AND tt.EventClass = 24

EDIT: The system health is a good indication of study. But for now I have to restrain myself to an SQL profiler trace table to study the locks of the query as I don't have easy access to customer's production server.
About the trace: the main event in trace is the Lock:Acquired.

The object ID is actually a partition id. This question over on SO provides a simple query to retrieve the needed information.

A side note, since you are using SQL Server 2008 R2 I would highly suggest taking a look into the System Health session that contains the recent deadlocks that have occurred, among other things. Depending on the activity of your instance you may need to pull this information out on a scheduled basis since it is buffer memory so does not stay there long. It is much more efficient to pull this information out than trying to setup a trace and waiting for it to occur again.

I actually wrote up a blog post on this area that includes a script to parse out the information where it is a bit more readable. The blog post is here, the script is below:

;WITH xDeadlock (Contents)
AS
(
select CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as xml) as DeadlockGraph
FROM
    (select CAST(target_data as xml) as TargetData
    from sys.dm_xe_session_targets st
    join sys.dm_xe_sessions s on s.address = st.event_session_address
    where name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
), Victims AS
(
SELECT    ID = Victims.List.value('@id', 'varchar(50)')
FROM xDeadlock
CROSS APPLY xDeadlock.Contents.nodes('//deadlock/victim-list/victimProcess') AS Victims (List)
), Locks AS
(
SELECT  --xDeadlock.DeadlockID,
        MainLock.Process.value('@id', 'varchar(100)') AS LockID,
        OwnerList.Owner.value('@id', 'varchar(200)') AS LockProcessId,
        REPLACE(MainLock.Process.value('local-name(.)', 'varchar(100)'), 'lock', '') AS LockEvent,
        MainLock.Process.value('@objectname', 'sysname') AS ObjectName,
        OwnerList.Owner.value('@mode', 'varchar(10)') AS LockMode,
        MainLock.Process.value('@dbid', 'INTEGER') AS Database_id,
        MainLock.Process.value('@associatedObjectId', 'BIGINT') AS AssociatedObjectId,
        MainLock.Process.value('@WaitType', 'varchar(100)') AS WaitType,
        WaiterList.Owner.value('@id', 'varchar(200)') AS WaitProcessId,
        WaiterList.Owner.value('@mode', 'varchar(10)') AS WaitMode
FROM    xDeadlock
        CROSS APPLY xDeadlock.Contents.nodes('//deadlock/resource-list') AS Locks (list)
        CROSS APPLY Locks.List.nodes('*') AS MainLock (Process)
        CROSS APPLY MainLock.Process.nodes('owner-list/owner') AS OwnerList (Owner)
        CROSS APPLY MainLock.Process.nodes('waiter-list/waiter') AS WaiterList (Owner)
), Process AS
(
-- get the data from the process node
SELECT  --xDeadlock.DeadlockID,
        [Victim] = CONVERT(BIT, CASE WHEN Deadlock.Process.value('@id', 'varchar(50)') = ISNULL(Deadlock.Process.value('../../@victim', 'varchar(50)'), v.ID)
                                     THEN 1
                                     ELSE 0
                                END),
        [LockMode] = Deadlock.Process.value('@lockMode', 'varchar(10)'), -- how is this different from in the resource-list section?
        [ProcessID] = Process.ID, --Deadlock.Process.value('@id', 'varchar(50)'),
        [KPID] = Deadlock.Process.value('@kpid', 'int'), -- kernel-process id / thread ID number
        [SPID] = Deadlock.Process.value('@spid', 'int'), -- system process id (connection to sql)
        [SBID] = Deadlock.Process.value('@sbid', 'int'), -- system batch id / request_id (a query that a SPID is running)
        [ECID] = Deadlock.Process.value('@ecid', 'int'), -- execution context ID (a worker thread running part of a query)
        [IsolationLevel] = Deadlock.Process.value('@isolationlevel', 'varchar(200)'),
        [WaitResource] = Deadlock.Process.value('@waitresource', 'varchar(200)'),
        [LogUsed] = Deadlock.Process.value('@logused', 'int'),
        [ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
        [HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
        [LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
        [TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
        [BatchStarted] = Deadlock.Process.value('@lastbatchstarted', 'datetime'),
        [BatchCompleted] = Deadlock.Process.value('@lastbatchcompleted', 'datetime'),
        [InputBuffer] = Input.Buffer.query('.'),
        xDeadlock.[Contents],
        [QueryStatement] = Execution.Frame.value('.', 'varchar(max)'),
        TranCount = Deadlock.Process.value('@trancount', 'int')
FROM    xDeadlock
        CROSS APPLY xDeadlock.Contents.nodes('//deadlock/process-list/process') AS Deadlock (Process)
        CROSS APPLY (SELECT Deadlock.Process.value('@id', 'varchar(50)') ) AS Process (ID)
        LEFT JOIN Victims v ON Process.ID = v.ID
        CROSS APPLY Deadlock.Process.nodes('inputbuf') AS Input (Buffer)
        CROSS APPLY Deadlock.Process.nodes('executionStack') AS Execution (Frame)
)
-- get the columns in the desired order
SELECT  p.Victim,
        p.LockMode,
        LockedObject = NULLIF(l.ObjectName, ''),
        l.database_id,
        l.AssociatedObjectId,
        LockProcess = p.ProcessID,
        p.KPID,
        p.SPID,
        p.SBID,
        p.ECID,
        p.TranCount,
        l.LockEvent,
        LockedMode = l.LockMode,
        l.WaitProcessID,
        l.WaitMode,
        p.WaitResource,
        l.WaitType,
        p.IsolationLevel,
        p.LogUsed,
        p.ClientApp,
        p.HostName,
        p.LoginName,
        p.TransactionTime,
        p.BatchStarted,
        p.BatchCompleted,
        p.InputBuffer
FROM
    Locks l
    JOIN Process p ON p.ProcessID = l.LockProcessID
--WHERE p.TransactionTime > '2013-10-01'
ORDER BY p.Victim DESC,
        p.ProcessId;


What does it mean to backup a database What does it mean to backup a database?

My impression is that you would simply dump everything that makes up the database into some format that can be stored externally and used to restore from when needed.

Are there different levels of backups? For example, maybe it is not necessary to backup absolutely everything about the database. Data is critical, so the table structure is important, but there's probably more to databases than just that.

Database backups are copies of your database contents to prevent data loss in case of a disaster or data corruption.

There are two general types of backup Physical & Logical.

A Logical Backup
exports the data stored in a table regardless of the location of the table.
Logical backups recreate the tables,indexes and insert all the data to the tables. Therefore, are relatively slower than physical backups. Depending on the RDBMS and the software you use you can change the way the logical backup is performed for the example you mentioned, in MySQL using mysqldump dbname --no-data  you can get a backup of table definitions without data in the tables for a database. A logical backup creates a file that when executed restores and recreates all the data in the database.

A Physical Backup
is an actual copy of the data files that store a database's Structure,Index and Data which are on the hard disk.

One of the challenges of taking backups is making consistent backups.When a database is active it may change while the backup is being performed.
During a hot backup( a backup when the database server is running) there are several methods to ensure consistency.

1) Using Table locks: Tables are temporarily locked and they won't execute any UPDATE, INSERT or DELETE.

2) Another method is using Multi-Version Concurrency Control (MVCC) where the database takes a point of time in the database as the backup reference point and backs everything up from there, while accepting UPDATE, INSERT or DELETE while backup is being performed. The advantage of MVCC is that the tables will completely operational while backup is taking place.

NOTE: Locking mechanisms depend on the RDBMS

A Cold backup
is another way to ensure consistency. In a cold backup the database server is turned off and not accepting any new clients.


Different MySQL index sizes after restore I've been trying to create a backup for a very large MyISAM DB with mysqldump and I've noticed that some of the tables have variations in index sizes after restoring.

For example, one of my larger tables has an index length of 50316242944 and the restored dump of that table has an index length of 50227628032

I've run optimize table and have done a checksum and everything seems to be fine. I've even run ALTER TABLE generic_dummy_table_name ORDER BY datetime; which I found on a similar question on this site.

Could someone explain to me why there is such a large difference in index length between my tables and if there is an easy way to fix it?

Random insert into or delete from a secondary index can cause the index fragmentation. Basically it will use extra space than required. There are several ways to reclaim this space. One of the way to reclaim space is, taking mysqldump and restoring it. This helps to reclaim the space.

As far as the restoration completed without any error this should be fine and you don't need to worry.

For more information on fragmentation you can refer to below link.
https://dev.mysql.com/doc/refman/5.6/en/innodb-file-defragmenting.html
https://dev.mysql.com/doc/refman/5.6/en/myisam-optimization.html


SQL Server 2016 SP2 CU2 Availability Group Online but Cluster Offline Am I missing something basic here? My AVG is online and working, but the Cluster on which it resides is saying offline. I didn't think this is possible, so I either have a basic misunderstanding of how this should work, or I have something odd going on. I have

  3 Node Multisubnet AOAG. 2 SQL Servers local data center sync, 1 SQL server DR async.

Get-ClusterResource results:

Cluster IP Address:                Failed
Cluster IP Address XX.XX.XXX.XX    Failed  (2nd subnet node)
Cluster name:                      Offline
File Share Witness:                Online
Availability Group Name:           Online
Availability local IP:             Online
Availability 2nd subnet IP:        Offline
Availability Group name:           Online

The databases are all synced and online. Availability group works fine. I get a lot of strange crashes that may or may not be related (CheckDB causes SQL to crash often despite this being a static test cluster with 8CPU and 128GB mem). Doesn't seem related at all. More baffled by the cluster being offline but AVG online. I have this same setup in prod (only diff is storage), and as expected, on that one Cluster name, and local IP are online

but the Cluster on which it resides is saying offline.

It's not offline, otherwise clussvc would stop and you'd have a spew of errors in your errorlog that the AG shutdown due to loss of cluster services and that it's waiting on the cluster services to startup before proceeding.
I believe you're basing the above quoted conclusion on:

Cluster IP Address:                Failed
Cluster IP Address XX.XX.XXX.XX    Failed  (2nd subnet node)
Cluster name:                      Offline

This is just letting you know that the cluster name and associated IPs are not online. These are part of the core cluster resources but will not stop the cluster itself nor most of the services on it. In fact, if you try to remotely connect to the cluster using the cluster name (like RSAT) tools it should fail but that's just name resolution and administrative endpoints. This won't impact the cluster resources in a different resource group unless for some reason they use that name (I couldn't fathom why, though).

The databases are all synced and online. Availability group works fine.

Yes, this is because the cluster is running though you have some failed resources that should probably be taken care of sooner rather than later.

What should I be checking for basic Cluster running (other than AVG is running)?

This is going into the realm of Windows and while I have no problem talking about it, I'm not sure if this is the right place for it so I'll give you a small primer of items to check with WSFC (Windows Server Failover Clustering):

Is the "clussvc" running?
Is the witness online?
Can you remotely connect to the cluster? (Get-Cluster MyCluster)
Are any resources in a state other than up or running? (Get-ClusterResource -Cluster MyCluster)
What are the current votes in the cluster? (Get-ClusterNode -Cluster MyCluster | Select Name, NodeWeight, DynamicWeight [WS2012+])

Note that you don't have to use powershell but it's one of the easiest and fastest ways to get information about cluster resources without writing your own calls via the clustering API.
Aside: I wouldn't call Availability Groups AVG's, typical nomenclature would be AG or AGs.


Error in using sp_describe_first_result_set with Restore Is querying the backup metadata with sp_describe_first_result_set system sp supported?

For example the query:

SELECT * FROM sys.dm_exec_describe_first_result_set (N'RESTORE HEADERONLY FROM DISK =''C:\SQLBackups\SomeDBbackup.bak''',NULL,0)

returns NULL for each field.

error_message field:

  The metadata could not be determined because statement RESTORE
  HEADERONLY .... does not support metadata discovery.

error_type_desc field:

  UNSUPPORTED_STATEMENT

Is there a way to make this query work?

Thank you

The metadata could not be determined because statement RESTORE
  HEADERONLY .... does not support metadata discovery.

Error message tells you clearly that RESTORE HEADERONLY is unsupported statement, it does not support metadata discovery.

So there is NO WAY to make this work. It is not implemented.

UPDATE

There is a solution in the link you posted, but the question there was NOT how to make work sp_describe_first_result_set with smth that is not supported (that is YOUR question), but how to get back only some columns from RESTORE HEADERONLY resultset, i.e. how to get the result of RESTORE HEADERONLY in "queryable" form.

There is a solution like this:

SELECT BackupStartDate
FROM OPENQUERY(LOCALSERVER,
               'SET FMTONLY OFF;
               EXEC(''
               RESTORE HEADERONLY
               FROM DISK = ''''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\Backup\DB1.bak''''
'')')

But as you see the Server in question was 2008.

Starting with SQL Server 2012 sp_describe_first_result_set was introduced, and now if you try to execute the query above, another thing is executed (you can see it using Profiler):

exec [sys].sp_describe_first_result_set N'SET FMTONLY OFF;
               EXEC(''
               RESTORE HEADERONLY
               FROM DISK = ''''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\Backup\DB1.bak''''
'')',NULL,1

It's wrong to think that OPENQUERY/OPENROWSET always use sp_describe_first_result_set, you can prove it to yourself executing simple SELECT queries and monitoring execution using Profiler (even on 2012). But in your particular case the query is transformed as described above and you cannot do nothing with it.


Make Oracle SQL Developer act like PL/SQL Developer Is it possible to configure Oracle SQL Developer v 4.03 to automatically display tables while I'm typing in names like in PL\SQL Developer?

Second question: How to make output grid look like PL/SQL Developer? Is it possible?

Is it possible to change default colors in zebra output? I have enabled 'Zebra pattern'

Preferences->Database->Worksheet->Grid in checker board or Zebra pattern

...but default colors are so...ugly. It would be great to change also default NULL values (change field color and (null) to just NULL). Could you add option to set user's color for NULL and zebra coloring? What if I want to use magenta or cyan?

The preferences dictate when the code assist features kick in. In 4.0.3 we filter the advice for the automatic bits when there are more than 10 suggested identifiers. So, typing:

select * from

...on a large schema, you'd likely not see any suggested table or views names, unless you invoked the helper directly via ctrl+spacebar.

As you type more, the more likely the suggested values will be of more value. The default settings will have automatic popups after .6 seconds and the number of hits goes below 10.

Tools > Preferences > Code Editor > Completion Insight

Make sure you don't have the first two items disabled. And try increasing the filter to something higher than 10. Examples of different insight 'helpers' and a video demo here.

You can set your own NULL text, that's in the preferences. I have a bug logged so you can set the NULL coloring AND use the zebra styling (fixed in version 4.1, soon to be released.) And I also have an ER logged for custom colors on the 'zebra' styling.

We fixed that bug, go get v4.1.1 - null color + zebra grids work now.


How to block external connections for an Oracle database? I have an oracle database (11g-r2) on my (windows) computer for development, and I am trying to forbid any outside connections. Is there a way to do this?

The solution is really dependant on your environment.  There are numerous options, from hardware firewalls, to software firewalls, to sqlnet.ora parameters.  The first two are beyond the scope of this site.

The quickest, probably, is sqlnet.ora.

Have a look at the tcp.invited_nodes parameter:
https://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF237

This goes hand in hand with the tcp.validnode_checking parameter:
https://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF238


Database access: what should I do here? I'll explain my situation here, I hope I'm posting this on the correct site, otherwise I'm sorry and I'll move this to somewhere else. I've built a Java program in order to control sales, inventory, prices, etc of a small business. The program communicates with a SQL database inside the WAMP folder.

It works fine. 100% fine. But here is where the problem begins. It would be better if I could access the database from more than one computer. I should be able to host it online, but one of the computers wouldn't always be online and the database has to be updated when the offline computer accesses it. For example, if I insert a new row on a table using one of the computers while accessing the database, when I'm not online I need to have that row there.

Could I use a CD, a flash drive, or should I download it every time I need to access it offline? Maybe there is a simple solution and as I'm a beginner in all this I hope you can help me out.

First things first, if your application needs a data set to function then it needs access to that data set. Normally the computer would be networked to the central repository, but if that isn't possible then the computer needs to have a local copy that synchronizes with the central repository whenever it can communicate with that repository.

Your application will need to maintain some sort of change log since the last synchronization. Then when the application discovers it can communicate with the central repository it will need to synchronize the local changes with the remote changes. Keep in mind this can cause problems.

For instance if your application is a sales database for Acme Widgets a sales person might check inventory to verify they have 15 gizmos in stock. They proceed to sell those gizmos at the fair, but when they get home to and synchronize their laptop the realize that during the day 5 other sales representatives in the home office sold 15 gizmos each. Now you have over sold your gizmos.

In order to avoid the catastrophe of over selling gizmos you should ask management to get cellular data plans for the laptops so they always have access to the central database. Alternatively you might not care if the database synchronization results in oversold gizmos. I don't know the details of your application.


Error log folder location and OLA's SQL maintenance solution Looking at one of our dev sql servers, it has a -e startup parameter to define the error log folder at this location : E:\DATA\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG.  However, only E:\DATA\MSSQL13.MSSQLSERVER\MSSQL exists on disk.  I can see Ola's SQL maintenance output files on disk at E:\DATA\MSSQL13.MSSQLSERVER\MSSQL\Log
Who / what is responsible for creating the error log folder as defined by the -e startup parameter?
Does OLA's maintenance solution have a fallback if it can't find the log folder defined with -E startup parameter?

All the code is on github
There is quite a lot of it now
github link
IF @Version >= 11
  BEGIN
    SELECT @LogDirectory = [path]
    FROM sys.dm_os_server_diagnostics_log_configurations
  END
ELSE
  BEGIN
    SELECT @LogDirectory = LEFT(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(max)),LEN(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(max))) - CHARINDEX('\',REVERSE(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(max)))))
END


Find signed procedures, functions, triggers, assemblies and by which certificates / asymmetric keys I have created a few Certificates (via CREATE CERTIFICATE) and Asymmetric Keys (via CREATE ASYMMETRIC KEY) and used them to sign and counter-sign various Stored Procedures, User-Defined Functions (UDFs), Triggers, and Assemblies (via ADD SIGNATURE). But now I need to find which Certificates and/or Asymmetric Keys have been used to sign which particular modules.

The easiest way seems to be using the sys.crypt_properties Catalog View which holds the relationship between Certificates / Asymmetric Keys and signed modules, as well as the usage (i.e. Signature or Counter-Signature). There are a few other means of finding which modules are signed, but none seem to indicate Signature vs Counter-Signature.

SELECT SCHEMA_NAME(so.[schema_id]) AS [SchemaName],
       so.[name] AS [ObjectName],
       so.[type_desc] AS [ObjectType],
       ---
       scp.crypt_type_desc AS [SignatureType],
       ISNULL(sc.[name], sak.[name]) AS [CertOrAsymKeyName],
       ---
       scp.thumbprint
FROM sys.crypt_properties scp
INNER JOIN sys.objects so
        ON so.[object_id] = scp.[major_id]
LEFT JOIN sys.certificates sc
        ON sc.thumbprint = scp.thumbprint
LEFT JOIN sys.asymmetric_keys sak
        ON sak.thumbprint = scp.thumbprint
WHERE   so.[type] <> 'U'
ORDER BY [SchemaName], [ObjectType], [ObjectName], [CertOrAsymKeyName];

In my test DB, this query returns the following:

Schema  ObjectName     ObjectType            SignatureType                        CertOrAsymKeyName  thumbprint
------  ----------     ----------            -------------                        -----------------  ----------
dbo     fnPaymentCalc  SQL_SCALAR_FUNCTION   COUNTER SIGNATURE BY ASYMMETRIC KEY  KeyTest1           0x2333B2FA6AA8004E
dbo     ModuleTest2    SQL_STORED_PROCEDURE  SIGNATURE BY CERTIFICATE             CrossDatabaseCert  0x49BA174584C78C878D923690C15898A809CBACDF
dbo     TestSig        SQL_STORED_PROCEDURE  SIGNATURE BY CERTIFICATE             bob                0x778B3DB4ED981FC27AB301ACE7A1AB8424F64792
dbo     TestSig        SQL_STORED_PROCEDURE  SIGNATURE BY ASYMMETRIC KEY          KeyTest1           0x2333B2FA6AA8004E


difficulties working with powershell Invoke-SQLCmd I've setup a simple sqlcmd script.  Contents below.

  use $(dbname);
  select * from $(tablename)

I'm then running the following Invoke SQLCMD

$SQLCMDVarArr = "dbname='MyDatabase'", "Tablename='MyTable'"

INVOKE-SQLCMD -ServerInstance MyServer\MyInstance -inputFile '\\MyShare\Database\Sprints\csrtemp2\csrtest.sql' -variable $SQLCMDVarArr

And I'm getting the error:

  INVOKE-SQLCMD : Incorrect syntax near 'MyDatabase'.

I pulled the syntax straight from the invoke-sqlcmd get-help examples.  Can anyone tell me what is wrong with this?

Thanks,

as per request this is running on windows Server 2008 R2

So it turns out that the get-help invoke-sqlcmd -example shows the array variable as using 'dbname="MyDatabase"' as the syntax.  However this gives the error above but if you swap the quote positions such that the variable uses "dbname='MyDatabase'" then the script works.


Restarting SQL Server if shrinking database files If frequently shrinking database files is it smart to restart SQL Server/ or service ?
I currently work in a company (Mainly analytics databases , using SIMPLE Recovery Model)where we have to shrink the data files sometime to reclaim  space on the disk (Although i did say to my manager shrinking data file is not a smart think to do. Shrinking the log are fine).
I suggested to my manager that we restart the server as we have been shrinking the data file a lot this month.
Can you share your thought and expertise on the subject.

As you mentioned yourself, often shrinking is a bad idea, and should only be done on special occasions. Eg. You just archived half of your db, and you're really in need of some extra space.

Rebooting the server will NOT solve your performance issues, what this will do is give you a bunch of new issues.

SQL likes to keep data pages (Buffer pool) and plan caches in memory, once you reboot the server these are gone, which means all queries will need to get new plans compiledand new data filled in buffer pool from disk, which of course is a lot slower.
A lot of the DMVs that return server information will have lost all useful information
Rebooting causes downtime, can you afford to have your services go down?

What you should do: Allocate enough space for your data files so they don't need to grow, and keep some extra free space in case of unexpected data growth.
If there's not enough disk space to use for your normal day to day use you could:

Increase disk size
Take a look at unused/duplicate indexes, perhaps some space can be freed up here
Take a look at your data compression
Archive unneeded data


SQL Server log shrinking Issue Possible Duplicate:
  Why Does the Transaction Log Keep Growing or Run Out of Space?

I have a 300 GB database log in our SQL Server. I want to shrink all the logs but it is not allowing me to do so. I tried by using query and UI in SQL Server 2008 R2 . I used

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

But without any error database logs are not shrinking.

Then I want to use one more method as below :

I will detach the database from SQL Server, then after the move particular .LDF file to some other location and attach only the database file back to SQL Server. I am able to successfully move all the logs from SQL Server to some other server.

Please let me know this is good practice to move transaction log to some other drive. If not please suggest some other solution to recover from shrinking database log.

If you are shrinking a log file, you won't be able to shrink it any less than the active log portion.  In other words, if you are in Full Recovery Model, and you haven't done any transaction log backups (which is why it could have grown that large), then running a DBCC SHRINKFILE will do absolutely nothing.

BOL has an example on how to do this:

USE AdventureWorks2012;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL;
GO

That was taken straight from BOL on DBCC SHRINKFILE.

It is worth noting in the above example that changing the Recovery Model to simple breaks the log chain.  So once this operation is complete, you need to immediately start a new log chain by running a database backup.

It is also worth noting that if your log file is growing like this, you are not taking [enough] transaction log backups (provided you are indeed in Full Recovery Mode, as this would only happen in very rare situations in Simple Recovery).  You may need to either start taking transaction log backups, or backup your log more often so it truncates the trans log and creates a portion of the file reusable so  you don't have this file growth.


Write Access Client App for SQL Server 2005 Backend I'm writing an inventory/payroll system, and I'm storing all the records on a remote server running SQL Server 2005 on Windows Server 2008 R2. I was wondering how I could start writing forms(very basic) in MS-ACCESS 2010 to upload things to the database. I just want to test it out to make sure that once I implement the entire database, things will work smoothly and without error. I want to figure out how to just make a single form that will be connected to the database, right now the form will just be a simple single text input with a submit button, that will be uploading barcodes(strings not images).

If I need to clarify anything, please just ask.

Thanks in advance for any help

Although Access has data bound controls, by and large you don't really want to use them for any sort of non-trivial application - partiuclarly if you want to do significant data validation before you save records.

You can use ADO through VBA on Access, much as the same as through VB.  The code to populate a form from a record set is pretty straightforward, and Access comes with a usable set of controls that can be used on a form.

If you're using ADO, the life cycle is that you read the record by issuing a query to the database.  Then you can populate the form from the recordset - this is straightforward to do with VBA.  The 'save' button onclick method calls a validate and save procedure.  You can add whatever other UI code you need to populate dropdowns, enable/disable controls etc.

You may also want to separate data access into a separate class, depending on the complexity of the application.  This lets you have a M:M relationship between formns and data items with the data access modularised into one place (i.e. one form can gather data from two or more queries by using the appropriate modules).

Beyond this we're getting into the realms of application architecture, which is probably a bigger scope than you had in mind for this question.  There are plenty of books on the subject, including ones specifically about application development with MS-Access.


How to create a table in MySQL with automatic numeration of columns? Example. My data is from Table name:BIKE

Bike_No   Repair_Date     Repair_Cost
--------  -----------     ------------
ABC1234   2013-01-05      50.00
BMX5678   2013-02-04      75.00
ABC1234   2013-01-25      20.00
BON3333   2013-03-06      80.00
DEB1111   2013-08-04      40.00
ABC1234   2013-09-06      50.00

I want to see the repair cost for each bike based on
a date range (example: from 01/01/2013 to 31/03/2013) and result table must
display the repair cost with Bike_No and the affected months as columns.
How do we generate the affected months as columns based on the queried date range?

Unfortunately, MySQL does not have a PIVOT function and it sounds like you are attempting to get the data into the format:

+---------+-----------------+-----------------+-----------------+
| bike_no | repair_cost_Jan | repair_cost_Feb | repair_cost_Mar |
+---------+-----------------+-----------------+-----------------+
| ABC1234 |              70 |               0 |               0 |
| BMX5678 |               0 |              75 |               0 |
| BON3333 |               0 |               0 |              80 |
+---------+-----------------+-----------------+-----------------+

If that is the case, then in MySQL you will want to use an aggregate function along with a CASE expression to convert your rows of data into columns.

If you have a know number of months that you are returning then you can easily hard-code the query similar to the following:

select bike_no,
  sum(case when month(repair_date) = 1 then repair_cost else 0 end) repair_cost_Jan,
  sum(case when month(repair_date) = 2 then repair_cost else 0 end) repair_cost_Feb,
  sum(case when month(repair_date) = 3 then repair_cost else 0 end) repair_cost_Mar
from bike
where repair_date >= '2013-01-01'
  and repair_date <= '2013-03-31'
group by bike_no;

See SQL Fiddle with Demo.

But in your case it sounds like you want to change the start date and end date to your query. If that is the case, then you will want to use a prepared statement to generate dynamic SQL that would be executed:

SET @sql = NULL;
set @startdate = '2013-01-01';
set @enddate = '2013-03-31';
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN month(repair_date) = ',
      month(repair_date),
      ' THEN repair_cost else 0 END) AS `repair_cost_',
      monthname(repair_date), '`'
    )
  ) INTO @sql
FROM bike
where repair_date >= @startdate
 and repair_date <= @enddate;

SET @sql
  = CONCAT('SELECT bike_no, ', @sql, '
            from bike
            where repair_date >= ''',  @startdate, '''
               and repair_date <= ''', @enddate, '''
            group by bike_no');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo.  Both queries will generate the table of data above.


How to avoid conditional join in order to write a tsql view I have a scenario where I have Roles, Set, DefaultSet, and CompanySet tables

Roles table

RoleName
---------
Admin
Viewer
User

Set table

Id      Name
---     ----
1       Employee Data
2       Help Desk
3       Comments
4       Interests

DefaultSet table

RoleName   SetId
--------   -----
Admin      1
Admin      2
User       1
User       3

CompanySet table

Company   RoleName   SetId
---------   --------   -----
Acme Inc    Admin      1
Acme Inc    Viewer     1
Bobs Paint  User       2
Dans Disks  Admin      2

This query works but I want to be able to restructure it in a way that it is parameterless('Acme Inc' condition is the param) and can be used in a view.

WITH T1 AS
(
    SELECT Company, RoleName, cs.SetId  FROM Roles r
    LEFT JOIN CompanySet cs on cs.RoleName = r.RoleName
    GROUP BY Company, r.RoleName, cs.SetId
),
T2 AS
(
    SELECT  RoleName, ds.SetId  FROM Roles r
        INNER JOIN DefaultSet ds on r.RoleName = ds.RoleName
        GROUP BY r.RoleName, ds.SetId

)
SELECT * FROM t2
    LEFT JOIN t1 ON t1.RoleName = t2.RoleName
                 AND t1.SetId = t2.SetId
                 AND T1.Company = 'Acme Inc'

Seems like maybe I have to cross join the company on the t2 table so I can match the condition against it. Let me know if there is a way to solve this.

It seems like to me that the query you have could be rewritten as:

SELECT R.RoleName, ds.SetID, --T2 Columns
CS.Company, R.RoleName, cs.SetID --T1 Columns
FROM Roles r
    INNER JOIN DefaultSet ds
        on r.RoleName = ds.RoleName
    LEFT OUTER JOIN CompanySet CS
        ON CS/RoleName = R.RoleName
WHERE CS.Company = 'Acme Inc'

If that is the case, than you would be able to create a view like:

CREATE VIEW ViewName
AS
BEGIN
    SELECT R.RoleName,
    ds.SetID,
    CS.Company --,
    --R.RoleName, --Don't Want To duplicate The Column In The View
    --cs.SetID --Don't Want To duplicate The Column In The View
    FROM Roles r
        INNER JOIN DefaultSet ds
            on r.RoleName = ds.RoleName
        LEFT OUTER JOIN CompanySet CS
            ON CS/RoleName = R.RoleName
END

Then you would simply be able to query the Company column like normal.

SELECT *
FROM ViewName
WHERE Company = 'Acme Inc'

Hopefully that helps and I didn't miss-read the question you asked.


Redis has "commands" that mutate the various data types it can store. Why? Redis is primarily a cache If you look at something like https://redis.io/commands#list, you will see that types that Redis stores and supports are fully mutable, atomically, within the data store's memory.

This does not make alot of sense given that Redis exists as primarily a cache to accelerate applications who have a database separate from Redis and only use Redis as a place to read values back from.

What am I missing here? Why have mutability on values directly on Redis when that does not fit a caching model?

Redis is a Key-Value (KV) store, and KV stores generally are well suited for cache however provide additional functionality.

KV stores can provide synchronisation between a variety of stateless front ends which is what this commands provide.


SSIS Visual Studio 2008 reference variable scope in expression builder When creating an SSIS package in Visual Studio 2008, in the expression builder, I know I can reference a variable by using @[User::FilePath] for example but this only works if you are evaluating the expression for an object that is within the same scope of the variable that you are referencing.

Is it possible to reference the SCOPE of the variable such as @[User:PackageScope:FilePath] or @[User:SQLTaskScope:FilePath] for example so that I can call a variable that is in a different scope then the object I am building the expression for?

No, the availability of variables is always subject to the scope hierarchy.

If you can't access the variable you want, then you need to refactor the structure or redefine the variable at a higher/different scope so that it's available. Sometimes this issue is indicative of a design problem in the package structure itself.


Teradata SQL syntax from date to quarter I need Teradata SQL syntax to extract quarter from a date:

Date: (YYYY-MM-DD)
2015-09-12
2015-05-22
2014-12-15

Given below is the expected output:

3Q15
2Q15
4Q14

Query which I have tried (wrapped for readability):

SELECT DATE, ((CAST(EXTRACT(MONTH FROM DATE) AS BYTEINT)-1)/3)+1
    || 'Q' || substring(DATE,3,2) quarter

Error:

  Missing parameters

Teradata supports Oracle's TO_CHAR syntax since TD14:

TO_CHAR(date, 'q"Q"yy')


SQL Server Processor affinity with trace flag 8002 ON I have been going through an article that explains the unwanted side effects of Setting a Processor Affinity in SQL Server.

What I have come to know is that in most of the cases the default setting of SQL Server works best as when we are setting processor affinity, we are making the scheduler CPU bound. I have a couple of questions here:

When the default setting of Processor affinity works best, then why does the need arise to set Processor affinity explicitly?
The article also states:

  With the trace flag 8002 turned on, the scheduler is not bound to CPU anymore.

Is it justified to say that trace flag 8002 being ON is just like the default Processor affinity configuration with the exception of having less number of cores available for SQL Server?

For example, if we set the processor affinity to 16 cores for a 32 core processor (with trace flag 8002 as ON), then would the schedulers that have SQL threads associated with them, will have only 16 cores to toggle?
Setting a Processor affinity restricts the SQL Server Scheduler to use some cores. Does this setting also restricts the core usage for other CPU intensive processes, such as a .NET application?

What I am trying to say is, if for a 32 core CPU, the processor affinity is set to 16 core, then SQL Server scheduler has only 16 cores to toggle. If yes, then what about another CPU intensive application such as a .NET application? Does the .NET app have the liberty of switching between all 32 cores, since it will be dependent on Windows OS and not SQLOS?

It is just a hypothetical scenario I had imagined. Given a production scenario, I highly doubt people would go for SSMS and a CPU intensive .NET application on the same server. That will greatly hamper performance and scarce the CPU availability for any other process. Correct me if I am wrong here.

Processor affinity should be set in the specific case that your workload fits into the processor cache. This would be rare in SQL Server but might happen when running analytics inside the DB e.g. with R
I believe that is correct, yes
No it does not - you would need to selectively set affinity for each workload. Typically if you had say 32 cores you might reserve 2 for the OS and divide up the others - but taking care to ensure that you keep cores and caches together. You could create a CPU set that uses a subset of cores on each CPU and kill performance with cross cache chatter!

Generally I would advise against this unless you are very sure that you need it, and have a good understanding of the underlying hardware, how its NUMA boards are arranged, etc. You won’t damage anything but you can degrade performance quite badly. If you need to segregate workloads, then a better option on a modern system is to use Hyper-V in 99% of use cases.

Note also that affinity masks will be deprecated anyway and the new method is NUMA-aware.


BULK INSERT does not fail when file contains commas instead of semicolons when FIRSTROW > 1 I encountered an issue where I received a CSV file that supposed to be delimited by semicolons (;) but was delimited by commas (,).
Bulk insert called by sqlcmd did not fail but did not insert either. I know that calling xp_cmdshell is not best practice but please don't comment on this.
After investigation I found that it only fails (as expected) when FIRSTROW = 1, but I need a header inside the file.
Table:
CREATE TABLE [dbo].[test_table](
    [id] [int] NULL,
    [title] [varchar](10) NULL,
    [val] [int] NULL
)

Format file:
12.0
3
1       SQLCHAR              0       4       ";"    1     "id"             ""
2       SQLCHAR              0       10      ";"    2     "title"          SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR              0       4       "\r\n"     3     "val"            ""

Data file:
1,first,0
2,second,2

Bulk insert:
DECLARE @cmd VARCHAR(4000);
SET @cmd = 'sqlcmd -b -S <servername> -Q "set nocount on; set dateformat dmy; bulk insert [test_db].[dbo].[test_insert] from ''C:\temp\test_table.csv'' with ( DATAFILETYPE = ''char'', TABLOCK, MAXERRORS = 1000, FIELDTERMINATOR = '';'', ROWTERMINATOR = ''\r\n'', BATCHSIZE = 100000, FORMATFILE = ''C:\temp\test_table.txt'', FIRSTROW = 2 );"';
EXEC xp_cmdshell @cmd;

Change the command
From
, FIELDTERMINATOR = '';''

to
, FIELDTERMINATOR = '',''

This should do the trick
BULK INSERT [test_db].[dbo].[test_insert]
FROM 'C:\temp\test_table.csv'
WITH
(
    FORMAT = 'CSV',
    FIELDQUOTE = '"',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)

As the ROWTERMINATOR  goes <ou should try it or switch to \r\n


SQL Server : reasons not to use default database settings for ANSI_NULLS? I've been given a case where a customer is experiencing this error - once in a while:

  Msg 8624, Level 16, State 21, Line 1
  Internal Query Processor Error:
  The query processor could not produce a query plan. For more
  information, contact Customer Support Services.

They're using our software, and running it on SQL Server 2008 R2 (RTM) and at compatibilty level 100 (SQL Server 2008). The database however was originally created on a 2000 or 2005 machine (can't reproduce anymore) and then moved to 2008 R2 recently.

The stored procedure in question has an ugly looking INSERT statement which grabs data from ten (yes!) "copies" of a given table, all joined with RIGHT OUTER JOIN against one another (same table - ten JOINs against it).

The one solution I found that seems most appropriate has to do with various settings - it's recommended to use:

set ANSI_NULLS ON
set ANSI_PADDING ON
set ANSI_WARNINGS ON
set CONCAT_NULL_YIELDS_NULL ON
set QUOTED_IDENTIFIER ON
set ARITHABORT ON

set NUMERIC_ROUNDABORT OFF

OK - sure - I can set these before every stored procedure I create (or alter).

My question to the database gurus out there would be: any risk when I set these as default settings for my database?

E.g.

ALTER DATABASE MyDB SET ANSI_NULLS ON

and be done with it? Thoughts? Insights? Recommendations?

It is not a risk at all.  In fact, the ability to turn ANSI_NULLS OFF has a limited lifespan. Soon enough that won't even be an option, it'll always be ON. The only time I've ever seen any form of reasoning for setting ANSI_NULLS OFF, is because there are developers that can't fathom the true meaning of NULL, and therefore want to use typical conditional logic with it. Again, terrible reasoning and not justifiable.  I look forward to the permanent ON setting for ANSI_NULLS.


Compare 4 or more columns values I have a table like:

ID   A0  A1  A2 A3 A4
14   A   B   A  C  A
15   A   A   A  A  A

I need to compare all 5 columns and check if they have same value and retrive the only one that have all same values....in this case the one with ID 15.
Is there a way to accomplish that in T-SQL?

I'm not familiar with SQL Server's brand of
SQL, but it must be something like:

SELECT ID FROM My_Table
WHERE
(A0 = A1) AND
(A1 = A2) AND
(A2 = A3) AND
(A3 = A4);

Check out this dbfiddle.uk which shows all the answers in the answer thread.


Mysql - Select next x records even if reaching rowset end I have a table with an ID field, unique, autoincremental.

I need to, given certain ID, retrieve next 3 records even when given ID is the last one.

Table:

ID
1
2
3
4
5
6
7

What I need:

Given ID is 1 => retrieve 2,3,4
Given ID is 2 => retrieve 3,4,5

.....

Given ID is 5 => retrieve 6,7,1
Given ID is 6 => retrieve 7,1,2
Given ID is 7 => retrieve 1,2,3

How can I do this?

Let me try for a pure SQL solution:

SELECT id
FROM
   (  ( SELECT *, 0 AS chck
        FROM tableX
        WHERE id > @givenID
        ORDER BY id
        LIMIT 3
      )
    UNION ALL
      ( SELECT *, 1
        FROM tableX
        WHERE id <= @givenID      -- just in case of a small table (< 3 rows)
        ORDER BY id
        LIMIT 3
      )
   ) AS t
ORDER BY chck, id
LIMIT 3 ;

Test at SQLfiddle.

The query is basically an improvement on Jehad's answer. It doesn't use a variable (@rank) and the two internal LIMITs will result in improved efficiency on a large table.

Assuming there is an index on id, the two subqueries will use the index and only need to fetch 3 ids and 3 rows each from the table. The external table (t) after the UNION will have a maximum of 6 rows to sort.


Getting the value of 'foo' for the closest previous row with higher 'bar' From this initial data:

select level as foo, mod(ora_hash(level),4) as bar from dual connect by level<9;
/*
FOO                    BAR
---------------------- ----------------------
1                      3
2                      2
3                      3
4                      3
5                      2
6                      0
7                      3
8                      2
*/

I want to get the value of foo, bar, prev_foo for each row, where prev_foo is the value of foo from the closest 'previous' row (defined by ordering on foo which is unique) such that bar in that row is greater than bar in the current row. In other words, I want this result:

/*
FOO                    BAR                    PREV_FOO
---------------------- ---------------------- ----------------------
1                      3
2                      2                      1
3                      3
4                      3
5                      2                      4
6                      0                      5
7                      3
8                      2                      7
*/

How do I achieve this?

with data as (
   select level as foo, mod(ora_hash(level),4) as bar from dual connect by level<9
)
select
  foo,
  bar,
  prev_foo
from
  data
model
      dimension by (foo, bar)
      measures     (cast (null as number) as prev_foo,
                    cast (null as number) as store_foo
                   )
      rules        (
        store_foo[any, any] = cv(foo),
        prev_foo [any, any] = max(store_foo) [foo < cv(foo), bar > cv(bar)]
      );


Can I stop replication of create/drop index statement on one slave of Mysql 5.6? I have this cluster where master and slave uses same indexes but one of the slaves has some extra indexes (large machine which runs some analytics queries).

So I have to maintain two sets of indexes. But this usually creates a problem when I want to recreate an index on master that was already present on the analytics slave.

Can I stop replication of create/drop index statements on one of the slaves so that I can manage them manually?

I don't believe there is a way to ignore specific DDL (or any DDL?).  If creating an existing index, it should throw an error, which you should be able to ignore on your analytics-slave.

Alternatively, don't replicate Index Creations.

set log_bin=0;
create index name on table;
set log_bin=1;

Create a wrapper script to apply to master and slave as needed.


query_cache_type on mysql galera cluster I was wondering could I turn on the query_cache_type for mysql galera cluster of two nodes.

The scenario is :
1. Proxysql infront of two master-master galera(mysql) nodes
2. Inserts are coming through the proxysql
3. On the node2 I have a local PHP application, which is used for generating reports (many selects). There I would like to use the query cache.
As far as I know query_cache should be disabled for galera cluster if you are using loadbalancing. At this particular case the selects are made directly on one of the nodes.

What is your opinion on the matter?

P.S. Or should I look for memcached instead....

No Query Cache on Galera Cluster.  It is much to complex to keep it in sync among the nodes.

Instead, depend on the buffer_pool, etc, to make the queries "fast enough".

In 95+% of Production systems, the Query Cache (or any other caching) is a waste.  Why?  Because data is flowing in so fast that the cache must be all-too-frequently purged.

Perhaps your application is mostly read-only?  That might be a justification for a read cache.

Perhaps your read queries are so slow that you have turned to caching as a 'fix'.  Instead, let's look at the queries.  Often, there is a simple solution (such as a composite index) to speed up reads to the point where caching is unnecessary.


Index will be removed I'm trying to simply change the length of a column within a table through SQL Server Management Studio, but receive a 'Validation Warning':

'MyTable' table
-Warning: The following schema-bound objects will be modified:
 -View 'dbo.MyViewName': indexes and schema binding will be removed.

I remember from experience that if I actually go through with this, the binding will not be readded. In the past I've had to manually write a script to re-add the view. This doesn't seem right - why would it just remove it without re-adding it at the end of the operation?

It makes me think I don't have something set up correctly, or am going about this the wrong way - am I? Or is it 'correct' procedure to have to manually re-add things in this way after table changes are made?

Creating a view with WITH SCHEMABINDING locks the underlying tables and prevents any changes that may change the table schema.
To change the base table you need to remove the SCHEMABINDING option from the objects or drop and recreate these objects once the table has been altered.
To find out if the view is schema bound use below tsql:
select objectproperty(object_id('view_name'), 'IsSchemaBound') as SchemaBound

To remove the with schemabinding option, just alter the view defination :
alter view schemaname.tablename
-- with schemabinding (remove it )
as
select some_column_name
from schema_name.table_name

Then make changes to the table and then alter the view again now with schemabinding
alter view schemaname.tablename
with schemabinding --(adding back )
as
select some_column_name
from schema_name.table_name

from BOL :

SCHEMABINDING :
Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.
Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.


pg_stat_activity entries with no corresponding process on the client If I see an entry in pg_stat_activity for a client_addr:client_port combination where there is no process bound to that port on the indicated client, what does that mean?  Just that the client process exited before the query completed?

You are probably correct that this simply means that the client process exited.  If it exits without cleaning up the connection while its back-end server process is working on a query, then the server process will not notice until it tries to send a message to the client and finds that no one is there.

If it takes a long time to return a tuple (because it has to sort or aggregate before it sends the first one, for example) then the server process can be a dead man walking for a long time.

There was discussion about fixing this, but finding a way to do it in a performant and cross-platform way is not easy.


java.sql.SQLException: Login failed for user 'sa' I am having trouble connecting to my SQL Server database through a jtds connection:

Connection conn = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/MnA;instance=SQLEXPRESS", "sa","");

SQL Server is enabled, TCP/IP is enabled, Mixed server authentication is enabled

Any ideas?

java.sql.SQLException: Login failed for user 'sa'.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
    at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:603)
    at net.sourceforge.jtds.jdbc.ConnectionJDBC2.(ConnectionJDBC2.java:345)
    at net.sourceforge.jtds.jdbc.ConnectionJDBC3.(ConnectionJDBC3.java:50)
    at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at DB.dbConnect(testConnection.java:17)
    at testConnection.main(testConnection.java:7)

Make sure your jdts jar version is 1.2x. If it is not ,download correct version. And the second option is using JDBC jar for connecting MSSQL database.