texts:
I have a table that has 4 million records. It has a clustered index on a date column (record creation date). It has 5 tables reference this table, all have FK indexes.
The machine has no down time. I had a program that clean up records that older than 31 days. It create a connection, Delete TOP 1000 rows, close the connection, and repeat until all old records are removed.
The delete has been very slow, it is deleting about 1000 rows per 10 sec. Ideally I want to do 1000 row per a second.
I notice that during the delete, it is performing a lot of page lock on the index.
I am wondering if there are faster way to delete data without causing timeout.
My idea is that would it be better if I do a table lock, perform the delete, wait for it for a sec so that it doesn't timeout other transaction, then perform the delete again.
My guess is that if I do a table lock, it should reduce the number of row locks or page locks, which may speed up the delete.
Any suggestions on this issue I have would help.
Please note that the harddrive or database isn't fragmented, and it is a RAID 10 machine.
[Update]
Thanks for asking for the performance execution plan. It looks like the live environment is different than my development environment. It is doing index scan rather than index seek. I think I have to investigate more about why it would do a index scan.

[Update 2] Here is the index that we have for some of those tables. Our index naming convention is [TableName]_[ColumnName], sorry we didn't use MSSQL naming standard. In addition, it turns out that client has a 96% fragmented index (VehicleLocationTP_VehicleLocationKey), that definitely is one of the problems. It may be a reason why SQL2005 would use index scan, rather than index seek.

[Update 3] I finally able to test the delete query on their testing server, instead on my own computer. They are running SQL 2005 Standard, verse SQL 2008 R2 Express on my machine.  The indexes where about 95% fragmented, and rebuilding the index has improved the delete from 25-50%. It is hard to do performance test when the their SQL Server is constantly running. However, checking the actual execution plan, it is the same as the estimated one. So you are right that fragmentation doesn't affect the execution plan. My guess is that it could be the number of rows in the table. Maybe if the table is small, it would uses index scan, rather than index seek.
In addition, this article give me a bit more insight on why it is a index scan Index Scan vs Index Seek
When the execution plan showing index scan, it really scanning the entire table. It calls it a index scan because VehicleLocationAPC is a cluster indexed table. This remove a bit of confusion. It means that index weren't use, it was doing an entire table scan.
Another thing to realize is that the content of the data in VehicleLocationAPC. VehicleLocationKey are almost unique all the time. Our application generation one VehicleLocationAPC row per VehicileLocation row. My guess is that because of this, SQL Server would rather scan the entire table, instead of using the index... but I could be wrong, because I would have thought that the index are sorted in a b-tree, which should be faster to scan the key, rather than doing a table scan.
My focus turns to VehicleLocationTP, this is the table that is causing 63% of the estimated time, and this table is huge.


texts: Can I build relationships in existing system without breaking it?

texts:
This is my first question here, so I would love help to make it work for SO:DBA.

I inherited an older relational system that is sitting on a SQL Server 2005 box and the database is at SQL2000 compatibility.  In my initial inspection, this system is loaded with cursor functions and very cumbersome code (including *= joins).  I have a feeling that this is because every table has one primary key column that is non-identity, and there are no PK-FK relationships built into any but two tables.  This column happens to be the same value, DocID.  There are few if any indexes outside the clustered index on the "PK" that all tables have.  I am finding 2nd and even 1st normalized tables in several locations.  There is no data dictionary that anyone knows of and the original builders of the system are now long gone, the company that built this system has been turned over 3-4 times and does not support this system anymore.   There is only the one box, and a "test" environment is another database structure on the same server, and I do not have the ability to create a secondary server to test/build a solution.  The last little wrinkle is that my company has decided to replace the system, and if nothing else, I will need to extract a great deal of baseline data from this system for import into the new system within the next 6-8 months, if nothing else, to leave the old system.

Currently, there are issues with severe slowdowns and from I/O requests and long lock conditions due to connection conditions and several massive stored procedures that touch most if not all the tables in rapid succession.  That is what I am tasked to eliminate.  I have already begun a manual mapping of the system's architecture from within SQL Server.

I have not experienced a database with this degree of brokenness.  I come from the developer's side of the room, so I know how to write/refactor/update queries in set notation, but I am struggling with why the system was built without using any kind of relationship outside of query WHERE logic and such a huge number of cursor based update and inserts.  (I suppose the why is moot at this point, but it would be nice to attribute the pain of this process to some “good” reason.)

What can I do to establish the relationships so that the DBMS can actually use its power to handle the requests that currently bring the system to a crawl 3-4 times a day?  Would using the SQL Server 2005 Relational Diagram tool be useful for building the relationships and examining the index potential for the columns in each table?  If I use the tool, will the relationship changes be done without "breaking" the legacy processes?  For example, will the stored procedures with cursors still function correctly, if I re-key and index the table they are working on without adapting the Stored Procedure?


texts: Retaining access to table that will be dropped and recreated

texts:
I have a small GIS table that is is dropped and recreated from a third-party tool called MAPINFO.

Each time I drop and recreate that table I grant access to roles created. Is it possible to retain access even after drop and create?

I cannot run SQL through Mapinfo. It does not provide truncate table - it just drops the table and creates a new one.


texts: Does restarting Windows restart the SQL Server?

texts:
Doesn't restarting Windows also restart the SQL Server?
Long story is that I just made the change so that both SQL Server and Windows authenticated users can log in. The server needs a restart for the change to work, can I just restart the machine instead of the server?
My server is having configuration issues, I wanted to change one of the settings so that SQL credentials can also log in (instead of just Windows authentication ). My admin said you can do any changes, but don't restart the SQL Server. I asked can I reboot the Windows server which the SQL Server is on, and he said yes.
Doesn't restarting the machine do the same as restarting the SQL Server?


texts: Mysql Trigger use inserted value as column name for update After Insert

texts:
I have 2 tables: One for storing order_products and another one for keeping inventory levels for multiple stores

The order_products table keeps the ID for the store in the store_id column.

product_id | quantity | store_id

The inventories table has a product_id column and subsequent columns for keeping inventory levels for each store. The name of each of those columns is the store_id.

product_id | 1 | 2 | 3 |....

I'm trying to set up an Update AfterInsert Trigger for updating inventory levels of the inventories table upon succesful insert in the order_products table.

The following is the query that needs to be performed:

UPDATE inventories

SET store_id = (store_id - quantity.NEW)

WHERE product_id = product_id.NEW

How can I use the inserted store_id value from the order_products table as the store_id column value in the update query?

I thought of using prepared statements but I believe they are not allowed for triggers.

Thanks!


texts: Diskbased to in memory tables sql server 2016 ram requirement

texts: I am planning to migrate my tables to memory optimized tables. Currently, I have a total of 250 gigabytes database size on disk. If I migrate this to in memory tables, how much physical RAM will I need?


texts: Restoring PostgreSQL 9.1.9 backup with 9.3.0 results in error

texts:
How can I resolve the following errors in 9.3.0:

psql mydb < ~/mydb_2013-10-25.backup.tar

results in

...
ALTER TABLE
ALTER TABLE
ALTER TABLE
ERROR:  could not open file "$$PATH$$/2309.dat" for reading: No such file or directory
 setval
--------
      1
(1 row)
...

I see the file 2309.dat in the tar, so I am slightly confused what can be the reason. I run 9.3.0 on OS X and made backup with 9.1.9 on Ubuntu.

Edit: I managed to restore data with pgAdmin3-1.18.1.


texts: Update trigger fired before insert trigger upon insert of new row

texts:
I have a table FOO and 2 triggers defined as follows

CREATE TRIGGER [dbo].[FOO_UPDATED]
   ON [dbo].[FOO]
   AFTER UPDATE
AS
BEGIN
   ...
END

and

CREATE TRIGGER [dbo].[FOO_INSERTED]
   ON [dbo].[FOO]
   AFTER INSERT
AS
BEGIN
   ...
END

The triggers were working fine on SQL Server 2012 - FOO_INSERTED was firing upon insert and FOO_UPDATED was firing upon update.

But now I migrated the database on SQL Server 2016 (no change in the structure and data, simple backup and restore). Upon insert of 1 new row in FOO, both triggers fire in order: first FOO_UPDATED, then FOO_INSERTED.

I've looked around for similar problems and found only that one topic: SQL Server: update trigger fires before insert trigger

Does anyone have an idea what could be the reason for this strange behavior?


texts: Passing values to variables defined in SSIS package at run time

texts:
I need to pass values to the variables in a SSIS package at run time. Obviously it is not practical to open BIDS and set a variable every time the package needs to be run. We run it on SQL Server 2008 R2.

What is the best way to do that?

For example we have 3 variables used in the package. 1 of them are populated in the packages on fly, the other 2 needs to be specified from begining:

Let's say we have these variables defined in the package:

@Rec_Count (No need to set a value at run time - it will be populated
by a dataflow automatically)
@Fetch_Size (Should be set a value when the package is kicked off)
@File_Path (Should be set a value when the package is kicked off)

How can we set the last two variables in run time by the user?

Thank you in advance.


texts: Find rows without matching second field for given constants in first field

texts:
Consider an example table with two string fields FIRST_NAME and LAST_NAME.

For a given Lastname == Smith, I find to expect a row with another given Lastname == Johnson and identical Firstname.

How can Find rows for which this isn't the case, i.e. Lastname == Smith, Firstname == Julia exists, but Lastname == Johnson, Firstname == Julia does not?.

Sample Data:

FIRST_NAME, LAST_NAME
Jane, Smith
John, Smith
Blake, Smith
Jane, Johnson
Blake, Johnson

Query so far:
SELECT FIRST_NAME FROM (SELECT DISTINCT LAST_NAME, COUNT(*) AS c, FIRST_NAME FROM Table1 GROUP BY FIRST_NAME) WHERE c = 1

What I'd like to get:

John, Smith


texts:
I use the maintenance plan of SQL Server, but it does not work well.

Transaction log truncation flow in the maintenance plan:

① Database consistency check task
↓
② Database compression task
↓
③ Database backup task
(Transaction log backup)
↓
④ Truncate transaction log area (execute T-SQL)

If run the maintenance plan manually in the manual server, it will successfully terminate the transaction log area and reduce the size.

The size is not reduced while the transaction log area is truncated, even though the schedule is successfully terminated.

Is there any problem with the execution of the maintenance plan?

USE [XXX system _SQL]
GO

DBCC SHRINKFILE (XXX system _SQL_log, TRUNCATEONLY)
GO


texts: Creating joined rows from multiple tables on date field


texts: Mysql Not Null Columns accepting null values

texts:
I am having trouble with Mysql not null columns. It seems my mysql installation is accepting null values for NOT NULL columns.

My mysql version is 5.6.25-1~dotdeb+7.1(debian).

Take this table for instance:

