Continuously Adding SSDs to an existing PostgreSQL Server An Ubuntu 18.04 server dedicated to running a PostgreSQL 11.2 server (with TimescaleDB extension) will be running out of disk space soon so new SSD disks will need to be added to the machine to support the growing database size.

The data is expected to continue increasing at the same/higher rate, so the storage hardware will need to be increased continuously until the machine runs out of 2.5" drive bays. Only then will distributing the database over multiple machines be considered, due to the increased complexity involved.

Thoughts

Union file systems like mergerfs can pool the drives together, solving the storage expansion problem easily. But this will increase the latency of database operations and are thus not recommended. Redundancy can be added by having an underlying RAID-1/5/6/10 or using SnapRAID.
RAID-0 and RAID-10 allows expansion of the RAID array into the newly added drive, with the bonus of increased performance from striping. However every drive being added is an additional point of failure. Furthermore, numerous people claims that mirroring SSDs is of limited use as both SSDs in RAID-0 will likely fail at the same time. So maybe this means that RAID-10 is no better than RAID-0. Furthermore, failure rates increase linearly with each additional SSD added.
RAID-5/6 reduce performance due to parity calculations and having write to 2 drives, reducing the effective IOPS by 75%. Seems to be a poor choice for databases.
PostgreSQL TABLESPACES can be used to assign every table to a specific drive. However, using tablespaces will make recovery very complicated. Furthermore, is it possible to create new tablespaces on the new drives and let Postgres automatically decide where to write new record to?
ZFS, BTRFS? Not familiar with them, willing to explore if they are suitable.

Question: What is the year-2020 recommended method of expanding the storage of a PostgreSQL machine, if expansion has to be done frequently (1-2X a year), performance should not be affected much, and recovery should not be so complicated that it may lead to data loss?

RAID-10 seems like a good idea to me, except that RAID-1 appears to have limited usage while causing the 'loss' of half the disk space, made worse by the increasing points of failure with increasing number of drives.

Due to budget constraints, we cannot fill up all 16 drive bays in a 2U chassis with SSDs at once, so it has to be done incrementally.

Any advice is greatly appreciated!

EDIT: After looking into ZFS, it seems like this might be one of the solutions for my case.

A ZFS pool with only mirrored ZFS vdevs (2 drives per vdev) will allow storage pool expansion by adding 2 drives at a time
Keeping 1-2 hot spares in the ZFS pool can allow automatic failover by ZFS when 1 of the drives fail
With mirrored vdevs, the rebuild time after a drive has failed will be way faster than with RAIDZ vdevs. This also reduces the chance that the surviving drive used for the rebuild can fail during the process. The degraded mirrored vdev will have much greater performance than a degraded RAIDZ vdev during rebuild
ZFS supports inline data compression which helps significantly (4X compression) with reducing the storage requirements of TimescaleDB data without having to use native TimescaleDB compression that prevents compressed data from being updated unless it is uncompressed. This is pretty important too as TimescaleDB is known to have poorer data compression compared to other databases like InfluexDB
Monitor and replace the SSDs when they are about to fail, as suggested by jjanes

If I understand this correctly, using ZFS mirrored vdevs will check all my boxes: Continuously add drives, allow drive pooling which provides a single mountpoint, data redundancy and a bonus ~4X data compression.

Have you turned on TimescaleDB's native compression, available since v1.5?  I ask because you mention ZFS/BTRFS above, which suggests you aren't employing its compression already.
Typically see 90-98% storage savings in the wild...
https://docs.timescale.com/latest/using-timescaledb/compression
Additionally, you can use TimescaleDB's attach_tablespace command to add more disks, and then new chunks are load balanced across the available tablespaces.
https://docs.timescale.com/latest/api#attach_tablespace


Database Design: New Table versus New Columns (This was suggested to be be repost here from StackOverflow)

Currently have a table .. and need to start adding new data columns to it.
Not every record (even going forward with new data after adding the new data columns) will have data.  So I am wondering if this is more suited for a new table since it is really an extension of some of the data rows and not applicable to every row.

In other words, since there will be a lot of unused columns for those new data elements, it seems like this would be more suited for a new table ?

The first table is a record of page views (currently 2million records)

- id
- IP address
- times viewed
- created_at timestamp
- date

for every IP address, a record is made per day - and consecutive pageviews are added to the times views per day

additional field(s) would be for point of origin tracking (ie google analytics source/medium/campaign)

Not every visit will have that info.
Im would assume about 10% of the rows will have the data (as it is usually only attributed on the first visit)

The main use for the data would be to attribute where people came from.  This may wind up being used more frequently (which then seems to lend itself to the single table)

Appreciate the feedback - can add more if needed

What you are wrestling with is vertical partitioning.  This is a physical database design technique to improve performance.  As with any physical database design technique, its applicability depends on the specific queries you are trying to optimize and if this technique will optimize them.  From a logical standpoint, if these new fields depend upon the candidate key for your entity then they are facts about it that belong with it.  First you should make sure you fully understand the functional dependence of these new fields on your candidate keys to verify they really are facts about daily page views.  If they are, deciding to partition them into another table is a performance optimization that should only be done if it achieves your performance goals.

In general, vertical partitioning is useful if you will query these new columns infrequently and distinctly from the other columns in the original table.  By placing those columns in another table that shares the same PK as your existing table, you can query it directly when you want those new columns and get much greater through-put as you will have many more rows per page on disk for this new table as all the columns from the original table won't be sitting on those rows.  However, if you will always query these columns along with the columns in the original table then a vertical partition wouldn't make much sense as you will always have to outer join to get them.  Pages from tables on disk come into the buffer pool of a DBMS independently, never pre-joined, and so that join will have to happen with every query execution even if the data is pinned in the buffer pool.  In this scenario making them NULLABLE columns on the original table would enable the DBMS storage engine to store them efficiently when NULL and eliminate the need to join on retrieval.

It sounds to me like your use case is the latter and adding them as NULLABLE to your original table is the way to go.  But as with everything else in database design, it depends, and in order to make the right decision you need to know your expected workload and what making a good choice depends on.  One good example of a proper use case for vertical partitioning would be a person search panel, where your application has some very rarely populated information about a person that someone might want to search on but rarely does.  If you put that information into a different table you have some good options for performance.  You can write the search so that you have 2 queries - one that uses the main, always populated information to search (like last name or ssn) only, and one that outer joins the very infrequently populated information only when it is requested for search.  Or
you could take advantage of the DBMS optimizer if it is smart enough to recognize for a given set of host variables that the outer join is not needed and won't perform it, and thus you only have to create 1 query.

What DBMS platform are you using?  The way in which the platform handles NULL column storage, optimizes your query, as well as the availability of sparse column support (SQL Server has this) will impact the decision.  Ultimately I would recommend trying out both designs in a test environment with production sized data and workload and seeing which better achieves your performance objectives.


How to get the ignored row while IGNORE_DUP_KEY is ON on SQL Server? I have a tmp table shown below.

In the above table

IGNORE_DUP_KEY is set to ON

and

the id column is the primary key.

Said table has only one key. After inserting lots of data I will get the

  Duplicate key was ignored.

message because of some redundant data.

I want to check which redundant row was tried to insert. I checked the origin of the message it was sys.messages.

Now, how to store the row which failed while the insertion attempt took place because of the duplicate primary key value?

There would be additional overhead, but one option might be to create an instead of insert trigger which would check for duplicates first and route those to another table.

--demo setup
set nocount on
DROP TABLE IF EXISTS [dbo].[TestTable]
CREATE TABLE [dbo].[TestTable](
    [ID] [int] NOT NULL,
    [ExtraInformation] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Employee_ID] PRIMARY KEY CLUSTERED
(
    [ID] ASC
) with (IGNORE_DUP_KEY = ON)
) ON [PRIMARY]
GO

DROP TABLE IF EXISTS [dbo].[TestTableIgnoredDups]
CREATE TABLE [dbo].[TestTableIgnoredDups](
    [ID] [int] NOT NULL,
    [ExtraInformation] [varchar](50) NOT NULL
)
 ON [PRIMARY]
GO

--create INSTEAD OF trigger
CREATE TRIGGER TestTable_InsteadOfInsert ON dbo.TestTable
INSTEAD OF INSERT
AS
BEGIN
    --select rows to be inserted into #temp
    SELECT *
    INTO #temp
    FROM inserted

    --insert rows to TestTableIgnoredDups where primary key already exists
    INSERT INTO TestTableIgnoredDups
    SELECT t.*
    FROM #temp t
    JOIN TestTable tt
        ON tt.id = t.id

    --delete the duplicate rows from #temp
    DELETE t
    FROM #temp t
    JOIN TestTable tt
        ON tt.id = t.id

    --insert rows to TestTableIgnoredDups where duplicates
    --exist on the inserted virtual table, but not necessarily on TestTable
    ;WITH DupsOnInserted
    AS (
        SELECT id
            ,count(*) AS cnt
        FROM #temp
        GROUP BY id
        HAVING count(*) > 1
        )
    INSERT INTO TestTableIgnoredDups
    SELECT t.*
    FROM #temp t
    JOIN DupsOnInserted doi
        ON doi.id = t.id;

    ;WITH DupsOnInserted
    AS (
        SELECT id
            ,count(*) AS cnt
        FROM #temp
        GROUP BY id
        HAVING count(*) > 1
        )
    DELETE t
    FROM #temp t
    JOIN DupsOnInserted doi
        ON doi.id = t.ID

    --insert the remaining rows to TestTable
    INSERT INTO TestTable
    SELECT *
    FROM #temp
END
GO

--verify by trying to insert a duplicate row
insert into testtable(id,ExtraInformation) values(1,'RowOne')
insert into testtable(id,ExtraInformation) values(1,'RowOneDup')

select * from TestTable
select * from TestTableIgnoredDups

Data from TestTable

| ID | ExtraInformation |
|----|------------------|
| 1  | RowOne           |

Data from TestTableIgnoreDups

| ID | ExtraInformation |
|----|------------------|
| 1  | RowOneDup        |


Random Assignment of Employees to Locations Oracle SQL Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
Production
PL/SQL Release 11.2.0.1.0 - Production "CORE 11.2.0.1.0
Production"
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

I will base this question of the HR Schema for simplicity.
I have two tables Employees and Locations. For this example, I have 37 locations and 173 employees.

My locations have a specific number assigned indicating the number of employees that need to be assigned to the location.
What I am attempting to do is randomly assign the number of required employees for each location from the employees table.

As such an example of desired results would be:

EMPLOYEE_ID FIRST_NAME LAST_NAME STREET_ADDRESS CITY STATE
1              A        Smith     737 Any Road Anywhere USA
2              B         Jones     50 Some Road Nowhere USA
3              C         Butler   737 Any Road Anywhere USA
4              D         Thomas    50 Some Road Nowhere USA
5              E         Marsh    737 Any Road Anywhere USA
6              F         Ruppert   50 Some Road Nowhere USA
7              G         Glenn     50 Some Road Nowhere USA

