Can an EXISTS clause be improved when the condition is checked for the same value multiple times? Assume that I'm modelling a Blog engine. Blogs have n Posts. Each User can be granted a role on different Blogs (Admin, Approver or Editor stored as enums/ints). Each Post can have a State (Draft, PendingApproval, Published stored as enums/ints). Here's how it's modeled:

Blogs
-----
Id (PK, int, not-null)
Name (varchar, not-null)

Users
-----
Id (PK, int, not-null)
Name (varchar, not-null)

Posts
-----
Id (PK, int, not-null)
BlogId (FK, int, not-null)
State (int, not-null)
Title (varchar, not-null)
Body (varchar, not-null)
(and there is an index on (BlogId, State))

UserBlogRoles
-------------
Id (PK, int, not-null)
UserId (FK, int, not-null)
BlogId (FK, int, not-null)
Role (int, not-null)
(and there is a unique index on (UserId, BlogId))

And here are the enums:

UserBlogRole
------------
Editor = 0
Approver = 1
Admin = 2

PostState
---------
Draft = 0
PendingApproval = 1
Published = 2

As an Approver, I want to view a list of all Posts that have the State = PendingApproval for all blogs that I have at least the Approver role.

Here's the query:

declare @approverRole int = 1;
declare @pendingApprovalState int = 1;
declare @myUserId int = 12345;

select
    p.Id, p.BlogId, p.State,  p.Title, p.Body
from Posts as p
where
    p.State = @pendingApprovalState and
    exists (
        select r.Id
        from UserBlogRoles as r
        where r.Role >= @approverRole and r.BlogId = p.BlogId and r.UserId = @myUserId
    )

This works as expected. But, I've been wondering about the performance of the exists(). If the same values are passed to the exists() multiple times, is it cached? For example, there could be many pending approval posts for a particular blog. Will it need to check the permissions for each of those posts and their associated blogs?

I've been thinking of changing it to a where in (query) instead:

select
    p.Id, p.BlogId, p.State,  p.Title, p.Body
from Posts as p
where
    p.State = @pendingApprovalState and
    p.BlogId in (
        select r.BlogId
        from UserBlogRoles as r
        where r.Role >= @approverRole and r.UserId = @myUserId
    )

But I'm not sure if that'd be better or worse performance? Does SqlServer execute the "in" query once and then use those values to check against the BlogId?

I guess this could be changed into a join too.

The execution plans of both showed the same (50% index scan on Posts PK and 50% index scan on UserBlogRoles PK) I think because my sample database doesn't have any data in it.

Exists and IN will give the same query plan because they are both "LEFT SEMI JOINs" internally. I prefer EXISTS generally because of consistency. NOT EXISTS is safer then NOT IN.

Caching does not apply and don't think about it. This is a set based operation and the SQL Server optimiser will work out the best way: It may spool the subquery or it may not. Note that a left semi join is typically very efficient. Don't overthink it.

Finally, a PK scan indicates a missing index on UserBlogRoles

Observation: use the the schema, Luke. dbo.UserBlogRoles for example


MOM and EOM in SQL Server 2005 What is the most sample and practical way to know the MOM (middle of month) and EOM (end of month) in a stored procedure in SQL Server 2005? (without doing a select case / switch)

EOM is the last day of the month, and MOM is the last day of the fortnight. It seems to be 15 for every month except on February.

Also I need to take care the leap-year, Holidays, business day option, etc. Like linux.

For complex date and time functions, use a user defined table-valued function and filter the results from it:

http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx

There are a number of ways to do this, but this is one I prefer.


Append JSON array to an object I'm trying to transform a table that has a rows with the some attributes that are the same, but others that differ like the table below to a specific JSON structure.

id
name
language
attr1
attr2
attr3
attr4

13
Example 1
en
0
80
10
100

13
Ejemplo 1
es
0
80
10
100

21
Beispiel 2
de
35
80
20
75

21
Exemple 2
fr
35
80
20
75

As some of the columns have the same value, I want them inside an object, and the columns that have different content as an array, like this:
{
  "accounts": [
    {
      "id": 13,
      "attr1": 0,
      "attr2": 80,
      "attr3": 10,
      "attr4": 100,
      "names": [
        {
          "language": "en",
          "name": "Example 1"
        },
        {
          "language": "es",
          "name": "Ejemplo 1"
        }
      ]
    },
    {
      "id": 21,
      "attr1": 35,
      "attr2": 80,
      "attr3": 20,
      "attr4": 75,
      "names": [
        {
          "language": "de",
          "name": "Beispiel 2"
        },
        {
          "language": "fr",
          "name": "Exemple 2"
        }
      ]
    }
  ]
}

I made a function that generates the accounts array without the names array in every object:
SELECT array_to_json(array_agg(accounts)) AS accounts
FROM (
         SELECT DISTINCT id, attr1, attr2, attr3, attr4
         FROM temp_table_from_fuction()
         ORDER BY attr1, attr2
     ) accounts

(temp_table_from_fuction() returns the first table)
This is the result:
[
  {
    "id": 13,
    "attr1": 0,
    "attr2": 80,
    "attr3": 10,
    "attr4": 100
  },
  {
    "id": 21,
    "attr1": 35,
    "attr2": 80,
    "attr3": 20,
    "attr4": 75
  }
]

How can I append an array to a JSON object calling the temp_table_from_fuction() again? Is that possible?

You didn't put a PostgreSQL version tag, but there are functions added around 9.4 that will help with this: you can use jsonb_object_agg(), jsonb_build_object() and jsonb_build_array() (or their equivalent json_ functions if you don't want jsonb).

See documentation here: https://www.postgresql.org/docs/10/functions-json.html

As a side note, you should normalize that table if it really is a table and not a view.  Specifically, have a look at second normal form.


How do I change all Oracle profiles PASSWORD_LIFE_TIME to UNLIMITED? This query:

select distinct profile from dba_profiles;

Returns 3 rows:

default
default1
default2.

I want to change the PASSWORD_LIFE_TIME for all these profiles to UNLIMITED with a single query or without manually passing the profile names in query.

Because I do not know how many profiles are there in the system, the number of profiles will vary. I have to execute this query in multiple systems.

You can simply do it in PL/SQL in a loop:

begin
  for c in (select distinct profile from dba_profiles)
  loop
    execute immediate 'alter profile ' || c.profile || ' limit password_life_time unlimited';
  end loop;
end;
/


Server-wide Database Permissions in SQL Server We have a SQL Server 2014 installation, which is accessible by a subset of our Active Directory users via Windows Authentication.

Currently, when they log into the server for the first time they are given the sysadmin server role. I want to change this so that they belong to a more restrictive role.

I would ideally like them to be able to deal with data (SELECT, UPDATE, DELETE etc), EXEC stored procedures, and CREATE, ALTER and DROP tables, stored procedures and views.

The problem is that those permissions are database-level rather than server-level. Is it possible to have a server-level role that defines a common set of permissions for all databases on the server? If not, what's the best approach here?

So basically it sounds like you want to give this group of individuals db_owner access to all databases on the server.  The way to do this is:

Create a local group account on the Windows Server (e.g. \\MYSERVER\SpecialGroup)
Add the AD accounts to that group.
Create a SQL Server login for \\MYSERVER\SpecialGroup
Then create a job, that runs every night (or however frequently you want) that loops through all your user databases and issues a CREATE USER statement for \\MySERVER\SpecialGroup and assigns them to db_owner.


Bulk insert into SQL Server from VMWare guest using distributed switch This is mostly likely not a SQL server issue but the setup seems to only be affecting BULK INSERTS to SQL Servers.

We have recently moved VM Hardware and all the guests that were moved had their virtual switches changed from standard to distributed.

I then started receiving

  A fatal error occurred while reading the input stream from the
  network. The session will be terminated (input error: 64, output
  error: 0)

on a two SQL servers during BULK INSERT operations. One of the SQL servers was a VM with the new configuration and the other was a physical server. Both BULK INSERT operation originated from a VM with the new configuration. The BULK INSERTs would not fail every time, it was very random when it would.

When we changed the virtual switch to be a standard switch instead of a distributed switch the issue goes away.

I am looking for more of an explanation to why it doesn't work with a distributed switch instead of a resolution. My guess would be that the BULK INSERT operation is serial and with a distributed switch the packets are being routed through different hosts, some of which may be busier than others, and are arriving at the destination server beyond some latency threshold. (note: there is nothing in the windows event log at the times of the errors on either the source or destination server)

UPDATE: turns out the issue was due to the NIC. All of our VMs were configured with an E1000 NIC which functioned well enough on standard switches. Once we moved to distributed switches, we started seeing issues with large data transfers, not just sql queries. Changing the NIC to VMXNET3 seems to have resolved the issue.

I would suggest using Wireshark or Microsoft NetMon and sniffing the traffic to see what is happening here.  Could also be related to the TCP Chimney Offload feature, assuming something has changed at the NIC level:

http://support.microsoft.com/kb/942861


execute as login for nonexistent login successful On Sharepoint database server, we found 18456 state 11: infrastructure error for account Domain\machineA$ every 10 minutes and nt authority\anonymous every hour.

I checked syslogins but didn't find login and xp_logininfo didn't return results.

I Ran "Execute as login=domain\machineA$" and it was successful.... That is strange

I executed sys.logins_tokens and it returned 10 groups(like 'Public', \Everyone, S-1-19-1012-33).but no one was there as login in sql server.

Please here help me in finding --> How Was i able to Ran "execute as login=domain\machineA$" successfully while login doesn't exist.

As Erland marked in https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ad6a80b1-717c-4d08-ae51-2eb029e59738/execute-as-login-for-nonexistent-login-successful?forum=sqldatabaseengine#ad6a80b1-717c-4d08-ae51-2eb029e59738

Yes, you can do EXEUTE AS LOGIN on a Windows login which exists in the AD, even if that login does not have permission to log into SQL Server.

The topic for EXECUTE AS in Books Online, includes this caution box:
The EXECUTE AS statement can succeed as long as the Database Engine can resolve the name. If a domain user exists, Windows might be able to resolve the user for the Database Engine, even though the Windows user does not have access to SQL Server. This can lead to a condition where a login with no access to SQL Server appears to be logged in, though the impersonated login would only have the permissions granted to public or guest.


