Good practices about Data Encryption I would like to know (better ideas/best practices...) what can I use to encrypt my data.
I've used TDE in the past and I really don't like it. because if something happens with the certificate, and the servers, I will not be able to restore the backups.
I'm doing tests after tests and I just don't fell safe about it.
What are some other options to secure my database/data/backup files?
I tried masked data too, but 1) its for sql server 2016+ (and for now we are using 2012).. and 2) it just creates another column with the data masked, with the original still there.
What is a good option for this task?
i want to protect my backups so nobody can "steal and restore" it in another server, and I would like to prevent data steal.
I'm already using a good secutity measure with logins and ETC. only me have the sa pass, I created a function where it changes the pass everymonth, etc etc.

i want to protect my backups so nobody can "steal and restore" it in
another server, and I would like to prevent data steal.

TDE would be the best built-in solution here, however if you solely care about backups and not your .mdf/.ndf/.ldf files  then you could look into backup encryption to encrypt your backups. But this will also need a: Certificate or Asymmetric Key. Third party backup solutions could also be an option.
Source
A solution that works together with TDE encryption is bitlocker:

With TDE alone, the database files themselves are encrypted (as well
as backup files of the TDE-encrypted databases). Therefore, if either
file is copied or stolen via a network, as in the above example, the
attacker cannot read it. But if the attacker gains access to the whole
drive, including SQL Server, he can start SQL Server and read the data
using SQL, because SQL Server is doing the decryption for him. Now if
both BitLocker and TDE are used concurrently, both data theft vectors
are mitigated. Both technologies complement each other, and it is
recommended that you use BitLocker together with TDE for defense in
depth.

Source

I tried masked data too, but 1) its for sql server 2016+ (and for now
we are using 2012).. and 2) it just creates another column with the
data masked, with the original still there.

Data masking is not an encryption feature, it is a masking feature based on your role assignments. It works together with restricting users and their rights to query objects such as ad-hoc querying.

Another solution to look into would be Always Encrypted but that restricts certain querying of that data such as rich computation when using deterministic or (randomized encryption before SQL Server 2019).
Source

I'm already using a good secutity measure with logins and ETC. only me
have the sa pass, I created a function where it changes the pass
everymonth, etc etc.

The better practice is to rename and disable the sa login.


Is there a free/web-based game that requires knowledge of SQL to win? Looking for an alternative way of teaching SQL, and wondering if there is a free/web-based game that requires knowledge of SQL to win. If possible, the game should not only be fun, but also track how much SQL the player knows.

I don't know of a gamified SQL learning tool, but I'll recommend The Manga Guide To Databases as a way to cut through the boredom factor. It's a fast read, even for retro-technical managers, and does a quick survey of how to design and use a DB.


Is MySQL Replication appropriate for keeping a laptop in sync? I've setup Mysql master slave replication with the production server as master. I have three other machines as slaves: 1 laptop, 1 desktop, and 1 server. The desktop and laptop both get suspended to ram or hibernated at least daily, if not several times a day. At first I setup the default 'mixed' replication, which is basically statement-based, until I realized that the computations on the server that involved numerous temporary tables were not getting picked up by the two slaves (the slaves would error out when then couldn't find the temporary tables). The slave server works perfectly, because it's always on.

SO, I switched to row-based replication, thinking that would work better. However, I'm still getting frequent errors after the machines are woken up, the most recent of which is:

Could not execute Update_rows event on table database.tablename; Can't find record in    'tablename', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;

I can use the 'SET GLOBAL_SQL_SKIP_COUNTER=1' trick to bypass the error and continue, but I'm afraid the slaves will get more and more out of sync if I setup that to happen automatically after any error. FYI, the slaves are just used for development and never get written to.

Is this not an appropriate setup for replication usage? It sounded from the documentation that network availability would not be an issue (other than the temporary tables problem, which row-based replication should work around).

Anyone else have experience with this kind of setup? I can provide configurations if it would be useful. I can do a daily import of a mysqldump gz file, but it seems like a clunkier solution, especially as the production server data is growing every day...

Thanks!
Scott

Replication is a tool specifically designed for load handling and failover--not well suited to online/offline syncing. Most replication systems fail immediately on losing connection with a host, and it is up to the admin to resolve the situation, hopefully while one or more of the machines in the cluster are still functional.

What you need is more of a snapshot/restore, or possibly a queuing method. I had one of my team write a simple tool for point-in-time recovery on mysql that may meet your need, although it isn't maintained. We use it in many of our applications.

It uses binary logging to bring hosts up to any point in time. I would suggest continuing replication with the slave server, and using a tool like this for hosts that go offline occasionally.

https://github.com/bryanagee/Pamiris-MySQL-Backup/branches


Analyse differential backup to see why it is so big We quickly have very big diff files. Although the database is a highly transactional database, there is also a lot of data at rest.
1 or 2 days after a full backup was taken, the diff backup is bigger than the full backup. (2 days there because the full backup failed one day , so I was able to witness a second day).
The current backup plan is: every day full, every 2 hour diff backup, and every minute log backup. All backups are in separate files.
Is there a way to analyse what is blowing up the diff backup?
As we take every minute a tran backup, it doesn't need so many restores if we have a diff backup every 2 hours. We took the advice of https://www.brentozar.com/archive/2014/02/back-transaction-logs-every-minute-yes-really/

Take snapshot of sys.dm_db_file_space_usage, the modified_extent_page_count column. This is exactly what is says. How many extents has been modified since last full backup, measured in pages.
This column was introduced in 2016. Prior to that you would crack the DIFF map pages. Just as explained and exemplified by Paul Randal here, and as suggested by @Mo64 in a comment.
Take such a snapshot every 5 minutes into a table, and see when it blows up. Possibly tis is enough for you to determine what is causing this. My guess is some batch job.
If that isn't enough, keep going so you have a reliable pattern ("every Tuesday between 3 pm and 3:15 pm"). You now can run an XE trace on your system at that time and catch the SQL submitted to your SQL Server.


return first record after sum of specific value I have a table where I want to get the first record after a certain value is reached with sum. I'm using PostgreSQL 9.3.

So - for the following table, summing the amount column for a specific user (eg user 1) I want the record where the amount gets to zero or below, in this case record with deposit_id 8.

deposit_id |   user_id            |   amount
-----------------------------------------------------------
1          |   1                  |   +10
2          |   1                  |   +5
3          |   2                  |   +10
4          |   1                  |   +5
5          |   2                  |   +5
6          |   1                  |   -15
7          |   1                  |   -3
8          |   1                  |   -3
9          |   1                  |   -5

What would the best way to structure a query for a specific user to sum the values and stop when the value has reached 0 or below and return the record which pushed it over the edge (the first record where total sum of amounts <= 0)?

A cumulative sum can be reached with a Window function and a CTE, the difficult part is to stop it.

sum(amount) over (partition by user_id order by deposit_id)

create table foo(deposit_id int,user_id int,amount int);
insert into foo values
(1,1,10),
(2,1,5),
(3,1,5),
(4,1,-15),
(5,1,-3),
(6,1,-3),
(7,1,-5);

with CumSum as
(
    select deposit_id, user_id, amount,
           sum(amount) over (partition by user_id order by deposit_id) as total
    from foo
)
select deposit_id, user_id, amount, total
from   CumSum
order by user_id, total desc;

deposit_id | user_id | amount | total
---------: | ------: | -----: | ----:
         3 |       1 |      5 |    20
         2 |       1 |      5 |    15
         1 |       1 |     10 |    10
         4 |       1 |    -15 |     5
         5 |       1 |     -3 |     2
         6 |       1 |     -3 |    -1
         7 |       1 |     -5 |    -6

To get the first element where total <= 0

with CumSum as
(
    select deposit_id, user_id, amount,
           sum(amount) over (partition by user_id order by deposit_id) as total
    from foo
)
select   deposit_id, user_id, amount, total
from     CumSum
where    total <= 0
order by user_id, total desc
limit 1;

deposit_id | user_id | amount | total
---------: | ------: | -----: | ----:
         6 |       1 |     -3 |    -1

dbfiddle here


Need Help Finding a Replacement for sys.sysprocesses The SQL 2016 Data Migration Assistant (Upgrade Advisor) is saying that I need to stop using old system table references.  Namely, it wants me to stop using sysdatabases and sysprocesses.

I have some code that's logging session information based on data from sysprocesses.  Included in that log is the database ID of the spid (session_id).

I have been searching online and have found only two possible replacements, neither of which work fully due to reasons explained below.

Option 1 (sys.dm_tran_locks) - This option seems to work rather reliably if I link back to sys.dm_exec_sessions but I noticed that it will never return a result if the session_id's database context is master or tempdb.  That means I will not be able to reliably fetch the resource_database_id for any session_id that is using master or tempdb.  I also cannot just assume it since there are at least two possible contexts to choose from.

Option 2 (sys.dm_exec_requests) - This option only works on session_id's that are actively running.  Idle/suspended session_id's do not appear in the list.

If I cannot get the dbid for session_id's that are suspended and/or are using the master database context then I will have a gap in my logs.

Is there any other way to reliably fetch this information without the use of sys.sysprocesses?

Unfortunately, Microsoft is aware of the lack of replacements. You can upvote & watch these Connect items for more details:

Deprecation of sysprocesses - DMV's doesn't fully replace all columns - by Tony Rogerson SQL

There is no real alternative to master.dbo.sysprocesses - by GV1973


SQL Server: dm_exec_requests.wait_time vs. dm_exec_sessions.last_request_end_time We have a third-party Microsoft Access application that uses SQL Server 2012 as its data store. It is a well known issue that Access often does not tell SQL Server that it has received the results of a query, causing sessions to sit in ASYNC_NETWORK_IO wait for minutes or even hours although the application screen displays the result of the query almost instantly. In some cases, this can cause blocking. I want to find sessions that are in that state for a long period of time. Eventually, I may want to create a procedure that will automatically kill them.

For each session that is currently in an ASYNC_NETWORK_IO wait, I want to find out how long it has been waiting. However, dm_exec_requests.wait_time is never as high as 2,000 even though the difference between GETDATE() and last_request_end_time is much longer. last_request_end_time equals GETDATE() minus total_elapsed_time (CalculatedStartTime in the query below) if both are rounded to the nearest second. This is also true of dm_exec_sessions.start_time and dm_exec_requests.last_request_end_time.

In the results of this query:

SELECT  SPID            = s.session_id
        ,Program        = s.[program_name]
        ,StartTime      = r.start_time
        ,ElapsedTime    = CONVERT(TIME, DATEADD(SECOND, DATEDIFF(SECOND, r.start_time, GETDATE()), 0), 114)
        ,s.last_request_end_time
        ,r.wait_time
        ,CalculatedStartTime = DATEADD(MILLISECOND, -r.total_elapsed_time, GETDATE())
  FROM  sys.dm_exec_sessions s
        JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
 WHERE  s.open_transaction_count    = 0
   AND  r.[status]                  = 'suspended'
   AND  r.wait_type                 = 'ASYNC_NETWORK_IO'
   AND  r.last_wait_type            = 'ASYNC_NETWORK_IO'
   AND  r.command                   = 'SELECT'
   AND  r.blocking_session_id       = 0
   AND  r.total_elapsed_time        > 10000 -- 10 seconds
 ORDER BY r.wait_time DESC

I must be misunderstanding the definition of some of these DMV columns. Note that in the result, row 9 shows a wait_time of only 629 ms, yet the ElapsedTime (time since the last_request_end_time) is over 28 minutes:

How can I get the results I intend (a row for each session_id that shows how long it has been waiting on ASYNC_NETWORK_IO)?

I see a lot of this from MS Office apps as well.

What is probably happening is that there are multiple ASYNC_NETWORK_IO waits per request while the clients slowly fetch and acknowledge subsets of the result set. Multiple wait events per request would explain why the current wait time is lower than the time elapsed since the last request ended. As Geoff mentioned, wait_time is only the time spent in the current wait event, not the total time the session has spent waiting.

Someone more familiar with the relevant client libraries and protocols could probably confirm whether this behavior is expected. You could probably get more detail on the internals with an extended event session capturing callstacks combined with a network packet capture, but I'm not sure that I'd want to do that in production.

The following extended event session would at least help you confirm that  there are multiple ASYNC_NETWORK_IO waits occurring per query:

CREATE EVENT SESSION [async_network_io_waits] ON SERVER
ADD EVENT sqlos.wait_info(
    ACTION(sqlserver.client_app_name,
      sqlserver.client_hostname,
      sqlserver.client_pid,
      sqlserver.database_id,
      sqlserver.database_name,
      sqlserver.server_principal_name,
      sqlserver.session_id,
      sqlserver.sql_text,
      sqlserver.username)
    --ASYNC_NETWORK_IO waits
    --See SELECT * FROM sys.dm_xe_map_values WHERE map_value = '%NETWORK_IO%';
    WHERE (([wait_type]=(99))
      -- Waits of more than 100 ms only
      AND [duration]>(100)
      AND ([sqlserver].[database_id]>(4))
      AND ([sqlserver].[database_id]<>(32767))))
-- Change the path to something reasonable
-- Up to 4 128MB files
ADD TARGET package0.event_file(SET filename=N'C:\temp\async_network_io_waits.xel',
  max_file_size=(128),
  max_rollover_files=(4))
WITH (MAX_MEMORY=16384 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=300 SECONDS)
GO
ALTER EVENT SESSION async_network_io_waits ON SERVER STATE=START;

If you run something like SELECT * FROM BigTable UNION ALL SELECT * FROM BigTable ... in SSMS you'll see the EE session showing multiple ASYNC_NETWORK_IO events firing for the same query. You might need to change the duration predicate in the EE session to something much lower to capture that in a test scenario with SSMS (again, not something I'd want to do in prod).

I don't have SQL Server 2012 around right now, so I can't test this on the same version you're using.

Extended event sessions can have performance overhead, so I'd probably filter that session more restrictively if I used it in production.

What I would probably monitor for in your situation (using a modified version of your query, not the EE session) is a large difference between the current time and last_request_end_time where the current wait type is ASYNC_NETWORK_IO. I'd probably want to gather data about what is being matched for a long while before automatically killing anything.

I also like the suggestion about exploring dumping the output of sp_WhoIsActive to a table and have had good success with that approach to troubleshooting issues generally.


Why postgres is using an odd index with no relation to the query (Debian 7, Postgres 9.3, dedicated machine with huge cache)

I have one big table called process_data (14gb) and another tiny lookup table called process_location. I'm doing a query between these two and in the explain query Postgres is using an odd index not at all related to the query stuff like this:

Query:

select l.name,
       count(1) as quantity
from process_data pd
     join process_location l on pd.fk_location = l.id_process_location
where pd.active and pd.fk_status = 1
group by l.name
order by l.name
limit 1000

The explain query give me this:

+----------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                  QUERY PLAN                                                                  |
+----------------------------------------------------------------------------------------------------------------------------------------------+
| Limit  (cost=166513.62..166513.88 rows=107 width=33)                                                                                |
|   ->  Sort  (cost=166513.62..166513.88 rows=107 width=33)                                                                                |
|         Sort Key: s.name                                                                                |
|         ->  HashAggregate  (cost=166508.94..166510.01 rows=107 width=33)                                                                     |
|               ->  Hash Join  (cost=4.84..165278.56 rows=246076 width=33)                                                                     |
|                     Hash Cond: (d.fk_location = s.id_process_location)                                                                       |
|                     ->  Index Scan using idx_process_data_last_execution_start on process_data d  (cost=0.43..161890.61 rows=246076 width=8) |
|                     ->  Hash  (cost=3.07..3.07 rows=107 width=41)                                                                            |
|                           ->  Seq Scan on process_location s  (cost=0.00..3.07 rows=107 width=41)                                            |
+----------------------------------------------------------------------------------------------------------------------------------------------+

As we can see, the query is using the index idx_process_data_last_execution_start, wich is:

"idx_process_data_last_execution_start" btree (priority, last_execution_start) WHERE fk_status = 1 AND active

None of its columns are mentioned in the query, so the question is: Why it's being used and how it's being helpful?

The second question is, why it's not using this index I created:

"idx_process_data_fk_location_active_status_1" btree (fk_location) WHERE active AND fk_status = 1

That would make much more sense and it's even smaller in size. The odd index is 41mb long and the second one is 30mb long.

I'm trying hard to understand how indexes work.

none of the columns are mentioned

Yes they are, right here:

where pd.active and pd.fk_status = 1

that matches the condition on the index and thus the index can be used to support the counting of the rows. Reading through all the rows in the index should be faster than doing a seq scan on the process_data table.

Why it's not using the other index I cannot tell. Maybe because the difference in size doesn't really matter for expected number of rows (246076)

Btw: there is absolutely no difference in performance between count(1) and count(*)

A very good site to understand how indexes work is "Use the index, Luke"
http://use-the-index-luke.com/


How to prevent SQL Server database server from running out of disk space? What are the best preventive approaches that we can do to prevent the SQL Server database server from running out of disk space ?

Is there a way to set the maximum database file size (both the PRIMARY and the LOG) ? Once the maximum size reach, it should delete older record to give more free space ?

Notes: I have my transaction log shipping every 2 days, plus daily backup of the db. My hard disk is 500 GB, but the database size is already reaching 490 GB. I'm afraid it'll run out of diskspace soon.

I have my transaction log shipping every 2 days, plus daily backup of the db. My hard disk is 500 GB, but the database size is already reaching 490 GB. I'm afraid it'll run out of diskspace soon.

Taking transaction log backups, as are required for log shipping, internally clears portions of the transaction log, allowing the physical space on disk to be reused.

Assuming it's the size of the log file that's the problem, the frequency of the log backups should be increased. This will mean less physical disk space is required for ongoing operations; the physical file could then be shrunk to reclaim the unused space, probably so it can be used by data file growth.

If you still only want to do log restores on the secondary once every 2 days, that's fine -- the frequency of the backup/copy/restore jobs can be set independently.

Note that this only applies to log files.

If it's the data portion of the database that is growing and the amount of log being generated isn't necessarily increasing (and is a small part of the total space used), you will need to either (a) provision more storage, or (b) implement some kind of archiving/data deletion process, as there is no built-in mechanism that will clear out data.


How to create multiple graphs with TitanDB (+Cassandra) in the same machine? I'm experimenting with TitanDB (with Cassandra as storage) + Rexter.

I've been reading the documentation during hours and trying to create multiple graph DBs on the same machine, but I didn't find the way to do it.

I find two main problems:

In the TitanDB docs, the Cassandra section tells how to create a graph on Cassandra, but the only parameters they comment are the host and the port, there isn't any reference to the graph name :s).
In the Rexter section we have essentially the same problem. We can define a list of named graphs, but every graph only can be mapped to a 3-tuple (backend type, host, and port).

The only way I can imagine is to instantiate more than one Cassandra server, and make that each Cassandra server listens to a different port. Then, configure Rexter to map every graph to a different cassandra instance.

There is a way to create more than one graph using the same Cassandra instance?

