Disable Journaling in MongoDB + WiredTiger We have a cluster of 3 Mongo 3.2.13 instances running with journaling enabled, with hundreds of DBs and collections which are accessed simultaneously.

We are checking the option to disable journaling, to improve the cluster's performance. According to the docs, WiredTiger performs a checkpoint every 60 seconds, so in a case of crash we should only loose up to 60 seconds of data. We are ok with that.

Are there any other risks / disadvantages for disabling journaling?

When running a journal disabled cluster, we observed frequent disk checkouts - once about every 3 seconds. This was confusing, as the docs specifically mention that checkpoints are created every 60 seconds.

After digging around, we found the following comment on mongo's Jira:

  ... it is expected that the primary node executes a checkpoint whenever there are multiple write threads as this checkpoint ensures that writes to the replica set are durable

This means that when journaling is disabled, and there are multiple threads writing to the DB, checkpoints become frequent and slow down the entire instance.

This, of course, was a sufficient reason for us to turn on journaling again.


Getting "There is already an object named 'Mytable' in the database" error I keep getting the following message:

  Msg 2714, Level 16, State 6, Procedure tr_AuditTableChange, Line 9
  There is already an object named 'Mytable' in the database.

Not sure what I am doing wrong and I can't figure out what this is.

Can somebody please help?

Look at the source code for trigger tr_AuditTableChange.

If it's creating a permanent table, see if it can use a #temp table instead, or better yet simple normal variables.

If it must use a permanent table, then quit creating and/or dropping that table, and leave it in place permanently.  If you have multiple sessions possibly stepping on each other, add @@SPID, but this is a bad technique in the first place.

For an auditing trigger, the auditing should do at most the following things

Insert into your audit table (which should have INSERT only permissions for normal users - no SELECT, no UPDATE, no DELETE).
ROLLBACK unauthorized transactions (if absolutely required)
Notify administrators of unauthorized transactions (if absolutely required).


Is it okay to delete MSDB? I'm not a DBA, I've only googled what MSDB does it's basically a DB of SQL Agent of its Job and History, Now I'm running out of space in my cloud server and I have 1 year worth of MSDB year 2017, Is it okay to delete this or do I keep it for backup purposes?

My MSDB is 93GB in a 250GB HDD.

You can't drop the msdb database as stated in the docs (emphasis mine):

  Restrictions

  The following operations cannot be performed on the msdb database:

  Changing collation. The default collation is the server collation.
  Dropping the database.
  Dropping the guest user from the database.
  Enabling change data capture.
  Participating in database mirroring.
  Removing the primary filegroup, primary data file, or log file.
  Renaming the database or primary filegroup.
  Setting the database to OFFLINE.
  Setting the primary filegroup to READ_ONLY.

Tampering with system databases isn't a good idea usually. You should check where your space problem is and consider expanding your drives.

To check where your size problem is:

Check table and index sizes inside msdb database using this query:

USE msdb
GO

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    TotalSpaceMB DESC

If sysjobhistory pops up in space then review your current job history rentention policy and make sure that your jobs' schedules are on check and not triggering more often than they need.
Check database data and log file sizes with this query:

;with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeInMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeInMB
from
    sys.databases db
where
    db.name = 'msdb'

If the log file size is high you need to find out which operation made it increase it's size and troubleshoot it. Shrinking the file will free some space but won't solve the underlying problem.


The broker in the sender's database is in single user mode Could you please help with why am I getting this sys.transmission_queue.transmission_status message after restoring from a snapshot?

  The broker in the sender's database is in single user mode. Messages cannot be delivered while in single user mode.

This is what I've tried without success:

Drop down to single user mode
Restore from a snapshot WITH NEW_BROKER option
Return to multiuser mode WITH NO_WAIT
Check sys.databases.is_broker_enabled and sys.service_queues.is_receive_enabled
Send a message which will get stuck in the transmission queue complaining about single user mode.

Is there another DMV or catalog to query to find the status of the service broker?

Thank you.

After a database restore, Service Broker queues with activation do not appear to have their queue monitors set back up properly, even if WITH ENABLE_BROKER is specified during the restore.

Explicitly re-enabling service broker causes the queue monitor to get recreated properly.  If anybody has a better workaround, I'd love to hear it.

You can check for the problem by querying dm_broker_queue_monitors for any queues that are in the undocumented state 'DROPPED' that do not also have corresponding queues in a different state (e.g. 'INACTIVE').

If this query comes up with anything, you have a broken queue:

select database_id,queue_id,[state] from sys.dm_broker_queue_monitors m
 WHERE state='DROPPED'
   and not exists (select 1 from sys.dm_broker_queue_monitors m2
                    where state <> 'DROPPED'
                          and m.queue_id=m2.queue_id
                          and m.database_id=m2.database_id)

You can fix it by re-enabling service broker (yes, even though it is enabled already; re-enabling it will cause the queue to get rebuilt properly.)  You need to be in single user to enable service broker:

ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [dbname] SET ENABLE_BROKER WITH NO_WAIT
ALTER DATABASE [dbname] SET MULTI_USER


Query to compare the structure of two tables in MySQL To automate the backup process of one of my MySQL databases, I would like to compare the structure of two tables (current version vs old version).

Can you think of a query that can compare two tables?

Here are some example tables that you can compare.

CREATE TABLE product_today
(
  pname VARCHAR(150),
  price int,
  PRIMARY KEY (pname)
);

CREATE TABLE product_yesterday
(
  pname VARCHAR(150),
  price int,
  PRIMARY KEY (pname)
);

CREATE TABLE product_2days_back
(
  pname VARCHAR(15),
  price int,
  PRIMARY KEY (pname)
);

The first two tables have identical structures. The last one is different. I just need to know whether two tables have different structures or not. I'm not interested in the how they differ.

TWO TABLES IN THE CURRENT DATABASE

If you want to know if two tables are different, run this

SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema=DATABASE()
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

If you actually need to see the differences, run this

SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema=DATABASE()
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

TWO TABLES IN A SPECIFIC DATABASE

If you want to know if two tables are different in database mydb, run this

SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema='mydb'
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

If you actually need to see the differences, run this

SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema='mydb'
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

TWO TABLES IN TWO DIFFERENT DATABASES

If you want to know if db1.tb1 and db2.tb2 are different, run this

SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE
    (
        (table_schema='db1' AND table_name='tb1') OR
        (table_schema='db2' AND table_name='tb2')
    )
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

If you actually need to see the differences, run this

SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE
    (
        (table_schema='db1' AND table_name='tb1') OR
        (table_schema='db2' AND table_name='tb2')
    )
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

GIVE IT A TRY !!!


Changing a column's data type fills the transaction log Running this code:

ALTER TABLE npidata
ALTER COLUMN npi varchar(20)

Gives this error:

  Msg 9002, Level 17, State 4, Line 2
  The transaction log for database 'SalesDWH' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

I am changing the NPI column's data type from varchar(80) to varchar(20).

The following code gives me the same error message:

insert into npidata1 select * from npidata

log_reuse_wait_desc shows NOTHING
The recovery mode is SIMPLE
autogrowth is set to NONE
autoshrink is set to true

What else can I do? My understanding is the logs should just be truncating every time they get too big. What am I doing wrong?

the very first thing you have to do is to change AUTOSHRINK to false. There is absolutely no reason to ever have it true. See AUTOSHRINK: Turn it OFF!.

Both operations you are attempting require a size-of-data update in a single transaction. A single transaction requires that much log, irrelevant of the recovery model. You must increase the log size to accommodate your transaction.


Why BULK INSERT insert data in random order? I have a csv file with 350,000 rows. Would like to insert data to temporary table in same order as is in csv file. I am trying to BULK INSERT using:

BULK INSERT ##AllRows
    FROM @FilePath
    WITH
    (
    FIELDTERMINATOR = '\n'
    )

Unfortunately BULK INSERT inserts data in random order. My header in every execution is in different row. I ran it on SQL Server 2016. Is there a possibility that in older versions of SQL Server order manner was different?

Using the FIRSTROW option does not recognize header as first row of file. File does not have any column for which we can order. In file always header is in first row.

It could be a coincidence but even with FIRSTROW=2 it is possibility that my header will be in table. I checked it. It looks like the more rows csv file contains the more chance that insert to table will be with random order.

The rows from the file are read in order, and added to the table in the same order.

The issue occurs when you read rows from the table. Without an ORDER BY clause on your SELECT, SQL Server is free to return rows from the table in whatever order is convenient.

Details

The question doesn't provide a definition for the table ##AllRows, but it seems certain the table is a heap (a table without a clustered index). SQL Server reads pages from a heap using Index Allocation Map (IAM) structures. This means data tends to be returned in file and page id order within each IAM chain, which will generally not reflect the order in which data was inserted. This is the underlying cause of the behaviour you are seeing.

Solutions

You need a column to indicate the order of rows in the file, then order by that column when you write your query. Unfortunately, SQL Server does not provide a built-in way to add this 'sequence' column during import.

There are a couple of common workarounds:

Pre-process the source file outside SQL Server to add a sequence number to each row. This is the most reliable method.
Assign a sequence number during the import.

The second method carries some risk because there is no documented guarantee that this will work reliably in all circumstances. Nevertheless, people have been using this idea successfully for a long time. The general idea is:

Add an IDENTITY column to the import table.
Create a view over the import table, omitting the IDENTITY column.
BULK INSERT into the view.

This will not work with a global temporary table, because a view cannot reference that type of table. You would need to use a regular table (perhaps in tempdb) instead.

Example 1

I used the following script to successfully import a csv file containing the Complete Works of Shakespeare:

The first step is to create a table in tempdb with an extra IDENTITY column:

USE tempdb;
GO
CREATE TABLE dbo.Test
(
    id integer IDENTITY PRIMARY KEY,
    line nvarchar(4000) NOT NULL
);

Now we create a view over that table, omitting the IDENTITY column:

CREATE VIEW dbo.ImportTest
WITH SCHEMABINDING
AS
SELECT
    T.line
FROM dbo.Test AS T;

Finally, we bulk insert into the view:

BULK INSERT dbo.ImportTest
FROM 'C:\Temp\shakespeare.txt'
WITH
(
    CODEPAGE = '65001',
    DATAFILETYPE = 'char',
    ROWTERMINATOR = '\n'
);

We can now see the first few lines in file order using a SELECT with ORDER BY:

SELECT TOP (20)
    T.id,
    T.line