CREATE TABLE `cities` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `state_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5562;

When I insert a value like this:

insert into cities(state_id) values (20);

Mysql spills a warning but commits the value anyway. Here is the warning.

12:51:43    insert into cities(state_id) values (20)    1 row(s) affected, 1 warning(s): 1364 Field 'name' doesn't have a default value 0.000 sec

If I remove the Unique Key unique_city_in_state I get the same behaviour.

I also tried creating the name column with a DEFAULT NULL, like so:

  `name` varchar(255) NOT NULL DEFAULT NULL,

This spills a error that would roughly translate to (Default value invalid for name).

I tried on a different mysql installation 5.1.73-1 (Debian) and I have the same behavior.

Now if I do this

insert into cities (name, state_id) values (null, 19);

I get the ERROR 1048 (23000) meaning the column name cannot be empty.

Any help is welcome.


texts:
I am working school homework question. I have an inpatient table, I have to determine the top 10 ICD code that have the highest frequency.  Please  output  each  code  with  its  frequency  (i.e.  occurrence)  in  the descending order.
My SQL code is:

select ICD9_DGNS_CD_1, ICD9_DGNS_CD_2, ICD9_DGNS_CD_3, ICD9_DGNS_CD_4,ICD9_DGNS_CD_5,ICD9_DGNS_CD_6,
ICD9_DGNS_CD_7,ICD9_DGNS_CD_8,ICD9_DGNS_CD_9,ICD9_DGNS_CD_10, count(*) AS frequency
from seis735_db.ip
group by ICD9_DGNS_CD_1,ICD9_DGNS_CD_2,ICD9_DGNS_CD_3,ICD9_DGNS_CD_5,ICD9_DGNS_CD_6,ICD9_DGNS_CD_7,
ICD9_DGNS_CD_8,ICD9_DGNS_CD_9,ICD9_DGNS_CD_10
order by count(*) desc
LIMIT 10;

This outputs the top 10 in each ICD code diagnoses. I am trying to find just the top 10 from all the 10 columns? I am new to sql and I am having a hard time writing the query for this question.

Any help will be much appreciated. Thank you!


texts:
I can't seem to dump/restore on Windows with my database. Lines used and error messages:

pg_dump -h localhost -U postgres --format=c -O -d ue > latest.dump
pg_restore -h localhost -U postgres -d ue latest.dump

Getting this error,

  pg_restore: [archiver] input file does not appear to be a valid archive

With this,

pg_dump -h localhost -U postgres -O -d ue > latest.dump
psql -h localhost -U postgres -d ue -f latest.dump

I get this error,

  psql:latest.dump:1: ERROR:  invalid byte sequence for encoding "UTF8": 0xff

I'm completely stumped. Most of the search results relate to not using the correct pg_restore/psql with the file type exported by pg_dump, but as you can see above, I've accounted for that.

latest.dump seems to be filling up correctly, at least in the plain format it exports SQL that I can read and is the correct export of the database.


texts: SQL Server Report Manager 404 using HTTPS

texts:
OK. I have scoured the Internet for information on trying to fix this and found nothing that deals with my situation.
I started here, that lead me to here, that lead me to looking at netsh commands for HTTP.

Understand: I have no problem what-so-ever accessing the Report Manager or Web Service using HTTP/80.

My problem is strictly getting HTTPS/443 to work and open up the exact same page that HTTP/80 uses. All I get is HTTP Error 404.

Build number for SQL and SSRS: 10.50.4270.0
My certificate is installed on the server
Windows Firewall is disabled, but I am just trying to bring HTTPS up locally on the server
I have configured everything as instructed by Microsoft in RSCM.
SSL configuration has been done through RSCM, and using that URL to attempt access
I have verified in netsh that the SSL certificate is
binded on IP:port 443.
The registry keys under
HKLM:\SYSTEM\CurrentControlSet\services\HTTP\Parameters\SslBindingInfo
are present for 0.0.0.0:443.
In netsh show iplisten was not showing anything, so I add iplisten ipaddress=0.0.0.0, but that did not affect anything
Within my rsreportserver.config the SecureConnectionLevel is set to value="0"

I am stuck, what am I missing here to do or check?

EDIT
I just completed a fresh install on a brand new server, same result.


texts: 'Select @VAR =' and 'Set @VAR =' behaving unexpectedly

texts:
Working on a database i didn't setup myself i was digging through some stored procedures to find out what they were doing.

In here i found numerous variables being set through a SELECT @var =, I never really did such a thing and often only used SET @var =.

Reading up on the documentation Microsoft TSQL as well as Stackoverflow it seemed to me they are similar in their functionality yet not entirely.

So I decided to test it out with

DECLARE @var1 varchar(200)
SELECT @var1 = 'Generic Name'
SELECT @var1 = (SELECT Name FROM Users)
SELECT @var1 AS 'Company Name'

DECLARE @var2 varchar(30)
SELECT @var2 = 'Generic Name'
SET @var2 = (SELECT Name FROM Users)
SELECT @var2 AS 'Company Name'

DECLARE @var3 varchar(200)
SELECT @var3 = 'Generic Name'
SELECT @var3 = Name FROM Users
SELECT @var3 AS 'Company Name'

DECLARE @var4 varchar(200)
SELECT @var4 = 'Generic Name'
SELECT @var4 = Name FROM Users WHERE Name = '2000'
SELECT @var4 AS 'Company Name'

DECLARE @var5 varchar(200)
SELECT @var5 = 'Generic Name'
SELECT @var5 = (SELECT Name FROM Users WHERE Name = '2000')
SELECT @var5 AS 'Company Name'

Based on the documentation i expected var1 to have the last value from the query and that it wouldn't throw an error. But var 1 and var 2 both throw the error

  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The above result is expected for set based on the documentation, but not for assigning variable through select based on my understanding. Var3 does produce the expected result of taking the last name returned.

Similarly var4 and var5 behave weirdly. Neither query returns results, so i'd expect both to stay their old value of Generic Name. But instead only var4 keeps its old value. Var5 is set to NULL instead. And I don't understand why. To me it seems under some circumstances SELECT @var = behaves exactly like SET @var =, and in some it acts according to documentation.


texts:
I'll admit, I'm an average query coder so I'm pretty sure my issue is "operator error"

I have 2 db tables.  The columns are in ()

table1 (form_id, form_name, field_order)
table2 (field_id, field_name)

Table1 contains all my forms.  The field_order column contains the ID's for all the field_names in table2.  So the field_order column in table 1 looks like (682,1,2,341,72,72,73)

These numbers correspond to the id's for the fields in table 2.

Where my problem is, is that in my query using the IN clause, the form fields results are listed in ASC order by field_id in table2.  I need the field_id and field_name to be listed in the field_order in table1.

Any help is appreciated.  I think I'm missing something simple.

My code is below.

<?php
$sqlForms = "select t1.form_id, t1.form_name, t1.field_ids from table1 as t1 where t1.form_id != '10' order by form_name asc";

$resultsSQL = $this->EE->db->query($sqlForms);
    if ($resultsSQL->num_rows() > 0)
    {
        echo "
";

        foreach($resultsSQL->result() as $row)
        {
        echo "
";
        echo "".$row->form_id."	".$row->form_name."
";

        $formID = str_replace("|", ",", "$row->field_ids");

        $sqlFields = "select t2.field_id, t2.field_name
                        from table2 as t2
                        where t2.field_id IN (".$formID.")";

            $resultsFields = $this->EE->db->query($sqlFields);
                if ($resultsFields->num_rows() > 0)
                {
                    echo "
";
                        foreach($resultsFields->result() as $rowFields)
                        {
                            echo "
".$rowFields->field_id."	".$rowFields->field_name."
";
                        }
                    echo "
";
                }

        echo "	";
        echo "
";
        }
        echo "
";
    }
?>


texts: Statistics injection for calculating the cost of a query

texts:
I want to find out the cost of a query by "EXPLAIN" query in Postgres. As I know, Postgres calculate this cost by using the statistics of the table. (e.g: relpages, reltuples in pg_class or some attributes in pg_statistics)
Is it possible to inject these statistics on a dummy table to get the cost without storing the data locally on the database?


texts: SQL Agent job history not found for few jobs

texts:
I have;backup  ,rebuild Indexes , Update Stats and few other jobs like space alerts, replication jobs
etc which run frequently on SQL Server 2102-SP2, Build Number:11.0.5058.0

and I do not see any log sucess/Failure for few jobs :

EXEC msdb.dbo.sp_help_jobhistory
@job_name = N'Database_Full_Backup_Daily11pm' ;
GO--NO log of Sucess or fail,

but the job ran and I see backup files stored on my backup location and I see the database last backup taken date is yesterday 11pm

and there are no other backup jobs are running at all
My backup job uses a custom script not maintenance plans
Owner of the job are same for all jobs
all jobs are enabled.
I do not see the history via Right click on job -view history also.
I see agent-properties-history--Limit size 1000, 100 chosen

but for few like below, I see history

EXEC msdb.dbo.sp_help_jobhistory
@job_name='Replication agents checkup'
GO----I see history

EXEC msdb.dbo.sp_help_jobhistory
@job_name = N'Distribution clean up: distribution' ;
GO----I see history

what could be wrong here ? any ideas experts?
Please help


texts: SQL Server suspended queries - tempDb pagelatch_up 2:1:2

texts:
I have performance issue on MS SQL 2014 db.
Store procedure that does SELECT has input parameters as custom Table Value Parameter type.
There are 50-100 sessions at the time and executing store proc can take 1s to 14s.
I can execute SP with simple input parameter and it can take 1s if I repeat the same it can take up to 14s, it almost if I am feeling lucky or not.
Running diagnostic queries I can see PAGELATCH_UP on many calls to this store procedure and there is a chain of one session id waiting on another session id.
PAGELATCH_UP is in the format of resource 2:1:2 2:6:2 2:9:2 depends from the time of capture.
So 2:: is tempDb :1: or :6: are tempDb files, there is 8 of them and this is 128GB 32core machine.
For the last one ::2 I could found out it is Global Allocation Map (GAM).
In all blogs people have usually have issue with resource ::1 or ::3 (PFS and SGAM), but haven't seen anything regarding GAM.
There is a recommendation from Microsoft how to resolve contention in tempDB which focuses on PFS and SGAM:
https://support.microsoft.com/en-gb/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-d
Would you suggest increasing number of tempDb files from 8 to 12, 16, ...32?
Is it issue with table-valued parameters, and this is the reason for pagelatch_up as this is select statement in SP?
Any other replacement for Table Value Parameters?


texts: Does MySQL store point datatypes as LAT LNG or LNG LAT?

texts: I'm used to seeing location format as latitude followed by longitude, but using libraries, I believe I understand MySQL to store it as POINT(LNG LAT), the reverse order. Is my library in the wrong, or is this the actual format? I can't seem to find this detail in the MySQL documentation.


texts: Getting Records with Min Max Value on Grouped in Single Table in postgresql

texts:
The table looks like this:

Existing Table:

table_1

Desired Result:

table_1

I have been able to get result like the following using this code:

table_1

But I would like to get minimum and maximum value on 'trans' row and their corresponding value on 'rate' row, "Grouped By date" on Single Table as following.

table_1


texts: How do I shrink the physical Transaction Log file when it's the principal in a mirror?

texts:
We setup database mirroring over the weekend, and forgot to re-enable the job that backs up the transaction logs. When I came in this morning, the transaction log had ballooned to 58GB, and was taking up most of the drive space.

I did a manual backup of the transaction log to disk to get the database running again, however running DBCC SHRINKFILE does not appear to decrease the physical size of the transaction log file.

DBCC SHRINKFILE (N'MyDatabaseName_Log', 1000)

If I check out the log usage using

DBCC SQLPERF(LOGSPACE)

I can see that only 22% of the current log is being used

Database Name    Log Size(MB)    Log Space Used (%)    Status
MyDatabaseName   55440.87        22.38189              0

If I check out log_reuse_wait_desc in sys.databses, the only record I see is DATABASE_MIRRORING, so I am guessing the mirror is playing a role in why the physical size of the log file won't shrink?

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = N'MyDatabaseName';

I've also noticed my principal database mirroring state is Suspended, and trying to Resume it immediately fails with the following error:

  The remote mirroring partner for database 'MyDatabaseName',
  encountered error 5149, status 1, severity 25. Database mirroring has
  been suspended.  Resolve the error on the remote server and resume
  mirroring, or remove mirroring and re-establish the mirror server
  instance.

The error logs on the mirror server contains this error as well, but also contains errors about the log file drive being full

  MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file.

and

  F:\Databaselogs\MyDatabaseName_1.ldf: Operating system error 112(There is not enough space on the disk.) encountered.

The principal server has 60GB on the log file drive (there are other databases hosted here), while the mirrored server only has 45GB.

Backing up the log file made the database usable again, however I also want to decrease the size of the physical log file on the disk, and get the mirroring resumed.

How can I shrink the size of my physical transaction log file without compromising the mirroring or the backup chain?

I am running SQL Server 2005


texts: How to include "Actual Row" counts in Execution plans pulled from the Query Cache or Query Store?

texts:
Obviously seeing the actual execution plan is a big deal when tuning, and normally I enable the query plan output via SET STATISTICS XML ON and run whatever query needs some TLC, but how can I see actual record counts for historical runs of a plan or for a process I cannot easily run manually (or simulate in a test environment)?

When I pull this information from the query cache via either sys.dm_exec_query_plan or sys.dm_exec_text_query_plan only estimated row counts are shown.  The same behavior exists when using the Query Store DMV, sys.query_store_plan.  Since all of these DMVs are pulling actual plans that were used, I would expect to see actual row execution counts included within the graphical representations of the plan, but they're not there.

The information returned from the sys.dm_exec_query_stats DMV is only somewhat useful as it returns total counts for the statement, but detailed operator counts within the plan seem to be hidden with historical plans.  With 2014, we get the sys.dm_exec_query_profiles DMV to use, which helps with plans currently being executed, but this also doesn't help when looking at historical executions.

Is this information stored elsewhere?  Should I treat the Estimated Row counts as Actual Count numbers (I doubt it, but since I'm asking...)?  Is there a connect item requesting this feature out there I can upvote?


texts: Unknown table 'table' in field list on referencing temporary table inside a trigger

texts:
I am creating a trigger BEFORE INSERT that checks a record from another table to see if it exists, otherwise signal sqlstate. The trigger definition shown below
DELIMITER //
DROP TRIGGER IF EXISTS pob_exists_barang_kode//
CREATE TRIGGER pob_exists_barang_kode BEFORE INSERT
ON `PURCHASE_ORDER_BARANG` FOR EACH ROW
BEGIN
    declare msg varchar(128);
    CREATE TEMPORARY TABLE kode_barang_tbl
    SELECT ven.`KODE_BARANG`  as kode_barang
    FroM  (
            SELECT po.`REQ_VENDOR_ID` as rvid
            FROM `PURCHASE_ORDER` po,
                 `PURCHASE_ORDER_BARANG` pob
            WHERE NEW.`PURCHASE_ORDER_ID` = po.`ID`
          ) `asdf`,
          `VENDOR_BARANG` ven
    WHERE ven.`VENDOR_ID` = asdf.rvid;

    CREATE TEMPORARY TABLE dummy1
    SELECT po.`REQ_VENDOR_ID` as rvid
            FROM `PURCHASE_ORDER` po,
                 `PURCHASE_ORDER_BARANG` pob
            WHERE NEW.`PURCHASE_ORDER_ID` = po.`ID`;

    IF NEW.barang_kode not in (kode_barang_tbl.kode_barang) THEN
        set msg = concat('Kesalahan! Tidak ada kode barang ',
                          cast(new.barang_kode as char),
                          ' pada vendor id ',
                          cast(dummy1.rvid as char)
                        );
        signal sqlstate '45000' set message_text = msg;
    END IF;

    DROP TEMPORARY TABLE dummy1;
    DROP TEMPORARY TABLE kode_barang_tbl;
END;//

DELIMITER ;

Trigger created fine, so I ran this script below to test if the trigger works:
insert into `PURCHASE_ORDER_BARANG` values ('po2', '1', 5135, null);

but it returns an error message
Error Code: 1109. Unknown table 'kode_barang_tbl' in field list

Executing the part of the statement works as expected:
SELECT ven.`KODE_BARANG`  as kode_barang
    FroM  (
            SELECT po.`REQ_VENDOR_ID` as rvid
            FROM `PURCHASE_ORDER` po,
                 `PURCHASE_ORDER_BARANG` pob
            WHERE po.`ID` = 'po2'
          ) `asdf`,
          `VENDOR_BARANG` ven
    WHERE ven.`VENDOR_ID` = asdf.rvid;

I'm not really sure which line causes the error, but I am sure I have created the temporary tables and the trigger correctly. I also have no table kode_barang_tbl inside the database except in this trigger. Am I missing something? Any help is appreciated.


texts: What are the limits of Postgresql's large object facility?

texts:
I've not found an equivalent of the about PostgreSQL page that covers the limitations of the large object facility in particular, but reading between the lines I think the following is true:

No more than 32TB of large objects can be stored (reason: they are all stored in a single table named pg_largeobject, and the per-table size limit is 32TB (assuming default page size)).
No more than 2^32 large objects can be stored (reason: the primary key of pg_largeobject is an oid which is a 32-bit quantity).
pg_largeobject cannot be partitioned (reason: because it's a system catalog?)

Is this all correct?


texts: Is there any way to group only syntax errors from exceptions given by oracle server?

texts: Basically the exceptions thrown by oracle are in the form, say for example 'ORA-01756','ORA-00907' etc. I would to know if there is a way to filter out just syntax errors may be something like a list of all such ORA related errors


texts: MySQL - MySQLDump problems with large table

texts:
I have a MySQL database that is 10.6GB - that has both MyISAM & InnoDB tables.
One particular table (called 'responses') is 8Gb - this table is InnoDB.

The live database is replicated to a different server & I take backups using MySQLDump from the replication server... the live server runs Windows, but the replication server runs CentOS.

The MySQLDump command is:

mysqldump --verbose --lock-tables=true --max-allowed-packet=1024M --host=192.168.1.182 --user=myusername --password=mypassword --opt --databases databasename > databasename.sql

The command runs until half-way through the 'responses' table - when we get:
Error 2013 - Lost connection to MySQL server during query when dumping table responses at row 12891212

After some Googling, I tried added the following commands to the MySQLDump (independently & at the same time):

--net-buffer-length=32704

and

--skip-extended-insert

The error still occurred at the same place.

For completeness, I've tried dumping the database in NaviCat and MySQL Workbench - both return the same error.

So, I wondered if it was an issue with corruption. I tried to run the following
 (on the replication server):

CHECK TABLE responses;

and

CHECK TABLE responses EXTENDED;

Both returned the following:
Error 2013 - Lost connection to MySQL server during query.

The live database is VERY busy, so I'm a bit worried... I started CHECK TABLE on the live table, but unsurprisingly, it caused the application to stop so I had to stop it.

Any advise would be welcome here... the ultimate goal is to get the backups using MySQLDump working.

Thanks

Update: I tried copying the table (on the replication server) to a new database on another server using NaviCat - it failed with the same message (transferred records: 12,889,214):
Error 2013 - Lost connection to MySQL server during query


texts: SQL Availability group IO prioritisation

texts:
How does SQL Server handle IO in terms of availability group secondaries?
To give an example scenario:
A 2 node cluster with 2 Availability groups
Each AG is primary on a different node.
Therefore each node is a primary for one AG but a secondary for the other.
Will IO for the primary workload be prioritised over the replicated data, assuming all disks are shared?
Also would this differ if the secondary was in sync or async mode?


texts: How to get Column Names list if row IS NOT NULL in PostgreSQL

texts:
can i get  'colNew_name' from a select query like table below?

original table:

category_name | col1_name  | col2_name  |
:------------ | :--------- | :----------|
postgres      | 1          | null       |
SAM           | null       | 1          |
MariaDB       | 1          | 1          |

result table:

category_name | col1_name  | col2_name  |      colNew_name   |
:------------ | :--------- | :----------| :------------------|
postgres      | 1          | null       |     col1_name      |
SAM           | null       | 1          |     col2_name      |
MariaDB       | 1          | 1          | col1_name,col2_name|


texts: SQL Server: Creating a user database with permissions

texts:
At work, I am tasked with creating a user database.  It needs to account for (end)user permissions for 8 different installed programs.  The database will be hosted by each client containing their specific information behind a firewall.  I have the majority of it built out (tables below), but I don't know how to incorporate permissions.

I could put all available permissions in a table, and then link them to users/groups and applications in another table.  Or I could put all available permissions in a table, then link them to users/groups in another table, and then applications in yet another table.  Problem is that both of these options don't play well with the applications because I cannot think of a way to actually search for a specific permission (ex: call to database to find if user can do 'x') without the programmer hard-coding the permission Ids into the program.

I could have a table per application with each of that application's permissions as a column, but there are 70+ permissions for each application and the tables would get huge (also not very expandable if we need to add more permissions later).

So the end questions is; How do I set up permissions for end-user accounts in a way that the programmer does not have to know and hard-code the Ids into the applications?

Table - Description

Applications - Basically a list of our applications so we have an Id to link the permissions to
GlobalSettings - Settings used for all users, mostly password requirments
Groups - Lists all end-user administrator defined groups of users (this way a permission can be tied to a group of users at one time)
PreviousPasswords - Lists the last x hashed passwords for each user (the ones they can't use again)
UnusablePasswords - Lists passwords we won't let our end-users use (such as password1)
User_Group_Link - Links a User to a Group
Users - Lists all end-users and their needed data (birthday, name, etc)


texts: Replace nested "select with count" with outer join? is this possible?

texts:
SELECT c.classid , c.classname ,
      (select count(s.id) from students s where s.classid = c.classid and
      status = 'failed') as noOfFailed,
      (select count(s.id) from students s where s.classid = c.classid and
      status = 'passed') as noOfPassed
      FROM class c where c.schedule = 'MWF'

Is there a way to replace/optimize this with JOIN?


texts: Reduce SQL Server Memory used by a Database

texts:
For weeks I have been at more than 25k Page Life Expectancy, since last sunday on my UAT Server the Page Life Expectancy Dropped from ~25k to 400/500/600, I can't identify what cause this huge drop as this is a UAT Server no processes running during the weekend and nobody was connected.

Apart of this I identified that 95% of my buffer pages are allocated into one specific table on a clustered Index...

The application that access this table does a high amount of Adhoc queries without any parameterization (3rd Vendor Application, nothing I can touch...) I don't know if there is actually something I can do to leverage this RAM usage as they are consuming more than 50GB of RAM and I'm starting to face memory pressure BCHR <95% and  low Page Life Expectancy!

Version:

Microsoft SQL Server 2012 (SP3-CU1) (KB3123299) - 11.0.6518.0 (X64)
Jan  7 2016 14:39:01
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Any help will be appreciated, Thanks!


texts: SQL Server 2012 install hangs during the RunRemoteDiscoveryAction

texts:
I've tried multiple times to install SQL Server 2012 on a 2-node cluster.  I've tried multiple different media.  Each time it hangs shortly after the initial validation check.

The detail log for the install has this for the last entry before it hangs...

(01) 2014-11-17 23:55:49 Slp: ----------------------------------------------------------------------
(01) 2014-11-17 23:55:49 Slp: Running Action: RunRemoteDiscoveryAction
(01) 2014-11-17 23:55:49 Slp: Running discovery on local machine
(01) 2014-11-17 23:55:50 Slp: Discovery on local machine is complete
(01) 2014-11-17 23:55:50 Slp: Running discovery on remote machine: NODE2

The same thing happens on NODE1 when it tries to install.

The only other google article I could find on this was an unanswered msdn article with almost the same description of my problem.
https://social.msdn.microsoft.com/Forums/en-US/18018405-522f-4793-b5fd-a84835f15234/install-sql-server-2012-sp1-on-a-windows-server-2012-r2-failover-cluster-hangs-at-running?forum=sqlsetupandupgrade

I can ping each node from the other.  The Cluster doesn't appear to be displaying any errors either.  All nodes and networks are showing up and running in the cluster manager.

How can I get the installer to skip this task? What is the fix for this?


texts:
Dear Helpful SE Users,

In my quest to get more experience of Symfony I thought I'd write a quiz website which I can use at school (I know that moodle does this but it is a bit overkill for my purposes). The idea is to have quizzes that pupils can do as many times as they like. I know similar questions have been asked before and my initial idea is based on this research.

Initially I'm only interested in using it for me so it only needs a limited number of courses (the year groups that I teach) and withing each course I can have a number of categories for quizzes corresponding to the different subjects within the course.

I have no experience of designing databases consisting of many tables so please go easy on my initial idea as it may contain redundancies.

I have tried to leave it general so I can include different question types (numerical/multichoice/true-false).


texts: How to set up SQL Server 2017 as a service

texts:
I have a question about what would be the best way to set up SQL Server 2017 as a service.

I have clients that buy a SQL Server database as a service, as if it were an Azure or AWS instance, where they do not have access to the server.  They only have a user and password in the instance, and I need to hide from those users the server configurations that are accessible through SQL Server Management Studio or by T-SQL commands, such as memory allocated to the instance, number of CPUs, etc.

In SQL Server 2016 I accomplished this by removing the permission on the master..xp_msver view.  Without this permission they cannot, for example, see instance properties (right click on the server in SSMS) and some data from the Facets form. Even without this permission they can log on and doing everything they need in their databases.

But in SQL Server 2017 this no longer works, and if I deny permission from this view it is no longer possible to log in SQL Server.

I know it may not be correct to use the standard SQL Server as a service, maybe it has not been prepared for this, but is there a right way? Or what alternatives can be used?

I've spent a lot of time on Google trying to find alternatives, but for SQL Server 2017 I did not find anything that hides the server settings.

Note: My users use SQL Server Management Studio...


texts: mysql - create a summary of event data in a event list by date even with 0 events in a given time frame


texts: Sybase ASE Auditing on sp_addlogin sp_droplogin sp_modifylogin

texts:
We have requirement to enable auditing on Sybase ASE(Adaptive Server Enterprise/15.7/EBF 27351 SMP SP139 /P/ia64/HP-UX B.11.31/ase157sp138x/4041/64-bit/FBO/Tue Aug  8 07:33:11 2017) and I enabled the same on master database as below:

I was under impression that this would also enable auditing on any change in login/user(sp_addlogin, sp_modifylogin, sp_droplogin, sp_adduser, sp_dropuser) as create/drop/alter was handled at master level, but that was incorrect as this is applicable to only objects and not users/logins. Recently when I dropped one user, it was not logged in the audit event; after checking multiple documents on the Sybase documentation and different portals on the internet and also with the help of vendor, came to know that one more type of auditing(login_admin) needs to be enabled in order to capture these events in audit events.

I executed below command and was expecting that this would solve the underlying issue:

1> sp_audit "login_admin", "all", "all", "on"
2> go
Audit option has been changed and has taken effect immediately.
(return status = 0)
1> sp_displayaudit "login"
2> go
No logins currently have auditing enabled.
(return status = 0)
1>

As evident from the result of sp_displayaudit, logins are still not being audited. I am not really sure if this requires ASE reboot to take effect or I am missing something here.

Any help would be greatly appreciated.


texts: Having different Table space for different indexes of same table, will causes any performance impact?

texts:
Table Master_Table is created in App1_Schema1.
Index_I01 is created in default TABLE SPACE.
Index_I02 is created in TABLE SPACE "APP2_INDEX".

Having different Table space for different indexes of same table, will causes any performance impact ?


texts:
I have a database which has product_sell and product_buy tables, these two tables are different in a few fields and each one has own comment table

product_sell >---< product_sell_cmt
product_buy >---< product_buy_cmt

now , I want to ask which modelling approach is better ?

design four tables as showed above
design product_sell and product_buy separately with common comment table
merge two product tables in one table with unused columns in rows or even using 1 to 1 relations

since, I will use product_sell and product_buy and their comments lonely in some cases , is it useful for indexing and performance to have 4 separated tables ?


texts: Is using the MongoDB _id field index as a time range query possible?

texts:
We are storing lots of data in our Mongo DB collection. We do not customize the _id field value. I have an index on a timeStamp field in this collection. This field itself records the insertion time of the record only. Given that the ObjectID in the _id field contains a timestamp can I use MongoDB _id field default index for the same queries?

Would using the _id field be as effective as the separate timestamp index?

I am trying to improve my insertion performance as this collection takes heavy write load.

In addition to the insertion load, we run an aggregation on this collection every 5 minutes based on time range.


texts: Poor performance on a 'leaderboard' query

texts:
Consider the following 2 tables:

players:  | rooms:
id        | id
name      | type
room_id   | embed_code_id
points    |

My query needs to fetch the 100 players with the most points from a given room type and embed_code_id.

here's what I have currently:

    select `players`.`id`,
       `players`.`points`
from `players`
       inner join `rooms` on `players`.`room_id` = `rooms`.`id`
where `rooms`.`embed_code_id` = 678
  and `rooms`.`type` = 'single'
order by `players`.`points` desc, `players`.`id` asc
limit 100;

This query results in poor performance (1.5 - 10 seconds, depends on the parameters passed)

Notes:
* Both tables have about 1M records
* when i remove the ORDER BY clause, the time drops below 100ms
* I tried setting an index on players.points & players.id, which didn't help
* I tried setting an index on embed_code_id & type which also didn't help

The EXPLAIN output suggests Using index; Using temporary; Using filesort which i don't know how to fix:

1   SIMPLE  rooms   NULL    ref PRIMARY,embed_code_id,type  type    6   const,const 478797  100.00  Using index; Using temporary; Using filesort
1   SIMPLE  players NULL    ref room_id room_id 4   playgo.rooms.id 1   100.00  NULL

Can this query be more efficient or i'm missing some index?

Thanks


texts: Immediate refresh materialized views (MQT) with OUTER JOIN under DB2

texts:
DB2 accepts a materialized view based on an "OUTER JOIN" query, as long it doesn't refresh automatically (REFRESH IMMEDIATE).

I cannot find a simple solution to get round this problem. Perhaps with (a lot of) triggers, but it isn't really nice...

As example, my database is

CREATE TABLE PHONEUSER (USERID INT NOT NULL PRIMARY KEY, NAME VARCHAR(30));
CREATE TABLE PHONECALL (CALLID INT NOT NULL PRIMARY KEY, USERID INT, HOUR TIMESTAMP);

INSERT INTO PHONEUSER VALUES (1, 'Olivier');
INSERT INTO PHONEUSER VALUES (2, 'Arthur');

INSERT INTO PHONECALL VALUES (1,1,CURRENT_TIMESTAMP);

and I want each call of each user. If an user has never called, I want a null value for him (it has sense in my application):

SELECT PU.USERID, CALLID, HOUR FROM PHONEUSER PU LEFT JOIN PHONECALL PC ON PC.USERID=PU.USERID;

USERID  CALLID  HOUR
------  ------  -----------------------
     2    null  null
     1       1  2013-04-09 16:36:08.952

This query runs well, but cannot be used for an MQT with refresh immediate:

CREATE TABLE CALLLIST AS (SELECT PU.USERID, CALLID, HOUR FROM PHONEUSER PU LEFT JOIN PHONECALL PC ON PC.USERID=PU.USERID) DATA INITIALLY DEFERRED REFRESH IMMEDIATE ;
The fullselect specified for the materialized query table CALLLIST is not valid. Reason code = "10".. SQLCODE=-20058, SQLSTATE=428EC, DRIVER=4.7.85


texts: pgbench - 20-30% variation in benchmark results (non-repeatable benchmarks)

texts:
Problem

I'm trying to use pgbench to help me tune Postgres for my hardware, but I'm seeing a very strange behaviour. I cannot seem to get a stable number for TPS in consecutive runs of pgbench. Since I was running pgbench for 60 secs, I assumed that this is because of checkpointing and auto-vacuuming, so I made the following changes to my config:

autovacuum=off
max_wal_size=5GB

But this led to even more wildly varying numbers! For example, here's the output of the exact same pgbench command run consecutively:

Output 1

starting vacuum...end.
progress: 5.0 s, 566.0 tps, lat 10.577 ms stddev 2.788
progress: 10.0 s, 513.0 tps, lat 11.689 ms stddev 2.907
progress: 15.0 s, 513.8 tps, lat 11.680 ms stddev 2.995
progress: 20.0 s, 519.6 tps, lat 11.546 ms stddev 2.969
progress: 25.0 s, 518.4 tps, lat 11.576 ms stddev 2.929
progress: 30.0 s, 518.2 tps, lat 11.576 ms stddev 2.978
progress: 35.0 s, 522.8 tps, lat 11.472 ms stddev 2.966
progress: 40.0 s, 521.0 tps, lat 11.516 ms stddev 2.962
progress: 45.0 s, 521.2 tps, lat 11.510 ms stddev 2.909
progress: 50.0 s, 581.6 tps, lat 10.313 ms stddev 2.636
progress: 55.0 s, 520.8 tps, lat 11.526 ms stddev 2.919
progress: 60.0 s, 522.2 tps, lat 11.494 ms stddev 2.927
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 2000
query mode: simple
number of clients: 6
number of threads: 6
duration: 60 s
number of transactions actually processed: 31699
latency average = 11.357 ms
latency stddev = 2.938 ms
tps = 528.185674 (including connections establishing)
tps = 528.269291 (excluding connections establishing)

Output 2

starting vacuum...end.
progress: 5.0 s, 528.4 tps, lat 11.318 ms stddev 2.940
progress: 10.0 s, 526.0 tps, lat 11.418 ms stddev 2.884
progress: 15.0 s, 522.8 tps, lat 11.473 ms stddev 2.892
progress: 20.0 s, 525.6 tps, lat 11.409 ms stddev 3.008
progress: 25.0 s, 528.0 tps, lat 11.366 ms stddev 2.858
progress: 30.0 s, 525.6 tps, lat 11.412 ms stddev 2.893
progress: 35.0 s, 521.8 tps, lat 11.503 ms stddev 2.973
progress: 40.0 s, 524.4 tps, lat 11.439 ms stddev 2.966
progress: 45.0 s, 736.6 tps, lat 8.152 ms stddev 3.801
progress: 50.0 s, 1101.2 tps, lat 5.447 ms stddev 0.738
progress: 55.0 s, 1012.2 tps, lat 5.929 ms stddev 0.609
progress: 60.0 s, 723.4 tps, lat 8.285 ms stddev 2.969
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 2000
query mode: simple
number of clients: 6
number of threads: 6
duration: 60 s
number of transactions actually processed: 38886
latency average = 9.257 ms
latency stddev = 3.629 ms
tps = 647.993705 (including connections establishing)
tps = 648.099359 (excluding connections establishing)

That's a 20% variation in TPS for the exact same configuration!

What am I missing here?

Hardware setup

postgres server: 32 GB RAM / 6-core (12 thread) / SSD with RAID1
pgbench server: 32 GB RAM / 4-core (8 thread) / SSD

Relevant Postgres config for the above output

max_connection=100
work_mem=4MB
maintenance_work_mem=64MB
shared_buffers=12288MB
temp_buffers=8MB
effective_cache_size=16GB
wal_buffers=-1
wal_sync_method=fsync
max_wal_size=5GB
autovacuum=off

pgbench settings

Initialisation

pgbench
  --initialize
  --init-steps=dtgpf
  --scale=2000  # Results in approx 30-32GB of data
  --username=benchmarking

Benchmarking

pgbench
  --builtin=tpcb-like
  --client=6
  --jobs=6
  --time=60
  --progress=5
  --username=benchmarking

Network connectivity between the two servers

# iperf -t 60 -c [IP-ADDRESS]

------------------------------------------------------------
Client connecting to [IP-ADDRESS], TCP port 5001
TCP window size: 85.0 KByte (default)
------------------------------------------------------------
[  3] local [IP-ADDRESS] port 40494 connected with [IP-ADDRESS] port 5001
[ ID] Interval       Transfer     Bandwidth
[  3]  0.0-60.0 sec  3.47 GBytes   496 Mbits/sec


texts:
Has the creation of statistics any impact on the estimated costs and the optimizer plan given by db2 optimizer. Or will (in the best case) only the execution time of a query change?

I ran something like this. Is this right/useful?

RUNSTATS ON TABLE DB2INST1.ARTISTS WITH DISTRIBUTION DEFAULT NUM_FREQVALUES 50 NUM_QUANTILES 5 AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE DB2INST1.ARTISTS ON KEY COLUMNS;
RUNSTATS ON TABLE DB2INST1.ARTIST ON COLUMNS((COUNTRY,ARTIST_NAME),(ID,ARTIST_NAME),(CONTACT_MAIL,CONTACT_PHONE),(WEBSITE,TWITTER,SOUNDCLOUD,FACEBOOK));

Two queries I ran on this database were:

SELECT * FROM ARTISTS WHERE WEBSITE IS NOT NULL AND TWITTER IS NOT NULL AND SOUNDCLOUD IS NOT NULL AND FACEBOOK IS NOT NULL
SELECT YEAR, FORMAT, count(*) AS ANZAHL, sum(TRACK_LENGTH) AS GESAMTLAENGE_sec FROM RELEASES JOIN TRACKS ON RELEASES.ID = TRACKS.RELEASE_ID GROUP BY YEAR, FORMAT ORDER BY YEAR DESC

The execution time of both queries was better than before running runstats. But the estimated cost and the optimizer plan given by db2expln were exactly the same.

db2expln command:

db2expln -q [one of the queries above] -t -g -d $dbname


texts: Why does Log Shipping .TRN file copy just stop

texts:
I apologize in advance for a long post, but I have had it up to here with the situation of having to delete Log Shipping (LS) configuration and starting it over for any DB that has got this error.

I have LS setup on 3 win2k8r2 servers (primary, secondary, monitor) with 100 databases backed up and shipped transactions from the primary to secondary and monitored by monitor. Backups and copies are run every 15 mins and then the ones older than 24 hrs are deleted.

Some DBs are very active and some not so active but shipped regardless for uniformity sake (basically to make secondary server identical to primary). Some DBs are for SharePoint 2010 and the majority are for inhouse apps.

The issue is that after all LS configs are set up, everything works well for about 3 to 4 days. Then I go to the Transaction LS Status report on the secondary, I see that randomly some LS jobs have an Alert Status, because the time since last copy is over 45 mins so no restore has occurred. This seems random and the only errors I see is from a SharePoint 2010 DB (WebAnalyticsServiceApplication_ReportingDB_77a60938_##########) which I belive is a Reporting DB that gets created weekly and LS just cannot figure out which the last copy to backup or to restore is.

I posted here regarding that and I have yet to find a permanent solution.

For my main error (time since last copy) I have not seen anything that could have caused that and I don't get any messages (even though some alert statuses have been ignored for 3 days).

Anyway, I would really appreciate any input on understanding what's causing this issue and how I could fix it.


texts: What are the cons of implementing faceted search with WHEREs and JOINs?

texts:
I'm implementing a filter search system (I understood today that is called faceted search) for an ecommerce website.

The fron end looks something like this:

Country:
[Select a country]

Categories:
(x) category 1
( ) category 2
(x) category 3
...

Format:
( ) format 1
(x) format 2
( ) format 3
...

...

I'm implementing it with an AND between each filters.
I implemented it my way, because I didn't know it was called faceted search and couldn't find suggestions on how to do it, so I came up with something like this:

SELECT ...
FROM (products AS p)
LEFT JOIN countries AS c ON c.id=p.country_id
JOIN categories_products AS cp ON p.id=cp.product_id
JOIN categories AS cat ON cp.category_id=cat.id
LEFT JOIN formats AS f ON f.id=p.format_id
WHERE cat.id IN (9, 12)
AND f.id IN (3, 5, 6, 7)
AND c.id =  94
GROUP BY p.id
HAVING COUNT(DISTINCT(cat.id)) = 2;

Basically, a product has only one country, but can belong to any number of categories. Then there's the format and other filters which I'm not displaying here. Each one of this has its own table.
It's working the way it is, but today I read about implementations with a facet table where are stored facts. So, from what I understood, every row would represent every possible combination of options.

I'm filtering a table of about 7000 rows. Is my approach (that) bad from a perspective point view? Other cons?

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country_id` int(11) DEFAULT NULL,
  `format_id` int(11) DEFAULT NULL,
  `featured` tinyint(1) DEFAULT '0',
  `is_limited` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `order` (`order`),
  KEY `photographer` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=33514 DEFAULT CHARSET=utf8;

