Can mutli-threading java client cause high CPU utilization of SQL Server 2008? I am a Java Developer, not a DBA, and would like to consult about an issue encountered in high CPU utilization in the SQL Server 2008 (not in the Java app server).

The Java client uses multi-threading, to simplify:

40 threads each select / insert / update (simple SQL statements) from Table X - autocommit is ON
10 threads each select / insert / update (simple SQL statements) from Table Y and selects (again simple)  from Table X (to check for existence) - autocommit is ON

Both set of threadpools run simultaneously. Each thread is assigned to "message from a queue" (a file), loads / reads its assigned "message", and inserts / updates to its corresponding table. These 2 thread pools get connections from a single connection pool via datasouce, for example:

    <Resource auth="Container" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" maxActive="100" maxIdle="10" maxWait="10000" name="jdbc/abc_DataSource" password="p" type="javax.sql.DataSource" url="jdbc:sqlserver://NTxxx\\instyyy:12345;databaseName=dbzzz" username="u"/>

The DB environment is SQL Server 2008 R2 and is shared by other apps, but the Java client above was flagged to be the causing the CPU utilization. When the Java client was shutdown, the utilization stabilized. From the Java client's application server Jboss, the statistics where the Java was running does not have any performance issues, CPU and memory are fine; it was only the DB Server that got its CPU spiked.

My question is, does the high amount of threads, which will consume the datasource's pool of maxActive 100, potentially be the cause? If so, how can I prove this from the DB perspective, ie. SQL server statistics pointing to the high volumne threads from Java client?

Potentially - yes. Especially on a totally underscaled database server. I would expect problems with 1000 connections, not with something as low as 50.

I would start investigating the SQL sent and see for optimizations there (using precompiled statements) to take load off. Also certain kinds of sub-optimal SQL can lead to significant CPU usage (due to table scans doing tons of processing instead of using an index). For that you need to find the resource intensive queries and analze them (or ask for help here).

Generally: CPU is normally not the limiting factor of a database server, IO etc. come first. But it is possible, to answer your question. Start analyzing.

Last time I had this issue on a large oracle we used 24 cores at nearly 100%, hitting the oracle server with close to 200 threads - and the CPU limitation came out to be an oracle configuration error limiting the database server to ONE CORE. That is how efficient databases are.

And obviously the java app may not even be taxed - if all it does is sending inefficient SQL to the sql server it has not a lot to do except to wait.


What could cause an auto increment primary key to skip numbers? I have a  table created with the following T-SQL:

CREATE TABLE [dbo].[TableName](
    [id] [int] IDENTITY(1,1) NOT NULL
PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I took a look at this table today and noticed the id column was skipping values:

  1,
  2,
  3,
  4,
  5,
  6,
  7,
  8,
  9,
  10,
  11,
  12,
  13,
  14,
  15,
  16,
  17,
  18,
  19,
  20,
  21,
  22,
  23,
  24,
  25,
  26,
  27,
  1027,
  1028,
  1029,
  1030,
  1031,
  1032,
  1033,
  1034,
  1035,
  2034,
  2035,
  3034

I'm not worried about this or anything (my application doesn't care what the primary key is), I was just curious about what might cause this to occur. It does seem there's a sort of pattern, of jumping 1000 every time a "jump" happens.

My application using entity framework to insert these rows, if that makes any difference.

There are many things that can cause gaps in an IDENTITY column (rollbacks, deletes), but in this case due to the jump I suspect it is this bug - caused by the changes to IDENTITY with the introduction of SEQUENCE:

Connect # 739013 : Failover or Restart Results in Reseed of Identity

So I bet that if you look in SQL Server's error logs, the rows associated with these jumps were inserted shortly after a SQL Server service restart, a database was detached/re-attached, etc.

Really, though, why do you care about gaps? You shouldn't. If you need a contiguous sequence of numbers, stop using IDENTITY.


SSRS 2008 Broken in Chrome Note, this is 2008, not 2008 R2.

This isn't an unknown problem, but I can't seem to get it to work. Seems like all of the solutions I've tried relate to 2008 R2 and above versions. Chrome updated and now our reports are showing in a small window instead of a full browser window.

I've searched high and low for a solution for this one. This seems to be the universal solution but it doesn't want to work, even after restarting SSRS:

SSRS 2008 R2 - SSRS 2012 - ReportViewer: Reports are blank in Safari and Chrome

I'm at a loss on this one. Does 2008 R2 differ that much in terms of reporting services configurations? The easy answer here is to use IE for reports, but our SAAS apps run better on Chrome and link to our reporting services reports.

Other fixes tried:

Anything from I could find from the Chrome Store: SSRS Fix, IE Tab which works, but we have over 75 machines and buying licensing right now isn't an option

To anyone who cares,

Our SQL 2008 instance was not on SP3. After installing SP3 everything looks to have taken care of itself.


How to select functions that belong in a given extension in PostgreSQL? I am trying to compose a SELECT from pg_catalog that pulls function name, its schema name and the name of extension that created this function.

To pull function list I use (a simplified version)

SELECT pg_proc.proname, pg_namespace.nspname AS proschema
FROM pg_proc
    INNER JOIN pg_namespace ON (pg_namespace.oid = pg_proc.pronamespace)

To select extensions I can run

SELECT pg_extension.extname, pg_namespace.nspname as extschema
FROM pg_catalog.pg_extension
    INNER JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = pg_extension.extnamespace)

Now I am looking for a link between extensions and functions they create when "CREATE EXTENSION". The SELECT should ideally produce a list of extension_name, extension_schema, foo_name, foo_schema

All credit for this answer goes to LaurenzAlbe who hinted the solution in the comment. The query that lists functions created by an extension is

SELECT e.extname, ne.nspname AS extschema, p.proname, np.nspname AS proschema
FROM pg_catalog.pg_extension AS e
    INNER JOIN pg_catalog.pg_depend AS d ON (d.refobjid = e.oid)
    INNER JOIN pg_catalog.pg_proc AS p ON (p.oid = d.objid)
    INNER JOIN pg_catalog.pg_namespace AS ne ON (ne.oid = e.extnamespace)
    INNER JOIN pg_catalog.pg_namespace AS np ON (np.oid = p.pronamespace)
WHERE d.deptype = 'e'
ORDER BY 1, 3


Disk space unreleased after cleaning up rows from PG table I understand space isn't abruptly released (unless one does TRUNCATE). But this one looks abnormal to me.

What could be the reason for a postgresql (9.3.10) table not releasing space after having 70% of its records removed?

I have a table called user_sessions_session. As the name implies, it stores sessions data for each user of a web app.

It's original size was:

              Table               |  Size   | External Size
----------------------------------+---------+---------------
 user_sessions_session            | 15 GB   | 13 GB

I thereafter deleted all user sessions older than 3 months ago. That was the majority of the rows in the table. This was 3 days ago. I just checked the table size again, here's what I see:

              Table               |  Size   | External Size
----------------------------------+---------+---------------
 user_sessions_session            | 15 GB   | 13 GB

Moreover, select * from pg_stat_activity where query like 'autovacuum:%'; reveals no vacuuming is going on at this point.

Btw I've had this same issue with this same sessions table before - it's not a one off.

In case it matters, here's the SQL I used to get table sizes (user_sessions_session showed up as number 1 in the list):

SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

From the docs,

  In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row. This approach is necessary to gain the benefits of multiversion concurrency control (MVCC, see Chapter 13): the row version must not be deleted while it is still potentially visible to other transactions. But eventually, an outdated or deleted row version is no longer of interest to any transaction. The space it occupies must then be reclaimed for reuse by new rows, to avoid unbounded growth of disk space requirements. This is done by running VACUUM.

At all. Never. There are no exceptions. When ever you delete a row, you essentially mark it as inactive for your snapshot. Other snapshots started by prior transactions can still see it. This means it's still on disk.

Rows aren't stored in their own file on disk. In order to delete that row, even when you want, you have to essentially rewrite the files stored on disk without that row. DELETE does not do this and under normal circumstances that's perfectly fine because dead rows that aren't visible to a transaction can be reclaimed internally by  a simply VACUUM. After being marked for reuse new data can be stored in the heap.

So essentially, only operations that internally cause the table's heap to be rewritten reclaim space. Those operations are:

CLUSTER
VACUUM FULL
TRUNCATE
ALTER TABLE (forms that rewrite the table)

All of these operations require an ACCESS EXCLUSIVE lock. And, TRUNCATE is such a sledge hammer it even violates MVCC.

  @EvanCarroll: and so technically, I can actually go out of diskspace even after having removed a ton of rows here? – Hassan Baig 1 min ago

Yea, really and technically and everything else, but let's make one special caveat to the above. If you DELETE a bunch of rows and commit they get marked as inactive. When you VACUUM the table's heap space gets reclaimed. From that point, to run out of disk space you to either

Exhaust that reclaimed space on that table's heap because PostgreSQL will just store stuff in that space.
Or, exhaust a different table's heap.

But yes, VERY bad things can happen when you exhaust your disk space. And, they'll happen with every MVCC database vendor. Simply, don't run of disk space.


You can use filtered aggregation for this:
select v.poll_id,
       count(*) filter (where v.option = p.option_a) as option_a,
       count(*) filter (where v.option = p.option_b) as option_b,
       max(p.created_at) as created_at
from votes v
  join polls p on p.id = v.poll_id
group by v.poll_id
order by v.poll_id;

The max(p.created_at) is necessary to make the group by happy.
Online example


Microsoft Links for SQL Capacity planning I went through this website because we have migrations coming up pretty soon for Landesk Management Suite.

We are planning to move to SQL 2014 but the site's contents are mostly relevant to SQL 2005.

Are there any other good websites or documentation from microsoft which I can go through to estimate I/O, disk and CPU planning before the Migration?

The url which I went through was this

Editing. I am trying this tsql code to get min,max and avg size of databases since 2015. Would this help?

SET NOCOUNT ON
DECLARE @endDate datetime, @months smallint;
SET @endDate = GetDate();  -- Data atual
SET @months = 12;          -- Nr. de meses a analisar

