Separating data to different tables relating to the same object I am unsure what is the correct practice here. I have a collection of rows with fixed values in the columns....consider them fixed attributes for a particular object of a given ID. So i guess you could say a read-only table.

Now I am also about to add a sizable number of more columns of which their values change (well over 30 columns most likely as the project progresses). Rows won't be deleted or inserted frequently, as there is one row per object, similar to the read-only table.

This will increase the number of columns by quite a large amount, of which, I read large column counts is not ideal. So i was wondering should i separate these columns to another table so that fixed values are in one table, changing values in another?

Are there any benefits to doing this? Or is there no point for something with less than 50 columns in a table?

I don't know the terminology for a lot database stuff, I am self taught from google. So am looking for advice from people who know how to structure databases to work at their best.

One concern with a large column counts is to do with the number of rows which can fit in a page. DBMS performs IO one fixed-size page at a time. If any part of a page is needed, all of it is read into memory where the required bit is processed. So, if there are a small number of short columns many rows can fit in a single page. Conversely tables with many, long columns can only fit a few rows per page and more IO is required to retrieve a given number of rows.

Is this good or bad? Well, it depends. (If you're new to database design you'd better get used to hearing that phrase!) If you only ever look for a single row at a time it would be better to have all the columns in single table so one IO will return all the values to memory. They are likely to stay there for a while, meaning subsequent actions on the row will not have to do physical IO. This is great for a typical transaction processing workload where an item goes through a number of steps until it reaches completion.

If your workflow is more BI oriented, where you need sums and averages and whatnot, then a query will retrieve many rows and will benefit from having as many rows per page as possible. Ideally, those rows should only have the columns required to answer the query. Other columns would be better held in a secondary table.

Sadly, few systems are this cut-and-dried. Often there is a mix of OLTP and BI work. No one query is so much more important than the others that it drives the DB design. The additional IO cost of referencing a secondary table can be troublesome. This is where the art takes over from the science of being a DBA. You have to understand the intended usage and juggle options accordingly.

For my two cents, without any understanding of your actual workload whatsoever, I'd suggest you keep everything in a single table for now. Anything else is premature optimisation.  When you run into an actual problem you can't solve another way, that's the time to split tables. The additional complication of keeping the secondary tables synchronised with the primary can be surprisingly tricky.

Before then covering indexes will help a lot. Materialised views are useful, too. Neither are free lunches, of course, so understand the costs and benefits before deploying either.

Should you decide to split over several tables I'd suggest keeping together those columns which are used together in a single business action. Mixing read-only and updatable columns in a table is not a problem. I can think of no advantage of separating them on those lines only.


Checksum of data imported from mysqldump not equal to the original checksum? I'm tring to create a mysql replication instance.
So I mysqldump --complete-insert --skip-dump-date -uuser -ppass db table > master.sql.

Also I've imported the master.sql to the slave instance without starting slave.
But now I find the checksums not the same.

Dumped with the same mysqldump options, diff master.sql slave.sql only prints out:

5c5
< -- Server version 5.6.30-0ubuntu0.14.04.1-log
---
> -- Server version 5.6.30-0ubuntu0.14.04.1

Any ideas? Why the checksums not equal?

Thanks in advance.

In the file slave.sql, modify the top line, delete -log. Then re-diff your files. If that's the only difference (and it appears to be), then there's no problem - the data part of your mysqldump is identical! (The small "difference" appears to be in the header generated by mysqldump).

A checksum will vary greatly even if the file contents differ by only one bit! A discrepancy of 4 characters is, to all intents and purposes, enormous in this context.

From here:

  Depending on its design goals, a good checksum algorithm will usually
  output a significantly different value, even for small changes made to
  the input. This is especially true of cryptographic hash functions,
  which may be used to detect many data corruption errors and verify
  overall data integrity;

(and the really important bit)

  if the computed checksum for the current data
  input matches the stored value of a previously computed checksum,
  there is a very high probability the data has not been accidentally
  altered or corrupted.

This is a facinating topic all in its own right and the maths gets very difficult very quickly. Just one thing worthy of note is the "Birthday attack".

This means that even if you have matching checksums, there is a small  (in reality, infinitesimal) possibility that your data does not match. Day-today, this is not a problem - included for completeness.


mysql set auto increment to 1 with foreign key I want to set auto increment to 1 by below command, but it doesn't works:

ALTER TABLE tablename AUTO_INCREMENT = 1

I searched and found out that I should use TRUNCATE TABLE. But this command fails due to foreign key constraint. Any idea?

If you ever need ALTER TABLE tablename AUTO_INCREMENT = 1 you are probably misusing AUTO_INCREMENT.  AUTO_INCREMENT is designed to provide unique keys, nothing more.  If you need it to be "1", you should invent your own mechanism; do not trust AUTO_INCREMENT.

Would you care to explain your purpose?  Perhaps we can help you find a suitable workaround.  (No "setting it to 1" is not the purpose, it is your attempted solution to some higher purpose.  Step back.)


Use filters instead of queries in MS Access? I realize many things could be done in Access like instead of using queries for filtering things, it is posible to use filters.
I'm not sure if the program makes SQL with VBA queries under the hood or the inner working is different, which in the case of the second:
What is better of the two?
Is it wrong to use filters and even making forms that make use of filters instead of making queries for filtering results?

No it isn't wrong, as both are the same, only the display is different, it simply doesn't matter.
Use the view you can best solve your problems.
Forms are only a gui, for your data. So that user can access , edit or delete data, without knowledge of sql or filters.
AS today a html form and web browser are much more common  and many different rdms server are available, the usage of access is reduced and that much new functionality in the newer Versions is almost not there.


PostgreSQL streaming replication with slots works for one slave, but not for another slave Master (10.0.0.1):

Edit postgresql.conf

listen_addresses = '*'
wal_level = logical
max_wal_senders = 3
max_replication_slots = 3
hot_standby = on

Edit pg_hba.conf

host    replication     postgres    0.0.0.0/0   trust

Create a replication slot for the slave

select * from pg_create_physical_replication_slot('master');

Slave #1 (10.0.0.2):

Remove 9.4/main directory

rm -rf /var/lib/postgresql/9.4/main

Do a base backup of the master

pg_basebackup -P -R -X stream -c fast -h 10.0.0.1 -U postgres -D /var/lib/postgresql/9.4/main

Edit recovery.conf

standby_mode = 'on'
primary_conninfo = 'user=postgres host=10.0.0.1 sslmode=prefer sslcompression=1 krbsrvname=postgres'
primary_slot_name = 'master'

Copy conf files from master

rsync -av user@10.0.0.1:/etc/postgresql/ /etc/postgresql/

Restart cluster

pg_ctlcluster 9.4 main restart

Slave #2 (10.0.0.3):

Same steps as for slave #1 except the ip of the slave is different.

The problem: A change on the master propagates the change onto slave #1 but not slave #2. Tried rebooting slave #2 and setting up again from a fresh install but still doesn't work. Any ideas?

Thanks!

Community Wiki answer generated from a comment left by the question author. Feel free to edit to improve

Nevermind, I fixed it. There needs to be one replication slot per slave. I added another replication slot "master2". So slave #1 is using the "master" replication slot and slave #2 is using the "master2" replication slot. Works fine now.


Reserving dates with quantities, table schema wrong? Okay I have a table like so.

ID   Productname  Quantity          Image       QuantityOut  LastDateReserved

1    productOne          5     'image.jpg'                 3       2013-03-15
2    productTwo          1     'product.jpg'               2       2013-03-02

I have a page that checks for the last part of an event date + 5 days buffer. Now here is where I get really confused. Say for instance I want to check out only 3 of productOne, how would I make that work with this table schema? Do I need to add more fields or tables? The way I have it now is that my LastDateReserved would be overwritten with a new date. Thus erasing the old date and not being accurate on when the earliest date actually is. What would be the proper table structure here?

If my question needs to be reworded, please let me know and I will change it.

Typically the way you would do this is using another reference table to hold the product ID with a date reserved.  Something like this:

ProductID    DateReserved
1            2013-03-15
1            2013-02-15
2            2013-03-02

Then you could join this table to the table you currently have to get the x most recent.  Your SQL could look something like this:

Select top 3 * from YourTable
Inner Join ReferenceTable on YourTable.ID = ReferenceTable.ProductID
Order by DateReserved DESC


MySQL DB dynamic ranking or "static" separately saved I have a table where I store the points with the connected categories and game-types of each user. There are different ways to get points. However everytime when the points need to get displayed in a ranking I get them in an SELECT statement, where I group and add them up.

I was wondering if it is more efficient to store and update the points of a user in a table everytime the points change. (Since I need the individual points and game-stats aswell I would still store them in the table I mentioned above.) But the SELECT statement would be simpler and less complicated.
However I think the way I am doing this is the favorable one but since I did not find any post about that I wanted to seek opinion of others.

I hope this is understandable.

Thank you.

The dynamic rank would seem more fluid.

As long as you index the rank field, updating the points gives the rank index a workout and gets the rank dynamically sorted in a logical manner at the same time.

The execution of the query  would be

query parsing
query optimization
rank index scan (if rank is indexed)
limit on result set determined by value of dynamic variable

Updating points and then running a single select would execute the query like this

query parsing
query optimization
points index scan (if points is indexed)
limit on result set determined by the compiled query

Sorting ranks vs sorting points could be just a matter of preference.

However, there is something to consider. If you rank by top 10,000 or 20,000, rank should be a SMALLINT. If the points is also SMALLINT (< 65536 or 64K), then either index/query scenario is fine. However, if points can go beyond 64K and need INT, then go with indexing the rank and dynamically assign the rank.

I would also store rank and point together in the same table to avoid the need to do JOINs. It already has a full plate doing sorts via the index on the points/rank.


Script Table Definition in Powershell I'm writing a powershell to create a duplicate table(in another schema) for the purposes of partition switching*.  I'm having problems adding the columns and the clustered column store index.

Here's what I have so far($schemaName is the destination schema).

<snip for parameters getting passed in>

$database = $server.Databases[$dbName]

$table = new-object Microsoft.SqlServer.Management.Smo.Table ($database, $tblName)

$table.Schema = $schemaName

# Next two lines are not working
foreach ($col in $tblname.columns) { $table.columns.Add($col) }
# $table.Columns.CopyTo($tblName.Columns, 0)

## NEED TO ADD CLUSTERED COLUMN STORE INDEX TO TABLE

Write-Output $table.Columns.Count # Getting zero column count
# This isn't working
# $table.Create()

Any ideas on how to add the columns/clustered column store index?  Or a better approach?

