Why is a Star Schema more normalized than a 3NF Schema? According to Oracle's documentation, third normal form schemas "may require less data-transformation than more normalized schemas such as star schemas".

I'm confused, I thought 3NF is the most normalized among common schema models, then goes snowflake schema and at last star schema.

As @ypercube stated this seems to be a typo and should be changed to "more de-normalized schemas". One of the following paragraphsinthe Oracle manual states:

Snowflake schemas normalize dimensions to eliminate redundancy. That is, the dimension data has been grouped into multiple tables instead of one large table. For example, a product dimension table in a star schema might be normalized into a products table, a product_category table, and a product_manufacturer table in a snowflake schema.

This product dimension table of the star schema described here is not in third normal form  but are results of joining (denormalize) some tables of the snowflake schema.

I found aricles on the web that describe why a star schema is not in 3rd normal form link link


Postgres slave max_connections = master's max_connections? I am using binary replication for Postgres.

Is it true that the max_connections has to be the SAME for both the master and slave in postgresql.conf?

Can they be different?

The documentation has something to say about it [emphasis mine - ed.]:

  When running a standby server, you must set this parameter to the same
  or higher value than on the master server. Otherwise, queries will not
  be allowed in the standby server.

There are a number of reasons to try to keep your hardware and configuration as close to identical as possible.  So I think a strict reading of your question is no.  But there are good reasons to keep it the same.

The big reasons have to do with two things: base backups and fail over.  If you are taking base backups from the slave then the base backup also includes the configuration.  If you later restore it on the master, then you have configuration changes taking place solely because you restored a backup.  In a disaster recovery situation, the last thing you want is to be trying to track down "what changed when we restored the backup?!"

The second thing is that this applies in more or less the same way when failing over.  Because failing back is still a bit of a pain, you need to be assuming your slave can handle the master's load for a bit of time while you set things up.  Keeping the configuration the same is a good way to avoid unpleasant surprises there.


Slow storage writes: how many milliseconds exactly? SQL Server 2008 R2 We're running SQL Server 2008 R2.

I'm using SPBlitz and it's saying that I have slow storage writes on Drive H. Blitz defines these as "averaging over 20ms."

But of course, someone asked "How much over?"

Is there a query I can use to determine what the average storage writes is in milliseconds?

Thanks,
mh

You can query sys.dm_io_virtual_file_stats to find out the read and write latency.

You can use the script from here or here - this is snip from my project of designing a comprehensive SQL Server health checker (this is something internal for now, but planning to opensource the code soon - as a thank you to sql server community !).

Also, read this blog post by  Erin Stellato - What Virtual Filestats Do, and Do Not, Tell You About I/O Latency. This blog post has a script that you can use as well.


How to catch erroneous SQL operator We have 3-rd party application. In one of the reports it gives us error message:
Microsoft OLE DB Provider for SQL Server: Subquery returned more than 1 value. This is not permitted...
I need to find which statement causes this error.
The report duration is quite long - about several hours.
I want to setup extended event session to catch this error.
Am I right?
Are there any other ways (except xEvents and trace) to grab this erroneous SQL statement ?

In this instance, if you wanted to use XE you can grab it through "sqlserver.error_reported". The error number for a subquery returning more than one value is 512 - so we can filter on ONLY error numbers of 512.

XE Session Code:

CREATE EVENT SESSION [Error512Tracking] ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_hostname,sqlserver.database_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([error_number]=(512)))
ADD TARGET package0.event_file(SET filename=N'C:\ExtendedEvents\Error512Tracking.xel',metadatafile=N'C:\ExtendedEvents\Error512Tracking.xem')


ORA-00257: archiver error I ran into this error earlier today and requested that the extra logs be purged from the server. It was done but it still claims that there isn't any room. Is there a way to notify the server that it has more room than it thinks?

You should purge the archived logs with RMAN:

RMAN> delete archivelog <delete conditions here>;
RMAN> crosscheck archivelog all;

or alternatively all of them:

RMAN>delete archivelog all;

You can check your recovery area usage like this:

SQL>select * from v$flash_recovery_area_usage;

If you encountered that problem though, you will very likely encounter it again. Purging the archived logs is a quick fix.

Whoever maintains the server needs to verify:

That archived logs backups are performed and are successful
That obsolete archived logs are deleted
That obsolete backups are deleted.
If the backups are performed regularly and obsolete archived logs deleted, maybe the database usage has increased and filling the fast recovery area faster than the archived logs get backed up. Increasing the db_recovery_file_dest_size parameter to a higher value to fit the business needs may be required.


Does PostgreSQL benefit from more cores or faster cores for READ heavy applications? I have been trying to figure out how to go about configuration of a PostgreSQL instance that will have 64GB ram (which is far larger than the database) and if I should opt for more cores or faster cores.(Assuming passmark total is within 15-20% difference)

Some extra details:
The most intensive READ will be JSONB through lots of UNION. The OS in this case is CentOS 6.

Memory related settings

You've already addressed the key bottleneck for read heavy applications, that is, having sufficient RAM for caching.  Just make sure you've set appropriately high values for shared_bufferes, work_mem, maintenance_work_mem, and effective_cache_size within your postgresql.conf file.

Actually, there's a litany of good info in this DBA.SE thread.

Also, here's a link to the Postgres Docs about these settings, amongst others.

Processor choice

As for choice of processors, it is important to keep in mind that Postgres is not capable of using more than one CPU per database process.  If there will be many concurrently connected users, a multi-core CPU can spread out the user queries amongst the cores, but cannot parallelize on a per-query basis. Effectively, you will only get to truly leverage a benefit from multiple cores when there are multiple connected users.

I guess, to answer succinctly, that if you have only one (or a small number) of users, favor faster cores, whereas if you'll have many concurrent users, you could benefit from a little of both.

Refer to this DBA.SE thread which covers some of this info.

If you are going to have many concurrently users, I would suggest you also look in to connection pooling.


DBCC SHRINKFILE - Necessary to run more than once to achieve shrink? We have a 2008R2 production DB in full recovery mode.

To produce a minimised dev version, for SQL Server 2014, the following steps are run:

RESTORE DATABASE BigDB FROM  DISK = N'C:\Share\BigDB.bak' WITH  FILE = 1,
MOVE N'BigDB_Data' TO N'C:\Database\BigDB_Data',
MOVE N'BigDB_Audit]' TO N'C:\Database\BigDB_Audit',
MOVE N'BigDB_Log' TO N'C:\Database\BigDB_log.LDF',  NOUNLOAD,  REPLACE,  STATS = 5

alter database BigDB set recovery simple with NO_WAIT
alter database BigDB set compatibility_level = 120

go

-- {truncate / delete statements.. with no additional 'go'}

DBCC SHRINKFILE (N'[BigDB_Audit]' , 500)
DBCC SHRINKFILE (N'BigDB_Data' , 1000)
DBCC SHRINKFILE (N'BigDB_Log' , 1000)
DBCC SHRINKFILE (N'BigDB_Data' , 1000) -- Without this repeat, file remains same
DBCC SHRINKFILE (N'BigDB_Log' , 1000) -- Without this repeat, file remains same

I've observed that without the repeat of the shrinkfile on the main data file, and the log, one or both does not actually shrink.

Can this behaviour be expected, and if so why?

There is likely not enough time passed between your truncates/deletes and the shrink operations. While I droned on and on and on about why you shouldn't shrink and what it means to have a too big log file in another answer, here's a situation where it makes sense to shrink.

I would suggest either building a pause in between the truncates and deletes or simply add a CHECKPOINT command within that database between the deletes and shrink. That will likely give you the desired results without the second shrink.


Join tables and pivot tbl_orders

order_id, qty
123, 2
124, 1
125, 3
126, 1
127, 1
128, 1
129, 4

tbl_score

order_id, name, created
123, product 1, 2018-06-01
124, product 1, 2018-06-01
125, product 1, 2018-06-02
126, product 3, 2018-06-02
127, product 2, 2018-06-03
128, product 3, 2018-06-03
129, product 3, 2018-06-03

Required output

             2018-06-01       2018-06-02        2018-06-03
product 1        3                  3               0
product 2        0                  0               1
product 3        0                  1               5

The following query produces the required output for 2018-06-01 but takes around 1 min and 45 seconds to complete on the live database. If I add more dates using case it takes more time. Can someone help me fine tune it or perhaps suggest another solution?

select s.name,
  sum( case when date(s.created) = '2018-06-01' then o.qty else 0 end ) as 'June 1'
from tbl_score s  inner join tbl_order o
 on s.order_id=o.order_id group by s.name;

Pivoting should be broken into two steps.

Step 1:  Create a SELECT with the data not yet pivoted:

SELECT product, date, qty
    FROM ...

In your case, that query is likely to have a JOIN.  (I am uncomfortable with which table date is in, but that is a separate issue.)

Step 2:  Do the Pivoting.

SELECT product,
       ( ... ) AS '2018-06-01',
       ( ... ) AS '2018-06-02',
       ...
    FROM ( the-above-SELECT ) AS x

There could be issues with step 2 -- As shown, it requires a fixed number of columns.  If you have a variable number of columns, you cannot build it this way, but need to construct the SELECT dynamically, then 'execute' it.

More discussion:  http://mysql.rjweb.org/doc.php/pivot


Error Creating Foreign Key in Oracle I have 2 tables:

PRODUCTS

PREFIX      SUFFIX      ATTR1   ATTR2
-----------------------------------------------------
001         AAA         50      100
001         BBB         20      300
010         ACC         50      500
100         ABC         10      200
100         CBA         30      200
100         BBB         10      100

OPTIONS

PREFIX      SUFFIX      OPT_CODE
----------------------------------------
001         AAA         A1
001         AAA         A2
001         AAA         A3
001         BBB         A2
001         BBB         A1
001         BBB         A3
100         ABC         A1
100         CBA         B1
100         BBB         A2

Created with this DDL:

create table products (
    prefix VARCHAR2(10),
    suffix VARCHAR2(10),
    attr1  VARCHAR2(100),
    attr2  VARCHAR2(100)
    )

create table options (
    prefix VARCHAR2(10),
    suffix VARCHAR2(10),
    opt_code VARCHAR2(2)
    )

I have created a primary key in the products table:

alter table products add constraint products_pk primary key (prefix, suffix);

and a primary key in the options table:

alter table options add constraint options_pk primary key (prefix, suffix, opt_code);

When I try and add a foreign key to the options table like so:

ALTER TABLE options ADD CONSTRAINT fk_options_products FOREIGN KEY (prefix, suffix)
   REFERENCES products (prefix, suffix);

I am getting this error:

SQL Error: ORA-02298: cannot validate (PRODDB.FK_OPTIONS_PRODUCTS) - parent keys not found
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
*Cause:    an alter table validating constraint failed because the table has
           child records.
*Action:   Obvious

I validated that all the child records (in OPTIONS) have entries in the parent table (PRODUCTS) but not all the entries in PRODUCTS have child entries in OPTIONS.

My understanding of that error - you get that when you don't have a parent record for a child entry.

Am I missing something here?

