SQL Server Disk I/O Latency During Specific Timeframe We are experiencing high disk I/O latency in a specific timeframe in SQL Server. Every day at exactly 4:30 AM we see a significant difference in disk I/O and it lasts up to 6:30 or 9:30 AM. I already checked with the development and storage teams. They confirmed that everything is running normally.  Storage team declared that there is no issue in LUN and they are blaming SQL Server.

One thing is sure--some job is triggering exactly at 4:30 AM every day, which causes the high disk I/O. But I am not sure how to find that out.  I attached the screen shot of the SQL error log at 4:30 AM. All the monitoring tools and the logs are stating that the transactions are taking longer that usual due to high disk I/O. But nowhere is it mentioned what is causing it.

Ours is 3 node Always On environment. P1 - primary, P2 - Stand only, P3 - Read only.

The issue we are facing now is from P3 (Read only node).

This is too long for a comment, but there won't be enough in here to provide the answer as to WHAT is causing this, just HOW to work out what IT is.

Firstly: What applications are connecting to databases on that server, and are they running batch processes against databases that have grown faster than you've kept track of, thus extending the time required to complete?

Are any backups being taken at this time? Context switching is really high, so there could be backups conflicting with batch processes running at the same time.

Here's an example of how you could capture the job/query causing your issues:

Download the WhoIsActive stored proc by Adam Machanic from http://whoisactive.com/. Run it against an Administration database you might have on the server, or in the master database.
Create a table that can capture the output of WhoIsActive to a table (http://whoisactive.com/docs/06_options/)
Create a SQL Agent job that runs executes sp_WhoIsActive every 15 seconds (this is my preference, change to suit yours) and log the data to the table you created.
Create a set of PerfMon counters (using https://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/) on the SQL Server and log the data to CSV.
If the server is a VM, ask your VM Admin for server stats for the given period. vRealize Operations contains a lot of useful reports for proving a point.
Get some reports from your SAN Admin showing SAN activity during the problem period.


how to get child categorys based on categoryid this is my table structure
i need to find child categoryids. If CategoryID 1 then 1,2,3,4,10,12,13,14,15,16 when CategoryID 2 then 2,10,12,13,14,15,16

Using recursive CTE. Is this what you wanted?

sample data:

/*
CREATE TABLE #Table
(
    CategoryID INT
    , Name VARCHAR(MAX)
    , IsParent BIT
    , PCategoryID INT
);

INSERT INTO #Table
  (CategoryID, Name, IsParent, PCategoryID)
VALUES
  (1, 'IIT', 1, 0),
  (2, 'IIT Maths', 0, 1),
  (3, 'IIT Chemistry', 0, 1),
  (4, 'IIT Physics', 0, 1),
  (5, 'Education', 1, 0),
  (6, 'Learnig', 0, 5),
  (7, 'Writing', 0, 5),
  (8, 'Sports', 1, 0),
  (9, 'Cricket', 0, 8),
  (10, 'Algebra', 0, 2),
  (11, 'Basic Algebra', 0, 10),
  (12, 'Trigonometry', 0, 2),
  (13, 'Geometry', 0, 2),
  (14, 'Al Cat-1', 0, 10),
  (15, 'Al Cat-2', 0, 10),
  (16, 'Tri Cat-1', 0, 12);
*/

the actual query:

;WITH CTE (CategoryID, Name, PCategoryID, Level, SortOrder)
AS (
    SELECT T.CategoryID, T.Name, T.PCategoryID, 0 AS Level, T.Name AS SortOrder
    FROM #Table AS T
    WHERE T.CategoryID = 1    --Your CategoryID here! :)

    UNION ALL

    SELECT T.CategoryID, T.Name, T.PCategoryID, C.Level + 1,  C.SortOrder + T.Name
    FROM #Table AS T
    INNER JOIN CTE AS C
        ON C.CategoryID = T.PCategoryID
)
SELECT CategoryID, PCategoryID, Level, SPACE(Level*3) + Name AS NameIndented
FROM CTE
ORDER BY SortOrder;


Seperate value of a specific column with "," delimiter in Oracle using "Regular Expression" I have a table with below structure:
create table CUSTOMER_INFO
(
  cust_id      NUMBER,
  cust_name    VARCHAR2(50),
  cust_address VARCHAR2(50)
)

Example Data :
cust_id   |  cust_name |  cust_address
------------------------------------------
  1       |     A      |  Tehran,Tehran
  2       |     B      |  Rasht,Tehran
  3       |     C      |  Minsk,Tehran

I want to have this output:
cust_id   |  cust_name |  cust_address
------------------------------------------
  1       |     A      |  Tehran
  2       |     B      |  Tehran
  2       |     B      |  Rasht
  3       |     C      |  Minsk
  3       |     C      |  Tehran

what I've written so far is this :
select distinct cust_id,
                cust_name,
                address
  from (select distinct cust_id,
                        cust_name,
                        regexp_substr(cust_address, '[^,]+', 1, level) address
          from customer_info
        connect by regexp_substr(cust_address, '[^,]+', 1, level) is not null)
order by cust_id

I want to know if there is better way of writing this query and Will this solution produce false result ?
Thanks in advance

Such an approach makes sure you don't get too many duplicates as your query does (so you removed them with a distinct in line #4; without it, you'd get 12 rows as a result):
SQL> with customer_info (cust_id, cust_name, cust_address) as
  2    (select 1, 'A', 'Tehran,Tehran' from dual union all
  3     select 2, 'B', 'Rasht,Tehran'  from dual union all
  4     select 3, 'C', 'Minsk,Tehran'  from dual
  5    )
  6  select --distinct      --> use DISTINCT to get result you want
  7         cust_id,
  8         cust_name,
  9         regexp_substr(cust_address, '[^,]+', 1, column_value) cust_address
 10  from customer_info cross join
 11    table(cast(multiset(select level from dual
 12                        connect by level <= regexp_count(cust_address, ',') + 1
 13                       ) as sys.odcinumberlist))
 14  order by cust_id;

   CUST_ID C CUST_ADDRESS
---------- - ----------------------------------------------------
         1 A Tehran
         1 A Tehran
         2 B Rasht
         2 B Tehran
         3 C Minsk
         3 C Tehran

6 rows selected.

SQL>

However, as you want to have only one 1-A-Tehran combination, distinct in line #6 has to be uncommented.


What does "Rows Removed by Index Recheck" without "lossy" mean? Here's part of an EXPLAIN ANALYZE from Postgres 9.6:

->  Bitmap Heap Scan on cities  (cost=90.05..806.49 rows=265 width=4) (actual time=4.733..45.772 rows=17 loops=1)
       Recheck Cond: (regexp_replace(regexp_replace(replace(replace(replace(replace(lower(name), 'Ã¤'::text, 'ae'::text), 'Ã¶'::text, 'oe'::text), 'Ã¼'::text, 'ue'::text), 'ÃŸ'::text, 'ss'::text), 'strasse\M'::text, 'strasse'::text, 'g'::text), '\W'::text, ''::text, 'g'::text) % 'coerde'::text)
      Rows Removed by Index Recheck: 567
      Heap Blocks: exact=497
      ->  Bitmap Index Scan on city_lookup_index  (cost=0.00..89.98 rows=265 width=0) (actual time=4.229..4.229 rows=584 loops=1)
             Index Cond: (regexp_replace(regexp_replace(replace(replace(replace(replace(lower(name), 'Ã¤'::text, 'ae'::text), 'Ã¶'::text, 'oe'::text), 'Ã¼'::text, 'ue'::text), 'ÃŸ'::text, 'ss'::text), 'strasse\M'::text, 'strasse'::text, 'g'::text), '\W'::text, ''::text, 'g'::text) % 'coerde'::text)

What does Rows Removed by Index Recheck mean?

Or in other words: The bitmap is non-lossy (only exact pages), so why did it contain tuple pointers that were (presumably) removed by the recheck?

Some index strategies don't definitely conclude that a tuple meets the criteria.  They can definitely and rapidly eliminate most tuples which provably can't meet the criteria (which is where the performance gain comes from) but some of the ones that pass might be false positives, so need to be rechecked.

I guess you are using gin_trgm_ops, which is an example of such an indexing strategy.


How to translate SQL Server stored procedure into Oracle stored procedure? This is my SQL Server stored procedure which is below:

CREATE PROCEDURE flight_details
AS
BEGIN
SELECT Airline.Airline_No, Airline_Name, Airplane.Airplane_No, Airplane_Name, Flight.Flight_No, Flight_Type, City_Departure, City_Transit, City_Arrival, Departure_Date_Time, Transit_Date_Time, Arrival_Date_Time, Flight_Duration_in_hours, Transit_Duration_in_hours, Flight_Status
FROM Airline,Airplane, Flight
WHERE Airline.Airline_No = Airplane.Airline_No AND Airline.Airline_No=Flight.Airline_No AND Airplane.Airplane_No=Flight.Airplane_No
END

EXECUTE flight_details

The above SQL Server stored procedure worked successfully.

Next, I tried to translate it into Oracle stored procedure code which is shown below:

CREATE OR REPLACE PROCEDURE flight_details
(p_flight_details OUT SYS_REFCURSOR)
AS
BEGIN
OPEN p_flight_details FOR
SELECT Airline.Airline_No, Airline_Name, Airplane.Airplane_No, Airplane_Name, Flight.Flight_No, Flight_Type, City_Departure, City_Transit, City_Arrival, Departure_Date_Time, Transit_Date_Time, Arrival_Date_Time, Flight_Duration_in_hours, Transit_Duration_in_hours, Flight_Status
FROM Airline,Airplane, Flight
WHERE Airline.Airline_No = Airplane.Airline_No AND Airline.Airline_No=Flight.Airline_No AND Airplane.Airplane_No=Flight.Airplane_No
END flight_details;

SET SERVEROUTPUT ON;

EXECUTE flight_details;

I tried creating this procedure in Oracle SQL Developer but it was showing the following error:

Error(5,1): PL/SQL: SQL Statement ignored

Error(7,133): PL/SQL: ORA-00933: SQL command not properly ended

