MariaDB subquery cache scope and settings I am migrating an application from MySQL 5.6 to MariaDB 10.  One major new feature of MariaDB is the subquery cache.  I am trying to determine the expected effect this cache will have on our application before enabling and testing it.
Under the "Implementation" section, the knowledge base article says this:

Every subquery cache creates a temporary table where the results and all parameters are stored. It has a unique index over all parameters. First the cache is created in a MEMORY table (if doing this is impossible the cache becomes disabled for that expression). When the table grows up to the minimum of tmp_table_size and max_heap_table_size, the hit rate will be checked:

if the hit rate is really small (<0.2) the cache will be disabled.
if the hit rate is moderate (<0.7) the table will be cleaned (all records deleted) to keep the table in memory
if the hit rate is high the table will be converted to a disk table (for 5.3.0 it can only be converted to a disk table).

The first thing I'm trying to determine is what exactly it means by, "Every subquery cache."  Since it states that the caches are created in temporary tables, it implies that they must be scoped to the current connection.  So my guess is that there is one subquery_cache table created per connection.  (I doubt every query has its own cache, because then the line about the cache "growing" wouldn't make sense.)  But that's just a guess; I can't find any more details.  Perhaps these temporary tables are indeed shared between connections somehow, unlike regular temp tables.  Or perhaps there are multiple caches per connection.
Can anyone confirm the number and scope of subquery caches created?
My next question is whether there are any options for tuning the cache.  Since we are moving from an optimized MySQL application, we don't currently have any queries that make extensive use of cachable subqueries (since such queries would be very slow under MySQL).  We do use plenty of subqueries though, just not in ways that are likely to cause a lot of cache hits.  Given that, I expect that enabling the cache will simply create an overhead in temporary table creation and lookup for each subquery, as well as memory used to store the cache tables (which apparently can't be tuned separately from the main tmp_table_size and max_heap_table_size variables).  Given that I expect to find that our application as it currently exists is more efficient without the cache enabled.  The ideal though would be if it supports something like the 'DEMAND' setting for the main query cache (query_cache), which allows you to specify exactly which queries should use the cache.
Since no such features are documented anywhere, I expect they don't exist, but it would be fantastic if anyone has knowledge regarding why not, or when we might expect them to.

The cache needs to return the same results as running the subquery would, so the scope has to be limited by active transaction too.

But seeing that the cache can only be enabled or disabled as an optimizer feature and  not configured otherwise, my guess is that it actually is instantiated once for each specific cacheable subquery for a query where the optimizer decides it is worth it.

That seems to be confirmed by: "It has a unique index over all parameters." - this is not doable if you store results for more than one subquery in the same table.

So it seems to me that the cache is instantiated once for each cacheable subquery and its lifetime would be limited by the outer query execution.

Edit: I checked some source code (disclaimer: I definitely do not fully understand the code as MariaDB is quite big and complex piece of software and my knowledge of C++ is far from expert level). The cache is implemented by the Expression_cache_tmptable. The table used to store the cached results is created in the init() method which is being called from some methods in Item_cache_wrapper and AFAIK lifespan of various Items is single (outer) query execution as different types of Items keep and manage different parts of the query (sort of expression tree if I understand it correctly).

From that I deduce that one instance of the subquery cache is created for each cacheable subquery at the time query is being "prepared" for execution. It is then initialized (and the temp table created) when it is first accessed during the query execution (so only if the subquery is actually executed at least once). The condition of "<0.2" is then checked (for each subquery independently) after 200 misses (that will probably be after processing some 200+ rows of the outer query) to see if the cache has any positive effect, and it is disabled early if it does not help. Then, if the cache table grows too much, it is checked again according to the conditions listed in the manual.

So the subquery cache is not tuneable, but seems to be made as unintrusive as possible. The optimizer will ever instantiate it for subqueries which can at least theoretically use it and it will be disabled early in the query execution if it does not prove useful.

If you are still worried about it slowing your queries down, you can disable it globally by configuring the optimizer_switch and then enable it only for selected queries by calling

SET SESSION optimizer_switch='subquery_cache=on';

before the query execution and optionally turn it off afterwards.


SQL: Repair With Keycache Now my MySQL server running a REPAIR TABLE, table is almost 15gb size. Status - Repair with keycache. How I can restart REPAIR TABLE with normal (repair with sort) mode?

Your myisam_max_sort_file_size is probably too small.

Perhaps your tmpdir hasn't got the elbow room.

First, kill the repair right now.

Add these two lines to my.cnf

[mysqld]
myisam_max_sort_file_size = 80G
tmpdir=/var/lib/mysql (or some other path or mount point)

Restart mysql and do the REPAIR TABLE again.

Give it a Try !!!


Backup SQL Server with VMware The VMs I run SQL Server on are regularly backed up by the VMware backup tool. I would prefer not to use this backup solution but I have no choice but to cope with it.

The problem I've come up across is the fact that every time the VM backup us ran, SQL Server thinks it has performed a full backup of the databases to a virtual device (physical_device_name = GUID).

I can see those backups in msdb (they are copy_only backups so I don't have to worry).

Do you know why it works this way? Is VMware aware of SQL Server existence? Or maybe SQL Server has wrong data in msdb?

Can I run into any problems with that solution (other than trying to restore the DB from the virtual device)?

I would prefer not to use this backup solution but I have no choice
  but to cope with it.

You are thinking absolutely correct. Please note that VMware backups take backup using snapshot mechanism its not like backup you take using TSQL Command. Its not a good idea to rely on VM backups I strongly suggest you to start takign TSQL backup specially if database recovery model is full and you want to take transaction log backup to truncate logs

  SQL Server thinks it has performed a full backup of the databases to a virtual device (physical_device_name = GUID).

This is how SQl Server thinks with any other third party tool. If you use any othet third party tool to take backup the VIRTUAL_DEVICE would be the location where you took backup and this would be logged into errorlog.

  Do you know why it works this way? Is VMware aware of SQL Server existence? Or maybe SQL Server has wrong data in msdb?

To explain how it works you must read Information Shedding Light on VDI VSS backups

  Can I run into any problems with that solution (other than trying to restore the DB from the virtual device)?

These are just snapshot backups which are not useful when you want to do point int time recovery.


Low cost of query result in JSON? Version: PostgreSQL 10.3

PostgreSQL supports outputting JSON results. This seems to be a golden match for my application demands.

Now, before turning my application code upside down for benchmarks and tests, I did some queries with some EXPLAIN ANALYZE and almost didn't cost anything extra.

Simple example:

explain analyze
    select id, name, phonenumber, email
    from contacts
        order by name
        limit 27
        offset 2;
                       QUERY PLAN
══════════════════════════════════════════════════════════════
 Limit  (cost=62.61..62.68 rows=27 width=61) \
        (actual time=0.223..0.226 rows=27 loops=1)...

Complete explain for the plain query here.

explain analyze
    select json_agg(contacts) as contacts
    from(
        select id, name, phonenumber, email
            from contacts
                order by name
                limit 27
                offset 2
        ) as contacts;
                       QUERY PLAN
══════════════════════════════════════════════════════════════
 Aggregate  (cost=63.02..63.03 rows=1 width=32) \
            (actual time=0.272..0.272 rows=1 loops=1)...

Complete explain for the JSON query here.

I'm quite new in this (SQL performance) and it seems too good to be true. Are the following conclusions correct?:

Looking to the total execution time, the impact to output JSON is relatively low for the given example
I found that the "excluded" time for JSON aggregation is direct proportional to the size of the result set. (By increasing the limit option). However, the same proportional consequence will be on the client side if the result set will be parsed there.
With more complex queries like joins, the relative (%) overhead for JSON aggregation will be lower.

with databases the disk accesses are the slow bits, converting to JSON instead of ASCII (or postgresql wire format) does not cost much compared
to all the disk activity.


Options to include with mysqldump when taking backup of hybrid databases I'm planning on taking backups of all MySQL databases on a remote production server. The combined database size is around 2 GB. Most of the tables are InnoDB ~1.8 GB and the rest are MyISAM.

Now I've gone through most of the questions on Serverfault and DBA.Stackexchange about taking backups of hybrid databases and almost all opinions are about third party tools like Percona Xtrabackup or about converting MyISAM tables into InnoDB and then running mysqldump with --quick --single-transaction options or taking backup from a replication system.

In my case not all databases are replicated on a slave system hence I cannot run mysqldump on a slave machine, also I'll be running mysqldump nightly either via a script or as a cronjob and I know that acquiring a global read lock all the databases while taking mysqldump won't be a problem as the number of DML statements executing at that time of the night will be extremely low.

So I'm thinking about including following options with mysqldump and I'd really appreciate if someone could help verify/rectify these options.

--extended-insert  --all-databases  --add-drop-database --lock-all-tables  --log-error=file_name --skip-opt

It would make more sense in my opinion to do a backup by table instead of by database. Here is a script I use to make backups of databases that contain MyISAM and InnoDB tables.

#!/bin/bash
USER="some_user"
PASSWD="some_password"
STAMP=$(date +"%d-%m-%y-%H:%M:%S")
HOST="some_host"
db="some_database"
FILESTAMP=$(date +"%d%m%y")
FILE="/some_directory/some_file-$STAMP.sql.gz"
Port=3306

TABLE_DUMP=$(mysql -u $USER -P$PASSWD -h $HOST -Bse "SELECT table_name FROM information_schema.tables where table_schema = '$db' and engine is not null order by table_type, table_name;")
for table_name in $TABLE_DUMP
do
    table_engine=$(mysql -u $USER -P$PASSWD -h $HOST -Bse "SELECT engine FROM information_schema.tables where table_schema = '$db' and table_name = '$table_name';")
    case "$table_engine" in
        InnoDB )
            echo "$table_name InnoDB\n"
            mysqldump --skip-opt --disable-keys --create-options --single-transaction --quick --extended-insert --add-drop-table -P $Port -u $USER -P$PASSWD -h $HOST $db $table_name | gzip -9 >> $FILE
        ;;
        MyISAM)
            echo "$table_name MyISAM\n"
            mysqldump --opt -P $Port -u $USER -P$PASSWD -h $HOST $db $table_name | gzip -9 >> $FILE
    esac

    #register all table names
    skiptables="$skiptables --ignore-table=$db.$table_name"