= PartitionManager, over on codeplex, doesn't support SQL Server 2014(clustered column store indexes), so I have to roll my own code.

edit title

Normally I would do somehthing like this using T-SQL, even if Powershell was running that T-SQL, eg

SELECT *
INTO dbo.copyTable
FROM dbo.originalTable
WHERE 1 = 2
GO

CREATE CLUSTERED COLUMNSTORE INDEX _CS ON dbo.copyTable
GO

However I did get the following Powershell to work as a simple example:

$instance = ".\sql2014"
$server = New-Object "Microsoft.SqlServer.Management.Smo.Server" $instance
$database = $server.Databases[$dbName]

$tblName = "cs_test"
$originalTable = $database.Tables[$tblName]

$newTable = New-Object ("Microsoft.SqlServer.Management.Smo.Table") ($database, "copyTable", "dbo")

# Loop through columns adding each one to new table
foreach ($col in $originalTable.Columns)
{
    $newCol = New-Object ("Microsoft.SqlServer.Management.Smo.Column") ($newTable, $col.Name, $col.DataType)
    $newCol.Nullable = $col.Nullable
    $newTable.Columns.Add($newCol)
}

# Add columnstore index
$idx = New-Object ("Microsoft.SqlServer.Management.Smo.Index")($newTable, "idx") ##,IndexType.ClusteredColumnStoreIndex)
$idx.IndexType = "ClusteredColumnStoreIndex"
$newTable.Indexes.Add($idx)

# Create the table
$newTable.Create()


Error trying to update stock with trigger I have bascially 2 tables

CREATE TABLE PRODUCTS (
    id_product BIGINT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(6) NOT NULL UNIQUE,
    name VARCHAR(30) NOT NULL,
    stock INT(4) UNSIGNED NOT NULL
);

CREATE TABLE PRODUCT_SALES (
    id_product_sale BIGINT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    QUANTITY INT(4) UNSIGNED NOT NULL,
    product_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (product_id) REFERENCES PRODUCTS(id_product)
);

And a trigger

CREATE TRIGGER updateStock
AFTER INSERT
ON PRODUCT_SALES
FOR EACH ROW
    UPDATE PRODUCTS
    SET PRODUCTS.stock = PRODUCTS.stock - PRODUCT_SALES.quantity
    WHERE PRODUCTS.id_product = PRODUCT_SALES.product_id

I want to update the stock of X product when someone makes an instert in product_sales table, but i get the following error when inserting a row in my product_sales table

  Unknown column 'PRODUCT_SALES.product_id' in 'where clause'

I'm useing MariaDB Ver 15.1 Distrib 10.2.12-MariaDB, for Win64 (AMD64).

I've made a DBFiddle.

Thanks in advance, best regards.

You cannot use the reference PRODUCT_SALES. for columns inside the trigger.

In the code, once you get passed FOR EACH ROW you must reference the new value with NEW.

CREATE TRIGGER updateStock
AFTER INSERT
ON PRODUCT_SALES
FOR EACH ROW
    UPDATE PRODUCTS
    SET PRODUCTS.stock = PRODUCTS.stock - NEW.quantity
    WHERE PRODUCTS.id_product = NEW.product_id;


Best practise how to sync SQL Server 2014 database with other database What is best practise if we have application using a SQL Server database and we would like to have backup if SQL Server does not work?

Our scenario:

We would like to use our main SQL Server database
If main SQL Server does not work, we would like to use a different SQL Server database with the same data.
If the main SQL Server works again, we need to sync data back to the main database
There is not possible generate same data, because every record use UID.
We can use SQL Server 2014 or SQL Server 2012.

Failover Cluster is option, but are they any other options?

May there will be help from some web pages where are those situations described and recommend what is best in each situation.

It sounds like what you are looking for is an Always On Availability Group in Synchronous Commit mode with automatic failover.  The transactions sync from the primary to the secondary and in the event of a system failure, will pass ownership of the database to the secondary. Your application would need to be aware of both the primary and secondary and which one is active.  This feature does make use of the cluster service but you don't have to have an actual cluster with shared storage (see this post from Brent Ozar for more details about hot to work around that). This is the more expensive option, since it requires Windows Enterprise.

You can implement mirroring in high safety mode which does essentially the same thing on any edition of windows, even a desktop if you want, since it doesn't require clustering features. You won't get all the extra benefits, but if you just want to have a synchronous hot standby of your database then it will do that. The caveat here is that mirroring is deprecated, so if you implement this now, you will eventually have to migrate to availability groups in the future.


AWS DMS connect to on premise SQL Server DB I am working through the process of migrating a SQL Server 2014 DB to AWS Redshift. I have converted the schema and provisioned a PostgreSQL Redshift instance. Now in AWS-DMS->Create Migration->Database endpoints->Connect source and target database endpoints I am not clear on the following:

Server name: ???

What do I need to specify for AWS to see this? My SQL Servername is 'SERVERNAME'. But I presume AWS would need an IP address of my machine as well? Also - I have not opened the Server up for internet access - it is a local install behind a firewall. How should I go about allowing AWS to access it?

Then for the target database connection details:

Server name: ???

Is this the full endpoint or just the server name without the ...amazon.com extension?

The network address of a server instance (its server network address or Endpoint URL) contains the port number of its endpoint, as well as the system and domain name of its host computer. The port number uniquely identifies a specific server instances.

You can try for example below format as we use for Database Mirroring :

TCP://srv5.corp.abc.com:5022

For your reference:

Configure SQL Server Database Mirroring Using SSMS
The Database Mirroring Endpoint (SQL Server)


Unable to connect to an Oracle 12c Docker container I'm working on a project with Oracle, and using a Oracle XE 11 container for testing.  But recent developments have ended up with me running up against a brick wall: I need larger table names.  Support for large table names was apparently added in version 12, so I'm trying to change out the container I'm using.
I'm setting up the container in Java using testcontainers:
db = new OracleContainer("epiclabs/docker-oracle-xe-11g");

When I look around on Docker Hub for Oracle 12 containers, I get a bunch of results, but every one I've tried so far, if I substitute its name in as the image name in the code above, it spins for 10-20 minutes, then errors out with

ORA-12514, TNS:Listener does not currently know of service requested in connect descriptor

And I have no idea what the problem is here.  I have plenty of SQL experience, but next to none with Oracle specifically, and trying to get this Oracle-related project working has been a bit of a disaster so far.  Does anyone know what's going wrong and how I can get connected to a Oracle 12 Docker container?

"Semi-official" images available on hub.docker.com
docker image ls
REPOSITORY         TAG           IMAGE ID       CREATED       SIZE
gvenzl/oracle-xe   18            e1d4dae1771b   4 days ago    3.69GB
gvenzl/oracle-xe   18.4.0        e1d4dae1771b   4 days ago    3.69GB
gvenzl/oracle-xe   latest        e1d4dae1771b   4 days ago    3.69GB
gvenzl/oracle-xe   18-full       13085168429b   12 days ago   6.36GB
gvenzl/oracle-xe   18.4.0-full   13085168429b   12 days ago   6.36GB
gvenzl/oracle-xe   full          13085168429b   12 days ago   6.36GB

docker-compose.yml for persistent database.
version: '3.8'
services:
  database:
    image: gvenzl/oracle-xe:18
    container_name: oracle
    environment:
      - ORACLE_PASSWORD=Welcome_1
    volumes:
      - type: volume
        source: oradata
        target: /opt/oracle/oradata
    ports:
      - 1521:1521
    networks:
      - dbnet
    healthcheck:
      test: sql -L -S sys/Welcome_1@localhost:1521/XEPDB1 as sysdba < /dev/null |grep 'ORA-'; if [[ $$? == 1 ]]; then echo 0; else echo 1; fi
      interval: 1m
      timeout: 10s
      retries: 20
      start_period: 40s
networks:
  dbnet:
volumes:
   oradata:
     driver: local
     driver_opts:
       type: none
       device: $PWD/oradata
       o: bind

Note, make sure "oradata" is rw by oracle user inside container.
Best of luck!


How define select, if bridge pair may have 3 players? Normaly a pair has 2 players, but if there is an odd number of players at the club, one 'pair' has 3 players.

Table, members:

pair, player_id

1 1
1 2
2 3
2 4
3 5
3 6
4 7
4 8
5 9
5 10
6 11
6 12
6 13

table players:

id name

1 Smith
2 Brown
3 Johnson
4 Wild
5 Hammer
6 Bolt
7 Pink
8 Bush
9 Novak
10 King
11 Holmes
12 Watson
13 Gold

Required result of select is:

Pair, Names
1, Smith - Brown
2, Johnson - Wild
3, Hammer - Bolt
4, Pink - Bush
5, Novak - King
6, Holmes - Watson - Gold