See https://github.com/thinkaurelius/titan/wiki/Using-Cassandra
scroll down to "Cassandra Specific Configuration", and
use property "storage.keyspace=" to use multiple keyspaces in cassandra.
Each keyspace will hold a graph. (default keyspace is 'titan'.

So, if you want 3 graphs, create 3 config files, with diff "storage.keyspace" property.

When you open a graph from your client using TitanFactory.open(config), use the appropriate config file.


What is the difference between COUNT(*) AND COUNT([attribute]) in the following scenario? I have the following tables :

DEPARTMENT

department_id
department_name
manager_id
location_id

and

EMPLOYEES

employee_id
first_name
last_name
email
phone_number
hire_date
job_id
salary
commission_pct
manager_id
department_id

I want to count the number of employees working in each department.

I have used the following query :

SELECT department_name, count(*)
FROM departments d LEFT OUTER JOIN employees e
ON e.department_id = d.department_id
GROUP BY department_name;

But in the results the employee which has no employees does not show up in the results. I want the number of employees to show up as 0. How do I do that?

In terms of the speed:

As I know that both the queries (select count(*) or Select Count(id)) are using same execution plan, so should use same amount of CPU as long as blocks are fetched from either buffer pool or from disk not mix.

  In terms of the result, and in your case:

if you said count(*), then it will count the row even if you don't have employees since you are making left join.
if you said count(employee_id), then this will only count the rows "which are exists", means not the null rows.. I think this is what you need to have 0 for your case
If you want to mention the field and you want to count it even if there's no row you have to say count(NVL(employee_id, 123)) while 123 is any value in case null


SQL Server Table Reports Disk Usage 0 Rows but reserved 46 GBs I've executed report on database "Disk Usage by Table"
And I have couple of weird tables, where there are no rows, but disk used couple dozens of gigabytes.
What caused such behavior? What to do with this?

The solution is to TRUNCATE TABLE table_name instead of  DELETE FROM table_name.

P.S.
This tables are stage table and ETL uses them quite often, every 20 minutes data is inserted and shortly after data was processed - deleted.


Using PostgreSQL functions instead of joining? I don't have a question on how to do something but if I should. Sorry if there's a more appropriate forum.

I have a function:

CREATE OR REPLACE FUNCTION get_name(user_id UUID) RETURNS TEXT AS $$
    SELECT username
    FROM   users
    WHERE  users.id=user_id
$$ LANGUAGE 'sql';

I like using this function to translate from user_id to username in other queries. It's a common operation and I thought a function would speed up development time in looking up one-off stats for data analysis purposes. I am not concerned about query speeds. Is this good practice to use a function to perform this operation or should I just perform a join?

I am new to this and my coworker said it was bad practice to use functions for things that regular selects/ joins can perform. He also mentioned that functions should preferably be used for things that regular selects just can't do. So I wanted to ask other people what good practices are?

Edit: If this is a bad practice, would it be more reasonable for the case when the function does a common operation that's a little more complicated?

A function is never going to speed up what SQL already does. In fact, if you don't actually add IMMUTABLE to it, it's likely to slow it down.

I would personally never write such a trite function.

If you're returning one row, I would use a correlated subquery.
If you're needing multiple users, I would do the join.
I would look probably retrieve this kind of information upon auth, like in the initial response, and send it back to the client to handle.

But above and beyond all of that, it's not merely about complexity for your function. You have a DSL that explicitly queries your schema and was created for just that purpose -- namely, SQL. What are you going to do when those functions need more logic and complexity. Been there seen that, the next step is CASE statements in functions. And, then slowly but surely you recreate a dynamic query generation mechanism. It's easier to just dynamically generate the SQL when needed.


Replicating Triggers I am doing some analysis on merge replication and triggers for a project.

I have two tables, Product and Audit that I am replicating.

I have an update trigger on the Product table which is replicated. This trigger adds a row to the Audit table, which itself has an identity column.

On a client subscription I can update an item in the Product table, and it adds an item to the Audit table.

As soon as I sync the changes to the server I get an error,

  A row update at 'ReplicatedDatabase' could not be propagated to
  'ServerDatabase'.  This failure can be caused by a constraint
  violation. Explicit value must be specified for identity column in
  table 'Audit' either when IDENTITY_INSERT is set to ON or  when a
  replication user is inserting into a NOT FOR REPLICATION identity
  column.

Am I right in thinking this error is because the trigger has fired at the client, and then again at the server when merged.

Is there a way to replicate a trigger to a client subscription, and not have it fire again when syncing to the server (if indeed this is what is happening)? My example here is not great, but our product will have times where triggers will need to fire at the client subscriptions.

The same triggers will need to fire at the server if data is updated directly on the server (not via replication).

I believe you want to mark the trigger NOT FOR REPLICATION.  This will prevent the trigger from firing when the Merge Agent makes the update.  Have a look at All about "Not for Replication".


Are there differences in performance of getting data between FullText Search and indexed columns In SQL Server 2008 R2 I have a query which looks like

create stored procedure give_me_art @filter varchar(15),@skl_id int
begin
select
  id,naziv,sifra,isnull(lager.kolicina,0) as lager
from art
left outer join lager on art.id=lager.art_id and lager.skl_id=@skl_id
where sifra like '%'+@filter+'%' or naziv like '%'+@filter+'
end

I have an index on column naziv and column sifra.

I am considering changing this query to full text search. Table art has around 150K records and my main goal is to get faster response from SQL Server, because this query is common during daily work.

If I make a full text index on these two columns and redesign my query to use the full text index, what will happen with performance of this query?

In large part it's going to depend on if @filter is a word or a group of words.  Full text indexes essentially break down the contents of the column into the individual words and let you search for a word (group of words) or a synonym etc.  150k rows really isn't all that much to be searching on as these things go, but you may very well see some performance increase, again depending on what @filter is.  I would also double check the rest of your indexes.  For example one on art.id and one on lager.art_id and lager.skl_id.  Also given how few columns you are returning (assuming that is the case in the real query not just this example) you might consider making them covering indexes by "including" (look at the key word INCLUDE in CREATE INDEX) the extra fields in your indexes.  That lets the query just look at the index and not have to go back to the original table.


Does PostgreSQL have a built in option for converting multi-line text to single line? Does PostgreSQL have a built in option for converting multi-line text to single line?

I need to store multiline text as Name=Value pairs, basically line .ini files, but need to convert Value back to its original form. Value might contain some escape sequences etc.

The multiline text is not that short and could be upto 65K in length.

Postgres does is capable of exporting multi-line text as single lines when it backups a table in plain format without using SQL inserts. It is the type of text passed to the COPY tablename (column1, column2, columnxx)from stdin when the plain text backup is views.

Is the function that does the conversion standalone?

I tried quote_literal but it didn't come out the way I want.

To get row and columns from key=value;key=value;key=value string use , ; acts as newline in this case.

select unnest(string_to_array('key=value ; key2=value2', ';' ));

returns :

column1
 "key=value "
 " key2=value2"

select (string_to_array(a.r, '=' ))[1] as  k , (string_to_array(a.r, '=' ))[2] as v  from (select unnest(string_to_array('key=value ; key2=value2', ';' )) as r  ) as a

returns:

k , v
"key";"value "
" key2";"value2"

To other way around (table test has k and v colums)

select string_agg(k || '=' ||v, ';') from test

if you have table like 'conf_file', 'key', 'value' you can use group by clause in string_agg


Explore postgres recovery log records I simulated a crash in Postgres in middle of a transaction by force closing the Postgres process, after running again the Postgres the data that was changed was recovered to last data before transaction start,thats correct behavior.
I want to see actual recovery logs that do this thing in Postgres.
I spend lots of time with log files in Postgres but didn't find anything related to recovery and it just shows this message when I force close Postgres:

unexpected EOF on client connection with an open transaction

anyone have an idea how I can get actual recovery logs in Postgres?
I searched all over the internet and did not find anything useful except WAL system that generates files that I think might have data that I want but I cannot open them? is there any way?

If it was the client that force-closed, not the server, then the server has not gone through recovery.  The client going away in the middle of a transaction is the same thing as the client explicitly aborting the transaction.

Recovery is what happens when the server crashes. I think you have an incorrect mental model of how PostgreSQL handles transaction aborts. The old data is still there, simply because the transaction that would have rendered it invisible failed to commit.  No explicit recovery is needed.


Partial Backup / Restore Sql Server Simple Recovery I have a scenario where we are using SQL Server 2008 R2 with FileStream.  I have partitioned the table where our files live into multiple filegroups.  What I want to be able to do is backup our primary data file and then ONLY the filestream filegroups that have changed and move them to another server.  I have a space limitation (files must fit on CD) so I only really want to move those fileGroups that have changed.

Server: Sql Server 2008 R2
Recovery: Simple

Example:

Primary Data File - Move
FileGroup 1 - No Change - Don't Move
FileGroup 2 - No Change - Don't Move
FileGroup 3 -Chagned - Move

My problem is that it appears that because my DB is in simple recovery I can't backup the individual filegroups I want to restore.  I would have to make FileGroups 1 & 2 Read Only and then use READ_WRITE_FILEGROUPS which could make it so my .Bak file is Greater than the CD Size limitation.

So with Simple Recovery is there a way I can still backup Primary Data File and FileGroup 3 separately and restore them or do I have to use READ_WRITE_FILEGROUPS? If so sounds like I will have to change the recovery model on our server.

Thanks,

S

In short, no, you can't backup the individual files that you want to restore. According to BOL, the only way to backup files on a database that uses simple recovery model is if they are all backed up up together: http://msdn.microsoft.com/en-us/library/ms179401.aspx .
Have you considered differential backups of the files? Doing this would give you smaller file size for backups (still requires a full at some point), but would also increase the number of steps required to restore the database. Differentials partial backups are described here: http://msdn.microsoft.com/en-us/library/ms190218.aspx.


Why is my replication lagging all of a sudden? We run a db.m4.10xlarge instance on AWS for our web app(s) and I've recently set up a read replica as a db.m4.2xlarge instance. We've been experimenting with smaller instances for a read replica but they couldn't keep up with the master. The db.m4.2xlarge had no trouble catching up with the master server within an hour or so - ready to go.

Then last night, something odd happened. Write activity on the replica dropped right down and now I can see the replica has drifted 2.5hrs or so from the master (we have constant write activity on one table with timestamps so it's easy to reliably check).

Does anyone know what the cause of this could be? I can't see anything at all in the logs - it's almost as though we've lost a write thread or something like that.. very weird.

Write IOPS last 24hrs:

Replica lag:

If anyone can provide any pointers that'd be helpful. Both the master and replica are MySQL 5.6.34. No changes have been made since spinning up the replica.

Ok, so after much googling I discovered the issue was quite simply we had run out of IOPS to catch up with the master database instance.

The replica DB had 120GB of storage, and the baseline IOPS for RDS is 3x storage space, so in this case it was 360 IOPS.

Cloudwatch can tell you the burst credits you have remaining, and it turned out they were all used up right at the time the replica lag started. This was great as it confirmed my suspicion that we'd run out of IOPS (in addition to @Rick_James suggesting it..)

I've now upgraded the replica to 250GB which gives 750 IOPS as a baseline - based off IOPS when everything was working fine this should be more than enough to keep up with the master server. Also, it's not too expensive ;)

Replica lag after EBS resize to 250GB:

IOPS chart after resize:


How to make tables unavailable for users during certain times in Oracle? Iam a student and one of the questions i am stuck is to implement an IT management rule on all tables in oracle in my own schema using pl/sql.I dont have DBA previlege and the requirement is that on fridays from 08:00-09:00 none of the tables should be available and should display message saying its maintainance time. so basically I have prevent myself from accessing the tables in my schema during those times. I have used the following DML trigger for insert,update and delete

create or replace TRIGGER test_trig

 BEFORE INSERT OR UPDATE OR DELETE ON employees
 FOR EACH ROW
 WHEN (TO_CHAR (SYSDATE, 'DY')='THU' AND
(TO_CHAR(SYSDATE, 'HH24:MI'))BETWEEN '20:25' AND '20:30')

DECLARE
 e_invalid_time EXCEPTION;

BEGIN
 //DBMS_OUTPUT.put_line (' Tables are unavailable at this time.Please try again
later');
RAISE_APPLICATION_ERROR(-20500, 'You may insert'
||' into EMPLOYEES table only during '
||' normal business hours.');

EXCEPTION
WHEN e_invalid_time THEN
DBMS_OUTPUT.put_line (' Tables are unavailable at this time.Please try again
later');
 END test_trig;

But this will only be effective on one table. And SELECT queries will be still performed.I have about 6 tables
I read Fine grain auditing will help with select and all DML commands. what is the bet approach?This is for educational purposes.

You can put the database in a Quiesced State (Oracle Database Administrator's Guide)

Restrict access. Only DBAs will be able to access the DB (i.e. user SYS or SYSTEM):

ALTER SYSTEM QUIESCE RESTRICTED;

Restore the database to normal operation:

ALTER SYSTEM UNQUIESCE;


Granting permission on a particular job to execute on SQL Server 2005 An user is requesting permission on a particular job. How can I give him access on job activity monitor which shows only that requested job?

User need access on that job, he can modify and run too.

You can give the user - SQLAgentUserRole.

From BOL :

  SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own.

-- lists activity for all jobs that the current user has permission to view.
USE msdb ;
GO

EXEC dbo.sp_help_jobactivity ;
GO


How long will it take to rename table How long will it take to rename a table with a size of 219 GB?

This is the specification of our server:

Memory: 65 GB
Available disk space: 266 GB

MySQL Ver 14.12 Distrib 5.0.77

Sorry for the confusion. Our plan is to rename a MySQL table with size of 219 GB.
How long will it take? Do we need to have a large disk space for this migration?

Table's engine: InnoDB.

Renaming a table in MySQL does not require a temporary table.
There are two statements that perform an equivalent operation.
RENAME TABLE t1 TO t2;
ALTER TABLE t1 RENAME TO t2; # as long as no other options to ALTER are also specified

Renaming a table acquires a metadata lock on the table which requires that no statements be running against the table, no transactions have row locks, and no transactions are holding on to consistent snapshots of any MVCC versions of the table.
Either style of RENAME operation will attempt to acquire the metadata lock and subsequently-started statement impacting the table will block, waiting for the pending metadata lock.

If you use ALTER TABLE tbl_name RENAME TO new_tbl_name without any other options, MySQL simply renames any files that correspond to the table tbl_name without making a copy.
— http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

Note that's a link to the 5.0 documentation, and the 5.1, 5.5, 5.6, and 5.7 all have the same information.
The major delay you could see will come from other sessions preventing the RENAME from starting because the locks can't be immediately required.
If you are able to execute a FLUSH TABLES table_name; statement and have it return within a few seconds, that should serve as confirmation that the rename operation will complete in a similar amount of time, since it will need the same locks in order to remove any entries in the query cache related to the table, close the table, and clean up some internal structures like the table share object... the table is automatically reopened by the next statement that accesses it... so it gives you a good test of what to expect and would be a good idea to run first, since it's typically going to make the actual rename operation slightly shorter by leaving less work for the rename to do.
SHOW FULL PROCESSLIST; while waiting for the FLUSH to complete (if there's a delay) will give you an idea of things that might get in the way of the rename operation, and you can safely KILL the flush if you need to.


High Availibility Solution to send data to multiple locations I inherited a very interesting environment and I'm tasked with finding and implementing High Availability/Disaster Recovery solution as soon as possible.

Simply speaking our environment looks like this:

Server 1 - production database(200GB)

Servers 2, 3, 4 - need daily restored copies of production database (have to be updated with production data once per day)

Server 5 - DR server (data loss should be as little as possible, no more than 5 minutes)

We use:

Windows 2012 R2 on all servers
SQL Server 2012 Enterprise
We are happy to upgrade software and hardware (within reason) if that's necessary.

Requirements:

We need solution that:

would require as little maintenance work as possible (only 1 super busy DBA is available)
would allow us to continue producing full, differential and transactional backups(we need them for auditors)

Options I already considered:

I thought about it and I excluded following methods:

1) log shipping because we need to be able to show log backups to auditors and store them for years. Please correct me if I'm wrong but log shipping wouldn't let us create separate log backups.

2) Mirroring only works between 2 servers (principal and mirror) so it doesn't solve problem of additional servers that have to be refreshed daily. Maybe I can use it on only one server and something else on the other 3 servers.

3) Replication is mostly meant for single tables and functions so I'm afraid its not possible to replicate one database on a slow server to 4 other servers.

4) I never worked with other HA techniques so for example AlwaysOn or clusters is a one big unknown for me. I am happy to learn though as long as someone points me in the right direction.