This is the relevant part of the table. I omitted some fields like name, description and some others.
What I meant saying that "each one of these has its own table" was that each of these filters (categories, format, country) has its own table. So there's a categories table and a categories_products relation table; there's a countries table; a formats table and so on.


texts: Select values from 2 tables with same properties

texts:
I am using SQL server 2012, running on windows data-center 2012, under windows azure cloud platform virtual machine.

I have the following situation :

I have a base settings table lets say as following:
ID as a key (int), and many settings column

I have another "extended settings" table that has the following:
ID as a key,
some information (some settings column),
SETTINGS_ID as int related to the settings table ID key

I need a query that would get me the settings from the settings table and would overwrite any value it can find from the extended table that is not null, in a generic way:

Get a settings_id as int, get an "extended_settings" id as int,

Returns a select of all the settings from the settings table by its id but for every setting that is similar by its property name in the extended settings table, and different than null (in the extended table), take the one from there,

I need it to be generic...

I could write the following:

SELECT ISNULL(EXTENDED_SETTINGS.A, SETTINGS.A),
       ISNULL(EXTENDED_SETTINGS.B, SETTINGS.B),
       ISNULL(EXTENDED_SETTINGS.C, SETTINGS.C),
       ISNULL(EXTENDED_SETTINGS.D, SETTINGS.D),
       SETTINGS.name , SETTINGS.SOME_OTHER_INFO_THATS_NOT_IN_THE_EXTENDED_TABLE
