Calculate months between two dates For a University course I've been given the following task

Suppose that we have an employee table like (first_name, last_name, salary, hire_date, ...). There is regulation which states that every employee each year after he/she is hired, must renew  his contract with  company. Show how many months remain before the next renewal for each employee.

This is my attempt:

select (abs(months_between(sysdate,hire_date)/12 -
         round((months_between(sysdate,e.hire_date))/12)))
from employees e

There are more efficient ways of doing this if your employee table is large, but this is the way I find easiest to understand :)

testbed:

create table employee( employee_id integer primary key,
                       name varchar(100) not null,
                       hire_date date not null );

insert into employee(employee_id, name, hire_date)
values(1, 'Alice', to_date('20090909', 'YYYYMMDD'));

insert into employee(employee_id, name, hire_date)
values(2, 'Bob', to_date('20101010', 'YYYYMMDD'));

insert into employee(employee_id, name, hire_date)
values(3, 'Chris', to_date('20111111', 'YYYYMMDD'));

insert into employee(employee_id, name, hire_date)
values(4, 'David', to_date('20101231', 'YYYYMMDD'));

query:

with w as ( select e.*, ( select max(add_months(hire_date, 12*level))
                          from dual
                          connect by add_months(hire_date, 12*(level-1))<sysdate )
                      as next_anniversary
            from employee e )
select w.*, round(months_between(next_anniversary, sysdate)) as months_from_now from w;

result:

EMPLOYEE_ID            NAME       HIRE_DATE                 NEXT_ANNIVERSARY          MONTHS_FROM_NOW
---------------------- ---------- ------------------------- ------------------------- ----------------------
1                      Alice      09-SEP-09 00.00.00        09-SEP-12 00.00.00        10
2                      Bob        10-OCT-10 00.00.00        10-OCT-12 00.00.00        11
3                      Chris      11-NOV-11 00.00.00        11-NOV-12 00.00.00        12
4                      David      31-DEC-10 00.00.00        31-DEC-11 00.00.00        1

You may prefer floor or ceil to round.


SQL Server License Migration We need to move our SQL Server 2016 standard License. Our plan is to install SQL Server on the new box with the same license key , test the application / databases and then delete the old SQL server after few days
Here is my question can the migration be done in parallel without uninstalling the old server  or we have to uninstall the old SQL server first then install the SQL server with the same key on another box.

It is possible but you will have two SQL server in place with only half licenses needed.
More appropriate would be to use an evalutation license (180 days) and the upgrade it to the proper full licence.
To do this use the proper DVD/ISO, Launch SQL Server "setup.exe", select maintenance tab and select edition upgrade.


Is there a standard way to replace codes with values from a lookup table for reporting or analytics? Suppose I have a "main" table that was probably heavily normalized and consists largely of columns that merely contain codes that are lookups into other tables (they are probably foreign keys but feel free not to assume so in your answer if it makes sense).  For instance, maybe this main table is a list of people and one of the columns is for favorite_color.  Of course then our lookup table would looks something like this:

color_cd    color_name
0           Unknown
1           Red
2           Blue
3           Orange
4           Other

Now if I'm creating reports from this main table or perhaps feeding this data into some software for domain experts to use, I'd rather replace the favorite_color code with the color_name from the lookup table.  One solution of course is to do this with a simple JOIN in my SELECT:

SELECT main.col1, main.col2, colors.color_name as `Favorite Color`
  FROM main LEFT OUTER JOIN colors ON (main.favorite_color = colors.color_cd);

This gets the job done.  Now suppose that the main table has dozens or maybe hundreds of such columns and millions of rows.  Some of the lookup tables are small, like the above example, but others might have hundreds or thousands of values.

Is there any sort of standard way of doing this transformation, or is the writing a long with dozens or hundreds of joins the way to go?  Is this type of thing a typical component of any ETL product?  Is it preferable to write a script that handles this without joins and outside of SQL?  Please forgive what are likely some abuses or misuses of terminology; I'm not even sure if the above process has a name.

In the ETL process, you can replace the codes with names while loading a target table. I'll focus on Informatica PowerCenter but I'm sure other ETL tools offer a similar feature.

There is a Lookup transformation that is used to look up values (DNAME) from a a relational table (it may also be a view or a flat file), based on a defined lookup match criteria (source.DEPTNO = lookup.DEPTNO). These values may then be appended to the source rows and stored in a target table (that is used for reporting).

When a session is executed, a SELECT statement is generated for each lookup in the mapping. These statements are run against the data source once and the results are stored in the lookup cache. Later, when a value needs to be looked up, the transformation uses the cache.


How can I find the difference between queries that return multiple rows? I have two tables, website and page, that look like such:

website
id | name
------------
1  | site 1
2  | site 2
3  | site 3

page
id | name | site_id |  deleted
----------------------------------
1  | home|    1     |     0
2  | pg2 |    1     |     1
3  | pg1 |    2     |     0
4  | pg2 |    2     |     0
5  | pg1 |    3     |     1
6  | this|    3     |     1

In the example, site 1 has one deleted page and one active page; site 2 has two active pages; site 3 has no active pages.  The goal is to return a list of websites that have no actives pages.

I have the following query that gives me the number of deleted pages for each site:

SELECT w.id, w.name, count(p.id) AS deleted_pages_num
FROM page p
RIGHT JOIN website w ON p.site_id = w.id
WHERE p.deleted = 1
GROUP BY p.site_id

Where you'd get this result

id| name   | deleted_pages_num
-------------------------------
1 | site 1 | 1
2 | site 2 | 0
3 | site 3 | 2

and vice versa where you'd set p.is_deleted = 0 to get the number of active pages each website has.  I need to find the difference between the two queries so that I can identify which sites have had all pages deleted.  Just totally blanking on this.

Try to use the following:

select * from website a where not exists (select null from page where site_id=a.id and deleted=0)

Probably this is what you're asking for


Bypassing version store for changing column datatype Database is Always On High Availability synchronised database on SQL Server 2019 with READ_COMMITTED_SNAPSHOT on and we are updating datatype from varchar to nvarchar (with "alter table" command) on a column on a large (1 TB) table.
It blew up our tempdb due to version store.
I understand why this happened, but really just looking at (maybe creative) ways to stop this happening (ideally to bypass the version store for this command).
I'm considering recommending ADR to at least mean that at least issue is isolated to this particular database, but would love to hear any other ideas.

Prefacing this by saying I think my idea is lame, but it is an idea. :)
You can try creating a new empty NVARCHAR column (of the appropriate size) and do batches of updates from OldVarCharColumn to NewNVarCharColumn at the rate your system can tolerate. You can even automate the entire update by iteratively looping and using a rolling range of the key (assuming it's numerical) for which rows, by setting a variable on each iteration (e.g. WHERE KeyField >= @CurrentIterationValue - 1000 AND KeyField < @CurrentIterationValue) and by using the WAITFOR DELAY command to pause between each iteration for any amount of time you specify (e.g. WAITFOR DELAY '00:05:00.000' will pause execution for 5 minutes).
Once your new column is fully updated you can then DROP the OldVarCharColumn.


Joining two tables with an alias I have the following query which casues some problems: it complains that ORA-00904: "TLEA"."TLSN_TRD_ID": invalid identifier even though TLSN_TRD_ID is clearly in the TLEA table. It is as if the aliasing doesn't work correctly. Both the RECON and TLEA table work separately but when I try to do a left join as below I get the error. Any suggestions what could be the problem?

SELECT * FROM

(
SELECT * FROM DF_RISK_SIT2_owner.RECON_RESULTS
WHERE (RS_ONLY = 1 or VERSION_MISMATCH = 1) AND  REC_SYSTEM = 'SUMMIT'
)RECON

LEFT JOIN

(
SELECT * FROM DF_RISK_CUAT_owner.TLEA
  INNER JOIN
        (SELECT  TLSN_TRD_ID,TLSN_LEG_ID, MAX(TLEA_COB_DT) AS MDate
            FROM    DF_RISK_CUAT_owner.TLEA
            GROUP BY TLSN_TRD_ID,TLSN_LEG_ID
        )MaxDate
            ON MaxDate.TLSN_TRD_ID = DF_RISK_CUAT_owner.TLEA.TLSN_TRD_ID
            AND MaxDate.MDate = DF_RISK_CUAT_owner.TLEA.TLEA_COB_DT
            AND MaxDate.TLSN_TRD_ID='C8112832LX'
)TLEA

ON RECON.RS_TRD_ID =       TLEA.TLSN_TRD_ID

Update:
It appears that the problem is related to duplicate column names:
When i replace the line below the LEFT JOIN with
SELECT MaxDate.TLSN_TRD_ID AS "A_A_TLSN_TRD_ID" and then join on TLSA.A_A_TLSN_TRD_ID it works. Strangely it doesn't work when I do on the join TLEA.MaxDate.TLSN_TRD_ID. Any idea why?

It's because you're using the SELECT * and joining a table to itself. The DB engine will try to return all columns, so it needs to know which columns you are referring to if you have multiple with the same name. If you want both you need to specify them with aliases to indicate which order to output them with.

The reason the aggregation works is because you're not trying to return any duplicated columns. Replace the SELECT * with the columns that you want, using the Aliases to specify which tables you want them from.

As a general best practice, do not use SELECT *; always specify your column names.


Safest way to perform mysqldump on a live system with active reads and writes? I'm not sure if this is true but I remember reading if you run the following command in linux

mysqldump -u username -p database_name > backup_db.sql

while reads and writes are being made to a database then the dump may contain errors.

Are there particular options in the command mysqldump to make sure this is done safely on a live system? I'm okay with reads/writes being disabled for our users for a few seconds (The database <50MB)

All Data is InnoDB
This is what will give you an exact point-in-time snapshot of the data:
mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql

--single-transaction produces a checkpoint that allows the dump to capture all data prior to the checkpoint while receiving incoming changes. Those incoming changes do not become part of the dump. That ensures the same point-in-time for all tables.
--routines dumps all stored procedures and stored functions
--triggers dumps all triggers for each table that has them
All Data is MyISAM or Mix of InnoDB/MyISAM
You will have to impose a global read lock, perform the mysqldump, and release the global lock
mysql -uuser -ppass -Ae"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
sleep 5
mysql -uuser -ppass -ANe"SHOW PROCESSLIST" | grep "SELECT SLEEP(86400)" > /tmp/proclist.txt
SLEEP_ID=`cat /tmp/proclist.txt | awk '{print $1}'`
echo "KILL ${SLEEP_ID};" > /tmp/kill_sleep.sql
mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql
mysql -uuser -ppass -A < /tmp/kill_sleep.sql

Give it a Try !!!
UPDATE 2012-06-22 08:12 EDT
Since you have <50MB of total data I have another option. Instead of launching a SLEEP command into the background to hold the global read lock for 86400 sec (that 24 hr) just to get the process ID and kill outside, let's try setting a 5 second timeout in mysql rather than in the OS:
SLEEP_TIMEOUT=5
SQLSTMT="FLUSH TABLES WITH READ LOCK; SELECT SLEEP(${SLEEP_TIMEOUT})"
mysql -uuser -ppass -Ae"${SQLSTMT}" &
mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql

This is a cleaner and simpler approach for very small databases.


an error in your SQL syntax; check the manual that corresponds to your MySQL server version I am restore a MariaDB 10.4.17 Database to Amazon aurora MySQL 5.6
while creating a table it is showing me this error
ERROR 1064 (42000) at line 25 in file: 'name.sql': 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 'sha2(uuid(),256),
  `InsertTs` timestamp NOT NULL DEFAULT current_timestamp(),
 ' at line 17
Query OK, 0 rows affected (0.001 sec)

error is in the line 'sha2(uuid(),256)'
CREATE TABLE `User` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `Version` bigint(20) DEFAULT 0,
  `FirstName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `LastName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `UserName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Password` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PasswordSalt` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PasswordExpires` datetime DEFAULT NULL,
  `ForcePasswordChange` bit(1) DEFAULT b'0',
  `Email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Phone` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `NickName` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `TextNumber` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `IsValid` bit(1) DEFAULT b'0',
  `IsMigrated` bit(1) DEFAULT b'1',
  `UUID` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT sha2(uuid(),256),
  `InsertTs` timestamp NOT NULL DEFAULT current_timestamp(),
  `UpdateTs` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `PIN` bigint(20) DEFAULT NULL,
  `LatestInviteStatus` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `LatestInviteDate` date DEFAULT NULL,
  `LatestInviteUUID` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `uk_User_UserName_UNIQUE` (`UserName`),
  UNIQUE KEY `uk_User_UUID_UNIQUE` (`UUID`),
  KEY `idxPIN` (`PIN`)
) ENGINE=InnoDB AUTO_INCREMENT=90184 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I cannot figure out what it is on workbench it says
sha2 is not valid at this position

