How do I check how many rows a query will affect before executing it? I'm writing an application to interface with my database (SQL Server 2014 SP1 Express), and there are two sections of the application that worry me slightly. The first involves an UPDATE:

UPDATE Jobs
SET JobStatus = 'Running' --there's also an UPDATE where this is set to 'Completed'
WHERE (ExperimentID = @currentID);

And a DELETE:

DELETE from Simulations
WHERE (ExperimentID = @currentID);

These seem like fine statements at a glance, but if there's something wrong with how @currentID is passed from my application to the DB, I could possibly lose all information about which Jobs have been run, and more gravely, all of my simulation data.

How do I protect these queries from deleting/updating more than @n rows? The UPDATE should really only update one row at a time, so I'd like that to be a restriction. There's also a column in the Simulations table that could be used to restrict how many the DELETE should be getting rid of, by doing:

SELECT MAX(CycleCount)+1 AS numRows
FROM Simulations
WHERE ExperimentID = @currentID

So what do you think? How best do I sanitize these queries to prevent them from doing damage in the event that my application fails?

There are three tables in this DB: Jobs, Parameters, and Simulations. ExperimentID is a primary key for both Jobs and Parameters, and they are both the same size (each job has a corresponding set of parameters). There's more detail about Parameters in my post history if you're interested.

The Simulations Table stores results from a simulation at each step of the simulation. So the primary key is then formed not just by ExperimentID (as theres many many steps per simulation), but by the compound of ExperimentID and CycleCount.

I'm trying to account for situations where something gets corrupted in transfer (highly unlikely, but you never know), and situations where a simulation terminates prematurely (loses connection with DB, computer loses power, etc). ExperimentID in both Jobs and Parameters is determined by IDENTITY(1,1). Hope this clears up a bit of what I'm hoping to accomplish here.

@currentID is programmed as an integer. It is pulled initially from the database using:

SELECT MIN(ExperimentID) FROM Jobs WHERE JobStatus = 'ToRun'

If ExperimentID is the primary key or has unique constraint/index, there is no need to limit the number of rows updated since there will never be more than one row updated.  If ExperimentID is not unique by itself, I suggest you add additional criteria to identify the desired row.  The TOP approach you provided will limit the number of rows updated for non-unique values but the one updated will be arbitrary unless your order by additional columns such that the updated row deterministic.

To validate no more than the expected number of rows are modified, one method is to perform the DML in a transaction and rollback if the number of rows is greater than expected.  Below is a parameterized example of this technique:

SET XACT_ABORT ON;

BEGIN TRY

    BEGIN TRAN;

    UPDATE Jobs
    SET JobStatus = @NewJobStatus
    WHERE ExperimentID = @currentID;

    DECLARE @RowCount int = @@ROWCOUNT;

    IF @RowCount > @MaxRowCount
    BEGIN
        RAISERROR ('Attempt to update %d rows, more than the %d row(s) allowed.  Action not performed.', 16, 1, @RowCount, @MaxRowCount);
    END;

    COMMIT;

END TRY
BEGIN CATCH

        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;

END CATCH;

I figured it out. You can use TOP to limit how many rows get acted upon:

UPDATE Jobs
SET JobStatus = 'Running' --there's also an UPDATE where this is set to 'Completed'
WHERE ExperimentID IN
    (SELECT TOP (1)
     FROM Jobs
     WHERE ExperimentID = @currentID
     ORDER BY ExperimentID ASC);


Very slow update with subquery (different tables) I run this query to update "domains" table based on "log" table:

UPDATE domains
SET domains.mx_collected = 0
WHERE domains.domain IN
(SELECT log.mail as domain_name FROM log WHERE
`time` >= DATE_SUB( CURDATE( ) , INTERVAL 30 DAY ) AND
`time` <= DATE_SUB( CURDATE( ) , INTERVAL 15 DAY )
group BY domain_name
HAVING count(*) > 200)

While the subquery itself runs very fast (0.6 sec) with a result set of only 6 items the update never ends (not after hours). But if I run the update-query with a item list instead of a subquery it successfully ends after 0.02 seconds:

UPDATE domains
SET domains.mx_collected = 0
WHERE domains.domain IN
("0815.ru","123.com","123mail.org","126.com","139.com","13hotmail.com")

Question is: why?

The subquery will be executed for each row from the outer query, and no index will be used. In addition, 0.6 sec is "relatively" long. For example, if you have 1000 rows in domain table, then expect more them 600 sec (10 minutes).

In the second query, MySQL is able to use the index on 'domain'. This is a feature of using "IN" instead of subquery.


Can I install SQL Server Management Studio locally? Can I install SQL Server Management Studio on my desktop to access a database on a SQL Server 2012 instance?

If so, where can I find the installer? Google searches only return 'express' versions whereas, I am looking for the full SQL Server Management Studio.

If you want the shiny new 2016 monthly releases of Management Studio, which include a handy check for updates mechanism, negates the need for a full setup program and download (since it only retrieves the components you need), as well as a release cycle completely independent from SQL Server, go here (and you can read about it here and here).

If you use this new web installer, much of the below no longer applies. If you want to use previous versions (currently 2012 SP2 and 2014 SP1), they've made it easier to find and download (see this page), but much of the below will still apply.

Well, if you have a legal copy of SQL Server, you should be able to install Management Studio from that media wherever you need to manage SQL Server from.

That said, the Express version of Management Studio, since 2012 Service Pack 1, has been a fully functional product with absolutely no differences (except you can download it for free instead of finding your licensed media). I would recommend downloading the 2014 version, because why use an older version that will be out of support sooner? My philosophy is that SSMS should always be >= the highest engine version you need to manage.

Anyway, I found the link by searching - the hint to not jumping through hoops set up by Microsoft marketing or MSDN is to look for the page with download in the URL:

This brings me here, where I click Download:

Then pick either the 32-bit or 64-bit version, depending on your platform, and click Next:

And success. I even get handy tips for installation if I need them.

If you really, really, really want to use the 2012 version instead of the 2014 version, well first I'd be interested why, but the same process will work, just use this link instead. Note that the 2012 version is a 950 MB download vs. 684 MB for 2014.

Since a lot of people get confused on how to install JUST SSMS with this installer, here are some other things you'll want to follow once you launch the .exe. First it will open the installation center. You want Installation > New SQL Server stand-alone [blah blah]...

Clicking that link will launch a new wizard, and leave the installation center open. Leave it open (see the end of this post for why). On the new screen, you may have product updates offered, and you definitely want to select them if so (currently this should only happen for 2012 SP1 or 2014, since 2012 SP2 was not affected by Security Bulletin MS14-044).

Next, and this is where most people get confused, you want to choose Perform a new installation - even though you are not trying to install an actual instance of the SQL Server database engine.

Then, you want to select both Management Tools options, basic and complete. From here you should be ok on your own.

Regarding the installation center window above, you need to leave the "SQL Server Installation Center" window open during setup, or it will fail with an error like this (the actual folder name is some random GUID-based thing):

  SQL Server Setup has encountered the following error: Could not find file 'c:\1d3c284ce0e72239e2f4e51c802d\MediaInfo.xml'

I filed a Connect item about it, but so far it remains active.

Its very difficult to find SQL Server management studio which is not express version because Microsoft only ships complete version with installable of Licensed version. If you have setup of any SQL Server 2012/2014 licensed version you can run the setup and install SSMS by selecting client tools. There is no addictional license for SSMS.

Other good point is you can use SQL Server 2012 express Management studio with SP1/SP2 to get all functionalities of SSMS which is shipped with licensed version. So you dont need to worry about simple SSMS an express SSMS2012 with SP1/SP2 would do all jobs for you. I would suggest you to install SQL Server 2012 SP2 management studio using This Link. The download would be SQLManagementStudio_x64_ENU.exe assuming you have 64 bit machine. You can manage previous versions(till 2005) as well as SQL Server 2014 using SSMS 2012 express management studio


In MySQL, does the order of the columns in a WHERE clause affect query performance? I am having performance issues on certain database queries that have large possible result sets.

The query in question, I have three ANDs in the WHERE clause

Does the order of the clauses matter?