FROM SETTINGS INNER JOIN EXTENDED_SETTINGS ON SETTINGS.ID = EXTENDED_SETTINGS.SETTINGS_ID
WHERE
EXTENDED_SETTINGS.ID = @extSetId AND SETTINGS.ID = @settID

this would work, but i want to make a generic query so that if i add a column named "J" to both tables it would also work, and if i add a column to the settings table it would also work, and i wont have to modify this query every time i change my tables...


texts: Should my Postgres backup server be as beefy (IO, processor, RAM) as my master?

texts:
I've set up a Postgres database on a server with 2 CPU cores, 4GB of RAM and an SSD volume (on top of RAID 10 of course). This is our (sort-of) "beefy" production database server (I can easily add more RAM, etc. when the time comes).

Now it's time to set up a backup Postgres server (not used for reads or anything else, just strictly a replication/backup). I don't want to have to fork out another $150/mo for the same exact setup for my backup server, and I know that a lot of the server resources used for a production database have to do with complicated queries, sequential scans, etc., so my thinking was that I could get away with a 512MB server with about 1/6 the I/O performance, and 1 CPU core.

Is that thinking correct, or does a replication/backup Postgres server typically need nearly the same specs as a production Postgres server?

Note: I'm using Postgres 9.2.4 with streaming (non-synchronous) replication on an Ubuntu 12.04.2 server.