;WITH HIST AS
   (SELECT BS.database_name AS DatabaseName
          ,YEAR(BS.backup_start_date) * 100
           + MONTH(BS.backup_start_date) AS YearMonth
          ,CONVERT(numeric(10, 1), MIN(BS.backup_size / 1048576.0)) AS MinSizeMB
          ,CONVERT(numeric(10, 1), MAX(BS.backup_size / 1048576.0)) AS MaxSizeMB
          ,CONVERT(numeric(10, 1), AVG(BS.backup_size / 1048576.0)) AS AvgSizeMB
    FROM msdb.dbo.backupset as BS
    WHERE NOT BS.database_name IN
              ('master', 'msdb', 'model', 'tempdb')
          AND BS.type = 'D'
          AND BS.backup_start_date
              BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate
    GROUP BY BS.database_name
            ,YEAR(BS.backup_start_date)
            ,MONTH(BS.backup_start_date))
SELECT @@SERVERNAME
      ,MAIN.DatabaseName
      ,MAIN.YearMonth
      ,MAIN.MinSizeMB
      ,MAIN.MaxSizeMB
      ,MAIN.AvgSizeMB
      ,MAIN.AvgSizeMB
       - (SELECT TOP 1 SUB.AvgSizeMB
          FROM HIST AS SUB
          WHERE SUB.DatabaseName = MAIN.DatabaseName
                AND SUB.YearMonth < MAIN.YearMonth
          ORDER BY SUB.YearMonth DESC) AS GrowthMB
FROM HIST AS MAIN
ORDER BY MAIN.DatabaseName
        ,MAIN.YearMonth

What I would think to do is see how your current server is performing by benchmarking the older hardware. This will allow you to get a good baseline so you do not go backwards from the migration.

To really hammer the disks have a few workstations use CrystalDiskMark and all point to the server, this will allow you to get concurrency testing also to simulate load.

As far as CPU, I default to Perfmon to see how the CPUs are holding up on the older hardware. If they are getting hammered then you either need to get more CPU or/and start tuning.

For the new server, Brent Ozar Unlimited has a first responder kit that has a good setup guide for SQL Server. However, he has a section in the setup guide that is a bit outdated, "Format the drives with 64K allocation blocks". Honestly, I don't believe this applies anymore :)

You can get this guide and a bunch of other goodies at First aid.

When you have the new server online, I would then start to have your QA team run through testing to make sure that everything works and that you are also getting the same or better performance from the server. Remember in 2014 we have a new cardinality estimator along with other features.


Correlated subqueries. Count Visits after Last Purchase Date I'm pretty new to SQL and have been trying to solve this task for a while...still no luck. I would appreciate if someone here could help me out.

I have a database with columns:

ClientID
VisitID
Date
PurchaseID (array)
etc.

What I'm trying to achieve is to retrieve a list containing the following data:

ClientID
Last Visit Date
First Visit Date
Last Purchase Date
Visits Count
Purchases Count
Visits After Last Purchase Count

When trying to retrieve a value for Visits After Last Purchase Count this is where I am stuck.

SELECT
ClientID,
FirstVisit,
LastVisit,
LastPurchaseDate,
Visits,
Purchases,
VisitsAfterPurchase
FROM
(
SELECT
    h.ClientID,
    max(h.Date) AS LastVisit,
    min(h.Date) AS FirstVisit,
    count(VisitID) AS Visits
FROM s7_visits AS h
WHERE Date > '2017-12-01'
GROUP BY h.ClientID
LIMIT 100
)
ANY LEFT JOIN
(
SELECT
    d.ClientID,
    max(d.Date) AS LastPurchaseDate,
    sum(length(d.PurchaseID)) AS Purchases,
    sum(
    (
        SELECT count(x.VisitID)
        FROM s7_visits AS x
        WHERE x.ClientID = d.ClientID
        HAVING x.Date >= max(d.Date)
    )) AS VisitsAfterPurchase
FROM s7_visits AS d
WHERE (length(PurchaseID) > 0) AND (Date > '2017-12-01')
GROUP BY d.ClientID
) USING (ClientID)

The database system I'm using is Yandex ClickHouse.
The USING syntax is absolutely normal for ClickHouse. It is used instead of ON clause in other RDBMSs.

This query is giving me the following error:

DB::Exception: Column Date is not under aggregate function and not in GROUP BY..

Sample Data:

  +----------+---------+------------+------------+
  | CliendID | VisitID |    Date    | PurchaseID |
  +----------+---------+------------+------------+
  |      123 |     136 | 01.12.2017 |            |
  |      123 |     522 | 05.12.2017 |            |
  |      123 |     883 | 08.12.2017 |            |
  |      123 |     293 | 09.12.2017 | ['345']    |
  |      123 |     278 | 12.12.2017 |            |
  |      123 |     508 | 12.12.2017 |            |
  |      123 |     562 | 15.12.2017 |            |
  |      123 |     523 | 21.12.2017 |            |
  |      456 |     736 | 29.11.2017 |            |
  |      456 |     417 | 03.12.2017 |            |
  |      456 |     950 | 04.12.2017 |            |
  |      456 |     532 | 05.12.2017 | ['346']    |
  |      456 |     880 | 09.12.2017 |            |
  |      456 |     296 | 12.12.2017 |            |
  |      456 |     614 | 15.12.2017 |            |
  +----------+---------+------------+------------+

And the result should be:

  +----------+-----------------+------------------+--------------------+--------------+-----------------+----------------------------------+
  | ClientID | Last Visit Date | First Visit Date | Last Purchase Date | Visits Count | Purchases Count | Visits After Last Purchase Count |
  +----------+-----------------+------------------+--------------------+--------------+-----------------+----------------------------------+
  |      123 |      21.12.2017 |       01.12.2017 |         09.12.2017 |            8 |               1 |                                4 |
  |      456 |      15.12.2017 |       29.11.2017 |         05.12.2017 |            7 |               1 |                                3 |
  +----------+-----------------+------------------+--------------------+--------------+-----------------+----------------------------------+

Problem solved.
Unfortunately ClickHouse does not support aliases for subqueries and hence referencing. So I had to find another workaround using SELECT from subquery (with LastVisitDate) LEFT JOINed with another subquery (with LastPurchase Date) containing and then using WHERE clause "LastVisitDate < LastPurchaseDate".


Rows deleted in the subscriber - what to do? - SQL Server Transactional Replication Someone has delete by mistake a row of a table in the subscribed server/database.

I am getting the following error message:

  The row was not found at the Subscriber when applying the replicated
  (null) command for Table '(null)' with Primary Key(s): (null) (Source:
  MSSQLServer, Error number: 20598)

Now I have restored the deleted row in the replicated server, but the distributor to the subscriber agent is taking so long to apply the changes (currently nearly 1.5 hours).

How can I make it do this update immediately?

If you have mistakenly removed a row on the subscriber that should receive an update through replication, the easiest way to fix would be to re-construct the missing row on the subscriber that matches the row that is failing to update.

Once the row is on the subscriber, the update can run successfully (even if it just writes the same value into the desired column), then replication should continue happily.

I have had this same scenario occur in the past and replication continued without issue.


SELECT DISTINCT on multiple columns Supposing we have a table with four columns (a,b,c,d) of the same data type.

Is it possible to select all distinct values within the data in the columns and return them as a single column or do I have to create a function to achieve this?

