You can't rename the files while they are in use, and don't make them "not in use" by stopping the SQL Server, it won't pick up on the change automatically.

The quickest way is as follows:

Detach the database with EXEC sp_detach_db 'database_name' or the appropriate dialogue in SQL Server Management Studio.
The database must have no current connections otherwise SQL Server won't allow you to detach it - if you have trouble with this force that database into single user mode.
Rename the files as needed
Reattach the database which assuming you ave only a single data file and a single log files is done with EXEC sp_attach_db 'database_name', 'full_path_to_mdf', 'full_path_to_ldf', for example: EXEC sp_attach_db 'my_renamed_db', 'd:\dbs\data\my_renamed_db.mdf', 'd:\dbs\logs\my_renamed_db.ldf'
Again, the appropriate dialogue in SQL Server Management Studio can be used instead.

As you are specifying the database name in the call to sp_attach_db, this does the rename step also.

Update:

As Travis reminds me in the comments, sp_attach_db has been officially deprecated for a while and you should use the newer DDL method instead. The above example becomes:

CREATE DATABASE my_renamed_db
    ON (FILENAME = 'd:\dbs\data\my_renamed_db.mdf')
     , (FILENAME = 'd:\dbs\data\my_renamed_db.ldf')
FOR ATTACH;

Slightly safer even, you can avoid completely detaching the database at all by setting it offline and modifying the file locations instead (you still have to manually move or rename the files though):

ALTER DATABASE my_renamed_db SET OFFLINE;
ALTER DATABASE my_renamed_db MODIFY FILE (NAME='data_file_name', FILENAME='d:\dbs\data\my_renamed_db.mdf');
ALTER DATABASE my_renamed_db MODIFY FILE (NAME='log_file_name', FILENAME='d:\dbs\data\my_renamed_db.ldf');
ALTER DATABASE my_renamed_db SET ONLINE;

You can move/rename the files any time after the SET OFFLINE but before SET ONLINE.

If you don't know the logical filenames you can look them up in SSMS or via

SELECT f.name, f.physical_name
FROM   master.sys.databases d
JOIN   master.sys.master_files f
       ON d.database_id = f.database_id
WHERE  d.name = 'my_renamed_db'

If moving tempdb things are different: you can't offline it and it is recreated every time the server starts. Instead make the ALTER DATABASE MODIFY FILE calls and restart the SQL Server instance.


Most efficient way to compare strings in SQL Server 2014?

So a couple things:

You are collating both sides of the predicate comparison which can affect your query's cardinality estimate which can severely affect performance. It looks like you're doing this to ensure a case insensitive comparison. You're better off storing the data in tables or a database with that collation specified instead, so you can eliminate the need to do it in your JOIN clause.

Your should insert the data into the table already trimmed to eliminate a function call from your predicates as well, that can also affect the cardinality estimates.

Ensure you have an index on (LastName, FirstName). While it's unlikely to be seeked on in your query (because you're doing a wildcard contains search) even an index scan on those fields might be more performant than nothing.

*4. If you're ok with loosening your comparison logic a little bit (at the risk of the JOIN not matching as many cases) you can do a starts with comparison, and a starts with comparison on the reversed strings of the FirstName and LastName fields instead of the contains search. This will be more efficient because starts with comparisons are SARGable whereas ends width and contains searches are not SARGable, meaning the index will be seekable. If you implement your logic this way that means your predicates would match on "John" and "Johnson", or "Smith" and "Blacksmith" but would not match on "lack" and "Blacksmith".


Mongodb: Document size of 29344731 is larger than maximum of 16777216

As per MongoDB BOL here The maximum BSON document size is 16 megabytes.

The maximum document size helps ensure that a single document cannot use excessive amount of RAM or, during transmission, excessive amount of bandwidth. To store documents larger than the maximum size, MongoDB provides the GridFS API.

GridFS is a specification for storing and retrieving files that exceed the BSON-document size limit of 16 MB.

For your further ref here and here


Working SSIS package fails as a job, probably due to data flow connection

All right, I think I've got it. I'm relatively new to this, so forgive any bad terminology.

I got on the right path with this: https://stackoverflow.com/questions/31755979/storing-password-in-ssis-package

Basically, the external connection to the Amazon DB was the issue. I had the correct credentials stored in the SSIS package to make the connection, but by default sensitive information (such as passwords) are encrypted in such a way that is specific to the user who created the package. So the package ran fine for me, but it wouldn't work for anyone else, including the job.

To fix this, you need to change the default Package protection to give the package a sort of master password, so that the package will run correctly for anyone, as long as they have the password for the package. In the project properties, you can find ProtectionLevel and switch the value to another option (I chose "EncryptSensitiveWithPassword.") You'll enter a password, then be prompted to also change the child package files. Get into the package properties and change protection level there too. Enter the same password on the line above. Save it.

Now, when you import package into Integration Services you'll be prompted to enter the password. Then you'll be prompted again when you set up the job. But with the password entered it runs successfully.

Hope this helps somebody.


Security implications of running SSRS and an OLTP database on the same server

The Moment you add Entry points for a system of course, naturally, you have increased the attack surface.
Besides this purely mathematical reason, you should think about:

Who will design reports
For designing reports someone, maybe the same person, will craft queries
Preferably you use stored procedures at least inside a separate, code-only, database
There you have the first permissions you will have to give inside your system for selecting data and creating objects. If done with care the risk is extremely low to zero. So keep it simple.
Above that on the server level you have the Reporting Services Service. Here you are strongly advised to keep to established security best practices such an “use dedicated service accounts”, especially since SSRS has a Login on the SQL Server box for accessing its system databases. Again, no problem if you isolate all that.
And then you of course have the Data Source Credentials which will be a Windows or even SQL Login with just the permissions necessary for running the above queries – at best only prepared stored procedures for mitigating the risk of elevating privileges.

So, long story quick answer: mixing services initially creates a higher risk for taking over the other one, but by following security best practices very strictly, in my opinion, the risk is not higher than if on a separate server where one could still make similar mistakes. In high security environments there will for that very reason be very strict overall rules to rule out such scenarios.


Select multiple columns as JSON in a single query

All the JSON syntax works regardless of compatibility level.

CREATE TABLE dbo.x(col1 char(4), col2 char(4));
GO
INSERT dbo.x(col1, col2) VALUES('val1','val2');
GO
SELECT * FROM dbo.x FOR JSON AUTO;

Results:

[{"col1":"val1","col2":"val2"}]

If you need it without the square brackets, you'll need to abstract it away so you can assign a column name to the JSON output:

;WITH x(y) AS (SELECT * FROM dbo.x FOR JSON AUTO)
  SELECT PARSENAME(y,1) FROM x;

Results:

{"col1":"val1","col2":"val2"}

And without quotes:

;WITH x(y) AS (SELECT * FROM dbo.x FOR JSON AUTO)
  SELECT REPLACE(PARSENAME(y,1),'"',' ') FROM x;

Results:

{ col1 : val1 , col2 : val2 }

And yes it works with all types:

DROP TABLE dbo.x;
GO
CREATE TABLE dbo.x(col1 char(4), col2 char(4), col3 int, col4 date);
GO
INSERT dbo.x(col1,col2,col3,col4) VALUES('val1', 'val2', 5, GETDATE());
GO
SELECT * FROM dbo.x FOR JSON AUTO;

Results:

[{"col1":"val1","col2":"val2","col3":5,"col4":"2019-02-11"}]

This assumes you don't have the closing square bracket ] in your data. If you do, you'll either need to double it on extraction, before FOR JSON looks at it, or else use a more tedious method than PARSENAME() to removing the outer square brackets.


Does Galera Cluster rsync only updated files or all files

From what I can tell it looks like it will usually transfer the whole file instead of using the delta-xfer algorithm unless you set the wsrep_sst_rsync_wan option.  I assume this is because on a local network transferring the full file is close enough or as fast as calculating the deltas between the files.  The relevant lines in the SST script for MariaDB 10.0.14 galera is below.

# Use deltaxfer only for WAN
inv=$(basename $0)
[ "$inv" = "wsrep_sst_rsync_wan" ] && WHOLE_FILE_OPT="" \
                                       || WHOLE_FILE_OPT="--whole-file"


When you install Oracle there are a set of registry entries under hkey_local_machine, software, oracle. If you don't have the registry, and don't want the Oracle home, then you can just delete the directories. There is an Oracle directory under program files, which stores your Oracle Inventory, and your Oracle Home where ever you put that.


How to export large table data into different files in oracle?

You should probably use utl_file and a for loop for this assignment. Then you can open and write one line per file. You should also let people know what a Lakh line is.


Converting a date to a datetime in MySQL


User Permisssions White List vs Black List

You would need two different user roles: one with the permission granted for most users, and one without the permission for User B.
In the world of security, grants or permissions should always be assigned according to the principle of least privilege (only what the user absolutely needs to do their job). As such, privileges should always be denied by default and assigned explicitly, as with a whitelist. They should never be assigned by default and blocked individually (blacklist). Implicit privileges, especially when combined with explicit ones, are more difficult to audit and troubleshoot. Keep it simple.


Return multiple rows from matching on multiple CASE matches?

A join would be the best solution here.

Create a table that contains a list of categories and which products are associated with them, something like Product, Category we'll call it ProductCategories. Then join on this table.

SELECT p.Product, p.Quantity_Sold, pc.Category
FROM sales_table p
JOIN ProductCategories pc ON pc.Product = p.Product'

This has the added benefit of not having to modify your case statement as you add more categories (which quickly gets difficult/impossible to maintain), and only requires a simple insert into the new table.


That error is caused when you have the STRICT_TRANS_TABLES sql mode enabled. It sounds like that mode is not enabled on your server by default and your IDE is enabling the setting for your session. You can either just enable STRICT_TRANS_TABLES sql mode on your server so all connections will be that way by default or run a query to change the sql mode with this module's connection prior to running your insert query (which is what your IDE is doing under the covers).


16 Cores 12 GB RAM server MySql Configuration - my.cnf

As a first course of action, I'd watch the output of 'iostat -xk 10 10' to see if the disk IO is saturating the line.

I notice that you sent specs for your memory system, but not your disk system. What's the underlying array here that you're writing so much to? If you are insert-heavy, then that could play a big part.


MYSQL: Display NULL value when foreing key is NULL

I suggest you use the ANSI join that was introduced in SQL92. If you use a LEFT JOIN I think you achieve what you want:

SELECT xs_bugtracking.id, product
     , xs_bugtracking.title AS title
     , status
     , xs_bugtracking.descr AS descr
     , assignee_tbl.user_nicename AS assignee
     , reported_tbl.user_nicename AS reported_by
     , importance
     , FROM_UNIXTIME(create_date) AS 'create_date'
     , FROM_UNIXTIME(create_date) AS 'modify_date'
FROM xs_bugtracking
JOIN xs_products
    ON xs_bugtracking.product = xs_products.name
LEFT JOIN wp_users AS assignee_tbl
    ON xs_bugtracking.assignee = assignee_tbl.ID
JOIN wp_users AS reported_tbl
    ON xs_bugtracking.reported_by=reported_tbl.ID
WHERE xs_products.lang='en'


Select roads that are between FROM and TO

select *
from road
connect by (prior to_ = from_ and from_ != 'Street V')
start with name_ = 'Street B' and from_ = 'Street II'
;

   ROAD_ID NAME_      FROM_      TO_              SORT
