texts:
I have a software system running in my office with MS SQL Server in back end.I want to backup the database daily and delete all data from database so that only one-day data will be in the database at office. I want to append this daily data to my system at home so that  all data including old remains at home pc.

IS it possible to append daily data from office to my home pc?


texts: odd OS memory consumption by two nearly-identical SQL 2008 R2 instances

texts:
Instances are legacy, something we have to carry for the next 3 years, 2008 R2 64-bit, both SQL Server and Windows Server OS. Two copies on two nodes.

Both servers are set to consume "Maximum server memory" parameter set at MAX.

VM #1 (8GB RAM total) - memory is utilized to the maximum extent and displayed correctly in Task Manager.
VM #2 (32GB RAM total) - memory utilization is shown at ~100MB only, even when I try to force it to eat at least 16GB at startup in the SQL Server properties.

Both task manager and resource monitor on the second VM report barely any RAM consumed by the process.

Is this a SQL Server bug, if the installed OS RAM volume is above certain level? Doubtfully, right?

Or could it be some memory setting on the OS itself?


texts:
How can I subtract query result B from query result A so that I can see the difference between the two?

A:

SELECT
  character_name,
  ROUND(minutes_played / 60, 0)
FROM
  outfit_member
WHERE timestamp =
(
  SELECT
    MAX(timestamp)
  FROM
    outfit_member
)

B:

SELECT
  character_name,
  ROUND(minutes_played / 60, 0)
FROM
  outfit_member
WHERE
  timestamp < NOW() - INTERVAL 1 WEEK

What I want is the Result of ROUND(minutes_played / 60, 0) in B minus ROUND(minutes_played / 60, 0) in A. For example if B returns 1000 Hours and A returns 900 I get 100 Hours.


texts: what is causing my windows page file to grow?

texts:
I have one of my production servers that has a windows page file of over 32 GB.

The server in question has 32 GB of RAM, windows server 2008.

It is exclusively used for Sql Server. Nothing else.

The lock pages in memory setting has not been set up for any of the sql server services accounts and I think this might be related to the big size of the page file.

I am already seeing if I can add more memory to this box, but I would like to find out what is causing memory pressure (if anything inside sql server).

what could be some options as how I can do that?

I kind of like, in this case, to monitor Paging File % Usage and if something goes off threshold, I would collect all that is running on sql server at that time. Hopefully the culprits would be all there.

any ideas?