As in, if I put the ASI_EVENT_TIME clause first (since that would remove the most of the results out of any of the clauses.

Will that improve the run time on the query?

QUERY:

SELECT DISTINCT  activity_seismo_info.*
FROM `activity_seismo_info`
WHERE
    activity_seismo_info.ASI_ACTIVITY_ID IS NOT NULL  AND
    activity_seismo_info.ASI_SEISMO_ID IN (43,44,...,259) AND
    (
        activity_seismo_info.ASI_EVENT_TIME>='2011-03-10 00:00:00' AND
        activity_seismo_info.ASI_EVENT_TIME<='2011-03-17 23:59:59'
    )

ORDER BY activity_seismo_info.ASI_EVENT_TIME DESC

EXPLAIN of query:

+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+
| id | select_type | table   | type  | possible_keys             | key          | key_len | ref  | rows  | Extra                       |
+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | act...o | range | act...o_FI_1,act...o_FI_2 | act...o_FI_1 | 5       | NULL | 65412 | Using where; Using filesort |
+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+

Using:

PHP 5.2

MySQL 5.0.51a-3ubuntu5.4

Propel 1.3

Symfony 1.2.5

I do not think so. The query optimizer should be clever enough.

You can try rearranging the WHERE clauses and see that EXPLAINS tells you the same in each case.

About what can be done to optimize this query: Is there an index on ASI_EVENT_TIME ? (this is the most crucial I think for this query as you also sort the results using it).

Are there indexes on the other two fields (ASI_SEISMO_ID and ASI_ACTIVITY_ID)?

It would be helpful if you posted the table structure.

ANY field that is used in your WHERE/HAVING clauses and has high selectivity (the number of unique values / the total number of records > 10%~20%) MUST be indexed.

So, if your ASI_EVENT_TIME column has many possible values, first index them all. Then as @ypercube told, try rearranging them and see what EXPLAIN tells you. Should be all around the same.

Additionally, want you to have a look at Indexing SQL LIKE Filters. Though it is not what you need an answer for, but you will still learn about how indexing works under the hood.

*Edit:
Refer to the links provided below in the comments to learn more about indexing.


Rebuild Very Large Primary Key Index I have a SQL database that is hosted on Azure. The problem is that the size is getting out of control, I can see up to 99% fragmentation in the Primary Key clustered indexes.

I'm able to rebuild all other indexes with online=on option and it won't affect performance. The size of one of the PK Clustered indexes is greater than 200GB, and for this one a REBUILD...WITH (ONLINE=ON) causes locking.

We do have users from all timezones accessing the site so really, I'm unable to find a time where I can rebuild the index offline.

What is the best strategy to rebuild large indexes without having a downtime in the site?

I believe reorganize won't help since fragmentation is 99%. The problem is that the table gets locked even with online. The main problem is that the index is greater than 200GB. The primary key is an integer.

Even though it's a bit late, I'm going to field a response with hope that it helps or at least spurns some additional ideas/commentary on this issue because I think it's a good question.

First, and I don't know if you're doing this or not, but please don't assume that high fragmentation levels on the index are always going to cause poor performance.  Stale statistics (e.g. sys.dm_db_stats_properties) and high amounts of white space per page (i.e. avg_page_space_used_in_percent column in sys.dm_db_index_physical_stats dmv) hold more relevance regarding performance issues than fragmentation alone.  Yes, highly fragmented indexes will generate more read-aheads and you typically do see stale statistics and higher levels of white space per page coupled with fragmentation, but fragmentation isn't directly tied to query plan optimizations nor how much memory loading the index from disk will actually consume.  Query plans are affected by statistics and your memory footprint bloats with more white space.  For instance, an index that is 99% fragmented but has less than 5% avg. white space and up-to-date statistics is likely not causing you drastic performance issues as compared to either a bad execution plan as a result of stale statistics or constant paging of an index that's too big to fully fit in memory because there's a significant amount of white space present per page.

If fragmentation is truly an issue, you can reduce it, ONLINE, by issuing an ALTER INDEX ... REORGANIZE statement as identified by Dan Guzman in the comments.  This won't create as streamlined an index as a REBUILD operation will, but it will reduce your fragmentation.  The key here is to identify windows of lower usage on your database and run it then.  This could be 15 minutes or multiple hours, obviously the longer the better, but the key here is this operation doesn't rollback and retains any progress made even if you kill it mid-execution.

If, in a perfect world where your fragmentation was eliminated, would it make more sense to utilize partitioning on this table?  Azure SQL Database does allow for table partitioning and Microsoft has a great article outlining some Partitioning strategies for Azure SQL Database.  If your data is non-volitile, partitioning it may help reduce maintenance needs, and if coupled with Table Compression, you may even be able to reduce your overall storage footprint as well.  Alberto Murillo's earlier answer alludes to utilizing Horizontal Partitioning based on a data region, and this approach may help create some maintenance windows for you as your data would be more regionally specific instead of global.

Transitioning to a partitioned table won't be easy with your current absence of maintenance windows, but you may be able to utilize an approach outlined by Maria Zakourdaev which uses Partitioned Views over the top of your current table and a new partitioned table to start partitioning future data.  As time goes on (and hopefully your old data is purged), you can eventually transition fully over to the partitioned table.  Again, I don't know your data or application, but maybe this approach is something you can employ.

Try to use sharding to distribute data of your database geographically. You then will be able to identify different maintenance windows for each geographic location, and the time to make maintenance will be shorter. This will also improve performance. You can learn more on this article. Do not wait for the database to get bigger.

With big databases and users connected 24 x 7, you need to use index reorganize and update only statistics that need to be updated (sp_updatestats) to minimize the time needed for maintenance and the impact to users.

Hope this helps.


"SELECT TOP N" returns the wrong results Supposing I have a table foo similar to:

ident int, primaryKey, auto_increment
someNumber int
statusId int
...

where the someNumber column is, except for a few special cases, equal to ident.  (in those special cases, someNumber is always equal to a previous ident).

I'm seeing problems which give the appearance that a query such as SELECT TOP 10 ident, someNumber FROM foo WHERE statusId=@desiredStatusId ORDER BY someNumber will return the wrong 10 records.  The records all have the correct statusId, but randomly there is one whose someNumber is way off.  For eaxmple:

ident        someNumber
-------      ----------
1234567      1234567
1234700      1234700
1234568      1234568
1234569      1234569
1234570      1234570
...          ...

What could be causing this query to intermittently return the wrong results?

A corrupted index could do it.  Have you run DBCC CHECKDB against that database?

looks like some one made an error while dml statement , other wise it shouldnt have given you that error. if you do a select statement and check the order of the number after 1234567 is 1234700 , then it is definitely insert statement error.


How can I have SQL Server email me the error details when a job fails? SQL Server lets you configure a job to send email alerts when it fails.  This is a simple and effective way to monitor your jobs.  However, these alerts do not include any detail--just a success or failure notice.

If a job fails, this is what a typical alert email will look like:

JOB RUN:        'DBA - Consistency Check Databases' was run on 8/14/2011 at 12:00:04 AM
DURATION:       0 hours, 0 minutes, 0 seconds
STATUS:         Failed
MESSAGES:       The job failed.  The Job was invoked by Schedule 2 (Nightly Before
                Backup 12AM).  The last step to run was step 1 (Check Databases).

To determine the cause of the failure, you have to navigate to the instance in SQL Server Management Studio, find the job, and view its execution history.  In a large environment it can be a pain to have to constantly do this.

The ideal alert email would include the failure reason upfront and let you get straight to working on the solution.

I am familiar with this solution to this problem.  Does anyone have any experience with it?  Its drawbacks are:

you have to add a new step every job you have, and
you have to pray that no-one messes up the alert proc, spDBA_job_notification

Has anyone come up with a better solution?

Something you might do that is just a thought, throwing ideas out...

Create a single job that periodically checks the job table in msdb to see if any jobs show as failed, that can be done with a good T-SQL query. Then you could go into the sysjobsteps table and see if an output log is set for the job. Have a stored procedure send an email attaching that file to it. You would be able to see exactly what the job did from beginning to failure without having to touch the server.

Then could also have PowerShell script check the event log for errors. It allows you to filter down a pretty good bit to get exactly what message types you are looking for. You could set that up as a SQL Agent job to run periodically. Then in the PowerShell script use the email cmdlet to send the message if it finds one.

Far fetched ideas here, just some I thought about.

Yet another answer to this really old question, this stored procedure seems to work very well.
https://karaszi.com/mailafterjob-send-email-after-agent-job-include-ouput-files
Tibor has included a great summary of the stored procedure, description of parameters and example.


Availability Group how to determine last Failover time I'd just like to know if there is any way to query when the AG group failed over.

eg - this is the Primary Replica now, but I'm pretty sure it was the secondary yesterday?  How can i find when the failover took place

is there something specific in the Logs I should be looking for, or is there a tsql script to use?

You can use following PowerShell script to get last failover time

    Get-winEvent -ComputerName ListnerName -filterHashTable @{logname ='Microsoft-Windows-FailoverClustering/Operational'; id=1641}| ft -AutoSize -Wrap

This the code I use to determine which server is the Primary node and which one is the secondary node.  I use this in every job on my server.

NOTE: this is for a 2-node AOAG.  Adjust it as needed to handle multiple-server AOAGs.

declare @PrimaryServer varchar(50),
        @SecondaryServer varchar(50)

set @SecondaryServer = (select ar.replica_server_name
                from master.sys.availability_replicas ar
                join master.sys.dm_hadr_availability_replica_states ars
                on ar.replica_id = ars.replica_id
                where role_desc = 'SECONDARY')

set @PrimaryServer = (select ar.replica_server_name
                from master.sys.availability_replicas ar
                join master.sys.dm_hadr_availability_replica_states ars
                on ar.replica_id = ars.replica_id
                where role_desc = 'PRIMARY')

--If this is the secondary server, do whatever
If @@SERVERNAME = @SecondaryServer
    begin
        --Put your code here for Secondary Server actions
    end
Else
    begin
        --Put code here for Primary Server actions
    end

--End If


/usr/libexec/mysqld: Normal shutdown, but my team doesn't do that? One of my databases has been shutting down abnormally, but the log said "normal":

130422 13:23:01 [Note] /usr/libexec/mysqld: Normal shutdown

130422 13:23:01 [Note] Event Scheduler: Killing the scheduler thread, thread id 379021
130422 13:23:01 [Note] Event Scheduler: Waiting for the scheduler thread to reply
130422 13:23:01 [Note] Event Scheduler: Stopped
130422 13:23:01 [Note] Event Scheduler: Purging the queue. 41 events
130422 13:23:01 [Note] Error reading relay log event: slave SQL thread was killed
130422 13:23:01 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
130422 13:23:01 [Note] Slave I/O thread killed while reading event
130422 13:23:01 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.002426', position 107
...
130422 13:23:04  InnoDB: Starting shutdown...
130422 13:23:04  InnoDB: Waiting for 201 pages to be flushed
130422 13:23:08  InnoDB: Shutdown completed; log sequence number 6942784551767
130422 13:23:08 [Note] /usr/libexec/mysqld: Shutdown complete

There is no related error or stack trace in the mysqld.log, just some errors:

No data - zero rows fetched, selected, or processed

from Event Scheduler.

I'm pretty sure my team doesn't stop it (via init script or mysqladmin shutdown, ...). grep through the last binlog file, I didn't see any SHUTDOWN statement.

Is there any other causes? Let me know if you need more information.

PS: mysql  Ver 14.14 Distrib 5.5.28, for Linux (x86_64) using readline 5.1

UPDATE at Mon Apr 22 18:23:33 ICT 2013

Searching through the source code, I found this:

$ grep -lir 'normal shutdown' mysql-5.5.28
mysql-5.5.28/scripts/mysqld_safe.sh
mysql-5.5.28/Docs/mysql.info
mysql-5.5.28/sql/share/errmsg-utf8.txt

$ grep -lr 'ER_NORMAL_SHUTDOWN' mysql-5.5.28
mysql-5.5.28/Docs/mysql.info
mysql-5.5.28/sql/mysqld.cc
mysql-5.5.28/sql/share/errmsg-utf8.txt

mysql-5.5.28/sql/mysqld.cc

  if (sig != 0) // 0 is not a valid signal number
    my_sigset(sig, SIG_IGN);                    /* purify inspected */
  if (sig == MYSQL_KILL_SIGNAL || sig == 0)
    sql_print_information(ER_DEFAULT(ER_NORMAL_SHUTDOWN),my_progname);
  else
    sql_print_error(ER_DEFAULT(ER_GOT_SIGNAL),my_progname,sig); /* purecov: inspected */

Looks like that there is no other reason for this message can be logged into the log file. One possibility I'm thinking is someone (who has SUPER privilege) turned off the binary log then run SHUTDOWN and close the db connection.

PS: In this bug report, there is one person also got this problem on 5.5.24.

Linux's Viewpoint
You should run this Linux command
history | grep mysqladmin

This will let you see if anyone ran a shutdown from within the server. Note that this will not let you see remote mysqladmin shutdowns. Perhaps running tcpdump and locating the word mysqladmin or shutdown might help.
MySQL's Viewpoint
Shutdown commands do not exist from command line utilities. Therefore, binary logs will not have shutdowns recorded. You are on the right track when you said;

One possibility I'm thinking is someone (who has SUPER privilege) turned off the binary log then run SHUTDOWN and turned it on again.

Look over the users with that privilege. Run this query:
SELECT user,host FROM mysql.user WHERE shutdown_priv='Y';

You may see something like this:
mysql> select user,host from mysql.user where shutdown_priv='Y';
+-------+--------------+
| user  | host         |
+-------+--------------+
| root  | localhost    |
| root  | 127.0.0.1    |
| root  | ::1          |
| root  | 10.240.163.% |
| root  | 10.48.19.%   |
| user1 | %            |
+-------+--------------+
6 rows in set (0.00 sec)

mysql>

You will see all the users that have shutdown privilege. SUPER privilege does not have the shutdown privilege. There is a separate privilege called SHUTDOWN.
You can quickly revoke that privilege. For example, note the user user1@'%'. It has remote shutdown privileges. You can yank it with
REVOKE SHUTDOWN ON *.* FROM user1.'%';

or
UPDATE mysql.user SET shutdown_priv='N'
WHERE user='user1' AND host='%';
FLUSH PRIVILEGES;

That takes care of the remote shutdown privileges.
You should also address securing user connections by removing SHUTDOWN privilege from every user that does not need to have it. Perhaps you can do this:
UPDATE mysql.user SET shutdown_priv='N'
WHERE CONCAT(user,'@',host) NOT IN
('root@localhost','root@127.0.0.1');
FLUSH PRIVILEGES;

That way, only root@localhost and root@127.0.0.1 can do mysqladmin shutdown within the localhost. I would definitely have the root@127.0.0.1 on hand because there are times when mysqld loses connection to the mysql.sock file and prevents root@localhost from connecting to issue a shutdown. With the root@127.0.0.1, you can issue this:
mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp

to issue a mysqladmin shutdown without needed the socket file.
Give it a Try !!!

I had same problem and I know that reason.
(Ubunut 18.04)
See syslog.
May 20 06:27:28 saw-ac-1 systemd[1]: Starting Daily apt upgrade and clean activities...
May 20 06:27:39 saw-ac-1 systemd[1]: Stopping MariaDB 10.1.47 database server...  <===========================
May 20 06:27:42 saw-ac-1 kernel: [20186037.318096] traps: GUI_OAMP[23214] general protection ip:7f8a850989fd sp:7ffe4d677630 error:0 in libc-2.27.so[7f8a85001000+1e7000]
May 20 06:27:42 saw-ac-1 systemd[1]: Stopped MariaDB 10.1.47 database server.
May 20 06:27:45 saw-ac-1 systemd[1]: Reloading.
May 20 06:27:50 saw-ac-1 systemd[1]: Reloading.
May 20 06:27:54 saw-ac-1 systemd[1]: Started Daily apt upgrade and clean activities.


select all rows with a minimum value In Sqlite 3 I'm trying to figure out how to select rows based on a minimum value.  I think that I'm limited by not knowing enough of the related terminology to effectively search google.

The table looks like:

num         text        num2
----------  ----------  ----------
0           a           1
0           a           2
1           a           3
1           b           4

I want to get the rows where num2 is 1, 2, and 4.  I want to do the selection based on the minimum value of num for each unique value of the text column.

So, for text = 'a', the minimum value of numis 0, so I want rows 1 and 2.  For text = 'b', the minimum value of num is 1, so I want row 4.

Using various combination of group by, I'm able to get either rows 1 and 2 or rows 1 and 4.  I feel like I'm missing a SQL component that would do what I want, but I haven't been able to figure out what it could be.

What is the proper way to do this type of query?

Possible Solution

I've found a way to do this.  I'm not reputable enough to answer my own question, so I'm doing the update here.  I'm not sure if it is always correct or what the efficiency is like.  Any comments are welcome.

I used a compound select statement, where one query finds the minimum value of num for each unique value of text:

sqlite> select num, text from t group by text having num = min( num );
num         text
----------  ----------
0           a
1           b

Then I joined this with the full table to get all rows matching these two columns.

sqlite> with u as
      ( select num, text from t group by text having num = min( num ) )
        select t.* from t join u on t.num = u.num and t.text = u.text;
num         text        num2
----------  ----------  ----------
0           a           1
0           a           2
1           b           4

As you have seen, a simple GROUP BY will not work because it would return only one record per group.

Your join works fine.
For a large table, it will be efficient only if there is an index on the join columns (num and text).

Alternatively, you could use a correlated subquery:

SELECT *
FROM t
WHERE num = (SELECT MIN(num)
             FROM t AS t2
             WHERE t2.text = t.text);

SQLFiddle

When being executed, this query does not require a temporary table (your query does for the result of u), but will execute the subquery for each record in t, so text should be indexed. (Or use an index on both text and num to get a covering index.)

Shouldn't be this query exactly what you need?

select min(num), text, num2 group by text, num2


Can a database's files be copied while a database is online? I'm working on setting up a development copy of a production database on SQL Server 2008 R2 SP1. The live database is getting lightly used by two developers for read only queries currently, but the new database will have updates done on it as well.

Since the database is 2.1TB and took a total of 3 days to restore and update to the latest build we need for testing, my original plan was to create a new set of backup files and then restore from those files. This would allow me to create the development copy of the database on the same SQL instance and machine, without having to take the current database offline.

However, in order to save a couple of those days, I was thinking that it might be a good idea to just copy over the physical database files and attach the new copy of the database. Unfortunately when I try to copy I get an error referring to the lock that SQL Server puts on those files.

Since I can't take the database offline for anything but transferring the log files (I can finish this before people get in in the morning), is there any way I can copy the live database files without putting the database into an offline state? Or should I wait until people go home to do that?

Why does it take 3 days to backup / restore a 2.1 TB database? If it is all about moving the backup, I'd suspect copying the MDF/LDF files would actually be slower since at least the backup could be compressed. And if it's just about restoring, then writing the copies of the file there shouldn't be any faster than the restore process itself - make sure you have instant file initialization enabled. Or get faster I/O on the secondary system.

Anyway, no, you can't copy the MDF/LDF files while the database is online, you'd have to take it offline to do so. And this is the absolute LEAST safe way to copy a database. If anything happens to the files while they're detached/offline, you now have zero copies of your database.

I would suggest looking into ways to make the backup/restore faster - whether it be ensuring you're compressing your backups and transferring the files in the best way possible, using the best I/O subsystem available, getting better I/O, making sure that IFI is enabled, etc.

Another option to consider is round robin log shipping - assuming production is in full recovery and you are taking regular log backups, you can perform log shipping to one database while developers work on another, and when you get to a stable point, restore with recovery and have the developers switch, taking their recent changes with them. Now re-initialize log shipping on the copy they just stopped working on, and it will be ready for them at the next "stable" cut-over, with no waiting.

Can a database's files be copied while a database is online?

Yes.

All you have to do is set the database to read-only:

Run:

USE [master]
GO
ALTER DATABASE [Database] SET READ_ONLY WITH NO_WAIT
GO

Copy the database files - MDF,LDF, etc. to their new destination
Once copy is done, you can detach and re-attach on the new location.  Just make sure to reset the read-only option and that you are using the same user account when manipulating these files.  Also, you might want to delete the old files after the move.

Another method

After the files are successfully copied (step 2 above):

You can set the database to: Single_User mode then:

Run:

USE [master]
GO
ALTER DATABASE [Database] SET  Single_USER WITH NO_WAIT
GO

D:\ being the new location

ALTER DATABASE Database MODIFY FILE
(NAME = DATABASE, FILENAME = 'D:\DATABASE.mdf')
GO
ALTER DATABASE OHDSI MODIFY FILE
(NAME = DATABASE_LOG, FILENAME = 'D:\DATABASE_LOG.ldf')
GO

Make sure the data files in the new location (D:\) has full access to the service account that is running SQL Server.
Restart the SQL services
Run:

USE [master]
GO
ALTER DATABASE [Database] SET  Multi_USER WITH NO_WAIT
GO

Done!


Are there standard SQL functions with side effects? Do the SQL standards define functions with side-effects?

For example, do they have functions for writing into files* or to update values in certain columns of a table when you do something like

SELECT myfunction(params...);

I've seen these sometimes, but I am just curious if the SQL standards do the same.

* This is not a question about PostgreSQL specifically. I'm only using the side-effects examples I see in PostgreSQL.

You have a few different questions in here.

Q: What are ANSI standard SQL functions?

ANSI standard functions are things like AVG, COUNT, MIN, MAX. They're covered in the 1992 ANSI standard, but that's one heck of a dry, boring read.

Q: Do ANSI standard SQL functions change data in the database?

No. You can use them to change data - for example, I can say:

INSERT INTO dbo.MyReport SELECT MAX(SalespersonRevenue) FROM dbo.Sales

But by themselves, just the use of a AVG, COUNT, MIN, MAX, etc shouldn't change data permanently inside your database.

Q: Does the ANSI standard allow me to write my own functions?

Yes, but the exact implementation varies from vendor to vendor. The functions you write may conform to the ANSI language standard, but what you do inside your function can be horrifically awful, like creating side effects.

When discussing intended behavior, it's possible to get a cross-platform answer.
When discussing side effects, it is not.

Q: Can I create my own function to write data?

Why sure, if you're creative. I'm a Microsoft SQL Server guy, so I'm going to focus on that platform. Books Online's function page says:

  User-defined functions cannot be used to perform actions that modify the database state.

To which I say:

  You're not my real dad.

So here's how I'd break the rules. Warning: very bad ideas follow.

In your function, query a new table specially created for this evil purpose, and then create something that watches the table for select statements, and then fires an action (Extended Events, auditing, or a Profiler trace). You can hook together a Rube Goldberg sort of contraption to perform work based on those select statements.
In the function, call CLR code - heck, you can even call a web service. That web service could very well push data back into your own database.
In the function, call xp_cmdshell and do something through the command prompt. (HT @AaronBertrand in the comments.)

All of these examples have huge drawbacks in the form of performance and transactional consistency. You just asked if it could theoretically be done, and the answer there is yes. I wouldn't ever use either of those in my own code - I'd step back and ask, "What's the business goal I'm trying to achieve here, and is there a way I can do it to achieve performance and transactional consistency?" If you'd like specific advice on those, I'd ask a separate Stack question with specifics.

I can only speak definitively regarding SQL Server, and it appears this is not consistent across all database implementations. But in SQL Server, functions may not produce side effects. This is a hard and fast rule I've tried to circumvent a number of times with no success.

If you are thinking about functions in the general sense, there are SQL modules that allow side effects (for example stored procedures) but user-defined functions do not.

There is a saying "Clever Solutions Don't Scale" which is especially true with Microsoft products. I've seen a number of clever workarounds in early versions of SQL Server that became obsolete in later versions, because MS added them as true features.

The ones that never became features, honestly, never became features because they fundamentally broke some aspect of T-SQL development. Side effects in functions is one of them.


Can a database restore fail after it has begun? I've noticed that a lot of times if a database restore is going to fail, it will fail before it even starts restoring in SQL Server 2008 Management Studio (displaying an error about space or something similar). I need to set up a schedule to restore a database overnight, but am not sure if I'll need to monitor it overnight or not. The hardware is stable enough that I believe this won't be a point of failure.

A better question might be: What checks are done against a backup prior to the restore process beginning?

Are you restoring to an existing instance of the DB? If so then space issues shouldn't be particularly relevant. At our place we run the following as job steps in restore jobs:

USE [master]
GO

ALTER DATABASE nameofDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

DECLARE @Kill nvarchar(1024) = ''

SELECT @Kill = @Kill+'KILL '+CAST(spid AS varchar(4))+'; '
FROM sys.sysprocesses s
JOIN sys.databases d ON s.dbid = d.database_id
WHERE name = 'nameofDB'

EXEC(@Kill)
GO

ALTER DATABASE nameofDB SET MULTI_USER;
GO

This will kill all system processes (spids) against your DB. As someone else mentioned ensure there is an agreement with users that this will occur, or that other system processes require it etc.

Hopefully these steps will add some resiliance.

NB: Assumes you run the DB in multi_user !


Msg 21, Level 21, State 1, Line 1 Warning: Fatal error 9001 We recently moved to a SQL Server that's on a VPS and every now and then we get a 9001 error when trying to do an update.

Rebooting the server fixes the problem.

I changed the maximum server memory (in MB) to 2000 because it's a 4GB VPS.

But I just don't know what's causing the error.

That error means that the log for your database (or for tempdb) is not avaialble for some reason.  That's a serious problem that is usually fixed by restarting the server.

If the problem recurs, it could be many things having to do with problems with your storage system.  One possible thing to check is to see if your database's log file (or tempdb's log file) is running out of space (not sure if that can cause this error or not).