We can debug this.  Create your FK with this code below, which includes the EXCEPTIONS clause.  It will identify the specific rows in your child table options responsible for the errors.

CREATE TABLE fk_exceptions (
    row_id     ROWID
  , owner      VARCHAR2(30)
  , table_name VARCHAR2(30)
  , constraint VARCHAR2(30)
);

ALTER TABLE options
  ADD CONSTRAINT fk_options_products
  FOREIGN KEY (prefix, suffix)
  REFERENCES products (prefix, suffix)
  EXCEPTIONS INTO fk_exceptions;

You'll still get your ORA-02298, but the evidence will be harvested and deposited into fk_exceptions table.  Your culprit row(s) will be revealed by:

SELECT * FROM options WHERE rowid IN (SELECT row_id FROM fk_exceptions);


Identify unattached File Size in SQL Server From a similar post on this forum, I have modified the below code as per comments and able to get the orphaned files in the drives. The code is :

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
GO

if object_id('tempdb.dbo.#os_files') is not null
    drop table #os_files
create table #os_files([filename] varchar(2000))

insert into #os_files exec xp_cmdshell 'DIR D:\SQL04\*.mdf /b /s'
insert into #os_files exec xp_cmdshell 'DIR D:\SQL04\*.ldf /b /s'

delete from #os_files where filename is null

update #os_files set filename=rtrim(filename)

select
    os.filename as orphaned_files
from
    #os_files os
    left outer join master.sys.master_files db on rtrim(db.physical_name) = os.filename
where
    db.database_id is null

EXEC sp_configure 'xp_cmdshell', 0;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
GO

Is it possible in any way to modify the above code and get the size of the files in the output apart from physical file names?
Also is there any other way to modify the above code to search for files other than .mdf and .ldf files in the content volumes, where the database and log files reside. In our environment we have folders where mdf and ldf files are kept separately. Hence, the need.

The output that I'm expecting should be like this:

PHYSICAL_FILENAME      SIZE
        file1             s1
        file2             s2

Kindly look into this issue and give your suggestion.
Thanks in advance.

Basically if you manage the contents of the files you select from and include the file size, it should not be too hard to do.  (But there are issues.)  Here is a greatly edited script that may help you.

    -- Gather the full names of Database Files
SET NOCOUNT ON
create table #os_files(fullfilename varchar(2000));

insert into #os_files exec xp_cmdshell 'DIR D:\DATA\*.mdf /B /S'
insert into #os_files exec xp_cmdshell 'DIR D:\DATA\*.ndf /B /S'
insert into #os_files exec xp_cmdshell 'DIR F:\LOGS\*.ldf /B /S'

create table #os_files_dir(dirstring varchar(2000));

-- Gather Filename (without path) and Filesize without commas (/-C).
-- Exclude system files (/A-S) and directories (/A-D)
insert into #os_files_dir exec xp_cmdshell 'DIR D:\DATA\*.mdf /-C /TC /A-S /A-D /S';
insert into #os_files_dir exec xp_cmdshell 'DIR D:\DATA\*.ndf /-C /TC /A-S /A-D /S';
insert into #os_files_dir exec xp_cmdshell 'DIR F:\LOGS\*.ldf /-C /TC /A-S /A-D /S';

create table #os_files_dirclean(cleanstring varchar(2000));
-- Clean out non-file rows: Exclude NULL columns,
--  all the information rows that begin with a ' '
--  and handle a 'File Not Found' row, if it appears.
insert into #os_files_dirclean
select * from #os_files_dir
WHERE SUBSTRING(dirstring,1,1) LIKE '[0-9]';

-- Select the database files that need to be cleaned up.
SELECT os.fullfilename AS PHYSICAL_FILENAME,
       CAST(SUBSTRING(fd.cleanstring,21,18) AS BIGINT) AS SIZE
FROM #os_files os JOIN #os_files_dirclean fd
       ON os.fullfilename like '%' + SUBSTRING(fd.cleanstring,40,2000)
    LEFT OUTER JOIN master.sys.master_files db
       ON RTRIM(db.physical_name) LIKE '%'+ RTRIM(os.fullfilename)
WHERE db.database_id IS NULL

-- Clean up the temp tables
drop table #os_files;
drop table #os_files_dir;
drop table #os_files_dirclean;

Issues that make this a fragile solution:

In order to get the full path name, used the `DIR ... /B /S'
In order to get the file name and size, used the `DIR ... /-C /TC /A-S /A-D /S'
Since in step 2 the first column of a row with a file is the date and time of the file. Used this to filter out all rows that do not start with a numeric.
Since step 1 and step 2 return different name formants, this results in needing to join a full path name with a file name only.
A file name can appear more than once on a server, if it is in a different location. So there may be some ambiguity in the join if that case arises on one of your servers.

If you need other file types, you will need to include them also in loading the table. One SQL Server example is ".ndf" used when adding files to the database beyond the primary .mdf. I included this in the dataset.

For a server I tested with I got the following.  Note that RLFJUNK is not really a database file, it is just named like one:

PHYSICAL_NAME                       SIZE
D:\DATA\Administration_Data.MDF     524288000
D:\DATA\EventHorizon_Data.MDF       629145600
D:\DATA\RLFJUNK.mdf                 19
F:\LOGS\Administration_log.LDF      107216896
F:\LOGS\EventHorizon_Log.LDF        2202009600

Future changes to the OS or the format of the DIR command could easily disrupt the script.  Still, hope it helps you for the time being.

This is more like something that you might want to find or develop a bit of CLR code to get the directory information without so much maneuvering.


Can I restore database A to database B but keep B's permissions? I have a live db A and a test db B. The live db is backup'd every day. And I use A's backup file to restore to B to keep in sync. But then B's permission setup is also overwritten by A's. Can I only restore A's data but keep B's permission?

A SQL Server database backup is the whole database, users included.  You can script out the permissions prior and reapply them as part of your process while blowing out the other permissions.  You could make your db schema work on both environments.  You could also just do data exports and load up data.  Tools like RedGate Data Compare could also help keep the data in sync.

If you want to give more info about your environment we can help guide you to the best option but you have several.


Oracle Case when problem I Have a table like fallowing

    work_id      group    effort
   -----------   -----   --------
       1           A        10
       1           B        5
       2           A        7
       2           C        20
       2           B         3

I need a case when query that returns like fallowing

   work_id       1.group   effort1   2.Group  effort2    3.group  effort3
   -----------   -----     --------  -------  ------    -------  ------
       1           A        10        B        5
       2           A        7         C        20        B         3

goups are grouped based on another parameter that in another table. But when i write my query like

case when <condition1> then group end as 1.group
case when <condition2> then group end as 2.group
case when <condition4> then group end as 3.group

...(Same for efforts as well)

it returns

work_id       1.group   effort   2.Group  effort    3.group  effort
-----------   -----   --------   -------  ------    -------  ------
    1           A        10        null     null      null     null
    1           null     null     B        5
    2           A        7         null     null    null     null
    2           null     null                          C      20
    2           null     null      null      null     B         3

Can anyone help?

You are trying to write a PIVOT query.  If you're trying to use a CASE to do so (which implies that you are not on 11g where you can simply use the PIVOT operator), you need to do an aggregate

SELECT work_id,
       MAX( CASE WHEN <<condition1>> then group else null end ) as "1.group",
       MAX( CASE WHEN <<condition2>> then group else null end ) as "2.group",
       MAX( CASE WHEN <<condition3>> then group else null end ) as "3.group",
       MAX( CASE WHEN <<condition1>> then effort else null end ) as "1.effort",
       MAX( CASE WHEN <<condition2>> then effort else null end ) as "2.effort",
       MAX( CASE WHEN <<condition3>> then effort else null end ) as "3.effort"
  FROM <<table name>>
 GROUP BY work_id

Of course, you presumably don't actually have a column named GROUP in your table since that is a reserved word.


Mongo 2.6 vs. 3.2 - data folder size I just migrated from Mongo 2.6 (Single node) to 3.2 (ReplicaSet).

In the old server the Mongo's data folder was a total of ~12GB, but looks like in the new servers (master and its slave) it's just ~2.5GB - make sense? Has Mongo enhanced the way they're holding data?

The size I mention above, ~12GB includes indexes (that have significant size in my DB, almost double it).

You've seen the right behaviour since the default storage engine in 3.2 is WiredTiger(WT). The Wired Tiger provides up to 80% of the data compression to your data.

Just to verify please note db stats before migrating and then after migrating the dbs to 3.2 to ensure you have got your data back.


How to calculate the space I need for the indexes? CREATE TABLE info(
home CHAR(10) CHARACTER SET latin1 NOT NULL,
id TINYINT UNSIGNED NOT NULL,
galaxy TINYINT UNSIGNED NOT NULL,
galaxy_pos INT UNSIGNED NOT NULL,
events TEXT NOT NULL,
UNIQUE KEY(galaxy, galaxy_pos, id), PRIMARY KEY(home, id)) ENGINE = INNODB

10+1+1+4+2 = 18 bytes + TEXT, are needed for the data. How to calculate the space I need for the indexes?

For the data record add:

6 bytes transaction id
7 bytes rollback pointer
5 bytes extra bytes header
2 bytes - for length of TEXT field (however if TEXT values longer than 7k overhead will be bigger)

Index record stores indexed fields + primary key. So its length will be

5 bytes - extra bytes
indexed fields length
primary key length (in your case it's 1+4+1 bytes)

Take into account an InnoDB page may be filled up to 15/16 i.e. 15k of data+headers.

Each page has 120 bytes in headers.

Plus take into account space that is used by non-leaf pages. It's not huge, but still.


Invalid column name 'Locked'. So we have this software that queries for content of Catalogue:

10:55:36,193 [ INFO] [rest.CatalogProvider:232] GET_CATALOG: {
  Id: '*',
  Title: '*',
  Clip: '*',
  IniDate: '*',
  EndDate: '*',
  DurMin: '*',
  DurMax: '*',
  Store: '*',
  FilterConfig: '*',
  FilterNoStore: '*',
  InfoStore: '*',
  Channel: '*',
  Group: '*',
  Type: '*',
  Validated: '*',
  SourceType: '*',
  NumResults: '*',
  StartIndex: '*',
  Order: '*'
}

This terminates in error:

10:55:36,285 [ WARN] [util.JDBCExceptionReporter:233] SQL Error: 207, SQLState: S0001
10:55:36,285 [ERROR] [util.JDBCExceptionReporter:234] Invalid column name 'Locked'.
10:55:36,286 [ERROR] [service.CatalogService:434] getCatalog Exception could not execute query

However I am quite sure we do not have a column name Locked. Where is this 'locked' coming from?

The server is SQL Server 2008.

The name locked is either referenced in the query being sent to SQL Server or referenced in a programmability object (e.g. trigger) being executed. I suggest you start with a trace of batch and rpc starting events to rule out the first case. The name lockedsuggests it may be used for application level concurrency so perhaps there's an ORM lay or similar injecting it.

The trace can be done using the legacy Profiler/SQL Trace or an Extended Events Trace. Below is an example XE trace that filters by application name to reduce the noise or you could run an unfiltered one on a test box.

CREATE EVENT SESSION [batch_and_rpc_completed] ON SERVER
ADD EVENT sqlserver.rpc_starting(
    ACTION(package0.event_sequence,sqlserver.session_id)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'YourApplication'))),
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(package0.event_sequence,sqlserver.session_id)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'YourApplication')))
ADD TARGET package0.event_file(SET filename=N'batch_and_rpc_completed')
WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF);

