texts: What is a deterministic method for evaluating a sensible buffer pool size?

texts:
I'm trying to come up with a sane way to understand if the max server memory (mb) setting is appropriate (either should be lower, or higher, or stay the way it is).  I am aware that max server memory (mb) should always be low enough to leave room for the operating system itself, etc.

The environment I'm looking at has several hundred servers; I need a reliable formula I can use to determine if the present size of the buffer pool is appropriate since RAM is costed per GB allocated to each server.  The entire environment is virtualized, and "physical" RAM allocated to a VM can easily be changed up or down.

I have a particular SQL Server Instance I'm looking at now with a PLE of 1,100,052 seconds, which equates to 12.7 days (the amount of time the server has been up).  The server has a max server memory setting of 2560MB (2.5GB), of which only 1380MB (1.3GB) is actually committed.

I've read several items including one by Jonathan Keheyias (post) and another by Paul Randal (post), and several others.  Jonathan advocates monitoring for a PLE below 300 per 4GB of buffer pool as being too low.  For the SQL Server Instance above, 300 * (2.5 / 4) = 187 results in a really really low target PLE below 300.  This instance has 290GB of SQL Server data (not including log files), and is only used for integration testing.  Assuming the last 12 days is representative of typical usage for this server, I'd say the max server memory (mb) setting could be lowered.

At the other end of the scale, I have another integration-test server with a PLE of 294, that has a max server memory (mb) setting of only 1GB.  This server has only 224MB of SQL Server data not including logs, and is running some BizFlow databases.  This server might benefit from a higher max server memory (mb) setting.

I'm thinking a good starting place for targets that might be assigned too much memory could include looking at:

SELECT
    RamMB = physical_memory_in_bytes / 1048576
    , BufferPoolCommittedMB = bpool_committed * 8192E0 / 1048576
    , BufferPoolCommitTargetMB = bpool_commit_target * 8192E0 / 1048576
    , PercentOfDesiredSizeMB = CONVERT(INT,(CONVERT(DECIMAL(18,2),bpool_committed)
                            / bpool_commit_target) * 100)
FROM sys.dm_os_sys_info;

If BufferPoolCommitTargetMB / BufferPoolCommittedMB is greater than 1, the server is not using the entire buffer pool.  If the machine in question also has a PLE of greater than "x" then it might be a good candidate for a decrease in max server memory (mb).

Since the Buffer Manager:Lazy writes/sec performance counter tracks the number of times SQLOS has written pages out to disk between checkpoints due to memory pressure, this might be another good thing to look at.

DECLARE @WaitTime DATETIME;
SET @WaitTime = '00:00:15';
DECLARE @NumSeconds INT;
SET @NumSeconds = DATEDIFF(SECOND, 0, @WaitTime);
DECLARE @LazyWrites1 BIGINT;
DECLARE @LazyWrites2 BIGINT;

SELECT @LazyWrites1 = cntr_value
FROM sys.dm_os_performance_counters dopc
WHERE (
        dopc.counter_name LIKE 'Lazy writes/sec%' COLLATE SQL_Latin1_General_CP1_CI_AS
    )
    AND dopc.object_name = 'MSSQL$' + CONVERT(VARCHAR(255),
               SERVERPROPERTY('InstanceName')) + ':Buffer Manager';

WAITFOR DELAY @WaitTime;

SELECT @LazyWrites2 = cntr_value
FROM sys.dm_os_performance_counters dopc
WHERE (
        dopc.counter_name LIKE 'Lazy writes/sec%' COLLATE SQL_Latin1_General_CP1_CI_AS
    )
    AND dopc.object_name = 'MSSQL$' + CONVERT(VARCHAR(255),
               SERVERPROPERTY('InstanceName')) + ':Buffer Manager';

SELECT LazyWritesPerSecond = (@LazyWrites2 - @LazyWrites1) / @NumSeconds;

The above code assumes the server is under load during the 15 seconds it takes to run, otherwise it will report 0; which might be a misleading false-negative.

Should I also look at PAGELATCHIO_* wait stats or some other wait type as an indicator of memory pressure, or lack thereof?

My question is, how can I reliably determine a "good" target value for PLE and max server memory (mb)?


texts: How can performance of queries containing keywords like IN, EXISTS, etc. be optimized?

texts:
I was interested in some of the general ways one can use to optimize the performance of queries that use keywords like IN and EXISTS (relying on the state of the entire database).

For example, something like

SELECT id FROM table1 WHERE A IN (SELECT B FROM table2 WHERE C < 200)

The execution plan for this query involves a hash join that takes almost 2 seconds on a 1 GB database. Given that I may be executing many of these queries, are there any steps I can take to optimize these queries? Something along the lines of creating an index/materializing some data?

Thanks!


texts:
Unexpected failure in transactions with isolation level serializable

Hi,

I have a challenge with transactions in Postgresql when they have the isolation level SERIALIZABLE.

This is the transaction table and data:

CREATE TABLE "test_transaction"
(
  "id" bigserial PRIMARY KEY,
  "other_id" bigint NOT NULL,
  "created_time" timestamp with time zone NOT NULL DEFAULT now(),
  "updated_time" timestamp with time zone NOT NULL DEFAULT now()
);

INSERT INTO "test_transaction"
    ("id", "other_id")
    VALUES
    (1, 1),
    (2, 2),
    (3, 1),
    (4, 2),
    (5, 1),
    (6, 2);

Start transaction A:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE;

Start transaction B:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE;

Select the last transaction with "other_id"=1 in transaction A:

select
  "test_transaction"."id",
  "test_transaction"."other_id",
  "test_transaction"."created_time",
  "test_transaction"."updated_time"
from
  "test_transaction"
where
  "test_transaction"."other_id" = 1
order by
  "test_transaction"."updated_time" desc limit 1;

Select the last transaction with "other_id"=2 in transaction B:

select
  "test_transaction"."id",
  "test_transaction"."other_id",
  "test_transaction"."created_time",
  "test_transaction"."updated_time"
from
  "test_transaction"
where
  "test_transaction"."other_id" = 2
order by
  "test_transaction"."updated_time" desc limit 1;

Update "updated_time" where id = 1 in transaction A:

update
  "test_transaction"
set
  "updated_time" = now()
where
  "test_transaction"."id" = 5
returning
  "test_transaction"."id",
  "test_transaction"."other_id",
  "test_transaction"."created_time",
  "test_transaction"."updated_time";

Update "update_time" where id = 2 in transaction B:

update
  "test_transaction"
set
  "updated_time" = now()
where
  "test_transaction"."id" = 6
returning
  "test_transaction"."id",
  "test_transaction"."other_id",
  "test_transaction"."created_time",
  "test_transaction"."updated_time";

Commit transaction A:

COMMIT;

Commit transaction B:

COMMIT;

Recieve an error in transaction B

ERROR: could not serialize access due to read/write dependencies among transactions
SQL state: 40001
Detail: Reason code: Canceled on identification as a pivot, during commit attempt.
Hint: The transaction might succeed if retried.

Not sure what goes wrong in transaction B as I think that transaction A and B should work on each set and only lock sets where other_id equals 1 and 2 respectively. But it seems like the entire table is protected?

I have tested the same with "Repeatable Read" isolation level and which works perfectly. I can see that it should be "Phantom Read" that is allowed here.

Can someone shred some light on what goes on here as I expected it would work without any issues?


texts: How to list all views in SQL in PostgreSQL?

texts:
How do I list all views for a database using an SQL command in PostgreSQL?

I would like something similar to output of the psql \dv command, but preferably just a list of view names.  e.g.,

SELECT ...;
my_view_1
my_view_2
my_view_3

I'm running PostgreSQL v9.1.4 on Ubuntu Linux.


texts: Where do Linked Server Queries get executed?

texts:
I have two instances ServerA and ServerB, and I have created a linked server in ServerA for ServerB as Linksrv_B.

I can execute a query on ServerA using the four part naming convention:

SELECT * FROM Linksrv_B.master.sys.databases

or OPENQUERY():

SELECT * FROM OPENQUERY(Linksrv_B, 'SELECT * FROM master.sys.databases')

I know linked server works across heterogeneous database using distributed transaction as a rowset.

Where does the OLEDB provider gets initiated/connected? ServerA or ServerB?
Where does the query gets executed on ServerA or ServerB? Does optimizer comes into play if so on which server?
Where does the result set get cached?
Could anyone give us a detailed explanation how linked server works.


texts: Difference between fsync and synchronous_commit - postgresql

texts: What is the difference between fsync and synchronous_commit? I read the documentation, the only thing I understood is both of them are trying to make updates physically written to disk.


texts: Storing information about office locations for international companies

texts:
I have a table in MS Access containing a list of about 500 companies and some information about them. I am trying to create another table that contains information about the office locations of these companies. I am think to populate this table with the following fields:

Country, Company 1, Company 2.... all the way to Company 500.

The Country field will contain all the countries in the world. The other 500 fields will contain True/False values for whether the company has an office in the country in question.

Needless to say, this will be a huge table containing true/false values, furthermore it will be difficult to populate and enter data accurately for this table. Does anyone know of a more "correct" and data efficient way to store this information and query it easily? The objective is to be able to summon a list of all the countries where each company has an office location easily and efficiently. Or to start from a country and come up with a list of all companies present in that country.


texts:
This question is different but similar to this request looking for SQL-86.

SQL 1989 is almost 30 years old. Is this spec freely available anywhere. The standard does not have a Wikipage or the like?

The spec goes by

ANSI X3.135-1989 Database Language - SQL With Integrity Enhancements
ISO 9075-1989
SQL-1989

The need for it came up while researching the question, Does the SQL-1992 standard restrict naming identifiers to 18 characters?


texts: Can't restart Postgres on Windows 10 with external drive

texts:
I've been working on a postgres database, with my data directory on my 8TB external USB drive. Occasionally, I need to restart the postgres instance to change configuration settings, but for some reason I can never get the service to restart.

Everything works fine in Windows 7
Can't restart postgres in Windows 10
Data directory in D:\postgresdata\
Database is empty at the moment, just the default postgres instance
When windows starts, the database engine starts automatically, and functions correctly
Running "show data_directory" returns D:/postgresdata/
Opening services.msc and restarting my postgres service starts and stops, never successfully starting.
Running pg_ctl.exe start -D D:\postgresdata fails to start with these errors:

FATAL:  could not access status of transaction 0
DETAIL:  Could not open file "pg_notify/0000": Invalid argument.

Things I've tried:

I've tested the same scenario on my C: drive, and it always starts and restarts correctly.
I've run chkdsk on the D: drive, nothing is wrong with it.
I've played around with permissions on D: and nothing makes any difference.
I've made a second partition from my C: drive and verified that a data directory on it always starts and restarts correctly.
Initializing a new database with pg_ctl init -D D:\someotherpath\ on the D: drive does not work either, same error as restarting in general.

I'm at a loss for what else to try. The weirdest thing is how the system always starts up and works at system boot time, but not in any other circumstance.

Update:
I attempted to run postgres against a USB thumb drive and the error is the same.


texts: MySQL table locks solution -> InnoDb / Partitions

texts:
I'm experiencing many table locks in a MyISAM MySQL table that is pretty big (Constx10xGB and 15M rows).

It's mainly caused due many heavy inserts. My current thoughts to optimize:

Changing to Innodb - to avoid table level lock. In the past I've had bad experience with Innodb, possibly due to bad configuration. If you would recommend this option, please advise regarding configuration.
Adding partitions - we're running on one server, and we don't have different mounts in the HDs, so we're just avoiding locks in such a solution. Currently the table isn't 'key-value', but it can be done (normalization to different table).

Which would you recommend / other options?

This is the SHOW CREATE TABLE

CREATE TABLE `tbl_name` (
 `var0` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `var1` int(11) NOT NULL,
 `var2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `var3` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `var4` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `var5` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `var6` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `var7` tinyint(1) NOT NULL,
 `var8` int(11) NOT NULL,
 `var9` int(11) NOT NULL,
 `var10` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `var11` int(11) NOT NULL,
 ...
 `var22` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`var0`),
 UNIQUE KEY `var1` (`var1`),
 KEY `var2` (`var2`,`var3`),
 KEY `var4` (`var4`),
 KEY `var5` (`var5`)
) ENGINE=MyISAM AUTO_INCREMENT=13932545 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

This is the SHOW TABLE STATUS

Name      Engine  Version Row_format  Rows      Avg_row_length  Data_length     Max_data_length  Index_length    Data_free    Auto_increment   Create_time           Update_time             Check_time              Collation         Checksum        Create_options  Comment
tbl_name  MyISAM  10      Dynamic     13931652  2605            36293518012     281474976710655  1318508544      0            13933203         2012-07-31 13:25:26   2012-08-26 14:44:44     2012-08-20 23:15:31     utf8_unicode_ci   NULL


texts: SQL Server creates different plans when OR condition is re-arranged

texts:
I was reviewing an under-performing query which looks like this:

WHERE manymany.Active = -1
  AND manymany.Check1 = -1
  AND manymany.WebsiteID = @P1
  AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
  AND main.Active = -1
  AND main.StatusID = 1
  AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
  AND (main.TextCol1 IS NOT NULL OR main.TextCol2 IS NOT NULL)
ORDER BY aux.SortCode

I accidentally used SSMS query designer on this query and it re-wrote the query as follows:

WHERE manymany.Active = -1
  AND manymany.Check1 = -1
  AND manymany.WebsiteID = @P2
  AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
  AND main.Active = -1
  AND main.StatusID = 1
  AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
  AND main.TextCol1 IS NOT NULL

   OR manymany.Active = -1
  AND manymany.Check1 = -1
  AND manymany.WebsiteID = @P2
  AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
  AND main.Active = -1
  AND main.StatusID = 1
  AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
  AND main.TextCol2 IS NOT NULL
ORDER BY aux.SortCode

If you look closely you'll notice that it simply expanded the OR condition by repeating all conditions i.e. it changed a AND (b OR c) to (a AND b) OR (a AND c).

The resulting query was 50% smaller in terms of cost and 33% smaller in terms of execution time. I simply do not understand why re-arranging the OR condition changed the plan when both queries are identical (?). I could have expanded the OR condition myself by copy-pasting the conditions but why should I?

Paste the plan and screenshot:

Number of rows:

main     2718
manymany 188761
aux      19

Notes:

TextCol1 and TextCol2 are text datatype and cannot be indexed
There are avg. 170.20 records in manymany table per website id


texts: How to SELECT multiple COUNT(*) columns with each GROUP BY having a different WHERE clause?

texts:
I have the following schema:

CREATE TABLE Person (
  PersonId int PRIMARY KEY
)

CREATE TABLE Action (
  ActionId int PRIMARY KEY,
  PersonId int NOT NULL FOREIGN KEY REFERENCES Person(PersonId),
  ActionTime datetime NOT NULL
)

and the following data:

INSERT INTO Person (PersonId) VALUES
(1),
(2),
(3),
(4)

INSERT INTO Action (ActionId, PersonId, ActionTime) VALUES
(1, 1, '2014-02-01'),
(2, 1, '2014-02-02'),
(3, 2, '2014-02-02'),
(4, 3, '2014-03-05')

I would like to run a query which shows the number of actions each person performs between the 15th of each month. For example, I am trying the following:

SELECT
    Person.PersonId,
    COALESCE(GroupA_Actions_Made, 0) AS GroupA_Actions_Made,
    COALESCE(GroupB_Actions_Made, 0) AS GroupB_Actions_Made
FROM
    Person
    RIGHT OUTER JOIN (
        SELECT
            PersonId,
            COUNT(*) AS GroupA_Actions_Made
        FROM
            Action
        WHERE
            ActionTime BETWEEN '2014-01-15 00:00:00' AND '2014-02-14 23:59:59'
        GROUP BY
            PersonId
    ) GroupA ON GroupA.PersonId = Person.PersonId
    RIGHT OUTER JOIN (
        SELECT
            PersonId,
            COUNT(*) AS GroupB_Actions_Made
        FROM
            Action
        WHERE
            ActionTime BETWEEN '2014-02-15 00:00:00' AND '2014-03-14 23:59:59'
        GROUP BY
            PersonId
    ) GroupB ON GroupB.PersonId = Person.PersonId

However, the query I am trying is returning the following:

PersonId | GroupA_Actions_Made | GroupB_Actions_Made
(null)     0                     1

but I would like

PersonId | GroupA_Actions_Made | GroupB_Actions_Made
1          2                     0
2          1                     0
3          0                     1

(I do not want the results to return anything for people who have not made actions.)

How can I obtain the results in the desired format?

UPDATE

Each of the answers works except that I had to wrap them as follows:

SELECT
    PersonId,
    GroupA_Actions_Made,
    GroupB_Actions_Made
FROM (
    -- (answer)
) t
WHERE
    GroupA_Actions_Made > 0
    OR GroupB_Actions_Made > 0

Using SQL Server Profiler, the accepted answer seems to have the fastest query time on large data sets.


texts: How do I combine rows in sql query and remove duplicates

texts:
I have a large database that is connected to a forms mapping program. In order for me to map results from SQL queries I have to put a single line field in for each result row. It defaults to row 0 and if there are more than one result I need to manual change the index of the field to 1,2,3,4 etc... The current data I am trying to query is for a table that looks like this:

+---------------+----------------+---------------+----------------+
|     Name      | Account Number | Accout Role 1 | Account Role 2 |
+---------------+----------------+---------------+----------------+
| John Smith    |          12345 | TaxSigner     |                |
| John Smith    |          12345 |               | TaxOwner       |
| Tim Anderson  |         678920 | TaxSigner     |                |
| Tim Anderson  |         678920 |               | TaxOwner       |
| Bill Williams |         510301 | TaxSigner     |                |
| Bill Williams |         510301 |               | TaxOwner       |
| Frank Stevens |         897090 | TaxSigner     |                |
+---------------+----------------+---------------+----------------+

But I need it to look like this:

+---------------+----------------+---------------+----------------+
|     Name      | Account Number | Accout Role 1 | Account Role 2 |
+---------------+----------------+---------------+----------------+
| John Smith    |          12345 | TaxSigner     | TaxOwner       |
| Tim Anderson  |         678920 | TaxSigner     | TaxOwner       |
| Bill Williams |         510301 | TaxSigner     | TaxOwner       |
| Frank Stevens |         897090 | TaxSigner     |                |
+---------------+----------------+---------------+----------------+

There could be some customers with a single role while others could have both. I am able to merge the duplicate rows into a single column but that won't work. I need each role to end up in a separate column.


texts: MySQL Partitioning: Is there a performance tradeoff between number of partitions and size of each partition?

texts:
I have a large table (several 100 million rows) that I would like to efficiently partition. My question is whether there is a tradeoff between partition size and number of partitions. As far as I understand, most queries on a column used in the partition will be faster because the query will (for most queries) only have to search within the partition applicable to the query. Thus, it would make sense that, in order to maximize efficiency, you should divide a large table into the maximum number of partitions, therefore, making each partition as small as possible. In the case of MySQL, this means 1024 partitions. But is there any performance drawback to having a large number of partitions? Is so, how does one find the optimum number of partitions?

Note: There is a somewhat similar question on stackoverflow already, but only one answer, which (from my perspective) misses the mark. So I will state the question in my own way...hopefully it is more clear


texts: How do I identify the natural key of a table?

texts:
In my mind, a natural key is like a primary key and thus should identify a unique row in the table.

The specific situation that I'm working on is that a bunch of duplicate data has been inserted into our tables. This is because the tables have no primary keys. I'm now need to know how to systematically identify duplicate records as well as a natural key to create a unique constraint.

Any info is appreciated!


texts:
Just stumbled upon this stackoverflow question: https://stackoverflow.com/questions/13648898/sql-update-with-row-number. The second answer struck me as odd:

UPDATE x
SET x.CODE_DEST = x.New_CODE_DEST
FROM (
      SELECT CODE_DEST, ROW_NUMBER() OVER (ORDER BY [RS_NOM]) AS New_CODE_DEST
      FROM DESTINATAIRE_TEMP
      ) x

Here, a column CODE_DEST is updated with the value of ROW_NUMBER(). However, x.CODE_DEST itself is the result of a select query. So, I'm updating the result of a select query. Is this common practice?


texts:
Another accidental DBA question. We have a DB instance that host 10 databases. This DB server doesn't have any maintenance plan set up. I want to set up a decent maintenance plan that will do a full database back up everyday and do an hourly transaction log back up. I tried clicking on new maintenance plan button from SSMS and it looks like the SQL server agent is not configured. I plan to enable this by running the following

 sp_configure 'show advanced options', 1;
 GO
 RECONFIGURE;
 GO
 sp_configure 'Agent XPs', 1;
 GO
 RECONFIGURE
 GO

Then I plan to use the Maintainence plan wizard and set up my plan. Am I following the right route>? Do I have to watch for something while setting up the plan? Your advise is appreciated. Thanks


texts:
This task was given to me in a programming exercise. I have already submitted my solution, but I didn't really manage to answer this problem in a good way. Since I want to learn I ask the question here in hopes that someone might help me figure this out. I have changed the wording of the task and the column names in hopes that this will not be the first answer on Google for other people trying to solve this exact problem.

I have this table of transactions, and I want to fetch the top 3 sellers for the top 5 items, for the past 10 days. By "top" I mean the sellers with the most transactions, and the items that have been sold the most times (most transactions).

+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment |
| item       | varchar(255) | NO   | MUL | NULL              |                |
| seller     | varchar(255) | NO   |     | NULL              |                |
| created_at | datetime     | NO   | MUL | CURRENT_TIMESTAMP |                |
+------------+--------------+------+-----+-------------------+----------------+

Is there a way to get this data in 1 SQL query. Keep in mind this is using MySQL. I think this should be solvable with Postgres with window functions, but I don't know them that well.

Currently my solution is to in a loop of days, do 1 query for each day getting the top 5 items for that day, then for each of those item, find the top 3 sellers. This results in 10*5*3 database queries which is not good. Surely this must be solvable in a more efficient manner.

I was testing with fetching a list of top items grouped by date by counting the items. And then fetching the list the top sellers grouped by date and items.Which potentially could solve this with just 2 queries. But I can't find a way to limit the number of results inside a group. So I end up with a list of all items for a given day, not just the top 5 items. And the same with sellers.

Any one now how to solve this efficiently?


texts: Backing up multiple databases to the same point in time

texts:
Usually when we start the backup we do not allow changes to commit or database will be not be accessible. I mean database will be in single user mode but I want to start the backup and release the db for use. Also, once I start the backup I do not want the ongoing changes to be written to the backup file.
I want to know how I can achieve this in Microsoft SQL Server 2012.Please assist me.

Well, let me explain my problem first. Currently I am setting the database to single user mode until the backup completes. This mode will serve my purpose of avoiding the data changes when backup is in progress. But my application is tied with the multiple databases(each databases are interlinked with each other and there are var dbs which keeps creating on monthly basis). So backing up all these databases has become tedious process and more importantly I have to keep the users out of the system when backup is in progress.

So I am looking for backup mechanism which will meet below mentioned requirements.

Start the backup of all the dbs at a time and release the db for use.
As databases are inter linked to each other I want to have data consistency maintained in backup files. So because of this data consistency requirement I don't want on going changes to be committed to my backup file.

All I want is - backup of all the dbs at a given time.


texts:
I'm trying to execute the following script in SQL Server Management Studio:
USE [master]
GO

CREATE DATABASE [test1] ON PRIMARY (
  NAME = N'test1',
  FILENAME =
    N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\test1.mdf',
  SIZE = 70656KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)
LOG ON (
  NAME = N'test1_log',
  FILENAME =
    N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\test1_log.ldf',
  SIZE = 164672KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

But I'm getting the error:

Msg 5123, Level 16, State 1, Line 2
CREATE FILE encountered operating system error 5 (Access is denied.)
while attempting to open or create the physical file
'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\test1.mdf'.
Msg 1802, Level 16, State 4, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Already have all role permissions for my user, any ideas on what's wrong?


texts: Postgres table contains 1 column, and that column is PK. How to insert new record?

texts:
In my application, I have an abstract base class, and 2 derived classes.  The number of derived classed will probably not increase.  If it does, it won't be more than a few.

The class structure looks like this:

abstract class Base
{
    int FirstParameter
    int SecondParameter
    abstract void DoSomeWork();
}

class Foo : Base
{
    int FoosParameter
    override void DoSomeWork(){...}
}

class Bar : Base
{
    override void DomeWork(){...}
}

Essentially, Bar only needs the data provided by Base, but does its own work on the data.  From a data standpoint, Bar and Base are the same.  Foo, on the other hand, has some extra data tacked on to it.

The database has 3 tables, one for Base, one for Foo, and for Bar.  Since the number of subclasses is not going to grow significantly, it makes more sense to give each class its own table.

tblBase:

base_id PK,
first_parameter int not null,
second_parameter int not null,
foo_id int,
bar_id int

tblFoo:

foo_id PK,
foos_parameter int not null,

tblBar:

bar_id PK

tblBar consists of 1 column, which is the auto incrementing primary key.  The primary key is stored in tblBase in column bar_id, which will identify any Base as being a Bar.

Now, for the real kicker:  How does one insert a new record into tblBar, when the only column is the auto incrementing primary key?  I suppose I could do

insert into tblBar(bar_id)
select max(bar_id) + 1
from tblBar

but that seems a bit....clunky.


texts: MySQL Query to get rank of a student of specific class divison or group

texts:
I just stuck in one area and I am glad if you help me out of this problem.
My problem is similar to Get the rank of a user in a score table
But here is twist you already mentioned how to use WHERE Clause but if I want to join another table with this current table for add different condition in WHERE clause then how can we write INNER JOIN another_table_name ON first_table_name.table_column = another_table_name.table_column in following your conditions:
Where I can write this INNER JOIN another_table_name ON first_table_name.table_column = another_table_name.table_column and in where condition we can use then another_table_name.table_column='' so please suggest me how can we use in following query
SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC )
FROM scores )
) AS rank
FROM scores
WHERE name =  'Assem'


texts:
Description:

Machine 1: Linux, PostgreSQL 9.3, on local, 10.12.10.123
Machine 2: Linux, PostgreSQL 9.3, on cloud, 104.10.124.123
Configuration (on both):
+ postgresql.conf : listen_addresses ="*"
+ pg_hba.conf : accept "trust" for both 10.12.10.123, 104.10.124.123
+ using postgresql-9.3-1103.jdbc3.jar

I am using Talend tool to ETL data from machine 1 to machine 2:

Database on machine 1 - table A - 10,000 rows
Database on machine 2 - table A - 0 rows

PSQL connection is OK. When running ETL, I am sure that client on machine 1 is connected to PG database on machine 2 , I tracked by query below

select * from pg_stat_activity

Problem:

I tested over 10 times, each of times when data is inserted about 2,000 rows (2,000 records is commited into machine 2) , with insert time about 3 minutes , I got errors:

org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:207)
        at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
        at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:136)
        at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29)
        at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:21)
        at org.postgresql.jdbc3g.Jdbc3gConnection.<init>(Jdbc3gConnection.java:24)

I would like to know how I can fix it.


texts:
I had this table:

tblA

id | name | value
1    nameA    1
2    nameA    0
3    nameB    0
4    nameC    0
5    nameC    1
6    nameC    1

As you see, Value can be 1 or 0

I need to perform a query that shows this for every nameX: nameX, count_1's, total_rows,

name  | 1's | total
nameA    1      2
nameB    0      1
nameC    2      3

I know that i can use GROUP by and COUNT.
But don't see how to put the "total_rows" in same query.

This will give me name and number of 1's.

SELECT name, value FROM tblA WHERE value = "1" GROUP BY name

How to add the last column i need?

Thanks


texts: SQL Server expensive nested loops join and lazy table spool

texts:
I am trying to tune the below query which takes 15-16 seconds no matter what value is passed in as a parameter, the query is:

select distinct d.documentpath as path, d.documentname as name, d.datecreated as created, pc.DateProcessed
from datagatheringruntime dgr
 inner join processentitymapping pem on pem.entityid = dgr.entityid
 inner join document d on d.entityid = pem.entityid or d.unitofworkid = pem.processid
 left join PendingCorrespondence pc on pc.PendingCorrespondenceId = d.PendingCorrespondenceId
 where rootid = @P0 and dgr.name in('cust_pn', 'case_pn')
OPTION(RECOMPILE)

I have updated the statistics for all the tables touched by the query (excluding the DataGatheringRuntime table which is quite big at ~100GB) and have tried re-factoring the query using a CTE but get the same execution plan and need some assistance.

The actual execution plan can be found here:

https://www.brentozar.com/pastetheplan/?id=ByUVIqlFE

It's clear from the execution plan that the problem lies with the outer input on the nested loop join specifically with the lazy table spool following the scan of the non-clustered IX_Camunda_1 index on the Document table but I have no idea how to tackle that issue and would appreciate any guidance.


texts: Oracle 11g database- performance enhancement and sql tuning methods

texts:
My production database(oracle 11g- windows platform) is growing(it's about 200 GB now) so that the risk of performance issue is also increasing.

Please give me some tuning tips used to follow by a DBA for high performance. What are the methods to follow while tuning the sql queries.

Also how to Identify the queries that are consuming considerable resources.It will be helpful if anyone can share the useful link of the sites mentioning about the same.


texts: How to find the fitting version of pgadmin III?

texts:
If you have different versions of PostgreSQL database servers you need a pgadmin III version with the respective pg_dump and pg_restore included.

I am running pgadmin III on a windows machine.

For example for PostgreSQL server 8.4.x you need pgadmin III with pg_dump 8.4.x, for PostgreSQL server 8.3.x you need pgadmin III with pg_dump 8.3.x and so on.

What I cannot figure out is how do you know which version of pgadmin III contains which version of pg_dump and pg_restore? I could not find the info on http://www.pgadmin.org/download/windows.php

I could just install some versions and try out, but maybe there is a better way.

pgadmin III V 1.8.4 comes with pg_dump 8.3.15


texts: Grouping rows by looking at two columns without considering the order

texts:
I have a table looks like this one below:

+----+---------+---------+
| ID | VALUE 1 | VALUE 2 |
+----+---------+---------+
| 01 | A       | B       |
| 01 | B       | A       |
| 02 | C       | D       |
| 02 | D       | C       |
+----+---------+---------+

So what I am trying to do is grouping them together by looking at the value1 and value2 without considering the order of this two values, which mean "A and B" is the same as "B and A".

I am looking for a result looks like this:

+-------+---------+---------+
| COUNT | VALUE 1 | VALUE 2 |
+-------+---------+---------+
| 02    | A       | B       |
| 02    | C       | D       |
+-------+---------+---------+

Does anyone has any idea about how can I get this?

Thanks a lot!

V


texts: How to restore/create linked server logins from one server to another

texts:
Here is the situation I am in. I have backup files for databases that I am using to restore them on a test server(B). The issue that I am having is that linked server logins appear to be on the master server level and I am unable to use those through a database's backup file.

I create these backup files from another server(A) and store them in a remote location. Is there a way to create a file or something with all the linked server logins from server A and use that file to restore them somehow when I use backup files to create Databases on Server B?


texts: Retrieve multiple rows from table2 based on single row from table1 in SQL Server

texts:
I have two tables and I am using SQL Server.

table1 structure is

Campiagn_Id  Campiagn_Name
1            test1
2            test2
3            test3

and table2 structure is

Campign_Id  EmalList
1           email_list_1
1           email_list_2
3           email_list_3
1           email_list_4
2           email_list_5
2           email_list_6
3           email_list_7
1           email_list_8

Now I want to fetch all the Campiagn_Id and Campiagn_Name from table1 and also all the respective EmalList from table2 in one query.

My Result will look like this

Campign_Id   Campiagn_Name    EmailList
1            test1            email_list_1,email_list_2,email_list_4,email_list_8
2            test2            email_list_5,email_list_6
3            test3            email_list_3,email_list_7

Or any other suggested output will be fine

I think my question is pretty much clear to all of you.


texts:
I want know how to delete a Windows Server Failover Cluster. My case is a bit special.

I encountered some problems while creating a failover cluster, and I stopped the cluster services then I did "evict nodes", then "destroy cluster".

Then the cluster disappears and now I can no longer recreate a cluster because it tells me that the servers are in a cluster. In this case how to delete a cluster which is not visible in server manager?

It's for SQL Server high availability.


texts: Fastest way to check if InnoDB table has changed

texts:
My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance.

My application does about 500-1000 CHECKSUM TABLE statements per second in peak times, because the clients GUI is polling the database constantly for changes (it is a monitoring system, so must be very responsive and fast).

With MyISAM, there are live checksums that are precalculated on table modification and are VERY fast. However, there is no such thing in InnoDB. So, CHECKSUM TABLE is VERY slow.

I've hoped to be able to check the last update time of the table, Unfortunately, this is not available in InnoDB either. I'm stuck now, because tests have shown that the performance of the application drops drastically.

There are simply too many lines of code that update the tables, so implementing logic in the application to log table changes is out of the question.

Is there any fast method to detect changes in InnoDB tables?


texts:
I am connecting to Oracle DB using sysdba:

sqlplus / as sysdba

What is the default password for sysdba user. I tried in net and found out the default password is change_on_install but it is not working for me.

I am interested to know if Oracle DB maintains some file or DB table where it stores the passwords.


texts: How do I move tables and indexes owned by user to new tablespaces in Oracle 19C?

texts: I have a user named SDE and the tablespace for this users tables are in SDE_TBS, I would like to move all of these tables to tablespace SDEBUS_DT.  For indexes I would like to move to SDEBUS_IX.  How would I accomplish this? I have reference this thread, but it does not address my question completely.


texts: Forced serialization on inline table valued function

texts:
Apologies if this is a stupid question. We are attempting to convert our multi-statement table valued functions (MSTVFs) to inline table valued functions (ITVFs), to avoid the forced serialization of queries.

I have the following ITVF function (watered down), but the Execution Plan still says it has a Degree of Parallelism of 1. The actual function in question has three basic SELECT statements separated by UNION ALLs.

Have I missed something? How can I avoid forced serialization here?

CREATE FUNCTION dbo.Test (@i int)
RETURNS TABLE
AS
RETURN
SELECT @i as [i];
GO

SELECT * FROM dbo.Test (2);
GO

https://www.brentozar.com/pastetheplan/?id=Hyn8o50D7

The instance has he following settings:

Cost Threshold for Parallelism = 5
Max Degree of Parallelism = 0


texts:
I need to upgrade and migrate SQL Server, SSRS, SSIS to the newest OS version and SQL Server version.

Server OS is currently 2008 R2 with SQL Server 2008 R2 Ent. installed.

My original plan was to do in place upgrades and then migrate to new server OS. It sounds like SQL Server 2017 is not supported on 2008 R2, so I'm not sure whether or not this is an option.

What is the best migration/upgrade path for server OS upgrade and SQL Server version upgrade?

Windows Server 2008 R2 and SQL Server 2008 R2 Ent -> Windows Server 2019 and SQL Server 2017 Ent.


texts: Mariabackup Throws Unknown Argument Error

texts:
I have just installed the MariaDB backup tool based on Percona Xtrabackup, Mariabackup, on my CentOS 7 system with MariaDB v10.1. However, when I try to test using this tool, it fails immediately complaining about an unknown argument.

I tried running this command as a full backup, as a full backup using the --xbstream option to pipe it through gzip, and as a full backup using gzip and OpenSSL. I also tried GPG with gzip.

Here are the commands I've tried:

With gzip/openssl:

mariabackup --user=root --password= `cat /etc/psa/.psa.shadow ` --backup     --stream=xbstream | gzip | openssl  enc -aes-256-cbc -k password123 >     backup.xb.gz.enc

with gzip/gpg:

mariabackup --user=root --password= `cat /etc/psa/.psa.shadow ` --backup --stream=xbstream | gzip | gpg -c --passphrase password123 --batch --yes -o backup.xb.gzip.gpg

With gzip:

mariabackup --user=root --password= `cat /etc/psa/.psa.shadow ` --backup --stream=xbstream | gzip > backup.xb.gz

And just straight up:

mariabackup --user=root --password= `cat /etc/psa/.psa.shadow ` --backup --target-dir=/root/sql_dumps/

And here's the output:

Info: Using unique option prefix 'backup' is error-prone and can break in the future. Please use the full name backup_encrypted instead.

mariabackup: Error: unknown argument: '$AES-128-CBC$GzzuTl0rBKRyCngfmWSypg==$JAmMp9hcbIQ/MTrzqCQ8eWIFQGixpUjZ7ESQqNLfuJM='


texts: How can I access certain information from YQL?

texts:
I notice the YQL commands such as

select * from yahoo.finance.quote where symbol in ("YHOO")

return all the data for that certain stock. I was wondering is it possible to access data from YQL based on volume or other specific data? For example is it possible to get the highest volume stock from the day?


texts: psql denies access to file when using COPY FROM

texts:
When I do this:

COPY "mytable" FROM '/my/file.csv' WITH DELIMITER AS ',' CSV;

On this:

-rw-r--r-- 1 peter peter 54819176 2011-07-21 13:17 file.csv

psql tells me this:

ERROR:  could not open file "/my/file.csv" for reading: Permission denied

How can I read my file??  Thanks!

--- Update

It looks like something called apparmor is installed by default in Ubuntu.
Seems to have the same functionality as SELinux, mentioned in the comments.

--- Update

After removing apparmor, I still have the same problem.  selinux is not installed.

Regarding the comment below about access, the COPY FROM is being run from a superuser account (it gives a different error message if not) and the file permissions, copied above, I understand as "readable by everyone".

--- Update

I tried to get to the file under the postgres user.  It gets stuck at a particular place in the tree

drwxr--r-- 6 peter peter 4096 2011-04-14 14:03 phm

postgres@dexter:/home/peter/PyPacks$ cd phm

bash: cd: phm: Permission denied

I suppose I will just put the file somewhere else, but this is strange!


texts: Are indexes unnecessary for columnar database?

texts: In a columnar database, are indexes unnecessary or useless?


texts: Enterprise Manager cannot connect to 11g database instance

texts:
In Oracle 11g when we are opening OEM in browser it is showing the following message :

"Enterprise Manager is not able to connect the database instance."
Listener status is showing Up and
Agent Connection to instance the status is showing unavailable.

please provide a solution.

I am attaching a screenshot of my problem. I still did not find any solution yet.plz help


texts:
Im taking a db backup from AWS RDS (MySQL) slave from an EC2 (m5.large - 2c/8GB) in same AZ using -

mysqldump -hxx -u xx -p --extended-insert  --single-transaction  --max_allowed_packet=1G db > db.sql

The backup dies at around 60-70 minutes with error : mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table xx at row: 1743503

The error row changes but the table is the same at which mysqldump errors out
Table has 4M records and size is around 1.5 GB
max_allowed_packet on source is 4MB (although this is inconsequential I believe)

So I tried with single table backup with pv enabled :

mysqldump -hxx -u xx -p --extended-insert  --single-transaction  --max_allowed_packet=1G db tbl_xx | pv > db_tbl_xx.sql

The pv stream was interesting as I saw ~10 MiB/s speed for sometime, then froze to 0 B/s for a few seconds. When it resumed, it hovered around 700-800 KiB/s for the rest of the time till the backup finished (1.79GB - 35 minutes). Only in case of entire DB, the backup never finished but died throwing the "Error 2013".

Interestingly, a backup from SQLyog GUI for the same table finished in around 5 minutes over the internet.

I'm unsure if the mysqldump parameters are not supporting large backups or some network level configuration is the problem here.
Some help with pointers to check on either side will be great, thanks!


texts: How can I filter my query on a left joined table, if I don't know the exact value I'm searching?

texts:
I had a hard time coming up with an appropriate title and it might be hard for me to explain the issue, but I'll try to be as clear as possible.

I have a dynamic query that is pulling assets based on their asset title or one of many "identifiers" that are attached to that asset. Every asset as a "snapshot" ID that is tied to a asset identifier snapshot table, that is then tied to a version table that holds the actual value. That relationship looks like this:

SELECT
    *
FROM
    vwAsset AS Asset
        INNER JOIN dbo.tblAssetIdentifierSnapshot ON tblAssetIdentifierSnapshot.AssetSnapshotId = Asset.AssetSnapshotId
            INNER JOIN dbo.tblAssetIdentifierVersion AS AssetIdentifierVersion ON AssetIdentifierVersion.AssetIdentifierVersionId = tblAssetIdentifierSnapshot.AssetIdentifierVersionId

The value in the tblAssetIdentifierVersion table is what I'm trying to get at. Here is what the full query looks like:

And here's a pastebin.

If I run this query without the two highlighted joins and remove the OR, it runs instantly. But adding these joins makes the query run for 25+ seconds.

When I run this through Sentry Plan Explorer, it shows that some 274K+ rows are being searched for each join, before it finally filters down to the 137 it returns.

This is the query columns that sentry plan explorer shows:

I'm not an expert, so take this with a grain of salt, but every looks mostly okay. I don't think anything can be done about index scans, right?

Is there any way to filter down the left joins more without knowing the snapshot ID or the versions that are tied to the asset?

Here's the execution plan: https://www.brentozar.com/pastetheplan/?id=HJtsYtGLe


texts: FDW, replication and CREATE SERVER - will it work?

texts:
We have two servers (master, slave) and we want to use the FDW. The two databases to be connected with the FDW are both on the master (and the slave of course).  When we are to CREATE SERVER we would run something like:

CREATE SERVER server_db2 FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'master', dbname 'db2', port '5432');

The question is this:  Does the slave create the server_db2 and connects to the master's IP address?  What will happen if I break the relationship and the slave is promoted to master? I suppose it will be looking for the master to connect right?

Would connecting via 127.0.0.1 and setting the method to something other than trust be a safe option?

In general the behavior of the postgres_fdw is unclear to me in a replicated environment.


texts: sql server 2014 unable to connect with Microsoft account or sa

texts:
I am running sql standard 2008 r2 on my box. and  its been working great.
I just installed sql server 2014 on my machine as a second instance.

The service is running fine, but when I try to log into it with my sa password, I get an error about the login failing.

Assuming I maybe messed up the password on install, I uninstalled and reinstalled.
Same issue.

The weird thing is that when I try log in with windows authentication, it also fails

yet I can log into my 2008 R2 instance with windows authentication, no problem

Something is bugging me that its not necessarily the log in, but maybe how the service is logging in, but it is logged in exactly the same way as the 2008r2 service.

Full error message is here

Server Name: CRAIG-PC\MSSQLSERVER2014
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536

Thanks in advance

Full screen grab of services

browser is running from this path
    "C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe"


texts: What are the different types of binary relationship types?

texts: I'm new to databases and while studying questions on it, I've come across one that I can't find the answer to. Define the three different binary relationship types that are common in relational database design. Provide one example for each relationship type. I have searched for it but can't seem to find it, I just keep running into cardinality and different relationships types i.e. unary etc. Thank you for any help you can give.


texts: Database Roles Security: In SQL Server architecture where does permissions are evaluated in RE or SE or Access methods

texts:
I got a question if i create a login and database user for a database named TEST, and i have given permissions to user as db_datareader only.

1) Where does this permission evaluated when a user connects and runs a DML query?
2) If permission is checked during early stage of Relational engine will execution plan be created and stored in cache?
3) My answer is transaction manager in storage engine which controls the locks and permissions am i right?


texts: Why are collations specified at the column level?

texts:
The charset defines how the row data is saved into the file store.

Row 17: <CHARSET('utf8', 'hello'), 17, CHARSET('latin-1', 'sup')>

But it seems that collation only affects sorting of these rows.

So why is the collation specified when you are defining the column?

Instead I would expect that you would only specify the collation when defining things that matter with sorting rows, i.e. indexes (because they are stored sorted), queries (because they test equality and inequalities) and constraints (because they are effectively queries).


texts: Why is MySQL InnoDB table copied despite using ALGORITHM=INPLACE for ALTER TABLE

texts:
I run an ALTER STATEMENT on an InnoDB table on a MySQL server version 5.7 that:

drop some foreign key constraints
drop some indexes (non unique)
add some columns (without index or foreign key constraint, but they are not added at the end of table, I use the AFTER clause)

I use the ALGORITHM=INPLACE option. But the table is still being copied, I see the temp file created in the database directory. According to the DDL operations documentation, all these operations support INPLACE modifications. Why is my table being copied anyway? I didn't get any error message.


texts: How to sum a value in a grouped set of results? SQL Server

texts:
So i need to sum the value "amount" where where debit_id and transfer_id are equals:
Table Example:

debit_id | transfer_id | amount | debit_type

1184685  |    861288   |  25.5  |     2

1184685  |    861288   |  59.5  |     2

1168516  |    861288   |   23   |     0

Right now i use this query thaht gives me almost the expected values

    WITH ct AS
  ( SELECT *,rn = ROW_NUMBER() OVER (PARTITION BY debit_id, transfer_id
   ORDER BY id DESC)
    FROM transfer_debits
  )
SELECT debit_id, transfer_id, amount, debit_type
FROM ct
WHERE rn = 1 AND transfer_id = 861288
ORDER BY debit_id, transfer_id;

I need this result but with the sum of the field amount grouped by debit_id and transfer_id
Desired result should be:

debit_id | transfer_id | amount | debit_type

1184685  |    861288   |   85   |     2

1168516  |    861288   |   23   |     0


texts: Is it possible to make simple postgres dump using replication server?

texts:
PostgreSQL 9.6, configured backup server via barman tool. Using WAL streaming (pg_receivexlog) and streaming backup (pg_basebackup).

All working good. However for development/debugging purposes I may need to create simple dump and copy it to localhost to inspect.

On master server I can simply use pg_dump tool. But I don't want to use it under heavy production load. So I was thinking - is there a way to create dump from our backup server?

Only one way I can think about: apply barman recover command to different server (not production), then there create simple dump via pg_dump. That will work, but isn't there a way to ditch intermediate server?


texts: What if the lowest granularity level can legitimately be null?

texts:
I have a normalized database that I'm going to be using as a source for a dimensional data store; one of the facts I'm collecting is Orders, the normalized schema looks roughly like this:

However when a new order is created in the source system, it doesn't have sizing information and so, there is no record for it in the OrderDetailUnits table.

I'm planning to make an Orders source table that flattens these tables into a single, denormalized table, but then I won't have a lowest-level record for quite a lot of the latest order data... which is expected as far as business is concerned.

How should I structure my Orders facts? Making separate "AllOrders" and "SizedOrders" fact tables / cubes would be annoying for the business users.


texts: Can I make Postgres print more lines of context for a PG::SyntaxError?

texts:
When provisioning our database in development, we are feeding in very long SQL statements to declare numerous functions. Sometimes, we make a mistake and there is a syntax error, so we get output like this:

PG::SyntaxError: ERROR:  syntax error at or near "BEGIN"
LINE 14782: BEGIN
            ^

That's fine, and we can eventually find the problem, but it would be really helpful if Postgres would emit a couple of lines before and after the problem instead of just the line with the problem. Is it possible to configure Postgres to print more lines before and after a PG::SyntaxError?


texts: Why is my RDS mysql slow query log full of server restarts?

texts:
One of my RDS mysql databases is giving me warnings about the slow query logs taking up significant space.

The logs are rotated once an hour, and contain a dozen repetitions of:

/rdsdbbin/mysql/bin/mysqld, Version: 5.6.13-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument

What could be causing this?


texts: Is it possible to migrate a postgresql database without downtime?

texts:
I have a Postgresql Database on my Centos, I use barman to backup my database, my question is that is it possible to migrate my database to another server without downtime?
I know some solutions with small downtime with pg_start_backup(), barman also can handle it, but is there anyway to do it without down time?
And one more question is, what is the best way to migrate it?


texts:
The Many-to-many (data model) Wikipedia article uses the example of Authors and Books:

  For example, think of A as Authors, and B as Books. An Author can
  write several Books, and a Book can be written by several Authors.

  In a relational database management system, such relationships are
  usually implemented by means of an associative table (also known as
  cross-reference table), say, AB with two one-to-many relationships A
  -> AB and B -> AB. In this case the logical primary key for AB is formed from the two foreign keys (i.e. copies of the primary keys of A
  and B).

With constraints, how would you enforce that a Book must have at least one Author?

Notes:

For this example, there is no such thing as a "primary" Author for the Book.
Answer in SQL (any RDBMS) or in general terms.


texts: Can I check the services currently running through SSMS?

texts: I want to check which services has started and which has not by running a script/query on SSMS.


texts:
0 down vote favorite

I have a DB with 150GB of data. I am using the MongoDump and Mongorestore methods to backup and restore.

My production server is running Mongo 2.2 and my test server is 2.6.1

When I take a back up from the Mongo2.2 production server it's taking a long time to complete. The restoration take 6-8 Hours. It hasn't completed without error. Sometimes the restore is dropped automatically and we need to run the restore again or restore the missed collection.

Is there a better way to to take a backup and restore method, where we can save time and run it without Errors?

Regards,

Rishi


texts: Converting rows to columns using crosstab in PostgreSQL not working (relation "table" does not exist)

texts:
I need to use the compiled data using CTE and then convert the columns to rows using crosstab(open to other ideas) in the next select statement. Below is the query.

  with checked_adgroup AS (
     SELECT
        ua.new_adgroup,
        ua.account,
        ua.campaign,
        ua.ad_group,
        ua."position",
        cp.category,
        pt.full_value,

       FROM unnest_adgroup ua
         LEFT JOIN taxonomy_category cp ON ua."position" = cp."position"
         LEFT JOIN taxonomy pt ON ua.short_val = pt.short_value AND cp.category = pt.category AND (pt.lob IS NULL OR pt.lob = ua.lob)
    )

SELECT *
from crosstab(
'select
cad.account,
cad.campaign,
cad.ad_group,
cad.category,
cad.full_value
FROM checked_adgroup cad
WHERE  cad.all_correct AND cad.category IS NOT NULL
ORDER BY 1,2,3')
AS final_result(
 account text, campaign text, ad_group text,
 division text, lob text, match_type text  )
;

Error message:

ERROR:  relation "checked_adgroup" does not exist
LINE 7:     FROM checked_adgroup cad

Output of checked_adgroup cte looks like below

Desired output of the final statement is:


texts: Using Mirroring for incremental backups?

texts:
We have a machine tool application that runs on a SQL server backend.  These tools run all day every day and I am looking for a way to incrementally backup the database without any machine downtime.

I've seen mirroring solutions with two servers, but these solutions would not work for us do to cost and size constraints.  Is it possible to programmatically or through some service to mirror this database to a backup hard drive on the main PC?

Some data loss is acceptable.


texts: calculate days spent of all visitors from encounters

texts:
I have an encounters table

CREATE TABLE visitors(
 id serial PRIMARY KEY,
 start_date TIMESTAMP NOT NULL,
 --  visitor_id bigint NOT NULL -- skip for now,
 end_date TIMESTAMP
);

(end_date can be null when visitor is still inside)

and two dates(start_dt and end_dt) are provided on which I need to calculate how many total days all visitors have spent in a certain given date range. eg

if from jan 1 2019 till jan 7, 100 visitors were present, these are 7 * 100 = 700 visitor days
if on jan 1, there were 10 visitors and on jan 2, there were 20 visitors then 1 * 10 + 1 * 20 = 30 visitor days for these given dates

Note: dates will be inclusive

Any help in building this query is very much appreciated(I am not able to achieve this query. I am also newbie in this area). thanks

Edit: So far I have tried/reached this

(WITH dates AS ( SELECT start_date, COALESCE(end_date::date, '2019-01-01') AS end_date FROM "visitors"),
    calendar AS (
    SELECT
    i :: DATE AS DATE,
    date_trunc( 'month', i ) :: DATE AS MONTH
    FROM
    generate_series ( '2018-01-01' , '2019-01-01', '1 day' :: INTERVAL ) i
    )
    SELECT COUNT ( * ) days FROM dates d
    INNER JOIN calendar C ON C.DATE BETWEEN d.start_date AND d.end_date);

Edit: I see some answers e.g age difference but these calculate age based on difference and not both dates being inclusive


texts:
I was editing some stored procedures in HeidiSQL on a mySQL database. I changed 2 or 3 of them, then I opened one more to edit and when i opened it I got an error saying "You have no privilege to this routine.". Now I get the same error on every stored routine I open, even ones I just opened.

When I run SHOW GRANTS I get this result.

  GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `ranktracker`.* TO 'ranktracker'@'%'


texts: Maintenance Plan Wizard generates invalid date parameter in script for delete

texts:
The generated SQL for my Maintenance Plan is

EXECUTE master.dbo.xp_delete_file 0,N'''',N'''',N''2019-01-02T10:44:21''

but the execution returns error message

  Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '2019'.

It dislikes the date parameter! I think it is to do with the fact my server is in Australia not USA, and we view dates differently!

Any ideas how I overcome this bug?


texts: I Want to Become a Database Administrator - How Can I Start?

texts:
I just took a database course in my college and found it fascinating. I want to learn more in this field, but I feel a bit lost with how to start.

How do I become a database administrator? What are some tips and tricks to get into the career field? I see a lot of job postings asking for 5 or more years of experience and am not sure how to start.

Are there entry level jobs I should look for? What kind of skills, traits or certifications would help me out or help me get more experience? I'm mostly interested in MySQL.


texts:
Is there an option to use Oracles online table redefinition feature but only copy a subset of the data from the source table?

I mean - the source table has 2M records and I want to only copy those that have ID > something. The problem is that START_REDEF_TABLE seems to sync all 2M records. Is there a way how to specify whics records should be synced over?


texts: How to Keep Audit Data and Effectively Query Large Tables

texts:
Thanks for reading this.  I have two types of data - demographic data and transactional data.  The demographic data is only being stored when it changes. I’m trying to keep an audit trail for the demographic data at the same time. The transactional data is being aggregated.  Both tables are keyed on Account Number and Date.   A greatly simplified example might make it clearer:

Acct#, Name, Phone#, EffectiveDt

123, John Smith, 123-132-1232, 1/1/2019

321, Mary Smith, 123-232-3121, 3/1/2019

123, John Smith, 432-123-4322, 5/1/2019. — two records for John Smith, the second containing the most recent data

Acct#, MonthEnding, Sales

123, 1/31/2019,5000

123, 2/28/2019,2000

123, 3/31/2019, 500

321, 3/31/2019,2008

123, 4/30/2019, 5009

321, 4/30/2019,2002

123, 5/31/2019, 5095

321, 5/31/2019,2000

So I’m achieving the audit history by keeping two records for John Smith in the demographics file but the effective date will be different than the effective date in my transaction file.

I am concerned about the design because I would have to select for max(effectivedt) and account number as part of getting both Demographic and transactional data.   The demographics file is really a set of five tables and with hundreds of thousands of records.    I’m concerned that my queries down the road might be too slow with this approach but there are a lot of aspects to this strategy that really appeal to me.  The primary one being that I want to avoid a separate set of audit tables to manage.

I did see this question but I wanted to check with others about my specific situation as I’m trying to avoid a separate set of tables if possible and I had hoped that others might have ideas or experience with this problem:  Data Change Audit Plan

Any thoughts or input would be greatly appreciated.


texts: Consultants using old version of Hibernate ORM for new application - should I be concerned?

texts: My company currently has consultants developing a Java-based application. They are using Hibernate ORM 3.5 (released 5 1/2 years ago in 2010) in this application. The back-end is SQL Server 2012. Should my company be concerned that they are using such an old version of Hibernate? Browsing the Hibernate change logs, I see various performance fixes over the years, but I don't know how much of a difference these make, and if it's worth putting pressure on the vendor to update to a newer version. As a production DBA, my main concern is performance. What do you think?


texts: SQL Backup with Mirror option in maintenance plan

texts:
I need to do backup ALL databases on an instance and put them in 3 different locations on our network, so i found that you can use backup with mirror, so I set that up.

I have a maintenance plan which runs a job (see job details below).
The problem I have is that if I add a database to the instance I will have to manually add it to the T-SQL job, is there a way I can avoid that.

I know you can have a step in the maintenance plan to backup all databases but you cant have it with the Mirror option (or can you?)

Can i get the maintenance plan to run my job on every database?

My job details: (I know there is only one database at the moment but I am just testing at the moment)

EXECUTE master.dbo.xp_create_subdir N'M:\Backup\DBA Tools'
    GO
    EXECUTE master.dbo.xp_create_subdir N'\\uhssql\sqlbak$\SQLDS\DBA Tools'
    GO
    EXECUTE master.dbo.xp_create_subdir N'\\sqlds02\backupprod$\Daily Backup\SQLDS\DBA Tools'
    GO

    DECLARE @BackupPathandDate VARCHAR(150)
    DECLARE @BackupPathandDateMirror VARCHAR(150)
    DECLARE @BackupPathandDateMirror2 VARCHAR(100)

    SET @BackupPathandDate = 'M:\Backup\DBATools_' + REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 100),':',''),' ','') + '.bak'
    SET @BackupPathandDateMirror = '\\uhssql\sqlbak$\SQLDS\DBATools_' + REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 100),':',''),' ','') + '.bak'
    SET @BackupPathandDateMirror2 = '\\sqlds02\backupprod$\Daily Backup\SQLDS\DBA Tools\DBATools_' + REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 100),':',''),' ','') + '.bak'

    BACKUP DATABASE [DBA Tools]
    TO DISK = @BackupPathandDate
    MIRROR TO DISK = @BackupPathandDateMirror
    MIRROR TO DISK = @BackupPathandDateMirror2
    WITH FORMAT

Many many thanks

Neil


texts: SQL Server 2005/2008 logging connections

texts:
How can I audit SQL connections to the database from

Windows credential
Sql Users

Is there some built-in feature where I can determine number of:

all connection
active connection
unclosed connection
current status
historical prevue

I hope that I'm going right way. I have application that takes all RAM to the SQL Engine and transaction logs raising to the unbelievable size on HDD durring one-two days.


texts: SQLite constraint violation - How to insert data to this schema?

texts:
First of all, sorry for the question but I was hours searching for a solution and the only things that I got are Java exceptions. I'm developing a little web crawler for movies, so I created this two tables.
CREATE TABLE IF NOT EXISTS movies(
    movie_id INTEGER PRIMARY KEY,
    title VARCHAR(100) NOT NULL UNIQUE,
    info VARCHAR(250)
);

CREATE TABLE IF NOT EXISTS genres(
    genre_id INTEGER PRIMARY KEY,
    title VARCHAR(50) NOT NULL UNIQUE,
);

Well, as you can see is a many to many relationship, so I made a junction table like this:
CREATE TABLE IF NOT EXISTS movies_genres(
    movie_id INTEGER NOT NULL,
    genre_id INTEGER NOT NULL,
    PRIMARY KEY(movie_id, genre_id),
    FOREIGN KEY (movie_id) REFERENCES movies(movie_id),
    FOREIGN KEY (genre_id) REFERENCES genres(genre_id)
);

And I'm trying to insert data with (arg are the method arguments):
INSERT INTO movies_genres(movie_id, genre_id)
SELECT m.movie_id, g.genre_id FROM
SELECT (movie_id FROM movies WHERE title = arg) AS m
CROSS JOIN SELECT (genre_id FROM genres WHERE title = arg) AS g;

But I get this SQLITE_CONSTRAINT]  Abort due to constraint violation (UNIQUE constraint failed. I don't know where is the error, maybe is in the schema but I can't see the error. Any idea on how fix it?.


texts:
I am using MariaDB Version 5.5.56
The database was restarted 2 days ago but I am not seeing any log entries in the error log except an error importing from a dmp file. The data seems complete and all processes and transactions are occuring correclt just no error logging. Is there a way to turn this on? I have tried "flush error logs" command but still no log entries.


texts:
Hi I Inserted A Select MaxID+1 Data and want to retrieve what was that ID I inserted the Following is the codes

Im trying to Insert data from Excel to Ms Access and Automatically Retrieve that Data.
For MultiUser Purpose I need to retrieve specifically the data I inserted.

Sub PostData()
Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rst As ADODB.Recordset 'dim the ADO recordset classe here
Dim dbPath
Dim x As Long, i As Long

'add error handling
On Error GoTo errHandler:

dbPath = Sheets("Sheet3").Range("h1").Value

Set cnn = New ADODB.Connection

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

Set rst = New ADODB.Recordset 'assign memory to the recordset
Sql = "INSERT INTO DvID(DVnumber)SELECT Max(DVNumber)+1 FROM DvID "
rst.Open Sql, cnn
Sheet3.Range("A2").CopyFromRecordset rst
rst.Close
cnn.Close

Set rst = Nothing
Set cnn = Nothing

On Error GoTo 0
Exit Sub
errHandler:

Set rst = Nothing
Set cnn = Nothing

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Export_Data"
End Sub


texts: What is the difference between UPDATE ... LIMIT and SELECT ... FOR UPDATE?

texts:
I have read an article, there is one sentence about MySQL:

  As one might expect, lock contention is an issue and we use several
  strategies to mitigate it including a separate table for each priority
  level , use of UPDATE … LIMIT instead of SELECT FOR UPDATE for the
  dequeue selection query, and carefully tuned schemas and secondary
  indexes to fit this type of workload.

So, what is the difference between UPDATE ... LIMIT and SELECT ... FOR UPDATE when considering lock?


texts: Is there a way to ask pgbouncer to log any query that generates a result set larger than, say, 5MB?


texts: Backup the whole SQL Server databases in one .bak file - SQL Server 2005

texts:
First I asked my question here:
Migrate all databases from one SQL Server 2005 to another

Many friends replied to it and I gathered lots of useful information.

I have another scenario in which we can have some hours of downtime. We want to close the SQL Server port on the firewall, backup the databases, move the backups to new server, restore them on our new server.

It can be achieved by creating backup of all databases including master DB. But in this case we will have 200 .bak files.

Is there any way to have a general .bak file including all of the data on our SQL Server? So we will easily restore this big .bak file in our new server?

Is there any internal option in SQL Server 2005 to restore the whole date on our new SQK Server 2005?

Thank you for your replies.


texts: SSRS dynamic data retrieved from another dataset

texts:
I want to get data(dynamic)according to the data in another dataset and put the result in textbox of the different dataset.
Right now, the data I get is static and I tried to use many expressions but no avail.
Problem lies in editing textbox expression. Since I am getting data from different dataset, it always ask me to have dataset aggregate function in my expression. If I specified the dataset from where I am getting the data, then, I cannot make it dynamic- and i get the undesired result.
And another way out that I attempted is merging the data sets into one rather than having multiples and use it only. But unsuccessful.
How can I make it work to get the result that I want.

By the way, the textbox and the tables are in different scope. I have tried to combine them in one scope too.

Please shed a light on it.
Thanks in advance!


texts: Can't deploy SSIS package - The certificate chain was issued by an authority that is not trusted

texts:
I have an Azure Windows Server in a managed Domain (AD Domain Services) running SQL Server Enterprise.

I am trying to simply deploy a local (laptop) SSIS project to the remote SSIS server. When the deployment dialog appears and I enter the IP address of the VM, I get the dreaded:

  A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

Importantly/Potentially related, I am using "Active Directory Password Authentication" and passing the credentials of a domain user (on the remote AD domain) which is an admin on the VM.

I tried also using SQL Authentication, but you cannot deploy a package using SQL Authentication (SSIS requirements).

I have searched long a wide for a solution to this, and a lot of hints point to changing a connection string, which doesn't apply to me since I'm using the VS2017 SSDT deployment wizard (no advanced options either).


texts: Move Oracle data to and from MySQL with the help of PHP?

texts:
My application is in PHP which by default uses MySQL db. I have connected MySQL & Oracle dbs with PHP.

Now I want to move data from Oracle db into Mysql db through PHP. Is it possible to do? Is it necessary for me to somehow sync between the two databases as we need to have our backup on the Oracle db.

How do I do this with the help of PHP?


texts:
I got a  call today from the platform owner, there was a bug in the code and some records got deleted over 2 weeks (3 records every day on average). He gave me a list with the records and when they got deleted but the time is in HH:mm. I restored one backup to this time also 1 min before but still didn't find this record. He said there is a chance the record got created and deleted at the same time.

I feel I am dead in the water. Can anyone help?


texts: ASP.NET & SQL Server 2012 timeouts once a month

texts:
We have an intranet site which works fine, except one day a month.

There are slow queries, unoptimized tables, tables with ~8 million records, etc. but daily usage is fine, although some functionality could be faster. The database server has 180GB RAM, 16 cores; it's a very fast server. The website is on another very decent server, with a very fast connection between.

Employees need to create a lot of invoices on the last day of each month and that's when it goes sour. On the last day of each month, people get a lot of time outs, a lot of pages which only run SELECT FROM TABLE will fail to load because of timeouts.

While people are getting the timeouts, I turned on Activity Monitor, and the CPU is at 20%, there are 300 calls per second, which is really not much at all for that server. On daily usage it's 100-200 calls per second and it works fine. I ran sp_who2, sp_who3, they don't report any locks. I have also enabled deadlock report, to report anything for more than 20 secs and it doesn't report anything.

Some queries have 7000 different plans, although that's bad it doesn't explain timeouts. We have a lot of SELECT queries without a NOLOCK but that doesn't explain why pages with SELECT only time out.

We get very low physical reads, since RAM is 180GB, most of the data is in RAM, so slow disk is not the problem. Network is not the problem, since two servers (database server and web site server) have a very fast connection between them. We backup the database every 15 mins so every 15 mins I see a high I/O activity, but that happens on other days as well without a problem.

The only oddity I noticed was that when I ran sp_who there were 300 Waiting_Command state connections, that's much lower on other days.

Do you reckon there's anything else I could check? The problem is we only have a few hours once a month when the time outs occur so we have very limited time to check what's going on, at other times it's OK. I wrote a JMeter script to reproduce the problem but it's not the same as 300 people creating invoices at the same time. We're thinking of contracting a DBA but I'm not sure even if a DBA could do anything with what happens once a month for a few hours.

Any ideas?

Update:

I checked sys.dm_os_wait_stats every few minutes but I couldn't see anything out of the ordinary. But as @AaronBertrand suggested I'll look into that.
We use datareader and datasets but we don't do any heavy processing with the reader results.
It happens on the last working day of each month so it's a different day every month.
I'm new here and I was told this has been happening every month for the past 10 years at least. 10 years ago we probably had fewer employees but also slower servers.
There's nothing else that runs on the last day of each month.
We have 15 mins backups, report generation that runs every 15, every 30 minutes etc. but they don't cause a problem on other days.
Both datareader and datasets are used, but datareader iterations are used to get total of all items, etc., just quick loops, nothing like reading a file for each record.
There doesn't seem to be anything else running on either servers.
This happens on last working day of each month so it's a different day each month.


texts: MS Access Query summarizing 2 tables doesn't produce expected output

texts:
I cannot successfully create a query in MS Access 2016 that correctly summarizes data from 2 tables with similar but not completely identical fields.

I have a fruit market. I buy each type of fruit once a season. I dispose of bad fruit multiple times each each season. I separately track purchases and disposal of bad fruit.

I wish to create a query that summarize the net fruit volume (purchased - bad) for each year and fruit.

Table of Purchased Fruit:

ID  CYear   Fruit   Purchased
3   2017    Apple   50
4   2018    Apple   30
5   2017    Pear    50
6   2018    Pear    60

Table of Bad Fruit

ID  CYear   CMonth  Fruit   Bad
2   2017    11      Apple   3
3   2017    12      Apple   20
4   2017    8       Pear    15
5   2018    2       Apple   10

This is my desired query output:

ID  CYear   Fruit   Purchased   Bad Net
1   2017    Apple   50          23  27
2   2017    Pear    50          15  35
3   2018    Apple   30          10  20
4   2018    Pear    60          0   60

The "Net" field is calculated from "Purchased - Bad".

I cannot create a query or union that matches the desired output. Most perplexing, because there is no record of "0" bad Pears in 2018, a query doesn't even select the 2018 Pear purchase.


texts: Is there a way to measure replication lag in MySQL with a resolution less than 1 second?

texts:
Is there a way by which you can measure replication lag in MySQL with a resolution of less than 1 second?

That is, can replication lag be measured on the microsecond or millisecond level?


texts: AlwaysON Availability group query very slow

texts:
I am running below tsql to find the secondary AlwaysON servers from the primary replica :

set transaction isolation level read uncommitted
set statistics time, io on
SELECT
ISNULL(agstates.primary_replica, '') AS [Primary_AG_Server],
dbcs.database_name AS [DatabaseName]
 ,ar.replica_server_name as [Secondary_AG_Servers]

   FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local <> 1 -- is_local is not 1 .. so we get secondary server names
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
    group by agstates.primary_replica, dbcs.database_name, ar.replica_server_name
    set statistics time, io off

The above query takes 12 mins and 38 secs to run and returns 34 rows. Below is the statistics IO and time output:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 10 ms, elapsed time = 10 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(34 row(s) affected)
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysguidrefs'. Scan count 0, logical reads 212, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 1266 ms,  elapsed time = 747852 ms.

Here is the link to actual execution plan.

Anything that can be done to make it faster ?

Below is from Plan Explorer

Click to enlarge

Click to enlarge

Thanks !


texts: Transform 14 columns into 2 (SQL Server 2008) - Pivot/Cursor not helping

texts:
After hours googling pivot and cursors. I couldn't think or figure out how to do this.

I have this query:

declare @banco varchar(100)
set @banco = 'CascVW'

declare @tbltemp1 table
    (
      prodt_8 float,
      prodt_9 float,
      prodt_10 float,
      prodt_11 float,
      prodt_12 float,
      prodt_13 float,
      prodt_14 float,
      prodt_15 float,
      prodt_16 float,
      prodt_17 float,
      prodt_18 float,
      prodt_19 float,
      prodt_20 float,
      prodt_21 float
     )

insert into @tbltemp1
   Select
      Round(Case When Round(Avg(Convert(float,HC08)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod08)),1) / Round(Avg(Convert(float,HC08)),1)  End,1) As Prodt_8,
      Round(Case When Round(Avg(Convert(float,HC09)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod09)),1) / Round(Avg(Convert(float,HC09)),1)  End,1) As Prodt_9,
      Round(Case When Round(Avg(Convert(float,HC10)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod10)),1) / Round(Avg(Convert(float,HC10)),1)  End,1) As Prodt_10,
      Round(Case When Round(Avg(Convert(float,HC11)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod11)),1) / Round(Avg(Convert(float,HC11)),1)  End,1) As Prodt_11,
      Round(Case When Round(Avg(Convert(float,HC12)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod12)),1) / Round(Avg(Convert(float,HC12)),1)  End,1) As Prodt_12,
      Round(Case When Round(Avg(Convert(float,HC13)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod13)),1) / Round(Avg(Convert(float,HC13)),1)  End,1) As Prodt_13,
      Round(Case When Round(Avg(Convert(float,HC14)),1)  = 0 Then 0 Else Round(Avg(Convert(float,Prod14)),1) / Round(Avg(Convert(float,HC14)),1)  End,1) As Prodt_14,
     Round(Case When Round(Avg(Convert(float,HC15)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod15)),1) / Round(Avg(Convert(float,HC15)),1)  End,1) As Prodt_15,
     Round(Case When Round(Avg(Convert(float,HC16)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod16)),1) / Round(Avg(Convert(float,HC16)),1)  End,1) As Prodt_16,
     Round(Case When Round(Avg(Convert(float,HC17)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod17)),1) / Round(Avg(Convert(float,HC17)),1)  End,1) As Prodt_17,
    Round(Case When Round(Avg(Convert(float,HC18)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod18)),1) / Round(Avg(Convert(float,HC18)),1)  End,1) As Prodt_18,
    Round(Case When Round(Avg(Convert(float,HC19)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod19)),1) / Round(Avg(Convert(float,HC19)),1)  End,1) As Prodt_19,
    Round(Case When Round(Avg(Convert(float,HC20)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod20)),1) / Round(Avg(Convert(float,HC20)),1)  End,1) As Prodt_20,
    Round(Case When Round(Avg(Convert(float,HC21)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod21)),1) / Round(Avg(Convert(float,HC21)),1)  End,1) As Prodt_21
from
    tblDadosProd
Where
    DescProjeto = @banco

select * from @tbltemp1

the results will be like this:

I need to make a TOP 3 in prodt_8 to prodt_14 and prodt_15 to prodt_21, but i can't do this with columns. So, I need something like this:

I've created a table and tried to insert, but with no success.

This cursor is not working either:

--DECLARE @AccountID INT
--DECLARE @getAccountID CURSOR

--SET @getAccountID = CURSOR FOR

--SELECT prodt_8,prodt_9
--FROM @temp1

--OPEN @getAccountID
--FETCH NEXT
--FROM @getAccountID INTO @AccountID

--WHILE @@FETCH_STATUS = 0
--BEGIN
--PRINT @AccountID
--FETCH NEXT
--  FROM @getAccountID INTO @AccountID
--END

--CLOSE @getAccountID
--DEALLOCATE @getAccountID

And this Pivot is even worse:

select u.p814, up1521
    from #tbltemp1 as u
pivot
    ( prodt_8,prodt_9)

How can i make this?

Thank you very much.

There are lots of questions here, but it helps only the asker.


texts:
Is there any way to populate a full text search index without storing the backing information in the database?

I don't mean using remote-blob-storage or FILESTREAM types. The backing information is a composite of some existing columns with the text of files on a unc share, the number of files is quite large and storing them on the database is not practicable.

I need to be able to have a record like:

| col_a  | col_b |
|--------+-------|
| ccc dd | ee ff |

Then be able to search for "ccc AND ff" without having to do a search for "ccc" and "ff" separately because there is a significant performance hit when doing this with more than 2 values (we will be frequently searching for more than 10 words across ~15 columns), additionally the value of col_b might be stored in a file on the UNC share and not actually in the database at all.

The approach I am currently thinking of is to write a custom IFilter and specify a new extension so that my table for full-text searching looks like this:

id        BIGINT
extension CHAR(3)
content   VARBINARY(8)

The content would represent the BIGINT key of another table, and the IFilter would do a SQL read get the data and then return the composite value back to the FTS indexer.

The only problem I can see from this is that if my backing data is updated this table wouldn't be and as such the FTS would not re-index the new values, but this is fixed by using an update trigger on the other table.

Can anyone see a problem with this implementation? or suggest a better one?


texts: Get last two IDs from rows for last two days


texts: PostgreSQL: Cannot change directory to /root

texts:
I am trying to copy a table planet_osm_polygon from one database osm to another test. I su postgres and performed the pg_dump.

Problem: However I'm getting the error could not change directory to "/root" and the Password: prompt appeared twice! Is there a way to perform the pg_dump when logged in as root?

root@lalaland:~# su postgres
postgres@lalaland:/root$ pg_dump -h localhost "osm" --table "public.planet_osm_polygon" |
    psql -h localhost "test" --table "staging.planet_osm_polygon"
could not change directory to "/root"
could not change directory to "/root"
Password: Password:

UPDATE

Problem #2: It appears that the table is copied into the public schema even though I passed the flag --table="staging.planet_osm_polygon". Why isn't it copied to schema staging?


texts: Fetch Data from same table name across schema

texts:
I have this multitenancy database let say of hotels. Each hotel Company is a schema in the database and all have same structure and table names.

Now I want to present menus from all available hotels. Basically I want something like

____________________________________________
| schema_name | menu_id | menu_name | cost   |
--------------------------------------------
| hotel_1     | 1       | Ugali     | 4000TZS|
--------------------------------------------
| hotel_2     | 1       | Ugali     | 3500TZS|
--------------------------------------------
| hotel_3     | 1       | Samaki    | 8000TZS|
--------------------------------------------

I can get menu_id, menu_name and cost from menus tables. How do I achieve that (if it is possible at all) ?

SET SEARCH_PATH TO hotel_1, hotel_2, hotel_3 does not work and brings results from only one schema.


texts:
We have log shipping taking place between server A and server B.

The logs are backed up every 15 minutes on server A.
Over on server B, the logs are copied every 15 minutes successfully.
The logs are restored on server B once per day at 3:35am successfully.

My issue is the alert job on server B is running every 2 minutes. It runs perfect for 45 minutes after the restore at 3:35am and then on the 47th minute, it fails and fails every 2 minutes until the next restore the next time at 3:35am

The threshold was set for 45 minutes so that part was correct.

My current considerations

This is what I'm thinking:

The copy job is working and I would get alert if it failed.
The restore job is working and I would get alert if it failed.

Questions

Why would I need the alert job? There is no way it can succeed. Since I am only restoring once per day, after that first 45 minute threshold is reached, it is an automatic failure every 2 minutes from then on.

Does this job alert on both copy and restore? What am I missing here?


texts: MySQL - query to get length of service in Years, months, days

texts:
I have the following query that I'm trying to get the length of service of a person at their company. The idea is to find how many years, months, and days they have served there.

For example:

Todays date: Jun 13, 2017.

Exhibit A: John Doe has served since Feb 10, 2016. So length of service should be 1 year, 4 months, 3 days excluding the end date.

Exhibit B: Jane Doe has served since 13th Jun, 2007. So length of service should be Or 10 years excluding the end date.

My Query spoiler: it's not right

    SELECT  `firstname` ,
            DATE_FORMAT( CURDATE( ) ,  '%Y' ) - DATE_FORMAT( startdate,  '%Y' ) - ( DATE_FORMAT( CURDATE( ) ,  '00-%m-%d' ) < DATE_FORMAT( startdate,  '00-%m-%d' ) ) AS years,
            PERIOD_DIFF( DATE_FORMAT( CURDATE( ) ,  '%Y%m' ) , DATE_FORMAT( startdate, '%Y%m' ) ) AS months,
            DATEDIFF( CURDATE( ) , startdate ) AS days
    FROM users

It gives me the results in years, as well as months, as well as days. So for someone who has served a year it would output: 1 year, 12 months, 365 days etc.


texts:
I currently have a mysql database where I keep data about rented books. When somebody rents a book, I save on the database an expiration date.

So, I want to create an mysql event to update the book status when it is not returned in time. How can I create this event?

Here's what I'm trying:

CREATE EVENT `EXPIRE` ON SCHEDULE EVERY 1
 HOUR STARTS '2016-07-16 00:00:00.000000' ENDS '2018-07-29
00:00:00.000000' ON COMPLETION NOT PRESERVE ENABLE DO DO IF
mtl_rents.dateLimit=CURRENT_DATE THEN
        UPDATE mtl_rents SET mtl_rents.status='Expired'
    END IF

But I'm getting Syntax errors on the IF.


texts:
Is there any way to check how many rows are in an SQL dump file? I have a large 32GB dump, and wanted to check how many rows are in the file.

I am using a windows machine with a MySQL server,

Thanks!


texts: How I can force the TSQL-Developers to use not deprecated TSQL?

texts:
I want to avoid the using of deprecated code.

Like the new feature Nr. 8
http://msdn.microsoft.com/en-us/library/ms178053.aspx

I want to force them to use "WITH" for example, or a semicolon at the end.

Is there an easy way to force this with ease?


texts: Oracle Error when replacing package body

texts:
I get the following error when replacing a package body:

Fehlerbericht -
ORA-00604: error occurred at recursive SQL level 1
ORA-12953: The request exceeds the maximum allowed database size of 11 GB
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
       (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
       can be corrected, do so; otherwise contact Oracle Support.

It worked before today, but not anymore. I changed the package body and added a few lines.

After getting the error I shrunk a big table to make room, as I suspected the table was eating into the 11 GB limit, so there should be enough space now.

I also restarted the database host.

What else can I do to improve the situation, or to at least diagnose the issue?

Here is a printout of the table spaces:

SYSAUX      795     5       800     1
UNDOTBS1    776     1272    2048    62
USERS       2       98      100     98
TS_DATA     6992    1824    8816    21
SYSTEM      377     1671    2048    82

Can I use ALTER TABLESPACE TS_DATA RESIZE 8G; to make things work again?
What about ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\TS_DATA.DBF' RESIZE 8G;? Which one is correct?

Update: I tried ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\TS_DATA.DBF' RESIZE 8G; and it fails because there are used extends at the end of the datafile (ORA-03297).


texts: PostgreSQL 10 - create partitions on column that needs to be calculated after importing a csv

texts:
I am in the situation where I need to create a table which also has a time column.
This table is filled with data taken from a CSV, but the csv has no time column.
Only after importing the data inside my table, I am calculating the time column, based on the other imported date and start columns.
The problem is that if the table is a partitioned table, and the partition is on the time column, when I try to import the csv, I got an error complaining that
Detail: Partition key of the failing row contains ("time") = (null).  Call getNextException to see other errors in the batch.
I understand that I could pre-generate the time column in the csv, but I was wondering if there is a method to avoid this (like automatically calculating the values of time based on the other date start column inside the table somehow).
My partiotioned table looks is this one:
CREATE TABLE mob.signals (
    db varchar NULL,
    freq varchar null constraint freq_constraint check (freq in ('daily', 'hourly')),
    country varchar(2) NULL,
    "date" date NOT NULL,
    "start" time NOT NULL,
    "end" time NULL,
    origin varchar NULL,
    destination varchar NULL,
    inc float4 NULL,
    isdiag int2 NULL CONSTRAINT isdiag_constraint CHECK (isdiag = 1 or isdiag = 0),
    "type" varchar NULL constraint type_constraint check ("type" in ('lower', 'nodata', 'nosignal', 'upper')),
    "level" varchar(6) null constraint level_constraint check ("level" in ('level0', 'level1', 'level2', 'level3', 'nodata')),
    "time" timestamp null
) PARTITION BY RANGE ("time");

The partiotions are created like so (monthly ranges on time column):
CREATE TABLE mob.signals_y2020m01 PARTITION OF mob.signals
    FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-02-01 00:00:00');

...

CREATE TABLE mob.signals_y2020m12 PARTITION OF mob.signals
    FOR VALUES FROM ('2020-12-01 00:00:00') TO ('2021-01-01 00:00:00');

When I try to import the csv, I receive the aforementioned error.
The csv looks like this:
"db","freq","country","date","start","end","origin","destination","inc","totinc","diaginc","offdiaginc","isdiag","type","level"
"XXX","hourly","xx","2020-04-23","23:00:00","01:00:00","XXX","ALL",999,999,NA,NA,1,NA,NA

Ideally, after importing the csv, I would use this query to generate the time values:
update mob.signals
set "time" = to_timestamp("date"::text || ' ' || "start"::text, 'YYYY-MM-DD HH24:MI:SS');

I need the time because I need to specify times for another application either with daily or hourly frequency in one single value (e.g. 2020-01-01 00:00:00 could either be a daily value or the first time step of day 2020-01-01).
Last but not least, I am going to create indexes on the various time columns in the partitions.


texts: Replication master binlog rotation when network is unavailable

texts:
I recently experienced an issue where the binlog file in master rotated because network connectivity between the slave and master was unavailable.

After solving the network issue, the slave was not able to follow the master as it was doing through previous binlog and position.

That was solved by purging the binlog to last binlog in master and pointing the slave to that last binlog and previous binlog's position which was following.

I am wondering if this issue is normal?