ERROR: role "user1" cannot be dropped because some objects depend on it On my Google Cloud SQL database instance (Postgres 9.6), I am unable to drop a user/role via the GCP Console that was created via the GCP Console. When I try, the Console shows a notification that says "User deleted", then upon further inspection I see that the operation failed with an "Unknown error". The instance's error logs show the following error:
06:42:36.347 UTC [199]: [1-1] db=cloudsqladmin,user=cloudsqlagent ERROR:  role "user1" cannot be dropped because some objects depend on it
06:42:36.347 UTC [199]: [2-1] db=cloudsqladmin,user=cloudsqlagent DETAIL:  privileges for database mydb
24 objects in database mydb

I am able to see the 24 objects by running
SELECT distinct table_name
FROM information_schema.role_table_grants
WHERE grantee = 'user1'

Following the advice at https://cloud.google.com/sql/docs/postgres/create-manage-users#deleting_users and I know that I must reassign the ownership to another role (postgres) before dropping the user, but when I attempt to run the following script (from https://stackoverflow.com/questions/51256454/cannot-drop-postgresql-role-error-cannot-be-dropped-because-some-objects-depe):
REASSIGN OWNED BY user1 TO postgres;
DROP OWNED BY user1;
DROP USER user1;

I am presented with a permissions error on the REASSIGN command (ERROR:  permission denied to reassign objects) due to the fact that Cloud SQL doesn't give my user SUPERUSER status (https://cloud.google.com/sql/docs/postgres/users#superuser_restrictions). I am also unsure of how to grant SUPERUSER status to a user in a Cloud SQL database instance; my assumption is that I cannot.
This issue is also occurring when using the gcloud command of the Cloud Shell. The output is similar:
$ gcloud sql users delete user1 --instance=<INSTANCE_ID_REDACTED>
user1@None will be deleted. New connections can no longer be made
using this user. Existing connections are not affected.

Do you want to continue (Y/n)? Y

Deleting Cloud SQL user...failed.
ERROR: (gcloud.sql.users.delete) [INTERNAL_ERROR]

Important information:

Postgres Version 9.6
A read replica is attached to this instance
Yes, I have tried turning it off and on again. No, the restart of both the main instance and the read replica did not solve this issue.
All database schema migrations and data migrations on this instance have been accomplished using Flask-Alembic with a very standard configuration
I can verify that no users have altered the schema outside of the Alembic process in our Cloud Build configuration
The user was created in the GCP console
No manual alterations have been made to the user, privileges, or object ownership

06:42:36.347 UTC [199]: [2-1] db=cloudsqladmin,user=cloudsqlagent
DETAIL:  privileges for database mydb 24 objects in database mydb