take your database offline then online will resolve your issue....
also set autoclose to false


Can Availability Groups cross domains? Is SQL Server 2014 capable of having Availability Groups set up across multiple domains?  If not, is there a third party tool you can recommend to mirror a 13TB (and growing) database across domains?

No, not in SQL Server 2014.

You can have Availability Groups across multiple subnets, but they all must reside on the same domain (even if they span data centers, continents, etc). This official document states as much for Windows Server 2008 R2, but I can assure you the same remains true for more recently released versions of Windows (emphasis mine):

  Windows Server 2008 R2 requires that all the cluster servers must be in the same Active Directory domain. Therefore, SQL Server multi-subnet failover cluster requires that all the cluster nodes be in the same Active Directory domain even if they are in different subnets.

And from this document (emphasis theirs):

  Availability replicas must be hosted by different nodes of one WSFC cluster:  For a given availability group, availability replicas must be hosted by server instances running on different nodes of the same WSFC cluster.

This is enforced by the underlying Windows Server Failover Cluster (WSFC), not AGs directly. You can't have a Windows cluster traverse domains, and since AGs need to all be part of a single WSFC, there is no way to have them span domains.

For newer versions of SQL Server and Windows there is a new concept called a Domain Independent Availability Group which you can read about here. And for SQL Server 2017 there was a requirement to support Linux, which obviously doesn't have Windows domains, and you can read about that here.