Extract Archived Log Files from RMAN Backupset I have an RMAN backup that is taken on 19th january with: BACKUP DATABASE PLUS ARCHIVELOG command. Now I need to restore Archived logs from that backupset to some other OS directory. Is there any way to extract Archived Log files from that backup?

Here is the solution.

RMAN > run
{
    ALLOCATE CHANNEL d1 DEVICE TYPE disk;
    restore archivelog all;
}


How to read files using the OPENDATASOURCE method? I am trying to understand what a particular piece of source code does. The original author has written the following excerpt:
/*******************************************/
-- Inserează în tabela temporară.
/*******************************************/
INSERT INTO #tmp
(
    [tip]
  , [ev]
  , [ap]
  , [cant]
  , [preț]
  , [data]
)
SELECT *
FROM
OPENDATASOURCE
(
    'Microsoft.Jet.OLEDB.4.0'
  , 'Data Source=C:\ETU; Extended Properties="TEXT; HDR=yes; FMT=DELIMITED"'
)...[EV#csv]

When I am executing this piece of code in SSMS, I get the following error:

Msg 7314, Level 16, State 1, Line 4
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "EV#csv". The table either does not exist or the current user does not have permissions on that table.

MSDN shows certain syntaxes for linked servers, but I do not think that the original author meant that.
There is a certain file on the hard disk which full path is C:\ETU\EV20150126000001.csv.
I have attempted to rename the file to the EV#csv new name, yet to no avail.
SELECT @@VERSION says:

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86)   Sep 16 2010 20:09:22   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition with Advanced Services on Windows NT 5.1  (Build 2600: Service Pack 3)

The Jet.OLEDB.4.0 is data access library for Excel/Access so the code is trying to read data from CSV file into a temporary table.

If you change the source to OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\ETU;Extended Properties="Text;HDR=YES;"')...[etu#csv] you will open c:\etu\etu.csv so changing the last parameter to ...EV20150126000001#csv will open your file. See:http://blog.waynesheffield.com/wayne/code-library/ad-hoc-querying/ As this is a CSV file I would prefer to use openrowset bulk as the Jet.OLEDB drivers are "interesting" on 64bit platforms:  see: http://bradsruminations.blogspot.com/2011/01/ for further ideas.


How do I get the remote hostname of the server? Can I use the hostname of the server in a PostgreSQL SELECT statement? The following may be close but apparently does not quite work:

\set x \! hostname
select :x;

This is for PostgreSQL 9.6 on Debian.

I don't think it's possible. I think what you're looking for is gethostname(). That's not called anywhere in the PostgreSQL source code. Nor is utsname or any kernel interface into this.

What is called is getaddrinfo that provides a CANONNAME but that's essentially just your own method of addressing the server not the "symbolic hostname". If you need the hostname, you'll have to build a C-extension that does just that. Or, use a third party procedural language, like perl plperlu, that has that support (through Sys::Hostname or Net::Domain, see perldoc -q hostname); or, alternatively through javascript with plv8 that has os.hostname().

Another method would be simply reading the file /proc/sys/kernel/hostname.

It's a database, not a remote administration utility. You can't really fault it for not doing this.


Need help mariadb optimization for performance configuration I have server with 20cpu, 40 core th, 96GB ram, 2x3.8TB Nvme disk. This server for shared hosting, and about 1000 account hosted.

Current my configuration on my.cnf

[client-server]
!includedir /etc/my.cnf.d

[mysqld]
max_connections=150
log-error = /var/lib/mysql/myerrnet.err
performance-schema = 0
default-storage-engine = MyISAM
innodb_file_per_table = 1
query_cache_size =0
query_cache_type =0
max_allowed_packet=268435456
max_heap_table_size=20M
tmp_table_size =20M
open_files_limit=50000
local-infile=0
wait_timeout = 50
interactive_timeout=50
connect_timeout = 50
#max_allowed_packet = 8M
max_connect_errors = 50
join_buffer_size=256K

####Disable Strick Mode ####
sql_mode=NO_ENGINE_SUBSTITUTION

[mysqldump]
quick
max_allowed_packet = 512M

And after run mysqltunner i got this:

Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    tmp_table_size (> 20M)
    max_heap_table_size (> 20M)
    table_open_cache (> 2000)
    table_definition_cache(400) > 91182 or -1 (autosizing if supported)
    performance_schema = ON enable PFS
    innodb_buffer_pool_size (>= 19.2G) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

What best configuration should i put on my.cnf ?

default-storage-engine = MyISAM

Better to be in InnoDB.

You currently have 19GB of InnoDB data, but no setting for innodb_buffer_pool_size?  That VARIABLE should be perhaps 70G for that server.

To dig deeper, let's get a more thorough analysis of variables, plus look for slow queries.  See http://mysql.rjweb.org/doc.php/mysql_analysis


Does SQL Server Express 2016 come with SQL Server Configuration Manager? Does SQL Server Express 2016 come with SQL Server Configuration Manager?

If not, is there any licenced way to add SQL Server Configuration Manager to an existing installation of SQL Server Express 2016?

If you already installed SQL Server 2016 Express Edition, you can find configuration manager here:

Computer Management > Services and Applications > SQL Server Configuration Manager


Tempdb log file was growing continuously on SQL Server 2008 R2-SP2. Could a pending insert statement which uses temporary table be a cause? I just started at my new job as a DBA. All of a sudden, we started getting alerts about disk space. When looked into it, I found that tempdb log file was growing continuously. There was an active transaction running against it. I checked the query and it was using temp tables. From 45 minutes, it hadn't progressed at all. I tried many things including these as a last resort.

use tempdb
GO
DBCC FREEPROCCACHE -- clean cache
DBCC DROPCLEANBUFFERS -- clean buffers
DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
DBCC FREESESSIONCACHE -- clean session cache

But it didn't help at all. I also killed the insert which was stuck. Seems like it was not out of the norm process. Finally I had to take an emergency outage of few minutes and restart SQL Service. Any tips about what I may have missed?

on this question:

How to identify which query is filling up the tempdb transaction log?

you will be able to see a lot more, in different ways and with more technical information as why this is happen and what to do regarding it.

below is a script that I have to check what is filling up the tempDB.
it may be a good starting point for your quest.

use tempdb

SELECT top 100
tsu.internal_objects_alloc_page_count,
tsu.user_objects_alloc_page_count,
es.host_name ,
es.login_name ,
es.program_name,
st.dbid as QueryExecContextDBID,
DB_NAME(st.dbid) as QueryExecContextDBNAME,
st.objectid as ModuleObjectId,

SUBSTRING(st.text, er.statement_start_offset/2 + 1,
          (CASE WHEN er.statement_end_offset = -1 THEN
               LEN(CONVERT(nvarchar(max),st.text)) * 2 ELSE
               er.statement_end_offset
           END - er.statement_start_offset)/2) as Query_Text,

tsu.session_id ,
tsu.request_id,
tsu.exec_context_id,