Crossdatabase queries in PostgreSQL are not implemented. PostgreSQL will see some dependencies with the specified pg_catalog.pg_shdepend.dbid, but will not be able to query the system catalog of that database to determine which objects they are. So, postgresql does hint only database names in error details for other databases. (this is also a reason why PostgreSQL has no drop user ... cascade - we can't modify or even read objects description from another database)
But if you connect to the database specified in the details and try to delete a user, you will get a much more detailed description:
postgres=# drop user user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  3 objects in database m2
postgres=# \connect m2
You are now connected to database "m2" as user "postgres".
m2=# drop user user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  privileges for table foo
privileges for table foofk
owner of table test

In this case user1 is owner for one table (REASSIGN OWNED will solve this easily) and some priviilegies on another two tables (REASSIGN OWNED will not touch this).
Certain commands are required for different objects (revoke for permissions, alter default privileges for default privileges and so on), unfortunately, there is no omnipotent ring to manage all dependencies even in one database.


How can a group track database schema changes? What version control methodologies help teams of people track database schema changes?

just a couple of minutes ago I was checking this: A table that should exist in all projects with a database, seems simple enough to put in practice, check it out:

  It’s called schema_version (or migrations, or whatever suits you) and its purpose is to keep track of structural or data changes to the database.
  A possible structure (example in MySQL) is:

create table schema_version (
     `when` timestamp not null default CURRENT_TIMESTAMP,
     `key` varchar(256) not null,
     `extra` varchar(256),
     primary key (`key`)
) ENGINE=InnoDB;

  insert into schema_version(key, extra) values ('001', 'schema version');

  Whether you add this table from the beggining of the project or just after you’ve deployed the first version to a staging or production server is up to you.

  Whenever you need to execute an SQL script to change the database structure or perform a data migration you should be adding a row in that table as well. And do that via an insert statement at the begining or end of that script (which is committed to the project’s code repository).

  …


Query Store - What are the Security permissions needed to view reports? Query store is enabled on 2 of our production databases but I no longer have access to see the Query Store node under the database since my SA privileges were revoked (I had them temporarily for a new server).

I can't find anywhere that describes what minimum permissions are needed for access to query store reports.

Just use

GRANT VIEW DATABASE STATE TO [USER];


SQL Fast database restore with Azure Virtual Machine instant recovery snapshot and SQL in Azure VM? I have a large database. If I want to restore to a point in time using a full back up, it takes around 4 hours + time taken for transaction log restores.

I also have VM image instant restore snapshots taken on Azure daily. The restore of a VM image by replacing existing disks takes only 20 minutes.

It is known that transaction logs cannot be restored unless the most recent full back up is restored.

Lets say I sequence my backup jobs in this order:
1. Full SQL Backup of the database taken and stored on Azure using "SQL in Azure VM" feature of the Recovery vault
2. Azure VM image instant restore back up following it (there may be one hour in between with transactions occurring)
3. Transaction log backups using the same feature on Azure as #1, every 15 minutes

Then if we have an outage, can I do a faster restore by restoring the VM image? Would I be able to skip the full backup restore and directly restore only the transaction logs in this state of the VM? Is this a sound theory?

The LSN of the database should be at the state when the VM snapshot was taken. Would it be a problem since the database would come online immediately and the LSN would change before I can take it offline and do a restore?

Then if we have an outage, can I do a faster restore by restoring the VM image?

No.  See my answer here. Is it possible to restore a database with database backups starting on top of a restored server point in time snapshot that is out of date?

You can't attach a database without crash recovery, and you can't perform a crash recovery without the transaction log file.  Log backups cannot be substituted.

If you put your database files on Azure Page blobs, instead of on an attached disk you can use File-Snapshot Backups for Database Files in Azure.


How to purge logs by using Flashback feature in Oracle Database I’ve configured flash recovery area for Oracle Database. My question is: how can I delete Flashback Database log?or is there any retention policy for that?

2- After I’ve enabled archive log mode and flash recovery area ,does purging automatically archived logs which are located archive log mode folder(not FRA logs)?or do I have to backup them via RMAN?

Thanks,

FRA will be purged automatically when free space is needed
Put the archived logs in FRA as well
Use RMAN to make the backups and use RMAN commands (REPORT OBSOLETE / DELETE OBSOLETE) to manage the purging of  backup pieces + archived logs from FRA

From docs: (http://docs.oracle.com/cd/B19306_01/backup.102/b14192/setup005.htm)

The Oracle Flashback Database feature, which provides an convenient alternative to point-in-time recovery, generates flashback logs, which are also considered transient files and must be stored in the flash recovery area. However, unlike other transient files, flashback logs cannot be backed up to other media. They are automatically deleted as space is needed for other files in the flash recovery area.

When Files are Eligible for Deletion from the Flash Recovery Area

There are relatively simple rules governing when files become eligible for deleteion from the flash recovery area:

Permanent files are never eligible for deletion.
Files that are obsolete under the configured retention policy are eligible for deletion.
Transient files that have been copied to tape are eligible for deletion.


SQL server not listing records with NULL values I faced a strange phenomenon today. I ran the below query to Find all requests with waits other than BROKER_RECEIVE_WAITFOR

select * from sys.dm_exec_requests where session_id > 55
and wait_type not like ('BROKER_RECEIVE_WAITFOR')

The query ran fine, But it failed to list any request with No waits, ie with wait_type as NULL(there were quite a lot of running queries without any waits at this time).

Why is this happening, Isn't it supposed to list all records with wait_type not like 'BROKER_RECEIVE_WAITFOR' including Nulls?

I'm running SQL server 2008R2 SP3.

The reason for this is that a NULL represents an unknown value.

NULL is not LIKE anything and not NOT LIKE anything either.

Try running the following 2 statements on your system:

SELECT CASE WHEN null like '%a%' THEN 'true' ELSE 'false' END;
SELECT CASE WHEN null not like '%a%' THEN 'true' ELSE 'false' END;

That should return false for both statements.

So your query should look like this:

select * from sys.dm_exec_requests where session_id > 55
and (wait_type not like ('BROKER_RECEIVE_WAITFOR') or wait_type is null)


What MySQL configuration options require more than editing the my.cnf file and restarting the server? One example of this is the innodb_log_file_size configuration option. As discussed here, there are a series of steps that starts an hour before shutdown and goes to after shutting down. However, this doesn't appear to be documented in the MySQL documentation for this variable and I don't see a list of variables that you may not be able to adjust by simply editing the configuration file and restarting the server.

Does such a list of variables exist? What variables do I need to potentially watch when tuning MySQL to ensure compatibility with existing binary backups?

It depends on which version you are using.  By the time you get to 8.0, virtually everything can be dynamically changed.  Here are 2 types of pages, each with a list.  Note the version number in the URL; change as needed.

http://dev.mysql.com/doc/refman/5.7/en/dynamic-system-variables.html

https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html -- Note the "Dynamic" column.

innodb_log_file_size used to be one of the worst settings to change.  You could not simply change the config and restart -- the server would complain that the size does not match.  Recently (sorry, don't know what version), the 'right thing' would be done with the files.  But it still required a restart.

In my experience, 90% of users can get away with setting innodb_buffer_pool_size based on RAM size, then ignore the rest of the settings.

(If you wanted to talk specifically about innodb parameters, say so.)

If you want a critique of your settings, provide RAM size, SHOW VARIABLES; and SHOW GLOBAL STATUS; after it has been up at least a day.  I have about 150 formulas to test; they typically lead to 5-10 recommended changes of the VARIABLES.


Reorganize Objects in OEM - Host Credentials do not have enough privileges When I try to re-organize objects from OEM (11g) and enter my host credentials (not "oracle"), I get the message:

  "An error occurred verifying the host credentials. Make sure the
  credentials are valid and that they specify an account with enough
  privileges to write to the directory where the reorganization script
  will be created. Credential verification error: ERROR: Invalid
  username and/or password"

My host username is a member of the oinstall and dba groups.  I guess my first question is what directory is it that I need write access to?  My second question is what privileges do I need in order to run a reorg (not as the "oracle" user)?

I should say that this is a linux Oracle server.

You have different types of credentials in OEM !
First you need to create those credentials and test them.
See here an article that walks you throw how to create and test your named credentials


Grant Windows logins to read a secondary database of log shipping? The following link, Creating Users on secondary server in log shipping, shows how to create a database login on the secondary server. How to grant Windows logins on the secondary server to access the database? The primary server and secondary server are not in the same domain so I cannot create the Windows domain user on the Primary server.

Since the secondary in a log-shipping configuration is either READ ONLY or RESTORING, you cannot make changes to the database (i.e. create a USER) until a ROLE CHANGE happens (i.e. when you failover from primary to secondary). You can create a LOGIN on the secondary server.

You can use this script - How to transfer logins and passwords between instances of SQL Server to script logins - both SQL and Windows.

If you are using SQL Server 2014, then you can just create a ROLE and then grant that role connect any database and select all user securables. This works on readonly databases in logshipping configuration as well.

Note that above is not that elegant if you keep security in mind, but still is an alternative that can be considered.


How does a users DEFAULT_SCHEMA get set to something other than "dbo" One of the settings for a SQL Server user is the DEFAULT_SCHEMA.  This is frequently dbo but not always.  I realize you can change the setting by using this code:

ALTER USER [UserName] WITH DEFAULT_SCHEMA = [SchemaName]

From what I can tell whenever I create a USER and don't specify a DEFAULT_SCHEMA it uses dbo.  But I'm still finding USERS that have a different default schema.

If I remember correctly when you have an instance that is upgraded from SQL 2000 the default schema is the schema matching the users name.  I'm guessing this is where most if not all of what I'm seeing comes from.

Are there any other ways that you can create a USER without specifying DEFAULT_SCHEMA and have it be created with something other than dbo?

Note: This is for versions of SQL Server 2005 and up.

One way this happens is by having a Login that has the CONTROL SERVER server-level permission (hence does not need an actual USER in order to access a database) but not in the sysadmin fixed server role (as then there is an implied default schema of dbo) create an object without explicitly specifying the schema to put it in.

According to the MSDN page for CREATE SCHEMA:

  Implicit Schema and User Creation

  In some cases a user can use a database without having a database user account (a database principal in the database). This can happen in the following situations:

  A login has CONTROL SERVER privileges.
  A Windows user does not have an individual database user account (a database principal in the database), but accesses a database as a member of a Windows group which has a database user account (a database principal for the Windows group).

  When a user without a database user account creates an object without specifying an existing schema, a database principal and default schema will be automatically created in the database for that user. The created database principal and schema will have the same name as the name that user used when connecting to SQL Server (the SQL Server authentication login name or the Windows user name).

Hence, I am speaking of the first case noted above, and @Max's answer is about that second case.

The following is an example for the first case (please note that the following example uses tempdb since every instance has that, but I did test on a non-tempdb database to be sure). To see an example of the second case, please see @Max's answer (there is no need to duplicate that here).

USE [master];

CREATE LOGIN [DefaultSchemaTest] WITH PASSWORD = 'DefaultSchemaTest';
GRANT CONTROL SERVER TO [DefaultSchemaTest];
GO

EXECUTE AS LOGIN = 'DefaultSchemaTest';

USE [tempdb];
SELECT * FROM sys.database_principals WHERE [type] <> 'R';
SELECT * FROM sys.schemas WHERE [name] NOT LIKE N'db[_]%';

CREATE TABLE MySchemaTest (Col1 INT);

SELECT * FROM sys.database_principals WHERE [type] <> 'R';
SELECT * FROM sys.schemas WHERE [name] NOT LIKE N'db[_]%';

USE [master];
REVERT;


Always On Availability Question mark In my production environment I have two database Servers know as DBN1 and DBN2. I have made a cluster between two and DBN1 is my primary database server. Now I used Always on availability feature on both servers and databases were synchronized.

Today I am encountering an issue. There is a small question mark icon coming on the DBN1 which is shown by DBN2 always on availability properties. Moreover it is also not showing that it is a primary database. Why this is happening ?

This is my primary server output & follow the arrow

This is my secondary server output & follow the arrow

The secondary replica does not know the role / status of that DBN1 replica

When trying to failover to the secondary, the status of the primary is not known:

it finds the current primary replica but not the primary replica status.

When failing over from the primary it does find this information.

When you select the properties of the replica with the question mark on the secondary replica:

Properties

Role: Unknown

Whereas on the primary replica all roles and their sync states are known.

sys.dm_hadr_availability_replica_states

Executing this query returns multiple lines on the primary, but only the one for the secondary on the secondary replica's.

select * from sys.dm_hadr_availability_replica_states;

This behaviour would be reversed after failover.

Extra checks

When looking at the query that gets executed when opening the properties of the unknown replica on the secondary replica, the sys.dm_hadr_availability_replica_states dmv is also used with a left join in a much bigger query.

An interesting part here is the OperationalState column:

ISNULL(arstates.operational_state, 6) AS [OperationalState],

Meaning if the replica is not present in that dmv, such as when executing on the secondary, return 6 as the OperationalState.

This state is not explicitly noted in the documentation on the dmv:

  operational_state tinyint Current operational state of the replica,
  one of:

  0 = Pending failover

  1 = Pending

  2 = Online

  3 = Offline

  4 = Failed

  5 = Failed, no quorum

  NULL = Replica is not local.

  For more information, see Roles and Operational States, later in this
  topic.

The 'Unkown' state is also not noted in the documentation.

In this case 6 is used when the role is not known.

It does not always appear to be 6, when refreshing the availability replica's on the secondary:

the query used sets the unknown replica's role status to 3:

ISNULL(arstates.role, 3) AS [Role],

Which is offline on the dmv's documentation.

And the connected state column will be 2

ISNULL(arstates.connected_state, 2) AS [ConnectionState],

Which is also not noted in the documentation:

  0 : Disconnected. ...

  1 : Connected. ...


SQL Server 2012 32 bit - will enabling locked pages allow it to use more memory? I recently found out, that a server got a 32 bit installation of SQL Server 2012 rather than 64bit, and it is running out of memory now.

Can I have this 32 bit installation use more memory using locked pages?

I do not know much about this. I've read this http://support.microsoft.com/kb/2659143/en-us bit I am still not clear if this will help, what needs to be done etc

Can I have this 32 bit installation use more memory using locked pages?

In the past, my answer would have been (a heavily qualified) "yes".

Using AWE (address windowing extensions) would enable up to 64GB memory. Unfortunately (from your point of view, perhaps) 32-bit AWE was removed from the database engine starting with SQL Server 2012.

In previous versions, 32-bit SQL Server running on a 64-bit version of Windows (WOW mode) was a supported configuration but one that I never encountered personally. Anecdotally, such configurations were often problematic; if you enjoy tracking down weird problems, 32-bit AWE SQL Server on 64-bit Windows is for you.

My very strong recommendation would be to use 64-bit SQL Server 2012 instead.


sp_repladdcolumn - is it possible to supply a list of Publications to add this column on? Scenario

We have an existing table in 3 different publications (going to 3 different subscribers), 1 of those publications does not publish all columns.

All of these are transactional pull subscriptions.

I now need to add some more columns onto this table and we want to add them on to just the first two publications.  However from MSDN:

  [ @publication_to_add =] 'publication_to_add' Is the name of the
  publication to which the new column is added. publication_to_add is
  nvarchar(4000), with a default of ALL. If ALL, then all publications
  containing this table are affected. If publication_to_add is
  specified, then only this publication has the new column added.

which implies that it you expects it to specify 1 publication or ALL publications.

A sample table/publication detail to help demonstrate the issue:

Table sample

[MyTable] (
    ID int IDENTITY(1,1) NOT NULL,
    myName varchar(50) NULL,
    myDOB datetime,
    myJob varchar(100),
    mySpouseID int )

Publication/Subscriber sample

[InternalPublication] All fields, sent to InternalAdmin system
[Reporting] All fields, sent to Reporting/Datawarehouse system
[External] sent to External accessable system, fields sent:
    (ID, myName, myDOB)

Question

How do I add on:

[myMarriageDate] datetime NULL,
[myDivorceDate] datetime NULL

and have it only added to the publications [InternalPublication] and [Reporting]?

Note: sample is a made up one to protect the innocent

sp_repladdcolumn is deprecated unless you have SQL2000 subscribers, you should use the Replicate Schema changes option on the publication. Assuming you have that set to No, I guess you could still use the sp_repladdcolumn, and if you look at the source for the proc, it looks like it has handling for accepting a comma separated list of publications, but using it is venturing into undocumented handling of a deprecated procedure...i.e. no guarantees.

sp_repladdcolumn  @source_object =  'fooTable',
                  @column =  'fooNewColumn',
                  @typetext =  'int' ,
                  @publication_to_add =  '[pub1],[pub2]'

EDIT: Publication names need to be in [] for it to work as in the code of sp_repladdcolumns it has:

select p.name, a.name, a.pubid from dbo.sysmergepublications p, dbo.sysmergearticles a
where p.pubid=a.pubid and a.objid=@objid and p.snapshot_ready=0
     and (p.name like @publication_to_add
         or charindex('[' + p.name + ']', @publication_to_add)>0)


Index included columns After running a fairly hefty query, the execution plan gave me a missing index suggestion which was of the form:

(Timestamp) INCLUDE (CustomerID, EventID, ID, EmployeeID)

Which seems to be a covering index (the INCLUDE column are all either primary keys (ID) or foreign keys). However, my querys WHERE clause is filtering by Timestamp, CustomerID, and EventID. I don't know why these weren't included in the main part of the index.

So my question is, is there any difference in using the suggested index above, or what I think is a better alternative;

(Timestamp, CustomerID, EventID) INCLUDE (ID, EmployeeID)

My understanding is that this will still allow Timestamp-only index seeking, but will also further assist my query by having the customer and event IDs (which are filtered) in the main part.

I think this was something to do with the width of the 'main' part - FYI, Timestamp is a datetime2(0), CustomerID is an int, and EventID is a byte.

I am testing this myself at the moment, but this is a HUGE table - over 1,000,000,000 rows - and it is taking time to compare the indexes. That, and I'd like to learn more about this.

Thanks.

Probably Timestamp is very selective, possible even unique. As such there is no point in adding other fields to the key, they will only increase the size of the key without contributing to the selectivity. Having them as INCLUDED columns instead allows them to be added only to the leaf pages, saving on the overall size of the index.

select top(1000) count(*) as cnt, Timestamp
from ...
group by Timestamp
order by cnt desc;

What does the above return?


Best practice for storing record metadata What is the best practice for storing metadata of individual records in a database?

I need to store common meta data such as creation time and time of last update for many tables in my database. I found a few different solutions:

Store the meta data directly in the tables.

Pros:

Meta data is directly linked to records
No joins are required to retrieve meta data

Cons:

A lot of duplicate columns are required (unless inheritance is used)
Meta data and business data are not separated

Create a general meta data table with and use soft foreign keys to link data to the correct tables and records.

Pros:

No duplication of columns
Meta data is separated from business data

Cons:

No direct links between meta data and data (FK's can't be used)
Joins require an additional condition

Create individual meta data tables for each table requiring meta data.

Pros:

Meta data is directly linked to records
Meta data is separated from business data

Cons:

A lot of extra tables are required
A lot of duplicate columns are required (unless inheritance is used)

Are there more options, pros or cons than the ones I mentioned here? And what is the best practice for storing this meta data?

The columns you are talking about occupy 20 bytes (if aligned without padding):

  creation time, update time and creation source

timestamp .. 8 bytes
timestamp .. 8 bytes
integer   .. 4 bytes

The tuple header and item identifier for a separate row in a separate table alone would occupy 23 + 1 + 4 = 28 bytes plus the 20 bytes of actual data, plus 4 bytes of padding at the end. Makes 52 bytes per row. See:

Configuring PostgreSQL for read performance

Concerning storage you have nothing to gain. Concerning performance you hardly lose anything with just 16 - 24 bytes more per row.

The columns also directly belong to the row, so it makes sense to keep them together. I make it a habit to add exactly such columns (plus separate source for the last update) to all relevant tables.

It's also easier to write a TRIGGER ON INSERT OR UPDATE to keep them current.

Long story short: a strong vote for your option 1.

Where I would go for option 3:
If the metadata is updated often, while the core row is not. Then it might pay to keep a separate 1:1 table to make UPDATEs cheaper and reduce bloat on the main table - or even go for option 2.

Where I would go for option 2:
If the set of metadata columns is highly repetitive. You could have a FK column to the set of metadata in the main table(s). Does not save much for three small columns like in your example.


Creating a copy of production database for reporting purposes We previously ran Crystal Reports straight off our production database. This caused a direct impact to the users using the database. We moved to a new (dedicated) server and a new database and ditched Crystal Reports in favor of SSRS. We also have a dedicated reporting server to which our production database is copied over to twice a day. We run queries off of the reporting server as to not slow down the production environment.

Is this the best solution to our problem? The backup restorations kick everyone off the reporting server whenever they occur and management is asking we step up this process (backup restoration) to once every two hours. However, just twice a day gives us issues. For example, as I write this, the database is stuck in "Restoring..." and has been for at least 12 hours. So in the meantime that the DBAs come in (I only write reports) I thought I'd ask. Is this the best possible setup we've got going on?

This is what Replication is for.  This is a much easier way to keep a reporting database in sync than backup/restore.


Change the sql table output - Rows to Columns I have a result from recursive query that gives me Table1 (SQL Server 2008 R2).
I need to convert the Table1 to Table2 and I don't know the best and simplest approach to do it.
Any suggestions?
Table1

Table2

Regards.

I found a very simple way of doing this. It might be not pretty but it's good enough for me!
-- Delete temporary tables
If OBJECT_ID('tempdb..#tempTable0') Is Not Null DROP TABLE #tempTable0
If OBJECT_ID('tempdb..#tempTable1') Is Not Null DROP TABLE #tempTable1
If OBJECT_ID('tempdb..#tempTable2') Is Not Null DROP TABLE #tempTable2
If OBJECT_ID('tempdb..#tempTable3') Is Not Null DROP TABLE #tempTable3

-- Add a row number to each record
SELECT *, ROW_NUMBER() OVER(ORDER BY (Select 0)) AS Row_Num
INTO #tempTable0
FROM Table1

-- Copy each record into a temporary table; Add 1 to be able join the temporary tables
Select Id as ID_1, Name as Name_1, 1 AS Row_Num Into #tempTable1 From #tempTable0 where Row_Num = 1
Select Id as ID_2, Name as Name_2, 1 AS Row_Num Into #tempTable2 From #tempTable0 where Row_Num = 2
Select Id as ID_3, Name as Name_3, 1 AS Row_Num Into #tempTable3 From #tempTable0 where Row_Num = 3

-- Final Record
Select ID_1, Name_1, ID2, Name_2, ID_3, Name_3
From
    #tempTable1 as T1 Left Join
    #tempTable2 as T2 On T2.Row_Num = T1.Row_Num Left Join
    #tempTable3 as T3 On T3.Row_Num = T1.Row_Num


What happens when you restore the same transaction log multiple times? I’m currently trying to keep a copy of our production database up to date by restoring a full backup, followed by a differential, then supplementing with transaction logs as they become available from the production database. The source is SQL Server 2008 Enterprise Edition and the destination is SQL Server 2014 Web. Doing this I noticed that you can restore the same log multiple times (as long as a newer one hasn't been restored after).

Question: what happens when you restore the same transaction log multiple times? Does it get overwritten? Discarded? Is it screwing up my database?

E.g.

  sqlcmd -E -Q "RESTORE LOG [databasename] FROM DISK = 'D:\ LOG\SERVERNAME_databasename_LOG_20160809_141504.trn' WITH NORECOVERY"
  Processed 0 pages for database 'databasename', file 'databasename_dat' on file 1.
  Processed 0 pages for database 'databasename', file 'MyDBDat' on file 1.
  Processed 65662 pages for database 'databasename', file 'databasename_log' on file 1.
  RESTORE LOG successfully processed 65662 pages in 27.589 seconds (18.593 MB/sec).

  sqlcmd -E -Q "RESTORE LOG [databasename] FROM DISK = 'D:\LOG\SERVERNAME_databasename_LOG_20160809_141504.trn' WITH NORECOVERY"
  Processed 0 pages for database 'databasename', file 'databasename_dat' on file 1.
  Processed 0 pages for database 'databasename', file 'MyDBDat' on file 1.
  Processed 65662 pages for database 'databasename', file 'databasename_log' on file 1.
  RESTORE LOG successfully processed 65662 pages in 26.238 seconds (19.551 MB/sec).

Restoring the same transaction log over and over buys you nothing.  The changes in the log will have already been applied to the database files after the first restore and no further action is needed on subsequent restores of the same transaction log backup.

Also, keep in mind that you cannot access a database until you have issued the with recovery command UNLESS you are using a standby file which will enable read only access.

Depending on WHY you are wanting to keep in sync with your production database, it sounds like you might want to research log shipping


MySQL Many to Many with additional data in intermediary table I need to create a Many to Many relationship and understand the need for an intermediate table to act as the link between the two. The understanding I have is the Group of ID's in the intermediate table should be unique to not duplicate data; is this correct?
The DB that I am designing is for a deck of cards.
I have two tables: decks and cards. The intermediary table is deck_cards.
The issue I am facing is: a deck can contain many cards but can also contain more than one of the same card.
Below is a screen shot of my current Schema. I am currently storing an additional bit of data in the column amount in the deck_cards table for a given relationship. Is this the appropriate way to approach this situation? Or, is there another intermediary table that is needed to be created?

If we have Person and School and a Person can enroll in zero to many schools and a School can enroll one to many people the many-to-many relation exists, but we would refer to the resulting entity as something like Student with its own properties (enrollment date, student id number, etc).
So outside of maybe changing the name of the entity, there's nothing wrong with that portion of the data model.
Other notes:

You need at least one alternate key on User, definitely one for Username, possibly one for Email.
You will need an alternate key on Deck, (user_id,name).
You will need an alternate key on Card, (name).


Unique Identifier with Extra Characters Still Matching in Select We are using SQL Server 2012 with a unique identifier and we've noticed that when doing selects with additional characters added onto the end (so not 36 chars) it still returns a match to a UUID.

For example:

select * from some_table where uuid = '7DA26ECB-D599-4469-91D4-F9136EC0B4E8'

returns the row with uuid 7DA26ECB-D599-4469-91D4-F9136EC0B4E8.

But if you run:

select * from some_table where uuid = '7DA26ECB-D599-4469-91D4-F9136EC0B4E8EXTRACHARS'

it also returns the row with the uuid 7DA26ECB-D599-4469-91D4-F9136EC0B4E8.

SQL Server seems to ignore all characters beyond the 36 when doing its selects. Is this a bug/feature or something that can configured?

It's not a massive issue as we have validation on the front end for the length but it doesn't seem correct behaviour to me.

The implicit conversion also works if the value is enclosed in curly brackets {...}.

If you add those in the query the implicit conversion will fail if the original value is too long because the last } ends up in the wrong place.

select *
from some_table
where uuid = '{'+'7DA26ECB-D599-4469-91D4-F9136EC0B4E8'+'}'

If you try the convert

SELECT CONVERT(UNIQUEIDENTIFIER, '{'+'7DA26ECB-D599-4469-91D4-F9136EC0B4E8EXTRACHARS'+'}');

you get

Msg 8169, Level 16, State 2, Line 1
Conversion failed when converting from a character string to uniqueidentifier.


Galera Replication error: log block numbers mismatch I am attempting to get Galera replication working between two nodes.

I am finding these errors in my innobackup.backup.log file:

  xtrabackup: error: log block numbers mismatch:
  xtrabackup: error: expected log block no. 671400745, but got no. 679592737 from the log file.
  xtrabackup: error: it looks like InnoDB log has wrapped around before xtrabackup could process all records due to either log copying being too slow, or  log files being too small.
  xtrabackup: Error: xtrabackup_copy_logfile() failed.

I am not sure where to do from here, I have googled a bit and didn't find anything that seemed like it could help me in my particular instance.

Any advice will be most appreciated.

I finally have solved this problem.

I found an article on launchpad that said to use this command to see if the problem was IO:
innobackupex --user=<username> --password=<password> --stream=tar -- ibbackup=/usr/bin/xtrabackup /tmp >/dev/null

This failed to, so I started thinking IO was not at fault.

I played around with this command and found this one to complete:
innobackupex --user=<username> --password=<password> --stream=xbstream --parallel=15 /tmp >/dev/null

This also showed that my ulimit was not high enough, so I had to set this higher for the backup to complete.

To force innobackupex to use xbstream instead of tar for the --stream option I but this in my.cnf on both of my galera servers:
[sst]
streamfmt=xbstream


SQL Server Trace Flag 834 on VMware It's recomended to enable trace flag 834 to use large-page on buffer pool for SQL Server running on VMware?

The tuning options kb of Microsoft (https://support.microsoft.com/en-us/kb/920093) don't mentions anything about virtualization and I have read somewhere that is mostly for dedicated machines.

Let's assume that the VM is more than 64GB in RAM and it's presented a topology of 2 v-sockets with 8 v-cores per socket for a 16 v-core VM.

Is the trace flag of help inside the hypervisor?

Large Pages change allocation of memory from 4KB to 2MB (normally), which means that the TLB (translation look-aside buffer) is improved in the CPU increasing the performance.

I've successfully used Trace flag 834 on production server in VMware for over a year. One important part before enable trace flag is that in VMware you should reserve at least the amount of memory dedicated to SQL Server, this is because the lock-pages in memory privilege required to enable large pages, "locks" the memory so it can't be swapped (paginated) to disk. So if the balloning driver kicks in, it won't try to force to reclaim memory from SQL Server.

Additionally the host should not be overcommitted in memory, if so it will degrade performance.

It should be carefully tested if there is a performance gain, so if unsure, just don't enable it. Also I have read that "Large Pages May Be Harmful on NUMA Systems" but I'm not sure if it applies to Windows/SQL Server

Trace flag 834 applies only to 64-bit versions of SQL Server. You must have the Lock pages in memory user right to turn on trace flag 834. You can turn on trace flag 834 only at startup.


Mirror db from production to test and acceptance nightly or weekly I have read this Easy or Schedulable way to move data from Production to Test
and it seems not automated. I would like to have a daily or weekly push of my production server data to one or two (remote) test databases.

My current production DB:

  Microsoft SQL Server 2008 (SP3) - 10.0.5520.0 (X64)
      Jul 11 2014 16:11:50
      Copyright (c) 1988-2008 Microsoft Corporation
      Standard Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (VM)

It might get an update to Azure SQL in the future, but it is mssql 2k8 for now.
The current test DB:

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64)
      Jul  9 2014 16:04:25
      Copyright (c) Microsoft Corporation
      Express Edition (64-bit) on Windows NT 6.2  (Build 9200: )

I have a lot of sql server jobs from http://ola.hallengren.com and I know about the existence of the import/export wizard, but I'd like to have an easy to setup weekly automated solution to export the data (logs not required) to my test and/or acceptance DB (located on other server)
So I don't think that I need a clustor or failover stuff, just a copy of the data to an other server in an easy automated way.

edit I have read that 2012 gives availability groups, but isn't that an overkill for my situation?

As I already have weekly full backup .bak and daily diff .bak files on my production server, I might just automated copy this somehow an restore. Is this a good idea? https://serverfault.com/a/472007/30695 or is there a microsoft tool with a nice wizard for it?

It is true that Availability Groups can make replicas of the active database and can support (depending on version) one or two readable replicas.  This does not work with SQL Server 2008 R2 servers.  But even if it did, this is not really the vehicle (yes, it is overkill) for getting a couple of databases to test with.

Using the backups that you are already taking to restore a database to another server is the quickest and easiest way to get a database to test against.

If you use mirroring, log shipping, or Availability Groups you can make something work within the parameters of those technologies. All three of these approaches require terminating those processes in order to do tests that include any updates to the data.

So, check this discussion for some details on using backup/restore and a useful script to help you:

Automatically restore SQL Server database from file from another server

EDIT: Getting a copy of a database to another server or using a new database name on the same server are most easily done by restoring the database from existing backups.  (Provided that your backups are frequent enough to support your tasks.)

If you restore to the same server but using a different database name, the logins are already on that server, so no extra work is needed. Likewise, the linked server is not needed in this case, since the information in the msdb database is local to your restore.
If you restore to an other server, depending on your needs you might need to add missing logins to the other server.  Since you would run the restore on the other server the linked server provides the path needed for the other server to select the information from the original server's msdb database to do the restore on the other server.

The linked server in the post was intended for case 2 above, where you need to derive the restore information for the other server from the original server.

Paul Brewer's script (or your edited version of the script) will likely help you automate your process.  But there are plenty of other scripts out there if you search for them.


Ways to Schedule Rman backups I am using oracle 11g database in windows platform. Also, my db is in archivelog mode and I am taking daily RMAN backups. Now, I need to schedule my Rman backups on daily and weekly basis. I read about dbms_scheduler and using windows task scheduler to schedule this.

Now my question is: Which is the better method to schedule this?
Do I use dbms_scheduler or batch scripts or does we have method using OEM?

Personally I would schule it using the Windows Scheduler. You might want to get robocopy if you are copying your backup files to another server. Having an email client that you can send emails from DOS can be helpful. But otherwise you would just write .BAT files and or a Powershell file. You want a process that creates a backup log and checks that log for errors. Then you can send out emails when there are errors. There should be something already written online somewhere.


Why are virtual log files not always allocated in order? Quarterly I check the VLF on all of my servers through CMS with a query from the tigertoolbox/Fixing-VLFs/ on github.  This includes suggestions (and code) for correcting what is found.  I always try to get a full understanding of what is going before making any adjustments.  90% of the time the VLF solution I apply is not the same as the recommendation, though it is usually close.

Using DBCC LOGINFO I find that several of the VLFs are not used in sequential order.  I am trying to understand why.   This highly voted answer to; DBCC SHRINKFILE on log file not reducing size even after BACKUP LOG TO DISK
 says it can happen, but not why.

  Because virtual log files are not always allocated in order,

This would seem to conflict with Jonathan Kehayias; An XEvent a Day (23 of 31) – How it Works – Multiple Transaction Log Files

We can see that of the 130 VLF only two (line 51 & 52) are currently used.  The largest FSeqNo is 41808, subtract 130 = 41678.  I don't see any FSeqNo lower then 41678 so presumably all have been used in the last 130 VLF rollovers.

IF we look at lines 108 - 109, we see line 110 is written to first, then line 109, then line 108. Also the parity is off (not sure what this adds to the scenario) The LSN show they are created in the opposite order.

I would expect that once whatever caused the disruption in sequence order has passed, the next time through the VLFs would be written to in order created.  Why are they not?

Notice line 86 is in both images, to show consistency.

The example is from a SQL 2014 server, the database is currently in FUll recovery and has been for a year or more.  The server has is part of 3 node AG, the results above are taken from the secondary server where the t-logs are backed up hourly.  The last 10 grows occurred through auto grow of 1000MB (2014+ creates 1VLF).  The log file currently has a Initial size of 115,000MB and autogrow of 1,000MB.

Edit
Max Vernon Thank you for the link in your answer.  I understood how they got written out of order, but I am making an assumption that they continued to written out of order once the blocking event was cleared. By following FSegNo backwards you can recreate how it occured. Line 52 is the current VFL with FSeqNo 41808.. At line 35 the FSeqNo is 41791. The FSeqNo 41790 is at line 130 (last line). Presumably VFL 130 was created when FSeqNo 41790 was being written.  BUT there have been 40,000 events of writing to the next VLF, with 130 VLF that would be more than 300 cycles through the log.

A couple of hours later the list looks this

Paul Randal details a scenario where log VLFs may be allocated out of the expected sequence in this blog post.

Essentially, log file growth may result in unexpected allocation order, even in simple mode, when the existing VLFs cannot be re-used.

Why are you wondering about the order-of-allocation?  VLFs are used in FSeqNo order, both when writing new log records, and when performing rollback and rollforward recovery operations.

Having a database in Full Recovery model does not prevent out-of-order VLF allocation order.  I've created a minimally complete verifiable example:

SET NOCOUNT ON;
USE master;
GO
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = N'TestVLFSeq')
BEGIN
    ALTER DATABASE TestVLFSeq SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TestVLFSeq;
END
GO
CREATE DATABASE TestVLFSeq
ON (NAME = 'system', FILENAME = 'C:\temp\TestVLFSeq.mdf', SIZE = 10MB, FILEGROWTH = 10MB, MAXSIZE = 100MB)
LOG ON (NAME = 'log', FILENAME = 'C:\temp\TestVLFSeq.ldf', SIZE = 1MB, FILEGROWTH = 1MB, MAXSIZE = 10MB);
GO

ALTER DATABASE TestVLFSeq SET RECOVERY FULL;
BACKUP DATABASE TestVLFSeq TO DISK = 'NUL:';
GO

SELECT d.name
    , d.recovery_model_desc
FROM sys.databases d
WHERE d.name = N'TestVLFSeq';
GO

output:

╔════════════╦═════════════════════╗
║    name    ║ recovery_model_desc ║
╠════════════╬═════════════════════╣
║ TestVLFSeq ║ FULL                ║
╚════════════╩═════════════════════╝

USE TestVLFSeq;
GO

CREATE TABLE dbo.TestData
(
    someVal varchar(8000) NOT NULL
        CONSTRAINT DF_TestData
        DEFAULT ((CRYPT_GEN_RANDOM(8000)))
);
GO

INSERT INTO dbo.TestData DEFAULT VALUES
 /* insert 64 rows - ~512 KB of log usage */
GO 64

DBCC LOGINFO
GO

output:

╔════════════════╦════════╦══════════╦═════════════╦════════╦════════╦════════╦═══════════╗
║ RecoveryUnitId ║ FileId ║ FileSize ║ StartOffset ║ FSeqNo ║ Status ║ Parity ║ CreateLSN ║
╠════════════════╬════════╬══════════╬═════════════╬════════╬════════╬════════╬═══════════╣
║              0 ║      2 ║   253952 ║        8192 ║     34 ║      2 ║     64 ║         0 ║
║              0 ║      2 ║   253952 ║      262144 ║     35 ║      2 ║     64 ║         0 ║
║              0 ║      2 ║   253952 ║      516096 ║     36 ║      2 ║     64 ║         0 ║
║              0 ║      2 ║   278528 ║      770048 ║      0 ║      0 ║      0 ║         0 ║
╚════════════════╩════════╩══════════╩═════════════╩════════╩════════╩════════╩═══════════╝

As you can see in the above output, we have 3 VLFs allocated.

Now, in a separate query window, run this to create log records that cannot be cleared, even by a log backup:

USE TestVLFSeq;
BEGIN TRANSACTION
INSERT INTO dbo.TestData DEFAULT VALUES
GO

Now, switch back to the first query window, and backup the database and the log to clear any VLFs that cleared:

BACKUP DATABASE TestVLFSeq TO DISK = 'NUL:' WITH INIT;
BACKUP LOG TestVLFSeq TO DISK = 'NUL:' WITH INIT;
GO

(The BACKUP DATABASE is not strictly required, but what the heck, we're backing up to "ether" anyway).

Now, when we check DBCC LOGINFO, we see:

╔════════════════╦════════╦══════════╦═════════════╦════════╦════════╦════════╦═══════════╗
║ RecoveryUnitId ║ FileId ║ FileSize ║ StartOffset ║ FSeqNo ║ Status ║ Parity ║ CreateLSN ║
╠════════════════╬════════╬══════════╬═════════════╬════════╬════════╬════════╬═══════════╣
║              0 ║      2 ║   253952 ║        8192 ║     34 ║      0 ║     64 ║         0 ║
║              0 ║      2 ║   253952 ║      262144 ║     35 ║      0 ║     64 ║         0 ║
║              0 ║      2 ║   253952 ║      516096 ║     36 ║      2 ║     64 ║         0 ║
║              0 ║      2 ║   278528 ║      770048 ║      0 ║      0 ║      0 ║         0 ║
╚════════════════╩════════╩══════════╩═════════════╩════════╩════════╩════════╩═══════════╝

The first and second VLF have been cleared.  If we now insert another 64 rows into the table, and look at DBCC LOGINFO:

INSERT INTO dbo.TestData DEFAULT VALUES
 /* insert 64 rows - ~512 KB of log usage */
GO 64

DBCC LOGINFO
GO

We see:

╔════════════════╦════════╦══════════╦═════════════╦════════╦════════╦════════╦═══════════════════╗
║ RecoveryUnitId ║ FileId ║ FileSize ║ StartOffset ║ FSeqNo ║ Status ║ Parity ║     CreateLSN     ║
╠════════════════╬════════╬══════════╬═════════════╬════════╬════════╬════════╬═══════════════════╣
║              0 ║      2 ║   253952 ║        8192 ║     38 ║      2 ║    128 ║                 0 ║
║              0 ║      2 ║   253952 ║      262144 ║     35 ║      0 ║     64 ║                 0 ║
║              0 ║      2 ║   253952 ║      516096 ║     36 ║      2 ║     64 ║                 0 ║
║              0 ║      2 ║   278528 ║      770048 ║     37 ║      2 ║     64 ║                 0 ║
║              0 ║      2 ║   253952 ║     1048576 ║     39 ║      2 ║     64 ║ 38000000039500007 ║
║              0 ║      2 ║   253952 ║     1302528 ║      0 ║      0 ║      0 ║ 38000000039500007 ║
║              0 ║      2 ║   253952 ║     1556480 ║      0 ║      0 ║      0 ║ 38000000039500007 ║
║              0 ║      2 ║   286720 ║     1810432 ║      0 ║      0 ║      0 ║ 38000000039500007 ║
╚════════════════╩════════╩══════════╩═════════════╩════════╩════════╩════════╩═══════════════════╝

Pretty clearly, we have VLFs that are "out of sequence".  However, since SQL Server uses the FSeqNo to perform recovery-related operations, it doesn't matter that the VLFs are not physically "in order".

If, for some reason, you programmatically expect the VLFs to be in FSeqNo order, you could query DBCC LOGINFO; like this:

DECLARE @cmd nvarchar(max);
IF OBJECT_ID(N'tempdb..#loginfo', N'U') IS NOT NULL
DROP TABLE #loginfo;
CREATE TABLE #loginfo
(
    RecoveryUnitID int
    , FileID int
    , FileSize int
    , StartOffset bigint
    , FSeqNo int
    , [Status] tinyint
    , Parity tinyint
    , CreateLSN bigint
);
SET @cmd = 'DBCC LOGINFO;';
INSERT INTO #loginfo
EXEC sys.sp_executesql @cmd;
SELECT *
FROM #loginfo li
ORDER BY /* put "0" FSeqNo rows at the end */
    CASE
        WHEN li.FSeqNo = 0 THEN CONVERT(int, POWER(CONVERT(bigint,2),31) - 1)
        ELSE li.FSeqNo
    END;

Which has the following output for the "strange" VLF arrangement:

╔════════════════╦════════╦══════════╦═════════════╦════════╦════════╦════════╦═══════════════════╗
║ RecoveryUnitID ║ FileID ║ FileSize ║ StartOffset ║ FSeqNo ║ Status ║ Parity ║     CreateLSN     ║
╠════════════════╬════════╬══════════╬═════════════╬════════╬════════╬════════╬═══════════════════╣
║              0 ║      2 ║   253952 ║      262144 ║     35 ║      0 ║     64 ║                 0 ║
║              0 ║      2 ║   253952 ║      516096 ║     36 ║      2 ║     64 ║                 0 ║
║              0 ║      2 ║   278528 ║      770048 ║     37 ║      2 ║     64 ║                 0 ║
║              0 ║      2 ║   253952 ║        8192 ║     38 ║      2 ║    128 ║                 0 ║
║              0 ║      2 ║   253952 ║     1048576 ║     39 ║      2 ║     64 ║ 38000000039500007 ║
║              0 ║      2 ║   253952 ║     1302528 ║      0 ║      0 ║      0 ║ 38000000039500007 ║
║              0 ║      2 ║   253952 ║     1556480 ║      0 ║      0 ║      0 ║ 38000000039500007 ║
║              0 ║      2 ║   286720 ║     1810432 ║      0 ║      0 ║      0 ║ 38000000039500007 ║
╚════════════════╩════════╩══════════╩═════════════╩════════╩════════╩════════╩═══════════════════╝

VLFs marked with a Status of "2" indicates the VLF cannot be re-used.  You can determine what is preventing log re-use using the following query:

SELECT d.name
    , d.recovery_model_desc
    , d.log_reuse_wait_desc
FROM sys.databases d
WHERE d.name = N'TestVLFSeq';

If you run this code while there is an open transaction in the database, as in the example above, you'll see the output looks like:

╔════════════╦═════════════════════╦═════════════════════╗
║    name    ║ recovery_model_desc ║ log_reuse_wait_desc ║
╠════════════╬═════════════════════╬═════════════════════╣
║ TestVLFSeq ║ FULL                ║ ACTIVE_TRANSACTION  ║
╚════════════╩═════════════════════╩═════════════════════╝

The log_reuse_wait_desc column can have the following possible values, taken from the Microsoft Transaction Log Documentation:

CHECKPOINT - No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file (VLF).  Applies to all recovery models.  Applies to SQL Server 2008 through SQL Server 2017.

This is a routine reason for delaying log truncation.

When a database has a memory-optimized data filegroup, you should expect to see the log_reuse_wait column indicate checkpoint or xtp_checkpoint.

You can force a checkpoint using the CHECKPOINT T-SQL command within the context of the database in question.
LOG_BACKUP - A log backup is required before the transaction log can be truncated.  Applies to full or bulk-logged recovery models only.  Applies to SQL Server 2008 through SQL Server 2017

Run BACKUP LOG <database> TO ... to create a backup of the log.
ACTIVE_BACKUP_OR_RESTORE - Applies to SQL Server 2008 through SQL Server 2017

Use the following query to see what backups are currently running:

SELECT d.name
    , der.command
    , der.start_time
    , der.percent_complete
FROM sys.dm_exec_requests der
    INNER JOIN sys.databases d ON der.database_id = d.database_id
WHERE der.command = N'BACKUP DATABASE'
    OR der.command = N'BACKUP LOG';

ACTIVE_TRANSACTION - Applies to SQL Server 2008 through SQL Server 2017

There is an open transaction that has not been committed or rolled-back.  Note this open transaction may not be actively running - it may be poorly written code that opens a transaction then performs some long-running action at the client, finally committing the transaction much later.

Consider the following:

A long-running transaction might exist at the start of the log backup. In this case, freeing the space might require another log backup. Note that long-running transactions prevent log truncation under all recovery models, including the simple recovery model, under which the transaction log is generally truncated on each automatic checkpoint.
A transaction is deferred. A deferred transaction is effectively an active transaction whose rollback is blocked because of some unavailable resource. For information about the causes of deferred transactions and how to move them out of the deferred state, see Deferred Transactions.
Long-running transactions might also fill up tempdb's transaction log. Tempdb is used implicitly by user transactions for internal objects such as work tables for sorting, work files for hashing, cursor work tables, and row versioning. Even if the user transaction includes only reading data (SELECT queries), internal objects may be created and used under user transactions. Then the tempdb transaction log can be filled.

DATABASE_MIRRORING - Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database.  Applies to full recovery model only.  Applies to SQL Server 2008 through SQL Server 2017

If you see a large number of VLFs with Status = 2, and database mirroring is enabled, ensure database transactions are being applied to the mirror in a timely fashion.
REPLICATION - During transactional replication, publication-relevant-transactions have not been delivered to the distribution database.  Applies to full recovery model only.  Applies to SQL Server 2008 through SQL Server 2017.
DATABASE_SNAPSHOT_CREATION - Applies to SQL Server 2008 through SQL Server 2017

A database snapshot is being created.  Check the following for details on snapshots that exist on your SQL Server:

SELECT SnapshotDBName = d.name
    , SnapshotCreateDate = d.create_date
    , SourceDBName = d_source.name
    , SourceCreateDate = d_source.create_date
FROM sys.databases d
    INNER JOIN sys.databases d_source ON d.source_database_id = d_source.database_id
WHERE d.source_database_id IS NOT NULL
ORDER BY d.create_date;

LOG_SCAN - This is a routine, and typically brief, cause of delayed log truncation.
AVAILABILITY_REPLICA - An Always On Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database. Applies to SQL Server 2012 through SQL Server 2017.
OLDEST_PAGE - Applies to SQL Server 2012 through SQL Server 2017

If a database is configured to use indirect checkpoints, the oldest page on the database might be older than the checkpoint log sequence number (LSN). In this case, the oldest page can delay log truncation. (All recovery models)

For information about indirect checkpoints, see Database Checkpoints (SQL Server).
OTHER_TRANSIENT - This value is currently not used.  Applies to SQL Server 2012 through SQL Server 2017
XTP_CHECKPOINT - When a database uses a recovery model and has a memory-optimized data filegroup, you should expect to see the log_reuse_wait column indicate checkpoint or xtp_checkpoint. - Applies to SQL Server 2014 through SQL Server 2017


What are the names of SQL Server database statuses 24, 1024 and 65544? using the following query:

SELECT Name, Status FROM dbo.sysdatabases
WHERE name IN ( 'db_name' )

I get in some cases status = 24, other times 1024 and other times 65544

I see that even when status is 24 I can open and check content of the database from SQL Management Studio but not from a legacy application which checks status to be at least 1024.

when I ask DBAs to fix this issue they run some magic scripts and status goes up to 65544 and all works fine, I would like to have some names for these statuses not just numbers like 24, 1024, 65544.

I have already checked this link: sysdatabases

which contains this table:

  Status bits, some of which can be set by the user with ALTER DATABASE
  (read only, offline, single user, and so on):

  1 = autoclose; set with ALTER DATABASE.

  4 = select into/bulkcopy; set with ALTER DATABASE RECOVERY.

  8 = trunc. log on chkpt; set with ALTER DATABASE RECOVERY.

  16 = torn page detection, set with ALTER DATABASE.

  32 = loading.

  64 = pre recovery.

  128 = recovering.

  256 = not recovered.

  512 = offline; set with ALTER DATABASE.

  1024 = read only; set with ALTER DATABASE.

  2048 = dbo use only; set with ALTER DATABASE RESTRICTED_USER.

  4096 = single user; set with ALTER DATABASE.

  32768 = emergency mode.

  4194304 = autoshrink , set with ALTER DATABASE.

  1073741824 = cleanly shutdown.

but I don't see 24 and 65544 in there :(

The others are combinations of the documented flags, e.g. 24 is 16 and 8.

This is a method for simulating optional parameters and is used in e.g. C iirc, the numbers 1, 2, 4, 8 and so on are structured like that because they correspond to binary values that combined in any unique way create a unique number. The function that accepts them then use bitmasking to extract each individual number. E.g. if you send the value 6 to a function, then we know that this is a combination of 4 and 2, to find this out using a bit mask we would do:

    0110 // 6
AND 0010 // 2
 == 0010

    0110 // 6
AND 0100 // 4
 == 0100

Both statements would be evaluated to true, thereby we have found out the individual values.

Note: In the real world, we would iterate through all values from 1, 2, 4, 8, 16 and so on to the maximum value of an integer (or whatever datatype the parameter was set to).


Scheduled copy of large table from SQL Server 2000 to 2008 I have a table on SQL Server 2000 with 10 million records and 20 columns. The table is rebuilt once a day and is essentially a reporting table, not a transactional table. Of the 10 million records, some, but not all, will change on a daily basis.

I have the option to use SQL Server 2008, but only for this table at the time being. The reason for bringing this table to the SQL Server 2008 instance is to have this table leverage the Full Text Search capabilities 2008 offers.

Given this situation, what is the best way to go about dumping this table across servers on a routine, daily basis?

(I suppose I run the risk of this question being closed as a duplicate of How to move data from big table,
but the scenario I am describing seems sufficiently unique from that question.)

I would likely put a TRIGGER on the 2000 table and insert the captured changes into an audit table, then either immediately sync the 2008 database with just those changes through SSIS ( or even a linked server if that suits you ) or do an overnight sync, depending on what would have the less impact on business-hours performance and the network as a whole, truncating the audit table on successful transfers either way.  This would ensure the changes are easy to acquire and small enough to pull across the network without generating calls from your infrastructure engineers.  The audit table itself could even live in a different file group to reduce disk contention if necessary.

That said, there's probably half a million ways to go about doing this, and your specific, optimal solution is going to revolve largely around the details of how your environment operates today and even the specific structure of the table in question.  For instance, if the "rebuild" you've mentioned is a severe example, involving either TRUNCATE or a complete DROP and CREATE, the simple TRIGGER suggestion I've made is not going to be the solution for you without a significantly extended amount of effort, especially compounded if your source table is using an IDENTITY column.

In that case, a Lookup task in a Data Flow may seem like a reasonable route at a glance, but this effectively results in the entire table being pulled across the network anyway for the comparison.  Comparison logic at this point is just unnecessary overhead, you may as well stick the receiving catalog in SIMPLE recovery mode and stage all of the data you've received on the other side of the pipe.  Doing an ALTER ... SWITCH after the transfer would at least then limit the lock on your reporting table to a single meta data operation, keeping it otherwise completely available to the reports being generated from it.


Restoring and upgrading multiple databases I have a SQL Server 2005 instance on my PROD server. I have been given a task to migrate databases from that instance to a different box (SQL Server 2008 R2 on Windows 2008 R2). The current instance has a maintenance plan set up, which backs up 8 different databases every night to a single .bak file.

How do I take that bak file and restore it onto the new SQL Server 2008 R2 instance? I have tried using SQL Server 2008 R2 SSMS, and set the restore from option to device and point it to that .bak file, but what should I target restore to since I have multiple databases to be restored in the new instance?

What is the best approach to migrate all databases in single shot to the new instance without doing one at a time?

First thing i would like to highlight is you should have the system backups in addition to user databases handy when you said you need to migrate from SQL2k5 to SQL2k8R2.

You can copy all the latest backups from the current server onto the Windows2K8 box you mentioned.

Once copied, the first thing you should be looking is restoring the system database backups as mentioned here Backup and Restore system databases

Doing the above will make sure all the logins, SSIS packages, jobs etc would be attached as well when migrating to new version.

Now, you should start restoring the user database backups copied from old server to this new box as guided in Restore database from existing full backups

In addition to above you can use Powershell to completely migrate all the databases in go as you mentioned , from here.

Also, you can refer Migrate SQL server from one machine to another


Will a primary key be added as a clustered index? I've inherited a database where no primary keys were defined on the tables. There are also no clustered indexes assigned to the tables.

If I perform an alter table to assign a primary key will this result in SQL Server also creating a clustered index? If so should I expect slowness on the database due to IO from the data being repositioned on the harddisk?

Yes, SQL Server will create the primary key as clustered by default, but you don't have to accept the defaults.

ALTER TABLE dbo.foo
  ADD CONSTRAINT pk PRIMARY KEY (bar); -- clustered

ALTER TABLE dbo.foo
  ADD CONSTRAINT pk PRIMARY KEY CLUSTERED (bar); -- clustered

ALTER TABLE dbo.foo
  ADD CONSTRAINT pk PRIMARY KEY NONCLUSTERED (bar); -- non-clustered

And yes, you will see some I/O activity here, so if it is a busy system, best to save this for quieter hours or a maintenance period.


MySQL how to find top consuming queries I have the need to find top resource consuming queries in my Percona 5.6 server so that those queries can be fine tuned to improve the performance of the database.

Also, is it possible to get the history of highly resource intensive queries for the past 3 days? Is it possible to fetch the info from the logs?

Thanks.!

You can use pt-query-digest which is part of the Percona Toolkit to identify queries that are running long. It can analyze queries from General Query log, Binary Log and the Slow Query Log.

Usage: pt-query-digest slow.log to get the slowest queries from logs, where slow.log is the log file name.
The log file is usually in the default directory.

The --history option can be used to save historical data and is helpful for analyzing Query performance over time.

If the slow-query-log option is disabled, you will need to enable it to log the query information.

Refer to the Slow Query Log official documentation on how to enable it and for all the available configuration parameters.


Why does the system generated TNSNAMES.ORA have two entires, one with a full connect descriptor and another with just a network address? My TNSNAMES.ORA file was auto-generated when I installed my database. It generates with two entries for my database however. One entry looks "complete" with a full connect descriptor, while the 2nd entry is just the network address of the listener.  What's the difference between the two and when would you use one versus the other?

DB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db2)
    )
  )

