Comprehensive troubleshooting of DatabaseMail I am having a problem sending email from one of my servers.

I want to use DatabaseMail and/or msdb.dbo.sp_send_dbmail

I have other production servers that use all the same databaseMail settings and the same SMTP server, and they are all working fine, only this one particular server is not sending emails via Sql Server and I need to find out why.

The first thing I test is the connection to the SMTP server, and for testing that I use Powershell to send an email, and it works fine!!!!

Here is the script as how to send emails using Powershell.

$smtpServer = "200.1.1.223"
$smtpPort = 25
$emailFrom = "donotreply@xxxxx.co.uk"
$emailTo = "mmiorelli@xxxxx.co.uk"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Port = $smtpPort
$subject = "ccatsql"
$body = "test email from ccatsql "
$smtp.Send($emailFrom, $emailTo, $subject, $body)

and that works fine.

the problem that I am having in that server is
DatabaseMail didn't start when an email is queued

when I query the sysmail_event_log I don't see any event related to the emails I have just sent (attempted to).

SELECT * FROM msdb.dbo.sysmail_event_log order by log_date desc

when I query the sysmail_unsentitems I can see all my emails are there, in an unsert state.

SELECT * FROM msdb.dbo.sysmail_unsentitems
ORDER BY send_request_date DESC

This is the piece of code that I am mostly using to test the email sending.
It shows how to send the result of a query by email, but in this case I made the query very simple because I need to get the email working first.

-===========================================================================
-- Failed to initialize sqlcmd library with error number -2147467259
-- that was caused because I had left a piece of rubish code from a previous query
-- and internally it was saying: Invalid column name 'sintMarketID'.
-- but I could only see this using the profile.
--==========================================================================

set deadlock_priority high
set transaction isolation level repeatable read

declare @sub varchar(150)
declare @radhe int
select @sub = '-- Number of rows affected by Hare Krishna job run at '
               + CAST(convert(datetime, getdate(), 100)AS VARCHAR)
--print @sub

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'DBA', -- Change profile name accordingly
        @recipients='mmiorelli@xxxxx.co.uk', -- change mail address accordingly
        @subject = @sub,
        @body_format = 'TEXT',
        @importance='High',
        @sensitivity='Private',
        @file_attachments=NULL,
        @execute_query_database='MSDB',
        @query_no_truncate=0,
        @query_result_header = 1,
        @mailitem_id= @Radhe OUTPUT,
        @query= '

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @SQL VARCHAR(MAX)

SELECT @SQL = ''

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select getdate()
''

EXEC(@SQL)

'

there are similar questions without a conclusion:

DatabaseMail process is shutting down

What is missing?

I checked which .Net versions I had installed

then I installed .NET 3.5 and all started to work nicely.

I believe it is poorly documented (or at least for me) the fact that DatabaseMail needs .NET 3.5.

SQL Server 2016 - Database Mail doesn't work without .NET 3.5

How can I see the current Database Mail configuration?

EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

EXEC msdb.dbo.sysmail_help_account_sp

this link below has also helped me specially when I need to copy the database mail settings from one server into another:

The database mail configuration saved into a temp table

I got the following script that helps troubleshooting databasemail basics.

Pay attention to the comments and run one step at a time.

USE msdb
 GO

-- Check that the service broker is enabled on MSDB.
-- Is_broker_enabled must be 1 to use database mail.
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
-- Check that Database mail is turned on.
-- Run_value must be 1 to use database mail.
-- If you need to change it this option does not require
-- a server restart to take effect.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Database Mail XPs';

