texts: Recent degradation/variability of HashBytes performance

texts:
Up until a few days ago, T-SQL code we have running in SQL Azure that generates an MD5 for temp table values was fast (a few seconds per hundred thousand on a Business Critical Gen 5, 2 VCores DB). Recently we noticed from our logs that on multiple client DBs and in our tests there is tremendous variance leading to frequent timeouts.
Here is sample code shown generating MD5 and also for SHA2 (though MD5 is our production requirement).
begin transaction;

/********************************/
-- create temp table
/********************************/
CREATE TABLE #tmp (
  Id int PRIMARY KEY,
  UriValue nvarchar(256),
  Md5Value binary(16)
);

/********************************/
-- generate many records into temp table
-- source: https://dba.stackexchange.com/questions/130392/generate-and-insert-1-million-rows-into-simple-table
/********************************/
  WITH
    L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), -- 2^1
    L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),       -- 2^2
    L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),       -- 2^4
    L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),       -- 2^8
    L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),       -- 2^16
    L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),       -- 2^32
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS k FROM L5
  )
  INSERT INTO #tmp ( Id, UriValue )
  SELECT k as Id,
         'http://sample.com/' + CONVERT(nvarchar(50), NEWID()) as UriValue
  FROM nums
  WHERE k <= 100000

/********************************/
-- test creation of SHA2 value
/********************************/
  DECLARE @dt datetime;   -- timer
  SET @dt = GETUTCDATE();

  UPDATE #tmp
  SET Md5Value = CAST(HashBytes('SHA2_256', CONVERT(VARCHAR(MAX), LOWER(UriValue)) COLLATE Latin1_General_100_CI_AS_SC_UTF8) as binary(16));

  SELECT DATEDIFF(ms, @dt, GETUTCDATE()) AS Sha2ElapsedMs;

/********************************/
-- test creation of MD5 value
-- note: our requirement is for a match to http://md5-hash-online.waraxe.us/
/********************************/
  SET @dt = GETUTCDATE();

  UPDATE #tmp
  SET Md5Value = CAST(HashBytes('MD5', CONVERT(VARCHAR(MAX), LOWER(UriValue)) COLLATE Latin1_General_100_CI_AS_SC_UTF8) as binary(16));

  SELECT DATEDIFF(ms, @dt, GETUTCDATE()) AS Md5ElapsedMs;

rollback transaction;

Note the use of NEWID() to generate a GUID, so I don't think caching is the issue in our tests.
Here are some test runs (times in seconds):

Test
SHA2
MD5

1
45
39

2
4
3

3
34
38

4
9
9

No other processes are running against the DB during these tests. I see spikes in the Azure portal for CPU (90%) and SQL Server Process (99%) that seem to correlate.
Any idea what might have changed here? Or other ideas for how we can get more predictable performance?
The COLLATE Latin1_General_100_CI_AS_SC_UTF8 helps me to tie to http://md5-hash-online.waraxe.us/ (my requirement).
We cannot use SQLCLR because that requires SQL Server Azure Managed Instance. Currently we are on Azure SQL Database.
I get slightly better performance without cast/convert/collate. But it's within reason (and I need those adjustments to match my spec). I've never had a problem generating millions either... until one week ago (with no other changes and same data and across multiple Azure BizCritical DBs) and now variance is very high.
Here is the execution plan. Odd that SQL Server Management Studio shows the 'Compute Scalar Cost' as 0%.


texts: SQL Server switched to use pagefile exclusively instead of RAM and effectively froze the world

texts:
I was hammering my SQL Server instance yesterday for about 3 hours running an intensive calculation which was consuming all allocated physical RAM (I have it limited to 2GB due to my limited workstation hardware), and suddenly it (and many other things?) released all their memory in an apparent switch to exclusively using pagefile.

My computer was lagging so incredibly after that I was forced to restart (which took 10 minutes just to access the power options).

I was able to get a screencap of task manager before restarting (which I leave running in the background with a low update speed). With the window resized to show the maximum history, this is what it looked like:

This surprising behavior caused downtime. Is this a documented feature?

I am running Windows Server 2008 R2 with an Enterprise 2014 database engine.

Thanks.


texts: Optimizing a Query for splitting strings with a known size

texts:
I have a Column that has a . as a delimiter, it looks like so....

abc.efg.hij

I want a query that turns this into three columns, Col1, Col2, and Col3. I am wondering what the fastest way to do this is. So far I haven't been able to do very well with my limited database experience. I've got a function:

CREATE FUNCTION [dbo].[split](
   @delimited NVARCHAR(MAX),
   @delimiter NVARCHAR(100)
 ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
 AS
 BEGIN
   DECLARE @xml XML
   SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

   INSERT INTO @t(val)
   SELECT  r.value('.','varchar(MAX)') as item
   FROM  @xml.nodes('/t') as records(r)
   RETURN
 END

This is how I'm doing it right now but I believe it could be made to go much faster, I'm also open to a significantly better function or outside the box ideas for splitting strings.I believe I'm running this dbo.split(Name, '.') three times and could only be running it once.

SELECT
      Col1 = (SELECT Val from dbo.split(Name, '.') WHERE Id = '1'),
      Col2 = (SELECT Val from dbo.split(Name, '.')  WHERE Id = '2'),
      Col3 = (SELECT Val from dbo.split(Name, '.')  WHERE Id = '3')
FROM Mains

any help would be greatly appreciated


texts: SSMS Linked Server -ORA-28009: connection as SYS should be as SYSDBA or SYSOPER (Microsoft SQL Server, Error: 7303)

texts:
How could I use "Linked Server" from SSMS IDE to connect the Oracle server database with "SYSDBA"?

I've tried many methods as following to show you what I did right now:
Oracle client connect to Oracle Server data:

SSMS connect to Oracle Server data:

More testing information
I also use "SQLPLUS" to check tnsnames.ora the file is okay or not but the answer is as following

sqlplus SYS/Oradoc_db1@DBNAME as SYSDBA <-- is okay
sqlplus SYS/Oradoc_db1@DBNAME <-- Error


texts:
I have a table , which contains upto 20 columns. The data is selected, inserted, and deleted by 3 columns: Username, StartDate, and EndDate.

These 3 columns are NOT unique.

The table is a standalone table - no joins to other tables.

Do I create a clustered index on these 3 columns? Or do I create a UniqueID that autoincrements as a PK, and then create a non-clustered index on those 3 columns? Or is a Primary Key required at all?

SELECT StartDate, EndDate, Username, Value1, Value2, Value3, Value4, Value5, Value6
FROM  myTbl
WHERE Username = @Username
AND StartDate = @StartDate
AND EndDate = @EndDate;

DELETE FROM  myTbl
WHERE Username = @Username
AND StartDate = @StartDate
AND EndDate = @EndDate


texts: Setting up cloud database for syncing to local SQLite database

texts:
Background
I have a SQL Server database hosted on AWS RDS and there are web applications and WEB APIs that talk to the database. The database is a multi-tenant database and we are currently using SQL Server 2014 although we can upgrade if required.
A third-party developed a local client application on our behalf which has it's own SQLite database. This application is developed in Xamarin so it runs on Windows, iOS and Android. The local SQLite database must be kept in sync with the cloud database. Syncing data up to the cloud database is not a problem, but syncing data down is causing us issues. Currently we sync data down to the local database by asking the WEB API, every minute, to return all changes that have occurred since a particular date. The cloud database has a DateCreated, DateModified and DateDeleted column in every table and these columns are queried to see what data has changed since the last time the client synced data. The local application records the last successful sync date for each table.
Problem
This approach worked when there were few local clients and few tables to sync but as our client base has grown this approach doesn't seem scalable. We are running into performance issues on our cloud database and a lot of the time the sync-down tasks are cancelled due to timeouts or take ages to run. Our customers are complaining about the time it takes for changes they make on the cloud to sync down to the local application.
Potential Solution
Having researched various methods of tracking changes on SQL Server I believe that using the built-in Change Tracking feature is a better approach than using the DateCreated, DateModified and DateDeleted columns for tracking changes. What I am not sure about is how best to set this up.
Things to consider:

Not all columns on the cloud database tables need to sync to the local database - For example, TableA on the cloud database has 20 columns but its corresponding client TableA may only have 5
Not all data relating to a tenant needs to sync to their local database - for example if a record is marked as "inactive" for that tenant it should never be synced locally
A table on the local database may contain data from two or more tables on the cloud database
Not all tenants have the local application yet but they will eventually (this may take a year or more to roll out)

What I am thinking of doing is as follows:

Create a separate database in AWS RDS that exactly matches the local database
Enable change tracking on this database rather than on the main database
Use triggers to keep the main database in sync with the new database
Query the change tracking tables on the new database and return the changes to the local application
Create a new table to track if data has changed or not for each tenant and table - this way we won't need to query the change tracking tables each minute only to find that nothing has changed

The reason for the second database is to reduce the strain on the main database when clients are trying to sync data down and also keeping the schemas in sync reduces the complexity on the queries when a client requests to sync changes. For example, if a record is marked as "inactive" for the tenant in the main database, but that record has been changed, I don't want to have to filter this record out when the client requests to sync the data down. I would prefer to already have those records filtered out so that they would never exist in the second database at all. Hope that makes sense!
I would very much value your feedback on this approach and please feel free to suggest better ways of doing it. If there is something that is not clear please let me know and I'll update the question!


texts: Performance issues when extracting scatter plot data

texts:
First off: Sorry for the title, couldn't think of anything better. Feel free to change it though.

Ok, assume I have the following table:

 -----------------------------------------------------------------------------------
| id | category | value | foreign_id_1 | foreign_id_2 | foreign_id_3 | foreign_id_4 |
 -----------------------------------------------------------------------------------
|  1 | A        |     4 | 1            | 1            | 1            | 1            |
|  2 | B        |     9 | 1            | 1            | 1            | 1            |
|  3 | A        |     5 | 2            | 2            | 2            | 2            |
|  4 | A        |     6 | 3            | 3            | 3            | 3            |
|  5 | B        |     8 | 3            | 3            | 3            | 3            |
|    |          |       |              |      ...     |              |              |
 -----------------------------------------------------------------------------------

Now I want to create a scatter plot for the categories (A as x, B as y based on the value column).
The desired output would be:

 ----------------------
| foreign_id_1 | x | y |
 ----------------------
| 1            | 4 | 9 |
| 3            | 6 | 8 |
 ----------------------

Row 3 from the original table isn't contained, because there only is a value for A and not for B.

What I've tried so far is the following:

SELECT A.foreign_id_1, A.value as x, B.value AS y
FROM
  (
    SELECT value, foreign_id_1, foreign_id_2, foreign_id_3, foreign_id_4
    FROM table
    WHERE category = 'A'
  ) AS A
JOIN
  (
    SELECT value, foreign_id_1, foreign_id_2, foreign_id_3, foreign_id_4
    FROM table
    WHERE category = 'B'
  ) AS B
ON
  (
    A.foreign_id_1 = B.foreign_id_1
    AND A.foreign_id_2 = B.foreign_id_2
    AND A.foreign_id_3 = B.foreign_id_3
    AND A.foreign_id_4 = B.foreign_id_4
  )

That all works fine, but it's really slow (30s with my current data set and that might expand in the future). The problem seems to be that the only way to identify the "matching rows" (the ones that form x and y of a data point) is expensive, because all foreign keys have to match.

Now to the question:

Assuming that I cannot change the table structure, is there a way to make this query faster? Or maybe there is a different way to get the desired result that I couldn't think of?


texts: I have MySQL 5.5 running in RHEL. I have checked for my.cnf file in my filesystem and its available in two destinations. One in the user's home directory and the other is in /data/etc_ORIG directory. It was not seen in /etc or /etc/mysql folder. Is there any issue with the config file present in a different path? And how do I check which config is being used by MySQL?


texts: Find the correct entry in myqlbinlog (using Row Based Replication)

texts:
I've just started testing Row Based Replication on my MySQL 5.5. databases.

I have now got the following error:

Could not execute Update_rows event on table dbname.tablename;
Can't find record in 'tablename', Error_code: 1032;
handler error HA_ERR_KEY_NOT_FOUND;
the event's master log mysql-bin1.000941, end_log_pos 211713441;

So, I grab the binary log off the master and run mysqlbinlog on it:

c:/mysql/mysqlbinlog.exe -h127.0.0.1 -P3306 -uxxxx -pxxxxx c:/folder/mysql-bin1.000941 --base64-output=DECODE-ROWS -v --result-file=c:/folder/941.log

I open the file (in this case I use gvim for windows because of the size) and search for 211713441. This returns:

[more of the same above]
#160915  2:05:04 server id 21  end_log_pos 211711147    Update_rows: table id 34
#160915  2:05:04 server id 21  end_log_pos 211712319    Update_rows: table id 34
#160915  2:05:04 server id 21  end_log_pos 211713441    Update_rows: table id 34
#160915  2:05:04 server id 21  end_log_pos 211714547    Update_rows: table id 34
#160915  2:05:04 server id 21  end_log_pos 211715723    Update_rows: table id 34
[more of the same below]

and eventually it gets to:

### UPDATE `dbanme`.`tablename`
### WHERE
###   @1=123
###   @2=30
###   @3=0
###   @4=''
###   @5='12345'
###   @6='6789'
###   @7=1

which is basically all the (hundreds of) updates listed one after the other, with no indication of which one is which.

But how do I find the one that is causing the problem on the slave? (e.g. which one is at position 211713441) There are literally hundreds of updates, one after the other.

I tried the same with the realy log. According to SHOW SLAVE STATUS I needed:

Relay_Log_File = slave-relay-bin.000342
Relay_Log_Pos = 103036740

I run mysqlbinlog on the file as above, and open it up. Search for 103036740 and I get:

##   @47=128
# at 103036713
#160915  2:44:46 server id 21  end_log_pos 207893984    Xid = 117694200
COMMIT/*!*/;
# at 103036740
#160915  2:05:04 server id 21  end_log_pos 207894043    Query   thread_id=649029478 exec_time=2438  error_code=0
SET TIMESTAMP=1473901504/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
BEGIN
/*!*/;
# at 103036799
# at 103037130
# at 103037219
# at 103038389

After that there's a whole load more of the # at 1030.... and then the file ends.


texts: Stored procedure triggered on insert causes deadlock in transaction

texts:
I'm trying to debug a deadlock that happens when a stored procedure is triggered inside a transaction. Table and trigger definitions are as follows:

CREATE TABLE a (
  id int(11) NOT NULL AUTO INCREMENT,
  b varchar(10) NOT NULL,
  d date NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1; //

CREATE PROCEDURE CheckNoDuplicates (new_id int(11), new_b varchar(10), new_d date)
BEGIN
  DECLARE existingId INT(11);

  SELECT id INTO existingId
  FROM a
  WHERE new_b = b
      AND d IS NOT NULL AND new_d IS NOT NULL AND YEAR(d) = YEAR(new_d)
        OR d IS NULL AND new_d IS NULL
       AND id <> new_id;

  IF existingId IS NOT NULL THEN
    SIGNAL SQLSTATE '23000';
  END IF;
END //

CREATE TRIGGER NoDuplicateOnInsert BEFORE INSERT ON a
FOR EACH ROW
  BEGIN
    CALL CheckNoDuplicates(NEW.id, NEW.b, NEW.d);
  END //

CREATE TRIGGER NoDuplicateOnInsert BEFORE UPDATE ON a
FOR EACH ROW
  BEGIN
    CALL CheckNoDuplicates(NEW.id, NEW.b, NEW.d);
  END //

When two inserts happen at the same time inside a transaction, sometimes they succeed, sometimes they give me back a deadlock (Deadlock found when trying to get lock; try restarting transaction).

If I get rid of the stored procedure, I get no deadlocks anymore.

How are stored procedure executed inside a transaction?
Is there a better way to keep the constraint I'm trying to get (uniqueness on b and the YEAR of date d)?


texts: PostgreSQL: Can i use tuple in where clause?

texts:
Actually might be a simple question:
Given 2 tables:
table1: A,B,C
(A1,B1,28471),
(A1,B2,01244),
(A2,B1,1283a),
(A2,B2,82r7e);

table2: A,B,D,E,F,G
(A1,B1,18,1,6,8),
(A2,B2,18,2,3,0),
(A3,B1,18,7,1,4),
(A4,B2,18,1,9,6);

Will the following statement result in a the following result given the example tables:
SELECT E,F,G FROM table2 WHERE (A,B) IN (SELECT A,B FROM table1)

expected result:
 E F G
(1,6,8), -- (A1,B1)
(2,3,0); -- (A2,B2)

Can someone confirm this is a valid method to select based on tuples?
asking as the most likely next step is to UPDATE the E field by adding +5 for matching results


texts: how to replicate articles from different schemas?

texts:
from sp_addarticle (Transact-SQL)

I get an example as how to add an article to a publication.

this adds the table dbo.Product to the publication AdvWorksProductTran

DECLARE @publication    AS sysname;
DECLARE @table AS sysname;
DECLARE @filterclause AS nvarchar(500);
DECLARE @filtername AS nvarchar(386);
DECLARE @schemaowner AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @table = N'Product';
SET @filterclause = N'[DiscontinuedDate] IS NULL';
SET @filtername = N'filter_out_discontinued';
SET @schemaowner = N'Production';

-- Add a horizontally and vertically filtered article for the Product table.
-- Manually set @schema_option to ensure that the Production schema
-- is generated at the Subscriber (0x8000000).
EXEC sp_addarticle
    @publication = @publication,
    @article = @table,
    @source_object = @table,
    @source_owner = @schemaowner,
    @schema_option = 0x80030F3,
    @vertical_partition = N'true',
    @type = N'logbased',
    @filter_clause = @filterclause;

How would I do if I had also the following tables from different schemas to add to this publication?

I want to add these two tables to the publication, how do I do it using sp_addarticle?

my_schema01.Product
my_schema02.Product


texts: Unique-like constraint in a complex scenario

texts:
I have a table in SQL Server 2016, as shown below. Records are inserted into the table each quarter, for all partners.
Constraint Needed: Should allow only one BatchID for a partnerID for a year-quarter, for a Distribution_Type. However it is allowed to have multiple records with same BatchID for the partner for a given year-quarter, for a distribution type.
How to create such a constraint in the table? If it can be done without trigger that would be great.

Table
CREATE TABLE [dbo].[Distribution]
(
    [Distribtution_Result_ID] [int] IDENTITY(1,1) NOT NULL,
    [PartnerID] [int] NOT NULL,
    [Distribution_Year] [int] NOT NULL,
    [Distribution_Quarter] [int] NOT NULL,
    [Distribution_Amount] [decimal](16, 4) NULL,
    [Distribution_Type] [varchar](100) NULL,
    [Batch_ID] [int] NOT NULL
)


texts: How to edit current query without executing it?

texts:
I am aware of using \e in psql to edit the query in the buffer.
But what if you are currently creating the query for the first time and want to jump to the editor?
Or better, you go through history to a previous, expensive, query and want to add some edits before.
A nasty trick could be going to the first char of the line an add a comment (/*) and the same at the end, but something like C-xC-e in the shell will be great.


texts: Mysql replication rebuild dump database lock

texts:
I need to rebuild master-slave replication of whole instance of mariadb on innodb. What I often see is that people do read lock then dump master then unlock, like:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
mysqldump --all-databases > all.sql
UNLOCK TABLES;

In this scenario master is locked for significant amount of time. My master is production, so I would rather perform this operation without downtime. My plan is to do this instead:

mysqldump --single-transaction --master-data --all-databases > all.sql

Am I right to assume that in this case I do not need to keep database lock and dump is consistent and good to be used to rebuild the slave machine? I understand that at the beginning of my statement there will be short lock. I just do not see any need to perform additional lock in my case. Is that correct?

Additionally is --single-transaction assuring consistency of single database or whole instance? Is it just one transaction no matter how many databases are there on the instance or new transaction for each database?


texts: Generate backup script of SQL Agent Jobs using c#

texts:
I am using C# code to read all the SQL Agent Jobs:

static readonly string SqlAgentJobName = "SqlAgentJob_123";
static ServerConnection conn;
static Server server;
static Job job;

try
{
    conn = new ServerConnection(SqlServer);
    server = new Server(conn);
    job = server.JobServer.Jobs[SqlAgentJobName]; //Get the specified job
    GenerateScript(job); // I need idea to write this method.
}
catch (Exception ex)
{
    Console.WriteLine("Failed to fetch the job :" + ex.Message);
    throw ex;
}

How can I generate a script out of it, so that I can run it on any server?
When I run the program, it should read all jobs from the server and generate a script.

Is there any built-in method? Will SQL Server Development Tools help?


texts: Best procedure to measure query optimization effects?

texts:
I could not find valuable answers or explanations to the following question:

How should a procedure to test query improvements effects look like? Using SSMS I am not absolutely sure about the steps.

Currently I do the following:

restore the production DB to an isolated test server, to cut off external influence / usage that falsifies results by additional server usage.
rebuild all indexes and statistics.

Then I repetitively execute the following steps:

SET STATISTICS IO ON - to get information about IO usage
DBCC FREEPROCCACHE; CHECKPOINT; - to free plan cache and consider e.g. index changes when repeating / testing the changes
DBCC DROPCLEANBUFFERS; - to avoid usage of cached data to make sure I have a fair comparison on each query execution
run the query

After each execution I quickly import all the statistics outputs into an excel sheet, to compare the changes to originals (see picture) as well as the runtime, checking if the number of returned rows still match the original and inspect the new execution plan to see the effects.

Unfortunately this process takes a lot of time because each execution with cleared caches takes about 15 minutes to complete.

Is this procedure fine or should I consider changes?
Is clearing out the data cache really necessary or is it still a fair comparison to execute the query with the data cache since there is no other users active that could make changes to that cache?


texts: Getting last modification date of a schema

texts:
Is there a way to discover the date a schema was last modified? I'm trying to decide if a schema's still being updated and is therefore "active" in my system.

I use PostgreSQL 9.2 under Linux CentOS release 6.4


texts: Translate query using APPLY to run under compatibility level 80

texts:
I want to use the following query:

SELECT
    SUBSTRING(qt.TEXT, ( qs.statement_start_offset / 2 ) + 1,
     ( ( CASE qs.statement_end_offset
           WHEN -1 THEN DATALENGTH(qt.TEXT)
           ELSE qs.statement_end_offset
         END - qs.statement_start_offset ) / 2 ) + 1) AS [Text] ,
 qs.execution_count ,
 qs.total_logical_reads ,
 qs.last_logical_reads ,
 qs.total_logical_writes ,
 qs.last_logical_writes ,
 qs.total_worker_time ,
 qs.last_worker_time ,
 qs.total_elapsed_time / 1000000 total_elapsed_time_in_S ,
 qs.last_elapsed_time / 1000000 last_elapsed_time_in_S ,
 qs.last_execution_time ,
 qp.query_plan
FROM sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC

I took the code snippet from How to find CPU intensive queries? on the SQL Server Central blogs.

I need to run this query on database which is in compatibility mode 80 and APPLY is not available there.


texts: How to refresh AD security group on Sql Server permissions

texts:
I am using Sql Server 2008 on Small Business Server 2003; client is using WinXP.

I added a user to my Active Directory Security Group; Why can't this user immediately access the database? It seems there is a delay before the user is recognized in Sql Server.

I am using AD Security Groups for permissions expressly so that I don't need to add individual users in Sql Server. So effectively I don't need to do anything but add the user to the AD Security Group in order to grant access.

But for some reason Sql Server doesn't immediately recognize the addition. I've seen this a number of times. I add the user to the group but that user can't access data until the next day. It seems that it doesn't query the Active Directory in real time. Can you confirm that is so?

What do I need to do so that Sql Server "refreshes" the list of users from Active Directory?


texts: why is mysql still writing data two hours after all transactions have stopped

texts:
I have a mysql server used mostly as data warehouse. It loads about 4-5GB of data per day (actual database size, not the size of the source data).

Lately, I noticed something peculiar. After I stopped the loading process, and nothing else modifies the database, mysql keeps writing data at a rate of 5MB/s for over two hours.

This particular install uses 20GB of innodb_buffer_pool, so even if it was flushing all pages in memory, it would take about 70 minutes to do that.

Additional background information

This server is being mirrored, so it keeps the transaction log.

innodb_buffer_pool_pages_dirty keeps yoyo-ing between 60 and 10.

innodb_rows_(deleted|inserted|updated) is stable, so I'm pretty sure no modification is happening.

I have confirmed that it is indeed mysql doing the writting using /proc/sys/vm/block_dump (see http://www.xaprb.com/blog/2009/08/23/how-to-find-per-process-io-statistics-on-linux/)

This last run, the server has only been up for two days, so it's highly unlikely that it has that many reads backlogged.

This is mysql 5.0 (yes it's old)


texts: How does MS SQL Server decide when it can use "Parallelism" when executing queries? I recently posted another question here that, when looking at the actual execution plan used parallelism when I had an order by on the query and did not when I didn't have the order by. So, I'm wondering how it figures out when it can use "Parallelism" and when it can't.


texts: Why can't I access my mariadb after installing homebrew?

texts:
I just installed homebrew and mariadb via brew.
For some reason, get an error when trying to access the database.
If leave the password as empty, which is normally the default, it gives an error, and if I try to use the rot password it also gives an error.

Here the result I am getting:

➜  ~ brew services list
Name    Status  User  Plist
mariadb started refix /Users/refix/Library/LaunchAgents/homebrew.mxcl.mariadb.plist
php     started refix /Users/refix/Library/LaunchAgents/homebrew.mxcl.php.plist
➜  ~
➜  ~
➜  ~
➜  ~ sudo mysql -uroot
Password:
Sorry, try again.
Password:
sudo: 1 incorrect password attempt
➜  ~ sudo mysql -uroot
Password:
Sorry, try again.
Password:
sudo: 1 incorrect password attempt
➜  ~

How do I access my db?


texts: SQL 2008: Why is *any* user, with minimal permissions able to restart/stop the SQL instance through Management Studio

texts:
So I'm on a project that I need to help admin SQL 2008 .
I'm coming from a MySQL background.

Why is it that a basic user, with the most basic/default permissions, through Management Studio is able to restart or stop the entire instance of SQL but not view a simple table?


texts: Ola's maintenance solution is not processing sysjob* tables

texts:
I have used Ola Hallengren's script to back up and maintain databases.

However, it does not perform any reindex or reorganize operations against certain tables in msdb, such as sysjobhistory, even though they have fragmentation above the defined thresholds. Here is the script I am using:

EXECUTE dbo.IndexOptimize
  @Databases = 'SYSTEM_DATABASES',
  @FragmentationLow = NULL,
  @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
  @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
  @FragmentationLevel1 = 5,
  @FragmentationLevel2 = 30,
  @UpdateStatistics = 'ALL',
  @OnlyModifiedStatistics = 'Y'


texts: Creating Common Fields across Databases for better SQL JOINs

texts:
In my company, our quality control organization has a two different SQL Server 2012 databases that capture manufacturing defects and component failures.  Each database has a table and a column for "Defect."

I am trying to commonize the input fields for these defects so that I can better correlate (and count) the number of defects and component failures, for example,  in Tableau.

One colleague suggested using a translation table.  I'm not really sure what that is.  I've used R to combine the two columns and I am manually removing duplicates.  The issue is that in one database, the words are abbreviated (i.e, missing vs. miss.) and the only way I know to solve this is by manually removing these types of duplicates.

Does anyone have any other suggestions?


texts: Question marks instead Hebrew characters after mysql dump and import

texts:
I tried to migrate my AWS RDS instance from Aurora to MySQL. I created a dump file from the Aurora and imported it into the MySQL instance.

Both instances have the same characters set.

mysql> show variables like '%char%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8mb4                                   |
| character_set_connection | utf8mb4                                   |
| character_set_database   | latin1                                    |
| character_set_filesystem | binary                                    |
| character_set_results    | utf8mb4                                   |
| character_set_server     | latin1                                    |
| character_set_system     | utf8                                      |
| character_sets_dir       | /rdsdbbin/mysql-5.7.26.R1/share/charsets/ |
+--------------------------+-------------------------------------------+

In the original DB the Hebrew letters appears as black diamonds (Converted to Hebrew in app side). After the migration to the new MySQL instance, instead of black diamonds i see questions marks.

What could be the issue?


texts: Export sql azure DB as bacpac file without including data for certain tables

texts:
When I export a sql azure database as data tier application from sql server management studio I get the option to tick off certain tables but then these tables are not included in the resulting file.

I want to export these tables schemas, but ignore their data (import empty tables essentially). For all the other tables I also want the data.

Is it possible to do that?


texts:
If I up to date my postgres from 9.6 to 12, can I get some issues with my queries?
I'm afraid to have some problems with my actual code if I upgrade my version.


texts:
I want to insert somewhere around 400 rows into a table. All of the column values will be the same fixed value for most of the columns, with exception to one column which I want to pull from a fairly complicated subquery that will pull over 400 unique values from another table using a series of joins and where statements. We attempted the following query:

INSERT INTO mytable (col1, col2, col3, col4)
VALUES(val1, (subquery), val2, val3)

It stated, that it wouldn't work because the subquery yielded more than one result. The only values being pulled from a different table is in the subquery. I cannot insert the subquery into the respective column by itself, since col1 is a NOT NULL column.


texts: Is there a SQL Server equivalent to "OVERRIDING USER VALUE"

texts:
While googling I found about the OVERRIDING USER VALUE parameter for an INSERT for IBM's iSeries.

Is there an equivalent command for Microsoft SQL Server versions 2005 or newer to allow you to insert into a table that has an IDENTITY column and use the auto-assigned value instead of the user value passed in?

In this instance, the main goal is to help convince a superior from moving us off of using a GUID as the clustered primary key (the schema was developed pre-SQL 2000 where that was a decent idea, vs. now where it is a horrible idea) to adding an identity column to the tables and moving the clustered primary key to that and converting the old index to a non-clustered unique index.

The biggest push back is:

  There are a lot of places in the legacy code that use things like Insert into XXXX Select * from YYYY where ... where the number of columns is different based on run-time adjustable settings on the client's front end. Using a identity column would break those queries whereas GUIDs are unique and won't have the issue and it is too much work to rewrite all of the queries to use named columns due to the fact that the column names are variable per installed client.

So that is why SET IDENTITY_INSERT ON is not a option, as this will be a primary key column. I was hoping to make the argument that if we could use the SQL Server equivalent of OVERRIDING USER VALUE I could use an argument like "We can just add this small bit to the insert statements and we don't need to include the column names."


texts: Could an update on all statistics separately cause bad performance?

texts:
We’ve been utilizing Ola Hallengren's maintenance scripts for our index optimization for years on our OLTP databases.  (we are on sql server 2008 R2)
2 weeks ago though one of the DBA’s here has created a maintenance job to update statistics separately and this runs weekly before the Index optimization job.
It seems that after this was created we are getting performance issues on a handful of stored procedures.  I am not too familiar with stat maintenance, so I am wondering, is it really necessary to run a weekly job separately to update all statistics?
The DBA here seemed to think this was needed but I feel that we started having badly performing stored procedures the morning after the maintenance jobs are running and I constantly needed to recompile the same stored procedures at least 1 or 2 times per day for the following few days then the issue just stops.
My theory is pointing to the statistics updates that occurred before but I am having trouble finding the reason why.  Don't statistics get updated after a certain amount of changes or during an index rebuild anyway?  Why would recalculating them separately cause an issue?

Would anyone be able to offer some clarity as to why this would happen to the same stored procedures every time statistics get updated?

This is the command being used for the statistics job:

EXECUTE dbo.IndexOptimize
    @Databases = 'DB_PRO',
    @FragmentationLow = NULL,
    @FragmentationMedium = NULL,
    @FragmentationHigh = NULL,
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y' ,
    @TimeLimit = 3600,
    @LogToTable = N'Y';

Then the Index Maintenance job runs with the following :

    sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize]
    @Databases = 'DB_PRO', @TimeLimit = 10800, @LogToTable = 'Y'" -b


texts: Exception while inserting records in SQL Server (Error message id : 586)

texts:
I am getting an exception when doing a batch insert operation using a prepared statement from Java.

  java.sql.BatchUpdateException: The prepared statement handle 5 is not
  valid in this context.  Please verify that current database, user
  default schema, and ANSI_NULLS and QUOTED_IDENTIFIER set options are
  not changed since the handle is prepared.

While analyzing this error message all I could find out was its error message id : 586. I could not gather any more information about this issue.

Can anyone help me with the reason for this exception, what it means and how to resolve it ?

Machine Details :

SQL Server Version - SQL Server 2014

jTDS JAR Version - 1.2.4

PS : This exception only occurs on a particular remote machine. We cant reproduce this issue locally. Any tips on how to reproduce this issue would also be helpful.


texts: Maintenance Tasks causes CPU to peg 100% utilization

texts:
I do not believe the maintenance tasks themselves to be the primary issue here, however CheckDB and index rebuilds cause the server to become unresponsive w/ 100% cpu utilization.

It is on one specific database that this occurs. I can run the maintenance tasks on all databases but one with no issue.

I can also restore that database to another SQL Instance (dev or test) and run the operations there as well with no issue. It is only with respect to this one database call it db01 on the production instance. I ran the consistency check for corruption on both the old (2008 + server 2008), as well as dev(2016 + server 2016) with no reports of corruption.

Does this prove out enough that the issue is with the production box/hardware versus my SQL Server configuration?


texts: Converting spatial data from Oracle to MySQL

texts:
This is a followup to this question: Problem getting zips in raius via MySQL

I have some spatial stuff in an Oracle table.

MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(-71.88455,42.27993,-71.88467,42.27977,-71.88271,42.27823,-71.88269,42.2780.........

How can I convert this geometry to a MySQL geometry type? I can write a script to do it if someone can give me the basics.


texts: How do I build index for MySQL table that inserts a lot and queries only most recent data

texts:
I have a MySQL database table using InnoDB and looks like this:

OrgID (INTEGER)
MachID (INTEGER)
Date (DATETIME)
IdleTime (INTEGER)

I do two operations on this table:

Insert a new record, with Org ID, Mach ID, Date and IdleTime
Query for either a given Organization/Machine ID pair, for last day, the sum of IdleTime:

SELECT SUM(IdleTime) FROM DBTable WHERE OrgID=1 AND MachID=2 AND Date BETWEEN 'YYYY-mm-dd 00:00:00' AND 'YYYY-mm-dd 23:59:59';

We may want to get the sum of total IdleTime for given OrgID, but it is just an extra bonus. In most cases, YYYY-mm-dd is yesterday.

We concern more on the performance ofINSERT operation but we do not want too slow SELECT. My boss recommended adding a new column as the primary key, namely something like INSERT_TIME(TIMESTAMP) , to ensure insertion is sequential. I am wondering if using a PRIMARY KEY (OrgID, MachID, Date) may help. Any suggestions on how do I use the index to boost the performance?


texts: Is there a way to access temporary tables of other sessions in postgres?

texts: I'm working with a Windows application that uses a (local) postgres Database and stores some information in a temporary table. I'd like to have a look at the temporary table, but pgadmin and dbVis tell me: ERROR: cannot access temporary tables of other sessions when trying to query the data. I tried changing the permissions of the schema and table, but this didn't seem to help, even though I'm accessing the database with the same user as the program itself (at least in dbVis). Is there a setting I can change in my database that allows me to have "root" access to all session in my database?


texts: How can I fill in a foreign key value after creating the first entry of the table the foreign key is referencing?

texts:
I am seeking to create an online store so I got to the database definition. I want to have as few tables as possible, so I've got them narrowed down to:

Users
Products
Orders
Cart_Items

I got to the following schema: image

The logic flows like this: For each product added in the cart, a new Cart_Item entry is created. But the Cart_Item entry needs to fill in the foreign key that references the Order table, which is cartItemOrderID. Since there's no order created, it'll have to be null at first. Once the user adds all their desired items in the cart, they place the order, and a new Order is created, so now I have the orderID. How would I pass this orderID to the entries in the Cart_Item table, since there's no way to tell they were placed by the current user?

I'm missing something huge but I can't put my finger on it.


texts:
I created a table called customer, which consists of one attribute called email.

Then I would like to add a constraint to email attribute.
Following is my coding:

Create table customer
( email varchar(100));

Here is my constraint:
"@" must be included in the email

However, I don't know how to add this constraint.
I am total new to psql. Thank you very much!


texts: Restart MySQL server while query is in Killed State

texts:
I need to reboot the server (not just MySQL instance) to perform some urgent server updates.
Currently on the MySQL server when I run:
select * from information_schema.processlist where info is not null and time > 0 order by time desc;
I get following output:
100654837   stats   127.0.0.1:40324 stats   Killed  6094224 closing tables  insert into `reports` (`request_id`, `report_status`) values (?, ?)
135085263   stats   127.0.0.1:51188 stats   Killed  2259745 query end   select * from `domain` where `domain`.`id` = ? limit 1

1st process: closing tables / 6094224 seconds (A LOT)
2nd process: query end / 2259745 seconds (A LOT)
What could possibly go wrong if I gracefully stop the MySQL instance first, do the necessary upgrades, reboot the whole server and start (autostart) the MySQL instance?
Also a side question, how long it might take to perform the MySQL graceful stop?
Few hours (4-5h) is in acceptable range.
And another side note: those 2 tables are not important, I can restore them from backups or even start anew with empty tables.
Thanks


texts: Is there a Windows Performance Counter which contains the "Log Send Rate" for Availability Groups?

texts:
I wish to be able to get the "Log Send Rate kb/s", as per the SSMS AG dashboard (which is also available via log_send_rate column on the DMV sys.dm_hadr_database_replica_states)

I'd like to get it as a Windows Performance Counter... looking at the available counters for the Database Replica category, the nearest is "Log Bytes Received/sec", which doesn't appear to report the same value.

Does anyone know if there is a performance counter or will I need to rely on the DMV?


texts:
I have a table similar to the following (Updated: added a type column** Simplified the real query too much I guess)

CREATE TABLE versions(
   type INT NOT NULL,
   version INT NOT NULL,
   important BOOLEAN NOT NULL,
   PRIMARY KEY (type, version)
)

Running SELECT type, MAX(version) FROM versions GROUP BY type is very efficient and retrieves a single row per type from the database.

However, running SELECT MAX(CASE WHEN important=1 THEN version END) FROM versions GROUP BY type seems to generate a full table scan.

Since I have an index on version I expected MySQL to start scanning versions from the highest and stop when it finds the first that has "important=1". Usually, with my data this requires scanning just a few rows.

Looking at performance_schema.events_statements_history (ROWS_EXAMINED column) it seems like MySQL scans the entire table in order to retrieve the row I need.

Any ideas on how to improve the performance of this query will be much appreciated.


texts: Replication - Binary log parsing(Tableid generation, ON DELETE CASCADE handling, Changed columns for update event)

texts:
I am using MySQL 5.5 with InnoDB engine. I have set binlog format as row-based replication.

I have some doubts regarding replication:

1) How is the tableID generated? Is it stored somewhere?

2) Say I have Table T1(parent), T2(child), T3(child). T1 has a FK(ON DELETE CASCADE) relationship with its children: T2 & T3. If a row is deleted from T1, then will a DELETE event be generated for all T1, T2 & T3?

3) Is there any way to get info about the changed columns in case of an UPDATE event?


texts: MS SQL rolling grouped sum for every new data entry

texts:
I am using MS SQL 2016, and I have a table which looks something like that (simplified)

 Object      Date         Amount  Owner  TypeOwner
  O1        20180101        10      A     X
  O2        20180101        50      B     Y
  O1        20180101        30      C     X
  O1        20180203        25      B     Y
  O2        20180203        20      B     Y
  O1        20180206        15      A     X
  O2        20180206        20      A     X

Simply put, owners declare how much of an object they have, at a date they choose. What I am looking for is reconstruct the history of ownership of the objects with respect to a column like TypeOwner, i.e. each time a new data point is available for a owner, recompute the current ownership. So the output table should look something like

Object      Date         Total  TypeOwner
  O1        20180101        40      X
  O2        20180101        50      Y

  O1        20180203        25      Y
  O2        20180203        20      Y
  O1        20180103        40      X

  O1        20180206        45      X
  O2        20180206        20      X
  O1        20180206        25      Y
  O2        20180206        20      Y

Basically each time there is a new entry in the table, it should look at all the most recent entries for any other owners and recompute a grouped sum per Object, TypeOwner.

How I obtain the result table:

As of 2018-01-01, we know that A owns 10 O1, B owns 50 O2 and C owns 30 O1. So group X (A and C) owns 40 O1 and group Y (B) owns 50 O2. That's the first two lines of the results.
On 2018-02-03, we have new data for B. This overrides the past entry for that owner. So as of this date A still owns 10 O1, C still owns 30 O1 and now B owns 25 O1 and 20 O2. So group X (A and C) owns 40 O1, group Y (B) owns 25 O1 and 20 O2.
On 2018-02-06, we have new data for A. This overrides the past entry for that owner. As of this date, A now owns 15 O1 and 20 O2, C still owns 30 O1 (2018-01-01), and B still owns 25 O1 and 20 O2 (2018-02-03). Once again group X (A and C) owns 45 O1 and 20 O2, group Y (B) owns 25 O1 and 20 O2

I tried using various techniques like inner join/cross apply to select last records up to some date, but I am not sure what would be te most efficient solution here. The real table is on thousands of objects and owners, hence any speed gain would be great.

Thank you


texts: Adding Foreign Keys to Composite Primary Key, referencing 2 tables

texts:
I have a composite primary key
PRIMARY KEY (alpha, beta)
I want to add foreign keys to primary keys alpha and beta
I tried:
-- ADD FOREIGN KEY (alpha) REFERENCES green(alpha);
-- ADD FOREIGN KEY (beta) REFERENCES blue(beta);

This didn't work. I then tried:
ADD FOREIGN KEY (alpha, beta) REFERENCES green(alpha), blue(beta);

And this didn't work either. I can't find any examples online of adding different foreign keys to the composite primary key. Any tips would be welcome.


texts:
I have a mysql database table with 20 million records.  It grows at roughly 25,000 records per day.  Each record has a number of numeric fields.  I have a similarity query that selects the 10 most similar records to a specified record as follows:

SELECT 1000 +
    IF(p2.ph != p.ph,-50,0) +
    ABS(p2.ss - p.ss)/6.1 * -50 +
    ABS(p2.fxx - p.fxx)/6.4 * -50 +
    ABS(p2.fxz - p.fxz)/5.35 * -50 +
    ABS(p2.px - p.px)/.94 * -50 +
    ABS(p2.pz - p.pz)/.92 * -50 +
    ABS(ABS(p2.x0) - ABS(p.x0))/.71 * -50 +
    ABS(p2.z0 - p.z0)/.46 * -50 +
    ABS(p2.sr-p.sr)/669.5 * -50 +
    ABS(p2.sd-p.sd)/68.2 * -50
    AS sim_score
FROM tableP p, tableP p2
WHERE p.pid = 123
    AND p.uid != p2.uid
    -- AND p2.ss>0
    -- AND p2.ph = p.ph
    -- AND ABS(p2.ss - p.ss) < 3.5
    -- AND ABS(p2.fxx - p.fxx) < 3.5
    -- AND ABS(p2.fxz - p.fxz)<3
    -- AND ABS(p2.px - p.px)<.6
    -- AND ABS(p2.pz - p.pz)<.6
    -- AND ABS(p2.z0 - p.z0)<.35
    -- AND ABS(ABS(p2.x0) - ABS(p.x0))<.5
    -- AND ABS(p2.sr-p.sr)< 350
ORDER BY
    1000 +
    ABS(p2.ss - p.ss)/6.1 * -50 +
    ABS(p2.fxx - p.fxx)/6.4 * -50 +
    ABS(p2.fxz - p.fxz)/5.35 * -50 +
    ABS(p2.px - p.px)/.94 * -50 +
    ABS(p2.pz - p.pz)/.92 * -50 +
    ABS(ABS(p2.x0) - ABS(p.x0))/.71 * -50 +
    ABS(p2.z0 - p.z0)/.46 * -50 +
    ABS(p2.sr-p.sr)/669.5 * -50 +
    ABS(p2.sd-p.sd)/68.2 * -50 DESC
LIMIT 0,10

The commented lines are a way to limit the comparison to more similar records.  I don't really like that idea, but it does speed this up a bit and serves the purpose of finding the 10 most similar records.

This query takes 5-25 seconds after caching, and will generally take 45+ seconds on first load.  This is obviously inadequate for a web page.  Is the structure of this query sound or is there a better way to do this?

Ideally, I'd like to have another table that stores the similarity scores each day.  Essentially, it would have two pid keys and a score.  That will speed up the prior query, however creating this is would be a mammoth nightly task as I'd be creating roughly 25,000 x 20,000,000 records nightly.

My second question is how can I calculate those bulk similarity score records efficiently?


texts:
Having asked This question on Stackoverflow, I wondered where what I have done is correct/best practise.

Basically, every object that I create is going into a schema with the schema name reflecting a usage. For example, I have the schemas Audit and Admin (amongst others).

This in turn leaves no objects in dbo. Is this ok? Is there anything else that I need to do?


texts: Do SQL OS Background Tasks Yield to Normal Tasks?

texts:
I have a SQL Server 2019 server that is running at about 22% CPU.  I look at what's running and I see that about half of that 22% is coming from background tasks (i.e. the status is "background") such as QUERY STORE APRC, GHOST CLEANUP, and TASK MANAGER.
So, if the server were to become busy running queries for the application, would those background tasks receive a lower priority or a smaller quantum or something that would see them give back the majority of the CPU they are using?
Basically, I keep getting asked what's using the CPU and I answer that about 50% of the CPU usage comes from background tasks.  Then I start getting asked how to turn them off.  If I can say that the background tasks are safe to ignore, I can make that stop.  Unfortunately, I have not been able to find anything definitive to support that assertion.


texts: Informix - dbaccess vs dbexport to export tables to text files

texts:
Currently, we are using the dbaccess utility to export all tables in the Informix database to text files.  These are used to "mirror" the database multiple Windows machines running MSSQL Server on a nightly basis.

I was looking into the dbexport utility to see if it would be a viable solution.  The text export takes almost 2 hours and I'm looking to shrink that time down if possible.  However, I'm not familiar with these Informix utilities and we do not have a test environment for this... so I'm wondering what exactly is the output from dbexport?  Is there an option to export each individual table to a text file with dbexport?  Thank you.


texts: Permission issue on restore of the db from primary to secondary

texts:
while restoring the User DB from primary to secondary replica through query in cmd prompt and got following error message

Msg 262, Level 14, State 1, CREATE DATABASE permission denied in database 'master'.
RESTORE LOG is terminating abnormally.Msg 3013, Level 16, State 1, RESTORE LOG is terminating abnormally.

provided sa permission on the user, but still it did not work. where should i need to look.


texts:
So I don't have experience with Mongodb besides a few tutorials. Currently a website is set up using Mongodb for user access and for some other data. Because of this, I'd like to stick with Mongodb even though I think this may be better in a relational database.

This is what I have in mind using references...

{name: Name,
 activity: [00001, 00002]}

{id: 00001
 type: run
 daily: {7/16/2018: {accuracy: 0.5, test: true},
         7/17/2018: {accuracy: .75, test: true}}

{id: 00002
 type: walk
 daily: {7/16/2018: {accuracy: 0.25, test: false},
         7/17/2018: {accuracy: .6, true}}

Are the two ok for schemas?

There shouldn't be a need to save all daily data but likely at least 1 year's worth.  Would this be problematic to update especially as the daily structure grows?


texts: Startup NOMOUNT and Startup MOUNT on dataguard oracle

texts:
I have a question here about Oracle data guard.

After performing a switchover, we shutdown the new standby database and startup it.
At this stage, sometimes I find DBA performance in 2 different steps:

startup mount;
startup nomount; then
alter database mount standby database;

What's the difference between above commands? Could somebody tell me?

Thanks for helping me and forgive me about my English writing.


texts: Create an alias for a SQL Server named instance pointing to sql default instance

texts:
I have an installation of SQL Server 2012 Std Ed on Windows 2012 R2 running in a default instance, and an app that wants to connect to it using a named instance (can't be changed)

E.g.

ServerName - mySQLServer
Alias required by app - mySQLServer\CrazyApp

I've tried to create an alias as follows:

Alias Name - .\crazyapp
Port No - 1433
Protocol - TCP/IP
Server .

I've:

checked TCP/IP protocol is enabled
have started SQL browser (not needed, I believe)
restarted SQL (several times)
tried with the machine name instead of "."
tried with no port number instead of 1433.
banged head against wall

...all to no avail.

Is it possible to do this, if so, would some kind person tell me what I'm doing wrong.

Thanks!


texts: Increasing max server memory to deal with System.OutOfMemoryException on SQL Server

texts:
I am not very experienced so please bear with me.

I have been attempting to import a 2500 MB and 3800 MB CSV into SQL Server 2016. Unfortunately I keep getting the System.OutOfMemoryException error. My computer has 8.00 GB RAM so I figured I would just increase the max server memory from the default up to 4000 MB. For some reason though each time I try to change the max server memory to make it higher it changes back to default the moment I press OK. How do I fix this problem?

I am using BULK INSERT to import the data. The BATCHSIZE, KILOBYTES_PER_BATCH, and ROW_PER_BATCH arguments are not set.

UPDATE

I have decided to split up my CSV document so that I can import less of it at a time. Unfortunately by the time I try to import the last piece of my CSV the error shows up again.

UPDATE

I have managed to substantially increase the max server memory to 4 GB. However this has not fixed the problem.


texts: What ongoing maintenance is needed for a MySQL environment?

texts:
I'm an MSSQL DBA, and I'm inheriting a 3-server MySQL 5.6 environment feeding a (currently) low traffic website.  I'm having a hard time finding what kind of regular / preventative maintenance a MySQL env needs.  I've done a fair amount of self-studying, but the guides etc I've run across mainly talk about installation and backups.  My environment is installed, and we have backups going.  Now what?

So what sort of care and feeding does a MySQL environment need?  Corruption checks?  Index rebuilds?  Aromatherapy?


texts:
how much to keep table_open cache and what settings is available in mysql to release unwanted memory cache and table cache?
MY server has 672GB RAM windows environment server 2012
Innodb_buffer_pool_size = 450GB
Can you please suggest me what are the things need to check ?


texts: In a mirror, how can I keep the SQL Server Agent offline, but have it start automatically in the event of a failover on the Principal?

texts:
In this scenario there are three MS SQL servers. ServerA has a series of jobs that write data to databases on ServerB. I want to set up ServerC as a mirror of ServerA, with all the same jobs available. I want to keep the SQL Server Agent down on ServerC until such a time as ServerA fails over, at which time I would like the SQL Server Agent on ServerC to automatically start up and continue to service those same jobs writing to ServerB until we get ServerA back online.
Can this be accomplished? And what is the best way to go about it?


texts: Insert output into another newly created table

texts:
How can I insert the output of below table into newly created [DBname].[dbo].[Tablename]?
The below T-SQL query outputs two columns: server and name.
T-SQL 1
if object_id ('tempdb..#temp1') is not null
drop table tempdb..#temp1

create table #temp1
([server] nvarchar(200),
[name] nvarchar(200),
)

insert into  #temp1

SELECT  (select @@SERVERNAME) as Server,name FROM      master.sys.databases
select * from #temp1

I want to insert the output of T-SQL 1 into a newly created table [DBname].[dbo].[Tablename].I already created two columns server and name.
So far I tried like below:
Insert into [DBname].[dbo].[Tablename]

select

if object_id ('tempdb..#temp1') is not null
drop table tempdb..#temp1

create table #temp1
([server] nvarchar(200),
[name] nvarchar(200),
)

insert into  #temp1

SELECT  (select @@SERVERNAME) as Server,name FROM      master.sys.databases
select * from #temp1

I used the Stack Overflow Q & A referenced below, but somehow could not do what I need:
How to do INSERT into a table records extracted from another table


texts: monitoring sql execution perfomance of an application that makes use of SP_prepare

texts:
we have an application that makes heavy use of sp_prepare and sp_execute and we would like to start monitoring the execution plans of the sql. but this is made difficult by the use of sp_prepare

much of the reading i have done suggests that sp_prepare is inefficient, is this correct?
also how would i monitor such database activity?

we are looking to monitor

potential missing indexes
Longest Running Queries

if you need any more information please let me know.


texts: Track database name in anemometer queries

texts:
Hello i have installed anemometer on our server in order to see the queries that need optimization.

From the website hosted a few have a wordpress blog on them so it is hard to guess witch one is affecting the most in the query bellow:

select  wp_posts.*
    from  wp_posts
    inner join  wp_postmeta on ( wp_posts.id = wp_postmeta.post_id )
    where  ?=?
      and  ( ( wp_postmeta.meta_key = ?
               and  cast(wp_postmeta.meta_value as char) = ? )
           )
      and  wp_posts.post_type = ?
      and  ((wp_posts.post_status = ?))
    group by  wp_posts.id
    order by  wp_posts.post_date desc

Is there a way to track the database as well in anemometer?


texts: Query extremely slow when joining 2 views

texts:
I will preface this by saying i am not a DBA, just a dev that is trying to figure out performance problems with the following query we have.

 SELECT
        OCCoverID as StaffID, FullName, SUM(OCWeight) AS Num
    FROM vuOCStaffAbsentExpandedMini
        INNER JOIN vuStaff ON OCCoverID = StaffID
    WHERE
        SADate = '2019-10-4'
        AND MIDent = 506
        AND SASupplyID IS NULL
        AND CoverStaffSupplyID IS NULL
        AND StaffMIDent <> 1
    GROUP BY
        OCCoverID, FullName

It takes about 1 minute to execute every time, we have double checked (we think) all required indexes, ran it through index tuner and still same issues.
Below is the actual plan that it creates.

Any help would be appreciated, we are using sql server 2012 express

https://www.brentozar.com/pastetheplan/?id=HJgOtXHur


texts:
This is slightly complex to explain, but very easy to reproduce.

Short version:

For about 10%-30% queries (by count) significant amounts of Query Store data is not available. I have looked at SQL 2016 and SQL 2017 where Query store had been running for weeks+, where there was activity on the database(s).

The query below will return two sets of data, the top set does not have a value for query_store_query.last_execution_time, while the bottom set does have data in the field.  The 'have nots' are also missing most the run time stats data.

EXEC sp_query_store_flush_db;  --Same results without this, but just to rule it out in the examples.
go
Select * from sys.query_store_query
where query_store_query.last_execution_time is null -- there are bunch of these, also missing other data, why?

Select * from sys.query_store_query
where query_store_query.last_execution_time is not null

Problem Solving:

I Initially discovered this using exported Query Store data My data is in an Excel sheet, I sorted and compared and did not find any common factors separating the 'haves' from the 'have nots'
To rule out data export issues, I used the code above to get data directly from the system dataview.  Similar results to findings in exported data.
To rule out the system dataview, I used "Tracked Queries" to report from the root data. (Query Store > Tracked queries > Configure)

3.A. For the 'haves', the query plan(s) and such are displayed, just as you would expect

3.B. For the 'have nots', there is no query plan and no metrics
The only scope limiting factor I have found is that on very active databases, the 'have nots' are limited to the last few hours.  But on slow databases, the 'have nots' can have initial_compile_start_time dating back several weeks.  (I suspect the 'have nots' are being purged then recreated the next time the now "Never seen before, new" query is run)
The 'have nots' can have any range of compile counts, number of plans, etc.

Question

What do the 'have nots' have in common? Why are they missing data when others are not?

Why this matters: If as I currently suspect, the run time data is not being captured correctly on the 'have nots' some/all of the most resource intensive queries may not be reported in query store reports.

Update: Query Store Capture Mode All does not eliminate this issue.

Using the suggested setting by Erin Stellato I have Query Store Capture Mode set to Auto, as pointed out in a comment and answer this could be the cause. But after testing (purged the QS on one of my database and set it to ALL), I am still seeing some 'have nots' after the database has run for a while


texts: In replication A->B->C, can A be binlog_format=STATEMENT and B be binlog_format=ROW?

texts:
I have replication setup between an on-prem MySQL 5.6 server (A) to another on-prem MySQL 5.6 server (B).  I was setting up an AWS DMS instance (C) that I wanted to migrate and then replicate.  It told me that I had, then, to have my binlog_format set to ROW-based replication.

Is this feasible?  Can I expect changes on A to replicate correctly to C?


texts:
Error messages:
  The schema script 'teste2_2.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)

  The process could not read file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VUZIQ_ENTP\MSSQL\ReplData\unc\ADSERVER$VUZIQ_ENTP_TESTE2_TESTE2\20130626155609\teste2_2.sch' due to OS error 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL0)
  Get help:

I have one sql from ip 199.168.***.*** working as Publication and another server 64.37.**.**. working as Subscription. The local Subscriptions of 199.168.***.*** is working fine but the external access from 64.37.**.** cant load file.

How can I add a login for 64.37.**.** server to have read/write permission to 199.168.***.*** server ?


texts:
I would like to duplicate to the same server Oracle Database with RMAN. I've configured flash recovery area for Oracle Database. My database is using SPFILE as shown below.

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile

SPFILE

My questions are:

if my source database uses SPFILE then do I have create a PFILE from the SPFILE? If so, how can I do that?
What do they mean by this?

  You only need to create directories that are referenced in the PFILE or SPFILE.

Which directories need to be created?

Production Database : /u01/app/oracle/oradata/DB11G/. My SPFILE (spfileDB11g.ora):

DB11G.__java_pool_size=4194304
DB11G.__large_pool_size=4194304
DB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DB11G.__pga_aggregate_target=155189248
DB11G.__sga_target=264241152
DB11G.__shared_io_pool_size=0
DB11G.__shared_pool_size=171966464
DB11G.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/DB11G/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/DB11G/control01.ctl','/u01/app/oracle/oradata/DB11G/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain='localdomain'
*.db_flashback_retention_target=720
*.db_name='DB11G'
*.db_recovery_file_dest='/u01/app/oracle/fra'
*.db_recovery_file_dest_size=3221225472
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)'
*.log_archive_dest_1='location=/u01/app/oracle/archive_logs'
*.log_archive_format='db11g%r_%t_%s.arc'
*.memory_target=417333248
*.open_cursors=3^AC^@^@C"^@^@^D^@^@^@^@^@^@^@^@^@^A^DU^F^@^@00
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

if backup files (autobackup and backup set) are in a different path on the source host (not default FRA Path) then how can I use the duplicate command? BTW, only the flashback logs are in the default location (/u01/app/oracle/fra/flashback). Path: /u01/app/oracle/oradata/DB11G/autobackup and /u01/app/oracle/oradata/DB11G/backupset.


texts: Dynamically determine the starting time and ending time for a. . . Breakfast of Champions!

texts:
Dynamically determine the starting time and ending time for a between statement based on the results of a previous query's table
  records when ordered appropriately for TOP and BOTTOM records datetime values!! Breakfast of Champions!!

Too BIG for SQLFiddler text file is right here and it'll create the data in the MYSQL tables as well

A zipped text file with the T-SQL in it to create tables and insert values.

Information

I have table Weight and another table SetBit

The Weight table can have records inserted more than once a second and this is a sample of real data but the recording mechanism only records to the second time wise and not to a millisecond. The machine can generate the records more than once per second and they are imported by a routine insert operation.The recording mechanism recording this data only records when the process is running so if nothing is running; we get no data.
The SetBit table has its values recorded once a minute only and there are never any duplicates in these records based on the Time and TimeUTC column never being the same / unique keys. This process will always run recording the values per run so these are updated to the DB every minute or so via an import process.

The task and the problem:

Somehow I need to tell it to show Time and Weight records from the Weight table between a certain date like with a between statement which is not a problem at all from the dates of the same table. The trouble I'm having is when trying to include or show the value of the Bit record from the SetBit table when the Time of the records from the Weight table occurs when there not a matching Time record value in the SetBit table to join, or do a between topmost Time on Weight ordered by, etc. and have it dynamically figure it out.

The bit value doesn't change often but I'd like to be able to show:

SELECT Weight.Time, Weight.weight, SetBit.Bit

Every JOIN statement I've run so far has not given me the desired output so I'm not sure how to match up or JOIN the Time values between the table if that is even possible for it to check the SetBit table time value whent he time value of the record in the Weight table is run and show me what the bit value is whether 0, 1, or 3.

I need help or told this is not even possible if you know for sure not though.

NOTE: please see the link with the SQL to create test data tables and insert a small sample of the data I'm working with.


texts:
I am trying to make a stock management software according to my client's requirement, where I bought products by weight or qty depending on product type. Which has following database structure.
User { id, name, address, role (Customer, Vendor, Labor etc)}
Product {id, name, type}

Types of product:

Sheet
Circle (After cutting)
Rolled Circle (After rolling)
Dark Circle (After Buffing)
Ready Circle (After Polish)

The issue is the product I purchase may go through 4 process:

Cutting
Rolling
Moulding
Polish

The above process depends on what type of product I purchase. If I purchase raw product then I may give it to labour who will cut it and give it back to me (i.e. cutting). In this process some material goes to scrap.
As you can see if I buy sheet which is purchased on weight is gone for cutting and turned circle (product type 2). Product type 2 is calculated as per qty.
I can ask client to put approximate weight of qty they received but issue is how can I manage the stock how much is remain at labour place.

Consider I am distributor I purchase products from multiple vendors, the products are classified in several types which is mentioned above. Every vendor has his price for products.
After purchase the product may go for processing or it may be sold. If it goes to process there will be 4 stages, according to client he can decide to sell product during any stage.
For e.g.

Client bought/purchased a product which is calculated as per weight in kg.
The product is given for cutting (stage 1) to multiple labours in parts for e.g. If I have 100 kg raw material, I gave 50kg to labour1, 20kg to labour2 and remaining is given to labour3, during this stage the product turned into multiple pieces (number of pcs are not same every time due to scrap generated during this process is not fixed so I cannot calculate the number of pcs by formula as of now we can expect the approximate pcs which may vary +/- 10 to 100).
Now labour will send me the pcs in parts for e.g. Labour1 got 50kg raw material which turned 1000 pcs he sent me these pcs in parts i.e.  500 pcs were sent day before yesterday 200 pcs were sent on yesterday 100 pcs were sent today Rest pcs are remaining at labour1 side

Issues I am facing

I am unable to manage how much stock I have given to labours
How much remain at their end (i.e. labours end)
How to maintain stock details for selling purpose at it is not managed through one table.

What I have done till now
I created following schema to solve my issue but I am not sure how much it is correct according to rules of DBMS.
Purchase
{ id, vendor_id, prod_id, weight, qty, focusOn (weight / qty),
  rate, amount, remainingWeight, remainingQty }

Job
{ id, purchase_id, prod_id, labour_id, weight, qty, focusOn (weight / qty) }

Returns
{ id, job_id, prod_id, weight, qty }

focus on parameter check whether to check weight of returned product or qty to make sure accountability.


texts: Determining how a schema change occurred?

texts:
Something bad happened yesterday.

A view that was created sometime back ago was modified by someone which eventually broke the reports. Unfortunately. somebody (knowingly or unknowingly) did this modification in PRODUCTION database.

My Question: Is there a way (script/software/freeware etc) by which we can come to know who (username) did this modification, so that i can revoke the access to production database for that user.

If my question is unclear, please comment.


texts: Parse a string to find data between delimiters, multiple occurrences

texts:
I have a SQL Server table with one specific nvarchar column (let's assume it's called details) having data like below:

Item1: A100 ; Item2: B200; Item3:C300: Item4:D400; Item5:E500; Item6: F600600600; Item7: ;Item8: H800

I am looking to parse this specific column details in the table and store each item value in individual columns through a SQL view so that I can use it for querying individual elements/reporting.

The item labels/descriptions Item1, Item2 etc will remain the same, so I may not need to extract that. Their number is expected to be fixed as well.

However I need to extract each item value between the : and ;  and have them assigned to Item1value, Item2value in my view.
And sometimes some values may be simply null like shown in the sample above.

create view v_name as
select ___ as Item1Value, ___ as Item2Value...
from details

How can this be accomplished?


texts:
I have a web crawler that have a table with tokens to crawl and each token have a fk_location column that is the website where that token must be processed.

I also have a function that returns me the next 20.000 (order by priority, last_started_execution limit 20000) tokens to search.

My problem is that sometimes the function returns me too much results from the same location and i need this 20.000 balanced by location.

For example: If i have 100.000 tokens in line to process, 5.000 from each location (20 locations in this case), I'll need the function get_next to return me 1.000 from each location, returning the 20.000 limit balanced.

The total line of tokens to process can be huge like millions, but I only need the next 20.000 as balanced as possible.

Also consider that I can have in line few tokens like 30.000 from location A and 2.000 from location B, in this case I will need 18.000 from location A and 2.000 from location B in a total of 20.000 being this the best possible result.

CREATE TABLE search_token (
    id SERIAL,
    search_token varchar(255),
    fk_location integer,
    priority integer,
    last_started_execution timestamp
);


texts:
I'm new to cloudsql for mysql. I have a table with trigger. I need to do some ddl changes on that table. However, I can't take any downtime.
What I have tried so far:
I tried with pt-online-schema-change. Because, it work for our local databases. However, in case of managed cloudsql - it wasn't working. Opened an issue with Percona(PT-1964).
Next, I checked for gh-ost. However, as per their documentation(https://github.com/github/gh-ost/blob/master/doc/requirements-and-limitations.md) -it doesn't support any table with trigger.
Making changes with shared lock option isn't available for me - because we have a replica too.
Does anyone knows any other method/suggeation to make ddl changes online for cloudsql for mysql.
Thanks in advance!


texts:
I am a application developer doing DBA work. I have to restore an oracle database on our test server. The databases are backed up using RMAN and the last db restore that was done on this server was in the year 2011. RMAN back up is running daily. Anyways I want to restore the test database to match the production production database. Can I copy the required datafiles and control file from the production server and place it on the test server at the exact location where the back up files are placed, or how to go about this. I want to perform a point in time recovery. I don't want to corrupt the production database. Since the production servers are backing up the database daily, I thought I would copy day before yesterday's copy and put it on the test server and give the path name to RMAN while doing point in time recovery..Please suggest me what to do..And this is the code I would use to recover, please let me know if it is ok. Thanks

SET NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"
SET ORACLE_SID=
rman target / nocatalog
shutdown immediate;
startup mount;
 run
 {
  SET UNTIL TIME "TO_DATE('set to whatever time you want to recover','DD-MON-YYYY  HH24:MI:SS')";
  restore database;
  recover database;
  alter database open resetlogs;
 }


texts:
I am pretty new to modifying databases (running phpmyadmin for WooCommerce) and I am currently stuck at a problem.

Basically I have around 400 items which have random SKUs (example).

JCB-5018057409641_888
OX TOOLS-5060242330476_870
REFINA-281314_856

I need to mass remove for everything going from the underscore and the 3 numbers.

I think I could possibly use Sublime or Notepad++ to achieve this but looking through many queries I can't seem to do it right.


texts: Defining a physical strategy for a table with ~110M rows

texts:
I am tasked with designing a table in PostgreSQL (never used it) to load data from flat files. This table will contain ~110M rows. Each row will have a certain range for IP Addresses (e.g.: 0.0.1.0 - 0.0.2.240) and around 20 attributes.

The data needs to be entirely replaced on weekly basis and once a day an update will be done based on daily files.

The application will query this table for a given IP Address. Appropriate range needs to be found and attributes returned.

My plan is to partition the table based on the first octet of the IPs. This will give me 255 partitions. Indexes will be based on Start and End IP for the range.

Please let me know if there are any more helpful ideas for partitioning and indexing? Any good practices will also help.


texts: Modelling an E-R diagram for a Pizza place database, need help representing the payment method for the customer

texts: I am modeling a "pizza place" and a customer has an account info. I created a separate table for account info. In here a customer will have an amount spent and method of payment attributes. The payment methods can be cash, cheque or credit card. If it is credit card, it needs to have a number. I'm wondering how to best represent this attribute. Does it need to be another table.


texts:
Hopefully a simple one, if I have two (fairly complex, 30-40 node) .sqlplan files (from the same SQL text) what is a good way to compare them side by side? Or just have it tell me what it did differently? Management Studio is being particularly ridiculous about laying them out with vast amounts of whitespace, and it won't let me reformat them to print on a single page even...

Thanks!


texts:
Does SQL Buffer Pool size = Max server memory.?
I have been trying to find out the reason why plan cache  in my SQL server 2016 gets cleared every few hours.

I have 128 GB Physical Memory.
Earlier i kept 126 GB as Max Server memory.
Only one SQL server instance and a main DB of size 1.5 TB
Plan cache was getting cleared and i know that nobody is running any
script to clear plan cache.

I always thought and still think that having 128 GB Physical memory won't cause memory pressure as i think it is more than enough for the application.

Clearing plan cache is causing my slow down in my application.I believe as plan reuse i not happening and bad plans are getting into the plan cache for some queries.

I checked ring buffer for memory pressure and there was indication of low physical memory.

So i changed Max Server memory to 102 GB and still after changing also i see RESOURCE_MEMPHYSICAL_LOW

I am confused where Plan cache is allocated.

I learned from plan cache internals that there are four types of caches stores and most of the stored procs,Adhoc queries etc are handles separately and sp_executesql queries are handles separately.
Is there any clues there because most of my queries(90%) are sp_executesql type
Any insights?

Result of querying select * from sys.dm_os_memory_clerks order by pages_kb desc


texts: How to Configure Management Data Collection through SSMS without Installation error?

texts:
I am trying to configure Management Data Warehouse Wizard from SSMS for Data collection.

Getting error message at step2: Run Installation Script on Database.

Error message info in below:

Run installation script on DB_Name (Error)

Messages:

Failed to configure data collection

------------------------------
ADDITIONAL INFORMATION:

The transaction log for database 'msdb' is full due to 'ACTIVE_TRANSACTION'. (Microsoft SQL Server, Error: 9002)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.1601&EvtSrc=MSSQLServer&EvtID=9002&LinkId=20476

Warning: Database created by the wizard is not cleaned up after failure.

------------------------------
ADDITIONAL INFORMATION:

The transaction log for database 'msdb' is full due to 'ACTIVE_TRANSACTION'. (Microsoft SQL Server, Error: 9002)

    Warning: Database created by the wizard is not cleaned up after failure.


texts:
I'm looking at trying to store a large amount of files in my SQL Server 2005 instead of saving them to a file system.

I recently read a white paper suggesting that anything over 1M should be stored on a file server as it can affect SQL Server performance.

Does anyone have any experience storing large binary data in SQL 05?

Thoughts on why I should or should not do so?


texts: GETDATE - Current date, Month Start and Month End in yyyymmdd

texts:
I deal a lot in ISO dates (yyyymmdd) and need to generate the first and last day of the month very frequently. I spent a good deal of time trying to put these together and figured I would put it all together and share it. Here is what I have come up with.

I took some work from an existing article and added the CONVERT function to get the date format that I need.

--Get Current Date, Current Month Start, Current Month End, and Current Next Month start in ISO date format
SELECT
CONVERT (varchar(8),GETDATE(),112) Current_Day_ISO,
CONVERT (varchar(8),(DATEADD(MONTH,0,GETDATE())-DAY(GETDATE())+1),112) CurrentMonth_start_ISO,
CONVERT (varchar(8),(DATEADD(MONTH,1,GETDATE())-DAY(GETDATE())),112) CurrentMonth_End_ISO,
CONVERT (varchar(8),(DATEADD(MONTH,1,GETDATE())-DAY(GETDATE()-1)),112) NextMonth_start_ISO

Here is another version that allows you to do the same from a declared date

--Get Declared Date, Declared Month Start, Declared Month End, and Declared Next Month start in ISO date format
DECLARE @dtDate DATE
SET @dtDate = '08/22/1998'
SELECT
CONVERT (varchar(8),@dtDate,112) Declared_Day_ISO,
CONVERT (varchar(8),(DATEADD(MONTH,0,@dtDate)-DAY(@dtDate)+1),112) DeclaredMonth_start_ISO,
CONVERT (varchar(8),(DATEADD(MONTH,1,@dtDate)-DAY(@dtDate)),112) DeclaredMonth_End_ISO,
CONVERT (varchar(8),(DATEADD(MONTH,1,@dtDate)-DAY(@dtDate-1)),112) DeclaredMonthNextMonth_start_ISO

Credit goes to:
Pinal Dave in the main article and Param in the comments section of this blog post
http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/

Feel free to review my code and put any suggestions you might have in the comments!


texts: How to move all schemas from postgres to userdb(user createdb database) in Postgres 11

texts:
End users are created all tables in PostgreSQL default database also in public schema.
As per best practice, I want to move all tables from public schema of "postgres" database to user created database "userdb".

What will be the fastest/proper methods?

I have read below. but, i want to know is there another way to do?

How do I GRANT for all tables across all schemas

Regards,
Adam Mulla


texts: E: Version '2.0.25' for 'mongodb-10gen' was not found while installing this specific version

texts:
I'm trying to install version 2.0.25 for some testing work. I followed this documentation.

Here are the steps:

sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 7F0CEB10

echo 'deb http://downloads-distro.mongodb.org/repo/ubuntu-upstart dist 10gen' | sudo tee /etc/apt/sources.list.d/mongodb.list

sudo apt-get update

sudo apt-get install mongodb-10gen

apt-get install mongodb-10gen=2.0.25

But I get this error every time
E: Version '2.0.25' for 'mongodb-10gen' was not found

How do I install this specific version?


texts: What is the standard de facto unit test framework for Postgres?

texts: I am new to Postgres and as I am about to start writing more and more code with it (script with procedures, etc.) I am wondering what is the "standard de facto" (more used, bigger community, open source, etc) unit test framework for it. Googling I have found a few options. Is PgTap the right choice?


texts: How to repair a block based Rocksdb sst file with corrupt masked crc32 checksum in that case?

texts:
Environment
I'm using the Ethereum cryptocurrency in a way which requires something called full Ethererum archival node where each new blocks (somewhat a batch of recent transactions) is added to the blockchain database with all previous states and virtual machine execution steps. More specifically, my case requires running the full open source OpenEthereum software for such task.
This means the underlying Nosql database is powered by Rocksdb and that the data is stored in 6 column format with built in Snappy compression.
The problem is OpenEthereum suffers from heavy Io workers thread synchronisation issues which means the rebuild speed of the database is capped by single thread performance of the cpu. And Ethereum grew so large that it would now take more than a year to complete a new full archival node sync using the current fastest per thread cpu (along writing several petabytes in terms of ssd durability).
This performance issue is a very complex problem to fix. So much, that it s one of the reason the company which created the software divested from it. So despite it s widespread usage, it s likely to remain unfixed especially, since the lack of manpower saw the replacement of some parallelized algorithms with serial and easier to maintain ones.
Such situation means that while the data is public for free through many websites (using their own Ethereum archival node copy), it s access as a database is not and that I m not interested by the data from the database but to use the tool which requires it.
My Problem
My node crashed last month when attempting to read a block deep in the past because a recent transaction referred it.
The issue lies at the Rocksdb backend and seems unrelated with OpenEthereum (except for some way to fix it like rebuilding just the corrupt entry by hand). One of my block blased sst file contains a masked crc32 checksum error on a single row which prevents Rocksdb to decompress it. Using the Rocksdb s dump tool, I discovered all my backups contains the error which means the issue went unnoticed for years, but neverless that this is the sole entry being corrupted.
Of course, I failed to find a multi column version of https://github.com/facebook/rocksdb/wiki/RocksDB-Repairer. But one of the easiest solution for rebuilding the file automatically using OpenEthereum would be to remove the damaged block or Ethereum transaction along all the data/sst which were happenned after it (I mean somewhat to reset the database up to the last valid block). But being new to Nosql, not only I ve no ideas on how to write a program opening the database and using DeleteRange() but I don t understand whether it would require understanding the binary format of OpenEthereum for storing index references.
So where to start for stripping the database?


texts: Cannot use "ON CONFLICT" with postgres updatable view and partial index

texts:
I have an updatable view pointing to an underlying table that has a partial index. It looks something like this
CREATE TABLE if not exists foo (
    a INT,
    b INT,
    x INT,
    y INT,
    z BOOLEAN,
    CONSTRAINT x_or_y CHECK (
      (z and x is not null and y is null)
      or
      (not z and x is null and y is not null)
    )
);
CREATE UNIQUE INDEX ux ON foo (x, a) WHERE z=TRUE;
CREATE UNIQUE INDEX uy ON foo (y, a) WHERE z=FALSE;
CREATE OR REPLACE VIEW  foo_view AS
    SELECT * FROM foo;

That is, for each row, y must be null if z is true; x must be null if z is false; and, only one of x and y may be not null. (x, a) and (y, a) must be unique. (Sorry if this is overly complicated. I'm translating from my real table that has a lot of other cruft.)
My problem comes when I want to update with ON CONFLICT. I believe I ought to be able to do this.
INSERT INTO foo_view(x, y, a, b, z)
  VALUES
  (5, null, 1, 2, true),
  (null, 5, 1, 2, false);

select * from foo_view;

INSERT INTO foo_view(x, y, a, b, z)
  VALUES
  (5, null, 1, 2, true)
ON CONFLICT (x, a) where z=true
  DO UPDATE
  set b = EXCLUDED.b;

But, I get the exception:
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

I can insert into foo instead of foo_view with the same ON CONFLICT without error.
Here is a fiddle: https://www.db-fiddle.com/f/cX2HXg91Q7yKoPeMBYzVLg/0


texts: How to measure I/O contention after adding calculated fields on indexed views to a table in SQL Server?

texts:
I have successfully added several calculated fields to different tables, for example:

ALTER TABLE dbo.tblBGiftVoucherItem
ADD isUsGift AS CAST(
    (
        ISNULL(
        CASE
            WHEN sintMarketID = 2
                AND strType = 'CARD'
                AND strTier1 LIKE 'GG%' THEN 1
            ELSE 0
        END
        ,0)
    ) AS BIT)
PERSISTED;

This doesn't seem to have had any impact on write performance on the involved table, in this case dbo.tblBGiftVoucherItem.

However, when I have added indexed views, especially those which touch more than one table with an INNER JOIN, I have noticed a considerable increase on the number of deadlocks directly related to the tables in question.

What DMVs can I use that will give me an indication of I/O pressure by database object?

For example the table dbo.tblBGiftVoucherItem from the example above.

Is there any way I can measure/monitor how long the storage engine is taking to deal with writing requests specifically related to this table?

If I knew this I would have a better idea of how many calculated fields I could add to the table, and the cost involved in each of them.

Same with the indexed views.


texts: How should I shift values to one place back in SqLite?

texts:
I have a SqLite DB in which i have 16 columns as id, and f1 to f15.
Now Mistakenly I entered data from 2 column,  leaving the first one empty. Now my all values are one place ahead i.e value of last column of first row is in 1st column of 2nd row.
Now I wish to correct this so that all values at their right place . Any query for this problem?


texts: SQL Server Create Access Denied even after adding MSSQLSERVER user permissions

texts:
I am aware that SQL Server instances need write access not for my user account (which is in fact irrelevant to that service's context) but for the user account or security principal under which the SQL Server Service is running.  (In my case NT Service\MSSQLSERVER)

Having done that, and having verified that when I create a new database in this folder, I can write to this folder, and that the database files created by this server instance are created with the user MSSQLSERVER, nevertheless, I still lack the ability to write to any folder on drive E, from the context of MS SQL, unless I also add "write" permissions for "Everyone", adding write access to the "MSSQLSERVER" is not enough.

There must be some other level of NTFS security involved here. It almost reminds me of a domain trust issue.  This is on MS SQL Server 2012, running on Windows 8 Pro.

What do you do next, if adding the MSSQL account name found in the "Log On" tab of the service, does not work?  It's frustrating, but I'm stuck with giving "Everyone" write access to this folder on drive E before any folder is writeable by SQL Server.  Drive E in my case is a regular local hard drive, not a network folder. (I'm not dumb enough to try to run SQL on a network mounted drive.)


texts: Connecting to SQL Server database from a .net application

texts:
I need to add a new form to an application which requires access to SQL Server to retrieve data from a single view and display it to the user. This application is an enterprise level application with ~1000 users.

I assume the best practice is to ask DBA to provide a system account with non-expiring password so I can use it to connect to the SQL Server.

Is there any other alternative to using SQL Database account?

How should I write the connection string in my code?


texts: How is an Index Seek satisfying this query?

texts:
I'm using the AdventureWorks database and running the following query:

SELECT ct.Name
FROM Person.ContactType ct
WHERE ct.Name LIKE 'Own%'

The execution plan shows that it's using an Index Seek (NonClustered), however, there is only one index on the table; that being the primary key on ContactTypeID.

The table is defined as follows:

/****** Object:  Table [Person].[ContactType]    Script Date: 15/10/2016 20:43:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Person].[ContactType](
    [ContactTypeID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ContactType_ContactTypeID] PRIMARY KEY CLUSTERED
(
    [ContactTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [Person].[ContactType] ADD  CONSTRAINT [DF_ContactType_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key for ContactType records.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType', @level2type=N'COLUMN',@level2name=N'ContactTypeID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Contact type description.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType', @level2type=N'COLUMN',@level2name=N'Name'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time the record was last updated.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType', @level2type=N'COLUMN',@level2name=N'ModifiedDate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of GETDATE()' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType', @level2type=N'CONSTRAINT',@level2name=N'DF_ContactType_ModifiedDate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key (clustered) constraint' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType', @level2type=N'CONSTRAINT',@level2name=N'PK_ContactType_ContactTypeID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Lookup table containing the types of business entity contacts.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType'
GO


texts: How to create and populate a PostgreSQL table from CSV automatically?

texts: I am new to database management and we are using PostgreSQL. All I need to do is to migrate CSV files (corresponding to around 200 tables) to our database. Manually creating tables for every CSVfile is a bit tiresome, so please help me out. Is there any way not only to import data, but also to generate tables from CSV files?


texts: non clustered index on same table but the order is different?

texts:
I have a table with 5 columns col1...col5 .

I need to create a non clustered index on col2,col3,col4. I did, then my boss is asking me to create one more non clustered index on col4,col3,col2. It doesn't make sense to me? Both are same right?


texts: MYSQL Multiple Join Query to Display Unique Column Values in One Field for Single Record

texts:
I am joining together three tables in order to display the names of recommended providers for a property.

First I join my properties (alias p) table to the properties_recommended_providers table

LEFT JOIN properties_recommended_providers pr ON pr.prop_id = p.id

I use a LEFT join to still get records of properties that don't have any recommended providers and wouldn't be in the properties_recommended_providers table.

Then I join with the recommended_providers table to get the names of the providers

LEFT JOIN recommended_providers rp ON rp.id = pr.recommended_provider_id

In the select clause I'm pulling info about each property (name, id, street, city, etc). Lastly, I want to list all the names of the recommended providers for each property in a final column which rp.name.

My issue is that before selecting rp.name I was getting distinct records for each record and now adding in rp.name I'm getting multiple records for each property that have more than one recommended provider. The multiple property records contain identical information in every column except the rp.name.

I know it's possible to concat together values from separate columns in a row and display that in a new column in a select query but in this particular case is it possible for any property with more than one recommended provider to have all those providers appear in one field together (separated by commas) for a single record for that property instead of having that property listed multiple times just to display each unique recommended provider ?


texts: Can I modify a column's data type against a live table in Postgres?

texts:
I have a sparse matrix table where every attribute is citext. There are some indexes built against a few of the fields.

I want to change these columns back to varchar.

Can I do this against a live production table, or do I need to bring down the
database?
Do the indexes need to be rebuilt afterwards?
Will this result in any space savings?
Will this give us any kind of performance boost (if those columns were heavily indexed)?


texts: Using mysql's load infile to only populate certain columns

texts:
I have a csv file with a lot of data in it and I want to upload it into a mysql table. However, I would like to keep one column (primary id) empty so I can edit it myself.
In mysql's documentation  it says that you can specify columns, but from my research it seems that you can only specify the columns of the csv file.

So is it possible to specify the columns of the table