texts: Latest value of each GROUP with INNER JOIN

texts:
I'm using sensors to get temperature and humidity every X minutes.
I've one table for sensors and one table for their results.
tb_sensors:

tb_results:

I want to get the latest measurement for every sensor (MAX(date)).
I'm using this:
SELECT id_sens, pos_sens, temp_res, humi_res, MAX(date_res) FROM tb_sensors
INNER JOIN tb_results ON tb_sensors.pk_sens = tb_results.fk_sens
GROUP BY id_sens ORDER BY id_sens

I'm expecting to get:

but the result is:

I've got the latest date mixed with the first id, position, temperature and humidity ever written.
How can I resolve this issue?


texts: Adding nullable column to table costs more than 10 minutes

texts:
I have problems to add a new column on a table.
I tried to run it a couple of times, but after more than 10 minutes running, I decided to cancel the query because of lock time.

ALTER TABLE mytable ADD mycolumn VARCHAR(50);

Useful information:

PostgreSQL version: 9.1
Number of rows: ~ 250K
Number of columns: 38
Number of nullable columns: 32
Number of constraints: 5 (1 PK, 3 FK, 1 UNIQUE)
Number of indexes: 1
OS type: Debian Squeeze 64

I found interesting information about the way PostgreSQL manages nullable columns (via HeapTupleHeader).