Your CREATE TABLE will fail because there is no PIN column mentioned in KEY `idxPIN` (`PIN`) line.
MySQL 5.6 does not allow an expression as default value. Use trigger:
CREATE TABLE `Customer` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `Version` bigint(20) DEFAULT 0,
  `FirstName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `LastName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `UserName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Password` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PasswordSalt` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PasswordExpires` datetime DEFAULT NULL,
  `ForcePasswordChange` bit(1) DEFAULT b'0',
  `Email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Phone` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `NickName` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `TextNumber` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `IsValid` bit(1) DEFAULT b'0',
  `IsMigrated` bit(1) DEFAULT b'1',
  `UUID` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `InsertTs` timestamp NOT NULL DEFAULT current_timestamp(),
  `UpdateTs` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`ID`),
  UNIQUE KEY `uk_User_UserName_UNIQUE` (`UserName`),
  UNIQUE KEY `uk_User_UUID_UNIQUE` (`UUID`),
-- -------------
`PIN` INT,
-- -------------
  KEY `idxPIN` (`PIN`)
) ENGINE=InnoDB AUTO_INCREMENT=90184 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TRIGGER set_uuid
BEFORE INSERT
ON Customer
FOR EACH ROW
SET NEW.UUID = COALESCE(NEW.UUID, sha2(uuid(),256));


Generate backup script of SQL Agent Jobs using c# I am using C# code to read all the SQL Agent Jobs:

static readonly string SqlAgentJobName = "SqlAgentJob_123";
static ServerConnection conn;
static Server server;
static Job job;

try
{
    conn = new ServerConnection(SqlServer);
    server = new Server(conn);
    job = server.JobServer.Jobs[SqlAgentJobName]; //Get the specified job
    GenerateScript(job); // I need idea to write this method.
}
catch (Exception ex)
{
    Console.WriteLine("Failed to fetch the job :" + ex.Message);
    throw ex;
}

How can I generate a script out of it, so that I can run it on any server?
When I run the program, it should read all jobs from the server and generate a script.

Is there any built-in method? Will SQL Server Development Tools help?

You can use the Script method of the Scripter object in SMO to achieve that.

An example can be found here.


Sum Up values in a query based on other information I am trying to grab the sum of 2 columns if 1 column is the same. I currently have a record-set that looks like this:

I get these results by running this statement:

 select distinct a.eventnum, a.num_cust, a.out_minutes, d.xpers
 FROM mv_outage_duration a
 INNER JOIN mv_aeven d
 ON d.Num_1 = a.eventnum
 and (d.DEV_NAME = 'T007F12127')
 and d.rev_num = (select max(rev_num)
 from mv_aeven d
 where a.eventnum = d.Num_1)
group by a.eventnum, a.num_cust, a.out_minutes, d.xpers

How do I get the sum of Num_cust and Out_Minutes for the record if the eventnum is the same?

I'd like to return 1 and only 1 row for each event number, and if there are more than 1 step, I'd like to add the Num_cust and Out_minutes for each step.

I've tried

 select distinct a.eventnum, a.num_cust, a.out_minutes, d.xpers, sum(a.Num_cust)
 FROM mv_outage_duration a
 INNER JOIN mv_aeven d
 ON d.Num_1 = a.eventnum
 and (d.DEV_NAME = 'T007F12127')
 and d.rev_num = (select max(rev_num)
 from mv_aeven d
 where a.eventnum = d.Num_1)
group by a.eventnum, a.num_cust, a.out_minutes, d.xpers

and it just returns the results as a new column sum(a.num_cust).

I also tried

 select distinct a.eventnum, a.num_cust, a.out_minutes, d.xpers, sum(select Num_cust from mv_outage_duration a where a.eventnum = d.num_1)
 FROM mv_outage_duration a
 INNER JOIN mv_aeven d
 ON d.Num_1 = a.eventnum
 and (d.DEV_NAME = 'T007F12127')
 and d.rev_num = (select max(rev_num)
 from mv_aeven d
 where a.eventnum = d.Num_1)
group by a.eventnum, a.num_cust, a.out_minutes, d.xpers

...but that just wouldn't run at all.

Here's some statements to set everything up

Create table mv_outage_duration( eventnum, num_cust, out_minutes, restore_dts, off_dts, cause, feeder, dev_name)
create table mv_aeven (Num_1, rev_num, xpers, weather_code, completion_remarks)

Insert into "mv_outage_duration" (EVENTNUM,NUM_CUST,OUT_MINUTES,RESTORE_DTS,OFF_DTS,CAUSE,FEEDER,DEV_NAME) values ('T00000000133',79,11,'20130307085914CS','20130307084811CS','10','17FL012011','T007F12127');
Insert into "mv_outage_duration" (EVENTNUM,NUM_CUST,OUT_MINUTES,RESTORE_DTS,OFF_DTS,CAUSE,FEEDER,DEV_NAME) values ('T00000000133',61,13,'20130307090200CS','20130307084811CS','10','17FL012011','T007F12127');
Insert into "mv_outage_duration" (EVENTNUM,NUM_CUST,OUT_MINUTES,RESTORE_DTS,OFF_DTS,CAUSE,FEEDER,DEV_NAME) values ('T00000000014',61,4,'20130304140400CS','20130304135945CS','09','17FL012011','T007F12127');
Insert into "mv_outage_duration" (EVENTNUM,NUM_CUST,OUT_MINUTES,RESTORE_DTS,OFF_DTS,CAUSE,FEEDER,DEV_NAME) values ('T00000000173',79,1,'20130307161532CS','20130307161424CS','01','17FL012011','T007F12127');
Insert into "mv_outage_duration" (EVENTNUM,NUM_CUST,OUT_MINUTES,RESTORE_DTS,OFF_DTS,CAUSE,FEEDER,DEV_NAME) values ('T00000000173',61,3,'20130307161800CS','20130307161424CS','01','17FL012011','T007F12127');

Insert into "mv_aeven" (NUM_1,REV_NUM,XPERS,WEATHER_CODE,COMPLETION_REMARKS) values ('T00000000014',10,796072,'LIGHTNING IN AREA','COMPLETETION REMARKS FROM TRUCK ON TOFS. ');
Insert into "mv_aeven" (NUM_1,REV_NUM,XPERS,WEATHER_CODE,COMPLETION_REMARKS) values ('T00000000014',11,796072,'NORMAL FOR SEASON','COMPLETETION REMARKS FROM TRUCK ON TOFS.');
Insert into "mv_aeven" (NUM_1,REV_NUM,XPERS,WEATHER_CODE,COMPLETION_REMARKS) values ('T00000000173',7,79607,'LIGNTNING IN AREA','wetr');
Insert into "mv_aeven" (NUM_1,REV_NUM,XPERS,WEATHER_CODE,COMPLETION_REMARKS) values ('T00000000173',6,79607,'LIGNTNING IN AREA','wetr');
Insert into "mv_aeven" (NUM_1,REV_NUM,XPERS,WEATHER_CODE,COMPLETION_REMARKS) values ('T00000000133',7,796072,'THUNDERSTORM','Testing Step Restore for Kasey');
Insert into "mv_aeven" (NUM_1,REV_NUM,XPERS,WEATHER_CODE,COMPLETION_REMARKS) values ('T00000000133',6,796072,'THUNDERSTORM','Testing Step Restore for Kasey');

Here is a solution that uses analytics to prevent having to do two scans of mv_aeven.  It does require more CPU to save the disk access and is a bit more complex, so you will have to weigh it accordingly.

SELECT * FROM
(
  SELECT a.eventnum, sum(a.num_cust), sum(a.out_minutes), a.off_dts, max(a.restore_dts)
     , a.cause, a.feeder, a.DEV_NAME
  FROM mv_outage_duration a
  WHERE a.DEV_NAME = 'T007F12127'
  GROUP BY a.eventnum, a.off_dts, a.cause, a.feeder, a.DEV_NAME
) a
INNER JOIN
  (
  SELECT Num_1, xpers, weather_code, completion_remarks FROM
     (
     SELECT Num_1, xpers, weather_code, completion_remarks, rev_num
        , max(rev_num) OVER (PARTITION BY Num_1) max_rev
     FROM mv_aeven
     )
  WHERE rev_num = max_rev
  ) d
ON d.Num_1 = a.eventnum;

SQL Fiddle (Includes column type definitions).


MySql Cannot connect after installation I am new to MySQL.

I installed MySQL through dmg file downloaded from oracle in mac. I created my first connection also but now whenever I am trying to connect it again it not allowing me.

I read over internet that I need to ALTER root password. So I tried many ways of doing it but either it says UPDATE Command not found or Access denied to root@localhost...

