Tracking down unused Views and Stored Procedures I am working on a SQL Server 2005 database that has two Microsoft Access ADP frontends.

This database needs a lot of love and attention which I am attempting to give it.

There are lots of stored procedures and views in this database, some are used, some are not, some are linked to Forms or Reports in Access, some are run directly in Access.

To make this project more manageable I would like to drop anything that is historical and not used any more. All the code is in source control so I can restore anything if needed.

What ways are there to log which stored procedures and views are used?

For the stored procedures I can add an INSERT INTO dbo.log with the name of the stored procedure to every stored procedure, but I can't think of a way to do this with the views.

For the unused stored procedures you can make use of dmv's especially sys.dm_exec_procedure_stat.

To find unused stored procs, please see the answer as explained by Aaron Bertrand.

Also, there is an good read to identify as explained here in the article
 How to find unused Stored Procedures in SQL Server 2005/2008

To tack down unused views:

a) Generate a List of non-recently-used code (by querying the plan cache).

b) Script the Views/Sprocs/etc that aren’t in cache (for whatever reason) using ALTER scripts. (i.e. the SSMS ‘Modify’ option when right-clicking on an object).

c) Execute the ALTER script generated by SSMS in production. Since the object wasn’t in cache, there’s no harm and no foul in worrying about recompilation overhead. And since the script is an ALTER (not a DROP/CREATE), permissions are retained intact.

d) But, if the object references invalid objects, it’ll throw an error and I can definitively know that it’s not only NOT used, but one of the culprits that’s causing me grief during replication snapshot procedures.

Also read as mentioned by Jonnathan@ here

  Unfortunately if you want to be 100% accurate, the plan cache isn't
  going to cut it, because there are all types of scenarios in SQL
  Server where a plan may not get cached at all. For example,
  OPTION(RECOMPILE), zero cost plans, optimize for ad hoc workloads and
  single use plan stubs, etc. Since you want a method that works across
  2000-2012 your only real option to guarantee you catch the usage is to
  use a server-side trace with the Audit Database Object Access event:


File / filegroup restore vs database / log restore in SQL Server everyone

I have been doing some reading about database backup and restore.

While I have some understanding about full / differential database backup and restore + subsequent log backup and restore, I can not comprehend the usefulness of DATA /FILEGROUPS backup and restore.

I am not asking about the syntax, but rather its usefulness and situations under which regular full database backup + differential database backup + log backup + tail-log backup would not be sufficient or inappropriate.

Thanks

Well, here is a simple example.  You have a large database, but most of the data is history.  You go ahead and partition out the historical data onto read only filegroups.  This information is used on a regular basis for reporting but not for the actual application.  It is, lets say, 90% of your database.

If there is a problem you can restore the read/write portion much more quickly to get the application up and running.  Then once your application is functioning you can restore the read/only portion (which of course will take much longer).


Oracle: Trigger to store username after INSERT into column of same table Let's say I have a column in a table within my database named 'CREATE_USER'.
How would I go about creating a trigger that on INSERT on the table: inserts the name of the row creating user into the column 'CREATE_USER' within that same row.

Basically a column that stores the row creating users username within the same row. I've searched around and could not find anything that worked for me. I included what I've tried below.

CREATE OR REPLACE TRIGGER trigger_name
AFTER INSERT ON table_name FOR EACH ROW

DECLARE v_username varchar2(20);

BEGIN

    SELECT USER INTO v_username
    FROM dual;

    INSERT INTO table_name (first_col_name, second_col_name)
    VALUES (:new.first_col_name, v_username);

END;

The first problem I immediately run into is 'ORA-00947: not enough values'...

Also, if I wanted to do this for every table in my database, would I need separate triggers for each table?

First of all, in the row-level trigger body you cannot perform DML against the table which fires the trigger. You will get "mutating table" error .
Secondly, you can't modify the row inside AFTER trigger body, only inside BEFORE .   Assuming CREATE_USER field has proper type and size, the trigger can look like

CREATE OR REPLACE TRIGGER BI_TABLE_NAME
BEFORE INSERT ON table_name FOR EACH ROW

BEGIN
    :new.CREATE_USER:= USER;
END;

Another option would be not to use trigger at all, but have a column with default value USER :

create table test_table (test_table_id int not null primary key,
 ...,
created_by varchar2(30) default user);


PostgreSQL How to get friend requests in table by seeing if there is a duplicate of the reverse of a column? I've a table,

CREATE TABLE friends (
    friend1 serial references users(id),
    friend2 serial references users(id),
    unique (friend1, friend2)
);

Friends are in no particular order, but I need to have a query that returns friends where it is a one-way friendship. For example, if the data is:

friend1 | friend2
-----------------
1       | 2
2       | 1
3       | 2

the query for friend id=2 should return:

friend1 | friend2
-----------------
3       | 2

because there is not another row where friend1=2 and friend2=3.

You can use EXCEPT to remove the complementing rows.

SELECT friend1,friend2 FROM friends
    WHERE friend1=2 or friend2=2
EXCEPT
SELECT friend2,friend1 FROM friends
    WHERE friend1=2 or friend2=2;

But maybe using a CTE will perform better.

WITH buddies
AS ( SELECT friend1,friend2 FROM friends
        WHERE friend1=2 or friend2=2 )
SELECT friend1,friend2 FROM buddies
EXCEPT
SELECT friend2,friend1 FROM buddies

Unfolding the or into a union may help performance too.

WITH buddies
AS ( SELECT friend1,friend2 FROM friends
        WHERE friend1=2
     UNION ALL
     SELECT friend1,friend2 FROM friends
        WHERE friend2=2
   )
SELECT friend1,friend2 FROM buddies
EXCEPT
SELECT friend2,friend1 FROM buddies


postgres system catalog query columns from table I am building an integration test against an app that provisions a database for a user. The User created for it is not a super user, and does not have access to schema_information.tables because when I try the following script:

SELECT table_name
FROM information_schema.tables
WHERE table_schema='{schema}'

I get a return of 0, as I should since this user does not have permission.

I am trying to query the database to verify the tables and the columns created. I can get the list of table names through the system catalog with the following script:

SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname = '{schema}'

And this outputs the table names the way I want it:

  business, location, person, etc...

I can't find the script with system catalog to then find the column names (And as a bonus, the data type) of each table. So far I've tried the following:

SELECT attname, format_type(atttypid, atttypmod) AS type
FROM pg_attribute
WHERE  attrelid = 'business'

and here is the error:

ERROR:  invalid input syntax for type oid: "business"
LINE 1: ...od) AS type FROM   pg_attribute WHERE  attrelid = 'business'
                                                         ^```

Also tried:

SELECT
    a.attname as "Column",
    pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
FROM
    pg_catalog.pg_attribute a
WHERE
    a.attnum > 0
    AND NOT a.attisdropped
    AND a.attrelid = (
        SELECT c.oid
        FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relname ~ '**Dont know what to put here, Schema? Database?**'
            --AND pg_catalog.pg_table_is_visible(c.oid)
    );

and this returns 0 with a schema or database. I'm not sure what to put there for c.relname. Am I also seeing 0 with this because a basic user just cannot see deeper than tables in a schema, period?

Just select from information_schema.columns instead.

SELECT table_catalog, table_schema, table_name, data_type
FROM information_schema.tables
WHERE table_schema='{schema}';

FOR GLORY

For whatever reason, if you can't query information_schema.columns (which would otherwise indicate something is funky to me). We can reverse engineer the catalogs efficiently with psql -E with a user that can. Then run the appropriate client (\) command in psql that shows you what you want, like \d schema. And copy out the parts of the exported query you need..

For me the final column in that is something like

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
  NULL AS indexdef,
  NULL AS attfdwoptions
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '1024334' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

Now I just need to get all the attrelid's for the schema. Running a quick \d asdofkodskf I see,

SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(asdofkodskf)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

We actually want n.nspname not c.relname

So given my version of psql, the official method of querying the catalog directly would be...

SELECT c.oid,
  n.nspname,
  c.relname, t.*
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL (
  SELECT a.attname,
    pg_catalog.format_type(a.atttypid, a.atttypmod),
    (
      SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
      FROM pg_catalog.pg_attrdef d
      WHERE d.adrelid = a.attrelid
        AND d.adnum = a.attnum
        AND a.atthasdef
    ),
    a.attnotnull, a.attnum,
    (
      SELECT c.collname
      FROM
        pg_catalog.pg_collation c,
        pg_catalog.pg_type t
      WHERE c.oid = a.attcollation
        AND t.oid = a.atttypid
        AND a.attcollation <> t.typcollation
    ) AS attcollation
  FROM pg_catalog.pg_attribute a
  WHERE a.attrelid = c.oid
    AND a.attnum > 0
    AND NOT a.attisdropped
) AS t
WHERE n.nspname ~ '^(public)$'  -- YOUR SCHEMA HERE
AND pg_catalog.pg_table_is_visible(c.oid);

Excerpt (without all the columns or all the rows) (relname is table, attname is column)

   oid   | nspname |           relname           |       attname        |       format_type
llation
---------+---------+-----------------------------+----------------------+-------------------------
--------
 1024242 | public  | spatial_ref_sys_pkey        | srid                 | integer
 1045853 | public  | product_discount_qty_excl   | qty                  | int4range
 1024334 | public  | valid_detail                | valid                | boolean
 1024334 | public  | valid_detail                | reason               | character varying
 1024334 | public  | valid_detail                | location             | geometry
 1045847 | public  | product_discount            | pid                  | integer
 1045847 | public  | product_discount            | qty                  | int4range
 1045847 | public  | product_discount            | percent_modifier     | real
 1024569 | public  | geography_columns           | f_table_catalog      | name


Dump the buffer pool to disk so it can be loaded after a restart Is there a way to preserve the buffer pool cache so that is can be loaded in after a restart? My research suggests that other DB servers can do this but SQL Server is not one of them. I'm looking for ways to maximize server performance after a maintenance restart.

No, there is no way to restore the buffer pool. Some will suggest pre-filling it by selecting all rows from all tables, but that is just silly given how rare it is for systems to use all rows from all tables (among other reasons that it wouldn't do what such an action is perceived to do).

I'm not even sure that it makes sense conceptually that this should be a thing to do. What is the actual gain here? Saving the time it takes to read those data pages into memory from disk? If the pages in the buffer pool were saved to a file to be reloaded upon start-up, it would still take that same (or nearly same) amount of time to load those data pages into the buffer pool, just from a different file. Or, perhaps you don't want the application pages' initial response times to reflect that the data pages had to be loaded? How long does your system take to load pages? One person waiting an extra 1 second for that particular result set (cuz after that, those pages are in memory for subsequent queries, right?) is hardly noticeable, nor is it something that they will remember when subsequent page loads take less time. But if this is a web-based application, then can it be said that any lag is the fault of the DB and not of the app server or some segment of the route between the client computer and the app server?

Don't worry about initial load times of data pages. Any areas for improvement of initial load times are going to be in better data modeling and index structure (i.e. making sure that reading pages from disk is as efficient as possible). Instead, worry about areas that drag on performance for repeated requests once the data has been loaded.

Besides, who is to say that you want all of the pages that were in the buffer pool? How many ad hoc support queries were performed recently that are still in the cache that likely won't get looked at again for 6 months or so? Just let SQL Server take care of managing the cache as the pages that get loaded when users start hitting the system after the restart will necessarily be the pages that should be there.

Also, given that SQL Server can evict pages from the cache whenever wants (if it feels that more memory is needed for other items) then you might load a bunch of pages that get evicted a moment later.


SQL Database Snapshot before code depolyment faster than backup We have code deployments every Thursday, some of the changes involve database changes - before the code is depolyed we do a SQL backup in case there are errors, this usually takes 45 minutes and everyone has to wait till it finishes.  Could I take advantage of taking a database snapshot, and if the code tests good just delete the snapshot, if there is a problem use the snapshot to roll back any changes made?

Depending on the changes being performed, database snapshots are a viable alternative to backups. You need to ensure you're meeting the prerequisites and that you won't be impacted by the limitations for the source and snapshot (see here).

There are limitations around certain feature usage, such as no MEMORY_OPTIMIZED_DATA filegroups, that prevent snapshots. In addition, some features like FILESTREAM filegroups and Full Text Indexes are unavailable in the snapshot database.

You also need to ensure you have sufficient disk space available when undergoing post-deployment testing because the snapshot will grow as you change data.

You also can't drop database files from the source DB after a snapshot is created. In circumstances where this is happening, backup is the solution.


SQL Server Backup - move to new server - Media Failure I've got a server in the UK with SQL Server 2008. It's very locked down firewall-wise.

We want a local duplicate of the DB to test and build a new report.
Backing up the relevant DB, and then transferring over FTP (binary-mode) to here in the USA ends up saying in SQL Server 2012:

  Media family on device is incorrectly formed. SQL server cannot process this media family.

I also tried transferring using File Transfer 1.2j (from Sourceforge), as well as splitting them into zips/7zip chunks and copying them slowly through RDP.
None of them load on our SQL Server 2012 machines. This is the key difference between my case and other SO posts I've read. They're going backwards in version. I'm going forwards, which is supposed to be supported.
I've also verified the files on both ends using certutil to generate SHA-1 hashes.
Lastly, the backup CAN be restored in the UK into itself! So the backup isn't corrupt when coming from SQL Server. I'm also 99% sure it's a copy-only backup so it doesn't need any other differential files.

So the only thing left I see is corruption across the internet (yet SHA-1 says otherwise), or, we're not saving correct backups, or, somehow our USA SQL doesn't want to load the older UK SQL. Does localization even matter, especially if they're both English?

As Andrew Loree suggested, this might be due to a 3rd party SQL backup product being installed on the source instance.

In order to find out what software was used to take the backup, you can run a RESTORE HEADERONLY on the .bak file from your SQL2012 instance. That should give you some information about how the backup was created.

RESTORE HEADERONLY FROM DISK='X:\file.bak'

If the above also fails, then there would be corruption, but you've already ruled out corruption via SHA1 checksums.


How to sort rows to appear alternating (e.g. A B A B ... )? Suppose I have a column x with values A and B. For example:

Row X
1   A
2   A
3   A
4   B
5   B
6   B

Now, I want to sort them into this fashion:

Row X
1   A
2   B
3   A
4   B
5   A
6   B

Of course, I can create a new Column Y. Then assign A rows to odd number (1, 3, 5, ...), and B rows to even number (2, 4, 6, ...), and then sort with the Y column. But is there an easier way to achieve this?

It will be best if the answer can work with MS SQL.

Try this:

CREATE TABLE SortTest (val char(1))

INSERT INTO SortTest VALUES
('A'),
('A'),
('A'),
('B'),
('B'),
('B')

SELECT val
FROM SortTest
ORDER BY row_number() OVER (PARTITION BY val ORDER BY val), val


How can I find port running Oracle Enterprise Manager I need to connect to the Oracle Enterprise manager of a remote machine. But I dont know the port on which OEM is running on the remote machine. I have access to the machine from terminal though.

Is there a command to see the port number OEM is running on. The remote machine is CentOS and I need to open the OEM on my local browser (I am running windows 10)

I would really appreciate all the help. I am new to Oracle.

The port configuration is documented in the following file:

$ORACLE_HOME/install/portlist.ini

If Enterprise Manager has been installed then setting off the following command will show the status of the Enterprise Manager:

./emctl status dbconsole

If you are receiving the following message:

./emctl:  not found.

...then you might not have Enterprise Management Console installed.

The Management Console is not required to have a fully functional Oracle RDBMS instance up and running.


Job completes successfully and logs the errors to a text file I am using Ola's scripts for our database backups, I am trying to come up with a way to be notified if any of the database backup fails. Job completes successfully and logs the errors to text file, Can we get a  notification\alert that it found errors.

Create a notification for the Job failure. The below email will help you with the steps:

Step 1 : Configure database mail and do a test if thats working fine.

Step 2 : Change Agent properties
         Under Mail session, select the option to Enable mail profile. Ensure the correct Mail profile is selected.

Step 3 : Create Operator.
         Under the SQL Server Agent, right-click Operators and select New Operator and configure accordingly.

Step 4 : Under the job properties for the backup, go to Notification tab and select email when the job fails:

Step 4 :

Notification for job failure


Vertica performance on select from view with union We have a huge number of rows(tens of millions), and for each table we have an associated view that is a select with UNION ALL between that table and several others.

Now, we are doing a select from that view with some conditions(where).
On Oracle and SQL Server the performance of the select is reasonable.
We have given a try to Vertica, because it is supposed to be good with selects on large tables, but the performance of this particular select is really bad.

I understand that when you do a union you lose the indexes on columns, because you can't get back to them.

What do you think is the problem with Vertica, is it missing some optimization steps that the other databases do ?

Vertica query performance depends highly on the predicate used in the query .

To get the gist of your performance , try getting the projection name of the selected columns of the query you are firing . The columns in the order by clause of the projection is very important in deciding the performance of your select. you can get that by running explain on your query .

Vertica improves performance by sorting the columns , compressing via encoding them so that they use minimal memory while running .

Also run analyze_histogram(tablename, 100) on all your tables . this will ensure statistics over complete data sample, not just 10% of data sample which is taken by analyze_statistics .

Also , since you are doing union you should try to keep the sort order of all the projections same as after union it might be meaningless .


Do I still need to disable or drop column store index before insert and/or update on big tables? As the title says, I thought that from MSSQL 2016 I don't really have to drop or disable ColumnStore indexes before ETL and rebuild it after.

Any consensus?

Correct, you don't have to.

For inserts be sure that ETL is saving data directly into compressed rowgroups, not the delta store.

If you want to update or delete data you need to be aware of the internal implementation of the updates on the column store. During DML operations data in a compressed column store rowgroup are never deleted or modified but just flagged as deleted. Updates are implemented as Insert/Delete.
Rows are physically deleted only during index reorg or rebuild.


Aurora Serverless alter table Error 1114 table is full Using Aurora Serverless, when attempting to alter table mytable add column mynewcolumn decimal(10,3) after myoldcolumn I receive "ERROR 1114 (HY000): The Table 'mytable' is full". The table has hundreds of millions of rows. Normally this error results from a full partition, but since I'm using Aurora Serverless, I can't manually provision partition space. What should I do to avoid this error?

On the AWS Forums, LawrenceAtAWS said,

  the amount of temporary storage space associated with Serverless increases linearly with the number of associated ACUs. If you're having issues running out of temporary storage space for large table mutations, you could temporarily scale the cluster up to a larger number of ACUs and then run the modify statement. This should increase the associated temporary storage an allow the statement to succeed.

The way to achieve this with the CLI is
  aws rds modify-current-db-cluster-capacity --db-cluster-identifier sample-cluster --capacity 64


SQL Server 2008 Patch on Clustered Server I am patching SQL server, going from SP1 to SP3, on a cluster while following Microsoft's instructions on how to do it.

Every single place I read tells me to do the following:

Pause the passive node
Update the passive node
Resume the passive node

And then it goes on, but when I try step number 2, I get the following error in the analysis before actually updating:

  Not clustered or the cluster service is up and online

If I try to do the update without pausing the node everything works fine, but I don't think that's a safe way to go.

What am I doing wrong here?

update:
I was actually following Windows itself patching instructions which states that the node should be paused. The instructions on how to patch SQL don't include pausing/resuming the node. But still, I believe this would be a best practice since something can happen to the active node making the cluster attempt to failover to the node being patch, with the results of that being unknown to myself.

Does anyone know what would happen?

2008 and later - patch passive node (B), failover, patch new passive (A)...done.


MySQL Replication : 1 Slave / Multiple Masters I have a web app with 2 web servers and 2 database servers.
The DBs are setup for multimaster replication. (this is the primary environment)

I also have the exact same setup on a different location acting as standby, in case the primary env fails. (this is the backup env)

What I need is for the backup env to be in sync with the dbs of the primary site.
However, all DBs in both environments have already replication configured.

How can I achieve my goal?

Thanks

This is actually an interesting scenario because I addressed this in the DBA StackExchange back on March 29, 2011.

The basic idea would be to use MySQL Replication in conjunction with DRBD and ucarp

Here is what you would need:

Two DRBD Clusters, one in each DataCenter
DRBD Pair at each DataCenter connected via a Crossover Cable (perhaps using 192.168.x.x subnet)
DRBD Primary has MySQL Running
DRBD Primary at one DataCenter running MultiMaster Replication with the DRBD Primary at other DataCenter via ucarp (in both directions)
Any needed read slaves at each Data Center would the DBVIP established for use within MySQL at that DataCenter via ucarp

Having this setup gives you block-level replication within both data centers. If you are using MySQL 5.5, you can have Semisynchronous Replication send SQL to the other DataCenter without waiting for the SQL to be executed, only acknowledged. This keeps any intermittency that standard MySQL Asynchronous Replication would normally cause a MySQL Master to a minimum. In the event of any sudden network latency, MySQL Replication between DataCenters would switch from Semisync to Async. Once conditions improve, then MySQL Replication would it switch back to Semisynch.

CAVEAT

This setup will not prevent table corruption, particularly if MyISAM is involved. Even with DRBD, block-replication of a corrupt MyISAM table would produce...you guessed it, a corrupt MyISAM table in the DRBD Secondary. Therefore, it is preferable if all user data were InnoDB.


Always Encrypted Certificate clarification I have configured AE using SSMS on the database server installed with SQL Server 2016. The certificate created under Local Machine option displays the validity as one year.

I just want to know what is the difference between creating a certificate using SSMS and creating a self-signed certificate using PowerShell.

What is a self-signed certificate means in the context of data encryption?

I just want to know what is the difference between creating a certificate using SSMS and creating a self-signed certificate using PowerShell.

You have much more control over the options used to create the self-signed certificate through Powershell. There is still the minimum options the certificate needs, but one example you've already mentioned is the valid date.

Other than that, the way it is done doesn't really matter. It's the output (certificate) that's needed.

  What is a self-signed certificate means in the context of data encryption?

In this case, not a whole lot. The SSMS cert is still a self signed cert. Self signing just means you're the only authority and no one else can claim you are who and or what you say you are which in this case, doesn't matter. The private and private keys will still be used to encrypt and decrypt regardless where the certificate was made, it just needs to be a valid cert.

You may or may not be bound by using an PKI ( Public Key Infrastructure) you have at your company or using HSM (Hardware Security Module) to store the keys versus just generating a local certificate on your machine for use in other places.


Return only one row from table with similar values in column that match a criteria I want to run a query from a table with similar values in the same column that match a certain criteria, but only return 1 row per similar value.

Let's say I have this simplified table:

Column1   Column2
  A           1
  A           2
  A           3
  A           4
  B           1
  B           2
  C           1
  C           3
  C           4
  D           2

I want to say, for example, return when Column 2 is not 3, and the result should be:

 B
 D

You can use the EXCEPT keyword:

CREATE TABLE #T1(c1 char(1), c2 int)
go

INSERT INTO #T1 Values('A',1)
INSERT INTO #T1 Values('A',2)
INSERT INTO #T1 Values('A',3)
INSERT INTO #T1 Values('A',4)
INSERT INTO #T1 Values('B',1)
INSERT INTO #T1 Values('B',2)
INSERT INTO #T1 Values('C',1)
INSERT INTO #T1 Values('C',3)
INSERT INTO #T1 Values('C',4)
INSERT INTO #T1 Values('D',2)
GO

SELECT c1 FROM  #T1
EXCEPT
SELECT c1 FROM #t1 WHERE c2 = 3

DROP table #T1


DDL contention on TempDB I have a SQL Server 2005 Standard x64 that is experiencing issues with TempDB DDL contention for the past few months. The server will experiencing contention on wait resource 2:1:103 (the wait type being PAGELATCH_EX).

The issue appears to happen sporadically when the server is under decent load. I've been monitoring "Temp Tables for Destruction" rate and it can jump to 5,000+ during times when we have issues of PAGELATCH_EX on 2:1:103. From what I've read this counter should be 0 the majority of the time, but ours seems to stay anywhere from 300-1100 the majority of the time. The counter only goes to 0 when there is very few users on the system.

How can I narrow down what is causing the DDL contention on tempdb without having to look for a needle in a hay stack?

I've seen this very issue and the hotfix that was ultimately released to fix it was actually a direct result of my case with Microsoft CSS. There is no public KB article for the fix. Please make sure you've applied Service Pack 4 and the most recent cumulative update to SQL Server (at the time of writing, that's Cumulative Update #3 (9.00.5259)).

Until the hotfix was released, Microsoft's suggestion was to simply stop creating #temp tables (much like KB #916086). Since this would have meant a substantial re-write of dozens and dozens of reporting procedures, the workaround in my case (regardless of trace flags or temp file layout) was to restart our cluster every other weekend. Yuck.

In order to track down tempdb usage, there are several scripts around that can help, e.g. see Adam Machanic's sp_whoIsActive, specifically:

sp_whoisactive: Analyzing Tempdb Contention
How to identify which query is filling up the tempdb transaction log?

And also this script (and ones in the comments) from @SQLSoldier:

Breaking Down TempDB Contention

I would make sure all your cursors are using LOCAL STATIC READ_ONLY FORWARD_ONLY (see this and this), and see if there are any known expensive queries that make extensive use of #temp tables / @table variables, CTEs, or may contain unnecessary sorts or lead to hash joins... all of which can contribute to the problem (I doubt you'll find one golden cause). The easiest sweeping fix as a "bang-for-your-buck" starting point will be to use proper and inexpensive cursor options instead of the defaults.

In the meantime I would (a) install CU#3 and (b) call PSS. Tell them you are after a very specific fix that has already been confirmed as a bug and released to other users as a private hotfix: "VSTS #109112 - Temp table deferred drop doesn't scale for certain workloads." You may have to pay the case fee initially but, since it is a bug, the charge should be refunded.


What can cause TRUNCATE TABLE to take a really long time? I'm running MySQL5.5 with Master/Slave replication (1 master, 2 slaves).

I have a process that runs once a week and truncate a specific table. The table is not large and only has a few thousands records.

For some reason, the TRUNCATE TABLE command takes really long time to execute (both on the master and on the slave). It takes about 400K ms to execute!! When it runs on the slave, it causes it to lag from the Master. After the TRUNCATE TABLE finishes, everything is back to normal.

I know that one of the slaves didn't receive any reads while performing the TRUNCATE TABLE since its a dedicated slave and the process that reads from that slave was down. Also, on this slave, it took the same amount of time to execute.

Here is the table structure: http://pastebin.com/qEQB4juR

Any thoughts on how I can speed up the TRUNCATE TABLE ?

Using TRUNCATE TABLE on an InnoDB table requires a full table lock because TRUNCATE TABLE is DDL (Data Definition Language) not DML (Data Manipulation).

Doing DELETE FROM user_engagements; will not help because MVCC info is written to the undo logs in ibdata1, and that can hold up the table from being emptied. If any uncommitted transactions are holding onto user_engagements, that could potentially hold up a TRUNCATE TABLE as well.

You could rename the table so that it is immediately available

SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE user_engagements_new LIKE user_engagements;
ALTER TABLE user_engagements RENAME user_engagements_zap;
ALTER TABLE user_engagements_new RENAME user_engagements;
DROP TABLE user_engagements_zap;
SET FOREIGN_KEY_CHECKS = 1;

This should replicate quickly except for the last statement.

Give it a Try !!!

If you have MySQL 5.1.16+, TRUNCATE TABLE requires DROP privilege. My answer performs what TRUNCATE TABLE now does.

If you have MySQL 5.1.15 and back, you need DELETE privilege, which my answer covers.


How do I find out what version of SQL Server for Linux I have? How do I find out what version of SQL I am running if I can't connect to it because it's crashing? Is there something like --version

$ ./sqlservr --version
Dump collecting thread [8633] hit exception [6]. Exiting.

Using dpkg

dpkg -l | grep -i mssql-server
ii  mssql-server                              14.0.3008.27-1                              amd64        Microsoft SQL Server Relational Database Engine

Using PAL_PROGRAM_INFO=1

You can set the undocumented environmental variable PAL_PROGRAM_INFO for a lot of information,

PAL_PROGRAM_INFO=1 ./sqlservr
sqlservr
  Version 14.0.3008.27
  Build ID 5fb3474a5f63ad2f4b7eddadad44a086839721f18a66c5fb5d7cfcce25c0f539
  Build Type release
  Git Version 693be7c
  Built at Sat Nov 18 04:54:59 GMT 2017

PAL
  Build ID a984d7631c4f1eb55ae1c77cfb51a09b905528d4e05e728a6343af04e744d96f
  Build Type release
  Git Version 2fe1654
  Built at Sat Nov 18 04:54:06 GMT 2017

Packages
  system.sfp                    6.2.9200.2,2fe1654c9,
  system.common.sfp             10.0.15063.540
  system.certificates.sfp       6.2.9200.2,2fe1654c9,
  system.netfx.sfp              4.6.1590.0
  secforwarderxplat.sfp         14.0.3008.27
  sqlservr.sfp                  14.0.3008.27


Oracle permission issue Please see the following statements. My question is, the table t1 was created in the users tablespace, so it will take up some space. But why it can be created before I impose the quota on the users tablespace?

Thanks.

sys@ORCL>create user a identified by a account unlock;

User created.

sys@ORCL>create table a.t1(c int);

Table created.

sys@ORCL>select owner, table_name, tablespace_name from dba_tables where table_name = 'T1';

OWNER                  TABLE_NAME             TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
A                  T1                 USERS

sys@ORCL>insert into a.t1 values(1);
insert into a.t1 values(1)
              *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

sys@ORCL>alter user a quota 10M on users;

User altered.

sys@ORCL>insert into a.t1 values(1);

1 row created.

sys@ORCL>commit;

Commit complete.

sys@ORCL>

BTW, I've tested using the user a to create the table before the user was imposed the quota. (Suppose the user has create session and create table privilege) The table still can be created. But in my example I'm creating the table using SYS user.

Edit:

This is the version information:

sys@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0  Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

sys@ORCL>

Because of deferred segment creation.

In Oracle 11.2, when you create a table with no data, you no longer allocate any space in the tablespace.  Oracle doesn't actually create the segment until you try to insert data into the table.  This is a difference from earlier versions in which the segment was created when the table was created rather than when data was inserted.

The reason for the new feature is that there are a lot of packaged applications on the market that create potentially hundreds of tables that will never have data because the customer isn't using some specific module of the project that uses those tables.  It was annoying for someone that packaged a big ERP tool, for example, to end up with potentially GB of space allocated to tables that would never have any data.

You'll only see deferred segment creation if you've set COMPATIBLE to 11.2 and if DEFERRED_SEGMENT_CREATION is set to the default of TRUE

SQL> conn / as sysdba
Connected.
SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.0.0
SQL> show parameter deferred

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL> create user deferred identified by deferred default tablespace users;

User created.

SQL> crant create table, create session to deferred;
SP2-0734: unknown command beginning "crant crea..." - rest of line ignored.
SQL> grant create table, create session to deferred;

Grant succeeded.

SQL> conn deferred/deferred
Connected.
SQL> create table test( col1 number );

Table created.

SQL> insert into test values( 1 );
insert into test values( 1 )
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'


use alter user to lock all user accounts except scott How do I use alter user to lock all user accounts and password expire them while picking out select users to exclude from the command?

You can't lock out a login except by attempting logins with incorrect passwords enough times to cause it to be locked out.  The ALTER LOGIN documentation shows the UNLOCK option, but there is no LOCK option.

Additionally, you can't force a password to be expired because the expiration is defined by the domain or local system policy.  You would have to change the policy to a date that would force the desired logins to have expired passwords, but there's no way you could ensure that would apply to only certain logins (unless you disabled password expiration on the others).


Why does adding a column to a MySQL table take incredibly long? I am using MySQL version 5.6 and I have a database that is in total around 110 GB in size. One of the tables, "outputs" is 49 GB in size. I tried to add a simple BOOL column to the outputs table with the following commands

ALTER TABLE outputs add spent BOOL;

that query took 6 days and 9 hours to complete. There was no index added, because it is a column with only two possible values. Does anyone have clues as to why this relatively simple query would take so long? FYI The outputs table has a primary key column and two other columns which are indexed. It has a total of 9 columns. I can edit this post if more information about my mysql configuration is required, but I'd need to know which parameters are important.

Adding a column in MySQL 5.6 requires the full table to be rebuilt.  This becomes online in MySQL 8.0 though, provided the column is at the end of the table! The MySQL manual covers this under 14.13.1 Online DDL Operations (search in page for "Adding a column"):

  Rebuilds Table?: Yes

Adding secondary indexes (i.e. not the primary key) should always be delayed until the last step so they can be sorted and optimally created. In the manual page I linked to, any operation that says In Place=No it will make sense to drop indexes first as well.


Summary statistics: Multiple joins and group by I have a sidewalk inspection system:

SIDEWALK_INSP

   INSP_ID
----------
         1
         2
         3

SIDEWALK_DEFICIENCY

    DEF_ID      INSP_ID   DEF_LENGTH
----------   ----------   ----------
        10            1           .5
        11            1            1
        12            1          1.5
        13            2            2
        14            2          2.5

SIDEWALK_OBSERVATION

    OBS_ID      INSP_ID   OBS_LENGTH   OBS_TYPE
----------   ----------   ----------   --------
       100            2            5      a
       101            2            6      b
       102            2            7      b
       103            2            8      b
       104            3            9      a
       105            3           10      a
       106            3           11      b

create table sidewalk_insp (insp_id number(10));
insert into sidewalk_insp values (1);
insert into sidewalk_insp values (2);
insert into sidewalk_insp values (3);
commit;

create table sidewalk_deficiency (
    def_id number(10),
    insp_id number(10),
    def_length number(10,2));
insert into sidewalk_deficiency values (10,1,.5);
insert into sidewalk_deficiency values (11,1,1);
insert into sidewalk_deficiency values (12,1,1.5);
insert into sidewalk_deficiency values (13,2,2);
insert into sidewalk_deficiency values (14,2,2.5);
commit;

create table sidewalk_observation (
    obs_id number(10),
    insp_id number(10),
    obs_length number(10,2),
    obs_type varchar(5));
insert into sidewalk_observation values (100,2,5,'a');
insert into sidewalk_observation values (101,2,6,'b');
insert into sidewalk_observation values (102,2,7,'b');
insert into sidewalk_observation values (103,2,8,'b');
insert into sidewalk_observation values (104,3,9,'a');
insert into sidewalk_observation values (105,3,10,'a');
insert into sidewalk_observation values (106,3,11,'b');
commit;

I want to calculate the following in a single query:

Length of deficiencies (per inspection)
Length of observations, where observation type = 'a' (per inspection)
Length of observations, regardless of type (per inspection)

The output would look like this:

+---------+------------+-------------------+----------------+
| INSP_ID | DEF_LENGTH | OBS_TYPE_A_LENGTH | OBS_ALL_LENGTH |
+---------+------------+-------------------+----------------+
|       1 |          3 |                   |                |
|       2 |        4.5 |                 5 |             26 |
|       3 |            |                19 |             30 |
+---------+------------+-------------------+----------------+

How can I create this query?

Here you go:

select
  si.insp_id, sd.def_length, so.obs_type_a_length, so.obs_all_length
from
  sidewalk_insp si
  left join
    (select insp_id, sum(def_length) as def_length from sidewalk_deficiency group by insp_id) sd
    on (si.insp_id = sd.insp_id)
  left join
    (select insp_id, sum(case when obs_type = 'a' then obs_length else 0 end) as obs_type_a_length,
            sum(obs_length) as obs_all_length from sidewalk_observation group by insp_id) so
    on (si.insp_id = so.insp_id)
order by
  si.insp_id
;

   INSP_ID DEF_LENGTH OBS_TYPE_A_LENGTH OBS_ALL_LENGTH
---------- ---------- ----------------- --------------
         1          3
         2        4.5                 5             26
         3                           19             30


Remote accessing a MySQL server for non-root user I have a MySQL server installed and running on an Ubuntu server. I use a Win7 PC to remotely access the server and for my 'root' user this works fine, I can start up MySQL Workbench, configure the connection to use the 'root' user and successfully connect and manage the database server.
My son will be doing A-Level computing and I thought it'd be good for him to have access to the database to learn with. Obviously, I don't want him to access everything on the server so I created a user in his name and a database for him to use. I've granted privileges for create, update, insert, delete and selectto him on his database only:-

grant insert, delete, update, create, select on 'hisdatabase'.* to 'hisusername'@'%';

So far, so good.
The problem I'm having is that when I use MySQL Workbench to set up a connection using 'hisusername' I get an error and can't login. The error dialog is:-

Failed to connect to MySQL at [server ip address]:[port] with user [hisusername]
Access denied for user [hisusername]@[Win7Client ip address] (using password: YES)

This connection works fine if I specifiy 'root' as the user but not 'hisusername'.
As user 'root' works perfectly, I'm guessing I'm missing a configuration parameter for 'hisusername' that is preventing remote access. Does anyone have any idea what this might be?

I found the solution! I needed to add "identified by" clause to the grant statement. I added that clause to the "create user" statement but I wasn't aware that it was required for adding permissions to the user as well!
It works great now.

P.S. So can you have different passwords to have different permissions for the same user? I get the idea of having different permissions based on where they're signing in from (localhost, ip-address, etc) but doing it based on what password they use seems odd (or is it just a by-product of the way the authentication system works?).


Getting Today's , MTD, YTD Sale and profit I have a database which give me sale and profit for each date. Schema looks like following :

Date         Sales      Profit

2015-03-07   133333     12348
2015-03-08   214563     34545
2015-03-09   299593     59940

Now I want to get results like following:

      Today    MTD     YTD

Sales

Profit

The brute-force approach I am using have 6 sub-queries with union.

It looks like following:

  SELECT 'Sales', TotalSales, 0, 0
  FROM SalesTable
  where Date = GETDATE()
  UNION
  SELECT 'Sales',0, SUM(TotalSales), 0
  FROM SalesTable
  where MONTH(Date) = MONTH(GETDATE())
  UNION
  SELECT 'Sales',0,0, SUM(TotalSales)
  FROM SalesTable
  where YEAR(Date) = YEAR(GETDATE())

and the similar for profit.

How can this be done in a more efficient/structural way?

Mocked up some data and used a couple of CTEs to generate the aggregates.  Similar to creating a bunch of separate queries joined or unioned however you wanted.  This provides a little more readability and flexibility.  I also added a variable to simulate running this for different dates.

    /*  Data mockup

    IF OBJECT_ID('SalesTable') IS NOT NULL
        DROP TABLE SalesTable;

    CREATE TABLE SalesTable
    (
          [Date] DATE
        , Sales  NUMERIC(19,4)
        , PROFIT NUMERIC(19,4)
    );

    INSERT SalesTable
    VALUES
           ('2014-12-07', 133333, 12348)
         , ('2015-03-07', 133333, 12348)
         , ('2015-03-08', 214563, 34545)
         , ('2015-03-09', 299593, 59940)
         , ('2015-05-07', 233333, 12348)
         , ('2015-05-08', 214563, 34545)
         , ('2015-05-09', 299593, 59940)
         , ('2015-09-07', 333333, 12348)
         , ('2015-09-08', 214563, 34545)
         , ('2015-09-09', 299593, 59940);
    */

    DECLARE @salesDT DATE;

    --SIMULATES PASSING IN A DATE TO AN SP
    --SET @salesDT = '2014-12-07';

    --SIMULATES A NULL BEING PASSED IN AND USING TODAY
    SET @salesDT = ISNULL(@salesDT, CONVERT(DATE,GETDATE()));

    WITH
    cteMonthlySales AS
    (
        SELECT MONTH([Date]) AS SalesMonth, SUM(Sales) MonthlySales
        FROM SalesTable
        WHERE MONTH([Date]) = MONTH(@salesDT)
        GROUP BY MONTH([Date])
    ),
    cteAnnualSales AS
    (
        SELECT YEAR([Date]) AS SalesYear, SUM(Sales) AS AnnualSales
        FROM SalesTable
        WHERE YEAR([Date]) = YEAR(@salesDT)
        GROUP BY YEAR([Date])
    )
    SELECT CAST(st.Sales AS MONEY) AS Today, CAST(sm.MonthlySales AS MONEY) AS MTD, CAST(ym.AnnualSales AS MONEY) AS YTD
    FROM SalesTable st
        JOIN cteMonthlySales sm ON sm.SalesMonth=MONTH(st.[Date])
        JOIN cteAnnualSales ym ON ym.SalesYear=YEAR(st.[Date])
    WHERE [Date] = @salesDT;


Tuning query to remove joins for reporting This is the query I was sent. There is currently no data in the system as I was converting this query from teradata to SQL server.

SELECT user_id FROM user_table
LEFT OUTER JOIN
(
SELECT user_id , MIN(event_date) FROM event_table WHERE event_code='859' GROUP BY user_id
) join_859
ON user_table.user_id = join_859.user_id
LEFT OUTER JOIN
(
SELECT user_id , MIN(event_date) FROM event_table WHERE event_code='223' GROUP BY user_id
) join_223
ON user_table.user_id = join_223.user_id

Would the following query return the same results and perform better?

SELECT user_table.user_id, event_table.event_date
FROM user_table
LEFT OUTER JOIN event_table ON user_table.user_id = event_table.user_id
WHERE event_table.event_code IN (
    '223',
    '859'
)

No it wouldn't (that is, it's not logically equivalent. Performance would probably be better though). Two reasons:

Both of the subqueries are aggregating to get the minimum event_date.
The second subquery (join_223) is an OUTER JOIN, not an INNER JOIN.

Incidentally, you have a repeated ON ON in the last line of the original query.


ERD directly producing 5NF relational schema - is this desirable? I am given a case study of Event Management to develop an entity-relationship diagram (ERD), and based on it check if the resulting relational schema meets the different normal forms. Now, I have developed my ERD based on the SRS that I have developed.

The question that I am having is: when I converted my ERD to the relational schema I got my tables already normalized. Because of the ERD I had nothing left to accomplish with respect to the normalization process, so I got confused.

Another question that arose in my mind is whether or not this is a desirable situation, I mean when I am getting my all the relations normalized from the ERD itself then why do I need to do normalization at all?

To put it differently, if we get the database already in 5NF from the ERD then why do we need to think of normalization?

If your tables are already in 5NF then you don't need to think much more about further normalization.

Note however that the principle of full normalization (POFN) is no panacea. There may remain non-obvious update anomalies.

Database constraints will need to be applied. In my experience, designers who start with an ER diagram are great at implementing primary keys and foreign keys but other constraints can be easily overlooked. Other design approaches may result in better constraints e.g. I have a little experience of object role modelling which seemed to work well.

Further normalization should only be considered in conjunction with constraints because 6NF often makes constraints more difficult to implement using today's industrial-strength technologies e.g. none support Standard SQL's CREATE ASSERTION nor CHECK constraints that may include a subquery.

Also consider the principle of orthogonal design (POOD).


using sp_executesql to run bulk insert I am trying to execute bulk insert statement using sp_executesql.
 I received syntax error:

  Msg 102, level 15, state 1Incorrect syntax near '\t'.

my code block are based on this link

Please review my code and advise what went wrong and how can I correct it.

declare @filepath nvarchar(500);
set @filepath = N'c:\bcp\textfiles\product.txt';

declare @bulkinsert nvarchar(2000);
set @bulkinsert =
N'bulk insert product from ''' +
@filepath +
N''' with (FIRSTROW = 2, FIELDTERMINATOR = "\t", rowterminator = "\n")';

exec sp_executesql @bulkinsert;

in product.txt file, the tab key is used as field delimiter
    below a sample from the text file
    Note: first row is 6 columns (productname column has no value)

  PRODUCTID PRODUCTNAME PRODUCTGROUPCODE    PRODUCTGROUPNAME    INSTOCKFLAG FULLPRICE
  10001     CA  CALENDAR    N   15

This has nothing to do with sp_executesql. If you replace the exec call with PRINT @bulkinsert; - an easy way to perform local debugging - you will see this output:

bulk insert product from 'c:\bcp\textfiles\product.txt'
  with (FIRSTROW = 2, FIELDTERMINATOR = "\t", rowterminator = "\n")

Put that in its own window, and you will see the exact same issue as the error you received is now highlighted by IntelliSense (note the red squiggly under the "\t" and the explanation in the tooltip if you hover over it):

The issue is you are using a double-quote (") as a string delimiter - this is not valid in T-SQL. You need to use single-quotes('') to delimit strings, but because the statement itself is embedded in another string, you need to double them up in order to escape them. So:

... @filepath + N''' with (FIRSTROW = 2,
    FIELDTERMINATOR = ''\t'', rowterminator = ''\n'');';

In fact, in the answer you cite, those are single quotes, not double quotes - perhaps a transcription error.


Can you have a WHERE clause with a greater than operator on a composite key? I'm a web developer (not DB admin!) having performance problems processing a 1.7m record table on MySQL. Here is that table :

CREATE TABLE `bbcmapimp_pc_raw` (
  `postcode` char(8) NOT NULL,
  `pc_sector` char(6) DEFAULT NULL,
  `pc_district` char(4) DEFAULT NULL,
  `pc_area` char(2) DEFAULT NULL,
  `parliamentary_constituency_code` char(9) DEFAULT NULL,
  `parliamentary_constituency` varchar(255) DEFAULT NULL,
  `mp_name` varchar(255) DEFAULT NULL,
  `district_code` char(4) DEFAULT NULL,
  `district` varchar(45) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `suffix` char(5) DEFAULT NULL,
  PRIMARY KEY (`postcode`),
  KEY `pcc` (`parliamentary_constituency_code`),
  KEY `suffix_index` (`pc_area`,`suffix`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I just need to iterate the table, but in my first attempt I used OFFSET which was horrendous! I've since learned what MySQL was doing under the bonnet, and understood why it was getting slower and slower.

So, I'm trying to modify my query to use a WHERE clause instead, but the issue is that I'm ordering the query by two fields (the table has a composite index defined).

One solution would be to add an ID field that's in the correct order and use that; but I was wondering if there was any way I could specify both fields of the composite index in a single greater than WHERE clause?

You can use

SELECT *
FROM `bbcmapimp_pc_raw`
WHERE (`pc_area`,`suffix`) > ('FO', 'BAR')
ORDER BY `pc_area`,`suffix`

Though actually this seems to have a better explain plan

SELECT *
FROM `bbcmapimp_pc_raw`
WHERE `pc_area` >= 'FO' AND (`pc_area` > 'FO' OR `suffix` > 'BAR')
ORDER BY `pc_area`,`suffix`


SQL Server 2008 R2 Mirroring issues I have this very frustrating problem that I've been trying to figure out for almost a week now. I have two SQL Server 2008 R2 STD w/SP3 box running on Windows 2012 R2 with two instances each, Server1 is the PRIMARY and Server2 is the MIRROR. Server2 was purchased for the purpose of mirroring so no live DBs yet. But Server1 is already in production. So here's the complete detailed setup for both.

Created a domain\user account and used it for both Servers' SQLService

Principal Server Configuration

Name: SERVER1.domain.com
NIC1 : 172.xx.xx.80
NIC2 : 172.xx.xx.81 (use for mirroring)
Named Instances: INSTANCE1 and INSTANCE2

Mirror Server Configuration

Name: SERVER2.domain.com
NIC1 : 172.xx.xx.82
NIC2 : 172.xx.xx.83 (use for mirroring)
Named Instances: INSTANCE1 and INSTANCE2

NO WITNESS YET

Static A records in DNS server

172.xx.xx.81 dbm-pri.domain.com and 172.xx.xx.83 dbm-mir.domain.com

Here's what's done on both servers:

checked SQL Server Config Manager and made sure TCP is enabled, then added the ports to be used in IPAll field
restarted the SQL Service (at one point of despair I restarted the servers itself, yes even the one running on production)
run netstat and confirmed that the endpoint ports are LISTENING, (sqlserver.exe), also run ping and tracert all tests are good
from both PRINCIPAL and MIRROR, ran telnet FQDN port, confirmed that I can connect from/to both
created the endpoints on both servers

PRINCIPAL

    CREATE ENDPOINT INSTANCE1_EndPoint
       STATE=STARTED
       AS TCP (LISTENER_PORT=6025,LISTENER_IP=(172.xx.xx.81))
       FOR DATABASE_MIRRORING (ROLE=PARTNER)

MIRROR

    CREATE ENDPOINT INSTANCE1_EndPoint
       STATE=STARTED
       AS TCP (LISTENER_PORT=6025,LISTENER_IP=(172.xx.xx.83))
       FOR DATABASE_MIRRORING (ROLE=PARTNER)

granted CONNECT permission to the domain\user account

CREATE LOGIN [domain\user]
FROM WINDOWS
WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

GRANT CONNECT on ENDPOINT::INSTANCE1_EndPoint TO [domain\user]
GO

backed up and restored the DB and logs with NO RECOVERY
ran alter db set partner on the mirror server

ALTER DATABASE DBName SET PARTNER = 'TCP://dbm-pri.domain.com:6025'

ran alter db set partner on principal server

ALTER DATABASE DBName SET PARTNER = 'TCP://dbm-mir.domain.com:6025'

Error:

  Msg 1418, Level 16, State 1, Line 1
  The server network address "TCP://dbm-mir.domain.com:6025" can not be reached or does not exist...

tested netstat, ping, tracert and telnet on both Servers, all are successful

I've tried re doing this so many times now and I'm lost for ideas

Have you tried using an IP address?  I have seen an issue in R2 in the past whereby SQL Server was unable to resolve an FQDN but was fine with an IP address.


SQL Server 2014 SP1 stops responding Recently I have upgraded my development machine to Windows 10 and SQL Server 2014 SP1 Express (12.0.4213).

Then I noticed that my application sometimes crashes with a timeout exception. It is quite a small .NET application with a 100MB database and few simple queries per second. The application is running at the same machine as the server and connects via shared memory.

I tried to connect to the server with SSMS when yet another timeout occurred, and found that the whole server is unresponsive. It took several attempts just to log in and I was unable to start the activity monitor due to timeouts. Then I tried to restart the server and it took about 1 minute to stop it.

The problem occurs several times per day. Sometimes it continues working normally after a timeout, sometimes I have to restart the server and it restarts quickly, sometimes it hangs for a while during restart.

The server never consumes more than 5% of CPU and 300 MB of memory, even during freezes. Usually there are few hundreds MB of free RAM and few GB of swap space available.

There are no errors or warnings in the event log at the time when the problem occurs.
Over the last week there were few errors: 17300, 17312, 28709. Not sure if they correspond to one of these timeouts. None of these occurred over last 2 days, but the problem continues to arise.

The same application never experienced any problems with SQL Server 2012 SP1 Express on Windows 7.

How this problem can be fixed?

Update

The application database had Auto Close enabled, I disabled it. Also I had to remove some data, so the database is now about 20 MB.

The timeouts on the application now occur less often (about 1 per day). However, the Activity Monitor in SSMS still timeouts almost always. Actions like viewing the database properties also execute very slowly (it takes about 20 seconds).

The sp_readerrorlog command shows nothing except startup info. Example.

I also have monitored the disk activity at the time I got a timeout in the Activity Monitor and the disk load was below 20%.

Do you have SQL Server Profiler installed? Try running a trace to capture any queries that may be causing excessive locks or resource problems throughout the day.


Find Max(value) for each department using GROUP BY MYSQL I want to print the name of student in each department who has the highest gpa,

Students(stuID: String, stuName: String, gender: String,
        birthdate: Date, enterYear: Year, gpa: Float)
Departments(deptName: String, numPhDs: Integer)
Majors(deptName: String, stuID: String, degreeProgram: String,
        attendYear: Year, attendSemester: String)

My query looks like

SELECT S.StuName, M.DEPTNAME
FROM Students S NATURAL JOIN MAJORS M
WHERE S.GPA=(SELECT MAX(S2.GPA) FROM STUDENTS S2)
GROUP BY DEPTNAME;

I am only getting one student's name and their corresponding department (MATHEMATICS). How can I get the student with the highest GPA for each department? Where did I go wrong on my query?

You're missing the ON clause in your join.
The GROUP BY is not necessary, since you're selecting the MAX(GPA) entry for each student anyway. For this, the subquery needs to relate to the outer query.

SELECT S.StuName, M.DEPTNAME
FROM Students S NATURAL JOIN MAJORS M ON S.stuID = M.stuID
WHERE S.GPA=(SELECT MAX(S2.GPA) FROM STUDENTS S2 WHERE S2.StuName = S.StuName)
;

Here's a manual entry regarding your problem, where you can also see other ways of solving this.


TDE Change Encryption Key - Is it safe? We use SQL Server 2012 Enterprise Edition and Transparent Data Encryption (TDE). Microsoft have a command to let you change the encryption on the database "ALTER DATABASE ENCRYPTION KEY". The docs page helpfully gives you the command, but it doesn't detail the process. I posted this question a the bottom of the page, but I thought that I might get a quicker answer here.

When I run this command, is the data left unencrypted on the disk at any point?
i.e. Does this command first unencrypt all the data using the old key/certificate and then re-encrypt it using the new one, which would mean that the data is unencrypted at some point in the process.
OR, is the process a pipe that takes in the data encrypted in the old key and spits out the data encrypted with the new key and the data is never stored on the disk unencrypted at any point.

Bonus question, what happens to the log file during the key change?

MS docs
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-encryption-key-transact-sql

Generally, for best results on Stack, you want to break each question up into its own question, but here we go:

Q: When I run this command, is the data left unencrypted on the disk at any point?

Not by the key change process, but be aware that filestream and replication data aren't encrypted regardless. For more details, check Books Online's section on TDE.

Q: i.e. Does this command first unencrypt all the data using the old key/certificate and then re-encrypt it using the new one, which would mean that the data is unencrypted at some point in the process.

No. For more details, see Microsoft's post on encryption key management.

Q: Bonus question, what happens to the log file during the key change?

Changes to the database are logged operations, so you'll need to watch the size and speed of your transaction log just like you did when you first applied TDE to the database.


Added R Services to SQL Server 2016 Enterprise - Cannot get past Msg 39021 SQL Server 2016 SP1 CU8

(Have already read the entirety of this answer Problem getting SQL Server 2016 R Services (In-Database) working, and all cited links)

We added R Services - In-database - from SP1 media, then re-ran the CU8 update, which detected and successfully patched the R Service.

The NT Service\MSSQLLaunchpad login has all 4 permissions stated for Local Policies => User Rights Assignment - viz:

Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
Bypass traverse checking (SeChangeNotifyPrivilege)
Log on as a service (SeServiceLogonRight)
Replace a process-level token (SeAssignPrimaryTokenPrivilege)

Server Windows Login MySQlServer\SQLRUserGroup is a server principal with public role membership.

All cited windows logins have express "Full" permissions to the ExtensibilityData folder, including all 20 of the MSSQLSERVERnn folders.

SQLRUserGroup has Full Control of the Working Directory.  (Services Restarted).

select CAST(SERVERPROPERTY('IsAdvancedAnalyticsInstalled') as int) as IsMLServicesInstalled
     , CAST(value_in_use as int) as ExternalScriptsEnabled
     , COALESCE(SIGN(SUSER_ID(CONCAT(CAST(SERVERPROPERTY('MachineName') as nvarchar(128))
                                   , '\SQLRUserGroup'
                                   , CAST(serverproperty('InstanceName') as nvarchar(128)
    )))), 0) as ImpliedAuthenticationEnabled
     , coalesce((select cast(r.value_data as int)
                   from sys.dm_server_registry as r
                  where r.registry_key like 'HKLM\Software\Microsoft\Microsoft SQL Server\%\SuperSocketNetLib\Tcp'
                    and r.value_name = 'Enabled'), -1) as IsTcpEnabled
  from sys.configurations
where name = 'external scripts enabled';

returns all 1's.

This has been run and the services restarted, including LaunchPad:

exec sp_configure 'external scripts enabled', 1;
reconfigure with override;

declare @login_name nvarchar(255) = CONCAT(cast(SERVERPROPERTY('MachineName') as nvarchar(128)), '\SQLRUserGroup', CAST(serverproperty('InstanceName') as nvarchar(128)));
if SUSER_ID(@login_name) is null
begin
    set @login_name = QUOTENAME(@login_name);
    exec('create login ' + @login_name + ' from windows;');
end;

select 'Machine Learning Services has been configured for use. SQL Server service needs to be restarted!' as Message

Everything has been installed and configured with either a Windows Local Admin or SQL sysadmin login.

This:

EXEC sp_execute_external_script  @language =N'R',
                                 @script=N'OutputDataSet<-InputDataSet',
                                 @input_data_1 =N'SELECT 42'
      WITH RESULT SETS (([TheAnswer] int not null));
GO

Always ends with:

  Msg 39021, Level 16, State 1, Line 1
  Unable to launch runtime for 'R' script. Please check the configuration of the 'R' runtime.

  Msg 39019, Level 16, State 1, Line 1
  An external script error occurred:
  Unable to launch the runtime. ErrorCode 0x80070490: 1168(Element not found.).

  Msg 11536, Level 16, State 1, Line 1
  EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

The rlauncher.config doesn't have embedded spaces in the working directory:

RHOME=C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES
MPI_HOME=C:\Program Files\Microsoft MPI
INSTANCE_NAME=MSSQLSERVER
TRACE_LEVEL=1
JOB_CLEANUP_ON_EXIT=1
USER_POOL_SIZE=0
WORKING_DIRECTORY=M:\SQLData\MSSQL13.MSSQLSERVER\MSSQL\ExtensibilityData

I've read every 'R' Install page in BOL and elsewhere I can web-search-find.

Finally, after four hours of head-bashing, I give up!

Can ANYONE, please offer assistance?

I know it is quite a while, but I had the very same error yesterday after
adding SQL R system Services to an SQL2016.

Besides the need to manually

add / grant  "NT Server\MSSQLFDLauncher" "LogonAsService"  (secpol.msc, or create a approriate Group)
add / grant "\SQLRusergroup"  "Allow logon locally" (secpol.msc)
to make SQLServer laucher start at all,

it turned out in my environment that the long name (I had something very similar to
"M:\SQLData\MSSQL13.MSSQLSERVER\MSSQL\ExtensibilityData") was causing a fuss -
despite it did not include a blank as described in some findings in WWW concerning this.

I did the following:

created a short folder name "E:\MSSQL_R"
xcopied the directories from e.g. E:\SQLData\MSSQL13.MSSQLSERVER\MSSQL\ExtensibilityData there (/s/e/v to include empty dirs)
Gave NT Service\MSSQLFDLaucher "FullControl" NTFS rights to this
changed the "WORKING_DIRECTORY" path in rlauncher.config to point there

et voila - test query worked after that.


How do I find duplicate values in multiple columns over different rows? I have a database with a table of games (some columns omitted):

gameid
hometeam
awayteam
`date`

Sometimes games are added with the same home and away teams, but on a different date that is just 1 day difference (game date moved), and the old game isn't deleted. I'd like to be able to search over a variable number of days as well. Thus, for football, having the same opponent in a time span of +/- 5 days would be wrong, but possible in basketball or baseball or many other sports.

It's easy to search for multiple values of the same home/away/date, but the date difference makes this harder.

Also, the home and away team may be swapped, with the date also being the same or slightly different.

Example data:

gameid hometeam awayteam date
5      777      999      2014-10-23
6      999      777      2014-10-23
7      777      999      2014-10-24
8      777      999      2014-10-25

All of these are duplicates. Determining which doesn't matter, just that it should let me know that there are 4 games scheduled for this which it should (probably) be 1.

This is what I use to find duplicated games for the same home/away/date:

SELECT COUNT(*) as num,hometeam as teamid,`date` FROM `game` WHERE sportid=1 AND 'deleted_at' IS NULL AND `date` BETWEEN '2014-07-01' AND '2015-06-30' GROUP BY `date`,hometeam HAVING `num`>1
            UNION
SELECT COUNT(*) as num,awayteam as teamid,`date` FROM `game` WHERE sportid=1 AND 'deleted_at' IS NULL  AND `date` BETWEEN '2014-07-01' AND '2015-06-30' GROUP BY `date`,awayteam HAVING `num`>1 ORDER BY `num` DESC;

Not sure why the question popped up now, but if you are still interested in an answer something like:

select g1.*
from games g1
where exists (
    select 1
    from games g2
    where g1.gameid <> g2.gameid
      and least(g1.hometeam,g1.awayteam)
        = least(g2.hometeam,g2.awayteam)
      and greatest(g1.hometeam,g1.awayteam)
        = greatest(g2.hometeam,g2.awayteam)
      and abs(datediff(g1.d, g2.d)) < 2
);

should give you what you need


change master to cause 1364 ip addr|grep inet
inet 192.168.8.47/24 brd 192.168.8.255 scope global ec1
inet 192.168.8.45/24 brd 192.168.8.255 scope global secondary ec1:1

I got like this

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: 192.168.8.47
                  Master_User: repl
                  Master_Port: 3310
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000126
          Read_Master_Log_Pos: 82811044
               Relay_Log_File: c60-relay-bin.000002
                Relay_Log_Pos: 426371
        Relay_Master_Log_File: mysql-bin.000125

the 8.47 ,will be remove on the master machine ,and then I do

on slave
stop slave ;
change master to master_host='192.168.8.45';
start slave ;

and then I got error like

Last_Error: Error 'Field 'xxx' doesn't have a default value' on query.

what the database do ? when change master master_host only?

how to resolve this problem ,and the right way to change master (change master to master_host,user,root..etc)?

on the slave

mysql> select @@sql_mode;
+------------------------------------------------------------------------------------+
| @@sql_mode                                                                         |
+------------------------------------------------------------------------------------+
| IGNORE_SPACE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

try with SET SQL_MODE = ''; or add this STRICT_TRANS_TABLES with existing data  modes


InnoDB engine getting disabled after decreasing innodb_page_size to 4K I'm trying to optimize my database (mariadb) for high write (insert & update).
Database is running fine with default  innodb_page_size (16K), but when I decrease the value of innodb_page_size=4K.
Database server starts successfully, but getting error while creating table on InnoDB engine.
Error Code: 1286. Unknown storage engine 'InnoDB'
Here is my.cnf
[mysqld]
log-error=/var/lib/mysql/mysql-3.kannel.com.err
performance-schema=0

max_allowed_packet=268435456

event_scheduler = ON
#innodb_page_size=16384
#innodb_page_size=8192
innodb_page_size=4K
innodb_file_per_table=4
innodb_buffer_pool_size=3G
innodb_buffer_pool_instances=2
innodb_log_file_size=1024M
innodb_log_files_in_group=4
innodb_log_buffer_size=64M
innodb_log_write_ahead_size=64M
innodb_flush_log_at_trx_commit =2
innodb_read_io_threads=32
innodb_write_io_threads=128
innodb_io_capacity=10000
innodb_thread_concurrency=2
innodb_flush_method=o_direct

join_buffer_size=512M
sort_buffer_size=20M
read_rnd_buffer_size=200M
datadir=/var/lib/mysql
max_allowed_packet=26843545600
#open_files_limit=10000
default-storage-engine=MyISAM
#default-storage-engine=InnoDB
max_connections = 500

Thanks!

max_allowed_packet is limited to 1G, not 26843545600.
join_buffer_size should not be more than 1% of RAM.
How much RAM do you have?


casting LEFT() from string to integer I have a date range column e.g. 1960-1970 in a table, I'm splitting these and copying them to new columns, I need to cast the string to an integer, the following code returns a generic syntax error, any ideas where I'm going wrong?

INSERT INTO rhp_tvp.time (
  visit_id_tmp,
  tvp_id_tmp,
  time_start,
  time_end)

SELECT
  v.visit_id,
  v.tvp_id,
  cast(LEFT(date_range, 4)AS int8),
  cast(RIGHT(date_range, 4) AS int8)
FROM tvp.visit v
WHERE  date_format = 'DMY' AND date_range NOT LIKE 'Pre%' OR date_range IS NOT NULL;

ERROR:  invalid input syntax for integer: "13/1"
SQL state: 22P02

I think you want something like this:

SELECT
  v.visit_id,
  v.tvp_id,
  LEFT(date_range, 4)::INT,
  RIGHT(date_range, 4)::INT
FROM tvp.visit v
WHERE  date_format = 'DMY' AND date_range NOT LIKE 'Pre%';

To answer your question I did the following:

Created a table:

CREATE TABLE my_range(the_range VARCHAR(9)); -- just the field of interest!

Some sample values:

INSERT INTO my_range VALUES ('1969-1986');
INSERT INTO my_range VALUES ('1932-1987');
INSERT INTO my_range VALUES ('1956-1999');

Query:

SELECT LEFT(the_range, 4)::INT, RIGHT(the_range, 4)::INT FROM my_range;

Result:

Left Right
1969 1986
1932 1987
1956 1999

See the dbfiddle here.

You can check that they are now integers by running:

SELECT LEFT(the_range, 4)::INT + 20, RIGHT(the_range, 4)::INT - 20 FROM my_range;

And you can see that addition and subtraction are indeed performed on the new values in the fiddle.


Executing Stored Procedure that accesses another SQL instance I apologize if this question repeats another already asked.  I have searched for hours and have not found one that fits my situation.

Desired Outcome

A user using SQL authentication has execute permissions to Database1 on Server1 (default instance) and that is it.  The user executes a stored procedure that, as part of its process, accesses Database 2 on Server1\Instance2.  I would like it to be safe and simple (both are important).

More Info

My windows credentials has access to both instances (which are on the same server).  Therefore, I can execute the stored procedure under my login without difficulty.  However, I don't want to give the user my level of access.  I also need to use a SQL login since the user will not be on the domain.

What I would like would be to give the stored procedure my level of access just for that procedure.  Since I am a sysadmin, that would give the user everything they needed for that procedure.  If I got that to work, I would probably create an account just for that purpose instead of using mine, but either way it would be safe since I control what the stored proc does.

I tried putting the "WITH EXECUTE AS" statement in my stored proc but I couldn't get it to take my windows login information.  When I put it in, I would get the following error upon compiling the stored proc:

  Cannot execute as the user 'domain\jdoe', because it does not exist or you do not have permission.

The user is sysadmin on both servers, like I said, so I'm not sure what more it needs.

I have looked into the following:

TRUSTED - I would rather not expose my database and this looks scary
Linked server - I don't want to give extra permissions.  I don't trust the other database to have access to my database and I don't trust my database to have access to all of the other database.
Certificates - This seems complicated and difficult.  Unless I could find a very simple way to do this and maintain it, I'm not sure it is worth the trouble.
Ownership chaining - Again, scary.  It looks like this causes more security issues when my goal is to prevent security issues.
Mirrored user - I've even created the same (different SID obviously) user on the other server instance and gave it the same password.  No go.

I feel like I am missing something obvious but I'm not sure what it is.  Since I've been banging my head against the wall all day on this, I'm probably too close to see it.  I would very much appreciate it if someone here could give me a hand or point me in the right direction.  I will say that I have read a lot of the MSDN articles (boy do I hate them - they never seem to tell me what I want to know).  What I would really like is a simple, easy to follow tutorial that walks me through how to do this.  Short of that, even a general indication of the direction I need to go would be helpful.

Try using EXECUTE AS LOGIN = 'DOMAIN\username' instead and see if that works.


Cannot find one or more components. Please reinstall the application I am getting this error in Windows Server 2012 with Visual Studio 2012 installed trying to open SQL Server Management Studio 2012:

  Cannot find one or more components. Please reinstall the application

I tried many things, including installing SQL Server Management Studio 2014, uninstalling, reinstalling… Then repairing VS2012, installing SSMS 2014 again. All to no avail.

Uninstall the Visual Studio 2010 Shell, this was done by downloading the installer.

Once downloaded, run the installer and remove the VS2010 Shell component. Once done, run the SQL 2014 Management Studio installer or do a repair on your existing installation, this will itself reinstall the VS2010 Shell components.

Then it opened for me.

In Order To Repair Sql Server Management Studio Do Below Steps -

Open Control Panel Then Click on Program and Features
Then Find Microsoft Sql Server 2012 , Right click on it and choose uninstall
Then uninstall wizard will open and Follow the wizard steps to uninstall it.


Query performance issues I need help in improving the performance of the following query. It has the purpose to select one record out of a group of records with an identical ID (doublettes). Rules are that Date2 must be the most recent and Date1 must exist:

For each distinct ID in InputTable (= rst(0) ) do:

INSERT INTO FinalTable(ID, FID, KeyValue1, KeyValue2, Date1)
SELECT InputTable.ID,
       InputTable.FID,
       InputTable.KeyValue1,
       InputTable.KeyValue2,
       InputTable.Date1
FROM
  ( SELECT TOP 1 *
    FROM
      ( SELECT *
        FROM InputTable
        WHERE ID='" & rst(0) & "' And Date1<>''
      )
    WHERE Date2 =
          ( SELECT Max(Date2)
            FROM InputTable
            WHERE ID = '" & rst(0) & "' and Date1 <> ''
          )
  );

The InputTable has about 400.000 records and ~200.000 distinct IDs and only contains the 5 fields that will be in the Final Table, all of which are TEXT.

InputTable is an importet .txt file and i am working with ACCESS outside of any real database. The Result will be again written into a .txt file.

It takes about 1 second for each ID, which is too long... Is it possible to improve the performance significantly?

I really appreciate any help.

Performing those subqueries repeatedly for each distinct ID value is what's killing you. I ran a test of your method on a sample table of 10,000 rows and it took ~2.5 minutes to execute on my notebook. By comparison, the following code consistently returned the same results in less than 0.5 seconds:

Option Compare Database
Option Explicit

Sub UsingAggregateQuery()
    Dim cdb As DAO.Database, t0 As Single
    Set cdb = CurrentDb
    cdb.Execute "DELETE FROM FinalTable", dbFailOnError
    t0 = Timer
    cdb.Execute _
            "INSERT INTO FinalTable(ID, FID, KeyValue1, KeyValue2, Date1) " & _
            "SELECT InputTable.ID, " & _
            "       Last(InputTable.FID), " & _
            "       Last(InputTable.KeyValue1), " & _
            "       Last(InputTable.KeyValue2), " & _
            "       Last(InputTable.Date1) " & _
            "FROM " & _
            "    (" & _
            "        SELECT ID, FID, KeyValue1, KeyValue2, Date1 " & _
            "        FROM InputTable " & _
            "        WHERE Date1 <> '' " & _
            "        ORDER BY ID, Date2 " & _
            "    ) " & _
            "GROUP BY ID;", _
            dbFailOnError
    Debug.Print Format(Timer - t0, "0.0") & " seconds"
    Set cdb = Nothing
End Sub

Edit

I just ran another test on a sample table of 400,000 rows and the code above completed in under 7 seconds


Selecting rows ordered by foreign key count and filtering by date I've got myself in a little pickle with an SQL query that is a bit out of my experience.
Here's the two tables I'm using:
                                          Table "public.Vote"
  Column   |              Type              | Collation | Nullable |              Default
-----------+--------------------------------+-----------+----------+------------------------------------
 id        | integer                        |           | not null | nextval('"Vote_id_seq"'::regclass)
 createdAt | timestamp(3) without time zone |           | not null | CURRENT_TIMESTAMP
 serverId  | integer                        |           | not null |
Indexes:
    "Vote_pkey" PRIMARY KEY, btree ("createdAt")
    "Vote.id_unique" UNIQUE, btree (id)
Foreign-key constraints:
    "Vote_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"(id) ON UPDATE CASCADE ON DELETE CASCADE
    "Vote_serverId_fkey" FOREIGN KEY ("serverId") REFERENCES "Server"(id) ON UPDATE CASCADE ON DELETE CASCADE

                                          Table "public.Server"
  Column   |              Type              | Collation | Nullable |               Default
-----------+--------------------------------+-----------+----------+--------------------------------------
 id        | integer                        |           | not null | nextval('"Server_id_seq"'::regclass)
 title     | text                           |           | not null |
 content   | text                           |           |          |
 createdAt | timestamp(3) without time zone |           | not null | CURRENT_TIMESTAMP
Indexes:
    "Server_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "Server_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"(id) ON UPDATE CASCADE ON DELETE CASCADE
    "Server_versionId_fkey" FOREIGN KEY ("versionId") REFERENCES "Version"(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE ""Vote"" CONSTRAINT "Vote_serverId_fkey" FOREIGN KEY ("serverId") REFERENCES "Server"(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE ""_ServerToTag"" CONSTRAINT "_ServerToTag_A_fkey" FOREIGN KEY ("A") REFERENCES "Server"(id) ON UPDATE CASCADE ON DELETE CASCADE

Here's how my current query looks:
SELECT s.id, s.title, s.content, count("serverId" WHERE "createdAt" >= '2020-01-01 00:00:00') AS "voteCount" FROM "Server" AS s LEFT JOIN "Vote" AS v ON (s.id = "serverId") GROUP BY s.id ORDER BY "voteCount" DESC;

At the moment the above query does this:

Selects all entries in "Server"
Counts how many entries in "Vote" each "Server" has
Orders the output data by "voteCount"

It's working pretty well, but now I would like to add an extra layer of complexity to it and only count votes made after a certain date.
So I came up with this query:
SELECT s.id, s.title, s.content, count("serverId") AS "voteCount" FROM "Server" AS s LEFT JOIN "Vote" AS v ON (s.id = "serverId") WHERE v."createdAt" >= '2020-01-01 00:00:00' GROUP BY s.id ORDER BY "voteCount" DESC;

While the above technically does work, it's also not outputting rows in "Server" that have no rows in "Vote". Is there any way to get those rows as well?

So this is happening because even though you used a LEFT JOIN, you're filtering on a field in the right-side table of that LEFT JOIN in the part of your query that does WHERE v."createdAt" >= '2020-01-01 00:00:00' so this removes all the records in your left-side table "Server" where there are no rows it could join to in the "Vote" table (i.e. where createdAt is null).
You can add this additional condition in your WHERE predicate to fix that:
SELECT s.id, s.title, s.content, count("serverId") AS "voteCount"
FROM "Server" AS s
LEFT JOIN "Vote" AS v ON (s.id = "serverId")
WHERE v."createdAt" >= '2020-01-01 00:00:00'
    OR v."createdAt" IS NULL
GROUP BY s.id ORDER BY "voteCount" DESC;


SSIS Connection String Expression at Run Time and during Execution I have an Excel connection manager which should write to a file which does not exist prior to runtime but rather is created during execution. I have an expression set for the connection string property of the excel connection manager which points to the file that will be created. The problem I am having is that since the file does not yet exist (prior to execution), when I go to run the package it is giving me an error since it can not find the file. If i create a dummy file and place it where the excel connec manager is pointing the package will run ok.

What I have tried to do is point the excel connection manager to an existing file prior to run time since it will not give an error and then hopping it will then pick up the new Path (excel connection string) from the Expressions but what is happening is that since I have an expressions set for the connection string property it will not let me overwrite the connection string property, it keeps defaulting to the value of the expressions which contains other variable that are populated during run time so that is where I am getting my error. The expressions string prior to runtime is not pointing to an actual file because it does not yet exist but mainly because the expressions is not fully populated until runtime since it also contains variables.

As of right now it seems like I might have to just point to a dummy template file (without using any expressions) prior to runtime and then maybe use a Script to change the connection string property of the excel connec manager to = a variable containing an expression containing the path to the file....

Does this sound like the way to go  or am I over complicating things?

When a package receives a signal to start, one of the tasks it performs is to ensure everything it needs is available and has the appropriate signatures. SSIS is all about the metadata. The idea behind this is that a package should blow up before it does any work if it can tell at the start that an error will occur (can't connect to a server, table doesn't exist, data type is incompatible, etc). It's generally a good thing, why start something you know you can't finish and potentially leave yourself in a messy/inconsistent state.

In your scenario, it is validating that this file is there and since it isn't, because it's generated at run-time, it fails. Therefore, you have to flag it such that the validation occurs right at the moment the resource is needed. As @mike Henderson indicated, you'll need to go into the properties of the objects and change the DelayValidation value from False to True. You will probably need to set it on both the Connection Manager and the Data Flow Task that uses it. Click once on the item, hit F4 or right click and select Properties.


After migrating from MySQL 5.7.16 to MariaDB 10 database fails to start MySQL Server Version is 5.7.16-0ubuntu0.16.04.1 (Ubuntu)

I want to switch over to MariaDB, but I can't get MariaDB to accept the existing database. I also tried doing a slow shutdown with InnoDB Engine and importing the database as .sql created with mysqldump.

The import of the .sql fails because the mysql.user table is different and I need to keep passwords, users and grants.

After migration I am getting this error:

  Fatal error: mysql.user table is damaged or in unsupported 3.20 format.

This is the final error before the DB fails to start.

Also mysql_upgrade does not work.

How should I perform the migration?

Edit:

This didn't work either (Official Bug Report).

Fixed this by dumping the MySQL database and reimporting it with MariaDB.


Optimize Table query causing Fatal error during execution with InnoDB table I have a problem with OPTIMIZE TABLE query with one of my InnoDB database tables.
I have a database that contains several different tables and data is constantly accumulating in these tables. However I have my own automatic services that I use for deleting older rows from these tables.
After deleting old rows with my service I also use optimize table command to free up disk space. (innodb_file_per_table = 1)
Everything has been working well (I have used these services successfully with other tables (size 100-200MB) but now I have faced a problem with one larger table (size 900-1000MB).
Deleting data with that larger table is working fine, but when I try to use OPTIMIZE TABLE query I get error:

Error on mysql optimizing log_events table: Fatal error encountered during command execution.   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at CleanTablesService.Service1.siivoaTaulut(Object& yhteys) in c:\users\green automation\documents\visual studio 2015\Projects\CleanTablesService\CleanTablesService\Service1.vb:line 127

I assume that this has something to do with some buffer_size or something like that? I'm able to execute this exactly same query successfully with MySQL Workbench but when executing query on my service it throws this error.
So is there some value in my.ini file that I need to adjust so that I can make it work or is there some other method that I should use for optimizing table size  (releasing disk space after I have deleted old rows)?
EDIT:
I added some more logging to my service and I noticed that this Fatal error is raised 30s after executing OPTIMIZE TABLE. In MySQL Workbench execution of this same operation takes 67.110s so probably problem is that for some reason MySQL is killing execution in the middle of the process after 30s?
However I cannot find any parameter from my MySQL Settings file my.ini that would affect this 30s timeout.

Ok I feel really stupid now...
Problem had nothing to do with MySQL Server settings etc.
Problem was that in my service (that is communicating with MySQL) I'm using MySqlCommand Object (Service is coded using VB NET). This MySqlCommand object has a parameter CommandTimeout and its default value is 30. So that was the reason why executions longer than 30s caused timeout that throws fatal error..
So now I'm setting this parameter large enough in my VB NET code before executing command and everything is working as planned!


Delayed transactional replication setup? I will need to setup transactional replication for a big database. I use backup/restore to initialize the database because it's too big to use snapshot over slow network. So the following steps are used

Backup the database
Copy the the backup files
Restore the database on subscriber
Setup replication

However, What if there are some data change (insert/update/delete) between after step 1 and before step 4? Will these transactions lost? Or the replication setup will fail? How to resolve the problem?

To initialize a Transactional subscription from a backup you actually end up creating the publication first, enabling it to initialize subscriptions from a backup, then take a backup, and then initialize subscriptions with the backup.

Changes that occur after taking the backup and before initializing the subscription(s) will be stored in the distribution database, which acts as a store-forward queue.  Those changes will then be replicated to subscribers after they have been initialized from the backup.

Note that by default the distribution cleanup jobs clears transactions older than 72 hours from the distribution database which is based on the publication retention period.  You may want to temporarily disable this job before the backup and re-enable it after you initialize the subscriptions from the backup.

Have a look at Initialize a Transactional Subscription Without a Snapshot and Initialize a Transactional Subscription from a Backup.


Handling time zones in data mart/warehouse We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and in books), a common solution seems to be to have a separate date and time dimension as well as a timestamp in the fact tables.

However, the question I am having hard time answering is what good does the date and time dimensions actually do for me considering my dynamic time zone requirements? A time dimension makes a little more sense but I'm having hard time with the date dimension. A general design approach for a date dimension usually includes properties such as day name, day of week, month name, etc. The problem I am having with all that is that 11:00 PM on Tuesday, December 31, 2013 in UTC is Wednesday, January 1st, 2014 in all time zones that are after UTC+2.

So if I will have to do all these time zone conversions on each and every query (and report) then what is the point of having and storing these properties that I will probably never use (seems like)? Some people suggest having fact rows for each time zone but that seems ridiculous to me. We need to be able to store millions of records each month.

Others suggest having a time zone bridge table which although makes some sense, it also seems like extra complexity and extra joins to accomplish something that my client apps and reports should easily be able to figure out from a date (reporting will be primarily web-based where there are a myriad of libraries to aide in converting, displaying and formatting dates).

The only thing I can think of is the ease and possibly performance of grouping by date and hour but how bad of a practice is to group by datepart (we're using MS SQL but we will be querying millions of rows) or should we consider just extremely simple date and time dimensions with not much more than hour, day, month and year numbers for the most part as most literals such as Monday wouldn't mean much when time zones come into play?

Firstly...

Separating Datime/Time into a Date dimension and a Time dimension is definitely the way to go.

To manage multiple time zones you need to duplicate the DateKey and the TimeKey so that you have the following:

LocalDateKey
LocalTimeKey
UtcDateKey
UtcTimeKey

You say...

  The problem I am having with all that is that 11:00 PM on Tuesday,
  December 31, 2013 in UTC is Wednesday, January 1st, 2014 in all time
  zones that are after UTC+2.

By having the 4 columns I've listed above you, will be able to join the fact table to the Date and/or Time dimension Using Table Aliases (in Kimball terminology these aliased dimension tables are known as "Role Playing Dimensions"), so you would have something like the following:

/*
    Assumes the following:
        - [DateLongName] has the format of this example "Tuesday, December 31, 2013"
        - [TimeShortName] has the format of this example "11:00 PM"
        - Both [DateLongName] & [TimeShortName] are strings
*/
select
    -- Returns a string matching this example  "11:00 PM Tuesday, December 31, 2013"
    localTime.TimeShortName + ' ' + localDate.DateLongName
    ,utcTime.TimeShortName + ' ' + utcDate.DateLongName
    ,f.*
from
    FactTableName  AS f

    -- Local Date and Local Time joins
    inner join dbo.Date  AS localDate
        on localDate.DateKey = f.LocalDateKey

    inner join dbo.Time  AS localTime
        on localTime.TimeKey = f.LocalTimeKey

    -- Utc Date and Utc Time joins
    inner join dbo.Date  AS utcDate
        on utcDate.DateKey = f.UtcDateKey

    inner join dbo.Time  AS utcTime
        on utcTime.TimeKey = f.UtcTimeKey

In closing...

As you're building a data mart, and not an OLTP database, the generation of the Local and Utc times should be performed in your ETL, NOT in any client side applications for the following reasons (apart from localisation of the UTC time to the report reader's perspective):

Having the calculation reside in any queries places an extra performance burden on them, multiplied by the number of times you have to run said query for any reports you have (this matters when reading millions of rows)
Extra burden of ensuring the calculation is maintained correctly in each query (especially when you take daylight savings time into account)
Prevent range scanning of any indexes the column is part of, as you'll be performing a calculation on the column which forces queries to perform index scans instead of seeks (which are typically more expensive as each data page is needed to be read); this is known as being non-sargable.

Edit due to comments: This applies if you push the conversion down into the actual query.

Using the concept of having the additional UTC dates and times available, there is nothing stopping you from taking this concept and extending it by calling this StandardisedDateKey, or CorporateHQDateKey, where instead of a UTC date table you standardise based on some other business agreed standard
Having the two separate column types (Local and UTC), allows for side-by-side comparison across geographic distance. Think -> someone in Australia enters a record that is timestamped with both Local and UTC, someone in New York reads the report with the Local (Australia) date and time and the New York representation of the UTC date and time, thereby seeing that something their Australian counterpart did during the middle of the day (Australia time) happened in the middle of the night their time (New York time). This comparison of time is indispensable in multi-national businesses.