My first guess is that because this table already has 32 nullable columns with 8-bits MAXALIGN, HeapTupleHeader is 4 Bytes length (not verified, and I don't know how to do so).

So adding a new nullable column could need an update of HeapTupleHeader on every row to add a new 8-bits MAXALIGN, which could cause performance issues.

So I tried to alter one of the nullable columns (which is not really nullable in reality) in order to decrease to 31 the number of nullable columns, to check if my guess could be true.

ALTER TABLE mytable ALTER myothercolumn SET NOT NULL;

Unfortunately, this alter also takes very long time, more than 5 minutes, so I also aborted it.

Do you have an idea of what could cause this performance cost?


texts: MongoDB Migrating away from RocksDB to WiredTiger

texts:
We have been working for some time with RocksDB as our engine and we are now trying to migrate to WiredTiger. We have some pretty big databases around 4~12 TB of data and according to the process described in the docs, we added a new node with WiredTiger and tried letting it replicate from scratch.

With the amount of data, the replication times are VERY long and quite a lot of times we were in the situation were the WiredTiger node decided to change the node it was replicating from, only to drop ALL data and start from scratch again. Only once we succeeded in completing the replication, but the node ended up way behind in comparison to the oplog.

Again with this amount of data it becomes prohibitive to have a big enough oplog to hold weeks of transactions and the process is also very flimsy, single threaded, slow and prone to fail.

So my questions are the following:

Is there a better way to proceed with this migration?
Is there a way to speed up replication (i.e. multithreaded replication)?
Is there a way to tell the new WiredTiger node to stop dropping all data in case of mishaps?

We are working with 3 and 5 nodes replica sets of Percona MongoDB version 3.4.13 and trying to move to Open Source MongoDB 3.4.13 (with the idea of upgrading to 4.x once we are in WiredTiger and dropping RocksDB and Percona entirely).


texts: "New query window" shortcut for Oracle SQL Developer

texts: I'm used to Ctrl-N to give me a new query window in SSMS. How do I do it in Oracle?


texts: Why does imported txt table consume so much space

texts:
I have a .txt file with storage size 1253 MB. After importing it to SQL Server 2016, the table takes 1680 MB on disk. Why does the storage space grow so much?

I didn't do any data transformation and use pure char format to store data. I read the data dictionary of the txt file and if the length of a variable is at most n, then I use varchar(3). If the file is fixed length n, then I used char(n).

About 4 to 5 columns are densely populated and all the other columns are very sparse. For the sparse columns, only 1% rows are not null.

Here is the code creating the table and importing data:

CREATE TABLE table1 (
var1 char(12),      var2 char(6),      var3 varchar(12),       var4 varchar(3),
var5 varchar(3),    var6 varchar(4),   var7 char(1),           var8 char(1),
var9 char(2),       var10 char(6),     var11     varchar(8),   var12 varchar(12),
var13 char(6),      var14 varchar(12), var15 varchar(14),var16 varchar(12),
var17 varchar(12),  var18 varchar(12), var19 varchar(12),var20 varchar(12),
var21 varchar(12),  var22 varchar(12));

How can I reduce the storage space?

Here is the storage information from command sp_spaced:

name       table1
rows       22260960
reserved   1721240 KB
data       1721128 KB
index_size 24 KB
unused     88 KB


texts: How to do a pgdump with strings encoded?

texts:
When I perform a dump with pg_dump tool, with a "flat" sql format, pg_dump create a regular sql file but the restoration with pg_restore fails because the strings are not escaped.
For example, single quotes used in strings are saved as single quotes, avoiding the correct parsing of the string and the entire file.

Is there a way to dump with character encoding and restore coorectly after ?


texts: Error: ERROR 1047 (08S01) at line 6955: WSREP has not yet prepared node for application use

texts:
While restoring a dump from a CloudSQL instance into a node of a Percona xtradb cluster, I am getting this error. The line 6955 in the .sql dump file is in bold below:

DROP TABLE IF EXISTS `next_balls`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `next_balls` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `match_id` int(11) NOT NULL DEFAULT '0',
  `inning_id` int(11) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=176335 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `next_balls`
--

****/*!40000 ALTER TABLE `next_balls` DISABLE KEYS */;****

The error came on restoring the table 'next_balls', this table was restored in the node, but data was not inserted, so I think the error comes due to the "ALTER TABLE next_balls.." statement. Any idea why this error pops up? The restoration stops when the error comes. Any help would be highly appreciated.

Edit 1: I had a 2 node percona cluster, it seems the error comes when one of the node is down or feels that the other is down, which results in a split brain situation and the cluster halts. Is this the case? Should I add another node in the cluster and do the restore? Or should I first do the restore in one node and then set up the cluster?


texts: Index file growing endlessly - how to fix it?

texts:
I need to do some maintenance on a database on our client's site. Both log and index files were huge (Don't know how they took their backups to end this way) anyway I fixed the log file (.ldf) issue, log file is now merely a few megabytes (database is in simple recovery mode)
The problem resides on the index file (.ndf) side. For comparison purposes, the database itself (data side, .mdf) is 265Mb. The index file (.ndf) is 57.3Gb!!

Every time I try to reorganise or rebuild indexes on this database, all available RAM is eaten up for this operation, the server becomes almost unresponsive and IO activity is incredibly high. On our test server, the operation wasn't ended after a full 8 hours activity. I can't do this on their server since this is a 911 emergency center.

I just wish to flush the whole index file data and then rebuild it properly. Is there any way to do this without having to render the server in an almost unusable state during the process and having to put the database offline?

Regards,

Nicholas


texts: can fifo be done in mysql or must it be done programily via php


texts: Mongo ReplicaSet - arbiter in connection string

texts:
I'm using ReplicaSet with Mongo and when i build the connection string i have all servers hosts and ports - comma delimited.

My question is, shall i add the arbiter address there as well or just the masters and secondaries?

Thank you!


texts: Columnstore index analysis does not point me to correct columns

texts:
I am using this query to look deeper onto my columnstore indexes while t-shooting my execution plans (i.e. segment elimination etc.) :
    SELECT
    tables.name AS table_name,
    indexes.name AS index_name,
    columns.name AS column_name,
    partitions.partition_number,
    column_store_segments.segment_id,
    column_store_segments.min_data_id,
    column_store_segments.max_data_id,
    column_store_segments.row_count
FROM sys.column_store_segments
INNER JOIN sys.partitions
ON column_store_segments.hobt_id = partitions.hobt_id
INNER JOIN sys.indexes
ON indexes.index_id = partitions.index_id
AND indexes.object_id = partitions.object_id
INNER JOIN sys.tables
ON tables.object_id = indexes.object_id
INNER JOIN sys.columns
ON tables.object_id = columns.object_id
AND column_store_segments.column_id =
     columns.column_id
WHERE tables.name = 'R***'

ORDER BY tables.name, columns.name,
column_store_segments.segment_id;

(table name redacted).
The result of the analysis is as follows:
Postimg.
My question is simple: How come in the outcome of the analysis does not correspond to the columns name in the indexes? As you can see on the right part of the image, the column names on the result grid are different to what is shown in columnstore index properties.
Environment: SQL 2019 CU10 Standard ; Windows Server 2019.
Thank you in advance for a feedback,
take care S.


texts: Influx - Help to choose in between Tag and Field

texts:
I have the following columns:

'datetime', - which gives the time when the sensor values are recorded.

'machineID', - which gives the machine ID of the machine from where the sensor values are recorded.

'volt', 'rotate', 'pressure', 'vibration' - 4 different sensor values measured for a particular Machine at a time.

Please let me know if I need to name datetime and MachineID coulumns under tag or fields.

Thank you in advance.


texts: Find continuous records with equal values characters

texts:
I need your kind support to resolve this issue.

I have a table contains similar to the below data :

|AccountNo|remarks    |
|Q32571   |Actual Read|
|Q32571   |Locked     |
|Q32571   |Actual Read|
|Q32571   |Actual Read|
|Q32571   |Actual Read|
|Q32571   |Locked     |
|Q32571   |Actual Read|

The expected result shall give me the continuous records only with the same remarks

|AccountNo|TotCont|
|Q32571   |    3  |

Can anyone help please
thanks


texts: Why does a MySQL MEMORY-backed table take so much more space than on disk?

texts:
I have a single InnoDB table on disk, with ~1,107,423 rows and taking up 45.6 MiB disk space.  The structure is:

messageid int(11)
signature varchar(255)

When I create a MEMORY-backed version of this table, it takes up 803.5 MiB of memory!

It seems reasonable there'd be some expansion: a lot of extra pointers from the B-tree index or something, but increasing by a factor of 16 seems awfully excessive!  What's the reason behind this?


texts: SQL Server Log Shipping: Does the native log shipping support encryption of the tlog backup?

texts:
SQL Server 2014 Std Edition

We use the built in SQL Server encryption for our db backups (sql server backup to disk) -- works great.

We would like to implement encryption for the tlog backups (which fire every 3 min)

Is this possible? Will the built in log shipping feature magically make it all happen?

We do not want to use redgate or third party tools.

Please advise how to solve, if the built in log shipping feature does not solve.


texts:
I have 2 servers with 2 kinds of replications between them.
One is Transactional and the other is merge.

The Transactional replication is working fine but in the merge replication, I'm getting error messages.

