Two databases of same size, backups sizes are very different This is not a big problem for me, but still, I would like to understand what is happening here. I have two databases on my SQL Server 2008 (10.00.1600) - they are restored from the same backup (and after that some optimization work was done by our C# program - the process was the same for both DBs, + some small amount inserts/deletes was executed afterwards).

But when I take a backup of those databases the output file size differs:

Database1:    706 MB
Database2:   1690 MB

The query used to take both backups (script generated by SSMS):

BACKUP DATABASE [DatabaseX] TO DISK = N'D:\databaseX.bak'
    WITH NOFORMAT, NOINIT, NAME = N'DatabaseX-Full', SKIP, NOREWIND,
    NOUNLOAD, NO_COMPRESSION,  STATS = 10

Here are file stats of databases:

Database1:
    Data allocated:       1950 MB
    Logs allocated:       5121 MB
    Output backup size:    706 MB
Database2:
    Data allocated:       1704 MB
    Logs allocated:         50 MB (this was truncated in the process of my investigation :))
    Output backup size:   1690 MB (NB! this is not true anymore, see update 3 below)

As you can see database 2 has 200 MB less data and still produces a much bigger backup file! Can anyone tell me why is this happening? Please do tell if some additional info is needed.

UPDATE 1

I got the data/log allocated sizes by executing the following query:

SELECT  d.name,
        mf.physical_name,
        round(mf.size * 8 / 1024, 0) Size_MBs
FROM    sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE   d.database_id > 4 -- Skip system databases
ORDER BY d.name

Ldf and mdf sizes were there (each DB has only two files - one mdf and one ldf). And if it matters both databases' recovery model is FULL.

UPDATE 2

Just to clarify - the operation after restoring databases involves all kind of stuff (adding/removing table fields, rebuilding indexes, adding/removing FKs and inserts/deletes). The final structure (tables, indexes and fks) are the same. The amount of data differs slightly.

UPDATE 3

Here the results of the query posted by gbn:

            Database 2          Database 1
            Reserv  Used        Reserv  Used
------------------------------------------------------
table1      475,88  230,98      240,94  240,51
table2      229,73  230,27      188,74  188,44
table3      162,78  163,18      69,06   68,35
table4      69,12   69,06       68,87   68,61
table5      68,75   68,50       66,83   66,37
table6      67,69   66,93       21,08   20,55
table7      19,02   11,05       15,28   14,88
table8      17,81   17,52       4,98    4,55
table9      8,06    3,91        2,52    2,30
table10     4,23    3,90        1,13    1,09
table11     3,40    3,09        0,97    0,93
table12     1,44    0,54        0,90    0,67
table13     1,20    1,04        0,77    0,59
table14     0,98    0,94        0,64    0,52
table15     0,65    0,63        0,39    0,29
table16     0,58    0,58        0,11    0,06
table17     0,12    0,06        0,08    0,08
table18     0,08    0,08        0,05    0,05
table19     0,06    0,06        0,05    0,05
table20     0,06    0,06        0,05    0,05
table21     0,05    0,05        0,05    0,05
table22     0,05    0,05        0,03    0,03
table23     0,05    0,05        0,03    0,03
table24     0,03    0,03        0,03    0,03
table25     0,03    0,03        0,03    0,03
table26     0,03    0,03        0,03    0,03
table27     0,03    0,03        0,03    0,03
table28     0,03    0,03        0,02    0,02
table29     0,02    0,02        0,00    0,00
------------------------------------------------------
TOTAL       1131,95 872,71      683,69  679,16

UPDATE 4

After rebuilding all indexes on Database2 its output backup file size reduced to 1165 MB.

As noted in comments, the tables do not have clustered indexes therefore you can not logically defragment them (rebuild indexes) completely. That is, the indexes are defragged but not the actual data.

Also, DELETEs from heaps (a table without a clustered index) do not deallocate space. See http://msdn.microsoft.com/en-us/library/ms189245(v=sql.105).aspx for more

Finally, not having clustered indexes is generally a bad thing except for staging tables that are truncated. However, GUIDs are not good clustering keys

Solutions?

Add clustered indexes for a long term fix (subject to regular index maintenance)
Run DBCC SHRINKFILE with NOTRUNCATE to compact data. YMMV of course


Calculate size of the filtered rows of a table in sql server My requirement is to know the size occupied(count of records and size in mb) by a table during every 24 hours, I found query to get the whole size of a table but not sure on where to start with or how to achieve this.

I have an audit column(record_inserted_time) by which I can filter the records and get the count but how to know the size occupied during this period by the table(size of the filtered records).

I have to get this stats for around 100 tables all of which has the same audit column

You are not going to get accurate figures for space used back in time because of differences in how efficiently things are laid out in the pages as your data changes over time.

Your "inserted date" column will give you an estimate of the number of rows present, by taking the current row count and subtracting the count of rows inserted since the date you are measuring for (or just counting those inserted on/before that date). It may give an accurate reading rather than an estimate if:

the table is never deleted from
the audit date is only updated on insert not also on update
any and all updates are performed with UPDATE or MERGE and not DELETE-then-reINSERT.

You can use the row-count-at-a-date-in-the-past to estimate the total size of the pages it occupied by taking calculating (data_size_now * rows_then) / rows_now.

That is for trying to look back in time when you haven't kept logs. Maintaining accurate records going forward is easy: setup a job that runs the analysis for each table and have it store the results for later review.


Restoring postgresql 9.4 crashed database Can anyone help me to restore my crashed database. Is there a way to use pg_resetxlog. I read a number of documentations telling about it but i'm always seeing the same log when i restart my postgresql service

2016-04-11 8:47:59 p.m. GMT [1710-1] LOG: the database system was interrupted during a restore at 2016-04-11 11:04:19 GMT
2016-04-11 8:47:59 p.m. GMT [1710-2] TIP: This probably means that the data has been corrupted and you
need to use the last backup for recovery.
2016-04-11 8:47:59 p.m. GMT [1710-3] LOG: the database system was not shut properly; restoration
automatic current
2016-04-11 8:47:59 p.m. GMT [1710-4] LOG: the re-execution starts at 4 / 32DF8E90
2016-04-11 8:47:59 p.m. GMT [1710-5] FATAL: could not access the transaction status 5598
2016-04-11 8:47:59 p.m. GMT [1710-6] DETAIL: Could not read the file "pg_multixact / members / 0000" at offset 57344: Success
2016-04-11 8:47:59 p.m. GMT [1710-7] BACKGROUND: xlog redo create mxiD 5598 offset 11603 Nmembers 2: 6486476 (keysh) 6,486,482 (keysh)
2016-04-11 8:47:59 p.m. GMT [1709-1] LOG: launch process (PID 1710) exits with exit code 1
2016-04-11 8:47:59 p.m. GMT [1709-2] LOG: start cancellation due to a failure in the launch process

I tried to fix my problem like this :

First one, saving my data in a external drive

  scp -r /var/lib/postgresql/9.4/main /media/myhdd/data

Second One, as postgres user i run the pg_resetxlog command like this :

./pg_resetxlog -f /var/lib/postgresql/9.4/main/

The command result after pg_resetxlog is

  Resetting the transaction log

But when i try to restart my postgresql service i get the same error message. Am i doing the right command whith pg_resetxlog ? I'm new on postgres

I finally solve my problem.
I recreate pg_multiaxt/members/0000 file by giving more offset. My first offset was 55344 and the new one is 100192.
The command is :

dd if=/dev/zero of=0000 bs=1 count=100192

After restarting my postgresql service my database is now working correctly.


Why can't I update or delete certain records in SQL Server? I am having a weird issue in SQL Server 2014. I cannot update or delete certain rows from a table.

I can insert rows fine, and select them, but when I try to update or delete a row, I get a timeout error. It is also strange, that some of the rows can be updated, while others cannot. The table is small, only a few thousand rows, and it's hard to see a pattern with which rows can and cannot be updated, but it seems the more recently inserted rows cannot be deleted/updated.

There are no triggers on the table, there are no locks on the database, and the table has a primary key.

What could be causing this?

Few Quick Possibilities:

Bad Statistics. Seems unlikely on a table under millions of rows, but technically possible. Try running UPDATE STATISTICS . If this does work look into why your statistics are getting out of whack and what you can do to mitigate the problem.
Lack of a good Clustered Index. This can be related to #1, if your CI is bad it can slow down UPDATEs/DELETEs as the CI needs to be searched and adjusted during such changes, but an INSERT may just be adding to the end of the CI.
Locks are being held too long over some sections of the table via other processes. Other transactions may be holding a lock on the table for excessive amounts of time, this may also be visible as deadlocking if you have the appropriate flags turned on for the SQL Logs.

Manually grabbing the query plan and taking a look at that may also help you figure out what is going on.


Compare two MySQL dump files I am trying to compare two MySQL dump files. One was made from a fresh install of a web application and the other is a dump from the same web application that has been upgraded from a previous version to the same version number as the fresh install.

Basically I want to make sure that the upgrade scripts worked properly and all the proper columns and constraints are in the upgraded version just as they are in fresh install. I thought a simple diff/sdiff would be good enough for this but I ran into the problem with that: Column order.

The column order between the fresh install and the upgrade are different giving many false positive "differences". It is easy to see when looking at the output that the same column is simply in a different spot but with hundreds of tables it would take a long time to check and identify these false positives.

Is there a better way to compare these two dumps that will ignore column order (or a way to force the dumps to have columns in alphabetical order or something similar) or an entirely different approach that would work better. I am admittedly fairly new to MySQL and am just learning as I go so any help would be much appreciated.

Thanks.

Unfortunately, you cannot force the ordering of columns in a mysqldump. You can, however, use the table INFORMATION_SCHEMA.COLUMNS. You will need the following:

MySQL Instance with the old data loaded
MySQL Instance with the new data loaded
Some Patience and Scripting

With this setup in mind, here is what you need to do:

STEP 01) Use Query to Present Columns Ordered Per Database/Table

Here is the Query:

SELECT CONCAT(table_schema,'.',table_name),
GROUP_CONCAT(column_name ORDER BY column_name)
FROM information_schema.columns WHERE table_schema NOT IN
('information_schema','performance_schema')
GROUP BY table_schema,table_name;

This will give you every table followed by a CSV list of alphabetically-sorted columns

STEP 02) Launch this Column Query From Both MySQL Instances

IPADDR_OLD=10.1.20.30
IPADDR_NEW=10.1.20.40
SQLSTMT="SELECT CONCAT(table_schema,'.',table_name),"
SQLSTMT="${SQLSTMT} GROUP_CONCAT(column_name ORDER BY column_name)"
SQLSTMT="${SQLSTMT} FROM information_schema.columns WHERE table_schema NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','performance_schema')"
SQLSTMT="${SQLSTMT} GROUP BY table_schema,table_name"
mysql -u... -p... -h${IPADDR_OLD} -ANe"${SQLSTMT}"  > column_listing1.txt
mysql -u... -p... -h${IPADDR_NEW} -ANe"${SQLSTMT}" >> column_listing2.txt
cat column_listing1.txt  > column_listing.txt
cat column_listing2.txt >> column_listing.txt

Run the Column Query and Collect the Results in column_listing.txt

