How can I measure the impact of queries with errors being rejected by SQL Server? After configuring XE on November 23, 2020 to capture some errors I could count 2,163,665 occurrences of the same error coming from requests of a legacy app (that probably won’t be corrected so soon). The errors can be classified on the 1st and 2nd steps for Processing a SQL Statement:

The DBMS first parses the SQL statement. It breaks the statement up
into individual words, called tokens, makes sure that the statement
has a valid verb and valid clauses, and so on. Syntax errors and
misspellings can be detected in this step.
The DBMS validates the statement. It checks the statement against
the system catalog. Do all the tables named in the statement exist
in the database? Do all of the columns exist and are the column
names unambiguous? Does the user have the required privileges to
execute the statement? Certain semantic errors can be detected in
this step.

The doc also says that Parsing a SQL statement does not require access to the database and can be done very quickly, but the amount of requests being fired at the server made me think I should verify.
I’d like to measure the resource consumption impact caused by those wrong requests being rejected on those steps. Is there a way to do so?

I see from your comment that the errors are not happening so frequently as to be a performance concern.
To answer the question asked, you could create a filtered XE trace to capture detail sql_batch_completed and rpc_completed events where result is Error. The events can then be summarized by a time interval for aggregated stats.
The example below summarizes the CPU and logical reads by one minute interval from a trace file target.
--example trace
CREATE EVENT SESSION [errors] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
    WHERE ([result]=(1))),
ADD EVENT sqlserver.sql_batch_completed(
    WHERE ([result]=(1)))
ADD TARGET package0.event_file(SET filename=N'C:\TraceFiles\errors',max_rollover_files=(2))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
GO

--example aggregation query
WITH
    event_data AS (
        SELECT CAST(event_data AS XML) AS event_data_xml
        FROM fn_xe_file_target_read_file('C:\TraceFiles\errors*.xel',NULL,NULL,NULL)
    )
    , event_data_fields AS (
        SELECT
              DATEADD(minute,DATEDIFF(minute,'',event_data_xml.value('(/event/@timestamp)[1]', 'datetime2')),'') AS interval
            , event_data_xml.value('(/event/data[@name="duration"]/value)[1]', 'bigint') AS duration
            , event_data_xml.value('(/event/data[@name="cpu_time"]/value)[1]', 'bigint') AS cpu_time
            , event_data_xml.value('(/event/data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads
        FROM event_data
    )
SELECT
      interval
    , SUM(duration) AS duration
    , SUM(cpu_time) AS cpu_time
    , SUM(logical_reads) AS logical_reads
FROM event_data_fields
GROUP BY interval
ORDER BY interval;


Search entire database Recently I was working on the database of an application that had no documentation and the database did not have any foreign keys. I needed to create a script to enforce a 10 year retention policy. It would have to go through and delete data that was older than March 31, 2006.

I wanted to get an idea how much of the database was affected but that is really hard without a data diagram. I ended up writing a script that would search the entire database for dates older than March 31st, 2006.

I know this seems like brushing your teeth with a sledge hammer but is there any way to improve this query?

--MOAQ: Mother of all Queries
--Sam Nesbitt
--2016-03-01

--user definable
declare @colType        int
declare @searchData     varchar(10) --change this datatype as needed
declare @searchOpp      varchar(10) --enter seach opperator
declare @searchTable    varchar(100)

set @colType = 61
set @searchData = '''2006-03-31'''      --Enter search data here
set @searchOpp = '<='               --Enter your search opporator here                          --'<='
set @searchTable = '%'              --Give the query a hint to the column name its looking for  --'%start_dt%','%'

IF OBJECT_ID('tempdb..##searchResults') IS NOT NULL
    DROP TABLE ##searchResults
CREATE TABLE ##searchResults(
    tableName   varchar(100)
    ,columnName varchar(100)
    ,result     datetime        --make sure this data type matches @searchData data type
)

--system use

declare @tableName  varchar(100)
declare @colName    varchar(100)

declare @searchTables table(
    tableName   varchar(100)
    ,columnName varchar(100)
)

insert into @searchTables
select QUOTENAME(d.TableName), QUOTENAME(d.ColName)
from
    (SELECT c.name AS ColName, t.name AS TableName
    FROM sys.columns c
        JOIN sys.tables t ON c.object_id = t.object_id
    WHERE c.name LIKE @searchTable
        and c.system_type_id = @colType --is datetime
    ) as d

--create a cursor for memory table
declare c1 cursor read_only
for select tableName, columnName
from @searchTables

print 'begin queries'

open c1
fetch next from c1
into @tableName, @colName