This is the MySQL solution (very similar to Leigh's Oracle solution):

SELECT Pair
 , GROUP_CONCAT(name SEPARATOR ' - ') Names
FROM Members m
JOIN Players p ON m.player_id = p.id
GROUP BY Pair;

Obligatory sqlfiddle which was modified from Leigh's illustration as well.*

* I give credit when I plagiarize!


How to get one row out of many rows with same column id I have a use case in which I have three tables.

Courses Table

Course_ Id   Name
 1   MathMatics
 2   Physics

Teacher Table

 Teacher_ID  Teacher_Name
    1    AAA
    2    BBB

TeacherVsCourses Table

 id  Course_id  Teacher_id
 1   1   1
 2   2   1
 3   1   2

My application is programmed using the MVC design pattern of ASP.NET. I am saving individual records into the database table as shown below:

What I want in my view is instead of two rows for Teacher-id=1, I need 1 row with comma separated courses.

How can I achieve this?

You can return a comma separated list of values by using the string_agg function.

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15


What are the available options to backup DB2 Express-C? I want to use DB2 Express-C edition with my application. I have never used DB2 but now want to use the Express Edition in a production environment to learn more about it.

I want to know what are the various backup options available in the Express Edition. In case of a sudden Windows DB2 service failure, how to recover data files?

Is there a way to just copy data files from one PC and attach on another instance of DB2 running on another PC?

Well, DB2 has a backup/restore system, and this allows to make full, incremental and delta backups. For more information about this, you can take a look at: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0005945.html

However, if you want to do a clone of the database, by just copying the data files, you could use the split mirror feature. This feature will stop all IO writes to the database, and you just have to copy the tablespaces and database files into other disk and voila.
For more information about spli mirroring: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/t0006422.html
This feature uses the "set write"command: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0009503.html

Other recent features in DB2 includes the possibility to merge backups, restore tables, and more characteristics that are part of the DB2 recovery expert: http://www-01.ibm.com/software/sw-library/en_US/detail/W269469I30015Y81.html


How can I use value of the id column as default value of another non-nullable column when using postgresql-rails? Here's one example where such a requirement comes up:

Suppose we have a table 'sample' in our database which has a column 'col1' and an auto-incremental 'id' column. We want the default value of 'col1' to be taken as the value of the 'id' column. How do I do that?

I am using rails to write the migration script which will create the table in my postgres databse.

You will need to manually create your own sequence instead of letting Postgres/Rails do it for you. Then, because you know the name of the sequence you can use currval() to grab the current value of id as the DEFAULT value for other rows.

In a Rails migration, you can drop down to raw SQL for this:

class CreateSampleTable < ActiveRecord::Migration

  def up

    execute "CREATE SEQUENCE sample_id_seq"

    execute %{
      CREATE TABLE sample (
        id   bigint UNIQUE NOT NULL DEFAULT nextval('sample_id_seq'),
        col1 bigint        NOT NULL DEFAULT currval('sample_id_seq')
        -- Add other columns here
      )
    }

    execute "ALTER SEQUENCE sample_id_seq OWNED BY sample.id"

  end

  def down
    drop_table :sample
  end

end

Now whenever you insert a row into this table, id will auto-increment and col1 will have the same value as id by default.


Oracle: list user-created tables in the SYS schema I need to delete all tables (hundreds) in the SYS schema that someone accidentally created with sqlplus. Looking in dba_tables and dba_objects, I don't see a way to distinguish these tables from pre-existing stock system tables. Any help appreciated.

Most tables in SYS schema are created when database is installed. You probably know approximately when the new tables were created, so query like this should work:

select *
from dba_objects
where object_type='TABLE'
  and owner='SYS'
  and created > <date before new tables were created>;


Below is an example that uses a recursive CTE to create weekly values and a simple INSERT...SELECT to populate your table.  I assumed a few identity IDs for testing's sake in addition to the postdate being the current date.  You don't need a cursor to do this work.  Enjoy!

    --CREATE SCENARIO TEMP TABLE
    IF OBJECT_ID('tempdb..#TempUserPaid','U') IS NOT NULL
        DROP TABLE #TempUserPaid

    CREATE TABLE #TempUserPaid
    (
          aid INT IDENTITY
        , uid INT
        , eid INT
        , name VARCHAR(50)
        , dob DATE
        , paid DECIMAL(19,4)
    )

    INSERT #TempUserPaid
    VALUES
         (100,10,'Mick','1980-03-29',40.00)
        ,(101,11,'Jack','1969-11-21',40.00)
        ,(205,11,'Lynn','1990-07-10',80.00)
        ,(217,10,'Dana','1987-02-15',40.00)

    --CREATE WEEKLYPAYMENTS TABLE IF IT DOESN'T EXIST TO MODEL USER TABLE
    --DROP TABLE WeeklyPayments  --> CLEAR OUT FOR TESTING PURPOSES
    IF OBJECT_ID('WeeklyPayments','U') IS NULL
        CREATE TABLE WeeklyPayments
        (
              id INT IDENTITY
            , uid INT
            , eid INT
            , [week] INT
            , postdate DATE
            , paid DECIMAL(19,4)
        );

    --INSERT YOUR CODE TO GENERATE NUMBER OF WEEKS HERE TO SET @weeksInMonth
    --HARDCODING FOR THIS EXAMPLE
    DECLARE @weeksInMonth SMALLINT;
    SET @weeksInMonth = 4;

    --RECURSIVE CTE TO GENERATE THE NUMBER OF WEEKS
    WITH cteWeeks AS
    (
        SELECT 1 WeekInMonth
        UNION ALL
        SELECT WeekInMonth+1
        FROM cteWeeks
        WHERE WeekInMonth < @weeksInMonth
    )
    INSERT WeeklyPayments
    SELECT t.uid, t.eid, c.WeekInMonth, CONVERT(DATE,GETDATE()), CAST(t.paid/4 AS money)
    FROM #TempUserPaid t, cteWeeks c
    ORDER BY t.uid;

    SELECT * FROM WeeklyPayments;


Backup and restore database - occured system error 5 In my work I made backup of database and I receive file .bak. Now I would like restore this database in my machine. But I get the following error:

  Cannot open backup device 'C:\Program Files...\MSSQL10_50.SQLEXPRESS...\file.bak'.
  Operating
  system error 5(failed to retrieve text for this error. Reason: 15105).
  RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server,
  Error: 3201)

Of course I searched the Internet, for example:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/805661f1-cf23-4230-ae31-d9cc16505359/cant-restore-backup-from-2005-to-2008r2?forum=sqlexpress

But all users have permissions to all folders so I'm really confused. What is the problem?

Sounds like a permissions error is being masked. You have to remember that the user that needs access to the folder where the backup resides (and where you restore the backup, if using WITH MOVE explicitly) is the SQL Server service account, not regular users.

If the folder listed in the error message does not represent the same instance where you're trying to restore, it's unlikely the service has access to the folder. Try moving the .bak file to the current instance's data or log folder, where SQL Server will certainly have adequate permissions.


Server quit without updating PID file when trying to start MySQL server? So I created my .bash_profile, opened it in a text editor and added:
export PATH=$PATH:/usr/local/mysql/bin

and then I saved it. Next, I tried running
sudo /usr/local/mysql/support-files/mysql.server start

was told to enter my password (so I did), and then terminal said
Starting MySQL

and then it kept printing dots until it said:
ERROR! The server quit without updating PID file (/usr/local/mysql/data/Nicks-MacBook-Pro.local.pid).

The error logs look like:
2021-01-25T20:50:49.707062Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.23) starting as process 15092
2021-01-25T20:50:49.710232Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2021-01-25T20:50:49.834599Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-01-25T20:50:49.943719Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-01-25T20:50:50.037810Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2021-01-25T20:50:50.125494Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-01-25T20:50:50.126128Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-01-25T20:50:50.151069Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.23'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server - GPL.
~
~
~
~
~
(END)

The output of ls -l /usr/local/mysql/data is:
total 377992
-rw-rw----    1 _mysql  admin    196608 Jan 27 23:20 #ib_16384_0.dblwr
-rw-rw----    1 _mysql  admin   8585216 Jan 25 15:50 #ib_16384_1.dblwr
drwxrwx---   12 _mysql  admin       384 Jan 27 22:34 #innodb_temp
-rw-rw----    1 _mysql  admin    112320 Jan 28 19:07 Nicks-MacBook-Pro.local.err
-rw-rw----    1 _mysql  admin        56 Jan 25 15:50 auto.cnf
-rw-rw----    1 _mysql  admin       667 Jan 25 15:50 binlog.000001
-rw-rw----    1 _mysql  admin       528 Jan 27 22:54 binlog.000002
-rw-r-----    1 _mysql  admin       156 Jan 27 22:34 binlog.000003
-rw-r-----    1 _mysql  admin        48 Jan 27 22:34 binlog.index
-rw-rw----    1 _mysql  admin      1676 Jan 25 15:50 ca-key.pem
-rw-rw----    1 _mysql  admin      1112 Jan 25 15:50 ca.pem
-rw-rw----    1 _mysql  admin      1112 Jan 25 15:50 client-cert.pem
-rw-rw----    1 _mysql  admin      1676 Jan 25 15:50 client-key.pem
-rw-r-----    1 _mysql  admin      3372 Jan 27 22:54 ib_buffer_pool
-rw-rw----    1 _mysql  admin  50331648 Jan 27 23:20 ib_logfile0
-rw-rw----    1 _mysql  admin  50331648 Jan 25 15:50 ib_logfile1
-rw-rw----    1 _mysql  admin  12582912 Jan 27 22:35 ibdata1
-rw-r-----    1 _mysql  admin  12582912 Jan 27 22:35 ibtmp1
drwxrwx---    8 _mysql  admin       256 Jan 25 15:50 mysql
-rw-rw----    1 _mysql  admin  25165824 Jan 27 22:35 mysql.ibd
-rw-rw----    1 _mysql  admin      2481 Jan 27 22:34 mysqld.local.err
-rw-r-----    1 _mysql  admin         4 Jan 27 22:34 mysqld.local.pid
drwxrwx---  111 _mysql  admin      3552 Jan 25 15:50 performance_schema
-rw-rw----    1 _mysql  admin      1680 Jan 25 15:50 private_key.pem
-rw-rw----    1 _mysql  admin       452 Jan 25 15:50 public_key.pem
-rw-rw----    1 _mysql  admin      1112 Jan 25 15:50 server-cert.pem
-rw-rw----    1 _mysql  admin      1676 Jan 25 15:50 server-key.pem
drwxrwx---    3 _mysql  admin        96 Jan 25 15:50 sys
-rw-rw----    1 _mysql  admin  16777216 Jan 27 23:20 undo_001
-rw-rw----    1 _mysql  admin  16777216 Jan 25 15:50 undo_002

How do I fix this?

I think the problem might be the PID file
There are two I can see
One in the directory
/usr/local/mysql/data/mysqld.local.pid

and one in the error message
/usr/local/mysql/data/Nicks-MacBook-Pro.local.pid

Please delete those two pid files if they still exist.
Then, start mysql and let’s us know what happens.
I have suggested this twice in the past, 4 years ago and 6 years ago
Before doing so, check to make mysqld is not running.
ps -ef | grep -v grep | grep mysqld

If you see mysqld still running, kill the process, then delete the pid files and retry starting mysql.
UPDATE 2021-01-29 15:56 EST
As it turns out, there were two installations of MySQL involved on the Mac as was learned in the Chat Session.
One installation was done as a dmg install; the other was a homebrew installation.
Once it was discovered that homebrew was competing with the dmg for the PID file, it was decided to stop using the dmg version and use the homebrew version via brew services approach.
The two installs were butting heads, having different PID files.


Export Data Pump all data from few schemas except one table structure only? we use to make a copy of schemas from one database and move it to another for testing using this par file ...

DIRECTORY=EXPORT
DUMPFILE=QQS_PROD.DMP
LOGFILE=QQS_PROD.LOG
SCHEMAS=QQSDATA_PROD,QQSREPOSITORY_PROD,QQS_CENTRAL_PROD,QQS_BD_PROD,QQS_QD_PROD
FLASHBACK_TIME=SYSTIMESTAMP
EXCLUDE=TABLE:"IN ('SYS_HISTORY')"

then create the CONFIG_HISTORY with a script on the target database!

is there any way to NOT EXCLUDE=TABLE:"IN ('SYS_HISTORY')" and export the structure only of this table?

can I use something like this:

INCLUDE=TABLE:"IN ('SYS_HISTORY')"
QUERY=SYS_HISTORY:"where CON_ID < 0"

