MySQL Error 1062 after reboot node in multi master replication I setup 2 MySQL Community Server (8.0.20) with a replication master to master. Today one of the nodes went down, and when it went up, the replication failed. I tried to just ignore a registry with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;, but the errors keep coming in.
This is the status log of the particular node that failed:
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: database_1
                  Master_User: replicador
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000122
          Read_Master_Log_Pos: 217195469
               Relay_Log_File: 33afd376b907-relay-bin.000105
                Relay_Log_Pos: 2829
        Relay_Master_Log_File: mysql-bin.000122
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1062
                   Last_Error: Could not execute Write_rows event on table gc57125800.ate_logs; Duplicate entry '49328847' for key 'ate_logs.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000122, end_log_pos 188792316
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 188791943
              Relay_Log_Space: 28407134
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1062
               Last_SQL_Error: Could not execute Write_rows event on table gc57125800.ate_logs; Duplicate entry '49328847' for key 'ate_logs.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000122, end_log_pos 188792316
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: bef45e1b-99d6-11ea-a355-3e2547e4f083
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 201029 17:59:51
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

Not sure what I should do. Should I dump the another master and do it all over again? Do I need to do this every time a server fails?

You should use pt-table-checksum to see the differences between the source and the replica. If the differences are many, importing data again from the source could be the fastest solution. Otherwise, you could restart MySQL with --slave-skip-errors=1062, fix the errors with pt-table-sync, and then restart MySQL normally.
To understand how these tools work and how they are used, take a look at this article: MySQL replication primer with pt-table-checksum and pt-table-sync.
You may also want to take a look at another tool called twindb-table-compare, which is complementary to pt-table-checksum. It reads the diffs generated by pt-table-checksum on the source and on the replica, and prints them in a format that is easier to understand, similar to the output of Linux utility diff.

First stop traffic from restarted node and enable slave-skip-errors 1062 on same node.
Once in sync then you should use pt-table-checksum which will provide difference between tables.
Then you need to take action accordingly.


How to get the next value of sequence? Could somebody tell what is wrong with obvious query below:

db2 => select next value for schema_name.sequence_name;

As a result I'm getting:

SQL0104N  An unexpected token "END-OF-STATEMENT" was found following
"schema_name.sequence_name".  Expected tokens may include:  "<table_expr>".
SQLSTATE=42601

You need to select from something. Another option is:

db2 => values next value for schema_name.sequence_name;

As you noted you can also use SYSIBM.SYSDUMMY1 or create a virtual table:

db2 => select next value for schema_name.sequence_name from ( values 1 );

In this query:

select next value for schema_name.sequence_name;

next value is creating problem because we can not make the spaces while writing a query. So other thing you can do is found the max of value and increase it by 1.

Below find query for that:

select max(value)+1 for schema_name.sequence_name;

This would be helpful for you.


Automatically create ER diagram from database schema I have a database with 193 tables and I need to create an entity-relationship diagram from them. There are no formal relationships between the tables with respect to FKs. The relationships are mapped with 1-* tables.

The database in question is the MusicBrainz database found here.

SchemaSpy doesn't detect the relationships.

Other than myself creating it manually does anyone have any suggestions. Or is anyone familiar with this database that could offer some assistance?

The relationship between tables in any relational database is done using Foreign Keys.

From the Wikipedia definition:

  Foreign keys play an essential role in database design. One important part of database design is making sure that relationships between real-world entities are reflected in the database by references, using foreign keys to refer from one table to another.

Without FKs there will be no way for any schema modelling tool of knowing that two tables are related.

A solution might be for you to script out FKs by parsing the names of the tables (if the related tables have similar names), then names and data types of columns that define the relationship. But that's no trivial task.

xplore.io will generate a schema from your database with logical relations (not only FK constraints) you can also add manual relations as you wish.


Multiple SQL Server instance install question for AlwaysOn I have a 2 node WSFC cluster with SQL Server 2014 installed on each for AlwaysOn.

This is working fine. My question is can I install another instance of SQL Server 2014 on these nodes for another AlwaysOn configuration.

I have a requirement that Skype for Business (SFB) has to be on its own instance to be supported and can't be installed on the existing AlwaysOn already up and running for some other DBs.

Unless you can point to the direct requirement you are referring to, I do not see any requirement in Skype for Business (SFB?) that states it has to be on a dedicated instance.

I think what you may be referring to is the database needs to be in a dedicated Availability Group. If that is the case you can create another AG on the same instance and cluster to support your database for SFB. A given SQL Server instance can support multiple AGs and each one can have their own listener.

Yes, I believe you can have a separate AG on the same servers but you would need to use different named instances.


Compare possible null values in one query Question is pretty short and simple: I have following query:

SELECT *
FROM CTE

Now I want to get all rows with only maximum value of some field.

SELECT *
FROM CTE
WHERE NodeId = (SELECT MAX(NodeId) FROM CTE)

But maximum value can be null, so this query doesn't work, and I should write:

SELECT *
FROM CTE
WHERE NodeId = (SELECT MAX(NodeId) FROM CTE)
OR (SELECT MAX(NodeId) FROM CTE) is null

So I should write another OR do return true if current value is equal to maximum or maximum is null (we shouldn't check for NodeId is null because it defenitly is).

Is there any way to simplify this script?

Your 2nd query seems correct and does what you want.

Another option would be to use the TOP .. WITH TIES but this works only because NULL values are treated as the lowest in SQL Server.

SELECT TOP (1) WITH TIES t.*
FROM cte AS t
ORDER BY t.NodeId DESC ;

If you had MIN() instead of MAX() and you tried the TIES with ASC instead of DESC, the above would fail miserably.

Assuming your NodeId contains INT values, use ISNULL to replace all nulls with the least of int values. This way you'll include NULL values as your least probability to occur. Following is an example:

DECLARE @MinNodeId INT;
SET @MinNodeId = MIN(NodeId) - 1
FROM CTE;

SELECT *
FROM CTE
WHERE ISNULL(NodeId, @MinNodeId) = (SELECT MAX(ISNULL(NodeId, @MinNodeId)) FROM CTE)


Tyring to get a better understanding of ORACLE admin accounts In reviewing some configuration information for an ORACLE 12C server, I see that the only user assigned the DBA role appears to be SYS account. Also, the only user shown with SYSDBA and SYSOPER privileges appears to be SYS account. My understanding is that these are built in accounts that can perform all administrative functions. Absent other user accounts,  it seems to indicate that the SYS account is used to perform day to days admin functions. Shouldn't there be a "named" user with appropriately assigned privileges,versus using the SYS account?

Yes, there should be. Even the documentation mentions that:

Administrative User Accounts

  Create at least one additional administrative user and grant to that
  user an appropriate administrative role to use when performing daily
  administrative tasks. Do not use SYS and SYSTEM for these purposes.

But the database can not force you to do this. It is a discipline that some companies follow, while other companies ignore.

named OS users assigned to the ORA_DBA (windows) or DBA (*nix) groups are not accounts that are in the database and would not show up in any data dictionary view.  from a persistence basis, they are unknown to the database. They are authenticated when they connect, using 'os authentication'. If a given OS user is a member of the requisite and os-specific'dba' group, they can connect with any username/password.  As long as they specify 'as sysdba' their OS account will be checked against the members of the DBA group and they will be connected to the database as 'sys'.  Try it for yourself.

SQL> connect fubar/snafu as sysdba
SQL> show user


What steps are necessary to safely restart a machine hosting Microsoft SQL Server? Are there any special steps necessary to prevent data corruption when restarting a server hosting an MS SQL Server instance?

For example, I recently encountered the recommendation of stopping the SQL service manually.  My understanding is that this is handled by the Windows shutdown process.

I'm sure there are a zillion steps which individual people may recommend, like that I just mentioned, but I'd like to avoid repeating obsolete or superstitious practices.  Are there any recommendations from Microsoft, or widespread industry standards?

This question relates to the short-term procedure of rebooting a machine.  There's another question regarding the long-term procedure of ensuring that a machine is unused, before taking it down permanently.

You don't have to be fancy/worried or scared when you are restarting sql server.

Just make sure that you dont have any long running transactions. Best is to restart sql server using console or shutdown command during a low/minimum activity period also called maintenance window to minimize impact on your business.

If you have any DR setup and you dont want to be down, then best is to failover and then restart the passive or secondary node.

Clean Shutdown SQL Server occurs in below scenarios :

Stop sql server using Services console.
Shutting down your server
running SHUTDOWN command in SSMS

In above all situations, sql server cleanly shutsdown all its databases and then terminates the service which involves commiting or rolling back all the transactions, writing all dirty pages to disk and then writing an entry into transaction log.

Improper shutdown of sql server :

shutdown with nowait
pulling power cable from your server (if you have access).
killing sqlserver.exe from task manager
Dirve failure on which sql server binaries, exe, system databases reside or windows system drive failure .. usually C:\ drive.
overheating of the server causing it to shutdown (should rarely happen !!)

SQL Server will always try to do a clean shutdown ...unless you do something improper as stated above.

Some really good reading links on what happens behind the scenes during recovery phase :

Understanding Logging and Recovery in SQL Server
Understanding How Restore and Recovery of Backups Work in SQL Server
Simple overview of the SQL Server Recovery Process

The way I do it:
1) Disable all Jobs.
2) Make sure no jobs are currently executing.
3) Run SP_Who3 frequently to check activity, also run sp_whoisactive for more info.
4) If there's no activity and the only thing you see is your current sp_who3 query
5) Take DBs offline
6) Right click at the top of the database and click Stop
7) Verify if services are in a stopped state in services.msc
8) Done

PS. Should you have a PAGEIOLATCH / IOCOMPLETION or any other activity in SP_Who3 do not do the above as it could put your databases in recovery mode.


Looping through values stripping 1 character at a time I want to loop through values and strip 1 character at a time from the values and display the result.

So if I have a table with values:

ID
___
34679
13390
89906

I want the result to look like this

Id
----
4679
679
79
9
3390
390
90
0
9906
906
06
6

Please don't use loops for things like this (I'd also reserve recursive CTEs for scenarios where you have much less control over things, like hierarchies). Loops are bad in SQL; SQL is optimized to work in sets.

DECLARE @foo TABLE(ID INT);

INSERT @foo VALUES(34679),(13390),(89906);

;WITH x AS
(
  SELECT TOP (2048) n = ROW_NUMBER() OVER (ORDER BY Number)
  FROM master.dbo.spt_values ORDER BY Number
)
SELECT RIGHT(f.ID, x.n) FROM x
INNER JOIN @foo AS f
ON x.n < LEN(f.ID);

Results:

9
79
679
4679
0
90
390
3390
6
06
906
9906

