do oracle redo logs need to be copied when all other data is copied to a new disks? I have to migrate all data for an Oracle server from  the present SAN to a new SAN.  It is an 11g database in production on a Linux server.  Database software is on local disk but all data (including datafiles, archive logs, control files, etc) are on a SAN via NFS.

The plan is to shut down Oracle before the sysadmin copies over the data to the new SAN.  Since we are using NFS, when I start up Oracle again, the change should be transparent to Oracle.

The sysadmin has been told that the redo logs do not need to be copied.  It seems to me that the redo logs should be archived before the copy.  Should I force a log switch (alter system switch logfile)before the shutdown and then copy the archive logs?  Or will a shutdown trigger the redo logs to be archived?

If you are using ASM, you could even do this online by first adding the new disks to the current diskgroups, rebalance the disk groups and drop the old SAN disks from the diskgroups. During this drop, the first thing that happens is - again - a rebalance action in which the data that is on the dropping disks is moved from those disks to the new disks. A very powerful feature, not sure if it can help you.

If it all has to be done using FS copies, it still can be done online, if you are running archive log mode. Since 11g RMAN can clone from an active database, also very powerful.

FWIW: some applications check the controlfile and complain if it is changed. This is part of a check for licenses.

Short answer for you question, you need the online redologfiles. The archives are only needed for recovery operations so they should remain accessible, not perse on the new SAN. A regular backup would be ok. Copy the online redo logfiles and use RMAN for the copy of the database since it handles everything that needs to be done in a good way.


Configure SQL Server to encrypt data traffic in both directions How can I ensure that SQL Server data traffic is encrypted over the wire both from server to client and from client to server?

I have set Force Encryption to Yes and that alone seems to hide query information from Profiler and MS Message Analyzer. Is that all I need to do? A lot of documentation states that a certificate is also required.

So I created a p12 certificate, imported it into the certificate store and selected it from the "Certificate" tab in SQL Server Configuration Manager Protocols. Then I was unable to restart the service even after removing the certificate.

I'd like to get some clear instructions on how to achieve wire encryption.

There are two possible ways to ensure that you encrypt connections from clients to the SQL Server. The first option is to setup IPsec either manually on both clients and server or by using a IPSec policy or you can setup SSL encryption. The simplest way to do so is to use the self signed certificates from the server but as those are exchanged during the connection handshake their security is limited so it's recommended to use certificates from a trusted root.

You can create a test certificate with MAKECERT.exe by following TheSQLDude instructions and copy the self created root certificate to all the clients.

So to set up SSL encryption over the wire in SQL Server you either need a certificate signed by a trusted provider, which means that your SQL Server needs to have a valid FQDN as no certificate providers create certificates for invalid Fully-Qualified Domain Names (eg .local). Or you can also install your own trusted PKI infrastructure for instance Active Directory Certificate Services (ADCS).

This is needed as all the clients need to trust the root certificate that signs the certificate you install on the SQL Server.

When creating a certificate for SQL server the certificate needs to be created with certain extended properties most importantly it needs to be setup as Server Authentication (1.3.6.1.5.5.7.3.1) which means you might need to create a template in ADCS or you can create the certificate CSR using certreq

For this you need to create an csr.inf file

[Version]
Signature = "$Windows NT$"
[NewRequest]
Subject = "CN = SERVER.DOMAIN.TLD"
FriendlyName = SERVER.DOMAIN.TLD
MachineKeySet = true
RequestType=Cert
;SignatureAlgorithm = SHA256
KeyLength = 4096
KeySpec = 1
KeyUsage = 0xA0
MachineKeySet = True
Exportable = TRUE
Hashalgorithm = sha512
ValidityPeriod = Years
ValidityPeriodUnits = 10
[EnhancedKeyUsageExtension]
OID=1.3.6.1.5.5.7.3.1

You can then create a CSR to send to a CA

  certreq.exe -new csr.inf keycertrequest.csr

or get a certificate from ADCS by in an elelvated command prompt

  Certreq -new -f cer.inf Certificate.cer

And then you can export that certificate from your personal store and import that to the store for the SQL Server account.

When you have a certificate installed you can enable encryption in the SQL Server Configuration manager.
It's recommended to only use TLS 1.2 but then you need make sure you have updated the SQL Server native client and your SQL Server to the latest update. This is all well explained by SentryOne and in the release announcement


Passing variable as parameter to json_query function I am getting error:

  The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal

...while trying to pass variable as parameter to JSON_QUERY function. Please help me to resolve it.

DECLARE @cnt INT = 1;
DECLARE @cnt_total INT = 1;
DECLARE @json NVARCHAR(MAX);
DECLARE @json1 NVARCHAR(MAX);
declare @str  VARCHAR(200);

WHILE @cnt <= 10
BEGIN
    set @str = '$.Seasons[0].Products['+convert(varchar,@cnt)+'].ProductChannels';
    set @str = char(39) +@str+ char(39);
    PRINT @STR
    select @json = json_query(jfile,@STR) from Import.tstjson;
--MORE CODE
   SET @cnt = @cnt + 1;
END

Sample data:

{
  "Seasons": [
    {
      "Season": "12321231",
      "Products": [
        {
          "ProductId": "211",
          "ProductChannels": [
            {
              "ChannelId": 1,
              "WeekQuantities": []
            }
          ],
          "ccc": 3,
          "Tttt": 4
        }
      ]
    }
  ]
}

It is possible in SQL Server 2017.

From JSON_QUERY (Transact-SQL)

  In SQL Server 2017 and in Azure SQL Database, you can provide a
  variable as the value of path.

In SQL Server 2016 you have to build the query dynamically.


Raid Configuration for MS SQL server Please Help...
What are the best RAID configuration for MS SQL server.
High availability, redundancy and recovery are require.
All I need is best performance. cost is not a factor for me but a 500-800  concurrent hits would be there.

You're going to have to do quite a bit more work on requirements if you want to do this properly, or you could cheat and buy SSDs and do minimal benchmarking.

Prior to SSDs putting anything on RAID6 for SQL Server was planned out very carefully, and done mostly for read only scenarios.  With the advent of SSDs we can often use RAID 6 with no impact to performance outside of some negligent latency.

Performance Metrics:

You will want to collect the following metrics at a minimum in Perfmon to see your usage.  Include all data and log files:

-Logical Disk
--Disk Sec/Read
--Disk Sec/Write
--Disk throughput/sec

RAID Considerations:

You'd want to invest in a good raid controller and with RAID 6 you can suffer a 2 disk outage instead of a single disk outage from RAID 5.  This requires more parity to be calculated and written thus reducing performance but the Samsung 850 Pro line can churn through that very easily.

File Placement:

The log file and tempdb were almost always split up and depending on your workload/high availability solution/acceptable data loss policy you might still be bound to some of that.  Just note that if you go with 1 big array and the disks go down, you lose the log file data that hasn't been backed up yet either; thus affecting your acceptable data loss policy.

I know it's rather unorthodox to recommend commodity hardware for SQL Server but after using thousands of these hard drives in high throughput and volume environments, I stand by it.

Do note that your disk controller and server are still a single point of failure.  You should also do real HA such as log shipping/mirroring/AGs/etc.


Import issue with Replication When I import some data from excel file to a table that is part of a merge replication, the data is imported successfully to the table, but those new records are not synced to subscribers that belong to the merge replication topology!

Is this by default?

Am I missing some configuration ?

It turns out that the Import wizard ( because it is considered like a Bulk Insert or BCP ) is disabling replication triggers on the tables they are loading data to, which prevents Merge Replication from detecting new records.

This article: Bulk loading data into a merge replicated table explains this with more details.

One workaround is to use the procedure: sp_addtablecontents after importing data to let the merge replication detect the new rows like the following:

Exec sp_addtabletocontents @table_name = 'Customers'


SQL Server Delta Records Pull & Push In our SQL Server DB, we have about some 800+ tables and there are 40 - 50 tables are business critical tables. MIS team needs to generate reports based on those 50 business tables.

Day by day, records are getting huge in those business tables. Daily MIS team is pulling those millions of records directly from Production like select * from Table1 and pushing those records into their environment. Table1 may have 30 million records.

Those 50 tables gets updated frequently. MIS team requires those delta records (update/inserted/delete)

Our Senior DBA advice us to use the below 4th approach. He says replication is a failure model and it will leads I/O problem.

What would be the best solution?

We have few approaches here

Always On
Replication
Mirroring
Introducing new column (LastModifiedDate & creating index) in those 50 tables and pulling those records periodically and populating it to MIS environment.

There will be huge code change for the new column LastModifiedDate approach.

Based on those 50 tables, we have huge number of stored procedures which it has Insert/Update statements. In those stored procedures, we need to do code change for LastModifiedDate.

Since we have SQL Server 2008 R2 only, we cant got for Always On approach. If it is the only approach, then we can approach management to upgrade the 2008 to 2014/2016.

What would be the best solution from the above approaches?

Please let me know if any other approach to do.

"Best" is fairly subjective since it depends on your requirements.

Your listed approaches are:

Always On - If you could implement a readable secondary, this might provide a good way for your MIS team to be able to see up-to-the minute data.  Downside to this - you need to upgrade your SQL Server, which might be costly, and it will take a fair amount of technical knowledge to get right.
Replication could be setup to replicate the desired tables to a second SQL Server instance that could then be used by the MIS team in a read-only fashion.  Replication can be tricky to setup correctly, and can be difficult to maintain.
Mirroring won't allow your MIS team to read data from the mirror database.  The mirror database is kept in a perpetual state of "recovery" until (if) the primary becomes unavailable.
Adding a column to the database.  If you have code written using SELECT *, adding a column can be problematic since you'll need to revisit all that code so it can handle the new column - the takeaway from that exercise would be "never use SELECT *".  Use a rowversion datatype for the new column since it will automatically increment every time any insert/update/delete occurs in the entire database.  At the end of each "import" operation, the MIS team would record the current rowversion value, to limit the rows being imported during the next run to only those rows that have been modified.

Another possible solution would be to use triggers to insert modified rows into holding tables that become the source of the MIS import job.

A simple example of how that might work.  We'll create this test in tempdb:

USE tempdb;
IF OBJECT_ID(N'dbo.MainTable_Holding', N'U') IS NOT NULL
DROP TABLE dbo.MainTable_Holding;
IF OBJECT_ID(N'dbo.MainTable', N'U') IS NOT NULL
DROP TABLE dbo.MainTable;

The main table:

CREATE TABLE dbo.MainTable
(
    MainTableID int NOT NULL
        CONSTRAINT PK_MainTable
        PRIMARY KEY
        CLUSTERED
        IDENTITY(1,1)
    , someval varchar(100) NOT NULL
);

The "holding" table, where rows will exist until they are processed by the MIS job:

CREATE TABLE dbo.MainTable_Holding
(
    op_id int NOT NULL IDENTITY(1,1)
    , MainTableID int NOT NULL
    , someval varchar(100) NOT NULL
    , op tinyint NOT NULL
        CONSTRAINT CK_MainTable_Holding_op
        CHECK (op IN (1, 2, 3, 4))
);

CREATE CLUSTERED INDEX CX_MainTable_Holding
ON dbo.MainTable_Holding (op_id);
GO

The trigger that will capture modifications from the main table into the holding table:

CREATE TRIGGER MainTableHoldingTrigger
ON dbo.MainTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @op_ins tinyint;
    DECLARE @op_del tinyint;
    SET @op_ins = 1;
    SET @op_del = 2;
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        SET @op_ins = 3; --updated to
        SET @op_del = 4; --updated from
    END

    INSERT INTO dbo.MainTable_Holding (MainTableID, someval, op)
    SELECT d.MainTableID
        , d.someval
        , @op_del --delete
    FROM deleted d;
    INSERT INTO dbo.MainTable_Holding (MainTableID, someval, op)
    SELECT i.MainTableID
        , i.someval
        , @op_ins --insert
    FROM inserted i;
END
GO

A couple of test statements.  First we insert 10 rows:

INSERT INTO dbo.MainTable (someval)
VALUES (REPLICATE(CHAR((CRYPT_GEN_RANDOM(1) % 26) + 65), 10));
GO 10 --insert 10 rows

Here, we delete the first 5 rows:

DELETE
FROM dbo.MainTable
WHERE dbo.MainTable.MainTableID <= 5;

This updates the 6th row:

UPDATE dbo.MainTable
SET dbo.MainTable.someval = 'XXXXX'
WHERE dbo.MainTable.MainTableID = 6;

Here we can see the contents of the holding table:

SELECT mth.MainTableID
    , mth.someval
    , Operation = CASE mth.op
        WHEN 1 THEN 'INSERT'
        WHEN 2 THEN 'DELETE'
        WHEN 3 THEN 'UPDATE TO'
        WHEN 4 THEN 'UPDATE FROM'
      END
FROM dbo.MainTable_Holding mth;

The results:

╔═════════════╦════════════╦═════════════╗
║ MainTableID ║  someval   ║  Operation  ║
╠═════════════╬════════════╬═════════════╣
║           1 ║ XXXXXXXXXX ║ INSERT      ║
║           2 ║ VVVVVVVVVV ║ INSERT      ║
║           3 ║ NNNNNNNNNN ║ INSERT      ║
║           4 ║ CCCCCCCCCC ║ INSERT      ║
║           5 ║ RRRRRRRRRR ║ INSERT      ║
║           6 ║ GGGGGGGGGG ║ INSERT      ║
║           7 ║ CCCCCCCCCC ║ INSERT      ║
║           8 ║ HHHHHHHHHH ║ INSERT      ║
║           9 ║ VVVVVVVVVV ║ INSERT      ║
║          10 ║ KKKKKKKKKK ║ INSERT      ║
║           5 ║ RRRRRRRRRR ║ DELETE      ║
║           4 ║ CCCCCCCCCC ║ DELETE      ║
║           3 ║ NNNNNNNNNN ║ DELETE      ║
║           2 ║ VVVVVVVVVV ║ DELETE      ║
║           1 ║ XXXXXXXXXX ║ DELETE      ║
║           6 ║ GGGGGGGGGG ║ UPDATE FROM ║
║           6 ║ XXXXX      ║ UPDATE TO   ║
╚═════════════╩════════════╩═════════════╝

The MIS job would simply read all rows from dbo.MainTable_Holding, performing the appropriate insert, update, or delete.  Rows that have been inserted into the MIS database could be removed from the holding table, limiting the amount of space required by it.

Another possible solution would be to use SQL Server's queue mechanism, Service Broker.  Service Broker could be setup to send messages into the MIS Server; each message would be an insert/update/delete operation from one of the affected source tables.  Service Broker on the MIS Server would then pickup those messages, processing them asynchronously.


What's the best practice for representing set operations in a relational database? I have a simple database representing collections of entities and sets of those collections. Sets contain multiple collections and collections contain multiple entities. Currently, when I query all the entities belonging to a given set I'm returning a union of all the entities in each collection belonging to that set. How could I redesign the schema and queries to allow for more flexible set logic? For instance given 4 collections, when I select all the entities in set 1, I want to get (collection 1 INTERSECT collection 2 EXCEPT collection 3) and when I query set 2 I want to get (collection 3 UNION (collection 2 INTERSECT collection 4)). In other words, I need a way to represent dynamic nested set operators in a relational database.

I can think of a few potential ways to accomplish this, suffice to say they are all non-trivial. What's the best practice for situations like this? Can anyone point towards any papers or articles on how to accomplish this? Thanks in advance.

Currently, when I query all the entities belonging to a given set I'm
  returning a union of all the entities in each collection belonging to
  that set.

The proper way to query all entities belonging to a given set is to JOIN your tables and use a WHERE clause to limit the set to the given set.

  …given 4 collections, when I select all the entities in set 1, I want
  to get (collection 1 INTERSECT collection 2 EXCEPT collection 3)

This statement does not make sense.  Either you are selecting all entities in set 1 or you are not.  If you are only selecting collection 1 INTERSECT 2 EXCEPT 3, then you are not selecting all entities in set 1.  You can select all entities in set one that are also in collection 1 INTERSECT 2 EXCEPT 3, let us know if this is what you meant.

It looks like you have different patterns of ways you would like to transform the data.  In Oracle, if you give each pattern a function returning a pipelined result set, you can then pass the appropriate collection identifiers to the appropriate pattern function and have it return the requested results.  So, for you first example you could call:

SELECT * FROM TABLE(MyPatterns.Pattern1(1,2,3);

The Pattern1 function would do the x INTERSECT y EXCEPT z and return the results.


MongoDB:Read from slave dirctly,how to guarantee that the data is just up-to-date? I know that MongoDB is the master/slave architecture,to make the read faster,some read request is directly sent to slaves.But since it is a distributed system,when the data is write to a master,the master will write the data to an editlog,the slaves will read the log to synchronize data of itself.So, the data read directly from slave maybe just dirty data.So , what we can do to make sure that the data read from salve is just the up-to-date data?

Master/Slave replication is deprecated, will be removed in future versions and has no advantage over a replica set with two data bearing nodes and an arbiter (which is extremely cheap in terms of disk, CPU and RAM usage), safe for the fact that you don't need to run an arbiter. A replica set however offers automatic failover and some administrative advantages.

Another misconception you have is that replication (no matter which kind) is supposed to increase read performance. While you can use it for this, it is a very poor man's solution. I'd rather call it a highly impovered man's solution for performance scaling. Replica sets are MongoDB's means of ensuring data availability and durability.

That being said: there is a way to ensure that the secondary nodes are consistent with the primary node of a replica set, but it comes at a cost - and not a small one. You can set the write concern in a way that a write is only considered successful when it is written to all nodes of a replica set. The problem is that this requires some round trips which make the write operation slow - by orders of magnitude in the worst case. Also, if one of your servers fails, write operations with a write concern of "all" will fail, since the nodes originally forming the replica set are taken into account, not only the ones which are up. In case you can live with those drawbacks, you can even make absolutely positively sure that the data is written to the data files and not "only" the journal by setting the fsync option to true. Be warned that writes may take a VeryVeryLongTime(TM) to return with this option activated. I generally discourage it's use except for very special use cases.

For performance scaling, the solution offered by MongoDB is scaling out by means of sharding. When done correctly, you basically distribute your collections over several shards, thereby keeping a larger part of your working set in RAM and making read and write operations on the data files being done in parallel (document wise).


Updating multiple tables with similar structure in one query in mysql I have multiple tables "100+", with the exact similar structure, yes I know I probably should not have it this way but it is what it is now.

What I want to do is, update all tables in one query without having to specify the table to each field.

Like so:

update table1,table2,tableN set field1 = "value" WHERE id = 1;

This of course throws an error "field1 is ambiguous", so I could do it like so:

update table1,table2 set table1.field1 = "value", table2.field1 = "value" WHERE table1.id = 1 AND table2.id = 1;

While this works, I'd rather not have a query that has 100+ table names and 100+ "ands" in the condition statements, I don't even know what's the limit on this thing.

Assuming there is no other way to do it, would it be better performance-wise, to loop through tables names and perform 100 queries instead?

Thanks

The solution might look like:

CREATE PROCEDURE update_all_tables (IN id BIGINT, IN val VARCHAR(64))
BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done = 1;
    DECLARE cur CURSOR FOR SELECT table_name
                           FROM information_schema.tables
                           WHERE table_schema = 'MyDatabase'
                           AND table_name LIKE 'table%';
    OPEN cur;
    LOOP
        @done := 0;
        FETCH cur INTO @table;
        IF @done THEN LEAVE LOOP;
        SET @sql := CONCAT('UPDATE ',@table,' SET field1 = ? WHERE id = ?');
        PREPARE stmt FROM @sql;
        EXECUTE stmt USING val, id;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END;

PS. Don't forget DELIMITER statement...


Unmarshalling Hierarchical JSON in SQL Server 2016+ We are attempting to parse out data from a JSON file with hierarchical data. Right now, I am just trying to extract the inner array's data. The basic structure of the document is:

[
  {
    "url": "https://www.google.com",
    "image-url": "https://www.google.com/imghp",
    "labels": [
                {
                  "source": "Bob, Inc",
                  "name": "Whips",
                  "info": "Ouch"
                },
                {
                  "source": "Weezles of Oregon",
                  "name": "Chains",
                  "info": "Let me go"
                },
               ...
              ],
    "Fact": "Fictional"
  },
  ...
]

The final output required for this JSON object would be something like url, Fact, name, info (one row per label per object).

I can easily query the outer array of objects with OPENJSON() and JSON_VALUE(). However, I have not deduced how to extract the "labels" array so I can use OPENJSON on it to access the name and info fields. And that's where I need help.

If I can get the Labels array out, I can go from there. If I am missing something obvious, kick me an let me know what it is. But I've spent about five hours on this so far, and the solution isn't obvious (to me, at least)

In this case, I'd use a cross apply.

DECLARE @json NVARCHAR(MAX) = N'{"menu": {
    "header": "SVG Viewer",
    "items": [
        {"id": "Open"},
        {"id": "OpenNew", "label": "Open New"},
        null,
        {"id": "ZoomIn", "label": "Zoom In"},
        {"id": "ZoomOut", "label": "Zoom Out"},
        {"id": "OriginalView", "label": "Original View"},
        null,
        {"id": "Quality"},
        {"id": "Pause"},
        {"id": "Mute"},
        null,
        {"id": "Find", "label": "Find..."},
        {"id": "FindAgain", "label": "Find Again"},
        {"id": "Copy"},
        {"id": "CopyAgain", "label": "Copy Again"},
        {"id": "CopySVG", "label": "Copy SVG"},
        {"id": "ViewSVG", "label": "View SVG"},
        {"id": "ViewSource", "label": "View Source"},
        {"id": "SaveAs", "label": "Save As"},
        null,
        {"id": "Help"},
        {"id": "About", "label": "About Adobe CVG Viewer..."}
    ]
}}
'

  SELECT
     JSON_VALUE(a.value, '$.header') AS MenuItem
    , JSON_VALUE(b.value, '$.id') AS Id
    , JSON_VALUE(b.value, '$.label') AS Label
  FROM OPENJSON(@json) a
    CROSS APPLY OPENJSON(a.value, '$.items') b