Update: Tested all 5 queries in SQLfiddle with 100K rows (and 2 separate cases, one with few (25) distinct values and another with lots (around 25K values).

A very simple query would be to use UNION DISTINCT. I think it would be most efficient if there is a separate index on each of the four columns It would be efficient with a separate index on each of the four columns, if Postgres had implemented Loose Index Scan optimization, which it hasn't. So this query will not be efficient as it requires 4 scans of the table (and no index is used):

-- Query 1. (334 ms, 368ms)
SELECT a AS abcd FROM tablename
UNION                           -- means UNION DISTINCT
SELECT b FROM tablename
UNION
SELECT c FROM tablename
UNION
SELECT d FROM tablename ;

Another would be to first UNION ALL and then use DISTINCT. This will also require 4 table scans (and no use of indexes). Not bad efficiency when the values are few, and with more values becomes the fastest in my (not extensive) test:

-- Query 2. (87 ms, 117 ms)
SELECT DISTINCT a AS abcd
FROM
  ( SELECT a FROM tablename
    UNION ALL
    SELECT b FROM tablename
    UNION ALL
    SELECT c FROM tablename
    UNION ALL
    SELECT d FROM tablename
  ) AS x ;

The other answers have provided with more options using array functions or the LATERAL syntax. Jack's query (187 ms, 261 ms) has reasonable performance but AndriyM's query seems more efficient (125 ms, 155 ms). Both of them do one sequential scan of the table and do not use any index.

Actually Jack's query results are a bit better than shown above (if we remove the order by) and can be further improved by removing the 4 internal distinct and leaving only the external one.

Finally, if - and only if - the distinct values  of the 4 columns are relatively few, you can use the WITH RECURSIVE hack/optimization described in the above Loose Index Scan page and use all 4 indexes, with remarkably fast result! Tested with the same 100K rows and approximately 25 distinct values spread across the 4 columns (runs in only 2 ms!) while with 25K distinct values it's the slowest with 368 ms:

-- Query 3.  (2 ms, 368ms)
WITH RECURSIVE
    da AS (
       SELECT min(a) AS n  FROM observations
       UNION ALL
       SELECT (SELECT min(a) FROM observations
               WHERE  a > s.n)
       FROM   da AS s  WHERE s.n IS NOT NULL  ),
    db AS (
       SELECT min(b) AS n  FROM observations
       UNION ALL
       SELECT (SELECT min(b) FROM observations
               WHERE  b > s.n)
       FROM   db AS s  WHERE s.n IS NOT NULL  ),
   dc AS (
       SELECT min(c) AS n  FROM observations
       UNION ALL
       SELECT (SELECT min(c) FROM observations
               WHERE  c > s.n)
       FROM   dc AS s  WHERE s.n IS NOT NULL  ),
   dd AS (
       SELECT min(d) AS n  FROM observations
       UNION ALL
       SELECT (SELECT min(d) FROM observations
               WHERE  d > s.n)
       FROM   db AS s  WHERE s.n IS NOT NULL  )
SELECT n
FROM
( TABLE da  UNION
  TABLE db  UNION
  TABLE dc  UNION
  TABLE dd
) AS x
WHERE n IS NOT NULL ;

SQLfiddle

To summarize, when the distinct values are few, the recursive query is the absolute winner while with lots of values, my 2nd one, Jack's (improved version below) and AndriyM's queries are the best performers.

Late additions, a variation on the 1st query which despite the extra distinct operations, performs much better than the original 1st and only slightly worse than the 2nd:

-- Query 1b.  (85 ms, 149 ms)
SELECT DISTINCT a AS n FROM observations
UNION
SELECT DISTINCT b FROM observations
UNION
SELECT DISTINCT c FROM observations
UNION
SELECT DISTINCT d FROM observations ;

and Jack's improved:

-- Query 4b.  (104 ms, 128 ms)
select distinct unnest( array_agg(a)||
                        array_agg(b)||
                        array_agg(c)||
                        array_agg(d) )
from t ;


Replica Set Oplog Maxsize I have two databases (Mongodb 3.6.5) using ReplicaSet, when I made a split in the network, I am forcing the secondary database to be the primary.

cfg = rs.conf()
cfg.members[0].votes = 0
cfg.members[1].votes = 1
cfg.members[0].priority = 0
cfg.members[1].priority = 1
rs.reconfig(cfg, { force: true })

I already tested insert in the secondary acting as primary and when returning the network and back to secondary to be secondary, the bases were replicated, the original primary was equal to secondary.

Now I unplugged the network again and did several operations on the secondary machine transformed into primary until the size of the Oplog exceeds the maxSize. After some time without doing operations, the size of the Oplog decreased somewhat. I did some more operations to increase the size value of Oplog but the same is not growing as before.

My next step is to return the network, return the secondary as a secondary, and see if it synchronizes.

I would like to know how does this Oplog work?

I have two databases (Mongodb 3.6.5) using ReplicaSet, when I made a split in the network, I am forcing the secondary database to be the primary.

Forced reconfiguration should only be used as an administrative last resort. This is a risky approach with only two replica set members as you can easily create data challenges that will require significant manual recovery effort.

If you have a network partition and inadvertently reconfigure both sides to have a primary, you will have to deal with rollbacks and manual reconcilation of conflicting writes. The worst case scenario would be having enough independent writes for the oplogs to no longer have a common point: you would have to choose one member to use as the primary and then resync the other.

  Now I unplugged the network again and did several operations on the secondary machine transformed into primary until the size of the Oplog exceeds the maxSize. After some time without doing operations, the size of the Oplog decreased somewhat. I did some more operations to increase the size value of Oplog but the same is not growing as before.

The replication oplog is a system capped collection whose implementation varies somewhat based on the underlying MongoDB storage engine and version of MongoDB server. Conceptually a capped collection is maintained in insertion order with a maximum size in bytes or documents. You can think of this as similar to a circular buffer: once the capped collection reaches its maximum size, the storage engine removes or overwrites the oldest documents based on insertion order.

The capped collection implementation for the oplog has some additional nuances (as compared to a standard capped collection) to improve performance for the replication use case. WiredTiger (the default storage engine in MongoDB 3.2+) does not preallocate the full space for the oplog so this collection will grow until it approaches the maximum oplog size. WiredTiger has a background thread that periodically prunes a batch of the oldest entries from the oplog.rs collection, so the size of this collection on disk is expected to fluctuate depending on overall write activity.


Query hanging in ClientRead and blocking all others This is related to Debugging a hanging session/lock

Every so often (~1-2 times a month) we have our database lock up because there is one query that doesnt finish, and that has requested locks that block all others. This is on a table that is core to our business, so it results in downtime.

The query in question is one of a couple of candidates, all of them are just simple UPDATEs running on just 1 row. The best lead I have so far (I am open to other avenues) is from RDS performance insights I see that while all other queries are locked on "tuple", there is always 1 query that is locked on "ClientRead". From my research, this appears to be Postgres waiting on the client lib to send it the bound parameters or similar. So my theory is that there is one query that randomly sometimes ends up in this state, and this blocks all others after it. Is this a reasonable evaluation?

If this is the case, how can I go into investigating and resolving it? We use sequelize 5.9.4 (node-js). We did upgrade it recently, ~1-2 months back, so I am wondering if there might be a bug in this version which might cause a error on the sequelize side when processing a query, is that possible? It could be a bug in node-postgres as well, this came up in my searching https://github.com/brianc/node-postgres/issues/1952.

I will try to update with a screenshot of RDS performance insights next time it happens, its retention was shorter than anticipated.

You have a problem with transaction management.

A session that waits for ClientRead is done processing the last query and waits for the client to send the next request. The only way that such a session can block anything is if its state is idle in transaction. All locks are held until the transaction ends, and no locks are held once the transaction finishes.

If sessions remain in state idle in transaction for a longer time, that is always an application bug where the application forgot to end the transaction. This is bad, not only for the reason you observe, but also because it keeps autovacuum from doing its essential job.

There are two things you can do:

Fix the application so that it properly closes transactions. This is the only good solution.
Prevent the worst from happening by setting idle_in_transaction_session_timeout so that these transactions are automatically rolled back by PostgreSQL after a while. This will keep locks from being held indefinitly, but the buggy application will receive an error.


SQL Server AlwaysOn Availablity Group Automatic seeding I created an AlwaysOn in SQL Server 2019 and I selected Automatic seeding, but it does not create my database in secondary nodes, On the other hand, if I create my database manually I give and error in which your database already exist ! what is the solution?

You should be able to see the reason automatic seeding didn't complete by looking in the SQL Server error log from the time period where you created the AG (on both the primary and secondary replicas).

One common cause for this failure is that the Availability Group doesn't have the "CREATE ANY DATABASE" permission on the secondary.  To correct that, you need to go to each secondary replica and run this command:

ALTER AVAILABILITY GROUP yourAgName
GRANT CREATE ANY DATABASE

Another place to look is the automatic seeding DMV.  This query is from the MS Docs:

SELECT start_time,
    completion_time
    is_source,
    current_state,
    failure_state,
    failure_state_desc
FROM sys.dm_hadr_automatic_seeding

Check out the failure reason for clues as to why automatic seeding isn't working.

After correcting any issues you find, you can retry automatic seeding by running this code on the primary:

ALTER AVAILABILITY GROUP yourAgName
MODIFY REPLICA ON 'SecondaryReplicaName'
WITH (SEEDING_MODE = AUTOMATIC);


Access Form: New Record command not available now I have an access database with two forms. When the form is opened I got a pop up saying acNewRec command is not available now. I can hit end or debug. If I hit end and manually re-open the form it works properly. It just gives the error eveytime the access is open. Any suggestions on how to fix this?

Private Sub form_load()
  DoCmd.Maximize
End Sub

Private Sub form_timer()
  [Clock1] = Now
End Sub

Private Sub form_open(cancel As Integer)
  DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub virusscanopen_click()
  DoCmd.Close Form.me.Name
  DoCmd.OpenForm "IT DEPARTMENT VIRUS SCAN FORM"
End Sub

Private Sub SIGN_IN_SUBMIT_Click()
  Me.Refresh

  If IsNull([LAST NAME].Value) Or IsNull(grade.Value) Or IsNull(issue.Value) Or IsNull(office.Value) Then
    Msbox "PLEASE ENTER ALL DATA FIELDS", vbCritical, "IT DEPARTMENT CUSTOMER INFO"
  Else

    txtmessage = grade.Value + Space(1) + [LAST NAME].Value

    MsgBox "Thank you" + Space(1) + (txtmessage) + "!" + Space(1) + vbNewLine_ + "Have a great day!" + vbNewLine_ + "Someone will assist you shortly." + vbNewLine_, vbInformation, "IT DEPARTMENT CUSTOMER INFO"

    [Date].Value = Format(Now, "mm/dd/yyyy")
    [Time].Value = Format(Now, "hh:mm:ss AM/PM")

    DoCmd.GoToRecord , , acNewRec

    Me.Refresh
  End If
End Sub

The fact that the error message said "acNewRec command is not available now" should have directed you to the line DoCmd.GoToRecord , , acNewRec.  The form's recordset is not guaranteed to be open and positioned correctly during the Form_Open() event, especially not always accessible via DoCmd methods.  Documentation says otherwise, but experience shows that there are potential problems with relying on the recordset to be in a reliable state in Form_Open.

There are other ways to make the form automatically open on a new record.  Try putting the same line of code within the Form_Load() event.  Setting the form's Data Entry value to Yes allows only new records.


Be aware of the implications in regions that have the nasty habit of changing their clocks twice a year and Check this Link


Need to identify non contigious rows My table structure is as follows:

create table daily_summary
(
    id                 uuid           default uuid_generate_v4() not null
            primary key,
    machine            bigint                                    not null
    odometer_start     numeric(10, 3)                            not null,
    odometer_end       numeric(10, 3)                            not null,
    distance           numeric(10, 3)                            not null,
    date               timestamp                                 not null,
    ...

);

The data in the daily_summary table should be contigious for a machine, ie the odometer_end for a row for a machine should be the odometer_start for the same machine in the next row when ordered by date.

Is there a query using lead() or any other way that I can use to identify non contigious row?

Something like this:

select *
from (
  select ds.*,
         lag(ds.odometer_end) over (partition by ds.machine order by ds."date") as prev_end
  from daily_summary ds
) t
where odometer_start <> prev_end + 1
order by "date"


Linked Server to Oracle error ORA-01804 Migrating SQL Server to a new machine. Migration involves setting up a number of linked servers, some SQL Server, Oracle and Teradata. No issues with SQL Server or Teradata servers, but every Oracle server I have tried has the ORA-01804 issue. The actual message is "Error while trying to retrieve text for error ORA-01804" but I figure the ORA-01804 is the issue.
TNSPING to the name in @datasrc uses LDAP to resolve and resolves fine. (note: there is no  tnsnames.ora file, ldap.ora and sqlnet.ora files were copied from the source server where everything is working.)
TOAD for Oracle has no trouble connecting to the database using that name as well.
SQL Server (source server is 2016, target is 2019, dev machine but same result on actual server):
SQL Server: 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 18363: ) (Hypervisor)

Oracle:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.9.0.0.0

I had scripted the linked server connection from the server being migrated so everything should be equal (names changed to protect the innocent):
EXEC master.dbo.sp_addlinkedserver
    @server = N'ORACLE_SERVER',
    @srvproduct=N'Oracle',
    @provider=N'OraOLEDB.Oracle',
    @datasrc=N'orasrv'

EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname=N'ORACLE_SERVER',
    @useself=N'False',
    @locallogin=NULL,
    @rmtuser=N'orasrv',
    @rmtpassword='redacted'