I would be very grateful if you could suggest HA/DR solution that can be implemented in the described environment.

Depending on your level of expertise, I would suggest you - Logshipping or AlwaysON AG.

  1) log shipping because we need to be able to show log backups to auditors and store them for years. Please correct me if I'm wrong but log shipping wouldn't let us create separate log backups.

Logshipping. It allows you to logship your databases to as many secondaries as you want.

You can choose standby option on secondary that will allow you to read data when the logs are not being restored. Think of it as reporting ONLY.

You can take COPY_ONLY log backups that wont break your log chain (or restrict users to COPY ONLY backups) and you can archive your log backups (depending on the frequency you have set up) - so that your auditors can store them. Just dont delete them or have a higher retention period in clean up job of logshipping.

  4) I never worked with other HA techniques so for example AlwaysOn or clusters is a one big unknown for me. I am happy to learn though as long as someone points me in the right direction.

You can implement AlwaysON availablity groups and happily take your Full, diffs and T-Log backups.

The important thing is that there is a learning curve for you. All the servers should be part of the same windows cluster, there is DNS dependency, you application should be able to use the new provider and connection string, etc. Since you are using Enterprise editon, this is something that you can try as a Proof of Concept and once you are confident, you can transition from logshipping to AlwaysON.

Note: We are currently transitioning from Logshipping to AlwaysON for our huge environment of 1000+ databases being hosted in different continents of the world.

Some references to get you started on ALwaysON part :

SQL Server 2012 AlwaysOn
Getting Started with AlwaysOn Availability Groups (SQL Server)
AlwaysOn Availability Groups
for SQL Server 2012, 2014, and 2016


Postgres Index scan forward vs backward = speed difference of 357X slower PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-51), 64-bit

Dedicated DB server

4GB ram
Shared_Buffers = 1 GB
Effective_cache_size = 3GB
Work_mem = 32MB

Analyze done

Queries ran multiple times, same differences/results

Default Statistics = 1000

Query (5366ms) :

explain analyze
select
    initcap (fullname)
  , initcap(issuer)
  , upper(rsymbol)
  , initcap(industry)
  , activity
  , to_char(shareschange,'FM9,999,999,999,999,999')
  , sharespchange || + E'\%'
from changes
where activity in (4,5) and mfiled >= (select max(mfiled) from changes)
order by shareschange asc
limit 15

Slow Ascending explain Analyze:

http://explain.depesz.com/s/zFz

Query (15ms) :

explain analyze
select
    initcap (fullname)
  , initcap(issuer)
  , upper(rsymbol)
  , initcap(industry)
  , activity
  , to_char(shareschange,'FM9,999,999,999,999,999')
  , sharespchange ||+ E'\%'
from changes
where activity in (4,5) and mfiled >= (select max(mfiled) from changes)
order by shareschange desc limit 15

Fast descending explain analyze:

http://explain.depesz.com/s/OP7

The index: changes_shareschange is a btree index created with default
ascending order. The is index size is 32mb

The query plan and estimates are exactly the same, except desc has index
scan backwards instead of index scan for changes_shareschange.

Yet, actual runtime performance is different by 357x slower for the
ascending version instead of descending.

Why and how do I fix it?

Since I like replacing aggregate functions by old-fashioned self-joins and NOT EXISTS clauses, here is my attempt:

SET search_path='tmp';

DROP TABLE tmp.changes CASCADE;
CREATE TABLE tmp.changes
        ( id integer NOT NULL PRIMARY KEY
        , fullname varchar
        , issuer varchar
        , rsymbol varchar
        , industry varchar
        , activity INTEGER NOT NULL
        , shareschange FLOAT
        , sharespchange FLOAT
        , mfiled FLOAT
        );

        -- lacking information from the OP
        -- I can only presume a flat distribution.
INSERT INTO tmp.changes(id, activity, shareschange,sharespchange,mfiled )
SELECT nm.*
        , (random() *20)::integer -- mfiled
        , random() *10000
        , random() *100
        , random() *100000
FROM generate_series(1,1000000) nm
        ;

ALTER TABLE tmp.changes
        ALTER shareschange
        SET STATISTICS 1000
        ;
ALTER TABLE tmp.changes
        ALTER mfiled
        SET STATISTICS 1000
        ;

VACUUM ANALYZE tmp.changes
        ;

CREATE INDEX changes_mfiled_shareschange
    ON tmp.changes(mfiled,shareschange)
        ;

EXPLAIN ANALYZE
SELECT initcap(ch.fullname) AS some_name1
     , initcap(ch.issuer) AS some_name2
     , upper(ch.rsymbol) AS some_name3
     , initcap(ch.industry) AS some_name4
     , ch.activity
     , to_char(ch.shareschange,'FM9,999,999,999,999,999') AS some_name5
     , ch.sharespchange || '%' AS some_name6
FROM   changes ch
WHERE  ch.activity IN (4,5)
        -- NOTE: the subquery is *not* correlated.
        -- [I had expected a subselect of nx.activity IN (4,5)
        -- like in the main query. ]
AND    NOT EXISTS (SELECT * FROM changes nx
        WHERE nx.mfiled > ch.mfiled
        )
ORDER  BY ch.shareschange ASC
LIMIT  15
        ;


By Mistake Deleted AlwaysOn Availability Database from live Production Server Today by mistake deleted The AlwaysOn Availability Database from live server.
Let  I am explaining the Details.
In AlwaysON Availability Groups, there is 2 Availability Replica. That is

Primary Replica Server (That is SQLServer1)
Secondary Replica Server (That is SQLServer2)

In Primary Replica Server, there is 4 databases.

AlwaysOnTest
Database2
Database3
Database4

In Secondary Replica Server (SQLServer2) , I can't say exactly because that is another SQLserver .But frankly i would like to say that i don't have access of that server.
When i got the accessibility of primary server it's already configured by Third party Vendor.
As a DBA my task was to take backup of all databases in SQLServer1 . Also take backup of AlwaysONTest Database.
Where AlwaysOnTest  Database of Primary Replica Server(SQLServer1) is synchronize with Secondary Replica server (SQLServer2). This is only one database , which synchronize with secondary replica.
Today at 12:10 P.M at Qatar time by mistake AlwaysOn database deleted.
I have Full backup of Yesterday of Primary Server.
Note: The Backup Preference of Backup is Secondary Only.
After instance I have also taken full backup of primary replica.
Now i want to again configure AlwaysONTest Database with Secondary Replica.
Note: Only by mistake deleted AlwaysOn Availability database. The environment is as it is.
What i shall do . Any suggestion or help will be appreciated.