EDIT:

Here's another trace that captures only error_reported events. This is a SQL 2014+ version gleaned from the Brent Ozar's SQL 2012 version of this trace @sp_BlitzErik referenced in his comment.

CREATE EVENT SESSION failed_queries ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.database_id, sqlserver.username)
    WHERE ([severity]> 10))
ADD TARGET package0.asynchronous_file_target(SET filename = 'failed_queries' ,max_file_size = 5,max_rollover_files = 5)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);


Schema for yearly data models I am developing a school management system.

Most data will not be reused after the end of the school year.

I am contemplating using schemas to separate data in various school years, however I am afraid I don't know very much about schemas.

Would it be a good idea to use a new schema for each school year to store data (see below), rather than adding a year field and using a where clause for all queries?

schema_year1.attendance_of_staff
schema_year2.attendance_of_staff

Maybe, you should use staff, attendance_staff, and school.

staff table
==========
id,
fullname,
birthday
...

school Table
==============
id,
name,
founded,
number_of_staff
...

attendance_staff table
=======================
id,
staff_id,
school_id,
attended_date,
...


remote postgresql 9.3 login using pgadmin3 and a public network server to connect to LAN-only machine I want to connect to a machine running postgresql 9.3. The machine itself is not reachable via a direct IP but is in a LAN with another machine with a public IP address.

My current approach is:

changing postgresql.conf:

/..../
listen_addresses = '*'
port = 5432
/..../

adding to pg_hba.conf:

/..../
host    all        all        192.168.59.1/24       trust
/..../

(of cause restarting the postgres service)

connecting to the public server via:

ssh -L 9999:192.168.59.130:5432 user@theip

which works just fine (I am logged in without any error message)

But when I try to connect pgadmin3 using:

name: randomName
Host: 127.0.0.1
Port: 9999
Service:
Maintenance DB: postgres
Username: postgres
Pasword: *********
Colour:
Group: Servers

I get:
Server doesn't listen ....

checking sudo netstat -tulpn shows:

tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      8860/postgres
tcp6       0      0 :::5432                 :::*                    LISTEN      8860/postgres

Therefore postgres seems to listen for LAN addresses though I get a "do not listen".

How do I fix that?

changing

host    all        all        192.168.59.1/24       trust

to

host    all        all        samenet       trust

did the trick for me. Though the difference between the two stays a secret for me.


MySQL sudden restart I have a strange issue with my MySQL server. It suddenly restarts. It's a rare but very big problem for me.

This server is running as Master in Master-Slave replication.
I'm using MySQL 5.6.16-1+sury.org~precise+1-log

In syslog I see next info:

Jun  4 10:53:32 localhost kernel: [18821099.786264] init: mysql main process (25937) terminated with status 1
Jun  4 10:53:32 localhost kernel: [18821099.786329] init: mysql main process ended, respawning
Jun  4 10:53:32 localhost kernel: [18821100.064250] type=1400 audit(1433404412.925:26): apparmor="STATUS" operation="profile_replace" name="/usr/sbin/mysqld" pid=9957 comm="apparmor_parser"

I see no errors in mysql common error files. I use Ubuntu 12.04 LTS, there is 32G of RAM on the server. Here is the config

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql

query_cache_type = 1
innodb_buffer_pool_size = 9500M
max_heap_table_size = 268435456
tmp_table_size = 268435456
query_cache_size = 512M
sort_buffer_size = 8388608

innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = 'O_DIRECT'
skip-external-locking
low-priority-updates
skip-name-resolve

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
max_connections        = 600
query_cache_limit       = 4M
log_bin                 = /var/lib/mysql/mysql-bin
binlog_format           = MIXED
expire_logs_days        = 7
max_binlog_size         = 100M
binlog-do-db            = db1
binlog-do-db            = db2
binlog-do-db            = db3

server-id               = 1355115082

max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[isamchk]
key_buffer              = 16M

Could you please help me with this problem or give some advice to think about?

Thank you in advance!

Today I found new logs at /var/log/upstart

So, terminating with signal 1 was the after effect but not the cause.

I saw next information

It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 5016582 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

So, I do assume, that the reason was in memory excess. Thus, I decreased max_threads (actually, it's the max_connections parameter) to have a security delta of memory.


PostgreSQL 9.4.6 under RHEL 7 - Extensive logfile because of log_statement_stats - Can't turn off I got a newly installed database (PG 9.4.6 / RHEL 7) which logfile grows rapidly.

Most entries are like

2016-03-03 10:33:29.259 CET [56868] user@db STATEMENT:  SELECT field1 AS a1, field2 AS a2, field3 AS a3, field4 AS a4, field5 AS a5, field6 AS a6, MSISDN AS a7, field8 AS a8, RETRY_COUNT AS a9 FROM table WHERE ((field3 <= $1) AND (field8 IS NULL)) ORDER BY field3 ASC LIMIT $2 OFFSET $3
2016-03-03 10:33:29.259 CET [56868] user@db LOG:  EXECUTE MESSAGE STATISTICS
2016-03-03 10:33:29.259 CET [56868] user@db DETAIL:  ! system usage stats:
        !       0.000006 elapsed 0.000000 user 0.000000 system sec
        !       [0.526408 user 0.112125 sys total]
        !       0/0 [0/0] filesystem blocks in/out
        !       0/0 [0/1281] page faults/reclaims, 0 [0] swaps
        !       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
        !       0/0 [3516/0] voluntary/involuntary context switches

I read through the documentation and found out, that it should be one of the log_*_stats parameters and despite the missing config entry in the postgresql.conf and postgresql.auto.conf

> egrep "log_.*stats" postgresql.*conf
postgresql.conf:#log_parser_stats = off
postgresql.conf:#log_planner_stats = off
postgresql.conf:#log_executor_stats = off
postgresql.conf:#log_statement_stats = off

the running instance shows that the parameter is set to "on".

> echo "show log_statement_stats" | psql
 log_statement_stats
---------------------
 on

I tried to change it by applying an "ALTER SYSTEM", but it doesn't help. The parameter stays "on".

I also reloaded the database (pg_ctl reload) and restarted it (its a pacemaker resource, so pcs resource restart postgre_db) - But nothing happens.

How do I turn off this setting?

There are multiple levels where you configuration options are set in PostgreSQL:

system defaults
values specified in postgresql.conf or the startup command line
values preset for a database, user (maybe others, too) using, for example, ALTER DATABASE ... SET {option} ...
values set for the current session

Going down the list from 2., you have fewer and fewer options to set.  Furthermore, there are some that can only be set by a superuser (log_statement_stats) being one of them.

At the same time, the lower in the list an option is set, the higher its precedence: a session-level setting will override all the upper levels.

Speaking specifically about log_statement_stats, it is possible to set it from a session - but only a superuser can do it.  If your application connects with a superuser role (which is not really recommended), it can set this as one of the first things after obtaining the connection.  Look for it in your sources.

Ways of changing settings

The numbers refer to the levels listed above.

Supplying defaults at compile time
ALTER SYSTEM - this will store the settings in a postgresql.auto.conf file, which will be read in addition to postgresql.conf.
ALTER DATABASE ... SET ... or ALTER ROLE ... [IN DATABASE database_name] SET ....  Some settings can be set on database objects (like tables, functions, etc.), too.
SET \[SESSION\] ... - to change things for the current connection (often dubbed 'session').  There is even a way to change things until the end of the current transaction: SET LOCAL ...


MySQL Deadlock - accessing different primary key values also creating deadlock I went through MySQL logs and found the reason for deadlock

1st thread is trying to execute below query. Which is waiting for thread 2.

UPDATE M_SAMP SET MM_Q_IND=0 WHERE M_ID IN (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19))

The result of inner query (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19)) is M_ID = 3562.

2nd thread is executing below query.

DELETE FROM M_SAMP WHERE M_ID=3455

There is no foreign key relation defined in M_SAMP and MM_RVW_SAMP tables. And M_ID is primary key of M_SAMP table. And engine is InnoDB. And this issue is repeating.

Can any one help me, how locks are granted because of which deadlock is happening?

Logs

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-02-23 10:52:28 de8
*** (1) TRANSACTION:
TRANSACTION 13275344, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 25 lock struct(s), heap size 2408, 1394 row lock(s), undo log entries 1
MySQL thread id 1455, OS thread handle 0x11ac, query id 39660 xyz 192.168.1.108 userName Sending data
UPDATE M_SAMP SET FLAG=0 WHERE M_ID IN (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 61569 page no 65 n bits 176 index `PRIMARY` of table `dbName`.`m_samp` trx id 13275344 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 29; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 13275335, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
254 lock struct(s), heap size 27512, 21595 row lock(s), undo log entries 7
MySQL thread id 1458, OS thread handle 0xde8, query id 39664 xyz 192.168.1.108 userName updating
DELETE FROM M_SAMP WHERE M_ID=3455
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 61569 page no 65 n bits 176 index `PRIMARY` of table `dbName`.`m_samp` trx id 13275335 lock mode S locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 29; compact format; info bits 0

Thank you.

Update:

Number of records in M_SAMP table is 3524 and in MM_RVW_SAMP is 2.

Below is the result of EXPLAIN query.

EXPLAIN UPDATE M_SAMP SET MM_Q_IND=1 WHERE M_ID IN (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19))

So, I will answer the question "why it is creating problem with whole query":

In MySQL's REPEATABLE-READ transaction isolation mode, no phantom rows appear. In order to do that, MySQL locks, not only the rows, but also the gaps between them (also know as next-key locking).

As MySQL happens to be executing the read on the first table using an index scan, what MySQL considers a gap is probably larger than expected, as it uses the primary key to check that no newer rows are inserted that could affect the query with that particular scan type. As a result, a large part of the table may be affected.

Reducing the transaction-isolation mode should avoid the gap-locking and only lock the specific row being updated and improve the general concurrency while avoiding deadlocks as only one row is affected for that particular query.

Update: It seems that the lower isolation level doesn't fix the locking for the update + subselect. Rewriting it as a JOIN does actually do the trick? (Maybe the UPDATE has a different optimization than the SELECT?). The idea of the JOIN rewrite is doing something like this:

UPDATE M_SAMP M
JOIN MM_RVW_SAMP MM
ON M.M_ID = MM. M_ID
SET M.MM_Q_IND = 1
WHERE MM.TARGET_M_ID = 19;


Partition constraint not used for joins involving tables partitioned by timestamp I have a partitioned table structure like:

CREATE TABLE measurements (
    sensor_id bigint,
    tx timestamp,
    measurement int
);