http://sqlperformance.com/generate-a-set-1
http://sqlperformance.com/generate-a-set-2
http://sqlperformance.com/generate-a-set-3

CREATE PROC udploop (@num varchar(10))
AS
       BEGIN
             DECLARE @len int;
             SET @len = LEN(@num);
             WHILE (@len > 1)
                   BEGIN
                         SELECT
                            @num = RIGHT(@num, @len - 1);
                         PRINT @num;
                         SET @len = LEN(@num);
                   END
       END

EXEC:

EXEC udploop 34679
EXEC udploop 13390
EXEC udploop 89906

RESULT:

4679
679
79
9
3390
390
90
0
9906
906
06
6


logins without any users There are several logins in my SQL Server that don't have user mappings to any database; they are simply dummy logins.

Someone created the login but it does not have any associated users.

I need to a script to list out those logins.

Thanks in advance.

The query below is a good starting point.  It returns a list of logins that have no matching user in any attached database.  Be aware there are many reasons a login might exist at the server level, but not have an explicitly created user in any user database.  Before removing the logins shown by this code, you should validate that they are not needed.  As an example, this code may list the service accounts used to run SQL Server services; removing them might be a bad idea.

It filters out server roles, since they cannot be added to databases as users.  Similarly for certificate-mapped logins, since they can only be used for code signing.

DECLARE @cmd nvarchar(max);
SET @cmd = N'';
SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N'
UNION ALL
' END + N'SELECT dp.sid
FROM ' + QUOTENAME(d.name) + N'.sys.database_principals dp
WHERE dp.sid IS NOT NULL
'
FROM sys.databases d
WHERE d.state_desc = N'ONLINE';

PRINT @cmd;

DROP TABLE IF EXISTS #database_sids;
CREATE TABLE #database_sids
(
    [sid] int NOT NULL
);
INSERT INTO #database_sids ([sid])
EXEC sys.sp_executesql @cmd;

SELECT sp.name
    , sp.type_desc
FROM sys.server_principals sp
WHERE NOT EXISTS (
    SELECT 1
    FROM #database_sids ds
    WHERE ds.sid = sp.sid
    )
    AND sp.[type_desc] NOT IN (
          N'SERVER_ROLE'
        , N'CERTIFICATE_MAPPED_LOGIN'
        )
ORDER BY sp.name;

The query gathers the list of security identifiers from each database in the SQL Server instance, then returns logins that don't appear in that list of security identifiers.

There are a couple of "gotchas" with this approach.  If any databases are offline when the code runs, users in the offline database(s) will not be excluded from the output, resulting in possible false-positives.

Since the script uses the security identifier, instead of the name of the user, it may return false positives for users and logins with the same name, but different SIDs.  This can easily occur If you restore a database from a different source SQL Server and run this code against it without "fixing" the orphaned users first1.

1 - I am associated with the site SQLServerScience.com and wrote this blog post

Following query first collects all users from databases (including system databases), then validate with all available logins in sever. Result shows list of user created logins that are not mapped any database in server:

Beware that the sp_MSforeachdb is undocumented procedure, you would probably want use custom stored procedure that can make script to run across the databases:

Declare @Temp Table (sid varbinary(85), name nvarchar (100) );

insert into @Temp (sid, name)

exec sp_MSforeachdb
'Use [?];
select sid, name
from sys.database_principals as dp
where type in (''S'', ''U'', ''G'') and sid is not null and (dp.name not in (''dbo'', ''guest'') and dp.name not like ''##%'' ) '

select
        sp.name,
        sp.sid,
        sp.type_desc,
        'drop Login [' + sp.name + ']' as FixCommand
from sys.server_principals as sp
    left outer join @Temp as dp on sp.sid = dp.sid
Where sp.type in ('S', 'U', 'G') and ( not sp.name = 'sa' and sp.name not like '##%' and sp.name not like 'NT %')and dp.name is null
GO

Following query would help you to list-out Orphaned Users from all user databases along with fix command (last column)

exec sp_MSforeachdb
'Use [?];
select  DB_NAME () as DBName, dp.name,
        dp.principal_id,
        dp.type,
        dp.type_desc,
        dp.sid,
        sp.sid,
        ''use '' + cast(DB_NAME () as varchar(50)) + ''; if not exists (select containment from sys.databases where not containment = 0) drop user ['' + dp.name + '']'' as FixCommand
from sys.database_principals as dp
        left outer join sys.server_principals as sp on dp.sid = sp.sid
where  dp.principal_id > 4 AND sp.sid is null and dp.type in (''S'', ''U'', ''G'')'
go

For the particular database scope, usually the Orphaned Users may occurred in user databases while restoring from a backup of different server, therefore system databases are not in our scope (as per following script):

select  DB_NAME () as DBName, dp.name,
        dp.principal_id,
        dp.type,
        dp.type_desc,
        dp.sid,
        sp.sid,
        'drop user [' + dp.name + ']' as FixCommand
from sys.database_principals as dp
        left outer join sys.server_principals as sp on dp.sid = sp.sid
where  dp.principal_id > 4 AND sp.sid is null and dp.type in ('S', 'U', 'G')


Repeated values in group_concat I have two tables, first the table food and Second is Activity:

INSERT INTO food
    (`id`, `foodName`)
VALUES
    (1, 'food1'),
    (2, 'food2'),
    (3, 'food3'),
    (4, 'food4'),
    (5, 'food5'),
    (6, 'food6'),
;
CREATE TABLE Activity
    (`id` int,`place` varchar(14),`food_id` int,`timing` TIME,`date_and_time` DATETIME)
;
INSERT INTO Activity
    (`id`,`place`, `food_id`,`timing`,`date_and_time`)
VALUES
    (1, 'place1', 1, '10:30am','2013-05-01'),
    (2, 'place1', 1, '12:30pm','2013-05-01'),
    (3, 'place1', 1, '04:30pm','2013-05-01'),
    (4, 'place2', 2, '10:30am','2013-05-02'),
    (5, 'place2', 2, '12:30pm','2013-05-02'),
    (6, 'place2', 2, '4:30pm','2013-05-02'),
    (7, 'place1', 2, '10:30am','2013-05-02'),
    (8, 'place1', 2, '12:30pm','2013-05-02'),
    (9, 'place1', 2, '4:30pm','2013-05-02'),
    (10, 'place2', 3, '10:30am','2013-05-03'),
    (11, 'place2', 3, '12:30pm','2013-05-03'),
    (12, 'place2', 3, '4:30pm','2013-05-03')
;

For now I'm using the following query:

SELECT
    a.activity_type AS Activity,
    COUNT(DISTINCT p.id) AS Products,
    CONVERT(GROUP_CONCAT(p.category_id SEPARATOR ',  ') USING utf8)
      AS Categories
FROM
    food AS p
  JOIN
    ( SELECT activity_type
           , prod_id
      FROM activity
      WHERE activity_type <> ''
      GROUP BY activity_type
             , prod_id
    ) AS a
    ON p.id = a.prod_id
GROUP BY
    activity_type
ORDER BY
    Products DESC ;

Could you please help me, I need output in the below format:

place | food_id | Timings             |                              |
      |         |---------------------|Date                          |
      |         |Time1 |Time2 | Time3 |                              |
---------------+----------+------------------------------------------|
place1 | 1      | 10:30am| 12:30pm| 4:30pm |2013-05-01(MAX timestamp)|
place2 | 1      | 10:30am| 12:30am| 4:30am |2013-05-01(MAX timestamp)|

If your date_and_time values are always the same, use this query:

select place, food_id, '10:30am' as Time1, '12:30pm' as Time2, '4:30pm' as Time3, DATE_FORMAT(MAX(date_and_time), '%Y-%m-%d') as Date from Activity group by food_id, place;

I think, it's better to use PIVOT. the below link shows how it works :
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx


How to uninstall SQL server 2012 not showing in control panel's "Programs and features" I am frustrated to the core. While fixing my Sql Server I've  made the situation worse.
Here is the problem: initially my master database was corrupted, so the MSSql service was not starting.

I tried to fix it following some instructions from the MSDN site, but I just could not, so I decided to uninstall Sql Server through Control Panel.
Uninstalled every bit of sql from Control Panel, but the Sql Server still exists in my system.

Then I read somewhere to delete the Sql Server registry keys, I did the same. And now I cannot uninstall or even reinstall Sql Server on my machine.

I don't want to reinstall my Windows, can anyone help in here with a sure shot method of how to uninstall Sql Server and then reinstall it. Note that I have tried using the installation media also, but just couldn't install.

My guess is since you have remamants of failed installation still present in your system that is why you cannot install the new instance. This can be fixed by deleting few registry keys related to SQL server. Follow below method , this method was given by Microsoft forum team and is recommended way to remove have helped me and forum users many time . If you have multiple instances below suggesstion  would not work so dont use it.

First please refer to below Microsoft support link for what actions to follow before removing SQL Server instance. Its is for 2008 r2 but would be applicable to 2012 as well.

http://support.microsoft.com/kb/955499/en-gb

If still you face problem you might as well try below suggesstion which involves removing registry entries.

Note: Removing necessary registry entries might cause inconsistency so backup registry before doing changes.

•1. Uninstall the existing SQL Server  and all the components from the add remove program.

•2. Backup the registry  . Please read in details the MS link before proceeding.

•3. Delete the following keys using regedit:

◦--HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server

◦--HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer

•4. Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall and delete all the sub-keys referencing SQL Server.

•5. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services and delete all the keys referencing SQL Server.

•6. Rename all the SQL Server folders in the computer.

•7. Reboot the machine.

If you dont find this useful or have confusion dont proceed. Open a case with Microsoft and allow them to have a look at your system. with my experience with many failed installation registry keys are left in corrupted state and only solution would be to remove them.But you need expert to fiddle with registry

PS: Above method was suggested in SQL server forum and works well.

Of course if you can please revert it would be helpful

just try to delete each and every files related to sql server 12 from:-

1. control panel
2. C:\Program Files (x86)\Microsoft SQL Server
3. From all registries also(open by using "win+r" then type "regedit"

then just restart your system and check is it existing again or not?


GHOST_CLEANUP not freeing up space on SQL Server Availability Group Had a problem in my SQL Server Cluster for 2 weeks, I had the GHOST_CLEANUP process running 24/7 never stopping even if the users were doing nothing.

I was checking the "Free Space" parameter in the Database and always was at the same value (Around 1GB).

I did a backup of the database and restored in the same server with the same name to see if running sp_clean_db_free_space would free up the space that GHOST_CLEANUP was not able to. After 2 hours...TADA!...the space was cleaned and I had 80GB free from 100GB Database.

So I decided to run sp_clean_db_free_space stored procedure on the original database to see what happens. After almost 30m it finished not freeing any space. The only different thing was that the original database was synched under an Availability Group. I disconnected the database from the Availability Group and now I can see that the GHOST_CLEANUP task is slowly freeing space.

I will assume that the problem was the Availability Group, but does anyone know WHY!?

I can confirm no blocking on primary by read operations on secondary because the secondary node is not even used for read only operations as the application doesn't allow to configure this. There are no users connected to the secondary node.

Today, after checking I found that each 5 seconds SQL Server jumped from 0-1% CPU to 10-15% CPU then dropped again to 0-1% CPU after 1 second. No I/0 usage, only CPU. Decided to disconnect all my Availability Replicas, after 1 minute, SQL Server dropped to a stable 0-1% CPU, connected the replicas again and remains at 0-1%. I have a problem with this GHOST_CLEANUP and I don't know how to solve it.

I am using Microsoft SQL Server 2012 (SP3-CU1) (KB3123299) - 11.0.6518.0 (X64).

There is a connect item. It is fixed in SP4.


Correct way to stop SQL Server (through automation) Question

Is it safe to start & stop SQL's services using PowerShell's Start-Service and Stop-Service commands?
If not, is there a recommended way to start & stop SQL's services through automation?

Additional Info

We're in the process of creating scripts to bring down our systems cleanly before maintenance windows, then to bring them back up afterwards.
By cleanly I mean it stops the application's services, then stops the database services (i.e. in dependency order), setting all to DISABLED, so that the infrastructure team can apply windows updates / perform any work, safely rebooting servers as required, before running the scripts to bring the system back up in the correct (dependency aware) order.

Our DBA mentioned that the correct way to stop services is through SQL's Configuration Manager; implying that that's different to just using services.msc (or for automation, powershell's stop-service / command line's sc \\someServer stop someService.

The Note at the top of this article implies something similar: https://technet.microsoft.com/en-us/library/ms175516(v=sql.105).aspx
This article points out that SQL Server Configuration Manager has some advantages for some functions: https://msdn.microsoft.com/en-us/library/ms174212.aspx

However I've not been able to find anything explicitly saying it's incorrect to start/stop services through the regular means, or what extra protection/functionality SQL Configuration Manager gives with regards to simply starting & stopping services.

You are safe either which way you go. SERVICES.MSC and POWERSHELL command lines are EQUAL functions to MSSQL Configuration manager...

Configuration manager is also used to control instances, etc etc but the function of starting and stopping a service is the same no matter which method you choose.

Some people have their own ways of doing things, for example to quit an application, you can do File > Exit. Or you can do ALT+F4, they are both the same function...

Please view this link below for additional information and helpful details. This covers all of the different methods (SQL configuration mgr, SSMS, Commandline etc)

https://msdn.microsoft.com/en-us/library/hh403394.aspx

UPDATE regarding Failover Cluster Instance...

If the instance is clustered, use of any of the above suggestions does not apply and should not be used. For a FCI you need to utilize Cluster Manager or the FailoverClusters PowerShell module commands.

From Microsoft SQL server Configuration Tools and right click sql server services and stop.

OR
Using Transact-SQL

SHUTDOWN;

To stop the Database Engine immediately, execute the following statement. SHUTDOWN WITH NOWAIT;

See the link Start, Stop, Pause, Resume, Restart the Database Engine


MySQL: How to avoid case sensitivity in SQL queries when migrate from Windows to Linux? I have migrated my Database from Windows to Linux server. In many places, query has been written without focusing their cases (uppercase / lowercase). So, these queries are not getting executed and generating errors. As a result, functionalities based on these queries are breaking. Below is the example query to run in Windows and Linux:

Select A.id,A.title from tablename a;

This query will work in Windows while never work in Linux.

Is there any way through which I can avoid these case sensitivity in MySQL
queries and no need to check and fix every query ?

In MySQL the commands themselves are not case sensitive, however depending on the OS, file structure variables might be case sensitive.

Check if your OS is case sensitive, and if this is the case, find out the correct case for the tables, and make sure your query is consistent in naming.
Don't forget though that you'll have to rewrite a lot of the queries to make sure they work in MySQL.

References:

MySQL docs: Identifier Case Sensitivity

@ursitesion We also had a similar problem after we migrated our MySQL DB from Windows to Linux. We were lucky that our table names were already in small caps, the only issue we faced was that inside our application & some Stored Procedures (SP) we had mentioned the table names in Large Caps / Camel Case.

We followed the following steps and were able to solve our problem:

1) Edit the MySQL configuration file (i.e. /etc/my.cnf), and add the following line in [mysqld] heading.

lower_case_table_names=1

Note: By running the 'mysqld' with lower_case_table_names=1, the database creates the tables in all lower-case names. The existing table names are unaffected.

2) Restart the database so that all database table names are now created in lower-case.

3) Verify that the change we made above did take effect in the database using this query