Use the random function to generate a number. "Number" your locations with slots equal to the number of positions they need. For example, if location A needs 15 and location B 16 then request a random number from 1 to 31 and:

1-15 go to A
16-31 go to B

Save that result to a table, and then pull all employees assigned per site.


SQL Server Alert Based On Severity Level Not Working I am new to SQL Server alerts, and I am trying to setup an alert That will run a job and send a notification, based on severity level.

In order to test if the alert works correctly I have downloaded a sample corrupted database from MSDN Blogs, and I have restored the database and issued a query against the database which failed with error 824 and level 24.

But although the error is a severe error and was logged to both the SQL log and the Windows Application Log nothing seemed to work, I have even tested with both an alert on the specific database and on all databases. (I have even setup alerts on all other levels from 19 and above just to confirm that nothing is happening.)

However an alert based on error number 824 did work, as well as an alert based on the performance counter's Sql Error counter did worked correctly.

Any suggestions?

The SQL Agent may need to be restarted to pick up the configuration changes.


how to change priority in ILIKE I have this simple table and user records.
I just want to get users with ilike or something else to get better order in PostgreSQL 10.12.
CREATE TABLE users(
   ID INT PRIMARY KEY      NOT NULL,
   username           CHAR(50) NOT NULL
);

INSERT INTO users (id, username) VALUES(1,'mandela');
INSERT INTO users (id, username) VALUES(2,'delpiero');
INSERT INTO users (id, username) VALUES(3, 'gondela');
INSERT INTO users (id, username) VALUES(4, 'del');
INSERT INTO users (id, username) VALUES(5, 'dell');
INSERT INTO users (id, username) VALUES(6, 'andel');

When I query "del" I want to get "del" user whose id is 4 in first place. it's ok with ordering by length. But still it is not what I want. The order should be like this. Second order must be "dell", third order must be "delpiero", fourth "andel", fifth "gondela" and sixth "mandela".
select * from users where username ilike '%del%' order by length(users.username) asc;

Here is SQL Fiddle

I would seem to me, that you want leading matches first, then shortest strings first, and finally alphabetic sort order:
SELECT *
FROM   users
WHERE  username ILIKE '%del%'
ORDER  BY username NOT ILIKE 'del%'  -- leading match first
        , length(username)           -- shorter words first
        , username;                  -- finally alphabetical

db<>fiddle here
NOT ILIKE 'del%' because false sorts before true. See:

Sorting null values after all others, except special

Aside: Never use char(n). See:

Any downsides of using data type “text” for storing strings?


MsSql Bulk Insert With commit size 1 vs Simple Insert Using SSIS OLE DB Destination, what is the difference between doing fast load with commit size 1 or inserts without the fast load option.

I understand that using fast load is going to invoke the bulk insert command, while the second option is just a simple insert. The actual question is whether there is any performance overhead doing bulk insert with maximum commit size equal to 1.

An explanation of what is happening under the hood in each case will be very appreciated, and whether there is any difference across various versions, starting with 2008.

Singleton inserts via a bulk insert technique are significantly slower than normal singleton inserts.  To prepare for bulk inserts, the client API must first send column meta data describing the tabular data stream that follows, a task not needed with normal inserts.  This overhead is offset by improved performance when streaming multiple rows of data but not when single rows are passed.  See https://msdn.microsoft.com/en-us/library/dd304523.aspx for details of the TDS protocol specification.

I ran tests a few years ago for a presentation on insert performance and found single-row bulk insert performance using SqlBulkCopy averaged about 500 rows per second compared to 1,800 per second for inserts.  With 100 row batches, bulk inserts averaged over 10K/sec compared to 3.5K with single-threaded singleton inserts. I would expect a similar difference with the IRowsetFastLoad OLE DB interface commonly used in SSIS.


Powershell script while executing mysqldump script I'm having an error 1045: Access denied for user 'mysqlsuperuser'@'test.internal.cloudapp.net' (using password: YES) when trying to connect
Here is the mysqldump command on my ps1 file.
$command = [string]::format("`"{0}`" -u {1} -p{2} -h {3} --default-character-set=utf8mb4 --quick --master-data=2 --single-transaction --routines --databases test1 --add-drop-database --result-file=`"{5}`" ",
        $mysqlDumpLocation,
        $databaseUsername,
        $databasePassword,
        $databaseIp,
        $database.DatabaseName,
        $saveFilePath);

But when I try to remove the {2} which is the DB password, then execute the ps1 file. When it prompt me for the password, it goes through.

I would guess that there's one or more characters in the password that are "confusing" the shell.
There are standard techniques for supplying passwords to mysql utilities that avoids having to have them coded into scripts or visible to any process list run on the machine.
See this answer.


How can I monitor the progress of an import of a large .sql file? I am importing a 7 GB foobar.sql to restore a table in a local database.

$ mysql -h localhost -u root 'my_data' < foobar.sql

$ mysql --version
/usr/local/mysql/bin/mysql  Ver 14.12 Distrib 5.0.96, for apple-darwin9.8.0 (i386) using readline 5.1

How can I monitor its progress?

If you're  just importing from a dump file from the CLI on *nix, e.g.

mysql -uxxx -pxxx dbname < /sqlfile.sql

then first install pipe viewer on your OS then try something like this:

pv sqlfile.sql | mysql -uxxx -pxxxx dbname

which will show a progress bar as the program runs.

It's very useful and you can also use it to get an estimate for mysqldump progress.

pv dumps the sqlfile.sql and passes them to mysql (because of the pipe operator). While it is dumping, it shows the progress. The cool thing is that mysql takes the data only as fast as it can progress it, so pv can show the progress of the import. I do not have any proof. But it seems so. I guess there is some buffer used, but at some point I think mysql does not read any more data when it is still busy processing.


Is it possible to extract the multiple variable parts of data into different columns without a procedure? I'm currently trying to do an analsis of a table containing a couple thousand rows of data. I have managed to restrict the data to what I really need, but I'm not sure if I can split it up further without a procedure to help.

The table itself has an index on the column myCol and the rows contain different parts of data (example rows for MyCol):

MyText
MyText2\nX1: [S 1: 3] (S 3)\nX2: [S 1: 2] (S 2)\nX3: [S 75: 1] (S 75)Further text2
MyText3\nX2: [S 800: 3] (S 2400)Further text3

Now what I want is to get all rows that have X1 - X4 set. That is no problem:

Select myCol from myTable where myCol LIKE '%X_: [S%] (S %)%'

But then I want to split the data for X1-X4 into 1 output column each. As they can be at different positions within the column (and it is not necessary that all of them appear) the question is if this is doable without having to rely on a procedure (I have very limited permissions on that sql server so I have to avoid procedures where possible).

You should be able to do this with some string manipulation using charindex() and substring().

It looks like you can use X1: to find the start of the value you are looking for and ) to locate the end of a value.

declare @T table(MyCol varchar(100));

insert into @T values
('MyText'),
('MyText2\nX1: [S 1: 3] (S 3)\nX2: [S 1: 2] (S 2)\nX3: [S 75: 1] (S 75)Further text2'),
('MyText3\nX2: [S 800: 3] (S 2400)Further text3');

select substring(T.MyCol, P1.X1, P2.X1 - P1.X1 + 1) as X1,
       substring(T.MyCol, P1.X2, P2.X2 - P1.X2 + 1) as X2,
       substring(T.MyCol, P1.X3, P2.X3 - P1.X3 + 1) as X3,
       substring(T.MyCol, P1.X4, P2.X4 - P1.X4 + 1) as X4
from @T as T
  cross apply (
              select charindex('X1:', T.MyCol) as X1,
                     charindex('X2:', T.MyCol) as X2,
                     charindex('X3:', T.MyCol) as X3,
                     charindex('X4:', T.MyCol) as X4
              ) as P1
  cross apply (
              select case when P1.X1 > 0 then charindex(')', T.MyCol, P1.X1) end as X1,
                     case when P1.X2 > 0 then charindex(')', T.MyCol, P1.X2) end as X2,
                     case when P1.X3 > 0 then charindex(')', T.MyCol, P1.X3) end as X3,
                     case when P1.X4 > 0 then charindex(')', T.MyCol, P1.X4) end as X4
              ) as P2;

Result:

X1                   X2                       X3                     X4
-------------------- ------------------------ ---------------------- -----
NULL                 NULL                     NULL                   NULL
X1: [S 1: 3] (S 3)   X2: [S 1: 2] (S 2)       X3: [S 75: 1] (S 75)   NULL
NULL                 X2: [S 800: 3] (S 2400)  NULL                   NULL


Create view shows single column values as columns aliases (names) in view I am making a simple application for survey for clients (lets say students), storing the answers in answer table with the following structure:

CREATE TABLE answers
(
    id INT(11) PRIMARY KEY NOT NULL,
    answer VARCHAR(255),
    studentId INT(11),
    questionIdentifier VARCHAR(255)
);

The answers will be stored like this:

INSERT INTO answers (answer ,studentId, questionIdentifier ) values ('5',222,'math')
INSERT INTO answers (answer ,studentId, questionIdentifier ) values ('4',222,'subject1')
INSERT INTO answers (answer ,studentId, questionIdentifier ) values ('1',222,'subject2')
INSERT INTO answers (answer ,studentId, questionIdentifier ) values ('5',222,'subject3')
INSERT INTO answers (answer ,studentId, questionIdentifier ) values ('3',222,'subject4')

I created view to get those answers as a columns so I return them back as JSOn to front end, shows the answers as columns, and students as rows:

CREATE OR REPLACE VIEW answers_view AS
  SELECT
    s.id AS `studentId`,
    s.number AS `studentNumber`,
    s.name AS `studentName`,
    ad.streetAddress AS `address`,

    ait1.answer AS `math`,ait2.answer AS `subject2`,ait3.answer AS `subject3`,
    ait4.answer AS `subject4`,ait5.answer AS `subject5`,ait6.answer AS `subject6`,

  FROM students s

    LEFT JOIN answers ait1 ON ait1.studentId = s.id AND ait1.questionIdentifier = 'math'
    LEFT JOIN answers ait2 ON ait2.studentId = s.id AND ait2.questionIdentifier = 'subject2'
    LEFT JOIN answers ait3 ON ait3.studentId = s.id AND ait3.questionIdentifier = 'subject3'
    LEFT JOIN answers ait4 ON ait4.studentId = s.id AND ait4.questionIdentifier = 'subject4'
    LEFT JOIN answers ait5 ON ait5.studentId = s.id AND ait5.questionIdentifier = 'subject5'
    LEFT JOIN answers ait6 ON ait6.studentId = s.id AND ait6.questionIdentifier = 'subject6'
    LEFT JOIN addresses ad ON ad.id = s.id
  GROUP BY s.id;