STEP 03) Perform Unique Sort ; Check For Duplicate Table Names

sort -u < column_listing.txt > column_listing.unq
UNQ_LINES=`wc -l < column_listing.unq`
TXT_LINES=`wc -l < column_listing.txt`
(( DIF_LINES = (UNQ_LINES + UNQ_LINES - TXT_LINES) / 2))
echo ${DIF_LINES}

If everything is identical, DIF_LINES should 0. Otherwise, there are duplicates present.

STEP 04) If There Are Duplicates, Find Them

diff column_listing1.txt column_listing2.txt

Give it a Try !!!

UPDATE 2013-03-22 13:04 EDT

You can also check other metadata tables such as

TABLE_CONSTRAINTS

mysql> desc TABLE_CONSTRAINTS;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME    | varchar(64)  | NO   |     |         |       |
| TABLE_SCHEMA       | varchar(64)  | NO   |     |         |       |
| TABLE_NAME         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_TYPE    | varchar(64)  | NO   |     |         |       |
+--------------------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

STATISTICS

mysql> desc STATISTICS;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512)  | NO   |     |         |       |
| TABLE_SCHEMA  | varchar(64)   | NO   |     |         |       |
| TABLE_NAME    | varchar(64)   | NO   |     |         |       |
| NON_UNIQUE    | bigint(1)     | NO   |     | 0       |       |
| INDEX_SCHEMA  | varchar(64)   | NO   |     |         |       |
| INDEX_NAME    | varchar(64)   | NO   |     |         |       |
| SEQ_IN_INDEX  | bigint(2)     | NO   |     | 0       |       |
| COLUMN_NAME   | varchar(64)   | NO   |     |         |       |
| COLLATION     | varchar(1)    | YES  |     | NULL    |       |
| CARDINALITY   | bigint(21)    | YES  |     | NULL    |       |
| SUB_PART      | bigint(3)     | YES  |     | NULL    |       |
| PACKED        | varchar(10)   | YES  |     | NULL    |       |
| NULLABLE      | varchar(3)    | NO   |     |         |       |
| INDEX_TYPE    | varchar(16)   | NO   |     |         |       |
| COMMENT       | varchar(16)   | YES  |     | NULL    |       |
| INDEX_COMMENT | varchar(1024) | NO   |     |         |       |
+---------------+---------------+------+-----+---------+-------+
16 rows in set (0.01 sec)

REFERENTIAL_CONSTRAINTS

mysql> desc REFERENTIAL_CONSTRAINTS;
+---------------------------+--------------+------+-----+---------+-------+
| Field                     | Type         | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG        | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME           | varchar(64)  | NO   |     |         |       |
| UNIQUE_CONSTRAINT_CATALOG | varchar(512) | NO   |     |         |       |
| UNIQUE_CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| UNIQUE_CONSTRAINT_NAME    | varchar(64)  | YES  |     | NULL    |       |
| MATCH_OPTION              | varchar(64)  | NO   |     |         |       |
| UPDATE_RULE               | varchar(64)  | NO   |     |         |       |
| DELETE_RULE               | varchar(64)  | NO   |     |         |       |
| TABLE_NAME                | varchar(64)  | NO   |     |         |       |
| REFERENCED_TABLE_NAME     | varchar(64)  | NO   |     |         |       |
+---------------------------+--------------+------+-----+---------+-------+
11 rows in set (0.01 sec)

mysql>

Setup queries that perform the same styled comparison


Make a temporary table visible for a function inside a function (or a subfunction) I have two functions in PL/pgSQL:

The function 1 calls function 2, and function 2 needs to process a temporary table created on function 1, in another words, this temporary table needs to be global in the function 1 context (that have function 2 inside it).

FUNCTION1
   | CREATE TEMPORARY TABLE
   | DO SOME SPECIFIC STUFF...
   | FUNCTION2
         | DO SOME GENERIC STUFF
         | INSERT SOME DATA INTO THE TEMPORARY TABLE
         | (Uhh!! Ohh! it hurts, table not visible)
   | RAISED ON ERROR

There is a way to pass the temporary table created on function 1 to this subfunction?

I need it because i am working with a very big function that process a lot of data in a temporary table, before inserting all the big data, but when i try to turn into a more modular form, the subfunctions just can't see the temporary table).

It is working by now (in a big one function), but i am doing CTRL+C CTRL+V to create new similar functions, as a lot of codes can be repeated inside it. If i can use subfunctions this problem will go out, as i can use this code multiple times.

You can pass the name of the table:

create or replace function f2(p_table_name text) returns text
  language plpgsql
as
$body$
begin
  execute $$insert into $$||quote_ident(p_table_name)||$$ (c1) values ('text1');$$;
  return 'ok';
end;
$body$;

create or replace function f1() returns text
  language plpgsql
as
$body$
declare
  v1 text;
begin
  create temporary table t1 (
    c1 text
  );
  perform f2('t1');
  select c1 into v1 from t1 limit 1;
  return v1;
end;
$body$;

select f1();

See it working at: http://rextester.com/FYMX28296


Can't install mongodb-enterprise. Error: nothing provides libsasl2.so.2()(64bit) I am trying to install mongodb-enterprise on fedora 25, as guided here. I created the repository (as suggested in step 1), but when I run the command sudo yum install -y mongodb-enterprise

I get this error:

  Error: nothing provides libsasl2.so.2()(64bit) needed by
  mongodb-enterprise-tools-3.4.0-1.amzn1.x86_64

and when I try to install cyrus-sasl-lib it says Dependencies resolved.
Nothing to do. , I found the command from here, which provides the library libsasl2.so.2. It seems I am stuck.

PS: I've already tried installing from a Tarball but that also doesn't seem to be working for me. And I have to use mongo-shell version 3.4 only.

As i saw in your statement , you are going to install MongoDB Enterprise in Fedora 25. As you said already run

sudo yum install -y mongodb-enterprise

First i would like to say about Red Hat Enterprise Linux (RHEL) & Fedora.

Red Hat Enterprise Linux (RHEL) is a product of Red Hat and is purchased from Red Hat and includes a support contact with Red Hat that you renew every year.

Fedora was created by Red Hat as a community-based open source project that would always have the latest versions as well as trying out new ideas. Items that do well in Fedora can make it into RHEL at a later time.

I am sure about that you are doing mistake here. Even as per MongoDB Fedora documentation in place of

sudo yum install -y mongodb-enterprise // for Red Hat installation

you have to run Here

dnf install mongodb-org mongodb-org-server // for Fedora installation

what is dnf?

  DNF by default uses the global configuration file at /etc/dnf/dnf.conf
  and all *.repo files found under /etc/yum.repos.d. The latter is
  typically used for repository configuration and takes precedence over
  global configuration.

The steps to install MongoDB on Fedora is pretty simple.

First, login to the server as root user.

Then, we should add the repository to download the package. Open a new repo file mongodb.repo using below command.

For 64-bit system

[mongodb]

name=MongoDB Repository

baseurl=http://downloads-distro.mongodb.org/repo/redhat/os/x86_64/

gpgcheck=0

enabled=1

Then exit and save the file with the command :wq

Then installing MongoDB is now one command away

dnf install mongodb-org mongodb-org-server

When rompted with Y / n, enter yes or y to download and install the package.

Once installed Start-Up MongoDB

[root@fedora]#systemctl start mongod

For further your ref Here and Here


How do I create a psql alias for "\q"? I'm using Postgres 9.5 on Ubuntu 14.04.  Even after using Postgres for a year, I can't get used to typing \q to exit my command line session.  My fingers just want to type "exit" or "quit".  How can I create an alias that does the same thing as \q in my Postgres command line session?

Don't use \q. Use CTRL + D. This sends End-of-file (EOF). It works on all interactive terminals (at least the ones built with readline, afaik)

  readline returns the text of the line read. A blank line returns the empty string. If EOF is encountered while reading a line, and the line is empty, NULL is returned. If an EOF is read with a non-empty line, it is treated as a newline.

Most of the interactive terminals and REPL utilities you use, use some variation of readline or libedit. They all run in a loop that process lines. When you type CTRL+D your terminal inserts the EOF signal, the loops get a null pointer, and the program reads that by convention as a desire to quit.

You can't make exit nor quit an alias in psql, because psql doesn't have aliases like bash; but, both of your other utilities that use exit and quit should work with CTRL+D. It's a good muscle memory to develop.

Two minor footnotes...

If you're on DOS or the newer graphical version, use CTRL+Z to send EOF. That's also the MS Word hotkey for Undo.
I'm not actually describing what happens in UNIX either. I'm keeping it simple. CTRL+D really sends End-of-Transmission (EOT) which causes the terminal to send EOF.


Dangerous MySQL functions It is well-known that the SQL function SLEEP() can be used to attack web applications, e.g. as described here. The sleep function cannot be disabled since it is compiled into the MySQL code and does not require any special privilege.

My question is: does MySQL include any functions except SLEEP() which have all of the following properties?

The function is not commonly required by web applications. Say, 95% of CRUD apps don't need to use the function.
The function is not provided by an external plugin, but is included in the core MySQL codebase.
The function does not require any special privileges to be executed.
The function provides some non-zero benefit to attackers in a SQL injection.

My motivation for the question: I am interested in techniques to prevent SQL injections by limiting MySQL functionality. I am curious if SLEEP()-injections are the only candidate for this technique or if there are other attacks that could be prevented.

You are looking in a bad way. Those security limits can get back to bite you.

The right solution is to sanitize user input etc. to prevent injection to happen.

SQL Injection isn't an error on the database side instead it is on the application side.

If you try to protect only db somebody will attack the app server and once there he can do full dump.


Powershell Missing Cluster Name for FCI I'm running the following simple Powershell command.

$Server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') 'SerName'
$Server | SELECT *

For FCI clusters the ClusterName value is always blank and the ClusterQuorumState and ClusterQuorumType are always NotApplicable.  IsClustered is set to True though.

If I run it against an Availability Group I get back the cluster name and the correct values for the other two properties.

Is there a way in Powershell to get the cluster name for the FCI instances?  Another cmdlet I can use?

No need to load up the Smo object, you can query WMI to get this

gwmi -Query "select Name from cim_cluster" -namespace root\mscluster -computername [computername] | select Name


how to select top ID from join operation and staging tables I have this inner join from staging and operations table jobsCode  I want to select the top surrogate key ID and get the Start_Date from this join my problem is my Query rerunning 2 records
original query

SELECT a.JobCode
    ,b.ID
    ,b.Start_Date
    ,a.EndDate
    ,b.Start_Date
    ,b.End_Date
FROM [RPStage].[dbo].[GLBJobCode] a
    INNER JOIN [operations].[dbo].[Job_Code] b ON  a.JobCode =  b.Job_Code
        AND a.EndDate = b.End_Date
        AND a.EndDate = '9998-12-31'
        AND a.StartDate != b.Start_Date;

trying to get it using max function

SELECT a.JobCode
    ,MAX(b.ID) ID
    ,MAX(B.Start_Date) Start_Date
    ,a.EndDate
    ,b.Start_Date
    ,b.End_Date
