Well, it was difficult to solve, but with the help of our sysadmin and reading through lot of places, we managed to get it working. It was a mix of things, but the 2 main ones were changing some parameters on SSRS config file:

Change <Add Key="SecureConnectionLevel" Value="0"/> to <Add Key="SecureConnectionLevel" Value="3"/>
Make sure that <RSWindowsNTLM/> is the only authentication type specified
on <AuthenticationTypes>
make sure all url's are absolute, without wildcards

And second part was to modify a windows registry and restart SSRS afterwards:

Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa

Create a new DWORD registry key DisableLoopbackCheck and set value to 1.

In case you are wondering, here are all the references consulted:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2d216af4-6586-4619-b8aa-56f05cbd8239/the-request-failed-with-http-status-401-unauthorized-in-ssrs?forum=sqlreportingservices
https://connect.microsoft.com/SQLServer/feedback/details/323214/the-request-failed-with-http-status-401-unauthorized
https://social.technet.microsoft.com/Forums/en-US/27ecdb2d-d6be-4e10-a7e2-7fedba1266e2/url-problems-with-sql-server-reporting-services-2012-with-wildcard-ssl-certificate?forum=sqlreportingservices
https://www.sqlservercentral.com/Forums/Topic908027-146-1.aspx
http://sqlmag.com/sql-server-reporting-services/implement-kerberos-delegation-ssrs
https://msdn.microsoft.com/en-us/library/cc281382.aspx


Effectively handle 10-100 millions row table of unrelated data

Using a simple BRIN index

TIAS.

Here is a table exactly as you described, worst case 100 million rows with 1 million rows per SEGMENT_ID

explain analyze
CREATE TABLE foo AS
  SELECT (x::int%100)::int AS SEGMENT_ID
  FROM generate_series(1,100e6) AS gs(x);

                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series gs  (cost=0.00..15.00 rows=1000 width=32) (actual time=21740.904..57589.405 rows=100000000 loops=1)
 Planning time: 0.043 ms
 Execution time: 96685.350 ms
(3 rows)

That means we created the table in 1.5 mins. Here we're adding an index.

CREATE INDEX ON foo
  USING brin (SEGMENT_ID);
VACUUM ANALYZE foo;

Then we add another million rows. SEGMENT_ID = 142

explain analyze
INSERT INTO foo(SEGMENT_ID)
  SELECT 142
  FROM generate_series(1,1e6) AS gs(x);

                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Insert on foo  (cost=0.00..10.00 rows=1000 width=0) (actual time=1489.958..1489.958 rows=0 loops=1)
   ->  Function Scan on generate_series gs  (cost=0.00..10.00 rows=1000 width=0) (actual time=174.690..286.331 rows=1000000 loops=1)
 Planning time: 0.043 ms
 Execution time: 1499.529 ms
(4 rows)

Adding a million rows took 1.5 seconds.. Now we select,

explain analyze
SELECT *
  FROM foo
  WHERE SEGMENT_ID=142;

                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=52.00..56.01 rows=1 width=4) (actual time=4.401..140.874 rows=1000000 loops=1)
   Recheck Cond: (segment_id = 142)
   Rows Removed by Index Recheck: 24832
   Heap Blocks: lossy=4535
   ->  Bitmap Index Scan on foo_segment_id_idx  (cost=0.00..52.00 rows=1 width=0) (actual time=1.504..1.504 rows=46080 loops=1)
         Index Cond: (segment_id = 142)
 Planning time: 0.059 ms
 Execution time: 176.902 ms
(8 rows)

Selecting a million rows took 176 ms.

That's on a 5 year old x230 with a "Intel(R) Core(TM) i5-3230M CPU @ 2.60GHz" and a single SSD. You can pick one up for a few hundred bucks and install Xubuntu it. Not exactly hard science either. I'm compiling Angular apps in the background.


calculate or get price of parent items on bill of materials

the price will surely be null if you tell it to. Initialize the base case for the CTE with 0 instead:
with recursive cte_main as
(
    select 'ALFA001'::character varying as id_father
         , 'ALFA001'::character varying as id_son
         , 'ALFA001' as tree
         , 1::double precision as qty
         , 0::double precision as price
         , 0::double precision as subtotal
         , 'ALFA001'::character varying as sort
         , 0 as levell

Second, you need to handle cases where the table contains null. You can use COALESCE for that purpose:
    union all
    select e.id_father
         , e.id_son
         , repeat('......',levell+1) || e.id_son as tree
         , e.qty
         , coalesce(e.price,0)
         , e.qty * coalesce(e.price,0) as subtotal
         , sort || ' a- ' || e.id_son::character varying as sort
         , levell+1
    from public.main as e
    inner join cte_main
        on e.id_father = cte_main.id_son
)
select * from cte_main
order by sort

Since all your attributes may contain null, you need to take that into consideration for the other attributes as well.


mongodump: are 4 shards with total datasize 575 GB consider a Small Sharded Cluster?

It is not a small sharded cluster.

Mongodump will take around 5 hrs for 600GB and restore would take more than 5 hrs based on the indexes in place on your collection.

My best suggestion is:

If you have a backup already in place taken by Mongo Ops-Manager, use that and restore on the new environment.
If time is not really an issue in your case, use the dump and restore method.
Mongodump and restore method can handle huge databases, if you want to restore few collections then use export and import option.

Note: A small hint i can give here to make the process faster is:

Mongodump will take a backup of the db and collection in 2 files for each, one is bson and other is json. Bson will have the indexes and json will have the data.

So, Mongorestore process will restore the json file first and then start restoring the bson file and apply indexes.

To make this process done faster, first make a mongodump and get all the indexes from all dbs and collections and apply the indexes first manually and then restore the data with mongorestore, this process will save atleast 30-40% of your time.


Backing entire Postgres database server up with pg_basebackup

Question: do I really have to copy the WAL to have complete backup from the time of starting pg_basebackup command?

Yes, you do.

The DB needs current WAL to start up from the base backup.

You can use pg_basebackup -X stream on 9.3 and above to copy all the required WAL along with the initial basebackup.


Postgres: What happens if your slave DB went down temporarily?

The answer is yes.  What is actually happening is that the slave receives binary patch files and applies those.  It is slightly complicated but in general your slave can be offline temporarily and then later catch up.  I would imagine that there are limits in the amount of time that can pass but in the event that it falls too far behind you can use WAL segments to catch up (as I understand it, unarchived wal segments are the primary key here, so if you pass the point where you need archived segments to catch up, you will need those segments accessible via log shipping or the like).


Most efficient way of updating table from another - Postgres

The fastest way will probably be to create a new table based on a join of the existing tables, create indexes and foreign keys constraints, and then drop the old "interface_events" and rename the new table into place.  Views and foreign keys into this table can complicate this.

create interface_events_temp as
    select interface_events.*, device.interface_id from interface_events
        join device on (device.id = interface_events.device_id)

But the interface_events.* notation relies on interface_id not already existing in interface_events.  Since it already exists, you would have to list out all columns in interface_events except for interface_id.


I do not find any system table show the  last login  time of roles。 if you want to see the
last login time of a role and the executed command of a user ,I suggest you set log_connections
parameter in postgresql.conf ,and you can see those information in your database log files.

In addition, you can use "\s" meta command see history command ,as the following:

[pg90@redhatB ~]$ psql
psql (9.0.9)
Type "help" for help.

postgres=# \s /home/pg90/script/tf/history.sql
Wrote history to file ".//home/pg90/script/tf/history.sql".

But only can see the history of one client.

To see other roles's connections information ,you can see pg_stat_activity for more informations。

postgres=# select usename,datname,count(*) from pg_stat_activity group by usename,datname order by 3 desc;

After that ,  you can make decision where drop a role or not。I also suggest that you shoud check  whether a role owner any objects.


How to enable implicit casts in PostgreSQL >=9.2?

One can also just add ?compatible=7.1 to a jdbc connection url (before the 42 release)... people say 'Implicit casts to text were removed for a reason' but in 10 years I have never had an issue with them. (I have fixed other bugs is pgsql though)

https://www.postgresql.org/docs/current/static/sql-createcast.html

\dC
********* QUERY **********
SELECT pg_catalog.format_type(castsource, NULL) AS "Source type",
       pg_catalog.format_type(casttarget, NULL) AS "Target type",
       CASE WHEN castfunc = 0 THEN '(binary coercible)'
            ELSE p.proname
       END as "Function",
       CASE WHEN c.castcontext = 'e' THEN 'no'
            WHEN c.castcontext = 'a' THEN 'in assignment'
            ELSE 'yes'
       END as "Implicit?"FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p
     ON c.castfunc = p.oid
     LEFT JOIN pg_catalog.pg_type ts
     ON c.castsource = ts.oid
     LEFT JOIN pg_catalog.pg_namespace ns
     ON ns.oid = ts.typnamespace
     LEFT JOIN pg_catalog.pg_type tt
     ON c.casttarget = tt.oid
     LEFT JOIN pg_catalog.pg_namespace nt
     ON nt.oid = tt.typnamespace
WHERE ( (true  AND pg_catalog.pg_type_is_visible(ts.oid)
) OR (true  AND pg_catalog.pg_type_is_visible(tt.oid)
) )
ORDER BY 1, 2;
**************************
                                         List of casts
         Source type         |         Target type         |      Function      |   Implicit?
-----------------------------+-----------------------------+--------------------+---------------
 abstime                     | date                        | date               | in assignment
 abstime                     | integer                     | (binary coercible) | no
 abstime                     | timestamp without time zone | timestamp          | yes
...


Single database schema or multiple schemas?

