texts: Lost DMK encryption password for master database

texts:
Back story:
My company has TDE encryption enabled for some of our databases. The password that the master key was encrypted by for one of our servers is lost. We do have the backup .key file, however, the password it was encrypted with is lost too.

We also have the certificate backed up, but those passwords are lost as well.

The original backup files (master key, cert) are still in existence and those encryption passwords are available for previous servers these DBs lived on.

Question:

We want to have the actual passwords on file for the certs/keys used in our environment. What is the best solution? - This is a production environment


texts: Galera wih MariaDB master-master to support write enable on disconnect

texts:
I'm trying out a distributed app using Galera replication topology that needs to support writes if nodes are disconnected from the cluster.  The idea was to have an extra MariaDB that is connected to each Galera node through master-master replication:

dbc0 <--> db0 <--> app
 |
dbc1 <--> db1 <--> app
 .
 .
 .
dbcn <--> dbn <--> app

Where the vertical replication between dbc's is Galera and needs to support 100's of nodes and WAN. The horizontal replication is MariaDB master-master within the same linux server but between different lxc containers.  The idea is the app would only communicate to the non-Galera db's, and writes would have keys containing the node #, so be guaranteed unique.  The sync between nodes can be slow, but the app (in another container) needs to interact quickly with its local db.

After setting it up with server_id's the same on the dbc's and unique on the db's, and log_slave_updates = 1 on the dbc's, it almost works:  creating a new table on any dbc creates the tables in all the dbc's and db's.  Adding a row in any dbc also shows up everywhere.

Adding a row in a db though successfully replicates to all the dbc's, but doesn't get to the other db's.  However, this topology does allow writes to a db when its dbc is turned off, and sync's nicely when the dbc is turned back on to all the other dbc's (but not the db's).  Hope this makes sense.

Is there a way to make a write in a db node replicate through both the Galera cluster dbc nodes and their corresponding MariaDB master-master replicated db's?  Or any other way to accomplish this?


texts:
I am finding that documentation for working with MySQL JSON data is still at this point apparently at it's infancy. Either that or I'm doing it wrong.
I found several docs online, i.e. https://benjaminlistwon.com/blog/working-with-json-data-in-mysql-part-1-of-3/ and others - but neither are working for me.

I recently switched from storing the station data in separate tables per station (4,100+ tables I had to do a "dynamic" PHP query on to get the data - wow!) - to JSON for speed and performance reasons, and an added benifit of not doing 4200 table writes per hour.

To get the aggregate data it's much faster - for a single row of data I can just iterate through the JSON keys. Problem is that to pull data from 83+ rows where Key=Value, PHP says out of memory error. My guess is that means I need to match the JSON value in the query instead of in PHP.

Below: SQL dump of a JSON based data table, StationData. Weather data from various stations collected hourly.

https://drive.google.com/open?id=0B6ScTEIoK3tPbGhrZkJOZmhRUTQ

Problem I'm having is features like JSON_EXTRACT, JSON_SEARCH, and JSON_etc are just returning NULL.

For example, for Olathe Kansas, I want every data stored under {"KOJC":{"Temperature":"25", "Dewpoint":"23"}} etc etc - but nothing from any other keys. The key is KOJC.
(for giggles tried the MySQL doc example):

select JSON_KEYS('{"a": 1, "b": {"c": 30}}');
+---------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
+---------------------------------------+
| ["a", "b"]                            |
+---------------------------------------+
1 row in set (0.02 sec)

select JSON_KEYS(jsonData) from StationData;
+---------------------+
| JSON_KEYS(jsonData) |
+---------------------+
| NULL                |

etc... 85 rows blah blah

select JSON_EXTRACT(jsonData, "$.KOJC") from StationData;

+----------------------------------+
| JSON_EXTRACT(jsonData, "$.KOJC") |
+----------------------------------+
| NULL                             |
| NULL                             |

etc....
    85 rows in set (0.16 sec)

select jsonData->>'$.KOJC' as Station from StationData;
+---------+
| Station |
+---------+
| NULL    |

etc....
    85 rows in set (0.14 sec)

select * from StationData were JSON_SEARCH(jsonData, 'all', '$.KOJC');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JSON_SEARCH(jsonData, 'all', '$.KOJC')' at line 1

select * from StationData where JSON_EXTRACT(jsonData, "$.KOJC");
Empty set (0.15 sec)

UPDATE - I found what's happening. JSON_EXTRACT works for a non-arrayed JSON string but when working with an array I am unable to pull out array elements.

select JSON_EXTRACT('{"KOJC":{"Tmp":"25"}}', '$.KOJC');                          +-------------------------------------------------+
| JSON_EXTRACT('{"KOJC":{"Tmp":"25"}}', '$.KOJC') |
+-------------------------------------------------+
| {"Tmp": "25"}                                   |
+-------------------------------------------------+
1 row in set (0.01 sec)

^ that works
But the below does not when presented with a array:

select JSON_EXTRACT('[{"KOJC":{"Tmp":"25"}}, {"KTWX":{"Tmp":"32"}}]', '$.KOJC');
 +--------------------------------------------------------------------------+
| JSON_EXTRACT('[{"KOJC":{"Tmp":"25"}}, {"KTWX":{"Tmp":"32"}}]', '$.KOJC') |
+--------------------------------------------------------------------------+
| NULL                                                                     |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

select JSON_EXTRACT('[{"KOJC":{"Tmp":"25"}}, {"KTWX":{"Tmp":"32"}}]', '$[KOJC][*]');
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 2.

select JSON_EXTRACT('[{"KOJC":{"Tmp":"25"}}, {"KTWX":{"Tmp":"32"}}]', '$[$.KOJC][*]');
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 2.


texts: MySQL - InnoDB - Partitioning a table with uniqueness

texts:
Following a previous post I posted: date-based-partitioning

This is my solution for partitioning a table with primary key.

I wanted to add partitioning by months and delete old record.

CREATE TABLE `filesoi` (
  `uniqueness` binary(32) NOT NULL DEFAULT '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `sha256` binary(32) NOT NULL,
  `datein` timestamp NOT NULL DEFAULT current_timestamp(),
  `lastSeen` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`uniqueness`),
  KEY `lastSeen` (`lastSeen`)
) ENGINE=InnoDB AUTO_INCREMENT=4102 DEFAULT CHARSET=utf8

Is this a good solution to create the table like so?:

CREATE TABLE `filesoi` (
  `uniqueness` binary(32) NOT NULL DEFAULT '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `sha256` binary(32) NOT NULL,
  `datein` timestamp NOT NULL DEFAULT current_timestamp(),
  `lastSeen` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `__month` tinyint(4) NOT NULL DEFAULT month(curdate()),
  PRIMARY KEY (`uniqueness`,`__month`),
  KEY `lastSeen` (`lastSeen`)
) ENGINE=InnoDB AUTO_INCREMENT=4102 DEFAULT CHARSET=utf8
 PARTITION BY RANGE  COLUMNS(`__month`)
(PARTITION `p0` VALUES LESS THAN (3) ENGINE = InnoDB,
 PARTITION `p1` VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION `p2` VALUES LESS THAN (7) ENGINE = InnoDB,
 PARTITION `p3` VALUES LESS THAN (9) ENGINE = InnoDB,
 PARTITION `p4` VALUES LESS THAN (11) ENGINE = InnoDB,
 PARTITION `p5` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)

This way I can still insert with Insert Ignore.. and the duplicates will be per partition.


texts: How to determine SQL server version install history? I.E. When it was updated last

texts:
I have various SQL server installs across several machines, and a different IT staff will sometimes randomly update each machine with different service packs and cause version issues.

Is there a way to see the version update history for the different SQL machines?


texts:
If I become a MySQL DBA, will I had to deal all day with those kind of issues or do you have tips to prevent from breaking the whole replication?

I received this message because I removed manually the database, and after that the php script remove it.

Last_SQL_Errno: 1133`
Error 'Can't find any matching row in the user table' on query.`


texts: Is it better to populate variables using SET or SELECT?

texts:
What is the better way (with regards to performance) to set a value to variable?

By SET command:

DECLARE @VarString nvarchar(max);
SET @VarString = 'john doe';
SELECT @VarString;

By SELECT command:

DECLARE @VarString nvarchar(max);
SELECT @VarString = 'john doe';
SELECT @VarString;


texts: Why do I get error 1054 using trigger on INSERT saying that column isn't on the field list?

texts:
I am trying to write a Trigger to register every new inserted genre into a news table.

Here are the tables definitions. They are only two, GENEROS & NOVEDADES.

This is the trigger:

CREATE TRIGGER insertarNovedades AFTER INSERT ON GENEROS
FOR EACH ROW
    INSERT INTO NOVEDADES (novedad) VALUES (CONCAT('Nuevo genero: ', genero));
$$

But MySQL don't let me use it:

mysql> INSERT INTO GENEROS (genero)VALUES('f');
ERROR 1054 (42S22): Unknown column 'genero' in 'field list'

What is wrong on the trigger code?


texts:
I need a sql or hql query to find all parents of a particular record, that should work in oracle as well as mysql, for eg if I have the id: 201, the parents would be: 188, 187, 1.
any help would be greatly appreciated, thanks


texts: Why do we have undocumented and unsupported functions in SQL Server?

texts:
After reading an article, dated 2012, about the undocumented functions fn_dblog and fn_dump_dblog I was wondering why they are still undocumented and unsupported if they have been around for a long time and are quite useful. Same with this sp_msforeachtable and sp_msforeachdb article from 2004.
I could benefit from one of them to answer a question on the forum (During a log backup is the data backed up to the start or end of the operation?), but I'd like to know why they're shipped with SQL Server in a undocumented and unsupported way until now.
I understand they're said not to be reliable, but if that was the only reason, in 16 years they could have been corrected. So there must be another explanation.


texts: vba Excel data Validation error 'Application-defined or object-defined error'

texts:
I've read loads of similar questions and answers, but keep getting error.

What I do is I export query via

DoCmd.TransferSpreadsheet acExport, 8, "blabla", outputFile, True

Then I'm attempting to add validation to a range in my new excel file.

Here I get an error on setting AlertStyle:=xlValidAlertStop, Operator:=xlBetween,

(no errors without them, but no validation is set as well, obviously)

[Application-defined or object-defined error ]

Running same code from Excel - cause no issues!

Sub ApplyExcelFormating(ByVal myFile As String, ByVal mySpreadsheet As String)

    Dim myValidationList(1) As String
    myValidationList(0) = "YES"
    myValidationList(1) = "OK"

    Dim XL As Object, WB As Object, WKS As Object
    Set XL = CreateObject("Excel.Application")
    XL.Visible = True
    XL.DisplayAlerts = True
    Set WB = XL.Workbooks.Open(myFile)
    WB.Activate
    Set WKS = WB.worksheets(mySpreadsheet)

    Dim lastrow As Integer
    lastrow = WKS.UsedRange.Rows.Count

    WKS.Range("K2:K" & lastrow).Activate
    WKS.Range("K2:K" & lastrow).Select