It is not possible to delete a database that currently resides in an availability group. It is also not possible to restore over top of it while it remains in the availability group. Thus, this database was not in an availability group to begin with.

What you are describing is not possible as you will get an error if the database is involved in Always On. You'll receive the following error:

  Msg 3752, Level 16, State 1, Line 1 The database '<Database name>' is
  currently joined to an availability group. Before you can drop the
  database, you need to remove it from the availability group. You have
  to SET HADR OFF before issuing a drop database.


Why doesn't Oracle expdp create the dump file? I'm trying to export a schema from an Oracle 10.2.0.5.0 database. For some reason, when I launch the expdp command, it finishes almost immediately and it only creates the log file, no dump file at all. It must be some silly mistake but I can't figure it out.

This is the command I'm executing on the server (logged in SSH as the Oracle user):

expdp my_user/my_pass@db schemas=my_user directory=my_dir dumpfile=exp.dmp logfile=exp.log

With SYS I get the same result:

expdp \"sys/sys_pass@db as sysdba\" schemas=my_user ...

There is only one database in the server, so I've also tried without the @db part. Same result.

I've checked that my_dir is properly defined and points to an existing filesystem location on the server, with available space (~3.4 GB free) and where the Oracle user can read and write. I've also granted read and write privileges to my_user on my_dir. And I've quadruple-checked that my_user is the correct schema with the tables I want to export.

This is the only output I get, both on the console (except the ;;;) and the logfile:

;;;
Export: Release 10.2.0.5.0 - 64bit Production on Monday, 08 April, 2019 13:27:31

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

And then nothing. No dump file, no errors, no nothing. It just ends.

I've found this page with a few useful (?) queries. v$datapump_job and dba_resumable are empty. There are a few exports in dba_datapump_jobs, all of them with status NOT RUNNING; I don't know for sure because there isn't any "date" field, but I think they are old jobs, they don't change when I launch the expdp command.

What else can I check?

Edit: if I run echo $? immediately after the expdp command, it outputs 1, which according to this page (for Oracle 12c, not 10.2...), means "exit with fatal errors". Yet, not important enough to write them in the logfile?

It was an issue with the TEMP tablespace, which was stored in a disk partition with no more available space.

Following CaM's suggestion (thanks!), I checked that my_user doesn't have the EXP_FULL_DATABASE role, so I launched again the expdp command but without the schemas parameter. For some reason, doing it this way Oracle finally took pity of me, gave up its shenanigans and decided to show the error:

ORA-39125: Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WORK_ITEMS while calling
           DBMS_LOB.CREATETEMPORARY []
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

After "moving" the temp datafile to another partition and having a nice fight with Unix to release the disk space, the expdp finally works again.


Real time info charts - Updating via trigger or via schedule I have 100K records table that record activities of users. I want to show some charts base on that data value per time unit. So I've created a chart base table.

Chart table have 3 columns

Time unit (primary key) column (1,2,3,4...)
Absolute value per time unit - example: 1, 2, 7, 20 ...
Accumulated value (and growing) per time unit - example: 1, 3, 10 , 30 ...

Which is better?

Update Trigger (when status change) - ... I assume that CPU work light only on specific record on every change to the table.  Accumulative value will be pass to the next time unit by internal select.

OR

Aggregation Updating each time unit (by schedule) - ... I assume that CPU work hard on all records. Accumulative value will be calculated on all records till current time unit.

So which approach is make more sense on real time info charts?

How many times will be a row updated ?
How many rows in percentage are updated per Aggregation ?
Trigger is a possible solution for a reduced number of updates , aggregation is a much better solution if large percentage of the table is updated .


Mutual authentication via SSL Oracle 11g I configure mutual authentication via SSL between client (Windows 7) and server (Windows Server 2008 R2). I create wallet via Oracle Wallet Manager (OWM) both on client and server, then  I create certificate requests for client and server in OWM, as it's shown in image below.
I create CA-certificate in OpenSSL according to:
openssl> genrsa -out root.key 2048
openssl> req -x509 -new -key root.key -days 1000 -out root.crt

Then I create sertificate request:
openssl> genrsa -out serv.key 2048
openssl> req -new –key serv.key –out serv.csr

Further I copy contents of cerfiticate request from OWM to *.csr file, that was created via OpenSSL, create sertificate from this file and sign it with CA certificate.
openssl> x509 -req –in serv.csr -CA root.crt -CAkey root.key -CAcreateserial -out serv.crt -days 500

Similary, I create a client certificate and sign it with CA certificate. Next, I import CA-certificate to OWM via "Import Trusted Certificate" both in client and server and a client or server sertificate via "Import User Certificate" on client or server-machine accordingly. Then save wallets and set "Auto Login" flag.
Next I create connection via Net Manager -> Local -> Profile -> Oracle Advanced Security -> SSL on both client and server. Listener.ora on server now contains this:

SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY
= C:\OraWallet) ) )
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL =
TCP)(HOST = WIN-QV6N8G35RD5.localdomain)(PORT = 1521)) ) (DESCRIPTION
= (ADDRESS = (PROTOCOL = TCPS)(HOST = WIN-QV6N8G35RD5.localdomain)(PORT = 2484)) ) )

Then I add new "TCP/IP with SSL" connection on client and test it, but error appears on client:

ORA-28864: SSL connection closed gracefully

I made trace on server and there is 2 errors:

TNS-12560:TNS: protocol adapter error
TNS-00540:SSL protocol adapter failure

Well, now I don't know, how to fix them.

Problem was solved via using orapki for creating wallets and sertificates for client and server. This manual helped me to solve the problem.


SQL Maintenance Rebuild Fails I am pretty new to SQL and I have this maintenance job that keeps failing and I am not sure how to troubleshoot this. I have troubleshot other job failures with google help to success but this one is eluding me. Any help is appreciated. Here is the log...BTW..is there an easier way to read these logs, or does it just take time and experience?

Date        2/1/2015 4:00:00 AM
Log     Job History (SERVER MAINTENANCE.REBUILD)

Step ID     1
Server      SERVER
Job Name        SERVER MAINTENANCE.REBUILD
Step Name       REBUILD
Duration        04:01:38
Sql Severity        0
Sql Message ID      0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted       0

  Progress: 2015-02-01 06:00:01.80     Source:
  Rebuild Index Task      Executing query "ALTER INDEX
  [DTA_reports_indexcolumn_index2] ON [d...".: 40% complete  End Pro...
  The package execution fa...  The step failed.

Firstly, make sure the account executing the job has sufficient privileges to run alter index.  If that fails, then find the index "DTA_reports_indexcolumn_index2" and see if you can rebuild it yourself by hand.  If that works, then you may have another process hitting this table at the same time the index maintenance is running.  You'll have to look through what else is running around this time.

If rebuilding it by hand doesn't work, then explore what error you get, if you get one.  You might have to drop and recreate the index.  You might also run dbcc checkdb against the database to ensure that there's no corruption and run dbcc checktable against said table to ensure it's not corrupted.


SQL Server 2008 R2 Query For Failed Email Notifications Is there a SQL query I can run to see if there has been any failed attempts to send database mail in the last X Minutes?

Let's say you wanted in the last 5 minutes:

USE msdb;
GO

DECLARE @age INT = 5; -- age in minutes
DECLARE @cutoff DATETIME = DATEADD(MINUTE, -@age, CURRENT_TIMESTAMP);

SELECT recipients, subject, sent_status, send_request_date
  FROM dbo.sysmail_faileditems
  WHERE send_request_date >= @cutoff
  ORDER BY send_request_date;

/*
 In fact, there are some items that appear in the log
 but not in failed items for some reason, and failed
 items doesn't describe the problem anyway. So as @Oleg
 added I would also run this query:
*/

SELECT mi.recipients, mi.subject, l.description, l.log_date
  FROM dbo.sysmail_log AS l
  LEFT OUTER JOIN dbo.sysmail_mailitems AS mi
  ON l.mailitem_id = mi.mailitem_id
  WHERE l.log_date >= @cutoff
  AND l.event_type > 1
  ORDER BY l.log_date;

Sorry, needed to poison an SMTP server to verify that my original answer was insufficient. :-)


Why does a plan with FULL optimization show simple parameterization? I read that only Trivial Plans can be Simple Parameterized, and that not all queries (even when the plan is Trivial) can be Simple Parameterized.

Then why is this plan showing Full Optimization, and Simple Parameterization at the same time?

Simple Parameterization is attempted when a trivial plan is found. The parameterization attempt may be considered safe or unsafe.

The key point is that a trivial plan is found and considered safe. If the cost of the trivial plan exceeds the cost threshold for parallelism, the optimizer will go on to later stages of optimization, where parallel plans may be considered. Whether the final result is a serial or parallel plan, it will be simple parameterized if the safe trivial plan found (but not ultimately used) was parameterized.

In the question example, setting the cost threshold for parallelism higher than the cost of the trivial plan will allow the optimizer to stop at that stage.

Looking at the query plan isn't always enough to figure out if your query has actually been Simple Parameterized.

The safest way is to check some DMVs to verify:

/*Unsafe auto param*/
SELECT *
FROM sys.dm_os_performance_counters AS dopc
WHERE dopc.counter_name LIKE '%Unsafe Auto-Params/sec%';

/*Safe auto param*/
SELECT *
FROM sys.dm_os_performance_counters AS dopc
WHERE dopc.counter_name LIKE '%Safe Auto-Params/sec%';

/*Trivial Plans*/
SELECT *
FROM sys.dm_exec_query_optimizer_info AS deqoi
WHERE deqoi.counter = 'trivial plan';

Additionally, you can also use undocumented trace flag 8607, but not as an OPTION clause hint. Using the OPTION clause prevents a trivial plan.

DBCC TRACEON(8607, 3604);
/*Wait*/

/*Run*/
SELECT u.CreationDate, u.Id
FROM dbo.Users AS u
WHERE u.Reputation = 2;

/*Clean up*/
DBCC TRACEOFF(8607, 3604);

If the plan is considered safe for Simple Parameterization, you'll see a message confirming it here.

********************