while @@FETCH_STATUS = 0
begin

    --magic happens here
    EXEC
    (
        'INSERT INTO ##searchResults select ''' + @tableName + ''', ''' + @colName + ''', d.' + @colName + ' from' +
        '(SELECT ' + @colName
        + ' from dbo.' + @tableName
        + ' where ' + @colName + ' ' + @searchOpp + ' ' + @searchData + ' ) as d'
    )

    --dump queries to log
    print 'INSERT INTO ##searchResults select ''' + @tableName + ''', ''' + @colName + ''', d.' + @colName + ' from' +
        '(SELECT ' + @colName
        + ' from dbo.' + @tableName
        + ' where ' + @colName + ' ' + @searchOpp + ' ' + CAST(@searchData AS varchar(256)) + ' ) as d'

    fetch next from c1
    into @tableName, @colName
end

close c1
deallocate c1

--select * from @searchTables
--select * from ##searchResults

--pull summary of results here
select tableName, columnName, count(result)
from ##searchResults
group by tableName, columnName
order by tableName, columnName

--comment this out if you want to use the results before they are dropped
IF OBJECT_ID('tempdb..##searchResults') IS NOT NULL
    DROP TABLE ##searchResults

https://github.com/Swazimodo/MOAQ

Update
I used QUOTENAME to wrap the column and table names. I also added dbo. to the table reference. One gotcha that I found was that QUOTENAME could not be inside the EXEC function. It had to be a sting literal.

I'm wondering if you can use the undocumented stored proc sp_MSforeachtable rather than use cursors?  Here's a little bit of code that brings back the number of rows in each table. Can you modify it to do what you need to do?

use pubs2014
go
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
'insert into #rowcount select ''?'', count(*) from ?'
select top 5 * from #rowcount
order by tablename
drop table #rowcount

And yes, I've brought both the pubs and the Northwind databases forward into SQL 2014 AND 2016...I'm nostalgic, what can I say?


MySQL Replication - Accidentally Dropped Database on Slave I am using MySQL replication and I executed DROP DATABASE against my slave by accident (long story). Can I execute DROP DATABASE IF EXISTS against my master to safely drop the schema on my master without causing replication to blow up?

This schema is no longer being written to on the master.

Yes, you should be fine.

You can DROP DATABASE IF EXISTS mydata; on the Master should replicate to the Slave, execute the IF EXISTS part on the Slave, do nothing, and be successful without erroring out.

I have done this before.

As an alternative, you could also run this on the Master:

SET sql_log_bin = 0;
DROP DATABASE mydata;
SET sql_log_bin = 1;

or

SET sql_log_bin = 0;
DROP DATABASE IF EXISTS mydata;
SET sql_log_bin = 1;

I have done this before as well.


Inserting data into a second table with some time lag I am new to SQL Server. I am using Management studio 2012 Express edition.

What I want to do is that suppose I have table1 and table2 and suppose someone enters some data into table1, then after some time lag the data must be entered automatically into table2.

I remembered reading about scheduled events, but realized that the express edition does not support Server Agent.

Any suggestions as to how I could tackle this problem?

Thank you :)

Given the constraints of Express (http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx), your best option is to fake an agent job by using Windows Scheduled Tasks and call SQLCMD on a stored procedure every n units of time.  Here is a basic SQLCMD usage rundown: http://msdn.microsoft.com/en-us/library/ms180944.aspx.


How to migrate huge MySQL databases one by one to new server? We have several servers with huge databases (on average 100-200GB per DB up to ~500GB for the biggest ones). All of them have a slave that is used for backups.

Now I need to migrate those with minimal or no downtime and preferably one DB at a time. First thing that came to my mind is doing a backup including master-data and setup replication. With the size of these databases dumping and reimporting takes quite a while, so I was looking into using innobackupex. That works and I can filter for one DB. However I still get all directories for all databases (even though they're empty) and an ibdata1 file of close to 2GB.

My main concern is, that this will only work for the first database. I can't do that for a second one and add that to a running slave as this would conflict with already existing data. Am I missing something or is it only possible to setup a slave for ALL databases of an existing MySQL server at once?

The servers are a mix of MySQL 5.1 and 5.5 and the new hosts should start with 5.7. Does the multi-source replication feature help with my approach?

I think I found a solution which is close to what I'm trying to achieve: https://dba.stackexchange.com/a/76198/107793.

The only thing I have to look into is, how to avoid the possible downtime with locking the tables. Since I'm on InnoDB exclusively (apart from the MySQL system DBs) that should be possible.


While testing our workload on SQL Server 2019 we experience much higher CPU utilization compare to the same test on SQL Server 2014 We are getting ready to upgrade our SQL Server 2014 systems to SQL Server 2019. As part of our due diligence we created a workload which we are testing against both systems. What we have observed was the following:

The query performance on average is about 50% faster on SQL Server 2019 - good!
The reads are about 40% less on SQL 2019 - good!
The CPU utilization is about 30% higher on average - not good.
This last point is what causes our concern. Does this means that we have to plan to increase our CPU capacity as part of our migration to SQL Server 2019?

To describe what we are seeing from slightly different angle: when we attempt to ramp up our workload by pushing higher number queries/sec, we are seeing lower throughput on SQL Server 2019 because we max out CPU earlier and start seeing errors as a result.
I hope this makes sense and I wonder if the others had similar experience?

Firstly: Is the CPU use really more for the same amount of work? As performance is 50% faster 33% more work is being done on average in any given period of time, so using 30% more CPU resource cancels down to using the same amount of CPU for the same amount of work, just over a shorter period of time. The fewer page accesses could explain this: the CPU is spending less time waiting for IO to complete between bits of work.

Of course this depends on how you are measuring CPU utilisation - remember that for modern CPUs some readings can be significantly inaccurate (see https://aaron-margosis.medium.com/task-managers-cpu-numbers-are-all-but-meaningless-2d165b421e43 amongst other similar articles). We need more details about how you are making measurements to give truly helpful answers.

we are seeing lower throughput on SQL 2019 because we max out CPU earlier

Again, we need to know how you are measuring throughput. Though as above you may be maxing out the CPU earlier because less IO is needed.
Also: have you compared query plans used between the runs on the different versions? It is possible that some of the queries being submitted are getting less optimal plans under the updated engine - the differences in things like cardinality estimates are usually beneficial but as they are only guestimates can backfire.

and start seeing errors as a result

Never simply report "I got an error". What errors are you seeing?


MongoDB setting permission for functions I want to create execute permission on some MongoDB functions
like permissions in Sql Server.

I want users access data that function provides for them. This is, because of security of application
that don't other team staf change collections.

Is there any solution for doing this?

Finally I got the answer. For executing user defined javascript functions in MongoDB first of all need to call db.loadServerScripts().

Every database in Mongo has a collection named system.js, which is in systems collections group.We can define our roles and permissions for collections and define privilage for system.js collection too.

If any function uses collections that we don't have permission to use, function will not execute and return authorization error, if collection have those privilages, funcion execute successfully


How to optimize this Select MAX() SQL query? In the following select query, it take almost 1 min to return the result, seems the table has over 2 millions record and growing. There are no index i can use from the table. How do I optimize this query?

SELECT MAX(StartDate) AS Expr1
FROM [MYSERVER].MYDB.dbo.ProductActivity AS ProductActivity_1

I already tried with the following, which also takes 1 min to get the result

SELECT TOP(1) *
FROM [MYSERVER].MYDB.dbo.ProductActivity AS ProductActivity_1
ORDER BY StartDate DESC

Is there another way to optimize the query?

As mentioned in the comments, an index on StartDate will let your query seek right to the row it needs, rather than scanning the whole table or another index (which is what it's likely doing right now):

CREATE NONCLUSTERED INDEX IX_StartDate
ON dbo.ProductActivity (StartDate DESC);

Like any other index, this one will need to be maintained anytime a row is inserted into, or deleted from, the ProductActivity table.  And anytime the StartDate column is updated (although that seems unlikely based on the name alone).

This doesn't seem like it would add a lot of overhead, but you should test with your workload to be sure.  If the MAX(StartDate) is needed very often, it's probably worthwhile to keep this index around.

If there are lots and lots of writes to this table (hundreds or thousands per second), and the MAX(StartDate) isn't needed often, then you might be better off just periodically waiting the 1 minute or so for that query to finish rather than paying the overhead on each write.


Oracle12c R2 on Windows 10 starts only one of two instances. Manual start results in LRM-00109 I have installed Oracle 12c R2 on my local Windows 10 (64 Bit) machine. For the installation I choose only to install the software.

After the installation I have used the tool that comes with the installation to create databases. After the first database was created and started I was able to connect to it without any problems.

But after I created a second database and restarted the machine only the newly created database was started.

The first database is not started on boot and I cannot start it using sqlplus. I get the error

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: Parameterdatei 'D:\ORACLE\PRODUCT\12.2.0\DBHOME_1\DATABASE\INITDBNAME.ORA' konnte nicht ge┐ffnet werden

The said file does in fact not exist. But such a file does also not exist for the other database. The directory contains a SPFILE<DBNAME>.ORA though for both databases. As far as I know oracle uses this pattern SPFILE<DBNAME>.ORA when starting the database. If cant find said file it goes on to use INIT<DBNAME>.ORA.

I have created a INITDBNAME.ORA file and added the content:

SPFILE = 'D:\SPFILEDBNAME.ORA'

and tryed to start the database using startup pfile = 'D:\INITDBNAME.ORA'
but the result was a

ORA-00205: Fehler beim Identifizieren der Kontrolldatei

error. Also confusing was that if the pfile was inside the Oracle Directory the command failed saying it cannot access the file.

EDIT
After I have deleted both instances, rebooted and created both instances again I can currently access them both.

When I try to login to the first instance I get the following error:

ERROR:
ORA-01116: Fehler beim Íffnen der Datenbankdatei 5
ORA-01110: Datendatei 5:
'D:\ORACLE\ORADATA\INSTANCE\DATAFILE\O1_MF_UNDOTBS1_DPKMRD6L_.DBF'
ORA-27041: Datei kann nicht ge÷ffnet werden
OSD-04002: Datei kann nicht ge÷ffnet werden
O/S-Error: (OS 5) Zugriff verweigert

EDIT
The Mirosoft Service Manager reports both inscantec as runngin.

Setting ORACLE_SID does not make any difference. After the installation of both instances I can connect via

sqlplus sys@instance1 as sysdba
or
sqlplus sys@instance2 as sysdba

But this goes well only for a short time. After couple of minutes it starts to throw ORA-01110 when I connect to the first instance.

The data files are not missing. They are in the location in which Oracle reports them missing.

I ran into the same problem. The database did not start with the service. First I checked the setting for the service using the Oracle Administration Assistant for Windows and configured the DB to start with the service start (by the way, I prefer the database to shut down immediately when the service is shut down).

But enabling the database to open at service start didn't solve the problem. Next I found that the folder %ORACLE_HOME%\database was read-only, and I did not succeed to clear this attribute using the properties dialog in the Windows Explorer. The attrib program showed no R flag, though...

It was not possible to start up the database as SYSDBA from sqlplus either - I got the same LRM-00109 error. So I copied the SPFILE to an INIT<INSTANCE>.ora file and edited it. Startup wasn't possible either, except when I used

startup open pfile='(ORACLE_HOME)\database\init(Instance).ora'

I finally changed the service from running as a virtual account to running as Local System - that works...

The Oracle version I use is 12.2.0.1 - I guess there is some problem with it.


Multiple named Instances and Availability Groups We have a two-node multi-subnet Windows Server 2012 R2 cluster in two regions. We have three named SQL Server 2012 standalone instances on each node and are in the process of setting up Availability Groups on each SQL instance. I have set one up on one instance successfully, but when I try to set up the second AG on the second named instance I get the following error message:

  A connection timeout has occurred while attempting to establish a connection to availability replica DPA01\BAPP01 with id [567C-F10B-43C3-A2DB-BEDDD307589B]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance."

It looks like it does not support multiple endpoints with different ports on a single node. Has anyone come across this scenario? First instance, I used 5023 as the end point port and for the second I used 5022. But I cannot telnet from node 2 to 1 on port 5023 whereas I can telnet from Node 1 to 2 on port 5023. Node 1 is not listening that port.

My simple question: Is the following setup supported?

  Having multiple SQL named instances on two node windows cluster and Always On Availability Groups on each Instance.

Example of my setup:

Node1
Named instances: SQLINS1, SQLINS2
Availability Groups - AO1 on SQLINS1 and AO2 on SQLINST2

Node2
Named instances: - SQLINS3, SQLINS4
Availability Groups - AO1 on SQLINS3 and AO2 on SQLINST4

Where:

Replicas of AO1: SQLINS1, SQLINS3
Replicas of AO2: SQLINS2, SQLINS4

If my question is not clear I am happy to give you more details about the setup which I am trying.

Your proposed setup looks okay to me; as long as both nodes are in the same domain, that should work fine.

What you need to resolve is the ability for each server to get to the other on the configured ports. It sounds like 5023 is being blocked by a firewall rule of some kind, or that you haven't used the same port consistently throughout the configuration of an availability group.

I've come across the error you're showing and in my case it was actually an encryption issue. Please check out the following posts in detail:

http://sqlperformance.com/2013/04/system-configuration/troubleshooting-alwayson

http://sqlperformance.com/2012/10/system-configuration/dedicated-network-availability-group


Copy SQL Server database to same server & decrypt by symmetric key return null I have a SQL Server 2012 database that I backed up and restored to the same server and instance.  When I attempt to use DecryptByKey it always returns null.

Since it is the same server, do I need to :

OPEN MASTER KEY DECRYPTION BY PASSWORD = '...';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

I have tried several things:

I dropped the symmetric key, certificate and master key.  I recreated them and added some data to a table.  The encryption portion works but the decryption still fails.  I'm using the following code to create my encryption objects:

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'mypass'

CREATE CERTIFICATE SomeCert
WITH SUBJECT = 'SomeSubject'

CREATE SYMMETRIC KEY SomeKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE SomeCert

I then encrypt and decrypt like so:

OPEN SYMMETRIC KEY CC_Key DECRYPTION
BY CERTIFICATE DB_CC_Cert;

UPDATE Table SET
     Column = ENCRYPTBYKEY(KEY_GUID('SomeKey'), @CCNum
WHERE SomeColumn = 1;

CLOSE SYMMETRIC KEY CC_Key;

OPEN SYMMETRIC KEY CC_Key DECRYPTION
BY CERTIFICATE DB_CC_Cert;

SELECT
  CONVERT(VARCHAR(50),DECRYPTBYKEY(Column)) AS Column
FROM Table

CLOSE SYMMETRIC KEY CC_Key;

This select query always returns null.  Would could be the issue?

Following worked for me on SQL Server 2008:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'master_key_password'
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY

For me it was essential to drop the encryption first, although the 'add' command should usually do this automatically.


AWS RDS (PostgreSQL) - Master user lost all privileges after upgrading from v9.6 to v10.6-R1 I just upgraded my AWS RDS DB instance from Postgresql v9.6 to v10.6-R1. I did this manually using the "Modify" tab in the Databases section under the Dashboard. Immediately afterwards, as the master user, I lost the ability to perform any operations in the public schema.

I can connect to the DB without issue, but am seeing this error when I try running a select statement on tables in the public schema: ERROR:  permission denied for schema public

It appears that my rds_superuser user can't log in anymore (freetiersuperuser is the name of my master user, although its name is misleading):

master_prod=> \du+
                                                                       List of roles
     Role name     |                         Attributes                         |                          Member of                          | Description
-------------------+------------------------------------------------------------+-------------------------------------------------------------+-------------
 freetiersuperuser | Create role, Create DB                                    +| {rds_superuser}                                             |
                   | Password valid until infinity                              |                                                             |
 rds_iam           | Cannot login                                               | {}                                                          |
 rds_password      | Cannot login                                               | {}                                                          |
 rds_replication   | Cannot login                                               | {}                                                          |
 rds_superuser     | Cannot login                                               | {pg_monitor,pg_signal_backend,rds_replication,rds_password} |
 rdsadmin          | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}                                                          |
                   | Password valid until infinity                              |                                                             |
 rdsrepladmin      | No inheritance, Cannot login, Replication                  | {}                                                          |

Additionally, running ANALYZE VERBOSE; works, and I can statistic information for my public tables.

If it helps, here are my default access privileges

master_prod=> \ddp
         Default access privileges
 Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)

Any ideas as to what went wrong or what I can do to fix this? I appreciate all suggestions.

Thank you

So running these two statements resolved my issue (being unable to access anything in the public schema):

master_prod=> ALTER SCHEMA public OWNER to freetiersuperuser;
ALTER SCHEMA
master_prod=> GRANT ALL ON SCHEMA public TO freetiersuperuser;
GRANT

However my rds_superuser role's attributes haven't changed, and I would definitely like to understand if this is normal (I don't think it is):

     Role name     |                         Attributes                         |                          Member of
-------------------+------------------------------------------------------------+-------------------------------------------------------------
 freetiersuperuser | Create role, Create DB                                    +| {rds_superuser}
                   | Password valid until infinity                              |
 rds_iam           | Cannot login                                               | {}
 rds_password      | Cannot login                                               | {}
 rds_replication   | Cannot login                                               | {}
 rds_superuser     | Cannot login                                               | {pg_monitor,pg_signal_backend,rds_replication,rds_password}
 rdsadmin          | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                   | Password valid until infinity                              |
 rdsrepladmin      | No inheritance, Cannot login, Replication                  | {}


Automatically rollback two-phase transactions on session disconnect I'm using two-phase commit in Postgres to reliably work with an external system (Amazon EC2).

I.e. I first do a PREPARE call, then call the external system. If the external system call fails for some reason, I simply rollback the prepared transaction.

It works fine. However, if I terminate my application while a distributed transaction is open, it will hang forever. Is it possible to automatically rollback 2PC transactions on session disconnection?

Anyway, it seems impossible after reading PostgreSQL source code. I settled for rolling back all the prepared transactions during application initialization.

Python code is:

def cleanup_prepared_xas(engine):
    conn = engine.connect()
    trs = [t[0] for t in conn.execute('select gid from pg_prepared_xacts')]
    conn.execute("rollback;")
    for t in trs:
        conn.rollback_prepared(t)
    conn.close()


order of inserting record is mismatching Please check the below table structure and records to be inserted in that table.

CREATE TABLE [tabGeneraltable](
   id int identity,
   [codGenLedger] [uniqueidentifier] NOT NULL,
   [codInvoice] [numeric](18, 0) NULL,
   [accountValue] [numeric](18, 2) NULL,
   [articleValue] [nvarchar](50) NULL,
   [codFinAccount] [int] NULL,
   [documentNbr] [nvarchar](50) NULL,
   [valueDate] [datetime] NULL,
   [insertDate] [datetime] NULL,
   CONSTRAINT [PK_tabGeneralLedger] PRIMARY KEY CLUSTERED([codGenLedger] ASC)
)

insert into [tabGeneraltable]([codGenLedger],[codInvoice], [codFinAccount],
                              [accountValue],[insertDate])
select NEWID(),1,11,232,getdate()
union all
select NEWID(),10,45,214,getdate()
union all
select NEWID(),9,425,410,getdate()
union all
select NEWID(),14,475,356,getdate()

After inserting all records, when executing a simple select statement on this table like

select * from tabGeneraltable

identity column ID is not coming in proper order or records are inserted randomly. (Check the insertDate column value if you insert all records separately.)

Why this is happening?

If you don't specify an order by clause your RDBMS is free to return the results as it chooses. Usually it will return the results in the order it can retrieve the records the fastest.

Since you have the column codGenLedger as the clustered key the records will be stored ordered by codGenLedger physically on disk (unless your indexes are fragmented, but they are still read in that order).

Your RDBMS will most likely do a clustered index scan in your example and return your results ordered by the clustered index.

See this sqlfiddle for a sql server example.

That is actually sorted by your uniqueidentifier, it doesn't look like it when you look at it alphabetically but it is sorted by the hex representation of the binary data.

See here for a complete explanation.

  In fact, the data is sorted; it's just not sorted the way you expect.
  SQL Server displays globally unique identifier (GUID) data as a series
  of alphanumeric strings, but the uniqueidentifier data type is
  equivalent to the binary(16) data type. The GUID value you see is
  simply the hexadecimal representation of the binary value. So the data
  is sorted by this hex representation, not alphabetically.


Is normalization process needed? Well after learning DBMS as a subject i got so many questions in mind. Normalization is one of them. As i learnt it there was a lot more confusion and i found that whatever we do in normalization process we can do it by general common sense also. Even while making projects also people are not used to follow it. So is it really needed? Is it followed in the companies? I am asking this question because probably it might consume more time to normalize the database. We can directly normalize it using just common sense therefore i don't think there is any need of following the standard normalization procedure. Correct me if i am wrong.

To answer your question, yes normalization is needed.

Common sense is a relative term and is open to interpretation.  RDBMS's have been around since the 1970's.  Normalization has been put into use on countless projects over the past 30 years, much to the benefit of the applications being developed.

The most complex time-consuming problems I have worked on in the past 15 years can be directly attributed to the database designer's lack of understanding (or willful ignorance of) the rules of normalization.

If you don't have the time to design a database properly, then you are in the wrong field, or are working on a project that is doomed to failure.


Clarification on steps creating Database Replication Definition I am being asked to create database replication definitions for a couple of databases (around 20 databases). I just wanted to clarify is the below-mentioned steps are the correct steps to be carried out?
Let's says 'customer' is one of my database.

Set ddl in tran to true
sp_dboption customer,"ddl in tran", true
Mark the primary database using sp_reptostandby
sp_reptostandby customer,'all'
Set the RepAgent parameter send warm standby xacts to true
sp_config_rep_agent customer,'send warm standby xacts', 'true'
Create db repdef/subs
create database replication definition db_repdef_customer
with primary at DS.customer
replicate DDL
replicate system procedures
go
create subscription db_sub_customer
for database replication definition db_repdef_customer
with primary at DS.customer
with replicate at DS2.customer1
without materialization
go

(Note: DS.customer and DS2.customer1 are ASE-ASE replication)

After I have followed the above steps to create db repdef/subs, I hit a lot of permission issues on my replication ID to do INSERT/UPDATE/DELETE operators on those tables I did not setup table replication yet. Further check on these tables in my 'customer' database (ex. I tried to do insert/update/delete operations manually on tables without setting table repdef, I realised that the data replication is working for all the tables under the 'customer' database with/without I setup table replication. Is this normal? Any steps I have missed out? Please help.

step 1 not needed and not normally recommended as it can lead to contention issues on system tables

setp 3 is needed if you want to replicate DDL and system procs (which coincides with what you've got in step 4 - replicate DDL/sysprocs)

create sub should be step 5

check sub should be step 6

step 7 would be sending a rs_ticket from the PDB and making sure it arrives in RDB's rs_ticket_history table

make sure your maintuser (in RDB) is aliased to dbo to get around permissions issues


Over 99 million records with geography column & spatial index - VERY VERY VERY slow queries! I’ve been working with SQL Server for a long time but have only recently encountered the need to work with spatial data. I have come into an environment that uses it pretty heavily and my first real challenge is to get queries to run faster (and stop timing out) against a table with a geography data type column (and index).

We use a query that identifies all geography points in this table that are found within polygons and multi polygons. There are over 99 million records in this table and I have NO friggin’ idea how to performance tune this beast!
I have identified the clustered index as being a bit larger than necessary and intend to add an identity column to do two things: 1) Reduce the size of the clustered index. 2) Eliminate page splitting for inserts. Although I expect to get some relief from doing this, I am not optimistic that it will help the spatial queries very much.

Given my almost complete lack of knowledge/experience with spatial data, I am
cannot make this better.

Example query:

    Declare @OrgID int
    Declare @Geog geography

    Set @OrgID =100011

    /* This will return a multi polygon */
    SELECT @Geog = geog
    FROM Organization
    WHERE orgid= @orgid

    Select count(*)
    FROM ProblemChild WITH (INDEX(IDX_geog))
    WHERE Geog.STIntersects(@geog) = 1)

    Table Design:
    CREATE TABLE [dbo].[ProblemChild](
        [Phone] [char](10) NOT NULL,
        [Lat] [float] NOT NULL,
        [Lon] [float] NOT NULL,
        [Geog] [geography] NOT NULL,
        [Recordsource] [varchar](2) NOT NULL
     CONSTRAINT [PK_Phone] PRIMARY KEY CLUSTERED
    ([Phone] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

Index design:

    CREATE SPATIAL INDEX [IDX_geog] ON [dbo].[[ProblemChild]]
    ([Geog]
    )USING  GEOGRAPHY_GRID
    WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = MEDIUM),
    CELLS_PER_OBJECT = 20, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

With:

    Number of records:  99,155,267

    MinLat      MinLon      MaxLat      MaxLon
    18.957356   -166.512394 71.292528   -66.967883

Look at the estimated query plan, and make sure the index is being used.

Also, the complexity of your MultiPolygon could be a significant factor. If you imagine your index as a series of grids over your MultiPolygon, there will be grids that either completely covered by your MultiPolygon or completely not covered by your MultiPolygon. Your ProblemChild points that fall into these grids are easy. When the grids are only partly covered by your MultiPolygon, it'll drill into the next level in and try the same.

When there are no more grid levels to drill into, the complexity really kicks in. If you have a particularly crinkly line, and you need to figure out which side of that line a particular point is, you need to check a lot of line segments and do maths around those. If you can simplify your MultiPolygon a lot, it'll speed up your query significantly. You can do this using the .Reduce() method, but then you need to be careful about correctness, in case you have points near the borders that would be on the other side of the line if were simplified.

By simplifying, I mean reducing it. Imagine an octagon, with eight points. If that were expressed using six points, or a four-point square, then the shape becomes different and something near a corner may have moved inside or outside as it the number of points reduced.

Edit: I've blogged about this topic at http://sqlblog.com/blogs/rob_farley/archive/2015/04/29/tuning-slow-spatial-queries-in-sql-server.aspx


Get two result sets in a single call I'm working on an eCommerce-based website using MongoDb.

In my DBCollection I have two types of documents:

Company details Item

{ doc_type : 'company',
  name : 'Acer',
  type : 'Laptops',
  helpline : '1800-200-000'
}

Item Details

{doc_type : "item",
  item_id : 1001,
  price : 2000,
  discount : 20}

Now in the product page I need to get data from both documents.

So, first I run

db.collection.find({doc_type:'item', item_id : 1001 });

to show product data, and then

 db.collection.find({doc_type:'company', name: "Acer"});

to get company data.

Is there any way to reduce these two calls to one and get data in a single result set?

If both documents are in the same collection, then yes you can - using the $or operator.

db.collection.find({
  $or: [
    {doc_type:'item', item_id : 1001},
    {doc_type:'company', name: "Acer"}
  ]
});

That will return both documents:

{ doc_type : 'company',
  name : 'Acer',
  type : 'Laptops',
  helpline : '1800-200-000'
}
{ doc_type : "item",
  item_id : 1001,
  price : 2000,
  discount : 20}
}

However, if you have two different types of record then it is probably best to store them in different collections.


What practices will help me write an efficient query against a large partitioned table? Table Properties:

Row Count: 276,027,782
Number of Partitions: 68

I'm looking to create a report off of one large table. I have a view which holds customer info. To return data of interest for 1 Client/Customer, it takes about 2 minutes. I have about 2200 Clients that I need to return data for. I'm joining on indexes and as far as I can tell I'm hitting the latest partition. As I'm returning data from 1/2017 onward. I'm looking for solutions or ideas that would speed up the code below:

select distinct
 vc.ClientID
,left(datestamp, 6) Month
,count(*) over (partition by left(datestamp, 6)) Cnt
,avg(delta) over (partition by left(datestamp, 6)) Perf
,percentile_cont (0.5) within group (order by delta) over (partition by left(datestamp, 6)) Median
,percentile_cont (0.90) within group (order by delta) over (partition by left(datestamp, 6)) Perc90
,percentile_cont (0.95) within group (order by delta) over (partition by left(datestamp, 6)) Perc95
,percentile_cont (0.99) within group (order by delta) over (partition by left(datestamp, 6)) Perc99
from schema.Monitor pm
    join Reporting.dbo.vwClient vc
        on pm.Institution_ID = vc.ClientID
            and vc.ActiveFlag = 1
            and vc.TestFlag = 0
            and vc.AccountType = 'Customer'

WHERE pm.LogInfo like ('StringFilter') -- no int or id value to filter on

--and pm.IID = @clientID -- filter for 1 customer
ORDER BY
 left(datestamp, 6) desc

Partitions do not really matter if you have proper indices. So, the practice is to learn your tools.

Partitions are not more effective than an index - they are there for managing data, i.e. fast delete (swapp out a partition in a millisecond instead of spending hours deleting 12 billion rows in it).

So, the question is: How do I make this query faster - without partition.

Start by deleting the distinct. Go on by actually givine a query plan and - with 2200 clients, please tell us about the server cluster you use to handle the load.


restore database using impdp full back I have new server and i installed oracle 10g same like production. and I have databump full backup using sys user. I need to restore on the new server.
I used the following command while database is not mounted but asks for directory parameter.

Code:

impdp full=y direcotry=/home/oracle/rmanscripts/dumbbackup dumpfile=sysfull.dmp logfile=full_db.log.

please share me the steps to restore the database to a new server!

Data Pump uses directory objects. Read this:

Default Locations for Dump, Log, and SQL Files

First create the directory object as:

create directory dump_dir as '/home/oracle/rmanscripts/dumbbackup';

Then you can use that in your impdp command:

impdp full=y direcotry=dump_dir dumpfile=sysfull.dmp logfile=full_db.log

Also:

  while database is not mounted

By this I hope you mean it is open. Data Pump needs a working database, even if it is empty.


Understanding postgres reason for picking this query plan I ran a query:

select * from orderline where orderid = 12345 and status > 0

that gave me the following plan:

Bitmap Heap Scan on orderline  (cost=286370.00..531410.78 rows=76372 width=155)
  Recheck Cond: ((orderid = 12081976) AND (itemstatus > 0))
  ->  BitmapAnd  (cost=286370.00..286370.00 rows=76372 width=0)
        ->  Bitmap Index Scan on orderline_orderid_index  (cost=0.00..4239.65 rows=229117 width=0)
              Index Cond: (orderid = 12081976)
        ->  Bitmap Index Scan on orderline_status  (cost=0.00..282091.91 rows=15274484 width=0)
              Index Cond: (status > 0)

I wanted to understand why postgres decided to run two separate index scans rather than the filter on the forign key (orderline_orderid - which returns about 5 rows out of millions) then filtering status from these results.

How can I work out what I've set up wrong here? (I'm assuming index fragmentation or maybe status shouldn't have an index?)

EDIT: I tried some of the things in the comments and the discovered the following:

I thought the cause of the issue was the index as I removed it and the problem went away:

Bitmap Heap Scan on orderline  (cost=4258.75..597209.93 rows=76372 width=155)
  Recheck Cond: (orderid = 12081976)
  Filter: (status > 0)
  ->  Bitmap Index Scan on orderline_orderid_index  (cost=0.00..4239.65 rows=229117 width=0)
        Index Cond: (orderid = 12081976)

However, I removed and added then index and then ran analyse and the the follow occured:

Index Scan using orderline_orderid_index on orderline  (cost=0.00..14.87 rows=4 width=63)
  Index Cond: (orderid = 12085242)
  Filter: (status > 0)

So is this because I rebuilt the index, or because I ran analyse?

It looks like Postgres had no idea how your values are distributed. It guessed that there's 229117 rows with orderid = 12081976 when it really was 5 of them. It has then chosen a plan which was optimal for 229117 rows.

When you ran analyze it was able to generate and save statistics about distribution of values in your table. And then it knew that there's about 4 rows with this orderid. And this allowed it to pick a better plan.

So it is analyze that caused this. Remember to run it after large changes to database data — for example after importing from external source.

Also — when you analyze your plans try to use explain analyze [query]; instead of just explain [query];. You can paste it to explain.depesz.com for easier to understand format.


Modify all tables in a database with a single command Is there a single or a one line command to modify all tables within a database. I would like to issue this command in every table within a database:

ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8;

My objective is to modify the charset from latin1 to utf8 to all tables.

UPDATE: RDBMS is MySQL

No, there is no such command. But what you can do is write a quick query to generate the SQL for you like so:

USE INFORMATION_SCHEMA;
SELECT
CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` CONVERT TO CHARACTER SET UTF8;")
AS MySQLCMD FROM TABLES
WHERE TABLE_SCHEMA = "your_schema_goes_here";

Then you can run the output from this to do what you need.

Sources:

http://forums.mysql.com/read.php?20,244395,244421#msg-244421


PL/SQL cursors and records I am a new to Pl/sql and I need to learn to write simple pl/sql scripts.
Is there any difference between cursors and records in PL/sql?
So far I find them both similar.

new to PL SQL here, and wanted to help with what i know...

A record is similar to a table row, can hold one value for each column at a time.
cursors are like "tables" they may hold multiple rows (records if you will).

this is one way how you can declare a record:

DECLARE
TYPE myRecType
IS
RECORD
(
  [COLUMN NAME] [DATA TYPE],
  .
  ..
  );

Cursors can be explicit or implicit.
Implicit cursors are created each time you run a query, and have some attributes that may give you an idea of what happened after your query is ran.
e.g.

SQL%FOUND will return either TRUE or FALSE if your DML statement affects at least 1 record.

SQL%ROWCOUNT will return the number of rows afected by your DML statement...

Explicit cursors on the other hand, are needed for advanced data manipulation, you can create your own cursor by supplying a query, and you can iterate through that cursor to do more data manipulations

simple syntax to declare a cursor with a SELECT query and loop through the results:

Declare
  cursor Mycursor is
    SELECT
      ColA,
      ColB,
      Colc
    FROM MyTable;
Begin

   -- Iterate using a for loop
  for X in Mycursor loop
    --print Mycursor.ColA
    -- do whatever you want to do with your data
  end loop;
end;


Throw an error from the trigger? When trigger returns NULL, row is not inserted, but result of the operation is still reported as successful (INSERT 0 0). And in my opinion this is not logical, as one would probably want to handle this properly. I wonder if there's a way to throw an actual error from the trigger?

If the trigger function is written in PL/pgSQL, then you can raise errors with RAISE:

CREATE FUNCTION my_trigger() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF random() < 0.01 THEN
        RAISE EXCEPTION 'internal error';
    END IF;
    RETURN NEW;
END;
$$;


Obtain virtual column formula with SQL We want to update the condition of a virtual column and to minimize error posibilites we want to extract the exact function working today to add new parameters to take into account.

I've firstly test with desc tablename but doesn't work, only shows column types.

This information can be found in the DATA_DEFAULT column of [DBA|ALL|USER]_TAB_COLUMNS.

Example:

SQL>  create table t1(c1 number, c2 number generated always as (abs(ceil(c1)*2)) virtual);

Table created.

SQL> select column_name, data_default from user_tab_columns where table_name = 'T1';

COLUMN_NAM DATA_DEFAULT
---------- --------------------
C1
C2         ABS(CEIL("C1")*2)


Resolving ORA-01033 "initialization or shutdown in progress" I followed the instructions at http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php to create a database export.

I used:

expdp scott/tiger@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

to create the export. However I get this error message:

UDE-00008: operation generated ORACLE error 12154
ORA-01033: ORACLE initialization or shutdown in progress

How do I resolve this issue?

First:

sqlplus /nolog

Then:

connect / @bdlocal as sysdba

(Alternatively, use sqlplus / as sysdba instead of the above two steps.)

Check the status (I guess it will tell you anything but STARTUP):

select status from v$instance;

Then turn it completly off:

shutdown immediate;

Then start it:

startup;

If you get errors starting up, check the alert log and your favorite search engine (searching for the ORA-xxxx alert number is a good place to start). If you can't figure it out, you can ask a question about that here (including the relevant part of the alert log or any other errors you see).


Management Studio licensing issues We have a licensed SQL Server 2012 Standard Edition instance, that for some reason keeps getting license expiration messages in Management Studio.  Sometimes it gets this message:

  Your Microsoft SQK Server Management Studio evaluation period has expired.

  You can get a key to extend your trial by registering this copy of Microsoft     Management Studio online.  You may also purchase a key to activate the product.

Other times, it says a key has been entered:

  A valid product key has been entered for Microsoft SQL Server Management Studio.

Still other times, SSMS just closes, prompting you to save files.

This machine has had various versions of Visual Studio installed and uninstalled by a developer who once had administrative rights, and I suspect that's what's got this all messed up.  However, I've removed Visual Studio, and uninstalled SQL all the way to the ground, and re-installed, but the message doesn't go away.

Has anyone experienced this, and is it worth trying to fix?  Or should I install on a new platform and migrate off this machine?

Just uninstall client tools completely, using Control Panel -> Programs and Features -> SQL Server 2012 -> Change. This should launch the setup program and allow you to uninstall the management tools. If the Control Panel method doesn't work, I have a more brute force way of doing it, just make sure you only remove those CLSIDs that relate to SSMS.

Then grab the Express edition of 2012 SP2 (x64 | x86), which has the same functionality as the version you had installed, but without the licensing and the expiration. For more details (or links to more modern versions of SSMS), see this answer.

There are more cumbersome workarounds listed here and here (yes, they say 2008, but they still apply). I think uninstall/reinstall is cleaner.


How to create a master key on the master database?What are the roles required to GRANT to the login and user of the master db for the same? I am trying to create a master key on a master database of my azure sql datawarehouse. But my user does not have required permissions to do so. What are the roles need to be assigned/granted to the user or login of the azure sql datawarehouse?
I was able to grant control access using GUI of SSMS to the user of sql server instance on-prem. And also I was able to create a master key with the help of that role on master database of that sql server.But there's no direct provision to provide such access to the user on azure sql warehouse using GUI of SSMS.
Is there any script to give permissions to user of master db so that I am able to create a master key on it?If yes, then what are those scripts and their sequence?I have really tried hard to find answers to these questions. Please help me to find the answers

I don't believe it's possible, or useful to CREATE MASTER KEY in the master database in Azure SQL Database/Azure Synapse Analytics (formerly SQL Data Warehouse).  And the error you're seeing is a bit misleading, as you get that error whenever you attempt an operation in master that is allowed in normal SQL Server, but prohibited in Azure SQL Database.

The Database Master Key (DMK) in the master database is used to protect Certificates and Asymmetric Keys in master, but you can't create either of those in the master database in Azure SQL Database.  And in normal SQL Server the DMK in master protects the Database Encryption Keys (DEK) for Transparent Database Encryption (TDE) of user databases.

The encryption hierarchy for TDE in Azure SQL Database works differently:

  Transparent data encryption encrypts the storage of an entire database
  by using a symmetric key called the database encryption key. This
  database encryption key is protected by the transparent data
  encryption protector. The protector is either a service-managed
  certificate (service-managed transparent data encryption) or an
  asymmetric key stored in Azure Key Vault (Bring Your Own Key). You set
  the transparent data encryption protector at the server level for
  Azure SQL Database and Data Warehouse, and instance level for Azure
  SQL Managed Instance.


SQL Server 2016 SP1 Managed Backup to blob storage I'm trying to configured managed backup for SQL Server 2016 SP1 to Azure's new cool blob storage and am only receiving errors. This is for a VM running in Azure. The documentation I've found is conflicting, but here's what I understand so far:

Cool blob storage only supports block blobs, not page blobs (reference)
"SQL Server Managed Backup to Microsoft Azure uses the Backup to Block Blob feature" (reference)
"Only a SAS URL is supported" by the managed_backup.sp_backup_config_basic procedure (reference)

With that in mind, I created a new cool blob storage account and a container called sqlbackups. I then created a shared access signature for the container with full permissions.

With this data, I then attempt to set the default backup configuration for the server:

EXEC msdb.managed_backup.sp_backup_config_basic
@enable_backup=1,
@container_url='https://mycoolblobstorage.blob.core.windows.net/sqlbackups?st=...',
@retention_days=30

Unfortunately, this just results in an error of:

  SQL Server Managed Backup to Microsoft Azure cannot configure the default backup settings for the SQLServer instance because the container URL was invalid. It is also possible that your SAS credential is invalid.

I've regenerated the SAS token several times without success. I've also tried creating a credential, which I think is needed only for the legacy page blobs method.

CREATE CREDENTIAL
[https://mycoolblobstorage.blob.core.windows.net/sqlbackups]
WITH IDENTITY = 'Shared Access Signature',
SECRET = '?st=...'

That seems to have no effect. Can someone help me get this working?

While Microsoft corrects this bug in SQL 2016 a workaround is the following:

Create a credential to your azure storage but use an Access Key as password instead of a SAS (this was the procedure for SQL 2014).
Enable the Managed Backup (it will work correctly)

At this point MB is enabled but backups created by it (and any manual backup using the same storage) will fail because of the credential being created in 2014 mode, so

Modify the credential changing the Password to the SAS.

Now backups will start to run.
If you need to disable or configure MB again you will have to switch to Access Key and then back again to SAS.


What operating systems are certified for Oracle 11gr2 RAC? How can this information be found?

My Oracle Support has a certify tab which lists current certifications, including the one you are looking for. Here is how:

Select "Oracle Database" as the Product.
Select the release "11.2.0.1".
Click the link "Check certifications with another product.
Select the "Product Category" radio button.
In the Category drop down select "Operating Systems".


can we use a mongodb 3.0 and mongodb 2.6 in same replica set? i know that replicas using different storage engines can coexist in a replica set on MongoDB 3.0  but can we add a MongoDB 3.0 replica to a replica set that consists of MongoDB 2.6 instances ?

That would be the easiest way to port to "MongoDB 3.0 + wiredTiger" instances from our MongoDB 2.6 instances

You can perform a rolling upgrade of your replica set from MongoDB 2.6 to 3.0.

While a mixed version 2.6/3.0 replica set is supported for the purposes of upgrading, you should not run with this configuration for longer than is required.

You should also read the 3.0 upgrade and compatibility change notes carefully, as important considerations will be included there. As with any upgrade, you should test in a development/staging environment before prior to your production release.

A few particular points to be aware of:

Before enabling the new WiredTiger storage engine, ensure that all replica set/sharded cluster members are running at least MongoDB version 2.6.8, and preferably version 3.0.0 or newer.
Once upgraded to MongoDB 3.0, you cannot downgrade to a version lower than 2.6.5
If you upgrade to 3.0 and have run authSchemaUpgrade, you cannot downgrade to 2.6 without disabling --auth or restoring a pre-upgrade backup. The authSchemaUpgrade discards MONGODB-CR authentication credentials used in MongoDB 2.6.


How to point SQL Server Python to a specific Python install? SQL Server 2017 installs an instance of Python to "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES" (documentation link) that it uses for its Python calls. This is installed with specific packages that SQL Server needs to communicate with Python.

Is there documentation on these packages, where to pull them (pip?), and how to point SQL Server to a different install of Python on the same server?

You cannot point SQL Server to a different Python installation; per the docs you linked:

  SQL Server does not modify the Python executables, but you must use the version of Python installed by Setup because that version is the one that the proprietary packages are built and tested on. For a list of packages supported by the Anaconda distribution, see the Continuum analytics site: Anaconda package list.

The Python 3.5 runtime and interpreter are available from this site.


When was Torn Page Detection and Checksum introduced to SQL Server and what are the upgrade behaviors? There are two different options in modern SQL Server for page verify; being Torn Page Detection and Checksum. None is also of course an option.

I believe Checksum was introduced in SQL Server 2005 and that upgrading or restoring a DB from a prior version would maintain its previous page verify method. i.e. there was no implicit upgrade.

The problem involved is that we have a production database that went into production using SQL Server 2000 and has since moved to a SQL Server 2008 R2 server. Page Verify is set to None when I had been expecting it to be Torn Page Detection. Going back this amount of time we seem to think the DB was originally developed in SQL Server 7.0 then migrated to SQL Server 2000 and this may explain the observed result.

I was wondering when Torn Page Detection and Checksum became a feature of SQL Server, and how they behaved when migrated or upgraded to newer versions.

Edit: Summing up some of the answers:

There is a little discrpenacy over some of the dates for when Torn Page Detection came into SQL Server.
Link 1: http://support.microsoft.com/kb/230785
Link 2: http://technet.microsoft.com/en-us/library/aa337525(v=sql.90).aspx

The first link indicates SQL 7.0 and the second SQL2000. I tend to put my faith in the SQL7.0 suggestion and that link two was confused over it being off by default in SQL7.0 and on by default in SQL2000.

In SQL Server 2000, if you want to identify corrupt pages, then the database option TORN_PAGE_DETECTION should be set to TRUE.
But in SQL 2005 and up, a new setting PAGE_VERIFY replaced the old TORN_PAGE_DETECTION which allows to choose from two different types of page verification :  TORN_PAGE_DETECTION and CHECKSUM.
Now the question comes which one to set  - TORN_PAGE_DETECTION or CHECKSUM ?
TORN_PAGE_DETECTION - writes a bit for every 512 bytes in a page allowing you to detect when a page was not successfully written to disk. The catch is that it wont tell you if the data stored in those 512 byes is actually correct or not due to the fact that couple of bytes may have been written incorrectly.
CHECKSUM - will caluclate a checksum of the page both when a page is written and when a page is read, assuming it has checksum on it.

The SQL Server computes the checksum based on the bit pattern on the page, stores it in the page header and then issues an I/O to write the page.  When the SQL Server reads the page, it re-computes the checksum using the same logic and then compares it with the value available in the page header. If the checksum value matches then it is assumes the page did not get corrupted during the write-read cycle.
Since the cost of computing the checksum is incurred on each page read and write, it can add to the CPU overhead and can possibly impact the throughput of your workload. Another thing to keep in mind is that the checksum is not unique for a specific bit pattern on the page. Two pages can possibly map to the same checksum value. So there is remote possibility that page corruption may go undetected.

Reference : Checksum in SQL2005
To specifically answer your questions :

I believe Checksum was introduced in SQL2005 and that upgrading or restoring a DB from a prior version would maintain it's previous page verify method. i.e. there was no implicit upgrade.

Yes CHECKSUM was introduced in SQL Server 2005 and is the DEFAULT. When you upgrade from 2000 to 2005, you have to explicitly change the database option Page Verify to use CHECKSUM.
If you restore the database already created on sql 2005 to another server running sql 2005, you dont have to set it. It will persist to what ever you have set the Page Verify option to.

I've not succeeded in researching when Torn Page Detection came in

From: http://support.microsoft.com/kb/230785

Versions of SQL Server earlier than 7.0
Versions of SQL Server earlier than 7.0 did not provide log parity or
torn bit detection facilities. In fact, those versions can write the
same log page multiple times until the log records fill the 2-KB log
page. This can expose transactions that have successfully committed.
If the log page is being rewritten during a failure, a sector with the
committed transaction may not get rewritten properly.

Thus, TORN_PAGE_DETECTION has been around since SQL Server 7.0. Even then, the default was that it was not enabled (same link).

Note Torn page detection is not enabled by default in SQL Server 7.0. See sp_dboption for how to enable the detection on your system.

Therefore, if the database was developed against a 7.0 instance and was subsequently upgraded, it would have upgraded the with the extant PAGE VERIFY option of NONE (as @ThomasStringer noted in his answer).

Edit : 09/24/2013  To improve the answer :
Refering to my SQL Server Internal notes from SQLSkills, I found that using a page dump, you can verify if torn bit detection - TORN_PAGE_DETECTION or CHECKSUM was enabled or not :
use database_name -- change here for your database !!
checkpoint
go
dbcc traceon (3604)   -- send output to screen
go
dbcc page (dbaalert, 1,1,0)
dbcc traceoff (3604)  -- turn off the trace flag
go

m_tornBits : This holds either the page checksum or the bits that were displaced by the torn-page protection bits – depending on what form of page protection is turnde on for the database.
Note: I dont have any older sql server versions running. Below is confirmed from sql server 2000 and up. If you have a 7.0 or 6.5 running around, you can confirm it as well :-)


Optimizing syncing of large dataset between 2 servers with SSIS I have two DBs on different servers, Server A and Server B. Both are SQL Servers. I need to keep second DB in sync with the first one. DB schema and tables are identical, but Server A periodically creates new records that are missing on Server B. I decided to use SQL Server Integration Services, which is included in SSDT tools.
The simplest way to sync Server B with Server A is by creating SSIS project in Visual Studio that will generate Package.dtsx.
Here is what the package does currently

create OLE DB source referencing Server A
create OLE DB destination referencing Server B
pick specific table that needs to be synced
create column mapping for this table between Server A and Server B
in SSMS, add SQL Server Job that will run Package.dtsx every 15 minutes

This package seems to download all the records from Server A and tries to insert them on Server B.
The issues

Some records may already exist on Server B. Is there a way to insert only records that are missing on Server B, to prevent a bunch of errors in the log about duplicated primary keys?
Server A may have a lot of records. Is there a way to get the difference between servers BEFORE synchronization starts, maybe there is some action that can compare records by IDs and download only the difference between servers?

You want to add a "lookup" operator in your SSIS pacakage.
this package will "check" for some columns (your PK for example) and you will then be able to take action on only the rows that are missing.


Response On Database Failover Let say, that I have principal server named P, and mirror server named M. I've got 7 databases that are in server P, and 7 mirror databases on server M. Whole engine works fine when we are talking about server failover. But sometimes I've got events, when only one database is broken ( for example data consistency problem ). Then this one database is switched to mirror server, and rest stay on principal server. What's wrong with this solution ?

On databases there are procedures and views, that use full-name object calling, for example:

SELECT * FROM DATABASE1.dbo.TABLE1
  LEFT JOIN DATABASE2.dbo.TABLE2

And now, when only DATABASE1 is switched, and when SP from DATABASE1 tries to select TABLE2 from DATABASE2 - it is impossible. DATABASE2 is in restoring mode.

My idea is to switch all database to MIRROR server whenever one database is broken. Is there a way to detect database failover ? (maybe trigger) Maybe You've got better ideas to solve this problem ?

Glen Berry has some example code on his blog here - http://sqlserverperformance.wordpress.com/2012/03/27/sql-server-database-mirroring-tips-and-tricks-part-3/ - which aims to fix this problem. It works by monitoring the tagged databases, and if it notices one of them has failed across to the mirror it will fail the others across. I had a play with it after a similar question on SE a couple of weeks ago and it does the job, though whether it's as fast/reliable as clustering is something you'll have to evaluate for yourself.

Grouping mirrored databases into a 'group' which fails over together is a feature of Availability Groups in SQL Server 2012.


Autogrowth - Percentage Use? Is it best not to use Percentage file growth for autogrowth settings?

The below recommends using percent value growth for databases under 500GB but is this recommended?

http://performance-expert.blogspot.ie/2012/06/tuning-autogrow-settings-of-sql-server.html

  If disk space is limited or databases cannot be sized, you should configure the >autogrowth value to a fixed percentage. For example, configure the autogrowth value to >10 percent for databases under 500 GB and to a fixed number of megabytes if a database >exceeds 500 GB.

Thanks!

UPDATE: Below I have added a snapshot of Autogrowth on some databases. Any recommendations on this?

Also what is best way to predict database size?

There are three answers and some great advice is included in each answer in part. That said I wanted to add a bit more from another perspective.

Talking about database files here...

Right Sizing Is Best

As most have said or hinted at - it is far better to "right-size" your database for current and future needs. TomTom is right to point out that there is a performance hit there, but KookieMonster is also right to point out that Instant File Initialization (IFI because I'm lazy) helps that.. Even with IFI - I prefer to find out about the sizing needs of my databases at their deployment and in their possible futures as best as I can. And then I try to size for that plus an "overage" based on what I know about the project/about the potential to grow.... Note: This is not an exact science, and there will be times (many times) that you get this wrong, that's okay.. Autogrowth is a good thing.. I just prefer to try and stay on top of it.

Why? Because I don't want to have that awkward conversation with a SAN admin when I start running out of space. And I don't want a SAN admin to have to make that choice to do some ugly things behind the scenes to give me space. I like to preallocate, watch my free space used in the database over time and use

Auto-Growth As an Emergency Helper

Autogrowth for data files is not evil, especially with IFI. But I like to monitor space used inside of a DB file and use the ability to automatically grow as a band-aid. This way I stay on top of the growth of the databases I'm responsible for as a DBA. Your monitoring regiment should include checking for used space and looking at that.

What Setting Is Best?

So with this in mind, it almost shouldn't matter what you use because you are helping manage the growth yourself. If you see your space used curve steeper than you like, you can look at your calculations and preallocate more space in one big growth. Even still, I am not a big fan of percentages. To me it is non-deterministic and a sign that someone isn't managing the growth in a lot of situations. I just prefer to have that level of control, and I pick a space that I feel is appropriate based on the needs of the database.

Big Caveat

"It Depends" - if your database is small and likely not going to be a big boon on disk ever then I wouldn't cry about keeping it at a percentage or even paying a bit less attention to it's setting. If I go to a client and see a 750GB database still at default growth percentages and no log file management, I cry a little inside. If I go to a client with a 1.25GB database that's been around for 3 years and still set to the defaults? I mention it in my report, but I have the whole "There are best practices.. And then there are situations where you are fine either way" conversation with them.. Now if IFI was disabled, and they had a valid reason to keep it disabled? I'd still probably say something with more seriousness to the smaller database, and I'd really say something like "Let's go crazy and grow this 4 times to preallocate some space" and risk "wasting" 2 to 3 GB of their disk space.

Transaction Log Files

Are a different matter. Log files do not/cannot take advantage of IFI. And they don't like autogrowth because of a little thing called VLF fragmentation (that link takes you to a lot more links all about the topic). I prefer to right size my transaction logs, watch them, and reevaluate what that right size is. I keep autogrowth on (a production transaction log file filling up also makes me cry inside.. actually outside too). And I right size them in "chunks" as per the guidance on the link provided for VLF.


You must concatenate those values. The best way is to use FOR XML PATH.

Query (SQL Fiddle):

SELECT [TicketID], [Type]
    , [Code] = stuff(
        (
            SELECT ', '+ [Code]
            FROM data d2
            WHERE [TicketID] = d1.[TicketID] AND [Type] = d1.[Type]
            FOR XML PATH('')
        ), 1, 1, ''
    )
FROM (SELECT DISTINCT [TicketID], [Type] FROM data) d1

Output:

TicketID                                Type    Code
44899B82-41C9-4020-9C2B-007C6000D283    ARTI    22150012, 22150017
44899B82-41C9-4020-9C2B-007C6000D283    CASH    NULL
76C6D696-55E7-4CA5-A1AA-0F0DB446D9A4    ARTI    22150018, 22150012, 22150011
76C6D696-55E7-4CA5-A1AA-0F0DB446D9A4    CASH    NULL

If [TicketID] and [Type] are in table Line and [Code] in table Article, LEFT JOIN must be used (SQL Fiddle):

SELECT [TicketID], [Type]
    , [Code] = stuff(
        (
            SELECT ', '+ [Code]
            FROM Line l2
            LEFT JOIN Article a ON a.ID = l2.ArticleId
            WHERE [TicketID] = l1.[TicketID] AND [Type] = l1.[Type]
            FOR XML PATH('')
        ), 1, 1, ''
    )
FROM (SELECT [TicketID], [Type] FROM Line) l1


What is the most compact way to calculate and then Update a maximum value for each day's data? I have a database that stores records for each bundle of material.  A sample is taken from each bundle and sent out for assaying (for lead and sulfur).  I receive the assay data and write the assay value to the record.  I also take the maximum value for each assays and assign them to the day's "sample" record.

Generally, I only receive a single days assays at one time.  However, on occasion, I received the assays for multiple days (if there was a re-assay or assays were delayed).  When this happens, I end up assigning the maximum for all included days instead of for each day separately.

What is a better way to set up the following query to prevent one day's assays from being included as a maximum value for another day?

UPDATE pOut
SET
pOut.lead_ppm = (SELECT MAX(p1.lead_ppm)
 FROM production p1,  assay_temp t1
 WHERE (p1.tank = 'B' or p1.tank = 'C')
 AND p1.scheduled_pull_date = t1.formatted_date),
pOut.sulfur_ppm = (SELECT MAX(p2.sulfur_ppm)
 FROM production p2,  assay_temp t2
 WHERE (p2.tank = 'B' or p2.tank = 'C')
 AND p2.scheduled_pull_date = t2.formatted_date)
FROM production pOut, assay_temp tOut
WHERE pOut.tank = 'S'
AND pOut.cell_num = 1
AND pOut.scheduled_pull_date = tOut.formatted_date

Code has been updated to use differently named aliases and to include the needed parenthesis.  I believe now I will receive a single maximum for lead and for sulfur, likely from the earliest date.  I need to improve this so that I get a maximum value for each day with assays, and write that value to the proper day's "sample" record.

I am not entirely sure but if the aggregated results are supposed to be per date and the date is production.scheduled_pull_date, then perhaps this:

UPDATE
  pOut
SET
  pOut.lead_ppm = (
    SELECT
      MAX(p1.lead_ppm)
    FROM
      dbo.production AS p1
    WHERE
       (p1.tank = 'B' or p1.tank = 'C')
       AND p1.scheduled_pull_date = pOut.scheduled_pull_date
  ),
  pOut.sulfur_ppm = (
    SELECT
      MAX(p2.sulfur_ppm)
    FROM
      dbo.production AS p2
    WHERE
      (p2.tank = 'B' or p2.tank = 'C')
      AND p2.scheduled_pull_date = pOut.scheduled_pull_date
  )
FROM
  dbo.production AS pOut,
  dbo.assay_temp AS tOut
WHERE
  pOut.tank = 'S'
  AND pOut.cell_num = 1
  AND pOut.scheduled_pull_date = tOut.formatted_date
;

Although I would also rewrite the join using the contemporary syntax:

FROM
  dbo.production AS pOut
  INNER JOIN dbo.assay_temp AS tOut ON pOut.scheduled_pull_date = tOut.formatted_date
WHERE
  pOut.tank = 'S'
  AND pOut.cell_num = 1

The assay_temp doesn't seem needed in the subqueries at all, and instead of matching scheduled_pull_date against formatted_date the subqueries should correlate with the outer query. Since you want aggregations per date, it seemed to me the subqueries should match against pOut.scheduled_pull_date.


Shrinking SQL Server 2000 database I apologize for the long question. I am trying to learn a lot on the fly as I have become the defacto SQL Admin. I have been tasked with moving a production database to a new server. My goal is to take a full backup and restore the database on new server. My question is focused around the concept of shrinking a database.

I have a production database almost 3 terabytes in size. There are 5 tables no longer needed. Combined the 5 tables account for almost 700GB. My thought process was to remove the 5 tables to save myself a considerable amount of space (700gb) before taking a full backup. I am learning real quick it is not as easy as it sounds. I removed the 5 tables using the drop command but then learned deleting tables will not actually free up any space. In order to free up space I must shrink the database. This is where I run into conflicting opinions. I am reading you do not want to shrink a database unless you have to.

Can someone please point me in the right direction. Now that I have deleted the tables should I shrink the database before I perform the full backup? Will shrinking the database have a negative effect?

Let's go through a few points.

1- Why Even Shrink? What Are The Benefits And Cons?

First is the 700GB extra space causing any issues?  If the DB is already at 2.3TBs, it will probably continue to grow, if so, then consider just leaving the space.  In fact, you WANT free space in your DB if it's still growing!  You don't want it to expand often, as that causes physical file fragmentation and causes blocking/performance issues when it's growing unless you have Instant File Init. enabled, then the blocking/locking issues are largely negated.

When you take backups those free 700GB are not actually copied in the backup, just pointers to empty pages which will then be populated on restores, thus doing this will not reduce backup time, restore time, backup space, but will take up restore space.  The only time it'll affect restore time is if you do not have instant file initialization enabled and your SQL Server Service user is not an administrator.

Reindexing again will increase the free space for it to hold temp info and such, but again, if you end up growing and using that free space then there's no problem.
Perhaps if you are restoring to a smaller dev server that doesn't have as much space, then this would be warrented.

2-To Shrink Or Not To Shrink

Now assuming you absolutely need to reduce your DB size, then read Paul Randal's post on moving to a different filegroup instead of shrinking.  This ensures that your shrink does not create additional free space.  If you have a clustered key it also reorders the base clustered index as well.


Is this a good way to store XML data in SQL SERVER 2008? I'm doing a feasibility study for storing data in the following structure with SQL SERVER 2008:

-MYTABLE
|ID|RECORDS|BRANCH_OFFICE|MONTH|YEAR

The RECORDS column has the XML data type and it will look like:

<visits>
  <visit id="000112233">
    <costumer>Mr. One Costumer</costumer>
    <date>2011-02-10</date>
    <employee>MAT01234</employee>
  </visit>
  <visit id="000112234">
    <costumer>Mr. Another Costumer</costumer>
    <date>2011-02-12</date>
    <employee>MAT01235</employee>
  </visit>
  <visit id="000112235">
    <costumer>Mr. Some Costumer</costumer>
    <date>2011-02-12</date>
    <employee>MAT01234</employee>
  </visit>
  <visit id="000112236">
    <costumer>Mr. Some Costumer</costumer>
    <date>2011-02-15</date>
    <employee>MAT01235</employee>
  </visit>
</visits>

I'm intending to query the xml column with xquery, is this a good way to store these data?
If i want to get the list of visits occurring in 2011-10, adding a tag with the difference of days between the "date" tag and now, how could it be?

...is this a good way to store these data?

No, this is probably the worst way to store this data if you want to query it effectively. You need to study some database normalisation examples before you go any further with this

This is a very quick sketch up of a possible schema that could be used. Incomplete and untested but it should be clear how this maps to your XML fragment.

CREATE TABLE dbo.Customer
(
    CustomerId INT NOT NULL
    , Title VARCHAR(20)
    , FirstName VARCHAR(100)
    , LastName VARCHAR(100)
)

ALTER TABLE dbo.Customer
ADD CONSTRAINT PK_Customer PRIMARY KEY (CustomerId ASC)

CREATE TABLE dbo.Employee
(
    EmployeeNumber VARCHAR(10) NOT NULL
    , Title VARCHAR(20)
    , FirstName VARCHAR(100)
    , LastName VARCHAR(100)
)

ALTER TABLE dbo.Employee
ADD CONSTRAINT PK_Employee PRIMARY KEY (EmployeeNumber ASC)

CREATE TABLE dbo.Visit
(
    VisitId INT NOT NULL
    , VisitDate DATETIME
    , CustomerId INT
    , EmployeeNumber VARCHAR(10)
)

ALTER TABLE dbo.Visit
ADD CONSTRAINT PK_Visit PRIMARY KEY (VisitId ASC)

ALTER TABLE dbo.Visit WITH CHECK ADD CONSTRAINT [FK_Visit_Customer] FOREIGN KEY(CustomerId)
REFERENCES dbo.Customer (CustomerId)

ALTER TABLE dbo.Visit WITH CHECK ADD CONSTRAINT [FK_Visit_Employee] FOREIGN KEY(EmployeeNumber)
REFERENCES dbo.Employee (EmployeeNumber)


Migrated data with Special Characters Counted as 2 Characters in DB2 Linux We are currently migrating data from DB2 10.1 AIX to DB2 11.1 Linux. Since we are using some features like versioning/history tables and rowchangetimestamps, db2move is not an option for some tables. As a result we do manual export and load for some tables.

The problem arrises for data having special character such as "Ñ".

  Sample Data

  PIÑATA

The AIX DB2 10.1 Counts the length for this as 6 characters and it fits fine to VARCHAR(6) columns. But when I load this to DB2 Linux 11.1, this is counted as 7 characters, thus data is truncated.

I tried CASTING this as VARCHAR(6) but the data is still truncated. I hope someone can point me to a possible solution.

Every character needs 1 byte in a single-byte database, but it may need from 1 to 4 bytes in a UTF-8 database.
When you specify VARCHAR(N) for a column, N is defined in string units.
OCTETS means bytes, and CODEUNITS32 means characters.
In your case N probably is OCTETS, and since Ñ character requires 2 bytes (OCTETS), then whole string length in the UTF-8 database is 7.
Character strings


Working of window functions and idea window size for window function I am not able to understand the concept of window functions. How exactly they work and what are the pros and cons of such technique?

I have read that using limit and offset is slow but its still used. So decision between limit/offset and window functions depend upon size of data to get from the db or window functions should always be used?

What is ideal window size to get maximum performance from this? Can it be the case where if number of rows to get is less then simply use limit/offset?

My use case basically requires sorting data on date and then bring the latest data first. How window functions fit in this scenario.

The elementary difference is that window functions are applied to all rows in a result set to compute additional columns after the rest of the result set has been determined. No row is dropped. They are available since PostgreSQL 8.4.

The LIMIT and OFFSET clauses of the SELECT command on the other hand do not compute additional columns. They just pick a certain "window" of rows from the result set (in cooperation with the ORDER BY clause) and discard the rest. Have been there like for ever.

While certain tasks can be tackled with either of these tools, they are very different in nature.

For your simple task

  sorting data on date and then bring the latest data first

you don't need either of them. Just add:

ORDER BY date_col DESC

According to your comment, you would need:

SELECT col1, col2
FROM   tbl
ORDER  BY date_col DESC
LIMIT  100   -- 100 latest rows
OFFSET 0;    -- just noise, but may be easier to code

Retrieve more:

...
LIMIT  100
OFFSET 100;  -- next page of 100 rows ...

Be sure to have an index on date_col in either case!


Switching to Simple Recovery - shrinking transaction logs Standard disclaimer: I am an "involuntary DBA" (a nice phrase I saw in this article) and have done lots and lots and lots and lots and lots of reading on this subject, but am still confused/concerned...

I need to change the recovery model of several SQL Server 2014 production databases from Full to Simple.

The databases are currently set to Full Recovery and were being backed-up using SqlBackupAndFTP, which would do a daily full backup, with 3 differential backups and 20 transaction backups each day.  This was working fine until we had huge problems with the company providing FTP-based storage.

About 3 months ago I switched to using Attix5 which is working well - however, I've now discovered that due to the way the software works the transaction logs are as big as the data files (in one case over 14Gb against a 13Gb data file).

I have been recommended to switch the recovery model to Simple, so that the transaction log doesn't grow.

I know that having Simple Recovery will not provide point-in-time restoration - that is absolutely fine, as full backups are taking place hourly and we are "happy to lose" anything that might have happened in the previous 1-59 minutes.

My understanding is that I should set the recovery model to Simple and let the backup take place, which will set a checkpoint in the transaction log allowing for the space to be re-used.

However, I really need to shrink the transaction log to a sensible size (so that it's not taking up so much of the backup space), but I keep reading that shrinking is a bad idea.  Or am I getting the wrong end of the stick?

If I shrink the transaction log down to around 25Mb (which should easily be big enough for a single hourly set of transaction) using the following command, is that enough?

DBCC SHRINKFILE('log file name',25)

(I'm really sorry to be asking something that appears to have been asked a zillion times before, but this is not something I can get wrong.)

First of all I'd like to again point out that, in most cases, SIMPLE recovery is not recommended for a production database.  However, if that is something you wish to change the best way I've found to shrink the LOG file, after you put the database into SIMPLE recovery model, is as follows.  This will not only accomplish releasing free space from the log, but also shrinking the file down to its smallest possible size.

As you stated, shrinking also is not normally a good practice, but there are times when it is warranted.  There are many articles pro/con about it, and I agree with those that will tell you to never use DBCC SHRINKDATABASE; always use DBCC SHRINKFILE if you absolutely have to do a shrink.

However, the log file does indeed have to have a reasonable size to it, even in the SIMPLE recovery model, as that is where information is kept during transactions and long running SPIDs need more of a log file.  Among many reasons, one of those is for roll-back in the event of a problem.  As I stated above, these scripts will reduce your log file to its absolute smallest size possible, but because you need at least some log space you should follow up by regrowing it manually and also make sure your "auto grow" is set to a reasonable number; arguably, a percentage is to always be avoided.  Shrinking the log to its smallest size and the regrowing it manually accomplishes a very important process - that of keeping your VLF fragmentation/counts down.  That's another topic I wont go into here, and many sites including Brent Ozar can go into the actual statistics and testing showing why, but always grow your log file by 8 GB per growth if it needs to be that large or larger.  You mentioned above 25 MB and I can't imagine that being large enough.  I personally manage over a hundred databases at the moment, and less than a dozen are set to 64 MB - All others are much larger ranging up to 64 GB.  Your actual SQL activity and queries will determine what your optimal size should be.  At minimum I'd go 512 MB to 2 GB and set your growth to the same until you know what your log file should sit at.

I find running each line separately works out the best, ensuring success before going to the next step.  The final line of course is what regrows your log to the size you set.  I have it at 8 GB, but you can change that to fit your needs.  If you do not at this time want to fully shrink and then regrow remove the last line and change the "1" in the line above to your size choice.

One last thing before you go out and shrink your log file.

IMPORTANT:  Any time a log file grows, manually or automatically by SQL, essentially your entire database is locked until the grow is done.  Pick a good time when doing that won't impact active transactions -  if you have "after hours" or maintenance windows.  Such a small size of 1 GB or less should be fairly fast and invisible to the end user, but that will truly be determined by your infrastructure.

USE [DatabaseName]

DBCC SHRINKFILE (N'LogicalName', 0, TRUNCATEONLY)

DBCC SHRINKFILE (N'LogicalName', 1)

ALTER DATABASE [DatabaseName] MODIFY FILE ( NAME = N'LogicalName', SIZE = 8192 MB )


Database can be referenced by tables I am a computer science student and yesterday in my class on database management systems and talking about distributed systems and good practices he said that we can reference other instances through something he called 'connetion stream' or 'connetion String 'I really did not quite understand what he said. But I raised the following question:

I theoretically own a license db, application X and application Y, are totally different applications and only share the access user. In the first database are stored company information, license and access users; In database Y and X are different data that need to share the same user that is in the license database.

I tried in SQL Server as follows:

CREATE TABLE Licences;
USE Licenses;
CREATE TABLE Persons(
 Id int identity(1, 1),
 Name varchar(100) unique not null,
 //Other user information
 primary key(Id)
);

CREATE TABLE X;
USE X;
CREATE TABLE XInformation(
 Id int identity(1, 1),
 //Other X information
 PersonId int not null references Licenses..Persons(Id)
);

CREATE TABLE Y;
USE Y;
CREATE TABLE YInformation(
 Id int identity(1, 1),
 //Other Y information
 PersonId int not null references Licenses..Persons(Id)
);

But I get an error saying that sql server does not support foreign keys between databases.

But if I create these tables without referencing I can query them like this:

SELECT * from Persons as p inner join X..XInformation as xi on p.Id = xi.PersonId;

What is the correct way to do this referencing?

How would a dba solve this case?

Can I only reference other databases in Views?

Thanks in advance.

Unfortunate, there isn't a way to directly create the FK constraint you'd like. Run through some alternatives instead.

(1) Is it really necessary to have those two tables in different databases? Is there a reason why you can't just move the table? You won't be able to enforce any rules on schema changes unless you have the tables in the same db. Push and prod developers to redo it 'the right way'.

(2) Workaround: Create a trigger on insert/update to the referencing table, which does the test FK constraint usually does. Create a trigger on update/delete on the referenced table to implement cascading if needed. You can create an additional DDL trigger that prevents changes to the referenced table's structure. The problem though is that nothing fully prevents table structure changes before the FK is dropped. Another problem can occur when the two databases are not restored together - leading to possible inconsistent configurations.

(3) Create synchronization/replication scheme so that a reasonably up to date copy of the referenced table can be kept in the database where referencing table is located. Can either be a job that copies new rows (Change tracking in mssql) or use SSIS to build an ETL package that incrementally loads new rows into the copy on the referenced table. If the two databases are on separate servers, there are more options using replication features of MSSQL. Loading data into a database local copy is another chance to check schema, but again you can't prevent changes on the referenced table while the FK exists.

For performance, it should still be using the indexes.

Cross database querying are not uncommon, nor are cross server distributed queries. DBA's best friend are scripts and automation, and you can script checks to make sure there's nothing amiss that would break your cross db/server queries and shoot you an email otherwise. There are also other ways to set up alerts (on ddl triger, etc.,) so that you know if there's going to be a problem.

As for your question regarding views, I'm not sure what you mean. You can access tables on another db from within a view, but the constraint part of the FK relationship still can't be directly maintained on a table from another db. You can't schemabind a view using tables from different database either.


Transaction log for SQL Server is too big I have a database that has a 33 gb transaction log. It is caused by the fact that the database is in full recovery mode has been used for over 5 years prior to the first backup.

I want to truncate and shrink the log file with the following command

Backup log dbname with truncate_only
go
DBCC shrinkfile('logicalfilename',100)

I want to know after I ran the above command, in the future, will I be able to do a restore to a point in time between Future full backup 1 and 2?

Time Line

Pre-Truncate Backup
truncate and shrink
Future full backup 1
Future full backup 2

Thanks,

will I be able to do a restore to a point in time between Future full backup 1 and 2?

Yes.  What you're concerned about is the Log Chain.  The log chain starts at a full backup.  Therefore at future full backup 1 you will start a new log chain and with transaction log backups you will be able to restore to point-in-time.

BOL reference on Working with Transaction Log Backups

As for your initial method of getting your log truncated, note the following from BOL on BACKUP:

  The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options have been discontinued. If you are using the full or bulk-logged recovery model recovery and you must remove the log backup chain from a database, switch to the simple recovery model.

And I'm guessing this goes without saying, but I'll be explicit.  In order to not run into this same problem you do need to be doing routine transaction log backups.


Problems caused by SQL Server 2016 Query Store I have been testing SQL Server 2016 (Standard) on an Azure VM and have encountered a problem.

In my SQL logs I am seeing SQLExceptions and eventid 1001 and this is happening every 15 minutes.

2016-06-30 15:20:45.83 spid43s     ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\LOG\SQLDump2693.txt
2016-06-30 15:20:45.83 spid43s     SqlDumpExceptionHandler: Process 43 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2016-06-30 15:20:45.83 spid43s     * *******************************************************************************
2016-06-30 15:20:45.83 spid43s     *
2016-06-30 15:20:45.83 spid43s     * BEGIN STACK DUMP:
2016-06-30 15:20:45.83 spid43s     *   06/30/16 15:20:45 spid 43
2016-06-30 15:20:45.83 spid43s     *
2016-06-30 15:20:45.83 spid43s     *
2016-06-30 15:20:45.83 spid43s     *   Exception Address = 00007FF9796D4BB2 Module(sqlmin+0000000000064BB2)
2016-06-30 15:20:45.83 spid43s     *   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION
2016-06-30 15:20:45.83 spid43s     *   Access Violation occurred reading address 0000000000000948

I have enabled Query Store on my databases and it is set to a Data Flush Interval of 15 minutes.

With query store turned off these exceptions appear to stop.

I am not experiencing this problem on my local install of SQL Server (Developer Edition).

Data is identical on Azure and local machine, both build 13.0.1601.5.

This was a known bug with Query Store on Standard and Express.

  Automatic data cleanup fails on editions other than Enterprise and Developer. Consequently, space used by the Query Store will grow over time until configured limit is reached, if data is not purged manually. If not mitigated, this issue will also fill up disk space allocated for the error logs, as every attempt to execute cleanup will produce a dump file. Cleanup activation period depends on the workload frequency, but it is no longer than 15 min.

The work around is to turn off cleanup policies and manually clear query store data.

to clear all query store data

ALTER DATABASE <database name> SET QUERY_STORE CLEAR

to selectively clear data

sp_query_store_reset_exec_stats

sp_query_store_remove_plan

sp_query_store_remove_query

The issue was first fixed in Cumulative Update 1 for SQL Server 2016:

Query Store automatic data cleanup fails on editions other than Enterprise and Developer edition of SQL Server 2016 (KB 3178297)


Oracle 11G Can't create database from template I am attempting to create a new database from an existing template in Oracle 11G.
I have done this before using dbca and haven't had a problem, but now I am hitting the following error:

TNS-04404: no error caused by oracle.net.ConfigException:
TNS-04414: File error caused by:
TNS-04610: No literals left, reached end of NV pair

After some research I found out that the TNS-04610 is likely a problem with the tnsnames.ora file. I found this file in the $ORACLE_HOME/network/admin/ directory. The files contains the following:

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_3/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

BASE_901_NEW =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BASE_901_new)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.domain.com)
    )
  )

BASE_901 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = Base_901)
    )
  )

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = Test)
    )
med =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = med)
    )

As far as I can tell that file looks correct.  Is there a syntax error that I'm not aware of?
This file was not created by me, and as far as I know it has not been manually edited by anyone.

Any leads are appreciated as I have not been able to find anything.

The last two entries have unbalanced parenthesis. You're not closing the top-level parens.

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = Test)
    )
  )              -- add this
med =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = med)
    )
  )              -- add this

(Don't add the comments though.)

Might be a good idea to be consistent with casing everywhere (even though I'm not sure it actually matters). If you call your database TEST, put TEST everywhere, not Test in some places and TEST in others.