---------- ---------- ---------- ---------- ----------
         5 Street B   Street II  Street III        200
         6 Street B   Street III Street IV         300
         7 Street B   Street IV  Street V          400

Answer for the updated question:

select road_id, name_, from_, to_, sort
from
(
  select road_id, name_, from_, to_, sort,
  row_number() over (order by sort) as rn from road
  where name_ = 'Street B'
)
connect by (prior rn + 1 = rn and from_ != 'Street VIII')
start with from_ = 'Street II'
;

   ROAD_ID NAME_           FROM_           TO_                   SORT
---------- --------------- --------------- --------------- ----------
         9 Street B        Street II       Street III             200
        11 Street B        Street III      Street IV              300
         3 Street B        Street VI       Street VII             600
         7 Street B        Street VII      Street VIII            700


Change length of varchar on live prod table

If you are increasing it to varchar(100 - 8000) (i.e. anything other than varchar(max)) and you are doing this through TSQL rather than the SSMS GUI

ALTER TABLE YourTable ALTER COLUMN YourCol varchar(200) [NOT] NULL

and not altering column nullability from NULL to NOT NULL (which would lock the table while all rows are validated and potentially written to) or from  NOT NULL to NULL in some circumstances then this is a quick metadata only change. It might need to wait for a SCH-M lock on the table but once it acquires that the change will be pretty much instant.

One caveat to be aware of is that during the wait for a SCH-M lock other queries will be blocked rather than jump the queue ahead of it so you might want to consider adding a SET LOCK_TIMEOUT first.

Also make sure in the ALTER TABLE statement you explicitly specify NOT NULL if that is the original column state as otherwise the column will be changed to allow NULL.


RDS: permission denied for relation spatial_ref_sys when copying

This error is a result of you not having access on that view. What you need to run to fix the permissions is

ALTER TABLE public.spatial_ref_sys OWNER TO rds_superuser;
GRANT SELECT, INSERT ON TABLE public.spatial_ref_sys TO public;

Found here on the AWS forums


InnoDB caches both data pages and index pages into the InnoDB Buffer Pool

MyISAM caches index pages only. In fact, you can do something interest if you combine replication with MyISAM.

SUGGESTION #1

Since there only 6700 spoken languages on Earth, you should use SMALLINT for language_id instead of INT. Makes for a smaller table and smaller index.

SUGGESTION #2

If you use MySQL Replication, try out this crazy idea: Convert the keywords table on the Slave to MyISAM. Leave the Master's Copy of the keywords as InnoDB.

You could run this query on the Slave ONLY to convert it...

ALTER TABLE keywords ENGINE=MyISAM;

SUGGESTION #3

Once you make the keywords table MyISAM on the Slave, you could create a dedicated MyISAM Key Cache just for keywords as follows (Example: Setup 512M KeyCache for keywords):

First create a script on the Slave

cd /var/lib/mysql
echo "SET GLOBAL keywords_cache.key_buffer_size = 1024 * 1024 *512;" > InitMySQL.sql
echo "CACHE INDEX keywords IN keywords_cache;" >> InitMySQL.sql
echo "LOAD INDEX INTO CACHE keywords;" >> InitMySQL.sql

Next, create the following entry for the startup script in /etc/my.cnf on the Slave

[mysqld]
init-file=/var/lib/mysql/InitMySQL.sql

Then, just restart mysql

service mysql restart

This keeps keyword index pages away from the general MyISAM Key Cache and in a dedicated cache.

SUGGESTION #4

Perform your keyword lookups on the Slave

I hope this helps !!!


Getting rid of partitioning in Postgresql 9

The easiest way is to first remove any triggers which direct new rows to the child tables, and then do this for each child table:

with t as (delete from child_table_1 returning *)
    insert into parent_table select * from t;

The locking and performance implications may dictate against this method, but it is hard to beat it for simplicity.

You might want to wrap it in a transaction block that starts with locking the child table and ends with dropping it, to make sure no one can keep using it for new tuples while you are cleaning out the old ones.


mysql separate email and cellphone from user table to avoid extra unused-space usage

Use InnoDB, not MyISAM.

Use CHAR only for columns that are truly fixed length (country_code, uuid, postal_code, md5, etc).  email is not likely to be such.

I recommend against using a numeric field for a phone number.  What if the user has an extension?  Or needs a leading 0?  Or enters dashes, etc?

How many users are you expecting?  A million translates to well under 1GB.  Even a billion users, with tens of GB needed is probably not important compared to what else is going on in your multi-GB server.

Use NULL when the business logic needs.  For phone and email in your app, it means "not supplied".  That's fine.

If you dissect the table structure, you will find that more than half of the bytes are "overhead" are "free".  Your attempted optimization is relatively insignificant.

There are other reasons for splitting columns out from the table, but I don't think you have found a valid one.


Encoding issue with SQL Server VARCHAR column retrieved in Python

SQL Server does not store UTF-8 under any circumstances. You get either UTF-16 Little Endian (LE) via NVARCHAR (including NCHAR and NTEXT, but don't ever use NTEXT) and XML, or some 8-bit encoding, based on a Code Page, via VARCHAR (including CHAR and TEXT, but don't ever use TEXT).

The problem here is that your code is mistranslating that 0x82 character, thinking that it's UTF-8, but it's not. There is no UTF-8 "character" having a value of 0x82, which is why you get the "unknown" / replacement symbol of "�". Please see the following UTF-8 table which shows that there is no character for a single-byte of 0x82:

UTF-8 encoding table

As stated by the O.P., the Collation of the column in question is SQL_Latin1_General_CP1_CI_AS, which means that the 8-bit encoding is using Code Page 1252, which is Windows Latin 1 (ANSI). And checking that chart (scroll down to the bottom chart as it has the character names) value 0x82 (look for "82" in the "Code Point" column) is in fact the Single Low-9 Quotation Mark that you see in SSMS. That character, in UTF-8, is a 3 byte sequence: E2 80 9A.

What all of this means is: your Python code needs to either set the client-encoding for the SQL Server connection to Code Page 1252, or you need to change / convert the encoding of the returned string from Code Page 1252 to UTF-8.

Of course, if this is being displayed on a web page, then you could change the declared charset of the page to be Windows-1252, but that might interfere with other characters on the page if there are UTF-8 characters already there.


MySQL get next unique value without auto increment

What is interesting about this situation can be solved using the MyISAM storage.

I answedred a question like this back in April 2012 : How can you have two auto-incremental columns in one table? You need to create one table whose sole purpose is the create sequences of work order for each site

CREATE TABLE site_workorder_seq
(
    SiteID int not null,
    SiteWorkorderNum int not null auto_increment,
    PRIMARY KEY (SiteID,SiteWorkorderNum)
) ENGINE=MyISAM;

Here is a sample loading into this table:

mysql>     DROP DATABASE david;
Query OK, 1 row affected (0.01 sec)

mysql>     CREATE DATABASE david;
Query OK, 1 row affected (0.00 sec)