Can someone please suggest a solution?

My issue got resolved using the information in URL below:

https://vladster.net/en/instructions/install-mysql-mac/

Steps taken:

Changed to MySQL directory:
cd /usr/local/mysql
Since my initial root password was expired and was not able to connect to root, so ran the installation again:
sudo bin/mysql_secure_installation
After providing machine password it automatically asked for new root password.

Above steps resolved my issue.


Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT? This is a followup question from: https://stackoverflow.com/questions/7684477/is-it-possible-to-set-transaction-isolation-level-snapshot-automatically

I'm still having deadlock/timeout situations in the ASP.NET application when running large reports simultaneously although READ_COMMITTED_SNAPSHOT ON.

So I have two questions:

How can i check if the Transaction Isolation Level Snapshot is working as expected/at all?
I'm assuming that the foreign keys (in the tables of the Web-Application to the report-tables) are responsible for deadlocks. I found this interesting article:

  Note SQL Server acquires shared locks when validating foreign keys,
  even if the transaction is using read committed snapshot (read
  committed using row versioning) or snapshot isolation level. Be
  mindful of this when examining deadlock graphs from transactions when
  these transaction isolation levels are used. If you see shared locks,
  check to see whether the locks are taken on an object that is
  referenced by a foreign key.

How can I check if the FK are really responsible for the Deadlock/Timeout situations, does that mean i could delete those foreign keys to prevent deadlocks(what would be an acceptable effort)?

Note: I'm only reading from the tables which cause deadlocks.

Any thoughts on this topic are greatly appreciated.

Edit Here is a Deadlock-Graph. Maybe somebody could help me to understand what causes the deadlock. It seems that it occured without any reports running only caused by the web-application, when two transactions want to write the same table(one update and one insert, the insert is as Stored-Procedure). Why does it aquire page locks and how to enable row locks only? The Insert-SP uses already TRANSACTION ISOLATION LEVEL REPEATABLE READ.

I have a strong suspicion that two triggers(one update and one insert) are responsible for the deadlocks. Here is the insert-trigger:

CREATE TRIGGER [dbo].[CreateRMAFiDates]
   ON  [dbo].[RMA]
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE RMA
    SET [fiCreationDate]=(SELECT idDate FROM tdefDate
        WHERE CONVERT(VARCHAR, INSERTED.Creation_Date, 112) = tdefDate.Text),
        [fiPopDate]=(SELECT idDate FROM tdefDate
        WHERE CONVERT(VARCHAR, INSERTED.POP_Date, 112) = tdefDate.Text),
        [fiManufactureDate]=(SELECT idDate FROM tdefDate
        WHERE CONVERT(VARCHAR, INSERTED.Manufacture_Date, 112) = tdefDate.Text)
    FROM INSERTED;
END

So this trigger updates the RMA-Table what causes the update-trigger to fire(what does similar). Does the deadlock-graph confirms my assumption? I think i'll delete those triggers and create a SP that is running once a day what would be perfectly sufficient, because these column are for a SSAS-Cube(Molap) only.

Edit: By the way, there was no deadlock anymore since i deleted these triggers :)

If the SQLCAT team says that FK validation is done using read-committed isolation, then they must know what they're talking about. Emphasis on validation. The real question is Why would a report trigger FK validation? Validation occurs on writes, and reports are supposed to be reads. Either your reports are causing writes, in which case snapshot isolation levels will help nothing, either the cause of the deadlock is different.

The only way to make progress is to capture the deadlock graph.

As for the other question, how can you check if you operate under snapshot isolation: look in sys.dm_tran_active_snapshot_database_transactions.


phpMyAdmin and mysql not showing the same database I have setup WAMP which I managed by using a tutorial and everything seems fine... I am logging into mysql as the root user with full privileges granted and I am able to create databases in mysql and view them by using SHOW DATABASES; so far so good !

...However...

When I access phpMyAdmin and go to the databases section, there are no databases which I have created in mysql, even though phpMyAdmin shows the Database server as User:root@localhost which is the same user I am accessing mysql with.

This situation is also the same when reversed, I can create a database as the root user in phpMyAdmin but the database will not show in mysql when I am logged in as root user and use SHOW DATABASES;

i have tried every soution available ONLINE .. most of them saying about

$cfg['Servers'][$i]['hide_db'] = '';

i have done that too .... but nothing seems to work.... plzzzz help

The database server that you are accessing using phpmyadmin and the mysql monitor (command line client) are different. If you look at the versions, phpmyadmin is accessing a 5.5.1-m2-community version, while you are accessing 5.6.17-community on the command line.

Either you are accessing a remote server or you have two separate installations of mysql on the server. If the later, use netstat -aon | more or the Task Manager to locate the mysql processes running on your machine (and on which port they are listening).


Export data tables from SQLPlus Is there any way to export data from an Oracle SQLPlus console, without using the exp or expdp commands? I don't have sysdba privileges.

I'm not a real Oracle guru so I'm using Navicat to export but it just takes too long. I would prefer to do it locally from command line, but don't know how.

I have 1 day until they close down the server and I really need that data.

SQL*Developer is a decent tool for this, but can still take some time.

If you're looking at SQL Plus, I'd recommend that you use spool, and make liberal use of SQL Plus's set command to format your output into flat files. For delimiting the flat files, you can make use of the concatenate feature to add in column headers. Here's an example:

select 'col1,col2,col3,col4'
  from dual
union
select col1||','||col2||','||col3||','||col4
  from some_table;

HTH.


To use or not use ISNULL(NULLIF(Field1, ''))? In the past I've seen use of something such as

SELECT ISNULL(NULLIF(Field1, ''), 'NewValue')

to tersely get a fallback value.

However, since the advent of CASE in TSQL, we've favored something more like

SELECT CASE Field1 WHEN '' THEN 'NewValue' ELSE Field1 END

Is one going to perform better than the other? What other reasons would there be to choose one over the other?

The primary difference between your examples is that the latter does not replace NULL with your default value.

With regard to performance, you will generally need to try very hard to find a measurable difference (in the order of a few seconds over millions of rows). Search for performance measures for isnull, coalesce, and case--you'll find lots of blog posts about them.

The common voice I've heard is that you should use the structure that feels the most readable for your team, and any time you get the feeling that it might not be as performant as it could be, test it. Run it both ways, and compare the time it takes to complete, and compare the execution plans.


Multiple types of parts with multiple types of information (unspecified amount) I'm having trouble designing a database for a project I was working on recently. It's to do with a garage management system.

So I've realised I need to store multiple types of parts (Tyres, engines, wheel bases, this list needs to grow), with multiple types of details, some that each part shares (name, part number, condition, quantity) and some that are unique to each part (engines have weight and power, tyres have size and alloy type) and that also needs to grow too. This can't be limited to just 2 kinds of unique information, it needs to be infinite, so that more can be added at a later date if required

How can I design a database that is capable of storing all of this information without having tables for each specific part, so a table for tyres, a table for engines etc? I want to keep it so the least amount of tables as possible is used, so once designed, no new tables are added as new data is added.

Any help is appreciated.

This is what I've designed as a solution for this problem but I don't think it's right, due to the tables being linked together in a circle.


how to run db.adminCommand() using pymongo for mongodb 3.6 I want to run this command and get it's output using pymongo module.

db.adminCommand({getParameter: 1, logLevel: 1})

or say I want to run this command

db.adminCommand( { setParameter: 1, disableJavaScriptJIT: false } )

basically I want to get the configurations settings using mongodb shell only. I don't have access to configuration file.

On mongo shell:
db.adminCommand({getParameter: 1, logLevel: 1})

{ "logLevel" : 0, "ok" : 1 }

On python:
from pymongo import MongoClient

server = 'localhost'

port = 27017

client = MongoClient(server, port)

print(client.admin.command(({'getParameter': 1, 'logLevel': 1})))

{'logLevel': 0, 'ok': 1.0}


Thoughts about Oracle backup procedure for 11.2 (beginner question) Two days ago we "found out" that our old, not taken care of, horribly neglected, Oracle DB server was extremely important to our business.

The only person who knew anything about this server was fired 2 years ago, so it's been running since without maintenance.

Being both relatively new in the office as well as in charge of the whole server infrastructure, I need to get to know this server, with zero Oracle experience.

My questions to you are:

Backups: I've read up a lot about RMAN, Import/Export etc. but I'm having a hard time finding a good overview.  What would I need to backup and how do I restore said data if the machine blows up?
Maintenance: is there something about maintenance that I specifically need to know?
Labbing: is it possible for me to download a copy of this and learn on my local linux machine?

If you think these tasks, for me without any experience, are to daunting, you're free to say so because we've been looking at hiring a consultant for this specific situation. Obviously I'd rather like to learn and solve it myself.

Any pointers to documentation, manuals etc. are very much appreciated!

[I'm not too sure if this is a DBA or ServerFault question actually, I believe it's a very thin line between the two but if you feel this thread is misplaced, feel free to move it.]

first think about what it costs if the database gets lost. For this there are several levels.

lost completely and forever because of lack of backup
lost production because you perform a restore without being able to recover last TX.
lost production because the site was unavailable

For all scenarios there are solutions. Normally I work for sites that won't allow the loss of any data. Most of the times they also want protection against unplanned downtime. This is the ultimate protection. As most things, it also has a price. Most protection has the highest cost. But if you need this, there will be a business case for it. That is why you should start with the question what dataloss costs your company. This will give you a budget.

For documentation start with DBA Essentials
A very nice third party solution for DR can be found here dbvisit standby

Hiring support from a good dba can make a huge difference for your company. A good dba has your business in mind when he/she proposes solutions. Your question a asking a bit much. You need dba support. For an overview pick the Oracle concepts guide. Read it and use it to help you hire a good dba.


SQL Server 2016- Email Alerts fo Errors/ Warnings I am using Microsoft SQL Server 2016

I am trying to set up email alerts for errors and I am getting this error:

07/24/2017 19:42:23,,Error,3111,The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2017-07-24T19:42:23). Exception Message: Cannot send mails to mail server. (A recipient must be specified.).<nl/>),22216,3029,,7/24/2017 7:42:23 PM,sa
07/24/2017 19:41:23,,Error,3110,The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2017-07-24T19:41:23). Exception Message: Cannot send mails to mail server. (A recipient must be specified.).<nl/>),22216,3029,,7/24/2017 7:41:23 PM,sa

However I do have recipients set not sure what I am missing here.

I ran the following query to see logs:

select log_id, event_type, log_date, description, process_id, mailitem_id,
account_id, last_mod_date, last_mod_user
from sysmail_event_log
where event_type = 'error'
order by log_date asc

select * from sysmail_faileditems

however for the 2nd query (failed items) it does have a valid email address in the recipient column. I cannot figure out what I missed in the database mail configuration.