LISTENER_DB2 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))

No, it generated 1 entry for the database (DB2), and another for the listener (LISTENER_DB2).

Then your local_listener parameter was set to LISTENER_DB2, so your database could use this address to find the listener and register into it, since it is not listening on the default port (1521).


Query works locally but not on AWS RDS We have a large query that works fine locally but deadlocks on AWS RDS. According to CloudWatch, we are not resource-bound on CPU, disk or IOPS, and nothing else should be accessing these tables.
The gist of the query is a three step process.
Pivoting
The upstream data is millions of records of (site, key, value) with a dozen or so standard keys. The pivot (which works on AWS) creates a new table with (site, value1, value2, value3,...) records. There are over 800k rows in the pivot table.
A modest amount of processing is done at this stage, mostly COALESCE() to allow some key-value pairs to override other key-value pairs.
Indexes and Analyzing
We add the indexes required to optimize the following query and VACUUM ANALYZE the newly created tables.
Denormalization
The pivot table values are foreign keys into various other tables. We denormalize the references for subsequent processing with a Java application. For performance reasons we create a table although a materialized view would probably also work. The table creation is a straightforward CREATE TABLE table_name AS SELECT * FROM view_name.
The foreign tables have a modest amount of data, typically on the order of dozens to hundreds of entries although one may hit 10,000+ records. All have the necessary indexes to optimize the query (per EXPLAIN).
A modest amount of processing is done at this stage, mostly CASE statements which tweak data irregularities.
The denormalization takes a long time (~hour) but always succeeds on my desktop. It has consistently failed on AWS, even at 24+ hours. As I mentioned above it's not resource-limited and we have verified that the hangup is locks on the pivot table.
Does anyone have any idea what could be the cause or a fix? It's a straightforward view with a fair number of scalar subqueries on foreign keys but no other joins. The table is large, over 800k records, but it's one record in -> one record out.
Our next step is to check the behavior with a smaller dataset but success would only tell us that we could try breaking apart our query into smaller pieces, not why we're seeing the deadlocks and what we can do to prevent them.