SHOW VARIABLES LIKE '%lower%';

The instructions are also mentioned  here .

Now all our tables are in small caps (which was the case even before we made the change) & queries having table / SP names in large Cap work fine.

Just in case you have your table names also in CAPS this link might be of help (i have not tried it)


Oracle user locks (dbms_lock): guaranteed order of requests granted? I was wondering if Oracle guarantees that the sequence of dbms_lock.request granted is the same as the sequence in which they were requested.
In my tests it always was the case, but I couldn't find any official statement in the Oracle documentation.

So let's assume we have at least three sessions:

Session 1 requests the lock via dbms_lock.request(1) and is granted the lock immediately (as the lock is not yet held by any other session)
Session 2 requests the lock via dbms_lock.request(1) and has to wait
Session 3 requests the lock via dbms_lock.request(1) and also has to wait
Now Session 1 releases the lock via dbms_lock.release(1) (or trx end or session end)

Is it guaranteed that Session 2 will now receive the lock and Session 3 still has to wait until Session 2 releases? Or might Session 3 receive the lock before Session 2?

Edit: if the answer is "yes, guaranteed", please provide link to Oracle documentation, whitepaper, V$ view or the like.

Edit 2: Database version in question is 11g and up

Yes!

There is a wait queue and the first requester is the first served.
Beware of deadlocks...


T-SQL command to log in as a different user? Is there a T-sql command, like the Oracle "Connect" which lets me change the user that I am logged in as?

You can use the EXECUTE AS command:

http://msdn.microsoft.com/en-us/library/ms181362.aspx

The question has multiple answers, dependent on the context of 'changing the user that I am logged in as.'

A public website will have one or a few 'user accounts' which are used by every site visitor. Even through those accounts, it may be necessary to elevate a permission to execute, for instance, dynamic SQL in solving a multivariable search requirement.

Errand Sommarskog has a very detailed discussion on the security and management of user accounts via T-SQL in stored procedures.
http://www.sommarskog.se/grantperm.html


Schema design for user activity logging I'm designing a schema to support logging of user activity, where users must be able to search:

Across all events (events of any type), with datetime range, by username;
Across events of one type, with same as above and additionally with parameters of that module.

I created this schema:

And designed queries to search:

across all events:

SELECT extract(epoch from log_time) * 1000,
  u.username,
  CASE WHEN l.event_type IN (0, 2) THEN e.template
    WHEN l.event_type = 1 THEN format(e.template, ecs.field)
    WHEN l.event_type = 3 THEN format(e.template, ess.field)
    WHEN l.event_type = 4 THEN format(e.template, ect.ip, ect.port)
  END
FROM logs l
JOIN users u ON u.id = l.user_id
JOIN event_def e ON e.id = l.event_type
LEFT JOIN event_client_search ecs ON ecs.log_id = l.id
LEFT JOIN event_switch_search ess ON ess.log_id = l.id
LEFT JOIN event_cable_test ect ON ect.log_id = l.id

across same-type events:

SELECT extract(epoch from log_time) * 1000,
    u.username,
    format(e.template, ect.ip, ect.port)
FROM logs l
JOIN users u ON u.id = l.user_id
JOIN event_def e ON e.id = l.event_type
JOIN event_cable_test ect ON ect.log_id = l.id

This is what the event_def table looks like:

 id |                template
----+----------------------------------------
  0 | Logged in
  2 | Logged out
  1 | Searched in clients %s
  3 | Searched in switches %s
  4 | Cable test switch %s port %s

But what I didn't like, when I have about 100 events (now I have about 50, but haven't implemented them yet), it is going to be a performance issue.

So I thought to merge events with same parameters, like this:

I don't think that will help a lot (it'll cut the number of events by half at most), maybe I'm thinking in the wrong direction?

One of the assumptions that I would ask you to reconsider is whether you really want to have different tables for each event type, or if rather these event types are rows in a table instead of distinct tables.

If you had an EVENT_TYPE table containing your list of event types (about 100 of these) you could take the columns from your current various event tables and make them rows in an EVENT_PARAMETER table.

This type of design would turn your schema changes into data changes when you add new event types.  That would save you code maintenance issues and also avoid making your queries more complex and potentially slower.

The instances of events would similarly be kept in two tables, for example a LOG table and a LOG_DETAIL table.

LOG would be an intersection between user and event type.

LOG_DETAIL would be an intersection between log and event parameter.

Your data model might look something like this:

Note that one objection some people may have to this approach is that you end up saving the log parameter values in a string format, rather than in a native format.  This is clearly a trade-off.  You have to ask yourself is it a good trade-off for your situation.

Can you explore a graph database for it?
Every user is an entity whose activity is a series of actions [connected node], can even maintain backtracking.
Even you can maintain the chronology easily.
Good read:
https://snowplowanalytics.com/blog/2014/07/31/using-graph-databases-to-perform-pathing-analysis-initial-experimentation-with-neo4j/


How to group by week even when the count is 0 My below example works fine, the only challenge i am facing is that weeks with 0 results do not show. Here is a sample of my code:

My current output:

week      | amount
4        |  354
6        |  222
7        |  144
8        |  354
9        |  45
10       |  55
11       |  76
12       |  98
13       |  45
14       |  344

The result above is missing many weeks (1,2,3 and 15,16,17 etc.) How do i get to show those with 0 count?

TEST EXAMPLE:

CREATE TABLE TABLE1 ( DATE datetime, COMPANY_ID varchar);
INSERT INTO TABLE1(DATE, COMPANY_ID)
VALUES
('2019-01-24 00:00:00.000','Arla'),
('2019-01-24 00:00:00.000', 'B/S'),
('2019-01-24 00:00:00.000', 'NAaN'),
('2019-02-20 00:00:00.000', 'SuperBest'),
('2019-02-20 00:00:00.000', 'GS'),
('2019-04-29 00:00:00.000', 'Aldi'),
('2019-04-29 00:00:00.000', 'Netto'),
('2018-01-24 00:00:00.000', 'Arla');