Klaus Aschenbrenner talks about setting up a Basic Availability Group in SQL Server 2016 Standard Edition and Windows Server 2016, on a simple workgroup (no domain at all):

How to create a SQL Server Availability Group WITHOUT an Active Directory Domain

I tried this on my laptop by configuring between virtual machines.
you can make use of this as reference. However I have not done this on production environment.

https://www.chiranjeevivamsydba.com/search/label/Multi%20Domain%20Always%20on


How to unnest and GROUP BY elements of a JSON array? Given the band table, with a json column holding an array :

id | people
---+-------------
1  | ['John', 'Thomas']
2  | ['John', 'James']
3  | ['James', 'George']

How to list the number of bands each name is part of?
Desired output:

name   | count
-------+------------
John   | 2
James  | 2
Thomas | 1
George | 1

The data type of the column people is json, as is the result of json_array_elements(people). And there is no equality operator (=) for the data type json. So you also cannot run GROUP BY on it. More:

How to remove known elements from a JSON[] array in PostgreSQL?

jsonb has an equality operator, so the "workaround" in your answer is to cast to jsonb and use the equivalent jsonb_array_elements(). The cast adds cost:

jsonb_array_elements(people::jsonb)

Since Postgres 9.4 we also have json_array_elements_text(json) returning array elements as text. Related:

How to turn JSON array into Postgres array?

So:

SELECT p.name, count(*) AS c
FROM   band b, json_array_elements_text(b.people) p(name)
GROUP  BY p.name;

It seems more convenient to get names as text instead of jsonb objects (double-quoted in text representation) and your "desired output" indicates you want / need text in the result to begin with.

GROUP BY on text data is also cheaper than on jsonb, so this alternative "workaround" should be faster for two reasons. (Test with EXPLAIN (ANALYZE, TIMING OFF).)

For the record, there was nothing wrong with your original answer. The comma (,) is just as "correct" as CROSS JOIN LATERAL. Having been defined earlier in standard SQL does no make it inferior. See:

What does [FROM x, y] mean in Postgres?

Neither is it more portable to other RDBMS, and since jsonb_array_elements() or json_array_elements_text() aren't portable to other RDBMS to begin with, that's also irrelevant. The short query does not get any clearer with CROSS JOIN LATERAL IMO, but the last bit is just my personal opinion.

I used the more explicit table and column alias p(name) and the table-qualified reference p.name to defend against possible duplicate names. name is such a common word, it might also pop up as column name in the underlying table band, in which case it would silently resolve to band.name. The simple form json_array_elements_text(people) name only attaches a table alias, the column name is still value, as returned from the function. But name resolves to it's single column value when used in the SELECT list. It happens to work as expected. But a true column name name (if band.name should exist) would bind first. While that won't bite in the given example, it can be a a loaded foot gun in other cases.

Don't use the generic "name" as identifier to begin with. Maybe that was just for the simple test case.

If the column people can hold anything but a plain JSON array, either query would trigger an exception. If you cannot guarantee data integrity, you might want to defend with json_typeof():

SELECT p.name, count(*) AS c
FROM   band b, json_array_elements_text(b.people) p(name)
WHERE  json_typeof(b.people) = 'array'
GROUP  BY 1; -- optional short syntax since you seem to prefer short syntax

Excludes violating rows from the query.

Related:

When can we use an identifier number instead of its name in PostgreSQL?

For someone in MySQL

SELECT
  JSON_EXTRACT(people, CONCAT('$[', idx, ']')) AS name, count(*) as count
FROM yourtable
JOIN subtable AS indexes
WHERE JSON_EXTRACT(people, CONCAT('$[', idx, '].id')) IS NOT NULL
group by name

with subtable like: Colum: idx, row: 0,1,2,3,4,5,6,7,8,9...


Cannot create SSRS subscription for a report using SSAS as datasource My questions is : how should I config SSRS subscription a report, which consumes SSAS datasource.
I already googled and lookup stackoverflow a lot and still couldn't make it work.

Problems:
I developed and deployed a SSRS report to the server, and next I need to make subscription for that report to send emails.

And the report is using SSAS as datasource.
I learnt that SSAS required to use windows credentials and I already setup
the datasource .

However, when I attempted to click [New subscription], it alerts:

I have blogged about this exact issue. The workaround/solution involves expression-based connection strings which can be a bit painful. But I have prototyped a tool to automate the setup of those connection strings after deployment:

Using EffectiveUserName To Impersonate in SSAS

in the report it self, inside "data source" use SQL credential :


Is changing the SQL Server port really that much safer? SQL Server's default port is 1433. I've been told by our admin that the port has to change "for security reasons".

Is it really that much safer to change the port? If the server is behind a firewall and it only allows connections to it from a certain range of IP's, is that not good enough?

It helps against common port scans that can be initiated through port scanning websites. But it won't help against a committed attacker. It's just another layer, but doesn't add much over the firewall, as you mentioned.

When an application connects to MS SQL over TCPIP the first part of the conversation takes place on 1433 with the default unnamed instance- not least is the desemination of any port numbers that named instances are communicating on. Any firewalls should be configuered to a) let this through to ip ranges that are appropriate, B) the fixed port numbers that any named instances may be using. These should be configuered in the SQL config manager as fixed. You can communicate with any instance using (ip address 192.168.22.55) : (port number 12345) in the connection string. If the SQL browser service is not enabled then 1433 will not provide the intitial conversation. If you are using NT authentication then there is little to be gained. If you are using SQL authenetication then there is a small amount to be gained.