CREATE TABLE measurements_201201(
    CHECK (tx >= '2012-01-01 00:00:00'::timestamp without time zone
       AND tx < ('2012-01-01 00:00:00'::timestamp without time zone + '1 mon'::interval))
)INHERITS (measurements);
CREATE INDEX ON measurements_201201(sensor_id);
CREATE INDEX ON measurements_201201(tx);
CREATE INDEX ON measurements_201201(sensor_id, tx);
....

And so on. Each table has approximately 20M rows.

If I query for a sample of sensors and a sample of timestamps in the WHERE clause, the query plan shows the correct tables being selected and indexes being usedm e.g.:

SELECT *
FROM measurements
INNER JOIN sensors TABLESAMPLE BERNOULLI (0.01) USING (sensor_id)
WHERE tx BETWEEN '2015-01-04 05:00' AND '2015-01-04 06:00'
    OR tx BETWEEN '2015-02-04 05:00' AND '2015-02-04 06:00'
    OR tx BETWEEN '2014-03-05 05:00' AND '2014-04-07 06:00' ;

However, if I use a CTE, or put the timestamp values into a table (not shown, even with indexes on the temporary table).

WITH sensor_sample AS(
    SELECT sensor_id, start_ts, end_ts
    FROM sensors TABLESAMPLE BERNOULLI (0.01)
    CROSS JOIN (VALUES (TIMESTAMP '2015-01-04 05:00', TIMESTAMP '2015-01-04 06:00'),
        (TIMESTAMP '2015-02-04 05:00', TIMESTAMP '2015-02-04 06:00'),
        (TIMESTAMP  '2014-03-05 05:00', '2014-04-07 06:00') ) tstamps(start_ts, end_ts)
)

Something like the below

SET constraint_exclusion = on;
SELECT * FROM measurements
INNER JOIN sensor_sample USING (sensor_id)
WHERE tx BETWEEN start_ts AND end_ts

Performs an index scan on every table. Which is still relatively fast, but with increasing complexity of queries, this can turn into seq scans which will end up being very slow for retrieving ~40K rows from a limited subset of partitioned tables (4-5 of 50).

I'm concerned that something like this is the problem.

  For non-trivial expressions you have to repeat the more or less verbatim condition in queries to make the Postgres query planner understand it can rely on the CHECK constraint. Even if it seems redundant!

How can I improve partitioning and query structure to reduce the likelihood of running seq scans on all my data?

Constraint-based exclusion [CBE] is performed on early stage of query planning, just after the query is parsed, mapped to actual relations and rewritten. (internals, Planner/Optimizer stage)

The planner cannot assume any contents of "sensor_sample" table.

So unless you have values hardcoded in the query, the planner will not exclude "partitions".

I guess what happens with the CTE variant... the planner is restricted because you use TABLESAMPLE and the whole subquery may be treated as volatile even if literals in the subquery are static. (that's just my guess, I'm not expert on planner code)

On the bright side, the index scan with negative result is blazingly fast. (single page scan at most!) so unless you have over 10000 partitions,  I would not bother.

So, to answer your question directly:

You cannot improve this data structure much more.
Regardin index scans - they are cheap;
Regarding sequential scans - they are avoided when possible, as you see on your own examples.


Restoring a Database Using Full Backup and Transaction Logs in SSMS I have a full backup file (.bak file) of a database for the 25/10/2019. However I want to restore the database to the 21/10/2019.

My problem is that I do not have the full backup files for the 21/10/19. The last full backup for the database I have is 25/10/19. What I do have are transaction logs going back to the 21/10/19.

In SSMS I have tried using the full backup file for 25/10/2019 and using the preceding transaction log files to restore the database to 21/10/19; however, it does not work and says unable to create restore plan due to break in LSN chain.

Please note a full backup of the database is made each night and every 30 minutes for the transaction logs.

The full backups prior to 25/10/2019 have been deleted.

Is it still possible to restore the database to a point in time using transaction logs? Either within SSMS or using a T-SQL command?

To perform a restore to (for example) 11:45 AM on October 21st, you need the last full backup that was taken prior to that time, plus your transaction log backups up to and including Noon (assuming the log backups happen exactly on the hour and half hour) on October 21st.

As you do not have a full backup prior to October 25th, you cannot do a restore to a point in time prior to that. With the backups you currently have, you cannot achieve what you're attempting to do.


Why is my SQL Server giving out of memory errors when there should be plenty? I have a production system running SQL Server 2019 Standard edition. It recently had a problem 3 times in 1 day where it became unresponsive until a reboot. Errors seem to point to memory limitations. 32GB is installed and the machine is dedicated to MSSQL. Max Memory set to 26GB.
(edited 2020-11-23 to remove some erroneous info re: MEMORYCLERK_SQLGENERAL.)
The most damnning info from the logs is a series of FAIL_PAGE_ALLOCATION errors.
Note: Since having the problem I've bumped up to a larger instance size (64GB)
Update 2020-11-22: The problem has reoccurred on the new larger instance. The memory errors began a few minutes after a scheduled backup of several databases. That may or may not be coincidence. It became nonresponsive and I forcibly restarted it.

I will start the MSFT support process as suggested by Josh and sqL_handLe.
I've made an Agent job (sproc here) to collect the results from dm_os_memory_objects every 5 minutes, based on sqL_handLe's query.
I've shared recent logs here. (I replaced the nl tags with actual newlines to make it easier to read.)
Disabled SSAS as suggested by Jana Sattainathan. I did confirm it was running and had 13GB "Min Working Set" as shown in the linked article, even though its actual working set was small. I think it's possible this subtle issue is the culprit, but if it's as dangerous as it seems, I'm very surprised I haven't run into it before. Jana, consider converting your comment to an answer?

The non-SQL load on this server is near-zero. The backup agent is just Ola Hallengren's TSQL scripts. The default SQL services are running but not in use. As mentioned, I've stopped SSAS. We occasionally use SSIS but not since the instance upgrade.

Identifying large memory objects associated with MEMORYCLERK_SQLGENERAL may make this situation on November 18 more clear.
-- memory objects in MEMORYCLERK_SQLGENERAL
SELECT omo.type, omo.pages_in_bytes, omo.partition_type_desc, omo.memory_node_id
FROM sys.dm_os_memory_objects omo
JOIN sys.dm_os_memory_clerks omc
ON omo.page_allocator_address = omc.page_allocator_address
WHERE omc.type = 'MEMORYCLERK_SQLGENERAL'
ORDER BY pages_in_bytes DESC;