mysql>     USE david
Database changed
mysql>     CREATE TABLE site_workorder_seq
    ->     (
    ->         SiteID int not null,
    ->         SiteWorkorderNum int not null auto_increment,
    ->         PRIMARY KEY (SiteID,SiteWorkorderNum)
    ->     ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql>     INSERT INTO site_workorder_seq (SiteID) VALUES
    ->     (1),(1),(2),(3),(3),(3),(3),(4),(4),(4),
    ->     (5),(5),(4),(2),(2),(2);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql>     SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                1 |
|      1 |                2 |
|      2 |                1 |
|      2 |                2 |
|      2 |                3 |
|      2 |                4 |
|      3 |                1 |
|      3 |                2 |
|      3 |                3 |
|      3 |                4 |
|      4 |                1 |
|      4 |                2 |
|      4 |                3 |
|      4 |                4 |
|      5 |                1 |
|      5 |                2 |
+--------+------------------+
16 rows in set (0.00 sec)

mysql>

Let's look at the the last WorkorderNum from each site

mysql> SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
    -> FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                2 |
|      2 |                4 |
|      3 |                4 |
|      4 |                4 |
|      5 |                2 |
+--------+------------------+
5 rows in set (0.05 sec)

mysql>

Now, suppose you want to get the next SiteWorkorderNum for SiteID 3. You could do this:

INSERT INTO site_workorder_seq (SiteID) VALUES (3);
SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
FROM site_workorder_seq WHERE SiteID=3;
SELECT @nextworkordernum;

Let's run this and see what happens

mysql> INSERT INTO site_workorder_seq (SiteID) VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
    -> FROM site_workorder_seq WHERE SiteID=3;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @nextworkordernum;
+-------------------+
| @nextworkordernum |
+-------------------+
|                 5 |
+-------------------+
1 row in set (0.03 sec)

mysql> SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                1 |
|      1 |                2 |
|      2 |                1 |
|      2 |                2 |
|      2 |                3 |
|      2 |                4 |
|      3 |                1 |
|      3 |                2 |
|      3 |                3 |
|      3 |                4 |
|      3 |                5 |
|      4 |                1 |
|      4 |                2 |
|      4 |                3 |
|      4 |                4 |
|      5 |                1 |
|      5 |                2 |
+--------+------------------+
17 rows in set (0.00 sec)

mysql> SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
    -> FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                2 |
|      2 |                4 |
|      3 |                5 |
|      4 |                4 |
|      5 |                2 |
+--------+------------------+
5 rows in set (0.00 sec)

mysql>

As long as you use this one MyISAM apart from all InnoDB tables, you can generate workordernums per site to your hearts content.


Why do SQL Server uniqueidentifier values look different when converted to Db2?

I feel your pain. I've worked with both SQL Server and DB2. I will admit that IMHO, the data extraction and importing tools are not as simple or straightforward to use in SQL Server as they are in DB2.

If you wish to stick to the database tools, here is what I recommend: Create queries to extract your data. Use CONCAT to encase each field with a character (one that won't conflict with your data). Hopefully you don't have millions of rows. Then run the query and copy and paste results into something like Notepad++ and save it as a CSV file. You can now use db2import to pull that data in much cleaner and easier. Clunky, yes. But its the best I've found. I've yet to find an easy way to use bcp or SSIS to do what db2import, db2load, and db2export do....

Anyway, if that doesn't suit your fancy, or if the data sets are too large for that, you may wish to consider a different tool. There are ETL tools (perhaps you have one in shop?) to move the data across. Microsoft's SQL Server Integration Services (SSIS) or IBM's InfoSphere DataStage are just two examples of this. (I've not really worked with SSIS, and what little I tried, it wasn't simple or intuitive to me compared with db2export. But perhaps that is just me and I need further exposure/training.)

Another tool (and one I can happen to leverage as I am not only a DBA, but also a Java developer) is to leverage an existing API such as pure JDBC in tandem with Apache POI or Apache Commons-CSV to get the data extracted in simple format. POI lets you extract to Excel format (both .xls and .xlsx) and Commons-CSV lets you obviously extract to CSV format. And once again, when you have the data in CSV format, it is easy thing to import/load into DB2.

This will help with some of the data issues. You still may need to spot check the data to make sure you don't have any codepage conversions going on. If so, you may need to deal with that in your extraction process.

I realize others will mention bcp or SSIS. I can only speak to what I have experienced. But hopefully I've given you some other options to consider.


You haven't indicated the methodology you're loading your DW database with, but you may be seeing quite a bit of logging due to IDENTITY values per this blog.


pgAdmin III 1.12 does not support PostgreSQL 9.1. So better upgrade your pgAdmin III to version 1.14 or newer.


Postgres - indexing multi-tenant database

Yes, that single index would serve both queries. But you cannot get rid of the index on id, since it is necessary to implement the primary key constraint.
I would research if the indexes are really the cause of your performance problem.


Optimal PostgreSQL setup for Windows 2008 R2

In general, the consensus is that shared_buffers is less important to increase on Windows than it is on Linux.  Again a lot of this is very workload specific.

I would probably start with a max connections of 20 unless you have a lot of idle connections as the norm.  Start with defaults for work_mem and shared_buffers and experiment with tweaking upwards.  Always run test queries twice and discard the first one just to ensure comparable caching.  work_mem should be tweaked upwards if you have a lot of sorts or aggregates which would use a lot of memory per operation.  shared_buffers may need to go higher if you need to reserve more space in memory specifically for PostgreSQL.  However assuming you aren't running anything else that is a memory hog on the machine, I would start with default values and see how far those get you.  In most setups these should be sufficient for decent performance (occasionally even on Linux, lower shared_buffers can be helpful) and then watch for problems.


MariaDB Maxscale REST API can't show result

already got for default configuration when you need enable rest api maxscale

the tag must be [maxscale] and this is part when you need enable rest api for maxscale

[maxscale]
...
admin_host=192.168.101.107
admin_port=8989
admin_auth=1
admin_enabled=1

admin_host as your physical ip server

and then after you restart maxscale service, command maxctrl list servers or anything else can't run normally they need run command like this maxctrl -h 192.168.101.107:8989 list servers

and then you need test rest api

curl --include --basic --user "admin:mariadb" http://192.168.101.107:8010/v1/servers

and this is the results

HTTP/1.1 200 OK
Connection: Keep-Alive
Content-Length: 13156
Last-Modified: Wed, 13 May 2020 05:56:59 GMT
ETag: "0"
Date: Wed, 13 May 20 08:42:51 GMT
Content-Type: application/json

{
    "links": {
        "self": "http://192.168.101.107:8989/v1/servers/"
    },
    "data": [
        {
            "id": "server67",
            "type": "servers",
            "relationships": {
                "services": {
                    "links": {
                        "self": "http://192.168.101.107:8989/v1/services/"
                    },
                    "data": [
                        {
                            "id": "Read-Write-Service",
                            "type": "services"
                        }
                    ]
                },
                "monitors": {
                    "links": {
                        "self": "http://192.168.101.107:8989/v1/monitors/"
                    },
                    "data": [
                        {
                            "id": "MariaDB-Monitor",
                            "type": "monitors"
                        }
                    ]
                }
            },
            "attributes": {
                "parameters": {
                    "address": "192.168.101.67",
                    "socket": null,
                    "protocol": "MySQLBackend",
                    "port": 3306,
                    "extra_port": 0,
                    "authenticator": null,
                    "monitoruser": null,
                    "monitorpw": null,
                    "persistpoolmax": 0,
                    "persistmaxtime": 0,
                    "proxy_protocol": false,
                    "ssl": "false",
                    "ssl_cert": null,
                    "ssl_key": null,
                    "ssl_ca_cert": null,
                    "ssl_version": "MAX",
                    "ssl_cert_verify_depth": 9,
                    "ssl_verify_peer_certificate": false,
                    "disk_space_threshold": null,
                    "rank": "primary",
                    "serv_weight": "1"
                },
.......................


How do I correctly use %p and %f in postgresql.conf to make the Write Ahead Log?

You need to copy the WAL segment somewhere else, not to its original location.

“Somewhere else” in this context means different storage on a different physical machine. So cp is only a good method of archival if the destination is an NFS mount or similar. Otherwise, scp would be better.

You have to make as sure as you can that any trouble that befalls the original database won't affect the WAL archives.

It is also a good idea to test if the cp would overwrite anything, like the example in the documentation does. Otherwise, if two clusters archive to the same directory by mistake, you could end up with data loss.


How to select multiple columns but only group by one?

In SQL Server you can only select columns that are part of the GROUP BY clause, or aggregate functions on any of the other columns. I've blogged about this in detail here. So you have two options:

Add the additional columns to the GROUP BY clause:

GROUP BY Rls.RoleName, Pro.[FirstName], Pro.[LastName]

Add some aggregate function on the relevant columns:

SELECT Rls.RoleName, MAX(Pro.[FirstName]), MAX(Pro.[LastName])

The second solution is mostly a workaround and an indication that you should fix something more general with your query.


Get the value of one attribute from the reference key of another table?

Try this:

SELECT DISTINCT a.product_name
FROM `ps_order_detail` a
JOIN ps_product_lang b
    ON a.product_id=b.id_product


Synchronising databases / database technologies

I strongly recommend they look at transactional replication. I recommend against triggers simply because they rely on the network connection and the other server being up. There are potentially some other solutions depending on whether you care about replicating the whole database or whether you can afford to have a delay in synchronization.

If you only need to update the secondary system once a day, then you could set up very simple log shipping. You simply restore a copy of the full database over top of the existing copy once a night. This will kick out active users.

For more flexibility (e.g. to only include certain tables, and to minimize disruption to uses) you could write your own methodology, which I've used in a few solutions. First, create two additional schemas in the destination database:

CREATE SCHEMA hold AUTHORIZATION dbo;
GO
CREATE SCHEMA cache AUTHORIZATION dbo;
GO

Then create a copy of each dbo table in the cache schema:

CREATE TABLE cache.TableName
(
  Columns...
);
GO

Don't forget indexes but be mindful of foreign keys if there are any in the database that point at this table.

Now however often you think this data needs to be refreshed, you can run a job that does the following - puts a fresh copy of the source table data into the "hidden" copy of the table:

TRUNCATE TABLE cache.TableName;

INSERT cache.TableName(columns) EXEC LinkedServer.database.dbo.StoredProcedure;

Where the remote Stored Procedure just does a standard SELECT columns FROM dbo.TableName; on the source server. Doing it this way allows you to keep the query logic, isolation level and other factors on the source and out of the distributed query scaffolding.

Now, the magic sauce, we can use a fast, metadata operation that should result in minimal blocking and disruption to end users. This is basically playing switcheroo with the active data.

BEGIN TRANSACTION;
  ALTER SCHEMA hold  TRANSFER dbo.TableName;
  ALTER SCHEMA dbo   TRANSFER cache.TableName;
  ALTER SCHEMA cache TRANSFER hold.TableName;
COMMIT TRANSACTION;

After this you can optionally truncate the cache copy, however I always kept it until the next switcheroo for troubleshooting purposes (and easy rollback).

The nice thing about this one is that it is very easy to run this as frequently or infrequently as you like, and since 99% of the work is being done against tables the application and users aren't looking at, there is very little impact to end users.


Changing ORDER BY direction causes performance issue

If the different categories you are searching on are not to many and you can build the more complex query, this will use an index on (category_id, newsstudio_id) and I'd expect it to  be more efficient than your previous query, with either ASC or DESC choice.

It's a bit more complex to construct and I'd expect efficiency to degrade if the number of categories are tens or hundreds:

SELECT newsstudio_id
FROM
  ( ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 1303
      ORDER BY newsstudio_id DESC  LIMIT 5
    )
    UNION
    ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 1313
      ORDER BY newsstudio_id DESC  LIMIT 5
    )
      ...
    UNION
    ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 632
      ORDER BY newsstudio_id DESC  LIMIT 5
    )
  ) AS tmp
ORDER BY newsstudio_id DESC
LIMIT 5 ;


Conditionally insert a value in an array when creating a new column

array_remove() seems to answer my use case (by removing in a 2nd step the null values):
select array_remove(array[col -> 0 ->> 'v', col-> 1 ->> 'v'], null) from src


Database slowdown at random times using mirroing

Database mirroring involves two copies of a single database that reside on different computers. At any given time, only one copy of the database is available to clients. This copy is known as the principal database. Unlike log shipping which works by applying full transaction log backups to a warm standby database, database mirroring works by transferring and applying a stream of database log records from the principal database to the other copy of the database (the mirror database). Database mirroring applies every database modification that is made on the principal database to the mirror database. This includes data changes as well as changes to the physical and logical structures in the database, such as database files, tables, and indexes.

I know we have two of the three issues listed above, but if it cannot communicate with the mirror it just holds the transactions in the transaction log until they are able to be applied.  It doesn't use any form of locking  or modification of the data since it is being read from the log file.

So look at Log generation rate, network throughput, and I/O throughput, influence the deployment of database mirroring.

References:
http://msdn.microsoft.com/en-us/library/ms189852.aspx


How to update a table by count from another table?

You can use JOIN syntax in UPDATE and conditional count in the subquery

UPDATE country_statistics s CROSS JOIN
(
  SELECT SUM(CASE WHEN list LIKE '%United States%' THEN 1 ELSE 0 END) us,
         SUM(CASE WHEN list LIKE '%United Kingdom%' THEN 1 ELSE 0 END) uk,
         SUM(CASE WHEN list LIKE '%Canada%' THEN 1 ELSE 0 END) ca
    FROM entries
) q
   SET s.us = q.us, s.uk = q.uk, s.ca = q.ca
 WHERE id = 1

Here is SQLFiddle demo


Severity 17 indicates SQL Server ran out of a required resource.  You typically see this when a database runs out of space.  In this case, it sounds like tempdb is being used for read committed snapshot isolation version rows for a query that needed to spill to tempdb.

This could be quite common if tempdb has a limited maximum size and a poorly written query is attempted that, for instance, performs a very large cross join.

You can use this query to see the current size of the version store in tempdb:

SELECT *
FROM sys.dm_os_performance_counters dopc
WHERE dopc.counter_name LIKE 'Version %';

Technet has an interesting article on version store usage at https://technet.microsoft.com/en-us/library/cc966545.aspx#EDAA

This MSDN page mentions error 3967:

  When tempdb runs out of space, the Database Engine forces the version stores to shrink. During the shrink process, the longest running transactions that have not yet generated row versions are marked as victims. A message 3967 is generated in the error log for each victim transaction. If a transaction is marked as a victim, it can no longer read the row versions in the version store. When it attempts to read row versions, message 3966 is generated and the transaction is rolled back. If the shrinking process succeeds, space becomes available in tempdb.

Neither of the pages I've linked above contain explicit advise about the maximum size of the version store in tempdb, which leads me to believe the only limitation is the maximum configured size of the tempdb itself, minus whatever space is being used in tempdb for other objects.


sqlplus connect without typing the @local_SID when only have one instance

What OS are you using?

Check what the environment ORACLE_SID is set to.  Change it to the existing one and you won't be needing to specify the connection string anymore.


In READ COMMITTED isolation, is it possible to read the old value after the update?

You can use trace flag 1200 to print out locking info and 3916 to print logging info. (Both undocumented AFAIK)

For the following test set up

CREATE TABLE T
(
A INT IDENTITY PRIMARY KEY,
B INT
)

CREATE NONCLUSTERED INDEX IX ON T(B)

INSERT INTO T
SELECT number
FROM master..spt_values

Then running the following twice (as the second run doesn't contain the irrelevant locks acquired during compilation)

DBCC TRACEON(3604,1200,3916,-1) WITH NO_INFOMSGS

SET NOCOUNT ON;

UPDATE T
SET B += 1
WHERE A = 1000

/*If you habitually have 3604 on then change the below*/
DBCC TRACEOFF(3604,1200,3916,-1) WITH NO_INFOMSGS

This returns the following output

  Process 54 acquiring IX lock on OBJECT: 11:245575913:0  (class
  bit2000000 ref1) result: OK

  Process 54 acquiring IU lock on PAGE: 11:1:127  (class bit0 ref1)
  result: OK

  Process 54 acquiring U lock on KEY: 11:72057594039042048
  (1f00de11a529) (class bit0 ref1) result: OK

  Process 54 acquiring IX lock on PAGE: 11:1:127  (class bit2000000
  ref0) result: OK

  Process 54 acquiring X lock on KEY: 11:72057594039042048
  (1f00de11a529) (class bit2000000 ref0) result: OK

  XdesId 0000:00000423: m_logReserved 9162, delta 9162, op
  LOP_BEGIN_XACT, caller GenerateLogRec, LSN 00000027:00000159:0001.

  XdesId 0000:00000423: m_logReserved 9379, delta 217, op
  LOP_MODIFY_ROW, caller GenerateLogRec, LSN 00000027:00000159:0002.

  Process 54 acquiring IX lock on PAGE: 11:1:166  (class bit2000000
  ref1) result: OK

  Process 54 acquiring X lock on KEY: 11:72057594039107584
  (dbf572a551f4) (class bit2000000 ref1) result: OK

  XdesId 0000:00000423: m_logReserved 9597, delta 218, op
  LOP_DELETE_ROWS, caller GenerateLogRec, LSN 00000027:00000159:0003.

  Process 54 acquiring IX lock on PAGE: 11:1:166  (class bit2000000
  ref0) result: OK

  Process 54 acquiring RangeI-N lock on KEY: 11:72057594039107584
  (a39688da7d04) (class bit1000000 ref1) result: OK

  Process 54 acquiring X lock on KEY: 11:72057594039107584
  (82cbfa4b30c1) (class bit2000000 ref0) result: OK

  XdesId 0000:00000423: m_logReserved 9671, delta 74, op
  LOP_INSERT_ROWS, caller GenerateLogRec, LSN 00000027:00000159:0004.

  Process 54 releasing lock reference on KEY: 11:72057594039107584
  (dbf572a551f4)

  Process 54 releasing lock reference on PAGE: 11:1:166

  Process 54 releasing lock reference on KEY: 11:72057594039042048
  (1f00de11a529)

  Process 54 releasing lock reference on PAGE: 11:1:127

  XdesId 0000:00000423: m_logReserved 0, delta -9671, op
  LOP_COMMIT_XACT, caller GenerateLogRec, LSN 00000027:00000159:0005.

No lock is taken on the non clustered index (1:166) until after the LOP_MODIFY_ROW update on the clustered index key (on page 1:127) so yes this would be possible.

And the above is with a narrow (per row) update plan.

With a wide (per index) plan each index is updated in turn. Possibly with intervening sort operations as in the plan below.

This would make the window of opportunity wider but, whilst a transaction could potentially read the "old" value from an index yet to be updated, it would not be possible for a read committed transaction to read the "new" version of the value until the transaction was committed.

It is certainly possible for a read committed statement to read two different committed versions of the same value though.

In one connection run

WHILE ( 1 = 1 )
  BEGIN
      UPDATE T
      SET    B += 1
      WHERE  A = 1000;
  END

And then in another run

SELECT *
FROM   T T1 WITH(READCOMMITTEDLOCK )
       INNER HASH JOIN T T2  WITH(READCOMMITTEDLOCK )
         ON T1.A + 0 = T2.A + 0
WHERE  T1.A = 1000
       AND T2.A = 1000

For me about 50% of the time this second query returns a row with different B values as the value changes between the two seeks on T.


Which DB for 1 table with 100 Million rows per year?

With that table an no extra keys/indexes you have 160 bytes per row, so 100,000,000 rows per year is about 16GByte per year. Any decent DBMS (SQL Server, postgres, [YourFavouriteDBHere], ...) should be able to cope with that and (assuming proper indexing) query it efficiently if you have appropriate hardware/virtual resources. The extra space taken by keys and other indexes shouldn't balloon the space requirements much, if it does then it is likely that the structure is not optimal more generally.

So simply storing the data should not be a worry.

Some databases support compression, sparse tables, and other techniques, that will help considerably with this structure if space is your primary concern, but before considering them first ensure that this is in fact the structure that you need.

As others discuss in the comments, your current structure may not be optimal for the analysis that you need to perform, so you would need to edit your question to include such details if you need help there. A key thing in all database design is to consider your desired outputs as well as your inputs.

  Unfortunately I don't know every required select-query at the moment.

You must have some idea of the sort of reports you expect to run against the data. It doesn't need to be every query you'll ever run, or even close to, but optimising for some expected reports is far better than blindly ploughing in data in the hope that it might be optimal for something/anything that will eventually come up.

Where at all possible, do not design based purely on your inputs.

Without any idea of the outputs, perhaps yes: just throw the data into a table like that with indexes on the date at least as that is very likely to be one of the key ways you'll want to filter/partition/aggregate the data in reports, and transform the data into something else via ETL when you do know what analysis you need to perform. But if you have an idea of the outputs to start with, you might be able to avoid having to create and maintain two structures (one for active recording and one for reporting) and the process to transform data from one into the other. Of course, this two structure system might be optimal, but we simply can't tell you one way or the other without more detail.


Database returned to CIRCULAR mode but logs are still being archived to old location

Run GET DB CFG with the SHOW DETAIL option, which will reveal any settings that are delayed (not yet active). Any difference between the "Current" and "Delayed" value indicates a DB CFG change that was made after the database was last activated.

If the old path isn't showing up in any of the settings in the "Current" column of the database config, then examine the logging settings of other local databases on the system (and in other local instances if any exist) to see if they're the culprit.

It also wouldn't hurt to take a look at the crontab schedules on the server to see if a cron job is responsible for copying the files to an inappropriate path.


Split MySql table into two tables with One to many Relationship

Not enough time to test or write out all of the table creation but this is a start...

First create the table to hold the users. You can get data from the following query. One thing I don't know how you want to handle is if a user exists with a different phone number.

SELECT DISTINCT name
  , phone
FROM old_table

After you have the user_accounts table join it back to the old_table to get the address along with the new user id.

SELECT DISTINCT a.address
  , b.id
FROM old_table a
INNER JOIN user_accounts b on a.name = b.name and a.phone = b.phone


Yes, this is not difficult: Just create the users table using a different storage engine than NDB.

  It is possible to create tables using other storage engines (such as
  InnoDB or MyISAM) on a MySQL server being used with an NDB Cluster,
  but since these tables do not use NDB, they do not participate in
  clustering; each such table is strictly local to the individual MySQL
  server instance on which it is created.

Source: Appendix A.20 MySQL 5.7 FAQ: NDB Cluster


Database design considerations for unused columns with every table has same schema

1) Yes. It's a maintenance/documentation nightmare but technically there's no reason it wouldn't work.

2) In general each null will be one bit of storage. So 80 null fields might be 10 bytes per row. The full answer is that it varies depending on data type but with varchar for the most part it's a good rule of thumb.

Some alternatives where you expect fields to be null, are column sets (see https://msdn.microsoft.com/en-AU/library/cc280521.aspx); where the engine munges those often unused columns into a single XML column. I don't think it's a very good fit for your purposes because it messes up indexing and introduces a bunch of limitations - but it's something to be aware of.

I don't think performance is going to be affected though. It will mean that you'll have additional lookups if queries are doing select * instead of just the columns they need (and so it would be hard to create proper covering indexes). But that's a generic problem and not specific to this design.

3) I think you should probably try to forget refactoring it and just go ahead building your application as best as you can with what you have.

A database developer would probably start by importing one of the existing databases into SSDT (a Visual Studio database project) which will create the schema, then they'd do any minor fixes required to get a build going, and see that it can create a usable empty database for use with the software.

After that they'd go over the source code and start using SSDT's right click refactor functionality to give the tables and columns proper names; which will rename all the procedures/functions etc that use it.

This still leaves any dynamic SQL broken, external reports broken, and anything that lives outside of the database broken (the application, web services, queries from customers with direct access!) This means it's a pretty big and collaborative effort to fix.

I'm almost certain they will run across fields which have multiple meanings - because that's what happens when people build software this way. Luckily if they were doing all of this then they can use that opportunity to split dual-usage fields into separate fields.

But can you do that? I imagine it's outside of your scope, and businesses generally don't like paying for such things when "why would you need to do that, the software works as-is!"


How to set delayed replication with MariaDB 10.0.11

MASTER_DELAY is not available in MariaDB 10.0.x. You may want to try Percona's pt-slave-delay.

Usage:

pt-slave-delay [OPTION...] SLAVE-HOST [MASTER-HOST]

Example: To hold slavehost one minute behind its master for ten minutes:

pt-slave-delay --delay 1m --interval 15s --run-time 10m slavehost

Source: http://www.percona.com/doc/percona-toolkit/2.1/pt-slave-delay.html


To avoid grouping by a specific column that returns multiple values, you can either remove it from the query, or you can explicitly tell it which value you want. You can do this using aggregate or analytic functions, like:

MAX(ExtensionID)
MIN(ExtensionID)
FIRST_VALUE(ExtensionID) OVER (...)
LAST_VALUE(ExtensionID) OVER (...)

For numeric values you can also present new values, such as:

SUM(Duration)
AVG(Duration)

But basically, if you want to show a single row collapsed from two different ExtensionID values, you need to define how to do that. In this case, is ExtensionID important? If it is, do you want to show the most recent one? The first one? The one that occurs most frequently? What about ties? There are solutions to all of these, but you have to know what you want your query to return.

You can't just leave things out of the GROUP BY like you can with MySQL, where you get - let's say - weird results.


Subset of Unordered Pairs of Distinct Primary Keys With Normalization

You can enforce, that valueone < valuetwo. Unfortunately MySQL doesn't support check constraints with which this would be easy (You can define them, but they have no effect). You'd have to use triggers.

CREATE TRIGGER pairs_bi
               BEFORE INSERT
                      ON pairs
               FOR EACH ROW
BEGIN
  IF new.valueone >= new.valuetwo THEN
    SIGNAL SQLSTATE '45000'
           SET MESSAGE_TEXT = 'valueone >= valuetwo';
  END IF;
END;

CREATE TRIGGER pairs_bu
               BEFORE UPDATE
                      ON pairs
               FOR EACH ROW
BEGIN
  IF new.valueone >= new.valuetwo THEN
    SIGNAL SQLSTATE '45000'
           SET MESSAGE_TEXT = 'valueone >= valuetwo';
  END IF;
END;

To enforce uniqueness you can then use a unique constraint/index or, if it where me, I'd simply define the two columns as primary key.

ALTER TABLE pairs
            DROP id;

ALTER TABLE pairs
            ADD PRIMARY KEY (valueone,
                             valuetwo);

db<>fiddle


Why "SET LOCAL statement_timeout" does not work as expected with PostgreSQL functions?

The way statement_timeout works, the time starts counting when the server receives a new command from the client.

Queries launches inside server-side functions are not commands from a client, they don't reset that timer or push a new one onto a stack of timers.

This is why SET LOCAL statement_timeout = 100; has no effect.

And if a function does SET statement_timeout = 100; it will have an effect only starting at the next command from the client.

I don't see any way to control the execution time of individual queries inside a function.


SQL SERVER FileTable/FILESTREAM share suddenly becomes unavailable

It appears that after setting it via configuration manager the issue went away.


Join 3 tables of a one-to-many relationship

Obviously, there can be multiple rows with the same value in table1.sri and / or in table2.seg_guid. That's the only logical explanation for the multiplied row count in your query result.

Which of both is impossible to tell from the information you provided. But the name "trucknetwork" indicates there should be many "street names" in the result, so your task seems contradictory at the outset.

My educated guess is that the multiplication happens in table1 (many start and end points of roads in the same truck network). There are various techniques to reduce to a single pick. The best one depends on information not in the question. Since you get so many result rows, a LATERAL join with LIMIT 1 is a hot contender. Be sure to support it with an index.

Since you want to join to exactly 1 row in the result for each row in trucknetwork, you have to define which row to pick from a set of multiple matches. More information we don't have. I pick an arbitrary winner.

And there is also the possibility that some of the rows in trucknetwork might find no matching row in table1 and/or table2 at all. In an ideal world, referential integrity would be enforced with FOREIGN KEY constraints. To make do with what we have, use LEFT [OUTER] JOIN to avoid eliminating trucknetwork rows (which would probably indicate data errors in table1 or table2).

So:

CREATE TABLE network_ex AS
SELECT shp.descriptio AS descr, shp.road_type, shp.geom, t2.s_name AS street_name
FROM   trucknetwork shp
LEFT   JOIN LATERAL (
   SELECT seg_guid
   FROM   table1
   WHERE  sri = shp.sri
   -- ORDER  BY ???      -- define which one to pick if that matters
   LIMIT  1              -- ! just the one !
   ) t1 ON true
LEFT   JOIN table2 t2 USING (seg_guid);

Ideally, you would have these two multicolumn indexes to make this fast:

CREATE INDEX table1_sri_seg_guid_idx ON table1 (sri, seg_guid);
CREATE INDEX table2_seg_guid_s_name_idx ON table2 (seg_guid, s_name);

If there can be duplication in table2 (as well), reduce to a single match in a similar fashion. Again, the best technique depends on the complete picture.

Alternatively, you could aggregate all (distinct) road names into a single string or array in the result ...

Related:

What is the difference between LATERAL and a subquery in PostgreSQL?
Optimize GROUP BY query to retrieve latest record per user

Asides:

Use CREATE TABLE AS. SELECT INTO is discouraged:

Creating temporary tables in SQL

Avoid unquoted CaMeL-case identifiers in Postgres.

Are PostgreSQL column names case-sensitive?


Since the additional trace flags allowed you to narrow the delay down to the stage where the process tries to write to msdb, I'm willing to bet one or both of these returns high numbers:

SELECT MAX(row_count), SUM(in_row_reserved_page_count)
  FROM msdb.sys.dm_db_partition_stats
  WHERE [object_id] = OBJECT_ID('msdb.dbo.backupset');

SELECT database_name, [type], c = COUNT(*)
  FROM msdb.dbo.backupset
  GROUP BY database_name, [type]
  ORDER BY c DESC;

You can consider whittling away at some of the data in that table. Of course, only you can know if you have reasons to keep backup history that otherwise should probably be cleaned up in most cases (for example, the first rule I declare to identify junk history data is for databases that no longer exist - but what if you have renamed a database recently?). So please think about each of these rules and only delete backups where the rules make sense to you based on these scenarios. For example:

CREATE TABLE #bs(id INT, ms INT);

-- Databases that no longer exist on this server:
INSERT #bs(id,ms) SELECT backup_set_id, media_set_id
FROM msdb.dbo.backupset
WHERE database_name NOT IN (SELECT name FROM sys.databases);

-- All copy_only backups (because who cares?):
INSERT #bs(id,ms) SELECT backup_set_id, media_set_id
FROM msdb.dbo.backupset
WHERE is_copy_only = 1;

-- All diffs except the most recent:
;WITH x AS
(
  SELECT backup_set_id, media_set_id,
    rn = ROW_NUMBER() OVER (PARTITION BY database_name
      ORDER BY backup_set_id DESC)
  FROM msdb.dbo.backupset
  WHERE [type] = 'I'
  AND backup_finish_date IS NOT NULL
)
INSERT #bs(id,ms) SELECT backup_set_id, media_set_id
FROM x WHERE rn > 1;

...

-- All log backups prior to the most recent full:
INSERT #bs(id,ms) SELECT backup_set_id, media_set_id
FROM msdb.dbo.backupset AS l
WHERE [type] = 'L' AND EXISTS
(
  SELECT 1
    FROM msdb.dbo.backupset AS bs2
    WHERE database_name = l.database_name
    AND [type] = 'D'
    AND backup_finish_date > l.backup_finish_date
);

-- All fulls except the most recent successful non-copy-only full backup:
;WITH x AS
(
  SELECT backup_set_id, media_set_id,
    rn = ROW_NUMBER() OVER (PARTITION BY database_name
      ORDER BY backup_set_id DESC)
  FROM msdb.dbo.backupset
  WHERE [type] = 'D'
  AND is_copy_only = 0
  AND backup_finish_date IS NOT NULL
)
INSERT #bs(id,ms) SELECT backup_set_id, media_set_id
FROM x WHERE rn > 1;

Need to also delete some file and filegroup information:

DELETE msdb.dbo.backupfile
WHERE backup_set_id IN (SELECT id FROM #bs);

DELETE msdb.dbo.backupfilegroup
WHERE backup_set_id IN (SELECT id FROM #bs);

DELETE mf
FROM msdb.dbo.backupmediafamily AS mf
WHERE media_set_id IN (SELECT ms FROM #bs)
AND NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset
  WHERE media_set_id = mf.media_set_id);

DELETE ms
FROM msdb.dbo.backupmediaset AS ms
WHERE media_set_id IN (SELECT ms FROM #bs)
AND NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset
  WHERE media_set_id = ms.media_set_id);

You also need to delete associated restore history:

DELETE msdb.dbo.restorefilegroup WHERE restore_history_id IN
(SELECT restore_history_id FROM msdb.dbo.restorehistory
  WHERE backup_set_id IN (SELECT id FROM #bs));

DELETE msdb.dbo.restorefile WHERE restore_history_id IN
(SELECT restore_history_id FROM msdb.dbo.restorehistory
  WHERE backup_set_id IN (SELECT id FROM #bs));

DELETE msdb.dbo.restorehistory
  WHERE backup_set_id IN (SELECT id FROM #bs);

And then finally the main rows can be deleted without violating any constraints:

DELETE msdb.dbo.backupset
WHERE backup_set_id IN (SELECT id FROM #bs);

DROP TABLE #bs;

This whole process will not likely be fast - I highly recommend doing it during a maintenance window or slow period, and most certainly suspend all backup jobs while doing this.

On Standard Edition there's no real clean way to do this as an online operation, but after you've deleted all those rows, you'll likely benefit most from a full rebuild:

ALTER INDEX ALL ON msdb.dbo.backupset REBUILD;

The above was based partially on the logic included in the system procedure dbo.sp_delete_backuphistory, which leaves out some of the above criteria, has a reputation for locking up the entire set of backup- and restore-related msdb tables, and only allows a singular filter on date (which may not be very helpful depending on a variety of factors, including the percentage of bloat attributed to log backups prior to their most recent fulls, never mind the fact that different databases may be on completely different schedules). You can see where most of my logic came from, though, by looking at the definition for yourself:

EXEC msdb.sys.sp_helptext @objname = N'dbo.sp_delete_backuphistory';


Combine multiple records into one, using name from only one record

this query will get you the results you wanted for your example

CREATE TABLE tracking(name VARCHAR(50), cnt INT);
INSERT INTO tracking
VALUES( 'Maddoc 74',229),
      ('M74',21),
      ('Maddoc7',14),
      ('John Smith',215),
      ('jsmith',47),
      ('johns',19),
      ('Anna Kellog',251),
      ('akellog',67),
      ('annak',14);
WITH cte AS(
SELECT ROW_NUMBER() OVER(ORDER BY name) AS rn,name,SUBSTRING(name,1,1)+SUBSTRING(name,CHARINDEX(' ',name,1)+1,LEN(name)) AS firstl,SUBSTRING(name,1,CHARINDEX(' ',name,1)-1)+SUBSTRING(name,CHARINDEX(' ',name,1)+1,1) AS flast
FROM tracking
WHERE name LIKE '% %')
SELECT MIN(c.name),SUM(t.cnt)
FROM tracking t
INNER JOIN cte c
ON t.name = c.name
OR t.name = c.firstl
OR t.name = c.flast
GROUP BY rn;

You will have issues if you also have a Jane smith, John Sanders, etc. this also doesn't account for middle names in the full name


How to design database with january-december for every entry

You can build a view that "unpivots" the table and divides item_quantity and total by 12 for each row. The syntax would look something like this:

CREATE VIEW transactions_pivot
AS

SELECT t.td_id, t.item, t.item_cost, x.m AS month_no, t.item_quantity/12
FROM transactions AS t
CROSS JOIN (
    SELECT 1 AS m UNION ALL
    SELECT 2 AS m UNION ALL
    SELECT 3 AS m UNION ALL
    SELECT 4 AS m UNION ALL
    SELECT 5 AS m UNION ALL
    SELECT 6 AS m UNION ALL
    SELECT 7 AS m UNION ALL
    SELECT 8 AS m UNION ALL
    SELECT 9 AS m UNION ALL
    SELECT 10 AS m UNION ALL
    SELECT 11 AS m UNION ALL
    SELECT 12 AS m) AS x

The CROSS JOIN is like a regular JOIN, but without any conditions, so it produces a cartesian product (12 rows for each row in "t"). There are prettier (and faster) ways of writing the code on different platforms, but this syntax is ANSI SQL compatible.

On a side-note: I wouldn't include "total" as a column, since that's calculated from the number of items multiplied by the item price.


If your Database has enabled AUDIT and your auditing tablespace SYSAUX gets 100% full,  NONE of database users will be able to log in to database anymore. Your database will not crash but only SYS db user will be able to log in - the first thing you should do in this case is to extend the SYSAUX tablespace (add a data file) and deal later with how SYSAUX got full at the first place.


how to Protect certificate in sql server

While it's true that anyone with read permissions can browse the database using SQL Server Management Studio, additional permissions need to be granted to use the keys for decrypting the data. The first point to keep in mind is that a certificate is a public object, and it's the related private key that needs protection to ensure that it can't be used by unauthorized users to decrypt  the symmetric key and subsequently the data. The private key is encrypted by the Database Master Key and is not visible to any user, even to those in the sysadmin role. Just because a user can see the objects doesn't mean they can use them. The permissions needed to decyrypt data using the encryption hierarchy you described, which is a symmetric key protected by certificate protected by the database master key, are CONTROL on the certificate and REFERENCES on the symmetric key. Without both of those permissions, decryption will not be possible. However, if you grant the CONTROL permission on the certificate directly to the user or the user is in the db_owner, db_ddladmin or db_securityadmin role, then the user can back up the certificate and private key, which is a threat to the security of your encryption. In addition to removing the users from the aforementioned roles, there are several ways to address this issue. One is to use an asymmetric key in your hierarchy instead of a certificate, since there is no backup command for asymmetric and symmetric keys. The other is to use code signing and only grant the permissions to a certificate user. Someone also mentioned that it may be possible to use impersonation , or "execute as" in a procedure, but this may not be the safest choice. If you want to replace the certificate with an asymmetric key in your hierarchy, then you should back up the Database Master Key for the database and backup the database, then restore both in a non production environment so you can create the script and test it completely before attempting this in a production environment.

If you really want to hide the names of all of the objects, then you can deny view definition to the users in the database. Again, test in non-production first.


How do you figure out what planner cost constants to use in Postgres?

First, the costs are not supposed to directly relate to the execution time.  They're strictly relative; a plan which costs more should take longer to actually execute.  You can adjust sequential_page_cost in order to "tune" costs so that they're closer to milleseconds of execution, but IMHO that's a waste of time.

For 99% of users, there's only three cost constants they care about at all:

effective_cache_size: set this to 75% of available RAM on your system.  Done.

random_page_cost: if you're using high-end SAN/DAS, lower this to 2.0.  If you're using SSDs or on Amazon, lower it to 1.5.  Otherwise, leave it alone.

effective_io_concurrency: if you're on Linux and have a drive array, raise this to the number of drives (or mirrored pairs) the database is stored on, maximum 4.

Tinkering with the other cost constants is not recommended unless you have a full performance test setup to determine if the result is really beneficial or not.


Query against partitioned table accessing more partitions than necessary?

These trivial queries are automatically parameterized. As such, the literal values are replaced with parameters during compilation. Partitions are eliminated at runtime dynamically using the internal RangePartitonNew function in a range seek predicate. Importantly, the seek predicate start/end range is inclusive so that the same cached plan can be reused for any possible value, not just exact boundary matches as in your queries. Rows are subsequently filtered based on the WHERE clause predicate to return only the rows desired.
Below is the seek predicate from the first 2 queries. At run time, the first query seek is equivilent to partition_number >= 1 AND partition_number <= 1 and the second is partition_number >= 1 AND partition_number <= 2. Partition 1 is hard-coded as the start range due to the less than operator.
Seek Keys[1]:
    Start: PtnId1001 >= Scalar Operator((1)),
    End: PtnId1001 <= Scalar Operator(RangePartitionNew(CONVERT(datetime2(0),[@1],0),(1),'2020-01-01 00:00:00','2021-01-01 00:00:00'))

The seek predicate from the third query differs only in that the start range also includes RangePartitionNew to eliminate partitions excluded by the greater than or equal to comparison operator. This seek is equivilent to predicate partition_number >= 2 AND partition_number <= 3.
Seek Keys[1]:
    Start: PtnId1001 >= Scalar Operator(RangePartitionNew(CONVERT(datetime2(0),[@1],0),(1),'2020-01-01 00:00:00','2021-01-01 00:00:00')),
        End: PtnId1001 <= Scalar Operator(RangePartitionNew(CONVERT(datetime2(0),[@2],0),(1),'2020-01-01 00:00:00','2021-01-01 00:00:00'))

You can avoid simple parameterization with these techniques from Paul's blog article:

OPTION (RECOMPILE)
Add a dummy predicate like WHERE 1 <> 2

If exact boundary values are your only use case, you could add a predicate to evaluate the partition function with a less than comparison operator. This will retain parameterization benefits. For example:
AND $PARTITION.partfun(logdate) < $PARTITION.partfun('20200101')

The resultant parameterized plan seek predicate subtracts one from the end of the boundary range to eliminate the partition excluded by the above predicate:
Seek Keys[1]:
    Start: PtnId1001 >= Scalar Operator((1)),
    End: PtnId1001 <= Scalar Operator(RangePartitionNew(CONVERT_IMPLICIT(datetime2(0),[@2],0),(1),'2020-01-01 00:00:00','2021-01-01 00:00:00')-(1))

Note that an index on the partitioning column will mitigate the need for these work-arounds. Even though an extra partition will be touched with exact boundary values, the overhead is only a couple of additional logical reads.


You can use a bit of dynamic SQL.  Assuming that you want to modify every VARCHAR2 column in one table

DECLARE
  l_sql_stmt varchar2(1000);
BEGIN
  FOR c IN (SELECT *
              FROM user_tab_columns
             WHERE data_type = 'VARCHAR2'
               AND table_name = <<table name>>
               AND char_used = 'B')
  LOOP
    l_sql_stmt :=
       'ALTER TABLE <<table name>> MODIFY( ' ||
          c.column_name || ' VARCHAR2( ' || c.char_length || ' CHAR) )';
    EXECUTE IMMEDIATE l_sql_stmt;
  END LOOP;
END;


DB2 Indexes are taking very large size on disk space

Indexes essentially duplicate data on disk. The idea though is the tradeoff in performance. Extra disk used, but faster response looking up data.

But since indexes do cost extra disk, one should weigh carefully whether or not the index is actually needed. If not needed, it will cost that extra disk and may even cost you in terms of performance (especially on inserts, updates, and deletes).


Getting pg_dump and pg_restore right for postgresl (9.3.10)

It's perfectly safe with especially with --serializable-deferrable however you're not answering the real question which is what do you do with data inserted while you're dumping and moving the database. The best way, in my opinion is the hard maintenance window.
I would use -c.
an inconstant backup or a failure to load because of a missing extension causes restoration errors, though I don't think you'll have that problem.

Since you know Azure supports 9.6 (they say so here), I would

back up the data directory
back up the database
upgrade to 9.6 locally
run it and make sure it works.

If it works, pg_dump and move move to Azure.


Despite being answered 10 years ago, I don't think I could improve on Justin Cave's very applicable answer from this similar question on SO: Should an Oracle database have more than one tablespace for data storage?

  My bias (and this is largely a matter of personal preference) is that
  if there is no compelling benefit to creating additional tablespaces,
  life is easier with a single tablespace.

  There is no performance benefit to putting objects in different tablespaces.  There is an old myth that separating tables and indexes
  would have some performance benefits.  There is a potential benefit to
  spreading I/O over all available spindles, but that's better done with
  multiple data files in a single tablespace then with multiple
  tablespaces since Oracle does a round-robin allocation of extents in
  different data files assuming that your SAN isn't already doing
  something to even out I/O.
  If you have large, static lookup/ history tables such that you could bring a new copy of the database to the client site by just bringing
  the smaller transactional tablespaces, that would be a reason to
  consider multiple tablespaces.  But there are very few applications
  that have this sort of setup.  If you'll have to bring all 200 GB, it
  doesn't matter how many tablespaces you have.
  Along the same lines, if you have large read-only objects, putting them in a read-only tablespace can vastly decrease the time and space
  required for backups.  Again, though, this isn't particularly common
  in practice outside of data warehouses.
  If your application could run without some subset of objects, there may be a benefit to creating separate tablespaces so that you could
  take one offline and do a tablespace-level restore.  Again though, few
  applications could run without a set of objects-- if you lose the
  index tablespace, for example, the application is likely just as dead
  as had you lost everything.
  If you have a large number of empty or mostly empty tables and a number of very large tables, separate tablespaces with different
  extent allocation policies may be preferrable from a space utilization
  standpoint.  This happens occasionally with packaged apps where any
  given installation is using a relatively small percentage of the
  available tables and you don't want each of the empty tables to have a
  relatively large extent assigned to it.  With automatic extent
  management in a locally managed tablespace, this tends not to be a
  major concern, it may be more concerning if you want to use uniform
  extents.
  If different objects have different priorities for disk performance, and you have different types of disk available, separate tablespaces
  can allow you to put different objects on different sets of disks.  In
  a data warehouse, for example, you may want to put older data on
  slower, cheaper disk and newer data on more costly disk.  This doesn't
  happen much with OLTP applications.

  Unless your application falls into one of these special cases, the
  only benefit to having separate tablespaces is to appeal to a DBA's
  sense of organization.  Personally, I'm more than happy to be able to
  avoid specifying a tablespace name every time I create an object or to
  spend cycles moving objects from the "wrong" tablespace when they
  inevitably get created in the default tablespace mistakenly.
  Personally, I'm not overly concerned if a few tens of MB of space are
  "wasted" when using locally managed tablespaces with automatic extent
  management over a hand-optimized set of tablespaces with different
  uniform extent sizes.  On the other hand, good DBA's tend to be very
  concerned about things being organized "just so" so I'm not militantly
  opposed if a DBA wants to have separate index and data tablespaces
  just because that appeals to someone's sense of aesthetics.


How to model Parent -> Child -> Grandchild if the Child can be removed

This answer is based upon your question as it stood before the clarification about each level being a different type.   Since you have identified the need for different types, I agree with my answer as it originally appeared, and your self-answer documenting how you've approached this problem.  Adding a single column to the grandchild table, referencing the top-most table, seems the simplest approach.

I'm leaving the below details in-place in case it helps a future visitor.

I would implement this with a cross reference table.

Below is a SQL Server-specific example; this table contains columns about the entities, including name, etc:

CREATE TABLE dbo.Entities
(
    EntityID int NOT NULL
        CONSTRAINT PK_Entities
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , EntityName varchar(30) NOT NULL
);

This table describes their relationships:

CREATE TABLE dbo.EntityRelationships
(
      EntityIDParent int NOT NULL
        CONSTRAINT FK_EntityRelationships_Parent
        FOREIGN KEY
        REFERENCES dbo.Entities (EntityID)
    , EntityIDChild int NOT NULL
        CONSTRAINT FK_EntityRelationships_Child
        FOREIGN KEY
        REFERENCES dbo.Entities (EntityID)
    , CONSTRAINT PK_EntityRelationships
        PRIMARY KEY CLUSTERED (EntityIDParent, EntityIDChild)
    , CONSTRAINT CK_EntitytRelationships
        CHECK ((EntityIDParent <> EntityIDChild))
);

Each relationship must be unique, that is any given parent can only be related to any given child once.

Next, we create an INSTEAD OF DELETE trigger on the Entities table that will handle deletes properly, by reparenting any relationships necessary, prior to removing the deleted Entity:

CREATE TRIGGER EntityRelationshipDelete
ON dbo.Entities
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.EntityRelationships (EntityIDParent, EntityIDChild)
    SELECT erp.EntityIDParent
        , erc.EntityIDChild
    FROM deleted d
        INNER JOIN dbo.EntityRelationships erp ON d.EntityID = erp.EntityIDChild
        INNER JOIN dbo.EntityRelationships erc ON d.EntityID = erc.EntityIDParent
    EXCEPT --don't create duplicate entries
    SELECT er.EntityIDParent, er.EntityIDChild
    FROM dbo.EntityRelationships er;

    DELETE
    FROM dbo.EntityRelationships
    FROM dbo.EntityRelationships er
        INNER JOIN deleted d ON er.EntityIDChild = d.EntityID OR er.EntityIDParent = d.EntityID;

    DELETE
    FROM dbo.Entities
    FROM dbo.Entities e
        INNER JOIN deleted d ON e.EntityID = d.EntityID;
END;
GO

Here we'll test that setup:

INSERT INTO dbo.Entities (EntityName)
VALUES ('Grandparent')
    , ('Parent')
    , ('Child');

INSERT INTO dbo.EntityRelationships (EntityIDParent, EntityIDChild)
VALUES (1, 2)
    , (2, 3);

SELECT Parents.EntityName
    , Children.EntityName
FROM dbo.EntityRelationships er
    INNER JOIN dbo.Entities Parents ON er.EntityIDParent = Parents.EntityID
    INNER JOIN dbo.Entities Children ON er.EntityIDChild = Children.EntityID;

The results of the select above:

╔═════════════╦════════════╗
║ EntityName  ║ EntityName ║
╠═════════════╬════════════╣
║ Grandparent ║ Parent     ║
║ Parent      ║ Child      ║
╚═════════════╩════════════╝

Here, we'll delete the "Parent" entity, and re-query the relationships:

DELETE
FROM dbo.Entities
WHERE dbo.Entities.EntityName = 'Parent';

SELECT Parents.EntityName
    , Children.EntityName
FROM dbo.EntityRelationships er
    INNER JOIN dbo.Entities Parents ON er.EntityIDParent = Parents.EntityID
    INNER JOIN dbo.Entities Children ON er.EntityIDChild = Children.EntityID;

The result:

╔═════════════╦════════════╗
║ EntityName  ║ EntityName ║
╠═════════════╬════════════╣
║ Grandparent ║ Child      ║
╚═════════════╩════════════╝

Note that running DELETE FROM dbo.Entities (with no WHERE clause) will delete all rows from both tables.

To show a slight more complex example; imagine you have 2 grandparents, 2 parents, and single child:

INSERT INTO dbo.Entities (EntityName)
VALUES ('Grandparent 1')
    , ('Grandparent 2')
    , ('Parent 1')
    , ('Parent 2')
    , ('Child');

INSERT INTO dbo.EntityRelationships (EntityIDParent, EntityIDChild)
VALUES (1, 3)
    , (2, 3)
    , (1, 4)
    , (3, 5)
    , (4, 5);

SELECT Parents.EntityName
    , Children.EntityName
FROM dbo.EntityRelationships er
    INNER JOIN dbo.Entities Parents ON er.EntityIDParent = Parents.EntityID
    INNER JOIN dbo.Entities Children ON er.EntityIDChild = Children.EntityID;

╔═══════════════╦════════════╗
║  EntityName   ║ EntityName ║
╠═══════════════╬════════════╣
║ Grandparent 1 ║ Parent 1   ║
║ Grandparent 1 ║ Parent 2   ║
║ Grandparent 2 ║ Parent 1   ║
║ Parent 1      ║ Child      ║
║ Parent 2      ║ Child      ║
╚═══════════════╩════════════╝

If we remove Parent 1 from the Entities table:

DELETE
FROM dbo.Entities
WHERE dbo.Entities.EntityName = 'Parent 1';

we see this:

╔═══════════════╦════════════╗
║  EntityName   ║ EntityName ║
╠═══════════════╬════════════╣
║ Grandparent 1 ║ Parent 2   ║
║ Grandparent 1 ║ Child      ║
║ Grandparent 2 ║ Child      ║
║ Parent 2      ║ Child      ║
╚═══════════════╩════════════╝

This performs a cleanup of our test data:

IF OBJECT_ID(N'dbo.EntityRelationships', N'U') IS NOT NULL
DROP TABLE dbo.EntityRelationships;

IF OBJECT_ID(N'dbo.Entities', N'U') IS NOT NULL
DROP TABLE dbo.Entities;
GO


Mysql daemon failed to start: unknown variable password=xxx

I followed the last suggestion here

cd /usr
./mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --ledir=/usr/libexec &

Now it started, but don't understand it...not very nice!!!


Alter TEXT column to NOT NULL when there is existing NULL rows

Run this query:
UPDATE tablename SET textname = "" WHERE textname IS NULL
after running that you can change the table structure so it does not allow null values anymore.

I recommend checking the code to make sure it doesn't check for null instead of empty strings before changing the way the dB behaves


Below is a literal translation based on your sample query and as such probably isn't the optimal solution:

IF ISNULL(@firstName, N'') = N'' OR ISNULL(@lastName, N'') = N''
    BEGIN
        SELECT  COUNT(*)
        FROM  [Services] s
            INNER JOIN .[Clients] c
                ON c.Client_Id = s.Client_Id
            INNER JOIN .[AdHoc_Referrals] r
                ON r.[Referral_Id] = s.[Referral_Id]
        WHERE r.[ReferralTypeId] = @referralTypeId
            AND s.[Service_Delivery_Type_Id] IN ({0})
            AND s.[Inital_Service_Assignment_Date] IS NOT NULL
            AND (-- This should be 'AND' to filter properly
                c.[Firstname] = @searchString
                OR
                c.[Surname] = @searchString
                OR
                r.[Reference_Number] = @searchString
                )
            AND (GETDATE() >= s.Inital_Service_Assignment_Date
                    AND @visitDate <= DATEADD(dd, 1,
                            COALESCE(s.Actual_Discharge_Date,
                                s.Inital_Service_Assignment_Date))
                )
   END
ELSE
    BEGIN
        SELECT COUNT(*)
        FROM [Services] s
            INNER JOIN .[Clients] c
                ON c.Client_Id = s.Client_Id
            INNER JOIN .[AdHoc_Referrals] r
                ON r.[Referral_Id] = s.[Referral_Id]
        WHERE r.[ReferralTypeId] = @referralTypeId
            AND s.[Service_Delivery_Type_Id] IN ({0})
            AND s.[Inital_Service_Assignment_Date] IS NOT NULL
            AND ( -- Parenthesis not needed, I'm just highlighting the change
                c.[Firstname] LIKE QUOTENAME(RTRIM(@firstName) + N'%')
                AND
                c.[Surname] = @lastName
                )
            AND (GETDATE() >= s.Inital_Service_Assignment_Date
                    AND @visitDate <= DATEADD(dd, 1,
                            COALESCE(s.Actual_Discharge_Date,
                                s.Inital_Service_Assignment_Date))
                )
    END


Maximum Server Memory adjust in blocks of 1024MB?

It's reasonable to assume, although I have no way to know, that the difference between allocating 3000 MB and 3072 MB is just those 72 MB as memory allocation is much more granular than, say, 128 or 256 MB.

The minimum amount of memory allocated to each query is 1024 kB, configurable as low as 512 kB (source). A fair guess is that memory allocation units are anywhere between that of disk I/O operations (64 kB = 1 extent = 8 pages) and 512 kB.

Either way, 72 MB back or forth probably won't make any difference.


How to identify connections using implicit transactions?

Well to answer your specific question, you should be able to see that in the existing connections and audit:login events in SQL Server Profiler/Tracing.

I would actually start by trying to investigate who the blockers are and then dig in, however. You may have better luck in actually tracking down the blockers if they actively block and you see the blocking in action.

You should be able to do that by either querying the dynamic management view - sys.dm_os_waiting_tasks (described here) or installing and using Adam Machanic's sp_whoisactive (a procedure I recommend you install anyway)


Trigger with IS DISTINCT FROM on rows with json/jsonb without comparison itemizing?

Immediate problem: type json
There are no equality or inequality operators defined for the Postgres data type json. See:

How to query a json column for empty objects?
How to remove known elements from a JSON[] array in PostgreSQL?

Hence row comparisons involving a json column are also bound to fail - with notable exceptions, see below.
There are (imperfect!) workarounds, but don't bother and switch to jsonb, which allows these checks.
Fix function & trigger
You also cannot compare OLD and NEW for INSERT operations, where there is no OLD - only for UPDATE or DELETE. I suggest a column default for modified_at, or a separate trigger function and trigger BEFORE INSERT to overwrite any input with now() unconditionally.
And this function & trigger for UPDATE:
CREATE OR REPLACE FUNCTION on_update_modified()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW IS DISTINCT FROM OLD THEN
      NEW.modified_at = now();
      RETURN NEW;
   ELSE
      RETURN NULL;  -- skip empty update, see below
   END IF;
END
$func$;

CREATE TRIGGER mytable_update  -- doesn't work!
BEFORE UPDATE ON tbl_js
FOR EACH ROW EXECUTE PROCEDURE on_update_modified();

db<>fiddle here (Postgres 9.6)
Nothing different in Postgres 13: db<>fiddle here
RETURN NULL; is optional. If the row has not changed at all (and while running the trigger function already anyway) you might as well skip the UPDATE for the row if it does not change it. That save the considerable cost of writing a new row version in vain. If other triggers depend on it, or for other exotic edge cases, you may still want to go through with it. Then RETURN NEW; - unconditionally at the end of the trigger function. RETURN OLD; would have no benefit after we have established both row values are identical.
Row comparison with json column can still work?
Oddly, yes. Row comparison is processed left-to-right. For the expression NEW IS DISTINCT FROM OLD Postgres can return true immediately on finding the first mismatch (before encountering the problem with json). This may or may not happen, depending on the code path. Quoting the manual on row-wise comparison :

Note
Errors related to the number or types of elements might not occur if the comparison is resolved using earlier columns.


Is it good sign? If SQL Server Buffer and Procedure cache hit ratio always showing 100%

If 100% Buffer and Procedure cache hit ratio is considered not good in general?

You should not ONLY rely on buffer cache hit ratio it does not accurately measures or give signs of memory pressure the reason has been shown by Jonathan Kehayias in this Blog. The reason is concept called as Read Ahead in SQl Server.

Due to read aheads more than required pages are brought into memory every time a single scatter gather read happens and this mostly keeps buffer pool populated with pages giving indication that their is no memory pressure due to churning out of pages. If you read the blog you would find other important counters to look for memory pressure.

  The read-ahead mechanism allows the Database Engine to read up to 64 contiguous pages (512KB) from one file. The read is performed as a single scatter-gather read to the appropriate number of (probably non-contiguous) buffers in the buffer cache. If any of the pages in the range are already present in the buffer cache, the corresponding page from the read will be discarded when the read completes. The range of pages may also be "trimmed" from either end if the corresponding pages are already present in the cache.

One cannot accurately say SQL Server has sufficient memory or not by just looking at BCHR, there are other counters to be taken into account


how to create double entry book keeping for member purchases, fees, charges

Double entry accounting is a specialist skill by itself. To do this properly you'll likely need the assistance of someone trained in accountancy.

The basic principal is that every bit of money is associated with an account. Choosing what accounts and how they relate to each other is where the expertise lies. Money only ever moves between accounts via matching transactions - one to debit the source and the other to credit the destination.


Deadlock while updating rows and inserting new ones

I bet your code does something like this logic:

IF EXISTS (SELECT 1 FROM dbo.table WHERE key = @key)
BEGIN
  UPDATE dbo.table SET foo = @bar WHERE key = @key;
END
ELSE
BEGIN
  INSERT dbo.table(key, foo) VALUES(@key, @bar);
END

If so, just do this:

UPDATE dbo.table SET foo = @bar WHERE key = @key;
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.table(key, foo) VALUES(@key, @bar);
END

That meaningless first SELECT is just a wasted seek (or scan if you are not identifying row(s) by something that can seek), and can often be the cause of deadlocks when you have many users simultaneously running this same piece of code.


You can create a sql job with first step to check for the runnnnig job using below code:

      SELECT
    job.Name, job.job_ID
   ,job.Originating_Server
   ,activity.run_requested_Date
   ,datediff(minute, activity.run_requested_Date, getdate()) AS Elapsed
    FROM
    msdb.dbo.sysjobs_view job
    INNER JOIN msdb.dbo.sysjobactivity activity
    ON (job.job_id = activity.job_id)
   WHERE
 run_Requested_date is not null
 AND stop_execution_date is null
 AND job.name like 'Your Job Prefix%'

Then you can create two more steps for success and failure notifications using sp_send_dbmail feature. Make sure Advanced options on step 1 is configured properly so that on completion of step 1 it would go to say step 2 which sends you the success notification else on failure step 3 for failure notification.

However, you can use a better way of doing this by using sql reporting services.

Use the codes from below and test to check the status and see if that works for you!

http://www.codeproject.com/Articles/431307/View-SQL-Server-Job-Status-using-SQL-Reporting-Ser


select rows with min values of multiple columns

I got the following to work...

SELECT
    r0.category,
    r0.name,
    r1.s1time AS "s1",
    r2.s2time AS "s2",
    r3.s3time AS "s3",
    r4.s4time AS "s4",
    r5.s5time AS "s5"
FROM
    (SELECT
        name, riderid, category
     FROM
         raceresults
     ) r0
    LEFT JOIN raceresults r1
        ON r1.riderid = r0.riderid AND r1.s1time=(SELECT min(s1time) FROM raceresults WHERE s1time>0 AND category=r1.category)
    LEFT JOIN raceresults r2
        ON r2.riderid = r0.riderid AND r2.s2time=(SELECT min(s2time) FROM raceresults WHERE s2time>0 AND category=r2.category)
    LEFT JOIN raceresults r3
        ON r3.riderid = r0.riderid AND r3.s3time=(SELECT min(s3time) FROM raceresults WHERE s3time>0 AND category=r3.category)
    LEFT JOIN raceresults r4
        ON r4.riderid = r0.riderid AND r4.s4time=(SELECT min(s4time) FROM raceresults WHERE s4time>0 AND category=r4.category)
    LEFT JOIN raceresults r5
        ON r5.riderid = r0.riderid AND r5.s5time=(SELECT min(s5time) FROM raceresults WHERE s5time>0 AND category=r5.category)

Is there a better way?


Postgres: Insert result of join as an array?

You need to use a single insert statement:

INSERT INTO joined (id, name, regs)
SELECT u.id, u.name, array_agg(reg_id)
FROM users u
  JOIN regs r ON r.user_id = u.id
GROUP BY u.id, u.name;

Your two step approach requires an update as the second step, not an insert:

INSERT INTO joined (id, name)
SELECT id, name
FROM users;

update joined
   set regs = t.regs
from (
  select user_id, array_agg(reg_id) as regs
  from regs
  group by user_id
) t
where t.user_id = joined.id;


The row:

Sandy         2452356   24523

is preferred because it has the largest o_id. You can pick the largest o_id per salesperson via:

select max(o_id), salesperson
from T
group by salesperson

An important property of SQL is that it is closed, i.e. a result of a query is a relation (table). So we can simply join this with the original table:

select x.o_id, x.Salesperson, x.fax, x.Pincode
from T as x
join (
    select max(o_id) as o_id, salesperson
    from T
    group by salesperson
) as y
    on x.o_id = y.o_id
   and x.salesperson = y.salesperson

Even if question does not say so explicitly, there is reason to believe that salesperson is functionally dependent of o_id. If that is the case it is sufficient to join by o_id alone:

select x.o_id, x.Salesperson, x.fax, x.Pincode
from T as x
join (
    select max(o_id) as o_id, salesperson
    from T
    group by salesperson
) as y
    on x.o_id = y.o_id

In SQL2003, window functions were introduced. They are a perfect fit for this kind of query. I have heard the next version of MySQL will support them, but I guess you are not using that.

You also mention this construction:

select salesperson,faxno,pincode
from OPPORTUNITY
Group By salesperson

It is probably the construction in MySQL that causes the most confusion. It is invalid in most (all?) other products, because the result is non-deterministic or random. What happens is that MySQL randomly picks a faxno, pincode for each salesperson.


I could easily suggest changing InnoDB settings which might be a littel heavy-handed just to get a mysqldump to work. You may not like what I am about the suggest, but I believe it's your best (only) option. Here it goes:
SUGGESTION #1 : Disable extended inserts
The default setting for mysqldump would include clumping together hundreds or thousands of rows in a single INSERT. This is known as an extended INSERT. It is causing some overrun beyond just max_allowed_packet.
I answered a post back on Sep 01, 2011 (MySQL server has gone away obstructing import of large dumps) where I discussed doing the same thing for importing a large mysqldump. I believe disabling extended INSERT would help with creating a troublesome mysqldump as well.
mysqldump -u root --skip-extended-insert -p maia > maia.sql

Bad news: What this does in create an INSERT command for each row. This will definitely increase the time it takes to perform the mysqldump. Consequently, it will also increase to time is take to reload (probably by a factor of 10-100.
I have discussed skip-extended-insert before

Aug 12, 2011 : Why is mysqldump file so large?
Aug 09, 2013 : Backup / Export data from MySQL 5.5 attachments table keeps failing!
Nov 16, 2014 : How to batch multiple insert statements for database dump with MySQL?

SUGGESTION #2 : Dump binary data as hex (OPTIONAL)
To make the mysqldump's binary data more byte portable, dump such data in hexadecimal
mysqldump -u root --skip-extended-insert --hex-blob -p maia > maia.sql

Bad News: It will bloat the mysqldump a little more
GIVE IT A TRY !!!
Side Note : The maximum size of max_allowed_packet is 1G


sp_blitzFirst Does Not Output to DB When Run as SQL Job

Moving the answer from the comments:

When this happens, start by double-checking the contents of your Agent job, and make sure it exactly matches the command you're running manually.

In this case, the text of the Agent job was calling for a different database name - one that didn't exist - so it was skipping the logging portion. Right now, if you call it with a database name that doesn't exist, it doesn't fail hard. It probably should - if you want to file a Github issue for that at http://FirstResponderKit.org, I can add that.


Sum Interval of Dates within same column

You can use the LEAD analytic function to obtain the next row's eventtype and eventdate alongside the current row's data:

SELECT
  eventtype,
  eventdate,
  LEAD(eventtype) OVER (ORDER BY eventdate) AS nexttype,
  LEAD(eventdate) OVER (ORDER BY eventdate) AS nextdate
FROM
  atable
WHERE
      eventdate >= '2015-01-01 00:00:00.00'
  AND eventdate <  '2016-01-01 23:59:59.59'

Using the above query as a derived table, you can filter the output further on eventtype = 'START' AND nexttype = 'END' and get the difference total:

SELECT
  SUM(EXTRACT(EPOCH FROM (nextdate - eventdate))) AS duration_seconds
FROM
  (
    SELECT
      eventtype,
      eventdate,
      LEAD(eventtype) OVER (ORDER BY eventdate) AS nexttype,
      LEAD(eventdate) OVER (ORDER BY eventdate) AS nextdate
    FROM
      atable
    WHERE
          eventdate >= '2015-01-01 00:00:00.00'
      AND eventdate <  '2016-01-01 23:59:59.59'
  ) AS s
WHERE
      eventtype = 'START'
  AND nexttype  = 'END'
;

As a slight variation, you can implement the subquery as a CTE:

WITH cte AS
  (
    SELECT
      eventtype,
      eventdate,
      LEAD(eventtype) OVER (ORDER BY eventdate) AS nexttype,
      LEAD(eventdate) OVER (ORDER BY eventdate) AS nextdate
    FROM
      atable
    WHERE
          eventdate >= '2015-01-01 00:00:00.00'
      AND eventdate <  '2016-01-01 23:59:59.59'
  )
SELECT
  SUM(EXTRACT(EPOCH FROM (nextdate - eventdate))) AS duration_seconds
FROM
  cte
WHERE
      eventtype = 'START'
  AND nexttype  = 'END'
;

This rewrite can have implications for performance, because unlike a derived table, a CTE is materialised in PostgreSQL. Testing should reveal if there is a difference and, if so, which option is better for you.


Using a join operation with conditionals

You can make use of the FOR XML PATH() construct to create the list of features.

For instance:

USE tempdb;

IF OBJECT_ID('dbo.Features') IS NOT NULL
DROP TABLE dbo.Features;
IF OBJECT_ID('dbo.Versions') IS NOT NULL
DROP TABLE dbo.Versions;
GO
CREATE TABLE dbo.Versions
(
    VersionID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , VersionNumber DECIMAL(10,2) NOT NULL
    , StartDate DATETIME NOT NULL
    , EndDate DATETIME NULL
    , ServerID INT NOT NULL
);
CREATE TABLE dbo.Features
(
    FeatureID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , FeatureInfo VARCHAR(250) NOT NULL
    , VersionID INT NOT NULL
        FOREIGN KEY REFERENCES dbo.Versions(VersionID)
);

INSERT INTO dbo.Versions(VersionNumber, StartDate, EndDate, ServerID)
VALUES (1.0,'2016-03-22T00:00:00', '2016-03-23T00:00:00', 1001)
    , (2.0,'2016-03-23T00:00:00', '2016-03-24T00:00:00', 1001)
    , (2.1,'2016-03-24T00:00:00', NULL, 1001);

INSERT INTO dbo.Features (FeatureInfo, VersionID)
VALUES ('blah', 1)
    , ('blee', 1)
    , ('bloh', 2);

;WITH Deets
AS
(
    SELECT VersionNumber = CONVERT(VARCHAR(10), v.VersionNumber, 0)
        , Features = STUFF((SELECT TOP(2147483647) ', ' + f.FeatureInfo
        FROM dbo.Features f
        WHERE f.VersionID = v.VersionID
        ORDER BY f.FeatureID
        FOR XML PATH ('')
        ), 1,2,'')
    FROM dbo.Versions v
)
SELECT 'Version ' + d.VersionNumber + ' has ' +
    CASE WHEN d.Features IS NULL THEN 'no features'
        ELSE d.Features
    END
FROM Deets d;

This returns a nice list, like:

Notice several things from my code above:

I always specify the schema
I use upper case for the SQL keywords
I use Camel Case for column names
The values I specified for the date columns are in ISO format, which makes it easier for SQL Server to understand, and prevents errors.


Probably Timestamp is very selective, possible even unique. As such there is no point in adding other fields to the key, they will only increase the size of the key without contributing to the selectivity. Having them as INCLUDED columns instead allows them to be added only to the leaf pages, saving on the overall size of the index.

select top(1000) count(*) as cnt, Timestamp
from ...
group by Timestamp
order by cnt desc;

What does the above return?


Granting select on syscat.schemata - DB2

Select privileges on all SYSCAT objects are granted by default to the PUBLIC pseudo-group (unless the database is created as "restrictive"), so you shouldn't have ended up with users not being able to access syscat.schemata unless you or someone before you chose to actively prevent that.

What could go "wrong"?

Users will be able to discover what schemas exist in your database. Obviously this doesn't give them any extra privileges to see or access in any way objects in those schemas. In other words, being able to read syscat.schemata is pretty harmless.