done

# View
mysqldump -P $Port -u $USER -P$PASSWD -h $HOST $db $skiptables | gzip -9 >> $FILE


How to get particular object from jsonb array in PostgreSQL? I have a field called 'user' that holds a json array that roughly looks like this:

"user":

[{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}]

Now I want a query like:

select count from tablename where id = "1"

I'm not able to get the particular field count from an array of json objects in PostgreSQL 9.4.

It would be much more efficient to store your values in a normalized schema. That said, you can also make it work with your current setup.

Assumptions

Assuming this table definition:

CREATE TABLE tbl (tbl_id int, usr jsonb);

"user" is a reserved word and would require double quoting to be used as column name. Don't do that. I use usr instead.

Query

The query is not as trivial as the (now deleted) comments made it seem:

SELECT t.tbl_id, obj.val->>'count' AS count
FROM   tbl t
JOIN   LATERAL jsonb_array_elements(t.usr) obj(val) ON obj.val->>'_id' = '1'
WHERE  t.usr @> '[{"_id":"1"}]';

There are 3 basic steps:

1. Identify qualifying rows cheaply

WHERE  t.usr @> '[{"_id":"1"}]' identifies rows with matching object in the JSON array. The expression can use a generic GIN index on the jsonb column, or one with the more specialized operator class jsonb_path_ops:

CREATE INDEX tbl_usr_gin_idx ON tbl USING gin (usr jsonb_path_ops);

The added WHERE clause is logically redundant, but it is required to use the index. The expression in the join clause enforces the same condition but only after unnesting the array in every row qualifying so far. With index support, Postgres only processes rows that contain a qualifying object to begin with. Does not matter much with small tables, makes a huge difference with big tables and only few qualifying rows.

Related:

PostgreSQL operator uses index but underlying function does not
Index for finding an element in a JSON array

2. Identify matching object(s) in the array

Unnest with jsonb_array_elements(). (unnest() is only good for Postgres array types.) Since we are only interested in actually matching objects, filter in the join condition right away.

Related:

How to turn json array into postgres array?

3. Extract value for nested key 'count'

After qualifying objects have been extracted, simply: obj.val->>'count'.


Schema Migration for application upgrades I have an application which is running with an application schema on Oracle DB. The application needs to be upgraded to new version of Schema on Oracle DB with changes in Schema structure (Table Structure, Values, Entity Relations). Which tool/utility to use to reduce the downtime?
Currently we are migrating the complete data using sql procedures and it is taking huge time (12 Hours). We want to reduce drastically the migration time at DB level.

Edition-Based Redefinition is perfect for this. You define schema changes in a new edition, along with procedures that are used to migrate the data between the base and new edition/schema. Switching between the two is seamless and can be done with zero downtime.

There's a good demo on the Oracle Base blog.


Postgres : Using WITH .... AS insert into to return non-inserted column I'm creating an import job which uploads to a temporary table and then splits the data into two separate tables, joined by a key.

What I'm doing is more complicated than the following but this simple example demonstrates what I want to do.

So let's say I have a temporary table temp_import defined by :

create temp_import(
    row_id int,
    name varchar(32),
    email varchar(255)
)

I have two target tables defined by :

create users (
    userid int not null default nextval('tmp_uid_seq'::regclass),
    username varchar(32)
)

create emails (
    userid int,
    email varchar(255)
)

Currently I have some plpython which inserts a row at a time into users returning userid, followed by a second insert which uses the returned userid to populate emails.

loop:
insert into users (username) values 'Blah' returning userid
insert into emails (userid, email) values (userid, 'Blah2')
goto loop

This works OK but I want to extract better performance by doing set based operations.

I want to create a temporary mapping table :

create table mapping (
    row_id int,
    userid int
)

and try to do something like :

with rows as (insert into users (username)
select username from temp_import returning userid, row_id)
insert into mapping (userid, row_id)

The issue here is that I cannot return row_id as I am not selecting it for insert into the target table

If I could do this I could populate the emails table in one go:

insert into emails (userid,email)
select userid,email from temp_import t, mapping m
where t.row_id = m.row_id

Anyone know how I can return the row_id in the with rows as statement above?

Thanks in advance.

One possible solution is adding userid to the staging table you already have:

CREATE TABLE temp_import (
    row_id int,
    name varchar(32),
    email varchar(255),
    userid int not null default nextval('tmp_uid_seq'::regclass)
);

Use the same column definition above as you do in users - this way you use the same sequence, and even the nextval() will be correct after the import.

When doing the COPY, the userid will be filled.  This way you have the mapping and the staging all in the same place.  Now you have to copy the userid into the final table, too:

INSERT INTO users (userid, name)
SELECT userid, name
  FROM temp_import;

INSERT INTO mapping (userid, row_id)
SELECT userid, row_id
  FROM temp_import;

Notes:

in PostgreSQL, unlimited text and varchar have some performance advantage over the limited types.  Use the latter if you really have a limit.  With varchar(255) the case is usually 'We don't know how long it can be, but definitely not that long'.  Email addresses are usually considered as limited to 254 characters, not 255: https://www.rfc-editor.org/errata_search.php?eid=1690  Also, are you sure that nobody can have a name longer than 32 characters, including spaces, punctuation and so on?
it usually makes sense to use some consistent naming scheme in any software system, including databases.  I'm already confused a bit about the different styles used in userid and row_id.
if the import will happen repeatedly or in parallel, just use the same sequence all the time.  Sequences are transaction-safe and won't reuse any values until they wrap over (which can be mostly prevented by using bigint/bigserial).


Query for cross Multiplication I want to implement cross multiplication in SQL queries, can anyone suggest me how to do it.

For example consider the values below

Column 1       Column 2
  10              5
  3               6
  4               8

I want to display this output

Column 1            Column 2
  10                  10
  3                  30(i.e 10*3)
  4                 120(i.2 30*4)

as so on.

You need to define the column that denotes the desired order of multiplication. I'm not sure if that's what column2 is supposed to be in your example data and have just used a column called id for that purpose below.

Assuming your RDBMS supports window functions then you can use the idea from here.

The below uses some non standard TSQL functions you will need to translate into the dialect of SQL that your implementation is actually using (if you are not in fact using SQL Server)

Online Demo

SELECT CASE
         WHEN Min(Abs(Column1))
                OVER (
                  ORDER BY id) = 0 THEN 0
         ELSE CASE
                WHEN Sum(Sign(CASE
                                WHEN Column1 < 0 THEN 1
                                ELSE 0
                              END))
                       OVER (
                         ORDER BY id) % 2 = 1 THEN -1
                ELSE 1
              END * Exp(Sum(Log(Abs(NULLIF(Column1, 0))))
                          OVER (
                            ORDER BY id))
       END
FROM   YourTable


Text file report is not generating of Availability Databases other Databases during auto backup through maintenance plans in MS SQL Server 2012 I have setup Automated backup in SQL Server 2012 Enterprise Database backup through 'Maintenance Plan'. I have also configure AlwaysOn High Availability to that server .There is 5 databases in the server, where there is 3 databases has added "Availability Database" in Availability Groups and properly configured with 'secondary replica' & where 'Availability Mode' is 'Synchronous commit' between both server replica.

The 'Maintenance Plan' backup is configure with 'copy-only backup' . Because i have added 3 databases in 'Availability Database' in availability group and which 'backup type' is 'full'.
I am also attaching the screen shot here

Whenever i am not selecting 'copy-only backup' in 'Back Up Database Task' window , as per microsoft alert message ' at promtly windows shows the message like ' This backup type is not supported on a secondary replica and task will fail if the task runs on a secondary replica. For information , see http://go.microsoft.com/fwlink/?Linkid=208213.

whenever i am selecting 'For availability database,ignore Replica Priority for Backup and Backup on Primary Settings'. The message shows like that 'This backup type is not supported on a secondary replica and task will fail if the task runs on a secondary replica. Note: Ignoring the backup priority and availability group settings may results in simultaneous parallel backups if the maintenance plan is cloned on all replicas. for more information, see: http://go.microsoft.com/fwlink/?Linkid=208213

So, I have finally select the option like 'copy-only Backup' due to copy-only backup in 'secondary replica' .

Note:- The 'Availability mode' of server is 'Synchronous commit' mode ( from 'Primary Replica Server' to 'Secondary Replica Server').

The schedule backup is running successfully , and 2 backup files are generating in (H: drive).Because out of 5 database (3 database are in full recovery mode, which are added in 'Availability Databases' and other 2 database are in 'simple recovery model'. Only 'Simple Recovery model' database backup file is generating. As microsoft BOL Here and MSSQLTIPS Blog documentation Here At the time of this writing, only transaction log backups and full backups with COPY_ONLY are supported on secondary replicas. But the'text file report' is not generating.

I am attaching the 'Log file viewer' history screen shot here

I am also attaching the backup preferencesscreen window here.

Note:- The Schedule Backup location drive is 'Network Drive'. In mine case here, it is (H:) Drive. As shown in picture.

I have configure the folder path of 'Reporting and logging' of my network drive. Here it is (H: Drive). I am attaching the screen shot of that window here

My MS SQL Server 2012 Enterprise version is

  Microsoft SQL Server 2012 - 11.0.2325.0 (X64)     Jun  8 2012 18:54:43
    Copyright (c) Microsoft Corporation     Enterprise Edition: Core-based
  Licensing (64-bit) on Windows NT 6.2  (Build 9200: ) (Hypervisor)

-----------------After Edit ---------------

Microsoft(R) Server Maintenance Utility (Unicode) Version 11.0.2325 Report was generated on "Primary Replica instace ID".
Maintenance Plan: AlwaysOn Backup
Duration: 00:00:01
Status: Succeeded.
Details:
Back Up Database (Full) (Primary Replica instance ID)
Backup Database on Local server connection
Databases: Database1,Database2,Database3,Database4,Database5
Type: Full
Append existing
Task start: 2016-09-10T00:00:01.
Task end: 2016-09-10T00:00:03.
Success

Note:- Here i have edited my Server instance id as well as Database Name . Because this is my Live server database. Here only Database4 and Database5 file is generated after execution of schedule backup. Which time is showing like this in mail message. Which is

Task start: 2016-09-10T00:00:01.
Task end: 2016-09-10T00:00:03.
Success

Note:-I am not including TSQL script here.

My concern is that What i am committing the mistake here for 'text file report' generation in 'Primary Replica Server'.
Any tips or suggestions will be appreciated.

Maintenance plans are garbage so this is running exactly as designed. However you don't need to take copy only backups on the secondary, you can if you want to but there's little point.

Any errors here are spurious. If you don't like them then your options are to switch to something professional like Hallengren scripts or Minion Backup; or ignore the errors (while maintaining normal testing of your backups to ensure they're viable).


