Disabling the recycle timeout settings in SSRS In order to prevent periodic recycling of the SSRS service it's been suggested to disable the <RecycleTime> setting in the RS server's config file.  I believe disabling would require setting this value to 0 (i.e., <RecycleTime>0</RecycleTime>).  Is this correct?
Also, I've heard of alternate methods of scheduling an 'empty' report to run once an hour to ensure the idle time of 720 minutes is never reached.  Is it recommended to use this approach instead of setting the <RecyleTime> setting to 0?

It's recommended to let SSRS recycle periodically so it doesn't hold onto memory it no longer needs, which may be useful to the rest of your server, for example if your SQL Server instance lives on the same server (you wouldn't want SSRS bottlenecking the rest of your SQL Server instance).
If you are able to adjust it to a recycle frequency that happens off-hours that may be best, as noted in this article. Otherwise the correct value is 0 to disable it, as you mentioned. For more information, see this other article Adjusting the SSRS service recycle time to zero seconds.


MySQL order by is equal, in what order are the results returned? I use
mysql  Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (x86_64) using readline 6.3

I have rows that are ordered by an order column.

Turns out, many times, the order number is the same, say 1. But the results are returned in a fixed order.

SELECT * from mytable ORDER BY fooOrder;

Will return results ordered by what if fooOrder is always 1?

There is obviously an order, but I don't know which one.

Consider running the EXPLAIN or EXPLAIN EXTENDED command in front of your SELECT statement. This will produce an explanation of how the query optimiser queries the database to retrieve the data you are asking it for.

Example:

EXPLAIN EXTENDED SELECT * from mytable ORDER BY fooOrder;

This output will display information about how the query optimiser went about retrieving the data.

Example output:

+----+-------------+-------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | mytable     | ALL  | NULL          | NULL | NULL    | NULL | 1141 |   100.00 | Using filesort              |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

As you can see in my example the EXTRA column displays additional information. Here a Using filesort.

With this information you can then proceed to consult the MySQL documentation for each condition.

ORDER BY Optimization

Depending on what your EXPLAIN EXTENDED results in, you can consult the official ORDER BY Optimization page to retrieve additional information.

I'll proceed with the Using filesort condition from my example which is explained as follows:

  The Original filesort Algorithm

  The original filesort algorithm works as follows:

  Read all rows according to key or by table scanning. Skip rows that do not match the WHERE clause.
  For each row, store in the sort buffer a tuple consisting of a pair of values (the sort key value and the row ID).
  If all pairs fit into the sort buffer, no temporary file is created. Otherwise, when the sort buffer becomes full, run a quicksort on it in memory and write it to a temporary file. Save a pointer to the sorted block.
  Repeat the preceding steps until all rows have been read.
  Do a multi-merge of up to MERGEBUFF (7) regions to one block in another temporary file. Repeat until all blocks from the first file are in the second file.
  Repeat the following until there are fewer than MERGEBUFF2 (15) blocks left.
  On the last multi-merge, only the row ID (the last part of the value pair) is written to a result file.
  Read the rows in sorted order using the row IDs in the result file. To optimize this, read in a large block of row IDs, sort them, and use them to read the rows in sorted order into a row buffer. The row buffer size is the read_rnd_buffer_size system variable value. The code for this step is in the sql/records.cc source file.

  One problem with this approach is that it reads rows twice: Once during WHERE clause evaluation, and again after sorting the value pairs. And even if the rows were accessed successively the first time (for example, if a table scan is done), the second time they are accessed randomly. (The sort keys are ordered, but the row positions are not.)

  The Modified filesort Algorithm

  The modified filesort algorithm incorporates an optimization to avoid reading the rows twice: It records the sort key value, but instead of the row ID, it records the columns referenced by the query. The modified filesort algorithm works like this:

  Read the rows that match the WHERE clause.
  For each row, store in the sort buffer a tuple consisting of the sort key value and the columns referenced by the query.
  When the sort buffer becomes full, sort the tuples by sort key value in memory and write it to a temporary file.
  After merge-sorting the temporary file, retrieve the rows in sorted order, but read the columns required by the query directly from the sorted tuples rather than by accessing the table a second time.

  The tuples used by the modified filesort algorithm are longer than the pairs used by the original algorithm, and fewer of them fit in the sort buffer. As a result, it is possible for the extra I/O to make the modified approach slower, not faster. To avoid a slowdown, the optimizer uses the modified algorithm only if the total size of the extra columns in the sort tuple does not exceed the value of the max_length_for_sort_data system variable. (A symptom of setting the value of this variable too high is a combination of high disk activity and low CPU activity.)

Solution

Run your query with EXPLAIN EXTENDED and see what the query optimiser tells you. Then proceed from there.

If you have an index on the table it might retrieve the data according to the index. If you don't have an index, then it might fall back to the FILESORT algorithm. The conditions dictate the ORDER BY Optimization.

Reference

8.8.2 EXPLAIN Output Format (MySQL Documentation)
8.8.3 Extended EXPLAIN Output Format (MySQL Documentation)
8.2.1.10 ORDER BY Optimization (MySQL Documentation)


How to pass environment variables to a sql file from bash shell (PostgreSQL) I need to be able to pass environment variables, from a bash shell executing a .sql file using psql.
The psql command I am running is:
su postgres -c "psql -v ON_ERROR_STOP=1 -v dbname=example -v dbuser=example -e" < ./create-db.sql

Where I have put example is usually set to $DATABASE_NAME and DATABASE_USER respectively which is set in the bash script before executing the psql command.
The create-db.sql file is:
\connect postgres
DROP DATABASE IF EXISTS :dbname;
DO $$BEGIN
    CREATE USER :dbuser;
EXCEPTION WHEN duplicate_object THEN
    RAISE NOTICE 'user already exists';
END$$;
ALTER ROLE :"dbuser" SET search_path TO :"dbname",public;
CREATE DATABASE :"dbname"
    OWNER=:dbuser
    ENCODING=UTF8
    LC_COLLATE='en_US.UTF-8'
    LC_CTYPE='en_US.UTF-8'
    TEMPLATE=template0;
\connect :"dbname"
CREATE SCHEMA zulip AUTHORIZATION :"dbuser";

The variable substitution in the line DROP DATABASE IF EXISTS :dbname; works, but everything after fails at the CREATE USER :dbuser; line fails and I have tried it as each of the following ways:
CREATE USER :dbuser;
CREATE USER :'dbuser';
CREATE USER :"dbuser";
And it always errors with:
You are now connected to database "postgres" as user "postgres".
DROP DATABASE IF EXISTS example;
NOTICE:  database "example" does not exist, skipping
DROP DATABASE
DO $$BEGIN
    CREATE USER :dbuser;
EXCEPTION WHEN duplicate_object THEN
    RAISE NOTICE 'user already exists';
END$$;
ERROR:  syntax error at or near ":"
LINE 2:     CREATE USER :dbuser;
                        ^

I'm really not sure where I'm going wrong with the above. Output of psql -V is psql (PostgreSQL) 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)

This will allow you to do it.  In your case, everything between '$$' is a "dollar-quoted string constant, so nothing gets interpreted.  See sql syntax, 4.1.2.4.
\set CREDO 'begin create user ' :dbuser '; exception when duplicate_object then raise notice ''user already exists''; end';
do :'CREDO';


Is it necessary to archive master WAL files in order to implement streaming replication in postgresql-11? The documentation at:

https://www.postgresql.org/docs/11/warm-standby.html#STREAMING-REPLICATION

Starts out by saying that:

"Streaming replication allows a standby server to stay more up-to-date than is possible with file-based log shipping. The standby connects to the primary, which streams WAL records to the standby as they're generated, without waiting for the WAL file to be filled."

Which sounds a lot like streaming replication is a replacement for WAL logfile-shipping. But a little later the instructions at 26.2.5 say:

"To use streaming replication, set up a file-based log-shipping standby server as described in Section 26.2. The step that turns a file-based log-shipping standby into streaming replication standby is setting primary_conninfo setting in the recovery.conf file to point to the primary server."

This sounds like a streaming-replication secondary must also be a logfile-shipping secondary, which seems redundant. I can understand that it's possible for the secondary to fall behind in replication so far that the master may delete WAL segments from it's xlog directory before they are replicated, and that is an argument for a WAL archive. But the documentation says that a "replication slot", apparently an indicator of the last WAL file processed by the secondary, will accomplish the same goal, I guess by telling the master to hold on to that and subsequent segments. At 26.2.5:

"If you use streaming replication without file-based continuous archiving, the server might recycle old WAL segments before the standby has received them. If this occurs, the standby will need to be reinitialized from a new base backup. You can avoid this by setting wal_keep_segments to a value large enough to ensure that WAL segments are not recycled too early, or by configuring a replication slot for the standby."

I'm in the midst of experiments, but any explanation would be appreciated.

Setting up the standby via a base backup is similar, and is not described in detail in both places.  One place refers you to the other.  That is all that is meant. You can have streaming without a WAL archive.


Choosing what table fields to partition on I'm having a tough time deciding whether or not to partition my table based on a unix timestamp or by a descriptor attribute. It's just over a billion rows currently.

My table basically contains:

BIGINT id
DOUBLE value
SMALLINT device_id
BIGINT unixtime
(and others...)

I can either choose to partition it based on the device_id or some modulus value on the unixtime field. On all of the examples I've seen on partitioning, everybody seems to do it on a timestamp field.

Right now, I'm constantly doing searches based on the device_id and a range of unixtime values: a user chooses a device, then picks out a time frame that they want to view (most of the time, it's the past 24 hours).

Would it even make sense to partition it based on the device_id column? Or would using the unixtime field be the way to go?

By all means, partition by unixtime. Why ???

You can separate data by day
You can archive the data of the oldest partition
This can be scheduled in the MySQL event scheduler for

adding new partitions
deleting old partitions

You drop the data of the oldest partition by DDL (ALTER TABLE tbalename DROP PARTITION partitionname) instead of DML (DELETE FROM tablename WHERE unixtime ...). See my answer to this post Partitioning and freeing disk space
If you are using MySQL 5.6, you can query against the specific partition instead of whole table.

Here is what I am going to do for you

Let say your table is as you gave as an example

