Generating maintenance database from maintenance tables currently in master and msdb I want to create a maintenance database that i can clean up master and msdb, i am trying to collect all the table info from master and msdb that are not Microsoft tables, bearing in mind that some instances might have different structures due to different requirements. I am getting an error on the code

  Msg 102, Level 15, State 1, Line 27
  Incorrect syntax near '@ServerName'

is there maybe a simpler way of doing this?

DECLARE servers_cursor CURSOR
    FOR
    -- select * from environment
    -- select * from DatabaseInfo
        select  distinct LinkedserverName,Environment,ServerUse
        from SQLMonitor.dbo.Environment
        join master..sysservers on srvname COLLATE DATABASE_DEFAULT = LinkedserverName COLLATE DATABASE_DEFAULT
        where ServerUse = 'DEV'
        --and IncludeBackupsStats = 1
        order by LinkedServerName

    OPEN servers_cursor
    DECLARE @Servername varchar(255)
    DECLARE @sql varchar(8000)
    DECLARE @Environment char(4)
    DECLARE @ServerUse varchar(15)

    FETCH NEXT FROM servers_cursor INTO @ServerName,@Environment,@ServerUse
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        PRINT @ServerName
        CREATE TABLE [_TempDetails_]
        (DatabaseName sysname
        ,TableName sysname
        ,ActualCommand nvarchar(max))
        INSERT INTO [TempTableScripts].dbo._TempDetails_
        SELECT * FROM OPENQUERY(@ServerName, '
    declare @TableNames table
    (
        DatabaseName sysname
        , TableName sysname
    )
        insert @TableNames
        EXEC sp_msforeachdb @command1 = ''use ?;
        IF ''''?'''' IN (''''master'''',''''msdb'''')
        BEGIN
        SELECT ''''?'''', name from sys.tables T WHERE is_ms_shipped = 0
        END'+'

    select *, ''''SELECT TOP 1 * INTO ['''' + @ServerName + ''''_-_'''' + Databasename +''''_-_'''' + TableName +''''] FROM ['''' + @ServerName+''''].[''''+ Databasename + ''''].dbo.['''' + TableName + '''']''''
    from @TableNames
    ')
            --exec (@sql)
      FETCH NEXT FROM servers_cursor INTO @ServerName,@Environment,@ServerUse
      --PRINT @ServerName
    END
    CLOSE servers_cursor
    DEALLOCATE servers_cursor

The following code will inspect the master and msdb databases for all linked-servers on the current instance, reporting non-Microsoft-shipped tables:

IF OBJECT_ID(N'tempdb..#obj_list', N'U') IS NOT NULL
DROP TABLE #obj_list;
CREATE TABLE #obj_list
(
    ServerName sysname NOT NULL
    , TableName sysname NOT NULL
);
DECLARE @server sysname;
DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT s.name
FROM sys.servers s
WHERE s.name <> @@SERVERNAME;

OPEN cur;
FETCH NEXT FROM cur INTO @server;
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = '
SELECT ServerName = ''' + QUOTENAME(@server) + N'''
    , TableName = N''master.'' + s.name + N''.'' + t.name
FROM ' + QUOTENAME(@server) + N'.master.sys.tables t
    INNER JOIN ' + QUOTENAME(@server) + N'.master.sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0
UNION ALL
SELECT ServerName = ''' + QUOTENAME(@server) + N'''
    , N''msdb.'' + s.name + N''.'' + t.name
FROM ' + QUOTENAME(@server) + N'.msdb.sys.tables t
    INNER JOIN ' + QUOTENAME(@server) + N'.msdb.sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0
ORDER BY TableName;';

    INSERT INTO #obj_list (ServerName, TableName)
    EXEC sys.sp_executesql @cmd;
    FETCH NEXT FROM cur INTO @server;
END
CLOSE cur;
DEALLOCATE cur;

SELECT *
FROM #obj_list ol
ORDER BY ol.ServerName
    , ol.TableName;

Your code has two problems;

You cannot use a variable in OPENQUERY like this:

SELECT * FROM OPENQUERY(@ServerName, '

That would need to be:

SELECT * FROM OPENQUERY([Server\Instance], '

Clearly, that won't work for your case since you're trying to do this dynamically.  To make that work, you'd need to do dynamic SQL inside dynamic SQL.
This line contains an embedded reference to the @ServerName variable, but that variable is not visible inside the dynamic T-SQL... That line would need to be:

select *, ''''SELECT TOP 1 * INTO [''''' + @ServerName + '''''_-_'''' + Databasename +''''_-_'''' + TableName +''''] FROM [''''' + @ServerName + '''''].[''''+ Databasename + ''''].dbo.['''' + TableName + '''']''''


How do I run a batch file from a scheduled Job via SQL Server Agent I'm attempting to run a (Windows) batch file from within a SQL Server agent job under SQL Server 2008 R2. In the Job Steps, I've created a new step, set its type to "Operating system (CmdExec)". I initially tried to put just the batch file name in the "Command" field. It gives an error that it can't find the file.

Then I tried: cmd.exe c:\test.bat

This starts, but never completes (and it doesn't look like it's actually working anyway).

I tried wrapping the batch file name in quotes: cmd.exe "C:\test.bat" but that also doesn't make any difference.

What is the magical combination to get this to work?

If memory serves me correctly:

cmd.exe /c "c:\test.bat"

Quotes are needed if there are any spaces in the path or file name


Add query executed user information to Mysql binlog We are using MYSQL as our production database and we have setup another salve for read requests. Time to time various database users can execute insert/update queries on master database. If we want identify those executed queries (auditing/troubleshooting) we are using mysql bingos for it (reading after decoding it). From mysql binlog we can identify the query but we cant identify the executed user. is their a way to get that query executed user info into binlog. Enable general log  wont be a option due to performance issues.

Not sure about general log, but this behavior belongs to auditing system.  If you don't insist to write into binlog, both Oracle and Percona offer solutions:

https://dev.mysql.com/doc/refman/5.6/en/audit-log.html
https://www.percona.com/doc/percona-server/5.6/management/audit_log_plugin.html


How to create a database with "polymorphism" I'm sorry I'm new when it comes to databases, I'm using Access right now to develop a database to store calibration data for a bunch of different types of transducers.  Each type of item is fundamentally different from the other so I'm planning to make a table for each item type, but across the board they all must have a unique ID number (so it can be related to one or many calibrations).  Here is an example in rusty SQL, hopefully it makes sense.

create table accelerometer (GAC_id, name, range, serial)
create table loadcell (GAC_id, name, description)
create table potentiometer (GAC_id, product_id, tag_id)

As you can tell the 3 tables have different columns but across the 3 tables there MUST be a UNIQUE GAC_id number, something that no matter what table there is only one GAC_id in the entire database.

One idea is if I were to create a 'master table' that had a GAC_id and a part type then would have a relationship to the appropriate sub-category part id but I don't think you can create a 'variable' relationship, can you? Following the example above...

create table Part (GAC_ID, part_type, accelerometer_ID/loadcell_ID/potentiometer_ID)

... where it chooses the third column based on the value in the second column

Anyway really need help here, thanks

Your best bet is likely going to be to create a single child table that references all three of the other tables onto a single GAC_id. So you'd end up with something more like this:

create table gac (
    GAC_id,
    accelerometer_id,
    loadcell_id,
    potentiometer_id,
    primary key (GAC_id),
    foreign key (accelerometer_id) refrences (accelerometer.accelerometer_id)
    foreign key (loadcell_id) references (loadcell.loadcell_id)
    foreign key (potentiometer_id) references (potentiometer.potentiometer_id)
)

Here, each foreign key should reference the primary from the appropriate table:

create table accelerometer (
    accelerometer_id,
    name,
    range,
    serial,
    primary key (accelerometer_id))
create table loadcell (
    loadcell_id,
    name,
    description,
    primary key (loadcell_id))
create table potentiometer (
    potentiometer_id,
    product_id,
    tag_id,
    primary key (potentiometer_id))

This will work from a relational standpoint if I understand what you are looking for correctly. Then you just make sure that all items in gac have a unique index associated with them, they cannot be null, and that each entry has to have a corresponding entry in the appropriate parent table. For Access specific SQL, use the MSDN Access SQL Reference to see Access specific queries to perform each task.

From a design standpoint, it would be a horrible idea to have three different tables with identical primary keys (GAC_ids in this case). Theoretically you could do it, but there are all sorts of issues that it can and will cause from a practical standpoint.

Theoretically you could also do something like what you had originally:

create table accelerometer (GAC_id, name, range, serial, primary key(GAC_id, <insert one of the other fields here>))
create table loadcell (GAC_id, name, description, primary key(GAC_id, <insert one of the other fields here>))
create table potentiometer (GAC_id, product_id, tag_id, primary key(GAC_id, <insert one of the other fields here>))

Then you could just create a view that joins them all on the GAC_id. Of course, this will only work if the GAC_ids correlate one-to-one:

create view my_view as select * from accelerometer a
    full outter join loadcell lc ON a.GAC_id = lc.GAC_id
    full outter join potentiometer p ON a.GAC_id = p.GAC_id

That would create a view that accomplishes the same task as the child table in the first explanation i gave, but it has the advantage of auto updating whenever an item is added to any table. the only catch is that if there is no corresponding entry in any of the other tables, you will see null values where those entries should be.

look into the MSDN Access SQL Reference for the specifics on how to do the different things i've mentioned, in Access SQL.

One thing that is worth mentioning is that Access SQL may not support full outter joins. I know that is a quirk with MySQL, but i am not familiar with Access SQL, so you will have to use the reference, and probably this website(dba.stackexchange.com) and google to see exactly how you can/should build your create queries in Access SQL.


How to save an interval of years in postgresql I have to save an interval of years, like:

id intervalYears
1  2014/2015
2  2015/2016
3  2016/2017

And so on. Those years are like accademic years, so the first year must be lesser then the second, but greater or equal then the actual year.

So, how to save this datatype? Should i separate the dates and add some costraints?

If possible, just store the first value

It doesn't look like you need to store two numbers at all.

If these are like tax years, i.e. "2014/2015 financial year", you only need to consistently store one part.

If you store 2014, your app knows it's the academic year beginning in 2014. So it can display "2014/2015 Academic Year" with no ambiguity.

If you really need a range

If you actually need ranges and your sample data doesn't express that, e.g. you might need to store 2012/2015, then two int2 columns would be a good choice. You'd use CHECK constraints to limit the range to a reasonable value for an academic year and to enforce that the lower bound is less than the higher bound, as @Neil has already showed.

Alternately you could use the new-ish and PostgreSQL-specific int4range type:

SELECT int4range(2014, 2015);

per the documentation on range types. This offers you lots more operators at the cost of being fiddlier to work with for basic queries and from client apps.

You can even make a DOMAIN over it to confine its range.

CREATE DOMAIN academicyearrange AS int4range
  CONSTRAINT must_be_sane_year
  CHECK ( lower(VALUE) BETWEEN 1900 AND 2100
      AND upper(VALUE) BETWEEN 1900 AND 2100 );

e.g.

regress=> SELECT '[1421,2247)'::academicyearrange;
ERROR:  value for domain academicyearrange violates check constraint "must_be_sane_year"

regress=> SELECT int4range(1421,2247)::academicyearrange;
ERROR:  value for domain academicyearrange violates check constraint "must_be_sane_year"

(Before range types I would've suggested a domain over a composite type, but int4range is better in every way than a composite type).

Most likely all this is unnecessary - if you don't need variable length ranges, i.e. it's always someyear/thenextyear, you should just store the first year.


With partitioned table, How can I use hints to group each partition separately Suppose I have the following table
 data(partitioned_key_index, some_dummy_measure)

Assume that partitions are of an equal large size. with Oracle 11g.

The end result should be like this

select partitioned_key_index, sum(some_dummy_measure)
  from data group by partitioned_key_index

Each partition will be grouped independently, the optimizer should be
clever enough to come up with a plan in which each partition will be aggregated then a simple 'union all' to get the desired output.

What I want to do is something close to this

 select 1 as partitioned_key_index, sum(some_dummy_measure)
  from data where partitioned_key_index = 1
 Union All
 select 2 as partitioned_key_index, sum(some_dummy_measure)
  from data where partitioned_key_index = 2
 Union All
  .
  .
  .
 select i as partitioned_key_index, sum(some_dummy_measure)
  from data where partitioned_key_index = i

My intuition with the above method is to to serialize the hash group operation
thus each partition will be moved from the disk to the buffer cache with the hope of not spilling into disk for the group by operation.

Any ideas how to tune this kind of queries?

The database does this by default, no hints/tuning needed.

create table data(partitioned_key_index number, some_dummy_measure number)
  partition by list (partitioned_key_index)
(
     partition p1 values (1),
     partition p2 values (2),
     partition p3 values (3),
     partition p4 values (4),
     partition p5 values (5)
);

insert into data with g as (select * from dual connect by level <= 1000)
select mod(rownum, 5) + 1, rownum from g,g where rownum <= 500000;
commit;

Then run the query:

alter session set statistics_level=all;

select partitioned_key_index, sum(some_dummy_measure) from data
  group by partitioned_key_index;

PARTITIONED_KEY_INDEX SUM(SOME_DUMMY_MEASURE)
--------------------- -----------------------
                    1             25000250000
                    2             24999850000
                    3             24999950000
                    4             25000050000
                    5             25000150000

Check what happened:

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID  537qapda9hdy4, child number 0
-------------------------------------
select partitioned_key_index, sum(some_dummy_measure) from data group
by partitioned_key_index

Plan hash value: 3405952922

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      5 |00:00:00.14 |    1065 |       |       |          |
|   1 |  PARTITION LIST ALL |      |      1 |    585K|      5 |00:00:00.14 |    1065 |       |       |          |
|   2 |   HASH GROUP BY     |      |      5 |    585K|      5 |00:00:00.14 |    1065 |    34M|  6473K|  738K (0)|
|   3 |    TABLE ACCESS FULL| DATA |      5 |    585K|    500K|00:00:00.06 |    1065 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Operations below PARTITION LIST ALL were done for all partitions. We have 5 partitions, including the GROUP BY. As you can see from the Starts column, HASH GROUP BY was really performed 5 times.

This is how it looks like, when GROUP BY is performed for the whole table at once:

select (partitioned_key_index + 0), sum(some_dummy_measure) from data
    group by (partitioned_key_index + 0);

(PARTITIONED_KEY_INDEX+0) SUM(SOME_DUMMY_MEASURE)
------------------------- -----------------------
                        1             25000250000
                        2             24999850000
                        5             25000150000
                        4             25000050000
                        3             24999950000

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2xxf42mtp53sc, child number 0
-------------------------------------
select (partitioned_key_index + 0), sum(some_dummy_measure) from data
group by (partitioned_key_index + 0)

Plan hash value: 3651737839

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      5 |00:00:00.24 |    1065 |       |       |          |
|   1 |  HASH GROUP BY      |      |      1 |    585K|      5 |00:00:00.24 |    1065 |    34M|  6473K| 4574K (0)|
|   2 |   PARTITION LIST ALL|      |      1 |    585K|    500K|00:00:00.14 |    1065 |       |       |          |
|   3 |    TABLE ACCESS FULL| DATA |      5 |    585K|    500K|00:00:00.06 |    1065 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

20 rows selected.

HASH GROUP BY was performend only once, for the whole amount of data, after it was collected from all partitions.


Deadlock between two databases I have my code running online shared hosting provider. Recently I am  seeing too many deadlock exception is being thrown. As you know that shared hosting uses the same database server to host all the databases, I am thinking does other database is running in same server blocking the resource that required for my database query to run. It sounds like stupid. But I have little knowledge on this matter. We are using MS-SQL server2005 and application written in .net framework 2.0. I am not sure about the Windows Server running.

I hope I had enough information here. Please let me know if you need more information.

Does deadlock occurs between two or more databases?

If you are only accessing your database, it is highly unlikely that deadlocks you are experiencing are caused by other customers that happen to have their database on the same server - they'd have to be intentionally crossing database boundaries with transactions, and they'd most certainly not have the ability to do that. However we need a lot more information about what kind of deadlocks you are seeing, as they could involve system objects, etc. Are the objects involved only in your database? Have you looked at the deadlock graphs at all, or are you just responding to "deadlock victim" messages? Lots of info in this StackOverflow question about interpreting deadlocks, but you need to capture the graph using Profiler or some kind of monitoring tool (for which you may need to ask the host's assistance).


Joining from database A to view in database B; where is processing done? If I join tables from database A to a view in database B (same server), are the joins within the view in database B processed on database B before joining to the tables in the current database?

Views are inlined into the query plan. Optimization then proceeds not even knowing that there once was a view.

The execution algorithm in general is not directly dictated by the way the query is written.

The join order in particular is very indirectly related to the way you wrote the joins.

So the answer depends on your particular schema, data and query. Likely, the answer is that the way you write the joins has no or has little impact.

You can find out by comparing the actual execution plans.


Error: 18456, Severity: 14, State: 11. Login-based server access validation failed with an infrastructure error We are getting the above error on a SQL 2012 server running Windows Server 2008R2 when connecting using an application and a service account. The server also has Config Manager database and GAP policy applied

The login will work if the user is sysadmin (but the application won't)

Of course we have looked at Aaron Bertrand's post on 18456 errors, which states:

  States 11 & 12 mean that SQL Server was able to authenticate you, but weren't able to validate with the underlying Windows permissions. It could be that the Windows login has no profile or that permissions could not be checked due to UAC. Try running SSMS as administrator and/or disabling UAC. Another reason could be that the domain controller could not be reached. You may need to resort to re-creating the login (see this post from Simon Sabin).

and also http://blog.sqlauthority.com/2015/08/19/sql-server-login-failed-for-user-reason-token-based-server-access-validation-failed-with-an-infrastructure-error/

So we have ensured that we have a profile on the servers by logging on with the service account.
We have run the application as administrator
We have confirmed connectivity to the DC
We have removed and re-created the login
We have confirmed there are no DENYs for the user or group

We have confirmed that the user and also the public role has connect permissions to the ConfigMgrEndpoint as well as SQL

What have we missed?
What else do we need to do to resolve this issue?

Check the new endpoint name on sys.endpoints table and grant connect permission for the user. The new endpoint name should be different from "TSQL Default TCP".

Use this script for windows login:

GRANT CONNECT ON ENDPOINT::"NEW_ENDPOINT_NAME" TO [DOMAIN\firstname.lastname]

Or use this for sql authentication login:

GRANT CONNECT ON ENDPOINT::"NEW_ENDPOINT_NAME" TO "username"


SQL column name search I'm using SQL Server Management Studio. I have a massive database and I want to search for column names that might be able to help me figure out what I need to join on.

Is there a tool or other method for doing that kind of a search? I have been looking for a way to do a column search but can't find one.

You have two options. Option 1 is to use the system views:

select
    t.name
    ,c.column_id
    ,c.name
    ,st.name
from
    sys.tables t
    join sys.columns c on (t.object_id = c.object_id)
    join sys.types st on (c.system_type_id = st.system_type_id)
where
    c.name = ''

Option 2 is a free add on from Red Gate Software called SQL Search.


If i index two columns (indepentently) do i get the same performance as putting and index on the two I want to put an index on two columns to improve my query performance for each. I also want to perform queries where both columns are used.
From my understanding, if you had a BTREE index on each column, you could theoretically get the same performance by querying both indices and then combining the range you got - if that makes sense?

e.g.
SELECT * FROM table WHERE f_name = 'first' and l_name = 'last'

Instead checking an index for ('first', 'last') you'd check the f_name index for first and the l_name index for last. Now, you can check if the results of both index queries are the same (the result of the initial query). Obviously, this is slightly slower than a composite index, but still much faster than no index.
With this idea, you get performance benefits not only when querying both columns together, but either column independently.
My main question is: if I have these 2 independent indices, would I need another index on both, to improve the performance of queries using both columns?

Your question is a bit jumbled.  No, you can't get theoretically the same performance, unless one of the two columns is unique or nearly unique.  But yes, you can get performance that while slower than the composite is still much faster than no index at all.  Will it be fast enough that you don't need the composite index?  No one can answer that but you.  We don't know either how fast you need it to be, nor how selective the condition against each column will be (both independently and jointly).
But if you are going to have indexes on (a) and on (b), you might as well just change the first one to be on (a,b) or change the second to be on (b,a).  The extra cost will be nearly negligible (unless the added column contains wide data values) while the extra value at least has the potential to be large.  The cost might be slightly higher in v13, because having the composite index might defeat the new deduplication logic compared to the one with just the prefix column.
Doing a detailed analysis of whether it is worthwhile is probably not worthwhile.


High Availability SQL Server We currently have a asp.net application running on a Win 2012 dedicated server, with a local copy of SQL 2014 Express.

We are looking to add a second web server and have them in a NLB cluster. But what to do with SQL... The website relies heavily on the SQL databases (read & writes)

I am vaguely familiar with the forms of SQL replication, availability groups, log shipping, etc and have read through quite a few posts on StackExchange regarding the same topic, but I still have a few questions.

The site has only moderate traffic, but HA and RPO is still very important. I would rather have some downtime, vs loose 1 row of SQL data, although the whole point in migrating to this setup is to decrease the chance of downtime.

A solution that will work with SQL Express would be ideal, SQL Standard is fine, but I dont want to have to upgrade to SQL Enterprise.

A) What options do I have/suggestions do you have regarding keeping SQL on the IIS servers. I would rather span SQL over the 2+ web servers then have to add additional servers just for SQL.
B) Are there any new features coming out in SQL 2016 that will help with this?

Although I know this is not exactly a DBA question, but I'll ask anyway. Is there a way to force a asp.net to write to all available SQL databases at the same time, but can read from any of them?
(Kinda like Windows NLB uses the same IP for all servers, posting a update/insert to the SQL IP would write it to all of them, but a query would simply use the result from the first server that answers?

Thank you all!

A solution that will work with SQL Express would be ideal, SQL
  Standard is fine, but I dont want to have to upgrade to SQL
  Enterprise.

If you want something straight out of the box, that's going to be difficult to find. However, 2016 does bring Standard Edition Availability Groups. Standard Edition before 2016 does have Mirroring. Additionally if you're using asp.net and using the session state databases, there may be additional considerations (like not mirroring or putting it into an AG as the data is volatile and not worth/needed kept).

  A) What options do I have/suggestions do you have regarding keeping
  SQL on the IIS servers. I would rather span SQL over the 2+ web
  servers then have to add additional servers just for SQL.

The best practice for scale would be to put SQL on its' own server. If that's too pricey, it would be possible to put it on the same server as your web services... this brings up a few risks (security, performance, uptime).

  B) Are there any new features coming out in SQL 2016 that will help
  with this?

Yes and No. Yes, Basic Availability Groups are available but I honestly don't believe it's the right fit for your situation. I actually don't believe Availability Groups in general is a good fit for what you've described.

  Although I know this is not exactly a DBA question, but I'll ask
  anyway. Is there a way to force a asp.net to write to all available
  SQL databases at the same time, but can read from any of them?

Unfortunately there is nothing inside of SQL Server out of the box except for Replication. In this case you could have Peer-To-Peer, but again that's an Enterprise Edition feature so we have to throw that away. It would be possible with Merge Replication but to be honest that's a pretty terrible beast I wouldn't wish on anyone.

Taking into account your given resources and in house abilities (not meant to be rude) I would say these are the items to investigate further to see which fits your situation the best (as you're the best person to make that decision as you know the environment).

1. Hosting in Azure

I want to be completely transparent, I work for Microsoft. Having said that, it could be the most cost effective and RPO/RTO effective to transition the application to Azure.

Why? There are a few key resource constraints in your current environment that could make it rough to achieve the uptime you're looking for balanced with cost and people resources. In Azure, there can be multiple copies of the database and you can spin up/spin down (elasticity) web hosts. This gives you the RPO/RTO you would like without having to know any of the SQL Server parts and configure it through a clickable GUI. This can be scaled up/down per cost control and usage.

The biggest win is you get the RPO/RTO and don't have to buy hardware or software.

2. SQL Server Mirroring

Mirroring doesn't require a Windows Cluster or a host of other pre-requisites like Availability Groups do. It is generally much easier to setup and is straight forward. It does require at least Standard Edition and there would most likely be licensing costs per instance of SQL Server, depending on a few things.

Mirroring allows for single database failover and a witness server can be used with SQL Server Express Edition. Additionally .NET applications have the ability to set a failover partner server in the connection string to help facilitate a more transparent failover scenario. Mirroring does not require a listener or client access point like Availability Groups do, thus it's very easy to use an IP load balancing solution to do health checks and automatically point to the proper host. This does not allow for multi-master scenarios, there will still only be a single "master" server which will then update the single partner server.

This is much less flexible of a design, but meets the RPO/RTO targets and requires the least amount of administrative upkeep (other than Azure). It is a deprecated (read, removed) technology but should get you through until 2014 is out of mainstream support and there may be other solutions.

3. SQL Server Clustered Instances

This should meet the RPO/RTO requirements as well as be one of the easier implementations if you're already familiar with Windows Clustering. If you are not familiar with it, then I'd probably stay away from SQL FCIs or have a contractor come in and set it up (if you choose this route).

FCIs only hold one copy of the database and do requires shared storage to implement. There are also the Windows Clustering requirements. It does, however, provide transparent failover and can be used with Standard Edition. There needs to be at least two servers to provide high availability.

4. Log Shipping

This is not a good fit but does most of what you'd like, doesn't deliver the RPO/RTO but does meet the cost requirements. It's also extremely easy to setup and does require at least Standard Edition.

This basically works by taking log backups through SQL Server agent, copying them to a location that all instances can see, and then the secondary servers check the location periodically and restore the latest log backups. Everything after that is completely manual. Though, again, it could be used with a CNAME or IP load balancer to make things a little more seamless. It would still require a ton of administrative work to do a failover and then setup reverse log shipping to keep all the other secondary servers in sync.

Aaron Bertrand has a good post of doing this on a budget while having "readable secondary servers"


Pages to MB, multiple and divide or just divide? When you are using things like sys.database_files it gives size of the file, in 8-KB pages, while many other things you are comparing to it to are in MB.

There are a number of ways to convert the 8-KB pages to MB, several ways are offered in the answers to Query to report disk space allocation and used space

The two most common are

Multiple by 8 and divide by 1024 (128000 * 8 / 1024 = 100)
Divide by 128 (128000 / 128 = 100)

The second is simpler, requiring only a single operation. But both seem to give the same answer.

Is there a good reason to choose one method over the other?

It's simple math. Those common equations are the same (not seem to give the same answer).

Simpler notation is the only benefit of the second one.

The first one just exlpains from where is the number 128 (8KB per page divides by 1024KB for MB size).


SQL Server Distributed Availability Group Usage Pattern I have got a client with DAG setup on 2016 & 2017 servers. I understand that their usage is most likely for DR purposes. What I'm a little unclear on (and I can't find any info on it) is how they are connected to (if at all). With a DAG / AG setup, does the application layer still only connect to the underlying AG (and not the DAG itself)? I've tried to connect to the DAG via SSMS and cannot. Is this by design? Does anything actually connect to the DAG itself or is it just in place to manage the synchronization across AGs? Any info most appreciated guys (and girls) :)

Regards,

Glenn

The documentation for Distributed availability groups states:

  Although each underlying availability group of the distributed availability group has a listener, a distributed availability group has no listener.

So there is no way for a client to connect to a distributed AG because there is no listener for it.  Clients must connect to the listener of the AG that is currently the primary.

In order to fail over a DAG and provide connectivity for clients, you would need to initiate failover on the DAG, and then create a listener on the DR cluster with the same name as the current listener that clients connect to.

If you are using Enterprise edition, creating a DR node in the cluster and making it an asynchronous replica in the regular availability group is usually a better option, so if you haven't considered that I recommend looking into it.


Optimizer choose incorrect index for query according to rows number in mysql 5.6 I have mysql 5.6. The problematic query has the following format:

SELECT field_1, field_2
  FROM trace_ejb3
 WHERE field_1 IN (...) AND field_2 BETWEEN ... AND ...
 GROUP BY field_1;

Optimizer calculates the cost for each execution plans and eventually choosees incorrect index to use. From what I understand the main reason - number of returned rows.
However in fact I need to use another index. The difference in execution is 2 days vs 1 hours.

I have tried to play with the following options:
- innodb_stats_persistent + sample_pages
- optimizer_switch
- optimizer_search_depth
- optimizer_prune_level

However none of the about options influence the results : optimizer still choose execution plan according to the least number of returned rows.

Question: is there a way to force optimizer not take into account rows number?

P.S. I know about index hints, but I don`t consider it like a long-term solution.

In general, the only way to force the optimizer to take a specific path is to add code (like index hints, as you mention) to explicitly tell the optimizer what it should (or shouldn't) use.

Beyond that, one generally has to look at the specific query and query plan, the information the optimizer has, and the reason why it would have chosen the path it did. In most cases, the optimizer chooses a sub-optimal path because either its information is bad (missing or out-of-date statistics), or because of a cached plan that worked well when cached, but not well with the current parameters. Of course, sometimes the best path available is still bad.

In these cases, you can look at the query plan, see where the query was having the most problems, and figure out a way to give the optimizer better options. This can be creating a covering index; adding or updating statistics (assuming that can be done under MySQL); breaking the query into multiple steps on which the optimizer can make accurate estimates, etc.


Report Configuration changes I created a stored procedure that I got on Erin Stellato's course on Pluralsight to report if there are any configuration changes on the server as a nightly job as follows:

CREATEPROCEDURE dbo.usp_SysConfigReport1
(
    @RecentDate DATETIME,
    @OlderDate DATETIME
)

AS

BEGIN;
    IF
    @RecentDate IS NULL
    OR @OlderDate IS NULL
    BEGIN;
    RAISERROR('Input parameters cannot be NULL', 16, 1);
RETURN;
END;
SELECT
        @@SERVERNAME ServerName,
        CaptureDate,
        [O].[Name],
        [O].[Value] AS "OlderValue",
        [O].[ValueInUse] AS"OlderValueInUse",
        [R].[Value] AS "RecentValue",
        [R].[ValueInUse] AS "RecentValueInUse"

    FROM [dbo].[ConfigData] O

    JOIN

        (SELECT [ConfigurationID], [Value], [ValueInUse]

        FROM [dbo].[ConfigData]

        WHERE [CaptureDate] = @RecentDate) R on [O].[ConfigurationID] = [R].[ConfigurationID]

    WHERE [O].[CaptureDate] = @OlderDate

    AND (([R].[Value] <> [O].[Value]) OR ([R].[ValueInUse] <> [O].[ValueInUse]))

END

--------
--Capture the capture dates into #temp table1
SELECT  DISTINCT
[CaptureDate]
INTO #CapturedDates
FROM [dbo].[ConfigData]
ORDER BY [CaptureDate]

    --Create temp table2
CREATE TABLE #CapturedDates2 (
         ServerName NVARCHAR(20)
        ,[CaptureDate] DATETIMEOFFSET
        ,Name NVARCHAR(1000)
        ,[OlderValue] INT
        ,[OlderValueInUse] INT
        ,[RecentValue] INT
        ,RecentValueInUse INT
)

------Inserting into Temp2 gin=ving error message as below

DECLARE @MINCapturedDate DATETIME
DECLARE @MAXCapturedDate DATETIME

SELECT @MINCapturedDate =(SELECT CAST(MIN([CaptureDate]) AS DATETIME) FROM #CapturedDates)
SELECT @MAXCapturedDate =(SELECT CAST(MAX([CaptureDate]) AS DATETIME) FROM #CapturedDates)

INSERT INTO #CapturedDates2
EXEC SYSDBA.dbo.usp_SysConfigReport1 @MINCapturedDate ,@MAXCapturedDate

But it gives me the following error message:

  Msg 257, Level 16, State 3, Procedure usp_SysConfigReport1, Line 19
  Implicit conversion from data type sql_variant to int is not allowed.
  Use the CONVERT function to run this query.

EDIT: Include definition of ConfigData

CREATE TABLE [dbo].[ConfigData]
   ( [ConfigurationID] [INT] NOT NULL,
     [Name] [NVARCHAR](35) NOT NULL,
     [Value] [SQL_VARIANT] NULL,
     [ValueInUse] [SQL_VARIANT] NULL,
     [CaptureDate] [DATETIME] NULL ) ON [PRIMARY]

As you can see from the definition you provided for [dbo].[ConfigData], both Value and ValueInUse are defined as SQL_VARIANT data types.  The error message you are receiving is:

  Msg 257, Level 16, State 3, Procedure usp_SysConfigReport1, Line 19
  Implicit conversion from data type sql_variant to int is not allowed.
  Use the CONVERT function to run this query.

So Value and ValueInUse are SQL_VARIANT (as also are the derived values for OlderValue, OlderValueInUse, RecentValue, and RecentValueInUse) but you want to use them as INT.

The error message is saying that you need to change the SQL_VARIANT into INT by using the CONVERT function.  An example of selecting a SQL_VARIANT and turning it into an INT.

SELECT CONVERT (int, Value) FROM [dbo].[ConfigData]

The CONVERT changes the datatype (if possible) from SQL_VARIANT to INT.

Just make sure to CONVERT to the proper data type and all should be well.


Use a query's value in where statement I'm searching how to do this and can't find it.

Here's my query:

SELECT
    S_Product.Name AS "Name"
    SUM(S_Item.Quantity) AS "QtySold"
FROM s_product
INNER JOIN S_Item ON S_Product.ID = S_Item.Product_ID
WHERE "QtySold" > 500
--(SUM(S_Item.Quantity)) cannot be used in a WHERE statement since it's a group function
GROUP BY S_Product.Name

Though this doesn't work I think you get my idea, how could I use the `"QtySold" value in the WHERE statement?

The answer is to put any testing of aggregations in to a HAVING clause:

SELECT
    S_Product.Name AS "Name"
    SUM(S_Item.Quantity) AS "QtySold"
FROM s_product
INNER JOIN S_Item ON S_Product.ID = S_Item.Product_ID
GROUP BY S_Product.Name
HAVING SUM(S_Item.Quantity) > 500 ;


SQL Server: Should we use TCP or Named Pipes or use the default? When connecting to a SQL Server 2008 R2 from a .NET 4 client application on a different server in the same LAN, one can set three different network protocols:

TCP
Named Pipes
Don't set anything in the connection string and use the default

What is best practice? What to choose?

Additional information: Both TCP and Named Pipes are enabled both on the server and on the client. The application is using database mirroring. Client and server communicate over a fast LAN.

We are investigating this because we have rare and spurious connectivity and timeout problems. (But regardless of that I'd like to know the best practice).

There is an article on this subject on MSDN but it is very generic and vague. It does not advise or recommend anything useful.

I prefer TCP/IP over Named Pipes, even though in most situations there will be no noticeable difference. You can do this by adjusting the protocols supported by the instance in SQL Server Configuration Manager rather than hard-coding things in your connection string (this makes it easier to make changes or to troubleshoot).

Essentially the routing and other overhead involved with named pipes (unless your apps are on the same machine as SQL Server, in which case there is only a little extra overhead) make it the less efficient option, especially at scale, in a slower network environment (100MB or less), or if your workloads come in bursts.

If your apps are on the same box as SQL Server, you should also keep shared memory in mind - if you have applications on the SQL Server box directly communicating with SQL Server, this is going to be the most efficient option.

You can read about the performance advantages of TCP/IP in more detail.


Keep total transaction in invoice application I want to make an application called as Invoice Application with MySQL database. Now I have designed this database. Now I want to know is this database good for Invoice Application. I will use MySQL with Yii framework(php). I am really confused in calculation table. In calculation table I want to store every individual invoice's total tax amount, sub total and total. So is this daabase good for all that purpose or should I keep total in InvoiceItems table?

The first question is: why do you want to store these values?

Typically this is done for performance, so you only have to recalculate them when edits are made.  If that's the case, I would also suggest caching the line item values in ia_invoice_items, so you can SUM those to compute the values in ia_calculations.

Usually, I see the ia_calculations values in the ia_invoices table.  Having them in their own, smaller table would make updates a little faster, but...

Managing them with triggers (rather than app code) will also protect you from changes made via any app (including the mysql command line tool, etc.)

And some comments that you didn't ask for:

Don't use INT as your id in your large tables, e.g. ia_users.  Use UNSIGNED INT instead.
Don't use INT to store dates, e.g. ia_users.createtime.  Use DATE or DATETIME instead.
Don't use INT to store booleans, e.g. ia_users.superuser.  Use TINYINT instead.
Don't use INT as the primary key in your small tables.  SMALLINT may be a better choice.
I wouldn't leave the length and precision of your DECIMAL columns to chance.  Define them explictly as DECIMAL(5,2) (or whatever length you need).
ia_invoices.updated_by only allows you to track the most recent update.  I would suggest an audit system, where you can track multiple edits, each with their own user_id, datetime, and notes.
Why don't you have product_id in ia_invoice_items?
Don't use VARCHAR(45) for postal code.  Nobody in the world has a 45-character postal code.  Since all customer records should have a postal code, I like to use CHAR here.


At what point is a (disk-based) table removed from memory after data is read from it? Are (disk-based, so no Hekaton) tables persisted in memory for longer than the lifespan of the query that is reading data from them?

If so, what determines how long they stay in memory for?

Is there a way to manage how long they persist in memory for?

This is a complicated topic, much of which is covered in this Microsoft Docs article.

To answer a couple of points:

  Are (disk-based, so no Hekaton) tables persisted in memory for longer than the lifespan of the query that is reading data from them?

Sort of.  Entire tables aren't necessarily persisted in memory.  Individual pages from a table or index are stored in memory - it could be the entire table, or just a portion of it.

  If so, what determines how long they stay in memory for?

You'll have to reference the article I linked to, but lots of things can cause "memory pressure" which will lead to data pages being "evicted" from the cache.

Things like the amount of memory available to SQL Server, other data and index pages being pulled into memory, other SQL Server data caches needing memory, "external" pressure from the OS and other processes on the box, etc can all result in pages being kicked out of cache.

SQL Server uses a "least recently used" cache, so the least recently referenced pages will generally get kicked out first.

  Is there a way to manage how long they persist in memory for?

Only by making sure SQL Server has enough memory to keep the pages in the buffer pool, and making sure other processes don't put pressure on SQL Server.

One thing that can help prevent the OS from taking memory back from SQL Server is turning on the Lock Pages in Memory (LPIM) feature.

As a historical note, DBCC PINTABLE used to work for keeping a table in memory, however, it was deprecated as of SQL Server 2005 (and now does nothing at all).


Issue in installing SSIS MS-SQL2012 While installing the MS SQL Server 2012 Enterprise Edition, everything goes through fine but I'm not able to install Integration Services alone. I get the error as following:

  The following error has
  occurred:C:\SQLserver....\Binn\DTSSVCPERF.ini Click retry to retry
  the failed action.

I have tried re-installing many times, but still am facing the same issue.
The OS is Windows Server 2012.

The problem is apparently not related to just SQL 2012; it has been reported on older versions also.

One guess is that your copy of SQL Server is probably corrupt. You could try downloading from a different source and checking if the issue repeats. The new SQL Server download might have all the required files in good condition, and save you the headache of any issues down the line. Of course, you should uninstall everything before attempting a fresh install from a different source.

You mentioned that it was Enterprise Edition? Assuming that it's a licensed copy,  you can also call Microsoft Support and ask them for assistance.


Can I seed Percona XtraDB cluster from the existing backup? I have a running setup of Percona MySQL 5.7. I would like to try out Percona XtraDB cluster. The current data size is ~500GB. From what I've observed (seen in a couple of YouTube videos: https://www.youtube.com/watch?v=uUVdte3wB3I ), I reckon that the cluster members do the initial sync from the currently serving master.
However, in my case, syncing 500 GB of data would take ages, which I can't afford.
On the other hand, I have a backup of the current data, taken using percona-innobackupex. Can I use this backup to seed the cluster members (like I would feed the slave while doing master-slave replication) so that the initial sync takes less time?

Yes you can.

For do this first start XtraDB Cluster on current server.
Then create an incremental backup from current full backup with --galera-info switch.(It's very important to use --galera-info)
Install Percona server in second server
Prepare the backup files (both backups.Full and Incremental) on second server
Open xtrabackup_galera_info file in your full backup directory and copy uuid and seqno (for example e0d744f3-c88a-11e6-ad0d-6ed1e3c29e6d:0)
create grastate.dat file in full backup directory and add the following lines

version: 2.1

uuid:    e0d744f3-c88a-11e6-ad0d-6ed1e3c29e6d

seqno:   0

cert_index:
Back up is ready to be copied over to the MySQL data directory.
Stop mysql service
Delete all contents in /var/lib/mysql
copy-back prepare backup

innobackupex --copy-back /restore/full
chown mysql directory to mysql.mysql
Config galera variables in my.cnf
Start mysql

You can find more information in this topic:

http://severalnines.com/blog/how-avoid-sst-when-adding-new-node-galera-cluster


About Apex SQL Refactor and SSMS keyboard shortcuts being broken Hi DBA Stack Exchange!

Today I tried Apex SQL Refactor and right from the get go, once installed and integrated into my T-SQL dev tool of Choice (SSMS 18.3.1) I noticed that some Keyboard shortcuts stopped working.

One example (maybe there are more, but this one I’m pretty sure): After installing and using SQL Refactor for first time (Refactor needs to be used at least once for this to happen) the “Available Databases” functionality in SSMS (which can be invoked by CTRL+U) no longer works. What is worse, this is not a configurable shortcut (like most of the Keyboard shortcuts that can be configured on Tools -> Options -> Keyboard) so once it is broken, the only solution so far (to make it work again) is to uninstall SQL Refactor.

This query shortcut (“Available Databases”) is an extremely useful one that people use it over and over again on SQL Sessions, so breaking the shortcut is a major thing for me.

I’m running the latest available builds of both SSMS (18.3.1) and Apex SQL Refactor (2018.08.0364)

My question is: have you also experienced this? If so, how did you solve the issue of keyboard shortcuts being irreparably lost after Apex SQL Refactor?

If you need me to provide any additional info about my system or logs that could help you reproduce this issue, just let me know.

Thanks to all of you!

Just got feedback from the Apex support team.

Apparently this is a known issue and they are working on it. I asked them to contact me so I get notified when a workaround/patch gets released that addresses this problem.


High Disk I/0 Issues Our database has been giving this error occassionally...
"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
   at System.Data.SqlClient.SqlInternalConnection.OnError"

I have run perfmon with below results. How can I check what is causing such high disk I/O?
The Avg. Disk Queue Length counter (blue) is very high.

Thanks!

To the question you asked:

I wouldn't rely on disk queue alone. In fact I rarely even ever look at disk queue lengths unless I'm getting in deep with a problem. It is best to look at your disk's latency. Those are the Avg. Disk Sec/Read (or  /Write and /Transfer) counters. That tells you what your disk latency is from Windows' perspective. So the time that the request was taking after sent to the disk and brought back.. Disk Queuing nowadays doesn't tell you a lot because most IO subsystems are able to handle a disk queue depth and have multiple spindles doing work in your RAID group often. Finally - In this case - your disk queue length doesn't even look that bad. From here it looks like the max it was in the time of this screenshot (for the average length) was 1.377. That's nothing on most SQL Server systems. Look at your actual latency. Also I don't look at % Disk Time.. I look at the idle time instead.  That is a more reliable counter and you just have to do a little math to read it.. The more idle, the less activity.

To The General Question Behind Your Question

I'll ask this one by starting with a question - Why did you go right to your IO? There could be any number of things causing your slowdown. And to answer that exhaustively here is tough but a high level of some things to look at/consider:

Are you experiencing blocking? I would download SP_Whoisactive and have a look at that while you are getting these errors. Do you see blocking? Do you see the query behind the request(s) that are timing out? What is the duration?

Have you analyzed your SQL Server Wait Stats to see what your chief cause of waits are?
Do you know which query or queries are causing the timeouts? If so can you look at those and see if there is any room for tuning?

There could be many other things here. This could be on the connection or network. It could be blocking, it could be a need for index tuning and query tuning, it could be that you expect the queries to take longer than the default 30 second timeout, etc.

But I'd try and gather more data and then choose a path to go down. This is an old whitepaper but it is very useful to performance tuning by waits. While there will be new wait type Tom didn't mention in this paper, it still very much applies and will help you out.


ERD to Relational schema I'm new to DBM and I'm trying to learn it online. Honestly, I need some help with this. I don't know if I'm doing this right, and any help will be appreciated. This is my answer for i.

There should be a table for every box, ie entity type/class, and every diamond, ie relationship type/class. There should be a column for every oval, ie attribute/property. Each line from a diamond to a box is indicates a foreign key. Relationship PKs are composite and consist of FK columns to associated/participant/referenced entity types.


DB2 cookbook by Graeme Birchall? Not sure if this question is appropriate here but I believe that it is of some interest for the db2:ers of the site.

Once upon a time the DB2 Cookbook by Graeme Birchall could be downloaded from:

http://mysite.verizon.net/Graeme_Birchall

The account seems to be closed, does anyone know if these are hosted elsewhere by the author or by anyone else? I believe that the pdf's where a great contribution for the DB2 community so it is a pity to see them gone.

For 9.7 they can be downloaded from: db2hub.com/freebies Does the cookbook exist for later versions?

I wrote to the author, but I never received an answer. It seems the most recent version is for v9.7. The book can be obtained from db2hub or from the wayback machine. Anyway, I published all of them in my Google Drive:

7.2 https://drive.google.com/file/d/0B86nuTd5nMTKZzQtaTVTLUZ5ckE/edit?usp=docslist_api
8.2 https://drive.google.com/file/d/0B86nuTd5nMTKS3ItN25IX2djTGc/edit?usp=docslist_api
9.1 https://drive.google.com/file/d/0B86nuTd5nMTKNFR1SU9PZkN6MnM/edit?usp=docslist_api
9.5 https://drive.google.com/file/d/0B86nuTd5nMTKdzBkS01nUlZwYXc/edit?usp=docslist_api
9.7 https://drive.google.com/file/d/0B86nuTd5nMTKd190MFptUEtoYXc/edit?usp=docslist_api

For things like this, we, the DB2 community, should work on collaborative project with collaborative tools. Let's suppose Graeme Birchall does not longer work with DB2, no-one will continue this precious work.

I advise to all of you to create open things, like open documents, open source tools in public forges, etc. My personal work around db2 is published in GitHub, and I am convinced of creating free stuff.


Save multiple SQL jobs in single files I'm using SQL Server 2005 and I need to save every SQL agent job in single 'create in' *.sql files. The problem is that I got about 20 SQL Server with 15 (up to 51) SQL jobs.

How can I export these jobs easy in single files?

If I mark all jobs they will be saved in a single file. It would take to long to manually export every single job to a file.

Did someone have an idea?

There are two possible ways
1.  Automatically create scripts for all SQL Server Jobs

I. Open Object Details

II. Select All Jobs (Shift + Mouse Click) -> Right Click -> Script Job as


Querying JSONB in PostgreSQL I have a table, persons, which contains two columns, an id and a JSONB-based data column (this table has just been made for demonstrational purposes to play around with PostgreSQL's JSON support).

Now, supposed it contains two records:

1, { name: 'John', age: 30 }
2, { name: 'Jane', age: 20 }

Now, supposed I want to get the name of every person older than 25. What I have tried is:

select data->'name' as name from persons where data->'age' > 25

Unfortunately, this results in an error. I can solve it by using ->> instead of ->, but then comparisons don't work as expected any more, since not the numbers are compared, but their representations as strings:

select data->'name' as name from persons where data->>'age' > '25'

I then figured out that I can actually solve the issue by using -> and a cast to int:

select data->'name' as name from persons where cast(data->'age' as int) > 25

This works, but it's not that nice that I have to know the actual type (the type of age in the JSON document is number anyway, so why can't PostgreSQL figure out that by itself?).

I then figured out that if I manually convert to text using the :: syntax, everything works as expected, too - although we are now comparing strings again.

select data->'name' as name from persons where data->'age'::text > '25'

If I then try this with the name instead of the age, it doesn't work:

select data->'name' as name from persons where data->'name'::text > 'Jenny'

This results in an error:

  invalid input syntax for type json

Pretty obviously, I don't get something here. Unfortunately, it's quite hard to find any real-world examples of using JSON with PostgreSQL.

Any hints?

This does not work because it's trying to cast a jsonb value to integer.

select data->'name' as name from persons where cast(data->'age' as int) > 25

This would actually work:

SELECT data->'name' AS name FROM persons WHERE cast(data->>'age' AS int) > 25;

Or shorter:

SELECT data->'name' AS name FROM persons WHERE (data->>'age')::int > 25;

And this:

SELECT data->'name' AS name FROM persons WHERE data->>'name' > 'Jenny';

Seems like confusion with the two operators -> and ->> and operator precedence. The cast :: binds stronger than the json(b) operators.

Figure out type dynamically

This is the more interesting part of your question:

  the type of age in the JSON document is number anyway, so why can't PostgreSQL figure out that by itself?

SQL is a strictly typed language, it does not allow the same expression to evaluate to integer in one row and to text in the next. But since you are only interested in the boolean result of the test, you can get around this restriction with a CASE expression that forks depending on the result of jsonb_typeof():

SELECT data->'name'
FROM   persons
WHERE  CASE jsonb_typeof(data->'age')
        WHEN 'number'  THEN (data->>'age')::numeric > '25' -- treated as numeric
        WHEN 'string'  THEN data->>'age' > 'age_level_3'   -- treated as text
        WHEN 'boolean' THEN (data->>'age')::bool           -- use boolean directly (example)
        ELSE FALSE                                         -- remaining: array, object, null
       END;

An untyped string literal to the right of the > operator is coerced to the respective type of the value to the left automatically. If you put a typed value there, the type has to match or you have to cast it explicitly - unless there is adequate implicit cast registered in the system.

If you know that all numeric values are actually integer, you can also:

... (data->>'age')::int > 25 ...


SQL Server : keep track of all connections/disconnections I have a Microsoft SQL Server 2008r2 SP1.

I need to know which users connect to, and disconnect from, database.

Ideally, I would like a list of (datetime, username, connect/disconnect).

Can this be done using extended events? Or is there a better way?

Here is a rundown of some of the options I have used to audit logins and logouts. I haven't had the chance to use Extended Events for auditing purposes yet. Below, I quickly describe tracking login and logout events using SQL Server tracing and SQL Server Audits. You'll probably end up going with setting up background server tracing which is described briefly below. If you need me to go into further detail, don't hesitate to ask.
Default Trace
The default trace is one way to audit activity on your server without making any changes. A post on what you can get out of the default trace is provided here.
Server Trace
If you are just looking for Login/Logout information, you can also create your own server trace to audit logins and logouts using SQL Server profiler. Just create a new trace and then export it as a SQL Server Trace definition file.

Open up SQL Server Profiler
Create a new trace using the blank template
Go to the Events selection tab
Under Security Audit, check Audit Login and Audit Logout

Run your trace, stop it and then under File, you can Export your trace as a SQL Server trace definition file. You can then run that trace in the background and save it to a file which can later be imported into a SQL Server table.
A portion of an example server Trace created by profiler:
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 2, @on
exec sp_trace_setevent @TraceID, 14, 66, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
.....
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

Here is the output I got from just capturing those items:

It is possible to convert server traces into extended event sessions and that is described in detail here.
Server Audits
There are also server audits which allows you to track and monitor Database/Server level events. Auditing may only be available in the Enterprise and Datacenter editions. I can't seem to find a specific article from Books Online that says otherwise.
USE MASTER
GO

CREATE SERVER AUDIT audit_test
TO FILE (FILEPATH = 'D:\Audit')
GO

CREATE SERVER AUDIT SPECIFICATION audit_test_spec
FOR SERVER AUDIT [audit_test]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE=ON)
GO

You can then view server audit events in SSMS by going to Security -> Audits. Then using the context menu, you can select View Audit Logs to review activity.

For more information on audits, you will find additional information here:
CREATE SERVER AUDIT (Transact-SQL)
CREATE SERVER AUDIT SPECIFICATION (Transact-SQL)
CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)


Here's one approach:

SELECT  a.part,
        a.description AS "desc",
        a.job_numbers
        dn.delnote_number,
        a.qty_ordered,
        dn.qty_del
    FROM ( SELECT  part, description, jobitemid
                GROUP_CONCAT(job_number ORDER BY job_number desc) AS job_numbers,
                SUM(qty_ordered) as qty_ordered FROM  job
            where  type = 'part'
            GROUP BY  part
         ) AS a
    LEFT JOIN dn  USING(jobitemid)

OP says that this is the result:

part        desc     job_numbers    delnote_num     qty_ordered      qty_del

"part1"     "desc1"    "2,1"       "1"              "456"            "30"
"part3"     "desc3"    "2"         "4"              "6666"           "6000"
"part1"     "desc1"    "2,1"       "5"              "456"            "500"
"part2"     "desc2"    "2,1"       NULL             "1343"           NULL
"part4"     "desc4"    "3"         NULL             "1000"           NULL


"an agent job has not been created for this subscription" I have set up merge replication with a publisher on my SQL Server 2008 R2 box with the distributor on this box too; in Express I have created a subscriber but the subscriber won't run because of the error:

  an agent job has not been created for this subscription

How can I solve this?

I don't know how to get past the error (I'm not sure how you're trying to "run" the subscriber), but you obviously can't use Agent to distribute changes back to the publisher, because SQL Server Express does not include Agent.

This thread seems to imply you can schedule the task outside of SQL Server using replmerg.exe from the command line:

replmerg.exe
  -Publisher    [PUBLISHER_INSTANCE_NAME]
  -PublisherDB  [DATABASE_NAME]
  -Publication  [PUBLICATION_NAME]
  -Subscriber   [SERVER\SQLExpress]
  -SubscriberDB [DATABASE_NAME]
  -Distributor  [PUBLISHER_INSTANCE_NAME]
  -DistributorSecurityMode 1

You might also find this Stack Overflow question useful - one of the answers there shows how to do a similar thing with C# (and I didn't read any further but there might be other useful info too). This blog post might also be helpful, showing how to accomplish this with the same type of batch script, or using Windows Sync Manager, Sync Center or Replication Management Objects (RMO).


how to reinitialize log shipping? Here is the situation:

DB on Server A had the transaction log shipping configured and uses Server B as secondary. It has been working well for a long time.

However, we recently detected that one of the trn files got corrupted and Server B cannot restore it.

DB on Server A is still healthy, so I would like to create a new full backup into Server B and restart the transaction log shipping.

Since all the timings and other settings were already tweaked and configured, I am trying to avoid starting from scratch.

What step would i need to follow in order to reinitialize the log shipping? (This is a SQL Servert 2008 R2)

Disable the log shipping jobs on both servers.
Back up the database on Server A.
Restore the database with the backup you just created and with norecovery on Server B.
Re-enable the log shipping jobs on both servers.


SQL Drive getting full delete millions of rows I'm looking for the easiest way to accomplish this in the limited hard drive space that I have.

I have a SQL drive that is dangerously close to getting full.  We have about 15 tables that have over 20 million rows in them (these are high read/write tables on a 24/7 database).

After running a few queries, it's determined that we can deleted about 5 million rows each from the 15 tables.

What is the safest way to delete all these rows from the tables without causing the log file to fill the rest of the hard drive space?

Batch deletion is your best bet.

If possible, I would do something like:

<create lookup table with a list of PKs or keys of deletable rows>

WHILE 1=1
BEGIN
    DELETE TOP (100000)
    FROM MyBigTable T
    INNER JOIN MyLookupTable L
          ON T.PK = L.PK
    IF @@ROWCOUNT < 100000 BREAK;
END

Batching will keep your log use low, and using the INNER JOIN will reduce tempdb usage since you did the work of filtering in creating the first table.


phpmyadmin not working after mysql update i followed this guide to update my mysql form 5.1 to 5.5. After lots and lots of command and headache, i was able to upgrade mysql. i checked the version via terminal.

But now there is another problem. phpmyadmin is not working. It says

Forbidden

You don't have permission to access /phpmyadmin/ on this server.

Apache/2.2.14 (Ubuntu) Server at localhost Port 80

i searched everywher and tried various solutions like including Include /etc/phpmyadmin/apache.conf in apache2.conf file and restared apache(it started showing 404 error). But it's still not working.

i remember the terminal was showing phpmyadmin is broken or not fully installed, while i was trying various solutions.

What should i do now? repair, reinstall, format??

Edit: Also now when i open terminal(Ctrl + Alt +t), it shows various errors:

Command 'uname' is available in '/bin/uname'
The command could not be located because '/bin' is not included in the PATH environment variable.
uname: command not found
bash: [: !=: unary operator expected
bash: [: too many arguments
bash: [: too many arguments
bash: [: too many arguments
bash: [: =: unary operator expected
bash: [: too many arguments
bash: [: too many arguments
bash: [: =: unary operator expected
bash: [: =: unary operator expected
bash: [: =: unary operator expected
bash: [: =: unary operator expected
bash: [: =: unary operator expected
Command 'sed' is available in '/bin/sed'
The command could not be located because '/bin' is not included in the PATH environment variable.
sed: command not found
bash: [: too many arguments
bash: [: =: unary operator expected
bash: [: too many arguments
bash: [: too many arguments
bash: [: =: unary operator expected
bash: [: =: unary operator expected
bash: [: =: unary operator expected
bash: [: =: unary operator expected
ubc-7@ubc-7-desktop:~$

Of the three, I prefer repair!

Looks like your phpMyAdmin was installed by the package manager, so the first thing to try is reconfiguring it. sudo dpkg-reconfigure phpmyadmin should accomplish that. Hopefully that will fix the error message you got that "phpmyadmin is broken or not fully installed" -- or if not, at least provide you with a more helpful message.

You may have other broken packages or missing dependencies, which you can try to fix with sudo aptitude -f. That will attempt to fix any broken packages.

I'm not really sure about your terminal error message. It appears that the /bin/ directory (which contains many of your system commands like ls, kill, mkdir, and many more), isn't in the list of directories used when looking for a command (the "path"). I'm hopeful that the aptitude -f will fix this too, because otherwise something really strange (and unrelated) happened to your bash configuration file.

Note that, while these are reasonably safe commands, I'm not responsible if your system explodes.


Long running READ queries stuck in "idle in transaction" I have a JDBC connection pool in a web API that makes frequent requests to the RDS Instance. Each request is for a long running SQL query where PG decides not to use indices because the amount of data to be returned is quite large.

I'm noticing that some of these long running queries are just stuck in state idle in transaction. When look up the pid for these processes in pg_locks table they have 152 rows . With mode AccessShareLock.

The intent for this table is to be never updated -- It serves as a point in time table. So I don't see a need for idle transaction or accesssharelock. Is there I can turn these off so that my queries run faster and aren't stuck because of AccessShareLock?

idle in transaction means the connection is not doing anything - it's "idle".

The query has finished, if the query was still running the connection would be shown as active.

The code that initiated the query, forgot to end the transaction by calling commit or rollback. If a connections stays in "idle in transaction" for a long time, this is typically a bug in the application.

Make sure your application properly ends every transaction - even "read-only" ones that only run SELECT statements. Or switch your connection to auto-commit in your code.


How to change the Write Ahead Log directory on Windows On postgres 9.6 for Windows server 2008. How do I change the Write Ahead Log directory?

Thanks.

From the docs

  WAL logs are stored in the directory pg_wal under the data directory, as a set of segment files [...]

The docs go on to day,

  It is advantageous if the log is located on a different disk from the main database files. This can be achieved by moving the pg_wal directory to another location (while the server is shut down, of course) and creating a symbolic link from the original location in the main data directory to the new location.

So your options are simple on Windows

NTFS Symbolic Link, or ReFS symbolic links.
Change the data_directory


Is MySql locking table during backup and how to avoid? I have a scheduled backup that runs daily in MySQL and I have noticed that during the backup a lot of queries that query a frequently used table seem to take forever to complete. This causes the website to lag like crazy during the backup and once the backup completes, specifically for the frequently used table, the site runs fast again.

Does MySQL lock table(s) during the backup and if so is there anyway to avoid this?

How do other big systems handle backups without disrupting / slowing their applications?

Thanks

Have you checked Run MySQLDump without Locking Tables question?

Everything boils down to the way you're doing data backups.


SSIS Package - "Subquery returned more than 1 value" - Inserting one record into a table SSIS on SQL Server 2008 R2

I have inherited an SSIS package that is failing when importing a spreadsheet. The part of the failure is in a data flow that consists of :-
  * an Excel Source Data Flow Component
  * a Derived Column Data Flow Component
  * an OLE DB Destination Data Flow Component

I have added data viewers on the two flows, and these show only 1 row on each.

The table that I'm importing into is empty.

I'm not sure what else to look at. Any assistance is appreciated. Thank you.

In my case there was a trigger that trying to update one of the rows. The unfortunate thing was that the query in the trigger returned more than one row in the subquery.

As it turned out for me the trigger was only relevant for one particular supplier (an IF condition in the trigger), also luckily for me, the previous occupant of my position hadn't reviewed this trigger, and it was no longer needed.

Removed the trigger. However in other scenarios I would have had to modify the trigger to return one row only.


DB2 - question regarding RESTORE OFFLINE backup and Archive Logs I configured last night DB2 to be in Archive Mode.
I created required offline backup at 23:00 last night.
First online backup will be created at Friday night.
Archive logs now keep coming to the archive log location which is OK.

My question is:
can I use restore operation (ROLLFORWARD also) for combination with OFFLINE backup and Archive Logs  or ROLLFORWARD operation can be used only for combination ONLINE backup+Archive Logs???

I am confused little with that.

So my question is can I do the restore of offline backup last night and ROLLFORWARD for example archive logs to the point of time today at 10 A.M.?

Is this possible or no? If this is possible what will be the command?Will it be different like when restoring Online Backup+Archive Logs???

Thank you in advance

Yes, you can issue ROLLFORWARD after restoring an offline backup. The ROLLFORWARD command syntax remains the same, regardless of the type of restored backup image.


Query multiple rooms with different conditionals (Adults, Children) I have a simple query where I select available x Rooms with x Adults + x Children per hotel that matches a date range, but I'm having a hard time trying to figure out how to query a list of rooms per hotel like this:

1 Room with 2 Adults / 0 Children
1 Room with 4 Adults / 2 Children
1 Room with 2 Adults / 1 Children

Here is my query:

SELECT
COUNT(pl.day) AS Days, p.property_ID AS Hotel_ID,
p.name AS Hotel_Name, r.room_name AS Room_Name, r.room_type_ID AS Room_ID

FROM property p

  INNER JOIN room_type r
    ON p.property_ID=r.property_ID
    AND (r.max_adults >= 3 AND r.max_children >= 0)

  INNER JOIN plan pl
    ON pl.room_type_ID=r.room_type_ID
    AND (pl.day >= "2014-07-07" AND pl.day <= "2014-07-11")

GROUP BY Room_ID, Hotel_ID
HAVING Days = 4

EDIT:
The tables are:

property > property_ID ...
plan > property_ID | room_type_ID | day | rate | alloc | min_stay ...
room_type > property_ID | max_adults | max_children ...
...

first insert the resultset of the room_type table to a temp table with the filtering by your parameters

 r.max_adults >= 3 AND r.max_children >= 0

then query that table and join the property  and plan tables to it and it should be faster.


Tricking SQL server into thinking it's a different date I have a unique problem (at least for me) that I have solved in one way but would like an easier way if possible :)

I often have to test our Windows app with dates in the past. Our app queries a SQL server which uses GETDATE.

I have setup a VM with our app and SQL server on it. I can change the system date backward or forward as needed after doing the below through powershell:

stopping windows time service
if running on HyperV, stop the vmictimesync service
setting the new date
restart time services
restarting the sql server (just in case) and our app

It seems to work very well - both our app and the SQL server can't tell the difference.

If I need to reset the date back to current I simply restart the windows time service. Easy.

However, it's a bit of effort to set that up at moments notice and was wondering if there is a way to fool the SQL server or an individual database into thinking it's a different date as needed?

So with the individual db perhaps I can set some properties such that it would shift GETDATE calls back to a certain time period.

All a stretch I know but if anyone has any other ideas I'm all ears.

Unfortunately our devs can't change the app to make this all easier.

The short answer: no. Ask the developers to fix the problem.

The long answer: it is possible to detour system calls. See for example Modifying application behavior with Detours (for Application Compatibility reasons). This allows you to modify the result of a system call for a specific process. In the linked example it is the GetSystemTime call and the author modifies it to return a date in the past. You can do similar and load sqlservr.exe in a detour environment and return the date of your choice.

However, I would not call this method 'easier' than modifying the system clock. And for sure is riskier. Not to mention, I'm not even sure the GETDATE translates to a call to GetSystemTime...

You could also try RunAsDate

  RunAsDate is a small utility that allows you to run a program in the date and time that you specify. This utility doesn't change the current system date and time of your computer, but it only injects the date/time that you specify into the desired application.

Look at your service parameters (eg. sc.exe qc MSSQLSERVER). You must match the startup parameters (what sc.exe reports as BINARY_PATH_NAME) and the startup user (ie. SERVICE_START_NAME). If you don't run as the correct user you simply won't have access to the required files (master, tempdb, logs etc).

See How to: Start an Instance of SQL Server (sqlservr.exe)


Trouble setting up Nagios to monitor Oracle services I've got an install of Nagios XI that doesn't seem to want to talk to any of my Oracle services here. I've pulled out the monitoring command and am running it manually, after setting ORACLE_HOME and LD_LIBDRARY_PATH of course, but it keeps generating the following error:

/usr/local/nagios # libexec/check_oracle_health --connect "oracle-server:1551" --username user --password "pass" --name OFFDB1 --mode tablespace-can-allocate-next --warning 20 --critical 30
CRITICAL - cannot connect to oracle-server:1551. ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA (DBD ERROR: OCIServerAttach)

I'm still fairly new to Oracle, but my googlings seem to indicate that SERVICE_NAME should match the GLOBAL_DBNAME in listener.ora, which is OFFDB1. Do I need to do something else here like modify the connect string?

As a note, there are multiple instances of oracle sharing the target box, but each seems to be intalled to separate partitions and are running their own listeners or various ports.

As Phil mentioned, I created $ORACLE_HOME/network/admin and tnsnames.ora.  I have something like this:

OFFDB1 =
  (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-server)(PORT = 1551))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = OFFDB1)
    )
  )

I then modified the monitoring commands:

/usr/local/nagios# libexec/check_oracle_health --connect=OFFDB1 --username user
    --password "pass" --mode tablespace-can-allocate-next --warning 20 --critical 30


How to debug trivial MariaDB query with simple execution plan which often runs really slowly? The query (with literal ? for dynamic query goodness):

SELECT UNIX_TIMESTAMP(my_timestamp_field) AS my_name
  FROM my_table
 WHERE my_column=?

This query is one of the most common in my slow query log (limit 1 second) - it has taken up to 10 seconds several times just in the last few hours. The lock time on every one of the slow queries except one was 50ms±2ms. I've done the research below, but I'm stumped for how to continue.

Is it possible that another lock somehow is taking up the time without being counted in the lock time? Are there any AWS EBS volume limits I need to be aware of?

Query plan with representative value:

MariaDB [my_schema]> EXPLAIN SELECT UNIX_TIMESTAMP(my_timestamp_field) AS my_name FROM my_table WHERE my_column=10094;
+------+-------------+----------+------+-----------------+---------------+---------+-------+------+-------+
| id   | select_type | table    | type | possible_keys   | key           | key_len | ref   | rows | Extra |
+------+-------------+----------+------+-----------------+---------------+---------+-------+------+-------+
|    1 | SIMPLE      | my_table | ref  | my_column_idx   | my_column_idx | 8       | const |    1 |       |
+------+-------------+----------+------+-----------------+---------------+---------+-------+------+-------+

The table is quite simple, with 21 fixed width columns:

MariaDB [(my_schema)]> SHOW CREATE TABLE my_table\G
*************************** 1. row ***************************
       Table: my_table
Create Table: CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `my_column` bigint(20) NOT NULL,
  `my_timestamp_field` datetime DEFAULT NULL,
  [other columns]
  PRIMARY KEY (`id`),
  UNIQUE KEY `ind_some_key` (`other_column_1`,`other_column_2`),
  KEY `my_column_idx` (`my_column`)
) ENGINE=InnoDB AUTO_INCREMENT=48033 DEFAULT CHARSET=utf8

The table is 45k rows, and all the columns are fixed length.

Running MariaDB 5.5 on CentOS 7 on a fast AWS EC2 machine.

Only answering the question "how to debug it further".

(Sorry about this one, but it must always be said) Make sure you're looking at the right record in the slow log. The part before a query relates to the query, the part after it does not:

# Time: 161128 19:20:53
# User@Host: root[root] @ localhost [127.0.0.1]
# Thread_id: 25  Schema: test  QC_hit: No
# Query_time: 1.925981  Lock_time: 0.924844  Rows_sent: 0  Rows_examined: 0
SET timestamp=1480353653;
select UNIX_TIMESTAMP(my_timestamp_field) AS my_name FROM my_table WHERE my_column=9;
# Time: 161128 19:21:11
# User@Host: root[root] @ localhost [127.0.0.1]
# Thread_id: 25  Schema: test  QC_hit: No
# Query_time: 10.000428  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0
SET timestamp=1480353671;

The first 6 lines in the block above are the relevant part. Even if the query actually takes 1 second and not 10, it is worth investigating, but it would be a quite different kind of mystery.

Double-check the records in the slow log, specifically Rows_sent and Rows_examined. Since my_column is not unique, it's possible that with some parameter values, or with some unfortunate statistics, the query scans many rows, not just one as in your query plan example. This should rule out a bad execution plan.

# Time: 161128 19:42:19
# User@Host: elenst[elenst] @ localhost [127.0.0.1]
# Thread_id: 26  Schema: test  QC_hit: No
# Query_time: 1.622502  Lock_time: 0.000373  Rows_sent: 32768  Rows_examined: 32768
SET timestamp=1480354939;
select UNIX_TIMESTAMP(my_timestamp_field) AS my_name FROM my_table WHERE my_column=3;

Additional check in regard to a bad plan. Try to run a similar query, but not using index, either with a non-indexed column in WHERE, or with IGNORE INDEX hint. The goal is to get full scan and to see how long it takes. With 45K records, even full scan shouldn't take 10+ seconds; but if it does, a wrong plan is still on the table. If it's not anywhere near 10+ seconds, then more likely something else is blocking the query.
Start a monitor which runs SHOW PROCESSLIST frequently, e.g. every 5 seconds, so that you certainly catch a 10-second query. Better if the monitor runs in a persistent connection, but if it's too difficult, just run the MySQL client with -e 'SHOW PROCESSLIST' in a loop. Don't forget to show timestamps, too. When you catch the query, the State field will show in which stage the query spends the time, and possibly the process list will also give a hint with which other queries your might be conflicting.
If you have permissions, add SHOW ENGINE INNODB STATUS\G to the monitor. It might show some details if the query is spending time inside InnoDB.
Add SHOW GLOBAL STATUS LIKE 'Open%' to the monitor, maybe less frequently. It should show whether the tables get kicked out of the table cache too often, it can cause delays in a busy environment.


Merge replication fails on executing query My merge replication was working fine this morning. Since an hour or 2 ago, my synchronisation status windows keeps showing this message:

Thread id 3108 will wait for 15 second(s) before retrying the query on Publisher 'MSI-SQL-MAS01'.

Can anyone tell me what's going on? The database is 500Gb in size and it's also pretty heavily used. However, the replication must work, we need to keep the database at the subscriber in sync.

Also this is what I see after a minute or so:

A query executing on Publisher 'MSI-SQL-MAS01' failed because the connection was chosen as the victim in a deadlock. Please rerun the merge process if you still see this error after internal retries by the merge process.

Like I just said, the replication was working fine this morning. I also restarted the merge agent like the error suggested but with no results.

I also tried

update sysmergepublications set [generation_leveling_threshold] = 0

at both subscriber and publisher database but no luck it seems.. only getting the following error sometimes too:

The merge process is retrying a failed operation made to article 'xx' - Reason: 'The merge process was unable to synchronize the row.'.

Thanks in advance.

I don't see my former error anymore. The only thing I get now is:

"The merge process is retrying a failed operation made to article 'xx' - Reason: 'The merge process was unable to synchronize the row.'."

The profiler makes no sense to me. I don't see any relevant information.

If I reinitialize the subscription, what does it do? I hope I do not need a new snapshot for that because that would be impossible at this time. The database is over 500Gb in size and the snapshot takes over 9 hours to complete. The database is in production all the time and very heavily used, so a lock on the database to create a snapshot is really a no-go.

Alright this is what I see in the conflict viewer:

The same row was updated at both 'master_server' and 'slave_server'. The resolver chose the update from 'master_server' as the winner.

How is it possible that the same row gets updated at both master and slave server while our application is connected to the master server?
It looks like something is updating the same row at both servers with different content and made merge agent crash?

"It looks like something is updating the same row at both servers with different content and made merge agent crash?"  This is handled by merge conflict tables, and would not cause the issues you are describing.  These conflict tables are located on the publisher database, and are named like:  MSMerge_conflict__.

To answer your question about what reinitialization does, by default, reinitialization will take a snapshot of your published articles, drop the articles on the subscriber side, recreate the articles on the subscriber side, and then bulk load data from the snapshot into the subscriber articles.  Since this is a production environment, and those articles need to be available on the subscriber side, this should only be used as a last resort.

What you can do is query the MSrepl_errors table on the Distribution database.  This will provide you with a command_id and an xact_seqno.  You can use these values as inputs into the sys.sp_browsereplcmds stored procedure.  This will provide you with command text that is actually failing.  Using this information, you can better understand the nature of the failure.  If a particular row cannot be inserted or deleted at the subscriber, you may have to either delete the existing row (to allow the insert) or insert a dummy row (to allow the delete), respectively.

I hope this information helps,

Matt


Scripting Microsoft Data Warehouse Wizard I am trying to automate the MDW setup process in my environment. Currently I have everything complete except for enabling the data collectors (usually uses a wizard as seen ).

Has anyone seen or used a script for this? Or have any suggestions?
Thanks.

This is not my area of expertise, but you might look at Dennes Torres Simple Talk post from January 2015 for any takeaways that help you.

https://www.simple-talk.com/sql/database-administration/centralize-your-database-monitoring-process/

Two subheadings of particular interest may be:

Configure Data Collector through CMS
Configure Collection Sets through CMS


How does sampling work when updating statistics? I have several massive tables. I'd like to ensure their statistics are up to date via a weekly maintenance plan.

However, doing so is taking too much time.

If I specify

WITH SAMPLE 50 PERCENT

does SQL Server then sample:

the first 50% of pages
every other page
or some other strategy?

BOL is not clear on this.

For WITH SAMPLE 50 PERCENT it works as though for each data page in the table SQL Server flips a coin. If it lands heads then it reads all the rows on the page. If it lands tails then it reads none.

Tracing the UPDATE STATISTICS T WITH SAMPLE 50 PERCENT call in Profiler shows the following query is emitted

SELECT StatMan([SC0], [SB0000])
FROM   (SELECT TOP 100 PERCENT [SC0],
                               step_direction([SC0]) OVER (ORDER BY NULL) AS [SB0000]
        FROM   (SELECT [C] AS [SC0]
                FROM   [dbo].[T] TABLESAMPLE SYSTEM (5.000000e+001 PERCENT)
                WITH (READUNCOMMITTED)) AS _MS_UPDSTATS_TBL_HELPER
        ORDER  BY [SC0],
                  [SB0000]) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 1)

With Plan

TABLESAMPLE SYSTEM (5.000000e+001 PERCENT) is responsible for the sampling and is documented here

  TABLESAMPLE SYSTEM returns an approximate percentage of rows and
  generates a random value for each physical 8-KB page in the table.
  Based on the random value for a page and the percentage specified in
  the query, a page is either included in the sample or excluded. Each
  page that is included returns all rows in the sample result set.

The documentation  also states

  Although the plan shows a table scan is performed, only those pages
  that are included in the result set are actually required to be read
  from the data file.

The STATMAN call is to an internal aggregate function briefly described here


Separate or Combine 2 Tables with Similar Attributes? I have two kind of users in my business system: Customer and Employee.
Both user have Username, Password, Fullname, Phone Number, Email, and other similar attributes.

  I have a difficulty to determine which is  better to merge
  Customer and Employee on one table (for example I store in User table) or separate each entities on different table?

On my case, the Customer has additional attributes that the Employee doesn't had (for example: NewsUpdateSubscription). And also for Employee, it has additional attributes that Customer doesn't had (for example: Salary). What is the best practice for this case? Thanks in advance.

I'd say "Customer" and "Employee" are two roles a person can play in your system. Thus, I'd add concept of "Role" to your design. Then you can store customer related attributes in one table, and employee related attributes in another. You will have something like

User (user_id, name, ...)
Role (role_id, description)
UserRole (user_role_id, user_id,role_id, date_from, date_thru)
-- note, dates are quite important(at least date_from should be not null)
CustomerRoleAttribute(user_role_id, NewsUpdateSubscription)
EmployeeRoleAttribute(user_role_id, salary)

Also, to be absolutely correct, Customer and Employee roles require second part - for instance, I'm a customer of AAA company, and an employee of BBB, so my role is defined by relationship of certain type with other user (which is organization in case of "Employment" relationship).

You can find out more about this approach in "The Data Model Resource Book" by Len Silverston


Exporting SQL Server 2008 R2 Activity Monitor Log In SQL Server 2008 R2, admin users are allowed to view the Activity Monitor and under "Recent Expensive Queries", there is a list of SQL statements being ran on the server.

Is there a way to export each and every one of the SQL statements displayed without using SQL Server profiler?

I was given the responsibility of monitoring a database with many redundant tables and I have no idea which tables are actually in use.

I want to remove those dummy or unused tables and retain the productive tables.

Any suggestions on how to accomplish this?

Well you can (on a dev system of course) run a trace against yourself running activity monitor and then isolate the queries you want to use for monitoring.  Then you could either create custom jobs or use custom collectors with management data warehouse to execute those queries on your desired schedule to log the activity you want to another table.  Then you can just analyze the results there.


how can I get only the SQL server logins, when we use Export-dbalogin when we run the export-dbalogin, it will will generate a script(.sql) file, which we will run the in the destination server, then the logins will be migrated, but this command will give both windows as well as SQL server logins. I only want SQL server logins.

I tried to use Get-dbalogin to generate a list of SQL server login and kept them in a variable, then I tried to pass that variable to the -login switch in Export-DbaLogin but it is not working.

Thanks In Advance.

As you can see from the Get-DbaLogin source, the -Type parameter accepts a value of 'SQL' to filter to only SQL Logins and exclude AD logins. You can pipe this directly into the Export-DbaLogin function like so...

Get-DbaLogin `
    -Type 'SQL' `
    -SqlInstance localhost |
Export-DbaLogin `
    -FilePath 'foo.sql'

Note that some logins are excluded by default as shown in the warning feed.


How to separate out individual sql statements from Oracle SQL script for execution from java code I have Oracle DB 11g Enterprise Edition and I want to execute sql script from my java code.
I am reading this sql script from a .sql file and the script contains statements such as create table, create type, alter table, drop type, drop procedure, insert into as well as pl/sql blocks with begin and end and all such constructs.

My question is, how to separate out individual sql statements for execution from java code? In MS SQL I could simply separate these sql statements by GO keyword, whereas in Oracle db there is no such separator. So normally what is used as separator in Oracle db scripts?

As an exmple say I have following create trigger block in my sql script along with other sql statements. Here I have semicolons which I can not consider as the SQL Statement terminating charter (PL/SQL block can itself contain multiple semicolon-separated statements). Which means I can not use ; similar to GO (in MS SQL).

CREATE OR REPLACE TRIGGER SQLVersionHistory_SeqNum_TRG
   BEFORE INSERT
   ON SQLVersionHistory
   FOR EACH ROW
   BEGIN
    :NEW.SequenceNumber := SQLVersionHistory_SeqNum.NEXTVAL;
   END;

What creates your sql script file? Is the sql script file creation modifiable?

If you can modify the sql script file creation, the easiest way is to append your own separator characters to your script between the statements. (Comments should be a good idea)

If not, if you're just receiving a bunch of sql or pl/sql statements in a unmodifiable-source plain text file, then you should code your own parsing algorithm.
You must keep in mind that EVERY sql statement end with semicolon (;), but since PL/SQL blocks can contains several sql statements they must end with a forwarded slash (/) by itself in a new line.

The confusing part is that some SQL statements are, in fact, stored PL/SQL blocks, like CREATE PROCEDURE or CREATE TRIGGER (complete list in documentation book "Oracle® Database PL/SQL Language Reference" : http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/sqlstatements.htm#LNPLS99979 )

(e.g.

ALTER, SELECT, DROP, GRANT etc.. statements end with semicolon ;
CREATE TABLESPACE, CREATE USER, CREATE TABLE, etc statements end with semicolon ;
CREATE PROCEDURE, CREATE PACKAGE, CREATE TRIGGER, etc statements end with slash /
DECLARE, BEGIN (annonymous pl/sql blocks) statements end with slash /

)

Regards