Display local variable in while loop I have a while loop that is running for days (bydesign) see related Is there a limit to how many backups can be appended to a single file?

I have a local variable that displays the loop count, when I run it in a query window I can display the count with print @counter

I am now running the while loop in a job, and I want to display the @countervalue.  After some research I tried RAISERROR (Transact-SQL) which should display in the SQL error log, but it is not working.  I read Using RAISERROR I think I have the code correct, but not sure it can be used in While loop, with a local variable.

Create the database and prep it

USE [master]
GO
Create database T_test

USE [master]
GO
ALTER DATABASE [T_test] SET RECOVERY FULL WITH NO_WAIT
GO

BACKUP DATABASE T_test to disk= N'K:\Test\T_test.bak' with noinit --Create first full
GO

Try running in a query window to test

DECLARE @counter int
SET @counter = 0
WHILE 1=1
BEGIN
BACKUP DATABASE [T_test] TO
DISK = N'K:\Test\T_Test.diff' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'T_Test-Diff Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION
SET @counter = @counter + 1;
RAISERROR(N'Count equals :%d', 16, 1, @counter );
print @counter
END

Stop after a couple of loops and check the SQL logs and the query message

The error is not in the SQL logs, but is displayed in the query message.

I don't think you can use GO 5000000 with a local variable, at least in my attempts it resets every time it is re-declared.

How can I display the local variable in a while loop, real time?

There are two extensions of RAISERROR that may help:

To write it to the error log:

DECLARE @msg NVARCHAR(1000) = N''
RAISERROR(@msg, 16, 1) WITH LOG;

To display it in the messages tab in "real time":

DECLARE @msg NVARCHAR(1000) = N''
RAISERROR(@msg, 16, 1) WITH NOWAIT;


SQL Server trace based on client TCP port I have a Windows terminal server with many different users logging in via RDP to run an application. The application makes one or more connections per user to a SQL Server 2008 R2 instance. All users access the same database using the same SQL login. I would like to be able to trace a particular user's SQL session, but I have not found a way to determine which SQL session(s) belongs to which user. However, I am able to determine the source TCP port each instance of the application is using.

Is there a way to trace a SQL session based on the client's TCP port?

Is there a way to trace a SQL session based on the client's TCP port?

Yes. You can query sys.dm_exec_connections to identify a session from the client's TCP port (column client_tcp_port).

For example:

SELECT DEC.session_id
FROM sys.dm_exec_connections AS DEC
WHERE DEC.client_net_address = '192.168.0.100'
AND DEC.client_tcp_port = 63465;