Only these two cause error:

    'AlertStyle:=xlValidAlertStop, Operator:=xlBetween,
    With WKS.Range("K2:K" & lastrow).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(myValidationList, ",")
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With

    WB.Save
    XL.Workbooks.Close
    XL.Visible = True
End Sub


texts: What are the pros and cons of this schema for flashcards app?

texts:
I am planning a flashcards app with the following schema:

user(id, name, email)
flashcard_group(id, user_id, group_name, group_tag)
flashcard(id, group_id, question, solution)
guess(id, user_id, flashcard_id, is_correct)

Some of the things I would like to ultimately calculate are:

The number of times a user guesses a flash card incorrectly and correctly (or a correctness percentage)
All of a user's flashcards
All of the user's flashcard groups
Probably more information about guesses.

Does this seem reasonable? How else could I go about capturing guesses in a logical fashion?


texts: My question is in a select statement how do you return the RID along with the table data?

texts:
I need your help with a concept I want to show developers, that concept is to help them understand how SQL Server retrieves data from a table.

In the event that a developer does not use a clustered index on a table I want to be able to show them the RID (Row Identification number) SQL Server uses to find the record.

So my question is in a select statement - how do you return the RID?

I know that you can use the following to find where SQL Server stores the data in the data file:

SELECT TOP(100) sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot], *
FROM  [dbo].[Badges] ;

SELECT   TOP(100) *
FROM   [dbo].[Badges]
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%);

My question is:

In a select statement how do you return the RID alongside the table data?


texts: sql server log shipping: how to monitor that the replica is fresh? (e.g. that log shipping is working)

texts:
I want a monitor on my replica (destination) server to message me:

a) once a day, telling me: "Data as of  is ### minutes old"

b) any time data is older than N minutes (indicating log shipping has gone down)

I typically have tlogs moving every 5 minutes, on a very active database.

Thank you!


texts:
I have a standalone server which is running Jira and PostgreSQL 9.6. I have noticed that PostgreSQL has shared_buffers parameter is equal 128MB where RAM is 32GB. Because server is shared with application Jira then there is 16GB left for other usage (including postgres, httpd).
According to PostgreSQL documentation size of the shared_buffers should be 25% of RAM. It could be 16GB * 0,25 = 6,5GB.
Unfortunately I have doubts because application jira is using 20 connection sessions to PostgreSQL server. So I afraid that when I increase shared_buffers it will multiply it by 20 usage of entire memory.

Is it true:

approx. all memory consumed by postgres = shared_buffers*sessions

What will you suggest to do with shared_buffers parameter?


texts: How do I validate all my constraints in postgresql?

texts:
It seems like I have been bitten by the first bug described in the release notes of postgresql 9.3.4: http://www.postgresql.org/docs/devel/static/release-9-3-4.html

I now have e.g. duplicate primary keys. What is the best approach on doing a loop of re-checking of all my constraints (pkeys, fkeys) -> fixing issues -> checking again, to make sure that my data is OK?

UPDATE

I decided to go with an approach on fixing the issue by deleting all constraints, and then recreating them using the following method http://blog.hagander.net/archives/131-Automatically-dropping-and-creating-constraints.html. However I'm currently stuck on an error message, trying to recreate a pkey:

ERROR:  failed to find parent tuple for heap-only tuple at (1192248,5) in table "fruits"
CONTEXT:  SQL statement "ALTER TABLE "pm"."fruits" ADD CONSTRAINT "fruits_pkey" PRIMARY KEY (id)"

What does this mean, and how do I fix that one (I can live with deleting it, if that's an option)?

Another question: Would my data actually be all good, if I just got rid of any duplicate rows by deleting them, and then did a pg_dump, and restored the database from that dump. It would rebuild the data structure - right?


texts:
I am a newbie to MySQL, but understand Oracle databases well. I need a quick help to understand the architectural differences. Currently I am working on a Windows platform.

1) In Oracle we have to put the database in archive log mode, do we have to do this with MySQL to perform point in time recovery?

2) In Oracle, an instance starts with memory structure SGA. How does it work in MySQL?

3) RMAN is the online backup tool for oracle. MySQL backup for MySQL, is it capable to do point in time recovery?

4) I don't understand logging and database files layout in MySQL In Oracle if i create tablespace  we see a file on the file system with name. How does this work in MySQL?


texts: Purpose of owning a schema in SQL Server

texts:
I've been spending the last few days cleaning up database permissions, grouping them all into AD groups and removing orphaned users.

This has lead me to a recurring scenario:

Find a user that owns a schema
Transfer schema ownership to SA
Remove user

The schema's are not subject to regular change, and most of the time it's a user owning the dbo schema.

I've been researching what the reasoning might be behind giving these users ownership of schema s, however I can't really find a proper reason. Most documentation speaks of schema membership, using schema's for permissions, but I'm hard pressed finding much about schema ownership.

What might be a correct reason for giving users ownership of schema's?
Are there any hidden dangers in removing schema ownership from orphaned users?


texts: Does Postgres read through entire WAL when starting replication from later point?

texts:
I have been investigating an inefficiency in our ETL pipeline, where on one side we have Postgres database with logical replication slots.
A consumer keeps track of the LSN where it's at, and sometimes disconnects by design. Upon return, it tells the database the LSN where it would like to begin. However, the time it takes for the replication slot to start returning any content seems to depend on the amount of WAL files currently on disk. It appears as if the replication slot parses through them all to find the right position, which can sometimes take hours.
This behavior does not seem normal or necessary, am I missing something?


texts: Why are breakpoints not being hit in SQL Server Management Studio?

texts:
SQL Server Management Studio allows debugging of SQL (breakpoints and stepping through the SQL). It is possible to step in to a stored proc being called and to see a call stack. However I cannot determine how to set a breakpoint on a stored proc that will be called from those statements, that is, I can set a breakpoint on the top level SQL statements that I run that call on to sub-procs, but any breakpoints in the sub-procs are not hit.

UPDATE:

When debugging the 'breakpoints' view shows a warning symbol against the problematic breakpoints with a tooltip text of 'The breakpoint will not currently be hit. Unable to bind SQL breakpoint at this time. Object containing the breakpoint not loaded'.

If however I step into a stored proc with the debugger and place breakpoints on the view that it has opened then the breakpoints are bound correctly. So my question is, how do I get to to a view that allows me to bind breakpoints to stored procs correctly? It seems that right clicking on a stored proc and selecting 'Modify' gets you to a view that is not bound to the debugger.


texts: How to back up a small database in SQL Server 2008 R2 Express Edition

texts:
I will shortly be migrating a database from SQL Server 2000 (part of SBS 2003) to SQL Server 2008 R2 Express Edition

The database is small, and there are only a few hundred short transactions per day - I'd like to keep everything as simple as possible from a recovery perspective whilst minimising the amount of lost data in the event of a failure

Can I just run a full backup every hour using Windows Scheduler? I already have a solution for long term archival of database backups which these could just plug in to

What can I do to back up the 'logins' - I understand they are not saved as part of a full database backup. I want to have everything necessary to be able to perform bare metal recovery  if we have to

Anything else I need to think about?


texts:
What I have

Table A                                 Table B