check for dependencies on other servers in SQL server 2000 I have an old server running SQL Server 2000.

I need to perform some maintenance on the server and one step is to check for dependencies on other servers.

The SQL Server 2000 machine has database A, while database B is running on a different server.  DB A has stored procedures that will modify some table fields on DB B and vice-versa.  I want to know what cross DB/Server references there are, what should I do?

I'm fairly sure that there isn't a reliable way to do this. I remember being very surprised when I dropped and recreated a table in SQL Server 2000 and lost all the dependencies to the stored procedures that refer to it.

You can try looking at sysremotelogins and sysservers, but I doubt that will cover the full list.


Reset Root Password MySQL on Ubuntu I know there are tons of posts about this on here but nothing seems to work.  I have tried every command I could find it will not work.
I just recently setup mysql on Ubuntu (SQL version is 8.0.23-0ubuntu0.20.04.1).  Afterwards i installed myphpadmin and I can get to it, but I noticed that it said that root doesn't have a password, even though I set one in the mysql_secure_installation.  I don't really want to go any farther until i get a password on root, as eventually i'm going to have this server exposed to the outside, but every single command I have tried seems to do nothing.  I am testing if it is working by entering "sudo mysql -u root" with no password and i'm assuming if there is a root password set then it will prompt for one instead of just going straight into the mysql> prompt.
These are the commands that i've tried:
mysql> UPDATE mysql.user SET Password=PASSWORD('newpassword');
RESULT: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('newpassword')' at line 1

mysql> SET PASSWORD = PASSWORD('newpassword');
RESULT: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('newpassword')' at line 1

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
RESULT: Query OK, 0 rows affect (0.01 sec)

Seeing this made me think its working, so i ran FLUSH PRIVILEGES; and refreshed myphpadmin but it still shows that it doesn't have password, so i kept going.
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');
RESULT: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('newpassword')' at line 1

shell> sudo mysqladmin -u root password
New password: newpassword
Confirm new password: newpassword
RESULT: Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

This one seems like the right path maybe?  But it says i need SSL...which is weird because i'm on the computer in a terminal, i don't know how to do an ssl connection to mysqladmin on the same computer using the terminal.
shell> sudo mysql_secure_installation
RESULT: Success.  All done!

This acts like it works, but it does nothing
Change from myphpadmin:  I tried changing it from myphpadmin as well, it goes through with no issues, but the password still seems to be the same.
mysql> UPDATE user SET authentication_string=PASSWORD('newpassword') WHERE User='root';
RESULT: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('newpassword')' at line 1

There was also mention of getting the first root login from /var/log/mysqld.log, but that doesn't exist on my computer
There are probably a few other odd things here and there i've tried but nothing has worked, does anyone have any insight into this?

I managed to figure it out.  I started digging more into the user table and found the root user entry, and then found the authentication_string column.  I had already followed the guides that someone commented and it didn't work, not sure why, so I tried the following.
mysql> USE mysql
mysql> UPDATE user
    -> SET authentication_string = 'newpassword'
    -> WHERE User = 'root';

RESULT: Query OK, 1 row affected (0.01 sec)
Rows matched:1 Changed: 1 Warnings: 0
mysql> FLUSH PRIVILEGES;

I then checked on phpmyadmin and it shows that it has a password on it now.  I think it should be good now...unless there is another check i need to run to be sure?


Comparing Tool for Reporting Services Objects I have three Reporting Services Instances (Server), all in version of SQL-Server 2017.

All instances have the same tree structure with hundreds of objects (datasources, datasets and reports)

Now I'm looking for a tool which compares the same objects between two instances. I already use the redgate comparing tools but they do not cover reporting services.

Any suggestions?

So basically what @saihtam8 said in his comment.

This SO thread might be helpful to you: Where does a published RDL file sit?

Essentially just compare the report BLOB columns (Content? in SQL Server 2008 R2) in the Catalog tables across each server instance.
In SQL Server 2008 R2, it looks like there are DataSets and DataSource tables as well which you can compare.

WinMerge is free and will compare the structures and contents of up to 3 folder paths at the same time.


What is the right data type to store only -1 0 and 1 in postgres column? I want to store only 3 states in a column (if it was 2, I would use a boolean)
I considered using an ENUM('up', 'down', 'none') but it seems ENUMs take some significant space
Is there a better way to store only -1 0 and 1 in a PostgresQL column?

If you want to save space, you can use the "char" data type. It stores a single byte.

you can cast integer or text to "char":

SELECT 'u'::"char", 'd'::"char", 'n'::"char";

 char | char | char
------+------+------
 u    | d    | n
(1 row)

An enum uses 4 bytes since it is internally stored as a real.

What you are out to save space, you'll have to take alignment into account. The values are always aligned according to the type alignment. For example, a bigint always has to start at an address that is divisible by 8.

Now if your table is defined as

CREATE TABLE (
   smallflag "char",
   largenum  bigint
);

there will be 7 padding bytes between the columns, which would render all the space gains from "char" moot.

So place your table columns carefully.

I'm not really an expert on Postgresql, but checking the docs you could use [var]char(n) as someone suggested. Or if you're comfortable working with bit values you could use [var]bit(n) which looks like the smallest possible. The storage size for [var]bit(n) is basically the number of bits rounded up to the next byte.

For portability: [var]char(n) is (afaik) universally supported, while [var]bit(n) is sometimes called [var]binary(n) instead


Why does START TRANSACTION WITH CONSISTENT SNAPSHOT not work with SERIALIZABLE isolation level? The MySQL doc for START TRANSACTION says:

  The only isolation level that permits a consistent read is REPEATABLE READ. For all other isolation levels, the WITH CONSISTENT SNAPSHOT clause is ignored. A warning is generated when the WITH CONSISTENT SNAPSHOT clause is ignored.

I'm surprised, as I thought that the SERIALIZABLE isolation level, being above REPEATABLE READ, would also offer a consistent read.

What prevents WITH CONSISTENT SNAPSHOT from working with SERIALIZABLE transactions?

I even found this bug report from 2012 where the doc originally said that this worked with both REPEATABLE READ and SERIALIZABLE, and they explicitly changed it to REPEATABLE READ only, so it looks like there is a technical reason for this. I tested it and MySQL does generate a warning when used with SERIALIZABLE.

The WITH CONSISTENT SNAPSHOT modifier starts a consistent read for
  storage engines that are capable of it.

  consistent read A read operation that uses snapshot information to
  present query results based on a point in time, regardless of changes
  performed by other transactions running at the same time.

With SERIALIZABLE isolation level you don't need to use a snapshot because all necessary data is already locked by your transaction.

SERIALIZABLE isolation level should be more ACID compliant than REPEATABLE READ because the second one allows non-locking reads which may lead to a violation of the I (Isolation) rule of ACID.

This is mainly a conjecture on my part, but the technical reason stems from when a SERIALIZABLE transaction goes into effect.

According to Paragraph 4 of the MySQL 5.7 Documentation on Transaction Isolation Levels

  You can enforce a high degree of consistency with the default REPEATABLE READ level, for operations on crucial data where ACID compliance is important.

  ...

  SERIALIZABLE enforces even stricter rules than REPEATABLE READ, and is used mainly in specialized situations, such as with XA transactions and for troubleshooting issues with concurrency and deadlocks.

How can you make SERIALIZABLE enforce consistency rules stricter than REPEATABLE READ ? Note the definition of SERIALIZABLE from the same Documentation Page:

  This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

So, you have to experiment with autocommit to see the differences.

With autocommit disabled used in SERIALIZABLE, SELECT ... LOCK IN SHARE MODE is summoned to bring about some readable locking but the locks would appear preemptively in  a REPEATABLE READ at certain stages before a read takes place.

With autocommit enabled, SERIALIZABLE can be viewed as being like REPEATABLE READ because nonlocking reads can run wild and the data being read would be essentially be the same.

When you look at transactions from this perspective, SERIALIZABLE can never be trusted to be consistent because of the stagnating shared locks. This really disqualifies it from being consistent. That is why REPEATABLE READ is the only isolation level used for ACID compliance.

Therefore, any snapshot made SERIALIZABLE could look different at different points in time as opposed to REPEATABLE READ .


copy only tables with data from one database to another database I have two database , dbOne(version - 10.50.1600 - locate in office server )  and dbTwo(version - 10.0.1600 - locate in my local server) .
I want to copy dbOne's tables with data to dbTwo .
Is there any way or script to do it ? I don't want to upgrade my local server-version !

Here are several options you can try:

Export/import wizard: If you only want to copy tables and don’t want to bother with relationships and such this is probably the quickest way to do this.

Generate scripts wizard: Generate scripts for all tables you want to migrate. Right click on the database -> Tasks -> Generate scripts -> select only tables you want to migrate.
Also make sure that you manually review execution order of scripts because SSMS doesn’t order these correctly be default (doesn’t take dependencies into account)

Third party tools: There are many database comparison tools out there you can use for this such as Red-Gate’s SQL Compare, ApexSQL Diff and many others…. All of these are premium tools but you can get the job done in trial mode.

If you have tcp connectin between two databases you can make linked serve.
then you can query your table on onther server.
in SQL MGM studio under object explorer there art node for linked servers.
You can easly add one.
after that you can do something like

insert into tableone.
lsect * from [linked server].sorucedatabse.dbo.tableone


SQL Server 2008 simple recovery mode issue During my daily maintenance backup task, I am attempting to set specific databases to recovery model simple along with shrinking the database.

ALTER DATABASE db_name SET RECOVERY SIMPLE
GO
DBCC SHRINKDATABASE (db_name)
GO

This happens at 1am along with some other tasks that use the same database. However, I am getting an error where

  Execute T-SQL Statement Task (AERIS\SQL2008) Execute TSQL on Local server connection   Execution time out: 0 Task start:  2011-12-08T01:00:04.
  Task end: 2011-12-08T01:16:45.
  Failed:(-1073548784) Executing the query
  "alter database db_name set recovery simple" failed with the following error: "ALTER DATABASE failed because a lock could not be
  placed on database 'db_name'. Try again later.
  ALTER DATABASE statement failed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly,
  parameters not set correctly, or connection not established correctly.