Our final solution was to split the query so we did pivoting and denormalization in separate steps. This is probably a cleaner solution anyway since it follows the "just do one thing" principle so will probably be easier to maintain.

Thanks for all of the help!


Speed up bulkinsert on sql server We're doing some testing and this involves adding 10.000 rows every 10seconds in a table that has 4 int columns and one of them is clustered index.
When we run the app the first bulkcopy takes around 1 minute to complete. The second takes 40 seconds to complete and after about 8 bulkcopy the time to complete the transaction is 1-2 seconds. Every transaction has 10.000 rows with random numbers. Is there anyway to prepare the database for fast bulkcopy from the first transaction?

1/ The first I have in mind is that time is spent on checking FK constraint in your destination table while new rows are inserted. Firstly, I would check if referenced tables have PKs defined (or even clustered from performance point of view). Give it a try by performing some bulk inserts after you have disabled those FKs.

2/ Statistics might not be updated on referenced table, case when a statement like this should be executed for each referenced table:

UPDATE STATISTICS tablename;

3/ Long term, you should take into consideration partitioning that table. This is the perfect scenario for doing this.

4/ Disable indexes on destination table, perform bulk insert then re-enable the indexes. Not-recommended, but give it try to see the results and to see if this is the root cause of your poor performance for bulk inserts.