FROM dbo.Test AS T
ORDER BY
    T.id ASC;

The result shows the text in the right order:

Example 2

It is also possible to use OPENROWSET with a format file. Using the same example csv file, I was able to import the data using the following format file (saved as shakespeare.xml):

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\r\n"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="line" xsi:type="SQLNVARCHAR" NULLABLE="NO"/>
 </ROW>
</BCPFORMAT>

and:

INSERT dbo.Test
    WITH (TABLOCK)
    (
        line
    )
SELECT
    ORO.line
FROM OPENROWSET
(
    BULK 'C:\Temp\shakespeare.txt',
    FORMATFILE = 'C:\Temp\shakespeare.xml',
    CODEPAGE = '65001'
) AS ORO;

Notice that this method does not require a view, so you could target a global temporary table. The target table still needs the extra IDENTITY column.


Maximum Server Memory adjust in blocks of 1024MB? Our protocol is to set Maximum Server Memory at about 75% of RAM.  For a 4GB RAM, sometimes it gets set at 3000MB and sometimes to 3072MB.  There is a 2 or 3 percent difference in these values.

Is the impact of setting memory at a value that is not evenly dividable by 128, greater then the difference between the rounded and binary values?

Asked differently; 128*23=2944 & 128*24=3072, Does using 3000MB provided a benefit that is between 2944 & 3072 or does it's fractional size disrupt memory usage?

It's reasonable to assume, although I have no way to know, that the difference between allocating 3000 MB and 3072 MB is just those 72 MB as memory allocation is much more granular than, say, 128 or 256 MB.

The minimum amount of memory allocated to each query is 1024 kB, configurable as low as 512 kB (source). A fair guess is that memory allocation units are anywhere between that of disk I/O operations (64 kB = 1 extent = 8 pages) and 512 kB.

Either way, 72 MB back or forth probably won't make any difference.


Point In Time recovery on Mysql Can someone please advise best methods to do a mysql PITR from binary logs that are saved in MIXED format.

I am finding it difficult to identify the wrong queries that executed to avoid from restore process using mysqlbinlog tool.

Following : https://www.percona.com/doc/percona-xtrabackup/LATEST/innobackupex/pit_recovery_ibk.html

When you inspect the binary log add -vvv to mysqlbinlog. It will show an effective query that corresponds to a binlog event.


Graphical Actual Execution Plan - Estimated vs Actual number of rows I am using:

SQL Server Management Studio v18.1
SQL Server 2017 CU9
AdventureWorks restored from here

Test query:

USE [AdventureWorks];
GO
SELECT TOP (13) *
FROM Sales.SalesOrderHeader AS s
    INNER JOIN Sales.SalesOrderDetail AS d ON s.SalesOrderID = d.SalesOrderID
WHERE s.TotalDue > 1000
OPTION (RECOMPILE);
GO

Turn on Actual Execution Plan (Ctrl+M).  Looking at the 'Clustered Index Seek' I see 13 of 17 (76%).  Looking at the properties I see Estimated Number of Rows or Estimated Number of Rows to be Read value is 3.85562. My question is where is this number 17 coming from?

Actual Execution Plan

The number 17 comes from the estimated number of rows (per execution) multiplied by the estimated number of executions.

3.85562 * 4.37149  =  16.8548043  ~=  17

In SentryOne Plan Explorer,* we perform the conversion for you, so that estimated rows simply starts at 17, not 3.85562 (I didn't try this specific plan, but that's how the logic in the code should work). I describe the reasoning behind this in this blog post, back when the fix first appeared in Plan Explorer.

  *Disclaimer: I work for SentryOne.


How do I change the instance name in MS SQL 2005 without installing a new instance? I have an instance that really has nothing on it, it's merely been installed (it was intended for one project, but that never actually got done on this server, the project was done in duplicate on another server) so since I have the instance, I want to rename it.

Can this be done? How would I do so?

Further investigation (googling) says it's not really possible. Additional consultation with my coworkers says I might as well install a 2k8 instance and move on.

It's not possible.

  You need to reinstall it. There is no
  instance rename facility for the
  Database Engine.

http://social.msdn.microsoft.com/Forums/en/sqlsetupandupgrade/thread/d5d297b0-2995-46ba-9d3b-d1ec8825f479


Get previous and next row from result set help I have to get "relevant" results from a table by joining it on another table and filtering the results on parent id and current id, this all goes fine until your current id is either the first or the last in a result set.

Imagine I have a result set that looks like this:
1,3,6,7,9,23,76
My current id is 3, I need to get the rows with id 1 and 3 back to my application, I managed to do that just fine, now the problem is that the application needs to make a circle.

So using the same result set as above, imagine my current id is 1 I need to get back 3 ( as the next result ) and 76 as my "previous" result.

I also managed to get that to work up to a certain extent, except for when your current id is the maximum, then something weird happens:
it gets the next row from the given result set (in the example case if max = 76 then next is 1), but isn't able to retrieve it's previous one.

If I run the query separately, it works fine.

I am using ifnull, since if you don't have a row with an id that is smaller or bigger than the given id, it will return null and should execute the next statement.

Here is the SQL I'm currently using:

SELECT
CONCAT_WS('/', LOWER(t1.name), LOWER(t2.slug), LOWER(t3.slug)) as slug,
t1.id, t2.id, t3.id
FROM table4 t4
left join table1 t1 on t1.id = t4.t1id
left join table2 t2 on t2.id = t4.t2id
left join table3 t3 on t3.id = t4.t3id
WHERE
t2.id IN (IFNULL((SELECT MAX(id) FROM table2 WHERE parent = 1046 AND id <       1424), (SELECT MAX(id) FROM table2 WHERE parent = 1046)))
OR
t2.id IN (IFNULL((SELECT MIN(id) FROM table2 WHERE parent = 1046 AND id > 1424), (SELECT MIN(id) FROM table2 WHERE parent = 1046)))

This of course is pseudo code, but the only thing that changes relative to the actual DB is the table/column names.

EDIT: the IFNULLs do actually both return the right result when run separately, and the full query works if the current id is anything but the bigges

I hope you can point me in the right direction !

Instead of 1424, perhaps you should use t4.t2id. Instead of 1046, if the parent exists in table4 or any of the others, use that link instead of the constant.

You could also use a single IN, and a UNION of both queries.

Finally, if you use a filter condition in WHERE on a LEFT JOINed table, that defeats the purpose of a LEFT JOIN and might result in bugs. Either use JOIN or move the condition to the ON part of that LEFT JOIN. In this case, the condition in the LEFT JOIN was different than the one in WHERE for the same field (t2.id), so it could never work.

SELECT
CONCAT_WS('/', LOWER(t1.name), LOWER(t2.slug), LOWER(t3.slug)) as slug,
t1.id, t2.id, t3.id
FROM table4 t4
left join table1 t1 on t1.id = t4.t1id
left join table2 t2 on t2.id IN (
    SELECT COALESCE((SELECT MAX(id) FROM table2 WHERE parent = 1046 AND id < t4.t2id),
                    (SELECT MAX(id) FROM table2 WHERE parent = 1046))
    UNION
    SELECT COALESCE((SELECT MIN(id) FROM table2 WHERE parent = 1046 AND id > t4.t2id),
                    (SELECT MIN(id) FROM table2 WHERE parent = 1046)))
left join table3 t3 on t3.id = t4.t3id
where t4.t2id = 1424


Converting Access SQL HAVING command to T-SQL I have this SELECT query I need to convert to a T-SQL view:

SELECT
    T_Compras.Guia, T_Compras.Data, T_Compras.Id_Embarcação,
    T_Compras.[Nº Maré], T_ComprasDet.Id_Peixe,
    T_ComprasDet.Id_Tamanho,
    SUM([Peso Kg] * [Caixas]) AS Peso,
    T_ComprasDet.Id_Frescura, Q_Guia_Fact.Valor
FROM
    (T_Compras
LEFT JOIN
    Q_Guia_Fact ON T_Compras.Guia = Q_Guia_Fact.Guia)
INNER JOIN
    T_ComprasDet ON T_Compras.Guia = T_ComprasDet.Guia
GROUP BY
    T_Compras.Guia, T_Compras.Data, T_Compras.Id_Embarcação,
    T_Compras.[Nº Maré], T_ComprasDet.Id_Peixe,
    T_ComprasDet.Id_Tamanho, T_ComprasDet.Id_Frescura,
    Q_Guia_Fact.Valor
HAVING
    (((T_Compras.Guia) = [Forms]![F_Compras]![Guia]));

I am able to get everything working but I don't know how that last HAVING clause would be in T-SQL. In Access what it is doing is prompting the user for a guide number (guide="guia") so that it only shows rows with that guide number.

Thanks in advance!

Create the view without the HAVING clause, and when you query the view, filter it using:

WHERE Guia = ????

...with your "????" being the value that you're passing in from your form.


How to update a table from a query result of another table related to the first one? Here is my attempt. What is the correct syntax to achieve this?
UPDATE table_a a
  SET a.firstCreationTime = c.created_at
FROM (
  SELECT created_at
  FROM table_b b
  WHERE a.id = b.id
  ORDER BY b.created_at ASC
  LIMIT 1
) AS c;

I think what you are searching for is

CREATE TABLE table_a ("id" int , "firstCreationTime" timestamp)

INSERT INTO table_a VALUES (1,CURRENT_TIMESTAMP - INTERVAL '1 days')

CREATE TABLE table_b ("id" int , "created_at" timestamp)

INSERT INTO table_b VALUES (1,CURRENT_TIMESTAMP - INTERVAL '10 days')
,(1,CURRENT_TIMESTAMP - INTERVAL '20 days'),(1,CURRENT_TIMESTAMP - INTERVAL '30 days')

WITH desired AS(
SELECT "id", MIN("created_at") as "created_at" FROM table_b GROUP BY "id")
UPDATE table_a AS a
SET "firstCreationTime" = "created_at"
FROM desired b
where a.id = b.id

1 rows affected

SELECT * FROM table_a

id | firstCreationTime
-: | :-------------------------
 1 | 2021-03-11 17:13:08.966639

db<>fiddle here


Data type for storing an array of flags (a bitmap/bit array) I need to store a bit array for each record of a table, supporting the following operations:

Testing if a bit is set, and setting a bit (using SQL)
Querying and setting the value using ADO 2.8 (not ADO.NET)
Indexing (in order to benefit from the "covering index" feature)