Your question is very broad.  I hope you have some understanding or clarity on how SQL Server alerts/warnings work and what are the things you need to monitor based on your business need.

As a start read this about 3 types of alert that you can configure via SQL Server Agent.

Alerts

There are numerous articles written online about this topic and some scripts are available that you can use once you understand what you need.

If you have replication set up you will see a bunch of predefined alert show up in your SQL Server agent under alerts. Read details below:

Configure Predefined Replication Alerts (SQL Server Management Studio)

There will always be situation that you need to be alerted that is not covered above and you have to roll out your own scripts.  That can be using tsql, Powershell and run via SQL Agent Jobs.  If you manage many SQL Server instances you might have consider having a central monitoring/DBA instance and manage alerts from there.

Few resource I am listing for you to start with:

SQL Server Alerts: Soup to Nuts by Robyn Page and Phil Factor
SQL Server Agent Alerts by Tim Radney
Blitz Result: No SQL Server Agent Alerts Configured
The Ten Commandments of SQL Server Monitoring by Adam Machanic


Sharded MongoDB backup solution I am looking for a way that I can backup my 12 MongoDB shards into a single server. Disk space is not an issue but getting the backups to be on a single node would be the best. I am not sure if there is a way to run more than one MongoDB server on a single machine or if there is a way to have all 12 shards merged into a single MongoDB instance. I am running on AWS and will have the primary data in US-East but want the backups in US-West. I dont mind if there is lag on the backup itself, but trying to avoid the app servers for waiting for the backup to be written.

You need to be a little more clear about your requirements:

Do you want to keep all 12 databases intact (12 instances versus all 12 in a single instance)?
Are you intending to serve any queries from the backup servers?
Do you want multiple snapshots?

You can approach this from many different ways, but I will cover the most common approaches:

Put secondaries in US-West

This approach means you have a full, complete copy of your databases on the west coast.  You can make them hidden and priority 0 to make sure they don't serve slaveOK reads or become primary by accident.  To make them primary (in a disaster recovery scenario) you will need to re-configure the set manually if they are hidden and priority 0.

This is quite an easy approach, though it could be expensive.  You can run multiple mongod processes on a single host but they will be competing for resources and it will still have to be fast enough to keep up with the primaries on the east coast.

Use EBS snapshots

Assuming you are running with journaling enabled, you can just take an EBS snapshot of your data folder (dbpath) and you can back that up to S3 or use it to "seed" hosts on the west coast.

Mongodump

mongodump can be used to dump out all of your data from the Eastern hosts to the Western hosts.  However, the output (BSON files) will need to be restored before it can be used.

Generally I would recommend a combination of the first two in order to combine the best resilience options with the most flexibility.


How create a table inside the selected database in PostgreSQL? This is the command to create a new database,

CREATE DATABASE mydb;

This is the command to create a new table,

CREATE TABLE IF NOT EXISTS mytable ( int a PRIMARY KEY );

But this does not add the mytable inside the mydb. How to add the table inside the database mydb?

But this does not add the mytable inside the mydb. How to add the table inside the database mydb?

You have to connect to the database mydb,

psql -d mydb

Then run the command


Can't list columns or query in MS Access 2003 linked server in SQL Server 2008 R2 using Microsoft Jet 4.0 OLE DB Provider? I can however list the tables...

If I run Exec sp_tables_ex 'LSTest' I get a result with a list of tables.

But if I run

Exec sp_columns_ex 'LSTest', '<one of those table names>'

I get an empty list, and if I try to query them I also get nothing.

If I go into the properties of the Linked Server I have the Be made using this security context and I use the Remote login: and With password: for the workgroup file that I would normally use in MS Access.

If I try to script a query by right clicking the table name in the linked server, instead of a select statement I get the following:

-- [LSTest]...[Tablename] contains no columns that can be selected or the current user does not have permissions on that object.

1. Set your SystemDB File in the Registry

Note that you can only specify ONE of these damn things.

On 32-bit Windows

If your Windows installation on which your SQL Server instance installed is 32-bit which is doubtful...use the following key to set the path to your MS-Access Workgroup file (*.mdw):

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB

On 64-bit Windows

On the other hand if your Windows installation on which your SQL Server Instance is installed, is 64-bit, (more likely) set the path to your MS-Access Workgroup file (*.mdw) here:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Jet 4.0\Engines\SystemDB

2. Create your linked server to MS Access

USE [master]
GO

-- Create a new linked table
EXEC sp_addlinkedserver
@server='LinkedServerToMSAccessMDB',
@provider='Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'E:\DB_local\MyMDB.mdb'
GO

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname='LinkedServerToMSAccessMDB',
@useself='FALSE',
@locallogin=NULL,
@rmtuser='someuser',
@rmtpassword='daspasswd'
GO

3. Make sure that your database credentials are passed to the Workgroup File (.mdw) when running a query from the (.mdb)

Now if you want to make sure that the password is being passed correctly after running this....in the Object Explorer panel, in <server-name>\Server Objects\Linked Servers, right click it and click refresh...your new LinkedServerToMSAccessMDB should now appear in the list, right click it and select Properties