the 'cleaner' solution would definitely using a single schema, and relate your data to a specific event, either with a EventID column, or yet better, using an Events table, relating your data to a row in this table (but keep it simple, you don't need to relate every row in every table to your event, just the 'top-most' objects)

but on the other hand, maybe 'You Aint Gonna Need It', a lot can change in a year, and you'll probably have to make a lot of changes and improvements for next years event. anyway you'll find yourself carrying over and migrating data which nobody needs or wants, so you may end up purging your database after all.

i learned to stick to the YAGNI rule the hard way several times, and keeping things simple always proofed to be the better solution. as long as you produce clean and maintainable code, it's often much simpler to add functionality, than to trim unneeded (and often untested) fat


How to find the fitting version of pgadmin III?

The most up-to-date version is always available with the most-up-to date version of postgres. So the coming 1.14 of pgAdmin support 9.1 with it's features, 1.12 -> 9.0, 1.10 -> >8.4. I noticed that pgAdmin will disable (or disallow you to use) certain features that do not support your installed pg version.

Sidenote: I made bad experiences with using the build-in backup/restore tools from pgAdmin (on Linux), no idea why this wasn't working but one machine just wasn't able to import a backup made by another one with same version, specs,... Had this issue many times, so I always prefer the command line export/import. The latter has never failed and offers more options for exporting and importing.


Why Do My Nonclustered Indexes Use More Space When I Delete Rows?

One possible scenario that very much amuses me:

The rows were originally written when the database didn't have Read Committed Snapshot (RCSI), Snapshot Isolation (SI), or Availability Groups (AGs) enabled
RCSI or SI was enabled, or the database was added into an Availability Group
During the deletions, a 14-byte timestamp was added to the deleted rows to support RCSI/SI/AG reads

Since this server is a primary in an AG, it's affected just like the secondaries are. The version info is added on the primary - the data pages are the exact same on both the primaries and secondaries. The secondaries leverage the version store to do their reads while the rows are being updated by the AG, but the secondaries don't write their own versions of the timestamp to the page. They just inherit the versions from the primary's work.

To demonstrate the growth, I took the Stack Overflow database export (which doesn't have RCSI enabled) and created a bunch of indexes on the Posts table. I checked index sizes with sp_BlitzIndex @Mode = 2 (copy/pasted into a spreadsheet, and cleaned up a little to maximize info density):

I then deleted about half of the rows:

BEGIN TRAN;
DELETE dbo.Posts WHERE Id % 2 = 0;
GO

Amusingly, while the deletes were happening, the data file was growing to accommodate the timestamps, too! The SSMS Disk Usage Report shows the growth events - here's just the top to illustrate:

(Gotta love a demo where deletes make the database grow.) While the delete was running, I ran sp_BlitzIndex again. Note that the clustered index has less rows, but its size has already grown by about 1.5GB. The nonclustered indexes on AcceptedAnswerId have grown dramatically - they're indexes on a small value that's mostly null, so their index sizes have nearly doubled!

I don't have to wait for the deletion to finish to prove that out, so I'll stop the demo there. Point being: when you do big deletions on a table that was implemented before RCSI, SI, or AGs were enabled, the indexes (including the clustered) can actually grow to accommodate the addition of the version store timestamp.


Moving PostgreSQL database to new server

Upgrading a major version of PostgreSQL (i.e. 8.3 to 8.4) requires a database dump and restore - simply copying in data from an older version will most likely not work.

There are still some copies of 8.3 that you can download from EnterpriseDB to get you going. However it would still be best to upgrade to the later version afterwards if possible.


So much of accounts and visitors is common, that it pains me to see the extra tables for sessions and cookies.  I'd rather see visitors with NULL values for the extra 3-4 columns.


Cannot be done with PG 8.4.
It's actually on the TODO of postgres to reduce trigger requirements:

https://wiki.postgresql.org/wiki/Todo
http://www.postgresql.org/message-id/1213387611.25121.250.camel@ebony.site


HashBytes Computed Column: Exclude Column

You could script this as part of your release process.  EG something like:

declare @script nvarchar(max)
with q as
(
    select   concat(N'
    alter table ',quotename(schema_name(t.schema_id)),'.',quotename(t.name),' drop column if exists RowHash;
    alter table ',quotename(schema_name(t.schema_id)),'.',quotename(t.name),'
    add RowHash as hashbytes(''SHA2_512'', concat('''',',STRING_AGG(cast(quotename(c.name) as nvarchar(max)), ',''|'',') WITHIN GROUP (ORDER BY c.column_id ASC),'));
    ' ) cmd
    from sys.tables t
    join sys.columns c
      on t.object_id = c.object_id
    join sys.types ty
      on c.user_type_id = ty.user_type_id
    where c.is_identity = 0
     and t.object_id in (select object_id from sys.columns where name = 'RowHash')
     and c.name <> 'RowHash'
     and t.is_ms_shipped = 0
     and c.is_computed = 0
     and ty.name not in ('hierarchyid','xml', 'geography', 'geometry')
    group by t.object_id, t.schema_id, t.name
)
select @script = string_agg(cast(cmd as nvarchar(max)) , N'
')
from q

print @script
--set xact_abort on
--begin transaction
--exec sp_executesql @script
--commit


Finding table name referfences in SSIS packages

If you check the table [SSISDB].[catalog].[packages], you'll find the packages XML. From there, you can parse the XML (might be quite painful) and extract the  tag.

This might be a good start: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6c31e43f-4f25-476c-8cfa-87f91fbf609e/help-navigating-xml-file-with-sql-nodes-method-against-a-column-of-xml-type?forum=sqlxml


SQL Server 2014 log growing just in Navision 2015 just when always on is active

Full recovery model and no log backups?
AlwaysOn not pushing data? (so unable to re-use the log filespace)


You don't want a calculated column, instead just use a datetime (non-nullable) column, with a default constraint using getdate().

The syntax for the column in the table definition SQL is as follows:

[autodate] DATETIME NOT NULL DEFAULT(GETDATE())

A Computed Column in SQL Server is calculated when it is queried (unless it is PERSISTED, then it has a value stored on disk and can be indexed, however it is still computed). It is intended to be changeable, and be the result of calculations on another column or columns.

A DEFAULT Constraint simply inserts a default value into the column for any inserts, but then it is held as a  fixed value just like any other data column.


Error installing Clustering with SQL Server 2012 on Windows Server 2012

Install with the pre-SP1 SQL 2012 media.  Then, after your cluster is up, follow the procedure to apply SP1.  It appears the 11/2012 SP1 slipstream installer was not tested for this component when being installed from scratch.

This media worked:  en_sql_server_2012_enterprise_edition_x86_x64_dvd_813294.iso

This did not: SQLServer2012SP1-FullSlipstream-ENU-x64.iso


Refresh a PostgreSQL materialized view automatically without using triggers

As a_horse_with_no_name said in a comment:

  No, that's not possible. You need some kind of scheduler that runs refresh materialized view e.g. pg_cron or something on the operating system level – a_horse_with_no_name

Alternatively, if you need a MATERIALIZED VIEW that refreshes when you run SELECT, just remove MATERIALIZED and use a regular VIEW. Materialization only adds a periodic cache. It's only needed when the query itself is prohibitively slow or hot.


Oracle extents - How can I have so few with this large size?

You may believe that this is a different question but my answer is going to be essentially identical assuming the additional information from the prior question is the same-- you're using automatic extent allocation and you're not sure how Toad is determining these numbers.

The data you're getting from Toad appears to be incorrect or, at least, misleading.  If you are using a locally managed tablespace with automatic extent allocation, Oracle will determine your initial and next extent sizes automatically.  In 11.2, the first 16 extents are going to be 64k in size (for a total of 1 MB).  The next 63 extents are going to be 1 MB in size.  So if you have 12 extents, that implies that the table occupies 768 kb of space on disk.  An initial extent of 118 MB makes no sense and a size of 376 MB makes no sense.

What does

SELECT tablespace_name,
       extent_id,
       bytes/1024 kb
  FROM user_extents
 WHERE segment_name = <<name of index>>

show you?  If you have 12 extents, you should see that extents 0-11 are 64 kb in size.

SELECT SUM(bytes)/1024/1024 size_in_mb
  FROM user_extents
 WHERE segment_name = <<name of index>>

will show you the total size of the table's extents on disk.


Should I separate table with a lot of columns and 3 index column?

I'm doing a bit of guessing here, but I imagine you have a table like:

CREATE TABLE ANSWERS
( USER_ID ...
, TIME_OF_TEST ...
, QUESTION1 ...
, QUESTION2 ...
...
, QUESTION300 ...
,    PRIMARY KEY (USER_ID, TIME_OF_TEST)
);

and now you want to add attributes for  weekScore, monthScore and yearScore.

I think it is a bad idea to add those attributes to this table, and in fact I suggest you rethink your whole design. I would have thought that there is an entity TEST that acts as a placeholder for a number of questions, but since you don't mention it I'm going to ignore that. I would suggest something like:

CREATE TABLE QUESTIONS
( QUESTION_ID ...
, WORDING_OF_QUESTION ...
, SCORE ...
, CORRECT_ANSWER ...
....
)

Dependent of what kind of questions there are, you might even normalize it further with a table QUESTION_ITEMS

CREATE TABLE ANSWERS
( USER_ID ...
, QUESTION_ID ...
, ANSWER ...
, TIME_OF_ANSWER ...
);

It is difficult giving any more specific advise without knowing more details, but hopefully it will give you some ideas.

One common mistake is to think of the database as a spread-sheet that represents the report (your first suggestion above). I suspect that naming a relation table in SQL is the reason for much of this confusion.

For the new attributes weekScore, monthScore and yearScore, I would start by determining them in runtime, i.e. don't store them anywhere. You can create a view for them, and if it turns out to slow, make a table out of it that you update with a batch-process.


How to deploy case-insensitive embedded firebird database?

The syntax you're looking for is here

  CREATE {DATABASE | SCHEMA}    ...    [PAGE_SIZE [=] size]    ...
  [DEFAULT CHARACTER SET charset [COLLATION collation]]    ...
  [DIFFERENCE FILE 'filepath']

  size  ::=  4096 | 8192 | 16384

Also check out this page and this link from it specifically
about case insensitive searches and collations on columns.

The good news is that you can set it both by database and column.

Also, check this out - you can also use "shadow" columns if you
can't get exactly what you want "out of the box" - i.e. UPPER()
function.


What is the technical meaning of "Bulk" in the “Bulk-Logged Recovery Model” and why this name?

As I understand, the Bulk-Logged Recovery Model doesn't fully log the bulk transactions. Then why is it named like this?

Without an answer from the folks that actually named this recovery model, we can only guess about the name.  But it seems to me like "bulk-logged" means something similar to "buying in bulk."

You can buy a single steak every day at the grocery store for one price.  OR, you could buy in bulk, and get a whole month's supply of steaks for significantly cheaper (per steak).

Similarly, you can log every single action and its data (Full Recovery model).  OR, you can log the transactions in bulk (just the page allocations), for significant "savings" in how much information needs to be written to the transaction log.


Concatenate Data from different row from same table

You can use for xml to concatenate values from multiple rows. There are many other ways to concatenate rows as outlined here

For XML example with your data above, first we create and load sample table

    CREATE TABLE [pecomment]([PE_CASE]    int,
                    [PE_ITEM]    varchar(4),
                    [PE_HIS_SEQ] int,
                    [PE_COMMENT] varchar(20));

INSERT INTO [pecomment]
VALUES
      (200, 0001, 9989, 'Comment 1'),
      (200, 0001, 9990, 'Comment 2'),
      (200, 0001, NULL, 'Comment 3'),
      (200, 0002, 9986, 'Comment 4'),
      (200, 0002, 9987, 'Comment 5'),
      (200, 0002, NULL, 'Comment 6'),
      (201, 0001, 9926, 'Comment 7'),
      (201, 0001, 9937, 'Comment 8'),
      (201, 0001, NULL, 'Comment 9'),
      (201, 0002, 9933, 'Comment 10'),
      (201, 0002, 9921, 'Comment 11'),
      (201, 0002, NULL, 'Comment 12');

Then use for xml to concatenate the comments, grouping by PE_CASE and PE_ITEM

SELECT [PE_CASE],
      [PE_ITEM],
      NULL,
      [PE_COMMENT] = SUBSTRING((
      SELECT '+ '+[PE_COMMENT]
      FROM [pecomment] AS [t2]
      WHERE [t1].[PE_CASE] = [t2].[PE_CASE]
           AND [t1].[PE_ITEM] = [t2].[PE_ITEM]
      ORDER BY [PE_CASE],
             [PE_ITEM]
      FOR XML PATH('')
      ), 3, 1000)
FROM [pecomment] AS [t1]
GROUP BY [PE_CASE],
        [PE_ITEM];

This should give you what I think is your example desired result, hence the null in PE_HIS_SEQ.


Compare sysdate month with a column data

You have not specified the data type of ExpireMonth.  If it is a date or timestamp you can try this

select * from CARDS where EXTRACT(MONTH FROM EXPIREMONTH)  + 1 = EXTRACT(MONTH FROM CURRENT_TIMESTAMP) + 1


Use a server-side trace, not Profiler. Both have an impact on throughput, Profiler much more so. ClearTrace is a great tool for offline analysis of the trace files.

To answer question 1), you connect to the instance not the node. Question 2), you obviously need to gather data from the node the instance is currently running on.


This is a generic error.

If you receive this in an active session while running some query, PL/SQL code or other statements, you should check the alert log of the database for further information. Usually it is caused by critical errors such as ORA-07445 that cause the server process that belongs to your session to terminate unexpectedly.

But if you receive this error in and idle session after a specific amount of time, as in your case, then do not worry, because it is absolutely normal when the database enforces an inactivity timeout, for example in the case of DRCP (Database Resident Connection Pooling).

Here is an example:

Database Resident Connection Pool (DRCP)


Every person has a contact info table and every contact has an address

Name - can't be considered a good candidate for primary key. Since, it can be duplicate/modified later. In your case, I don't see any natural key which can be used as primary key. Better, you can add/use a surrogate key(Serial No. -autoincrement) as primary key.
For address table: If you are also storing email address. You can create  separate columns for email and postal address and make email address as primary key.
To reference Contact Info from Person table - you can use email address as foreign key into person table.
So, schema can be like below:
    Person:
    S.N. : (Auto Increment) - Primary Key
    name :
    email : Foreign key

    Contact:
    email : Primary key
    Postal address


Is there a way to force sp_send_dbmail to use ANSI, or to not include the Unicode prefix?

I think you can generate the reports using powershell and then use DBMail to mail the attachments

Write-host "Running SQL Query to export to ANSI CSV file"
Invoke-Sqlcmd -Query $dbQuery -ServerInstance $instanceName | export-csv $targetFileAnsi
Write-host "Running SQL Query to export to Unicode CSV file"
Invoke-Sqlcmd -Query $dbQuery -ServerInstance $instanceName | export-csv $targetFileUnicode -encoding "unicode"

$dbMailQuery1 = "execute msdb..sp_send_dbmail
@profile_name = 'msft_profile',
@recipients = 'seths@microsoft.com',
@subject = 'Email with Attachment - ANSI attachment',
@body_format = 'TEXT',
@body = 'This email has ANSI file attached to it',
@file_attachments = 'c:\temp\result_ansi.csv' "

Source: http://blogs.msdn.com/b/sqlagent/archive/2011/07/06/email-query-output-as-ansi-unicode-file-attachment-using-powershell-and-database-mail.aspx


Restrict Oracle DB Login based on SQL Client being used

You can check it with a database trigger, for example:
CREATE ROLE ROLE_POWER_USER NOT IDENTIFIED;

CREATE OR REPLACE TRIGGER LOG_T_LOGON
    AFTER LOGON ON DATABASE
DECLARE

    osUser VARCHAR2(30);
    machine VARCHAR2(100);
    prog VARCHAR2(100)
    ip VARCHAR2(15);

BEGIN

    IF ora_login_user IS NULL THEN
        RETURN;
    END IF;

    SELECT OSUSER, MACHINE, PROGRAM, ora_client_ip_address
    INTO osUser, machine, prog, ip
    FROM V$SESSION
    WHERE SID = SYS_CONTEXT('USERENV', 'SID');

    IF NOT DBMS_SESSION.IS_ROLE_ENABLED('ROLE_POWER_USER') THEN
        IF LOWER(prog) <> 'your_application_name.exe' THEN
            RAISE_APPLICATION_ERROR(-20000, 'Logon denied: You must use only the official client application');
        END IF;
    ELSE
        IF LOWER(prog) NOT IN ('sqlplus.exe', 'toad.exe') THEN
            RAISE_APPLICATION_ERROR(-20000, 'Logon denied: You must use only SQL*Plus or TOAD for you private queries');
        END IF;
    END IF;
    -- Successful login, continue as normal
END;
/

You can also check other conditions like IP-Address or the machine name.
SELECT privileges on tables and views you have to grant to the user or ROLE in the "classic" way. This trigger only prevents to logon to the database with certain tools.
Note, a user with System Privilege ADMINISTER DATABASE TRIGGER (for example DBA role, or SYS of course) never get the exception, i.e. they can logon to the database in any case and you cannot block this by the trigger.
Another note: This trigger is insecure! For example you can simply make a copy of your local sqlplus.exe and name it your_application_name.exe. Then this trigger would allow to use it.
With JDBC it is even a simple property you can set, see https://stackoverflow.com/questions/42027389/programatically-set-vsession-program-property


How to configure postgresql to use multiple cores?

PostgreSQL automatically uses multiple cores to support multiple database connections, if you have multiple cores.  No further configuration is necessary.


You can rebuild an unusable index using

alter index DM_ALM.DM_ALM_USER_ROLES_PK rebuild;


Same SSRS report, two different subscription names

Need to update XML code in report database tables(after report deployed in report server then need to create subscription, its data will be stored in sql table by xml format) with required format.


Reducing database size to restore to SQL Server Express

The sum of the file size for all data files has to be < 10 GB, not the amount of data in the file. So, deleting data from some tables, or even dropping some tables, does not solve problem. You need to shrink the file, something like this:

ALTER DATABASE mydb MODIFY FILE (name = N'logical_name', size = 2048MB);

This will fail if the database size can't be reduced to 2 GB. You may need to first issue:

DBCC SHRINKFILE(logical_name, 2048);

If you use any form of SHRINKFILE, then you'll need to validate in File Explorer that the data file(s) are actually as small as you think (because shrink operations will shrink as much as they can, and stop silently when they can't reach your target size).

Then take a backup, then restore on SQL Server Express (with @@VERSION the same or higher than the source, of course).

You may come across other issues, for example if you have used any features that aren't supported on Express.


MySQL HDD activity with empty process list

The InnoDB Storage Engine does lots of writing to disk behind-the-scenes. Please remember the subheading ASPECT #4 : Disk Issues from My Answer to Your Previous Question:

  ASPECT #4 : Disk Issues

  What is also noteworthy is this regard is the system tablespace
  ibdata1. Every time you access an InnoDB tables, the data dictionary
  is always consulted. Thus, having spread out tables onto other disk
  does not negate accessing ibdata1 back in datadir. Accessing two disks
  would always be in order.

  InnoDB has many moving parts in ibdata1:

  Data Dictionary
  Double Write Buffer (Safety Net to Prevent Data Corruption Helps Bypass OS for Caching)
  Insert Buffer (Streamlines Changes to Secondary Indexes)
  Rollback Segments
  Undo Logs
  Click Here to see a Pictorial Representation of ibdata1

  Expect the Highest I/O around ibdata1. In light of this, whatever disk tuning you do, give weight to the location of ibdata1.

From what source does InnoDB do this background writing to ibdata1?

The InnoDB Buffer Pool contains Dirty Pages. Those dirty pages must be systematically flushed to disk. Dirty pages come about when INSERTs, UPDATES, and DELETE are made. Those changes are not posted directly to tables. They are queued up in the InnoDB Buffer Pool's Dirty Pages.
There are also writes being posted in the Double Write Buffer within ibdatato bypass OS caching.
Secondary Index changes are queued up in the InnoDB Buffer Pool and  migrated to the Insert Buffer within ibdata1
Since up to 1023 simultaneous Transactions are possible for InnoDB, any data written in the Undo Logs and Rollback Segments are discarded in an orderly fashion.
When there is heavy writing to InnoDB, the Log Buffer catches the initial changes and are scheduled based on how Transaction Flushing is Done (set by innodb_flush_log_at_trx_commit,)

Setting innodb_flush_log_at_trx_commit=0 flushes the log buffer out to the log file once per second and the flush to disk operation is performed on the log file, but no writes are done at a transaction commit. A 1-second loss of data is possible on a server crash or a mysqld crash.
Setting innodb_flush_log_at_trx_commit=1 (Default) flushes the log buffer for each commit and flush the logs to disk
Setting `innodb_flush_log_at_trx_commit=2 flushes the log buffer out to the file at each commit, but the flush to disk operation is not performed on it. A 1-second loss of data is possible on a server crash.

NOTE : Even if no new InnoDB data have been written in a while, reads from the moving parts of ibdata1 must be done to determine availability of incoming data changes. Running a SELECT query against an InnoDB table requires checking the .frm file of the table and cross checking the data dictionary inside ibdata1. I noted this storage engine behavior in my past post : MySQL tables/databases into different disks


sys.database_files and sys.master_files show different physical_name values

I couldn't find anything about this in the documentation for sys.database_files or sys.master_files, or even sys.sysfiles or sys.sysaltfiles, but there will likely be several state changes that won't be truly reflected across all file-related catalog views until the SQL Server service has been restarted. If the changes are isolated to a single database, it might be worth trying to set the database OFFLINE and ONLINE again, but I haven't confirmed that will work.


Should I backup a database with only temporary data?

If there is a database with data on it, someone, at some point, will consider it important. So best approach would be run a full backup to avoid any hassle. Maybe weekly with couple of daily differentials. If there are frequent changes then maybe a daily full backup and one differential in the middle of the day.

If the data is temporary and can be recreated somehow, then I would configure the recovery model to SIMPLE and take a FULL backup daily. And to avoid spaces issues, if the data is not so important and can be recreated afterwards, I would add a process to empty all tables before running the backup. Then configure another step after backup process in order to add fresh data once again so anyone using the database has data to continue working.

And last, but not least, you could just script the database and recreate it from those scripts. Do you have your database code under source control? That would help a lot on this case. We have couple of databases with similar characteristics and we only save the recreation scripts, data can be easily recreated with couple of scripts.


Unix Epoch timestamp for now() in postgresql

Add an appropriate type cast:

CAST (EXTRACT (epoch from current_timestamp) AS bigint)


Should I use one-to-many or many-to-many to log previous records?

I don't think there is many-to-many relationship exists in this case as the Intern will always be associated with a single company.

I would prefer to go with say a LOG (or InternCompanyHistory) table to save InternId, CompanyId and DateOfChange or any other attributes associated with change of company.


You can check what's going on using SQL Profiler.  When you execute an OLE DB task, you should see something like this as an RPC:Completed event:

declare @p1 int
set @p1=1
exec sp_prepare @p1 output,N'@P1 int',N'update testtable set id = @P1',1
select @p1

In this case, I'm just updating a test table (called testtable).

Then, for each iteration, you'll see an RPC:Completed event which reads something like:

exec sp_execute 1,50

This is saying to execute the first prepared event (our update statement--sp_prepare's first parameter is "1" here), and send in 50 as the new ID for testtable.

If you don't see these calls on your database as you debug your flow, it might indicate that you're actually doing the process on a different instance (or maybe there's something like a trigger preventing your change from occurring?).


How does pausing a SQL Server mirror affect transaction logs on the principal?

I would recommend that you strongly consider breaking the mirror and rebuilding it once the maintenance is complete.

When you pause the mirror, the transactions that need to be sent over will build up in your transaction log.  Taking a transaction log backup will not free this space for reuse, although the log backups will be consistent and usable for their intended purpose (point in time recovery).

The same type of consideration needs to be made for when using Availability Groups.  In an outage (planned or otherwise), how many (minutes/hours/days) of transactions can my log file hold?  How long will it take that information to be sent over?  How long will it take to rebuild the mirror/availability group?

I usually make sure that there is enough free space for the time for (error to happen, notifications to bubble up (phone calls, text, etc.) then allow an hour or two for investigation/corrective action.  I fight for at least that much disk space.

You can look at the transaction log backup size (uncompressed) for an idea of how much data you are talking about.  Then plug in guesses for the rest.  You can buy yourself extra time by turning off any maintenance (index defragmentation, for example) while the mirror is paused.

But since you are going to measure this outage in days, I would break the mirror and rebuild it once it's back online.


MySQL - Delete row that has a foreign key constraint which reference to itself

Besides disabling foreign keys which is is dangerous and can lead to inconsistencies, there are two other options to consider:
Modify the FOREIGN KEY constraints with the ON DELETE CASCADE option. I haven't tested all cases but you surely need this for the (owner_id) foreign key and possibly for the other as well.

ALTER TABLE forum
    DROP FOREIGN KEY owner_id_frgn,
    DROP FOREIGN KEY parent_id_frgn ;
ALTER TABLE forum
    ADD CONSTRAINT owner_id_frgn
        FOREIGN KEY (owner_id)
        REFERENCES forum (id)
        ON DELETE CASCADE,
    ADD CONSTRAINT parent_id_frgn
        FOREIGN KEY (parent_id)
        REFERENCES forum (id)
        ON DELETE CASCADE ;

If you do this, then deleting a node and all the descendants from the tree is simpler. You delete a node and all descendants are deleted through the cascade actions:

DELETE FROM forum
WHERE id = 1 ;         -- deletes id=1 and all descendants

The issue that you stepped into is actually 2 issues. The first is that deleting from a table with self-referencing foreign key is not a serious problem for MySQL, as long as there is no row that references itself. If there is a row, as in your example, the options are limited. Either disable foreign keys or use CASCADE action. But if there are no such rows, deleting becomes a smaller problem.

So, if we decide to store NULL instead of the same id in owner_id, then you could delete without disabling foreign keys and without cascades.

You would then stumble upon the second problem! Running your query would raise a similar error:

DELETE FROM forum
WHERE owner_id = 1 OR id = 1 ;

  Error(s), warning(s):
  Cannot delete or update a parent row: a foreign key constraint fails (rextester.forum, CONSTRAINT owner_id_frgn FOREIGN KEY (owner_Id) REFERENCES forum (id))

The reason for this error would be different than before though. It's because MySQL checks each constraint after every row is deleted and not (as it should) at th eend of the statement. So when a parent is deleted before its child is deleted, we get a foreign key constraint error.

Fortunately, there is a simple solution for this, thnx to the nested set model and to that MySQL allows us to set an order for the deletes. We just have to order by nleft DESC or by nright DESC, which makes sure that all the children are deleted before a parent:

DELETE FROM forum
WHERE owner_id = 1 OR id = 1
ORDER BY nleft DESC ;

Minor note, we could (or should) use a condition that considers the nested model as well. This is equivalent (and might use an index on (nleft, nright) to find which nodes to delete:

DELETE FROM forum
WHERE nleft >= 1 AND nright <= 8
ORDER BY nleft DESC ;


Effect of --flush-logs on a slave mysqldump?

Point to be considered whether your --flush logs ( Closes current binary log & opens new binary log with consecutive number ) will be beneficial or not :

One thing that you are using --single-transaction option (It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications). This means a snapshot of current database is taken for backup where data modification during this timestamp is not considered in backup & can be retrived from binary logs.

To check the write operation is performed during backup activity on database, you can verify by no of binary logs written during that time.

If write operation intensity is more during this time, then you can afford to start replication from last file written during backup completion.

To overcome this thing,

Measure no of binary logs written during backup time. ( Hope backup time is preferred where less write operation are performed on db).

If No of Binary logs are more during backup time :
Take before backup binary logs reading from master & can be used for starting replication ( Probably there may duplicate error, need to skip it) as data consistency is concerned.

mysql -uuser -pXXX -h192.168.XX.XX -e 'show master status' > before_rd.sql

time mysqldump  -uuser -pXXx -h192.168.XX.XX --force --all-databases --max_allowed_packet=360M --lock-tables=false --triggers --routines > backup_$dd.sql 2> bkp_mep_$dd.log

mysql -uuser -pXXX -h192.168.XX.XX -e 'show master status' > after_rd.sql

If No of Binary logs are less or none during backup time :
Take after backup binary logs reading from master & can be used for starting replication ( -- flush-logs can be used ) as data consistency is maintained.


Database organization for mirroring / partitioning / multistore

Would service broker be an option? It can handle disconnected situations.
Use a local database on each PC ("clients") and one central database on the server. Each client sends Service Broker messages (XML files e.g.) to the server. For instance send information about an "action" from the production line.
Whenever the connection is lost, all messages are queued until you reconnect.
At the server you need to write a handler that processes these messages.


I would recommand always to use the appropriate datatypes, operators and functions and explicit conversion  and not to use string literals and string processing and implicit conversion.

Look at your statement

select from_date
from date_table
where length(extract(year from from_date))='2'
/

The return type of the length statement is clearly a number. So why do you compare it with a string literal? Something has to be converted implicitly. The number result of the length function to a string or the string literal '2' to a number. Do you know which one is converted? Do you know if there can any problems arise from this conversion? Is there a special reason why you take a string constant instead a number constant?

As far as I know the string constant will be converted to a number and I can't see any problems with this conversion. I also think that there is no special reason why you take a string constant instead of a number constant. But why bother about that questions?

Use a number literal and write

select from_date
from date_table
where length(extract(year from from_date))=2
/

I think you want to find all rows where from_date by the implicit conversion to a very early date and therefore is before 1st January 100. So why string processing? Use date processing and write

select from_date
from date_table
where from_date<to_date('0100-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
/

If there is an index on the from_date column this query may be able to use the index.

This query will also find rows with a from_date before '0010-01-01 00:00:00' and even dates before '0000-00-00 00:00:00'. Also it can be easily adjusted to other timestamps. But if you want to explicitly use the year ( and you are sure that there are no dates before 0 B.C.) you can

select from_date
from date_table
where extract(year from from_date) between 0 and 99
/

If there is an index on the from_date column this query may not be able to use the index.

Adding 2000 years to a date can be done by adding INTERVAL '2000' YEAR(4)

UPDATE table_date
SET from_date  = from_date+INTERVAL '2000' YEAR(4)
WHERE to_date <to_date('0014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
/

UPDATE table_date
SET from_date  = from_date+INTERVAL '1900' YEAR(4)
WHERE to_date >=to_date('0014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
/

Because you started with string processing by inserting rows with the date represented as strings you can also make your correction using string processing. If you did not use a time part when inserting the rows you could/should omit the HH24:MI:SS format part in the following statements

UPDATE table_date
SET from_date  = to_date('20'||substring(to_char(from_date,'YYYY-MM-DD HH24:MI:SS'),2),'YYYY-MM-DD HH24:MI:SS')
WHERE to_date <to_date('0014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
/

UPDATE table_date
SET from_date  = to_date('19'||substring(to_char(from_date,'YYYY-MM-DD HH24:MI:SS'),2),'YYYY-MM-DD HH24:MI:SS')
WHERE to_date >=to_date('0014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
/


Grouping Selecting unique values in a single column

SQL Server 2012 offers the 'LAG' keyword which enables a SELECT statement to display the previous row.  You could use this to compare the current row to the previous row in the WHERE clause.

Here is an example:

USE Test;
CREATE TABLE LagTest
(
    ID INT NOT NULL CONSTRAINT PK_LagTest PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Data NVARCHAR(255) NOT NULL
);

INSERT INTO LagTest (Data) VALUES ('test1');
INSERT INTO LagTest (Data) VALUES ('test1');
INSERT INTO LagTest (Data) VALUES ('test1');
INSERT INTO LagTest (Data) VALUES ('test2');
INSERT INTO LagTest (Data) VALUES ('test2');
INSERT INTO LagTest (Data) VALUES ('test2');
INSERT INTO LagTest (Data) VALUES ('test3');
INSERT INTO LagTest (Data) VALUES ('test3');
INSERT INTO LagTest (Data) VALUES ('test3');

SELECT *
FROM (
SELECT ID, Data, LAG(Data,1,0) OVER (ORDER BY ID) AS PreviousData
FROM LagTest
) AS L
WHERE L.PreviousData <> L.Data;

The returns:


Analysis services access through MS Excel- permissions issues

My issue here was that the accounts had an Active Directory policy that was set to expire the password at first log in. In other words, the user was meant to reset their password when they first use the account.

Taking this off the password policy made the accounts work.


mysql: ERROR 1040 (HY000): Too many connections and can't restart because of Error: 28 (No space left on device)

here there is a full guide on how I solved the issue:
I tried to log in as root with the command:
sudo mysql -u root -p

But it did not work for me, mysql kept giving the error:
ERROR 1040 (HY000): Too many connections

After some searches I tried
service mysqld restart --max-connections=500

This way stopped mysqld, which wasn't able to restart because at the same time my disk was full and receiving:
 Error: 28 (No space left on device)

Then, in order to inspect on ubuntu where my space was occupied I used the command:
du -h --max-depth=1 | sort -hr

That let me know all the space was occupied by binlog files of mysql.
I couldn't remove files by hand, it is not recommended procedure.
But I did not have anything to remove and I was not able to start mysql to purge the binlog files.
Then I run the following command which gave me enough space to restart mysql:
apt-get clean
service mysql restart || systemctl restart mysqld

At this point I was able to login to mysql and purge the binlog files with:
PURGE BINARY LOGS BEFORE '2021-03-03 22:46:26';

But this is not a long term solution.
To avoid the problem in the future I did:
SET GLOBAL binlog_expire_logs_seconds = 432000; // 5 days of binlog files

I am going to improve this answer when I solve the maximum connection issue for a long term solution.


We had 2 problems:

1) We had one mysql query that used three joins. Turns out this function was crashing Mysql. We rewrote this query to use 4 mysql queries without joins and that solved that problem. (Bit of a hot-fix, we will probably rewrite the function so it is possible to cache it).

2) We were experiencing around 99.9 % I/O wait when we only used 10% cache Mysql only using 10% of cache . We tried to edit the mysql config (quoted at the bottom). This helped a lot but did not fix the problem. It turned out another user on the shared server was causing 99.8% I/O spikes. After contacting our server provider they moving the server to another partition and the problem was fixed.

table_open_cache = 1024
sort_buffer_size = 4M
read_buffer_size = 128k
query_cache_size= 128M
query_cache_type = 1
tmp_table_size = 64M
thread_cache_size = 20
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_file_per_table innodb_file_format = Barracuda


How do I get multiple rows output in one line?

You can try as below:

SELECT name,
LISTAGG(item_name, ' ') WITHIN GROUP (ORDER BY item_name) AS item_name
FROM item
GROUP BY name;


Facing error while executing function inside the package in PL/SQL

Well, yes - but not necessarily. As you wanted to use EXEC (which is SQL*Plus), then have a look how you could have done it; first, a package with a function that returns department name for a certain department number.

SQL> create or replace package my_first_package as
  2    function show_Description(par_deptno in varchar2) return varchar2;
  3  end;
  4  /

Package created.

SQL> create or replace package body my_first_package as
  2    function show_Description(par_deptno in varchar2)
  3      return varchar2
  4    is
  5      retval dept.dname%type;
  6    begin
  7      select dname
  8        into retval
  9        from dept
 10        where deptno = par_deptno;
 11      return retval;
 12    exception
 13      when no_data_found then
 14        return null;
 15    end;
 16  end;
 17  /

Package body created.

Now, how to use EXEC: declare a variable first, and then ...

SQL> var l_ret varchar2(20);
SQL> exec :l_ret := my_first_package.show_description(10);

PL/SQL procedure successfully completed.

SQL> print l_ret;

L_RET
-------------------------------------------------------------
ACCOUNTING

SQL>

If you'd want to use that function in PL/SQL, then you could do the same - declare a variable and return the function value into it, e.g.

declare
  l_ret varchar2(20);
begin
  some code here
  l_ret := my_first_package.show_description(10);
  ...
end;

Or, you could use it "directly" in, for example, WHERE clause:

begin
  select ...
  into ...
  from ...
  where description = my_first_package.show_description(10)
    and ...
end;


Compare Microsoft SQL server performance between virtual and dedicated server

Testing isolated queries isn't going to help you make this decision. As you have an existing database live, use it to capture a  representative workload.

Capture a profiler trace from your live, dedicated server
Use the RML toolset to create a replay file
Execute against the virtual server using ORCA

I posted a brief summary of the RML tools in an earlier question which you may find useful. To compare results across both runs, capture a trace of the first trace being run against the virtual server and run both through ClearTrace.


There's an XEvent for that:

  query_cache_removal_statistics

  Occurs when a query plan is removed from the plan cache and the
  historical statistics for the object are about to be destroyed

So something like:

CREATE EVENT SESSION [PlanCacheEvictions] ON SERVER
ADD EVENT sqlserver.query_cache_removal_statistics(
    ACTION(sqlserver.sql_text))

Also if your plan cache has a large number of single-use plans consider setting optimize for ad hoc workloads.


Trying to install sql_server_2014_enterprise_edition_x64_dvd Windows 7(Ultimate) 64Bit OS

First of all I think you should be installing Developer Edition as Windows 7 is not a server-OS.  Make sure you have admin rights on your machine.  It may also be worth trying the different install media?

However it looks like there is an open connect item for this error and further discussion here

As a workaround, you could use some of your Azure credits to spin up one of the pre-prepared VMs which already have SQL Server 2014 installed, or try Express edition.


MySQL read only slave - how to create views

I have an issue now in that I am really limited to what I can do with
  the data in MySQL - cannot create views etc.

The biggest issue in above is "etc".
Any editing operation on Slave can crash the replication process, and Your head of IT just absolutely correct want avoid regular restore of server.

The possible ways:

create all views on Master. It safe. Views not add additional loading to server and will be automatically replicate to Salve
If You do not need real-time data (it normal practice) - go from replication to nightly restore few important tables. Operation could be automate easily
and as mentioned in Your post - continue use Access. May be not the best, but it very good tools for personal use.
In addition to Access - look for PowerBI (or similar tools) they could make many transformation in memory without creating views

It is possible to create views on Slave without collisions with replication, but ... but feature unpredictable and IT just try to avoid any potentials issues, and they are right.


What would cause an elastic pool to exhibit high "Sql Server process core percent" metricsj, but low DTU / CPU

So DTU actually combines three different things into one (CPU, Memory and Disk) and even with Disk you get a fixed Disk Size but also a max limit of IOPS and Throughput. I would recommend to check all these metrics out and see what all of these are showing you. Also look at connection failures other connection related metrics as well. For a time trend average stat is a good view but for a time period when you would like to know what happened at that minute then look at both average and max.
More details on DTU model can be found here.


I am concerned about users entering malicious queries. Is there a way to assure (via syntax checking or role permissions or whatever) to assure that the database is kept secure while providing this functionality?

That is actually two separate, though related, questions: one about "security", and one about "malicious". While security violations are malicious, not all malicious queries are security violations.
You can lock down read and write access to various tables through proper use of permissions and Views. However, I don't think you can prevent them from doing things such as issuing SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; and then using XLOCK or TABLOCK table hints. Or combine that with issuing BEGIN TRAN; and then select from a table using those query hints, and then issue a WAITFOR DELAY '10:00:00.000'; -- 10 hour delay, hee hee.
Read-only access to user databases doesn't prohibit temporary objects, right? So they can still create temporary tables and table variables? If so, someone could intentionally, or "accidentally" fill up tempdb **, or at least degrade performance by adding a lot of contention to tempdb and its log file.
Lots of CURSORs will cause either more locking and contention (i.e. blocking) if dynamic, or more tempdb usage if STATIC.
<sarcasm>And I'm sure all of these users have been trained in both T-SQL and your data-model, hence won't be executing horribly inefficient queries that will fill the Buffer Pool more than necessary, thereby reducing Page Life Expectancy and degrading performance of the entire application.</sarcasm>
This is what a Read-Only Replica is for, or a secondary system populated via log-shipping, etc. Don't give direct, ad hoc query access to your OLTP system to anyone who can't be fired for misuse (i.e. employee or subcontractor).
** @MartinSmith noted that one can just execute the following to create a 10 billion character string:
SELECT DATALENGTH(REPLICATE(REPLICATE(CAST('X' AS VARCHAR(MAX)),100000),100000));

That amounts to 9.31 GB of data space needed in tempdb (it shows up when using the sys.dm_db_task_space_usage and sys.dm_db_session_space_usage DMVs). OR, just add one little letter to make the VARCHAR(MAX) an NVARCHAR(MAX) instead, and you will have yourself an 18.63 GB string ;-). Woo hoo!

Since the question was clarified to focus mostly on data loss and not "denial of service", I can think of two scenarios to look out for:

Linked Servers: If you have any Linked Servers, make sure that the local, read-only Login will not connect to the remote server using a mapped Login/password for a read-write account.
Or you can at least try to hide Linked Servers from the read-only Login. Please see the "Permissions" section of the MSDN page for sys.servers for details.

Elevated permissions due to Ownership-Chaining, EXECUTE AS, and code signing: Do a thorough review of all Stored Procedures that this read-only Login might have EXECUTE rights to. It is really easy to bypass direct object permissions via Ownership-Chaining, EXECUTE AS, and code-signing. If you are using any "Ole Automation Procedures" or SQLCLR objects, those can potentially reach outside of the database and so while you might have CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, MERGE, TRUNCATE, etc locked down, there can be Stored Procedures that write to the file system, registry, etc that won't be prohibited by those other restrictions. You need to make sure that the entire system is read-only, not just SQL Server.

P.S. While this is not a security issue, on a more practical level relating to giving "users" the ability to write ad hoc queries: how will these queries be used, and what is your level of obligation to notify the "users" of changes to the data model? Giving people ad hoc query ability is not the same thing as providing an API. The data model will inevitably change over time. These changes will possibly break queries that the "users" had been executing successfully (even if painfully) in the past. Will your client be upset with you when their users complain that there queries broke (and yes, they will complain because they will save queries in a file or wiki page and build business-processes to run them on a regular basis)?


Does PostgreSQL support ICU collation's options and settings?

Case insensitive or accent-insensitive collations cannot be used prior to PostgreSQL 12, because internally PostgreSQL considers that strings with a different binary representation are not equal. When the collation-aware comparator says they are equal, it uses the non-collation-aware strcmp() function as a tie-breaker, to obtain what Unicode calls a "deterministic" comparison without normalization.

Starting with PostgreSQL 12, collations have a deterministic property, which must be set to false to benefit from equality of non-binary equal strings. From CREATE COLLATION:

  DETERMINISTIC

  Specifies whether the collation should use deterministic comparisons.
  The default is true. A deterministic comparison considers
  strings that are not byte-wise equal to be unequal even if they are
  considered logically equal by the comparison. PostgreSQL breaks ties
  using a byte-wise comparison. Comparison that is not deterministic can
  make the collation be, say, case- or accent-insensitive. For that, you
  need to choose an appropriate LC_COLLATE setting and set the collation
  to not deterministic here.

  Nondeterministic collations are only supported with the ICU provider.

The exact query in the question is not possible with nondeterministic collations because they don't support LIKE or any form of pattern matching (as of PostgreSQL 12).
Also the locale should have a -u- before the collation subtags, otherwise they're going to be silently ignored by the collator.

What does work as intended:

=# CREATE COLLATION special (
     provider = icu, locale = 'en-u-ks-level1', deterministic=false
   );

=# SELECT 'Å' = 'A' COLLATE "special";
 ?column?
----------
 t


MySQL tuning (my.cnf) for very large tables and ad hoc queries

very large tables more then 70Million to query

If you have more than 70 million tables then you need to get a big stick and apply it repeatedly to the person responsible for this.

There's lots of things you do to improve the performance a database (and ignoring this line, some small changes could massively increase the performance of a sensible schema) - but until you sort the number of tables you are wasting your time.


How to add sysadmin to user in SQL Server 2008 when no sysadmin accounts exist

An actual backdoor into SQL Server does exist that does not require restarting and/or rebooting anything into single-user mode. I have done this on systems where I did not have access but needed to check stuff.

Download PSexec tools from here. Place this on the server and then in a command prompt execute this command:psexec -i -s SSMS.exe, or sqlwb.exe

This will open up SSMS as the system account that has sysadmin access to the instance of SQL Server. This is done during installation of SQL Server, however I have heard that this will not be so with SQL 2012.


Mysql 8.0.22 master slave replication: why slave is trying to create user?

Found the issue.  It was caused by the left over binlog.  I externalized the binlog folder, after delete binlog folder on the host and start again, it worked.


Mysql multiline statement causes syntax error

Your multiquery is subject to a race condition. You may not get back the value of the insert you do.

Use insert-id like:

$output = $wpdb->query("
      INSERT INTO wp_priceGrids (gridId) VALUES (NULL)";
$lastid = $wpdb->insert_id;

I've also heard the multiquery aspect of the mysql protocol is generally considered a mistake. Recommend avoiding it.


why do I have huge log files (SQLFT000060005.LOG.x, 3+Gb) with recent timestamps when I haven't used SSMS for 4 months?

Log files are created by the SQL Server service, not by SSMS. If you installed with the default settings, you can stop and restart the service multiple times (or just call sp_cycle_errorlog repeatedly), and that should cycle away the big log files that are there. They'll come back, though, over time - even if you don't open SSMS.

If you don't need SQL Server to be running anymore, disable the service or, if you don't need it at all, uninstall it. You can also uninstall full-text search if you aren't using it (or never needed it). Those FT logs might be from that service.


How can I execute multiple procedures continuously?

The quick and dirty answer here would be to do the following

prepare each query once
prepare each query in separate statement handles
call your execute and save result sets in separate arrays

LIke this

$query = "CALL user_top_categories_score(?, 'ALL', 0, 1)";
$topcat_sth = $this->dbh->prepare($query);

$query = "CALL user_top_tags_score(?, 'ALL', 0, 3)";
$toptag_sth = $this->dbh->prepare($query);

$topcat_sth->execute([$user_id]);
$topcat_rs = $topcat_sth->fetchAll(PDO::FETCH_ASSOC);

$toptag_sth->execute([$user_id]);
$toptag_rs = $toptag_sth->fetchAll(PDO::FETCH_ASSOC);

...


You can observe transaction log usage in Tempdb with DMVs, and then possibly instrument your procedure and monitor its transaction log usage in tempdb to see if you can account for the log file growth and possibly identify what's contributing to it.

EG:

select database_transaction_log_bytes_used /1024. log_mb_used
from sys.dm_tran_database_transactions dt
join sys.dm_tran_current_transaction ct
  on dt.transaction_id = ct.transaction_id
where database_id = 2


Executing query is too slow when accessing SQL Server located in an other country

Any reason why you need to download the entire table every time to every location? select * from table w/o filters across geo locations is an uphill battle you're bound to loose. Filter the data remotely before download. Or make local read-only copies (eg. via log shipping).


Function for generate a huge and possible random relationship between tables

Lets say that the media tables has 1K rows and the emails one has 100K rows.

Then this will insert into the emails_media_xref all possible combinations, 1K x 100K = 100M, that is a hundred million rows.
Warning, this may take some time:

INSERT INTO emails_media_xref
    (emails_id, media_id)
SELECT
    emails.id, media.id
FROM emails
  CROSS JOIN
     media ;

If you want to insert a row for every email, that is 100K rows (random, different choice of media per email):

INSERT INTO emails_media_xref
    (emails_id, media_id)
SELECT
    emails.id,
    ( SELECT media.id
      FROM media
      ORDER BY RAND()
      LIMIT 1
    )
FROM emails ;

If you want to insert (up to) @N rows for every email, that is a  maximum of N x 100K rows (random, different choices of media per email). This will not be exactly N rows per email because of the duplicates (which the DISTINCT will remove):

INSERT INTO emails_media_xref
    (emails_id, media_id)
SELECT DISTINCT
    emails.id,
    ( SELECT media.id
      FROM media
      ORDER BY RAND()
      LIMIT 1
    )
FROM emails
  CROSS JOIN
    ( SELECT 1
      FROM media
      LIMIT @N           -- pick a @N here
    ) AS x ;

If you want to insert a fixed number of rows, say 2000, totally randomly.
Warning, this may take even more time than the first query:

INSERT INTO emails_media_xref
    (emails_id, media_id)
SELECT
    emails.id, media.id
FROM emails
  CROSS JOIN
     media
ORDER BY RAND()
LIMIT 2000 ;


oracle - list users with access to certain tables

List all users who have been assigned a particular role

-- Change 'DBA' to the required role
select * from dba_role_privs where granted_role = 'DBA'

List all roles given to a user

-- Change 'PHIL@ to the required user
select * from dba_role_privs where grantee = 'PHIL';

List all privileges given to a user

select
  lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
  (
  /* THE USERS */
    select
      null     grantee,
      username granted_role
    from
      dba_users
    where
      username like upper('%&enter_username%')
  /* THE ROLES TO ROLES RELATIONS */
  union
    select
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;

Note: Taken from http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html

List which tables a certain role gives SELECT access to?

-- Change 'DBA' to the required role.
select * from role_tab_privs where role='DBA' and privilege = 'SELECT';

List all tables a user can SELECT from?

--Change 'PHIL' to the required user
select * from dba_tab_privs where GRANTEE ='PHIL' and privilege = 'SELECT';

List all users who can SELECT on a particular table (either through being given a relevant role or through a direct grant (ie grant select on atable to joe))? The result of this query should also show through which role the user has this access or whether it was a direct grant.

-- Change 'TABLENAME' below
select Grantee,'Granted Through Role' as Grant_Type, role, table_name
from role_tab_privs rtp, dba_role_privs drp
where rtp.role = drp.granted_role
and table_name = 'TABLENAME'
union
select Grantee,'Direct Grant' as Grant_type, null as role, table_name
from dba_tab_privs
where table_name = 'TABLENAME' ;


How can I view what NLS settings are active for other session?

There is no simple way to achieve this. You can either do it by tracing the session or by using a login trigger. Here's an example of how to do it using a DB login trigger.

Logging table:

create table nls_session_parameters_log
(
    inserted_date    date,
    sid              number,
    username         varchar2(128),
    parameter        varchar2(30),
    value            varchar2(40)
);

Public grant and synonym:

grant insert on nls_session_parameters_log to public;

create public synonym nls_session_parameters_log for sys.nls_session_parameters_log;

Database login trigger:

create or replace trigger nls_log_dbtrig after logon on database
begin
  if user in ('PHIL') then
    insert into nls_session_parameters_log
    select sysdate, (select sys_context('USERENV','SID') from dual), (select user from dual),parameter, value
    from nls_session_parameters;
  end if;

  exception
    when others then
      null;
end;
/

Test:

SQL> conn phil/phil
Connected.
SQL> select count(*) from nls_session_parameters_log;

  COUNT(*)
----------
        17

SQL>

Obviously you'd be better off putting the table in an appropriate schema and granting on a per-user basis.

If you need help with another approach (session tracing), let me know.


Is the overhead of frequent query cache invalidation ever worth it?

You should just disable the query cache with

[mysqld]
query_cache_size = 0

and then restart mysql. Why would I suggest that ???

The Query Cache will always butt heads with InnoDB. It would be nice if InnoDB's MVCC would let queries be served from the query cache if modifications do not affect repeatable reads for other transactions. Unfortunately, InnoDB just does not do that. Apparently, you have a lot of queries that get invalidated rather quickly and are probably not being reused.

For InnoDB under MySQL 4.0, the query cache was disabled for transactions. For MySQL 4.1+, InnoDB plays traffic cop when allowing access to the query cache on a per-table basis.

From the perspective of your question, I would say that the justification of removing the query cache is not so much the overhead, but how InnoDB manages it.

For more information on how InnoDB interacts with the query cache, please read pages 213-215 of the book "High Performance MySQL (Second Edition)".

If all or the majority of your data is MyISAM, you could go with your original idea of using SQL_NO_CACHE.

If you have a mix of InnoDB and MyISAM, you will have to find the right balance for your application based on on how high your cache misses are. In fact, pages 209-210 of the same book point out reasons for cache misses :

  The query is not cacheable, either because it contains a nondeterministic construct (such as CURRENT_DATE) or because its
  result set is too large to store.Both types of uncacheable queries
  increment the Qcache_not_cached status variable.
  The server has never seen the query before, so it never had the chance to cache its result.
  The query's result was previously cached, but the server removed it. This can happen because there wasn't enough memory to keep it, because
  someone instructed the server to remove it, or because it was
  invalidated

and the root causes of high cache misses with few uncacheable queries may be :

  The query cache is not warm yet. That is the server hasn't had a chance to fill the cache with result sets.
  The server is seeing queries it hasn't seen before. If you do not have a lot of repeated queries, this can happen even after the cache
  is warmed up.
  There are a lot of cache invalidations.

UPDATE 2012-09-06 10:10 EDT

Looking your latest updated info, you have query_cache_limit set to 1048576 (1M). This limits any result set to 1M. If you retrieve anything bigger, it will simply not be cached. While you have have query_cache_size set to 104857600 (100M), this only allows for 100 cached results set in a perfect world. If you perform hundreds of queries, fragmentation will come about rather quickly. You also have 4096 (4K) as the minimum size result set. Unfortunately, mysql has no internal mechanism for defragmenting the query cache.

If you must have the query cache and you have so much RAM, you could execute the following:

SET GLOBAL query_cache_size = 0;
SELECT SLEEP(60);
SET GLOBAL query_cache_size = 1024 * 1024 * 1024;

in order to purge the query cache. You lose all cached results, so run these lines during off-peak hours.

I would also assign the following:

query_cache_size = 1G
query_cache_limit = 8M

That leaves 23G of RAM. I would raise the following:

innodb_buffer_pool_size = 12G
key_buffer_size = 4G

That leaves 7G. This should be adequate for OS and DB Connections.

Keep in mind that the key buffer caches only MyISAM index pages, while the InnoDB Buffer Pool caches data and indexes.

One more recommendation: upgrade to MySQL 5.5 so you can configure InnoDB for multiple CPU and multiple threads for read/write I/O.

See my earlier posts on using MySQL 5.5 in conjunction with accessing multiple CPUs for InnoDB

Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Oct 05, 2011 : Query runs a long time in some newer MySQL versions
Sep 20, 2011 : Multi cores and MySQL Performance
Jun 19, 2011 : How do I properly perform a MySQL bake-off?

UPDATE 2012-09-06 14:56 EDT

My method for clearing the query cache is rather extreme because it hoses cached data and forms a completely different segment of RAM. As you did point out in your comment, FLUSH QUERY CACHE (as you suggested) or even RESET QUERY CACHE would be better. For clarification, when I said "no internal mechanism," I meant exactly that. Defragmentation is needed and has to be done manually. It would need to be crontab'd.

If you do DML (INSERTs, UPDATEs, DELETEs) on InnoDB more often than on MyISAM, I would say remove the query cache altogether, which I said in the beginning.


Best info for the subject found on MSDN is here: Execution Plan Caching and Reuse
.
Quote from the MSDN article:

"Removing Execution Plans from the Procedure Cache

Execution plans remain in the procedure cache as long as there is enough memory to store them. When memory pressure exists, the Database Engine uses a cost-based approach to determine which execution plans to remove from the procedure cache. To make a cost-based decision, the Database Engine increases and decreases a current cost variable for each execution plan according to the following factors.

When memory pressure exists, the Database Engine responds by removing execution plans from the procedure cache. To determine which plans to remove, the Database Engine repeatedly examines the state of each execution plan and removes plans when their current cost is zero. An execution plan with zero current cost is not removed automatically when memory pressure exists; it is removed only when the Database Engine examines the plan and the current cost is zero. When examining an execution plan, the Database Engine pushes the current cost towards zero by decreasing the current cost if a query is not currently using the plan."

It's better to read the complete article, it's very well documented. See that it also has some links.


Are InnoDB tables harder to backup and slower?

Have a look at this link What are the main differences between InnoDB and MyISAM.?

After looking into this you will somewhat able to decide what to do.

From your question prospective :

1.InnoDB is slower at most things:

It is not true well designed InnoDB tables performs very well i have a 1TB of data with InnoDB table and is performing well

the reason for poor performance may be

  improper use of indexes

  fragmentation in tables

  improper setting of innodb variables like innodb_buffer_pool , log
  files,how the table space is configured

There may be a lot of reasons due which it doesn't perform well. Have a look how to tune InnoDB Performance

2.InnoDB is hard to backup

It is not also very hard to backup the InnoDB tables there might be time that you feel it is tough because of its table space/log files/frm files.As for a large MyISAM table you can directly copy the databases using the copy operation and it is quite fast.

due to that you might feel that backing up InnoDB is hard but it is not so.

There are proper backup techniques for InnoDB tables also, please see the following link for this Backing Up and Recovering an InnoDB Database.

3.InnoDB only shines when you have complex joins on tables with udpates and inserts.

No they can also perform well in all cases in which the MyISAM can.


How to show all system privileges for all users in Oracle database

"Retrieving all user privileges within Oracle can range from a simple task using a basic SQL query to an advanced script, depending primarily on how involved the roles and privileges are configured within the server."
https://chartio.com/resources/tutorials/oracle-user-privileges--how-to-show-all-privileges-for-a-user/


Start, stop, configure PostgreSQL through Mac terminal

The first error indcates the server is already running.  Have you tried connecting with psql or run the following to establish that?

pg_ctl -D /usr/local/cellar/postgresql/9.5.2/data status

If you know it is down, then for the second error with the logfile on startup, either reissue the command with an absolute path to where you can write a file:

pg_ctl -D /usr/local/cellar/postgresql/9.5.2/data -l /tmp/logfile start

Or if that doesn't work for you, try starting without the -l switch:

pg_ctl -D /usr/local/cellar/postgresql/9.5.2/data start

Without the switch, you still get the PostgreSQL logs in the pg_log directory under the $PGDATA - in your case /usr/local/cellar/postgresql/9.5.2/data/pg_log


In General

I would consider reading up on what information the master database contains and why you need to backup the system databases (master, msdb, ...).

Reference: Back Up and Restore of System Databases (SQL Server) (Microsoft Docs)

master Database

The master database contains the following information for each SQL Server instance:

  The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database. Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable.

(emphasis mine)

Reference: master Database (Microsoft Docs)

msdb Database

The msdb database contains the following information of a SQL Server instance:

  The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.

  For example, SQL Server automatically maintains a complete online backup-and-restore history within tables in msdb. This information includes the name of the party that performed the backup, the time of the backup, and the devices or files where the backup is stored. SQL Server Management Studio uses this information to propose a plan for restoring a database and applying any transaction log backups. Backup events for all databases are recorded even if they were created with custom applications or third-party tools. For example, if you use a Microsoft Visual Basic application that calls SQL Server Management Objects (SMO) objects to perform backup operations, the event is logged in the msdb system tables, the Microsoft Windows application log, and the SQL Server error log. To help your protect the information that is stored in msdb, we recommend that you consider placing the msdb transaction log on fault tolerant storage.

(emphasis mine)

Reference: msdb Database (Microsoft Docs)

Conclusion

If you backup the master database, then all the login information for the instance is safe. However, if you want to separately back up (as in: used to transfer logins to different instances, used to reset passwords) the SQL Logins of the instance, then you would have to apply a different solution.

Individual SQL Server Login Backup Solutions

Tool Reference List

dbatools.io (dbatools.io)

Copy-DbaLogin
Export-DbaLogin
Copy-DbaCredential
Copy-DbaAudit

Transfer Login Task (Microsoft Docs)

Added bonus

How to transfer logins and passwords between instances of SQL Server (Microsoft KB article)

The last reference is an interesting article on how to manually export the SQL Server logins and the hashed passwords in order to manually transfer them to a a new SQL Server instance. The article involves creating two stored procedures and then running one to create the information.

Here is the code from the refercned article to create the two procedures:

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

Once you have create the stored procedures run sp_help_rev_login to retrieve the information.

EXEC sp_help_revlogin

  The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

Reference: How to transfer logins and passwords between instances of SQL Server (Microsoft KB article)

Enjoy


PGPool II - Failover when current master node dies

Sounds like you might have incorrect values for your failover_command value. And Typically the failover script is run on pgpool server, not the database node.

failover_command = '/etc/pgpool-II/failover.sh %d %H %P /tmp/postgresql.trigger.failover [hostname of failover pgpool server]'

This configuration above is passing the new master candidate and an argument to my failover script which is executed on pgpool but ultimately promotes the master candidate (which is a hot standby) to a master using postgresql.


Finding road segements which are not split at intersection.(Query Optimization )

I just added st_intersects(a.geom,b.geom) at the start of second and third queries, as st_intersects is fast so it only calculates st_intersection where st_intersects returns 'True' hence performance of query is enhanced. Below is the query after modification.

SELECT a.id touched, b.id touching,  ST_Intersection(a.geom, b.geom)
FROM   pak_roads a, pak_roads b
WHERE ST_Crosses(a.geom, b.geom) and a.id < b.id and (a.MODE IS NULL
and b.MODE IS NULL )
and (a.grade_t IS NULL and a.grade_f IS NULL)
and (b.grade_t IS NULL and b.grade_f IS NULL) OR
-- "vertical" T bar, touching
(
-- The "vertical" start node touches, but not on either of the "horizonal" --nodes
st_intersects(a.geom,b.geom) and ST_Equals(ST_Intersection(a.geom, b.geom),
ST_StartPoint(b.geom))
AND NOT ST_Equals(ST_StartPoint(a.geom), ST_StartPoint(b.geom))
AND NOT ST_Equals(ST_EndPoint(a.geom), ST_StartPoint(b.geom))
) OR (
-- The "vertical" end node touches, but not on either of the "horizonal"
--nodes
st_intersects(a.geom,b.geom) and ST_Equals(ST_Intersection(a.geom, b.geom), ST_EndPoint(b.geom))
AND NOT ST_Equals(ST_StartPoint(a.geom), ST_EndPoint(b.geom))
AND NOT ST_Equals(ST_EndPoint(a.geom), ST_EndPoint(b.geom))
)

But I think there is still some room for improvement.


How can we publish Aurora (Postgres compatible) logs to Cloudwatch?

Full Disclosure: I am the product manager for Aurora PostgreSQL.

We are working to add support for the feature you noticed that is already available for RDS for PostgreSQL and for Aurora MySQL, to automatically copy database logs to Cloudwatch. While I don’t have a date to communicate, we plan to launch this feature soon - stay tuned!

Kevin Jernigan
Principal Product Manager, Amazon Aurora with PostgreSQL Compatibility


Can no longer connect to localdb after updating from Sql Server 2016 to 2019

check the
SQL Server Configuration Manager see manual

On the right side you see which server are running and how to call them in the brackets.
and see if it is called MSSQLLocalDB any longer


Following the comment by Akina:

  Edit dump file, remove all DDL statements from it. Restore data only - LOCK TABLESuserWRITE; INSERT INTOuserVALUES ... ; UNLOCK TABLES;.

I did it by taking only the data saved on the file and insert them into mysql.user like

INSERT INTO mysql.user VALUES(...)


You can achieve this by stopping the replication, which is the slon daemon. By stopping the daemon, sync event get accumulated on sl_log tables on master. When slon get started again, this accumulated logs will get synced to slave.

Hope it helps.


How to debug deadlocks in  SQL Server 2008 R2?

"Sometimes this deadlocks - two threads with different queries never ends"

What you are experiencing is blocking, not deadlock. When a deadlock condition occurs, SQL server automatically detects it and choose one of the queries as a victim and throws error 1205.

If you are really experiencing deadlock errors, turn on trace flag 1222. It will log the deadlock errors to the SQL server log.

If you are experiencing blocking, configure the "block process threshold" (http://msdn.microsoft.com/en-us/library/ms181150(v=SQL.90).aspx)

Also see http://www.mssqltips.com/sqlservertip/2429/how-to-identify-blocking-in-sql-server-2005-and-2008/


partitioning by datetime vs date vs int performance

You shouldn't get any additional performance gain by adding the extra column. If any, I doubt it will outweigh the additional storage cost required for the extra column.

There is one perk that you'll get from having a date only column and that is that you can have a date dimension. If this is for a data warehouse, I'd definitely recommend including.

If you decide to add the additional column (and assuming you're on SQL 2008 +), use the Date datatype. Int (formatted in YYYYMMDD) used to be the recommended format for partitioning, as it was cheaper (@ 4 bytes/row) than datetime (@ 8 bytes/row). Date is 3 bytes/row and is in a natural date format. Also, INT is a pain in the butt to query on as you have to add conversions in your' search arguments:

WHERE DateID = (CONVERT([int],CONVERT([char](8),getdate(),(112)),(0)))


Data mismatch on Subscriber Database in SQL server transactional replication

Please help me on how can i find for what could have been possibly going wrong where data does not match between both the DB.

You can use tablediff utility to find out the difference in data between your Publisher and Subscriber server.

Check How to run TableDiff utility for ALL replicated tables ? article by Chris Skorlinski on REPLTalk. This blog has all the required scripts that will help you out.

If you have 3rd party tools like redgate schema and data compare, then your life would become much easier.

As to find out what went wrong, you should check if

replication was changed to have filters applied or some column properties has changed to NOT FOR REPLICATION ?
distribution agent is working fine along with the log reader agent ?
Check where the data is missing -

Is data present in distribution database ?
Is data in distribution database and not in subscriber database ?


Problem solved, there were IO limits on that VM.

As for SQLIO, it was lieing on hyper-v:

https://social.technet.microsoft.com/Forums/azure/en-US/b8e1674d-f1c7-4e46-b1f1-09fb0d75d82e/strange-blazingfast-io-performance-inside-hyperv-vm?forum=winserverhyperv

https://serverfault.com/questions/287311/does-sqlio-lie-when-run-from-a-hyper-v-guest-on-a-vhd


Does a very large MDF file negatively impact latency? If so what is the appropriate size?

Only the size of MDF does not impact IO latency. Latency is related to your disk IO subsystem, its configuration and disk partition alignment.

To find out if disk subsystem is a bottleneck, use sys.dm_io_virtual_file_stats DMV or Capturing IO latencies for a period of time

Having multiple data files on separate LUNs - depends on number of CPU Cores will help to alleviate contention to some extent as well.

Look at the NUMA configuration of your server instance, since there is a single I/O thread and a single lazy writer thread for each NUMA node..

  We have peaks where latency (as measured by [Physical Disk\Avg Disk Sec read] on the drive where our data file is) goes up to 0.2 sec.

You should look into the autogrowth events and if there are frequent autogrowth events fired, you will see a performance hit. Make sure, you have configured autogorowth to a reasonable value - definitely away from Percent increase. Also, configure Instant file initialization, so that data files can leverage its awesomeness.


Select repeating number x times and then increment

One solution using simple integer arithmetic on a table of numbers. This example uses an efficient way to generate a sequence on the fly, in practice you would probably have a permanent Numbers table.

DECLARE
    @z bigint = 10,
    @x integer = 4;

-- Itzik Ben-Gan's row generator
WITH
  L0   AS (SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS n FROM L5)
-- Main logic
SELECT TOP (@z)
    # = ((N.n - 1) / @x) + 1
FROM Nums AS N
ORDER BY N.n;

Output:

╔═══╗
║ # ║
╠═══╣
║ 1 ║
║ 1 ║
║ 1 ║
║ 1 ║
║ 2 ║
║ 2 ║
║ 2 ║
║ 2 ║
║ 3 ║
║ 3 ║
╚═══╝

Execution plan:


SQL Server 2012 - where is the table in dbo?

Oftentimes this problem stems from one of two scenarios:

Either you created the table in the wrong database, or you never created the table at all (user error or just forgetfulness. It happens to us all).

What you need to do is look through each of your databases for your table.  You can do this through the sys.tables system catalog view in your instance's databases. Usually in SSMS people forget to change the database context from master to their desired database, causing DDL to execute in master. Regardless, master is a good first place to look.


This should not be possible, from an architectural standpoint:
The non-clustered index has 220 rows. Even if you have only one row per page in your index, you would have only 1.7 MB (plus a little bit in the non-leaf levels, but that is marginal). So, something is "not normal" here - this should be impossible.
Are you sure you aren't looking at the size of the data, and that the table is a heap table? SQL Server has a well known issue with not free up space for heaps. That would explain it. The solution is to not have a heap. I see, however that you don't have a heap, since you mention that the clustered PK.
A very far-fetched theory is that SQL Server's meta-data doesn't reflect reality regarding the space used by the index. We had that prior to SQL Server 7.0 (or was it 2005 that this was fixed?). We refreshed the meta-data using DBCC UPDATEUSAGE. You can of course use this, just in case. It doesn't explain bad performance, but it is a quick thing to verify. I.e., the theory is that you are exposed to some code-path in SQL Server where this issue (non-correct space usage meta-data) isn't handled.
If this was me, I would first rule out the meta-data aspect. Look at space usage using several methods, just to verify that they agree on the size. And last resort would probably be to see what is really there using commands such as DBCC PAGE, sys.dm_db_database_page_allocations() and such...
(Come to think of it, row versioning could potentially explain some extra space usage, but that would be in tempdb...)


How to get paged resultset from a table?

Quick and dirty example for smaller data sets.  For larger data sets I definitely recommend checking out the link Aaron Bertrand provided: http://www.sqlservercentral.com/articles/paging/69892/

declare @pagewidth int, @pagenumber int

select *
from
    (
        select
            *,
            row_number() over (order by PAGINGCOLUMN/S) as cnt
        from datatable
    )a
where cnt > (@pagenumber - 1) * @pagewidth
    and cnt <= @pagenumber * @pagewidth


Remove Local RAID Drive from MS SQL Windows Cluster

SSDs are great in general, but they are much better suited to read heavily applications, while tempdb is a mix of reads / writes and the nature of tempdb generates a consistent churn which is not good for an SSD. But it sounds like you've already gotten your tempdb off the SSD, so that step is already done.

I've not tried swapping out a local SSD for a SAN drive, but I've been involved in a migration which did that process in reverse (swapped a SAN drive for a local SSD) We failed over to a virtualized server during the process (the main server was a physical server) but it was very straightforward. We might have been able to swap in the SSD and done the entire process in a 30 minute window and skipped the fail over altogether had we known. But the policy at my current work is that any downtime longer than 30 minutes needs to be a failover.

So TLDR, it was very easy, but I wasn't the person responsible for doing the physical drive swap, only for failing over SQL Server, then doing the work to utilize the new SSD, which was reassigned the drive letter that we used for data originally.