texts: Failed SQL Login - Source server same as destination cannot locate SPID SQL Server 2014

texts:
Bit of a confusing one here.

We are getting repeated messages in the SQL Server error log as below for a SQL Server instance:

  NT AUTHORITY\SYSTEM
  Reason: Failed to open the explicitly specified database XXX [CLIENT: xx.xx.xxx.xx]

SQL Server Profiler shows nothing nor does sp_who2 or sys.dm_exec_requests.

The client IP Is actually the same SQL Server instance, with the source in the Windows Application Log being MSSQLSERVER - so its trying to connect to itself but failing.

However I cannot find what exactly is occurring. The start of these messages is recorded in the SQL Log. As you can see the database was started up due to a CHECKDB. The CHECKDB completed then the login errors have been there since.

  2015-07-13 22:11:27.790   spid30s Starting up database XXX.
  2015-07-13 22:11:28.480   spid30s CHECKDB for database XXX finished without errors on 2015-07-11 03:36:12.720 (local time). This is an informational message only; no user action is required.
  2015-07-13 22:11:33.780   Logon   Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database XXX. [CLIENT: XX.XX.XXX.XX]
  2015-07-13 22:11:38.750   Logon   Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database XXX. [CLIENT: XX.XX.XXX.XX]

Any info on this would be appreciated!


texts: During a SQL migration/upgrade, is there an intuitive way to mirror the exact file paths of the old install without manually combing through?

texts:
I am performing a migration upgrading from 2008 to 2016 and the requirements are that the file paths need to stay exactly the same as they were, with same drive letters, paths, and folder names due to the application being so static. I don't foresee it taking that much work to do this manually but I feel there must be a better way to mirror the config exactly. I know there will be some differrnt things no matter what inherently from upgrading the instance, but if anyone has any answers that would be very useful.

Thanks!


texts: Has anyone connected to Oracle from SSIS using Oracle Driver ( 12.1.0 ) from VS 2017 Data Tools SSIS

texts:
I have installed Oracle Driver ( 12.1.0 ) and I have VS 2017 Data Tools SSIS . The connection to Oracle Service from local machine via ODBC 64 bit on my machine is working.

Connection to Oracle Server from SSMS 17 via Linked Server is not working

I get 'Architecture Mismatch' error when I create a Oracle OLDEB connection from SSIS.
Any ideas how do I access Oracle from SSMS and SSIS  ..

Thanks,
Shuchi


texts: Collecting DB Used Space (MB) from SCOM on an Always-On DB

texts:
My client has asked for database sizing for capacity planning.

I am trying to use System Center Operations Manager (SCOM), however when I get to the Always-On Cluster, it doesn't seem to display the databases which are part of the Availability Group. The counter is named "DB Used Space (MB)"

I am using the SQL Server 2012 Management Pack version 6.6.4.0.

Is this simply a limitation of SCOM, or is something that should be working?


texts: Insert Data Into NULL Rows from Previous Row (t-SQL)


texts: Mysql Server optimization tips needed! (InnoDB)

texts:
I've recently been trying to optimize our mysql server but I've encountered some pretty serious problems on the way. We're running a game server which is coded in C#. We have two dedicated servers. The game server is connecting our dedicated mysql server which is located in the same network. It's using mysql connection pooling. Current settings for the pooling are:

  minsize=80
  maxsize=400

We've about 300-400 threads connected on peaktime and most of our queries (~70%) are usually updates/inserts. So most of the tables are set to InnoDB. Sometimes we get some massive lag and the game server says that it lost the connection to the mysql server. So we are now trying to figure out is there something wrong with our mysql server configuration. The reason why It's now showing more reads is because our game server hasn't been working properly due mysql problems.

Here's our current my.cnf:
http://pastebin.com/dF0a5ubR

And here's "show engine InnoDB status" output:
http://pastebin.com/fmmad02q

Mysqltuner:
http://pastebin.com/pDTXSGTj

tuning-primer:
http://pastebin.com/89PVGqyC

Our mysql server:

Ram 16gb
Cpu Intel(R) Core(TM) i5-2500 CPU @ 3.30GHz
OS Linux Debian 64-bit
120GB SSD

I'm in need of some suggestions on how to improve our mysql server configuration.


texts: When should I create a primary key on multiple columns?

texts:
I'm interested to know in which case I should create a primary key on multiple columns and why ?
To be more specific about my question, I want to know  what are the advantages and disadvantages of primary key on multiple columns in terms of performance.
I did some research and found this question where someone is saying

Another good question is SHOULD a primary key be multiple columns. :) – Sonny Boy Nov 17 '09 at 17:31
Well, that's a different question, and the answer is NO, NOT EVER, AND
IF YOU DO I WILL HUNT YOU DOWN AND HURT YOU. Just a personal
preference, of course :-) – Stu Dec 4 '09 at 16:46

Despite the joke, there should be some good reason on why one shouldn't use primary key on multiple columns but I couldn't find any answer.

I was wondering about this because I have a concrete problem.
Consider the following table:
CREATE TABLE IF NOT EXISTS `user_travel_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_user` int(10) unsigned NOT NULL,
  `day` date NOT NULL,
  `origin` varchar(8) NOT NULL,
  `destination` varchar(8) NOT NULL,
  `data` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ukey` (`id_user`, `day`, `origin`, `destination`)
)

In this table, for each user I have some data for each day and for each  travel from origin to destination.
I need this table because it's very easy to use WHERE clause on it.
However, the numbers of line in this table are just insane. Usually, a user has ~50 origins and ~100 destinations.
With 30 days filled & 200 users, there's ~30 million lines in my table.
Because each user has around ~150000 lines, I had to create some tools for them to edit/delete/add lines in group.
And, because of these tools, users tends to delete old lines and create a whole bunch of new lines. This leads to a very fast increase of my primary key.
While it is not a problem now, it could be one pretty soon.
So, since I don't use the id on this table, I was wondering if I could do something like :
PRIMARY KEY (`id_user`, `day`, `origin`, `destination`)

This answer is also quite useful but didn't help with my problem


texts:
I'm learning the Innodb buffer pool of mysql 5.7.
And I have some question about that.

What is the relation of buffer pool,buffer block,buffer page and buffer chunk?

How to locate a row in the buffer pool?


texts: Multiple databases through a single connection MongoDB, GridFS

texts:
I want to use GridFS in your project. As the main database using mongodb.

My application can do only one connection to the database (such conditions).

Can I make calls to other databases using the primary as a proxy?


texts:
In Cassandra, everyone always stresses how important data modeling is, and rightfully so. However, when using Elassandra, we have Elasticsearch baked in. How does should change how we think about modeling our Cassandra tables and partitions?
For example, for vanilla Cassandra we need to try to minimize the amount of searching we do across our partitions (see here under header "Basic Goals" > "Rule 2: Minimize The Number Of Partitions Read". Is this still true when we have our data indexed by Elasticsearch? Is it still worth the extra complexity of duplicating our data and managing duplicate tables for the sake of not searching across partitions?
Another example would be choosing our primary key. In Cassandra, normally we cannot run a CQL query using WHERE statements that don't specify all fields from the primary key (unless we use a Cassandra secondary index or override the warnings using ALLOW FILTERING, see here). With Elassandra however, this is [easily overcome by way of Elasticsearch integration, whether by using the Elasticsearch REST API or by using CQL with Elasticsearch queries. If we know we will use Elassandra, does that therefore make a difference in how we choose our primary key?


texts: SQL Server 2017 Express Edition firewall install issue

texts:
Im currently trying to install this in the SQL server 2017 setup, but I get a warning from windows firewall stating: The Windows Firewall is enabled. Make sure the appropriate ports are open to enable remote access. I`ve seen videos where your suppose to change the ports in the management studio, but I heard that your suppose to install SQL before you install management studio.

Thanks.


texts: Questioning a DB Consultant and their recommendations

texts:
We have hired a systems design consultant to help use architect a new CRM system, bringing together several business needs into one system, instead of the several we currently have.

It's the typical project that a company spends a fortune on, and then discovers it doesn't do what it thought it would. I'm trying to make sure that doesn't happen to us.

Could I have some community feedback on some parts of the solution our consultant has recommended? Personally I'm unsure, but I'd defer to their expertise, and to this community's collective knowledge.

Our User Tables

Some of our users have verified accounts with us. Some will have bought tickets through a third party provider. Others will simply reach out to our customer service, and may not be customers yet.

One way of doing this would be to use three tables: VerifiedUsers (data from our verified user accounts -- email, name, post code, etc.), TicketPurchasers (data pulled from our ticket provider - also verified -- email, name, post code, etc.), and Individuals (essentially only an email addresses used to contact our customer service).

Obviously it would be great to make connections between these tables, so we can pull together as much information about our customers/users as possible.

This all seems fine to me so far. Where I get a little unsure is that the consultant has suggested the Individuals table should be the master table.

This table is populated with the most "dirty" data: The least verified data sent from people with the lowest at stake (potentially not even customers, sometimes just so they can rant at our customer service team).

Also, if someone has several email addresses they use to reach out to customer service, how are we supposed to make a reliable trustworthy connection between the TicketPurchasers and VerifiedUser tables? It just seems likely that one person could easily have several rows of data in the Individuals table, and we would never really know.

Question: Does this make sense, even just from a database integrity point of view?

Building our own analytics

The consultant has also recommended we start creating our own analytics. This ostensibly makes sense as well: It would be great to be able to collate every touch point our customers/users make.

The idea would be that a table would be filled with a user_id (presumably taken from the Individuals table), an action_id (to reference the action they took -- eg. "Logged into their account", "Contacted customer service") and a timestamp.

This data could be potentially interesting to look at (although I'd like to sit down and ensure it's useful, too), but after a few quick calculations it seems would could fill 1,000,000 rows in a year.

If we continue growing as a company (as we wish to), this table of analytics could easily hit 10,000,000 rows in a few years, and just keep growing.

This scares me.

For a start, I don't like the idea of a table just growing and growing. It makes me uneasy. Secondly, running live operations on a table of millions of rows (which is what would be required), could be prohibitively resource intensive. (Yes, I guess it depends how much my organisation is prepared to spend on this.)

Question: Are my concerns valid, or is it my inexperience?

Edit: In terms of size, it looks like I need to relax: https://stackoverflow.com/a/1995078/199700 Phew.

Thanks for any help!


texts:
Does somebody know what´s the reason of this stacktrace?