Where are SYS Audit Entries in Windows Event Log? I have audit_sys_operations set to true, audit_trail set to DB, have restarted the database since setting them, and have logged in as and performed actions as sysdba. However, I cannot find these entries in the Windows Event Log Viewer. From what I read, they should be under Windows Logs->Application. When looking there, I see nothing with an event id of 34 or a Source containing Oracle.

Am I missing something, or is something perhaps not configured properly?

Audit files get written into the folder audit_file_dest points to.

select value from v$parameter where name='audit_file_dest';


MySQL 8.021 = 100% CPU when many UPDATE queries to 1 table I just install fresh LEMP stack at Freebsd 12 with MySQL 8.021
Noticing high usage of CPU
I tested my queries in normal conditions individual and every is about 0.2s execute time - which is absolutely fine for me, but...
My PHP script run VERY SIMPLE queries:

update set value=0 in client_table;
update set value=1 in client_table;
loop 10 queries - update set about 10 fields in client_table, each query = 1 specified row with WHERE clause;
(when I loop 100 queries its more than 60s and I got timeout because of cloudflare also... - their limit 90s) in general I dont want to exceed 60s because of cron
I use 10 instances of script by crontab every 1 minute

1 instance run: 20-25s
10 instance run: 40-50s EACH script so at least double
The fact is when I raise number of queries from 50 of each instance to 100 it getting almost 3 time more than 1 instance run.
mysql table has almost 30.000 records, about 20 fields tinyint - int, 5x datetime, 1 text max 64k chars - most of fields are empty now so definitely not much data there
- top
PID, USERNAME, THR PRI NICE   SIZE    RES STATE    C   TIME, WCPU COMMAND
67805 mysql,  60  20    0  4454M  1147M select   0   3:29 106.81% mysqld
ps. at most CPU is 20% user, because WCPU shows all cores usage (six)
so maybe it's not really big CPU usage, rather clogging when much queries to 1 table at same time?
I use Contabo VPS with 6 virtual cores and 16GB of RAM and a 400GB ssd disk
I think my script even with loops and all that stuff shouldnt be that much harmful for MySQL... I hear about 8.018 but from ports its 8.021 actually.. so all things should be fixxed.
my.cnf
Edit:
** The modified config I tried: (switched now for mysql 5.7 - but same situation so I doubt it's mysql VERSION issue) **
Maybe you have any performance tips for my config file to serve big more queries to 1 table in same time or maybe u experienced such problems?
My queries from script:
# 1 update
UPDATE products SET available_worker_id = 0
    WHERE available_worker_id = 1

# 2 update
UPDATE products SET available_worker_id = '1'
    WHERE check_available_date <= NOW()
      AND available_worker_id = 0
      AND product_id > 0
    ORDER BY check_available_date ASC LIMIT 100

# 3 update
SELECT product_id, next_check_avail FROM products
    WHERE available_worker_id = '1'
    ORDER BY check_available_date ASC LIMIT 100

while ($item = mysqli_fetch_assoc($result)) {
    $mysqli->query("UPDATE products SET shop1-shop10 = $TINY_INT, available_date = NOW(), check_available_date = '".date("Y-m-d H:i:s", time() + $item['next_check_avail'])."', available_worker_id = 0
    WHERE product_id = $product_id
    LIMIT 1")
}

** DEBUG SQL: (2 instances running at once) **
Max_used_connections    4
Max_used_connections_time   2020-10-27 19:03:02
Delayed_insert_threads  0
Performance_schema_thread_classes_lost  0
Performance_schema_thread_instances_lost    0
Slow_launch_threads 0
Threads_cached  3
Threads_connected   1
Threads_created 4
Threads_running 1
10 instances: Max_used_connections  11
Threads_connected   11
Threads_created 11
Threads_running 10

TABLE: https://pastebin.com/raw/qQPWR3mD

As a rule, Databases do not run slowly.  Queries do.

update set value=0 in client_table;

Look at the actual query (not the paraphrasing above).
Look at the "where clause" on that query.
Is it supported by indexes or is your database having to scan through the entire table to find the record you want to update?  That would easily account for the excessive CPU load (you may well find that the entire table is sitting in memory).
Running updates in a loop is also questionable.
Have you retrieved a list of record 'ID's and are now looping through them, making a change to each?
Relational DBMSs like to work on "sets" of rows that can extend to millions of rows.  "Sniping" at individual rows where you could process them all in a single, set-based, query will be slower.


SQL Server Management Studio DAC connection error I'm trying to connect to SQL Server 2012 via DAC using Management Studio. I type admin: before my server name, yet I keep getting the same error:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The remote computer refused the network connection.) (Microsoft SQL Server, Error: 1225)

What I've checked:

I can login with no problems when using regular connection(non DAC)
I can also login when forcing tcp: in connection string
user is a sysadmin (sa)
I am logging in from a local server (physical sql machine)
SQL Server browser is enabled
Remote connections are enabled and firewall was disabled
TcpDynamicPorts in regedit was set to 59036
I've executed this:

exec sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
exec sp_configure 'remote admin connections', 1
RECONFIGURE WITH OVERRIDE

So far, nothing has helped me.

TcpDynamicPorts in regedit was set to 59036

Your instance is named and is configured to use dynamic ports.
The default instance is listening on 1433 for all the connections and on 1434 for DAC, your port for DAC is still unknown but you can find it in error log:

xp_readerrorlog 0,1,N'Dedicated admin connection'

Then you need to check that it's open in your firewall and you can connect specifying directly this port:

myPCname,port


How to make CREATE INDEX in InnoDB table faster I have platform RHEL 5 with 8G RAM and MySQL version 14.12 distrib 5.0.86. I want to create an index in an InnoDB table, but it takes a long time:

Time | State             | Info
2291 | copy to tmp table | create index index_test

The table has 8,000,000 rows and in /etc/my.cnf

innodb_buffer_pool_size = 4G

but it is still too slow.

How can I make the create index in innodb table faster?

I already have these variables:

read_buffer_size = 2M
sort_buffer_size = 8M
tmp_table_size   = 64M

SHOW CREATE user_t\G

CREATE TABLE `user_t` (
  `t_id` mediumint(8) unsigned NOT NULL auto_increment,
  `user_id` varchar(50) NOT NULL default '',
  `event_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `user_A` double(11,2) NOT NULL,
  `txt1` varchar(150) default NULL,
  `txt2` varchar(250) default NULL,
  `txt3` varchar(250) default NULL,
  `txt4` varchar(250) default NULL,
  `txt5` varchar(250) default NULL,
  `inv_id` varchar(100) NOT NULL default '',
  `display_text` varchar(250) default NULL,
  `ip` varchar(15) default NULL,
  `testID` varchar(150) NOT NULL default '',
  `bad_t` enum('0','1') default '0',
  `t_id_m` mediumint(11) unsigned default NULL,
  `status` enum('open','pending','processed') NOT NULL default 'processed',
  `b` double(11,2) default NULL,
  `system_id` varchar(50) default NULL,
  `isfixed` int(1) default '0',
  `tmptxt1` varchar(150) default NULL,
  PRIMARY KEY  (`t_id`),
  KEY `undex1` (`testID`,`event_time`,`txt1`),
  KEY `index2` (`t_id_m`)
) ENGINE=InnoDB AUTO_INCREMENT=10260505 DEFAULT CHARSET=utf8

and the following the create index command that take a long time:

create index index_test on user_t (event_time);

RHEL5 ships with MySQL 5.0, unless you installed something else.  So I would focus on increasing these three variables.  They are not specific to either InnoDB or MyISAM.

read_buffer_size        = #of Mb
sort_buffer_size        = #of Mb
tmp_table_size          = #of Mb

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_read_buffer_size

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_sort_buffer_size

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_tmp_table_size

example :

read_buffer_size        = 16M
sort_buffer_size        = 64M
tmp_table_size          = 256M

Increasing the buffer_pool won't help you to speed up index creation. It is used for caching.

http://dev.mysql.com/doc/refman/5.0/en/innodb-buffer-pool.html

Also, if it were possible, I would also try to put the tmpdir on a faster disk.


How to handle null value number DataWarehouse I have a question.

I'm developing a Data Warehouse, I retrieve the data from my reconciled level, this in order to keep updated my Data Warehouse, but i am not sure how to handle some numeric fields that have a null value.

I was thinking to replace it with some special numeric value like -1 for example, that never appear in my column list. But during the analysis process this value replaced by -1 they must not be considered.

Now my question is, is better leave the null numeric field to null, or replace it with some numeric value?

If you don't have a thorough understanding on the business meaning of the data point, you should NEVER put in a dummy or made-up value.

If this is a fact table, you will need to work with the business teams to understand the scenarios in which the NULL can occur.

If the column represents a monetary value, using zero works in most cases, but again, understanding the data point is necessary here too.

If you determine with the business teams that NULL can/should be represented by something else e.g. $0, or -1, etc., I recommend working with the folks responsible for the source data, and trying to affect the change there if possible. Then the appropriate business values flow through to the DWH in your ETL.

If the business determines a field/data point can legitimately have no value, then let there be nulls!

If the column is an identifier e.g. a primary key to another dimension or reference table, then it is a different story. In your dimension or reference table, there would typically be a row representing 'no value', which would have an id of it's own... This key would go in your fct table.

Regardless of what you work out with your business teams... ALWAYS document it all in your data dictionary, so that users of the data (and your ETL/DWH developers) have a reference back to what it was done that way.

Side Note: I hear lots of arguments for not allowing nulls which relate to "oh, what if users writing queries don't know how to properly account for NULLs in joins or where clauses, etc... This is never a good reason to support a design decision at the data model/etl level. This issue is best addressed at the data presentation or reporting layer. E.G. some BI tools have built in handling for this or abstract it away, etc.


Question on Enabling Kerberos Authentication using SetSPN for Availability Listener alternative DNS name We have Availability Group, and original DNS name that was given to AG Listener, is too long (something like longername-Listener-01.mydomain.com); Its hard for us to change original DNS name of listener at this point

So we simply added new shorter DNS name into DNS server (shortername.mydomain.com), to point to listener's IP address

But the issue is that original long Listener's DNS name, was setup to use Kerberos authentication using Kerberos Configuration Manager tool;
Apparenly this tool simply run command

SetSPN -s "MSSQLSvc/longername-Listener-01.mydomain.com:1433" "MYDOMAIN\SQLSvcAccount"

So currently everyone who connect to original longer DNS name, are connecting to SQL Server using KERBEROS auth_scheme;
And everyone who connect to new shorter DNS name, connecting using NTLM auth_scheme - which we want to avoid

Question:

If we execute following command on Availability Group servers:

SetSPN -s "MSSQLSvc/shortername.mydomain.com:1433" "MYDOMAIN\SQLSvcAccount"

Would it make connections to "shortername.mydomain.com" use KERBEROS auth_scheme instead of NTLM ?
And connections to "longername-Listener-01.mydomain.com" would still be using KERBEROS auth_scheme, too ?

Yes, a SPN is simply telling the client that when it connects to the specified service (MSSQLSvc) on the specified host (shortername.mydomain.com), the specified principal (MYDOMAIN\SQLSvcAccount) will be the owner of the process that it is connecting to.  This ensures that the connection will fail if DNS is wrong and it ends up connecting to a process owned by a different principal.


MySQL InnoDB Cluster - Add instance issue with local port I have an issue to add instance to MySQL InnoDB Cluster using MySQL Shell. Here are the steps that I have done:

Login to one DB-01 node
Run command: mysqlsh
shell.connect('username@ip-address:port');
cluster = dba.createCluster('cluster-name');
Checking the cluster status: cluster.status(). Here is everything ok. I have one instance in the cluster.
cluster.addInstance('username@ip-address-of-db-02-node:port');
Getting an error.

MySQL log from DB-02 node contains the following errors:

[Repl] Plugin group_replication reported: '[GCS] Error connecting to all peers. Member join failed. Local port: 33061'
2020-12-26T14:40:29.234106Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'

It's same when I try to add DB-03 node into cluster. All servers are in the same VLAN and have been configured using mysql shell (there is same user and pass for all nodes). IP tables service is not enabled and SELinux is disabled.
When I tried telnet from DB-02 to DB-01 on port 33061, it's opened but when I run telnet from DB-01 to DB-02 then connection is refused.
Does anyone have an idea how to fix this issue?

Your IP ranges most probably don't fall in the range of IPs that would be automatically added to the allowlist: https://dev.mysql.com/doc/refman/8.0/en/group-replication-ip-address-permissions.html
For that reason, you must manually configure the allowlist: https://dev.mysql.com/doc/mysql-shell/8.0/en/configuring-innodb-cluster.html#create-allowlist-servers


Can additional indices ever aggravate lock contention? Not considering index creation: Can it ever be disadvantageous to create an index on a table with regards to lock acquisition?

I have experienced already some cases where a carefully selected index has enabled much higher parallelism in a database, as is expected by (and documented for) MySQL's use of index (gap) locking.

But is the opposite possible as well, i.e. two or more queries from different transactions that will not get in each other's way without some index, but which given an additional index will incur additional waiting on a lock?

My question is not limited to a single RDS implementation, but I am most interested about MySQL and Postgres.

Speaking for Postgres.

In default READ COMMITTED transaction isolation, indexes don't introduce the possibility for deadlocks where there wouldn't be without them - AFAIC. It's still possible to experience deadlocks only after creating a new index. Changed query plans can make lurking design problem surface as side effect, but not as immediate fault of the index.

Creating or deleting indexes is special, quoting the manual:

  An exclusive lock on the index as a whole will be taken only during
  index creation, destruction, or REINDEX.

And there are corner cases with SERIALIZABLE transaction isolation. See the last paragraph of chapter "Index Locking Considerations" in the manual. (Or read it all for details.)

But indexes can certainly aggravate lock contention in various ways. Obviously, additional indexes incur additional cost for write operations (indexes have to be maintained). There are other corner cases that might impair performance with a similar effect. Longer transactions mean more lock contention, since locks are only released at the end of transactions.

For large inserts or updates it can pay to delete unrelated indexes and recreate afterwards (in the same transaction) for better performance - but at the cost of an exclusive lock on the table. Example:

Best way to populate a new column in a large table?

Typically, though, if you only create indexes you actually need, the opposite is the case: shorter transactions, less lock contention.

Related:

Deadlock best practices


How to send big email via procedure sp_send_dbmail? I need to send emails via MS SQL server. But the problem is that I have too much information to send. Let's say 50-200Kb per one mail. This is HTML formatted reports.

For sending, I run sp_send_dbmail with the following parameters:

EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'profile',
        @recipients = 'mail@server.com',
        @subject = 'subject',
        @body = 'body',
        @body_format = 'HTML', -- or TEXT
        @query = 'select * from ##t',
        @attach_query_result_as_file = 1,
        @query_attachment_filename = '1.html';

I've used the following options:

Prepared HTML in @body NVARCHAR(MAX) variable. But it truncated to 8000 symbols.
Prepared HTML inside ##t:

CREATE TABLE ##t (ID INT IDENTITY(1,1), email NVARCHAR(MAX))

and then used @query = 'select * from ##t' in sp_send_dbmail procedure.

I can send a lot of rows, but all of them will be truncated to 256 symbols.

Even If I use @query_result_width = 32767 (what is the maximum limit) - I receive truncated columns.

I wish I could get round this "256-symbols-on-column" limitation. This will be the simplest method of sending emails for me.
Prepared HTML inside ##t and used SQL Job with CMD command inside:

bcp "SELECT html FROM ##t ORDER BY Id" queryout c:\temp\report.html -c -T

This is working approach, but not so simple as I want because now I should create some folder and bother about cleaning files inside it.
I can use PowerShell script which will be called by Windows Task Scheduler
This is not so simple too.

What can you suggest in this situation?
How can I send emails with minimum effort of development and security rights changing? I need a simple solution which could be installed on any Windows server.

Thank you in advance.

I would recommend dynamically building the body of the mail message itself using a query, instead of attaching the query results to the mail using sp_send_dbmail.

For example, something like:

DECLARE @BodyHtml nvarchar(max);

SET @BodyHtml = N'';
SELECT @BodyHtml = @BodyHtml + CONVERT(nvarchar(max), html) FROM ##t;

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'profile',
    @recipients = 'mail@server.com',
    @subject = 'subject',
    @body = @BodyHtml,
    @body_format = 'HTML';

This should send the entire HTML inside the e-mail body itself instead of as an attachment.

The @body parameter is defined as nvarchar(max), so it's likely that you're getting implicit conversions to varchar(8000) when you're building your html body, and that's why it was truncated at 8000 characters (unless, maybe, you're using a very old version of SQL?). This is why the CONVERT function is important while you're building your html body.


How do I attach a database in SQL Server? Some common questions relating to attaching databases in SQL Server:

  What does it mean to attach or detach a database?
  How do I detach a database?
  How do I attach a database?

  What does it mean to attach and rebuild the log?

  How do I do it in SQL Server Express?
  When might I consider detaching and attaching?
  Are there any risks or warnings?
  What about attaching between versions and editions of SQL Server? (Standard to Enterprise? 2000 to 2008? 2012 to 2008?)

What Is Detach or Attach and How do They Work?

We'll start with detach. When you detach a database in SQL Server, you are taking the database offline and removing it from the SQL Server instance from which you are detaching it. The databases data and log files remain in tact and are left in a consistent state so you can then attach the database at a later point or to another SQL Server instance. Attach connects the data and log files from a database that has been properly detached (or that were copied from a cleanly shut down instance of SQL Server) to an instance of SQL Server and brings the database online.

How Do I Detach a Database?

You can do this in T-SQL or from the SQL Server Management Studio GUI.

In the GUI, you right click on the database you wish to detach, select All Tasks and click on Detach. From there you'll get the detach dialog.  You can choose to drop connections first to forcibly disconnect any active connections and rollback work they were in the middle of executing. You can also choose to update statistics before the detach.

In T-SQL:

-- You don't want to be in the database you are trying to detach
USE Master
GO

-- Optional step to drop all active connections and roll back their work
ALTER DATABASE DatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- Perform the detach
EXEC sp_detach_db 'DatabaseName'
GO

For the system stored procedure sp_detach_db there are two paramaters that you can pass in optionally:

@skipchecks - acceptable input is 'True' or 'False' if 'True', SQL Server will update statistics before detach. If 'False', it won't. If you don't specify anything here the statistics will be updated in SQL Server 2005 or later.

@keepfulltextindexfile - The default here is 'True' - if this is set to true, the full text index metadata will not be dropped during the detach.

To see a lot more about detach and some more details on the risks I highlight below, the Books Online article for sp_detach_db is a good place to start.

How Do I Attach a Database?

You can also do this in T-SQL or from the SQL Server Management Studio GUI.

(NOTE: If you have the data and log files from a database that was not properly detached, your attach may not work. When detach occurs, the database is brought offline and the log and data files are put into a consistent state. This also happens when a service is cleanly shut down.)

In the GUI, you right click on the top level Databases folder for your instance and select Attach. In the next dialog you would then select the primary data file (.MDF) of the database you wish to attach and ensure you have the other files selected and their appropriate locations specified, and click OK, attaching your database.

In T-SQL the best way to do this in SQL Server 2005 and forward is through the CREATE DATABASE command. This is the method that is supported beyond SQL Server 2012. If you want to see how to use sp_attach_db, you can see that in the books online articles for [sp_attach_db][3] or [sp_attach_single_file_db][4]

When you have your log file and data files available and they are consistent this is the T-SQL approach:

-- Using Create Database and the FOR ATTACH clause to attach

CREATE DATABASE DatabaseName
    ON (FILENAME = 'FilePath\FileName.mdf'), -- Main Data File .mdf
    (FILENAME = 'FilePath\LogFileName.ldf'), -- Log file .ldf
     (FILENAME = 'FilePath\SecondaryDataFile.ndf)  -- Optional - any secondary data files
    FOR ATTACH
GO

You can see more about the Create Database statement in books online as well.

How Do I Detach/Attach in SQL Server Express?

It's actually the same. If you are using SQL Server Management Studio Express you can use the detach/attach dialog in the GUI described above or the T-SQL steps through SSMS Express described above as well. No difference with Express there.

If you don't have SSMS Express, you can download it (Here is the SQL Server 2012 Express version).

Of you can enter into a SQLCMD session and use the same T-SQL constructs described above.

When Should I Consider Doing a Detach or Attach?

First a word on what detach and attach is not meant to be used for: Backup and Recovery Detach and Attach is not a way to backup your database for routine recovery purposes. There are no transaction log backups this way, it puts your database into a state where the database files can be deleted accidentally and is not a good way at all for this purpose.

That said, detach and attach are good for a few use cases (not exhaustive, feel free to edit to add or create a new answer with more):

Sometimes for migrations (although I prefer backup/restore for those as discussed in my answer here)
When you want to remove a database that is no longer actively used but have the ability to attach later as needed.
In certain troubleshooting situations, this may be called upon
Don't have the space to backup or to restore both a data and log files to another environment (you shouldn't ever be here but I've used it to move dev databases around environments at times.. Didn't want or need the log so did an attach/rebuild of the log file)

Risks and Warnings

Again, books online is a good resource here, but I'll call out some specific considerations to have in mind with detaching or attaching a database -

Detach

You are taking your database offline. It won't be accessible anymore. This should be obvious, but worth calling out. This is why it isn't a great backup option.
When your database is online, SQL Server locks the files. I wouldn't recommend trying this to prove me wrong, because there could be some other situation at play, but you typically can't delete a database file (data, secondary data or log file) while SQL Server is online. This is a good thing. When you detach, you have no such protection - this can be a bad thing.
If you are dealing with database corruption and you find some article someplace that has a first step of Detach - it's wrong - if you detach a corrupt database, that may be it. You may not be attaching that database again.
Cutting and pasting your production database files throughout your network is a way to potentially introduce file level corruption.. Another reason I prefer backup/restore when doing migrations.
It might cause a maintenance plan to fail.  The situation is that you have, as I did, set up a maintenance plan to carry out regular backups of all databases without checking best practice. This works fine so you stop thinking about it. Someone else then decides to take a database they're not using offline. The maintenance plan will fail from that point forwards until you modify the maintenance plan by checking the "ignore databases whose state is not online" option in the "Database(s)" dialog. Note that it won't just fail for the offline database - the maintenance plan will fail with an error at the point when it tries to backup the offline database so some online databases might not be backed up.  (different author for this point so treat with suspicion)

Attach
 - Just like you shouldn't run scripts from the internet or accept packages from strangers at the airport, you shouldn't attach a database you got from someone else without some steps to verify it. This database could have code inside of it in triggers, stored procedures, etc. that could compromise your environment. You should review a database you want to attach in a safe, and firewalled environment, not your production system.

What About Different Versions or Editions of SQL Server?

These are no different than the rules around restoring databases between versions. You can generally restore up to the next version for 3 versions (SQL Server 2008 to SQL Server 2012, for example will work. SQL Server 2000 to SQL Server 2012 will not). You cannot go backwards at all via backup/restore or detach/attach - you'd have to script out objects and script out the inserts and do it manually or with a tool that does this. For editions, you can generally move between the main SKUs of SQL Server - for instance you can move a database from Standard to Enterprise with no extra work. If you are using Enterprise features (Say, compression or partitioning), you'll need to disable those features before you make the move, though. You can get an idea of the features you'd need to consider disable by looking here.


Update single table with mutually exclusive threads I have 2 threads (for example 2 different sql sessions) reading rows of the same table in parallel. They will do some processing and update a column to inform the row has been processed.

Thread id:88 will read batch of 5  rows and write into Thread variable
Thread id:99 will read batch of 10 rows and write into Thread2 variable

(So I can see if the processing does overlap)

This is the table structure:

And the database has a READ COMMITTED SNAPSHOT isolation level with row versioning. Version 2012.

The execution will be as following:

Thread 88 will read 1 to 5 rows (that have not been processed or not being processed), lock them and update the thread column to 88 (to inform the thread 88 processed it)

Then (or simultaneously), thread 99 will start and fetch next 10 records And it will pick 6 to 15 (as 1..5 are processed or currently being processed by thread 88).

This is the stored procedure I am using for thread 88:

DECLARE @Thread INT = 88
DECLARE @Continue INT = 1000

DECLARE @Id int
DECLARE @InUse INT

while (@Continue > 0)
BEGIN
    DECLARE MY_CURSOR CURSOR
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR
        SELECT TOP 5 Id
        FROM Names WITH (READPAST, UPDLOCK)
        WHERE inUse = 0
        ORDER BY 1

    BEGIN TRAN
        OPEN MY_CURSOR

        FETCH NEXT FROM MY_CURSOR INTO @Id

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @InUse = InUse FROM Names where id = @Id

            IF @InUse = 0
            BEGIN
                UPDATE Names
                SET InUse = 1, Thread = @Thread
                WHERE id = @Id
            END

            FETCH NEXT FROM MY_CURSOR INTO @Id
        END
        CLOSE MY_CURSOR
        DEALLOCATE MY_CURSOR

        IF @Id IS NULL
            SET @Continue = 0

    COMMIT

    WAITFOR DELAY '00:00:00:90';
    SET @Continue = @Continue - 1
END

And this for thread 99 (They are the same, just changing number of records to process and the column to inform the thread):

DECLARE @Thread INT = 99
DECLARE @Continue INT = 1000

DECLARE @Id int
DECLARE @InUse INT

WHILE (@Continue > 0)
BEGIN
    DECLARE MY_CURSOR CURSOR
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR
        SELECT TOP 10 Id
        FROM Names WITH (READPAST, UPDLOCK)
        WHERE inUse = 0
        ORDER BY 1

    BEGIN TRAN
        OPEN MY_CURSOR

        FETCH NEXT FROM MY_CURSOR INTO @Id

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @InUse = InUse
            FROM Names
            WHERE id = @Id

            IF @InUse = 0
            BEGIN
                UPDATE Names
                SET InUse = 1, Thread2 = @Thread
                WHERE id = @Id
            END

            FETCH NEXT FROM MY_CURSOR INTO @Id
        END

        CLOSE MY_CURSOR
        DEALLOCATE MY_CURSOR

        IF @Id IS NULL
            SET @Continue = 0
    COMMIT

    WAITFOR DELAY '00:00:00:90';
    SET @Continue = @Continue - 1
END

When running both simultaneously this is the output:

The batches are gone, and I don't have more 5 and 10 together. For example, thread 88 has a random number of continuous rows processed)

Now the questions:

Is ok to use (READPAST, UPDLOCK) where inUse = 0 to read mutually exclusive records?
If 1 is true, why it starts good and then the batches are gone...?
Anyway, It seems the threads are not overwriting each other as there is no row with Thread 1 and Tread 2 with same Thead id. Is this code good to  read mutually exclusive records?

Thanks!

Yes, it's ok to use (READPAST, UPDLOCK) where inUse = 0.

UPDLOCK hint will force SQL Server to use (U) locks instead of (S) locks and (U) locks are incompatible with each other, so the two threads will not be able to fetch the same row.

READPAST hint will force SQL Server to skip the locked rows rather than being blocked.

The batches are not "gone". They are just not always consist of records with consecutive IDs.

To illustrate this , let's add a FetchTime column to the table and modify the relevant parts of the procedure:

      DECLARE MY_CURSOR CURSOR
         LOCAL STATIC READ_ONLY FORWARD_ONLY
         FOR
         SELECT top X Id,GETUTCDATE() as FetchTime
         FROM Names WITH (READPAST, UPDLOCK)
         where inUse = 0
         order by 1

.
.
.

  OPEN MY_CURSOR
        FETCH NEXT FROM MY_CURSOR INTO @Id,@Fetchtime

.
.
.

 update Names set InUse = 1, Thread = @Thread,FetchTime=@FetchTime where id = @Id

All the rows in a batch will have the same value in the FetchTime column.

Now it is more evident that the batches are not "gone" :

Yes. In fact, this approach is more efficient than synchronizing access to the table by implementing a critical section or using a mutex because the two threads can read the data in parallel without blocking each other.


Switching from a SQL Server 2016 Failover Cluster to Availability Groups The DBA role fell into my lap and now I was asked to investigate Always On Availability Groups.

So, in the Availability Group environment, do all servers need to have the same specifications?
Say I have Server A with a 20 core enterprise environment, do servers B & C have to have 20 Core each?
How does Disaster Recovery work?
Currently I have a mirror to an offsite with a decent connection, do I have to up the connection bandwidth? Is the DR site part of the Availability Group?
I was under the impression (probably 100% wrong), that if the db is in recovery mode, not accessible, you don’t have to pay for the SQL Server licenses. Now with Always On sounds like you are forced to get the licenses even if you are not accessing the server.
Using Availability Groups, can I create a primary server for one group and set a different primary server for another AG, is that possible?

Do you need to have the same server configuration for both nodes?

No, this is not required, however it is a best practice. In the event that your secondary replica has fewer cores and less memory then you would see a degradation in performance when failed over to the lower powered machine. This might still give you sufficient performance, only you can make that decision, but it is not a requirement.

One thing I would recommend is to always ensure that you keep the drive letters and paths the same on all machines in an AG, otherwise you'll run into a lot of problems attempting to add data or log files.

  How does Disaster Recovery work?

You will need to license your secondary if you allow read intent routing. That is something that gets configured at the AG level. But ALWAYS check with a licensing representative to ensure that you are compliant.

You should not need additional bandwidth for AG traffic if you are already using mirroring. Packets are compressed prior to sending between machines (when running in asynchronous commit mode).

  Can I create a primary for one AG and a different AG for another?

Yes.

The limitation here is that a database can only reside within a single AG, and that all nodes must be in the same WSFC. Be aware though (going back to your licensing question) that if a server is a primary for databases then it must be appropriately licensed.


mysql event running at wrong time I created an event to run every 24 hours and be run at 14h. Its selects some data from tabels and writes it as to a file (csv format)

The query and writing to file goes ok. The event is triggered.

But (there is always a but) the event is triggered at different intervals.

It runs at 21h and 18h and sometimes at 0h. It seems it runs at will.

Am I doing something wrong?
Is there a setting I need to change?

this is the event

'def', 'x54_pro_dbo', 'export_timepro_to_genius', 'notes@%', 'SYSTEM', 'SQL', 'call     export_timepro_to_genius', 'RECURRING', NULL, '24', 'HOUR', 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION', '2012-01-26 14:00:00', '2036-01-26 00:00:00', 'ENABLED', 'PRESERVE', '2012-01-26 12:51:43', '2012-01-26 12:51:43', '2012-01-29 21:00:01', ' export timepro to genius', '0', 'utf8', 'utf8_general_ci', 'latin1_swedish_ci'

This is how I created the event

drop event if exists export_timepro_to_genius;
  CREATE EVENT IF NOT EXISTS  export_timepro_to_genius
   on  SCHEDULE EVERY 24 HOUR STARTS '2012-01-27 14:00:00'
   on completion preserve
   comment ' export timepro to genius'
 do  call export_timepro_to_genius  ;

I'm running mysql 5.5.6-rc

This is is from the .err log. So you can see that the query is runned at 14h and creates the file and then it is run again at 21h and gives an error because it cannot overwrite the file (witch is not the problem, the problem is that the event is triggerd at 21h)

120127 21:00:00 [ERROR] Event Scheduler: [notes@%][x54_pro_dbo.export_timepro_to_genius] File 'c:/tmp/TimePro20120127.csv' already exists
120127 21:00:00 [Note] Event Scheduler: [notes@%].[x54_pro_dbo.export_timepro_to_genius] event execution failed.
120127 21:07:14 [ERROR] Event Scheduler: [notes@%][x54_pro_dbo.export_timepro_to_genius] File 'c:/tmp/TimePro20120127.csv' already exists
120127 21:07:14 [Note] Event Scheduler: [notes@%].[x54_pro_dbo.export_timepro_to_genius] event execution failed.
120128 18:07:14 [ERROR] Event Scheduler: [notes@%][x54_pro_dbo.export_timepro_to_genius] File 'c:/tmp/TimePro20120128.csv' already exists
120128 18:07:14 [Note] Event Scheduler: [notes@%].[x54_pro_dbo.export_timepro_to_genius] event execution failed.
120128 21:00:00 [ERROR] Event Scheduler: [notes@%][x54_pro_dbo.export_timepro_to_genius] File 'c:/tmp/TimePro20120128.csv' already exists
120128 21:00:00 [Note] Event Scheduler: [notes@%].[x54_pro_dbo.export_timepro_to_genius] event execution failed.
120129 15:07:16 [ERROR] Event Scheduler: [notes@%][x54_pro_dbo.export_timepro_to_genius] File 'c:/tmp/TimePro20120129.csv' already exists
120129 15:07:16 [Note] Event Scheduler: [notes@%].[x54_pro_dbo.export_timepro_to_genius] event execution failed.
120129 21:00:01 [ERROR] Event Scheduler: [notes@%][x54_pro_dbo.export_timepro_to_genius] File 'c:/tmp/TimePro20120129.csv' already exists
120129 21:00:01 [Note] Event Scheduler: [notes@%].[x54_pro_dbo.export_timepro_to_genius] event execution failed

The first GA version of MySQL 5.5.8. You are using MySQL 5.5.6-rc

You need to get the latest version of MySQL 5.5


SQL Server 2008 R2 High memory usage I have SQL Server 2008 R2 that runs multiple jobs over night, the problem is SQL Server will eat up all RAM and CPU, then it will not release the RAM resources after the job finishes, as a workaround this problem, I run a schedule task for windows to kill the SQL process (taskkill /im sqlservr.exe /f)

Would anyone know a better way to release RAM?

Below is the SQL server info:

Microsoft SQL Server Management Studio      10.50.1600.1
Microsoft Analysis Services Client Tools    10.50.1600.1
Microsoft Data Access Components (MDAC)     6.0.6002.18005
Microsoft MSXML                             3.0 5.0 6.0
Microsoft Internet Explorer                 9.0.8112.16421
Microsoft .NET Framework                    2.0.50727.4216
Operating System                            6.0.6002

Per your comment:

  Max RAM=2147483647 (24GB) AWE not enabled

The Max Memory setting is in MB, not bytes. The above setting (the default) is 2,048 TB (i.e., unlimited).

For a box with 24 GB of physical memory with a single instance of SQL Server, a good starting point for Max Memory would be 20480 (20 GB).

You may need to reserve more memory for the operating system or other applications. Note also that on SQL 2008 R2, memory allocated for the procedure cache is not included in the Max Memory setting, so generally you have to take a conservative educated guess (see above) and tweak from there.

How do you know if you guessed right? In addition to other PerfMon counters, I strongly recommend monitoring (recording) the Memory\Available MBytes counter to make sure it's >= ~500 MB at all times.

Finally, changing the memory settings in the server properties flushes the procedure cache -- I normally recommend making this change during an off-peak time, as this will create extra CPU load.


How do I combine rows in sql query and remove duplicates I have a large database that is connected to a forms mapping program. In order for me to map results from SQL queries I have to put a single line field in for each result row. It defaults to row 0 and if there are more than one result I need to manual change the index of the field to 1,2,3,4 etc... The current data I am trying to query is for a table that looks like this:

+---------------+----------------+---------------+----------------+
|     Name      | Account Number | Accout Role 1 | Account Role 2 |
+---------------+----------------+---------------+----------------+
| John Smith    |          12345 | TaxSigner     |                |
| John Smith    |          12345 |               | TaxOwner       |
| Tim Anderson  |         678920 | TaxSigner     |                |
| Tim Anderson  |         678920 |               | TaxOwner       |
| Bill Williams |         510301 | TaxSigner     |                |
| Bill Williams |         510301 |               | TaxOwner       |
| Frank Stevens |         897090 | TaxSigner     |                |
+---------------+----------------+---------------+----------------+

But I need it to look like this:

+---------------+----------------+---------------+----------------+
|     Name      | Account Number | Accout Role 1 | Account Role 2 |
+---------------+----------------+---------------+----------------+
| John Smith    |          12345 | TaxSigner     | TaxOwner       |
| Tim Anderson  |         678920 | TaxSigner     | TaxOwner       |
| Bill Williams |         510301 | TaxSigner     | TaxOwner       |
| Frank Stevens |         897090 | TaxSigner     |                |
+---------------+----------------+---------------+----------------+

There could be some customers with a single role while others could have both. I am able to merge the duplicate rows into a single column but that won't work. I need each role to end up in a separate column.

SELECT `Name`
     , `Account Number`
     , MAX(`Accout Role 1`) `Accout Role 1`
     , MAX(`Account Role 2`) `Accout Role 2`
FROM MyTable
GROUP BY `Account Number` -- , `Name`


Is it safe to change the Recovery Model of SSRS database ReportServer to SIMPLE? I'm changing the recovery model of ReportServer database from FULL to SIMPLE.

Is there any reason why i would not want to do this? Besides the obvious answer of losing point-in-time recovery? (i am not doing transaction log backups on this database anyways.)

If you're not using transaction log backups, then you don't need FULL recovery mode. It's that simple.


TempDB Contention with 24 physical tempdb files on 48 core AMD Opteron box I just received the following notification about an instance of SQL Server 2005.  The instance has a core-to-tempdb-file ratio of 2:1 for a total tempdb file count of 24 files.  Contention should not be happening - how would I detect the source of this contention?  TempDB and all other databases are on SAN storage via 10 GB Ethernet.  The SAN has 46 10k SAS drives configured in one RAID-60 array.  This array is shared with several VMWare servers and an Exchange server or two.

Notification from Idera SQL Diagnostic Manager:

11/8/2012 10:49:00 PM, Tempdb Contention (ms) on MGSQL01 is Critical.

Tempdb latch contention has been detected on MGSQL01. The total wait time detected is 1782 milliseconds. This is an indication that performance is being impacted by contention on allocation maps in tempdb. If this is a regular problem it may be alleviated by following best practices with respect to tempdb file count, size, and IO subsystem.

PFS Wait Time: 1782 ms GAM Wait Time: 0 ms SGAM Wait Time: 0 ms

Tempdb Contention (ms): Current wait time for tempdb allocation maps (GAM, SGAM, and PFS), in milliseconds. This alert may only be raised on instances running SQL 2005 or above.

Trace flag 1118 forces uniform extent allocations instead of mixed page allocations.   The trace flag is commonly used to assist in TEMPDB scalability by avoiding SGAM and other allocation contention points. If you have SQL Server 2008 or SQL Server 2005 and the fix applied and are still encountering TEMPDB contention consider trace flag 1118 for further assistance in resolving the contention.


Reverting Compatibility Mode 120 to 100 We recently upgraded the compatibility level (CL) of our production databases from 100 to 120.

Within a couple of days, users started complaining of increased wait times and general slowness. It was decided to revert the CL back down to 100. While things did get better, query times did not go back to historical averages.

We then cleared the plan cache to remove any 120 query plans still hanging around, and restarted the SQL Server service in configuration tools. After all this, our average times are still noticeably higher, though significantly better than when we initially switched to CL 120.

Is there anything else I can do, or is there a step I missed, to revert back to the old Cardinality Estimator?

I don't think this had any impact, but part of the same changes that we made was to enable CLR with the CL upgrade, and to disable it again when trying to revert back.

We are on SQL Server 2014 Service Pack 1, no CUs yet. We have a limited update schedule. CU 6 is scheduled for next month. We update stats and check for indexes to be rebuilt nightly.

Is there anything else I can do, or is there a step I missed, to revert back to the old Cardinality Estimator?

In SQL Server 2014, reverting to OLD CE can be done in 2 ways

Setting the database compatibility mode to less than 120.
(or) Enabling trace flag 9481 globally or at query level using option querytraceon

Now that you have reverted to old CE, your next steps in troubleshooting are finding out/isolating queries that are poorly performing - using XEvents or server side trace.

You should tune those queries.

My Suggestions :

Dont revert to old CE, instead isolate poor performing queries and tune them. Use option (querytraceon 9481) as query hint if you are not able to tune and want specific queries to use the OLD CE.
Since you are on SQL Server 2014 + SP1, you should enable trace flag 4199.

Also refer to

Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads


Reduce MDF file size I am working with a 500 GB SQL Server database. I need to reduce the size of the database data file after removing a large amount (50%) of the data.

I tried re indexing, it's good the database has de-fragmented. But the DB size is not reduced.

I am not prepared to use shrink database. I have already tried compression and DBCC UpdateUsage but no space gain. Please advise me any other ways to reduce the database size.

Our client will not allow us to take the DB Offline. It might take 5 hr to shrink the database and then re indexing will take 3 hrs, so in total we would need 8 hr down time. I am looking for a solution without downtime.

Step 0) Shrink disclaimer

I'm not even going to say it. I'll let Brent Ozar tell you to Stop Shrinking Your Database Files. Seriously. Now.

In your comments, you mention you deleted 50% of the data in the database, so you very likely have a good use case for a one-time database shrink. (Assuming that the deleted data isn't going to come back.)

I'm not even going to touch on shrinking logs. We're just talking data files.

Step 1) Look at file size

Before actually thinking about shrinking a file, look at file size and how much free space there is. Run this in the database you want to shrink data files for:

SELECT
     LogicalName = dbf.name
    ,FileType = dbf.type_desc
    ,FilegroupName = fg.name
    ,PhysicalFileLocation = dbf.physical_name
    ,FileSizeMB = CONVERT(DECIMAL(10,2),dbf.size/128.0)
    ,UsedSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0
           - ((dbf.size/128.0) - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0))
    ,FreeSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0
           - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0)
FROM sys.database_files dbf
LEFT JOIN sys.filegroups fg ON dbf.data_space_id = fg.data_space_id
ORDER BY dbf.type DESC, dbf.name;

If you look at the FreeSpaceMB column in your results, that is the amount of unused space you might potentially be able to reclaim. If it's significant, you might decide to move on and shrink the files.

Step 2) Consider the side effects

If you shrink the data file, any data located at the "end" of the file needs to be related elsewhere in the file. These operations are logged, so you're going to generate a lot of transaction log usage (Unless your database is in SIMPLE recovery). Make sure your transaction log is big enough, and make sure you are running transaction log backups frequently enough.

If your database is being mirrored, or is in an Availability Group, or is log shipping, you might find that your secondary server(s) fall behind during this operation.

Shrinking is going to create a lot of fragmentation. Depending on your workload, you might have a performance impact during the process.

Shrinking generates a bunch of IO. If you are already seeing IO issues, that might be exacerbated by shrinking, and you might have performance impact during the process.

Shrinking is a mostly online process, where data is moved page-by-page to the "front" of the file so that the end of the file can be truncated. I say mostly online because its possible for this operation to cause blocking--I've seen this particularly often in the PRIMARY filegroup. Because of the possibility of this causing blocking, I never let it run unattended, and never during peak business hours.

Be really sure that you need to shrink.

Step 3) Shrink files

I always use DBCC SHRINKFILE(BOL) when I shrink, never DBCC SHRINKDATABASE. If I am going to shrink a file, I want as much control over the process as possible.

Using the output from the above SELECT, figure out what you want to use for a target size on your database--You'll want to leave some free space in your data file, otherwise it's just going to grow bigger right away.

Now you can build the statement(s) to shrink your data file. Remember, this is an online process, but if you're worried about impacting performance, watch yoru server carefully while this runs:

USE [DatabaseName];
DBCC SHRINKFILE(LogicalName, TargetSize);

Step 4) Review fragmentation

Congratulations, you've successfully created a ton of physical fragmentation. I know some shops run applications where fragmentation just doesn't cause any problems. If you're one of those shops, then you're done--stop reading.

If fragmentation does affect your performance, then you already have a regularly scheduled index maintenance job. After shrinking the data file, run that regular index maintenance job to get things squared away.

If you don't have a regular index maintenance job, and thing you need one: figure out what the best options are, and if you even need to consider it. Kendra Little gives a good intro to the topic here. I personally like Ola Hallengren's IndexOptimize procedure.

Remember: Even if you're running Standard Edition, REORGANIZE is an online operation, and if you kill it, you won't lose your work--it will pick up where it left off the next time.

It can be done completely online
You can use DBCC SHRINKFILE and REORGANIZE to do this completely online. If the shrink causes massive fragmentation that impacts production, you can shrink/reorganize/shrink/reorganize in smaller chunks. This will take a really, really long time. But that's a trade-off... There are faster methods, but those will likely involve varying amounts of downtime.

The easiest solution is to just leave the database half-full and accept that you're using more disk space than you need to.


Is there a negative effect if I place an index on every column in a wide table? We have a wide dataset that isn't very tall (say 250K rows, 90 columns). We will be providing users with an interface that allows them to generate arbitrary queries against this dataset. They can select any number of columns, rows; they can filter by, group by, sort by and aggregate by any columns they want.

This table is actually 'precomputed' from a set of relational tables; this precomputed tables is 90 columns wide and will be used solely for the users' queries. This big flat table will be produced just once a day.

Because we have no idea in advance what they will actually be doing these operations against, we were suggested to place an index on every column of the table.

My question is, would this have any adverse effects?

We would also want this 'methodology' to extend to future datasets that we provide similar functionality on; 1.7 million rows with 120 columns

For reference, we are using SQL Server 2008 R2.

It sounds like this is more of a DataWarehouse workload than an OLTP.
In that case, heavily indexed tables are normal.

Keep in mind a couple of things, when you do your nightly loads, drop the indexes first then reindex after. Also, there will be a higher storage cost but generally this wont be a problem (but be aware of it).

I know your using 2008R2 but FYI, I believe the new columnstore indexes in 2012 shine against this type of workload. If you can't get the performance you want out of 2008R2 consider testing with this index technology on 2012.

Finally, you could monitor the index usage for the first few days/weeks There are missing index scripts and unused index scripts, that can be run on this table once the user query pattern is determined.


Error ERROR 1153 (08S01) during mysqldump restoration on Amazon RDS I am trying to import a table on an Amazon RDS server using mysqldump. During restoration, i get the following error message:

ERROR 1153 (08S01) at line 925: Got a packet bigger than 'max_allowed_packet' bytes

I then add --max_allowed_packet=128M to the restoration command, and now get the following error:

ERROR 2006 (HY000) at line 925: MySQL server has gone away

Here is the mysqldump restoration command:

mysql --max_allowed_packet=128M -uroot -p -h db-name.123456789012.us-east-1.rds.amazonaws.com dbname < /srv/tmp/dbname.sql

And when i try to set the max_allowed_packet and net_buffer_length globally during the current session without having to restart the RDB instance, i get the following error:

ERROR 1227 (42000) at line 1: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Attempt to add 'SUPER' privilege to the user also fails.

In order to change a dynamic option in Amazon RDS, you will have to modify the DB Parameter associated with the DB Instance.

Edit the option max_allowed_packet value, setting it to 1073741824.

Once you save it, it should be applied immediately since it is a dynamic option.

Afterwards, you can verify the change by logging into the RDS instance and running

SELECT @@global.max_allowed_packet;

Once verified, you may proceed to load your mysqldump.


How can I manipulate database in stack n-up order? I need to print a mail merge 3-up.  The database ( Micorsoft Access ) is in order of ID 1 to 1845.  Instead of printing sheet#1 (ID 1, 2, 3)  I'd like to print sheet#1 (ID 1, 616, 1231) and so on.  The purpose is to stack each pile after I cut it and then it's in order.  I don't want to end up with 3 piles in order.
How can I set up my database in ID order to print every 615th record starting from ID #1?

Database should look like:

**ID**      **Address**
1       123 Sample Street
616     987 Hello Road
1231    456 Print Avenue
2       999 Access Parkway
617     111 Boogie Court
1232    555 Database Sreet

I'm not sure what the modulo function in Access is, but if I were to write an SQL query I'd use this to order the records:

...order by case mod(id,615) when 0 then 615 else mod(id, 615) end, id


Joining unrelated data I have two tables. Each table has a single column with 2 records. How would I go about merging the columns from both tables into another?

For instance, the first table has records 1 and 2, and the other 4,5. I would like the new table to look like the following.

Here's an option that uses ROW_NUMBER to create a join based on the order of the ID column from each table.  Naturally, this assumes an equal number of rows in each table.

--demo setup
Declare @T1 table (id int)
Declare @T2 table (id int)

insert into @T1(id) values(1),(2)
insert into @T2(id) values(4),(5)

--solution
select a.id, b.id from
(
select id, ROW_NUMBER() over(order by id) as rn from @T1
) a
join
(
select id, ROW_NUMBER() over(order by id) as rn from @T2
) b
on b.rn = a.rn

id  id
1   4
2   5


How to create a table with multiple data type like char short int string in mysql? Now I have two way to do this as below, but I do not know which is better and is there some better way?

CREATE TABLE `params_value1` (
  `params_id` int(11) NOT NULL AUTO_INCREMENT,
  `params_value_int8` tinyint(4) DEFAULT NULL,
  `params_value_int16` smallint(1) DEFAULT NULL,
  `params_value_int32` int(11) DEFAULT NULL,
  `params_value_string` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`params_id`),
  UNIQUE KEY `params_id_UNIQUE` (`params_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

OR

CREATE TABLE `params_value2` (
  `params_id` int(11) NOT NULL AUTO_INCREMENT,
  `params_value` varchar(32) NOT NULL,
  PRIMARY KEY (`params_id`),
  UNIQUE KEY `params_id_UNIQUE` (`params_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The follow picture is main tab used:

Entity-Attribute-Value is the nightmare you are embarking on.  See the additional tag I added.  See a similar tag in stackoverflow.com; there is a lot more discussion there.

It is traditional to have another column in the Attribute table:  entity_id, attribute, value.  Your question applies to how to store value; that is only part of the nightmare.

In the cases of integers, simply make it BIGINT; that will hold the various sizes (with a little wasted space), and simplify your code some.


Table does not exist even if there is one I have MySQL master-slave replication setup in Amazon EC2. For the sake of resilience, I have set up LVM on RAID 10 array, on both master as well as slave. I have been using  ec2-consistent-snapshot  for some time now to take the snapshot from the slave instance.

So, I tried to create the disks from those snapshots and attach them to a new MySQL installation. Since the new installation would not recognise the LVM and RAID setup, I reactivated the LVM and RAID in the new instance using the following commands.

Scan the disks for RAID array members:

mdadm --examine --scan <comprising disks>

Reactivate the RAID array:

mdadm -A -s

Reactivate the lvm:

vgchange -ay

Since snapshotting took the backup of the ibdata and iblogfiles, I removed them for the fresh installation. MySQL would start normal.

Help me out of this

Removed ibdata1? You can* remove iblogfiles but not the ibdata1!!
They're system tablespaces storing tables, indexes, metadata, undologs....

*=> Removing iblogs is okay once you stop mysql but not while it's running!

Update: Removing iblogs is okay only after shutting down mysql with innodb_fast_shutdown disabled.

You might like to review this answer to understand what is wrong! You might want to do a restore from the snapshot may be.


why sql server has high Worker threads? Product : SQL Server 2008 (SP4)

CPU : 64

64-Bit system

Worker thread configuration = 0

SCOM Alerts : SQL DB 2008 Engine Thread Count is too high

After receiving this alert from SCOM i have queried [sys.dm_os_sys_info] and
[sys.dm_os_threads] to check the Total number of threads \ used \ available and found them as follow :

Total    ---     Used   ---     Free    ---    Used Percentage

1472     ---    1260    ---    212      -----      85 %

this server is used for online transactions processing. Is it normal that the Used threads are always between 1100 and 1300 throughout the day ? which obviously cause this alert to be sent when too many queries are executing on the server ?

Moreover, Is this related to the configuration of MAX Degree of Parallelism (Which is set to 0) that causes queries exceed threshold use parallel threads to finish ?

Thanks

You should set MAXDOP and "Cost Threshold for Parallelism" according to best practices.

MAXDOP should generally be set to the number of cores per NUMA node.  See my question here for more details.

Cost Threshold for Parallelism should likely be set to at least 50.


database design for storing individual users' items I am trying to make an application which users can have their own account
For the account, I have an single table and now I would like to add a function which
allows users to items in to their account

For example, user A want to add item A ,B ,C ,D to his account,
then on the database should have 4 rows of new data
Also there are no limit on the maximum number of item users can add.

My question will be how the item should I store on the database.
In a single table or a new table for each of the users?
Will the data retrieving time be very slow if there are lots of users with items?
And will many tables in a single database affect my works?

A general rule of thumb is that you will have a table for each type of thing that you are recording information about.  Each instance of a thing will be a record in a table.

It would probably* be a terrible idea to have one table per user.  Have one table with USERS and one table with THINGS.  In the THINGS table you will have a foreign key which is a column containing a user_id that points at the USERS record for the owner of the thing.

Relational databases are designed specifically to make this kind of structure easy and fast to work with.  You shouldn't* ever need to worry about how many users there are or how many things they have.

* NOTE: Your requirements are very sparse.  These are rules of thumb.


Which file do I need to modify my.cnf MariaDB on Ubuntu 18.04 I want to modify these values:

[mysqld]
key_buffer_size    = 256M
max_allowed_packet  = 128M
thread_stack    = 256K
query_cache_limit  = 64M
query_cache_size        = 384M
innodb_buffer_pool_size = 1073741824
thread_cache_size = 256
back_log = 256
thread_handling = pool-of-threads

I've tried to edit the files that MariaDB says:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

But none of them seems to apply changes.

I'm running last version of MariaDB on Ubuntu 18.04
(10.1.34-MariaDB-0ubuntu0.18.04.1)

I've checked Stack Overflow before asking, including How do I find the MySQL my.cnf location

I had a similar problem but rectified it by adding the following line to /etc/mysql/my.cnf

!include /etc/mysql/mariadb.cnf

This seemed to fix the issue of the mariadb config file not being read.


Should I used varchar(max) or varchar(4000) SPARSE? I have a "comment"-type column that is rarely used -- around 6% non-null in a population of 3 million records. The average length (when used) is 6 characters, and the max so far is around 3KB. A maximum of 4000 characters is reasonable for this field. I have two options:

comments varchar(max) NULL -- this is the current column definition
comments varchar(4000) SPARSE NULL

My current understanding is that in both cases, a NULL value would require no storage -- just the column's NULL bit set and a length of 0 in the row metadata.

But for the non-null cases, does one have a clear advantage over the other?

The extra 4-byte pointer for sparse columns with values suggests they are always stored off-row like text or very large varchar(max) fields. Is that the case?

If so, I'd lean toward using varchar(max), since it only stores values off-row if the total row length exceeds 8KB, and the majority of my values are short and unlikely to put a row over the limit.

I haven't seen this particular situation addressed in the BOL, so I'm hoping someone here knows enough about the innards of MSSQL to give some insight.

(If it matters, I'm currently using 2008R2, but hoping to upgrade soon to 2014.)

There is no advantage for the non-NULL cases when using SPARSE, and in fact, there are two stated disadvantages:

an extra 4 bytes per each non-NULL value
slightly longer access time

As you pretty much already gathered, the SPARSE option only makes sense for fixed-length datatypes; I can't think of a single reason to use it on variable-length types.

I am not sure that the extra 4 bytes implies anything about it being stored off-row, and the MAX types aren't entirely off-row when they exceed 8000 bytes as there is then the 16-byte pointer in the row to that off-row location.

Stick with VARCHAR(4000), no SPARSE, and I would even consider making it NOT NULL DEFAULT('') (an empty string is still 0 bytes, but now you don't need to mess with the NULL indicator, and can a comment really be "unknown" as opposed to "no comment"?).


How to restore IBM DB2 database from AIX to Windows? I had made backup from remote IBM DB2 server in AIX using this command:

./db2 backup database DBEMP to /home/dbemp/backup

Then I grab the backup file using FTP Client from my Windows.

I run this command to restore the database in my local machine:

./db2 restore database DBEMP from "C:\backup" TAKEN AT 20130521002219

But I got this error message:

  SQL2570N  An attempt to restore on target OS "NT-64" from a backup
  created on source OS "AIX-64" failed due to the incompatibility of
  operating systems or an incorrect specification of the restore
  command.  Reason-code: "1".

So what is the solution for this?

You cannot restore a DB2 database from AIX into Windows because of the endian. These are not compatible architectures.

For more information, check the InfoCenter: http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0005960.html

The only way to retrieve the data between these platforms is via db2look + db2move.

db2look http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0002051.html
db2move http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0002079.html


How to see what was executed in the database via binlogs for specific date? I am trying figure out how to "query" binlogs to see what was exectued in the database for a specific date. For example, if I execute mysqlbinlog binlog then the thing just runs forever and it is had to see what exactly was executed there, especially if binlog is huge. Thank you!

You can give mysqlbinlog datetime stamps

Here is a sample datadir folder

[root@********* mysql]# ls -l /var/lib/mysql/mysql-bin.*
-rw-rw---- 1 mysql mysql 1073742028 Aug 13 03:29 /var/lib/mysql/mysql-bin.003111
-rw-rw---- 1 mysql mysql 1073741943 Aug 15 13:37 /var/lib/mysql/mysql-bin.003112
-rw-rw---- 1 mysql mysql  434864318 Aug 16 16:00 /var/lib/mysql/mysql-bin.003113
-rw-rw---- 1 mysql mysql         57 Aug 15 13:37 /var/lib/mysql/mysql-bin.index

Suppose you want to see everything executed from 10:45 AM to 2:25 PM on Aug 14th. All of Aug 14th is in /var/lib/mysql/mysql-bin.003112. Run the following:

mysqlbinlog --start-datetime="2012-08-14 10:45:00" --stop-datetime="2012-08-14 14:25:00" /var/lib/mysql/mysql-bin.003112 > SQL.txt

If your range was Aug 13th 5:00PM to Aug 14th 2:00AM, you must span two binary logs. So, you would run the following:

mysqlbinlog --start-datetime="2012-08-13 17:00:00" --stop-datetime="2012-08-14 02:00:00" /var/lib/mysql/mysql-bin.003111 /var/lib/mysql/mysql-bin.003112 > SQL.txt

Give it a Try !!!


Limiting PostgreSQL logging I have a table that can contain reasonably large BYTEA values (the same could apply to large TEXT values). It looks like this:

CREATE TABLE testtable (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL, -- This is just an example: this could be the PK
    value BYTEA -- This could be TEXT
);

If the application using this table tries to insert two rows using the same name, I get this error in the logs (this is an expected error, of course):

ERROR:  duplicate key value violates unique constraint "testtable_name_key"
STATEMENT:  INSERT INTO testtable(name, value) VALUES ('a', ...something rather long...);

While logging the error is useful, as well as logging the statement (and possibly the value for "name" in this particular context), logging the long BYTEA or TEXT value isn't. In fact, binary data in the logs are escaped in text form (e.g. E'\\377\\327...'), which effectively makes it take even more space than it would in binary form in the database. This can cause more problems than help when the value's size is tens of kB or more.

Is there a way to prevent these values from appearing in the logs when there is an error? I'm still interested in knowing there was an error (and preferably have some information about the statement). Of course, this is not just about this particular error and use case, but any error log that could potentially dump multiple kilobytes of uninteresting data in the logs.

Otherwise, is there any way to truncate the "STATEMENT:  INSERT INTO ..." line? Failing that, would there be any way to keep the ERROR: line but to remove the STATEMENT: line altogether?

I've tried to set log_error_verbosity = terse in postgresql.conf, but this doesn't seem to change anything for this.

(I'm also using log_destination = 'syslog' to log to rsyslog, so a solution that limits each log "bundle" to a few lines there would be a reasonable compromise.)

I'm using PostgreSQL 8.4, but if solutions only present in newer versions would also be interesting.

Yes you can hide those messages in the log.

In the calling session, before running the INSERT statement, issue this statement:

SET log_min_messages TO log;

If you want to still see the ERROR: part, and hide only the STATEMENT: part, use another parameter:

SET log_min_error_statement TO log;

See docs.

Use only session parameters, your application must know which statements will be silenced. Technically, you could make the setting permanent (in postgresql.conf) but it's not a good idea. In some cases this will be a footgun.


PL/pgSQL - Indexes vs Collation vs Pattern Ops I'm using PostgreSQL 9.3.

Having already read articles and answers on DBA stackexchange:

Why would you index text pattern ops on a text column

PostgreSQL documentation: Indexes - Opclass

Pattern matching with LIKE/SIMILAR TO/Regular expressions

and being aware of EXPLAIN command I've crossed paths with performance issues in my DB.

As mentioned in documentation and answers above, it seems that when my database has a collation C

db=# SHOW LC_COLLATE;
 lc_collate
------------
 C

I do not need to use special operator ie text_pattern_ops on my indexes.

I've also tested how things work in my database and it seems that I:

have collate C
have table (assume it's named as table) containing column named signature of type text
have index on that table idx_table_sign btree (upper(signature))
also have index on that table idx_table_sign_like btree (upper(signature) text_pattern_ops)

Now let's assume I have a row with signature value I Lm 837/32.

EXPLAIN SELECT * from table WHERE upper(signature) = 'I Lm 837/32'

Does search using index

Index Scan idx_table_sign on table ...
Index Cond: ...

Which is OK considering our index, but let's see this:

EXPLAIN SELECT * from table WHERE upper(signature) LIKE 'I Lm 837/3%';

    Index Scan idx_table_sign_like on table ...
    Index Cond: (... ~>=~ ...) AND (... ~<~ ...)
    Filter: ... ~~ ...

Which seems good as well, but as I've read when I do have collation C i don't need to set text_pattern_ops indexes, so imagine this:

DROP INDEX idx_table_sign_like;

EXPLAIN SELECT * from table WHERE upper(signature) LIKE 'I Lm 837/3%';

Which only differs in operators (~>=~ is now simply >= and ~<~ is <)

    Index Scan idx_table_sign_like on table ...
    Index Cond: (... >= ...) AND (... < ...)
    Filter: ... ~~ ...

Now, thank you for reading this and getting so far, here are questions:

Do I need two indexes on this column with one using text_pattern_ops? Doesn't collate C do the trick? Also, is it possible that I have a table or a column that overwrites the collation of entire database, and because of that it might have been added? If yes, please tell me how do I check that.
Are the operations INSERT, UPDATE, DELETE slowed by this index (imagine I have 4 columns like this in a table containing 1 milion rows - main point is that those indexes need to be rebuilt (as far as I know), which may be unnecessary - this question is about telling me whether or not queries also use this index with text_pattern_ops instead of just C collation and if that may result in lower performance - you can see the EXECUTION PLANS with that index
Do queries like select ... where upper(signature) like 'xxx%' - left-anchored patterns use index idx_table_sign_like which is totally unnecessary considering my collation which they could be using instead? - please notice the difference in operators that planner is using depending on index (having it or not)
Added after Craig Ringer answer. In my columns I'm having text and varchar which contain polish characters. What does it change in my specific case? I hope this is some kind of a hint for the answerer on topic of neediness of those indexes.

These questions are very important, as my database is pretty huge (> 1TB) and has more than a dozen tables containing such indexes on several columns, on which inserts and updates come on a daily basis.

consider using the citext data type available in contrib. you don't have to do this upper / lower nightmare anymore. for the regular expression type you can use gist along with gist_trgm_ops. it will boost regular expressions nicely.