** Query marked as Cachable

** Query marked as Safe for Auto-Param


Could not complete cursor operation because the set options have changed since the cursor was declared - SQL Server I get this error when I execute this cursor.

  Msg 16958, Level 16, State 3, Line 56 Could not complete cursor operation because the set options have changed since the cursor was declared.

But after that I review my login and his roles. Everything is fine. So I don't why i get this error but everything went fine.

USE MASTER
GO
  CREATE LOGIN [login1] with password = 'test12',
  CHECK_POLICY=OFF;
GO

declare @ejecutable nvarchar(100)
declare @database   nvarchar(50)
declare @DynCommand nvarchar(max)

declare cr1_database cursor
local fast_forward
for
select name
from sys.server_principals
where name like 'login1';

open cr1_database

fetch next from cr1_database into @ejecutable

WHILE (@@FETCH_STATUS = 0)

BEGIN
         SET @DynCommand =N'
        USE ?
         IF (DB_NAME () NOT IN (''DBA''))
        BEGIN
         create user [' + @ejecutable+ ']
         for login [' + @ejecutable+ '];
         exec sp_addrolemember ''db_datareader'', ['+@ejecutable+']
         exec sp_addrolemember ''db_datawriter'', ['+@ejecutable+']
         exec sp_addrolemember ''db_ddladmin'', ['+@ejecutable+']
         exec sp_addsrvrolemember ['+@ejecutable+'] , ''securityadmin''

        END '

    EXEC sp_foreachdb
    @command = @DynCommand,
    @user_only = 1

    BEGIN
        USE msdb
      BEGIN
        CREATE user [login1] for login [login1]
        EXEC sp_addrolemember @rolename= 'SQLAgentReaderRole', @membername = 'login1'
        EXEC sp_addrolemember @rolename= 'db_datareader', @membername = 'login1'
      END

    END

 FETCH NEXT FROM cr1_database INTO @ejecutable
END
CLOSE cr1_database
DEALLOCATE cr1_database

@Tony You were correct that it is the use db option that messes with the set options (not sure why).
But it is trivial to qualify the relevant queries for master and then run the core of the loop from msdb, negating the requirement to switch the database just to inject the user into that db.

   USE MASTER
   GO
    CREATE LOGIN [login1] with password = 'test12',
    CHECK_POLICY=OFF;
   GO

   USE MSDB
   GO

   declare @ejecutable nvarchar(100)
   declare @database   nvarchar(50)
   declare @DynCommand nvarchar(max)

   declare cr1_database cursor
   local fast_forward
   for
   select name
   from master.sys.server_principals
   where name like 'login1';

   open cr1_database

   fetch next from cr1_database into @ejecutable

   WHILE (@@FETCH_STATUS = 0)

   BEGIN

          SET @DynCommand =N'
         USE ?
          IF (DB_NAME () NOT IN (''DBA''))
         BEGIN

          create user [' + @ejecutable+ ']
          for login [' + @ejecutable+ '];
          exec sp_addrolemember ''db_datareader'', ['+@ejecutable+']
          exec sp_addrolemember ''db_datawriter'', ['+@ejecutable+']
          exec sp_addrolemember ''db_ddladmin'', ['+@ejecutable+']
          exec sp_addsrvrolemember ['+@ejecutable+'] , ''securityadmin''

         END '

      EXEC master..sp_foreachdb
      @command = @DynCommand,
      @user_only = 1

      BEGIN
        BEGIN
         CREATE user [login1] for login [login1]
         EXEC sp_addrolemember @rolename= 'SQLAgentReaderRole', @membername = 'login1'
         EXEC sp_addrolemember @rolename= 'db_datareader', @membername = 'login1'
        END

      END

    FETCH NEXT FROM cr1_database INTO @ejecutable
   END
   CLOSE cr1_database
   DEALLOCATE cr1_database

Which adds the user to all the user dbs + msdb.


Getting notification when log truncation is delayed Is there any way to notify DBA proactively when transaction log truncation has been queued multiple times however for some reason (it could be an open transaction, replication, currently running full backup) failed to truncate the log.

Considering a scenario in which client application initiate a transaction using BEGIN TRAN and perform a huge update, it failed to acquire necessary locks during the set time (as another transaction was blocking) and it times out (in .net commandtimeout is set to default 30 seconds and no error handling in client application) and leave an open transaction on SQL server which in turn prevent log truncation. Now everytime log backup is run it can't truncate log because you have an open (hosed) transaction. I admit we can subscribe to log growth event in many ways but was wondering if there is something built in to the SQL server that notifies when log truncation has been queued multiple times but it failed to truncate it.

I'd approach this from a different tack and monitor for long running transactions.  You can query open transactions using the DMVs (sys.dm_tran_active_transactions should show it).  Here's a query (borrowed and modified from Gail Shaw) that will show you currently running transactions:

SELECT
    ec.connect_time,
    tat.transaction_begin_time,
    tat.transaction_type,
    tat.transaction_state,
    es.host_name,
    es.program_name,
    tst.session_id,
    es.login_name,
    est.text
FROM sys.dm_tran_session_transactions tst
    INNER JOIN sys.dm_tran_active_transactions tat ON tst.transaction_id = tat.transaction_id
    INNER JOIN sys.dm_exec_connections ec ON tst.session_id = ec.session_id
    INNER JOIN sys.dm_exec_sessions es ON tst.session_id = es.session_id
    CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) est

From here, I'd set up a polling job against the DMVs and alert me if a transaction is held open for longer than an acceptable period of time.  What this amount of time is will be dependent on your environment.


Why are collations specified at the column level? The charset defines how the row data is saved into the file store.

Row 17: <CHARSET('utf8', 'hello'), 17, CHARSET('latin-1', 'sup')>

But it seems that collation only affects sorting of these rows.

So why is the collation specified when you are defining the column?

Instead I would expect that you would only specify the collation when defining things that matter with sorting rows, i.e. indexes (because they are stored sorted), queries (because they test equality and inequalities) and constraints (because they are effectively queries).

Unless MySQL / MariaDB does something that I'm unaware of (being that I typically focus on SQL Server): because encoding (i.e. "character set") is handled separately, column-level collation should simply be a default used for those operations that you mentioned (creating indexes, queries, etc), and comparisons, so that you don't need to specify the collation each and every time (strings cannot be either compared or sorted if the specific rules for doing so are not provided). That would not only greatly complicate queries, it would make it nearly impossible to provide consistency across queries as not having it declared at the column level (or at least at the DB level) would leave it to query developers to remember the intention of the data modeler, and that's certainly not a recipe for success ;-).

If not specified at the column level, collation needs to at least be specified at the database level or even the instance level (and I believe there are RDBMSs that do specify at either level, and maybe don't even allow for specifying at the column level).

Collation just needs to be specified somewhere, else you can't even rebuild an index (let alone create it) without providing the collation. And, allowing the granularity of specifying collation at the column level allows for maximum flexibility as different string columns within a table, or between tables in the same database, can easily have different needs / requirements.

And, just as a side note: for RDBMSs such as SQL Server that combine encoding along with collation in a singular "Collation" (and no way to separate the two), it is a necessity to specify at the column level as it indicates the encoding (at least for VARCHAR / CHAR / TEXT column; NVARCHAR / NCHAR / NTEXT are always UTF-16 LE).


Want to know the No. of rows,space used,space reserved in each table against all the databases on server level I need to find the No. of rows,size of table,index size,data used,datareserved in each table against all the user created database on server level.Is it possible in a single script or query.or i have to query each database.Write now the script which i have got is this.

//---script i have to run on each database.
use MyDbName;

GO
CREATE TABLE #TableSpaceUsed
(
       Table_name NVARCHAR(255),
       Table_rows INT,
       Reserved_KB VARCHAR(20),
       Data_KB VARCHAR(20),
       Index_Size_KB VARCHAR(20),
       Unused_KB VARCHAR(20)

)

INSERT INTO #TableSpaceUsed
EXEC sp_MSforeachtable 'sp_spaceused ''?'''

SELECT TOP 1 db_Name() As DB_Name,Table_name,Table_Rows,
  CONVERT(INT,SUBSTRING(Index_Size_KB,1,LEN(Index_Size_KB) -2)) as indexSizeKB,
  CONVERT(INT,SUBSTRING(Data_KB,1,LEN(Data_KB) -2)) as DBSizeUsed_KB,
  CONVERT(INT,SUBSTRING(Reserved_KB,1,LEN(Reserved_KB) -2)) as DBSizeReserved_KB,
  CONVERT(INT,SUBSTRING(Unused_KB,1,LEN(Unused_KB) -2)) as unusedKB
  FROM #TableSpaceUsed
  ORDER BY DBSizeUsed_KB DESC

DROP TABLE #TableSpaceUsed

I believe one I use provides everything you need?

--Table space
SELECT
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages)/128 AS TotalSpaceMB,
    SUM(a.used_pages)/128 AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages))/128 AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
    AND p.[rows] > 500000
GROUP BY
    t.Name, p.Rows
ORDER BY
    t.Name

Note the conditions in the WHERE statement which may be different for you. This is one I obtained from another website I believe, so it isn't my work! I can't find the reference to which website though...

EDIT

OK, this one is better, includes non clustered index space, identifies the schema for the tables and includes decimal points:

WITH idxs AS (
    SELECT
        t.object_id as TableID,
        t.name AS TableName,
        SUM(a.used_pages)/128.0 AS 'Indexsize(MB)'
        --, i.*
    FROM
        sys.tables t
        INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
        INNER JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
        INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
    WHERE i.[type]=2
    GROUP BY t.object_id, t.name
)
SELECT
    s.name AS SchemaName,
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages)/128.0 AS TotalSpaceMB,
    SUM(a.used_pages)/128.0 AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages))/128.0 AS UnusedSpaceMB,
    idxs.[Indexsize(MB)]
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
LEFT OUTER JOIN
    idxs ON t.object_id = idxs.TableID
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
    --AND p.[rows] > 500000
GROUP BY
    s.name, t.Name, p.Rows, idxs.[Indexsize(MB)]
ORDER BY
    t.Name


How to debug database server freeze (not accepting connections, timeouts etc) Apologies if this question is a little bit vague but we are having issues for a couple of days now and have no clue on what may be wrong.

We have a pair of Windows servers with SQL Server installed in high availability (Always On in a master / replica setup). Exact spec is Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30  Enterprise Edition (64-bit) on Windows Server 2016 Standard 6.3  (Build 14393: )