I'll skip the options, but they are there as well.

To test Linked Server connectivity, first reboot, and then test OleDb connectivity with 64-bit PowerShell.  EG
PS C:\> $con = new-object system.data.oledb.oledbconnection("provider=OraOledb.Oracle;data source=N'ORACLE_SERVER;user id=xxxxxxx;password=xxxxxxx")
PS C:\> $con.Open()


MySQL Restore from a Year Old Backup I have two backups created via mysqlhotcopy:

backup-before-deletion from August 7, 2014 and
backup-after-deletion from February 3, 2016

A deletion event occurred on July 17, 2015 which resulted in the deletion of about 15,000 rows across 7 different tables.

Because this segment of data was hardly ever touched it wasn't noticed until a recent audit.

The good news is the deletion event can be simulated but it would have to be done on the backup-before-deletion.

What is the most cost effective way (paid for tools are on the table) to restore the deleted data?

Consider

Load the data into a different database, or even a different MySQL instance.
Locate the deleted rows.
Export them in some manner -- INSERT .. SELECT .. into another table; SELECT INTO OUTFILE ..., mysqldump, etc.
Load them into your real database.

Cost:  A bunch of your time.


How much free space do I need for mongoDB? and how to reclaim lost space? I ran out of disk space on my mongo database. I saw it and dropped some collections (120 out of 1700 i had). And ran db.compact(...) thinking it would release free space. well, that was a stupid idea.

Anyway, the database is around 800GB, and I dont have any space (~5GB) in the current disk. I symlinked a folder from another disk that has around 35GB and tried db.repairDatabase() (and mongod --repair --repairpath ...), but that failed too saying not enough space. What else can I do? short of getting another 1TB HDD?

Why is even 35GB not enough? Since I dropped the collections, I know I should get another ~50GB free after the repair. Is that going to be enough?

The partition I use for data would have around ~800GB for mongo. How much free space should I ideally have?

Mongo says I need working set+2GB free space. But no queries are running and mongod stopped. So I'm not sure why 35GB is not enough.

There are two operations here
1. Compact - This is an in place compact of data inside the data files. This will however not release any of the data files. Even thought it does not return the free space to the file system mongo should be able to use the space for growth.
2. Repair - This will release any unused space back to the filesystem. This is what you need. However repair requires the same amount of space as your database. So if you want to repair you will 800GB + few extra GB for repair to complete.


Which stored procedures are using a specifc index I've been reviewing the indexes in our databases (adding missing indexes and removing bad ones). I stumbled on an index I created months ago which looks way to good to be true. Since the last server reboot (about 2 weeks ago) there had been only 109 writes to it, but nearly 2.7 million reads from it. Normally when I see more reads than writes I put it down as a good job and move on to the next, but this one just looks too good to be true.

So my question: It there a way to determine which store procedures are using the index? Other than reading through all stored procedures manually and checking joins and where conditions.

I couldn't quite get Jonathan's XML query to pull this out for me, but I was able to get it by parsing showplan_text the simple way. This is using AdventureWorks2012 and after issuing queries against Sales.SalesOrderHeader:

DECLARE @Index SYSNAME = N'PK_SalesOrderHeader_SalesOrderID';

SELECT t.[text], s.execution_count, CONVERT(XML, p.query_plan)
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_text_query_plan
(
  s.plan_handle, s.statement_start_offset, s.statement_end_offset
) AS p
CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS t
WHERE p.query_plan LIKE '%Index="\[' + @Index + '\]"%' ESCAPE '\';

Results:

To get stored procedure names specifically (for those queries that are not issued ad-hoc), you'll want to make sure you are in the context of your database, and then add this column to the output list of the above query:

, obj = QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid))
  + '.' + QUOTENAME(OBJECT_NAME(t.objectid))

This column will be NULL for any ad-hoc queries.

Once the query plan is in the results pane, you can of course click on the XML to load the graphical plan. And then if you have SentryOne Plan Explorer and the SSMS add-in installed, you can right-click the graphical plan to view in Plan Explorer.

Note that the query won't return results for queries issued from another database, so you should repeat the query in the context of any database that might be used in conjunction with the target database.

In contrast, Jonathan's query should be able to retrieve queries in the plan cache that reference any index with the specified name (there may be multiple). You can further filter to find references to only this specific one, even if those queries originated in other databases.


PDO count(*) does not work with LIMIT and OFFSET here's my code
it works normally, but this time i want to also count the records it gives. at the point of inserting COUNT(*) it will give NULL value to counted rows

        $query_list_records = "SELECT COUNT(*) from myl_news_comments WHERE news_id=:news_id LIMIT :ajax_read_how_many_comments OFFSET :items_already_loaded";
        $result_list_records = $db->prepare($query_list_records);
        $result_list_records->bindValue(':news_id', $_POST['news_id'] , PDO::PARAM_INT);
        $result_list_records->bindValue(':ajax_read_how_many_comments', (int)$ajax_read_how_many_comments , PDO::PARAM_INT);
        $result_list_records->bindValue(':items_already_loaded', (int)$_POST['items_already_loaded'] , PDO::PARAM_INT);
        $result_list_records->execute();
        $total_records = $result_list_records->fetchColumn();

echo $total_records;

how can i make this work ?

thanks
have a great night

Within the query itself, trying separating the COUNT(*) from the retrieval by rewriting the query like this:

SELECT COUNT(*) from (SELECT myl_news_comments WHERE news_id=:news_id LIMIT :ajax_read_how_many_comments OFFSET :items_already_loaded) A

Your code should look like this now:

        $query_list_records = "SELECT COUNT(*) from (SELECT myl_news_comments WHERE news_id=:news_id LIMIT :ajax_read_how_many_comments OFFSET :items_already_loaded) A";
        $result_list_records = $db->prepare($query_list_records);
        $result_list_records->bindValue(':news_id', $_POST['news_id'] , PDO::PARAM_INT);
        $result_list_records->bindValue(':ajax_read_how_many_comments', (int)$ajax_read_how_many_comments , PDO::PARAM_INT);
        $result_list_records->bindValue(':items_already_loaded', (int)$_POST['items_already_loaded'] , PDO::PARAM_INT);
        $result_list_records->execute();
        $total_records = $result_list_records->fetchColumn();

echo $total_records;

Give it a Try !!!


SQL Server Error 833 On LDF File I'm getting error 833 on my ldf file and i'm getting it a lot.

Please see the following. When this happens, my application is hang already.

I know error 833 has been reported a lot, but what does it mean if it is consistently pointing to my LDF file which it could not access?
Is it because the LDF file is already large?

I'm using SQL Server 2008 R2 on a windows 2008 environment.
The database is in a state in which it is unresponsive, a restart was the only that removed the hang I/O. Once it started, it didn't end already.

What's a possible explanation for this? Please see the log files in the sql error logs.

MIght it also be because of this bug?

http://support.microsoft.com/kb/2708012

Btw, the sql server was unresponsive the whole time, and this happened during the night time. Only the restart made the server responsive again.

01/21/2013 12:22:09,spid3s,Unknown,SQL Server has encountered 19 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\MSSQL.1\MSSQL\DATA\test_database_log.LDF] in database [test_database] (6).  The OS file handle is 0x00000000000008B8.  The offset of the latest long I/O is: 0x00001028a8ac00
01/21/2013 12:15:29,spid3s,Unknown,SQL Server has encountered 17 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\MSSQL.1\MSSQL\DATA\test_database_log.LDF] in database [test_database] (6).  The OS file handle is 0x00000000000008B8.  The offset of the latest long I/O is: 0x00001028c6ac00

Error 833 is not an 'error' per-se. It is reported because your IO subsystem is slow. the size of the LDF has probably nothing to do with it. It can be caused by many factors:

an inherently slow drive
a flaky hardware that masks errors with many retries (typical of consumer grade drives)
the drive is experience more load that it can bear

You need to investigate by looking at the drive load (Perfmon Physical Disk Object), at any OS reported errors for it (Event Viewer), check the drive SMART status. Also investigate the SQL IO load and how much goes to the problem drive, see sys.dm_io_virtual_file_stats


What permission is needed to set Database read-only? I am dbowner of a database which I want to set read-only. (through sql management studio). When I try that I get a permission error (297)

Which role is needed to perform that action?

Form Query Analyzer or Management Studio, you can issue the following command:

ALTER DATABASE database-name SET READ_ONLY

As mentioned here, you need the alter database rights and you must set the database to single mode.

  Changing the state of a database or filegroup to READ_ONLY or
  READ_WRITE requires exclusive access to the database.

USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO


Optimize BETWEEN Query in SQL Server Azure I have a table that stores time-series (5-minute) data and am trying to run a query that has a BETWEEN clause included in it. Below is the table structure:-
Timestamp | ComponenentID | Parameter1 | Parameter2 | Parameter3
The table has an index on ComponentId and also a Clustered Columnstore index (Azure S3 and above get this feature).
The query I am trying to run:-
SELECT * FROM table
WHERE Timestamp BETWEEN '2020-01-01'
AND '2020-01-02'

I am looping through multiple similar tables to fetch data and sometimes it takes about 30 seconds to get one day worth of data. Is there anything I can do to reduce this time?

To make this query run fast you will need the data ordered by timestamp.
You have a few options
If you use a clustered columnstore index you need to FIRST order the data in the table by adding a clustered index on Timestamp and then create the columnstore index using maxdop = 1 and with drop_existing.
full syntax https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/performance-tuning-ordered-cci
You could get rid of the columnstore index and just use a clustered index on Timestamp.
You could use a nonclustered index on Timestamp and include all the other indexes in the table (might be OK if you only have 5 columns depending on the datatypes).
You could partition the table by timestamp - this is another longer conversation


MongoDB Replica Set Sync data by Copying Data Files from Another Member I used MongoDB 3.6 and was following MongoDB documentation:

https://docs.mongodb.com/manual/tutorial/resync-replica-set-member

to do a full copy of data from Primary node to another mongod instance. I copied both admin and local plus all database folders into the new node. After all data are ready, added new node into replica set. It seems the replica set starts another initial sync and copy data from Primary to the new node. I got 400 GB data in primary node, so after a few hours Secondary node data size reach 600 GB and no disk space left and cause the sync fail. So it seems MongoDB still copying all data from Primary to Secondary even after I did this data copy manually. More surprisingly, it keeps the data I copied and continue to do its own data sync. Any suggestion?