Could the issue be that since a user is logged on the the server at the point the recovery model is being changed? I'm puzzled since sometimes the recovery model change works intermittently.

If you change the recovery model to SIMPLE to back to either FULL or BULK_RECOVERY you make all your transaction log backups useless.  The recovery model shouldn't every be changed regularly.

Shrinking the database is simply fragmenting all the objects within the database tables.

You should stop doing both of these things.

You have to restart the database server after a change of recovery model , so what you are doing isn't going to work anyway.

Why are you shrinking on a daily basis anyway, it's at best a complete waste of time in nearly all cases.


Partial updates with functions My applications rely on PostgreSQL functions to carry out operations on the database. I'm having trouble figuring out a good practice to make partial updates where only some fields are provided.

Let's take a posts table as an example:

+-----------------------------------+
|             post table            |
+-----------------------------------+
| post_id | SERIAL PRIMARY KEY      |
| title   | VARCHAR(100)            |
| content | TEXT                    |
+-----------------------------------+

The application can update the data when one or any combination of fields change. For example, when only title changes, do not update content. Or when only content changes, do not update title. Or when they both change, both of them have to be updated.

This is a simple example, but it applies to all tables with many more columns, etc.

There are some ways I can think of:

I could make a function for every column: post_update_title(changed_title, post_id), post_update_content(changed_content, post_id). Each of them only change the specific column they are responsible for. But this approach gets messy really quickly when there are tables with many columns.
Create a general function that takes in the column name, changed value and post ID: post_update_column(column_name, changed_value, post_id). This approach makes me share information specific to the database with my applications. This is something I don't want.
Create a general function for updating and set a default NULL value to parameters. This way I could detect which columns have to be changed. But this introduces a problem when there are columns that can be set NULL themselves. Then I couldn't say if the column was not updated or was explicitly set NULL.
Call UPDATE queries directly from inside the application. This approach I dislike the most: I don't want to couple the application with the DB this way.

Has anyone faced something similar? How did you solve it?

For example, when only title changes, do not update content. Or when only content changes, do not update title. Or when they both change, both of them have to be updated.

Why? When either of them changes just update both of them. It's an MVCC database and any change to the table will result in a whole new row being written and the old one being marked as dead.

UPDATE post
SET title = $2, content = $3
WHERE post_id = $1
  AND title <> $2
  OR content <> $3;

And don't use VARCHAR(100) use text


Issue in installing SSIS MS-SQL2012 While installing the MS SQL Server 2012 Enterprise Edition, everything goes through fine but I'm not able to install Integration Services alone. I get the error as following:

  The following error has
  occurred:C:\SQLserver....\Binn\DTSSVCPERF.ini Click retry to retry
  the failed action.

I have tried re-installing many times, but still am facing the same issue.
The OS is Windows Server 2012.

The problem is apparently not related to just SQL 2012; it has been reported on older versions also.

One guess is that your copy of SQL Server is probably corrupt. You could try downloading from a different source and checking if the issue repeats. The new SQL Server download might have all the required files in good condition, and save you the headache of any issues down the line. Of course, you should uninstall everything before attempting a fresh install from a different source.

You mentioned that it was Enterprise Edition? Assuming that it's a licensed copy,  you can also call Microsoft Support and ask them for assistance.


What happens when sga and pga reach limits? I have the following doubt, I have a server with Oracle 9i and another with Oracle 12c, in both cases I have the doubt that happens when Oracle reaches the limit imposed by the defined memory parameters (SGA and PGA)? ... Example hypothetical:
I have a server with 32GB of RAM in which I only have running an instance of oracle 12c which has assigned the following parameters:

sga_max_size = 19327352832
sga_target = 19327352832
pga_aggregate_target = 10737418240
pga_aggregate_limit = 12884901888

If it were to reach the limit of those parameters that would happen ?, the operating system (RedHat) would begin to swap?

SGA: nothing serious, it is a fix sized memory area, once it gets full, the database reuses memory that is rarely accessed using LRU (least recently used) algorithm

Memory Architecture

PGA: Once PGA_AGGREGATE_LIMIT is reached, the database aborts calls of sessions having the most untunable (like PL/SQL collections) PGA memory, and those sessions receive ORA-04036 error. If that does not help and PGA usage is still above the limit, the database may even kill those sessions:

PGA_AGGREGATE_LIMIT

  Actions Taken When PGA_AGGREGATE_LIMIT is Exceeded

  Parallel queries will be treated as a unit. First, the sessions that
  are using the most untunable memory will have their calls aborted.
  Then, if the total PGA memory usage is still over the limit, the
  sessions that are using the most untunable memory will be terminated.

  SYS processes and background processes other than job queue processes
  will not be subjected to any of the actions described in this section.
  Instead, if they are using the most untunable memory, they will
  periodically write a brief summary of their PGA usage to a trace file.

(19327352832 + 12884901888)/1024/1024/1024 = 30

You have 32 GB memory in your server, you have allocated 30 GB memory to the database, and there is nothing else running on it, as you said. With proper configuration and sizing, you should not see swapping under normal circumstances:

hugepages for decreasing page tables
filesystemio_options=setall for direct I/O and avoiding filesystem caching
vm.swappiness set to 10 or even lower
sizing: reasonable amount of database sessions/processes

This does not mean the system will never swap. For example:

SYS sessions and background processes are not limited by PGA_AGGREGATE_LIMIT. You can still have rogue procesesses consuming PGA memory above the limit causing swapping.
If you use and unreasonably sized connection pool, like 100s or even 1000s of database processes, those processes have some private memory that is not part of PGA, so they add up on top of the SGA + PGA limit. This is especially significant on AIX, fortunately not so much on Linux.

Applications will start to fail as they want to allocate more memory than the limits you set.