USE mydatabase
CREATE TABLE mytable
(
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    value DOUBLE NOT NULL,
    device_id SMALLINT NOT NULL,
    unixtime INT UNSIGNED,
    PRIMARY KEY (id,unixtime),
    KEY device_id (device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(unixtime))
(PARTITION p2015_05_09 VALUES LESS THAN (1431230400) ENGINE = InnoDB,
 PARTITION p2015_05_10 VALUES LESS THAN (1431316800) ENGINE = InnoDB,
 PARTITION p2015_05_11 VALUES LESS THAN (1431403200) ENGINE = InnoDB,
 PARTITION p2015_05_12 VALUES LESS THAN (1431489600) ENGINE = InnoDB,
 PARTITION p2015_05_13 VALUES LESS THAN (1431576000) ENGINE = InnoDB,
 PARTITION p2015_05_14 VALUES LESS THAN (1431662400) ENGINE = InnoDB,
 PARTITION p2015_05_15 VALUES LESS THAN (1431748800) ENGINE = InnoDB,
 PARTITION p2015_05_16 VALUES LESS THAN (1431835200) ENGINE = InnoDB,
 PARTITION p2015_05_17 VALUES LESS THAN (1431921600) ENGINE = InnoDB,
 PARTITION p2015_05_18 VALUES LESS THAN (1432008000) ENGINE = InnoDB,
 PARTITION p2015_05_19 VALUES LESS THAN (1432094400) ENGINE = InnoDB,
 PARTITION p2015_05_20 VALUES LESS THAN (1432180800) ENGINE = InnoDB,
 PARTITION p2015_05_21 VALUES LESS THAN (1432267200) ENGINE = InnoDB,
 PARTITION p2015_05_22 VALUES LESS THAN (1432353600) ENGINE = InnoDB,
 PARTITION p2015_05_23 VALUES LESS THAN (1432440000) ENGINE = InnoDB,
 PARTITION p2015_05_24 VALUES LESS THAN (1432526400) ENGINE = InnoDB,
 PARTITION p2015_05_25 VALUES LESS THAN (1432612800) ENGINE = InnoDB,
 PARTITION p2015_05_26 VALUES LESS THAN (1432699200) ENGINE = InnoDB,
 PARTITION p2015_05_27 VALUES LESS THAN (1432785600) ENGINE = InnoDB,
 PARTITION p2015_05_28 VALUES LESS THAN (1432872000) ENGINE = InnoDB,
 PARTITION p2015_05_29 VALUES LESS THAN (1432958400) ENGINE = InnoDB,
 PARTITION p2015_05_30 VALUES LESS THAN (1433044800) ENGINE = InnoDB,
 PARTITION p2015_05_31 VALUES LESS THAN (1433131200) ENGINE = InnoDB,
 PARTITION p2015_06_01 VALUES LESS THAN (1433217600) ENGINE = InnoDB,
 PARTITION p2015_06_02 VALUES LESS THAN (1433304000) ENGINE = InnoDB,
 PARTITION p2015_06_03 VALUES LESS THAN (1433390400) ENGINE = InnoDB,
 PARTITION p2015_06_04 VALUES LESS THAN (1433476800) ENGINE = InnoDB,
 PARTITION p2015_06_05 VALUES LESS THAN (1433563200) ENGINE = InnoDB,
 PARTITION p2015_06_06 VALUES LESS THAN (1433649600) ENGINE = InnoDB,
 PARTITION p2015_06_07 VALUES LESS THAN (1433736000) ENGINE = InnoDB,
 PARTITION p2015_06_08 VALUES LESS THAN (1433822400) ENGINE = InnoDB,
 PARTITION p2015_06_09 VALUES LESS THAN (1433908800) ENGINE = InnoDB,
 PARTITION p2015_06_10 VALUES LESS THAN (1433995200) ENGINE = InnoDB,
 PARTITION p2015_06_11 VALUES LESS THAN (1434081600) ENGINE = InnoDB,
 PARTITION p2015_06_12 VALUES LESS THAN (1434168000) ENGINE = InnoDB,
 PARTITION p2015_06_13 VALUES LESS THAN (1434254400) ENGINE = InnoDB,
 PARTITION p2015_06_14 VALUES LESS THAN (1434340800) ENGINE = InnoDB,
 PARTITION p2015_06_15 VALUES LESS THAN (1434427200) ENGINE = InnoDB,
 PARTITION p2015_06_16 VALUES LESS THAN (1434513600) ENGINE = InnoDB,
 PARTITION p2015_06_17 VALUES LESS THAN (1434600000) ENGINE = InnoDB,
 PARTITION p2015_06_18 VALUES LESS THAN (1434686400) ENGINE = InnoDB,
 PARTITION p9999999999 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Please note that

unixtime must be part of the PRIMARY KEY.
unixtime should be INT UNSIGNED instead of BIGINT

This will make your table 30 days back and 10 days ahead. Of course, those extra 10 days should be empty all the time.

If you run the following code, this will create the stored procedures and events that will run every night

USE yourdatabase

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_Partition_Table_Append` $$
CREATE PROCEDURE `sp_Partition_Table_Append`(
  TargetDB VARCHAR(64),
  TargetTB VARCHAR(64)
)
BEGIN

  DECLARE Target_DBTB,pname VARCHAR(1024);

  SET Target_DBTB = CONCAT(TargetDB,'.',TargetTB);

  SET @dt1 = DATE(NOW()) - INTERVAL 7 DAY;

  SET @looking_for_opendate = 1;
  WHILE @looking_for_opendate = 1 DO
    SET @dt1 = @dt1 + INTERVAL 1 DAY;
    SET @dt2 = @dt1 + INTERVAL 1 DAY;
    SET pname = CONCAT('p',LCASE(LEFT(DATE_FORMAT(@dt1,'%Y_%m_%d'),11)));
    SELECT COUNT(1) INTO @looking_for_opendate
    FROM information_schema.partitions
    WHERE table_schema   = TargetDB
    AND   table_name     = TargetTB
    AND   partition_name = pname;
  END WHILE;

  SET @sql = CONCAT('ALTER TABLE ',Target_DBTB,' DROP PARTITION p9999999999');
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
  SET @sql = CONCAT('ALTER TABLE ',Target_DBTB,' ADD PARTITION (PARTITION ',pname,' VALUES LESS THAN ( UNIX_TIMESTAMP(',QUOTE(@dt2),') ) ENGINE=InnoDB)');
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
  SET @sql = CONCAT('ALTER TABLE ',Target_DBTB,' ADD PARTITION (PARTITION p9999999999 VALUES LESS THAN (MAXVALUE) ENGINE=InnoDB)');
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
  SET @sql = CONCAT('FLUSH TABLES ',Target_DBTB);
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_Partition_Table_Rotate` $$
CREATE PROCEDURE `sp_Partition_Table_Rotate`(
  TargetDB VARCHAR(64),
  TargetTB VARCHAR(64),
  DaysToKeep INT
)
ThisStoredProcedure:BEGIN

  DECLARE Target_DBTB,OldPartitionToKeep,partitions_to_drop VARCHAR(1024);
  DECLARE FirstOfTheMonth,OldestMonthToKeep DATE;

  SET Target_DBTB = CONCAT(TargetDB,'.',TargetTB);

  SET @today  = DATE(NOW());
  SET @oldest = @today - INTERVAL DaysToKeep DAY;
  SET OldPartitionToKeep = CONCAT('p',LCASE(LEFT(DATE_FORMAT(@oldest,'%Y_%m_%d'),11)));

  SELECT COUNT(1) INTO @zapcount
  FROM information_schema.partitions
  WHERE table_schema   = TargetDB
  AND   table_name     = TargetTB
  AND   partition_name < OldPartitionToKeep;

  IF @zapcount = 0 THEN LEAVE ThisStoredProcedure; END IF;

  SELECT GROUP_CONCAT(partition_name) INTO partitions_to_drop
  FROM information_schema.partitions
  WHERE table_schema   = TargetDB
  AND   table_name     = TargetTB
  AND   partition_name < OldPartitionToKeep;

  SET @sql = CONCAT('ALTER TABLE ',Target_DBTB,' DROP PARTITION ',partitions_to_drop);
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

END $$

DELIMITER ;

DELIMITER $$

DROP EVENT IF EXISTS Append_Daily_Partitions $$
CREATE EVENT Append_Daily_Partitions
  ON SCHEDULE
  EVERY 1 DAY
  STARTS DATE(NOW() + INTERVAL 1 DAY) - INTERVAL 15 MINUTE
  ON COMPLETION PRESERVE
DO
ThisScheduledEvent:BEGIN
  DECLARE srcdb,srctb VARCHAR(64);
  DECLARE FoundCount INT;

  SET srcdb = DATABASE();

  DROP TABLE IF EXISTS PartitionedTablesToRotate;
  CREATE TABLE PartitionedTablesToRotate
  (
    id INT NOT NULL AUTO_INCREMENT,
    table_schema VARCHAR(64) NOT NULL,
    table_name VARCHAR(64) NOT NULL,
    days_to_retain INT NOT NULL DEFAULT 30,
    PRIMARY KEY (id),
    UNIQUE KEY DB_TBL_INDEX (table_schema,table_name)
  );
  INSERT INTO PartitionedTablesToRotate
  (table_schema,table_name)
  SELECT table_schema,table_name
  FROM information_schema.partitions
  WHERE table_schema=srcdb
  AND partition_name='p9999999999';

  SET @ndx = 0;
  SELECT COUNT(1) INTO @lastndx FROM PartitionedTablesToRotate;
  WHILE @ndx < @lastndx DO
    SET @ndx = @ndx + 1;

    SELECT table_name INTO srctb
    FROM PartitionedTablesToRotate WHERE id = @ndx;

    SELECT COUNT(1) INTO FoundCount
    FROM information_schema.partitions
    WHERE table_schema = srcdb
    AND table_name = srctb
    AND partition_name = 'p9999999999';

    IF FoundCount = 1 THEN
      CALL sp_Partition_Table_Append(srcdb,srctb);
    END IF;
  END WHILE;

END $$

DELIMITER ;

DELIMITER $$

DROP EVENT IF EXISTS Rotate_Daily_Partitions $$
CREATE EVENT Rotate_Daily_Partitions
  ON SCHEDULE
  EVERY 1 DAY
  STARTS DATE(NOW() + INTERVAL 1 DAY) + INTERVAL 15 MINUTE
  ON COMPLETION PRESERVE
DO
ThisScheduledEvent:BEGIN
  DECLARE srcdb,srctb VARCHAR(64);
  DECLARE FoundCount,DaysToKeep INT;

  SET srcdb = DATABASE();

  SELECT COUNT(1) INTO FoundCount FROM information_schema.tables
  WHERE table_schema=srcdb AND table_name='PartitionedTablesToRotate';
  IF FoundCount = 0 THEN
    LEAVE ThisScheduledEvent;
  END IF;

  SET @ndx = 0;
  SELECT COUNT(1) INTO @lastndx FROM PartitionedTablesToRotate;
  WHILE @ndx < @lastndx DO
    SET @ndx = @ndx + 1;

    SELECT table_name,days_to_retain INTO srctb,DaysToKeep
    FROM PartitionedTablesToRotate WHERE id = @ndx;

    SELECT COUNT(1) INTO FoundCount
    FROM information_schema.partitions
    WHERE table_schema = srcdb
    AND table_name = srctb
    AND partition_name = 'p9999999999';

    IF FoundCount = 1 THEN
      CALL sp_Partition_Table_Rotate(srcdb,srctb,DaysToKeep);
    END IF;
  END WHILE;

END $$

DELIMITER ;

DELIMITER $$

SET GLOBAL event_scheduler = 'ON';

The events will do the following:

Append_Daily_Partitions will add new partition at 11:45 PM
Rotate_Daily_Partitions will delete old partition at 12:15 AM

Please install this on a Dev or Staging Server and verify to works for you.

GIVE IT A TRY !!!


SQL Server AlwaysOn Failover Transparency This is using RC0.

I have set up a SQL Server 2012 AlwaysOn (or more accurately, highly-likely-but-not-always-on) Availability Group.

I take the Primary offline.

The Secondary takes up to 25 seconds to assume the Primary role.
Any queries that were executing at the time of failover returns an error to the client.

Can anyone who has tested this new AlwaysOn feature validate my results?

How soon should I expect the failover to execute and complete?
Does an executing query get resubmitted against the new Primary, or am I correct that it causes a "connection failure" type error to the client?
Is the following form of connection string correct, i.e. no "Failover_Partner" (because this is not mirroring)?

ConnectionString=Provider=SQLOLEDB.1;Server=10.10.10.10;User ID=x;Password=y;Persist Security Info=True;Initial Catalog=z

Note: I tried the below, but all I get is "connectivity link failure" and it never recovers.  One would have thought the Native client would work better?

ConnectionString=Provider=SQLNCLI11.1;Server=tcp:10.10.10.10,8888;User ID=x;Password=y;Persist Security Info=True;Initial Catalog=z

It depends on how long it takes the clustering to fail over and then for crash recovery to run.
No, not unless you program your app to resubmit the query.
Yes, provided that you setup an AlwaysOn listener (that might not be the correct name) and the IP 10.10.10.10 is the IP address which the cluster service and AlwaysOn are using for the floating listener.  If that IP is an IP which is assigned to one of the specific servers which is hosting a copy of the database then no.


SQL Server Standard Edition CPU + Tempdb Configuration I have SQL 2014 sp3 (Standard) With below CPU Details according to 8 CPU we have configured 8 Tempdb datafiles.

Based on my configuration could you please suggest what should be the CPU & Tempdb configuration for the same.

I am facing the Top 2 Wait type to the server.

ASYNC_NETWORK_IO
SOS_SCHEDULER_YIELD

ASYNC_NETWORK_IO has nothing to do with tempdb.  ASYNC_NETWORK_IO is the wait that occurs when SQL Server is transmitting data to the client.  So the only ways to address this are to upgrade the network to provide more bandwidth by either upgrading the network or, if the network is saturated, address the saturation in whatever way possible, or modify the queries or number of users to reduce the amount of data that has to be transmitted to clients.

For SOS_SCHEDULER_YIELD, giving the full treatment in an answer here is a bit overkill (IMHO), and I would refer you to SOS_SCHEDULER_YIELD at sqlskills.com to get started on your journey there.  However, I might say that if you have one or more queries that are returning massive amounts of data, resulting in the ASYNC_NETWORK_IO waits, it's very likely that the same queries are also the cause of the SOS_SCHEDULER_YIELD waits.

These waits may be inevitable on data warehouse or big data systems where queries that return massive amounts of data are the norm.


Placing a uniqueness constraint on a date range I have a table reservation with columns roomno(INTEGER), startdate(DATE), enddate(DATE) with a primary key on (roomno, startdate).

How do I place a constraint on the table such that bookings are not allowed to overlap?

I am trying to implement this in SQLFIDDLE postgreSQL9.3

For example:

101  2016-01-01   2016-01-05
101  2016-01-03   2016-01-06 [This row should not be possible to insert]

startdate and enddate are data type date.

Use an exclusion constraint and be aware how bounds for the type daterange work. date is a discrete type, so ranges have default [) bounds. Per documentation:

  The built-in range types int4range, int8range, and daterange all use a
  canonical form that includes the lower bound and excludes the upper
  bound; that is, [).

So, if you want to book a room from 2016-01-01 to 2016-01-05, including first and last day, you have to specify bounds explicitly:

daterange(start_date, end_date, '[]')

Or you enter dates with exclusive upper bounds; to include 2016-01-05, enter 2016-01-06. Then you can use the simpler expression:

daterange(start_date, end_date)

Either make it a functional index with the above expression in the exclusion constraint, like demonstrated by @ypercube:

Uniqueness constraint with date range

This way you can keep columns like you have right now.

Or you can replace startdate and enddate with a daterange column and use the plain column in the exclusion constraint. Like in this related answer:

Updating table of versioned rows with historical records in PostgreSQL

Either way, assuming roomno is an integer column, you need to install the additional module btree_gist first, once per database.

Related:

Combining separate ranges into largest possible contiguous ranges
Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL


Install SQL server 2008 R2 with VS 2010 Ok, I've installed SQL Server 2008 R2.

I'm trying to add a "SQL Server Database" to an ASP MVC project.

But it keeps telling me I need SQL Server for it to work.
What do I need to do to setup the SQL Server properly for it to work with VS2010 ?

I've tried messing with the SQL Server Management Studio, and can't seem to connect to anything through there.

What am I doing wrong ? How can I create a working SQL service ?

Go to your SQL Server Configuration Manager. You will find it in the SQL Server folder, under Configuration Tools.

Check the list of services that are currently running. At the minimum you should have started "SQL Server (MSSQLSERVER)" (if it's the default instance).

After you check that, go to the client and to the server protocols and see if the TCP/IP and Shared Memory protocols are enabled. If yes, then you should be set for the first time use of SQL Server.

Open Management Studio and try to connect to the local instance: use local machine name or IP.
If it won't work, please tell us any error that you might get.


Installing time zone data on MariaDB 10.0 Yesterday, I set the time zone on our DB server (as explained here).  Doing so required using the mysql_tzinfo_to_sql executable.  Fortunately, this already existed on the server, and conversion of the time zone data went smoothly.

However, today I can see that our slave is generating the same error.  The problem is that the slave doesn't seem to have the mysql_tzinfo_to_sql executable.  apt-get tells me that it exists in these other packages:

root@slave:~# mysql_tzinfo_to_sql /usr/share/zoneinfo/|mysql -u root mysql
The program 'mysql_tzinfo_to_sql' can be found in the following packages:
 * mysql-server-5.5
 * mariadb-server-5.5
 * mysql-server-5.6
 * percona-xtradb-cluster-server-5.5
Try: apt-get install <selected package>

I'm concerned that installing one of these (probably would choose mariadb-server-5.5) would conflict with our currently-installed version of MariaDB (10.0.17-MariaDB-1~precise-log).

How should I go about installing this time zone data on the slave?

Can it be easily copied over from the master's DB?
Is my concern about installing mariadb-server-5.5 unwarranted?
Is there a totally different approach I'm missing?

I'm concerned that installing one of these (probably would choose mariadb-server-5.5) would conflict with our currently-installed version of MariaDB (10.0.17-MariaDB-1~precise-log).

Ubuntu Precise was released 12.04. It's super ridiculous old, and it was when you asked the question. That's why the version of MariaDB was so old too. MariaDB dropped support for precise in 10.0.31. Because you're running on an unofficial in Debian/Ubuntu's eyes version of MariaDB, it's not guaranteed to work with the system.

All of that said, the question is essentially who packaged your MariaDB. It seems likely you got it from MariaDB upstream. So while you have a workaround, the question is better phrased like this

Where does MariaDB install mysql_tzinfo_to_sql?

Clearly it's not in your path, you can find it by

running the package you downloaded through dpkg -c.
using apt-file
Running: sudo updatedb; locate mysql_tzinfo_to_sql;


pg_restoring into a table with a not null column I'm merging two databases. The newer one has a table with an integer column failed_port which is NOT NULL. The older database does not have this column.

So after using pg_dump to dump both databases and then pg_insert to merge them there is an error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2336; 0 58976 TABLE DATA c_a postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  null value in column "faild_port" violates not-null constraint
DETAIL:  Failing row contains (1, A, B, 2000, null, 2019-01-19 16:27:15.335126, Tool).
    Command was: INSERT INTO c_a (c_a_pk, c_b_fk, name, port, row_created, row_creator) VALUES (1...

The table in older database looks like this:

c_a_pk | c_b_fk | name | port  |        row_created         | row_creator
-------+--------+------+-------+----------------------------+-------------
     1 | A      | B    |  2000 | 2019-01-19 16:27:15.335126 | Tool

And in the newer like this:

c_a_pk | c_b_fk | name | port | failed_port |        row_created         | row_creator
-------+--------+------+------+-------------+----------------------------+-------------
     1 | C      | D    | 2000 |           0 |2019-02-19 16:27:15.335126  | Tool

How to merge the older table with the newer? Is there for example a command for pg_restore to automatically set default value to 0 if there is a NOT NULL column that doesn't exist in first table? I could do this manually, but I'm planning to merge multiple databases so it would be burdensome to do this by hand.

Temporarily put a trigger on your target table to set default values:

CREATE FUNCTION override_null()
RETURNS trigger AS $$
BEGIN
  IF NEW.failed_port IS NULL THEN
    NEW.failed_port := 0;
  END IF;
  RETURN NEW;
END
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER override_null_trigger
BEFORE INSERT ON c_a
FOR EACH ROW
EXECUTE PROCEDURE override_null();


Why can't you add an Identity to an existing colum in SQL Server? What is so special about an Identity column?
I am pretty sure there is a valid reason, but I'm trying to get a better understanding of SQL.
So what is so special about an Identity column that this can't be easily added in retrospect?
Why is this not just a single int/bigint flag somewhere that is being incremented every time you insert a new row?
Is there anything physically stored per row that this is a difficult process?

So what is so special about an Identity column that this can't be
easily added in retrospect?

An identity does not insert values on a table by itself, it depends on an INSERT command and the old rows on your table would require an UPDATE. If you try to UPDATE an identity column, you get an error like this:

Msg 8102, Level 16, State 1, Line 8 Cannot update identity column
'ColumnName'.

Every feature has a purpose and limitations. The purpose of the identity is not for updating values.

Why is this not just a single int/bigint flag somewhere that is being
incremented every time you insert a new row?

In fact it is something like that. The CREATE TABLE (Transact-SQL) IDENTITY (Property) says:

SQL Server might cache identity values for performance reasons and
some of the assigned values can be lost during a database failure or
server restart.

So your IDENTITY column has something like a flag that asks SQL Server for one of those values when inserting a new row.
From the Remarks section of the IDENTITY doc you can see the feature has some restrictions and the reason is:

These restrictions are part of the design in order to improve
performance, and because they are acceptable in many common
situations.


How can I corrupt a table in SQL Server? I have to write a program that detects corrupted tables and tries to repair them. For this I need to corrupt tables regularly.

Is there anyway to corrupt tables manually?

Paul Randal has several Sample corrupt databases to play with:

DemoDataPurity -  192-MB SQL Server 2005 database with a single 2570 (data purity) error
DemoFatalCorruption1 -  1-MB SQL Server 2005 database with a corrupt system table (that allows CHECKDB to complete)
DemoFatalCorruption2 - 1-MB SQL Server 2005 database with a corrupt system table (that terminates CHECKDB)
DemoNCIndex - 192-MB SQL Server 2005 database with a bunch of nonclustered index corruptions
DemoRestoreOrRepair - 1-MB SQL Server 2005 database with a page checksum failure (in fact a zero'd out page)
DemoCorruptMetadata - 1-MB SQL Server 2000 database with corrupt syscolumns table

Also, How to create a corrupt database using BULK INSERT/ UPDATE and BCP - SQL Server as a HEX editor.


Triggers vs. Jobs in SQL Server I’m designing a database with a table that automatically gets loaded with a new record every 15 seconds (each record will have a 'timestamp' field and around 50 ‘float’ fields).  Each record in this table needs to be copied to another table.  I have done some initial testing with two different approaches to do this:

Use a trigger to insert each record into the other table (will fire every time a record is added to the source table - every 15 seconds)
Use a job (SQL Server Agent) to run every ten minutes or so and insert a batch of records into the other table (and use a flag field in the source table to indicate which records have been processed).

I don’t have very much experience with triggers or SQL Server jobs so I don’t know the impact regarding overhead and system resources (over time, both tables will have a lot of records).

In general, what approach would be better?  Is there another approach I should consider?

Thanks

When inserting one record each 15 seconds I would not be worried about using triggers.

Consider these two heap tables, with one rowversion column and 50 float columns each.

CREATE TABLE dbo.floattable1
(Rowver ROWVERSION
,Float1    float
,Float2    float
...);
CREATE TABLE dbo.floattable2
(Rowver ROWVERSION
,Float1    float
,Float2    float
...);

Both tables have ~2M rows (1940480).

Solution with a trigger

When we create an AFTER INSERT TRIGGER on dbo.floattable1 to also insert into dbo.floattable2

CREATE TRIGGER dbo.floattrigger
ON dbo.FloatTable1
AFTER INSERT
AS
INSERT INTO dbo.floattable2(Float1    ,Float2     ,Float3     ,Float4     ,Float5     ,Float6     ,Float7     ,Float8     ,Float9
,Float10  ,Float11  ,Float12  ,Float13  ,Float14  ,Float15  ,Float16  ,Float17  ,Float18  ,Float19  ,Float20  ,Float21  ,Float22
,Float23  ,Float24  ,Float25  ,Float26  ,Float27  ,Float28  ,Float29  ,Float30  ,Float31  ,Float32  ,Float33  ,Float34  ,Float35
,Float36  ,Float37  ,Float38  ,Float39  ,Float40  ,Float41  ,Float42  ,Float43  ,Float44  ,Float45  ,Float46  ,Float47  ,Float48  ,Float49  ,Float50 )
SELECT Float1     ,Float2     ,Float3     ,Float4     ,Float5     ,Float6     ,Float7     ,Float8     ,Float9     ,Float10  ,Float11  ,Float12  ,Float13
,Float14  ,Float15  ,Float16  ,Float17  ,Float18  ,Float19  ,Float20 ,Float21  ,Float22  ,Float23  ,Float24  ,Float25  ,Float26  ,Float27  ,Float28  ,Float29
,Float30  ,Float31  ,Float32  ,Float33  ,Float34  ,Float35  ,Float36  ,Float37  ,Float38  ,Float39
,Float40  ,Float41  ,Float42  ,Float43  ,Float44  ,Float45  ,Float46  ,Float47  ,Float48  ,Float49  ,Float50
FROM inserted;

Afterwards, we insert one whole row in dbo.floattable1 resulting in trigger execution:

And a very simple plan with 1 logical read.

Solution for the job / FlagColumn

Drop the trigger, add a flagcolumn, set it to 1 and add an index on it.

DROP TRIGGER  dbo.floattrigger;
ALTER TABLE dbo.floattable1
ADD FlagColumn bit;
UPDATE dbo.floattable1 SET FlagColumn = 1;

CREATE INDEX IX_FlagColumn on dbo.floattable1(FlagColumn);

Insert 40 test records with flagcolumn = 0

SET STATISTICS IO, TIME ON;
INSERT INTO dbo.floattable1(Float1    ,Float2     ,... ,FlagColumn)
SELECT TOP(40) Float1     ,Float2     ,... , 0
FROM dbo.floattable1

Insert into dbo.floattable2 and update the flagcolumns

BEGIN TRANSACTION
INSERT INTO dbo.floattable2
(Float1   ,Float2     ,...)
SELECT Float1     ,Float2     ,...
FROM dbo.floattable1
WHERE FlagColumn = 0;

UPDATE dbo.floattable1
SET FlagColumn = 1
WHERE FlagColumn = 0;
COMMIT TRANSACTION

The first insert can be improved to be equal to the trigger's insert, by removing the RID lookup (adding all floats to the included column list of the index).

But the update will give some extra overhead.

.

The advantage you have here is that you can schedule this when you want, and have some more information on your data via the flag. The extra index means extra overhead and Insert + Update means extra overhead than only issueing an Insert.

Both ideas are viable, but it will really depend on how complex / simple the table is used. If it is only used as a logging table, and the trigger will fire for one record each 15 seconds (and nothing else), the trigger should be fine.
If the data sync does not need to happen that fast / often, data load with an INSERT + UPDATE should be fine too.
YMMV, always test before implementing.


ASPState Log file growing tremendously - SQL Server 2008 Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Web Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

I have an ASPState database online, and its log file ASPState_log is growing tremendously. The current size is 536gb.

How can I maintain this log file?

I take a daily backups through SQL Agent job. Can I truncate the logs?

What is the best practice to keep the log file size in control?

99% of the time, the asp.net session state database does not warrant FULL recovery and transaction log backups. In fact, it usually doesn't warrant any backups at all. I'd favour a script to recreate it over taking backups.

NB: Be wary of folk sneaking persistent objects in to your state database. Lock 'em out.

If your SQL Server isn't clustered, you have the option of targeting tempdb for state storage as an alternative to a persistent database, so it's treated as truly disposable.

Assuming your usage is typical and recovery of the data isn't required either:

Switch to SIMPLE recovery model.
Follow Kimberly Tripp's suggested route for shrinking the log.

Or, if you aren't clustered go the tempdb route and drop your state database, by re-running aspnet_regsql with the option -sstype t.


What is the easiest way to use a SQL Server backup to retrieve just a few rows? I need to manually retrieve a few rows from a SQL Server 2008 database backup. Normally I create a new database, restore the database backup to the new database, and run my queries. The database is huge, however, so it takes forever to restore.

Is there an easier way to get at the older data?

There is no way to restore either a single table or a few rows in SQL server without a third party product. If the table for which you are trying to retrieve few rows is in a separate file group you can restore only that filegroup (assuming you have Enterprise edition).

Else use a third party product like Quest Litespeed. I believe it has a feature that lets you to restore just a table from backup.

If your database is huge, use backup compression (available in SQL 2008 R2 standard and enterprise edition). Usually, a compressed backup takes less time to restore.


Aggregate Same Column, Different Tables I have the following schema:

table_1
id, updated_at

table_2
id, updated_at

table_3
id, updated_at

and in total there are about 12 tables that I want to get the updated_at column and determine the MAX() aggregate value from all of them, based on a single id value.

How would I set up this query?

Get the maximum updated_at for a given id from each table, combine the results into one row set and then get the maximum from it

SELECT
  MAX(updated_at)
FROM
  (
    SELECT MAX(updated_at) AS updated_at FROM table1 WHERE id = @param_id
    UNION ALL
    SELECT MAX(updated_at) FROM table2 WHERE id = @param_id
    UNION ALL
    SELECT MAX(updated_at) FROM table3 WHERE id = @param_id
    UNION ALL
    ...
  ) AS derived
;

This is much more efficient then combining the tables first and then filtering and aggregating the combined set, even though the code would look simpler that way.

This method can also benefit from an index on (id, udpated_at) in each table, while probably no index would help if you decided to combine whole tables first.


Invalid object name 'column_name' I have created a procedure as

create procedure testPagination
(
@PageNumber bigint,
@PageSize Bigint,
@OrderByColumn varchar(1000),
@OrderType varchar(1000)
)
As
Begin
    --print @PageNumber
    --print @PageSize

    declare @strSQL nvarchar(max)
    declare @intStartSrNo bigint
    declare @intEndSrNo bigint

    if @OrderType = 'asc'
    begin
        set @intStartSrNo=((@PageNumber*@PageSize)-@PageSize)
        set @intEndSrNo=(@PageNumber*@PageSize)
    end
    else if @OrderType = 'desc'
    begin
        create table #RecCount(cnt bigint)
        declare @intTotRecords bigint
        set @strSQL='insert into #RecCount(cnt) select isnull(max(a.sr_no),0) from ' + @OrderByColumn + ' a with(nolock)'
        exec sp_executesql @strSQL
        select @intTotRecords=cnt from #RecCount

        set @intStartSrNo=(@intTotRecords-(@PageNumber*@PageSize))+1
        set @intEndSrNo=((@intStartSrNo-1)+@PageSize)

    end
    else
    begin
        return
    end

    if @OrderType = 'asc'
    begin
        set @strSQL=
        '
        select a.sr_no, bv.id,bv.name_voter,bv.home_street_address_1, bv.home_address_city from ' + @OrderByColumn + ' a with(nolock) ' +
        ' inner join base_voter bv with(nolock) on bv.id=a.base_voter_id where a.sr_no > ' + cast(@intStartSrNo as varchar(500)) + ' and a.sr_no <= ' + cast(@intEndSrNo as varchar(500))
    end
    else if @OrderType = 'desc'
    begin
        set @strSQL=
        '
        select a.sr_no, bv.id,bv.name_voter,bv.home_street_address_1, bv.home_address_city from ' + @OrderByColumn + ' a with(nolock) ' +
        ' inner join base_voter bv with(nolock) on bv.id=a.base_voter_id where a.sr_no >= ' + cast(@intStartSrNo as varchar(500)) + ' and a.sr_no <= ' + cast(@intEndSrNo as varchar(500))
    end
    --print @strSQL
    exec sp_executesql @strSQL
    return

End
go

3 indexes as

 create index IX_base_voter_name_voter_asc_deleted_at on base_voter(name_voter asc,deleted_at) include(home_street_address_1, home_address_city) where deleted_at is null
 create index IX_base_voter_home_street_address_1_asc_deleted_at on base_voter(home_street_address_1 asc,deleted_at) include(name_voter, home_address_city) where deleted_at is null
 create index IX_base_voter_home_address_city_asc_deleted_at on base_voter(home_address_city asc,deleted_at) include(name_voter, home_street_address_1) where deleted_at is null

Now, when I try to execute this stored procedure I receive the error

Invalid Object name 'home_address_city'

I am executing by right-clicking on the stored procedure, which actually executes the following query

USE [test_db]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[testPagination]
        @PageNumber = 1,
        @PageSize = 50,
        @OrderByColumn = N'home_address_city',
        @OrderType = N'asc'

SELECT  'Return Value' = @return_value

GO

I have already hit ctrl+shift+r a lot but with no success

Your proc contains this dynamic code:

set @strSQL='insert into #RecCount(cnt) select isnull(max(a.sr_no),0) from ' + @OrderByColumn + ' a with(nolock)'

When executed, this code throws the error mentioned above because from context it seems that Some Column is passed through @OrderByColumn, but your code tries to do a SELECT from this Some Column.


Correct way to join row in parent table to row in one of several children tables I have a parent table product, and several children tables like phone, tablet, smartwatch etc. The parent and children have 1-1 relationship. This means that for every row in parent table, there will be exactly 1 row in exactly one of the several children tables. This is to basically pull up common fields in a single table (like product.product_name), in my effort to follow the DRY principle.

Now the part where i am worried is that, although i can easily join to the parent row, provided the child row, how would i efficiently do vice versa.
For example: if i have a row from the product table, and now i want to get the rest of the product information, how would i quickly find which child table contains the rest of the information, other than having to check each and every child table in a big query. In simpler words: How to efficiently find the correct child table in order to join a row in parent table to its corresponding row in one of the children tables.

Let's assume i would soon have 100+ children tables. In such a case, an exhaustive query would get bigger and bigger with every new child table.

One obvious solution would be to maintain a 'product_type' field in the parent table. But this is clearly redundant information, violates the Single Source Of Truth rule , and prone to human error (eg; product_type says 'phone' but the product actually was a 'tablet'). So i am not sure if this is the recommended way to do it.

I am meaning to find out the canonical way to solving such a problem when designing schema, and not just an opinion-based answer.

I am sorry if this wasn't the right forum to ask this question.

UPDATE:

I later came across this answer to another post and am really feeling positive about it.

I think your whole schema is not fully thought through.

A product is a product which, presumably, you are going to sell?

Have one product table - no need to split the products up into phone, tablet or smartwatch. There's no problem having millions of records in a table these days.

Have a table product_category (or type as you suggest) which will have phone, tablet &c. which will be a FOREIGN KEY (pointing back to an INTEGER PRIMARY KEY) in the product table. Then you can have your classic supplier, client, billing tables &c.

You might want to take a look at databaseanswers.org and also you could download Open Source products which correspond to your particular system - check out their schemas and see what they've done.

You asked about the canonical way of designing these sorts of schemas - this is (probably) it. Plus, you won't be writing queries with 100's of tables. You mentioned DRY and SPOT. Don't forget YAGNI (You Ain't Going To Need It) - also espoused by Eric Raymond in the excellent "The Art of Unix Programming".

Don't try and be all things to all men from day one! Start easy and design incrementally - don't expect to get everything right the first time (hint: you won't :-)) - best of luck with your project.


Mongo Replication with VIP Have anyone implemented Mongo replication using replset arbiter. At the time of fail-over from Primary to Secondary, Arbiter should bring down VIP on primary and enable on secondary when Arbiter appoints secondary as Master.

Is this is possible?

I do understand that it is for voting purpose. But when it can decide who should be available for writes and who shouldn't be, I would like to embed a logic that should also route application requests to the winner. Also I don't want to keep the host decision on app end. Kinda doesn't look clean for me.

Note: It will open write on secondary, doesn't mean that it will route the requests from application to secondary (NewMaster).

So the drivers here are application drivers should be written in such a way.

@mongo_client = MongoReplicaSetClient.new(
['n1.mydb.net:27017', 'n2.mydb.net:27017', 'n3.mydb.net:27017']
).db("test").collection("sample")

To choose its member. I'm I right?

Community Wiki answer generated from comments on the question by James Wahlin and Markus W Mahlberg

James: The arbiter is a voting member in an election but doesn't appoint members as primary or control the process. I would suggest reading: Replica Set Elections in the MongoDB manual.

When there is an election, all client connections are dropped by MongoDB. At that point in time the client will connect to one of its seeds and perform an isMaster call. This call reports back to the client which member is primary and which members are secondaries. In that way the client will seamlessly write to the correct member, based on read preference.

Markus: It will route requests from the application to the secondary. The drivers are replica set aware. Since all connections are either actively dropped by the old primary or stale, the driver will try to find the current primary in the list of replica set members it knows. You can verify this behaviour by setting up a replica set locally, running at 3 different ports on your local machine and kill one of them at random. After the short while an election needs to be finished, your client application writes to the newly elected primary.


"CREATE FILE encountered operating system error 32" when attempting to attach database I am having trouble connecting to a database I created in SSMS. Keep in mind this is my first time working with databases. All I've done in this database is create some tables and add a few records. What I need to do is connect to this database in Visual Studio 2015. However, when I try to add a connection to a Microsoft SQL Server Database File to my windows forms application it says the file is in use and will not let me select it. When I select a different database file I created in SSMS and test the connection it tells me this database file is not compatible with the current instance of SQL Server. To resolve this issue, you must upgrade the database file by creating a new connection or modify the existing connection to this database file. Now in order to upgrade a database file, according to MS documentation, is to attach the database. But when I right click the "Database" node in SSMS and click attach and try to add either of the two database .mdf files from earlier, it gives me this error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

------------------------------

CREATE FILE encountered operating system error 32(The process cannot access the file
because it is being used by another process.) while attempting to open or create the
physical file 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Mestek Database.mdf'.
(Microsoft SQL Server, Error: 5123)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2000&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476

I ran this query on the database which I guess returns any process using the database.

SELECT * FROM sysfiles

But this just returns the file path and name of the .mdf file and log file.

Do not fear everyone, I fixed my problem. What I had to do was detach the databases that I needed so that they were no longer being used by SSMS. This got rid of the error I was having and allowed me to add a connection to the data source in visual studio.


Oracle: How do I add users to the HTTP ACL in Oracle 11? How do I add a user to the network ACL (access control list)?

When running this code

declare
    req utl_http.req;
begin
    req := utl_http.begin_request('http://example.com');
end;

I receive this error.

ORA-24247: network access denied by access control list (ACL)

First, see if you have an ACL for port 80, the HTTP port.  If you do, it should look something like  this.

select * from dba_network_acls;

HOST     LOWER_PORT UPPER_PORT ACL                     ACLID
---      ---------- ---------- ---                     -----
*        80         80         /sys/acls/www.xml       1FBEC09C32D78F5AE05335D6488A7883

If you need to recreate it, you can drop it with this command:

dbms_network_acl_admin.drop_acl('www.xml');

Create the ACL:

 dbms_network_acl_admin.create_acl(
     acl => 'www.xml',
     description => 'WWW ACL',
     principal => 'SCOTT',
     is_grant => true,
     privilege => 'connect'
 );

Assign hosts and ports to the ACL:

 dbms_network_acl_admin.assign_acl(
     acl => 'www.xml',
     host => '*',
     lower_port => 80
 );

Add users to the ACL:

 dbms_network_acl_admin.add_privilege(
     acl => 'www.xml',
     principal => 'OE',
     is_grant => true,
     privilege => 'connect'
 );

  version note: kevinsky points out that this is applicable for Oracle 11 but that it has changed in Oracle 12.


Secondary mysql instance runs but mysqld_multi reports it is "not running" On my Ubuntu server, there are two mysql instances running at the same time.

A default, normal mysql instance
A secondary mysql instance running with mysqld_multi.

The command sudo service mysql status correctly states the mysql service is active.

I want to check the secondary instance's status so I use this command :

sudo mysqld_multi report

...and I get :

MySQL server from group: mysqld1 is not running

which seems pretty inconsistent since I can connect to this mysql instance from my DB client.

Why does mysqld_multi tell me the instance is not running ?

Check creation mysql instance dir, confirm mysql rights and install instance in dir
mkdir -p /u01/data/mysql/mysql1 && chown mysql:mysql /u01/data/mysql /u01/data/mysql/mysql1
mysql_install_db --user=mysql --datadir=/u01/data/mysql/mysqld1


Add ENum Attribute to existing table using Alter I am using oracle XE and trying to add a attribute that restricts to only three  available value 'Small','Medium','large' to an existing table using Alter Table and Enum.

Tried doing,

ALTER TABLE TRUCK ADD TTYPE ENUM('SMALL','MEDIUM','LARGE');

but gets invalid option

ALTER TABLE TRUCK ADD TTYPE ENUM('SMALL','MEDIUM','LARGE');
                                 *

where the error highlights after ENUM.

I think I am having syntax error. Please help to resolve.

I think you want this:

ALTER TABLE TRUCK
    ADD (ttype VARCHAR2(6)
    CONSTRAINT con_type
    CHECK (ttype in('SMALL','MEDIUM','LARGE'))
    );


SSMS18 - Default File Location (Changed ssms.Application.pkgdef) I have changed the DefaultProjectsLocation (SSMS18) from the default (My Documents) to a folder on my network. But when I run SSMS18 and open Projects it is still looking at the default location. What am I missing?

If using SSMS with a full install of SQL Server then there is a good answer on Stackoverflow.

If using SSMS as a standalone then the following should work

Locate C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Extensions\Application
Open ssms.application.pkgdef with a text editor(e.g. Notepad)
Change the value of DefaultProjectsLocation
"DefaultProjectsLocation"="C:\MYNEWPATH"
Save the file


Transactional Replication: can create 52 publication, but creating 53rd leads to problems My apologies in advance for a long post, but I was really trying to make sure I do my due diligence before turning to the community for help, so here it goes.

I am working on a proof of concept with the purpose of using transactional replication to maintain a "reporting" copy of our databases.  The setup will include a "Publisher" server and a separate "Subscriber" server.  The replication will be a "Pull" transactional replication with a distributor running on the Subscriber server. Once again: Publisher => pull => (Distributor + Subscriber)

We expect to have a lot of databases on each of our servers, so, in order to mimic this scenario, I restored 100 databases (about 1 GB each) on the publisher.  All of the databases are identical.

I wrote a script, which enables me to setup replication in an automated fashion (since obviously I don't want to do it manually dozens of times).  The script performs the following steps:

Enable replication for a database (if not enabled already):
Create logreader agent for a database
Create publication for a database (if doesn't exist)
Create snapshot agent for a database (don't run it yet)
Add articles to the publication
Run snapshot agent job (and wait for it to finish)
Create subscription database (if it doesn't exist)
Add subscription in the subscription database (if it doesn't exist)
Create distribution agent

I am able to successfully setup replication for about 52 of my databases and everything is running fine.  When I get to database #53 things start to go wrong.  The snapshot creation is successful, but the logreader and the distribution agents fail.  Moving on to database #54 everything is now completely broken.  The snapshot does not get created and none of the agents are running.  I have carefully reviewed replication monitor and SQL Agent jobs looking for error messages, but I didn't find anything helpful.  The replication monitor simply says: "Agent Shutdown.  For more information see the SQL Server Agent job history for job ‘……’".  In the details section it says: "The replication agent encountered a failure.  See the previous job step history message or Replication Monitor for more information".  Next I go to the SQL Agent Jobs and review the output.   In the job history the failed step says: "Message : The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information".  In turn, the previous step has no info at all.   Just the word “Message” with nothing after that.  I have also reviewed the windows application log looking for any errors, but it simply repeats the same messages I see in the replication monitor and agent job history.  I added “–output” parameter to the agent command (in the SQL Agent Job step properties) hoping that the output file would provide me with more information, but no output file gets created, which leads me to believe that the agents don’t even start.

I was thinking that I might have hit some hardware limit, so I checked the disk space and there is plenty of it available.  I have also checked RAM and it's not even 50% used.

Finally, what really gets me is that I am able to run all of the agents manually from the command line.  For example, I can copy paste parameters from the SQL Agent snapshot job and run snapshot.exe from the command line with these parameters – the snapshot gets created successfully.  The same goes for logreader and distribution agents.

I am completely confused here and appreciate any pointers in the right direction.

Final note (and maybe the only silver lining): The problem is fully re-producible.  I can successfully setup my 50+ subscriptions and then the issues will always start around subscription number 52-53.

Thank you!

I believe you are running into the desktop heap issue described here (support.microsoft.com/kb/949296) and here (support.microsoft.com/kb/824422).


Postgresql: Move database to new tablespace I'd like to completely move a database (tables, indexes, etc.) to a new tablespace. I know how to move each item individually but is there a way to move it all in one go? I'm using postgresql 9.1.

ALTER DATABASE name SET TABLESPACE new_tablespace

Excerpt from the docs:

  ... changes the default tablespace of the database. Only the database owner or a superuser can do this; you must also have create privilege for the new tablespace. This command physically moves any tables or indexes in the database's old default tablespace to the new tablespace. Note that tables and indexes in non-default tablespaces are not affected.


Is it possible to write out images from SQL Server to a mapped drive? I am using a cursor to write out images stored in a database to text.

Code snippet here:

DECLARE @SOURCEPATH VARBINARY(MAX),
        @DESTPATH VARCHAR(MAX),
        @ext varchar(MAX),
        @ObjectToken INT,
        @image_ID Varchar(255)

DECLARE IMGPATH CURSOR FAST_FORWARD FOR
    SELECT <my data>
    FROM <my table>

OPEN IMGPATH

FETCH NEXT FROM IMGPATH INTO @SOURCEPATH, @image_ID

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @DESTPATH = '<my path>'+ @image_ID

    EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
    EXEC sp_OASetProperty @ObjectToken, 'Type', 1
    EXEC sp_OAMethod @ObjectToken, 'Open'
    EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @SOURCEPATH
    EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @DESTPATH, 2
    EXEC sp_OAMethod @ObjectToken, 'Close'
    EXEC sp_OADestroy @ObjectToken

    FETCH NEXT FROM IMGPATH INTO @SOURCEPATH, @image_ID
END

CLOSE IMGPATH
DEALLOCATE IMGPATH

I am able to write to a local drive without a problem, but I cannot write to the network share that I'd like to write to. I've followed the steps here,

https://www.mssqltips.com/sqlservertip/3499/make-network-path-visible-for-sql-server-backup-and-restore-in-ssms/

in an effort to make the drive "visible" to SQL Server, but I still am not having any luck writing the images to this mapped drive.

Is this functionality supported, should I go down the route of checking permissions with our network guys, or is this not something that is possible?

SQL Server can usually see a UNC path like \\server\share\subfolder

But only if the service running SQL Server has rights to that share. For example, if you're running a scheduled job, the job runs as the user for the SQL Agent Service. If you're running code directly in SSMS or a stored procedure called from your program, then the SQL Engine Service must have rights to that shared folder.

If that user is a local user only, then it won't have rights to the shared folder. SQL Server engine and/or agent must be running as a domain user to access the volume.

For example, we run our backups to a \\server\DBs\SQL\<server name> shared drive. The SQL Agent Service account has to have full rights to that shared folder for the backups to succeed. There's no mapped drive involved in these backups.


Massive MySQL delete from CSV file To insert massive CSV file into MySQL, there is LOAD DATA INFILE, but is there any similar command to delete massive entries with keys stored in a massive CSV file?

If not, what would you see as the fastest way to perform such operation?

EDIT: Also which one of these 2 methods would you see as the most efficient:

Multiple deletes

DELETE FROM myTable WHERE mykey=1 LIMIT 1;
DELETE FROM myTable WHERE mykey=2 LIMIT 1;
...
DELETE FROM myTable WHERE mykey=300000 LIMIT 1;

Or Batch delete

DELETE FROM myTable WHERE mykey IN (1,2,...,10000);
DELETE FROM myTable WHERE mykey IN (10001,10002,...,20000);
...
DELETE FROM myTable WHERE mykey IN (...,...,300000);

Thanks!

DELETE TECHNIQUE #1
If you have a massive delete of keys, you may want to resort to the opposite of doing a massive DELETE: Create the table with the remaining values.
Let's say you loaded a table from the CSV file called KeysToDelete with the field mykey
Here is how to perform the massive DELETE without using DELETE
CREATE TABLE mynewtable FROM mytable;
INSERT INTO mynewtable
SELECT A.* FROM mytable A
LEFT JOIN KeysToDelete B USING (mykey)
WHERE B.mykey IS NULL;
ALTER TABLE mytable RENAME myoldtable;
ALTER TABLE mynewtable RENAME mytable;

That's it.
DELETE TECHNIQUE #2
If you want to use DELETE and the table is not that big, you could do a DELETE JOIN as follows:
DELETE A.* FROM mytable A INNER JOIN KeysToDelete B USING (mykey);

Here are my earlier posts where I discuss these techniques:

Feb 15, 2012 : How to implement soft deletes?
Mar 05, 2012 : Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios
Nov 07, 2012 : Can I do anything about a very long running KILL command?
Aug 07, 2013 : How to delete duplicate records

Give it a Try !!!


Save history editable data RDBMS I want to make application like testing system. Every question has one or many variant of answers (and one or many can be right). I apologize that tutors and students use my testing system. It means that tutor can make CRUD operations with subject, questions and answers. But in this case appeares one big problem as saving version. For example, I am a tutor and I decide edit some question or answer ( or may be several questions and several answers). When I enter as student, I want to get a list of questions in which I made mistakes but tutor already edited this question and I get wrong information.

Main question: How I can save old versions questions and answers?

Each test has questions and every question has naswers. Tutors can edit/add/delete any of those components. For example, he or she can modify one of the questsion's text, add several questions to a test and edit one of the answers to the question that has just been modified. Any hints how I can handle all this so that I could retreive a version of the test at the exact time in the past?

Here is what I have come up with so far. The solution is extremely complex and I feel there is a better way of doing that.

I've seen two common approaches to storing historical data: Storing history in a separate _history table, and storing it in the same table as the "current" record, but with a version number and maybe an indicator to show that it's historica data.

If you separate the history to a separate table you'd have something like this:

question
--------
    question_id (PK)
    question_text

question_history
----------------
    question_history_id
    original_question_id (FK to question.question_id)
    question_version_number
    version_timestamp
    question_text
    (PK of this table is composed of question_history_id, original_question_id, and question_version_number)

When you make an edit for a question with some question_id, you do it in two stages:

insert into question_history the question_id (as original_question_id) and text into question_history. question_version_number and version_timestamp and question_history_id can be populated via a trigger.
update the existing record in question based on the data from the user's edit.

In this situation, the "current" version is always in the main question table, and if you want historic data you have to query question_history.

If you want to have everything in a single table, you could do it more simply like this:

question
--------
    question_id (PK)
    question_text
    question_version_number
    version_timestamp

This simplifies your database structure and the user's edit operations, but querying for the most current question will now always require you to sort the questions by version_timestamp (or by question_version_number - truth is, you only really need one of these). If you have lots of history, this could become a performance issue. It's also probably a good idea to include question_version_number or version_timestamp in a composite primary key that includes question_id so that you can't have the same version number for two records for the same question.

As for answers:

You can apply the same pattern to answers that I applied to questions. Additionally, you'll probably want to keep track of which version of a question an answer applied to, so you'll need a question_version_id for each version of an answer. Example:

answers
-------
    answer_id (PK)
    answer_text
    student_id (FK to students - I assume you will need a way to know who wrote which answer)
    answered_question_id (FK to questions.question_id)
    answered_question_version_number (FK to questions.version_number or
                                      question_history.version_number,
                                      depending on how you want to do it
                                       - you could also reference the version_timestamp
                                      if you prefer).

Now for the tests. This is where it could get tricky. You will want to version the tests, but does the version number of the test change every time one of the questions changes? That might be a good way to do it, though if someone makes minor edits to every question on a 50-question test, that might create too many versions. You could also try to only create a version of a test when a user explicitly clicks a "new version" button for the test, but if someone forgets it could screw things up in other ways. Or you could have all questions on one page so one single edit of the test will create one version but with all edits to all questions. That's something that's probably best controlled in the application.

I think the data structures will probably be the same though:

test
----
   test_id
   test_version_number
   version_timestamp

questions
---------
    question_id (PK)
    question_text
    question_version_number
    version_timestamp
    test_id (FK to tests)
    test_version_number (FK to tests.version_number - or you
                        could reference tests.version_timestamp if you prefer)

Now, say you want to get all answers for a particular version of a test for a particular student:

select answers.*
from answers
inner join questions
    on answers.question_id = questions.question_id
    and answers.answered_question_version_number = question.version_number
    and questions.test_id = $TEST_ID
    and answers.student_id = $STUDENT_ID
inner join tests
    on tests.test_id = questions.test_id
    and questions.test_version_number = tests.test_version_number
    and tests.test_version_number = $TEST_VERSION_NUMBER;


Amount of memory used by postgres I am curious as to whether the performance of postgres depends on the amount of RAM available. i.e. will postgres performance(during reads) on several runs get affected by the amount of RAM that is available in the system. For example, would postgres performance after several runs be different for 4GB, 8GB, 16GB, 64GB systems.

I noticed change in postgres's perfomance. I want to acertain whether it is because of the RAM available in the system or not. Can someone please help me with this?

Yes, PostgreSQL performance is influenced by the amount of RAM available to PostgreSQL, among many other factors.

PostgreSQL benefits from RAM for:

The buffer cache in shared_buffers. Think of this as the database's first level cache.
work_mem for performing sorts, hash joins, etc
maintenance_work_mem for index building etc
Operating system disk cache - RAM that appears to be "unused" will be used by the OS to cache disk blocks, so they're much faster for PostgreSQL to read. This is estimated with effective_cache_size.

It's not as simple as "more RAM good" though.

Lots of slow RAM will be worse than less fast RAM for a database that fits entirely in RAM on both systems. Once you have enough RAM to cache the indexes and tables in heavy use, more RAM generally makes little difference.

NUMA (non-uniform memory architecture) machines can sometimes have performance issues related to NUMA page migration because the Linux kernel doesn't understand PostgreSQL's use of shared memory properly. This can have a drastic influence on performance, so a "bigger" machine can be slower. Appropriate kernel tuning will solve this issue.

There's a cost to maintaining shared_buffers, so too big a shared_buffers can slow things down. It's a balance between having enough workspace and the maintenance cost of that workspace.

effective_cache_size tells the PostgreSQL query planner how much system RAM is thought to be used for disk cache, and will help it choose better plans. If unset or incorrectly set the planner is less likely to pick the correct plan for a system.

A machine with tons of RAM and a pathetic disk subsystem might still be slower than one with little RAM and really fast disks - depending a lot on the workload.

RAM makes less difference for write-heavy workloads, it's mostly beneficial for read-heavy workloads.

You might be noticing a theme here - it depends on what you're doing, and no single system spec like amount of RAM can be taken in isolation. It'd be nice if it was simpler, but it isn't.


SQL Server memory usage alert script We have a production server with 500GB RAM. max server memory is set to 450GB. Whenever we checked the memory usage it's showing 450GB, but maybe SQL Server reached 450GB for some time? The memory is not released and it will be used whenever it is needed in future. So we have a free space for utilizing by SQL Server when it needs.

I need to configure an alert when SQL Server really uses 450GB of memory. If it's having FREE space among 450GB then how much it is and if the free space is used beyond 90% of available memory then I need an alert.

I can say in DB level even the SQL Server occupies DB files more than it wants and didn't release it. But I can tell to anyone like we would have unused space and it could be used by SQL Server at any time without increasing the file size.

Like that I am looking for script in Memory Level as well. I hope you are understanding my problem. My SQL Server is fully dedicated and no other applications or no other services (ssis,ssrs etc) are running. Find the below example DB space related script.

select
NAME = left(a.NAME,15),
[FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)),
[FILE_MAXSIZE_MB] = convert(decimal(12,2),round(a.maxsize/128.000,2)),[FILE_GROWTH] = convert(decimal(12,2),round(a.growth/128.000,2))
from
dbo.sysfiles a

As JamesZ mentioned, the memory is intended for the SQL Server to use, particularly for the data cache.  Therefore SQL Server will attempt to use everything that has been made available to the SQL Server process.  (Plus some operating overhead.)

If you are concerned about reserving memory for Windows Operating System and any other processes that you run on the server you can adjust your maxservermemory to a lower number.

I have had good success with Jonathan Kehayias's "formula" that he posted here:

How Much Memory Does My SQL Server Actually Need?

The first paragraph has a good outline of how Kehayias would by default  configure the Maximum Memory.  Of course, this is not absolute and you will need to observe your SQL Server and determine whether more or less memory needs to be reserved.

Brent Ozar has a sanity check post at:
Memory Dangerously Low or Max Memory Too High

So, you should not have a script to tell you that there is too little memory available.  You should configure appropriately for what your system needs.

Brent Ozar's post for a 512 GB Server suggests:

EXEC sys.sp_configure ‘max server memory (MB)’, ‘471859’;
RECONFIGURE;


Which postgres design for a nested resource that can link to the same type of resource I have this kind of data that I need to store in postgres (ruby object).

#<InitRes:0x007f8ec3734c78
 @kind="RestrictionsSchema",
 @restrictions=
  [#<ParsedRes:0x007f8ec60ed218
    @description="Disallow user",
    @key="disallowUserConfig",
    @restriction_type="bool",
    @title="Disallow user">,
   #<ParsedRes:0x007f8ec2c96898
    @description="Restriction message",
    @key="disallowUserConfigMessageKO",
    @restriction_type="string",
    @title="Restriction message">,
   #<ParsedRes:0x007f8ec3551730
    @description="List of configurations",
    @key="configurations",
    @nested_res=
     [#<ParsedRes:0x007f8ec2565678
       @description="configuration",
       @key="configuration",
       @nested_res=
        [#<ParsedRes:0x007f8ec2504328
          @description="configuration name",
          @key="name",
          @restriction_type="string",
          @title="Profile Name">,
         #<ParsedRes:0x007f8ec34b35d0
          @description="server URL",
          @key="server",
          @restriction_type="string",
          @title="Server">,
         #<ParsedRes:0x007f8ec2a64228
          @default_value=
           #<ParsedResRestrictionValue:0x007f8ec3437de0
            @type="bool",
            @value_bool=false>,
          @description="Use Web logon",
          @key="weblogonMode",
          @restriction_type="bool",
          @title="Use Web logon for authentication">],
       @restriction_type="bundle",
       @title="Configuration">],
    @restriction_type="Array",
    @title="Configurations">]>

The ParsedRes can have multiple nested_res that contains and a list of ParsedRes. It can be nested up to two levels deep.

I'm wondering what will be the best design for this kind of data. I would to need to be able to fetch the same tree to display it in the future.

I thought about having a table that can link to a parent and daughter

CREATE TABLE parsed_res (
  id           SERIAL PRIMARY KEY,
  -- other columns
  parent_id    INT,
  daughter_id  INT
);

Is it a good idea?
What is the best way to fetch this tree (subselect?)
ltree ?

Two methods of querying hierarchical data:

Ltree. You can see an example of me using it in a similar question here.. The problem with ltree is you have to serialize the relationship yourself. It's a flat table. This is a pita. Updating it also kind of sucks.
Self-referencing table. You can see an example of me using it in a similar question here.. This is probably ideal because you're only storing the identifier of the parent. This create a simple hierarchical data type that you can query with a RECURSIVE CTE.

Example table,

CREATE TABLE foo (
  idfoo          serial    PRIMARY KEY
  idparent       int       REFERENCES foo
  description    text,
  key            text,
  restriction    text,
  title          text,
  default        text
);

Then you insert your root, and your children which point to the parent.


macOS homebrew: database system was not properly shut down; automatic recovery in progress I'm noticing some errors in my logs when restarting PG 9.6. I also tried upgrading to 10.1, and the problem continues. This is on macOS via a homebrew installation.

First, I shut down with "brew services stop postgres":

2017-12-16 13:56:09.778 EST [34295] LOG:  received smart shutdown request
2017-12-16 13:56:09.779 EST [34295] LOG:  worker process: logical replication launcher (PID 34316) exited with exit code 1
2017-12-16 13:56:14.787 EST [34317] FATAL:  terminating connection due to unexpected postmaster exit

Then I restart it with "brew services start postgres":

2017-12-16 13:56:43.152 EST [5604] LOG:  listening on IPv6 address "::1", port 5432
2017-12-16 13:56:43.152 EST [5604] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2017-12-16 13:56:43.153 EST [5604] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2017-12-16 13:56:43.215 EST [5619] LOG:  database system was interrupted; last known up at 2017-12-16 10:43:27 EST
2017-12-16 13:56:43.407 EST [5619] LOG:  database system was not properly shut down; automatic recovery in progress
2017-12-16 13:56:43.409 EST [5619] LOG:  redo starts at 0/5B51E580
2017-12-16 13:56:43.409 EST [5619] LOG:  invalid record length at 0/5B51E660: wanted 24, got 0
2017-12-16 13:56:43.409 EST [5619] LOG:  redo done at 0/5B51E628
2017-12-16 13:56:43.435 EST [5604] LOG:  database system is ready to accept connections

This is with a default postgres.conf. I believe the brew services script ends up running:

/usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres stop

I also tried using pg_ctl -D /usr/local/var/postgres stop, but that just gives:

waiting for server to shut down......................................... failed
pg_ctl: server does not shut down

What is the proper way to stop and then re-start postgres?

It looks like "brew services stop postgres" is sending a signal to shutdown, and then following up 5 seconds later with a hard-kill.  That seems like a rather hostile thing for it to do, but I have zero experience with brew.  Maybe that is configurable?

But the other question is, why does it take more than 5 seconds to shut down?  Unless you have a lot of dirty data in memory which needs to be written out, shutting down should be pretty quick.  When you tried to shutdown with the pg_ctl command, what ended up in the server log file after that attempt?  When pg_ctl reports that the shutdown failed, that just means that pg_ctl got sick of waiting for it, it doesn't mean the shutdown permanently failed (i.e. it doesn't shoot the postmaster in the head the way brew seems to).  So you can keep checking the log file even after pg_ctl reports a failure.  You can use the -t option to make pg_ctl more patient.

Also, you should turn log_checkpoints=on, and then try the pg_ctl stop -t 3600 ... again and look in the server log files.


Pulling Top queries returns NULL in query plan and sql text I'm using the following code to pull our top 20 queries (ordered by CPU):

SELECT TOP 20 qs.sql_handle
    ,qs.execution_count
    ,qs.total_worker_time AS [Total CPU]
    ,qs.total_worker_time / 1000000 AS [Total CPU in Seconds]
    ,(qs.total_worker_time / 1000000) / qs.execution_count AS [Average CPU in Seconds]
    ,qs.total_elapsed_time
    ,qs.total_elapsed_time / 1000000 AS [Total Elapsed Time in Seconds]
    ,st.TEXT
    ,qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC

However, I'm seeing this:

Can anyone shed some light as to why the Query Plan and SQL Text are showing up NULL?  Are they some sort of system process or external application?  We are running SQL 2008 R2.

Thanks, as always, everyone!

There are limitations when using sys.dm_exec_query_plan
5.4 When and How to Use Sys.dm_exec_text_query_plan

The query_plan column returned by sys.dm_exec_query_plan has type XML and suffers from the inherent limitation of the data type that it cannot have nest levels greater than or equal to 128. In SQL Server 2005 RTM and SP1, if the query_plan had a depth of greater than or equal to 128 levels this would prevent the query from returning. The second limitation is that it is not easy to get the query plan of a particular statement in the batch directly from the DMVs without parsing the query plan XML returned from sys.dm_exec_query_plan.
To address both these limitations, in SQL Server 2005 SP2, we introduced sys.dm_exec_text_query_plan. Sys.dm_exec_text_query_plan is a TVF that takes 3 parameters: plan_handle, statement_start_offset and statement_end_offset. It returns the showplan in text format for the batch or for a specific statement within the batch.

Instead of reinventing the wheel, best is to use Brent's - sp_BlitzCache or Glenn's Diagnostic queries. or Aaron's answer here.
From SQL Server Query Performance Analysis using DMVs :
-- Which Queries are taking the most time/cpu to execute
SELECT TOP 20
    total_worker_time, total_elapsed_time,
    total_worker_time/execution_count AS avg_cpu_cost, execution_count,
    (SELECT DB_NAME(dbid) + ISNULL('..' + OBJECT_NAME(objectid), '')
        FROM sys.dm_exec_sql_text([sql_handle])) AS query_database,
    (SELECT SUBSTRING(est.[text], statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
            ELSE statement_end_offset
            END - statement_start_offset) / 2
        )
        FROM sys.dm_exec_sql_text([sql_handle]) AS est) AS query_text,
    total_logical_reads/execution_count AS avg_logical_reads,
    total_logical_writes/execution_count AS avg_logical_writes,
    last_worker_time, min_worker_time, max_worker_time,
    last_elapsed_time, min_elapsed_time, max_elapsed_time,
    plan_generation_num, qp.query_plan
FROM sys.dm_exec_query_stats
    OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp
WHERE [dbid] >= 5 AND DB_NAME(dbid) IS NOT NULL
  AND (total_worker_time/execution_count) > 100
--ORDER BY avg_cpu_cost DESC;
--ORDER BY execution_count DESC;
ORDER BY total_worker_time DESC;


SQL Server: CONVERT From ISO8601 To DateTime Within Query I have a Microsoft SQL Server 2008 R2 database where the DateTime column is not a datetime data type, but varchar(50). Our website logs that data and I don't really have control over it (I'm sure a proper datetime column would be preferable). I'm trying to do a query that gets some year-to-date results with BETWEEN DateTime field AND NOW().

SQL Server table:

Id  DateTime                    Ip              City            State       Country         Continent   UserAgent
-----------------------------------------------------------------------------------------
1   2013-02-05T17:58:45-06:00   68.55.34.32     Laurel          Maryland    United States   North America   NULL
2   2013-02-05T17:58:52-06:00   70.88.57.62     Port Charlotte  Florida     United States   North America   NULL
3   2013-02-05T17:59:06-06:00   71.3.202.110    Cape Coral      Florida     United States   North America   NULL
4   2013-02-05T17:59:23-06:00   67.239.18.161   Naples          Florida     United States   North America   NULL
5   2013-02-05T17:59:58-06:00   96.19.107.135   Gulfport        Mississippi United States   North America   NULL

SQL query:

SELECT *
FROM dbo.RebroadcastSmoothStreaming
WHERE
   (DateTime BETWEEN CONVERT(datetime, SUBSTRING('2014-01-01T00:00:00-06:00', 1, 19)) AND { fn NOW() })
ORDER BY Ip

I'm getting an error

  Conversion failed when converting date and/or time from character string

This query works...

SELECT CONVERT(datetime, SUBSTRING('2014-01-01T00:00:00-06:00', 1, 19)) AS Expr1

I've also tried CAST()...

SELECT *
FROM dbo.RebroadcastSmoothStreaming
WHERE (DateTime BETWEEN CAST(SUBSTRING('2014-01-01T00:00:00-06:00', 1, 19) AS datetime) AND { fn NOW() })
ORDER BY Ip

That gives the same conversion error, but this works...

SELECT CAST(SUBSTRING('2014-01-01T00:00:00-06:00', 1, 19) AS datetime) AS Expr1

I'm sure that I'm missing some fundamental bit, but it seems like it should work.

What am I doing incorrectly?

JJ

The issue is that you cannot CONVERT or CAST a VARCHAR ISO8601 datetime with an offset to a DATETIME.

From SQL Server 2008 onwards, the DATETIMEOFFSET datatype was introduced to handle datetimes with offsets.

As answered elsewhere, you would need to CAST your DateTime VARCHAR column to a DATETIMEOFFSET

SELECT *
FROM dbo.RebroadcastSmoothStreaming
WHERE
   (
       CAST(DateTime AS DATETIMEOFFSET) BETWEEN
         CAST('2014-01-01T00:00:00-06:00 AS DATETIMEOFFSET)
         AND { fn NOW() }
   )
ORDER BY Ip


Does "consolidation" of query statements improve query performance? Assume we have a set of postal addresses A1, A2, A3, ... of the form:

A1 = 124, "Smith", "Rd"
A2 = 142, "Jones", "St"
A3 = 8190, "Smith", "Ln"
A4 = 142, "Via Gracia", NULL
A5 = 190, "Jones", "St"
A6 = 31425, "Wedgewood", "Cir"
A7 = 810, "Via Gracia", NULL
...

where A_N is a vector of 3 components: StreetNumberN, StreetNameN, StreetSuffixN.

No two vectors are ever equal component-for-component, however many share StreetName and StreetSuffix.

Translating this dataset directly into a SQL conditional, using a loop, one might obtain:

WHERE
    (StreetNumber = 124 AND StreetName = "Smith" AND StreetSuffix = "Rd")
 OR
    (StreetNumber = 142 AND StreetName = "Jones" AND StreetSuffix = "St")
 OR
    (StreetNumber = 8190 AND StreetName = "Smith" AND StreetSuffix = "Ln")
 OR
    (StreetNumber = 142 AND StreetName = "Via Gracia")
 OR
    (StreetNumber = 190 AND StreetName = "Jones" AND StreetSuffix = "St")
 OR
    (StreetNumber = 31425 AND StreetName = "Wedgewood" AND StreetSuffix = "Cir")
 OR
    (StreetNumber = 810 AND StreetName = "Via Gracia")

Now, when the StreetName and StreetSuffix match for two condition statements, it is logically possible to  "consolidate" StreetNumbers:

WHERE
    (StreetNumber = 124 AND StreetName = "Smith" AND StreetSuffix = "Rd")
 OR
    (StreetNumber IN(142, 190) AND (StreetName = "Jones" AND StreetSuffix = "St"))
 OR
    (StreetNumber = 8190 AND StreetName = "Smith" AND StreetSuffix = "Ln")
 OR
    (StreetNumber IN (142,810) AND StreetName = "Via Gracia")
 OR
    (StreetNumber = 31425 AND StreetName = "Wedgewood" AND StreetSuffix = "Cir")

Please keep in mind this is just a toy example to demonstrate the principle of "consolidation" that I mean. In practice, there would be many such "consolidations" to perform.

Is there/should there be any performance benefit in consolidating StreetNumbers as demonstrated above?

Reasonable question.  Let me set you straight on the relative costs of various components of the query.

OR is the de-optimizer.  Either of those formulations will work this way:

Scan the entire table
For each row, check the WHERE clause
Most or all of the OR parts will be evaluated to eliminate a given row, thereby making your suggest a small improvement.

Scanning the table and fetching every row is the most costly part.  Evaluating the WHERE is a minor part.

That is, any savings you get will be insignificant in the big picture.  And, focusing on getting rid of OR may provide more gain.

If you have a million rows in the table, then this formulation will run much faster:

SELECT .. FROM .. WHERE (StreetNumber = 124 AND StreetName = "Smith" AND StreetSuffix = "Rd")
UNION ALL
SELECT .. FROM .. WHERE (StreetNumber = 142 AND StreetName = "Jones" AND StreetSuffix = "St")
UNION ALL
SELECT .. FROM .. WHERE (StreetNumber = 8190 AND StreetName = "Smith" AND StreetSuffix = "Ln")
...

but there are issues with such.  You will need an index:

INDEX(StreetNumber, StreetName, StreetSuffix)  -- (in any order)

If there might be duplicates, then use UNION DISTINCT instead of UNION ALL.

For up to a few hundred rows, just write it whatever is simplest for you (eg, OR without optimization).

You will soon find that your model for addresses is incomplete.  What about "Apt. 123" or "c/o ...".  Etc.  There is probably no good reason to split an address into multiple columns -- simply have a single string with all the parts written in a way that facilitates displaying.  Eliminating the AND would be a tiny optimization.


What are points to consider when replacing the SQL Server service account? In What are the implications of changing the SQL Server Service Logon Account? it mentions that when replacing the SQL Server service account, the service will need to be restarted.

But there is more to it than that. According to Configure Windows Service Accounts and Permissions, the change of logon account should be done through the SQL Server Configuration Manager.

  Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server Database Engine or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as updating the Windows local security store which protects the service master key for the Database Engine. Other tools such as the Windows Services Control Manager can change the account name but do not change all the required settings.

  For Analysis Services instances that you deploy in a SharePoint farm, always use SharePoint Central Administration to change the server accounts for Power Pivot service applications and the Analysis Services service. Associated settings and permissions are updated to use the new account information when you use Central Administration.
  To change Reporting Services options, use the Reporting Services Configuration Tool.

My current environment has some 15 servers between production and development and they all use the same domain account to run the SQL Server services and a different domain account for the SQL Server Agent.

I want to replace this with 15 different pairs of active directory accounts (SQL Server service and Agent).

Questions

What can possibly go wrong? Any gotchas?
Can any part of this process be automated?

If you have Kerberos in the mix you may run into SPN issues. – Jeff A

Depending on your domain functional level (need to be 2012 I believe), you could use Group managed service accounts – Bob Klimes

Any manually created SPNs will need to be deleted and recreated. Any SPNs automatically created by the service on startup will be removed on shutdown, so that shouldn't be a concern. I'll second Bob Klimes' suggestion that you should go to Group Managed Service accounts, if possible. They're absolutely fantastic. Finally, you may need to configure service account permissions within each instance depending on your current configuration. Generally these accounts can be minimally provisioned, but it'll depend on your current config. – John Eisbrener

PowerShell can be used to automate – Bob Klimes

Community Wiki answer compiled from question comments. Community wiki posts have been donated to the community in hopes that others will edit them to keep them up to date, to add useful information, and generally improve their quality.


SQL Server 2012 linked server to SQL Server 2000 I am trying to select through an linked server from SQL Server 2012 to SQL Server 2000.

The linked server has been setup and I can browser the target database through the linked server which indicates that permissions are not a problem.

However, when I try to run a query, I get the following error (not my actual sql):

  Msg 7320, Level 16, State 2, Line 1
  Cannot execute the query "SELECT * FROM BOB" against OLE DB provider "SQLNCLI10" for linked server

Anyone have any ideas of what the problem is and how to solve it?

Using SQLNCLI10 to create a linked server to SQL Server 2000 causes a fault. You can create an odbc connection than attach tables to your 2012 database. Then you build your treatments in SQL 2012 database as if the table was native. This is just a workaround that sometimes works. It's not recommended for large data transaction, like more that 7MM.


Can I make this UPDATE faster? I got this code (python - psycopg2) which takes about 2 minutes to complete. I need each row (60 rows) in each column (288 columns) to get the value from its following column (and corresponding row). Is there any SQL trick to make it happen faster?

for i in range(1, 288):
    oy = i + 1
    for izi in range(1, 61):
        cur.execute("UPDATE d2 SET stav_" + str(i) + " = (SELECT stav_" + str(oy) + " FROM d2 WHERE usek = " + str(izi) + ") WHERE usek = " + str(izi) + ";")
        conn.commit()

You are running 17568 updates (and 17568 selects) for something that can be done with a single statement:

update d2
   set stav_1 = stav_2,
       stav_2 = stav_3,
       stav_3 = stav_4,
       ....
where usek between 1 and 61;
commit;

If you don't want to type 288 column names, then use your program to generate the above statement and execute that once.

Having numbered columns is very often a sign of a bad data model. The need to "move" values from one column to another is another sign that you should really re-design your data model and normalize it.


Strategies to archive high frequent data in SQL Server I have a database that stores high frequent data, the data has a complex data model. Now I want to offload/archive this data in 15 minute intervals to another database server so user can do reporting on this data with a minimal delay. To improve performance I don't want to keep more than 2 days of data on the primary system.
In a previous version of this system the data model was very simple so I used SSIS and transfered data from two tables based on a time stamp, however now with the complex data model I have some doubts that this approach will be efficient.

What strategies would be best for such an environment ? HADR would be nice, however with the deletion of the date on the primary it won't work since I want to keep all data on the reporting instance.

If performance is your only consideration if I were you I would consider partitioning your table(s) rather than just deleting the data.  Then you could look into either log shipping (if you want the whole database to be updated) or replication (if you only want a couple of tables copied).  As an alternative you could look into change data capture as a method of storing the changes you are making to the data.  You can then pull this information out to apply it to your other database.


How to inquire about current value of MAX_EXECUTION_TIME I understand that I can set the value of the global MAX_EXECUTION_TIME by running (from mysql client):
SET GLOBAL MAX_EXECUTION_TIME

But how do I just inquire its value?
GET instead of SET does not seem to accomplish that.

SHOW VARIABLES LIKE 'MAX_EXECUTION_TIME' or select @@max_execution_time for retrieval.
"The execution timeout for SELECT statements, in milliseconds. If the value is 0, timeouts are NOT enabled." per documentation.


Avoid user login Directly to AlwaysOn High Availability Primary I have setup an AlwaysOn High Availability in Sql Server 2014.
my IP addresses are according to this :

192.168.1.189  -- Listener
192.168.1.180  -- Replica
192.168.1.181  -- Primary

Right now users can login to my Primary directly and I want to avoid this.
Is it possible ?

There are a few questions that are similar to what you are asking, may be board-line duplicate but either way:

Protect SQL Server from hackers
Limit connection rights to SQL Server

That second question listed is probably the duplicate of this one.

Overall if you are looking at ensuring logins can only access via the listener you are going to have problems trying to use a login trigger. I am not sure you can pick up DNS or IP address it used to actually connect to SQL Server. If it can then it would be a viable option.

I would opt to use your firewall or network infrastructure. A firewall can be much more efficient and easier to manage with blocking access to either the IP or access from an IP range to a specific IP.