Microsoft SQL Server 2017 (RTM-CU3-GDR) (KB4052987) - 14.0.3015.40 (X64)
Dec 22 2017 16:13:22
Copyright (C) 2017 Microsoft Corporation
Web Edition (64-bit) on Linux (Ubuntu 16.04.3 LTS)

Jan 30 10:18:51 cambalacheBD sqlservr[5445]: #015This program has encountered a fatal error and cannot continue running.
Jan 30 10:18:51 cambalacheBD sqlservr[5445]: The following diagnostic information is available:
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:        Reason: 0x00000006
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:        Status: 0xc0000005
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:       Message: Last Chance
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:       Address: 0x6a446912
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:    Parameters: 0x70373f270
Jan 30 10:18:51 cambalacheBD sqlservr[5445]: #015               #015               #015   Stacktrace: 000000006a4e84f6 000000006a43fb1e 000000006a43f9ca
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:                000000006a518473 000000006a446912
Jan 30 10:18:51 cambalacheBD sqlservr[5445]: #015      Process: 5447 - sqlservr
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:        Thread: 5662 (application thread 0x132c)
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:   Instance Id: c0074e4f-7ce0-43ef-bbc1-c9f5caecdfb6
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:      Crash Id: 1839d4f4-07d4-4a59-b4f8-894c6f19a995
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:   Build stamp: 0fa9143612e88b9b2379174bbdc492bbfae2bec2f0278445d22e11dd124604ab
Jan 30 10:18:51 cambalacheBD sqlservr[5445]: Capturing core dump and information...
Jan 30 10:18:53 cambalacheBD sqlservr[5445]: Hint: You are currently not seeing messages from other users and the system.
Jan 30 10:18:53 cambalacheBD sqlservr[5445]:       Users in the 'systemd-journal' group can see all messages. Pass -q to
Jan 30 10:18:53 cambalacheBD sqlservr[5445]:       turn off this notice.
Jan 30 10:18:53 cambalacheBD sqlservr[5445]: No journal files were opened due to insufficient permissions.
Jan 30 10:18:53 cambalacheBD sqlservr[5445]: Hint: You are currently not seeing messages from other users and the system.
Jan 30 10:18:53 cambalacheBD sqlservr[5445]:       Users in the 'systemd-journal' group can see all messages. Pass -q to
Jan 30 10:18:53 cambalacheBD sqlservr[5445]:       turn off this notice.
Jan 30 10:18:53 cambalacheBD sqlservr[5445]: No journal files were opened due to insufficient permissions.
Jan 30 10:18:53 cambalacheBD sqlservr[5445]: tail: no se puede abrir '/var/log/syslog' para lectura: Permiso denegado
Jan 30 10:18:53 cambalacheBD sqlservr[5445]: Attempting to capture a dump with paldumper
Jan 30 10:18:58 cambalacheBD sqlservr[5445]: Core dump and information are being compressed in the background. When
Jan 30 10:18:58 cambalacheBD sqlservr[5445]: complete, they can be found in the following location:
Jan 30 10:18:58 cambalacheBD sqlservr[5445]:   /var/opt/mssql/log/core.sqlservr.01_30_2018_10_18_51.5447.tbz2
Jan 30 10:18:58 cambalacheBD systemd[1]: mssql-server.service: Main process exited, code=exited, status=1/FAILURE
Jan 30 10:18:58 cambalacheBD systemd[1]: mssql-server.service: Unit entered failed state.
Jan 30 10:18:58 cambalacheBD systemd[1]: mssql-server.service: Failed with result 'exit-code'.
Jan 30 10:18:58 cambalacheBD systemd[1]: mssql-server.service: Service hold-off time over, scheduling restart.
Jan 30 10:18:58 cambalacheBD systemd[1]: Stopped Microsoft SQL Server Database Engine.


texts: Query Store - What are the Security permissions needed to view reports?

texts:
Query store is enabled on 2 of our production databases but I no longer have access to see the Query Store node under the database since my SA privileges were revoked (I had them temporarily for a new server).

I can't find anywhere that describes what minimum permissions are needed for access to query store reports.


texts: Nesting multiple INNER JOIN within LEFT OUTER JOIN in sql on deifferent side of keyword

texts:
I am having some problems with one of the question's answered in the book "SQL FOR MERE MORTALS".

Here is the problem statement

Problem Statement

Here is the Database Structure

The Database Structure

Here is the answer which I am unable to comprehend

DoubtFul Answer

Here is an answer which I am able to understand

Confirmed Answer

Now the problem with UNDERSTANDING the first answer I am having is: We first use LEFT OUTER JOIN for recipe class and recipes. So it selects all recipe class rows but only matching recipes. Perfecty fine as the question is demanding. Lets call this result set R. Now in the next step when we use INNER JOIN to join RecipieIngridients, it should filter out the rows from R in which Recipie ID doesn't match with the Recipe Id in Recipie Ingredients and hence filtering out the related Recipe class and recipe description also(Since it filters out the entire row of R). So this contradicts with the problem which demands all recipieID and RecipieDescription to be displayed from Recipe_Classes Table in this very step only. How can it be correct. Or Am i Missing some concept.

NOTE: this a solved example from SQL FOR MERE MORTALS. So I am assuming both answers must be correct.


texts: Object Reference Not Set ... Debugging Stored Procedure in Visual Studio

texts:
I'm working on updating a stored procedure that is called to update a user. This stored procedure needs to also create a description of what was updated for auditing purposes. However when debugging in Visual Studio I get!