What is the best way to split a string into integers and units? I've got a table of medication prices, each with their own units.
I am trying to split the strength into four columns: Numerator_integer, Numerator_uom, denominator_integer and denominator_uom.
I'm able to split the fraction into a numerator and denominator with a little game of 'find the slash' like so:
2GM/50ML becomes 2GM and 50ML
            if object_id('tempdb..#example') is not null begin drop table #example end

    create table #example (cost decimal(10,4), size decimal(10,4), strength varchar(255))

    insert into #example (cost, size, strength)
    values (918.060,    210.000,    '0.05MG/ML'),
            (808.480,       50.000, '1GM/50ML'),
            (1619.190,   50.000,    '2GM/50ML'),
            (1941.960,    1.000,    '300MG'),
            (30005.560,    40.000,  '5MG/ML')

    ;with costs2 as (select cost, size, strength,
    case when strength like '%/%' then replace(left(strength, charindex('/', strength)),'/','')  else strength end as numerator,
    case when strength like '%/%' then right(strength, len(strength) - charindex('/', strength)) else '1' end as denominator
    from #example),

    costs as (select *, null as numerator_uom, null as denominator_uom from costs2)

    select * from costs

However I can't think of an elegant way to loop through the numerator and split out the numeric half from the varchar half.
Question: how would you split out the numeric and varchar halves of a string?

We can use APPLY to calculate the position of the various things we need, we use each APPLY in series like variables in procedural code.
We assume that the number is the start of the string, followed by arbitrary letters, a slash, another number, then more letters.
Note: no tally function needed here:

select
    cost,
    size,
    strength,
    v4.*

from example e

cross apply ( values (
    charindex('/', e.strength)
) ) as v1 (slash)

cross apply ( values (
    iif(slash = 0, e.strength, left(e.strength, slash - 1)),
    substring(e.strength, nullif(slash, 0) + 1, len(e.strength)) -- len means the rest of string
) ) as v2 (numer, denumer)

cross apply ( values (
    patindex('%[^0-9.]%', numer),  -- first non-number
    patindex('%[^0-9.]%', denumer)
) ) as v3 (numerUnit, denumerUnit)

cross apply ( values (
    try_cast(iif(numerUnit = 0, numer, left(numer, numerUnit - 1)) as real),
    iif(numerUnit = 0, null, substring(numer, numerUnit, len(numer))),

    try_cast(iif(denumerUnit = 0, denumer, left(denumer, denumerUnit - 1)) as real),
    iif(denumerUnit = 0, null, substring(denumer, denumerUnit, len(denumer)))
) ) as v4 (numerator, numeratorUnit, denumerator, denumeratorUnit)

I wrote two functions that do an OK job:
for the integer/decimal part:
    ALTER function [dbo].[get_dec](@string varchar(max))
    returns varchar(max)

    as
    begin

    declare
    @i int = 1,
    @end int = len(@string),
    @char varchar(max),
    @lastchar varchar(max)

    while @i <= @end
        begin
        set @char = left(@string, @i)
        if isnumeric(@char) = 1
            begin
            set @lastchar = @char
            end

        set @i = @i + 1
        end

    return @lastchar
    end

and for the units of measurement:
    ALTER function [dbo].[get_char](@string varchar(max))
    returns varchar(max)

    as
    begin

    declare
    @i int = 1,
    @end int = len(@string),
    @char varchar(max),
    @return varchar(max)

    while @i <= @end
        begin
        if isnumeric(left(@string, 1)) = 1 or left(@string, 1) = '.'
            begin
                set @string = right(@string, len(@string) - 1)

            end
        set @i = @i + 1
        end

    return @string
    end

Your mileage may vary. These functions did ok on my server, for a few thousand rows.


Cannot start SQL Server 2014 service. Memory Error (resource pool 'internal') Under Windows 2012 R2 Standard (Fully Updated): When trying to start my SQL Server 2014 Service I get in Application event viewer:

"there is insufficient system memory in resource pool 'internal' to run this query"

Here is the complete ERRORLOG in pastebin.

Significant errors I can see:

2017-07-04 15:32:56.59 spid8s       Failed allocate pages: FAIL_PAGE_ALLOCATION 1
...
2017-07-04 15:32:56.62 spid8s      Error: 701, Severity: 17, State: 123.
2017-07-04 15:32:56.62 spid8s      There is insufficient system memory in resource pool 'internal' to run this query.
2017-07-04 15:32:56.65 spid8s      SQL Server shutdown has been initiated
2017-07-04 15:32:56.68 spid8s      Error: 701, Severity: 17, State: 123.
2017-07-04 15:32:56.68 spid8s      There is insufficient system memory in resource pool 'internal' to run this query.
2017-07-04 15:32:56.77 spid8s      SQL Server shutdown has been initiated

This is a RAM Map screnshot of my server.

This machine is hosted in a VPS, and technical support says the "Driver locked" RAM is assigned on demand, so I can't find why SQL Server throws this memory error. Any ideas??

This machine is hosted in a VPS, and technical support says the
  "Driver locked" RAM is assigned on demand, so I can't find why SQL
  Server throws this memory error. Any ideas??

Yes.  Your hoster has "borrowed" your VMs memory and given it to another VM.  The "Driver Locked" memory is a "Baloon Driver" that both VMWare and Hyper-V use to enable dynamic memory allocation to VMs.  That memory appears to belong to the VM, but is actually held by the hypervisor, and may be currently being used by other VMs.

Not all operating systems support actually changing the amount of memory they use without a reboot.  In Windows this capability only appeared in Windows 10/Windows Server 2016.  So the way hypervisors handle this is they install something inside the VM to allocate memory.  Then this memory is actually removed from the VM and is available for other VMs.  Typically they use a driver (as opposed to a user process), commonly called a "Baloon Driver".

So the almost 12 GB are "Driver Locked" by the VPS manager is not actually available.  You need to ask your hoster to give your VM more memory.

It may be the case that the hypervisor is supposed to respond to memory pressure inside the VM adding memory to the VM and reducing the amount locked away in the Baloon Driver.  But if so, this is just not working well enough to enable your SQL Server to start, and they need to bump up the startup memory for your VM a bit.

On the other hand, if you paid for 16GB of ram, you might want to look for a new hoster who doesn't engage in such shenanagans.

This could have happened, if we have set the max server memory below recommended practices, and have also added the SQL Server Service Startup logon Account to "Lock Pages in Memory" option at OS policies. So if the memory is too low and this option is enabled the SQL service will not start as it has to lock the pages in memory and we have set the wrong max server memory. To rectify this error we need to remove the service Log on Account from this OS policy and then start the service. Once the Service has been started, please set the max server memory in SQL properties to correct value. After that, set the policy "Lock pages in Memory" again with SQL Service Logon and restart the SQL service.

Regards,
Ibrahim


How can I monitor my server’s status before changing the “Cost threshold for parallelism” settings Our production server is running SQL Server 2008 SP3.

I’ve read a post on “cost threshold for parallelism” - http://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/
And I have a few questions.

We have a relatively high amount of CXPACKET wait events on our production server (in average of 85%).
The server has default values for “cost threshold for parallelism” and “Max Degree of Parallelism”

I started to explore the possibility of increasing the value of “cost threshold for parallelism”.
I suspect the indeed the value is too low, but I wanted to get as much information as possible before I change anything and not just change it to 25 or 50 because that’s what the some of the internet posts suggested.

I’ve read this nice post about how to determine the value:
http://sqlknowitall.com/determining-a-setting-for-cost-threshold-for-parallelism/

The median cost in my server was 63.

How can I monitor my server’s status “before” and “after” I change to settings, in order to verify the effect of the value on the system’s throughput?

Thanks in advance,
Roni.

If you have a lot of CXPACKET waits it is possible that reducing setting the MaxDOP is appropriate and will help. You should not change the cost threshold for parallelism setting assuming you have a NUMA machine (which you most likely do).

MaxDOP: https://support.microsoft.com/en-us/kb/2806535

...and I read somewhere that cost threshold needn't be changed but am still trying to track that link down :(

In any case, focus on MaxDOP.


Reclaim Transaction Log space of SQL Server I have a SQL Server databases for which log files have grown huge. I have log backup scheduled to run only once a day. But now all the disk space is used by these log files.

Can anyone help me figure out what could be possible ways to reclaim that used space again. Here's what I understood and aware off.

I need to take more frequent log backups. As, SQL Server will internally reuse that logspace as and when required.
I have DB's in Full Recovery Model and under AlwaysOn setup. Hence, if I want to shrink them, I'll need to first remove them from AlwaysOn and then put them in Simple Recovery mode and then Shrink the Log File to desired size limit.

Is there any other way apart from the two mentioned above. Can I create a new log files with fresh required size on some other drive and make my db's point to that new location. Is it a doable task? need more clarity on this.

Any inputs on this would be a great help.

Yes, take log backups more frequently. Is there a reason they are so infrequent? It seems like you have a lot of potential for losing data (not just in complete disaster). The log file will continue to grow until a backup is taken, because it will not re-use the space.

Another solution would be to direct your transaction logs to a separate drive, and continue with the log backup or you could find the longest running transaction and kill it


MAX DATE With NULL VALUE Trying to get the max date from my table, i can't retrieve the value when the date is null.
Sample :

the following table :

+----+--------+------+------------------------+
| id | parent |    Date                       |
+----+--------+------+------------------------+
|  1 | Alex   |   2018-10-01 00:00:00.0000000 |
|  1 | TIM    |   2012-11-07 00:00:00.0000000 |
|  3 | Cath   |    NULL                       |
+----+--------+------+------------------------+

To get the max date im using the following:

select id, Parent, Date From Table T1
Where Date = (select Max(Date) from Table T2 where T1.id= T2.id )

Wich works fine for the id = 1
But for the id = 3 it's not working

The result is :

1 | Alex   |   2018-10-01 00:00:00.0000000

Any ideas Why?

Aggregated functions does not consider rows with NULL values.

You could use a WINDOW FUNCTION to enumerate rows and then get first row only:

with ct as
(
    select id, Parent, Date,
           row_number() over (partition by id order by id, date desc) rn
    from   tbl
)
select id, Parent, Date
from   ct
where  rn = 1
order by id;

id | Parent | Date
-: | :----- | :------------------
 1 | Alex   | 01/10/2018 00:00:00
 3 | Cath   | null

db<>fiddle here

As McNets states, this is normal SQL behaviour. You have to watch out using NULL's in comparisations since NULL is actually everything.

You might be able to solve this to set a "dummy" value to the NULL value.
This way you can select the NULL value as the highest date.

select CASE WHEN MAX(COALESCE(Date, 1000000)) = 1000000 THEN NULL ELSE MAX(Date) END as Date
from Table T2 where T1.id= T2.id


PostgreSQL - getting Too many client repeatedly I am running PSQL with PostGis extension. After the start of server, after about 3 weeks, I start getting fatal errors for connections with "too many clients already". When I restart the server, problem is gone, but is back again after aprox. 3 weeks.

If I run pg_stat_activity, I got

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

"active";2
"idle";944

And all idle queries are the same - SELECTs.

I cannot debug this problem and restarting server all over is not a real solution.

Your application has a connection leak.
There is no way to fix this on the database side - you'll have to change the application so that it closes connections again.

I guess you set max_connections so high to work around this problem (1000 is too high).

if it possible for you, you can kill the connection based on its idle time but its always better to fix it at app level.


Restore a database backup on another server without sysadmin account I need to restore my own database backup on another server.

On both servers I have the same user account name with limited privileges (eg WebUser).

On the target server I can't use a sysadmin account, so how can I restore my backup and give access to limited user account WebUser?

Of course I can't use SQL Server Management Studio but only code in my app.

I've tried with an account with this server role

[serveradmin]
[diskadmin]
[dbcreator]

I can restore the backup but can't access it nor give permissions on the database.

I think your main issue is that after restoring the database to your new target user, that your account "WebUser" is effectively orphaned.

You should have your DBA change the SID for WebUser (and any other user that matters) to be the same as in your source server.

Note that this will require dropping the user and re-creating it in the target server.  And any other databases will need to be fixed as well.

But this will allow you to restore the database from the source server, to the target server and not have to worry about permissions when you get done.

Use any login with [securityadmin] server role on target to create your user [WebUser] on restored db and assign [db_datareader] role on same.


Search and compare list of ever-growing IPs I'm a newbie when it comes to mysql things and queries in general, just wanted to point that out.

I'll keep this as short as I can.

I have a table where I track the IPs of users that enter the front-page of a site. Any other page is not logged.

I have a unique ID for that table set as the IP of the client.

Every time a new user hits the front-page I have to look-up his IP in the table to make sure his IP is not there, if it's not it gets inserted, if it is, nothing happens.

So far this is working great, but then again, there aren't many IPs in there.

Since I'm using Laravel's Eloquent, I can't give you the precise query, in PHP it looks like this:

Tracker::where('ip', $client_ip)->first();

Although I'm not sure, I suspect that the raw query looks something like this:

SELECT ip FROM table_name WHERE column_client_ip = '127.0.0.1' LIMIT 1

The IP itself is saved as a varchar. I know about INET_ATON but I'm not exactly sure if I can use it with ipv6.

My questions are as follows:

I know that searching and comparing varchar type fields isn't the fastest thing in the world, do I have any other options to save IP? Keep in mind that they can also tunnel their IP which, in case I'm not wrong, it adds to the total length of the string I get as the IP - I could be way off here, my sysadmin skills are basically non-existent.
Currently the search is really fast, mostly because there aren't a lot of records and I also keep an index on the ip column. Realistically speaking, at what point should I start being concerned that the search/compare will slow down significantly?

If you guys have any other suggestions or want to warn be about possible issues I may have in the future with the current setup, I'm all ears.

I'm doing this at work. I use INET_ATON() to IPv4 and INET6_ATON() to IPv6. This search is faster than VARCHAR fields search. But, one important point:

This function was added in MySQL 5.6.3.
INET_ATON()   Return the numeric value of an IP address.
INET_NTOA()   Return the IP address from a numeric value.
INET6_ATON()  Return the numeric value of an IPv6 address.
INET6_NTOA()  Return the IPv6 address from a numeric value.

Documentation: MySQL Misc


When was a table trigger disabled and re-enabled? I need to know if the enable/disable history of a table trigger is natively tracked by SQL Server.

I've reviewed the system views:
 • [sys].[triggers] contains a modify_date field
 • [sys].[trigger_events] focuses on the trigger INSERT/UPDATE/DELETE events

Can you recommend any other sources of information about trigger history?

SQL Server doesn't track this information (I half expected to see it in the default trace, but it's not there either). Enable/disable will update modify_date, but you won't be able to distinguish that from a rename or code modification. Also, it will only show you when the last change took place. If you want any other tracking (such as who did it), you'll need to implement auditing or trace. I thought perhaps a DDL trigger could be used as well, but this seems like an intentional functionality gap:

http://connect.microsoft.com/SQLServer/feedback/details/509242/fire-a-ddl-trigger-when-the-new-syntax-disable-trigger-is-executed (abandoned)
https://connect.microsoft.com/SQLServer/feedback/details/434951/ddl-trigger-still-not-working-disable-trigger-is-this-not-an-event (abandoned)
https://feedback.azure.com/forums/908035-sql-server/suggestions/32898904-fire-a-ddl-trigger-when-the-newer-syntax-disable

Here is how you can capture this information going forward using an audit:
USE master;
GO

CREATE SERVER AUDIT MyAudit
  TO FILE (FILEPATH = 'C:\temp\', MAXSIZE = 1 GB)
  WITH (ON_FAILURE = CONTINUE);
GO

ALTER SERVER AUDIT MyAudit WITH (STATE = ON);
GO

USE [your_database];
GO

CREATE DATABASE AUDIT SPECIFICATION schema_db_change
  FOR SERVER AUDIT MyAudit
  ADD (SCHEMA_OBJECT_CHANGE_GROUP),
  ADD (DATABASE_OBJECT_CHANGE_GROUP);

ALTER DATABASE AUDIT SPECIFICATION schema_db_change WITH (STATE = ON);
GO

Now go and disable your trigger, and then run:
SELECT event_time, succeeded, server_principal_name, [object_name], [statement]
FROM sys.fn_get_audit_file ('C:\Temp\MyAudit*', NULL, NULL);

If you can't use audit due to edition or other issues, you could do this relatively inexpensively using a server-side trace. Just capture SQL:BatchCompleted and optionally filter on TextData LIKE '%disable%trigger%' (You'll have to test if it's better in your scenario to pay the cost for the filter to avoid collecting too much, or just collect more and whittle it down later. Filters can be quite expensive but it depends on the system.)
I'm sure there is also a way to do this with extended events. But XEvents and audit both require 2008+ and you didn't specify version...
Better yet, remove the ability to modify triggers for users who bypass change management. Ideally, you should be able to determine who enabled or disabled a trigger without ever looking at the database, because nobody should be doing that without documenting it.

Such operation will likely be recorded in the default administrative trace. The link contains instructions how to view it.

If it's stored in the default location, you can use:

SELECT *
FROM fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\log.trc', default)
GO


Historicizing of database models We want to build a SQL-Database for a system to manage regulations in different countries for different years. The problem is that the regulations change every year for every country. Like for social insurance you need different data in 2015 than you needed in 2014 and we have companies that enter their data to connect with their social insurance informations for every year.

If I want to build my models on this data I would have to create a different model for every year with different attributes. I can't just use migrations, because I have to keep the historical data and information.

Right now I am researching for the right solution and I would like to know if there is a name for this problem to find some solutions?

If you use PostgreSQL probably http://pgxn.org/dist/temporal_tables/ can help you.

All depends on how many changes there are each year of depending by Country.
You could build a generic system that allows you to enter it by country and by year in one table. Then another field could be FieldName.
At that moment you can enter all data.
Now for calculations you build a procedure and a separate formulas table. In this formula's table you enter the dynamic SQL statements.
Is a bit strange in the beginning but we had a similar system to calculate weekly  filmhires in several countries. Some had citytaxes, other had several author rights, all calculated on different ways. Even in some countries depending on what kind off a contract you had the formulas had to behave differently. But finally worked for 10 years without missing. Just adding the newer formulas each year.
Ok, this had to be done by a SQL guy, but changing this towards a more natibe pseudo code would not be that difficult.


Cannot connect to 19c with SQL Developer though database is mounted and opened, error ORA-01034 I have recently had a server crash that is hosting both an 11g and 19c databases.  Upon restart by IT staff, I discovered that one of the two 19c instances was idle. The 11g instances are all mounted, open and accepting SQL Developer connections.I was able to mount and open it using the init.ora file as an argument in the startup. I checked and the database is reporting, through SQL Plus, that it is mounted and open.  The listener has been restarted successfully.  However, trying to connect to it through SQL Developer I get ORA-01034. Any advice on what the problem is would be appreciated.
Error: ORA-01034: Oracle not available
ORA-27101: shared memory realm does not exist
01034, 0000 "Oracle not available"
SQL Developer is local and I have not tried remote sql plus as I am remoting into the Win 2012 R2 host server to run the commands. There is one listener for the database. Stopped and restarted listener and reports STATUS UNKNOWN for both instances.
Have not done anything with the environment variables. I do not have admin rights to the server.

You have to restart your DB instances while listener's are up and don't restart the listener after that. When DB's are started: try to connect to them locally (just with username/password) and locally through tnsnames (username/password@tnsname) If listeners are shown correct status and you are able to connect locally with tnsnames: make sure the sam tnsnames file is on your client and there is no firewall between DB and client.


Transaction recovery and rollback As I understand:

UNDO: Undoing a write item operation consists of examining its log entry [write_item, T, X, old_value, new_value] and setting the value of item X in the database to old_value. UNDO is always performed in the reverse order from the order in which the operations were written in the log.

REDO: Redoing a write item operation consists of examining its log entry [write_item, T, X, new_value] and setting the value of item X in the database to new_value.

In a transaction recovery algorithm, for example the one outlined in the Q & A Understanding Transaction Recovery

and let's say we have a schedule such as:

R1(X) R2(Y) W1(X) C W2(Y)

where C denotes a crash. According to the algorithm, we should UNDO T1, but REDO T2, since it is still active at the time of crash. But T2 hasn't had any Write operations, so what exactly does an UNDO entail here? Does anything need to actually happen? And where does the concept of a rollback come in? Is that just implicit in an UNDO?

Assuming that R means Read and W means Write, then the work you're proposing is like this:

W1(x) C

That's it. Reads  are irrelevant for recovery. And a crash denotes the end, you can't have a write after crash. So the task of recovery is to redo the one write and then undo it, since there is no Commit.

The algorithm is very simple:

Redo everything, in forward order
Undo anything not committed, in reverse order, generating a compensating write for every action being undone.

A good explanation is the ARIES paper.

See also How to read and interpret the SQL Server log on my blog.

It depends on the DBMS system being used and the time frame you are trying to examine.

Your question is very simplified and leaves a lot of room for interpretation.

I put your transactions and crashes into a timeline and added one for the CHEKPOINT, because (depending on the DBMS) without a CHECKPOINT no write to disk.

Memory Time Line T1
     R1(X)-----W1(X)
-----1---------1-------------> (t)

Memory Time Line T2
          R2(Y)------W2(Y)
----------2----------2-------> (t)

Checkpoint Time Line (C)
                 Checkpoint
-----------------C-----------> (t)

Crash Time Line CR (X)
                   Crash
-------------------X---------> (t)

If T1 decides to begin a transaction R1(X) and the commits the transaction W1(X) then everything is still in memory. At a certain point in time the DBMS will decide to write the contents of memory to disk (Transaction Log in SQL Server/UNDO - REDO Tablespace in Oracle). The Transaction Log then has knowledge of the transaction that were previously in memory. They are on disk. This includes the start of the transaction T2 with its Read R2(Y)

When the system crashes and no writes can be performed, then everything in memory is lost.

Your disk timeline (TLog / Redo - Undo TS) will look like this at the point of the crash:

Disk Time Line
     R1(X)------W1(X)
-----1----------1C-----------> (t) [T1]
          R2(Y)--|
----------2------C-----------> (t) [T2]
-----------------C-----------> (t) [Checkpoint]
-------------------X---------> (t) [Crash]

Everything after the CHECKPOINT that couldn't be written to disk is lost and everything that was CHECKPOINT-ed will either have to be COMMIT-ed or UNDO-ne.

The interval at which CHECKPOINTs are issued is controlled by the DBMS and can be modified (e.g. Microsoft SQL Server) by the DBA to prevent data loss.

A crash will end any transactions that were in memory. Any BEGIN transactions that happened after the CHECKPOINT won't be stored on disk and don't have to be UNDO-ne or REDO-ne.

Microsoft SQL Server

Database Checkpoints (SQL Server)

  A checkpoint creates a known good point from which the SQL Server Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash.

Configure the recovery interval Server Configuration Option

  The recovery interval option defines an upper limit on the time recovering a database should take. The SQL Server Database Engine uses the value specified for this option to determine approximately how often automatic checkpoints to issue automatic checkpoints on a given database.


MYSQL Calculate ranking of row that is not yet inserted I've got a table similar to this data:

|     id    | username | value |
|-----------|----------|-------|
| 1         | bob      | 46    |
| 483       | alice    | 90    |
| 176       | sue      | 3001  |
| 82        | harry    | 0     |
| 10493     | bill     | 327   |

I have this query that returns me the ranking of a user based on their id

SELECT
    username,
    value,
    rank from
    (
        SELECT
        tp.username,
        tp.value,
        tp.id,
        @curRank := @curRank + 1 AS rank
        FROM table tp,
        (SELECT @curRank := 0) r
        ORDER BY tp.value DESC
    )
as temp
WHERE id = 483;

So for the above query, I would get a ranking returned of 4 for the id 483.

Let's say I want to insert the following row into the table:

|   id   | username | value |
|---------------------------|
|    2   |  sally   | 2000  |

Is there any way to know what rank this row will have after it is inserted, without actually inserting it?

I.e. sally would have a rank of 2 from the above query if inserted.

The reason I am curious if this is possible is that I'm attempting to insert the row into the database and only have this one transaction, rather than having to insert the row, then re-run the rank query.

Thanks!

BEGIN;
INSERT the row
calculate the rank; put it into an app variable
ROLLBACK;

The ROLLBACK lets you undo the INSERT.  But meanwhile, you have grabbed the rank.

Where does the new data come from? Do you just manually type it?

If the data is coming from an application, you may declare a variable with its ID then before insert it, you make a select query that will count the rank of the new data before it's added to the table.

If you insert data one by one, then declaring variable is your best choice. You just need to change the value of the variables then you're good.

Declare Id int = 2
Declare username varchar(50) = 'sally'
declare value int = 2000

Select statement that will provide you the rank of the new data insert statement.