SQL Server has around 7-8 databases to support different services of a single quite sizeable app. The sizes of each database varies but it ranges from a few GB to over 1TB. Some apps

Suddenly (check note 1), 2 days ago our master db server instance unexpectedly stopped working, requests started timing out etc. When I say it hangs, I mean that nothing goes through on any database. The whole DB server just stops working. The OS and server is fine though. Its just the SQL Server instance.

When this happens we need to manually fail-over to the replica, restart the SQL Server processes on the old master and re-attach it to the cluster.

This has started happening 2 days ago and it occurs every 8-10 hours or so. I thought there was a regular frequency but there isn't unfortunately. Through this time we tried pausing all maintenance jobs on SQL Server (index rebuilds, log cleanup, backups etc). The problem still occurs.

I know that you dont have a magic ball to guess whats going on. So this question is how would you go about finding the problem here? What commands or steps would you run to get more info? Obviously we can't reproduce the issue - it just occurs.

Btw, I am not a DBA but I dont quite get why SQL Server doesnt automatically failover since nothing works on master...The underlying process is still running but nothing works...

Note 1: No app releases have happened to justify that. No releases at all really. No major change in db sizes or anything along these lines really.

So this question is how would you go about finding the problem here? What commands or steps would you run to get more info?

Monitoring Tool

Do you have a monitoring tool?  If so, check and see what the wait stats are / were during (or at least leading up to) the incident.

Logging Queries and Waits

If you don't have a monitoring tool, I would recommend setting up SQL Server Agent jobs to log information about wait stats and active queries to a table for later analysis.  There are a number of ways to do this, such as:

How to Log Activity Using sp_whoisactive (for active queries)
How to Log Wait Stats to Table with sp_BlitzFirst (for wait stats)
Wait statistics, or please tell me where it hurts (great overview of your top waits)

DAC

While the problem is occurring, you could connect to SQL Server using the dedicated admin connection, and then run sp_WhoIsActive to see exactly what is running and slowing things down.  Despite not accepting connections from other sources, you should still be able to connect this way.

  When I say it hangs, I mean that nothing goes through on any database. The whole DB server just stops working. The OS and server is fine though. Its just the SQL Server instance.

Blocking

This sounds very much like you're experiencing a long blocking chain that never gets resolved - possibly due to long-running queries, but it could also be due to issues with replica synchronization.  Keep an eye out for LCK_* waits (for the blocking chains).

Slow Synchronous Commits

You mentioned "always on" and if by this you mean Availability Groups, then look out for HADR_SYNC_COMMIT (for the replica synchronization issues).  If the secondary gets overloaded or overly busy, throughput could stall on the primary while waiting for acknowledgements from the secondary.

Poison Waits

It's also possible you're experiencing "poison waits."  Look carefully for those waits in the wait info you start logging (specifically RESOURCE_SEMAPHORE* and THREADPOOL).  This seems less likely to me though, as it probably shouldn't be going on for long periods of time.

  Btw, I am not a DBA but I dont quite get why SQL Server doesnt automatically failover since nothing works on master

This means the server is still responding to "are you up?" checks from the cluster manager, likely because the problem is blocking-related, which is "expected behavior" from SQL Server.  Things are running, they're just not running well.

Additionally, depending on your setup, you might never get automatic failover (if, for example, you have an availability group configured with only an async replica as the secondary).


Could not obtain exclusive lock on database 'model' - who is using it? when trying to run the following query in order to create a new database:

    CREATE DATABASE [Lunch]
     CONTAINMENT = NONE
     ON  PRIMARY
    ( NAME = N'Lunch',
FILENAME = N'E:\Data Files\Lunch.mdf' ,
SIZE = 110592KB , FILEGROWTH = 1048576KB ),
     FILEGROUP [DATA]
    ( NAME = N'Lunch_Data',
FILENAME = N'E:\Data Files\Lunch_Data.ndf' ,
SIZE = 110592KB , FILEGROWTH = 1048576KB ),
     FILEGROUP [NONCLUSTERED_INDEXES]
    ( NAME = N'Lunch_nonclusteredindexes',
FILENAME = N'E:\Data Files\Lunch_nonclusteredindexes.ndf' ,
SIZE = 110592KB , FILEGROWTH = 1048576KB )
     LOG ON
    ( NAME = N'Lunch_log',
FILENAME = N'F:\logFiles\Lunch_log.ldf' ,
SIZE = 524288KB ,
FILEGROWTH = 524288KB )
    GO

Getting the following error message:

  Msg 1807, Level 16, State 3, Line 1

  Could not obtain exclusive lock on database 'model'.

  Retry the operation later.

  Msg 1802, Level 16, State 4, Line 1

  CREATE DATABASE failed.

  Some file names listed could not be created.  Check related errors.

what is using the model database and does not allow me to obtain the exclusive lock?

Your model database is locked now so what you should do is to find out the session that has a lock on it, this can be done using sys.dm_tran_locks:
select request_session_id
from sys.dm_tran_locks
where resource_type = 'database' and
      resource_database_id = 3 and
      request_type = 'LOCK' and
      request_status = 'GRANT';

You can have a user that just opened an SSMS query window with the context of model database and did not ever execute any query there, but that session still holds S lock on the database resource.
Once you have found the locking session, just kill it.


how to identify table name using a column name if we are not aware about the table name how to identify table name using a column name if we are not aware about the table name

Imagine if you have  10000 tables in Database

I am aware about the column name , but i dont know the exact table name .

Could you please advise is there  a chance to find the table name  , using the column name which you are aware about it  already?

You can use INFORMATION_SCHEMA.COLUMNS instead of sys.columns

SELECT
    TABLE_CATALOG,TABLE_SCHEMA,TABLE_SCHEMA,COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    COLUMN_NAME LIKE '%MyColumn'


mysql crashed, after start again, the new data lost, only old records of some day ago I got a server restart, the mysql server seems crashed.
After reboot, mysql database filled of old data for bug tracker.

Do you know how to fix this?
mysql error log as below

 131027 15:29:19 [Note] Plugin 'FEDERATED' is disabled.
 131027 15:29:19 InnoDB: The InnoDB memory heap is disabled
 131027 15:29:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins
 131027 15:29:19 InnoDB: Compressed tables use zlib 1.2.7
 131027 15:29:19 InnoDB: Using Linux native AIO
 131027 15:29:19 InnoDB: Initializing buffer pool, size = 128.0M
 131027 15:29:19 InnoDB: Completed initialization of buffer pool
 131027 15:29:19 InnoDB: highest supported file format is Barracuda.
 InnoDB: The log sequence number in ibdata files does not match
 InnoDB: the log sequence number in the ib_logfiles!
 131027 15:29:19  InnoDB: Database was not shut down normally!
 InnoDB: Starting crash recovery.
 InnoDB: Reading tablespace information from the .ibd files...
 InnoDB: Restoring possible half-written data pages from the doublewrite
 InnoDB: buffer...
 131027 15:29:24  InnoDB: Waiting for the background threads to start
 131027 15:29:25 InnoDB: 5.5.32 started; log sequence number 320322004
 131027 15:29:25 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
 131027 15:29:25 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
 131027 15:29:25 [Note] Server socket created on IP: '127.0.0.1'.
 131027 15:29:26 [Note] Event Scheduler: Loaded 0 events
 131027 15:29:26 [Note] /usr/sbin/mysqld: ready for connections.
 Version: '5.5.32-0ubuntu0.13.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
 131027 15:29:34 [ERROR] /usr/sbin/mysqld: Table './bugtracker/mantis_bug_history_table' is marked as crashed and should be repaired
 131027 15:29:34 [Warning] Checking table:   './bugtracker/mantis_bug_history_table'
 131027 15:29:34 [ERROR] Got an error from unknown thread, /build/buildd/mysql-5.5-5.5.32/storage/myisam/ha_myisam.cc:912
 131027 15:29:34 [Warning] Recovering table: './bugtracker/mantis_bug_history_table'

Do you know how to fix this?

Yes, Apply the latest backup.

If you can not do that because you do not take backups - look please for a job where you are not responsible for managing data. Backups is "professional databases for beginner admins, day 1, hour 1". Fundamental Like not driving a car totally drunk level of fundamental.

That being said, this sounds "crazy". As in - I am sure you overlook something. This is not a behavior I would expect on a crash UNLESS it runs on a VM and was rolling back to a backup or fixed start state.... corruption DOES sometimes make things bad or result in lost data, but rarely in a consistent state some days back. I would investigate whether you have a larger problem. I remember a company loosing money because the (not so smart) admin had set up vm's to roll back to a known state in case of a backup... which wiped access keys that can not be recovered to some serious funds (http://siliconangle.com/blog/2011/08/01/third-largest-bitcoin-exchange-bitomat-lost-their-wallet-over-17000-bitcoins-missing/). I would expect your problem to be something along this line. A rollback without corruption to a state some days ago is very very unlikely.


Managing version control of Erwin file changes with multiple environments What is the easiest way to manage three environments (DEV, QA and PROD) within a single Erwin file or multiple Erwin files?  The Erwin file is separated by subject area, so I'm not sure reverse engineering is a viable option.  We perform migrations every day, but they don't always conform to what is in Erwin, so we have a difficult time showing our end-users what is in QA and what is in PROD.

Erwin is a great tool for managing logical schemas but rarely being used to manage code (package, procedure, etc…) and content management.

Some editions of Erwin have a built-in version control, but again only for the artifacts being managed by Erwin.

