Are Database Master Key protected with same password, the same? As it is said here it is a good practice to backup the database master key, because when the database is moved to another instance it will need the original DMK that has been used to protect the certificates in order to skip regeneration of the encrypted stuff.

In the context of database backup encryption, when the DMK is stored in the master database with the rest of the certificates used for backup encryption, do we need to create a backup of the DMK?

Also, I am wondering, as it said that

  When it is created, the master key is encrypted by using the AES_256
  algorithm and a user-supplied password.

Does this mean, that if I used same password to create and protect each DMK in my master databases across all instances, the key will be the same?

If not, it is OK to create a DMK on one database, and to restore it on all instances in order to have the same key everywhere and to need make a backup of only one key?

As it is said here it is a good practice to backup the database master key, because when the database is moved to another instance it will need the original DMK that has been used to protect the certificates in order to skip regeneration of the encrypted stuff.

That answer isn't factually correct. When the database is migrated, the DMK was encrypted with the old SMK and the password. You can open the DMK with the password on the new instance and add decryption by the new SMK. This doesn't require that you decrypt and encrypt the securables protected by the DMK again, since the actual DMK key didn't change - just what is protecting it.

  In the context of database backup encryption, when the DMK is stored in the master database with the rest of the certificates used for backup encryption, do we need to create a backup of the DMK?

Depends but most likely you won't directly need it unless there is something really, really bad that happens. Then you'll need to restore the DMK over the other one but that's not ideal, fun, or easy, either. If you have a backup of the master database then it already holds the DMK, additionally you will need a safe place to store the password. Since master is a special database, the use cases of this are much smaller than a user database.

  Does this mean, that if I used same password to create and protect each DMK in my master databases across all instances, the key will be the same?

No, there is an initialization vector that is used so the keys won't be the same. It's not based off of the password used.

  If not, it is OK to create a DMK on one database, and to restore it on all instances in order to have the same key everywhere and to need make a backup of only one key?

Definitely not ok. Is it ok to have just one password for everything from your email, online banking, and fantasy football account? Probably not. Probably not good to have a single key that opens your car, front door, mailbox, gate, bank deposit box, etc.


Can sqlplus fetch more than one row at a time? We have a task running every day dumping results of a query into a CSV-file -- as the stdout of sqlplus running a query.