Recently I saw a case where automatic seeding of an availability group replica was generating errors on the remote side due to insufficient disk space. In that case, memory object MEMOBJ_DBCOPY grew to be quite large over time (MEMOBJ_DBCOPY is in MEMORYCLERK_SQLGENERAL).
Editing this to add ~~
Obviously, if MEMORYCLERK_SQLGENERAL just keeps growing it'll consume memory that could instead be used for other activities and performance will degrade over time.  At some point, OOMs will occur - for example maybe there won't be enough memory in the pool and SQL Server will inform of that with an error.
And there's one more threshold/behavior to be aware of.  When SQL Server "total server memory" is no longer in its initial growth after startup, and database cache declines to 2% of "target server memory" on any one SQLOS memory node (in this case looks like there is only one memory node), most if not all of the user worker threads will likely try to correct the situation and a large amount of CPU will be expended performing "free list stalls".
Microsoft mentions this briefly in this kb article (although the kb article doesn't shed any light on what might be causing MEMORYCLERK_SQLGENERAL to be growing so large in this case).
KB4536005 - Improvement: Fix incorrect memory page accounting that causes out-of-memory errors in SQL Server
https://support.microsoft.com/en-us/help/4536005/kb4536005-improvement-fix-incorrect-memory-page-accounting-that-causes
In some cases where SQL Server memory gets into internal trouble, using DBCC DROPCLEANBUFFERS, or DBCC FREEPROCCACHE, or DBCC FREESYSTEMCACHE ('ALL') can rescue the instance for the time being.  Unfortunately, not the case here.  Have to actually identify the memory objects involved in order to bring MEMORYCLERK_SQLGENERAL back under control.
Edited to discuss November 22 SQL Server log...
~~
In the November 22 memorystatus output, memoryclerk_sqlgeneral doesn’t seem to be of immediate concern: immediately after each of the many errors memoryclerk_sqlgeneral seems to be at or about the same size of 22040 kb. Other extremely interesting details: [system physical memory high] is 1(hinting SQL Server memory could grow) and [current committed] is less than [target committed] (also hinting SQL Server memory should be able to grow).
The full SQL Server log shows the increase of RAM in the instance.
11/18/2020 10:15:11,Server,Unknown,Detected 32409 MB of RAM. This is an informational message; no user action is required.

11/18/2020 15:26:01,Server,Unknown,Detected 64723 MB of RAM. This is an informational message; no user action is required.

We also see the modifications to [Max Server Memory].
11/18/2020 10:33:03,spid96,Unknown,Configuration option 'max server memory (MB)' changed from 300000 to 280000. Run the RECONFIGURE statement to install.

11/19/2020 11:34:10,spid94,Unknown,Configuration option 'max server memory (MB)' changed from 280000 to 60000. Run the RECONFIGURE statement to install.

Both 300000 mb and 280000 mb were too large for SQL Server to attain on a VM with 32409 mb of memory.  So SQL Server chose lower values of [target server memory] on the VM that it could attain, and managed memory based on those values.
What is likely not as intuitive is on the new VM, for reasons currently unknown, SQL Server also chose a lower target than the new [Max Server Memory] value of 60000 mb.  That can be seen in memorystatus sections like the following.
11/22/2020 09:06:59,spid69,Unknown,Memory Manager               KB
---------------------------------------- ----------
VM Reserved                               101157820
VM Committed                                 636280
Locked Pages Allocated                     51513180
Large Pages Allocated                             0
Emergency Memory                               1024
Emergency Memory In Use                          16
Target Committed                           58468304
Current Committed                          52149464
Pages Allocated                            50371824
Pages Reserved                               273864
Pages Free                                    35352
Pages In Use                                7855040
Page Alloc Potential                       50512960
NUMA Growth Phase                                 0
Last OOM Factor                                   3
Last OS Error                                     0

The [Target Committed] value here of 58468304 kb corresponds to what appears in perfmon as [Target Server Memory].  Its approximately 57098 mb.  Many of the errors have this value.  The other value I see in there for [Target Committed] is 57882440 kb, or approximately 56526 mb.
If the non-SQL Server workload on this VM is as expected (eg backup agent if there is one, other SQL Server related stuff like SSIS, SSAS, SSRS and any other apps including SSMS or ADS) I recommend setting [Max Server Memory] no higher than 56500 mb for this instance.  That may result in better memory management from SQL Server.
However, there's still the question: If memorystatus results show [system physical memory high] why hasn't SQL Server grown [Current Committed] closer to [Target Committed]?  Especially rather than triggering OOM type errors.  I don't have an answer for that, yet.
~~
aha! missed this before.
parsing MEMORYSTATUS results from the error log can be tricky.  the newest lines of the error log are at the top. But... the memorystatus results within each error log line separated with  should not be reversed like the error log lines.  First reverse full lines from the error log, then replace  with newlines.
Correct order is: clerk, VM Reserved, VM Committed, Locked Pages Allocated, SM Reserved, SM Committed, Pages Allocated.
11/22/2020 09:06:59,spid69,Unknown,MEMORYCLERK_SQLBUFFERPOOL (node 0)               KB
---------------------------------------- ----------
VM Reserved                                25686200
VM Committed                                 141312
Locked Pages Allocated                      1072140
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                            44537424
11/22/2020 09:06:59,spid69,Unknown,MEMORYCLERK_SQLGENERAL (node 0)                  KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                               22040

The easiest way to confirm this ordering is by using sqlcmd against the instance.
This should give the details for each clerk in the correct order.
sqlcmd -Q "dbcc memorystatus"
So scrolling all the way back to November 18 in the logs on Google Drive we find:
11/18/2020 15:10:48,spid51,Unknown,MEMORYCLERK_SQLBUFFERPOOL (node 0)               KB
---------------------------------------- ----------
VM Reserved                                12867644
VM Committed                                  75776
Locked Pages Allocated                       546740
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                            22821672
11/18/2020 15:10:48,spid51,Unknown,MEMORYCLERK_SQLGENERAL (node 0)                  KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                               22416

During the events on November 18, it wasn't MEMORYCLERK_SQLGENERAL that was over 21.75 gb in size.  That was MEMORYCLERK_SQLBUFFERPOOL, the database cache.  MEMORYCLERK_SQLGENERAL was a much more reasonable 22416 kb in size on November 18 during the OOMs.
There's some kind-of-good news in this: although this is something that will still take Microsoft's involvement to fix (because SQL Server really shouldn't throw OOMs when there's room to grow into [Max Server Memory]), if the problem persists this is the type of conditions in which clearing caches and dropping clean buffers can prevent needing to restart the service or having to reboot the VM.  I saw something similar - very similar actually - on SQL Server 2017 CU18.  Once-a-day we cleared caches and dropped clean buffers whie working with Microsoft to try to understand what was going on.  That's not ideal but its better than crashing.  Unfortunately, the problem magically went away before we could diagnose what was really going wrong.
This is pretty drastic for a band-aid but if the system becomes desparate...
CHECKPOINT
DBCC DROPCLEANBUFFERS -- drop all clean buffers from database cache
DBCC FREEPROCCACHE -- clear plan/procedure cache
DBCC FREESYSTEMCACHE('ALL') -- clear any other system caches

It's problably redundant to FREEPROCCACHE in addition to FREESYSTEMCACHE('ALL') - I never checked whether that's the case or if the FREEPROCCACHE lends some benefit of its own in that scenario.  Not something you'd want to do anymore than necessary since plans would have to be re-compiled and its kicking a lot lof stuff out of the database cache.  But if you get desparate while waiting to resolve the condition...


Why SQL server does not have column-level locking? As far as i know, the lowest level locking is row-level

However i don't see the reason why it can not have column level locking

Assume that 2 queries simultaneously updates 2 different columns.

So why it can not happen? Why each update has to lock at least row level?

This behavior tells me that i should split table into smaller tables so less row level locking would happen

Am i incorrect?

This feature could exist. It would consume resources and complicate the architecture. So it's a trade-off that had to be made.

For the same reason some databases go in the opposite direction: They lock on the page or table level. MongoDB even has a global write lock for the entire database! This surely makes some physical implementation concerns much easier.

SQL Server's locking model already is quite sophisticated and, in my experience, addresses practical scenarios very well. I'm not sure column level locking would make it a better product (even if the developer time were free for Microsoft).


SQL Server's READ COMMITTED SNAPSHOT vs SNAPSHOT I was researching the differences between SQL Server's READ COMMITTED SNAPSHOT and SNAPSHOT isolation levels and came across the following resource:

Choosing Row Versioning-based Isolation Levels

  For most applications, read committed isolation using row versioning
  is recommended over snapshot isolation for the following reasons:

  It consumes less tempdb space than snapshot isolation.
  Snapshot isolation is vulnerable to update conflicts that are not applicable to read committed isolation using row versioning. When a
  transaction running under snapshot isolation reads data that is then
  modified by another transaction, an update by the snapshot transaction
  to the same data causes an update conflict and the transaction
  terminates and rolls back. This is not an issue with read committed
  isolation using row versioning.

I'm somewhat new to these topics, but I can't seem to understand the two bullet points from the link above.

Why would the tempdb space be different for these modes? Does one store more granular versioning than the other?
Why is snapshot isolation more vulnerable to update conflicts?

READ COMMITTED SNAPSHOT uses a new snapshot after each statement. That means that less row versions are being kept alive. (The statement you quoted from the docs is slightly misleading because it suggest that this is always true - it is only true in case of long-running SNAPSHOT transactions.) Snapshot row versions are created on writes. The reads do not influence what gets put into tempdb. Writers cannot possibly foresee what reads will be carried out in the future. Readers only influence only what can be purged.
When a SNAPSHOT transaction T1 writes to a row that was modified by another transaction T2 in the time between T1 started and T1 attempted the write, the statement fails with an update conflict error. This is an optimistic concurrency model. With READ COMMITTED SNAPSHOT T1 would wait for T2 to release the X-lock on the row and continue normally.


Identifying why query is sleeping with open transactions We have a 3rd-party application which is sporadically leaving connections in a sleeping state with an open transaction. This is causing many other queries to be blocked. I've been logging sp_WhoIsActive to a table, and below is the lead blocker. I believe the Java application is using Hibernate ORM.

The query is against a very small table with just a few thousand rows. If I run this statement manually, it finishes immediately. The query in my sp_WhoIsActive table shows NULL in the wait_info table, and 10 million reads, which sounds suspicious. I force statistics updates every day on the entire database as we've seen hundreds of millions of reads on similar small queries in the past. That seems to have helped, but this still doesn't sound right.

How can I troubleshoot this to see what's happening? The record does not show the execution plan.

(@P0 decimal(38,0),@P1 decimal(38,0),@P2 nvarchar(4000))DELETE FROM
  SCHEDULE WHERE (PROCESS_ID= @P0 AND PARENT_ID= @P1 AND SCHEDULE!= @P2)

Been there 10+ years ago, here's what we did

We had the same issue with Kronos/Payroll. It get's pretty serious when people cannot get their paycheck. Like yourself, I narrowed things down to open/sleeping transactions and it was Java related as well. What's more when we contacted the vendor, they refused to take any responsibility for it. So we internally had to find a solution. I reviewed the vendor API and it definitely looked like the issue was on the vendor side.

In my case, which MAY not be your case, I discovered by working with the payroll staff that these open sleeping transactions had actually completed their work. So we implemented the following job that still looks for these processes and kills them to this day.

Basically, a T-SQL job shown in Listing A calls stored procedure in Listing B to kill the open/sleeping transactions, and the data about these issues is stored in Listing C. My apologies for any legacy T-SQL faux pas you may find--this code is crusty. Please try it in your dev/test environment first to see if it works for you. Just talking about killing these is just as creepy today as it was then--I don't like it.

Oh yes, the kicker for us all is that killing the open/sleeping transactions did NOT roll them back--but allowed them to complete (as well as stop all the blocking chains). My guess is that they were not programmed correctly to be distributed but rather they programmed the app and SQL Server to be on the same server.

Listing A: T-SQL job killing open/sleeping transactions (first four lines should be commented out)

This process kills transactions that have the following attributes
1.) They're blocking other transactions
2.) They're sleeping (i.e. doing no work)
3.) They're in a transaction that has not closed (continues to remain open)

while (1=1)
begin
declare @sleepingblocks int
exec sp_kill2 @sleepingblocks=@sleepingblocks output
--print @sleepingblocks
while (@sleepingblocks > 0)
begin
      waitfor delay '00:00:15'
      exec sp_kill2 @sleepingblocks=@sleepingblocks output
--    print @sleepingblocks
end
end

Listing B: the sp_kill2 stored procedure looks for open/sleeping transactions and kills them

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_kill2]    Script Date: 10/13/2017 4:07:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

    ALTER proc [dbo].[sp_kill2]
    @sleepingblocks int output
    as
    --declare @sleepingblocks int
    --select count(*) from kronos..totaleventts where totalizationstatus <> 0
    select @sleepingblocks=0
    set nocount on
    create table #one
    (
          RowID int primary key identity(1,1),
          spid int,
          sqlhandle binary(20)
    )

    create table #two
    (
          RowID int primary key identity(1,1),
          spid int,
          sqlhandle binary(20)
    )

    create table #three
    (
          RowID int primary key identity(1,1),
          killcmd varchar(4000),
        spid varchar(4000)

    )

    insert into #one(spid,sqlhandle)
    select distinct T.spid,T.sql_handle from master..sysprocesses T --where spid=169
    join (select distinct blocked,spid,sql_handle from master..sysprocesses where blocked <> 0) D
    on T.spid = D.blocked
    where T.status like '%sleeping%' and open_tran > 0
    select * from #one

    waitfor delay '00:00:15'

    insert into #two(spid,sqlhandle)
    select distinct T.spid,T.sql_handle from master..sysprocesses T --where spid=169
    join (select distinct blocked,spid,sql_handle from master..sysprocesses where blocked <> 0) D
    on T.spid = D.blocked
    where T.status like '%sleeping%' and open_tran > 0
    select * from #two

    insert into #three(killcmd,spid)
    select distinct 'kill ' +  convert(varchar(255),a.spid),convert(varchar(255),a.spid) from #one a join #two b on a.spid = b.spid and a.sqlhandle = b.sqlhandle

    declare @currow int, @maxrow int, @cmdstring varchar(8000)
    select @currow=1
    select @maxrow=0
    select @maxrow = count(*) from #three

    while (@currow <=@maxrow)
    begin

    select @cmdstring = 'select distinct spid
    ,kpid
    ,blocked
    ,waittype
    ,waittime
    ,lastwaittype
    ,waitresource
    ,sp.dbid
    ,uid
    ,cpu
    ,physical_io
    ,memusage
    ,login_time
    ,last_batch
    ,ecid
    ,open_tran
    ,status
    ,sid
    ,hostname
    ,program_name
    ,hostprocess
    ,cmd
    ,nt_domain
    ,nt_username
    ,net_address
    ,net_library
    ,loginame
    ,context_info
    ,sql_handle
    ,stmt_start
    ,stmt_end
    ,request_id
    ,text from master..sysprocesses sp cross apply sys.dm_exec_sql_text(sql_handle) as st where sp.spid ='+spid  from #three where RowID = @currow

    --print @cmdstring
    --select @cmdstring = 'dbcc inputbuffer('+spid+')' from #three where RowID = @currow

    insert into dbadmin..KilledProcesses3(
    spid
    ,kpid
    ,blocked
    ,waittype
    ,waittime
    ,lastwaittype
    ,waitresource
    ,dbid
    ,uid
    ,cpu
    ,physical_io
    ,memusage
    ,login_time
    ,last_batch
    ,ecid
    ,open_tran
    ,status
    ,sid
    ,hostname
    ,program_name
    ,hostprocess
    ,cmd
    ,nt_domain
    ,nt_username
    ,net_address

    ,net_library
    ,loginame
    ,context_info
    ,sql_handle
    ,stmt_start
    ,stmt_end
    ,request_id
    ,text
    )
    exec (@cmdstring)
    --print @cmdstring

    --select @cmdstring = 'dbcc inputbuffer('+spid+')' from #three where RowID = @currow
    select @cmdstring = killcmd from #three where RowID = @currow
    --print @cmdstring
    exec (@cmdstring)
    select @currow = @currow +1
    select @sleepingblocks =1
    end

    drop table #one
    drop table #two
    drop table #three

    /*
    select
    spid
    ,kpid
    ,blocked
    ,waittype
    ,waittime
    ,lastwaittype
    ,waitresource
    ,sp.dbid
    ,uid
    ,cpu
    ,physical_io
    ,memusage
    ,login_time
    ,last_batch
    ,ecid
    ,open_tran
    ,status
    ,sid
    ,hostname
    ,program_name
    ,hostprocess
    ,cmd
    ,nt_domain
    ,nt_username
    ,net_address
    ,net_library
    ,loginame
    ,context_info
    ,sql_handle
    ,stmt_start
    ,stmt_end
    ,request_id
    ,text
    from master..sysprocesses sp cross apply sys.dm_exec_sql_text(sql_handle) as st where sp.spid =169
    select * from dbadmin..KilledProcesses3

    truncate table dbadmin..killedprocesses3

    */