This just a sample of my code. I have like 50 questions, each question has its own questionIdentifier.

The view above is working so good but so slow too and the performance is so poor. I have like 6 students but the view lines is big and takes a minutes to finish.

How I can fix this view?

I can smell its bad smell, the code is clear but so bad for performance, and I am not that experienced in SQL.

I appreciate the help for his.

Get rid of id in answers.  Instead, have PRIMARY KEY(student_id, question_id).

While you are at it, change the 255 down to something realistic.

The sluggishness is due to having to scan the entire answers table for each JOIN.

And I suspect this was just plain wrong:

LEFT JOIN addresses ad ON ad.id = s.id


Which table gets the FK? Is there a clear Parent-Child relationship here? I have the following tables:

Each ProductLine will encompass many Parts. For this reason, it initially seemed to me that the Part table is a child of the ProductLine table. However, a part is not simply an extension of a product line, and when I asked myself if it is possible to a part to exist without a product line, the answer seems to be yes.

So I'm wondering if the above design appears to be correct or if there is a better design. In the Part table, PartBase can be thought of as the unique identifier for each part (the part number if you will.)

As a somewhat unrelated question, I have these tables set up as a one to many relationship. Since each Part must belong to a ProductLine but cannot belong to more than one, is it more correct to have this as a one-and-only-one to many relationship? Is the only difference that a Part must contain a not null ProductLineNumber in the actual database? Is that different from requiring that ProductLineNumber be not null?

To follow up to @HandyD's comment, as a business rule, every part must belong to exactly one product line. However, when I was thinking about a part as a physical object, it shouldn't need a product line to exist (a product line is just a label after all.) I'm comparing here to how a sale needs a customer to make sense, so that a Sale table would more clearly be the child of a Customer table. Is this distinction between Part-ProductLine and Sale-Customer a false one?

If the business requirement is

a business rule, every part must belong to exactly one product line.

So if the rule is, each part belongs to one product line the other side of that relationship is each product line contains zero-to-many parts which should convince you this is a clear instance of a parent/child relationship.
How a something exists in the real world versus how it exists within a business context/database are not always strongly tied.  If I have a part on my desk I do not care about it's product line.  But if I'm a business and I am selling the part I will probably need to categorize that part for search/reporting/sales/commission purposes.
The other columns you will probably need FKs on (PartType, Grade, Family, PopularityCode, UnitClass) will have the same physical implementation (parent table, foreign key reference) even if the context might not be the same (Each Part is described by One Part Type/PartType describes zero-to-many Parts, etc).
Won't get into the column types or other aspects of the design because that's not pertinent to the question but I'll be happy to provide guidance elsewhere.


Retrieve alert error description text via SQL Server Agent We have configured Alerts for the major severities and error codes on our SQL Server instances. Each alert then runs the same SQL Server Agent job, to trigger an alert in our monitoring system (for SMS messaging).

It would be useful to be able to retrieve the alert description text (see "This was just a test!" in the example below) from somewhere, to feed through to our monitoring system.

DATE/TIME:   27/09/2018 16:08:19
DESCRIPTION: Error: 50000 Severity: 20 State: 1 This was just a test!
COMMENT:     (None)
JOB RUN:     SystemAlerts

Is this text stored anywhere in the msdb database? Or is it not persisted like SQL Server Agent job history?

Tokens may be what you're looking for:

  SQL Server Agent token replacement is simple and efficient: SQL Server
  Agent replaces an exact literal string value for the token. All tokens
  are case-sensitive. Your job steps must take this into account and
  correctly quote the tokens you use or convert the replacement string
  to the correct data type.

Using $(ESCAPE_SQUOTE(A-MSG)) should allow you to access the description from an error message when the agent job is triggered by an alert.


Find reason long running stored proc SQL Server I have a stored proc which is called every 2.5 seconds by a program, 24-7.
This proc normally runs under the 10 milliseconds. A few times a day (3 -5 times) it takes around 42953 milliseconds to run (always between 42950 and 42959 milliseconds).

How can I find or profile the reason why it takes so long?

Update:
Sql server version 2008 R2

Procedure:

select
    [view_EventsToRaise].EventId,
    [view_EventsToRaise].EventStatus,
    [view_EventsToRaise].Tag,
    [view_EventsToRaise].SessieGebruikerId,
    Postbus.Persoon_Id_afz      as PersoonIdAfz,
    Postbus.Persoon_Id_adr      as PersoonIdAdr,
    Postbus.Persoon_Id_derde    as PersoonIdDerde,
    Postbus.Locatie_Id          as LocatieId,
    Postbus.Artikel_Id          as ArtikelId,
    Postbus.Onderwerp_Id        as OnderwerpId,
    Postbus.Bedrijf_Id_afz      as BedrijfIdAfz,
    Postbus.Bedrijf_Id_adr      as BedrijfIdAdr,
    Postbus.Bedrijf_Id_derde    as BedrijfIdDerde,
    Postbus.Document_Id         as DocumentId,
    Postbus.Document            as Document,
    DataKenmerk.Code            as PostbusDataCode,
    PostbusData.Inhoud          as PostbusDataInhoud,
    SimObj.Id                   as SimObjId,
    Eventsoort.Code             as Eventsoort
from
    [view_EventsToRaise]
    left outer join Postbus
        left outer join PostbusData
            inner join DataKenmerk
            on PostbusData.DataKenmerk_id = DataKenmerk.id
        on PostbusData.Postbus_Id = Postbus.id
    on Postbus.Id = [view_EventsToRaise].PostbusId
    left outer join SimObjEvent
        inner join eventsoort
        on eventsoort.id = SimObjEvent.eventsoort_id
    on [view_EventsToRaise].SimObjEventId = SimObjEvent.Id
    left outer join SimObj
    on SimObj.Id = SimObjEvent.SimObj_Id
order by
    [view_EventsToRaise].Tijd,
    [view_EventsToRaise].EventId

Update 29-9-2014:
When the above described situation occurs, looking to the profiler, the following got mine attention:
When the RPC:Completed event is registered, the the starttime is the current system time at the log moment, but the endtime is a value in the future (current system time plus the duration, approx. 43 sec in the future).

How is the value of the endtime column determined?
Is this a prediction?

This is probably caused by a blocking process. 3-5 times a day this is blocked by some update or other write that takes ~40 seconds. Your SELECT blocks until it can continue. If this is indeed the case, the problem can be identified using the Blocked Process Report Event. You will have to enable this event and set the threshold to ~35 seconds. See blocked process threshold Server Configuration Option. Obviously the event may fire for other queries too, but arguable those issues also needs to be addressed.

A simple workaround for blocking is using a row versioned isolation level. Read Understanding Row Versioning-Based Isolation Levels.

Otherwise, you will need to investigate this as a performance problem. Read How to analyse SQL Server performance.


Is it Ok to have one table with two relations? I have an event table that has two kinds of participants. Each event will have many participants of each type. Is it good form to have a separate table for each participant type, each related to the event table?

If your user community sees these as two separate things then model them as such.  If a row for one type populates one set of columns but always leaves other columns empty whereas a row for the second type populates the columns the first type left empty, that is an indication that separating them may be beneficial.  There are many other things to consider, however, such as use patterns, update frequency, the number of columns involved and the characteristics of your particular DBMS software in each permutation.  You may be willing to accept a compromised design if it runs quickly.


Indexing only for most recent entries in PostgreSQL Consider the following table:

|-----------------------------------------------------|
| raffle                                              |
|----|---------|----------|-----|---------------------|
| id | shuffle |  user_id | ... |           notify_at |
|----|---------|----------|-----|---------------------|
| 1  | 4D6G8Z1 |      542 | ... | 2019-12-01 14:00:00 |
| 2  | 64G264D |        6 | ... | 2019-12-28 14:00:00 |
| 3  | 4IPF93D |       58 | ... | 2020-01-01 14:00:00 |
| 4  | D25LF03 |       58 | ... | 2020-01-14 14:00:00 |
| 5  | G04LDWE |      684 | ... | 2020-03-02 13:00:00 |

In this table, most requests are not done to the id column, but to the user_id and notify_at, which is a 64-bit timestamp (no 2038 Bug):

SELECT *
  FROM [raffle]
 WHERE [user_id] = ?
   AND [notify_at] = ?

The table grows by the minute, but that is not the problem, but rather, the records for the notify_at in the current month are most accessed than the rest. In 10.000.000 records, an index of the user_id and notify_at sums 160MB, which only 1% of these are heavily accessed.