The way you perform the resync isn't right. To perform resync of the secondary member (assuming it is already a member of the replica set), bring down (stop mongod process) the secondaray node, delete all the folders from the /data directory including journal and restart the node so it re-joins the replica set again

Once it re-joins the replica set, mongodb will automatically copy all the data files including local database to the secondary. You don't need to copy admin or local database manually as mongo will take care of it

Thank you!

-Anban


MySQL - Master to Slave setup question i have now setup a Master => Slave setup, after hard fightning its now working and this is great.

the next step is, how can tell the slave if i create a new table/stroed procured or function on my Master its need to copy that to?

right now i need to run my sync script for on the master and the slave, i think if i got a lot of slaves this will be a big problem, so i hope there are a friendly person there can explain what to do now?

so what i need is i'm only need to consentrat about my Master setup, and lat the slaves copy every thing from my master and not only the row's i, can i that? and how.

The intent of replication is that the dataset on the Slave is always identical to what is on the Master.

But, Stored Procedures, Stored Functions, Triggers, and Events may (or may not) do things that should not be re-done on the slave.  Therefore, you must decide case by case whether you want them to be on both servers.

To apply a CREATE to all your slaves -- that should be as simple as a script that connects to each slave and performs the same query.

mysqldump is cautious about dumping such Routines, for fear that you will use the dump to initialize a Slave.

The prime example of something that should not be on the Slaves:  A Trigger that Inserts a row into another table.  And you have Row Based Replication.  The Insert is already sent to the Slave via RBR, and the Slave has no way to perform a Trigger on something in the replication stream anyway.


Does read-only activity on synchronous secondary replica slow down primary replica in Availability Group? Suppose that we have an Availability Group configuration (SQL Server 2017) where primary and synchronous secondary do not share any resources
(each replica have dedicated CPU, memory, disk, etc.)

Will read-only activity on synchronous secondary have performace impact on primary replica ?

The reason I ask because different sources tell different (contradictory) things:

Some sources say it has impact and not recommended to offload read-only activity to sync secondary
(recommend only offload to asynchronous replica), and others tell that is best practice to offload reporting load on secondary even if it is sychronous

What do you think on this ?

usually, there is no impact on primary replica in performance point of view as the resource utilization is completely on secondary replica. i.e. (CPU, Memory, IO), and read operation would takes place on secondary copy of the database.

I believe, your assumption of performance impact considering the log processing workers, but that (AG worker connections) would anyway constantly active (which can be identified with select * from sys.dm_exec_connections where protocol_type = 'Database Mirroring' ) on primary replica regardless weather the secondary read-only operation or not. If it's really a concern you can use dedicated Ethernet for Database mirroring end-pints.

In summary, there is no performance impact but we should consider about Data Latency as the active transactions from Primary replica cannot be available in Secondary replica's select query until and unless the transaction committed by Primary replica and secondary replica has processed particular transaction.


Given the information in the question I would start out with something like:

CREATE TABLE players
( player char(8) not null primary key
, additional attributes );

CREATE TABLE games
( game_id int not null primary key
, additional attributes );

CREATE TABLE player_games (match?)
( player char(8) not null
     references players (player)
, game_id int not null
     references games (game_id)
, participant_no smallint not null
,    constraint ... check (participant_no between 1 and 4)
,    primary key (player, game_id)
,    unique (game_id, participant_no) )

CREATE TABLE results
( game_id int not null
, player char(8) not null
, score ... not null
,     foreign key (game_id, player)
      references player_games (game_id, player)

Example of queries that can easily be answered

Which games has a player participated in?

SELECT game_id
FROM player_games
WHERE player = ?

JOIN with games if you need more info from each game

Which players participated in a game?

SELECT player
FROM player_games
WHERE game_id = ?

JOIN with players if you need more info from each player

Order the players from game X according to there score:

SELECT player, score
FROM results
WHERE game_id = X
ORDER BY score


Find out what queries are causing the biggest amount of network traffic On a production SQL server I am seeing intermittent enormous spikes in data traffic. Up to 200Mbit/s which is causing NETWORK IO waits which in turn cause query timeouts. How can I find out what queries are returning big result sets?

You can find this from the DMVs:

SELECT session_id, num_writes, st.text AS statement_text
FROM sys.dm_exec_connections AS ec
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS st
ORDER BY num_writes DESC

Unlike tracing this should be perfectly safe to run on a Production server.


How can I create a join on three tables, each from a different database? Here is a simplified example of what I'd like to do:

SELECT a.create_time, b.create_time
FROM DOB.dob_state AS a
JOIN numID.numID_history AS b ON a.create_time = b.create_time;

I'd like to join THREE tables instead of just two (each in its respective database on the same server), and I'm not sure how to do that concisely. The third database.table is entity.entity AS c. Where do I add that into the join?
Moving to slightly more complicated...I actually want to match on three fields, each of which will be included in the tables of the three databases: hash (int), create_time (timestamp), and update_time (datetime). How would I do this in a cost-efficient way?
In designing the three databases, is it essential that I use the same hash ID in all three databases or is there a way to link the three hashes (each referring to the same record) to a master ID in another database? That is for a related issue that I'll mention elsewhere, but it might affect the query process, so I'm including that consideration as well.

I am joining tables that have actual relationships with each other, but separate databases is part of the security strategy, which also includes encryption, etc. I'm looking to eventually figure out a way to do this same thing with databases on different servers, but this is just a start.

If you are using MySQL you can do this:

select *
from dob.dob_state
join numid.numid_history as t2
    on t2.create_time = dob_state.create_time
    and t1.last_name = t2.last_name
    and t1.first_name = t2.first_name
join entity.entity as t3
    on t3.create_time = t2.create_time
    and t2.last_name = t3.last_name
    and t2_first_name = t3_last_name;

Tip: You don't have to write inner join, just join.


Tablespace on ephemeral storage For performance reasons in some scenarios, e.g. Amazon EC2, you have access to a faster and cheaper storage device, which loses all its data on reboots, so it is called "ephemeral".

This question is about taking advantage of such type of storage in installations of the Oracle database. Which breaks down into:

What is a way to keep a tablespace's datafiles on ephemeral storage
and have Oracle create those on boot (and possibly run some scripts to create/populate a few tables) and be ok when they are lost.
What would be the implications for backups (there should be no backup of the ephemeral data).
Any other possible consideration for tables and other object on top of it

possible optimizations: e.g. disable logging.
what is lost with a reboot (data or data+metadata)

The TEMP tablespace is a perfect candidate for this optimization and in fact for MS SQLServer's equivalent there are on the web howtos for doing just this.

Let's consider a data warehouse, as the reference use case, with a recurring job that imports many gigabytes of data (from CSVs or datapump) into a staging schema "STG" and a subsequent ETL process that saves the results on the production schema.

This workload would benefit a lot from having very fast read and write access on the staging schema, easily tolerating the volatility of its data.

Oracle does not record checkpoint information in tempfile. So, for the tempfile:

Oracle is able to start even if tempfile is missing. You will get a message in the alter log and you should recreate tempfile.
No backup is taken of tempfile so it's safe to loose it
There is no need to trick with any storage parameter for tempfile if resides on ephemeral storage.

About physical data structure are not tempfile, it is not safe to put them in the ephemeral storage. Oracle keep track of changes in the datafile even if you have disabled logging. Disabling logging will have only effect in the recoverability of the database, redo and overall performance. Does not permit you to put datafile on ephemeral storage. Opening the DB with a missing database (database does not take care of how important were data on it) will raise.

ORA-01116: error in opening database file %s
ORA-27041: unable to open file
ORA-01157: cannot identify/lock data file %s - see DBWR trace file
ORA-01119: error in creating database file '%s'

And you have to manually deal with the issue. Hence the use of ephemeral storage with normal datafile have to be avoided.


How to model a three-way association that involves Product, Category and Label? I have the following three tables:
products:
    product_id,
    product_name,
    ...

categories:
    category_id,
    category_name,
    category_parent_id,
    category_priority (for sort ordering),
    .....

labels:
    label_id,
    label_name,
    .....

The idea is that products assigned to a category will be grouped within each category by labels and listed on the website in this manner:
---label1---
product_1
product_2
product_3
---label2---
product_4
product_5
---label3---
product_6
product_7
product_8
product_9
etc.

I can’t figure out how to design an association table (or tables) that glue all this together and prevent anomalies like this:
---label1---
product_1
product_2
---label2---
product_2
product_3

At the same time, I’d like to allow a situation where there will be no labels assigned to a category when there are not enough products to justify it.
Questions
Is it possible to design a structure that will hold it together or should I ‘abandon all hope’ and go with something like this:
categories:
    category_id,
    category_name,
    category_parent_id,
    categor_is_label,
    category_priority

products:
    product_id,
    product_name,
    ...

plus the association table that follows:
categories_products:
    category_id,
    product_id,
    priority

and handle all logic and checking for anomalies in the application?
I’m assuming that users won’t have direct access to the database.
Comments and chat interactions
For those interested in an in-depth conversation about the business context at hand, you can visit this chat room.

Note: This answer presents an approach to cover specifically the business rules derived by way of the earliest series of comment and chat interactions (most of which can be seen in this question revision). Nevertheless, after more in-depth clarifications and deliberations took place, @yemet indicated that the business context may require a somewhat distinct method, due to the successive identification of dfferent business rules.

The fact that you have determined that there exists a three-way association (also known as ternary or diamond shaped relationship) that involves the entity types (tables once implemented) of interest shows that you are heading in the right direction.
Business rules
The objective should be to handle the three distinct relationships at hand separately, starting with a logical level analysis before considering the implementation aspects in full. In this regard, it is quite helpful to write down some formulations that describe the relevant business rules, e.g.:
First, for the following many-to-many (M:N) relationship:

A product is classified by one-to-many categories
A category classifies zero-one-or-many products

Which implies the existence of an associative entity type that I am going to call product_category.
Second, for a distinct M:N relationship:

A category is integrated by zero-one-or-many labels
A label integrates zero-one-or-many categories

Situation that suggests that there is another associative entity type, in this case the one  that I will name category_label.
Then, it is time to manage another M:N relationship, this time between the two associative entity types discussed above:

A product_category may receive zero-one-or-many label_assignments
A category_label may take part in zero-one-or-many label_assignments

As noted, I have included a new entity type that I denominated label_assignment but, naturally, you may name it using a term that is more meaningful with respect to your business domain.
I have assumed, based on the structure of the categories table contained in your question (specifically the column categories.category_parent_id), that there is a self-recursive one-to-many (1:M) relationship concerning the entity type called category. Later, you confirmed such situation, so the following rule applies as well:

A category comprises zero-one-or-many categories

Logical Model
Then I have derived an IDEF1X1 logical model from the business rules formulations presented above, that is shown in Figure 1:

With this arrangement you can solve much of your needs, since:

Each product has to first be related to a certain category before it receives a label assignment.
A label cannot be assigned to a particular product if it has not been connected to a certain category previously.
A product can be related to a certain category without having to be involved in a relationship with a label.

Expository DDL structure
Consequently, I coded the following DDL structure (tested on SQL Fiddle):
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE product
(
     product_id       INT      NOT NULL,
     product_code     CHAR(30) NOT NULL,
     name             CHAR(30) NOT NULL,
     description      CHAR(90) NOT NULL,
     created_datetime DATETIME NOT NULL,
     CONSTRAINT PK_product             PRIMARY KEY (product_id),
     CONSTRAINT AK_product_code        UNIQUE      (product_code), -- (Possible?) ALTERNATE KEY.
     CONSTRAINT AK_product_name        UNIQUE      (name),         -- ALTERNATE KEY.
     CONSTRAINT AK_product_description UNIQUE      (description)   -- ALTERNATE KEY.
);

CREATE TABLE category
(
    category_number         INT      NOT NULL,
    parent_category_number  INT      NULL, -- Set up as ‘NULLable’, in order to focus on the main aspects of the approach exposed.
    name                    CHAR(30) NOT NULL,
    description             CHAR(90) NOT NULL,
    created_datetime        DATETIME NOT NULL,
    CONSTRAINT PK_category                         PRIMARY KEY (category_number),
    CONSTRAINT AK_category_name                    UNIQUE      (name),        -- ALTERNATE KEY.
    CONSTRAINT AK_category_description             UNIQUE      (description), -- ALTERNATE KEY.
    CONSTRAINT FK_FROM_category_TO_parent_category FOREIGN KEY (parent_category_number)
        REFERENCES category  (category_number)
);

CREATE TABLE label
(
    label_number     INT      NOT NULL,
    name             CHAR(30) NOT NULL,
    description      CHAR(90) NOT NULL,
    created_datetime DATETIME NOT NULL,
    CONSTRAINT PK_label             PRIMARY KEY (label_number),
    CONSTRAINT AK_label_name        UNIQUE      (name),       -- ALTERNATE KEY.
    CONSTRAINT AK_label_description UNIQUE      (description) -- ALTERNATE KEY.
);

CREATE TABLE product_category -- Associative table.
(
    product_id          INT      NOT NULL,
    category_number     INT      NOT NULL,
    classified_datetime DATETIME NOT NULL,
    CONSTRAINT PK_product_category                  PRIMARY KEY (product_id, category_number),
    CONSTRAINT FK_FROM_product_category_TO_product  FOREIGN KEY (product_id)
        REFERENCES product  (product_id),
    CONSTRAINT FK_FROM_product_category_TO_category FOREIGN KEY (category_number)
        REFERENCES category (category_number)
);

CREATE TABLE category_label -- Associative table.
(
    category_number     INT      NOT NULL,
    label_number        INT      NOT NULL,
    integrated_datetime DATETIME NOT NULL,
    CONSTRAINT PK_category_label                  PRIMARY KEY (category_number, label_number),
    CONSTRAINT FK_FROM_category_label_TO_category FOREIGN KEY (category_number)
        REFERENCES category (category_number),
    CONSTRAINT FK_FROM_category_label_TO_label    FOREIGN KEY (label_number)
        REFERENCES label    (label_number)
);

CREATE TABLE label_assignment -- Associative table that ‘concretizes’ a relationship between two distinct relationships.
(
    product_id        INT       NOT NULL,
    category_number   INT       NOT NULL,
    label_number      INT       NOT NULL,
    assigned_datetime DATETIME  NOT NULL,
    CONSTRAINT PK_label_assignment                          PRIMARY KEY (product_id, category_number, label_number), -- Composite PRIMARY KEY.
    CONSTRAINT FK_FROM_label_assignment_TO_product_category FOREIGN KEY (product_id, category_number)   -- Composite FOREIGN KEY.
        REFERENCES product_category (product_id, category_number),
    CONSTRAINT FK_FROM_label_assignment_TO_category_label   FOREIGN KEY (category_number, label_number) -- Composite FOREIGN KEY.
        REFERENCES category_label   (category_number, label_number)
 );

Pay especial attention to the two composite FOREIGN KEY definitions of the label_assignment table, because the category_number attribute is included in both of them.
You brought up a requirement that stipulates that:

A product has to be assigned to category in order to be visible in the shop.

Therefore, you should guarantee that each time that you INSERT a product row you as well link it with a certain category by means of the INSERTion of a row in the associative table here called product_category. In this way, both operations should be executed within the same ACID TRANSACTION, so that they either succeed or fail as a single unit.
Similar scenarios
You might find of help my answer to

Best data modelling approach to handle redundant foreign keys in relational model,

and also the @Ypercubeᵀᴹ answer to

Many to Many and Weak Entities.

Endnote
1. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) theoretical work authored by the originator of the Relational Model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown. It is worth noting that IDEF1X was formalized by way of first-order logic.