note: there is no CON_ID with value less than 0, it will return nothing when CON_ID < 0!

You probably can try something like,

DIRECTORY=EXPORT
DUMPFILE=QQS_PROD.DMP
LOGFILE=QQS_PROD.LOG
SCHEMAS=QQSDATA_PROD,QQSREPOSITORY_PROD,QQS_CENTRAL_PROD,QQS_BD_PROD,QQS_QD_PROD
FLASHBACK_TIME=SYSTIMESTAMP
query=SYS_HISTORY:"where 1=2"

This way, the metadata about table SYS_HISTORY will be copied but no data.
Note: since you are not prefixing table name (to exclude data from), with schema name, this filter will apply to this table in each schema specified.


Is there any way to connect to one server and utilize a linked server using Windows authentication and NTLM? I currently have SQL Server 2005 (Server A) that has a SSAS 2005 linked server (Server B). The powers that be do not want to enable Kerberos authentication, so I'm stuck with NTLM.

The problem is that when developers connect from their local workstation to Server A and try to run queries against the linked server, Server B, they are not able to. They are only able to if they RDP into Server A and then run queries against the linked server, Server B. My understanding is that this is due to constraints of NTLM.

Is there any way at all I can enable the developers to connect to Server A on their local workstation and be able to run queries against the linked server?

To clarify, the user has access to both the SQL Server instance containing the linked server, as well as the SSAS catalog that the linked server points to.

Without setting up Kerberos then no.  Why do they not want to setup Kerberos?  It's already 99% setup.  All you need to do is create the SPNs and restart the SQL Server instances.  Kerberos is the default AD authentication system.  You are falling back to the less secure authentication systems.  Sounds like the powers that be don't understand the differences.


Has anyone had luck sending dbmail via relay to an external server on an Azure VM (which appears to block SMTP outbound regardless of rules)? Typically the servers we manage we handle a lot of the alerting for the SQL noise via operators/profiles/DBMail which gives us the most raw information about any issues that crop up on the server.

It appears that within Azure VMs, outbound via port 25 is disabled and can not be enabled (as far as my luck has gone, that is). This prevents the typical use of a relay to our mail server to then send mail to our ticketing boards.

Has anyone run into anything similar, and if so, are there any good workarounds that you found? I am sure this question is a bit confusing so please ask for whatever relevant info is needed.

Please read: Enhanced Azure Security for sending Emails – November 2017 Update which outlines the recommendations, options and restrictions for sending emails in Azure.

In particular:

  For Pay-As-You-Go or Microsoft Partner Network subscriptions created
  after November 15, 2017, there will be technical restrictions blocking
  e-mail sent directly from VMs in these subscriptions.  Customers that
  need the ability to send e-mail from Azure VMs directly to external
  e-mail providers (not using an authenticated SMTP relay) can make a
  request to remove the restriction.  Requests will be reviewed and
  approved at Microsoft’s discretion and will be only granted after
  additional anti-fraud checks are performed.  To make a request, open a
  support case with the issue type Technical --> Virtual Network -->
  Connectivity --> Cannot send e-mail (SMTP/Port 25).  Be sure to add
  details about why your deployment needs to send mail directly to mail
  providers instead of going through an authenticated relay.


Is table aliasing a bad practice? I remember learning to do this in a DBMS course for Master of Information Services students. To save yourself some typing, you can type:

SELECT t1.id, t2.stuff
FROM
              someTable    t1
   INNER JOIN otherTable   t2
      ON t1.id=t2.id
;

But... Why is this acceptable in stored procedures and such? It seems like all it does is harm the readability of the statement while saving an extremely minor amount of time. Is there any functional or logical reason to do this? It seems to add ambiguity rather than remove it; the only acceptable reason I can see for using this format is if you were adding a semantically meaningful alias -- for example, FROM someTable idsTable -- when the table name isn't descriptive enough.

Is table aliasing a bad practice or is this just a misuse of a helpful system?

Table aliasing is a common and helpful practice.

It saves you keystrokes when referencing columns anywhere in your query.
It improves the readability of your SQL when you are referencing many tables. Aliases let you give those tables a short name plus a little meaning to how they are being used.
It is even required when you join a table to itself or when you join to the same table multiple times. This is so the query optimizer knows which table you are referencing when you mention a column.

The following reporting extract illustrates all of the above points nicely:

INSERT INTO reporting.txns_extract
SELECT
    -- 30+ columns snipped
    --
    -- Would you want to type out full table names for each
    -- column here?
FROM
    -- ... and in the JOIN conditions here?
                billing.financial_transactions  ft_cdi   -- alias required here
    INNER JOIN
                billing.cash_application_links  cal
            ON  ft_cdi.key_num = cal.applied_ft_key_num
    INNER JOIN
                billing.financial_transactions  ft_pmt   -- alias required here
            ON  cal.owner_key_num = ft_pmt.key_num
    LEFT OUTER JOIN
                billing.invoice_lines           invl
            ON  ft_cdi.key_num = invl.invoice_key_num
    LEFT OUTER JOIN
                billing.charges                 chrg
            ON  invl.creator_key_num = chrg.key_num
    LEFT OUTER JOIN
                billing.customer_services       cs
            ON  chrg.cs_key_num = cs.key_num
    INNER JOIN
                billing.billers                 bil
            ON  ft_cdi.biller_account_key_num = bil.biller_account_key_num
    INNER JOIN
                billing.formal_entities         fe
            ON  bil.frml_key_num = fe.key_num
WHERE
    -- ... and in the WHERE conditions here?
        ft_cdi.transaction_type <> 'Payment'   -- alias tells me this table is not for payments
    AND ft_cdi.status = 'Approved'
    AND ft_pmt.transaction_type =  'Payment'   -- alias tells me this table is for payments
    AND ft_pmt.status = 'Approved'
    AND ft_cdi.last_user_date >   ft_last_user_date_begin
    AND ft_cdi.last_user_date <=  ft_last_user_date_end
;


Assign same random uuid to every row This obviously results:

SELECT *, uuid_generate_v4() FROM fruits;

apple      |f6b9a8a5-31c9-4917-9649-f366b7c12492
orange     |a55da08f-95a0-4f33-b536-866a3eae71e9

I know I could do

SELECT * FROM fruits LEFT OUTER JOIN uuid_generate_v4() ON TRUE;

But is there some nicer way to assign same uuid?
I'm mostly interested on conceptual level, the uuid() function is just example.

So you want to make sure that uuid_generate_v4() is only called once.

Your solution is essentially doing that. Other solutions only vary in the syntax being used.

You could use a common table expression to somehow express that in your query:

with data (id) as (
   select uuid_generate_v4()
)
SELECT *
FROM fruits
  cross join data;

As the CTE only returns a single row, the cross join won't increase the number of rows in the complete result. Alternatively you could use an "implicit" cross join to make it shorter:

with data (id) as (
   select uuid_generate_v4()
)
SELECT *
FROM fruits, data;

Or you could remove the CTE and use the function call in a derived table:

SELECT *
FROM fruits, (select uuid_generate_v4() as id);


Logging Postgres startup errors on Windows I have been experimenting with Postgres configuration parameters and noticed that when I use settings that prevent Postgres from starting nothing shows up in the pg_log directory logs. I have

log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'

Is there a way to cause Postgres to log startup errors to pg_log on Windows 7 and Postgres 9.3?

There's a chicken-and-egg problem there.

PostgreSQL reads postgresql.conf to decide where to log, and how.

So it cannot really log errors in postgresql.conf to the PostgreSQL logs, unless it uses some kind of fallback/default log.

Instead, it uses the Windows Event Log, which is what Windows applications are supposed to do anyway. (You can have PostgreSQL write all logs to the Windows Event Log if you prefer, by changing its logging configuration).

So you should look in the windows event log - see the Event Viewer - for startup issues with PostgreSQL.


Diferent result between query and application (MySQL) I have this scenario:

I have this query:

SELECT min(id)
  FROM ( SELECT c.id
              , id_Cliente
           FROM creditos AS c
          INNER JOIN  creditos_ren AS r
                  ON r.id = (SELECT rr.id
                               FROM creditos_ren AS rr
                              WHERE rr.id_credito= c.id
                                AND rr.cancelado=false
                                AND rr.fecha_vto>='2018-07-15'
                                AND rr.fecha_vto<'2018-07-24'
                              ORDER BY rr.fecha_vto
                              LIMIT 1
                            )
          LEFT JOIN  creditos_ren AS rx
                 ON rx.id =(SELECT xx.id
                              FROM creditos_ren AS xx
                             WHERE xx.id_credito=c.id
                               AND xx.id != r.id
                               AND xx.fecha_vto >= r.fecha_vto
                               AND xx.cancelado=false
                             ORDER BY xx.fecha_vto
                             LIMIT 1
                           )
          WHERE c.id NOT IN ( SELECT d.id_credito
                                FROM campanas_instancia_lineas AS d
                            )
            AND c.gestion_local = 0
            AND c.enumerador IN ('CR_NORMAL','CR_ENTREGADO','CR_NUEVO','CR_MOROSO')
          ORDER BY r.fecha_vto
                 , c.total_vencido desc
                 , rx.fecha_vto
        ) AS p
 GROUP BY id_Cliente;

When run the query in the WorkBench, the result for example are 260 matches.

But, when I run the same query in the application the result are 497 matches.

I use javaEE with nativeQuery.

I try to use VIEWs, with the same result

If someone help me, please.

Thanks in advance

Make sure both approaches are looking the same DATABASE.

Further debugging:

Turn on the general log.
Run the query both ways.
Turn off the general log.
Compare the two queries; there must be something different.
Figure out what is changing what.

Meanwhile, these indexes may help performance:

creditos_ren:  INDEX(id_credito, canceladom fecha_vto, id)
creditos:      INDEX(gestion_local, enumerador)

What table is id_Cliente in?


How to make Database to support Multiple Languages How to make the database to support national language characters if there are any special characters in languages other than English it is showing as ?.

For example :AQUAFRESH Мой первый зубик is showing as AQUAFRESH ??? ?????? ????? in Database.

Database is not supporting Language other than English.

The data is showing correct in the application (front end) but incorrect in database.  Can you please suggest what database settings needs to be changed to get the correct characters in the database.

What do you mean by "data is showing correct in the application (front end) but incorrect in database"? The database doesn't show anything, it stores the data and hands it back to you (your app) when commanded. If you used the right types, you're good to go as far as the data's integrity is concerned. The database's language doesn't affect storage but it will affect display and some tasks (e.g. sort).