In the window that appears next in the Select a page panel, click Security and then in the For a login no defined in the list above, connections will: radio button list, select the Be made using this security context: (if you don't want to be particular about what network users have access to it....otherwise use the mappings) and then in enter the username and password you would normally use to access MS Access using your .mdw file in the Remote login: and With password: text boxes.

4. Run a Query

After you click OK you should now be able to run a select on the .mdb file so that you can create a VIEW in a schema for some specified user.

SELECT [Field1]
  FROM [LinkedServerToMSAccessMDB]...[T_Table1]
GO


What's the difference between a v$ view and its dba_ equivalent? What's the general difference between a v$ view and its dba_ equivalent? Take, for example, v$tablespace and dba_tablespaces.

The easiest way to think of it is:

DBA_ / USER_ / ALL_ views are built on the data dictionary - they're not available if the database is not mounted and opened.
V$ views tend to run against the instance, and therefore may be available if the database is not mounted, or is not mounted and opened, depending on the nature of the view.

Using your example:

V$TABLESPACE is a view on X$KCCTS, which is an internal memory structure.
DBA_TABLESPACES is a view on the data dictionary table SYS.TS$


SQL server 2012 how to apply a check constraint on multiple columns I would like to ask if it is possible to apply a check constraint on multiple columns using one statement. I mean instead of adding a constraint for each column.

Scenario:
I have a table named 'Buyer' and contains these colums: BuyerId, BuyerName, BuyerSurname, BuyerEmail & BuyerAddress.

I need to apply a no empty string check constraints on BuyerName, BuyerSurname & BuyerEmail columns only.

Can this be done at one go instead of one constraint for each column?

Thanks for your help in advance

Kevin

You can create a table-level check constraint with multiple columns specified in the expression in order to avoid separate check constraints.

This example will not allow an empty string, all spaces, or NULL in these columns:

CREATE TABLE dbo.Buyer(
      BuyerId int NOT NULL
        CONSTRAINT PK_Buyer PRIMARY KEY
    , BuyerName varchar(50) NOT NULL
    , BuyerSurname varchar(50) NOT NULL
    , BuyerEmail varchar(50) NULL
    , BuyerAddress varchar(50) NOT NULL
    , CONSTRAINT CK_Buyer_BuyerName_BuyerSurname_BuyerEmail_NotEmpty CHECK
        ('' NOT IN (BuyerName,BuyerSurname,BuyerEmail) )
    );


Relations, dependencies and normal form I have some issues in understanding about relations and dependencies. I will show two examples and what I want to know:

Assume the relation GTX {G,T,X} with the following dependencies:

G,X -> T
T -> X

or this one:

Relation {W,X,Y,Z}:

X,Y -> Z
Z -> W

So to my question, how do I properly draw a dependency diagram for
this kind of relations? (tutorials or/and explanations are much
appreciated)
How do I see what the primary key is? (I know what a pk is)
In which normal form are the relations?

I have never been able to understand this fully and always got confused by the tutorials I found. I appreciate all answers!

From a set of FDs, you can only infer a set of candidate keys.

A "primary key" is one particular member of that set of candidate keys that you elect to be "primary".  And there are no scientific rules that tell you which one you should choose - you can pick just any key and label it "primary".

In your first example, you should end up with a set of candidate keys

{ {T G} {G X} }

In the second example, you should end up with a singleton set of candidate keys

{ {X Y} }

As for how to arrive at the set of keys, you can take a look at https://stackoverflow.com/questions/5735592/determine-keys-from-functional-dependencies/5736174#5736174


MySQL: What exact commands does each privilege level allow? I am configuring some new logins for users who have already been using the relevant MySQL servers under other logins. I would like to limit their privileges to the minimum each user needs, based on their input and reviewing the logs of what commands they have been executing. I would therefore like to know exactly what commands are allowed by each grantable privilege.

The specification in MySQL's documentation provides some insight beyond the obvious (e.g. "The SELECT privilege enables you to select rows from tables in a database.") but it seems like the information given there is not exhaustive. For example, I see in the logs that a user with only Select_priv enabled also used SHOW VARIABLES and SET but I don't see any documentation of those commands being included in the SELECT privilege.

This answer and this question give clues that there is more to the privileges than their names would directly imply.

UPDATE: @Colin 't Hart's comment is informative in that it explains the ability to execute SHOW and SET regardless of privileges. So my question should be limited to server side commands. I.e. Are there commands not listed in the specification which can be used by users with a given privilege?

show and set are commands of the MySQL client; you can't prevent users from using them.

set global is only allowed if the user has the super system privilege. See dev.mysql.com/doc/refman/5.1/en/grant.html#grant-privileges


Are partition numbers guaranteed to be in order of value? Say I have a partitioned table, set up like so:

CREATE PARTITION FUNCTION PF_Month (DATE) AS RANGE RIGHT FOR VALUES (
  '2017-01-01',
  '2017-02-01',
  '2017-03-01',
  '2017-04-01',
  '2017-05-01',
  '2017-06-01',
);
GO

CREATE PARTITION SCHEME PS_Month AS PARTITION PF_Month ALL TO ([Primary]);
GO

CREATE TABLE Logs
(
     Id           INT NOT NULL,
     DateRecorded DATE NOT NULL,
     FixStatus    INT NOT NULL
);
GO

ALTER TABLE Logs
    ADD CONSTRAINT PK_Logs PRIMARY KEY (Id, DateRecorded)
    ON PS_Month(DateRecorded);
GO

CREATE NONCLUSTERED INDEX [IX_DateRecorded] ON Logs(DateRecorded)
    INCLUDE(FixStatus)
    ON PS_Month(DateRecorded);
GO

If I want to query the logs, in order of date, I have been told I can use the partition number in order to avoid a sort when the results from each partition are joined back together.

SELECT * FROM Logs WHERE ... ORDER BY $PARTITION.PF_Month(DateRecorded), DateRecorded

Are the partition numbers in order of when each partition was created, or are they in order of DateRecorded? E.g. if I were to add another split to the function while it is in use, would ordering by partition number still work?

ALTER PARTITION FUNCTION PF_Month() SPLIT RANGE ('2016-12-01')

I wasn't able to find a formal guarantee of this behavior, but I did find multiple examples — including a modified version of your original example — in which query optimization decisions appear to be made based on a guarantee that partition numbers are in order by value. Here's what I found:

Your example

Your example code doesn't currently compile, and also uses an index that does not cover the sample query. Here's an updated script that contains working code for your original example.

With these updates, we can see that the query plan for your ORDER BY query does not include a sort operator; query optimization appears to rely on the fact that partition numbers are ordered by value in order to omit the extra sort.

-- Create a partition-aligned index that includes all
-- columns in your SELECT statements
CREATE NONCLUSTERED INDEX [IX_DateRecorded] ON Logs(DateRecorded)
     INCLUDE (FixStatus) ON PS_Month(DateRecorded)
GO

-- Now that your index is properly defined, this query produces
-- an ordered index scan without a sort
SELECT * FROM Logs ORDER BY DateRecorded
GO

Documentation

There is strong evidence in the documentation that partition numbers are in order by value, but I agree that the documentation itself is not conclusive. Here are a few pieces of evidence that I found:

The CREATE PARTITION FUNCTION documentation states that the partitions will initially be in order:

  If the values are not in order, the Database Engine sorts them, creates the function, and returns a warning that the values are not provided in order.

The sys.partition_range_values documentation states that boundary_id, which appears to be used to define the partition number, is an

  ID (1-based ordinal) of the boundary value tuple, with left-most boundary starting at an ID of 1.

The $PARTITION documentation stats that

  $PARTITION returns an int value between 1 and the number of partitions of the partition function.

Further Testing

With a bit of testing, we can see that there are multiple cases in which SQL Server makes query plan decisions that appear to rely on the fact that  partition numbers are in order by value.

Here's the full test script, and here are a few of the most relevant queries:

-- The predicate x BETWEEN 4 AND 7 is converted to a range seek
-- on the partition number, strongly indicating that there is a
-- guarantee that partition numbers are in order by value.
-- Seek Keys[1]:
--      Start: PtnId1001 >= Scalar Operator(...[@1]...),
--      End: PtnId1001 <= Scalar Operator(...[@2]...)
SELECT COUNT(*)
FROM dbo.test_partition_ranges
WHERE x BETWEEN 4 AND 7
GO

-- If we change our predicate to use the partition number directly,
-- we see the same seek predicate.
-- Seek Keys[1]:
--      Start: PtnId1001 >= Scalar Operator(...[@1]...),
--      End: PtnId1001 <= Scalar Operator(...[@2]...)
SELECT COUNT(*)
FROM dbo.test_partition_ranges
WHERE $PARTITION.PF_INT_1to10(x) BETWEEN 4 AND 7
GO

-- If we mix conflicting predicates that use values and partition numbers,
-- SQL Server knows that no partitions are eligible.
-- "Actual Partition Count" is 0 and there are 0 logical reads
SELECT COUNT(*)
FROM dbo.test_partition_ranges
WHERE $PARTITION.PF_INT_1to10(x) > 5
    AND x <= 5
GO

-- All 10 partitions are accessed, and ordering by the partition column
-- does not produce a sort! It appears that SQL Server is once again
-- relying on the partition numbers being in order by value.
SELECT x
FROM dbo.test_partition_ranges
ORDER BY x
GO


How to find memory usage by table? I want to discover how much memory is being used by each table. Is this information available in DMVs or elsewhere?

what you can do is

Find out the database that consumes highest memory in buffer pool using below :

SELECT COUNT(*) AS cached_pages_count ,
    ( COUNT(*) * 8.0 ) / 1024 AS MB ,
    CASE database_id
      WHEN 32767 THEN 'ResourceDb'
      ELSE DB_NAME(database_id)
    END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id

Drill down the database using below query :

set nocount on;
set transaction isolation level read uncommitted;
select
   count(*)as cached_pages_count,
   (COUNT(*) * 8.0) / 1024 AS Total_MB_Occupied, -- convert pages into MB -  the page size is 8 KB for sql server
   obj.name as objectname,
   ind.name as indexname,
   obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
inner join
(
    select       object_id as objectid,
                       object_name(object_id) as name,
                       index_id,allocation_unit_id
    from sys.allocation_units as au
        inner join sys.partitions as p
            on au.container_id = p.hobt_id
                and (au.type = 1 or au.type = 3)
    union all
    select       object_id as objectid,
                       object_name(object_id) as name,
                       index_id,allocation_unit_id
    from sys.allocation_units as au
        inner join sys.partitions as p
            on au.container_id = p.partition_id
                and au.type = 2
) as obj
    on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind
on  obj.objectid = ind.object_id
and  obj.index_id = ind.index_id
where bd.database_id = db_id()
and bd.page_type in ('data_page', 'index_page')
group by obj.name, ind.name, obj.index_id
order by cached_pages_count desc

Reference :

A quick look at: sys.dm_os_buffer_descriptors
What's swimming in your bufferpool?
sys.dm_os_buffer_descriptors aggregations -- more detailed info - what objects/structures/files/etc. are consuming the largest amount of space at a given time (or over time) within the Sql Server buffer pool.


ERD - Multiple FKs with only one of them being filled for each record I'm designing a database schema for a music platform.

Some of the entities as follows:

track
album
band
artist

A "track" can have an "artist". It can also have a "band" which makes the artist FK null.

Same goes with "album", an "album" can have an "artist" but if the "album" is from a "band" then the artist FK will be null. That's because an "album" by a "band" can't also have a single "artist" as well.

The two(or three) FKs will never be filled together and only one of them must be filled for each record.

Is this a good design practice?

Thanks

Good design practice is to implement and enforce the rules of the problem domain. So if your rules lead to this design then that's what you've got. Having mutually exclusive values is not uncommon.

They can be implemented in different ways. One is as you describe. Another is to use table inheritance. With this you would have tables track, artist and band as you do currently but also band_track and artist_track. These will have only the foreign keys specific to themselves, defined as not null. The problem then becomes one of enforcing exclusion between band_track and artist_track. Personally, I don't think this approach actually improves our position much.

For my two cents I'd go with what you have. Declare CHECK constraints to prevent illegal combinations within a row (or use triggers if you absolutely must).


How I can impersonate a login with securityadmin server role On MSSQL 2008R2 I have two instances on different servers, and I need sync up the password changes for all logins.
I thing that i could create a store procedure where a user (impersonate) with securityadmin server role change the password.

Obs: CHANGE_PASSWORD is a user with securityadmin server role.

ALTER PROCEDURE SPU_CAMBIO_PASSWORD
(
    @USER VARCHAR(50),
    @PASSWORD VARCHAR(100)
)
WITH EXECUTE AS 'CHANGE_PASSWORD'
AS
    DECLARE @STATEMENT VARCHAR(200)
    SET @STATEMENT= 'ALTER LOGIN '+ @USER+ ' WITH PASSWORD = '''+@PASSWORD+''', CHECK_POLICY =OFF'
    EXEC(@STATEMENT)
GO

Error:
Cannot execute as the server principal because the principal "CAMBIO_PASSWORD" does not exist, this type of principal cannot be impersonated, or you do not have permission.

The bad news is that the EXECUTE AS clause (which you are using), as opposed to the statement, only works for Users (Database-level), and not Logins (Server-level).

BUT, the good news is that you don't need to use impersonation to solve this issue. Instead you need to use module signing, and you do that via the following steps (all done in [master]):

Create a Certificate (be sure to specify ENCRYPTION BY PASSWORD = 'password')
Create a Login from that Certificate
Grant that Login whatever permission you want, or add it to whatever Role is appropriate
Sign that Stored Procedure (i.e. SPU_CAMBIO_PASSWORD), after removing the EXECUTE AS clause
Backup that Certificate to files (a .cer file for the Certificate and a .pvk file for the Private Key). This is done so that it can be re-created later.
Remove the Private Key from that Certificate. This prevents anything else from being signed with it (so you better do that backup first!).

If that Stored Procedure is ever changed after signing it, the signature will be dropped automatically, thereby losing the association with those special permissions set up for that Certificate-based Login. This is a good thing as it allows you to do a code review (assuming you didn't make the change) before re-enabling the permissions. In this case, you would:

Restore the Private Key (using the same ALTER CERTIFICATE command that was used to remove the Private Key).
Sign the Stored Procedure
Remove the Private Key again


MySQL Stored Procedure Requiring SUPER I'm trying to figure out a "safe" way of allowing a non-SUPER user to execute a specific command requiring SUPER privileges, i.e. SET SESSION binlog_format = 'MIXED'. I don't want to grant the user SUPER privileges for security reasons. Can this be accomplished using a stored procedure or any other method?

You can have stored procedures execute as the definer instead of the invoker:

CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1()
SQL SECURITY DEFINER
BEGIN
  UPDATE t1 SET counter = counter + 1;
END;

See: Access Control for Stored Programs and Views


Reclaiming mysql empty space... nothing works I've deleted tons of records from my database but I can't reclaim the space....
Use % is at 96%
Your MariaDB connection id is 242212
Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

I have innodb_file_per_table -
MariaDB [(none)]>  show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.16 sec)

I have the table with the space I need to reclaim
MariaDB [(none)]> SELECT TABLE_NAME,
    -> ROUND(DATA_LENGTH/1024/1024) AS USED_SPACE_MB,
    -> ROUND(DATA_FREE/1024/1024) AS UNUSED_SPACE_MB
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE ROUND(DATA_FREE/1024/1024) > 1000
    -> ORDER BY UNUSED_SPACE_MB;
+--------------+---------------+-----------------+
| TABLE_NAME   | USED_SPACE_MB | UNUSED_SPACE_MB |
+--------------+---------------+-----------------+
| correlations |         65720 |           54103 |
+--------------+---------------+-----------------+
1 row in set (0.01 sec)

But when I run
'optimize table correlations'
.... it does nothing.
I even ran mysqlcheck -o
....it does nothing.
Use % stays at 96%.
Is there something I'm missing?   I'd appreciate any suggestions.  Thanks!
Adding config info...
[mysqld]
innodb-defragment=1
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
innodb_buffer_pool_size = 4G
skip-name-resolve
max_heap_table_size= 128M
tmp_table_size= 128M

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1

#
# * Fine Tuning
#
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam_recover_options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10

#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M

Down to 4% disk space?  This will be tricky.
Find the largest files on the system; sort by size; see if any other files (logs, other apps, etc) can be freed up.

Find out how big ibdata1 is.
Run SHOW TABLE STATUS.  (Show me this, so I can explain what the rest of the steps mean.)
Figure out whether some tables are "file-per-table" and some are in ibdata1.
Figure out how much free space is in ibdata1.  Let'c call this x.
For each table that is not in ibdata1, but is small enough (data_free+index_free) to fit into x, move it into ibdata1 -- but start with the one with the largest Data_free.  Do not bother will tables with free space below 8MB.  (This involves a SET and an ALTER.)
For each of the rest of the tables with more than 8MB free and not currently in ibdata1, rebuild (ALTER).  Start with the smallest and work your way up, until their is not enough free disk to do the next table.

If that won't suffice then:  Get a bigger machine, dump and reload.
For the future:  You should worry about disk space when 50% full (not 96%).


150 External flat files used to create internal DB I have a situation in which I must create an entire DB off of ~150 external txt files (pipe delimited) which all represent unique tables. Furthermore these txt files come in daily and need to be loaded (with correct types) into this internal DB. The files do have filenames that correspond with the table names and they also have header rows.

Creating 150 different flat file connection strings, data conversions, and data flow tasks in SSIS does not sound plausible. Thus I am considering doing a dynamic TVP load (a hybrid of this: What's the most efficient way to load a csv file into SQL Server 2016?) via c#, where I would dynamically set the table name and columns based on the metadata provided in the txt files, then I would pass these parameters along with the data (one row at a time leveraging a TVP parameter) and insert the data into the dynamically set table.

My question is, has anyone dealt with the task of ingesting an entire DB export via flat files and recreating it locally in their environment? If so, how did you do it?

BIML is designed for this kind of semi-repetitive task. One defines a "template" that includes placeholders for the file and table names. The tool will then generate the corresponding 150 SSIS jobs.


Auto-generate scripts to create tables, indexes, etc In SSMS, we can right click a database/table/index/... and select the "Script As" option to generate a drop and create script.

Is there a way to automate this script generation and drop the scripts to a temp location, rather than manually right clicking each object and generating them?

SQL Server Management Objects SMO is your answer. You can use it to accomplish this task. Here is an example to generate Create Table Scripts.

    public string GetTableDescription(string pDatabaseName, string pSchemaName, string pTableName, string connectionString)
    {
        connectionString = ConnectionHelper.RemoveProviderFromConnectionString(connectionString);
        Server server = new Server(new ServerConnection(new SqlConnection(connectionString)));
        Database db = server.Databases[pDatabaseName];
        Table t = db.Tables[pTableName, pSchemaName];
        ScriptingOptions baseOptions = new ScriptingOptions();
        baseOptions.NoCollation = true;
        baseOptions.SchemaQualify = true;
        baseOptions.DriDefaults = true;
        baseOptions.IncludeHeaders = false;
        baseOptions.DriPrimaryKey = true;
        baseOptions.ExtendedProperties = true;
        //baseOptions.DriAll = true;
        //baseOptions.Indexes = true;
        //baseOptions.DriAllKeys = true;
        //baseOptions.SchemaQualifyForeignKeysReferences = true;
        baseOptions.EnforceScriptingOptions = true;
        StringCollection result = t.Script(baseOptions);
        return StringCreate(result);
    }


User defined table type with a check constraint I have a user defined table type where I want to make sure that a certain column is within a given range. This range however is defined in another table. So I thought I can use a check constraint on this column with a scalar valued function.

    CREATE TYPE [dbo].[Accounts] AS TABLE(
    [AccountName] [varchar](25) NOT NULL
         CONSTRAINT CHK_AccountName CHECK (dbo.validateAccountName(AccountName) = 1),
    [Sales] [int] NOT NULL,
    [Returns] [int] NOT NULL
);

However I get a syntax error, when I do the same on a regular table it works. Based on MSFT documentation check constraints should be ok on table types. What am I missing here ? Any better approach to validate input on a table type ?

Although table types do allow unnamed CHECK constraints, it is not possible to use a user-defined function inside one.

The error message is:

  Msg 2785 Level 16 State 1 Line 3
  User-defined functions, user-defined aggregates, CLR types, and methods on CLR types are not allowed in expressions in this context.
  Msg 1750 Level 16 State 0 Line 1
  Could not create constraint. See previous errors.

See the db<>fiddle demo


Normal form definitions I've had trouble finding a straight definition of normal forms online.

I don't want any examples or metaphors, just what each form is defined by in a nice format ! I believe there is more than 4 normal forms so it would be greatly appreciated if you could provide the extra information.

Please complete any missing information I've found or lay it out in a better format :

1FN:

Each cell is to be single valued
Entries in a column must be of the same type
Rows must be uniquely identified

2FN:

All attributes must be dependent of the key

3FN:

Rows must be uniquely identifiable only by the key

4FN:

No multi-valued dependencies

@A_V, I think you can find what you're looking for here. It has explanations of 1NF - 4NF, including BCNF.

From the page:

  First normal form (1NF) sets the very basic rules for an organized database:

  Eliminate duplicative columns from the same table.

  Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

  Second normal form (2NF) further addresses the concept of removing duplicative data:

  Meet all the requirements of the first normal form.
  Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
  Create relationships between these new tables and their predecessors through the use of foreign keys.

  Third normal form (3NF) goes one large step further:

  Meet all the requirements of the second normal form.
  Remove columns that are not dependent upon the primary key.

  The Boyce-Codd Normal Form, also referred to as the "third and half (3.5) normal form", adds one more requirement:

  Meet all the requirements of the third normal form.
  Every determinant must be a candidate key.

  Finally, fourth normal form (4NF) has one additional requirement:

  Meet all the requirements of the third normal form.
  A relation is in 4NF if it has no multi-valued dependencies.

  Remember, these normalization guidelines are cumulative. For a database to be in 2NF, it must first fulfill all the criteria of a 1NF database.


Your structure i commonly known as an Adjacency List (should perhaps be Set) Model. If you don't want to change the structure of your model, you basically have to do something similar to what you are currently doing. You can rewrite your query using joins like:

SELECT q1.* FROM qa_posts q1
WHERE q1.postid=1
UNION
SELECT q2.* FROM qa_posts q1
            JOIN qa_posts q2
                on q1.postid=q2.parentid
WHERE q1.postid=1
UNION
SELECT q3.* FROM qa_posts q1
            JOIN qa_posts q2
                on q1.postid=q2.parentid
            JOIN qa_posts q3
                on q2.postid=q3.parentid
WHERE q1.postid=1;

If you are willing to change the model you can search for Nested Set (example http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/), Materialized Path (example http://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/) and Transitive Closure Table (at the end of http://www.slideshare.net/billkarwin/models-for-hierarchical-data, at the beginning the other two models are described)

Edit: Adding DFS with different weights according to type:

select postid, parentid, type
from (
    SELECT q1.*
        , lpad(q1.parentid,8,case q1.type when 'Q' then 'a'
                                          when 'C' then 'b'
                                          when 'A' then 'c'
                             end)
        || lpad(q1.postid,8,case q1.type when 'Q' then 'a'
                                         when 'C' then 'b'
                                         when 'A' then 'c'
                             end) as padded_path
    FROM qa_posts q1
    WHERE q1.postid=1
    UNION
    SELECT q2.*
       , lpad(q1.parentid,8,case q1.type when 'Q' then 'a'
                                         when 'C' then 'b'
                                         when 'A' then 'c'
                            end)
        || lpad(q1.postid,8,case q1.type when 'Q' then 'a'
                                         when 'C' then 'b'
                                         when 'A' then 'c'
                            end)
        || lpad(q2.postid,8,case q2.type when 'Q' then 'a'
                                         when 'C' then 'b'
                                         when 'A' then 'c'
                            end) as padded_path
    FROM qa_posts q1
    JOIN qa_posts q2
        on q1.postid=q2.parentid
    WHERE q1.postid=1
    UNION
    SELECT q3.*
         , lpad(q1.parentid,8,case q1.type when 'Q' then 'a'
                                           when 'C' then 'b'
                                           when 'A' then 'c'
                              end)
         || lpad(q1.postid,8,case q1.type when 'Q' then 'a'
                                          when 'C' then 'b'
                                          when 'A' then 'c'
                              end)
         || lpad(q2.postid,8,case q2.type when 'Q' then 'a'
                                          when 'C' then 'b'
                                          when 'A' then 'c'
                              end)
         || lpad(q3.postid,8,case q3.type when 'Q' then 'a'
                                          when 'C' then 'b'
                                          when 'A' then 'c'
                              end) as padded_path
    FROM qa_posts q1
    JOIN qa_posts q2
        on q1.postid=q2.parentid
    JOIN qa_posts q3
        on q2.postid=q3.parentid
    WHERE q1.postid=1
) as x
order by padded_path;

+--------+----------+------+
| postid | parentid | type |
+--------+----------+------+
|      1 |        0 | Q    |
|      8 |        1 | C    |
|      2 |        1 | A    |
|      4 |        2 | C    |
|      5 |        2 | C    |
|      6 |        1 | A    |
+--------+----------+------+
6 rows in set (0.00 sec)

Edit: The other Questions result in (padded_path included):

+--------+----------+------+--------------------------+
| postid | parentid | type | padded_path              |
+--------+----------+------+--------------------------+
|      3 |        0 | Q    | aaaaaaa0aaaaaaa3         |
|      7 |        3 | C    | aaaaaaa0aaaaaaa3bbbbbbb7 |
+--------+----------+------+--------------------------+
2 rows in set (0.00 sec)

+--------+----------+------+----------------------------------+
| postid | parentid | type | padded_path                      |
+--------+----------+------+----------------------------------+
|      9 |        0 | Q    | aaaaaaa0aaaaaaa9                 |
|     10 |        9 | C    | aaaaaaa0aaaaaaa9bbbbbb10         |
|     11 |        9 | A    | aaaaaaa0aaaaaaa9cccccc11         |
|     12 |       11 | C    | aaaaaaa0aaaaaaa9cccccc11bbbbbb12 |
+--------+----------+------+----------------------------------+
4 rows in set (0.00 sec)

Changing the predicate q1.postid=1 to q1.type='Q' results in:

+--------+----------+------+----------------------------------+
| postid | parentid | type | padded_path                      |
+--------+----------+------+----------------------------------+
|      1 |        0 | Q    | aaaaaaa0aaaaaaa1                 |
|      8 |        1 | C    | aaaaaaa0aaaaaaa1bbbbbbb8         |
|      2 |        1 | A    | aaaaaaa0aaaaaaa1ccccccc2         |
|      4 |        2 | C    | aaaaaaa0aaaaaaa1ccccccc2bbbbbbb4 |
|      5 |        2 | C    | aaaaaaa0aaaaaaa1ccccccc2bbbbbbb5 |
|      6 |        1 | A    | aaaaaaa0aaaaaaa1ccccccc6         |
|      3 |        0 | Q    | aaaaaaa0aaaaaaa3                 |
|      7 |        3 | C    | aaaaaaa0aaaaaaa3bbbbbbb7         |
|      9 |        0 | Q    | aaaaaaa0aaaaaaa9                 |
|     10 |        9 | C    | aaaaaaa0aaaaaaa9bbbbbb10         |
|     11 |        9 | A    | aaaaaaa0aaaaaaa9cccccc11         |
|     12 |       11 | C    | aaaaaaa0aaaaaaa9cccccc11bbbbbb12 |
+--------+----------+------+----------------------------------+
12 rows in set (0.00 sec)


LDF and MDF file sizes for a dynamically created Database I am creating a dynamic database from C# code, and would like the database to have a fixed size (for example 5 GB). While creating the DB through code, I need to specify MDF and LDF max file sizes. What should be the ideal values? 2.5GB each or is their some other ratio?

I have to make sure that the total DB size does not go more than 5GB, so should I only worry about the MDF size (keeping it fixed at 5GB) and leave the LDF size to grow unlimited (I cant do that but if it hurts performance then I have to think about it)?

Thanks,
Vivek

This all depends on what you want to happen when the database hits 5GB. Do you want users to get errors and the database to become unusable full stop, or do you just want to be notified?

It also depends on whether you want to consider the log size separately from the actual data. The log size will fluctuate based on a variety of factors, including recovery model, frequency of full/log/diff backups, avg/peak size of transactions, duration between begin transaction and commit/rollback, etc.

Finally, it depends on whether you want to be have to manage this proactively. If for any of the above reason your log reaches 5GB but you've decided you don't want to stop the database from working because of log usage, you'll have to intervene if the max log size is set to 5GB but the data itself really hasn't grown...


MySQL Installation error: Unable to update security settings I tried to configure MySQL server 5.5 on Windows and this is what I got:

mysql-server-5.5-winx64:56 - Attempting to update security settings.
mysql-server-5.5-winx64:100 - Unable to update security settings. Unable to connect to any of the specified MySQL hosts.
mysql-server-5.5-winx64:62 - Attempting to verify command-line client shortcut.

Any thoughts? I found this article on it, but I'm not really sure what to do. I tried getting random processes to work but it didn't seem to change much.

Problem was with root access and firewall.

To fix this I:

Ran it on a machine with full root acces
Added all MySQL items to
firewall list and ran reconfiguration of the MySQL

Ability to access root is important. I tried repeating this on a machine were I do not have root access and failed on every attempt


Oracle Forms Runtime error When I am running a form in Forms 11g

http://rahulsingh-pc:8888/forms/frmservlet

I'm getting an error:

Failure of server APACHE bridge:
No backend server available for connection: timed out after 10 seconds or idempotent set to OFF.

Products installed:

Web Logic 10.3.5
forms&reports&portals 11.1.2.0

Can anybody help me fix this problem?

This is Weblogic. The FORMS services are actually answered by a Weblogic Java Server. It is named WLS_FORMS and you can start it via the web logic management console (available by default as http://localhost:7001/console).

Click on the servers link on the left, then on the main pane, select "Control" tab. Select the checkbox next to WLS_FORMS and select "Start listening".


SQL Service Account creation in AD using PowerShell I'm wondering if it's possible to create a service account remotely from the SQL box using PowerShell, and put the service accounts created in the OU (folder) they belong in AD as part of a unattended SQL install.

Could this be done without having to pre-create the service accounts before SQL installation? I'm able to query the AD from the SQL box by importing the AD module.

If you have installed the Active Directory PowerShell components, and the account under which you are running the session has sufficient permission to perform the action, then it is actually pretty simple:

New-ADUser -Name "Jo Bloggs" -GivenName Jo -Surname Bloggs`
-SamAccountName jbloggs -UserPrincipalName jbloggs@corp.contoso.com `
-AccountPassword (Read-Host -AsSecureString "AccountPassword") `
-Path 'OU=Whatever,OU=Blah,DC=corp,DC=contoso,DC=com' `
-PassThru | Enable-ADAccount

The documentation is pretty extensive and can be found here:
https://technet.microsoft.com/en-us/library/ee617253.aspx


The job failed. The owner (pc\user) of job FULL DB BACKUP does not have server access SQL Server 2008R2 is failing to execute log backup hourly.

  Date      5/2/2013 10:22:19 PM Log       Job History (LOG BACKUP)

  Step ID       0 Server       pc Job Name        LOG BACKUP Step
  Name      (Job outcome) Duration     00:00:03 Sql Severity      0
  Sql Message ID        0 Operator Emailed      Operator Net
  sent       Operator Paged         Retries Attempted     0

  Message The job failed.  The owner (pc\user) of job LOG BACKUP
  does not have server access.

ps: its a windows 7 x86 ultimate virtual machine.
the account have administrator privilege.
any idea why its failing?

Try to create the PC\user as a login and give him the backup privilege in your database with this code.

USE [master]
GO
CREATE LOGIN [SERVER\Test1] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [mydb]
GO
CREATE USER [SERVER\Test1] FOR LOGIN [SERVER\Test1]
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [SERVER\Test1]
GO


How to create BAG on non-wsfc nodes (SQL Server 2016 STANDARD Edition) Stumbled over this a few times now, while trying to figure out if a consultant is right or not in his claim. I hope you can shed light on it.

Is it, or is it not possible to configure a Basic Availability Group on a database present in two AD-Servers with each its own standard edition SQL Server 2016 SP2 cu2 (default instance)
(Both had the SSMS 16.5.3 – now upgraded to SSMS 17.9)
None of them are configured as members in any failover cluster.

Almost everywhere I seem to find, it looks as if they all require that nodes be member(s) of a WSFC.

“Almost”, because I read a Microsoft note, stating that it’s possible with SSMS 17. I did install that and it also fails to enable the options to switch on AG?

If it should be possible, do you know where I could go to read what’s needed…

Re:
https://www.brentozar.com/archive/2015/06/how-to-set-up-standard-edition-alwayson-availability-groups-in-sql-server-2016/#comment-2654300

Is it, or is it not possible to configure a Basic Availability Group on a database present in two AD-Servers with each its own standard edition SQL2016sp2 cu2 (default instance) (Both had the SSMS 16.5.3 management studio – now upgraded to SSMS 17.9) None of them are configured as members in any failover cluster.

As far as my knowledge goes the BAG's require WSFC and you cannot configure BAG without WSFC for even SQL Server 2017, till now at the time of writing the answer. MS technology is changing too fast and who knows you might be able to do it in future. The option of "clusterless" AG is for "full fledged" availability groups and not for the basic one and again the feature comes from SQL Server 2017 vNext and not from SQL Server 2016. So if you are using SQL Server 2016 and trying to configure clusterless BAG, it is not possible


Run a query in loop, with the results of another query First, I want to list databases with a specific table:

 SELECT name
FROM   sys.databases
WHERE  CASE
         WHEN state_desc = 'ONLINE'
              THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[TB_INF_CLI]', 'U')
       END IS NOT NULL

Then, I want to use the database_name inside a loop, to list all indexes of this table ( TB_INF_CLI )  of all these databases, with this query:

SELECT DB_NAME() AS Database_Name
, o.name AS Table_Name
, i.name AS Index_Name
, i.type_desc AS Index_Type
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL
AND o.type = 'U'
ORDER BY o.name, i.type

How can I do this without msForEachDb?

with this solution, I can only list the dabatase i'm connected. I can't understand how can I implement the database_name inside this query:

SELECT DB_NAME() AS Database_Name
, o.name AS Table_Name
, i.name AS Index_Name
, i.type_desc AS Index_Type
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL
AND o.type = 'U'
and db_name() in
(SELECT name
FROM   sys.databases
WHERE  CASE
         WHEN state_desc = 'ONLINE'
              THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[TB_INF_CLI]', 'U')
       END IS NOT NULL
)
ORDER BY o.name, i.type

EDIT:

i could achieve what I want with this:

create table ##TB_REN (database_name varchar(200),table_name varchar(200),index_name varchar(200),index_type varchar(200))
sp_msforeachdb'use [?]
insert into ##TB_REN( database_name,table_name,index_name,index_type)
SELECT db_name() AS Database_Name
, o.name AS Table_Name
, i.name AS Index_Name
, i.type_desc AS Index_Type
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL
and o.name =''TB_INF_CLI''
AND o.type = ''U''
ORDER BY o.name, i.type'

Is there  a better solution??

If you want to assemble the statement at runtime, you're locked into Dynamic SQL as far as I know.  I don't like sp_MSforeachdb as well because it's undocumented and I've ran into bugs with it, so here's an alternative that doesn't require its use:

DECLARE @cmd nvarchar(max) = N'';

SELECT @cmd = @cmd + N'
UNION ALL

SELECT ' + QUOTENAME(QUOTENAME(name),N'''') + N' AS Database_Name
, o.name AS Table_Name
, i.name AS Index_Name
, i.type_desc AS Index_Type
FROM sys.indexes i
INNER JOIN ' + QUOTENAME(name) + '.sys.objects o ON i.object_id = o.object_id
INNER JOIN ' + QUOTENAME(name) + '.sys.schemas sc ON o.schema_id =     sc.schema_id
WHERE i.name IS NOT NULL
AND o.type = ''U'''
FROM sys.databases
WHERE database_id > 4  -- comment out if you want to include system databses

SELECT @cmd = STUFF(@cmd, 1, 13, N'') + N' ORDER BY 1, 2'

PRINT @cmd

EXEC sp_executesql @cmd;


Why SQL server unload CLR assembly every midnight Every midnight not a specific time, the sql server will unload the CLR assembly. The reason is that due to memory pressure, the assembly is marked with unload and will be unloaded soon.

How to make the assembly never unloaded, since reload will impact the query performance a lot.
Second question is how to find the root cause of sql server reported high memory pressure at midnight?

I was using the performance monitor to track the memory usage of each OS processes, and didn't see the huge memory usage increase at that time.

The system is Windows Server 2008 R2 and SQL server 2008 R2.

The system is x64. Sql server is x64 standard.

The log is "AppDomain 9 ( is marjed for unload due to memory presure.)"

I don't think there is a schedule job at that time.

The reason of why the memory pressure is high at that time is unknown.

SQL server 32 bit instance's virtual memory is limited to 4GB. If you use SQL CLR extensively with a SQL Server 32 bit instance and experience one or more of above errors, Microsoft recommends that you migrate to a 64 bit SQL Server. This will enable SQL CLR to access more virtual memory and may prevent the above errors from happening in your environment.

For futher information check out the following page: http://support.microsoft.com/kb/969962


MySQL data to Postgres every X time Our team (Uni students) have a project that we are updating.

Old project team has a Live MySQL database. We want to make a archive/warehouse from it in PostgreSQL.

My question is: How can we import / copy data from MySQL DB to PostgreSQL DB after every X time (12 hours for instance) has passed?

You'll need to either use an ETL Tool eg Microsoft SSIS. There's plenty of open source products available too. Or you can build your own extract utility using a programming language like PHP, .Net etc.

The Kimball guys have an article on whether you should build your own or use an off the shelf product.
http://www.kimballgroup.com/2008/04/should-you-use-an-etl-tool/

To schedule the load, you can use windows scheduler on Windows or cron jobs on Unix.


Mongo ReplicaSet - arbiter in connection string I'm using ReplicaSet with Mongo and when i build the connection string i have all servers hosts and ports - comma delimited.

My question is, shall i add the arbiter address there as well or just the masters and secondaries?

Thank you!

Connecting to a replica set

Most drivers will show the following behavior, as far as I know

If only a single node is given, the driver will simply connect to this node.
If the replicaSet option is given in the connection string, the driver will start to explore said replica set to identify all non-hidden members, regardless of the number of nodes given in the connection string.
The same behavior is triggered when multiple nodes are given in the connection string. Since this is a bit more driver dependent than the above (for example, you can give multiple mongos as well), I personally would not rely too much on it.

Should I include an arbiter in the connection string?

The reason why you should always give at least two members of a replica set in a connection string is that this enables the driver to connect to a replica set, even when one node is down. This can be any node (except a hidden one, I suppose, but I have never tried that), since each node is fully aware of the replica set.

In a three node replica set with two data bearing members, it does not make much sense to include the arbiter into the connection string. If you give the two data bearing nodes, and both are unreachable, your replica set is not reachable any more.

In a five node replica set, however, you may include an arbiter in the connection string, since you may want to add 3 nodes altogether to make sure that either you can connect to one of them or can safely assume that the replica set is unavailable.

TL:DR

It does not hurt to include an arbiter in the connection string, though it makes a bit less sense in a minimal replica set consisting of two data bearing nodes and an arbiter.

To be on the safe side, you will almost always include the majority of voting members in your connection string. Which type of member the individual member is does not play a significant role for the purpose of replica set discovery.

I would suggest to always add the replicaSet option to the connection string, and be it just for being clear on the intended purpose.


Anyway to identify mysql slaves have read upto which binary logs I have multiple slaves reading from one master.. But few of them are keep disconnecting due to network issue.

Is there any way that i can make sure before setting up the bin log purging script on master that all the slave have read upto certain bin logs which we can purge?

The easiest method is the following

Go to each Slave and run SHOW SLAVE STATUS\G
Look for Relay_Master_Log_File on Each Slave
Whichever Slave has the oldest Relay_Master_Log_File is the one you purge to on the Master

Why Relay_Master_Log_File ? First Look at SHOW SLAVE STATUS\G

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000255
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 858190247
            Relay_Log_Space: 873772
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

You see Master_Log_File and Relay_Master_Log_File

In this display they are different. So why choose Relay_Master_Log_File ?

Master_Log_File represents the binlog with the last binlog event downloaded to the Slave
Relay_Master_Log_File represents the binlog with the last binlog event executed on  the Slave

In the event of any replication lag, Relay_Master_Log_File is always the oldest. If they are the same, fine. You choose Relay_Master_Log_File always.

In this case, you purge to mysql-bin.000254 on the Master. In the event the SQL thread dies, you don't want to erase binlogs from the Master the Slave has not processed yet.

Getting back to multiple slaves, you choose the oldest Relay_Master_Log_File of all Slaves.

To clarify, the oldest Relay_Master_Log_File is the binary log name with the lowest number.


How do you approach refactoring an ugly procedure/query? I've inherited a database which contains several procedures which are 1000-1500 lines long, with complex nested sub-selects going up to 7 or 8 levels deep in places. I desperately need to refactor them for my own sanity, but how can I begin to do this with any level of confidence that they still work the same?

I would write unit tests if this were .Net - do you recommend a similar approach?

Yes, create unit tests.  This is really the only way to ensure that a modified version meets the same requirements as the original.

To refactor the procedures look for any repeating patterns of code that could be extracted into separate procedures or nested procedures.  If the procedures are still too long break parts of it into separate procedures that each complete one task.  As you break off new procedures you should add unit tests for it.

Seven or eight levels of sub-selects does sound excessive, but you may find that some or even most of these are necessary to produce the data required.  I would focus on the procedure initially and then tackle the SQL.

To do unit testing in Oracle, the foremost commercial product for testing is Quest's Code Tester.  Oracle has Unit Testing built into it's free SQL Developer product.  The question Unit testing for PL/SQL on StackOverflow has some other options or you can just write your own tests.


There is insufficient system memory in resource pool default to run this query I'm using SQL Server 2014 - 12.0.2269.0 (X64) on Windows Server 2012 and I'm experiencing some memory issues. When I run a stored procedure that is doing a quite "heavy" calculation, I get an error after something like 10 minutes:

  There is insufficient system memory in resource pool 'default' to run this query.

My SQL Server has multiple databases (like 15, but they are not always used at the same time). I looked to the SQL Server log file (after I got the error), and I saw a lot of lines like this:

  2015-12-17 12:00:37.57 spid19s     Disallowing page allocations for database 'Database_Name' due to insufficient memory in the resource pool 'default'. See 'http://go.microsoft.com/fwlink/?LinkId=330673' for more information.

A report is generated in the log with the memory used by each component (I think). If I correctly interpret the report, we can see that there's a lot of memory consumed by MEMORYCLERK_SQLBUFFERPOOL. You can find the report here: http://pastebin.com/kgmk9dPH

I also generated a report with a graph that shows the same "conclusion":

Here's maybe another useful reports:

Note that I've also seen this error in the log:

  2015-12-17 12:04:52.37 spid70       Failed allocate page due to
  database memory pressure: FAIL_PAGE_ALLOCATION 8

Here's some information regarding the server's memory:

Total memory on the server: 16 Gb
Memory allocated to SQL server: 12288 Mb
Physical Memory In Use (from sys.dm_os_process_memory): 9287 Mb

If it can help, the server doesn't host any SharePoint database.

While this probably isn't an exact answer to your problem I'll post it anyway hoping it may help you in a way.

What you are seeing is not the MEMORYCLERK_SQLBUFFERPOOL but the MEMORYCLERK_SQLLOGPOOL taking up all the memory.

There is a known issue with SQL 2012 SQL Server 2012 experiences out-of-memory errors. Even though you are running 2014 there is a chance that you are running into the same issue (even though I couldn't find a connect item for 2014).

There is also this known issue for both 2014 and 2012 involving index rebuilds and always on. You didn't specify you are using that but maybe the same bug has other triggers: FIX: SQL Cache Memory decreases and CPU usage increases when you rebuild an index for a table in SQL Server

So my best suggestion for now is to update to the latest CU and see if it still happens, since there have been a few fixes around MEMORYCLERK_SQLLOGPOOL recently. Since you are running RTM with 2 security fixes only that could make sense, there is an SP1 + multiple CU's available.

Since there is also fairly high memory usage in MEMORYCLERK_XTP used by in-memory OLTP this article may help you troubleshoot: Monitor and Troubleshoot Memory Usage


SSIS Custom Task with a default Task UI I followed the tutorial here to build a very simple task and was able to get that task to work.

Here are my current tools:

Sql Server 2012
Visual Studio 2013 (project is compiled in .net 4.0)
Sql Server Data Tools (from Sql Server 2012)

What I want to do, however, is use the default IU with the property window that you see inside Sql Server Data Tools.  Here is a tutorial to do so, but it is quite old.

The problem I seem to be having is that I can't seem to locate Microsoft.DataTransformationServices.Controls.  I tried adding the compatibility tools, but that did not bring the dll into my GAC.

I would really like to be able to pull in that dll and use DTSBaseTaskUI so that I can inherit the default look of the UI within Sql Data Tools without recreating the already used UI.

Not sure if you ever got this solved, but I was able to find it in the GAC:

C:\Windows\assembly\GAC_MSIL\Microsoft.DataTransformationServices.Controls\...\Microsoft.DataTransformationServices.Controls.DLL

Let me know if that did the trick for you


high concurency issue with read-write on mysql table I have a mysql innoDB table (mysql 5.7, ubuntu 14.04) :

+-------------------+-------------+------+-----+---------+----------------+
| Field             | Type        | Null | Key | Default | Extra          |
+-------------------+-------------+------+-----+---------+----------------+
| id                | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| altitude          | smallint(6) | YES  |     | NULL    |                |
| counter           | double      | YES  |     | NULL    |                |
| datetime          | datetime    | NO   |     | NULL    |                |
| details           | longtext    | YES  |     | NULL    |                |
| deviation         | smallint(6) | YES  |     | NULL    |                |
| engine            | smallint(6) | YES  |     | NULL    |                |
| event             | smallint(6) | YES  |     | NULL    |                |
| fuel_level        | double      | YES  |     | NULL    |                |
| gsm_level         | int(11)     | YES  |     | NULL    |                |
| latitude          | double      | NO   |     | NULL    |                |
| longitude         | double      | NO   |     | NULL    |                |
| satellite         | smallint(6) | YES  |     | NULL    |                |
| speed             | smallint(6) | YES  |     | NULL    |                |
| time              | time        | NO   |     | NULL    |                |
| treated           | tinyint(1)  | NO   | MUL | 0       |                |
| id_driver         | bigint(20)  | YES  | MUL | NULL    |                |
| id_vehicle_device | bigint(20)  | NO   | MUL | NULL    |                |
+-------------------+-------------+------+-----+---------+----------------+

This table has 10 to 20 or more inserts per second, also there are some updates and reads with where condition from the same table. The table is about 60 GB and has more than 20 M records.

To avoid a very high increase of data on this table, which could pose problems on dealing with it, each day at midnight, I try to delete old records and keep the last 45 days. This delete has to be done relatively for each id_vehicle_device, so the delete can be general.

The issue I'm facing is that when the delete query is running, it locks the record table and prevents inserts. How could I delete old records without lock issue, and the best way to deal with read/write in such huge mysql table?

Please provide SHOW CREATE TABLE; it is more descriptive than DESCRIBE.

Please show us samples the INSERTs and UPDATEs and SELECTs; we need to talk about the indexes.

Purging 'old' data can be done 'instantly' if you use PARTITION BY RANGE(TO_DAYS(datetime)) ...  See here .

Are most of the fields really NULLable?  Use NOT NULL where appropriate.