The maximum number of bits to be stored in this array is fixed, but may exceed 32. That is, a simple int column doesn't always work.

From what I've seen so far, my options are:

Use several int columns
Use bigint (works as long as the number of bits is <= 64)
Use binary
?

The first option would work, but require quite a bit of refactoring in the code that accesses the data. The second option is a temporary relief only, and from my searches so far I'm not too sure if ADO works that well with bigint. I have no experience with binary, and I'm not aware of any other options.

Which data type would you choose, given the requirements?

I can't advocate strongly enough not to use a single field for this.

I'm currently dealing with maintaining a very large dataset with a bigint bitmask field and it's a bit of a performance nightmare.

If you check a single bit it's fine.  If you check more than one bit performance degrades very quickly.

Due to the nature of bitmask integers, the data distribution will be very imbalanced and you'll get suboptimal plans.

Multiple bit checks result in range or index scans with a function running against every row.  It's a mess.

My workaround was simple - I made a table to store the PK for each of the conditions to be checked.  This is counter-intuitive initially but the space needed is low (you only store the PK) and lookups are lightning fast, especially if you use a UNIQUE CLUSTERED INDEX.

You can add as many conditions as you want without affecting your main table, and updates also don't affect your main table.

Indexing is simple since you just index all the lookup tables individually, and since your clustered key is the same on your main table and the lookups all your evaluations are merge joins which are very efficient.


Store HTTP response in PostgreSQL I want to store a whole HTTP response in PostgreSQL database row.

I need the HTTP status, the headers and the body.

AFAIK there is no native data type for this.

How could I store a HTTP response?

Update

I have been thinking about this again. I guess it is best if I follow this pattern: I won't search anything inside the http response. It is like a blob. Everything I want to search in it while be extracted before and put into a different column. Up to now only the http status code will get used and it will get an own column.

The HTTP/1.1 spec says, through RFC-7230, that:

the status code is a 3-digit number, so an int4 or int2 would do.
for the entire header, see 3.2.4, "Field parsing":

  Historically, HTTP has allowed field content with text in the
     ISO-8859-1 charset [ISO-8859-1], supporting other charsets only
     through use of [RFC2047] encoding.  In practice, most HTTP header
     field values use only a subset of the US-ASCII charset [USASCII].
     Newly defined header fields SHOULD limit their field values to
     US-ASCII octets.  A recipient SHOULD treat other octets in field
     content (obs-text) as opaque data.

"opaque" pretty much implies that bytea is the only safe choice, if you want to handle the responses of any HTTP server out there.
The message body is defined as  message-body = *OCTET, so bytea is also pretty much the only type that fits, unless you prefer the large objects storage and API. bytea is limited to 1Gb so you may want to chunk the value across several smaller rows if you target any size. In practice, very large bytea values tend to be unworkable, personally I wouldn't go over 128Mb per row.


Who is using my worker threads? SQL Server 2014 - HADR We had recently a problem on our SQL Server 2014 HADR environment, where one of the servers ran out of worker threads.

We got the message:

  The thread pool for AlwaysOn Availability Groups was unable to start a
  new worker thread because there are not enough avaiable worker
  threads.

I already opened another question, to get a statement which (I thought) should help me analyze the problem (Is it possible to see which SPID uses which scheduler (worker thread)?). Although I have now the query to find the threads which are using the system, I don't understand why that server ran out of worker threads.

Our environment is as follows:

4 Windows Server 2012 R2
SQL Server 2014 Enterprise
24 Processors --> 832 Worker threads
256 GB Ram
12 Availability Groups (overall)
642 Databases (overall)

So, the server which had the problem had the following configuration:

5 Availability Groups (3 Primary / 2 Secondary)
325 Databases (127 Primary / 198 Secondary)
MAXDOP = 8
Cost Threshold for Parallelism = 50
Power plan is set to "High performance"

To "resolve" the problem we manually failed one Availability Group over to the secondary server. The configuration of that server is now:

5 Availability Groups (2 Primary / 3 Secondary)
325 Databases (77 Primary / 248 Secondary)

I'm monitoring the available threads with this statement:

declare @max int
select @max = max_workers_count from sys.dm_os_sys_info

select
    @max as 'TotalThreads',
    sum(active_Workers_count) as 'CurrentThreads',
    @max - sum(active_Workers_count) as 'AvailableThreads',
    sum(runnable_tasks_count) as 'WorkersWaitingForCpu',
    sum(work_queue_count) as 'RequestWaitingForThreads' ,
    sum(current_workers_count) as 'AssociatedWorkers'
from
    sys.dm_os_Schedulers where status='VISIBLE ONLINE'

Normally the server has around 250 - 430 worker threads available, but when the issue started there were no workers left.

Today, out of nowhere, the available workers dropped from 327 down to 50, but only for a minute and went then back up to around 400.

I already saw the other question (HADR high worker thread usage) but it does not help me.

Our system ran stable for over a year without any problems. We haven't had any failover or other major change in the distribution of the databases.

We are using "Synchronous commit" between the replicas. From my understanding there is no compression involved, see Tune compression for availability group in the documentation.

Does any one have an idea what is using all the worker threads?

EDIT:
Found this page where there is a lot of information about exactly those issues
http://www.techdevops.com/Article.aspx?CID=24

Community wiki answer:

You have a high number of databases in availability groups, that's going to be where your threads are going. There is a lot involved in the compression, encryption, and transport cost. Try turning off compression, it will reduce your thread usage by a about a third (depending on the replica count).

The question is tagged SQL Server 2014, which will by default use compression. SQL Server 2016, by default, will not use compression for sync.

You may need to increase the worker threads on the instance, or better: balance out the most active ones and inactive ones on multiple servers. See the related Q & A AlwaysON Availability group query very slow.

You may also find it is an application that is unable to close requests properly. This can result in lot of sleeping sessions lying around (which consume workers).

The number of threads actually used depends on how active the databases are. You could have 1,000 databases and, if most are idle 95% of the time, you won't have any issues. It seems that your databases have become active more often and have eaten more of your threads. That's the long and short of it.


MySQL Cluster - Management Node Redundancy I am trying to design a fully redundant MySQL Cluster implementation for a service provider's network. Nodes within this cluster will be geographically separated between towns in the state that the service provider resides. Though I understand that using Geographic Replication for MySQL Cluster requires an asynchronous replication configuration, since the two data centers that the cluster nodes will reside in are connected via a high bandwidth (10Gbps), low latency pipe (~2ms), my goal is to have geographic diversity and synchronous replication across these data centers.

The nodes per data center break down like this:

Data Center A:

Management Node 1
SQL(API) Nodes 1 and 2
Data Node 1 (Node Group 1) and Data Node 2 (Node Group 2)

Data Center B:

Management Node 2
SQL Node 3
Data Node 3 (Node Group 1) and Data Node 4 (Node Group 2)

In this scenario, data node redundancy is strong (I believe), considering that a node from each node group will reside at each data center and a given data node in node group 1 (in either data center A or B) will have half of the data while the data node from the other node group in this same data center will have the other half of the data. In the instance of an isolation event or other type of failure that breaks apart the data centers, each location will have at least a full copy of the data.