Applications need to be able to handle the types, languages and fonts. For instance, if you use a version of TOAD that does that not support Cyrillic characters, it won't render the data correctly even though it is stored correctly in your database. That's why you see the ???????? when you retrieve non-ANSI character data.

Older version of TOAD did not support all Unicode characters and fonts by default (they did EASCII/ANSI); you needed a Unicode version of TOAD that had the right font support and/or the right NLS_LANG setting. Check your version of TOAD against their specs to see if you have both the language support and font support.

It used to be a really weird dance getting the settings right between the OS, Oracle, TOAD, SQL*PLUS and whatever else you have running. That's largely gone with recent versions of the product stack. I think TOAD's been Unicode enabled since Toad 10 but please verify. If you are not or cannot get on the latest version, check out TOAD's documentation or the Oracle community in Toadworld for workarounds.


POSTGRESQL error Could not create shared memory segment: Cannot allocate memory Recently my client database has restarted and thrown the below error.

FATAL:  could not create shared memory segment: Cannot allocate memory
DETAIL:  Failed system call was shmget(key=5433001, size=5616156672, 03600).
HINT:  This error usually means that PostgreSQL's request for a shared memory segment
       exceeded available memory or swap space, or exceeded your kernel's SHMALL
       parameter. You can either reduce the request size or reconfigure the kernel with
       larger SHMALL. To reduce the request size (currently 5616156672 bytes), reduce
       PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or
       max_connections.
The PostgreSQL documentation contains more information about shared memory configuration.
LOG:  database system was interrupted; last known up at 2017-06-07 08:10:13 BST
FATAL:  the database system is starting up
FATAL:  the database system is starting up

How can I resolve this issue?

The HINT seems pretty clear,

  This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space, or exceeded your kernel's SHMALL parameter. You can either reduce the request size or reconfigure the kernel with larger SHMALL. To reduce the request size (currently 5616156672 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.

Redhat has really good docs on SHMALL. The sysctl method is the new method.

sysctl -w kernel.shmall=5616156672

See man sysctl. These changes do not persist through reboot. Pay attention to the bottom, to make the change permanent,

echo "kernel.shmall=5616156672" >> /etc/sysctl.conf

Also note the top which is great generic advice This parameter sets the total amount of shared memory pages that can be used system wide. Hence, SHMALL should always be at least ceil(shmmax/PAGE_SIZE).


SQL Server ERRORLOG way too big Im running SQL server 2014 on a VPS with 150Gb ssd. Today i found that my hard disk was full 100% and there's a very large file from SQL Server Error log that was 45.7Gb.
I tried manually delete the file, but Windows says the disk space doesnt change at all.
How can I delete this file ? I dont need error log data, it's fine for me to delete it and regain the disk space.
Thank you very much for your support.

You can limit the number and size of SQL Server error log files to avoid excessive space. This can be done from SSMS Object Explorer by right-clicking on the SQL Server Logs folder and selecting Configure. Alternatively, use a T-SQL script like the below example that limits the number of error logs to 10 files of up to 100MB each for your default instance:
EXEC master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'ErrorLogSizeInKb'
    , REG_DWORD
    , 100;
EXEC master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'NumErrorLogs'
    , REG_DWORD
    , 10;

Examine the log to identify and remediate the root cause for the excessive size. The reason could be repeated login failures due to an attack, misconfigured application, repeated stack dumps, etc.
To view the current log, double-click on it from SSMS Object Explorer under Management-->SQL Server Logs or with the query below. In either case, you may need to stop the query due to the very large log size but the sampled records should be enough to identify the cause.
EXEC xp_readerrorlog 0, 1;


Why database file logs are huge? We have an industrial program. For a device that is running 24 hours a day. The database is constantly used.
What I noticed today after 4 months: The database log file size was 80 Gig !

Query execution speeds were slower. Shrink did not reduce the volume of logs. With my search I set the Recovery Model to Simple, and then I did the Shrink . Logs volume reduced to 1 MB !

Recovery model set to Full.

But why did this happen? And how can I stop it?

If you need more info tell me to add it

Here's a simple analogy as to why transaction logs vs simple backups are vastly different sizes.

Imagine you have the number 10. Each day for the next week you add 1 to it. This gives you the number 17 after a week.

If you're recording every single transaction in a complex logging scenario you're essentially storing:

10+1+1+1+1+1+1+1

Although in this case each +1 is actually dozens of lines of code!

Wouldn't it be easier just to store the result (17). Now obviously this is less flexible and won't allow you to roll-back cleverly if things go wrong - but that's the fundamental trade-off here!


SQL Server 2012 Standard server with 50 instances won't uninstall instances I suspect this is an issue because we hit the 50 instance max limit of SQL Server. At the limit it won't install more instances, naturally. However, it seems it won't UNinstall them either. That's worse.

It hangs at the "Select Features" dialogue when you hit next after selecting the SQL Engine for the instance.

I didn't find a lot of info about this. The most interesting hit was this:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/24a1e5f3-25f3-48c8-973a-4c6a18578e42/trying-to-uninstall-1-of-50-sql-server-2008-r2-express-hangs-on-please-wait

It also suggest using setup.exe with some parameters to uninstall the instance, but I can't get it to do so without starting the GUI and into the normal uninstall which fails.

Elevation does nothing. Reboot doesn't help. Logs only say "canceled by user", because I have to kill it in the end. Nothing obvious prior to that point. Validations are all green.

The setup says it is SQL Server 2012 SP1 - all instances are at 11.0.2100.

I' choosing to to answer/close my own question since Microsoft has closed the ticket with the status of "Won't fix."

Hopefully they will have fixed this for SQL Server 2014, but I have not and do not plan to test that myself, nor do they provide any additional comment on closing the ticket.

It will likely be because the setup GUI is trying to enumerate all the instances installed  and failing miserably, running the uninstall from the command line should do it

Running the below command with admin privileges will uninstall the SQL server instance with no setup GUI displayed:

Setup.exe /Action=Uninstall /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /Q

I assume that you only have the SQL Server feature installed, if you have others you want to remove, you can just add them in a comma separated list of values, such as /FEATURES=SQL,RS,Tools

For different instances, just replace MSSQLSERVER with the name of the instance you want to remove.

Command line reference for setup.exe is available here:

Install SQL Server 2012 from the Command Prompt


Need a query to show user present/absent within given interval I am working on a project where I need to show whether users were logged in or not over a given interval (currently within a 24-hour period over the course of a month, but could change).

I have a table with, among other things, the user's login and a timestamp (in milliseconds)

CREATE TABLE tableA (user_login varchar(64), tstamp double, ...)

(NOTE: I don't want to store unix time seconds or date in sql - as I lose precision of 4 places - here in this case it is not relevant but project is big. I expect to need this information, with full precision, elsewhere. I don't need to worry if I store unix time - seconds as date/timestamp divide by 1000 or multiply etc.)

From this, I need create a "map" to see if each user has logged in daily ( so the step will be one day in milliseconds ).  The start and end will vary by days in month. In addition to a simple present/absent Boolean value, I'd like to produce an aggregated daily login count as an additional value.

Now, I could query the table, select back all the data, and put this together easily enough in my preferred coding language. However, we'll have thousands of rows per user; transporting all this over the network is a waste of resources for the client. So, I decided it made more sense to do this at the database server end.

So: Given the following data:

base data ( input  table A ):

user_login     |   tstamp                 |  ...
---------------------------------------------------------------
    a          | 1                        |  ...
    a          | 3                        |
    a          | 5                        |
    a          | 6                        |

And the following definition of the intervals we're interested in:

start of analysis period (dstart) = 1
end of analysis period (dend) =  10
interval size = 3

How do I get the following results?

login|dt|presence | count (facultative column with aggregated count)
-----------------
a    |4 |1        | 2
a    |7 |1        | 2
a    |10|0        | 0

where:
 - dt = the end of each interval
 - presence = was there at least one row found for the user within the interval (1 = yes, 0 = no)
 - count = the total number of rows fond for this user in this interval

Additional notes:
 - if a tstamp falls on the boundary between two intervals, it's OK for it to be counted in both intervals.

So, at the heart of things, we want to take a list of users, combine that with a list of ranges, and report how many records each user has in each range (1 or more means "present", 0 means "absent").

