SQL Server 2016 SP1 CU1 Install Fails I am trying to install CU1 for SQL Server 2016 SP1 on a clustered node and it fails with an error that I am not able to find information on. In the summary log file it has the following:

  Instance MSSQLSERVER overall summary:
  Final result:                  The patch installer has failed to update the shared features. To
  determine the reason for failure, review the log files.
  Exit code (Decimal):           -2146233079
  Exit facility code:            19
  Exit error code:               5385
  Exit message:                  The given key was not present in the dictionary.
  Start time:         2017-02-14 10:02:56
  End time:                      2017-02-14
  10:03:18
  Requested action:              Patch

When I look in the detail log, I am not seeing anything that is helpful (at least to me). There are a lot of occurrences of these lines before the error,

"Sco: Attempting to..."
and
"Skipping service control for the following instance because it has one or more clustered features: MSSQLSERVER"

I installed this CU to another cluster without any issues. The only difference between them, the one that it succeeded on was 2016 Standard with SP1, and the one that does not work is 2016 Developer with SP1.

The installer that I was initially using was SQL Server 2016 Developer with SP1. When I would try and apply CU1 for SP1 it was failing. I downloaded the SQL Server 2016 Developer RTM and installed it. I then applied SP1, rebooted and applied CU1 for SP1 and that worked. What I am not sure of is why there is a difference between the two scenarios.


Get missing weeks by country I've got one table called "Units_Sold" which has these fields:
year, month, week, country, units
and another table called "Dates" which has these other fields:
year, week, month
The dates table has 1 entry per week/month/year
The Units Sold Table has more than 1 record per week, month, year as it is multiple units sales on a given week
What I'd like is to get the missing units sales weeks BY COUNTRY, this is, all the weeks in which there are no unit sales
I'm not sure how to approach this, if I join the 2 tables I get the weeks in which unit sales is present, that's not an issue, how can I get the inverse by country? that is, all weeks for each country in which there were no sales at all...

Typically using a NOT EXISTS condition is the most efficient way. But you also need to generate the combination of all possible countries and dates through a cross join before finding those that don't exist.
Without a lookup table, this would be something like:
with countries as (
  select distinct country
  from units_sold
)
select c.country, d.year, d.month, d.week
from countries c
  cross join dates d
where not exists (select *
                  from units_sold u
                  where (u.year, u.month, u.week) = (d.year, d.month, d.week))
order by c.country, d.year, d.month, d.week;

If you do have a lookup table with the countries, you can remove the CTE (with countries ...)  and replace it with the real table.


Why am I getting a "login failed" when creating this linked server? I'm creating a linked server from ServerA to ServerB, both of them are SQL Server 2008 servers. I need to create the linked server using a domain service account so that anyone will be able to utilize the linked server. ServerB only accepts Windows authentication, so I've added the service domain account to ServerB with proper permissions. I've verified the permissions by logging in with the service domain account to ServerB and everything looks great. But when I create a linked server on ServerA with the script below, I get a "login failed for user". Is this script incorrect, or am I missing something else?

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'ServerB', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'ServerB', @catalog=N'MyDatabase'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ServerB',@useself=N'False',@locallogin=NULL,@rmtuser=N'mydomain\myuser',@rmtpassword='mypassword'
GO

Log in to the serverA using "mydomain\myuser" credentials.
Run below TSQL :

EXEC master.dbo.sp_addlinkedserver
                            @server = N'serverB',
                            @srvproduct=N'',
                            @provider=N'SQLNCLI',
                            @datasrc=N'serverB',
                            @catalog=N'MyDatabase',
                            @provstr='Integrated Security=SSPI;'
GO
EXEC sp_addlinkedsrvlogin 'serverB', true
go

Check if the "serverB" exists in sys.servers :

select * from sys.servers

Check if the "serverB" works from ServerA

select * from serverB.master.dbo.sysdatabases


SQL Server 2014 login issue I have a login issue here. I created a new user login in SQL Server 2014. I tried to connect to my database using this new login and the below error pops up

  Message
  Error: 18456, Severity: 14, State: 58.

I searched for the error and found out that I have to be in mixed authentication mode. I went to properties of the server and changed it to mixed mode from security tab, restarted SQL Server and still the same issue. I tried to investigate more and when I tried the below query

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
END as [Authentication Mode]

It shows that I'm still in Windows Authentication mode, although the fact is that I changed it to mixed mode.

I am still stuck with this issue and am not able to identify the problem. Can anyone help me out?

I am connecting from SSMS, not code.

Right clicking the server and going to properties -> security, shows SQL Server and Windows.

This always happens because during setup you forgot to adjust your instance to be in both Windows and server authentication. Open SQL Server Management Studio; log in using Windows authentication; right click on your server  then properties, then security tab, and mark on both Windows and server authentication; then click ok.

Close SSMS then here is the trick:

start SQL Server configuration
stop all the services
restart all the services

then you will be able to connect in SQL Server authentication.


How to save response file in DBCA oracle 12c I have read the oracle documentation for 12c on saving response files from dbca and have found this:

  Starting with Oracle Database 11g Release 2 (11.2), you can save all
  the installation steps into a response file during installation. You
  can click the Save Response File button on the Summary page to do
  this. Later, this file can be used for a silent installation.

However, I am at the summary page in DBCA and do not see an option to record a response file. I poked around other pages in DBCA and could not find it either. Does anyone have any information on how to record a reponse file for 12c DBCA?

The above documentations refers to OUI (Oracle Universal Installer), not DBCA.
Saving response files in DBCA is available starting with version 12.2.

See the below example (Save Response File in bottom right corner):


How to grant connect and read/write permission to ALL databases? I want a SQL Server login that can READ, WRITE and View definition on all databases.

After doing:

USE master;
GRANT VIEW ANY Definition TO [a];

It looks like I have to GRANT CONNECT and read/write to each and every single database.

Isn't there an easier way?

You indeed have to add the user to the db_datareader and db_datawriter roles for each of your existing databases.

Such a query can be used for existing (and ONLINE) user databases:

DECLARE @user sysname = 'userTest1';
DECLARE @login sysname = 'userTest1';
DECLARE @SQL nvarchar(max) = '';

SELECT @SQL = @SQL + '
    USE ' + QUOTENAME(NAME) + ';

    IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name = ''' + @user + ''')
    BEGIN
        CREATE USER ' + QUOTENAME(@user)
           + ' FOR LOGIN ' + QUOTENAME(@login)
           + ' WITH DEFAULT_SCHEMA=[dbo];
    END
    ELSE
    BEGIN
        EXEC sp_change_users_login ''Update_One'', ''' + @user + ''', ''' + @login + ''';
    END

    EXEC sys.sp_addrolemember ''db_datareader'', ''' + @user + ''';

    EXEC sys.sp_addrolemember ''db_datawriter'', '''+ @user + ''';
'
FROM sys.databases
WHERE database_id > 4 AND state = 0;

--EXEC sp_executesql @sql;
print @sql

This will print the first 4000 chararacters from @sql. Comment out the EXEC line to execute it.
The user is created if it does not exist or the link to the login is updated if the user is already there
It then add the user to the db_datareader and db_datawriter roles
Any other right or role needed can be added to the query

New databases are created from model and will inherit its setting.
The User can be added to the db_datareader and db_datawriter roles on model:

use model
go
CREATE USER [userTest1] FOR LOGIN [userTest1] WITH DEFAULT_SCHEMA=[dbo];
EXEC sys.sp_addrolemember 'db_datareader', 'userTest1';
EXEC sys.sp_addrolemember 'db_datawriter', 'userTest1';

The first query can take care of both model and user database using this WHERE clause:

WHERE (database_id > 4 or name = 'model') AND state = 0;

Note (From Kenneth Fisher): With SQL Server 2014, there are some new permissions CONNECT ANY DATABASE and SELECT ALL USER SECURABLES that would cover the connect and read but unfortunately there is nothing similar yet for write permissions. (MSDN)


Using file .log.3.gz I have a file which I need to look at for errors, I got it from someone its extension is mysql.log.3.gz and when I uncompressed it, it has mysql.log.3, It is a log file? And if I open it in text editor like sublime text. It’s a huge file. But how do I really make use of this file so it is useful to clear out errors?

mysql.log files is where some distros store the MySQL error log. It contains errors, warnings, notes and every time that MySQL has started and stopped.

mysql.log.3.gz is the third archived log, and if there is no mysql.log.4.gz, the latest before the currently being written now. So, it is probably not the latest (current) log.

If you had a UNIX console, the first thing I would do is:

zgrep ERROR mysql.log.3.gz | wc -l  # count how many errors are there

zgrep ERROR mysql.log.3.gz | tail   # show the latest errors

You will see that it contains timestamps, which will give you an idea of the time frame.

zgrep and zless (using the search function /) are good tools to search for specific patterns on compressed files. There are some equivalents on Windows.

Please note that depending what was happening at the time, you may find many useless errors (already corrected) or warnings that are more important than the errors ("table missing, ignoring it").


SQL Database Mirroring Number Restriction We are thinking of incorporating Database Mirroring. Company does not have time/budget for AlwaysOn Availability Groups. We currently conduct 24hr backup/restore from one server to another secondary server in geographical location. We want to reduce 24hr gap. Will utilize Asynchronous High Performance Mode.

We have around 50 databases, that we will move from Principal Server A to Mirror Server B.
Does SQL Server have any limitations on number of databases we can incorporate?

Company only consumes 1-2TB a yr, 50 million rows max in table, 200GB memory, 50 Cores on Principles,  same with Mirror server

Thank you,

Per the official Microsoft documentation, there isn't a technical limitation on the number of databases you can mirror. The more you have, the more you will have to manage, obviously.

As noted in that link, mirroring is deprecated and will be removed from SQL Server at some point in the future. Microsoft themselves recommend against implementing a new solution using mirroring as a result. You should consider alternatives such as log shipping, which may give you the protection you need (depending upon your SLAs, RTO, RPO, etc.).


Changing autogrow settings with database mirroring Recently I changed the Autogrow settings for some of the databases on our SQL Server 2008 R2 server. These are involved in a database mirroring configuration, with the principal on SERVER1 and the mirror on SERVER2.

This week I failed over three databases- now SERVER1 is the mirror and SERVER2 is the principal for these databases. It appears that the autogrow settings did not move over to SERVER2 properly, as the databases now show that they grow by a percentage (two are set to  32768%, the other to 131072%).

This is different than the settings that used to be there (I believe it was the default- 10%), and also different that the 256MB I set on SERVER1.

To make things more confusing, this is only happening on the primary file- the secondary files and log file has retained the settings I set on SERVER1.

My suspicion is that this is a bug- I did patch SQL after changing the autogrow settings. My question is- has anyone seen this scenario before? Is there a method to make sure all of the settings are correct on both servers without failing all the databases over?