FROM [Stage].[dbo].[JobCode] a
    INNER JOIN [operations].[dbo].[Job_Code] b ON  a.JobCode =  b.Job_Code
        AND a.EndDate = b.End_Date
        AND a.EndDate = '9998-12-31'
        AND a.StartDate != b.Start_Date
GROUP BY a.JobCode
    ,b.ID
    ,a.EndDate
    ,b.Start_Date
    ,b.End_Date;

Window Functions, or the OVER() Clause

It appears that what you are looking for is a window function or an OVER() clause.

In your original example, you are trying to use two max() conditions, which doesn't work, because when you try to then apply GROUP BY, you can have a condition where the max of the first column ID and the second column startdate aren't in the same row, and so then GROUP BY simply can't be understood.

However, if you are looking to extract the max over a grouping, and you can define that grouping, and you want to obtain the 'column-wise' maximum for more than one column for that grouping (as you seem to want to do), then here's the solution.

CREATE TABLE test (
  jobcode integer,
  id integer,
  a_startdate datetime,
  a_enddate datetime,
  b_startdate datetime,
  b_enddate datetime);

  INSERT INTO test VALUES (513801, 7136, '11-01-2011', '12-31-9998', '11-01-2011', '12-31-9998');
  INSERT INTO test VALUES (513801, 7137,'04-26-2014', '12-31-9998','04-26-2014', '12-31-9998');

I first made this table to recreate your input data in the picture.  I then applied this query.

SELECT jobcode, max(id) OVER (PARTITION BY jobcode),
max(a_startdate) OVER(PARTITION BY jobcode),
a_enddate,  b_startdate, b_enddate FROM test;

Try out this SQL Fiddle, and see if it gives the results you're looking for.  I did the best I could with the description I had.


Find out why SQL Server released memory I have a SQL Server 2008 R2 used for sharepoint and ssrs. The SQL Server hosts two instances.

For a few minutes both instances were timing out, not responding.

CPU slowly rose to 100%, 30% from both instances, and 30% from the OS (and a few smaller things).

Afterwards, 16GB of memory was unallocated from SQL Server, a few GB of memory was used by some process (can't tell which on the esxi).

Since then, SQL Server again has its maximum amount of memory, and PLE is around 15000.

What I'd like to know is, how can I track WHY SQL Server was forced to give up its memory?

Either a history somewhere in the trace file, or a windows event? (I struck out on both so far)

I tried:

DECLARE @log NVARCHAR(100)
SELECT @log = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) + '\log.trc'
FROM   sys.traces
WHERE  id = 1
SELECT
    g.DatabaseID,
    g.LoginName,
    g.StartTime,
    g.EndTime,
    g.DatabaseName,
    g.FileName,
    e.name
FROM  ::fn_trace_gettable(@log, 0) as g
inner join sys.trace_events e on g.eventclass = e.trace_event_id
inner join sys.trace_categories as c on e.category_id = c.category_id
inner join sys.master_files as m on g.databaseID = m.database_id and g.filename = m.name
order by StartTime desc

But couldn't find anything during the time of the outage.

If it turns out not to be possible to find out why this happened, how can I monitor this in the future.

EDIT:

SQL Server version: 10.50.6000.34 Enterprise edition,

The server is being virtualised in esxi 5.5,

Page file is set at 8GB.

The error log shows a single failed logon of a sharepoint service user. (And a lot of successful log backups)

Update:

I've since had confirmation from the VMWare team that no Vmotions happened during this period on any related machines. And no ballooning occurred that might have affected the machine.

I've also had the network team confirm that Nagios showed no noticeable spike in latency on any of the machines related to this farm.

And we've since excluded any possible reports that were running at the time.

We have however seen 3 other occurrences of a 16GB of memory drop from SQL server in past statistics. 2 of which were related with reports of a temporary decrease in performance.

Because SQL Server released the memory back to the OS, this means the memory pressure was external, so you aren't going to find the reason within SQL Server.  Your answer is going to come from outside SQL Server and I know of one tool that can track the memory allocations of the OS.

Rammap  https://technet.microsoft.com/en-us/sysinternals/rammap.aspx


MSSQL Single Listener for Multiple AGs I was looking for a solution in which my application, one which I can configure only one Listener param, if we can address multiple Availability Groups or not.
I have heard of using multiple listeners for one or multiple AGs, but is it possible to use a single listener and address multiple AGs?

but is it possible to use a single listener and address multiple AGs.

A listener is specific to Instance of AG so you cannot connect to multiple AG's using one listener. If you have multiple instances you have to use multiple AG's.

How do you think a single listener will manager multiple AG's now how will it know which AG to connect ?


Creating a hot standby with Postgres 9.6 I'm trying to create a hot standby with Postgres 9.6 on Ubuntu 16.04.

I can successfully ssh from my standby machine to the primary as postgres.

I have removed /var/lib/postgresql/9.6/main and running:

pg_basebackup -h <primary IP> -D /var/lib/postgresql/9.6/main -U rep -v -P --xlog-method=stream

that successfully creates a new main directory.

In /etc/postgresql/9.6/main/postgresql.conf, I have:

added this servers IP address to the listen_addresses
set wal_level = replica
set hot_standby = on

I've created a recovery.conf with:

standby = on

primary_conninfo = 'host=<primary ip> port=5432 user=rep password=<password>

On sudo service postgres start, postgres starts and I see:

2016-12-22 07:24:29 UTC [10728-1] LOG:  database system was interrupted; last known up at 2016-12-22 07:13:44 UTC
2016-12-22 07:24:30 UTC [10728-2] LOG:  redo starts at 3/4D000028
2016-12-22 07:24:30 UTC [10728-3] LOG:  consistent recovery state reached at 3/4D000130
2016-12-22 07:24:30 UTC [10728-4] LOG:  redo done at 3/4D000130
2016-12-22 07:24:30 UTC [10728-5] LOG:  MultiXact member wraparound protections are now enabled
2016-12-22 07:24:30 UTC [10732-1] LOG:  autovacuum launcher started
2016-12-22 07:24:30 UTC [10727-4] LOG:  database system is ready to accept connections
2016-12-22 07:24:30 UTC [10734-1] [unknown]@[unknown] LOG:  incomplete startup packet

Should I not see a message where it tries to contact the master?

2016-12-22 07:24:30 UTC [10728-3] LOG:  consistent recovery state reached at 3/4D000130
2016-12-22 07:24:30 UTC [10728-4] LOG:  redo done at 3/4D000130
2016-12-22 07:24:30 UTC [10728-5] LOG:  MultiXact member wraparound ?protections are now enabled
2016-12-22 07:24:30 UTC [10732-1] LOG:  autovacuum launcher started
2016-12-22 07:24:30 UTC [10727-4] LOG:  database system is ready to accept connections

Looks like your recovery.conf file has ignored.  Are you shure that the recovery.conf has the right permissions (basically owned by the user that run the service, usually postgres)?


ERROR: loop variable of FOREACH must be a known variable or list of variables I have few FOREACH loops in my functions. The loops seem to perfectly agree with the official postgresql documentation. This error message is returned:

ERROR: loop variable of FOREACH must be a known variable or list of variables
SQL state: 42601

Google found nothing for postgresql "loop variable of FOREACH". I solved this somehow for my older foreach loops, but I don't remember how, and I can't find any difference that could be significant for the solution of this error.

I have PostgreSQL 9.3.4 and pgAdmin III 1.18.1 on Windows XP 32 bit. Here are interresting parts of the functions:

CREATE OR REPLACE FUNCTION vloz_kont() RETURNS trigger AS $$
DECLARE
  _idmat integer;
  _nazmat text;
  _zast text;
  sj_nad text;
  ...
BEGIN

...

--bugged loop
FOREACH _mat IN ARRAY NEW._material::text[] LOOP
  SELECT split_part( _mat, ' ', 1 ) INTO _nazmat;
  SELECT rtrim( ltrim(split_part( _mat, ' ', 2 ), '(' ), ')' ) INTO _zast;
  SELECT id INTO _idmat FROM pro_material WHERE popis = _nazmat;
  IF _idmat IS NOT NULL THEN
    INSERT INTO material_konst ( id_mat, kod_sj, kod_akce, zastoupeni )
    VALUES ( _idmat, NEW._kod_sj, NEW._kod_akce, _zast );
  ELSE
    INSERT INTO material_konst ( id_mat, kod_sj, kod_akce, jiny_nazev, zastoupeni )
    VALUES ( 8, NEW._kod_sj, NEW._kod_akce, _nazmat, _zast );
  END IF;
END LOOP;

...

--OK LOOP
FOREACH sj_nad IN ARRAY NEW._nad::text[] LOOP
  INSERT INTO s_vztah (nad, pod, kod_akce, typ_vztahu)
  VALUES(
    sj_nad,
    NEW._kod_sj,
    NEW._kod_akce,
    1
  );
END LOOP;

...

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Both _material and _nad are text array columns of a view, as well as all other variables. The whole INSTEAD OF trigger function is too long to reproduce in entirety (almost 200 code rows) - ... in the code sample represent skipped unrelated code.

So why is there the error, and how to solve it?

Without seeing complete code, or a cut-down test case, it's hard to be sure but the error strongly suggests that:

FOREACH _mat
        ^^^^
        this variable is undeclared


Join against two tables to get single combined result set tl;dr I made a SQLFiddle here http://sqlfiddle.com/#!9/031e71/1 with a minimal, complete, reproducible test case (though very specific to the problem domain of orders and products).

I have a Zen Cart ecommerce system on MariaDB and have a problem with the way order information is extracted into downstream order management systems.  I have also looked into scripting in the order management system to modify the data, but it seems their API doesn't allow modification of orders at that stage (argh!) so I'm trying it via the SQL used as the orders are read from MariaDB into the order management system.

The scenario is where a coupon has been applied to an order, resulting in a discount, and I want that coupon's existence to appear in a resultset alongside the products in the order.  So if you purchase Nuts for $100 and apply a $10 discount coupon, I want two rows in the resultset, one for Nuts at $80 and one for the coupon at $10, so the total equals $90 (the $100 cost less the $10 discount).  The maths isn't the problem, the problem is that the existence of the products, and the existence of the coupon, are in two very different tables.

Zen Cart has three main tables:

orders - one record per Order
orders_totals - 4 or 5 records per Order, with things like Total, Sub-Total, VAT Content, Discount Coupon (this is the crux of my problem)
orders_products - one record per product in each order.

I want to join orders against both orders_totals and orders_products to get one row per product, and an extra row if a row exists in orders_totals for that order that is a coupon.

Is there an elegant way to solve this?  I'm not sure if a cunning JOIN is enough, or if a View or PARTITION may be required.

** Edit ** I made a new fiddle which takes the coupon amount, divides it by the number of products, and subtracts that portion from each product amount, combined with the UNION suggestion.  See http://sqlfiddle.com/#!9/27ad50/3 .. but this seems quite ugly with all the subselects going on in the joins in the first half of the UNION, I hesitate to say this is a good solution!

In this case, and due Coupon is an specific entry of another table I'd use a UNION.

As a suggestion, I'd try to convert 'Coupon' entry of orders_total, into another product added as another row to the orders table.