5/ Have a look at waiting threads while a bulk insert is executing.

6/ Post the result of wait statistics in order to see which types of waits are commonly arrive on your server.

Tell us the result and we'll come with more details depending on the context.


a couple of notes, seems Rob Farley would also write something similar (I just read his comment now). Secondly I missed your 10 minute rule - I'm going to leave some of the fun for you too
/*
create table #t (msgtime datetime2, userid int)

insert #t values ('2020-12-14 10:07:49.358739',11)
insert #t values ('2020-12-14 10:08:16.156553',11)
insert #t values ('2020-12-14 10:08:32.195645',11)
insert #t values ('2020-12-14 13:36:16.103253',12)
insert #t values ('2020-12-14 13:46:43.871769',12)
insert #t values ('2020-12-14 13:50:42.867178',12)
insert #t values ('2020-12-14 13:55:30.530697',12)
insert #t values ('2020-12-14 13:56:42.472823',12)
insert #t values ('2020-12-14 13:57:05.880580',12)
insert #t values ('2020-12-14 13:57:40.377021',4)
insert #t values ('2020-12-14 13:58:12.723215',12)
insert #t values ('2020-12-14 14:34:30.633401',4)
insert #t values ('2020-12-15 10:16:18.188387',4)
insert #t values ('2020-12-15 10:16:30.735493',4)
insert #t values ('2020-12-15 10:18:03.501372',4)
insert #t values ('2020-12-15 12:50:46.953427',4)
insert #t values ('2020-12-15 12:51:11.772787',4)
insert #t values ('2020-12-15 12:59:34.264237',11)
insert #t values ('2020-12-15 13:02:10.622184',12)
insert #t values ('2020-12-14 10:28:27.489880',12)
insert #t values ('2020-12-14 10:28:43.793308',12)
insert #t values ('2020-12-14 19:29:56.208375',12)
insert #t values ('2020-12-14 19:45:34.401379',12)

select * from #t order by userid, msgtime

*/