Listing C: Processes3 table contains data about the sleeping/open transactions

USE [dbadmin]
GO

/****** Object:  Table [dbo].[killedprocesses3]    Script Date: 10/13/2017 4:08:56 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

    CREATE TABLE [dbo].[killedprocesses3](
        [spid] [smallint] NOT NULL,
        [kpid] [smallint] NOT NULL,
        [blocked] [smallint] NOT NULL,
        [waittype] [binary](2) NOT NULL,
        [waittime] [bigint] NOT NULL,
        [lastwaittype] [nchar](32) NOT NULL,
        [waitresource] [nchar](256) NOT NULL,
        [dbid] [smallint] NOT NULL,
        [uid] [smallint] NULL,
        [cpu] [int] NOT NULL,
        [physical_io] [bigint] NOT NULL,
        [memusage] [int] NOT NULL,
        [login_time] [datetime] NOT NULL,
        [last_batch] [datetime] NOT NULL,
        [ecid] [smallint] NOT NULL,
        [open_tran] [smallint] NOT NULL,
        [status] [nchar](30) NOT NULL,
        [sid] [binary](86) NOT NULL,
        [hostname] [nchar](128) NOT NULL,
        [program_name] [nchar](128) NOT NULL,
        [hostprocess] [nchar](10) NOT NULL,
        [cmd] [nchar](16) NOT NULL,
        [nt_domain] [nchar](128) NOT NULL,
        [nt_username] [nchar](128) NOT NULL,
        [net_address] [nchar](12) NOT NULL,
        [net_library] [nchar](12) NOT NULL,
        [loginame] [nchar](128) NOT NULL,
        [context_info] [binary](128) NOT NULL,
        [sql_handle] [binary](20) NOT NULL,
        [stmt_start] [int] NOT NULL,
        [stmt_end] [int] NOT NULL,
        [request_id] [int] NOT NULL,
        [text] [nvarchar](max) NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO


clustered and covering index ignored on delete statement. Table scan occurs Why would SQL Server 2005 find it more efficient to perform a table scan instead of using the available clustered index on the primary key (and only the primary key)?

DISCLAIMER:
There is also a non-clustered, non-unique index on the primary key with no included columns. This is baffling to me and we've had a good office chuckle already. If this index ends up being the problem, then we know who to shoot. Unfortunately, it's a production site and I can't just rip it out but will make plans to do so if necessary.

Maybe the problem is not the mentally deficient contrary index, however...

According to Foglight PASS the following statement has been causing a scan on a table with ~10 million rows about 600 times an hour when we delete a row by the primary key:

DELETE FROM SomeBigTable WHERE ID = @ID

The table DDL:

CREATE TABLE [SomeBigTable]
(
    [ID] [int] NOT NULL,
    [FullTextIndexTime] [timestamp] NOT NULL,
    [FullTextSearchText] [varchar] (max) NOT NULL,
    CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
) -- ...
ON PRIMARY

The clustered index constraint in detail:

ADD CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
(
    [ID] ASC
) WITH  PAD_INDEX = OFF
       ,STATISTICS_NORECOMPUTE = OFF
       ,SORT_IN_TEMPDB = OFF
       ,IGNORE_DUP_KEY = OFF
       ,ONLINE = OFF
       ,ALLOW_ROW_LOCKS = ON
       ,ALLOW_PAGE_LOCKS = ON
       ,FILLFACTOR = 75
ON PRIMARY

The non-unique, non-clustered index on the same table:

CREATE NONCLUSTERED INDEX [IX_SomeBigTable_ID] ON [SomeBigTable]
(
    [ID] ASC
) WITH  PAD_INDEX = OFF
       ,STATISTICS_NORECOMPUTE = OFF
       ,SORT_IN_TEMPDB = OFF
       ,IGNORE_DUP_KEY = OFF
       ,ONLINE = OFF
       ,ALLOW_ROW_LOCKS = ON
       ,ALLOW_PAGE_LOCKS = ON
       ,FILLFACTOR = 98
ON PRIMARY

There is also a foreign key constraint on the [ID] column pointing to an equally large table.

The 600 table scans are about ~4% of the total delete operations per hour on this table using the same statement. So, not all executions of this statement cause a table scan.

It goes without saying, but saying it anyway...this is a lot of nasty I/O that I'd like to send packing.

The optimizer may find a scan more appropriate based on statistics on the duplicate index instead of statistics on the PK. You didn't define the duplicate index as UNIQUE, so getting the "good" or "bad" plan could be just a matter of which index metadata is used by the optimizer to produce the plan.
Very hard to tell without the actual execution plan, though.


SQL Server 2008 can't repair consistency I have a problem with a SQL Server 2008 database.

Launching

DBCC CHECKDB

I get this error:

  SQL Server detected a logical consistency-based I/O error: incorrect
  checksum (expected: 0xd2e00940; actual: 0x925ef494). It occurred
  during a read of page (1:15215) in database ID 22 at offset
  0x000000076de000 in file 'C:\Program Files\Microsoft SQL
  Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\file.mdf'.
  Additional messages in the SQL Server error log or system event log
  may provide more detail. This is a severe error condition that
  threatens database integrity and must be corrected immediately.
  Complete a full database consistency check (DBCC CHECKDB). This error
  can be caused by many factors; for more information, see SQL Server
  Books Online.

I found the table causing the problem:

DBCC CHECKTABLE

  Msg 824, Level 24, State 2, Line 8
  SQL Server detected a logical
  consistency-based I/O error: incorrect checksum (expected: 0xd2e00940;
  actual: 0x925ef494). It occurred during a read of page (1:15215) in
  database ID 22 at offset 0x000000076de000 in file 'C:\Program
  Files\Microsoft SQL
  Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\file.mdf'.
  Additional messages in the SQL Server error log or system event log
  may provide more detail. This is a severe error condition that
  threatens database integrity and must be corrected immediately.

  Complete a full database consistency check (DBCC CHECKDB). This error
  can be caused by many factors; for more information, see SQL Server
  Books Online.

So I tried with the repair operations:

DBCC CHECKTABLE (table_name, REPAIR_ALLOW_DATA_LOSS)

but I get the same error:

  Msg 824, Level 24, State 2, Line 8
  SQL Server detected a logical
  consistency-based I/O error: incorrect checksum (expected: 0xd2e00940;
  actual: 0x925ef494). It occurred during a read of page (1:15215) in
  database ID 22 at offset 0x000000076de000 in file 'C:\Program
  Files\Microsoft SQL
  Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\file.mdf'.
  Additional messages in the SQL Server error log or system event log
  may provide more detail. This is a severe error condition that
  threatens database integrity and must be corrected immediately.

  Complete a full database consistency check (DBCC CHECKDB). This error
  can be caused by many factors; for more information, see SQL Server
  Books Online.

I've also tried setting the DB in SINGLE_USER mode, but with no result.

I am not able to delete nor truncate the table. As I always get the same error.

The table does not have any constraints. It has one PK and one Index, but I can't drop any of them.

You need to restore the page from a full backup, then applying the logs forward to current or you need to do a full restore.

I'd also run chkdsk as it appears you might be having a disk problem as the same page is reporting problems when attempting to repair it.  Also run any disk management tools and see if they report problems with the disk.


How to run multiple ST_DWITHINs/ ST_INTERSECTS within a single WHERE clause PostgreSQL 11, on Windows 10.

I'm working with a post-gis enabled database.
I want to UPDATE a large polygon table (100,000+ rows) WHERE the geometry of that host table intersects the geometry of another table (table a) AND another table (table b). 'Table a' also has a WHERE field = value attribute filter placed on it.

Using this statement, it takes an indefinte amount of time, seemlingly never to complete (I even left it overnight and it still didn't finish):

UPDATE schema1.polygontable poly
SET ref_id = '5z'
FROM schema2.tablea a, schema3.tableb b
WHERE (ST_DWITHIN(a.geom, poly.geom,0) AND (a.type = 'Ground')) OR (ST_DWITHIN(b.geom, poly.geom,0));

However, If I split the query at the OR clause, the now separate 2 functions take 5 seconds and 1 second respectively:

UPDATE schema1.polygontable poly
SET ref_id = '5z'
FROM schema2.tablea a
WHERE ST_DWITHIN(a.geom, poly.geom,0) AND (a.type = 'Ground');

and

UPDATE schema1.polygontable poly
SET ref_id = '5z'
FROM schema3.tableb b
WHERE ST_DWITHIN(b.geom, poly.geom,0);

Nb: all three tables have a spatial index each. And they are all hosted on an Amazon AWS instance.

So, why does my query freeze/hang when I combine the functions? My syntax looks fine I think.

Your FROM clause creates an unwanted cross join between the tables. This is not related to PostGIS and geometric functions.

FROM schema2.tablea a, schema3.tableb b

Indeed as suggested, use two separate UPDATE queries, one for each table. When you compare the EXPLAIN commands, you should see 2 nested loops for the big query instead of one for the alternative solution with two small queries.


"The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'." after adding indexes to temp tables Recently,  I added some indexes on temp tables to procedures which prepare data for data warehouse. On a testing db, tests of these procedures worked fine but when running on client's server it failed due to this error and the server needed to be restarted. Can this be related to adding the indexes or it could be something totally else?

Client's server version:

Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64)
Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Testing server version:

Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Developer Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

An index create on a temp table will use space in the tempdb transaction log while the index is being built.  So yes, the index could have caused the issue in the general sense that it added enough transactions to the log file that it ran out of space.

Transaction Log Disk Space for Index Operations


How to repeat row based on qty field I have a table like

id product qty
1  apples  3
2  oranges 1

I want to get a query that will return

1  apples  1
1  apples  1
1  apples  1
2  oranges 1

How do I accomplish that? I've seen several answers on splitting a column into rows like "a,b,c" but not on qty like this. Any help is appreciated.

You need a table of numbers, and fill this table with sequential number as much as you want.

CREATE TABLE numbers (
    number int
)
ENGINE = InnoDB;

INSERT INTO numbers (number) VALUES (1), (2), (3), (4), (5); -- you can add more number according your needs.

And then join this table with your table.

SELECT p.id, p.product, 1
FROM products p
     JOIN numbers n ON p.qty >= n.number;

This should produce result as you want.

If you can't create table, you can simulate table with UNION.

SELECT p.id, p.product, 1
FROM products p
    JOIN (
                SELECT 1 as number UNION
                SELECT 2 UNION
                SELECT 3 -- you can continue this line
               ) n
    ON p.qty >= n.number;


SQL Server: UPDATE permission for table variable SQL Server 2012

I have a maintenance script that I wrote for some of my operators.  They are members of the fixed db_datareader and db_datawriter roles.  Part of the script creates a table variable, then populates the variable with some statistics generated during the script.

The problem occurs when the script attempts to UPDATE the table variable.  INSERT appears to work, but not UPDATE.

...
--!!! THIS WORKS !!!--
INSERT INTO @tblRecCounts([table_name])
select name from sys.tables where
name not in
('sysdiagrams','sysssislog')

--!!! THIS FAILS !!!--
USE db1
UPDATE @tblRecCounts
SET f.db11PreLoadCount=dtRC.row_count
FROM @tblRecCounts f
INNER JOIN
(
<some subquery>
)...

The script works if I run it with higher permissions (e.g. owner).  The error that db_datawriter gets is "The user does not have permission to perform this action."

What and how do I set permissions for these roles for a table variable that they create??? I would think the entity that creates the variable (table) would own it.

Martin Smith's comment ("Does the subquery itself work?") was ultimately the answer and highlighted my insufficient forensic work! ;-)

The subquery was returning ROW COUNTS from tables:

...
INNER JOIN sys.dm_db_partition_stats AS ddps
ON i.OBJECT_ID = ddps.OBJECT_ID
...

The fixed roles I was using don't give VIEW DATABASE STATE permission (which is required to view the db_partition_stats table).  So, I'll either need to mess with the roles for these users or find another workaround.

I guess it's worth noting that if you come across this circumstance, you need to deconstruct your query.  The fixed db roles that I refer to CAN CREATE, INSERT, UPDATE, and DELETE records from a table variable.


If the table data means that the datetimes of the trips are consistent with the id order, and trips always start at 35 and end at 38 and vice versa, then you could use the LAG() function, assuming that you are in SQL Server 2012 or later version:

with ins_and_outs as
  ( select zoneid,
           previous_zoneid = lag(zoneid) over (order by id)
    from tablex
  )
select whole_trips = count(*) - 1
from ins_and_outs
where zoneid in (35, 38)
  and (previous_zoneid <> zoneid or previous_zoneid is null) ;

This checks the "previous" zoneid, based on id ordering to see if the zone has changed. You may have additional columns (with date/time data) that would be more accurate to base the order than the (order by id) I used.

Perhaps this variation might be more close to the description of a "trip" (from 35 to 38 and from 38 to 35):

with ins_and_outs as
  ( select zoneid,
           previous_zoneid = lag(zoneid) over (order by id)
    where zoneid in (35, 38)
    from tablex
  )
select whole_trips = count(*) - 1
from ins_and_outs
where (previous_zoneid <> zoneid or previous_zoneid is null) ;

This will give different results, if there are cases, "trips" that start from zone 35, go to other zones and then return to 35 without going through zone 38. Query 1 will count such a trip. Query 2 will not count it.


Insert Into Multiple Tables At Once I am migrating data between two databases.  From the first database I am exporting a csv file that has the following columns:

Customer_Name
Contact_First_Name
Contact_Last_Name
Phone1
Phone2
Fax

I need to take this data and put it into the following tables for the new database:

Table: customer_contact
    contact_id  INT IDENTITY(1,1)
    first_name  VARCHAR(50)
    last_name   VARCHAR(50)
    customer_id INT
    PK = contact_id

Table: contact_phone
    contact_id INT
    phone_num  VARCHAR(15)
    in_use     BIT
    PK = contact_id, phone_num

Table: contact_fax
    contact_id INT
    fax_num    VARCHAR(15)
    in_use     BIT
    PK = contact_id, fax_num

My plan is to BULK INSERT the data from the csv into a temporary table, and then use that to do INSERTs into each of the other tables.

Since the main identifier for each contact if their first and last names, how do I insert the corresponding data into the phone and fax tables?  I am using SQL Server.

This solution applies when you have repeated data in the CSV and you want to control what to do in every case. Since, there is nothing in your question about the following fields, I'm not including any logic for them:

customer_id INT,
in_use     BIT

0 - create the tables you already declared in your question

1 - create a t_Bulk table having the exact structure of your cvs and execute the bulk copy

CREATE TABLE T_BULK (
Customer_Name VARCHAR(100),
Contact_First_Name VARCHAR(50),
Contact_Last_Name VARCHAR(50),
Phone1 VARCHAR(15),
Phone2 VARCHAR(15),
Fax VARCHAR(15)
);
GO

2 - With the T_BULK table already full, let's process each record:

DECLARE @ContactId int, @FirstName varchar(50), @LastName varchar(50), @Phone1 varchar(50), @Phone2 varchar(50), @Fax varchar(50)

    DECLARE MY_CURSOR CURSOR
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR
    SELECT Contact_First_Name, Contact_Last_Name, Phone1, Phone2, Fax
    FROM dbo.T_BULK

    OPEN MY_CURSOR
    FETCH NEXT FROM MY_CURSOR INTO @FirstName, @LastName, @Phone1, @Phone2, @Fax
    WHILE @@FETCH_STATUS = 0
    BEGIN

    //if the contact already exists we get the identity
    IF EXISTS (select contact_id from customer_contact where first_name=@FirstName and last_name=@LastName)
        select @ContactId = contact_id from customer_contact where first_name=@FirstName and last_name=@LastName

    //if the contact does not exit we need to insert and retrieve the new Identity value
    ELSE
        BEGIN
            insert customer_contact (first_name,last_name) values (@FirstName,@LastName)
            set @ContactId = SCOPE_IDENTITY()
        END

    //now we continue with the phones and fax
    //insert only new phones
    INSERT contact_phone (contact_id,phone_num)
    SELECT Id, Phone
    FROM (select @ContactId as Id, @Phone1 as Phone UNION select @ContactId ,@Phone2) P
    WHERE NOT EXISTS (SELECT * FROM contact_phone C WHERE C.contact_id = P.Id and C.phone_num = P.Phone);

    //insert only new fax
    INSERT contact_fax (contact_id,fax_num)
    SELECT Id, fax
    FROM (select @ContactId as Id, @Fax as fax) F
    WHERE NOT EXISTS (SELECT * FROM contact_fax C WHERE C.contact_id = F.Id and C.fax_num = F.fax);

        FETCH NEXT FROM MY_CURSOR INTO @FirstName, @LastName, @Phone1, @Phone2, @Fax
    END

    CLOSE MY_CURSOR
    DEALLOCATE MY_CURSOR

Good luck

In order to include an additional field:

in the declare zone include an aditional variable:
@Extension.

which you must include in both FETCH NEXT lines. Then you need to include that variable when inserting the phones, but in this case you need to check that phone2 is not equeal to phone1, just to ensure you don't have repeated phones:

    (select @contactId as Id, @Phone1 as Phone, @Extension as Extension
UNION select @ContactId, @Phone2, null where @Phone2 <>@Phone1) P


SQL Server 2008, SOAP Endpoint, http.sys, timeout I am debugging a SOAP webservice hosted by SQL Server 2008. I know that those are deprecated in future versions and we should consider a different technology, of course.
There is an exposed procedure which legitimately runs for quite a long time sometimes, depending on circumstances. Now, when it spills over 120 seconds, the connection gets reset due to a timeout, in the http error log there is an entry

443 HTTP/1.1 POST /APIDevelop/ - - Timer_EntityBody -

and the CURL-based library on the clientside gets a 104 error (CONNRESET).
We have tried to adjust the timeout settings for http.sys by running

netsh http add timeout timeouttype=idleconnectiontimeout value=480

but it seems that http.sys is not impressed by these settings (which end up in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\HTTP\Parameters), not after restarting SQL Server and eventually the whole server, either. The timeout is still there at 120 seconds. Actually, the call takes around 130s but I attribute the difference (maybe, mistakenly) to other latencies.

Anyway, the question is: SQL Server is using http.sys for running its SOAP endpoints - how can I configure http.sys's in this case? Specifically for timeouts?

For anyone who might stumble into this, too.
It turned out to be not a matter of timeouts, but sizes of the POSTed payload. The transfer would always get stuck if the size of the posted parameter would be over a certain threshold (somewhere between 8 and 9 Mb).
What fixed it was setting the SSL version to 3 as one of the CURL options (we have a custom SOAP client library).

A big complication in debugging it in our case was that certain servers were behind a load balancer built on Nginx proxy. For those, the call worked fine! But not for the ones that we connected to directly. My guess (I am no specialist in neither Nginx nor SSL) is that Nginx has enough compatible interfaces on "both ends" and was able to receive the call and forward it to the server properly.

What lead to the solution was trying communicating from different clients. When it worked fine from SoapUI but not from our library it was only a matter of prying on every detail of the connection parameters until something made a difference.


how to use the same timestamp along several tables? Lets say a have 2 tables and both have a datetime column.

On table A, on datetime column, lets say, ts1, is non null, default is CURRENT_TIMESTAMP and on update CURRENT_TIMESTAMP;

The other table, B, the datetime column, lets say, ts2 is non null;

The datetime values on both tables must be the same (A.ts1==B.ts2).

As ts1 is auto-updated on inserts and updates, I don't need to get the current_stamp on advance.

Now, how can I do inserts on table B in such way that B.ts2 has the same timestamp value (A.ts1) from a specific record on table A?

Is there a relation ship between the tables to achieve this or some special insert/select statement ?

sorry if the answer is obvious...I am a new to databases...
PS: Using mySql 5.7 innoDB.

PS2: I found this works, but seems to be awkward.

insert into B (y, ts2) values ('a value for y', (select ts1 from A where x=1));

any other better ideas ?

@Lennart:
Thanks for your comments. Yes I need help in the design. I am open to suggestions.

Developing this a bit more:

On table A, x is a key.
Also, ts1 can not be unique since more than 1 record can have the same ts, because of possible concurrent access to create records on A.

Currently, there is no keys on table B.
The column ts2 can not be unique too, since table B store several y values with the same timestamp ts2;
In fact the excepted usage of table B is something like:

select * from B where ts2=(select ts1 from A where x=somevalue);

I liked how this sounds "add a trigger that does the update". Can you develop this a bit more, please ?

PS3: @Lennart, here we go:

CREATE TABLE `device` (
  `deviceID` varchar(12) NOT NULL,
  `snmpSet` varchar(30) NOT NULL,
  `firstSeen` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `lastSeen` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

ALTER TABLE `device`
  ADD PRIMARY KEY (`deviceID`);
COMMIT;

CREATE TABLE `snmpData` (
  `deviceID` varchar(12)  NOT NULL,
  `ts` datetime NOT NULL,
  `oid` varchar(45)  NOT NULL,
  `value` varchar(30)  NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf16 COMMENT='hold all snmp data from each device';

if it is a new device, insert it into table device and add its data to snmpData

INSERT INTO device (deviceID, snmpSet) VALUES ($id, $snmpSet);
INSERT INTO snmpData (deviceID, ts, oid, value) VALUES ($id, (SELECT lastSeen FROM device WHERE deviceID=$id),$oid,$value);

else, it is a existing device, I need to update the lastSeen on device and then, the same insert on snmpData

UPDATE device SET lastSeen=current_timestamp() WHERE deviceID=$id;
INSERT INTO snmpData (deviceID, ts, oid, value) VALUES ($id, (SELECT lastSeen FROM device WHERE deviceID=$id),$oid,$value);

The general idea:
This DB will support a monitoring tool that store snmp data (oid/value pairs) for several devices (deviceID)
The table device holds basic info about every device and the snmpData holds the data itself for every device.
If I want to check the last values received from a device I plan to do something like this:

select oid, value from snmpData where deviceID=$id and ts=(select lastSeen from device where device=$id);

as result, I will receive a list of 50 or more records, each one for a specific metric and its value.
There is some simplification here. In the real case, both tables have more columns, but you got the idea.

caveats:
The code for writing to tables device and snmpData are not on the same logical block.
It is a PHP program and the operation on table device has its own try/catch block.
If everything it is ok with the INSERT/UPDATE on device table, there is another try/block with the INSERT operation on table snmpData.
Each block has its own prepare/execute staements, so it is not possible to  use the variable trick
"SET @ts := NOW();" that @Rick James  suggested.
BTW, I loved, its new to me, and I will use it on another context. thanks)

Edit: an alternative approach

-- remove first- last- seen from device
CREATE TABLE device
( deviceID varchar(12) NOT NULL primary key
, snmpSet varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

-- add p.k. and f.k.
CREATE TABLE snmpData
( deviceID varchar(12)  NOT NULL
, ts datetime NOT NULL
, oid varchar(45)  NOT NULL
, value varchar(30)  NOT NULL
,    constraint pk_snmpData primary key (deviceID, ts) -- assumed
,    constraint fk_snmpData foreign key (deviceID)
                            references device (deviceID)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

Now, what is first- and last- seen? To me, this seems like a kind of aggregation or summary of a device. This kind of construction is sometimes named Incremental Evaluation System

CREATE TABLE device_summary
( deviceID varchar(12) NOT NULL
, firstSeen datetime NOT NULL
, lastSeen datetime NOT NULL
,   constraint pk_device_summary primary key (deviceID)
,   constraint fk_device_summary foreign key (deviceID)
                                 references device (deviceID)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

The logic for updating device_summary is easily expressed in a trigger:

DELIMITER $$
CREATE TRIGGER after_snmpData_insert
AFTER INSERT ON snmpData
FOR EACH ROW
BEGIN
    -- if device exists, insert will be ignored
    INSERT IGNORE INTO device_summary
        (deviceID, firstSeen, lastSeen)
    VALUES (NEW.deviceID, NEW.ts, NEW.ts);

    -- update lastSeen
    UPDATE device_summary
        SET lastSeen = NEW.ts
    WHERE deviceID = NEW.deviceId;
END$$
DELIMITER ;

Since a device most likely exists in device_summary, it is more efficient to update first and check for number of updated rows (if 0, insert)

CREATE TRIGGER after_snmpData_insert
AFTER INSERT ON snmpData
FOR EACH ROW
BEGIN
    -- update lastSeen
    UPDATE device_summary
        SET lastSeen = NEW.ts
    WHERE deviceID = NEW.deviceId;

    IF ( ROW_COUNT() = 0 ) THEN
        -- No rows updated, i.e. the device does not exist
        INSERT INTO device_summary
            (deviceID, firstSeen, lastSeen)
        VALUES (NEW.deviceID, NEW.ts, NEW.ts);
    END IF;
END

Edit: added a fiddle, extended extended fiddle

To get the latest data for each device:

select x.oid, x.value
from snmpData x
join device_summary y
    on x.deviceId = y.deviceId
   and x.ts = y.lastSeen;


How to solve ORA-011033: ORACLE initialization or shutdown in progress I have followed link  to solve the problem.

When it is giving ORA-00600: internal error code, arguments: [kcratr1_lostwrt] then I am using:

SQL> startup mount

the above line should return ORACLE Instance started
but it is showing "cannot start already-running Oracle - shut it down first".

ORA-00600 is Oracle's generic message for un-handled internal exceptions i.e. bugs.  Usually they require us to raise an SR with Oracle Support.    This can be a problem for sites who are using Oracle without a support contract.  In that situation we are thrown on the resources of the internet.

The problem you are experiencing is due to a corrupted block.  The top result in Google offers a simple workaround: find out more.

SQL> shutdown immediate;
SQL> startup mount;
SQL> recover database;
SQL> alter database open;

(Of course, this is only a solution if you are running in archivelog mode, and so can recover the lost block).


Help needed on database schema design I work as full stack developer, but never really spent time on Database designs. I had written code to retrieve data out of DB, but never ever created a schema from scratch. So, I thought of doing some side project to understand how database design involves.

I had decided to create a Resume Builder Application. Here are some of the features that I had thought about:

User can sign up to create his/her own resumes.
User can have multiple resumes. (only applicable for paid users, for free users only one resume)
Resume can be built from the UI (from available components) and user can save it as draft.
Resume can be published via url.
Published resumes stats like visitors count needs to be calculated.
User can delete the resume.
User can upload his/her pic.
User can add description about themselves.

Given all these, I have come up with this schema:

https://ibin.co/3CoJEfeQTuAp.png

Here are the questions I have:

I have a general knowledge on RDBMS, so my schema targets databases like Oracle, MySql etc. But can we choose NoSql for this problem? If so why?
Is my schema correct? Does it work on large data with ease?
I thought of saving the resume layouts (layouts for each resume is decided by the user via say UI) in JSON format. Is it a good practice to use JSON datatypes?
ResumeData table serves to store the user data for each resume. Is it a good to have a separate table for the same?
If the answer for the first question is RDBMS, which database you will prefer? I had decided to use PostgreSql.

Thanks a lot for spending time on answering these questions.

I would suggest not storing images in the database.
Don't store unsalted password in the database. Hint: use pgcrypto
In PostgreSQL, don't use varchar where text would otherwise work, because it's implemented the same way except it doesn't have the check.

Don't use the JSON datatype unless you have good reason. Use jsonb, which you can operate on and index.


How to setup SSL in SQL Server 2012 with own certificate? Environment: Windows 7 Professional x64 in a domain, running SQL Server 2012. I have local admin rights, so messing up the system is easy. My SQL Server instances are SQLEXPRESS and MSSQLSERVER. FQDN should be for example my-pc.mydomain.local.

Problem:

I don't know the right way to setup SSL on this SQL Server. I actually only want to setup SSL with a certificate created of my own.

Can anybody help me out here? Most of the documentation online only describes the way of going through a CA and have different knowleadge as a prerequirement.

This blog post details the entire process.

You use IIS manager to create a self signed certificate, then add it through the Certificates MMC for the computer account.

Then give SQL server's service account read permissions on the certificate, and choose the certificate in SQL Server's network configuration in configuration manager.


Maximum memory for SQL Server 2000 TB when my RAM is just 512 GB Maximum allocated for a SQL Server 2012 instance shows as 2000 TB when my RAM for machine is just 512 GB. Why is it displaying like this?

Attached is the screenshot. Please check it and help me to know on this.

Think of the "Maximum server memory" setting in your right screenshot as a limit setting. You can configure SQL Server so that it won't use any more than a certain amount.

By default, this number is effectively unlimited. (That's what your server is set to.) It's kinda like setting your own personal financial budget - by default, many of us are willing to spend an unlimited amount of money on things that we should probably avoid.

Generally, you want to cap SQL Server's memory consumption, and there are varying recommendations out there. In our SQL Server Setup Checklist, we recommend leaving 10% free for the OS as a starting point. That means you'd set SQL Server's maximum server memory to 512 * 90% = 461GB, so you'd put 461000 in the max server memory box. (It's measured in MB, not GB.)

It might seem odd to leave over 50GB of memory free, but I've seen what you people do on your SQL Servers. You remote desktop in, you run SSMS, SSIS, SSRS, SSAS, and Outlook from the SQL Server. If you run a tight ship, and you never remote desktop in, then you can go higher on max memory. (Although since you're remote desktopping in to run this stuff, you should probably leave memory free. And also, stop remote desktopping in.)


Transferring (backup/restore) from development to an online server I have a PostgreSQL database that is on my computer owned by postgres user and another database on a server, owned by a different user let's say user_server

I'm using pgAdmin.

I create a backup on my computer using pgAdmin with the default settings (custom or tar).
I connect to database on the server
I execute a restore on the server database using the backup from my computer

After doing the operations, pgAdmin says that it fails, but the database looks ok. There are sometimes issues with sequences where the start number is lower than the max corresponding ID/PK.

I checked the logs and I have multiple errors, but all of them are similar to the following:

----
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3398; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
    Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
--------

pg_restore: [archiver (db)] Error from TOC entry 217; 1259 87892 TABLE association postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  must be member of role "postgres"
    Command was: ALTER TABLE public.association OWNER TO postgres;

----

pg_restore: [archiver (db)] Error from TOC entry 216; 1259 87890 SEQUENCE association_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  must be member of role "postgres"
    Command was: ALTER TABLE public.association_id_seq OWNER TO postgres;

Looks like I'm having an issue because they are owned by 2 different users.

Can this be done from pgAdmin, like a setting to ignore users or do I need to modify something on the server?

pgAdmin backup dialog screen has option "Do not save owner".

Reference:

https://www.pgadmin.org/docs/pgadmin3/1.22/backup.html
https://www.pgadmin.org/docs/pgadmin4/3.x/backup_dialog.html


Backup-strategy for redundant data in postgresql-db. I have a (postgresql-9.3)-table that contains images as a bytea-column as well as several other columns that contain extracted information from these images. (Some feature points, descriptors). These values are currently stored in the DB because they are computationally rather expensive, so that I want to cache them in the DB.

However, I do not want to integrate these columns in the backup (or the WAL files) as they are rather large. Is there a way to do this or should I rather try another approach (as e.g. writing the additional data to the filesystem)?

One option would be to use materialized views. The main downside there is that at present there's no support for incremental refresh, so refreshing the views would be quite expensive.

Instead I suggest using UNLOGGED tables. That'll skip WAL archiving (and streaming replication) of the data. If you pass --no-unlogged-table-data it'll also omit their contents from database dumps. So you'll basically be implementing your own incrementally updated materialized views over the data.