SELECT orders_id, products_name as name, cost
FROM orders o
JOIN  orders_products op on o.orders_id = op.fk_orders_id
WHERE o.orders_id = 1
UNION
SELECT fk_orders_id, name, total as cost
FROM   orders_totals
WHERE fk_orders_id = 1
AND   name = 'coupon'

Results:

| orders_id |   name | cost |
|-----------|--------|------|
|         1 |   Nuts |  100 |
|         1 | coupon |  -10 |

sqlfiddle here


Should I update to sql server 2008 sp2 before restoring the db's or do the update after? I'm making a move from SQL Server 2008 to SQL Server 2008 R2 (Azure VM) as part of a hosting provider switch (from MyHosting to Azure). Should I update the VM to R2 SP2 before restoring the db's or do the update after or don't even do the update?

That does not matter. When you upgrade an existing instance with a database attached SQL Server automatically upgrades the database too. If you attach the database after the upgrade, it goes through the same process.

In either case make sure that the database is at the right compatibility level. While usually nothing happens, you might run into problems if the database is still at 80. Changing the compatibility level however will change behavior, so make sure you test you application well.


Postgres value that never goes below zero For my application i need to make a row with a value that can never go below zero. I know about check but this one can't fail the query if i do for example: update table set special_row = special_row - 100 where special_row = 50 what i want it to do is to just make special_row be 0 instead o -50 as the query would do.

You can do this with BEFORE INSERT and BEFORE UPDATE triggers. Here is a tutorial.

These triggers can access the row being inserted (or the updated value) in the NEW variable. Do not use an UPDATE statement to change the row! Simply change the NEW variable, have the trigger function RETURN NEW, and the values will be used in the INSERT/UPDATE that fired the trigger. This only works in BEFORE triggers. AFTER INSERT/UPDATE cannot modify values since the operation is already done.

Code would look like this:

CREATE OR REPLACE FUNCTION mytrigger() RETURNS TRIGGER AS $example_table$
   BEGIN
      IF NEW.yourcolumn < 0 THEN NEW.yourcolumn := 0 END
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

Then use the CREATE TRIGGER command as mentioned in the tutorial.


DBCA and can't init database SQL> startup;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.0.1/db_1/dbs/initAWSDB.ora'

I created a new Oracle 12c database using dbca

the only files in the dbs directory

$ ls -ltrh
total 24K
-rw-r--r--. 1 oracle oinstall 3.1K May 15  2015 init.ora
-rw-r-----. 1 oracle oinstall   24 Mar 22 23:10 lkCFRDB
-rw-r-----. 1 oracle oinstall 3.5K Mar 22 23:13 orapwCFRDB
-rw-rw----. 1 oracle oinstall 1.6K Mar 22 23:27 hc_CFRDB.dat
-rw-r-----. 1 oracle oinstall 3.5K Mar 22 23:27 spfileCFRDB.ora
-rw-rw----. 1 oracle oinstall 1.6K Mar 22 23:42 hc_AWSDB.dat

When I tried to start up with init.ora

SQL> startup pfile=/u01/app/oracle/product/12.2.0.1/db_1/dbs/init.ora
ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
ORA-01262: Stat failed on a file destination directory
Linux-x86_64 Error: 2: No such file or directory

Your instance is called CFRDB, but you have set ORACLE_SID to AWSDB.

export ORACLE_SID=CFRDB, then try again.


Help defining the correct relationship between two tables I am working on the implementation of a point of sale system for restaurants, in my question I need to know the relationship that exists between the entities tables and orders, based on the following conditions

A customer can be served at a table.
A client can request to join several tables in consequence the order of this client can have several tables.

The current idea is that I should create a table called for example order_table with a many-to-many relationship between tables and orders or it should be a one-to-many relationship between orders and order_table.

Based on the conditions that I mention what you think would be the best solution to this case.

Thank you very much for your time

simply you should have these Tables

Customer: Which Represents some information about your Clients
Order: Which Represents Information about the Orders your customers are making, of course you should have a CustomerID field in this table which references Customer(CustomerID)
Table:  each Restaurant can have some Tables which you are storeing information about Tables in this [Database table]
OrderTables : in order to Relate Orders with Tables you should use  this Table which you will have a structure as {OrderID , TableID} in this Table
so it will be a one-to-many Relation


Which of the following pieces of SQL is most efficient I need to add the date part of GetDate() to the time part of a stored DateTime.

Naively I thought I could do the following:

declare @testTime DateTime = '2013-04-23 13:55:06'
select cast(getdate() as date) + cast(@testTime as time)

but this gives the following error:

  Operand data type date is invalid for add operator.

I did some more research and have come up with three possible ways of doing the calculation:

declare @testTime DateTime = '2013-04-23 13:55:06'

select dateadd(dd, 0, DATEDIFF(dd, 0, GetDate())) + cast(@testTime as time)

select cast(cast(GetDate() as date) as datetime) + cast(@testTime as time)

select dateadd(day, datediff(day, @testTime, GetDate()), @testTime)

All three produce the same result but I'm not 100% happy about implementing any of them, though the third way seems to be a better way - though I have no evidence for this.

Which (if any) of the three is the most efficient?

Is there a better way?

You could use DATETIMEFROMPARTS (Transact-SQL)

select datetimefromparts(
                        datepart(year, getdate()),
                        datepart(month, getdate()),
                        datepart(day, getdate()),
                        datepart(hour, @testTime),
                        datepart(minute, @testTime),
                        datepart(second, @testTime),
                        datepart(millisecond, @testTime)
                        )

Update:

The first two of your queries.

select dateadd(dd, 0, DATEDIFF(dd, 0, GetDate())) + cast(@testTime as time)

select cast(cast(GetDate() as date) as datetime) + cast(@testTime as time)

require that the database compatibility level is SQL Server 2008(100). If they are executed in compatibility level (110) or (120) you will get

  Msg 402, Level 16, State 1, Line 3 The data types datetime and time
  are incompatible in the add operator.

so you should not consider those at all.

The last query

select dateadd(day, datediff(day, @testTime, GetDate()), @testTime)

works just fine.

I did a test over 1000000 rows in SQL Server 2014 and the datetimefromparts version took 650 ms and dateadd/datediff took 350 ms.


Debugging TSQL in SSMS What techniques can I use to debug TSQL code now the debugger in SSMS 18 has been deprecated/removed?

The way I am using might not the best, however, just to reassure you that this is possible and it has been working fine for me:

if you really like the debug least for now I am using both versions of ssms, the 17.9 and the 18 side by side as you can see on the picture below:

I use sql server from 2008 r2 up to sql server 2017.


function json_populate_record(..., text) does not exist I went by the example from the docu which went fine:

select * from json_populate_record(null::x, '{"a":1,"b":2}')