PostgresSQL - cumulative sum grouped by values Have this query returning right values where c.tiporifiuto = 0
WITH data AS (
    SELECT TIME_BUCKET_GAPFILL('1 day', c.dataora) as giorno
         , SUM(c.peso) as totale
    FROM conferimenti_001_1d AS c
    WHERE c.idcomune = 3
      AND c.dataora >= '2020-06-01'
      AND c.dataora <= '2020-06-12'
      AND c.tiporifiuto = 0
    GROUP BY giorno
    ORDER BY giorno ASC
) SELECT giorno, SUM(totale) OVER (ORDER BY giorno ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  FROM data

returning
       giorno        |   sum
---------------------+---------
2020-06-01 00:00:00 |   24.09
2020-06-02 00:00:00 |   48.05
2020-06-03 00:00:00 |   88.96
2020-06-04 00:00:00 |  183.78

Need same query but grouping ALSO for tiporifiuto, expected results should be
       giorno        |   sum   |  tiporifiuto
---------------------+------------------------
2020-06-01 00:00:00 |   24.09  |  0
2020-06-01 00:00:00 |   36.09  |  2
2020-06-01 00:00:00 |   13.09  |  3
2020-06-02 00:00:00 |   48.05  |  0
2020-06-02 00:00:00 |   72.18  |  2
2020-06-02 00:00:00 |   26.18  |  3
2020-06-03 00:00:00 |   xx.05  |  0
2020-06-03 00:00:00 |   xx.96  |  2
2020-06-03 00:00:00 |   xx.78  |  3

help pls
UPDATE
Source data is a materialized view
CREATE MATERIALIZED VIEW public.conferimenti_001_1d
TABLESPACE pg_default
AS
   SELECT time_bucket('1 day'::interval, c.dataora) AS dataora,
   sum(c.peso) AS peso,
   c.tiporifiuto,
   c.idcomune
   FROM conferimenti c
   GROUP BY (time_bucket('1 day'::interval, c.dataora)), c.idcomune,
   c.tiporifiuto
WITH DATA;

SOLVED WITH
WITH data AS (
    SELECT TIME_BUCKET_GAPFILL('1 day', c.dataora) as giorno, SUM(c.peso) as totale, c.tiporifiuto
    FROM conferimenti_001_1d AS c
    WHERE c.idcomune = 3 AND c.dataora >= '2020-06-01' AND c.dataora <= '2020-06-12'
    GROUP BY giorno, c.tiporifiuto
    ORDER BY giorno ASC, c.tiporifiuto ASC
)
SELECT giorno, SUM(totale) OVER (PARTITION BY tiporifiuto ORDER BY giorno ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as totale, tiporifiuto
FROM data


Evaluation edition expired I have a client that has a recently expired SQL Server 2008 R2 Enterprise Evaluation Edition.  I've been tasked to get it licensed and running SQL Server 2008 R2 Standard.  How can I achieve this?

You have to install SQL Server 2008 r2 Standard edition side by side (if you want to do it on the same server) and then move the database using backup/restore method.

Below is an outline of what needs to be done (if you want to do it on the same server without installing a side by side SQL 2008R2 instance).

backup all database on Enterprise Edition & script out the logins as well.
uninstall Enterprise Edition
install Standard Edition
Patch it to latest SP
restore databases
Create logins and map orphan users (if any).

Also, Unless you're using Enterprise-only features, you should be able to use detach and attach method from Enterprise to Standard.

Refer to answer by Jonathan here.

Also, refer to this approach with scripts... BUT WITH EXTREME CAUTION !! ... it uses detach/attach method and if you are using any enterprise features (which wont work in Standard edition) then the attach wont work.

Also, refer to Version and Edition Upgrades Matrix.


MySQL: No. of Connections I have a medium sized database (7 GB) with around 200 concurrent users. I am getting some database lag issues, suddenly my node-mysql client freezes during selects and inserts.

As a process of troubleshooting I checked SHOW STATUS on the DB. Everything seemed to be okay, but the connections attribute has a value of 262050.

I want to understand if this number is okay or if the figure is exorbitant?

Server Status Variables

  Connections

  The number of connection attempts (successful or not) to the MySQL server.

I.e. it is a counter which can increase only. Each attempt to connect to server, both successful and falling, increase it. Each connection, both existing and already broken, is taken into account. So this variable value can be used only for to calculate the average number of connections per some time period.

  Threads_connected

  The number of currently open connections.

It is the amount of real external connections in any state. Do not include inner server services connections (sheduler connection, for example).

  Threads_running

  The number of threads that are not sleeping.

It is the amount of real connections not in sleeping state, including inner server services connections (sheduler connection, for example).

You can see a correlation between last 2 variables while compare the output of the commands

SHOW GLOBAL STATUS
    WHERE Variable_name IN ( /* 'Connections', */
                            'Threads_connected',
                            'Threads_running');

and

SHOW PROCESSLIST;


How to solve error "ORA:01-006 :bind variable does not exist" when working with bind variables? I have a table with below structure and data :
create table TEST_TABLE
(
  item_number NUMBER,
  item_name   VARCHAR2(50),
  item_col    VARCHAR2(50),
  item_pol    VARCHAR2(50)
)

Sample data:
item_number |  item_name|  item_col |  item_pol
------------------------------------------------
     1      |   blue    |     b     |   c
     2      |   red     |     d     |   a
     3      |   black   |     e     |   a
     4      |   yellow  |     d     |   b

This is my sample procedure with in which I'm trying to use bind variables .
create or replace procedure test_bind_variable(res         out sys_refcursor,
                                               item_number varchar2,
                                               item_name   varchar2,
                                               item_col    varchar2,
                                               item_pol    varchar2) is

  qry varchar2(8000);

begin

  qry := 'select * from test_table where 1=1 ';

  if (item_number is not null) then
    qry := qry || ' and item_number = :1  ';
  end if;

  if (item_name is not null) then
    qry := qry || ' and item_name = :2 ';
  end if;

  if (item_col is not null) then
    qry := qry || ' and item_col= :3 ';
  end if;

  if (item_pol is not null) then
    qry := qry || ' and item_pol = :4 ';
  end if;

  dbms_output.put_line(qry);

  open res for  qry
    using item_number, item_name, item_col, item_pol;

end;

The problem is that when all Input parameters have a values , the procedure works properly , without any error , but when only one or two parameters have value , I receive this error : ORA-01006: bind variable does not exist,. How can I solve this problem? Some parameters might have value and some may not .
Thanks in advance

You can completely avoid dynamic SQL (you also should never name PL/SQL variables the same as table columns):
create or replace procedure test_bind_variable(res         out sys_refcursor,
                                               p_item_number varchar2,
                                               p_item_name   varchar2,
                                               p_item_col    varchar2,
                                               p_item_pol    varchar2) is

  qry varchar2(8000);

begin

  open res for select * from test_table where
    (p_item_number is null or p_item_number = item_number) and
    (p_item_name is null or p_item_name = item_name) and
    (p_item_col is null or p_item_col = item_col) and
    (p_item_pol is null or p_item_pol = item_pol)

end;

PS. Make sure you take the operator precedence into account, unlike me.


Updating BLOB Columns I have table blobtable which has a BLOB column blobcol.
A user updated the table using a command similar to this:

UPDATE BLOBTABLE
SET BLOBCOL=BFILENAME('directory/subdirectory', 'filename.txt')
WHERE ROWID='ROWIDVALUE';

Note that the file filename.txt existed in the directory before this update was performed.

Now, the user is saying that when they select from blobtable (using PL/SQL developer), blobcol contains "Value Error" for this row.  Normally, blobcol contains the value "BFILE".  Out of curiosity, I tried selecting the same record in SQL*Plus and I get the following value for blobcol:

bfilename(directory/subdirectory', 'filename.txt')

I'm not very familiar with BLOBs and how they work, so my questions are:

Did the user update the blobcol properly?
What could cause the "Value Error" and how can this be corrected?

That is not how you load a BLOB field. BFILENAME returns a BFILE locator, and BFILE data is not stored in the database, but in files outside of but accessible to the database.

The above command would not even succeed if BLOBCOL is really of BLOB type, you would get the below error:

ORA-00932: inconsistent datatypes: expected BLOB got FILE

Another mistake is that BFILENAME expects the name of a directory database object, not a directory path on the filesystem.

If you want to update a BLOB field with the contents of a file, this is how you do that:

CREATE DIRECTORY MY_DIR AS '/directory/subdirectory';

DECLARE
  src_bfile BFILE := BFILENAME('MY_DIR', 'filename.txt');
  dest_blob BLOB;
BEGIN
  SELECT BLOBCOL into dest_blob FROM BLOBTABLE WHERE ROWID = 'ROWIDVALUE';

  DBMS_LOB.OPEN(src_bfile, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_blob,
                         SRC_LOB  => src_bfile,
                         AMOUNT   => DBMS_LOB.GETLENGTH(src_bfile) );
  DBMS_LOB.CLOSE(src_lob);

  COMMIT;
END;
/

As to what caused the Value Error message, we do not know your table structure nor the exact method how it was loaded, but it was done with an inappropriate method, I think.

Finally, if your file really is a plain text file (just guessing because of .txt), then you could store that in a CLOB instead of BLOB.


Mysql query - Event to delete rows by its value need help here

I want a query to delete all rows in which column value is older than today.
One of the columns is 'check_in' (values in timestamp format) and I want the query to identify the row that has values where the timestamp is older than today (midnight oriented ex: 1588809600 ) and delete them.

Try this but select all rows :

DELETE FROM events WHERE timestamp < (NOW() - INTERVAL 1 DAY)

Maybe something like this but it give me sintax erros :

DELETE FROM events WHERE check_in = timestamp < (NOW() - INTERVAL 1 DAY)

Basically, i want to create an event that every day delete rows where check_in value in older than today.

Can you help?

To fix the syntax error:

DELETE FROM events WHERE check_in < (NOW() - INTERVAL 1 DAY)

That won't land on midnight.  Instead change to:

DELETE FROM events WHERE check_in < CURDATE() - INTERVAL 1 DAY

Give writing the EVENT a try.

Be sure to do (once):

SET event_scheduler = ON;


Understanding Simple MongoDB Replication Works I've been using MongoDB (3.2.9) replication for years and barely know the detail how it works. My Stack is Rails 4.2.5 with Mongoid 5.1.3. I've 3 database servers, let's call them A (Primary), B (Secondary) & C (Secondary). My Mongoid configuration as follow:

production:
    clients:
      default:
        database: db_production
        hosts:
          - A:27017
          - B:27017
          - C:27017
        options:
          pool_size: 10
          timeout: 60
          replica_set: my_replset
    options:
      raise_not_found_error: false

What I thought for years, server A is only for write and B+C for read only operation. But when I check on the doc lately (https://docs.mongodb.com/v3.2/replication/), it show that server A is doing write & read while B & C get the copy from A.

So my questions is: How to make sure if B & C serving a 'read' operation to any request? I'm afraid all the requests hitting the server A while B & C are exists just for backup failover.

thanks for any feedback

You can play with read preference modes.
Basically this will instruct your application to read from secondary members of the replica set.
The default behaviour is to read from primary.
Be aware that if you use asynchronous replication, data in the secondary may not reflect the most recent write operations.

More details here:
https://docs.mongodb.com/manual/core/read-preference/


SQLite: How do I use the sum function The code I am using is here
CREATE TABLE timecard (
   id INTEGER PRIMARY KEY autoincrement,
   clock_in REAL not null,
   clock_out REAL,
   lunch integer,
   pay_type TEXT
);
INSERT INTO timecard VALUES(1,julianday("2021-02-15 08:00"),julianday("2021-02-15 16:00"),0,"HOLIDAY");
INSERT INTO timecard VALUES(2,julianday("2021-02-16 07:23"),julianday("2021-02-16 16:49"),1,"WORK");
INSERT INTO timecard VALUES(3,julianday("2021-02-17 07:16"),julianday("2021-02-17 16:05"),1,"WORK");
INSERT INTO timecard VALUES(4,julianday("2021-02-18 07:54"),julianday("2021-02-18 15:48"),1,"WORK");

.header on
.mode column

SELECT strftime('%W',clock_in) AS 'weekofyear',
       time(clock_in) as "IN",
       time(clock_out) as "OUT",
       CASE when lunch = 1 then round(Cast (( JulianDay(clock_out) - JulianDay(clock_in)) * 24 - .5 As REAL),2)
            when lunch = 0 then round(Cast (( JulianDay(clock_out) - JulianDay(clock_in)) * 24 As REAL),2)
       END as "total_time",
       sum("total_time")
FROM timecard
GROUP BY "weekofyear";

This is the output of the command.
weekofyear  IN          OUT         total_time  sum("total_time")
----------  ----------  ----------  ----------  -----------------
07          08:00:00    16:00:00    8.0         0.0

I am trying to get the total hours for the week of year.  Not sure as to what I am doing wrong.  I am new to the case statement in SQL and SQL in general.
Thanks for your time

You can't reference a column alias within the same SELECT clause as it was created in. In other words sum("total_time") is referencing something that doesn't exist yet. You'll either need to copy your previous CASE statement into your SUM() function call like this:
SUM
(
    CASE when lunch = 1 then round(Cast (( JulianDay(clock_out) - JulianDay(clock_in)) * 24 - .5 As REAL),2)
         when lunch = 0 then round(Cast (( JulianDay(clock_out) - JulianDay(clock_in)) * 24 As REAL),2)
    END
)

Or use something like a subquery to generate the total_time column first, then apply the SUM() function on top of that column from your subquery.


Multi-master + slaves replication in MySQL I've been playing around with MySQL and tried to create replication topology according to the following picture. Slaves are used as read servers and Masters are used as both read and write servers.

The problem is that it does not work. If there is a write made on one of the masters, the other master does not redistribute the statements/rows to its slave.

What's the problem ? I'll be grateful for any suggestions :)

PROBLEM

Looking at the diagram and seeing the red line, I immediately thought of one thing that's missing.

The root of the problem is that both masters require log-slave-updates in my.cnf. Why ?

When a master receives a binlog event from its relay logs, it cannot replicate to any other Slave if it cannot record a binlog event into its local binary logs. For a MySQL Instance to be both Master and Slave, it has to be allowed to record Slave-based binlog events into its Binary Logs so other Slaves can read the same event.

SOLUTION

All you need to do is add this to each Master's my.cnf

[mysqld]
log-slave-updates

and restart mysqld.

Give it a Try !!!

CAVEAT #1

I have discussed log-slave-updates in this situation before

Oct 09, 2013 : Multi-master and multi slave
Jan 31, 2013 : Master to Slave to Slave Configuration in MySQL
Feb 06, 2012 : Converting 3 Master + 1 Slave to 4 Master - Any advice?
Dec 17, 2011 : Mysql Master-Master Replication Topologies on >2 machines

CAVEAT #2

You need to adjust the diagram for another reason. The blue arrows need to be reversed. Why ?

In a diagram, any DB Server can have multiple arrows pointing out, but can only have one arrow coming in. This is due to a Slave only having one Master.
MySQL Replication can only run CHANGE MASTER TO from one Master.

UPDATE 2014-03-07 12:12 EST

In your last comment, you said

  What about updates between masters M1 & M2. M1 and M2 are both slaves to each other. If M1 sends update to M2. Will M2 send the same update back to M1? Will it go on forever ?

Yes, M2 will send the same update back to M1.

However, the update will not get executed again on M1. Why ?

Each binlog event and each relay log event includes the server-id of the event. This allows MySQL Replication to work as follows for a Master that is also a Slave:

Server sees relay log event
Server examines server-id of relay log event
If server-id of relay log event is the same as my server-id, do not execute
If server-id of relay log event is not the same as my server-id

Execute relay log event
If log-slave-updates is enabled, record relay log event in local binlogs

I have discussed server-id issues like this before

Jul 06, 2012 :  Screwed up replication by sharing server ids
Dec 27, 2011 : Mysql thinks Master & Slave have the same server-id
Dec 07, 2011 : Master and Slave having same id's

As long as M1, M2, S1, S2 all have unique server-ids, you should have no issues.


Can I find out which database is queried on the most? We have a number of database which all have the same schema for each customer.
I was wondering if there was a query which could select count(*),db_name from somesysview group by db_name;

From a different angle, the same query broken down by user?

You can use sys.dm_exec_sessions. This will only give you the count based on the current activity. You would need to store this historically if you wanted to trend it. Keep in mind that session ids of 50 or less are system sessions.

--By Database
select
    db_name(database_id) DatabaseName
    ,count(session_id) as Session_count
from sys.dm_exec_sessions
where
    session_id > 50
group by
    database_id

--By Login
select
    login_name
    ,count(session_id) as Session_count
from sys.dm_exec_sessions
where
    session_id > 50
group by
    login_name


MySQL - SELECT on MyISAM Table on index - Does table size (rowcount) matter? I was arguing with a coworker if SELECT * WHERE t BETWEEN ? AND ? on a MyIsam table where t is an index is significantly slower for huge tables. The hypothetically  returned dataset is always the same, but in one query makes up for .1% of the tables content, and for the other... say 90%.

The table we were speaking about is used for logging. This means the index for the time t is increased chronologically, which makes analyzing the table unneccessary.

My explanation was that other then searching the index to find the first and the last row (aka the first and the last reference to the data) the performance should be the same. It would take a bit longer to search a long index compared to a small one - but returning the data should take the same time. Is this right or am I completely off the track on this one?

Thanks!

Broadly speaking, on a MyISAM Table with a range scan, the process is:

Find the first Index result using the BTREE (inside the .MYI file) and access the row result (on the .MYD file) - Handler_read_key
Get the next result, using the index (and in the same order), until the value retrieved is larger than the one defined (multiple instances of Handler_read_next)

You can actually get this plan by observing that you get a range join type on EXPLAIN and on the Handler_* counters on SHOW SESSION STATUS.

Theoretically, the first step is O(log n) -where n is the number of records indexed (the table size)- while the second is O(m)- where m is the number of records returned. So, theoretically, a larger table will take more to return the records. Why so I say theorically? Because the O() notation can be deceitful if you do not have into account the constants. Indexes usually end up in memory, while rows (specially on MyISAM, which has not a dedicated buffer for data) can be on disk, so the difference in performance of both operations is large. Also, MyISAM has problems with large tables, so then number of levels tend not to be too large.

Let me show you an unrelated graph:

In the above graph, the full table scan (blue line) should be flat, because all rows are examined, but it is not mainly because at that point, reading and returning 16M rows is more costly than returning only 1.

So the answer is- both operations take time, which one dominates depend on the actual value of m and n, plus the state of the database in terms of speed of hardware (memory, disk) and the state of the buffers (filesystem, key buffer). In conventional usage, an index scan of a single row is a very fast operation, but it depends comparing to what, and if you have into account extreme cases, like large tables where the BTREE index doesn't fit into memory.


Database design for Magazine website I need to design a CMS based website using asp.net web form (c#) and MS SQL Server as backend database for a magazine website which will have monthly issue.

I am finding it hard to design a database that can help me fetch menus from different table for example as show in image below

Example of Database

Sample Data

MagazinePages Table
PageID  PageName        LangID  PagePositionNo  PageURL         PageInheritance
1       Home            1       10              Default.aspx    0
2       About Us        1       20              Page.aspx       0
3       PageOne         1       10              Page.aspx       2
4       PageTwo         1       20              Page.aspx       2
5       Multimedia      1       30              Page.aspx       0
6       Video           1       10              Videos.aspx     5
7       PhotoGallery    1       20              Gallery.aspx    5
8       News            1       40              News.aspx       0
9       Issues          1       50              #               0
10      Publication     1       60              Page.aspx       0
11      SpanishHome     2       10              Default.aspx    0
12      SpanisAbout Us  2       20              Page.aspx       0
------------------------------------------------------------------------------
Magazine Table
MagazineID  MagazineIssueCode   LangID  MagazineTitle       MagazineLiveIssue(CurrentIssue)
1           101                 1       Mag Title           0
2           102                 1       Mag Title           1
3           101                 2       SpanisgMag Title    0
4           102                 2       Mag Title           1
------------------------------------------------------------------------------

News Table
NewsID  NewsTitle   NewsCatID   MagazineID  Language
1       News one    100         1           1
2       News two    100         1           1
3       news three  200         1           1
4       News four   300         1           1
5       News Five   100         2           1
6       News Six    300         2           1
7       News seven  200         2           2

Problem with above approach is that I can create sub menus only if all the records are in MagazinePages based on above example I can create sub menus for  “About Us” & Multimedia how can i design my database so that i can pull data from different table such as news by category (Politics, Culture...) & Issue from Magazine Table (Issue 101, Issue 102, Issue 103....)

I am planning to use ASP Menu control for this which may not be very flexible, since i will have over 100 issue of magazine how or which multi-column menu i can use with asp.net

My database may have many flow, I would appreciate help in this regard so that i can use this database for this CMS system . Please feel free to ask question if any regarding this.

I am finding it hard to design a database that can help me fetch menus from different table for example as show in image below

To me this sounds like you are trying to make your database design match the menus in the UI layout. This might work until the UI changes. Then will you change the database structure?

It might be better off to create proper tables based on the data itself, and then design some views that are more accessible by the UI.


Where is my ORACLE_HOME Windows directory? I'm trying to find my listener.ora file to edit it, but the docs say it's in the ORACLE_HOME directory, but where is that?

I'm running 12.1.0 on Windows Server 2008

Open up a command prompt

Start -> Run -> "cmd.exe"

Start sqlplus as administrator

sqlplus / as sysdba

There's a system stored procedure called get_env for retrieving the value for ORACLE_HOME, thanks Sabdar Syed for the query.

SQL > var OHM varchar2(100);
SQL > EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;
SQL > PRINT OHM

Should return a path like this:

  C:\app\oracle_account\product\12.1.0\dbhome_1


Store arrays in table I'm not sure if this is the best SE section to ask my question. If not, I kindly ask you to help me to find out the best one.

Developing a web application in php and MySql I'm in doubt how to store a huge quantity of data from arrays.

Scenario

The application receives text messages from clients around the world. The number of clients might reach several thousands.
Each one sends few times per day a message with a text payload (json).

The json contains few objects to identify the client itself and an array of about 10 objects, in example:

{
    "id_client": "abcdefgh",
    "id_msg": 3245,
    "datetime": "20180308101130",
    "data": [
        { "temp": 20.5 },
        { "humy": 45.1 },
        { "batt": 12.0 },
        { "cnt1": 123456 },
        ...
    ]
}

I need to store this information in the database.

My thoughts

The keys of the dictionary passed in the data array are stored into a separate table, along with their description, units, etc...

The first three fields (id_client, id_msg, datetime) are required to identify exact message received. Hence, when I receive such a message, currently, I create a row in a table for each entry in the dictionary:

+-----------+--------+----------------+------+--------+
| id_client | id_msg |    datetime    | key  | value  |
+-----------+--------+----------------+------+--------+
| abcdefgh  |   3245 | 20180308101130 | temp | 20.5   |
| abcdefgh  |   3245 | 20180308101130 | humy | 40.1   |
| abcdefgh  |   3245 | 20180308101130 | batt | 12.0   |
| abcdefgh  |   3245 | 20180308101130 | cnt1 | 123456 |
+-----------+--------+----------------+------+--------+

I wondering if this is the right approach for such a situation.
I'm afraid about the number of lines: if I have 1000 clients that send a message with 10 entries 2 times per day, I will append:

1000 * 10 * 2 = 20.000 rows per day
20.000 * 30 = 600.000 rows per months
20.000 * 365 = 7.300.000 rows per year

The data should be online at least year-by-year in order to make queries and statistics.

What about this approach? Is there something better for this kind of applications?

You are duplicating the common data on multiple rows which can lead to consistency issues. Look up "normal form" for a discussion about the standard ways to deal with this and help ensure your data is consistent and efficient. Read around database normalisation for more about this.

To break that duplicated data out you could do something like:

                    Message             MessageData
Client              ==============      ================
==============      id_msg    (PK) <--- id_msg (FK) (PK)
id_client (PK) <--- id_client (FK)      key         (PK)
client_name         time_stamp          value
contact_no

You potentially have a second problem though: the message data table presented above is still a property bag or Entity-Attribute-Value (EAV) model which while flexible can be problematical in a number of ways. You'll find many discussions online (including here on DBA.SE) and in print (Bill Karwin's excellent "SQL Anitpatterns" has a relevant chapter) about the pros and cons of EAV structures so I'll not delve into more detail here.

If those array keys in your JSON example are fixed then a better layout might be:

                    Message
Client              ==============
==============      id_msg    (PK)
id_client (PK) <--- id_client (FK)
client_name         time_stamp
contact_no          temperature
                    humidity
                    battery
                    cnt1

Or if those keys are common in the data but there are potentially others that and you need to allow any key/value pair, you can use a hybrid of fixed columns and property bag:

                    Message             OtherMessageData
Client              ==============      ================
==============      id_msg    (PK) <--- id_msg (FK) (PK)
id_client (PK) <--- id_client (FK)      key         (PK)
client_name         time_stamp          value
contact_no          temperature
                    humidity
                    battery

  7.300.000 rows per year

With appropriate table and index design 7.3 million rows is not a lot at all.


text unique to the grain should be stored in fact table or dimension table? I am modeling a star schema for user reviews using the yelp data set.
Each user review has a business dimension key, user dimension key and a bunch of data associated with the review.  All of the review data is numeric except for the field where they put in their text for the review (stored in a column named text).
Does it make sense to store that text data in the fact table since it relates to the grain of the fact?  Or should it be placed in a dimension table that grows it's rows at the same rate as the fact table?

Yes it does make sense to keep the text in the fact table for two reasons :

It is the same grain as the fact, so having a dimension just for that would indeed make it grow as fast as the fact table
It is not linked to other attributes in the fact table, so it can be modeled as a Degenerate Dimension directly in the fact table (even though it usually applies more to an ID or a label).

Text won't be part of your select statement when you aggregate reviews so it shouldn't impact performance. It will be there only when showing the data at the most granular level.
If you find out you have some other low-cardinality descriptive attributes that you can't easily put in an existing dimension because they are not linked to other attributes, you can also build a junk dimension with all these unrelated attributes.


Why does SQL Server Backupset table goes back a few months, but Agent Logs go back further? I ran this script looking at the history of my backups for the past month.

select * from msdb.dbo.backupset order by backup_start_date

No problem, but if I want to go back to Jan 2020, there are no records.

So why does the Agent Log, with Log Type = 'DATABASE' go back further?

Thanks

The Agent Log is limited by the number of entries, not by a date. The default is 1000 rows, with 100 rows per job. You can see this via Right-click SQL Server Agent in SSMS -> Properties -> History.

So, if you've had fewer than that number of events since January, the history will go back further. You can change that limit in the dialog noted above.

Many DBAs will configure a job to purge backup history on a rolling month or two month basis, sometimes coordinated with how long backups are kept on local storage.


Lots of isMaster queries running I see a lot of these kind of queries running  on my primary server where all the replica set members keep checking if the Primary is Primary by running isMaster:

query: { ismaster: true }  ntoreturn: -1 ntoskip: 0

10.117.241.104:27017 <<-- 10.117.241.104:38837 480 bytes id:16f386 1504134 - 1602

reply n:1 cursorId: 0

This is causing a connection bottleneck as my primary has 28000+ connections, which keeps increasing at the rate of 8-10 per second, and takes the primary down.

How can I handle this?

Client lib provides the API for the App to access the MongoDB server. At startup, the client lib will connect to some member (based on a seed list) of the Replica set and issue a isMaster command to gather the current picture of the set (who is the primary and secondaries). After that, the client lib connect to the single primary (where it will send all DB modification request) and some number of secondaries (where it will send read-only queries). The client library will periodically re-run the isMaster command to detect if any new members have joined the set. When an existing member in the set is crashed, connections to all existing clients will be dropped and forces a resynchronization of the latest picture.

I suggest you check the mongod.log for details.


Problem with only_full_group_by I was trying to solve this HackerRank problem using MySQL and here's what I came up with:

select id, wands_property.age, min(wands.coins_needed), wands.power
from wands
join wands_property
on wands_property.code = wands.code
where wands_property.is_evil = 0
group by power, age
order by power desc, age desc;

This gives me a compilation error:

  ERROR 1055 (42000) at line 1: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'run_ctaqjwhj9bt.wands.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Now, I understand that there are multiple results for id - this was the point of the problem, having multiple wands with the same age and power but different prices - but since I've specified that I want the minimum price shouldn't I get only the id corresponding to the wand with the minimum price?

Edit: I solved it with this:

select w.id, wp.age, w.coins_needed, w.power
from wands as w
join wands_property as wp
on wp.code = w.code
where wp.is_evil = 0 and w.coins_needed = (
    select min(coins_needed)
    from wands
    join wands_property
    on wands.code = wands_property.code
    where wands.power = w.power and wands_property.age = wp.age
    group by power, age
)
order by power desc, age desc;

but I noticed something I don't quite understand: if I add

and wands.id = w.id

to the last where clause I'm getting duplicate (power and age) results with different prices instead of only the minimum one. Why is that?

Telling you directly the answer would be cheating, so I will give you just a hint- the problem is about a very very famous SQL problem, so much, that it has its own page on the MySQL manual:

https://dev.mysql.com/doc/refman/5.7/en/examples.html

And a search finds that this kind of problem has been asked on this site at least a dozen times already.

Telling you more than that would be against the spirit of the challenge.

I can explain why it errors out- When using an aggregated function, you can only select fields you have aggregated by, or other fields, using an aggregated function, but you cannot select non-aggregated-by fields, because SQL doesn't know you want the minimum one. What you want to do is to find the minimum, and then use that value to get the corresponding one on the same row, but you cannot "aggregate and get the value of another column" with such a simple query- once it gets aggregated, the other fields get "lost". In previous versions of MySQL, your query was legal, but it gave out a "random/not deterministic" id value.


How to active mariadb log? I want active log file in mariadb for check last query used in php, i find mysql config in internet like as blow:

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

or

log = /var/log/mysql/mysql.log

I added this config in /etc/mysql/my.cnf and [mysql] section and restart mariadb service.
but i didn't get any log, how to fix this?

i find answer:

[mysqld]
...
log_output = FILE
general_log = ON
general_log_file = mysql.log

mysql.log save in datadir, run SHOW VARIABLES WHERE variable_name = 'datadir' in mariadb to get datadir.

You also need one of these:

log_output = FILE

log_output = TABLE

And put them in this section:

[mysqld]

('d' as in 'daemon' as in the 'server')


SQL AlwaysOn Data Sync We are in the planning phase to implement AlwaysOn Availability Groups. What is the best practice to setup the file share for data synchronization? Network path or local machine (creating a share on one of the SQL servers)? If we put it on a file server, Would there be a performance hit reading the data from the file server? If we put it on one of the SQL server, what will happen if we lost that server? Please help. Thank you.

What is the best practice to setup the file share for data synchronization? Network path or local machine

You have to really read up on AlwaysON. AlwaysON ships only log blocks. So there is no fileshare concept.

On the windows cluster, you can set a witness as fileshare. So, the witness (disk or fileshare) is not AlwaysOn specific. It is required by the Windows Server Failover Cluster to maintain quorum during node failures. Check What exactly is a File Share Witness and when should I use one?

Some good references to get you started :

AlwaysOn Availability Groups Quiz and FAQ [Video] - by Brent Ozar.
Overview of AlwaysOn Availability Groups
Creation and Configuration of Availability Groups


How can I configure maximum number of concurrent connections WITHOUT SSMS? I'm having an issue in a production environment and I was trying to simulate it in my local SQL Server Express instance.

I used SSMS to change the "maximum number of concurrent connections" from "0" (max = 32767) to "2".

After restarting the service, I'm able to connect to it with SSMS but when I try to open Server Properties (or "New Query" window) to change the setting back to "0", SSMS crashes.

Is there any other place I can change this setting without SSMS (e.g. registry)?

This should do it:

EXEC sp_configure 'user connections', 0;
RECONFIGURE

That will set the number of connections back to the default, which is 32767.

Once you have SSMS open, disconnect from the server in the "Object Explorer" Window.  Then you should be able to create a new query window and connect to it there.  Alternately, you could use sqlcmd.exe to connect from the command-line without having SSMS open, then run the command from there.

The setting is stored in the master database, and can be seen through the following statement:

SELECT *
FROM sys.configurations c
WHERE c.name = 'user connections';

sys.configurations is actually a view, defined as:

CREATE VIEW sys.configurations AS
    SELECT configuration_id,
        name,
        value,
        minimum,
        maximum,
        value_in_use,
        description,
        is_dynamic,
        is_advanced
    FROM sys.configurations$
    WHERE is_not_use = 0

The only way to see the sys.configurations$ table is through the Dedicated Admin Connection, more commonly known as the DAC.  The DAC needs to be turned on through SQL Server Configuration Manager, since it is off by default in SQL Server Express.


Link MS Project to Access I have a large project tracking database used to track PMs, status, help needed, action items, reports etc. I keep the project schedule in a large MS-Project file. I've exhausted all efforts to have my organization invest in an MS-Project server.
How can I use the data found in my project file: Task Name, Resource, Start Date, End Date... etc as source data into my access database. Can these files be linked somehow if I put the project ID# into my project file as a foreign key? I just need to make the data available to people other than the PM. Ideas?

It looks like there is an odbc driver for project

You could try attaching access directly via a linked table.


SQL Server login not working I have a user who was added with windows authentication to a SQL Server 2008 R2 machine.

He had left the company a few months ago, at which point his AD user was disabled and moved to a different OU.
The users database logins and users were not affected.
He then returned to the company, his AD user was reactivated, and moved to the correct OU.

However all his database logins are currently giving login failures.
The issue is resolved if the following actions are taken:

Drop the existing login
Create the login again
Remap the login to the existing database users

This is an impractical method, as it means tracking down each login and recreating it.

How can I resolve this in a more practical way?
Failing a more practical solution, what is the cause for this issue?

Check the SIDs of the accounts in SQL and AD before the SQL login deletion to confirm they are indeed the same and the AD admins didn't really delete and recreate the AD user account just telling you otherwise.

Articles: http://sqlserver-help.com/2014/05/27/sql-internals-how-to-map-login-sid-to-domain-account/ and https://technet.microsoft.com/en-us/library/ff730940.aspx

There may be a way to get both from PowerShell but confirm what the AD domain and forest functional levels are and research with your version of SQL Server for known bugs -- maybe it's time to upgrade/update one or the other.


Information_Schema on oracle? I'm a SQL Server DBA, and I like to mess around with Oracle here and there.

I can see with select * from information_Schema.columns everything about a table (everything that I need now).

Is there a way to list these informations on a Oracle Schema?

The equivalent in Oracle is user_tab_columns for tables owned by the current user or all_tab_columns for tables accessible to the current user.


What does a DBA have to know about SSAS? I've seen a lot of material covering the business aspect of SSAS, but not really much about the important aspects of administration and management.

From the point of view of administering an instance of SQL Server Analysis Services, what does a working DBA have to know about SSAS to manage it correctly and efficiently?

A quick overview of SSAS for DBAs

So, you're a SQL Server DBA and you've just inherited some cubes out of the blue to manage.  A quick crash course on SSAS administration seems to be in order.

From an administrative point of view, SSAS is a fairly straightforward, if resource hungry application.  It's way simpler than a DBMS platform, although different in several ways.  Also, there are a few administrative tasks such as usage based optimisation specific to SSAS that you might have to get your head around.

Back up your configuration file

All config data lives in a file called msdmsrv.ini.  It's an XML file.  If you tinker with the config through SSMS (Connect to the server, right click on the server, select properties) you can set options that will break the server on start up.  Take a copy of msmdsrv.ini before you play with anything.

Important parameters

Memory: SSAS is a biblical memory hog.  It likes 64 bit builds if possible, and lots of memory.  The parameters 'Memory\LowMemoryLimit' and 'Memory\HighMemoryLimit' control memory usage policies.  LowMemoryLimit is not a minimum memory allocation.  It is a threshold where SSAS considers the system is low on memory and starts flushing stuff out of its caches.  HighMemoryLimit is the absolute maximum it will use.

Note that SSAS stores its data in files (lots of files - it doesn't have any mechanism equivalent to filegroups), so it makes a lot of use of O/S file system caching for those files.  Note that the default for these limits is about 65% and 80% of the machine's memory respectively, so if you want the OLAP server to coexist with a SQL Server instance you will need to turn those down so it isn't fighting over memory with the database server.

Directories:  Five parameters of interest cover this: DataDir, AllowedBrowsingFolders, BackupDir, LogDir and TempDir.  DataDir and AllowedBrowsingFolders are the most important.

AllowedBrowsingFolders affects the list of folders that the OLAP server will put its data files in.  Anything with a user interface (e.g. the deployment wizard) will restrict your options to the list in AllowedBrowsingFolders.  The value is a pipe ('|') delimited list of directories.
DataDir is the default path for files.  If you intend to partition the cube over multiple volumes you will need to configure AllowedBrowsingFolders appropriately.
LogDir is where the server places various log files including flight recorder and query logs.  The flight recorder log is used for troubleshooting and the OLAP query log is used for usage based optimisation (more on this later).
TempDir is a location for temporary files that SSAS creates while processing.  If you are processing large data volumes and having performance issues, you may benefit from shifting this off onto another volume from the data.
BackupDir is what it says on the tin.

Miscellaneous:
A few miscellaneous parameters may also be of interest.  A couple of sets that you may have to tweak are:

DefaultMaxDrillthroughRows: This limits the size of drillthrough row sets.  You may need to fiddle this to allow more.
Threads/Timeouts: You may need to adjust these.  I've never had to bother.

Those are the basics.  You may need to tweak the others for specific reasons, but you can do your homework on that.

A reference guide for the SSAS server properties can be found here.

Operations

Deployment:  You can compile a project in BIDS and get a set of files that can be deployed with the deployment wizard.  You may need to adjust file paths for partitions and a few other things.

Programmed and batched admin tasks: Commands are issued to SSAS through a web service API called XML/A.  Microsoft provides an interactive tool for issuing MDX and XML/A connands.  If you have to embed MDX in an XML/A command, watch out for the need to use XML escapes such as &amp;.  This is not an issue with the MDX editor and query tool in SSMS.

Offline jobs can be done through various SSIS cube processing tasks, a command line utility called ascmd.exe or a .Net API called AMO.  You can also get various powershell tools and suchlike.  ascmd.exe takes an XML/A file and posts it to the server.  If you have to frig the file programatically you might be better off working with little .Net tools rather than trying to manipulate XML files from a .cmd script.

The operations guide goes into this in more detail.

Security

Security on SSAS is fairly simplistic.  It has a global 'Server' role that has administrative permissions across the whole system.  Unfortunately you need 'Server' to create databases, so it's quite likely you will need to grant it to developers on any development OLAP servers you have.

Other security can only be applied to individual cube schemas.  You can grant permissions to read, process, drillthrough, writeback and suchlike on individual items to roles within a schema.  Roles on OLAP schemas can be defined within BIDS and are deployed with the cube.  AD groups or users can be assigned to those roles through SSMS.

An example of how to programatically manage role membership can be seen here.

Usage-based optimisation

As a DBA you might well get involved in this one, but first a little backgrounder about the physical storage.  SSAS works by calculating and persisting pre-built aggregates along with the base data.  If a query can be satisfied by hitting an aggregate the OLAP server will use this in preference to the base data, as the aggregate will involve much less I/O and therefore it will be quicker to retrieve the data.

However, you have to work out which aggregates to calculate (i.e. what combinations of dimension attributes to generate rollups for).  BIDS has a tool that will take a guess at this and generate some for you.  Some tools, such as BIDS helper will also allow you to manually edit the aggregates.

Usage based optimisation works by taking a log of the actual queries issued to the server, and then using that log to work out a set of aggregates that would be optimal for those queries.  As a DBA, you may set up OLAP query logging to capture this data, and then run the optimisation on the cube.  A blurb about setting up the query log can be found here.

The tool for doing this is called the 'Usage based optimisation wizard.'  This lives in SSMS, and can be found by opening the partition int the explorer and selecting 'Usage Based Optimisation' off the right-click menu.

The performance guide goes into tuning in more detail.

MDX

MDX looks a bit like SQL but works very differently.  A treatise on 'MDX for SQL programmers' is an entire topic in its own right.  I suggest reading through some of the tutorials on it and/or getting a book on it.  Plus, the friendly folks here at dba.se can help with questions if you have any.

MDX doesn't have any concept of filtering rows.  The language has a whole lot of set operations to work out what to display on the various axes of the query1, plus 'SELECT'.  You can use with statements that look a bit like CTEs to define measures and sets.

Some introductory MDX programming resources can be found here and here (this one is quite old and very long winded).  There are also a few good books on the subject; this SO question has quite a bit of fan-out to SSAS resources.

1 Although it does have non-empty operators that restrict the results to combinations that actually have data.  Most MDX queries consist of defining what slices to show on the axes, and non-empty operators will be necessary to avoid queries returning combinatoric level quantities of empty cells.


What affects the speed of a count(*) besides the number of records I have a query like

SELECT COUNT(*) FROM Foo Where Bar = 1 AND Baz = 2

the table has 12934600 records of which 1000001 match that predicate

Looking at the query statistics I see

  (1 row(s) affected) Table 'Foo'. Scan count 1, logical reads 1863,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

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

Looking at the query plan 80% of the time is spent in a index seek on the Bar and Baz columns and 20% on aggregating the result.

is there a way to speed this up and if so how? I would also like to understand which hardware components have a big influence here CPU or Disk IO or bus speed.

Bear in mind that the query plan shows an estimate of cost which is not the same as time.  The estimated cost values are a unitless aggregation of CPU, memory, and IO, not how long each operation takes to execute.  Also bear in mind that the cost values are estimates even in an "actual" execution plan.

The biggest bottleneck for this and most other SQL Server operations is disk IO.  Your example STATISTICS looks to be from a second run since there are no physical reads, though, so you see a higher CPU which is used for the aggregation/sorting.

You may be able to speed this particular query up with a filtered index, if your WHERE predicate is consistent (i.e. always Bar = 1 AND Baz = 2).

Otherwise I'm not aware of a way to speed it up beyond something more drastic like an indexed view.


SQL Server Index recomended but already there (DMVs) I am using the DMV views to get a list of index recommendations and then I am reviewing each of them to make sure they make sense and don't overlap etc.

Some examples are

Index ON X ( Y ) and Table X already has the INDEX Y.  So far this is for non clustered indexes and the indexes have been defragmented.

SQL Server 2008 R2, database is fairly busy and lots of index recommendations are coming up.  Right now it's hard to see the wood for the trees; I am just trying to get some low hanging fruit to start with.

I have checked the last access times and they're all recent.

SQL Server version I'm using is:

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64) Jul 9 2014 16:04:25 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2  (Build 9200: )

SQL Server 2008 and 2008 R2 have known issues in the index-recommendation code that were fixed in SQL Server 2012.

Having said that, index recommendations should always be looked at carefully to see if there is (1) an existing index that might be slightly modified to handle it's current load along with the new load, and (2) if any changes can be made to the query producing the recommendation so it uses an existing index.

Paul Randal wrote about the bug in SQL Server 2008 here.


Keep Mongo DB set up up and running with two data center always There is a scenario where we have 5 member replica set across two data centers.
Data center 1 : 3 Members
Data center 2 : 2 Members
Situation is if DC 1 goes down DC 2 becomes read only but  we need to keep it ready to accept writes also.
Is there any possibility for same using one extra member like hidden node or two arbiter.
We do not have third data center.

Thanks
Virendra Agarwal

This is a configuration will work with using Virtual Host Names for config and arbiters. When you bring up the hidden ones you only need to change DNS entries to point to DC2 servers.


SQL Server AAGs/Possible Endpoint contention/Synchronous replication to WAN Secondary In the interest of saving some SQL Server licensing moolah ($$$), we’re going down the path of extreme consolidation. We’re trying to shoehorn as many databases as possible into a SQL Server 2016 Always On Availability Group “Cluster” which, the way I see it is really one active server at a time with the data replicated to other standby secondaries. To me, the word “cluster” (as Microsoft SQL Server does it) is more of a marketing term and a dangerous one at that because it gives the impression of a big bad server when it is not.

So on this new environment we have, among other things, a default instance of SQL Server hosting about 20 AAGs, and a second instance to host Sharepoint as well. Overall a total of potentially 70 databases in these AAGs (including classic hits like MS SCCM which uses service broker and is highly transactional+did I mention, Sharepoint) on a 4 core SQL Server VM w/72GB of RAM. One of the things I noticed is that each AAG communicates with a single HADR endpoint on the destination instance so you end up with 20 AAGs communicating with a SINGLE endpoint.

Will all of those AAGs sending log data to a single endpoint (single tcpip port) create a point of contention?

The design was to use synchronous replication EVEN to the remote site which I immediately saw as a potential problem but I was told that there have been significant improvements in SQL Server 2016 specific to AAG replication. Your thoughts here will be appreciated as well.

Will all of this work? Is it fair for me to wear a Nostradamus hat and predict a potential Cluster-Mesa or should I play “Let it Be” like the Beatles and keep on trucking...

Few things that I learned from having a similar setup with my old company. We had 53 AOAGs with many DBs in each AG.

TCP Endpoint will not be a contention. (remember that you can have only one endpoint per server instance).Even with our setup having MPLS network (NY and LD) and a dedicated VPN between (NY and HK), we ran the above setup and we were fine.
with such amount of DBs, your availability group DMV queries might be slow. So make sure you use temp tables.
Since you are running SQL Server 2016, Always On Availability Groups are Turbocharged along with better AlwaysOn Parallel Compression / Improved Algorithms
Make sure you offload read work by utilizing Round-robin load balancing in readable secondaries.
Make sure you are on latest 2016 SP2 service pack.

As always, do some benchmark and testing and have a baseline to compare. This will give you confidence in presenting to management that the consolidation project indeed helped !


Sync data near real time between 2 databases on 2 different servers One of my clients asked me to find a solution that can sync the 2 databases in near real time. Basically, they try to pull the data through linked server from server A through server B, but since server A goes down they can't pull the data as the database is not available, so they need some near real time data available to be queried even if the source is not available for sometime. Also, something that I forgot to add the database should always be on server A.

SQL version - SQL 2012 SP3 (both server A and server B)
RAM - 32 GB

So essentially, Server A's database is the production DB, and Server B's database is used as a "read-only" copy - no changes go through server B.  If Server A goes down, then Server B is up-to-date, or almost so.  However, Server B is not intended to become the live production DB at that point - it's just available to reference the data until Server A comes back up

It sounds like you want to make a copy of the data from server 'A' on server 'B' that can be can be used as reference data.  The data on server 'B' should be refreshed from server 'A' as often as possible.  Presumably, you don't need to make changes to the data once it is on server 'B'.

I'd recommend using replication to update server 'B' on a schedule; if server 'A' is down, no replication will happen, however once server 'A' comes back online, server 'B' will be brought up to date at the next scheduled replication event.


Odd inconsistent behavior of SQL Server "IN" clause Query 1:

select count(UserID)
from users
where UserID IN (select UserID from otherTable)

Returns: 178

Query 2:: (simply changed IN to NOT IN)

select count(UserID)
from users
where UserID NOT IN (select UserID from otherTable)

Returns: 0

Query 3: (remove the IN-clause completely)

select count(UserID)
from users

Returns: 1047123 (over a million)

What? I can't... Even...

The only explanation I have is my database in inconsistent/corrupted, I have to run DBCC CHECKDB or something.

PS. UserID is a primary key, so - no dupes.

NOT IN will not evaluate to true when one or more NULL values are returned by the subquery.

Use NOT EXISTS to avoid this common gotcha. See this answer for more information and links to more detailed information.


What should be the command for following role creation requirement? I need to create a role where the assigned user can access only the 'AdventureWorksDB" where he will have execute permission to all tables,views and stored procedures in the database. I wrote the below script.

USE AdventureWorks2012;
GO
CREATE USER test1 FOR LOGIN test1;
GO

CREATE ROLE db_executor

GRANT EXECUTE TO db_executor

EXEC sp_addrolemember 'db_executor', 'test1'

But he also needs SQLAgentOperatorRole permission for SQL Agent. But If I am to grant this I have to give him the access to msdb to be able to do this. But if I give the user , access to msdb user can access other databases as well. What could I do to make the user access only one database ad have full access to SQL Agent?

I am not sure of any other alternative but i believe user needs to be there in MSDB or should have the admin rights to execute or perform any required action on the job. There is a connect item about this as well, SQL2005 SQLAgent MSDB security roles

Or you can use an option as mentioned here:

  You could create a stored procedure that runs the job. You can use
  with execute as owner to run the stored procedure as the database
  owner. That way, the users themselves don't need permissons on
  sp_start_job

Also, if feasible, you can read Launching SQL Agent Job from stored procedure not on [msdb] database, or, How Necessary is SET TRUSTWORTHY ON?


tempdb not releasing space I have 1 tempdb mdf file, and 7 ndf files.  They all grew to 63 GB, and filled up the 500 GBdrive (the .mdf and .ndf files say 99% free space.).
I ran sp_who2 'Active' and there is no active transaction using tempdb.  Why can't I release space?
I cannot restart this server till Friday, which is our designated maintenance window. What should I do until then?
Update
So I got permission to reboot the server. However it did not free up the space...tempdb files are still big. What do I do?

They all grew to 63 gb, and filled up the 500 gb drive
Why can't I release space?

To be clear, the files won't "shrink" back to their previous size on their own while the system is running.  They should go back to their configured size after a reboot (since tempdb is recreated when the database engine starts up).
You can check the configured size vs. actual size using these queries from Andy Mallon's blog:
--Check sys.master_files in master
USE master
GO
SELECT DataFileName = mf.name,
       FileSizeMB  = mf.size*8/1024
FROM sys.master_files AS mf
WHERE mf.database_id = db_id('tempdb')
ORDER BY mf.type, file_id;
GO
--Check sys.database_files in tempdb
USE tempdb
GO
SELECT DataFileName = dbf.name,
       FileSizeMB  = dbf.size*8/1024
FROM sys.database_files AS dbf
ORDER BY dbf.type, dbf.file_id;

The values from sys.master_files are the ones that tempdb should return to after a reboot.  If those values have increased to 63 GB, then someone has configured them that way.
If you choose the shrink the files, be sure to heed Andy's suggestion:

If you do shrink a tempdb file, check the sys.master_files metadata before & after to ensure you leave it in the ideal state. Use ALTER DATABASE...MODIFY FILE to repair the metadata for the next restart

Once you have the immediate size problem addressed, you really need to:

identify the root cause (what queries are causing tempdb) to grow, see the Q&A here: How to find the SQL statements that caused tempdb growth?
set the tempdb data files to a fixed size, disabling autogrowth
preferably move them to their own drive (if they are not already on a dedicated drive), to prevent this from causing user databases or the server from being as impacted


Rebuilding Index - sort in temp db What is the significance of SORT_IN_TEMPDB when working with indexes on a table of significant size (20GB plus)?

Where else does sorting take place?

It depends.

The options are -

Sort in the destination file group (normally PRIMARY on the DB the index is on)
Sort in TempDB

If your TempDB is on a different disk array and not active, you could see a performance increase.  There are also space considerations.

There's a pretty good article on MSDN about this topic here.


Fetch records of postgres and store it in different table , if one column is null I have a table employee(id,empname,salary,dob)
   If the record of id and empname is null..whole record should be removed in different table...

For example.....

 id  | empname |salary | year
_____________________________
101  |  ishi   |100000 |   null
null | aarush  |200000 |   4
102  | rkr     |2000000|   6
104  |null     |300000 |   3
105  |null     |400000 |   3

If Id column has null value..the record should be fetch and placed in different table say null_table ...same case for empname column
the rest record should be placed in different table say success_table

The output should be

success_table:

 id  | empname |salary | year
_____________________________
101  |  ishi   |100000 |   null
102  | rkr     |2000000|   6

null_table:

 id  | empname |salary | year
_____________________________
null | aarush  |200000 |   4
104  |null     |300000 |   3
105  |null     |400000 |   3

I am new to postgres...Any suggestions are appreciated...

Well, normally id is used to implement primary key which is not nullable... But I guess your problem is that you're getting dirty data from another source and try to sort it out ?

You could either design correctly (with all due constraints) your table and use a tool like pg_loader to load your data. It will create a error file with all the lines that don't fit.

Or you can import all your data into a junk table (which is the approach you seem to have chosen) and perform SQL on top of that to sort out your data.

You can try something like that:

/*Processing "nulls" that can be a problem*/
with nullable_rows as
(
  delete
  from input_table
  where id is null
    or  emp is null
    /*Add any other condition here*/
  returning *
)
insert into null_table
(id, emp, salary, year)
select * from nullable_rows;

/*Processing correct rows*/
with correct_rows as
(
  delete
  from input_table
  where id is not null
    and emp is not null
    /*Add any other condition here*/
  returning *
)
insert into sucess_table
(id, emp, salary, year)
select * from correct_rows;

That's if you load your data out of another source.

If another source is inserting dirty data in your database, use constraints to prevent it...


Alter TEXT column to NOT NULL when there is existing NULL rows Mysql does not support default values for TEXT columns. I have a live database with NULL values

What is the proper way to change all null values to a predefined value and change the column to not null? I would like to do this in a single transaction to prevent new NULL values being inserted between updating all old NULL values and ALTERING the column.

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

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


Memory usage on select for large number of rows I am trying to dump the entire contents of a large table, from the command line using pqsl, but am running into a problem where memory usage goes up, to the point where the process is killed, before any data is even dumped.

What I don't understand is: why isn't the query returning results immediately, and completing without running out of memory?

Here is an explanation of exactly what I'm attempting:

I have a table, say:

CREATE TABLE big
(
  id integer,
  rand double precision
)

A large number of rows is inserted (50 million):

insert into big
  select generate_series(1, 50000000) AS id, random();

The query plan to select every row looks like (not surprisingly):

$ psql -d big -c "explain select * from big;"

                       QUERY PLAN
----------------------------------------------------------------
 Seq Scan on big  (cost=0.00..924326.24 rows=50000124 width=12)
(1 row)

I then attempt to dump the contents to file:

$ psql -d big -c "select * from big;" > big.dump

As I said above, this command is failing before any data is written, seemingly by taking up an ever increasing amount of memory before being killed by the OS (by "OOM killer").

Note: I understand I could use pg_dump to accomplish something similar, but in reality, my query is more complex than this - specifically, I would like to encode each row as JSON when dumping.

Some configuration details:

postgresql version = 9.3.4
work_mem = 1MB
shared_buffers = 128MB
effective_cache_size = 128MB

By default, the results are entirely buffered in memory for two reasons:

1) Unless using the -A option, output rows are aligned so the output cannot start until psql knows the maximum length of each column, which implies visiting every row (which also takes a significant time in addition to lots of memory).

2) Unless specifying a FETCH_COUNT, psql uses the synchronous PQexec function directly on the query, which buffers the entire resultset. But when setting a FETCH_COUNT, it will use a cursor-based method with successive fetch calls and freeing or reusing the client-side buffer every FETCH_COUNT rows.

So a big resultset should be fetched by a command like:

psql -A -t --variable="FETCH_COUNT=10000" \
     -c "select columns from bigtable" \
     > output-file

With FETCH_COUNT reduced if the rows are very large and it still eats too much memory.

The -t stands for --tuples-only, which suppresses the output of headers and footers.


RMAN restore of primary database - impact on standby database I've just restored an 11g primary DB that was destroyed. Restored the controlfile from tape and performed a full restore, recovery, and open resetlogs. Looks fine now. How will my physical standby (using dataguard) handle this? When I 'show configuration' in dgmgrl I see:
Configuration - DG_Config

  Protection Mode: MaxPerformance
  Databases:
    TESTDB11  - Primary database
    TESTDB11A - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

How can I repair this standby after a restore of the primary?
Thanks.

You should be able to use Flashback Database to get the standby back to the point in time (i.e. SCN) to which the primary was restored (Flashback Database is a requirement for automated failover).  If you can do this, then Data Guard should be able to wire things back up together again.
Have a look at these:

http://www.br8dba.com/resetlogs-on-primary-where-standby-in-place/
http://oracle-help.com/dataguard/flashback-database-after-resetlogs-in-data-guard/

If in doubt, though, or if you don't have Flashback Database enabled, then trash the standby and recreate it from scratch.


extended events rpc_completed duration does not matching with rpc_starting Does rpc_completed duration (micro_sec) = rpc_starting.timestamp - rpc_completed.timestamp?
In my case, I saw a big difference between them. Duration is only few millsec, where rpc_starting.timestamp - rpc_completed.timestamp is 6 - 10 sec.

I am on enterprise 2016 sp2-cu14, the duration field is in microsecond. I did similar thing for catching sp_statement_starting and sp_statement_completed, sp_batch_starting/sp_batch_completed inside rpc_starting and rpc_completed. it shows the same situation. My question is "which one tell me how much time the query actually took? Is it Duration in sp_statement_completed or the difference between starting and completed?"

My question is "which one tell me how much time the query actually took? Is it Duration in sp_statement_completed or the difference between starting and completed?"

"Duration" in the sp_statement_completed event is how long the query actually took at the server.  It doesn't include the time spent sending results to the client (usually, see here for details).
I'm not 100% sure why the Extended Events timestamp column shows such a large discrepancy between the "starting" and "completed" events, but that's not where you want to focus your attention.