-- Check the Mail queues
-- This system stored procedure lists the two Database Mail queues.
-- The optional @queue_type parameter tells it to only list that queue.
-- The list contains the length of the queue (number of emails waiting),
-- the state of the queue (INACTIVE, NOTIFIED, RECEIVES_OCCURRING, the
-- last time the queue was empty and the last time the queue was active.
EXEC msdb.dbo.sysmail_help_queue_sp -- @queue_type = 'Mail' ;

-- Check the status (STARTED or STOPPED) of the sysmail database queues
-- EXEC msdb.dbo.sysmail_start_sp -- Start the queue
-- EXEC msdb.dbo.sysmail_stop_sp -- Stop the queue
EXEC msdb.dbo.sysmail_help_status_sp;

-- Check the different database mail settings.
-- These are system stored procedures that list the general
-- settings, accounts, profiles, links between the accounts
-- and profiles and the link between database principles and
-- database mail profiles.
-- These are generally controlled by the database mail wizard.

EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
--  Check that your server name and server type are correct in the
--      account you are using.
--  Check that your email_address is correct in the account you are
--      using.
EXEC msdb.dbo.sysmail_help_profile_sp;
--  Check that you are using a valid profile in your dbmail command.
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
--  Check that your account and profile are joined together
--      correctly in sysmail_help_profileaccount_sp.
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

-- I’m doing a TOP 100 on these next several queries as they tend
-- to contain a great deal of data.  Obviously if you need to get
-- more than 100 rows this can be changed.
-- Check the database mail event log.
-- Particularly for the event_type of "error".  These are where you
-- will find the actual sending error.
SELECT TOP 100 *
FROM msdb.dbo.sysmail_event_log
ORDER BY last_mod_date DESC;

-- Check the actual emails queued
-- Look at sent_status to see 'failed' or 'unsent' emails.
SELECT TOP 100 *
FROM msdb.dbo.sysmail_allitems
ORDER BY last_mod_date DESC;

-- Check the emails that actually got sent.
-- This is a view on sysmail_allitems WHERE sent_status = 'sent'
SELECT TOP 100 *
FROM msdb.dbo.sysmail_sentitems
ORDER BY last_mod_date DESC;

-- Check the emails that failed to be sent.
-- This is a view on sysmail_allitems WHERE sent_status = 'failed'
SELECT TOP 100 *
FROM msdb.dbo.sysmail_faileditems
ORDER BY last_mod_date DESC

-- Clean out unsent emails
-- Usually I do this before releasing the queue again after fixing the problem.
-- Assuming of course that I don't want to send out potentially thousands of
-- emails that are who knows how old.
-- Obviously can be used to clean out emails of any status.
EXEC msdb.dbo.sysmail_delete_mailitems_sp
  @sent_before =  '2017-09-28',
  @sent_status = 'failed';


Run query every 5 minutes Please find the below query:
select count(*) from v$session;

This has to be run on the database every 5 minutes. What is the right way to achieve this?
The query has to be run every 5 minutes on the same sql*plus screen. Instead of pressing "/" every minute, I wanted to understand if there is a way.
Database/Server details:
SQL> select * from v$version;
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production

SQL> !uname -a
HP-UX ap12121 B.11.11 U 9000/800 ap12121 unlimited-user license

How about using a PL/Sql block:
DECLARE
    v_sessions NUMBER;
BEGIN
    WHILE true LOOP
        SELECT COUNT (*) INTO v_sessions FROM v$session;
        DBMS_OUTPUT.PUT_LINE ('Sessions: ' || v_sessions);
        DBMS_SESSION.SLEEP (300); -- 5 minutes
    END LOOP;
END;

If it really is 8i, use DBMS_LOCK.SLEEP (300) instead.


Pivoting data for stacked charts (grouping and creating new columns for each distinct value)? I have something that looks like this (SQL Fiddle here):

Date        Location    Value
1/1/2012    Baltimore   36
1/1/2012    Houston     36
1/1/2012    Chicago     55
2/1/2012    Baltimore   49
2/1/2012    Houston     58
2/1/2012    Chicago     51
3/1/2012    Baltimore   11
3/1/2012    Houston     13
3/1/2012    Chicago     18

However, I want it to look like this:

Date        Baltimore   Houston     Chicago
1/1/2012    36          36          55
2/1/2012    49          58          51
3/1/2012    11          13          18

How do I group by the Date and create new columns for each distinct Location? I do not know the complete set of possible Locations. I'm working in Oracle 11g Release 2.

Another way to do this if the columns are known is using an aggregate and a CASE statement:

SELECT d,
  sum(case when loc = 'Baltimore' then v else 0 end) as Baltimore,
  sum(case when loc = 'Houston' then v else 0 end) as Houston,
  sum(case when loc = 'Chicago' then v else 0 end) as Chicago
FROM test
group by d
order by d;

See SQL Fiddle with Demo

If you want to do this dynamically, then you can create a procedure to generate the SQL statement:

CREATE OR REPLACE procedure dynamic_pivot(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select d ';

    begin
        for x in (select distinct loc from test order by 1)
        loop
            sql_query := sql_query ||
                ' , sum(case when loc = '''||x.loc||''' then v end) as '||x.loc;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' from test group by d order by 1';

        open p_cursor for sql_query;
    end;
/

Then to call it:

variable x refcursor
exec dynamic_pivot(:x)
print x

the result is:

D                                BALTIMORE    CHICAGO    HOUSTON
------------------------------- ---------- ---------- ----------
01-JAN-12 12.00.00.000000 AM            36         55         36
01-FEB-12 12.00.00.000000 AM            49         51         58
01-MAR-12 12.00.00.000000 AM            11         18         13


Missing 'Microsoft Office 12.0 Access Database Engine' in SSMS I've got several users that need to import / export ACCDB Access files into out of SQL Server.

Unfortunately, I have been unable to get 'Microsoft Office 12.0 Access Database Engine' to show up as a data source despite installing / uninstalling seemingly every different bit version of the driver:

We are running SQL Server 64 bit Enterprise 2012 on Server 2008 R2 64 bit. No Office is installed on the server itself.

I'm curious to see what is the proper configuration, because nothing is working for me! Wondering if I'm missing another component.

Used the following drivers to no avail:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&WT.mc_id=soc-n-%5BTR%5D-loc-%5BServices%5D-%5Bfarukc%5D&id=13255

Also have tried this:

http://www.microsoft.com/en-us/download/details.aspx?id=23734

The driver must be installed on whatever server you are on when you connect. So if you're on your laptop and using Management Studio to connect to SQL Server then the driver needs to be on your laptop.


Why does MongoDB 4.0 require journaling for replica set members using WiredTiger? This is a very interesting change in version 4.0 since checkpoints seem to serve a very similar role to journaling.

  The checkpoint ensures that the data files are consistent up to and including the last checkpoint; i.e. checkpoints can act as recovery points.

OK so checkpoints give me a durable view of data at that last checkpoint. I lose any data after the last checkpoint in the instance of a crash.

  The WiredTiger journal persists all data modifications between checkpoints. If MongoDB exits between checkpoints, it uses the journal to replay all data modified since the last checkpoint.

Which is a little bit of extra durability seeing as the Journal tracks all operations. Its like a list of what has been done that we can reapply after failure.

  The journal record includes any internal write operations caused by the initial write. For example, an update to a document in a collection may result in modifications to the indexes; WiredTiger creates a single journal record that includes both the update operation and its associated index modifications.

Seems like the point of checkpoints was to move fast, but at the risk of losing a minute of data at a time. Whereas the journal is all about accounting for everything. So why have both? Seems like having the Journal kind of overrides any gains that Checkpoints were supposed to give.

Seems like the point of checkpoints was to move fast, but at the risk of losing a minute of data at a time. Whereas the journal is all about accounting for everything. So why have both? Seems like having the Journal kind of overrides any gains that Checkpoints were supposed to give.

Although journal and checkpoint processes both write data to disk, they serve different purposes. Journal writes are fast append-only writes to smaller journal files (up to 100MB), whereas a checkpoint persists changes to the data files (which has more overhead in terms of files and complexity of reconciling updates).

The journal requirement for MongoDB 4.0+ replica set members using WiredTiger is motivated by improvements to replication performance and rollback behaviour:

In MongoDB 3.2+, a w:majority write concern implies j:true (acknowledged writes are confirmed by a majority of data-bearing members and will not be rolled back) if j is not specified. If the journal is not enabled, the alternative is to either acknowledge in-memory writes for j:true (which could potentially lead to rollback) or trigger a checkpoint (which will force syncing changes to data files on every write). Requiring journaling is a stronger guarantee that acknowledged majority writes will not be rolled back, which is important for applications or server features like Change Streams that return data based on majority read concerns.
A WiredTiger performance improvement in MongoDB 4.0+ reduces the write amplification of some data changes that were previously journaled multiple times (data written to the oplog and to the target collection) and allows faster rollback without limitations on data size (prior to MongoDB 4.0 the rollback limit was 300 MB of data before manual intervention was required). However, the new Recovery to a Timestamp (RTT) rollback algorithm relies on the journal to recover the oplog to a consistent point. For more technical details see the Startup Recovery and Rollback descriptions in the MongoDB source repo on GitHub.

If you do not have a replica set configuration it is still possible (but not recommended) to run with the journal disabled in MongoDB 4.0+.

If you attempt to start a MongoDB 4.0+ replica set member using WiredTiger with the journal disabled, mongod will fail to complete the startup sequence and shutdown with a log message like:

Running wiredTiger without journaling in a replica set is not supported. Make sure
you are not using --nojournal and that storage.journal.enabled is not set to 'false'.


Using REGEXP_REPLACE in Oracle Am trying to use backreference expression to take out three subexpressions from a search, and reposition each subexpression as follows:

 SELECT REGEXP_REPLACE('AAA 123 ppp','(^[[:alpha:]]+) ([[:digit:]]) ([[:alpha:]]+)','\3 \1 \2') from dual

However am getting the result as AAA 123 ppp where expecting ppp AAA 123
Where am i getting it wrong?

Missing + after [[:digit:]]

select  regexp_replace('AAA 123 ppp','(^[[:alpha:]]+) ([[:digit:]]+) ([[:alpha:]]+)','\3 \1 \2')
from    dual

  ppp AAA 123


Access denied for user 'root'@'%' I used to access the root user in MySQL just fine. But recently, I am no longer able to.

I am able to login fine :

 mysql -u root -p

Here is the mysql status after login :

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (i686) using readline 6.2

Connection id:      37
Current database:
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.28-0ubuntu0.12.04.3 (Ubuntu)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /var/run/mysqld/mysqld.sock
Uptime:         4 min 16 sec

Threads: 1  Questions: 112  Slow queries: 0  Opens: 191
Flush tables: 1  Open tables:  6  Queries per second avg: 0.437
--------------

But when I want to do any action, such as :

mysql> CREATE DATABASE moyennegenerale;
ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'moyennegenerale'

I understand % is used to signify any host , but my status clearly states localhost. Does somebody have an idea of what might be going on?

I think you have anonymous users

Try running this:

SELECT user,host,password FROM mysql.user WHERE user='';

This will show what anonymous users exist. Most likely, you will see a line with a blank user, host %, and a blank password as shown below:

mysql> select user,host,password from mysql.user;
+-----------+-------------+-------------------------------------------+
| user      | host        | password                                  |
+-----------+-------------+-------------------------------------------+
| lwdba     | 127.0.0.1   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| lwdba     | localhost   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| lwdba     | %           | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root      | localhost   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root      | 127.0.0.1   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| vanilla   | localhost   |                                           |
| mywife    | %           |                                           |
|           | %           |                                           | <<<--- LOOK !!!
| replicant | 10.64.113.% | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| kumar     | %           |                                           |
+-----------+-------------+-------------------------------------------+

So, how did you login? Run this query:

SELECT USER(),CURRENT_USER();

What does this tell you?

USER() reports how you attempted to authenticate in MySQL
CURRENT_USER() reports how you were allowed to authenticate in MySQL

The second function CURRENT_USER() reveals how which anonymous user was used to log in.

What privileges did you have when you logged in?

Please run

SHOW GRANTS;

This will unveil what privileges you had at the time you logged in. The fact that you were blocked from creating a database shows you were not root but some lower-privileged user.

Please clean up your user grants.

Feb 17, 2012 : MySQL : Why are there "test" entries in mysql.db?
Feb 17, 2012 : What is the mysql.db table used for?
Jan 18, 2012 : MySQL error: Access denied for user 'a'@'localhost' (using password: YES)

As for resetting the root password, please do the following:

echo "SET PASSWORD FOR root@localhost=PASSWORD('password');" > /var/lib/mysql/rootpwd.sql
service mysql restart
rm -f /var/lib/mysql/rootpwd.sql

I learned this efficient method from @ShlomiNoach.

Give it a Try !!!


How to determine when REORG with LONGLOBDATA is required (DB2) REORGCHK only identifies when a reorg is required for space reclamation on normal (non-LOB) data. Is there a way of determining how many pages are wasted due to LONG and LOB data that has been logically deleted and thus indicate that a reorg with LONGLOBDATA would be beneficial?

The admin table ADMINTABINFO accurately identifies the number of pages containing LOBDATA but gives no indication of how much of that data is "real" data, and how much has been logically deleted.

eg. Before REORG with LONGLOBDATA column LOB_OBJECT_P_SIZE/32 showed 35328 pages. After the reorg it showed 2176 pages. A substantial saving. But I had no way of knowing that I would save this much until actually running the reorg.

There doesn't seem to be a specific metric that you can use, but indirectly the high ratio of FPAGES to NPAGES and, if you have indexes, INDCARD to NDEL will tell you that there is a significant number of logically deleted rows. Unfortunately, this does not work for tables where rows are UPDATEd but not DELETEd, so I guess you will just want to run regular reorgs on tables where LOB columns are regularly modified.


How do I compare two tables with the same column definitions in different schemas? Any advice would be appreciated. I'm leading my projects initiative to upgrading our ETL software. This can result in data integrity differences. My testing plan is as follows:

create an identical schema, schema B, with the same table definitions as schema A
run all the ETL jobs to populate schema B using the upgraded ETL version
**compare the two schemas and record differences
determine why those differences occured

**So my question is regarding step 3. What technically do I need to do (commands, queries, etc) to compare every field in every row between the two schemas to confirm that they are identical?

Thank you for your time!

If you just want to do a quick check, you can always use EXCEPT in you query to identify if rows in 2 tables are identical.

SELECT 'TABLE1-ONLY' AS SRC, T1.*
FROM (
      SELECT * FROM TABLE1
      EXCEPT
      SELECT * FROM TABLE2
      ) AS T1
UNION ALL
SELECT 'TABLE2-ONLY' AS SRC, T2.*
FROM (
      SELECT * FROM TABLE2
      EXCEPT
      SELECT * FROM TABLE1
      ) AS T2
WITH UR;

If you are using Toad, it has data compare features as well.


SQL Server - Remove all logins that they are asossiated with orphan users I have an SQL Server 2008R2 that had lots of databases.

The procedure was that a logins were created and users were mapped to databases.

Most of these databases have been removed from the server now but it ended up having LOTS of logins that are associated to users mapped to non existing databases.

Is there a batch way to get rid of the these logins and their assosiated users?

Thank you

First of all, Orphaned Users is a term to define database users associated to non-existing logins. According to your explanation, that is not the case for you and your logins.

If databases have been removed from the server, there will be no orphaned user since they will be gone with the database, there will be logins which have no associated users and probably not in use.

To find these logins with no database mapping please look here!

However, To get rid of these logins with no mapping you need to be sure that there is no activity, you can try several methods such as Extended Events, Profiler, Server Audit etc to capture all login activity, parse login events and detect unused logins.
Extended events would be the best one since it is faster and requires less resource. I have encountered the same problem and developed a solution for this years ago with Extended Events. The script creates an SQL Agent job which creates everything necessary dynamically.An extended events to capture all login events on server writes events to a file on C drive which is then parsed by the job and logs everything into a summary table.
I can upload the script if you want but unfortunately there is no documentation or blog post about it (yet).


What's a good way to add a PRIMARY KEY to a large InnoDB table? I have a table with around 30 million rows in MySQL 5.5 using the InnoDB storage engine.  This table has a foreign key that links to another table with around 3 million rows.

I want to add a primary key to the larger table, but am unsure of how best to go about this.  There's no existing column (or set of columns) I can use for this, so how should I go about this?

I have seen some references to an internal InnoDB record ID, but have not found if this is accessible.

Otherwise, perhaps I will need to dump and reload the data, adding the id in manually.

Thanks.

this may be the wrong way to go about it, but..

couldn't you just alter the table structure,

add a primary key column to the table

then...

write a quick script to go through the entire table

adding an autoincrementing value to the new column?


How to deal with Users Needing to Enter Mixed Data Types in MS SQL Server I have a form in a C# application that I can't change with a data table bound to a query. The user enters a mark for a student's task or 'A' for absent.

However, the person who designed this had the underlying table as all varchars, when really it would be nice to have decimals for marks and char for 'A'. To this end, I thought I would make an instead of update trigger that moves 'A' values to an absences table and puts marks in the existing marks table. I can then change the data type in my marks table to decimal.

This is my trigger code:

INSTEAD OF UPDATE
AS
BEGIN

SET NOCOUNT ON;

IF (TRY_CONVERT(varchar,(SELECT weekReviewWk1 FROM inserted)) = '' OR (SELECT weekReviewWk1 FROM inserted) = NULL)
BEGIN

    UPDATE assessmentData
        SET assessmentData.weekReviewWk1 = NULL
        FROM inserted
        WHERE assessmentData.studentID = inserted.studentID
    UPDATE assessmentAbsences
        SET weekReviewWk1 = NULL
        FROM inserted
        WHERE assessmentAbsences.studentID = inserted.studentID
    RETURN
END

IF (TRY_CONVERT(varchar,(SELECT weekReviewWk1 FROM inserted)) = 'A')
BEGIN
    UPDATE assessmentAbsences
        SET assessmentAbsences.weekReviewWk1 = inserted.weekReviewWk1
        FROM inserted
        WHERE assessmentAbsences.studentID = inserted.studentID
    UPDATE assessmentData
        SET weekReviewWk1 = NULL
        FROM inserted
        WHERE assessmentData.studentID = inserted.studentID
    RETURN
END

BEGIN

    UPDATE assessmentData
        SET weekReviewWk1 = TRY_CONVERT(decimal(4,1), (SELECT weekReviewWk1 FROM inserted))
        FROM inserted
        WHERE assessmentData.studentID = inserted.studentID
    UPDATE assessmentAbsences
        SET weekReviewWk1 = NULL
        FROM inserted
        WHERE assessmentAbsences.studentID = inserted.studentID
END
END

The trigger works for decimal values, however I get "Error converting data type varchar to numeric" when I attempt to update the table with 'A'. The absences table has char as the type and the value should just be inserted into that, with nothing in the actual marks table, thus not violating the decimal type constraint. Why is this happening?

In addition, is there a better way to handle this scenario?

The presence of an INSTEAD OF INSERT trigger does not prevent SQL Server from validating the data-types of the INSERT statement against the data-types of the target columns.

The error message:

  Msg 245, Level 16, State 1, Line 70
  Conversion failed when converting the varchar value 'A' to data type numeric.

indicates that SQL Server knows it can't even try to insert 'A' into the numeric field.  The trigger hasn't even ran at that point.

Having said that, you can use a trigger to put the absentee info into a another table, you just can't change the column type to a numeric value.

Here is an example:

USE tempdb;

/* drop the test tables, if they already exist */
IF OBJECT_ID('dbo.VarData') IS NOT NULL
DROP TABLE dbo.VarData;
IF OBJECT_ID('dbo.NumData') IS NOT NULL
DROP TABLE dbo.NumData;
GO

CREATE TABLE dbo.NumData
(
    NumDataID INT NOT NULL
        CONSTRAINT PK_NumData
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , Grade VARCHAR(30) NULL /* STILL a varchar value */
) ON [PRIMARY];

CREATE TABLE dbo.VarData
(
    VarDataID INT NOT NULL
        CONSTRAINT PK_VarData
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , NumDataID INT NOT NULL
        CONSTRAINT FK_VarData_NumDataID
        FOREIGN KEY
        REFERENCES dbo.NumData(NumDataID)
    , AbsentInd VARCHAR(1) NOT NULL
) ON [PRIMARY];
GO

/* drop-and-create the trigger */
IF OBJECT_ID('NumData_Insert',N'TR') IS NOT NULL
DROP TRIGGER NumData_Insert;
GO
CREATE TRIGGER NumData_Insert
ON dbo.NumData
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #NumDataInsert
    (
        NumDataID INT NOT NULL
    );
    /* only insert numeric values into the main table */
    INSERT INTO dbo.NumData (Grade)
    SELECT i.Grade
    FROM inserted i
    WHERE TRY_CONVERT(NUMERIC(4,2), i.Grade) IS NOT NULL;
    /* insert a NULL row into the main table, to obtain the NumDataID
        which allows us to match the Absentee rows to the associated row
        in the main table
     */
    INSERT INTO dbo.NumData (Grade)
    OUTPUT inserted.NumDataID INTO #NumDataInsert
    SELECT NULL
    FROM inserted i
    WHERE TRY_CONVERT(NUMERIC(4,2), i.Grade) IS NULL;

    /* insert any Absentee rows into the VarData table */
    INSERT INTO dbo.VarData (NumDataID, AbsentInd)
    SELECT ndi.NumDataID, 'Y'
    FROM #NumDataInsert ndi;

    SET NOCOUNT OFF;
END
GO

INSERT INTO dbo.NumData (Grade)
VALUES (97.2)
    , (42.0);

INSERT INTO dbo.NumData (Grade)
VALUES ('A');

SELECT *
FROM dbo.NumData;
SELECT *
FROM dbo.VarData;

The two SELECT statements above produce this output:

Even though this does not allow you to actually change the data-type of the column, it does allow you to prevent entering incorrect data into that column.  This will provide time for you to modify the code in the program to only enter numeric values into the NumData table, while preventing new "Absentee" rows being inserted into it.  Once you get the code changed in the client program, you'd ALTER the column to be numeric, and remove the trigger.

The below code still fails since SQL Server cannot coalesce alphabetic values into numeric values:

/* these still fail, since 'A' cannot be converted to match the
   datatype of the value, 97.2, which is numeric */
INSERT INTO dbo.NumData (Grade)
VALUES ('A')
    , (97.2);

INSERT INTO dbo.NumData (Grade)
VALUES (97.2)
    , ('A');

This will work, which is likely how the client program is inserting rows:

INSERT INTO dbo.NumData (Grade)
VALUES ('97.2')
    , ('A');


Mariadb Galera Cluster using Xtradb backup is not working I am trying out the first test of MariaDB Galera cluster setup without success.

This is the error in the log

innobackupex: Created backup directory /tmp
tar: -: Cannot write: Broken pipe
tar: Error is not recoverable: exiting now
innobackupex: 'tar chf -' returned with exit code 2.
innobackupex: got a fatal error with the following stacktrace: at /usr//bin/innobackupex line 4882
        main::backup_file_via_stream('/tmp', 'backup-my.cnf') called at /usr//bin/innobackupex line 4931
        main::backup_file('/tmp', 'backup-my.cnf', '/tmp/backup-my.cnf') called at /usr//bin/innobackupex line 4955
        main::write_to_backup_file('/tmp/backup-my.cnf', '# This MySQL options file was generated by innobackupex.\x{a}\x{a}# T...') called at /usr//bin/innobackupex line 3762
        main::write_backup_config_file('/tmp/backup-my.cnf') called at /usr//bin/innobackupex line 3689
        main::init() called at /usr//bin/innobackupex line 1566
innobackupex: Error: Failed to stream '/tmp/backup-my.cnf': 2 at /usr//bin/innobackupex line 4882.

Joiner my.cnf

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]
datadir=/var/lib/mysql
log_error=/etc/log/mysql/mysql.err
log_bin=/etc/log/mysql/mysql-bin.log
slow_query_log_file=/etc/log/mysql/mysql-slow.log
# this is only for the mysqld standalone daemon
[mysqld]
user=mysql
innodb_log_file_size=100M

# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://1.1.1.1 #master
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
wsrep_cluster_name=rms
wsrep_node_address=1.1.1.2
wsrep_node_name=slave
wsrep_sst_method=xtrabackup
wsrep_sst_auth=sst:sst
# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.0 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.0]

Donor my.cnf

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]
datadir=/var/lib/mysql
log_error=/etc/log/mysql/mysql.err
log_bin=/etc/log/mysql/mysql-bin.log
slow_query_log_file=/etc/log/mysql/mysql-slow.log

wsrep_sst_receive_address=1.1.1.2
# this is only for the mysqld standalone daemon
[mysqld]
user=mysql
innodb_log_file_size=100M
#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
wsrep_cluster_name=rms
wsrep_node_address=1.1.1.1
wsrep_node_name=master
wsrep_sst_method=xtrabackup
wsrep_sst_auth=sst:sst

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.0 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.0]

I have been trying this without success from past 10 hours, please any help is appreciated.
Using CentOS 6

When I execute this in donor it's working fine [to test xtrabackup] innobackupex --user=sst --password=sst /tmp/

In my experience, it seems one failed run can leave fragments of config that block future runs.

Case in reference: Xbstream rather than tar is used, although the traceback is consistent in that it halts on writing to backup-my.cnf.

First run fails because my_print_defaults is reading from the wrong file, and this file was stored as the backup.

Once the --defaults-file flag is added to innobackupex, suddenly there is output on the other side of the xbstream.

xbstream: Can't create/write to file '././backup-my.cnf' (Errcode: 17 - File exists)
xbstream: failed to create file.

I believe this is an innobackupex safety feature and tar may be making the same checks as xbstream. I would have liked to comment on this rather than answer it but lack the reputation.


How to know or recover my pg_dump password? I would like to backup my Postgre db using pg_dump. However, the first question asked by the prompt is the password.
But I have no idea what it is. Indeed, it is different from my admin account password, and I don't know where to find it or recover it.

pg_dump does not have a password specific to itself.  You run pg_dump as a certain database user (often the user spelled 'postgres', but not always), and authenticate however that user would authenticate for any other (non-pg_dump) connection.  So your question really has nothing to do with pg_dump.

You probably can't recovery the password (unless you are willing to crack md5 hashes to brute force it--which is really only feasible with poor passwords or with big bucks), but you can use your "admin account" to reset the password to something you do know.  How you do that depends on whether "admin account" means a database superuser account, or an OS account for the OS user who owns and runs the database files.  Also, it might depend on your settings in pg_hba.conf.


How do I round time to the upper multiple of an arbitrary time interval? Example:

If the current time is 2018-05-17 22:45:30 and the desired interval is INTERVAL '5 minute', then the desired output is 2018-05-17 22:50:00.
If the current time is 2018-05-17 22:45:30 and the desired interval is INTERVAL '10 minute', then the desired output is 2018-05-17 22:50:00.
If the current time is 2018-05-17 22:45:30 and the desired interval is INTERVAL '1 hour', then the desired output is 2018-05-17 23:00:00.
If the current time is 2018-05-17 22:45:30 and the desired interval is INTERVAL '1 day', then the desired output is 2018-05-18 00:00:00.

Assuming data type timestamp. Some details are different for date or timestamptz.
A general solution for any time interval can be based on the epoch value and integer division to truncate. Covers all your examples.
The special difficulty of your task: you want the ceiling, not the floor (which is much more common). Exercise care with lower and upper bounds to avoid corner case bugs: you don't want to increment exact floor values. (Or so I assume.)
For common time intervals built into date_trunc() (like 1 hour and 1 day in your examples) you can use a shortcut.
The definition of days depends on the time zone setting of the session with timestamptz (but not with timestamp).
A natural alternative is with ceil(). A bit slower in my tests, but cleaner.
Short demo

-- short demo
WITH t(ts) AS (SELECT timestamp '2018-05-17 22:45:30')  -- your input timestamp
SELECT t2.*
FROM  (SELECT *, ts - interval '1 microsecond' AS ts1 FROM t) t1 -- subtract min time interval 1 µs
     , LATERAL (
   VALUES
      ('input timestamp' , ts)
    , ('5 min' , to_timestamp(trunc(extract(epoch FROM ts1))::int / 300 * 300 + 300) AT TIME ZONE 'UTC')
    , ('10 min', to_timestamp(ceil (extract(epoch FROM ts)/ 600) * 600) AT TIME ZONE 'UTC') -- based on unaltered ts!
    , ('hour'  , date_trunc('hour', ts1) + interval '1 hour')
    , ('day'   , date_trunc('day' , ts1) + interval '1 day')
   ) t2(interval, ceil_ts);

interval        | ceil_ts
:-------------- | :------------------
input timestamp | 2018-05-17 22:45:30
5 min           | 2018-05-17 22:50:00
10 min          | 2018-05-17 22:50:00
hour            | 2018-05-17 23:00:00
day             | 2018-05-18 00:00:00

The "trick" for the '5 min' calculation is to subtract the minimum time interval of 1 µs before truncating, and then add the respective time interval to effectively get the ceiling. EXTRACT() returns the number of seconds in the timestamp, a double precision number with fractional digits down to microseconds. We need trunc() because the plain cast to integer would round, while we need to truncate.
This way we avoid incrementing timestamps that fall on the upper bound exactly. It is slightly dirty, though, because the minimum time interval is an implementation detail of current Postgres versions. Very unlikely to change though. Related:

Ignoring time zones altogether in Rails and PostgreSQL

The '10 min' calculation is simpler with ceil(), we don't need to shift bounds by subtracting 1 µs. Cleaner. But ceil() is slightly more expensive in my tests.
Extended test case

WITH t(id, ts) AS (
   VALUES
     (1, timestamp '2018-05-17 22:45:30')  -- your input timestamps here
   , (2, timestamp '2018-05-20 00:00:00')
   , (3, timestamp '2018-05-20 00:00:00.000001')
   )
SELECT *
FROM  (SELECT *, ts - interval '1 microsecond' AS ts1 FROM t) t1  -- subtract min time interval 1 µs
     , LATERAL (
   VALUES
      ('input timestamp' , ts)
    , ('5 min'  , to_timestamp(trunc(extract(epoch FROM ts1))::int / 300 * 300 + 300) AT TIME ZONE 'UTC')
    , ('10 min' , to_timestamp(ceil (extract(epoch FROM ts)/ 600) * 600) AT TIME ZONE 'UTC') -- based on unaltered ts!
    , ('hour'   , date_trunc('hour', ts1) + interval '1 hour')
    , ('day'    , date_trunc('day' , ts1) + interval '1 day')
    , ('alt_day', ts1::date + 1)
   ) t2(interval, ceil_ts)
ORDER  BY id;

id | ts                         | ts1                        | interval        | ceil_ts
-: | :------------------------- | :------------------------- | :-------------- | :-------------------------
 1 | 2018-05-17 22:45:30        | 2018-05-17 22:45:29.999999 | input timestamp | 2018-05-17 22:45:30
 1 | 2018-05-17 22:45:30        | 2018-05-17 22:45:29.999999 | 5 min           | 2018-05-17 22:50:00
 1 | 2018-05-17 22:45:30        | 2018-05-17 22:45:29.999999 | 10 min          | 2018-05-17 22:50:00
 1 | 2018-05-17 22:45:30        | 2018-05-17 22:45:29.999999 | hour            | 2018-05-17 23:00:00
 1 | 2018-05-17 22:45:30        | 2018-05-17 22:45:29.999999 | day             | 2018-05-18 00:00:00
 1 | 2018-05-17 22:45:30        | 2018-05-17 22:45:29.999999 | alt_day         | 2018-05-18 00:00:00
 2 | 2018-05-20 00:00:00        | 2018-05-19 23:59:59.999999 | input timestamp | 2018-05-20 00:00:00
 2 | 2018-05-20 00:00:00        | 2018-05-19 23:59:59.999999 | 5 min           | 2018-05-20 00:00:00
 2 | 2018-05-20 00:00:00        | 2018-05-19 23:59:59.999999 | 10 min          | 2018-05-20 00:00:00
 2 | 2018-05-20 00:00:00        | 2018-05-19 23:59:59.999999 | hour            | 2018-05-20 00:00:00
 2 | 2018-05-20 00:00:00        | 2018-05-19 23:59:59.999999 | day             | 2018-05-20 00:00:00
 2 | 2018-05-20 00:00:00        | 2018-05-19 23:59:59.999999 | alt_day         | 2018-05-20 00:00:00
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00        | input timestamp | 2018-05-20 00:00:00.000001
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00        | 5 min           | 2018-05-20 00:05:00
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00        | 10 min          | 2018-05-20 00:10:00
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00        | hour            | 2018-05-20 01:00:00
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00        | day             | 2018-05-21 00:00:00
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00        | alt_day         | 2018-05-21 00:00:00

db<>fiddle here
I added an alternative shortcut for full days: ts1::date + 1. The cast to date truncates to the full day and we can add integer 1 to add a day.
Function wrapper
You later disclosed you work with timestamptz, so we can drop AT TIME ZONE from the expression.
In my tests declaring the function STABLE yielded best performance because it allowed function inlining. I would have expected IMMUTABLE to be best, but that declaration is more picky about what's allowed inside to be inlined. Related:

Function Performance

A bit faster in my tests:
CREATE OR REPLACE FUNCTION f_tstz_interval_ceiling2(_tstz timestamptz, _int_seconds int)
  RETURNS timestamptz AS
$func$
SELECT to_timestamp(trunc(extract(epoch FROM ($1 - interval '1 microsecond')))::int / $2 * $2 + $2)
$func$  LANGUAGE sql STABLE;

Cleaner IMO:
CREATE OR REPLACE FUNCTION f_tstz_interval_ceiling1(_tstz timestamptz, _int_seconds int)
  RETURNS timestamptz AS
$func$
SELECT to_timestamp(ceil(extract(epoch FROM $1) / $2) * $2)
$func$  LANGUAGE sql STABLE;

Call:
SELECT f_tstz_interval_ceiling1(my_tstz, 600);  -- 600 = seconds in 10 min

For convenience, you could overload each function with an alternative taking an interval as $2:
CREATE OR REPLACE FUNCTION f_tstz_interval_ceiling1(_tstz timestamptz, _interval interval)
  RETURNS timestamptz LANGUAGE sql STABLE AS
'SELECT f_tstz_interval_ceiling1($1, extract(epoch FROM $2)::int)';

Just invoking the first version with extracted seconds. Then you can also call:
SELECT f_tstz_interval_ceiling1(my_tstz, interval '10 min');


How to get first and last value by same item in sequence, with possibility of the same item occurring again Similar to this question but with the possibility that the same name can appear again in the following rows.

I have this table:

id name     value
1  Daniel   3
2  Daniel   7
3  Daniel   2
4  Carol    9
5  Carol    4
6  Carol    9
7  Ray      5
8  Charles  1
9  Daniel   6
10 Daniel   5
11 Daniel   5

I would like to get the first and the last value (ordered by id) in a table like:

name    value_1st  value_last
Daniel  3         2
Carol   9         9
Ray     5         5
Charles 1         1
Daniel  6         5

WITH
cte1 AS ( SELECT *,
                 CASE WHEN name = LAG(name) OVER (ORDER BY id)
                      THEN 0
                      ELSE 1
                      END the_same
          FROM test ),
cte2 AS ( SELECT *, SUM(the_same) OVER (ORDER BY id) group_num
          FROM cte1 )
SELECT DISTINCT
       name,
       FIRST_VALUE(value) OVER (PARTITION BY group_num ORDER BY id) value_1st,
       FIRST_VALUE(value) OVER (PARTITION BY group_num ORDER BY id DESC) value_last
FROM cte2;

fiddle


Database design - many to many I have one MySQL table which contains:

a question
4 possible answers separated by a comma
a number (0-3) representing the correct answer.
unique id

with about 2000 entries.

I also have another table of users. The goal of each user is to solve all of the questions on the website.

I need to be able to randomly pick about 30 questions that the user hasn't answered yet.

How do you suggest I should store data about the questions the user has already solved?

At first I thought about a table with | user_id | - | question_id | (so each time the user solves a question correctly a new row will be added), but I don't know how I'll be able to pick 30 rows that he hasn't answered yet..

Considering that the question is labeled database-design I will offer a solution without that CSV field. Even if you decide to keep it, this may offer some ideas.

Question contains all possible questions.
Answer has all possible answers (to any question)
QuestionAnswer contains all QA combinations offered, note that there must exist a default answer for "I do not know".
UserQA contains questions user answered.
Note that rule "There can be one and only one correct answer for each question" (IsCorrect = 'Y'), has to be implemented on the application level or as a stored procedure/trigger.

To get questions that a user with specific_UserId has not yet answered

select  qq.QuestionID
from Question as qq
where not exists (select 1
                    from UserQA as xx
                   where xx.QuestionID = qq.QuestionID
                     and xx.UserID     = specific_UserId );

Or, better if there may be some new questions which do not have defined answers yet

select distinct qq.QuestionID
from QuestionAnswer as qq
where not exists (select 1
                    from UserQA as xx
                   where xx.QuestionID = qq.QuestionID
                     and xx.UserID     = specific_UserId );


SQL Server uses more memory than maximum setting Recently I have added more memory to my VM which has SQL Server installed (now it has 32 GB).

I have set the limit in SQL to 24 GB.

A few days later I see in my process window that the SQL Server process is using 26 GB.

Why is the limit overridden? Will this process increase even more?

To contribute a little more to this, there is a post on dba.stackexchange.com that explains this situation a little more: SQL Server Max Memory not limiting use of RAM.  Also, in the answers on that question, Mark Storey-Smith recommends reading Brent Ozar's article "A Sysadmin's Guide to Microsoft SQL Server Memory" to further one's understanding on how Microsoft SQL Server handles memory.  I second that recommendation.


Postgres record locking issue - could asynchronous UPDATES be a solution? Is it possible (and sensible) to issue asynchronous UPDATEs to a postgres table?

Here’s the issue. I have two process, both of which access the same table.

Process “A” loads vast quantities of external records into a postgres table, this involves a trigger that updates a summary table (the summary table is the one involved in the conflict)

Process “A” is quite critical, by tweaking the number of records in each INSERT statement, and the number of INSERTS before issuing a COMMIT, I can insert about 4000 records in a single transaction that takes between 5 and 8 seconds.

Process “B” reads data from the database, does a lot of number crunching on the data and then UPDATEs the summary table. The problem is that although the reading and processing of the data can be achieved in milliseconds, the UPDATEing of the summary table typically takes  4 to 5 seconds to wait for the process “A” transaction to release it’s locks on the summary table. This is very in-efficient and dramatically slows down the overall process.

The following may (or may not) also be relevant.

Process “A” updates only fields “A”, “B” and “C” in the summary
table.
Process “B” updates only fields “D”, “E” and “F” in the summary
table.
Both process “A” and “B” only update the summary table by
explicitly identifying single rows via the unique primary key.
No other processes read or update the summary table.
Only the latest version (for any one record) of the values in “D”, “E” and “F” are relevant.

Bearing in mind the above, is it possible for process “B” to issues it’s UPDATES to the summary table asynchronously (by this I mean issue the UPDATE, and then carrying on with the next iteration of the process without waiting for the response to the UPDATE). If it is possible, how? And perhaps more importantly, is it sensible?

Thanks

Having thought a bit more about the problem, I realise there's actually a relatively simple solution. Since each process only updates its 'own' fields the solution is to split the data into two tables allowing each half to be updated by it's own process and any SELECTs can be done by joining on the common primary key.


How to find out "InnoDB Write buffer efficiency"? Is there any way to find out that "InnoDB Write buffer efficiency" without tools like mysqltuner?

I can't find it in INNODB STATUS or information_schema.

Why do you need to know it?  There is no actionable information in the statistic.

Make innodb_buffer_pool_size about 70% of available RAM (assuming more than 4GB of RAM); that will be optimal.

Look for slow queries and improve them; that will cut back on performance problems.

Here are some things you can get from SHOW GLOBAL STATUS and SHOW VARIABLES:

Innodb_pages_written / Innodb_buffer_pool_write_requests -- Write requests that had to hit disk -- <15% is 'good'
Innodb_buffer_pool_wait_free / Innodb_buffer_pool_write_requests -- Should not have to wait to get into the buffer pool. -- <1% is 'good'
Innodb_buffer_pool_pages_flushed / Uptime -- Writes actually going to disk
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) -- Buffer pool writes per query


Deadlocks in Mariadb We have hit  deadlocks in 10.1.25-MariaDB-1~trusty. Looks like it has something to do with select for update queries. But the deadlock increases when number of tps increases. Is there any alternative to fix this from DB end or from application end to change the logic?

A deadlock is effectively two concurrency transactions updating the same data. This need to be handled at application level because there's no obvious solution that always works without accounting for the business case.


Does a database backup reduce size/increase free space of the log file? I have a SQL Server database where the log file is massive and keeps growing every day.

I've archived data from the database and then done a full backup.  I was expecting the log to then be basically "empty".  e.g. It might be the same physical size, but now have lots of free space.  However, it's currently only got 0.1% free.

Any ideas why there is still almost no free space in the log?

Got a SQL database and the log file is massive and keeps growing every day.

Log files growth depends on Recovery Model of a database and transaction log backups.
if database is not critical and data loss is not an issue in any case then it should be in SIMPLE recovery mode. Which allows SQL Server to reuse log space when a transaction is committed and log growth remains in limits.
But if database is critical and point in time recovery can be a requirement then database must be in FULL Recovery mode. Which is an indication for SQL Server that keep all the log in transaction log file and don't reuse, until it is backed up (transaction log backup).

  I've archived data from the database and then done a full backup...I
   was expecting the log to then be basically "empty". e.g.* Full backup or even Transaction Log backup don't left log file empty.
  Transaction log backup only mark log file space to reusable but still
  file size remains same.

If archiving means you have removed some data after fetching some data and keeping it in any other format. Then one must keep in mind that deleting data never reduce physical size of data or log files, though space could be available for reuse by database.


No shardIdentity document - MongoDB Having a sharded cluster in production which has been running for a good year now without issues. Everything is on 4.0, classic 3-3-3(config) + 2 mongos.

While revisiting our back-up strategy i stumped upon this step.

I tried finding that value on every node (mongos, primaries, secondaries) and none of them have a shardIdentity.

mongos> db.system.version.find()
{ "_id" : "featureCompatibilityVersion", "version" : "4.0" }
{ "_id" : "authSchema", "currentVersion" : 5 }

This thing looks important and is part of STARTUP2 phase.

For the sake of testing and documenting our back-ups, i followed all the steps from the "Restore Sharded Cluster" documentation. At the end of it:

sh.status() is looking good, i'm seeing the shards
rs.status() is also good
A standalone mongod gives me the proper stats on restored data
mongod output seems fine..

But then db.coll.stats() fails with :

{
        "ok" : 0,
        "errmsg" : "Cannot accept sharding commands if sharding state has not been initialized with a shardIdentity document",
        "code" : 203,
        "codeName" : "ShardingStateNotInitialized",
        "operationTime" : Timestamp(1548184216, 1),
        "$clusterTime" : {
                "clusterTime" : Timestamp(1548184216, 1),
                "signature" : {
                        "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                        "keyId" : NumberLong(0)
                }
        }
}

And i can see this error from mongod:

I CONTROL  [LogicalSessionCacheReap] Sessions collection is not set up; waiting until next sessions reap interval: sharding state is not yet initialized

So far i tried:

sh.enableSharding() returns true (1) but doesn't change anything
Been through https://github.com/mongodb/mongo/wiki/Sharding-Internals#sharding-component-initialization
Double checked on our production cluster and no shardIdentity there.

So, how does one enforce a shardIdentify or generate one? How do you find out the "shard name" because i couldn't find such a thing. And, well, why would sh.status() give the proper result but cannot associate its shards with the current data?

Thanks,

At first I didn't want to mess around with the admin database but it seemed it was the only thing to do.

Gather information

sh.status() on a mongos will give the shards name needed and the shard cluster id.
Something like that:

"clusterId" : ObjectId("3b743bf3134df1f277980ard")
...
shards:
        {  "_id" : "shardname1",  "host" : "[host:port]",  "state" : 1 }
        {  "_id" : "shardname2",  "host" : "[host:port]",  "state" : 1 }

For each replica set

Get on the primary, select the admin database

Then:

db.system.version.insert({
    "_id" : "shardIdentity",
    "clusterId" : ObjectId("3b743bf3134df1f277980ard"),
    "shardName" : "REPLICA_SHARD_NAME",
    "configsvrConnectionString" : "conf/[host]"
})

See Shard Aware.

Reboot all replica set members


Is it possible to shrink an .MDF file on a drive with low free space? Is there a way to shrink mdf file of database in SQL Server 2008 without using additional space?

Total space in hard disk is 136 GB, the .MDF file size is 124 GB, and the log file size is 2 GB.  I have only 12 GB free space and while running the shrink command on the  .MDF file, logs are growing and consume the free space -- low disk spaces throws the shrinking into a no respond state.

Is there another method to shrink this file without using additional disk space?

To summarize the answers here and to inject my own advice:

1: Blindly shrinking a data file is not the best route. Your data is your data and it is characterized by this size. Unless you are planning on deleting large chunks of data, shrinking is not going to do much of anything. As @billinkc pointed out, simply shrinking the file will not give you any sort of appreciable gain.

2: Running this is going to make your log file grow. Apparently you have your data and log on the same drive. I'd recommend against this for many reasons including space and IO contention.

3: You can certainly create a new data file and put that on a different volume. You could "archive" old data to free space in the current .mdf, or you could leave the current data file as-is and it would become an archive, of sorts.

4: I'm guessing that there are other things on this volume as well. I would move those things as quickly as I could in order to free up space.

If this is enterprise data, I'd push for more disk. This would allow you to separate your data and log files. Additionally, outside of just deleting data, you're going to need more disk to implement Brandon's suggestion anyway.


How to repair all indexes in a MySQL database? My database got corrupted, and now the php app is throwing this error:

Invalid query: Can't find FULLTEXT index matching the column list

The error is not very useful, since it doesn't say what column/table is causing it.
Is there a way to regenerate FULLTEXT indexes on all columns in all tables within a specific database?

Thanks

Perhaps you should look for all tables that have fulltext indexes

SELECT DISTINCT table_schema,table_name
FROM information_schema.statistics
WHERE index_type='FULLTEXT';

To individually remake all tables that have FULLTEXT indexes

REPAIR_SQL_SCRIPT=RemakeTables.sql
SQL="SELECT"
SQL="${SQL} CONCAT('ALTER TABLE ',db,'.',tb,' ENGINE=',eng,';')"
SQL="${SQL} FROM (SELECT A.table_schema db,A.table_name tb,B.engine eng"
SQL="${SQL} FROM (SELECT DISTINCT table_schema,table_name"
SQL="${SQL} FROM information_schema.statistics WHERE index_type='FULLTEXT') A"
SQL="${SQL} INNER JOIN information_schema.tables B"
SQL="${SQL} USING (table_schema,table_name)) AA"
mysql -uroot -ANe"${SQL}" > ${REPAIR_SQL_SCRIPT}
cat ${REPAIR_SQL_SCRIPT}

If you are OK with the contents of RemakeTables.sql, then execute it:

mysql -uroot < ${REPAIR_SQL_SCRIPT}

CAVEAT : All indexes are remade as well


Is it possible to put mdf and ldf to NAS (Synology) I'm planning to purchase NAS (synology) for personal use. I'm working also as developer, it is possible to put my test database (SQL server data and log) to NAS? This just for personal and for development purpose. Because I have two pc setup on 1st floor and 2nd floor of our house, I just don't want to copy and paste the database every now and then when I go to my pc to another. Or do you have any suggestion?

You should be able to store the files on the NAS.
A bigger problem will be moving between PCs. Each machine will be its own instance of SQL Server. It will not be possible for both instances to own the database files at the same time. You will have to attach the file to one instance, work on that machine, detach from that machine, go to the other and attach the files to that instance before you can work on the DB.
A simpler approach would would be to have one of your boxes (the more powerful one) own the files permanently. Use the other box to remotely communicate with the owning box. Use a remote desktop session, or simply have SSMS (or other client tool) resolve to the host\instance name.


How to identify consonant/vowel pattern matches in MySQL? Evening all,
I'm having a bit of trouble with a MySQL table full of vocabulary words today. I need to be able to quickly retrieve words with the same arrangement of consonants and vowels, so my thinking is something structured along these lines with about 300k rows:

CREATE TABLE words(
 `value` VARCHAR(100),
 `pattern` VARCHAR(100)
);

To populate a small fraction of the contents:

INSERT INTO words(`value`) VALUES
  ('aardvark'),
  ('aardvarks'),
  ('aardwolf'),
  ('aardwolves'),
  #...
  ('mortal'),
  ('mortals'),
  #...
  ('posted'),
  ('posteen'),
  #...
  ('zymotoxic'),
  ('zymurgies');

The following query should return "mortal" and "posted":

SELECT `value` FROM words WHERE `pattern` = '101101';

because '1' represents a consonant and '0' a vowel. I'm aware that MySQL doesn't have a regexp_replace equivalent, so I'm wondering what's the best way to populate the pattern field? I'm assuming it's not the deplorably slow:

UPDATE words SET `pattern` = REPLACE(`value`, 'a', '0');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'b', '1');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'c', '1');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'd', '1');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'e', '0');
#...
UPDATE words SET `pattern` = REPLACE(`pattern`, 'z', '1');

Currently trying a stored procedure with a cursor looping through the words table and updating each pattern one by one but, perhaps unsurprisingly, that's no quicker. Am I missing something really obvious?

Thanks in advance for any help you're able to give me.

Edit: added the stored procedure approach. This is quicker but not by much (0.7 seconds per thousand rows, which adds up) but it gets the job done.

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `set_word_pattern`()
BEGIN
  DECLARE l_last_row INT DEFAULT 0;
  DECLARE temp_word VARCHAR(100);
  #DECLARE temp_char CHAR(1);
  DECLARE temp_pattern_word VARCHAR(100);

  DECLARE c_traverse_words CURSOR FOR SELECT `value` FROM words WHERE pattern = "" LIMIT 20000;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row = 1;

  OPEN c_traverse_words;
  cursor_loop: LOOP
    FETCH c_traverse_words INTO temp_word;
    IF l_last_row = 1 THEN
      LEAVE cursor_loop;
    END IF;
    SET temp_pattern_word = get_pattern(temp_word);
    UPDATE words SET `pattern` = temp_pattern_word WHERE `value` = temp_word;
  END LOOP cursor_loop;
  CLOSE c_traverse_words;

  SELECT "Done"; #temp_pattern_word;
END

and the function it calls:

DELIMITER $$

CREATE DEFINER=`root`@`%` FUNCTION `get_pattern`(new_word VARCHAR(100)) RETURNS varchar(100) CHARSET utf8
BEGIN
  DECLARE temp_char CHAR(1);
  DECLARE temp_pattern_word VARCHAR(100);

  DECLARE i INT;

  SET temp_pattern_word = "";
  SET i = 1;

  WHILE(i <= CHAR_LENGTH(new_word)) DO
    SET temp_char = MID(new_word, i, 1);

    IF temp_char IN ("a", "e", "i", "o", "u") THEN
      SET temp_pattern_word = CONCAT(temp_pattern_word, "0");
    ELSE
      SET temp_pattern_word = CONCAT(temp_pattern_word, "1");
    END IF;
    SET i = i + 1;
  END WHILE;
RETURN temp_pattern_word;
END

To do it in one pass:

UPDATE ...
    SET pattern =
        REPLACE(
        REPLACE(
        ...
        REPLACE(value
                'a', '0')
                'b', '1')
                ...
                'z', '1');

Doesn't your table need

PRIMARY KEY(value),
INDEX(pattern)

There is probably a way to convert those 0s and 1s into a bit string and store it into a BIGINT UNSIGNED for slightly faster lookup and less disk space.


sql server full text search - fuzzy searching? Is there a way to configure fuzzy searches in sql server full text search. Meaning if I search for a term called POWDER, I must get matches (i.e. strings) which contain any variations of it within an allowable distance, like for e.g. the matches can be strings which can contain the following variations of the previously mentioned word:

PWDR (distance = 2)
PWDER (distance = 1)
WDER (distance = 2)

I remember reading somewhere you can do this kind of thing, however this specifically requires you to specify the allowable edit distance. How or where do you do this?

You could try an implementation of one of the string distance functions.  Here's a T-SQL implementation of the Levenshtein distance taken from here.  That post does suggest there's a performance issue with the function and scalar functions generally can be bad for performance when used in queries, but worth a look.  You could always try and convert it to a table function.


Database is slow after server transfer I have a big database, about 250GB, and recently our system had to be transfered to a new machine.

The old machine:

Has a Windows Server 2008 R2 Standard Edition
Runs SQL Server 2012
32GBs of RAM, with 20GBs defined for SQL MAX RAM.
22 VCPUs

The new machine:

Windows Server 2012 R2 Standard
Runs SQL Server 2012
40GBs of RAM, with 24GBS defined for SQL MAX RAM
22 VCPUs

As you can see, the new server should be enought to run the system with the same performance that it ran in the old one, but it is slower. The same Procs takes 2 to 5 times more to run sometimes (it seems to vary).

Things that I tried to solve the problem:

Enable flag 1118 (wasn't enabled in the old one)
Turn on Instant File Initialization (also wasn't active in the old one)
Set the MAX RAM to 20GBs, as in the old one
Reindexing and checking integrity with the Ola Hallegren's SQL Server Maintenance Solution
Changing SQL Process priority

The Database was transfered by detaching on the old server and attaching on the new server.

Any more information that should be needed do identify my problem I'll be glad to provide.

And, to finish, I am not a DBA, I'm just a programmer in charge of a task that I shouldn't be.

The same Procs takes 2 to 5 times more to run sometimes (it seems to vary).

You should recompile the stored proc using sp_recompile 'procedureName'. This should be one of the post migration step.

Another thing to be aware is if you are going to use more max memory (since you have more RAM on new server), you should look into testing and enabling TF2335

Also check the power option on the server. It should be set to high performance.

I agree with @Phil, check with your VM admin to see if the host machine is over committed in-terms of resources or not. Depending on if you are using VMWare  there are things like balloon driver (pdf whitepaper). For Hyper-V, read Running SQL Server with Hyper-V Dynamic Memory whitepaper.


Disallow new UDFs that contain a cursor Is there a way to prevent developers from creating new user-defined functions that use a cursor?  Would a database trigger that can read the code of a UDF do?

CREATE TRIGGER PreventCursorUDFs
    ON DATABASE
    FOR CREATE_FUNCTION
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventData XML = EVENTDATA();

    IF LOWER(@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]','NVARCHAR(MAX)'))
      LIKE N'%declare%cursor%fetch%'
    BEGIN
        RAISERROR('Yo, no cursors in functions!', 11, 1);
        ROLLBACK;
    END
END
GO

With a caveat, this will also disallow a trigger that contains a comment, like:

/* we used to do this a dumb way, using DECLARE CURSOR and FETCH */
/* now we're a little smarter and use this table-valued function */

...or if you have really inconvenient queries such as:

SELECT [declare] = [cursor] * 10 FROM dbo.[fetch];

...or even if you call your function:

CREATE FUNCTION dbo.ModeClarevoyantForCursoryFetching()
RETURNS TABLE
AS ...

EDIT

Addressing Nick's question here instead of as a comment, because it's going to get a little long-winded.

PBM is great for some stuff, but not so great at other stuff. This requirement falls under the "other stuff" category. The main problem is that the definition (e.g. OBJECT_DEFINITION()) is not exposed as a property to facets like user-defined functions. This means your condition cannot simply express something like:

@ObjectDefinition NOT LIKE '%DECLARE%CURSOR%FETCH%'

If this were the case, you could create this condition, wrap a policy around it, set it to "On prevent: change" and go to lunch. To implement this as a policy requires a little more work.

Since @ObjectDefinition is not a valid Field for this facet, we need to obtain it using ExecuteSql(). So your condition would have to be something like:

ExecuteSql('Numeric', 'SELECT x = PATINDEX(''%declare%cursor%fetch'',
  LOWER(OBJECT_DEFINITION(OBJECT_ID(@@SchemaName + ''.'' + @@ObjectName))))')

Ugly, right? When this evaluates to 0, the policy should succeed; when it is <> 0, the policy should fail. (Remember that a policy is supposed to be expressed in terms of the state you want the system to be in, not the state that you don't.)

So to start you would create a condition by opening Object Explorer, expanding Management > Policy Management, right-clicking Conditions, and selecting New Condition... Give it a name, choose the User Defined Function facet, and click on the Advanced Edit button. There you can enter the ExecuteSql() string above and click OK.

Change the Operator to =, enter 0 as the Value, and click OK. Now create a Policy. Right-click Policies, New Policy... Give it a name, choose the condition you just created, and then choose the Evaluation Mode:

Uh oh. Why aren't "On prevent" actions available? On prevent: change of course would allow you to prevent the function from being created. Because properties like ID and definition aren't available through the facet, requiring us to go through ExecuteSql(), we are subject to a restriction that prevents policies with conditions using ExecuteSql() from being automated. While this PBM blog post from 2008 suggests that this restriction has been lifted, I'm still finding it is enforced in SQL Server 2012 with Cumulative Update #1 applied (11.0.2316).

So for now, you can implement this using a policy, but you won't be able to prevent such a function from being created - you will only be able to investigate violations after the fact (by choosing either On Demand or On Schedule). Keep in mind that even if you run this policy on demand, it uses the exact same logic as the DDL trigger, so is subject to the same caveat: it may introduce false positives if you have comments or valid non-cursor queries that include the same sequence of words.

If you want the properties available to facets to be more flexible and more complete, which would allow for more control in using policies in favor of DDL triggers, please vote for and comment on these two Connect items:

http://connect.microsoft.com/SQLServer/feedback/details/552345/pbm-add-objectid-as-a-parameter-for-executesql

http://connect.microsoft.com/SQLServer/feedback/details/649944/pbm-enable-the-ability-to-pass-more-parameters-to-executesql

I don't know of an item that addresses relaxing the restriction on ExecuteSql() in general. I will look later and file one if I can't find one. I think the blog post reflects the fact that you can now run these on a schedule, but if you can run it on a schedule, why can't it work in "on prevent" mode?

Closing the loop: here is the new Connect item. Please vote and/or add comments describing your use case / business need (this is often much more valuable than just raw votes):

http://connect.microsoft.com/SQLServer/feedback/details/749317/allow-on-change-prevent-for-policies-with-executesql-conditions


How to determine the best index on a table with 3 nullable columns? I am examining a users table similar to this:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(81) DEFAULT NULL,
  `mobile_phone_number` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

I am not sure if I need a big index comprising the 3 primary searched and identifiers : username,email,mobile_phone_number .
I need all of these fields to be searched as quick as possible.
Main queries contain where conditions on either email and mobile_phone_number or username. As well as each on their own.

How can I determine the best index over these ?

My fear of using a index over all 3 is that it would not be efficient in some of my main queries as they are all nullable columns. Is the column beeing nullable even relevant to the index use ?

When looking up contact info, an index with username, email, mobile_phone_number would be great except for three small problems

uniqueness of username
uniqueness of email
uniqueness of mobile_phone_number

If username is supposed to be unique, then username, email, mobile_phone_number would just be overkill with index bloating.

Since email and mobile_phone_number are nullable, they need unique indexes as well.

If you create a unique index on username, email or mobile_phone_number, MySQL will disallow the insert for you on any individual uniqueness.

You could still create an index username, email, mobile_phone_number as a good covering index to retrieve all three columns when looking up by username with

SELECT username, email, mobile_phone_number
FROM users WHERE username = '...';

SUGGESTION

Create the indexes to have the following

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(81) DEFAULT NULL,
  `mobile_phone_number` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `mobile_phone_number` (`mobile_phone_number`),
  UNIQUE KEY `contact_info_ndx` (`username`,`email`,`mobile_phone_number`)
) ENGINE=InnoDB;

by running

ALTER TABLE users
   ADD UNIQUE KEY (`username`),
   ADD UNIQUE KEY `email` (`email`),
   ADD UNIQUE KEY `mobile_phone_number` (`mobile_phone_number`),
   ADD UNIQUE KEY `contact_info_ndx` (`username`,`email`,`mobile_phone_number`)
;


Adding a 'last time occurred' flag column I need to add a column into a table that contains 3 columns:

GroupKey - Shows the number of an item.
Status - the number of the status that the group was according the insert date. The status number can be changed back and forward several times.
Insert date - the time & date that the status changed.

This 3 columns never change. its like a "log" table , rows can be only added and not deleted or changend

The fourth column (calculated Column) that I need is a flag column that will show if the status was the last time the status has been changed based on the insert_date.

If it is the last status then show 1, else show 0. I hope I explained this well enough. Here is an example:

This cannot be done with a computed column. One option is to use a trigger:

Sample data

CREATE TABLE dbo.Example
(
    GroupKey integer NOT NULL,
    [Status] integer NOT NULL,
    inserted_date datetime NOT NULL DEFAULT GETUTCDATE(),
    IsLastTimeOccurred bit NOT NULL DEFAULT 'false',

    CONSTRAINT PK_dbo_Example_GroupKey_Status_inserted_date
    PRIMARY KEY CLUSTERED (GroupKey, [Status], inserted_date)
);

INSERT dbo.Example
    (GroupKey, [Status], inserted_date)
VALUES
    (11136, 10, '2015-03-30T13:01:50.153'),
    (11136, 11, '2015-03-30T13:02:03.590'),
    (11136, 12, '2015-03-30T13:02:08.980'),
    (11136, 10, '2015-04-30T09:37:40.477'),
    (11136, 11, '2015-04-30T09:37:46.400'),
    (11136, 12, '2015-04-30T09:38:04.177');

Initial setting

This assumes the rows all have IsLastTimeOccurred = false to begin with:

UPDATE E
SET IsLastTimeOccurred = 'true'
FROM dbo.Example AS E
WHERE NOT EXISTS
(
    SELECT *
    FROM dbo.Example AS E2
    WHERE E2.GroupKey = E.GroupKey
    AND E2.[Status] = E.[Status]
    AND E2.inserted_date > E.inserted_date
);

Maintenance

The following trigger ensures the column stays correct when the table changes:

CREATE TRIGGER trg_dbo_Example_AIUD
ON dbo.Example
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET ROWCOUNT 0;
    SET NOCOUNT ON;

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    -- Holds group keys and the latest insert date
    CREATE TABLE #Changes
    (
        GroupKey integer NOT NULL,
        [Status] integer NOT NULL,
        inserted_date datetime NOT NULL,

        PRIMARY KEY (GroupKey, [Status])
    );

    -- Populate the temporary table
    INSERT #Changes
    (
        GroupKey,
        [Status],
        inserted_date
    )
    SELECT
        E.GroupKey,
        E.[Status],
        MAX(E.inserted_date)
    FROM dbo.Example AS E
    WHERE EXISTS
    (
        SELECT *
        FROM Inserted AS I
        WHERE I.GroupKey = E.GroupKey
        AND I.[Status] = E.[Status]

        UNION ALL

        SELECT *
        FROM Deleted AS D
        WHERE D.GroupKey = E.GroupKey
        AND D.[Status] = E.[Status]
    )
    GROUP BY
        E.GroupKey,
        E.[Status];

    -- Set the flag
    UPDATE E
    SET E.IsLastTimeOccurred =
        CASE WHEN E.inserted_date = C.inserted_date THEN 1 ELSE 0 END
    FROM #Changes AS C
    JOIN dbo.Example AS E
        ON E.GroupKey = C.GroupKey
        AND E.[Status] = C.[Status]
    WHERE
        E.inserted_date = C.inserted_date
        OR E.IsLastTimeOccurred = 'true';
END;

This is a general solution that handles inserts, updates, and deletes. It was written before the question was clarified to state that the table was insert-only. Even so, I would probably leave the full general logic in; it doesn't cost much, and provides a safety net.


Keeping default max memory 2147483647 MB value in SQL Server, a bad practice? Based on that msdn page :  server memory settings it's a good practice to keep the default values for SQL-Servers min and max memory to keep it dynamic...

From what I learned through tutorials you should always define a max value, about min I haven't heard much. I am monitoring my pagefile using simple old perfmon and I realised that seldomly diskswapping is happenning and once it nearly caused a server crash.

Can that be related to have the default max memory setting in SQL Server?
The databases are around 150 GB and Memory is 48GB, there is no other application running on that machine.

Also, why should you have a min value? I know that a too low value can prevent the SQL-Server from starting and I know that SQL-Server keeps stuff in memory so doesn't try to release it's cache.

I am using SQL-Server 2012, thank you in advance!

Some details to clear things up

  Based on that msdn page : server memory settings it's a good practice to keep the default values for SQL-Servers min and max memory to keep it dynamic.

No, it's not written it is a good practice it says it's recommended and I am sure MS books online cannot write it as good, this is because of fact that environment varies and what is good for one environment might not suit other. Its mostly good to have restriction on something which is heavily consumed although managed efficiently. I consider a good practice to define max server memory on system because it will restrict buffer pool and will tell SQL Server how much max a buffer pool can grow although SQL Server can consume memory outside buffer pool/max server memory setting if it heavily uses Third party DLL's extended stored procs and Linked servers.

Page file is used by Windows to hold temporary data which is swapped in and out of physical memory in order to provide a larger virtual memory set. Page file largely depends on how much memory OS is committing and changes accordingly as per min and max value set. if you want to monitor page file you must rely on perfmon counters. Please read this MSDN Blog

Memory: Committed Bytes --Number of bytes of virtual memory that has been committed. This does not necessarily represent page file usage - it represents the amount of page file space that would be used if the process was completely made nonresident

Memory: Commit Limit-- Number of bytes of virtual memory that can be committed without having to extend the paging files.

Paging File: % Usage-- Percentage of the paging file committed

Paging File: % Usage Peak-- Highest percentage of the paging file committed

  Also, why should you have a min value? I know that a too low value can prevent the SQL-Server from starting and I know that SQL-Server keeps stuff in memory so doesn't try to release it's cache.

Min Server memory is minimum amount of memory available to the SQL Server Memory Manager for an instance of SQL Server. This comes into active picture when windows is facing memory pressure in such case it will raise Low Memry Notification Flag. SQLOS will respond to it, it would ask SQL Server to trim its memory consumption and free its cache then SQL Server will non preemptively trim down till its min server memory value. If pressure is too high SQL Server might not be able to react fast and then SQL Server process would be paged out. Remember min server memory does not say that when SQL Server starts it would minimum take this much memory if not required, SQL Server will start up consuming much less than min server memory.

Other use of Min server memory is if you do not want SQL Server to be paged out to disk because of some misbehaving OS/Third party driver. You set max server and min server memory to almost same value and give SQL Server service account Locked pages in memory privilege and in this case even if OS faces memory crunch SQL Server would max trim down to min server and if OS memory crunch is still there OS process would be paged out and would become extremely slow . But this is bad thing to do and I would not recommend


Measure SQL Azure Transaction Rate We are on SQL Azure v12 Standard/S2 Service Tier and are not satisfied with the performance so I am trying to measure where the bottleneck is. I realize that we could be hitting the limits of the S2 service tier. S2 has 50 DTU and 2,570 transactions per minute limitation.

Memory Pressure:
I looked at the sys.dm_exec_query_memory_grants and there are no pending memory grants and the requested memory is being granted.
Page Life expectancy is about 600.

DTU
The average DTU doesn't seem to be more than 50% unitized. avg_cpu_percent and avg_data_io_percent are rarely over 50%. avg_memory_usage_percent stays at 99%.
I got this information from the sys.dm_db_resource_stats

Transactions Per Sec
How do I measure Transactions/Sec in SQL Azure? The DMV sys.dm_os_performance_counters for Transactions/Sec doesn't have any values for the User databases. it has values for system databases.

You can use this script written by Dimitri Furman from Microsoft. It will  measure transactions/sec per database.  You need to run this against the database where you want to measure transactions/sec. I tested this against V12 version and it works.

https://blogs.msdn.microsoft.com/dfurman/2015/04/02/collecting-performance-counter-values-from-a-sql-azure-database/


How to get user-specific settings in PostgreSQL? I have to collect some information of PostgreSQL roles.  One piece of information is whether the role has different settings than 'usual', usual meaning here default values for only a few parameters (eg. log_statement).

So far I have only one idea: loop over all the users and issue the commands

\c postgres username

SHOW log_statement;

and then process the output.

I am wondering whether there is a similar method like querying the pg_settings view, showing per-user settings?  I don't need to know session-specific differences, only those that are persisted.

Unfortunately pg_shadow does not show roles with no LOGIN privilege.

I find it more useful to use @dezso hint view in the following query:

SELECT coalesce(role.rolname, 'database wide') as role,
       coalesce(db.datname, 'cluster wide') as database,
       setconfig as what_changed
FROM pg_db_role_setting role_setting
LEFT JOIN pg_roles role ON role.oid = role_setting.setrole
LEFT JOIN pg_database db ON db.oid = role_setting.setdatabase;

As always, just add it to psqlrc file (see doc)


How to make a table read/writes faster? I am using AWS RDS DB in my web application. The problem is a table which has millions of rows in it and the number of reads/writes per second on the table are huge. This happens even though I have DB replication, caching and queueing (to write data) mechanism in place.

Becuase of the indexes and the size of the table, the inserts are becoming slower. Sharding/partitioning would also not help as there is not a single column with which table can be partitioned. Is there any other way I can improve write frequency per second to the table?

There are hundreds of answers.

Get rid of redundant indexes.  Index cookbook
Batch the INSERTs
Build "summary tables" (very useful in DW type apps).  Summary tables
Get a bigger AWS instance
With very rare exceptions Partitioning does not speed up anything.  Partition blog
Sharding is useful only if the data can be split is certain ways; meanwhile it slows down other operations.
AWS does a decent job of setting the tunable VARIABLES, so there is not likely to be any benefit in further tuning them.  Further analysis
Turn on the slowlog to find out which queries are the 'worst'.  Slowlog
If possible, avoid using GUIDs/UUIDs.  UUIDs don't scale
Don't do pagination via OFFSET.  Pagination blog

Please provide some clues of what the application is about.  Include SHOW CREATE TABLE and sample INSERTs and SELECTs.


DOWS meaning in T-SQL I am reading a book which shows this example

SELECT wait_type ,
SUM(wait_time_ms / 1000) AS [wait_time_s]
FROM sys.dm_os_wait_stats DOWS
WHERE wait_type NOT IN ( 'SLEEP_TASK', 'BROKER_TASK_STOP',
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT',
'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP' )
GROUP BY wait_type
ORDER BY SUM(wait_time_ms) DESC

I want to know the exact role(meaning) of a keyword DOWS next to FROM statement. I tried to search but nothing helpful was found.
Thanks for your time!

It is the table_alias as shown in the documentation for the FROM clause.

[ FROM {  } [ ,...n ] ]
 ::=
{
    table_or_view_name [ [ AS ] table_alias ]
        [  ]
        [ WITH (  [ [ , ]...n ] ) ]
    | rowset_function [ [ AS ] table_alias ]
        [ ( bulk_column_alias [ ,...n ] ) ]
    | user_defined_function [ [ AS ] table_alias ]
    | OPENXML
    | derived_table [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ]
    |
    |
    |
    | @variable [ [ AS ] table_alias ]
    | @variable.function_call ( expression [ ,...n ] )
        [ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]
    | FOR SYSTEM_TIME
}

It would be clearer if the code author had chosen to use the optional AS keyword, and to use the alias elsewhere in the query:

SELECT
    DOWS.wait_type,
    SUM(DOWS.wait_time_ms / 1000) AS [wait_time_s]
FROM sys.dm_os_wait_stats AS DOWS
WHERE
    DOWS.wait_type NOT IN
    (
        'SLEEP_TASK', 'BROKER_TASK_STOP',
        'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT',
        'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP'
    )
GROUP BY
    DOWS.wait_type
ORDER BY
    SUM(DOWS.wait_time_ms) DESC;


Slow-running query returns incomplete resultset So I'm trying to get some data from an SQL Server 2008 instance, the query is quite simple in structure but it has an extremely poor performance, it takes just under a minute to retrieve a bit over 1,000,000 rows (from SSMS).

Here you can see the execution plan.

The data is intended for statistical purposes and the query is being used from R-Studio (through RODBC), when querying from this environment its 'breaks' somewhere near the 450,000 - 500,000 rows. I figure RODBC is timing out somewhere near the 30 Sec mark...

The query

SELECT
    cc.ReportID,
    cc.ReportDate,
    cc.UnitID,
    cc.Callsign,
    cc.SpeciesCode,
    cc.ComName,
    cc.CatchWeight,
    cc.DayGrid,
    cc.NightGrid,
    cc.UnitEffortType,
    cc.UnitEffort,
    cc.Depth,
    cc.ReportType,
    cc.EffortDesc,
    cc.LicenceUsed,
    vn.ValidFrom,
    vn.Nation,
    vn.NationCode,
    vg.GRT
FROM
    vwVesselGRT vg
    INNER JOIN vwVesselNationality vn ON vg.UnitID = vn.UnitID
    INNER JOIN vwClientCatch cc ON vn.UnitID = cc.UnitID AND vn.ValidFrom <= cc.ReportDate AND vn.ValidTo > cc.ReportDate
WHERE
    cc.ReportDate <= '2016-04-30'

I was wondering if anyone has experienced such problem. Should I attempt to change the query or tackle the R side of the problem? I might not be spotting the real issue here, I'm afraid.

In SQL what's how many CPU's do you have for that server? What's your MAXDOP and Cost threshold for parallelism'?

One thing you can do to check to see if your cost threshold is too low is run the same query with OPTION (MAXDOP 1)

SELECT cc.ReportID,
       cc.ReportDate,
       cc.UnitID,
       cc.Callsign,
       cc.SpeciesCode,
       cc.ComName,
       cc.CatchWeight,
       cc.DayGrid,
       cc.NightGrid,
       cc.UnitEffortType,
       cc.UnitEffort,
       cc.Depth,
       cc.ReportType,
       cc.EffortDesc,
       cc.LicenceUsed,
       vn.ValidFrom,
       vn.Nation,
       vn.NationCode,
       vg.GRT
FROM   vwVesselGRT vg
       INNER JOIN vwVesselNationality vn
         ON vg.UnitID = vn.UnitID
       INNER JOIN vwClientCatch cc
         ON vn.UnitID = cc.UnitID
            AND vn.ValidFrom <= cc.ReportDate
            AND vn.ValidTo > cc.ReportDate
WHERE  cc.ReportDate <= '2016-04-30'
OPTION (MAXDOP 1);

Without a total re-write you should be able to Schema Bind your Views you can add indexes on your views without indexing the underlying tables.

Best option if this is going to be something you use often though is to re-write your 3 views into a single query and then make your indexes based on that.

How bad is your query plan for just selecting from each of the views every single bit of that query Plan screams re-write to me though.

Views are like a saved select I wouldn't really use them for anything an application depends on.

as a temporary fix without any tuning you can just run this

SELECT cc.ReportID,
       cc.ReportDate,
       cc.UnitID,
       cc.Callsign,
       cc.SpeciesCode,
       cc.ComName,
       cc.CatchWeight,
       cc.DayGrid,
       cc.NightGrid,
       cc.UnitEffortType,
       cc.UnitEffort,
       cc.Depth,
       cc.ReportType,
       cc.EffortDesc,
       cc.LicenceUsed,
       vn.ValidFrom,
       vn.Nation,
       vn.NationCode,
       vg.GRT
INTO   BadQueryTempFixTable
FROM   vwVesselGRT vg
       INNER JOIN vwVesselNationality vn
         ON vg.UnitID = vn.UnitID
       INNER JOIN vwClientCatch cc
         ON vn.UnitID = cc.UnitID
            AND vn.ValidFrom <= cc.ReportDate
            AND vn.ValidTo > cc.ReportDate
WHERE  cc.ReportDate <= '2016-04-30'

Then From the other side just query

SELECT ReportID,
       ReportDate,
       UnitID,
       Callsign,
       SpeciesCode,
       ComName,
       CatchWeight,
       DayGrid,
       NightGrid,
       UnitEffortType,
       UnitEffort,
       Depth,
       ReportType,
       EffortDesc,
       LicenceUsed,
       ValidFrom,
       Nation,
       NationCode,
       GRT
FROM   BadQueryTempFixTable


Setting a variable from a statement that starts with EXEC I am trying to get the Server Name from a LinkedServer and assign it to a variable.

From this question (Get @@SERVERNAME from linked server), I found that either of these return the Server Name.

EXEC LinkedServer.[master].sys.sp_executesql N'SELECT @@VERSION;';

or

EXEC('SELECT @@VERSION;') AT LinkedServer;

Yet, when I try to assign that value to a parameter, SQL Server states it's incorrect:

SET @DbServerName = (EXEC TMR_DM_LS.[master].sys.sp_executesql N'SELECT @@SERVERNAME')

  Msg 156, Level 15, State 1, Line XXXX Incorrect syntax near the keyword 'EXEC'.
  Msg 102, Level 15, State 1, Line XXXX Incorrect syntax near ')'

What am I doing wrong?

Try something like the following:

DECLARE @DbServerName sysname;

EXEC [TMR_DM_LS].[master].sys.sp_executesql
   N'SELECT @DbServerName_tmp = @@SERVERNAME;',
   N'@DbServerName_tmp sysname OUTPUT',
   @DbServerName_tmp = @DbServerName OUTPUT;

SELECT @DbServerName AS [RemoteName];

HOWEVER, the above is how to return info in general. IF you are truly only wanting the server name, then I think you already have it on your local server. The Linked Server definition, depending on how it was configured, might very well have the name you are looking for:

DECLARE @DbServerName sysname;

SELECT @DbServerName = [data_source]
FROM   sys.servers
WHERE  [name] = N'TMR_DM_LS';

SELECT @DbServerName AS [RemoteName];

See if that works before doing the actual remote execution since it would be much more efficient to get the value locally.

UPDATE
O.P. replied that the desired value was indeed in the sys.servers system catalog view.


Peer-to-Peer Transactional Replication Load Balancing in MS SQL Server I'm looking at Peer-to-Peer Transactional Replication and it looks like a perfect fit for my requirements, but there's one unclear part - load balancing (see picture below).

How it is implemented in SQL Server? Is it programmable? Or it's something that I should implement myself? If so, what's the best way of doing that?

My business rules are:

Server A and Server B are in different geographical locations.
Data is replicated between both servers constantly.
If Server A becomes unavailable, its users requests are redirected to Server B. Some data loss is acceptable in this case.
If user performed some writes on Server A - he must keep using Server A (with only one exception, when Server A becomes unavailable). The user can't perform some writes on Server A and some writes on Server B. But he may perform reads from both servers.

Both servers uses MS SQL Server 2012 Enterprise.

AlwaysOn Availability Groups and SQL Server Cluster for some reasons is not an option for me.

I ended up using F5 BIG-IP LTM Virtual Edition, because I didn't find any other free solutions, that fits my business requirements.


MongoDB replication: going into maintenance mode with 10333 other maintenance mode tasks in progress I have a MongoDB instance where resync is required.

2016-11-07T11:59:23.330+0000 I REPL     [ReplicationExecutor] syncing from: x.x.x.x:27017
2016-11-07T11:59:23.354+0000 W REPL     [rsBackgroundSync] we are too stale to use x.x.x.x:27017 as a sync source
2016-11-07T11:59:23.354+0000 I REPL     [ReplicationExecutor] could not find member to sync from
2016-11-07T11:59:23.354+0000 E REPL     [rsBackgroundSync] too stale to catch up -- entering maintenance mode
2016-11-07T11:59:23.354+0000 I REPL     [rsBackgroundSync] our last optime : (term: 20, timestamp: Oct  4 07:41:29:1)
2016-11-07T11:59:23.354+0000 I REPL     [rsBackgroundSync] oldest available is (term: 20, timestamp: Oct 17 02:13:33:5)
2016-11-07T11:59:23.354+0000 I REPL     [rsBackgroundSync] See http://dochub.mongodb.org/core/resyncingaverystalereplicasetmember
2016-11-07T11:59:23.355+0000 I REPL     [ReplicationExecutor] going into maintenance mode with 10333 other maintenance mode tasks in progress

what does this line mean?

[ReplicationExecutor] going into maintenance mode with 10333 other maintenance mode tasks in progress

what are maintenance mode tasks? No docs from MongoDB. Why there are 10333 queued? Howto see them (list)? With a search engine I found also log entries with with 0 other maintenance mode tasks in progress

What are maintenance mode tasks?

The "maintenance mode tasks" message is referring to a counter of successive calls to the replSetMaintenance command and (as at MongoDB 3.4) isn't associated with specific queued tasks. The replSetMaintenance command is used to keep a secondary in RECOVERING state while some maintenance work is done. A RECOVERING member remains online and potentially syncing, but is excluded from normal read operations (eg. using secondary read preferences with a driver). Each invocation of replSetMaintenance either increases the task counter  (if true) or decreases it (if false). When the counter reaches 0 the member will transition from RECOVERING back into SECONDARY state assuming it is healthy.

As at MongoDB 3.4, changes in maintenance mode are currently only noted in the MongoDB log. This command is generally only used internally by mongod, but you can invoke it manually as well.

Here's an annotated set of log lines and the associated mongo shell commands showing the task counter changing:

// db.adminCommand({replSetMaintenance: 1})
[ReplicationExecutor] going into maintenance mode with 0 other maintenance mode tasks in progress
[ReplicationExecutor] transition to RECOVERING

// db.adminCommand({replSetMaintenance: 1})
[ReplicationExecutor] going into maintenance mode with 1 other maintenance mode tasks in progress

// db.adminCommand({replSetMaintenance: 0})
[ReplicationExecutor] leaving maintenance mode (1 other maintenance mode tasks ongoing)

// db.adminCommand({replSetMaintenance: 0})
[ReplicationExecutor] leaving maintenance mode (0 other maintenance mode tasks ongoing)
[ReplicationExecutor] transition to SECONDARY

// db.adminCommand({replSetMaintenance: 0})
[ReplicationExecutor] Attempted to leave maintenance mode but it is not currently active

  Why there are 10333 queued?

In MongoDB 3.2 a replica set member that becomes "too stale" (i.e. doesn't have any oplog entries in common with another healthy member of the replica set) will remain in RECOVERING mode and periodically check if a new valid sync source is available. Each check currently increments the "maintenance task" counter, so this doesn't actually indicate a meaningful number of tasks if the member has become stale.

In theory "too stale" is not a terminal state as conceivably a member with a larger oplog may temporarily be offline; in practice a "too stale to catch up error" generally means a manual resync is required.

2016-11-07T11:59:23.354+0000 I REPL     [rsBackgroundSync] our last optime : (term: 20, timestamp: Oct  4 07:41:29:1)
2016-11-07T11:59:23.354+0000 I REPL     [rsBackgroundSync] oldest available is (term: 20, timestamp: Oct 17 02:13:33:5)

In this case the replica set member in question went stale almost two weeks earlier, so the maintenance mode counter has continued to creep up over time. There's a related issue in the MongoDB Jira you can watch/upvote: SERVER 23899: Reset maintenance mode when transitioning from too-stale to valid sync source.


MySQL server with root user that can be accessed remotely with just a password. How hard is it to break in? I have a MySQL server that has a root user enabled.
This root user is protected by a password and can be accessed remotely: %.

The reason for this is from time to time I use MySQL Workbench to access the server and run queries directly to see some stats.

I know that this is bad.

My question is how hard is it to break in.

The server has fail2ban installed so I think multiple failed attempts will block the intruder via IP. The password is somewhat strong.

As a follow up, what can I do to still be able to access the server with MySQL Workbench but improve security of the box.

create a user with certain privileges (depends on what queries you run). I also advice to disallow root remotely login.