Error(8,19): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     ( begin case declare end exception exit for goto if loop mod    null pragma raise return select update while with    <an identifier> <a double-quoted delimited-identifier>    <a bind variable> << continue close current delete fetch lock    insert open rollback savepoint set sql execute commit forall    merge pipe purge json_exists json_value json_query    json_object json_array

FYI, you can access the following link and convert your block code from SQL to Oracle.

http://sqlines.com/online


how to retrieve MAX ID from inner join SQL Query? I am working on a search stored procedure which, the outcome of the procedure retrieving search rows in increments of 60. The values that this stored procedure is receiving is the search value, optional starting ID, optional ending ID.
The problem I am facing is retrieving the max ID within the search parameters along with retrieving records between the start and end.

My aim is to capture records between start-end (using max ID as marker to determine where to start getting records) for specific search value.

ALTER Procedure [dbo].[searchLog]
@searchValue varchar(150),
@StartLoc INT = NULL,
@EndLoc INT = NULL
AS
Declare @myquote varchar(10)
Declare @wildcard varchar(5)
set @myquote ='"'
set @wildcard ='*'
set @searchValue = @myquote + @searchValue + @wildcard +@myquote

SET NOCOUNT ON;

     IF @StartLoc IS NULL BEGIN
          SET @StartLoc = (SELECT MAX(o.ID), o.ID, o.Name, o.DateRan, s.Name AS Job,
                                  o.Start, o.[End], o.Status, o.Logs
                           FROM dbo.output AS o
                           LEFT OUTER JOIN dbo.jobs AS j
                                ON o.Job = j.ID
                           LEFT OUTER JOIN dbo.scripts AS s
                                ON j.Script = s.ID
                           WHERE CONTAINS ((o.Name, o.Logs),  @searchValue)
                                 AND (o.DateRan < GETDATE())) - 60;
     END;

     IF @Endloc IS NULL BEGIN
           SELECT @EndLoc = (SELECT MAX(o.ID), o.ID, o.Name, o.DateRan, s.Name AS Job,
                                  o.Start, o.[End], o.Status, o.Logs
                           FROM dbo.output AS o
                           LEFT OUTER JOIN dbo.jobs AS j
                                ON o.Job = j.ID
                           LEFT OUTER JOIN dbo.scripts AS s
                                ON j.Script = s.ID
                           WHERE CONTAINS ((o.Name, o.Logs),  @searchValue)
                                 AND (o.DateRan < GETDATE()));
     END;
BEGIN

SET NOCOUNT ON
SELECT   o.ID, o.Name, o.DateRan, s.Name AS Job, o.Start, o.[End], o.Status, o.Logs
FROM            dbo.output AS o LEFT OUTER JOIN
                         dbo.jobs AS j ON o.Job = j.ID LEFT OUTER JOIN
                         dbo.scripts AS s ON j.Script = s.ID
WHERE CONTAINS ((o.Name, o.Logs),  @searchValue)
AND
(o.DateRan < GETDATE())
AND
(o.ID Between @StartLoc and @EndLoc)
     ORDER BY o.ID DESC
END
RETURN

I adjusted your current logic to the following, since it was broken in multiple ways (-60 meant 61 rows, not 60, and your subqueries with MAX in them don't work):

ALTER Procedure [dbo].[searchLog]
@searchValue varchar(150),
@StartLoc INT = NULL,
@EndLoc INT = NULL
AS
Declare @myquote varchar(10)
Declare @wildcard varchar(5)
set @myquote ='"'
set @wildcard ='*'
set @searchValue = @myquote + @searchValue + @wildcard +@myquote

SET NOCOUNT ON;

     IF @StartLoc IS NULL BEGIN
          SET @StartLoc = (SELECT MAX(o.ID)
                           FROM dbo.output AS o
                           LEFT OUTER JOIN dbo.jobs AS j
                                ON o.Job = j.ID
                           LEFT OUTER JOIN dbo.scripts AS s
                                ON j.Script = s.ID
                           WHERE CONTAINS ((o.Name, o.Logs),  @searchValue)
                                 AND (o.DateRan < GETDATE())) - 59;
     END;

     IF @Endloc IS NULL BEGIN
           SELECT @EndLoc = (SELECT MAX(o.ID)
                           FROM dbo.output AS o
                           LEFT OUTER JOIN dbo.jobs AS j
                                ON o.Job = j.ID
                           LEFT OUTER JOIN dbo.scripts AS s
                                ON j.Script = s.ID
                           WHERE CONTAINS ((o.Name, o.Logs),  @searchValue)
                                 AND (o.DateRan < GETDATE()));
     END;
BEGIN

SET NOCOUNT ON
SELECT   o.ID, o.Name, o.DateRan, s.Name AS Job, o.Start, o.[End], o.Status, o.Logs
FROM            dbo.output AS o LEFT OUTER JOIN
                         dbo.jobs AS j ON o.Job = j.ID LEFT OUTER JOIN
                         dbo.scripts AS s ON j.Script = s.ID
WHERE CONTAINS ((o.Name, o.Logs),  @searchValue)
AND
(o.DateRan < GETDATE())
AND
(o.ID Between @StartLoc and @EndLoc)
     ORDER BY o.ID DESC
END
RETURN

I did not adjust your logic that assumes all matching IDs will be contiguous.

Then I rewrote this logic using OFFSET/FETCH, as I suggested in a comment:

CREATE PROCEDURE dbo.searchLog2
  @searchValue varchar(150),
  @startLoc    int = NULL,
  @endLoc      int = NULL
AS
BEGIN
  SET NOCOUNT ON;

  SET @searchValue = '"' + @searchValue + '*"';

  ;WITH results AS
  (
    SELECT o.ID, o.Name, o.DateRan, s.Name AS Job,
           o.Start, o.[End], o.Status, o.Logs
    FROM dbo.output AS o
    LEFT OUTER JOIN dbo.jobs AS j
      ON o.Job = j.ID
    LEFT OUTER JOIN dbo.scripts AS s
      ON j.Script = s.ID
    WHERE CONTAINS ((o.Name, o.Logs), @searchValue)
    AND o.DateRan < GETDATE()
    AND o.ID >= COALESCE(@startLoc, 0)          -- assuming unsigned
    AND o.ID <= COALESCE(@endLoc,   2147483647) -- assuming int
 )
  SELECT * FROM results
  ORDER BY ID DESC
  OFFSET 0 ROWS FETCH NEXT 60 ROWS ONLY;
END
GO

If you compare the plans when all three parameters are supplied, they're pretty close:

EXEC dbo.searchLog  @searchValue = 'name', @startLoc = 100, @endLoc = 1000;
EXEC dbo.searchLog2 @searchValue = 'name', @startLoc = 100, @endLoc = 1000;

Yours:

Mine:

Comparison (don't take too much stock in estimated cost %, but it's a hint):

So, pretty similar, how acceptable the tiny hit of the TOP operator reflects in the estimated cost depends on how often you call it with both parameters and how much value you place on readability.

But once you remove either or both Loc parameters, your extra IF conditional queries add up by also having to perform this work, either once or twice:

EXEC dbo.searchLog  @searchValue = 'name', @startLoc = 100;
EXEC dbo.searchLog2 @searchValue = 'name', @startLoc = 100;

EXEC dbo.searchLog  @searchValue = 'name';
EXEC dbo.searchLog2 @searchValue = 'name';

You can see the additional work reflected in statistics I/O and number of rows read/returned by the entire batch (even when you return the wrong number of rows if your matching IDs are too far apart because subtracting 60 from an ID only works if there are no gaps in your IDs and there are no gaps between matching rows).


Can't connect with UDL file to SQL Server 2016 but with SSMS it works without problems I have a strange issue using a UDL file.
I can't connect with the UDL file to SQL Server 2016 with a specific user (login) but when I'm trying to connect with the same user via SSMS,  it works without problems.

A Diffrent user can connect both ways without issues.

What's wrong with my user/UDL file?

I found what caused it - UDL connections doesn't allow using a different Windows NT authentication other than the current user.
The "Custom user" is only for SQL authentication.


Randomizing the Auto Increment in Mysql We have a database, where we insert data into table whenever we receive an order from our services.

We use auto_increment to generate the ID and assign the order with the same.
This is usually same everywhere.

But the problem is, since this information is passed to third party vendors, they are able to trace, how many orders we are receiving per day. By sending a request in the early morning and sending another request in midnight and count the difference in the ID.

This is giving insight information of our services. Since it's bit complex system, we can't do complex operation that can randomise this.

My initial idea is to generate auto_increment numbers in bit random fashion way.

1,2,3,4,5,67,68,69,70,71,133,134,135,136,137,189,190,191,192,193,194

I guess it should be simple, as Mysql runs out of cache, generate next sequence by appending some random number.

How to achieve this without impacting the performance?

Here is an overview of methods to generate random unique ids in mysql, by Rick James: http://forums.mysql.com/read.php?24,425424,425491

The overview is quite comprehensive, gives 5 different strategies, lists pros and cons for every one.


How to transform this result set? I have the result set above that I stuffing into a table variable @ApptTemp, and trying to transform it into something similar to the one below.  Note that of the 4 rows that have a description of Staff Meeting, that they are 2 different events.  One starting at 8:00 AM, and one starting at 10:00 AM.  I have tried a few things, PIVOT , and a WHILE loop with no success.  I was thinking of using a CURSOR, but I have not written one that advanced before.  Any suggestions would be greatly appreciated.

The example below uses a CTE to find the gaps in your TimeScheduleId sequence.  The field  HasPreviousItem identifies if a row is the first in a sequence and RowNum is used to number all of the records.
To find the start dates/times select for records where  HasPreviousItem = 0.

The Duration assumes that each record is a 15 minute block of time.  The cross apply subquery finds all records for the same ApptCode in the same block.  That is records in sequence not interuppted by a HasPreviousItem value change.
If the "duration" of a record is not always 15 minutes an alternate method to find the duration of an appointment would be needed.

CREATE TABLE ApptTemp (
     ProviderId INT NOT NULL DEFAULT 196
    , OfficeId INT NOT NULL DEFAULT 7
    , ApptCode CHAR(1) NOT NULL
    , Description VARCHAR(20) NOT NULL
    , ScheduleTimeId INT NOT NULL
    , ScheduleTime DATETIME NOT NULL
    , ApptDate DATETIME NOT NULL
);

GO

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES ( 'L', 'Lunch', 49, '1900-01-01 12:00', '2014-03-20 12:00'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'L', 'Lunch', 50, '1900-01-01 12:15', '2014-03-20 12:15'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'L', 'Lunch', 51, '1900-01-01 12:30', '2014-03-20 12:30'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'L', 'Lunch', 52, '1900-01-01 12:45', '2014-03-20 12:45'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'S', 'Staff Meeting', 33, '1900-01-01 08:00', '2014-03-20 08:00'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'S', 'Staff Meeting', 34, '1900-01-01 08:15', '2014-03-20 08:15'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'S', 'Staff Meeting', 41, '1900-01-01 10:00', '2014-03-20 10:00'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'S', 'Staff Meeting', 42, '1900-01-01 10:15', '2014-03-20 15:00'  );

WITH ApptCte
AS (
SELECT ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate, ProviderId, OfficeId

        , CASE
            WHEN  EXISTS
                (
                SELECT  *
                FROM     ApptTemp AS A2
                WHERE  A2.ScheduleTimeId = A1.ScheduleTimeId - 1
                    AND A2.ApptCode = A1.ApptCode
                )
                    THEN 1
            ELSE 0
        END AS HasPreviousItem

        , RowNum = Row_Number() OVER(ORDER BY ScheduleTimeId)

FROM ApptTemp AS A1

)

SELECT
     ApptDate
    , Description AS ApptDetail
    , OfficeId
    , ProviderId
    , Duration.Total AS Duration

FROM ApptCte AS MainCte
    CROSS APPLY
        (
            SELECT (COUNT(*) + 1) * 15 AS Total
            FROM ApptCte AS CountCte
            WHERE CountCte.ApptCode = MainCte.ApptCode
                AND CountCte.HasPreviousItem = 1
                AND CountCte.RowNum > MainCte.RowNum
                AND NOT EXISTS ( SELECT *
                                    FROM  ApptCte AS InnerCte
                                    WHERE InnerCte.ApptCode = CountCte.ApptCode
                                        AND InnerCte.HasPreviousItem = 0
                                        AND InnerCte.RowNum != MainCte.RowNum )
        ) AS Duration

WHERE HasPreviousItem = 0

ORDER BY ApptDate;


SQL Server - High buffer time I'm noticing a high number of waitingRequestsCount on SQL server. The latch_class is buffer. See attached screenshots. Going through SQL server dashboard, I see high number (32221334) waits for Buffer Pool. Can someone please explain what could contribute to high number of waits?

Reference:

Diagnosing and Resolving Latch Contention on SQL Server by Microsoft Corporation
Buffer Latch Class by SQLSkills.com

Some latch contention is to be expected as a normal part of the operation of the SQL Server engine. It is inevitable that multiple concurrent latch requests of varying compatibility will occur on a high concurrency system. SQL Server enforces latch compatibility by requiring the incompatible latch requests to wait in a queue until outstanding latch requests are completed.

Latches are lightweight synchronization primitives that are used by the SQL Server engine to guarantee consistency of in-memory structures including; index, data pages and internal structures such as non-leaf pages in a B-Tree. SQL Server uses buffer latches to protect pages in the buffer pool and I/O latches to protect pages not yet loaded into the buffer pool.

Whenever data is written to or read from a page in the SQL Server buffer pool a worker thread must first acquire a buffer latch for the page. There are various buffer latch types available for accessing pages in the buffer pool including exclusive latch (PAGELATCH_EX) and shared latch (PAGELATCH_SH). When SQL Server attempts to access a page which is not already present in the buffer pool, an asynchronous I/O is posted to load the page into the buffer pool. If SQL Server
needs to wait for the I/O subsystem to respond it will wait on an exclusive (PAGEIOLATCH_EX) or shared (PAGEIOLATCH_SH) I/O latch depending on the type of request; this is done to prevent another worker thread from loading the same page into the buffer pool with an incompatible latch.

Latches are also used to protect access to internal memory structures other
than buffer pool pages; these are known as Non-Buffer latches.

My recommendation to you will be instead of focusing on the number of buffer class latch, start from wait types.  Especially Pagelatch_XX and Pageiolatch_xx type in this case if those are showing up as the top waits in your case. You will find details about these wait types here by sqlskills.com

Quote from Paul Randal:

  I have not seen this latch be a noticeable contention point.

  You should filter this wait type out of results from
  sys.dm_os_latch_stats otherwise it will overwhelm the other latch
  information.


MySQL and MariaDB: COLLATE on table vs COLLATE on all columns? Will the following table behave or perform any differently

CREATE TABLE t1 (
  foo char(60) COLLATE utf8_bin,
  bar char(64) COLLATE utf8_bin,
  baz char(80) COLLATE utf8_bin
) DEFAULT CHARSET=utf8;

than this table

CREATE TABLE t2 (
  foo char(60),
  bar char(64),
  baz char(80)
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

I ask this because when I run SHOW CREATE TABLE mysql.db it shows up with the marker on every column (as in t1), and on the table (as in t2). I don't ever add DEFAULT COLLATE when I create tables. I'm wondering why the system tables have this option set.

In their current form: the behavior and performance of t1 and t2 should be identical,

and:
IF a string column is added that does not specify either CHARSET or COLLATE clauses: the behavior and performance will be different. This is due to:

new columns in t2 will be CHARSET=utf8 and COLLATE=utf8_bin (because they were specified as defaults for the table), but
new columns in t1 will be CHARSET=utf8 (because that was specified as default for the table), but will also be COLLATE=utf8_general_ci (because this should be the default for the utf8 character set)

Now, I created the two tables as you have them in the question, and that resulted in the following:

SHOW CREATE TABLE t1;

CREATE TABLE `t1` (
  `foo` char(60) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `bar` char(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `baz` char(80) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

and:

SHOW CREATE TABLE t2;

CREATE TABLE `t2` (
  `foo` char(60) COLLATE utf8_bin DEFAULT NULL,
  `bar` char(64) COLLATE utf8_bin DEFAULT NULL,
  `baz` char(80) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

I'm not sure why CHARACTER SET became explicitly stated in the stored version of the CREATE TABLE t1 statement, but I think I know why COLLATE did for CREATE TABLE t2: once you submit the statement, that's what it gets translated into as it's being processed. Why just the COLLATE statement and not also CHARACTER SET? That is most likely due to character sets working with 1 or more collations, so specifying the character set does not imply the collation. On the other hand, a collation works with only 1 character set, so specifying the collation does imply the character set. Yes, character sets have default collations, but defaults can change over time or between contexts. Yes, there are defaults explicitly declared for the table, but what if a) the table defaults aren't respected, and/or b) whatever is reading the DDL wants to know everything about a column as it parses the column, as opposed to needing to read the full table DDL first and potentially go back to apply defaults. Adding the COLLATE clauses makes the operation very clear and unambiguous.

Also, the documentation for CREATE TABLE Statement Retention states:

  The original CREATE TABLE statement, including all specifications and table options are stored by MySQL when the table is created. The information is retained so that if you change storage engines, collations or other settings using an ALTER TABLE statement, the original table options specified are retained. This enables you to change between InnoDB and MyISAM table types even though the row formats supported by the two engines are different.

I tested this on db<>fiddle against MariaDB 10.2 and 10.3. and MySQL 5.6, 5.7, and 8.0, and the behavior was the same across all of them.


Rely on .pgpass in CREATE USER MAPPING I am trying to create a script which creates a postgres-fdw connection between two postgres 9.4 databases. The script (which is checked in under version control), has been relying on pgpass to do other things. Is there any option I can use to request that the password be looked up in pgpass? ... in general, where is the documentation on what options are available for CREATE USER MAPPING? the reference just says that options depend on the server.

It may not fully answer your question, but:

If you specify a user mapping for a superuser for postgres_fdw you can specify it without a password. It will then rely on the .pgpass from the server.

By using a mapping for a superuser you will however need to think about what security concerns this raises for your usage.

Data source:

dbname=sales
host=sales.db
user=sales_reader

Own DB:

dbname=dwh
host=dwh.db
user=dwh_writer
~/.pgpass of the postgres OS user contains:        sales.db:*:sales:sales_reader:verysecret

Example session showing the difference between NOSUPERUSER and SUPERUSER:

CREATE SERVER sales FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'sales.db', dbname 'sales');
GRANT USAGE ON FOREIGN SERVER sales TO dwh_writer;
CREATE USER MAPPING FOR dwh_writer SERVER sales OPTIONS (user 'sales_reader');
CREATE USER MAPPING FOR postgres   SERVER sales OPTIONS (user 'sales_reader');

CREATE FOREIGN TABLE sales_summary (
    item_id text,
    amount bigint,
    date_sold date
)
SERVER sales
OPTIONS (schema_name 'sales_data', table_name 'sales_summary');

GRANT SELECT ON sales_summary TO dwh_writer;

\c dwh dwh_writer

dwh_writer@dwh=> SELECT count(*) FROM sales_summary;
ERROR:  password is required
DETAIL:  Non-superusers must provide a password in the user mapping.

\c dwh postgres

postgres@dwh=# SELECT count(*) FROM sales_summary;
 count
-------
     2
(1 row)


Auto Update Statistics Duration and Interruptions Our production SQL Server 2008 R2 sometimes begins to perform slowly: all queries begin to work too slow (and sometimes all queries just hang and wait something then continue). This usually lasts several minutes.

I’ve read that sometimes auto update statistics can slow down the server:

Is there a reason to update statistics manually?

I see that sometimes auto update statistics occurs for rather large indexes (1.7 GB size, table of 5.5M rows). I can see the date of the most recent update for statistics via STATS_DATE, but I can’t see the duration of this process.

Could you, please, suggest how can I see the duration of the statistics auto update? Or how can I determine that auto update statistics causes problems?

Thank you.

P.S.
After we’d encountered these performance problems, I began to monitor wait stats. The highest wait type is CXPACKET. It varies from 36% to 60% of all waits. And in the morning, when working day begins, it has the maximum value during the working day (users usually work with application 12 hours a day). The second highest wait type is SOS_SCHEDULER_YIELD (15%-20%).
I found some posts in the Internet where it’s mentioned that CXPACKET wait time reveals that auto update stats makes problems. But I haven’t found the real proof of such statements. It is clear for me that CXPACKET can show problems with parallel execution of SQL queries (from http://www.dbadiaries.com/reducing-sql-server-cxpacket-wait-type).

You can grab this data with the Auto Stats Event Class with SQL Trace.  One of the event's data columns is Duration.  As per the referenced BOL documentation above, here is a quote:

  Duration: Amount of time (in microseconds) taken by the event.

That should give you a pretty good idea when/how long auto stats is taking.  If you are seeing a lot of performance-halting auto update stats (synchronously), you could consider utilizing auto update statistics async.  Turning this on will tell the query optimizer to not wait for statistics to be updated in order to continue compiling the query.  This is one solution in order to prevent the direct interference of stats updates, but of course you're sacrificing fresh statistics for that particular query compilation.

For more information on this, please see the MSDN reference on Statistics.


Postgres rewriting entire table We have a database that is just over 200 gb in size, with around 430 million rows, a primary key and a spatial index. Every few weeks we get an update which involves around 3 million rows, deleting some old features and updating new ones. This is done offline.
We also have a history table, which stores the accumulated deletions from each update. This table is currently around 20 gb and 40 has million rows.
Essentially, we first insert into the history table anyting that is marked as to delete in the change table, then delete everything from the main table, that is either to delete or new, and then insert into the new table all the changed records (all offline, to be clear).

If I run this in a single procedure/function a table rewrite seems to be triggered, as I have come back after a few hours and found it failed due to using up the available disk space, around 100 gb. If I run it line by line in a psql console, no such problem. As there are no DDL statements and the total number of changed rows is vastly smaller than the total database size, I am puzzled as to what might be causing this amount of temporary disk usage, as the total space required to record the entire change as one transaction would seem to be much less.

Is there some rule of thumb for this or a simple diagnostic query that I can run to see what is happening?

I'd say the difference is that your PL/PgSQL procedure runs in a single transaction.

If you run line by line in psql, unless you explicitly BEGIN and COMMIT, you're running in individual transactions. This can be quite a lot slower, but it also means that autoVACUUM can come along to free and reuse deleted rows, so subsequent UPDATEs can write new row versions into those freed spaces.

If you're in a single transaction, the system has to keep the old row versions around because it needs them in case you ROLLBACK the transaction or hit an error.

So you can't really do what you want in PL/pgSQL alone, unless you're willing to use hacks like dblink. You need to batch your work into a series of smaller transactions and since PostgreSQL doesn't yet support autonomous commit from within a PL/PgSQL function, that means dblink or an external client.


Custom calendar for calculating workdays per month I need to create a custom report for my PMO (Project Management Office) where they want allocation to be calculated by multiplying working days for each month * total hours.

For example, January 2014 should have 21 working days (2 holidays minus Saturday and Sunday) * 8 = 168 hours.

How would I go about creating this logic for the calendar? Should I focus on creating a function that will calculate working days for each month? Also we have various calendars but the server that I'm using doesn't have any connection to them.

I'd start with a table that contains the date and some convenience columns plus a workday indicator.  (date full db date column, day, month, year, day_of_week, work_day Y/N field, isvacation, isweekday).

Populate that with values in the time range you're going to use.  Set appropriate flags (e.g. update date_table set isweekday where day_of_week between 1 and 5) and update all the work_day and isvacation flags by vacation that your company observes.  This is going to be a manual process, but not daunting.  Some holidays are the same day each year, some are not.  A vacation day could fall on the weekend, but be observed the previous Friday, etc.

Then you can calculate work hours pretty easily (e.g. select count(1)*8 from date_table where month = 1 and year = 2014 and work_day = 'Y'

Once the table is created, you can use it for lots of stuff.  Add a quarter field, first work day of the month field, day names, month names, etc..  Pretty handy, especially for reporting, just join on the truncated date column in your source data to the date field in the table.


Best practice to shrink Tempdb in a production environment What is best practice to use when shrinking Temporary db in SQL Server 2008?

Is it risky to use the following?

use tempdb
GO

DBCC FREEPROCCACHE -- clean cache
DBCC DROPCLEANBUFFERS -- clean buffers
DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
DBCC FREESESSIONCACHE -- clean session cache
DBCC SHRINKDATABASE(tempdb, 10); -- shrink tempdb
dbcc shrinkfile ('tempdev') -- shrink db file
dbcc shrinkfile ('templog') -- shrink log file
GO

-- report the new file sizes
SELECT name, size
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

It is a best practice to proactively monitor the normal usage of Tempdb and set the size accordingly.
If this is one off case where the Tempdb has grown to such a size and its a PROD env, I would restart SQL Server Services during weekly maintenance. There after Tempdb would go back to its configured size.

Shrinking the file is fine as long as Tempdb is not being used, else existing transactions may be impacted from performance point of view due to blockings and deadlocks.

Cleaning procedure cache, buffer caches etc will have negative impact on the database performance itself until those are not re-created. I would not do this on PROD.

Hope that helps!


Backup of Databases not present in Availability Groups How can I take backups of databases that are not present in Availability Groups?

Can I use a single maintenance plan for both AG dbs and non AG dbs?

Should the copy_only option be enabled for non AG dbs too? I know Ola Hallengren's script has an option to include non-AG databases, but I want to stick with the in-built plans as of now.

The databases that are not in the AG are on the primary node. Should I create a different maintenance plan for them? All databases are on the primary including the non-AG dbs.

I understand copy_only option allows us not to break the LSN chain. This is a  2 node setup with a primary and secondary replica.

These are vendor based databases, so the business is fine with a backup of at least a day earlier from the backup schedule.

I checked the job history of the non-AG databases. There are only 2 of them, and I see the backup files being copied to the backup drive. But the job history still shows that "The job failed at step 1 - job was invoked" . I am not sure what's wrong. I can see the backup files in the drive.

This is the message I see in the history:

  The job failed.
  The Job was invoked by Schedule 1100 (DailyBackup_NonAAG).
  The last step to run was step 1 (Subplan_1).

I am also adding the creation start and finish days of a database to give you a clear idea:

database_creation_date  backup_start_date   backup_finish_date
2016-12-22 20:31:43.000 2017-04-05 00:08:34.000 2017-04-05 00:34:31.000

It really Depends,

Technically, as others have mentioned, you can use one built in maintenance plan to backup all of the databases.  Regardless if they are in an AG or not in an AG.

But having it all in one plan, and having that plan fail, means you may miss some SLAs.  There is a question I ask myself when setting up backups:  "What is the Restore Strategy and the SLA?" Your backup strategy should account for your restore and recovery requirements in the SLA.  Can you restore exactly the amount of data that business requires, in the time frame required?  Imagine if your one maintenance plan fails, and only backups up half of the databases one night. What happens if a disaster occurs between the failed backups and the next backup cycle?  Can you recover the data and get the business back online?  It depends on your SLA.  That should help you decide if you need one job, two jobs, or a different strategy entirely.

Also Since you mentioned,  "Do i need to use copy_only?", am I right in assuming you have Databases acting as principals/primaries, and other in an AG group acting as secondary replicas?  If so you can still techincally use one job but you need to understand what copy_only does. The article has some good highlights.


Login issues for the same SQL login on mirror server working fine on principal server I will try my best to describe the situation, while we've been trying to figure this out:

We have this SQL Login [LA] which shows successful connection on principal server in the sql error logs [connection made from various apps]. This login shows successful connection on its mirror server as well.

Now, here is this another login [LB] which connects successfully on principal server but fails to connect on mirror server. [same application as above login LA connects]

Troubleshooting done:

run sp_help_revlogin on both servers and matched the hashed password for login [LB]. Thus that confirms password is same for this login in terms of SQL login for both principal and mirror.
SID's of login is same at both servers, so this should not be a problem as well.
Application uses same creds to connect on principal which is a success but shows login failed on mirror server.

Since something is making the login unsuccessful attempts, login keeps getting locked out.

What else can i check as a DBA before passing this as an application issue to the app owners?

Please help me troubleshot to find the root cause of this issue, thank you.

If logins are getting locked, that indicates the password is incorrect.  Either the server has the incorrect password recorded for the login, or the client is presenting the wrong password.

Since this is a mirroring environment, I'd bet the client is using the same password for both instances; meaning the password for the login on the mirror is not the same as the password for the login on the master.

Recreate the login on the mirror server with the known-good password, i.e. not the hashed version.

Ensure you get the sid correct (use copy-and-paste) from this query, ran against the principal server:

SELECT sp.name
    , sp.sid
FROM sys.server_principals sp
WHERE sp.name = 'login_name';

Use that sid in the CREATE LOGIN statement you run on the mirror:

CREATE LOGIN login_name
WITH PASSWORD = 'thepassword'
    , SID = 0x000000000000 --put the real sid here.
    , DEFAULT_DATABASE = tempdb;

The sid is only important because it links the login to the user inside the database.


View MySQL Event Body Is there a way to view the body of a MySQL event? The SQL part after the DO section? I can easily rename, enable, disable, even alter the schedule and the body separately, but exactly viewing what my event is doing, seems arduous to achieve.

The closest thing I've found was using the recreate command SHOW CREATE EVENT event_name, like explained here on the official MySQL site.

Is there another command or trick to retrieve the body of a MySQL event?

Running the following command retrieves the body of a mysql event:

SELECT EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='event_name'


Postgresql - Sybase concepts correspondence The following diagram depicts the main PostgreSQL concepts pretty nicely:

I find myself having to work with Sybase ASE 15 and I was wondering what the similar concepts are in Sybase. E.g. from what I've understood so far Sybase lacks the concept of schemas. So some questions are:

does Sybase ASE 15 have the concept of schemas?
what is the boundary for referential integrity constraints? E.g. in PostgreSQL you can have foreign keys across schemas (but not across databases). What is the situation in Sybase ASE 15?

SAP/Sybase ASE uses a slightly different conceptual model than PostgreSQL, Oracle & SQLServer, which all have the concept of a schema.

PostgreSQL  |   SAP ASE
-----------------------
Cluster     |   Database Server or Database Server Cluster
Catalog     |   Database
Schema      |   User
-----------------------

In ASE, a cluster is multiple physical Database Servers working as a single logical Database Server.  There is no separate designation if a Database Server (single or cluster) is running multiple databases, since that is the expected operation.

Database is the collection of related objects (Tables, views, triggers, users, etc).

Schemas: In ASE a user can own objects, and set permissions on those objects independently, which satisfies some of the same use cases for schemas BUT a user can not be dropped from the database if they own any objects.  So for practical purposes, most sites don't use user level objects.

The lowest granularity storage object in ASE is a page (other databases call this a block) the size of which is set when the server is installed, but can be 2k, 4k, 8k or 16k.  This means that the database allocates pages in chunks equivalent to the page size setting.

Referential integrity can cross database (schema) boundaries.

I highly recommend checking out sypron.nl.  Rob Verschoor is an SAP VP & Engineer and his site is packed with good information, tips, tricks and scripts.  His quick reference books are also VERY good, and I would recommend them to anyone needing to work with ASE, Replication Server or IQ.


Is it useful to have the SQL Server instance root directory on a separate drive? I know it's possible to change many of the default paths when installing SQL Server, and generally when I do an install I change the data and log folders to be on separate drives (typically D and E), however I've recently been given a pre-installed machine which is running an instance name other than the default and they've configured the instance root directory to be on the D drive along with the mdf files. This means that on what would normally be a relatively clean drive with just folders and database files on it I now have a full installation of the SQL Server binaries as well.

i.e. I now have the following :

C:\Program Files\Microsoft SQL Server\ --Base Install
D:\Microsoft SQL Server\MSSQL10_50.MyInstance --Instance Binaries
D:\Microsoft SQL Server\MSSQL10_50.MyInstance\MSSQL\DATA --Data Files
E:\Microsoft SQL Server\MSSQL10_50.MyInstance\MSSQL\LOGS --Log Files

Where normally I would run with something like :

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\ --Base Install & Default Instance Binaries
D:\MSSQL\DATA --Data Files
E:\MSSQL\LOGS --Log Files

I can understand why having a separate instance binary folder is necessary, but I can't see why it would be useful to put all those binaries on a separate drive.

Can anyone tell me why it might be a reasonable thing to do? Or perhaps it just makes no difference at all? To me it just seems terribly untidy...

With regards to splitting the instance root there are a couple arguments in favor of doing it.

Some People are in favor of keeping their "C" drive dedicated to just the OS and OS binaries. This can give you some different options for recovery in the event of a crash on the C drive, it can help keep the OS from causing or receiving space related issues from sharing with other apps.
You are isolating SQL Server's binaries from other programs and ensuring availability of some of the critical folders like the Logs folder where error logs go - this folder needs to be accessible for SQL Servers start up. You are protecting yourself from others, basically.

You can put the SQL Server binaries/instance files in the same place you tend to put your other program files. But if you do that - at least make sure you take your system database files and potentially your default backup location and move it someplace else..

Here is what I tend to do when given an unlimited number of drive letters to play with (at a minimum.. Letters aren't important here):

C - OS and system level files. Only
D - Program files for all apps (including SQL Server)
S - Instance level files/SQL Server system databases and log files typically (except for TempDB) (note.. If I have multiple instances, I won't make 4 of these.. I'd put all SQL binaries for all instances on S in most situations, with the folders providing the separation)

(ED- Another note - I often don't have an "S" drive available. At the end
   of the day, having your system database files for Master, Model, MSDB
   and Resource db living on the same drive as some of your user
   database files, but in a separate folder for logical separation to keep things less confusing isn't the end of the world.)

F - Data files for user databases
L - Log file drive for user databases
T - TempDB
X - Backup drive (though in a lot of cases I elect to stream a backup across to a network drive, not paying for a copy after the backup and I'm immediately backing up to storage someplace else.)

I'll often have more data and log drives and sometimes another TempDB drive. Add in multiple instances and you can run out of drive letters quickly. You can certainly get away with putting your instance level files on C:. And I do a lot of health checks for clients that were setup like that - and I never say "oh wow.. we have to fix that now" - Now  if their TempDB file(s) are there, too, I'll typically have them change that. Sometimes move their master and MSDB databases as well..

But the world won't end if you don't split these things up. I think the benefit is really just keeping -your- files separate. As a DBA you should have a healthy paranoia around other roles at your company, other applications, other installations, etc. and the more you can isolate yourself from the potential for conflicts, the better you'll be. And it gives you some more options for reinstallation and recovery. So yes separate your binaries from C.. But my advice wouldn't be to go crazy on a separate drive for each instance..


MSSQL - Uniquely identify an application connecting SQL Server we are implementing a database firewall on Oracle based on a unique value associated to a given application. With this value, we can reliably identify the application, even if one changes the name of the application before connecting to the RDBMS.

We plan on doing the same thing with SQL Server.

I've already seen that there is a connection_id column in sys.dm_exec_connections, but it identifies a connection, not an application.

In SQL Server, iIs there something, that could reliably identify an application ?

The only way I can think of is, using "Application Name" in the connection string.
That is how SQL server recognises different applications.

I think, you can append application name in the connection string at runtime using your v$session column.


High number of undistributed commands after a "An existing connection was forcibly closed by the remote host" event For the lasts days I'm getting a high undistributed commands value in the replication monitor. Restarting the Server Agent solve the problem (momentarily) but after few hours starts again. Even when there are a lot of undistributed commands, I inject a token and get low latency (00:00:09 the most high). sp_replmonitorsubscriptionpendingcmds return the same values from the Replication Monitor.

The 'Distributor To Subscriber History' shows the message "TCP Provider: An existing connection was forcibly closed by the remote host". I'm looking for network issues and I didn't find nothing, just in the Application logs are the events 14152.

There are 6 replication jobs and they fail in similar time, but not in the same moment (all are replicating to the same subscriber), I means, in a period of few minutes between them. If was a network issue, they must broke at same moment, right?

After restart the agent or the server, it's working for several hours. I didn't find any pattern in the time, sometimes broke in the morning, others in the afternoon, etc.

How can I continue debugging the problem?

note: SQL server 2008 R2

It's almost certainly a network/connectivity issue, even if the jobs don't fail at the same time.

Error 14152 is logged when "Agent shuts down after unsuccessfully retrying an operation (agent encounters an error such as server not available, deadlock, connection failure, or time-out failure).", and if the last error was "existing connection was forcibly closed" that suggests there were also earlier tries with the same result even if not logged.

There are many reasons this could be happening intermittently. One specific reason can be multiple NICs in the distributor having the same IP assigned. It could also be the network connection is simply overloaded at times.


restoring database using RMAN If I have new data like new users and tables created after backing up  database with RMAN , in case of restoring database will the operation affect my new data made after taking the backup?

No. RMAN is aware of that possibility, hence why it creates a snapshot controlfile before performing backup. Rman needs to access control file to map objects to be backed up, and as controlfile is always in use, snapshot is used for that specific point in time. When you restore a backup, you're simply telling oracle to place those files from the backup back, so you don't need to worry about your data getting overwritten.


database development environment, MySql I need to create copy/replication of our databases for the development environment.

I'm not sure how to do it. DBs are updating many times during a day; not only new records added but also  old records are updated.

How should I implement it?

The "right answer" is highly application dependant, but here are a few strategies I've seen used before.

Daily Backup / Restore-on-demand

This works well if you have a small data set, and the need to be able to have various developers use the backups ad-hoc. The idea is basically a mysqldump on a replication target system and some scripts to do a MySQL import.

Binary Log Replication

This method probably won't work for you, as you specifically want to read and write to the development database. Sometimes people will use replication to run read-only regression testing against, but modifying the database will result in failed replication and/or inconsistent data.

[https://serverfault.com/questions/380701/replicate-main-mysql-db-to-a-development-server-to-play-with-real-data]


MySQL and AUTO_INCREMENT columns in multiple-column index for InnoDB tables: support status? Missing support for AUTO_INCREMENT columns in multiple-column index for InnoDB tables is a widely known limitation, as is the trigger workaround for when it is needed (see e.g this post). Yesterday, however, I ran into this thread claiming support for this feature was added as early as 5.1. I tried the following statement on my 5.7 install (mysql-community-server-5.7.19-1.el7.x86_64), and it appears to work flawlessly:

CREATE TABLE `tbl1` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `data` datetime NOT NULL,
  PRIMARY KEY (`id`,`data`)
 ) ENGINE=InnoDB;

My problem is that, for the life of me, I cannot find a positive statement of support of the feature on MySQL's documentation site - or elsewhere. I am rather wary of deploying in production something unannounced  (which might be experimentally present) so I hope somebody can point me to the official status of this.

Edit: It turns out that the manual contains, in parts unrelated, this rather oblique statement:

“…an InnoDB table with an AUTO_INCREMENT column requires at least one key where the auto-increment column is the only or leftmost column.”

which suggests, but does not state, support.

Edit 2: Using information from @rolandoMYSQLDBA below, I also located this additional snippet: "An AUTO_INCREMENT column must appear as the first column in an index on an InnoDB table.". I am therefore accepting his answer.

And in case someone is wondering, the multiple column index thing is needed for table partitioning - the customer has auto_increment PK on all tables and they want to partition on a date column.

Under normal circumstances, I would have closed this question as a duplicate. There are already close votes. In this particular instance, since you put a bounty on this question, I'll try to answer it as best as I can.

Look at the first post you mentioned. Note the table definition

CREATE  TABLE `issue_log` (
`sr_no` INT NOT NULL AUTO_INCREMENT ,
  `app_id` INT NOT NULL ,
  `test_id` INT NOT NULL ,
  `issue_name` VARCHAR(255) NOT NULL ,
primary key (app_id, test_id,sr_no)
);

The auto_increment column is sr_no. This is neither the only column nor the leftmost column in the primary key definition. Now, if the primary key was written

primary key (sr_no, app_id, test_id)

then sr_no would be leftmost. No error would have occurred with InnoDB.

When you ran the command

CREATE TABLE `tbl1` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `data` datetime NOT NULL,
  PRIMARY KEY (`id`,`data`)
) ENGINE=InnoDB;

your auto_increment column id was leftmost.

You made reference to a second post. Please note that in the answer to that second post, it clearly notes that MyISAM and BDB storage engines support auto_increment ids. When it comes to MyISAM, the auto_increment behavior allows an auto_increment number to be paired with one or more keys in such a way that the number 1 can appear multiple times but be associated uniquely with the columns. I have discussed this a few times over the years:

Apr 21, 2012 : How can you have two auto-incremental columns in one table?
Jun 10, 2012 : MySQL get next unique value without auto increment
Feb 26, 2013 : How to use 2 auto increment columns in MySQL phpmyadmin
Aug 28, 2013 : How to go about modeling the main object of relationship?

Keep in mind that while InnoDB support for an auto_increment column in a PRIMARY KEY exists, it does not have the same auto_increment behavior as MyISAM. MyISAM can bind with other columns to allow a number to exist multiple times, InnoDB does not do that. Once an auto_increment value is used, it is unique for the whole table.

The excerpt you found

  an InnoDB table with an AUTO_INCREMENT column requires at least one key where the auto-increment column is the only or leftmost column.

comes from a page in the MySQL Documentation that has to do with how replication sees and handles auto_increment values. This is a vital issue since the order data are written on a Master may get serialized in a different order on a Slave.

Just recently (back on Jun 16, 2017) I answered Does it ever make sense to create an index with additional columns after the primary key?, where I mentioned from the MySQL Documentation that when there is an auto_increment column in a partitioned table, it is mandatory that the column is included in the PRIMARY KEY along with the columns that define the sharding of the table rows.

To conclude, let me just say this: support for auto_increment columns in a PRIMARY KEY in InnoDB does not come with any fringe benefits or additional bell-and-whistles like it does with MyISAM. That's why the MySQL Documentation does not have a lot written about it.

Support for this was mentioned long ago. If you look in the MySQL 5.1 Documentation on InnoDB Restrictions, you see bulletpoint #15

  For an AUTO_INCREMENT column, you must always define an index for the table, and that index must contain just the AUTO_INCREMENT column. In MyISAM tables, the AUTO_INCREMENT column may be part of a multi-column index.

There is more extensive stuff in the MySQL 5.1 Documentation AUTO_INCREMENT Handling in InnoDB. It is also available in the MySQL 5.5 Documentation. Unfortunately, these are in the old Documentation format. This is probably why you can't find anything now. Just be assured that the support is there.


SMO Copy database or remove all non-system database objects I have c# SMO script which copy all database objects to existed empty database or to a new database.

But this approach has two problems

Existed database is not empty, manual intervention is needed.
New database will have different options like DB Owner, disk
management etc.

Because I need to Clone existed database from one instance to another and I am able to copy only database objects right now, therefore my question is

it is possible to create a new database according to settings of
existed one ?
it is possible to drop all objects, regardless on dependencies, from existed database ?

my script look like

Transfer transfer = new Transfer(databaseSource);
transfer.CopyAllObjects = true;
transfer.CopyAllUsers = true;
transfer.Options.WithDependencies = true;
transfer.DestinationServer = serverDestination.Name;
transfer.DestinationDatabase = databaseDestination.Name;
transfer.DestinationLoginSecure = true;
transfer.CopySchema = true;
transfer.CopyData = true;
transfer.Options.Permissions = true;
transfer.Options.ContinueScriptingOnError = true;
transfer.TransferData();

You could use SELECT * FROM sys.databases d WHERE d.name = '<target_db_name>'; to obtain the existing settings for the database prior to running the transfer.  Store the results in a DataSet so you can potentially use them later.

If the database does not exist, you'll get no rows returned.  If the database does exist, you could drop the existing database as the first step.

After you run the TransferData() method, you could apply the necessary configuration changes based on the items in the DataSet.  For instance the recovery_model_desc column contents could be used to set the recovery model by running this code:  ALTER DATABASE <target_db_name> SET RECOVERY = 'xxx';  where xxx is the value contained in the column.


Differences between .dump and .sql in pg_dump PostgreSQL I'm trying to take a backup of schema using pg_dump like,

$ pg_dump -U ganapathy -n hotel_management > ganapathy_schema.dump

It create the backup, But I can't restore it using pg_restore. It give error like this,

  pg_restore: [archiver] input file does not appear to be a valid
  archive

Then I try to take the backup with .sql extension like,

$ pg_dump -U ganapathy -n hotel_management > ganapathy_schema.sql

But It also gives the same error.

  pg_restore: [archiver] input file does not appear to be a valid
  archive

After that I try to take a backup by specifying the format like, -Fc in both .dump and .sql extensions.
Then I try to restore it. It restored successfully.

So now my doubt is,

  What is the differences between the extensions of .dump and .sql in
  pg_dump in PostgreSQL?

Because both needs to specify the format.

Thanks in advanced...

The file extension means nothing. At all. It's just a part of a file name.

If you want a custom-format dump for use with pg_restore use -Fc as an argument to pg_dump.

pg_dump defaults to generating SQL-format dumps for use with psql.

See the manual for more details.


CDC on Sql Server 2017 with HA cluster stopped working I am currently using Sql Server 2017 (14.0.3294.2) running on Windows Server 2016 as an HA cluster, one primary with two secondaries.
A few days ago, the primary was rebooted and I think the person that did it executed some operations that made the primary server primary again. Since then, CDC stopped working. Querying the cdc tables doesn't offer a clue about the status. The CDC job is enabled.
I found this post: https://stackoverflow.com/questions/23744083/cdc-is-enabled-but-cdc-dbotable-name-ct-table-is-not-being-populated, however, I am very reluctant to use the solution in this context.
The last error in the CDC job log is (after the primary was restarted):

Executed as user: UserName. Failed to update database "DatabaseName"
because the database is read-only. [SQLSTATE 25000] (Error 3906).
NOTE: The step was retried the requested number of times (10) without
succeeding.  The step failed.

Any suggestions on how to make CDC work again?
Thank you

it seems that the problem is generated by the fact that the database is not primary.
In fact it is read-only state. Try to fail back the relative AG group.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/perform-a-planned-manual-failover-of-an-availability-group-sql-server?view=sql-server-ver15
About the cdc job you need to have it in every node that of the AG cluster.
Take a look at how to manage cdc in an alwayson environment:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replicate-track-change-data-capture-always-on-availability?view=sql-server-ver15


What are the effects of GRANT DELETE? What is the difference between:

GRANT DELETE ON TABLE1 TO ROLE1

and

GRANT DELETE TO ROLE1

One of our DBAs executed the second version by mistake.  I want to be certain I understand all the ramifications.

The first is table specific. The second allows deletion from any table


Postgresql :i want tro truncate column I have

select * from generale where titolo  ~* 'hp-ux' order by titolo;
      autori       |                                                   titolo                                                    |        casaeditrice
-------------------+-------------------------------------------------------------------------------------------------------------+----------------------------
 Asghar Ghori      | HP Certified Systems Administrator: Hp-ux 11i V3: Exam Hp0-a01: Training Guide and Adminstrator's Reference | Endeavor Technologies Inc.
 Autori vari       | HP-UX tuning and performance                                                                                | Prentice Hall

The line title is very long for Book Entry Hp Certified...etc,etc
I want to truncate to this

 Asghar Ghori      | HP Certified Systems Administrator:....

The ... are not important i want to truncate column output to max 20 characters,
on oracle i use column titolo format a20 how to do this on postgres?
Thanks

try this

select autori, substring(titolo,1,20) || '...'
from generale where titolo  ~* 'hp-ux' order by titolo;

it truncates your output but doesn't touch the data in the table


Calculating values from three related tables, without using join or union I have a simple basic three tables on SQL Server database:

What is needed is to get the quantity available, without using joins or unions which is calculated:

  Quantity Available  = Items Opening Balance - Items Delivered +
  Items Received

Any Ideas are welcome.

It seems you are being asked to write the query using correlated subqueries, for example:

SELECT
    IOB.ItemID,
    IOB.Descript,
    IOB.Quantity +
    (
        -- Total received for this item
        SELECT ISNULL(SUM(IR.Quantity), 0)
        FROM dbo.ItemsReceived AS IR
        WHERE IR.ItemID = IOB.ItemID
    )
    -
    (
        -- Total delivered for this item
        SELECT TotalDelivered = ISNULL(SUM(ID.Quantity), 0)
        FROM dbo.ItemsDelivered AS ID
        WHERE ID.ItemID = IOB.ItemID
    )
FROM dbo.ItemsOpeningBalance AS IOB;

SQLFiddle Demo

If you're using SQL Server 2005 or later, you could also use APPLY (a lateral or correlated join) but I suspect this would be viewed as not in the spirit of the exercise. The following actually requires SQL Server 2008 or later, because it (gratuitously) uses GROUP BY ().

SELECT
    IOB.ItemID,
    IOB.Descript,
    QuantityAvailable =
        IOB.Quantity +
        ISNULL(ItemsReceived.Quantity, 0) -
        ISNULL(ItemsDelivered.Quantity, 0)
FROM dbo.ItemsOpeningBalance AS IOB
OUTER APPLY
(
    SELECT SUM(IR.Quantity)
    FROM dbo.ItemsReceived AS IR
    WHERE IR.ItemID = IOB.ItemID
    GROUP BY ()
) AS ItemsReceived (Quantity)
OUTER APPLY
(
    SELECT SUM(ID.Quantity)
    FROM dbo.ItemsDelivered AS ID
    WHERE ID.ItemID = IOB.ItemID
    GROUP BY ()
) AS ItemsDelivered (Quantity);

SQLFiddle Demo


In PostGIS, how do I swap the lat/log of invalid points, like MySQL ST_SwapXY? MySQL has a function ST_SwapXY which swaps the x and y values (long, and lat). A function, like ST_SetSRID, that's useful if you import data incorrectly.

  Accepts an argument in internal geometry format, swaps the X and Y values of each coordinate pair within the geometry, and returns the result.

How do I do this in PostGIS?

With PostGIS you would use the accessors ST_X() and ST_Y(), and recreate the point with a constructor like ST_MakePoint(x,y) for example:

SELECT ST_AsText(geom) AS old, ST_AsText(newgeom) AS new
FROM ( VALUES (ST_MakePoint(0,1)) ) AS t(geom)
CROSS JOIN LATERAL
  ST_SetSRID(  ST_MakePoint(ST_Y(geom),ST_X(geom)), ST_SRID(geom)  )
AS t2(newgeom);
    old     |    new
------------+------------
 POINT(0 1) | POINT(1 0)
(1 row)

You can create a simple function _ST_SwapXY to accomplish this on points.

CREATE FUNCTION _ST_SwapXY(geom geometry(point))
RETURNS geometry(point) AS $$
  SELECT ST_SetSRID(  ST_MakePoint(ST_Y(geom),ST_X(geom)), ST_SRID(geom)  );
$$ LANGUAGE sql
IMMUTABLE;

CREATE FUNCTION _ST_SwapXY(geog geography(point))
RETURNS geography(point) AS $$
  SELECT ST_SetSRID(  ST_MakePoint(ST_Y(geog::geometry),ST_X(geog::geometry)), ST_SRID(geog)  )::geography;
$$ LANGUAGE sql
IMMUTABLE;

Testing it over a geography and geometry object (EWKT is Extended Well-known Text (WKT) to include the SRID.)

SELECT ST_AsEWKT(pt) AS orig,
  ST_AsEWKT(  _ST_SwapXY(pt::geometry)   ) AS geom,
  ST_AsEWKT(  _ST_SwapXY(pt::geography)  ) AS geog
FROM ( VALUES
  (ST_MakePoint(0,1)),
  (ST_SetSRID(ST_MakePoint(0,1),4326))
) AS t(pt);
         orig         |         geom         |         geog
----------------------+----------------------+----------------------
 POINT(0 1)           | POINT(1 0)           | SRID=4326;POINT(1 0)
 SRID=4326;POINT(0 1) | SRID=4326;POINT(1 0) | SRID=4326;POINT(1 0)

Note: MySQL's ST_SwapXY() is substantially more powerful covering LINESTRINGS and POLYGON too, but hopefully your structures are not internally that screwed up. If they are you'll have to recursively do this.


Purge mysql log bins older than two weeks automatically I have to manually write

purge binary logs bin to "mysql-bin.somenumbers" ;

Once in a while, I need to reduce the spaces that binlogs are taking on my web server. Is it possible to purge binary logs bins automatically every two weeks? probably calling a bash script in crontab?

I have MySQL 5.5 running on CentOS 6.5

I am glad you found your own answer.

I am sure you found out you can add this to my.cnf

[mysqld]
expire_logs_days = 14

You do not have to restart mysql. You login as root@localhost and run this:

mysql> SET GLOBAL expire_logs_days = 14;

To trigger the auto rotate immediately, run

mysql> FLUSH LOGS;

If you want to zap binary logs from the mysql console that is older that two weeks, use the PURGE BINARY LOGS BEFORE syntax. For example, here is a query to give you midnight two weeks ago from today's date:

SELECT DATE(NOW() - INTERVAL 2 WEEK) + INTERVAL 0 SECOND;

Just apply that query to PURGE BINARY LOGS BEFORE

PURGE BINARY LOGS BEFORE (DATE(NOW() - INTERVAL 2 WEEK) + INTERVAL 0 SECOND);


Querying non-ASCII rows from Postgres Does [:ascii:] class work in Postgres at all? It is not listed in their help, however I see examples in the web which utilize it.

I have a UTF-8 database, where collation and c_type are en_US.UTF-8, and Postgres version is 9.6.2.
When I search for non-ASCII rows like this:

select title from wallabag_entry where title ~ '[^[:ascii:]]';

I get both Unicode and non-Unicode symbols (full output is here):

Сталинская правозащитница: мать Меленкова бабушка Настя
Дневник НКВДиста Шабалина: Знает ли Москва положение на фронте?
Бег по городу и поездка на осле: как в средневековье наказывали прелюбодеев
Как комиссар Крекшин в 1740-е чуть не отменил историю России
Have you heard of Saint Death? Don’t pray to her.
Архаїчна українська мова: перевага чи недолік?
Гренада не их
China’s marriage rate is plummeting because women are choosing autonomy over

What is wrong with this query?

To answer your question: [:ascii:] works. You may have some characters in your text that you do not recognize as non-ASCII, yet they're there. They can be something like a non-breakable space, for instance, or any other Unicode space character.

It is not strange to have non-breakable spaces (&nbsp;) in texts that you copy-and-paste from a web page, yet you don't notice they're there.

Here is an example to show:

WITH t(t) AS
(
    VALUES
      ( 'Сталинская правозащитница: мать Меленкова бабушка Настя' ),
      ( 'Дневник НКВДиста Шабалина: Знает ли Москва положение на фронте?' ),
      ( 'Бег по городу и поездка на осле: как в средневековье наказывали прелюбодеев' ),
      ( 'Как комиссар Крекшин в 1740-е чуть не отменил историю России' ),
      ( 'Have you heard of Saint Death? Don’t pray to her.' ),
      ( 'Архаїчна українська мова: перевага чи недолік?' ),
      ( 'Гренада не их' ),
      ( 'China’s marriage rate is plummeting because women are choosing autonomy over ' )

)
SELECT
    t,  regexp_replace(t, '([^[:ascii:]])', '[\1]', 'g') AS t_marked
FROM
    t
WHERE
    t ~ '[^[:ascii:]]' ;

That's what you get:

                                       t                                       |                                                                                t_marked
-------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Сталинская правозащитница: мать Меленкова бабушка Настя                       | [С][т][а][л][и][н][с][к][а][я] [п][р][а][в][о][з][а][щ][и][т][н][и][ц][а]: [м][а][т][ь] [М][е][л][е][н][к][о][в][а] [б][а][б][у][ш][к][а] [Н][а][с][т][я]
 Дневник НКВДиста Шабалина: Знает ли Москва положение на фронте?               | [Д][н][е][в][н][и][к] [Н][К][В][Д][и][с][т][а] [Ш][а][б][а][л][и][н][а]: [З][н][а][е][т] [л][и] [М][о][с][к][в][а] [п][о][л][о][ж][е][н][и][е] [н][а] [ф][р][о][н][т][е]?
 Бег по городу и поездка на осле: как в средневековье наказывали прелюбодеев   | [Б][е][г] [п][о] [г][о][р][о][д][у] [и] [п][о][е][з][д][к][а] [н][а] [о][с][л][е]: [к][а][к] [в] [с][р][е][д][н][е][в][е][к][о][в][ь][е] [н][а][к][а][з][ы][в][а][л][и] [п][р][е][л][ю][б][о][д][е][е][в]
 Как комиссар Крекшин в 1740-е чуть не отменил историю России                  | [К][а][к] [к][о][м][и][с][с][а][р] [К][р][е][к][ш][и][н] [в] 1740-[е] [ч][у][т][ь] [н][е] [о][т][м][е][н][и][л] [и][с][т][о][р][и][ю] [Р][о][с][с][и][и]
 Have you heard of Saint Death? Don’t pray to her.                             | Have you heard of Saint Death? Don[’]t pray to her.
 Архаїчна українська мова: перевага чи недолік?                                | [А][р][х][а][ї][ч][н][а] [у][к][р][а][ї][н][с][ь][к][а] [м][о][в][а]: [п][е][р][е][в][а][г][а] [ч][и] [н][е][д][о][л][і][к]?
 Гренада не их                                                                 | [Г][р][е][н][а][д][а] [н][е] [и][х]
 China’s marriage rate is plummeting because women are choosing autonomy over  | China[’]s marriage rate is plummeting because women are choosing autonomy over

You can see from this, that your problem is the right-apostrophe character. ASCII only supports the apostrophe. Left-apostrophe and right-apostrophe are typographically correct Unicode extensions.

dbfiddle here

You can check it also with previous versions at http://rextester.com/UKIQ48014  (PostgreSQL 9.5) and http://sqlfiddle.com/#!15/4c563/1/0 (PostgreSQL 9.3)

The texts that I guess you think are pure ASCII, and are not:

 WITH t(t) AS
 (
     VALUES
       ('A fully ASCII text!'),
       ('Have you heard of Saint Death? Don’t pray to her.'),
       ('China’s marriage rate is plummeting because women are choosing autonomy over ')
 )
 SELECT
    regexp_replace(t, '([^[:ascii:]])', '[\1]', 'g') AS t_marked
 FROM
    t
 WHERE
    t ~ '[^[:ascii:]]' ;

 | t_marked                                                                        |
 | :------------------------------------------------------------------------------ |
 | Have you heard of Saint Death? Don[’]t pray to her.                             |
 | China[’]s marriage rate is plummeting because women are choosing autonomy over  |

dbfiddle here

These texts are using ’ instead of ' to mark apostrophes.

Check Punctuation: Why is the right single quote (U+2019), and not the semantically distinct apostrophe (U+0027), the preferred apostrophe character in Unicode? ... to see that you're not the first person encountering this problem.


Inability to query underlying table of a schema-bound view I migrated a database from SQL Server 2008R2 to SQL Server 2019 (both Enterprise Edition) using the backup-restore technique, and a set of post-transfer actions like DBCC UPDATEUSAGE or UPDATE STATISTICS XXX.
At statistics update, I get following error:
Msg 402, Level 16, State 1, Procedure ZZZZ, Line 5 [Batch Start Line 0]
The data types datetime and time are incompatible in the add operator.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'ZZZZ' because of binding errors.

I know that the message is quite explicit (the view, which was syntactically correct on 2008R2 is not anymore on 2019). I don't understand why a view defined WITH SCHEMABINDING that is not valid would prevent the update of statistics on an underlying table.
Moreover, I get the same error message when querying the underlying table with a WHERE clause, except if I force a FULLSCAN with following hint:
OPTION(TABLE HINT( $mytable, FORCESCAN ))

I know SQL Server will fire an error in case of DDL change to underlying schema-bound objects, but I don't understand why, if the view is invalid, the underlying objects cannot be used as usual.
I'm sure there is an explanation (like the protection mechanisms for schema-bound views cannot be processed due to the invalidity of the view), but I can't find it for sure in the documentation.
I ran a DBCC CHECKDB without problem. I changed the compat level to 150.

From the documentation:

A query does not have to explicitly reference an indexed view in the FROM clause for the Query Optimizer to use the indexed view. If the query contains references to columns in the base tables that are also present in the indexed view, and the Query Optimizer estimates that using the indexed view provides the lowest cost access mechanism, the Query Optimizer chooses the indexed view, similar to the way it chooses base table indexes when they are not directly referenced in a query.

When you have an invalid indexed view in your database, this Enterprise Edition feature can produce errors when the optimizer considers using the indexed view instead of base table access.
This automatic indexed-view matching feature can be disabled with the EXPAND VIEWS query hint:

EXPAND VIEWS
Specifies the indexed views are expanded. Also specifies the Query Optimizer won't consider any indexed view as a replacement for any query part. A view is expanded when the view definition replaces the view name in the query text.
This query hint virtually disallows direct use of indexed views and indexes on indexed views in the query plan.

When you upgraded your database, a warning was emitted:

Warning: The object "ZZZZ" could not be bound and was ignored during upgrade. Consider reviewing and correcting its definition.

This is easy to miss, and may not even be visible to the user in some scenarios. Still, the lesson is clear: A database with invalid objects may produce unexpected errors.
DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS will also detect the problem.
If you need to run the database on SQL Server 2019, use database compatibility level 100 until you are able to resolve the upgrade issues. This will allow datetimes to be added to times using the addition operator.


Snapshot Azure database for fast revertion to snapshot I have spent a considerable amount of effort creating a set of Azure SQL databases and importing data. Importing the data with the DMA tool took the best part of two days. This is on an 'SQL Server (logical server)'.

I now want to take a snapshot of these databases so that I can quickly return them to this initial state. I assume the best way to do this is to create a backup of each one that I can restore later. I'll probably want to do this restore quite often, so speed of restore is very important; 2 days is certainly unacceptable.

But I'm struggling to find out how to do this. There is no 'Backup Now' button in the Azure Portal that I can find. I've waited a day since creating the databases and still nothing is listed in the Available backups page on the Azure Portal. I looked at 'Configure policies' and this doesn't seem to allow creation of a backup on-demand, or to modify the retention period of an existing backup.

I'm probably barking up the wrong tree. What is the accepted way to create a snapshot of an Azure SQL database and then return to that snapshot quickly?

SQL Server native backups are not available on Azure SQL. Here you have automated backups with a retention period of up to 35 days or you have long-term backups that you can restore.

If you do not want to use long term backups, you can export your databases as bacpac to Azure BLOB storage and import that bacpac at any time you want. You can also export your database to a local on-premises storage.

Additionally you can create a copy of your databases, remove existing databases when you want, and rename the copy database as you wish.


How to translate this query to join? My MariaDB doesn't support IN and I cannot seem to write a proper query that uses JOIN and gives the same result. Here's the query with IN

SELECT * FROM topics WHERE topic_id IN
( SELECT DISTINCT(topic) FROM answers
ORDER BY date LIMIT 500 )

How would that query translate to something that doesn't use IN but JOIN (prefrably)?

You can use a correlated sub-query.

SELECT *
FROM topics
INNER JOIN
(SELECT DISTINCT(topic) as topic FROM answers LIMIT 500) x on x.topic = topic_id


Too many VLFs - How do I truncate them? I have a database that has log file of around 64 GB and the VLF count for the same is 500+. Almost all of them are active. I checked the open transactions and the oldest transaction on that database is from two days back. The session for that transaction is in SUSPENDED state. So, if I kill this SPID, will it mark the VLFs as INACTIVE ? Or will it go into roll back mode and run for another couple of days without marking the VLFs as INACTIVE?

I am not even able to identify from where this transaction is originated. The host name just displays a web server and the Program Name has .Net SqlClient Data Provider so this looks like some transaction initiated by some code in the application.

Too many VLFs - How do I truncate them?

What is to many VLF?

Google around a bit and you find a bunch of different opinions on what constitutes to many. I think the better question is:

What is the right amount of VLFs for my requried log size?

Optimally transaction logs should be on a drive separate from the datafiles.  There should be a single log file, that is sized correctly to meet reoccurring needs without auto growing. VLF should number between 4 and 50, with a maximum size of 500MB. For log files greater than 25GB special consideration is required. Other than during proactive maintenance they should not shrink or grow.

How do I tell how many I have and how big they are?

SQL Server writes logs sequentially. The logs are written into sub sections called Virtual Log Files (VLF).  You can get information about the VLFs of a database with the undocumented DBCC LOGINFO command.  The four fields to understand first are:

FileSize; The size of the VLF in bytes.  Optimally these should be about the same size.  Except for those with a CreateLSN of zero.
FSeqNo; The sequence order the VLFs were last written to.
Status; Has two values, 0 = VLF is available for reuse, 2 = VLF is not available.
CreateLSN; The Log Sequence Number (LSN) when the VLF was created.  If the Value is 0 it the VLF was created at the same time as the database (Original Equipment)

VLF are created when;

The database is created for the first time (CreateLSN = 0) generally it will have between 2 and 8 original equipment VLFs
The logfile is manually grown. i.e. (ALTER DATABASE [MyDB] MODIFY FILE (NAME = N' MyDB_log', SIZE = 51200KB) using T-SQL or the GUI (Graphical User Interface)
The logfile auto grows because all the current VLFs have a “Status” of 2 and more transactions need to be written to the logs.

Status changes;

To 2, when the content is not fully committed and/or in the case of full recovery the transaction has not been backed up.
To 0, when the VLF is available to be overwritten.  All the content is committed and/or the transactions are backed up.

FSeqNo changes when:

The VLF is written to. The value indicates the sequence the VLF was written to. A FSeqNo value of 0 indicates a VLF that has never been used.
The highest value FSeqNo will always have a Status Value of 2, it is the VLF currently being written to.

How do I get the right number of VLF for my required log size?

First it depends on what version of SQL you are running. The DBA can not directly create VLFs.

Files size and number of VLF created per LSN:

The size and number of VLF created is handled by an algorithm based on growth size and SQL version.
SQL 2008 R2 and earlier, same as below but avoid growing in increments or multiples of 4GB

https://connect.microsoft.com/SQLServer/feedback/details/357502/transaction-log-file-size-will-not-grow-exactly-4gb-when-filegrowth-4gb

SQL 2012 and earlier

Grow 1 to 64MB, creates 4 new VLF, each about 1/4 of the growth
Example; 50MB grow gives 4 VLF of 12.5MB
Grow 64MB to 1GB, creates 8 new VLF, each about 1/8 of the growth
Example; 250MB grow gives 8 VLF of 31.25MB
Grow 1GB or larger, creates 16 new VLF, each about 1/16 of the growth
Example; 1GB grow gives 16 VLF of 62.5MB

SQL 2014 and newer

If the growth is less than 1/8 of the current log file (ldf) creates 1 VLF of the growth size
Example: 10GB ldf growing 1GB gives 1 VLF of 1GB
If the growth is more than 1/8 of the current log file uses the SQL 2012 algorithm
Example: 10GB ldf growing 2GB gives 16 VLF of 125MB

What Grow means:

Each grow is resetting the current allocated size (shown as Initial size in the GUI).  If the current size is 500MB and you change it to 1GB (1000MB) you are growing 500MB. This will create 8 new VLFs at 62.5MB each.  All the existing VLF remain unchanged.  If you had 76 VLF you will now have 84 VLF.

Ok, Now I am ready to see what I have and start changing them.  How do I do it?

You can examine VLF on a single database, an entire server, or against a group in Central Management Servers (CMS).

For a single database, run DBCC LOGINFO to get a picture of the current VLF.  The number of rows = the number of VLF.

USE [MyDB]
GO
DBCC LOGINFO

You can use the GUI (Database Properties > Files) or the query below to see file size and location information.  This query also gives used & free space information which is not available in the GUI

select file_id
, type_desc
, name
, substring([physical_name],1,3) AS [Drive]
, physical_name
, state_desc
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB'
, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
, growth / 128 as 'GrowthSettingMB'

 from sys.database_files
 order by type_desc Desc, name

To examine all the databases on server or in a CMS group this query at github from the Microsoft tiger team, provides details on all database with greater than 50 VLF along with code and directions to make improvements.

https://github.com/Microsoft/tigertoolbox/tree/master/Fixing-VLFs

As of August 2017 it does not take into account the SQL 2014 VLF growth considerations outlined in Paul’s post https://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/ Nor does it consider Orginal Equipment VLF. The Tiger solution offers generic solutions. On most occasions after looking at all available information I have chosen a slightly different solution than offered by the Fixing-VLFs query.

What size the log file should be, is to much to include here. For now let's assume you know what the best size is.  The following should be done, off peak or during scheduled down time.

1.Set the auto grow to your first new allocated size

Why? If/when the shrink gets you 4 VLF with a total allocated size of 1MB, and anything happens on the database, the logs will auto grow.

.

USE [master]
GO
ALTER DATABASE [MyDB] MODIFY FILE ( NAME = N'MyDB_log', FILEGROWTH = 4000MB )
GO

Shrink the log

Why? Other than the original equipment VLF the goal is to create, new VLF that are properly sized.  Using SHRINKFILE with a size of 1, will eventually clear all except the original equipment

.

USE [MyDB]
GO
DBCC SHRINKFILE (N'MyDB_log' , 1)
GO

See how many VLF are left

Why? There are several scenarios, you may still have several hundred VLF or only the original equipment VLF left, you may have got down to 1MB and had an immediate auto

.

USE [MyDB]
GO
DBCC LOGINFO

Cycle the t-logs, if you have not cleared off all the old VLF.

Why? If anything other than original equipment VLF and/or brand new auto grows remain you need to cycle to get the currently active VLF to the top of the list.
Code: (varies) Run on demand t-log backups or CHECKPOINT

Return to Step 2 and repeat, until all the old growth VLFs are gone.

Why? Depending on multiple variables, it may take 3 or 4 runs through to get all the old grows off.

First Grow

Why? Create your first set of planned VLFs

.

USE [master]
GO
ALTER DATABASE [MyDB] MODIFY FILE ( NAME = N'MyDB_log', SIZE = 4000MB )
GO

See how many VLF you have now

Why? See if you got what you expected

.

USE [MyDB]
GO
DBCC LOGINFO

Second Grow

Why? If you decided, you need more, repeat as needed

.

USE [master]
GO
ALTER DATABASE [MyDB] MODIFY FILE ( NAME = N'MyDB_log', SIZE = 4000MB )
GO

Set a reasonable Auto Grow

Why? If everything goes perfect, and you have taken everything into account it will never auto grow.  You need to decide what is going make the best sense on your system.

.

USE [master]
GO
ALTER DATABASE [MyDB] MODIFY FILE ( NAME = N'MyDB_log', FILEGROWTH = 250MB )

SQL 2014+

Important Reminder: on SQL 2014 and newer “If the growth is less than 1/8 of the current log file (ldf) creates 1 VLF of the growth size” Example an 8GB Log file growing 1000MB is not going to create 8 new VLF at 125MB, it is will create 1 VLF at 1000MB, if it grows 1100MB it will create 16 new VLF at 68.75MB, Huge differences here (where 1GB = 1024MB).

Log File (ldf) greater than 25GB

Optimal configuration is often described as not more than 50 VLF at not more than 500MB each.  Three grows of 8GB, give us about 48 VLF with an allocated space of 24GB.  What happens if your log file needs to be 50 or 100GB? Do you increase VLF size or count?  This document does not attempt to address those scenarios, I have found a few suggestions of allowing VLF to be up to 1GB, but when you get over the 24 or 25GB allocated file size there are just to many variables to offer a generic path to solution.

References:

https://www.sqlskills.com/blogs/paul/multiple-log-files-and-why-theyre-bad/
http://sqlblog.com/blogs/kalen_delaney/archive/2009/12/21/exploring-the-transaction-log-structure.aspx
https://technet.microsoft.com/en-us/library/ms190411(v=sql.105).aspx
https://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
https://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/
https://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/
https://www.brentozar.com/archive/2015/05/how-to-see-recent-autogrowth-events-on-databases-sql-server/


Linked Server - login trying to access database that is not default Trying to create a linked server from server A (2005) to B (2008 R2). Everything worked when created. The login on server B was mapped to two databases: DataBase1 and Database2. Database1 was the default.

Database2 was taken off-line. When opening the User Mapping, there is a message, "One or more databases are inaccessible and will not be displayed in list."  I assume Database2 is the culprit. I brought Database2 online and unchecked it.

When testing the connection on the Linked Server, it fails because it can't access Database2. Again, Database1 is the default.

I rebuilt the login using the exact same script with no mention of Database2, still get the error.

Because I need a different Linked Server name that does not match the actual SQL Server name, let's say I want to link to server "A" but call the link "X"

EXEC master.dbo.sp_addlinkedserver @server = N'X'
, @srvproduct=N'Microsoft OLE DB Provider for ODBC'
, @provider=N'MSDASQL', @datasrc=N'X'
, @provstr=N'DRIVER={SQL Server};SERVER=B;UID=test;PWD=*******;'

I can go into SSMS and create a linked server through the GUI. Picking SQL Server, which won't allow me to use a name for the linked server except the same name as the server and it works. This does not use the OLE DB Provider for ODBC.

EXEC master.dbo.sp_addlinkedserver @server = N'B', @srvproduct=N'SQL Server'

Can I script this so I can indicate the link name as X and the server or datasrc?

I would also change the parameters as follows:

@srvproduct = 'sql_server'
@provider = 'SQLNCLI' if you're coming from 2005 (and 'SQLNCLI10' from 2008/8R2, and 'SQLNCLI11' from 2012, etc.)
@provstr can be removed entirely; it's not necessary with this product and provider; your linked server logins will handle that.

Whatever SQL Server login is being used by the linked server probably has an offline/removed default database.  Change the default database for that login to one that exists and that the user has permissions to and try again.

This is a guess, as you didn't post the CREATE USER script that generated the particular username logging in.


Best Way to design a table with different types of attachment I am currently Building a table for message where user can send a message with different types of attachment,

Sender can:

send text per message
send one location per message
send one or multiple images per message
send one or multiple videos per message

Currently, this is my table design,

message
    -id
    -type (text,images,videos,location)
    -sender_id

message_text
    -id
    -message_id
    -body

message_location
    -id
    -message_id
    -latitude
    -longitude

message_images
    -id
    -message_id
    -image_path
    -image_height
    -image_width

message_videos
    -id
    -message_id
    -video_path

Am I doing things right?

My concern was user can attach multiple of images or videos but only one location.

You are probably over-complicating matters by using separate tables for message, message_text and location. Just merge them into one table:

message
-id
-type
-sender_id
-body
-latitude
-longitude

If the columns may not have values, be sure to set them to NULLable in the table definition.

There are various valid reasons for using one to one relationships, for example for example if you expect to have a lot of records with shorter fields in the primary table but then only occasionally need records with longer fields in the secondary table but in most cases they are not necessary and only serve to complicate your design which will require more joins it otherwise would not.

From the general tone of your question you seem to be new to database design and I doubt you really want to do this. Keep everything about the message in one table and then you can inherently only have one location, one body, etc.

IF you wanted to create a one-to-one relationship the usual way you would go about this would be to make the primary key on the secondary table also a foreign key referencing the primary table. So both tables would have the same primary key value for a given record- this would ensure you could only have one.

https://www.mkyong.com/mysql/how-to-define-one-to-one-relationship-in-mysql/

As it is now, you could have multiple records in these tables, a one-to-many relationship.

But I would strongly suggest you just merge into one table.

Your attachment and image tables look fine, that will be a one-to-many relationship with separate primary keys and linked using a foreign key.


Conditional Where Statement Involving a NULL value? Trying to find a way to pass off a third value within a 'bit' field. I would like to be able to utilize NULL as a searchable value. As in 'True', 'False', or 'NULL', the only issue is I'm unsure how to go about it. My initial thoughts were some kind of 'if' branch within the statement, but it doesn't appear to allow this kind of syntax. Here is my current WHERE clause. I am trying to make it possible to have [ERROR_FREE] registered as '0', '1' or 'NULL' based on a variable passed from my C# application.

WHERE [TBL_OUTBOUND_REVIEW].[ACTIVE_DIRECTORY] LIKE @UserName AND
      CONVERT(date, [TBL_OUTBOUND_REVIEW].[DATE_SUBMITTED]) LIKE @DateSubmitted AND
      [TBL_OUTBOUND_REVIEW].[ERROR_FREE] = @Status AND
      [TBL_OUTBOUND_REVIEW].[LOCATION] LIKE @Location AND
      [TBL_OUTBOUND].[UDF_0] LIKE @UDF0
ORDER BY [TBL_OUTBOUND_REVIEW].[DATE_SUBMITTED] DESC;

More or less I'd like to accomplish this :

WHERE [TBL_OUTBOUND_REVIEW].[ACTIVE_DIRECTORY] LIKE @UserName AND
      CONVERT(date, [TBL_OUTBOUND_REVIEW].[DATE_SUBMITTED]) LIKE @DateSubmitted AND
              IF @Status IS NULL
               [TBL_OUTBOUND_REVIEW].[ERROR_FREE] IS NULL AND
              ELSE
               [TBL_OUTBOUND_REVIEW].[ERROR_FREE] = @Status AND
      [TBL_OUTBOUND_REVIEW].[LOCATION] LIKE @Location AND
      [TBL_OUTBOUND].[UDF_0] LIKE @UDF0
ORDER BY [TBL_OUTBOUND_REVIEW].[DATE_SUBMITTED] DESC;

... Though I know that doesn't work. :\

Use an OR to check if either the parameter matches the value, or both the parameter and the value are NULL, like this:

WHERE
(
    [TBL_OUTBOUND_REVIEW].[ERROR_FREE] = @Status
    OR
    ([TBL_OUTBOUND_REVIEW].[ERROR_FREE] IS NULL AND @Status IS NULL)
)

You could also use a CASE statement as your IF if you really wanted to, but I think it's easier to read and understand the OR syntax above, so would prefer to use that.

WHERE
(
    CASE WHEN @Status IS NULL AND [TBL_OUTBOUND_REVIEW].[ERROR_FREE] IS NULL THEN 1
       WHEN [TBL_OUTBOUND_REVIEW].[ERROR_FREE] = @Status THEN 1
       ELSE 0 END
) = 1