ID    ID_Fruit    ,,, so on             ID      ID_Fruit   ID_CRATE  DateTime
1     1002                              i_1     1002          1      2014-07-28 00:00.000
2     4534                              i_2     1002          2      2014-06-25 00:00.000
3     1243                              i_3     1243          NULL   2014-07-09 00:00.000
4     1003                              i_4     4534          1      2014-07-23 00:00.000`

Result I want:

ID    ID_Fruit    ,,, so on             ID      ID_Fruit    ID_CRATE     DateTime
1     1002                              i_1     1002           2      2014-06-25 00:00.000
2     4534                              i_4     4534           1      2014-07-23 00:00.000

What I am trying,

SELECT *
FROM TABLEA
    OUTER APPLY
        (SELECT *
         FROM TableB
         WHERE TableB.ID_Fruit = TableA.ID_Fruit
           AND TableB.ID_CRATE IS NOT NULL
        ) As Table_B

I want only 1 row from several rows in Table B based on closest DateTime


texts: Is it safe to add new features to production SQL Server Instance

texts:
I need to add Integration Services Catalogs to our production instance of SQL Server 2008 in order to automate some SSIS packages. It wasn't included in the original installation 5 or 6 years ago.

Is there any danger in adding new features to the production server?

We have backups in place, but I would prefer to find a different automation solution if there is a probability that the data could become corrupt.

I would follow the steps from this post if I were to install the additional feature-
https://stackoverflow.com/questions/14551255/how-do-i-enable-integration-services-ssis-in-sql-server-2008

****EDIT****

I realized that Integration Services Catalogs isn't available for SQL Server 2008. So the link I posted is irrelevant. I'll leave the question up in case anyone has valuable input regarding adding features to existing SQL Server instances.


texts:
I have a trigger on a table that kicks off several other processes. However, I would like to ensure that even if the other processes fail, the insert is successfully committed to the original table.

I've been doing this by issuing an explicit COMMIT TRAN in the trigger before calling my other procedure. This seems to have been working fine, except for an unsightly error message:

  Msg 3609, Level 16, State 1, Line 3
  The transaction ended in the trigger. The batch has been aborted.

Upon doing a bit more research I changed the procedure, adding SET XACT_ABORT OFF; at the very top, and removed the explicit commit. This seems to have had the same effect.

Which one of these is preferable? Are there any pitfalls I should be aware of with either method?


texts: mysql - Can a inner join statement limit query results?

texts:
I am attempting to write a query (mysql maria db) that pulls all rows from my main table (19m records) but also pulls categories / sub categories (in another table -> business_sub_category / categories).

I have written a number of inner joins into my statement to pull the tables but it appears that i have made a mistake. The below query only pulls 500 records. There are at least 7m emails.

Why is my query being limited to only 500 (thats how many get returned from this result set).

Even just giving me an idea of why my query might be limited would be very helpful. I'm new!

SELECT businesses_old.id, businesses_old.name, businesses_old.email, business_sub_category.sub_category_id, sub_categories.id, sub_categories.category_id, categories.id, categories.name
FROM businesses_old
INNER JOIN business_sub_category
ON businesses_old.id = business_sub_category.id
INNER JOIN sub_categories
ON business_sub_category.id = sub_categories.id
INNER JOIN categories
ON sub_categories.category_id = categories.id
WHERE email IS NOT NULL AND email <> '';


texts: We are running database maintenace with indexing the system, indexing fails due to locking?

texts:
We have been trying to do re-indexing in our system, there are users still using the database during the indexing. While doing index for certain tables there is failure of indexing this seems to be due to deadlocks

the indexing script we use is:

--------  REBUILDING THE INDEXES----------------------

CREATE TABLE #TableIDs (Tablename nvarchar(200),IndexName nvarchar(1000))
        --Database to be indexed--
        INSERT INTO #TableIDs (Tablename, IndexName)
        SELECT a.object_id,name
        FROM sys.dm_db_index_physical_stats (DB_ID(N'RAL'), NULL, NULL, NULL, NULL) AS a
        JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
        Where avg_fragmentation_in_percent > 20 and name is not null  and OBJECT_SCHEMA_NAME(a.object_id) = 'dbo'
        And object_name(a.object_id) not in (SELECT
                                             OBJECT_NAME(p.object_id) BlockedObjectName
                                             FROM sys.dm_tran_locks AS tl
                                             INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
                                             INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
                                             INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
                                             INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
                                             INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
                                             CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
                                             CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
                                             Where db.name = 'RAL')

declare c cursor for select object_name(Tablename) as Tablename ,IndexName from #TableIDs
declare @TableName varchar(1000)
declare @IndexName varchar(1000)
open c
fetch next from c into @TableName, @IndexName
        while @@FETCH_STATUS = 0
        BEGIN

              if ((SELECT
                                         count(OBJECT_NAME(p.object_id)) BlockedObjectName
                                         FROM sys.dm_tran_locks AS tl
                                         INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
                                         INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
                                         INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
                                         INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
                                         INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
                                         CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
                                         CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
                                         Where db.name = 'RALMYT' and OBJECT_NAME(p.object_id) = @TableName) ) = 0
                BEGIN
                    EXEC ('ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD WITH (FILLFACTOR=90,STATISTICS_NORECOMPUTE = OFF,ONLINE=OFF,MAXDOP=8)')
                    PRINT 'Reindexing ' + @IndexName + ' on  ' + @TableName + ' table'
                END
     fetch next from c into @TableName, @IndexName
End
CLOSE c
DEALLOCATE c

DROP TABLE #TableIDs

Please let me know..... how to avoid the errors or failures while indexing


texts: Effective way of accumulating index fragmentation statisitcs

texts:
Please help me figure out a way to gather fragmentation statistics of all tables in my DB on a daily basis.

The problem is that scripts like this one:

SELECT OBJECT_NAME( ind.OBJECT_ID )
AS TableName,
       ind.name
AS IndexName,
       indexstats.index_type_desc
AS IndexType,
       indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID( ), NULL, NULL, NULL, NULL) indexstats
INNER JOIN
sys.indexes ind
ON ind.object_id=indexstats.object_id
   AND ind.index_id=indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent>30
ORDER BY indexstats.avg_fragmentation_in_percent DESC;

will work forever to complete as tables in our DB are <=300GB.
My goal is to regularly gather statistics on fragmentation to implement proper fillfactor for indexes based on how heavily they get fragmented over time according to this article:

5 Things About Fillfactor


texts: Change Data Capture and the __$update_mask binary

texts:
We're using CDC to capture changes made to a production table.  The changed rows are being exported out to a data warehouse (informatica).  I know that the __$update_mask column stores what columns were updated in a varbinary form.  I also know that I can use a variety of CDC functions to find out from that mask what those columns were.

My question is this.  Can anyone define for me the logic behind that mask so that we can identify the columns that were changed over in the warehouse?  Since we're processing outside of the server we don't have easy access to those MSSQL CDC functions.  I would rather just break down the mask myself in code.  Performance of the cdc functions on the SQL end is problematic for this solution.

In short, I'd like to identify changed columns by hand from the __$update_mask field.

Update:

As an alternate sending a human readable list of changed columns over to the warehouse was also accepatable.  We found this could be performed with performance far greater than our original approach.

The CLR answer to this question below meets this alternative and includes details of interpreting the mask for future visitors.  However the accepted answer using XML PATH is the fastest yet for the same final result.


texts: How to use WITH RECURSIVE with values in an array?

texts:
I'm sure I'm missing something simple here, but I'm trying to use PostgreSQL's WITH RECURSIVE to create a graph of children records.

The data (simplified) looks like this:

CREATE TABLE offspring (parent, children)
AS VALUES
  ( 'aaaaaaaa-8865-4b40-a482-78fc8a27d1bb'::uuid, '{ bbbbbbbb-8865-4b40-a482-78fc8a27d1bb, cccccccc-8865-4b40-a482-78fc8a27d1bb }'::uuid[] ),
  ( 'bbbbbbbb-8865-4b40-a482-78fc8a27d1bb'::uuid, '{ dddddddd-8865-4b40-a482-78fc8a27d1bb, eeeeeeee-8865-4b40-a482-78fc8a27d1bb }'::uuid[] ),
  ( 'cccccccc-8865-4b40-a482-78fc8a27d1bb'::uuid, '{ ffffffff-8865-4b40-a482-78fc8a27d1bb }'::uuid[] ),
  ( 'dddddddd-8865-4b40-a482-78fc8a27d1bb'::uuid, '{ 11111111-8865-4b40-a482-78fc8a27d1bb }'::uuid[] ),
  ( 'eeeeeeee-8865-4b40-a482-78fc8a27d1bb'::uuid, '{ 22222222-8865-4b40-a482-78fc8a27d1bb }'::uuid[] ),
  ( 'ffffffff-8865-4b40-a482-78fc8a27d1bb'::uuid, '{}'::uuid[] ),
  ( '11111111-8865-4b40-a482-78fc8a27d1bb'::uuid, '{}'::uuid[] ),
  ( '22222222-8865-4b40-a482-78fc8a27d1bb'::uuid, '{}'::uuid[] );

I've taken several stabs at it that I thought would work, but nothing has proven successful.

This simplified view reflects just the fields necessary to do the recursion, but when done, I'll also need to check timestamps on each record to filter appropriately, but I'm guessing I can handle that once the basic recursive query is in place.

My goal here is to end up with a single CTE that maps a single parent UUID to a UUID array of all of its children.  Neither depth nor order are important in the resulting array.

Any help is appreciated.


texts:
Given this set...

Site, Date
4   10/10/18
3   10/10/18
4   07/10/18
5   12/10/18
3   11/10/18
5   13/11/18

I want to order it such that the rows are ordered by site and the site groups are ordered by MIN date.

i.e.

Site, Date
4   07/10/18
4   10/10/18
3   10/10/18
3   11/10/18
5   12/10/18
5   13/11/18

I have tried using...

SELECT

    RANK() OVER (PARTITION BY Site ORDER BY (Date)) AS GroupRank,
    a.*
FROM

    (select * from
    (select 4 AS Site,   '10/10/18' as date union
    select 3,   '10/10/18' union
    select 4,   '07/10/18'  union
    select 5,   '12/10/18'  union
    select 3,   '11/10/18'  union
    select 5,   '13/11/18'  as date ) b) a
    order by GroupRank

The results don't make sense.

1   3   10/10/18
1   4   07/10/18
1   5   12/10/18
2   5   13/11/18
2   4   10/10/18
2   3   11/10/18

It's supposed to be partitioning by site but it's given the same rank to rows with different sites.


texts: Finding out the hosts blocked by mysql server

texts: Can someone tell me how to list the hosts which are blocked by the mysql server due to the reason that they crossed the limit of max_connect_errors. Is there any table in which MySQL server keeps this data. I am using mysql-server-5.1.63


texts:
I have a SYSTEM's table AUD$ where AUDIT records are stored. My AUDIT_TRAIL is set to DB,EXTENDED. I am on Oracle DB version 12c (12.2.0.1.0).

With time (in 3 years) my AUD$ table grew to 500GB in size, especially because of it's 2 CLOB columns (SQLBIND and SQLTEXT).

I want to shrink  storage consumed by AUD$ to minimum, without losing any audit data.

I came up with the following plan:

CREATE TABLESPACE TS_AUDIT_HISTORY
CREATE USER AUDIT_USER in tablespace TS_AUDIT_HISTORY
CREATE  partitioned TABLE AUDIT_USER.AUDIT_HISTORY_TABLE in tablespace TS_AUDIT_HISTORY with:

3.1. COMPRESS HIGH on LOB's  (SQLBIND and SQLTEXT)
3.2. COMPRESS HIGH on all partitions

INSERT INTO AUDIT_USER.AUDIT_HISTORY_TABLE select * from SYS.AUD$; COMMIT;
TRUNCATE TABLE SYS.AUD$

I haven't implemented my plan on any Production environment yet, since almost everyone advices against any other solution but DBMS_AUDIT_MGMT - which I don't want to use because it doesn't compress table's data / CLOB's).

But I have implemented my plan on a Development environment and results were amazing - After compressing I got from  500GB -> 50GB which is like 90% compress ratio !

My questions:

A) How 'smart' is to truncate SYSTEM's table AUD$, can it have any other impact besides the fact that user's won't be able to log in?

B) When I will be TRUNCATEing AUD$, table will be locked, which will prevent users to log in! - any way to 'bypass' that, since users being able to log in to DB is very important?

C) What do you think of my plan?

Any input / concern / comment is welcome.

STATUS UPDATE:

I successfully created a new tablespace, new user (with default new tablespace), new table with partitions and compression on it as well as on the LOB's and inserted all the data from SYS.AUD$ to the new table -I had to use parallel hint here and it still took me 6 hours. Afterwards I truncated AUD$. Original AUD$ was 500GB in size and now the new one (with all the records of course) is only 40GB in size. You were right guys, Truncate SYS.AUD$ finished in like 1 or 2 seconds.

So now I saved like 460GB.... but not really. Coz now these 460GB can be used only in AUD$'s tablespace (SYSAUX) and not in other tablespaces :/   I really hoped that TRUNCATE TABLE AUD$ DROP STORAGE; actually releases the allocated storage, but it does not. I can see that SYSAUX is 460GB less in size now, but these 460GB didn't free in ASM storage.

The only 100% way to reallocate the storage to be available as Usable storage in ASM as far as i know is:

Drop a table (which is of course not an option for AUD$)
export / import table (which is not an option since Oracle does not allow that for AUD$ table, already tried to export it with expdp but it didn't work).

So how do I actually make the 'freed' / deallocated storage available to ASM / other tablespaces?


texts:
I am wanting to send an email through the SQL Server Agent once an insert has been made to a specific table.  I tried the syntax below, but the email is not being sent, nor queued to send.

ALTER TRIGGER [dbo].[Insert_Trigger] ON [dbo].[ProdTable]

AFTER INSERT
AS

BEGIN

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Test',
    @recipients = 'XXXX@gmail.com',
    @body = 'Test',
    @subject = 'Test' ;

END

If I run the EXEC command by itself in a query window, it executes successfully, so I don't think it's an issue with my general configuration of Database Mail that's causing the problem.

What is the proper way to do this?


texts: Scheduled Job Succeeds, Does Nothing; Manual Job Succeeds, Performs Requirements

texts:
I have a job in SQL Server Agent (2012 Instance) that deletes rows from a table.  When I manually run the job, it succeeds without issue and in reviewing the table, does exactly what I want it to do.

When I've set it to an automatic schedule, the history indicates that the job has successfully run each step, yet in reviewing the table, the records are not deleted.  The job is set to enabled, and the history shows that it's run on the schedule, even with no records being removed.  If I run it manually, suddenly I see the correct results in the table.

Each step is run under the same login, for instance EXECUTED as user: NT ACCOUNT\SQLSRVAGENT, whether I run the job manually or it's run on its schedule.  The only difference I see between running it manually or the automated schedule is what invokes the job.  When it's the schedule, it reports The Job was invoked by Schedule 1 (Name) vs. when I run it manually, it reports, The job was invoked by User YouUser.  Note that this standard setup exists on other servers and doesn't create the same issue, so even though this is the only discrepancy I can find between how the job is run (manual vs. schedule), this doesn't look to be the issue.

Any other idea what could be causing this?


texts: Create MySQL trigger that updates position of each row when sorted by a specific field

texts:
Let's see an example, I've got this table that contains teams, each of them having a certain number of points:

CREATE TABLE teams (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  points INT(11) NOT NULL,
  position INT(11),
  PRIMARY KEY (id)
);

In the position field, I want to have their current position in comparison with other teams, so for example I've got 3 teams, sorted by the position:

id  name   points  position
2   team1  1700    1
3   team2  1500    2
1   team3  1300    3

Every time teams table is updated, I want to update the position. For example, I update the team2 with 500 points so it has 2000 points now and is first:

id  name   points  position
3   team2  2000    1
2   team1  1700    2
1   team3  1300    3

So how do I update position field every time points are updated? My idea was to create a trigger as follows:

delimiter |

CREATE TRIGGER updatePoints AFTER UPDATE ON teams
  # How to go over each row (not only updated rows) and change each row's position (rank) when teams are sorted by points?
  BEGIN
    # And here code to update the points

  END;
|

delimiter ;

However I have no idea how to create a trigger that will update positions correctly when the table is sorted by points.

EDIT: The idea is to create a trigger that will update the team by assigning correct position to each team in order to be able to retrieve the position of a specific team just by doing:

SELECT position FROM teams WHERE id = 1


texts: Normalizing data past initial db design phase

texts:
I wanted to poke at everyones brain to better understand when it makes sense to normalize a table that carries redundant data. I recently worked on a project at my startup and really question myself on whether it was a good idea. So the problem was something like this.

Our web app is a e-commerce web app. Our db contains a table that looks like this:

Store Product Table
-----------------------
- product_name
- price
- store_product_id
- coupon_name

The data looks something like this:

pk, product_name, price , store_product_id, coupon_name
1, 'Orange Juice', 10.0, 2, 'ORANGE15PERCENT',
2, 'Orange Juice', 10.0, 2, 'ORANGE50PERCENT',
3, 'Orange Juice', 10.0, 2, 'ORANGE70PERCENT'
4, 'Milk', 9.0, 89, 'MILK20PERCENT',
5, 'Milk', 9.0, 89, 'MILK50PERCENT'
6, 'Ham', 5.0, 45, '',
...
300, 'Jacket, 100.0, 90, 'JACK50PERCENT',

This is a relatively small table (~200 records). As you can tell there is a ton of redundancy. I saw redundancy and broke this out into two tables a Product table and Coupon table. This eliminated all the redundancy. I felt proud at first, but I wonder if it was really necessary. Should one always remove redundancy in a tables and break apart as two tables? Please note that this decision was made way past the initial db design phase. It's probably an easy decision to make when you are first designing things, but what about when you've built a codebase around a redundant table?

Note: I realize that there are pros to having redundant data if your table is a read heavy table, since this is a small table I don't think thats super relevant in this situation. Open to be proved wrong though.


texts:
When should I use Full-Text Search instead of using a LIKE search, such as

SELECT * FROM myTABLE WHERE myCOLUMN LIKE '*something'


texts: MySQL: "CREATE DATABASE mydb" command hangs

texts:
On my MySQL 5.6 server, when logged in as root, the command CREATE DATABASE hangs. No error message - nothing. The MySQL error log doesn't show anything special either.

What could I look into to troubleshoot the problem?


texts: Placing a uniqueness constraint on a date range

texts:
I have a table reservation with columns roomno(INTEGER), startdate(DATE), enddate(DATE) with a primary key on (roomno, startdate).

How do I place a constraint on the table such that bookings are not allowed to overlap?

I am trying to implement this in SQLFIDDLE postgreSQL9.3

For example:

101  2016-01-01   2016-01-05
101  2016-01-03   2016-01-06 [This row should not be possible to insert]

startdate and enddate are data type date.


texts: SQL Server 2012 Patching Service Pack and CU Question

texts: I am tasked with updating a SQL Server 2012 from a version(11.0.5623.0) before SP3 all the way to SP3 CU2. Can I install both patches without a reboot until afterwards? All the documents I have found on Google indicate that a reboot is only needed if a file is locked.


texts:
I have a nagios application running on 2 servers, i am planning to have have master-master replication for HA. for master-slave replication, slave should be read-only. in this manner is there any pre-req for master-master replication. while doing master-master replication i ran into a lot of duplicate as both the databases are auto-increment on their own and trying to replicate each other.

can some one please help to resolve the issue.

Thanks,


texts:
What is the difference between setting the transaction isolation level in the client configuration and the server configuration?
If I set the client isolation level to be serializable while the server's is read-repeatable what is the result for the transactions? Both of the same client and with transactions of other clients on the same tables?


texts:
Is there a way to pass the result from OPENROWSET BULK into a variable so i can use STRING_SPLIT to divide it into rows?

Something like:

    DECLARE @MyString = FROM OPENROWSET (BULK 'C:\Temp\File.csv', SINGLE_CLOB) MyFile

SELECT * FROM STRING_SPLIT ( @MyString , '\n' )


texts: Running Check Check Constraint and still have untrusted foreign keys (not a replication issue)

texts:
Anyone any ideas on this one?  As per Brent Ozar's recommendation (thanks Brent and team!), I've ran the query below which shows FK's that are untrusted:

SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname
from sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0;
GO

SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname
from sys.check_constraints i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND  i.is_disabled = 0;
GO

It displays two keys.  I then run WITH CHECK CHECK CONSTRAINT against these two foreign keys (they are on massive tables), which takes some time but eventually completes.  I run the checking query above again, and they are still there!

I've done some research online and found that "is_not_for_replication" can be the common cause for this, but when I run:
    SELECT name, is_not_trusted, is_not_for_replication FROM sys.foreign_keys   WHERE is_not_trusted = 1; it is showing is_not_for_replication = 0.

Anyone any ideas why these foreign keys aren't trusted despite running CHECK CHECK CONSTRAINT?  It should be noted that I am able to run CHECK CHECK CONSTRAINT against other foreign keys successfully.

Thanks in advance!


texts: Two SQL Servers cross-mirroring each other?

texts:
I'm looking to see if I can create a "Brother's Keeper" environment in SQL Server 2008 Standard Edition.  I have a large number of databases across multiple servers.  I'm not worried about scalability (for now), but wanted to know if anyone has had practice mirroring the databases hosted on server A on server B, the ones on Server B on Server C, etc.... I'm trying to purely have a failover system without significant additional licensing/resources.

Thanks!


texts: Is there a sql command I can use to manually start replication?

texts:
The title pretty much says it all. I need a command or a script that will manually kick off SQL Server 2012 replication if it is beyond the time it scheduled to start but hasn't yet.

Is there such a thing?


texts: SQL trigger that outputs incremental numbers from zero whenever the month changes

texts:
I have the following trigger that outputs a number in the format such as 170200005 where 17 is the year and 02 is the month (February) and changes as months changes e.g March 03 then the last digits are incremental.

BEGIN

  SET @var1 = LPAD((SELECT MONTH(CURRENT_DATE())), 2, '0');
  SET @var2 = (SELECT YEAR(CURRENT_DATE));
  SET @var2 = (SELECT MID(@var2, 3, 2));
  SET @var1 = CONCAT(@var2, @var1);

  INSERT INTO table1_seq VALUES (NULL);
  SET NEW.id = CONCAT(@var1, LPAD(LAST_INSERT_ID(), 5, '0'));

END

I would like to have a code that will output in the same format as highlighted before but then starts from 00001 whenever the month changes.


texts: Is there an easy way to have a non-IDENTITY, auto incrementing column based on another column?

texts:
+----+-------------+-----------------+-------------+
| ID | ProductName | ProductCategory | ProductCode |
+----+-------------+-----------------+-------------+
|  1 | Item 1      |               1 |           1 |
|  2 | Item 2      |               1 |           2 |
|  3 | Item 3      |               2 |           1 |
|  4 | Item 4      |               3 |           1 |
|  5 | Item 5      |               1 |           3 |
|  6 | Item 6      |               2 |           2 |
+----+-------------+-----------------+-------------+

Essentially what I am trying to do is have a column autoincrement but based on the last row in the same ProductCategory.

Currently I have a stored procedure that does the following

SELECT TOP 1 @NextProductCode = ProductCode + 1
  FROM Products
 WHERE ProductCategory = @ProductCategory

This works, but despite the fact that this is a pretty low traffic database there's an obvious concurrency issue here. I could throw a TABLOCKX in the stored procedure I guess but I'm curious if there's a more simple way to accomplish this.

I'm also trying to avoid dynamic SQL otherwise I could create a sequence for each new ProductCategory.


texts: Deployment script always re-create constraints and indexes

texts: When generating a deployment script from SSDT it always generates code that drops the foreign key constraints & indexes then creates again those constraints and indexes. How to remove the code that drops and recreates those objects when generating deployment script? Any options in SSDT?


texts: Write to different schema with own tablespace from another account

texts:
Say I have created these two accounts:

CREATE USER u1 IDENTIFIED BY "&&u1Password"
    DEFAULT TABLESPACE u1tbs
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON u1tbs
    QUOTA 256M ON users
    QUOTA 0M ON ket;

CREATE USER u2 IDENTIFIED BY "&&u2Password"
    DEFAULT TABLESPACE u2tbs
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON u2tbs
    QUOTA 256M ON users
    QUOTA 0M ON u1tbs;

I have granted u2 full access to u1's schema. All tables in u1 are in the tablespace u1tbs as you can see. Where will data go when u2 adds data to u1's schema? If so won't it contradict with quota 0M on ...?

Edit: Question should have been on dba.stack...


texts: database with same schema and same AWS instance type, but different performance

texts: Hi I am getting a new set of production servers ready. I generated a DB script from SSMS using the schema from Server A to deploy to a new AWS instance of the same type as Server A. After populating the required configuration tables on the new DB I ran and saw a performance degradation on this new server. When I look at the execution plans for a query I see that they are very close to the same other than the value for "Memory Grant". Memory Grant is more on the original server (1656 vs 1112 on the new server). Any ideas on what could be causing this performance issue on the new server?


texts: Why is index not being used? Can I replicate plan?

texts:
I have been tuning a query/ indexes in a dev environment, but cannot replicate the new query plan when applying the same changes to a UAT environment.
Specifically, in the UAT environment the optimizer chooses to ignore a particular index and instead performs a nonclustered index seek on an existing unique constraint, followed by a key lookup on the clustered index.
An abridged version of the query is:

select  d.dim_date_id
       ,   f.dim_form_id
       ,   d.date

INTO #TMP
from    DWH.dbo.tbl_fact_outcome f
join    DWH.dbo.tbl_dim_date d      ON d.date >= DATEADD(DAY,-1,f.known_from) and d.date < f.known_to
join    DWH.dbo.tbl_dim_form df     ON  f.dim_form_id = df.dim_form_id
join    DWH.dbo.tbl_dim_question Q  ON f.dim_question_id = Q.dim_question_id

where   (d.flag_latest_day = 'Y' or d.flag_end_of_month = 'Y'  or (d.flag_end_of_week = 'Y' AND d.flag_latest_week = 'Y'))
       and d.flag_future_day = 'N'
       and df.flag_latest_form = 'Y'
       and f.deleted = 0
       and q.question_key like 'R%'

and d.date >= '14/07/2020'

The index in question is:
CREATE NONCLUSTERED INDEX [ix_tbl_dim_date_flag_future_day_includes] ON [dbo].[tbl_dim_date]
([flag_future_day] ASC)
INCLUDE([dim_date_id],[date],[flag_end_of_month],[flag_latest_day],[flag_end_of_week],[flag_latest_week])

It's not a large table and should only return one or two dates which filter results from subsequent joins accordingly. Adding other fields to key columns in index made no difference in the dev environment - the only improvement came from the order/ shape of the query plan.

The dev query plan (good) is here : https://www.brentozar.com/pastetheplan/?id=SyZjIGA1v
The UAT query plan (bad) is here : https://www.brentozar.com/pastetheplan/?id=BkBBLzCyv

Both environments have the exact same table structure and indexes and are using the same query.
The dev environment does have around half the volume of data overall, but even if I amend the date range in either to return a similar number of records the query plans in both environments stay as they are.
There was a dramatic improvement on performance in the dev environment since this query plan has been used and I am fairly confident the same would be seen in my UAT environment.
I have tried using query hints to force the index and/ or the order but haven't been able to replicate this plan.
Essentially I have two questions:

Why would the optimizer choose a key lookup over this index?
Is there anything I can do to force it to follow the shape seen in the dev environment?

I am using SQL Server 2014 enterprise edition.

EDIT - 20/07/2020 - Actual execution plans added
Actual good execution plan from dev environment - running this took 37 seconds:
https://www.brentozar.com/pastetheplan/?id=BJkr-Q7gP
Actual bad execution plan from UAT - running this took 33m 42s: https://www.brentozar.com/pastetheplan/?id=H1nsxX7ew


texts: MS SQL - CLR Stored Procedure runs as deleted user

texts:
I have a SQL Server 2017 database which contains some CLR stored procedures. When trying to call those CLR SP's, I get a "login failed" error back from the CLR SP.

My user is connected and works fine - it seems that the CLR SP is coded to run as another specific user.

I don't have access to the underlying code for the CLR SP's. Is there any way for me to change which user it's executing as?


texts: How to synchronize multiple instances of a stored procedure executing at the same time in SQL Server 2012?

texts:
Overview:
In my scenario a user uploads an excel file that needs to be written into database, I am allocating each uploaded file a batch_id   that batch_id is written in last column of each row of my table as shown in these pictures

With the help of this batch_id column I will be able to segregate that which rows belong to which document.(for example: if batch_id=1 than these 1000  rows belongs to document 1)

Problem:
Consider two users user1 and user2 executing this stored procedure at the same time while Stored Procedure  is writing batch_id=1 in all the rows which belong to document1,at the same time rows belonging to  document2 will gets inserted thus causing a conflict wether to write batch_id=1 or batch_id=2 in the rest of remaining rows.

Imperfect  Solution:
To Execute Stored Procedure on one document at a time,when its done execute it on document 2 but this will let the user to wait until the current execution finishes which i don't want to  happen.

UPDATE:::::::::::::::

The batch_id value is coming from another table MASTER_CDT in this table batch_id is primary key and auto incremented, when a document gets uploaded first a batch_id is generated this batch_id is then inserted into rows corresponding to that batch, the problem is while batch_id(consider batch_id=1) is getting written into current batch rows if another user uploads document then batch_id is changed to value 2 while rows regarding batch 1 are still missing batch_id value but now batch_id=2 will be written to them therefore causing trouble.


texts:
hi how can make this result query:

to this:

I have this code and i know it's still farfetched:

select
        max(case subcod when 'DISABILITY' then A end) as disability,
        max(case subcod when 'DISMEMBERMENT' then B end) as dismemberment,
        max(case subcod when 'MEDICAL' then C end) as medical,
        max(case subcod when 'OTHERS' then D end) as others
    from
        (
        select subcod, description as A, null as B, null as C, null as D from grp_appl_ref where ref_id = 'RIDERS' and subcod = 'DISABILITY'
        union
        select subcod, null, description, null, null from grp_appl_ref where ref_id = 'RIDERS' and subcod = 'DISMEMBERMENT'
        union
        select subcod, null, null, description, null from grp_appl_ref where ref_id = 'RIDERS' and subcod = 'MEDICAL'
        union
        select subcod, null, null, null, description from grp_appl_ref where ref_id = 'RIDERS' and subcod = 'OTHERS'
        )

I was wondering if there's another reserved word instead of max so i can remove the null values so the cells with values would move up.

is it possible?

thanks,


texts:
I'm really battling an issue where I have a Users table that has a growing number of user characteristics (regligion, smoking preferences, etc). The strategy I've used thus far has been to add a column for each preference that keys off onto another table.

For example, if User XYZ has a RelgionId of 3, that could mean they're Christian. At runtime, if I need their religion, I join onto another table.

This strategy has worked so far. However, I'm getting concerned about the number of columns in the tables as the number of preferences is increasing. Also, this strategy leads to many joins if I need to get all values for a single user.

I'd like to find out the most normalized way of representing this data. Anybody have any ideas?


texts: Add header to multi server or instance query (powershell)

texts:
I have the following PowerShell script I am looking to add a header to this results without having a duplicate per each iteration but I am not familiar enough with Powershell if anyone can point me in the right direction it would be helpful.

  $servers = 'E:\DBA\servers.txt'
  $outfile = 'E:\DBA\out.csv'
  $queryfile = 'E:\DBA\query.sql'

  Get-Content $servers -First 1 | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -First 1 | Out-File
  $outFile -Force} Get-Content $servers | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append $outFile}

The error is


texts: SQL Server 2016 not using index used in 2012

texts:
2016 plan here

2012 plan here

I have almost identical tables with very small difference in row count. One on 2012, and the other on 2016. Indexing is identical. These VMs are in exact same environment with upgraded OS and SQL Server versions.  Same number vcores, same memory, same server settings (max degree of parallelism = 8, cost threshold for parallelism = 30).

This drop dead simple query for single record using a single column for filter and single column return. The column in the where filter is the sole column in the index.

2016 version has 8254356 rows
2012 version has 8254427 rows

They are the same queries.  2016 is missing the index and doing full table scan for no apparent reason.  2012 does a RID lookup (Heap) on the table after the index scan.

I tried WITH (index = CONTACT_RC_NUI1) on 2016 server and the cost jumped from 991 to 1889. On 2012 the cost was 29.

I tried adding AND 1 = (SELECT 1) and that made no difference.
I tried removing parameter sniffing as a possible problem by using OPTION (RECOMPILE) and that made no difference.

The DBA ran index rebuilds after restoring the database. Both servers had fairly recent index stats updates (we run Ola's index update script).  And to be sure I rebuilt the index on 2016 which had no effect on the 2016 explain plan.

I added hint below in the query...

select address1_stateorprovince
from dla.dcrm.CONTACT_RC WITH (index = CONTACT_RC_NUI1)
where wv_partyid = 343083;

This resulted in taking cost from 991 to 1889 even though it shows almost identical to 2012 explain plan (2016 just added parallelism (gather streams)).

What 2016 appears to be doing is costing the index for only 1% but the RID lookup is 99%.  In 2012 this was reversed. It appears 2016 used the index to scan all the entries and then looked up every RID in the table?  Could that be true?  I think 2016 optimizer has been smoking something seriously strong.

wv_party_id is nvarchar(100)


texts: CREATE TABLE TEXTIMAGE_ON [PRIMARY] is redundant it there's only one filegroup?

texts:
I've recently generated scripts for a legacy database, and discovered that most of the tables were created with TEXTIMAGE_ON [PRIMARY], but there's only one filegroup.

TEXTIMAGE_ON [PRIMARY] is redundant when there is only a single filegroup, correct?


texts: Is maintaining a single database an issue when the underlying project becomes larger?

texts:
I have seen this related post but it was more focusing on the amount of consumers for the databases, which is not my only problem.

I have a situation, where I am taking part of the design of a new data model implying a lot of relationships across tables and where different users would not be allowed to access all the data in the DB.

At the moment the infrastructure has many databases, even many database servers, with references to each other which makes it chaotic (in my opinion), and I am trying to know whether my idea to make the new data model in a single database is good, or at least, not ridiculous.

These are the reasons why the current architecture was on multiple databases (not servers, I mean databases):

Possibility to easily handle the access rights of the users (with Windows authentication) at a DB level.
It makes it easier to restore databases if somebody makes a mistakes and deletes all the records in a table (the size of a unique DB would make the "rollback" (using backups) too lengthy and too difficult)
The fact that if a single database has a deadlock, then all the architecture is blocked (with multiple databases, at least the independent data is still accessible).
Storing all the data in a single database is less efficient when the amount of records gets very large.

For all these points, I have the following answers:

It is possible to handle security very well using schemas. It comes down to having a good data model where security and compliance issues are also included in the design.
Maybe it's easier, but if somebody is able to affect the database badly enough, then that user simply shouldn't have had the right to perform that action. This comes back to a good security policy design.
Indeed, if there is a server failure, this is an issue. My best answer is that we could have a secondary server replicating the first, ready to take over if the first one was to fail.
I do not think this is true, data storage management should allow the database server to address performance issues.

Moreover, I would say that having a model where all the references between tables is represented by a foreign key is a key element to maintain data consistency. Having reference ID in one DB coming from another DB is inefficient and dangerous.

That's my thoughts, but I'm no expert in  DB management and I would be extremely happy if somebody told me that I am wrong, or could help my add arguments to my cause.

I would like to add that I am discussing an infrastructure in SQL Server (the price of the required hardware is not really an issue, but I could not change the underlying technology).

I would also be extremely happy to know if some academic research has been done on the subject.


texts: creating a database scheme for storing information about an item, which option is better?

texts:
I have a database which stores information about various items. In particular, for each item I need to store the name, type and color. Each type can have a various amount of different colors. For example, I need to be able to ask 'what color can an item of type A have?'

I'm struggling to see how I should best structure my database. Each item has a color and a type, but I also need to capture the fact that each type has a unique range of colors available.

currently I have these ideas:

FIRST OPTION

items            (ID(PK), name, types_and_colors_ID (FK))
types and colors (ID(PK), type_name (FK), color(FK))
types            (ID(PK), type_name)
colors           (ID(PK), color_name)

SECOND OPTION

items            (ID(PK), name, type(FK), color(FK))
types and colors (type (PK FK), color (PK FK))
types            (ID(PK), type_name)
colors           (ID(PK), color_name)

THIRD OPTION

items            (ID(PK), name, type_color_id(FK))
types            (ID(PK), type_name))
type_colors      (type_color_id, color_name (PK), type_id (PK))


texts: Query execution time is random, need to identify the root cause

texts:
I have a query like below

select * from table1 where column2=123 and column3='B'

Now the issue faced by us is that this query is executing very slowing sometimes as seen in our performance tracking tool. Also from the knowledge of our application, we know that it gets executed 100s of time in a day, but we only see it being reported as slow 2-3 times a day.

When I run it in SQL Server Management Studio, it gets executed in almost 200ms consistently.

What I need to know is, what is the cause of such random slowness and what could be few strategies to identify/fix the root cause?

Also, note the table has only 3 columns.

Column1 - Primary Key, (Integer)
Column2 - Foreign Key, (Integer)
Column3 - Value, varchar(10)

Other important information as asked in comments.

Indexes on table - No index
Are statistics on this table updated? - No, how to do that?
Is there a possibility that query is blocked by
some data changes or data load? - There are chances that an edit
script might be running at the same time this query is running, but
the update script would not be updating the same row.
What is the transaction isolation level used in this DB? - Read Uncommitted.


texts: A slow query on MS-SQL-Server 2019 cannot be optimized

texts:
In the following thread I received some important and valuable information on how I can improve a slow query on MS-SQL-Server 2019.
However, that didn't do very much.
First I deleted all non-clustered indexes and replaced them with clustered indexex. With the clustered index, some queries seem to be at least a little faster.
First question: It always seemed to me as if MS-SQL had not used the non-clustered index at all because creating a non-clustered index did not improve performance. Is that possible?
I kept getting the tip from you that I should absolutely avoid data conversions when accessing the index.
I tried that in a view. In the following script, the date values are ALL nvarchars.
However, the script runs for 78 minutes! Without the SUBSTRINGs it doesn't run any faster.
Second question: Is there a reason why this script has been running for so long?
The following query is the old version of the same script, just with different tables, but the table structure and the amount of data is roughly the same:
The script from the Second Execution Plan runs for 7 minutes, although here "DATETIME date values" are linked with "NVARCHAR date values".
I don't understand why this script runs so fast, although it is obvious that type conversions must take place here when the index is accessed.
The latter query is then integrated as a view in the following script:
This insert runs for 29 minutes. Previously, on MS-SQL 2008, the same insert ran for 10 minutes.
In this script I also removed the data conversions when accessing the index, but that did not bring any performance gain at all.
This script always runs for about 30 minutes, no matter what I do, I can't get it any faster.
Third question: Do you see a bottleneck in the third execution plan?
After all, you can see in all three execution plans that clustered index accesses take place.


texts: Find the last record of group of overlapped records

texts:
I have a little dilemma with filtering out result.
The result and what I want to achieve is not very trivial and I didn't find an answer anywhere after extensive search.  So here is my problem:

I have a table: Query with columns
startIndex and endIndex (of type Int(20)) defining range

Here is example subset of our table

CREATE TABLE IF NOT EXISTS `query` (
`id` int(20) unsigned NOT NULL,
  `indexStart` int(20) unsigned NOT NULL DEFAULT '0',
  `indexEnd` int(20) unsigned NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `query` (`id`, `indexStart`, `indexEnd`) VALUES
(1, 1, 5),
(2, 4, 7),
(3, 7, 9),
(4, 12, 15),
(5, 15, 16),
(6, 16, 17);

+----+------------+----------+
| id | indexStart | indexEnd |
+----+------------+----------+
|  1 |          1 |        5 |
|  2 |          4 |        7 |
|  3 |          7 |        9 |
|  4 |         12 |       15 |
|  5 |         15 |       16 |
|  6 |         16 |       17 |
+----+------------+----------+

The output of query  should be

+----+------------+----------+--+
| id | indexStart | indexEnd |  |
+----+------------+----------+--+
|  3 |          7 |        9 |  |
|  6 |         17 |       17 |  |
+----+------------+----------+--+

I want to find the min max solution -
the last record of the first overlapping group.

Overlapping group should be defined by records that are  indexEnd >=  indexStart of another record in the group

In the example subset, I should get the record with id 3 when I execute the desired SQL query,

Because the previous records for a group are overlapping indexStart and indexEnd, and the record with index 4 have indexStart 12 which is greater than the previous record's indexEnd 9 so a gap between records is formed, so the record with id 3 is the last record of first overlapping group.


texts: How to support arbitrary column sorting in a database?

texts:
Our product has a "table view" feature that allows customers to display their data in tabular form.

That data is made up of form fields the customer creates, and thus there can be an arbitrary number of fields that aren't known ahead of time.

Right now, this is implemented in Mongo using an array of "column" objects. This works fine right now because we don't allow sorting by arbitrary fields.

What we would like to do is allow our users to sort by any columns, in any order.

For example, if a customer had fields f1, f2, f3, then we would like to allow them to sort by (f3 desc, f1 asc, f2 desc).

So far, the possible solutions I've thought of are:

index every field: I feel like that will have enormous storage overhead
create a collection for each customer: this keeps their collection small, making indexes not required until they have tons of data
combo: make a collection for each customer, and only index them when they pass a certain size

We are not 100% tied to using Mongo, so if there's a database that is capable of doing this naturally, we're open to switching.

Are these the only options in Mongo, or are there better suited databases for this task?

Also, if it's helpful, we are also using PostgreSQL as our canonical data store.


texts: Internal query on CDEF$ and CON$ very CPU bound

texts:
I am checking an application (Infor ERP LN) that loads a lot of data into an oracle 10.2 database. While checking one of the user sessions with SQL_TRACE and its output with tkprof, I found that the most time consuming query is one internal to oracle. (I assume it is an internal one since the application does not execute that query.) Here it is:

select c.name, u.name
from
 con$ c, cdef$ cd, user$ u  where c.con# = cd.con# and cd.enabled = :1 and
  c.owner# = u.user#

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     5901      0.09       0.09          0          0          0           0
Execute   5901      0.26       0.24          0          0          0           0
Fetch     5901    978.68     969.36          0   65955477          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    17703    979.04     969.71          0   65955477          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  HASH JOIN  (cr=11177 pr=0 pw=0 time=161966 us)
     86   TABLE ACCESS FULL USER$ (cr=7 pr=0 pw=0 time=897 us)
      0   HASH JOIN  (cr=11170 pr=0 pw=0 time=161253 us)
      0    TABLE ACCESS FULL CDEF$ (cr=11170 pr=0 pw=0 time=159825 us)
      0    TABLE ACCESS FULL CON$ (cr=0 pr=0 pw=0 time=0 us)

From what I see this seems related to cdef$ table that have 915386 records. The same number or records is in con$.

So, is this a normal behaviour? May I add an index on cdef$(con#,enabled)? Would that make any change?

Thank you very much.


texts: Postgresql 9.3 GROUP BY on an indexed column in 30m rows table taking a long time

texts:
I have a simple table which holds GPS data for various vehicles with 30 million rows.

         Column         |            Type             | Modifiers | Storage  | Stats target | Description
------------------------+-----------------------------+-----------+----------+--------------+-------------
 id                     | bigint                      | not null  | plain    |              |
 equipmentid            | bigint                      |           | plain    |              |
Indexes:
    "some_pkey" PRIMARY KEY, btree (id)
    "some_idx" btree (equipmentid)

The table has more columns, but in my opinion they are not revelant for my question (but if you think they are let me know and I'll add them).

I'm trying to obtain the last GPS position for each equipment like so:

user=> explain (analyze, verbose, buffers) select max(gps2.id) from GpsData gps2 group by gps2.equipmentId;
                                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.56..131266837.64 rows=298 width=16) (actual time=64679.945..637667.147 rows=307 loops=1)
   Output: max(id), equipmentid
   Buffers: shared hit=5662655 read=11225117
   ->  Index Scan using some_idx on public.gpsdata gps2  (cost=0.56..131099834.76 rows=33399980 width=16) (actual time=35.452..634355.329 rows=29176300 loops=1)
         Output: equipmentid, id
         Buffers: shared hit=5662655 read=11225117
 Total runtime: 637677.321 ms
(7 rows)

As you can see this query runs in more than 5 minutes, which is an awful lot. It also seems to me that this is one of the most simple use-cases for a GROUP BY (i.e. I expect it to be much faster).

I'm trying to look at the analysis, but... I don't know where to begin. I mean it does say rows=29176300, which looks like a lot of rows, but still 5 mins seems excessive.

I should also note that I've forced an index scan via  SET enable_seqscan = off hoping it would be faster, but I was wrong (I had Seq Scan instead of index scan in my output before; it used to take around 4 minutes which is slightly less)

Any hints for me on where to start? My postgres version is PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit.

Edit: Some more info, as requested.

user=> select pg_total_relation_size('some_idx');
 pg_total_relation_size
------------------------
             1620033536
(1 row)


texts: Error 1304 when creating a procedure after a drop if exists

texts:
I have some php code that runs queries stored in a database to update some thousand client databases. Now, for the first time, I needed to create a stored procedure because the update is complex. I'm executing the following queries:

DROP PROCEDURE IF EXISTS updateProc100;
DELIMITER $$
CREATE PROCEDURE updateProc100()
  MODIFIES SQL DATA
  SQL SECURITY INVOKER
BEGIN
...
...
END$$
DELIMITER ;
CALL updateProc100();
DROP PROCEDURE updateProc100;

This code is executed the first time each client accesses the system, in its own database, and is executed only one time.

My problem is that I'm receiving error messages from a small number of client systems with the following MySQL error:

SQLSTATE[42000]: Syntax error or access violation: 1304 PROCEDURE updateProc100 already exists

No other procedures with that name exists in any of the databases and even if they existed, they should be dropped by the first statement. The number of error messages is somewhat low, but even so, I don't understand how this can be. I've manually checked each of the databases and no stored procedure exists.

I've googled about this problem, but all the answers that I find say to do what I'm already doing, executing a DROP IF EXISTS before the CREATE.

Does anyone have any idea for the cause of the error?
Thanks


texts: PostgreSQL - how to rename CamelCase column?

texts:
When I tried to rename a CamelCase column like this:

ALTER TABLE mytable RENAME COLUMN camelCaseColumn TO camel_case_column;

I get the following error:

ERROR: column "camelcasecolumn" does not exist

Surrounding the CamelCase column name with single quotes like this:

ALTER TABLE mytable RENAME COLUMN 'camelCaseColumn' TO camel_case_column;

gives me a syntax error.

Is there any way I can rename the column? Or do I have to create a new table and transfer the data?

The database is running on Ubuntu, and I am using Mac OS X to ssh into the server where the database is running on to issue the ALTER TABLE commands. I know that Mac OS X is case insensitive, so could this be part of the problem?

Thanks!


texts: What are the risks in running ALTER VIEW in a live production environment?

texts:
What risks do I need to consider if I want to run ALTER VIEW in a live SQL Server production environment?  Assume lots of long-running queries could be going on, both SELECTS and INSERT/UPDATE/DELETEs, but no ALTER TABLES or other schematic changes.  Is there any risk of disrupting users in the following cases?

Nobody is selecting from the view I'm updating, or anything that depends on it, but they may be updating data in tables the view uses.
Users may be selecting the from the view being updated immediately before or after the statement is run.  For example, if a user starts (A) a long-running SELECT on the view, I run (B) ALTER VIEW and it has to wait for (A) to finish, then a different user starts (C) another SELECT on the view.  Does (B) block (C) until (A) is completed?

If you need to know, the environment is SQL Server 2005 SP4 (9.0.5000), but please indicate if your answer is different for newer versions.


texts:
In a table, many rows have been deleted, how to get the id of missing rows for next INSERT?

For example

id      col1
1       1
3       3
4       4
5       5
8       8
9       9

how can I get the value of first available id for next INSERT. Here I want to get the id = 2.

Something like

SELECT id+1 FROM table WHERE CLAUSE // pointing to the least available value
// or x = id +1 (after SELECT)
INSERT INTO table (id, ....) VALUES ('x', ....)


texts: Cannot stop MySQL 5.5 replication threads

texts:
We have a master-master replication setup using MySQL 5.5. On one of the servers I am trying to stop the slave thread, but without success. To ensure that the replication does not connect to a real server, I set MASTER_HOST to a nonexistent address. I have run the STOP SLAVE command multiple times, but I am still seeing Slave_SQL_Running: Yes when I run SHOW SLAVE STATUS;.

This is what gets logged (every few seconds):

[Note] Error reading relay log event: slave SQL thread was killed
[ERROR] Slave I/O: Fatal error: Invalid (empty) username when attempting to connect to the master server. Connection attempt terminated. Error_code: 1593
[Note] Slave I/O thread killed while connecting to master
[Note] Slave I/O thread exiting, read up to log 'FIRST', position 4
[Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log '/var/log/mysql/mysql01-relay-bin.000002' position: 4

I want all the replication threads to go away. How can I do this?


texts:
Can you help me please with this problem? I need rewrite this simply trigger from sqlite to PostgreSQL format:

In sqlite:

CREATE TRIGGER connections_INSERT_update_connection_root_trg
                    AFTER INSERT ON dionaea.connections
                    FOR EACH ROW
                    WHEN
                            new.connection_root IS NULL
                    BEGIN
                            UPDATE connections SET connection_root = connection WHERE connection = new.connection AND new.connection_root IS NULL;
                    END;

I have tried this:

CREATE FUNCTION dionaea.connections_INSERT_update_connection_root_trg() RETURNS TRIGGER AS '
                    BEGIN
                            UPDATE connections SET connection_root = connection WHERE connection = new.connection AND new.connection_root IS NULL;
                    END
                    ' LANGUAGE plpgsql;

It is correctly accepted.

CREATE TRIGGER connections_INSERT_update_connection_root_trg
AFTER INSERT ON dionaea.connections
FOR EACH ROW
WHEN
 NEW.connection_root IS NULL
EXECUTE PROCEDURE dionaea.connections_INSERT_update_connection_root_trg();

After this I get this error:

NEW.connection_root IS NULL

Where is the problem please?


texts: SP for Restoring Database and performing other tasks on it

texts:
I am creating a stored procedure for our QA dept that will do the following:
Accept specific inputs to generate a consistent name, restore a database (giving it the generated name,) set the db up for replication (three merge publications), and then initialize the snapshots of these three publications.

Don't worry about the replication portion of this, I have it handled so far. What I am wondering is:

What will be the most effective/eficient method of restoring the database? Will it be restoring from a backup (the 2 DB's that they will be restoring from are 1.5gb and 599mb) or would it be another way? If it is not from the .bak files, what other way of restoring do you think would be appropriate?

:: This needs to happen, strictly, with TSQL ::

Thank you,
Wes

Here is the script I used


texts: What steps should I perform to prepare a MDF for distribution as a (mostly) read-only database?

texts:
I am on a team that develops a product that uses several SQL Server databases. Some of them are mostly read-only. Tables do not get changed, but some views and stored procedures get changed as a way of configuring the app. These views lack indexes. They are not materialized views. Changes to the tables only happen during upgrades and involve shipping the clients new MDFs that are attached. What steps should I perform to put these databases in a "ship ready" state? UPDATE Note that there is a LOT of free space in these MDFs, therefore a shrink followed by re-indexing.

We are doing the following currently:

Databases are in simple recovery mode (and we generally recommend keeping the original copy of the MDFs as the backups anyway)
We make the databases owned by sa and remove all logins (the deployment process adds appropriate logins)
We run DBCC SHRINKDATABASE on the databases
We run Ola Hallengren's Integrity and Maintenance scripts:

EXECUTE dbo.DatabaseIntegrityCheck
    @Databases = 'DB_IN_QUESTION',
    @CheckCommands = 'CHECKDB',
    @ExtendedLogicalChecks = 'Y',
    @TabLock = 'Y'
GO

EXECUTE dbo.IndexOptimize
    @Databases = 'DATABASE_IN_QUESTION',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @SortInTempdb = 'Y',
    @MaxDOP = 0
GO

Should we use other parameters for Ola's scripts? Should we take any other steps? Making the database read-only would be a pain because the views and stored procedures are often modified by the end user.


texts: Where in pg_catalog is the database or catalog information stored about relations?

texts:
When I query a table like information_schema.tables

SELECT
  table_catalog,
  table_schema,
  table_name
FROM information_schema.tables;

I get a result like this,

 table_catalog |    table_schema    |              table_name
---------------+--------------------+---------------------------------------
 myDB          | mySchema           | myTable

I can get table_schema, and table_name from pg_catalog.tables, but how would I get the table_catalog. Where is a the _catalog information stored in the pg_catalog schema?


texts: How to create shared folder between my on-premise Windows Server and Azure VM Server?

texts: I'm trying to migrate my on-premise SQL Server to Azure VM using 'Microsoft Data Migration Assistant'. But it is asking for shared folder to be accessed by both source and target. How to achieve this?


texts:
I have the following relation:

R = ABCDE
F: AB->DE, AC->B, DE->C, AE->C, C->AB

I need to determine this relations candidate keys.

Now, if I understood the defintion correctly:

  In the relational model of databases, a candidate key of a relation is
  a minimal superkey for that relation; that is, a set of attributes
  such that the relation does not have two distinct tuples

The candidate keys would be:
C, AB, AE, DE.

Is this correct or are there more?


texts: Is it possible to list column entry only once?

texts:
While this is not a huge deal, I'm just wondering if this is possible (I'm using MySQL in case that matters).

I have a query that returns something like this:

Basket      Contents
basket1     apple
basket1     orange
basket1     pear
basket2     lemon
basket2     banana
basket2     lime

Is it possible using only SQL to get this output?

Basket      Contents
basket1     apple
            orange
            pear
basket2     lemon
            banana
            lime

In other words, is it possible to list the column entry only once for each entry?  Thank you.


texts: MS SQL Server Error 18456 after database move

texts:
I have a windows service connecting to a SQL database. Recently, I was told to change the DB name in the connection string, all else was the same. When I attempted to connect, it threw Error 18456. With some research I found:

If I debug against the DB in visual studio on my machine, I get data
If  I use SQL Server Mgmt Studio v18.2 (newest version?) on my machine, I get
data
If  I run it in SQL Server Mgmt Studio 2008 on my machine, I do
NOT get data unless I run as Administrator

I am not SQL Server savvy and I don't know what the issue is or how to describe the issue to the DBA. Can someone help me identify the issue or give assistance in how to bring this to the DBA's attention? Thank you.

EDIT: I've installed the service code on a different server and it properly returns data. So this appears to be specific to the server and it's ability to get data from a SQL server. Additionally, both servers I am testing on have an operation which are able to connect to the Oracle. maybe this detail will spark some ideas? Unfortunately, I am not allowed to remote into the issue server as it is production, so I am unable to get much more info. Thanks again.


texts: What is the best way to include a large amount of configuration data in a SQL project?

texts:
I have a BIDS SQL Server Database Project with a database that I publish dynamically to various servers (SQL2016). It initially starts blank and everything is parameterized. Everything is in Azure DevOps and deploys to places.

I want to include/pack in (mostly static) configuration data for a few tables. Initially I used the script wizard to script the tables, then just put that script text in my post-deployment file (InitConfigs.PostDeployment1.sql).

I've come across one config table, when scripted, is a nearly 600mb script. The size isn't the problem, it just seems like there must be a proper/better way to embed it in the project.

Some things I've tried:

Export to flat file and embed somehow. - Seems messy and I have to figure out a way to import it in my post deployment script
Export single table bacpac - Doesn't seem to work because it tries to include entire database
Use Script wizard to generate a giant *.sql file. - I can add to the project and reference with SQLCMD :r. Seems odd.

Is there some sort of data package dacpac/bacpac-style file that can wrap everything up nicely and can be easily imported? Perhaps compressed too?


texts: mysql update based on select result, How combine?

texts:
Here are two SQL statements. One is an select statement and the other is a update statement. I want to combine them.

Select

SELECT * FROM test WHERE `id`='1'

Update

UPDATE test SET `select_count`=`select_count`+'1' WHERE `id`='1'


texts: SQL Server - Mirroring from 2014 to 2017 check unsent log status

texts:
I configured SQL Server Mirroring from SQL server 2014 standard to SQL Server 2017 Enterprise.

Steps I followed:

Create an endpoint on both servers with certificate authentication.
Dump and restored.
On secondary - alter db set partner ='TCP://primary:5022'
On Primary - alter db set partner ='TCP://secondary:5022'

Now the mirroring setup is done.

I know it's not officially recommended but using TSQL we can achieve this.

I can see the status of this mirroring is in Syncrozied state. But in sp_dbmmonitorresults its showing NULL.

Is there anyway that I can see the Unsent log/Unrestored Log status?


texts: What is SQL/PSM and how does it differ from other versions of SQL?

texts: I have searched SQL/PSM and I have learned that it stands for 'Structured Query Language/Persistent Stored Modules', but what I want to understand is what that really is and how it differs from T-SQL and any other versions of SQL?


texts: Can Oracle SE2 RAC works with only a single instance? Is it recommended?

texts:
I've got a client with an Oracle DB 12 SE2 RAC with two instances.

One of the instances is giving too much problems. He wants just to remove it, and therefore leaving Oracle RAC running in only with one node.

Is the client forced to convert its DB to RAC ONE NODE or to Single Instance?

Regards!


texts:
Sometimes when running SSIS packages on flat files, I receive error: "The process cannot access the file because it is being used by another process".

Another person already has file opened.

Is there a way for SSIS to manually close the file, so it can be processed?
I assume SSIS needs admin rights in the folder its processing.
I don't see "Close file" option in FileSystem task? Should I maybe use Powershell?

Please let me know of best options, I prefer SSIS first.

Thanks,


texts: Index SEEK is not used unless OPTION (RECOMPILE)?

texts:
(Question moved from SO)

I have a table (dummy data) with clustered index contains 2 columns :

Now I run those two queries :

declare
@productid int =1 ,
@priceid  int = 1

SELECT productid,
       t.priceID
FROM   Transactions AS t
WHERE  (productID = @productid OR @productid IS NULL)
       AND (priceid = @priceid OR @priceid IS NULL)

SELECT productid,
       t.priceID
FROM   Transactions AS t
WHERE  (productID = @productid)
       AND (priceid = @priceid)

The actual execution plan for both queries is :

As you can see , the first one is using SCAN while the second one is using SEEK.

However - adding OPTION (RECOMPILE) to the first query  , made the execution plan also to use SEEK :

Friends at DBA chat told me that:

  In your query, @productid=1, which means that (productID=@productID OR
  @productID IS NULL) can be simplified to (productID=@productID). The
  former requires a scan to work with any value of @productID, the
  latter could use a seek. So, when you use RECOMPILE, SQL Server will
  look at what value you actually have in @productID and make the best
  plan for it. With a non-null value in @productID, a seek is best. If
  the value of @productID is unknown, the plan has to suit any possible
  value in @productID, which would require a scan. Be warned: OPTION
  (RECOMPILE) will force a recompile of the plan every time you run it,
  which will add a few milliseconds to every execution. Though this is
  only a problem if the query runs very frequently.

Also :

  If @productID is null, to what value would you seek? Answer: there is
  nothing to seek to. All values qualify.

I understand that OPTION (RECOMPILE) forces SQL Server to see what actual values the parameters has , and see if it can SEEK with it.

But now I lose the benefit of ahead-compilation.

Question

IMHO - SCAN will only occur if a param is null .
That's fine - let SQL SERVER create an execution plan for SCAN.
BUT if SQL Server sees that I run this query many many times with values: 1,1 , then why doesn't it create ANOTHER execution plan and use SEEK for that ?

AFAIK - SQL creates execution plan for the most hit queries.

Why doesn't SQL SERVER save an execution plan for  :

@productid int =1 ,
@priceid  int = 1

( I run it many many times with those values)

Is it possible to force SQL to keep that execution plan(which uses SEEK) -  for future invocation ?

Full create table script + data


texts:
In SQL Server 2008 I am attempting to run the below calculation, but I am getting an error of

  Msg 207, Level 16, State 1, Line 11
  Invalid column name 'Rpt1'.
  Msg 207, Level 16, State 1, Line 11
  Invalid column name 'Rpt2'.

This is the syntax I am trying to execute.

  Declare @saleID varchar(100), @totalsales int

Set @totalsales = (Select COUNT(numsales)
                  FROM OPENQUERY(444.222.333.123, 'Select
                                                   numsales, saleID
                                                   FROM saledate')
                  WHERE saleID = @saleID)

Select
Rpt1 = count(case when empID = '1931' Then 1 else 0 end)
,Rpt2 = count(case when empID = '1831' Then 1 else 0 end)
,Balance = @totalsales-Rpt1-Rpt2
From totalsaleinfo
Where saleid = @saleid


texts: How to set ALL the replication to DEADLOCK_PRIORITY LOW?

texts:
I have just got this email here:

Then looking at the sql server log I find this deadlock was caused by one of our procedures (that was the victim) against the replication.

O.K. in this case in particular I noticed that this specific procedure could be tuned, I even found a missing index, as you can see below.

But this same situation has happened so many times, I would have had the procedure won, the replication was the victim, and I would deal with the procedure later on.

How can I put this:

SET DEADLOCK_PRIORITY LOW

everywhere, in every replication transaction?

this is to check it out:

    SELECT session_id,deadlock_priority
FROM sys.dm_exec_sessions
    WHERE SESSION_ID = @@SPID

How


texts: SQL fast in PSQL, very slow using MS.NET driver

texts:
There's thread with exact same title and problem, but with no answer, NPGSQL driver takes very long time to execute queries, but other language drivers or terminal works fine.

From what I've found, it's related to parameter sniffing in other SQL databases, but I could not find much for Postgres.

I'm running UPDATE statement, so it doesn't return many rows.

.NET Core 3.0, NPGSQL 4.1.1, PostgreSQL 10.9 (Ubuntu)


texts: MS Access Data Type for time in hh:mm:ss format

texts:
My database contains three different sporting events with finishing times imported from an Excel spreadsheet in the hh:mm:ss format.

How do I customize the Access data type field to accept this format?  Additionally, I then need to add three different event times per participant to create a total time, but need assistance in the format.


texts: MySQL table design / architecture, table is too big

texts:
I have a MySQL DB that contains a lot of text, I'm fetching data from a website and inserting it into a table.

I'm using a SSD HD (100GB) for the DB and I'm out of space, I think that something in the table structure made it too big, I can't predict the size of all the columns so I'm using varchar\text\medium text for most of the fields. when I insert all the data to the DB I monitor the errors and when I see that a certain field is too small for the data I'm trying to insert I'm increasing the size of the field (e.g. from varchar(1000) to varchar(2000)).

until now I have about 1.8M~ rows, I think that I'm doing something wrong.

here is the structure of my table -

CREATE TABLE `PT` (
  `patID` int(11) NOT NULL,
  `Title` varchar(450) DEFAULT NULL,
  `IssueDate` date DEFAULT NULL,
  `NoFullText` tinyint(1) DEFAULT NULL,
  `Abstract` text,
  `ForeignReferences` varchar(15000) DEFAULT NULL,
  `CurrentUSClass` varchar(2200) DEFAULT NULL,
  `OtherReferences` mediumtext,
  `ForeignPrio` varchar(900) DEFAULT NULL,
  `CurrentIntlClass` varchar(3000) DEFAULT NULL,
  `AppNum` varchar(45) DEFAULT NULL,
  `AppDate` date DEFAULT NULL,
  `Assignee` varchar(300) DEFAULT NULL,
  `Inventors` varchar(1500) DEFAULT NULL,
  `RelatedUSAppData` text,
  `PrimaryExaminer` varchar(100) DEFAULT NULL,
  `AssistantExaminer` varchar(100) DEFAULT NULL,
  `AttorneyOrAgent` varchar(300) DEFAULT NULL,
  `ReferencedBy` text,
  `AssigneeName` varchar(150) DEFAULT NULL,
  `AssigneeState` varchar(80) DEFAULT NULL,
  `AssigneeCity` varchar(150) DEFAULT NULL,
  `InventorsName` varchar(800) DEFAULT NULL,
  `InventorsState` varchar(300) DEFAULT NULL,
  `InventorsCity` varchar(800) DEFAULT NULL,
  `Claims` mediumtext,
  `Description` mediumtext,
  `InsertionTime` datetime NOT NULL,
  `LastUpdatedOn` datetime NOT NULL,
  PRIMARY KEY (`patID`),
  UNIQUE KEY `patID_UNIQUE` (`patID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