All of my input values are valid, I leave none null, and my update_* procedures without the action_taken value are working. I'm seemingly writing something incorrectly, but I haven't figured out what.

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_user`(
in requestor_id BIGINT(20),
in user_id BIGINT(20),
in department_id BIGINT(20),
in location_id BIGINT(20),
in shift_id BIGINT(20),
in first_name VARCHAR(45),
in last_name VARCHAR(45),
in email VARCHAR(45),
in phone VARCHAR(45),
in security_stamp LONGTEXT
)
BEGIN
    DECLARE queried_username VARCHAR(45);
    DECLARE action_taken VARCHAR(255);
    SET action_taken = '';

    SELECT
        users.username
    INTO
        queried_username
    FROM users
    WHERE users.id=user_id
    LIMIT 1;

    IF (queried_username IS NULL) THEN
    BEGIN
    END;

IF (department_id != users.department_id) THEN
    SET action_taken = CONCAT(action_taken, '[Department Updated]');
END IF;
IF (location_id != users.location_id) THEN
    SET action_taken = CONCAT(action_taken, '[Location Updated]');
END IF;
IF (shift_id != users.shift_id) THEN
    SET action_taken = CONCAT(action_taken, '[Shift Updated]');
END IF;
IF (first_name != users.first_name) THEN
    SET action_taken = CONCAT(action_taken, '[First Name Updated]');
END IF;
IF (last_name != users.last_name) THEN
    SET action_taken = CONCAT(action_taken, '[Last Name Updated]');
END IF;
IF (email != users.email) THEN
    SET action_taken = CONCAT(action_taken, '[Email Updated]');
END IF;
IF (phone != users.phone) THEN
    SET action_taken = CONCAT(action_taken, '[Phone Updated]');
END IF;

    BEGIN
        UPDATE users SET
            users.department_id=department_id,
            users.location_id=location_id,
            users.shift_id=shift_id,
            users.first_name=first_name,
            users.last_name=last_name,
            users.email=email,
            users.phone=phone,
            users.modified=NOW(),
            users.security_stamp=security_stamp
        WHERE users.id=user_id;

        CALL update_audit_log(
            requestor_id,
            user_id,
            null,
            department_id,
            location_id,
            shift_id,
            null,
            "User Administration",
            CONCAT(
                'Modified user (id="',
                user_id,
                '", username="',
                queried_username,
                '") | Action= ',
                action_taken
            )
        );
    END;
    END IF;
END

Any help is very much appreciated!


texts:
I have a large set of files with SQL commands that I run with a SQL*Plus script that uses the @@ operator. I run the script on third-party computers (or even send it by e-mail) so I'd like to make it as self-contained as possible.

Files are encoded as Win-1252. Currently, I need to set the NLS_LANG environment variable before running the script:

C:\> SET NLS_LANG=SPANISH_SPAIN.WE8MSWIN1252

C:\> echo exit | sqlplus foo/bar@//example.com/xe @install.sql

This works but I wonder if it's possible to change the client encoding with a SQL or SQL*Plus command. (Or at least read the value and abort execution if it doesn't match.)


texts: Are there any tools that support Database query submission, review, approval and execution workflow?

texts:
We are a very small setup in the e-commerce space. More than often there is a need to correct data manually in our MySQL database via SQL query execution. There is 1 database administrator in our setup. He is currently getting overwhelmed with the manual query execution and needs to be available every day for some part of the day for query execution.

I am searching for a database query administration tool that can :

Allows an end-user (submitter) to raise a database query execution request
Allows a DBA (reviewer) to review database query and approve/reject a query
Executes approved query on the selected database and emails results back to submitter and reviewer both.

A very simple tool can be built inhouse however the engineering teams bandwidth is not available.


texts: Azure Recovery services vault for sql server machine off

texts: Does azure recovery service vault creates backups of the databases if the machine is off?. Normally the machine is on from the 6am to 8pm from monday to friday, but the idea is to do a full backup at midnight once a week.


texts: Filtering records not present with specific conditions in another table

texts:
I have a table (let's call it extra_info) in MySQL that has various extra information/labels on user accounts:

account_id / data_key
11 / 'test'
11 / 'blah'
12 / 'coupon a'
12 / 'coupon b'
15 / 'whatever'
18 / 'test'

And so on.

I am building reports on other tables but need to filter out records that are present in the extra_info table as 'test'.

So in the example above, records 1-10, 13-14, 16-17 are fine because they are not in extra_info, 11 needs to be filtered out (it is 'test'), 12 is fine (but obviously just need to have it once even though is has 2 labels, 15 is fine (not 'test'), and 18 needs to be removed.

I have done this with subquery in the WHERE clause:

WHERE
    -- filter out test labels
    a.id NOT IN (SELECT ei.account_id
                 FROM extra_info AS ei
                 WHERE ei.data_key = 'test')

This does its job, however, I have a suspicion that a regular join would have worked faster. What's the right syntax for it?

If I just join and filter out the ones with data_key='test', #11 would still be there - so this is not a solution. Grouping by account_id will probably has performance implications and basically same impact as the current code, right?

Any ideas?


texts: Why does PostgreSQL perform a seq scan when comparing a numeric value with a bigint column?

texts:
Given a table vp with column timestamp type bigint, and a btree index on timestamp, why would Postgres ignore the index and run a seq scan on comparison of timestamp with a floating point value, when an index scan would produce identical results?

Integer comparison:

SELECT * FROM vp WHERE vp.timestamp > 1470752584 takes 48 ms:

 Index Scan using vp_ts_idx on vp  (cost=0.57..257.87 rows=2381 width=57) (actual time=0.014..38.669 rows=80323 loops=1)
   Index Cond: ("timestamp" > 1470752584)
 Total runtime: 48.322 ms

Numeric comparison:

SELECT * FROM vp WHERE vp.timestamp > 1470752584.1 takes 103 seconds because it ignores vp_ts_idx and performs a seq scan of the entire table:

  Seq Scan on vp  (cost=0.00..7378353.16 rows=95403915 width=57) (actual time=62625.420..103122.701 rows=98240 loops=1)
   Filter: (("timestamp")::numeric > 1470752584.1)
   Rows Removed by Filter: 285945491
 Total runtime: 103134.333 ms

Context: A query for recent vehicle positions compared timestamp with EXTRACT(EPOCH FROM NOW()) - %s, where %s was the desired number of seconds, without explicitly casting to a bigint. The workaround is to use CAST(EXTRACT(EPOCH FROM NOW()) - %s AS bigint).

Why wouldn't the query planner doesn't do this automatically when the column type is bigint? Is this a bug, or am I not considering some edge case where this behavior would be useful?


texts: Using MySQL 8.0 "Upgrade Checker" on RDS

texts:
The MySQL 8.0 Shell came with an "Upgrade Checker".

Useful to run before upgrading, as you may detect incompatible issues with your table structure / etc.

Running its python version from the shell be like:

util.check_for_server_upgrade()

Unfortunately, when using AWS's RDS, we get the following error:

  SystemError: LogicError: Util.check_for_server_upgrade: The upgrade
  check needs to be performed by user with ALL privileges.

What can be done?


texts: Connect analysis services to oracle database

texts:
I am looking to connect analysis services to an oracle database, and when I hit Process in AS, it gives me a generic Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error] Oracle: ORA-12154: TNS:could not resolve the connect identifier specified.

Things I've tried:

When I create the tables in VS2017, the connection works
When I do a tnsping from the command line, that works
I replaced all of the default tnsnames.ora files in ./app with the one that I know works (its what the TNS_ADMIN env variable is set to

I don't see where the location of the actual utilities used by the service are detailed, so I can't test it using those.

How can I make SSAS use the tnsnames.ora file that I need?


texts:
I have a fairly simple SQL Server 2014 Standard database with one main table. Here's the workload:

I insert a row, do a few updates to it (up to 5 updates), and move on. I always update recent rows. After a few seconds, I never update the row again. I do occasional selects, but my main problem is with the inserts and updates, even when there are no selects happening.

The queries look like:

insert into Table (Id, Field1, Field2, ...) values (@Id, @Field1, @Field2, ...)

update Table set Field3 = @Field3, Field4 = @Field4 where Id = @Id

Id is the only column in the clustered index. There are no other indices on this table. Id is a uniqueidentifier. I changed it from bigint identity after seeing a huge amount of pagelatch_ex waits.

It's doing about 45k requests/sec with the CPU at 90% usage and IO at 50%. This is in AWS where I'm on c3.2xlarge and 2000 IOPS. I don't have much room to grow CPU wise, but I do have another 10x IOPS. What can I do to decrease CPU usage?


texts: Difference between a trigger and a transaction

texts:
I'm implementing movie rating service, so I'm considering about using triggers or transactions.

What I'm considering is inserting movie information into a movie_list and to insert the genre of that movie into movie_genre_list.

In this case, my thinking is to write a trigger to insert the genre when the movie information is inserted.

But, I don't know whether a trigger or a transaction is right for this case. What should I do?


texts:
I have one table named  test.
In that table I have a column named  test_time of type DATE. I want to calculate the time difference between test_time and sysdate in minutes.

select 1440 * (to_date('2009-07-07 22:00', 'YYYY-MM-DD hh24:mi')
- to_date('2009-07-07   19:30', 'YYYY-MM-DD hh24:mi')) diff_hours
from dual;

this one giving time difference in minutes. But I tried this one to my table like

select 1440 * (to_date(test_time, 'YYYY-MM-DD hh24:mi')
- to_date(sysdate, 'YYYY-MM-DD hh24:mi')) diff_hours
from test;

but I'm not able to calculate the time difference in minutes. Kindly help me to solve this problem.


texts: SQL Server 2014 data integration between servers

texts:
Given two MS SQL servers, each with a different database, how does one create an SSIS package such that one can run a complex query that fills a temporary table and then transfer that data as a bulk insert into a permanent table on the other server?

Today this is done by running an external program that generates insert statements for each row, but this is slow and results in a lot of transaction logging.

I have been looking for resources on how to do this online, but as per usual, MS docs are less than helpful.

I have visual studio 2013 and 2015, but am unable to find any SSIS package creation tool, even after install "DACFramework" and "SSDT".


texts: Is Oracle not suited as the database behind websites like Stackoverflow?

texts:
I see from the thread Which tools and technologies build the Stack Exchange Network? that the database specifications are:

  Database SQL Server 2008 R2 running Microsoft Windows Server 2008 Enterprise Edition x64

While this does not necessarily mean that Oracle wont do this job, it does indicate that it is not as good for this part.

How far is it true? What are the reasons?


texts:
In his book "An Introduction to Database Systems" C.J.Date wrote a chapter about the ACID principles called "Dropping ACID". In this chapter he calls Correctness(Consistency) "essentially meaningless" and the other principles at most "a desideratum".

This book is from 2004 and everywhere i go on the internet i see ACID principles as the guidline for RDBMS application programming. Does that mean that Date's ideas to ACID were not accepted? Or is ACID from a theoretical point of view not as mandatory as it seems when reading internet documentation about database programming?

I hope my question makse sense and is in the right forum.


texts: Postgresql versus MySQL insert performance with same indexes

texts:
I'm trying to compare the performance speed differences for an insert of 100,000 records (7 columns) between Postgresql and MySQL:

I get the following insert query speeds for MySQL:

Test 1 (primary key): 58 seconds
Test 2 (primary key, index 1) : 52 seconds
Test 3 (primary key, index 1, index 2): 414 seconds

I get the following insert query speeds for Postgresql:

Test 1 (primary key): 56 seconds
Test 2 (primary key, index 1) : 52 seconds
Test 3 (primary key, index 1, index 2): 60 seconds

Is there a reason why Postgresql doesn't seem to suffer from insert performance when I add more indexes for 100,000 records? Does this have something to do with the type of indexes Postgresql is using compared to MySQL or is it something to do with memory differences.


texts: how to edit a sql server profile template using a text editor like notepad or ssms?

texts:
I am running a trace where I need to filter the behaviour or about 50 users, and some 50 database objects.

I would like, for this purpose to create a sql server profile template and later edit it to add the events I want and the filters I want. How can I achieve that?

I have tried to save my template, but when I then try to open it using a text editor like notepad I get the result below:

On the other hand, if from inside sql server profile, I import my template, and use it to run a trace that is fine, but when I want to add my filters I would need to go through a manual process and there are too many filters and each needs to be added individually.

how can I achieve this by editing the template file?


texts: How to "group" based on consecutive occurrences of an element in a column in postgres

texts:
I have a table that, when ordered, describes a path by point descriptions in each row.
A column tells whether a row is a special (stop) point within that path and is empty otherwise. The path starts and ends with such a row and has many in between. What I want to do is create line segments from the point rows that start at such a row and also end at such a row.

point  stop
  1     yes
  2
  3     yes
  4
  5
  6     yes

I want to call ST_Makeline from PostGIS to turn the ordered points into these line segments. How can I enforce that ST_Makeline, being a "summary" function is only called for the inclusive point groups 1 to 3 and 3 to 6?


texts: Script to merge-replicate two MDF database files?

texts:
I have a program on a computer and on a tablet. The program uses SQL Server Express to interact with a local database (the same information on both) from a .MDF file. So if I plug the tablet into the PC, I have two .MDF files that I want to synchronize.

I want both users to be able to make changes to the version on their device, then I want to get both .MDF files and run a script that merges them.

The database has a GUID primary key, and a DateTime of when it was last changed, so if there are conflicts, I would just take the most recent version.

How should I do this?

Or alternatively, if I'm going about this the wrong way, could you recommend an alternative way to keep the databases of these two machines synchronized without an internet connection?

Thanks in advance.


texts:
ENV:
AIX 7.1
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bitNo RAC, No PDB. (Pretty plain-vanilla)
We recently had an outage of some of our Oracle AIX environments.
After the servers were restarted, I am not able to start Oracle.
Startup just hangs.
SQL> startup

It then lets me type into the session, but won't let me ctl-c out, and it doesn't respond to anything. (quit, exit, etc.)
The dbstart command $ORACLE_HOME/bin/dbstartsimilarly hangs.
It's log file contains SQL> Connected to an idle instance.
What I've done:

I've tried SQLPlus / as sysdba ... startup with various parameters
I've tried the dbstart command
I've looked at all data files to assure they are not corrupted (using dbv command) .
Looked for alert log.xml file. (nothing being produced with today's date)
Looked for trc files.  (nothing being produced with today's date)
Verified that $ORACLE_HOME and $ORACLE_SID are set correctly

Can anyone point me in a direction that might be productive? I need to open a support request with Oracle, but thought I'd start here :-)


texts: I’m trying to start a GridDB node with gs_startnode command. But, there is an error: AD0103: Unable to start node


texts: MySQL InnoDB over a Raw Device in an Active/Passive topology

texts:
We have an Active/Passive topology where there are two x86 complexes with a shared raw storage, where only one of the nodes in a given moment has an access to the shared storage (AKA the active node). In case of a a failover in the active node, the passive node initiates a take over and becomes the active node with an access to the shared storage.  Each node has its own boot device storage with a filesystem. However, the shared storage cannot have a filesystem mounted on it.

We are interested in installing MySQL on both nodes, where its data resides in the shared storage and only the active node is running the server.

MySQL with InnoDB is capable of running on a raw device, and there is also a guide on how to run MySQL over a cluster similar to our topology. However, in the second example, they do have a filesystem mounted on  the shared storage. The filesystem issue raises a major concern:

ib_logfile* still require a file system. So the raw MySQL feature is not exactly fully raw. Please correct me if I'm mistaken. Is there a workaround to store those files in the raw storage? We can save the redo logs (ib_logfile0, ib_logfile1) in the node's boot device and always delete those files before the server is starting (So we won't have old logfiles in case of multiple failovers). However, this might lead to uncommitted transaction to be partially committed in case of a failure in a middle of a transaction, thus contradicting the whole idea of transactions.

Are there any more files/features that might affect the behaviour of mysql in this topology?


texts: Why do two identical strings have a different length but the same binary value?

texts:
I am trying to return a distinct set of department names from a table - nothing special. However, when using the following query duplicates are displayed:

  select distinct department_name
    from dbo.departments;

I also tried:

  select distinct department_name
    from dbo.departments
group by department_name;

So this led me to believe that I might have hidden characters in the values and sure enough, when I checked the length of the strings they returned different values. So, I decided to locate the hidden characters using the function from this question on stack overflow. Strangely enough this only return SPACE. I then tried the following query and it made no difference at all:

select distinct ltrim(rtrim(department_name)) as department_name
  from dbo.departments;

Intrigued, I cast the values to VARBINARY and noticed that they have the exact same binary value, and performing a DISTINCT on the binary value does indeed produce a unique result set.

I have tried casting between VARCHAR and NVARCHAR and to a different collation also (the values are in the same column, within the same database using Latin1_General_CI_AI). I really need to be able to get a distinct set from this table. Does anybody know what might be causing this issue?

UPDATE

After some further investigation, this issue seems to only happen for strings that end with the hex value 0xA000. Any value in the column not ending with this character are fine.

UPDATE 2

If I remove the 0xA000 character from the string I can then apply DISTINCT as normal like so:

DECLARE @binary VARBINARY(8) = 0xA000;
DECLARE @string VARCHAR(8) = CONVERT(VARCHAR(MAX), @binary);
 UPDATE dbo.departments
    SET department_name = REPLACE(department_name, @string, '');

But this won't work long term as users can update this table and I would need to adjust every query to do a replacement in the WHERE clause. I am using a workaround right now which is no more than using MIN to return the entry with the shortest length. This is less than ideal as the problem with distinct is also affecting most of the other language elements as well, for example GROUP BY, ORDER BY, any window functions and COUNT.


texts: Make MySQL print a timestamp at each time it starts

texts:
I am investigating an issue in an environment where MySQL is often restarted, by various actors (backups admins etc).

To make analysis saner, I need to know exactly when MySQL is (re)started.

Here is the current slow.log:

/usr/sbin/mysqld, Version: 5.5.27-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.5.27-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.5.27-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument

As you can see, there is no timestamp, I can only know that MySQL has been started 3 times that day, but I don't know when.

Question: How to make MySQL print a timestamp at each time it starts?

For unfortunate real-world reasons I can't get every single actor to reliably keep  track of when they restart MySQL.


texts: PostgreSQL error: OperationalError: FATAL: role "username" does not exist

texts:
I got this error When I'm trying to run the Code.

I searched the site like this problem

But It couldn't solve my problem.

When I'm trying to create superuser with my username (which is TCOYUKSEL) , It create another one with "tcoyuksel"

ozgur.py Line 9  db = psycopg2.connect("dbname=news")

ozgur.py Line 73 get_pop_articles(), str("views"))

init.py  Line 130    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)  --I didn't do anything with this file--


texts: Can I check what the allocation unit size is on my drives via a query?

texts: Assuming that I do not have access to windows server, can I run a script from SSMS that would allow me to check whether allocation unit size is formatted with 64K?


texts: SQL Replication Subscriber with AlwaysON setup

texts: We have 2 AlwaysON cluster and we want that to set up as a SQL Replication server which is the Always-on Cluster 1 is the Publisher and Always-on Cluster 2 is the SUBSCRIBER, my question is it possible to set up the subscriber which is in an AlwaysON setup? Do I need to configure both nodes as a subscriber? Please give me an idea how to execute that setup


texts: How to query the top TWO and the bottom TWO values at the same time in MySql

texts:
I am able to query the MAX and MIN at the same time but in my case I need the first TWO max and the first TWO min. I am able to query the top two and the bottom two but separately. Like this

SELECT playerName,teamName, scoreChange,category
FROM players order by scoreChange desc limit 0,2

and

SELECT playerName,teamName, scoreChange,category
FROM players order by scoreChange limit 0,2

the following query is working fine but it results only in max and min values .

SELECT playerName,teamName, scoreChange,category
FROM players
WHERE scoreChange = (SELECT MAX(scoreChange) FROM players)
UNION
SELECT playerName,teamName, scoreChange,category
FROM players
WHERE scoreChange = (SELECT MIN(scoreChange) FROM players)


texts: How can I force SQL Server Management Studio to update the IntelliSense cache?

texts:
IntelliSense in SSMS isn't updating unless I shut it down and restart. I've tried disconnecting from the server and reconnecting while the SSMS session is active, but that isn't working.

Steps to recreate:

Create a table
Select something from that table
SSMS displays
squiggly red lines under it with the tip "invalid object name"
Execute the query

How can I force IntelliSense to update without restarting SSMS?


texts:
I want to select last unique rows based on time, so:

 SELECT DISTINCT ON (track) *
 FROM   eco.tracks WHERE id > (SELECT id FROM eco.tracks WHERE time_track < ((SELECT time_track FROM eco.tracks ORDER BY id DESC LIMIT 1)  - INTERVAL '300 seconds')  ORDER BY id DESC LIMIT 1)
 ORDER  BY track, time_track DESC;

It takes 20 seconds, that's too slow.
If I replace id by the actual value, the query takes only 2 ms:

SELECT DISTINCT ON (track) *
FROM   eco.tracks WHERE id > 48000000
ORDER  BY track, time_track DESC;

That subquery alone takes only 2 ms.

SELECT id FROM eco.tracks
WHERE  time_track < ((SELECT time_track FROM eco.tracks ORDER BY id DESC LIMIT 1) - INTERVAL '300 seconds')
ORDER  BY id DESC
LIMIT  1

Why the big difference?


texts: Why is MySQL is creating so many temporary tables on disk?

texts:
Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows

Current max_heap_table_size = 200 M
Current tmp_table_size = 200 M
Of 17158 temp tables, 30% were created on disk

table_open_cache = 125 tables
table_definition_cache = 256 tables
You have a total of 97 tables
You have 125 open tables.
Current table_cache hit rate is 3%

Earlier temp table was "of the 23725 temp tables 38% were created on disk" but I changed max_heap and tmp_table to 200m from 16m and it lowered to 30%..

Configuration:

engine myisam
group_concat_max_len = 32768
key_buffer_size = 3.7 GB,
thread_stack = 256k,
table_cache = 125
query_cache_limit = 1M
query_cache_size = 16M
join_buffer_size = 2.00 M
max_connections = 800

Another system with default configuration is showing "of 23725 temp tables, 1% were created on disk" with the same database.

I tried changing to default on the machine with this issue and it still shows "Of 580 temp tables, 16% were created on disk".

I am using Ubuntu 11.4 64 bit with 48 gb ram. Can any one suggest a solution?

Will changing the db engine from "myisam" to "memory" on tables using "group by" fix this? As explained here: http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caused-by-on-disk-temporary-tables/


texts:
I have a query which was executing slowly earlier. Later I found out that it was not running parallel and this is making the query execution slow.

The query involves a big view and then querying the view with lots of temp tables and sub query.

I removed one UDF from the view and used inline functions and also used a scalar TVF and then it started running fast in parallel execution.

It was going well for few days and one fine day I noticed that the query ran slowly. So I checked the execution plan and found out that the query is executing in serial mode. I checked the plan cache for the query and I saw lots of cached plans involving that view. I deleted plans which are not parallel and then the query runs quickly.

Now I do this every morning to force the query to run parallel.

Additional Details:

SQL Server 2016 Standard
Query gets generated via LINQ-SQL from the dot net application. So ad hoc query.

How can I force the query to run parallel forever?


texts: Postgresql subquery speed much slower than individual queries

texts:
In Postgres 9.1. I am attempting to do a single query to get the timezone of a given postal code. I have data loaded in my database, and I am using PostGIS to store the coordinates of each postal code.  Here are the two queries that I need to do in order to get the data I need:

SELECT coord FROM public.postal_code WHERE postal_code = 'T1K0T4' LIMIT 1

query time: 6ms

This gives me the coordinate of the center of the postal_code area.  Then I use this coordinate to find which timezone it intersects:

SELECT *
FROM public.timezones as tz
WHERE ST_Intersects(ST_GeomFromText('POINT(-112 49)',4326),geom)

query time: 36ms

Now when I combine the 2 queries, the query time jumps to 7-8 seconds.  This is my query:

SELECT *
FROM public.timezones as tz
WHERE ST_Intersects((SELECT coord FROM taduler.postal_code
                     WHERE postal_code = 'T1K0T4' LIMIT 1),geom)

I have a spatial index on the coord column in the postal_code table, and also on the geom column in the timezone table, but it seems like it isn't being used for the subquery.

Does anyone know of a better way to optimize this query?  I have tried several variations of this query, like joining the tables and such, but they have all resulted in the same query speed.

Output from EXPLAIN ANALYZE:

Seq Scan on timezones tz  (cost=8.37..167.47 rows=136 width=335547) (actual time=4606.136..7274.428 rows=1 loops=1)
  Filter: st_intersects($0, (geom)::geography)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.00..8.37 rows=1 width=128) (actual time=0.011..0.011 rows=1 loops=1)
          ->  Index Scan using postal_code_idx on postal_code  (cost=0.00..8.37 rows=1 width=128) (actual time=0.010..0.010 rows=1 loops=1)
                Index Cond: ((postal_code)::text = 'T1K0T4'::text)
Total runtime: 7274.448 ms

EXPLAIN ANALYZE for the following query:

SELECT *
FROM public.timezones as tz
JOIN taduler.postal_code as pc on ST_Intersects(pc.coord, tz.geom)
WHERE pc.postal_code = 'T1K0T4'

Output:

Nested Loop  (cost=0.00..174.61 rows=1 width=335714) (actual time=4870.908..7572.723 rows=1 loops=1)
  Join Filter: ((pc.coord && (tz.geom)::geography) AND (_st_distance(pc.coord, (tz.geom)::geography, 0::double precision, false) < 1e-05::double precision))
  ->  Index Scan using postal_code_idx on postal_code pc  (cost=0.00..8.37 rows=1 width=167) (actual time=0.012..0.019 rows=1 loops=1)
        Index Cond: ((postal_code)::text = 'T1K0T4'::text)
  ->  Seq Scan on timezones tz  (cost=0.00..56.08 rows=408 width=335547) (actual time=0.002..2.795 rows=408 loops=1)
Total runtime: 7572.787 ms


texts: Database design - summary table vs always counting over tables

texts:
Imagine a system where a user can make a whole lot of different actions, like:

Making a purchase
Liking something
Opening a specific view (pretty much every click)
Inviting a friend
...

The actions are stored in tables, example: purchase_log, invite_log etc. With "log", I mean the action history of our users. Not system logs.

The actions need to be acted upon, examples:

Display user statistics (when opening profile view for example)
Counting actions toward giving achievements (by a separate service, performed once a minute or so for all users)

My questions:

Is it bad practice to have a separate table like user_statistics where a simple count is incremented, instead of counting/searching over the xxx_log-tables? This obviously breaks the normalisation rules.
Does the answer change depending on system load / size of application?
Does the answer change when considering transaction locks / concurrency issues?
Does the answer change with SQL vs NoSQL?
Does the answer change with sync vs async systems?


texts:
There are 100 data files, each with 60 fields and over 4 million records. There is a perl program that inserts the records or updates them based on an userdefined _id field. There is also a History collection that stores all values ever written for three fields. A replica set with two servers and an arbiter has been set up. Initially the files were loading into the MongoDB at 45 minutes per file. After around 20 files the speed has dropped considerably. The speed at this time is 20 hours per file. The servers have started slowing down badly. I am unable to use the logout command even quickly.

I have built indexes on the _id field with hashed indexing and for the History collection I have built indexes with id and date field. The number of records at this time in the collections are 4 million for the actual data collection and around 100 million for the History collection. I have two 17 GB RAM processor, of which only around 3.5 gigs are used as per the Mongostat res command. However since the data records are to be inserted date wise sequentially, I cannot exploit parallelism either.

The limits of mongo for the specific scenario have been reached? Is this slowdown to be expected? I have fsynced manually every now and then to ensure files are being written to disk. Is there some other diagnostics that I can run to better explain the situation? Is there a solution to this?

Thanks


texts:
I have a server that gets updated and backed up once a day and processes queries the rest of the day (selects). I'm trying to improve performance of the update process and the selects. I'm not worried at all about redundancy since the database is backed up after it's updated.

I'm currently using RAID0 with 2 SSDs but I think that the controller is limiting the read and write performance. I read that when using multiple files in a single file group then data is written in a stripped way to all files. Should I move from RAID0 to 2 single disks where the data is stored in one file in each disk?


texts: Is it possible for SQL Server to miss rows on a select * where PK = "value" query

texts:
We have just migrated an application to an SQL Server 2008 R2 from SQL Server 2005 and moved from a physical to virtual hosting environment.

I have a very strange error in our application and I was wondering whether anyone has seen a condition that could result in the following condition:

Table structure is very simple

CREATE TABLE [dbo].[TableName](
[PKCol] [nvarchar](8) NOT NULL,
[DataCol] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
(
    [PKCol] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TableName]
WITH CHECK ADD CONSTRAINT [FK_OtherTable] FOREIGN KEY([PKCol])
REFERENCES [dbo].[OtherTable] ([PKCol])
GO

ALTER TABLE [dbo].[TableName] CHECK CONSTRAINT [FK_OtherTable]
GO

Query is:

SELECT PKCol, DataCol FROM TableName
WHERE PKCol = 'SomeString'

This query, for the last 5 years has returned the row for the tested strings.  For a very short period of time (1 second in 2 weeks) this query is returning 0 rows (for a query string that exists 1 second before and 1 second after the query returns zero rows).  If it helps there are a few dozen rows in this table and a search for ANY of them returns zero rows when the "condition" is occurring (as in the select query for different strings returns zero rows).

There are no inserts, deletes or updates being executed against the table.  It is possible that a transaction is occurring on another table where this table is the foreign key.

The query is NOT returning any form of SQL error, it is a valid response from SQL which appears to indicate zero rows, which then affects the behaviour of the application code.

Any clues where I should start to look and/or KB/Hotfixes I should be looking at?


texts: MySQL 5.7 Upgradation - Binary log basename

texts: Recently upgraded to MySQL 5.7 and I am in the process of setting up binary log. I have enabled the binary log but I am not able to set the log_bin_basename and binary log path. It showing it is a global read only variable. I need to separate binary logs from data directory. How to edit the log_bin_basename?. After editing in configuration file, the server not starting. Thanks in advance.


texts: Does disk fragmentation affect DB performance?

texts:
In one of the recent interviews I was asked DB question -
There is a DB with ta table having million rows. Over time will it's performance degrade. Why and how?
My answer was due to fragmentation (We already discussed indexes and other performance impacts large table has). What the interviewer was primarily interested is to know how data is stored in blocks for a DB and how fragmentation really affects the performance.

He also gave an example saying lets say there are 3 records.
1- > 2 -> 3
and lets say 2 gets deleted
will 1->3 or
1 will still point to 2 which on knowing its deleted will move to 3.

I would appreciate if someone who has faced such a similar problem is actual DB and know how it works please do help me understand.


texts:
I use indexes like most developpers do (mostly on... well ! index), but i'm sure there is a lot of subtle way to optimize a database using index. I'm not sure if it is specific to any implementation of a DBMS.

My question is : what are good examples of how to use index (except for basic, obvious cases), and how does a DBMS optimize its database when you specify an index on a table ?


texts: PostgreSQL pg_upgrade --link mode performance issue after upgrade

texts:
Trying to do PostgreSQL upgrade from 9.0 to 9.3. I'm using pg_upgrade with the --link mode since the downtime is very minimal compared to other options like pg_dumpall and pg_upgrade without using --link.

After upgrading and executing an SQL-statement to check execution-time. It is taking nearly 30 minutes the 1st time and for the 2nd time onwards, it is taking 15 mins. The same query executed in 9.0 is not even taking a full 45 seconds.

No of Rows : 150000 records
Using same postgresql.conf file
vaccumdb all : done after upgrade

Is there any thing else I need to configure, to improve the performance?

It doesn't state anywhere in the documentation that pg_upgrade --link will degrade performance.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows on the new cluster                        ok
Deleting files from new pg_clog                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID for new cluster                 ok
Deleting files from new pg_multixact/offsets                ok
Setting oldest multixact ID on new cluster                  ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Adding support functions to new cluster                     ok
Restoring database schemas in the new cluster

mydatabase

Setting minmxid counter in new cluster                      ok
Removing support functions from new cluster                 ok
Adding ".old" suffix to old global/pg_control               ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from /opt/postgres/9.0/data/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.

Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    delete_old_cluster.sh

I got a message like the above, but I couldn't find such files in the postgres folder to execute.

I am using postgresql 9.3.5 from openscg community

**postgresql.conf changes**

shared_buffers = 4GB
work_mem = 128MB
maintenance_work_mem = 256MB
effective_cache_size = 12GB


texts: Postgres Query to retrieve locations within a given distance

texts:
I have the following sql query that works fine in MYSQL but it gives me an error in Postgres:

SELECT * ,
        ( 3959 * acos( cos( radians('13.1') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('53.1') ) + sin( radians('13.1') ) * sin(radians(lat)) ) ) AS distance
FROM person
HAVING distance < 10
ORDER BY distance LIMIT 20

**ERROR**:  column "distance" does not exist
LINE 1: ...(radians(lat)) ) ) AS distance FROM person HAVING distance <...


texts: How to split a wide table in Production?

texts:
Due to some changing business requirements, I will be needing to split a large table in the near future to support new business requirements allowing a 1<=>M relationship from a table that was designed for a 1<=>1 relationship.

The challenge is that this table is fairly large and continuously used.  Somewhere around 150 columns, and 50 million rows.

The approach I'm considering is as follows: (the table is named "Customers")

Create two new tables, (Customers_1, and Customers_Many representing the new table structure.
Copy all the data across, as appropriate.
Create a view, selecting from Customers_1 and Customers_M that duplicates the Customers table exactly.
Create a trigger to handle inserts / updates on the view to update the parent tables.
Update foreign keys to point to the new tables.
Use sp_rename to Swap the table and the view.
Change code in subsequent iterations to point to the new tables, instead of the view.
(Eventually) Drop the view, drop the old table.

Can anyone recommend any challenges with this approach, validate this approach, or offer different approaches?


texts: Does setting Query Optimizer Fixes to ON enable Scalar UDF Inlining on a database with compatibility level 140?

texts: Azure SQL Database Managed Instance. Current Compatibility Level is set to 140 to match on-prem SQL Server 2017 test and development server. I just read this article on Scalar UDF Inlining and was curious if setting Query Optimizer Fixes to ON would enable this feature? I have read several more articles on the topic but none of them address this question.


texts: I am getting a failed login message in the SQL Server error logs however, nothing actually failed?

texts:
I have a server which runs third party software (called Visualcut) to email Crystal Reports to our users.

When I log onto the Visualcut Server, I can open the software and then preview a report. This will ask me to enter a username and password (for an Microsoft SQL account) and then the report will display with no errors however, whenever I do this, an error is logged in the SQL Server error logs as follows:

  10/26/2015 08:15:39 Logon Unknown Login failed for user ''. Reason: An
  attempt to login using SQL authentication failed. Server is configured
  for Windows authentication only. [CLIENT: 10.10.10.26]

  10/26/2015 08:15:39 Logon Unknown Error: 18456 Severity: 14
  State: 58.

I don't understand why the report would authenticate seemingly without any issues however, the log file states otherwise.

I would like to add that our SQL Server is configured to use mixed authentication.


texts: Calculate the growth of deltas in SQL Server

texts:
Is there a way to calculate the growth per year of deltas in database?

I would like to know if my current server is enough or should I switch to bigger server and required to check the growth over the years, is it doable?

Thanks.


texts: Please explain the difference between row and page compression

texts: I'm trying to determine which to apply and when is the appropriate time to apply compression. I'm posting this question to get insight from this community.  I have read several articles, but wanted to have a place where this addressed in DB Administrators.


texts:
Is it possible to create a table from a record? For example:

DO $$
DECLARE
  v1 RECORD;
BEGIN
  SELECT 1 AS c INTO v1;
  CREATE TEMP TABLE temp AS
    SELECT v1.*;
END;
$$
LANGUAGE 'plpgsql';

This returns

ERROR:  record type has not been registered
CONTEXT:  SQL statement "CREATE TEMP TABLE temp AS
    SELECT v1.*"
PL/pgSQL function inline_code_block line 6 at SQL statement

I tried more complex solutions such as using hstore to loop through the record keys and create a custom command but then I would not be able to use the same data types.


texts: download mysql slow query log on google cloud sql

texts:
I'm having a difficult time locating the log file to download.  When running
show global variables like '%slow%';

I can see it is located
/mysql/logs/slow.log

When I log into the instance though that path doesn't exist.  Any ideas?
This is through google cloud sql and using mysql as the db.


texts: Will Entity Framework and/or Linq-To-SQL queries show up as "Adhoc Queries" in SSMS' "Server Dashboard" Report?

texts:
Its pretty much all in the title. I have a SQL Server which is showing that 90%+ of CPU Usage and Logical IO Performed is due to "Adhoc Queries" The rest rest of the usage shows up under the actual DB name. This is all from the "Server Dashboard" report.

I have a few databases on this server which are accessed directly by vendor applications of which I have no control. They are very low usage in general; I have two databases that are accessed and used by internal applications written in Entity Framework and Linq-To-SQL. I'm wondering if that is why my "Adhoc Queries" are showing such high use instead of the names of my internal databases.

The reason I'm asking is because I've been noticing somewhat degregated performance and the CPU on my server is running 95% on sqlserver.exe process for extended periods of time, with minimal "down spikes" to 10-15% use.


texts: Make sure slave has finished reading from master

texts:
I am planning to have a master-slave system in which occasionally the master and slave will reverse roles.  To do this, I will stop further updates to master, and redirect traffic to the slave.  But, before I make the slave the new master, I need to make sure the slave has finished reading all the updates from the old master.  How can I do that?  Is running "flush logs" on the master sufficient?

An solution that can be readily scripted would be preferred.


texts: I have a column which is unique and when I add Αγάθη and Αγαθή (which are different) it says #1062 - Duplicate entry 'Î‘Î³Î±Î¸Î®' for key 'word'. My collation is set to utf8_general_ci. Is there any better collation?


texts:
I am not very experienced with sql queries and looking for an efficient way to perform the same WHERE IN condition on multiple columns.

More specifically, i'm doing the following:

SELECT a, b
FROM links
WHERE a IN (90696, 110083, ... 147455)
  OR b IN (90696, 110083, ... 147455)

Since both IN-lists are identical and can contain several hundreds of items, the above query just doesn't feel very right to me.

If it helps (but I don't think so, actually):

the following condition is always met: row[x].a < row[x].b for all rows x in Table links.
Im using MySQL InnoDB. Yet, I'm open to different database solutions if would significantly improve performance.
Currently, the items of the WHERE-IN clause are computed. However, I could also acquire them by an additional query (which would actually be the union of two queries).

Edit:

The IN-list could be constructed by

SELECT i_id as l FROM t_i WHERE id=x
UNION ALL
SELECT o_id as l FROM t_o WHERE id=x


texts:
I am using MySQL with PHP for printing details from a database. I tried to make a new user, which was successful, although I get an error from MySQL when I tried to fetch data from it. It showed me this error:

       ( ! ) Warning: mysqli::mysqli(): (HY000/1045): Access denied for user 'root'@'localhost' (using password: NO) in D:\wamp\www\index_feed\index_feed.php on line 8
Call Stack
#   Time    Memory  Function    Location
1   0.0010  238200  {main}( )   ..\index.php:0
2   0.0024  289384  require_once( 'D:\wamp\www\index_feed\index_feed.php' ) ..\index.php:48
3   0.0024  289672  feed_print( )   ..\index_feed.php:136
4   0.0024  291264  mysqli ( )  ..\index_feed.php:8

The dilemma here is that I am not even using a root user and, yes, I do have a password for root, but in the error it is showing using password : NO. I am totally stuck here, I think it's a problem with using password.

I have tried using the root user for fetching data, but it still doesn't work. The same error showed up.


texts: Run PS-Script after job is done on 3 instances - SQL Server

texts:
I have backup jobs running on a server with 3 instances.

Link to script: https://ola.hallengren.com/sql-server-backup.html

The solution I found for the other 2 servers with only once instance was to create a job which has the backup scripts as steps via TSQL and then simply runs the PowerShell script.

Is there a way to do this on a multi-instance server?

Server is running SQL Server 2012


texts:
I'am facing some difficulties regarding my Sql Backup method, i am not sure if my method is correct, i am using a backup method described below;

Created a Full backup named Xbackup in Maintenance Plans, which backups in X External Drive with the extension of *.bak.
Once in a day Job created Automatically in Sql Server Agent .
Once in a day backup software transfers the bak files to a NAS device.
If *.bak files are older than 2 days a job deletes them on server
If *.bak files are older than 5 days a job deletes them on Nas Device

The difficulties are;

There are 7 database to backup and 7 *.bak files to copy, it is huge
330 GB to back up and transfer to NAS everyday and it is increasing up to 5
gb everyday.
Some times backups does not transfered correctly
it takes a lot of time to backup and transfer it to Nas device

Not: Nas device was shutting down when it finishes the transfer and Turn on when transfer time comes, but nowadays i canceled this task because of increasing file size i couldn't figure it out how long it takes to backup .bak files and how long Nas device has stay on in order to finish transfer.

What would be your suggestions?


texts: Limit CPU usage without Resource Governor

texts:
We have an SQL Server 2008R2 Standard Edition with several databases belonging to different applications on a 16 core server.

One recently introduced application is regularly executing expensive queries that lead to 100% CPU usage. Of course the other applications are reporting performance issues.

The Resource Governor seems like a suitable tool to put the reins on the rogue application, unfortunately it is only available in the Enterprise Edition.

Since the other applications are rather simple, I tried to get the problem under control by reducing the "max degree of parallelism" of the instance, so that a single query can not bring down everything. While that succeeded in keeping the CPU load at 50%, it did surprisingly nothing to keep other applications from being bogged down.

Now we have decided to move the databases for the new application to a dedicated instance, but what would be the best configuration for this instance? Should I keep the MAXDOP-setting, use a CPU-affinity mask or is there another option to limit CPU usage that I am not aware of?


texts: PostgreSQL Streaming Replication - corruption on master

texts:
We have PostgreSQL 9.3.9 streaming replication between Master DB and Slave DB.
Is there issues can happen on Master that can cause persistency problems or full failure of Master and Slave DB both while replicating? Am I always will get persistant and ready to work or restore Slave DB while replicating?


texts:
The following image contains all of the charts  from the Status tab in MMS.

How does a noob looking at these charts navigate these waters, so to speak?

The mongo box is a standalone installation but i have no db.stats() for this period.

The questions i have is there were like ~220 update req/s (if i read the OpCounters chart right) which seems to increase the lock %.

Is 6MB high for the writeToDataFiles green line in Journal Stats?

Is 100 'units' high for the accessesNotInMemory or is that showing we have something not non-indexed that should be since all this is butte ing on us?

I'll even take RTFM if you give me a link to the manual that corealates all these charts. I want to know how to read the tea leaves.


texts: Are duplicates ever acceptable in a database

texts:
A warehouse system I am working on allows inwards consignments [type = 0] and outwards consignments [type = 1].  The user selects multiple products for each consignment and then multiple warehouse locations for each product.

The system needs to keep track of stock coming in and allow reporting and checking for available stock etc.

When a consignment goes out, the system must select which products to use (based on first in first out or other custom logic).  Products from an inwards consignment can go out on multiple outwards consignments.  Products from an outwards consignment can come from multiple inwards consignments.

I have designed the following table structure:

products [id, description]

locations [id, description]

consignments [id, description, type]

consignment_products [id, consignment_id, product_id]

consignment_product_locations [id, consignment_product_id, qty, notes]

consignment_outwards [id, consignment_product_location_id, consignment_product_id, qty]

All the tables hold the info for both inwards and outwards consignments.

consignment_product_locations.qty = the qty that the user selected for the consignment

consignment_outwards.qty - the breakdown (detail) of the assigned inwards consignment product quantities.

Is this the best way to design the database?
consignment_product_locations.qty is used for inwards consignments.  However, for outwards consignments, it will always be the sum of the matching consignment_outwards.qty values.  Is it acceptable to store a calculated field in this case to maintain consistency with inwards consignments and to aid reporting?  Or would it be better to fill in the quantity for inwards consignments but leave the qty null for outwards consignments.
Would it be better to split consignment_product_locations into 2:
consignment_product_locations_inwards [ id, consignment_product_id, qty, notes]
consignment_product_locations_outwards [ id, consignment_product_id, notes]

(NB: this would make reporting harder)


texts: SQL Server 2000 : Upgrade from Personal Edition to Enterprise Edition

texts:
I've never tried this before but this is what I'm attempting to do :

Backup all the databases
Copy current master.mdf and master.ldf to another location (for later purpose)
Uninstall SQL Server 2000 Personal Edition
Install SQL Server 2000 Enterprise Edition (SP4)
Stop the newly installed server, and replace the master.mdf and master.ldf with those old two files above
Restart server

I've done this before, but only with Enterprise Edition, after reinstalling SQL Server I'll just have to replace the master file and everything is fine.

Will it work in this case ?


texts: Sql Server Database 2008 R2 is in recovery mode still after 16 hours

texts:
Sql Server 2008 R2 Database  is in recovery mode still after 16 hours for one of the database which is is Simple Recovery model.

The data file is 200 GB and Log file 90 GB.

We have to do Crash Recovery(Restarting the sql server engine) and the particular Database of size as mentioned above gets in recovery mode for past 16 hours.

There are other 5 database of equal or more size, but these other 5 database recovers faster. the reason why we do stopping the service is due to one of the Session is not getting killed in the aforesaid database, even we tried to kill it explicitly.

This session is blocking and causes long operation for backups for the above mentioned database. we checked the VLF's it has as many as 719 records(vlf file) with lot more marked as inactive and everytime the log usage is above 99% near to 100% (DBCC SQLPERF(logspace)).

we tried to increase the autogrowth to 30% for the log, but to no avail, we are unable to the kill the Session, unable to the backup due to extended longer duration and unable to the put the database online and ready(after restarting the 2008 R2 sql service).

What needs to be done for :

to Kill that particular Session which is creating the block ( Header
Block set to 1 in activity monitor)?
how do i resize the log file and see to it that it has good enough space?
is it possible to delete the inactive VLF?
how do i make the database to become online faster ( after restart of 2008 R2 service)?


texts: Here is the story: We want to do side by side SQL upgrade from 2016 to 2019 with a new windows of 2016. My SA wants to robocopy all drives except drive C from the old server to the new server. Do I get any issues with live or offline databases (data and log files) after transfer? will they get corrupted?  Please provide me your ideas.


texts: Shell Script to kill slow running Mongo Queries

texts: I need to create a shell script that finds and kills long running mongo queries more than a specified threshold e.g. 15sec I plan to use db.currentOp(); and db.killOp(); to find and kill queries but I do not have much experience with shell scripts. how do I do this?


texts:
I am trying to draw an generic entity-relationship diagram (ERD) to model a software object (Let's say a XML file). This XML file has references to other XML files. Also, if a XML file (say "file-A") is referred by another XML file (say "file-B"), then "file-B" cannot referred by other XML files.

If I need to model those relationships in an ERD, the source entity and the target entity becomes same. I want to know whether this is acceptable or not? Because, if it is acceptable, specifying cardinality becomes an issue as different readers interpret the cardinality in different ways:

Eg - an XML file can have references to zero or more XML files
Eg - an xml file can have only one reference to another XML file


texts: Configuring Custom Hierarchical Reporting Roles

texts:
I need to set up some custom database roles within Microsoft SQL Server 2014 for reporting purposes. I need to set up the following roles:

Administrative Reporting Role

Read-only access to some subset "A" of all tables/views that will not contain tables with various sensitive info (hence it cannot simply be the db_datareader role).
Can update Standard Reporting Role, granting/removing access to tables within subset "A"

Standard Reporting Role

Read-only access to some subset "B" of all tables in subset "A"
Can never gain access to tables outside of subset "A"
Can never gain higher level privileges than Administrative Reporting Role has

So far, for the Administrative Reporting Role, I simply created a new database role, set its owner to db_datareader, and then used the Securables section of the UI to grant Select access to a few tables. I then tried creating a new database role with the Administrative Reporting Role as the owner, but when I assigned test users to the role, the user in the Administrative Role was unable to grant access to the user in the Standard Role. Is there a way to achieve this, or perhaps a built-in feature I should be using instead?

Additionally, when granting access to tables, is there a good way to add a lot at once (I'm guessing via query as opposed to the UI)?


texts:
I created a Relational Database Service (RDS) with Oracle database at Amazon Web Services (aws.amazon.com). However, when I attempt to connect to the database (with SQLDeveloper or Navicat), I am getting:

ORA-12545: Connect failed because target host or object does not exist

Other databases in RDS work (I tested MySQL and PostgreSQL). Based on the error message I know that I can connect to the database and it's not a firewall issue.

When I connect to the database with:

nc -zv oracle3.cocvztbjagzq.us-west-2.rds.amazonaws.com 1521

I am getting:

Connection to oracle3.cocvztbjagzq.us-west-2.rds.amazonaws.com port 1521 [tcp/ncube-lm] succeeded!

I tried to reboot the DB instance and wait for a few hours. But that neither help.

Do you have an idea how to fix the problem?

Note: RDS from Amazon is provided as a service - I do not have access to the operation system.


texts:
We have a SQL server concurrency issue we are facing and would like some ideas on how to solve it. There are several procedures we have written the contain INSERT/UPDATE statements on tables in a Set based manner. I will try to illustrate an example here

Lets assume that the isolation level set by the AppServer over the JDBC connection is READ COMMITTED

DELETE FROM TargetAmountTable
/*whole bunch of JOINS on other tables*/
WHERE SourceAmountSetTable.isModified = 1

INSERT INTO TargetAmountSetTable
SELECT /**whole bunch of JOINS**/
WHERE SourceAmountSetTable.isModified = 1

UPDATE TargetAmountSetTable
SET TargetAmountSetTable.isModified = 1
/*whole bunch of JOINS*/
WHERE SourceAmountSetTable.isModified = 1

INSERT INTO TargetAmountTable
SELECT /*whole bunch of JOINS*/
WHERE SourceAmountSetTable.isModified = 1

UPDATE SourceAmountSetTable
SET SourceAmountSetTable.isModified = 0
FROM /*whole bunch of JOINS*/
WHERE SourceAmountSetTable.isModified = 1

So the problem we are facing is, when there are multiple processes trying to do the INSERT on the [TargetAmountTable], duplicate rows are added since the last UPDATE statement is not yet executed

Any ideas on how to resolve this?


texts: How to include empty string in string_agg?

texts:
I've done a groklearning exercise up until the point of:

  Make sure to list all properties, even if there is some unknown
  address information; replace any missing parts with an empty string

Could someone guide me on how to make this work?

SELECT D.council_name, string_agg( P.house_number || ' ' || P.street  || ' ' ||    L.locality,  ', ' ORDER BY S.contract_date) AS commercial_properties
FROM Sales S, Properties P, Districts D, Localities L
WHERE S.property_id = P.property_id
AND P.locality_id = L.locality_id
AND P.district_code = D.district_code
AND P.primary_purpose = 'COMMERCIAL'
AND S.contract_date BETWEEN '2017-01-01' AND '2017-12-31'

GROUP BY D.council_name;

But it prints:

+-----------------+----------------------------------------------------------------------------------------+
|  council_name   |                                 commercial_properties                                  |
+-----------------+----------------------------------------------------------------------------------------+
| COOTAMUNDRA     | 218 PARKER ST COOTAMUNDRA                                                              |
| COWRA           | 6 VAUX ST COWRA                                                                        |
| GREAT LAKES     | 102 MANNING ST TUNCURRY                                                                |
| LAKE MACQUARIE  | 214 MACQUARIE RD WARNERS BAY, 19 NELSON RD CARDIFF, 21 NELSON RD CARDIFF               |
| NEWCASTLE       | 8 RURAL DR SANDGATE                                                                    |
| PARRAMATTA      | 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 ROSS ST PARRAMATTA |
| PORT STEPHENS   | 17 STOCKTON ST NELSON BAY                                                              |
| THE HILLS SHIRE | 24 LEXINGTON DR BELLA VISTA                                                            |
| WYONG           | 172 PACIFIC HWY TUGGERAH, 20 AMSTERDAM CCT WYONG                                       |
+-----------------+----------------------------------------------------------------------------------------+

Instead of:

+-----------------+----------------------------------------------------------------------------------------+
|  council_name   |                                 commercial_properties                                  |
+-----------------+----------------------------------------------------------------------------------------+
| COOTAMUNDRA     | 218 PARKER ST COOTAMUNDRA                                                              |
| COWRA           | 6 VAUX ST COWRA                                                                        |
| GREAT LAKES     | 102 MANNING ST TUNCURRY, MACWOOD RD SMITHS LAKE                                        |
| LAKE MACQUARIE  | 214 MACQUARIE RD WARNERS BAY, 19 NELSON RD CARDIFF, 21 NELSON RD CARDIFF               |
| NEWCASTLE       | 8 RURAL DR SANDGATE                                                                    |
| PARRAMATTA      | 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 ROSS ST PARRAMATTA |
| PORT STEPHENS   | 17 STOCKTON ST NELSON BAY                                                              |
| THE HILLS SHIRE | 24 LEXINGTON DR BELLA VISTA                                                            |
| WYONG           | 172 PACIFIC HWY TUGGERAH, 20 AMSTERDAM CCT WYONG                                       |
+-----------------+----------------------------------------------------------------------------------------+

It doesn't print "MACWOOD RD SMITHS LAKE" as it is missing the house_number.  How can I add the missing parts to the results?


texts:
I have a database in a server in Africa, and need a daily sync\restore to a server in Europe.

My first idea was to sync the data between the 2 databases, but the database has more than 1000 tables, and is not well built, so the software I've tested didn't work great.

For what I've read the best option is to backup the database in one server and restore it to the remote server.

Is this the best solution?

Is Redgate Backup Pro the best tool for the task?

I've been playing with it and it seems to have everything I need: backup\restore scheduling, compression, etc.

Need some advice on this. Thanks in advance :)


texts: How to run multiple ST_DWITHINs/ ST_INTERSECTS within a single WHERE clause

texts:
PostgreSQL 11, on Windows 10.

I'm working with a post-gis enabled database.
I want to UPDATE a large polygon table (100,000+ rows) WHERE the geometry of that host table intersects the geometry of another table (table a) AND another table (table b). 'Table a' also has a WHERE field = value attribute filter placed on it.

Using this statement, it takes an indefinte amount of time, seemlingly never to complete (I even left it overnight and it still didn't finish):

UPDATE schema1.polygontable poly
SET ref_id = '5z'
FROM schema2.tablea a, schema3.tableb b
WHERE (ST_DWITHIN(a.geom, poly.geom,0) AND (a.type = 'Ground')) OR (ST_DWITHIN(b.geom, poly.geom,0));

However, If I split the query at the OR clause, the now separate 2 functions take 5 seconds and 1 second respectively:

UPDATE schema1.polygontable poly
SET ref_id = '5z'
FROM schema2.tablea a
WHERE ST_DWITHIN(a.geom, poly.geom,0) AND (a.type = 'Ground');

and

UPDATE schema1.polygontable poly
SET ref_id = '5z'
FROM schema3.tableb b
WHERE ST_DWITHIN(b.geom, poly.geom,0);

Nb: all three tables have a spatial index each. And they are all hosted on an Amazon AWS instance.

So, why does my query freeze/hang when I combine the functions? My syntax looks fine I think.


texts: How can I create an ad-hoc domain user on SQL Server

texts:
Okay, so here's the problem. I have an application I need to install and run locally for testing and debugging. The application has been designed to run on a domain and as such expects to log into the database using domain credentials.

I can't modify the section of code that handles the logins without a serious amount of work which I don't have time for this week, nor do I have any ability to add the machine hosting the database to the domain (contractors machines aren't allowed to be added to the domain under the I.T. policy).

So I'm in a bit of a bind. I cannot use the CREATE LOGIN [DOMAIN\User] From Windows routine as I get the following error:

Windows NT user or group 'DOMAIN\User' not found. Check the name again.

I can't just insert into the relevant security tables: sys.server_principals table,  sys.SysLogins etc. because

Ad hoc updates to system catalogs are not allowed.

Is there any way I can back door my local SQL Server installation to accept these user credentials from the application without being attached to the domain?

Of course adding the machine to the domain and logging in as a domain user would resolve this issue in a couple of minutes, but political issues prevent that from happening.


texts: How big of an impact does NUMA really have?

texts:
I was reading up on Non-Uniform Memory Access (NUMA) and I was wondering how much of a difference it can really make?  Another way to word that, what level of throughput is required before this becomes beneficial?

Also, what type of server do you need to look for when you want a system with multiple system busses?


texts: What is the advantages and disadvantages of maintaining one ORACLE_HOME?

texts:
I am a little confused about why a DBA would want to maintain one ORACLE_HOME? I can see no advantages.

Having multiple homes would allow multiple versions of Oracle installed.


texts: how to execute unix commands within nzsql?

texts:
I'm converting a script from Teradata to Netezza, and I'm stuck at the portion where BTEQ uses the functionality of the OS to check if a file contains any data. If it does, it is deleted. I want to perform the same function in nzsql.

Can anyone please help?


texts: MySQL cycling between periods of high I/O and high lock contention

texts:
Starting about 2 days ago, my CloudSQL DB started having this periodic cycle of high latency. The time between these latency bursts vary, from 30 minutes to 50 minutes.

I finally set up a PMM instance today to get a better look at things. I see that the periods of high latency coincides with high active thread count and a lot of lock activity. CloudSql metrics show that these high latency periods also correlate with a reduction in the buffer pool dirty page count

Our workfloads are pretty consistent, certainly we have nothing running that's cyclic with a varying period. Changing some MySQL configs (io threads and lru scan depth) seem to have some impact on this period.

I uploaded some PMM screenshots here, of I/O, locks, and flushing over the last couple of hours. The pattern is pretty apparent.

Data I/O

Locks

Flushing

What is going on? I assume there is something going on under the hood of MySQL operating normally while using up some resource, then going through this period of high latency while trying to free up more of that resource to be able to operate normally again, but I can't piece together exactly what this is.

For more context: this started happening when we added a couple columns (unindexed) to a table. Since then we've reversed that ALTER TABLE and restarted the DB a couple of times to apply config changes. I'm interested both in finding a root cause and in applying a short term band aid to decrease the effect of these spikes.

Thanks!


texts:
I have a "production" user and database on which I want to give right access on all the users belonging to a production group.

Unfortunately, I don't succeed, keeping the access denied error.

Can you have a look at the example script below and tell me what I missed (production user/database is toto and production group is toto_group)?

Some comments:

The use case is: The data loading is already in production. Developpers (titi, etc.) now only have to read the data, and we want them to read it from the production database in order to avoid having to duplicate everything.
I've initially tried on Linux, but I did the test script on windows. Same results.
I'm using postgresql 9.5
I have also tried to grant the access to the tables instead of the schema. Same results.

Thanks a lot.

\c postgres postgres

DROP DATABASE IF EXISTS toto;
DROP TABLESPACE IF EXISTS toto_ts;
DROP ROLE IF EXISTS toto;
DROP DATABASE IF EXISTS titi;
DROP TABLESPACE IF EXISTS titi_ts;
DROP ROLE IF EXISTS titi;
DROP ROLE IF EXISTS toto_group;

CREATE ROLE toto_group NOLOGIN;

CREATE ROLE toto WITH PASSWORD 'toto' LOGIN;
CREATE TABLESPACE toto_ts OWNER toto LOCATION 'd:/pg_ts/toto';
CREATE DATABASE toto TABLESPACE=toto_ts TEMPLATE=template0 OWNER=toto;
GRANT toto_group TO toto;

CREATE ROLE titi WITH PASSWORD 'titi' LOGIN;
CREATE TABLESPACE titi_ts OWNER titi LOCATION 'd:/pg_ts/titi';
CREATE DATABASE titi TABLESPACE=titi_ts TEMPLATE=template0 OWNER=titi;
GRANT toto_group TO titi;

COMMIT;

\c toto

DROP SCHEMA public;
CREATE SCHEMA s_test;
SET SCHEMA 's_test';
GRANT SELECT ON ALL TABLES IN SCHEMA s_test TO toto_group;
CREATE TABLE t_test (id INTEGER);

COMMIT;

\c titi

DROP SCHEMA public;
CREATE SCHEMA s_test;
SET SCHEMA 's_test';
GRANT SELECT ON ALL TABLES IN SCHEMA s_test TO toto_group;
CREATE TABLE t_test (id INTEGER);

COMMIT;


texts: Database table as a queue, order by another table's column

texts:
I have two tables. One table serves as the queue from which multiple users simultaneously dequeue items one at a time, by flagging the item's row. The other table is joined and used for ordering. It is required that no two users will get the same item from the queue and the queue will not block users, so if user a is busy getting the top item, user b should be able to get the next available item.

The problem is, and i can reproduce, simultaneous dequeue will result in exactly one user to succeed; the others are left empty handed. This happens even when there are multiple candidate items in the queue, so the queue is not empty.

If i remove the order by clause, everything works fine, users can simultaneously dequeue, without blocking each other.

How can i solve the problem, i need the ordering and i need the queue to be non-blocking.

Following are the two tables:

CREATE TABLE DBO.QUEUE
(
  QUEUEID    INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
  SOMEACTION VARCHAR(100)
);
GO

DECLARE  @counter INT
SELECT   @counter = 1
WHILE (@counter <= 10)
BEGIN
    INSERT INTO DBO.QUEUE (SOMEACTION)
    SELECT 'some action ' + CAST(@counter AS VARCHAR);
    SELECT @counter = @counter + 1
END

CREATE TABLE DBO.QUEUE_ORDERING
(
    QUEUEID    INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    ORDERING_COLUMN INT
);
GO

DECLARE  @counter INT
SELECT   @counter = 1
WHILE (@counter <= 10)
BEGIN
    INSERT INTO DBO.QUEUE_ORDERING (ORDERING_COLUMN)
    SELECT @counter;
    SELECT @counter = @counter + 1
END

And the testing script is as follows , running them from multiple sessions will reproduce my problem:

BEGIN TRAN TRAN1
DECLARE @QUEUEID INT;
;WITH QUEUE_CTE AS
(
    SELECT   TOP(1) Q.QUEUEID, Q.SOMEACTION
    FROM     DBO.QUEUE Q WITH (ROWLOCK,READPAST, UPDLOCK)
    JOIN     DBO.QUEUE_ORDERING QR WITH (NOLOCK)
    ON       QR.QUEUEID = Q.QUEUEID
    ORDER BY QR.ORDERING_COLUMN DESC
)

SELECT @QUEUEID = QUEUEID FROM QUEUE_CTE
PRINT 'processing item # ' + CAST(@QUEUEID AS VARCHAR)

WAITFOR DELAY '00:00:05'
COMMIT

Edit:

I've traced down the issue to indices. If i order by the column that is part of the clustered index, then only the relevant row is locked. Otherwise, all rows are locked. I still don't have any idea how to resolve the issue. I need the ordering to be on a column that can not be part of the clustered index. The first image shows the problematic one, you can see that all the rows are locked. In the second one, below, only one row gets locked; order by is on the clustered index column.


texts: --wal-method=stream is not working for Postgresql 10.3

texts:
I am just leaning streaming replication using postgres Db, trying to achieve something as described here :- https://cloud.google.com/community/tutorials/setting-up-postgres-hot-standby

I have done all the configurations as mentioned for both master and standby server.But in my case I am using Postgres version 10.3.

So while running the pg_basebackup command I am getting below error:-

[vagrant@postgres1 bin]$ ./pg_basebackup -h /tmp/ -p 5432 -D /home/vagrant/postgres/data2  -P -v --wal-method=stream

pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2D000028 on timeline 1
pg_basebackup: starting background WAL receiver
33824/33824 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/2D0000F8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: could not send copy-end packet: no COPY in progress
pg_basebackup: child process exited with error 1
pg_basebackup: removing data directory "/home/vagrant/postgres/data2"

But while executing pg_basebackup with --wal-method=fetch option it completed successfully

[vagrant@postgres1 bin]$ ./pg_basebackup -h /tmp/ -p 5432 -D /home/vagrant/postgres/data2  -P -v --wal-method=fetch

pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2F000028 on timeline 1
50209/50209 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/2F0000F8
pg_basebackup: base backup completed

But what I want is the real time replication of the primary to the secondary, which is possible by --wal-method=stream option.

Please let if any has inputs for the same.

Thanks in advance


texts:
Table1 contains ServerName and a list of applications (from Application01 to Application20) installed on the server.

Now I need to import data from Table1 to Table2. The final Table2 should look as following:

How can I do this with a Stored Procedure?


texts: Error: 9245, Severity: 16, State: 1. / During the last time interval XXX query notification errors were suppressed

texts:
I've got this sequence of error messages showing up regularly in my server error log:

  Error: 9245, Severity: 16, State: 1.
  During the last time interval 257 query notification errors were suppressed.

But I cannot find any specifics on Error 9245. I've looked at the spid that is generating the error, and it's related to the service broker. I also have errors like these showing up regularly, and I suspect they are all related:

  The query notification dialog on conversation handle '{1C8BCD7B-3366-E211-93C7-005056BA3606}.' closed due to the following
  error:
  <?xml version="1.0"?>
  <Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error">
  <Code>-8490</Code>
  <Description>Cannot find the remote service &apos;SqlQueryNotificationService-b7240bb0-6586-4cee-bb7c-a3d2e706c0f8&apos; because it does not exist.</Description>
  </Error>

That error is one we have known about for a while, but haven't been able to resolve in our application code as of yet. The first two errors have just started showing up in the last few days though, and both the frequency of the errors and the number of query notifications that are being suppressed seem to be increasing.

Does anyone have any insight on further troubleshooting steps, or information on Error 9245?

Edit: Some additional information -

Regarding Jon's question, we are implementing SqlDependency in the App, and I believe it may not be done correctly, resulting in the query notification dialog messages. When we previously ran this application on SQL 2008R2 we enabled trace flag 4133 as outlined in this KB: http://support.microsoft.com/kb/958006 which at least alleviated the number of messages that were occuring in the event log.

That said, the first sequence of error messages that I posted above (Error: 9245) are new (to my knowledge). We upgraded the server to SQL 2012 SP1 a few weeks back and had NOT enabled trace flag 4133 as of the time that the 9245 error started showing up.

Also of note, while the errors were occurring with increasing frequency (nearing 1 error every five to ten minutes) for approximately a 12 hour period they stopped abruptly yesterday morning shortly after I asked here about Error: 9245. About an hour after that I enabled trace flag 4133, and I have not seen the error since. While it seems the two errors are related, I'm not entirely sure they're directly connected.

I'm going to see what I can find from other windows event logs on this server and see if anything else correlates to the times these errors occur. I've also disabled 4133 to confirm that it has not had an effect on the 9245 error.

Edit2: Minutes after disabling trace flag 4133 the errors have begun occurring again, and I believe I know the relation between the two now as well, so I'm going to go ahead and answer my own question.