(tsu.user_objects_alloc_page_count
  - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,

(tsu.internal_objects_alloc_page_count
 - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,

er.start_time,
er.command,
er.open_transaction_count,
er.percent_complete,
er.estimated_completion_time,
er.cpu_time,
er.total_elapsed_time,
er.reads,er.writes,
er.logical_reads,
er.granted_query_memory

FROM sys.dm_db_task_space_usage tsu
inner join sys.dm_exec_requests er
        ON (     tsu.session_id = er.session_id
             and tsu.request_id = er.request_id)

inner join sys.dm_exec_sessions es
       ON ( tsu.session_id = es.session_id )

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st

WHERE (tsu.internal_objects_alloc_page_count
       + tsu.user_objects_alloc_page_count) > 0

ORDER BY (tsu.user_objects_alloc_page_count
          - tsu.user_objects_dealloc_page_count)
        +(tsu.internal_objects_alloc_page_count
           - tsu.internal_objects_dealloc_page_count)

DESC


RMAN Creating automatic instance fails I am trying to transport a tablespace in RMAN. The command I'm running is

transport tablespace usr_table auxiliary destination '/oracle/rman/auxFiles' tablespace destination '/oracle/rman';

The initialization parameters used for the automatic instance are

initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=tnpD_pitr_ORCL
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/opt/oracle
_system_trig_enabled=FALSE
sga_target=2048M
processes=200
db_create_file_dest=/oracle/rman/auxFiles
log_archive_dest_1='location=/oracle/rman/auxFiles'
#No auxiliary parameter file used

The error I'm getting is

RMAN-03002: failure of transport tablespace command at 06/10/2020 15:32:40
RMAN-04014: startup failed: ORA-27125: unable to create shared memory segment
Linux-x86_64 Error: 28: No space left on device
Additional information: 3822
Additional information: 2130706432

I need to change the

diagnostic_dest=/opt/oracle

Should I make an auxiliary file and if so do I need to put all the parameters that are shown in the rman output?

My issue was that the auto_extend for one of my tablespaces had been set to off. Once I changed that I was able to run my commands


There is probably a better way to do it but here's how I get the expected result.
I went with dynamic SQL as your date will change and that was the only way I can think of to get different column name depending on the content of the table.
/* Creating a temp table to reproduce */
create table #demo (id int identity(1,1), vendor varchar(30), sales int, [date] date);
insert into #demo (vendor, sales, [date])
values ('John',10,'2020-07-20'), ('John',5,'2020-07-20'), ('Jeff',15,'2020-07-21'), ('Jeff',20,'2020-07-21'), ('John',5,'2020-07-21'), ('Jeff',30,'2020-07-20');
--Adding data with another date to make sure the query works when there will be multiple days
insert into #demo (vendor, sales, [date])
values ('John',8,'2020-07-19'), ('Jeff',12,'2020-07-19');

declare @maxDate date;
declare @mindate date;
select @maxDate = max([date]) from #demo;
set @mindate = dateadd(day,-1,max(@maxDate));

declare @cmd varchar(max)
set @cmd ='
with Last2Days as (
select vendor, sum(sales) sales, [date]
from #demo
group by vendor, [date]
having date between convert(date,'''+convert(varchar(10),@mindate)+''') and convert(date,'''+convert(varchar(10),@maxdate)+''')
)

select  vendor, ['+convert(varchar(10),@mindate)+'] as "Sale '+convert(varchar(10),@mindate)+'", ['+convert(varchar(10),@maxdate)+'] as "Sale '+convert(varchar(10),@maxDate)+'", pv.['+convert(varchar(10),@maxdate)+']-pv.['+convert(varchar(10),@minDate)+'] "Variance"
from
(select vendor, [date], sales from Last2Days) as s
Pivot
( max(sales) for [date] in ([2020-07-20] , [2020-07-21] )) as pv'

exec (@cmd);

drop table #demo;

To recap, I create a CTE to get only the data from the last 2 days. I also sum up the sales value per day/vendor.
From that CTE, I ran a select with a PIVOT to get the output expected.


Stroring a type of an entry There is a table estate with numeric fields districtru and districthe (for a name of a district in Russian and Hebrew), which refer to primary keys of the table districts. The districts table has the following fields:

id INT - primary ID
str VARCHAR - the name of the district in either Russian or Hebrew.

The question: Is it worth to add ENUM('ru','he') ("Russian" or "Hebrew") field to the districts table. It seems not to be strictly necessary as it is possible to conclude if a string in this table is a Russian or Hebrew name (or maybe both) by consulting the table estate (which of its keys districtru or districthe refer to this string in districts table).

If this is a language translation issue and the districts are the same whether they are listed in Hebrew or Russian, you might actually have more success with a slightly different table structure.

Right now, based on your description, you have:

ESTATE
+---------+------------+------------+
| Columns | DistrictRU | DistrictHE |
+---------+------------+------------+
|       1 | 1          | NULL       |
|       2 | NULL       | 2          |
|       3 | 3          | 4          |
+---------+------------+------------+

DISTRICTS
+----+-----+
| ID | STR |
+----+-----+
|  1 | R1  |
|  2 | H2  |
|  3 | R3  |
|  4 | H3  |
+----+-----+

Instead of doing this, I'd propose something slightly different:

LANGUAGE
+----+---------+
| ID |   STR   |
+----+---------+
|  1 | Russian |
|  2 | Hebrew  |
+----+---------+

DISTRICTS
+----+------------+-----+
| ID | LanguageID | STR |
+----+------------+-----+
|  1 |          1 | R1  |
|  2 |          2 | H2  |
|  3 |          1 | R3  |
|  4 |          2 | H3  |
+----+------------+-----+

ESTATE
+---------+------------+
| Columns | DistrictID |
+---------+------------+
|       1 |          1 |
|       2 |          2 |
|       3 |          3 |
+---------+------------+

There are a few advantages here.  First of all, if you need more languages than Russian and Hebrew (say, English or possibly Amharic), you don't need to modify the table structure.  Secondly, you now have three normalized tables rather than one normalized and one with repeating structures.  And thirdly, you don't have to worry about an enumeration field on your DISTRICTS table that might be out of sync with a value in ESTATE.

Tying these together with a query assumes that you know the language you want to display results in (presuming that you don't need to display both names at once).  Displaying all of the names at once becomes slightly more difficult, but if you're pulling this data through a business layer and displaying to end users through an application, the easy version is to use two queries:  one to get the estate(s) and one to get the full listing of districts by estate.  Then, the web server could piece it together.


What are some common and useful fields for a users table in a database? I want to begin implementing user functionality on my website, but I'm not sure exactly what kind of information I should be storing. Obviously the basic things like username, password, password hash, real name, email, etc..., but what are some other things that might be useful to know about the user, in a general sense? (I've been tossing around things like last login, login location)

This is what I recommend for absolute, must-have fields in a users table:

Is there a standard implementation of a Users Database?

Things like last login, login location, login IP, etc. are better served in a historical table where you can run inserts and then query against the table if you need information. The idea is to have infrequent updates in a heavily used table, like users. Purge old records as necessary.


Write a slow SQLITE query to test timeout Is it possible to write a 'simple' SQLite query that will take more than 5 seconds to run.

I am writing some unit-tests in my application to log slow queries, to that end, I would like a slow query that I know will take more than 5 or 10 seconds to run, (and create a warning to be raised/logged).

I was thinking of creating 10 tables with 10 entries in each table and doing a join select on all the table.
But I am not sure if that is a good way of testing a slow select, (rather than a large return set).

You should create a user-defined function that sleeps for the desired amount of time.

If you are restricted to build-in mechanisms, try using a recursive common table expression:

WITH RECURSIVE r(i) AS (
  VALUES(0)
  UNION ALL
  SELECT i FROM r
  LIMIT 1000000
)
SELECT i FROM r WHERE i = 1;

But the number of iterations needed for 5 s is strongly dependent on the CPU speed.


Which Database to choose: MySQL or Oracle I have an application generating time series data (32 channels, 22KHz, 6 secs) once a minute. These have to be stored in a database. At the moment I'm using MySQL with InnoDB tables (file_per_table,file_format=Barracuda).

As the data can be compressed, I'm using a medium-blob column with compression enabled. In compressed form, that would generate about 1TB a year. So far, so good.

But is this the optimal selection? I want to use a free database - but of course there are alternatives like Oracle or maybe PostgreSQL.
The target machine running the DB would be Debian stable. I'm not sure either which file system would be best.

I'm open for any suggestions.

Which db to choose? I would choose PostgreSQL having worked with MySQL and PostgreSQL but it is worth noting how different the databases are.  I will say I have frequently been impressed (and only rarely disappointed) by what sort of abuse I can throw at PostgreSQL only to watch things be handled gracefully.

In your specific case, however, there may be reasons to look specifically at PostgreSQL. The major issue is programmability and advanced indexing but there are other reasons too (basically in InnoDB your table is contained in the pkey index, while in PostgreSQL you can do a physical-order scan of the table (and this has advantages when pulling a lot of rows).  Major disadvantages might include not liking the binary type interfaces (and often saving/retrieving the values can use more memory than I would like).  As for filesystem I know a lot of people who are very happy with XFS and also with ZFS.

Finally one specific tradeoff here is the Pg 8k data page size.  It sounds like your time series would be larger than this, and so would be TOASTed (moved to extended storage).  This speeds up many queries, but it also slows down retrieving the specific time series slightly because extra physical files must be scanned.

As for programmability, basically you could write functions that can look inside a time series for specific useful information and then you can index the output of this function.  Consequently this can be used to speed up searches because the function doesn't have to be run.


Oracle Unknown Character I have a script,

UPPER(REPLACE(REPLACE(REPLACE(REGEXP_REPLACE(TRIM(mbr.mbr_first_name) || ' ' || TRIM(mbr.mbr_last_name), '[-+"=;:,<>?\/.~`!@#$%^&*()_\-|{}[]]*',''), CHR(9), ' '), CHR(10), ' '), CHR(13), ' '))

where it basically removes special characters (as much as possible) and remains only the alphabet and numeric.

But after generation, I still get this (�) black diamond with white question mark.

Is there a way to include this in the regexp_replace or to identify the CHR() value for me to remove this kind of characters

regexp_replace(TRIM(mbr.mbr_first_name) || ' ' || TRIM(mbr.mbr_last_name),'[^a-zA-Z0-9 ]')


How to make SSMS upper case keywords I recently started using Management Studio 2012. When using MySQL Workbench, a handy feature was that I could stay all in lower case and any reserved word (like SELECT, INSERT) would convert to upper case automatically. How do I replicate this behavior in SSMS?

No, Management Studio does not have this native functionality in any version from 2005 -> 2016 CTP 3.2. You may want to check out 3rd party add-ins, like Mladen Prajdić's SSMS Tools Pack or Red-Gate's SQL Prompt, both of which have the capability to format a block of code, including automatically upper-casing keywords. However I don't know of any tool that will do this automatically as you type.

For me, I have been doing it so long, it just comes naturally, and not pressing Shift would actually slow me down. When I get someone else's code where the keywords are lower case, I highlight either the whole block or the offending sections, and hit Ctrl+Shift+U. If I have to highlight the whole section, I then go in to the object / column names and use IntelliSense to case them correctly (I have a real hang-up about ensuring that all keywords are upper case and all entity names match the metadata exactly, because you never know when your code will be deployed to a case sensitive collation).


MySQL: Row size too large (> 8126) I have a table which has 300 columns in it.  Each column is up to 255 bytes (there is a business justification for this).

When I try it create it using VARCHAR(255), I go past the limit for the maximum number of bytes.  So I create is using 300 TEXT fields.  When I then try and insert data, I get the error:

  Row size too large (> 8126).
  Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help.
  In current row format, BLOB prefix of 768 bytes is stored inline.

After reading up on this, I tried to change the table to use Barracuda format by specifying ROW_FORMAT=COMPRESSED.  The issue now seems to be that when I try and create the table using that format, I get the same error.

CREATE TABLE T_ObjLarge__c (Id VARCHAR(18), Name VARCHAR(80),
ObjLarge_Field_1__c TEXT,
ObjLarge_Field_2__c TEXT,
...
ObjLarge_Field_300__c TEXT
) ROW_FORMAT=COMPRESSED ;

The error I get is:

  Row size too large (> 8126).
  Changing some columns to TEXT or BLOB may help.
  In current row format, BLOB prefix of 0 bytes is stored inline.

I am using MySQL 5.5.31 on Linux mint.  No indexes.  I have tried DYNAMIC format; it behaves the same way.

Output of SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';:

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+

This is a very tough question to answer simply because you are going beyond InnoDB current limits.

Your question is not by any means unique. This has been addressed here before

By Me, Aug 14, 2012 : error 1118: row size too large. utf8 innodb
By Bill Karwin, Oct 06, 2011 : InnoDB create table error: "Row size too large"

I would also look into the character set you are currently using.

Bill Karwin said it best in his last paragraph

  I also have to comment that I've never seen a well-designed table exceed the row size limit. It's a strong "code smell" that you're violating the repeating groups condition of First Normal Form.

You are going to have to define a better design. No business reason can ever justify it. Why?

Back on July 20, 2011, I answered this question: Too many columns in MySQL

I personally eyewitnessed this

  In my earlier days as a developer, I worked at a company back in 1995 where DB2 was the main RDBMS. The company had a single table that had 270 columns, dozens of indexes, and had performance issues retrieving data. They contacted IBM and had consultants look over the architecture of their system, including this one monolithic table. The company was told "If you do not normalize this table in the next 2 years, DB2 will fail on queries doing Stage2 Processing (any queries requiring sorting on non-indexed columns)." This was told to a multi-trillion dollar company, to normalize a 270 column table. How much more so a 2000 column table.

A table with 300 TEXT columns is asking for same kind of trouble.

SUMMARY : Bill Karwin said it before and I agree: REDESIGN THE TABLE. That will circvumvent the row length issue for sure.


Is fragmention limited to indexes in sql server? As far as I can see with research the only thing that fragments is indexes no other objects in SQL can fragment.

I have to keep track of fragmentation within multiple databases, would it be correct to understand that I only check index fragmentation?
Can any other object fragment?
Does it need to be checked?

While this probably fits better on dba stackexchange, it's kind of a grey area/borderline.

The short answer is that indexes get fragmented and need to be periodically checked and reorganized or rebuilt based on how much they have fragmented.

Going at it we find the fragmentation levels primarily through querying:

SELECT * FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'LIMITED')

However this query also returns the index_types. Where we see the expected clustered and non clustered indexes. But also the 'HEAP' Index type, which, isn't actually an index (the object_ID also doesn't have any kind of name in the sys.indexes view).

These are for tables with no indexes at all, and thus also no primary keys (which automatically creates an index). These too can get fragmented. Because they represent simply how spread out the data is across the disk.

You should also check and 'amend' these, either simply by actually giving them say a primary key. Or, if it's desired that they have no indexes/PK. Simply by rebuilding the table. This reorganizes the data within the table as well.

A quick example of code:

ALTER INDEX [PK_Foo] ON [dbo].[Foo] REORGANIZE --Generally used on low fragmentation count
ALTER INDEX [PK_Foo] ON [dbo].[Foo] REBUILD --Used for high fragmentation count
ALTER TABLE [dbo].[Bar] REBUILD --Used for HEAP indexes with medium-high fragmentation

There are many options that these statements can have, like padding factor. One thing to note is that REORGANIZE allows the table to stay online and other than using resources don't block users. The other options do lock users from accessing the table during the process.


Inner cursor performance issues Before I tried to avoid using cursor because of performance issues. But now I have to do some calculations to reach some goals. I have a stored procedure that has a code like this:

DECLARE  Outer_Cursor CURSOR FOR...

   OPEN Outer_Cursor
   FETCH NEXT FROM Outer_Cursor INTO ...

   WHILE @@FETCH_STATUS = 0
   BEGIN

      DECLARE  Inner_Cursor CURSOR  FOR...

      OPEN Outer_Cursor
      FETCH NEXT FROM Inner_Cursor INTO ...

      WHILE @@FETCH_STATUS = 0
      BEGIN

        ...

        FETCH NEXT FROM Inner_Cursor INTO ...
      END

      CLOSE Inner_Cursor
      DEALLOCATE Inner_Cursor

      FETCH NEXT FROM Outer_Cursor INTO ...

  END

  CLOSE Outer_Cursor
  DEALLOCATE Outer_Cursor

My question is: is it possible to avoid (minimize) so expensive cursor recreation for inner cursor. How can I reuse inner cursor declaration, is it possible?

Thank you.

I'd suggest that instead of using the inner cursor, if you want to avoid its creation and release for every step of the outer cursor, to use a temp table to fetch data and truncate it for each step.

That way you'll create it just once, and the truncation operation is not logged, so it will not cause any problems.

PS: in this situation I'm always in favor of temp tables with an identity column that will be looped for each record (or batches :-).
But I agree with the comments, better tell us the problem and maybe we'll find another solution.


How are Mongodb indexes stored on disk? So let me first start the question with my understanding of how MongoDb stores data on disk: So when you create a database in mongodb, it allocates a large file named <databasename>.0 and within that file allocates extents that are contiguous regions that correspond to data for a particular collection or particular index.

At such point as this datafile is filled, it creates a new file called <databasename>.1 and populates it in a similar way. Therefore it seems sensible to assume that the most recently inserted data into a particular database will be in the highest numbered file (and my performance tests confirm this).

However, I can't see how this could be true for indices... since we're talking about a bTree, it doesn't seem possible/sensible to have this bTree scattered across files in the same way. As Mongo is doing the maintenance for an index, does the whole index live in one extent until it outgrows it at which point it is relocated to the current (highest numbered datafile)?.

This has become important to me as when starting a database from Amazon EBS snapshot, it seems there's a huge overhead for hitting these datafiles until the volume warms up. I am only interested in a subset of the most recent N documents from a collection. If I could be sure I was only going to need the most recent couple of datafiles I could prewarm these files by reading sequentially before starting mongod.

The delay you are seeing when loading from a snapshot is not cause by how indexes are laid out on disk, it's far more likely that you are seeing the delay because when you start an instance from a snapshot, the data is loaded only on first use, and will be significantly slower than subsequent uses - that is a basic limitation of using snapshots in this way and really has little to do with the application that is trying to access disk.  That's why you will see guides on "how to warm up an EBS volume" and the like (there are penalties on writing for the first time too).  If you do that (warm up the disk with another application like dd for example) and the performance issue goes away, then you have pretty decent proof that the layout of data has nothing to do with the issue.

Along those lines, MongoDB has the touch command that will allow you to warm up the data before you use it in anger (you can touch data, data and indexes or just indexes).  Again, after you initially attach the volume, it will be slow, and touch is going to take a while, but at least after that warming up phase, your results should be somewhat consistent.

In terms of how things are stored on disk, you have the basics correct in terms of file allocation but there is a logical structure within the files, extents, that are the real units of storage.  That and far more is covered in detail by this presentation by Mathias Stearn - one of the kernel developers at MongoDB.

Indexes are just another (structured) form of data in MongoDB and they are stored in linked extents throughout the file.  Fragmentation can become an issue (that's what the compact command is for) as can disk space used (the repair command is used to reclaim) but you haven't described a workload that would immediately make me think you are hitting a fragmentation issue which is why I suspect something else (like the first use penalty) is your root cause.


Can SQL Server Replication decrease publisher performance? We have an application that will generate a lot of insert/update transactions per second.
Recently, we've moved the application to a new server and since then, the performance deteriorated significantly (about 3x slower).  I know it could explained by many things but one of the main difference between the 2 servers is the SQL Server replication that was added to the new server.
My comprehension of the replication process is that the subscriber database will try to catch up with the publisher by using it's log file.  A slow latency would certainly affect the time required by the subscriber to synch back, but what about the publisher?  I don't know what are the exact replication configurations but is there scenarios where a publisher's performance would affected by the replication process?

While it's theoretically possible for replication on the Publishing database to be affecting your application's write performance to that database (dependent on many factors) it's less likely that's your issue.
When you change servers, even if it's an exact replica server to the same specs (hardware and software) and same exact version of SQL Server, there's still going to be performance affecting differences such as statistics that will need to be calculated (and potentially change on recalculation) on the new server.
One way it's theoretically possible is if your Distributor lives on the Publisher's server, and is causing resource contention, but this is highly unlikely to be the case. Depending on which kind of replication you're using, there may be other applicable ways it can affect performance as well. For example, Snapshot Replication is more resource intensive than Transactional Replication when it's creates a snapshot (since it's recreating everything, not just the differentials, and locks the table the entire time it's snapshotting it), and therefore isn't meant to be used for as real-time of data synchronizations as Transactional Replication is meant for. If you were using Snapshot Replication and had it scheduled very frequently (e.g. every 5 minutes) then I could see that being more of a potential problem.
But most likely your issue is arising from somewhere else. I'd recommend comparing the old execution plan (if possible) to the new one, and comparing the Time and I/O statistics to try to trace your issue for your application's slow queries. You'll more likely find the root cause going that route.


Do Postgres GIN indexes need to be rebuilt periodically? Do they get bloated like btree indexes? If so how can the bloat be measured e.g. by an automated reindexing script. I am interested in improving query performance and saving space.

Empirically, I've found GIN indexes need to be re-indexed after I've inserted a large number of rows into the table.

I had a table with 15 million rows that had a GIN index that let me do a keyword search in under 3 seconds. After I inserted another million rows, that same search took 30 seconds. I waited for a day after my inserts, but the search was still slow, so I manually ran REINDEX on my GIN index, which took a little over an hour, but afterwards my search was back to 3 seconds.


group by clause without aggregate function I've always assumed GROUP BY was designed specifically for aggregate functions and in all other circumstances you should use ORDER BY. For example, we have three tables: orders, shippers, and employees:

OrderID     CustomerID  EmployeeID  OrderDate   ShipperID
10248   90  5   1996-07-04  3
10249   81  6   1996-07-05  1
10250   34  4   1996-07-08  2

ShipperID   ShipperName     Phone
1   Speedy Express  (503) 555-9831
2   United Package  (503) 555-3199
3   Federal Shipping    (503) 555-9931

EmployeeID  LastName    FirstName   BirthDate   Photo   Notes
1   Davolio     Nancy   1968-12-08  EmpID1.pic  Education includes a BA....
2   Fuller  Andrew  1952-02-19  EmpID2.pic  Andrew received his BTS....
3   Leverling   Janet   1963-08-30  EmpID3.pic  Janet has a BS degree...

We can then determine the number of orders sent by a shipper:

SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;

Group By helps here because it prevents a duplicate shipper name in the result set. That is, the aggregate function itself without using GROUP BY would return two rows of shipper name if shipper name appears more than once in the shippers table. GROUP BY gives our aggregate without duplicates.

Makes sense. But then I come across this result set from an ORM (ActiveRecord in Rails in this case):

SELECT users.* FROM users
INNER JOIN timesheets ON timesheets.user_id = users.id
WHERE (timesheets.submitted_at <= '2010-07-06 15:27:05.117700')
GROUP BY users.id

There were no aggregate functions in that sql statement. Shouldn't it be using ORDER BY instead?

This:

SELECT users.* FROM users
INNER JOIN timesheets ON timesheets.user_id = users.id
WHERE (timesheets.submitted_at <= '2010-07-06 15:27:05.117700')
GROUP BY users.id

Finds all users who have a timesheet submitted on or before the given date. It's equivalent to:

SELECT DISTINCT users.* FROM users
INNER JOIN timesheets ON timesheets.user_id = users.id
WHERE (timesheets.submitted_at <= '2010-07-06 15:27:05.117700');

or:

SELECT  users.*
FROM users
WHERE EXISTS (
    SELECT 1
    FROM timesheets
    WHERE timesheets.user_id = users.id
    AND timesheets.submitted_at <= '2010-07-06 15:27:05.117700'
);

It works because users.id is the primary key, so all other fields of users are functionally dependent on it. PostgreSQL knows that you don't have to use an aggregate to guarantee a single unambiguous result for each field in a row because there can only be one candidate users.name or whatever for any given users.id row.

(Older PostgreSQL versions didn't know how to identify functional dependencies of the primary key and and would throw an ERROR about needing to use an aggregate or include the field in the GROUP BY here).


Database not able to startup showing an error in reading control file On Windows Server 2008 R2, when I am trying to startup Oracle 11g database it shows an error:

SQL> startup
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2180024 bytes
Variable Size            1996491848 bytes
Database Buffers         1191182336 bytes
Redo Buffers               16982016 bytes
ORA-00204: error in reading (block 3, # blocks 8) of control file
ORA-00202: control file: 'D:\APP\FROZEN\ORADATA\ORCL\CONTROL01.CTL'
ORA-27070: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 1117) The request could not be performed because of an I/O device error.

The disk is online.

Can any one help me how to resolve this issue?

if you do not have problems with your disks and the controlfile is corrupted you can do the following:
start the database in nomount (startup nomount), then check the output of the command

show parameter control

(run the command in sqlplus). If the command shows more than one control file, you can overwrite D:\APP\FROZEN\ORADATA\ORCL\CONTROL01.CTL with onw of the others controlfile listed in the output.

Alternatively you can try to overwrite the bad controlfile with the snapshot controlfile:

  The snapshot control file is a copy of a database control file created
  in an operating system-specific location by RMAN. RMAN creates the
  snapshot control file so that it has a consistent version of a control
  file to use when either resynchronizing the recovery catalog or
  backing up the control file.

In Windows the default location should be

%ORACLE_HOME%/database

The name of the snapshot controlfile should be:

snapcf_instancename.f

If you can't solve in this way, you have to restore the controlfile from backup.
Regards
Giova


Optimizing query using a WHILE statement I'm trying to convert a view that extracts a yearly amount into something more WHILEish

The query is more or less:

SELECT *
FROM
(SELECT SUM(EmployeePaid)*2 AS Total2013 FROM TblRecords WHERE FYear = 2013) t1,
(SELECT SUM(EmployeePaid)*2 AS Total2014 FROM TblRecords WHERE FYear = 2014) t2,
(SELECT SUM(EmployeePaid)*2 AS Total2015 FROM TblRecords WHERE FYear = 2015) t3

So this returns:

=====================================
‖ Total2013 ‖ Total2014 ‖ Total2015 ‖
=====================================
‖ 42983.31  ‖ 76345.10  ‖  87233.5  ‖
=====================================

Which is nice and easy to use for generating a chart, for example. But as I mentioned earlier I started to look into this sort of query and I believe they can turn into something more compact as in a WHILE cycle. I'm aware of the old time If its not broken don't fix it so any input in the matter would be appretiated.

So far I'm stepping into something around these lines:

DECLARE @intYear INT
SET @intYear = 2013
WHILE (@intYear < Year(GETDATE()))
BEGIN
    SELECT SUM(EmployeePaid)*2 AS CurrYear FROM TblRecords WHERE FYear = @intYear
    SET @intYear = @intYear + 1
END

Right now I'm stuck in trying to get the same output as the original query, since my WHILE its doing the trick but returning output for 3 different queries. I also cant manage to get CurrYear to "gain" a new value in each round, sort of like a CurrYear + @intYear kind of name.

Any hint is appreciated

Iterative approaches do have their place, but generally speaking it is much better to use set based approaches when working with databases.  I've heard that the math says that "set based solutions will be faster than iterative solutions in the vast majority of the cases" several times, but I can't seem to find a good reference at the moment.

If you really need to format it "like a chart" then you would pivot the data.  Here is the pivot:

SELECT
    *
FROM
(
    SELECT
        [EmployeePaid] * 2 AS [EmployeePaid] -- Why 2x?
        , N'Total ' + CAST([FYear] AS NVARCHAR(4)) AS [Year] -- This output must match the 'IN' clause below
    FROM [TblRecords]
) as s
PIVOT
(
    SUM([EmployeePaid) -- Aggregate
    FOR [Year] IN ([Total 2013], [Total 2014], [Total 2015]) -- Match to pivoted columns
)as [pivot]

That gives you the output you wanted:

========================================
‖ Total 2013 ‖ Total 2014 ‖ Total 2015 ‖
========================================
‖ 42983.31   ‖ 76345.10   ‖  87233.5   ‖
========================================

Also in parting I would recommend trying for increased clarity instead of compacting your code.  While it is true the best line of code is the one you never write, the lines of code you do write must be maintainable.  That being said, PIVOT queries aren't the nicest to read because you're trying to get SQL Server to output data contrary to what it wants.  It might be worthwhile to have the application layer format the data instead.  Then your query would be as simple as:

SELECT
    [FYear]
    , SUM([EmployeePaid]) * 2 AS [YearTotal] -- Why 2x?
FROM [TblRecords]
WHERE
    [FYear] IN (2013, 2014, 2015)
GROUP BY
    [FYear]


Visualizing Database Schema I am not a DBA, but an end user that mashes together data from various databases within my company to generate insights.

When I first encounter a new (to me) database, I spend time scrolling through the tables/views/columns in SSMS or Power Query to understand what data is in the database, and how it is structured.  This is a manual process, and I may overlook key tables when scrolling through a long list.

Is there an easy tool that can scan a database and visually represent the database structure?  I believe older versions of Visio could reverse engineer a database and generate a database model/entity relationship diagram, but the current version does not. If it matters, the databases in question are all MSSQL

In SSMS you can easily create diagrams showing the relationships between tables. The basic steps are:

Connect to the database server
Expand the Databases folder
Expand the folder for the database you're investigating.
Right click on the Database Diagrams folder.

a. Select Install Diagram Support if needed
Select New Database Diagram
In the wizard add any tables you're interested in.
Take the appropriate next step:

a. Curse the previous DBA/Developer that generated this mess.

b. Thank your deity of choice for the blessing of a well structured/modeled database.

c. Shrug your shoulders and tell yourself this is manageable.


AWS RDS showing large growth in Write Ops/sec, despite low # DB connections? I have a Multi-AZ AWS RDS instance running MYSQL 5.5

I've noticed that my write ops/sec are growing fairly rapidly, despite low (and sometimes zero) DB connections - see charts for Avg write ops/sec over last 12 months and last 2 weeks, and Avg DB connections over last 2 weeks:

I know the overall level of write ops/sec is low in the scheme of things, but I'm expecting more volume (like 100x) over the next few months and want to make sure I'm on top of any issues.

I'm trying to understand what is causing these write ops. I've tried connecting to the RDS instance and executing:

show full process list

and this shows:

+--------+----------+---------------------------------------------------+-------+---------+------+-------+------------------+
| Id     | User     | Host                                              | db    | Command | Time | State | Info             |
+--------+----------+---------------------------------------------------+-------+---------+------+-------+------------------+
|      7 | rdsadmin | localhost:51067                                   | mysql | Sleep   |    9 |       | NULL             |
| 705938 | rdsuser  | ip-xx-xx-xx-xx.eu-west-1.compute.internal:xxxx | NULL  | Query   |    0 | NULL  | show processlist |
+--------+----------+---------------------------------------------------+-------+---------+------+-------+------------------+

ie no active threads.

Appreciate any thoughts on what's going on.

UPDATE
Thanks to Roland's response, I did some further digging. In particular, I adding general logging and slow query logging to the RDS instance and saw that a cron job I had running every minute was making unnecessary queries that was making the instance do quite a lot of un-needed work.

I switched on logging by creating a new Parameter Group in RDS Management console and setting:

general_log = 1
slow_query_log = 1
long_query_time = 1

Note that I also used

CALL mysql.rds_rotate_slow_log;

and

CALL mysql.rds_rotate_general_log;

to rotate the log files - as they get pretty large pretty quickly.

If those processes are the only DB Connections in the MySQL Instance, then you can only look to two sources of Write I/O

The VM housing MySQL

Since you are using Amazon MySQL RDS, you can access only the MySQL Instance inside their provisioned VM. There can be lots of reads from MySQL just for their internal purpose. Note that process ID 7 is rdsadmin. They could be rolling up stats on disk for MySQL Usage to report back to the RDS API they provide to have stats to present.

I am sure you could eliminate that by moving our data to MySQL on an Amazon EC2 instance.Of course, you would have to be responsible for your own stats.

InnoDB Architecture

InnoDB has many moving parts that require writes.

Buffer Pool

Data and Index Pages From InnoDB tables That Have Been Modified (Dirty) Must Be Systematically Written to the Double Write Buffer within System Tablespace (a.k.a. ibdata1)
Insert Buffer, which are changes to Secondary Indexes, Must Be Systematically Written to the Insert Buffer within System Tablespace

Log Buffer: When full, the changes within the Log Buffer must written to the Redo Logs (a.k.a. ib_logfile0, ib_logfile1). In a high write system, you should increase innodb_log_buffer_size to 256M to reduce writes to Redo Logs.
Rollback Segments / Undo Space: There are 1023 rollback segments inside System Tablespace. Every time you do INSERTs, UPDATEs, and DELETEs, one transaction will use a single Rollback Segment to save the previous state of data the transaction is about to change. There are internal threads dedicated to cleaning up the UNDO space. Therefore, lots of big writes (or even little writes) will require disk I/O from those internal threads that do the UNDO space cleanup.

I have written about this before

Jul 21, 2012 : InnoDB - High disk write I/O on ibdata1 file and ib_logfile0
May 17, 2012 : why is mysql still writing data two hours after all transactions have stopped


SQL Server 2005 Log Truncate - how long for a 60Gb log; mins, hours or days? I have taken over an old website. To cut a long story short the people that host the computer, backup all the database files, but there is no maintenance plan in place for the backup (e.g. they literally backup the files from the disk, so truncating does not happen).

The recovery mode is FULL (SQL Server 2005).

This is a bit of a "how long is a piece of string" question, but just need a ball park figure really of how long a truncate will take:

BACKUP LOG  databasename  WITH TRUNCATE_ONLY

Is it likely to be minutes, hours or days for a 60Gb log (the machine is reasonable intel 4 core 2.5Ghz 8Gb machine (not SSD drives))?

but there is no maintenance plan in place for the backup (e.g. they literally backup the files from the disk, so truncating does not happen). The recovery mode is FULL (SQL Server 2005).

I guess previous users were taking file system backup which in my opinion is not best practice when you have important app running. You can never get point in time recovery. Your thinking is correct about creating a maintenance plan backup it gives you more control on backup. Plus if you don't want point in time recovery you can put database in simple recovery mode and can just have full and differential backups as per RPO and RTO. This would avoid the hassle of transaction log file backup and unnecessary growth of log file

  This is a bit of a "how long is a piece of string" question, but just need a ball park figure really of how long a truncate will take. Is it likely to be minutes, hours or days for a 60Gb log (the machine is reasonable intel 4 core 2.5Ghz 8Gb machine (not SSD drives))?

First please note log truncations and transaction log backup are not same thing.Its transaction log backup only which is responsible for log truncations in full recovery mode. For DB in simple recovery mode checkpoint causes log truncation or when log grows 70 % of its size.

Regarding how much time transaction log backup of 60 GB log file would take its hard to predict there is not such formula. If you have faster disk where log file resides and faster disk where you are dumping the backup it would take normal time. Backup reads data from disk and writes it to disk so if disk is not facing I/O contention it would be quick.

You must also read

Optimizing backup and restore Performance in SQL Server

Understanding SQL Server Backup


Access to the path 'c:\some\path' is denied for MSSQL CLR I think this is a permissions problem, but I'm having trouble locating it.

I have a group of CLRs on one server (SQL Server 2016) and they work as they should.  All are marked UNSAFE and they do various types of file I/O (read, write, copy, move, rename, etc.).  I can run them via SSMS or from a job with equal ease.

I need to install them on another server (also SQL Server 2016).  Using the original Visual Studio Project I have deployed them to the new sever.  They show up in SSMS.  That part looks fine.

When I, from SSMS, try to run one I get the following error: "Access to the path 'whatever path I passed in' is denied."

I'm logged into SSMS under my windows login.  I have permissions to the database, I'm dbo.  I'm an admin on the server.  I have permissions in the file system.

What else could I be missing?

I have permissions to the database, I'm dbo. I'm an admin on the server. I have permissions in the file system.

None of that matters, typically. Unless you (or whoever coded the SQLCLR methods) implemented Impersonation, then the security context used for external operations is that of the service account running SQL Server (similar to xp_cmdshell behavior). It is that account that needs permission to the path(s) that you are trying to access.

For the sake of completeness regarding file access permissions:

For local (on the box) access, it is as simple as either

the service account (default behavior) for the Database Engine (i.e. MSSQLSERVER or MSSQL$InstanceName) service needing permission, or
if Impersonation has been implemented in the code

and a the login executing the code is a Windows Login, not a SQL Server login, then it is that Windows account that needs permission
but a SQL Server login is being used, the external access is still done as the Database Engine service account

For remote access (shared drive), constrained delegation might need to be set up (via Active Directory; including SPNs). Good 'ol Kerberos double-hop issue. In this case, you would see a difference between logging into SQL Server from another computer, other than the server it is running on vs logging directly onto the server running SQL Server and then connecting to the local SQL Server instance.

Keep in mind that "DENY"s take precedence over "GRANT"s (just like with SQL Server permissions).

In order to determine if the account used for external access actually has the necessary permission to the folder(s) and/or file(s):

Go to the "Properties" of the path in question (the specific file or folder reporting the error)
Go to the "Security" tab
Click the "Advanced" button
Go to the "Effective Access" tab
Click the "select a user" link
Enter in the fully account name (e.g. NT Service\MSSQLSERVER)
Click the "OK" button
Click the "View effective access" button
Does that account have access to that resource?

Are there any DENY permissions anywhere in the path that you are trying to access?

ALSO If all the code is doing is file system stuff, then most likely you don't need to have the assembly marked as UNSAFE and it should instead be EXTERNAL_ACCESS. Not too many file system operations should require UNSAFE. One of them is getting a list of fixed drives, but not sure of what else.


Order results from query to have a certain result at the top I'm not sure there is even a way to do this, but thought I would ask anyway.

I have the following example output from a query:

Name     Country   Number
--------------------------
Harry    Germany   2
Harry    UK        5
Harry    Portugal  6
Zoe      Spain     10
Zoe      UK        5

For the task in hand, we use the data for UK most often, and would therefore like this at the top of the results set. The data is grouped by name, and then split in to country and number, and it needs to be ordered by Name, and then Country (but with UK at the top of each grouping). The desired output would be:

Name     Country   Number
--------------------------
Harry    UK        5
Harry    Germany   2
Harry    Portugal  6
Zoe      UK        5
Zoe      Spain     10

As you can see, UK is at the top of each grouping.

I would appreciate any advice, even if this cannot be done.

Thanks in advance

Use this syntax:

ORDER BY name,CASE WHEN country='UK' THEN 0 ELSE 1 END,country


Does the 'ORDER BY' clause of a subquery pass through to the main results (POSTGRES) I've got a query written something like this.....

with subquery as
(
  SELECT A, B, C, D,
  row_number() OVER(PARTITION BY X ORDER BY Y) as RN
  FROM blah blah
  ORDER BY A,B
  LIMIT 100
)
SELECT C,D
FROM subquery
WHERE RN=1

As you can see, the subquery is sorted, but the top-level query isn't.

The top level query doesn't do anything complicated, it just selects based on the partition by clause.

Will the sort order from the subquery be maintained in the final results?

Observation of the various data-sets I've tested against indicates the sort order IS maintained, but I'm not sure if this is just a lucky coincidence or not.

No, the order by or the sub-query will not influence the order of the outside result.

If at all, it might be influenced by the order by of the window function.

But you can never rely on that.

If you need a specific order for the outer query that you can rely on, you have to use an order by there as well.


MySQL Security - Is there an easy way to encrypt a confidential data and also that it cannot view or access by DBA? Is there an easy way on how can I encrypt a data and store it MySQL  database and also it also cannot be viewed by dba?

Traditionally this is done using a multi-tier architecture where the Application tier performs encryption before storing data in the Database tier.  The database administrators have access to dump all the data, but they don't have the keys.  The application administrators have the keys, but can't dump all the data out of the database.  Logging and auditing helps ensure that the application administrators don't try to extract mass data using their limited access.


mysql hardware refresh + version upgrade currently I am running MySQL 5.0.80 on windows server 2003 (x64), 64gb memory, I have a master and 2 slaves.  It's a large 400 gb Database.  I am setting up all new hardware (SAN + dedicated MySQL servers with 256 gb memory running windows server 2008 r2.  I need to come up with a good plan for moving everything over while minimizing downtime.  Is it absolute truth that you cannot go from 5.0 to 5.6 even when using mysqldump to move the data?  I was originally hoping to run mysqldump during off hours from master (don't fully trust the slaves data integrity), then make master available to users again while I source the sql files into a new 5.6 server and use it as a slave for a day or so until I knew all was well, then promote to master.  Not sure that is feasible because of the jump in versions as well as the replicating (temporarily) from 5.0 to 5.6.  Does anyone have suggestions as to a work plan for moving the master and two slaves onto new hardware with a version upgrade?  Every time i dump the data it will take 5+ hours and sourcing it in even longer so I am trying to be smart about this since my organization is so sensitive to downtime.  thank you.

I think your plan is largely sound, and is how I handled a similar upgarde (albeit 5.0 > 5.5). What I did was to take a Dump from the Master in the quiet hours like you said, and then set up a new Slave on one of the new 2008r2 boxes.

This allows you time to 'play' with the new 5.6 database so you can tweak the config, and make sure the data replicates correctly.
Bearing in mind that as akuzminsky mentioned 5.0 > 5.6 is a big change, and a lot of things will have changed (binlog_format and GTID to name two of the big ones), and various settings will have been deprecated, or removed entireley.

In this way you get to keep the original Master and two slaves in production use, whilst you get up to speed with the 5.6 database. You can also repeat the process with a fresh install and different settings  if things don't quite work as planned.

Once you are happy with the new slave, then you can set up two additional 5.6 slaves connected to the new 5.6 database giving you a new 5.6 system (master and two slaves), allowing you to do more checks.

Finally, once you are completely happy with the new 5.6 system, you can then simply move your application(s) to look at the new master and slaves at a convenient time.

Also, a bit of advice from someone that's been there, keep an eye on your error logs on the new servers. The later versions are more sensitive to errors, and I suddenly found my error logs filling up very quickly, requiring a bit of time to re-write some of the queries in our applications to make them 'replication safe'.


Is it bad practice to launch more than 100 sql statements at once? I have PHP code that is meant to updating the billing status of someone. I only want it to update if the bill has been successfully created and therefore I think a possible solution would be to perform an update after each bill. However, this means that I will end up performing over 100 queries in a very short amount of time.

As I am unfamiliar with database management in general I want to pose this question here.

Perhaps there is a query cache limit that I don't know of or perhaps this is a really bad practice in the database community such that I should be expelled for even proposing this.

I could make a work around where I just collect all of the successfully generated bills and then apply updates to those people, but then I would still need a whole lot of "WHERE" clauses to update only those customers.

Suggestions, ideas, concerns, quips?

I am using postgres for this database, but I would prefer answers that are not specific to Postgres as this database has been migrated before.

It depends on how your queries are organised. Important is that you use transactions. The first step in the transaction would be the creation of the bill and the next step in the transaction would be the update of the billing status.
Both need to succeed or both need to fail. If the second step fails, the first step will also be rolled back.
If this is some kind of a batch process, try to work in sets instead of on single rows. If you can process the work in 1 or 2 calls it will be a lot faster than issuing 100 or 200 calls for the same amount f work. With a lot I really mean a lot. If the code is correct easily a magnitude of 100 faster.


Database structure for a mailing list. I am designing a database structure for a mailing list.

The data I need to record is the mail recipient data:

Since the mail recipient data is not predefined, it is causing a problem.

For example, some of the recipients have name, mail address, phone number, while others have name, birth date, mail address, gender, etc...

So my design is like this:

Table Receiver:
PK: ReceiverID
    AttributeID
    DataID

Table Attribute:
PK: AttributeID
    AttributeName

Table Data:
PK: DataID
    DataContent

An example of one recipient is:

Receiver: 1 1 1
Attribute: 1 mailAddress
Data:1 test@test.com

Receiver: 1 2 2
Attribute: 2 Name
Data:2 Tony

This is causing a problem because I want to check the data field type and length.  What is a better database design?

Sorry about the poor description, I will clarify it if you are confused.

Whenever possible, you want to avoid the EAV design, which is basically what you are proposing.

EAV has it's place, but it's overused.  Essentially you are adding a layer of abstraction to your data by storing the meta-data (describing the actual data) in the data tables, then having to query the meta data before you can actually get at the real data.  It gets incredibly complicated very quickly.

For your (relatively simple) use case, you should make a "normal" table along the lines of:

ReceiverID, ReceiverName, ReceiverAddress, ReceiverBirthdate...

As long as you have a couple of MAIN fields (I'm assuming you will want at a minimum an address and an ID), the others can be populated or NULL depending on if you have data for those.

There's no need to use an EAV model for something so straightforward, especially if you  want to use it just to account for missing data.


Should we favor multiple INSERTs or multiple UPDATEs I always have an impression that INSERT would be beneficial for performance (let's forget storage for now) rather than UPDATES, in all the DB that I know. Especially in Postgres, UPDATES involves transaction and may result in locks

Imagine we are building a shopping cart. For each item I have a table with these fields [row_id, product_id, quantity].

Should I model data for UPDATE (I update quantity column multiple times when user add/remove from cart)

[row_id = 1, product_id = A, quantity = 3]

or to model for multiple INSERTS (I insert a line for each time user clicks, and remove all when they remove from cart) like an event stream

[row_id = 1, product_id = A, quantity = 1] // user clicks add to cart
[row_id = 2, product_id = A, quantity = 1] // user clicks add to cart
[row_id = 3, product_id = A, quantity = 1] // user clicks add to cart
[row_id = 4, product_id = A, quantity = -1] // user clicks remove from cart

All changes to the database involve transactions and may result in locks.
In general, locks are a problem only if concurrent transactions try to access/modify the same row. But concurrent updates are unlikely to happen for a shopping cart.

Postgres does not actually update rows. Other transactions that started before the UPDATE transaction has committed must be able to see the old row, so it can be necessary for the old row version and the new row version to exist at the same time. Therefore, an UPDATE command just inserts a new row, and marks the old row as outdated.

I do not see any upside for doing one INSERT per click. (Except that your code has to choose to do either INSERT or UPDATE, or to use INSERT … ON CONFLICT DO UPDATE.)


HeidiSQL solution for SQL Server error 18488? I'm connecting to a remote database using HeidiSQL client but looks like the administrator has activated the enforce password change policy (Error 18488).

Is there a workaround to solve this with HeidiSQL? Or I need to download and install SQL Server Management Studio?

You can change the password from sqlcmd using -z or -Z:

  -z new_password Change password:

sqlcmd -U someuser -P s0mep@ssword -z a_new_p@a$$w0rd

  -Z new_password Change password and exit:

sqlcmd -U someuser -P s0mep@ssword -Z a_new_p@a$$w0rd


Oracle losing connection ( ORA-03114 ) after some time not using the software After some time idle, if users try to use the our system, we receive the error:

ORA-03114 - not connected to Oracle.

I know there's a parameter called SQLNET.EXPIRE_TIME.

the recomendation is SQLNET.EXPIRE_TIME=10 but I didn't find what this 10 means.

I'm a SQL Server DBA trying to help our Oracle senior dba.

Our sqlnet.ora file is like this:

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Shoud I add **SQLNET.EXPIRE_TIME=10** on it?

To simulate the error, if we open the software and leave it idle for 1hr, we receive the error.

IDLE_TIME is unlimited too on dba_profiles.

Yes, that will work. The 10 is a value in minutes (documentation link).

  To specify a time interval, in minutes, to send a check to verify that
  client/server connections are active

The official Oracle documentation regarding DCD (Dead Connection Detection) is here.

The primary purpose of SQLNET.EXPIRE_TIME=10 is to clean up dead connections, but (I quote):

  A less common usage scenario for DCD is to keep database connections
  alive when an external firewall timeout is configured to terminate
  idle connections


Crow's Foot notation and identifying -vs- non-identifying relationships Is it possible to distinguish between identifying and non-identifying relationships using the Crow's Foot notation?

Case in point: In table Book, part of the book's Primary Key is a FK to the Author table. In other words I embed the author's identifier in the book's identifier. Also, a book has a FK to a Book_Type (containing values like "hardcover", "papercover") (this is just an example, I understand that I would probably need a "book edition" table for that).

The thing is that this latter FK relationship isn't nearly really as important as the former one (the FK to Book_Type is not part of Book's Primary Key), yet my understanding is that in the Crow's Foot diagram shown below, both these relationships are rendered identically:

Have I misunderstood something or is there a way to signify the identifying versus non-identifying relationship distinction using the Crow's Foot notation?

Information Engineering methodology placed a big "I" on the relationship to show the foreign key was part of the primary key:


TRC: Select an ID if it appears on all measurements I have these two "tables":

Station (station_id, city)            // Stations details
Rainfall (station_id, date, amount)   // Rainfall measurements

With Tuple Relational Calculus (TRC), I need to get the IDs of all stations that have related measurements on every date in which any measurement was taken.

So with the following tables I'm supposed to select 5 because this station has measurements on every date that has a measurement.

       Station                             Rainfall
       -------                             --------
--station_id-----city--      --station_id-----date-----amount--
      5           LA               5          01/01      4
      7           NY               7          02/02      8
                                   5          02/02      3

I've tried this but I'm not quite sure if this would work if there are no measurements at all (in this case I'm supposed to select all station IDs):

{ t | ∃s ∈ Station (t[station_id] = s[station_id] ∧
    ∀r ∈ Rainfall (∃q ∈ Rainfall (r[date] = q[date] ∧ s[station_id] = q[station_id])) ) }

I need your help in understanding if what I did is correct.

I had to look up wikipedia because I did not know Tuple Relational Calculus. I did only  skim over that article. There are differnces in notation as far as I can  see.

But after activating my knowledge about formal logic I think you are right because

∀r ∈ Rainfall (...)

is true if Rainfall does not contain any tuples. In this case your query reduces to

{ t | ∃s ∈ Station (t[station_id] = s[station_id]}

and this describes all tuples of Station

What I am missing in your notation is that is unclear which attributes the tuple t of the solution contains. So if you want to select station_id  then the query should start with something like

{ t: {station_id} | ...}

if one uses the notataion of the wiki article.

Maybe you can supply some reference to your notation.


sql server replication - how to find and drop subscriptions? I had 2 subscriptions to a publication,
but they were from the same subscriber server and writing to the same subscriber database, just one was bad and one was running fine.

I could not find a way to delete the bad one, so I decided to drop both, and then recreate the subscription.

  on the publisher server

use [SupplierDB]
go
exec sp_dropsubscription @publication = N'SupplierDB',
@subscriber = N'REPLON1',
@destination_db = N'SupplierDB',
@article = N'all'
GO

That dropped the good subscription.

Because I could not find a way to drop or even see the bad subscription I decided to drop the publication, but when I try it I get the following error message:

-- Dropping the transactional publication
use [SupplierDB]
exec sp_droppublication @publication = N'SupplierDB'
GO

  Msg 14005, Level 16, State 1, Procedure sp_MSdrop_publication, Line 75
  Cannot drop the publication because at least one subscription exists
  for this publication. Drop all subscriptions to the publication before
  attempting to drop the publication. If the problem persists,
  replication metadata might be incorrect; consult Books Online for
  troubleshooting information.

How can I find and drop this subscription?

This is a problem that I originally had on another question:
NoSync subscriptions will need to be dropped and recreated

You need to look at the actual catalog/metadata table(s) and clean them up. The GUI and stored procedures often don't cleanup the underlying tables properly. So, after you have run the sp_dropsubscription SP run a select from MSsubscriptions and MSsubscriptions to see if there is any data referring to your unwanted subscriptions. See: msdn.microsoft.com/en-us/library/ms179855(v=sql.105).aspx.
Delete any row(s) representing subscriptions that shouldn't be there and it should resolve the problem. You may need to set sp_configure 'Allow Updates' = 1 to make changes to the table(s).


Compression setting not effective for differential backup maintenance plan? We are taking backups (Full, Differential, and Transaction Log) of databases using the maintenance plans feature in SQL Server.

While taking the backup, I have chosen the compress option to compress the backup files, and I have enabled "backup compression default" also.

Full backup maintenance plans are compressing the backup file; file size is 4 times less than the database size. But when we comes to differential backup, I think the compression setting is not working.

I have observed today's backup differential file. Yesterday 4 tables in a database got populated with 10GB of data each, so differential backup file size crossed 40GB. I am certain these were the only changes, because it is a fresh database, and there were no maintenance activities run on that day.

The .ldf file size grew to 500 GB on the day the backup was performed. This might cause an issue for big size, but database size (.mdf) is less than 50GB.

Please look at the below image for additional info:

I want to confirm that compression will not work for differential backups, or perhaps there is something else I missed?

If it does not work, based on Paul Randal's database percent change script, I may decide to take full or differential backup.

Version

select @@version;

Microsoft SQL Server 2012 (SP1) - 11.0.3156.0 (X64)
    May  4 2015 18:48:09
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Size Info

select type,
       backup_size,
       compressed_backup_size
from msdb..backupset
where backup_start_date >= '20170927'
and database_name = 'yourDB'

type    backup_size      compressed_backup_size
I       213,473,351,680  42,147,293,093

Community wiki answer:
Checking compression
You can check if your backup is compressed or not. There are a few methods in How can I tell if a SQL Server backup is compressed?.
You can also check the value of compressed_backup_size and backup_size column of the backupset table in msdb. For example:
SELECT
    database_name,
    type,
    PctCompression =
        CONVERT(NUMERIC(20,2),100 - (compressed_backup_size / backup_size) * 100)
FROM msdb.dbo.backupset
WHERE ...

The update to your question shows that the differential backup was indeed compressed.
Backup larger than expected
That 10GB of data could be spread over a lot of pages, and those pages could total a great deal more than the 10GB you inserted (especially with poor clustered index choices). Note also that differential backups operate at the extent level, so any change to any page within an 8-page extent will result in the whole extent being backed up.
Check the log_reuse_wait_desc after doing log backup or use dbcc opentran. If there is an open transaction since yesterday, your full and differential backups will be larger due to the included active log.
There may be no open transaction now, but if a long-running transaction was active at the time of differential backup, the backup file size will be more than expected.
The fact that your log grew to 500GB (and you take regular log backups) means the log could not be cleared for long time due to something (probably an open transaction). All the log associated with that transaction was added to the differential backup.
If you want to check if it's so, try to restore your database into another (new) database, perhaps on another server. This way you will do 2 things:

You'll verify your full and your diff backups; and
You'll see how much time your differential will take to restore.

If the differential takes a long time (e.g. 3+ hours) it indicates a lot of time was spent undoing the effects of a long-running open transaction.


Do repeated columns affect normalization? For example these tables contain many repeated columns.

Does this affect normalization or this is a common practice? Would it be better to create a separate 1 to 1 table like this.

This is 1 to 0..1 relationship, not 1 to many.

Repeated data violates first normal form itself.

For an example, to be in the first normal form, you cannot have columns like phone1,phone2 in the employee table.

However in your scenario, having the same columns for two tables does not violate first normal form as long as you have a single column for a particular property.

Your approach to have a common table should be based on your application requirements.

External Source

Hope this helps :)


Execute output from query I've got this query that builds my drop login statements.  As part two I want to execute this output.  Ideally I want to do this in a sql job with two steps (generate and execute).  Can this be done?

SELECT 'DROP LOGIN [' + name + ']'
FROM [master].[dbo].[syslogins]
WHERE isntgroup = 0
  AND isntuser = 0
  AND sysadmin = 0
  AND name != 'sa'
  AND name NOT LIKE '##%'
ORDER BY name

What you can do is set the output of your query to concat a string variable.  Something like the following:

declare @sql_command varchar(max);
set @sql_command = '';

SELECT @sql_command += 'DROP LOGIN [' + name + ']' + char(13)
FROM [master].[dbo].[syslogins]
WHERE isntgroup = 0
  AND isntuser = 0
  AND sysadmin = 0
  AND name != 'sa'
  AND name NOT LIKE '##%'
ORDER BY name

print @sql_command;
-- exec (@sql_command);

The PRINT will show you your command, and I've commented out the EXEC but that'll run the generated SQL.  Note, you'll have to uncomment the EXEC for anything to actually happen (be wary beforehand, and ensure this is the behavior you're looking for).

As for the one step to generate, and the other to execute, I'm not quite sure what you'd gain by doing that.  It doesn't sound like you're giving yourself time to "review" the drop statements prior to execution.  But, either way, you can store the statements in a table in one step then execute the recently generated DROP LOGIN commands in a subsequent step if you so choose.  That'd be one way to go about that requirement.

I realize you probably have a requirement for the above task, but please note that you are asking to create a SQL Server Agent job to do something not-so-routine such as dropping server principals.  This may call for seeing why there is actually a requirement and working on the situation at the cause of needing to drop logins rapidly on a reoccurring basis.  Again, just something to think about.


Backup is getting failed with following error Msg 3202, Level 16, State 1, Line 1
  Write on "\w-mum-fp\SQLBKP\Weekly-FL\Interactions_26_01_2014.bak" failed: 1130 (Not enough server storage is available to process this command.)
  Msg 3013, Level 16, State 1, Line 1
  BACKUP DATABASE is terminating abnormally.

Available storage is 400 GB and database backup size with compression is 50GB, still backup is failing. Kindly assist.

The error message "Not enough server storage is available to process this command" is a Windows error message that can be extremely misleading.  The error message indicates there is not enough RAM available to complete the operation.

It is highly likely that Windows is very low on free memory - check the SQL Server "max server memory" setting to ensure there is a reasonable amount of memory left for the operating system.

This command shows how much memory SQL Server is configured to use:

EXEC sys.sp_configure 'max server memory (MB)';

See What is a deterministic method for evaluating a sensible buffer pool size? for details about what this value should be.

If you see this error, open Windows Task Manager and check how much free memory Windows has.


Unable to start Oracle database We have an Oracle database, one data file (users tablespace) of the instance got corrupt and another data file (containing application data) was deleted due to some issue.

The database is in ARCHIVELOG mode, but we neither have data file backup of both data files nor the pertaining archive logs. Hence, I guess we cannot go ahead with the recovery of data files. I am unable to open the database as the data files are not available and face.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

I am just able to STARTUP MOUNT the database

I wanted to ask if we can somehow we can open the database by taking both the tablespaces offline after STARTUP MOUNT the database instance. Or if there is any other way I can start the database.

There was a power issue and the  server got abruptly shutdown. When we tried to restart the database and found that we were facing ORA-01589: must use RESETLOGS or NORESETLOGS option for database open. On further analysis we found that 2 of datafiles were creating problem as one got corrupt other deleted as mentioned. As of now if I try to restart the database it says one datafile which belongs to users tablespace need recovery. I wanted to confirm as if we can start the instance by bringing the tablespace offline.

I tried:

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/flowsystems/oradata02/flsta02/system01.dbf'

I don't have any archive logs.

What you can try is the following while your database is in the MOUNTED estate

alter tablespace USERS offline;

and the same for the application tablespace you lost.

recover datafile 1;
alter database open;

NOTE:

If there is no backup and no way to restoring the lost datafiles, what you can do is backup any other important datafile/tablespace and recreate the database. I think it will be the less painful way to get a fully working database.


Installing instances of older versions of SQL Server Are there any issues to be aware of when installing an instance of previous version of SQL Server AFTER installation of a newer version? e.g. Installing 2008 to a server with an existing 2012 instance?

I can find not statements specific to that circumstance, but it seems risky.

Installing an older version of the database engine should be fine and completely separate (though the superstitious side of me would prefer to do it the other way around).

Don't install client tools or anything - keep it to the engine. If you need visual studio, BIDS, SSDT, SSIS etc., good luck...

Use the latest version of SQL Server Configuration Manager to manage the instances on the box, since newer versions are typically backwards compatible.


Help - SQL query // Extracting from XML // getting error if no data found I am building a query, where I am extract values from XML. I am getting output if the xml has values. But if no xml found am getting error I need help to fill those columns with "yet to get" or something.

below is sample query:

Select Column1,Column2,Column3,Column4,
       extractvalue(xmltype(column5), '/PymtInit:notifyOutwardCrdtTrnsfrStatusReq/') "Response",
Extractvalue(Xmltype(Responsemsg)
From Table.Name
where column5 is not null;

Note: if I give Column5 is not null, I am getting output but I need output without "column5 is not null" also.

Have you tried checking if column5 is NULL in the SELECT list instead of in the WHERE clause? I presume that feeding NULL to xmltype() is the source of your errors.

Select Column1,Column2,Column3,Column4,
       CASE WHEN column5 IS NOT NULL
         THEN extractvalue(xmltype(column5), '/PymtInit:notifyOutwardCrdtTrnsfrStatusReq/')
         ELSE 'No Value Yet'
       END as "Response",
       Extractvalue(Xmltype(Responsemsg))
From Table.Name
where column5 is not null;

I've also added a closing parenthesis to the sixth column in the SELECT list; you may want to give that column an alias as well.

Note: not a frequent Oracle user, and have no experience with their XML tools; just applying standard SQL. Also, of course, untested.


How to rollback the identity seed after deadlock Now that's an approximate sequence of operations Im performing:

SET IDENTITY_INSERT <table-name> ON;
INSERT SOMETHING to <table-name> with explicitly specifyed id
DECLARE @oldID bigint
SELECT @oldID = <some simple logic here>
DBCC CHECKIDENT ('<table-name>', RESEED, @oldID) WITH NO_INFOMSGS;
SET IDENTITY_INSERT <table-name> OFF;

So, as you see, everything's simple: basically, I insert record with explicit ID, then I want to return the identity seed back to its previous value (because the insert will obviously shift it).

I need this kind of strange logic (from the first POW) becase I can have ID-s in a large range (billions of rows) of values where the first digit has some special meaning.

For instance, I have current identity seed 1000007777. Then I want to insert a record with ID 2000007777 (this will shift identity to 2000007778). But I want to turn it back to 1000007777, because I believe that I will never reach the point when an identity collision will occur (I just don't operate such volumes of data). As I said, the first digit serves special purposes.

Now, the problem is, if I rollback the transaction after insert, the identity will not get rolled back (as i got it, that's the design of SQL server, and I believe many DB providers).

I can handle this manually if I am the one who conciously performes the rollback.
But what if there is deadlock (or some error) right at the point before reseed?

How can I handle that (except handling the exception and performing unconditional reseed)? Any ideas?

I believe your best bet is going to be to use a TRY-CATCH.  I've done two things here.  First declare and get your "oldID" before your transaction begins.  This will help to keep your transaction time down and avoid deadlocks as much as possible.  Second I've put the "core" of the rest of it into a TRY block and then in the CATCH block put the reseed again.  This way regardless of the error you will re-set the seed.  I believe this will work even in a deadlock situation.

DECLARE @oldID bigint
SELECT @oldID = MAX(id) from identtest where id<3000

BEGIN TRANSACTION

BEGIN TRY
    SET IDENTITY_INSERT identtest ON;
    INSERT INTO identtest (id, x) VALUES (4000,'y');
    DBCC CHECKIDENT ('identtest', RESEED, @oldID) WITH NO_INFOMSGS;
    SET IDENTITY_INSERT identtest OFF;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    DBCC CHECKIDENT ('identtest', RESEED, @oldID) WITH NO_INFOMSGS;
END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

DBCC CHECKIDENT ('identtest', NORESEED)

All of that being said you may be better off switching your logic around a bit.  Would it be possible for you to set your "normal" inserts to start at say 20000000 and then your IDENTITY_INSERTS to start at 10000000?  This way regardless of your insert the seed will stay at the larger "normal" value.  It sounds like you are a bit far in to do this, but you never know.