Is there a way to optimize an index (or any other strategy) to make retrieval of the records for the current month snappier (as in, "trying to use the index instead of sweeping the whole table for records)?

Update 1: I'm asking this way because the table holds many notifications. This would grow large over time, and the SQL query would only take those in the current month:

SELECT *
  FROM raffle
 WHERE user_id = 542
   AND notify_at > '2020-01-01 00:00:00'
   AND notify_at < '2020-01-31 23:59:59'

As you can see, the index would also grow larger.

The index type you are looking for is Range Type


How to find column with all same repeating character I have a char(15) NOT NULL column. I need to see if it is empty or contains all zeros. I can obviously do= '000000000000000' But is there a  way using LIKE so its not hard coded to 15? I don't see why the [^0] below doesn't match all zeros.

CASE WHEN LTRIM(RTRIM(IL.ItemNumber)) = '' THEN 1 ELSE 0 END IsEmpty
CASE WHEN LTRIM(RTRIM(IL.ItemNumber)) <> '' AND LTRIM(RTRIM(IL.ItemNumber)) NOT LIKE '[^0]' THEN 1 ELSE 0 END IsZeros

I'm basically trying to validate the column. Trimming works for empty, but maybe there is a much cleaner/flexible way to accomplish both of these validations? Thanks!

Actually there is REPLICATE() function:

WHERE <column> = REPLICATE('0',15)

Funny thing, it's actually using 17 characters, exactly the same as the '000000000000000' literal.

(Reading again the question, I suppose you don't want the above either because it has the 15 hard coded.)

The LIKE condition you have is not working as expected because the LIKE '[^0]' would be true only if the column had exactly 1 character. I think you need to check if it has only 0s or not:

WHERE <column> NOT LIKE '%[^0]%'

To check if it has only 0s or only spaces, this do (for CHAR and VARCHAR columns) and you don't even have to trim the column:

WHERE <column> = ''
   OR <column> NOT LIKE '%[^0]%'


Best way to delete large set of rows knowing field to NOT delete Coming from a Rails background, I have a large production database that I have a copy of where I only need records from 4 of the 2000+ companies.  I'm trying to delete all the rows except the ones belonging to the 4, and I know the way I have it is not optimal.

DELETE FROM appointments
WHERE (appointments.company_id NOT IN (6, 753, 785, 1611))

another example is when I have to delete records on a table where the company_id is on an associated table:

DELETE FROM mappings
WHERE mappings.id IN (SELECT mappings.id
                      FROM code_mappings
                      INNER JOIN codes ON codes.remote_id = mappings.code_remote_id
                      WHERE (codes.company_id NOT IN (6, 753, 785, 1611)))

In relation to the first table, appointments, make sure that you have an index on company_id column.

In relation to the mappings table, using EXISTS rather than IN may yield better performance. You can re-write your query as following:

DELETE FROM mappings AS m
WHERE EXISTS (  SELECT 1
                FROM code_mappings AS cm
                  INNER JOIN codes AS c
                    ON c.remote_id = cm.code_remote_id
                WHERE
                (
                c.company_id NOT IN (6, 753, 785, 1611)
                AND cm.id = m.id
                )
)

In the above query, you will also benefit from indexes on the mappings and code tables.

Documentation for creating indexes is @ https://www.postgresql.org/docs/current/static/sql-createindex.html. In your case, you can create indexes on the relevant tables as following:

CREATE INDEX company_id_idx ON appointments (company_id);

CREATE INDEX remote_id_company_id_idx ON codes (remote_id, company_id);

CREATE INDEX code_remote_id_id_idx ON code_mappings (code_remote_id, id);

-- If you don't already have a primary key OR index on `id` column in the `mappings` table, then create one:

ALTER TABLE mappings ADD PRIMARY KEY (id);
-- Choose primary key, or index: CREATE INDEX id_idx ON mappings (id);


No luck trying out Firebird 3.0 Today I though I'd give Firebird 3.0 a shot, but for some reason I can't seem to get it installed - at least not in a way that I can connect from a JDBC application.

What I did was to follow this article:

http://firebird3.blogspot.com.br/2012/03/how-to-install-firebird-3.html

This is what happened:

c:\etc\Firebird30>gsec -add sysdba -pass masterkey

c:\etc\Firebird30>start firebird.exe -a -m

c:\etc\Firebird30>isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> create database "localhost:c:\temp\mydbfb3.fdb" user "sysdba" password "masterkey";
Statement failed, SQLSTATE = 08006
Error reading data from the connection.
SQL>

Now creating the database "locally" works:

SQL> create database "c:\temp\mydbfb3.fdb" user "sysdba" password "masterkey";
SQL> commit;
SQL>

But in that case, when I connect from a JDBC application (still everything "localhost"), I get the error message (using sysdba/masterkey to log in)

GDS Exception. 335544472. Your user name and password are not defined. Ask your database administrator to set up a Firebird login.
Reason: Your user name and password are not defined. Ask your database administrator to set up a Firebird login.

I double-checked (more than once) that the username and password in the JDBC application is sysdba/masterkey.

From the commandline I can indeed access that newly created database:

SQL> connect 'c:\temp\mydbfb3.fdb';
Database:  'c:\temp\mydbfb3.fdb'
SQL> create table foo  (bar integer);
SQL> insert into foo values (1);
SQL> select * from foo;

         BAR
============
           1

SQL> commit;
SQL>

I'm sure I'm missing something obvious, but what?

The problem is twofold:

First Firebird 3 uses tighter security and has the old authentication mechanism disabled by default. So clients depending on older connection libraries will not work out of the box.

To allow the old authentication mechanism to work, the following setting needs to be set in firebird.conf:

AuthServer = Legacy_Auth, Srp, Win_Sspi

(be sure to remove the # prefix)

Second, the pure-java wire protocol implementation of Jaybird (the Firebird JDBC driver) uses an outdated authentication mechanism that is not supported by this Legacy_Auth option. Using Jaybird to connect to Firebird 3 at this time will only work if you use the native option (with the jdbc:firebirdsql:native: URL-prefix and jaybird2x.dll and fbclient.dll).

Addendum 1

Firebird 3 (since Alpha 1) has now also implemented the old authentication method used by Jaybird, so Jaybird will now also work without using the native library (as long as the legacy authentication is enabled, and the user has been setup with the legacy usermanager).

See also Jaybird and Firebird 3

Addendum 2

Jaybird 3 and higher support the more secure Srp authentication model out of the box, and Jaybird 3.0.4 introduced support for the wire protocol encryption. This means that it can connect to Firebird 3 without additional configuration.


Remove text from columns that may or may not be present multiple times I have the following table in SQL Server 2008.

create table OrgUnits
(
    OrgUnitId int not null constraint PK_OrgUnits1 primary key,
    OrgUnitName varchar(100),
    OrgUnitLevel int not null,
    OrgUnitKey varchar(100) constraint UQ_OrgUnits_OrgUnitKey unique,
    Level1Id int not null,
    Level2Id int,
    StatusId int not null constraint FK_OrgUnits_Status references Status,
    VersionNumber int not null
);

INSERT INTO OrgUnits (OrgUnitId, OrgUnitName, OrgUnitKey, Level1Id, Level2Id,
                      StatusId, VersionNumber)
VALUES
(218824, 'Company1 (5) (5) (5)', '5', 218824, 1, 1),
(218825, 'Division1 (1) (1) (1)', '1', 218825, 1, 1),
(218826, 'XAVAT (4) (4) (4)', '4', 218826, 1, 1),
(218827, 'WATe (7) (7) (7)', '7', 218827, 1, 1),
(218828, 'Communications', '05-905-6320-300', 218824, 218828, 1, 1),
(218829, 'Corporate Development', '05-955-6320-300', 218824, 218829, 1, 1),
(218830, 'Corporate Board of Directors', '05-111-3515-301', 218830, 1, 1);

Ideally I'd have OrgUnitName appear like OrgUnitName + ' (' + OrgUnitKey + ')';
But it appears like that update has been run on some but not all the values in the table.

What would the update statement be to update all rows so they would have the correct format? Please note I have hundreds of rows and this is only to fix an earlier issue a previous update caused. The query only needs to be run once.

Something like this?

UPDATE dbo.OrgUnits
SET OrgUnitName = RTRIM(REPLACE(OrgUnitName,'('+OrgUnitKey+')',''))+' (' + OrgUnitKey + ')';

View the result

SELECT  OrgUnitName from dbo.OrgUnits;

Result

OrgUnitName
Company1 (5)
Division1 (1)
XAVAT (4)
WATe (7)
Communications (05-905-6320-300)
Corporate Development (05-955-6320-300)
Corporate Board of Directors (05-111-3515-301)


PostgreSQL Extension Multiple SQL Files I'm creating a custom extension for PostgreSQL. I can get the extension to create successfully, but my foo--0.1.sql is getting rather large and complex. I'd like to break it out into multiple files for ease of maintenance but I can't figure out from the docs how to do this.

It seems like I could go one of two routes:

Use a built-in functionality with PG's extension building infrastructure to automatically use all of my .sql files.
Use the Makefile to concatenate all of my .sql files when the extension is first built.

My questions are whether #1 is even possible (I can't see anything in the docs - it only references the single extension--version.sql file that is required).

Also, how would I do #2? I don't have much experience with Makefiles and can't quite figure it out.

Thanks!

You could probably achieve #1 with "\i" directives to import snippets. I wouldn't advise it, though, I'd use a Makefile to concatenate the desired chunks.

It's as simple as something like:

myext--1.0.sql: myext-types.sql myext-tables.sql myext-views.sql
    cat $< $@

Note that the indent must be a tab, not spaces.

There's lots of good info out there on writing basic Makefiles, so I won't go on further.


How can I use Spatial Indexes for timestamps (non-spatial 1d data)? I'm using MySQL 8.0. I have some table containing TIMESTAMP column and I need to select within TIMESTAMP range:

SELECT *
FROM t_tbl
WHERE ts BETWEEN @someDate1 AND @someDate2

I want to make this query as fast as possible so I decide to try GEOMETRY trick. First of all I add some magik geometry column:

ALTER TABLE t_tbl
  ADD COLUMN geom_ts GEOMETRY NOT NULL
  GENERATED ALWAYS AS (Point(TO_SECONDS(ts),0)) STORED;
ALTER TABLE t_tbl ADD SPATIAL INDEX IDX_g_i (geom_ts);

So ts momentum is really a point onto X (time) axis. Now to check ts is in some range I'm using such geometry expression:

SELECT *
FROM t_tbl
WHERE MBRCONTAINS(LineString(point(@someDate1,0),point(@someDate2,0)), geom_ts)

This works but EXPLAIN doesn't show me any spatial index usage - it performs tablescan...

TIMESTAMP, DATETIME, INT, FLOAT, DECIMAL, etc are effectively "numbers".  Numbers can be ordered/searched/etc with ordinary BTREE indexes.  There is no need for SPATIAL for such one-dimensional quantities.

For date-like quantities, I like to do:

WHERE ts >= '2018-02-23'
  AND ts  < '2018-02-23' + INTERVAL 3 WEEK

INDEX(ts)

This avoids dealing with leap years, kludges like '23:59:59', inclusive/exclusive (not the careful choice of inequality operators), etc.


Mysql rollback and triggers How does mysql triggers and rollback working together? I need to know how this is working together? I can rollback if i proceed with more than one sql statement because it is in same query but it seems different with triggers because it proceeds seperately

If my sql statement fails and raises an error then trigger still proceeds? If it does how can i stop trigger?

And if my sql statement was successfull and trigger raised and error, how will i know and rollback?

Thanks

To provide examples...

Case 1:

  SET autocommit=ON;
  INSERT ...; -- with or without triggers
  UPDATE ...; -- with or without triggers

The INSERT and its trigger actions are performed atomically.  That is, the INSERT and trigger are either completely done or completely undone.
The UPDATE and its trigger actions are performed atomically.  That is, the UPDATE and trigger are either completely done or completely undone.

Case 2:

  -- (the value of `autocommit` does not matter)
  START TRANSACTION;
  INSERT ...; -- with or without triggers
  UPDATE ...; -- with or without triggers
  if ... then ROLLBACK else COMMIT;

The INSERT (and its trigger actions) might succeed or might fail and be rolled back.
The UPDATE (and its trigger actions) might succeed or might fail and be rolled back.
You might choose to ROLLBACK -- In this case all of the above stuff would be undone.
You might choose to COMMIT -- In this case all of the above stuff that has not already been rolled back would be committed.

Case 3:  (Do not use this case; it is too user-error-prone.)

  SET autocommit = OFF;
  INSERT ...; -- with or without triggers
  UPDATE ...; -- with or without triggers
  if ... then ROLLBACK else COMMIT;

This works the same as Case 2, except there is some question about the scope of the ROLLBACK/COMMIT.

(Please edit this answer if it is not correct.)


How do I completely disable MySQL replication I am running dual master MySQL replication and now want to move to a single database with no replication. How do I completely disable replication on both databases?

To completely disable replication with a master-master setup, you should do the following on each slave:

STOP SLAVE;
RESET SLAVE; (Use RESET SLAVE ALL; for MySQL 5.5.16 and later)
Edit the my.cnf and remove any information (if present) which refers to "master-..." or "replicate-..." options.  You may not have anything in the my.cnf, since replication can be setup dynamically as well.
Restart mysqld.


MongoDB wiredTiger Error: collection-*.wt does not appear to be a WiredTiger file (Window10) mongoDB version: 3.4.2 os: win10
Question: Cannot start mongoDB due to .wt file corruption
When reset mongoDB database path, cmd give the error as in picture
Any hint would be appreciated, thanks

@Lizhen, may be Wiredtiger does unclean shutdown. Just go to 'E:\Work_App\MongoDB\bin' and type in command prompt like

Then run the query in command prompt “mongod --dbpath /data/db –repair”

Suppose that i have installed MongoDB in C:\Program Files\MongoDB\Server\3.2\bin then the query statement will be like that

C:\Program Files\MongoDB\Server\3.2\bin>mongod --dbpath /data/db --repair

As you already said your MongoDB installation path is E:\Work_App_MongoDB\bin. So in your place it would be

E:\Work_App_MongoDB\bin>mongod --dbpath /data/db –repair

I hope so that it will be help out to you.


IO request time and less write latency I am facing issues related to constantly growing log file due to which I am getting error.
When I checked SQL Log I found below messages (error log is filled of these messages almost 90%)

  SQL Server has encountered 1 occurrence(s) of I/O
          requests taking longer than 15 seconds to complete on file

This is happening almost for all databases, including temdb [.mdf and .ndf files]
along with that I am getting below messages also

  average throughput:   0.34 MB/sec I/O saturation: 196 context switches
  1210

  last target outstanding: 530 avgWriteLatency 2

  FlushCache: cleaned up 6233 bufs with 384 writes in 142370 ms (avoided
  99 new dirty bufs) for db 6:0

My temdb size and other database and log file size is big enough.

History:

Previous developers had done both database shrinking and log shrinking many times.
All database is in compressed mode.

My action plans:

I have found that log file initial size is small with 10% growth. I am planning to increase the initial size by 512 MB with 512 MB growth to have reasonable number of VLF's.

Question 1: Though I will do in non peek hours of business but are there any chances that making these changes can corrupt my database or log file?

Question 2: Does database compression mode can effect IO operation? If yes, how can I resolve it?
I am planning to remove all database and log files from antivirus check.
I am planning to change target recovery time to < 1 minute of data base (specifically tempdb and my database)

Question 3: Is this going to impact my database? I mean flushing buffer and writing to disk should improve performance, right?

Question 4: I have three data file for tempdb already have 3 data files. As per Microsoft recommendation, do I have to increase my temdb data files/log files? My current processor configuration is mentioned below

Question 5: Am I going in wrong direction? Is there is something which I am missing that should be done to rectify this issue?

Edit Why I believe this is related to Log file size?

If there is an IO issue then read write ability of SQL will decrease, this will result in long running transaction hence cause large log file size.

Question 1: Though I will do in non peek hours of business but are there any chances that making these changes can corrupt my database or log file?

No this cannot damage your Log file.

  Question 2: Does database compression mode can effect IO operation? If yes, how can I resolve it?

Yes database compression affects I/O and as far as my experience goes it decreases the I/O and is actually benefitial if you look at I/O consumption. I will tell you how.

Compressed data will take fewer pages when stored on disk hence when data is read fewer I/O is required to fetch data in memory.
Again since fewer pages are bought into memory this increases buffer pool availibility.
CPU is what consumed when compression or decompression happens. But in most cases you would find that benefits gained by space and I/O would outweight the CPU consumption
Please remember a compressed page will remain compressed on disk as well as compressed when brought in memory for reading or writing. So no additional work of decompressing in memory

This is excellent article on data compression, its big, but would help you in understanding data compression

You must remove all SQL Server related folder and files from Antivirus check specially if you have McAfee Antivirus.

  •I am planning to change target recovery time to < 1 minute of data base (specifically tempdb and my database)

I can only say please leave it to default, I am not sure how recovery time has anything to do with log file and its configuration

Regarding Tempdb make sure you have Data files equal to number of physical cores. Paul has more to say about how many files you can have as per cores IMHO you can start with 4 tempdb files but keep monitoring for contention. Make sure that all tempdb data files have SAME INITIAL SIZE and SAME AUTOGROWTH setting. You can also enable TF 1118 to avoid contention

To check contention you can run below query

select
session_id,
wait_duration_ms,
resource_description
from    sys.dm_os_waiting_tasks
where   wait_type like 'PAGE%LATCH_%' and
resource_description like '2:%'

There are some good article for you to read about Tempdb and Contention

Troubleshooting Tempdb Contention by Paul Randal
Recommendation to reduce Allocation contention in Tempdb database
Monitoring Allocation Bottleneck In Tempdb

Edit:

  SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file

This message means that a particular session or query requested for I/O to get data from disk but that session had to wait more than 15 sec and after that request was catered. You can guess that 15 sec is threshold value moment this time is crossed message is dumped in errorlog. This MOSTLY means that disk is not able to cope up with I/O request which is getting generated and in turn means disk might be slow. Since you said 90 % of your errorlog is filled with this information I am forced to believe that underlying hardware is slow or might require a firmware upgrade. This Article will help you in understanding the issue and fixing it


mysql query only works when value is compared with primary key I'm working with this table:

mysql> describe dispositivos;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| sn        | varchar(25) | NO   | PRI | NULL    |       |
| device_id | varchar(25) | YES  |     | NULL    |       |
| ip_adress | varchar(25) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

I've noticed that when I type a query like this:

mysql> select * from dispositivos WHERE sn = "SAL1027TVGH";
+-------------+---------------+------------------+
| sn          | device_id     | ip_adress        |
+-------------+---------------+------------------+
| SAL1027TVGH |  CU_DGIE_6509 |  148.228.110.254 |
+-------------+---------------+------------------+
1 row in set (0.00 sec)

everything goes fine, but if I search by any other field, the query is not working.

mysql> select * from dispositivos WHERE ip_adress ="148.228.110.254";
Empty set (0.00 sec)

mysql> select * from dispositivos WHERE device_id ="CU_DGIE_6509";
Empty set (0.00 sec)

Do you know why this is happening?

there are other tables, but the problem just affect this one, it is being filled automatically with LOAD DATA LOCAL INFILE.

thanks.

Look at the output of your select statement: it seems you have a leading blank in your values.


I have hard time understanding Postgresql OVERLAP function when provided Interval On postgresql documentation whilst reading for date and time operations I have hard time understanding the following example:

SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: false

I failed to understand the reason why it returns false, is it because an Interval is being passed as a parameter?

As far as I understand it, (DATE '2001-02-16', INTERVAL '100 days') is evaluated as (DATE '2001-02-16', DATE '2001-02-16' + INTERVAL '100 days')

Which in turn is the same as (DATE '2001-02-16', DATE '2001-05-27') and that obviously doesn't overlap with (DATE '2001-10-30', DATE '2002-10-30')


How To Remove Leading Characters in SQL I have a table in SQL 2012 which we can call 'Table' for instance. It has items like this in it,

Column

_one
_two
three_blah
four_blah

I would like to be able to do this,

select * from Table order by Column

And the result be this,

four_blah
one
three_blah
two

So it orders the items based on removing the leading underscore characters. It can't just blindly remove the underscore characters though because the underscores might be in the middle of the items.

For instance this does not work,

select Replace(Column, '_', '') from Table order by Replace(Column, '_', '')

Assuming col caps out at 255 characters, this doesn't have to perform any checks on length or complicated case expressions:

SELECT col = SUBSTRING(col, PATINDEX('%[a-z]%', col), 255)
  FROM dbo.table
  ORDER BY col;

Updating to show an example that works with varchar even if the value I hard-coded exceeds the size of the column:

DECLARE @t TABLE(col VARCHAR(32));

INSERT @t VALUES
('_one'),
('_two'),
('three_blah'),
('four_blah');

SELECT col = SUBSTRING(col, PATINDEX('%[A-Za-z]%', col), 255)
  FROM @t
  ORDER BY col;

Results:

col
----------
four_blah
one
three_blah
two

Another alternative (that doesn't require hard-coding the length of the string or determining the length of every value in the table) is to use STUFF. Note that this does change how strings without a single alphabetic character will be handled (NULL output instead of being left alone). Those rows will still sort first, they'll just be less meaningful. You can deal with those however you like by adding COALESCE.

SELECT col = STUFF(col, 1, PATINDEX('%[A-Za-z]%', col)-1, '')
  FROM @t
  ORDER BY col;

(This portion of the answer should be credited to @MikaelEriksson.)


Node and Instance Relationship on Oracle RAC I'd like to ask about the process of Node and Instance on Oracle RAC functionality.
I know that RAC provides Failover, a RAC feature that keeps an application running even if the node that was servicing its request failed to operate.
Well so if RAC enters the Failover mode, is that mean the instance that was on the failure node will be shutted down or it was transferred to another node?

Thank You!

In general, it would mean that the instance that was servicing the request failed and is no longer running.  In a RAC cluster, you run one instance on each node of the cluster.  I wouldn't make sense to move an instance from one node to another in the cluster-- that would imply that one node would be running two instances of the same database.  Depending on your environment, the DBA might choose to add a new node (and a new instance) to the RAC cluster at least temporarily in response to the failure of one of the instances.


I have a beginning balance I need to calculate ending balance running total by month rows I have been searching this site and others trying to find an answer to this. I tried various things but cannot get my head around an answer so here goes.
Here my data:

What I need is to start with the begbal of each Acct and add the debits, subtract the credits and produce an ending balance of that month (row). When the Acct changes, pick up the new begbal and start again. With the data it should look like this:

I can get a running total on the debits and credits by doing this:

SELECT
pph.Acct,
pph.Year,
pph.Prd,
pph.begbal,
pph.debit,
pph.credit,
SUM(pph.debit+pph.credit) OVER (PARTITION BY pph.Acct ORDER BY pph.Year, pph.Prd) AS endbal
FROM GL_PeriodPostingHistory pph

What I want to do is

  If Acct <> prevoius Acct
  then Sum( begbal+debit-credit)
  else Sum(previous endbal+debit-credit) as endbal

and I just can't figure out how.

You can use the FIRST_VALUE window function along with SUM(pph.debit-pph.credit) to get your desired output.

SELECT
pph.Acct,
pph.Year,
pph.Prd,
pph.begbal,
pph.debit,
pph.credit,
FIRST_VALUE(pph.begbal) OVER (PARTITION BY pph.Acct ORDER BY pph.Year, pph.Prd)
    + SUM(pph.debit-pph.credit) OVER (PARTITION BY pph.Acct ORDER BY pph.Year, pph.Prd) AS endbal
FROM dbo.GL_PeriodPostingHistory pph;

db fiddle link.

Thanks to HandyD for the sample data written as T-SQL.


How to know the problem when i make this Full Backup Scenario? i'm working new on database administrator and i have some questions
if i want to make SQL full backup database every Saturday at 6:00 AM
and for some reasons the device was shutdown
the questions are :
whats the issues for my backup ? is it rollback ?
how can i know that was happened , when its not the time of work ?
what the a good plan to be sure to take the backup every week with out problems

If the database is down when the backup is supposed to start the backup can not run. If the database goes offline while the backup is running the backup will simply stop. It'll need to be restarted after the database comes back online.

There won't be anything logged that there was a backup running when the database went offline. You'd need to look at the job history of the backup job to see if it was running when the database went offline.

There's no way to guarantee that the backups run without issue.


Character set query in Oracle12c I have created my Oracle 12c database with database character set as AL32UTF8 and I have been told by the developers that it needs to be changed to UTF8.
National character set is UTF8.

Is it supported and feasible conversion of character set from AL32UTF8 to UTF8?

In Oracle 12c you should use the Database Migration Assistant for Unicode (DMU) to perform this operation.

https://docs.oracle.com/database/121/DUMAG/toc.htm

Your proposed conversion is very straightforward and shouldn't be a problem, however: Are you sure this is what you want to do? Oracle recommends AL32UTF8 above UTF-8, unless it is absolutely neccessary:

  Oracle supports two encodings of Unicode as the database character
  set: UTF-8 through the AL32UTF8 character set and CESU-8 through the
  UTF8 character set. (Note the use of the hyphen in the name of Unicode
  encoding and the lack of a hyphen in the name of Oracle character set.
  This differentiation is used throughout Oracle documentation.) UTF-8
  is a multibyte varying width Unicode encoding using 1 to 4 bytes per
  character. CESU-8 is a compatibility-only encoding, discouraged for
  information exchange by the Unicode standard. CESU-8 is very similar
  to UTF-8 except that so-called supplementary characters encoded by
  UTF-8 in 4 bytes, CESU-8 encodes as pairs of 3-byte codes. Oracle
  Database has deprecated the UTF8 character set, so it should not be
  used as a database character set except when explicitly required by an
  application, such as Oracle E-Business Suite Release 11i.


how to add removed partition? This is the structure of my table:

-- Table: t_keyword_conversion_ga
-- Create Table:
CREATE TABLE `t_keyword_conversion_ga` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`own_domain_id` int(11) DEFAULT NULL,
`keyword_id` int(11) DEFAULT NULL,
`traffic_date` date DEFAULT NULL,
`targeturl_id` int(11) DEFAULT NULL,
`entrance` int(11) DEFAULT NULL,
`transactions` int(11) DEFAULT NULL,
`item_revenue` decimal(9,2) DEFAULT NULL,
`goal1completions` int(11) DEFAULT NULL,
`goal2completions` int(11) DEFAULT NULL,
`goal3completions` int(11) DEFAULT NULL,
`goal4completions` int(11) DEFAULT NULL,
`goal5completions` int(11) DEFAULT NULL,
`goal6completions` int(11) DEFAULT NULL,
`goal7completions` int(11) DEFAULT NULL,
`goal8completions` int(11) DEFAULT NULL,
`goal9completions` int(11) DEFAULT NULL,
`goal10completions` int(11) DEFAULT NULL,
`goal1Value` decimal(9,2) DEFAULT NULL,
`goal2Value` decimal(9,2) DEFAULT NULL,
`goal3Value` decimal(9,2) DEFAULT NULL,
`goal4Value` decimal(9,2) DEFAULT NULL,
`goal5Value` decimal(9,2) DEFAULT NULL,
`goal6Value` decimal(9,2) DEFAULT NULL,
`goal7Value` decimal(9,2) DEFAULT NULL,
`goal8Value` decimal(9,2) DEFAULT NULL,
`goal9Value` decimal(9,2) DEFAULT NULL,
`goal10Value` decimal(9,2) DEFAULT NULL,
`medium` varchar(255) DEFAULT NULL,
`source` varchar(255) DEFAULT NULL,
KEY `id` (`id`),
KEY `keyword_id` (`keyword_id`),
KEY `traffic_date` (`traffic_date`),
KEY `own_domain_id` (`own_domain_id`,`traffic_date`),
KEY `targeturl_id` (`targeturl_id`)
) ENGINE=InnoDB AUTO_INCREMENT=581732054 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE COLUMNS(traffic_date)
(PARTITION p201101 VALUES LESS THAN ('2011-01-01') ENGINE = InnoDB,
PARTITION p201102 VALUES LESS THAN ('2011-02-01') ENGINE = InnoDB,
PARTITION p201103 VALUES LESS THAN ('2011-03-01') ENGINE = InnoDB,
PARTITION p201104 VALUES LESS THAN ('2011-04-01') ENGINE = InnoDB,
---->PARTITION p201105 VALUES LESS THAN ('2011-05-01') ENGINE = InnoDB,
PARTITION p201106 VALUES LESS THAN ('2011-06-01') ENGINE = InnoDB,
PARTITION p201107 VALUES LESS THAN ('2011-07-01') ENGINE = InnoDB,
PARTITION p201108 VALUES LESS THAN ('2011-08-01') ENGINE = InnoDB,
PARTITION p201109 VALUES LESS THAN ('2011-09-01') ENGINE = InnoDB,
PARTITION p201110 VALUES LESS THAN ('2011-10-01') ENGINE = InnoDB,
PARTITION p201111 VALUES LESS THAN ('2011-11-01') ENGINE = InnoDB,
PARTITION p201112 VALUES LESS THAN ('2011-12-01') ENGINE = InnoDB,
PARTITION p201201 VALUES LESS THAN ('2012-01-01') ENGINE = InnoDB,
PARTITION p201202 VALUES LESS THAN ('2012-02-01') ENGINE = InnoDB,
PARTITION p201203 VALUES LESS THAN ('2012-03-01') ENGINE = InnoDB,
PARTITION p201204 VALUES LESS THAN ('2012-04-01') ENGINE = InnoDB,
PARTITION p201205 VALUES LESS THAN ('2012-05-01') ENGINE = InnoDB,
PARTITION p201206 VALUES LESS THAN ('2012-06-01') ENGINE = InnoDB,
PARTITION p201207 VALUES LESS THAN ('2012-07-01') ENGINE = InnoDB,
PARTITION p201208 VALUES LESS THAN ('2012-08-01') ENGINE = InnoDB,
PARTITION p201209 VALUES LESS THAN ('2012-09-01') ENGINE = InnoDB,
PARTITION p201210 VALUES LESS THAN ('2012-10-01') ENGINE = InnoDB,
PARTITION p201211 VALUES LESS THAN ('2012-11-01') ENGINE = InnoDB,
PARTITION p201212 VALUES LESS THAN ('2012-12-01') ENGINE = InnoDB,
PARTITION p201301 VALUES LESS THAN ('2013-01-01') ENGINE = InnoDB,
PARTITION p201302 VALUES LESS THAN ('2013-02-01') ENGINE = InnoDB,
PARTITION p201303 VALUES LESS THAN ('2013-03-01') ENGINE = InnoDB,
PARTITION p201304 VALUES LESS THAN ('2013-04-01') ENGINE = InnoDB,
PARTITION p201305 VALUES LESS THAN ('2013-05-01') ENGINE = InnoDB,
PARTITION p201306 VALUES LESS THAN ('2013-06-01') ENGINE = InnoDB,
PARTITION pEOW VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
1 row in set (0.02 sec)

I dropped the partition 'p201105'. Now I am trying to add it again with the below statement but it throws an error:

ALTER TABLE t_keyword_conversion_ga
ADD PARTITION (PARTITION p201105 VALUES LESS THAN ('2011-05-01'));

ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

How to add the same partition?

The following seems to work in Mysql 5.5.

ALTER TABLE `t_keyword_conversion_ga` REORGANIZE PARTITION p201106  INTO (
    PARTITION p201105 VALUES LESS THAN ('2011-05-01') ENGINE = InnoDB,
    PARTITION p201106 VALUES LESS THAN ('2011-06-01') ENGINE = InnoDB
);

as you can check by sqlfiddle


Width in Postgres EXPLAIN? Explain plan in Postgres has width, which (per docs) is:

  Estimated average width of rows output by this plan node (in bytes).

My question: when will width be 0? Because in examples from docs, it sometimes is, like below:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=5.07..229.20 rows=101 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)
         Index Cond: (unique1 < 100)

Answer is in the article you are referring to:

  Here the planner has decided to use a two-step plan: the child plan
  node visits an index to find the locations of rows matching the index
  condition, and then the upper plan node actually fetches those rows
  from the table itself.

Word Output here means physical read of values. Child plan does not read values, it only reads locations (addresses) of rows, then parent plan actually reads the values. And next sentence provides explanation why two step process is chosen:

  Fetching rows separately is much more expensive than reading them
  sequentially, but because not all the pages of the table have to be
  visited, this is still cheaper than a sequential scan. (The reason for
  using two plan levels is that the upper plan node sorts the row
  locations identified by the index into physical order before reading
  them, to minimize the cost of separate fetches. The "bitmap" mentioned
  in the node names is the mechanism that does the sorting.)

Later in the text you'll find examples of two child processes where one process has width=244 and the other width=0. Usually child process will fetch row locations with 0 output, and then parent process actually output rows. It is not used always, but there are situations when it is faster than reading rows straight away.


mssql revert changes made today without backup A bug in an app made some changes now i need to rollback without a backup
Can i use the logfile for this ?
Is 'ROLLBACK' another option ?

INFO:
-My Recovery model is set to simple
-AutoCommit hasnt been set
-When i try to use the restore task, it tells me 'no backupset selected to be restored'

My Recovery model is set to simple

In short, there is nothing you can do but learn from this lesson that important databases should use the full recovery model (along with proper restore plans).

  Can i use the logfile for this

Since it's in the simple recovery model, most likely not.

  Is 'ROLLBACK' another option ?

Only if the transaction is currently open and it's a single transaction. If it's been committed, nope.


Unable to connect to mongodb I am trying to connect to mongodb and querying data from it. Here is what I am doing:

In node js my db connection file is looks like this:

//Using mongodb drivers has to be installed using npm
var MongoClient = require('mongodb').MongoClient;
var dburl = 'mongodb://localhost:27017/meanhotel';

var _connection = null;

var open = function() {
MongoClient.connect(dburl, function(err, db) {
if (err) {
console.log("DB connection failed",err);
return;
}else{
_connection = db;
console.log("DB connection open");
}
});
};

var get = function() {
return _connection;
};

module.exports = {
open : open,
get : get
};

And the node js code which I am using to query data from the database is like this:

var dbconn = require('../data/dbconnection.js');

var hotelData = require('../data/hotel-data.json');

module.exports.hotelsGetAll = function(req, res) {
//
var db = dbconn.get();
var collection = db.collection('hotels');
var docs=collection.find();

collection
.find()
.toArray(function(err,docs){
console.log("Found hotels",err);
res
.status(200)
.json(docs);
});

If I visit the URL

  http://localhost:3000/api/hotels

Nothing is coming out.

Also in terminal I am getting this output.

Found hotels { [MongoError: not authorized on meanhotel to execute command { find: "hotels", filter: {} }]
name: 'MongoError',
message: 'not authorized on meanhotel to execute command { find: "hotels", filter: {} }',
ok: 0,
errmsg: 'not authorized on meanhotel to execute command { find: "hotels", filter: {} }',
code: 13,

codeName: 'Unauthorized' }

I am using this for launch the mongo shell:

mongo localhost:27017/admin -u user -p pass

Also if I want to dump data to the database I am using this.

sudo mongodump --db meantest -u user -p pass --authenticationDatabase
admin --gzip

What could be going wrong?

The right URL format when MongoDB authentication is enabled is:

mongodb://[username:password@]host1[:port1][,host2[:port2],...[,hostN[:portN]]][/[database][?options]]

For your case: mongodb://user:pass@localhost:27017/meanhotel?authSource=admin

Reference: MongoClient or how to connect in a new and better way


Safely moving and creating new tempdb files Two things I'd like to know:

how do you safely move tempdb with minimal downtime?
how many tempdb files do you need?

Is it 1 file per core? So quad-core = 4 tempdb files, creating three new ones?

To move tempdb files, you simply need to do the following:

alter database tempdb
modify file
(
    name = tempdev,
    filename = 'C:\YourNewTempdbDir\tempdb.mdf'
)
go

alter database tempdb
modify file
(
    name = templog,
    filename = 'C:\YourNewTempdbDir\templog.ldf'
)
go

If you want to add a new file to tempdb, you simply need to do the following (provided you want to add it to the PRIMARY filegroup, or create your own):

alter database tempdb
add file
(
    name = tempdb2,
    filename = 'C:\YourNewTempdbDir\Tempdb2.ndf'
)
go

For these changes to take effect, you will need to restart the SQL Server service.  So as far as minimizing downtime goes, you are constrained to the amount of time it will take for the service restart.  You don't have to worry about moving the pre-existing tempdb database files, as SQL Server always recreates the files and the new locations/files will be created upon service startup.

As for the "1 tempdb data file per core", that is largely a myth.  The correct approach is to monitor tempdb file contention for the Page Free Space (PFS), Global Allocation Map (GAM), and the Shared Global Allocation Map (SGAM) pages.  Please reference this article to get a query (alternative link) that looks through the sys.dm_os_waiting_tasks DMV to see how much tempdb file contention there is.  Then you need to go off of this, instead of just blanketing tempdb with the same amount of files as there are cores.  It's the more advisable approach.


Recovering logical space from a tablespace I have a tablespace called DATA, and it is setup with auto-extend as false. This tablespace has two datafiles and is set up so it takes up 350 GB of physical space.

A week ago I queried user_tablespaces and dba_data_files and noticed it had 20% of available logical space. I then procedeed with a cleanup and removed many records from tables in this tablespace. We were expecting to see a big increase in available space. Unfortunately when I queried the views I noticed the available space was now 20.5%.

Could this be due to data fragmentation? Can we "defrag" the tablespace somehow and recover the lost space? Or do we need to recreate the tablespace from scratch?

When you delete records there is nothing that automatically compacts the segment, therefore you will need to do a segment shrink to reclaim the space.  Here is excerpt from the 11.2 Administrator's Guide on Reclaiming Wasted Space:

  Over time, updates and deletes on objects within a tablespace can
  create pockets of empty space that individually are not large enough
  to be reused for new data. This type of empty space is referred to as
  fragmented free space.

  Objects with fragmented free space can result in much wasted space,
  and can impact database performance. The preferred way to defragment
  and reclaim this space is to perform an online segment shrink. This
  process consolidates fragmented free space below the high water mark
  and compacts the segment. After compaction, the high water mark is
  moved, resulting in new free space above the high water mark. That
  space above the high water mark is then deallocated. The segment
  remains available for queries and DML during most of the operation,
  and no extra disk space need be allocated.

Further down on the same page you can read this:

  Segment shrink is an online, in-place operation. DML operations and
  queries can be issued during the data movement phase of segment
  shrink. Concurrent DML operations are blocked for a short time at the
  end of the shrink operation, when the space is deallocated. Indexes
  are maintained during the shrink operation and remain usable after the
  operation is complete. Segment shrink does not require extra disk
  space to be allocated.

  Segment shrink reclaims unused space both above and below the high
  water mark. In contrast, space deallocation reclaims unused space only
  above the high water mark. In shrink operations, by default, the
  database compacts the segment, adjusts the high water mark, and
  releases the reclaimed space.

The page includes much more information on the issue including examples.

The "Segment Space and the High Water Mark" section from the Concepts Guide might also be useful.


Duplicate entries in query with sum I have the following tables:

players: Columns: name, school_name, gender

double: Columns: player_name_fkey, school_name_fkey, category, tournament_number, score

My entries in the simple table will be similar to this. Each line is the score by a player at a specific tournament number.

Joe, Harvard, Junior, 1, 40
Joe, Harvard, Junior, 2, 30
Joe, Harvard, Junior, 3, 60
Anne, MIT, Senior, 1, 45
Anne, MIT, Senior, 2, 55
Anne, MIT, Senior, 3, 20 etc.

What I am looking to do is to get is, for each player, its total score over all tournaments. I then intent to select the best five players per school (which is why the partition command).

I have the following query:

SELECT player_name_fkey,
       school_name_fkey,
       sum_scores,
       rank() OVER (PARTITION BY school_name_fkey ORDER BY sum_scores DESC) AS scorerank
FROM (
  SELECT player_name_fkey,
         school_name_fkey,
         score,
         sum(score) AS sum_scores
  FROM double
    LEFT JOIN players ON players.name = double.player_name_fkey
  WHERE category = 'Benjamin'
    AND gender = 'Masculin'
    AND tournament_number >= 2
  GROUP BY player_name_fkey,
           school_name_fkey,
           score
) t1
GROUP BY t1.player_name_fkey,
         t1.school_name_fkey,
         t1.sum_scores;

I do get the correct sum for players, but unfortunately, I get an extra duplicate row for each player with a value of zero and I have no idea why.

Here is the code: http://rextester.com/XRRX92434

Thanks in advance for any help!

Benoit

You don't need the derived table, you can use the window function to order by the sum.

The LEFT JOIN works as an INNER JOIN as columns from both tables appear in the WHERE clause, so it's probably better to explicitly state it as an inner join.

I added the school_name in the joining condition. It seems like it be there but it's not very clear in the question description. (Could a player be in one school and appear in a different school in the double table? The foreign key definition indicates that it can't.)

You state that you want "for each player, its total score over all tournaments" but the code has WHERE tournament_number >= 2. Which is correct?

SELECT d.player_name_fkey,
       d.school_name_fkey,
       sum(d.score) AS sum_scores,
       rank() OVER w AS scorerank
FROM players AS p
    JOIN double AS d
    ON  p.name = d.player_name_fkey
    AND p.school_name = d.school_name_fkey
WHERE p.gender = 'Masculin'
  AND d.category = 'Benjamin'
  AND d.tournament_number >= 2
GROUP BY d.player_name_fkey,
         d.school_name_fkey
WINDOW w AS (PARTITION BY d.school_name_fkey ORDER BY sum(d.score) DESC) ;


Php PDO Get all column names from table in specific database I currently have the following query. Which fetches all column names for a table. But when there are 2 tables with the same name (in different databases) it fetches those column names as well.

In the following query $table of course means the requested table. In this case for example customer.

select column_name from information_schema.columns
where lower(table_name)=lower(\''.$table.'\')

So my question is: How do I specify which database the query should look into.

In MySQL database is a synonym for schema. Subsequently, the information you're looking for is in the column table_schema:

select column_name from information_schema.columns
where lower(table_name)=lower(\''.$table.'\')
  and lower(table_schema)=lower(\''.$mydb.'\')


SQL Server 2008 linked server to an Access 2007 DB that is in a protected directory on the Network We have a SQL Server 2008 database that we are attempting to link with an Access 2007 DB (the maintainer works down the hall and refuses to migrate to SQL Server). We have the password to the database (assume the database password = dbpassword, and if it was only this level of protection I think we would already have linked to the Access database with minimal problems.

However, the Access database is on a network drive, and within a directory on that network drive that is protected. We know the username and password (let's assume the username = cat and the password = pancakes).

I'm looking into this sp_addlinkedsrvlogin, and I believe it's what we need to do. Isn't it?

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
     [ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ]
     [ , [ @locallogin = ] 'locallogin' ]
     [ , [ @rmtuser = ] 'rmtuser' ]
     [ , [ @rmtpassword = ] 'rmtpassword' ]

The problem is, I don't know what to put in these parameters.

I'm assuming rmtuser would be 'cat' and rmtpassword would be 'pancakes'...

Here is the full path to the Access database:
\volume9\v9\projects\cats\cooking\pancakedb\back-end

volume9 appears to correspond with the K: drive...

So would rmtsrvname = '\volume9\v9\projects\cats\cooking\pancakedb\back-end'?

Also,  useself = 'FALSE' right? And I don't know about locallogin.

Am I going about this the right way? This is the first time I've ever set up a sp_linkedserver. Any help is greatly appreciated!

EDIT:

Forgot! Our linked server is called PANCAKECONFIGDB. I don't know if that's useful

EDIT 2:

These are the settings for SQL Server on my local machine. I'll put the information about the server's one's in a moment, as well as the full sp_linkedserver text. Is this where I should be changing things?

EDIT 3:

Here is the server's Log On As thing:

Also the script for the linkedserver...

****** Object:  LinkedServer [PANCAKECONFIGDB]    Script Date: 06/20/2012 14:14:28 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'PANCAKECONFIGDB', @srvproduct=N'Access 2007', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'\\volume9\v9\projects\cats\cooking\PANCAKECONFIGDB\back-end\PANCAKECONFIGDB.accdb', @provstr=N';PWD= dbpassword'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PANCAKECONFIGDB',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

I'll just summarize all my comments here as an answer.

You should read this:

http://msdn.microsoft.com/en-us/library/ms143504%28v=sql.105%29.aspx#Use_startup_accounts

The SQL Server Service is the SQL Server engine and runs under an account specified for the service and linked servers which use a file share will necessarily use those permissions, since there is no setting to have the Server Service impersonate another account when it connects through the filesystem.

The SQL Server Agent is a separate process which runs scheduled jobs.  If you use this and it connects to network shares on its own (not just telling SQL Server engine to run some statement against a linked server which would be under the engine permissions above), it would need to run under an appropriate domain account.  In your case, that doesn't appear to be running, so I wouldn't bother fooling with it.

Running the SQL Server Express has certain database size and functionality limitations, and I wouldn't consider it for general server-class production use except for high numbers of lightweight servers - for instance a deployment of an small inhouse application to several worksites - where you wouldn't want to pay for licensing and the requirements fit the product.

The built in network account is "NT AUTHORITY\NETWORK SERVICE" and you can just put that in and blank out the passwords to restore it.

If you switch the SQL Server Service to run as a domain account, you will need to give it sufficient permissions to access the OS on the database server - i.e. folder access to where the database files are if the account doesn't have access.


Why SSRS use Share point server for deployment of reports? I switched from Oracle platform to Microsoft SSRS.

Currently I am working on SSRS.
What is the use of Share point server for deployment of SSRS reports ?

Technet has a decent article.
https://technet.microsoft.com/en-us/library/bb326358(v=sql.105).aspx


Can MySQL Enterprise Backup perform partial backup/restore of views? I'm not able to backup and restore views using partial backup/restore (with tts) using MEB 3.12 with MySQL 5.6.28. Is this by design?

Have been searching for quite a while but not able to find helpful information on how views, triggers and stored functions are handled by MEB. Any information on the above subject is greatly appreciated. Thanks.

As per MySQL documentation here you can "partial" Restore of particular tables or tablespaces.

For your further ref here and here


index doesn't work properly I have this query

SELECT user_id, MAX( highscore ) AS highscore, stage, COUNT( * ) AS count
FROM single
GROUP BY user_id, stage

table engine is innodb, plus I haven't use any indexes except one key which is game_id it takes 0.16 sec to execute the query on 250k rows.

well I added INDEX(user_id_stage) to my indexes and execution time reduced to 0.07 sec
and EXPLAIN SELECT shows that mysql uses my index, but when I run this query, mysql don't use the index, what's the problem here? what am I doing wrong?

SELECT user_id, SUM( ts.highscore ) AS highscore, MAX( ts.stage ) AS stage, SUM( count ) AS total_played
        FROM (

            SELECT user_id, MAX( highscore ) AS highscore, stage, COUNT( * ) AS count
            FROM single
            GROUP BY user_id,stage
        ) AS ts
GROUP BY user_id

EDIT: I mixed up the order of my group bys sorry, and there's another group by in the outter select hich I also forgot

here's table defenition:

`single` (
  `game_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` mediumint(9) NOT NULL,
  `stage` tinyint(4) NOT NULL,
  `highscore` bigint(20) unsigned NOT NULL,
  `played_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`game_id`),
  KEY `user_id_stage` (`user_id`,`stage`)
)

this table stores savegames for players, user_id comes from members table.
each member can play a stage as much as he wants, then I need a ranklist which requires max highscore of each stage for each member, then sum all of highscores for each member and sort them.
so a member can play for example 100 times each stage, and I want to show this in my ranklist, that's why I'm counting total attemps of a stage for each member for each stage, and then sum these values for each member in order to get that value.

here's EXPLAIN SELECT for second query
EXPLAIN SELECT results

I use user_id to inner join this table with members table to get member's username.

I am not a MySQL expert (or even a user!) but the ideal index for your query appears to be as shown below:

create table `single` (
  `game_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` mediumint(9) NOT NULL,
  `stage` tinyint(4) NOT NULL,
  `highscore` bigint(20) unsigned NOT NULL,
  `played_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`game_id`),
  KEY `user_id_stage` (`stage`,`user_id`, `highscore`)
)

The plan I get is:

See the SQLfiddle


Not able to install PostgreSQL 9.4 on Ubuntu When I try to install PostgreSQL 9.4.+ on Ubuntu, it is installing version 9.3.9. I have used the following command:

sudo apt-get install postgresql postgresql-contrib

Then I have tried with sudo apt-get install postgresql-9.4.
But that gave me an error like "package not found".

How can I install 9.4 on Ubuntu?

Distribution details:

Distributor ID:   Ubuntu
Description:  Ubuntu 14.04 LTS
Release:  14.04
Codename: trusty

14.04 shipped with PostgreSQL 9.3, so that's what you're going to get if you use Ubuntu's repositories.

If you want PostgreSQL 9.4 or newer releases you need to add a 3rd party package repository, like the official PostgreSQL repositories from http://apt.postgresql.org/ . See the instructions there for details.

(You should also probably read https://help.ubuntu.com/community/PostgreSQL)


Three databases : one instance or three? I am a new DBA working for a charity and I have project to consolidate the multiple databases scattered across the organization.

I have identified the main databases that are mission critical to the organisation. We have a virtualised infrastructure. We currently run SQL Server 2008R2.

Fundraising Database (off the shelf package)
Case Load Services Database (Developed in house)
Finance Database (off the shelf package)

Now the question I have is (bearing in mind limited budget and resources) is whether I should put all 3 of these databases on 1 single instance on 1 single Windows Server 2008 VM. Or would it be better to have them on a single box with 3 named instances? Or possibly even 3 separate VM Servers with 1 databases each?

So even though each database is mission/business critical it is ok to have all of them on 1 single instance. So we would create a virtualised windows 2008 server called SQL01 for example. Install 1 instance of SQL 2008r2 and restore all 3 databases on that single instance? Or would it be better to create 3 names instances on that single server and have each database within its own named instance?

The only benefit to using three separate instances would be if you plan to take one instance offline for some reason, e.g. to patch SQL Server one instance at a time, without affecting the other two databases. Of course this doesn't buy you isolation from other types of events, such as patching Windows, never mind that all your eggs are in one VM's basket, too - so all three databases will be vulnerable to underlying hardware issues, resource limitations, etc. whether they are on one instance or three. And of course all three instances will be sharing the same resources within the VM; there is significant overhead involved with running each instance regardless of the databases or volume.

Also, using separate instances - and especially separate VMs - seems to fly in the face of your overall mission (consolidation).

So, I strongly recommend you create a single instance, and put all three databases on one instance.


SQL Server 2008R2 Alias not working I have an interesting situation here.  We're basically trying to migrate some SQL Server infrastructure to new hardware, and one of the boxes (single-instance, 2-node cluster) is running 2008R2.  The others are 2012 & 2014, but those don't exhibit this problem.

There's an app that connects to a server named, say, "OLD-SQL"; and let's say that IP is 11.22.33.44.  This is the name of the legacy SQL box running the default instance, SQL 2008R2, and Windows Server 2008R2.  The app's connection setting/config/string/whatever cannot be changed at this time.

The new SQL box that is set to replace that one, is named, say, "NEW-SQL"; and let's say it's IP is 11.22.33.55.  Also running SQL 2008R2 (same SQL build).  OS is Windows Server 2012 R2 (newer OS). Both boxes are actually Clustered Instances w/ 2 nodes each (old-fashioned failover clustering, nothing fancy).

So to aid the migration, for now, for testing/QA purposes, we've done the following:
1. Set up Hosts file on the client QA'ing machine to redirect the name "OLD-SQL" to 11.22.33.55 (new server).
2. Created a SQL Server Alias on the NEW-SQL server (using SQL Config Mgr.), named "OLD-SQL", pointing at itself, port 1433, protocol TCP/IP.

To test it out, I try connect via SSMS; I enter "OLD-SQL" as the server-name to connect to.  It fails with the infamous "SSPI context" error (https://support.microsoft.com/en-us/kb/811889).  The same thing happens with the application that's being tested.  Ping from cmd-line resolves fine -- it knows "OLD-SQL" resolves to new IP 11.22.33.55 based on the Hosts file.

Now, to really throw a wrench into things.  I go back onto the server NEW-SQL, and add another Alias, same parameters but named "OLD-SQL2".  This name is unique within the domain network.  I go back to my box, change my Hosts file to point from that name to the IP (11.22.33.55), and go to SSMS and try to connect again.  THIS WORKS!

I verify that I'm on the "right server" by doing a SELECT @@SERVERNAME, and lo-and-behold, it says "NEW-SQL".  But of course this isn't the alias I really want; I want to be able to give it the alias "OLD-SQL" and have my app redirected to "NEW-SQL" via the Hosts entry and the SQL Alias.

So what am I doing wrong with the first alias?  Is it just that I can't use the same name as an existing server on the network, with 2008R2?

Similar post on SO: https://stackoverflow.com/questions/6406811/alias-not-working-on-sql-server-2008-r2
(Did not solve the issue)

PS: I say "with 2008R2" specifically, because when I try the same setup (Hosts, SQL Alias) with our 2012-2014 boxes, they work the first way just fine!  (I.e. the Alias on the "NEW-SQL2012" box can be "OLD-SQL2012", which is the same as an existing server, and no problems connecting or anything.)

PPS: I did read about these aliases being more of a client-side thing, but that's why I'm using the Hosts file trick.  When we get ready for the "real" migration, we'll use DNS and other tricks outside of my knowledge (that's the SysEng's domain) to do the redirection of clients (apps/computers) to the new servers, but they said for now, for testing, the Hosts file thing is a good substitute.

Update: The main difference between the "working" setups and the "non-working" one, besides the SQL Server versions, is the fact that the old 2008R2 instance "SQL-OLD", when I connect using the "pristine" setup (no aliases or hosts-file-redir.), uses Kerberos authentication.  When I connect via a unique alias or redirect, such as "OLD-SQL2", it registers as NTLM.  And in ALL OTHER working connection methods, it's also NTLM.  What the hell is Kerberos doing to me??

Summarizing what we discovered in the chat.

When connecting over the network if the Windows Server is registered in Active Directory (AD) with a Service Principal Name (SPN), clients that try to connect with Integrated Authentication will get the Kerberos token from AD for the AD object that matches the server name to attach to the authentication.  Kerberos won't be used when either the server that is being connected doesn't have an SPN or is being connected using SQL Authentication.

In this case, because "OLD-SQL" is still registered in AD and has an SPN registered spoofing, the DNS in the local host file won't work when using Integrated/Windows Authentication, as there is nothing adjusting which token is returned/used from AD. There doesn't appear to be a way to have the client know to use Kerberos token for "NEW-SQL" with the original "OLD-SQL" server still being in service, except to remove the "OLD-SQL" SPN from AD to force the authentication to fall back to NTLM.  Spoofing the DNS works for aliases in which are not actual computer names registered in AD or do not have an SPN registered, as AD doesn't return a token for them, so the authentication falls back to using NTLM.

References

Understanding Kerberos Keys

Registering an SPN