The statement has been terminated I have a table for which a update stored procedure has been created.
For this table I created an update trigger. But when I either execute the stored procedure or execute an update query I am getting this error message:

  Msg 8152, Level 16, State 14, Procedure OperationHistory, Line 95
  String or binary data would be truncated.
  The statement has been terminated.

Create Trigger OperationHistory on [dbo].[Employee]
For Update
AS
Declare @EmployeeID int;
Declare @Employeename varchar(100);
Declare @SSN bigint;
Declare @Phonenumber bigint;
Declare @JoinDate Date;
Declare @OperationHistory varchar(100);
Declare @Operationtimestamp Datetime;

select @EmployeeID=i.EmployeeId from inserted i;
select @Employeename=i.Employeename from inserted i;
select @SSN=i.SSN from inserted i;
select @Phonenumber=i.Phonenumber from inserted i;
select @JoinDate=i.JoinDate from inserted i;

If Update (EmployeeId)
Set @OperationHistory='updated employee id';
If Update (Phonenumber)
Set @OperationHistory='updated phonenumber';
If Update (SSN)
Set @OperationHistory='updated SSN';
If Update (Employeename)
Set @OperationHistory='updated employeename';
If Update (JoinDate)
Set @OperationHistory='updated JoineDate';

Insert into Employee_History
Values(@EmployeeID,@Employeename,@SSN,@Phonenumber,@JoinDate,@OperationHistory,getdate())

Go

Insert into Employee_History
  Values(@EmployeeID,@Employeename,@SSN,@Phonenumber,@JoinDate,@OperationHistory,getdate())

Make sure the column order of the Employee_History table matches the order of the variables listed.  The best practice is to list column names on INSERT statements.

The other possible cause is that the EmployeeName column in the Employee_History is shorter than 100 characters.

Importantly, as suggested in the comments, triggers fire once per statement instead of once per row so must be coded to handle multi-row inserts.  The history table insert should be refactored as INSERT INTO Employee_History(column-list) SELECT column-list FROM inserted  Not related to this error, though.

Also, there are a number of other errors in the trigger.  An UPDATE statement can update multiple columns yet the trigger code identifies only one the potentially changed columns.  The UPDATED function in an UPDATE trigger does not indicate the value has changed, only that it specified in the SET clause of the UPDATE statement.  This may cause erroneous messages in the history table.


Using GIN to index a JSON column I need to add index on a JSON column in my table as per documentation I am executing a query similar to the one below.

CREATE INDEX idxgin ON api USING gin (jdoc);

I am getting following error:

  ERROR:  data type json has no default operator class for access method "gin"
  HINT:  You must specify an operator class for the index or define a default operator class for the data type.

Here is the link for the documentation.

GiN(Generalized Inverted Index) is inverted index, a structure that has only one single index entry per a key and store the mapping information(posting list) of all key row to have the same value in the index entry. In postgreSQL, the key is stored in the index entry and mapping information for the key is stored in the posting tree. To search the index entry and posting tree is using a B-Tree. Therefore, GIN is useful when an index must map many values to on row, such as indexing array, documents.

I suppose to that before creating GIN index , you are doing some mistake. as you see here postgresql GIN pg_trgm default operator class

To Creates a GIN (Generalized Inverted Index) the syntax should be like that

CREATE INDEX name ON table USING gin(column);

Creates a GIN (Generalized Inverted Index)-based index. The column must be of tsvector type.

Default operator class jsonb_ops supports existence operators( ?, ?&, ?| ) and containment operator( @> ). And jsonb_path_ops supports containment operator only. Therefore GIN index is only possible to search for that have a particular key or key-values.

When we use ->> operator of JSONB, PostgreSQL can use B-tree or Hash index for processing the operations. ->> oerator returns the value of the specified attribute in text format. PostgreSQL can use indexes for the text results as compare operands. GIN index can be used by GIN jsonb operator class.

To see the json operator Here

For further ref Here and Here


When Execution Plan Cost Differs From IOPs and Time Generally, the query execution plan's overall cost aligns with the statistical IOPs.  However, I've run into a situation where this isn't the case; one plan, which uses no throttling of CPU, shows it's less expensive by 6%, yet the more expensive plan (which uses OPTION (MAXDOP 1)) has significantly fewer reads and requires less time.  I tested the order of the query which came first and last and I continue to obtain the same results (even when running them independently).

  The no maxdop option query is 6% faster by query execution plan, but has significantly more reads and takes more seconds regardless of whether it is run first or last over the MAXDOP 1 query.

In a situation where the query execution plan shows that one plan is smaller than the other overall, but the statistics show a lot more reads on the more expensive one and it takes a lot less time, what's the best way to determine which is more effective?

Update

Just some numbers:

Plan one (no MAXDOP): 6% less cost by execution plan

Plan two OPTION (MAXDOP 1): Per Stats IO, fewer scans, logical reads,WorkTables involved; less time by 2 seconds overall.