First (since you explain that you're trying to track whether users have logged on to the system every day for a month), I should note that there are a number of good ways to do this simply using dates, and a date table (a list of all possible dates in your range), and that matching date to date (note, date, not datetime or timestamp) might be faster than checking intervals. However, from our conversations, I think you need times down to milliseconds for other aspects of your project, and maintaining both is pointless. This approach probably makes more sense than converting the milliseconds to a date to compare to another date.

You have said you want to use three values to define the intervals:

The minimum value we want to look at (@dstart)
The maximum value we want to look at (@dend)
The size of each interval we want to examine (@interval)

Again, coming out of our discussion, your expectation is that these values will always relate as follows:

@datart + (N * @interval) - 1 = @dend

where N is the number of intervals to be checked. However, it's always a good idea to account for mistakes in the input. If the above statement is not true, then we have a partial interval to account for at the end of the analysis period. This can be handled three ways: add another full interval (that goes past @dend); add the partial interval (ending at @dend); or ignore the partial interval. You agreed that ignoring the partial interval made sense.

For our initial test data , we'll use the following:

INSERT INTO tblA (user_login, tstamp) VALUES
       ('a', 1),('a',3),('a',5),('a',6)
      ,('x', 2),('x',6),('x',7),('x',10)
;

SET @dstart = 1;
SET @dend = 10;
SET @interval = 3;

IN fact, this gives us an example of a partial interval. With our sample values, we should be checking three intervals: from 1 through 3; from 4 through 6; and from 7 through 9. Any tstamps set to 10 will be ignored.

My first suggestion is to generate the interval ranges (the start and end values for each interval) with a recursive Common Table Expression, or CTE.

At its base, a CTE lets you define a temporary table for the duration of a single query statement. The expression can be used multiple times within the query, just like a actual table. It has the additional advantage of allowing you to build the "table" by creating an initial data set, and then using the existing set recursively to build the full data set. This is frequently used for assembling hierarchical data (like organization charts), but can also be used to build something like a list of sequential dates, or our intervals.

Here's the CTE to build the interval list:

WITH recursive theRange AS
     (SELECT @dstart as startRange
            ,@dstart + @interval as endRange
        FROM DUAL
      UNION ALL
      SELECT endRange, endRange + @interval
        FROM theRange
       WHERE endRange + @interval <= @dend
     )

We start with our initial interval:

SELECT @dstart as startRange
      ,@dstart + @interval as endRange
  FROM DUAL

Then, using that as our starting point, we add new intervals, starting each new interval with the end value from the previous interval:

SELECT endRange, endRange + @interval
  FROM theRange

The WHERE clause tells us when to stop:

 WHERE endRange + @interval <= @dend

However, this didn't work out for you; it evidently requires a newer version of MariaDB than what you have available.

So, for option two, I went with using a "numbers table". If you're not acquainted with those, it's a table (or, in our case, a view) with one column, holding integer values (in most cases stepping by one, and starting at 0 or 1). This allows a set-based solution to tasks that might otherwise require a loop or a cursor.

I've borrowed the code to do this from the accepted answer to this question. Check that answer (and the article it links to) for more info on numbers tables.

CREATE OR REPLACE VIEW generator_16
AS (SELECT 0 n)   UNION ALL (SELECT 1 n)   UNION ALL (SELECT 2 n)   UNION ALL
   (SELECT 3 n)   UNION ALL (SELECT 4 n)   UNION ALL (SELECT 5 n)   UNION ALL
   (SELECT 6 n)   UNION ALL (SELECT 7 n)   UNION ALL (SELECT 8 n)   UNION ALL
   (SELECT 9 n)   UNION ALL (SELECT 10 n)  UNION ALL (SELECT 11 n)  UNION ALL
   (SELECT 12 n)  UNION ALL (SELECT 13 n)  UNION ALL (SELECT 14 n)  UNION ALL
   (SELECT 15 n);

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

We start by manually creating a view with the numbers from 0 to 15 (which require up to 4 bits to represent). We can then expand quickly by using a neat little trick. If take the bit representation of the numbers 0-15, shift the bits 4 places to the left, and "concatenate" each of these with our original numbers (i.e., a cross join), we get all numbers represented in 8 bits (the "concatenation is actually a bitwise OR). Do thsi once, and you get 0 - 255; do it again with those results, and you get 0 - 65,535; again, and you get 0 - 4,294,967,296; etc. We shouldn't need that many numbers, but I'll include the views in the full code at the end.

So, our first interval will run from @dstart to @dstart + @interval - 1; our second from @dstart + @interval to @dstart +  (2 * interval) - 1. More generally, the start of interval X is @dstart + ((X - 1) * @interval), and the end if @dstart + (X * @interval) - 1). Since our generator starts us at 0 instead of 1, X will be n + 1 in our actual query:

INSERT INTO intervalRange (startRange, endRange)
SELECT @dstart + (n * @interval)
      ,@dstart + ((n + 1) * @interval) - 1
  FROM generator_256
 WHERE @dstart + ((n + 1) * @interval) - 1 <= @dend
;

And, of course, we stop when the next endRange would be bigger than @dend.  I'll include a query to show you the intervals below, as well.

Once we have the intervals, we simply need to match each interval up to the data for each user in our table, and see how many rows have a tstamp value that falls within the current range:

SELECT user_login, dt, CASE WHEN theCount = 0 THEN 0 ELSE 1 END as presence, theCount
  FROM (SELECT user_login, endRange as dt
              ,SUM(CASE WHEN     tblA.tstamp >= r.startRange
                             AND tblA.tstamp <= r.endRange
                     THEN 1
                     ELSE 0
                   END
                  ) as theCount
          FROM tblA CROSS JOIN intervalRange r
         GROUP BY user_login, endRange
       ) z
;

The subquery aliased as z does all the real work here. We match every record in tblA with every interval; then, we group the records togather based on user_login and startRange, and count the records that fall into our range (with the SUMmed CASE statement). Since each interval is compared with all recods for the user_login, we do get a row out even if none of the records fall within the interval; we do need to see cases where the user wasn't present.

The outer part of the query uses the count to set presence (it's 0 if the count is zero, 1 otherwise); we could probably do this all at once, but it would make the query that much harder to understand, and we'd just be duplicating effort.

So, here's the full code. I tested it using the MySQL 5.5 engine at db-fiddle.com (most of the code was tested in pieces using the MariaDB 10.2 RC engine at dbfiddle.uk, but I haven't been able to get that site to respond for the past couple of days).

CREATE TABLE tblA (user_login varchar(128), tstamp bigint, state varchar(128));

INSERT INTO tblA (user_login, tstamp) VALUES
       ('a', 1),('a',3),('a',5),('a',6)
      ,('x', 2),('x',6),('x',7),('x',10)
;

SET @dstart = 1;
SET @dend = 10;
SET @interval = 3;

CREATE OR REPLACE VIEW generator_16
AS (SELECT 0 n)   UNION ALL (SELECT 1 n)   UNION ALL (SELECT 2 n)   UNION ALL
   (SELECT 3 n)   UNION ALL (SELECT 4 n)   UNION ALL (SELECT 5 n)   UNION ALL
   (SELECT 6 n)   UNION ALL (SELECT 7 n)   UNION ALL (SELECT 8 n)   UNION ALL
   (SELECT 9 n)   UNION ALL (SELECT 10 n)  UNION ALL (SELECT 11 n)  UNION ALL
   (SELECT 12 n)  UNION ALL (SELECT 13 n)  UNION ALL (SELECT 14 n)  UNION ALL
   (SELECT 15 n);

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;

CREATE OR REPLACE VIEW generator_4b
AS SELECT ( ( hi.n << 16 ) | lo.n ) AS n
     FROM generator_64k lo, generator_64k hi;

CREATE TABLE intervalRange (startRange bigint, endRange bigint);

INSERT INTO intervalRange (startRange, endRange)
SELECT @dstart + (n * @interval)
      ,@dstart + ((n + 1) * @interval) - 1
  FROM generator_256
 WHERE @dstart + ((n + 1) * @interval) - 1 <= @dend
;

SELECT * FROM intervalRange;

SELECT user_login, dt, CASE WHEN theCount = 0 THEN 0 ELSE 1 END as presence, theCount
  FROM (SELECT user_login, endRange as dt
              ,SUM(CASE WHEN     tblA.tstamp >= r.startRange
                             AND tblA.tstamp <= r.endRange
                     THEN 1
                     ELSE 0
                   END
                  ) as theCount
          FROM tblA CROSS JOIN intervalRange r
         GROUP BY user_login, endRange
       ) z
;

Here's the results using the test data shown:

user_login | dt | presence | theCount
-----------+----+----------+---------
   a          1          1         2
   a          4          1         2
   a          7          0         0
   x          1          1         1
   x          4          1         1
   x          7          1         1

Note that user x only has 3 logins counted; his login with timestamp 10 matches our @dend value, but @dend doesn't match up with the end of an interval, so anything more than '9' is ignored.

I also generated some test data that should be more similar to what would actually be seen, using these values:

INSERT INTO tblA (user_login, tstamp) VALUES
       ('a', 1492834674999)
      ,('a', 1493038075000)
      ,('a', 1493056075000)
      ,('a', 1493088475000)
      ,('a', 1493162275000)
      ,('a', 1493209075000)
      ,('a', 1493218075000)
      ,('a', 1493300875000)
      ,('a', 1493381875000)
      ,('a', 1493390875000)
;

SET @dstart = 1492798675001;
SET @dend = 1493403475000;
SET @interval = 86400000;

Results were:

user_login |       dt      | presence | theCount
-----------+---------------+----------+---------
   a         1492885075000          1         1
   a         1492971475000          0         0
   a         1493057875000          1         2
   a         1493144275000          1         1
   a         1493230675000          1         3
   a         1493317075000          1         1
   a         1493403475000          1         2


When using DAC, receiving the "[…] the maximum number of '1' dedicated administrator connections already exists" message in error log I am about to escalate this to MS, but I thought I would try here first and see if anyone has any ideas as to how to prevent this faux error message...
When connecting to SQL Server (2016 and 2017 latest builds) with a dedicated administrator connection (DAC) via a PowerShell, I get the following error message:

Date      4/2/2019 1:59:13 PM
Log       SQL Server (Current - 4/2/2019 1:59:00 PM)
Source        Logon
Message
Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]

The query does run successfully. Numerous connections string management iterations have been attempted, this one is the most robust so far.
A solution exists on Stack Exchange that involves killing the spid before closing the connection, but that also throws a nuisance message into the SQL Server error log, so no joy there.
Examining sys.dm_exec_sessions reveals nothing of interest, no connections remain open using this technique.  The below PowerShell has a dummy query in it, I can't talk about why we are connecting this way because it is proprietary, but it is 100% necessary, it is a very quick connection, and I need to do it once every 10 minutes.
This error is a total nuisance/noise. DAC query runs and works as expected. Error fires every time even with a fresh restart on a quiesced system.  There are NO other DACs - if there is it would throw a very heinous error message at the command prompt.
Interestingly, when using sqlcmd for this, it does not throw the error message.
#begin powershell script
$SqlServerName = "server\instance"

$DbQuery = "
INSERT INTO  master.dbo.sometable(value1,value2) values ('test14','testtest14');"

function Get-SqlConnection
{
    param (
        [String] $SqlServerName
    )
    $sqlConnection = $null

    try
    {
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $sqlConnection.ConnectionString = "data source=admin:$SqlServerName;Integrated Security=True; pooling=false"
        $sqlConnection.Open()
    }
    catch
    {
        if ($sqlConnection)
        {
            [void] $sqlConnection.Dispose()
        }
        throw
    }
    $sqlConnection
}

try
{
    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
    $sqlConnection = Get-SqlConnection -SqlServerName $SqlServerName
    $sqlCommand.Connection = $SqlConnection
    $sqlCommand.CommandText = $dbQuery
    [void] $sqlCommand.ExecuteNonQuery()
}
finally
{
    if ($sqlCommand)
    {
        [void] $sqlCommand.Dispose()
    }

    if ($sqlConnection)
    {
        [void] $sqlConnection.Dispose()
    }
}

After working up through several layers of Microsoft support, it was discovered that the solution to this issue was to change " pooling=false" to "Pooling=False" It is case sensitive.


Store table in separate filegroup in SQL Server I have big SQL Server database, with a lot of blob data in some tables.

For performance optimization, I want to move one table to separate hard drive.

I created new filegroup, and "row data" file.

But, I can't change any parameters in table properties: they are all disabled.

And I don't know a way to enable it.

Can anybody help me?

Create / recreate the CLUSTERED INDEX. It's leaves are the data.

http://gallery.technet.microsoft.com/scriptcenter/c1da9334-2885-468c-a374-775da60f256f

Shows a script for this.

http://www.mssqltips.com/sqlservertip/2442/move-data-between-sql-server-database-filegroups/

has a step by step guide with Pictures. And more usefull scripts all around filegroups.

Both Show up when you ask "SQL move table filegroup" in Google.


