texts: Ola Hallengren Index Optimize - Skip "unused" Indexes

texts:
Is there a way to skip "unused" indexes based on the total reads of that index during an index maintenance w/ Ola's scripts?

I understand that

Index stats are only since the last restart, so monitoring over a business cycle is important
Skipping a reorg or rebuild on an "unused" index which may be needed later, even once, in the business cycle could cause performance issues
Everyone's thinking "why don't you just remove it"

To answer the latter, it's a vendor supplied application w/ an OLTP DB of ~2TB. To stay compliant, we can't change much and I can't drop their indexes among other things. Thus, I thought it'd be beneficial to reduce the maintenance time by skipping these indexes once I've confirmed they aren't ever used. This would be beneficial for situations where, like this, my hands are mostly tied.

Follow on for comments would be if this isn't able to be done, is it worth tossing to Ola as an enhancement?


texts:
I am working on an oracle database and want to record the exact time of any transaction happening on a specific table in the schema. Using triggers on that table is out of question as the client do not want to use it for unknown reasons, also it is not possible to alter the existing tables

Can anyone tell me if there is any other way to achieve this. I just want to record the time of the transaction and the ROWID of the inserted or updated row in a separate table.


texts: Getting the number of Saturdays and Sundays in a month

texts:
I am trying to get the number of Saturdays and Sundays for a given month represented by an end_date and a start date.

I have been trying to understand the algorithm in the SO question here . I have understood the matrix they have created but I don't get the algorithm that is being used, plus the thing is just the opposite of what is needed, and this thing is pretty new to me, mostly I do normal select, insert, update and joins in mysql.

It would be helpful to get some ideas on this issue.


texts: Why is MongoDB oplog size different between servers

texts:
I have a ReplicaSet running fine once I ran rs.stepDown() and looking for rs.status() I get fine logs.

Here is one as evidence:

rsdev:SECONDARY> db.printSlaveReplicationInfo()
source: lab:27017
    syncedTo: Mon Jul 17 2017 09:20:52 GMT-0300 (BRT)
    0 secs (0 hrs) behind the primary

I got one problem with my application that if I change the primary to the server X which used to be the secondary than I just can't use its oplog as I was when using server Y as primary.

I know that when I have  a new member in the ReplicaSet it may will have the dataSet size smaller than its primary due to its age, delete, insert and so on.

But should its oplog size be different between them?

rsdev:PRIMARY> db.getReplicationInfo().usedMB
40543.1

rsdev:SECONDARY>  db.getReplicationInfo().usedMB
21734.5

This secondary server is the new one which I get into problem if become primary.

Both using wiredTiger storage.


texts: Howto restore Trigger filesystem copy (*.TRG and *.TRN files) in addition to mysqldump?

texts:
We use mysqldump for backup (without Trigger/Events/Routines included).

mysqldump --opt --flush-logs --single-transaction --ignore-table=mysql.event --all-databases

The triggers we copy with cp to backup folder. We use Galera 5.5.41-MariaDB-wsrep.

After restoring the mysqldump, howto restore the Triggers? Simple cp or are other tasks necessary?

# find . -name *.TRG
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/user_login.TRG
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/conversation.TRG
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/conversation_participant.TRG
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/accounttovacancyrelation.TRG
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcomment.TRG
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/user.TRG
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcommentlike.TRG

# find . -name *.TRN
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/trg_conversation_participant_insert.TRN
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/trg_conversation_update.TRN
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/trg_accounttovacancyrelation_update.TRN
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/trg_user_login_insert.TRN
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/trg_accounttovacancyrelation_delete.TRN
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/trg_accounttovacancyrelation_insert.TRN
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/user_AFTER_DELETE.TRN
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcommentlike_AFTER_INSERT.TRN
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcommentlike_AFTER_DELETE.TRN
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/user_AFTER_INSERT.TRN
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcomment_BEFORE_DELETE.TRN

example content of files

# cat ./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcomment_BEFORE_DELETE.TRN
TYPE=TRIGGERNAME
trigger_table=eventcomment

# cat ./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/user_AFTER_INSERT.TRN
TYPE=TRIGGERNAME
trigger_table=user

# cat ./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcomment.TRG
TYPE=TRIGGERS
triggers='CREATE DEFINER=`test`@`%` TRIGGER `DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED`.`eventcomment_BEFORE_DELETE` BEFORE DELETE ON `eventcomment` FOR EACH ROW\nBEGIN\n    DELETE FROM eventcommentlike where eventcommentlike_comment = OLD.eventcomment_id;\nEND'
sql_modes=0
definers='test@%'
client_cs_names='utf8'
connection_cl_names='utf8_general_ci'
db_cl_names='latin1_swedish_ci'


texts: What is the primary key on a temporal table?

texts:
If I use the following table design to capture history...:

CREATE TABLE MyTable (
    insertion_timestamp TIMESTAMP,
    deleted_flag BOOLEAN,
    natural_key INT,
    attribute VARCHAR
);

... then what should the primary key be?

(The history mechanism is INSERT-only: updated rows are inserted with a different insertion_timestamp, and deleted rows are inserted with a different timestamp and the deleted_flag set to true.)

I'm thinking PRIMARY KEY (insertion_timestamp, deleted_flag, natural_key), but the only reason for including deleted_flag is to account for the possibility of a row being inserted and then (soft) deleted immediately, sooner than the next tick of the granularity of the TIMESTAMP data type. This feels overly paranoid...


texts: How many log files does a database usually have?

texts:
I'm studying SQL Server. I have read that a database can have several data files and one log file, but recently I was heard that a database can have more than one log file.

Is this true?


texts: SQL Server Tuning a table for massive inserts

texts:
I'm trying to solve a performance issue and I need advice.

I have a table, which about twice a day has a massive surge of inserts coming in, and exactly at the same time everybody is trying to read from it. So I have locks and everything becomes painfully slow.

My performance requirements are as follows:

many writers, from different sources, and i prefer each write to be atomic.
i don't care about the order of the writes, number 7 can come in before number 3.
writes should end ASAP, I want writers to wait the least possible amount of time.
data is never updated, only inserted.
I have many many readers.
readers want to see the most updated snapshot possible at the time of their read.
I can live with readers seeing delayed data (10-20 seconds)
I need occasionally do complex and large queries on this data, so i need several indexes on it.

How would you approach this, and how would you implement this with SQL Server?

I'm thinking of splitting it into 2 tables... one for the inserts, and one for reading, and a worker moving data.

the large queries will run only on the second, the readers that need updated data, can run on the second, and complete what they need from the first.


texts:
I have two DB2 tables.

Table 1, called EmployeeFile

EmployeeFile has the following columns: EMP_ID, FNAME, LNAME, DOB

The data looks like this:

   EMP_ID, FNAME, LNAME, DOB
 - GB2342, John,  Smith, 1970-12-10
 - L3243C, Jane,  Jones, 1969-11-09

Table 2, called OriginalData

OriginalData has the following columns: INTERNAL_ID, FNAME, LNAME, DOB.

The data looks like this:

   INTERNAL_ID, FNAME, LNAME,    DOB
 - 6575678588,  Jane,  Jones,    1969-11-09
 - 1232145564,  Rob,   Mitchell, 1968-10-08
 - 3452345345,  John,  Smith,    1970-12-10
 - 6786478568,  John,  Smith,    1970-12-10

I want to show all rows of EmployeeFile for which the (FNAME, LNAME, DOB) combination occurs only once in OriginalData.

For example, if I ran the query on the example dataset shown above, it should return just the following row:

Jane, Jones, 1969-11-09

I don't want 'John Smith' because although he exists in EmployeeFile, his (FNAME, LNAME, DOB) combination occurs twice in OriginalData.

I don't want 'Rob Mitchell' because he doesn't exist in EmployeeFile.

Question

How do I do this?

The only way I can think of is immensely ugly: Concatenate (FNAME, LNAME, DOB) (with some delimiter like ~) from both tables, do a join on that column, do a group by having count(that concatenated column) = 1, then do another select on EmployeeFile, selecting only those rows where the (FNAME, LNAME, DOB) match the parsed substrings of that concatenated string!


texts:
I want to insert bulk data into a MongoDB.
Later on I want to find exactly the bulk I inserted.

In MySQL I would have done:

 SELECT * FROM data WHERE id >= start_id AND id <= end_id

How can I fix this in MongoDB?


texts: Necessary to have separate index for lookups against one of a multi-column PK?

texts:
Sorry if this is a FAQ, but I'm having problems turning up exactly what I'm asking:

I have a table (mysql) with a primary key which is composed of multiple columns only because this is necessary to make the PK unique (won't be using an autoincrement column).

However, most lookups will be against just the first column in the primary key.

So should I have a separate index only containing that single column, or is the multi-column PK sufficient (at least as long as the query column is listed first in the index definition)?


texts: Wrap identifiers automatically with brackets in SSMS text editor

texts:
Working in SQL Server Management Studio, when using the text editor:

Is there any way to automatically wrap schema names, table names, column names and other identifiers with brackets so I don't have to put them manually, just in the same way it does it when it auto-generates queries? Is there (perhaps) any Intellisense feature I'm missing?

EDIT: see accepted answer


texts: I am trying to represent the value of gender field with 'male' if 1 and 'female' is 0

texts:
Need to convert gender field has data type as bit.

I used this syntax:

Select case when CAST([GenderIsMale] AS bit)=1 then 'Male'
when CAST([GenderIsMale] AS bit)=0 then 'Female'
else  CAST([GenderIsMale] AS bit)
end

Returns the error:

  Conversion failed when converting the varchar value 'Male' to data type bit.


texts:
I need that all my system users can send email from the DB.
not to assign everyone the role in the msdb database I created one login/user "send_mail", and grant permission on msdb db, specifically DatabaseMailUserRole.
I need that all my users send email execute a store procedure that within execute the statement execute as user='send_mail" before of msdb..sp_send_db_mail.
The user send_mail have db_owner, and the DatabaseMailUserRole, but can't send email.
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.
Anyone can help me please?


texts: Is it possible to replicate to a different database name using SQL Server Alwayson Availability Groups?

texts: Is it possible to replicate to a different database name using SQL Server High Availability Groups?


texts:
An MDX (a language dealing with multidimensional data, in particularly dealing with the cube metaphor) statement can be translated to an SQL (a well-known language for the relational model) statement - Mondrian is one example solution which provides such translation.

I was wondering if there are any available solutions to provide a translation in the other direction, i.e. from SQL to MDX. If not, what would be involved in creating such a functionality?


texts: Oracle's Unusual Behaviour: Either database not opened or connected to an idle instance on startup

texts:
I have been getting this issue multiple times whenever I am trying to connect to oracle db.

  Connected to an idle instance error

This was because the database din start correctly

  Database not started error even though connection has been made

This was because the database failed to shutdown.

Although, I was able to resolve this via startup command to mount the db again on connecting to an idle instance but why this unusual behaviour on switching off the oracle service after all the tasks have been performed as OracleServiceXE consumes a lot of Ram memory as well as disk.

Is there any way I could stabilize the connection each time the service gets closed and started because I run eclipse with oracle and I have only a 4gb ram so even if i start these services seperately there is 100% of disk as well as ram utilization.


texts: I currently have a maintenance plan that is reorganizing indexes everyday and I would like to know if you reorganize an index everyday would you ever need to rebuild the index? If so, why?


texts: Modeling staff dimension in data warehouse

texts:
I need to write a data warehouse diagram. I've got a problem with staff area. I have to store a information about workers details like name, age etc., and workers job time and agreement details like job_start, job_end, salary etc.

Firstly, I tried to draw a dimension for each data, but I consider whether it should be done like a connection between these dimensions each other?


texts:
I have linked server connection named MMSHO. There is a parameterized function in Oracle server where I want to send store codes by OPENQUERY then filter in where condition. :

        DECLARE
       @OPENQUERY   NVARCHAR (MAX),
       @STOREID     VARCHAR (2),
       @ARTICLEID   VARCHAR (10),
       @SQL         NVARCHAR (MAX)
    SET @STOREID = 10;
    SET @ARTICLEID = 245511;
    SET @OPENQUERY =
             'SELECT *
              FROM OPENQUERY (
                      MMSHO,
                      ''SELECT MS000'
           + @STOREID
           + '77TRP.MW070K01.MW070P01_REKE@MS000'
           + @STOREID
           + '77TRP(ART_NR, VAR, GEBI_NR) AS ARTICLE_NNBP
                                    FROM MS000'
           + @STOREID
           + '77TRP.MW_MA_VAR_GEBI_SNAP@MS000'
           + @STOREID
           + '77TRP
                                    WHERE SUBSYS_ART_NR = '
           + @ARTICLEID
           + '''';

    SET @SQL =
             'SELECT STORE_NO,
           SUBSYS_ART_NO,
           ARTICLE_NO,
           ARTICLE_DESC,
           PUAR,
           SUPPLIER_NO,
           SORTEN_TEXT AS ARTICLE_VARIANT,
           GEBI_NR,
           BLOCK_CD,
           PACKAGE_CONTENT AS CONTENT,
           PACKAGE_TYPE AS CONTENT_TYPE,
           VAT AS ARTICLE_VAT,'
           + CHAR (13)
           + '('
           + @OPENQUERY
           + ') AS ARTICLE_NNBP,
           ARTICLE_NNSP,
           STOCK_QTY AS ARTICLE_STOCK,
           CASE WHEN DEPT_NO IS NULL THEN 0 ELSE DEPT_NO END DEPARTMENT
           FROM ARTICLE
           WHERE STORE_NO = 10 AND SUBSYS_ART_NO = 245511'

    PRINT (@SQL)

When I run the above query with EXEC (@SQL), SQL Server throw an error message which is :

Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'CASE'.
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near 'DEPARTMENT'.

I have tried to remove the lines between OPENQUERY and FROM keyword but no success. What am I doing wrong? Could you please help me?

PRINTED QUERY

SELECT STORE_NO,
       SUBSYS_ART_NO,
       ARTICLE_NO,
       ARTICLE_DESC,
       PUAR,
       SUPPLIER_NO,
       SORTEN_TEXT AS ARTICLE_VARIANT,
       GEBI_NR,
       BLOCK_CD,
       PACKAGE_CONTENT AS CONTENT,
       PACKAGE_TYPE AS CONTENT_TYPE,
       VAT AS ARTICLE_VAT,
       (SELECT *
          FROM OPENQUERY (
                  MMSHO,
                  'SELECT MS0001077TRP.MW070K01.MW070P01_REKE@MS0001077TRP(ART_NR, VAR, GEBI_NR) AS ARTICLE_NNBP
                                FROM MS0001077TRP.MW_MA_VAR_GEBI_SNAP@MS0001077TRP
                                WHERE SUBSYS_ART_NR = 245511') AS ARTICLE_NNBP,
       ARTICLE_NNSP,
       STOCK_QTY AS ARTICLE_STOCK,
       CASE WHEN DEPT_NO IS NULL THEN 0 ELSE DEPT_NO END DEPARTMENT
       FROM ARTICLE
       WHERE STORE_NO = 10 AND SUBSYS_ART_NO = 245511


texts:
MSSQL database has previously been archived on another database. Approximately 70m rows (includes sales information.)

Now, I am asked to move this archived data to live database. Because, customer can't see order history older the 3 years.

Is it reasonable to do this? I think it will be slow. Are there other solutions?

Live DB size : 250GB
Archive DB size : 50GB
CPU : 8 Core
Memory : 64GB
Version : Microsoft SQL Server 2016 (SP1)


texts: Are there advantages to using temporary tables over derived tables in SQL Server?

texts:
I read the derived tables have better performance than temporary tables, but anyway many SQL Server developers prefer the second ones. Why?
I must do queries with large data (millions records) and I want to be sure I am using the best choice.

CREATE TABLE A(
    id BIGINT IDENTITY(1,1) NOT NULL,
    field1 INT NOT NULL,
    field2 VARCHAR(50) NULL,
);

CREATE TABLE B(
    id INT IDENTITY(1,1) NOT NULL,
    field1 VARCHAR(10) NULL,
    field2 INT NULL
);

INSERT INTO A
    (field1,field2)
VALUES
    (1,'a'),(2,'b'),(3,'c'),(2,'d'),(5,'e'),
    (6,'f'),(7,'g'),(8,'h'),(9,'i'),(2,'j');

INSERT INTO B
    (field1,field2)
VALUES
    ('a',1),('b',2),('c',3),('d',4),('e',5),
    ('f',6),('g',7),('h',8),('i',9),('j',2),('k',3);

DECLARE @begin INT=0,@end INT=200;

Derived tables

/*derived tables*/
SELECT
    C.id,C.field1,C.field2,C.field3
FROM
(
    SELECT
        A.id,A.field1,A.field2,B.field2 AS field3,
        ROW_NUMBER() OVER (ORDER BY A.id) AS iRow
    FROM
        A INNER JOIN B ON A.field1=B.id
) C
WHERE iRow BETWEEN @begin AND @end;

Temporary tables

/*temporary tables*/
CREATE TABLE #C (
    iRow INT IDENTITY(1,1),
    id bigint,
    field1 INT,
    field2 VARCHAR(50),
    field3 INT );

INSERT INTO #C
    (id,field1,field2,field3)
SELECT TOP 1000
    A.id,A.field1,A.field2,B.field2
FROM
    A INNER JOIN B ON A.field1=B.id
ORDER BY
    A.id;

SELECT id,field1,field2,field3
FROM #C
WHERE iRow BETWEEN @begin AND @end;

DROP TABLE #C;


texts: Join tables in two databases make query slow? is better to partition db?

texts:
I have a table1 in db1 and table2 in db2 on SQL Server 2008 R2.

If I make a select query joining both tables, it's really slow to get the results.

A simple query like

SELECT *
FROM db1.dbo.table1 t1
LEFT JOIN db2.dbo.table2 t2 ON t1.k1 = t2.k2

is REALLY slow sometimes.

I not sure if this is common to SQL Server and "have to be like a rule" to "not join two tables that are from different databases".

In that case... I add to this question that I have one of those db binary data stored on a field and I like to separate from main db to not increase main table size... it is better to partition a db for this?

I tested with two simple tables and still joining those two tables is slow.

Thanks in advance for any help.

..Update some years later... 24-09-18

be sure that the fields that you are joining are of same type, size and collation.

Example: some property is varchar(255) and another varchar(20) ... that can be a problem because the engine have to cast one type to another (an implicit conversion occurs) and while sometimes it runs faster... if a reindex or some change in the db occurs, you can see that in some moment the query start to took a lot more time to complete....

If you cannot change the field type to match in one of the db/tables, try doing an explicit cast to see if that improves the query speed.
use
cast(fieldname as type(size)) = fieldName2)


texts:
I installed mariadb 5.5.36 on a win32 system. Everything runs fine except innodb requests are 2 x slower than an identical MySQL installation. So I want to switch back to original innodb plugin to test the performance with it.

In my.ini I put under [mysqld] the following:

ignore_builtin_innodb
plugin-load=innodb=ha_innodb.dll

The plugin is located in BASEDIR/lib/plugin.

But when I start the service I get the following in the error log:

140315 18:03:04 [Note] Plugin 'FEEDBACK' is disabled.
140315 18:03:04 [ERROR] Plugin 'InnoDB' init function returned error.
140315 18:03:04 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140315 18:03:04 [ERROR] Unknown/unsupported storage engine: InnoDB
140315 18:03:04 [ERROR] Aborting

If I comment both lines out then everything runs fine.

Even when I try to run a plain MariaDB data dir (without the log files ...) the error occurs.

Is there someone who have a hint for me?


texts: SQL Join too slow on postgresql 9.0 vs postgresql 9.1

texts:
There is a big difference in execution time, altough the ammount of table data are pretty much the same in both databases.
On production DB - Postgresql 9.0 DB hosted by webprovider it takes 29seconds, versus of Postgresql 9.1 installed on virtual machine.

Also seems when I remove the second condition in ON section it runs fast but when there are both ON conditions it runs super slow.

a) I've tried to change type from varchar(255) to text and vice versa for type reference_number_1 so it would match document_number type varchar(255).

b) Tried to REINDEX command for both tables

SELECT dro.document_number, dro.document_year, dro.document_date, dro.process_id
FROM data_recent_orders dro
LEFT JOIN data_tracking_system dts
ON
dts.reference_number_1_metaapp = dro.document_number AND
dts.reference_number_1_year_metaapp = dro.document_year
WHERE
dro.document_type = 'Sofortrechnung'
LIMIT 10000;

Postgresql 9.0

Postgresql 9.1


texts: Processor queue length but not very high CPU usage

texts:
I've a virtual server running SQL Server 2016 (4 vcores) where most of the time there is a processor queue length of 4 (sometimes it goes up to 15) but the CPU is using 25% on average. There are about 3000 batches/sec.

Using a query found in an article of Glenn Berry found on SQLSkills.com I found out that there is an avg_task_count of 15 and a avg_runnable_task_count of 2 (but not constantly) :

SELECT AVG(current_tasks_count) AS [Avg Task Count],
AVG(work_queue_count) AS [Avg Work Queue Count],
AVG(runnable_tasks_count) AS [Avg Runnable Task Count],
AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count]
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255 OPTION (RECOMPILE);

Is it correct to say that the processor queue length and the avg_task_count are an indication there are a lot of requests and the server has problems to process them? But what I don't understand is why isn't the cpu usage higher?

The physical host has a 25% CPU usage, there is no memory reservation but there is no memory over-commitment on the host.


texts:
I've been looking at getting some disk space back on some of our development machines.
These are machines that are not being backed up, not being developed on directly, and not being maintained particularly well.
Any recovery is done from PRD or PRE-PRD to the DEV environment, nothing on this particular DEV environment is critical.

The recovery models on these databases has to remain on FULL because it was so ordained (despite no actual backups being taken).
As these databases in some cases have been around for quite some time without backups, I'm looking into a proper way to shrink their logs.

I've found two approaches that might serve the purpose:

BACKUP LOG [databasename] TO DISK=’NUL:’

Followed by shrinking the log.

Or:

ALTER DATABASE [databasename] SET RECOVERY SIMPLE;
ALTER DATABASE [databasename] SET RECOVERY FULL;

Followed by shrinking the log.

My actual questions:
What are the differences between these two approaches?
Is there something I'm missing / overlooking?


texts: I couldn't see Conflict tables in replication monitor SQL Server 2008 R2 in Merge Replication

texts:
I work with merge replication in SQL Server 2008 R2. In the replication monitor I can't see conflict table, I must to see conflict tables in "MSmerge_conflicts_info", please help me.

How to see conflict tables in replication monitor? Thanks


texts: Removing Replication from MS Access mdb file using External Data->Access, is anything lost?

texts:
The new version of MS-Access 365 does not support replication as evidenced by the following error message:

  "The current version of Microsoft Access does not support replicated
  databases. To use this database, open in a previous version of
  Microsoft Access"

Our company uses MS-Access for front-ends to backend databases, and thus I must remove replication from some of these databases.

I've had luck doing this by opening a new blank .mdb file in MS-Access, and clicking External Data->Access from the ribbon bar.  From there it appears that I'm able to import the tables into a clean database and delete the unnecessary replication fields.

Next I select the old .mdb file with the replication in the Get External Data dialog box, and elect to import the data into the database rather than to link  it.

On the Import Objects screen, I select just the tables that do not start with MSys*, all queries, all forms, all macros and modules.

Then I make sure that Relationships, Menus and Toolbars, Import/Export Specs and Nav Pane Groups are all checked;

I select the Definition and Data radio button and the As Queries radio button.

And finally click Ok.

This seems to work well enough; I was not here when the databases were implemented, and there's rather sparse documentation about the system; so I was wondering if there is anything else I need to import before proceeding with these de-replicated tables in production.


texts: Oracle 11g displaying ORA-01034, ORA-27101, and Linux "error 2" messages.

texts:
When I try to connect to Oracle 11g using:

sqlplus noah/noah@orcl

I get these errors:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

The service does start up when I connect as sysdba using:

sqlplus /nolog
conn / as sysdba
startup

Here's my listener.ora:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
  (GLOBAL_DBNAME = orcl)
  (ORACLE_HOME = /opt/oracle/112)
  (SID_NAME = orcl)
)
)

LISTENER =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = mylinux)(PORT = 1521))
)

And the tnsnames.ora:

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mylinux)(PORT = 1521))
(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = orcl)
)
)

Here is the sqlnet.ora:

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES,HOSTNAME)

Could anyone give me a hand making this work?


texts: Query to filter out the duplicate and pick the latest

texts:
I'm trying to filter out duplicates and just get the latest of the form name based on the the higher number, no dates on the table:

Form Name
abc
abc-01
abc-01
abc-02
abc-02
abc-03
def-01
def-02
xyz

And I would like to get this result:

Form Name
abc-03
def-02
xyz

What is the query to filter that out? Any suggestions? Thank you!


texts:
Well I am just a db beginner, I want to design something like this

I want to keep simple as possible, well I will enter all values[Cities, Distance etc.,] manually, because my cities are limited.
And also I want to Query like 'oneCity' Between 'AnotherCity', will yield the cities between them.

Any Advice, solution, concepts, technique or tutorials are appreciated. I googled a lot, but no solutions are appropriate :(

And also I don't know this is the right place to post, if this is a wrong place please ignore my mistake.

Thanks.


texts:
I don't quite know how to formulate the question because we don't know much yet, but I would like to ask sooner than later because this looks like something that should not be neglected.

This week we started having issues with our database server. It appears to be a data consistency problem and it manifests by timeouts even on very simple queries and small tables. We "fixed" the problem by restarting the server earlier this week and it went away, but now it seems that it is coming back and this time on more crucial tables. For instance, I just did some investigation and I'm looking at a query like this:

SELECT * FROM table WHERE id = 1234

for a particular ID. The table has about 30+ million rows. But it seems that it happens only for a small fraction of records. I bet when I restart the server or backup and restore the database on another server, it will all be fine. But I will try.

At this point, I'm running:

DBCC CHECKTABLE ('table', NOINDEX)

but it seems it's going to run forever. When we hit the issues the first time, I checked the offending table and it was fine. This new table is much bigger.

Some background technical information:

SQL Server 2008 R2, Windows Server 2008 R2
AWS/EC2, m2.2xlarge, 32 GB RAM, 4 x 1TB RAID 0
almost no disk IO, it seems that most of the db is in memory
total db size: 100GB

The ELB volumes are "brand" new. We created them this week.

Edit: I just used the following command:

SELECT
    sqltext.TEXT,
    req.session_id,
    req.blocking_session_id,
    req.wait_type,
    req.wait_time,
    req.last_wait_type,
    req.wait_resource,
    req.open_transaction_count,
    req.transaction_id,
    req.total_elapsed_time
FROM
    sys.dm_exec_requests req
CROSS APPLY
    sys.dm_exec_sql_text(req.sql_handle) AS sqltext

and found that my query it waiting for a shared lock (LCK_M_S) where the blocking session is waiting for another shared lock blocked by a session that does not exist.

Edit 2: OK, the session exists (I found it using sys.dm_exec_sessions), but it does not appear to do anything now.

Edit 3: I can't find anything interesting about the session. I see what web server it comes from, but not much else.

Edit 4: Edit 4: We found a possible bug in our code: a function that was not making sure a database connection is closed. On the other hand, it looked like the transaction the function was using used to be correctly disposed in which case all locks should have been cleared. It's still not very clear to me, but it seems to be the likely reason. We are going to fix the bug and and keep an eye on it.


texts:
I was going through the examples of deadlock exceptions online and found this link

As I understand, here we have two processes (session 1 and 2). The first one acquires lock on pd1, and second acquires on pd2. Then after that, first one want to read from pd2 and second one from pd1.

What I dont get is that why do we have a deadlock here. Wouldnt it work in a way that session updates and once it is done updating, it will release the lock on pd1, and with that session 2 will be able to read from pd1. Similarly the other way round? Why does the Update + Select has to be a single operation here?

Is it because they are part of the same transaction, hence within a transaction they wont let go of their locks acquired on any object?


texts: Scheduled optimization of tables in MySQL InnoDB

texts: What is the best way to schedule automatic optimization of tables in a MySQL InnoDB database? Can I use events for example? I have recently had a big performance issue (when querying) with one of the tables which is actually the largest one in my database and frequently being updated. After I run OPTIMIZE on the table, it has solved the problem.


texts: creating a report on the mysql jasperReport server

texts:
I have mysql local database, in it I have sample data mart. I have installed jasperReport server to my computer. Just using this tool, I want connect to the local mysql database and just create a pdf report or any report without using any tool except jasperReport server. Can I do ? HOw?

Note; report will be directly constructed on the whole data reside in the mysql database. (No filtering) I want to see the report on the jasperReport report page. If any change occur on the mysql database, it should be carried out on the report page of the JAsperReport server


texts: Using Oracle XE, how do I create a CASE statement To return a specific date

texts:
I am Trying to create a view based on a business calendar table called Business Payment Calendar view with two new calculated columns:

Payment Warning Date (Payment_Warning_Date)
Payment Due Date (Payment_Due_Date)

And, also the non-calculated Transaction_Date

Those dates are based on the number of BUSINESS days from the transaction date from the business calendar, 7 and 10 business days. The Business Calendar table contains columns populated for the year.

CAL_Date (which is our datestamp),
Day_Name,
Month_Name,
Cal_Year,
Month_Num,
Weekend (True/False),
Holiday (True/False),
Bus_Working_Day (True/False).

So for example, say you have a purchase today, the output will be:

Transaction_Date: 6/26/2017
Payment Warning Date: 7/6/2017
Payment Due Date: 7/11/2017

Figure we have the weekend coming up, next Monday (July 3) is a work day, but Tuesday (July 4) is a US holiday, then 3 more work days.

I'm just not having any luck building a case statement that properly takes into account the Bus_Working_Day field for SYSDATE+7 and +10 and then return the correct date.


texts: pg_replication_slots: how to determine how far behind a consumer is?

texts: Given the replication api exposed in postgres 9.6.6, for a given replication slot and a consumer, what system catalogs or view could inform how far behind the consumer was the latest state of the wal?


texts: Get postgresql exact log file name with sql

texts:
I get current log file by using SQL without getting in server via ssh. So I use a query like the one below;

SELECT pg_read_binary_file(
  concat_ws('/',
    current_setting('log_directory'),
    'postgresql-Fri.log'
  )
);

Log files are not too big so i am fine with that. The thing is, I cannot use this query in a generic way. Is there a way to get 'postgresql-Fri.log' from my log_filename setting which is currently postgresql-%a.log but the format can differ?


texts: List of open (uncommitted transactions) in DB2 LUW

texts:
How can I get list of open (uncommitted transactions) in DB2 LUW?

Or just the oldest transaction id?

Basically, I need to get the first change of the oldest open transaction. I found APPL_ID_OLDEST_XACT in SYSIBMADM.SNAPDB view, but it tells me only the application id and I could figure out how to get to the actual LSN/LRI.

I was suggested to use db2pd -transactions, but I can only run it from server side.
Is there a solution that I can run from client side?


texts: Suppress specific SQL Server log warnings and messages

texts:
I want to minimize the output of certain warnings and messages in the SQL Server 2012 logs, such as when an spid is killed. I am unable to find information on suppressing/expanding which messages are actually sent to the error logs. Is this done through trace flags?

I am trying to reduce the number of warnings on our development database server and restrict it to legitimate errors. We have devs killing spids or flushing the query cache for performance tuning (playing with index tuning, forcing intentional table contention, creating deadlocks, etc), across multiple databases on the same server. Having a log file filled with these warnings is just an annoyance that I wanted to see if it were possible to suppress.


texts:
My application is running on MySQL 5.1.58 community edition. As I studied on SO websites that After acquisition by Oracle, new InnoDB plugin has been released and MySQL 5.1 with InnoDB plugin is the best for performance. Below is the command which displays the current InnoDB configuration in my system:

mysql> SELECT * FROM information_schema.plugins where plugin_name like 'InnoDB';
PLUGIN_NAME : InnoDB
PLUGIN_VERSION : 1.0
PLUGIN_STATUS : ACTIVE
PLUGIN_TYPE : STORAGE ENGINE
PLUGIN_TYPE_VERSION : 50158.0
PLUGIN_LIBRARY : NULL
PLUGIN_LIBRARY_VERSION : NULL
PLUGIN_AUTHOR : Innobase OY
PLUGIN_DESCRIPTION : Supports transactions, row-level locking, and foreign keys
PlUGIN_License: GPL
1 row in set (0.00 sec)

To upgrade this plugin, I have added below in my.ini file:

[mysqld]
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.dll;

My plugin folder is below:

E:\MySQL\MySQL Server 5.1\lib\plugin

ha_innodb_plugin.dll and ha_innodb_plugin.pdb are available in the above plugin directory.

Now, when I check using show plugins; command, I can't see InnoDB entry in the resultset.

Please answer me below things:

Why am I not able to see the InnoDB in plugin list?
Is it possible to upgrade InnoDB plugin with my existing MySQL version?
If yes, then is it my approach correct?
If not, what will be the correct approach?
Will it help me to improve the system performance?
For this, should I upgrade from mysql 5.1.58 to 5.1.73?


texts: Troubleshooting high memory usage on MariaDB 10.5.4

texts:
I am experiencing very high memory usage on MariaDB server. I am not sure if it is due to misconfiguration or a bug.
Server:                 MariaDB
Server version:         10.5.4-MariaDB MariaDB Server

We are running MariaDB 10.5.4
Our server is configured to use 140G for bugger and 9G for log.
innodb_buffer_pool_size=140G
innodb_log_file_size=9G
max_heap_table_size            = 8G
tmp_table_size                 = 8G

However, our server is currently using 225G without any load on server, and frequently spikes over 240G during queries.
All tables are InnoDB. Memory buffer usage according to information schema is the following:
MariaDB [db]> SELECT  ENGINE,         ROUND(SUM(data_length) /1024/1024, 1) AS "Data MB",         ROUND(SUM(index_length)/1024/1024, 1) AS "Index MB",         ROUND(SUM(data_length + index_length)/1024/1024, 1) AS "Total MB",                                                                                COUNT(*) "Num Tables"     FROM  INFORMATION_SCHEMA.TABLES     WHERE  table_schema not in ("information_schema", "PERFORMANCE_SCHEMA", "SYS_SCHEMA", "ndbinfo")     GROUP BY  ENGINE;
+--------+-----------+----------+-----------+------------+
| ENGINE | Data MB   | Index MB | Total MB  | Num Tables |
+--------+-----------+----------+-----------+------------+
| NULL   |      NULL |     NULL |      NULL |         20 |
| Aria   |       0.8 |      0.3 |       1.1 |         24 |
| CSV    |       0.0 |      0.0 |       0.0 |          2 |
| InnoDB | 1952761.3 | 284144.6 | 2236905.9 |       2517 |
| MEMORY |       0.0 |      0.0 |       0.0 |          3 |
+--------+-----------+----------+-----------+------------+

Total large memory allocated 151397597184
Dictionary memory allocated 899109080
Buffer pool size   9019360
Free buffers       1024
Database pages     9018336
Old database pages 3329014
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 47473690, not young 10534657191
0.00 youngs/s, 0.00 non-youngs/s
Pages read 192376965, created 62530810, written 146872114
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9018336, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Server memory usage starts out normally, but greatly increases during couple days until it eventually consumes all of server's resources and restarts.
We began experiencing this issue shortly after upgrading from an ancient version of MariaDB. (do not recall which one, but it was very dated).
How can I troubleshoot this issue further, and what can I do to ensure that memory usage does not go above 160G?


texts:
I'm trying to write a query that basically is searching by price, though sometimes the items may have a discount on them and this price isn't stored in the DB, only the discount percent and dates the discount runs between are, so we have to work out the price of the item on the fly to see if it should be included.

Additionally, if the item is an auction we ignore the sale price and just look at the start_price and max_bid to work out the current price (max bid is only set if the item has at least 1 bid).

We know it is NOT an auction if start_price == buyout_price, so know we have to figure out if the item is on sale or not and if so calculate the sale price to see if to include it in their requested price range.

I have this code..

SELECT
    whatever
FROM
    auctions AS a
LEFT JOIN
    ..............
WHERE
    ..............
AND
    IF(a.start_price == a.buyout_price AND a.discount_start_date < UNIX_TIMESTAMP() AND a.discount_end_date > UNIX_TIMESTAMP() AND a.discount > 0, (a.buyout_price - ((a.buyout_price / 100) * a.discount)), IF(a.max_bid > a.start_price, a.max_bid, a.start_price)) >= '500' AND IF(a.start_price == a.buyout_price AND UNIX_TIMESTAMP() > a.discount_start_date AND UNIX_TIMESTAMP() < a.discount_end_date AND a.discount > 0, (a.buyout_price - ((a.buyout_price / 100) * a.discount)), IF(a.max_bid > a.start_price, a.max_bid, a.start_price)) <= '700'
AND
    ..............;

As you may be able to make out I'm trying to get all results with prices between 500 - 700.

I'm also getting a error which appears to have something to do with the UNIX_TIMESTAMP(), though not sure why!?

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 '== a.buyout_price AND  a.discount_start_date < UNIX_TIMESTAMP() AND a.discount_e' at line 1


texts:
I just spent some time discussing two alternative DB designs with a coworker and I'm not convinced. Neither of us is strictly DBA so we may be missing something.

The overall goal is to create the ability to attach open-ended free-text attributes for each of three (possibly 4) entities.

Let's call the business entities Device, Location and Part; they have relationships between themselves.

Design A: Create DeviceAttribute, LocationAttribute and PartAttribute tables, each of which has an ID, Reference ID (FK to respective table), Name, Value and Type.

Design B: Create Attribute table with (ID, Name,Value and Type columns) and three reference tables - each holding a reference from one of the entity tables to one of the Attribute tables' IDs.

The concern is mostly about performance:
 - will 3 separate xxxAttribute tables perform better when querying only for an
   Entity's data, such as "give me Device X and all its attributes", or
   do both designs perform the same?
 - will 1 Attribute table (Design B) perform better when querying for entities with given attribute name/value, such as "give me all entities that have Attribute.Name='GPS'", or is it equivalent to querying a view that combines the 3 tables of Design A?
 - in case of Design B: does update on an entity table (Location, Device, Part) cause a lock on querying in other entity tables?

The system may have tens of thousands of Parts, thousands of Devices and hundreds of Locations and may have to process in the order of tens to few hundreds queries per second.


texts: Synchronizing a RDS instance with another RDS instance

texts:
I have a database on an RDS Instance (master) that needs to be synchronized to another RDS instance (slave). I created a read-replica for the first one, but that is created as read_only so I cannot do any operations on it.

Basically, I'll need to create a few other databases on the slave instance as well so that needs to be non-read-only. I'll also need to setup sync to happen once daily.

I am using MySQL 5.6+.


texts: Pglogical - multiple database tables to single table

texts:
I have 4 databases, each database is having 2 tables. The table structure on all the databases are same. So i want to replicate these two tables from all 4 databases to a single database.

So my centralized db will have 2 tables. Those tables having all 4 databases data.

FDW would be a better solution. But  i have logical replication as the required solution. So want to know the approach, possibilities.


texts: Databases lost after Postgres upgrade from 11 to 12

texts:
This is not my first upgrade. In the past I upgraded from 9 to 10 and 10 to  11. I thought that I wrote everything down the last time but apparently not. In verion 11 my databases are in nl_BE.utf-8:
                              List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |
-----------+----------+----------+-------------+-------------+
 glewlwyd  | glewlwyd | UTF8     | nl_BE.utf-8 | nl_BE.utf-8 |
 postgres  | postgres | UTF8     | nl_BE.utf-8 | nl_BE.utf-8 |
 prda      | postgres | UTF8     | nl_BE.utf-8 | nl_BE.utf-8 |
 template0 | postgres | UTF8     | nl_BE.UTF-8 | nl_BE.UTF-8 |
 template1 | postgres | UTF8     | nl_BE.utf-8 | nl_BE.utf-8 |

(5 rows)

After the installation (apt-get install postgresql-12) I modify the /etc/postgresql/12/main/postgresql.conf and change the en_US.UTF-8 into nl_BE.UTF-8. In the previous upgrades I copied this file to my data directory but 12 does not seem to like that. The port number of the 11 and 12 version differ.
When I create the database with /usr/lib/postgresql/12/bin/initdb --locale=nl_BE.UTF-8 --pgdata=/srv/postgres/data/12 the new instance is created but the databases are in en_US.UTF-8. The /usr/lib/postgresql/12/bin/pg_upgrade -b /usr/lib/postgresql/11/bin -B /usr/lib/postgresql/12/bin -d /srv/postgres/data/11 -D /srv/postgres/data/12 ends without an error.
When I connect to the instance my own databases are not there. Must be because the locale is not correct. I also changed the locale (export LANG=nl_BE.UTF-8) of my shell but this did not help. What could I have done wrong?


texts: is it possible to get the lost data deleted from rails console?

texts:
The command I ran in rails console is

Product.delete_all("id > 44 AND id < 51")

Is there any way to get the records back?

Thanks


texts: How to connect to remote MySql db with user that has '@' symbol in name?

texts:
I am having trouble dumping database schema into a self contained file using this command
mysqldump -u 'dbadmin@morecharacters' -p -h 'remotehost' -P 31337 --no-data dbname > schema.sql.
The error that I get looks like this:
mysqldump: Got error: 1045: Access denied for user 'dbadmin'@'[resolved ip address]' (using password: NO) when trying to connect.
The problem is that the username is being resolved to just dbadmin when it should be dbadmin@morecharacters. I'm not the (idiot) one who created the user, so changing the username is not an option. Unfortunately, all environments have a similar naming pattern.
When I export the database structure from MySql Workbench using strictly GUI, it works. If I take the same query that works in MySql Workbench and try to run it via command line, I get the same error as mentioned above. My question is how do I get the command to run properly?
USING: Windows 10, Powershell version 5.1


texts: "Replicate" Table structures with sp_addscriptexec

texts:
In our customers environment is a massive central server (running SQL Server Enterprise 2008R2) and some distributed PCs (running SQL Server Standard 2008R2).

The central server has a replication for all Stored Procedures and Functions, but only for some tables with master data (like products and materials) for the other PCs.

I've come across sp_addscriptexec to add a script that will be able to do this (except I haven't found a good explanation for this).

But the catch with our customer is that not all PCs are online all the time, so there is the probability that there was more than one change to the database structure.

How are the sp_addscriptexec scripts actually executed? Are they executed only once or on every replication run? Will they run in a specific order or randomly? Also can someone give me an example how to actually use the command?

And the biggest question, is this a good approach for doing this kind of stuff or are there other alternatives?


texts: I have an SSIS process that is built to push records to a web service that has some sporadic auth timeouts. My design is working well but we are running large batches of records 250,000 per batch over Millions of rows. What I'm looking to do is something similar to this post https://stackoverflow.com/questions/7076018/how-to-fail-ssis-component-on-invalid-rows but instead of failing the data flow task I want to to just complete successfully without processing more rows so it iterates on to my next loop iteration. Essentially once I get an error I know the rest for that batch will fail and I want to iterate my loop which I already have logic built to re-run failed records. Any thoughts on how to accomplish?


texts:
I'm trying to create a dynamic database creation script.

There are a lot of steps and we create this database often so the script looks something like this.

 DECLARE @databaseName nvarchar(100) = 'DatabaseName'
 EXEC('/*A lot of database creation code built off of @databaseName*/')

This is all well and good except for one view that we'd like to create in @databaseName.

I've tried four different ways to create this view without success:

My first thought was to simply set the database context and then create the view in one script. Unfortunately, this didn't work because CREATE VIEW must be the first statement in its query block (details).

--Result: Error message, "'CREATE VIEW' must be the first statement in a query batch"
EXEC
('
    USE [' + @databaseName + ']
    CREATE VIEW
')

To get around (1) I tried to set the context separately so that CREATE VIEW would be the first command in the EXEC. This did create the view but did so within my current context and not @databaseName. It seem that the effects of calling USE in EXEC only persist until the end of that EXEC statement (details).

--Result: The view is created in the currently active database rather than @databaseName
EXEC ('USE [' + @databaseName + ']')
EXEC ('CREATE VIEW')

Next I tried putting everything back into one script but included a GO command in order to make CREATE VIEW the first command in a new query block. This failed because GO isn't allowed within an EXEC script (details).

--Result: Error message, "Incorrect syntax near 'GO'"
EXEC
('
    USE [' + @databaseName + ']
    GO
    CREATE VIEW
')

Finally I tried to specify the target database as part of the CREATE VIEW command. In this case the script failed because CREATE VIEW doesn't allow the database to be specified as part of its creation (details).

--Result: Error message, "'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name"
EXEC ('CREATE VIEW [' + @databaseName + '].[dbo].[ViewName]')

Any suggestions? I think this should be a common use case but Google wasn't able to help me.


texts: Can SQL Express be used as a Log Shipping monitor server?

texts:
I'm keen to setup a "Monitor Server".

We have SQL 2008 R2 Enterprise edition on both primary and secondary servers.

Can we use SQL Express in monitoring server setup for licencing purposes?


texts: Run sp_BlitzCache an agent job to assist performance tuning efforts / baselining query performance?

texts:
Just getting into using various Brent Ozar tools like sp_BlitzCache and sp_BlitzIndex to identify and correct SQL Server performance issues. Been experimenting with running sp_AskBrent (now BlitzFirst...) and sp_WhoIsActive as agent jobs as a way to baseline query performance.

Do any of you run sp_BlitzCache periodically as an agent job to capture history / provide a baseline and/or evidence for your SQL Server performance tuning efforts? If so:

How specifically has this helped you in your environment?
Which sp_BlitzCache sort_order do you find most helpful to log? I'm considering separate output tables by cpu, reads, duration, and executions per minute. But wondering if I should be using "avg X" for these sorts.
Should I stick with the default of @top = 10? Or go for 20, 30, etc?

Here's an example where I'm using total reads and top 20:

EXEC DbaTools.dbo.sp_BlitzCache
 @top='20', @sort_order = 'reads', @output_database_name = 'DbaTools', @output_schema_name = 'dbo', @output_table_name = 'BlitzCache_by_reads'

How frequently would you run such a job? Weekly? Since the plan cache doesn't get wiped out until a host / service restart, an explicit command to do so (dbcc freeproccache), or in some other perhaps unexpected way (to some) such as changing MAXDOP (our Team learned the latter the hard way).
This is more in line with with another StackExchange question just I posted about sp_AskBrent and sp_WhoIsActive, but how resource-intensive is it to run these SPs on a periodic basis?

That other StackExchange question is here:

sp_AskBrent not logging queries when saving to table vs. sp_WhoIsActive


texts: Reclaim space from dropped column when there isn't enough space for index rebuild

texts:
I dropped a char(10) column from a table that has about 111 million rows (30gb data, 11gb index)

I then ran an index rebuild on the clustered index.  This ate up all the space on the drive containing the database.

I then ran dbcc cleantable and got all the space back, plus a few more gigs.

Is there a way to reclaim space from the dropped column when there isn't enough space for index rebuild?

Or did the cleantable operation actually reclaim the space?  (I know it reclaimed space, but I'm guessing it's possible it reclaimed space for a different reason, since cleantable isn't suggested as the way to reclaim space after dropping a column)


texts: Group by with no aggregation expresssion is not supported

texts:
I need help i am getting an error to the following query on azure sql.

SELECT illuminance as illuminance,
       temperature as temperature,
       (select to_char(sysdate,'DD/MM/YYYY HH:MI:SS AM') from dual) AS Date
  INTO [PowerBIVisualizationOutput]
FROM [PowerBIVisualizationInput]
GROUP BY illuminance, temperature, TumblingWindow(minute,5)


texts: Get a specific row from a TSQL common table expression

texts:
I have this tsql code to recursively get the whole workgroup hierarchy for a specific workgroup (WgID 283 here):

WITH GroupList(WgID,WgParentWgID) AS (
  SELECT WgID,WgParentWgID
  FROM tblplWorkGroup WITH (NOLOCK) WHERE WgID = 283 AND WgActive=1

  UNION ALL

  SELECT B.WgID,B.WgParentWgID
  FROM GroupList A
  INNER JOIN tblplWorkGroup B WITH (NOLOCK) ON A.WgParentWgID = B.WgID
  WHERE B.WgActive=1
) SELECT * FROM GroupList

Gives this:

WgID    WgParentWgID
283     315
315     272
272     4
4       0

I need to get just the second to last row. I can do it by changing the last select statement.

 SELECT *
 FROM GroupList
 WHERE WgParentWgID = (SELECT TOP (1) WgID
                       FROM GroupList
                       WHERE WgParentWgID = 0)

Gives:

WgID    WgParentWgID
272     4

However, if I want to get a nth row, it starts to get cumbersome. Is there a way to get a nth row from a WITH table expression? Or output row information in the output table too?

I tried converting the WITH expression to use a temporary table (where looks like I can get the nth row) but could not make the recursive functionality work. Any help is much appreciated.


texts:
I currently have all of my data in a MySQL database which handles my entire application system. My current setup is a Java desktop application that publishes data to the database through a NodeJS API. There is an iOS application that connects to this API as well as a website that allows people to view the data. The website updates the data on the screen every second for each person. I fear that I will not be able to keep my costs down as the users scale because the query that is run every second contains a lot of joins and sorts.

What I would like to do is add a Mongo database like a cache. Overtime certain data is inserted to MySQL from my API, I would have the API run that intensive query and store the results in the mongo database. I am currently having to take the results form the query and transform it into a lot of nested arrays and return it in son form. It seems like I could save a lot of time/resources by only running the query when data changes and then storing it already formatted into the Mongo database.

I actually started with using purely MongoDB a while back, but it was her to run reports and analyze the type of data I am storing. It really needs to be stored normalized for easier reports.

Does this idea seem feasible and practical? I feel like I will have to do a lot of scaling with the MySQL side of things as my user base grows.


texts: Why do I need to backup the Database Master Key?

texts:
When I make a Database Master Key for a database:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Some Long Password'

This page from Microsoft recommends I back it up offsite:

http://msdn.microsoft.com/en-us/library/ms174382.aspx

I've confirmed through testing that the Master Key is stored when you backup the database. I'm already backing up the database, and I'm already storing the password safely. So why do I need to backup the Master Key too?

(As an aside, I am already backing up the Service Master Key and storing offsite).


texts: Took a full backup of a running mariadb server and restored, but none of the databases are visible

texts:
I'm setting up a mariadb galera cluster to ultimately switch production to. However, I don't have the ability to stop the production instance so I'm trying to set this up using various workarounds I've come across. The current setup relies on a pair of mariadb servers as a master/slave pair. I've added a 3rd mariadb server and I'm restoring a full backup to third, empty server.

To backup the server I executed the following:

mysqldump -u root -p --all-databases --master-data=2 | gzip > backup.sql.gz

once the new server was ready, I restored the database:

zcat backup.sql.gz  | mysql -u root --port=3309 --socket=/var/run/mysqld/mysqld400.sock

When I log into mysql and do a show databases, I don't see anything other than the default db's (information, performance, and mysql). I stopped and restarted mariadb and it still the same. Is there another step I need to point to the data that was just restored?


texts: Save output of a Mongo Command to a Variable bash script

texts:
I have been writing a script to find and kill slow mongodb queries, how can I store output of db.currentOp() in a variable?
current I am using this but failing
#! /bin/bash

out=""
mongo << EOF

out=db.currentOp();
EOF
echo"----------------------------"
echo"$out"

Updated Script
#! /bin/bash

out=$(mongo --eval 'db.currentOp({op:"query"})')
echo"-----------------------"
echo"$out"

Output
./kill_slow_mongo_queries.sh: line 4: echo-----------------------: command not found
./kill_slow_mongo_queries.sh: line 5: echoMongoDB shell version v3.6.8
connecting to: mongodb://127.0.0.1:27017
Implicit session: session { "id" : UUID("ce96f3ee-f5e1-49b5-aeef-b5766328dda3") }
MongoDB server version: 3.6.8
{ "inprog" : [ ], "ok" : 1 }: No such file or directory


texts: Is it possible to determine what part of a stored procedure is currently executing?

texts:
Say I have a stored procedure like this (assume that the table schemas are fine):

CREATE PROCEDURE p_MyProc
AS

INSERT MyTable SELECT Col1 FROM Table1

INSERT MyTable SELECT Col2 FROM Table2

INSERT MyTable SELECT Col3 FROM Table3

Assuming that these end up being big inserts that can take several minutes each, is it possible to determine which one is currently running from within the context of a stored procedure?

I know how to find what stored procedure is running, I'm just looking to see if there's a way to get more granular without having to add PRINT or other tracing statements in there.  Is there maybe a way to make the statements show up in a trace?


texts: Getting error USE statement is not supported to switch between databases with Azure Synapse formerly SQLDW

texts:
Whenever I attempt to submit a query to Azure Synapse SQLDB (or try to do anything with the database from my application called DBForge) I get the following error message:
USE statement is not supported to switch between databases
Can someone let me know how to overcome this issue?


texts: SQL Batches and the GO command: which parts of my script failed?

texts:
I executed a script that had multiple sections, separated by the GO command, something like this:

Use DB1
GO

ALTER TABLE foo ADD bar VARCHAR(10) NULL
UPDATE foo SET bar = 'nananana'
ALTER TABLE foo ALTER COLUMN bar VARCHAR(10) NOT NULL
DROP TABLE oldtrash
GO

sp_rename 'zippidy.dodah', 'day', 'COLUMN';
GO

USE DB2
GO

sp_rename 'zing.bang', 'bong', 'COLUMN';
GO

ALTER TABLE whiz ADD dum INT NULL
DROP TABLE moretrash
GO

When I ran it (all at once), I got a mix of success and error statements, something like:

(red text)
Msg 207, Level 16, State 1, Line 6
Invalid column name 'bar'.

(black text)
Caution: Changing any part of an object name could break scripts and stored procedures.

(red text)
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 238 [Batch Start Line 27]
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

I have some ideas about what went wrong here, but I have a few questions (hopefully these are more broadly applicable than just this one script):

If an error refers to Line 6, is that from the top of the script? Or from the top of the batch? (after the prior GO)
If an error is encountered, does it roll back only the statements in that batch? (between the GOs)? So, if the UPDATE foo fails (because the column doesn't exist when the statement is evaluated), then none of the other statements in that batch commit, including the DROP TABLE oldtrash?
Would the order of the statements within the batch have mattered, if one of them errored out? For example, if my DROP TABLE had come before the UPDATE foo, would it have run?
The order of the output leads me to believe that the first sp_rename succeeded (with the "caution" output), and the second failed with the error. I'll check the tables, of course, but is that what you'd assume here? Or could the first sp_rename have both issued the caution and thrown the error?
The failure of the prior batches doesn't prevent later batches from running, correct? So regardless of what happened above, my final ALTER TABLE whiz and DROP TABLE moretrash likely executed (and due to the lack of output, succeeded)?


texts:
Say I have a table called listing and it has the following columns:
id | name   | description | price | created_date|
 0 | iPhone | A phone     | 1000  |   7/4/2020  |

Since I want to track when a listing is sold, I'm considering adding a sold_date column.
My concern is that this would cause a bunch of rows in the listing table to have a null in the sold_date column since every listing wouldn't be considered sold. Would having null values be okay in this case, or should I create another table that would contain the sold_date column and references a listing?


texts: Migrating PostgreSQL 9.1 database to Ubuntu 14.04

texts:
I want to migrate a database from PostgreSQL 9.1 installed on an old Debian system to Ubuntu 14.04. The database is making use of triggers and it is a requirement that the triggers be migrated as well, but without having the triggers executed on every row as it is migrated to the new database causing the new database to contain a garbled version of the original data.

The PostgreSQL version in the Ubuntu 14.04 repository is 9.3.

Is there any way I can convert the data from /var/lib/postgresql/9.1 on the old system to be usable on PostgreSQL 9.3?

What I have tried so far

I found an older answer on how to migrate from 9.1 to 9.3. But I did not have much luck using it.

Using pg_dump will as far as I understand require the old database to be running as the data is being dumped, which could lead to updates written during the dump to be missing from the new database.

Using pg_upgrade requires both PostgreSQL versions to be installed simultaneously. But 9.1 is not available in the Ubuntu 14.04 repositories. I considered replicating the old system into a chroot on the new machine, but I don't know if that will play well with how pg_upgrade will create hardlinks.

There is another hurdle which so far prevented me from testing pg_upgrade. When I try to run pg_upgrade I am told that the command is in the postgres-xc package which is not installed.

If I try to install postgres-xc it tells me that postgres-xc depends on postgres-xc-client.

If I try installing postgres-xc-client I am told that doing so will uninstall posgres-9.3. So it appears it is not even possible to have pg_upgrade and PostgreSQL 9.3 installed at the same time.


texts: adding cluster index other than primary key default cluster index

texts:
create table t1 (id int primary key auto_increment, name varchar(20))

In the above table id is primary key and by default cluster index is added to primary key column.

My question is, can we add cluster index on column (name) too??.


texts: Cannot create relationship: Error:Missing index on column(s)

texts:
I am trying to make relation on 2 tables in database designer.

I am using relationship tool. Below are the steps followed:

I clicked on the ID in table lekar.
Then I click on lekar column in the next table.
Pressed OK.

It throws the below error, what is the problem ?

Error: Missing index on column(s).

I added ID column so it will have indexing, but it still does not work.


texts: "SELECT LAG(id) FROM cb_cat WHERE id=450";

texts:
I want to return the previous row based on the current id number.
All id numbers are UNIQUE, but are NOT (necessarily) SEQUENTIAL.
SELECT LAG(id) FROM cb_cat WHERE id=450;

I thought this query should work.
If possible, I would like to return * from the previous row before the current id.
I do not know which id number that is, but it is smaller than the current id number, e.g. 450. It could be 449 or even 1.
How can I achieve this?


texts:
we have a few long running procedures in the database, that cause an instant Table Lock on our main text storage table, which causes all other requests of the web app to wait (because the table is used in nearly every request).

Setup

Web Application with ASP.NET, Entity Framework, SQL Server 2008R2
A lot of Tables (Table per Class Strategie) with data attributes
ONE Table ("dbo.Translations") for dynamic defined texts (to enable translations).
No foreign keys to this table

To get the text of the object in the right language, the Object ID is saved to the translation table with the text value

Table Definition

So the Translation Table looks like:

  dbo.Translations (ID varchar(36), type varchar(10), lang varchar(5), value varchar(max))

whereas Type is defined for each object and lang is like 'en-GB' (not relevant).

Index

The table has two indexes:
PK_Translations on ID (Clustered, not unique)
UNQ_Translations on all columns (nonclustered, unique)

Problem

The table has to handle two operations:
SELECTS and INSERTS.

Update and delete is not a common use-case.

The table holds nearly 1.000.000 rows and an index over ID,type,lang.

When the web application wants to insert (multiple) records, the operation takes a lot of time and creates an instant table lock. This stops every other request to this table (maybe even to other tables, so sometimes it seems to be a database lock?!) and causes them to wait.

Inserts

The INSERTS of large data is done in the following way:

INSERT INTO Translations
   (Id, Typ, lang, value)
SELECT
   td.nId,
   td.Typ,
   td.lang,
   td.value
   FROM #tempDictionary td

Note: #tempDictionairy is a temporary table

Steps

The following steps where tested and did not improve the situation:

Start the SELECT transaction with read uncommitted -> no change
Extend the index sparsity to 50% to ensure that the inserted records can be saved easily -> no change

How can I find out why the SQL Server creates an table / db lock?


texts: SQL Server Launchpad - The user has not been granted the requested logon type at this machine

texts:
I am trying to execute python scripts within t-sql on sql server 2017. With external scripts enabled, when executing a trivial script, I get the error "SQL Server was unable to communicate with the LaunchPad service. Please verify the configuration of the service". When I try to start the launchpad service, it fails to start reporting error "The user has not been granted the requested logon type at this machine" to the event logs. I have local admin on the host server and have tried to add NT Service\MSSQLLaunchpad to adminstrators and still fails for the same error. The NT Service\MSSQLLaunchpad does not have the explicit permission allow logon locally or logon as a service. I had my dba change the login to a domain user which does have those permissions and the same error occurred. Have also tried to repair the instance installation.
Any ideas of how to fix this problem?


texts:
I have a trigger, that I want to try to update a field based off the value of another table.  I have manually verified that the data exists in the other table so I know the join will work.  However, data is being inserted into the table, but no update made.  Which makes me think I did something off with the trigger
Create Trigger [dbo].[trig_OnInsert] ON [dbo].[SHIPMENT]
For Insert
    AS
        BEGIN
            UPDATE si
            SET si.au = au.au + ' - ' + si.au
            FROM SHIPMENT si
            JOIN alphauniversal au
            ON si.cm_controllerID = au.controllerid
            WHERE si.cm_controllerID IN (Select si.cm_controllerID FROM Inserted)
        End

Also, if the trigger presents an error, or doesn't execute successfully, will the record still be inserted in the table?


texts: MySQL Error Code: 1044. Access denied for user (temporary)

texts:
Edit, just to clarify, following comments: While this incident was ongoing (around 3 hours) I was still able to run queries as normal. The only thing that didn't work was creating temporary tables.

Original question:
I am trying to investigate a strange problem. I had some working code in an application (RStudio), which suddenly stopped working. It was trying to create a temporary table in an otherwise read-only database replica for which I have been granted CREATE TEMPORARY TABLES privilege. I went into MySQL Workbench and tried to run a basic test:
CREATE TEMPORARY TABLE tmp_foo AS SELECT * FROM sometable LIMIT 10 ;

And it resulted in the following error in MySQL Workbench:
Error Code: 1044. Access denied for user '_______'@'_______' to database '_______'

Note that I do not have access to the server itself. When the DB support person came online to help later (and I hadn't checked it since), my code suddenly worked with no problem, and it was still using the same connection it had established just prior to the problem occuring. Yet the error persisted im MySQL Workbench. They asked me to restart MySQL Workbench, which I did, and then it also worked with no error. Now, they said that they had done nothing on their side whatsoever, which I found very strange, so I asked to see the MySQL error log file, but when they sent me that, there was nothing there which seemed relevant - only some warnings about [Server] IP Address '_______' could not be resolved none of which were the IP address I was connecting from, nor were they at the times that my inciden happened.
So I have a few questions:

Is there some other logging that needs to be enabled to show these errors in case it happens again ?
What could be the cause of the Error Code: 1044 errors which magically stopped happening a few hours later.
Is there anything else I can do to troubleshoot this, or request support to do ?

Note that the MySQL database is version 8.0.17 running in Amazon RDS, and I am connecting to it from an AWS EC2 instance running Ubuntu 18.04.5 LTS


texts: MariaDB indices do not start at 1 after removing database and re-creating it

texts:
I am currently experimenting with a DB-Driven app. It automatically sets up its database-tables if its database does not exist, so it is very easy for me to drop the database and just start from scratch.
During those experiments I noticed that after such a fresh start the db-indices do not start at 1 again, but continue counting as if the DB had never been removed.
So it seems just deleting the DB is not sufficient. Are the last indices cached somewhere or kept in memory - do I need to restart MariaDB to get "truly fresh" indices after a reset?
(It won't affect the software, but I'm concerned I may not be cleaning up well enough...)
Here's an example: table migrations is created using
CREATE TABLE `migrations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int(11) NOT NULL,
  `exec_ts` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Looking at the current content, there are 4 records with id from 24 to 27.
After dropping the db and going through the creation, I see the same values. Ok, so somehow their base hasn't changed - but why don't the autogenerated indices start at 1?
Browsing around, I came across information_schema.TABLES which has a record for this table and AUTO_INCREMENT indeed shows the value that the next record got when I manually created one. I have verified that dropping the DB also deleted that record, but it must have "hidden" somewhere else...


texts:
This query takes ~5x longer than it should due to the WHERE IN() statement at the end. I am a junior DBA in over my head trying to figure out a way to optimize it. Is there a way to check IN() the resultset of the outer query? Can any of the self-joins be combined? Are there any query optimization resources you would recommend?

Apologies for the lack of minimalism, completeness, and verifiyibility - including the DDL seemed like overkill. Below are the query and the execution plan bottleneck.

SELECT SC.ROW_ID      C1_CASE_ID,
       SC2.ROW_ID     C2_NEW_CASE_ID,
       SC2.CASE_NUM   C3_NEW_CASE_NUM,
       MC.ROW_ID      C4_MSTR_CASE_ID,
       MC2.ROW_ID     C5_NEW_MSTR_CASE_ID,
       MC2.CASE_NUM   C6_NEW_MSTR_CASE_NUM
 FROM  siebel.ODS_S_CONTACT          MCON2,
       siebel.ODS_S_CONTACT          MCON,
       siebel.ODS_S_CASE             SC,
       siebel.ODS_S_CASE             MC,
       siebel.ODS_S_CASE_BNFTPLAN    CBP,
       siebel.ODS_S_CASE             MC2,
       siebel.ODS_S_CASE             SC2
WHERE (SC.STATUS_CD = 'Withdrawn')
  AND (CBP.STATUS_CD IN ('Active', 'Approved','Inactive')
  AND EXISTS (
               SELECT 1
                 FROM (SELECT cbp2.row_id cbp2_row_id,
                              cbp2.case_id cbp2_case_id,
                              DENSE_RANK() OVER (PARTITION BY cbp2.case_id ORDER BY CASE WHEN cbp2.status_cd = 'Active' THEN 1
                                                                                WHEN cbp2.status_cd = 'Approved' THEN 2
                                                                                WHEN cbp2.status_cd = 'Inactive' THEN 3
                                                                                ELSE 4
                                                                                END,cbp2.created DESC
                                                ) cbp2_order
                         FROM siebel.ods_s_case_bnftplan cbp2
                      --where cbp2.case_id = CBP.CASE_ID
                      ) sq
                WHERE cbp2_order = 1
                  AND CBP.CASE_ID = cbp2_case_id
                  AND CBP.ROW_ID = cbp2_row_id
             )
      )
WHERE (SC.MSTR_CASE_ID=MC.ROW_ID)
  AND (MC.APPLICANT_ID=MCON.ROW_ID)
  AND (MCON.SOC_SECURITY_NUM=MCON2.SOC_SECURITY_NUM AND MCON.ROW_ID <> MCON2.ROW_ID)
  AND (MCON2.ROW_ID=MC2.APPLICANT_ID)
  AND (MC2.ROW_ID=SC2.MSTR_CASE_ID AND SC2.TYPE_CD = SC.TYPE_CD AND SC2.STATUS_CD = 'Active')
  AND (SC.ROW_ID=CBP.CASE_ID)
  AND SC2.CREATED IN (
                        -- default: 46 rows  ~30seconds
                        SELECT MAX(SCSUB.CREATED)
                          FROM siebel.ODS_S_CASE          SC2SUB
                         INNER JOIN siebel.ODS_S_CASE    MC2SUB        ON MC2SUB.ROW_ID = SC2SUB.MSTR_CASE_ID
                         INNER JOIN siebel.ODS_S_CONTACT MCON2SUB      ON MC2SUB.APPLICANT_ID = MCON2SUB.ROW_ID
                         INNER JOIN siebel.ODS_S_CONTACT MCONSUB       ON MCON2SUB.SOC_SECURITY_NUM = MCONSUB.SOC_SECURITY_NUM
                         INNER JOIN siebel.ODS_S_CASE    MCSUB         ON MCONSUB.ROW_ID = MCSUB.APPLICANT_ID
                         INNER JOIN siebel.ODS_S_CASE    SCSUB         ON MCSUB.ROW_ID = SCSUB.MSTR_CASE_ID
                         INNER JOIN siebel.ODS_S_CASE_BNFTPLAN CBPSUB  ON CBPSUB.CASE_ID = SCSUB.ROW_ID
                         WHERE SC2SUB.ROW_ID = SC2.ROW_ID
                           AND  CBPSUB.STATUS_CD IN ('Active', 'Approved')
                           AND SC2SUB.TYPE_CD = SCSUB.TYPE_CD
                           AND SCSUB.STATUS_CD = 'Active'
                      )

Execution Plan here.


texts: Best practice database design - user rating tables

texts:
I want to create a table to store user ratings of items for collaborative filtering algorithms. So far my table looks like this:

IID | UID | Rating
i.e.

item ID |user id |rating.

From collaborative algorithm examples the tables rather look like:

Item ID | Rating U1 | Rating U2 | ... | Rating Un

This means i would have to alter (extend) the table every time a new user signs up. Alternatively i have to create a new table for each user. Both does not seem to be very efficient solution. Can anyone point me into the right direction?

Thanks


texts: Postgresql: Sortable index on array values

texts:
Suppose the following table:

CREATE TABLE foo (id serial, category int, bar int[]);
INSERT INTO foo VALUES (1, 1, '{4,10,20}');
INSERT INTO foo VALUES (2, 1, '{1,8,9}');

We want to query sorted individual values from bar column. This can be achieved with this query:

SELECT unnest(bar) AS u, id FROM foo ORDER BY u;
 u  | id
----+----
  1 |  2
  4 |  1
  8 |  2
  9 |  2
 10 |  1
 20 |  1

How we can build an index for this query?

UPDATE:

Version of postgres is not important, lets say we want to implement with latest version (9.5)
bar column is variable-length, different rows may have different number of elements, (but no empty array).
Elements are not null.
We want an index to support efficient queries with LIMIT (so not retrieving all rows in queries, see blow)
This is a simplified version of an actual (in design) use-case.

UPDATE:
Example with LIMIT clause

SELECT unnest(bar) AS u, id FROM foo WHERE category = 1 ORDER BY u LIMIT 3;
 u  | id
----+----
  1 |  2
  4 |  1
  8 |  2


texts: os_buffer_descriptors blocking problem SQL Server 2008 R2

texts:
The following query :

select (buffer_descriptors.datacache) as 'Data Cache (MB)',
    case
    when buffer_descriptors.datacache > 4096 then (((buffer_descriptors.datacache)/4096)*300)
    else '300'
    end as 'Min Page Life Expectancy',
perf_mon.ple_value as 'Actual Page Life Expectancy (sec)'
from
(
    select (count(*)*8/1024) AS 'datacache'
    from sys.dm_os_buffer_descriptors
    where page_type in
    (
        'INDEX_PAGE'
        ,'DATA_PAGE'
    )
) as buffer_descriptors,
(
    SELECT cntr_value as ple_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Page life expectancy'
    AND OBJECT_NAME LIKE '%Buffer Manager%'
) as perf_mon

Is generating a SOS_SCHEDULER_YIELD resource wait and a drain on memory

  blocking_session_id : 0
  wait_type : SOS_SCHEDULER_YIELD

The client hasn't noticed any particular problems and the server appears relatively quiet.

Any thoughts as to why a SELECT on dm_os_buffer_descriptors might cause a drain on memory and a SOS_SCHEDULER_YIELD wait ?

Update 11:39

Running a CHECKPOINT and removing buffers DBCC DROPCLEANBUFFERS resolved the blocking issue. Still don't understand why though.

Update 18:46

The blocking occured again so I took Jon's advice and set the ISOLATION level to READ UNCOMMITTED. This worked and allowed the query above to complete. Afterwards, I went hunting around for exclusive locks in dm_tran_locks but didn't find any. Running the dm_os_buffer_descriptors query a second time in READ COMMITTED didn't hang.

Here is the query used to view active locks

SELECT

         SessionID = s.Session_id,
         resource_type,
         DatabaseName = DB_NAME(resource_database_id),
         c.client_net_address,
         request_mode,
         request_type,
         host_name,
         program_name,
         client_interface_name,
         login_name,
         nt_domain,
         nt_user_name,
         s.status,
         last_request_start_time,
         last_request_end_time,
         request_status,
         request_owner_type,
         objectid,
         dbid,
         a.number,
         a.encrypted ,
         a.blocking_session_id,
         a.text
     FROM
         sys.dm_tran_locks l
         JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
         JOIN sys.dm_exec_connections c ON c.session_id = s.session_id
         LEFT JOIN
         (
             SELECT  *
             FROM    sys.dm_exec_requests r
             CROSS APPLY sys.dm_exec_sql_text(sql_handle)
         ) a ON s.session_id = a.session_id

Should I be able to see what's blocking os_buffer_descriptors using dm_tran_locks?
If not, what should I use?


texts:
I have the classic error:

Msg 7391, Level 16, State 2

When I try to execute a transaction over a linked server.
I have the same identical configuration in 2 environment, production and test. It works fine in production, but I have an error in test.

Source server is:

Microsoft SQL Server 2014 (SP1-CU5) (KB3130926) - 12.0.4439.1 (X64)
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

Destination server is:

Microsoft SQL Server 2008 (SP2) - 10.0.4321.0 (X64) Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

They are both instances on 2 node clusters.

The query:

DBCC TRACEON (3604, 7300)
GO
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION
DELETE FROM [mylinked].mydb.dbo.atable
ROLLBACK TRANSACTION

Linked server options:

MSDTC Options, on local DTC and clustered DTC:

I tested with Dtcping. Everything seems fine. And no error in event viewer.
The full error:

OLE DB provider "SQLNCLI11" for linked server "mylinked" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 7
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "mylinked" was unable to begin a distributed transaction.

In the SQL Server trace, capturing OLEDB Errors I find, in source server:

<hresult>-2147168242</hresult>
<inputs>
    <punkTransactionCoord>0x0000000073FA54F0</punkTransactionCoord>
    <isoLevel>4096</isoLevel>
    <isoFlags>0</isoFlags>
    <pOtherOptions>0x0000000000000000</pOtherOptions>
</inputs>

The only thing I'm not sure about is the network. Windows firewall is of. Everything works fine if not under a transaction. But there are firewalls and switch and network stuff between the 2 instances, where I have no control.
Port range for dcom protocols are in a fixed range.

The questions:

How can I check if it is really a network problem? I need to demonstrate to network group the problems. They say: "Everything is fine".
Why the Dtcping works fine?
What else can I check?


texts: MySQL wont start after modifying /etc/my.cnf

texts:
I have Linux Red Hat 5, with Zend Server and MySQL Server installed.

I am trying to change the default MySQL port to 512.  However, after making this change, MySQL will no longer startup.  I get the error below:

Starting MySQL...The server quit without updating PID file
    (/var/lib/mysql/localhost.localdomain.pid).

How do I resolve this issue?


texts:
I intended to drop all functions in all schemas within a certain database, however, after I executed the bash file containing the following code block, the functions are still there

# use pg_dump and pg_restore to migrate some databases from another server

psql $DBNAME << EOF
DO \$$
DECLARE
    myschema RECORD;
    myview RECORD;
    myfunc RECORD;
    myseq RECORD;
BEGIN

    FOR myschema IN (SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name <> 'information_schema')
    LOOP

        -- Drop all views
        FOR myview IN (SELECT viewname FROM pg_catalog.pg_views WHERE schemaname=myschema.schema_name)
        LOOP
            EXECUTE 'DROP VIEW ' || quote_ident(myschema.schema_name) || '.'  || quote_ident(myview.viewname) || ';';
        END LOOP;

        -- Drop all sequences
        FOR myseq IN (SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema=myschema.schema_name)
        LOOP
            EXECUTE 'DROP SEQUENCE ' || quote_ident(myschema.schema_name) || '.'  || quote_ident(myseq.sequence_name) || ';';
        END LOOP;

        -- Drop all functions
        FOR myfunc IN (SELECT routine_name FROM information_schema.routines WHERE routine_type='FUNCTION' AND specific_schema=myschema.schema_name )
        LOOP
            EXECUTE 'DROP FUNCTION ' || quote_ident(myschema.schema_name) || '.'  || quote_ident(myfunc.routine_name) || ';';
        END LOOP;

    END LOOP;

END;
\$$ LANGUAGE plpgsql;

After I partition the do block into three separate do blocks for dropping all views/functions/sequences, I managed to drop all sequences. I assume this is because the transaction must be atomic. However, because I intend to clean the databases and drop all unused functions/sequences/views, I cannot know ahead which are dependent ones and will raise errors. Can I ask plpgsql to ignore the error so that the unused views/functions/functions can be successfully dropped?

I revised my code to based the suggestions from @LaurenzAlbe. However, I got to error ERROR:  syntax error at or near "EXCEPTION". Did I place the EXCEPTION statements in the wrong place?

psql $DBNAME << EOF
DO \$$
DECLARE
    myschema RECORD;
    myseq RECORD;
    myview RECORD;
    myfunc RECORD;
BEGIN
    FOR myschema IN (SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name <> 'information_schema')
    LOOP
        -- Drop all functions
        FOR myfunc IN (SELECT routine_name FROM information_schema.routines WHERE routine_type='FUNCTION' AND specific_schema=myschema.schema_name)
        LOOP
            EXECUTE format('DROP FUNCTION %I.%I', myschema.schema_name, myfunc.routine_name);

            EXCEPTION
            WHEN dependent_objects_still_exist THEN
            RAISE WARNING 'Could not drop object %.% because of dependencies', myschema.schema_name, myfunc.routine_name;
        END LOOP;

        -- Drop all sequences
        FOR myseq IN (SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema=myschema.schema_name)
        LOOP
            EXECUTE format('DROP SEQUENCE %I.%I', myschema.schema_name, myseq.sequence_name);

            EXCEPTION
            WHEN dependent_objects_still_exist THEN
            RAISE WARNING 'Could not drop object %.% because of dependencies', myschema.schema_name, myseq.sequence_name;
        END LOOP;

        -- Drop all views
        FOR myview IN (SELECT viewname FROM pg_catalog.pg_views WHERE schemaname=myschema.schema_name)
        LOOP
            EXECUTE format('DROP VIEW %I.%I', myschema.schema_name, myview.viewname);

            EXCEPTION
            WHEN dependent_objects_still_exist THEN
            RAISE WARNING 'Could not drop object %.% because of dependencies', myschema.schema_name, myview.viewname;
        END LOOP;
    END LOOP;

END;
\$$ LANGUAGE plpgsql;


texts: What is the naming convention where column names are prefixed by chars indicating the data type?

texts:
I work on a database where column names are prefixed by a character representing the datatype of the column. I quite like this, because it's easy to see at a glance what type of data each column holds.

For example, in the model City associated with many People, you might have the following relations (this is an example of column names):

Person

id
sName (string)
sSurname (string)
iAge (int)
dtDateOfBirth (date)
bIsLiving (bit)
dSomeDecimalColumn (decimal)
hCity (means foreign key reference to the 'City' relation)

City

id
sName
dLat
dLong
etc

The database where I have seen this standard is Yardi. Someone told me this was a standard they had come across before and that it was associated with Bulgaria. But they couldn't be more precise


texts: Log Shipping vs Data Replication in SQL Server 2012

texts:
Just wonder if anyone has tried to use Log Shipping on SQL Server 2012 collecting data from SQL Server 2008R2 and SQL Server 2005 databases? I heard it would be an easier option to implement compared to database replication.

Trying to run a project. Not sure to use Database Replication or Log Shipping. This is just a central database for reporting purposes only.


texts: postgresql replace table without losing dependancies

texts:
i want to replace an existing table with a new one, without losing foreign keys or inheritance relations. But I feel lazy to alter it to make it look like the new one. Is there a way to replace the table without doing all the hard job? Like a CREATE OR REPLACE statement in SQLITE or by tweaking the table name in pg_class table?

CREATE TABLE a (
  id serial NOT NULL,
  -- a bunch of other columns
  CONSTRAINT a_pk PRIMARY KEY(a)
);

CREATE TABLE b (
  id serial NOT NULL,
  a_id bigint refernces a(id)
);

--REPLACE a with a new table without violating b FOREIGN KEY relations(somehow)
CREATE OR REPLACE a(
  id serial NOT NULL,
  -- a bunch of new columns
  CONSTRAINT a_pk PRIMARY KEY(a)
);


texts: How to break table into two without losing performance?

texts:
According to https://stackoverflow.com/a/174047/14731, splitting away infrequently-needed columns frees up the cache allowing faster retrieval of the commonly-used columns.

I've got a table whose columns are always retrieved together, but I'd still like to split them for design reasons (reduce duplication across multiple tables, improve code reuse). For example, I've got different tables that use the same permission scheme. Instead of adding permission columns to each table, I'd like to use a foreign key to reference a separate permission-scheme table.

I've populated MySQL with 1 million rows, ran queries against both versions, and discovered that the version with a JOIN is ~3x slower (0.9 seconds vs 2.9 seconds).

Here are my tables:

original
(
    id BIGINT NOT NULL,
    first BIGINT NOT NULL,
    second BIGINT NOT NULL,
    third BIGINT NOT NULL
);
part1
(
    id BIGINT NOT NULL,
    first BIGINT NOT NULL,
    second BIGINT NOT NULL,
    PRIMARY KEY(id)
);
part2
(
    link BIGINT NOT NULL,
    third BIGINT NOT NULL,
    FOREIGN KEY (link) REFERENCES part1(id)
);

Here are my queries:

SELECT first, second, third FROM original;
SELECT part1.first, part1.second, part2.third FROM part1, part2 WHERE part2.link = part1.id;

Is there any way to reduce the performance overhead of the split design?

If you want to reproduce this test on your side, you can use the following Java application to generate the SQL script to populate the database:

import java.io.FileNotFoundException;
import java.io.PrintWriter;

public class Main
{
    public static void main(String[] args) throws FileNotFoundException
    {
        final int COUNT = 1_000_000;
        try (PrintWriter out = new PrintWriter("/import.sql"))
        {
            for (int i = 0; i < COUNT; ++i)
                out.println("INSERT INTO original VALUES (" + i + ", " + i + ", 0);");
            out.println("INSERT INTO original VALUES (" + (COUNT - 2) + ", " + (COUNT - 1) +
                ", 1);");
            out.println();
            for (int i = 0; i < COUNT; ++i)
            {
                out.println("INSERT INTO part1 (first, second) VALUES (" + i + ", " + i + ");");
                out.println("INSERT INTO part2 VALUES (LAST_INSERT_ID(), 0);");
            }
            out.println("INSERT INTO part1 (first, second) VALUES (" + (COUNT - 2) + ", " +
                (COUNT - 1) + ");");
            out.println("INSERT INTO part2 VALUES (LAST_INSERT_ID(), 1);");
            out.println();
        }
    }
}


texts: MySQL: Index when joining to tables not being used (Performance optimizing question)

texts:
I need to optimize the following query:

SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM blogposts
JOIN articles ON articles.blogpost_id = blogposts.id
WHERE blogposts.deleted = 0
AND blogposts.title LIKE '%{de}%'
AND blogposts.visible = 1
AND blogposts.date_published <= NOW()
ORDER BY blogposts.date_created DESC
LIMIT 0 , 50

EXPLAIN SELECT gives me the following result:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE articles ALL blogpost_id NULL NULL NULL 6915 Using temporary; Using filesort
1 SIMPLE blogposts eq_ref PRIMARY PRIMARY 4 articles.blogpost_id 1 Using where

Why does it first take the articles and then the blogposts? Is it because blogposts have more entries?
And how can I improve the query so that the articlepost can use an index?

Update:
An index is set on blogposts.date_created.
Removing the blogposts.title LIKE condition and the date_published <= NOW() doesn't do anything.

When I remove the "articles.id AS articleid" it can use the blogpost_id Index on articles...
Sounds strange to me, someone knows why? (because I actually need it..)

The new explain looks like this:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  articles    index blogpost_id blogpost_id    4    NULL    6915    Using index; Using temporary; Using filesort
1   SIMPLE  blogposts   eq_ref  PRIMARY PRIMARY 4   articles.blogpost_id    1   Using where


texts: Set MySQL option to restore MySQL server after deleting ibdata1

texts:
ON an Ubuntu Server 12.10 machine, I had attempted to delete the ibdata1 file before reading Rolando's explanation about how exactly to do so. Specifically, I had not set innodb_fast_shutdown = 0 before shutting MySQL down.

How might I start MySQL, now that ibdata1 has been deleted but innodb_fast_shutdown had not been set?

I have backups of all the databases, but I cannot reinstall MySQL since the Ubuntu version is no longer supported. I cannot update Ubuntu to a supported version due to an accessibility issue in the current versions, nor can I downgrade to 12.04 LTS (still supported) since Ubuntu was never designed to be downgradable and some applications that I need won't run well on that older version if I do a complete reinstall as they depend on a string of dependencies not in 12.04 but in 12.10.


texts: MySQL optimization question -- how to speed up a simple query

texts:
I have a very simple database, one table with five columns (one INTEGER key, two VARCHAR, a DATE, and a DOUBLE). The table contains 909,000 records.

A simple query like

SELECT t.price
FROM commoddb.tbcommodprices t
WHERE
      t.ticker='LAV05 Comdty'
   && t.whichprice='last'
   && t.date = '2005-09-01'

takes over 1.6 seconds to respond to a local query, which seems very slow. I would appreciate any suggestions for ways to speed this up.


texts: What's a good tool for visualizing SQL Agent jobs?

texts:
I find the built-in MS SQL Studio tools for managing Agent jobs to be a little frustrating.  What tools out there have you found helpful?

Offhand, there are three things I'd like to see in such a tool:

A graphical summary of which jobs ran when, for how long, and whether they succeeded.
A current status view, like the Job Activity Monitor, but refreshed in near-real time.
A more convenient interface for duplicating or modifying jobs (e.g., compare two job steps without being blocked by modal dialogs).

It would probably be simple to write a little app to handle this, but someone's surely already done it, and done it better.

This is obviously a subjective question, so if a mod of some sort wanders past, feel free to make it a CW.


texts: How to use VARRAY in where clause of a select query in ORACLE

texts:
I got a trouble in executing a select count (*) query having a where clause. I put codes into a procedure.

I declare three arrays (VARCHAR2):  v_nameTableArray, v_nameColumnArray,  v_valueArray.

And then I assign value to three arrays.

v_nameTableArray:= nameTableArray('Dimension_Product', 'Dimension_Store', 'Dimension_Customer','Dimension_Supplier');

v_nameColumnArray:= nameColumnArray('PRODUCT_ID', 'STORE_ID', 'CUSTOMER_ID','SUPPLIER_ID');

v_valueArray:= valueArray('1', '2', '3','4');

My aim is checking exists these values in each table corresponding to name of columns. When I try to run Procedure, I got errors:

•   Error(19,4): PL/SQL: SQL Statement ignored
•   Error(21,39): PL/SQL: ORA-22905: cannot access rows from a non-nested table item

Sentence error is:

when exists(select count(*) from v_nameTableArray(i) where  v_nameColumnArray(i) = v_valueArray(i)).

Can I anyone guide me how to fix issue? Thank you!

CREATE OR REPLACE PROCEDURE SP_CallArray1
IS
  type nameTableArray IS VARRAY(4) OF VARCHAR2(10);
  v_nameTableArray nameTableArray;

  type nameColumnArray IS VARRAY(4) OF VARCHAR2(10);
  v_nameColumnArray  nameColumnArray;

  type valueArray IS VARRAY(4) OF VARCHAR2(10);
  v_valueArray  valueArray;

  v_checkExists NUMBER(1);
BEGIN
 v_nameTableArray:= nameTableArray('Dimension_Product', 'Dimension_Store', 'Dimension_Customer','Dimension_Supplier');
  v_nameColumnArray:= nameColumnArray('PRODUCT_ID', 'STORE_ID', 'CUSTOMER_ID','SUPPLIER_ID');
  v_valueArray:= valueArray('1', '2', '3','4');

    FOR i in 1 .. v_nameTableArray.count LOOP
            select
                case
                    when exists(select count(*) from v_nameTableArray(i) where  v_nameColumnArray(i) = v_valueArray(i))
                    then 1  -- yes
                    else 0  -- no
                end into v_checkExists
            from DUAL;
            DBMS_OUTPUT.PUT_LINE ('---------------' || v_checkExists );

     if v_checkExists = 1 then
        update ….
     else
        insert into…
     end if;
  END LOOP;
END;