Error messages:

  The merge process could not connect to the Publisher ' '. Check to
  ensure that the server is running. (Source: MSSQL_REPL, Error number:
  MSSQL_REPL-2147198719) Get help: http://help/MSSQL_REPL-2147198719 The
  process could not connect to Publisher ' '. (Source: MSSQL_REPL, Error
  number: MSSQL_REPL20084) Get help: http://help/MSSQL_REPL20084 Login
  failed. The login is from an untrusted domain and cannot be used with
  Windows authentication. (Source: MSSQLServer, Error number: 18452) Get
  help: http://help/18452

Two weeks ago everything was working fine (we didn't change anything and they didn't change as well).

I've read and check that the user is db_owner.
Telnet is open (and the transactional replication is working) so there are not any network problems.
I deleted and recreated both replications.

when I'm looking at the publisher server events log I see the error

  SSPI handshake failed with error code 0x80090301, state 14 while
  establishing a connection with integrated security; the connection has
  been closed. Reason: AcceptSecurityContext failed. The Windows error
  code indicates the cause of failure.

Any ideas?


texts: Strange colored sqlcl results on Windows

texts:
I am trying to color the result of SQL statements with SQLcl.

I set sqlformat ansiconsole and execute a select '@|red red |@' red from dual;

The output I get looks like this:

I blame it on the Windows environment. Am I right to blame Windows and what can I do about it?


texts: SQL Server Rollback being performed - how to stop it?

texts:
I have a query that takes 60 million rows, copies them in a new table, does some updates on them, then inserts them into another new table.  It's all wrapped in a transaction.  I thought this would be good at first, but realize it's a dumb mistake.

While it ran for 7 hours on my local instance machine, on the production DB it took over 2 days to run! Tired of waiting and realizing that my solution was probably not the best way to do this, I closed my query window and did a kill on the process.  Now, it's doing a rollback.

Meanwhile, I've run this: KILL 60 WITH STATUSONLY and EXEC sp_who2 'active'.  The status says "SPID 60: transaction rollback in progress. Estimated rollback completion: 62%. Estimated time remaining: 31849 seconds".  The seconds and diskio is going up, so I know it's trying to do the rollback.  It's extremely slow and going up a percentage complete every several hours.

My issue is that because of my actions, the database got huge and is taking up disk space.  Furthermore, I don't care about these two new tables I tried to copy into.  I'd like to truncate/drop them and start over with a much better method of solving this issue.  However, I can't truncate/drop them in the middle of the rollback?  I could care less what happens to these new tables.  They are also in their own file group and .ndf files.  I just want to delete these as quickly as possible so I can resume my better method.  I'm on SQL 2014 and need urgent help as it's Saturday and I'd rather not bother my boss! :)


texts: How do I rescue SQL Agent jobs from a set of detached master, model, mdb files?

texts:
I had a MSSQL (MSSQL 2008 R2) cluster that failed in a way that I couldn't bring it back up (long story, servers ended up in BSOD loops, I'm blaming Broadcom and myself).

I've SQL backups, and even better, I've got the detached MDF and LDF files as they were on the SAN before everything went wrong. I've now got the task of restoring these back to a newly built server.

The databases themselves, logins and SSIS packages aren't a problem, as I've got all these. The thing that's bothering me is the SQL Agent jobs that were defined on the previous server. I'm dimly aware that these are stored in MSDB somewhere, but what steps should I go through to retreive these from the MDF/LDF files I've already got and load them on the new server?

I'm not going to be restoring the modal, msdb, master and temp databases on the new server, so should I be looking to attach the old files under different names and pull job definitions out of them, or is there some other (better) way?


texts:
I have a server with 6 cores, I've installed SQL Server 2012 web edition, based on core licensing, allowing up to 16 cores.
However, it only uses 4 cores.
In the error log file:

2015-07-09 14:00:32.52 Server      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Web Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
2015-07-09 14:00:32.70 Server      SQL Server detected 6 sockets with 1 cores per socket and 1 logical processors per socket, 6 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

Should I define something else to use all cores?

Thank you.


texts:
I have a many-to-many relationship setup between three tables.

  CompanyNotices (M-M) CompanyNoticesLocations (M-M) Locations

I would like to retrieve all rows from the CompanyNotices table for one CompanyID and aggregate all of the LocationNames (from the Locations table).

I have tried to use the Coalesce function to do this in a stored procedure but it is returning all LocationNames for each CompanyNotice row.

DECLARE @CompanyID INT
SET @CompanyID = 9
DECLARE @Location varchar(4000)

SELECT @Location = COALESCE(@Location + ', ', '') + L.LocationName
FROM dbo.CompanyNotices AS CN
INNER JOIN dbo.CompanyNoticesLocations AS CNL ON CN.CompanyNoticeID = CNL.CompanyNoticeID
INNER JOIN dbo.Locations AS L ON CNL.LocationID = L.LocationID
WHERE CN.CompanyID = @CompanyID
GROUP BY L.LocationName

SELECT CN.CompanyNoticeHeading, CN.CompanyNoticeText, IsHR, IsApprover, IsEmployee, @Location AS LocationName
FROM dbo.CompanyNotices AS CN
INNER JOIN dbo.CompanyNoticesLocations AS CNL ON CN.CompanyNoticeID = CNL.CompanyNoticeID
INNER JOIN dbo.Locations AS L ON CNL.LocationID = L.LocationID
WHERE CN.CompanyID = @CompanyID
GROUP BY CN.CompanyNoticeID, CN.CompanyNoticeHeading, CN.CompanyNoticeText, IsHR, IsApprover, IsEmployee

I do realise that when I am setting the @Location that it is not linked to each CompanyNotice, so I am wondering how I can incorporate the first SQL query into the second to COALESCE the LocationName for each CompanyNotice?

Thanks in advance.


texts:
I'm starting to prepare my server for a classic configuration Apache PhP MySQL.

I installed Apache 2.2.24 without problems. (Output "It works").
I installed php 5.2.1 - win32 - installer. (launching info.php I see I see all configuration details).

Now I have to install MySQL.

Question:

Which version do you recommend suitable for Apache 2.2.24 and PHP 5.2.1.
You could attach the link?

P.s.: only classic configuration (no wamp or others).


texts:
I am trying to configure the MongoDB BI Connector.

I have installed mongodb-bi-win32-x86_64-v2.5.0.msi Mongodb version is 3.0.15

1.Started mongod

mongod --dbpath d:/data1 --auth

Started mongosqld
mongosqld --auth --mongo-username xxxx --mongo-password xxx --mongo-authenticationSource admin

I am getting the following error

E SAMPLER [schemaDiscovery] unable to initialize schema: error sampling collection: failed to execute aggregate: unrecognized fi eld 'readConcern'

Let me know how to resolve the issue


texts: explain on view shows derived while explain on select shows simple select type

texts:
I have a view v which involves a couple of sub-selects in its SELECT clause plus a couple of joins by id or indexed column.

The view query is like:

SELECT col1, col2, (SELECT ... FROM subt) AS col3 FROM t1 JOIN t2 ON [..]

The fact is that if I query that view by col1, which is pk of t1:

SELECT * FROM v WHERE col1='some value'

the EXPLAIN shows a bunch of DERIVED tables, but if I run the same query on the view SELECT statement:

SELECT col1, col2, (SELECT ... FROM subt) AS col3 FROM t1 JOIN t2 ON [..] WHERE col1='some value'

those same tables show up as SIMPLE.

How's that? I need to fix that view because it is not using some indices (although all joins are by pk or indexed columns), but I'm stuck on those DERIVED rows and I can't go further on my analysis.

UPDATE

For comparison, I've run a couple more explains on a view that doens't show  these different execution plans for the same WHERE clause.

Given the view v1 create with CREATE VIEW v1 AS v_select_stmt, the plan for the query:

SELECT * FROM v_crm_v1 v WHERE v.id='some value'

gives all plan steps with select_type=simple.

The plan for:

[v_select_stmt] AND id='some value'