But my self-constructed JSON simplified similar to this did not work :-(

-- p_some_num of type int
select * from json_populate_record( null:my_record_type, '{"a":'||p_some_num||'',"b":2}' )

resulting in:

ERROR: function json_populate_record(my_record_type, text) does not exist

I should have read more carefully and was not aware of the implicit text to json conversion in the example. After some fiddling around with what was wrong, of course the following works:

select * from json_populate_record(
  null:my_record_type, ('{"a":'||p_some_num||'',"b":2}')::json )


How do I speed up a string manipulation query where I want to replace characters, extract certain values and update a table with the results? I am trying to extract information from strings that are presented in a key-value format, with the keys and values being separated by commas. I want to extract the values associated with certain keys and add them to dedicated columns in my table.
Some notes on the data:

The keys I am interested in are connected, as in keyB relates to keyA;
in some cases keyB or keyA may not exist
If keyB doesn't exist, but keyA is something specific, then I can set the value for keyB anyway.

I have a solution that does what I want ([db-fiddle]), but it is painfully slow (9.6 hours) and I can't help think that there must be a better way as I've not been in this DB game long.
For info,
The table has ~8.2M rows and is hosted on AWS RDS on a t3.large DB (virtual CPUs = 2, Memory = 8.0GB).
Some pointers on where I can improve this are much appreciated.

You only need a single INSERT and a single UPDATE for the main table. The whole default detection and populating of the "variables" (columns) can be done while inserting the data:
INSERT INTO main_tags (id, tags, variablea, variableb)
select id, tags, var_a,
       case
          when var_b is null and 'valA' = any(tags)  then 'valB_default'
          when var_b is null and 'valA_xx' = any(tags) then 'valB_default_two'
          else var_b
       end as var_b
from (
  SELECT id, tags,
         tags[array_position(tags, 'keyA') + 1] as var_a,
         tags[array_position(tags, 'keyB') + 1] as var_b
  FROM (
    SELECT id, string_to_array(regexp_replace(tags,'[{}"]','','g'), ',') as tags
    FROM main
  ) as b
) x
WHERE tags && array['keyA','keyB'];

The final update can also handle the "unknown" value directly, no need to run three updates:
UPDATE main e
  SET variableA = coalesce(et.variableA, 'unknown'),
      variableB = coalesce(et.variableB, 'unknown')
FROM main_tags et
WHERE e.id = et.id;

One way to speed that up, is to create an index on main_tags (id)

You can actually get rid of the temp table completely and do everything in a single statement. This might or might not be faster, but I think it's worth trying:
UPDATE main e
  SET variableA = coalesce(et.var_a, 'unknown'),
      variableB = coalesce(et.var_b, 'unknown')
FROM (
  select id, tags,
         var_a,
         case
            when var_b is null and 'valA' = any(tags)  then 'valB_default'
            when var_b is null and 'valA_xx' = any(tags) then 'valB_default_two'
            else var_b
         end as var_b
  from (
    SELECT id,
           tags,
           tags[array_position(tags, 'keyA') + 1] as var_a,
           tags[array_position(tags, 'keyB') + 1] as var_b
    FROM (
      SELECT id, string_to_array(regexp_replace(tags,'[{}"]','','g'), ',') as tags
      FROM main
    ) as b
  ) x
  WHERE tags && array['keyA','keyB']
) as et
WHERE e.id = et.id;


Arithmetic Overflow at RunningPackages (SSIS) problem I've developed an SSIS Package with the C# Code.All the Package were running fine.But for the past 2 days, we are experiencing

public void Main()
        {
            // TODO: Add your code here
            try
            {
                MessageBox.Show("PAckage Starts");
                Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

                MessageBox.Show("BEFORE ERROR");

                string a=Dts.Variables["System::MachineName"].Value.ToString();
                RunningPackages pkgs = app.GetRunningPackages(null);
               RunningPackages abc = app.GetRunningPackages(a);
                MessageBox.Show("AFTER ERROR");
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
                MessageBox.Show(e.Message.ToString());
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

This is my Stack Trace.
I've already browsed regarding this issue but not getting any solution. Some Reference here.

Investigating into this with Process Monitor tool. The Issue is coming from MsDtsSrvr.dll (C:\Program Files (x86)\Microsoft SQL Server\DTS\Binn\MsDtsSrvr.dll)

Arithmetic operation resulted in an overflow. (MsDtsSrvr)

Unfortunately, this Issue is not happening in other Environments.

Anybody, please guide me to solve this error

Thanks in advance,
Jay

I've found the solution.As I've told already this is related to MsDtsSrvr.dll

So, I've just Restarted the MsDtsServer110 Service.

No it works Perfectly


Find time difference in different rows with a condition I have a table showing statuses (working, not working) for several devices (ordered by device desc, datetime desc):

rownum device     datetime                  status
--------------------------------------------
1     device_1   2018-06-27 12:10:05.443       1
2     device_1   2018-06-27 12:02:31.467       0
3     device_1   2018-06-27 11:57:24.260       1
4     device_2   2018-06-27 11:57:24.260       1
5     device_2   2018-06-26 12:57:24.260       1
6     device_3   2018-06-26 09:57:24.260       1
7     device_3   2018-06-25 09:57:24.260       0
8     device_3   2018-06-26 09:57:24.260       0
9     device_3   2018-06-24 09:57:24.260       1
10    device_4   2018-06-27 09:57:24.260       0
11    device_4   2018-06-26 09:57:24.260       0

I need to get the amount of time during which a device was not available (status = 0).
So for device_1 it would be time difference between the date it was not working (row 2) and working (row 1).

Device_2 was always working.

Device_3 broke (row 8) and started working (row 6).

Device_4 was never working, so the amount of time would be from (row 11) to (row 10)

Query output table is to be:

device    amountoftimeinmins
----------------------------
device_x    0

I'd appreciate any help.

You don't need a pivot here. You can use LAG window function to retrieve the previous status and datetimes for each device, then compare their minutes if it goes from 0 to 0 or from 0 to 1.

IF OBJECT_ID('tempdb..#DeviceStatus') IS NOT NULL
    DROP TABLE #DeviceStatus

CREATE TABLE #DeviceStatus (
    rownum INT IDENTITY,
    device VARCHAR(10),
    datetime datetime,
    status BIT)

INSERT INTO #DeviceStatus (
    device,
    datetime,
    status)
VALUES
    ('device_1',   '2018-06-27 12:10:05.443',      1),
    ('device_1',   '2018-06-27 12:02:31.467',      0),
    ('device_1',   '2018-06-27 11:57:24.260',      1),
    ('device_2',   '2018-06-27 11:57:24.260',      1),
    ('device_2',   '2018-06-26 12:57:24.260',      1),
    ('device_3',   '2018-06-26 09:57:24.260',      1),
    ('device_3',   '2018-06-25 09:57:24.260',      0),
    ('device_3',   '2018-06-26 09:57:24.260',      0),
    ('device_3',   '2018-06-24 09:57:24.260',      1),
    ('device_4',   '2018-06-27 09:57:24.260',      0),
    ('device_4',   '2018-06-26 09:57:24.260',      0)

;WITH LaggedStatus AS
(
    SELECT
        D.*,
        LaggedStatus = LAG(D.status) OVER (PARTITION BY D.device ORDER BY D.datetime ASC),
        LaggedDatetime = LAG(D.Datetime) OVER (PARTITION BY D.device ORDER BY D.datetime ASC)
    FROM
        #DeviceStatus AS D
),
MinutesBroken AS
(
    SELECT
        L.*,
        MinutesBroken = CASE WHEN L.LaggedStatus = 0 THEN DATEDIFF(MINUTE, L.LaggedDatetime, L.datetime) END
    FROM
        LaggedStatus AS L
)
SELECT
    M.device,
    TotalMinutesBroken = ISNULL(SUM(M.MinutesBroken), 0)
FROM
    MinutesBroken AS M
GROUP BY
    M.device

Result:

device      TotalMinutesBroken
device_1    8
device_2    0
device_3    1440
device_4    1440

Note: LAG is for version 2012+, if you have a previous one, you can simluate lag's effect with ROW_NUMBER().


How to trace a query on Firebird? With SQL Server we can trace for a period of time, to get what queries are running.

How can I do this with Firebird? I'm reading something on their website but there's only user trace like if the login failed or not.

Found something here:

https://stackoverflow.com/questions/30248937/how-to-capture-firebird-sql-queries

Will test it.

Firebird has two distinct options for monitoring:

The monitoring tables, which provide a snapshot of activity on the server. For example MON$STATEMENTS provides the currently running statements. Note that content of monitoring tables are stable within a transaction, so to refresh it, you will have to commit and start a new transaction.
The trace facility, which allows you to get information on activity in a database. It can be either be configured by an administrator to run server side, or users can do limited tracing of their own sessions remotely (users with administrator rights have more access). For details see Trace and Audit Services in the Firebird 2.5 release notes, and the article Audit and Trace Services in Firebird 2.5 by Thomas Steinmaurer.

Tooling included with Firebird is rather basic and low level, and you may want to look at third party tools that simplify access to this trace facility.

In addition, there are tools that act like a proxy for Firebird, allowing you to trace activity that way, for example FBScanner.

See also my answer on How to capture Firebird SQL queries? on Stack Overflow (as you already found).


How to check/know the highest run queries I would like to gather statistics on the highest run queries in my database.

For that I need to know how I can track queries so I can create views or materialized views to cache the results of the highest run queries since I have a big DB.

As someone said in the comments, pg_stat_statements is the way to get the statistics. Put this into your postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000
pg_stat_statements.track = all

Then run this query:

CREATE EXTENSION pg_stat_statements;

After that, this sample query (copied from the docs linked above) will give you the stats for 5 top queries from all the databases:

SELECT query, calls, total_time, rows,
100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

If you want results for a single database, you need to filter by dbid which you can get from pg_database by db name. Add this WHERE clause to the query above:

WHERE dbid = (select oid from pg_database where datname = 'YOUR_DB_NAME')

You could also do a join.

When you're testing this it may be a good idea to exclude the queries to the stats/schema tables themselves, for example:

AND query not similar to '%( pg_|information_schema)%'

There's a bunch of free and commercial tools that can help you visualize the data.


PG_bulkload utility postgresql need help i am using COPY command to export the data to the out file with changes made to the data before export using option ST_asBinary() using command

COPY (
    SELECT st_asBinary(geom) FROM n_america
) TO '/install/shp_data/n_america_asbin_1_col.txt'
WITH DELIMITER ',';

now i am facing problem with Restoring back to the other table with geometry column , which is now converted to binary while exporting

i have tried with COPY FROM as well as PG_BULKLOAD utility , but i did't got any solution to do this ,

any one please help

Regards
Deepak M

Your simple solution is to import into a separate table and then use another query to copy the data back, converting to proper format.


Comparing data types of a primary and foreign key I'm writing a script to check SQL against a standards document before it's implemented. I'm currently trying to write a script that compares the Primary and Foreign key's data type and precision. I know I need to use:

DBA_TAB_COlUMNS
DBA_CONSTRAINTS
DBA_CONS_COLUMNS

But I can't seem to join them in a way that's going to compare the datatypes and return a result if there's a difference.

I am sure this can be optimized but here is to experiment,

with parent as (select --+ no_merge(dc) no_merge(dccr) no_merge(dtcr)
distinct dc.r_owner ref_owner,  dc.r_constraint_name,dtcr.column_name
,case when dtcr.NULLABLE = 'N' then 'NOT NULL ' end ||
  case when dtcr.DATA_TYPE in ('CHAR','VARCHAR2') then dtcr.DATA_TYPE || ' (' || dtcr.DATA_LENGTH || ')'
     when dtcr.DATA_TYPE = 'DATE' then 'DATE'
     when dtcr.DATA_TYPE = 'TIMESTAMP' then 'TIMESTAMP (' || dtcr.DATA_SCALE || ')'
     when dtcr.DATA_TYPE = 'NUMBER' and dtcr.DATA_PRECISION is null then 'NUMBER'
     when dtcr.DATA_TYPE = 'NUMBER' and dtcr.DATA_PRECISION is not null then 'NUMBER (' || dtcr.DATA_PRECISION ||',' || NVL(dtcr.DATA_SCALE,'0') || ')'
end c_dt
from dba_constraints dc
join dba_cons_columns dccr on (dccr.owner = dc.r_owner and dccr.constraint_name = dc.r_constraint_name)
join dba_tab_columns dtcr  on (dccr.owner = dtcr.owner and dccr.column_name = dtcr.column_name and dccr.table_name = dtcr.table_name)
where dc.constraint_type = 'R' and dc.r_owner = 'SCHEMA_OWNER')
,child as (select --+ no_merge(dc) no_merge(dccr) no_merge(dtcr)
distinct dc.owner ref_owner,  dc.constraint_name, dc.r_constraint_name, dtcr.column_name, dtcr.table_name
,case when dtcr.NULLABLE = 'N' then 'NOT NULL ' end ||
  case when dtcr.DATA_TYPE in ('CHAR','VARCHAR2') then dtcr.DATA_TYPE || ' (' || dtcr.DATA_LENGTH || ')'
     when dtcr.DATA_TYPE = 'DATE' then 'DATE'
     when dtcr.DATA_TYPE = 'TIMESTAMP' then 'TIMESTAMP (' || dtcr.DATA_SCALE || ')'
     when dtcr.DATA_TYPE = 'NUMBER' and dtcr.DATA_PRECISION is null then 'NUMBER'
     when dtcr.DATA_TYPE = 'NUMBER' and dtcr.DATA_PRECISION is not null then 'NUMBER (' || dtcr.DATA_PRECISION ||',' || NVL(dtcr.DATA_SCALE,'0') || ')'
end c_dt
from dba_constraints dc
join dba_cons_columns dccr on (dccr.owner = dc.owner and dccr.constraint_name = dc.constraint_name)
join dba_tab_columns dtcr  on (dccr.owner = dtcr.owner and dccr.column_name = dtcr.column_name and dccr.table_name = dtcr.table_name)
where dc.constraint_type = 'R' )
select parent.ref_owner, parent.r_constraint_name ref_constraint, parent.column_name ref_col, parent.c_dt parent_col_datatype
,child.ref_owner child_owner, child.table_name child_table, child.r_constraint_name child_constraint, child.column_name child_col, child.c_dt child_col_datatype
from parent join child on (parent.r_constraint_name = child.r_constraint_name) order by 1,2,3;


Can I run UPDATEs on a replicated MySQL slave, safely? We would like access to accurate MySQL backups for debugging, without having personal information (PII) in the backups.

Is it possible to run UPDATEs on the replicated database (ie. replacing text columns with Lorem Ipsum) without breaking replication?

(Note: The schema allows us to identify newly updated rows very easily - meaning it is trivial for us to identify new PII quickly)

Yes with many caveats- I have literally this setup to expose Wikipedia database in public, and I would not recommend it unless you do not have an alternative options. We use triggers that update/blank fields that are private, replication filters to avoid full tables/databases, and views to limit certain per-row accesses.

Problems we faced:

Statement based replication is very flexible, but if you run certain writes based on existing data (e.g. INSERT...SELECT, updates based on modified columns), it will either break replication or create a data drift. You should limit application writes by inserts and updates by fields you are not going to modify-but it is not possible in most cases to control all possibilities except on simple models (insert-only writes, for example). The breakage can be very subtle but it may happen over time if the application is minimally complex.
Row based replication is more accurate (because you get the final state), but it does not trigger the triggers. We started using MariaDB 10.1 to have replica-only triggers to be able to do that (thus solving data drift), but we still have issues due to row based replication have limitation in terms of supporting different schema on master and replica.
Triggers and filters make things difficult to manage and more room for errors
If possible, I would suggest not having real time replication, but using dumps so you can filter on query time (in our cases it is not possible due to the size of the database and requiring real time from some usages).
Because you have different data, running pt-table-checksum and other comparing tools is not really possible (to check for data discrepancies)

When we have the resources, we will likely move to a custom filtering automation that analyzes the row based binary protocol in pseudo real-time and performs more complex filtering on application layer.


SELECT INTO with regexp_replace() doesn't write changes into newly generated table I have been using this query in Postgres 9.1 in order to remove '' from existing data.
Below is a sample of the data before running regexp_replace():

6''
6''
6''
20''
12''
18''
20''
8''
10''
''
''

Upon running:

select REGEXP_REPLACE(regexp_replace, $$^'*$$, '' , 'g')
from temp_4 order by id;

I receive this clean output:

6
6
6
20
12
18
20
8
10
( ) <- stand-in for " "
( )

however when attempt to then write these results into a table, say using:

select * into table_3 from (select REGEXP_REPLACE(tbl, $$^"''*,*$$, '' , 'g') from temp_2 order by id) as temp_3;

I receive

6
6
6
20
12
18
20
8
10
''
''

Where '' has been removed from all values, except in the case it was the only value present. I have attempted to whitelist using [\w\s*] all other values instead of blacklist with this regex ^'*, but in both instances values of ''. I feel like I'm taking crazy pills.

How do I write my table where I can replace '' with 0 or null values?

Also, is my syntax terribly wrong in my attempts to write this data to other tables? Is there a better way to do this?

Your regexp_replace() statement is invalid. It would work like this:

SELECT regexp_replace(tbl, '('''')$', '' , 'g')
FROM (
 VALUES
  ('6''''')
 ,('6''''')
 ,('6''''')
 ,('20''''')
 ,('12''''')
 ,('18''''')
 ,('20''''')
 ,('8''''')
 ,('10''''')
 ,('''''')
 ,('''''')
) tbl(tbl)

Your SELECT INTO statement is invalid. It would look like this:

SELECT regexp_replace(tbl, '('''')$', '' , 'g')
INTO   temp table_4
FROM   temp_2
ORDER  BY id;

But I would use neither.

SELECT INTO is discouraged. Only supported for historical reasons. Use CREATE TABLE AS instead, which is the SQL standard way. Per documentation:

  CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE
  AS is the recommended syntax, since this form of SELECT INTO is not
  available in ECPG or PL/pgSQL, because they interpret the INTO clause
  differently. Furthermore, CREATE TABLE AS offers a superset of the
  functionality provided by SELECT INTO.

Bold emphasis mine.

For the presented examples, rtrim() is much simpler and faster:

CREATE TEMP TABLE table_4 AS
SELECT rtrim(tbl, '''')     -- trim all trailing '
FROM   temp_2
ORDER  BY id;


MySQL 5.1.73 change default log location How do you change the default location of the log_error file?  If you point it to a new path, will it automatically move the existing file? Or will it create a new file, and the original file can be deleted?  Software is installed on Windows.

Also, how do you change the default location of the innodb log files?

A). To change Mysql innodb_log file to a new location.

 1. Change my.cnf settings for innodb_log file or innodb_fast_shutdown=0
   (so it does full flush of log files from buffer).

 2. Stop mysql.

 3. Change innodb_log_group_home_dir = /mysql/new_location/ in my.cnf.

 4. Start Mysql, it should be up now.

B). To change Mysql log_error file to a new location.

 1. Create a new file for log_error file OS level.
     - copy nul > /mysql/NEW_LOCATION/mysql_error
     - Give mysql permission to /mysql/NEW_LOCATION/mysql_error to read and write.

 2. Change my.cnf log_error = /mysql/NEW_LOCATION/mysql_error

 3. Stop Mysql.

 4. Start Mysql.

You may do A and B in one shot, I will let you do organize which ever way you wanted.


Give me suggestion about backup of schema and full backup in oracle I need your help about backup in oracle database. Currently I am using Export Utility of Schema Backup but taking more time to complete and size is 40GB.

For full database backup using Image Copy and taking time more than 15 hours to complete and size is 650GB.

Which backup method is best for schema backup and full database backup which take less time and less size.

Database version Oracle Database 11gR2

Regards

650*1024/15/3600 = 12.3 MB/s. Sounds like you have a 100 Mbit network connection for backup. In that case, using a compressed backupset instead of image copy will significantly reduce the time it takes to backup the database. – Balazs Papp


inner join shows more entries maybe this is a really basic question, but I'm having problems whit it

I want to generate a new table based on info taken from those two tables:

Trelaciones:

+-------------+-------------+------+-----+-------------------+-------+
| Field       | Type        | Null | Key | Default           | Extra |
+-------------+-------------+------+-----+-------------------+-------+
| Device_SN_O | varchar(25) | NO   | PRI | NULL              |       |
| Device_SN_D | varchar(25) | NO   | PRI | NULL              |       |
| Interface   | varchar(25) | YES  |     | NULL              |       |
| Port_ID     | varchar(25) | YES  |     | NULL              |       |
| date        | timestamp   | NO   |     | CURRENT_TIMESTAMP |       |
+-------------+-------------+------+-----+-------------------+-------+

dispositivos:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| sn        | varchar(25) | NO   | PRI | NULL    |       |
| device_id | varchar(25) | YES  |     | NULL    |       |
| ip_adress | varchar(25) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

dispositivos has a record of all devices in a network, its primary key is sn and this field match with Device_SN_O (O for origin) and Device_SN_D (D for destiny)

Trelaciones has information about the connections

let's say that I want to search for all devices connected to certain device i do something like this:

SELECT * FROM Trelaciones WHERE Device_SN_O= "FCQ1527Y0B3";

Result:

+-------------+-------------+--------------------+-----------------------+---------------------+
| Device_SN_O | Device_SN_D | Interface          | Port_ID               | date                |
+-------------+-------------+--------------------+-----------------------+---------------------+
| FCQ1527Y0B3 | FOC1929S37N | GigabitEthernet0/1 | GigabitEthernet1/0/23 | 2017-04-27 09:58:11 |
+-------------+-------------+--------------------+-----------------------+---------------------+

I would like to generate an inner join sentence that creates a table like this:

+-----------------+-----------------+--------------------+-----------------------+---------------------+
| IP_origin       | IP_destiny      | Interface          | Port_ID               | date                |
+-----------------+-----------------+--------------------+-----------------------+---------------------+
| 148.228.110.249 | 148.228.110.250 | GigabitEthernet0/1 | GigabitEthernet1/0/23 | 2017-04-27 09:58:11 |
+-------------+-------------+--------------------+-----------------------+---------------------+

So far I've tried this:

SELECT dispositivos.ip_adress,Trelaciones.Interface, Trelaciones.Port_ID
FROM Trelaciones
INNER JOIN dispositivos
ON Trelaciones.Device_SN_O = "FCQ1527Y0B3";

But it is not even close to the result i expect, this is returning 89 rows and I don't know why.

This part of your join is incorrect:

Trelaciones.Device_SN_O="FCQ1527Y0B3"

That should be your WHERE clause.

Your join should be:

FROM Trelaciones
INNER JOIN dispositivos
ON  Trelaciones.whatever_key_column = dispositivos.whatever_key_column

So

FROM Trelaciones
INNER JOIN dispositivos
ON  Trelaciones.whatever_key_column = dispositivos.whatever_key_column
WHERE Trelaciones.Device_SN_O="FCQ1527Y0B3"


MySQL data warehouse calculate difference over years in percentage I'm trying to create a query to calculate the percentage difference based on a time range (e.g. WHERE dim_date.year BETWEEN 2009 AND 2011) spreaded on months.

My question is how to add the new column to calculate the differences (positive or negative) against same month from base year.

I'm aware they are two questions in one but if the above question is not possible, it is possible to compare a given date range against current year/month.
Please see the below table for a better overview of the question. This is the desired output..

The simple SELECT statement (without COUNT or SUM) is like this:

SELECT
  dim_date.year,
  dim_date.month,
  fact.total_cost
FROM fact
  INNER JOIN dim_date
    ON fact.date_key = dim_date.date_key
WHERE dim_date.year BETWEEN 2009 AND 2011

For simplicity please have a look at below star schema.

Thank you in advance for any help.

Ponder a LEFT JOIN something like this

SELECT  a.year,
        a.month,
        a.total_cost,
        IFNULL(( b.total_cost - a.total_cost ) / a.total_cost ),
                'BASE') AS Diff
    FROM  fact AS b
    LEFT JOIN  fact AS a  ON  a.year  = b.year - 1
                         AND  a.month = b.month
    WHERE  b.year BETWEEN 2009+1 AND 2011+1
      AND  a.year BETWEEN 2009   AND 2011;


My sql select query during long updates on the same table. Junk or old data? What happens when select query happens simultaneously during long updates. Will I be seeing a junk data or the old data before update.

It is controlled by Transaction Isolation Levels in MySQL. Default Transaction Islation Level is Repeatable Read. With this you will see the old data until it is committed.


Materialized view in SQL Server Have a bit of glitches from the time I was using SQL Server back in 2006 and the many other databases I played with (and which I have not touched since then).
I need to create a materialized view to quickly get access to the data without overloading the DB but also to pull them what it was made static by the time the view was created.
SQL Server seems to support the materialized views with the Indexed views, but to be created these require among the other thing the following:

the WITH SCHEMABINDING construct
a clustered INDEX

Failing in that, my understanding is the view is not auto refreshing, which is probably what I need?
My goal is to get a view, that I can refresh whenever I need.
So far I created a Materialized view in the SQL 2005 instance I have access to with the following statement:
CREATE MATERIALIZED VIEW xys AS
SELECT 123
FROM X
WHERE ...
GROUP BY

Any memory refresher?

Thinking about the requirements:

"quickly get access to the data without overloading the DB"
"pull them what was made static [at] the time the view was created"

Especially the part about the data being static captured at a moment in time, I doubt a view is what you want. I think you may want something more like CDC, but that wasn't introduced until SQL Server 2008.  For SQL Server 2005, a database snapshot may meet your needs.
Dominique's answer also potentially an option.  There are multiple ways to fulfil above two requirements. Another example might be to create a stored procedure as shown below and schedule via SQL Server Agent. E.g. assuming source table:
CREATE TABLE SourceTable
(
      C1 [datatype1] PRIMARY KEY
    , C2 [datatype2]
    , C3 [datatype3]
)

and a "Snapshot table" created to match:
CREATE TABLE SnapshotOfSourceTable
(
      C1 [datatype1] PRIMARY KEY
    , C2 [datatype2]
    , C3 [datatype3]
)

then you could do something like this:
CREATE PROCEDURE dbo.UpdateSnapshotOfSourceTable
AS
BEGIN

BEGIN TRAN

BEGIN TRY
    DELETE FROM SnapshotOfSourceTable
    WHERE C1 NOT IN
    (
        SELECT C1
        FROM SourceTable
    )

    UPDATE Snap
    SET
          Snap.C2 = Source.C2
        , Snap.C3 = Source.C3
    FROM SnapshotOfSourceTable [Snap]
    INNER JOIN SourceTable [Source]
    ON Snap.C1 = Source.C1

    INSERT INTO SnapshotOfSourceTable
    (C1, C2, C3)
    SELECT C1, C2, C3
    FROM SourceTable
    WHERE C1 NOT IN
    (
        SELECT C1
        FROM SnapshotOfSourceTable
    )

    COMMIT
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
        @ErrorMessage = 'Error when attempting to update snapshot table from source: ' + ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               )
        WITH LOG;

    ROLLBACK
END CATCH
END


Need help in optimising the MySql stored procedure I have a stored procedure which is used to delete table entries. I have the input to the procedure as an array so that I can pass them as is for the delete queries [For ex: Use the IN clause to submit the array]. Even though the array items are integers, I had read that I need to pass them as string arrays. What is happening with this is, I'm not able to pass the array entries entirely to the Delete query and need to loop through them and submit individual entry to the Delete query. Could I get any suggestions/help to achieve this? Below mentioned is the procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `Delete_Proc`(IN entity_id_array varchar(100), IN mgmt_module varchar(10))
BEGIN

    -- Declare variables to hold diagnostics area information
    DECLARE errCode CHAR(5) DEFAULT '00000';
    DECLARE msgTxt TEXT;
    DECLARE rowCount INT;
    DECLARE resVal TEXT;

    DECLARE _sRemainder TEXT;
    DECLARE _Delimiter CHAR(1);
    DECLARE _nPos INT DEFAULT 1;
    DECLARE _sStr VARCHAR(100);
    DECLARE _entitId INT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION

    GET DIAGNOSTICS CONDITION 1
        errCode = RETURNED_SQLSTATE, msgTxt = MESSAGE_TEXT;

    SET _sRemainder = entity_id_array;

        WHILE CHAR_LENGTH(_sRemainder) > 0 AND _nPos > 0 DO

            SET _nPos = INSTR(_sRemainder, ',');

            IF _nPos = 0 THEN
                SET _sStr = _sRemainder;
            ELSE
                SET _sStr = LEFT(_sRemainder, _nPos - 1);
            END IF;

            IF TRIM(_sStr) != '' THEN

                Set _entitId = CAST(_sStr AS UNSIGNED);

                        DELETE user_management, user_endpoint_association, user_group_association
                                FROM user_management
                                    INNER JOIN
                                        user_endpoint_association ON user_endpoint_association.user_id = user_management.user_id
                                    INNER JOIN
                                        user_group_association ON user_group_association.user_id = user_management.user_id
                            WHERE
                                user_management.user_id = _entitId;

            END IF;

            SET _sRemainder = SUBSTRING(_sRemainder, _nPos + 1);
        END WHILE;

  -- Check whether the delete was successful
  IF errCode = '00000' THEN
    GET DIAGNOSTICS rowCount = ROW_COUNT;
    SET resVal = CONCAT('Delete succeeded, row count = ', rowCount);
  ELSE
    SET resVal = CONCAT('Delete failed, error = ', errCode,', message = ', msgTxt);
  END IF;
  -- Say what happened
  SELECT resVal;

END

=======================================================================

Here is the output of Explain Delete:

=========================================================================

Here is the output after adding the indexes:

So, your problem is, that you want to give a string like 1,3,5,7 as parameter to the procedure to actually do a query like delete from foo where id in (proc_parameter), right?

The problem with this is, that the query becomes this: delete from foo where id in ('1,3,5,7'). Then an implicit conversion from string to integer happens and the only entry that gets deleted is the one with id 1.

The most convenient solution would be, that the query with the replaced parameter wouldn't have those single-quotes. You can do this with prepared statements. The procedure would then look something like this:

DELIMITER $$
CREATE PROCEDURE sp_del(IN my_array VARCHAR(100))
BEGIN
    SET @sql := CONCAT('DELETE FROM foo WHERE id IN (', my_array, ');');
    SELECT @sql AS the_fancy_query_I_will_execute; -- debugging, not needed ;)
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