;with ResultsTable as
(

SELECT
DATENAME (WK, DATE) AS WEEK,
COUNT (DISTINCT COMPANY_ID) AS AMOUNT
FROM
(
SELECT COMPANY_ID, DATE = MIN(DATE)
FROM TABLE1 A

WHERE YEAR(A.DATE) = '2019' AND

COMPANY_ID NOT IN(SELECT COMPANY_ID  FROM TABLE1 WHERE DATE < '2019-01-01')
GROUP BY COMPANY_ID) d

GROUP BY dateadd(wk, datediff(wk, 0, DATE), 0),  DATENAME(WK, DATE)

)
,WeekNumbers (WeekNumber)
AS (
SELECT 1 AS Number

UNION ALL

SELECT WeekNumber + 1
FROM WeekNumbers
WHERE WeekNumber < (
        SELECT datename(week, '2019-12-31')
        )
)
--join cte to original results
--when no match on week number, set amount to 0
SELECT wn.WeekNumber AS [week]
,COALESCE(rt.[week], 0) AS amount
FROM WeekNumbers wn
LEFT JOIN ResultsTable rt ON rt.[week] = wn.WeekNumber

Could the solution be as simple as using a recursive CTE to generate a numbers table of the weeks in the year and then left joining to your main query?  Using coalesce to output either the real amount or zeros when no match on week number is found?

--demo setup
Declare @ResultsTable table (
  [week] INTEGER,
  amount INTEGER
);

INSERT INTO @ResultsTable
  ([week], amount)
VALUES
  ('4', '354'),
  ('6', '222'),
  ('7', '144'),
  ('8', '354'),
  ('9', '45'),
  ('10', '55'),
  ('11', '76'),
  ('12', '98'),
  ('13', '45'),
  ('14', '344');

--recursive cte to generates week numbers
;WITH WeekNumbers (WeekNumber)
AS (
    SELECT 1 AS Number

    UNION ALL

    SELECT WeekNumber + 1
    FROM WeekNumbers
    WHERE WeekNumber < (
            SELECT datename(week, '2019-12-31')
            )
    )
--join cte to original results
--when no match on week number, set amount to 0
SELECT wn.WeekNumber AS [week]
    ,COALESCE(rt.amount, 0) AS amount
FROM WeekNumbers wn
LEFT JOIN @ResultsTable rt
    ON rt.[week] = wn.WeekNumber

| week | amount |
|------|--------|
| 1    | 0      |
| 2    | 0      |
| 3    | 0      |
| 4    | 354    |
| 5    | 0      |
| 6    | 222    |
| 7    | 144    |
| 8    | 354    |
| 9    | 45     |
| 10   | 55     |
| 11   | 76     |
| 12   | 98     |
| 13   | 45     |
| 14   | 344    |
| 15   | 0      |
| 16   | 0      |
| 17   | 0      |
| 18   | 0      |
| 19   | 0      |
| 20   | 0      |
| 21   | 0      |
| 22   | 0      |
| 23   | 0      |
| 24   | 0      |
| 25   | 0      |
| 26   | 0      |
| 27   | 0      |
| 28   | 0      |
| 29   | 0      |
| 30   | 0      |
| 31   | 0      |
| 32   | 0      |
| 33   | 0      |
| 34   | 0      |
| 35   | 0      |
| 36   | 0      |
| 37   | 0      |
| 38   | 0      |
| 39   | 0      |
| 40   | 0      |
| 41   | 0      |
| 42   | 0      |
| 43   | 0      |
| 44   | 0      |
| 45   | 0      |
| 46   | 0      |
| 47   | 0      |
| 48   | 0      |
| 49   | 0      |
| 50   | 0      |
| 51   | 0      |
| 52   | 0      |
| 53   | 0      |

So, using your original query incorporated into a Common Table Expression, the final solution might look something like this:

;with ResultsTable as
(
SELECT
DATENAME (WK, DATE) AS WEEK,
COUNT (DISTINCT COMPANY_ID) AS AMOUNT
FROM
(
SELECT COMPANY, DATE = MIN(DATE)
FROM TABLE1 A INNER JOIN TABLE2 B
ON A.ID = B.ID
WHERE YEAR(A.DATE) = '2019' AND COMPANY_ID NOT IN(SELECT COMPANY_ID  FROM
TABLE1 A INNER JOIN TABLE2 B  ON A.ID = B.ID AND DATE < '2019-01-01') GROUP
BY COMPANY_ID) d

GROUP BY dateadd(wk, datediff(wk, 0, DATE), 0),  DATENAME(WK, DATE)
)
,WeekNumbers (WeekNumber)
AS (
    SELECT 1 AS Number

    UNION ALL

    SELECT WeekNumber + 1
    FROM WeekNumbers
    WHERE WeekNumber < (
            SELECT datename(week, '2019-12-31')
            )
    )
--join cte to original results
--when no match on week number, set amount to 0
SELECT wn.WeekNumber AS [week]
    ,COALESCE(rt.amount, 0) AS amount
FROM WeekNumbers wn
LEFT JOIN ResultsTable rt
    ON rt.[week] = wn.WeekNumber

You need to change JOIN to LEFT JOIN appropriately.
Your code is not clean and clear, so just give an idea.


Avoiding a divide by zero error in a computed column How do you avoid divide by zero error in the below table?

CREATE TABLE [dbo].[TblDivision]
(
    [Numerator] int NOT NULL,
    [Denominator] int NOT NULL,
    [Result] AS (Numerator/ Denominator)
)
GO
Insert into (Numerator, Denominator) TblDivision values (3,0)
GO

Just add a special case for division by 0:

CREATE TABLE [dbo].[TblDivision]
  (
  [Numerator] int NOT NULL,
  [Denominator] int NOT NULL,
  [Result] AS case when Denominator=0 then 0 else (Numerator/ Denominator) end
  );

case
 when Denominator=0 then 0
 else Numerator/ Denominator
end as [Result]


Is a process created in Oracle for every SQL statement? After reading the Oracle docs, it´s still not clear for me if an Oracle process is created for every SQL statement or just for every connection (or not really related to both).

Any comment here would be really great.

Thanks.

A process is created per connection, which can then execute SQL statements for the duration of the connection.

A SQL statement may use more than one Oracle background process (for things like Parallel Query) during its execution, but these aren't forked specifically for the SQL statement.

Note that I'm ignoring things like connection pooling (DRCP) and shared servers (MTS) to keep the answer simple.

Whenever a SQL statement is fired from oracle, it just creates a psedocode of that
SQL Statement and stores it in the Library cache (Shared SQL Area).

This is stored basically for fast retrieval and reuse the SQL statements present in Cache.
So, there would not be any process created when a SQL statement is fired.
But as Phil said,

  A SQL statement may use more than one Oracle background process (for things like Parallel Query) .

SQL statement uses the background process but doesn't create it.

This link might be useful for you.
Kindly Check
 this.


Working with JSON data MySQL I am finding that documentation for working with MySQL JSON data is still at this point apparently at it's infancy. Either that or I'm doing it wrong.
I found several docs online, i.e. https://benjaminlistwon.com/blog/working-with-json-data-in-mysql-part-1-of-3/ and others - but neither are working for me.

I recently switched from storing the station data in separate tables per station (4,100+ tables I had to do a "dynamic" PHP query on to get the data - wow!) - to JSON for speed and performance reasons, and an added benifit of not doing 4200 table writes per hour.

To get the aggregate data it's much faster - for a single row of data I can just iterate through the JSON keys. Problem is that to pull data from 83+ rows where Key=Value, PHP says out of memory error. My guess is that means I need to match the JSON value in the query instead of in PHP.

Below: SQL dump of a JSON based data table, StationData. Weather data from various stations collected hourly.

https://drive.google.com/open?id=0B6ScTEIoK3tPbGhrZkJOZmhRUTQ

Problem I'm having is features like JSON_EXTRACT, JSON_SEARCH, and JSON_etc are just returning NULL.

For example, for Olathe Kansas, I want every data stored under {"KOJC":{"Temperature":"25", "Dewpoint":"23"}} etc etc - but nothing from any other keys. The key is KOJC.
(for giggles tried the MySQL doc example):

select JSON_KEYS('{"a": 1, "b": {"c": 30}}');
+---------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
+---------------------------------------+
| ["a", "b"]                            |
+---------------------------------------+
1 row in set (0.02 sec)

select JSON_KEYS(jsonData) from StationData;
+---------------------+
| JSON_KEYS(jsonData) |
+---------------------+
| NULL                |

etc... 85 rows blah blah

select JSON_EXTRACT(jsonData, "$.KOJC") from StationData;

+----------------------------------+
| JSON_EXTRACT(jsonData, "$.KOJC") |
+----------------------------------+
| NULL                             |
| NULL                             |

etc....
    85 rows in set (0.16 sec)

select jsonData->>'$.KOJC' as Station from StationData;
+---------+
| Station |
+---------+
| NULL    |

etc....
    85 rows in set (0.14 sec)

select * from StationData were JSON_SEARCH(jsonData, 'all', '$.KOJC');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JSON_SEARCH(jsonData, 'all', '$.KOJC')' at line 1

select * from StationData where JSON_EXTRACT(jsonData, "$.KOJC");
Empty set (0.15 sec)

UPDATE - I found what's happening. JSON_EXTRACT works for a non-arrayed JSON string but when working with an array I am unable to pull out array elements.

select JSON_EXTRACT('{"KOJC":{"Tmp":"25"}}', '$.KOJC');                          +-------------------------------------------------+
| JSON_EXTRACT('{"KOJC":{"Tmp":"25"}}', '$.KOJC') |
+-------------------------------------------------+
| {"Tmp": "25"}                                   |
+-------------------------------------------------+
1 row in set (0.01 sec)

^ that works
But the below does not when presented with a array:

select JSON_EXTRACT('[{"KOJC":{"Tmp":"25"}}, {"KTWX":{"Tmp":"32"}}]', '$.KOJC');
 +--------------------------------------------------------------------------+
| JSON_EXTRACT('[{"KOJC":{"Tmp":"25"}}, {"KTWX":{"Tmp":"32"}}]', '$.KOJC') |
+--------------------------------------------------------------------------+
| NULL                                                                     |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

select JSON_EXTRACT('[{"KOJC":{"Tmp":"25"}}, {"KTWX":{"Tmp":"32"}}]', '$[KOJC][*]');
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 2.

select JSON_EXTRACT('[{"KOJC":{"Tmp":"25"}}, {"KTWX":{"Tmp":"32"}}]', '$[$.KOJC][*]');
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 2.

Do not have 4100 identical tables.  There are so many issues with such, and it is repeatedly panned in this and other forums.

4100 "stations", each with, say 10 readings, per hour?  41000 is about 12/second (average), which is not very much if you had one row per datapoint per station.  One table with 3 columns: hour (or datetime), station_id, reading.  And batch the 10 rows in a single INSERT statement for a little more efficiency.