yields all the steps with select_type=simple too (and they're identical to the previuos query plan).

Finally, in the plan for:

SELECT * FROM ( [select_stmt] ) v WHERE id='some value'

the steps have all select_type=derived.


texts: Availability Group failover stuck in Resolving State

texts:
I am setting up a lab for testing automatic failover for SQL Server

I have a 2 node WSFC (SQL-CLUSTER-TEST-01) with the nodes

SQL-TEST-01
SQL-TEST-02

A listener SQL-HA-GRP-01

My initial testing is to just power off either of the nodes (virtual machines). It it failing correctly in one direction - SQL-TEST-01 to SQL-TEST-02. But when SQL-TEST-02 is the primary and I pull the power on it, the availability group goes into a permanent "resolving" state which I can only resolve by powering on SQL-TEST-02.

When I check the logs or try to view the properties of the availability group it says the quorum was lost.

What am I missing?


texts: Upgrading to SQL Server 2017 from SQL Server 2012 time allowed

texts:
We have a customer running a SQL 2012 database for their document management system but this needs to be upgraded, They want to upgrade to SQL Server 2017 and looking at the forums the best method is side by side.
Can I ask what sort of time should I allow for this type of upgrade - I need to inform the customer of the downtime.. Approximate size of the database is 800GB.

Any help or advise is greatly appreciated.


texts: How to view the query of another session in pg_stat_activity without being super user?

texts:
I have a Postgresql 9.2 database. Two users are created on this database. When I perform the following query as superuser, I can see everything.

select * from pg_stat_activity

However, is it possible to acheive the same result without being connected as superuser ?

Which privilege/role should I grant/create to have the results that a superuser can see ?


texts: Getting nice PostgreSQL pgAdmin-style DDL

texts: In pgAdmin 3 (and pgAdmin 4 if you're prepared to wait longer) the DDL for the currently selected database object is shown in the main panel, along with corresponding triggers, constraints, etc, all nicely formatted. It looks quite different to the DDL exported from pgdump. Is there a way to get the nicer version, without manually copy-and-pasting from pgAdmin?


texts: MySQL lost records in a master slave scheme

texts:
We have developed a web application that uses two MySQL servers set up with replication (master-slave scheme). The application was to be used for a couple of days only, when ~ 50-60 users would enter numeric data. The database was setup to used InnoDB tables. Before the big day, we made a test when a great portion of the expected users entered data in order to see the cpu utilization of the master server and its performance in general.

The big day came and when the data entry began we experienced an issue of losing data from one table. The user data entry form included data that were saved in two different tables. We discovered an application bug that the data for the first table was outside the transaction for the second. This would explain the behavior of not saving data for the sub-form (the second table). However, after fixing the bug and rolling out the newer version, after ~ 2 hours, the issue happened again. The were several records that had the data for the main form saved (the first table) and the data for the sub-form lost (the second table). The users entered again the missing records. Querying the database returned 0 records of lost rows. And then, the issue stroke again. Without explanation, we had somewhat ~170 lost records from the sub-form (the second table).

Could a possible corrupted ibd file lead to such a situation? After doing some basic research in Google, it came down to a post saying that when using InnoDB plugin (as in our case) and you upgrade to a newer version, inconsistencies could arise and you have to delete your database and re-create it again to be sure that all is ok. When we started developing the application, the MySQL version used was 5.1.56 (Debian 6 stable) and at some point, 5.1.61 was pushed as an update. Could this upgrade lead to a situation when some relatively medium-weight contention cause lost records?


texts:
In PostgreSQL, will VACUUM reset the dead tuple count (n_dead_tup in pg_stat_all_tables)?

The situation I'm seeing is that vacuum is apparently being run manually, once daily, and the n_dead_tup / n_live_tup data shows that there are a large, and increasing number of dead tuples.

PostgreSQL 9.3 here.


texts: Do primary keys change when indexes are rebuilt or reorganized?

texts:
I have a database containing tables that each have auto-increment int columns serving as the primary key column.

When I do a rebuild or reorganize operation on my indexes, does either operation potentially change the values of this column?

Does it matter if the indexes are 'clustered'?

I'm using various versions of MS T-SQL from 2008 onward.


texts: Clarification on steps creating Database Replication Definition

texts:
I am being asked to create database replication definitions for a couple of databases (around 20 databases). I just wanted to clarify is the below-mentioned steps are the correct steps to be carried out?
Let's says 'customer' is one of my database.

Set ddl in tran to true
sp_dboption customer,"ddl in tran", true
Mark the primary database using sp_reptostandby
sp_reptostandby customer,'all'
Set the RepAgent parameter send warm standby xacts to true
sp_config_rep_agent customer,'send warm standby xacts', 'true'
Create db repdef/subs
create database replication definition db_repdef_customer
with primary at DS.customer
replicate DDL
replicate system procedures
go
create subscription db_sub_customer
for database replication definition db_repdef_customer
with primary at DS.customer
with replicate at DS2.customer1
without materialization
go

(Note: DS.customer and DS2.customer1 are ASE-ASE replication)

After I have followed the above steps to create db repdef/subs, I hit a lot of permission issues on my replication ID to do INSERT/UPDATE/DELETE operators on those tables I did not setup table replication yet. Further check on these tables in my 'customer' database (ex. I tried to do insert/update/delete operations manually on tables without setting table repdef, I realised that the data replication is working for all the tables under the 'customer' database with/without I setup table replication. Is this normal? Any steps I have missed out? Please help.


texts: Postgres COPY with multiple small chunks

texts:
In postgres, we can do COPY to export the data into file. But all the data will be saved into a file. But I want to chunk that file into small files. We can do this after the export.
But is there a way to export it during the COPY process?
OR in psycopg2 can we do this?


texts: What is the safest way to run SSIS on Multiple Named SQL Server Instances?

texts:
I have an Active/Active SQL Server 2008 R2 cluster. There are two named instances on each node and no default instance.

I need to deploy SSIS packages to the different instances. To do this, I needed to modify the  MsDtsSrvr.ini.xml file to add the instance names so they would be seen by BIDS and SSMS. I put all 4 instance names in that XML file (on both nodes) and restarted SSIS on both nodes. I can now see the folder for each instance when connecting to any instance in SSMS through an Integration Services connection. However, clicking the 2 instance names that are on the other node generates a login failure since they are not active on the current node. Maybe this is just an inconvenience, but am I going to run into any issues with this setup? Or is there a way to do it that makes more sense?


texts: What exactly can SQL Server 2014 execute in batch mode?

texts:
When a columnstore index is being used in a query SQL Server is able to use batch mode. Documentation is thin on what can run in batch mode and what can't. Please look at the following (motivating) query plan where a surprising number of things executes in batch mode (green):

(This is an estimated plan. I used the actual plan to verify that the actual execution mode is indeed batch.)

Note, that only the build side of T1 uses a columnstore index. All probe inputs (T2 and T3) are rowstores. Their data seems to transition into batch mode. I always thought that batch mode is being used for the data stream that runs through the probe side only.

It seems that data can transition into batch mode even if it does not originate from a columnstore index. That poses the question: Why doesn't SQL Server use batch mode for rowstore-only queries as well? Could be beneficial for some of them. Is using a columnstore index a formal requirement that is necessary to make SQL Server consider batch mode? Could we maybe add a zero row dummy table with a columnstore index to induce batch mode and realize performance gains?

What exactly can run in batch mode as of SQL Server 2014?


texts: Why PostgreSQL's service are different between system service and pg_ctl?

texts:
Installed PostgreSQL 10 by a normal user.

Enabled and started its service:

sudo systemctl start postgresql-10
sudo systemctl enable postgresql-10

Then login with postgres user:

sudo su - postgres

Add this setting to .bash_profile file:

export PATH=$PATH:/usr/pgsql-10/bin/

Restart PostgreSQL by pg_ctl:

-bash-4.2$ pg_ctl restart

Then exit to normal user and check postgresql's status again, it failed. Even use this way to restart can't success:

sudo systemctl restart postgresql-10
sudo systemctl stop postgresql-10

It always failed.

But if I use postgres user to test restart, stop, start, all of them can success.

These 2 ways aren't controlling the same process?

Add postgresql-10 service content

$ sudo systemctl cat postgresql-10.service

# /usr/lib/systemd/system/postgresql-10.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  If you want to customize, the
# best way is to create a file "/etc/systemd/system/postgresql-10.service",
# containing
#       .include /usr/lib/systemd/system/postgresql-10.service
#       ...make your changes here...
# For more info about custom unit files, see
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F

# Note: changing PGDATA will typically require adjusting SELinux
# configuration as well.

# Note: do not use a PGDATA pathname containing spaces, or you will
# break postgresql-setup.
[Unit]
Description=PostgreSQL 10 database server
Documentation=https://www.postgresql.org/docs/10/static/
After=syslog.target
After=network.target

[Service]
Type=notify

User=postgres
Group=postgres

# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.

# Location of database directory
Environment=PGDATA=/var/lib/pgsql/10/data/

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0

ExecStartPre=/usr/pgsql-10/bin/postgresql-10-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-10/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT

# Do not set any timeout value, so that systemd will not kill postmaster
# during crash recovery.
TimeoutSec=0

[Install]
WantedBy=multi-user.target


texts: How to get minimum and maximum for grouped timestamps


texts: Queries running fast most of the time but >100x slower other times

texts:
I know this is a very generic question, but if someone can point me to what else I should investigate/check I would greatly appreciate it.

We have a view which runs around 15 seconds most of the time but during random times starts running around 45 minutes. These long running episodes last for hours and all of a sudden out of now where go back to running normal times.
We using SQL SERVER 2016. We have one dedicated hard drive for data, one for log and one for backup.

I have confirmed the following during the long running episodes:

1) There are no transactions open

2) There are no other heavy queries running. Used the query below:

SELECT sqltext.TEXT,
    req.session_id,
    req.status,
    req.command,
    req.cpu_time,
    req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

3) There are no backups running and anti-virus does not scan the database files

4) Execution plans between the normal and the long runs are exactly the same (at least the execution plan of the top level view. The top level view unions some child views)

I am in no way a database administrator and getting one is not one of the available options.

I am wondering else could be causing these issues and what kind of queries I can run to determine the culprit. If there is anything else you would like to know please let me know.

Thank you


texts:
Query that is being ran:

INSERT INTO `log_url` (`url_id`, `visitor_id`, `visit_time`) VALUES (?, ?, ?)

Error that is being thrown:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '41721' for key 1

Where does 41721 fall into? How do I read this? How do I check the 'key 1'?


texts: Discrepancy between system catalog view and sp_change_users_login?

texts:
I want to check for orphaned users on a database. To that end, I use the following query on my SQL Server 2005 instance to get all users and their accompanying logins for a specific database (say MyDB):

SELECT  U.name AS UserName
        ,U.type_desc
        ,U.create_date
        ,U.modify_date
        ,U.default_schema_name
        ,P.name AS LoginName
        ,P.is_disabled
        ,P.type_desc
        ,P.default_database_name
        ,P.create_date
        ,P.modify_date
FROM    MyDB.sys.database_principals AS U
LEFT OUTER JOIN master.sys.server_principals AS P ON U.sid = P.sid
WHERE   U.type IN ('G', 'S', 'U')
ORDER BY UserName;

When I compare the results of this query with the results of USE MyDB; EXEC sp_change_users_login @Action = 'Report'; things do not seem to add up. The first query gives me 12 users of which 6 do not have logins; but the call to sp_change_users_login returns only 1 orphaned user.

Even if I take users like guest, INFORMATION_SCHEMA and sys out of the equation, there are still two users without a login attached in my first query that do not show up in the call to sp_change_users_login. What am I missing?

EDIT
I have changed use of the deprecated sysuser system table to the database_principals system view, according to the handy MS Mapping System Tables to System Views article (and Aaron of course).


texts: When attempting to failover many logins fail due to nonmatching passwords on both instances.  Is there a way for me to replicate passwords to other servers for all logins which passwords do not match.


texts: How do flush innodb table, copy files, then unlock tables from Windows bat file?

texts:
I can successfully execute "FLUSH TABLES myTable FOR EXPORT;" in MySql Workbench. Then I can copy the cfg and ibd files I need, and then I can go back to workbench and run "UNLOCK TABLES" and all is well. But I would like to do this from a Windows batch file.
However, if I run the following bat file:

mysql -username u -password < prepareTableForCopy.txt

and prepareTableForCopy.txt contains the following:

use mydata;
FLUSH TABLES myTable FOR EXPORT;

I get no errors, but when I look in the data folder the files I need to copy are not there. It seems that as soon as the flush is run and the script returns the files are gone.

How can I fix this so that I can achieve my goal which is to have a .bat file that prepares the table for export, copied the correct files to another directory. zips the files and then unlocks the table?


texts:
I have created a 2012 WFC and SQL Server 2014 is clustered on it. The cluster works as expected but have a question on Users:

I added a windows domain group to the cluster as sysadmin.

When the user tries to log into SQL on the active server the users rights are not right. They can’t see anything. If the same user logs in from the no active server they have full admin rights, if they log in from their local machine they have the proper sysadmin rights.

Anyone have a clue as to what might be happening?


texts: SQL, category tree with multiple parents

texts:
I need to make a category tree where some leafs can have multiple parents, for exaple

g
 \--b
 \--a
c
 \--d
 \--a

above category 'a' has two parents(g and c)

My first attempt was to create the following table

create table catalogcategories2 (
  id serial,
  name text not null,
  pid int null,
  primary key(id,pid),
  CONSTRAINT catalogcategories_fk FOREIGN KEY (pid)
  REFERENCES catalogcategories2(id)
);

where both parent id and id are primary keys but I get the following error

  there is no unique constraint matching given keys for referenced table "catalogcategories2"