SQL server uses a cost based optimization model. The exact cost will depend on the row size and distribution info which will be context-sensitive and dynamic. Even in actual execution plans, the cost figure is based on estimates.

From : An Introduction to Cost Estimation

  In recent versions of SQL Server this cost should no longer be interpreted as seconds, milliseconds, or any other unit.

Back to your question :

  what's the best way to determine which is more effective?

I used to rely on statistics time and IO. Any change I make e.g. create index, rewrite the query, use query hint, update / create statistics etc. I would measure the CPU time, reads and writes.

Since I purchased SQL Sentry's Plan Explorer PRO, I just use it to compare the before and after plans.

Also, you can get Multiple Plans for an "Identical" Query - check the SET options, any cAsE and whitespace or schema references as well.

Great references :

Inside the Optimizer: Plan Costing
When is a Seek not a Seek?


Insert data into a column where other columns have constraints I want to insert data into the zipcode column of the employee3 table.  SQL says write - INSERT INTO Employee3 (ZipCode) VALUES (28279);, however I keep getting this error message:

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'EMPLOYEEID', table
'AdventureWorks2012.dbo.EMPLOYEE3'; column does not allow nulls.

INSERT fails.

I understand other columns have "NOT NULL" CONSTRAINTS, but I only want to INSERT data to the ZipCode column. Is there anyway around having to update the other columns?

You cannot insert a row without specifying values for columns that have NOT NULL constraints. What would be the point of having NOT NULL constraints on those columns if it wasn't enforced?

You would need to remove the NOT NULL constraint clause for each column in the table in order to insert a row that only contains the Zip code value.

This begs the question:  Are you really trying to insert a row that ONLY has a zip code, and nothing else?  Or are you actually trying to modify the zip code for already existing rows in the table?  If that is the case, you need to use an UPDATE <TABLE> statement, not an INSERT INTO statement.


how to restore differential back daily basis on sql server DB Im working with SQL Server DB, what i need is that i have a DB1 and clients site communicate with this DB,Reporting module of the application is also using the same DB (i-e That shows different reports in web pages) client want to point the reporting instance to another DB i-e DB2.

What we need to do is: create a new DB2 i-e copy of DB1 and on daily basis we will take the differential backup of DB1 and will restore it to DB2.

For this i tried to take the differential backup of DB1 using this query,

BACKUP DATABASE [DB1] TO  DISK = N'D:\DB_location\DB1-Diff.bak'
WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'DB1-Differential Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

and to restore it to DB2 i use

RESTORE DATABASE [DB2] FROM  DISK = N'D:\DB_location\DB1-Diff.bak'
WITH  FILE = 1,  norecovery,  NOUNLOAD,  STATS = 10
GO

it runs perfect for the 1st time but next time if gives me error:

Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Please also advise if i can use Transaction Log backup if that is more batter then differential back up.
Can any one help me to solve this issue.

Thanks in advance.

This error happens when Full back up is not restored before attempting to restore differential backup or full backup is restored with WITH RECOVERY option. Make sure database is not in operational conditional when differential backup is attempted to be restored.

Please have a look on the below blog.

http://blog.sqlauthority.com/2010/03/21/sql-server-fix-error-3117-the-log-or-differential-backup-cannot-be-restored-because-no-files-are-ready-to-rollforward/


Can not grant permission in mysql When I create a user account with localhost and password as

create user user1@localhost identified by 'user1'

It is ok to grant.
But when i create user using '%' and without password, it is not ok for grant.

create user user2@'%'
grant select on newdatabase.s to user2

and I have been checked the privileges of user2 using show grants for user2. It shows correctly.
But when i use as user2, user2 can not access 'newdabase'
Why?

are you sure you're doing what you want? In first query you specified localhost while in second you specified %... so for % you will have to connect to server remotely or using ip.

Check below:

root@server3:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3797
Server version: 5.5.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create user user1@localhost identified by 'user1';
Query OK, 0 rows affected (0.21 sec)

mysql>
[1]+  Stopped                 mysql
root@server3:~# mysql -uuser1 -puser1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3798
Server version: 5.5.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
root@server3:~# fg
mysql
create user user2@'%'; grant select on test.* to user2;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> show grants for user2;
+-----------------------------------------+
| Grants for user2@%                      |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'%'       |
| GRANT SELECT ON `test`.* TO 'user2'@'%' |
+-----------------------------------------+
2 rows in set (0.03 sec)

mysql> \! ifconfig
eth0      Link encap:Ethernet  HWaddr 00:0c:29:db:fe:fe
          inet addr:192.101.86.13  Bcast:192.101.86.255  Mask:255.255.255.0
          inet6 addr: fe80::20c:29ff:fedb:fefe/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:250027 errors:0 dropped:0 overruns:0 frame:0
          TX packets:259715 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:105217244 (105.2 MB)  TX bytes:29466344 (29.4 MB)
          Interrupt:19 Base address:0x2024

lo        Link encap:Local Loopback
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:65536  Metric:1
          RX packets:158798 errors:0 dropped:0 overruns:0 frame:0
          TX packets:158798 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:33510924 (33.5 MB)  TX bytes:33510924 (33.5 MB)

mysql>
[1]+  Stopped                 mysql
root@server3:~# mysql -uuser2 -p -h192.101.86.13
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3804
Server version: 5.5.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
root@server3:~#