How you can fetch the data in a variety of ways -- "just temperatures at 6am on weekends" becomes a simple, and somewhat efficient query.

Alternatively, have one table with 12 columns:  hour/datetime, station_id, and 10 specific readings.  This is likely to be more efficient in all ways.  This also has the advantage that the readings can have specific datatypes for each measurement.  Temperature or humidity:  TINYINT SIGNED is 1 byte and allows -128..127 or DECIMAL(5.2) (3 bytes) for +/-999.99 instead of a generic FLOAT (4 bytes).

The alternatives probably take a similar amount of disk space.  This database will be rather big after a year; you should probably estimate the future size and think ahead about indexing, and other issues.

So I found the root cause of my problems.

I was adding unneeded complexity to my JSON array from generation.

I was storing in a [{"key":{"subkey":"val","subkey":"val"}}, {"key":{"subkey":"val","subkey":"val"}}]

I was having to find the data in MySQL using json_extract(field, '$**.key') - which was taking roughly 30 seconds to scan the data on 1 single row.

I took the [ ] master array out, now I have the data in a {"key":{"subkey":"val","subkey":"val"}}, {"key":{"subkey":"val","subkey":"val"}} array structure - I can access the data using '$.key' instead of '$**.key' - and it's instant.

Guessing this is a bug or something really screwed up in the way MySQL searches for a key - but this fixed it.