My concern now becomes having a management node located in each data center. I am not sure if redundancy for the management nodes is best implemented this way or not. From what I understand about MySQL Cluster, if an isolation event occurs at one of the data centers (let's say data center A) and the connection between these two locations is lost, data nodes in data center B would start looking at management node 2 in this data center as the arbitrator, instead of the management node 1 in data center A, which would have originally been the arbitrator.

In my mind, this situation creates a split brain operation, where each data center is looking at its own arbitrator and, as a result, you still have all data nodes turned up in the cluster, yet only half of the storage locations are being written to. You break your synchronous environment.

Should I instead have the redundant management node also located in data center A on a separate physical resource, or does this change nothing about the logic of the cluster? Should I go ahead and instead create an asynchronous replication environment for the database and have each data center house its own cluster instead of breaking a cluster out between two geographic locations?

Warning: This is my first database project ever, so I am quite new to this stuff. I may be completely butchering this design implementation.

If you have split exactly in half brain situation MySQL Cluster resolves it designating one node as master. If you look into ndb_mgm output below you will notice asterisk at the end of node 10 line. It marks master.

It means that if your cluster will be split in two equal parts the half which contains master will continue running. Nodes from the other half will be shut down.

ndb_mgm> show
Connected to Management Server at: 10.1.1.10:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=10   @10.1.1.20  (mysql-5.6.24 ndb-7.3.9, Nodegroup: 0, *)
id=11   @10.1.1.21  (mysql-5.6.24 ndb-7.3.9, Nodegroup: 0)


Using the database engine tuning advisor - how do I generate the scripts for the recommendations? I have run a server-side profile trace for over an hour to produce a .trc file with all activities in one of my databases.

I have then passed this .trc trace file as a parameter to the database engine tuning advisor.

After running the DTA I get the recommendations:

How do I script out the recommendations?

I am using SQL Server 2005, and I don't seem to find any other way than scripting them individually which is overly time consuming.

Check the View Tuning Output:

  If you want to save all of the Transact-SQL scripts that create or drop all database objects in this recommendation into one script file, click Save Recommendations on the Actions menu.

As always review and test the recommendations before blindly applying them to your PROD environment.

I would highly suggest to look at more sane tools like sp_BlitzIndex from folks at BrentOzar.com


Searching for a close numeric match on spatial coordinates I have a large Postgres table contain geographic positions (latitude and longitude) both fields are indexed, and both are defined as NUMERIC(9,6).
If I run a query looking for an exact position match, something like this:
WHERE latitude  = 1.234567890123456789
AND   longitude = 9.876543210987654321

Then get a very fast response, but I get very few results because the database is searching for a very precise match.
For my purposes, I'm looking for positions that match to within a few meters so a match to 4 or 5 decimal places should be fine. This gives me the results I'm looking for:
WHERE ABS(latitude  - 1.234567890123456789) < 0.0001
AND   ABS(longitude - 9.876543210987654321) < 0.0001

But NOT the performance (it can take 5 minutes to run, compared to a fraction of a second for the exact search)
Next I tried rounding the precision down:
WHERE ROUND( latitude, 4) = ROUND( 1.234567890123456789, 4)
AND   ROUND( longitude,4) = ROUND( 9.876543210987654321, 4)

Again, same problem. Got the results I wanted, but took far too long.
So, my question is how can I search for a close match between two numbers, without losing performance?
UPDATE - SOLVED:
As a couple of commenters have observed, using BETWEEN seems to work fine.

The smart and fast solution for this class of problems is an index-backed "nearest neighbor" search.
For the record: if you want precise results with spatial data use PostGis and operate with geometry or geography types. Here is a starting point. And operate with ST_DWithin(). Examples:

Order by distance
PostGIS radius query

Sticking to your setup (2-D points, no PostGis), and ignoring the additional approximation error of handling spatial data in a 2-D plain, which seems negligible for the case at hand - I suggest a space-partitioned GiST index (on an expression in your case):
CREATE INDEX tbl_location_spgist_idx ON tbl USING SPGIST (point(longitude, latitude));

Why SP-Gist? See:

Perform this hours of operation query in PostgreSQL

https://www.2ndquadrant.com/en/blog/postgresql-12-implementing-k-nearest-neighbor-space-partitioned-generalized-search-tree-indexes/

To get a maximum of 10 "nearest neighbors" in next to no time:
SELECT *
FROM   tbl
ORDER  BY point (longitude, latitude)
      <-> point '(9.876543210987654321,1.234567890123456789)'
LIMIT  10;

You can then filter the ones close enough. To get a maximum of 10 closest within a maximum distance:
SELECT *, ((longitude - 9.876543210987654321) ^ 2
        + (latitude   - 1.234567890123456789) ^ 2) AS squared_dist
FROM   tbl
WHERE  ((longitude - 9.876543210987654321) ^ 2
      + (latitude  - 1.234567890123456789) ^ 2) < 0.000000001  -- or whatever
ORDER  BY point (longitude, latitude)
      <-> point '(9.876543210987654321,1.234567890123456789)'
LIMIT  10;

db<>fiddle here
I use a squared distance to get nearest neighbors based on simple Pythagorean theorem. The beauty of it: the calculation is only performed on the nearest neighbors, so it's still very fast when the calculation gets more expensive - even in big tables.


Can I anchor my data type to system type? The following code:

create or replace type sqlids_t is table of sys.v_$sql.sql_id%type;

...gives me...

Error(2,31): PLS-00329: schema-level type has illegal reference to SYS.V_$SQL

I get the same error creating a %object or %row type.

Is there any way I can use a sys type in my type definition? I know I can use the underlying VARCHAR2(13), but I'd like to avoid it if possible.

Short answer: No, you can't, and not because it's a system type. You can't anchor a freestanding type to any table's column data type.

%TYPE is a PL/SQL construct. CREATE [OR REPLACE] TYPE is SQL. You can't use %TYPE in SQL.

It somewhat makes sense that you can't. If you use MYTABLE.MYCOLUMN%TYPE in PL/SQL, you have anchored that PL/SQL type to the table, and should the type of MYCOLUMN change PL/SQL can invalidate your code, then recompile it. It's much less clear what Oracle would have to do if your example worked.

Imagine what would happen if you were storing objects of SQLID_T in a table, and the definition of SQL_ID in V_$SQL changed. Would Oracle need to change the definition of the stored objects? What if it couldn't (e.g, SQL_ID was changing from a VARCHAR2 to a NUMBER)? Should that prevent the definition of V_$SQL from changing? Or should the table that used that type become invalid in some way ... you can't SELECT from it anymore?

Oracle tries to prevent this kind of thing from happening (from Oracle's Object-Relational Developer's Guide):

SQL> CREATE OR REPLACE TYPE t1 FORCE AS OBJECT (c varchar(20));
  2  /
Type created.

SQL> CREATE TABLE tb1 (c1 t1);
Table created.

SQL> CREATE OR REPLACE TYPE t1 FORCE AS OBJECT (d number);
  2  /
CREATE OR REPLACE TYPE t1 FORCE AS OBJECT (d number);
*
ERROR at line 1:
ORA-22866: cannot replace a type with table dependents


Optimize query to not read all rows in table I have this in my slow query log.
Slow Query Log
# Time: 2020-03-09T08:42:46.946084Z
# User@Host: admin_admin[admin_admin] @ localhost [127.0.0.1]  Id:    16
# Query_time: 0.001558  Lock_time: 0.000278 Rows_sent: 0  Rows_examined: 255
SET timestamp=1583743366;
select DISTINCT q.id,q.*
            from bot_message_queue q
            left join bot_message_queue_wait w1 on q.botId=w1.botId AND q.chatId=w1.chatId
            left join bot_message_queue_wait w2 on q.botId=w2.botId AND w2.chatId=0
            where
              q.status=0 AND

              (w2.retry_after IS NULL OR w2.retry_after < 1583743366) AND
              (w1.retry_after IS NULL OR w1.retry_after < 1583743366)
            order by q.priority DESC,q.id ASC
            limit 1;

My tables
Following the DDL for the tables involved in the query.
bot_message_queue
CREATE TABLE IF NOT EXISTS `bot_message_queue` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `botId` int(10) UNSIGNED NOT NULL,
  `chatId` varchar(50) CHARACTER SET utf8 NOT NULL,
  `type` varchar(50) DEFAULT NULL,
  `message` longtext,
  `add_date` int(10) UNSIGNED NOT NULL,
  `status` tinyint(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0=waiting,1=sendig,2=sent,3=error',
  `priority` tinyint(1) UNSIGNED NOT NULL DEFAULT '5' COMMENT '5=normal messages,<5 = bulk messages',
  `delay_after` int(10) UNSIGNED NOT NULL DEFAULT '1000',
  `send_date` int(10) UNSIGNED DEFAULT NULL,
  `identifier` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `botId` (`botId`,`status`),
  KEY `chatId` (`chatId`,`status`),
  KEY `botId_2` (`botId`,`chatId`,`status`,`priority`,`identifier`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
COMMIT;

bot_message_queue_wait

CREATE TABLE IF NOT EXISTS `bot_message_queue_wait` (
  `botId` int(10) UNSIGNED NOT NULL,
  `chatId` varchar(50) CHARACTER SET utf8 NOT NULL,
  `retry_after` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`botId`,`chatId`),
  KEY `retry_after` (`retry_after`),
  KEY `botId` (`botId`,`chatId`,`retry_after`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `bot_message_queue_wait`
--
ALTER TABLE `bot_message_queue_wait`
  ADD CONSTRAINT `message_queue_wait_botId` FOREIGN KEY (`botId`) REFERENCES `bot` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

Question
It's not slow, but it is reading the whole table on every request.
What indexes should I change/add to fix this?
UPDATE
Explain result without distinct

UPDATE 2
This is a very heavy read/write/update table, its the single most used table in my entire database, usually it has just less than 1000 rows but it could go up to 10000+ when someone is sending bulk messages ...
I clean up processed rows every hour to keep it small ...

Try this:

CREATE INDEX ix_bot_message_queue_status ON bot_message_queue (status, priority desc, id, botId, chatId);
select q.id
from bot_message_queue q
where
              q.status=0
              AND
              (NOT EXIST (SELECT 1 FROM bot_message_queue_wait w1 WHERE botId=w1.botId AND q.chatId=w1.chatId)
              OR EXIST (SELECT 1 FROM bot_message_queue_wait w1 WHERE botId=w1.botId AND q.chatId=w1.chatId AND w1.retry_after < 1583743366))
              AND
              (NOT EXIST (SELECT 1 FROM bot_message_queue_wait w2 WHERE botId=w2.botId AND 0=w2.chatId)
              OR EXIST (SELECT 1 FROM bot_message_queue_wait w2 WHERE botId=w2.botId AND 0=w2.chatId AND w2.retry_after < 1583743366))

order by q.priority DESC,q.id ASC
limit 1;

you may need to use a trick with generated column if your MySQL version doesn't support descending indexes


How can I know my database version? I need to know what is my database version.

I'm using DB2 V9.7 on linux 64 bits.
I'm connected to a database and i need to know if it is 32 or 64 bits.

Can someone help me?

If you follow this link there are several ways.
One of them shows you the architecture.

From that page:

$ db2level
DB21085I  Instance "db2inst5" uses "64" bits and DB2 code release "SQL09071"
with level identifier "08020107".
Informational tokens are "DB2 v9.7.0.1", "s091114", "IP23034", and Fix Pack "1".
Product is installed at "/opt/ibm/db2/V9.7".


Backup plan for development server I have some SQL servers that are used only by developers, and the developers (i.e. me) are expected to maintain them. We really don't care much about losing data, so backups are not a priority, but the transaction logs do have a tendency to fill up, slowing everyone down until I can remember how to truncate them.

Does anyone have a sample scheduled task that deals with this situation? Second best: advice on how I should write my own?

Set the database to simple recovery. This way the transaction log is only used during active transactions. Once transaction is committed, the active portion of the log is release and can be reused, thus minimizing log growth. Long running transactions with lot of DML will still cause the log to grow.

ALTER DATABASE DBNAME SET RECOVERY SIMPLE;


Oracle database issues I had my database working properly but I had an issue and when I tried to repair it I executed the following command

alter tablespace system add datafile '/var/oracle/oradata/data.dbf' size 10000m autoextend on maxsize unlimited;

Then I noticed that the command didn't resolve my problem then I deleted the file data.dbf... But the problem that I'm having right now is that when I'm trying to restart my database it doesn't run.

I looked and didn't find how to fix the problem. Can someone help me? Any help please... I'm down

If you deleted the SYSTEM tablespace datafile as you say, and do not have a backup, there is no recovery. You will need to recreate the database.

Side note, you should never delete a datafile directly at the OS level and never the SYSTEM.


Postgres permission denied I'm a rookie with postgres, and after I started the postgresql-11.service,
I enter in 'su postgres', and try create my db, but appear this message:

[root@127 nemo]# systemctl enable postgresql-11.service
[root@127 nemo]# systemctl start postgresql-11.service
[root@127 nemo]# su postgres
bash-4.4$ createdb mydb
could not change directory to "/home/nemo": Permission denied
createdb: could not connect to database template1: FATAL:  no pg_hba.conf entry for host "[local]", user "postgres", database "template1", SSL off"

Can anyone help me?

createdb needs to connect to a database to issue a CREATE DATABASE SQL statement, and by default it will use:

a Unix socket domain connection
the current username from the shell as the database user (so postgres in your case)
template1 as the database

When it does that, in your case you get this error message:

  createdb: could not connect to database template1: FATAL:  no
  pg_hba.conf entry for host "[local]", user "postgres", database
  "template1", SSL off"

It indicates that postgres as a database user does not have the permission to connect through a Unix domain socket (that's what [local] means).

This is unusual, since in general, postgres installers on Unix tend to install a pg_hba.conf starting with this rule:

local   all       postgres       peer

Meaning that the postgres Unix user has the right to connect locally to any database as the postgres database user, which suits the needs of administrating databases, including creating new ones.

In fact your pg_hba.conf does not look like a default one. The simplest way to proceed to immediately solve the problem of database creation would be to edit it,  add the above line as the first rule, and reload the postgres service.


How to change permissions to mysql pid file By default mysql creates pid file as -rw-rw---- mysql:mysql mysql.pid. Is there any way to change those permissions? I need o+r for monitoring purposes.

Of course I could add sticky bit for directory above and change group ownership but if it could be changed somewhere in settings it would be the cleanest solution.

I'm running mysql 5.5 if that matters.

There's no need. You can check the existence of the file without changing the permissions:

if [ -f /var/run/mysqld/mysqld.pid ]; then     echo "mysqld running"; fi

If that's not good enough you can check the pid (i.e. the contents of the file) directly:

ps -U mysql | grep mysqld | cut -f1 -d' '


AlwaysOn: Can reads on secondary block DDL on primary We have a db that is replicated on a secondary in an AlwaysOn Availability Group. Doing some schema migrations proves very hard. We have read requests on the synchronous secondary replica basically 24/7. Schema migrations are only applied during deploy. On larger tables schema changes seem to cause a lot of blocking, which lead us to not applying some of our changes. Obviously this is not a desirable state, as the database schema diverges from the schema that we want (and use in our testing and development db).

We got the advice to disable ReadOnly connections to the secondary replicas for the schema migrations. However I am unsure if those can affect the schema changes at all. From what I have read, I would guess that it doesn't, because reads at the secondary should not block DDL queries on the primary. Could someone explain, what the actual consequences are, if any?

Could someone explain, what the actual consequences are, if any?

Sure.

  On larger tables schema changes seem to cause a lot of blocking, which lead us to not applying some of our changes.

This is happening on the primary (if it truly is blocking), you'll need to find what sessions are blocking it at why (on the primary for the primary).

  We got the advice to disable ReadOnly connections to the secondary replicas for the schema migrations.

This won't help the blocking, it could lessen it but only if the secondary is in synchronous commit mode and only if the workload on the secondary is causing extra HADR_SYNC_COMMIT waits. If it isn't, then it's not going to help the primary for blocking.

What turning off readable seconaries will do, is stop the workload on the secondary allowing it to have the most speed in keeping up by not having any other workloads to contend with. It may also help with redo blocking on the secondary since you're running DML and could lessen the send queue/redo queue. If you had synchronous replicas you may want to look at making them asynchronous for the time being depending on if the schema changes are size of data operations or not.

  From what I have read, I would guess that it doesn't, because reads at the secondary should not block dml queries on the primary.

Correct.


SQL Server Massive delete causing huge/full transaction log Application has a stored procedure that executes a massive delete (millions of records) all at one time which causes a huge transaction log.  CANNOT modify stored proc (to break up delete into batches) - vendor restrictions.
SQL Server 2019 / Simple Recovery Model
Does anyone have an alternate approach to manage the transaction log growth when this stored proc runs?

If you cannot in any way modify the stored proc, then it sounds like the vendor is at fault for shipping a stored proc that doesn't work well for their customers.  They would need to fix something.
Your options would be things like giving yourself a bigger transaction log that can manage it.  If you don't have space you could temporary split the log into 2 files and put it on 2 drives.  That comes with risk and benefits such as more IO depending.
Also please look at VLF fragmentation.  Your log files are probably heavily fragmented now.  Defragging your log files after you find the proper sizing will help performance, recoverability, restores, and some disk space savings.


How does pausing a SQL Server mirror affect transaction logs on the principal? One of our two datacenter's is undergoing maintenance for a minimum of 72 hours, but it could be up to 5 days, and I have a very old legacy SQL Server running a production service as a mirror across both data centers.

I'm thinking that for the duration of the outage, I can pause the mirror, but I'm wondering what the effect will be on the transaction logs of the principal? We have scheduled t-log backups running hourly, so this should keep them from growing too large right?

How then does that affect replication when restarting the mirror? Would it be easier to break the mirror completely, put the database into simple recovery mode, and then rebuild the mirror afterwards?

I would recommend that you strongly consider breaking the mirror and rebuilding it once the maintenance is complete.

When you pause the mirror, the transactions that need to be sent over will build up in your transaction log.  Taking a transaction log backup will not free this space for reuse, although the log backups will be consistent and usable for their intended purpose (point in time recovery).

The same type of consideration needs to be made for when using Availability Groups.  In an outage (planned or otherwise), how many (minutes/hours/days) of transactions can my log file hold?  How long will it take that information to be sent over?  How long will it take to rebuild the mirror/availability group?

I usually make sure that there is enough free space for the time for (error to happen, notifications to bubble up (phone calls, text, etc.) then allow an hour or two for investigation/corrective action.  I fight for at least that much disk space.

You can look at the transaction log backup size (uncompressed) for an idea of how much data you are talking about.  Then plug in guesses for the rest.  You can buy yourself extra time by turning off any maintenance (index defragmentation, for example) while the mirror is paused.

But since you are going to measure this outage in days, I would break the mirror and rebuild it once it's back online.


Managing logins with Availabilty Groups I have created a user on my primary node that has access to all of the databases in Availability Group AG1.  How do I ensure that same user has the same rights on all the other replicas in AG1?

According to this MSDN article (http://msdn.microsoft.com/en-us/library/hh270282(v=sql.110).aspx) I need to determine if they are contained databases first but I'm not entirely sure I have contained databases.  The user that I created on the other replica's get's an error when I try to add him to the AG1 databases.  The error indicates they are part of an Availability Group and are not accessible.

Assuming these are not contained databases, you need to worry about the logins (server level), not the users (database level). Contained users will be transferred automatically without difficulty.

Manually creating a login with the same name on the other server is not good enough - the SID will not match.

Robert Davis has written a post that describes how to generate the login scripts with the right SID and the right password hash. This is what you need to run on each of the replicas:

Transferring Logins to a Database Mirror

(Also see the comment thread on this answer.)

In order to just script all of the logins on the local server, change the stored procedure by commenting out this section (and the End that goes with it):

--If Not Exists (Select 1 From sys.server_principals
--          Where name = @LoginName)
--  Begin

(There are other, similar IF NOT EXISTS sections you'll want to comment out in order to get roles and permissions.)

Then call the stored procedure with hard-coded @@SERVERNAME and @debug = 1. This will print out CREATE LOGIN commands with the password hash and you can pick the ones you need and run those commands on the replicas.

Jonathan Kehayias wrote an SSMS add-in that handles all of the uncontained objects (don't forget about things like jobs), but I don't know if it has been maintained for newer versions of SSMS:

Synchronize Availability Group Logins and Jobs


select top 5 accounts in each region in an month per sales My data is in table Sales

ACCT_ID |REGION | DATE_FILTER | SALES
1001    | TEXAS |  2018-01    | 10
1002    | DENV  |  2018-01    | 30
1004    | WDC   |  2018-01    | 50
1001    | TEXAS |  2018-02    | 500
1002    | DENV  |  2018-02    | 300
1004    | WDC   |  2018-02    | 120

I want to find out rank of each account in a district per month by sales my output will look like

ACCT_ID |REGION | DATE_FILTER | SALES   | Rank
1001    | TEXAS |  2018-01    | 10      |3
1002    | DENV  |  2018-01    | 30      |2
1004    | WDC   |  2018-01    | 50      |1
1001    | TEXAS |  2018-02    | 500     |1
1002    | DENV  |  2018-02    | 300     |2
1004    | WDC   |  2018-02    | 120     |3

I am using Mysql 5.7 version, I have tried below

SELECT
        ACCT_ID ,REGION,SALES,DATE_FILTER,n RANK
    FROM
      ( SELECT  @prev := '', @n := 0 ) init
    JOIN
      ( SELECT  @n := if(DATE_FILTER != @prev_rpt_mnth and REGION != @prev_reg, 1, @n + 1) AS n, /* HERE I AM USING TWO COLUMNS TO GROUP BY */
                @prev_rpt_mnth := DATE_FILTER,
                @prev_reg := REGION
                ACCT_ID ,REGION,SALES,DATE_FILTER,UNITS
            FROM  SALES
            ORDER BY
                DATE_FILTER,REGION ASC,
                UNITS DESC
            LIMIT 999999
      ) x
    WHERE  n <= 5
    ORDER BY  DATE_FILTER,REGION, n;

I am getting duplicates as well it not properly ranking based on sales. Where I am going wrong and is there any other efficient way.

You must init ALL variables on the level where they are used. In your case - in subquery data source. - akina


Error installing SQL Server Database Engine Services Instance Features - The User Log directory in the registry is not valid We have a SQL Server:

Microsoft SQL Server 2017 (RTM-CU14-GDR) (KB4494352) - 14.0.3103.1 (X64)   Mar 22 2019 22:33:11  Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0  (Build 14393: ) (Hypervisor)

Tried to install "Cumulative Update Package 18 for SQL Server 2017 - KB4527377"
https://www.microsoft.com/en-us/download/details.aspx?id=56128
Setup was doing something for 15 min, installing and updating files, stopping / starting services, it looked like its going to succeed, but in the end it showed this:

Action required:
Use the following information to resolve the error, and then try the setup process again.
Feature failure reason:
An error occurred during the setup process of the feature.
Error details:
Error installing SQL Server Database Engine Services Instance Features
The User Log directory in the registry is not valid.
Verify DefaultLog key under the instance hive points to a valid directory.
Error code: 0x851A0044
Visit https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=14.0.3257.3&EvtType=0xD8FB5EBA%400x97A656BB%401306%4068&EvtType=0xD8FB5EBA%400x97A656BB%401306%4068 to get help on troubleshooting.

What's preventing CU18 from installing ?

Ok I found this article

https://blog.sqlauthority.com/2015/05/16/sql-server-service-pack-error-the-user-data-directory-in-the-registry-is-not-valid-verify-defaultdata-key-under-the-instance-hive-points-to-a-valid-directory/

It says my CU update fails because of Default Log path does not exist anymore

Default Log path can be checked by one of below ways:

Execute T-SQL

exec [master].sys.xp_regread
    @rootkey='HKEY_LOCAL_MACHINE',
    @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\',
    @value_name = 'DefaultLog'

SSMS -> Right Click Server -> Properties -> Database Settings -> Database Default Locations
Regedit -> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\DefaultLog

Updated Default Log path to point to existing location, and CU18 update installed fine without any errors


Unexpected Results with File Restore I am practicing a file restore using the AdventureWorks database and I am not getting the restore I am expecting. I am sure there's some embarrassingly simple cause, but I have not found it yet.

Here is what I have done

Created a new file group named USERTABLES_01.
Copied data from HumanResources.Employee table to new a new .HumanResources.Employee_01 in the USERTABLES_01 filegroup.
Generated a Full backup.
Executed DELETE FROM HumanResources.Employee_01 with no where clause
Shazbot, I need to restore the table. OK, I'll restore the filegroup USERTABLES_01.
Executed RESTORE DATABASE [AdventureWorks2012]  FILE N'AdventureWorks2012_UserTables_01' ...
Generated a tail log backup
Restored tail log backup

Everything restores without complaint. But my HumanResources.Employee_01 doesn't have any data.

Reviewed MS Example, maybe I didn't follow it correctly, but I am pretty sure I did.

Reviewed similar question, but not sure it's the answer I am looking for:  Restore .mdf data file for a filegroup in SQL Server.

SQL Servers highest priority is to comply with the ACID properties. That means, no matter what you do, your database is always going to be transactionally consistent.

What you are trying to do however could get you into an inconsistent state. Think about the standard example to explain the ACID properties: The bank account. Say you have two accounts that reside on different filegroups (e.g. two account tables or one with partitioning). Now you transfer money from filegroup one to filegroup two within a transaction. Then you go and restore filegroup one to before the transfer. That results in the money being in both accounts now, clearly not a desired state.

So, what is happening in your example?

6) the filegroup is successfully restored to a point in time before the disaster. However it is in the "restoring" state and not accessible.

8) the log restore is bringing the filegroup up to speed with the rest of the database (on a transactional level). To do that, all committed transaction that are captured in the log backup are reapplied to that filegroup. However, the delete was a committed transaction, so it is re-executed too. This leaves the database consistent, but the table empty.

The Technet example you are pointing to makes the assumption that the file goes offline for some external reason, e.g. a drive failure. In that case the restore as described makes sense as you would want all the transactions to be re-applied.

In your case you need to restore the database somewhere else and then copy the data back into the original table. For that you can just restore the filegroup containing the data in question and the primary filegroup. You cannot just restore a secondary filegroup, the primary has to always be restored too. This is one of the reasons why it is recommended to not have any data in the primary filegroup. That way the impact of having to restore that one too is minimal.


Mysql Join with previous record Pardon for being naive. This is a simple simple question but i am unable to figure out how to query this

Query :

I have 2 tables

kknagar_data & kknagar_rates

I am trying to query out in such a way that

get current record from kknagar_data  - i was able to achieve this
get kk_egg,kk_cash of previous record from current in kknagar_data
get rates from kknagar_rates by joining kk_rate_id in kk_nagar_rates & kk_nagar_data -  I was able to achieve this

What i am trying to achieve

I want all the above 3 points in a single query.

I was able to do point 1 & 3 and i queried like this

SELECT kk_data_id,kk_date,kk_egg,kk_cash,kk_in,kk_out,kk_expenditure,..... from kknagar_data INNER JOIN kknagar_rates on kknagar_data.kk_rate_id = kknagar_rates.kk_rate_id"

but i am unable add point 2 to the above query so that i get all the data in a single shot

What i tried :

I tried with Unions but i get error because the columns doesnt match

What i am using now :

Currently i use 2 different queries to achieve the above

SELECT kk_data_id,kk_date,kk_egg,kk_cash,kk_in,kk_out,kk_expenditure,..... from kknagar_data INNER JOIN kknagar_rates on kknagar_data.kk_rate_id = kknagar_rates.kk_rate_id"

and

Select kk_egg,kk_cash FROM kknagar_data WHERE kk_data_id < 3 ORDER BY kk_data_id DESC LIMIT 1

It gives me the result but i am trying to change this to make it more efficient. Another reason is when i json out the values, i find it difficult to decode multiple objects.

Goal :

Get current record with 2 fields of previous record and join with kk_nagar_rates & kk_nagar_data using rate_id

Any guindance will be much helpful.

Thanks in Advance

You can use two nearly identical subqueries in your original SELECT:

SELECT
  kk_data_id,
  kk_date,
  kk_egg,
  kk_cash,
  kk_in,
  kk_out,
  kk_expenditure,
  (  SELECT kk_egg              -- You get kk_egg from the "previous" row
       FROM kknagar_data k2
      WHERE k2.kk_data_id < k1.kk_data_id
   ORDER BY kk_data_id DESC
      LIMIT 1
  ) AS prev_kk_egg,
  (  SELECT kk_cash              -- You get kk_cash from the "previous" row
       FROM kknagar_data k2
      WHERE k2.kk_data_id < k1.kk_data_id
   ORDER BY kk_data_id DESC
      LIMIT 1
  ) AS prev_kk_cash
FROM
  kknagar_data k1
  INNER JOIN kknagar_rates kr on kr.kk_rate_id = k1.kk_rate_id ;

Having two subqueries looking for two different columns on the same row looks like a waste of time, but it basically mimics what you're doing right now and it works.

You can check it at SQLFiddle.

Note on aliases

You have one table (kknagar_data) that is accessed for three different purposes. First you use it to get the list of "normal records". That's when this table is used as kknagar_data k1 (which can also be written as kknagar_data AS k1). The k1 is just an alias for the table, within that specific context.

Then, in your subqueries, you also refer to the same table, but for a different purpose. In that case, I've used a different alias (FROM kknagar_data **k2**), so that I can refer to the same table with two different contexts. The condition k2.kk_data_id < k1.kk_data_id means: look at the same table (k2 and k1 are actually the same table), but right now (k2) choose the ids that are less than the ones for the "normal records" (k1).

Both subqueries use k2 as an alias because I just copied and pasted the code, and changed the column name. That's possible because the symbol k2 is local to each subquery, and cannot be used outside of its context. That's the same as using the same variable name in two adjacent blocks of a program written in, let's say, Java. It is not advisable to use the same alias when one block contains the other, because you would have, at least, ambiguities.

Aliases are needed when you refer more than once to the same table. For instance, when you have self-referencing tables (i.e.: a table persons where you have a parent-child relationship), or when you want to access "a record of the same table somehow related to the one you already have", such as in your case. Sometimes it is just used for simplicity (k1 is shorter than kknagar_data).

Window functions as a (better) alternative

Databases allowing for WINDOW functions would let you to use the lag() function to have the same effect, in a more efficient manner. Check Introduction to LEAD and LAG for Microsoft SQL Server. You can achieve the same with PostgreSQL, or also with the latest versions of MariaDB. MySQL 8.0 is going to have window functions as well.


trying to understand table size Can somebody help explain the "data_length" of the table? Why is it so big in my example below of a table with just one column and one row?

mysql> create table `network_type` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into network_type(id) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT data_length FROM information_schema.TABLES  WHERE table_schema = "test";

+-------------+
| data_length |
+-------------+
|       16384 |
+-------------+
1 row in set (0.01 sec)

With innodb_file_per_table configured, the network_type table will exist in a physical file under the DB folder. For example, let's say your table has the following attributes:

Table name somedb.network_type
datadir is /var/lib/mysql

You will find the physical file /var/lib/mysql/somedb/network_type.ibd

That file is the home of all data and index pages in InnoDB. The pre-compiled size of a page is 16K. There is room inside that page for more data, but 16K is the fixed allocation for data and index.

When you ran SELECT data_length FROM information_schema.TABLES  WHERE table_schema = "test";, MySQL simply reported the most granular space InnoDB is using.


Which datatype to store XML data in: VARCHAR(MAX) or XML I am defining a schema for a new set of resources using SQL Server 2008... In this case, each record (e.g. row) will need to store XML fragments. From time-to-time; although not frequently; I'll need to query the XML to find element and attribute values. If left to my own devises, I would tend to use the xml data type although I’ve been led to believe this is wrought with issues. So that leads me to my questions.

Given this scenario, what factors should I be considering when trying to decide between storing XML in an xml column vs. a varchar(MAX) column

If it helps… here are some additional details:

No decision has been made regarding the use of schema’s for these fragments (e.g. XSD’s)
Sizes of the fragments will range from small to very large
All XML will be well-formed
Over the course of a day, there will be up to ~10,000 fragments collected with online query support needed for ~3 months
Queries against the XML will happen throughout the day but should remain light with few concurrent queries of this type

what factors should I be considering when trying to decide between storing XML in an xml column vs. a varchar(MAX) column

The factors are:

The XML type is queryable / parseable through XQuery expressions, including being able to use FLWOR Statement and Iteration

Data in XML variables and columns can be modified inline using XQuery expressions via XML DML.

XML data is stored as UTF-16 LE (Little Endian), so VARCHAR(MAX) would be a poor choice as it could result in data loss. Hence, the true decision should be between XML and NVARCHAR(MAX), given that NCHAR / NVARCHAR is also UTF-16 LE.

XML data can be validated against an XSD / XML SCHEMA COLLECTION. No validation (outside of ensuring well-formedness) is done if no XML Schema Collection is specified, but this option is not available when using NVARCHAR(MAX).

One major benefit of the XML type is that it is stored in a highly optimized format (not VARBINARY(MAX) as stated in @Oleg's answer) that does not store the exact string representation that you see, but instead has a dictionary of Element and Attribute names and refers to them by their ID. It also removes whitespace. Try the following:
  DECLARE @Test1 XML = N'<Test><TagName>1</TagName><TagName>2</TagName></Test>';

 DECLARE @String1 NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @Test1);

 SELECT DATALENGTH(@Test1) AS [XmlBytes],
        LEN(@String1) AS [StringCharacters],
        DATALENGTH(@String1) AS [StringBytes];

 SET @Test1 = N'<Test><TagName>1</TagName><TagName>2</TagName><TagName>3</TagName>
 <TagName>4</TagName><TagName>5</TagName><TagName>6</TagName></Test>';

 SET @String1 = CONVERT(NVARCHAR(MAX), @Test1);

 SELECT DATALENGTH(@Test1) AS [XmlBytes],
        LEN(@String1) AS [StringCharacters],
        DATALENGTH(@String1) AS [StringBytes];

Returns:
<!-- language: lang-none -->

    XmlBytes   StringCharacters   StringBytes
    56         53                 106

    XmlBytes   StringCharacters   StringBytes
    84         133                266

As you can see in the example output above, adding four elements (#s 3, 4, 5, and 6) added 80 characters (hence 80 bytes if using VARCHAR) and 160 bytes to the NVARCHAR variable. Yet, it only added 28 bytes to the XML variable, which is less than it added for VARCHAR (just in case someone was going to argue in favor of VARCHAR over XML because XML is UTF-16 which is [mostly] double-byte). This optimization can save tons of space, and is reason enough by itself to use the XML datatype.


Tracking changes made in database structure MYSQL Is it somehow possible to keep track of changes made to the STRUCTURE (not the data) made to tables?

I want to know when the table definition is been changed (and what those changes were). E.g. to know if/when a column was added, removed, if a column type was changed, if an index was added, and also the new table added to database schema etc.

In other words, to keep track of all the "ALTERS" of the tables and "CREATE" of the tables. (and the state before those alters)

I'd like this to know exactly when some changes were made, and to keep track of discrepancies of data, for example to know when a column was changed to hold VARCHARs instead of INTs (just an idea), or when a new column was added, and who made the changes, who added new table and etc.

Is there any way to find it out? Or enabling an option to be able to track this in the future?

There is no way to do that from the database side, but you can version control your schema changes by putting all your schema modification .sql scripts in a Git repository the same way you do with source code.

There are also tools such as FlyWay and Liquibase that can help you with this sort of thing.


Schedule Job to Start at Different Steps I am working on a system that generates a number of recurring emails as parts of various jobs scheduled in SQL Server Agent. The emails contain text-based reports that are loosely associated to the subject matter of their jobs, but not dependent on the job's outcome. When a customer requests a change to the contents of the report or the distribution list, it can be difficult to find the specific job/step that is responsible for generating the email/report.
To make maintenance of these tasks easier, I want to create a new central job and move each email/report step into that job. I would configure each step of the job to "Quit the job reporting success." I would configure a unique schedule for each step in the job so that the email/reports continue to be generated on their normal schedules. For instance, step 1 would run daily at 7am. Step 2 would run on Saturdays and Sundays at noon. And so on.

When I went to create a schedule, I was unable to find a way to specify which step to start on. I know that msdb.dbo.sp_start_job accepts a @step_name parameter. Likewise, SQL Server Agent allows me to pick a step to start from when manually starting a job through the UI. Is there a way to specify the start step from a schedule, or am I limited to starting with the configured (default) start step as listed in the job?

A schedule has no dependencies on a job. In fact, a schedule is a shared resource which can be used by several jobs.
There's no such thing as a "local" schedule. There's nothing stopping you, of course, from having only one job using a schedule.
I.e., there is no place in the Agent architecture for a schedule to kick off a certain job step.
So, yes, you are limited to having a schedule starting the job at the step defined in the job.
The way I read your scenario, there is nothing in common between the steps except that they share the same subsystem of your application (or however you would phrase it). I'd love for agent to have some namespace/folder functionality for jobs, but no. IMO, the closest you can come is to have a healthy naming standard for your jobs, using some prefix in the job name to identify which system each set of jobs belong to. And, of course, comments inside the job.


Help in designing mysql table for large project I am planning to create a website which allows different Companies to register in it. It will have Employees module. Each company will have a CompanyId. How do I maintain the Employees table, since in near future data sets may go extremely large. Kindly suggest which is appropriate. I am listing my intended designs, kindly suggest a better one .

Note : The Employees of one company has no relation with employees of other companies.

    Companies Table

    CompanyId CompanyName
    1       A
    2       B
    3       C

Employees Table

DESIGN 1:

DB : Companymanagement
Table Name : Employee

  Here the employees for a company are identified from the companyId

    EmployeeId      CompanyId   EmployeeName
    1       1(from companies table) Employee-A
    2       2(from companies table) Employee-B
    3       2(from companies table) Employee-C

DESIGN 2:

DB : Companymanagement
Table Name : Employee_1

    EmployeeId      EmployeeName
    1               Employee-A
    2               Employee-B
    3               Employee-C

    Table Name : Employee_2
    EmployeeId      EmployeeName
    1       Employee-A
    2       Employee-B
    3       Employee-C

  Here the employees for a company are stored in individual tables with
  companyid in table name ex Employee_1

DESIGN 3:

DB : Companymanagement_1
Table Name : Employee

    EmployeeId      EmployeeName
    1       Employee-A
    2       Employee-B
    3       Employee-C

DB : Companymanagement_2
Table Name : Employee

    EmployeeId      EmployeeName
        1           Employee-A
        2           Employee-B
        3           Employee-C

  Here, the employees for a company are stored in individual databases
  with companyid in database name ex Companymanagement_1

Kindly suggest me the right direction to manage datas. Sorry if I am vague.

If there is a possibility of an employee to work in many companies such as Advisor or Instructor, the above designs will fail.
There are possibilitites where one employee can work in more than one company.

So the perfect design would be the below

Company_Id   Company_Name
   1          Aaaa
   2          Bbbb
   3          cccc

Employee_Id   Employee_Name
   11          A
   22          B
   33          c

Company_Id   Employee_Id
   1          11
   2          22
   3          33
   3          22

This works in both the ways. One employee can work in many companies and many companies can have same employee. Many to Many relationship .


WITH behavior in relation to indexes I have a question related to behavior of WITH queries.

So I have

WITH test as (
    SELECT id, name, ....
    FROM test...
    WHERE ... )

In that auxiliary statement I select a column(s) that have index on them. So question is - If i select later on from test will it use that index?

Short answer

No

Long Answer

The WITH clause, also known as CTE ( Common Table Expressions ) is evaluated first. If there are multiple CTE's the are evaluated in order of the appearance.

The resultset is kept in something like an "in memory temporary table" without any index on it.

Unless there is a very good reason, i would suggest to avoid CTE's as they are optimization fences ( for the query planner).


SQL Server Application paths to include in server backup? I have an instance of SQL Server 2017 Standard installed on a physical server running Windows Server 2019. I'm currently working with our server administrator to configure the server backup, which he is using Symantec Backup Exec to complete. He is using the SQL Server agent to perform the SQL Server Database backups, but for the file backup I was wondering if there are any specific directories that it is best practice to include.
I'm under the impression that database and log files should be excluded from the file backup, but was wondering if there are any specific folders where SQL Server was installed to that should be included? I've tried looking this up in Microsoft's documentation and Googling it, but can only seem to find documentation regarding performing database backups.
Any advise would be appreciated
Thanks

For server backup on Windows the backup software will typically interact with the the Volume Shadow Copy Service to generate a a point-in-time consistent view of each volume regardless of changing data or open files.
Then for server-level recovery, restore based on the volume snapshots to and then perform SQL Server database RESTORE using backups taken sometime later.
If you don't have the ability to protect whole volumes and open files, then the server backup is not going to be much use in any recovery scenario.


Do INSERTs get auto-committed? Our application fires an INSERT query to the MySQL Database to add records. I want to know whether or not the records get auto-committed. If I run the ROLLBACK command, when does the database perform a rollback ? Is a ROLLBACK possible after a COMMIT ?

The answer to your question depends on whether-or-not you're within a transaction that will span over more than one statement. (You've tagged the question with InnoDB, the answer would be different with MyISAM.)

From the reference manual: http://dev.mysql.com/doc/refman/5.1/en/commit.html

  By default, MySQL runs with autocommit mode enabled. This means that
  as soon as you execute a statement that updates (modifies) a table,
  MySQL stores the update on disk to make it permanent.

So yes, by default, if you're just using INSERT, the records you insert will be committed, and there is no point trying to roll them back. (This is effectively the same as wrapping each statement between BEGIN and COMMIT.)

However, if you're dealing with transactions explicitly, you will have to use COMMIT to commit store the records, but you will also be able to use ROLLBACK.

You can start a transaction explicitly by using START TRANSACTION (or BEGIN). This is independent of the autocommit setting (on by default):

  With START TRANSACTION, autocommit remains disabled until you end the
  transaction with COMMIT or ROLLBACK. The autocommit mode then reverts
  to its previous state.

Alternatively, if autocommit=0, I think any statement following another end of transaction, will start a transaction (but you can still use START TRANSACTION explicitly); that's at least the way I interpret this:

  The autocommit mode. If set to 1, all changes to a table take effect
  immediately. If set to 0, you must use COMMIT to accept a transaction
  or ROLLBACK to cancel it. If autocommit is 0 and you change it to 1,
  MySQL performs an automatic COMMIT of any open transaction. Another
  way to begin a transaction is to use a START TRANSACTION or BEGIN
  statement. See Section 12.3.1, “START TRANSACTION, COMMIT, and
  ROLLBACK Syntax”.

More specifically "another way to begin a transaction" seems to imply that setting "autocommit=0" is enough to start a transaction (at least just before each statement at the start a session or that follows a COMMIT/ROLLBACK).
I would suggest to use BEGIN or START TRANSACTION explicitly anyway even if autocommit=0, as it can make it clearer to see when the transaction starts or ends.

(How you start a transaction may depend on the way your application uses MySQL.)


BACKUP failed to complete the command BACKUP DATABASE Not sure if this is a false-positive issue or not, but I need quell my curiosity about it.  I get the following error message:

  Error: 3041, Severity: 16, State: 1.
  BACKUP failed to complete the command BACKUP DATABASE DBApp. Check the backup application log for detailed messages.

I get this error fairly often, but here is the kicker, when I check the backup folder for the DB in question, the .trn & .bak file are there.  So, it appears it is backing it up, but stating it failed.  The backup is performed by a maintenance plan and it finishes fine while backing up all the subsequent databases.

Any thoughts or should I just completely ignore this from now on as long as the DB is listed in the backup folder?

Looks to be a post-completion check failure as there is no evidence elsewhere stating otherwise.


Query MST instead of UTC for timestamps on SQL Server, i'm trying to query all of last months data based off of the time stamp, however, all those timestamps are in UTC and i need them to be in MST (the reason is mainly for the end dates)

So far this is what I have:

Select Vehicle.Timestamp
WHERE Vehicle.Timestamp >= DATEADD(mm,DATEDIFF(mm,32,GETUTCDATE()),0)
AND Vehicle.Timestamp < DATEADD(mm,DATEDIFF(mm,0,GETUTCDATE()),0)
order by Vehicle.Timestamp

i'm not entirely sure which way to move forward.

I've seen the use of switchoffset however, not sure the best way to apply it.

If you just need this for a one-time query, I'd recommend something like this:

WITH AdjustedVehicleInfo AS
(
    SELECT DATEADD(HOUR, -7, Timestamp) AS MST_Timestamp
    FROM Vehicle
)
SELECT MST_Timestamp
FROM AdjustedVehicleInfo
WHERE MST_Timestamp BETWEEN @StartDate AND @EndDate

where @StartDate and @EndDate are set to MST times.

The reason I put that disclaimer is because the offset between GETDATE() and GETUTCDATE() is not always -7 for MST, and indeed is not the same across all timeframes for most timezones (places like Arizona and other non-DST-observant localities being exceptions). If you want this to become a query that people/software is going to use for a long time, you'll need to figure out a different way of adjusting the values to the desired timezone.


Is it possible to drop data from OLAP database (and to keep definitions)? [SQL Server Analysis Services from MS SQL Server 2008 R2 Standard]

Our OLAP DB takes data from OLTP DB. We want to make some changes to its structure and to do this - it would be helpful if we could transfer only current OLAP DB definitions and not data which is "buffered" inside. Then OLAP DB could re-take data from OLTP DB again.

Is it possible?

Yes, it is possible.

The easiest way would be to go with Management Studio to the proper SSAS server, right click on the database -> Script Database as -> Create to -> New query window.

That will generate a .XMLA file that creates the complete definition of your SSAS database. That's without the prepared data inside. When you need your database and its data, you just run the XMLA file, it will create the OLAP db, and then Process that database. This will add the data.

But, in theory at least, you should have a Visual Studio project where that SSAS database was created, so if you have it and it's the latest version, than you don't need the OLAP database at all, you just redeploy and process the data.


Unattended install of SQL Server 2008 R2 cluster fails with error - "Illegal characters in path." I am trying to build an .ini file for unattended installations of SQL Server 2008 R2. I followed this blog post to build an initial .ini file: http://blogesh.wordpress.com/2008/09/23/silent-install-of-sql-server-2008/

After cancelling the install and trying to install from the command line
(\server\store\SQL\setup.exe /ConfigurationFile="\server\store\SQL\Config.ini"), I get this error - "Illegal characters in path."

I get this error after resolving a number of simpler errors (such as paths showing up with no "\") because the created .ini file had some bugs (such as listing the path as "R:" in the .ini)

I'd like to know if anyone has been successful with installing a cluster using an ini file. Any help is appreciated.

Update:
I tried to install the cluster using the ini created from sirsql.net's powershell script. SQL does install now, but fails to bring the network name online (in the Failover Cluster Manager). All the settings look identical to our existing (functional) clusters, but a Wireshark capture shows that the new cluster is using the "realm" of the parent domain instead of the child domain (the server's domain, and the domain of all the service accounts, including the account I am using to install with). Our functional clusters show the "realm" as the child domain.
The packet that comes back from the domain controller shows this Kerberos error: KRB5KDC_ERR_S_PRINCIPAL_UNKNOWN.

I'd really like to get a Failover cluster setup without having to go through the gui since we are build a couple new clusters each week. Any ideas on what could be causing this or how to fix it?

Nicholas Cain (@SirSQL on Twitter) published three blogs recently regarding the installation of SQL on a WIndows Failover Cluster using PowerShell to script the activity.

The first of the three blog entries may be found at http://sirsql.net/blog/2011/7/11/creating-a-clustered-sql-install-ini-file-using-powershellpa.html

The second of the three blog entries may be found at http://sirsql.net/blog/2011/7/13/creating-a-clustered-sql-install-ini-file-using-powershellpa.html

Finally, the third blog entry may be found at http://sirsql.net/blog/2011/7/18/creating-a-clustered-sql-install-ini-file-using-powershellpa.html

His approach uses PowerShell and it might give you some ideas.


SQL Server replication - set PollingInterval in sp_addmergepushsubscription_agent I am trying to create replication subscriptions to and SQL Server 2008 R2 via a script. There are 3 subscribers so this script creates the subscriptions by calling

sp_addmergesubscription

and then

sp_addmergepushsubscription_agent

with the required parameters for each subscriber. The only thing I have not accomplished so far is to tell the sp_addmergepushsubscription_agent that I wish to set the PollingInterval to 30 seconds instead of the default 60 seconds. What parameters need to be changed in order to do the trick?

You need to set the PollingInterval Merge Agent parameter in the replication agent profile after you create your subscription and your subscription agent.

Have a look at Work with Replication Agent Profiles.


Indexing very similar character varying field in postgres I have a table with a column "name", the table is describing a media asset and the name is a character varying field containing the file name. The table is generated and used by a CMS (Strapi) and I can't really tweak how the columns are used nor the SQL being executed. What I'm hoping to do is slap on an index (or two) and get a bit better performance.
The file names of our files are very similar, pretty much XYZ12345-Q2.png, where XYZ is the same for about 80% of the files. So what I'm wondering is what kind of index (if any) would help speeding up a query such as:
select count(*) as "count"
from "upload_file"
where ("upload_file"."name"::text ILIKE '%some_string%'
       or "upload_file"."id"::text ILIKE '%some_string%'

The id is the primary key, and it's an auto incrementing positive integer.
My concern regarding the actual string is that an index won't do much when the file names are so similar? Or would it actually make a difference? In that case what would be the best index type to use? My understanding of Gin is that it wouldn't really suit this case because there are no words (none of the file names contain space).

What a terrible query! Consider using different software.
The only thing that could help are trigram indexes on both columns:
CREATE EXTENSION pg_trgm;

CREATE INDEX ON upload_file USING gin ((name::text) gin_trgm_ops);
CREATE INDEX ON upload_file USING gin ((id::text) gin_trgm_ops);

Then you might get two bitmap index scans and a "bitmap or".
If not, the condition is probably not selective enough, and a sequential scan is the most efficient way to handle it.


The relations between sessions, DOP and sets of parallel execution servers From How Parallel Execution Works:

  The number of parallel execution servers in one set is the degree of parallelism (DOP)

What is the meaning of "one set" in the document? Does it have a relationship with user session? My understanding is that one session has only one set, two sessions have two sets, and the total session's execution servers is limited by parallel_servers_target. Is that correct?

That quote is definitely unclear. I interpret "one set" to be one set of operations, which is also called a data flow operation in the context of a parallel plan. One parallel query may have more than one data flow operation, so one session may contain more than one set. I recommend reading Understanding Parallel Execution - Part 1:

The question is often asked: "How parallel?" For example, what actual Parallel Degree was used for the execution of that statement last night? The question in fact is not that simple to answer since it lacks some precision. First of all there is a so called Degree Of Parallelism (DOP) that is determined by the optimizer when generating the execution plan. A recent overview of how this DOP is determined can be found here, on the The Data Warehouse Insider blog, including the latest Automatic Degree Of Parallelism (Auto DOP) feature introduced in Oracle Database 11.2.
Then there is the problem that it is possible that the optimizer generates multiple so-called "Data Flow Operations (DFO)" per execution plan, and that each DFO has its own, possibly different, DOP.
At execution time for each DFO there can be up to two Parallel Slave Sets active at the same time, each Parallel Slave Set consisting of DOP number of Parallel Slaves.
So for a single SQL statement executed in parallel, there is potentially no single answer to the question "How parallel?" because the optimizer might de-compose it into several DFOs, each having its own DOP.


Error 35338: Clustered columnstore index is not supported On SQL Server 2012, I want to index a table with a columnstore index. But an error message apears:

  Msg 35338, Level 16, State 1, Line 1
  Clustered columnstore index is not supported.

My code is :

CREATE TABLE [dbo].[Card](
    [CardId] [int] IDENTITY(1,1) NOT NULL,
    [CardSerialNumber] [varchar](19) NULL,
    [CreateDate] [datetime] NOT NULL
) ON [PRIMARY]

GO
CREATE CLUSTERED COLUMNSTORE INDEX cci_Simple ON [dbo].[Card]

SQL Server 2012 only supports the creation of nonclustered columnstore indexes. Change your code to:

CREATE NONCLUSTERED COLUMNSTORE INDEX cci_Simple ON [dbo].[Card]

In SQL Server 2012 this will make your table read-only. You should probably look at adding all three columns of the table to the columnstore index but I'll leave that to your testing.

Note that columnstore indexes also require Enterprise Edition.


Getting recovered db files to work on rebuilt server I've had to rebuild my server and have tried to copy the database files from the old drive to /var/lib/mysql/. I have restarted apache and run 'show databases', this list all the databases. I have entered one of these databases 'joomla' and run 'show tables' this shows all the tables I would expect.

However trying to run a query such as 'SELECT * from bsf_session' thows up and error:

ERROR 1146 (42S02): Table 'joomla.bsf_sessions' doesn't exist

checking the table I get:

mysql> check table bsf_seminar;
+--------------------+-------+----------+---------------------------------------------------------+
| Table              | Op    | Msg_type | Msg_text                                                |
+--------------------+-------+----------+---------------------------------------------------------+
| joomla.bsf_seminar | check | Error    | Can't find file: './joomla/bsf_seminar.frm' (errno: 13) |
| joomla.bsf_seminar | check | error    | Corrupt                                                 |
+--------------------+-------+----------+---------------------------------------------------------+
2 rows in set (0.00 sec)

When I copied the files over I change the permissions to match those of the mysql table, however I couldn't change ownership, the files currently look like this (ls -g):

-rw-rw---- 1 root   8842 2012-02-23 09:54 bsf_session.frm
-rw-rw---- 1 root 271012 2012-02-23 09:54 bsf_session.MYD
-rw-rw---- 1 root  25600 2012-02-23 09:54 bsf_session.MYI

I have tried to run

chown mysql bsf_session.frm

but this doesn't make a difference. I don't know if this is related to the main problem or not.

I'd really appreciate any help you could give...

I asked this on stackoverflow and was recommended to ask it here too...

If the user that the MySQL server does not have permissions to read and write to the files, then the server will not recognize the table. Usually the server runs under the 'mysql' user.

I would first stop your mysql instance before trying to change the ownership.

Once that is done, then (from a non-root user) do sudo chown mysql bsf_session.* in the directory with the files.

You could also try to change the group (sudo chgrp mysql bsf_session.*), however having the files as the same owner as the server is the best option.

If you do not have sudo privileges or root access, then you will need to contact whoever does to have them change the ownership.