Backup to three drives suddenly slow The situation: I have a 1.3 TB Database (SQL 2005) that I am currently backing up to 2 network shares on separate machines and on one local USB. (Don't ask why - long story involving a DBA that quit last night)
The backup started 12 hours ago and for the first 2 hrs it was showing an estimated total runtime of 8 hrs. For the last two hours it is showing an estimated remaining time of 90 minutes. It seems to be doing 1% per hor right now leaving another 8 hours to finish.

Perfmon is showing 6MB/s going out on the network and 6MB/s going in on an otherwise idle machine (share 1). Share 2 is not idle, so there is noise on the network but no significant trafic. USB drive is at 0 bytes a second for a while now.

Any Ideas of how to revive the two "forgotten" backup targets to get this backup finished before tonight?

P.S:ETA and percent completed coming from sys.dm_exec_requests

Backup command is:

BACKUP DATABASE [XXXXXXXXXX] TO
DISK = N'D:\backup\XXXXXXXXXX_20121019_T3_A',
DISK = N'\\server3\XXXXXXXXXXBak\XXXXXXXXXX_20121019_T3_B',
DISK = N'\\server2\XXXXXXXXXXBak\XXXXXXXXXX_20121019_T3_C'
WITH NOFORMAT, NOINIT,  NAME = N'XXXXXXXXXX-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM

The drive that it is currently stil writing to is the one that we usually backup to. That usually takes 60 hours writing to a single file.

Do you have a fast spindle or spindle set that is not part of the DB itself to back up to?

Or even a tape drive local to SQL ?

If backup space is snug, a tool like redgate sql backup will compress a backup. A 1TB database might be 100-300mb compressed. The backup is much faster also, because the amount being written is so much smaller (our backups of 0.5TB went from an hour to 19 mins).

The key is to have sufficient space, either local on the box or local on a fast LAN, so you can do a straight, single shot backup. The target should be a spindle set that is not used for SQL data or logs, or any other high IO operation (as the backup itself is heavy IO).

Additionally: If possible, take the db to single user, or read only state before running the backup. In some cases this can improve performance (by eliminating concurrent IO to the db itself during the backup).


First Normal Form, why is it good and how does it reduce redundancy I asked a similar question on SO and was advised to ask the type of question here. This is for a course on relational databases. A sample problem asks "how come every table in a relational database should be in First Normal Form" my first problem with this is isn't 3NF considered better so wouldn't the statement be false? My second problem with this is on SO people said that First Normal Form is good because it eliminates redundancy. I don't see how it does this because 1)all values are atomic 2)there is a primary key - neither of which eliminates redundancy.

I guess another way to ask it is why is it important for values to be atomic?

The primary importance of first normal form is not that it eliminates redundancy, but rather, it's that it eliminates repeating groups.

Instead of having multiple columns of the same kind of data in a record, (0NF) you remove the repeated information into a separate relation and represent them as rows.  This is what constitutes 1NF.

Tables that have columns like: phone_1, phone_2, phone_3 or that contain list-oriented data like: 212-555-1212, 212-555-1234, 416-967-1111 violate 1NF.

1NF is important because it is much more flexible than 0NF while being much easier to use when inserting, updating and reading data.  This is because every type of data element (e.g. customer phone number) has exactly one column in which to find it and that column has only one piece of data for each record.  This means that you can use simple SQL statements to read or write individual data elements without having to parse delimited strings or use constructions like: where phone_1=@Number or phone_2=@Number or phone_3=@Number and so forth.

Regarding 1NF vs 3NF, the normal forms are cumulative.  A table in 3NF is also in 1NF, so it is just as true to say that "Every table in a relational database should be in 1NF" as it is to say that "Every table in a relational database should be in 3NF."  I would say both of these are true, but I would add "unless you have a really good, well considered reason to denormalize".


Plug a PDB in a new CDB without XML manifest In oracle 12c the ALTER PLUGGABLE DATABASE "PDB1" UNPLUG INTO 'PDB1.XML' statement create the XML manifest.
Is it possible to plug a PDB in a new CDB if it isn't correctly unplugged first and the source CDB isn't active?

you can recover the XML file of a not unplugged PDB.

COPY the PDB folder into the CDB folder on the new machine.
Connect as SYSDBA to the new CDB (I use SQLDeveloper)
EXECUTE the package DBMS_PDB.RECOVER

it uses 3 parameters:

/path/to/new/file.xml
NAME_OF_PDB (you can use the same as OLD PDB or a NEW ONE)
/path/to/pdb/folder/

EXAMPLE:

BEGIN
  DBMS_PDB.RECOVER (
  pdb_descr_file => '/path/to/new/file.xml.xml',
  pdb_name => 'NAME_OF_PDB',
  filenames => '/path/to/pdb/folder/'
  );
END;
/

THEN, you can plug the database using:

CREATE PLUGGABLE DATABASE NAME_OF_PDB USING '/path/to/new/file.xml' NOCOPY TEMPFILE REUSE;

THEN open the PDB:

ALTER PLUGGABLE DATABASE NAME_OF_PDB OPEN FORCE;

IF PDB DON'T OPEN, check PDB_VIOLATIONS

SELECT * FROM PDB_PLUG_IN_VIOLATIONS;

SOURCE
http://www.oraclebuffer.com/oracle/oracle-12c-lost-your-pdbs-xml-manifest-file-heres-how-you-can-recover-it/


How should I handle Products with different attributes in my database structure? We have an e-commerce site (web application) built with PHP.

There are different types of products like mobiles, tshirts, watches, books.

A mobile can have attributes like, for example, ram, model name, os.
A t-shirt can have attributes like, for instance, color, size.

My questions are the following:

Is it better to save all attributes in a single table called Products (even if there will be/it will create a lot of 'NULL' marks)?
Save different types of products in different tables like Mobiles, Tshirts, etc.?

In the second case, more tables will be created, see the two images below:

As suggested via comments, I am using the EAV model, so I drew the following design [ image 3 ] :

as suggested in answer, instead of eav , i combined all 3 tables in one table as   product_information + attributes_list + attributes_values = products information i drew all in one table  [image 4 ]:

Please suggest me is i need to go with image 3 or image 4 ?

What you can also do, is following. You create a table with three columns: product_id, property and value. So you get not more tables then neccessary. But it's a matter of taste.


CREATE TABLE not issued on other masters Database is created on all multi masters.

Why CREATE TABLE doesn't being replicated to other masters.

some explanations?

If you have MultiMaster Replication, every DB server needs this
[mysqld]
log-slave-updates

This will tell a Slave to log completed SQL commands from the Relay Logs into the Slave's binary logs. Just add that option to every Slave and restart mysql.
If this does not work, you may need to explain more things to us.
Here are my other posts where I suggested using log-slave-updates

Oct 09, 2013 : Multi-master and multi slave
Dec 05, 2012 : MySQL Slave Relay Logging but not logging Binary Log
May 29, 2012 : mysqld-multi with first DB as Slave and second DB as Master
May 07, 2012 : Setting Circular Replication in mysql

Give it a Try !!!


Oracle Database LOB increases untypical An Oracle 12c production database includes tables with a BLOB column. Usually the growth rate is 2GB per week. This is the table create statement:

CREATE TABLE XXX.FILEENTRY
(
  FILENAME     VARCHAR2(300 CHAR),
  FILESIZE     NUMBER(20),
  FILECONTENT  BLOB
)
LOB (FILECONTENT) STORE AS SECUREFILE (
  TABLESPACE  DATA
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  CACHE
  LOGGING
      STORAGE    (
                  INITIAL          104K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                 ))
TABLESPACE DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;

Since some days the LOB increases up to 100GB a day. The LOB which increases that fast is: SYS_LOB0000119621C00011$$:

Owner,Segment name,Segment size (GB)
xxx,FILEENTRY,1,8
xxx,SYS_LOB0000119621C00011$$, 1156,1

The application has a transfer table which only shows the usual input of about 2GB a week. What could be the reason for this growth if this is not coming from the users? Is there a way to check the content of SYS_LOB0000119621C00011$$?

Is there a way to check the content of SYS_LOB0000119621C00011$$?

Yes you can first look at dba_lobs view to check which table/column is associated with this segment.

  What could be the reason for this growth if this is not coming from
  the users?

Maybe one huge file has been inserted in your table. You can use DBMS_LOB.GETLENGTH(column_name) to find the size of each lob.

select filename, DBMS_LOB.GETLENGTH(filecontent) from fileentry order by 2;


How do I deploy an SSIS package? I am very new to SSIS. I have inherited a bunch of databases and SSIS packages.  I notice many jobs utilizing many different packages.  When I look at an existing job and try to see where a package it is using is located I just see that it is in the "SSIS Packages" folder.

I know you can either deploy a package to the file system or as a database object(?).  How can I tell which type of deployment a package has taken?

Also, in an existing job, an associated SSIS solution where multiple projects contain multiple packages utilized by this job, there is one package I needed to modify. It isn't currently scheduled as part of a step of the job.  When I go to add the new step, I don't see as an option that I can choose this package. I assume it must not be deployed.  So how do I deploy it and how do I make sure I deploy it the same way the other packages in this job were deployed? (Really - how do I not mess things up!?)

How can I tell which type of deployment a package has taken?

Look at the definition of your SQL Agent Job Step.

If it specifies /file then the package has been to deployed to the file system somewhere.

If it specifies /DTS then it's using a well known location on the SQL Server installation path (precise location eludes my memory as of this posting but \Program Files\Microsoft SQL Server\XXX\DTS\Packages and/or C:\Program Files (x86)\Microsoft SQL Server\XXX\DTS\Packages are good guesses)

If it specifies /sql then it is deployed to the specified SQL Server in msdb.dbo.sysdtspacakges90 (SQL Server 2005) or msdb.dbo.sysssispackages (SQL Server 2008/2008R2+)

As to scheduling a package that you can't see, this sounds suspiciously like you're not on 2008 R2 as you think but much more like 2012+ as this is the SSISDB with the folder/project/package structure. If this is the case, packages can be specified as being Entry Point Packages or not. It's just a UI convention, there's nothing that prevents them from running. There's a selector that specifies whether you want Entry Point Packages or All. Switch to All packages


Create dynamic WHERE clause from a JSON field We have tables like this:

people_people (
   "id" integer...
   "attributes" HSTORE
)
object_permission (
    "permissions" JSON
)

An example of an object_permission.permissions is:

{"matricule":{"authorized_keys": ["12"],
              "unauthorized_keys": ["1", "2", "20"]},
 "departement":{"authorized_keys": ["it"],
                "unauthorized_keys": ["finance", "account"]},
 "access":{"authorized_keys": ["superuser", "user", "login"],
           "unauthorized_keys": ["web"]}}

From these data, I'd like to generate the query:

SELECT * FROM people_people
WHERE
(attributes->'matricule' IN ["12"]) AND
(attributes->'departement' IN ["it"]) AND
(attributes->'access' IN ["superuser", "user", "login"]);

How to generate dynamic WHERE clause based on a JSON field?
I don't want to use dynamic SQL because of the possible SQL injection.

Here is a small example to prove my point:

CREATE TABLE some_data (
    data hstore
);

INSERT INTO some_data
VALUES ('a=>bla'),
       ('access=>bla'),
       ('access=>user');

Now taking your JSON as an example, we can do something like this:

WITH condition_elements AS (
    SELECT key,
           ARRAY(SELECT jsonb_array_elements_text(value->'authorized_keys')) AS value
      FROM jsonb_each('
        {
         "matricule":   {"authorized_keys":["12"],
                         "unauthorized_keys": ["1", "2", "20"]},
         "departement": {"authorized_keys": ["it"],
                         "unauthorized_keys":["finance", "account"]},
         "access":      {"authorized_keys" : ["superuser", "user", "login"],
                         "unauthorized_keys" :  ["web"]}
        }'::jsonb)
)
SELECT data->key AS key,
       value,
       data->key=ANY(value) AS matches
  FROM some_data,
       condition_elements;

The actual output is to describe what to expect from above:

 key  │         value          │ matches
──────┼────────────────────────┼─────────
      │ {superuser,user,login} │
 bla  │ {superuser,user,login} │ f
 user │ {superuser,user,login} │ t
      │ {12}                   │
      │ {12}                   │
      │ {12}                   │
      │ {it}                   │
      │ {it}                   │
      │ {it}                   │

If we move the necessary part to a condition, it will work nicely:

-- add some more data for testing
INSERT INTO some_data
VALUES ('departement=>bla,access=>bla'),
       ('departement=>it,access=>bla'),
       ('departement=>it,access=>login');

WITH condition_elements (... as above ...)
SELECT *
  FROM (
        SELECT data, bool_and(data->key=ANY(value)) AS full_match
          FROM some_data, condition_elements
         GROUP BY data
       ) t
 WHERE full_match;

                  data                  │ full_match
────────────────────────────────────────┼────────────
 "access"=>"login", "departement"=>"it" │ t
 "access"=>"user"                       │ t

Notes:

It is always better to present real code in your question - like actual CREATE TABLE statements, a syntactically correct JSON snippet and a query that actually can run.
For translating JSON arrays into PostgreSQL arrays, I used here a technique that is described in detail in Erwin Brandstetter's excellent answer.
Check the documentation about the different JSON(B) operators and functions.  It contains quite a lot of useful stuff.


How to tell if Database Certificate Used? I plan to migrate a SQL Server database to another server. There is a certificate listed under the Security > Certificates view in SSMS Object Explorer.

I ran the below query to check if database is encrypted and it is not...

select name, is_encrypted from sys.databases

How can I find if this certificate is used? And how do I ensure successful migration?

Thanks!

This will provide details about the certificates encrypting any database that has TDE encryption enabled:

use master
select
    db_name(db.database_id) DatabaseName,
    c.name,
    *
from sys.databases db
join sys.dm_database_encryption_keys dek
    on
    dek.database_id=db.database_id
join sys.certificates c
    on
    dek.encryptor_thumbprint=c.thumbprint
where
    db.is_encrypted=1

For all other column level encryption within a user database, backup the Database Master Key, migrate it to the other environment and restore it after restoring the user database.


Is it possible for increasing the SQL Server max memory limit to degrade performance? As far as I can tell, raising the SQL Server max memory limit has no drawbacks, provided that sufficient memory is left for the OS and other applications.

Assuming this is the case, can there be any detrimental effects to raising the max memory limit?

The only thing I have seen is this KB article from Microsoft, which states,

  When you configure max server memory sp_configure option in Microsoft SQL Server to a large value, you may notice that a particular query may run slowly. But if you lower the value for this option, the same query may run much faster.

but it only applies to old versions of SQL Server (<=2008).

Generally speaking, no, there is no detrimental effect from maxing out memory once the OS and other applications have been accounted for. This is exemplified through the default settings of SQL Server:

  The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 MB.

SQL Server Memory Configurations (MSDN)

While this setting allows dynamic re-sizing of the memory SQL Server acquires, it also goes to show that there isn't a number that is "too much" (aside from OS/app/version considerations). SQL Server will only use the memory it requires, even though it may reserve more based on the min/max settings. Giving it access to as much as possible simply gives it the room it may need to operate efficiently.

As Shanky mentioned, in 2012 and onwards, SQL also uses this setting for thread stacks, CLR allocations, and more which may have other effects in specific scenarios when it comes to memory optimization.