Unable to find how a table is being populated Using SSMS2014 w/ SQL Server 2008 R2.
I have a table that I am trying to find out how and from where data inside it is coming from. (I didn't create the table and am unable to contact the person who did)
I've tried the following:

RMB on the table -> View Dependencies.
Result: This comes back empty.
I've tried running the following code:

select object_name(object_id) as DatabaseName, last_user_update, *
from sys.dm_db_index_usage_stats
where database_id = DB_ID('PackardIntranet')
and object_id=object_id('tbl_Johnstone_Packard_SalesReport')

Result: This just shows NULLs and 0's except when I queried the table previously
I've tried using RedGate's SQL Search using the table name or a column name as a parameter.
Result: The only result is the table itself

Am I missing something or can it be that this table was just created and populated once by the person who created it? Or can this be part of a script that is used to create and populate this table? Is there any way I can attempt to find out more about this table?

Index statistics should tell you when the table was last updated, so maybe it really has been just populated once. Of course you could for example add a trigger to the table to track of someone inserts / updates it, but don't forget to remove it later.

SELECT name
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%WhatYouWantToFind%'

I use this to at least start me in the right direction


Execute a script with SQLPlus containing blanks, semi-colons, and forward slashes Occasionally I will get a script that will run fine in SQL Developer or Toad, but requires modification in order to be run successfully from SQL*Plus.  Here is a worst case example containing multiple statements each with blank lines, semicolons, and forward slashes:

INSERT INTO t1 VALUES ('a

;
/
');

INSERT INTO t1 VALUES ('b

;
/
');

DELETE FROM t1 WHERE c1 = 'c

;
/
';

For various reasons these statements need to be run from SQL*Plus.  The blank lines are easy to resolve with a simple...

set sqlblanklines on

I am aware that the sqlterminator can be changed and/or turned off, but both would require modifications to the code, the former moves the problem without resolving it and neither resolves the embedded slash issue.

The best answer would be a way to allow these statements to run without modification by changing the environment in some way (as sqlblanklines does).  If that isn't possible, then perhaps there is a way to programmatically modify the scripts.  I am trying to avoid manual changes.

You can do most of this by using a login.sql. login.sql is executed during - surprising - login and is loaded from your SQLPATH or current directory. For the examples you gave, your really chose the worst case.

Problem is the sqlterminator. Whatever you put in there, the forward slash is maintained as a free sqlterminator. Next to that, sqlplus first scans for the sqlterminator and does this before scanning to the string terminator. A bug if you ask me. The forward slash can be used in a string as long as it is not alone on a seperate line. As soon as sqlplus finds the character specified as sqlterminator, it ignores everything else and stops reading.

The forward slash can be handled, as long as it is not alone on a line.

login.sql contains:

prompt run login.sql
show sqlterminator
show sqlblanklines
set sqlblanklines on
set sqlterminator ';'
show sqlterminator
show sqlblanklines
prompt ready login.sql
set echo on

leigh.sql contains:

INSERT INTO t1 VALUES ('fail bc semicolon
a;a
/
');

INSERT INTO t1 VALUES ('fail bc solo /

aa
/
');

INSERT INTO t1 VALUES ('ok / not solo

aa
/a
');

DELETE FROM t1 WHERE a = 'c

a/
';

run the script:

sqlplus leigh/leigh@orcl @leigh
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 9 22:36:20 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

run login.sql
sqlterminator ";" (hex 3b)
sqlblanklines OFF
sqlterminator ";" (hex 3b)
sqlblanklines ON
ready login.sql
SQL> INSERT INTO t1 VALUES ('fail bc semicolon
  2  a;a
  3  /
ERROR:
ORA-01756: quoted string not properly terminated

SQL> ');
SP2-0042: unknown command "')" - rest of line ignored.
SQL>
SQL> INSERT INTO t1 VALUES ('fail bc solo /
  2
  3
  4  aa
  5  /
ERROR:
ORA-01756: quoted string not properly terminated

SQL> ');
SP2-0042: unknown command "')" - rest of line ignored.
SQL>
SQL> INSERT INTO t1 VALUES ('ok / not solo
  2
  3
  4  aa
  5  /a
  6  ');

1 row created.

SQL>
SQL> DELETE FROM t1 WHERE a = 'c
  2
  3
  4  a/
  5  ';

0 rows deleted.

No need to fiddle with begin/end blocks. Can't handle sqlterminator inside command, no matter where it is, in a string or not, can't handle lines with forward slash alone on a line in a string.

set sqlterminator OFF
<...>
set sqlterminator ON


SQL Server RDS Database Restore from S3 stuck in Restore Mode Summary:
We are doing a restore of a SQL Server database from S3 on an RDS Instance. And all though the job will say the restore is complete when we go to access the database it's stuck in "Restore" Mode and we can't do anything with it.
Detail:
We have spun-up a new SQL Server RDS Instance and we're trying to restore a database from back-up stored in S3. Which is simple enough, we had to do it in the past before maybe a year ago or so. We run the command from the AWS Documentation
EXEC msdb.dbo.rds_restore_database
 @restore_db_name = 'OurDB'
, @s3_arn_to_restore_from = 'arn:aws:s3:::bucket/SQLBackUp.bak'
, @with_norecovery = 1

And we know that command won't work if the Options Group and IAM roles aren't set up, so we've made sure they are. When we run the command it takes a few minutes but appears to run fine.
We run the command to check on the status
EXEC msdb.dbo.rds_task_status
 @db_name='OurDB'

And what as % complete grows over the next few minutes to 100% and the lifecycle value says "SUCCESS". However when I try to access the database, I'm unable to and it says it's stuck restoring and the exact error message I get is "The database OurDB is not accessible. (ObjectExplorer)"
Whenever I search RESTORE DATABASE Stuck RDS SQL SERVER or variations of that, all the stuff I find is about doing a restore of the entire RDS Instance and not an individual database. Or if I'm able to find something about restoring a database that's stuck then it's not about RDS but about regular SQL Server.
Here are some screenshots of what I'm seeing too.

It's not stuck, it's just still in restore mode because you used @with_norecovery = 1. You need to restore database with recovery to finish the process.

...and unless there's something special about the RDS environment, you can just execute
RESTORE DATABASE ourDb WITH RECOVERY

to get you out of that restoring state.


Capitalize only the first letter of each word of each sentence in SQL Server I want to capitalize only the first letter of each word of each sentence in a SQL column.

For example, if the sentence is:

  'I like movies'

then I need the output:

  'I Like Movies'

Query:

declare @a varchar(15)

set @a = 'qWeRtY kEyBoArD'

select @a as [Normal text],
upper(@a) as [Uppercase text],
lower(@a) as [Lowercase text],
upper(left(@a,1)) + lower(substring(@a,2,len(@a))) as [Capitalize first letter only]

Here I did upper, lower, and capitalize first letter only in my column (here I put just a random word).

Here are my results:

Is there any possibilities to do that?

Any possibilities to get results without using user defined function?

I need the output Qwerty Keyboard

declare @a varchar(30);

set @a = 'qWeRtY kEyBoArD TEST<>&''"X';

select stuff((
       select ' '+upper(left(T3.V, 1))+lower(stuff(T3.V, 1, 1, ''))
       from (select cast(replace((select @a as '*' for xml path('')), ' ', '<X/>') as xml).query('.')) as T1(X)
         cross apply T1.X.nodes('text()') as T2(X)
         cross apply (select T2.X.value('.', 'varchar(30)')) as T3(V)
       for xml path(''), type
       ).value('text()[1]', 'varchar(30)'), 1, 1, '') as [Capitalize first letter only];

This first converts the string to XML by replacing all spaces with the empty tag <X/>. Then it shreds the XML to get one word per row using nodes(). To get the rows back to one value it uses the for xml path trick.

Test Data

declare @word varchar(100)
with good as (select 'good' as a union select 'nice' union select 'fine')
select @word = (SELECT TOP 1 a FROM good ORDER BY NEWID())

Implementation

select substring(Upper(@word),1,1) + substring(@word, 2, LEN(@word))


Need assistance shrinking MDF File Verbose logging was accidentally left on for one of our database ballooning our database MDF file from roughly 90GB to 192GB.

We've disabled verbose logging, truncated the logging table but now we want to shrink the MDF file to reclaim disk space as the drive the MDF file is on is only around 200GB. I don't have the luxury of increasing its size. Our disk space monitoring tool is alerting me that drive space is less than 8% over and over and over.

Given this is the first instance this has ever happened, we don't expect to ever need that extra 102GB of disk space again. We'd like to reclaim it.

I've tried to use the GUI to shrink the database and the files but it doesn't actually change in size. I've also tried using DBCC SHRINKFILE and DBCC ShrinkDatabase with NoTruncate and TruncateOnly. No matter what I do the file doesn't seem to budge.

One potential issue is that somehow the database initial size is 188GB. Deleting and re-creating the database isn't a very practical option for us so how can I shrink the MDF file and reclaim our disk space?

Running dbcc shrinkfile('My_Data'), I get the following results:

DBID: 5
Field: 1
CurrentSize:24030464
MinimumSize: 128
UsedPages: 17770472
EstimatedPages: 17770472

The database MDF file is still the same size (192,243,712 KB).

The table has a clustered index:

Name:TRACES
ObjectID: 394588594
Type: Clustered Index/b-tree

The query:

SELECT
    name,
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files

...for the database I am trying to shrink, returns:

Name: My_Data AvailableSpaceInMB: 48693.375000
Name: My_Log  AvailableSpaceInMB: 67028.437500

I've also tried shrinking in small chunks using a PowerShell script that shrinks in 20MB increments. That also doesn't change the size of the MDF file.

I've tried dbcc shrinkfile(My_Data) without any other options from SQL Management Studio. It runs for a long time, finishes, and the MDF file is still the same size. Specifying a target size, it runs for a good 30-40 minutes (finishes) and nothing changes file size wise.

I ran this: DBCC SHRINKFILE (N'My_Data' , 139045) and get the following results:

DbId: 5
Field: 1
CurrentSize: 24030464
MinimumSize: 128
UsedPages: 17808744
EstimatedPages: 17808744

I've tried to use the GUI to shrink the database and the files but it doesn't actually change in size.

You said that you truncated large table, in such case you might not be able to shrink "immediately". From Dropping and Rebuilding Large Objects

  When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after a transaction commits. This implementation supports both autocommit and explicit transactions in a multiuser environment, and applies to large tables and indexes that use more than 128 extents.

It also says that

  Because the physical phase occurs after a transaction commits, the storage space of the table or index might still appear as unavailable. If this space is required for the database to grow before the physical phase is completed, the Database Engine tries to recover space from allocation units marked for deallocation. To find the space currently used by these allocation units, use the sys.allocation_units catalog view.

Other reason why you would not be able to shrink immediately because records are marked ghosted. And shrink will actually give you space backup after background Ghost Cleanup operation has completed, some more information about Ghost Cleanup Task

Initial size as such is not initial size actually, its the current size. You can shrink below initial size. What I suggest you is wait for some time till background operation finishes. Use dbcc shrinkfile tsql command to shrink the files and shrink in small chunks.

Shrinking is single threaded and takes time.

PS: If you are not aware, please note that shrinking causes massive logical fragmentation so don't forget to rebuild fragmented indexes after shrinking is done

Bear in mind that shrinking generates lots of transaction log, so you will need very carefully to manage the size of your transaction log and its backups, while the shrink is running.

Do it in small-ish chunks like 2GB or 5GB, maybe start even smaller, and see how you go. That way if it causes issues while running they hopefully won't last too long. Shrinking is a massively I/O-intensive operation so be prepared for significant performance impact. Run at low- or no-activity times.


logical reads vs. scan count I'm joining a small table (1,000 rows) against a large table (8M rows) in SQL Server 2008. The join uses a nonclustered covering index on the large table, and the join can produce three possible query plans. I'm trying to figure out which plan is better, but also I want to generalize this knowledge so next time I can better know what heuristics to use when looking at SQL I/O statistics.

Plan #1 is a loop join and emits statistics for the large table like this:

Scan count 2582, logical reads 35686, physical reads 1041, read-ahead reads 23052

Plan #2 is a merge join and emits statistics like this:

Scan count 1, logical reads 59034, physical reads 49, read-ahead reads 59004

Plan #3 is a hash join and emits statistics like this:

Scan count 3, logical reads 59011, physical reads 5, read-ahead reads 59010

The covering index is ordered by (ID, Date). The query returns data for about 50% of the IDs and, for each ID, returns a contiguous chunk of the most recent 3 months of data, which is usually about 1/4 or the rows for each ID.  The query returns about 1/8 of the total rows in the index.  In other words, the query is sparse but consistently so.

My assumption is that plan #1 is awful for this workload, because moving the disk head around 2,500 times (or even 1,041 times) is far more expensive than a sequential disk scan.  I also assume that #3 and #2 have similar, sequential (and therefore more efficient) I/O patterns.

But is there a case where plan #1 is really best, where "best" means less impact on the I/O subsystem and less impact on other queries running concurrently?

Or does it really depend on many variables like the kind of disk subsystem I have, index fragmentation, etc. If "it depends" are there any rules of thumb to approach the problem?

Here is the killer deal: it January it was costing $12k to buy 864*GB* of RAM. You can get a lot of bang for the buck by simply increasing the RAM of your server up to the point that you will never hit a physical read (after warm up, of course).

Other than that is really hard to give an black or white opinion about either of those data points you present. Sure plan #1 had most physical reads, but are you positive that all tests were done on similarly warmed up cache? Could it be that #1 warmed up the cache for #2, what is your test methodology to ensure all cases are considered on level ground? Even so, if you shell out $500 and double the RAM, would it matter any more? #1 does have the least logical reads...

But then #2 is probably benefit from a high DOP (that one scan can be parallel). Is the wall-clock time of #2 better that #1 after you added sufficient RAM?

How many of these plans run in parallel? Are there tens of queries requesting concurrently a significant memory grant for the hash of #3 and thus creating contention for the RESOURCE_SEMAPHORE? Is the #2 doing a sort and also requesting a memory grant? Will #1 work better since it requires no grant (at least from the info posted...)?

Is really really relative and the question you ask is more like finding one solution for a complex system of equations... there simply could be more that one solutions.

One things is sure: 8M rows should fit in RAM with plenty room to spare. Those physical reads are begging for some memory banks.

Ignore Scan Count, it is not important. Focus on how to lower Logical Reads. Based on http://www.practicalsqldba.com/2013/07/sql-server-performance-tuning.html.


How to configure my.cnf in 5.6.17? On production server(linux machine mysql 5.6.17), I tried to find my.cnf to change the system variables, but my.cnf was not in /etc/my.cnf location. It was in usr/ directory.
Also that file contains only comments like -

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

Please tell me where should I copy my.cnf and how I can change variables like -

innodb_buffer_pool_size = 200M
max_connections = 300

mysql started with option --defaults-file=FILE  Use the specified defaults file

change config.file(my.cnf)


Redshift: do with clauses /CMTs ALWAYS create temporary tables? I like to use "with clauses" as filters: it makes my queries more "functional" in the linearish functional programming style x= a.b().c().filter(condition).sum() sense (obviously, SQL is one giant functional language, it's just a bit obtuse).

If I am careful about avoiding groupings, distincts or orderings, and only use a CMT in one following query/CMT, will Redshift dispense with the temporary table, and just treat it like a filter? Monitoring seems to suggest this to be the case.

For instance, will this query create two temp tables, or will the planmer say: "hey! no need to store any data…"?

with FilteredList as (
          select col1, col2 from Table
          where col1 is not Null
     ),
     MoreFiltering as (
           select col1, col2 from FilteredList
           where col2 is not Nill
      )
select col1+col2 as Summo
from MoreFiltering;

If you want to know what is going to do the planner. I would suggest to do
EXPLAIN

I would say your planner will execute both CMT


MySQL query of Hotel database How to  List the room numbers that are ‘currently’ occupied at Holiday Inn Express.

I wrote the MySQL query ,but I don't get any output. Can anyone help to solve this?

SELECT r.rm_number
FROM   hotel h,
       reservation rr,
       room_booking b,
       room r
WHERE  rr.res_id = b.res_id
       AND b.rm_id = r.rm_id
       AND r.htl_id = h.htl_id
       AND h.htl_name = 'Holiday Inn Express'
       AND rr.res_checkin <= CURRENT_DATE
       AND rr.res_checkout >= CURRENT_DATE

SELECT r.rm_number
FROM   hotel h,
     reservation rr,
     room_booking b,
     room r
WHERE  rr.res_id = b.res_id
     AND b.rm_id = r.rm_id
     AND r.htl_id = h.htl_id
     AND h.htl_name = 'Holiday Inn Express'
     AND rr.res_checkin IS NOT NULL
     AND rr.res_checkout IS NULL ;


SQL Server - Benefits of splitting databases across different logical drives We're about to start a project to migrate a large DWH to new physical servers in a new data centre. The current server spec is SQL Server Enterprise 2016 SP2 running on Windows 2012 R2. The new servers will be MSSQL 2019 Enterprise running on Windows 2019.
SAN storage for the current and new servers is an all flash storage array. In the current environment as well as separating data and log files onto different logical drives, different databases (data files only) are also split across different logical drives.

Local SSD - TempDb
Logical Drive 1 - log files
Logical Drive 2 - data files for staging databases
Logical Drive 3 - data files for user facing databases
Logical Drive 4 - data files for support databases (ReportServer, MDS database)

As part of the server migration I am considering combining all data files onto a single logical drive.

Local SSD - TempDb
Logical Drive 1 - log files
Logical Drive 2 - data files

As well as database file management is there any performance benefits to keep the data files split across different logical drives? Does multiple logical drives give better IO, even though ultimately it's the same physical storage array?

is there any performance benefits to keep the data files split across different logical drives?

If all the volumes map to the same set of physical disks on the SAN there's normally no difference.
However, if each Volume maps to a different SAN LUN, it's possible for the SAN to allocate storage resources differently to the volumes.  For instance, they can be hosted across separate SAN controllers, have different caching policies, be separately monitored, etc, even if the LUNs share the same underlying storage.
If each volume maps to a separate set of physical disks, then splitting them up is very costly, as you can't pool and share IO resources between the volumes.  And as most database file IO is background IO, you should normally pool all the IO resources for all database files together to maximize efficiency, sharing, and per-database peak-IO throughput.
So, it depends, and you need to work with your SAN experts to choose, and to appropriately configure the SAN and the server (eg IO Queue Depth).

My article from 2007 is still very relevant to this question.  Yes you get a speed boost when using multiple physical volumes.  It applies to SSD as it applies to Harddrives.  In fact, splitting a SQL database across physical volumes is faster than using RAID based on my testing.
https://www.zdnet.com/article/comprehensive-raid-performance-report/


Can SQL Server Audit be used to track hostnames or IP adresses of the users performing DML on a table? I have been reading about SQL Server Audit, and so far I have assimilated that it tracks the command that has been executed, and some other additional information(SPID, object ID and stuff).
But can this be used to uniquely identify the individual that did it, or some type of unique ID of the computer from which the command was executed, such as host name or IP address?

I just do not want to waste my time reading it if it can not uniquely identify the machine or the person executing the command.

With SQLAudit you cannot track HostName or IP address.

However, there's an alternative method for auditing, based on streaming extended events. I blogged about it earlier this year.

Basically, you treat locks acquired as indicators of the operations being performed on the tables (shared locks and schema stability locks for reads and exclusive locks for writes) and you capture these events with an extended events session. You will need to hook a script (or an executable) to that session using the streaming API and you can implement your own logic to filter, categorize and persist the events you're interested in.

Another possibility is capturing the "Audit Schema Object Access Event" events with a trace and streaming them to your own custom application. Here's a tutorial. In Extended Events the same event is not available, as it is reserved for the SQLAudit implementation.

I haven't done it myself but as I understand it you can create a login/out audit specification (these events do pull hostname) and then tie that information to other audit results using the SPID and rundate / login/out date information.

If I have time I'll try to come back later with more specifics.


How to detect inactive tables? I started working on a project whose former developer ragequited, and it left a complete mess both in the code and database. The server is using MySQL 5.5

There are a lot of tables created for backup or test purposes. For example, from all this tables, only two are really being used.

Is there any way to check which tables weren't used in the last XX hours, to be able to safely delete them? Can I set a log for that, besides from the general log, which is really big and fills the available space too quickly?

I cant trust names as in some cases, the table being used is the one that ends with _test or _bk.

Unfortunately, using the update_time column from INFORMATION_SCHEMA.TABLES only works for MyISAM tables. It does not work for InnoDB.

The most effective way to get the last time a table was written is to rely on the OS. You must check the timestamp of the .ibd or .MYD files within a database folder.

I wrote earlier posts about how to do this:

Dec 21, 2011 : Fastest way to check if InnoDB table has changed
Apr 04, 2013 : How to check which tables in DB (MYSQL) updated in last 1 hour / last 1 minute?
Jun 03, 2013 : Is there a way to find the least recently used tables in a schema?
Sep 25, 2014 : want to find out which databases are used in last 30 days or not
Jan 16, 2015 : How can I determine which Innodb table is being written?

SELECT update_time FROM information_schema.tables WHERE table_name='yourtablename'

Or if you want a list of all tables, with last accessed time in order

SELECT table_schema ,
  table_name,
  update_time as LastAccessed
FROM information_schema.tables
GROUP BY table_schema
ORDER BY update_time ASC

For tables weren't used in the last XX hours add WHERE clause

WHERE update_time < 'yyyy-mm-dd'


SQL Server 2012 Installation Error on Windows 2008 Server I am facing an error while trying to install SQL Server 2012 EXPRESS ADVANCED EDITION(32bit) on Windows Server 2008 Enterprise(64bit).

After passing Rule Check, at install updates section, error occurs about update could not be downloaded. On clicking next, following error in dialog box occurs.

SQL Server Setup has encountered the following error:

Setup encountered a failure while running job UpdateResult.

Error code 0x876E0003.

How can I solve this?

Please include the option "UpdateEnabled=False" in the defaultsetup.ini file which can be found in Media**X64\defaultsetup.ini**.
This works like a charm. Please let us know how this goes.


Yes/No columns in logical database design I have an assignment where I have to design a logical model.

I have a relation NURSE that has "nurse_id" and "certification". The certification attribute has yes/no values, so, should I move it to another relation, or is it okay to keep it in the same NURSE relation?

And is the suitable data type for that attribute (CHAR)?

Thank you,

For a logical database design you don’t need to specify TINYINT or CHAR — that’s a physical consideration. Some DBMSes such as Postgres have a BOOLEAN data-type which would be preferred, but again, a physical design.

In your logical model you could assume a binary-valued datatype with Yes and No as the two values (or a ternary-valued type if NULL should also be allowed).

You have two questions actually; for the other, yes, I would keep the flag in the same relation.

You can take a column "flagcertificate" TINYINT(1) datatype.

Additionally  you can create a new Table named nurse_certificates to store certification information.

By adding new table you will be able to store one to one or one to many certification details for one nurse id.


SQL Server 2008 R2 upgrade to 2017 I need to upgrade and migrate SQL Server, SSRS, SSIS to the newest OS version and SQL Server version.

Server OS is currently 2008 R2 with SQL Server 2008 R2 Ent. installed.

My original plan was to do in place upgrades and then migrate to new server OS. It sounds like SQL Server 2017 is not supported on 2008 R2, so I'm not sure whether or not this is an option.

What is the best migration/upgrade path for server OS upgrade and SQL Server version upgrade?

Windows Server 2008 R2 and SQL Server 2008 R2 Ent -> Windows Server 2019 and SQL Server 2017 Ent.

As always it depends...

Whats the complexity of your instance?
How many SSIS packages / SSRS reports do you need to move along?
How much time do you have to perform this upgrade?
Whats your maintenance windows within which the service can be inaccessible?
And many many more...

Before you start looking for any kind of answers I would recommend to create an inventory of what exactly do you have.
Very helpful thing is to add source control over your entire DB server. With that in hand you can "mask it a bit" and then share with the community - much higher chances for good answers.

Algorithm:

first upgrade to MS SQL Server 2014 with the latest updates
upgrade Windows Server to version 2019
update MS SQL Server to 2016 or 2017 version

I don’t put the 2017 version, and there were a lot of problems including administration. Upgrade MS SQL Server to 2016, and then wait for the release of MS SQL Server 2019


Script to backup all databases I'm working on automating backups of SQL Server databases on SQL 2008 R2 Express. These backups are initiated daily by the Windows Task Scheduler, which execute a batch file, which in turns executes a SQL Script. I cannot use the SQL Agent because this is the express edition.

Currently, I am writing a script which backs up each database one by one. However, this is quite high maintenance - every time a database is added, dropped, or renamed, I have to modify this script to reflect these changes. I would instead like to write one single SQL script which backs up all databases at once, without the need to explicitly backup each database one by one.

This is currently the script used to backup a single database:

Print 'Backing up MyDatabase...'
BACKUP DATABASE MyDatabase
TO DISK = 'F:\Backups\SQL Databases\MyDatabase.bak'
   WITH FORMAT,
      NAME = 'Full Backup of MyDatabase';
GO

How can I go about enumerating a list of all databases and performing a loop to do this backup to all databases?

Just some additional notes of the backup process:

Scheduled Task in Windows executes a batch file at 9:00 PM daily
Batch file calls OSQL which executes the SQL script
The OSQL outputs a text file saving the results
There are about 30 databases to be backed up
Some of the databases are over 2GB (so it will take some time)
A third-party backup service obtains the resulting backup files and pushes them off-site at midnight every night

I believe you can refer to ola.hallengren. SQL server Backup scripts.
As stated on how to use for SQL server Express:

SQL Server Express has no SQL Server Agent. Therefore, the execution
of the stored procedures must be scheduled by using cmd files and
Windows Scheduled Tasks. Follow these steps.

Download MaintenanceSolution.sql.
Execute MaintenanceSolution.sql. This script creates the stored    procedures that you need.
Create cmd files to execute the stored procedures; for example:    sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType
= 'FULL'" -b -o C:\Log\DatabaseBackup.txt
In Windows Scheduled Tasks, create tasks to call the cmd files.
Schedule the tasks.
Start the tasks and verify that they are completing successfully.

Now you can modify this easily as per you're needs.
Note: In MaintenanceSolution, you can separately only download SQL server Backup scripts individually.

Although sp_msforeachdb will be deprecated soon for Microsoft SQL Server this should work for you. Wrap this up in a stored procedure / job, make sure the account has access to write to the drive..

sp_msforeachdb @command1='
DECLARE @string NVARCHAR(500)
SET @string = CONVERT(NVARCHAR(20),GETDATE(),120)
SET @string = REPLACE(REPLACE(REPLACE(@string,''
'',''''),'':'',''''),''-'','''')
SET @string = REPLACE(@string, '' '', '''')
SET @string = ''
BACKUP DATABASE # TO DISK = N''''F:\BACKUPS\SQL DATABASES\#_FULL_''+@string+''.bak''
SET @string = @string + ''''''
WITH FORMAT, NAME = N''''Full Backup of #''''''
IF ''#''not in(''master'', ''tempdb'',''msdb'',''model'')
begin PRINT @string  execute sp_executesql @string end'
,@replacechar='#'

Example execution output:

BACKUP DATABASE DatabaseDuo TO DISK = N'F:\BACKUPS\SQL DATABASES\DatabaseDuo_FULL_20151019190410.bak'
WITH FORMAT, NAME = N'Full Backup of DatabaseDuo'

BACKUP DATABASE Databaaase TO DISK = N'F:\BACKUPS\SQL DATABASES\Databaaase_FULL_20151019190410.bak'
WITH FORMAT, NAME = N'Full Backup of Databaaase'

BACKUP DATABASE GarbageBase TO DISK = N'F:\BACKUPS\SQL DATABASES\GarbageBase_FULL_20151019190410.bak'
WITH FORMAT, NAME = N'Full Backup of GarbageBase'

Note that this will ignore master tempdb msdb and model databases. But will backup everything else up for you.


Microsoft access to Sql Server Data Migration I need to migrate some old MS Access data to a SQL Server Database. Which tool is easier for a newbie to work with: SQL Server Import/Export or Migration assistant for access(SSMA)

I would use the SQL Server import wizard, but make sure to use staging tables to import the data from Access, so you can perform data cleansing before inserting on the definitive tables.

A staging table is a temporary table used to load the data "as is" in the destination system (SQL Server in your case) so you can make sure the datatypes, collation, any custom format is according to your needs. This is made to avoid key/constraint violations, errors due to incompatible datypes, etc...

So for this example, you can import your access tables and ask the wizard to create the new tables during the import, after your are ok with your data, you can insert them in your definitive tables.

Access can hook up directly to SQL Server through what are called linked tables. Then it's a copy/paste.


SELECT , GROUP AND DISPLAY IN TABULAR FORM I Have a table as above. How can I get an output (as shown below) with a single mysql query.

SELECT location,
       AVG(CASE WHEN qtr = 'Q1' THEN achievement END) q1-average,
       AVG(CASE WHEN qtr = 'Q2' THEN achievement END) q2-average
FROM sourcetable
-- WHERE year = '2018'
GROUP BY location;

SELECT location,
       AVG(SELECT achievment FROM location WHERE QTR='Q1'),
       AVG(SELECT achievment FROM location WHERE QTR='Q2')
FROM location
GROUP BY location


Monitoring replication status from the subscriber I have two databases that are populated by our vendor using replication from their databases. I do not have access to their distributor db. But I want to monitor from our end whether the data has been refreshed as per schedule.

Is there a way to do this? All the blogs/posts I have seen require access to the distribution db.

You can use the canary method to monitor replication status on the subscriber. Check it out here in the "Easy Replication Monitoring: Alert on Latency with Canary Tables" section.

With this method, you would be writing to a table on the publisher that has just one column. Update the table every minute with CURRENT_TIMESTAMP. Monitor the table on the subscriber and fire off an alert if the difference between the table and the current time has exceeded your threshold.

If your replicated environment are supposed to assume the same amount of work and be exactly the same as your vendor's, you could maybe turn on the MySQL queries logging, so you can see all the SQL statements hitting your database, by monitoring the queries logs you can have accurate data about the database and know if it is refreshed and when.
Implementing some kind of automatic methods to filter the queries you are interested in should allow you to know all you need. For example you could count the total amount of "INSERT" and "UPDATE" type quereis and draw graphs with that information.
This is just an idea, but maybe you could make use of some opensource monitoring tool to get the most value out of it and even draw graphs, reports...
Hope this helps.


Why database has char data type when varchar is more efficient? I understand the basic difference between CHAR and VARCHAR datatype that CHAR takes up fixed length whereas VARCHAR takes up space based on the content being stored.
But if VARCHAR is so efficient in handling the space management dynamically based on the content being stored, why databases has the support for CHAR datatype, all CHAR(n) fields can be VARCHAR(n) fields, right ?

You should read this article SQL varchar data type deep dive, and specifically the Storage and performance considerations using SQL varchar section:

Because of the fixed field lengths, data is pulled straight from the column without doing any data manipulation and index lookups against varchar are slower than that of char fields. CHAR is better than VARCHAR performance wise, however, it takes unnecessary memory space when the data does not have a fixed-length. So in cases where disk size is not an issue, it is recommended to use CHAR.

Note this specifically speaks to the differences from a Microsoft SQL Server perspective, YMMV for other database systems, and not all support both CHAR and VARCHAR data types (e.g. SQLite has neither data type and only supports what it calls the TEXT data type).
And here's some good information, in similar vein, from MySQL's perspective, courtesy of RolandoMySQLDBA - DBA.StackExchange answer to What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?:

Since CHAR fields require less string manipulation because of fixed field widths, index lookups against CHAR field are on average 20% faster than that of VARCHAR fields.

Surprisingly, in the domain of PostgreSQL, it sounds like the same performance differences don't exist, and therefore the practical differences between the data types are less abundant. Please see this StackOverflow answer to Difference between text and varchar (character varying) and this DBA.StackExchange answer to Index performance for CHAR vs VARCHAR (Postgres):

...the performance of inserts and selects for all 4 data types are similar.

CHAR and VARCHAR are implemented exactly the same in Postgres (and Oracle). There is no difference in speed when using those data types.

I would like to explain using a simple a example
You need to Store gender in database
Possible Values F,M,O
using char it will take 1 byte for each entry (8bits)
using varchar it will take 1 byte and 2 byte for each entry (say 24 bits)
by using char you would be able to reduce size of database to 33%


Query problem: there can be only one auto column Can someone please tell me what's wrong with this table definition.
mysql version is 5.1.52-log

root@localhost spoolrdb> create table spoolqueue (
                             queue int,
                             idx bigint not null auto_increment,
                             status smallint,
                             querystring varchar(2048),
                             contenttype varchar(255),
                             characterencoding varchar(16),
                             body text,
                             primary key(queue,idx)
                             );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

This will apparently work with MyISAM as storage engine, not InnoDB, if you can live with that.

Another way to get it to work is if you swap places of queue and idx in the primary key declaration.

Try removing the queue field from the primary key.
You can index the queue column if you want


Use pg_dump with postgis extensions? I'm using Postgres 9.4. I'm trying to dump a database (both the schema and the actual data) with the PostGIS extensions (set up using CREATE EXTENSION):

$ pg_dump prescribing -U prescribing -h localhost -Fc > prescribing.dump
Password:

But when I type in the db password, I see this:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for schema topology
pg_dump: [archiver (db)] query was: LOCK TABLE topology.topology IN ACCESS SHARE MODE

How can I dump this database?

UPDATE: it says these tables are owned by postgres:

 public | frontend_sha                               | table             | prescribing
 public | geography_columns                          | view              | postgres
 public | geometry_columns                           | view              | postgres
 public | pg_stat_statements                         | view              | postgres
 public | raster_columns                             | view              | postgres
 public | raster_overviews                           | view              | postgres
 public | spatial_ref_sys                            | table             | postgres
 public | vw_chemical_summary_by_ccg                 | materialized view | prescribing

Maybe I could just dump the other tables, and import them into another database set up in the same way? Or maybe I could run pg_dump as a superuser? But if I try pg_dump prescribing then it still asks me for a password, and I don't know what that should be.

What about using -T modifier for pg_dump to skip those tables?

For example -T topology.*


How to most efficiently find out if a record has child records? I am writing a query that returns a single record from the parent table. I would like to also return in this query if it has any children. This is a one to many relationship.

parent:
 -parent_id
 -name

child:
-child_id
-name
-parent_id

My first instinct is to write the following query:

select name, (select count(child_id) from child c  where c.parent_id=p.parent_id) children
     from parent p
     where name like 'some name'

But I was wondering if there was a more efficient way to do this, since I don't actually care about the count, just whether or not it has children. Any pointers?

Don't forget that Postgres has a boolean datatype. The following is the most succinct way to express the query:

select
  parent_id,
  name,
  exists (select from child where parent_id = p.parent_id) as has_children
from parent p;

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=86748ba18ba8c0f31f1b77a74230f67b

As long as there is index on parent_id column of child table your query should be good.


XtraBackup fails with my.cnf Percona Configuration After using a percona optimized .cnf file the backup fails
Installing my.cnf
# service mysql stop
(Installed my.cnf)
# rm ib_logfile0
# rm ib_logfile1
# service mysql start

Mysql creates new log files on startup

my.cnf
# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208

[client]

# CLIENT #
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld]

# GENERAL #
user = mysql
default_storage_engine = InnoDB
pid_file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
basedir = /usr/local/mysql
tmpdir = /tmp

# MyISAM #
key_buffer_size = 32M
myisam_recover = FORCE,BACKUP

# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
sql_mode = STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION
sysdate_is_now = 1
innodb = FORCE
innodb_strict_mode = 1
bind_address = 127.0.0.1

# DATA STORAGE #
datadir = /var/lib/mysql

# BINARY LOGGING #
log_bin = /var/lib/mysql/mysql-bin
expire_logs_days = 14
max_binlog_size = 100M
sync_binlog = 1

# CACHES AND LIMITS #
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0
query_cache_size = 0
max_connections = 500
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 1024
table_open_cache = 2048

# INNODB #
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
innodb_buffer_pool_size = 6G

# LOGGING #
log_error = /var/lib/mysql/mysql-error.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log

error log
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

131210 11:53:50 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
131210 11:53:50 innobackupex: Connected to MySQL server
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".

innobackupex: Using mysql server version 5.6.14-log

innobackupex: Created backup directory /var/www/dyntest.dk

131210 11:53:50 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/tmp --tmpdir=/tmp --stream=tar
innobackupex: Waiting for ibbackup (pid=6728) to suspend
innobackupex: Suspend file '/tmp/xtrabackup_suspended_2'

xtrabackup_56 version 2.1.5 for MySQL server 5.6.11 Linux (x86_64) (revision id: undefined)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Error: log file ./ib_logfile0 is of different size 268435456 bytes
InnoDB: than specified in the .cnf file 50331648 bytes!
innobackupex: Error: The xtrabackup child process has died at /usr/bin/innobackupex line 2579.

the hint is in last error messages as follows

xtrabackup: innodb_log_file_size = 50331648
InnoDB: Error: log file ./ib_logfile0 is of different size 268435456 bytes

my.cnf implicitly refereced by xtrabackup has innodb_log_file_size = 48M (50331648 bytes), but actual log file size is 256MB (as shown your my.cnf)

I think you should specify my.cnf explicitly like this

xtrabackup --defaults-file=/path/to/my.cnf

I had the same issue.
The fix is simple, just add the following to my.cnf in the [mysqld] section
innodb_log_file_size=48M

(Use whatever size log file you need, 48M is OK for small Dbs)

Then follow the instructions to restart mysql with a new log file size.
The basics are (but READ the Manual!)
stop mysql
remove the ib_logfile* files from /var/lib/mysql
restart mysql

Be sure not to be using innodb fast shutdown (read the manual!)


Powershell Restore Azure SQL Database to local with Restore-Database I have written a powershell script that uses Start-AzureSqlDatabaseExport & Get-AzureStorageBlob to export my Azure DB to a bacpac and download it locally, the next step is to restore the bacpac to my local SQL DB using Restore-Database, but below fails with "the media family on device is incorrectly formatted".

Restore-Database -dbName "MyDB" -backupFile "C:\MyAzureBackup.bacpac"

Is there a way to get this working with Restore-Database, or is there an alternative command that will do this?

I have not actually tried this (but it peaks my interest to later).

You will not be able to use Restore-Database because it only reads native backup formats from SQL Server, not Azure SQL.

The BACPAC file created from your backup of an Azure SQL database is in a data-tier format. Which in this format contains the schema and data for the database. In order to restore this to a local instance in PowerShell you will have to utilize the DAC objects to do the restore. Which if you are loading the SQLPS module the DAC assembly is loaded for you.

Basic examples that I find online seem to follow this format:

Add-Type -path "C:\Program Files (x86)\Microsoft SQL Server\<version>\DAC\bin\Microsoft.SqlServer.Dac.dll"
$restoredDatabaseName = 'NewDatabase'
$bacpacFile = "Your BACPAC file you downloaded"
$conn = "Data Source=Server;Initial Catalog=master;Connection Timeout=0;Integrated Security=True;"

$importBac = New-Object Microsoft.SqlServer.Dac.DacServices $conn
$loadBac = [Microsoft.SqlServer.Dac.BacPackage]::Load($bacpacFile)
$importBac.ImportBacpac($loadBac, $restoredDatabaseName)

#Clean up
$loadBac.Dispose()

Once I get something setup I will test the above myself, just to make sure.

Update

The dbatools PowerShell module now includes commands around managing DacPac files. This can be utilized to perform export and publish operations.

Publish-DbaDacpac. I have not had chance to test this against an Azure SQL instance.

I cant take credit for this. Credit goes to David Browne.

https://stackoverflow.com/questions/51730309/the-term-microsoft-sqlserver-dac-bacpackageload-is-not-recognized

but this

$loadBac = Microsoft.SqlServer.Dac.BacPackage::Load($bacpacFile)

should be this

$loadBac = [Microsoft.SqlServer.Dac.BacPackage]::Load($bacpacFile)

Square brackets.


PostgreSQL: Merge rows with the same date and time but different numeric values in one row with average of the values I am working on a financial software where we have huge table of ticks with more than 800M rows.

A single row: symbol_id, broker_id, date, time, bid, ask

The problem is the table has many ticks with identical first four columns and different bid and ask.

What is the best way to update the table and combine such rows into one by taking an average of bid and ask columns?

Example:

|1|2|2012-01-01|12:00:00|1.3456|1.3678|

|1|2|2012-01-01|12:00:00|1.3463|1.3612|

Result row:

|1|2|2012-01-01|12:00:00|1.3459|1.3645|

|1|2|2012-01-01|12:00:00|1.3459|1.3645|

SELECT col1, col2, col3, col4, AVG(col5) col5, AVG(col6) col6
FROM table_name
GROUP BY col1, col2, col3, col4;