select @@Version

  Microsoft SQL Server 2005 - 9.00.5057.00 (X64)   Mar 25 2011 13:33:31
  Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition
  (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

dbcc memorystatus

Understanding pagefile.sys % Usage performance monitor (perfmon) counter correctly


texts: Table Partition Switch Problem SQL Server

texts:
I have

Tables Schema:

CREATE TABLE [dbo].[t1](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[id_file] [bigint] NOT NULL,
[id_campo] [bigint] NOT NULL,
[valor] [nvarchar](255) NULL,
[id_doc] [bigint] NULL,
[grupo] [bigint] NULL,
CONSTRAINT [PK_valores_texto_test_id] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

CREATE TABLE [dbo].[t2](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[id_file] [bigint] NOT NULL,
[id_campo] [bigint] NOT NULL,
[valor] [nvarchar](255) NULL,
[id_doc] [bigint] NULL,
[grupo] [bigint] NULL),
CONSTRAINT [PK_valores_texto_switch_id] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

The table t1 has data, meanwhile t2 is empty with the same schema as t1, then I want to partition both tables and switch the new data inserted into t2 to t1.

First, I create the partition function and partition schema.

-- Create the partition function
CREATE PARTITION FUNCTION PF_valores_texto_partition_HASH_BY_VALUE (BIGINT)
AS RANGE LEFT
FOR VALUES (2500000, 5000000, 7500000, 10000000, 12500000, 15000000)
GO

-- Create the partition scheme
CREATE PARTITION SCHEME PS_valores_texto_partition_HASH_BY_VALUE
AS PARTITION PF_valores_texto_partition_HASH_BY_VALUE
ALL TO ([PRIMARY]);
GO

And finally, I partitioning the tables.

CREATE UNIQUE NONCLUSTERED INDEX [partition_by_id_file] ON
t1
(
    id ASC,
    id_file
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
ON PS_valores_texto_aux_HASH_BY_VALUE([id_file])

CREATE UNIQUE NONCLUSTERED INDEX [partition_by_id_file] ON
t2
(
    id ASC,
    id_file
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
ON PS_valores_texto_aux_HASH_BY_VALUE([id_file])

But when I try to make a switch bettwen tables with this command:

ALTER TABLE t1 SWITCH PARTITION 1 TO t2 PARTITION 1

It returns me this error:

'ALTER TABLE SWITCH' statement failed. The index 'partition_by_id_file' is partitioned while table 't1' is not partitioned.


texts: PostgreSQL : inconsistent result when using WHERE clause on VIEW

texts:
This is my VIEW declaration
CREATE OR REPLACE VIEW inv_groups_vw AS
 SELECT gg.id,
    gg.sku,
    gg.name,
    gg.description,
        CASE
            WHEN count(i.*) = 0 THEN '[]'::json
            ELSE json_agg(i.*)
        END AS items
   FROM ( SELECT g.id,
            g.sku,
            g.name,
            g.description,
            m.item_id
           FROM inv_items g
      LEFT JOIN inv_items_group_members m ON g.id = m.group_id
     WHERE g.is_group = true
     ORDER BY m.item_index) gg
   LEFT JOIN inv_items i ON gg.item_id = i.id AND i.is_group = false
  GROUP BY gg.id, gg.sku, gg.name, gg.description;

And peforming SELECT * FROM inv_groups_vw result
{
   "id": 3
   "sku": "PVC-1861-J",
   "name": "PVC 18 oz (jaune)",
   "description": "Rouleau de PVC 18 onces de couleur jaune",
   "items": [
      {
         "id":1,
         "sku":"PVC-186150-J",
         "name":"PVC 18 oz 61 in 50 vg (jaune)",
         "description":"...",
         "is_group":false
      }, {
         "id":2,
         "sku":"PVC-1861100-J",
         "name":"PVC 18 oz 61 in 100 vg (jaune)",
         "description":"...",
         "is_group":false
      }
   ]
}

However, if I execute SELECT * FROM inv_groups_vw WHERE sku = 'PVC-1861-J'; result
{
   "id": 3
   "sku": "PVC-1861-J",
   "name": "PVC 18 oz (jaune)",
   "description": "Rouleau de PVC 18 onces de couleur jaune",
   "items": [
      {
         "id":2,
         "sku":"PVC-1861100-J",
         "name":"PVC 18 oz 61 in 100 vg (jaune)",
         "description":"...",
         "is_group":false
      }, {
         "id":1,
         "sku":"PVC-186150-J",
         "name":"PVC 18 oz 61 in 50 vg (jaune)",
         "description":"...",
         "is_group":false
      }
   ]
}

Why is ORDER BY m.item_index not respected?
Update
This may seem to work, but is it really?
CREATE OR REPLACE VIEW inv_groups_vw AS
 SELECT gr.id,
    gr.sku,
    gr.name,
    gr.description,
    json_agg(gr.item) AS items
   FROM ( SELECT gg.id,
            gg.sku,
            gg.name,
            gg.description,
                CASE
                    WHEN i.id IS NULL THEN '{}'::json
                    ELSE row_to_json(i.*)
                END AS item
           FROM ( SELECT g.id,
                    g.sku,
                    g.name,
                    g.description,
                    m.item_id,
                    m.item_index
                   FROM inv_items g
              LEFT JOIN inv_items_group_members m ON g.id = m.group_id
             WHERE g.is_group
             ORDER BY m.item_index) gg
      LEFT JOIN inv_items i ON gg.item_id = i.id AND i.is_group = false
     ORDER BY gg.item_index) gr
  GROUP BY gr.id, gr.sku, gr.name, gr.description;


texts: count null values of table from information_schema tables

texts:
Is there any way to count null values in a column by using information_Schema tables? EG:

 SELECT table_name, table_rows
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA ='myschema'
 and table_name='mytable'
 and table_rows is null;

Clarifications from comments:

I can not get the number of null values directly from the table as the table is large and not even returning the count of null values, so I thought information_schema would be the best option to get count null values from table. But I don't know how to do this. Is there a way?

I have a table named cal and one of its column contains null values. I just need to count the number of those null values. As table is large I cannot get result by using select count(*) from tablename where column_name is null.

So I need a query which counts null values from Information_Schema somehow.

About the suggestion to add index:

  But can I add an index on a column if column_type = mediumtext?

MySQL version 5.1.73 and table is MyISAM.


texts: connecting to a database via tsql on mac 10.10.4

texts:
I'm trying to connect to a database via tsql.

I use the following command:

TDSVER=8.0 tsql -S <server IP> -p 1433 -U <my username> -P <my password>

Now, I know the server IP is correct because I can ping it. I also know the port is correct from tsql -LH. It tells me the tcp is 1433.

However, when I connect the log shows:

log.c:196:Starting log file for FreeTDS 0.91.103
    on 2015-07-01 14:35:39 with debug flags 0x4fff.
iconv.c:330:tds_iconv_open(0x7fca62700310, UTF-8)
iconv.c:187:local name for ISO-8859-1 is ISO-8859-1
iconv.c:187:local name for UTF-8 is UTF-8
iconv.c:187:local name for UCS-2LE is UCS-2LE
iconv.c:187:local name for UCS-2BE is UCS-2BE
iconv.c:349:setting up conversions for client charset "UTF-8"
iconv.c:351:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
iconv.c:391:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
iconv.c:394:tds_iconv_open: done
net.c:205:Connecting to **<edited for security>** port 4000 (TDS version 7.1)
net.c:270:tds_open_socket: connect(2) returned "Operation now in progress"
net.c:306:getsockopt(2) reported: Connection refused
net.c:316:tds_open_socket() failed
util.c:331:tdserror(0x7fca62700260, 0x7fca62700310, 20009, 61)
util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:384:tdserror: returning TDS_INT_CANCEL(2)
mem.c:615:tds_free_all_results()

Why does it resort to port 4000? When I specifically specify 1433? Also, when I do tsql -C it tell me version 5.0 when I specifically edited /opt/local/etc/freetds to use 8.0?

This has been driving me nuts!


texts: What server property makes TransactionScope escalating to MSDTC?

texts:
In our application we're using TransactionScope's. We're aiming to not use the MSDTC service because it's a lot slower than lightweight transactions.

using (var transactionScope = new TransactionScope())
{
  ...
  transactionScope.Complete();
}

While connected to the test database we need no MSDTC but the exact same code, executed on the same machine, escalates to use MSDTC when connected to the production or development database.

The development database is not clustered and isn't a named instance.

All are SQL Server 2008 (10.50.2500) and the connection strings are exactly the same except for the hostnames.

It seems there has to be some difference in the setup of the databases. Any ideas on what that could be?

EDIT

The transaction was there to protect inserts in two different tables. I noticed that there was also two calls that resulted in two selects in there. There was no need for these to be in the transaction so I moved them outside of it. The need for MSDTC then disappeared in all three databases. Problem solved but without really knowing what caused it.


texts: How to start building your own DBMS (based on PostgreSQL)?

texts:
I'd like to learn how to make a DBMS (relational), based on PostgreSQL specifically. Not for production purposes to make a commercial product but to learn.

I plan on using C++ but, it's not exactly easy to find information out there on how to build your own (or at least where to start).

I know there are people in this community who are even contributors to the PostgreSQL source code (such as Laurenz Albe) so it made sense to ask this question here.

I've been following this course https://www.youtube.com/playlist?list=PLSE8ODhjZXjbohkNBWQs_otTrBTrjyohi to understand the ins and outs of a DBMS. But as most of it simply explains the theoretical, I don't have a chance to see the practical or how to actually do it.


texts: Why are these T-SQL jobs from different SQL Server instances executed on the same instance (AlwaysOn Availability Groups)

texts:
Recently, our blocked processes dashboard has been reporting blocked processes around the time when we do our statistic update.

The reason was quickly found: an update statistics job step (T-SQL) that is starting on both the secondary and the primary SQL Server instance.
The job updates several statistics on the same database, that is part of an AlwaysOn Availability Group. I would expect this to fail on the secondary instance.

A quick rundown of the failover history:

Server A, which should stay active due to licensing (will be named Active Server), failed over unexpectedly to Server B (Passive Server) on 20/02 at 9PM.

After the unplanned failover, we did another (but this time planned) manual failover back to the Active Server on 21/02 at 12PM.

Job history

Before the first failover all was good, and the active server is the only one running the job.

One job running.
We see the stat updates running on the active side. (which is primary replica at the time)

During the short time that the passive server was the primary replica, we don't have any monitoring and the job history was cleared.

After the failover, back to the 'normal' state, after being on primary on the passive node for less than 24 hours, the job step on the passive instance has also been starting and running on the active instance.

(I killed the sessions).

Now the interesting part to me, is that both jobs are running on the active server, seeming like the job is using the listener to get to the database. But it could be an entirely different reason.

There is a copy job PowerShell task running nightly at 01 AM (dbatools):

powershell.exe Copy-DbaAgentJob -ExcludeJob "CopyJobs,CopyLogins" -Source INDCSPSQLA01  -Destination  INDCSPSQLP01     -Force

My guess is now aimed at the one time, the job copy happened from the active, secondary node --> primary passive node with -Force. This happened at 21/02 01 AM.

The Question

Why is the job step on the passive instance executing on the active instance's database?

Checklist

On both instances, the job target is local:

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

The servername is correct

select name from sys.servers
select @@SERVERNAME

both return the passive server.

The job IDs of active and passive are different:

--08C63F07-0853-41DA-BC88-8FDF44AE491F -- passive
--E8C88965-C581-4E06-B651-CC10637FCEEF -- active

Both jobs use the database in question in their step:

@database_name=N'DB1',

--> Should not be accessible on the passive instance, resulting in failure.
No readable secondaries

The database is not accessible on the passive instance:

Version of both servers: 14.0.3030.27

T-SQL Job step command example

 @subsystem=N'TSQL',
        @command=N'update statistics dbo.table with fullscan ...'

Nothing is running on the passive instance when the job starts.

EDIT:

Restarting the agent on the passive node 'fixes' this, resulting in a new error on executing:

  Unable to connect to SQL Server 'INDCSPSQLP01'.  The step failed.

As a result, it no longer updates statistics on the primary instance

Hostnames = Passive & Active server, jobs where visibly running.

Application info

SQLAgent - TSQL JobStep (Job 0x9D358B2EF6C53C4BAD6A61CA87D51BF5 : Step 1)
SQLAgent - TSQL JobStep (Job 0x6589C8E881C5064EB651CC10637FCEEF : Step 1)


texts: Adding multiple foreign keys to single table

texts:
I'm trying to add multiple foreign keys to the same table; however, the error code 1215: Cannot add foreign key constraint keeps popping up.  I've read other people's posts on this same topic and tried to add innodb, but the error is still popping up. Any help is appreciated!

Here's my code:

Create Database if not exists Research;
Use Research;

Create table if not exists CompanyInfo
(
CID int primary key, Company varchar(45),
SIC int, InnovativeIndex int
)
engine = innodb;

Create table if not exists TimePeriod
(
Year int primary key, GDP int, GDPgrowth int,
inflation decimal(5,2), unemployment decimal(5,2)
)
engine = innodb;

Create Table If not exists CompanyRank
(
CID int, Year int, IndexYN int, SPRank int, FortuneRank int,
primary key (CID, Year), index (CID), Index(year),
foreign key (CID) references CompanyInfo.CID,
foreign key (Year) references TimePeriod.Year
)
engine = innodb;


texts: How to setup a SQL Cluster between 2 sites in active passive mode without shared storage

texts:
we need to install SQL at 2 sites, 1 primary and 1 DR. We don't to pay for license for the DR and need to know how will we setup a SQL cluster/replication/alwayson/bag or any other method that will keep the Sql service down at DR and only when primary is unavailble then the SQL service starts.

currently we installed a BAG and the 2 sites have sql service running, though we use only one site at a time, Auditors flagged it as active/active while we thought it was active/passive.

thanks in advance for your suggestions.


texts: mongodb could not find host matching read preference { mode: \"primary\" } for set?

texts:
I am deploy a monodb sharding, I have deploy a replica set in three machines:

dev41:27017,dev42:27017,dev193:27017

and configsvr in three machine:

dev41:27019,dev42:27019,dev193:27019

and also a mongos in machine:

dev41:28000

at last I try to add my replica into mongos:

mongo dev41:28000/admin
sh.addShard('rs0/dev41,dev42,dev193')

then I got errors:

{
    "ok" : 0,
    "errmsg" : "could not find host matching read preference { mode: \"primary\" } for set rs0",
    "code" : 133
}


texts: Relational Database design best practice

texts:
I am a student and I am trying to design a simple database, so if the ideas seem bad please let me know how to improve.

I have three tables in my mind, one for Professors and one for Students. Both of them have Contacts details. I need to know what is the best practice to achieve a relationship between the three.

I see two possibilities:
A) Having both Professors and Students tables use an auto-increment field and a supplementary default initialised field (let's say with 1 for Professors and 2 for Students). Then create a key in the third table (Contacts) as a combination of the unique ID and default initialized fields.

B) Having for tables instead of three, with two identical structure tables as ContactStudents and ContactProfessors, each serving for the appropriate table.

From my perspective I would choose the first one since if I would want to add new fields to the Contact table both would benefit from it.

Which one do you think is best and why? Is there another, better way, of achieving this?

EDIT:

After that variety of answers and at zagrimsan's suggestion I add the following requirements:
Student can be also a Professor.
A person (Student of Professor) can have multiple contacts.
A Contact can be reused for multiple Students and Professors (same kind) but not both Student and Professor at same time except the case when the same Student is also a Professor.


texts: Distributed Availability Group with Manual Seeding

texts:
I am looking for a step by step walk through of how to setup a distributed availability group with manual seeding.  I can get the automatic seeding working but when I try to manually seed I cannot get the secondary database into the AG on the forwarder.

If I add the distributed AG to the secondary before I try to add the database to the regular AG I get the following message:

Msg 41190, Level 16, State 7, Line 22
Availability group 'MYDB' failed to process add-database command.  The local availability replica is not in a state that could process the command.  Verify that the availability group is online and that the local availability replica is the primary replica, then retry the command.

If I try to add the DB first without joining the Distributed AG on the secondary, I get the following message because it thinks it should be a primary:

Msg 927, Level 14, State 2, Line 22
Database 'MYDB' cannot be opened. It is in the middle of a restore.

I have none of these problems with automatic seeding.  Everything just magically works.  All of the examples I find online use automatic seeding.

thanks in advance


texts: How to find MySQL creating temporary table in memory or disk?

texts:
I have a query, after running it if i run the show status in the same session like below

show status where Variable_name like '%Created_tmp_disk_tables%';

It got incremented by one value. So it means temporary table is created on disk right? Not sure whether it is correct or not.

But if I enable profiling for that query. Below is the status I got.

| status                | duration  |
|---------------------- |---------- |
| starting              | 0.000246  |
| checking permissions  | 0.000035  |
| Opening tables        | 0.000098  |
| System lock           | 0.000044  |
| init                  | 0.000209  |
| optimizing            | 0.000075  |
| statistics            | 0.000336  |
| preparing             | 0.000138  |
| Creating tmp table    | 0.000435  |
| executing             | 0.000038  |
| Copying to tmp table  | 2.923929  |
| Sorting result        | 0.267926  |
| Sending data          | 0.000945  |
| end                   | 0.000071  |
| removing tmp table    | 0.077263  |
| query end             | 0.000022  |
| closing tables        | 0.000055  |
| freeing items         | 0.000092  |
| logging slow query    | 0.000074  |
| cleaning up           | 0.000022  |

Here I have found Copying to tmp table but not Copying to tmp table on disk.

Why there is a conflict between these two?

Whether Show status variable is correct or profiling is correct?

Need to know whether temporary table is created on disk or not.


texts: Dump MySQL table to csv file (utf8mb4_unicode_ci)

texts:
I have MySQL table with columns (character-set: utf8, collation: utf8mb4_unicode_ci) to be exported to a .csv file.

mysqldump -u *** -p -t -T/tmp/ dbname tablename --default-character-set=utf8mb4

The output has more lines than the records in the table due to some symbols? characters? to break the lines incorrectly. (see pics below) Adding "enclosed by" or "escaped by" do not help.


texts: Where condition execution sequence in MySQL

texts:
I am using MySQL 5.0.88. I have a query like

select DISTINCT party.id FROM party LEFT join party_identifier AS pi ON pi.party_id = party.id WHERE (pi.source_note=? AND first_name LIKE ? OR last_name LIKE ? OR pi.identifier LIKE ?) AND party.type = ? AND party.record_status_id = ?

Here what my doubt is if suppose Pi.source_note result becomes false means the rest of the condition will execute or not (In point of SQL NOT programming languages).


texts: Caching derived dataset automatically in SQL Server

texts:
I have a view that combines multiple tables via UNION ALL, and handles some basic errors with the data. Due to the way that the errors are handled, the view is a bit slower than I would hope. Is there any way to cache the output of the view, and dynamically update it whenever any of the underlying tables are updated?

A few quirks of my system:

The database only sees updates once a day, during off-hours. So a resource-intensive solution is fine, as long as the resources are consumed during these off-hours.
Due to the way the system is set up, I would prefer to not clean up the data while loading it, and would rather leave the underlying tables untouched.
I have appropriate supporting indexes set up
The tables all contain distinct records with no overlap, so I need to pull them all in

Some options I've considered:

An indexed view won't work due to the view relying on the UNION operator.
I haven't found any way to cache the output of a TVF.
I could create a cache table and manually load it any time the underlying table if modified, but this feels like a hacky workaround. I think I'd also have to create a SQL Agent Job for this, since setting it as a trigger would involve refreshing the table millions of times each evening while the table is overwritten.
I could live with the poor performance, but this would negatively impact end-users.

Here's a simplified example of the view:

CREATE OR ALTER VIEW AllNamesAndAccountNumbers AS
SELECT
    x.AccountNumber
    , COALESCE(x.FullName, n.FullName) AS FullName
FROM (
    SELECT
        AccountNumber
        , FullName
    FROM TableWithNames
    UNION ALL
    SELECT
        AccountNumber
        , NULL AS FullName
    FROM TableMissingNames
) x
LEFT JOIN NameMappings n -- this join is slow due to the table being really huge
ON n.AccountNumber = x.AccountNumber

Oh - if this is an antipattern, and I should be attacking this from a fundamentally different angle, please say so. I'm concerned I'm looking at this wrong.


texts: PostgreSQL DELETE query hangs (and so does EXPLAIN ANALYZE!)

texts:
I'm trying to delete a bunch of rows from a table matching a query. The general form of my query is:

DELETE FROM mytable WHERE _id IN (SELECT _id FROM mytable WHERE ...);

The _id column is a SERIAL PRIMARY KEY.

If I run the inner SELECT query on its own it runs in about 1 second and returns about 100,000 rows. But when I add the DELETE call onto it, it seems to just sit and chug away. And chug away. I let it run for about a minute or so, and then cancelled it assuming no progress was being made.

I added EXPLAIN ANALYZE onto the front of it to see if I could check what was taking so long, but that call also hangs for a very long period of time.

Can anybody tell me what could be going on such that it's very quick to identify the rows I'd like to delete but takes an indeterminate amount of time to delete them?

Update: The full query is as follows.

DELETE FROM cards
WHERE _id IN (
    SELECT _id
    FROM cards
    LEFT JOIN game_results ON game_results.card_id = cards._id
    WHERE NOT available
    AND game_id IS NULL
)

A simplified version of the tables would be:

CREATE TABLE cards (_id INTEGER PRIMARY KEY, available BOOLEAN);
CREATE TABLE games (_id INTEGER PRIMARY KEY);
CREATE TABLE game_results (game_id INTEGER REFERENCES games, card_id INTEGER REFERENCES cards);

I'm sure this query would eventually finish running, I'm just surprised at how long it's taking given that retrieving all of the IDs to delete is so quick.

Solution:

The problem was three-fold!

I had one of the earlier (very slow!) queries running that had been triggered by an aborted script. The query was still running even though the script had been aborted. As such, operations like trying to drop indexes were locked waiting for the query to finish. Manually cancelling that query using pg_cancel_backend allowed me to experiment with dropping indexes and foreign key constraints.
Foreign key constraints seem to be the issue slowing everything down. The fact that the game_results table referenced cards made the delete take forever. The funny thing is, I was only deleting cards explicitly unused in the game_results, but of course that didn't stop the foreign key checks from happening upon delete! That made things very slow. I dropped the foreign key constraint before running the delete and that sped things up to a level I felt was appropriate.
For whatever reason, the second delete query in the accepted answer ran much quicker than the first.

By combining these three things I was able to do the whole delete in a few seconds, whereas until I had combined these three factors together my queries were running to 5-10 minutes (before I was cancelling them!).

Thanks to all for the help!


texts:
I want to delete multiple databases and its entries. These instances are not getting started. I want to delete data files also.

EDIT: I am thinking delete all data files. Please help on below concern.

Shall I delete data files manually? Will it cause any problem to my working database instance?


texts: What is the date time format for msdb.dbo.agent_datetime scalar value function?

texts: Looking at msdb.dbo.agent_datetime scalar value function.  What date / time format is this expressed in, UTC?  Same question for msdb.dbo.sysjobs columns : run_date, run_time


texts: update value in row based on value in another row

texts:
I have a table with timestamped data.

t_stamp | col1 | col2 | col3

I want to update the value in say col3 based on values in col1 and col2 from the previous row in the table. By previous row I mean the row with the next lowest timestamp value. I also want to do this for every row in the table.

For example:

col3 = col1.prev + col2

I was able to use a window function to create a SELECT query to give me the desired values for col3

SELECT lag(col1) OVER (ORDER BY t_stamp ASC) + col2 AS col3
FROM table1

but this does not update the values in the table. Can I somehow apply this to the original table? Or is there a way to format an update query in the same way?


texts: Can you create a primary xml index in the CREATE TABLE statement (SQL Server)?

texts:
I'm creating a temporary table and would like to create a primary and secondary XML index on a contained xml column within the CREATE TABLE statement since that is the only way I can create an index without throwing an error under snapshot isolation.
I know the syntax for creating standard (non-XML) clustered and nonclustered indexes inline, and it works fine, but I don't seem to be able to find a syntax that works for XML indexes either in documentation or via experimentation.
Does anyone know whether this is possible?


texts:
I am trying to find a list of PostgreSQL trusted procedural languages (PL) as I often need to use a PL in a non-superuser environment.

But I couldn't find a definitive answer. For example, the Postgres documentation here suggest that there is a both a trusted and an untrusted PL for perl, a language that I am not very familiar with. Here, it says the TRUSTED keyword is something you can specify when creating a PL language, which may be ignored under circumstances.

But I also have the (possibly wrong) impression that certain PL languages are trusted only (e.g. JavaScript PL/V8) or untruested only (Python). Other posts seem to suggest that one can tweak pg to make even python a trusted language with something like:

UPDATE pg_language SET lanpltrusted = true WHERE lanname = 'plpythonu';

My question is more practical than theoretical. Basically:

Is it generally true that any PL can be both set up to be trusted and untrusted?

If not, what are the specific languages that can be set up to be trusted or are trusted.

(By "trusted", I mean normal users should have effectively the same rights when creating/executing/modifying functions in the PL language. Also, I am using Postgres 9.x)


texts: Azure SQL Database throwing error 1132 "The elastic pool has reached its storage limit" error, even if it's not full

texts:
I'm trying to execute the following (sample) batch in an Azure SQL Database inside an Elastic Pool:
drop table if exists [dbo].[InsertTest];
create table [dbo].[InsertTest] (
    [id] uniqueidentifier,
    [filler] nvarchar(max)
);

insert into [dbo].[InsertTest] ([id], [filler])
select top 1000 newid(), replicate('X', 2048)
from [sys].[objects] as [T1]
    cross join [sys].[objects] as [T2];

/* drop table if exists [dbo].[InsertTest]; */

But the insert statement fails with the error message:
Msg 1132, Level 16, State 1, Line 1
The elastic pool has reached its storage limit. The storage used for the elastic pool cannot exceed (51200) MBs.

I tried to see if I ran out of space by running the query
select
    [type_desc]         as [file_type],
    [size] / 128        as [size_in_MB],
    [max_size] / 128    as [max_size_in_MB]
from [sys].[database_files];

but it seems that I still have space left.

file_type
size_in_MB
max_size_in_MB

ROWS
320
256000

LOG
584
1048576

FILESTREAM
0
0

What can I do about it? Am I missing something?


texts: UPDATE from subquery updates rows that shouldn't be updated

texts:
Using PostgreSQL, I want to update a column. The information how to update is in another table.

My try was this, but it also updates rows I don't want to update:

update table_1
set column_x = (
    select table_2.rename
    from table_2
    where table_2.rename IS NOT NULL
        and table_1.id = table_2.id)

How to modify the query?

Maybe this helps to explain my problem: http://www.sqlfiddle.com/#!15/318fb/1


texts: Database Restoration Issue Msg 3156, Level 16, State 6

texts:
Recently while restoring a database with memory optimized filegroup I start facing an issue with below error message:-

  Msg 3156, Level 16, State 3, Line 2 File 'XYZ' cannot be restored to
  'D:\FolderPath\Memory_Optimized_FolderName'. Use WITH MOVE to identify
  a valid location for the file.

First I thought it might be syntax error but syntax was correct and I even checked the permission to filepath which was also fine. So what is the reason for this error?


texts: Schannel 36871 Error when sending emails from Database Mail - after upgrading to SQL Server 2016

texts:
I have recently upgraded from SQL Server 2014 ENT to 2016 ENT, and now I cannot send emails from Database Mail. I get the usual

The mail could not be sent to the recipients because of the mail server failure.
    (Sending Mail using Account 1 (2018-09-07T13:19:55).
Exception Message: Cannot send mails to mail server.
(Failure sending mail.).

May or may not be related: I also get a system Error for SChannel

A fatal error occurred while creating an SSL client credential. The internal error state is 10013.

Googling around I enabled FIPS and this solved my database mail issue but (1) It broke Availability Groups eek! and (2) It is not recommended.

Any advice and information for understanding this is much appreciated.

I do have .Net Framework 3.5 SP1 installed - I have updated registry to force TLS 1.2. It looks like SQL Server is still trying to send emails with TLS 1.0


texts:
Let's say that I configured streaming replication between Server A (master) and Server B (slave). If server A goes down and Server B is promoted, would I be able to use pg_rewind, where Server B is the source and Server A is the target, to bring Server A back up up to speed with Server B? According to the documentation regarding pg_rewind:

  The result is equivalent to replacing the target data directory with the source one.

I assume that the rewind wouldn't "rewind" anything, but since:

  When the target server is started for the first time after running pg_rewind, it will go into recovery mode and replay all WAL generated in the source server after the point of divergence.

wouldn't I just be able to place server A in recovery mode and recover all the WAL segments generated by Server B, even though they are on different timelines?

Thanks!


texts: Log file growth due to replication issue

texts:
I have one primary database and it's getting replicated to another server. Both servers are SQL Server 2008 R2 with SP1. Replication was running fine till last week. We have transaction log backup every five minutes. I don't see any issue with log backup. Transactional Replication is setup as pull replication on secondary server. Distributor runs on secondary server. Everything was running fine till last week. This database is not mirrored. But cdc is enabled on the database. IsSyncWithBackup status is 0.

All of a sudden from last week, I see following message in last action column of Log Reader Agent even though replication monitor shows replication is in sync with no latency (??)

  Replicated transactions are waiting for next Log backup or for mirroring partner to catch up.

@Kin, Sorry. This is the first time I am using stackexchange portal. For some reason, I am not able to add comments to your replies. So, I am editing original post based on replies I am getting.

@Mike, It showed REPLICATION for log_reuse_wait_desc for the database set up in replication.

As a last resort, we dropped the replication and we will recreate it after business hours today. As of now, this issue is resolved. Still not sure though what caused it in first place. Thank you all for your replies.


texts: Arrange SQL Server rows in a paired sequence

texts:
I have a database table in which a row has its matching pair. Checkpoint A has a matching pair checkpoint B. Every IN status has a matching OUT status. To match these exists a 'related_id' column with the id of the row it is related to.
Normally the table order can be as follows:

id | registration_no | checkpoint | related_id | status
 1      H2080              A            2          IN
 2      H2080              B            1          OUT
 3      H2081              A            8          IN
 4      H2070              A            6          IN
 5      H2087              A            7          IN
 6      H2070              B            4          OUT
 7      H2087              B            5          OUT
 8      H2081              B            3          OUT

but instead i would like it to be ordered in this way

id | registration_no | checkpoint | related_id | status
 1      H2080              A            2          IN
 2      H2080              B            1          OUT
 3      H2081              A            8          IN
 8      H2081              B            3          OUT
 4      H2070              A            6          IN
 6      H2070              B            4          OUT
 5      H2087              A            7          IN
 7      H2087              B            5          OUT

in which the leading checkpoint A is immediately followed by the matching checkpoint B by somehow using the related_id to tell which columns are paired together. So the rows will alternate between A->B then A->B and so on.

I hope i managed to explain the question.

Any help is greatly appreciated. thank you


texts:
I have a seemingly simple delete:

DELETE FROM table WHERE added_at < '2017-08-14'

on a table with ~20k rows (w ~10k affected rows), but it has been running for 2000+ seconds.

SELECT * FROM table WHERE added_at < '2017-08-14'

takes a few ms.

I have a few other tables with FOREIGN KEYs pointing to this id and I had some previous records pointing to it, but they have already been deleted.

I have tried to:

check if the table is locked using the answer in  https://stackoverflow.com/a/26596931/2171758
terminate all backends
VACUUM


texts:
Suppose I have a person table with an id and some other columns. Other tables reference the person table using ON UPDATE CASCADE. for instance:

CREATE TABLE person (id int PRIMARY KEY);
CREATE TABLE sale (id int PRIMARY KEY,
    person_id int REFERENCES person(id) ON UPDATE CASCADE);

Now, what I am trying to do is: Remove duplicate entries from the person table, fixing the references sale references. For instance, using this data:

INSERT INTO person values (1), (2), (3);
INSERT INTO sale VALUES (11, 1), (12, 2), (13, 3);

Person 1 and 2 are actually the same, so they should be 'merged'. By merging I mean excluding person 2 while updating the references for person 1 in the sale table.

Since there is an ON UPDATE CASCADE on the table that references person, I wonder if there is some way to, without having to manually update the sale table, cause the cascade update while deleting the person 2

What I am trying to achieve here is avoiding a dedicated procedure to update the references since the number of referring tables will most likely increase, requiring maintenance in this procedure (and it is quite big already).


texts: A row read and locked with UPDLOCK, ROWLOCK, READPAST is not subsequently available

texts:
For many years we had a simple queue system, where a regularly executed loop would select and lock a next available row and call a stored procedure to actually process it, which, in turn would also select the same row with the same locking hints.

In a simplified form:

begin tran;

select top (1) @current_queue_row_id = QueueTable.row_id
from
  dbo.QueueTable with (updlock, rowlock, readpast)
  inner join <other tables>
where
  QueueTable.status = 'Placed'
  and <other tables>.something = 'OK';

if @current_queue_row_id is not null
    exec dbo.ProcessQueueItem @current_queue_row_id;

commit tran;

create procedure dbo.ProcessQueueItem
    @queue_row_id int
as
begin
    ...
    select <@variables> = <columns>
    from dbo.QueueTable with(rowlock, updlock, readpast)
    where row_id = @queue_row_id;
    ...
end;

This worked for years, but after updating to SQL Server 2016 we intermittently (several times a week) see a situation where the outer select top (1) would find a row, call ProcessQueueItem, and it would fail because its select returns nothing, so all @variables remain null. We have put in all sorts of logging and determined that the value passed to ProcessQueueItem is a valid one, and that current_transaction_id() returns the same value in the outer code and inside ProcessQueueItem.

The isolation level is READ COMMITTED, but no one is even trying to delete rows from anywhere, so non-repeatable reads should not be a problem.

Is there something fundamentally wrong with our setup in the first place, so that it's no wonder it broke after a server upgrade, or does this look like a problem in SQL Server 2016?


texts: Executing sys.dm_fts_parser without sysadmin server role

texts:
A web application needs to execute sys.dm_fts_parser to discover which words SQL Server Fulltext Search used to match documents for the purpose of syntax highlighting.  That stored procedure can only be executed if the caller is in the sysadmin server role.

  Permissions

  Requires membership in the sysadmin fixed server role and access rights to the specified stoplist.

http://msdn.microsoft.com/en-us/library/cc280463%28v=sql.105%29.aspx

Since it would be most unwise to grant that server role to the web server's user, I attempted to create a stored procedure that runs as a separate user that is in that role.

create procedure usp_fts_parser
    @query nvarchar(max)
with execute as owner
as
select display_term from sys.dm_fts_parser(@query, 1033, 0, 0);
go

I then created a user login_sign_fts_parser, made it the owner of the stored procedure

  alter authorization on usp_fts_parser to login_sign_fts_parser

and attempt to allow the web server's DB user rights to impersonate that user:

  GRANT IMPERSONATE ON USER::[IIS APPPOOL\Strategic Window] TO login_sign_fts_parser

When I attempt to execute usp_fts_parser, I receive the error:

  Cannot execute as the database principal because the principal "login_sign_fts_parser" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Why do I get this error?  How can I accomplish my goal of executing sys.dm_fts_parser without granting undue permissions to the web server's DB user?


texts: how to remove all permissions in a database?

texts:
every time I need to either restore a database or reinitialize a replication publication I first (on the restored to be or subscription database) I script all the permissions, triggers, indexes, synonyms, sometimes statistics

then I do the restore or (apply the snapshot) and then
re-apply my saved scripts on the restored database.

I have some LIVE servers where the db_denydatawriter have been used profusely and although I have all the permissions as they used to be, because the db_denydatawriter comes with the restore (because it was in live) the new restored database has it now.

and it causes problems.

so much so that now I have this script (uses sp_foreachdb) just to remove that in test:

     declare @db_list NVARCHAR(MAX)

     SELECT @db_list  = STUFF((
                                SELECT ', ' + name FROM sys.databases
                                WHERE name NOT IN ('DBA','TABLEBACKUPS','MASTER','MSDB','MODEL','TEMPDB','distribution')
                                FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')

     SELECT [@db_list]  =@db_list

     exec sp_foreachdb @database_list = @db_list
                     ,@exclude_list = 'dba,tablebackups,master,msdb,model,tempdb'
                     ,@command='use ?;begin try EXEC sp_droprolemember @rolename = [db_denydatawriter], @membername = [mycompany\Developers] end try begin catch end catch '

question:

How can I delete all the permissions in the newly restored database before I apply my scripts containing the permissions before the restore?

In other words, what would be the easiest way to remove all existing permissions (after the restore) before I re-apply the former permissions?


texts: Nested aggregate functions over different dimensions

texts:
I have a table where I periodically record the throughput of all network interfaces on a set of servers:

create table net (
    ts timestamptz not null,
    host text not null,
    interface text not null,
    recv_bytes bigint not null
);
create index on net (ts);
insert into net (ts, host, interface, recv_bytes) values
    ('2017-01-01 00:00:00+00', 'a', 'eth0',  500),
    ('2017-01-01 00:00:00+00', 'b', 'eth1', 2000),
    ('2017-01-01 00:00:01+00', 'b', 'eth0', 1000),  -- measurements arrive with some jitter, +1s here
    ('2017-01-01 00:00:02+00', 'c', 'eth0',  100),  -- only present in this interval
    ('2017-01-01 00:00:04+00', 'b', 'eth1', 1900),
    ('2017-01-01 00:00:05+00', 'a', 'eth0',  550),
    ('2017-01-01 00:00:05+00', 'b', 'eth0', 1200),

    ('2017-01-01 00:00:10+00', 'a', 'eth0',  600),
    ('2017-01-01 00:00:10+00', 'b', 'eth0', 1500),
    ('2017-01-01 00:00:11+00', 'b', 'eth1', 1900),
    ('2017-01-01 00:00:15+00', 'a', 'eth0',  600),
    ('2017-01-01 00:00:15+00', 'b', 'eth1', 1400),
    ('2017-01-01 00:00:16+00', 'b', 'eth0', 1400),
    ('2017-01-01 00:00:16+00', 'b', 'eth1', 1700);  -- (b,eth1) appears 3 times in this interval

I would like to find the total throughput across all interfaces for a given time range, averaged over 10 second intervals. If a (host, interface) pair is missing data for a given interval, it can simply be omitted. This is what I came up with:

select ts_interval, host, sum(recv_bytes) as recv_bytes
from (
    select
        to_timestamp(floor(extract(epoch from ts) / 10) * 10) as ts_interval,
        host,
        interface,
        avg(recv_bytes) as recv_bytes
    from net
    group by ts_interval, host, interface
) avg_net
where ts_interval >= '2017-01-01 00:00:00+00' and ts_interval < '2017-01-01 00:00:20+00'
group by ts_interval, host
order by ts_interval, host;

Result:

      ts_interval       | host |      recv_bytes
------------------------+------+-----------------------
 2017-01-01 00:00:00+00 | a    |  525.0000000000000000
 2017-01-01 00:00:00+00 | b    | 3050.0000000000000000
 2017-01-01 00:00:00+00 | c    |  100.0000000000000000
 2017-01-01 00:00:10+00 | a    |  600.0000000000000000
 2017-01-01 00:00:10+00 | b    | 3116.6666666666666667

This query seems overly verbose. The essence of that I'm trying to do is to sum over one dimension and average over the other. In pseudo-SQL:

select
    to_timestamp(floor(extract(epoch from ts) / 10) * 10) as ts_interval,
    host,
    sum(avg(recv_bytes OVER ts_interval) OVER host) as recv_bytes
from net
where ts >= '2017-01-01 00:00:00+00' and ts < '2017-01-01 00:00:20+00'
group by ts_interval, host
order by ts_interval, host;

Is there a more concise way to do this?


texts:
I'm designing a document with MongoDB where users have relationships with each other. Easy enough, right?

{
    _id: "012",
    username: "happy4u",
    // ...
    related: [
        '123', // user ids
        '456',
        '789'
    ]
}

But then I want to check if some user (the above), has some attribute in common with other guys in his relation. This is also easy, right?

But what if I wanted to check what he has in common with other guys related to the users in his own related list, not just one level, but multiple levels?

I thought of having a loop over the related list and then checking the result, then have another find in those results and have another one in the result's results, ... but this makes no sense to me.

Then I thought of having a separate document for each user and storing their relations, perhaps 4 levels of relation. Something like an index of my own. This also is very hard to keep clean and every update in some user's list will require updates in this document. Not just one place but in multiple places. This way also makes no sense to me.

How can I achieve my goal? Do I need to change my schema? How can I design  my schema to be more dynamic?


texts:
Is there a way we can get a document's creation date in Couchbase (2.2.0).

We are currently not storing creation date in our documents, so wanted to know if couch stores that as part of document meta data.

We plan to query documents based on time, and currently the only option that we think will work is to change the server code to store additional creation date field as part of the document itself.


texts: Is it possible to configure pgbouncer to distribute queries among master and slave?

texts:
We have master (write-read) and slave (readonly) Postgres 10 databases. Slave is online replication from master.

Now our application (c# webapi) connects to master only directly. We want to execute all SELECT queries on slave to distribute load among our hardware.

Idea is add some connection pooler that will pass all SELECTS on slave, other queries pass to master
If replication falls down, it will detect that automatically ASAP and pass all SELECTS to master till replication online again.

Is pgbouncer ok for that? Some another tool?


texts: Unable to get SQL Server 2016 CTP3 service to start on Windows 10

texts:
During install of MS SQL Server 2016, on a developer/DBA workstation client PC which is joined to a domain, the installation using all logon and other installer defaults, completes with failures, and a very generic message that just means that the main SQL Database Engine Service (MSSQLSERVER) did not start. Starting it manually from services.msc, one sees this equally generic error:

Windows could not start the SQL Server (MSSQLSERVER) on Local Computer.
For more information, review the System Event Log. If this is a
non-Microsoft service, contact the service vendor, and refer
to service-specific error code -2146885628.

In the system event viewer (eventvwr.msc) there are several failures:

#1. (1500) User Profile Service error for user NT SERVICE\MSSQLSERVER: Windows
cannot log you on because your profile cannot be loaded. Check that
you are connected to the network, and that your network is
functioning correctly.

#2. (17826) Could not start the network library because of an internal error in
the network library. To determine the cause, review the errors
immediately preceding this one in the error log.

#3. (17182) TDSSNIClient initialization failed with error 0x80092004,
 status code 0x1. Reason: Initialization failed with an infrastructure
 error. Check for previous errors. Cannot find object or property.

#4. (17182)  TDSSNIClient initialization failed with error 0x80092004, status
code 0x80. Reason: Unable to initialize SSL support. Cannot find
object or property.

#5. (17190) Initializing the FallBack certificate failed with error
code: 1, state: 20, error number: 0.

There are hundreds of kilobytes of log files, but no clear output in any of them to indicate a root cause.

This appears to be a variant of the SSL-init-failure startup failure case, but I had this same error with CTP 2.3, and was hoping that the SQL Server installer would help me figure out why none of the Windows 10 PCs in my local office domain will accept and run MS SQL 2016 (or MS SQL 2014) without hitting some variation of these SQL errors.

SystemConfigurationCheck_Report.htm contains no errors, everything is green checkmarks, and no errors were reported during the actual installation. There were no other MS SQL services/instances on the machine previously, but there is a Visual Studio 2015 and MS SQL localdb.

Is this an SQL error, or some local profile error, or something else? I tried this in SQL 2016 CTP 2.3  and 2.4, and had the same issue.  I decided to wait until SQL 2016 CTP 3, which is now out, but I have the same issue.

It seems to me that on windows 10, with the sql service logged on as NT SERVICE\MSSQLSERVER everything SHOULD just work. But it does not.

This question really boils down to "Why does MS SQL pick a service login account named NT SERVICE\MSSQLSERVERand then fail to ensure that this account is valid, and can work with SSL, or has a local profile that is valid, or whatever else sqlsrvr.exe needs to run?", and given that the installer default behaviour is unsuitable even for a DBA or SQL Developer's own private workstation (a non production environment), what should one do instead?


texts: DBA Permissions on LOCAL SERVER containing SQL SERVER

texts:
BACKGROUND: Infrastructure team has revoked local admin where SQL Servers resides to everyone including DBAs. Finally, they agreed to let us RDP to the SQL Servers (which they had taken out), but any of the tasks we need elevated permissions are being done via a webex where the individual with rights logs into the server and gives me control to do what I have to do. (I still have to wait for that person to be available to do this for me).

I am Database Administrator III, MCSE, MCSA, and I can't even support my servers. My coworker and I provide support to over 140+ SQL servers from 2008-2014.  95% are virtual servers (Hypervisor). I am trying to get opinions from the SQL Server experts in the industry so I can document the answers for management, and would really appreciate your comments.

QUESTION: * What is an appropriate workaround? *

These new security policies implemented (and without letting us know when they were going into effect) against all the staff in IT, and have prevented my fellow DBA and I from doing the very basic DBA tasks (which I have already documented for my manager and explained locations affected, registry keys, etc..):

Install SQL server
Install Upgrades, Hotfixes, or apply patches to SQL server
Upgrade or install features/components to SQL server
Create databases
Create data or log files and or move them to different devices
Manage files or file groups for space utilization, cleanup, and TROUBLESHOOT
Backup and Restores
Install Microsoft Assessment and planning tool.  Had to install under someone else’s login now I can’t run it or access the database in (localdb)\maptoolkit
Reboot the server after installs or to clear up an issue.- I’m ok with infrastructure doing this
Start and stop services for SQL components.- I’m ok with infrastructure doing this
Implement:

replication
high availability
SQL Server cluster
another business continuity strategy

Implement auditing
Import/export data
Start the instance of SQL Server in single-user mode
Connect to SQL Server When System Administrators Are Locked Out
Server Configuration Manager
Create jobs in the OS scheduler if needed - I’m ok with infrastructure doing this if needed, and we can provide scripts

What is an appropriate workaround?


texts:
Hi I'm trying to create a login role (user) with a fairly minimal ability to modify the database. They should be able to select, delete, create triggers, insert and update. I would then like to have multiple accounts which are members of user so they have the same permissions but I can log edits for individual users.

I'm using pgadmin for my database design and my program accesses this with QtSQL.

The error message I get is caught by Qt when trying to select some data as a test. When I run the same code as the database owner and superuser I have no problems.

bool run_query(const QString & q){
    QSqlDatabase db = QSqlDatabase::database("default");
    QSqlQuery query(db);

    query.exec(q);

    if (!query.isActive()){
        QMessageBox::warning(0, QObject::tr("Database Error"),
                             query.lastError().text());
        return false;
    }

    while (query.next()){
        QString title = query.value(0).toString();
        std::cerr << qPrintable(title) << std::endl;
    }
    return true;
}

run_query("Select forename from contacts;");

  ERROR:  permission denied for relation contacts
  QPSQL: Unable to create query

So far I've created the role (user).

-- Role: user

-- DROP ROLE "user";

CREATE ROLE "user" LOGIN
  ENCRYPTED PASSWORD 'md54d45974e13472b5a0be3533de4666414'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
COMMENT ON ROLE "user" IS 'Low level user';

As this gave the previous problems. I tried adding permissions for public for the database but this didn't work either.

Now I've no idea where to look for a solution. Any help is much appreciated.
Thanks.


texts: New instance with same @@VERSION as existing instances

texts: I have a SQL Server 2008 R2 server with several instances installed. Is there a way to add a new instance and have it be to the same build as the existing (and updated / patched) instances?


texts: How to GRANT SELECT by default to B, on tables created by A?

texts:
Background
In my PostgreSQL instance (say with one unique database constituted of one table only), I have 2 users:

user_A, who can create and populate my_table,
user_B, who should only be able to read (SELECT) from it.

It happens that, because of an API I do not control, user_A keeps dropping and recreating my_table from scratch.
This causes a reset of user_B privileges on my_table.

Thus, I am forced to

my_db=# GRANT SELECT on TABLE my_table TO user_B;

every time user_A recreates my_table.

Notes:
I know (cf. Grant permissions to user for any new tables created in postgresql) default privileges can only be set for objects created by the user they are granted to (i.e., that would be easier to set default privileges to user_B for tables created by user_B) — but this is not the case here.

Moreover, as mentioned in question linked above, the documentation specifies that "You can change default privileges only for objects that will be created by yourself or by roles that you are a member of".
So I could have

CREATE ROLE super_role;
GRANT super_role TO user_A;
GRANT super_role TO user_B;

and then SET ROLE super_role before user_A creates the table… but I unfortunately have no control over how user_A creates it.

Question
How can I GRANT SELECT by default to user_B on tables created by user_A?


texts:
in most of my tables, command

SELECT column_name FROM information_schema.columns WHERE table_name='table'

worked as expected, but in one table i am getting additional tables that i was not expecting. What are those and how can i not see them ?

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.12    |
+-----------+

mysql> DESCRIBE users;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| userID      | int(11)      | NO   | PRI | NULL    | auto_increment |
| username    | varchar(20)  | YES  |     | NULL    |                |
| password    | varchar(100) | YES  |     | NULL    |                |
| email       | varchar(50)  | YES  |     | NULL    |                |
| avatar      | varchar(255) | YES  |     | NULL    |                |
| admin       | tinyint(1)   | YES  |     | NULL    |                |
| createdTime | datetime     | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

mysql> SELECT column_name FROM information_schema.columns WHERE table_name='users' ORDER BY ORDINAL_POSITION;
+---------------------+
| COLUMN_NAME         |
+---------------------+
| userID              |
| USER                |
| CURRENT_CONNECTIONS |
| username            |
| password            |
| TOTAL_CONNECTIONS   |
| email               |
| avatar              |
| admin               |
| createdTime         |
+---------------------+
10 rows in set (0.01 sec)

As you can see, I am getting 3 additional columns "USER, CURRENT_CONNECTIONS, TOTAL_CONNECTIONS" which i was not expecting. I need just column names as they are so that i can pass those columns to python for parsing

Is maybe name 'users' clashing with some internal Mysql column or other database?


texts: Do dormant databases impact server performance?

texts: On our web server, my team has been creating new databases in MySQL to manage backups.  They simply create new database_DATE and copy over our live site.  Which made me think today...  Can having a database (no possibility of ever being used) impact performance (guessing memory) of the server?


texts: Create view to know missing records between two tables

texts:
I have two tables, Tab_A is a template that generate Tab_B.
Tab_A:
ID,
ORDER,
ASSEMBLY,
DESCRIPTION.

Tab_B:
ID,
ORDER,
ASSEMBLY,
DESCRIPTION.

Tab_B in same situation might have less records then his template Tab_A.

How can I create a view to know less records?
Best regards


texts: Is SQL Server 2012 Management Studio "Express" any different?

texts: Are there any differences from the version of SSMS that comes as part of the SQL Server 2012 Enterprise Installer and the SQL Server 2012 Express Management Studio?


texts: Modelling a relationship involving books, book parts and reviews

texts:
I would like to model something similar to the following scenario in a database built on MySQL:

I am managing reviews of texts. These texts can either be books or parts of books (e. g. different articles). There may be multiple reviews per text. It can also be guaranteed there is an identifying relationship between from an article to the containing book.

My approach was the following:

The table Review has an ID as a primary key, a name and a foreign key to the primary keys of Book and Book Part. Book and Book Part have primary keys and some custom attributes for each (see below). Additionally, there is an identifying 1-to-n relationship between Book and Book Part.

Is this a viable approach?

Edit: I created a picture for the model.

Description of attributes

Review
------
author - author of the review
rating - an integer describing how well the author liked the text
idBook - book the review is about
idBook_Part - book part the review is about

Book
----
author - author of the book
year - year the book was published

Book Part
---------
author - author of the book part
start page - page where the book part starts in the book
end page - page where the book part ends in the book
idBook - book that contains the part

Note: The attributes of Book and Book Part are intentionally not supersets of each other.

To make sure Review contains either an idBook or an idBook_Part I have written a MySQL trigger for INSERT and UPDATE statements that checks that exactly one of them is NULL.

References to literature are appreciated as well.


texts:
In the past couple of months my SQL Server instance has been experiencing higher than usual CPU usage than before, at times hitting 100% and affecting an application.
There have been no recent changes to any SQL Server configurations or options in that time, the OS may have in terms of Patching but that is another teams area, and it was not operating like this before the past couple of months.When we have high sustained period of CPU use, I see mostly wait stats of CXPACKET, LCK_M_X, THREADPOOL, LATCH_EX, RESOURCE_SEMEPHOR_QUERY_COMPILE.  CXPACKET for parallelism being by far the largest.
I like to run Brent Ozar’s sp_blitzcache and the one thing that stands out is that the plan cache seems to be cleared by something and 99% of the plans are created in the last hour.  This says this could be memory pressure or plan cache instability.I’m not sure what to check of where to look.
I have thought of enabling LPIM which isn’t now but besides this not sure what to try and I would be grateful for any help.
Edit**
I had started to log sp_whoisactive into a table. I found that there was a long running process of the application that was running a very intensive denormalisation process on the database, that gradually built and built until it finally starte a stage when it rebuild indexes.
Obviously this is what killed server performance.  We have made sure this does not run again.
However the business has become very twitchy about the appplication/server.  On friday there was a period when again the CPU maxed out to 100% for about 10 mins, unlike before when it lasted longer, this time it stopped after the 15mins. From looking at the results of sp_whoisactive I can see before the CPU got maxed I started to see a high number of RESOURCE_SEMAPHORE_QUERY_COMPILE wait types, these carried on for about 10 mins. I kind of understand what this wait type is, but did this cause the high CPU for the period, as some kind of cumulative effect of threads waiting?
If it was, how can I fix this so that it won't happen again?
I don't have any scope to tune queries/IXs, would adding more memory help?
*****Edit 2 *****
Update on thisIt seems that the inital issue was another team who started to run a Denormalisation process on the database that was really intensive and just killed the server.
After they were stopped doing this I  recieved very few RESOURCE_SEMAPHORE_QUERY_COMPILE wait types unlike before
The situation was much better than before, however CPU was still hitting 100% at times for longer than I wanted and I was still experiencing plan cache instability, it was continually being trimmed.
After coming across this webpage - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6b9118f3-b6c1-4f39-ae80-d149392656f9/sql-server-2016-memory-pressure-leads-to-the-plan-cache-clearing?forum=sqldatabaseengineI realised that something at the os level had changed and must have been needing more memory, causing memory pressure and then it taking memory resource away from sql server and the plan cache.
I lowered my instance max server memory setting and things have settled down much more, CPU overall is significantly lower and rarely hitting 100% and the plan cache is a nice size and never being significantly trimmed down.


texts: Different databases should have same scructure/values on tables

texts:
I would not like to use TRIGGERS for this, but I think it's the only way.

I have 2 databases with the same structure. What I would like to do is when someone inserts/updates something in this table from Database1, it should insert/update in the same table on database2. should I use triggers on this? I don't like to use triggers because it can mess things up.


texts: User defined data types causing implicit conversions

texts:
user defined datatypes, i'm looking at some execution plans and seeing implicit conversions happening in the background when a var is declared of type user defined data type and pulling a value into that var from a column that uses that user defined data type, it's showing it as an implicit conversion. Is this how it functions normally?

Added

ok so under Programmability -> Types -> User-Defined Data Types: there is

dbo.BOOL(char(1), not null)

and

ImageAccessibleFLAG TYPE_NAME is BOOL

New test

I can make an implicit conversion happen by doing the following
reasonforcreatetype  is char(1) in the database

declare @test char(1)
set @test = (select top 1 reasonforcreatetype from result)
select @test

<ScalarOperator ScalarString="CONVERT_IMPLICIT(char(1),[Result].[ReasonForCreateType],0)">

Now I create a new table with a char(1) and a bool.
Run the same query and I don't get the implicit conversion.

Update 2

Ok when adding a new column under the same table having the issue I get the following

Warning: Columns have different ANSI_PADDING settings.
New columns will be created with ANSI_PADDING 'on'.

I'm assuming this has something to do with it now but I'm not sure the proper way to fix or correct these issues.


texts: Access or modify tnsnames.ora on AWS Oracle RDS

texts:
I have an Oracle RDS instance running on AWS and I need to access the tnsnames.ora file and check its configuration since I am getting this error

Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

which is directly connected these two following resolutions

How to Fix 'ORA-12505, TNS:listener does not currently know of SID given in connect descriptor'
TNS:listener does not currently know of SID given in connect descriptor

the problem is that AWS does NOT allow remote access to the Oracle RDS and I cannot find anything in the AWS console which would help me change or even access the contents of the tnsnames.ora.

Can I maybe do it using SQL? I'm at my wits end here... This error does not even seem to be consistent since for example some queries do work....

///
Adding more info after reading some comments:

Queries like the following are successfully executed:

SELECT COUNT(*) FROM (

SELECT
  DISTINCT tab.column_a, tab.column_a AS L0, tab.column_a AS L1, tab.column_a AS L2
FROM
  ADMIN.table_something tab
WHERE
  tab.column_a IS NOT NULL

)

the ones that fail look like this

SELECT
  DISTINCT tab.column_a, tab.column_a AS L0, tab.column_a AS L1, tab.column_a AS L2
FROM
  ADMIN.table_something tab
WHERE
  tab.column_a IS NOT NULL

and by fail I mean producing the error I mentioned above. Executing these from a SQL command prompt produces no errors.

I am using this jdbc URL:

jdbc:oracle:thin:@<host_URL>:1521:SID

I also tried using this format

jdbc:oracle:thin:@<host_URL>:1521/ServiceNames

But apparently the AWS RDS instance is not configured with service names. Most of the answers I've found refer to the tnsnames.ora as the place where the service names and sids are configured and therefore includes information on how to actually form a connection string. This is why I want to access it and if I find something suspicious then modify it.


texts: How to restore Network backup to Network Path in SQL Server 2005?

texts:
I am trying to restore network backup file to network path.. is there any way to do it?

I am using SQL Server 2005 and code....

RESTORE DATABASE DB_NAme
FROM DISK = N'\\NetworkPath\backup_20140501030004.BAK'
WITH  FILE = 1
, MOVE 'DB_Data' TO '\\NetworkPath\DATA\DB.mdf'
, MOVE 'DB_Data2' TO '\\NetworkPath\DATA\DB_1.mdf'
, MOVE 'DB_Data3' TO '\\NetworkPath\DATA\DB_2.mdf'
, MOVE 'DB_Log' TO '\\NetworkPath\Log\DB_log.ldf'
, NOUNLOAD,REPLACE, STATS = 10;

Error:

  Msg 5110, Level 16, State 2, Line 2
  The file "\NetworkPath\DATA\DB.mdf" is on a network path that is not supported for database files.
  Msg 3156, Level 16, State 3, Line 2
  File 'DB_Data' cannot be restored to '\NetworkPath\DATA\DB.mdf'. Use WITH MOVE to identify a valid location for the file.

Thanks in advance


texts:
A simple query fails with the error. Has anyone faced this issue or has any solutions?

   SELECT MAX(some_value)
      FROM some_table
    WHERE some_id = 62328188199
       AND some_name = 'someParameter'
       AND someother_name = 'name';

  ORA-00600: internal error code, arguments: [kksgaItLvl_Setup: desc],
  [3], [3], [], [], [], [], [], [], [], [], []


texts: BULK INSERT Data from file but ignore the first occurence of field terminator

texts:
I have a file that has data arranged in the following style:

    1    1    0.5102    0.4898    0.0000    0.0000    0.0000
    1    2    0.3114    0.6886    0.0000    0.0000    0.0000

I need to load this into a table on MS-SQL and process it. I am using the BULK INSERT statement with the following settings

FIELDTERMINATOR = '    '
ROWTERMINATOR = '\n'

But what happens is that I end up with a table having this content (NOTE: I made all columns VARCHAR to see how the data is getting imported):

COL1    COL2    COL3    COL3    COL5    COL6    COL7
NULL    1       1       0.5102  0.4898  0.0000  0.0000    0.0000
NULL    1       2       0.3114  0.6886  0.0000  0.0000    0.0000

Essentially, there is a '    '(four spaces) before the first column of data. But BULK IMPORT assumes that the first column is NULL and offsets the data by a column.

Now, my question - is it possible to obviate this issue? Can we instruct BULK INSERT to ignore the first occurrence of the FIELD TERMINATOR? I cannot change the input file as it is used by some other parts of the process.


texts: SOA + Fusion + Weblogic + Oracle 11g - STUCK threads

texts:
This is sort of a last-ditch effort.  We have Oracle SOA on Fusion, 11.1.1.5.0 (+ patches on a separate server) running, and nearing the point where we're taking it into production.  I've posted on OTN and talked to numerous folks at Oracle about this problem, but no one can seem to help me.

When under production-level load (simulating about 1 request every 30 seconds) Weblogic encounters STUCK threads and SOA simply stops processing composite instances.  The composite that fails changes from time to time, and the logs indicate that deadlocked thread is trying to load a WSDL or referenced schema file in MDS.  We have several shared schema and WSDL files across many composites on multiple partitions.  Each SOA partition has its own MDS repository.  The end result is that the Weblogic server needs to be restarted before processing can proceed.  There's nothing logged in the audit trail and nothing blatantly evident in the server's diagnostics logs.  I upped the logging to the most verbose level, and can't personally see a pattern.  I have since given Oracle the best possible logs and thread dumps available.

In the audit trail, I can click into the faulting mediator/composite for details, and the call graph shows that it calls the related service, but there's no link, and viewing the invoked composite in EM does not show an entry related to that invocation.

Is this a familiar symptom to anyone?


texts: making a trigger to update another table using old values

texts:
there are 3 tables, product, invoice and LINE
so what im trying to do is create a trigger, so that when i delete a row from one LINE, it will subsequently subtract the amount of the deleted row from the other two rows, essentially updating the other rows.

what i have so far is:

BEGIN

     UPDATE PRODUCT
            SET PRODUCT.P_QOH = P_QOH - deleted.P_QOH
            WHERE PRODUCT.P_CODE863 = deleted.P_CODE863;

    UPDATE INVOICE
            SET INVOICE.INV_AMOUNT = INV_AMOUNT - DELETED.INV_AMOUNT
            WHERE INVOICE.INV_NUMBER863 = DELETED.INV_NUMBER863;

END; $$
DELIMITER ;


texts: Fastest way to add a field to a large table

texts:
I have a fairly large table (approx 400 million rows) and I need to add new column of type bit with NOT NULL constraint. My approach is to add the field without NOT NULL constraint, set the field to 0 and the alter the column to add the NOT NULL constraint.
This is a pretty slow process. Is there a quicker approach?

I am on SQL Server 2014 Enterprise Edition


texts: Change LAST_NUMBER value in a sequence (NEXTVAL)

texts:
My question seems easy but i haven't found a solution.
I'm using the nexval sequence in a insert SQL script but we have errors because the LAST_NUMBER value haven't been updated and its value is not correct with tabla data. For this case for example the value is 5 and the table has 62 rows.

I've searched on google but don't found the command or the way yo change the LAST_NUMBER parameter of the sequence.

ALTER SEQUENCE rs2qnes0 START WITH 62;

or ALTER SEQUENCE rs2qnes0 LAST_NUMBER 62; don't work


texts:
We have a Microsoft SQL Server (SQL Server 2012 SP3), we've noticed that transaction log I/O is very high. We do have a busy server; we're hosted at Rackspace with SSD block storage.  The Logs are are their own partition, and the log is set to "auto grow" and is about 70GB in size (data file is about 100GB)

Any thoughts on how to improve I/O performance for the transaction logs?  We have a sense that it's affecting performance.

Transaction Logs are backedup every 30 minutes
I/O on the transaction log partition is 47% during normal activity
and when we run one of our jobs it goes to 90%+
We have a 8 vCPU cloud server and we have 8 temp db and 8 temp log
Attached performance graphsdb performance graph

I should mention that we don't have a DBA, I do have some SQL experience and we have SQL developers.

DBCC Log (info) shows 456 rows

NEW QUESTION: Could it be that my transaction log at 60GB is TOO LARGE? Would I get less I/O making the trans log smaller, say 20GB-30GB?


texts: How to use LIKE operator with multiple conditions at once in PostgreSQL

texts:
I want to select records using LIKE operator with multiple conditions, I can use OR between the conditions but I am looking for a short way to solve this issue.
I am trying the following query:

select *
from employees
where name like '%.%'
   OR name like '%,%'
   OR name like '%;%'
   OR name like '%/%' -- this code goes very long

I am looking for a way to shorten this query.
Any help is appreciated in advance.


texts: MySQL right join with tables with 100's of 1000's of rows

texts:
The following join is taking far too long in MySQL, almost half a day and it’s still not finished:
INSERT INTO `aaaaa`
SELECT *
FROM `chosenLang`
RIGHT JOIN `chosenLOCC_P`
    ON SUBSTRING_INDEX(SUBSTRING_INDEX
      (SUBSTRING_INDEX(`chosenLang`.`call`,'.',1),' ',1),'*',1)
       LIKE concat(`chosenLOCC_P`.`code`,`chosenLOCC_P`.`reference`)

chosenLang has  413826 rows, 42 columns (38 of them longtext, 3 of them varchar, 1 of them int(11)). call is varchar.
chosenLOCC_P has 117077 rows, 23 columns (21 of them longtext, 2 of them int(10)).
What can I do to maximise the speed of this query?


texts: Any ideas what is wrong with this code, Error at or near Insert on a new function

texts:
CREATE FUNCTION insert_m_a_k("odOrganizacijaId" uuid, "doOrganizacijaId" uuid) RETURNS boolean AS
$BODY$BEGIN
    INSERT INTO mapper_a_k (id, model_a_k_id, organizacija_id, k_a_k, k_a_k_ime, aktiven, deleted)
    SELECT  uuid_generate_v4(), model_a_k_id, doOrganizacijaId , k_a_k, k_a_k_ime, aktiven, deleted
    FROM mapper_a_k WHERE organizacija_id = odOrganizacijaId AND deleted = false;
END;$BODY$
LANGUAGE sql VOLATILE NOT LEAKPROOF;


texts: How to clear Last_SQL_Error in show slave status

texts:
a failed "create user root" query error is being displayed in show slave status\G

Password is visible there and I want to clear the error. How should I?


texts: change SSIS package protection level before deployment

texts:
I'm developing SSIS project locally using project deployment model. I use SQL Server Authentication to connect to remote server. During this period Protection level is set to EncryptSensitiveWithUserKey.

However, upon deployment this package will be executed in a completely different environment, using SSPI security context and with environment variables from SSIS catalog.

This means that right now, before deployment I have to manually change Protection level to DontSaveSensitive inside a project and every package in it. Only after that tedious procedure I deploy the project to remote server.

I cannot keep DontSaveSensitive locally because every time I run a package it will ask me for a password. I cannot deploy with EncryptSensitiveWithUserKey either, because it gives error saying it cannot decrypt some of the values (passwords).

My question is how can skip changes of protection level manually every time I deploy a package? How can I automate this?


texts: Best way to back up databases on a Microsoft SQL Server Availability Group DR Site node

texts:
I have an Availability Group (Primary only is readable) with 2 nodes locally and 1 node at a DR site.  Both sites have a backup system.  Locally, I’m using the local backup system to back up the AG data bases on the Primary node.  But what is the best strategy for backing up the AG data bases when the primary node fails over to the DR site?  Use the backups system at the DR site?  Use the backup system from the local site (if the data center hasn’t been hit by an asteroid)?  Both?  Something else?  And for System data bases (not in the Availability Group)?

My concerns are having local backups at the DR site (in case the other site is hit) vs. keeping the log chain intact to avoid full backups unnecessarily (1 of our data bases is almost a TB).

Thanks for any help or advice.


texts: Trigger and temp table or materialized view

texts:
I'm looking for the better/best solution to compare data in a table before update and after. This can be done with trigger and temp/global temp table or with read only m-view as far as I know.
Would like to ask DBA community which solution is better, more efficient etc... The table is not big, less then 1000 rows. It may grow in the future, but not much.
Which is better for big tables, with million(s) rows please?
Thank you all.


texts: How to migrate data of to another sql server

texts:
Good day,

I have a database running on a virtual machine hosted at our ISP. It is a fairly large database which has about 5GB of data inserted into it every day (network logs).

It is partitioned daily at midnight and has a file group and a file for every day.

We currently only have enough space to keep a few days data on the virtual machine. I have setup a dedicated machine at our offices which is running sql server with plenty of hdd space  and will be much better suited for running queries on the large volumes of data.

Now what I want to find out is:
how can I efficiently move off one (partition / filegroup) from sql1 machine to sql2 machine?

We don't have great internet here in South Africa, so any sort of compression / safety checks will be needed.

I was thinking I could backup the file to a .bak and zip it, then download that file to the sql2 server and restore it. However I am looking for a more integrated approach possibly using sql server and  want not not code my whole own program to do such a thing.

I know that sql server has a lot of mechanisms built in for data warehousing. I am sure that there is some component in sql which does something like this. After all I am trying to achieve a basic version of data warehousing.

What options do I have / does sql server provide me and where can I go look to how to implement them properly?

I am running Sql Server 2012 Enterprise on both machines.


texts: If dbo.DatabaseIntegrityCheck fails, what happens, i.e. how am I notified?

texts:
I'm new to Ola Hallengren's MaintenanceSolution (which looks fantastic), and DBA-ing generally. I've created a job to run dbo.DatabaseIntegrityCheck (weekly against ALL_DATABASES). If there are errors found by DBCC CHECKDB does this cause the proc to error and therefore the Job to fail, so I'll get a notification in the normal way from my job failing? Will it continue to verify the integrity of all the other databases or does it stop on the first problem DB it finds?
I couldn't find any notes on the overview, sorry if I missed something obvious.


texts: How to access SQL Server 2012 on EC2 server from my ASP.NET website published on the same EC2 server?

texts:
My site is not able to establish a connection to the SQL Server 2012 from EC2. My connection string is as follows:

Data Source=AMAZONA-2SSRDM3;Initial Catalog=MyDatabase;Integrated Security=SSPI;

As you can see I am using Windows Authentication; is there something wrong with this approach I am taking? I am new to Ec2

Please help

The New Connection string i am using is

 connectionString="Data Source=http://ec2-46-137-228-121.ap-southeast-1.compute.amazonaws.com/;Initial Catalog=MyDb;User ID=something;Password=something"

More Info : I just restored a new database on to my Ec2 Machine . My sql server instance was using only Windows Authentication which i changed to Mixed Mode. I created a new Login something and made it the Db Owner of the Restored Database as well


texts:
sql> SELECT * FROM runners;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | John Doe     |
|  2 | Jane Doe     |
|  3 | Alice Jones  |
|  4 | Bobby Louis  |
|  5 | Lisa Romero  |
+----+--------------+

sql> SELECT * FROM races;
+----+----------------+-----------+
| id | event          | winner_id |
+----+----------------+-----------+
|  1 | 100 meter dash |  2        |
|  2 | 500 meter dash |  3        |
|  3 | cross-country  |  2        |
|  4 | triathalon     |  NULL     |
+----+----------------+-----------+

What will be the result of the query below?

SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races);

The above query will return nothing because the races table contain null winner_id. But what is the reason behind this?


texts: Why does REGEXP_REPLACE concatenate with a previous result?

texts:
This looks like strange behaviour, and I get the same on the MySQL command line as via db<>fiddle:

create table t(foo varchar(255));

insert into t (foo) values
('the quick brown fox'),
('jumped over the lazy dog');

select * from t;

| foo                      |
| :----------------------- |
| the quick brown fox      |
| jumped over the lazy dog |

select regexp_replace(foo, '[^a-z]+', '-') from t;

| regexp_replace(foo, '[^a-z]+', '-')         |
| :------------------------------------------ |
| the-quick-brown-fox                         |
| the-quick-brown-foxjumped-over-the-lazy-dog |

db<>fiddle here

I'd expect to see:

select REGEXP_REPLACE(foo, '[^a-z]+', '-') from t;

| REGEXP_REPLACE(foo, '[^a-z]+', '-') |
| :---------------------------------- |
| the-quick-brown-fox                 |
| jumped-over-the-lazy-dog            |

Am I doing something wrong or is this a MySQL bug?


texts:
A server running only a MySql 5.7.30 instance consumes on average around 25% of its CPU, having over 100 threads connected, and of which generally 15-25 are running threads. The CPU in the server has 8 cores / 16 threads, and the load on CPU seems to be distributed over all cores/threads well.
Now a second identical CPU has been added to the server, but we notice that the second CPU does not get any load on any of its cores/threads. Are there any settings I need to adjust, to tell MySql to make use both CPU's?
Here are some settings from the my.ini on the machine:
default-storage-engine=INNODB
log-output=FILE
table_open_cache=2000
thread_cache_size=10
myisam_max_sort_file_size=100G
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
open_files_limit=4161
table_definition_cache=1400
binlog_row_event_max_size=8K
max_connections=301
tmp_table_size=6G
myisam_sort_buffer_size=12G
key_buffer_size=11M
innodb_buffer_pool_size=20G
innodb_log_file_size=2G
innodb_thread_concurrency=33
innodb_open_files=2000
join_buffer_size=4M
max_allowed_packet=32M
sort_buffer_size=512K
event-scheduler=ON
max_heap_table_size=32M
query_cache_type=1


texts: WARN max_connections from pt-variable-advisor - what to configure for DBaaS?

texts:
Our VM configuration (hosted on Vmware)

# cat /proc/cpuinfo |grep "cpu cores" | awk -F: '{ num+=$2 } END{ print "cpu cores", num }'
cpu cores 32

# free -h
             total       used       free     shared    buffers     cached
Mem:           62G        23G        39G       500K       349M        10G
-/+ buffers/cache:        12G        50G
Swap:          50G         0B        50G

I got from pt-variable-advisor a warning about max_connections:

pt-variable-advisor h=localhost,u=root,p=Quule0juqu7aifohvo2Ahratit --socket /var/vcap/sys/run/mysql/mysqld.sock
(...)
# WARN max_connections: If the server ever really has more than a thousand threads running, then the system is likely to spend more time scheduling threads than really doing useful work.
(...)

Why? Any details?

Configuration in my.cnf

max_connections                 = 15360

settings from prd db cluster (MariaDB 10.1.x and Galera)

MariaDB [(none)]> SHOW STATUS WHERE `variable_name` = 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 718   |
+-------------------+-------+
1 row in set (0.01 sec)

MariaDB [(none)]> SHOW STATUS WHERE `variable_name` = 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 924   |
+----------------------+-------+
1 row in set (0.02 sec)

I found a MySQL manual and an (very) old similar question.

The default value is 151 to improve performance

  The number of connections permitted is controlled by the
  max_connections system variable. The default value is 151 to improve
  performance when MySQL is used with the Apache Web server.
  (Previously, the default was 100.) If you need to support more
  connections, you should set a larger value for this variable.

and

  The maximum number of connections MySQL supports depends on the
  quality of the thread library on a given platform, the amount of RAM
  available, how much RAM is used for each connection, the workload from
  each connection, and the desired response time. Linux or Solaris
  should be able to support at least 500 to 1000 simultaneous
  connections routinely and as many as 10,000 connections

We currently have 460 users and every user is allowed to do 100 max_connections. This would be the max value. Is the 100 max_connections per user and database too high? With modern connection pooling we can set this to 20? How we should configure that not risking overloading our server with context switching? Is it possible that one web app use one connection (every statement over same connection)?


texts: EMStagePatches_$ORACLE_SID is using up space

texts:
We have this directory:

$ORACLE_HOME/EMStagePatches_$ORACLE_SID

We are running out of space and this directory is using up a lot of space. Is it safe to delete it? I think it is something to do with the Enterprise Manager patches. How can I stop Enterprise Manager from downloading stuff into that directory?

We are using Oracle Database 11g Enterprise Edition Release 11.1.0.6 running on Red Hat Enterprise Linux Server release 5.2.


texts: SQL Server Reporting Services (MSSQLSERVER) Process using high memory

texts:
We have the Reporting Services installed on our database but we are not actually using it. Generally, the running process takes up a few MB but all of a sudden it has jumped to 4GB. We have restarted the process but it immediately jumps to 4 GB.

I ran the below query but the results are empty

Use  ReportServer
 Go

select *
from ExecutionLog2
order by TimeStart DESC

What could we do to help identify what is happening with this process?

For context, we are running the 2012 BI edition


texts: Unique identifiers for the same type of object in the same request

texts:
This question is about your opinion and common approach for relational databases. So please, do not vote me down. I really need to solve this problem.. I remove this question once I meet the answer.

I have a response from my server sth like this:

"groups":[
              {
                 "id":"ea369ff6-cbc3-44b6-8eb6-b79e8db0145c",
                 "ingredients":[
                    {
                       "id":"f5126c1f-11a7-5ce5-a717-c6c86483058e",
                       "name":"First",
                       "position":1
                    },
                    {
                       "id":"dfcf217e-53dd-b06c-9f2b-084f8180ba1a",
                       "name":"Second",
                       "position":2
                    }
                 ]
              },
              {
                 "id":"0fa91ada-1fe4-4d6d-b772-caae44758848",
                 "position":2,
                 "ingredients":[
                    {
                       "id":"9cacade2-92fe-bd5c-c84c-7c5fe36c8a5e",
                       "name":"Third",
                       "position":1
                    },
                    {
                       "id":"f5126c1f-11a7-5ce5-a717-c6c86483058e",
                       "name":"First",
                       "position":2
                    }
                 ]
              }
           ]

Here are two groups. Each of them has unique id.
Every group has some ingredients. They are the same type
Every group has ingredient with name First. This ingredient has the same id in every group, which means, it is the same object.

but

First ingredient has different values of position depending on where it is located.

The question is:

Should they have the same id while they are not the same? What is good approach for this problem? I need simple, quick and clarified answer.

Doesnt id mean that this is unique along with the same type of object? What If I need to save it to core data or other persistent store on device?

Very thanks.


texts: Approaches towards Marking Membership of Group?

texts:
Given this somewhat SQL pseudo-code:

CREATE TABLE foo(
   id      NUMBER(5) PRIMARY KEY,
   marked  BOOL
);

I'll keep track of whether ID's have been "marked" or not.

Alternatively, I've thought to alter the table to consist of a list/set, more or less:

CREATE TABLE foo(
  id NUMBER(5) PRIMARY KEY
);

such that existence in the table indicates marked = Y, whereas, the opposite notes marked = N.

In other words, to determine whether, conceptually, an id is "marked," I would check whether it simply existed in the table.

Is there any advantage to choosing one over the other? Also, I don't have experience with the latter type of table structure. Is it common?


texts: How a DBA can monitor multiple huge SQL Server databases more than 2 tera bytes

texts:
I need a some suggestions from you people, I am not a DBA but moving to DBA platform.

I have some questions/clarifications can any body answer? Please.

We have almost 350 servers and more than 2500 database how can we monitor all those in a shot(interms is one DBA can handle or multiple DBA are there in a shift).
How can we monitor all those databases backups and performance.
If per Client individual how many production server will be there.
As an average a DBA how many databases can handle.
How will we provide security for Database?

Can any one answer these questions please? Thanks in advance


texts: Storing Live Timeseries Data in SQL Server Azure Database

texts:
I have an SQL Server Azure Database and am trying to store time-series data that I get from multiple sensors.
Data Source:- 5-minute data is obtained via an API.
Current table structure: -
 Timestamp | ComponentId | Parameter1 | Parameter2 | Parameter3

Each sensor has a unique ComponentId. I have a non-clustered index on Timestamp and ComponentId to eliminate duplicates and also a clustered column store index on the whole table (It compresses data and saves space. Also gives a performance boost for aggregate queries). A python script is used to fetch the data via the API and pyodbc library is used to push this data to the table. The script runs every 10 minutes and inserts data into the table.
Some queries like fetching data for a particular component for just one day seem to take 5 seconds. Is this normal?
QUERY:-
SELECT Timestamp,ComponentId,ParameterId FROM TABLE WHERE ComponentId=1 AND Timestamp BETWEEN '2021-05-01' AND '2021-05-02'

IO Stats: Table 'RawData'. Scan count 2, logical reads 10164, physical reads 3, page server reads 0, read-ahead reads 10146, page server read-ahead reads 0, lob logical reads 2766, lob physical reads 12, lob page server reads 0, lob read-ahead reads 4877, lob page server read-ahead reads 0.
Table 'RawData'. Segment reads 2, segment skipped 10.
Is this way of fetching/pushing data fine? Please let me know if it can be improved and if there are any better methods to do the same.


texts: Maintaining multiple build versions of asp .net / SQL Server applications

texts:
We have new versions of .net applications deployed every week for testing given in form of new build files. Can any one suggest how I can maintain many versions of the builds without having to keep many copies of builds and databases?

Here's what I have in mind :

For each build maintain a new folder in inetpub > wwwroot
Have one database
Keep SQL Server stored procedures in same one database in different^schemas(*) or keep it in local folder drives (*not sure how to do this)
In web.config file point to same database for each of the builds, but somehow be able to suggest which build version of stored procedures to use for each build from the web.config (again not sure how to do this)

Is this possible and how? A way of version control and deploy and maintain builds on a single db Or can anyone recommend other better methods for maintaining multiple build versions, any links or thoughts will be helpful


texts:
I am setting up Test SQL ( 2012) server cluster with always on feature.

Cluster consist of two windows server 2012 R2 servers.

I need to setup SQL service accounts,

Does anyone know if it will be good idea to use same service accounts for both servers in cluster or separate service accounts ?


texts:
We have an Oracle database, one data file (users tablespace) of the instance got corrupt and another data file (containing application data) was deleted due to some issue.

The database is in ARCHIVELOG mode, but we neither have data file backup of both data files nor the pertaining archive logs. Hence, I guess we cannot go ahead with the recovery of data files. I am unable to open the database as the data files are not available and face.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

I am just able to STARTUP MOUNT the database

I wanted to ask if we can somehow we can open the database by taking both the tablespaces offline after STARTUP MOUNT the database instance. Or if there is any other way I can start the database.

There was a power issue and the  server got abruptly shutdown. When we tried to restart the database and found that we were facing ORA-01589: must use RESETLOGS or NORESETLOGS option for database open. On further analysis we found that 2 of datafiles were creating problem as one got corrupt other deleted as mentioned. As of now if I try to restart the database it says one datafile which belongs to users tablespace need recovery. I wanted to confirm as if we can start the instance by bringing the tablespace offline.

I tried:

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/flowsystems/oradata02/flsta02/system01.dbf'

I don't have any archive logs.