what should I do? I have about 20% of the data (which means I'm going to need 350GB~ space) what is the performance impact here? should I divide the table into several tables over several HDs? I'm going to use sphinx to index and query the data in the end.

should I partition the table and use another disk for the partitioned table? will it help to create several tables out of this one? what would be the best way of doing so?

most of the queries will be SELECT, I'm going to search on about 4-5 text columns in this table (full text search + Sphinx).

Thanks!


texts: PostgreSQL - Check value is not the same in multiple columns

texts:
I have a table to hold user information and is used as part of the login/registration process.
Part of the requirement is to allow the user to login using either their username or email address.
A snippet of the table is below and DB is PostgreSQL 12.4
CREATE TABLE account_users
(
    id            int          default nextval('postgres.account_users_id_seq'),
    username      varchar(100) not null unique,
    email         varchar(100) not null unique,
);

However this has raised a concern that the username field could be set to an email address of another user, which could cause a potential issue with users being able to login to their account as the SQL to get the users password from the database is:
SELECT password FROM account_users WHERE username = $1 or email = $1 LIMIT 1;

What I'm looking for is a way to ensure that the value in username and email are not the same for any row
For example:
username          | email
----------------------------------------------
user1             | user1@example.com - OK
user2             | user2@example.com - OK
user3@example.com | user3@example.com - Not OK
user1@example.com | user4@example.com - Not OK

Is this possible to do at the DB level or would it be something for the application to handle?
There is going to be validation done at the application level, client-side and server-side, however would a be nice to have if the DB could also catch this as well.