The number of records is many millions (don't ask), and the job takes over two hours.

In trying to improve the timings, I wrote a C-program (using Oracle's ofetch() call), but the performance didn't change... Bumping up the Session Data Unit (SDU) size (to 1GB! on the client -- not sure, where the server would clamp it) didn't help.

I then did some more research and found, that ofen(), if used to capture multiple rows at a time, can improve performance dramatically -- and changed my C-program to use it instead.

Fetching 4K rows at a time, the timings went down to 21 minutes -- a factor of 10x reduction -- causing much rejoicing.

Before we go through the trouble of installing my new executable on production servers, maybe,  the stock sqlplus can also do the same multi-row fetching -- if supplied with the proper command-line option(s)?

Can it? We're using Oracle-11 although, if this feature is available in the client version 12, we could upgrade the client-side...

SET ARRAYSIZE has a default of 15

"Sets the number of rows that SQL*Plus will fetch from the database at one time.

Valid values are 1 to 5000. A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more memory. Values over approximately 100 provide little added performance. ARRAYSIZE has no effect on the results of SQL*Plus operations other than increasing efficiency."

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqpug/SET-system-variable-summary.html#GUID-773AEA4E-060E-4C1E-B1A0-52F7DE084AE8

There are additional tweaks that can be done (including the SDU) in either the sqlnet.ora (changing the DEFAULT_SDU_SIZE ) or in the connection string. That can be defined in tnsnames.ora but you can also put it as part of a versbose connection string such as

sqlplus usr/pwd@(DESCRIPTION=(SDU=11280)(ADDRESS=(PROTOCOL=TCP)(HOST=svr)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=svc)))

Put in your appropriate values for usr,pwd,svr and svc
https://docs.oracle.com/cd/E18283_01/network.112/e10836/performance.htm


Recover all databases to a new server I need to copy an entire SQL Server instance, with all databases from one server to another server in my test lab.

I am aware that this can be done the "cold way", by stopping/pausing the databases and copying the database files to new location and attaching them.

Is it however possible to do it the "hot way," using the full and diff backup files?

You can just use dbatools --> Start-DbaMigration  to do the entire server migration/copy - (choose backup/restore method) including jobs, logins, user objects created in system dbs, etc

  Start-DbaMigration
    [[-Source] <DbaInstanceParameter>] [[-Destination] <DbaInstanceParameter[]>]
    [-DetachAttach] [-Reattach] [-BackupRestore] [[-SharedPath] <String>]
    [-WithReplace] [-NoRecovery] [-SetSourceReadOnly] [-ReuseSourceFolderStructure]
    [-IncludeSupportDbs]
    [[-SourceSqlCredential] <PSCredential>]
    [[-DestinationSqlCredential] <PSCredential>]
    [[-Exclude] <String[]>] [-DisableJobsOnDestination] [-DisableJobsOnSource]
    [-ExcludeSaRename] [-UseLastBackup] [-Continue] [-Force] [-EnableException]
    [-WhatIf] [-Confirm] [<CommonParameters>]


How can I be specific about which CDB I want to connect to wrapping a PDB? I'm quite new to Oracle. I created a CDB like cdb1 and then a PDB within like pdb1. Now I can connect to my PDB using something like:

sqlplus user/password@server:port/pdb1

But can't I have two PDBs with the same name in different CDBs? If that happened, sqlplus wouldn't know which PDB I wanted to connect to. Does the listener have some robust way to map the PDB passed in the connection to an actual PDB within a CDB on the machine where the listener is running?

EDIT: I found out that when I write pdb1 in the sqlplus connection identifier, that is actually not the name of the PDB, but the name of the service, which just happens to be the same as the name of the PDB.

As far as I understand, I can map the service name to PDB name using select name, pdb from v$services;.

It seems to map services to CDBs I need to type lsnrctl service. And then I get output like:

Service "pdb1" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER

So I guess the instance cdb1 is referring to the CDB cdb1? Or does it also just happen to have the same name?

Then again, surely the service stating that it has 1 instance, means it could have 2 instances and these could point to separate CDBs, which brings me back to my original question of how to differentiate different PDBs with the same name.

Quite easily.

The full syntax for Easy Connect is:

Understanding the Easy Connect Naming Method

CONNECT username@[//]host[:port][/service_name][:server][/instance_name]

You just need to specify the service_name and the instance_name as well.

$ lsnrctl services
...
Service "pdb1" has 2 instance(s).
  Instance "RAIN", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER
  Instance "WIND", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER

You will have more than 1 instance serving the same service. The below may connect to any of the instances, but the listener decides which instance it forwards to your request, not you:

sqlplus user/password@localhost:1521/pdb1

The below commands always connect to just one of the instances. If that instance is down, the connection attempt fails, even though there is another instance providing the same service:

sqlplus user/password@localhost:1521/pdb1/rain
sqlplus user/password@localhost:1521/pdb1/wind


MYSQL : many-to-many problem i'm trying to filter product with their subcategories :
for example :
if subcategories = (1,3,4)
and if this is subcategory_product :

how can i get the products with subcategories id=(1,3,4) ??!! plz help
in this case i should get the product with id 62 .

One approach (you will need to test this for performance if the dataset is large) is this:

Select `sc1`.`product_id`
  From `subcategory_product` As `sc1`
  Join (Select `product_id` From `subcategory_product` Where `subcategory_id` = 3) `sc3`
    On `sc1`.`product_id` = `sc3`.`product_id`
  Join (Select `product_id` From `subcategory_product` Where `subcategory_id` = 4) `sc4`
    On `sc1`.`product_id` = `sc4`.`product_id`
  Where `subcategory_id` = 1
;

Basically, you're scanning the subcategory_product table once for each of the values, and then doing an INNER JOIN on the results to only get rows back where all three exist.


Always On Windows Server Edition For setting up Always On Availability Groups in SQL Server I know you need to have at least Enterprise Edition for SQL Server 2012, 2014, or 2016.

Do you also need the Datacenter or Enterprise Edition of Windows Server 2012 or 2016 for Always on, or can you set this up properly on Windows Server Standard 2012 or 2016?

Do you also need the Datacenter or Enterprise Edition of Windows
  Server 2012 or 2016 for Always on, or can you set this up properly on
  Windows Server Standard 2012 or 2016?

Windows Server Standard Edition is fine.

Starting with Windows Server 2012 every feature of Windows Server is available in Standard Edition.  In particular Standard Edition supports Windows Server Failover Clustering which is needed for some Always On capabilities.


"Cannot continue the execution because the session is in the kill state" when querying system tables This is a snippet from some code I run on a 2012 (SP3-CU9) (KB4016762) - 11.0.6598.0 (X64) system that I just inherited and have not yet had a chance to fully patch.  AlwaysOn AG are not enabled, and there's plenty of space in the LDF.
declare @sqlstr nvarchar(2048);
declare @dbname     sysname = 'GSMPRD'
declare @qdbname     sysname;
set @qdbname = QUOTENAME(@dbname);
set @sqlstr =
        'use ' + @qdbname + '; ' +
        'select ''' + @dbname + ''', '+
                'schema_name(tbl_schid) as sch_name,
                tbl_name,
                ndx_name,
                cast(ips.avg_fragmentation_in_percent as decimal(5,2)) as frag_pct
        FROM (select i.object_id,
                i.index_id,
                i.name as ndx_name,
                t.name as tbl_name,
                t.schema_id as tbl_schid
                from ' + @qdbname + '.sys.tables as t, ' +
                    @qdbname + '.sys.indexes as i
                where i.object_id = t.object_id
                ) as ndx,
                sys.dm_db_index_physical_stats(' + cast(@dbid as char(4)) + ', NULL, NULL, NULL, NULL) as ips
        WHERE ndx.object_id = ips.object_id
            AND ndx.index_id = ips.index_id
            and ips.index_type_desc <> ''HEAP''
            and ips.avg_fragmentation_in_percent >= 5.0
            and ips.fragment_count > 25
        ORDER BY tbl_name, ndx_name;'
exec(@sqlstr)

Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Sometimes it runs, and sometimes it doesn't.  Also, I frequently see A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) when rerunning SQL that had previously run.
sqlsvr.exe is using less than it's max allowed RAM, and the Task Manager says that it's barely using half of available RAM.
What can be causing this?

It's possible there is corruption in those system tables.  Try running:
DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

On all the databases on this instance to check for corruption.
It's also possible you're encountering a SQL Server bug. Try checking the SQL Server error log for any error messages around the time you get that "a severe error occurred" error message.  You might get more details about the error, or maybe even stack dumps that are happening at that time.


Microsoft Key Storage Provider for Always Encrypted Master Key Hoping someone can help-
I've been experimenting with Always Encrypted columns and have generated a certificate in the "Microsoft Software Key Storage Provider" -

How can I remove the "Always-Encrypted-Auto1" entry?
using SQL Express 2017 with SQL Management Studio 17.
Have not found an answer to this question via conventional search engine means or via dba.stackexchange.com.
Cheers!

How can I remove the "Always-Encrypted-Auto1" entry?

Since there isn't a way in the SSMS GUI and I doubt you want to write your own code to find out, look in %APPDATA%\Microsoft\Crypto\Keys and attempt to figure out based on date which file it may be.


Spike in AWS RDS Oldest Replication Slot lag Does anyone know about this "Oldest Replication Slot Lag" in AWS RDS? What does it represent?

I have a PostgreSQL instance on RDS (with Logical Replication switched on) for transferring data from PostgreSQL DB to S3 bucket (in CSV form) using AWS Data Migration Service. The graph of "Oldest Replication Slot Lag" is too high on this instance. and also Transaction Log Disk Space goes up too.

Any help?

The lagging size of the replica lagging the most in terms of WAL data received. Applies to PostgreSQL.

If you’re using logical replication you might consider setting up some monitoring around this metric as it could indicate that your logical replication slot on RDS has switched to inactive - meaning it is no longer consuming the transaction logs.

If left unchecked a replication slot in an inactive state can consume all your disk space.

If you’re using DMS, check the task and replication instance metrics to see if anything sticks out as a bottle neck.

Source: https://docs.aws.amazon.com/en_pv/AmazonRDS/latest/UserGuide/MonitoringOverview.html


Is there a quantitative method to follow to determine FILLFACTOR for NONCLUSTERED INDEXes? I've read different ideas to set FILLFACTOR and then monitor fragmentation over time (#1: 5 Things About Fillfactor, #2: Database Maintenance Best Practices Part II – Setting FILLFACTOR).

Instead of making an 'educated' guess upfront and then adjusting, I was wondering:

If anyone knew of a (quantitative) way to determine what level of fillfactor would be needed upfront?

(I'm primarly concerned with respect to NONCLUSTERED INDEXes, as I typically create my clustered indexes on ever-increasing integers.)

No generalised quantitative method exists to be followed.

One would'e come up by now (1+ years since question), nor have i come across one on the internet searches.


Does the number of indexes effect the modification of table? First of all Good morning !

Today I got one question regarding indexes and tables. Actually I am not facing problem regarding this but due to curiosity I want to post !!

Now come to the point,

Suppose I create 20 indexes ( obviously non-clustered) on one table (which have id,name,username,password). It will work fine.

Now the point is if I modify my table as (id,name,username,password,status,varification_code) and indexes are remains 20. Will it effect the performance ?

And what will be if indexes increase or decreases and table remains same ?

And what will be if indexes increase or decreases and table modified ?

Every time you update a record in the table, all indexes whose entries have changed because of that also need to be updated. So there more indexes you have, the longer the update will take.

Usually that is not a concern, but if you bulk-load a lot of table, it might be a good idea to disable all indexes (and constraints) and re-enable them after the load is done (as index rebuilding is faster than continuous updates).


Toad for Oracle: Procedures, Packages, Job, and DBMS_SCHEDULER If I have about 10 queries which contain subqueries, would it be a best practice to contain all of them in one procedure? This script creates a small fraction of the data required for a report.

DROP TABLE TEST1;
CREATE TABLE TEST1 AS (
SELECT
C.DATE,
C.ITEM,
(SELECT name FROM name_codes WHERE code = C.DEPARTMENT) AS DEPARTMENT,
C.BUSINESS
FROM LOGS C));

That's a fraction of it but the code more or less creates tables, filters fields, and combines outputs until the desired result. I am looking to automate data input for other scripts as well. Each script calculates a KPI and would be its own procedure. Then all of those procedures would be wrapped into a package.

That package will be scheduled as a job through DBMS_SCHEDULER. Does this sound like the correct course of action? If so, can I call an existing script to create a procedure or would I need to paste the PLSQL into the procedure script? For the moment, I just want to test the first script as a procedure which is scheduled as a job through DBMS_SCHEDULER.

If so, can I call an existing script to create a procedure or would I
  need to paste the PLSQL into the procedure script?

Both works.

DBMS_SCHEDULER.CREATE_JOB

The type of the job (job_type) can be PLSQL_BLOCK or STORED_PROCEDURE as well.

For PLSQL_BLOCK, you can specify an anonymous block, a piece of PL/SQL code such as begin ... end;.

For STORED_PROCEDURE, you can specify the name of the stored procedure to call.


Simple SQL query but huge table - how to optimize? I've got this very simple MySQL query:

SELECT target FROM table WHERE goal_id=1 AND year>=2015 AND year<=2020

The table has about 5 millions rows, though. And as a result, it's very slow (about 10 seconds).

What could I do to improve things? Would an index help, and if so on which column?

The optimal index for that query is

INDEX(goal_id, year, target)

in that order.  And regardless of the cardinality of any of the columns.

Start with the = test(s)
Add on at most one range (the two year tests are effectively one "range")
Add on all the other columns found anywhere in the query; this makes the index "covering" for another boost.  Now the entire query is performed in the index's BTree, without also having to bounce over to the data's BTree.

(That is a simplification.  For more details and varied cases: http://mysql.rjweb.org/doc.php/index_cookbook_mysql )

But...  That query looks like it might return lots and lots of rows.  If so, you may be network-bound.  If so, then think about whether you can do more processing in SQL without shoveling a million rows to the client.


Can you give me one example of Business Intelligence? I don't really understand what Business Intelligence is all about. If I start from having a corporate DB, what is it that a BI person would do? I found plenty of material on the web, but it usually is a bit too complex. I want a simple example that would make me understand what BI is all about and what would a BI person produce that is of value to my organization.

Business Intelligence is often a completely separate sect from Database Administration and Database Development.  Business Intelligence, at the highest level, includes three main facets:

Reporting
Integration
Analysis

Reporting

Reporting is the creation, deployment, and management of reports as well as the added ability for users to customize reporting dynamically.

Integration

Data integration and transformation solutions.  On the very simplest level, it is the means of extracting, transforming, and loading data into a data source, from a data source (which could be anything as simple as a flat file).  Integration is a mile deep, but that's the most basic functionality of it.

Analysis

Online Analytical Processing (OLAP) used to design, create and manage structures that contain data aggregrated from source data stores.  A catch phrase for this is data mining.

These are extremely simplified descriptions of what Business Intelligence encorporates.  There is a science behind BI, as well as each of these facets individually.  Database Professionals dedicate their time and careers to mastering these.


Why isn't transaction log free space always reused? After monitoring the Transaction log files for a while I noticed that some of them sometimes behave in a way I did not expect.

Here are two samples for comparison. All data is based on EXEC('DBCC SQLPERF(LOGSPACE);')

Sample 1: This shows the development of a TL file over time. Orange is the physical file size of the log file. Blue is the ammount of data that is used. The total file size does not increase in the moment where about 26GB of it are used, because the file is empty and there is enough volume to place 26 GB of data inside of it without growing the file.

Sample 2: This other sample shows that the total file size of this TL file has increased at the moment space were required. About 10 Gb of data has been filled into this file, that has had 25 GB total file size. At the same time the file grew 10 GB.

I would have expected that the empty/unused file capacity of the transaction log files always are reused as long as the required ammount of data fits into this space.

So the question is: Why is the empty space allocated by the transaction log file not re-used in sample 2 and instead the file grew again?

You may have got the answer already, but it interested me so I thought I'd take a look, and expand a bit more with sources.

As hinted briefly above, the extra space reserved for a possible rollback.
As I can't say it any better, Microsoft TechNet describes it thus:

  Each transaction reserves space on the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. The amount of space reserved depends on the operations performed in the transaction, but generally it is equal to the amount of space used to log each operation. This reserved space is freed when the transaction is completed.
  - Log Architecture and Management

So basically your transaction required that "extra" space reserved until it was actually committed, as that space was not available in your log file, it grew. Your transaction was committed (and therefore not rolled-back), and so that space was not actually needed.

I won't labour the point further, but just this might be of use too:

The ever-wise Aaron Bertrand talks about some events that SQL Server tracks, including log growth. These might help you track down what's happening with your log autogrows in the future


Optimising MariaDB for large AWS machines Been reading up on DB optimisation (I am an analyst, not really a Sysadmin), and it would seem the biggest adjustment one can make is change the innodb_buffer_pool_size,  innodb_buffer_pool_instances and innodb_file_per_table.

Is there some point that an extremely large buffer pool just doesn't make sense anymore. For example, when using AWS, I can get a machine with 64 cores and 500GB RAM if I need to do analysis on data that is around 200 mil rows (indexed). Is it worth setting innodb_buffer_pool_size = 400G and innodb_buffer_pool_instances = 60:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld]
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
user                           = mysql
# applies only when running as root
#memlock                        = 1

table_open_cache               = 3072
table_definition_cache         = 4096
max_heap_table_size            = 64M
tmp_table_size                 = 64M
max_connections                = 505
max_user_connections           = 500
max_allowed_packet             = 16M
thread_cache_size              = 32
query_cache_size               = 64M

# InnoDB
default_table_type             = InnoDB

# 80% of ram that is dedicated for the database
innodb_buffer_pool_size        = 400G
# number of CPU cores dedicated to the MySQL InnoDB backend
innodb_buffer_pool_instances = 60

innodb_data_file_path          = ibdata1:128M:autoextend
innodb_file_per_table          = 1
innodb_log_file_size           = 1G
innodb_log_files_in_group      = 2

innodb_buffer_pool_instances will not give you a huge advantage unless you are suffering from a very specific write contention between multiple write threads. I have personally never needed to increase it over the default 8 instances.

I use 500GB servers for MySQL and dedicate around 384 GB to the buffer pool- more would also make sense - I optimize for huge concurrency and even then I am thinking to increase it to an 80% or 85% of the total available memory (but want to leave enough for the filesystem cache).

If that would be an advantage for you or not, it will depend on your access patterns- if you are doing analytics queries, your speed up may be minimal- and of course it has decreasing returns to the point of not being an advantage at all for memory larger than a dataset.

It is very difficult to provide a general advice, but if I had todo it would be this: 1) make sure you are using the right tool (vanilla mysql-InnoDB- may be not a great a great tool for analytics- plugins or external tool may fit better); 2) keep 10-20 percent of your data on memory (most needs follow a 90-10, or 80-rule for frequency of access; and 3) keep your buffer pool with a 75%-80% of your total memory if you only use InnoDB as your main engine. Stress on general advice, your needs will vary and so your configuration. See what is your bottleneck and try to optimize that.


What's the problem with PostgreSQL temporary files? I'm studying PostgreSQL architecture and in almost every book or blog post I read people say that we have to track any increasing of temp_files numbers and find the best work_mem size to avoid new temp_file creation.

But none explain why these temp_files are so bad. Could anyone clarify to me why we need to keep the number of temp_files low?

Memory is faster than disk.

Anything that can be done only using memory will work faster instead of using the hard disks to store temporary content.

PostgreSQL documentation says:

  work_mem (integer)

  Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files.

So if you find the proper value of it (which depends on total amount of RAM, number of workers, SQL queries done, etc.) then everything should be able to run from RAM only, hence fast.

If this value is exceeded for some operation, temporary files will be created on disk, and hence the operation will be slower.

So tracking amount of temporary files and investigate if it grows is a possible strategy to get better performances (maybe work_mem is just too low, its default value being 4MB; or it is good but it maxes out currently available RAM and then you need a box with more RAM).

Hence the following tools:

1) temp_files in pg_stat_database whose value is "Number of temporary files created by queries in this database. All temporary files are counted, regardless of why the temporary file was created (e.g., sorting or hashing), and regardless of the log_temp_files setting."

and

2) log_temp_files configurable item whose documentation is:

  Controls logging of temporary file names and sizes. Temporary files can be created for sorts, hashes, and temporary query results. A log entry is made for each temporary file when it is deleted. A value of zero logs all temporary file information, while positive values log only files whose size is greater than or equal to the specified number of kilobytes. The default setting is -1, which disables such logging. Only superusers can change this setting.


Automate daily backup of database I am trying to create automate/daily backup of database in SQL Server 2008 R2. I did bit of research and I get to know that as I am using Express edition; which doesn't come with Maintenance Plan, I can't create database backup automate/daily basis. So, the only possibilities are I have to use either TSQL or create Job. I don't have much understanding of SQL Job, so I am left with T-SQL only.

Can anyone explain me how to do automate backup of database in T-SQL using stored procedure or is there any other options I have to do that.

Regards.

Since you are using SQL Server Express, you will have to get creative.  You don't have any SQL Server native scheduling tool, you can utilize Task Scheduler, and schedule a daily task to run SQLCMD, with query text to backup your database.

I'd recommend creating a script that does some string manipulation to generate a day-unique filename for the BACKUP DATABASE T-SQL.

Your script could resemble something like this:

-- declare the backup filename (with path) without the file extension
declare @DestFile varchar(128) = 'C:\BackupDir\BackupFileName_';

-- get the current date as a nice and sortable string
declare @CurrentDate varchar(64) = '';
set @CurrentDate = replace(convert(varchar(10), getdate(), 111), '/', '');

set @DestFile += @CurrentDate + '.bak';

backup database YourDatabaseName
to disk = @DestFile;
go

Save that T-SQL to a script file (for example's sake, C:\YourScriptDir\BackupDatabase.sql).  To run this script using SQLCMD, you could do:

sqlcmd -S YourServerName\YourInstanceName -i C:\YourScriptDir\BackupDatabase.sql

Then just schedule that with Task Scheduler to run daily.  It's one giant spaghetti workaround, but that's what you get with a free version.

(the above process would be tailored to a database in Simple Recovery mode.  If you are in Full, then you need to consider transaction log backups as well)


SQL Query is sometimes slow, sometimes fast I have two hosts, one with my rails app, one with my MySQL database. Going through my rails Logfile I can see that sometimes queries take much more time than the same query at other times. Think 200ms vs 2ms. The query itself is a select, explain tells me that it uses my index.

The engine is innodb, I already increased the innodb_buffer_pool_size, max_connections and the thread_cache_size. vmstat tells me that there is no paging.

What could be the reason for sometimes slow and sometimes fast queries?

There could be several reasons why the execution time is varying. The first step I would take is to test the query directly using the MySQL client from your MySQL server. This example is for Linux.

Create a script to test your query

vi query_1.sql

-- SQL_NO_CACHE means do not use the cache so you get the a more accurate execution time
SELECT SQL_NO_CACHE column_1, column_2 FROM sometable WHERE column_N = 'somevalue';
Save your file \wq
Execute the query from the console using the time command

time mysql -u root -p somedatabase < query_1.sql

real    0m2.009s

user    0m0.000s

sys     0m0.004s

You will get an output showing how long it truly took to execute your query. The times should remain constant.

You can also perform the same test from your rail app host, if you have the mysql client installed on that machine.

time mysql -h address.ip.database.host -u root -p somedatabase < query_1.sql

This might help to identify possible network issues if the query exectuion time is much longer from the Rails app host.

Update 12:25
If this is a network issue, you can follow network activity using a program like bwm-ng Bandwith Monitor to analyze network activity on every network card installed on your system and the rate at which traffic is traveling through those links.

If that is not the case, then we can move forward by looking at other possible factors such as IO, Memory and other applications


SQL Server Table Size Discrepancy Hopefully this has a simple answer and I've missed something obvious. I have a table where the number of rows * the row size is much smaller then the actual data space used by the table.

If I run the standard report "Disk Usage by Top Tables", the numbers I get are:

# Records   Reserved (KB)      Data (KB)      Indexes (KB)      Unused (KB)
33,245        32,962,192       31,070,264       144               1,891,784

which (unless I'm missing something) implies each row is taking up just under 1MB!

The table schema (which I didn't design) is:

CREATE TABLE [MySchema].[MyTable]
(
    [Code]     [varchar](4) NOT NULL,
    [SomeID]   [smallint] NOT NULL,
    [SomeID1]  [smallint] NOT NULL,
    [Updated]  [datetime] NOT NULL,
    [SomeId2]  [int] NULL,
    [SomeId3]  [int] NULL,
    [Somekey]  [real] NULL,
    [desc1]    [char](12) NULL,
    [colA]     [real] NULL,
    [someid4]  [char](20) NULL,
    [starttime] [real] NULL,
    [endtime]  [real] NULL,
    [duration] [real] NULL,
    [reason]   [real] NULL,
    [status]   [real] NULL,
    [category] [real] NULL,
    [comment]  [char](30) NULL,
    [ColB]     [real] NULL,
    [ColC]     [real] NULL
)

It has no indexes or keys.

A bit of research lead me to the idea that perhaps in the past the table had variable length columns that had been deleted, so I ran DBCC CLEANTABLE with no change.

SQL Server 2005 x64 Service Pack 3 (as far as I can work out from the following version string:

[Microsoft SQL Server 2005 - 9.00.4053.00 (X64)   May 26 2009 14:13:01   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)]

Update: I'm fairly sure this is due to a pathologically fragmented HEAP ....
I rebuilt by applying a clustered index (an index is required, according to missing index reports), and the size discrepancy has gone.

When you delete from heaps, allocated space may not be unallocated unless you use a table lock.
See "Deleting Rows from a Heap" from DELETE on MSDN

This is a separate problem to fragmentation (which happens of course)

Just in case, have you tried this to update usage info?

EXEC sp_spaceused 'MySchema.MyTable', 'true'

With no indexes or keys, you can't defragment it normally.
You can add/drop a clustered index, or do something like this

SELECT * INTO [MySchema].[MyTableNew]
FROM [MySchema].[MyTable]
-- ORDER BY something

-- if you like
EXEC sp_spaceused 'MySchema.MyTableNew', 'true'

DROP TABLE [MySchema].[MyTable];

EXEC sp_rename 'MySchema.MyTableNew', 'MyTable';

oops: just seen updates, my Internets has been broken on and off...


In MySQL Workbench, insert a new column at the "top" of a table? I must keep a table's columns as specified in a specification that changes quite often.

In MySQL Workbench, adding a new columns after all the others is easy, but I can't find a way to insert a new column where I want.
It is a problem, because I want to keep the columns ordered just like in the specification.

So I do it the painful way:

Create the new column at the bottom
Repeat Right-click-"Move up" tens of times to crawl my way up to the desired position

I am sure there is a more efficient way to achieve this, with the GUI rather than writing SQL code?
Even a shortcut to avoid the right-click would save me a lot of time.

OK, I have found! It is so easy I am ashamed I didn't find earlier...

Plain old drag and drop.

Just add the column at the bottom, then move it with your mouse to the position you want.


Is a 'block' and a 'page' the same thing in PostgreSQL? I've seen many blogs and videos talking about Postgres 'blocks'. Are these the same as 'pages' in other database management system implementations?

If you're talking about the 8kB subdivisions of physical table storage, then yes.

These are usually called "pages" in Postgres as well, and that is the terminology used in the docs.


Postgres 11 declarative partitioning by relative time I'm working on a system that uses PostgreSQL 11 as it's main data store, and I'm looking at how to most efficiently query a large table of events (up to 120 million rows, so not 'big data' big, but not exactly tiny), mainly based on time ranges. In particular, events will be searched across these preset time periods:

Last 1 hour
Last 4 hours
Last 24 hours
Last 7 days
Last 30 days

I was immediately drawn to the declarative partitioning capabilities of Postgres 11, but I've never used it before.

All of the examples I've seen use static values for partitioning, such as FOR VALUES FROM ('2018-02-01') TO ('2018-03-01'), which isn't going to work without a background task to manage partitions - so I have a couple of questions:

Can rows overlap multiple partitions? For example, an event that occurred 3 hours ago fits the criteria for the first 2 time periods above
Can rows be partitioned by relative time periods, such as 'last 1 hour'?

You could partition by 4 hour intervals (aligned to a specific boundary pattern) and then subpartition that into hours.  But, I don't see what that would get you. If you have them partitioned by hour and run a query that covers 4 hours, it will just query the appropriate 4 individual hourly partitions.  Adding an intervening layer of 4-hour partitioning doesn't get you anything.

For 2, no.  It would have to move rows between partitions atomically with the tick of the smallest clock granularity, and that just isn't feasible.  But if you query for an unaligned one hour interval, it will just have to include two adjacent one hour partitions, so it should still get a large part of whatever benefit there is to be had.

But it isn't clear what benefit there is to be had. It will depend on the nature of your queries. The biggest benefit you might get would be that once data reaches 31 days, you could drop the partition, rather than needing a massive DELETE.


Risks of Switching Between Simple & Full Recovery in SQL Server 2005 Ok, so we have several instances on several nodes in a clustered environment. One of them is for JD Edwards stuff and has had an issue lately with a job failing because there was not space in the tempdb to complete it. The code for this job hasn't changed in YEARS, and yet I realize the data size may have.

Anyway, after setting the tempdb to some Autogrowth that wouldn't bork that drive I started digging deeper on this install. It seems there are some scheduled jobs that are switching the recovery mode back and forth from Simple to Full with backups supposed to be happening around those for some reason. I believe this is what caused the T-Logs backup to fail at some point and thus not have enough room for the tempdb itself to complete these long-standing JDE jobs.

So questions:

Am I totally ignorant to think that switching back and forth from Simple to Full is a bad idea on a regular basis?
If I am right that this is a bad idea what other scenarios other than the one I have already described can I use to illustrate this to my Sr. DBA who must have created this situation?

TIA

In this situation you should first investigate what amount of data loss is possible-- i.e., whether you need point in time recovery at all times.

If you DO need to be able to restore up to the minute, then breaking the log chain by switching to the simple recovery model is not acceptable. You lose the ability for up to the minute restore after the final log backup is taken prior to switching to simple, and you do not regain it  until  you have switched back to FULL and then performed an additional log backup.

If you do NOT need to be able to  restore up to the minute, you can investigate using the BULK LOGGED recovery model as a better alternative than switching to simple. Do note that  you will still have a large transaction log backup after completing BULK LOGGED transactions, however.

Here is a helpful MSDN reference on switching from the FULL recovery model to either BULK LOGGED or SIMPLE.

From the context of your question, it sounds that your database log files are on the same drive as your tempdb files. If it is possible to isolate your TempDb files to another drive, that would be beneficial for management purposes, as well as performance (depending on your drive layout).


Selecting non-aggregate column on 3 table inner join I have 3 tables in a standard parent/child kind of "belongs to" relationship in postgres:

donations --(belong to)--> campaigns --(belong to)--> organisations

Each table has an id column as a primary key, that's used as a foreign key by the child table
If I'm doing a SELECT with a GROUP BY on the table, I can't select a column from the  related organisations table without an aggregate function, even though there can only be one joining row. I've always worked around this by using MAX() since I know there's only one row for the join, eg
SELECT
campaigns.name,
MAX(organisations.name),
SUM(amount)
FROM donations
JOIN campaigns ON campaigns.id = donations."campaignId"
JOIN organisations ON organisations.id = campaigns."organisationId"
GROUP BY campaigns.id;

but this feels like quite hacky. Is there a more correct way to do this?

This can be solved by aggregating first and joining to the result of the aggregation:
SELECT c.name as campaign_name,
       o.name as organization_name
       d.total_amount
FROM (
  select "campaignId", sum(amount) as total_amount
  from donations
  group by "campaignId"
) d
  JOIN campaigns c ON c.id = d."campaignId"
  JOIN organisations o ON o.id = c."organisationId"
;


XAMPP MySQL troubleshooting with MySQL Server MySQL in XAMPP isn't starting after I installed MySQL Server.
MySQL Server is using port 3306, so I've changed in xampp\mysql\bin\my.ini:

[client]
port            = 3360
socket          = "C:/xampp/mysql/mysql.sock"

[mysqld]
port= 3360

And in xampp\php\php.ini:

mysqli.default_port=3360
mysql.default_port=3360

Also in XAMPP Control Panel -> Config -> Service and Port Settings -> MySQL changed Main Port to 3360.

After all these changes it still isn't working.

Nestat is showing 3306 is used by mysqld.exe, so I think XAMPP MySQL should work correctly on 3360.

Also I've tried to switch off MySQL server, didn't help to.

Error:

Error: MySQL shutdown unexpectedly

Logs:

2017-11-24 17:08:48 2498 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2017-11-24 17:08:48 9368 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2017-11-24 17:08:48 9368 [Note] InnoDB: The InnoDB memory heap is disabled
2017-11-24 17:08:48 9368 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2017-11-24 17:08:48 9368 [Note] InnoDB: Memory barrier is not used
2017-11-24 17:08:48 9368 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-11-24 17:08:48 9368 [Note] InnoDB: Using generic crc32 instructions
2017-11-24 17:08:48 9368 [Note] InnoDB: Initializing buffer pool, size = 16.0M
2017-11-24 17:08:48 9368 [Note] InnoDB: Completed initialization of buffer pool
2017-11-24 17:08:48 9368 [Note] InnoDB: Restoring page 0 of tablespace 0
2017-11-24 17:08:48 9368 [Warning] InnoDB: Doublewrite does not have page_no=0 of space: 0
2017-11-24 17:08:48 9368 [ERROR] InnoDB: space header page consists of zero bytes in data file C:\xampp\mysql\data\ibdata1
2017-11-24 17:08:48 9368 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2017-11-24 17:08:48 9368 [ERROR] Plugin 'InnoDB' init function returned error.
2017-11-24 17:08:48 9368 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-11-24 17:08:48 9368 [Note] Plugin 'FEEDBACK' is disabled.
2017-11-24 17:08:48 9368 [ERROR] Unknown/unsupported storage engine: InnoDB
2017-11-24 17:08:48 9368 [ERROR] Aborting

Someone, please help.

i suspect this

  2017-11-24 17:08:48 9368 [ERROR] Unknown/unsupported storage engine:
  InnoDB

is the problem.

Try deleting

ib_logfile0 and ib_logfile1 from your MySQL directory


sql 2000 trace : sp_releaseschemalock For a SQL 2000 server, is it bad to see too many

  exec sp_releaseschemalock 1

events in a SQL trace file?

Since sp_releaseschemalock is an internal stored procedure, under normal circumstances, seeing many of it is not an issue.


Converting Data Type of a full Column/Field I have a primary key ID column I want  to convert to a string, any type of string.
Now, I know how to do this for a specific value, by using either CAST or CONVERT. But how do I go about doing it for the whole field? I guess we use some type of Transact SQL , but I would appreciate some pointers on how to do this.
Thanks.

I'm not sure what you mean by 'whole field", but this is how you'd convert an Integer to a varchar string.  The 'second' select proves it's a string because you can concatenate brackets around it.

You'd apply the same technique when selecting rows from a table.

DECLARE @IntColumn int = 5
select convert(varchar(20),@IntColumn)
select '[' + convert(varchar(20),@IntColumn) + ']'


Create a PostgreSQL constraint to prevent unique combination rows Imagine you have a simple table:

name | is_active
----------------
A    | 0
A    | 0
B    | 0
C    | 1
...  | ...

I need to create a special unique constraint which fails on following situation:
different is_active values can't co-exist for the same name value.

Example of permitted condition:

Note: simple multi-column unique index won't permit combination like this.

A    | 0
A    | 0
B    | 0

Example of permitted condition:

A    | 0
B    | 1

Example of failed condition:

A    | 0
A    | 1
-- should be prevented, because `A 0` exists
-- same name, but different `is_active`

Ideally, I need unique constraint or unique partial index. Triggers are more problematic for me.

Double A,0 allowed, but (A,0) (A,1) isn't.

You can use an exclusion constraint with btree_gist,

-- This is needed
CREATE EXTENSION btree_gist;

Then we add a constraint that says:

"We can't have 2 rows that have the same name and different is_active":

ALTER TABLE table_name
  ADD CONSTRAINT only_one_is_active_value_per_name
    EXCLUDE  USING gist
    ( name WITH =,
      is_active WITH <>      -- if boolean, use instead:
                             -- (is_active::int) WITH <>
    );

Some notes:

is_active can be integer or boolean, makes no difference for the exclusion constraint. (actually it does, if the column is boolean you need to use (is_active::int) WITH <>.)
Rows where name or is_active is null will be ignored by the constraint and thus allowed.
The constraint makes sense only if the table has more columns. Otherwise, if the table has only these 2 columns, a UNIQUE constraint on (name) alone would be easier and more appropriate. I don't see any reason for storing multiple identical rows.
The design violates 2NF. While the exclusion constraint will save us from update anomalies, it may not from performance issues. If you have for example 1000 rows with name = 'A' and you want to to update is_active status from 0 to 3, all 1000 will have to be updated. You should examine whether normalizing the design would be more efficient. (Normalizing meaning in this case to remove is_active status from the table and add a 2-column table with name, is_active and a unique constraint on (name). If is_active is boolean, it could be totally stripped and the extra table just a single column table, storing only the "active" names.)


Is it safe to insert the newest row with SELECT INTO? Trying to keep rows consistent then referencing previous rows.

CREATE TABLE transactions (
    id         int     PRIMARY KEY
       GENERATED BY DEFAULT AS IDENTITY,

    account_id bigint  REFERENCES account,
    change     integer NOT NULL,
    balance    integer NOT NULL,
)

Query from the client is basically

SELECT balance
FROM transactions
WHERE account_id = X
ORDER BY id DESC
LIMIT 1;

INSERT INTO transactions (account_id, change, balance)
VALUES (X, 100, $balance + 100);

This code will produce incorrect results on concurrent inserts.
So, the question is how to make balance calculation always correct?

Will INSERT INTO ... SELECT(...) be enough?

You could kind of make it work if you included a change_id in the table, had a unique index on (account_id, change_id), and always incremented change_id by 1 from the previous value for that account_id.  Then on conflicting inserts to the same account_id, one will fail with a unique constraint violation and need to re-try.

You could do it in one insert into ... select statement.  Or you can do it in two separate statements as long as they are in one transaction, and the first statement selects both balance and change_id simultaneously.

I think it is horrible design.  But it can work.  Really you have two entities, a transaction and an account, and so should have two tables rather than trying to cram them into one.


Error using triggers with SQL Server Profiler Tables After noticing 100's of sort_warning errors in a recent analysis of the default trace output, I am trying get the text of the queries causing those warnings.

SQL Server Profiler has an event called Sort Warnings, but it doesn't have the TextData column.

Using Extended Events is not possible for 2008 R2, because the sort_warning event isn't available in Extended Events for 2008 R2.

I thought about placing a trigger on the table I'm using to store profiler's results to capture the output from DBCC INPUTBUFFER.

-- table to store the results of dbcc inputbuffer
CREATE TABLE Trace_Results(EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(255))

CREATE TRIGGER [dbo].[Trace_Trigger]
  ON [dbo].Trace_Table
  AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL VARCHAR(MAX)

    SELECT @SQL = 'DBCC INPUTBUFFER(' + CONVERT(VARCHAR(10), ins.SPID) + ')'
    FROM   inserted ins

    --PRINT @SQL

  INSERT INTO Trace_Results (EventType, Parameters, EventInfo)
    EXEC(@SQL)

END

I can start the trace from profiler without any errors, but when I run a query that causes a sort warning, I get the following error:

Which causes the trace to stop.

I also just tried inserting directly into the Trace_Table table with the following command

INSERT INTO trace_table (eventclass) values(1)

, but nothing shows up in the Trace_Results table.

If I place the same trigger on any other user table in my database, the results from DBCC INPUTBUFFER are stored as expected in Trace_Results.

What would prevent a trigger on a table created in SQL Server Profiler from inserting data into another table?

UPDATE 24/01

Trying @Kin's solution, but still getting NULL in the SQLText columns

Query used to cause sort warnings

-- Declare table variable
DECLARE @T1 TABLE (Col1 int, Col2 varchar(8000))
-- Insert data to table variable
INSERT INTO @T1
SELECT * FROM dbo.TestTable

-- Return data sorted on Col2 column
SELECT * FROM @T1
ORDER BY Col2 ASC

Screen shot of sort_warnings_log table

These are the messages that get logged:

  16943  Could not complete cursor operation because the table schema changed after the cursor was declared.
  19052  The active trace must be stopped before modification.

So it seems like this is an inherent issue with creating a trigger on the trace table (and I did not crack open a debugger to see exactly what Profiler is trying to do in this case). This has nothing to do with the fact that you're pulling spid values in the trigger, or using DBCC INPUTBUFFER, or using dynamic SQL. A simple trigger that just does PRINT N'hi'; also causes the same symptom: those two exceptions, the error dialog in Profiler, and the trace stops logging to that table altogether.

Perhaps you can just set up a trace that collects both the sort warning events and SQL:StmtCompleted and try to manually correlate them using sequence / transaction info or simple intuition. Or you could try my idea from above:

  You can always automate pulling in trace data to a table from a file once a server-side trace is up and running - I suspect such a table would have the ability to utilize such a trigger when Profiler is not involved.

It's not exactly as simple as it sounds, and it would have to poll quite frequently in order for the trigger to rely on the timeliness of subsequent inputbuffer data. I don't want to get too deep into the implementation details of that, it's just an off-the-cuff idea.

The other idea is to upgrade. :-)


Why Does SSIS Oracle Source by Attunity return DT_WSTR for Oracle CHAR(1) Column? Using SSIS 2012 and Microsoft SSIS Connector for Oracle by Attunity v. 2.0.

When I add an Oracle Source for a particular table, it is setting the data type for an Oracle CHAR(1) field to DT_WSTR.

I have read documentation and searched the web, and I would expect an Oracle CHAR(1) to be DT_STR and not DT_WSTR.

Does anyone know why this is happening?

Further, when I try to set the data type in my Oracle Source to DT_STR for the column, the setting doesn't seem to stick.

Anyone encounter this, and do you have any suggestions as to how to work around it?

I believe I figured this out, but if anyone has better documentation or expertise, please do post a separate answer, and I will vote it up and accept it.
What I have discovered is that Oracle PL/SQL has a means for expressing the concepts of "byte semantics" and "character semantics" in PL/SQL when declaring character data types.
See the Length Semantics for Character Datatypes heading in this Oracle Documentation Web Page where it states:

Consider the size of characters when you specify the column length for character datatypes. You must consider this issue when estimating space for tables with columns that contain character data.
The length semantics of character datatypes can be measured in bytes or characters.
Byte semantics treat strings as a sequence of bytes. This is the default for character datatypes.
Character semantics treat strings as a sequence of characters. A character is technically a codepoint of the database character set.

While reviewing my Oracle source database, I am now noticing that the fields that have been imported as DT_WSTR are in fact declared using the PL/SQL syntax xxxCHAR(n CHAR) - where the the second "CHAR" is explicitly stating the character semantics - rather than the default byte semantics.
Thus, in my Oracle database, I believe I need to treat a column with database data type CHAR(1 CHAR) as a character datatype using character semantics, and I believe that means that the field can accommodate 1 character regardless of the byte length of the character set in use.
Given the above, SSIS (via the Microsoft Oracle Connector by Attunity) will promote non-NCHAR/NVARCHAR data types to DT_WSTR (as opposed to DT_STR).
And, following the above, I will need NCHAR and NVARCHAR SQL Server database data types when importing columns expressed using character semantics.
Once I do this, I will no longer have to convert DT_WSTR data types to DT_STR in my SSIS data flows (which was becoming quite tedious).
Note, I was already using NCHAR and NVARCHAR for fields declared as such on the Oracle side - expecting to provide the proper storage for these types on the SQL Server side.
UPDATE: Here is the docs.oracle.com web page that shows the syntax I describe as follows:

CHAR
You use the CHAR datatype to store fixed-length character data. How the data is represented internally depends on the database character set. The CHAR datatype takes an optional parameter that lets you specify a maximum size up to 32767 bytes. You can specify the size in terms of bytes or characters, where each character contains one or more bytes, depending on the character set encoding. The syntax follows:
CHAR[(maximum_size [CHAR | BYTE] )]


SQL Server Agent logging actions and changes done to jobs Is there a way to track changes done to SQL Server Agent jobs?

If you had a reason to believe somebody is playing around with your job settings. How would you proceed to track who did it and from where the changes were made to the individual job?

I'm thinking along the lines of extended events.

For sake of simplicity, I will assume that you want to track sysjobs, sysjobsteps and sysjobschedules. There may be other tables you want to monitor.

Option 1: SQL Audit (requires Enterprise Edition)

USE [master]
GO

-- Audit
CREATE SERVER AUDIT [jobs]
TO FILE
(   FILEPATH = N'PathToSomeFolder'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = 'e807469a-6c9d-43f1-af46-cf7b89ba898d'
)
ALTER SERVER AUDIT [jobs] WITH (STATE = ON)
GO

USE [msdb]
GO

CREATE DATABASE AUDIT SPECIFICATION [job_changes]
FOR SERVER AUDIT [jobs]
ADD (UPDATE ON OBJECT::[dbo].[sysjobs] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[sysjobsteps] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[sysjobschedules] BY [public])
WITH (STATE = ON)
GO

The data captured by the audit appears like this:

Option 2: Extended Events session

-- Step 1: extract object_id for the following tables
SELECT object_id
from sys.tables
WHERE name IN ('sysjobs','sysjobsteps','sysjobschedules');

-- Step 2: use those object_ids in the following session:
CREATE EVENT SESSION [capture_job_changes] ON SERVER
ADD EVENT sqlserver.lock_acquired (
    SET collect_database_name = (0)
        ,collect_resource_description = (1)
    ACTION(sqlserver.client_app_name, sqlserver.is_system, sqlserver.server_principal_name)
    WHERE (
        [package0].[equal_boolean]([sqlserver].[is_system], (0)) -- user SPID
        AND [package0].[equal_uint64]([resource_type], (5)) -- OBJECT
        AND [package0].[equal_uint64]([database_id], (4))  -- msdb
        AND (
               [object_id] = 1125579048 -- sysjobs
            OR [object_id] = 1269579561 -- sysjobsteps
            OR [object_id] = 1477580302 -- sysjobschedules
        )
        AND (
               [mode] = (8) -- IX
            OR [mode] = (5) -- X
        )
    )
)
WITH (
     MAX_MEMORY = 20480 KB
    ,EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS
    ,MAX_DISPATCH_LATENCY = 30 SECONDS
    ,MAX_EVENT_SIZE = 0 KB
    ,MEMORY_PARTITION_MODE = NONE
    ,TRACK_CAUSALITY = OFF
    ,STARTUP_STATE = OFF
);
GO

-- Step 3: add a convenient target to the session (file target?)

The data in the XE session appears like this:

Regarding this second option, I wrote a blog post on a similar subject (tracking object usage) where I describe the details of the technique. Basically, you can consider IX/X locks as updates to the underlying tables.

This session captures the bare minimum, but you can add more fields/actions to it to capture the sql text or the computer name or whatever makes sense for you.


SQL column goes from first to last position in output I have an SQL query inside a SQL Server Integration Services (SSIS) package where the preview shows the column order correctly, however the actual output uses a different column order.

This is exported to a .csv file for import into another application and needs to be in a specific order.

I need the Date column first, which shows up that way when I preview the data output in the OLE DB Source Editor window; however, when I click on the "Columns" to display their order it shows them outputing in the last position, which is what they do.

Here is the SQL query:

DECLARE @now DATETIME;

SET @now = GETDATE()-45;

SELECT CONVERT(VARCHAR(8), [ORDERS].[STARDAT], 10) AS [MM-DD-YY], [ORDERS].[CLIENTID],
    [ORDERS].[SAMPNAME], [ANALYTES].[OP10AN], [RESULTS].[FINAL]
FROM [ORDERS], [RESULTS], [RUNS], [ANALYTES], [SamNam]
WHERE [ORDERS].[sampname] = [SamNam].[SName]
    AND [RUNS].[RUNSTS]= 'D'
    AND [RUNS].[COMPDATE]>@now
    AND [ORDERS].[SAMPTYPE] IS NULL
    AND [ORDERS].[ORDNO] NOT IN (308333,308334)
    AND [ORDERS].[CLIENTID] <> 'Ind Waste'
    AND [RESULTS].[ORDNO]=[ORDERS].[ORDNO]
    AND [RESULTS].[RUNNO]=[RUNS].[RUNNO]
    AND [RESULTS].[ANALPRINT]= 'Y'
    AND [ANALYTES].[SINONYM] =[RESULTS].[SINONYM]
    AND [ANALYTES].[TESTCODE] =[RESULTS].[TESTCODE]
    AND [ANALYTES].[OP10AN] IS NOT NULL
ORDER BY  [ORDERS].[ordno];

Any input into why the query is getting turned around?

In the Columns page on the OLE DB Source, you can use the check boxes beside the columns to change the order.

The columns are added to the output in the order in which they get checked, so what you need to do is uncheck all the columns, and then check them again in the correct order.

Downstream data flow tasks will want to update themselves because the column mappings "broke", but this shouldn't be a big issue to fix.


Create unique numeric token on PostgreSQL I have a database where I want to generate unique tokens of six numeric characters. These tokens should be generated each time an invoice number is inserted in the invoice_no column. This would mean that every time I do an insert and the column invoice_no is not null, the token is generated.

I want the token to be to generate as many tokens as possible without a token being repeated.

create table ledgers( invoice_no varchar(10),
                      debit numeric,
                      credit numeric,
                      details varchar(20),
                      token char(6) );

If you don't mind the unique token being sequential, you can use a sequence, like so:

CREATE TABLE invoices
(
    invoice_no integer,
    token char(6)
);

CREATE SEQUENCE invoices_token_seq START WITH 100000;

ALTER TABLE invoices
ALTER COLUMN token
SET DEFAULT CAST(NEXTVAL('invoices_token_seq') AS CHAR(6));

To test:

postgres=# INSERT INTO invoices ( invoice_no ) VALUES ( 1 );
INSERT 0 1
postgres=#
postgres=# SELECT * FROM invoices;
 invoice_no | token
------------+--------
          1 | 100000
(1 row)

postgres=#

Note that I have used CAST as you specified that the token should be "numeric characters".

The above is the same as using an AUTO_INCREMENT column in MySQL.

However, to accommodate for the fact that you only want to generate a token when the invoice_no IS NOT NULL, you need to use a trigger as follows:

create table ledgers
(
    invoice_no varchar(10),
    debit numeric(6,2),
    credit numeric(6,2),
    details varchar(20),
    token char(6)
);

CREATE SEQUENCE ledgers_token_seq START WITH 100000;

CREATE OR REPLACE FUNCTION token_insert() RETURNS trigger AS '
BEGIN
  IF NEW.invoice_no IS NOT NULL
  THEN
    NEW.token := CAST(NEXTVAL(''ledgers_token_seq'') AS CHAR(6));
    END IF;
  RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER ledgers_insert BEFORE INSERT ON ledgers FOR EACH ROW EXECUTE PROCEDURE token_insert();

Test:

postgres=# INSERT INTO ledgers ( invoice_no, debit, credit, details ) VALUES ( 1, 2, 3, 'NOT NULL invoice_no' );
INSERT 0 1
postgres=#
postgres=# INSERT INTO ledgers ( debit, credit, details ) VALUES ( 2, 3, 'NULL invoice_no' );
INSERT 0 1
postgres=#
postgres=# select * from ledgers;
 invoice_no | debit | credit |       details       | token
------------+-------+--------+---------------------+--------
 1          |  2.00 |   3.00 | NOT NULL invoice_no | 100000
            |  2.00 |   3.00 | NULL invoice_no     |
(2 rows)

postgres=#


How to recover auto committed transaction in Oracle with normal privilage I have updated the table & need to rollback that changes.

I have tried Rollback work command but data still not rollback.
I have also tried Flashback command but it is not working because "row movement is not enabled"

Could you please help me out. I don't have DBA access as well, still I need to recover the data.

You cannot recover the committed transaction with a normal privilege.
However, you may be able to
ask your DBA to flashback(If the database is set up with flashback enabled for tables and UNDO_RETENTION has set approximately)the table to a specific point in time, or,assumin they are using hot backups, Database Point-In Time Recovery(This option will also rollback changes made by others on the database) could be an alternative for flashback.

For details:Rewinding a Table with Flashback Table


Would a table benefit if it was a heap I've a logging table with approx 1.500.000 rows, the primary key is an ascending identity and the clustered index is on the primary key. The identity value is auto-generated => records are always added at the end. The average row size is 1570 bytes.

There are a lot of page splits because new rows are added frequently. No rows get updates/deleted and there is a non-clustered index on the table so rows can be selected.
Due to the page splits, the clustered index is always fragmented > 65%.

I wonder my table would benefit of removing the clustered index and make it a heap table?

This is how my table + non-clustered index looks like:

CREATE TABLE [dbo].[LogEntry](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Application] [varchar](20) NOT NULL,
[EntityFullName] [varchar](80) NOT NULL,
[Action] [int] NOT NULL,
[UserName] [varchar](25) NOT NULL,
[TimeStamp] [datetime] NOT NULL,
[EntityId] [varchar](50) NOT NULL,
[WhatChanged] [nvarchar](max) NULL,
CONSTRAINT [PK_LogEntry] PRIMARY KEY CLUSTERED(
   [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY] )
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE NONCLUSTERED INDEX [ID_Application_Entity_FullName_TimeStamp] ON [dbo].[LogEntry]
(
    [Application] ASC,
    [EntityFullName] ASC,
    [TimeStamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO

Update:
Someone had enabled auto-shrink behind my back => this will be the cause of the fragmentation

This started as a comment/questions but it got to long so I moved it here:

I'm really thrown by this question.  1.5mil rows isn't really all that big.  And the point behind an identity is that it's ever increasing.  If that's your CL you shouldn't be doing inserts into the middle of a page, certainly not often enough to cause the level of fragmentation you're seeing.

Couple of questions:

Are you doing IDENTIY_INSERTS?  Basically specifying what the identity value should be?  Or have you re-set the identity at some point so that you are inserting into the middle of the range?

Typically if you are doing inserts it looks like this:

5 6 7 8 < Next insert goes here >

But if you have something like this (assume your next identity value is 4)

 1 2 3 < Next insert goes here > 100 101

Then you could be seeing quite a few page splits.  But in the normal course of things you shouldn't be.

Is there any chance you are shrinking your database?  Auto_shrink or a maint plan/job that does shrinks?  If so it's the shrink that's causing your fragmentation not the clustered index.

In general there is nothing wrong with a HEAP and they can be faster for INSERTs.  My biggest concern with them tends to be if you are doing large numbers of deletes or updates (which you say you aren't).  In those cases you can get a space leak and end up with a table that is multiple GBs in size but has 0 rows.

Actual answer

Given you have a log file, and are only ever inserting, you could try dropping the PK and see how performance goes (in a test environment first of course). Once you've run some tests using your workload and seeing how things go then make your change in production and monitor there for a while. You might even consider dropping the identity column entirely.

Do check that SHRINK thing though.  That's a killer.


Tricky condition for PostgreSQL UPDATE query I need help with a PostgreSQL query.
I have a Django app + pg backend, where users post interesting URLs (or links) and can then comment under such links (called replies). There are two pg tables to represent this: links_link and links_publicreply.
I am to delete aged rows from links_publicreply. That's easy enough:
DELETE FROM links_publicreply WHERE "submitted_on" < now() - interval '7 days';

But some of these are referenced in the links_link table, hence the query fails. The reference in the links_link table is:
latest_reply = models.ForeignKey
    (
        'links.Publicreply',
        blank=True,
        null=True,
        on_delete=models.SET_NULL
    )

My first question is: How do I delete these foreign key references too?
My hunch is:
begin;
UPDATE links_link SET latest_reply=NULL where latest_reply_id in
    (SELECT id FROM links_publicreply where "submitted_on" < now() - interval '7 days');
DELETE FROM links_publicreply WHERE "submitted_on" < now() - interval '7 days';
commit;

Does this look right?
If you read the update query above closely, you'll notice it sets latest_reply to null wherever it pointed to an aged links_publicreply row.
I don't actually want to do that. I ideally want to set latest_reply to the next available links_publicreply record (associated to the particular links_link record), which didn't get deleted by the query:
DELETE FROM links_publicreply WHERE "submitted_on" < now() - interval '7 days';

How do I do that? That's my second question.

Tables
PG tables, as described in my Django app's models.py, are as follows:
class Link(models.Model):
    description = models.TextField(validators=[MaxLengthValidator(500)])
    submitter = models.ForeignKey(User)
    submitted_on = models.DateTimeField(auto_now_add=True)
    reply_count = models.IntegerField(default=0)
    latest_reply = models.ForeignKey
        ('links.Publicreply', blank=True, null=True, on_delete=models.SET_NULL)

class Publicreply(models.Model):
    submitted_by = models.ForeignKey(User)
    answer_to = models.ForeignKey(Link)
    submitted_on = models.DateTimeField(auto_now_add=True)
    description = models.TextField(validators=[MaxLengthValidator(250)])

Just add an ON DELETE CASCADE option to your foreign key:

ALTER TABLE links_link
DROP CONSTRAINT constraint_name,
ADD CONSTRAINT constraint_name FOREIGN KEY (latest_reply_id)
      REFERENCES links_publicreply(id) ON DELETE CASCADE;

Apparently in django the above is translated as:

on_delete=models.CASCADE


SSMS: Limit a login to be able to view and work only with some databases, but not able to make any changes on the server level On a shared cloud we would like to work with one instance of MS SQL Server. Different clients have various databases. They can be owner of their databases and with DENY VIEW ANY DATABASE TO PUBLIC in SSMS they can only see their databases. That works fine. To connect to SSMS they need the CONTROL SERVER permission. But even after a DENY of all the server level permissions they are able to do many things in SSMS, like restart the sql server services. Of course this should not be possible for such users.
How can a user be configured not to be able to do any actions to the server itself?

To connect to SSMS they need the CONTROL SERVER permission.

No, that is not true at all. I hope you didn't learn that from any documentation, blog post or presentation. If you did, you should tell us where, so we can hunt them down and correct it.

In order to connect, they need to be a member of the public role (granted by default to all logins). To do anything aside from connect, they need to be granted explicit permissions or added to server-level roles. To connect to a database, they need CONNECT. To do anything aside from connect to a database, they need to be granted explicit permissions or added to database roles.

If you don't want them to have the permissions inherited from CONTROL SERVER, then don't grant that permission. Simple.

And don't test what an end user can do at the service level by connecting to the server in your copy of Management Studio using their credentials. The ability to perform certain operations (like restarting the service) has nothing to do with accounts or permissions inside SQL Server - your Windows domain account is checked, regardless of what user is connected in Object Explorer. To verify this, go to someone's workstation who doesn't have the ability - via their Windows account - to restart services at the Windows level, and try again. Or create a guest account on your own machine.


Query to compare two column of table 1 with two column of table 2 and update the third column in table 1 as 'y' if the record matches I have two tables, Table 1 with column email , number, enabled and Table 2 with column email,number.
Now i have to compare the records of both the column from Table 1 and Table 2 , If the records matches in both the table then i have to update the column enabled as  "Y" in table 1.
Please help me with the query.

One way would be

UPDATE t1
SET    t1.enabled = 'Y'
FROM   Table1 t1
WHERE  EXISTS (SELECT *
               FROM   Table2 t2
               WHERE  t1.email = t2.email
                      AND t1.number = t2.number);

Or a more concise alternative

UPDATE Table1
SET    enabled = 'Y'
FROM   Table2
WHERE  Table1.email = Table2.email
       AND Table1.number = Table2.number;


What's the difference between a thread vs. a process? Specifically regarding MySQL, what is the difference between a thread and a process? The documentation seems to refer to them interchangeably at times, and it even throws in the term "session" too.

At the most basic level, the command to show threads includes the term PROCESSLIST, as if a list of processes is a list of threads. (https://dev.mysql.com/doc/refman/5.6/en/show-processlist.html -- The first sentence says this list "shows which threads are running.") But then, in the performance_schema.threads table, each thread there has its own unique thread_id that is not the ID returned from SHOW [FULL] PROCESSLIST. Instead, PROCESSLIST_ID is a foreign key to the PROCESSLIST (https://dev.mysql.com/doc/refman/5.6/en/threads-table.html). This leads me to believe that threads and processes are not the same thing.

MySQL has been implemented on a lot of Operating Systems -- From Atari to Windows to Solaris to Linux.  Different OSs work differently when it comes to "processes" versus "threads".  MySQL picked whichever was better in each case.  Then it got sloppy in saying "THREADS_runnng" and "show PROCESSlist".

So, in MySQL context, "threads" and "processes" are synonymous.

In Operating System context, they are different enough to necessitate multiple courses in Computer Science.

In MySQL, one connection uses one thread (or process).  There is also a main thread (process) for coordinating between the connections.  Also, InnoDB has some extra threads (which may actually be threads and not processes?) to deal with background I/O tasks.


I'm attempting to write a stored procedure that can read from a file and act on the contents I have a text file filled with SELECT statements that I would like to reference from a stored procedure. This is what my text file looks like:

SELECT '4200100116' AS [SRC_GEOID]
       ,SUM([Workers_in_Flow])
FROM [table]
WHERE [DEST_GEOID] IN ('4200100116'
                      ,'4200105880'
                      ,'4200132144'
                      ,'4213357872')

I want to iterate through the text file and run these commands and append the results to a table. Is this possible? If so, how?

I've looked into T-SQL and gotten conflicting ideas on whether that's what I want or not. Also, I've learned about BULK INSERT but that seems to be more about inserting into a table whereas I want to run the actual commands and append the results.

This can be done simply with powershell. Use get-content to get queries from file, invoke-sqlcmd to run the queries, and write-sqltabledata to output results to table. Save example below to .ps1 and run in powershell session passing the path to file with queries. (ex. C:\you\powershell> .\run-tsql.ps1 C:\temp\sql_queries.txt)

#paramter passed to script
param( [string] $auditlist)

#read contents of file
$queries = Get-Content $auditlist

#adds sql server module to get the sql server commands
import-module sqlserver

#cycle though all results retrived from file
foreach ($query in $queries)
{
    #run each command and load into table
    Invoke-Sqlcmd -serverinstance <instance name> -database <databasename> -query $query -OutputAs DataTables |Write-SqlTableData -ServerInstance <instance name> -DatabaseName <dbname> -SchemaName <schema> -TableName <table>
}


Having issues with SQL with a join issues I have the following code:

SELECT a.oop_action_id,
  a.requestor,
  a.discussion_topic,
  a.action_item_desc,
  a.owner,
  a.partner,
  a.CP_Director,
  a.Chief_of_Staff,
  a.Date_Due,
  c.empl_email secondary,
  b.empl_email prime,
  d.empl_email cpdirector,
  e.empl_email cos,
  a.mail_sent,
  a.status
FROM OOP_ACTION_ITEMS A, EMPLOYEE B,  EMPLOYEE C,  EMPLOYEE D,  EMPLOYEE E
WHERE a.owner = b.LAST_NAME||','||b.FIRST_NAME||' '||SUBSTR(b.MIDDLE_NAME,1,1)(+)
AND a.partner = c.LAST_NAME||','||c.FIRST_NAME||' '||SUBSTR(c.MIDDLE_NAME,1,1)(+)
AND a.CP_Director = d.first_name||' '||SUBSTR (d.MIDDLE_NAME, 1, 1)||' '||d.last_name(+)
AND a.Chief_of_Staff = e.first_name||' '||SUBSTR (e.MIDDLE_NAME, 1, 1)||' '||e.last_name(+);

I'm trying to run this and I keep getting a 'missing keyword' error on line 17

can anyone explain?

It's one of the many limitations of the old syntax for outer joins. On a join condition of the type:

WHERE  a . a_expression  =  b . b_expression (+)

while a_expression can be an arbitrary expression involving more than one columns of table a, b_expression can only be a column of table b. For more details, see the related documentations Joins, where it states:

  The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.

To solve this, you can rewrite with the ANSI join syntax:

FROM  OOP_ACTION_ITEMS A
  LEFT JOIN  EMPLOYEE B
    ON  a.owner
      = b.LAST_NAME||','||b.FIRST_NAME||' '||SUBSTR(b.MIDDLE_NAME,1,1)
  LEFT JOIN  EMPLOYEE C
    ON  a.partner
      = c.LAST_NAME||','||c.FIRST_NAME||' '||SUBSTR(c.MIDDLE_NAME,1,1)
  LEFT JOIN  EMPLOYEE D
    ON  a.CP_Director
      = d.first_name||' '||SUBSTR (d.MIDDLE_NAME, 1, 1)||' '||d.last_name
  LEFT JOIN  EMPLOYEE E
    ON  a.Chief_of_Staff
      = e.first_name||' '||SUBSTR (e.MIDDLE_NAME, 1, 1)||' '||e.last_name ;


Is there a place to store ERDs in Postgres? Is there a designated spot in Postgres to store Entity-Relationship Diagrams (ERDs)?

I could just create a table with a BTYEA column or something, but I want to make sure to use what exists if something already exists to document the database.

I recommend storing the XML of the ERD in a Code Repository (or Document Management System).  Read your Vendor's recommendation.

If you are building your own Document Manager, use a BLOB data type (which seems to be bytea in PostreSQL).


DELIMITER issue in MySQL 5.6 Drop procedure if EXISTS rotatepjp;
DELIMITER //
CREATE PROCEDURE rotatepjp()
 BEGIN
  DECLARE u_id, settingval, pjp_userid, pjp_beatid, u_frequency INT;
  DECLARE  u_pjpstartdate, pjp_fordate, tilldate, newdate DATE;
     SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
     SELECT value INTO settingval FROM settings where name = 'autorotatepjp';
     IF settingval > 0 THEN
       BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE cur1 CURSOR FOR SELECT id, pjpstartdate, frequency FROM users;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        #open cursor
        OPEN cur1;

        #starts the loop
        the_loop: LOOP

            #get the values of each column into our variables
            FETCH cur1 INTO u_id, u_pjpstartdate, u_frequency;
            IF done THEN
                LEAVE the_loop;
            END IF;
            #Do some post processing
            IF u_frequency > 0 THEN
                SET tilldate = DATE_ADD( u_pjpstartdate, INTERVAL u_frequency DAY );
                IF tilldate = CURDATE() THEN
                    BEGIN
                        DECLARE done1 INT DEFAULT FALSE;
                        DECLARE cur2 CURSOR FOR SELECT user_id, beat_id, fordate FROM pjps where user_id = u_id and fordate >= u_pjpstartdate and fordate < tilldate;
                        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;
                        OPEN cur2;
                        the_loop1: LOOP
                            #get the values of each column into our variables
                            FETCH cur2 INTO pjp_userid, pjp_beatid, pjp_fordate;
                            IF done1 THEN
                                LEAVE the_loop1;
                            END IF;
                            SET newdate = DATE_ADD( pjp_fordate, INTERVAL u_frequency DAY );
                            INSERT INTO pjps (user_id, beat_id, fordate, created, modified )
                            VALUES ( pjp_userid, pjp_beatid, newdate, NOW(), NOW() );
                        END LOOP the_loop1;
                        UPDATE users set pjpstartdate = CURDATE(), modified = NOW() where id = u_id;
                    END;
                END IF;
            END IF;
        END LOOP the_loop;
     END;
END IF;
COMMIT ;
END //
DELIMITER ;

this code is working in innodb_version 5.5.34 but not working innodb_version 5.6.21.......any clue, where I am doing mistake???

error - #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1

When creating a Stored Procedure, you do not need COMMIT;.

I have never used it for Stored Procedures.

No one should have to because Stored Procedures are stored in a MyISAM table (mysql.proc)

Please remove the COMMIT; before the END // and try again.

I would also change line to say

DELIMITER $$

and the end clause to

END $$

and try again

I thought you were working in the mysql client. If your problem is SQLFiddle, here is post from StackOverflow by its Author (Execute triggers stored procedures on SqlFiddle. Mysql)

Based on it, stop using DELIMITER altogether. Look for the fourth button under the query pane.

I just installed MySQL 5.6.22 for Windows on my laptop and ran your code as is. It Works !!!

The only culprit left would be phpmyadmin. It cannot handle delimiters against the latest version of MySQL, which is 5.6. You may need an upgrade of phpmyadmin.


Connect to SQL Server 2012 Database from 2008 Agent Job containing 2008 SSIS Package I'm planning the rollout of SQL Server 2012. My client has a number of SQL Server Integration Services (SSIS) packages currently deployed to SSIS 2008 and consumed by SQL Server Agent Jobs defined on an instance of SQL Server 2008.

The SSIS packages (and the databases they connect to) will be upgraded to SQL Server 2012 over several months.

If the packages remain on the SSIS 2008 (consumed by the SQL Server Agent Jobs on the SQL Server 2008 instance), can the 2008 instance Agent connect to the 2012 server?

Yes, most likely. This is a short answer, but it sounds like you are really just asking - Can a job on SQL Server 2008 calling an SSIS package built on 2008 connect to a database in SQL Server 2012 and do some work within that database?

SSIS can connect to many OLEDB/ODBC compliant connections. You could, for instance use an SSIS 2008 (or 2005 or 2012) Package to read data from one Oracle database, do some transformations and the write the results of that to another Oracle database, never talking to SQL except the SSIS engine.

So an SSIS package in 2008 should be able to work with a connection to 2012. Some caveats like - if you are using a deprecated command structure in an execute SQL task - it will fail.. Or test it and verify first. But this should work, if you are just asking if - from within the SSIS package can I talk to SQL Server 2012.


Unable to change innodb_buffer_pool_size MySQL 5.6 in CentOS 6.7 I have searched all over but couldn't find where service mysqld restart picks up the configuration file from:

sudo find / -name "*.cnf" | grep my

/usr/share/doc/mysql-community-server-5.6.33/my-default.cnf
/usr/share/mysql/my-default.cnf
/var/lib/mysql/auto.cnf
/var/lib/mysql/my.cnf
/etc/my.cnf

My MySQL 5.6.33 is running on 1 GB of InnoDB Buffer Pool whereas none of these files has that setting. I have setting

[mysqld]
innodb_buffer_pool_size = 4G

in the file copied from /usr/share/mysql/my-default.cnf to /etc/my.cnf - I did this:

service mysqld restart --defaults-file=/etc/my.cnf

Still not working.

About     Service mysqld start --defaults-file=..

AFAIK it isn't correct way to pass argument. It should not be accepting that argument.
Never done that; importantly it isn't even necessary for default location!!!

Confirm the which my.cnf is being used:

mysqld –verbose –help | grep -C1 my.cnf

Confirm we have correct entry in my.cnf

grep innodb_buffer_pool_size /etc/my.cnf

Verify your error log, you should see something like

InnoDB: Initializing buffer pool size = ...


Tutorial in datamining Where can I find about tutorial of datamining in SQL server 2012 or 2008 R2?

The purpose is to improve my knowledge in datamining.

this book is useful

http://www.amazon.com/Data-Mining-Microsoft-Server-2008/dp/0470277742/ref=pd_sim_b_3

and here is some reviews and nice articles about it

http://blogs.msdn.com/b/jamiemac/archive/2008/10/02/data-mining-with-sql-server-2008.aspx

http://richardlees.blogspot.com/2008/12/book-review-data-mining-with-sql-server.html

slideshow
http://www.slideshare.net/PeterGfader/10-data-mining

--

i also suggest watching these video tutorials by Learn it first
as a start they can give you a basic but useful introduction

Data Mining with Analysis Services
http://www.learnitfirst.com/Course/165/Chapter/292/Data-Mining-SSAS.aspx

http://www.learnitfirst.com/Course/165/SSAS-2008.aspx

--

chapter 11 of BI Architecture and Design Guide (best practices)
is focused on Data mining

http://biarchanddesignguide.codeplex.com/releases/view/6572


Copy a Large INNODB table in MySQL I have an INNODB table that's > 93 million rows. A lot of the data is considered "temp" data and is governed by an "is_active" flag of 1/0. When a user updates a form, new data is written with an "is_active=1" and the previous active records are updated to "is_active=0".

We wanted to move the data to a new table to clean up and ran statement like...

INSERT INTO tblNew (a, b, c)
SELECT a, b, c FROM tblOld WHERE is_active=1

This ran overnight and when I looked in the morning I noticed there were a bunch of processes backed up in the SHOW PROCESS LIST so I did a KILL on the process on the ID, which started the ROLLBACK and brought the server down for another 10 hours... production box of course.

I've been reading a lot on how you can try to repair, etc. and have been doing that all day, but I'm wondering if there's any kind of option I could have added to avoid the need for rollback on failure? Or is there a strategy commit or flush every X number of rows, etc.

I was trying this...

INSERT INTO tblNew (a, b, c)
SELECT a, b, c FROM tblOld WHERE is_active=1 AND pkID > 0 AND pkID < 1000000

Where the pkID was the primary key. I would run it in groups of 550k - 1M and up the number range for PK each run. There's an index on the PK and on is_active, yet I noticed speeds increased each run from 30 seconds to over 5 minutes a run by time it was in the 20M range. Any idea why this would take longer each run when it's the same number of rows for the work?

So 2 in summary, questions...

1) Can I do something to keep a huge rollback from happening if I stop the process?

2) Why did inserting the same number of items based on PK and indexed column take progressively longer per run?

Thanks

What percentage of the table is_active normally?

The following should avoid having to do massive updates to flip that flag:

Consider abandoning the is_active flag in the main table.  Instead have a separate table with PRIMARY KEY matching the main table, plus (optionally) a timestamp (I'll get back to that in a minute).

Instead of setting is_active, INSERT a row in the new table.

Instead of turning off is_active, DELETE the row.

To check for [in]active, use LEFT JOIN plus WHERE id IS [NOT] NULL.

The TIMESTAMP is to deal with programming errors that insert a row but somehow forget about it.  Also add a user_id column for debugging, if relevant.


Questions around migrating to a live replicated database from an existing log shipping database in SQL Server 2016 SP1 I have two large databases, one on my production server named S1 and the other on my reporting server named S2.

S1 is the live database used for our customer orders and accounting, and S2 is the reporting database, which is a one-way, read-only copy of S1.

Every 2 hours, S2 is taken offline, then a log shipping job runs on S1 and updates the last 2 hours of transactions into S2 on the reporting server. It works, but isn't quite up to date enough any more for my users' needs, and having it go offline every 2 hours and interrupt any queries running at that point is less than ideal, so they end up running queries on S1 and causing untold problems.

I need to set up simple replication such that S2 is updated as soon as there are any changes in S1. I've never done this before so I'm quite nervous.

I can set up a new database on my reporting server, there's plenty of capacity, so I'm in the process of setting up a live read-only transactional replication named S3. When I'm happy it's working and matches S2, I just want to do a straight swap and start using S3 for all my reports etc.

Is it as simple as just detaching S2, then renaming S3 to S2?

Provided the tables and indexes etc were all identical, Would all my users' queries just work at that point?

Instead of doing this, is it a better idea to reconfigure the existing DB from a log shipping model to a transactional model, or is that painful?

Your requirement is real-time data synchronization from S1 to S2 for users requirement of realtime reports.

  It works but isn't quite up to date enough anymore for my users'
  needs, and having it go offline every 2 hours and interrupt any
  queries running at that point is less than ideal, so they end up
  running queries on S1 and causing untold problems.

So you have 2 options other than Log-Shipping.

Transactional Replication
AlwaysOn

Transactional Replication

Advantages:

Allows you to replicate all or required data by adding condition while adding an article to the publisher.
We can create indexes on the subscriber (S3/S2) according to the requirement of reports queries to improve report performance.

disadvantage

We need to run index maintenance script on both servers separately.
Separate FULL & T-Log backup of the subscriber database maxes redundant backup of same data.
Distribution database maintenance like index rebuilding/reorganizing, a database full & transaction log backup to make sure transaction log don't grow to consume available space.
For large OLTP database, replication performance becomes a challenge.
Above all handling, replication is quite tough.

AlwaysOn

Advantages:

Read-Only secondary database.
All database maintenance script will be executed on the primary database.
You can choose the preferred database server to execute a backup.
No need to execute T-log backup of the database across all nodes.
Easy to manage.

Disadvantage:

You can't create additional indexes on the secondary replica according to the requirement of report queries.

  Is it as simple as just detaching S2, then renaming S3 to S2?

If all SQL Server credentials, Security, and settings are same, then yes you can simply detach S2 and rename S3 to S2. make sure computer name/object name (S2) is updated at DNS server and redirecting to the IP address of new S2(renamed from S3) server.

Or

You can also CName a CName connecting to current S2. Once S3 is ready you can redirect the CName to connect S3.

This way wouldn't have to make any changes in the applications connection string whenever you'll be migrating DB server. Only you'll need to update CName on the DNS server.

  Provided the tables and indexes etc were all identical, Would all my
  users' queries just work at that point?

If you have all database objects (tables, views, type, function, stored procedures and triggers) on S2 identical to S1, Yes it will work properly.

  Instead of doing this is it a better idea to reconfigure the existing
  DB from a log shipping model to a transactional model, or is that
  painful?

I would say Yes it would be better to reconfigure Log-Shipping secondary DB to the subscriber of transactional replication.

Reason: DB on S2 is already in sync so if you initialize subscription from backup, subscriber database will take very less time to sync in comparison to the lengthy process prepare S3 server.

Thanks!


SQL Server Management Studio Error Access Denied Editing Job Step I recently changed the owner of a job from my user to sa. After the ownership change I am able to edit the Schedules and Notifications for the job. However all attempts to to create New, Insert, Edit, or Delete job steps result in the window closing with the error 'Access Denied'.
I tried changing the owner back to my user and also granted my user the SQLAgentOperatorRole in msdb, but the 'Access Denied' error persists when changing job steps.

This error also occurs when creating a new job. It's impossible to Create a job step.

Any idea how to resolve this?

Thanks

I though the problem was with SQL Server, but it appears to have been caused by some bug in SSMS. Rebooting my PC solved the problem.


Selecting most recent date when dates are stored in another table Working in a MySQL database, I have tables set up like this:

Table song_instances has song_id (int), meeting_instance_id (int)

The meeting_instances table has columns meeting_instance_id (int), meeting_date (datetime), meeting_id (int).

Each meeting_id (referencing the meetings table) might have numerous meeting_instances, and each meeting_instance might have numerous songs associated with it.

I'm trying to get a list of all songs from song_instances that are related to a particular meeting type (meeting_id)and include the date of the most recent date that the song has been used. Because the meeting_id and the meeting_date are only referenced in the meeting_instances table, I am joining that table in. But every attempt falls short.

Example:
song_instances:

song_id     meeting_instance_id
1           1
1           2
2           2
2           3
3           1
1           5
3           5

Meeting Instances:

meeting_instance_id      meeting_id       meeting_date
1                        4                2014-09-01
2                        4                2014-09-02
3                        2                2014-09-03
5                        4                2014-09-04

I want to get back:

song_id         lastDate
1               2014-09-04
2               2014-09-03
3               2014-09-04

etc.

Here is my most recent (and closest to successful) attempt.

SELECT si.song_id, mi.lastDate FROM song_instances si LEFT JOIN
        (SELECT MAX(meeting_date) as lastDate, meeting_id, meeting_instance_id FROM meeting_instances WHERE meeting_id = 4 GROUP BY meeting_instance_id) as mi
    ON si.meeting_instance_id = mi.meeting_instance_id
    GROUP BY si.song_id ORDER BY lastDate DESC

This currently gives me the date farthest in the past rather than the most recent date.
Not sure why. Any thoughts on how to improve this query?

This is a derived table from a much larger query, that is selecting song names and topics from other tables, but this is the only part of the whole query that is not working as intended.

Please try the following.

If you need most recent date for every song over all meeting instances:

select song_id, max(meeting_date)
from song_instances si
    join meeting_instances mi on mi.meeting_instance_id = si.meeting_instance_id
group by song_id

If you're interested only in particular meeting (meeting_id = 4):

select song_id, max(meeting_date)
from song_instances si
    join meeting_instances mi on mi.meeting_instance_id = si.meeting_instance_id
where meeting_id = 4
group by song_id


How use raise notice in nested and sub-function? I'm using PgAdmin to create multiple pgsql functions.
I'm beginner on this environment.
I'm using 'raise notice' to help me for debugging phase.

I have two functions with 'raise notice' statement.
The first function calls the second function.

I can show the 'raise notice' statement for the first function.
I can't show the 'raise notice' statement for the second function. In place, the console show blank lines for each 'raise notice' that should be shown.

Function 1 - Example :

raise notice 'Function 1 executing';    -- This raise notice works
PERFORM public.function_2();

Function 2 - Example :

raise notice 'Function 2 is now running';   -- This raise notice doesn't work

Is it possible to do what I want?

Yes it is possible. I have tested the following with PG 12.2 and with psql CLI:

show client_min_messages;
client_min_messages
---------------------
notice
(1 row)
create or replace function f2 () returns text
as
$$
declare
begin
 raise notice 'Function 2 is now running';
 return 'f2: OK';
end;
$$
language plpgsql;
CREATE FUNCTION
create or replace function f1 () returns text
as
$$
declare
begin
 raise notice 'Function 1 executing';
 perform f2();
 return 'f1: OK';
end;
$$
language plpgsql;
CREATE FUNCTION
select f1();
psql:t2f.sql:27: NOTICE:  Function 1 executing
psql:t2f.sql:27: NOTICE:  Function 2 is now running
   f1
--------
 f1: OK
(1 row)