and called with

CALL sp_del('1,3,5,7');

But that all being said, why don't you just solve this on application level instead? That's what I would do!


Disk I/O Utilization at 95% My disk I/O utilization is at 95% at my MySQL server. What are steps I could take to fix this? It is a Ubuntu VP with 16 GB Memory and 8 cores.

Here is a screen-shot of the load:

We had 2 problems:

1) We had one mysql query that used three joins. Turns out this function was crashing Mysql. We rewrote this query to use 4 mysql queries without joins and that solved that problem. (Bit of a hot-fix, we will probably rewrite the function so it is possible to cache it).

2) We were experiencing around 99.9 % I/O wait when we only used 10% cache Mysql only using 10% of cache . We tried to edit the mysql config (quoted at the bottom). This helped a lot but did not fix the problem. It turned out another user on the shared server was causing 99.8% I/O spikes. After contacting our server provider they moving the server to another partition and the problem was fixed.

table_open_cache = 1024
sort_buffer_size = 4M
read_buffer_size = 128k
query_cache_size= 128M
query_cache_type = 1
tmp_table_size = 64M
thread_cache_size = 20
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_file_per_table innodb_file_format = Barracuda


"merge" two tables -from different databases- together? Well say I have a table running on a database at localhost (donor database A), and a receiving table running at another server externally (database B) With table creation (on both sides) like:
CREATE TABLE public.my_table (
    id integer NOT NULL,
    key text NOT NULL UNIQUE,
    description text,
    PRIMARY KEY (id)
);