Both Primary and Foreign Key Needed on Table? Someone recently asked this question:

  There are two tables: Deal and DealCategories. One deal can have many
  deal categories.

  So the proper way should be to make a table called DealCategories with
  the following structure:

 DealCategoryId (PK)
 DealId (FK)
 DealCategoryId (FK)

  cont ...

Can someone explain why DealCategories table has

DealCategoryId (FK)

on it?

That part did not make sense to me either. The OP (of that question) states:

"One deal can have many deal categories."

I assume that there can also be many deals in a deal category. So, the "proper" way to would be a DealCategory table:

DealCategory
--------------
DealCategoryId (PK)

and a DealInCategory many-to-many table, with:

DealInCategory
--------------
DealId (FK to Deal)
DealCategoryId (FK to DealCategory)
PK: (DealId, DealCategoryId)


How to track SSIS memory and CPU performance? I may be missing some pieces to this puzzle, but this is what I have:

All of this was written for us by an outside firm.

There is a .Net application that runs as a service on Server A.

Inside that application SSIS packages are being called. DTEXEC?

Those packages (37 total) reach out to two different SQL Servers (B&C), pull in some data, perform calculations on it, then insert the results into a database on Server C.

Of the 37 packages, only a few of them are my problem area.  I've seen the details, and there are many, many tasks in them.

There are no SQL Server components (database engine or SSIS) on Server A where the .Net app lives, so this is all churning internal to the application. .Net is using 1 NUMA node of 20 cores, 1TB RAM on the box.

This app is 3.0 version, with new functionality added.  2.4 (previous version) works fine on identical hardware.

All of that background to ask this:

Is there any way I can find poorly performing T-SQL inside the SSIS packages that are inside the .Net application?   Or am I missing something hugely obvious in the equation?
What it boils down to is: "where are the calculations/T-SQL actually running" (A vs B/C) and how do I trace/tune them if inside the .Net space?

Profiler, sp_whoisactive, etc. all point to SQL instances. This is all happening solely inside the .Net app, with small exceptions to select data from B and insert to C after calculations.  All network, disk, etc. is performing perfectly.   I'm looking for a tuning tool that can reach into the .Net SSIS if such a thing exists.

It's definitely a tuning issue, but Profiler, sp_whoisactive, etc. show nothing of more than maybe 3 seconds, most well under 1/2 second and less. I don't know enough about .Net to know if those T-SQL tasks are actually running on server A inside that memory space like I think they are, or if there are just so many of them that its all aggregating into "too long".