;with times as
(select t1.userid, t1.msgtime as prevmsgtime ,
    LEAD(msgtime, 1, NULL) OVER
    (
       Partition By UserID
        ORDER BY
            msgtime
    ) AS nextmsgtime
from #t t1
)
, seq as
(
select
userid,
prevmsgtime ,
nextmsgtime ,
datediff(mi, prevmsgtime , nextmsgtime ) as timediff,
-- If nextmsgtime is null wwe have changed userid
-- or it the time difference is more than 2 mins - a new group
case when (nextmsgtime  is null) or (datediff(mi, prevmsgtime , nextmsgtime ) > 2)
then convert(int,1)
else convert(int,0)
end as newseq
from times
where datediff(mi, prevmsgtime , nextmsgtime ) is not null

)
,
grouper as
(
select
userid,
prevmsgtime  as prevmsgtime ,
nextmsgtime  as nextmsgtime ,
timediff,
newseq,
-- we can calculate a column to group by by summing up by user the number of times we start a new group
sum(newseq) over (partition by userid order by prevmsgtime ) as groupcol
from seq
)
-- get the min and max msgtime when we have a group
select
userid,
min(prevmsgtime ) as prevmsgtime ,
max(nextmsgtime ) as nextmsgtime ,
count(*)+1 as nrows
from grouper
where newseq = 0
group by userid,groupcol
union all
-- just get the values if there isn't a group
select
userid,
prevmsgtime ,
nextmsgtime ,
1 as nrows
from grouper where newseq = 1
order by userid, prevmsgtime