CREATE SEQUENCE public.my_table_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.my_table ALTER COLUMN id SET DEFAULT nextval('public.language_dictionary_id_seq'::regclass)

Now I wish to merge from database A into database B. However only merging should happen on keys that are not existing on database B, and id should be recalculated (obviously to not have duplicates).
Basically as if each row from database A is insert into database B using:
INSERT INTO public.my_table (key, description)
VALUES (...)
ON CONFLICT DO NOTHING

In MS SQL there is a MERGE statement that meets your requirements.
PostgreSQL has the same.
Read more: https://www.postgresql.org/message-id/attachment/23520/sql-merge.html
It allows full control about conditions when to add a row, update a row or delete a row in the target db. I have frequently used it. But it is not a high performance operation if used with a lot of rows.
Of course you need to take care for a direct connection between the servers running the databases.
Sample:
MERGE CustomerAccount CA
USING (SELECT CustomerId, TransactionValue,
   FROM Transactions
   WHERE TransactionId > 35345678) AS T
ON T.CustomerId = CA.CustomerId
WHEN MATCHED
  UPDATE SET Balance = Balance - TransactionValue
WHEN NOT MATCHED
  INSERT (CustomerId, Balance)
  VALUES (T.CustomerId, T.TransactionValue)
;


Should I keep pg_xlog on the same disk as data if using disk snapshotting? We're running on EBS volumes on EC2.  We're interested in leveraging
EBS snapshotting for backups.  However, does this mean we'd need to
ensure our pg_xlog is on the same EBS volume as our data?

(I believe) the usual reasoning for separating pg_xlog onto a separate
volume is for performance.  However, if they are on different volumes,
the snapshots may be out of sync.

Having the transaction log (pg_xlog) on separate storage can increase write performance on most RDBMS platforms. If yours is a (concurrent) write heavy application, you may see a performance benefit.

If the snapshots are not synchronous however, you could lose data from uncomitted transactions in the event of an unclean restart. I suppose in certain situations a pg_resetxlog could be required after a failure.

You could look into WAL archiving, or even techniques similar to ZFS snapshotting if EBS snapshots have the same effective purpose.


LEFT JOIN / WHERE clause with is null and false Estimated Number Of Rows I have a table named Product and a table named ProductPropertiesCountry.

Product is the definition of a product (description, price, productId, etc)

ProductPropertiesCountry contains restrictions on the product by country, for example IsBlocked, and attributes such as Special Price.

Product
 - ProductId
 - Description
 - Price

ProductPropertiesCountry
 - ProductId (PK)
 - CountryId (PK)
 - SpecialPrice
 - IsBlocked

I'm doing a LEFT JOIN between Product and ProductPropertiesCountry with a WHERE clause like this:

SELECT P.ProductId
FROM Product P
    LEFT JOIN ProductPropertiesCountry PPC ON
        P.ProductId = PPC.Product AND PPC.CountryID = 1
WHERE (IsBlocked IS NULL OR IsBlocked = 0)

The problem is that the execution plan does not handle IsBlocked IS NULL efficiently and it gives me Estimated number of rows = 1 after that filter.  As result, the query is slower.

This is much faster:

;WITH CATALOG
AS
(
   SELECT P.ProductId, ISNULL(IsBlocked, 0) AS IsBlocked
    FROM Product P
        LEFT JOIN ProductPropertiesCountry PPC
            ON P.ProductId = PPC.Product AND PPC.CountryID = 1
)
SELECT ProductId, IsBlocked
FROM CATALOG
WHERE IsBlocked = 0;

Do you have any idea for the reason of this behavior?

Any suggestion to change it to get the right estimated number of rows in execution plan?

I'm using SQL Server 2008.

Because you need to pull a value from PPC, you can't do a proper Semi / AntiSemi Join. If you put a unique index on PPC (CountryID, Product) INCLUDE (IsBlocked, SpecialPrice), you should get a better cardinality estimate, as it'll either be there or not, and the multi-column statistics from this index will give a better answer.


How to remove identity specification for multiple tables I have 109 tables in my database. Now I am migrating to Amazon server with identity insert and I want to remove auto increment column specification for all tables. How set auto increment column i.e. Identity specification to "no" in SQL Server 2008 for all tables?

For Example, I have

CREATE TABLE dbo.tbl_acc
    (
    a1     NUMERIC (18) IDENTITY NOT NULL,
    a2     NUMERIC (18) NULL,
    a3     VARCHAR (4000) NULL,
    a4     VARCHAR (3999) NULL,

    CONSTRAINT PK_tbl_acc PRIMARY KEY (a1)
    )
GO

And now I would like to remove IDENTITY, not only for this but for all remaining tables.

There is no built-in command to remove the identity property from a column in SQL Server. You may have to create a new table, copy all the data across, and then rename the table. This can be time-consuming and awkward, especially if you have things like foreign key constraints, schema-bound functions, indexed views and so on.

That said, there is a way to avoid copying the data at least, but again this will not work in all scenarios, particularly if you have foreign keys. Example follows:

Example table and data

CREATE TABLE dbo.tbl_acc
    (
    a1     NUMERIC (18) IDENTITY NOT NULL,
    a2     NUMERIC (18) NULL,
    a3     VARCHAR (4000) NULL,
    a4     VARCHAR (3999) NULL,

    CONSTRAINT PK_tbl_acc PRIMARY KEY (a1)
    )
GO
INSERT dbo.tbl_acc
    (a2, a3, a4)
VALUES
    (1, 'a', 'b'),
    (2, 'c', 'd');

Meta data transfer and rename

-- Copy of the source table specification,
-- but without the IDENTITY property
-- Note the PRIMARY KEY constraint name
-- also has to be different
CREATE TABLE dbo.tbl_acc2
    (
    a1     NUMERIC (18) NOT NULL,
    a2     NUMERIC (18) NULL,
    a3     VARCHAR (4000) NULL,
    a4     VARCHAR (3999) NULL,

    CONSTRAINT PK_tbl_acc2 PRIMARY KEY (a1)
    )