TeamWork by dbMaestro (which I'm part of) does a real Database Version Control and Database Deployment.
As many of our customers do have Erwin, we recommended them to use Erwin on a dedicate environment so the flow will be like Erwin -> Dev -> QA -> …

Changes being made in the Erwin environment are promoted to Dev using TeamWork Deployment Manager and the Dev, QA, etc… environments are managed by TeamWork version control.
This gives you the ability to have logical design by Erwin and a real database version control for the physical layer by TeamWork. I hope I was helpful.


PostgreSQL function's input/output as JavaScript nested objects I am looking for a simple/clean way that can be used consistently to automatically input JavaScript nested objects to user-defined PostgreSQL functions and also automatically output to JavaScript nested objects.

Shall I used nested composite types? JSON? other ways?
How to parse to/from these types and JavaScript nested objects automatically?
Any better thoughts?

Thank you.

JavaScript nested objects can be easily (and I'd say "naturally") represented using JSON. This is "by definition" of what JSON (JavaScript Object Notation) is.

So, in your use-case use JSON

On the JavaScript side:

To parse the returned values from PostgreSQL (as strings), use JSON.parse().
To convert JSON to a string that can be included in a SQL statement, use JSON.stringify().
You can also use some libraries, such as JSON-js, although I've never used it myself.
See also Safely turning a JSON string into an object.

In PostgreSQL, on top of using json data type, you might also use jsonb.

It's got a number of advantages (smaller size, indexability, more functions), and a few differences with json (order of fields is not preserved, you can't have {"a":1,"a":2}, and a few other smallish differences that normally don't matter).
See Explanation of JSONB introduced by PostgreSQL. Look also at the second answer.

JSON is not only available in PostgreSQL. Most current databases (MySQL, MS SQL Server, Oracle, SQLite with the JSON1 extension, among others) also support it.


Can I use SSRS 2016 with SQL Server 2008 R2 database engine? I don't know that much about the state of products offering reporting/analytics, but I first got into OLAP in the late 90s, with a data warehousing tool, but then didn't touch it again for a while.  My vocabulary on the topic may not be precise.

I have data in a SQL Server 2008 R2 database.  The application putting data into it is not certified for SQL 2016, but SSRS 2016 (SQL Server Reporting Services 2016) has caught my eye.  It seems to have the features I'd like to use with this application.  I am adding reporting capabilities to a 3rd party application.

Can I create a new machine with just SSRS 2016 (which is a feature of SQL Server as a whole) and point it to the SQL 2008 R2 server with the database such that I can then start doing reporting on the application database?

What are the ramifications if the SQL 2008 R2 server is also an Enterprise server (or cluster) with many other databases on it?

This is not related to Sharepoint.  The 3rd party application is not a Microsoft application.

You can use SSRS 2016 and fetch data from another databases.
When you defines the connection string of data sources, you can use whatever of your installed drivers.
According to MS Docs:
Built-in data extensions

Default data extensions in Reporting Services include the following types of data connections:
Microsoft SQL Server
Microsoft SQL Server Analysis Services
Microsoft SharePoint List
Azure SQL Database
Microsoft SQL Server Parallel Data Warehouse
OLE DB
Oracle
SAP NetWeaver BI
Hyperion Essbase
Teradata
XML
ODBC
Microsoft BI Semantic Model for Power View: On a SharePoint site that has been configured for a Power Pivot gallery and Power View, this data source type is available. This data source type is used only for Power View presentations. For more information, see Building the Perfect BI Semantic Tabular Models for Power View.


Powershell dbatools combining result of multiple Get-dbaxxx commands to generate a report I'm new to powershell and dbatools. It is a great tool but when I try to combine the result of few different commands into an array to be exported to csv, it is not providing the intended result. It is only printing the output of first commands. Can someone please help me with joining the result of all three variables? What I'm trying to do is writing a script to automate generating our SQL Server Inventory and Capacity assessment report.

Here's my code so far:

$Servers = Get-Content 'C:\Users\temp\ServerList.txt'

$reportData = @()

foreach ($Server in $Servers) {

  if (Test-Connection -ComputerName $Server -Count 1 -ErrorAction 'SilentlyContinue') {

    $OSInfo=Get-DbaOperatingSystem -ComputerName MyServer| Select ComputerName, OSVersion, Version, Architecture
    $DiskSPace=Get-DbaDiskSpace -ComputerName MyServer| Select ComputerName, Label, Name, Capacity, Free
    $SQLInstanceInfo=Connect-DbaInstance -SqlInstance MyServer | Select DbaInstanceName, Edition, NetPort, IsClustered, Processors, ProductLevel, ServiceName,

    $reportData +=$OSInfo
    $reportData +=$DiskSPace
    $reportData +=$SQLInstanceInfo

  }

  else {

    write-host $Server "not connected"
  }
}

Write-Output $reportData | Format-Table

TL;DR
Create a PSCustomObject with the properties of your variables.

PowerShells output formatter uses the first object in the output set as a template for displaying subsequent objects in table format.
If the first object has properties A, B, and C those will be the table headers.
If the second object has only properties D, E, and F then a blank line will be shown.
You can see it for yourself with this example:
$TestResults = @()

$Test1 = [PSCustomObject]@{
    Name = 'Test1'
    Type = 'PSCustomObject'
}
$Test2 = [PSCustomObject]@{
    Name = 'Test2'
    Type = 'PSCustomObject'
}
$Test3 = [PSCustomObject]@{
    Label = 'Test3' # <-- This is "Label", not "Name".
    Type = 'PSCustomObject'
}

$TestResults += $Test1
$TestResults += $Test2
$TestResults += $Test3

# Show output:
$TestResults

Option 1 - Calculated Properties (Wouldn't recommend in your scenario):
So, you could rename the properties in your variables to have the same name using calculated properties?
$Test3 | Select-Object @{ Name = 'Name'; Expression = { $_.Label }}, Type

Then when you add this to your array, all the values should show:
$TestResult_02 = @()

$TestResult_02 += $Test1
$TestResult_02 += $Test2
$TestResult_02 += $Test3 | Select-Object @{ Name = 'Name'; Expression = { $_.Label }}, Type

$TestResult_02

Why not this method for your scenario?
All your properties mean separate things.
The Architecture property of $OSInfo doesn't match up with any property of $DiskSpace for example.
Using calculated properties here would likely end up confusing everyone in time.
Option 2 - Creating a PSCustomObject (Would recommend)
It's easy to build up a PSCustomObject type, especially if you know the output of commands or variables in advance as you do in your scenario.
$ResultObject = [PSCustomObject]@{
    Test1_Name = $Test1.Name
    Test1_Type = $Test1.Type
    Test2_Name = $Test2.Name
    Test2_Type = $Test2.Type
    Test3_Name = $Test3.Label
    Test3_Type = $Test3.Type
}

$ResultObject

Using your examples with the information on my machine, we can create the following PSCustomObject
$FullResults = [PSCustomObject]@{
    ComputerName = $OSInfo.ComputerName
    OSVersion = $OSInfo.OSVersion
    Version = $OSInfo.Version
    Architecture = $OSInfo.Architecture
    InstanceName = $SQLInstanceInfo.DbaInstanceName
    Edition = $SQLInstanceInfo.Edition
    NetPort = $SQLInstanceInfo.NetPort
    IsClustered = $SQLInstanceInfo.IsClustered
    Processors = $SQLInstanceInfo.Processors
    ProductLevel = $SQLInstanceInfo.ProductLevel
    ServiceName = $SQLInstanceInfo.ServiceName
    Disks = $DiskSPace
}

$FullResults


Can I use transaction logs to rebuild a database? We've had some data loss and don't have a reliable backup it seems. We do have HUGE transaction logs and there was some thought that we might be able to use these to get back to a certain point.

Is it possible to help rebuild a database using transaction logs?

SQL Server 2008 R2, btw.

(x-posted from stackoverflow)

No. You need a previous image of the a database (ie. a reliable backup) to start from.


Query to output column names and data types of a query, table or view Is there a PostgreSQL query or command that returns the field names and field types of a query, table or view?

E.g., a solution if applied to simple SELECT query like SELECT * from person should return a list like:

Column Name   | Column Type
===========================
First Name    | character
Last Name     | character
Age           | integer
Date of Birth | date

I have looked up the information_schema views described in an answer below and it seems to cover tables quite well, and I suspect it covers views as well but I haven't  checked that yet.

The last is any arbitrary but valid SELECT query eg involving, JOINS,  UNIONS etc, on the database. Is there a built-in procedure, or other stored procedure or script that can return the same for any valid QUERY at all?

I am developing a program that creates data and querying forms and the information is needed for data validation and executing functions on the returned data.

Information schema vs. system catalogs
We have had discussions about this many times. The information schema serves certain purposes. If you know your way around the system catalogs, those serve most purposes better, IMO. The system catalogs are the actual source of all information.
The information schema provides standardized views which help with portability, mostly across major Postgres versions, because portability across different RDBMS platforms typically is an illusion once your queries are sophisticated enough to need to look up system catalogs. And, notably, Oracle still doesn't support the information schema.
Views in the information schema must jump through many hoops to achieve a format complying to the standard. This makes them slow, sometimes very slow. Compare plans and performance for these basic objects:
EXPLAIN ANALYZE SELECT * from information_schema.columns;
EXPLAIN ANALYZE SELECT * from pg_catalog.pg_attribute;

The difference is remarkable. It really depends on what you are looking for.
Your example
For your example SELECT * from tbl, compare the two queries below for this simple table:
CREATE TEMP TABLE foo(
   A numeric(12,3)
 , b timestamp(0)
);

Using pg_attribute:
SELECT attname, format_type(atttypid, atttypmod) AS type
FROM   pg_attribute
WHERE  attrelid = 'foo'::regclass
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;

format_type() returns the complete type with all modifiers:
attname | type
--------+-------------------------------
a       | numeric(12,3)
b       | timestamp(0) without time zone

Also note that the cast to regclass resolves the table name somewhat intelligently according to the current search_path. It also raises an exception if the name is not valid. Details:

ERROR: could not find array type for datatype information_schema.sql_identifier

Using information_schema.columns:
SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'foo'
ORDER  BY ordinal_position;

The information is standardized, but incomplete:
column_name | data_type
------------+----------------------------
a           | numeric
b           | timestamp without time zone

To get full information for the data type you need to consider all of these columns additionally:
character_maximum_length
character_octet_length
numeric_precision
numeric_precision_radix
numeric_scale
datetime_precision
interval_type
interval_precision

Related answers:

How to check if a table exists in a given schema (on SO)
How do I list all columns for a specified table

A list of pros & cons, the biggest pros (IMO) in bold:
Information schema views

often simpler (depends)
slow
preprocessed, which may or may not suit your needs
selective (users only see objects they have privileges for)
conforming to an SQL standard (that's implemented by some of the major RDBMS)
mostly portable across major Postgres versions
do not require much specific knowledge about Postgres
identifiers are descriptive, long and sometimes awkward

System catalogs

often more complex (depends), closer to the source
fast
complete (system columns like oid included)
not complying to an SQL standard
less portable across major Postgres versions (but basics aren't going to change)
require more specific knowledge about Postgres
identifiers are terse, less descriptive but conveniently short

Arbitrary query
To get the same list of column names and types from a query, you could use a simple trick: CREATE a temporary table from the query output, then use the same techniques as above.
You can append LIMIT 0, since you do not need actual data:
CREATE TEMP TABLE tmp123 AS
SELECT 1::numeric, now()
LIMIT  0;

To get the data type of individual columns, you can also use the function pg_typeof():
SELECT pg_typeof(1);