I wish I was allowed to enable heavy logging in SSIS, but this is part of the vendor code I am not allowed to touch, even for logging :(

Have you looked into RedGate's reflector? It sounds like exactly what you're looking.

Note: I have no affiliation with RedGate. Web searches solve problems!


Indexed view in SQL Server I have a table and an indexed view on it like

Create table mytable1 (ID int identity(1,1), Name nvarchar(100))

Create table mytable2 (ID int identity(1,1), Name nvarchar(100))

Create view myview
with schemabinding
as
   select a.name, b.name
   from mytable1 a
   join mytable2 b on a.Id = b.Id

Now if I run the following query

select a.name, b.name
from mytable1 a
join mytable2 b on a.Id = b.Id

It does not use my indexed view. Is there any hint (or other way) to force SQL Server to use indexed view instead?

I have a big system, and need to optimise it. I can't change all my SQL scripts to select from the view instead of tables. I want to create indexed views and force SQL Server to get data from them instead of tables.

I am using SQL Server 2014 Enterprise Edition.

I build indexed views in SQL Server all the time to tune existing products. The optimizer is smart enough to use the index if you are utilizing the appropriate columns.

Using your example, it looks like you created the view but did not actually create an index upon it.

if object_id(N'mytable1') is not null
drop table mytable1
if object_id(N'mytable2') is not null
drop table mytable2
go

Create table mytable1 (ID int identity(1,1), Name1 nvarchar(100))
GO
Create table mytable2 (ID int identity(1,1), Name2 nvarchar(100))
GO

insert into mytable1 values ('steve')
insert into mytable1 values ('jack')
insert into mytable1 values ('mike')
insert into mytable1 values ('ralph')
insert into mytable1 values ('simon')

insert into mytable2 values ('smith')
insert into mytable2 values ('jackson')
insert into mytable2 values ('mikaelson')
insert into mytable2 values ('montalvo')
insert into mytable2 values ('singer')
go

if object_id(N'myview') is not null
drop view myview
go

Create view myview
with schemabinding
as
select a.id, a.name1, b.name2
from dbo.mytable1 a
join dbo.mytable2 b on a.Id = b.Id
GO

select a.name1, b.name2
from mytable1 a join mytable2 b on a.Id = b.Id
GO

Since there is no index on this view, we scan on the base tables:

But once we add an index, the optimizer can use it:

CREATE UNIQUE CLUSTERED INDEX [ix_cl_names] ON [myview]
(
    [name1] ASC,
    [name2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

This appropriately used the view:

  I can't change all my SQL scripts to select from the view instead of tables. I want to create indexed views and force SQL Server to get data from them instead of tables.

There is no hint or other method to force SQL Server to use an indexed view when it is not referenced in the query.

Additional information (from Geoff Patterson)

One extra point is that while the optimizer can, in Enterprise Edition only, use the indexed view in this case, it may make sense to directly reference the view using the NOEXPAND hint if you need to be 100% sure of the view index being used or if you ever want it to be used in Standard Edition.

I've frequently seen queries even in Enterprise Edition where the optimizer does not pick up on the fact that the view index can be used unless NOEXPAND is used. It's more common with complex queries, but can happen with simple queries also.

Paul White has one of the better articles I've read exploring the nuances of NOEXPAND; beyond just usage of the view index, the hint can also impact things like whether statistics are automatically created on the indexed view and cardinality estimates for the plan.

And from Zane: As a side note, be careful with indexed views as like any other index it will add to your update, insert, and delete times.


Selecting unique rows based on the maximum date I am using PostgreSQL and I am trying to create a view of data:

forecast_datetime, file_date, thing_id, thing_name, facts...

The forecast_datetime column is a datetime for each 30 minute interval. The issue is that the forecast is for 1 month into the future which causes a lot of duplicate data. (so today, there will be a forecast for 2016-12-25 01:30:00 and then tomorrow there will be a forecast for the same interval, over and over again potentially until the period actually happens.).

The view I want to create is to just take a look at the last forecast which was appended to the table. This is indicated by the file_date column (as the name suggests, I receive a daily forecast file which is appended to the database. This column indicates the date from the filename).

I basically want to filter all rows for each thing_id and select the forecast_datetime columns where there file_date was the maximum date for that forecast. So each thing_id should only have one row per unique forecast_datetime interval which is based on the latest file_date.

CREATE OR REPLACE VIEW schema.view AS
SELECT DISTINCT ON (thing_id, forecast_datetime, file_date)
       forecast_datetime,
       interval,
       time_zone,
       file_date,
       thing_id,
       thing_name,
       other_cols...
FROM   schema.orig_table
ORDER  BY  file_date DESC

Would that query do the trick? I can do this in pandas/python easily, but I am newer to SQL and wanted to try a view instead of writing a new physical table.

Here is some sample data after applying the query I wrote above this. Because I included the the file_date in the SELECT DISTINCT query, there is still a repeat row which duplicates each forecast_datetime. This causes issues when I join this data based on the thing_id and forecast_datetime back to the actual results table.

forecast_datetime   file_date   thing_id
2016-12-12 20:30    2016-12-11  99
2016-12-12 20:30    2016-12-10  99
2016-12-12 20:30    2016-12-09  99
2016-12-12 20:30    2016-12-08  99

I would like to only have the row with the max file_date. In the example above, that would be the first row (2016-12-11). The other rows would be dropped from the view.

CREATE OR REPLACE VIEW schema.view AS
SELECT DISTINCT ON (thing_id, forecast_datetime)
       forecast_datetime,
       interval,
       time_zone,
       file_date,
       thing_id,
       thing_name,
       other_cols...
FROM   schema.orig_table
ORDER  BY  thing_id, forecast_datetime ASC, file_date DESC

This appears to have worked perfectly.


AlwaysOn Availability Group listener port selection Simple question (I hope!)

Why would you use a non-default port for an Always On AG listener?

I haven't found a good post (yet) outlining advantages vs disadvantages.

Currently, I can think of three reasons to use something other then 1433:

Following Sql Server security hardening guidelines.
Avoiding port conflicts (as per https://msdn.microsoft.com/en-us/library/hh213417.aspx#SelectListenerPort).
Being able to identify db calls to specific AG's quicker in network traces and such.

Are there any others people can think of?

Following Sql Server security hardening guidelines.

Because sometimes the Windows Firewall, if enabled, and add the exception for this IP/Port.

  Avoiding port conflicts (as per
  https://msdn.microsoft.com/en-us/library/hh213417.aspx#SelectListenerPort).

You can configure the default port to 1433 in order to allow for simplicity of the client connection strings. If using 1433, you do not need to designate a port number in a connection string.

  Being able to identify db calls to specific AG's quicker in network
  traces and such.

Since each availability group listener will have a separate virtual network name, each availability group listener configured on a single WSFC can be configured to reference the same default port of 1433.

  Why would you use a non-default port for an Always On AG listener?

In the “Port” field, choose an adequate port, something that is not being used. By Default sometime port sql server port 1433.

suppose that you are configuring the port ( let say 5122) After configuring the 5122 port in AlwaysOn availability group in listener. you must test through telnet like

“telnet <your listener name> <listener port>”

After checking the test connection of listener port number . if you shall get the error like

connecting to SQLServerName ... could not open connection to the host
,on port 5122 : connect failed

Note: Here SQLServerName is the Name of the Primary Replica Server Name.

In that case, check if you didn’t misspell something (the hostname or the port), if all looks good, you might check the Windows Firewall, if enabled, and add the exception for this IP/Port. Another try is use the listener IP, instead f the computer name. If the connection using the IP works, you need to check why the name resolution is not working. Talk with the Domain/DNS/System Administrators in order to troubleshoot that.

If all looks good on Windows point of view, and if the instance/listener are really only and should be replying, you need to check with the Networking team of your company, on order to have this unlocked. Always pass to them the following information:

Source IP and hostname (from where you are connecting).
Destination IP and hostname.
Destination port.

For your further ref Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server) and Here


What is the right tool to process big .xel files (SQL Server Extended Events logs)? My aim is to audit all queries on an instance, who's running them and from where, how many reads, in order to consolidate/migrate on another instance.

Figured out the best way is via Extended Events to .xel files, over 2 weeks, copy those and analyse on my well-specd desktop. Set up the capture with the minimal number of details (fields), so really can't make it smaller than this - aprox 12GB in 12 files. Used "Merge Extended Event Files" in SSMS to load the .xel files and process as detailed in https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/advanced-viewing-of-target-data-from-extended-events-in-sql-server?view=sql-server-2017

Is this really the best way to do it ? Is there a better tool to load the files ? It's really slow with SSMS 17.7 - example: with only 4 filters so far (before getting into grouping and aggregates) it took over 12h to get to crunch through 50% of the logs.

For large XE trace files, I use custom tooling with QueryableXEventData. I've found this to be much faster than XML parsing in T-SQL.

Below is a basic PowerShell that imports selected fields and actions from an rpc_completed event trace into a table in 10K batches. You'll need to include an Add-Type command for the Microsoft.SqlServer.XE.Core.dll and Microsoft.SqlServer.XEvent.Linq.dll assemblies, which will be in your SQL Server installation folder with the exact location will varying depending on SQL version and chosen install location.

$SharedPath = "C:\Program Files\Microsoft SQL Server\140\Shared";
$SqlInstanceName = "";

$xeCore = [System.IO.Path]::Combine($SharedPath, "Microsoft.SqlServer.XE.Core.dll");
$xeLinq = [System.IO.Path]::Combine($SharedPath, "Microsoft.SqlServer.XEvent.Linq.dll");
Add-Type -Path $xeLinq;

if( [System.IO.File]::Exists($xeCore) )
{
    Add-Type -Path $xeCore;
}

Note that there are separate Fields and Actions collections in the PublishedEvent class so you'll need to extract values from the appropriate connection.

# create target table
$connectionString = "Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$command = New-Object System.Data.SqlClient.SqlCommand(@"
CREATE TABLE dbo.rpc_completed(
      event_name sysname
    , timestamp datetimeoffset
    , statement nvarchar(MAX)
    , username nvarchar(256)
);
"@, $connection)
$connection.Open()
[void]$command.ExecuteNonQuery()
$connection.Close()

# data table for SqlBulkCopy
$dt = New-Object System.Data.DataTable
[void]$dt.Columns.Add("event_name", [System.Type]::GetType("System.String"))
$dt.Columns["event_name"].MaxLength = 256
[void]$dt.Columns.Add("timestamp", [System.Type]::GetType("System.DateTimeOffset"))
[void]$dt.Columns.Add("statement", [System.Type]::GetType("System.String"))
[void]$dt.Columns.Add("username", [System.Type]::GetType("System.String"))
$dt.Columns["username"].MaxLength = 128
$dt.Columns["statement"].MaxLength = -1

$events = new-object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData("D:\TraceFiles\Log\rpc_completed*.xel")

# import XE events from file(s)
$bcp = New-Object System.Data.SqlClient.SqlBulkCopy($connectionString)
$bcp.DestinationTableName = "dbo.rpc_completed"
$eventCount = 0
foreach($event in $events) {
    $eventCount += 1
    $row = $dt.NewRow()
    $dt.Rows.Add($row)
    $row["event_name"] = $event.Name
    $row["timestamp"] = $event.Timestamp
    $row["statement"] = $event.Fields["statement"].Value
    # username is a collected action
    $row["username"] = $event.Actions["username"].Value
    if($eventCount % 10000 -eq 0) {
        $bcp.WriteToServer($dt)
        $dt.Rows.Clear()
    }
}
$bcp.WriteToServer($dt) # write last batch
Write-Host "$eventCount records imported"


Unspecified error (MS Visual Database Tools) when creating a table using GUI I added my user to db_ddladmin role, but initially i had my own custom role with rights to create a table for that user. Either way creating a table using TSQL works, being part of both roles. Problem occurs when i try to create a table using GUI, tables -> new table, i get the Unspecified Error(MS Visual Database Tools)

However if i add that user as a db_owner , it works. I would just like to know if there is any right in particular that i might have missed which specifies whether user can create a table through GUI or not?

Note:
I am using SSMS 17.4 and SQL Server 2016.User is part of a domain created login.

To answer my own question. After a some testing i noticed that the user needs to have Control permission on the database in order to be able to create tables using GUI. That is why db_securityadmin and db_owner worked.

Now i have tried before posted question to create a new login and user, that worked just fine. For some reason this domain user was problematic, so i explicitly granted him that permission. I dont think this happens occasionally, but if anyone encounters this issue that i have, i hope this helps. And some clarification if anyone has would be greatly appreciated.


Any correlation between tempdb and sysmail? Is there any correlation between [tempdb] getting full and subsequently  db_mail disabled ? [This needs sysmail_start_sp to be executed in msdb]

Yes there is correlation between tempdb and db_mail.
Ref:
https://technet.microsoft.com/en-us/library/ms345368(v=sql.105).aspx
Database mail user service broker which in turn use tempdb.

Service Broker helps developers build asynchronous, loosely coupled
applications in which independent components work together to
accomplish a task. These application components exchange messages that
contain the information that is required to complete the task. Service
Broker explicitly uses tempdb for preserving existing dialog context
that cannot stay in memory. The size is approximately 1 KB per dialog.
Also, Service Broker implicitly uses tempdb by the caching of objects
in the context of query execution, such as work tables that are used
for timer events and background delivered conversations.
Database
Mail, Event Notifications, and Query Notifications implicitly use
Service Broker.


Job to remove logins on secondary replica that don't exist in primary replica How to remove logins on a secondary replica that don't exist in primary replica?
I want to create automated jobs that should check if the login still exists on a secondary after the login on the primary dropped. I want to drop this login on the secondary replica.

You can create a linked server to the primary and write an Agent job that compares the logins on both servers.


how to prevent dropping sysadmin user by another user in sql server I have a legacy application installed on my server which has configured to work only with one user "sa" and there is time bomb like script written inside this application which whenever it finds another user it will delete that user without any hassle.

We need to have access to database with another application and password of "sa" is lost so the only way remaining is to create another user with sysadmin privileges.

but since the script deletes all the users I can't do anything special, so my question is :

How can I prevent that script or rollback its job every time it is trying to delete my user?

CREATE TRIGGER [DDL_PreventLoginDrop]ON ALL SERVER
FOR DROP_LOGIN
AS
DECLARE @LoginName NVARCHAR(128),@eventData XML

SET @eventData = EVENTDATA()
SELECT @LoginName=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')
IF @LoginName IN ('Login name that you want to protect')
  BEGIN
    PRINT 'Gotha, you nasty application!'
    ROLLBACK;
  END
GO
ENABLE TRIGGER [DDL_PreventLoginDrop] ON ALL SERVER

disclaimer:
Hopefully you'll agree that this answers your question "How to prevent dropping a login" but in no way is this a proper root cause fix. However, I know that there are some pretty nasty legacy applications where it's not possible to change the code... I must admit, this one just made me smile..


Full backup of Simple recovery model - how much log is backed up if there is an open transaction I have a situation right now.
There is a database in SIMPLE recovery model.

Note:
 1. Compression is not enabled which will be enabled pretty soon.
 2. SQL Server 2012 SP3

BackupSize: 8GB (Usually)<br/>
Last Saturday BackupSize: 73GB (yes, 73 !!)

This is because of an application that left 2 transactions open.
Right now the LOG file is 154GB.

Transaction: So far it inserted 90M records to a not so large table)

Question:

In a FULL backup, how much LOG information is recorded if there is an open transaction?
When the FULL backup is restored, does it ROLLBACK the actions by the OPEN transaction?
If so, why does FULL backup even bother to backup the LOG for an OPEN transaction when it is still open after the backup is finished (on a SIMPLE recovery model though).
If I kill the spid from SQL Server, does it need more LOG space to ROLLBACK? We are already out of space here !!

In a FULL backup, how much LOG information is recorded if there is an open transaction?

Answer to your question is in This Blog by Paul Randal

  When the FULL backup is restored, does it ROLLBACK the actions by the OPEN transaction?

Yep if the transaction has not committed when the full backup completes it will be rolled back when the backup is restored, simply, because SQL Server does not have idea about "state of transaction" when the backup finished before the transaction committed/completed. Please read below for more details

Understanding Backups in SQL Server

  If so, why does FULL backup even bother to backup the LOG for an OPEN transaction when it is still open after the backup is finished (on a SIMPLE recovery model though).

As stated above because it does not have any idea about the state of transaction, like whether it will commit or rollback and since transactions in SQL Server are first written in transaction log before changes are made to pages the backup so it does its job of backing up enough transaction logs so that if transaction commits it can bring database to consistent state after backup is restored and if transaction does not commits before backup is finished the backups takes it as uncommitted and when it will be restored their would be no information about changed made by that transaction in the restored database.

Read The First Myth pointed out by Paul Randal

  If I kill the spid from SQL Server, does it need more LOG space to ROLLBACK? We are already out of space here !!

Yes it does needs space in transaction log file to rollback. Rollback is mostly single threaded and undoes what the transaction has done.

Rollback: What happens when you kill a session


Connect as postgres user on AWS RDS to install extension to pg_catalog I need to connect to our AWS RDS PostgreSQL 9.5 instance so I can add the citext extension to pg_catalog schema so it is available to all schemas (PG prepends pg_catalog to the beginning of the search_path by default).

I'm easily able to do this on my local machine:

> sudo su - postgres
> psql
> \c mydatabase
> CREATE EXTENSION IF NOT EXISTS citext SCHEMA pg_catalog;
> \q
> exit

If I login to the RDS using a direct connection as AWS docs show, I am given permission denied for schema pg_catalog when I try to CREATE EXTENSION:

psql
   --host=myrdsawshostname
   --port=5432
   --username=myusername
   --password
   --dbname=mydatabase

This is the only way I know how to connect and I can only seem to connect as "myusername" not "postgres".

What am I doing wrong here?

Your attempt to write to the pg_catalog schema probably fails because you don't have a superuser on RDS, just a role with limited admin privileges. And Amazon is smart enough to prevent users from messing with the system catalogs. I.e.: no CREATE privilege in the schema pg_catalog.
It's generally not advisable to install additional modules like citext into the pg_catalog system schema. (Some exceptions have the schema preset.) Install it to public or some dedicated schema.

Is it recommended to install extensions into pg_catalog schema?

The "home" schema of the installed extension needs to be in the search_path. The manual:

The schema containing the citext operators must be in the current
search_path (typically public); if it is not, the normal
case-sensitive text operators will be invoked instead.

You can easily set the search_path for one / some / all users or databases or user / database combinations or temporarily or for the current session or generally:

How does the search_path influence identifier resolution and the “current schema”