GO
BEGIN TRANSACTION
    -- Transfer the data as a metadata operation
    -- TODO: Add error handling
    ALTER TABLE dbo.tbl_acc
    SWITCH TO dbo.tbl_acc2;

    -- Drop the source table (now empty)
    DROP TABLE dbo.tbl_acc;

    -- Rename the table
    EXECUTE sys.sp_rename
        @objname = N'dbo.tbl_acc2',
        @newname = N'tbl_acc',
        @objtype = 'OBJECT';

    -- Rename the primary key
    EXECUTE sys.sp_rename
        @objname = N'dbo.tbl_acc.PK_tbl_acc2',
        @newname = N'PK_tbl_acc',
        @objtype = 'INDEX';
COMMIT TRANSACTION;


Align values to 5 minute intervals I have values inserted with random intervals in the table. I would like to fetch the first (if exists) value in (say) 5 minutes interval. So is required to have 5 minutes column and the values column should express the first possible value before the next 5 minute range.
Using the aggregate function it is possible to get min, max, avg ... which is not the case here. I need the first possible value in the range.

Id  TSDest                  Value
1   2019-02-18 08:31:00.000 3472
2   2019-02-18 08:35:00.000 3471
3   2019-02-18 08:39:00.000 3464
4   2019-02-18 08:43:00.000 3462
5   2019-02-18 08:47:00.000 3456
6   2019-02-18 08:51:00.000 3455
7   2019-02-18 08:55:00.000 3463
8   2019-02-18 08:59:00.000 3466
9   2019-02-18 09:04:00.000 3166

I need the values aligned to a constant time interval e.g. 5 minutes
(A plus feature would be to get the nearest timestamp value in the range).

Id  TSDest                  Value
1   2019-02-18 08:30:00.000 3472
2   2019-02-18 08:35:00.000 3471

4   2019-02-18 08:40:00.000 3462
5   2019-02-18 08:45:00.000 3456
6   2019-02-18 08:50:00.000 3455
7   2019-02-18 08:55:00.000 3463

9   2019-02-18 09:00:00.000 3166

Help appreciated,

Assuming seconds always are '00.000':

WITH cte AS ( SELECT Id,
                     DATEADD(mi, -DATEPART(mi, TSDest) % 5, TSDest) TSDest,
                     Value,
                     ROW_NUMBER() OVER (PARTITION BY DATEADD(mi, -DATEPART(mi, TSDest) % 5, TSDest)
                                        ORDER BY TSDest ASC) rn
              FROM test )
SELECT Id, TSDest, Value
FROM cte
WHERE rn = 1
ORDER BY TSDest

fiddle

Added by OP

Thank you very much, this was very helpful.
BTW, because I had records with seconds and milliseconds, I had to add cast the time
to "smalldatetime" which did the trick very well,

WITH cte AS ( SELECT Id,
                         cast(DATEADD(mi, -DATEPART(mi, TSDest) % 5, TSDest) as smalldatetime) TSDest,
                         Value,
                         ROW_NUMBER() OVER (PARTITION BY cast(DATEADD(mi, -DATEPART(mi, TSDest) % 5, TSDest) as smalldatetime)
                                            ORDER BY TSDest ASC) rn
                  FROM test )
    SELECT Id, TSDest, Value
    FROM cte
    WHERE rn = 1
    ORDER BY TSDest


Why does a MySQL MEMORY-backed table take so much more space than on disk? I have a single InnoDB table on disk, with ~1,107,423 rows and taking up 45.6 MiB disk space.  The structure is:

messageid int(11)
signature varchar(255)

When I create a MEMORY-backed version of this table, it takes up 803.5 MiB of memory!

It seems reasonable there'd be some expansion: a lot of extra pointers from the B-tree index or something, but increasing by a factor of 16 seems awfully excessive!  What's the reason behind this?

VARCHARs are stored with a fixed length in memory, so your max length of 255 will be using a lot of space.  Your InnoDB disk table will be storing the rows with a dynamic length.

http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html says...

  MEMORY tables use a fixed-length row-storage format. Variable-length
  types such as VARCHAR are stored using a fixed length.


Create database design with requirement I has a small database structure with an Account and Person. I want apply the following requirement:

Account have one Person

Person do not have necessarily one Account (can be null)

When I delete an Account I don't necessarily delete Person

When I delete a Person I want delete Account

How to do that ? (one to one relationship ? on delete cascade ?)

This is good ?

Some clarification after getting some answers:

Thank for your answers.
I try to explain more the contextual meaning of Account and Person.

A Person is a contact with personal profile (Physical person, Customer, Company).

An Account is specified by an username, password, email. It's really an account for sign in on a app.

Therefore a Person can be exist without a account. A Person can only have a maximum of 1 Account. In a other direction Account have necessarily a Person.

Consequently when we remove an Account the Person is not also remove. But When I remove Person this associated Account no longer needed.

PS: I use PostgreSQL.

An Account belongsTo a Person. A person can exist without an account.

If a Person hasOne Account, then Account's primary key should also be a foreign key to People.

create table people (
  person_id int primary key,
  meta1 text,
  meta2 text
);

create table accounts (
  person_id int primary key references people(person_id) on delete cascade,
  meta1 text
);


MySQL Database Schema recommendations? Hello, all! I have several needs that I'm attempting to accomplish with my database implementation.

List number of wins / losses for a particular user
Keep track of the amount of coins a user has
Allow user to purchase items from a shop
List profitability from all gambling-based games

For my use case, I have several games the end user can play. The "Fun" category of games does not award 'coins'; however, I want users to be able to list how many they've won. All the gambling-based games allow players to earn / lose their 'coins' when playing.

Fun Games:

Rock, Paper, Scissors
Connect Four
TicTacToe

Gambling Games

Slot Machine (Slots)
Black Jack
HiLo
Coin Flip

After doing some research online, I have implemented a 'match' table to keep track of all the matches between players. With two player game sessions, two rows will be created. If I want to know how many wins a player has, I can use a sum(wins) from the match table. Additionally, from the 'gambling' match table, I can run a similar query to pull the profitability loss / gain. Is there a better way of doing this?
Additionally, if a user profited from a gambling match - I'm assuming I'd need to perform two operations:

Add new row in GamblingMatch
Update amount of coins a player has

At this moment, I'm not sure of a way to do that in one operation.

Your queries are fine.
To combine multiple row insert/updates/deletes together, use transactions.


How to index refined null values? I have table with two indexes:

latest_channel_snapshots_views_idx (view_count DESC NULLS LAST)
latest_channel_snapshots_network_views_idx (network_id, view_count DESC NULLS LAST)

What I want is to be able to sort by view_count overall, and within specific network. Postgres uses correct index for both of these cases. However, if i want to find record with most views where network_id is NULL, it uses the first index, and filters out the , thus performing really slow:

explain analyze SELECT *
FROM latest_channel_snapshots
WHERE network_id IS NULL
ORDER BY view_count DESC NULLS LAST
LIMIT 5 OFFSET 500000;

                                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=39599.04..39599.44 rows=5 width=74) (actual time=1926.271..1926.287 rows=5 loops=1)
   ->  Index Scan using latest_channel_snapshots_views_idx on latest_channel_snapshots  (cost=0.00..42001.59 rows=530336 width=74) (actual time=0.060..1899.224 rows=500005 loops=1)
         Filter: (network_id IS NULL)
         Rows Removed by Filter: 305022
 Total runtime: 1926.309 ms
(5 rows)

You have two issues here:

large offset: PG will need to scan through the provided offset number of rows and will prefer an index to do this above a table scan.
how to index the data: I think a partial index might help you here. Can you try

create index xxx_idx on latest_channel_snapshots(view_count DESC NULLS LAST) where network_id is null;

See http://www.postgresql.org/docs/9.2/static/indexes-partial.html for more information on partial indexes. In particular, example 11-2 on unbilled orders seems to apply to your case.


Why does my SQL Server database triple in size after deleting millions of records? We have two database tables ErrorLog and Audit which have both grown fairly large over the last couple of years. So in an effort to reduce the size of the database, I have to write a script to clean out all the rows older than 6 months from these two tables.

This is what I came up with:

ALTER DATABASE StudioWebTest SET RECOVERY SIMPLE;

DELETE FROM [Audit] WHERE AuditDate < DATEADD(M, -6, GETDATE());
DELETE FROM [ErrorLog] WHERE ErrorDate < DATEADD(M, -6, GETDATE());

ALTER DATABASE StudioWebTest SET RECOVERY FULL;

On my development machine (SQL Server 2008 R2) with or without changing the recovery mode the database size grows 2-3 times in size. However if I follow this up immediately with a shrink command it cuts the size of database back to half the original size (prior to the record deletion).

However if I hold off on doing the shrink for a few days, the shrink is not nearly as effective, in that while the database size is reduced, it is still almost twice the size of what it was (before deleting the records). Not knowing too much about how SQL Server uses the space it has allocated I assume that this has something to do with it using the extra space that has been freed.

This would all be okay except for one thing.  When we run this on our production test environment which uses SQL Server 2005, the shrink command does not reduce the size of the database back to half its original size.

As an alternative I have also tried using TRUNCATE in place of DELETE but this doesn't appear to make much difference.  The database still grows massively after the statements complete and I still have to shrink it for the same result.  We haven't tried this on the production test machine yet, but as the shrink command doesn't seem to be cutting it, it seems doubtful that this will yield any improvement.

Anyway, I was just wondering if someone could explain why the database grows by so much despite changing the error recovery setting?  How can this be prevented?  Or possibly suggest an alternative means for reducing the size of the database if this is not the best way to go about this.

UPDATE:

I was just doing some more tests using TRUNCATE it doesn't seem to be increasing the size now (maybe I imagined it).  I do still need to shrink the database to see a reduction in overall size though.  I might try this on the production test server.  At the end of the day I think my managers will be happy as long as the size goes down to some degree.

You are doing two giant deletes, each one occurring inside an implied transaction.  Each record being deleted is first written to the transaction log.

Because you have changed the database to be in simple mode, the transaction log will be truncated on checkpoint, but you are still causing it to grow during each delete statement.

I suggest you break up the deletes into chunks.  Inside a loop, you can delete one day at a time, starting with the oldest data, stopping at the 6 month mark.  (make sure you have indexes that support your delete criteria, create them if you have to, drop them afterwards).

This way, the checkpoints will occurr more frequently, thus keeping the size of the transaction log down to a minimum.

Truncating a table will NOT grow the database.

Also, before you issue the delete statements, make sure you find out if any triggers exist on that table.


SSIS Package Security I am writing a Package for one of my client according to his requirements. Package development is totally complete now time is to protect it with some security.
Is this possible I encrypt all the data with password so no one can open package file(.dtsx) ? and when some one want to execute package he require some other password instead of which I used to protect package file.

This is not native functionality. You can encrypt: Nothing, Sensitive items, Everything. There is no capability to assign one key for development and one for execution.

Instead, the way to restrict execution is through proper security, which is never the last item. If the SSIS package is loading a database, you ensure that only the correct user(s) can do connect/write to the database. If you store the packages in the database, deny them the ability to run the packages. If it's on the file system, set the proper ACL so they can not get to them.