UPDATE: Using a 4th database that I'll call DB1, I set the autogrow on SERVER1 to 512MB (after failing the database over, witnessing the same issue, and failing it back). When I failed it over to SERVER2 after that, it shows growth of 65536%. The takeaway is that the is_autogrow_percent value in sys.master_files is not moving to SERVER2, but the growth value is.

UPDATE2: With DB1 on SERVER1, I changed the autogrow to 10% and failed it to SERVER2. The 10% value remained. I then repeated this, setting autogrow back to 256MB this time. Again, the growth value changed but the is_autogrow_percent did not.

I'm addressing the issue using Policy Based Management. I created a policy on the Data File facet that states all files must have @GrowthType = KB.

I also filed a connect item:
https://connect.microsoft.com/SQLServer/feedback/details/796354/autogrow-is-percent-flag-set-on-principal-does-not-persist-to-mirror

UPDATE: This is fixed in SQL Server 2008 R2 SP2 CU8 and SP1 CU13.


How can I dynamically back up all SSAS databases on a given instance? I want to dynamically back up all the databases on a given SSAS instance using a SQL Agent job (which would most likely involve executing an SSIS package). It is imperative that this is a dynamic process - if users add databases or cubes, I want to set up a job one time that can automatically detect all existing SSAS metadata.

Unfortunately, I don't see anything out there that tells me how I can automatically and dynamically back up all of the databases on an SSAS instance in a clean way. By "clean", I mean:

Using the DatabaseId in the backup command, not the DatabaseName. Sometimes there can be a difference between a DatabaseName and a DatabaseId, and if there is a difference, the backup will fail if the DatabaseName is used in place of the DatabaseId. Just querying the Catalogs schema  alone won't give me the DatabaseId.
Avoiding the need to create a linked server every time I come across a new instance to back up.

This can in fact be done. There are probably a few ways to do it, and here is a fairly straightforward example. For this solution, you will use a combination of:

A SQL Agent job with a step for each instance that needs backed up (i.e. A step for the development server, the qa server, and for production).
One dynamic SSIS package that is called in each step of the job.
An Execute Script Task that uses the Analysis Management Objects (AMO).

Create the SSIS Package

Variables

VariableName|Scope|Type

BackupDir | Package level or Foreach level | String
DatabaseId | Package level or Foreach level | String
DatabaseName | Package level or Foreach level | String
InstanceForDatasource | Package level | String

Intended for use in the Set Values tab of the SQL Agent job if more than one instance will be backed up. This variable will be used to build the ConnectionString.

ConnectionString | Package level | String

Set the EvaluateAsExpression property to true.
Set the Expression property as follows: Data Source="+ @[User::InstanceForDatasource] +";Provider=MSOLAP.4;Integrated Security=SSPI;
Please note that this approach can be expanded upon to have the entire ConnectionString be dynamic and dictated by the calling SQL Agent job step if necessary.

XMLAScript | Package or Foreach level | String

Connection Managers

Use a real connection in design time so that the metadata plays nice.

Creating the connection managers now isn't required, but it makes it easier for later. For each task in the process, you will have the appropriate connection manager available in the drop-down without the need to create any on-the-fly.

ADO.NET

Create a new ADO.NET connection manager that uses the Microsoft OLE DB Provider for Analysis Service
Using the Property Expressions Editor, set the ConnectionString property as follows: [User::ConnectionString]

OLEDB

Create a new OLEDB connection manager that uses the Microsoft OLE DB Provider for Analysis Service
Using the Property Expressions Editor, set the ConnectionString property as follows: [User::ConnectionString] + "Format=Tabular;"

Analysis Services

Create a new Analysis Services connection
Using the Property Expressions Editor, set the ConnectionString property as follows: [User::ConnectionString] = "Impersonation Level=Impersonate;"

Create the Foreach Container

Here, you will create a Foreach based on the Catalogs schema rowset. This will get us the DatabaseName for each database in the instance and the DatabaseName will be put into its corresponding variable.

Create an Execute Script Task

Set the script to use Visual Basic.

Set the ReadOnlyVariables and ReadWriteVariables as follows:

ReadOnlyVariables: User::ConnectionString,User::DatabaseName,User::InstanceForDatasource
ReadWriteVariables: User::BackupDir,User::DatabaseId,User::XMLAScript

Edit the Script

Add a reference to the Analysis Management Objects (AMO) assembly.

Right-click on the project name (should be the top-most item in the Project Explorer in the Visual Studio window that opened when you clicked Edit Script) and select Add Reference.
Add a reference to the Analysis Services Objects component. The dll for AMO should be located in <SQL Server InstallationDrive>:\Program Files\Microsoft SQL Server\<SQL Server Version>\SDK\Assemblies.

In the script header, add an Imports statement to use AMO: Imports Microsoft.AnalysisServices
Replace the contents of Public Sub Main() with the following script:

Public Sub Main()
    '
    Dim ASServer As New Microsoft.AnalysisServices.Server()
    Dim AsDatabase As New Microsoft.AnalysisServices.Database
    Dim ASConn As String = ""
    Dim ASDatabaseName As String = ""
    Dim ASDatabaseId As String = ""

    'Create a variable that uses the dynamic ConnectionString variable
    ASConn = Dts.Variables("ConnectionString").Value

    'What database are we working with in this iteration of the Foreach?
    ASDatabaseName = Dts.Variables("DatabaseName").Value

    'Use the Analysis Services AMO to get the DatabaseId for this DatabaseName.
    '   It is necessary to get the DatabaseId because the XMLA backup command requires the DatabaseId,
    '   and if at any point the database is renamed, the DatabaseId will differ from the DatabaseName.
    Try
        'Establish the connection to SSAS.
        ASServer.Connect(ASConn)

        'Get the database.
        AsDatabase = ASServer.Databases.FindByName(ASDatabaseName)

        '***ONLY IF the EstimatedSize > 0***.
        ' --> If it is 0, it could be a corrupted database which causes the backup process to stop.
        ' --> In that case, we will leave the DatabaseId variable blank to be a visiblie indicator that the db is corrupt.
        '***ONLY IF the number of cubes > 0***.
        ' --> The databse could be corrupt or backup could hang if there are no cubes.
        If AsDatabase.EstimatedSize > 0 And AsDatabase.Cubes.Count > 0 Then
            'Get the DatabaseId
            'Retrieve the DatabaseId from the Databases collection.
            ASDatabaseId = ASServer.Databases.FindByName(ASDatabaseName).ID
        Else
            Dts.Events.FireWarning(0, "Verifying database", "The estimated size and/or number of cubes in the database does not meet the requirements. Estimated Size= " & AsDatabase.EstimatedSize & "; " & "Number of cubes= " & AsDatabase.Cubes.Count, String.Empty, 0)
        End If
    Catch ex As Exception
        'Couldn't connect. Do not error out because maybe the next iteration will succeed.
        Dts.Events.FireWarning(0, "Establishing SSAS Connection", "Unable to connect to the SSAS Server with ConnectionString= '" & ASConn & "'. Error: " & ex.ToString, String.Empty, 0)
    End Try

    'Print info for output while testing/debugging.
    Dts.Events.FireInformation(0, "Establishing SSAS Connection", "Connection established. ConnectionString= '" & ASConn & "'.", String.Empty, 0, True)

    'Put the DatabaseId into the SSIS variable.
    Dts.Variables("DatabaseId").Value = ASDatabaseId

    'If we were able to retrieve the DatabaseId, then generate the backup directory and filename and the XMLA script.
    If ASDatabaseId <> "" Then

        'Print info for output while testing/debugging.
        Dts.Events.FireInformation(0, "Retrieving DatabaseId", "DatabaseId retrieved for " & ASDatabaseName & ". DatabaseId= '" & ASDatabaseId & "'.", String.Empty, 0, True)

        'Get timestamp for the backup file
        Dim timeStamp As String = Now.Year & _
                                    Right("0" & Now.Month, 2) & _
                                    Right("0" & Now.Day, 2) & _
                                    Right("0" & Now.Hour, 2) & _
                                    Right("0" & Now.Minute, 2) & _
                                    Right("0" & Now.Second, 2)

        'Get the filename and location for the backupfile
        Dim filePath As String = "\\<servershare>\" & Dts.Variables("ServerForDatasource").Value & "\" & ASDatabaseName
        Dim fileName As String = ASDatabaseName & "_" & timeStamp & ".abf"

        'Put the filepath into the SSIS variable
        Dts.Variables("BackupDir").Value = filePath

        'Print info for output while testing/debugging
        Dts.Events.FireInformation(0, "Retrieving full backup path", "BackupPath= '" & filePath & "\" & fileName & "'.", String.Empty, 0, True)

        'Build XMLA script
        Dim xmlaScript As String = ""
        xmlaScript = "<Backup xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">" & _
                            "<Object><DatabaseID>" & ASDatabaseId & "</DatabaseID></Object>" & _
                            "<File>" & filePath & "\" & fileName & "</File>" & _
                        "</Backup>"

        'Put the XMLA script into the SSIS variable
        Dts.Variables("XMLAScript").Value = xmlaScript

        'Print info for output while testing/debugging.
        Dts.Events.FireInformation(0, "Generate XMLAScript", "XMLAScript= '" & xmlaScript & "'.", String.Empty, 0, True)
    Else
        'Print info for output while testing/debugging.
        'If DatabaseId is empty, we will not perform the rest of the tasks in the Foreach container.
        Dts.Events.FireWarning(0, "Retrieving DatabaseId", "Unable to retrieve the DatabaseId for Database= '" & ASDatabaseName & "'.", String.Empty, 0)
    End If

    ' Return success
    Dts.TaskResult = ScriptResults.Success
End Sub

Create a File System Task (to create the Backup Directory)

It is important to set UseDirectoryIfExists to true to avoid an error if the backup directory already exists.

Edit the precedence constraint between the Execute Script Task and the File System Task

This precedence constraint will handle a missing DatabaseId. The DatabaseId would be missing if a connection to the SSAS server could not be established in the vbscript, or if the database is corrupted. You do not want to back up a corrupted database. It will bring the backup to a halt.

Example of Expression: `@[User::DatabaseId] !=""

Create an Analysis Services Execute DDL Task

Your final Control Flow should look like this:

Create the SQL Agent Job

Add a step for each SSAS instance that will be backed up.
Each step should be configured to execute the SSIS package.
For each step, click the Set values tab and set the value of InstanceForDatasource to the instance name for the step.

Example syntax for Property Path: \Package.Variables[User::InstanceForDatasource].Properties[Value]
(More on using the Set values tab)

Notes:

To make the entire backup directory dynamic, you would just have to add another variable for that and account for it in the vbscript.
Again, if necessary, the entire connection string variable could be set by the SQL Agent job instead of just the instance name.


Is there a way to used grouped data multiple times in a query Suppose a table contains employee id, department name and salary. I want to select the department that gives total salary greater than average salary given by all departments.

The solution that I know, is grouping data by department to find sum of salaries given by that department and then use having clause to compare the sum with the output of a subquery, that again groups by department and finds average department salary.

I want to know if it is possible to group according to departments once, and use this grouped data multiple times so that we don't have to group again and again.

The following will get you the assignment answer. There are many ways to solve this, and it is not necessarily bad to group several times in sub-queries. Most RDBMS engines will be able to optimize your query (some) and performance is likely not an issue for this particular question.

SELECT TOP (1)
     Department
    ,SumSalary
    ,AvgSalary
FROM
(
SELECT
     Department
     ,SumSalary
    ,AVG(SumSalary) OVER(PARTITION BY NULL) AS AvgSalary
FROM
(
SELECT
     Department
    ,SUM(Salary) AS SumSalary
FROM DIM.Property
GROUP BY Department
) SubQuery
) x
WHERE SumSalary > AvgSalary
ORDER BY SumSalary DESC

I'd suggest that you read up on Window Functions so that you can fully explain any work submitted (there are plenty of sites and technical documentation for each DB and ANSI SQL).

Also note that a TOP (1) gets just the single highest paying department that is above the average, since you may have many that meet this criteria. To ensure you get the highest paying the ORDER BY guarantees the highest paying is on top. You still have the possibility of two departments paying the same amount though in which case you have a non-deterministic query (not guaranteed to get the same department every time). You need a tie-breaker unless you accept this potential for random results or it is guaranteed not to happen by some other business process or rule (yeah right!).


PostgreSQL LDAP authentication We are setting up a PostgreSQL 9.1 database at work. There are no classified data in the database, but we want to know who is doing what. We are using LDAP authentification on the network, so we would like to use that for the database as well, but according to the documentation, all users anyhow need to be defined as database users.

We could of course do a "dump" of users to the database, but we would prefer if it could be possible to define users in the database as soon as they have authenticated in LDAP. This would not be too difficult using a wrapper script, but is it somehow possible to do this directly? (Most users log in using psql.)

I'm not very familiar with LDAP (beware of understatement!), but I'm pretty sure there is no way to do this automatically on the PostgreSQL side.  If you create your LDAP users in a GUI then probably you want to set up a cron script to export the users to the database (I don't know if you can define trigger-like behaviour there, I mean something which would fire a user-creation DB script once a user is created for LDAP).  If you do this from a script then it is quite easy to solve the problem, as you already mentioned.


Are the ALL_OBJECTS and DBA_OBJECTS different when you have DBA Role? I am trying to fully understand the difference between ALL_OBJECTS and DBA_OBJECTS.
I am still new in Oracle and get confused by the whole Access/Roles thing.

From here:

ALL_OBJECTS shows all objects of current and those object which you have rights to access.
DBA_OBJECTS shows all object of all users .

My question is, if you have DBA role, should ALL_OBJECTS be equal to DBA_OBJECTS?
I have DBA role and in the Database I am working on and ALL_OBJECTS returns fewer elements than DBA_OBJECTS.

If I have DBA Role shouldn't I have access to all the objects in DBA_OBJECTS and therefore both views should be equal?

First let's define ALL_OBJECTS and DBA_OBJECTS in oracle:

ALL_OBJECTS describes all objects accessible to the current user.
DBA_OBJECTS describes all objects in the database

As result if you select everything they are different because there are multiple levels of DBA privileges on Oracle. For example if you have SysDBA privileges then you have much more access than a normal DBA.


Why does RDS PostgreSQL always recover on restart? I am running an RDS instance of PostgreSQL 9.6.3: select version(); returns PostgreSQL 9.6.3 on x86_64-pc-linux-gnu [...].

I have found that after issuing a stop from the RDS console followed by a start the database always reports that the database system was not properly shutdown, requiring recovery. This behavior has been independently verified by at least one other RDS PostgreSQL user (https://forums.aws.amazon.com/message.jspa?messageID=809401#809401).

The query

select name, setting
from pg_settings
where name in ('fsync', 'wal_sync_method', 'synchronous_commit');

reports that fsync = on, wal_sync_method = fdatasync (correct for a Linux system), and synchronous_commit = on.

I see the following in the logs after issuing a stop:

2017-10-12 16:37:36 UTC::@:[3464]:LOG: received fast shutdown request
2017-10-12 16:37:36 UTC::@:[3464]:LOG: aborting any active transactions
2017-10-12 16:37:36 UTC::@:[3515]:LOG: autovacuum launcher shutting down
2017-10-12 16:37:36 UTC::@:[3512]:LOG: shutting down
2017-10-12 16:37:36 UTC::@:[3512]:LOG: checkpoint starting: shutdown immediate
2017-10-12 16:37:36 UTC::@:[3512]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=0.007 s, sync=0.002 s, total=0.145 s; sync files=1, longest=0.002 s, average=0.002 s; distance=16384 kB, estimate=16402 kB
2017-10-12 16:37:36 UTC::@:[3464]:LOG:  database system is shut down

And the following after issuing a start:

2017-10-12 17:05:33 UTC::@:[3293]:LOG: database system was interrupted; last known up at 2017-10-12 16:37:50 UTC
2017-10-12 17:05:33 UTC::@:[3293]:LOG: database system was not properly shut down; automatic recovery in progress
2017-10-12 17:05:33 UTC::@:[3293]:LOG: redo starts at 165/1160
2017-10-12 17:05:33 UTC::@:[3293]:LOG: unexpected pageaddr 164/D2000000 in log segment 000000010000016500000003, offset 0
2017-10-12 17:05:33 UTC::@:[3293]:LOG: redo done at 165/20000A0
2017-10-12 17:05:33 UTC::@:[3293]:LOG: last completed transaction was at log time 2017-10-12 16:50:53.823582+00
2017-10-12 17:05:33 UTC::@:[3293]:LOG: checkpoint starting: end-of-recovery immediate
2017-10-12 17:05:33 UTC::@:[3293]:LOG: checkpoint complete: wrote 2 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 3 recycled; write=0.029 s, sync=0.002 s, total=0.046 s; sync files=2, longest=0.002 s, average=0.001 s; distance=49147 kB, estimate=49147 kB
2017-10-12 17:05:33 UTC::@:[3293]:LOG: MultiXact member wraparound protections are now enabled

Given my understanding of PostgreSQL's (fast) shutdown and startup sequences, the above log messages seem to indicate that PostgreSQL is writing and completing a final checkpoint before shutdown, and then successfully shutting down.

However, based on the code at xlog.c line 6023 and xlog.c line 6503 (https://github.com/postgres/postgres/blob/ca9cfed883333d5801716eb01cf28b6b5be2b5cd/src/backend/access/transam/xlog.c; can't post more than 2 links), corresponding to the database system was interrupted [...] and database system was not properly shut down [...] log lines, respectively, it would appear that at least the pg_control file was not flushed to disk. This worries me, as I'm expecting Amazon have taken care to make sure that the volumes on which PostgreSQL data and log files are stored are fit-for-purpose (i.e. they don't report fsync completion until it is really complete).

Is there another explanation for this behavior?

An answer to this question was provided on the AWS Developer Forums thread to which it was originally posted.
Summary
RDS PostgreSQL (as of the versions available in April 2018) doesn't always recover after restart; however, if the RDS instance does not shutdown within an unspecified timeout, RDS will force terminate the instance, requiring PostgreSQL to recover on reboot.
Full text of reply

Thanks for using RDS!
I saw this thread referenced somewhere else and thought I'd give a quick input. I work closely with the PostgreSQL service team and I can confirm that the behavior you're observing is correct. As of today (April 2018) it's the case that sometimes, if shutdown hasn't completed quickly enough, the RDS automation will time-out and force termination. Various behaviors like this have in fact existed for a long time, so you may have observed it before. In the handful of specific cases that have been discussed on this thread, I can't really comment on why PostgreSQL (or other database engines) took longer than usual to shut down - there are probably many different possible causes for this.
To be clear, the specifics about if and how and when a force-termination occurs might change over time depending on many variables. What will never change is the RDS team's commitment to durability and availability of customers' data. Meaning that (1) our engineers carefully design with safety in mind, avoiding things that might increase risk of corruption in any way and (2) our engineers are always mindful that downtime - whether measured in minutes or seconds or milliseconds - is worth minimizing as far as safely possible.
I hope this helps, to hear that what you're observing is there by design!
-Jeremy


DB2 LUW: Should temporary tablespaces share bufferpools with other tablespaces? We are in the process of tuning a massive application our company has just written. Part of that tuning includes tuning the databases. We run DB2 LUW on AIX. Specifically we are at 9.7 Fix Pack 4 (although we hope to migrate to 10.1 Fix Pack 2 sometime this year).

We initially built out tablespaces only needed by the size of the data (ie, if all the tables fit in a 4K tablespace, we only created 4K tablespaces, our thought being why create tablespaces to take more disk if they are unused). This was the same for temporary tablespaces as well.

The developers have told us that they have achieved better performance by adding in a 32K temporary tablespace. So all their tables are going into 4K, 8K, or 16K tablespaces. Yet they added a 32K temporary (which has its own bufferpool) and they claim they halved some of the transaction times of their application.

When I think about it, I suppose that makes sense. I am guessing the optimizer sees the 32K space as a great place to perform joins/sorts and has more memory at its disposal being 32K vs 4K.

My fellow colleage said he (regardless of the above), he had read somewhere that DBAs should always place temporary tablespaces into their own bufferpools. When I asked him for the links to read up on it, he couldn't remember.

I am wondering...should temporary tablespaces always get their own bufferpools? Does this help with join/sort performance? Is that a good practice?

And secondly on the side, does it make sense to always create a 32K bufferpool and 32K temporary tablespaces for those join/sorts?

First of all, you must have a bufferpool that matches the page size of the tablespace. By that token if the temporary tablespace is the only one with the 32K page size then it will have the bufferpool for its exclusive use.

If you have other tablespaces with the 32K page size, only monitoring the system performance will tell you if there may be a benefit from a separate temp space bufferpool.

You can use select * from sysibmadm.mon_bp_utilization to look at the bufferpool hit ratios and select * from table (mon_get_bufferpool(NULL,NULL)) to check page cleaner activity (POOL_NO_VICTIM_BUFFER and POOL_DRTY_PG_STEAL_CLNS should ideally be 0). If you see that a drop in the bufferpool hit ratio or high dirty page contention coincides with the temporary space use (a spike in POOL_TEMP_DATA_L_READS in select * from table (mon_get_tablespace(NULL,NULL))), creating a separate bufferpool of an appropriate size for the temporary tablespace in question might be useful.


Query whith DATETIME How to get this result

expected result

CodeCompany      fechaActual    Clave  Usuario  Depto   entrada        salida       sumaHoras
2300                201712       307    USER1   TTT    06:00:04       14:04:12         8

This is my Query

DECLARE @Hoy DATETIME
DECLARE @Hoy1 DATETIME
DECLARE @Hoy2 DATETIME

SELECT  @Hoy = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))
SELECT  @Hoy1 = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))+'00:00:00'
SELECT  @Hoy2 = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))+'23:59:59'

select c.CodeCompany,
       CONVERT(char(6), @Hoy, 112) AS fechaActual,
       a.idClave,
       c.nombre + ' ' + c.apellidos as Nombre,
       d.Descripcion as Depto,
       CONVERT (VARCHAR(20), @Hoy1,108) fechaEntrada,
       CONVERT (VARCHAR(20), @Hoy2,108) fechaSalida,
       DATEDIFF(HOUR, fechaEntrada, fechaSalida) %24 AS horasAlDia
from   tblasistencia a
JOIN   tblpersonal c
ON     a.idClave = c.idClave
JOIN   tblDepto d
ON     c.fkDepto = d.idDepto
where  fechaEntrada BETWEEN CONVERT(DATETIME, @Hoy1,103)
                    and CONVERT(DATETIME, @Hoy2,103)
and    fkStatus != 1

unexpected result

CodeCompany      fechaActual   Clave   Usuario   Depto     entrada      salida   sumaHoras

2300             201712        10120   USER1      TTT    00:00:00      23:59:59     8

The result is unexpected because not show the hour entry and exit time

Replace

CONVERT (VARCHAR(20), @Hoy1,108) fechaEntrada,
 CONVERT (VARCHAR(20), @Hoy2,108) fechaSalida,
DATEDIFF(HOUR, fechaEntrada, fechaSalida) %24 AS horasAlDia

with

CONVERT (VARCHAR(20), Entrada,108) fechaEntrada,
 CONVERT (VARCHAR(20), Salida,108) fechaSalida,
DATEDIFF(HOUR, Entrada, Salida) %24 AS horasAlDia

Here is the script:

DECLARE @Hoy DATETIME
DECLARE @Hoy1 DATETIME
DECLARE @Hoy2 DATETIME

SELECT  @Hoy = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))
SELECT  @Hoy1 = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))+'00:00:00'
SELECT  @Hoy2 = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))+'23:59:59'

 select c.CodeCompany,
 CONVERT(char(6), @Hoy, 112) AS fechaActual,
 a.idClave,
 c.nombre + ' ' + c.apellidos as Nombre,
 d.Descripcion as Depto,
 CONVERT (VARCHAR(20), Entrada, 108) fechaEntrada,
 CONVERT (VARCHAR(20), Salida, 108) fechaSalida,
 DATEDIFF(HOUR, Entrada, Salida) %24 AS horasAlDia
  from tblasistencia a JOIN tblpersonal c ON a.idClave=c.idClave
 JOIN tblDepto d ON c.fkDepto=d.idDepto
 where
   fechaEntrada BETWEEN CONVERT(DATETIME, @Hoy1,103) and
    CONVERT(DATETIME, @Hoy2,103)and fkStatus !=1


Will frequent small select queries slow down frequent write queries in InnoDB table in MySQL? I'm running a MySQL database with tens of tables in remote server. To monitor the data, I write a small program to query to database via VPN connection every 10 seconds. Each query is a union of 20+ select sub-queries from 20 different tables with the same structure. A server program receives data from external sources and writes thousands of records each second to a table I don't query.

What's the impact of my queries to the database? What if I query every second? Do they slow down the whole database notably or slow down the writing of the table I don't query?

Since the remote database is working in a production system, it is not allowed to stop working. Do my queries create any risk that might shut down the whole database?

The only way to know is to benchmark it.  If your query is fast, the chance of any notable impact on other queries is low, assuming your database server has sufficient disk bandwidth, multiple CPU cores, sufficient memory, and an appropriate innodb_buffer_pool_size setting for both the working dataset and the hardware.

If you don't already have a replica ("slave") server, then you should consider implementing that -- a slave server imposes a relatively minimal load on the master, the select queries could be run against the replica, and, of course, you would then have an alternate server you could use in the event of a catastrophic failure of the master server.

Use EXPLAIN SELECT ... to understand how the server is actually processing your query, and then consider whether it needs to be optimized or rewritten, particularly if it is doing things like full table scans or temporary tables.

When benchmarking your query, remember to use SELECT SQL_NO_CACHE ..., otherwise the performance may be deceptively fast if none of the underlying tables has changed between subsequent invocations.


Should I shrink the Log File I am a developer but have to do DBA stuff now and then.

We had a problem where the Transaction log files of our DBs where increasing to large size of 50GB while the data file (MDF) was only 10-15 GB.

So I tracked the issue to the file size, the Autogrowth was set to 10% for Log File

According to the Start Kit by BrentOzar, they recommend setting the file size to:

256 MB for Data File
128 MB for Log File

Which I have done.

Currently the DBs are in Simple Recovery Mode - Which I intend to change to Full Recovery Mode (and then take regular log backups)

But because the Log Files have grown so large, there is no space on the Hard Disk - I need to make up some space,

The Question is:

Is it okay for me to Shrink some of the log files while they are in Simple Recovery Mode? Because there is alot of free space in some of them

like this one has 99% free space

Another:

Irrespective of Recovery model,shrinking wont help you if there is an active transaction or due to the  reasons mentioned here

Transaction log space will be reused in all recovery models irrespective of Size shown in file Explorer,you may still have free space..

Here is what MSDN has to say about Log space reuse..

  Log truncation frees space in the log file for reuse by the transaction log. Log truncation is essential to keep the log from filling. Log truncation deletes inactive virtual log files from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the Physical transaction log. If a transaction log were never truncated, it would eventually fill all the disk space that is allocated to its physical log files.

How the log space will  be reused is different in different Recovery Models

1.In simple recovery Model,Log Truncation will happen after checkpoints which occur due to many factors as discussed here
2.In Full Recovery Model,TLOG space will  be reused only with Backups of Tlog

So even in simple recovery model,Your log space  has grown huge which means there is some thing which is preventing logspace reuse..so changing to Full Recovery Model also won't help unless you troubleshoot what is the cause..

You can use below query to see what is Preventing TLOG space reuse..

select log_reuse_wait_desc from sys.databases

Further shrinking Log file is bad for many reasons some of them include
1.file growth operations are expensive
2.Instant file Initialisation wont help for log files

Further Readings:
https://www.red-gate.com/library/sql-server-transaction-log-management


Generate a sequence of timestamps but skip given periods I have a table things_to_do in PostgreSQL. For example:

thing_id
---------
 1
 2
 3
 4
 5
 6

And I want to add a timestamp to every row:

SELECT thing_id, '2016-02-10 19:57:00'::timestamp
                  + '0:01:00'::interval * row_number() OVER () AS estim_date
FROM things_to_do
ORDER BY 1;

 thing_id |    estim_date
----------+---------------------
 1        | 2016-02-10 19:57:00
 2        | 2016-02-10 19:58:00
 3        | 2016-02-10 19:59:00
 4        | 2016-02-10 20:00:00
 5        | 2016-02-10 20:01:00
 6        | 2016-02-10 20:02:00

Basically, I'm doing one thing every minute. But I don't want to work after 20:00 and not before 09:00. I would like my request to skip the night.

The question is:

Is it possible to give my request a period of time and skip that time? Some sort of modulo, maybe?

The final result I would like to have:

 thing_id |    estim_date
----------+--------------------
 1        | 2016-02-10 19:57:00
 2        | 2016-02-10 19:58:00
 3        | 2016-02-10 19:59:00
 4        | 2016-02-11 09:00:00
 5        | 2016-02-11 09:01:00
 6        | 2016-02-11 09:02:00

You can assign any numbers with a derived table. generate_series() is instrumental for a simple solution.

With a table like this:

CREATE TABLE things_to_do AS
SELECT * FROM generate_series (1,6) AS thing_id;

A simple solution for the given example

SELECT *
FROM   things_to_do
JOIN (  -- derived table with prepared timestamps
   SELECT row_number() OVER () AS thing_id, *
   FROM  (
      SELECT *
      FROM   generate_series(timestamp '2016-02-10 19:57'
                           , timestamp '2016-02-10 19:59'
                           , interval  '1 min') estim_date
      UNION ALL
      SELECT *
      FROM   generate_series(timestamp '2016-02-11 09:00'
                           , timestamp '2016-02-11 09:02'
                           , interval  '1 min') estim_date
      ) sub
   ) t USING (thing_id)
ORDER  BY 1;

Just append two sets ..

For a more general solution:

SELECT row_number() OVER (ORDER BY d, ts) AS id
     , ts + interval '1 day' * d AS ts
FROM   generate_series(timestamp '2016-02-10 09:00'
                     , timestamp '2016-02-10 19:59'
                     , interval  '1 min') ts
     , generate_series(0,6) d
ORDER  BY 1;

Produces a table with one row per minute from 09:00 to 19:59 for seven consecutive days starting at 2016-02-10 and an id number starting from 1 ...

Or:

SELECT row_number() OVER (ORDER BY d, ts) AS id
     , d + ts::time AS ts
FROM   generate_series(timestamp '2000-01-01 09:00'
                     , timestamp '2000-01-01 19:59'
                     , interval  '1 min') ts
     , generate_series(timestamp '2016-02-10 0:0'
                     , timestamp '2016-02-17 0:0'
                     , interval  '1 day') d
WHERE  d > '2016-02-10' OR ts > '2000-01-01 19:56'  -- start at '2016-02-10 19:57'
ORDER  BY 1;

Or:

SELECT row_number() OVER (ORDER BY ts) AS id
     , ts
FROM   generate_series(timestamp '2016-02-10 19:57'
                     , timestamp '2016-02-17 19:59'
                     , interval  '1 min') ts
WHERE  ts::time BETWEEN '08:00' AND '19:59'
ORDER  BY 1;


How to restore a transaction log Could you tell me the reason why it is mandatory to restore the full backup with NORECOVERY option before restoring the transaction log? otherwise Transaction log is always grayed (Task -> restore -> Transaction log)

Thanks a lot in advanced

If you perform a full RESTORE WITH RECOVERY, that means you want to only restore the full backup and immediately bring the database online.

When you perform a full RESTORE and want to also restore differential or transaction log backups as well, then you want to RESTORE WITH NORECOVERY so that the database does not come online and additional restores are possible.

You can use T-SQL to perform both at the same time (copied from BOL):
Restore Transaction Log Backup

RESTORE DATABASE AdventureWorks2012
   FROM AdventureWorks2012_1
   WITH NORECOVERY;
GO
RESTORE LOG AdventureWorks2012
   FROM AdventureWorks2012_log
   WITH FILE = 1,
   NORECOVERY;
GO
RESTORE LOG AdventureWorks2012
   FROM AdventureWorks2012_log
   WITH FILE = 2,
   NORECOVERY;
GO
RESTORE LOG AdventureWorks2012
   FROM AdventureWorks2012_log
   WITH FILE = 3,
   NORECOVERY;
GO
RESTORE DATABASE AdventureWorks2012
   WITH RECOVERY;
GO


Representation of circular references constraint We have a database where we store commands and properties for devices that may be controlled via a network.

For instance, "Concrete Device" is an instance of some DeviceType, which in turn contains commands and properties.

We have the ability to get values of the properties of devices by analyzing responses on commands; therefore there is a relationship between commands and properties. A problem arises when we trying to link commands from one DeviceType to properties from another DeviceType - this kind of link is useless and confusing.

How can I prevent this 'circular reference' through integrity constraints?

Here is a SqlFiddle for my scenario.

All you need is to propagate TypeId down to the link table:

CREATE TABLE Commands(
    CommandId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL,
    DeviceTypeId int NOT NULL REFERENCES DeviceTypes(TypeId),
    CONSTRAINT UniqueCommandNameInSpecificType UNIQUE(Name, DeviceTypeId),
-- added this constraint
    CONSTRAINT Commands_FkTarget UNIQUE(CommandId , DeviceTypeId)
);

CREATE TABLE DeviceProperties(
    PropertyId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL,
    DeviceTypeId int NOT NULL REFERENCES DeviceTypes(TypeId),
    CONSTRAINT UniquePropertyInSpecificType UNIQUE(TypeId,Name),
-- added this constraint
    CONSTRAINT DeviceProperties_FkTarget UNIQUE(PropertyId , DeviceTypeId)
);

CREATE TABLE DevicePropertiesInCommands(
    CommandId int NOT NULL,
    PropertyId int NOT NULL,
        DeviceTypeId INT NOT NULL,
    OrderNoOfProperty int NOT NULL,
    PRIMARY KEY(CommandId, PropertyId),
    CONSTRAINT UniqueOrderNoInSpecificCommand UNIQUE(CommandId,OrderNoOfProperty),
        CONSTRAINT FK_DevicePropertiesInCommands_Commands
            FOREIGN KEY(CommandId , DeviceTypeId ) REFERENCES Commands(CommandId , DeviceTypeId ),
        CONSTRAINT FK_DevicePropertiesInCommands_DeviceProperties
            FOREIGN KEY(PropertyId , DeviceTypeId ) REFERENCES DeviceProperties(PropertyId , DeviceTypeId )
);


How a Transaction is atomic? I am really confused as to how to digest the fact that a transaction is atomic. If a transaction is set of "queries" then how will it be atomic. I am relating the word "queries" to a general SQL query. Therefore a transaction then becomes nothing but a set of SQL queries executed at the same time. But the fact that each query multiple operations, I am really not getting as to how the previously executed queries(in the same set) roll back if an error occur in a later query. What am I missing here?
Thank you!

TL;DR; The transaction log contains all information necessary to re-create or undo each transaction, depending on whether it contains a commit record for that transaction or not.

In SQL databases transaction atomicity is implemented most frequently using write-ahead logging (meaning that the transaction log entries are written before the actual tables and indexes are updated).

Queries in the strict sense of the word, that is, SELECT statements and other read operations that do not change the database state, are not logged as there is nothing to commit or roll back, and as such the concept of atomicity does not really apply here, at least at the DBMS level. The application consuming query results might need to enforce atomicity by discarding results of previous queries if one of the subsequent queries fail and cause the rollback.

When DML statements (INSERT, UPDATE, DELETE, MERGE) and other statements that change the database state get executed, the changes they make are first written into the write-ahead log (WAL). If all statements succeeds and a commit (explicit or implicit) is issued, this fact is also recorded in the WAL, and the log is persisted. This makes the changes durable. They may be written out to the actual table and index segments at some later time, during a checkpoint or log replay.

However, if one of the statements fails, there would be no commit record in the log for this transaction, and the DBMS will undo table and index changes made so far using the preceding log records of that transaction.

Similarly, if the DBMS process crashes, or the power goes out, there won't be a commit record for the in-flight transaction either, and the table or index changes that may have already been persisted will be undone during log replay.


Methods for identifying differences in accumulating history table? This question will probably be elementary to most DBAs.

I'm doing light DB work which isn't my specialty and I'd like to create some kind of "change log" for a table that is copy/appended to a history table each morning. The table is being created in the fashion below, where the entire contents of one table is being appended to a history table with a column on the end called backup_date.

I wrote some SQL to catch new records and deleted records from the previous day but what is the best method to determine what records have changed?

For example: on March 9th, Mr. Gates told HR that he prefers to go by "Bill" instead of "William".  Data for March 10th shows that HR made a change to his first name.  What is the best method of attack to compare to previous data in the table?  I'm mostly interested in comparing to the previous day (each day) but what if I wanted a list of all changes to a particular record?

Using your current method you might use a query such as the one below.  This example uses derived tables to treat each backup date range as if it where a table.  Then joins two backup date derived tables (named Today and Previous) on the emp# (I have used EmpNo in my example) and uses the WHERE clause to get only records with at least on difference between the two.  Note that the syntax for derived tables may vary depending on what database product you are using.  Adding the database product to the question might help the question be answered more accurately.

SELECT
    Today.EmpNo
    , Today.First AS TodayFirst
    , Today.Last AS TodayLast
    , Today.DOB AS TodayDOB
    , Previous.First AS PreviousFirst
    , Previous.Last AS PreviousLast
    , Previous.DOB AS PreviousDOB
FROM
    (
        SELECT *
        FROM employeehist
        WHERE BackupDate = '3/10/2014'
    ) AS Today

    INNER JOIN
    (
        SELECT *
        FROM employeehist
        WHERE BackupDate = '3/9/2014'
    ) AS Previous
        ON ( Today.EmpNo = Previous.EmpNo )

WHERE  ( Today.First != Previous.First OR Today.Last != Previous.Last OR Today.DOB != Previous.DOB );

To find all versions of a particular employee record use DISTINCT.  This will return unique versions of each record.

SELECT DISTINCT First, Last, DOB
FROM employeehist
WHERE EmpNo = 1;

You might also consider creating a trigger on the employee table which will only write records to the history table when changes are made. This would allow you to track all edits to an employee without having to compare each backup version to every other backup version.


Finding rows that are not duplicate I have two DB2 tables.

Table 1, called EmployeeFile

EmployeeFile has the following columns: EMP_ID, FNAME, LNAME, DOB

The data looks like this:

   EMP_ID, FNAME, LNAME, DOB
 - GB2342, John,  Smith, 1970-12-10
 - L3243C, Jane,  Jones, 1969-11-09

Table 2, called OriginalData

OriginalData has the following columns: INTERNAL_ID, FNAME, LNAME, DOB.

The data looks like this:

   INTERNAL_ID, FNAME, LNAME,    DOB
 - 6575678588,  Jane,  Jones,    1969-11-09
 - 1232145564,  Rob,   Mitchell, 1968-10-08
 - 3452345345,  John,  Smith,    1970-12-10
 - 6786478568,  John,  Smith,    1970-12-10

I want to show all rows of EmployeeFile for which the (FNAME, LNAME, DOB) combination occurs only once in OriginalData.

For example, if I ran the query on the example dataset shown above, it should return just the following row:

Jane, Jones, 1969-11-09

I don't want 'John Smith' because although he exists in EmployeeFile, his (FNAME, LNAME, DOB) combination occurs twice in OriginalData.

I don't want 'Rob Mitchell' because he doesn't exist in EmployeeFile.

Question

How do I do this?

The only way I can think of is immensely ugly: Concatenate (FNAME, LNAME, DOB) (with some delimiter like ~) from both tables, do a join on that column, do a group by having count(that concatenated column) = 1, then do another select on EmployeeFile, selecting only those rows where the (FNAME, LNAME, DOB) match the parsed substrings of that concatenated string!

Pretty straightforward:

select e.*
from EmployeeFile e
inner join
(
  select FNAME, LNAME, DOB
  from OriginalData
  group by FNAME, LNAME, DOB
  having count(1) = 1
) o
on (e.FNAME, e.LNAME, e.DOB) = (o.FNAME, o.LNAME, o.DOB)


New and old passwords on same MySQL database? Is it possible to set up a user in MySQL 5.5 with a new password whilst keeping existing users with the old type of passwords? By old and new passwords I mean the style of hash.

I was thinking I could try specify the hash directly but I'm struggling to get that working (I'm new MySQL).

Unfortunately I can't update all users to the new type of passwords as it would break access to a system I can't control. I also need the default new account to use the old style hash.

It is possible before version 5.7, where it has been disabled.

To set up an old password for a user, use command:

SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('mypass');

More information: Password Hashing in MySQL.

You can enable/disable old passwords globally or per session. On a server where they are enabled globally (i.e. the server was started with old_passwords = 1), run this:

SET @@session.old_passwords = 0;
SET PASSWORD FOR 'test'@'localhost' = PASSWORD('mypass');

to disable them for a session. The PASSWORD in the above snippet will return a password in the new format, affecting only the current session.


What is the technical meaning of "Bulk" in the “Bulk-Logged Recovery Model” and why this name? With this model there are certain bulk operations such as BULK INSERT, CREATE INDEX, SELECT INTO, etc... that are not fully logged in the transaction log and therefore do not take as much space in the transaction log.

What is the technical meaning of "Bulk" in the "Bulk-Logged Recovery Model"? I need clarity on what-all is classified as bulk please. Is every bulk insert, create index, select into classified as bulk or does it depend on volume of data involved?

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

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

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

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

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


Secure database access from particular applications I have an C#.NET application and a SQL Server database (SQL Server 2008 R2) and would like to restrict the access to some of the Tables when it has been accessed from some other application rather than my application.

We tried to use a CLR trigger for this. But we couldn't make it. Please give some immediate suggestions. It would be much appreciated!

This is a prime reason why different applications should be using different principals to authenticate on.  If you use Login1 for 5 applications, then it would become a nightmare to manage security if you ever need those 5 different applications to have different security schemes.

My recommendation?  Have a separate login for both of those applications.  And then grant the specific permission sets to each of those logins (either directly or through roles).

It is worth noting that a common solution to your problem would be by implementing logic to check for the application name that is passed to SQL Server, but I'm not even going to expand on that solution because it is very easily circumvented.  You can basically tell SQL Server that you are Application1 in an ad hoc manner.  That approach isn't secure whatsoever.  Because of that, I don't recommend you implement this logic.


How to fix a MySQL server with a full hard drive? Overnight, our MySQL server completely filled the hard drive. Now, I cannot login via phpMyAdmin

`Cannot log in to the MySQL server`

or via the command line

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

or via mysqladmin

mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'

There is one big table I can delete as a temporary fix if I can login to the server, but I can't. How can I login?

You did not need to erase the large tables. Why ???
Here is the strange thing about mysql. mysqld uses MyISAM temp tables for it internal operation. When mysqld runs out of space, it does not crash. IT FREEZES !!!
The following is  my answer to 2 year old post "Site Offline" MySQL server failing to start and stop
EXCERPT
According to MySQL 5.0 Certification Study Guide

Page 408,409 Section 29.2 Bulletpoint 11 says:

If you run out of disk space while adding rows to a MyISAM table, no
error occurs. The server suspends the operation until space becomes
available, and then completes the operation.

In reality, mysqld is not dead. It is waiting for free diskspace to continue whatever it normally does. Even during startup, if mysqld makes a temp table, it is a MyISAM table. No space left on the disk? No problem. MyISAM just patiently waits for you to make free space.
END OF EXCERPT
All you had to do was manually delete the binary logs. Then, mysqld would start to function normally. Even deleting just 10 logs would have freed 1G and would make mysqld get going again.
You need to do the following:
Login to mysql and run
mysql> RESET MASTER;
mysql> SET GLOBAL expire_logs_days = 7;

Then, add this to my.cnf
[mysqld]
expire-logs-days = 7

Now, mysqld will automatically delete any binary log older than 7 days.


Latest value of each GROUP with INNER JOIN I'm using sensors to get temperature and humidity every X minutes.
I've one table for sensors and one table for their results.
tb_sensors:

tb_results:

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

I'm expecting to get:

but the result is:

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

Most database systems won't even allow the syntax you wrote because it's a little non-sensical from the database engine's perspective. But if I understand you correctly, essentially you want the row with the MAX(date_res) for each sensor.
The way to accomplish this might vary depending on which database system you're using, so you should always tag your question with the database system and version, but here is an answer that should be applicable to most relational database systems:
WITH CTE_Results_Sorted AS
(
    SELECT fk_sens, temp_res, humi_res, date_res, ROW_NUMBER() OVER (PARTITION BY fk_sens ORDER BY date_res DESC) AS SortId -- Generates a unique ID (assuming each date is unique per sensor) for each sensor result, sorted by date descending
    FROM tb_results
)

SELECT id_sens, pos_sens, temp_res, humi_res, date_res
FROM tb_sensors
INNER JOIN CTE_Results_Sorted results ON tb_sensors.pk_sens = results.fk_sens
WHERE SortId = 1 -- Filter out everything but the latest date results row per sensor
ORDER BY id_sens


Connect to Microsoft Access database via Microsoft SQL Server / MySQL A client has an Microsoft Access database where they save their products.
To access the database now you have to login via VPN and open Microsoft Office Access.
I need to access this database from a PHP script running on another server.

I've done some research and found that it's not possible to connect to an Access database like a MySQL database.
So what I wonder is if it may be possible to set up an Microsoft SQL Server on the same server that uses the Microsoft Access database. So I can connect to the Microsoft SQL Server with the PHP script. Or if it's possible MySQL.

They are running Microsoft Windows Server 2008 and Microsoft Office Access 2003.

An access database is not a server, it's just a file.  To access it, your server will need access to the filesystem on which it resides.  Or, if you want to set up an SQL server on the same machine as the access database, it will still need access to the folder in which the file is stored.

A user logged into a VPN may not have the same drive mappings or file system permissions as a server process running on the same machine, so you will need to reflect this in the path to the file you use in the code.


SQL counting distinct over partition I have a table with two columns, I want to count the distinct values on Col_B over (conditioned by) Col_A.

MyTable

Col_A | Col_B
A     | 1
A     | 1
A     | 2
A     | 2
A     | 2
A     | 3
b     | 4
b     | 4
b     | 5

Expected Result

Col_A   | Col_B | Result
A       | 1     | 3
A       | 1     | 3
A       | 2     | 3
A       | 2     | 3
A       | 2     | 3
A       | 3     | 3
b       | 4     | 2
b       | 4     | 2
b       | 5     | 2

I tried the following code

select *,
count (distinct col_B) over (partition by col_A) as 'Result'
from MyTable

count (distinct col_B) is not working.
How can I rewrite the count function to count distinct values?

This is how I'd do it:

SELECT      *
FROM        #MyTable AS mt
CROSS APPLY (   SELECT COUNT(DISTINCT mt2.Col_B) AS dc
                FROM   #MyTable AS mt2
                WHERE  mt2.Col_A = mt.Col_A
                -- GROUP BY mt2.Col_A
            ) AS ca;

The GROUP BY clause is redundant given the data provided in the question, but may give you a better execution plan. See the follow-up Q & A CROSS APPLY produces outer join.

Consider voting for OVER clause enhancement request - DISTINCT clause for aggregate functions on the feedback site if you would like that feature added to SQL Server.


View locks acquired during query execution (SQL Server) Query execution plan does not show the locking details by default, is it possible to view the locks, along with the type, acquired during the execution of a query?

is it possible to view the locks, along with the type, acquired during the execution of a query?

Yes, for determining locks,

You can use beta_lockinfo by Erland Sommarskog

beta_lockinfo is a stored procedure that provides information about processes and the locks they hold as well their active transactions. beta_lockinfo is designed to gather as much information about a blocking situation as possible, so that you can instantly find the culprit and kill the blocking process if the situation is desperate. Then you can sit back and analyse the output from beta_lockinfo to understand how the blocking situation arose and figure out what actions to take to prevent the situation from reoccurring. The output from beta_lockinfo shows all active process as well as passive processes with locks, which objects they lock, what command they last submitted and which statement they are executing. You also get the query plans for the current statements. Normally, you run beta_lockinfo to look at the output directly, but there is also an archive mode where the data is saved to table. This is not the least useful, if you want someone to send you the output from beta_lockinfo at a site you don't have access to yourself.

Another method is to use sp_whoIsActive by Adam Machanic with @get_locks = 1
EXEC sp_WhoIsActive
@filter = '',
@filter_type = 'session',
@not_filter = '',
@not_filter_type = 'session',
@show_own_spid = 0,
@show_system_spids = 0,
@show_sleeping_spids = 1,
@get_full_inner_text = 0,
@get_plans = 1,
@get_outer_command = 1,
@get_transaction_info = 0,
@get_task_info = 1,
@get_locks = 1,   ----------> 1 = ON (get lock info); 0 = OFF
@get_avg_time = 0,
@get_additional_info = 0,
@find_block_leaders = 0,
@delta_interval = 0,
@output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]',
@sort_order = '[start_time] ASC',
@format_output = 1,
@destination_table = '',
@return_schema = 0,
@schema = NULL,
@help = 0


Right Trimming Binary Data in SQL Server Scenario:

I am inserting a string into a binary field (CONTEXT_INFO) and then later attempting to pull it out and convert it back to a string.  When I do, the resulting string has a length of 128 because it has trailing null characters.

Example:

DECLARE @string VARCHAR(128)
DECLARE @binary VARBINARY(128)

SET @string = 'abcdefg'
SET @binary = CONVERT(VARBINARY(128), @string) --0x61626364656667000000...
SET CONTEXT_INFO @binary
SET @binary = CONTEXT_INFO()

-- I would like to change the following line so it trims trailing null chars
SET @string = CONVERT(VARCHAR(128), @binary)

SELECT
    @binary AS [binary],
    DATALENGTH(@binary) AS [binary.Length], --128 as expected
    @string AS [string],
    DATALENGTH(@string) AS [string.Length] --This is 128, but I need it to be 7

Question:

How can I trim the trailing null characters when I convert the binary field to a string?

This looks kind of unsafe, but it turns out that CONTEXT_INFO will eat empty strings and give you back a NULL anyway:

SET @string = LEFT(@string, CHARINDEX(CONVERT(varchar(1), 0x00), @string, 1) - 1);

Or, it turns out you can directly specify a binary value to search for in the REPLACE function:

SET @string = REPLACE(CONVERT(VARCHAR(128), @binary), 0x00, '');

This of course won't trim only trailing null characters, but I assume would be sufficient.

Note: neither of these solutions (nor the one in the comments) work if you switch to using nvarchar(64).


SQL agent job, why is the job history showing that a step is still running even though the job has completed I have a sql agent job that runs Powershell as its first step (there are 3 steps in total).

I have set this step to have 2 retries, with a 3 minute retry interval.
When I look into the job history, the step_1 states that it is still running, and also that it has completed. It has done this for every time that it has run (at least the last year).
Am I missing something from my powershell? Or is this something to do with sql agent itself?

Details of querying the sysjobhistory table (Pipe seperated):

Step_name|step_id|run_date|run_time|run_duration|run_status

(Job outcome)|0|2014/02/12|01:20:00|5|Succeded

Record volume space to file|1|2014/02/12|01:20:00|2|In Progress

Record volume space to file|1|2014/02/12|01:20:00|2|Succeded

Load Volume Space|2|2014/02/12|01:20:02|1|Succeded

Record database data file space|3|2014/02/12|01:20:03|2|Succeded

Step 1 runs the following Powershell:

gwmi win32_volume | select name, capacity, freespace | Export-Csv c:\temp.tmp

On the Hey Scripting Guy! blog at Technet, Chad Miller reports that if you run a Windows Powershell command as a SQL Agent job, and there are no syntax errors yet, the command produces an error (for example, attempting to get operating system information from an unavailable server). The SQL Server Agent job will report success.
Check tip #10 in the post for instructions on how to implement error handling with different options so that you can, for instance, have SQL Agent continue on error or halt the Agent job. The reported "succeeded" may not in fact have succeeded.

10-tips-for-the-sql-server-powershell-scripter


Would it be better to do a sepparate query instead of a left JOIN? Only a couple of rows is returned when not using LEFT since there isn't always data in the associated table - recipe_preparation. This is one of a couple of tables with the same "issue".

SELECT r.id, r.name,
    rp.explanation preparation,
    g.name
FROM recipes r
    LEFT JOIN recipes_preparation rp ON r.id = rp.fk_recipes_id AND rp.in_use = 1
    JOIN glassware g ON r.fk_glassware_id = g.id

The reason to why the preparation details is in a sepparate table is because of the ability to suggest/submit a better explanation. This is so the user can add their own/select a preffered one, and connect it to their account..
The AND rp.in_use is for the public one.

Anyway; I've read somewhere that using LEFT is more expensive, and that it should be avoided if possible. I could query recipes and only JOIN glasses. Then, in my application, I could just do a new query on each recipes to check if there's any data to retrieve.

What would be the better solution?
and as I mentioned before; this is just one of several other tables with this "issue"..

UPDATE:
Here is a EER diagram that I'm working with:

I've added a couple of more tables after this screenshoot was taken, and the in_use column in recipes_preparation (not indexed)..
Maybe this could tell a little more...

UPDATE #2:
I looked into the EXPLAIN statment, and this is the result I got - which didn't tell me much at first.

Looks like it starts with table g(glasses)? And it looks like it uses ALL as the join type - which is not good since that is a full table scan according to the MySQL Refrence..
Also; "MySQL found no index to use for executing the query more efficiently" more than? - the PK? - or non at all?

By your fine naming conventions I can take it that the fk_recipes_id column is indexed. This alone suggests that the query should be OK (pending cardinality of fk_recipes_id). If you further expand that index to include (fk_recipes_id, in_use), that would improve your query's execution plan.

Generally speaking, it is better to do a JOIN (including a LEFT one) within your database than to loop in your application code and submit multiple queries.

I can't know for sure what goes on in your app; you should indeed just test it as @Jon suggests. With a large dataset I suspect you will see the difference very quickly (and it will be in favour of the database JOIN).


How to update time of day in PostgreSQL I have this statement

SELECT *
FROM memberships
WHERE EXTRACT(HOUR FROM expired_at) = 0
  AND EXTRACT(DAY FROM expired_at) = 15;

I need to UPDATE all those records to be 08:00:00

So for example, if a record is 2016-06-15 00:00:00, I want to change it to 2016-06-15 08:00:00

One way of doing this:

update memberships
   set expired_at = expired_at::date + time '08:00:00'
where extract(hour from expired_at) = 0
  and extract(day from expired_at) = 15

expired_at::date will create a date without a time and then the time is simply added.


20 mysqld processes using only 1 port? These are from Aliyun Cloud DB Servers，20 mysqld processes using one 3306 port

Has anyone seen this before ?
Can anybody explain how to do that ?
Are there any advantages to this ?

What we see is impossible, so the logical assumption is that it's an illusion.

Multiple MySQL server instances can't listen on the same port.  They also can't share the same pid file, as these do, nor can the data files for InnoDB be shared.

The most sensible explanation for this is that the tools are showing you threads, not processes -- for whatever reason. Threads in Linux (if that's what this is) share the same process ID, but they also do have their own thread ID, a number that comes from the same numbering space as PIDs.

Most compelling, I suspect, is there are 20+ entries, each consuming 10.9% of available memory.  While it is highly unlikely for multiple identical instances if MySQL server to hold the same amount of memory (particularly the busy ones), it is impossible that your memory usage on this server is in excess of 200%.

mysql> SHOW FULL PROCESSLIST; when executed by a user with the SUPER or PROCESS privilege should show you the active client threads, and will likely contain entries representing to two that seem particularly busy, here.  Of course, there will be fewer in the output than are shown here, since not all threads are client threads.

Prove to yourself that there is really only one instance running, by repeatedly connecting and disconnecting and issuing SELECT CONNECTION_ID(), UUID_SHORT(); and/or by doing this on more than one connection at the same time. Both values will increment pretty much monitically (connection_id increments with each connection but remains the same while connected, uuid_short increments with each query) and will never be smaller.  This would be virtually impossible if multiple MySQL server daemon instances were involved.


Extending condition in a join with Postgres I have a situation like this:

with
my_data as (
  select A.a1,B.b1,B.b2
  from some_table A
  left join lateral (
     select b1, b2 from X where X.id = A.id limit 1
  ) B ON TRUE
)
select * from my_data where b1 is not null
union all
select A.a1,C.b1,C.b2
from my_data A, lateral (
   select b1, b2 from Y where Y.id = A.id limit 1
) C
where A.b1 is null;

and it returns the expected result but I would like to know if there is a better way of writing it.

For example one iteration like:

select A.a1,B.b1,B.b2
from some_data A, lateral (
   select b1, b2 from X where id = A.id limit 1
   -- and if the query on 'X' fails then try:
   select b1, b2 from Y where id = A.id limit 1
) B

any idea?

select b1, b2 from X where id = A.id limit 1
-- and if the query on 'X' fails then try:
select b1, b2 from Y where id = A.id limit 1

In general, you can implement this with UNION ALL, and then LIMITing the entire query so that the second query is taken only if the first one did not return something:

select b1, b2, 1 as order_me from X where id = A.id
union all
select b1, b2, 2             from Y where id = A.id
order by order_me
limit 1


This should work for you.

SELECT t.timestamp, t.deviceid, coalesce(eventcount, 0)
FROM foo
RIGHT OUTER JOIN (
  SELECT timestamp, deviceid
  FROM ( SELECT DISTINCT deviceid FROM foo ) AS d
  CROSS JOIN ( SELECT min(timestamp), max(timestamp) FROM foo ) AS ts
  CROSS JOIN LATERAL generate_series(ts.min, ts.max, '10m') AS gs(timestamp)
) AS t
ON foo.deviceid = t.deviceid
AND foo.timestamp = t.timestamp
ORDER BY 2, 1;

In the first run we generate a table with this

SELECT timestamp, deviceid
FROM ( SELECT DISTINCT deviceid FROM foo ) AS d
CROSS JOIN ( SELECT min(timestamp), max(timestamp) FROM foo ) AS ts
CROSS JOIN LATERAL generate_series(ts.min, ts.max, '10m') AS gs(timestamp)

This table we generate looks like this

      timestamp      | deviceid
---------------------+----------
 2017-01-01 00:00:00 | AAAAA
 2017-01-01 00:10:00 | AAAAA
 2017-01-01 00:20:00 | AAAAA
 2017-01-01 00:30:00 | AAAAA
 2017-01-01 00:40:00 | AAAAA
 2017-01-01 00:50:00 | AAAAA
 2017-01-01 00:00:00 | BBBBB
 2017-01-01 00:10:00 | BBBBB
 2017-01-01 00:20:00 | BBBBB
 2017-01-01 00:30:00 | BBBBB
 2017-01-01 00:40:00 | BBBBB
 2017-01-01 00:50:00 | BBBBB
(12 rows)

It has all the time-ranges needed. Then we RIGHT-OUTER join it.

The end result looks like this

      timestamp      | deviceid | coalesce
---------------------+----------+----------
 2017-01-01 00:00:00 | AAAAA    |     1000
 2017-01-01 00:10:00 | AAAAA    |        0
 2017-01-01 00:20:00 | AAAAA    |        0
 2017-01-01 00:30:00 | AAAAA    |     1000
 2017-01-01 00:40:00 | AAAAA    |     1000
 2017-01-01 00:50:00 | AAAAA    |        0
 2017-01-01 00:00:00 | BBBBB    |     2000
 2017-01-01 00:10:00 | BBBBB    |     2000
 2017-01-01 00:20:00 | BBBBB    |        0
 2017-01-01 00:30:00 | BBBBB    |        0
 2017-01-01 00:40:00 | BBBBB    |        0
 2017-01-01 00:50:00 | BBBBB    |     2000
(12 rows)


Index fragmentation problem after disabling page-level locking for an Index I have an Index which has page level locking disabled and now I have index fragmentation issues for that index.

I was aware that I won't be able to Reorganize the index, but I believed that I would be able to rebuild the index.

Now I think rebuild also is not working. I am using the default fill factor of 100. My database is huge so I don't want the table/db size to get increased by giving a fill factor of 80% or less. By not working, I mean that the Avg Fragmentation remains the same after running Ola's script the previous night.

A little background on why page level locking is disabled.

I was getting lots of transaction deadlocks on this index in that particular table. This table was kind of a fact table where insert, update and delete was happening every moment. And there are some foreign key relationship to a few other tables with CASCADE deletes. So, I was getting lots of locks/deadlocks and after disabling the page level lock, I was able to get rid of all those deadlocks.

I started using Ola Hallengren's script for Index maintenance and I applied Index Rebuild for all those indexes for which Index Reorganize is not working.
But I noticed that Index Rebuild also is not working.

Now, I see a AvgPageFragmentaiton of 95.9413 and Page count of 1196826 for the index, which is not good.

Additional info:

I am using Ola Hall's script as shown below.

 EXECUTE dbo.IndexOptimize
    @Databases = 'DB_NAME',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
    @FragmentationHigh = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE'',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @SortInTempdb = 'Y',
    @MaxDOP = 0,
    @WaitAtLowPriorityMaxDuration = 0,
    @WaitAtLowPriorityAbortAfterWait= 'NONE',
    @Indexes = 'DB_NAME.[dbo].TB1,DB_NAME.[dbo].TB2'

My question is how to reduce the fragmentation in an index if the page level lock is disabled for that index?

The answer to your query is buried in some documentation:

  Online index rebuilding may increase fragmentation when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF options.

The reasons for this are explained in detail in the following MSDN blog post:

How It Works: Online Index Rebuild – Can Cause Increased Fragmentation

Some options to resolve the problem in your situation, where page locks are disabled for the index, are:

rebuilding offline
rebuilding at MAXDOP 1


multisubnet listener Good morning all ,
I have an alwayson instance with a multisubnet listener
I configured RegisterAllProvidersIP = 0 in order to register a single DNS entry
HostRecordTTL is configured at 1200 seconds
I would like if with this configuration I will have timoeout during the failover
if so how much time is needed for the application to connect to the lsitener
thanks

if so how much time is needed for the application to connect to the lsitener

Since you said HostRecordTTL is set to 1200 seconds, the application will take up to 20 minutes to reconnect after a failover that crosses subnets.
Ideally you should set RegisterAllProvidersIP = 1, and update your application's connection string to include MultiSubnetFailover = True.  This will provide the best failover experience.
If your application uses a driver that doesn't support this setting, or you can't change the connection string for some other reason, and you need to reduce the failover timeout, then you'll need to lower the HostRecordTTL setting.


NUMA config - sp_blitz output I'm trying to learn more about how SQL Server works with NUMA nodes as I noticed something in the output of sp_Blitz that I didn't understand:

Node: 0 State: ONLINE Online schedulers: 8 Offline schedulers: 0 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 1343

Node: 1 State: ONLINE Online schedulers: 8 Offline schedulers: 0 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 1343

Node: 2 State: ONLINE Online schedulers: 8 Offline schedulers: 0 Processor Group: 0 Memory node: 1 Memory VAS Reserved GB: 0

Node: 3 State: ONLINE Online schedulers: 8 Offline schedulers: 0 Processor Group: 0 Memory node: 1 Memory VAS Reserved GB: 0
The server has 32 logical processors so the numbers above make sense. We're running SQL Server 2016 SP2 Enterprise Edition
I have a conceptual understanding of NUMA from online reading and need to do much more research, however in the short term, I'd like to understand the meaning/importance of VAS Reserved, why it differs between the nodes above, and whether the difference is anything to be concerned about.
This is the query (taken from sp_Blitz):
SELECT  'Node: ' + CAST(n.node_id AS NVARCHAR(10)) + ' State: ' + node_state_desc
        + ' Online schedulers: ' + CAST(n.online_scheduler_count AS NVARCHAR(10)) + ' Offline schedulers: ' + CAST(oac.offline_schedulers AS VARCHAR(100)) + ' Processor Group: ' + CAST(n.processor_group AS NVARCHAR(10))
        + ' Memory node: ' + CAST(n.memory_node_id AS NVARCHAR(10)) + ' Memory VAS Reserved GB: ' + CAST(CAST((m.virtual_address_space_reserved_kb / 1024.0 / 1024) AS INT) AS NVARCHAR(100))
FROM sys.dm_os_nodes n
INNER JOIN sys.dm_os_memory_nodes m ON n.memory_node_id = m.memory_node_id
OUTER APPLY (SELECT
COUNT(*) AS [offline_schedulers]
FROM sys.dm_os_schedulers dos
WHERE n.node_id = dos.parent_node_id
AND dos.status = 'VISIBLE OFFLINE'
) oac

WHERE n.node_state_desc NOT LIKE '%DAC%'
ORDER BY n.node_id OPTION (RECOMPILE);

Thanks.

You have two physical NUMA nodes (Memory Nodes), with Automatic Soft-Numa dividing each one into two NUMA nodes in SQL Server.

I'd like to understand the meaning/importance of VAS Reserved

Virtual Address Space (VAS) is reserved as a first step to allocating memory, see: Reserving and Committing Memory.  For 64bit processes the VAS is immense, and so it's common practice to reserve a huge block of it for later use.  This doesn't allocate any physical memory, it just reserves a contiguous range of VAS.  Later the application will "Commit" chunks of this address space to map the VAS to either physical RAM or the page file.
And in a NUMA system the memory allocation happens on the same node that the code is running on.  So that query just shows that a large VAS range was reserved on Node 0, probably at startup.
But SQL Server also supports a secondary method of memory allocation using the Address Windowing Extensions APIs originally added to Windows to allow 32bit applications to address more than 4GB of ram.  AWE allocations have the benefit of being exempt from the Virtual Memory Manager paging.  SQL Server calls memory allocated this way Locked Pages, and will use them if it has the Lock Pages In Memory permission.  AWE allocations won't show up under Reserved or Committed VAS, and are tracked separately in SQL Server's DMVs.
To to see the total amount of memory in use by a node you need to add virtual_address_space_committed_kb and locked_page_allocations_kb
sys.dm_os_memory_nodes
And, no I have no idea why sp_Blitz reports on VAS reservations instead of real memory allocations.


Are ER diagrams independent of a DBMS? When modelling a database, does the ER diagram depend on the DBMS used to implement the said ER?

When the ER (Entity-Relationship) model was devised in the 1970s, it was specifically intended to be implementation neutral (sometimes called "implementation agnostic").  The relational model, by contrast, was tilted towards a relational design and implementation.

In classical three stage methodology, an ER model is devised to capture the data analysis of the subject matter.  You could implement the database as relational, or OODB, or unstructured, or whatever. This is called the conceptual model.

The next model is a relational model, and captures the logical design.  This is tilted towards a class of Relational DBMS products that includes Oracle, SQL Server, and many others.  This is called the logical model.

The third model is a detailed model of the proposed database, and captures features that are DBMS specific, like indexes, and table spaces.  This is called the physical model.

Very often the logical and physical models are merged together, because the DBMS has already been chosen, and they are both design models.


Standard Edition of SQL Server and cores Looking for confirmation that we are overpaying for our VM :)

We have a SQL Server 2012 Standard Edition on a 7 socket (2 cores/socket) VM. From error log:

SQL Server detected 7 sockets with 2 cores per socket and 2 logical processors per socket, 14 total logical processors; using 8 logical processors based on SQL Server licensing.
Node configuration: node 0: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003:0. This message provides a description of the NUMA configuration for this computer.
Node configuration: node 1: CPU mask: 0x000000000000000c:0 Active CPU mask: 0x000000000000000c:0. This message provides a description of the NUMA configuration for this computer.
Node configuration: node 2: CPU mask: 0x0000000000000030:0 Active CPU mask: 0x0000000000000030:0. This message provides a description of the NUMA configuration for this computer.
Node configuration: node 3: CPU mask: 0x00000000000000c0:0 Active CPU mask: 0x00000000000000c0:0. This message provides a description of the NUMA configuration for this computer.
Node configuration: node 4: CPU mask: 0x0000000000000300:0 Active CPU mask: 0x0000000000000000:0. This message provides a description of the NUMA configuration for this computer.
Node configuration: node 5: CPU mask: 0x0000000000000c00:0 Active CPU mask: 0x0000000000000000:0. This message provides a description of the NUMA configuration for this computer.
Node configuration: node 6: CPU mask: 0x0000000000003000:0 Active CPU mask: 0x0000000000000000:0. This message provides a description of the NUMA configuration for this computer.

As I understand it, Standard Edition supports the lesser of of 4 sockets or 24 cores, thus I conclude we are paying extra for 3 sockets/6 cores that are not being used (this is a dedicated SQL box).

I am thinking we have two choices:

Update VM and remove the 3 unused sockets, so we have 4 sockets/8 cores.
Update VM and remove the 3 unused sockets, update number of cores per socket from 2 to 4, so we have 16 processors.  I need to check if this is even possible.

If #2 is possible, would this help with performance, assuming we are hitting CPU pressure at times?

I am thinking we have two choices:

  Update VM and remove the 3 unused sockets, so we have 4 sockets/8
  cores.

This is probably a better option. As Kin suggested in the comment on your question, NUMA imbalance would be a factor by trying to balance CPU affinity across physical cores.

  Update VM and remove the 3 unused sockets, update number of
  cores per socket from 2 to 4, so we have 16 processors. I need to
  check if this is even possible.

Even if it is, go with fewer cores. On a virtual environment, this is preferred, especially with Standard Edition.

Final note: if you do share the host with other VMs, please be wary of something called CPU lag. This can happen when VMs with different numbers of vCPUs are running on the same host.


Handling user logins while using a multiple schema design to support multi-tenancy I'm creating a web application (in PHP and PostreSQL) that will be used by organisations and their staff and I want to use multiple schemas to support multi-tenancy. There will be one database and I want all data related to organisation A to be stored in a schema named A and all data for organisation B to be stored in schema named B etc..

What's confusing me is how to handle user logins. Let's say Amy is a member of Organisation A and Ben is a member of Organisation B. When Amy goes to example.com to access my web application she's presented with a login form, which asks for her username and password. Now, Amy's username and password is stored in the users table of schema A but how does the web application know where to look to authenticate her?

I've never used multiple schemas in a web application before so in the past I would create a fixed username, i.e. webapp for the application, which would log in and check the credentials supplied by Amy against the data stored in the public.users table (where public is the default schema created by PostgreSQL) but with the design I'm thinking of using, there is no way to tell which schema Amy belongs to and as a result, it wouldn't be easy to find the correct users table to authenticate her.

One approach would be to stop using a users table for authentication and create a role with the LOGIN privilege for the database for every user, then only give that user permission to interact with the appropriate schema. That's a solution I've seen but not one I really want to use. I assume there's a better way but I just haven't been able to find it.

So, to summarise, all I want is for users to be able to log in to my app via the web interface and be restricted to the schema of the organisation they belong to. What's the best way to accomplish this, bearing in mind I intend to have several organisations and users in the long run.

All help is appreciated. Thanks in advance.

Your options are:

Use roles, and SET search_path on each role so it looks in the appropriate organization's schema. While in many ways the cleanest approach from a database perspective, this is a pain when giving orgs the right to manage their own users.
Require users to specify their organisation at login time, probably with user@domain style logins, or using mycompany.myhostedapp.com style URLs. Then look up the org by @domain suffix before authing the user against that org. That's probably what I'd do.
Create a view that's a union of all company user tables and look users up in this. Likely to be a performance nightmare, and how will you handle duplicates?
Create a parent table for all user tables, make all the user tables inherit it, and query the parent table. Just a slightly more efficient version of the view approach, not recommended.

I strongly suggest an authentication design that carries the company identity of the user along with their login request.


Trying to take a backup of a database, but drives are different than in file explorer In SQL Server Management Studio for SQL Server 2008, I do the following:

In the Object Explorer windows, right click on the database
Choose Tasks -> Back Up...
The Back Up Database windows appears. I choose 'Disk' under Destination
I choose 'Add...' under destination
The Select Backup Destination window appears. I choose '...'
The Locate Database Files window appears.

Now I want to save it to a network drive, but there are only three drives to choose from (C,G,H). But in any other place in windows there are C, and the network drives K, Q and Z. Why can't I save it on a network drive? And what is drive G and H? I'm fine with saving it on G or H, but where is it and how much space is left on these drives?

Are you using SMSS on the actual SQL server?

Or are you using SMSS on a local machine then connecting to SQL server over the network?

If you are connecting to a remote SQL Server over the network that would explain the differences in the drives you're seeing.


How do I find all uses of a data type in a MySQL database? I'm planning an upgrade to MySQL where DATETIME, TIMESTAMP, TIME and YEAR are potentially a problem. I'd like to query the database to find all uses of these datatypes. How can I accomplish this?

The following query will search for you usage of these datatypes across a database.

SELECT COLUMN_NAME, DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = "database"
      AND DATA_TYPE in ('DATETIME', 'TIME', 'TIMESTAMP',
                        'YEAR(2)', 'YEAR(4)', 'YEAR');


SQL PRINT statement prints nothing I don't get any output for this simple PRINT statement. What am I missing?

Data supplied in sqlfiddle.

DECLARE @team varchar(20)

BEGIN
   SELECT @team = TEAM
   FROM DISTRIB_LINE
   PRINT 'value is' + @team
END

The PRINT statement will print nothing if the expression evaluates to a null. Apparently @team ends up being null as the result of the preceding statement, causing the PRINT to output nothing. If you want to print something even when the variable is null, use ISNULL or COALESCE:

PRINT 'value is ' + ISNULL(@team, '(null)');

You could also add a filter to the SELECT to ensure that NULLs are skipped (if you want them skipped, that is):

SELECT @team = TEAM
FROM DISTRIB_LINE
WHERE TEAM IS NOT NULL;

However, if the table has no rows or if all TEAM values are null, this statement will not do any assignment. In this case, if PRINT still outputs nothing, it means @team was null even before the SELECT. Therefore, you may still need to apply the previous method.

Besides, it is not a good idea to assign a variable this way when a table has many rows, because you have no control of which value will ultimately be assigned. Therefore. Alexei has a point there as well with regard to using TOP, as well as combining it with ORDER BY and, possibly, additional filters.


You can use HAVING to filter the columns you created:
select
  subscriber_id,
  MAX(case when field_id = '1' then value else 0 end) as email,
  MAX(case when field_id = '2' then value else 0 end) as first_name,
  MAX(case when field_id = '3' then value else 0 end) as last_name
from test_fields_table

group by subscriber_id
HAVING email LIKE '%com'
AND last_name LIKE 'M%'
limit 100;

See result