Procedure returning different values I have a procedure TempSalesUpdateID which gets all ProductName's column in TempSales table and set it's corresponding ProductID.

ALTER PROCEDURE [dbo].[TempSalesUpdateID]
AS
UPDATE T
SET        T.ProductID = P.ProductID
FROM       TempSales T
INNER JOIN Products P
ON         P.PN=T.ProductName

When I execute it through SQL Server Management Studio it runs perfectly: when it finds a corresponding ID, it fills ProductID column, otherwise leaves it NULL.

When I run it from my C# Windows Form Application, it puts almost random numbers instead of leaving NULL (the ones which doesn't have a corresponding ID).

I say almost because: 13098 rows have value 1 instead of NULL, 14742 rows have value 2 instead of NULL, 13601 value 3, etc.

Could you please help me? I'm really lost here

The problem was solved by using a new connection to execute the procedure.

I think it might help if someone is on the same situation.


How to implement many-to-many relationship with one table I have a table:

CREATE DATABASE IF NOT EXISTS test;
USE test;

CREATE TABLE IF NOT EXISTS emp_list(
id INT UNSIGNED NOT NULL AUTO_INCREMENT primary key,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
job_position  VARCHAR(20) NOT NULL,
salary decimal(15,2) UNSIGNED,
);

INSERT INTO emp_list ( id, first_name, last_name, job_position, salary) VALUES ( null, 'Ivan', 'Noscov', 'Director', 69000);
INSERT INTO emp_list ( id, first_name, last_name, job_position, salary) VALUES ( null, 'Igor', 'Malov', 'Accountant', 54000);
INSERT INTO emp_list ( id, first_name, last_name, job_position, salary) VALUES ( null, 'Ira', 'Solnceva', 'Salesperson', 27000);
INSERT INTO emp_list ( id, first_name, last_name, job_position, salary) VALUES ( null, 'Ivan', 'Noscov', 'Security guard', 15000);
UPDATE employees.emp_list SET last_name = 'Varnaev' WHERE id=4;
INSERT INTO emp_list ( id, first_name, last_name, job_position, salary) VALUES ( null, 'Natasha', 'Bolshova', 'Salesperson', 27000);

I need update the database schematic so that it is possible to store the information about
subordinates. No restrictions: each employee can have several subordinates (or not have them)
and may have more than one boss (or no boss at all). Request a sample of all
of a certain employee's subordinates.

Need to do this by adding a new column and need to use a foreign key to create relationships

You cannot do this with one additional column You need to create a new table for relations between employees.

CREATE TABLE IF NOT EXISTS emp_relations(
emp_id1 INT UNSIGNED NOT NULL,
emp_id2 INT UNSIGNED NOT NULL,

FOREIGN KEY (emp_id1 )
      REFERENCES emp_list(id),
FOREIGN KEY (emp_id2 )
      REFERENCES emp_list(id),

UNIQUE KEY(emp_id1 ,emp_id2)
 );

Link both columns to emp_list.id. Make emp_id1 and emp_id2 together unique.

This way you will have hierarchy and many to many relations.

Mysql - Foreign keys

Mysql - Unique keys


How to check SqlSetPos compatibility on database? I have a database tool with the possibility to suppress the use of SqlSetPos. According to the documentation, some databases are not fully compatible with this function. How can I check if my database is compatible with this function or not?

You'll need to check the documentation for the specific ODBC driver you are using. I would expect the SqlSetPos function to return SQL_ERROR or SQL_SUCESS_WITH_INFO if the driver doesn't support (or partially supports) the function. In that case, a subsequent call to SQLGetDiagRec should return a SQLSTATE with the error/warning cause, such as IM001 (Driver does not support this function).  See the SQLSetPos function reference for details.


Excel Import ID, Data Type Double I get a file from another department that contains an id column. The id column is mostly numbers, but also contains some letters.

I receive the file in XLS format.

When I try to use the import function of SQL, it reads the id column as a double integer type. Is that not incorrect? Can double datatype have letters? Either way, it is correctly importing the number values for the id, but not the values that begin with letters. It also does not advise of any errors or problems.

I have tried to convert it to a flat file and import it that way, but I am fighting with the date column data types as well. (They are in 1/13/2011 format - I don't know why the import wizard can't import this either).

I would like the end result to be a nvarchar. How can I go about getting this file to convert properly?

The ID should definitely be an NVARCHAR and not a double, you can change the type it thinks it is in the mappings. Depending on your data validation settings it may be truncating or nullifying your non-double IDs to fit them.

As for the date issue, I can't seem to get that particular style of date to fail an import. If there is more to that field than just the date portion it's possible the time portion is causing the problem.


How do I effectively handle very skewed data? Statistics up to date but don't seem to help I have a table, dbo.ClaimBilling, with 130,000 rows. In this table, the column OperatorID is a varchar(max) and is heavily skewed.  125,000 rows are 'user1', and the remaining 5000 rows are split between 6 other values, with 'user2' having a total of 3 records.

There is a non-clustered index on OperatorID, and the clustered index is the primary key, IDClaimBilling.

I have the following query currently:

SELECT DISTINCT IDClaimBilling
FROM dbo.ClaimBilling cb
INNER JOIN dbo.BillingItem bi
    ON cb.IDClaimBilling = bi.ClaimID
WHERE OperatorID = @operator

No matter what value @operator is, the estimate of rows from ClaimBilling is ~4000, which is not close to what any value would return, and it's always a clustered index scan, it doesn't use the operatorID index.  If I remove the join and do

SELECT DISTINCT IDClaimBilling
FROM dbo.ClaimBilling
WHERE OperatorID = @operator

then it does use the OperatorID index, but again the estimates are wrong regardless of the value of @operator, this time always estimating ~18,000 or so.

I did an UPDATE STATISTICS dbo.ClaimBilling WITH FULLSCAN before running the queries.

Why are these estimates so wrong even if the statistics know exactly how many rows there are per value?

I'm declaring and assigning @operator a value in testing.  It was originally part of a procedure and I assumed that was the problem, but it behaves the same when used in an ad hoc statement as well.

The query only runs when the user first logs in, so only maybe a couple times a day per user.

Community Wiki answer generated from comments left on the question

If you are running the query with @operator as a variable, SQL Server cannot 'sniff' the value in the variable, so it will use the statistics' average density value to calculate an estimate.  No matter what value you assign to the variable, this average value estimate will always be the same.

One way to resolve this is by using an OPTION (RECOMPILE) query hint. This will compile a fresh plan on each execution, with the plan optimized for the specific value in the variable at that time. This comes at the cost (usually small) of a statement recompilation each time.

You could also modularize the code.  You could use an IF statement and check the value of operatorid and if it's "user1" call one stored proc, let's say sp_user1. If it is not "user1" call a different procedure.  The first sp would be optimized for "user1" and the other for the rest of the value.  You could also use option (recompile) in the second sp for non-"user1" values if required.

It might also be a decent use-case for dynamic SQL. That would turn your @operator variable into a literal value, and would customize the plan for each user. Since there's only 7 users in that table I don't think that would really pose an issue for you.

For more information, see:

Local Variables vs. Parameterized Stored Procedures in SQL Server (Video) by Kendra Little


Is it possible to install a service pack to a SQL 2012 Express instance? I have a SQL 2012 Express instance that is on Service Pack 3. I am trying to find a way to update it to Service Pack 4 but this doesn't seem to be possible or maybe I am just not finding the right answer? Anyone know if this is possible and if so, can you provide a little guidance?

Thanks!

I just now figured it out. right after posting the question... should have waited a few more min.

I was trying to install SQL 2012 SP4 but it wasn't letting me choose the express instance, to apply the update to, only our other standard edition instance. I realized I had downloaded the x64 SP4 package and that when the Express instance was installed it was installed with the x86 version. I downloaded the x86 SP4 package and now it is letting me update the instance.

Maybe this will help someone in the future.


Return the lowest value How do I go about getting the course number (CNO) of all the lowest enrolled by department?
For example if math had coursenum 123 and the 0 enrolled, and anoher coursenum 555 with 6 enrolled, it would return cno = 123

QUERY

--get the course number of the lowest course in the department
select select c.cno
from course c,
     -- number of students per course
(
     select c.dname as tempDname, c.cno as tempCno, count(e.sid) as tempCount
     from course c
     left outer join enroll e
     on e.dname = c.dname and e.cno = c.cno
     group by c.dname, c.cno
) temp
where  c.dname = tempDname and c.cno =

At the moment I am stuck. The sub query at the moment returns the number of students in each class.

Sub query returns:

Check out Analytical functions. Here's how you could solve your problem using the RANK() analytical function in Oracle (for simplicity I select from a table called "courses" and not your subquery; the table has the same structure as the one in your picture):

select * from (
    select coursedept,
           coursenum,
           numstudent,
           rank() over(partition by coursedept order by numstudent) num_s_rank
     from courses
 ) where num_s_rank = 1;

Note that in case of ties multiple rows get the same rank, which means you could get more than one course per department.


Managed backup large database to Azure Got a bit of an issue here that I need some help to solve.

Hosting a database, that is about 400Gigs. This is giving us a little headache getting it to work with Managed Backup to Azure Storage Container.

This is the error we get

10/21/2018 12:45:18,spid149,Unknown,Write to backup block blob device https://NAME.blob.core.windows.net/CONTAINER/DB_NAME_20181021120709+02.bak failed. Device has reached its limit of allowed blocks.

The setup was done with

EXEC managed_backup.sp_backup_config_basic

@database_name = 'DB_NAME',
@enable_backup=1,
@container_url = 'https://NAME.blob.core.windows.net/CONTAINER',
@retention_days=30;

USE msdb;
GO
EXEC managed_backup.sp_backup_config_schedule
     @database_name =  'DB_NAME'
    ,@scheduling_option = 'Custom'
    ,@full_backup_freq_type = 'Daily'
    ,@backup_begin_time =  '00:30'
    ,@backup_duration = '02:00'
    ,@log_backup_freq = '00:05'
GO

And the log backup is running fine, so we know the credential is ok. But it will not make a backup of the database, since it is too large to fit, so it needs to be split up. But how to do that?
When I run a manual backup like this

Use MSDB
Go
EXEC managed_backup.sp_backup_on_demand
 @database_name = 'DB_NAME'
,@type = 'Database'

It does split it up into two pieces in the Azure container. So how do I get the automatic job to split up the BAK file.

We are running on SQL Server 2017.

Hope someone has a good idea, since Google doesn't give anything good.

Thanks

Reference:

Backing up a VLDB to Azure Blob Storage by Dimitri Furman
Understanding Block Blobs, Append Blobs, and Page Blobs
SQL Server Backup to URL

With your size you should be able to use page blog and specify MAXTRANSFERSIZE = 4194304 and BLOCKSIZE = 65536.
If you need to backup databases greater than 1TB use block blob and striped backup. To use block blobs, you need to create a Shared Access Signature (SAS), and create a credential for the SAS.  There are examples in the third reference above.

There are bunch of questions answered in the comment section of the first reference that will help in case if you run into any issues.


Optimize Postgresql query I'm struggling to get the below query to perform well. It's doing a sequential scan on the children table even though there's indexes on the join columns. Any ideas?

EXPLAIN ANALYZE SELECT "children".* from "children"
    INNER JOIN "parents" ON "parents"."id" = "children"."parent_id"
    WHERE "parents"."partner_pk" = 'partner'

Hash Join  (cost=55541.97..140831.44 rows=20804 width=168) (actual time=366.809..1851.311 rows=21215 loops=1)
  Hash Cond: (children.parent_id = parents.id)
  Buffers: shared hit=47476
  ->  Seq Scan on children  (cost=0.00..66250.30 rows=2152130 width=168) (actual time=0.007..1012.502 rows=2215906 loops=1)
        Buffers: shared hit=44729
  ->  Hash  (cost=55282.99..55282.99 rows=20718 width=4) (actual time=17.496..17.496 rows=21215 loops=1)
        Buckets: 4096  Batches: 1  Memory Usage: 746kB
        Buffers: shared hit=2747
        ->  Bitmap Heap Scan on parents  (cost=876.99..55282.99 rows=20718 width=4) (actual time=2.339..10.817 rows=21215 loops=1)
              Recheck Cond: ((partner_pk)::text = 'partner'::text)
              Heap Blocks: exact=2614
              Buffers: shared hit=2747
              ->  Bitmap Index Scan on index_parents_on_partner_pk  (cost=0.00..871.81 rows=20718 width=0) (actual time=1.992..1.992 rows=21215 loops=1)
                    Index Cond: ((partner_pk)::text = 'partner'::text)
                    Buffers: shared hit=133
Planning time: 0.280 ms
Execution time: 1855.458 ms

CREATE TABLE "public"."parents" (
    "id" int4 NOT NULL DEFAULT nextval('parents_id_seq'::regclass),
    "email" varchar NOT NULL,
    "partner_id" int4,
    "partner_pk" varchar
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."parents" OWNER TO "bark";
CREATE UNIQUE INDEX "index_parents_on_LOWER_email" ON "public"."parents" USING btree("lower(email::text)" ASC NULLS LAST);
CREATE INDEX "index_parents_on_partner_id" ON "public"."parents" USING btree(partner_id ASC NULLS LAST);
CREATE INDEX "index_parents_on_partner_pk" ON "public"."parents" USING btree(partner_pk ASC NULLS LAST);

CREATE TABLE "public"."children" (
    "id" int4 NOT NULL DEFAULT nextval('children_id_seq'::regclass),
    "first_name" varchar,
    "last_name" varchar,
    "parent_id" int4,
    "email" varchar,
)

CREATE INDEX "index_children_on_parent_id" ON "public"."children" USING btree(parent_id ASC NULLS LAST);

The obvious alternative to the seq scan on the child table would be the nested loop in which it has to probe the child table by index 20,718 separate times.  (Actually 21,215 times, but of course the planner doesn't know that at the time it makes its decision).  Each one of those probes is going to go to an assumedly random leaf page in the index, and then follow up to a random spot in the child table itself, triggering a bunch of random IO.  If all that of that data is already found in the cache, this will be a good plan.  But if it actually has to hit the disk for each one this is going to be ungodly slow (especially since your disk seems to be powered by gerbils--your pre-edit plan where it took 22 seconds to seq scan the child table is simply horrible).

If you want to see the alternative plan, you can set enable_hashjoin = off before executing the query.  This is generally not a setting you would want to do in production, it is more for exploratory purposes.  But as a last resort, you could make this setting change locally just for running this query, then reset it.

If you like the nested loop because most of your data actually is in memory most of the time, you can signal this to the planner by reducing the value of "random_page_cost" to something closer to 1.  In my hands, lowering from the default of 4 down to 1.4 was enough to flip the plan from the seq scan to the nested loop. If most of your data is always in cache (not just the child table and its index) then it might be reasonable to make a global change to this parameter.  If you do that, however, you should have a plan to pre-warm your database in case you reboot the machine (such as this).  Getting the data back into cache the natural way is likely to be slow and painful.


How to reduce sysaux tablespace size with Audit Trail Unified enabled I have Oracle 12c Standard instance with dev database with enabled Audit Trail Unified.

And now SYSAUX tablespace has size 32Gb, but all data is only 9Gb.
When I tried to reduce size of SYSAUX I received an error message:

ORA-03297: file contains used data beyond requested RESIZE value

Then I saw that some big sized objects in this tablespace:

I've executed the following scripts:

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
   last_archive_time => SYSDATE-1,
   rac_instance_number => 1 /* single instance database */),
   container            => DBMS_AUDIT_MGMT.container_current;
END;
/

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => TRUE);
END;
/

After this in AUDSYS."CLI_SWPXXXXXXX" table I don't see any rows.
But it still have the same size .

Also any attempts to shrink this table or move it to another tablespace give me an error message:
ORA-55941: DML and DDL operations are not allowed on table "AUDSYS"."CLI_SWPXXXXXXX"

So, my question is: can I make size of SYSAUX tablespace smaller or move this audit tables to another tablespace?

Update: by this link: click I've found info that "Moving CLI.SWP possible only for Enterprise edition, for standard impossible due partitioning." Is that true? It seems like my instance is Standard...

SQL> select owner||'."'||table_name||'"' t from dba_tables where table_name like 'CLI_SWP$%';

T
-----------------------------
AUDSYS."CLI_SWP$18c7c2a9$1$1"

SQL> truncate table AUDSYS."CLI_SWP$18c7c2a9$1$1";
truncate table AUDSYS."CLI_SWP$18c7c2a9$1$1"
*
FEHLER in Zeile 1:
ORA-55941: DML- und DDL-Vorgänge sind auf Tabelle
"AUDSYS"."CLI_SWP$18c7c2a9$1$1" nicht zulässig

SQL> shutdown immediate;
…
ORACLE-Instanz heruntergefahren.
SQL> startup upgrade;
…
Datenbank geöffnet.
SQL> truncate table AUDSYS."CLI_SWP$18c7c2a9$1$1";

Tabelle mit TRUNCATE geleert.

SQL> shutdown immediate;
SQL> startup;


Reduce backup creation time I will be performing a postgres upgrade from 11.5 to 12.5 that will affect production environment. The first step assumes, that the most recent backup of database was taken, before upgrading - we would like to reduce time of this stage, due to performing upgrade in night hours. After unsuccessful research I would like to as if taking a backup made few hours before exact upgrade backup will reduce the time of next backup procedure?

That depends on how you take the backup.
If you use pg_dump or pg_basebackup, the backup time is not influenced by how long ago the previous backup was taken.
If you backup with pg_basebackup or another online file system backup technique, you don't need a new backup. Just use the previous one and the WAL archives, and you can restore any point in time in the life of the database.


Update column based on other column with concat by underscore and manage duplicate value MySQL query for mysql 5.7.27 I need to update column value based on other column with adding separator and with manage duplicates values using sql query.

For example :

I have one database table A.

Here in it two columns : 1) locality 2) slug

Locality column already have data. So need to update 'slug' column based on locality value. Please review below screenshots.

Currently i have below table.

And i need to update 'slug' column like below screenshot.

I used below sql query but it will not worked on MYSQL5.7
So please help me to convert below query with MYSQL5.7

UPDATE tableA,
( SELECT suburb_id,
         ROW_NUMBER() OVER ( PARTITION BY LOWER(locality)
                             ORDER BY suburb_id ASC ) rn
         FROM tableA ) cte
SET tableA.slug = CASE WHEN cte.rn = 1
                       THEN replace(LOWER(tableA.locality), ' ', '_')
                       ELSE CONCAT( replace(LOWER(tableA.locality), ' ', '_'), '_', cte.rn-1 )
                       END
WHERE tableA.suburb_id = cte.suburb_id

I know that PARTITION BY and OVER() function not worked into MYSQL5.7
So, please me to create query for above.

Now i created below query.

UPDATE tableA,
(
SELECT
    @row_number:=CASE
        WHEN @customer_no = locality
          THEN
              @row_number + 1
          ELSE
               1
        END AS rn,
    @customer_no:=locality locality,
    id
FROM
    tableA,
    (SELECT @customer_no:=0,@row_number:=0) as t ORDER BY locality) cte
SET tableA.slug = CASE WHEN cte.rn = 1
                       THEN replace(LOWER(tableA.locality), ' ', '_')
                       ELSE CONCAT( replace(LOWER(tableA.locality), ' ', '_'), '_', cte.rn-1 )
                       END
WHERE tableA.suburb_id = cte.suburb_id

I used above query but not worked properly.
Inner SELECT query worked fine and get right row number for all locality with duplicate value.
But Update query not properly work.
I don't know how to manipulate with right connection for update and inner select query.
Please help me.

I need to output like below :

subrub_id |  locality | slug
-----------------------------
1         |  Post one  | post_one
2         |  Post TWO  | post_two
3         |  Post Three| post_three
4         |  Posts     | posts
5         |  Post One  | post_one_1
6         |  Posts     | posts_1
7         |  Post Four | post_four
8         |  Post Five | post_five
9         |  Post Ten  | post_ten
10        |  Posts     | posts_2
11        |  Post Two  | post_two
12        |  POSTS     | post_3

Solved this by me. See below query if help to others.

UPDATE tableA
JOIN
(
SELECT cte.subrub_id as main_id, cte.post_name, cte.rn, CASE WHEN cte.rn = 1
                       THEN replace(LOWER(tableA.locality), ' ', '-')
                       ELSE CONCAT( replace(LOWER(tableA.locality), ' ', '-'), '-', cte.rn-1 )
                       END AS a  FROM tableA,
(
SELECT
    @row_number:=CASE
        WHEN @customer_no = locality
          THEN
              @row_number + 1
          ELSE
               1
        END AS rn,
    @customer_no:=locality post_name,
    id
FROM
    tableA,
    (SELECT @customer_no:=0,@row_number:=0) as t ORDER BY locality ASC
        LIMIT 0, 10000 ) as cte
WHERE tableA.subrub_id = cte.subrub_id ORDER BY cte.post_name ASC
)
as t
ON tableA.subrub_id = t.main_id
SET tableA.slug =  t.a

akina: You need to emulate ROW_NUMBER(). There are 2 general ways:

Subquery which uses 2 table references (see, for example, Order by is ignored if inside a derived table of a update, with the correction that the subquery must be moved to main query's UPDATE clause); and
Subquery which uses user-defined variables (see, for example, query to replace text with sequential numbers).